Jeff,

Test this one out:

USE [SandBox]

GO

/****** Object: UserDefinedFunction [dbo].[ufn_Tally2] Script Date: 09/22/2009 09:06:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

alter function [dbo].[ufn_Tally2](

@pStartValue bigint = 1,

...

I incorporated the changes that both Gus and Jeff suggested and using the simple SET STATISTICS TIME ON method of testing, the routine generates 1,000,000 rows in approximately 900ms.

Here is...

See, and now we are finding some very interesting errors in the code that I missed. Thanks, I do appreciate it. 🙂

Jeff Moden (9/22/2009)

---

Lynn Pettis (9/22/2009)

---

First, leave it to you squeeze another 33% out of a routine, awesome. I'll have to incorporate that change into my code. I think...

Jeff Moden (9/22/2009)

---

Lynn Pettis (9/22/2009)

---

laughingskeptic (9/22/2009)

---

One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:

declare @i bigint

set...

Here is some code to play with as well:

declare @Date1 datetime,

@Date2 datetime;

set @Date1 = '2009-09-22 12:20:21.333';

set @Date2 = '2009-09-22 12:20:21.000';

select

...

To get the best help in response to your request for help, read and follow the instructions in the first article i have referenced in my signature block regarding asking...

laughingskeptic (9/22/2009)

---

One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:

declare @i bigint

set @i = 0

select @i=@i+1...

Jeff,

First, leave it to you squeeze another 33% out of a routine, awesome. I'll have to incorporate that change into my code. I think Steve needs to add...

Jeff Moden (9/22/2009)

---

Lynn Pettis (9/22/2009)

---

Jeff Moden (9/22/2009)

---

MrAkki (9/22/2009)

---

Nice article. 🙂

Another way would be:

DECLARE @Start BIGINT, @Step BIGINT, @max-2 BIGINT

SELECT @Start = 1, @Step = 10 , @max-2 =...

Jeff Moden (9/22/2009)

---

MrAkki (9/22/2009)

---

Nice article. 🙂

Another way would be:

DECLARE @Start BIGINT, @Step BIGINT, @max-2 BIGINT

SELECT @Start = 1, @Step = 10 , @max-2 = 10000000

SELECT...

Matt, Thank you for the link. I was not aware of this either and have been looking at it for a couple of projects that are currently on the...

MrAkki (9/22/2009)

---

Nice article. 🙂

Another way would be:

DECLARE @Start BIGINT, @Step BIGINT, @max-2 BIGINT

SELECT @Start = 1, @Step = 10 , @max-2 = 10000000

SELECT (Number *...

peter-757102 (9/22/2009)

---

I wonder why a SQL magazine published code that used a multiline table valued function given their reputation for scaling bad. It will not help to educate readers on...

dbishop (9/22/2009)

---

I am a bit perplexed by the article. I buiul a tally table in each of my databases a couple of years ago. Static table, 100K rows, PK. When...