Agreed, thanks for posting. I'm always happy to see someone introduced to the tally table.

And thank you Jeff, for keeping an eye out for the opportunities to make those introductions.

One...

Thanks! I hope this helps with a problem we're currently having.

Script requires BIGINT, not INT, here:

@iBatchStart AS BIGINT,

...

So which is faster? On our clunky dev server, it takes about 30 seconds to iterate both methods one million times.

Avg. execution time of each method:

[font="Courier New"]

One-liner : 0.014490000000000

With Tally:...

Here you go, Jack. Thanks for making me dig.

--SET LANGUAGE italian

--SET LANGUAGE us_english

DECLARE @StartDate date

DECLARE @DaysOut tinyint

DECLARE @DesiredDayOfWeek tinyint

DECLARE @TargetDate date

DECLARE @MaxDays int

SET @StartDate = '20111013' --The starting date

SET...

No matter what, SQL makes my head itch. So no matter what, I comment...comment..comment.

Thanks Itzik Ben-Gan for the in-line number generator, and tally table crusader Mr. Moden for getting...

b3yond:

I agree set-based is best, but it doesn't work for large number of columns and/or rows, due to @CMD length limitation.

I would suggest speeding up cursor method by eliminating the...

Does this seemingly simpler, and far faster, method produce accurate results?

SELECT name [Table], sum(row_count) AS [Rows]

FROM sys.dm_db_partition_stats WITH (NOLOCK), sysobjects WITH (NOLOCK)

WHERE index_id < 2-- Just grab...

Here is the result of my Convert-Loop-To-Tally exercise:

/* Convert signed integer to binary notation: */

DECLARE @BINval CHAR(39) = ''-- Format: 'xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx'

,@INTval INT =...

That is what kchant means when he says

If stats aren't updated, sys partitions won't be accurate.

cvcscvcs: Use of CURSOR is avoidable here (and almost everywhere else) when you bang your head against the wall long enough.

Same output format as yours:

SELECT name, sum(row_count) AS MAX_MSG_ID

FROM...

Total noob here. Love this series, can't wait for 3rd installment.

I found some code in an article today, author said "The easiest way I've found to do this is run...