ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,566 through 2,580 (of 10,143 total)

RE: Assign Consecutive Numbers to a block of data

You can probably figure it out from this:
SELECT *, [GroupBy] = TestDate - rn
FROM (
SELECT *, [rn] = ROW_NUMBER() OVER(PARTITION BY TestId ORDER BY TestDate)
FROM tblDatesSequenceTest
) d
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 18, 2015 at 7:32 am

#1784767
RE: Count on table with million data too slow

appdev13 (3/18/2015)
I am having a table with over 120 million rows. I am executing a count query and it is taking about 4 mins to execute. Every 1 second 100...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 18, 2015 at 7:24 am

#1784762
RE: null problem in select query

rexyrexin (3/18/2015)
I got the answer:
select * from mytable
where class='something'
AND ISNULL(xyz,0) = CASE WHEN @abc = 1 THEN 1 ELSE ISNULL(xyz,0) END
Here @abc is the stored procedure parameter .
Thanks all for...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 18, 2015 at 7:09 am

#1784749
RE: Are the posted questions getting worse?

GilaMonster (3/18/2015)
Luis Cazares (3/17/2015)
Can someone help with a crystal ball or interrogation system for this thread?
http://www.sqlservercentral.com/Forums/Topic1668185-391-1.aspx
Sorry, mine's broken, it's only showing clouds.
You're holding it upside down:-P
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 18, 2015 at 3:24 am

#1784685
RE: Try Convert causes change in transaction state

chris.rogers00 (3/16/2015)
Why does this code (below) cause a change in the transaction state?
DECLARE @date DATE,
@string VARCHAR(5)= 'test'
BEGIN TRAN
PRINT XACT_STATE()
BEGIN TRY
-- see if the data I've been given can be cast...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 17, 2015 at 8:08 am

#1784503
RE: Splitting a single column into multiple columns

Then try SUBSTRING(string, startpos, 8000) or calculate the correct length for the third parameter.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 17, 2015 at 7:43 am

#1784492
RE: Splitting a single column into multiple columns

sqlquery29 (3/17/2015)
Thanks andy...
We have like after 40 characters the unit of measure is place in the description field. We have more than one space between the description and unit of...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 17, 2015 at 6:42 am

#1784470
RE: different estimated rows on same index operation?

WolfgangE (3/16/2015)
hi,
thank you for the answer.
I know I could optimize the index, but that's not the point. I'd like to understand why optimizer is estimating the way it does...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 16, 2015 at 10:55 am

#1784293
RE: different estimated rows on same index operation?

Try these indexes:
CREATE INDEX ix_MasterId_Etc ON DetailTable (MasterId, CreateDate) INCLUDE (DetailId)
CREATE INDEX ix_MasterId_Etc ON MasterTable (Name, MasterId)
These are a first guess based on the join columns, filters and output...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 16, 2015 at 10:05 am

#1784280
RE: Need non blank,non null value from table to update same table.

APPLY has been a part of TSQL since 2005. Read more here:
http://www.sqlservercentral.com/articles/APPLY/69953/%5B/url%5D
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 13, 2015 at 10:13 am

#1783942
RE: Need non blank,non null value from table to update same table.

-- Check
WITH SequencedData AS (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY emp ORDER BY rdate DESC)
FROM #sample
)
SELECT *
FROM SequencedData s
CROSS APPLY (SELECT TOP 1 * FROM #sample si...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 13, 2015 at 9:37 am

#1783930
RE: Dynamic SQL Truncating at 4K

measterbro (3/13/2015)
i split my query into ~4K pieces and assigned each piece to a variable. Then i assigned them to one, and executed it.
SET @DynamicPivotQuery = @part1 + @part2 +...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 13, 2015 at 7:54 am

#1783881
RE: Dynamic SQL Truncating at 4K

measterbro (3/12/2015)
Unfortunately I am not able to post data or DDL since it is proprietary. I just thought there might be a known problem with dynamic SQL and appending variables,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 13, 2015 at 7:46 am

#1783875
RE: Dynamic SQL Truncating at 4K

measterbro (3/13/2015)
Your GamesWon definition gives these errors:
Msg 207, Level 16, State 1, Line 15
Invalid column name 'LeagueName'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'ShortName'.
Msg 207, Level 16,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 13, 2015 at 7:43 am

#1783869
RE: Query to find "games behind" in a sports table

aaron.reese (11/14/2014)
...VARCHAR / NVARCHAR - who cares on a small adatabase that is only ever going to be used with Latin CP1 collation unless the number of records is going...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

March 13, 2015 at 7:36 am

#1783862

Viewing 15 posts - 2,566 through 2,580 (of 10,143 total)