table-variable

When should I use a table variable vs temporary table in sql server?

by

I’m learning more details in table variable. It says that temp tables are always on disk, and table variables are in memory, that is to say, the performance of table variable is better than temp table because table variable uses less IO operations than temp table. But sometimes, if there are too many records in … Read more

What’s the difference between a temp table and table variable in SQL Server?

by

In SQL Server 2005, we can create temp tables one of two ways: declare @tmp table (Col1 int, Col2 int); or create table #tmp (Col1 int, Col2 int); What are the differences between these two? I have read conflicting opinions on whether @tmp still uses tempdb, or if everything happens in memory. In which scenarios … Read more

SELECT INTO a table variable in T-SQL

by

Got a complex SELECT query, from which I would like to insert all rows into a table variable, but T-SQL doesn’t allow it. Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries. http://odetocode.com/Articles/365.aspx Short example: declare @userData TABLE( name varchar(30) NOT NULL, oldlocation varchar(30) NOT NULL ) … Read more