I won't go into the difference between temp tables and table variables - here's a blog which does this for me:
Temporary Tables versus Table Variables
and another one:
What's the difference?
The way I read these descriptions, there is little to choose between the two techniques. They certainly look very alike - here's the grammar to create a temporary table, pretty much the same as creating a regular, permanent table except for the # character:
--create temporary table
create table #MemscalesA
(
bodid int,
familyid int,
strForename nvarchar(100),
strSurname nvarchar(100),
coverid int,
strCover nvarchar(100),
IsMember tinyint,
IsSpouse tinyint,
IsDependant tinyint
)
So when Microsoft announced table variables, for SQL Server 2000 I think it was, this revolutionary concept must have stunned and amazed the community:
-- declare table variable
declare @MemscalesA table
(
bodid int,
familyid int,
strForename nvarchar(100),
strSurname nvarchar(100),
coverid int,
strCover nvarchar(100),
IsMember tinyint,
IsSpouse tinyint,
IsDependant tinyint
)
Wow, eh? Sit back and catch your breath.
I'm being a bit unfair,of course - there are differences, and they depend on what exactly you are doing with your tables. Dimitri Tsuranoff puts it well (in the first link above) when he says "There is no universal rule for when and where to use temporary tables or table variables. Try them both and experiment."
So I was a bit surprised when I found that there was a circumstance when you MUST use table variables if you want your code to work. It's if you want to use it in an Reporting Services report.
My colleague wrote some code to find people who had purchased unsuitable insurance cover (single people with family policies, families with policies excluding dependants, that sort of thing. Chances are that their circumstances changed after the purchase, but obviously it isn't right and the business wanted to put it right). He did it using temporary tables and it worked like a charm. Muggins here then tried to set up an SSRS report so that it could be delivered automatically to the people that needed it every month. It plain refused to work.
Your report will work fine whichever technique you use, while you are building it. But SSRS almost certainly uses a different permission to you - and crucially, that permission is something like a db_datareader - it can read data, but isn't allowed to create a table, permanent or temporary.
