在 Sql Server 2008 中截断/清除表变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22046487/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Truncate/Clear table variable in Sql Server 2008
提问by shrekDeep
Is it possible to truncate or flush out a table variable in sql server 2008 ?
是否可以在 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
回答by peter
just delete everything
删除所有内容
DELETE FROM @tableVariable
回答by TTeeple
No, you cannot TRUNCATEa table variable since it is not a physical table. Deleting it would be faster. See this answer from Aaron Bertrand.
不，您不能TRUNCATE使用表变量，因为它不是物理表。删除它会更快。请参阅Aaron Bertrand 的这个答案。
回答by MikeTeeVee
I'd add to the "technically" correct answer of using DELETE @VariableTablethat if you happen to also have an Identity-Field in your @TableVariable (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.
我想补充到“在技术上采用的”正确的答案DELETE @VariableTable，如果你碰巧也有一个身份场在你的@Table变量（例如i int (1,1)），你想重新使用该表（即使你重新声明它一个循环）它仍在范围内，并且也无法重新播种。
See: Table Variable Identity Column
请参阅：表变量标识列
It is best to use #TempTablein these cases - then you may Truncate or use DBCC to reseed.
You will reap performance improvements with Truncate andbe 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 #TempTableand TRUNCATEinstead.
最好#TempTable在这些情况下使用- 然后您可以截断或使用 DBCC 重新播种。
您将通过 Truncate 获得性能改进，并能够创建其他索引。
我认为经验法则是，如果您打算使用 删除所有内容DELETE @VariableTable，那么您已经引入了一种代码味道，表明您应该使用#TempTable和TRUNCATE代替。
回答by Martin Smith
Table variables do not support TRUNCATEsyntax - the only way of truncating them is implicitly by letting them fall out of scope.
表变量不支持TRUNCATE语法 - 截断它们的唯一方法是让它们超出范围。
Both temporary tables and table variables can be cachedwhen 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 #temptable instead as that supports TRUNCATEdirectly.
当然，更简单的替代方法是改用直接#temp支持的表TRUNCATE。
DML on both table variables and temp tables writes to the tempdbtransaction log. Whether or not it is worth switching to TRUNCATErather than DELETEdepends on the size of data involved. TRUNCATEwill just log the page deallocations. DELETEwill log the actual deleted values. One other difference between the two is that TRUNCATEdeallocates the last page from the table and DELETEdoesn'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 lessthan the overhead from constantly deallocating and reallocating the single page in the table.
表变量和临时表上的 DML 写入tempdb事务日志。是否值得切换TRUNCATE而不是DELETE取决于所涉及的数据大小。TRUNCATE只会记录页面释放。DELETE将记录实际删除的值。两者之间的另一个区别是TRUNCATE解除分配表中的最后一页而DELETE没有。如果在每次循环迭代中仅插入和删除少量数据，则记录已删除行的开销可能小于不断释放和重新分配表中单个页面的开销。
Conversely if you will be inserting and deleting large amounts of data on each iteration you may find that TRUNCATEnot only makes the operation of deleting all rows more efficient but also can benefit the subsequent insert statement.
相反，如果您将在每次迭代中插入和删除大量数据，您可能会发现这TRUNCATE不仅使删除所有行的操作更加高效，而且对后续的插入语句也有好处。
回答by Daniel Holth
--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 tablesif you don't need the tables later.
如果以后不需要这些表，请记住使用 #temp 表。
回答by oraclebhoy
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.
希望这可以帮助。
