shortcut for table variables?

Question

shortcut for table variables?

sqlguy-736318

Hall of Fame

Points: 3859
More actions
July 27, 2015 at 5:15 pm

#310242

Hi, are you aware of any shortcut strategies for table variables similar to shortcut strategies for temp tables? For example, the following SQL will create a temp table named #MyTable with all of the columns from student and then select the top 10 rows of student data into #MyTable:
select top 10 * into #MyTable from Student
Is there any type of similar or equivalent shortcut for table variables like:
select top 10 * into @MyTable from Student
Afaik, if I want to select rows from Student into a @MyTable table variable then I need to create the @MyTable table variable with a column structure like a normal table which is more time-consuming than using the temp table approach.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

sqlguy-736318 (7/27/2015)
Hi, are you aware of any shortcut strategies for table variables similar to shortcut strategies for temp tables? For example, the following SQL will create a temp table named #MyTable with all of the columns from student and then select the top 10 rows of student data into #MyTable:
select top 10 * into #MyTable from Student
Is there any type of similar or equivalent shortcut for table variables like:
select top 10 * into @MyTable from Student
Afaik, if I want to select rows from Student into a @MyTable table variable then I need to create the @MyTable table variable with a column structure like a normal table which is more time-consuming than using the temp table approach.

You cannot do a select * into @tablevar

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

sqlguy-736318 (7/27/2015)
Afaik, if I want to select rows from Student into a @MyTable table variable then I need to create the @MyTable table variable with a column structure like a normal table which is more time-consuming than using the temp table approach.

By 'more time-consuming', you mean it'll take longer to write? Yes, but not by all that much.

Why are you using table variables over temp tables?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

It's only more time consuming when you're writing it. I'm with Gail though, be sure you're using table variables for the right reasons.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 4

I'm using the SS debugger and I want to set a watch on the table variable so I can set a breakpoint, inspect and view the values of the table variable. I don't think that this is possible with a temp table is it?

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 5

I posted a separate thread and table variables don't provide additional debugging capabilities using the SSMS debugger unfortunately.

What are the main benefits of using table variables over temp tables? I'm assuming the main benefit is that a table variable ensures an isolated scope.

In the previous posts to this thread, some forum members seemed to have some bias towards temp tables over table variables. Why is that?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

Table variables also don't have statistics. Sometimes, this is a great thing. Sometimes it's a horrible thing. But it's one of the biggest differences between table variables and temporary tables.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 7

There are quite a few differences. One of the best answers I've ever seen was a comprehensive look at that by Martin Smith over at Stack Overflow.

Check out the answer here: http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server

One of the big ones that people often run into is that if a query using a table variable is compiled before the table is populated, the optimizer will assume 1 row in the table variable, which can cause some issues.

Cheers!