Пересекать/очищать переменную таблицы в Sql Server 2008

Можно ли усекать или вытеснять переменную таблицы в SQL Server 2008?

Declare @tableVariable table
(
   id int, 
   value varchar(20)
)


while @start<[email protected]

  begin

    insert into @tableVariable(id,value) 
    select id
         , value 
      from xTable 
     where [email protected]

   --Use @tableVariable 

   [email protected] should be flushed out of 
   -- old values before inserting new  values

    set @start = @start + 1 
  end

Ответ 1

просто удалите все

DELETE FROM @tableVariable

Ответ 2

Нет, вы не можете TRUNCATE использовать переменную таблицы, поскольку она не является физической таблицей. Удаление было бы быстрее. См. Этот ответ от Аарон Бертран.

Ответ 3

Я бы добавил к "технически" правильному ответу на использование DELETE @VariableTable, что, если у вас также есть поле Identity в вашей переменной @Table (например, i int (1,1)), и вы хотите, используйте эту таблицу (даже если вы повторно объявите ее в цикле), она все еще находится в пределах видимости, и там она также не может ее повторно выполнить.

Смотрите: Столбец идентификатора таблицы переменных

Лучше использовать #TempTable в этих случаях - тогда вы можете обрезать или использовать DBCC для повторного копирования.
Вы будете получать улучшения производительности с помощью Truncate и сможете создавать дополнительные индексы.
Я думаю, что эмпирическое правило состоит в том, что если вы когда-либо удаляете все, используя DELETE @VariableTable, то вы введете запах кода, который говорит: вместо этого вы должны были использовать #TempTable и TRUNCATE.

Ответ 4

Табличные переменные не поддерживают синтаксис TRUNCATE - единственный способ их усечения неявно, позволяя им выпасть из области видимости.

Как временные таблицы, так и переменные таблицы могут быть кэшированы при использовании в хранимых процедурах, а ниже может быть в конечном итоге та же переменная таблицы, используется после усечения, а не фактического падения и создает

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

Конечно, гораздо проще было бы переключиться на использование таблицы #temp вместо того, чтобы напрямую поддерживать TRUNCATE.

DML как для табличных переменных, так и для временных таблиц записывается в журнал транзакций tempdb. Независимо от того, стоит ли переходить на TRUNCATE, а не DELETE, зависит от размера данных. TRUNCATE будет просто регистрировать освобождение страниц. DELETE будет записывать фактические удаленные значения. Еще одно отличие между ними заключается в том, что TRUNCATE освобождает последнюю страницу из таблицы, а DELETE - нет. Если в каждую итерацию цикла вставляется и удаляется только небольшое количество данных, тогда накладные расходы при протоколировании удаленных строк могут быть меньше, чем накладные расходы от постоянного освобождения и перераспределения одного страницы в таблице.

И наоборот, если вы будете вставлять и удалять большие объемы данных на каждой итерации, вы можете обнаружить, что TRUNCATE не только делает операцию удаления всех строк более эффективной, но также может принести пользу следующий оператор insert.

Ответ 5

Я знаю, что это старый вопрос, но я решил, как это сделать. у нас были таблицы с миллионами строк и они не хотели удалять их из-за пространства журнала транзакций.

Создайте процедуру, которую вы передаете в названии таблицы, которую хотите усечь, процедура создаст другую процедуру, которая выполняет trucate, а затем удалит процедуры.

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
    '[email protected]+'')

-- EXECUTE THE PROCEDURE
    exec [dbo].[ClearOutTable_TEMP]

-- DROP THE PROCEDURE
    drop procedure [dbo].[ClearOutTable_TEMP]

Надеюсь, что это поможет.