Updating a table variable

User Role There is a limitation when indexing table variables.Indexes can be created only in the table definition, moreover after declaration, table variables can't be altered.

Test Table AS TABLE ( ID INT, Name NVARCHAR(40) ) --Declaring table variable DECLARE @Test Table AS dbo.

Test Table --Inserting data into table variable INSERT INTO @Test Table(ID, Name) VALUES(1, 'Abc') There is a common misconception that table variables are in memory objects.

In this tip we will compare the performance of a temp table vs a table variable using a few straightforward scenarios.

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.

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.

For example, when we are updating a table and the transaction is rolled back and we want to audit these uncommitted changes, we can store it in a table variable and after the rollback then insert this data into a table for auditing.

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.

Before passing a list of parameters to the procedure we should define the appropriate table type. User Role ( User Role ID INT IDENTITY(1,1), User ID INT, Role ID INT CONSTRAINT IX_User Role_User ID UNIQUE NONCLUSTERED (User ID ASC) ) INSERT INTO dbo. Role ID WHEN NOT MATCHED THEN INSERT (User ID, Role ID) VALUES (source. Role ID); END Take into account that table variables passed to a stored procedure are READONLY.

Tags: , ,