Jeff Moden

Forum Replies Created

Viewing 15 posts - 47,356 through 47,370 (of 59,095 total)

RE: Divide by zero error encountered

Kishore.P (11/7/2008)
use COALESCE function instead of IsNull or NullIf.
Why? NullIf is nothing like IsNull or Coalesce and the idea of code portability is a myth.
--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)

November 9, 2008 at 10:11 pm

#896114
RE: Normalizing-Denormalized Tables

I'm all for normalizing data... but if no one is going to use the normalized form, then you shouldn't bother. In this case, it actually provides a negative ROI...
--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)

November 9, 2008 at 9:49 pm

#896112
RE: Mitigatng Circumstances for a Heap ?

SQLBOT (11/8/2008)
LOL,
yes, yes... touche
In those cases when I'm strapped for extra 208k of disk, I usually drop the clustered index.
~BOT
Heh... Like you say... touche`. You're absolutely correct about...
--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)

November 8, 2008 at 9:30 am

#895964
RE: Mitigatng Circumstances for a Heap ?

You bet... thanks for the feedback, Timothy.
--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)

November 8, 2008 at 7:47 am

#895955
RE: Mitigatng Circumstances for a Heap ?

Because, contrary to popular belief, Clustered Indexes are NOT totally contained in the data at the Leaf level... there is an Intermediate Level containing index rows AND a Root Level,...
--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)

November 7, 2008 at 7:14 pm

#895898
RE: Parsed string into a view

Robert klimes (11/6/2008)
And after further investigation the number of parsed coulmns will be static.
So... how many columns?
--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)

November 7, 2008 at 6:55 pm

#895897
RE: Pattern matching on bad date stamps

cliffb (11/6/2008)
Jeff Moden (11/6/2008)
Cliff, take a look at the text file attached in the first post of this thread... not much to "pattern match" on... certainly, not enough to...
--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)

November 7, 2008 at 6:20 pm

#895895
RE: compacting a numbered list

Thanks... and that's a very appropriate use for that code. If it was a huge table with large gaps, I was gonna suggest a different way.
--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)

November 7, 2008 at 6:17 pm

#895894
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

David Jackson (11/7/2008)
Excellent. 😀
I particularly like the date formatting trick. Why didn't I think of that! :w00t:
Many thanks Hugo. I'll run the extract later today and post some...
--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)

November 7, 2008 at 6:12 pm

#895893
RE: SQL - Derived Tables

tony rogerson (1/16/2008)
The derived table itself is just expanded into the main query (just like a view) this means that there are no statistics available for the derived table.
Tony.
I'm not...
--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)

November 7, 2008 at 5:57 pm

#895890
RE: Mitigatng Circumstances for a Heap ?

SQLBOT (11/7/2008)
even then a clustered index doesn't hurt because it doesn't take up extra space.
~BOT
Any bet's on that? 😉
USE TempDB
GO
--===== Create and populate a 1,000,000 row test 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)

November 7, 2008 at 5:34 pm

#895888
RE: Trigger that uses a Parameter with Inserted/Deleted

To make a really long story shorter...
Some manager was complaining about one of his developers as being non-compliant, difficult to control, and generally an arrogant moron. After many suggestions...
--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)

November 7, 2008 at 5:12 pm

#895884
RE: Retrieve variable value from dynamically concatenated variable

rbarryyoung (11/7/2008)
Local variables are entirely local in scope and visibility: whereas dynamic SQL makes a new batch scope for every invocation that will not be able to see the original...
--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)

November 7, 2008 at 4:55 pm

#895878
RE: creating temporary table

Then, I have to agree with kshitij... unless I'm missing something, those all sound like "read" operations and there's no need for the overhead of building a 1+ million row...
--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)

November 6, 2008 at 6:01 am

#894791
RE: Pattern matching on bad date stamps

Cliff, take a look at the text file attached in the first post of this thread... not much to "pattern match" on... certainly, not enough to build a whole...
--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)

November 6, 2008 at 5:57 am

#894786

Viewing 15 posts - 47,356 through 47,370 (of 59,095 total)