Jeff Moden

Forum Replies Created

Viewing 15 posts - 33,601 through 33,615 (of 59,099 total)

RE: How to get the number of hours Working

At this point, my recommendation would be to build a Calendar or Date table. If you have problems finding a good one on Google or Yahoo (or whatever your...
--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 17, 2011 at 6:31 pm

#1340742
RE: Best way to alias column names

jmaloney-1117255 (6/17/2011)
Steve Hoyer (6/17/2011)
I'm working on a query that's returning about 45 rows and most of the rows are combinations of other fields in one way or another, so most...
--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 17, 2011 at 6:22 pm

#1340741
RE: temp table in user database?

glen.wass (6/17/2011)
HI, try looking up variable temp tables, as i understand it these work from memory...
It's a myth... Table Variables aren't "memory only" (which is what I believe you were...
--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 17, 2011 at 4:48 pm

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

Jayanth_Kurup (6/17/2011)
I have a report which i use to check the health of my database servers, as a part of it i use xp_cmdshell to execute pings to the db...
--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 17, 2011 at 4:44 pm

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

opc.three (6/17/2011)
I don't disagree. My stance on it is part security and part "developers, stay away from the servers hosting my SQL Server instances". If I open up xp_CmdShell then...
--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 17, 2011 at 4:38 pm

#1340732
RE: Degrading Service Performance Over Time

allmhuran (6/17/2011)
My mind first jumped to indexing but I don't think it makes sense. Why would a reboot fix it?
Because a reboot clears proc cache. It's like telling...
--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 17, 2011 at 6:13 am

#1340344
RE: temp table in user database?

Carlton Leach (6/17/2011)
Also, depends on the code that's being written by your developers.
Exactly. 🙂 "Accidental Cross-Joins" (think many-to-many joins frequently overcome by the use of DISTINCT or GROUP 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)

June 17, 2011 at 5:51 am

#1340338
RE: temp table in user database?

Brandie Tarvin (6/16/2011)
However, a lot of people I know use staging tables that they create and drop at need in a user db. These are permanent tables, even though they...
--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 17, 2011 at 5:47 am

#1340335
RE: temp table in user database?

Jayanth_Kurup (6/17/2011)
try using ctes
Oh, be careful, now. That can actually make the problem worse especially if the data in the result set of the CTE is needed more than...
--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 17, 2011 at 5:33 am

#1340330
RE: High CPU, Low Reads

yasubmj (6/17/2011)
Hi,
Here is the code in the sp which takes a lot of time, around 17 secs, the reads are only 1303,
SELECT
col1, col2, col3....,
(CASE @LocDBSortOrder
WHEN 'DESC' THEN
CASE @LocstrSortExpression...
--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 17, 2011 at 5:22 am

#1340327
RE: How to remove duplicates from column without using Temp table

Brandie Tarvin (6/16/2011)
If you don't have an Identity, add it to the table.
I prefer a method which exploits the IDENTITY column, as well, but it's just not possible to 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)

June 17, 2011 at 5:10 am

#1340322
RE: How to remove duplicates from column without using Temp table

bitbucket-25253 (6/16/2011)
For a matter of record the above code was copied from a posting by Jeff Moden on a SSC forum, and not my work, but one I use frequently.
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)

June 17, 2011 at 5:08 am

#1340321
RE: How to remove duplicates from column without using Temp table

Sriram.RM (6/16/2011)
u might wanna try this!!!!
SET ROWCOUNT 1
DELETE YOURTABLE
FROM YOURTABLE A WHERE (SELECT COUNT(*) FROM YOURTABLE B WHERE A.COLNAME=B.COLNAME) >1
WHILE @@ROWCOUNT >0
DELETE YOURTABLE
FROM YOURTABLE A WHERE (SELECT COUNT(*) FROM YOURTABLE...
--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 17, 2011 at 5:03 am

#1340317
RE: How to remove duplicates from column without using Temp table

vaibhavraut88 (6/16/2011)
Great Job dude ur script works. but can u explain me that code.
Ummm.... which script? The one with the WHILE Loop or the other one?
--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 17, 2011 at 5:01 am

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

opc.three (6/16/2011)
:Wow: I just set myself up big time huh?
Nah... considering the number of hacker attacks designed specifically to exploit OLE DB Automation, I can see your point especially in...
--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 17, 2011 at 4:50 am

#1340312

Viewing 15 posts - 33,601 through 33,615 (of 59,099 total)