TRUNCATE TABLE – DDL-операция для быстрой очистки таблицы

Данный оператор является DDL-операцией и служит для быстрой очистки таблицы – удаляет все строки из нее. За более детальными подробностями обращайтесь в MSDN.

Некоторые вырезки из MSDN. TRUNCATE TABLE – удаляет все строки в таблице, не записывая в журнал удаление отдельных строк. Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов системы и журналов транзакций.

Если таблица содержит столбец идентификаторов (столбец с опцией IDENTITY), счетчик этого столбца сбрасывается до начального значения, определенного для этого столбца. Если начальное значение не задано, используется значение по умолчанию, равное 1. Чтобы сохранить столбец идентификаторов, используйте инструкцию DELETE.

Инструкцию TRUNCATE TABLE нельзя использовать если на таблицу ссылается ограничение FOREIGN KEY. Таблицу, имеющую внешний ключ, ссылающийся сам на себя, можно усечь.

 

Пример:

TRUNCATE TABLE EmployeesBonusTarget

 

Заключение по операциям модификации данных

Здесь я наверно повторю, все что писал ранее.

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

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

Не забывайте перед очень серьезными изменениями делать резервные копии, хотя бы той информации, которая будет подвергнута модификации, это можно сделать при помощи SELECT … INTO …

Помните, что модификация данных это очень серьезно.

Приложение 1 – бонус по оператору SELECT

Подумав, я решил дописать этот раздел для тех, кто дошел до конца.

В данном разделе я дам примеры с использованием некоторых расширенных конструкций:

·PIVOT

·UNPIVOT

·GROUP BY ROLLUP

·GROUP BY GROUPING SETS

Попробуйте разобрать каждый из следующих примеров самостоятельно, анализируя результаты выполнения запросов. Обращайте внимание на комментарии, которые я указал в текстах запросов, некоторые важные вещи указаны в них.

Получение сводных отчетов при помощи GROUP BY+CASE и конструкции PIVOT

Для начала давайте посмотрим, как можно создать сводный отчет при помощи конструкции GROUP BY и CASE-условий. Можно сказать, это классический способ создания сводных отчетов:

-- получение сводной таблицы при помощи GROUP BY

SELECT

EmployeeID,

SUM(CASE WHEN MONTH(BonusDate)=1 THEN BonusAmount END) BonusAmount1,

SUM(CASE WHEN MONTH(BonusDate)=2 THEN BonusAmount END) BonusAmount2,

SUM(CASE WHEN MONTH(BonusDate)=3 THEN BonusAmount END) BonusAmount3,

SUM(CASE WHEN MONTH(BonusDate)=4 THEN BonusAmount END) BonusAmount4,

SUM(CASE WHEN MONTH(BonusDate)=5 THEN BonusAmount END) BonusAmount5,

SUM(CASE WHEN MONTH(BonusDate)=6 THEN BonusAmount END) BonusAmount6,

SUM(CASE WHEN MONTH(BonusDate)=7 THEN BonusAmount END) BonusAmount7,

SUM(CASE WHEN MONTH(BonusDate)=8 THEN BonusAmount END) BonusAmount8,

SUM(CASE WHEN MONTH(BonusDate)=9 THEN BonusAmount END) BonusAmount9,

SUM(CASE WHEN MONTH(BonusDate)=10 THEN BonusAmount END) BonusAmount10,

SUM(CASE WHEN MONTH(BonusDate)=11 THEN BonusAmount END) BonusAmount11,

SUM(CASE WHEN MONTH(BonusDate)=12 THEN BonusAmount END) BonusAmount12,

SUM(BonusAmount) TotalBonusAmount

FROM EmployeesBonus

WHERE BonusDate BETWEEN '20140101' AND '20141231' -- отберем данные за 2014 год

GROUP BY EmployeeID

 

Теперь рассмотрим, как получить эти же данные при помощи конструкции PIVOT:

-- получение сводной таблицы при помощи PIVOT

SELECT

EmployeeID,

[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],

ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)+

ISNULL([5],0)+ISNULL([6],0)+ISNULL([7],0)+ISNULL([8],0)+

ISNULL([9],0)+ISNULL([10],0)+ISNULL([11],0)+ISNULL([12],0) TotalBonusAmount

FROM

(

/*

в данном подзапросе мы отберем только необходимые для свода данные:

   - поля BonusMonth и BonusAmount будут задействованы в конструкции PIVOT

   - прочие поля, в данном случае это только EmployeeID, будут использованны для группировки данных

*/

SELECT

EmployeeID,

MONTH(BonusDate) BonusMonth,

BonusAmount

FROM EmployeesBonus

WHERE BonusDate BETWEEN '20140101' AND '20141231'

) q

PIVOT(SUM(BonusAmount) FOR BonusMonth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) p

 

В конструкции PIVOT кроме SUM, как вы думаю догадались, можно использовать и другие агрегатные функции (COUNT, AVG, MIN, MAX, …).

Конструкция UNPIVOT

Давайте теперь рассмотрим, как работает конструкция UNPIVOT. Для демонстрации сбросим сводный результат в таблицу DemoPivotTable:

SELECT

EmployeeID,

SUM(CASE WHEN MONTH(BonusDate)=1 THEN BonusAmount END) BonusAmount1,

SUM(CASE WHEN MONTH(BonusDate)=2 THEN BonusAmount END) BonusAmount2,

SUM(CASE WHEN MONTH(BonusDate)=3 THEN BonusAmount END) BonusAmount3,

SUM(CASE WHEN MONTH(BonusDate)=4 THEN BonusAmount END) BonusAmount4,

SUM(CASE WHEN MONTH(BonusDate)=5 THEN BonusAmount END) BonusAmount5,

SUM(CASE WHEN MONTH(BonusDate)=6 THEN BonusAmount END) BonusAmount6,

SUM(CASE WHEN MONTH(BonusDate)=7 THEN BonusAmount END) BonusAmount7,

SUM(CASE WHEN MONTH(BonusDate)=8 THEN BonusAmount END) BonusAmount8,

SUM(CASE WHEN MONTH(BonusDate)=9 THEN BonusAmount END) BonusAmount9,

SUM(CASE WHEN MONTH(BonusDate)=10 THEN BonusAmount END) BonusAmount10,

SUM(CASE WHEN MONTH(BonusDate)=11 THEN BonusAmount END) BonusAmount11,

SUM(CASE WHEN MONTH(BonusDate)=12 THEN BonusAmount END) BonusAmount12,

SUM(BonusAmount) TotalBonusAmount

INTO DemoPivotTable -- сбросим сводный результат в таблицу

FROM EmployeesBonus

WHERE BonusDate BETWEEN '20140101' AND '20141231'

GROUP BY EmployeeID

 

Первым делом посмотрите, как у нас выглядят данные в данной таблице:

SELECT *

FROM DemoPivotTable

 

Теперь применим к данной таблице конструкцию UNPIVOT:

-- демонстрация UNPIVOT

SELECT

*,

CAST(REPLACE(ColumnLabel,'BonusAmount','') AS int) BonusMonth

FROM DemoPivotTable

UNPIVOT(BonusAmount FOR ColumnLabel IN(BonusAmount1,BonusAmount2,BonusAmount3,BonusAmount4,

                                    BonusAmount5,BonusAmount6,BonusAmount7,BonusAmount8,

                                    BonusAmount9,BonusAmount10,BonusAmount11,BonusAmount12)) u

 

Обратите внимание, что NULL значения не войдут в результат.

Как вы наверно догадались, на месте таблицы может стоять и подзапрос с заданным для него псевдонимом.