February 25, 2011 at 8:42 am
Hi,
I am a bit confused about the table variables. As transaction does not affect the table variable operations. So is there any way to force table variable to be part of transaction or in such situations we'll have to use temp tables only?
Also when we submit a query having a table variable then the query optimizer always assumes only single row and the statistics are not generated for the table variable so I am curious that why table variables are better option over temp tables (not always)?
Regards,
AJ:unsure:
February 25, 2011 at 8:51 am
Check this great article by Wayne Sheffield:
http://www.sqlservercentral.com/articles/Temporary+Tables/66720/
-- Gianluca Sartori
February 25, 2011 at 8:53 am
Variables are not part of a transaction. If you are using a table variable it will not be part of the transaction as there is not a table to log against. This is no different than any other variable type. If you want to be able to rollback changes during an operation you should use a temp table.
so I am curious that why table variables are better option over temp tables (not always)?
As with everything in sql the correct answer is "it depends". In this case a temp table is probably the better choice.
_______________________________________________________________
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/
February 25, 2011 at 8:54 am
In my opinion a Table varialbe is great for very small data sets (1 or two columns, and less than 100 rows), any more than that and you should consider a temp table.
Also consider that Table variables actually end up as unindexed tables in the tempdb anyway, as demonstrated below.
Declare @TableVariable Table (DT DateTime Default GetDate() NOT NULL)
Insert Into @TableVariable DefaultValues
WaitFor DELAY '00:00:10'
Create Table #TempTable (DT DateTime Default GetDate() NOT NULL)
Insert Into #TempTable Defauly Values
Select DT as TableVariable From @TableVariable
Select DT as TempTable From @TempTable
Select * from tempdb.sys.objects
Where type='U'
and create_date between DateAdd(minute,-1,GetDate()) and GetDate()
Drop Table #TempTable
Its best to use this on local or development box where the useage is almost non-existant.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 25, 2011 at 8:55 am
AJ-636201 (2/25/2011)
Hi,I am a bit confused about the table variables. As transaction does not affect the table variable operations. So is there any way to force table variable to be part of transaction or in such situations we'll have to use temp tables only?
Also when we submit a query having a table variable then the query optimizer always assumes only single row and the statistics are not generated for the table variable so I am curious that why table variables are better option over temp tables (not always)?
Regards,
AJ:unsure:
I was thinking that I had read a good article about this topic not long ago. 🙂
_______________________________________________________________
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/
February 25, 2011 at 9:05 am
Sean Lange (2/25/2011)
...there is not a table to log against. This is no different than any other variable type.
Not true: operations on table variables are logged, but don't participate in transcations.
Jason-299789 (2/25/2011)
Also consider that Table variables actually end up as unindexed tables in the tempdb anyway...
Not true: add a primary key or unique costraint and you can add indexes to a table variable.
Here's a great post by Gail Shaw on both myths: http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/
-- Gianluca Sartori
February 25, 2011 at 9:12 am
Gianluca Sartori (2/25/2011)
Sean Lange (2/25/2011)
...there is not a table to log against. This is no different than any other variable type.Not true: operations on table variables are logged, but don't participate in transcations.
Jason-299789 (2/25/2011)
Also consider that Table variables actually end up as unindexed tables in the tempdb anyway...Not true: add a primary key or unique costraint and you can add indexes to a table variable.
Here's a great post by Gail Shaw on both myths: http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/
I stand corrected on that point, I was pulled up by a senior on a code review several years ago when he caught me using table variables, and he insisted I revisit all my code and change the table variables to Temp tables.
ps : thanks for the link, its a good article.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 25, 2011 at 9:18 am
My 5 cents is always use a temp table. It behaves more like a real table than a table variable does.
Carlton.
February 25, 2011 at 9:26 am
Carlton Leach (2/25/2011)
My 5 cents is always use a temp table. It behaves more like a real table than a table variable does.Carlton.
There are perfectly valid reasons to use a table variable instead of a temp table. One thing about sql that never changes is that the word "always" is never 100% accurate. Read the first article referenced and you can see clearly that in some situations a variable would be preferred over a temp table and vice versa.
_______________________________________________________________
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/
February 25, 2011 at 9:29 am
Gianluca Sartori (2/25/2011)
Sean Lange (2/25/2011)
...there is not a table to log against. This is no different than any other variable type.Not true: operations on table variables are logged, but don't participate in transcations.
Ah yes. I guess I wasn't really thinking about if the changes were actually in the log or not since the end result is that it is not in the transaction. Would only make sense that any data changes would have to be logged. I guess I was trying to over simplify it in my head.
_______________________________________________________________
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/
February 25, 2011 at 9:43 am
AJ-636201 (2/25/2011)
Also when we submit a query having a table variable then the query optimizer always assumes only single row
If you are using the entire table (ie. no where clause), then you could use the OPTION RECOMPILE query hint on each query to get the actual number of rows. However, if you're not using the entire table, the optimizer then assumes a value - this isn't definite, but it looks to be about 1/3 of the number of rows in the table.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 25, 2011 at 9:45 am
Gianluca Sartori (2/25/2011)
Check this great article by Wayne Sheffield:http://www.sqlservercentral.com/articles/Temporary+Tables/66720/
Thanks Gianluca.
[shameless plug] And if you happen to be going to SQL Saturday #69 in Philadelphia on March 5, 2011, I'll be giving a presentation on this. I'll guarantee you'll learn something that you didn't know before! [/shameless plug]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 25, 2011 at 9:45 am
Sean Lange (2/25/2011)
Carlton Leach (2/25/2011)
My 5 cents is always use a temp table. It behaves more like a real table than a table variable does.Carlton.
There are perfectly valid reasons to use a table variable instead of a temp table. One thing about sql that never changes is that the word "always" is never 100% accurate. Read the first article referenced and you can see clearly that in some situations a variable would be preferred over a temp table and vice versa.
Granted, I guess 'always' is a strong word. I prefer them as they are more stable, and I don't trust anyone.
For instance in a stored proc: when will you know if the record count for a table variable exceeds the limit where it is practical? Where will the developer who wrote it be at this point :unsure:
oonce again, my 5 cents.
Carlton.
February 25, 2011 at 9:48 am
Carlton Leach (2/25/2011)
Sean Lange (2/25/2011)
Carlton Leach (2/25/2011)
My 5 cents is always use a temp table. It behaves more like a real table than a table variable does.Carlton.
There are perfectly valid reasons to use a table variable instead of a temp table. One thing about sql that never changes is that the word "always" is never 100% accurate. Read the first article referenced and you can see clearly that in some situations a variable would be preferred over a temp table and vice versa.
Granted, I guess 'always' is a strong word. I prefer them as they are more stable, and I don't trust anyone.
For instance in a stored proc: when will you know if the record count for a table variable exceeds the limit where it is practical? Where will the developer who wrote it be at this point :unsure:
oonce again, my 5 cents.
Carlton.
I also prefer temp tables and tend to only rarely think about table variables. 😉
_______________________________________________________________
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/
February 25, 2011 at 9:49 am
WayneS (2/25/2011)
Gianluca Sartori (2/25/2011)
Check this great article by Wayne Sheffield:http://www.sqlservercentral.com/articles/Temporary+Tables/66720/
Thanks Gianluca.
[shameless plug] And if you happen to be going to SQL Saturday #69 in Philadelphia on March 5, 2011, I'll be giving a presentation on this. I'll guarantee you'll learn something that you didn't know before! [/shameless plug]
I'm in the middle of a fight with my CIO to let me go to a conference in Milan, I don't think I can ever win for Philadelpia. 🙂
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply