Viewing 15 posts - 1,126 through 1,140 (of 1,228 total)
Paul White (2/27/2010)
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...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 27, 2010 at 8:15 am
Paul White (2/27/2010)
It is certainly orders of magnitude faster than a recursive CTE on large sets
Hi Paul, an excellent solution as always, and a method to remember.
There are a...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 27, 2010 at 4:36 am
Jeff Moden (2/26/2010)
nathan 7372 (2/26/2010)
Thank you for pointing out proper posting etiquette. I had not seen that article before and I apologize.
Absolutely no problem and no need to apologize....
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 26, 2010 at 3:34 pm
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],...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 26, 2010 at 2:51 pm
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...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 26, 2010 at 1:57 pm
About 450ms on this lappy.
Cheers
ChrisM
Junior Software Engineer
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 26, 2010 at 12:44 pm
@pam:
sys.databases.[name]
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 25, 2010 at 2:54 pm
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, DATEDIFF(mm,0,getdate()), 0))
select @enddate...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 25, 2010 at 2:43 pm
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...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 25, 2010 at 2:18 pm
Steve Barlow-144765 (2/23/2010)
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...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 23, 2010 at 3:36 pm
klineandking (2/22/2010)
declare @data_refresh datetime
set @data_refresh=convert(datetime,convert(varchar(10),getdate(),101))
select...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 23, 2010 at 3:18 pm
Surely...ORDER BY NEWID()
would be faster than
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), k.ID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 22, 2010 at 2:40 pm
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...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 22, 2010 at 2:14 pm
Ray K (2/22/2010)
I have it taped up on my desk now! 😀
There's something of the night about you, sir.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 22, 2010 at 1:05 pm
Paul White (2/21/2010)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 21, 2010 at 3:55 am
Viewing 15 posts - 1,126 through 1,140 (of 1,228 total)