You're welcome Jeff - it's always interesting to see what surprise you pull out of a hat when you have one...

Paul White (2/27/2010)

---

Chris,

Turns out all that is needed to get rid of the index spool is to define the clustered index as UNIQUE. Given that information, a better plan...

Hi Paul, an excellent solution as always, and a method to remember.

There are a...

Jeff Moden (2/26/2010)

---

Absolutely no problem and no need to apologize....

Hi Nathan

Thanks for posting the sample data and expected results, it doesn't half make a difference. Top work.

Here you go.

;WITH CTEdata AS (

SELECT ExecSeq = ROW_NUMBER() OVER (ORDER BY Dates.[year],...

Have you tried joining to a table containing "random" ID's?

DROP TABLE #Sampler

DECLARE @TableSize INT, @SampleSize INT

SET @TableSize = 1200000

SET @SampleSize = @TableSize/10 -- 10%

SELECT TOP(@SampleSize) SampleID = ABS(CHECKSUM(NEWID()))%@TableSize

INTO #Sampler

FROM...

About 450ms on this lappy.

Cheers

ChrisM

Junior Software Engineer

@pam:

sys.databases.[name]

DECLARE @startdate DATETIME, @enddate DATETIME

DECLARE @sql NVARCHAR(MAX)

SELECT @sql = ISNULL(@sql, '') + 'USE ' + 'name' + '

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

select @startdate = dateadd(mm,-3,DATEADD(mm,...

Have you eyeballed the data? Try this:

SELECT

[Day] = DateName(weekday, LDate),

LID,

Shift1 = CASE WHEN LTime < '16:00' THEN 1 ELSE 0 END,

Shift2 = CASE WHEN...

Steve Barlow-144765 (2/23/2010)

---

Hi,

My client has a piece if SQL he is having trouble with. Here is the SQL:

He wants to return the results of these joins returned as well as...

klineandking (2/22/2010)

---

good afternoon guys am trying to find a better way to rewrite this code, i inherited it from some one else, any help would be welcome

declare @data_refresh datetime

set @data_refresh=convert(datetime,convert(varchar(10),getdate(),101))

select...

Surely...ORDER BY NEWID()

would be faster than

WHERE 0.01 >= CAST(CHECKSUM(NEWID(), k.ID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)

Hi

Firstly, try NULLIF instead of ISNULL to eliminate empty strings and NULLs of ErrorDescription from the output.

Secondly, rewrite the query to eliminate the possibility of more than one row returning...

There's something of the night about you, sir.