Updating a table variable
Unlike temporary tables, table variables are not affected by a rollback.As regular variables, they keep the data which was modified during the transaction even if the transaction is rolled back.If you've been a DBA for any amount of time I'm sure you've been asked the question: Which is better to use, a SQL Server temp table or a table variable?There are technical reasons why to use one over the other, but being that I am usually just interested in performance I am more concerned with which one will be faster and use fewer resources.Test Table AS TABLE ( ID INT, Name NVARCHAR(40) ) --Declaring table variable DECLARE @Test Table AS dbo.
For those of us that aren't familiar with all the technical differences between the two objects here is a great tip that covers all you need to know regarding the differences between temporary tables and table variables.
Like regular variables they are not affected by rollback in transactions which could be a big plus depending on your requirements.
Review these tips for more information: Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.
We can't use SELECT INTO or INSERT EXEC statements to initialize a table variable.
Table variables can be passed to stored procedures and functions only as READONLY. User ID --Is referenced using an alias Table variables are a good solution for working with small temporary data, also they allow us to pass a list of values to stored procedures and functions which makes some development needs much simpler.
Let's see this behavior with an example: --Creating table variable DECLARE @Test Table TABLE ( ID INT, Name NVARCHAR(40) ) --Creating temporary table CREATE TABLE #test Table ( ID INT, Name NVARCHAR(40) ) DECLARE @id INT = 0 BEGIN TRANSACTION SET @id = 5 INSERT INTO @Test Table (ID, Name) VALUES (1, 'Name1'), (2, 'Name2') INSERT INTO #test Table (ID, Name) VALUES (1, 'Name1'), (2, 'Name2') ROLLBACK --Selecting data after rollback SELECT @id AS '@id' SELECT * FROM @Test Table SELECT * FROM #test Table Thanks to this feature table variables can be used for auditing purposes.