Is it possible to truncate or flush out a table variable in SQL Server 2008?
declare @tableVariable table
(
id int,
value varchar(20)
)
while @start <= @stop
begin
insert into @tableVariable(id, value)
select id
, value
from xTable
where id = @start
--Use @tableVariable
--@tableVariable should be flushed out of
-- old values before inserting new values
set @start = @start + 1
end
just delete everything
DELETE FROM @tableVariable
No, you cannot TRUNCATE a table variable since it is not a physical table. Deleting it would be faster. See this answer from Aaron Bertrand.
I'd add to the "technically" correct answer of using DELETE @VariableTable that if you happen to also have an Identity-Field in your @Table Variable (e.g. i int (1,1)) and you'd like to re-use this table (even if you re-declare it in a loop) it is still within scope and there it no way to reseed it either.
See: Table Variable Identity Column
It is best to use #TempTable in these cases - then you may Truncate or use DBCC to reseed.
You will reap performance improvements with Truncate and be able to create additional indexes.
I think the rule of thumb is, if you're ever going to delete everything using DELETE @VariableTable, then you've introduced a code-smell that says, you should have used #TempTable and TRUNCATE instead.
DELETE @VariableTable; in which case you will have no choice but to use for a Looped-Sproc with a Table-Parameter. What if your Logic needs a Resettable Identity-Field, or is DML Intensive, or its Population is Complex? In these cases you could still use a Temp-Table, then Insert into a Deleted Table-Variable (IF it is more performant to do so; YMMV). See: Variable vs. TempTable variables do not support TRUNCATE syntax - the only way of truncating them is implicitly by letting them fall out of scope.
Both temporary tables and table variables can be cached when used in stored procedures and the below may well end up with the same table variable being used after truncation rather than an actual drop and create
CREATE PROC dbo.foo @start INT
AS
BEGIN
DECLARE @tableVariable TABLE (
id INT,
value VARCHAR(20))
INSERT INTO @tableVariable
(id,
value)
SELECT id,
value
FROM xTable
WHERE id = @start;
--Use @tableVariable
END
GO
WHILE @start <= @stop
BEGIN
EXEC dbo.foo @start
SET @start = @start + 1
END
Of course a far easier alternative would be to switch to using a #temp table instead as that supports TRUNCATE directly.
DML on both table variables and temp tables writes to the tempdb transaction log. Whether or not it is worth switching to TRUNCATE rather than DELETE depends on the size of data involved. TRUNCATE will just log the page deallocations. DELETE will log the actual deleted values. One other difference between the two is that TRUNCATE deallocates the last page from the table and DELETE doesn't. If only a small quantity of data is inserted and deleted in each loop iteration then the overhead from logging the deleted rows can be less than the overhead from constantly deallocating and reallocating the single page in the table.
Conversely if you will be inserting and deleting large amounts of data on each iteration you may find that TRUNCATE not only makes the operation of deleting all rows more efficient but also can benefit the subsequent insert statement.
DELETE FROM @tableVariable, as described in the accepted answer, to get functionality substantially equivalent to TRUNCATE TABLE (except for the logging - this could certainly be a problem if there were a lot of rows in the variable, or the SQL that created the variable was being run very often).
TRUNCATE has over DELETE of all rows is transaction logging.This is other limitation of the Table variable . Most of the cases we use Truncate command where we want Identity of the table needs to be reset. Suppose you are inserting values into Table variable inside cursor & you need to clear table variable , for each insertion new identity will be populated . In such scenarios , we need to use #temp to get new identity for each fetch for the cursor
--Usage: exec sp_truncateifexists tablename
CREATE PROCEDURE sp_truncateifexists
@tableVariable nvarchar(200)
AS
BEGIN
IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tableVariable )
BEGIN
DECLARE @query nvarchar(250)
SET @query = 'TRUNCATE TABLE ' + @tableVariable
EXECUTE (@query)
END
END
GO
Remember to use #temp tables if you don't need the tables later.
TABLE. Table variables are in-memory only and exist only for the duration of the session in which they are declared. They can be used instead of temp tables in some circumstances, and can be passed as parameters to stored procedures. See official documentation for details.I know this is an old question but i've figured a way to do this. we had tables with millions of rows and didn't want to delete them due to transaction log space.
Create a procedure that you pass in the table name you want to truncate, the procedure will create another procedure that does the trucate and then deletes the procedures.
USE [My_Database]
GO
/****** Object: StoredProcedure [dbo].[ClearOutTable_p1] Script Date: 23/09/2015 09:03:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Oraclebhoy
-- Create date: 23/09/2015
-- Description:
--
-- removes the content of the table passed in through the parameter
-- =============================================
create procedure [dbo].[ClearOutTable_p1]
@tablename varchar(max)
as
-- CREATE THE TRUNCATE STATEMENT PASSING IN TABLE VARIABLE
declare @truncatesql varchar(max)
set @truncatesql = 'truncate table ' + @tablename
-- IF PROCEDURE EXISTS DROP
if exists (select name from sys.all_objects where name = 'ClearOutTable_TEMP'and type = 'P')
begin
drop procedure [dbo].[ClearOutTable_TEMP]
end
-- CREATE TEMP PROCEDURE
exec ('create procedure [dbo].[ClearOutTable_TEMP]
as
'+@truncatesql+'')
-- EXECUTE THE PROCEDURE
exec [dbo].[ClearOutTable_TEMP]
-- DROP THE PROCEDURE
drop procedure [dbo].[ClearOutTable_TEMP]
Hope this helps.
--Use @tableVariableI assume it will be used for some calculations, which aren't enclosed