ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,916 through 3,930 (of 10,143 total)

RE: Help with SQL Query

Probably the best tool for this is a dynamic cross-tab. Jeff Moden has an excellent article here[/url]. If after reading the article you are still having difficulty, post back.
^{“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

December 4, 2013 at 6:43 am

#1670696
RE: Are the posted questions getting worse?

dwain.c (12/3/2013)
Steve,
I thought you might want to see this:
http://www.sqlservercentral.com/Forums/FindPost1519227.aspx
By alerting you, I am not vouching for its veracity.
Dwain, I thought some of that code looked familiar - didn't we mess...
^{“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

December 4, 2013 at 6:23 am

#1670690
RE: Core T-SQL

Steve Jones - SSC Editor (12/2/2013)
L' Eomot Inversé (11/24/2013)
I think the idea of "Core TSQL" isn't unreasonable, but no set of knowlege which is just about T-SQL and not about...
^{“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

December 3, 2013 at 2:20 am

#1670293
RE: Identify Duplicate Records according to Multiple Criteria

Here's the simplest way. Without further information it's unlikely to be the best.
-- I have an Employee table with Columns:
DROP TABLE #Employee
CREATE TABLE #Employee (EmpID INT IDENTITY(1,1), FirstName VARCHAR(100), MiddleName...
^{“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

December 2, 2013 at 6:59 am

#1670107
RE: Find out if the whole column of data in a table is empty - dynamic sql creation

This is likely to be an expensive process whatever you do. What's the purpose?
^{“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

December 2, 2013 at 6:43 am

#1670099
RE: Working of the function

Jeff Moden (11/29/2013)
Not that many people would notice but you can get the code to be almost a third faster by eliminating the (-1) subtraction. Last but not least,...
^{“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

December 2, 2013 at 2:57 am

#1670037
RE: Core T-SQL

Jeff Moden (12/1/2013)
ChrisM@Work (11/26/2013)
Jeff Moden (11/25/2013)
rlortega (11/25/2013)
So what minimum T-SQL skills are necessary if you're a DBA, not a developer?
To be honest... learning to read minds and to anticipate "what's...
^{“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

December 2, 2013 at 1:53 am

#1670024
RE: Working of the function

Note that you don't need CTE N6:
DECLARE @startdate DATE, @enddate DATE
SET @startdate = '00010101'
SET @enddate = '99990101';
with
N0 as (SELECT 1 as n UNION ALL SELECT 1) -- 2...
^{“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

November 29, 2013 at 7:09 am

#1669834
RE: Working of the function

Run the following code, query by query. If you're still unsure, post back.
WITH
N0 as (SELECT 1 as n UNION ALL SELECT 1) -- 2 rows
,N1 as (SELECT 1...
^{“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

November 29, 2013 at 7:07 am

#1669832
RE: Core T-SQL

Jeff Moden (11/26/2013)
As a bit of a sidebar, I especially like the one about "make it work, make it fast, make it pretty" except you left off the 4th item...
^{“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

November 28, 2013 at 2:02 am

#1669634
RE: Core T-SQL

Jeff Moden (11/25/2013)
rlortega (11/25/2013)
So what minimum T-SQL skills are necessary if you're a DBA, not a developer?
To be honest... learning to read minds and to anticipate "what's coming" by keeping...
^{“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

November 26, 2013 at 6:35 am

#1669121
RE: Recursive CTE

If your hierarchy is likely to remain consistent then this may be more efficient than a rCTE:
DROP TABLE #Entity
CREATE TABLE #Entity (EntityId INT, EntityTypeId INT, Description VARCHAR(100), ParentEntityId INT)
INSERT INTO...
^{“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

November 25, 2013 at 7:10 am

#1668788
RE: Rearrange Result of Data with TSQL Script

Lynn Pettis (11/20/2013)
sunil.mvs (11/20/2013)
Thanks Chris ...
The solution is as expected.. but need to check performance .
Thanks
Surya Sunil
You should also make sure you understand how...
^{“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

November 21, 2013 at 6:27 am

#1668092
RE: Rearrange Result of Data with TSQL Script

Here's a simple islands'n'gaps solution as suggested by Koen:
DROP table #temp
CREATE table #temp (SysKey int ,BusName varchar(9) ,BusType varchar(6) ,StartDateKey int ,EndDatekey int )
Insert into #temp (SysKey ,BusName,BusType,StartDateKey,EndDatekey)
Select...
^{“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

November 20, 2013 at 6:58 am

#1667788
RE: split performance

sathiyan00 (10/8/2013)
but even this is also taking more time for 25K records.
with Improved SQL 8K “CSV Splitter” Function its taking around 25 sec
with xml its taking around 30sec
with substring its...
^{“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

October 8, 2013 at 8:09 am

#1656754

Viewing 15 posts - 3,916 through 3,930 (of 10,143 total)