October 30, 2010 at 5:52 am
Question for the experts.
In reviewing code written by a contractor, they declare a table variable inside a cursor, but it is never used. The cursor itself can have thousands of rows, so it's not huge, but it is pretty good size.
It's easy enough to delete the declaration.
My question - how does the server handle this? is any memory allocated to this structure if nothing is inserted into the table variable? Does each iteration of the loop create a new instance of the variable (so, are there x copies in memory), or only one?
WHILE @@FETCH_STATUS = 0
BEGIN
IF @promo <> 'XX'
BEGIN
DECLARE @xItems TABLE
(.....)
END
FETCH NEXT FROM @MyCursor INTO @promo
October 30, 2010 at 7:45 am
There will be only one copy of the table variable.
N 56°04'39.16"
E 12°55'05.25"
October 30, 2010 at 3:30 pm
Kevin Bullen (10/30/2010)
Question for the experts.In reviewing code written by a contractor, they declare a table variable inside a cursor, but it is never used. The cursor itself can have thousands of rows, so it's not huge, but it is pretty good size.
It's easy enough to delete the declaration.
My question - how does the server handle this? is any memory allocated to this structure if nothing is inserted into the table variable? Does each iteration of the loop create a new instance of the variable (so, are there x copies in memory), or only one?
WHILE @@FETCH_STATUS = 0
BEGIN
IF @promo <> 'XX'
BEGIN
DECLARE @xItems TABLE
(.....)
END
FETCH NEXT FROM @MyCursor INTO @promo
I don't know if you want to "hear it" or not, but I'd be more interested in why the contractor elected to use a cursor to begin with. What does the cursor do? If you're interested in getting rid of it, post it and maybe some sample data and people will likely jump on the problem for you.
As a side bar, a contractor that leaves unused code in a proc is someone that you may want to consider keeping a very close eye on or maybe even replacing. While leaving the definition of an unused table variable in the code may not seem like much (although it does take a smidge of extra time and resource which add up over many instances), it does show a lack of attention to detail on the part of the contractor and begs the question "What else are they doing wrong?".
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2010 at 7:18 pm
Jeff Moden (10/30/2010)
Kevin Bullen (10/30/2010)
Question for the experts.In reviewing code written by a contractor, they declare a table variable inside a cursor, but it is never used. The cursor itself can have thousands of rows, so it's not huge, but it is pretty good size.
It's easy enough to delete the declaration.
My question - how does the server handle this? is any memory allocated to this structure if nothing is inserted into the table variable? Does each iteration of the loop create a new instance of the variable (so, are there x copies in memory), or only one?
WHILE @@FETCH_STATUS = 0
BEGIN
IF @promo <> 'XX'
BEGIN
DECLARE @xItems TABLE
(.....)
END
FETCH NEXT FROM @MyCursor INTO @promo
I don't know if you want to "hear it" or not, but I'd be more interested in why the contractor elected to use a cursor to begin with. What does the cursor do? If you're interested in getting rid of it, post it and maybe some sample data and people will likely jump on the problem for you.
As a side bar, a contractor that leaves unused code in a proc is someone that you may want to consider keeping a very close eye on or maybe even replacing. While leaving the definition of an unused table variable in the code may not seem like much (although it does take a smidge of extra time and resource which add up over many instances), it does show a lack of attention to detail on the part of the contractor and begs the question "What else are they doing wrong?".
And, if they're using a c.u.r.s.o.r. - why are you spending your money on someone that doesn't know how to code set-based properly? Want to hire me to get it done right?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 30, 2010 at 9:21 pm
Understood, I've already re-written the stored proc in question to use a set based solution. The problem is the person who hired the contractor is not a SQL programmer and doesn't appreciate that just because you can spell SQL doesn't mean you can write it.
I was more looking to understand, for my own knowledge, on how variable declarations work inside a cursor. If the cursor iterates 1000 times, are 1000 instances of a table created, or is the same instance released and recreated.
Thanks for the input.
October 30, 2010 at 11:56 pm
Kevin Bullen (10/30/2010)
If the cursor iterates 1000 times, are 1000 instances of a table created, or is the same instance released and recreated.
Neither.
SQL Server is smart enough to move all declares to the beginning of the procedure regardless of where they logical are created in the procedure.
See this very simple repro!
IF 1 = 2
BEGIN
DECLARE @Sample TABLE (i INT)
END
IF 1 = 2
BEGIN
INSERT @Sample VALUES (1)
END
SELECT * FROM @Sample
Before you copy and test the code, do you expect the code to generate an error in the last SELECT clause?
N 56°04'39.16"
E 12°55'05.25"
October 31, 2010 at 12:38 am
It still has to be declared before it is referenced... if you move the if/declare block to the end of the code, it does generate an error.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 31, 2010 at 10:12 am
SwePeso (10/30/2010)
Kevin Bullen (10/30/2010)
If the cursor iterates 1000 times, are 1000 instances of a table created, or is the same instance released and recreated.Neither.
SQL Server is smart enough to move all declares to the beginning of the procedure regardless of where they logical are created in the procedure.
See this very simple repro!
IF 1 = 2
BEGIN
DECLARE @Sample TABLE (i INT)
END
IF 1 = 2
BEGIN
INSERT @Sample VALUES (1)
END
SELECT * FROM @Sample
Before you copy and test the code, do you expect the code to generate an error in the last SELECT clause?
Ummmm....
IF 1 = 2
BEGIN
INSERT @Sample VALUES (1)
END
IF 1 = 2
BEGIN
DECLARE @Sample TABLE (i INT)
END
SELECT * FROM @Sample
Sorry, Peter. I have to disagree. SQL Server wasn't smart enough and didn't move all declares to the beginning of the procedure. 🙂 Perhaps you meant something else?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2010 at 10:57 am
Ok.. so we understood the importance of order of declaration,thanks for that.
But even I have the initial doubt in the post, similar code is here using WHILE.
Is it like in each flow of the loop, the table variable declaration is considered as a new declaration? (I don't know) , if so then how the data is retained in it. And I can see if it is temp table, it will throw an error telling that there is a table already. (as it has created in the memory by the first flow itself..)
So can some one let me know how the table variable declaration is happening inside the loop..?
DECLARE @v-2 INT = 1
WHILE(@V < 10)
BEGIN
DECLARE @a TABLE (ID INT)
INSERT INTO @a
SELECT @v-2
END
SELECT * FROM @a
Thanks & Regards,
MC
October 31, 2010 at 10:58 am
WayneS is spot on. The table is declared not matter what AT the location in code where it is so all following code is referencing it correctly.
N 56°04'39.16"
E 12°55'05.25"
October 31, 2010 at 11:29 pm
WayneS (10/31/2010)
It still has to be declared before it is referenced... if you move the if/declare block to the end of the code, it does generate an error.
Sorry, Wayne... blew right past your post and didn't see it until Peter pointed it out.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2010 at 3:58 am
So friends my doubt is still not clear.. I'm copying it here again..
Ok.. so we understood the importance of order of declaration,thanks for that.
But even I have the initial doubt in the post, similar code is here using WHILE.
Is it like in each flow of the loop, the table variable declaration is considered as a new declaration? (I don't know) , if so then how the data is retained in it. And I can see if it is temp table, it will throw an error telling that there is a table already. (as it has created in the memory by the first flow itself..)
So can some one let me know how the table variable declaration is happening inside the loop..?
DECLARE @v-2 INT = 1
WHILE(@V < 10)
BEGIN
DECLARE @a TABLE (ID INT)
INSERT INTO @a
SELECT @v-2
END
SELECT * FROM @a
Thanks & Regards,
MC
November 1, 2010 at 5:03 am
No, the table is NOT declared for each loop. You can confirm that at the final result.
However, I consider this bad practice and confusing to put the table declaration inside the loop.
N 56°04'39.16"
E 12°55'05.25"
November 1, 2010 at 7:13 am
Oh..ok thanks for your reply. Even I don't prefer to declare the table inside loop. But just wanted to know how it is working..
As you said the table is created only once and we can see only one table in the final result. But I'm wondering how it is differing in case of temp table.
As you know if we replace the table variable with temp table., we will get an error as ' The table already existing ' because it will be created by the first flow itself.
So what I'm trying to understand is how the table creation is happening only once in case of table variable but trying to create in each flow of the loop in case of temp table (and because of that we are getting error in case of temp table ).
Can I know how it is happening...?
Thanks & Regards,
MC
November 1, 2010 at 11:12 am
Temp tables and table variables act differently with different scopes.
The CREATE TABLE #tmp is equivalent, in the scope of the script, to CREATE TABLE tbl_table. It's not a singular delcaration. It can hold its own indexes, constraints, non-clusters... the works. Unless it's dropped, a second CREATE goes kablewie... again, within the scope of a single script (or connection, if you leave one open). The #tmp exists for the connection, the @Tmp only exists during run-time, and is not persisted.
The DECLARE @tbl TABLE is a variable, and is treated as such. The pre-processor makes sure it's declared prior to usage, and then stores all the declares prior to running the real scripting. It's not continuously declared. Point in case you can't declare it twice period. Try this:
DECLARE @Table TABLE ( tID INT)
DECLARE @Table TABLE ( tID INT, vField VARCHAR(100))
Of course to make my life interesting, this gets grumpy too and I can't remember how to get it to behave offhand. Maybe someone else remembers how to get this to behave without GO statements to split the script.
CREATE TABLE #tmp (tID INT);
DROP TABLE #tmp;
CREATE TABLE #tmp (tID INT, tText VARCHAR(200));
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply