Контакты

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

  • 05.04.2024

Leran2002 9 апреля 2015 в 12:31

Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть первая

  • SQL ,
  • Microsoft SQL Server
  • Tutorial

О чем данный учебник

Данный учебник представляет собой что-то типа «штампа моей памяти» по языку SQL (DDL, DML), т.е. это информация, которая накопилась по ходу профессиональной деятельности и постоянно хранится в моей голове. Это для меня достаточный минимум, который применяется при работе с базами данных наиболее часто. Если встает необходимость применять более полные конструкции SQL, то я обычно обращаюсь за помощью в библиотеку MSDN расположенную в интернет. На мой взгляд, удержать все в голове очень сложно, да и нет особой необходимости в этом. Но знать основные конструкции очень полезно, т.к. они применимы практически в таком же виде во многих реляционных базах данных, таких как Oracle, MySQL, Firebird. Отличия в основном состоят в типах данных, которые могут отличаться в деталях. Основных конструкций языка SQL не так много, и при постоянной практике они быстро запоминаются. Например, для создания объектов (таблиц, ограничений, индексов и т.п.) достаточно иметь под рукой текстовый редактор среды (IDE) для работы с базой данных, и нет надобности изучать визуальный инструментарий заточенный для работы с конкретным типом баз данных (MS SQL, Oracle, MySQL, Firebird, …). Это удобно и тем, что весь текст находится перед глазами, и не нужно бегать по многочисленным вкладкам для того чтобы создать, например, индекс или ограничение. При постоянной работе с базой данных, создать, изменить, а особенно пересоздать объект при помощи скриптов получается в разы быстрее, чем если это делать в визуальном режиме. Так же в скриптовом режиме (соответственно, при должной аккуратности), проще задавать и контролировать правила наименования объектов (мое субъективное мнение). К тому же скрипты удобно использовать в случае, когда изменения, делаемые в одной базе данных (например, тестовой), необходимо перенести в таком же виде в другую базу (продуктивную).

Язык SQL подразделяется на несколько частей, здесь я рассмотрю 2 наиболее важные его части:
  • DML – Data Manipulation Language (язык манипулирования данными), который содержит следующие конструкции:
    • SELECT – выборка данных
    • INSERT – вставка новых данных
    • UPDATE – обновление данных
    • DELETE – удаление данных
    • MERGE – слияние данных
Т.к. я являюсь практиком, как таковой теории в данном учебнике будет мало, и все конструкции будут объясняться на практических примерах. К тому же я считаю, что язык программирования, а особенно SQL, можно освоить только на практике, самостоятельно пощупав его и поняв, что происходит, когда вы выполняете ту или иную конструкцию.

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

При написании данного учебника использовалась база данных MS SQL Server версии 2014, для выполнения скриптов я использовал MS SQL Server Management Studio (SSMS).

Кратко о MS SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) - утилита для Microsoft SQL Server для конфигурирования, управления и администрирования компонентов базы данных. Данная утилита содержит редактор скриптов (который в основном и будет нами использоваться) и графическую программу, которая работает с объектами и настройками сервера. Главным инструментом SQL Server Management Studio является Object Explorer, который позволяет пользователю просматривать, извлекать объекты сервера, а также управлять ими. Данный текст частично позаимствован с википедии.

Для создания нового редактора скрипта используйте кнопку «New Query/Новый запрос»:

Для смены текущей базы данных можно использовать выпадающий список:

Для выполнения определенной команды (или группы команд) выделите ее и нажмите кнопку «Execute/Выполнить» или же клавишу «F5». Если в редакторе в текущий момент находится только одна команда, или же вам необходимо выполнить все команды, то ничего выделять не нужно.

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

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

Немного теории

Реляционная база данных (РБД, или далее в контексте просто БД) представляет из себя совокупность таблиц, связанных между собой. Если говорить грубо, то БД – файл в котором данные хранятся в структурированном виде.

СУБД – Система Управления этими Базами Данных, т.е. это комплекс инструментов для работы с конкретным типом БД (MS SQL, Oracle, MySQL, Firebird, …).

Примечание
Т.к. в жизни, в разговорной речи, мы по большей части говорим: «БД Oracle», или даже просто «Oracle», на самом деле подразумевая «СУБД Oracle», то в контексте данного учебника иногда будет употребляться термин БД. Из контекста, я думаю, будет понятно, о чем именно идет речь.

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

Таблица – это главный объект РБД, все данные РБД хранятся построчно в столбцах таблицы. Строки, записи – тоже синонимы.

Для каждой таблицы, как и ее столбцов задаются наименования, по которым впоследствии к ним идет обращение.
Наименование объекта (имя таблицы, имя столбца, имя индекса и т.п.) в MS SQL может иметь максимальную длину 128 символов.

Для справки – в БД ORACLE наименования объектов могут иметь максимальную длину 30 символов. Поэтому для конкретной БД нужно вырабатывать свои правила для наименования объектов, чтобы уложиться в лимит по количеству символов.

SQL - язык позволяющий осуществлять запросы в БД посредством СУБД. В конкретной СУБД, язык SQL может иметь специфичную реализацию (свой диалект).

DDL и DML - подмножество языка SQL:

  • Язык DDL служит для создания и модификации структуры БД, т.е. для создания/изменения/удаления таблиц и связей.
  • Язык DML позволяет осуществлять манипуляции с данными таблиц, т.е. с ее строками. Он позволяет делать выборку данных из таблиц, добавлять новые данные в таблицы, а так же обновлять и удалять существующие данные.

В языке SQL можно использовать 2 вида комментариев (однострочный и многострочный):

Однострочный комментарий
и

/* многострочный комментарий */

Собственно, все для теории этого будет достаточно.

DDL – Data Definition Language (язык описания данных)

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

В данном случае столбцы таблицы имеют следующие наименования: Табельный номер, ФИО, Дата рождения, E-mail, Должность, Отдел.

Каждый из этих столбцов можно охарактеризовать по типу содержащемся в нем данных:

  • Табельный номер – целое число
  • ФИО – строка
  • Дата рождения – дата
  • E-mail – строка
  • Должность – строка
  • Отдел – строка
Тип столбца – характеристика, которая говорит о том какого рода данные может хранить данный столбец.

Для начала будет достаточно запомнить только следующие основные типы данных используемые в MS SQL:

Значение Обозначение в MS SQL Описание
Строка переменной длины varchar(N)
и
nvarchar(N)
 При помощи числа N, мы можем указать максимально возможную длину строки для соответствующего столбца. Например, если мы хотим сказать, что значение столбца «ФИО» может содержать максимум 30 символов, то необходимо задать ей тип nvarchar(30).
Отличие varchar от nvarchar заключается в том, что varchar позволяет хранить строки в формате ASCII, где один символ занимает 1 байт, а nvarchar хранит строки в формате Unicode, где каждый символ занимает 2 байта.
Тип varchar стоит использовать только в том случае, если вы на 100% уверены, что в данном поле не потребуется хранить Unicode символы. Например, varchar можно использовать для хранения адресов электронной почты, т.к. они обычно содержат только ASCII символы.
Строка фиксированной длины char(N)
и
nchar(N)
 От строки переменной длины данный тип отличается тем, что если длина строка меньше N символов, то она всегда дополняется справа до длины N пробелами и сохраняется в БД в таком виде, т.е. в базе данных она занимает ровно N символов (где один символ занимает 1 байт для char и 2 байта для типа nchar). На моей практике данный тип очень редко находит применение, а если и используется, то он используется в основном в формате char(1), т.е. когда поле определяется одним символом.
Целое число int Данный тип позволяет нам использовать в столбце только целые числа, как положительные, так и отрицательные. Для справки (сейчас это не так актуально для нас) – диапазон чисел который позволяет тип int от -2 147 483 648 до 2 147 483 647. Обычно это основной тип, который используется для задания идентификаторов.
Вещественное или действительное число float Если говорить простым языком, то это числа, в которых может присутствовать десятичная точка (запятая).
Дата date Если в столбце необходимо хранить только Дату, которая состоит из трех составляющих: Числа, Месяца и Года. Например, 15.02.2014 (15 февраля 2014 года). Данный тип можно использовать для столбца «Дата приема», «Дата рождения» и т.п., т.е. в тех случаях, когда нам важно зафиксировать только дату, или, когда составляющая времени нам не важна и ее можно отбросить или если она не известна.
Время time Данный тип можно использовать, если в столбце необходимо хранить только данные о времени, т.е. Часы, Минуты, Секунды и Миллисекунды. Например, 17:38:31.3231603
Например, ежедневное «Время отправления рейса».
Дата и время datetime Данный тип позволяет одновременно сохранить и Дату, и Время. Например, 15.02.2014 17:38:31.323
Для примера это может быть дата и время какого-нибудь события.
Флаг bit Данный тип удобно применять для хранения значений вида «Да»/«Нет», где «Да» будет сохраняться как 1, а «Нет» будет сохраняться как 0.

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

Для выполнения примеров создадим тестовую базу под названием Test.

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

CREATE DATABASE Test
Удалить базу данных можно командой (стоит быть очень осторожным с данной командой):

DROP DATABASE Test
Для того, чтобы переключиться на нашу базу данных, можно выполнить команду:

USE Test
Или же выберите базу данных Test в выпадающем списке в области меню SSMS. При работе мною чаще используется именно этот способ переключения между базами.

Теперь в нашей БД мы можем создать таблицу используя описания в том виде как они есть, используя пробелы и символы кириллицы:

CREATE TABLE [Сотрудники]([Табельный номер] int, [ФИО] nvarchar(30), [Дата рождения] date, nvarchar(30), [Должность] nvarchar(30), [Отдел] nvarchar(30))
В данном случае нам придется заключать имена в квадратные скобки […].

Но в базе данных для большего удобства все наименования объектов лучше задавать на латинице и не использовать в именах пробелы. В MS SQL обычно в данном случае каждое слово начинается с прописной буквы, например, для поля «Табельный номер», мы могли бы задать имя PersonnelNumber. Так же в имени можно использовать цифры, например, PhoneNumber1.

На заметку
В некоторых СУБД более предпочтительным может быть следующий формат наименований «PHONE_NUMBER», например, такой формат часто используется в БД ORACLE. Естественно при задании имя поля желательно чтобы оно не совпадало с ключевыми словами используемые в СУБД.

По этой причине можете забыть о синтаксисе с квадратными скобками и удалить таблицу [Сотрудники]:

DROP TABLE [Сотрудники]
Например, таблицу с сотрудниками можно назвать «Employees», а ее полям можно задать следующие наименования:

  • ID – Табельный номер (Идентификатор сотрудника)
  • Name – ФИО
  • Birthday – Дата рождения
  • Email – E-mail
  • Position – Должность
  • Department – Отдел
Очень часто для наименования поля идентификатора используется слово ID.

Теперь создадим нашу таблицу:

CREATE TABLE Employees(ID int, Name nvarchar(30), Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
Для того, чтобы задать обязательные для заполнения столбцы, можно использовать опцию NOT NULL.

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

Обновление поля ID ALTER TABLE Employees ALTER COLUMN ID int NOT NULL -- обновление поля Name ALTER TABLE Employees ALTER COLUMN Name nvarchar(30) NOT NULL

На заметку
Общая концепция языка SQL для большинства СУБД остается одинаковой (по крайней мере, об этом я могу судить по тем СУБД, с которыми мне довелось поработать). Отличие DDL в разных СУБД в основном заключаются в типах данных (здесь могут отличаться не только их наименования, но и детали их реализации), так же может немного отличаться и сама специфика реализации языка SQL (т.е. суть команд одна и та же, но могут быть небольшие различия в диалекте, увы, но одного стандарта нет). Владея основами SQL вы легко сможете перейти с одной СУБД на другую, т.к. вам в данном случае нужно будет только разобраться в деталях реализации команд в новой СУБД, т.е. в большинстве случаев достаточно будет просто провести аналогию.

Создание таблицы CREATE TABLE Employees(ID int, -- в ORACLE тип int - это эквивалент(обертка) для number(38) Name nvarchar2(30), -- nvarchar2 в ORACLE эквивалентен nvarchar в MS SQL Birthday date, Email nvarchar2(30), Position nvarchar2(30), Department nvarchar2(30)); -- обновление полей ID и Name (здесь вместо ALTER COLUMN используется MODIFY(…)) ALTER TABLE Employees MODIFY(ID int NOT NULL,Name nvarchar2(30) NOT NULL); -- добавление PK (в данном случае конструкция выглядит как и в MS SQL, она будет показана ниже) ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID);
Для ORACLE есть отличия в плане реализации типа varchar2, его кодировка зависит настроек БД и текст может сохраняться, например, в кодировке UTF-8. Помимо этого длину поля в ORACLE можно задать как в байтах, так и в символах, для этого используются дополнительные опции BYTE и CHAR, которые указываются после длины поля, например:

NAME varchar2(30 BYTE) -- вместимость поля будет равна 30 байтам NAME varchar2(30 CHAR) -- вместимость поля будет равна 30 символов
Какая опция будет использоваться по умолчанию BYTE или CHAR, в случае простого указания в ORACLE типа varchar2(30), зависит от настроек БД, так же она иногда может задаваться в настройках IDE. В общем порой можно легко запутаться, поэтому в случае ORACLE, если используется тип varchar2 (а это здесь порой оправдано, например, при использовании кодировки UTF-8) я предпочитаю явно прописывать CHAR (т.к. обычно длину строки удобнее считать именно в символах).

Но в данном случае если в таблице уже есть какие-нибудь данные, то для успешного выполнения команд необходимо, чтобы во всех строках таблицы поля ID и Name были обязательно заполнены. Продемонстрируем это на примере, вставим в таблицу данные в поля ID, Position и Department, это можно сделать следующим скриптом:

INSERT Employees(ID,Position,Department) VALUES (1000,N"Директор",N"Администрация"), (1001,N"Программист",N"ИТ"), (1002,N"Бухгалтер",N"Бухгалтерия"), (1003,N"Старший программист",N"ИТ")
В данном случае, команда INSERT также выдаст ошибку, т.к. при вставке мы не указали значения обязательного поля Name.
В случае, если бы у нас в первоначальной таблице уже имелись эти данные, то команда «ALTER TABLE Employees ALTER COLUMN ID int NOT NULL» выполнилась бы успешно, а команда «ALTER TABLE Employees ALTER COLUMN Name int NOT NULL» выдала сообщение об ошибке, что в поле Name имеются NULL (не указанные) значения.

Добавим значения для полю Name и снова зальем данные:


Так же опцию NOT NULL можно использовать непосредственно при создании новой таблицы, т.е. в контексте команды CREATE TABLE.

Сначала удалим таблицу при помощи команды:

DROP TABLE Employees
Теперь создадим таблицу с обязательными для заполнения столбцами ID и Name:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
Можно также после имени столбца написать NULL, что будет означать, что в нем будут допустимы NULL-значения (не указанные), но этого делать не обязательно, так как данная характеристика подразумевается по умолчанию.

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

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30) NULL
Или просто:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30)
Так же данной командой мы можем изменить тип поля на другой совместимый тип, или же изменить его длину. Для примера давайте расширим поле Name до 50 символов:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(50)

Первичный ключ

При создании таблицы желательно, чтобы она имела уникальный столбец или же совокупность столбцов, которая уникальна для каждой ее строки – по данному уникальному значению можно однозначно идентифицировать запись. Такое значение называется первичным ключом таблицы. Для нашей таблицы Employees таким уникальным значением может быть столбец ID (который содержит «Табельный номер сотрудника» - пускай в нашем случае данное значение уникально для каждого сотрудника и не может повторяться).

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

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID)
Где «PK_Employees» это имя ограничения, отвечающего за первичный ключ. Обычно для наименования первичного ключа используется префикс «PK_» после которого идет имя таблицы.

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

ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения PRIMARY KEY(поле1,поле2,…)
Стоит отметить, что в MS SQL все поля, которые входят в первичный ключ, должны иметь характеристику NOT NULL.

Так же первичный ключ можно определить непосредственно при создании таблицы, т.е. в контексте команды CREATE TABLE. Удалим таблицу:

DROP TABLE Employees
А затем создадим ее, используя следующий синтаксис:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30), CONSTRAINT PK_Employees PRIMARY KEY(ID) -- описываем PK после всех полей, как ограничение)
После создания зальем в таблицу данные:

INSERT Employees(ID,Position,Department,Name) VALUES (1000,N"Директор",N"Администрация",N"Иванов И.И."), (1001,N"Программист",N"ИТ",N"Петров П.П."), (1002,N"Бухгалтер",N"Бухгалтерия",N"Сидоров С.С."), (1003,N"Старший программист",N"ИТ",N"Андреев А.А.")
Если первичный ключ в таблице состоит только из значений одного столбца, то можно использовать следующий синтаксис:

CREATE TABLE Employees(ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, -- указываем как характеристику поля Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
На самом деле имя ограничения можно и не задавать, в этом случае ему будет присвоено системное имя (наподобие «PK__Employee__3214EC278DA42077»):

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30), PRIMARY KEY(ID))
Или:

CREATE TABLE Employees(ID int NOT NULL PRIMARY KEY, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
Но я бы рекомендовал для постоянных таблиц всегда явно задавать имя ограничения, т.к. по явно заданному и понятному имени с ним впоследствии будет легче проводить манипуляции, например, можно произвести его удаление:

ALTER TABLE Employees DROP CONSTRAINT PK_Employees
Но такой краткий синтаксис, без указания имен ограничений, удобно применять при создании временных таблиц БД (имя временной таблицы начинается с # или ##), которые после использования будут удалены.

Подытожим

На данный момент мы рассмотрели следующие команды:
  • CREATE TABLE имя_таблицы (перечисление полей и их типов, ограничений) – служит для создания новой таблицы в текущей БД;
  • DROP TABLE имя_таблицы – служит для удаления таблицы из текущей БД;
  • ALTER TABLE имя_таблицы ALTER COLUMN имя_столбца … – служит для обновления типа столбца или для изменения его настроек (например для задания характеристики NULL или NOT NULL);
  • ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения PRIMARY KEY (поле1, поле2,…) – добавление первичного ключа к уже существующей таблице;
  • ALTER TABLE имя_таблицы DROP CONSTRAINT имя_ограничения – удаление ограничения из таблицы.

Немного про временные таблицы

Вырезка из MSDN. В MS SQL Server существует два вида временных таблиц: локальные (#) и глобальные (##). Локальные временные таблицы видны только их создателям до завершения сеанса соединения с экземпляром SQL Server, как только они впервые созданы. Локальные временные таблицы автоматически удаляются после отключения пользователя от экземпляра SQL Server. Глобальные временные таблицы видны всем пользователям в течение любых сеансов соединения после создания этих таблиц и удаляются, когда все пользователи, ссылающиеся на эти таблицы, отключаются от экземпляра SQL Server.

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

Для создания временной таблицы можно использовать команду CREATE TABLE:

CREATE TABLE #Temp(ID int, Name nvarchar(30))
Так как временная таблица в MS SQL аналогична обычной таблице, ее соответственно так же можно удалить самому командой DROP TABLE:

DROP TABLE #Temp

Так же временную таблицу (как собственно и обычную таблицу) можно создать и сразу заполнить данными возвращаемые запросом используя синтаксис SELECT … INTO:

SELECT ID,Name INTO #Temp FROM Employees

На заметку
В разных СУБД реализация временных таблиц может отличаться. Например, в СУБД ORACLE и Firebird структура временных таблиц должна быть определена заранее командой CREATE GLOBAL TEMPORARY TABLE с указанием специфики хранения в ней данных, дальше уже пользователь видит ее среди основных таблиц и работает с ней как с обычной таблицей.

Нормализация БД – дробление на подтаблицы (справочники) и определение связей

Наша текущая таблица Employees имеет недостаток в том, что в полях Position и Department пользователь может ввести любой текст, что в первую очередь чревато ошибками, так как он у одного сотрудника может указать в качестве отдела просто «ИТ», а у второго сотрудника, например, ввести «ИТ-отдел», у третьего «IT». В итоге будет непонятно, что имел ввиду пользователь, т.е. являются ли данные сотрудники работниками одного отдела, или же пользователь описался и это 3 разных отдела? А тем более, в этом случае, мы не сможем правильно сгруппировать данные для какого-то отчета, где, может требоваться показать количество сотрудников в разрезе каждого отдела.

Второй недостаток заключается в объеме хранения данной информации и ее дублированием, т.е. для каждого сотрудника указывается полное наименование отдела, что требует в БД места для хранения каждого символа из названия отдела.

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

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

Давайте создадим 2 таблицы справочники «Должности» и «Отделы», первую назовем Positions, а вторую соответственно Departments:

CREATE TABLE Positions(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Positions PRIMARY KEY, Name nvarchar(30) NOT NULL) CREATE TABLE Departments(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Departments PRIMARY KEY, Name nvarchar(30) NOT NULL)
Заметим, что здесь мы использовали новую опцию IDENTITY, которая говорит о том, что данные в столбце ID будут нумероваться автоматически, начиная с 1, с шагом 1, т.е. при добавлении новых записей им последовательно будут присваиваться значения 1, 2, 3, и т.д. Такие поля обычно называют автоинкрементными. В таблице может быть определено только одно поле со свойством IDENTITY и обычно, но необязательно, такое поле является первичным ключом для данной таблицы.

На заметку
В разных СУБД реализация полей со счетчиком может делаться по своему. В MySQL, например, такое поле определяется при помощи опции AUTO_INCREMENT. В ORACLE и Firebird раньше данную функциональность можно было съэмулировать при помощи использования последовательностей (SEQUENCE). Но насколько я знаю в ORACLE сейчас добавили опцию GENERATED AS IDENTITY.

Давайте заполним эти таблицы автоматически, на основании текущих данных записанных в полях Position и Department таблицы Employees:

Заполняем поле Name таблицы Positions, уникальными значениями из поля Position таблицы Employees INSERT Positions(Name) SELECT DISTINCT Position FROM Employees WHERE Position IS NOT NULL -- отбрасываем записи у которых позиция не указана
То же самое проделаем для таблицы Departments:

INSERT Departments(Name) SELECT DISTINCT Department FROM Employees WHERE Department IS NOT NULL
Если теперь мы откроем таблицы Positions и Departments, то увидим пронумерованный набор значений по полю ID:

SELECT * FROM Positions

SELECT * FROM Departments

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

Добавляем поле для ID должности ALTER TABLE Employees ADD PositionID int -- добавляем поле для ID отдела ALTER TABLE Employees ADD DepartmentID int
Тип ссылочных полей должен быть каким же, как и в справочниках, в данном случае это int.

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

ALTER TABLE Employees ADD PositionID int, DepartmentID int
Теперь пропишем ссылки (ссылочные ограничения - FOREIGN KEY) для этих полей, для того чтобы пользователь не имел возможности записать в данные поля, значения, отсутствующие среди значений ID находящихся в справочниках.

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID)
И то же самое сделаем для второго поля:

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID)
Теперь пользователь в данные поля сможет занести только значения ID из соответствующего справочника. Соответственно, чтобы использовать новый отдел или должность, он первым делом должен будет добавить новую запись в соответствующий справочник. Т.к. должности и отделы теперь хранятся в справочниках в одном единственном экземпляре, то чтобы изменить название, достаточно изменить его только в справочнике.

Имя ссылочного ограничения, обычно является составным, оно состоит из префикса «FK_», затем идет имя таблицы и после знака подчеркивания идет имя поля, которое ссылается на идентификатор таблицы-справочника.

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

ALTER TABLE таблица ADD CONSTRAINT имя_ограничения FOREIGN KEY(поле1,поле2,…) REFERENCES таблица_справочник(поле1,поле2,…)
В данном случае в таблице «таблица_справочник» первичный ключ представлен комбинацией из нескольких полей (поле1, поле2,…).

Собственно, теперь обновим поля PositionID и DepartmentID значениями ID из справочников. Воспользуемся для этой цели DML командой UPDATE:

UPDATE e SET PositionID=(SELECT ID FROM Positions WHERE Name=e.Position), DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department) FROM Employees e
Посмотрим, что получилось, выполнив запрос:

SELECT * FROM Employees

Всё, поля PositionID и DepartmentID заполнены соответствующие должностям и отделам идентификаторами надобности в полях Position и Department в таблице Employees теперь нет, можно удалить эти поля:

ALTER TABLE Employees DROP COLUMN Position,Department
Теперь таблица у нас приобрела следующий вид:

SELECT * FROM Employees

ID Name Birthday Email PositionID DepartmentID
1000 Иванов И.И. NULL NULL 2 1
1001 Петров П.П. NULL NULL 3 3
1002 Сидоров С.С. NULL NULL 1 2
1003 Андреев А.А. NULL NULL 4 3

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

SELECT e.ID,e.Name,p.Name PositionName,d.Name DepartmentName FROM Employees e LEFT JOIN Departments d ON d.ID=e.DepartmentID LEFT JOIN Positions p ON p.ID=e.PositionID

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

Так же стоит отметить, что таблица может ссылаться сама на себя, т.е. можно создать рекурсивную ссылку. Для примера добавим в нашу таблицу с сотрудниками еще одно поле ManagerID, которое будет указывать на сотрудника, которому подчиняется данный сотрудник. Создадим поле:

ALTER TABLE Employees ADD ManagerID int
В данном поле допустимо значение NULL, поле будет пустым, если, например, над сотрудником нет вышестоящих.

Теперь создадим FOREIGN KEY на таблицу Employees:

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID)
Давайте, теперь создадим диаграмму и посмотрим, как выглядят на ней связи между нашими таблицами:

В результате мы должны увидеть следующую картину (таблица Employees связана с таблицами Positions и Depertments, а так же ссылается сама на себя):

Напоследок стоит сказать, что ссылочные ключи могут включать дополнительные опции ON DELETE CASCADE и ON UPDATE CASCADE, которые говорят о том, как вести себя при удалении или обновлении записи, на которую есть ссылки в таблице-справочнике. Если эти опции не указаны, то мы не можем изменить ID в таблице справочнике у той записи, на которую есть ссылки из другой таблицы, так же мы не сможем удалить такую запись из справочника, пока не удалим все строки, ссылающиеся на эту запись или, же обновим в этих строках ссылки на другое значение.

Для примера пересоздадим таблицу с указанием опции ON DELETE CASCADE для FK_Employees_DepartmentID:

DROP TABLE Employees CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, ManagerID int, CONSTRAINT PK_Employees PRIMARY KEY (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID) ON DELETE CASCADE, CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID)) INSERT Employees (ID,Name,Birthday,PositionID,DepartmentID,ManagerID)VALUES (1000,N"Иванов И.И.","19550219",2,1,NULL), (1001,N"Петров П.П.","19831203",3,3,1003), (1002,N"Сидоров С.С.","19760607",1,2,1000), (1003,N"Андреев А.А.","19820417",4,3,1000)
Удалим отдел с идентификатором 3 из таблицы Departments:

DELETE Departments WHERE ID=3
Посмотрим на данные таблицы Employees:

SELECT * FROM Employees

ID Name Birthday Email PositionID DepartmentID ManagerID
1000 Иванов И.И. 1955-02-19 NULL 2 1 NULL
1002 Сидоров С.С. 1976-06-07 NULL 1 2 1000

Как видим, данные по отделу 3 из таблицы Employees так же удалились.

Опция ON UPDATE CASCADE ведет себя аналогично, но действует она при обновлении значения ID в справочнике. Например, если мы поменяем ID должности в справочнике должностей, то в этом случае будет производиться обновление DepartmentID в таблице Employees на новое значение ID которое мы задали в справочнике. Но в данном случае это продемонстрировать просто не получится, т.к. у колонки ID в таблице Departments стоит опция IDENTITY, которая не позволит нам выполнить следующий запрос (сменить идентификатор отдела 3 на 30):

UPDATE Departments SET ID=30 WHERE ID=3
Главное понять суть этих 2-х опций ON DELETE CASCADE и ON UPDATE CASCADE. Я применяю эти опции очень в редких случаях и рекомендую хорошо подумать, прежде чем указывать их в ссылочном ограничении, т.к. при нечаянном удалении записи из таблицы справочника это может привести к большим проблемам и создать цепную реакцию.

Восстановим отдел 3:

Даем разрешение на добавление/изменение IDENTITY значения SET IDENTITY_INSERT Departments ON INSERT Departments(ID,Name) VALUES(3,N"ИТ") -- запрещаем добавление/изменение IDENTITY значения SET IDENTITY_INSERT Departments OFF
Полностью очистим таблицу Employees при помощи команды TRUNCATE TABLE:

TRUNCATE TABLE Employees
И снова перезальем в нее данные используя предыдущую команду INSERT:

INSERT Employees (ID,Name,Birthday,PositionID,DepartmentID,ManagerID)VALUES (1000,N"Иванов И.И.","19550219",2,1,NULL), (1001,N"Петров П.П.","19831203",3,3,1003), (1002,N"Сидоров С.С.","19760607",1,2,1000), (1003,N"Андреев А.А.","19820417",4,3,1000)

Подытожим

На данным момент к нашим знаниям добавилось еще несколько команд DDL:
  • Добавление свойства IDENTITY к полю – позволяет сделать это поле автоматически заполняемым (полем-счетчиком) для таблицы;
  • ALTER TABLE имя_таблицы ADD перечень_полей_с_характеристиками – позволяет добавить новые поля в таблицу;
  • ALTER TABLE имя_таблицы DROP COLUMN перечень_полей – позволяет удалить поля из таблицы;
  • ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения FOREIGN KEY (поля) REFERENCES таблица_справочник(поля) – позволяет определить связь между таблицей и таблицей справочником.

Прочие ограничения – UNIQUE, DEFAULT, CHECK

При помощи ограничения UNIQUE можно сказать что значения для каждой строки в данном поле или в наборе полей должно быть уникальным. В случае таблицы Employees, такое ограничение мы можем наложить на поле Email. Только предварительно заполним Email значениями, если они еще не определены:

UPDATE Employees SET Email="[email protected]" WHERE ID=1000 UPDATE Employees SET Email="[email protected]" WHERE ID=1001 UPDATE Employees SET Email="[email protected]" WHERE ID=1002 UPDATE Employees SET Email="[email protected]" WHERE ID=1003
А теперь можно наложить на это поле ограничение-уникальности:

ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE(Email)
Теперь пользователь не сможет внести один и тот же E-Mail у нескольких сотрудников.

Ограничение уникальности обычно именуется следующим образом – сначала идет префикс «UQ_», далее название таблицы и после знака подчеркивания идет имя поля, на которое накладывается данное ограничение.

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

ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения UNIQUE(поле1,поле2,…)
При помощи добавления к полю ограничения DEFAULT мы можем задать значение по умолчанию, которое будет подставляться в случае, если при вставке новой записи данное поле не будет перечислено в списке полей команды INSERT. Данное ограничение можно задать непосредственно при создании таблицы.

Давайте добавим в таблицу Employees новое поле «Дата приема» и назовем его HireDate и скажем что значение по умолчанию у данного поля будет текущая дата:

ALTER TABLE Employees ADD HireDate date NOT NULL DEFAULT SYSDATETIME()
Или если столбец HireDate уже существует, то можно использовать следующий синтаксис:

ALTER TABLE Employees ADD DEFAULT SYSDATETIME() FOR HireDate
Здесь я не указал имя ограничения, т.к. в случае DEFAULT у меня сложилось мнение, что это не столь критично. Но если делать по-хорошему, то, думаю, не нужно лениться и стоит задать нормальное имя. Делается это следующим образом:

ALTER TABLE Employees ADD CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME() FOR HireDate
Та как данного столбца раньше не было, то при его добавлении в каждую запись в поле HireDate будет вставлено текущее значение даты.

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

INSERT Employees(ID,Name,Email)VALUES(1004,N"Сергеев С.С.","[email protected]")
Посмотрим, что получилось:

SELECT * FROM Employees

ID Name Birthday Email PositionID DepartmentID ManagerID HireDate
1000 Иванов И.И. 1955-02-19 [email protected] 2 1 NULL 2015-04-08
1001 Петров П.П. 1983-12-03 [email protected] 3 4 1003 2015-04-08
1002 Сидоров С.С. 1976-06-07 [email protected] 1 2 1000 2015-04-08
1003 Андреев А.А. 1982-04-17 [email protected] 4 3 1000 2015-04-08
1004 Сергеев С.С. NULL [email protected] NULL NULL NULL 2015-04-08

Проверочное ограничение CHECK используется в том случае, когда необходимо осуществить проверку вставляемых в поле значений. Например, наложим данное ограничение на поле табельный номер, которое у нас является идентификатором сотрудника (ID). При помощи данного ограничения скажем, что табельные номера должны иметь значение от 1000 до 1999:

ALTER TABLE Employees ADD CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999)
Ограничение обычно именуется так же, сначала идет префикс «CK_», затем имя таблицы и имя поля, на которое наложено это ограничение.

Попробуем вставить недопустимую запись для проверки, что ограничение работает (мы должны получить соответствующую ошибку):

INSERT Employees(ID,Email) VALUES(2000,"[email protected]")
А теперь изменим вставляемое значение на 1500 и убедимся, что запись вставится:

INSERT Employees(ID,Email) VALUES(1500,"[email protected]")
Можно так же создать ограничения UNIQUE и CHECK без указания имени:

ALTER TABLE Employees ADD UNIQUE(Email) ALTER TABLE Employees ADD CHECK(ID BETWEEN 1000 AND 1999)
Но это не очень хорошая практика и лучше задавать имя ограничения в явном виде, т.к. чтобы разобраться потом, что будет сложнее, нужно будет открывать объект и смотреть, за что он отвечает.

При хорошем наименовании много информации об ограничении можно узнать непосредственно по его имени.

И, соответственно, все эти ограничения можно создать сразу же при создании таблицы, если ее еще нет. Удалим таблицу:

DROP TABLE Employees
И пересоздадим ее со всеми созданными ограничениями одной командой CREATE TABLE:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, HireDate date NOT NULL DEFAULT SYSDATETIME(), -- для DEFAULT я сделаю исключение CONSTRAINT PK_Employees PRIMARY KEY (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT UQ_Employees_Email UNIQUE (Email), CONSTRAINT CK_Employees_ID CHECK (ID BETWEEN 1000 AND 1999))

INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID)VALUES (1000,N"Иванов И.И.","19550219","[email protected]",2,1), (1001,N"Петров П.П.","19831203","[email protected]",3,3), (1002,N"Сидоров С.С.","19760607","[email protected]",1,2), (1003,N"Андреев А.А.","19820417","[email protected]",4,3)

Немного про индексы, создаваемые при создании ограничений PRIMARY KEY и UNIQUE

Как можно увидеть на скриншоте выше, при создании ограничений PRIMARY KEY и UNIQUE автоматически создались индексы с такими же названиями (PK_Employees и UQ_Employees_Email). По умолчанию индекс для первичного ключа создается как CLUSTERED, а для всех остальных индексов как NONCLUSTERED. Стоит сказать, что понятие кластерного индекса есть не во всех СУБД. Таблица может иметь только один кластерный (CLUSTERED) индекс. CLUSTERED – означает, что записи таблицы будут сортироваться по этому индексу, так же можно сказать, что этот индекс имеет непосредственный доступ ко всем данным таблицы. Это так сказать главный индекс таблицы. Если сказать еще грубее, то это индекс, прикрученный к таблице. Кластерный индекс – это очень мощное средство, которое может помочь при оптимизации запросов, пока просто запомним это. Если мы хотим сказать, чтобы кластерный индекс использовался не в первичном ключе, а для другого индекса, то при создании первичного ключа мы должны указать опцию NONCLUSTERED:

ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения PRIMARY KEY NONCLUSTERED(поле1,поле2,…)
Для примера сделаем индекс ограничения PK_Employees некластерным, а индекс ограничения UQ_Employees_Email кластерным. Первым делом удалим данные ограничения:

ALTER TABLE Employees DROP CONSTRAINT PK_Employees ALTER TABLE Employees DROP CONSTRAINT UQ_Employees_Email
А теперь создадим их с опциями CLUSTERED и NONCLUSTERED:

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED (ID) ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE CLUSTERED (Email)
Теперь, выполнив выборку из таблицы Employees, мы увидим, что записи отсортировались по кластерному индексу UQ_Employees_Email:

SELECT * FROM Employees

ID Name Birthday Email PositionID DepartmentID HireDate
1003 Андреев А.А. 1982-04-17 [email protected] 4 3 2015-04-08
1000 Иванов И.И. 1955-02-19 [email protected] 2 1 2015-04-08
1001 Петров П.П. 1983-12-03 [email protected] 3 3 2015-04-08
1002 Сидоров С.С. 1976-06-07 [email protected] 1 2 2015-04-08

До этого, когда кластерным индексом был индекс PK_Employees, записи по умолчанию сортировались по полю ID.

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

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

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

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

Подытожим

На данном этапе мы познакомились со всеми видами ограничений, в их самом простом виде, которые создаются командой вида «ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения …»:
  • PRIMARY KEY – первичный ключ;
  • FOREIGN KEY – настройка связей и контроль ссылочной целостности данных;
  • UNIQUE – позволяет создать уникальность;
  • CHECK – позволяет осуществлять корректность введенных данных;
  • DEFAULT – позволяет задать значение по умолчанию;
  • Так же стоит отметить, что все ограничения можно удалить, используя команду «ALTER TABLE имя_таблицы DROP CONSTRAINT имя_ограничения».
Так же мы частично затронули тему индексов и разобрали понятие кластерный (CLUSTERED ) и некластерный (NONCLUSTERED ) индекс.

Создание самостоятельных индексов

Под самостоятельностью здесь имеются в виду индексы, которые создаются не для ограничения PRIMARY KEY или UNIQUE.

Индексы по полю или полям можно создавать следующей командой:

CREATE INDEX IDX_Employees_Name ON Employees(Name)
Так же здесь можно указать опции CLUSTERED, NONCLUSTERED, UNIQUE, а так же можно указать направление сортировки каждого отдельного поля ASC (по умолчанию) или DESC:

CREATE UNIQUE NONCLUSTERED INDEX UQ_Employees_EmailDesc ON Employees(Email DESC)
При создании некластерного индекса опцию NONCLUSTERED можно отпустить, т.к. она подразумевается по умолчанию, здесь она показана просто, чтобы указать позицию опции CLUSTERED или NONCLUSTERED в команде.

Удалить индекс можно следующей командой:

DROP INDEX IDX_Employees_Name ON Employees
Простые индексы так же, как и ограничения, можно создать в контексте команды CREATE TABLE.

Для примера снова удалим таблицу:

DROP TABLE Employees
И пересоздадим ее со всеми созданными ограничениями и индексами одной командой CREATE TABLE:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, HireDate date NOT NULL CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME(), ManagerID int, CONSTRAINT PK_Employees PRIMARY KEY (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID), CONSTRAINT UQ_Employees_Email UNIQUE(Email), CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999), INDEX IDX_Employees_Name(Name))
Напоследок вставим в таблицу наших сотрудников:

INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID,ManagerID)VALUES (1000,N"Иванов И.И.","19550219","[email protected]",2,1,NULL), (1001,N"Петров П.П.","19831203","[email protected]",3,3,1003), (1002,N"Сидоров С.С.","19760607","[email protected]",1,2,1000), (1003,N"Андреев А.А.","19820417","[email protected]",4,3,1000)
Дополнительно стоит отметить, что в некластерный индекс можно включать значения при помощи указания их в INCLUDE. Т.е. в данном случае INCLUDE-индекс чем-то будет напоминать кластерный индекс, только теперь не индекс прикручен к таблице, а необходимые значения прикручены к индексу. Соответственно, такие индексы могут очень повысить производительность запросов на выборку (SELECT), если все перечисленные поля имеются в индексе, то возможно обращений к таблице вообще не понадобится. Но это естественно повышает размер индекса, т.к. значения перечисленных полей дублируются в индексе.

Вырезка из MSDN. Общий синтаксис команды для создания индексов

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON (column [ ASC | DESC ] [ ,...n ]) [ INCLUDE (column_name [ ,...n ]) ]

Подытожим

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

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

Заключение по DDL

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

Главное - понять суть, а остальное дело практики.

Удачи вам в освоении этого замечательного языка под названием 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. Тем не менее он может оказаться полезным для решения некоторых задач (я предпочитаю его курсору).

  • Перевод

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

Существует три базовых правила для написания такого 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 , которые помогут начинающим программистам баз данных освоить данный язык и писать запросы и приложения на языке Transact-SQL.

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

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

Описание справочника

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

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

Для детального изучения языка T-SQL рекомендую почитать мою книгу «Путь программиста T-SQL. Самоучитель по языку Transact-SQL », в которой я максимально подробно, с большим количеством примеров рассказываю о языке T-SQL.

Transact-SQL справочник для начинающих

База данных

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

Создание

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

CREATE DATABASE test

где, test это название базы данных.

Подробней о создании базы данных на SQL сервере, мы разговаривали в материале Как создать базу данных в MS Sql 2008

Удаление

Если Вам необходимо удалить базу данных, то можете использовать запрос:

DROP DATABASE test

Изменение

Для изменений параметров базы данных можно использовать графический интерфейс Management Studio, в котором все параметры подробно описаны, а можно посылать запросы ALTER DATABASE, например, для включения автоматического сжатия базы данных test, используем следующий запрос

ALTER DATABASE test SET AUTO_SHRINK ON; --А для выключения ALTER DATABASE test SET AUTO_SHRINK OFF;

Надеюсь понятно, ALTER DATABASE команда на изменение, test название изменяемой базы данных, SET команда, указывающая на то, что мы будем изменять параметры базы данных, AUTO_SHRINK непосредственно сам параметр, ON/OFF значение параметра.

Типы данных

Самые распространенные и часто используемые

Точные числа

  • tinyint — 1 байт
  • smallint — 2 байта
  • int — 4 байта
  • bigint — 8 байт
  • numeric и decimal (тип с фиксированной точностью и масштабом )
  • money — 8 байт
  • smallmoney — 4 байт

Приблизительные числа

  • float [ (n) ] – размер зависит от n (n может быть от 1 до 53, по умолчанию 53)
  • real — 4 байта

Дата и время

  • date – дата
  • time — время
  • datetime — дата, включающая время дня с долями секунды в 24-часовом формате.

Символьные строки

  • char [ (n) ] – строка с фиксированной длиной, где n длина строки (от 1 до 8000). Размер при хранении составляет n байт.
  • varchar [ (n | max) ] — строка с фиксированной длиной, где n длина строки (от 1 до 8000). Если указать max, то, максимальный размер при хранении составит 2^31-1 байт (2 ГБ), а при указании n то фактическая длина введенных данных плюс 2 байта.
  • text – строковые данные переменной длины, максимальный размер 2 147 483 647 байт (2 ГБ).
  • nchar [ (n) ] — строка с фиксированной длиной в Юникоде, где n длина строки (от 1 до 4000). Размер при хранении составляет удвоенное значение n в байтах
  • nvarchar [ (n | max) ] — строка с фиксированной длиной в Юникоде, где n длина строки (от 1 до 4000). При указании max, максимальный размер при хранении составит 2^31-1 байт (2 ГБ), а если n, то удвоенная фактическая длина введенных данных плюс 2 байта.
  • ntext — строковые данные переменной длины, с максимальной длиной строки 1 073 741 823 байт.

Двоичные данные

  • binary [ (n) ] — двоичные данные с фиксированной длиной, размером n байт, где n значение от 1 до 8000. Размер при хранении составляет n байт.
  • varbinary [ (n | max) ] — двоичные данные с переменной длиной, где n может иметь значение от 1 до 8000. Если указать max то максимальный размер при хранении составит 2^31-1 байт(2 ГБ). При указании n то размер хранения это фактическая длина введенных данных плюс 2 байта.
  • image — двоичные данные переменной длины, размером от 0 до 2^31 – 1 (2 147 483 647) байт.

Другие

  • xml –хранение xml данных. Подробно рассматривали в материале Transact-sql – работа с xml , а если Вы вообще не знаете что такое XML, то об это мы разговаривали в статье Основы XML для начинающих .
  • table – хранение результирующего набора строк.

Таблицы

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

Создание

CREATE TABLE test_table( IDENTITY(1,1) NOT NULL,--идентификатор, целое число int, не разрешены значения NULL (50) NULL, --ФИО, строка длиной 50 символов, значения NULL разрешены NULL, --сумма, приблизительное числовое значение, значения NULL разрешены NULL, --дата и время, значения NULL разрешены (100) NULL --строка длиной 100 символов, значения NULL разрешены) ON GO

Добавление колонки

ALTER TABLE test_table ADD prosto_pole numeric(18, 0) NULL
  • test_table — это название таблицы;
  • add — команда на добавление;
  • prosto_pole – название колонки;
  • pole numeric(18, 0) – тип данных новой колонки;
  • NULL – параметр означающий что в данном поле можно хранить значение NULL.

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

Давайте изменим, тип данных нового поля, которое мы только что создали (prosto_pole) с numeric(18, 0) на bigint и увеличим длину поля comment до 300 символов.

ALTER TABLE test_table ALTER COLUMN prosto_pole bigint; ALTER TABLE test_table ALTER COLUMN comment varchar(300);

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

Удаление колонки

Для удаления определенной колонки используем команду drop, например, для удаления поля prosto_pole используем следующий запрос

ALTER TABLE test_table DROP COLUMN prosto_pole

Удаление таблицы

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

DROP TABLE test_table

Представления

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

Создание

CREATE VIEW test_view AS SELECT id, fio, comment FROM test_table WHERE summa > 1000 GO

Пример обращения к представлению :

SELECT * FROM test_view

Изменение

ALTER VIEW test_view AS SELECT id, fio, comment FROM test_table WHERE summa > 1500 GO

Удаление

DROP VIEW test_view

Системные представления

В СУБД MS SQL Server есть таки системные объекты, которые могут предоставить иногда достаточно полезную информацию, например системные представления. Сейчас мы разберем парочку таких представлений. Обращаться к ним можно также как и к обычным представлениям (например, select * from название представление )

  • sys.all_objects – содержит все объекты базы данных, включая такие параметры как: название, тип, дата создания и другие.
  • sys.all_columns – возвращает все колонки таблиц с подробными их характеристиками.
  • sys.all_views – возвращает все представления базы данных.
  • sys.tables – все таблицы базы данных.
  • sys.triggers – все триггеры базы данных.
  • sys.databases – все базы данных на сервере.
  • sys.sysprocesses – активные процессы, сессии в базе данных.

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

Функции

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

Создание

CREATE FUNCTION test_function (@par1 bigint, @par2 float) RETURNS varchar(300) AS BEGIN DECLARE @rezult varchar(300) SELECT @rezult=comment FROM test_table WHERE id = @par1 AND summa > @par2 RETURN @rezult END
  • CREATE FUNCTION – команда на создание объекта функция;
  • test_function – название новой функции;
  • @par1 и @par2 – входящие параметры;
  • RETURNS varchar(300) – тип возвращаемого результата;
  • DECLARE @rezult varchar(300) – объявление переменной с типом varchar(300);
  • Инструкция select в нашем случае и есть действия функции;
  • RETURN @rezult – возвращаем результат;
  • BEGIN и END – соответственно начала и конец кода функции.

Пример использования ее в запросе :

SELECT test_function(1, 20)

Изменение

ALTER FUNCTION test_function (@par1 bigint, @par2 float) RETURNS varchar(300) AS BEGIN DECLARE @rezult varchar(300) SELECT @rezult=comment FROM test_table_new WHERE id = @par1 AND summa >= @par2 RETURN @rezult END

Удаление

DROP FUNCTION test_function

Встроенные функции

Помимо того, что SQL сервер позволяет создавать пользовательские функции, он также предоставляет возможность использовать встроенные функции, которые за Вас уже написали разработчики СУБД. Их очень много, поэтому самые распространенные я разбил на группы и попытался их кратко описать.

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

Здесь я приведу несколько примеров функций, которые возвращают различные системные данные

  • @@VERSION – возвращает версию SQL сервера;
  • @@SERVERNAME – возвращает имя сервера;
  • SUSER_NAME() – имя входа пользователя на сервер, другими словами, под каким логином работает тот или иной пользователь;
  • user_name() – имя пользователя базы данных;
  • @@SERVICENAME – название сервиса СУБД;
  • @@IDENTITY — последний вставленный в таблицу идентификатор;
  • db_name() — имя текущей базы данных;
  • db_id() – идентификатор базы данных.

Агрегатные функции

Функции, которые вычисляют какое-то значение на основе набора (группы) значений. Если при вызове этих функций нужно указать колонку для вывода результата, то необходимо выполнить группировку данных (group by) по данному полю. Подробно данную конструкцию мы рассматривали в статье Transact-SQL группировка данных group by

  • avg – возвращает среднее значение;
  • count – количество значений;
  • max – максимальное значение;
  • min – минимальное значение;
  • sum – сумма значений.

Пример использования :

SELECT COUNT(*) as count, SUM(summa) as sum, MAX(id) as max, MIN(id) as min, AVG(summa) as avg FROM test_table

Строковые функции

Данный вид функций соответственно работает со строками.

Left (строковое выражение , количество символов ) – возвращает указанное число символов строки начиная слева.

Пример

SELECT LEFT("Пример по работе функции left", 10) --Результат "Пример по"

Right (строковое выражение , количество символов ) – возвращает указанное число символов строки начиная справа

Пример

SELECT Right("Пример по работе функции Right", 10) -- Результат "кции Right"

Len (строка ) – возвращает длину строки.

Пример

SELECT len("Пример по работе функции len") --Результат 28

Lower (строка ) – возвращает строку, в которой все символы приведены к нижнему регистру.

Пример

SELECT lower("Пример по работе функции lower") --Результат "пример по работе функции lower"

Upper (строка ) — возвращает строку, в которой все символы приведены к верхнему регистру.

Пример

SELECT Upper("Пример по работе функции Upper") --Результат "ПРИМЕР ПО РАБОТЕ ФУНКЦИИ UPPER"

Ltrim (строка ) – возвращает строку, в которой все начальные пробелы удалены.

Пример

SELECT ltrim(" Пример по работе функции ltrim") --Результат "Пример по работе функции ltrim"

Rtrim (строка ) – возвращает строку, в которой все пробелы справа удалены

Пример

SELECT Rtrim (" Пример по работе функции Rtrim ") -- Результат" Пример по работе функции Rtrim"

Replace (строка , что ищем , на что заменяем ) – заменяет в строковом выражении все вхождения указанные во втором параметре, символами указанным в третьем параметре.

Пример

SELECT Replace ("Пример по работе функции Replace", "по работе", "ЗАМЕНА") -- Результат "Пример ЗАМЕНА функции Replace"

Replicate (строка , количество повторений ) – повторяет строку (первый параметр) столько раз, сколько указанно во втором параметре.

Пример

SELECT Replicate ("Пример Replicate ", 3) -- Результат "Пример Replicate Пример Replicate Пример Replicate "

Reverse (строка ) – возвращает все в обратном порядке.

Пример

SELECT Reverse ("Пример по работе функции Reverse") -- Результат "esreveR иицкнуф етобар оп ремирП"

Space (число пробелов ) – возвращает строку в виде указанного количества пробелов.

Пример

SELECT Space(10) -- Результат " "

Substring (строка , начальная позиция , сколько символов ) – возвращает строку, длиной в число указанное в третьем параметре, начиная с символа указанного во втором параметре.

Пример

SELECT Substring("Пример по работе функции Substring", 11, 14) -- Результат "работе функции"

Математические функции

Round (число , точность округления ) – округляет числовое выражение до числа знаков указанного во втором параметре

Пример

SELECT Round(10.4569, 2) -- Результат "10.4600"

Floor (число ) – возвращает целое число, округленное в меньшую сторону.

Пример

SELECT Floor(10.4569) -- Результат "10"

Ceiling (число ) – возвращает целое число, округленное в большую сторону.

Пример

SELECT Ceiling (10.4569) -- Результат "11"

Power (число , степень ) — возвращает число возведенное в степень указанную во втором параметре.

Пример

SELECT Power(5,2) -- Результат "25"

Square (число ) – возвращает числовое значение, возведенное в квадрат

Пример

SELECT Square(5) -- Результат "25"

Abs (число ) – возвращает абсолютное положительное значение

Пример

SELECT Abs(-5) -- Результат "5"

Log (число ) – натуральный логарифм с плавающей запятой.

Пример

SELECT Log(5) -- Результат "1,6094379124341"

Pi – число пи.

Пример

SELECT Pi() -- Результат "3,14159265358979"

Rand – возвращает случайное число с плавающей запятой от 0 до 1

Пример

SELECT rand() -- Результат "0,713273187517105"

Функции даты и времени

Getdate() – возвращает текущую дату и время

Пример

SELECT Getdate() -- Результат "2014-10-24 16:36:23.683"

Day (дата ) – возвращает день из даты.

Пример

SELECT Day(Getdate()) -- Результат "24"

Month (дата) – возвращает номер месяца из даты.

Пример

SELECT Month(Getdate()) -- Результат "10"

Year (дата ) –возвращает год из даты

Пример

SELECT year(Getdate()) -- Результат "2014"

DATEPART (раздел даты , дата ) – возвращает из даты указанный раздел (DD,MM,YYYY и др.)

Пример

SELECT DATEPART(MM,GETDATE()) -- Результат "10"

Isdate (дата ) – проверяет введенное выражение, является ли оно датой

Пример

SELECT Isdate(GETDATE()) -- Результат "1"

Функции преобразование

Cast (выражение as тип данных ) – функция для преобразования одного типа в другой. В примере мы преобразуем тип float в int

Пример

SELECT CAST(10.54 as int) --результат 10

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

Пример

SELECT GETDATE(), CONVERT(DATE, GETDATE(), 104) --Результат --2014-10-24 15:20:45.270 – без преобразования; --2014-10-24 после преобразования.

Табличные функции

Создаются, для того чтобы получать из них данные как из таблиц, но после различного рода вычислений. Подробно о табличных функциях мы разговаривали в материале Transact-sql – Табличные функции и временные таблицы

Создание

--название нашей функции CREATE FUNCTION fun_test_tabl (--входящие параметры и их тип @id INT) --возвращающее значение, т.е. таблица RETURNS TABLE AS --сразу возвращаем результат RETURN (--сам запрос или какие то вычисления SELECT * FROM test_table where id = @id) GO

Изменение

--название нашей функции ALTER FUNCTION fun_test_tabl (--входящие параметры и их тип @id INT) --возвращающее значение, т.е. таблица RETURNS TABLE AS --сразу возвращаем результат RETURN (--сам запрос или какие то вычисления SELECT * FROM test_table where id = @id and summa > 100) GO

Удаление

DROP FUNCTION fun_test_tabl

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

Пример обращения к этой функции

SELECT * FROM fun_test_tabl(1)

Процедуры

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

Создание

CREATE PROCEDURE sp_test_procedure (@id INT) AS --объявляем переменные DECLARE @sum FLOAT --SQL инструкции SET @sum = 100 UPDATE test_table SET summa = summa + @sum WHERE id = @id GO

Изменение

ALTER PROCEDURE sp_test_procedure (@id int) AS --объявляем переменные DECLARE @sum float --SQL инструкции SET @sum = 500 UPDATE test_table SET summa = summa + @sum WHERE id = @id GO

Удаление

DROP PROCEDURE sp_test_procedure

Вызов процедуры

Можно вызывать по разному, например:

EXECUTE sp_test_procedure 1 --или EXEC sp_test_procedure 1

Где, EXECUTE и EXEC вызов процедуры, sp_test_procedure соответственно название нашей процедуры, 1 значение параметра

Системные процедуры

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

Их огромное множество, поэтому приведу всего несколько примеров.

sp_configure – процедура для отображения и внесения изменений в конфигурацию ядра СУБД. Первый параметр название параметра конфигурации, второй параметр значение.

Пример

Изменяем значение параметра EXEC sp_configure "Ad Hoc Distributed Queries",1 reconfigure --применяем EXEC sp_configure --просто просматриваем значения всех параметров

где, ‘Ad Hoc Distributed Queries’ — это название параметра, 1 соответственно значение, на которое мы хотим изменить, reconfigure применят введенное значение.

На практике мы применяли эту процедуру в материале Межбазовый запрос на Transact-SQL

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

Пример

EXECUTE sp_executesql N"SELECT * FROM test_table WHERE id = @id", N"@id int", @id = 1

Где, первый параметр — sql инструкция (строка в Юникоде), второй — определение всех параметров встроенных в sql инструкцию, третий — значение параметров.

sp_help – возвращает подробные сведения о любом объекте базы данных.

Пример

EXECUTE sp_help "test_table"

sp_rename – переименовывает объект в базе данных. Можно использовать для переименования таблиц, индексов, название колонок в таблицах, Не рекомендуется использовать эту процедуру для переименования пользовательских процедур, триггеров, функций.

Пример переименования таблицы

EXEC sp_rename "test_table", "test_table_new"

где, первым параметром идет объект со старым названием, а второй параметр — это новое название объекта.

Пример переименования столбца в таблице

EXEC sp_rename "test_table.summa", "summa_new", "COLUMN"

Третьим параметром указывается, что переименовывается колонка.

Триггеры

Триггер – это обычная процедура, но вызывается она событием, а не пользователем. Событие, например, может быть вставка новой строки в таблицу (insert), обновление данных в таблице (update) или удаление данных из таблицы (delete).

Создание

CREATE TRIGGER trg_test_table_update ON test_table for UPDATE --можно также delete, insert AS BEGIN --sql инструкции в случае UPDATE END GO

Изменение

ALTER TRIGGER trg_test_table_update ON test_table for insert --можно также delete, update AS BEGIN --sql инструкции в случае insert END GO

Удаление

DROP TRIGGER trg_test_table_update

Включение/Отключение

--отключение DISABLE TRIGGER trg_test_table_update ON test_table; --включение ENABLE TRIGGER trg_test_table_update ON test_table;

О триггерах мы разговаривали в статье — Как создать триггер на Transact-SQL .

Индексы

Это объект базы данных, который повышает производительность поиска данных, за счет сортировки данных по определенному полю. Если провести аналогию то, например, искать определенную информацию в книге намного легче и быстрей по его оглавлению, чем, если бы этого оглавления не было. В СУБД MS SQL Server существует следующие типы индексов:

Кластеризованный индекс — при таком индексе строки в таблице сортируются с заданным ключом, т.е. указанным полем. Данный тип индексов у таблицы в MS SQL сервере может быть только один и, начиная с MS SQL 2000, он автоматически создается при указании в таблице первичного ключа (PRIMARY KEY).

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

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

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

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

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

Columnstore index доступен начиная с 2012 версии SQL сервера в редакциях Enterprise, Developer и Evaluation.

Создание

Кластеризованного индекса

CREATE CLUSTERED INDEX idx_clus_one ON test_table(id) GO

Где, CREATE CLUSTERED INDEX — это инструкция к созданию кластеризованного индекса, idx_clus_one название индекса, test_table(id) соответственно таблица и ключевое поле для сортировки.

Некластеризованного индекса

CREATE INDEX idx_no_clus ON test_table(summa) GO

Columnstore index

CREATE columnstore INDEX idx_columnstore ON test_table(date_create) GO

Отключение

--отключение ALTER INDEX idx_no_clus ON test_table DISABLE --включение, перестроение ALTER INDEX idx_no_clus ON test_table REBUILD

Удаление

DROP INDEX idx_no_clus ON test_table GO

Курсоры

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

Пример (все это в коде процедуры)

Объявляем переменные DECLARE @id BIGINT DECLARE @fio VARCHAR(100) DECLARE @summa FLOAT --объявляем курсор DECLARE test_cur CURSOR FOR SELECT id, fio, summa FROM test_table --открываем курсор OPEN test_cur --считываем данные первой строки в курсоре --и записываем их в переменные FETCH NEXT FROM test_cur INTO @id, @fio, @summa --запускаем цикл до тех пор, пока не закончатся строки в курсоре WHILE @@FETCH_STATUS = 0 BEGIN --на каждую итерацию цикла можем выполнять sql инструкции --..................SQL инструкции................. --считываем следующую строку курсора FETCH NEXT FROM test_cur INTO @id, @fio, @summa END --закрываем курсор CLOSE test_cur DEALLOCATE test_cur

Подробно о курсорах мы разговаривали в материале Использование курсоров и циклов в Transact-SQL .

Запросы DML

DML (Data Manipulation Language ) – это операторы SQL, с помощью которых осуществляется манипуляция данными. К ним относятся select, update, insert, delete.

SELECT

Пример

SELECT * FROM test_table

UPDATE

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

Пример

Обновятся все строки в таблице UPDATE test_table SET summa=500 --обновятся только строки, у которых id больше 10 UPDATE test_table SET summa=100 WHERE id > 10

INSERT

Оператор на добавление данных

Добавление одной строки INSERT INTO test_table (fio, summa, date_create, comment) VALUES ("ФИО",100, "26.10.2014", "тестовая запись") --массовое добавление на основе запроса INSERT INTO test_table SELECT fio, summa, date_create, comment FROM test_table

DELETE

С помощью этого оператора можно удалить данные.

Пример

Очищение всей таблицы DELETE test_table --удаление только строк попавших под условие DELETE test_table WHERE summa > 100

Вот и все, справочник закончился! Надеюсь, он Вам хоть как-то помог. Удачи!

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

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

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

  • DDL (Data Definition Language) - операторы определения данных
  • DML (Data Manipulation Language) - операторы манипуляции данными
  • DCL (Data Control Language) - операторы определения доступа к данным
  • TCL (Transaction Control Language) - операторы управления транзакциями

SQL является стандартизированным языком. Стандартный SQL поддерживается комитетом стандартов ANSI (Американский национальный институт стандартов), и соответственно называется ANSI SQL.

Многие разработчики СУБД расширили возможности SQL, введя в язык дополнительные операторы или инструкции. Эти расширения необходимы для выполнения дополнительных функций или для упрощения выполнения определенных операций. И хотя часто они очень полезны, эти расширения привязаны к определенной СУБД и редко поддерживаются более чем одним разработчиком. Все крупные СУБД и даже те, у которых есть собственные расширения, поддерживают ANSI SQL (в большей или меньшей степени). Отдельные же реализации носят собственные имена (PL-SQL, Transact-SQL и т.д.). Transact-SQL (T-SQL) – реализация языка SQL корпорации Microsoft, используемая, в частности, и в SQL Server.

Запросы на выборку данных (оператор SELECT)

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

Выборка отдельных столбцов

SELECT

FROM Product

В приведенном выше операторе используется оператор SELECT для выборки одного столбца под названием Description из таблицы Product. Искомое имя столбца указывается сразу после ключевого слова SELECT, а ключевое слово FROM указывает на имя таблицы, из которой выбираются данные.

Для создания и тестирования данного запроса в Management Studio выполните следующие шаги:

Выборка нескольких столбцов

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

SELECT , InStock

FROM Product

Выборка всех столбцов

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

SELECT *

FROM Product

Сортировка данных

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

FROM Product

ORDER BY InStock

Это выражение идентично предыдущему, за исключением предложения ORDER BY, которое указывает СУБД отсортировать данные по возрастанию значений столбца InStock.

Сортировка по нескольким столбцам

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

SELECT IdProd, , InStock

FROM Product

ORDER BY InStock,

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

Указание направления сортировки

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

SELECT IdProd, , InStock

FROM Product

ORDER BY InStock DESC ,

Ключевое слово DESC применяется только к тому столбцу, после которого оно указано. В предыдущем примере ключевое слово DESC было указано для столбца InStock, но не для Description. Таким образом, столбец InStock отсортирован в порядке убывания, а столбец Description в возрастающем порядке (принятым по умолчанию).




Статьи по теме

Последние статьи
Популярные статьи
Выбор редакции

Сайт компьютерной помощи

© Copyright 2024,
rzdoro.ru -Сайт компьютерной помощи

  • Рубрики
  • Программы
  • Microsoft Office
  • Интернет
  • Linux
  • Программы
  • Microsoft Office
  • Интернет
  • Linux
rzdoro.ru