March 5, 2013 at 8:58 am
Hi Guys,
I'll basically do my best to describe what I'm trying to accomplish, and give you the query I have so far (which isn't correct).
I have several different tables. In this case, three: ERTutAccounts, ERTutPositions, and ERTutSecMast.
What I'm trying to accomplish is a table where one result set lives. Within this table I'd ideally like to have:
Portfolio | PortfolioID | # Of Securities | Total Market Value of Portfolio
I've gotten as far as Column 1, Column 2, and Column 4, but am unable to add the 3rd column (counting the # of securities in each portfolio).
My query is as follows:
SELECT ERTutAccounts.Portfolio, ERTutPositions.PortfolioID, SUM(ERTutPositions.MarketValue) AS SumOfMV
From ERTutAccounts, ERTutPositions
WHERE ERTutPositions.PortfolioID=ERTutAccounts.PortfolioID
GROUP BY ERTutAccounts.Portfolio, ERTutPositions.PortfolioID
This query above does the job, but when I add in the Securities...
SELECT ERTutAccounts.Portfolio, ERTutPositions.PortfolioID, SUM(ERTutPositions.MarketValue) AS SumOfMV, ERTutSecMast.SecID AS '# of Securities'
From ERTutAccounts, ERTutPositions, ERTutSecMast
WHERE ERTutPositions.PortfolioID=ERTutAccounts.PortfolioID
AND ERTutPositions.SecID=ERTutSecMast.SecID
GROUP BY ERTutAccounts.Portfolio, ERTutPositions.PortfolioID, ERTutSecMast.SecID
When I do this, it will list each SecID individually and repeat the PortfolioID over and over again.
I understand this is confusing, and I attempted to make it concise as possible. As you can tell, I'm an extreme newbie who has been using SQL for about three days now. Any input is appreciated!
March 5, 2013 at 9:16 am
meadow0 (3/5/2013)
Hi Guys,I'll basically do my best to describe what I'm trying to accomplish, and give you the query I have so far (which isn't correct).
I have several different tables. In this case, three: ERTutAccounts, ERTutPositions, and ERTutSecMast.
What I'm trying to accomplish is a table where one result set lives. Within this table I'd ideally like to have:
Portfolio | PortfolioID | # Of Securities | Total Market Value of Portfolio
I've gotten as far as Column 1, Column 2, and Column 4, but am unable to add the 3rd column (counting the # of securities in each portfolio).
My query is as follows:
SELECT ERTutAccounts.Portfolio, ERTutPositions.PortfolioID, SUM(ERTutPositions.MarketValue) AS SumOfMV
From ERTutAccounts, ERTutPositions
WHERE ERTutPositions.PortfolioID=ERTutAccounts.PortfolioID
GROUP BY ERTutAccounts.Portfolio, ERTutPositions.PortfolioID
This query above does the job, but when I add in the Securities...
SELECT ERTutAccounts.Portfolio, ERTutPositions.PortfolioID, SUM(ERTutPositions.MarketValue) AS SumOfMV, ERTutSecMast.SecID AS '# of Securities'
From ERTutAccounts, ERTutPositions, ERTutSecMast
WHERE ERTutPositions.PortfolioID=ERTutAccounts.PortfolioID
AND ERTutPositions.SecID=ERTutSecMast.SecID
GROUP BY ERTutAccounts.Portfolio, ERTutPositions.PortfolioID, ERTutSecMast.SecID
When I do this, it will list each SecID individually and repeat the PortfolioID over and over again.
I understand this is confusing, and I attempted to make it concise as possible. As you can tell, I'm an extreme newbie who has been using SQL for about three days now. Any input is appreciated!
You should get out of the habit of using the old style join syntax and instead use the new syntax.
Here's a total guess for what you want, as you haven't supplied sample data or DDL (see link in my signature about the best way to ask a question) it's hard to be sure.
SELECT A.Portfolio, B.PortfolioID, SUM(B.MarketValue) AS SumOfMV,
[# of Securities]
From ERTutAccounts A
INNER JOIN ERTutPositions B ON A.PortfolioID = B.PortfolioID
CROSS APPLY (SELECT COUNT(*)
FROM ERTutSecMast
WHERE B.SecID = SecID) C([# of Securities])
GROUP BY ERTutAccounts.Portfolio, ERTutPositions.PortfolioID;
Edited: Thinking about it, you probably don't need that third table. Try this: -
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;
March 5, 2013 at 9:44 am
Edited: Thinking about it, you probably don't need that third table. Try this: -
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;[/quote]
This one worked!! Only issue was with the GROUP BY Query. I had to switch the ERTutAccounts to A.Portfolio and ERTutPositions to B.PortfolioID
March 5, 2013 at 9:47 am
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.
March 5, 2013 at 10:02 am
Thanks for all the help so far.
Initially I thought once I had the query set up, it'd be easy to put a WHERE clause in (for this purpose I'm attempting to limit to [# of Securities] > 70 OR SumOfMV > 100
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
WHERE COUNT(B.SecID) > 70 OR SUM(B.MarketValue) > 100
GROUP BY A.Portfolio, B.PortfolioID
The error is as follows:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
I would like to learn this stuff, because I am going to eventually be using it everyday. Is there a good place to start?
March 5, 2013 at 10:05 am
Eh, That was a really easy fix. Sorry for the trivial question. Thanks guys!
March 5, 2013 at 10:07 am
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....
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.
Out of curiosity, why avoid GROUP BY? Do you suggest this only because it is "easier"?
March 5, 2013 at 11:26 am
calvo (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....
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.
Out of curiosity, why avoid GROUP BY? Do you suggest this only because it is "easier"?
Well, yes, to a degree I find it easier to maintain and more readable if it will do the job.
But sometimes you may want to SUM on one grouping and COUNT on a different grouping. You can do that using the OVER() clause but it would get complicated when the only grouping tool available is GROUP BY.
How many times have you received the error message: "Column 'dbo.TableName.ColName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." :crazy:
March 5, 2013 at 11:33 am
Can anyone point me to good literature on learning how to do 'Temp Tables' and the like.
Basically I need to create this same exact table with the same exact results, but rather than a query, a temp table.
I can't seem to find any useful information online. Any help is appreciated, thanks!
March 5, 2013 at 11:37 am
Steven Willis (3/5/2013)
calvo (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....
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.
Out of curiosity, why avoid GROUP BY? Do you suggest this only because it is "easier"?
Well, yes, to a degree I find it easier to maintain and more readable if it will do the job.
But sometimes you may want to SUM on one grouping and COUNT on a different grouping. You can do that using the OVER() clause but it would get complicated when the only grouping tool available is GROUP BY.
How many times have you received the error message: "Column 'dbo.TableName.ColName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." :crazy:
The only time I've used an OVER() clause is with ROW_NUMBER. I can't wait to give it a shot with aggregates, I was totally unaware that it could be used with them.
Hey, learned something new!
March 5, 2013 at 5:51 pm
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
March 5, 2013 at 6:24 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 5, 2013 at 8:24 pm
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! 😛
March 5, 2013 at 8:48 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 5, 2013 at 9:04 pm
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
...;
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply