SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


query help


query help

Author
Message
yogi123
yogi123
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 394
hello friends

I need one request,

here is DLL


create table #temp (Student_ID char(9),
Sex char(1),
RaceCode varchar(5),
Lunch int,
Student_ESL int,
GIEP int,
IEP int,
SchoolCode int)

Insert into #temp values ('004242726','F','Black',1,0,0,0,204)
Insert into #temp values ('004242734','F','Black',0,1,0,0,356)
Insert into #temp values ('004242777','M','White',1,0,0,0,141)
Insert into #temp values ('004242793','F','Black',1,0,0,1,164)
Insert into #temp values ('004242831','M','White',0,0,0,1,141)
Insert into #temp values ('004242866','M','White',1,0,0,0,204)
Insert into #temp values ('004242882','M','White',0,1,0,0,141)
Insert into #temp values ('004242890','F','Black',0,0,0,0,204)
Insert into #temp values ('004242971','F','Black',0,0,0,1,164)
Insert into #temp values ('004243129','F','Black',0,0,0,1,204)
Insert into #temp values ('004243137','M','Black',0,0,0,0,164)
Insert into #temp values ('004243188','M','Black',1,0,0,0,204)
Insert into #temp values ('004243196','F','Other',1,1,0,0,204)
Insert into #temp values ('004243285','M','Other',0,0,0,1,204)
Insert into #temp values ('004243293','F','Other',0,0,0,1,164)
Insert into #temp values ('004243323','M','Other',0,0,0,1,204)
Insert into #temp values ('004243447','F','Black',1,1,0,0,204)
Insert into #temp values ('004243455','M','Black',0,0,0,0,164)



so the desired output should be

if we filter based on schoolcode for example 204 then output should be



BLACK WHITE OTHER
MALE 1 1 2
FEMALE 4 0 1
IEP 1 0 2
GIEP 0 0 0
LUNCH 3 1 1
ESL 1 0 1



so i need to count all my column data like sex,IEP,GIEP,LUNCH,ESL based on race code.
for IEP,GIEP,LUNCH,ESL, when the value = 1 then we need to count else not count.

I am thinking to do with pivoting and do union but if is there any easy way to solve this please let me know.
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6670 Visits: 7394
Not certain why you'd want to do this type of thing in SQL instead of Reporting Services or Excel (but I'm sure there's reasons ;-)) - here's a great article that will point you in the proper direction

http://www.sqlservercentral.com/articles/Stairway+Series/87629/

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
subbu1
subbu1
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1049 Visits: 695
pls try below code

select case when Sex='M' THEN 'MALE' WHEN Sex='F' THEN 'FEMALE' END COLUMN_H,COUNT(CASE WHEN RaceCode='Black' THEN Sex END) AS Black,
COUNT(CASE WHEN RaceCode='White' THEN Sex END) AS White,
COUNT(CASE WHEN RaceCode='Other' THEN Sex END) AS Other from #temp
where SchoolCode='204'
GROUP BY (case when Sex='M' THEN 'MALE' WHEN Sex='F' THEN 'FEMALE' END)
UNION
select emp,Black,White,Other from
(SELECT RaceCode,emp,case when ord=1 then ord end ord FROM
(SELECT RaceCode,Student_ESL as ESL,GIEP,IEP,Lunch FROM #temp where SchoolCode='204')K
UNPIVOT
(ord FOR emp IN (ESL,GIEP,IEP,Lunch)
) AS pvt)k
pivot
(count(ord) FOR RaceCode IN (Black,White,Other))pvt
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7273 Visits: 6431
You can try something like this, although I don't get the same counts as you do:


;WITH Unpivoted AS (
SELECT RaceCode, Code, Value
FROM #Temp
CROSS APPLY (
VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)
) a (Code, Value)
)
SELECT Category=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END)
,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)
,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)
,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)
FROM #Temp
GROUP BY Sex
UNION ALL
SELECT Code
,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)
,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)
,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)
FROM Unpivoted
GROUP BY Code




I count 9 males and 9 females in your data and my query above returns this.

Note that I've used the "Other UNPIVOT" because it is usually faster: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39295 Visits: 38529
MyDoggieJessie (8/7/2012)
Not certain why you'd want to do this type of thing in SQL instead of Reporting Services or Excel (but I'm sure there's reasons ;-)) - here's a great article that will point you in the proper direction

http://www.sqlservercentral.com/articles/Stairway+Series/87629/


Let's make this easier for others, no cut and paste required:

http://www.sqlservercentral.com/articles/Stairway+Series/87629/

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39295 Visits: 38529
dwain.c (8/8/2012)
You can try something like this, although I don't get the same counts as you do:


;WITH Unpivoted AS (
SELECT RaceCode, Code, Value
FROM #Temp
CROSS APPLY (
VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)
) a (Code, Value)
)
SELECT Category=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END)
,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)
,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)
,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)
FROM #Temp
GROUP BY Sex
UNION ALL
SELECT Code
,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)
,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)
,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)
FROM Unpivoted
GROUP BY Code




I count 9 males and 9 females in your data and my query above returns this.

Note that I've used the "Other UNPIVOT" because it is usually faster: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/


Because the counts shown by the OP were for a specific school code, not all the records.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7273 Visits: 6431
Thanks Lynn! Apparently if I could read, I would've come up with this version instead:


;WITH Unpivoted AS (
SELECT SchoolCode, RaceCode, Code, Value
FROM #Temp
CROSS APPLY (
VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)
) a (Code, Value)
)
SELECT Sex=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END
,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)
,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)
,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)
FROM #Temp
WHERE SchoolCode = 204
GROUP BY Sex
UNION ALL
SELECT Code
,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)
,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)
,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)
FROM Unpivoted
WHERE SchoolCode = 204
GROUP BY Code





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3894 Visits: 8472
dwain.c (8/8/2012)
Thanks Lynn! Apparently if I could read, I would've come up with this version instead:


;WITH Unpivoted AS (
SELECT SchoolCode, RaceCode, Code, Value
FROM #Temp
CROSS APPLY (
VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)
) a (Code, Value)
)
SELECT Sex=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END
,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)
,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)
,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)
FROM #Temp
WHERE SchoolCode = 204
GROUP BY Sex
UNION ALL
SELECT Code
,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)
,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)
,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)
FROM Unpivoted
WHERE SchoolCode = 204
GROUP BY Code




Do you not like GROUP BY ROLLUP ?

e.g.
SELECT Code, BLACK, WHITE, OTHER
FROM (SELECT Code,
BLACK=SUM(CASE WHEN RaceCode = 'Black' AND Value = 1 THEN 1 ELSE 0 END),
WHITE=SUM(CASE WHEN RaceCode = 'White' AND Value = 1 THEN 1 ELSE 0 END),
OTHER=SUM(CASE WHEN RaceCode = 'Other' AND Value = 1 THEN 1 ELSE 0 END),
MAX(Pos) AS Pos
FROM (SELECT SchoolCode, RaceCode, Code, Value, Pos
FROM #temp
CROSS APPLY (VALUES ('LUNCH', Lunch, 5), ('ESL', Student_ESL, 6), ('GIEP', GIEP, 4), ('IEP', IEP, 3),
('Male', CASE WHEN Sex = 'M' THEN 1 ELSE 0 END, 1),
('Female', CASE WHEN Sex = 'F'THEN 1 ELSE 0 END, 2)
) a(Code, Value, Pos)) a
WHERE SchoolCode = 204
GROUP BY ROLLUP (Code)
) a
WHERE Code IS NOT NULL
ORDER BY Pos;



Results in: -
Code   BLACK       WHITE       OTHER
------ ----------- ----------- -----------
Male 1 1 2
Female 4 0 1
IEP 1 0 2
GIEP 0 0 0
LUNCH 3 1 1
ESL 1 0 1


I've only added the ORDER BY to replicate the ordering that the OP posted.

I'll set up a big performance test in a little while to test the difference, but my gut feeling is that the GROUP BY ROLLUP will be faster.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3894 Visits: 8472
Performance test as promised: -

SET NOCOUNT ON;

SELECT TOP 1000000 Student_ID,
Sex = CASE WHEN sex = 1 THEN 'M' ELSE 'F' END,
RaceCode = CASE race WHEN 0 THEN 'Other' WHEN 1 THEN 'Black' ELSE 'White' END,
Lunch = lunch, Student_ESL = esl, GIEP = giep, IEP = iep,
SchoolCode = schoolcode
INTO #temp
FROM master.dbo.syscolumns sc1
CROSS JOIN master.dbo.syscolumns sc2
CROSS JOIN master.dbo.syscolumns sc3
CROSS APPLY (SELECT DISTINCT LEFT('0' + (CAST((ABS(CHECKSUM(NEWID())) % 999999999) + 1 AS VARCHAR(9))),9)) sc4(Student_ID)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc5(sex)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 3)) sc6(race)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc7(lunch)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc8(esl)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc9(giep)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc10(iep)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 899) + 100) sc11(schoolcode);

CREATE CLUSTERED INDEX PK_Student_ID_temp ON #temp (Student_ID);
CREATE NONCLUSTERED INDEX NC_SchoolCode_temp ON #temp (SchoolCode);

PRINT REPLICATE('=',80);
PRINT 'DWAIN';
PRINT REPLICATE('=',80);
SET STATISTICS IO, TIME ON;

;WITH Unpivoted AS (
SELECT SchoolCode, RaceCode, Code, Value
FROM #temp
CROSS APPLY (
VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)
) a (Code, Value)
)
SELECT Sex=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END
,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)
,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)
,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)
FROM #temp
WHERE SchoolCode = 204
GROUP BY Sex
UNION ALL
SELECT Code
,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)
,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)
,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)
FROM Unpivoted
WHERE SchoolCode = 204
GROUP BY Code

SET STATISTICS IO, TIME OFF;

PRINT REPLICATE('=',80);
PRINT 'CADAVRE';
PRINT REPLICATE('=',80);
SET STATISTICS IO, TIME ON;

SELECT Code, BLACK, WHITE, OTHER
FROM (SELECT Code,
BLACK=SUM(CASE WHEN RaceCode = 'Black' AND Value = 1 THEN 1 ELSE 0 END),
WHITE=SUM(CASE WHEN RaceCode = 'White' AND Value = 1 THEN 1 ELSE 0 END),
OTHER=SUM(CASE WHEN RaceCode = 'Other' AND Value = 1 THEN 1 ELSE 0 END),
MAX(Pos) AS Pos
FROM (SELECT SchoolCode, RaceCode, Code, Value, Pos
FROM #temp
CROSS APPLY (VALUES ('LUNCH', Lunch, 5), ('ESL', Student_ESL, 6), ('GIEP', GIEP, 4), ('IEP', IEP, 3),
('Male', CASE WHEN Sex = 'M' THEN 1 ELSE 0 END, 1),
('Female', CASE WHEN Sex = 'F'THEN 1 ELSE 0 END, 2)
) a(Code, Value, Pos)
) a
WHERE SchoolCode = 204
GROUP BY ROLLUP (Code)
) a
WHERE Code IS NOT NULL
ORDER BY Pos;

SET STATISTICS IO, TIME OFF;



Results: -
================================================================================
DWAIN
================================================================================
Sex BLACK WHITE OTHER
------ ----------- ----------- -----------
FEMALE 197 177 171
MALE 164 199 191
LUNCH 178 177 178
GIEP 193 187 179
IEP 184 189 188
ESL 180 186 178
Warning: Null value is eliminated by an aggregate or other SET operation.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#temp'. Scan count 2, logical reads 6762, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 20 ms.

================================================================================
CADAVRE
================================================================================
Code BLACK WHITE OTHER
------ ----------- ----------- -----------
Male 164 199 191
Female 197 177 171
IEP 184 189 188
GIEP 193 187 179
LUNCH 178 177 178
ESL 180 186 178

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#temp'. Scan count 1, logical reads 3381, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 16 ms.


I'm a bit surprised, seems the results are comparable. So I guess it's down to personal preference.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7273 Visits: 6431
Cadavre (8/8/2012)

Do you not like GROUP BY ROLLUP ?



Actually, I perused the link you provided but couldn't think about how to use ROLLUP that way.

The performance test result is interesting.

Try mine with:

OPTION (MAXDOP 2)



And watch the CPU result level out to yours. I'm still trying to figure that one out.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search