We are trying to save result of a query in database by tokenizing a column but query is taking more than 3 minutes to finish his objective. My question is, do we have any faster way to tokenize a column?  Query:   Select * from (         SELECT TblTwo.ID Desc_ID,            (CASE WHEN TblTwo.Description_Type = 'Default' THEN coalesce(tblOne.Code + ' ','') ELSE '' END) + TblTwo.[Description] AS String            FROM TableOne tblOne         INNER JOIN TableTwo TblTwo ON tblOne.CodeID = TblTwo.CodeID         WHERE TblTwo.Description_Type IN ('Default','Custom') )ResultOne   ---- Return almost 20000 rows and take 1 sec to execute  CROSS APPLY dbo.udf_TokenString(String) ---- return almost 194379 rows and take 3 mins to execute   Result of drived table ResultOne : Desc_ID     String 1           HCD.39877 2           HCD.39777 Personal test history of documents 4           CD.397 Unspecified document text 5           N.7 This is end of file    Script of function:  CREATE FUNCTION [dbo].udf_TokenString (@string VARCHAR(MAX)) RETURNS @Results TABLE (     Item VARCHAR(255),     location INT,     Sequence INT IDENTITY PRIMARY KEY ) AS BEGIN       DECLARE @Len INT, @Start INT, @end INT, @Cursor INT,@length INT      SELECT @Cursor=1, @len=LEN(@string)      WHILE @cursor&lt;@len        BEGIN         SELECT @start=PATINDEX('%[^.A-Za-z0-9][A-Za-z0-9%]%',                        ' '+SUBSTRING (@string,@cursor,50)                        )-1         IF @start&lt;0 break                        SELECT @length=PATINDEX('%[^.A-Z''a-z0-9-%]%',SUBSTRING (@string,@cursor+@start+1,50)+' ')          INSERT INTO @results(item, location)            SELECT  SUBSTRING(@string,@cursor+@start,@length), @cursor+@start        SELECT @Cursor=@Cursor+@Start+@length+1         END     RETURN  END   GO
42242
I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value.  Ex: "one,two,three,four" would return a new table with only one column containing the following values:  one two three four   Does this code look error prone to you guys? When I test it with   SELECT * FROM utvf_Split('one,two,three,four',',')    it just runs forever and never returns anything. This is getting really disheartening especially since there are no built in split functions on MSSQL server (WHY WHY WHY?!) and all the similar functions I've found on the web are absolute trash or simply irrelevant to what I'm trying to do.  Here is the function:  USE *myDBname* GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[utvf_SPlit] (@String VARCHAR(MAX), @delimiter CHAR)  RETURNS @SplitValues TABLE (     Asset_ID VARCHAR(MAX) NOT NULL )  AS BEGIN             DECLARE @FoundIndex INT             DECLARE @ReturnValue VARCHAR(MAX)              SET @FoundIndex = CHARINDEX(@delimiter, @String)              WHILE (@FoundIndex &lt;&gt; 0)             BEGIN                   DECLARE @NextFoundIndex INT                   SET @NextFoundIndex = CHARINDEX(@delimiter, @String, @FoundIndex+1)                   SET @ReturnValue = SUBSTRING(@String, @FoundIndex,@NextFoundIndex-@FoundIndex)                   SET @FoundIndex = CHARINDEX(@delimiter, @String)                   INSERT @SplitValues (Asset_ID) VALUES (@ReturnValue)             END              RETURN END
22294
We have a large database on which we have DB side pagination. This is quick, returning a page of 50 rows from millions of records in a small fraction of a second.  Users can define their own sort, basically choosing what column to sort by.  Columns are dynamic - some have numeric values, some dates and some text.  While most sort as expected text sorts in a dumb way. Well, I say dumb, it makes sense to computers, but frustrates users.  For instance, sorting by a string record id gives something like:  rec1 rec10 rec14 rec2 rec20 rec3 rec4   ...and so on.  I want this to take account of the number, so:  rec1 rec2 rec3 rec4 rec10 rec14 rec20   I can't control the input (otherwise I'd just format in leading 000s) and I can't rely on a single format - some are things like "{alpha code}-{dept code}-{rec id}".  I know a few ways to do this in C#, but can't pull down all the records to sort them, as that would be to slow.  Does anyone know a way to quickly apply a natural sort in Sql server?    We're using:  ROW_NUMBER() over (order by {field name} asc)   And then we're paging by that.  We can add triggers, although we wouldn't. All their input is parametrised and the like, but I can't change the format - if they put in "rec2" and "rec10" they expect them to be returned just like that, and in natural order.    We have valid user input that follows different formats for different clients.  One might go rec1, rec2, rec3, ... rec100, rec101  While another might go: grp1rec1, grp1rec2, ... grp20rec300, grp20rec301  When I say we can't control the input I mean that we can't force users to change these standards - they have a value like grp1rec1 and I can't reformat it as grp01rec001, as that would be changing something used for lookups and linking to external systems.  These formats vary a lot, but are often mixtures of letters and numbers.  Sorting these in C# is easy - just break it up into { "grp", 20, "rec", 301 } and then compare sequence values in turn.  However there may be millions of records and the data is paged, I need the sort to be done on the SQL server.  SQL server sorts by value, not comparison - in C# I can split the values out to compare, but in SQL I need some logic that (very quickly) gets a single value that consistently sorts.  @moebius - your answer might work, but it does feel like an ugly compromise to add a sort-key for all these text values.
75988
This question comes up occasionally, but I haven't seen a satisfactory answer.  A typical pattern is (row is a DataRow):   if (row["value"] != DBNull.Value)  {       someObject.Member = row["value"];  }   My first question is which is more efficient (I've flipped the condition):    row["value"] == DBNull.Value; // Or   row["value"] is DBNull; // Or   row["value"].GetType() == typeof(DBNull) // Or... any suggestions?    indicates that .GetType() should be faster, but maybe the compiler knows a few tricks I don't?  Second question, is it worth caching the value of row["value"] or does the compiler optimize the indexer away anyway?  For example:    object valueHolder;   if (DBNull.Value == (valueHolder = row["value"])) {}   Notes:   row["value"] exists. I don't know the column index of the column (hence the column name lookup). I'm asking specifically about checking for DBNull and then assignment (not about premature optimization, etc.).   I benchmarked a few scenarios (time in seconds, 10,000,000 trials):  row["value"] == DBNull.Value: 00:00:01.5478995 row["value"] is DBNull: 00:00:01.6306578 row["value"].GetType() == typeof(DBNull): 00:00:02.0138757   Object.ReferenceEquals has the same performance as "=="  The most interesting result? If you mismatch the name of the column by case (for example, "Value" instead of "value", it takes roughly ten times longer (for a string):  row["Value"] == DBNull.Value: 00:00:12.2792374   The moral of the story seems to be that if you can't look up a column by its index, then ensure that the column name you feed to the indexer matches the DataColumn's name exactly.  Caching the value also appears to be nearly twice as fast:  No Caching: 00:00:03.0996622 With Caching: 00:00:01.5659920   So the most efficient method seems to be:   object temp;  string variable;  if (DBNull.Value != (temp = row["value"]))  {       variable = temp.ToString();  }
108409
When I write a query like this...  select * from table1 t1 join table2 t2 on t1.id = t2.id   Does the SQL optimizer, not sure if that is the correct term, translate that to...  select * from table1 t1, table2 t2 where t1.id = t2.id   Essentially, is the Join statement in SQL Server just an easier way to write sql? Or is it actually used at run-time?  Edit: I almost always, and will almost always, use the Join syntax. I am just curious what happens.
63216
Using SQL Server 2005.  I am performing a huge DELETE FROM  with no where clauses.  It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE.  The problem is the table is huge - 10 million rows, and it takes over an hour to complete.  Is there any way of making it faster without:   Using Truncate Disabling or dropping indexes?   The t-log is already on a separate disk.  Any suggestions welcome!
75489
I am using IBM DB2.  So, I am retrieving a column named SSN from a table. I have to apply the following regex on that column:  ([1-57-8][0-9]{2} |0([1-9][0-9] |[0-9][1-9]) |6([0-57-9][0-9] |[0-9][0-57-9])) ([1-9][0-9]|[0-9][1-9])([1-9]\d{3}|\d[1-9]\d{2}|\d{2}[1-9]\d|\d{3}[1-9])   Someone suggested to me that I have to write a function/procedure in the query to validate that the SSN picked from the db follows this regex else it should not be stored in the result set of the query.  I don't know how to write a function or a procedure in SQL, can you please help me out here?
32081
I am working on a SQL query that reads from a SQLServer database to produce an extract file. One of the  requirements to remove the leading zeroes from a particular field, which is a simple VARCHAR(10) field. So, for example, if the field contains '00001A', the SELECT statement needs to return the data as '1A'.  Is there a way in SQL to easily remove the leading zeroes in this way? I know there is an RTRIM function, but this seems only to remove spaces.
122950
I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning:      Type conversion in expression (CONVERT_IMPLICIT(nvarchar(50),[tempdb].[dbo].[#MyFunIntTable].[EvenCoolerColumn],0)) may affect "Cardinality Estimate" in query plan choice.   Being a concerned citizen, I checked all of the obvious suspects and eventually dug into the XML to confirm that it was actually warning about the insert into the table. The problem is, I can't figure out why this would ever affect cardinality estimates. If I were doing this in a join or somewhere with a little more logic it would make sense, but there shouldn't be a cardinality estimate mismatch for the actual insert operation, right?  I noticed that this happened when it was more than just a trivial query - as soon as more than one value is inserted, or we're pulling a value from a table, we hit this.  This question has attracted some potential duplicates, including:       I think it is different from these questions because I'm literally not doing anything with this column. I'm not using it in a filter, or a sort, or a grouping, or a join, or in a function - any of these things makes the scenario more complicated. All I'm doing is inserting a bigint into a nvarchar, which should never impact a meaningful cardinality estimate that I can think of.  What I'm specifically looking for out of an answer is:   An explanation of why I get this warning despite nothing of interest going on - is it just that SQL Server will be conservative and report even when it won't affect plan choice? What cardinality estimate is actually at risk here, and what operation would change based off of inaccuracies in that cardinality estimate? Is there a scenario where this could affect plan choice? Obviously if I start joining or filtering on the converted column it could, but as-is? Is there anything that can be done to prevent it from warning, besides changing data types (assume this is a requirement of how the data models interact)   I recreated it with the below simple example ()  DROP TABLE IF EXISTS #MyFunStringTable; DROP TABLE IF EXISTS #MyFunIntTable;  CREATE TABLE #MyFunStringTable (   SuperCoolColumn nvarchar(50) COLLATE DATABASE_DEFAULT NULL );  CREATE TABLE #MyFunIntTable (   EvenCoolerColumn bigint NULL );  INSERT INTO #MyFunIntTable ( EvenCoolerColumn ) VALUES ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 );  INSERT INTO #MyFunStringTable ( SuperCoolColumn )   SELECT EvenCoolerColumn     FROM #MyFunIntTable;  INSERT INTO #MyFunStringTable ( SuperCoolColumn ) VALUES ( 1 );  INSERT INTO #MyFunStringTable ( SuperCoolColumn ) VALUES ( 1 ), ( 2 );  INSERT INTO #MyFunStringTable ( SuperCoolColumn )   SELECT 1;  INSERT INTO #MyFunStringTable ( SuperCoolColumn ) SELECT 1 UNION ALL SELECT 2;  INSERT INTO #MyFunStringTable ( SuperCoolColumn )   SELECT 1     FROM #MyFunIntTable;
65876
I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure?  Here's what my query looks like.   It takes a specific set of records (identified by @id and @createdDate), and a specific time frame (1 year starting from @startDate) and returns a summarized list of letters sent and estimated payments received as a result of those letters.  CREATE PROCEDURE MyStoredProcedure     @id int,     @createdDate varchar(20),     @startDate varchar(20)   AS SET NOCOUNT ON      -- Get the number of records * .7     -- Only want to return records containing letters that were sent on 70% or more of the records     DECLARE @limit int     SET @limit = IsNull((SELECT Count(*) FROM RecordsTable WITH (NOLOCK) WHERE ForeignKeyId = @id AND Created = @createdDate), 0) * .07      SELECT DateSent as [Date]          , LetterCode as [Letter Code]         , Count(*) as [Letters Sent]         , SUM(CASE WHEN IsNull(P.DatePaid, '1/1/1753') BETWEEN DateSent AND DateAdd(day, 30, DateSent) THEN IsNull(P.TotalPaid, 0) ELSE 0 END) as [Amount Paid]     INTO #tmpTable     FROM (          -- Letters Table. Filter for specific letters         SELECT DateAdd(day, datediff(day, 0, LR.DateProcessed), 0) as [DateSent] -- Drop time from datetime             , LR.LetterCode -- Letter Id             , M.RecordId -- Record Id         FROM LetterRequest as LR WITH (NOLOCK)         INNER JOIN RecordsTable as M WITH (NOLOCK) ON LR.RecordId = M.RecordId         WHERE ForeignKeyId = @id AND Received = @createdDate             AND LR.Deleted = 0 AND IsNull(LR.ErrorDescription, '') = ''             AND LR.DateProcessed BETWEEN @startDate AND DateAdd(year, 1, @startDate)             AND LR.LetterCode IN ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o')     ) as T     LEFT OUTER JOIN (          -- Payment Table. Payments that bounce are entered as a negative payment and are accounted for         SELECT PH.RecordId, PH.DatePaid, PH.TotalPaid         FROM PaymentHistory as PH WITH (NOLOCK)             INNER JOIN RecordsTable as M WITH (NOLOCK) ON PH.RecordId = M.RecordId             LEFT OUTER JOIN PaymentHistory as PR WITH (NOLOCK) ON PR.ReverseOfUId = PH.UID         WHERE PH.SomeString LIKE 'P_'              AND PR.UID is NULL              AND PH.DatePaid BETWEEN @startDate AND DateAdd(day, 30, DateAdd(year, 1, @startDate))             AND M.ForeignKeyId = @id AND M.Created = @createdDate     ) as P ON T.RecordId = P.RecordId      GROUP BY DateSent, LetterCode     --HAVING Count(*) &gt; @limit     ORDER BY DateSent, LetterCode      SELECT *     FROM #tmpTable     WHERE [Letters Sent] &gt; @limit      DROP TABLE #tmpTable   The end result looks like this:   Date        Letter Code     Letters Sent    Amount Paid 1/1/2012    a               1245            12345.67 1/1/2012    b               2301            1234.56 1/1/2012    c               1312            7894.45 1/1/2012    a               1455            2345.65 1/1/2012    c               3611            3213.21   I'm having problems figuring out where the slowdown is, because everything runs extremely fast in the query editor. It's only when I move the query to a stored procedure that it starts taking so long to run.   I'm sure it has something to do with the query execution plan getting generated, but I don't know enough about SQL to identify what could be causing the problem.  It should probably be noted that all the tables used in the query have millions of records.  Can someone explain to me why this is taking so much longer to run as a stored procedure than in the query editor, and help me identify what part of my query could be causing performance issues when run as a stored procedure?
68617
I have a table with approximately one million rows.  It is being used in production and I ran an UPDATE which covers ~95% of those rows.  After five hours I cancelled the request because it was taking so long.    The table has an auto-incrementing ID column, so I tried extending the WHERE condition of my query to include id BETWEEN 1 AND 500.  This update completed in approximately two seconds. I then manually iterated up through the id in batches of 500, e.g. BETWEEN 500 AND 1000, then BETWEEN 1000 AND 1500.  At this rate it will require 2000 batches of 500 to update the whole table.  Updating 2000 batches at 2 seconds each is just over an hour.    My questions are:    What reasons are there for this difference? I don't care about transaction isolation, so is there a way to simulate this 'update in batches', without having to script the 2000 updates to run separately.
103055
Please look at this code:  create table #t1(   id int identity (1,1),   val varchar(10) );   insert into #t1 values ('a'); insert into #t1 values ('b'); insert into #t1 values ('c'); insert into #t1 values ('d');   Now, whenever you execute this  select *,      ( select top 1 val from #t1 order by NEWID()) rnd  from #t1 order by 1;   you will get a result with where all rows have the same random value. e.g.  id          val        rnd ----------- ---------- ---------- 1           a          b 2           b          b 3           c          b 4           d          b   I know a way using a cursor to loop throw the rows and get different random values, but that is not performant.   A clever solution to this is   select t1.id, t1.val, t2.val from #t1 t1     join (select *, ROW_NUMBER() over( order by NEWID()) lfd from #t1) as t2 on  t1.id = t2.lfd    But I simplified the query. The real query looks more like  select *,      ( select top 1 val from t2 where t2.x &lt;&gt; t1.y order by NEWID()) rnd  from t1 order by 1;   and the simple solution doesn't fit. I'm looking for a way to force repeated evaluation of  ( select top 1 val from #t1 order by NEWID()) rnd    without the use of cursors.  Edit: Wanted output:  perhaps 1 call  id          val        rnd ----------- ---------- ---------- 1           a          c 2           b          c 3           c          b 4           d          a   and a second call  id          val        rnd ----------- ---------- ---------- 1           a          a 2           b          d 3           c          d 4           d          b   The value for each row just should be a random value independent from the other rows  Here is the cursor version of the code:  CREATE TABLE #res ( id INT, val VARCHAR(10), rnd VARCHAR(10));  DECLARE @id INT DECLARE @val VARCHAR(10) DECLARE c CURSOR FOR SELECT id, val FROM #t1 OPEN c FETCH NEXT FROM c INTO @id, @val WHILE @@FETCH_STATUS = 0 BEGIN     INSERT INTO #res     SELECT @id, @val, ( SELECT TOP 1 val FROM #t1 ORDER BY NEWID()) rnd      FETCH NEXT FROM c INTO @id, @val END CLOSE c DEALLOCATE c  SELECT * FROM #res
66399
I have the following code:  const string Sql =      @"select distinct [name]        from tblCustomers        left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId         where (tblCustomer.Name LIKE '%@SEARCH%' OR tblCustomerInfo.Info LIKE '%@SEARCH%');";  using (var command = new SqlCommand(Sql, Connection)) {            command.Parameters.AddWithValue("@SEARCH", searchString);     ... }   This does not work, I tried this as well:  const string Sql =      @"select distinct [name]       from tblCustomers       left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId        where (tblCustomer.Name LIKE @SEARCH OR tblCustomerInfo.Info LIKE @SEARCH );";  using (var command = new SqlCommand(Sql, Connection)) {            command.Parameters.AddWithValue("@SEARCH", "'%" + searchString + "%'");     ... }   but this does not work as well. What is going wrong? Any suggestions?
113863
Working in Marketing cloud where we are building a query that splits apart data from a single column into 4 separate columns where the delimiter is a semicolon.   Error is displayed when doing a "check syntax"   Error message: ';' is a reserved word and may not appear in your query.   We cant change the data to use a different delimiter so wanted to know if anyone knows of a work around?   select  c.contactid as subscriber_key  , coalesce(i.Preferred_Correspondence_Name, i.[first name]) as Preferred_Correspondence_Name  , i.[email address] as email  , c.casenumber  , c.Id as case_salesforce_id  ,party.name as party  , c.LastModifiedDate as last_modified_date  , c.CreatedDate as created_date  , c.Bank_Account_Change__c as bank_account_change  ,'add' as action  ,LTrim(PARSENAME(REPLACE([c.Bank_Account_Change__c],';','.'),4)) as Party_Details  ,LTrim(PARSENAME(REPLACE([c.Bank_Account_Change__c],';','.'),3)) as Party_ID_Details  ,LTrim(PARSENAME(REPLACE([c.Bank_Account_Change__c],';','.'),2)) as Bank_Account_Details  ,LTrim(PARSENAME(REPLACE([c.Bank_Account_Change__c],';','.'),1)) as Start_Date_Details  from  ENT.Case_salesforce_2 as c  inner join ENT.RecordType_Salesforce as rt on c.RecordTypeId = rt.Id  inner join ENT.account_salesforce as party on party.id = c.accountid  inner join contact i on i.[subscriber key]= c.contactid  where  Type = 'Payments'  and Sub_Type__c = 'Bank Account Change'  and Raised_By__c = 'External'  and Origin = 'Web'  and rt.Name = 'Financial Enquiries'  and c.Bank_Account_Change__c is not null
53355
I have been trying to tokenize a string using SPACE as delimiter but it doesn't work. Does any one have suggestion on why it doesn't work?  Edit: tokenizing using:  strtok(string, " ");   The code is like the following  pch = strtok (str," "); while (pch != NULL) {   printf ("%s\n",pch);   pch = strtok (NULL, " "); }
30459
The query is a single select containing a lot of grouping levels and aggragate operations. With SET ARITHABORT ON is takes less than a second, otherwise it takes several minutes. We have seen this behavior on SQL Server 2000 and 2008.
50293
I've been using  for some time:  SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col))   However recently, I've found a problem with columns with all "0" characters like '00000000' because it never finds a non-"0" character to match.  An alternative technique I've seen is to use TRIM:  REPLACE(LTRIM(REPLACE(str_col, '0', ' ')), ' ', '0')   This has a problem if there are embedded spaces, because they will be turned into "0"s when the spaces are turned back into "0"s.  I'm trying to avoid a scalar UDF.  I've found a lot of performance problems with UDFs in SQL Server 2005.
121040
I expect the column to be a VARCHAR2, in my Oracle Database.  US Zips are 9.  Canadian is 7.  I am thinking 32 characters would be reasonable upper limit  What am I missing?  [EDIT] TIL: 12 is a reasonable answer to the question Thanks to everyone who contributed.