Jeff Moden

Forum Replies Created

Viewing 15 posts - 52,276 through 52,290 (of 59,072 total)

RE: The Cost of Function Use In A Where Clause

Matt Miller (2/28/2008)
I'm thinking he means that if you persist the computed column, you can then include it in the index being used to optimized the query. saves all...
--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)

February 28, 2008 at 7:45 am

#783169
RE: The Cost of Function Use In A Where Clause

smonarch (2/28/2008)
Why not create an index computed column where you can provide this capability for routinely used functions.
Ummmm.... not sure what you mean by an "index computed column".... how does...
--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)

February 28, 2008 at 7:35 am

#783161
RE: The Cost of Function Use In A Where Clause

GSquared (2/28/2008)
Alternately, if you don't often select by year, month, day, but do often join on the whole date, you can add a calculated column:
alter dbo.TableA
add DateColumnDate as cast(convert(varchar(25), DateColumn,...
--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)

February 28, 2008 at 7:23 am

#783151
RE: The Cost of Function Use In A Where Clause

David Jackson (2/28/2008)
This looks complex but will use your index.
where CreatedDate between dateadd(dd, datediff(dd,0,Getdate()),0) --midnight TODAY
and dateAdd(ss,-1,dateAdd(dd,1,dateadd(dd, datediff(dd,0,Getdate()),0)))...
--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)

February 28, 2008 at 7:14 am

#783146
RE: The Cost of Function Use In A Where Clause

Nicely done, Gregory Jackson. Outstanding intro to the pitfalls of doing calculations in the WHERE clause.
--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)

February 28, 2008 at 6:59 am

#783138
RE: The Identity Debate

Manie Verster (2/28/2008)
😎 I have to agree that identity columns are indeed evil! Especially when someone before you created a database and now you sit with the problems. Example: 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)

February 28, 2008 at 6:39 am

#783128
RE: Restore part of the data

I believe that is correct... I say "believe" because I've never had to do it... I've only read about it. Gails snapshot option is also viable.
--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)

February 28, 2008 at 6:28 am

#783127
RE: Restore part of the data

Rumor has it that creating a table on a different filegroup will do what you ask.
--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)

February 27, 2008 at 7:31 pm

#782954
RE: change variable on the fly

You know why #77? You get "eight" more 😉
--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)

February 27, 2008 at 7:17 pm

#782953
RE: change variable on the fly

Matt Miller (1/28/2008)
I can't take credit for it - I got clued into this by Jeff Moden (who was using 2000's syscolumns equivalent).
Was doing some research on Tally tables 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)

February 27, 2008 at 6:42 pm

#782950
RE: how to show row wise data columnwise data

Just curious... did that solve your problem or did you die? 😉
--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)

February 27, 2008 at 6:37 pm

#782949
RE: Sequential numbering issue

Heh... nah... it's only 0.229 M rows... same as the other two tests...
I did mislabel it though... probably due to low Caffeine with a Niquil kicker, as well... the last...
--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)

February 27, 2008 at 5:40 pm

#782948
RE: Help on splitting the name field in a query

Okidoki... first things first... If you don't already have a Tally table, now is the time to add one to your arsenal of SQL prestidigitation... a Tally table (also known...
--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)

February 27, 2008 at 5:26 pm

#782946
RE: Help on splitting the name field in a query

Perfect... I'll be back soon...
--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)

February 27, 2008 at 3:34 pm

#782915
RE: Sequential numbering issue

Sorry... just as a courtesy, I should post the code for making a Tally table, huh?
--===== Create and populate the Tally table on the fly
SELECT TOP 19000 --equates 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)

February 27, 2008 at 3:31 pm

#782912

Viewing 15 posts - 52,276 through 52,290 (of 59,072 total)