Jeff Moden

Forum Replies Created

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

RE: How do I know when to use temp tables/table variables?

Tom.Thomson (12/13/2010)
Junglee_George (12/2/2010)
Hi
Please have a look into this article also
http://www.mssqltips.com/tip.asp?tip=1556
Thanks
Rather a dangerous article with false statements like
# Table variables can not have Non-Clustered Indexes
and
# You can not create constraints...
--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 14, 2010 at 7:31 am

#1262210
RE: How do I know when to use temp tables/table variables?

SQLNW (12/13/2010)
hi everyone ,request your help urgently.
Conversion failed when converting the varchar value '12.80' to data type int. I checked all the datatypes in my procedure
thanks for your help.
That's...
--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 14, 2010 at 7:27 am

#1262206
RE: Unable to modify table. Timeout expired. The timeout period elapsed prior

shyam-792781 (12/7/2010)
Hi Rich Script is not working in my Case it is taking too much in the DROP TABLE dbo.tableName
I have backup for the table before deleting main table
Thanks
Shyam
What do...
--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:20 pm

#1261498
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

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