Введение в MS SQL Server и T-SQL. Семь смертных грехов программиста на T-SQL Сортировка по нескольким столбцам

Язык Т-SQL предназначен для управления наборами данных. По этой причине он не обладает некоторыми характерными чертами традиционных языков, которые необходимы для программирования приложений. Если вы уже давно занимаетесь созданием приложений, то наверняка противопоставите мышление программирования в Т-SQL и в других языках, таких как VB, C# и Java.

Пакеты T-SQL

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

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

Прерывание выполнения пакета

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

Разделитель пакетов на самом деле является функцией Management Studio, а не самого сервера. Его можно изменить на странице Query Execution диалогового окна свойств программы, но я не рекомендовал бы этого делать (по крайней мере, друзьям).

Инструкции DDL

Некоторые инструкции DDL языка Т-SQL, такие как Create Procedure, обязательно должны быть первыми инструкциями пакета. Очень длинные сценарии, которые создают множество объектов, часто требуют наличия нескольких разделителей пакетов. Так как SQL Server отдельно разбирает синтаксис по пакетам, такое наличие множества разделителей помогает локализовать ошибки.

Переключение между базами данных

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

Выполнение пакетов

Пакет может быть выполнен несколькими способами.

Сценарий SQL в полном объеме (т.е. все входящие в него пакеты) может быть выполнен путем открытия файла. sql в редакторе SQL утилиты Manage ment Stu dio и нажатия клавиши (или щелчка на кнопке! Execute панели инструментов, или выбора в меню пункта Query 1 ^Execute). (Я настроил свою операционную систему Windows так, чтобы при двойном щелчке на файле. sql автоматически запускался анализатор запросов.)

В редакторе SQL утилиты Management Studio могут быть выполнены и отдельные инструкции SQL. Для этого их нужно выделить и нажать клавишу (или щелкнуть на кнопке! Execute панели инструментов, или выбрать в меню пункт Query 1 ^Execute).

В приложении пакет Т-SQL можно выполнить с помощью ADO или ODBC.

О Сценарий Т-SQL может быть выполнен с помощью утилиты командной строки SQLCmd с передачей ей имени файла. sql в качестве параметра.

Утилита SQLCmd имеет несколько параметров и может быть легко сконфигурирована практически для любых нужд.

Дополнительная Подробно об утилите SQLCmd см. в главе 6, посвященной Management Studio.

информация

Выполнение хранимой процедуры

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

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

Следующие два вызова системной хранимой процедуры демонстрируют использование команды ехес в пакете:

ЕХЕС sp_help;

В этом разделе мы рассмотрели только использование команды ехес в пакете. Более подробная информация о творческом использовании ключевого слова ехес содержится в разделе “Динамический SQL”.

Форматирование в T-SQL

На протяжении всей этой книги программный код отформатирован для улучшения наглядности; в этом разделе мы рассмотрим ключевые моменты форматирования.

Завершение инструкции

Стандарт ANSI SQL требует помещения в конце каждой инструкции точки с запятой. В то же время при программировании на языке Т-SQL точка с запятой не обязательна. При этом следует руководствоваться несколькими правилами.

Не помещайте ее после оператора try end.

Не помещайте ее после условия if.

Обязательно помещайте ее после общетабличных выражений СТЕ.

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

Продление строк

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

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

Комментарии

Язык Т-SQL допускает использование в одном пакете комментариев двух стилей: ANCI и языка С. Первый из них начинается с двух дефисов и заканчивается в конце строки:

— Это комментарий стиля ANSI

Также комментарии стиля ANSI могут вставляться в конце строки инструкции:

Select FirstName, LastName – извлекаемые столбцы FROM Persons – исходная таблица

Where LastName Like ‘Hal%"; — ограничение на строки

Редактор SQL может применять и удалять комментарии во всех выделенных строках. Для этого нужно соответственно выбрать команду меню Edit^Advanced^Comment Out ( или ) или Edit^ Advanced 1 ^ Remove Comments ( или ).

Комментарии стиля языка С начинаются с косой черты и звездочки (/*) и заканчиваются теми же символами в обратной последовательности. Этот тип комментариев лучше использовать для комментирования блоков строк, таких как заголовки или большие тестовые запросы. /*

Триггер вставки таблицы Order Пол Нильсен

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

Отладка T-SQL

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

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

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

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

(1 row(s) affected)

Иногда полезно приостанавливать выполнение программы, чтобы увидеть блокировки или содержимое объектов. Команда pause позволяет приостановить выполнение пакета на заданное время. Например, при выполнении следующего кода его вторая строка вывода отобразится после двухсекундной паузы:

Print 1 Начало";

waitfor delay *00:00:02′ ;

Print ‘Конец 1 ;

Результат выполнения кода:

Ключевым моментом является то, что в утилиту Management Studio версии SQL Новинка Server 2005 не включен отладчик языка Т-SQL, - он присутствует в пакете

2005 а Visual Studio 2005. Если в будущем отладчик будет включен в какой-либо пакет

обновлений, я сообщу об этом на сайте www. SQLServerBible. com.

Переменные

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

Значения по умолчанию и область определения переменных

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

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

DECLARE @Test INT,

@TestTwo NVARCHAR(25);

SELECT @Test, @TestTwo;

SET @TestTwo = ‘значение";

SELECT @Test, @TestTwo ;

SELECT @Test as BatchTwo, @TestTwo;

(1 row(s) affected)

1 значение

(1 row(s) affected)

Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable “@Test”.

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

Эти переменные имеют локальную область определения, которая не распространяется на другие пакеты и хранимые процедуры.

Использование команд set и select

Команды SET и SELECT могут использоваться для присвоения значений переменным. Основным отличием между ними является то, что команда SELECT может извлекать информацию из источника данных (т.е. таблицы, подзапроса, представления, и т.п.) и включать в себя другие инструкции SELECT, в то время как команда SET ограничена извлечением данных из выражений. Как одна, так и другая команда может содержать функции. Используйте более простую команду SET, когда требуется присвоить переменной результат функции или константу и не требуется рассматривать какой-либо источник данных.

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

Declare ©TempID INT,

@TempLastName VARCHAR(25);

SET @TempID = 99;

@TempID = PersonID,

@TempLastName = LastName

ORDER BY PersonID;

Результат выполнения пакета:

32 @code last:Campbell

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

Если инструкция SELECT не возвращает ни одной строки, то на переменные не оказывается никакого влияния. Следующий запрос не возвращает значений, поскольку записи с идентификатором 100 в таблице Person не существует. По этой причине переменной @TempIDvariable присваивается значение последней существующей строки, при этом переменная фамилии сохраняет изначальное пустое значение:

Declare @TempID INT,

@TempLastName VARCHAR(25);

SET @TempID = 99;

SELECT @TempID = PersonID,

@TempLastName = LastName FROM Person WHERE PersonID = 100 ORDER BY PersonID;

SELECT @TempID, @TempLastName;

99 @code last:NULL

Условный отбор

Следующая инструкция SELECT содержит предложение WHERE, и ее синтаксис правильный, хотя для некоторых может выглядеть непривычно:

SELECT @переменная = выражение WHERE булево_выражение;

В данном случае предложение WHERE функционирует как условный оператор if. Если булево выражение истинно, то переменной присваивается значение, в противном случае инструкция SELECT все равно выполняется, но значение переменной не изменяется.

Использование переменных в запросах SQL

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

Везде, где в запросе может использоваться выражение, может использоваться и переменная. В следующем примере продемонстрировано использование переменной в предложении WHERE: USE OBXKites;

DECLARE @ProductCode CHAR(10);

SET @Code = ‘1001’;

SELECT ProductName FROM Product

WHERE Code = @ProductCode;

Будет получен следующий результат:

Basic Box Kite 21 inch

Переменные с множественным присвоением

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

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

SELECT @переменная = @переменная + d.столбец FROM (управляемая_таблица) as d;

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

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

В следующем примере создается список дат событий тура Outer Banks Lighthouses, предлагаемого в учебной базе данных Cape Hatter as Ad ventures:

@EventDates VARCHAR(1024);

SET @EventDates = ‘ ‘ ;

SELECT ©EventDates = @EventDates + CONVERT(VARCHAR(15), a.d,107) + ‘

FROM (select DateBegin as [d] from Event join Tour

on Event.TourID = Tour.TourlD WHERE Tour. = ‘Outer Banks Lighthouses’) as a;

SELECT Left(@EventDates, Len(@EventDates)-1)

AS ‘Outer Banks Lighthouses Events";

Результат выполнения пакета:

Outer Banks Lighthouses Events

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

Последнее обновление: 24.06.2017

SQL Server является одной из наиболее популярных систем управления базами данных (СУБД) в мире. Данная СУБД подходит для самых различных проектов: от небольших приложений до больших высоконагруженных проектов.

SQL Server был создан компанией Microsoft. Первая версия вышла в 1987 году. А текущей версией является версия 16, которая вышла в 2016 году и которая будет использоваться в текущем руководстве.

SQL Server долгое время был исключительно системой управления базами данных для Windows, однако начиная с версии 16 эта система доступна и на Linux.

SQL Server характеризуется такими особенностями как:

Производительность. SQL Server работает очень быстро.

Надежность и безопасность. SQL Server предоставляет шифрование данных.

Простота. С данной СУБД относительно легко работать и вести администрирование.

Центральным аспектом в MS SQL Server, как и в любой СУБД, является база данных. База данных представляет хранилище данных, организованных определенным способом. Нередко физически база данных представляет файл на жестком диске, хотя такое соответствие необязательно. Для хранения и администрирования баз данных применяются системы управления базами данных (database management system) или СУБД (DBMS). И как раз MS SQL Server является одной из такой СУБД.

Для организации баз данных MS SQL Server использует реляционную модель. Эта модель баз данных была разработана еще в 1970 году Эдгаром Коддом. А на сегодняшний день она фактически является стандартом для организации баз данных.

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

Для идентификации каждой строки в рамках таблицы применяется первичный ключ (primary key). В качестве первичного ключа может выступать один или несколько столбцов. Используя первичный ключ, мы можем ссылаться на определенную строку в таблице. Соответственно две строки не могут иметь один и тот же первичный ключ.

Через ключи одна таблица может быть связана с другой, то есть между двумя таблицами могут быть организованы связи. А сама таблица может быть представлена в виде отношения ("relation").

Для взаимодействия с базой данных применяется язык SQL (Structured Query Language). Клиент (например, внешняя программа) отправляет запрос на языке SQL посредством специального API. СУБД должным образом интерпретирует и выполняет запрос, а затем посылает клиенту результат выполнения.

Изначально язык SQL был разработан в компании IBM для системы баз данных, которая называлась System/R. При этом сам язык назывался SEQUEL (Structured English Query Language). Хотя в итоге ни база данных, ни сам язык не были впоследствии официально опубликованы, по традиции сам термин SQL нередко произносят как "сиквел".

В 1979 году компания Relational Software Inc. разработала первую систему управления баз данных, которая называлась Oracle и которая использовала язык SQL. В связи с успехом данного продукта компания была переименована в Oracle.

Впоследствии стали появляться другие системы баз данных, которые использовали SQL. В итоге в 1989 году Американский Национальный Институт Стандартов (ANSI) кодифицировал язык и опубликовал его первый стандарт. После этого стандарт периодически обновлялся и дополнялся. Последнее его обновление состоялось в 2011 году. Но несмотря на наличие стандарта нередко производители СУБД используют свои собственные реализации языка SQL, которые немного отличаются друг от друга.

Выделяются две разновидности языка SQL: PL-SQL и T-SQL. PL-SQL используется в таких СУБД как Oracle и MySQL. T-SQL (Transact-SQL) применяется в SQL Server. Собственно поэтому в рамках текущего руководства будет рассматриваться именно T-SQL.

В зависимости от задачи, которую выполняет команда T-SQL, он может принадлежать к одному из следующих типов:

DDL (Data Definition Language / Язык определения данных). К этому типу относятся различные команды, которые создают базу данных, таблицы, индексы, хранимые процедуры и т.д. В общем определяют данные.

В частности, к этому типу мы можем отнести следующие команды:

CREATE : создает объекты базы данных (саму базу даных, таблицы, индексы и т.д.)

ALTER : изменяет объекты базы данных

DROP : удаляет объекты базы данных

TRUNCATE : удаляет все данные из таблиц

DML (Data Manipulation Language / Язык манипуляции данными). К этому типу относят команды на выбору данных, их обновление, добавление, удаление - в общем все те команды, с помощью которыми мы можем управлять данными.

К этому типу относятся следующие команды:

SELECT : извлекает данные из БД

UPDATE : обновляет данные

INSERT : добавляет новые данные

DELETE : удаляет данные

DCL (Data Control Language / Язык управления доступа к данным). К этому типу относят команды, которые управляют правами по доступу к данным. В частности, это следующие команды:

GRANT : предоставляет права для доступа к данным

REVOKE : отзывает права на доступ к данным

SQL (Structured Query Language) - это универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных (язык структурированных запросов).

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

В настоящее время широко распространенны следующие спецификации SQL:

Базы данных и спецификации SQL

Тип базы данных	Спецификация SQL
Microsoft SQL	Transact-SQL
Microsoft Jet/Access	Jet SQL
MySQL	SQL/PSM (SQL/Persistent Stored Module)
Oracle	PL/SQL (Procedural Language/SQL)
IBM DB2	SQL PL (SQL Procedural Language)
InterBase/Firebird	PSQL (Procedural SQL)

В данной статье будет рассмотрена спецификация Transact-SQL, которая используется серверами Microsoft SQL. А так как база у всех спецификаций SQL одинаковая, то большинство команд и сценариев с легкостью переносятся на другие типы SQL.

Определение

Transact-SQL - это процедурное расширение языка SQL компаний Microsoft. SQL был расширен такими дополнительными возможностями как:

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

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

Опытная база данных

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

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

Создание базы данных USE master CREATE DATABASE TestDatabase GO -- Создание таблиц USE TestDatabase CREATE TABLE Users (UserID int PRIMARY KEY, UserName nvarchar(40), UserSurname nvarchar(40), DepartmentID int, PositionID int) CREATE TABLE Departments (DepartmentID int PRIMARY KEY, DepartmentName nvarchar(40)) CREATE TABLE Positions (PositionID int PRIMARY KEY, PositionName nvarchar(40), BaseSalary money) CREATE TABLE (CustomerID int PRIMARY KEY, CustomerName nvarchar(40), CustomerAddress nvarchar(255)) CREATE TABLE (OrderID int PRIMARY KEY, CustomerID int, UserID int, text) GO -- Заполнение таблиц USE TestDatabase INSERT Users VALUES (1, "Ivan", "Petrov", 1, 1) INSERT Users VALUES (2, "Ivan", "Sidorov", 1, 2) INSERT Users VALUES (3, "Petr", "Ivanov", 1, 2) INSERT Users VALUES (4, "Nikolay", "Petrov", 1, 3) INSERT Users VALUES (5, "Nikolay", "Ivanov", 2, 1) INSERT Users VALUES (6, "Sergey", "Sidorov", 2, 3) INSERT Users VALUES (7, "Andrey", "Bukin", 2, 2) INSERT Users VALUES (8, "Viktor", "Rybakov", 4, 1) INSERT Departments VALUES (1, "Production") INSERT Departments VALUES (2, "Distribution") INSERT Departments VALUES (3, "Purchasing") INSERT Positions VALUES (1, "Manager", 1000) INSERT Positions VALUES (2, "Senior analyst", 650) INSERT VALUES (1, "Alex Company", "606443, Russia, Bor, Lenina str., 15") INSERT VALUES (2, "Potrovka", "115516, Moscow, Promyshlennaya str., 1") INSERT VALUES (1, 1, 1, "Special parts") GO

Примечание. В Microsoft SQL Server 2000 запросы выполняются в приложении Query Analyzer. В Microsoft SQL Server 2005 запросы выполняются в SQL Server Management Studio.

В результате работы сценария на SQL сервере будет создана база данных TestDatabase с пятью пользовательскими таблицами: Users, Departments, Positions, Local Customers, Local Orders.

Users

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
3	Petr	Ivanov	1	3
4	Nikolay	Petrov	1	3
5	Nikolay	Ivanov	2	1
6	Sergey	Sidorov	2	3
7	Andrey	Bukin	2	3
8	Viktor	Rybakov	4	1

Positions

PositionID	PositionName	BaseSalary
1	Manager	1000
2	Senior analyst	650
3	Analyst	400

Local Orders

OrderID	CustomerID	UserID	Description
1	1	1	Special parts

Departments

DepartmentID	DepartmentName
1	Production
2	Distribution
3	Purchasing

Local Customers

CustomerID	CustomerName	CustomerAddress
1	Alex Company	606443, Russia, Bor, Lenina str., 15
2	Potrovka	115516, Moscow, Promyshlennaya str., 1

Элементы синтаксиса

Директивы сценария

Директивы сценария - это специфические команды, которые используются только в MS SQL. Эти команды помогают серверу определять правила работы со скриптом и транзакциями. Типичные представители: GO - сигнализирует SQL-серверу об окончании сценария, EXEC (или EXECUTE) - выполняет процедуру или скалярную функцию.

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

-- - строковый комментарий исключает из выполнения только одну строку, перед которой стоят два минуса.
/* */ - блоковый комментарий исключает из выполнения целый блок команд, заключенный в указанную конструкцию.

Типы данных

Как и в языках программирования, в SQL существуют различные типы данных для хранения переменных:

Числа - для хранения числовых переменных (int, tinyint, smallint, bigint, numeric, decimal, money, smallmoney, float, real).
Даты - для хранения даты и времени (datetime, smalldatetime).
Символы - для хранения символьных данных (char, nchar, varchar, nvarchar).
Двоичные - для хранения бинарных данных (binary, varbinary, bit).
Большеобъемные - типы данных для хранения больших бинарных данных (text, ntext, image).
Специальные - указатели (cursor), 16-байтовое шестнадцатиричное число, которое используется для GUID (uniqueidentifier), штамп изменения строки (timestamp), версия строки (rowversion), таблицы (table).

Примечание. Для использования русских символов (не ASCII кодировки) испольюзуются типы данных с приставкой "n" (nchar, nvarchar, ntext), которые кодируют символы двумя байтами. Иначе говоря, для работы с Unicode используются типы данных с "n".

Примечание. Для данных переменной длины используются типы данных с приставкой "var". Типы данных без приставки "var" имеют фиксированную длину области памяти, неиспользованная часть которой заполняется пробелами или нулями.

Идентификаторы

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

@ - идентификатор локальной переменной (пользовательской).
@@ - идентификатор глобальной переменной (встроенной).
# - идентификатор локальной таблицы или процедуры.
## - идентификатор глобальной таблицы или процедуры.
- идентификатор группировки слов в переменную.

Переменные

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

Объявление переменной выполняется командой DECLARE, задание значения переменной осуществляется либо командой SET, либо SELECT:

USE TestDatabase -- Объявление переменных DECLARE @EmpID int, @EmpName varchar(40) -- Задание значения переменной @EmpID SET @EmpID = 1 -- Задание значения переменной @EmpName SELECT @EmpName = UserName FROM Users WHERE UserID = @EmpID -- Вывод переменной @EmpName в результат запроса SELECT @EmpName AS GO

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

Операторы

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

Арифметические операторы: "*" - умножить, "/" - делить, "%" - модуль от деления, "+" - сложить, "-" - вычесть, "()" - скобки.
Операторы сравнения: "=" - равно, ">" - больше, "<" - меньше, ">=" - больше или равно, "<=" меньше или равно, "<>" - не равно.
Операторы соединения: "+" - соединение строк.
Логические операторы: "AND" - и, "OR" - или, "NOT" - не.

Cистемные функции

Спецификация Transact-SQl значительно расширяет стандартные возможности SQL благодаря встроенным функциям:

Агрегативные функции- функции, которые работают с коллекциями значений и выдают одно значение. Типичные представители: AVG - среднее значение колонки, SUM - сумма колонки, MAX - максимальное значение колонки, COUNT - количество элементов колонки.
Скалярные функции- это функции, которые возвращают одно значение, работая со скалярными данными или вообще без входных данных. Типичные представители: DATEDIFF - разница между датами, ABS - модуль числа, DB_NAME - имя базы данных, USER_NAME - имя текущего пользователя, LEFT - часть строки слева.
Функции-указатели- функции, которые используются как ссылки на другие данные. Типичные представители: OPENXML - указатель на источник данных в виде XML-структуры, OPENQUERY - указатель на источник данных в виде другого запроса.

Примечание. Полный список функций можно найти в справке к SQL серверу.

Примечание. К скалярным функциям можно также отнести и глобальные переменные, которые в тексте сценария вызываются двойной собакой "@@".

USE TestDatabase -- Использование агрегативной функции для подсчета средней зарплаты SELECT AVG(BaseSalary) AS FROM Positions GO -- Использование скалярной функции для получения имени базы данных SELECT DB_NAME() AS GO -- Использование скалярной функции для получения имени текущего пользователя DECLARE @MyUser char(30) SET @MyUser = USER_NAME() SELECT "The current user""s database username is: "+ @MyUser GO -- Использование функции-указателя для получения данных с другого сервера SELECT * FROM OPENQUERY(OracleSvr, "SELECT name, id FROM owner.titles") GO

Выражения

Выражение - это комбинация символов и операторов, которая получает на вход скалярную величину, а на выходе дает другую величину или исполняет какое-то действие. В Transact-SQL выражения делятся на 3 типа: DDL, DCL и DML.

DDL (Data Definition Language)- используются для создания объектов в базе данных. Основные представители данного класса: CREATE - создание объектов, ALTER - изменение объектов, DROP - удаление объектов.
DCL (Data Control Language)- предназначены для назначения прав на объекты базы данных. Основные представители данного класса: GRANT - разрешение на объект, DENY - запрет на объект, REVOKE - отмена разрешений и запретов на объект.
DML (Data Manipulation Language)- используются для запросов и изменения данных. Основные представители данного класса: SELECT - выборка данных, INSERT - вставка данных, UPDATE - изменение данных, DELETE - удаление данных.

USE TestDatabase -- Использование DDL CREATE TABLE TempUsers (UserID int, UserName nvarchar(40), DepartmentID int) GO -- Использование DCL GRANT SELECT ON Users TO public GO -- Использование DML SELECT UserID, UserName + " " + UserSurname AS FROM Users GO -- Использование DDL DROP TABLE TempUsers GO

Управление выполнением сценария

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

Блок группировки - структура, объединяющая список выражений в один логический блок (BEGIN … END).
Блок условия - структура, проверяющая выполнения определенного условия (IF … ELSE).
Блок цикла - структура, организующая повторение выполнения логического блока (WHILE … BREAK … CONTINUE).
Переход - команда, выполняющая переход потока выполнения сценария на указанную метку (GOTO).
Задержка - команда, задерживающая выполнение сценария (WAITFOR)
Вызов ошибки - команда, генерирующая ошибку выполнения сценария (RAISERROR)

Динамическое конструирование выражений

Итак, поняв основы Transact-SQL и попрактиковавшись на простых примерах, можно перейти к более сложным структурам. Обычно базы данных создаются и заполняются с помощью сценариев (скриптов) - хотя визуальный редактор прост в обращении, но им никогда быстро и без недочетов не создашь большую базу данных и не заполнишь ее данными. Если вспомнить начало статьи, то опытная база данных как раз создавалась и заполнялась с помощью сценария. Сценарий - это одно или более выражений, объединенных в логический блок, которые автоматизируют работу администратора.

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

USE master -- Задание динамических данных DECLARE @dbname varchar(30), @tablename varchar(30), @column varchar(30) SET @dbname = "TestDatabase" SET @tablename = "Positions" SET @column = "BaseSalary" -- Использование динамических данных EXECUTE ("USE " + @dbname + " SELECT AVG(" + @column + ") AS FROM " + @tablename) GO

Выборка данных

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

SELECT <названия колонок или *> FROM <название таблицы>

По умолчанию в команде SELECT используется параметр ALL, который можно не указывать. Если в команде указать параметр DISTINCT, то в результат попадут только уникальные (неповторяющиеся) записи из выборки.

Для того, чтобы изменить имена объектов в командах к SQL-серверу, используется команда AS. Использование этой команды помогает сокращать длину строки запроса, а так же получать результат в более удобочитаемом виде.

Выбрать все записи из таблицы Local Customers SELECT * FROM -- Выбрать уникальные записи колонки UserName из таблицы Users SELECT DISTINCT UserName FROM Users

UserName
Andrey
Ivan
Nikolay
Petr
Sergey
Viktor

Фильтрация данных осуществляется с помощью команды WHERE, в которой используются следующие операторы и команды сравнения: =, <, >, <=, >=, <>, LIKE, NOT LIKE, AND, OR, NOT, BETWEEN, NOT BETWEEN, IN, NOT IN, IS NULL, IS NOT NULL. В общем виде команда SELECT с фильтром выглядит так:

SELECT <названия колонок или *> FROM <название таблицы> WHERE <условие>

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

% - любое количество символов;
_ - один символ;
- любой символ, указанный в скобках;
[^] - любой символ, не указанный в скобках.

-- Выбрать все записи из таблицы Users, где DepartmentID = 1 SELECT * FROM Users WHERE DepartmentID = 1

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
3	Petr	Ivanov	1	2
4	Nikolay	Petrov	1	3

-- Выбрать все записи из таблицы Users, у кого в имени есть буква A SELECT * FROM Users WHERE UserName LIKE "%a%"

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
4	Nikolay	Petrov	1	3
5	Nikolay	Ivanov	2	1
7	Andrey	Bukin	2	2

-- Выбрать все записи из таблицы Users, у кого в имени вторая буква не V SELECT * FROM Users WHERE UserName LIKE "_[^v]%"

Фильтрация позволяет использовать подзапросы, то есть конструировать запрос из нескольких подзапросов:

Выбрать записи колонки PositionID из таблицы Positions, где BaseSalary < 600 SELECT PositionID FROM Positions WHERE BaseSalary < 600 -- Выбрать все записи из таблицы Users, у кого имя Ivan или Andrey SELECT * FROM Users WHERE UserName IN ("Ivan", "Andrey")

Для сортировки данных в выборке используется командаORDER BY, но следует учесть, что эта команда не сортирует данные типа text, ntext и image. По умолчанию сортировка производится по возрастанию, поэтому параметр ASC в этом случае можно не указывать:

SELECT <названия колонок или *> FROM <название таблицы> WHERE <условие> ORDER BY <названия колонок>

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

SELECT TOP [количество строк] <названия колонок или *> FROM <название таблицы> WHERE <условие> ORDER BY <названия колонок>

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

AVG(колонка) - среднее значение колонки;
COUNT(колонка) - количество не NULL элементов колонки;
COUNT(*) - количество элементов запроса;
MAX(колонка) - максимальное значение в колонке;
MIN(колонка) - минимальное значение в колонке;
SUM(колонка) - сумма значений в колонке.

Примеры использования команд ORDER, TOP и функций агрегирования:

Выбрать 3 первые уникальные записи колонки UserName из таблицы Users, -- отсортированных по возрастанию UserName SELECT DISTINCT TOP 3 UserName FROM Users ORDER BY UserName -- Найти величину максимального оклада в организации SELECT MAX(BaseSalary) FROM Positions -- Найти сотрудников, у кого максимальный оклад в организации SELECT * FROM Users WHERE PositionID IN (SELECT PositionID FROM Positions WHERE BaseSalary IN (SELECT MAX(BaseSalary) FROM Positions))

Группировка данных

SQL позволяет производить группировку данных по определенным полям таблицы. Чтобы сгруппировать данные по какому-нибудь параметру, в SQL-запросе необходимо написать команду GROUP BY, в которой указать имя колонки, по которой производится группировка. Колонки, упомянутые в команде GROUP BY, должны присутствовать в команде SELECT, а так же команда SELECT должна содержать функцию агрегирования, которая будет применена к сгруппированным данным.

Найти количество работников в каждом отделе (сгруппировать работников по -- идентификатору отделов и сосчитать количество записей в каждой группе) SELECT DepartmentID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID

DepartmentID	Number of users
1	4
2	3
4	1

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

Найти количество работников в первом отделе (сгруппировать работников по -- идентификатору отделов, сосчитать количество записей в каждой группе и -- вывести в результат только отдел с идентификатором равным 1) SELECT DepartmentID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID HAVING DepartmentID = 1 -- Найти количество работников с определенной должностью в каждом отделе -- (сгруппировать работников по идентификатору должностей и отделов и -- сосчитать количество записей в каждой группе), а также сосчитать -- количество работников в каждом отделе и общее количество работников SELECT DepartmentID, PositionID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID, PositionID WITH ROLLUP

DepartmentID	PositionID	Number of users
1	1	1
1	2	2
1	3	1
1	NULL	4
2	1	1
2	2	1
2	3	1
2	NULL	3
4	1	1
4	NULL	1
NULL	NULL	8

Команда группировки также может дополняться оператором WITH CUBE, который дополняет формирует всевозможные комбинации из группируемых колонок: если есть N колонок, то получится 2^N комбинаций.

Найти количество работников с определенной должностью в каждом отделе -- (сгруппировать работников по идентификатору должностей и отделов и -- сосчитать количество записей в каждой группе), а также сосчитать -- количество работников по каждой должности, по каждому отделу и -- общее количество работников SELECT DepartmentID, PositionID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID, PositionID WITH CUBE

DepartmentID	PositionID	Number of users
1	1	1
1	2	2
1	3	1
1	NULL	4
2	1	1
2	2	1
2	3	1
2	NULL	3
4	1	1
4	NULL	1
NULL	NULL	8
NULL	1	3
NULL	2	3
NULL	3	2

Функция агрегирования GROUPING позволяет определить, была ли запись добавлена командами ROLLUP и CUBE, или это запись получена из источника данных.

Найти количество работников в каждом отделе (сгруппировать работников по -- идентификатору отделов и сосчитать количество записей в каждой группе) -- а так же пометить дополнительные строки, несуществующие в источнике данных SELECT DepartmentID, COUNT(UserID) AS "Number of users", GROUPING(DepartmentID) AS "Added row" FROM Users GROUP BY DepartmentID WITH ROLLUP

DepartmentID	Number of users	Added row
1	4	0
2	3	0
4	1	0
NULL	8	1

Еще одна команда группировки COMPUTE позволяет группировать данные и выводить по ним отчет в разные таблицы. То есть команда GROUP BY с операторами ROLLUP и CUBE группирует данные и дописывает в таблицу дополнительны строки с отчетом, а команда COMPUTE группирует данные, разрывая исходную таблицу на несколько подтаблиц, а также формирует подтаблицы с отчетами. Команда COMPUTE может использоваться в двух режимах:

как простая функция агрегирования, выводящая результат в отдельную таблицу;
с параметром BY как команда группировки, разрезающая таблицу на несколько подтаблиц

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

Вывести таблицу пользователей компании, а также посчитать их количество SELECT * FROM Users COMPUTE COUNT(UserID)

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
3	Petr	Ivanov	1	2
4	Nikolay	Petrov	1	3
5	Nikolay	Ivanov	2	1
6	Sergey	Sidorov	2	3
7	Andrey	Bukin	2	2
8	Viktor	Rybakov	4	1

cnt
8

-- Найти количество работников в каждом отделе (сгруппировать работников по -- идентификатору отделов и сосчитать количество записей в каждой группе) SELECT * FROM Users ORDER BY DepartmentID COMPUTE COUNT(UserID) BY DepartmentID

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
3	Petr	Ivanov	1	2
4	Nikolay	Petrov	1	3

cnt
4

UserID	UserName	UserSurname	DepartmentID	PositionID
5	Nikolay	Ivanov	2	1
6	Sergey	Sidorov	2	3
7	Andrey	Bukin	2	2

cnt
3

UserID	UserName	UserSurname	DepartmentID	PositionID
8	Viktor	Rybakov	4	1

cnt
1

Соединение таблиц

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

Соединять таблицы в SQL можно двумя способами: вертикально и горизонтально.

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

Найти всех пользователей с именем Ivan и соединить результат с -- результатом от запроса "Найти всех пользователей с фамилией Petrov" -- дублирующие записи исключить SELECT * FROM Users WHERE UserName = "Ivan" UNION SELECT * FROM Users WHERE UserSurname = "Petrov"

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
4	Nikolay	Petrov	1	3

-- Найти всех пользователей с именем Ivan и соединить результат с -- результатом от запроса "Найти всех пользователей с фамилией Petrov" -- дублирующие записи сохранить SELECT * FROM Users WHERE UserName = "Ivan" UNION ALL SELECT * FROM Users WHERE UserSurname = "Petrov"

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
1	Ivan	Petrov	1	1
4	Nikolay	Petrov	1	3

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

SELECT <названия колонок или *> FROM <таблица_1> INNER JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Чтобы выполнить сцепление по всем полям левой таблицы, независимо, есть ли такие записи в правой таблице, необходимо использовать команду LEFT JOIN. Эта команда соединяет таблицы, выбирая все строки из левой таблицы, а отсутствующие данные правой таблицы заполняются значением NULL.

SELECT <названия колонок или *> FROM <таблица_1> LEFT JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Команда RIGHT JOIN аналогична предыдущей, разница заключается лишь в том, что она соединяет таблицы, выбирая все строки из правой таблицы, а отсутствующие данные левой таблицы заполняются значением NULL.

SELECT <названия колонок или *> FROM <таблица_1> RIGHT JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Команда FULL JOIN объединяет в себе левое и правое сцепление, то есть она соединяет таблицы, выбирая строки из обоих таблиц, а отсутствующие данные заполняются значением NULL.

SELECT <названия колонок или *> FROM <таблица_1> FULL JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Последняя и редкоиспользуемая команда соединения таблиц - это CROSS JOIN. Эта команда сцепляет таблицы без использования ключевого поля, а результат - это комбинация из всевозможных строк исходных таблиц.

SELECT <названия колонок или *> FROM <таблица_1> CROSS JOIN таблица_2

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

SELECT * FROM Users INNER JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Petr	Ivanov	1	2	1	Production
4	Nikolay	Petrov	1	3	1	Production
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution

SELECT * FROM Users LEFT JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Petr	Ivanov	1	2	1	Production
4	Nikolay	Petrov	1	3	1	Production
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution
8	Viktor	Rybakov	4	1	NULL	NULL

SELECT * FROM Users RIGHT JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Petr	Ivanov	1	2	1	Production
4	Nikolay	Petrov	1	3	1	Production
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution
NULL	NULL	NULL	NULL	NULL	3	Purchasing

SELECT * FROM Users FULL JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Petr	Ivanov	1	2	1	Production
4	Nikolay	Petrov	1	3	1	Production
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution
NULL	NULL	NULL	NULL	NULL	3	Purchasing
8	Viktor	Rybakov	4	1	NULL	NULL

SELECT * FROM Users CROSS JOIN Departments

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Petr	Ivanov	1	2	1	Production
4	Nikolay	Petrov	1	3	1	Production
5	Nikolay	Ivanov	2	1	1	Production
6	Sergey	Sidorov	2	3	1	Production
7	Andrey	Bukin	2	2	1	Production
8	Viktor	Rybakov	4	1	1	Production
1	Ivan	Petrov	1	1	2	Distribution
2	Ivan	Sidorov	1	2	2	Distribution
3	Petr	Ivanov	1	2	2	Distribution
4	Nikolay	Petrov	1	3	2	Distribution
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution
8	Viktor	Rybakov	4	1	2	Distribution
1	Ivan	Petrov	1	1	3	Purchasing
2	Ivan	Sidorov	1	2	3	Purchasing
3	Petr	Ivanov	1	2	3	Purchasing
4	Nikolay	Petrov	1	3	3	Purchasing
5	Nikolay	Ivanov	2	1	3	Purchasing
6	Sergey	Sidorov	2	3	3	Purchasing
7	Andrey	Bukin	2	2	3	Purchasing
8	Viktor	Rybakov	4	1	3	Purchasing

SELECT dpt.DepartmentName AS "Department", usr.UserName + " " + usr.UserSurname AS "User name", pos.PositionName AS "Position" FROM Users AS usr LEFT JOIN Departments AS dpt ON usr.DepartmentID = dpt.DepartmentID LEFT JOIN Positions AS pos ON usr.PositionID = pos.PositionID ORDER BY dpt.DepartmentID, pos.PositionID

Department	User name	Position
NULL	Viktor Rybakov	Manager
Production	Ivan Petrov	Manager
Production	Ivan Sidorov	Senior analyst
Production	Petr Ivanov	Senior analyst
Production	Nikolay Petrov	Analyst
Distribution	Nikolay Ivanov	Manager
Distribution	Andrey Bukin	Senior analyst
Distribution	Sergey Sidorov	Analyst

Изменение данных

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

За транзакции в Transact-SQL отвечает структура BEGIN TRANSACTION ... COMMIТ TRANSACTION. Эту структуру использовать необязательно, но тогда все команды сценария являются необратимыми, то есть нельзя сделать "откат" к предыдущему состоянию. Полная структура блока транзакций:

BEGIN TRANSACTION [имя транзакции] [операции] COMMIТ TRANSACTION [имя транзакции] или ROLLBACK TRANSACTION [имя транзакции]

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

Утановить всем сотрудникам новый оклад BEGIN TRANSACTION TR1 UPDATE Positions SET BaseSalary = 2500000000000000 IF @@ERROR <> 0 BEGIN RAISERROR("Error, transaction not completed!",16,-1) ROLLBACK TRANSACTION TR1 END ELSE COMMIT TRANSACTION TR1

Для вставки данных в таблицы SQL-сервера используется команда INSERT INTO:

INSERT INTO [название таблицы] (колонки) VALUES ([значения колонок])

Вторая часть комнады является необязательной для MS SQL Server 2003, но MS JET SQL без этого слова будет выдавать ошибку синтаксиса. Вставка обычно производиться целострочно, то есть в комнаде указываются все колонки таблицы и значения, которые нужно в них занести. Если же колонка имеет значение по умолчанию или разрешает пустое значения, то в команде вставки эту колонку можно не указывать. Команда INSERT INTO также разрешает указывать вносимые данные не по порядку следования колонок, но в этом случае нужно обозначить используемый порядок колонок.

В таблицу Users вставить строку с данными UserID = 9, UserName = "Nikolay", -- UserSurname = "Gryzlov", DepartmentID = 4, PositionID = 2. INSERT INTO Users VALUES (9, "Nikolay", "Gryzlov", 4, 2) -- В таблицу Users вставить строку с данными UserID = 10, UserName = "Nikolay", -- UserSurname = "Kozin", DepartmentID - значение по умолчанию, PositionID - не указано. INSERT Users VALUES (10, "Nikolay", "Kozin", DEFAULT, NULL) -- В таблицу Users вставить строку с данными UserName = "Angrey", UserSurname = "Medvedev", -- UserID = 11, остальные значения по умолчанию INSERT INTO Users (UserName, UserSurname, UserID) VALUES ("Angrey", "Medvedev", 11)

Для того, чтобы изменить значение ячейки таблицы, используется команда UPDATE:

UPDATE [название таблицы] SET [имя колонки]=[значение колонок] WHERE [условие]

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

Установить всем должностям зарплату в 2000 единиц. UPDATE Positions SET BaseSalary = 2000 -- Должностям с идентификатором 1 установить зарплату в 2500 единиц. UPDATE Positions SET BaseSalary = 2500 WHERE PositionID = 1 -- Должностям с идентификатором 2 уменьшить зарплату на 30%. UPDATE Positions SET BaseSalary = BaseSalary * 0.7 WHERE PositionID = 2 -- Установить всем должностям зарплату, равную (30 000 разделить на количество -- сотрудников в организации) UPDATE Positions SET BaseSalary = 30000 / (SELECT COUNT(UserID) FROM Users)

Удаление данных производится командой DELETE:

DELETE FROM [название таблицы] WHERE [условие]

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

Удалить пользователя с идентификатором 10 -- В режиме отладки рекомедуется использовать команду SELECT, -- чтобы знать, какие данные будут стерты: -- SELECT UserID FROM Users WHERE UserID = 10 DELETE FROM Users WHERE UserID = 10 -- Удалить всех польователей отдела Production DELETE Users FROM Users INNER JOIN Departments ON Users.DepartmentID = Departments.DepartmentID WHERE Departments.DepartmentName = "Production" -- Удалить всех пользователей DELETE FROM Users

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

Более быстрая команда для очистки таблицы - это TRUNCATE TABLE.

TRUNCATE TABLE [название таблицы]

Пример удаления всех данных:

Очистить таблицу Users TRUNCATE TABLE Users

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

Создать временную таблицу #TempTable, в которую скопировать содержание -- колонки UserName таблицы Users SELECT UserName INTO #TempTable FROM Users -- Выбрать все записи временной таблицы #TempTable SELECT * FROM #TempTable

Хранимые процедуры и функции

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

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

Пример создания хранимой процедуры и хранимой функции:

Создание функции обновления зарплат CREATE PROCEDURE usp_UpdateSalary AS UPDATE Positions SET BaseSalary = 2000 GO -- Создание функции получения имени пользователя CREATE FUNCTION usf_GetName (@UserID int) RETURNS varchar(255) BEGIN IF @UserID IS NULL SET @UserID = 1 RETURN (SELECT UserName + " " + UserSurname FROM Users WHERE UserID = @UserID) END GO -- Обновление зарплат EXEC TestDatabase.dbo.usp_UpdateSalary -- Получение имени пользователя с идентификатором 2 SELECT TestDatabase.dbo.usf_GetName(2)

Итак, хранимые процедуры и функции дают следующие преимущества:

производительность;
общая логика для всез запросов;
уменьшение трафика;
безопасность - доступ пользователю дается не к таблице, а к процедуре;

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

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

Избегать NOT - команды отрицания выполняются в несколько этапов, что увеличивает нагрузку на сервер.
Избегать LIKE - этот оператор сравнения применяет более мягкие шаблоны сравнения, чем оператор =, что увеличивает необходимое число этапов фильтрации.
Применять точные шаблоны поиска - применение подстановочных символов увеличивает время выполнения запроса, так как для проверки всех вариантов подстановки требуется дополнительные ресурсы сервера.
Избегать ORDER - команда сортировки требует упорядочивания строк таблицы вывода, что задерживает получение результата.

Виталий Бочкарев

Перевод

Недостаточно писать код хорошо читаемым: он также должен быстро выполняться.

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

Пишите, исходя из структуры хранения данных: если вы храните данные типа datetime, используйте именно datetime, а не varchar или что-нибудь еще.
Пишите, исходя из наличия индексов: если на таблице построены индексы, и они должны там быть, пишите код так, чтобы он мог использовать все преимущества, предоставляемые этими индексами. Убедитесь, что кластерный индекс, а для каждой таблицы он может быть только один, используется наиболее эффективным образом.
Пишите так, чтобы помочь оптимизатору запросов: оптимизатор запросов – восхитительная часть СУБД. К сожалению, вы можете сильно затруднить ему работу, написав запрос, который ему «тяжело» будет разбирать, например, содержащий вложенные представления – когда одно представление получает данные из другого, а то из третьего – и так далее. Потратьте свое время для того, чтобы понять как работает оптимизатор и писать запросы таким образом, чтобы он мог вам помочь, а не навредить.

Существует несколько типичных ошибок, которые люди допускают в своем коде на T-SQL – не совершайте их.

Использование неправильных типов данных

В теории избежать этой ошибки очень просто, но вот на практике она довольно часто встречается. Например, вы используете какой-либо тип данных в своей базе данных. Используйте его же в своих параметрах и переменных! Да, я знаю, что SQL Server может неявно приводить один тип данных к другому. Но, когда происходит неявное преобразование типа, или же вы сами приводите тип данных столбца к другому типу, вы выполняете преобразование для всего столбца. Когда вы выполняете это преобразование для столбца в выражении WHERE или же в условии соединения – вы всегда будете видеть сканирование таблицы (table scan). По этому столбцу может быть построен превосходный индекс, но поскольку вы делаете CAST для значений, хранящихся в этом столбце, чтобы сравнить, например дату, хранящуюся в этом столбце, с типом char, который вы использовали в условии, индекс не будет использоваться.

Не верите? Давайте посмотрим на этот запрос:

SELECT e.BusinessEntityID, e.NationalIDNumber FROM HumanResources.Employee AS e WHERE e.NationalIDNumber = 112457891;
Хорошо написан и очень прост. Он должен покрываться индексом, созданным на этой таблице. Но вот план выполнения:

Этот запрос выполняется достаточно быстро и таблица невелика, так что только четыре операции чтения потребуются, чтобы просканировать индекс. Обратите внимание на небольшой восклицательный знак на операторе SELECT. Если обратиться к его свойствам, мы увидим:

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

SELECT e.BusinessEntityID, e.NationalIDNumber FROM HumanResources.Employee AS e WHERE e.NationalIDNumber = "112457891";
И мы получаем вот такой план выполнения запроса:

И здесь используются только две операции чтения, вместо четырех. И да, я понимаю, что сделал и так быстро выполняющийся запрос чуть-чуть более быстрым. Но что было бы, если бы в таблице хранились миллионы строк? Ага, тогда-то я стал бы героем.

Используйте правильные типы данных.

Использование функций при составлении условий соединения и в выражениях WHERE

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

SELECT a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID FROM Person.Address AS a WHERE "4444" = LEFT(a.AddressLine1, 4) ;
Эта функция, LEFT, получает в качестве аргумента столбец, что выливается в этот план выполнения:

В результате, осуществляется 316 операций чтения, чтобы найти нужные данные, и это занимает 9 миллисекунд (у меня очень быстрые диски). Все потому что ‘4444’ должно сравниться с каждой строкой, возвращенной этой функцией. SQL Server не может даже просто просканировать таблицу, ему необходимо выполнить LEFT для каждой строки. Однако, вы можете сделать нечто вроде этого:

SELECT a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID FROM Person.Address AS a WHERE a.AddressLine1 LIKE "4444%" ;
И вот мы видим совершенно другой план выполнения:

Для выполнения запроса требуется 3 операции чтения и 0 миллисекунд. Ну или пусть будет 1 миллисекунда, для объективности. Это огромный прирост производительности. А все потому что я использовал такую функцию, которая может быть использована для поиска по индексу(ранее это называлось sargeable – непереводимое, в общем-то, слово: SARG – Search Arguments –able, если функция SARGeable – в нее можно передавать столбец в качестве аргумента и все равно будет использоваться Index Seek, если не SARGeable – увы, всегда будет использоваться Index Scan - прим. переводчика ). В любом случае, не используйте функции в выражениях WHERE или условиях поиска, либо используйте только те, которые могут быть использованы в условиях поиска по индексу.

Использование Multi-statement UDF

Multi-statement UDF в русской редакции msdn переводится примерно как «Функции, определяемые пользователем, состоящие из нескольких инструкций, но звучит это, на мой взгляд, как-то странно, поэтому в заголовке и дальше по тексту я старался избегать перевода этого термина - прим. переводчика

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

Проблема с этими функциями заключается в том, что они строятся на табличных переменных. Табличные переменные – это очень крутая штука, если вы используете их по назначению. У них есть одно явное отличие от временных таблиц – по ним не строится статистика. Это отличие может быть очень полезным, а может … убить вас. Если у вас нет статистики, оптимизатор предполагает, что любой запрос, выполняющийся к табличной переменной или UDF, возвратит всего одну строку. Одну (1) строку. Это хорошо, если они действительно возвращают несколько строк. Но, однажды они возвратят сотни или тысячи строк и вы решите соединить одну UDF с другой… Производительность упадет очень-очень быстро и очень-очень сильно.

Пример достаточно велик. Вот несколько UDF:

CREATE FUNCTION dbo.SalesInfo () RETURNS @return_variable TABLE (SalesOrderID INT, OrderDate DATETIME, SalesPersonID INT, PurchaseOrderNumber dbo.OrderNumber, AccountNumber dbo.AccountNumber, ShippingCity NVARCHAR(30)) AS BEGIN; INSERT INTO @return_variable (SalesOrderID, OrderDate, SalesPersonID, PurchaseOrderNumber, AccountNumber, ShippingCity) SELECT soh.SalesOrderID, soh.OrderDate, soh.SalesPersonID, soh.PurchaseOrderNumber, soh.AccountNumber, a.City FROM Sales.SalesOrderHeader AS soh JOIN Person.Address AS a ON soh.ShipToAddressID = a.AddressID ; RETURN ; END ; GO CREATE FUNCTION dbo.SalesDetails () RETURNS @return_variable TABLE (SalesOrderID INT, SalesOrderDetailID INT, OrderQty SMALLINT, UnitPrice MONEY) AS BEGIN; INSERT INTO @return_variable (SalesOrderID, SalesOrderDetailId, OrderQty, UnitPrice) SELECT sod.SalesOrderID, sod.SalesOrderDetailID, sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderDetail AS sod ; RETURN ; END ; GO CREATE FUNCTION dbo.CombinedSalesInfo () RETURNS @return_variable TABLE (SalesPersonID INT, ShippingCity NVARCHAR(30), OrderDate DATETIME, PurchaseOrderNumber dbo.OrderNumber, AccountNumber dbo.AccountNumber, OrderQty SMALLINT, UnitPrice MONEY) AS BEGIN; INSERT INTO @return_variable (SalesPersonId, ShippingCity, OrderDate, PurchaseOrderNumber, AccountNumber, OrderQty, UnitPrice) SELECT si.SalesPersonID, si.ShippingCity, si.OrderDate, si.PurchaseOrderNumber, si.AccountNumber, sd.OrderQty, sd.UnitPrice FROM dbo.SalesInfo() AS si JOIN dbo.SalesDetails() AS sd ON si.SalesOrderID = sd.SalesOrderID ; RETURN ; END ; GO
Отличная структура. Она позволяет составлять очень простые запросы. Ну, например, вот:

SELECT csi.OrderDate, csi.PurchaseOrderNumber, csi.AccountNumber, csi.OrderQty, csi.UnitPrice FROM dbo.CombinedSalesInfo() AS csi WHERE csi.SalesPersonID = 277 AND csi.ShippingCity = "Odessa" ;
Один, очень простой запрос. Вот его план выполнения, так же очень простой:

Вот только выполняется он 2,17 секунды, возвращает 148 строк и использует 1456 операций чтения. Обратите внимание, что наша функция имеет нулевую стоимость и только сканирование таблицы, табличной переменной, влияет на стоимость запроса. Хм, правда что ли? Попробуем посмотреть что скрывается за оператором выполнения UDF с нулевой стоимостью. Этот запрос достанет план выполнения функции из кэша:

SELECT deqp.query_plan, dest.text, SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1, (deqs.statement_end_offset - deqs.statement_start_offset) / 2 + 1) AS actualstatement FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE deqp.objectid = OBJECT_ID("dbo.CombinedSalesInfo");
И вот что там происходит на самом деле:

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

Вот! А теперь мы видим Clustered Index Scan, при котором сканируется большое число строк. Это уже не здорово. Вообще, во всей этой ситуации, UDF кажутся все менее и менее привлекательными. Что если мы, ну, я прямо не знаю, просто попробуем напрямую обратиться к таблицам. Вот так, например:

SELECT soh.OrderDate, soh.PurchaseOrderNumber, soh.AccountNumber, sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID JOIN Person.Address AS ba ON soh.BillToAddressID = ba.AddressID JOIN Person.Address AS sa ON soh.ShipToAddressID = sa.AddressID WHERE soh.SalesPersonID = 277 AND sa.City = "Odessa" ;
Теперь, выполнив этот запрос, мы получим абсолютно те же самые данные, но всего за 310 миллисекунд, а не за 2170. Плюс, SQL Server выполнит всего 911 операций чтения, а не 1456. Честно говоря, очень просто получить проблемы с производительностью, используя UDF

Включение настройки «Работай быстрее!»: использование «Грязных чтений»

Возвращаясь в прошлое, к старым компьютерам с 286-ми процессорами на борту, можно вспомнить, что по ряду причин, на передней панели у них располагалась кнопка «Turbo». Если вы случайно «отжимали» ее, то компьютер сразу же начинал безумно тормозить. Таким образом, вы поняли, что некоторые вещи всегда должны быть включены, чтобы обеспечить максимальную пропускную способность. Точно так же, многие люди смотрят на уровень изоляции READ_UNCOMMITTED и хинт NO_LOCK, как на турбо-кнопку для SQL Server. При их использовании, будьте уверены – практически любой запрос и вся система в целом станут быстрее. Это связано с тем, что при чтении не будут накладываться и проверяться никакие блокировки. Меньше блокировок – быстрее результат. Но…

Когда вы используете READ_UNCOMMITTED или NO_LOCK в своих запросах, вы сталкиваетесь с грязными чтениями. Все понимают, что это означает, что вы можете прочитать «собака» а не «кошка», если в этот момент выполняется, но еще не завершилась операция обновления. Но, кроме этого, вы можете получить большее или меньшее количество строк, чем есть на самом деле, а так же дубликаты строк, поскольку страницы данных могут перемещаться во время выполнения вашего запроса, а вы не накладываете никаких блокировок, чтобы избежать этого. Не знаю как у вас, но в большинстве компаний в которых я работал, ожидали, что большая часть запросов на большинстве систем будут возвращать целостные данные. Один и тот же запрос с одними и теми же параметрами, выполняемый к одному и тому же множеству данных, должен давать один и тот же результат. Только не в том случае, если вы используете NO_LOCK. Для того, чтобы убедиться в этом я советую вам прочесть этот пост .

Необоснованное использование хинтов в запросах

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

Например, множество людей считает, что LOOP JOIN – это лучший способ соединения таблиц. Они приходят к такому выводу, поскольку он наиболее часто встречается в небольших и быстрых запросах. Поэтому они решают принудительно заставить SQL Server использовать именно LOOP JOIN. Это совсем не сложно:

SELECT s. AS StoreName, p.LastName + ", " + p.FirstName FROM Sales.Store AS s JOIN sales.SalesPerson AS sp ON s.SalesPersonID = sp.BusinessEntityID JOIN HumanResources.Employee AS e ON sp.BusinessEntityID = e.BusinessEntityID JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID OPTION (LOOP JOIN);
Этот запрос выполняется 101 миллисекунду и совершает 4115 операций чтений. В общем-то неплохо, но если мы уберем этот хинт, тот же самый запрос выполнится за 90 миллисекунд и произведет всего 2370 чтений. Чем более загружена будет система, тем более очевидной будет эффективность запроса без использования хинта.

А вот еще один пример. Люди часто создают индекс на таблице, ожидая, что он решит проблему. Итак, у нас есть запрос:

SELECT * FROM Purchasing.PurchaseOrderHeader AS poh WHERE poh.PurchaseOrderID * 2 = 3400;
Проблема опять-таки в том, что когда вы выполняете преобразование столбца, ни один индекс не будет адекватно использоваться. Производительность падает, поскольку выполняется сканирование кластерного индекса. И вот, когда люди видят, что их индекс не используется, они делают вот что:

SELECT * FROM Purchasing.PurchaseOrderHeader AS poh WITH (INDEX (PK_PurchaseOrderHeader_PurchaseOrderID)) WHERE poh.PurchaseOrderID * 2 = 3400;
И теперь они получают сканирование выбранного ими, а не кластерного, индекса, так что индекс «используется», правда ведь? Но вот производительность запроса изменяется – теперь вместо 11 операций чтения выполняется 44 (время выполнения у обоих около 0 миллисекунд, поскольку у меня реально быстрые диски). «Использоваться»-то он используется, но совсем не так как предполагалось. Решение этой проблемы заключается в том, чтобы переписать запрос таким образом:

SELECT * FROM Purchasing.PurchaseOrderHeader poh WHERE PurchaseOrderID = 3400 / 2;
Теперь количество операций чтения упало до двух, поскольку используется поиск по индексу – индекс используется правильно.

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

Использование построчной обработки результата выполнения запроса (‘Row by Agonizing Row’ processing)

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

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

BEGIN TRANSACTION DECLARE @Name NVARCHAR(50) , @Color NVARCHAR(15) , @Weight DECIMAL(8, 2) DECLARE BigUpdate CURSOR FOR SELECT p. ,p.Color ,p. FROM Production.Product AS p ; OPEN BigUpdate ; FETCH NEXT FROM BigUpdate INTO @Name, @Color, @Weight ; WHILE @@FETCH_STATUS = 0 BEGIN IF @Weight < 3 BEGIN UPDATE Production.Product SET Color = "Blue" WHERE CURRENT OF BigUpdate END FETCH NEXT FROM BigUpdate INTO @Name, @Color, @Weight ; END CLOSE BigUpdate ; DEALLOCATE BigUpdate ; SELECT * FROM Production.Product AS p WHERE Color = "Blue" ; ROLLBACK TRANSACTION
В каждой итерации мы совершаем две операции чтения, а количество продукции, отвечающей нашим критериям, исчисляется сотнями. На моей машине, без нагрузки, время выполнения составляет больше секунды. Это совершенно неприемлемо, тем более что переписать этот запрос очень просто:

BEGIN TRANSACTION UPDATE Production.Product SET Color = "BLUE" WHERE < 3 ; ROLLBACK TRANSACTION
Теперь выполняется всего 15 операций чтения и время выполнения составляет всего 1 миллисекунду. Не смейтесь. Люди часто пишут такой код и даже хуже. Курсоры – это такая штука, которую следует избегать и использовать только там, где без них нельзя обойтись – например в задачах обслуживания, где вам надо «пробегать» по разным таблицам или базам данных.

Необоснованное использование вложенных представлений

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

Вот, например, последовательность простых запросов, определяющих представления:

CREATE VIEW dbo.SalesInfoView AS SELECT soh.SalesOrderID, soh.OrderDate, soh.SalesPersonID, soh.PurchaseOrderNumber, soh.AccountNumber, a.City AS ShippingCity FROM Sales.SalesOrderHeader AS soh JOIN Person.Address AS a ON soh.ShipToAddressID = a.AddressID ; CREATE VIEW dbo.SalesDetailsView AS SELECT sod.SalesOrderID, sod.SalesOrderDetailID, sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderDetail AS sod ; CREATE VIEW dbo.CombinedSalesInfoView AS SELECT si.SalesPersonID, si.ShippingCity, si.OrderDate, si.PurchaseOrderNumber, si.AccountNumber, sd.OrderQty, sd.UnitPrice FROM dbo.SalesInfoView AS si JOIN dbo.SalesDetailsView AS sd ON si.SalesOrderID = sd.SalesOrderID ;
А вот здесь автор текста забыл указать запрос, но он приводит его в комментариях (прим. переводчика):
SELECT csi.OrderDate FROM dbo. CominedSalesInfoView csi WHERE csi.SalesPersonID = 277
В итоге наш запрос выполняется 155 миллисекунд и использует 965 операций чтения. Вот его план выполнения:

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

SELECT soh.OrderDate FROM Sales.SalesOrderHeader AS soh WHERE soh.SalesPersonID = 277 ;
А теперь запрос выполняется за 3 миллисекунды и использует 685 операций чтения – довольно-таки сильно отличается. И вот его план выполнения:

Как вы можете убедиться, оптимизатор не в силах выкинуть все лишние таблицы в рамках процесса упрощения запроса. Поэтому, в первом плане выполнения есть две лишние операции – Index Scan и Hash Match, собирающий данные воедино. Вы могли бы избавить SQL Server от лишней работы, написав этот запрос без использования представлений. И помните – этот пример очень прост, большинство запросов в реальной жизни намного сложнее и приводят к гораздо большим проблемам производительности.

В комментариях к этой статье есть небольшой спор, суть которого в том, что Грант (автор статьи), похоже выполнял свои запросы не на стандартной базе AdventureWorks, а на похожей БД, но с несколько иной структурой, из-за чего план выполнения „неоптимального“ запроса, приведенного в последнем разделе, отличается от того, что можно увидеть, проводя эксперимент самостоятельно. Прим. переводчика.
Если где-то я был излишне косноязычен (а я это могу) и текст труден для понимания, или вы можете мне предложить лучшую формулировку чего бы то ни было - с радостью выслушаю все замечения.

Transact-SQL (так же называется T-SQL ) это база данных (database ) процедурный язык программирования принадлежащее монопольно Microsoft и используется в SQL Server .

Процедурный язык был создан для расширения возможностей SQL с возможностью хорошо интегрировать с SQL . Добавлены некоторые функции, как локальные переменные и обработка строк/данных. Эти функции делают язык Turing-complete (**).

Они так же используются для записи процедур хранения: Фрагмент кода находящийся на сервере управляет сложными бизнес правилами, которые сложно или невозможно управлять операциями на основе набора (pure set-based operations).

A Turing Complete system means a system in which a program can be written that will find an answer (although with no guarantees regarding runtime or memory).

2- Обзор Transact-SQL

T-SQL организован блоками команд, один блок команд можно вложить в другой блок команд, блок команд начинающийся с BEGIN и заканчивающийся на END , в блоке имеется много конманд, и команды отделены друг от друга точкой запятой(;).

Структура блока:

BEGIN -- Declare variables -- T-SQL Statements END;

3- Начать с SQL Server Management Studio

В данной статье я покажу вам программирование SQL Server , на визуальном инструменте SQL Server Management Studio .

Это иллюстрация SQL Server Management Studio при открытии. Есть некоторые примеры database , когда вы полностью устанавливаете SQLServer .

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

Нажать на правую мышь на database , выбрать "New Query" чтобы открыть окно для этого database .

Вы готовы программировать database с SQL Server .

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

Begin -- Declaring a variable Declare @v_Result Int; -- Declaring a variable with a value of 50 Declare @v_a Int = 50; -- Declaring a variable with a value of 100 Declare @v_b Int = 100; -- Print out Console (For developer). -- Using Cast to convert Int to String -- Using + operator to concatenate 2 string Print "v_a= " + Cast(@v_a as varchar(15)); -- Print out Console Print "v_b= " + Cast(@v_b as varchar(15)); -- Sum Set @v_Result = @v_a + @v_b; -- Print out Console Print "v_Result= " + Cast(@v_Result as varchar(15)); End;

Нажмите на знак чтобы запустить блок команд, и смотрите результат на SQL Server Management Studio :

4- Базовые команды Transact-SQL

4.1- Команда веток If-elsif-else

Синтаксис:

IF THEN Job 1; END IF;

BEGIN -- Declare a variable DECLARE @v_Option integer; DECLARE @v_Action varchar(30); SET @v_Option = 2; IF @v_Option = 1 SET @v_Action = "Run"; ELSE IF @v_Option = 2 BEGIN PRINT "In block else if @v_Option = 2"; SET @v_Action = "Backup"; END; ELSE IF @v_Option = 3 SET @v_Action = "Stop"; ELSE SET @v_Action = "Invalid"; -- Logging PRINT "@v_Action= " + @v_Action; END;

Результаты запуска примера:

4.2- Цикл WHILE

В цикле WHILE вы можете использовать BREAK чтобы выйти из цикла.
Используйте команду CONTINUE чтобы пропустить команды в блоке WHILE и ниже, чтобы начать новый цикл.

< @y WHILE (@x < @y) BEGIN SET @step = @step + 1; -- Every time loop execute, x increases by 1. SET @x = @x + 1; -- Every time loop execute, x decreases by 2. SET @y = @y - 2; PRINT "Step =" + CAST(@step AS varchar(10)); PRINT "@x =" + CAST(@x AS varchar(10)) + " / @y = " + CAST(@y AS varchar(10)); END; -- Write log PRINT "x,y = " + CAST(@x AS varchar(10)) + ", " + CAST(@y AS varchar(10)); END;

Результаты запуска примера:

BEGIN -- Declaring 2 variables x and y DECLARE @x integer = 0; DECLARE @y integer = 10; -- Step DECLARE @step integer = 0; -- While @x < @y WHILE (@x < @y) BEGIN SET @step = @step + 1; -- Every time the loop execute, x increases by 1 SET @x = @x + 1; -- Every time the loop execute, y decreases by 1 SET @y = @y - 2; PRINT "Step =" + CAST(@step AS varchar(10)); PRINT "@x =" + CAST(@x AS varchar(10)) + " / @y = " + CAST(@y AS varchar(10)); -- If @x > 2 then exit the loop -- (Although conditions in the WHILE is still true). IF @x > 2 BREAK; END; -- Write log PRINT "x,y = " + CAST(@x AS varchar(10)) + ", " + CAST(@y AS varchar(10)); END;

Результаты запуска примера:

Команда CONTINUE позволяет пропустить команды находящиеся ниже (в цикле), чтобы начать новый цикл.

BEGIN -- Declaring 2 variables x and y. DECLARE @x integer = 0; DECLARE @y integer = 10; -- Step DECLARE @step integer = 0; -- While @x < @y WHILE (@x < @y) BEGIN SET @step = @step + 1; -- Every time the loop execute, x increases by 1 SET @x = @x + 1; -- Every time the loop execute, x decreases by 2 SET @y = @y - 2; -- If @x < 3 , then skip the statements below -- And continue new step IF @x < 3 CONTINUE; -- If @x < 3 the statements below "CONTINUE" will not be run. PRINT "Step =" + CAST(@step AS varchar(10)); PRINT "@x =" + CAST(@x AS varchar(10)) + " / @y = " + CAST(@y AS varchar(10)); END; -- Write log PRINT "x,y = " + CAST(@x AS varchar(10)) + ", " + CAST(@y AS varchar(10)); END;

5- Прикрепить данные запроса в переменную

Переменным могут присвоить значение из запроса. Смотрите иллюстрированный пример ниже:

Assign_Value_Example

BEGIN -- Declaring a variable @v_Emp_ID DECLARE @v_Emp_ID integer = 1; DECLARE @v_First_Name varchar(30); DECLARE @v_Last_Name varchar(30); DECLARE @v_Dept_ID integer; -- Assgin values to variables SELECT @v_First_Name = emp.First_Name, @v_Last_Name = emp.Last_Name, @v_Dept_Id = emp.Dept_Id FROM Employee Emp WHERE Emp.Emp_ID = @v_Emp_Id; -- Print out values PRINT "@v_First_Name = " + @v_First_Name; PRINT "@v_Last_Name = " + @v_Last_Name; PRINT "@v_Dept_Id = " + CAST(@v_Dept_ID AS varchar(15)); END;

Результаты запуска примера:

6- Особенные виды данных в T-SQL

6.1- Виды данных TABLE (Неявный вид)

T-SQL позволяет вам объявит переменные с видом данных TABLE .

Синтаксис:

Define a variable of type TABLE. -- NOTE: The constraints can also participate in declaration (See example). Declare @v_variable_name TABLE (Column1 DataType1, Column2 DataType2);

Declare a variable of type TABLE. Declare @v_Table TABLE (First_Name Varchar(30), Last_Name Varchar(30), Dept_ID Integer, Salary Float); -- The constraints can also participate in declaration: Declare @v_table TABLE (Product_ID Integer IDENTITY(1,1) PRIMARY KEY, Product_Name DataType2 NOT NULL Default ("Unknown"), Price Money CHECK (Price < 10.0));

Пример: Вставить данные в переменные вида TABLE .

Вы так же можете обновить Update на переменных вида TABLE :

Delete на переменных вида TABLE :

Query данных на переменных вида TABLE :

BEGIN DECLARE @v_Emp_ID integer = 1; -- Declare a variable of type TABLE. DECLARE @v_Table TABLE (First_Name varchar(30), Last_Name varchar(30), Dept_Id integer, Salary float DEFAULT 1000); -- Using INSERT INTO statement to insert data into @v_Table. INSERT INTO @v_Table (First_name, Last_Name, Dept_ID) SELECT emp.First_Name, emp.Last_Name, emp.Dept_Id FROM Employee Emp WHERE Emp.Emp_ID < 4; -- Update @v_Table UPDATE @v_Table SET Salary = Salary + 100 WHERE First_name = "Susan"; -- Query @v_Table. SELECT * FROM @v_Table; END;

Результаты запуска примера:

6.2- Вид данных TABLE (Явный вид)

T-SQL позволяет вам объявить переменные вида TABLE косвенным образом. Название переменной начинается с # .

BEGIN -- Using SELECT INTO statement to insert data into #v_My_Table. SELECT emp.First_Name, emp.Last_Name, emp.Dept_Id, 1000 Salary INTO #v_My_Table FROM Employee Emp WHERE Emp.Emp_ID < 4; -- Update #v_My_Table UPDATE #v_My_Table SET Salary = Salary + 100 WHERE First_name = "Susan"; -- Query #v_My_Table. SELECT * FROM #v_My_Table; END;

Результаты запуска примера:

7- Курсор (Cursor)

7.1- Что такое курсор?

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

7.2- Объявить курсор

Синтаксис

ISO Syntax DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] [;] -- Transact-SQL Extended Syntax DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]

7.3- Пример с курсором

USE learningsql; BEGIN -- -- Declare a variable: DECLARE @v_Emp_ID integer; DECLARE @v_First_Name varchar(50); DECLARE @v_Last_Name varchar(50); DECLARE @v_Count integer; -- Declare a CURSOR. DECLARE My_Cursor CURSOR FOR SELECT Emp.EMP_ID, Emp.FIRST_NAME, Emp.LAST_NAME FROM Employee Emp WHERE Emp.EMP_ID < 3; -- Open Cursor OPEN My_Cursor; -- Move the cursor to the first record. -- And assign column values to variables. FETCH NEXT FROM My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name; -- The FETCH statement was successful. (@@FETCH_STATUS = 0) WHILE @@FETCH_STATUS = 0 BEGIN PRINT "First Name = "+ @v_First_Name+" / Last Name = "+ @v_Last_Name; -- Move to the next record. -- And assign column values to the variables FETCH NEXT FROM My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name; END -- Close Cursor. CLOSE My_Cursor; DEALLOCATE My_Cursor; END;

Результаты запуска примера:

7.4- Пример использования курсора (Объявление вида переменной)

USE learningsql; BEGIN -- -- Declare a variable: DECLARE @v_Emp_ID integer; DECLARE @v_First_Name varchar(50); DECLARE @v_Last_Name varchar(50); -- Declaring a cursor variable. DECLARE @My_Cursor CURSOR; -- Set Select statement for CURSOR variable. Set @My_Cursor = CURSOR FOR SELECT Emp.EMP_ID, Emp.FIRST_NAME, Emp.LAST_NAME FROM Employee Emp WHERE Emp.EMP_ID < 3; -- Open Cursor OPEN @My_Cursor; -- Move the cursor to the first line. -- And assign column values to the variables. FETCH NEXT FROM @My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name; -- The FETCH statement was successful. (@@FETCH_STATUS = 0) WHILE @@FETCH_STATUS = 0 BEGIN PRINT "First Name = "+ @v_First_Name+" / Last Name = "+ @v_Last_Name; -- Move to the next record. -- And assign column values to the variables. FETCH NEXT FROM @My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name; END -- Close Cursor. CLOSE @My_Cursor; DEALLOCATE @My_Cursor; END;

The results run the example:

8- Обработка исключения

При программировании T-SQL могут появиться некоторые ошибки в вашем коде, например ошибка при делении на 0. Или ошибка когда вы вставляете запись, но она дублирует значение с первичным ключом, ... Вам нужно исправить эти ситуации.

Смотрите простой пример, как исправить ошибку при делении на 0.

TryCatch_Example

USE learningsql; BEGIN -- -- Declare a variable: DECLARE @v_a float = 20; DECLARE @v_b float = 0; DECLARE @v_c float; DECLARE @v_Error_Number integer; -- Use BEGIN TRY .. END TRY to trap errors. -- If an error occurs in this block -- It will jump to block BEGIN CATCH .. END CATCH. BEGIN TRY --- PRINT "@v_a = " + CAST(@v_a AS varchar(15)); PRINT "@v_b = " + CAST(@v_b AS varchar(15)); -- Divide by 0 error, occurring here. SET @v_c = @v_a / @v_b; -- Below this line will not be running. -- Program jump to block BEGIN CATCH .. END CATCH PRINT "@v_c= " + CAST(@v_c AS varchar(15)); END TRY -- BEGIN CATCH .. END CATCH must be placed immediately behind BEGIN TRY .. END TRY. BEGIN CATCH -- Error Number. SET @v_Error_Number = ERROR_NUMBER(); -- Print out error number: PRINT "Error Number: " + CAST(@v_Error_Number AS varchar(15)); -- Error message: PRINT "Error Message: " + ERROR_MESSAGE(); -- The severity of the error: PRINT "Error Severity: " + CAST(ERROR_SEVERITY() AS varchar(15)); -- Error State: PRINT "Error State: " + CAST(ERROR_STATE() AS varchar(15)); -- Line Number: PRINT "Error Line: " + CAST(ERROR_LINE() AS varchar(15)); -- Name of procedure (or function, or trigger). PRINT "Error Procedure: " + ERROR_PROCEDURE(); END CATCH; END;

Pезультат

Информация ошибки:

Функция	Описание
ERROR_NUMBER()	Возвращает номер ошибки.
ERROR_MESSAGE()	Возвращает полностью сообщение об ошибке. Сообщение включает значения предоставленные параметрам, как длина, название объекта, или время.
ERROR_SEVERITY()	Возвращает степень серьезности ошибки.
ERROR_STATE()	Возвращает статус ошибки.
ERROR_LINE()	Возвращает номер строки кода совершившая ошибку
ERROR_PROCEDURE()	Возвращает название stored procedure или trigger , где произошла ошибка.

9- Функция (Function)

Как procedure (процедура), function (функция) являются командами T-SQL , выполняющими определенную роль. В отличии от процедуры, функция возвращает значение сразу при вызове.
Функция так же может быть сохранена в database в виде Store procedure .

Синтаксис создания function (Функции).

Function_name: -- argument: -- mode: INPUT, OUTPUT, default INPUT -- datatype: CREATE FUNCTION ([ @argument1 datatype1 , @argument2 datatype2 , ... ]) RETURNS datatype AS BEGIN -- Declare variables -- Statements -- Return value END;

Function with parameters CREATE FUNCTION Sum_Ab(a Integer, b Integer) RETURNS Integer AS Begin return a + b; End; -- Function without parameters CREATE FUNCTION Get_Current_Datetime() RETURNS Date AS Begin return CURRENT_TIMESTAMP; End;

Отмена функции (Drop function):

Drop Function DROP FUNCTION ; -- For example: DROP FUNCTION My_Function;

Пример создания функции:

Пример создания вашей первой function (функции) с SQL Server :

Создать функцию (Function)
Компилировать данную функцию
Запуск функции

Check the existence of the function -- If it did exist, should drop it in order to create a new one. IF OBJECT_ID(N"dbo.My_Sum", N"FN") IS NOT NULL DROP FUNCTION My_Sum; GO CREATE FUNCTION My_Sum (@p_a float, @p_b float) RETURNS float AS BEGIN -- Declaring a variable type of Float DECLARE @v_C float; -- Assign value for v_C SET @V_C = @p_A + @p_B; -- Return value. RETURN @v_C; END;

Нажмите на знак чтобы компилировать функцию.

Функция, которую вы создали, является простой функцией возвращающая скалярное значение (Scalar-value). Вы можете увидеть как она создается на SQLServer Management Studio :

Можете протестировать функцию, нажав на правую мышь, выберите:

Script function as -> SELECT to -> New Query Editor Window

Откроется тестовое окно, вы можете поменять параметры значений:

Поменять параметры значений и нажать на запуск.

Функции могут участвовать в команде SELECT .

SELECT acc.account_id, acc.cust_id, acc.avail_balance, acc.pending_balance, dbo.MY_SUM(acc.avail_balance, acc.pending_balance) balance FROM account acc;

Результаты запроса SQL:

10- Процедура (Procedure)

Группа команд T-SQL , выполняющие определенные функции могут быть собраны в процедуре (procedure), чтобы увеличить возможность выполнения, общего пользования, безопасности, безопасности данных, и полезность в развитии.

Процедуры могут быть сохранены в database как объект в database , готовый для переиспользования. Процедура в данный момент называется Store procedure . Чтобы выполнить Store procedure , сразу после сохранения Store procedure , они компилируются в p-code поэтому могут повысить возможность выполненния.

Процедуры не возвращают значения напрямую как функции (function ). Но могут иметь 0 или более параметров на выход.

Синтаксис создания процедуры:

Procedure_name: -- argument: -- mode: input type: INPUT or OUTPUT, default is INPUT -- datatype: -- Note: The procedure parameters can put in an (), or unnecessary. CREATE PROCEDURE [ argument1 datatype1 , argument2 datatype2 , ... ] AS BEGIN -- Declare variables. -- Statements .. END; -- OR: CREATE PROCEDURE ([ argument1 datatype1 , argument2 datatype2 , ... ]) AS BEGIN -- Declare variables. -- Statements .. END;

Procedure without parameters. CREATE Procedure Do_Something AS Begin -- Declare variables here. Declare @v_a Integer; -- Do something here -- .... End; -- Procedure with parameters -- 1 input parameter and 2 output parameters CREATE Procedure Do_Something (@p_Param1 Varchar(20), @v_Param2 Varchar(50) OUTPUT) AS Begin -- Declare variables Declare @v_a Integer; -- Do something here. -- ... End;

Отменить процедуру (Drop procedure):

Drop Procedure: DROP PROCEDURE

Шаги для выполнения процедуры:

Пример создания процедуры:

Get_Employee_Infos

Drop procedure Get_Employee_Infos if it already exists. -- (To enable recreate) IF OBJECT_ID(N"dbo.Get_Employee_Infos", N"P") IS NOT NULL DROP PROCEDURE Get_Employee_Infos; GO -- Procedure with input parameter: p_Emp_Id -- And output: v_First_Name, v_Last_Name, v_Dept_Id. CREATE PROCEDURE Get_Employee_Infos (@p_Emp_Id integer , @v_First_Name varchar(50) OUTPUT , @v_Last_Name varchar(50) OUTPUT , @v_Dept_Id integer OUTPUT) AS BEGIN -- Use the Print command to print out a string (for programmers). -- Use Cast to convert Integer to string (Varchar). -- Use the + operator to concatenate two strings. PRINT "Parameter @p_Emp_Id = " + CAST(@p_Emp_ID AS varchar(15)); -- -- Query data from the table and assign values to variables. -- SELECT @v_First_Name = Emp.First_Name, @v_Last_Name = Emp.Last_Name, @v_Dept_Id = Emp.Dept_Id FROM Employee Emp WHERE Emp.Emp_Id = @p_Emp_Id; -- -- Log (For developers). -- PRINT "Found Record!"; PRINT " @v_First_Name= " + @v_First_Name; PRINT " @v_Last_Name= " + @v_Last_Name; PRINT " @v_Dept_Id= " + CAST(@v_Dept_Id AS varchar(15)); END;

Mark a savepoint in transaction: save transaction name_of_savepoint

@@trancount variable: shows the number of transactions is being executed (has not been finished with rollback or commit) in the current connection.

Команда rollback tran + tên_của_savepoint помогает отменить(rollback) транзакцию до соответствующей позиции savepoint (без эффекта завершения транзакции), блокировки (locks) будут разблокированы (unlock) при выполнении манипуляций rollback определенных частей.
При объявлении явной транзакции, нужно удостовериться что она может быть отменена (rollback ) или зафиксирована в явном виде (commit ), если нет, транзакция будет продолжать существовать и занимать ресурсы, предотвращая выпонения других транзакций .
Команда rollback помогает отменить только транзакции в базе данных (insert , delete , update ). Другие команды, например прикрепить, не будут зависеть от команды rollback .

Transaction_Example1

BEGIN -- In this example the accounts ACCOUNT_ID = 1, 2 actually exists in DB -- In fact you can write statements to check before the start of transaction -- -- account A (Already guarantees exist in DB) DECLARE @Account_Id_A integer = 1; -- account B (Already guarantees exist in DB) DECLARE @Account_Id_B integer = 2; -- Amount DECLARE @Amount float = 10; -- Bank DECLARE @Execute_Branch_Id integer = 1; -- Write out transaction Count. -- In fact, at this time there is no transaction yet PRINT "@@TranCount = " + CAST(@@Trancount AS varchar(5)); PRINT "Begin transaction"; -- Begin transaction BEGIN TRAN; -- Error trapping. BEGIN TRY -- -- Subtract $10 from account A UPDATE Account SET AVAIL_BALANCE = AVAIL_BALANCE - @Amount WHERE Account_Id = @Account_Id_A; -- -- Insert transaction info into Acc_Transaction table. INSERT INTO ACC_TRANSACTION (TXN_DATE, FUNDS_AVAIL_DATE, TXN_TYPE_CD, ACCOUNT_ID, AMOUNT, EXECUTION_BRANCH_ID) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, "CDT", @Account_Id_A, , @Execute_Branch_Id); -- -- Add $10 to Account B. UPDATE Account SET AVAIL_BALANCE = AVAIL_BALANCE + @Amount WHERE Account_Id = @Account_Id_B; -- -- Insert transaction info into Acc_Transaction table. INSERT INTO ACC_TRANSACTION (TXN_DATE, FUNDS_AVAIL_DATE, TXN_TYPE_CD, ACCOUNT_ID, AMOUNT, EXECUTION_BRANCH_ID) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, "CDT", @Account_Id_B, @Amount, @Execute_Branch_Id); -- Commit transaction IF @@Trancount > 0 PRINT "Commit Transaction"; COMMIT TRAN; END TRY -- If there are errors Catch block will be execute. BEGIN CATCH PRINT "Error: " + ERROR_MESSAGE(); PRINT "Error --> Rollback Transaction"; IF @@Trancount > 0 ROLLBACK TRAN; END CATCH; END;

Результаты запуска примера:

12- Trigger

Статья про Trigger отделена, вы можете посмотреть инструкцию по ссылке:

TODO Link!