Jeff Moden

Forum Replies Created

Viewing 15 posts - 32,911 through 32,925 (of 59,099 total)

RE: Create table by transforming values from string patterns

Mark-101232 (8/10/2011)
Another way, this one using a tally table
SELECT t.ID,
DATEADD(wk,n.N-1,t.StartDate) AS NewStartDate
FROM #MyCrazyDateThing t
INNER JOIN dbo.Tally n ON n.N BETWEEN 1 AND LEN(t.WeekPattern)
...
--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)

August 10, 2011 at 3:33 pm

#1366980
RE: T-SQL Question

No need to fuss with all that. Just do a BCP out to the file with no row terminator. 😉
--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)

August 10, 2011 at 3:29 pm

#1366977
RE: Avoiding insertion of duplicate rows

If you don't care to track what the dupes are, just put a UNIQUE index on the columns with the IGNORE DUPES option and do the inserts.
--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)

August 10, 2011 at 3:27 pm

#1366976
RE: hierarchy id functions

My recommendation would be to skip the HierarchyID and go straight to "Nested Sets" which Joe Celko made famous.
--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)

August 10, 2011 at 3:23 pm

#1366972
RE: Hidden RBAR: Counting with Recursive CTE's

Kenney Hill (8/10/2011)
Excellent article, as always, Jeff! And as all of your articles that I mange to see and read, this one has been added to my briefcase. ...
--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)

August 10, 2011 at 11:09 am

#1366752
RE: Hidden RBAR: Counting with Recursive CTE's

Dugi (8/9/2011)
Jeff,
Thanks for the very nice stuff! Simple to say that I need to read it again and doing more and more practicing and practicing ...!
Have a nice day,
Dugi
Hi Dugi,
Long...
--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)

August 9, 2011 at 8:45 pm

#1366380
RE: Hidden RBAR: Counting with Recursive CTE's

Mattrick (8/9/2011)
Jeff,
Thanks for the article and the performance metrics. After having read it, I will be sending a link to all of the developers in my department as 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)

August 9, 2011 at 8:41 pm

#1366379
RE: Hidden RBAR: Counting with Recursive CTE's

Revenant (8/9/2011)
Jeff
I have printed this article and put it into my 'keep for frequent reference' folder.
Much thanks!
You bet, Revenant. Thank you for the read and the feedback. 🙂 ...
--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)

August 9, 2011 at 8:37 pm

#1366377
RE: Hidden RBAR: Counting with Recursive CTE's

ChrisM@Work (8/9/2011)
Crackin' good read as always Jeff.
Thanks, Chris. Always good to hear from you.
I never noticed it before. Your signature line says you're a defender of moggies. ...
--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)

August 9, 2011 at 8:34 pm

#1366376
RE: Hidden RBAR: Counting with Recursive CTE's

John Dempsey (8/9/2011)
Long time no see, John. Thanks for the feedback. Heh... I'd forgotten about the "Pork Chop Logo". I wonder how T-Shirts with it would sell...
--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)

August 9, 2011 at 8:23 pm

#1366373
RE: Hidden RBAR: Counting with Recursive CTE's

tfifield (8/9/2011)
Excellent article Jeff!
It really puts the kabosh on the rCTE method. It also shows something that I found a while back. That the old fashioned tally 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)

August 9, 2011 at 8:19 pm

#1366372
RE: Hidden RBAR: Counting with Recursive CTE's

lstrashnoy (8/9/2011)
Of cause your calculations little different:
WITH --===== Itzik-Style CROSS JOIN counts from 1 to the number of days needed
E1(N) AS (
...
--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)

August 9, 2011 at 8:04 pm

#1366371
RE: how to run a sql from other servers without using linked servers

Jayanth_Kurup (8/9/2011)
in SSMS you can directly connect to the target server , you could use Sqlcmd as well.
[font="Arial Black"]HOW?[/font] That's the question. 😉
--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)

August 9, 2011 at 5:50 am

#1365795
RE: How to check stored procedure was last modified in sql server 2000

solomon.jernas (8/9/2011)
Hi,
Its works. Great
Thanks lot....
NO! It doesn't work. Despite the column heading of LAST_ALTERED, SQL Server 2000 had no mechanism for storing the date a stored procedure,...
--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)

August 9, 2011 at 5:47 am

#1365793
RE: Return a single row instead of multiple

{Edit} Sorry... wrong thread...
--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)

August 9, 2011 at 5:23 am

#1365771

Viewing 15 posts - 32,911 through 32,925 (of 59,099 total)