Jeff Moden

Forum Replies Created

Viewing 15 posts - 33,676 through 33,690 (of 59,099 total)

RE: Returning 0's in a SUM (SQL Spackle)

berzat.museski (6/14/2011)
Jeff, I must tell you that your solution is briliant as well!
Thanks for the compliment but, nah... I just used a variation of what is in the article. :-)...
--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)

June 14, 2011 at 10:11 am

#1338511
RE: Returning 0's in a SUM (SQL Spackle)

Gatekeeper (6/14/2011)
Alexey Voronin (6/14/2011)
What's this COALESCE(SUM(SalesTestData.SaleQty),0) ?
You do not know ISNULL ?
ISNULL(SUM(SalesTestData.SaleQty),0)
They are interchangeable. Both are the same thing except COALESCE() allows for more than one evaluation.
Actually, they're not interchangeable...
--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)

June 14, 2011 at 10:06 am

#1338505
RE: Does anyone have a good reason to run xp_CmdShell?

GSquared (6/14/2011)
"Grand Master at command line use," isn't likely to land a job.
Heh... I agree. For most, it won't. For me, it was a different story... a similar...
--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)

June 14, 2011 at 10:00 am

#1338497
RE: Does anyone have a good reason to run xp_CmdShell?

Thomas LeBlanc (6/14/2011)
3rd party backup software
Thomas
I just love short, sweet, and accurate. Thanks, Thomas. 🙂
--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)

June 14, 2011 at 9:56 am

#1338493
RE: Does anyone have a good reason to run xp_CmdShell?

Shawn Melton (6/14/2011)
Jeff Moden (6/13/2011)
Shawn Melton (6/13/2011)
SQLCMD is used mostly locally on the server
Thanks again, Shawn. The real question is "How is SQLCMD being called?" Are you calling...
--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)

June 14, 2011 at 9:56 am

#1338491
RE: Does anyone have a good reason to run xp_CmdShell?

I knew I shouldn't have posted this on a Friday night. 😛 You good folks sure did come alive on Monday. Thank all of you a huge amount.
--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)

June 14, 2011 at 6:44 am

#1338319
RE: Using SELECT INTO a bad practice?

tfifield (6/13/2011)
Jeff Moden (6/12/2011)
allmhuran (6/12/2011)
Interesting. Ever discovered a pattern for when they're fast or slow? I pretty much abandoned them a while ago, figuring it was just asking a bit...
--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)

June 13, 2011 at 10:51 pm

#1338158
RE: Remove Duplicate Records

AmolNaik (6/13/2011)
Smart technique. But beware SET ROWCOUNT is set to be deprecated in the future releases, instead you could use TOP 1.
An alternate method to remove duplicates is 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)

June 13, 2011 at 10:13 pm

#1338150
RE: Returning 0's in a SUM (SQL Spackle)

berzat.museski (6/13/2011)
No need for uncle Google in this case, spt_values is quite enough, unless you are querying some large data warehouse.
However, I agree that GetNums is a cool trick 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)

June 13, 2011 at 10:09 pm

#1338149
RE: Returning 0's in a SUM (SQL Spackle)

harry9katz (6/13/2011)
Hi
You have to add hour to both tables
SELECT cast(floor(cast(SaleTime as float)) as datetime)Saledate,DATEPART(hour,SaleTime) saleshour
,sum([SaleQty]) sumsales
into #salessum
...
--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)

June 13, 2011 at 6:35 pm

#1338130
RE: Returning 0's in a SUM (SQL Spackle)

DavidBridgeTechnology.com (6/13/2011)
I have a number table called Numbers on my system with a single column called Number and indexed values from 0 to 99,999. I use this table for 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)

June 13, 2011 at 6:19 pm

#1338128
RE: Does anyone have a good reason to run xp_CmdShell?

Shawn Melton (6/13/2011)
SQLCMD is used mostly locally on the server
Thanks again, Shawn. The real question is "How is SQLCMD being called?" Are you calling it through a job...
--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)

June 13, 2011 at 3:51 pm

#1338082
RE: Does anyone have a good reason to run xp_CmdShell?

Ninja's_RGR'us (6/13/2011)
Because I can!
BWAA-HAAAA!!!! I'm right there with you except my line of reasoning would be "Because I LIKE it!" 😀
Thanks for the feedback, Remi.
--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)

June 13, 2011 at 10:06 am

#1337877
RE: Does anyone have a good reason to run xp_CmdShell?

allmhuran (6/13/2011)
The reason why I use xp_cmdshell instead of CLR is that file handling in .NET is not pretty. Robocopy does the job far more easily, plus from within 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)

June 13, 2011 at 10:03 am

#1337875
RE: Does anyone have a good reason to run xp_CmdShell?

Lowell (6/13/2011)
lol...i use it myself whenever i feel like it...but can i justify using it at all; it was the fastest to grab tool in the toolbox...nah, can't justify myself...
--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)

June 13, 2011 at 10:00 am

#1337872

Viewing 15 posts - 33,676 through 33,690 (of 59,099 total)