Jeff Moden

Forum Replies Created

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

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
RE: Writing the code for a stored procedure

Bob Fazio (3/15/2008)
Boy was that cough medicine strong :sick:
I never thought of a computer as heavy machinery 😉
😀 Man, thanks for having a good sense of humor. I've...
--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 9:59 am

#790017
RE: How do I BULK INSERT only one or some of the fields in the row?

mukundbtech (3/13/2008)
Thank u .. and like to know is there any other option other than using a format file .. if so help me
Rethinking this, DTS would probably be the...
--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:45 am

#789970
RE: How do I BULK INSERT only one or some of the fields in the row?

Greg Snidow (3/14/2008)
Jeff, I have found that information helpful to a format file newbie is hard to come by. Maybe you know of an article that explains it 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)

March 15, 2008 at 12:43 am

#789969
RE: How to fill the record for missing month

halifaxdal (3/14/2008)
Thank you Jeff.
You bet... thank you 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)

March 15, 2008 at 12:36 am

#789968
RE: Linking to the Previous Row

Before you use any of those that have a self join or correlated subquery that use the ">=" or "<=" or any other inequality method to generate row numbers, you...
--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:31 am

#789967
RE: Writing the code for a stored procedure

Bob Fazio (3/14/2008)
It is currently against the law to use the SSN as a PK (I went through much pain to change several databases). Ok, the law states 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 12:11 am

#789966
RE: Writing the code for a stored procedure

Bob Fazio (3/14/2008)
Pat Reiter (3/14/2008)
Examples use CHAR(9), but compare to string of format '###-##-####' . Are the dashes not part of the string?
The example was wrong. It should...
--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:02 am

#789965

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