December 11, 2008 at 3:59 am
Hi jeff,
thanks for your kind gesture .
could u reveal the logic behind the Query you sent .
Thanks in Advance!
Regards,
Sabari.C
December 11, 2008 at 4:31 am
Try this
declare @startDate as datetime
Declare @enddate as datetime
declare @interval as int
declare @ACounterVariable as int
declare @calc as int
set @StartDate = Getdate() - 10
Set @enddate = getdate()
Set @ACounterVariable = 0
Select @interval = datediff(day,@startdate, @endDate)
select @interval
While @ACounterVariable < @interval
Begin
Set @calc = @interval - @ACounterVariable
Select getdate()- @calc
Set @ACounterVariable = @ACounterVariable + 1
end
December 11, 2008 at 5:46 am
Sergiy (12/10/2008)
Jeff, I know, you've got SQL2k5 to play with, but this is SQL2k forum, remember?😉
Crud... nah... I missed that... I was just flying through some posts and didn't look. Thanks for the reminder.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2008 at 5:52 am
sabarichandru (12/11/2008)
Hi jeff,thanks for your kind gesture .
could u reveal the logic behind the Query you sent .
Thanks in Advance!
Regards,
Sabari.C
Basically, it's nothing more than a bunch of "derived tables" that reference each other using cross-joins to generate the correct count of rows... except it uses the 2k5 replacement for derived tables known as CTE's. It's very, very fast and will blow a While loop away for performance.
I've been meaning to convert the method to something that will work in SQL Server 2000... I'm on my way to work, but I'll see if I can do the conversion later tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2008 at 6:32 am
Actually, if you do a search for "Numbers Table", you'll find all manner of methods to make such a "tableless" number generator. I don't use them... I use an inplace Tally table all the time. When I'm really in a pinch and can't use a table because of a DBA that doesn't understand the value of such a thing, I'll gen one in TempDB and us it.
Like this...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE #Tally
ADD PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
You can also build one as a function... one of the fastest and most flexible I've seen for SQL Server 2000, is also one of the most complex and pretty much defies memorization...
CREATE FUNCTION dbo.fnTally
/**********************************************************************************************************************
Purpose:
This function returns a series of integers as a table variable.
Programmer Notes:
1. Positive or negative numbers may be used for either the Lo number or the Hi number in the range.
2. The maximum range (Hi-Lo+1) is 4096^2 or 16,777,216.
3. This function may be used wherever a Tally or Numbers table is used
in peformance.
4. Note that the embedded SELECT TOP 100 PERCENT code should not be removed as it is reponsible for about 50% of the
performance in Tally table related usage.
Benchmarks:
100 numbers in 0 to 16 milliseconds (13 typical)
1,000 numbers in 0 to 16 milliseconds (16 typical)
10,000 numbers in 30 to 46 milliseconds (33 typical)
100,000 numbers in 250 to 266 milliseconds (266 typical)
1,000,000 numbers in 3,313 to 3,593 milliseconds (3513 typical)
10,000,000 numbers in about 33,060 milliseconds (33 seconds)
Code for Benchmark Testing:
DECLARE @Bitbucket INT
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
SELECT @Bitbucket = N FROM dbo.fnTally(1,100) --Change "100" to other benchmark numbers
SELECT DATEDIFF(ms,@StartTime,GETDATE())
Functionality:
Basically, this function internally generates two tables with values from 0 up to 4095. The max value of each table
is determined by the finding the square root of the desired absolute range. The values from the second table are
multiplied by the square root of the desired absolute range. Then, the two tables are cross-join added to the @pLoNum
to create the desired absolute range of numbers. It all happens remarkably fast.
Example Usage:
SELECT * FROM dbo.fnTally(@pLoNum,@pHiNum)
Revision History:
Rev 00 - 03/29/2005 - Michael Valentine Jones - Initial creation
Reference - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
Rev 01 - 05/09/2007 - Jeff Moden - Added full documentation, code cleanup, and minor enhancement to performance.
**********************************************************************************************************************/
--=====================================================================================================================
-- Setup
--=====================================================================================================================
--===== Declare input/Output parameters
(
@pLoNum INT, --The desired lowest integer in the range
@pHiNum INT --The desired highest integer in the range
)
RETURNS TABLE
AS
RETURN
(--==== This final outer SELECT does the cross-join add of the two internally generated derived tables to the low
-- number of the desired range to produce the final resulting range of whole numbers
SELECT TOP 100 PERCENT
N = (t1.N + t2.N)
+ CASE --Add the low number to all the numbers to start the series with the low number.
WHEN @pLoNum <= @pHiNum --When the input parameters are in the correct order...
THEN @pLoNum --add to the first parameter...
ELSE @pHiNum --otherwise, add to the second paramter because it's lower than the first.
END
FROM (--Creates all numbers from 0 to 4095 (4096 numbers, total) using cross join between 3 derived tables
--and is limited by the square root of the range (+1 to be sure)
SELECT TOP 100 PERCENT
N = (c1.N+c2.N+c3.N)
FROM (--Base 16 raised to the 0 power (16^0)
SELECT N = 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
) c1
,
(--Base 16 raised to the 1st power (16^1)
SELECT N = 0 UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 UNION ALL
SELECT 64 UNION ALL SELECT 80 UNION ALL SELECT 96 UNION ALL SELECT 112 UNION ALL
SELECT 128 UNION ALL SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL
SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
) c2
,
(--Base 16 raised to the 2nd power (16^2)
SELECT N = 0 UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768 UNION ALL
SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536 UNION ALL SELECT 1792 UNION ALL
SELECT 2048 UNION ALL SELECT 2304 UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL
SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840
) c3
WHERE (c1.N+c2.N+c3.N) < SQRT(ABS(@pLoNum-@pHiNum)+1) --Limits to the square root of the desired range +1
ORDER BY N
) t1
,
(--Creates all numbers from 0 to 4095 (4096 numbers, total) using cross join between 3 derived tables
--and is limited by the square root of the range (+1 to be sure)
--Notice that these numbers are multiplied by the square root of the desired absolute range
SELECT TOP 100 PERCENT
N = (c1.N+c2.N+c3.N)
* CONVERT(INT,CEILING(SQRT(ABS(@pLoNum-@pHiNum)+1))) --SQRT of Range difference
FROM (--Base 16 raised to the 0 power (16^0)
SELECT N = 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
) c1
,
(--Base 16 raised to the 1st power (16^1)
SELECT N = 0 UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 UNION ALL
SELECT 64 UNION ALL SELECT 80 UNION ALL SELECT 96 UNION ALL SELECT 112 UNION ALL
SELECT 128 UNION ALL SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL
SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
) c2
,
(--Base 16 raised to the 2nd power (16^2)
SELECT N = 0 UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768 UNION ALL
SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536 UNION ALL SELECT 1792 UNION ALL
SELECT 2048 UNION ALL SELECT 2304 UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL
SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840
) c3
WHERE (c1.N+c2.N+c3.N) < SQRT(ABS(@pLoNum-@pHiNum)+1) --Limits to the square root of the desired range +1
ORDER BY N
) t2
WHERE (t1.N + t2.N) < ABS(@pLoNum-@pHiNum) + 1 --Limits the output to the desired absolute range
AND CASE --For some reason, using CASE in this one spot shaves off some milliseconds
WHEN ABS(@pLoNum-@pHiNum) + 1 <= 16777216 --Ensures the inputs do not request and overflow of the max range
THEN 1
ELSE 0
END = 1
ORDER BY N
)
It does have a limit of 16 million... if you need more than that, you might be doing something else wrong, anyway.
Still, the best way in SQL Server 2000 is to use a Tally or Numbers table. DBA's that don't allow them (especially in 2k) pretty much need to be dismissed as ignorant.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply