August 28, 2015 at 11:20 am
Lynn Pettis (8/28/2015)
mar.ko (8/28/2015)
Lynn Pettis (8/28/2015)
My biggest pet peeve! The semicolon is a terminator, not a begininator. It belongs at the end of statements not the beginning.
Exactly, another dumb rule.
I don't care what anyone says, CTE's are temporary views and should persist for the life of a stored proc.
I am sure Microsoft could have made that possible.
Really? A dumb rule? Let's see, the MERGE statement must be terminated by a semicolon. IIRC, the THROW statement that was recently introduced also requires it be terminated by a semicolon. Looks to me that what once was optional, the semicolon terminating a SQL statement, is starting to be come mandatory. In fact don't be surprised if it does become mandatory. Best thing to do, get used to doing it now.
+1
Microsoft's had that (optional termination with a semicolon) on the deprecated list since 2008. It's just a matter of time, so we'd better get used to it
August 28, 2015 at 11:22 am
Lynn Pettis (8/28/2015)
Lowell (8/28/2015)
the command prior to WITH MyCTE AS(...must be terminated with a semicolon. that's probably where the issue likes.
that issue makes a lot of people precede their [WITH] to feature a semi colon, which is annoying to purists, but it works:
;WITH MyCTE AS(...
My biggest pet peeve! The semicolon is a terminator, not a begininator. It belongs at the end of statements not the beginning.
First, just because it's called a terminator doesn't mean that it actually is a terminator. It obviously doesn't behave the way that you expect a terminator to behave.
Second, it's called a terminator, because it appears at the terminus, which can be either extreme. Terminus, terminal, terminator, and terminate all have the same root, but terminate has come to have an additional directional component that is not present (as strongly) in the other terms.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 28, 2015 at 11:25 am
mar.ko (8/28/2015)
Lynn Pettis (8/28/2015)
My biggest pet peeve! The semicolon is a terminator, not a begininator. It belongs at the end of statements not the beginning.
Exactly, another dumb rule.
I don't care what anyone says, CTE's are temporary views and should persist for the life of a stored proc.
I am sure Microsoft could have made that possible.
To be honest, I wouldn't even have to call them views to think that would be useful, they can compile and hold the definition of a cte for the scope of the following select, so its not like a named expression throughout the life of the sproc is that terribly hard to imagine. Back in my "wouldn't it be nice" days, thats how I thought they could work.
Alas it isn't to be, its only valid for the scope of the following select, insert, update and or delete!
LOL messed that up!
August 28, 2015 at 12:06 pm
GilaMonster (8/28/2015)
mar.ko (8/28/2015)
I don't care what anyone says, CTE's are temporary viewsThey're not. They are named subqueries.
If you consider them temporary views, you're going to keep running into situations where they don't behave as you expect.
Could MS make temporary views? Yes, but that would be a different feature with different rules and behaviours.
I don't see how a temporary view would accomplish anything that we can't do already. We have temp tables.
August 28, 2015 at 12:08 pm
Lynn Pettis (8/28/2015)
IIRC, the THROW statement that was recently introduced also requires it be terminated by a semicolon.
Previous statement must be terminated. Can't recall offhand if the THROW has to be as well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2015 at 12:27 pm
Sometime tells me Microsoft does not even want you creating temporary views.
CREATE VIEW #TestView AS (
SELECT 1 as a
)
Returns
Msg 4103, Level 15, State 1, Line 1
"#TestView": Temporary views are not allowed.
August 28, 2015 at 1:17 pm
mar.ko (8/28/2015)
I don't care what anyone says, CTE's are temporary views and should persist for the life of a stored proc.I am sure Microsoft could have made that possible.
They did make this type of thing possible. They have been around longer than CTE's. They are called temp tables.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 28, 2015 at 4:23 pm
CTE's also have bugs and restrictions:
When I run this code, the rows in the newly created table are not in sorted order.
W_CALLS AS
(
SELECT TOP 100 PERCENT
C.CALL_TP_ID
,C.CALL_TYPE
,C.HOUR_INT
,SUM(C.CallCnt) AS CallCnt
,SUM(C.CoreTotal+C.NonCoreTotal) AS ActSales
FROM dbo.V_CONV_HRLY_CALLS_SALES AS C
WHERE C.CALL_DT = @dateparm AND C.Call_TP_ID IN (0,2)
GROUP BY C.CALL_TYPE,C.CALL_TP_ID, C.HOUR_INT
ORDER BY C.CALL_TYPE,C.CALL_TP_ID, C.HOUR_INT
)
SELECT
C.CALL_TYPE
,C.HOUR_INT
,dbo.fnRptHourIntv(C.HOUR_INT) AS HOUR_INTV
,TARG_CALLS
,TARG_CONV
,C.CallCnt
,C.ActSales
INTO dbo.CONV_HRLY_RPT_CONVERSION_TBL
FROM W_CALLS AS C
LEFT JOIN W_TARG AS T ON T.INTV_HOUR = C.HOUR_INT AND T.CALL_TYPE = C.CALL_TP_ID
WHERE TARG_CONV IS NOT NULL
ORDER BY C.CALL_TYPE, C.HOUR_INT
I'm going to try a temporary view....
It appears the ORDER BY is ignored.....and all the TOP 100 PERCENT did is elminate the error message.
August 28, 2015 at 5:02 pm
Neither of those are bugs.
Tables are defined as unordered sets of rows. An ORDER BY on an insert affects the order that Identity values are assigned.
Order is something which a calling select statement imposes on rows. If you want the data to come from the table in a particular order, you put an ORDER BY on the select which queries from it.
Order By in a view is only honoured for a row-limiting TOP (ie not 100%) because the only place where an ORDER BY will affect the order of rows is in the outermost select statement, ie the select which queries from the view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2015 at 5:13 pm
I've seen the TOP 100 PERCENT...ORDER BY hack used in actual view definitions. I know it worked in SQL 2005, but I seem to recall it not working in a later version. I wish I could remember which version, but it's still a hack.
August 28, 2015 at 5:18 pm
Ed Wagner (8/28/2015)
I've seen the TOP 100 PERCENT...ORDER BY hack used in actual view definitions. I know it worked in SQL 2005, but I seem to recall it not working in a later version. I wish I could remember which version, but it's still a hack.
It worked in SQL 2000. It was one of the breaking changes to 2005 that the optimiser could ignore ORDER BY with non-row limiting TOP. There was a traceflag to allow the SQL 2000 behaviour in 2005, I can't remember what versions the traceflag worked in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2015 at 5:24 pm
GilaMonster (8/28/2015)
Ed Wagner (8/28/2015)
I've seen the TOP 100 PERCENT...ORDER BY hack used in actual view definitions. I know it worked in SQL 2005, but I seem to recall it not working in a later version. I wish I could remember which version, but it's still a hack.It worked in SQL 2000. It was one of the breaking changes to 2005 that the optimiser could ignore ORDER BY with non-row limiting TOP. There was a traceflag to allow the SQL 2000 behaviour in 2005, I can't remember what versions the traceflag worked in.
Sweet! Thank you, Gail. The semi-ironic part is that the database where I saw it is still on 2005. I'm setting up the new 2012 server next week, after which we're going to be migrating the databases. The whole exercise should be a lot of fun. 😉
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply