September 22, 2016 at 11:51 am
Luis Cazares (9/22/2016)
drew.allen (9/22/2016)
Sue_H (9/22/2016)
Luis Cazares (9/22/2016)
Interview question?My thoughts as well. The follow up to that specific question could be interesting if someone doesn't know what would be different between the two.
The answer to this interview question is, "Why are you asking questions about a version that is after it's end-of-life?"
Drew
Because we're planning a migration of our system on SQL Server 2000 and those are the versions we have available. :hehe:
Or, we migrated our development servers to SQL Server 2008 but we still have to support our SQL Server 2005 servers in production. Been there.
September 22, 2016 at 12:03 pm
Lynn Pettis (9/22/2016)
Luis Cazares (9/22/2016)
drew.allen (9/22/2016)
Sue_H (9/22/2016)
Luis Cazares (9/22/2016)
Interview question?My thoughts as well. The follow up to that specific question could be interesting if someone doesn't know what would be different between the two.
The answer to this interview question is, "Why are you asking questions about a version that is after it's end-of-life?"
Drew
Because we're planning a migration of our system on SQL Server 2000 and those are the versions we have available. :hehe:
Or, we migrated our development servers to SQL Server 2008 but we still have to support our SQL Server 2005 servers in production. Been there.
And they want you to refresh the dev server from production.
Edit:
Oops sorry, meant the other way around. Have those - Production 2008, Dev 2005 and want refreshes in Dev.
September 22, 2016 at 12:12 pm
🙂
thanks for all the replies I am smiling now because of you guys(or gals)!
This is also why simple questions don't always have simple answers esp. for SQL.
So my question now is when I declare a variable and set it to value as such in 2008..
DECLARE @MY_VAR INT=0;
From an initialization standpoint it initializes to NULL and sets it to 0? The same as 2005 which can only be done as...
DECLARE @MY_VAR INT;
SET @MY_VAR=0;
September 22, 2016 at 12:13 pm
Smendle (9/22/2016)
Eirikur Eiriksson (9/22/2016)
For a variable inside the procedure use this😎
DECLARE @MY_INT_VAR INT;SET @MY_INT_VAR = 0;
i could be confusing this with a .NET programming environment but doesn't that initialize the variable to NULL then set it to 0?
I know DECLARE @MY_INT_VAR INT = 0; only works in 2008 and up but that's within a procedure.
to initialize to 0 for a stored proc for 2005 and 2008 you would need to do it this way.
alter procedure usp_mytestproc @MY_INT_VAR INT=0
WITH EXECUTE AS OWNER
AS
BEGIN
SELECT @MY_INT_VAR as InitilizedValue
END
exec usp_mytestproc
Acutally the OP asked initializing a variable. Your code here does not have any variables. What you posted is demonstrating how you provide a default value for a parameter. Pedantic perhaps but they are very different animals.
_______________________________________________________________
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/
September 22, 2016 at 12:17 pm
Smendle (9/22/2016)
🙂thanks for all the replies I am smiling now because of you guys(or gals)!
This is also why simple questions don't always have simple answers esp. for SQL.
So my question now is when I declare a variable and set it to value as such in 2008..
DECLARE @MY_VAR INT=0;
From an initialization standpoint it initializes to NULL and sets it to 0? The same as 2005 which can only be done as...
DECLARE @MY_VAR INT;
SET @MY_VAR=0;
That's right. It's a coding shortcut, but it does the same behind the scenes.
September 22, 2016 at 11:54 pm
Smendle (9/22/2016)
Eirikur Eiriksson (9/22/2016)
For a variable inside the procedure use this😎
DECLARE @MY_INT_VAR INT;SET @MY_INT_VAR = 0;
i could be confusing this with a .NET programming environment but doesn't that initialize the variable to NULL then set it to 0?
I know DECLARE @MY_INT_VAR INT = 0; only works in 2008 and up but that's within a procedure.
That's wrong.
DECLARE @MY_INT_VAR INT = 0; is not a single operation.
It's actually two operations combined into one syntax construction.
SQL Server splits this construction in 2 and executes them separately:
DECLARE part during compilation;
SET = 0 part in run time.
The correct answer is what Eirikur posted.
_____________
Code for TallyGenerator
September 23, 2016 at 12:21 am
To illustrate my point I make a little amedment to the piece of code from Luis:
DECLARE @N int = 0;
WHILE @N < 5
BEGIN
DECLARE @What int = 0;
SELECT @What [@What], @N [@N];
SET @N += 1 ;
SET @What +=1
END;
I'm trying to increment @What the same way as @N is incremented.
And guess what?
@What stays 0 in every cycle.
You may even for assurance do it this way:
SET @N += 1 ;
SET @What = @N
Same thing: @N gets incremented, @What remains 0.
So, the statement DECLARE @What int = 0; obviously is executed on every iteration.
But it still does not fail because of repeating declaration of the same variable.
I have only one explanation for that:
the statement
DECLARE @What int = 0;
actually represents 2 operations - DECLARE and SET.
And they are executed independently from each other.
BTW, the same is true for .NET.
_____________
Code for TallyGenerator
September 23, 2016 at 5:23 am
Sergiy (9/22/2016)
Smendle (9/22/2016)
Eirikur Eiriksson (9/22/2016)
For a variable inside the procedure use this😎
DECLARE @MY_INT_VAR INT;SET @MY_INT_VAR = 0;
i could be confusing this with a .NET programming environment but doesn't that initialize the variable to NULL then set it to 0?
I know DECLARE @MY_INT_VAR INT = 0; only works in 2008 and up but that's within a procedure.
That's wrong.
DECLARE @MY_INT_VAR INT = 0; is not a single operation.
It's actually two operations combined into one syntax construction.
SQL Server splits this construction in 2 and executes them separately:
DECLARE part during compilation;
SET = 0 part in run time.
The correct answer is what Eirikur posted.
That has been meted out already Serigy.
whether its 2005's way of needing to declare then set (Eirikur's answer)
DECLARE @MYVAR INT;
SET @MYVAR=0
or 2008 inline declaration which as you have stated is a two stage operation in one line of code
DECLARE @MYVAR INT=0;
or a Stored Procedure which passes a default value to a parameter into it and works for both 2005 and 2008 (my attempt to spark a debate which I think worked brilliantly!)
CREATE STORED PROC usp_MYPROC @MYVAR INT=0
AS
BEGIN
SELECT @MYVAR AS Variable
END
I would love to get an official response from someone in the know on source code...
1. Is a passed parameter treated as a variable once inside the stored proc?
2. If the answer to 1 is YES than what is the initialized value of that variable?
I do not claim to know the source for sqlserver.exe but I imagine that the answer to my questions are:
1. Yes
2. It is initialized to the value passed into the parameter once inside the parameter.
Understand that at compilation time the way things happen, in the order they do happen, does affect the outcome of results, even for simple questions like what the OP posed to us.
I bolded and italicized the key word in the OP question
How do I initialise a sql variable to 0 in stored-procedure ?
Maybe the correct answer is "You cant", but as I said I believe a passed parameter, once you are inside that parameter is treated, in all aspects, as a variable inside the parameter.
But for the purposes of argument you can only initialize a variable in a stored proc to NULL and then SET IT TO 0 (ZERO).
Its the difference between driving a car and understand all the moving parts that work in making the car go (or stop!)
My final point here is, yes I "basically" knew the answer all along but I wanted to spark debate about this very "simple" question.
If you are reading this and you are in any way involved with utilizing SQL Server to perform work for a living than understanding the difference between what you "see" (in SSMS or otherwise) and whats going on behind the scenes are two very different things.
Most people here understand that concept, its what imo makes you all very good at your jobs. I have never worked with anyone here on this board (that I know of) but I respect and want to learn for each and every one of you because of how you respond to not only this thread, but all the others I have read on this board.
Its the curse of IT in general "a lot of different ways to perform 1 ounce of work"
"Understand your canvas and you will understand the limits of what you are about to create"
I want to thank everyone who has contributed to this thread. I am smiling once again because it is things like this simple thread that confirms what I have known for sometime,
"Its all in the details"
Thanks,
Greg Russell
September 23, 2016 at 6:47 am
Smendle (9/23/2016)
I would love to get an official response from someone in the know on source code...
1. Is a passed parameter treated as a variable once inside the stored proc?
2. If the answer to 1 is YES than what is the initialized value of that variable?
I do not claim to know the source for sqlserver.exe but I imagine that the answer to my questions are:
1. Yes
2. It is initialized to the value passed into the parameter once inside the parameter.
For basic things, the parameter is treated as a variable, but it really shouldn't because it has differences.
Some of the differences are:
1. Parameter sniffing: Even if you declare a variable and give it a static value, it won't affect the execution plan the same way parameters will.
2. Table-valued variables vs Table-valued parameters: Variables can be modified, parameters are read only (no inserts, no deletes, no updates).
3. Parameters are part of the object definition, variables are not.
So, yes, you can treat parameters and variables the same way. The problem is that SQL Server won't do that and you shouldn't either.
Disclaimer: I don't know the source code, I've just learn this from reading and practicing. For people knowing the source code, you need to ask a Microsoft engineer.
September 23, 2016 at 8:09 am
Quick thought, the value of the variable will not be compiled but set at run time
😎
CTE example to get an execution plan
DECLARE @VAR_01 INT;
SET @VAR_01 = 1;
DECLARE @VAR_02 INT = 2;
DECLARE @VAR_03 DATETIME = GETDATE();
;WITH BASE_DATA(V1,V2,V3) AS
(
SELECT
@VAR_01
,@VAR_02
,@VAR_03
)
SELECT
BD.V1
,BD.V2
,BD.V3
FROM BASE_DATA BD;
The execution plan XML
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.5" Build="13.0.1708.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="5" StatementEstRows="1" StatementId="2" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="1.157E-06" StatementText="WITH BASE_DATA(V1,V2,V3) AS ( SELECT @VAR_01 ,@VAR_02 ,@VAR_03 ) SELECT BD.V1 ,BD.V2 ,BD.V3 FROM BASE_DATA BD" StatementType="SELECT" QueryHash="0x732E0B0B900A7040" QueryPlanHash="0xA5D3409D4AE2F2A4" RetrievedFromCache="true" SecurityPolicyApplied="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="8" CompileTime="0" CompileCPU="0" CompileMemory="104">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="153600" EstimatedPagesCached="38400" EstimatedAvailableDegreeOfParallelism="2" />
<RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="0" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
</RunTimeInformation>
<ConstantScan />
</RelOp>
<ParameterList>
<ColumnReference Column="@VAR_03" ParameterRuntimeValue="'2016-09-23 15:05:34.423'" />
<ColumnReference Column="@VAR_02" ParameterRuntimeValue="(2)" />
<ColumnReference Column="@VAR_01" ParameterRuntimeValue="(1)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
September 23, 2016 at 8:17 am
Smendle (9/23/2016)
Sergiy (9/22/2016)
Smendle (9/22/2016)
Eirikur Eiriksson (9/22/2016)
For a variable inside the procedure use this😎
DECLARE @MY_INT_VAR INT;SET @MY_INT_VAR = 0;
i could be confusing this with a .NET programming environment but doesn't that initialize the variable to NULL then set it to 0?
I know DECLARE @MY_INT_VAR INT = 0; only works in 2008 and up but that's within a procedure.
That's wrong.
DECLARE @MY_INT_VAR INT = 0; is not a single operation.
It's actually two operations combined into one syntax construction.
SQL Server splits this construction in 2 and executes them separately:
DECLARE part during compilation;
SET = 0 part in run time.
The correct answer is what Eirikur posted.
That has been meted out already Serigy.
whether its 2005's way of needing to declare then set (Eirikur's answer)
DECLARE @MYVAR INT;
SET @MYVAR=0
or 2008 inline declaration which as you have stated is a two stage operation in one line of code
DECLARE @MYVAR INT=0;
or a Stored Procedure which passes a default value to a parameter into it and works for both 2005 and 2008 (my attempt to spark a debate which I think worked brilliantly!)
CREATE STORED PROC usp_MYPROC @MYVAR INT=0
AS
BEGIN
SELECT @MYVAR AS Variable
END
I would love to get an official response from someone in the know on source code...
1. Is a passed parameter treated as a variable once inside the stored proc?
2. If the answer to 1 is YES than what is the initialized value of that variable?
I do not claim to know the source for sqlserver.exe but I imagine that the answer to my questions are:
1. Yes
2. It is initialized to the value passed into the parameter once inside the parameter.
Understand that at compilation time the way things happen, in the order they do happen, does affect the outcome of results, even for simple questions like what the OP posed to us.
I bolded and italicized the key word in the OP question
How do I initialise a sql variable to 0 in stored-procedure ?
Maybe the correct answer is "You cant", but as I said I believe a passed parameter, once you are inside that parameter is treated, in all aspects, as a variable inside the parameter.
But for the purposes of argument you can only initialize a variable in a stored proc to NULL and then SET IT TO 0 (ZERO).
Its the difference between driving a car and understand all the moving parts that work in making the car go (or stop!)
My final point here is, yes I "basically" knew the answer all along but I wanted to spark debate about this very "simple" question.
If you are reading this and you are in any way involved with utilizing SQL Server to perform work for a living than understanding the difference between what you "see" (in SSMS or otherwise) and whats going on behind the scenes are two very different things.
Most people here understand that concept, its what imo makes you all very good at your jobs. I have never worked with anyone here on this board (that I know of) but I respect and want to learn for each and every one of you because of how you respond to not only this thread, but all the others I have read on this board.
Its the curse of IT in general "a lot of different ways to perform 1 ounce of work"
"Understand your canvas and you will understand the limits of what you are about to create"
I want to thank everyone who has contributed to this thread. I am smiling once again because it is things like this simple thread that confirms what I have known for sometime,
"Its all in the details"
Thanks,
Greg Russell
This:
CREATE STORED PROC usp_MYPROC @MYVAR INT=0
Is NOT the same as either of these:
DECLARE @MYVAR INT;
SET @MYVAR = 0;
DECLARE @MYVAR INT = 0;
September 23, 2016 at 11:04 am
This:
CREATE STORED PROC usp_MYPROC @MYVAR INT=0
Is NOT the same as either of these:
DECLARE @MYVAR INT;
SET @MYVAR = 0;
DECLARE @MYVAR INT = 0;
Lynn Pettis
but @MYVAR as a parameter can be handled the same as variable. The only difference is in object/variable creation and instantiation.
I like what Eirikur wrote as his addendum it sheds more light that you saying its not the same over and over again....how do you know? Again Ill ask do you have source code access to sqlserver.exe?
Please share why...I really am interested....(not trying to argue just lively debate 🙂 )
September 23, 2016 at 11:28 am
I'll repeat it, no one has access to the SQL Server code except for people at Microsoft.
If you liked the Eirikur's example, here's a variation to show the difference between Parameters and variables.
ALTER PROCEDURE VariablesVsParams(
@VAR_01 INT = 2,
@VAR_02 DATETIME
)
AS
SET @VAR_02 = GETDATE();
DECLARE @VAR_03 INT = 1;
WITH BASE_DATA(V1,V2,V3) AS
(
SELECT
@VAR_01
,@VAR_02
,@VAR_03
)
SELECT
BD.V1
,BD.V2
,BD.V3
FROM BASE_DATA BD;
GO
EXEC VariablesVsParams @VAR_01 = 9999, @VAR_02 = '20160923';
Which result on the following execution plan:
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.4100.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="5" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="1.157E-06" StatementText="WITH BASE_DATA(V1,V2,V3) AS ( SELECT @VAR_01 ,@VAR_02 ,@VAR_03 ) SELECT BD.V1 ,BD.V2 ,BD.V3 FROM BASE_DATA BD" StatementType="SELECT" QueryHash="0x89E782947055DC41" QueryPlanHash="0xA5D3409D4AE2F2A4" RetrievedFromCache="true">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="8" CompileTime="0" CompileCPU="0" CompileMemory="88">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="416233" EstimatedPagesCached="104058" EstimatedAvailableDegreeOfParallelism="2" />
<RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="0" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<ConstantScan />
</RelOp>
<ParameterList>
<ColumnReference Column="@VAR_03" ParameterRuntimeValue="(1)" />
<ColumnReference Column="@VAR_02" ParameterCompiledValue="'2016-09-23 00:00:00.000'" ParameterRuntimeValue="'2016-09-23 13:22:16.627'" />
<ColumnReference Column="@VAR_01" ParameterCompiledValue="(9999)" ParameterRuntimeValue="(9999)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
As you can see, the parameters have a compiled value, but the variable doesn't. It's also a bad idea to modify parameter values inside the procedure as they won't match the compiled values and the plan might not be optimal for the new values.
September 23, 2016 at 11:41 pm
Smendle (9/23/2016)
I bolded and italicized the key word in the OP question
How do I initialise a sql variable to 0 in stored-procedure ?
Maybe the correct answer is "You cant", but as I said I believe a passed parameter, once you are inside that parameter is treated, in all aspects, as a variable inside the parameter.
But for the purposes of argument you can only initialize a variable in a stored proc to NULL and then SET IT TO 0 (ZERO).
I guess you have a wrong understanding of the term.
I'm saying this because it was my issue too.
In computer programming, initialization is the assignment of an initial value for a data object or variable. The manner in which initialization is performed depends on programming language, as well as type, storage class, etc., of an object to be initialized.
https://en.m.wikipedia.org/wiki/Initialization_(programming)
You cannot initialise any variable with NULL, because NULL is not a value.
It's rather lack of value.
And initialization is assigning a value.
So,
DECLARE @MyVar int does not initialise a variable, it only allocate a placeholder for it.
SET @MyVar =0 is what named "initialise a variable"
If you rethink it - everything will find its place.
BTW, default value of a parameter is totally different matter.
Parameter gets initialised anyway with a value every time when it's mentioned in a SP call.
Default value just indicates what value to use for initialisation when the parameter is not mentioned in the proc call.
_____________
Code for TallyGenerator
September 24, 2016 at 5:14 am
Sergiy (9/23/2016)
Smendle (9/23/2016)
I bolded and italicized the key word in the OP question
How do I initialise a sql variable to 0 in stored-procedure ?
Maybe the correct answer is "You cant", but as I said I believe a passed parameter, once you are inside that parameter is treated, in all aspects, as a variable inside the parameter.
But for the purposes of argument you can only initialize a variable in a stored proc to NULL and then SET IT TO 0 (ZERO).
I guess you have a wrong understanding of the term.
I'm saying this because it was my issue too.
In computer programming, initialization is the assignment of an initial value for a data object or variable. The manner in which initialization is performed depends on programming language, as well as type, storage class, etc., of an object to be initialized.
https://en.m.wikipedia.org/wiki/Initialization_(programming)
You cannot initialise any variable with NULL, because NULL is not a value.
It's rather lack of value.
And initialization is assigning a value.
So,
DECLARE @MyVar intdoes not initialise a variable, it only allocate a placeholder for it.
SET @MyVar =0is what named "initialise a variable"If you rethink it - everything will find its place.
BTW, default value of a parameter is totally different matter.
Parameter gets initialised anyway with a value every time when it's mentioned in a SP call.
Default value just indicates what value to use for initialisation when the parameter is not mentioned in the proc call.
Thank you Sergiy for clarifying that process of instantiation and initialization.
Do you know if a passed parameter is handled the same way as a variable once in a stored proc? Since it seems to act like a variable, I am curious to know as this question I have really hasn't been answered and I know where to go to find it, I was just hoping someone here could give a definitive answer.
Does a parameter get instantiated and used the same way as a variable once inside a stored proc?
Thanks again for the clarification.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply