Табличная переменная ms sql: MS SQL Server и T-SQL

Что может подстерегать новичков при работе с SQL Server. Часть 2

В отличие от первой статьи, никаких Жозефин, уточек, пьяных альпинистов с комплексом «трёхлитровки» и прочих веселостей тут уже не будет. И не потому, что количество историй вдруг решило уменьшить свое поголовье. Причина кроется в другом: работа с базой данных — это не всегда сырок моцарелла и смузи после 8-часового дня. Есть много аспектов, на которые нужно обращать внимание при написании запросов на T-SQL.

Views

Кто-то любит представления, кто-то нет. Навязывать мнение не использовать view — себе дороже, но знать про несколько особенностей при работе с ними нужно обязательно.

Создаем тестовую таблицу и view на основе нее:

USE tempdb
GO
IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
    DROP TABLE dbo.tbl
GO
CREATE TABLE dbo.tbl (a INT, b INT)
GO
INSERT INTO dbo.tbl VALUES (0, 1)
GO
IF OBJECT_ID('dbo.vw_tbl', 'V') IS NOT NULL
    DROP VIEW dbo.vw_tbl
GO
CREATE VIEW dbo.vw_tbl
AS
    SELECT * FROM dbo. tbl
GO
SELECT * FROM dbo.vw_tbl
GO

Значения возвращаются правильно:

a           b
----------- -----------
0           1

Теперь добавим новый столбец в таблицу и пробуем опять вычитать данные из view:

ALTER TABLE dbo.tbl
    ADD c INT NOT NULL DEFAULT 2
GO
SELECT * FROM dbo.vw_tbl
GO

Получим тот же результат:

a           b
----------- -----------
0           1

А все потому, что нужно либо явно задавать столбцы, либо рекомпилировать скриптовый объект:

EXEC sys.sp_refreshview @viewname = N'dbo.vw_tbl'
GO
SELECT * FROM dbo.vw_tbl
GO

Чтобы получить правильный результат:

a           b           c
----------- ----------- -----------
0           1           2

При прямом обращении к таблице подобного прикола не будет. Что ж, идем дальше.

Есть любители в одном запросе соединить все данные и обернуть это все в одном view. За примером далеко ходить не будем и посмотрим на «хороший паттерн» из AdventureWorks:

ALTER VIEW HumanResources. vEmployee
AS
    SELECT e.BusinessEntityID
         , p.Title
         , p.FirstName
         , p.MiddleName
         , p.LastName
         , p.Suffix
         , e.JobTitle
         , pp.PhoneNumber
         , pnt.[Name] AS PhoneNumberType
         , ea.EmailAddress
         , p.EmailPromotion
         , a.AddressLine1
         , a.AddressLine2
         , a.City
         , sp.[Name] AS StateProvinceName
         , a.PostalCode
         , cr.[Name] AS CountryRegionName
         , p.AdditionalContactInfo
    FROM HumanResources.Employee e
    JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID
    JOIN Person.BusinessEntityAddress bea ON bea.BusinessEntityID = e.BusinessEntityID
    JOIN Person.[Address] a ON a.AddressID = bea.AddressID
    JOIN Person.StateProvince sp ON sp.StateProvinceID = a.StateProvinceID
    JOIN Person.CountryRegion cr ON cr.CountryRegionCode = sp.CountryRegionCode
    LEFT JOIN Person.PersonPhone pp ON pp.BusinessEntityID = p.BusinessEntityID
    LEFT JOIN Person. PhoneNumberType pnt ON pp.PhoneNumberTypeID = pnt.PhoneNumberTypeID
    LEFT JOIN Person.EmailAddress ea ON p.BusinessEntityID = ea.BusinessEntityID

А теперь вопрос: что, если мне нужно получить не всю информацию, а только ее часть? Например, вернуть имя и фамилию работников:

SELECT BusinessEntityID
     , FirstName
     , LastName
FROM HumanResources.vEmployee
SELECT p.BusinessEntityID
     , p.FirstName
     , p.LastName
FROM Person.Person p
WHERE p.BusinessEntityID IN (
        SELECT e.BusinessEntityID
        FROM HumanResources.Employee e
    )

Посмотрим на план выполнения в случае использования view:

Table 'EmailAddress'. Scan count 290, logical reads 640, ...
Table 'PersonPhone'. Scan count 290, logical reads 636, ...
Table 'BusinessEntityAddress'. Scan count 290, logical reads 636, ...
Table 'Person'. Scan count 0, logical reads 897, ...
Table 'Employee'. Scan count 1, logical reads 2, . ..

И сравним с запросом, который мы осмысленно написали ручками:

Table 'Person'. Scan count 0, logical reads 897, ...
Table 'Employee'. Scan count 1, logical reads 2, ...

Оптимизатор в SQL Server сделали весьма умным и на этапе simplification, при построении плана выполнения он умеет отбрасывать неиспользуемые соединения. Однако эффективно делать он может это не всегда. Иногда ему мешает отсутствие валидного внешнего ключа между таблицами, когда нет возможности проверить, повлияет ли соединение на результат выборки. Или, например, когда соединение идет по более чем одному полю. Ну не умеет некоторых вещей оптимизатор, но это же не повод нагружать его лишней работой.

Code style

Стиль написания кода — это строго индивидуальное, но, чтобы не вносить хаос в разработку, все уже давно придерживаются тех или иных правил. Самое парадоксальное, что за все время работы я не видел ни одного вменяемого свода правил при написании запросов. Все их пишут по принципу: «главное, чтобы работало». Хотя потом рискуют хорошо хлебнуть при разворачивании базы на сервере клиента.

Давайте создадим отдельную базу и таблицу в ней:

USE [master]
GO
IF DB_ID('test') IS NOT NULL BEGIN
    ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE test
END
GO
CREATE DATABASE test COLLATE Latin1_General_CI_AS
GO
USE test
GO
CREATE TABLE dbo.Employee (EmployeeID INT PRIMARY KEY)
GO

На время наденем погоны Junior Developer и напишем такой запрос:

select employeeid from employee

Работает? Бесспорно, ведь все на уровне букваря. А теперь попробуйте поменять COLLATE на какой-нибудь регистрозависимый:

ALTER DATABASE test COLLATE Latin1_General_CS_AI

И попробуем повторно проверить нашу удачу:

Msg 208, Level 16, State 1, Line 19
Invalid object name 'employee'.

Оптимизатор использует правила текущего COLLATE при построении плана выполнения. Точнее, на этапе связывания, когда производится проверка на существование таблиц, колонок и других объектов и сопоставление каждого объекта синтаксического дерева с реальным объектом системного каталога.

Если хочется писать запросы, которые будут везде работать, то нужно всегда придерживаться правильного регистра в именах объектов, которые используются в запросе.

Column order

На чем еще спотыкаются — задание в ORDER BY порядкового номера столбца. Эта штука весьма коварная, но не такая хитрая, как предыдущий пример:

USE tempdb
GO
IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
    DROP TABLE dbo.tbl
GO
CREATE TABLE dbo.tbl (a INT, b INT)
GO
INSERT INTO dbo.tbl VALUES (1, 99), (3, 4)
GO
IF OBJECT_ID('dbo.GetLastRecord') IS NOT NULL
    DROP PROCEDURE dbo.GetLastRecord
GO
CREATE PROCEDURE dbo.GetLastRecord
AS
    SELECT TOP(1) *
    FROM dbo.tbl
    ORDER BY 1 DESC
GO
EXEC dbo.GetLastRecord
GO

RecordID    Value
----------- -----------
3           4

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

Вся эта логика может накрыться медным тазом при простой пересоздании таблицы, когда кто-то захочет поменять порядок столбцов:

IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
    DROP TABLE dbo.tbl
GO
CREATE TABLE dbo.tbl (b INT, a INT)
GO
INSERT INTO dbo.tbl VALUES (99, 1), (4, 3)
GO
EXEC dbo.GetLastRecord
GO

Value       RecordID
----------- -----------
99          1

Потому что сортировка будет идти уже по другому столбцу. Однако это не самое страшное.

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

USE AdventureWorks2014
GO
IF OBJECT_ID('dbo.AWBuildVersion2', 'U') IS NOT NULL
    DROP TABLE dbo.AWBuildVersion2
GO
CREATE TABLE dbo.AWBuildVersion2 (
      InfoID TINYINT PRIMARY KEY
    , VersionDate DATE NOT NULL
    , ModifiedDate DATE NOT NULL
)
GO
INSERT INTO dbo.AWBuildVersion2
SELECT SystemInformationID
     , VersionDate
     , ModifiedDate
FROM dbo. AWBuildVersion
GO

Предположим, кто-то опять сделал диверсию и пересоздал таблицу с новым порядком:

IF OBJECT_ID('dbo.AWBuildVersion2', 'U') IS NOT NULL
    DROP TABLE dbo.AWBuildVersion2
GO
CREATE TABLE dbo.AWBuildVersion2 (
      InfoID TINYINT PRIMARY KEY
    , ModifiedDate DATE NOT NULL
    , VersionDate DATE NOT NULL
)
GO
INSERT INTO dbo.AWBuildVersion2
SELECT SystemInformationID
     , VersionDate
     , ModifiedDate
FROM dbo.AWBuildVersion
GO

В этом случае у нас данные будут записываться уже некорректные:

InfoID VersionDate ModifiedDate
------ ----------- ------------
1      2014-02-20  2014-07-08

InfoID VersionDate ModifiedDate
------ ----------- ------------
1      2014-07-08  2014-02-20

Поэтому считается хорошим тоном не лениться и всегда явно указывать столбцы в конструкции INSERT:

INSERT INTO dbo.AWBuildVersion2 (InfoID, VersionDate, ModifiedDate)
SELECT SystemInformationID
     , VersionDate
     , ModifiedDate
FROM dbo. AWBuildVersion

Data length

Нужно всегда указывать размерность типа, чтобы не натыкаться на подобного рода грабли:

DECLARE @a DECIMAL
      , @b VARCHAR(10) = '0.1'
      , @c SQL_VARIANT
SELECT @a = @b
     , @c = @a
SELECT @a
     , @c
     , SQL_VARIANT_PROPERTY(@c,'BaseType')
     , SQL_VARIANT_PROPERTY(@c,'Precision')
     , SQL_VARIANT_PROPERTY(@c,'Scale')

В чем суть данной проблемы? Явно не указали размерность типа и вместо дробного значения получаем «вроде целое»:

---- ---- ---------- ----- -----
0    0    decimal    18    0

Со строками все еще веселее:

DECLARE @t1 VARCHAR(MAX) = '123456789_123456789_123456789_123456789_'
DECLARE @t2 VARCHAR = @t1
SELECT LEN(@t1)
     , @t1
     , LEN(@t2)
     , @t2
     , LEN(CONVERT(VARCHAR, @t1))
     , LEN(CAST(@t1 AS VARCHAR))

Если явно не указывается размерность, то у строки длина будет 1 символ:

----- ------------------------------------------ ---- ---- ---- ----
40    123456789_123456789_123456789_123456789_   1    1    30   30

При этом поведение преобразовании типов имеет свою особенность: не указали размерность в CAST/CONVERT, то браться будут первые 30 символов.

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

STRING_CONCAT

Я бы мог посоветовать использовать функцию STRING_CONCAT, если бы она была… На дворе 2016 год, а отдельной функции, чтобы склеивать строки, в SQL Server так и не добавили. Нужно же как-то выходить из положения?

Рассмотрим пару наиболее популярных вариантов, но вначале создадим тестовую таблицу:

IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t
GO
CREATE TABLE #t (i CHAR(1))
INSERT INTO #t
VALUES ('1'), ('2'), ('3')

И начнем с моего «любимца» — конкатенация строк через присваивание значений в переменную:

DECLARE @txt VARCHAR(50) = ''
SELECT @txt += i
FROM #t
--ORDER BY i
SELECT @txt

--------
123

Все работает, но сам MS намекает, что данные способ недокументированный, и никто не застрахован от такого результата:

DECLARE @txt VARCHAR(50) = ''
SELECT @txt += i
FROM #t
ORDER BY LEN(i)
SELECT @txt

--------
3

Скажу честно, сам в первый раз долго разбирался, почему у меня отчет по бухгалтерской проводке только последнюю строку показывает. После этого прикола было много еще чего: CLR, UPDATE, временные таблицы, рекурсия, циклы… И это все чтобы склеить строки.

На практике, в 90% случаев достаточно использовать XML:

SELECT [text()] = i
FROM #t
FOR XML PATH('')

--------
123

Однако и тут нас может поджидать пара нюансов. Во-первых, очень часто необходимо склеить строки в разрезе каких-то данных, а не все в одно:

SELECT t.name
     , STUFF((
            SELECT ', ' + c.name
            FROM sys.columns c
            WHERE c.[object_id] = t.[object_id]
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM sys.objects t
WHERE t.[type] = 'U'

------------------------ ------------------------------------
ScrapReason              ScrapReasonID, Name, ModifiedDate
Shift                    ShiftID, Name, StartTime, EndTime

При этом крайне желательно избегать использования XML метода для парсинга — они очень ресурсоемкие:

Теперь пробуем не использовать value:

SELECT t. name
     , STUFF((
            SELECT ', ' + c.name
            FROM sys.columns c
            WHERE c.[object_id] = t.[object_id]
            FOR XML PATH('')), 1, 2, '')
FROM sys.objects t
WHERE t.[type] = 'U'

И такой вариант будет работать хорошо и быстро, если не одно «но». Попробуйте выполнить вот такой запрос:

SELECT t.name
     , STUFF((
            SELECT ', ' + CHAR(13) + c.name
            FROM sys.columns c
            WHERE c.[object_id] = t.[object_id]
            FOR XML PATH('')), 1, 2, '')
FROM sys.objects t
WHERE t.[type] = 'U'

Если в строках встречаются спецсимволы, вроде табуляции, перевода строки и прочее, то мы будем получать не совсем корректные результаты:

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

Subquery

Без лишних прелюдий выполним запрос:

USE AdventureWorks2014
GO
SELECT p. BusinessEntityID
     , (
        SELECT s.SalesQuota
        FROM Sales.SalesPersonQuotaHistory s
        WHERE s.BusinessEntityID = p.BusinessEntityID
      )
FROM Person.Person p

и получим ошибку:

Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Как решаются подобные проблемы? Элементарно — добавляется TOP(1), и проблема ушла. Однако не все так просто, как может показаться. Использование операции TOP(…) заставляет оптимизатор форсировать использование IndexSeek. К таким же последствиям приводит использованием OUTER/CROSS APPLY вместе с TOP.

К примеру, есть запросы:

SELECT p.BusinessEntityID
     , (
        SELECT TOP(1) s.SalesQuota
        FROM Sales.SalesPersonQuotaHistory s
        WHERE s.BusinessEntityID = p.BusinessEntityID
        ORDER BY s.QuotaDate DESC
      )
FROM Person. Person p
SELECT p.BusinessEntityID
     , t.SalesQuota
FROM Person.Person p
OUTER APPLY (
    SELECT TOP(1) s.SalesQuota
    FROM Sales.SalesPersonQuotaHistory s
    WHERE s.BusinessEntityID = p.BusinessEntityID
    ORDER BY s.QuotaDate DESC
) t

У них одна и та же проблема на плане выполнения:

Table 'SalesPersonQuotaHistory'. Scan count 19972, logical reads 39944, ...
Table 'Person'. Scan count 1, logical reads 67, ...

Вооружившись оконной функцией, перепишем запрос :

SELECT p.BusinessEntityID
     , t.SalesQuota
FROM Person.Person p
LEFT JOIN (
    SELECT s.BusinessEntityID
         , s.SalesQuota
         , RowNum = ROW_NUMBER() OVER (PARTITION BY s.BusinessEntityID ORDER BY s.QuotaDate DESC)
    FROM Sales.SalesPersonQuotaHistory s
) t ON p.BusinessEntityID = t.BusinessEntityID AND t.RowNum = 1

И посмотрим что изменилось:

Table 'Person'. Scan count 1, logical reads 67, ...
Table 'SalesPersonQuotaHistory'.  Scan count 1, logical reads 4, ...

Думаю, что комментарии излишни. Поэтому перейдем к десерту.

@TableVariable vs #TempTable

Существует извечный холивар относительно того, что лучше использовать: табличные переменные или временные таблицы. Говорят, что первые живут исключительно в оперативной памяти, а вторые хранятся на диске. И поэтому табличные переменные лучше использовать для хранения небольших наборов данных, а временные таблицы — для всего остального. Так ли это?

На самом деле табличные переменные и временные таблицы физически хранятся одинаково — как таблицы в базе tempdb. Исключение составляют InMemory табличные типы.

USE [master]
GO
IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t
GO
SELECT COUNT(*) FROM tempdb.sys.tables WHERE [name] LIKE '#_%'
GO
DECLARE @t TABLE (id INT)
SELECT COUNT(*) FROM tempdb.sys.tables WHERE [name] LIKE '#_%'
CREATE TABLE #t (id INT)
SELECT COUNT(*) FROM tempdb.sys.tables WHERE [name] LIKE '#_%'

-----------
0
-----------
1
-----------
2

На этом основное сходство заканчивается, и начинаются грабли. Сперва мои самые любимые, которые часто попадаются на собеседованиях Middle/Senior DB Developer:

USE [master]
GO
IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t
GO
DECLARE @t TABLE (id INT)
CREATE TABLE #t (id INT)
BEGIN TRANSACTION
INSERT INTO @t VALUES (2)
INSERT INTO #t VALUES (2)
ROLLBACK
SELECT COUNT(*) FROM @t
SELECT COUNT(*) FROM #t

-----------
1
-----------
0

Временные таблицы работают по аналогии с обычными таблицами и отражают все изменения в логе, которые мы и откатили с помощью ROLLBACK.

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

DECLARE @t TABLE (id INT PRIMARY KEY)
INSERT INTO @t VALUES (1)
INSERT INTO @t VALUES (1), (2)
SELECT * FROM @t

Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint 'PK__#AD25707__3213E83F1AADB62D'.  Cannot insert duplicate key in object '[email protected]'. The duplicate key value is (1).

id
-----------
1

В чем еще разница? Перечислять много, но мы остановимся на самом главном. Во временных таблицах все по аналогии с обычными таблицами — используется статистика и показывается кардинальность, более или менее адекватная:

USE AdventureWorks2014
GO
IF OBJECT_ID('tempdb.dbo.#SalesOrderDetail') IS NOT NULL
    DROP TABLE #SalesOrderDetail
CREATE TABLE #SalesOrderDetail (ProductID INT PRIMARY KEY)
INSERT INTO #SalesOrderDetail (ProductID)
SELECT DISTINCT ProductID
FROM Sales.SalesOrderDetail
SET STATISTICS IO ON
SELECT *
FROM Production.Product p
WHERE p.ProductID IN (SELECT s.ProductID FROM #SalesOrderDetail s)
SET STATISTICS IO OFF

Table 'Product'. Scan count 1, logical reads 15, ...
Table '#SalesOrderDetail_000000000030'. Scan count 1, logical reads 2, ...

У табличных переменных нет статистики, и кардинальность всегда равна единице:

USE AdventureWorks2014
GO
DECLARE @SalesOrderDetail TABLE (ProductID INT PRIMARY KEY)
INSERT INTO @SalesOrderDetail (ProductID)
SELECT DISTINCT ProductID
FROM Sales. SalesOrderDetail
SET STATISTICS IO ON
SELECT *
FROM Production.Product p
WHERE p.ProductID IN (SELECT s.ProductID FROM @SalesOrderDetail s)
SET STATISTICS IO OFF
GO

Table 'Product'. Scan count 0, logical reads 532, ...
Table '#A28D5CEC'. Scan count 1, logical reads 2, ...

Именно по этой причине при использовании табличной переменной могут строиться не оптимальные планы выполнения:

Есть еще куча всего по мелочи. Например, использование табличных переменных мешает оптимизатору выбирать параллельные планы выполнения. Также использование временных таблиц приводит к рекомпиляции плана, в то же время табличная переменная таким недостатком не обладает. Можете поверить на слово — это все мелочи по сравнению с неверной оценкой кардинальности при использовании табличных переменных.

А вот лечить данную проблему можно по-разному: использовать временные таблицы либо избирательно добавлять OPTION(RECOMPILE) к проблемным запросам. Хотя постойте, еще стоит упомянуть про trace flag 2453, который можно использовать, начиная с SQL Server 2012 SP2 и SQL Server 2014 CU3.

На этом пока все. Знаю, конечно, что за бортом осталось еще много чего интересного, но все же надеюсь, что этот поток мыслей кому-то будет полезным.

Все про українське ІТ в телеграмі — підписуйтеся на канал редакції DOU

Теми:
junior, SQL Server, tech, розробка

Разработка серверной части ИС в СУБД MS SQL Server. Особенности настройки свойств таблиц. Общие рекомендации по выбору типа данных, страница 6

Информатика и выч. техника \
Базы данных

Функции Multi-statement комбинируют способность
скалярных функций содержать сложный программный код со способностью внедренных
табличных функций возвращать результирующий набор данных в виде таблицы. Этот
тип функций создает табличную переменную, а затем заполняет ее в теле функции.
Сформированная таблица возвращается функцией и может использоваться в
инструкциях SELECT. Преимущество функций Multi-statement — возможность
формировать результат с помощью пакета инструкций, а затем напрямую
использовать этот набор данных в инструкциях SELECT.

В общем виде синтаксис табличной функции с множеством
инструкций можно представить в виде:

Пример.

1.4. Хранимые процедуры

При создании приложения, интегрированного с SQL
Server, возникает проблема организации программного взаимодействия с БД. Для
решения этой задачи имеются две основные возможности:

·  формировать команды SQL на стороне клиента, а
затем посылать эти команды на сервер для выполнения;

·  перенести часть программного кода на сторону
сервера.

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

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

Основное преимущество хранимых процедур -
высокая оперативность обработки информации, обусловленная использованием, как
правило, мощного компьютера-сервера и тем, что для доступа к данным не тратится
лишнее время, поскольку БД размещается на сервере.

В сравнении с использованием динамических операторов
языка SQL хранимые процедуры работают быстрее, т.к. они
компилируются при первом выполнении с использованием оптимизации доступа к
информации из таблиц.

По области видимости различают хранимые процедуры
следующих четырех типов: системные (System),
локальные (Local), временные (Temporary)
и удаленные (Remote).

Системные хранимые процедуры размещаются в системной БД Master,
используются сервером и администратором. Имена системных процедур начинаются с
символов sp_. Для создания собственной системной хранимой процедуры нужно присвоить
ей имя, начинающееся с sp_, и поместить ее в системную БД Master.
Системные хранимые процедуры предназначены для выполнения различных
административных действий. Практически все действия по администрированию
сервера выполняются с их помощью. Можно сказать, что системные хранимые
процедуры являются интерфейсом, обеспечивающим работу с системными таблицами.
Системные хранимые процедуры могут быть вызваны в контексте любой другой БД.

Локальные хранимые процедуры размещаются в пользовательских БД.

Временные хранимые процедуры имеют имена, начинающиеся с символов #
или ##. Процедуры с одним символом # в начале имени являются временными
локальными процедурами и доступны из соединения (сессии), в котором они
были созданы. При закрытии этого соединения процедура автоматически
уничтожается. Процедуры с двумя символами ## в начале имени являются временными
глобальными процедурами и доступны в любой сессии работы с данным
сервером.

Удаленные хранимые процедуры можно вызывать с сервера, отличного от
текущего сервера. При этом текущий сервер играет роль промежуточного звена.
Кроме того, удаленную хранимую процедуру можно вызвать непосредственно с сервера,
на котором она находится.

Еще одним типом хранимых процедур являются расширенные
хранимые процедуры (extended stored procedures). Они пишутся на языке программирования
(например, С). Расширенные хранимые процедуры оформляются как функции в составе
библиотек динамических связей — DLL (Dynamic Link Library), что повышает скорость их выполнения. Имена
расширенных хранимых процедур начинаются с символов хр_.

Скачать файл

Табличные переменные В T-SQL

Microsoft представила табличные переменные с SQL Server 2000 в качестве альтернативы использованию временных таблиц. Во многих случаях табличная переменная может превзойти решение с использованием временной таблицы, хотя в этой статье нам нужно будет рассмотреть сильные и слабые стороны каждой из них.

Табличные переменные хранят набор записей, поэтому, естественно, синтаксис объявления очень похож на оператор CREATE TABLE, как вы можете видеть в следующем примере:

 DECLARE @ProductTotals TABLE
(
  ID продукта целое,
  Выручка
)
 

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

 ВСТАВЬТЕ В @ProductTotals (ProductID, Доход)
  ВЫБЕРИТЕ ProductID, СУММА (цена за единицу * количество)
    ОТ [Детали заказа]
    СГРУППИРОВАТЬ ПО ProductID
 

Табличные переменные можно использовать в пакетах, хранимых процедурах и пользовательских функциях (UDF). Мы можем ОБНОВИТЬ записи в нашей табличной переменной, а также УДАЛИТЬ записи.

 ОБНОВЛЕНИЕ @ProductTotals
  УСТАНОВИТЬ доход = доход * 1,15
ГДЕ ProductID = 62


УДАЛИТЬ ИЗ @ProductTotals
ГДЕ ProductID = 60


ВЫБЕРИТЕ ТОП 5 *
ОТ @ProductTotals
ЗАКАЗАТЬ ПО доходу DESC
 

Вы можете подумать, что табличные переменные работают так же, как временные таблицы (CREATE TABLE #ProductTotals), но есть некоторые отличия.

Объем

В отличие от большинства других типов данных в SQL Server, вы не можете использовать табличную переменную в качестве входного или выходного параметра. На самом деле табличная переменная относится к хранимой процедуре, пакету или определяемой пользователем функции точно так же, как и любая локальная переменная, которую вы создаете с помощью оператора DECLARE. Переменная больше не будет существовать после выхода из процедуры — не будет таблицы для очистки с помощью инструкции DROP.

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

Ограниченная область действия табличной переменной дает SQL Server некоторую свободу для выполнения оптимизации.

Производительность

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

Использование временной таблицы внутри хранимой процедуры может привести к дополнительным повторным компиляциям хранимой процедуры. Табличные переменные часто могут избежать этой перекомпиляции. Дополнительные сведения о том, почему хранимые процедуры могут перекомпилироваться, см. в статье 243586 базы знаний Майкрософт (INF: устранение неполадок при перекомпиляции хранимых процедур).

Прочие характеристики

Ограничения — это отличный способ убедиться, что данные в таблице соответствуют определенным требованиям, и вы можете использовать ограничения с табличными переменными. Следующий пример гарантирует, что значения ProductID в таблице будут уникальными, а все цены меньше 10,0.

 ОБЪЯВИТЬ СТОЛ @MyTable
(
  ProductID целое УНИКАЛЬНОЕ,
  Цена денег CHECK(Цена < 10.0)
)
 

Вы также можете объявить первичные ключи. столбцы идентификаторов и значения по умолчанию.

 ОБНОВЛЕНИЕ @ProductTotals
ОБЪЯВИТЬ ТАБЛИЦУ @MyTable
(
  ProductID int IDENTITY(1,1) ПЕРВИЧНЫЙ КЛЮЧ,
  Имя varchar(10) НЕ NULL ПО УМОЛЧАНИЮ('Неизвестно')
)
 

Пока кажется, что табличные переменные могут делать все то же, что и временные таблицы в рамках хранимой процедуры, пакета или UDF), но есть некоторые недостатки.

Ограничения

Вы не можете создать некластеризованный индекс для табличной переменной, если индекс не является побочным эффектом ограничения PRIMARY KEY или UNIQUE для таблицы (SQL Server применяет любые ограничения UNIQUE или PRIMARY KEY с помощью индекса).

Кроме того, SQL Server не поддерживает статистику по табличной переменной, и статистика активно используется оптимизатором запросов для определения наилучшего метода выполнения запроса. Однако ни одно из этих ограничений не должно быть проблемой, поскольку табличные переменные обычно существуют для определенной цели и не используются для широкого круга специальных запросов.

Табличное определение табличной переменной не может измениться после оператора DECLARE. Любой запрос ALTER TABLE, пытающийся изменить табличную переменную, завершится ошибкой синтаксиса. Точно так же вы не можете использовать табличную переменную с запросами SELECT INTO или INSERT EXEC. Если вы используете табличную переменную в объединении, вам потребуется псевдоним таблицы, чтобы выполнить запрос.

 ВЫБЕРИТЕ ProductName, Доход
ИЗ ПРОДУКЦИИ Р
  ВНУТРЕННЕЕ СОЕДИНЕНИЕ @ProductTotals PT ON P.ProductID = PT.ProductID
 

Вы можете использовать табличную переменную с динамическим SQL, но вы должны объявить таблицу внутри самого динамического SQL. Следующий запрос завершится с ошибкой «Необходимо объявить переменную @MyTable».

 ОБЪЯВИТЬ СТОЛ @MyTable
(
  ID продукта целое,
  Имя varchar(10)
)


EXEC sp_executesql N'SELECT * FROM @MyTable'
 

Также важно отметить, что табличные переменные не участвуют в откате транзакций. Хотя это может быть преимуществом в производительности, оно также может застать вас врасплох, если вы не знаете о поведении. Чтобы продемонстрировать, следующий пакет запросов вернет 77 записей, несмотря на то, что INSERT был выполнен внутри транзакции с ROLLBACK.

 DECLARE @ProductTotals TABLE
(
  ID продукта целое,
  Выручка
)
 
НАЧАТЬ СДЕЛКУ
 
  ВСТАВЬТЕ В @ProductTotals (ProductID, Доход)
    ВЫБЕРИТЕ ProductID, СУММА (цена за единицу * количество)
    ОТ [Детали заказа]
    СГРУППИРОВАТЬ ПО ProductID
 
ОТМЕНА ТРАНЗАКЦИИ
 
ВЫБЕРИТЕ СЧЕТ(*) ИЗ @ProductTotals
 

Выбор между временными таблицами и табличными переменными

Теперь вы подошли к хранимой процедуре, которой требуется временное хранилище набора результатов. Зная, что мы узнали до сих пор, как вы решите использовать табличную переменную или временную таблицу?

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

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

Пример: Разделить

Табличные переменные — лучшая альтернатива использованию временных таблиц во многих ситуациях. Возможность использовать табличную переменную в качестве возвращаемого значения UDF — одно из лучших применений табличных переменных. В следующем примере мы рассмотрим общую потребность: функцию для разбора строки с разделителями на части. Другими словами, учитывая строку «1,5,9” — мы хотим вернуть таблицу с записью для каждого значения: 1, 5 и 9.

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

.

 SELECT * FROM fn_Split('foo,bar,widget', ',')
 

вернет следующий набор результатов.

 значение позиции
1 фу
2 бар
3 виджет

 

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

, если существует (выберите * из dbo.sysobjects, где id = ob-ject_id(N'[dbo].[fn_Split]') и xtype в (N'FN', N'IF', N'TF'))
функция сброса [dbo].[fn_Split]
ИДТИ

ВЫКЛЮЧИТЬ QUOTED_IDENTIFIER
ИДТИ
ВЫКЛЮЧИТЬ ANSI_NULLS
ИДТИ

СОЗДАТЬ ФУНКЦИЮ fn_Split(@text varchar(8000), @delimiter varchar(20) = ' ')
ВОЗВРАТ @Strings ТАБЛИЦА
(
  позиция int ИДЕНТИФИКАЦИОННЫЙ ПЕРВИЧНЫЙ КЛЮЧ,
  значение varchar(8000)
)
КАК
НАЧИНАТЬ

ОБЪЯВИТЬ @index int
УСТАНОВИТЕ @индекс = -1

ПОКА (ДЛСТР(@текст) > 0)

  НАЧИНАТЬ
    SET @index = CHARINDEX(@delimiter , @text)
    ЕСЛИ (@index = 0) И (LEN(@text) > 0)
      НАЧИНАТЬ
        ВСТАВИТЬ В @Strings VALUES (@text)
          ПЕРЕРЫВ
      КОНЕЦ

    ЕСЛИ (@индекс > 1)
      НАЧИНАТЬ
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
        SET @text = RIGHT(@text, (LEN(@text) - @index))
      КОНЕЦ
    ЕЩЕ
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    КОНЕЦ
  ВОЗВРАЩАТЬСЯ

КОНЕЦ
ИДТИ

ВЫКЛЮЧИТЬ QUOTED_IDENTIFIER
ИДТИ

УСТАНОВИТЕ ANSI_NULLS ВКЛ. 
ИДТИ
 

Резюме

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

К. Скотт Аллен

Помощник SQL Server

 Сервер: сообщение 1077, уровень 16, состояние 1, строка 1
ВСТАВИТЬ в столбец идентификаторов, не разрешенный в таблице
переменные.  

 DECLARE @Company TABLE (
    [CompanyID] INT NOT NULL IDENTITY(1, 1),
    [CompanyName] NVARCHAR(100) НЕ NULL,
    [Адрес] VARCHAR(100) НЕ NULL,
    [Город] VARCHAR(50) НЕ NULL,
    [Состояние] CHAR(2) НЕ NULL,
    [Почтовый индекс] VARCHAR(10) НЕ NULL
)

INSERT INTO @Company ( [CompanyID], [CompanyName], [Address], [City], [State], [ZIPCode])
ЗНАЧЕНИЯ ( 101, 'Facebook', '1 Hacker Way', 'Менло Парк', 'Калифорния', '94025')

INSERT INTO @Company ( [CompanyID], [CompanyName], [Address], [City], [State], [ZIPCode])
ЗНАЧЕНИЯ ( 102, 'Google', '1600 Amphitheatre Pkwy', 'Mountain View', 'CA', '94043' )

INSERT INTO @Company ( [CompanyID], [CompanyName], [Address], [City], [State], [ZIPCode])
ЗНАЧЕНИЯ ( 103, 'Microsoft', 'One Microsoft Way', 'Редмонд', 'WA', '98052' ) 

 Сообщение 1077, уровень 16, состояние 1, строка 10
ВСТАВЬТЕ в столбец идентификатора, который не разрешен для табличных переменных. 
Сообщение 1077, уровень 16, состояние 1, строка 13
ВСТАВЬТЕ в столбец идентификатора, который не разрешен для табличных переменных.
Сообщение 1077, уровень 16, состояние 1, строка 16
ВСТАВЬТЕ в столбец идентификатора, который не разрешен для табличных переменных. 

 УСТАНОВИТЬ IDENTITY_INSERT @Company ON

INSERT INTO @Company ( [CompanyID], [CompanyName], [Address], [City], [State], [ZIPCode])
ЗНАЧЕНИЯ ( 101, «Facebook», «1 Hacker Way», «Menlo Park», «CA», «94025») 

 Сообщение 102, уровень 15, состояние 1, строка 4
Неверный синтаксис рядом с '@Company'.  

 DECLARE @Company TABLE (
    [CompanyID] INT NOT NULL IDENTITY(1, 1),
    [CompanyName] NVARCHAR(100) НЕ NULL,
    [Адрес] VARCHAR(100) НЕ NULL,
    [Город] VARCHAR(50) НЕ NULL,
    [Состояние] CHAR(2) НЕ NULL,
    [Почтовый индекс] VARCHAR(10) НЕ NULL
)

ВСТАВЬТЕ В @Company ( [CompanyName], [Address], [City], [State], [ZIPCode])
ЗНАЧЕНИЯ («Facebook», «1 Hacker Way», «Menlo Park», «CA», «94025')

ВСТАВЬТЕ В @Company ( [CompanyName], [Address], [City], [State], [ZIPCode])
ЗНАЧЕНИЯ ("Google", "1600 Amphitheatre Pkwy", "Mountain View", "CA", "94043")

ВСТАВЬТЕ В @Company ( [CompanyName], [Address], [City], [State], [ZIPCode])
ЗНАЧЕНИЯ ("Microsoft", "One Microsoft Way", "Redmond", "WA", "98052") 

 ВЫБЕРИТЕ * ОТ @Company 

 CompanyID CompanyName Адрес Город Штат Почтовый индекс
 ---------- ------------ -------- ----- --------- ----- --------
 1 Facebook 1 Hacker Way Menlo Park CA 94025
 2.
Назад: Отключить windows 10: Отключение антивирусной защиты в Защитнике Windows на панели «Безопасность Windows»
Вперед: Программы на пк лучшие: Лучшие программы для ПК скачать бесплатно 2023 без регистрации на русском языке — Мои Программы