Jeff Moden

Forum Replies Created

Viewing 15 posts - 35,716 through 35,730 (of 59,099 total)

RE: Sorting Months By Number (SQL Spackle)

Harveysburger (12/10/2010)
SELECT [Month] = DATENAME(mm, DATEADD(mm, MONTH(SomeDateTime), 0)), Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime...
--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)

December 12, 2010 at 8:13 pm

#1261496
RE: Returning the Top X row for each group

BTW... the problem with the first two queries (after the test data setup) is that they both have full blown Triangular Joins in them.
--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)

December 12, 2010 at 8:10 pm

#1261494
RE: Returning the Top X row for each group

david.soussan (12/10/2010)
Well Dave, I was hoping for some real benchmark data, but thanks anyway.
Setup SQL Profiler and you'll have it. 😉
So far as true portability across platforms goes, it's 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)

December 12, 2010 at 7:45 pm

#1261490
RE: Delete duplicate records in a table

Hardy21 (12/7/2010)
Sachin Nandanwar (12/7/2010)
Jeff Moden (12/4/2010)
Sachin Nandanwar (12/3/2010)
Also it is a better idea to put the recover model to SIMPLE or else your log file will bloat.But make sure 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)

December 12, 2010 at 7:27 pm

#1261487
RE: Tuning slow query

LOOKUP_BI-756009 (12/9/2010)
SELECT DISTINCT A.Status
FROM dbo.Test1 A
LEFT JOIN dbo.Test2 B
ON A.Status= B.Status
WHEREB.Status IS NULL
AND A.Status IS NOT NULL
Number of rows in dbo.Test1 A = 150 000
Number of rows in dbo.Test2 B...
--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)

December 12, 2010 at 6:28 pm

#1261475
RE: Date Ranges

WayneS (12/11/2010)
Jeff Moden (12/11/2010)
Try this...
I knew there was a simpler way, but I just couldn't figure it out. Thanks Jeff!
Thanks for the feedback, Wayne. Hmmmm... one "spackle" article coming...
--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)

December 12, 2010 at 10:28 am

#1261433
RE: Date Ranges

BSavoie (12/11/2010)
I don't have to try it Jeff, that is exactly what I was looking for. I remember it now that I see it. Thank You!! Now I just have...
--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)

December 12, 2010 at 10:27 am

#1261432
RE: Date Ranges

Try this...
--===== Define the desired dates for a reservation
DECLARE @StartDate DATETIME,
@EndDate DATETIME;
SELECT @StartDate = '2011-02-07',
...
--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)

December 11, 2010 at 9:29 pm

#1261370
RE: avoding recursion in CTE

Jeff Moden (12/11/2010)
Eralper (12/2/2010)
But if you are using cursors instead of CTEs, then that will not be a performance optimized option.
In most cases of recursive CTE's, I've actually found 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)

December 11, 2010 at 5:01 pm

#1261354
RE: avoding recursion in CTE

Abhijit More (12/2/2010)
Is there is any way to break / avoid the recursion in the CTE QUERY??
No. If you could explain a bit more of what you're actually trying...
--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)

December 11, 2010 at 4:26 pm

#1261351
RE: avoding recursion in CTE

Eralper (12/2/2010)
But if you are using cursors instead of CTEs, then that will not be a performance optimized option.
In most cases of recursive CTE's, I've actually found the WHILE loop...
--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)

December 11, 2010 at 4:24 pm

#1261350
RE: trying to count number of distinct minutes of record inserted by hour. If minutes are in sequential order then they should count as 1.

@BaldingLoopMan,
This should do it compliments of some old but very reliable mag tape methods I learned in the 50's 😉 No need for an extra table, either.
WITH
cteGroupedDates 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)

December 11, 2010 at 4:13 pm

#1261349
RE: trying to count number of distinct minutes of record inserted by hour. If minutes are in sequential order then they should count as 1.

CELKO (12/8/2010)
set up and populate a table with the time slots defined by the start and end times, then update that table as you get data. It should be an...
--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)

December 11, 2010 at 4:09 pm

#1261347
RE: Get new Identity Value Insert into another table

CELKO (12/8/2010)
Your approach is completely wrong. You are trying to build pointer chains using the count of physical insertion attempts as pointers instead of a real key. You are not...
--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)

December 11, 2010 at 2:56 pm

#1261343
RE: PreVisor Assessment Test

Just a personal opinion here... I don't believe in such canned tests because they're not testing for what I want to see in a Developer or DBA.
--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)

December 11, 2010 at 2:38 pm

#1261341

Viewing 15 posts - 35,716 through 35,730 (of 59,099 total)