T-SQL – UNPIVOT vs Dynamic SQL vs VALUES


Tsql теория > T-SQL – UNPIVOT vs Dynamic SQL vs VALUES
28.05.2018 17:19:36


Наиболее часто встречающиеся слова в статье:

[использовать] [UserBadges] [object_id] [SMALLINT] [BadgeCount] [BadgeType] [столбцов] [column_id] [OBJECT_ID] [UserBadges'']


Статья:

Чтобы задача не казалось абстрактной, предположим, что в нашем распоряжении таблица, содержащая информацию о количестве медалей среди пользователей.

IF OBJECT_ID(''dbo.UserBadges'', ''U'') IS NOT NULL
	DROP TABLE dbo.UserBadges
GO

CREATE TABLE dbo.UserBadges (
	  UserID INT
	, Gold SMALLINT NOT NULL
	, Silver SMALLINT NOT NULL
	, Bronze SMALLINT NOT NULL
	, CONSTRAINT PK_UserBadges PRIMARY KEY (UserID)
)

INSERT INTO dbo.UserBadges (UserID, Gold, Silver, Bronze)
VALUES
	(1, 5, 3, 1),
	(2, 0, 8, 1),
	(3, 2, 4, 11)

Начиная с SQL Server 2008 стало возможным использовать конструкцию VALUES не только для создания многострочных INSERT запросов, но и внутри блока FROM.

Применяя конструкцию VALUES, запрос выше можно переписать так:

SELECT p.UserID, t.*
FROM dbo.UserBadges p
CROSS APPLY (
    VALUES 
          (Gold,   ''Gold'')
        , (Silver, ''Silver'')
        , (Bronze, ''Bronze'')
) t(BadgeCount, BadgeType)

UNPIVOT


С релизом SQL Server 2005, стало возможным использовать новую конструкцию языка T-SQL – UNPIVOT. 

Применяя UNPIVOT предыдущий запрос можно упростить до:

SELECT UserID, BadgeCount, BadgeType
FROM dbo.UserBadges
UNPIVOT (
    BadgeCount FOR BadgeType IN (Gold, Silver, Bronze)
) unpvt

Dynamic SQL


Применяя динамический SQL, есть возможность создать «универсальный» запрос для любой таблицы. Главное условие при этом — столбцы, которые не входят в состав первичного ключа, должны иметь совместимые между собой типы данных.

Узнать список таких столбцов можно следующим запросом:

SELECT c.name
FROM sys.columns c WITH(NOLOCK)
LEFT JOIN (
	SELECT i.[object_id], i.column_id
	FROM sys.index_columns i WITH(NOLOCK)
	WHERE i.index_id = 1
) i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id
WHERE c.[object_id] = OBJECT_ID(''dbo.UserBadges'', ''U'')
	AND i.[object_id] IS NULL

 

В ситуации, когда необходимо произвести простое преобразование столбцов в строки, то наиболее предпочтительно использовать конструкции UNPIVOT или VALUES.

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

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