InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 17:33:42 GMT20http://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxFor earlier versions of sql that don't have row_number or cte's you can use the following :-[CODE]SET STATISTICS TIME ONDECLARE @DUMMYVAR INTSELECT @DUMMYVAR = seq from (select f.col * 100000 + e.col * 10000 + d.col * 1000 + c.col * 100 + b.col * 10 + a.col as Seq from ( select colid -1 as col from master.dbo.syscolumns where id = 1 and colid < 11 ) as a cross join ( select colid -1 as col from master.dbo.syscolumns where id = 1 and colid < 11 ) as b cross join ( select colid -1 as col from master.dbo.syscolumns where id = 1 and colid < 11 ) as c cross join ( select colid -1 as col from master.dbo.syscolumns where id = 1 and colid < 11 ) as d cross join ( select colid -1 as col from master.dbo.syscolumns where id = 1 and colid < 11 ) as e cross join ( select colid -1 as col from master.dbo.syscolumns where id = 1 and colid < 11 ) as f ) as numsSET STATISTICS TIME OFF[/CODE]SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 1871 ms, elapsed time = 1871 ms.its not as fast but it worksConanFri, 15 Feb 2008 05:02:09 GMTConan-605016http://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx[quote][b]John Beggs (2/11/2008)[/b][hr]I don't know why I didn't think of this last week, but the problem (in my case) was in the database compatability level. I was working in an old testing DB that I had moved over from a 2000 server some time back and never changed it to 9.0 compatability level.@cgrigolini -- only you will know if it's safe to change the compatability level in your DB. It's my guess that is the cause of the Syntax Error.[/quote]Perfect, John... Dunno why I didn't think of that... maybe it's 'cause I quit smokin' and can't see past the thumb I'm sucking now :PMon, 11 Feb 2008 16:01:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx[b]Yes, thank youthe instruction[/b]sp_dbcmptlevel 'MYDBNAME', 90[b]has solved the problem[/b]Mon, 11 Feb 2008 11:08:17 GMTcgrigolinihttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxI don't know why I didn't think of this last week, but the problem (in my case) was in the database compatability level. I was working in an old testing DB that I had moved over from a 2000 server some time back and never changed it to 9.0 compatability level.@cgrigolini -- only you will know if it's safe to change the compatability level in your DB. It's my guess that is the cause of the Syntax Error.Mon, 11 Feb 2008 10:45:28 GMTJohn Beggshttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxReally don't know. It works on the all of the 2005 machines I've tried and fails on the 2000 machine with the exact same error.Mon, 11 Feb 2008 10:30:21 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxMicrosoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)Mon, 11 Feb 2008 10:24:15 GMTcgrigolinihttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx[quote][b]cgrigolini (2/11/2008)[/b][hr]What are CTEs?How can I check the version I'm using?What I get from SQLSERVER2005 console is:Microsoft SQL Server Management Studio 9.00.3042.00Herramientas cliente de Microsoft Analysis Services 2005.090.3042.00Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 7.0.5730.11Microsoft .NET Framework 2.0.50727.832Sistema operativo 5.1.2600[/quote]SELECT @@VERSIONMon, 11 Feb 2008 10:20:00 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxWhat are CTEs?How can I check the version I'm using?What I get from SQLSERVER2005 console is:Microsoft SQL Server Management Studio 9.00.3042.00Herramientas cliente de Microsoft Analysis Services 2005.090.3042.00Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 7.0.5730.11Microsoft .NET Framework 2.0.50727.832Sistema operativo 5.1.2600Mon, 11 Feb 2008 10:16:47 GMTcgrigolinihttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx[quote][b]cgrigolini (2/11/2008)[/b][hr][b]The error message is:[/b] Mens. 102, Nivel 15, Estado 1, Línea 3 Sintaxis incorrecta cerca de 'datediff'.[b]Translating to English it sounds:[/b] Message 102, Level 15, State 1, Line 3 Sintax error near 'datediff'[u][b]Consider that the following code works fine:[/b][/u];declare @i as integer;set @i = 365;WITH T1 AS( SELECT CAST('2007-01-01' AS DATETIME) + Number - 1 as MYDATEFROM dbo.GetNumbers(1, @i))SELECT * from T1[/quote]SQL Server 2000 gives that error (CTEs aren't supported).Please check the version you are running.Mon, 11 Feb 2008 10:08:19 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxI get the same error with an English install of 2005 Standard.I have not been able to find anything on the problem...Mon, 11 Feb 2008 09:59:33 GMTJohn Beggshttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx[b]The instruction:[/b]Select datediff(dd, '20070101','20071231')+1[b]correctly returns 365.[/b]It looks like if it didn't accept nested functions, but that's obliously not true... so I really don't know...Mon, 11 Feb 2008 08:00:20 GMTcgrigolinihttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxDunno what to say... like I said, the following code which you posted, works fine on my machine... no errors of any sort...[font="Courier New"];WITH T1 AS( SELECT CAST('2007-01-01' AS DATETIME) + Number - 1 as MYDATEFROM dbo.GetNumbers(1, datediff(dd, '20070101','20071231')+1) )SELECT * from T1[/font]I'm running SQL Server 2005 sp2 Developer's Edition which is the same as the Enterprise Edition for all practical purposes.Perhaps the problem is the "language"... maybe the datepart of "dd" needs to be changed... you'd have to look that up in your version of Books Online. Curious, what do you get when you run just the following?[font="Courier New"]Select datediff(dd, '20070101','20071231')+1[/font]Mon, 11 Feb 2008 06:30:57 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx[b]The error message is:[/b] Mens. 102, Nivel 15, Estado 1, Línea 3 Sintaxis incorrecta cerca de 'datediff'.[b]Translating to English it sounds:[/b] Message 102, Level 15, State 1, Line 3 Sintax error near 'datediff'[u][b]Consider that the following code works fine:[/b][/u];declare @i as integer;set @i = 365;WITH T1 AS( SELECT CAST('2007-01-01' AS DATETIME) + Number - 1 as MYDATEFROM dbo.GetNumbers(1, @i))SELECT * from T1Mon, 11 Feb 2008 01:35:31 GMTcgrigolinihttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxHeh... maybe the 3rd time will be the charm...[b]PLEASE copy and paste the exact error you're getting[/b]... I get no errors when I run the code you posted in the presence of the function (which also needs to be created, just in case you missed that ;) )Sun, 10 Feb 2008 11:01:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxit's your code which gives me sintax error:;WITH T1 AS( SELECT CAST('2007-01-01' AS DATETIME) + Number - 1 as MYDATEFROM dbo.GetNumbers(1, datediff(dd, '20070101','20071231')+1) )SELECT * from T1The version i'm using is:Microsoft SQL Server Management Studio 9.00.3042.00Herramientas cliente de Microsoft Analysis Services 2005.090.3042.00Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 7.0.5730.11Microsoft .NET Framework 2.0.50727.832Sistema operativo 5.1.2600Sun, 10 Feb 2008 10:38:35 GMTcgrigolinihttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx[quote][b]Dennis D. Allen (2/6/2008)[/b][hr]What do folks think about adding directly to a datetime and allowing implicit conversions to take place?[code][color="blue"]CREATE FUNCTION [/color][color="black"]dbo.DateRange[/color][color="gray"]( [/color][color="#434343"]@start [/color][color="blue"]DATETIME[/color][color="gray"], [/color][color="#434343"]@length [/color][color="blue"]INT [/color][color="gray"]) [/color][color="blue"]RETURNS [/color][color="#434343"]@range [/color][color="blue"]TABLE [/color][color="gray"]( [/color][color="black"]dateOf datetime [/color][color="blue"]PRIMARY KEY [/color][color="gray"])[/color][color="blue"]ASBEGIN[/color][color="green"]-- Populate the output table of dates using a number table [/color][color="blue"]INSERT INTO [/color][color="#434343"]@range [/color][color="gray"]( [/color][color="black"]dateOf [/color][color="gray"]) [/color][color="blue"]SELECT [/color][color="#434343"]@start[/color] [color="gray"]+[/color] [color="black"]n[/color] [color="blue"]AS [/color][color="black"]dateOf [/color][color="blue"]FROM [/color][color="gray"]( [/color][color="blue"]SELECT [/color][color="gray"]([/color][color="black"]n1.n [/color][color="gray"]+[/color][color="black"]n10.n [/color][color="gray"]+[/color][color="black"]n100.n [/color][color="gray"]+[/color][color="black"]n1000.n[/color][color="gray"]) [/color][color="blue"]AS [/color][color="black"]n [/color][color="blue"]FROM [/color][color="gray"]([/color][color="blue"]SELECT [/color][color="black"]0 [/color][color="blue"]AS [/color][color="black"]n [/color][color="blue"]UNION SELECT [/color][color="black"]1 [/color][color="blue"]UNION SELECT [/color][color="black"]2 [/color][color="blue"]UNION SELECT [/color][color="black"]3 [/color][color="blue"]UNION SELECT [/color][color="black"]4 [/color][color="blue"]UNION SELECT [/color][color="black"]5 [/color][color="blue"]UNION SELECT [/color][color="black"]6 [/color][color="blue"]UNION SELECT [/color][color="black"]7 [/color][color="blue"]UNION SELECT [/color][color="black"]8 [/color][color="blue"]UNION SELECT [/color][color="black"]9[/color][color="gray"]) [/color][color="blue"]AS [/color][color="black"]n1 [/color][color="gray"]CROSS [/color][color="blue"]JOIN [/color][color="gray"]([/color][color="blue"]SELECT [/color][color="black"]0 [/color][color="blue"]AS [/color][color="black"]n [/color][color="blue"]UNION SELECT [/color][color="black"]10 [/color][color="blue"]UNION SELECT [/color][color="black"]20 [/color][color="blue"]UNION SELECT [/color][color="black"]30 [/color][color="blue"]UNION SELECT [/color][color="black"]40 [/color][color="blue"]UNION SELECT [/color][color="black"]50 [/color][color="blue"]UNION SELECT [/color][color="black"]60 [/color][color="blue"]UNION SELECT [/color][color="black"]70 [/color][color="blue"]UNION SELECT [/color][color="black"]80 [/color][color="blue"]UNION SELECT [/color][color="black"]90[/color][color="gray"]) [/color][color="blue"]AS [/color][color="black"]n10 [/color][color="gray"]CROSS [/color][color="blue"]JOIN [/color][color="gray"]([/color][color="blue"]SELECT [/color][color="black"]0 [/color][color="blue"]AS [/color][color="black"]n [/color][color="blue"]UNION SELECT [/color][color="black"]100 [/color][color="blue"]UNION SELECT [/color][color="black"]200 [/color][color="blue"]UNION SELECT [/color][color="black"]300 [/color][color="blue"]UNION SELECT [/color][color="black"]400 [/color][color="blue"]UNION SELECT [/color][color="black"]500 [/color][color="blue"]UNION SELECT [/color][color="black"]600 [/color][color="blue"]UNION SELECT [/color][color="black"]700 [/color][color="blue"]UNION SELECT [/color][color="black"]800 [/color][color="blue"]UNION SELECT [/color][color="black"]900[/color][color="gray"]) [/color][color="blue"]AS [/color][color="black"]n100 [/color][color="gray"]CROSS [/color][color="blue"]JOIN [/color][color="gray"]([/color][color="blue"]SELECT [/color][color="black"]0 [/color][color="blue"]AS [/color][color="black"]n [/color][color="blue"]UNION SELECT [/color][color="black"]1000 [/color][color="blue"]UNION SELECT [/color][color="black"]2000 [/color][color="blue"]UNION SELECT [/color][color="black"]3000 [/color][color="blue"]UNION SELECT [/color][color="black"]4000 [/color][color="blue"]UNION SELECT [/color][color="black"]5000 [/color][color="blue"]UNION SELECT [/color][color="black"]6000 [/color][color="blue"]UNION SELECT [/color][color="black"]7000 [/color][color="blue"]UNION SELECT [/color][color="black"]8000 [/color][color="blue"]UNION SELECT [/color][color="black"]9000[/color][color="gray"]) [/color][color="blue"]AS [/color][color="black"]n1000 [/color][color="blue"]WHERE [/color][color="gray"]([/color][color="black"]n1.n [/color][color="gray"]+[/color][color="black"]n10.n [/color][color="gray"]+[/color][color="black"]n100.n [/color][color="gray"]+[/color][color="black"]n1000.n[/color][color="gray"]) BETWEEN [/color][color="black"]0 [/color][color="gray"]AND [/color][color="#434343"]@length [/color][color="gray"]- [/color][color="black"]1 [/color][color="gray"]) [/color][color="blue"]AS [/color][color="black"]numbers [/color][color="blue"]ORDER BY [/color][color="black"]n [/color][color="blue"]ASC[/color][color="gray"]; [/color][color="blue"]RETURN[/color][color="gray"];[/color][color="blue"]END[/color][/code][/quote]Nicely done is what I think...The only "catch" I see is exactly what GSquared has been talking about... you might not think an extra 200 milliseconds is much, but if you have to do it a million times, things add up in a hurry. Case in point (both examples generate 30 years of dates for, say, a mortgage)...[code]SET STATISTICS TIME ONDECLARE @BitBucket DATETIMEDECLARE @StartDate DATETIME SET @StartDate = '01/01/2000' PRINT REPLICATE('=',20)+'DateRange Function'+REPLICATE('=',20) SELECT @BitBucket = DateOf FROM dbo.DateRange('01/01/2000',11000) PRINT REPLICATE('=',20)+'Tally Table Method'+REPLICATE('=',20) SELECT @BitBucket = (@StartDate +N -1 ) FROM dbo.Tally WITH (NOLOCK) WHERE N <= 11000PRINT REPLICATE('=',20)+'End'+REPLICATE('=',20)SET STATISTICS TIME OFF[/code]... produces the following results... the important parts are highlighted...[font="Courier New"]SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.====================DateRange Function====================SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.SQL Server Execution Times: [b][color="RED"] CPU time = 203 ms, elapsed time = 196 ms.[/color][/b]====================Tally Table Method====================SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.SQL Server Execution Times:[b][color="BLUE"] CPU time = 0 ms, elapsed time = 10 ms.[/color][/b]====================End====================SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.[/font]Summary... like GSquared has said, the Tally (or Numbers) table method absolutely smokes other methods when it comes to sheer performance.Sun, 10 Feb 2008 09:04:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxHello,Here is another simple SQL way to find gaps in a numeric column,that is: the lowest number for whom its successor (+1) does not exists... (regarding Jacob Sebastian's case):SELECT min(t.CoordinatorID) FROM Coordinators t where not exists (SELECT 1 FROM Coordinators where CoordinatorID = t.CoordinatorID +1);regards,MosheSun, 10 Feb 2008 08:31:01 GMTmoshevhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx[quote][b]jerryhung (2/6/2008)[/b][hr]Weird, your CTE code took 16 seconds (twice) on my SQL Serverthat is not VERY VERY fast. Anyway, we don't have the need for it, I was just curious16 seconds on a SQL Server on VMWare, and 16 seconds on a physical SQL Server (8 cpu, 24GB RAM)[code]WITHL0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rowsnum AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)SELECT N FROM NUM WHERE N <= 1000000;[/code][/quote]Jerry,I believe you're probably looking at the little elapsed time meter in the lower right corner of the display. That includes the total time to execute AND display. Usually, this type of thing is consumed rather than displayed.To see how long it actually takes to run, try this...[code]SET STATISTICS TIME ONGOWITHL0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rowsnum AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)SELECT N FROM NUM WHERE N <= 1000000;[/code]That'll produce message output like the following...[font="Courier New"]SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.(1000000 row(s) affected)SQL Server Execution Times: CPU time = 922 ms, elapsed time = 19219 ms.[/font]The "CPU time" is how long it takes the server to execute the code... the "elapsed time" includes the amount of time it takes to display the million rows.Just so you can see the difference, let's jam the results into a throw-away variable and measure the time the same way...[code]SET STATISTICS TIME ONGODECLARE @BitBucket INT;WITHL0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rowsnum AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)SELECT @BitBucket = N FROM NUM WHERE N <= 1000000;[/code]That produces ...[font="Courier New"]SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.SQL Server parse and compile time: CPU time = 78 ms, elapsed time = 80 ms.[b][color="BLUE"]SQL Server Execution Times: CPU time = 891 ms, elapsed time = 1022 ms.[/color][/b][/font]Notice that the elapsed time for the final step (the CTE) is much less because the only thing it had to display was the execution time instead of a million rows.So, just to summarize... the speed of execution that everyone is talking about is the "CPU Time"... and that doesn't include any of the time it takes to display results (elapsed time) because results for these types of things are normally consumed by the system rather than displayed to the user.Sun, 10 Feb 2008 08:30:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxThen, you need to post the exact code you're getting the error with and the exact error... because the code I posted gives no such error on my machine. You might also want to post the version number of the SQL Server you're using.Fri, 08 Feb 2008 16:08:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxhello Jeffthanks a lot for your reply.(FYI, I normally don't use literals. I put literals only to make my sentence more readable fro the blog. And sorry about the inverted dates, I copied them reversed from my code)PLEASE, CONSIDER THAT IN ANY CASE I GET A SINTAX ERROR. EVEN YOUR LAST PIECE OF CODE RETURNS A SINTAX ERROR ("Sintax Error near Datediff".Please verifythanksC a r l o G r i g o l i n iFri, 08 Feb 2008 08:11:13 GMTcgrigolinihttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxWhich error? The one about conversion from char to date producing an out of range value? And, even if you fix that, you'll still end up with a negative number as a feed to the second operand of the function because your larger date comes before the smaller. You might also come up with a syntax error because of a missing ";".Try this and see what you get...SET DATEFORMAT DMY;WITH T1 AS( SELECT CAST('2007-01-01' AS DATETIME) + Number - 1 as MYDATEFROM dbo.GetNumbers(1, datediff(dd, '01/01/2007','31/12/2007')+1) )SELECT * from T1Just an FYI... if you get into the habit of using ISO date literals and always prefacing the CTE with a ";", you'll never have such problems as you've had with this one... like this...;WITH T1 AS( SELECT CAST('2007-01-01' AS DATETIME) + Number - 1 as MYDATEFROM dbo.GetNumbers(1, datediff(dd, '20070101','20071231')+1) )SELECT * from T1Fri, 08 Feb 2008 07:06:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx-- ¿Can anybody explain why the following sentence returns an error?Thanks WITH T1 AS ( SELECT CAST('2007-01-01' AS DATETIME) + Number - 1 as MYDATE FROM dbo.GetNumbers(1, datediff(dd, '31/12/2007', '01/01/2007') ) ) SELECT * from T1/* the GetNumbers function is the one previously defined this post */Fri, 08 Feb 2008 03:25:54 GMTcgrigolinihttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxAlso on Tony's function, unless you're planning on going over 2-billion (American billion), don't use BigInt. Int is good up to 2,147,483,647 per BOL. Making it BigInt just takes more RAM, adding to the probability of having to dump into onto the disk in TempDB, and adds a conversion step, in most cases. That's almost certain to be at least slightly slower.Thu, 07 Feb 2008 07:15:38 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx[quote][b]Tony McGarry (2/6/2008)[/b][hr]I prefer this simple and elequent way to generate numbers.I doesn't generate unnecessary numbers and is easy to understand[font="Courier New"]CREATE FUNCTION dbo.GetSequence( @Start BIGINT, @End BIGINT, @Increment BIGINT)RETURNS @ret TABLE(Number BIGINT)ASBEGIN WITH seq(num) as ( select @Start union all select num + @Increment from seq where num + @Increment <= @End ) INSERT INTO @ret(Number) Select * From SeqEND[/font][/quote]You might want to reconsider, Tony... It takes 47 seconds to generate a million numbers if you need it... and, you need to add a couple of things to it to get it to count that high...drop function getsequenceGOCREATE FUNCTION dbo.GetSequence(@Start BIGINT,@End BIGINT,@Increment BIGINT)RETURNS @ret TABLE(Number BIGINT)ASBEGINWITHseq(num)as(select @Startunion allselect num + @Increment from seqwhere num + @Increment <= @End )INSERT INTO @ret(Number)Select * From Seq[color="RED"][b]OPTION (MAXRECURSION 0)RETURN[/b][/color]ENDWed, 06 Feb 2008 18:31:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxThanks for the retest, John :)Sure... Matt and I go round'n'round... it's all in good nature and we both learn things in the process. Hopefully, everyone both understands that [i]and [/i]gets a benefit from it.Wed, 06 Feb 2008 18:09:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx[quote][b]John Beggs (2/6/2008)[/b][hr]@MattI think you'll find that if you want to do a "true" (well not true, but closer than it was) "right sizing" of the comparison, then you'll need to make the adjustment in bold.L5 AS (SELECT 1 AS C FROM L4 AS A, L[b]2[/b] AS B),--[b]1048576[/b] rowsI think you'll find that this puts Jeff's method back on top.As a frequent reader but not a frequent poster, I know you and Jeff have this back and forth thing...but I felt compelled to jump in here. ;)[/quote]ACK - caught at my own game I see...hehe. It would help if I returned a million rows when asked for them (and not 100,000)You're right I dropped a zero in there. The funny part about it is if you run both version @100K rows, Itzik's does in fact win (47ms to 62ms). However - by the time you hit 1M to return, the ORDER BY in the Itzik method makes it less efficient (since sys.all_columns is already indexed).Hey - jump in any time! we like to bash each other with tests, demolition derby-style, so - the more the merrier:).Wed, 06 Feb 2008 15:58:37 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx@MattI think you'll find that if you want to do a "true" (well not true, but closer than it was) "right sizing" of the comparison, then you'll need to make the adjustment in bold.L5 AS (SELECT 1 AS C FROM L4 AS A, L[b]2[/b] AS B),--[b]1048576[/b] rowsI think you'll find that this puts Jeff's method back on top.As a frequent reader but not a frequent poster, I know you and Jeff have this back and forth thing...but I felt compelled to jump in here. ;)Wed, 06 Feb 2008 15:29:56 GMTJohn Beggshttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxJeff/Jacob:first - Jacob - very nice. Good solid method for handling a common request.Jeff - I'll chalk this up to being the forever contrarian... But the comparison gets more interesting when you "right-size" the Itzik method. Notice what one tiny little change does (since we KNOW how many results we want....)[code]SET STATISTICS TIME ONGODECLARE @BitBucket INT--=============================================================================PRINT REPLICATE('=',100)PRINT 'Itzek''s method:';WITHL0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L[b]0[/b] AS B),--131072 rowsnum AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)SELECT @BitBucket = N FROM NUM WHERE N <= 1000000;--=============================================================================PRINT REPLICATE('=',100)PRINT 'Jeff Moden''s Method'; WITH cTally AS(----------------------------------------------------------------------------- --==== High performance CTE equivalent of a Tally or Numbers table SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY t1.ID) AS N FROM Master.sys.SysColumns t1 CROSS JOIN Master.sys.SysColumns t2)-----------------------------------------------------------------------------SELECT @BitBucket = N FROM cTally --Do your outer join with table being checked herePRINT REPLICATE('=',100)[/code]Can you spot the difference (it's in bold....hehe)? Of course - we could debate why we're fighting so hard to reclaim 200ms or less, but - what's the fun in that?Wed, 06 Feb 2008 13:48:55 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxI prefer this simple and elequent way to generate numbers.I doesn't generate unnecessary numbers and is easy to understand[font="Courier New"]CREATE FUNCTION dbo.GetSequence( @Start BIGINT, @End BIGINT, @Increment BIGINT)RETURNS @ret TABLE(Number BIGINT)ASBEGIN WITH seq(num) as ( select @Start union all select num + @Increment from seq where num + @Increment <= @End ) INSERT INTO @ret(Number) Select * From SeqEND[/font]Wed, 06 Feb 2008 10:37:00 GMTTony McGarryhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxWhat do folks think about adding directly to a datetime and allowing implicit conversions to take place?[code][color="blue"]CREATE FUNCTION [/color][color="black"]dbo.DateRange[/color][color="gray"]( [/color][color="#434343"]@start [/color][color="blue"]DATETIME[/color][color="gray"], [/color][color="#434343"]@length [/color][color="blue"]INT [/color][color="gray"]) [/color][color="blue"]RETURNS [/color][color="#434343"]@range [/color][color="blue"]TABLE [/color][color="gray"]( [/color][color="black"]dateOf datetime [/color][color="blue"]PRIMARY KEY [/color][color="gray"])[/color][color="blue"]ASBEGIN[/color][color="green"]-- Populate the output table of dates using a number table [/color][color="blue"]INSERT INTO [/color][color="#434343"]@range [/color][color="gray"]( [/color][color="black"]dateOf [/color][color="gray"]) [/color][color="blue"]SELECT [/color][color="#434343"]@start[/color] [color="gray"]+[/color] [color="black"]n[/color] [color="blue"]AS [/color][color="black"]dateOf [/color][color="blue"]FROM [/color][color="gray"]( [/color][color="blue"]SELECT [/color][color="gray"]([/color][color="black"]n1.n [/color][color="gray"]+[/color][color="black"]n10.n [/color][color="gray"]+[/color][color="black"]n100.n [/color][color="gray"]+[/color][color="black"]n1000.n[/color][color="gray"]) [/color][color="blue"]AS [/color][color="black"]n [/color][color="blue"]FROM [/color][color="gray"]([/color][color="blue"]SELECT [/color][color="black"]0 [/color][color="blue"]AS [/color][color="black"]n [/color][color="blue"]UNION SELECT [/color][color="black"]1 [/color][color="blue"]UNION SELECT [/color][color="black"]2 [/color][color="blue"]UNION SELECT [/color][color="black"]3 [/color][color="blue"]UNION SELECT [/color][color="black"]4 [/color][color="blue"]UNION SELECT [/color][color="black"]5 [/color][color="blue"]UNION SELECT [/color][color="black"]6 [/color][color="blue"]UNION SELECT [/color][color="black"]7 [/color][color="blue"]UNION SELECT [/color][color="black"]8 [/color][color="blue"]UNION SELECT [/color][color="black"]9[/color][color="gray"]) [/color][color="blue"]AS [/color][color="black"]n1 [/color][color="gray"]CROSS [/color][color="blue"]JOIN [/color][color="gray"]([/color][color="blue"]SELECT [/color][color="black"]0 [/color][color="blue"]AS [/color][color="black"]n [/color][color="blue"]UNION SELECT [/color][color="black"]10 [/color][color="blue"]UNION SELECT [/color][color="black"]20 [/color][color="blue"]UNION SELECT [/color][color="black"]30 [/color][color="blue"]UNION SELECT [/color][color="black"]40 [/color][color="blue"]UNION SELECT [/color][color="black"]50 [/color][color="blue"]UNION SELECT [/color][color="black"]60 [/color][color="blue"]UNION SELECT [/color][color="black"]70 [/color][color="blue"]UNION SELECT [/color][color="black"]80 [/color][color="blue"]UNION SELECT [/color][color="black"]90[/color][color="gray"]) [/color][color="blue"]AS [/color][color="black"]n10 [/color][color="gray"]CROSS [/color][color="blue"]JOIN [/color][color="gray"]([/color][color="blue"]SELECT [/color][color="black"]0 [/color][color="blue"]AS [/color][color="black"]n [/color][color="blue"]UNION SELECT [/color][color="black"]100 [/color][color="blue"]UNION SELECT [/color][color="black"]200 [/color][color="blue"]UNION SELECT [/color][color="black"]300 [/color][color="blue"]UNION SELECT [/color][color="black"]400 [/color][color="blue"]UNION SELECT [/color][color="black"]500 [/color][color="blue"]UNION SELECT [/color][color="black"]600 [/color][color="blue"]UNION SELECT [/color][color="black"]700 [/color][color="blue"]UNION SELECT [/color][color="black"]800 [/color][color="blue"]UNION SELECT [/color][color="black"]900[/color][color="gray"]) [/color][color="blue"]AS [/color][color="black"]n100 [/color][color="gray"]CROSS [/color][color="blue"]JOIN [/color][color="gray"]([/color][color="blue"]SELECT [/color][color="black"]0 [/color][color="blue"]AS [/color][color="black"]n [/color][color="blue"]UNION SELECT [/color][color="black"]1000 [/color][color="blue"]UNION SELECT [/color][color="black"]2000 [/color][color="blue"]UNION SELECT [/color][color="black"]3000 [/color][color="blue"]UNION SELECT [/color][color="black"]4000 [/color][color="blue"]UNION SELECT [/color][color="black"]5000 [/color][color="blue"]UNION SELECT [/color][color="black"]6000 [/color][color="blue"]UNION SELECT [/color][color="black"]7000 [/color][color="blue"]UNION SELECT [/color][color="black"]8000 [/color][color="blue"]UNION SELECT [/color][color="black"]9000[/color][color="gray"]) [/color][color="blue"]AS [/color][color="black"]n1000 [/color][color="blue"]WHERE [/color][color="gray"]([/color][color="black"]n1.n [/color][color="gray"]+[/color][color="black"]n10.n [/color][color="gray"]+[/color][color="black"]n100.n [/color][color="gray"]+[/color][color="black"]n1000.n[/color][color="gray"]) BETWEEN [/color][color="black"]0 [/color][color="gray"]AND [/color][color="#434343"]@length [/color][color="gray"]- [/color][color="black"]1 [/color][color="gray"]) [/color][color="blue"]AS [/color][color="black"]numbers [/color][color="blue"]ORDER BY [/color][color="black"]n [/color][color="blue"]ASC[/color][color="gray"]; [/color][color="blue"]RETURN[/color][color="gray"];[/color][color="blue"]END[/color][/code]Wed, 06 Feb 2008 10:26:42 GMTDennis D. Allenhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxWeird, your CTE code took 16 seconds (twice) on my SQL Serverthat is not VERY VERY fast. Anyway, we don't have the need for it, I was just curious16 seconds on a SQL Server on VMWare, and 16 seconds on a physical SQL Server (8 cpu, 24GB RAM)[code]WITHL0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rowsnum AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)SELECT N FROM NUM WHERE N <= 1000000;[/code]Wed, 06 Feb 2008 10:25:13 GMTJerry Hunghttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxYou can also find missing dates with a Numbers table more easily than is outlined in the article.To set up the test:[code]create table MissingDates (Date datetime primary key)goinsert into dbo.missingdates(date)select dateadd(second, subtime.number, subdate.date)from (select dateadd(day, number, '1/1/08') as Date from common.dbo.Numbers where number <= 100) SubDatecross join (select number from common.dbo.BigNumbers where number <= (3600 * 24) - 1) SubTimegodelete from dbo.missingdateswhere date between '1/12/08' and '1/13/08'godelete from dbo.missingdateswhere date between '1/30/08' and '1/31/08'[/code]This time, I tried my usual Numbers table method:[code]select dateadd(day, number, '1/1/08')from common.dbo.numbersleft outer join dbo.missingdates on date >= dateadd(day, number, '1/1/08') and date < dateadd(day, number + 1, '1/1/08')where date is nullnumber between 0 and 31[/code]------------------SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.(2 row(s) affected)Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'MissingDates'. Scan count 32, logical reads 5638, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Numbers'. Scan count 3, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 688 ms, elapsed time = 687 ms.------------------Then I tried the method recommended in the article:[code]SELECT CAST('2008-01-01' AS DATETIME) + Number-1 FROM dbo.GetNumbers(1, 30)where CAST('2008-01-01' AS DATETIME) + Number-1 not in (select cast(convert(varchar(100), date, 101) as datetime) from dbo.missingdates)[/code](The cast-convert is necessary because without that, it will simply detect missing midnights, not whole missing days.)I killed that process after it had been running for 3 minutes. Not sure how long it would have taken in all.Converted it to:[code]select dateadd(day, number, '1/1/08')from dbo.getnumbers(1,31)left outer join dbo.missingdates on date >= dateadd(day, number-1, '1/1/08') and date < dateadd(day, number, '1/1/08')where date is null[/code]Which is pretty much identical to the query used with the Numbers table.----------------SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.(2 row(s) affected)Table '#28D10FF3'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'MissingDates'. Scan count 31, logical reads 5418, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 673 ms, elapsed time = 366 ms.-----------------This time, the total elapsed time was shorter, but the CPU time was nearly identical. One less scan count on the MissingDates table, and the server was able to split the process across more than 1 CPU (the computer I'm testing this on is a dual-core Pentium D).Some advantage to the CTE, but in a loaded up server, it won't make much difference.Wed, 06 Feb 2008 07:53:08 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx[quote][b]GSquared (2/6/2008)[/b][hr]Yet again, I have to say this is clever, but a Numbers table is better.Yes, if you for some reason need to create a list of sequential numbers on the fly, this CTE is probably the fastest means of doing so. It certainly is at least A fast means of doing so. But having an actual Numbers table, with a clustered index on it, is MUCH better.(Yes, this is the third or fourth time I've written on this exact subject. But for whatever reason, it keeps coming up.)[/quote]Spot on, G... didn't see your post as I was writting mine.Wed, 06 Feb 2008 07:33:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxSuperaltive article. Thanks.Wed, 06 Feb 2008 07:30:44 GMTMike DiRenzohttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxAgreedWed, 06 Feb 2008 07:27:01 GMTMike DiRenzohttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxGreat tip Jeff. Thank you for sharing this. I remember this discussion is coming up for the second time. I appreciate your comments and am glad to learn something new everytime I read your posts :)Wed, 06 Feb 2008 07:26:35 GMTjacob sebastianhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxGSquared,I agree that a number table should be the first choice. The approach presented in this article is useful for cases when you do not want to use a number table.Thanks for sharing the performance data. That was really helpful.Wed, 06 Feb 2008 07:20:17 GMTjacob sebastianhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx[i]Very [/i]nicely done, Jacob.Please... not trying to take anything away from this great article... just sharing a different method...As a slightly easier to remember (and, a bit faster, too) approach to generating numbers, check out the following (comparison between Itzek's and a method that I and several others use)...[code]SET STATISTICS TIME ONGODECLARE @BitBucket INT--=============================================================================PRINT REPLICATE('=',100)PRINT 'Itzek''s method:';WITHL0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rowsL1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rowsL2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rowsL3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rowsL4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rowsL5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rowsnum AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)SELECT @BitBucket = N FROM NUM WHERE N <= 1000000;--=============================================================================PRINT REPLICATE('=',100)PRINT 'Jeff Moden''s Method'; WITH cTally AS(----------------------------------------------------------------------------- --==== High performance CTE equivalent of a Tally or Numbers table SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY t1.ID) AS N FROM Master.sys.SysColumns t1 CROSS JOIN Master.sys.SysColumns t2)-----------------------------------------------------------------------------SELECT @BitBucket = N FROM cTally --Do your outer join with table being checked herePRINT REPLICATE('=',100)[/code]True, Itzeks's will generate more than what an INT can handle, but, how often do you think you're gonna need to generate more than 121 million numbers? ;)Here's the same thing as a programmable function...[code] CREATE FUNCTION dbo.fnTally/**************************************************************************************** Purpose: Given a range of Integers not exceeding a count of 121 million, return the range of numbers as a table. Notes: Preserved as an "inline" single statement function for sheer performance. Therefore, no error checking, etc. Revision History: Rev 00 - 23 Dec 2005 - Jeff Moden - Initial creation and unit test****************************************************************************************/--===== Declare the parameters ( @piStartNumber INT, @piEndNumber INT )RETURNS TABLE AS RETURN (WITH cTally AS (-------------------------------------------------------------------------------- --==== High performance CTE equivalent of a Tally or Numbers table SELECT TOP (@piEndNumber-@piStartNumber+1) ROW_NUMBER() OVER (ORDER BY t1.ID) AS Number FROM Master.sys.SysColumns t1 CROSS JOIN Master.sys.SysColumns t2 )-------------------------------------------------------------------------------- SELECT N = Number+@piStartNumber-1 FROM cTally )[/code]Still, a small (11k rows - 30 years of dates) permanent Tally table with a Clustered Index on N will usually beat calculated table functions once the table is cached.Again, I say, nice article, Jacob! Do it again!Wed, 06 Feb 2008 07:13:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxHello Jacob,Beautiful article, nice and crisp!I have recently used a similar approach for identifying the next business date for a given date which required the recognition of weekends and holidays. The solution was to - select the minimum date from this dates set as you described - which is greater than the date of the transaction - which is not a weekend (datename <> Sunday / Saturday) - which is not a holiday (date not in HolidayTable) Wed, 06 Feb 2008 07:05:56 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic452000-356-1.aspxYet again, I have to say this is clever, but a Numbers table is better.Method in the article, run against a table with 9989 rows:----------------SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 4 ms.(1 row(s) affected)Table 'Table'. Scan count 1, logical reads 39996, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#28D10FF3'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 140 ms, elapsed time = 137 ms.----------------Numbers table method:[code]select min(number)from Common.dbo.Numbersleft outer join dbo.Table on number = idwhere id is nulland number > 0[/code]--------------SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.(1 row(s) affected)Table 'Table'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Numbers'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 6 ms.-----------------I ran each five times and results were +/- 2 ms total.To make it more fair, I changed the method in the article to a left join, instead of a Where Not In:[code]SELECT MIN(Number) FROM dbo.GetNumbers(0, 9999) left outer join dbo.Table on number = idWHERE id is null[/code]--------------SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.(1 row(s) affected)Table 'Table'. Scan count 0, logical reads 19998, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#28D10FF3'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 109 ms, elapsed time = 103 ms.----------------Which was a measurable improvement over the Not In method, but still much slower than a Numbers table.Test against code suggested in article: Numbers table 20 times as fastTest against article modified to Left Join instead of Not In: Numbers table 15 times as fastYes, if you for some reason need to create a list of sequential numbers on the fly, this CTE is probably the fastest means of doing so. It certainly is at least A fast means of doing so. But having an actual Numbers table, with a clustered index on it, is MUCH better.(Yes, this is the third or fourth time I've written on this exact subject. But for whatever reason, it keeps coming up.)Wed, 06 Feb 2008 07:01:18 GMTGSquared