Jeff Moden

Forum Replies Created

Viewing 15 posts - 41,866 through 41,880 (of 59,102 total)

RE: The Dynamic Tally or Numbers Table

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 @i =...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 22, 2009 at 1:28 pm

#1057088
RE: The Dynamic Tally or Numbers Table

the sqlist (9/22/2009)
Nice article but it has a problem: the function CAN'T be created (or it's code used) on SQL server 2000 because of the CTE approach and and row_number()...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 22, 2009 at 1:26 pm

#1057084
RE: The Dynamic Tally or Numbers Table

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 Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 22, 2009 at 1:17 pm

#1057077
RE: The Dynamic Tally or Numbers Table

timothyawiseman (9/22/2009)
Great article Lynn. It seems that even if you aren't permitted to change the schema that if you are going to use it multiple times you might get...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 22, 2009 at 11:25 am

#1056990
RE: The Dynamic Tally or Numbers Table

Lynn Pettis (9/22/2009)
Also remember that syscolumns is depreciated. It is there for backward compatibility.
Agreed. For existing 2k and 2k5 apps that aren't likely to migrate, that's probably ok...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 22, 2009 at 11:13 am

#1056974
RE: The Dynamic Tally or Numbers Table

Excellent article, Lynn. Very well laid out and nicely explained. It's too bad that the publishing process ate some of the spaces in the code or it would...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 22, 2009 at 11:09 am

#1056970
RE: The Dynamic Tally or Numbers Table

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 = 10000000
...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 22, 2009 at 10:42 am

#1056956
RE: The Dynamic Tally or Numbers Table

peter-757102 (9/22/2009)
Jeff Moden (9/22/2009)
peter-757102 (9/22/2009)
We really need a mode of SQL server where al output order is randomized if no ordering is in effect (explicit or otherwise). This would take...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 22, 2009 at 10:35 am

#1056948
RE: The Dynamic Tally or Numbers Table

peter-757102 (9/22/2009)
We really need a mode of SQL server where al output order is randomized if no ordering is in effect (explicit or otherwise). This would take away the guessing...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 22, 2009 at 10:23 am

#1056938
RE: The Dynamic Tally or Numbers Table

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 *...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 22, 2009 at 10:19 am

#1056933
RE: Lockdown or Let Them Free

Heh... visit the world of SOX and let me know if you folks still think not locking down the production server is the right way to go.
Have some dummy wipe...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 21, 2009 at 12:27 pm

#1056426
RE: temp db is logspace used is >90%.

charipg (9/21/2009)
it's 90% of the default size.
Are you saying that it's 90% of 1 megabyte? What are the sizes of the MDF and LDF files for TempDB and what...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 21, 2009 at 6:41 am

#1056178
RE: Importing csv files into SQL table using openrowset and format files

Outstanding. Thanks for the tips, Tal. I'll give it a whirl.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 21, 2009 at 6:38 am

#1056173
RE: My first (important) T-SQL stored procedure

Very cool... thanks for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 21, 2009 at 6:35 am

#1056170
RE: SQL Functions VS JOINS

cm62597 (9/18/2009)
So we are thinking about about creating SQL functions to call look up data for our application. Are there any drawbacks in using functions instead of doing a...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

September 20, 2009 at 4:42 pm

#1056069

Viewing 15 posts - 41,866 through 41,880 (of 59,102 total)