Jeff Moden

Forum Replies Created

Viewing 15 posts - 51,766 through 51,780 (of 59,098 total)

RE: SQL to do a upsert or update/insert

Thanks Adam, but he said
"The source table will have approx 10,000 records."
I want to know how many rows are in the destination table. That will answer...
--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)

March 15, 2008 at 3:31 pm

#790062
RE: Select date columns value which are older than 10 days from today?

Hi, everyone. I'm working on my senior project.
Cool! Which school?
--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)

March 15, 2008 at 3:25 pm

#790060
RE: SQL to do a upsert or update/insert

How many rows in the desitination 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)

March 15, 2008 at 3:11 pm

#790059
RE: More RBAR and "Tuning" UPDATEs

Dang, Barry... good stuff but awfully long for saying that folks preach the standards right up to the point where the standards don't work for them and then they violate...
--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)

March 15, 2008 at 2:42 pm

#790055
RE: More RBAR and "Tuning" UPDATEs

Barry, you're correct... the single column update example yields nearly identical results for both query types in 2k5.
[font="Courier New"]Correlated Subquery...
Table 'TargetTable'. Scan count 1, logical reads 54, physical reads 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)

March 15, 2008 at 1:42 pm

#790051
RE: More RBAR and "Tuning" UPDATEs

All I can say is “Not in My Experience.” Although Sql2000 did have some problems in this area (performance “bugs”, IMHO) these were mostly addressed in Sql2005.
Thanks for that...
--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)

March 15, 2008 at 1:18 pm

#790050
RE: Date Manipulations

Oh... ok. Thanks Jack.
Hey, do you have a dates table? I've got a neat trick to show you if you do...
--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)

March 15, 2008 at 12:56 pm

#790048
RE: Generating sequence

Like this...
--===== Solve the problem is 2k5
SELECT Category,[Desc],ID,
DENSE_RANK() OVER (ORDER BY Category,[Desc]) AS Seq
FROM #yourtable
ORDER BY...
--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)

March 15, 2008 at 11:55 am

#790045
RE: Copy one row from any table to same table

But for some application elements it would be very handy to copy a complete row of data
and then alter some values of the new record via the userapplication,
You really...
--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)

March 15, 2008 at 11:45 am

#790044
RE: Generating sequence

Sure... use DENSE_RANK instead of ROW_NUMBER... look it up in Books Online... except for the partition, the rest of the query will be pretty much the same...
--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)

March 15, 2008 at 11:06 am

#790039
RE: Date Manipulations

Ummm... just in case you don't have a "Dates" table like what Jack used, please consider the information at the following URL...
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
--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)

March 15, 2008 at 10:47 am

#790033
RE: Select with IS NULL on column running slow

Heh... I think the OP "left the building".
The other thing is, ( I don't think anyone mentioned this above) you will never get the same speed using SELECT * no...
--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)

March 15, 2008 at 10:43 am

#790032
RE: Add [ code ] tag to IFCode shortcuts

Jack Corbett (3/15/2008)
Steve Jones - Editor (3/15/2008)
I've added this to the list. turns out to be a switch 🙂
Sounds like good data-driven design to me:P
I gotta agree there...
... but...
--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)

March 15, 2008 at 10:38 am

#790030
RE: Generating sequence

Oh yeah... almost forgot... I recommend that you don't actually use keywords for column names...
--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)

March 15, 2008 at 10:34 am

#790028
RE: Generating sequence

Jack beat me to it but I'll post my solution anyway... the reason is that I want you to see an example of how to post information about a 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)

March 15, 2008 at 10:32 am

#790027

Viewing 15 posts - 51,766 through 51,780 (of 59,098 total)