September 19, 2013 at 5:39 am
Leifton (9/19/2013)
Not sure how everyone is getting this right. The script fails (as i suspected) in SQL 2005, 2K8 and 2K12. Since the first block is not run due to the false condition, how do the variables get declared for the second block?
If you have tried running it in 2k8 or 2K12 and it fails then you have copied the code wrongly I think.
For an explanation, read the whole thread, and also this recent discussion
http://www.sqlservercentral.com/Forums/Topic1494825-3185-4.aspx
September 19, 2013 at 5:41 am
Gary Stacey (9/19/2013)
Stewart "Arturius" Campbell (9/19/2013)
Keld Laursen (VFL) (9/19/2013)
I think it would have been prudent to mention the version restrictions in the question.if memory serves, QotD only relates to the current mainstream versions of SQL Server at the time of the question being posted. That would, currently, be SQL2008, SQL2008R2 and SQL2012.
SQL2005 is no longer in mainstream support (despite the number of customers still using it)
While that's true, I think it would have been useful to mention a version in this case, given the difference in behaviours between versions.
It does not fail for me in 2008R2.
//Edit - not sure what I did but apparently quote-reply on the wrong post. DISREGARD.
September 19, 2013 at 5:42 am
You need to specify which version. A lot of us are still on 2005 and it gives an error as we knew it would.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@word".
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@word".
Msg 137, Level 15, State 1, Line 10
Must declare the scalar variable "@word".
Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@word".
September 19, 2013 at 5:46 am
This was removed by the editor as SPAM
September 19, 2013 at 6:36 am
Good one, thanks!
September 19, 2013 at 7:49 am
raulggonzalez (9/19/2013)
Thanks for the question.Interesting the logical execution of
declare @word varchar(100) = 'apple'
in two phases.
It makes sense, though. It's more efficient to allocate space for objects in one pass during parsing, but if you assign the initial values during parsing, then you effectively limit the initial values to constants. Consider, for example, the following:
DECLARE @Start datetime = getdate();
SELECT * FROM v_SeveralMinutesToExecuteView;
DECLARE @End datetime = getdate();
SELECT DATEDIFF(ms,@Start,@End) as TimeToExecute;
If values were set during parse time, @Start and @End would have the same value (possibly off by a small amount if the code executed across a millisecond boundary).
Or consider this:
DECLARE @Rows BIGINT = (SELECT COUNT(*) FROM SomeTable);
SELECT @Rows;
This would simple be impossible if values were assigned during parse time.
September 19, 2013 at 7:49 am
Oops, guessed correctly, but tested on SQL 2005. Should have not doubted myself.
September 19, 2013 at 8:14 am
SQL 2008 is the "official" version assumed for QOTD.
However, I still run into SQL 2005 regularly. The licensing structure changed significantly from 2005 --> 2008, making the change prohibitive for many customers.
September 19, 2013 at 8:21 am
Jeff.MSSqlSage (9/19/2013)
SQL 2008 is the "official" version assumed for QOTD.However, I still run into SQL 2005 regularly. The licensing structure changed significantly from 2005 --> 2008, making the change prohibitive for many customers.
OK, where is it documented that "SQL 2008 is the "official" version assumed for QOTD."? Why is not SQL 2012?
This not intended to be mean, just want to know because I am lacking this information.
September 19, 2013 at 8:33 am
There's not an "official" version. However, the expected version is the current version (2012 at the moment) and 2 previous major versions (2005 and 2008).
The question might have needed to specify it should be used on 2088+ but the syntax shows it clearly and I wouldn't expect it to work on 2005 and don't consider that failing to choose the correct answer after running it on 2005 is something to complain about.
September 19, 2013 at 8:36 am
Koen Verbeeck (9/19/2013)
Very similar to the question a few days ago, but a bit harder.Really nice question, thanks.
+1
September 19, 2013 at 8:53 am
Luis Cazares (9/19/2013)
There's not an "official" version. However, the expected version is the current version (2012 at the moment) and 2 previous major versions (2005 and 2008).The question might have needed to specify it should be used on 2088+ but the syntax shows it clearly and I wouldn't expect it to work on 2005 and don't consider that failing to choose the correct answer after running it on 2005 is something to complain about.
I don't have any documentation. I'm just parroting what I've heard a dozen times.
Also SQL 2088 is not out yet
September 19, 2013 at 9:29 am
sknox (9/19/2013)
(...)Consider, for example, the following:
DECLARE @Start datetime = getdate();
SELECT * FROM v_SeveralMinutesToExecuteView;
DECLARE @End datetime = getdate();
SELECT DATEDIFF(ms,@Start,@End) as TimeToExecute;
If values were set during parse time, @Start and @End would have the same value (possibly off by a small amount if the code executed across a millisecond boundary).
(...)
I really wouldn't mind if that would happen. The way I see it, that code should have been written more like this:
DECLARE @Start datetime
DECLARE @End datetime
SET @Start = getdate();
SELECT * FROM v_SeveralMinutesToExecuteView;
SET @End = getdate();
SELECT DATEDIFF(ms,@Start,@End) as TimeToExecute;
September 19, 2013 at 9:39 am
Easy question for the day...
September 19, 2013 at 10:59 am
Primo Dang (9/19/2013)
sknox (9/19/2013)
(...)Consider, for example, the following:
DECLARE @Start datetime = getdate();
SELECT * FROM v_SeveralMinutesToExecuteView;
DECLARE @End datetime = getdate();
SELECT DATEDIFF(ms,@Start,@End) as TimeToExecute;
If values were set during parse time, @Start and @End would have the same value (possibly off by a small amount if the code executed across a millisecond boundary).
(...)
I really wouldn't mind if that would happen. The way I see it, that code should have been written more like this:
DECLARE @Start datetime
DECLARE @End datetime
SET @Start = getdate();
SELECT * FROM v_SeveralMinutesToExecuteView;
SET @End = getdate();
SELECT DATEDIFF(ms,@Start,@End) as TimeToExecute;
That's a good way to do it, but it simply eliminates, rather than answering, the question of when initial values are assigned.
The code I used was merely for illustration. If I were actually using that code, I'd probably write it as
DECLARE @Start datetime = getdate();
SELECT * FROM v_SeveralMinutesToExecuteView;
SELECT DATEDIFF(ms,@Start,getdate()) as TimeToExecute;
to keep it concise. Here too, there could be a different result if @Start were assigned its value during parse time: there may be one or more millisecond boundaries between that point in parsing and the start of execution.
Of course, to get a more accurate measure of the time to actually run the query itself, it'd probably be better to use
SET STATISTICS TIME ON;
GO
SELECT * FROM v_SeveralMinutesToExecuteView;
GO
SET STATISTICS TIME OFF;
GO
although that is not accurate if you're using lightweight pooling (see http://technet.microsoft.com/en-us/library/ms190287.aspx)
EDIT: NOT accurate with lightweight pooling. Darn brain keeps running faster than I can type!
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply