Jeff Moden

Forum Replies Created

Viewing 15 posts - 38,896 through 38,910 (of 59,072 total)

RE: Understanding and Using APPLY (Part 1)

korolana (4/13/2010)
Nice article, but what is an advantage to use this approach vs simple join?
From the performance point of view I don't think that calling UDTF on every row of...
--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)

April 13, 2010 at 1:59 pm

#1151003
RE: Recursive queries - get the top N quantity

steve-lauzon (4/13/2010)
Holy smokes !
Thanks a lot guys... you've just saved me a bunch of time - just the solution I was searching for
Can't wait for the day that I'll be...
--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)

April 13, 2010 at 7:34 am

#1150712
RE: Recursive queries - get the top N quantity

P.S. I don't believe that FLOAT is the thing to use here. Could be wrong, though.
--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)

April 12, 2010 at 11:01 pm

#1150484
RE: Recursive queries - get the top N quantity

This could probably stand a tweek here and there but here's the code I came up with that I was going to post before you beat me to the punch....
--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)

April 12, 2010 at 11:00 pm

#1150483
RE: Recursive queries - get the top N quantity

Just convert the Qty from the temp table back to weight and do a final update on the original second table. Don't forget to update the Qty on 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)

April 12, 2010 at 10:16 pm

#1150479
RE: Recursive queries - get the top N quantity

Heh... I was just getting ready to post a nearly identical solution. I'm pretty proud of you guys... you beat me at my own game anymore. 😛 Well...
--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)

April 12, 2010 at 9:58 pm

#1150475
RE: Recursive queries - get the top N quantity

Yes, it helps a lot. Thanks. Just one final question... is there a column in the second table to show what has been consumed and what has 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)

April 12, 2010 at 8:27 pm

#1150461
RE: TSQL Lookup using BETWEEN (Ip Addresses)

Stefan_G (4/12/2010)
Jeff Moden (4/12/2010)
Stefan_G (4/12/2010)
1) Add a nonclustered index on CountryCode, IpNumber on [my].[ipAddresses]
It'll make for a fine nonclustered index. Using it as a clustered index can cause 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)

April 12, 2010 at 5:56 pm

#1150447
RE: Recursive queries - get the top N quantity

I don't believe it can be done without a wee bit more information. Either the Qty column is missing from the second table or we're supposed to guess 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)

April 12, 2010 at 4:55 pm

#1150425
RE: Is This Odd Behavior? Empty String ('') vs int

GSquared (4/12/2010)
Empty strings = 0. It's kind of arbitrary, but that's how it works.
IsNumeric has a number of flaws, this is just one of them. Check out IsNumeric('1d3')...
--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)

April 12, 2010 at 4:41 pm

#1150420
RE: TSQL Lookup using BETWEEN (Ip Addresses)

Stefan_G (4/12/2010)
Jeff Moden (4/12/2010)
Stefan_G (4/12/2010)
1) Add a nonclustered index on CountryCode, IpNumber on [my].[ipAddresses]
Ohhhh... be careful now. Unless that index is applied after all data is present and 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)

April 12, 2010 at 4:09 pm

#1150413
RE: Using IsNull when passed variable value is ''

sql_avid_fan (4/12/2010)
--delete from folders_rel
where exists (select b.id
from folders_rel a with(nolock)
...
--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)

April 12, 2010 at 1:06 pm

#1150286
RE: Which is better and Why.?

GSquared (4/12/2010)
Jeff Moden (4/12/2010)
Bill Kline-270970 (4/12/2010)
I work in a multi db platform environment. We use both SQL Server and Oracle. I'm just a newbie when it comes 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)

April 12, 2010 at 1:02 pm

#1150280
RE: Clustered index scan versus seek

Robert Frasca (4/12/2010)
The indexes were rebuilt last night, are not fragmented and the statistics are up to date. Additionally, I've forced a recompile of the proc a couple of times.
Did...
--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)

April 12, 2010 at 12:55 pm

#1150270
RE: TSQL Lookup using BETWEEN (Ip Addresses)

Stefan_G (4/12/2010)
1) Add a nonclustered index on CountryCode, IpNumber on [my].[ipAddresses]
Ohhhh... be careful now. Unless that index is applied after all data is present and no new data will...
--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)

April 12, 2010 at 12:51 pm

#1150268

Viewing 15 posts - 38,896 through 38,910 (of 59,072 total)