Jeff Moden

Forum Replies Created

Viewing 15 posts - 50,941 through 50,955 (of 59,098 total)

RE: Boost TSQL query code

p.s. For future reference, it's almost always going to be a huge performance drain to try to join to aggregated columns like you have. Preaggregation using a temp...
--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 29, 2008 at 7:44 am

#808342
RE: Boost TSQL query code

I believe every one has hit all the hot spots... Carl's last post should be a big help, as well. The problem is that the derived table in 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 29, 2008 at 7:41 am

#808336
RE: Issues related to the speed of the execution of sql statement in mssql server 2005

That covers just about all of it... can't think of anything else to check unless the WHERE clauses have a formula in one and not the other.
--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 29, 2008 at 7:23 am

#808307
RE: Fetch Row by Row with out using cursor

Grant is correct. Tell us what needs to be done... not how to do it. There's usually no need for any form of RBAR. Post some data...
--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 29, 2008 at 7:21 am

#808304
RE: Find Last Day of Month

karthikeyan (4/29/2008)
Jeff,
I have refined the above code as
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '04/Apr/2007', @DateEnd = '29/Apr/2008'
SELECT convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N-1,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N-1,@DateStart))),103),
DateAdd(DD,-1,convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N,@DateStart))),103))
FROM dbo.Tally
where DateAdd(DD,-1,convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N,@DateStart))),103)) <= @DateEnd
I got the below output:
Apr...
--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 29, 2008 at 7:16 am

#808303
RE: IT salary is going down !

I guess I didn't know you were a "bubble head", Grant. I was the lead ping jockey on SSN 604... did a little time on the Parche (637 stretch...
--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 29, 2008 at 7:12 am

#808299
RE: How to update, in a table, all the columns with a name that begins with the same string (same value for all)

Adrian's code does create and execute "one big update" statement as you've asked... have you tried it on a test 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)

April 29, 2008 at 7:07 am

#808297
RE: Query to get all days of a specific month and year

For a single result set that you can join to...
--===== Declare your parameters
DECLARE @pYear INT, @pMonth INT
--===== Set the parameters (simulates a proc or udf parameters)
SELECT @pYear = 2008,
...
--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 29, 2008 at 2:24 am

#808167
RE: Find Last Day of Month

To get all of the dates in the range all at once, then the following will do it for you...
--===== Here are the two parameters you wanted
DECLARE @DateStart DATETIME
DECLARE @DateEnd...
--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 29, 2008 at 2:15 am

#808164
RE: Working with strings

Well... lemme shift gears here, a bit. You say you want a "text representation" of the matrix of 100,000 columns and 10,000 rows... that's 1,000,000,000 or a Billion "cells"...
--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 29, 2008 at 1:35 am

#808151
RE: Altering Column Values

Understood... I was agreeing with 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)

April 28, 2008 at 5:13 pm

#808086
RE: query to find space usage of each column in a table

Lemme ask again... what's in the TEXT column?
--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 28, 2008 at 5:09 pm

#808085
RE: Updating the rows with Identity values

virgo (4/28/2008)
:w00t: ok Jeff....just in some cases i need to do that way....am using sql server 2005....
thanks much for ur reply
Ian's method using ROW_NUMBER() OVER will work just fine, 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)

April 28, 2008 at 5:06 pm

#808083
RE: Altering Column Values

Zactly...
--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 28, 2008 at 7:15 am

#807739
RE: Where can I find this in BOL?

Heh... thanks Grant... I'm just a wee bit embarrased that I didn't pick up on that before I posted the question. 😛 More COFFEE!:hehe:
--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 28, 2008 at 7:02 am

#807725

Viewing 15 posts - 50,941 through 50,955 (of 59,098 total)