Jeff Moden

Forum Replies Created

Viewing 15 posts - 49,291 through 49,305 (of 59,102 total)

RE: How to add serial number to a resultset

That constitutes a "Triangular Join" in a correlated subquery... please read the following as to why those can be so very bad...
http://www.sqlservercentral.com/articles/T-SQL/61539/
--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)

July 10, 2008 at 6:44 pm

#841188
RE: OUTPUT FROM TSQL

You bet...
--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)

July 10, 2008 at 6:42 pm

#841187
RE: eliminate nested while loop

gan (7/9/2008)
then you meant ident_current('table name') can be used as scope_identity ?
Absolutely 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)

July 10, 2008 at 6:24 pm

#841186
RE: query tree, show tree structure

Aw rats... the fly in the ointment is that the ID's must already be in the correct hierarchical order in order to display in the correct order... Gotta add...
--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)

July 9, 2008 at 9:17 pm

#840605
RE: query tree, show tree structure

The neat thing about that code is that it allows for multiple top levels if you need them... thanks again.
--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)

July 9, 2008 at 9:10 pm

#840603
RE: query tree, show tree structure

Nicely done! 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)

July 9, 2008 at 9:06 pm

#840602
RE: Deadlocks

terry.jago (7/9/2008)
Yes it is possible to have no deadlocks, however, in this day and age most code is written badly, however, if you do get deadlocks there is no need...
--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)

July 9, 2008 at 9:03 pm

#840601
RE: -dynamic- function to convert several rows of a table to a csv list

Martin,
I also have to say that this is usually a very bad thing to do in SQL Server... most
will tell you that if you need to do it 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)

July 9, 2008 at 8:51 pm

#840599
RE: -dynamic- function to convert several rows of a table to a csv list

Leo Mepham (7/9/2008)
Any CLR experts want to offer help?
No... but you don't need any CLR experts to do this either... Leo... I'm not picking on you but
I have 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)

July 9, 2008 at 8:28 pm

#840597
RE: query tree, show tree structure

MentalWhiteNoise (7/9/2008)
You should be able to track depth easily with yoru CTE (if you are not already). Use this depth to append a precursor string... such as REPLICATE('-', Depth) +...
--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)

July 9, 2008 at 7:50 pm

#840592
RE: OUTPUT FROM TSQL

This will help... details are in the comments
[font="Courier New"]--===== How to force messages to screen on long running queries
--=========================================================================
-- What normally happens is...
--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)

July 9, 2008 at 7:38 pm

#840588
RE: Query regarding BULK INSERT

Make your life easy... import the date#time into a single column and replace the # sign with a space in the process of converting it to a real date/time.
--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)

July 9, 2008 at 7:34 pm

#840587
RE: bulk insert with csv file terminated with ascii end of file character.

Drat... I forgot you said you had a header row... you'll need to add a FIRSTROW = 2 to 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)

July 9, 2008 at 6:50 pm

#840584
RE: eliminate nested while loop

Not quite... IDENT_CURRENT('table_name') takes a table name as it's operand and has no scope other than the table itself... trigger shouldn't affect it like it can @@IDENTITY.
--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)

July 9, 2008 at 6:39 pm

#840582
RE: Query Help

Here's another way using one of the "new" functions in SQL Server 2005 and a Classic Cross Tab... this example would require two Pivot clauses and a join or two,...
--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)

July 9, 2008 at 6:35 pm

#840580

Viewing 15 posts - 49,291 through 49,305 (of 59,102 total)