Jeff Moden

Forum Replies Created

Viewing 15 posts - 41,731 through 41,745 (of 59,098 total)

RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Thanks for the feedback and the excellent observation, Adam. That's also why I prefer the Cross Tab method over the Pivot method. If they're both the same for...
--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)

October 2, 2009 at 11:27 am

#1061093
RE: The Dynamic Tally or Numbers Table

peter-757102 (10/2/2009)
Jeff Moden (10/2/2009)
peter-757102 (10/2/2009)
Jeff Moden (10/2/2009)
That's why I shifted to All_Columns for future code. Rumor has it that syscolumns is going away and Master.sys.All_Columns always has at least...
--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)

October 2, 2009 at 11:09 am

#1061080
RE: The Dynamic Tally or Numbers Table

the sqlist (10/2/2009)
Jeff Moden (10/2/2009)
the sqlist (10/2/2009)
What is the purpose of set statistics time on inside the function?
Displays CPU consumption and Duration.
Yes, I am perfectly aware of that. My question...
--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)

October 2, 2009 at 9:28 am

#1061017
RE: The Dynamic Tally or Numbers Table

RBarryYoung (10/2/2009)
Apparently you have to include the ".sys." for the new catalogs.
Agh... of course. Thanks, Barry. On a new installation, sys.system_columns comes in at under 4k so I'll...
--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)

October 2, 2009 at 9:26 am

#1061014
RE: The Dynamic Tally or Numbers Table

peter-757102 (10/2/2009)
Jeff Moden (10/2/2009)
That's why I shifted to All_Columns for future code. Rumor has it that syscolumns is going away and Master.sys.All_Columns always has at least 4k rows on...
--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)

October 2, 2009 at 9:19 am

#1061004
RE: The Dynamic Tally or Numbers Table

Paul White (10/1/2009)
Jeff Moden (9/22/2009)
Actually, it doesn't come close. To generate 1 to 10,000,000 numbers, Lynn's method pretty much blows the doors off that type of cross join (ie:...
--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)

October 2, 2009 at 9:16 am

#1061002
RE: The Dynamic Tally or Numbers Table

the sqlist (10/2/2009)
What is the purpose of set statistics time on inside the function?
Displays CPU consumption and Duration.
--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)

October 2, 2009 at 8:55 am

#1060984
RE: The Dynamic Tally or Numbers Table

RBarryYoung (9/23/2009)
Jeff Moden (9/23/2009)
RBarryYoung (9/23/2009)
Great article, Lynn! I might finally be ready to stop cross-joining master..syscolumns. 🙂
I already quit... heh... I use Master.sys.All_Columns now. 😛
Heh. I try...
--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)

October 2, 2009 at 8:50 am

#1060982
RE: Stored Procedure SQL 2000 to SQL 2005

Kevin,
I'll admit to being a bit lazy here... do you have a link about that change? Thanks.
--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)

October 1, 2009 at 3:32 pm

#1060720
RE: Use of cursor in SQL Server vs Oracle

It is NOT true that Oracle deals with cursors better compared to set based code than SQL Server does. Set based code will beat the pants off of cursors...
--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)

October 1, 2009 at 3:20 pm

#1060718
RE: Weird CPU/Disk spikes

It happens for me, as well. It's the virus scanner and the SQL "heartbeat"... normally, the heartbeat shows up as a mere ripple... with a virus scanner turned on,...
--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)

October 1, 2009 at 3:11 pm

#1060712
RE: Converting VARCHAR value to TIME

It's a mistake to do so. You'll be losing the date element forever. Instead of converting the BIGINT to VARCHAR etc, etc, convert it to a DATETIME and...
--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)

October 1, 2009 at 3:06 pm

#1060710
RE: CTE's, Do you use them on an everyday basis?

Bradley Jacques (10/1/2009)
Just wondering how many people actually use Common Table Expressions?
My answer is somewhere in between your two poll answers. It's like asking if someone writes derived table...
--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)

October 1, 2009 at 3:01 pm

#1060708
RE: very large update

BrentMc (10/1/2009)
cheers, thanks.
That's what I had too but it's the last bit where it's checking for rows that are not null that seems to take too long in a test...
--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)

October 1, 2009 at 3:00 pm

#1060704
RE: Help writing a SQL

Test 1 fails , 2 and 3 pass but 4 fails because the clustered index update that seth was seeing in his attempt of using a view.
Heh... I ran your...
--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)

October 1, 2009 at 9:32 am

#1060522

Viewing 15 posts - 41,731 through 41,745 (of 59,098 total)