Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Really confused newbie - Perhaps a Temp Table query? Expand / Collapse
Author
Message
Posted Tuesday, March 5, 2013 5:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:39 AM
Points: 47, Visits: 133
Temp tables are created just like a persistent table- the difference is you do not typically specify the schema and you put a # before the name, such as:

CREATE TABLE #MyTempTable (id int, firstName varchar(100) NULL, lastName varchar(200) NOT NULL)

INSERT INTO #MyTempTable ...

SELECT ...
FROM #MyTempTable ...

Sometimes you just want to "dump" the results of a query into a temp table (though I would not do this in production code- and this only works if the temporary table doesn't already exist):

SELECT {some stuff}
INTO #MyTemporaryTable
FROM {some things}
...

The temp table will exist for the duration of the connection (and can only be accessed by the connection that created the temp table). As soon as you disconnect, the temp table is effectively gone. The temp table is actually a real table that is created in the system db "tempdb".

If you precede the name with ## instead of #, you will create a global temporary table. A global temporary table will exist until the server is restarted (or the table is dropped). A global temporary table can be accessed from any connection.


An alternative is a table-type variable. These are declared like variables and exist for the duration of the BATCH (slightly different than temp tables that exist for the duration of the connection).

DECLARE @MyTableTypeVariable TABLE (id int, firstName varchar(100) NULL, lastName varchar(200) NOT NULL)

INSERT INTO @MyTableTypeVariable...

SELECT...
FROM @MyTableTypeVariable...

Whenever possible, I use table type variables (especially in production code) because, true or not, I have the impression that it requires the least amount of overhead on the server.

Here's an article that goes into much more depth:

https://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/



I wonder if what you really want is a VIEW? A view is a logical construct (does not contain any data). It's basically a query that you can refer to as if it is a table.

CREATE VIEW dbo.SalesSummary
AS
SELECT {some complex query}
GO

Then at some later point (a year from now, for instance) you could refer to the view:

SELECT *
FROM dbo.SalesSummary

Post #1427097
Posted Tuesday, March 5, 2013 6:24 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:47 PM
Points: 3,648, Visits: 5,318
Steven Willis (3/5/2013)
Another old habit to break if you are using SQL2008 or greater is to avoid GROUP BY when all you really need is an aggregated column value.


SELECT
A.Portfolio
,B.PortfolioID
,SUM(B.MarketValue) AS SumOfMV
,COUNT(B.SecID) AS [# of Securities]
FROM
ERTutAccounts A
INNER JOIN ERTutPositions B
ON A.PortfolioID = B.PortfolioID
GROUP BY
ERTutAccounts.Portfolio
,ERTutPositions.PortfolioID;



SELECT
A.Portfolio
,B.PortfolioID
,SUM(B.MarketValue) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS SumOfMV
,COUNT(B.SecID) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS [# of Securities]
FROM
ERTutAccounts A
INNER JOIN ERTutPositions B
ON A.PortfolioID = B.PortfolioID


You can still use GROUP BY of course if you need to group the portfolios themselves but just to do sums or counts (or min or max, etc) the OVER() clause makes things a lot easier.

 
 


Steven,

Your first query needed a little correction (namely to the stuff in bold) to make it run (minor).

But my big question is why your are making the suggestion to unlearn an old habit here when the two queries produce different results. Knocking up some test data:

CREATE TABLE #ERTutAccounts
(PortfolioID INT IDENTITY(1,1), Portfolio VARCHAR(10))

INSERT INTO #ERTutAccounts
SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'

CREATE TABLE #ERTutPositions
(PortfolioID INT, MarketValue MONEY, SecID INT)

INSERT INTO #ERTutPositions
SELECT 1, 50000, 1 UNION ALL SELECT 1, 100000, 2 UNION ALL SELECT 1, 150000, 3
UNION ALL SELECT 2, 30000, 1 UNION ALL SELECT 2, 175000, 2 UNION ALL SELECT 2, 250000, 3
UNION ALL SELECT 3, 40000, 1 UNION ALL SELECT 3, 150000, 2 UNION ALL SELECT 3, 250000, 3

SELECT
A.Portfolio
,A.PortfolioID
,SUM(B.MarketValue) AS SumOfMV
,COUNT(B.SecID) AS [# of Securities]
FROM
#ERTutAccounts A
INNER JOIN #ERTutPositions B
ON A.PortfolioID = B.PortfolioID
GROUP BY
A.Portfolio
,A.PortfolioID;

SELECT
A.Portfolio
,B.PortfolioID
,SUM(B.MarketValue) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS SumOfMV
,COUNT(B.SecID) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS [# of Securities]
FROM
#ERTutAccounts A
INNER JOIN #ERTutPositions B
ON A.PortfolioID = B.PortfolioID

DECLARE @NumPortfolios INT = 100000
-- Performance test (add some rows)
;WITH Tally (n) AS (
SELECT TOP (@NumPortfolios) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT INTO #ERTutAccounts
SELECT Portfolio + CAST(n AS VARCHAR(10))
FROM #ERTutAccounts
CROSS APPLY Tally

;WITH Tally (n) AS (
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT INTO #ERTutPositions
SELECT 1 + ABS(CHECKSUM(NEWID())) % (3*@NumPortfolios+3)
,20000 + ABS(CHECKSUM(NEWID())) % 250000
,1 + ABS(CHECKSUM(NEWID())) % 500
FROM Tally

DECLARE @Holder1 VARCHAR(10)
,@Holder2 INT
,@Holder3 MONEY
,@Holder4 INT

PRINT 'GROUP BY'
SET STATISTICS TIME ON
SELECT
@Holder1=A.Portfolio
,@Holder2=A.PortfolioID
,@Holder3=SUM(B.MarketValue) -- AS SumOfMV
,@Holder4=COUNT(B.SecID) -- AS [# of Securities]
FROM
#ERTutAccounts A
INNER JOIN #ERTutPositions B
ON A.PortfolioID = B.PortfolioID
GROUP BY
A.Portfolio
,A.PortfolioID;
SET STATISTICS TIME OFF

PRINT 'WINDOW AGGREGATE W-DISTINCT'
SET STATISTICS TIME ON
SELECT DISTINCT
@Holder1=A.Portfolio
,@Holder2=B.PortfolioID
,@Holder3=SUM(B.MarketValue) OVER (PARTITION BY A.Portfolio, B.PortfolioID) -- AS SumOfMV
,@Holder4=COUNT(B.SecID) OVER (PARTITION BY A.Portfolio, B.PortfolioID) -- AS [# of Securities]
FROM
#ERTutAccounts A
INNER JOIN #ERTutPositions B
ON A.PortfolioID = B.PortfolioID
SET STATISTICS TIME OFF

DROP TABLE #ERTutAccounts
DROP TABLE #ERTutPositions


I'm thinking that the first result is what the OP was looking for. To make them identical, you'd need to change the second query to SELECT DISTINCT. Or you could do as you suggest which is to add GROUP BY, in which case you'd need to aggregate (using MAX or MIN) the window aggregates, but I don't think you can do that (at least not in this case).

My main intention by looking at this was that I have heard that some of the window aggregates don't perform as well as the corresponding GROUP BY notation (and I believe I've seen it proven at least once). So I thought I'd give that a try (also in the SQL above). The results I got were:

GROUP BY
SQL Server Execution Times:
CPU time = 483 ms, elapsed time = 217 ms.

WINDOW AGGREGATE W-DISTINCT
SQL Server Execution Times:
CPU time = 2151 ms, elapsed time = 774 ms



Which arguably might be at least partially due to adding DISTINCT, or maybe not. Whatever the reason, I think they're sufficiently different to take notice.

Edit: Oooh! Just noticed another case of parallelism introduced by SQL Server into a query for my study on parallelism!



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!
Post #1427108
Posted Tuesday, March 5, 2013 8:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:17 PM
Points: 23,394, Visits: 32,212
dwain.c (3/5/2013)
Steven Willis (3/5/2013)
Another old habit to break if you are using SQL2008 or greater is to avoid GROUP BY when all you really need is an aggregated column value.


SELECT
A.Portfolio
,B.PortfolioID
,SUM(B.MarketValue) AS SumOfMV
,COUNT(B.SecID) AS [# of Securities]
FROM
ERTutAccounts A
INNER JOIN ERTutPositions B
ON A.PortfolioID = B.PortfolioID
GROUP BY
ERTutAccounts.Portfolio
,ERTutPositions.PortfolioID;



SELECT
A.Portfolio
,B.PortfolioID
,SUM(B.MarketValue) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS SumOfMV
,COUNT(B.SecID) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS [# of Securities]
FROM
ERTutAccounts A
INNER JOIN ERTutPositions B
ON A.PortfolioID = B.PortfolioID


You can still use GROUP BY of course if you need to group the portfolios themselves but just to do sums or counts (or min or max, etc) the OVER() clause makes things a lot easier.

 
 


Steven,

Your first query needed a little correction (namely to the stuff in bold) to make it run (minor).

But my big question is why your are making the suggestion to unlearn an old habit here when the two queries produce different results. Knocking up some test data:

CREATE TABLE #ERTutAccounts
(PortfolioID INT IDENTITY(1,1), Portfolio VARCHAR(10))

INSERT INTO #ERTutAccounts
SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'

CREATE TABLE #ERTutPositions
(PortfolioID INT, MarketValue MONEY, SecID INT)

INSERT INTO #ERTutPositions
SELECT 1, 50000, 1 UNION ALL SELECT 1, 100000, 2 UNION ALL SELECT 1, 150000, 3
UNION ALL SELECT 2, 30000, 1 UNION ALL SELECT 2, 175000, 2 UNION ALL SELECT 2, 250000, 3
UNION ALL SELECT 3, 40000, 1 UNION ALL SELECT 3, 150000, 2 UNION ALL SELECT 3, 250000, 3

SELECT
A.Portfolio
,A.PortfolioID
,SUM(B.MarketValue) AS SumOfMV
,COUNT(B.SecID) AS [# of Securities]
FROM
#ERTutAccounts A
INNER JOIN #ERTutPositions B
ON A.PortfolioID = B.PortfolioID
GROUP BY
A.Portfolio
,A.PortfolioID;

SELECT
A.Portfolio
,B.PortfolioID
,SUM(B.MarketValue) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS SumOfMV
,COUNT(B.SecID) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS [# of Securities]
FROM
#ERTutAccounts A
INNER JOIN #ERTutPositions B
ON A.PortfolioID = B.PortfolioID

DECLARE @NumPortfolios INT = 100000
-- Performance test (add some rows)
;WITH Tally (n) AS (
SELECT TOP (@NumPortfolios) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT INTO #ERTutAccounts
SELECT Portfolio + CAST(n AS VARCHAR(10))
FROM #ERTutAccounts
CROSS APPLY Tally

;WITH Tally (n) AS (
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT INTO #ERTutPositions
SELECT 1 + ABS(CHECKSUM(NEWID())) % (3*@NumPortfolios+3)
,20000 + ABS(CHECKSUM(NEWID())) % 250000
,1 + ABS(CHECKSUM(NEWID())) % 500
FROM Tally

DECLARE @Holder1 VARCHAR(10)
,@Holder2 INT
,@Holder3 MONEY
,@Holder4 INT

PRINT 'GROUP BY'
SET STATISTICS TIME ON
SELECT
@Holder1=A.Portfolio
,@Holder2=A.PortfolioID
,@Holder3=SUM(B.MarketValue) -- AS SumOfMV
,@Holder4=COUNT(B.SecID) -- AS [# of Securities]
FROM
#ERTutAccounts A
INNER JOIN #ERTutPositions B
ON A.PortfolioID = B.PortfolioID
GROUP BY
A.Portfolio
,A.PortfolioID;
SET STATISTICS TIME OFF

PRINT 'WINDOW AGGREGATE W-DISTINCT'
SET STATISTICS TIME ON
SELECT DISTINCT
@Holder1=A.Portfolio
,@Holder2=B.PortfolioID
,@Holder3=SUM(B.MarketValue) OVER (PARTITION BY A.Portfolio, B.PortfolioID) -- AS SumOfMV
,@Holder4=COUNT(B.SecID) OVER (PARTITION BY A.Portfolio, B.PortfolioID) -- AS [# of Securities]
FROM
#ERTutAccounts A
INNER JOIN #ERTutPositions B
ON A.PortfolioID = B.PortfolioID
SET STATISTICS TIME OFF

DROP TABLE #ERTutAccounts
DROP TABLE #ERTutPositions


I'm thinking that the first result is what the OP was looking for. To make them identical, you'd need to change the second query to SELECT DISTINCT. Or you could do as you suggest which is to add GROUP BY, in which case you'd need to aggregate (using MAX or MIN) the window aggregates, but I don't think you can do that (at least not in this case).

My main intention by looking at this was that I have heard that some of the window aggregates don't perform as well as the corresponding GROUP BY notation (and I believe I've seen it proven at least once). So I thought I'd give that a try (also in the SQL above). The results I got were:

GROUP BY
SQL Server Execution Times:
CPU time = 483 ms, elapsed time = 217 ms.

WINDOW AGGREGATE W-DISTINCT
SQL Server Execution Times:
CPU time = 2151 ms, elapsed time = 774 ms



Which arguably might be at least partially due to adding DISTINCT, or maybe not. Whatever the reason, I think they're sufficiently different to take notice.

Edit: Oooh! Just noticed another case of parallelism introduced by SQL Server into a query for my study on parallelism!


You know Dwain, now if we could just get you to use the semicolon as a terminator instead of a begininator!



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)
Post #1427137
Posted Tuesday, March 5, 2013 8:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:47 PM
Points: 3,648, Visits: 5,318
Lynn Pettis (3/5/2013)

You know Dwain, now if we could just get you to use the semicolon as a terminator instead of a begininator!


Lynn - I'm 100% with you on that but old habits die hard. I'm at least trying to do it now when I write an article. Making it an everyday practice is a real challenge.



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!
Post #1427147
Posted Tuesday, March 5, 2013 9:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:17 PM
Points: 23,394, Visits: 32,212
dwain.c (3/5/2013)
Lynn Pettis (3/5/2013)

You know Dwain, now if we could just get you to use the semicolon as a terminator instead of a begininator!


Lynn - I'm 100% with you on that but old habits die hard. I'm at least trying to do it now when I write an article. Making it an everyday practice is a real challenge.


Coming from an old COBOL environment with periods at the end of statements, it wasn't too hard to put semicolons at the end of statements in SQL for me.

I just think this looks really weird:


;WITH SomeCte as (
select ...
)
MERGE
...;






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)
Post #1427151
Posted Wednesday, March 6, 2013 1:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:47 PM
Points: 3,648, Visits: 5,318
Lynn Pettis (3/5/2013)
dwain.c (3/5/2013)
Lynn Pettis (3/5/2013)

You know Dwain, now if we could just get you to use the semicolon as a terminator instead of a begininator!


Lynn - I'm 100% with you on that but old habits die hard. I'm at least trying to do it now when I write an article. Making it an everyday practice is a real challenge.


Coming from an old COBOL environment with periods at the end of statements, it wasn't too hard to put semicolons at the end of statements in SQL for me.

I just think this looks really weird:


;WITH SomeCte as (
select ...
)
MERGE
...;





PL/1 for me actually and it required semicolons at the end of every statement.

But that's been too long now to confess to.



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!
Post #1427233
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse