Jeff Moden

Forum Replies Created

Viewing 15 posts - 51,121 through 51,135 (of 59,091 total)

RE: Generate Month Day Table Variable

Just my 2 cents... you don't need CTE's for this... don't even need derived tables... just the Cross Join...
SELECT t1.N AS Month, t2.N AS Day
FROM dbo.Tally...
--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 14, 2008 at 8:23 pm

#802339
RE: Deleting all the values from all tables in a Database

I agree with Sergiy... life would be a lot easier that way... create a "Gold Set" of scripts or even an empty database to copy from.
--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 14, 2008 at 8:14 pm

#802338
RE: Subsitute of TO_CHAR in transact SQL???

Heh... I'm thinking "tie" and I'm thinking that, once again, only RegEx replace beats T-SQL... you sure it can't be done using some magic RegEx replace?
And, by the way, I...
--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 14, 2008 at 8:12 pm

#802337
RE: rounding up to the next whole number

SELECT CEILING(CAST (12.2 AS FLOAT))
--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 14, 2008 at 8:08 pm

#802336
RE: Query Help - T-SQL

You do realize that your table is a bit denormalized? Can't you turn it into a true EAV (Entity, Attribute, Value) 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 14, 2008 at 8:03 pm

#802335
RE: T-sql update name fields

Splitting free text names is a huge pain in the patoooti... For example, two-part first names, middle initials, and a host of other formatting sins (FullName being the first sin)...
--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 14, 2008 at 7:49 pm

#802334
RE: PASS 2008 - Article

Steve, do you have a URL handy to sign up?
--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 14, 2008 at 7:01 pm

#802330
RE: Slow clustered index seek?

Only if the tuning Wizard says so... haven't figured them well enough to do it on my own, yet... I guess it is a possibility here...
--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 14, 2008 at 6:57 pm

#802329
RE: total time difference between more than two dates

Heh... you started it... was hoping you had some final code 😉
--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 14, 2008 at 11:42 am

#802205
RE: Slow clustered index seek?

Ooohhh... not good. Just wait till you try a bunch of inserts that are close to each other for the cSpecialty column... it's a 4 letter word... WAIT....
--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 14, 2008 at 11:40 am

#802204
RE: total time difference between more than two dates

Sorry, Gus... we're posting in parallel...
The -1 addition will work but I think the tweek needs to be extended to the days column, as 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 14, 2008 at 11:33 am

#802201
RE: total time difference between more than two dates

GSquared (4/14/2008)
Jeff, good article. Just to add to it, you can do this:
declare @D1 datetime, @D2 datetime
select @d1 = '02/Nov/06 9:14:21 AM', @d2 = '19/Apr/07 11:52:31 AM'
select datepart(year, @d2-@d1)-1900...
--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 14, 2008 at 11:31 am

#802199
RE: Subsitute of TO_CHAR in transact SQL???

Matt Miller (4/14/2008)
Jeff Moden (4/14/2008)
I wonder which would work more quickly? I think the CLR might win this one because it uses implicit functions...
As of right now - "straight...
--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 14, 2008 at 10:51 am

#802178
RE: Oracle LEAD function - Is there an equivalent function in SQL Server ??

antonio.collins (4/14/2008)
LEAD()/LAG() give access to next/prior rows in an ordered set without needing a self join.
http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php
Thanks, Antonio.
Heh... "Ordered Sets" in an RDBMS... I use them a fair bit but what...
--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 14, 2008 at 10:44 am

#802171
RE: Generate CREATE TABLE script from DataTable structure

Temp table???? What temp 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 14, 2008 at 10:35 am

#802166

Viewing 15 posts - 51,121 through 51,135 (of 59,091 total)