Как пользоваться sql server management studio

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Табельный номер	ФИО	Дата рождения	E-mail	Должность	Отдел
1000	Иванов И.И.	19.02.1955	i.ivanov@test.tt	Директор	Администрация
1001	Петров П.П.	03.12.1983	p.petrov@test.tt	Программист	ИТ
1002	Сидоров С.С.	07.06.1976	s.sidorov@test.tt	Бухгалтер	Бухгалтерия
1003	Андреев А.А.	17.04.1982	a.andreev@test.tt	Старший программист	ИТ

В данном случае столбцы таблицы имеют следующие наименования: Табельный номер, ФИО, Дата рождения, 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.

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

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

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

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

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

В данном случае нам придется заключать имена в квадратные скобки […].

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

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

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

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

Для того, чтобы задать обязательные для заполнения столбцы, можно использовать опцию NOT NULL.

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

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

Чтобы не быть голословным, приведу несколько примеров тех же команд для СУБД ORACLE:

Для ORACLE есть отличия в плане реализации типа varchar2, его кодировка зависит настроек БД и текст может сохраняться, например, в кодировке UTF-8. Помимо этого длину поля в ORACLE можно задать как в байтах, так и в символах, для этого используются дополнительные опции BYTE и CHAR, которые указываются после длины поля, например:

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

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

В данном случае, команда 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.

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

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

Можно также после имени столбца написать NULL, что будет означать, что в нем будут допустимы NULL-значения (не указанные), но этого делать не обязательно, так как данная характеристика подразумевается по умолчанию.

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

Так же данной командой мы можем изменить тип поля на другой совместимый тип, или же изменить его длину. Для примера давайте расширим поле Name до 50 символов:

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

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

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

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

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

Стоит отметить, что в MS SQL все поля, которые входят в первичный ключ, должны иметь характеристику NOT NULL.

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

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

После создания зальем в таблицу данные:

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

На самом деле имя ограничения можно и не задавать, в этом случае ему будет присвоено системное имя (наподобие «PK__Employee__3214EC278DA42077»):

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

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

Подытожим

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

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

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

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

Так как временная таблица в MS SQL аналогична обычной таблице, ее соответственно так же можно удалить самому командой DROP TABLE:

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

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

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

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

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

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

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

Заметим, что здесь мы использовали новую опцию IDENTITY, которая говорит о том, что данные в столбце ID будут нумероваться автоматически, начиная с 1, с шагом 1, т.е. при добавлении новых записей им последовательно будут присваиваться значения 1, 2, 3, и т.д. Такие поля обычно называют автоинкрементными. В таблице может быть определено только одно поле со свойством IDENTITY и обычно, но необязательно, такое поле является первичным ключом для данной таблицы.

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

То же самое проделаем для таблицы Departments:

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

ID	Name
1	Бухгалтер
2	Директор
3	Программист
4	Старший программист

ID	Name
1	Администрация
2	Бухгалтерия
3	ИТ

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

Тип ссылочных полей должен быть каким же, как и в справочниках, в данном случае это int.

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

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

И то же самое сделаем для второго поля:

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

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

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

Так же в некоторых случаях ссылку можно организовать по нескольким полям:

В данном случае в таблице «таблица_справочник» первичный ключ представлен комбинацией из нескольких полей (поле1, поле2,…).

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

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

ID	Name	Birthday	Email	Position	Department	PositionID	DepartmentID
1000	Иванов И.И.	NULL	NULL	Директор	Администрация	2	1
1001	Петров П.П.	NULL	NULL	Программист	ИТ	3	3
1002	Сидоров С.С.	NULL	NULL	Бухгалтер	Бухгалтерия	1	2
1003	Андреев А.А.	NULL	NULL	Старший программист	ИТ	4	3

Всё, поля PositionID и DepartmentID заполнены соответствующие должностям и отделам идентификаторами надобности в полях Position и Department в таблице 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

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

ID	Name	PositionName	DepartmentName
1000	Иванов И.И.	Директор	Администрация
1001	Петров П.П.	Программист	ИТ
1002	Сидоров С.С.	Бухгалтер	Бухгалтерия
1003	Андреев А.А.	Старший программист	ИТ

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

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

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

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

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

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

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

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

Удалим отдел с идентификатором 3 из таблицы Departments:

Посмотрим на данные таблицы 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):

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

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

Полностью очистим таблицу Employees при помощи команды TRUNCATE TABLE:

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

Подытожим

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

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

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

А теперь можно наложить на это поле ограничение-уникальности:

Теперь пользователь не сможет внести один и тот же E-Mail у нескольких сотрудников.

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

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

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

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

Или если столбец HireDate уже существует, то можно использовать следующий синтаксис:

Здесь я не указал имя ограничения, т.к. в случае DEFAULT у меня сложилось мнение, что это не столь критично. Но если делать по-хорошему, то, думаю, не нужно лениться и стоит задать нормальное имя. Делается это следующим образом:

Та как данного столбца раньше не было, то при его добавлении в каждую запись в поле HireDate будет вставлено текущее значение даты.

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

Посмотрим, что получилось:

ID	Name	Birthday	Email	PositionID	DepartmentID	ManagerID	HireDate
1000	Иванов И.И.	1955-02-19	i.ivanov@test.tt	2	1	NULL	2015-04-08
1001	Петров П.П.	1983-12-03	p.petrov@test.tt	3	4	1003	2015-04-08
1002	Сидоров С.С.	1976-06-07	s.sidorov@test.tt	1	2	1000	2015-04-08
1003	Андреев А.А.	1982-04-17	a.andreev@test.tt	4	3	1000	2015-04-08
1004	Сергеев С.С.	NULL	s.sergeev@test.tt	NULL	NULL	NULL	2015-04-08

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

Ограничение обычно именуется так же, сначала идет префикс «CK_», затем имя таблицы и имя поля, на которое наложено это ограничение.

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

А теперь изменим вставляемое значение на 1500 и убедимся, что запись вставится:

Можно так же создать ограничения UNIQUE и CHECK без указания имени:

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

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

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

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

Напоследок вставим в таблицу наших сотрудников:

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

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

Для примера сделаем индекс ограничения PK_Employees некластерным, а индекс ограничения UQ_Employees_Email кластерным. Первым делом удалим данные ограничения:

А теперь создадим их с опциями CLUSTERED и NONCLUSTERED:

Теперь, выполнив выборку из таблицы Employees, мы увидим, что записи отсортировались по кластерному индексу UQ_Employees_Email:

ID	Name	Birthday	Email	PositionID	DepartmentID	HireDate
1003	Андреев А.А.	1982-04-17	a.andreev@test.tt	4	3	2015-04-08
1000	Иванов И.И.	1955-02-19	i.ivanov@test.tt	2	1	2015-04-08
1001	Петров П.П.	1983-12-03	p.petrov@test.tt	3	3	2015-04-08
1002	Сидоров С.С.	1976-06-07	s.sidorov@test.tt	1	2	2015-04-08

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

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

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

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

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

Подытожим

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

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

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

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

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

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

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

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

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

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

Напоследок вставим в таблицу наших сотрудников:

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

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

Подытожим

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

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

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

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

Learn SQL Server Management Studio — Part 1 Step by Step Setup

A free and powerful tool to leverage data, skill-up, and stand out during interviews

Towards Data Science

What to Expect?

In this series of articles, I will do a walkthrough of the Microsoft SQL Server Management Studio (SSMS). Today we’ll start with a setup of the environment. In the next articles, we’ll create our own databases and tables. Create, read, update, delete records. We’ll write, save and alter our own stored procedures. We’ll also schedule jobs, create SSIS packages… If you’re not familiar with some of those terms, you’ll soon get more than a grasp, and create and manage them yourself.

SSMS is a powerful tool, and an interesting skill to have on your belt if you’re looking to skill-up or interview for another job. It is also a chance to put into practice what you learned during your SQL 101 on a browser window, but never got a chance to experience the feel of database management software.

What is SQL?

SQL stands for Structured Query Language. It is designed to manage data from relational databases. And spoiler alert: it does it really well. The language was born in the 1970s¹ and has since evolved quite a bit. Over the 40+ years of its existence, the language was enriched by new data types, operators, and functionalities.

What is Microsoft SQL Server Management Studio?

Microsoft SQL Server Management Studio mostly referred to as SSMS is a tool used for configuring, managing, and administering data. The tool allows you to connect to SQL Server instances on-premises (the cool kids say ‘on-prem’ ) and in the cloud.

What can you achieve?

In short: a lot of things. A more verbose answer would be that within SSMS you can:

• connect to servers and their instances,
• create and manage databases and their underlying tables,
• connect to visualization tools such as PowerBI, Tableau, Spotfire, …
• connect to automation tools like Power Automate, or to low and no-code platforms like PowerApps and others,
• create and manage stored procedures, which are scripts you can write and save. Those procedures can then later be executed to leverage your data and for instance apply some manipulations to your data, archive records or tables, send emails, …
• create and manage triggers, which are automated actions triggered following a certain action (creation/deletion/update of records,…)
• create and manage scheduled jobs, so that a given stored procedure would run at any defined frequency,
• backup and restore data,
• import and export data
• create SSIS packages, which in Microsoft’s own words² are:

“SQL Server Integration Services (SSIS) is a platform for building enterprise-level data integration and data transformations solutions. Use Integration Services to solve complex business problems by copying or downloading files, loading data warehouses, cleaning, and mining data, and managing SQL Server objects and data”

SSIS packages are notably useful if you’d like to copy tables between databases or between servers. An SSIS package can be saved, and then used as part of a scheduled job.

Now, that’s quite a list. But how and where to get started? Read on!

Setup Your Environment

This should come as a relief: the installation and setup won’t be a nightmare. This software is mature and stable and I haven’t encountered any bugs or crashes. There will be two components to install to be all set.

1. Download SSMS

Go to this page and download the latest version under ‘Download SSMS’. At the time of writing this piece, it’s v18.8. The launcher requires you to have approximately 657MB of free disk space. Once you’re through with the installation, you should find such a logo.

A database with the equivalent of the hammer & the sickle of any data-minded person. Upon launching the program, this screen should appear:

And quickly followed by a window letting you connect to a server:

OK, now is the right time to panic. “Connecting to a server? But you haven’t told us a server would be needed.” And you’d be right. If you have access to one, you can enter its name and instance: <name>\instance#. For example myserver\inst1.

If you aren’t fortunate to have a server handy, do not worry, there’s a workaround to still enjoy and discover SSMS. Enters the 2nd download:

2. SQL Express

We’ll use our computer and SQL Express as our server & instance. Go to this page and download the free specialized edition ‘Express’

Once you’ve installed it, let’s return to the SSMS ‘Connect to server’ window below:

First, we’ll need to find our computer name. On Windows 10, hit the Windows key then type ‘about your PC and you should land on a page where our required info is after ‘Device name’.

You can also right-click on the “My Computer” icon on your desktop, then select Properties. You’ll find the same window as above.

Copy this name and paste it within the SSMS window. Then write \SQLExpress. It should look like this, with your computer name instead of mine.

Hit connect… and we’re live! The Object Explorer presents a neat list of folders, each encapsulating SSMS features, and capabilities.

That’s it for the initial setup. In the next article, we’ll discuss database & table creation & design, the CRUD acronym (create, read, update, delete) for records management, and more.

Happy Coding!

Continue the journey in SQL with Part 2 on Databases, Tables & more!

Getting Started With SQL Server Management Studio

The skillset that will make you fun at parties!

Or select another episode of your choice

In Episode 3, we introduce the CRUD operations and the Primary and Foreign Keys — Part 3 CRUD Operations, Primary & Foreign Keys

In Episode 4, we discuss schemas & the main normalization steps — Part 4 Schemas & Normalization

In Episode 5, we cover Stored Procedures & Scheduling, this is a real beast. I can’t stress enough how this will facilitate and automate your daily (data-)life. Part 5 Stored Procedures & Scheduling

In Episode 6, we introduce SSIS Packages for ETL, and reviewed out to import and export data between two databases, and between a database and Excel. Part 6 SSIS Packages Introduction

In Episode 7, we connect SQL Studio to PowerBI and build our first visuals. Part 7 Connect to PowerBI & First Visuals

Thanks for reading! Enjoyed this story? Join Medium for complete access to all my stories.

Name already in use

sql-docs / docs / ssms / sql-server-management-studio-ssms.md

• Go to file T
• Go to line L
• Copy path
• Copy permalink

• Open with Desktop
• View raw
• Copy raw contents Copy raw contents

Copy raw contents

Copy raw contents

What is SQL Server Management Studio (SSMS)?

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure. Use SSMS to access, configure, manage, administer, and develop all components of [!INCLUDE ssnoversion-md], Azure SQL Database, Azure SQL Managed Instance, SQL Server on Azure VM, and Azure Synapse Analytics. SSMS provides a single comprehensive utility that combines a broad group of graphical tools with many rich script editors to provide access to [!INCLUDE ssnoversion-md] for developers and database administrators of all skill levels.

. image type=»content» source=»media/sql-server-management-studio-ssms/ssms.png» alt-text=»Screenshot of SQL Server Management Studio.».

SQL Server Management Studio components

Description	Component
Use Object Explorer to view and manage all of the objects in one or more instances of [!INCLUDE ssnoversion-md].	Object Explorer
Use Template Explorer to build and manage files of boilerplate text that you use to speed the development of queries and scripts.	Template Explorer
Use the deprecated Solution Explorer to build projects used to manage administration items such as scripts and queries.	Solution Explorer
Use the visual design tools included in [!INCLUDEssManStudio] to build queries, tables, and diagram databases.	Visual Database Tools
Use the [!INCLUDEssManStudio] language editors to interactively build and debug queries and scripts.	Query and Text Editors

SQL Server Management Studio for business intelligence

To access, configure, manage, and administer [!INCLUDEssASnoversion], Integration Services, and Reporting Services, use SSMS. Although all three business intelligence technologies rely on SSMS, the administrative tasks associated with each of these technologies are slightly different.

[!NOTE]
To create and modify Analysis Services, Reporting Services, and Integration Services solutions, use [!INCLUDEssBIDevStudioFull], not SSMS. [!INCLUDEssBIDevStudioFull] is a development environment that is based on Microsoft[!INCLUDEvsprvs].

Manage Analysis Services solutions using SQL Server Management Studio

SSMS enables you to manage [!INCLUDEssASnoversion] objects, such as performing back-ups and processing objects.

[!INCLUDEssManStudio] provides an [!INCLUDEssASnoversion] Script project in which you develop and save scripts written in Multidimensional Expressions (MDX), Data Mining Extensions (DMX), and XML for Analysis (XMLA). You use [!INCLUDEssASnoversion] Scripts projects to perform management tasks or re-create objects, such as database and cubes, on [!INCLUDEssASnoversion] instances. For example, you can develop an XMLA script in an [!INCLUDEssASnoversion] Script project that creates new objects directly on an existing [!INCLUDEssASnoversion] instance. The [!INCLUDEssASnoversion] Scripts projects can be saved as part of a solution and integrated with source code control.

Manage Integration Services solutions using SQL Server Management Studio

SSMS enables you to use the Integration Services service to manage packages and monitor running packages. You can also use [!INCLUDEssManStudio] to organize packages into folders, run packages, import and export packages, migrate Data Transformation Services (DTS) packages, and upgrade Integration Services packages.

Manage Reporting Services projects using SQL Server Management Studio

Use SSMS to enable Reporting Services features, administer the server and databases, and manage roles and jobs.

You manage shared schedules by using the Shared Schedules folder, and manage report server databases ( ReportServer , ReportServerTempDB ). You also create a RSExecRole in the master system database when you move a report server database to a new or different [!INCLUDE ssde-md]. For more information about these tasks, see the following articles:

You also manage the server by enabling and configuring various features, setting server defaults, and managing roles and jobs. For more information about these tasks, see the following articles:

Non-English language versions of SQL Server Management Studio

The block on mixed languages setup has been lifted. You can install SSMS German on a French Windows. If the OS language doesn’t match the SSMS language, the user needs to change the language under Tools > Options > International Settings. Otherwise, SSMS shows the English UI.

For more information about different locale with previous versions, reference Install non-English language versions of SSMS.

Support policy for SSMS

• Starting with SSMS 17.0, the SQL Tools team has adopted the Microsoft Modern Lifecycle Policy.
• Read the original Modern Lifecycle Policy announcement. For more information, see Modern Policy FAQs.
• For information on diagnostic data collection and feature usage, see the SQL Server privacy supplement.

For more information about SSMS, common tasks, and related tools, see the following articles:

SQL Server Management Studio (SSMS)

SQL Server Management Studio is a free multipurpose integrated tool to access, develop, administer, and manage SQL Server databases, Azure SQL Databases, and Azure Synapse Analytics. SSMS allows you to manage SQL Server using a graphical interface.

SSMS can also be used to access, configure, manage & administer Analysis services, Reporting services, & Integration services.

SSMS runs only on Windows. Use Azure Data Studio to run on your macOS or Linux (and also Windows). Install SQL Server Management Studio if you have not installed it yet.

To launch SSMS from the Start menu, click Start and scroll down and expand Microsoft SQL Server Tools 18. Click on Microsoft SQL Server Management Studio.

SQL Server Management Studio(SSMS)

Clicking on the "Microsoft SQL Server Management Studio" will open the following window to connect with the server.

Connect to SQL Server Instance

Select Server Type, Server Name, and Authentication mode to connect with your server. SSMS can be used to connect with Database Engine, Analysis Services, Reporting Services, Integration Services, etc. Here, we will connect with our local SQL Server database, so select Database Engine as a server type.

Next, select the server name to which you want to connect. It can be a local or remote DB server. Select an appropriate server name or <Browse for more..> to browse server name.

Next, select the authentication mode. The latest version of SSMS offers five modes of authentication to choose from, as shown below. The most common is Windows Authentication and SQL Server Authentication.

SQL Server Authentications

After selecting the authentication type and entering the user name and password (SQL authentication) and click on the Connect button.

On successful login, the SSMS opens the following window.

SQL Server Management Studio

SSMS Components

SQL Server Management Studio has the following components:

• Object Explorer
• Security
• Server Objects
• Query and Text Editor
• Template Explorer
• Solution Explorer
• Visual Database Tools

Object Explorer

Object Explorer contains different components of one or more instances of SQL Server in a hierarchical manner. You can view and manage components such as Databases, Security, Server Objects, Replication, Polybase, Management, etc. Expand the component node to see further objects.

For example, expand the Databases folder to see all the databases available in the server instance. Any new database you create will be available here. There is a default System databases folder, which hosts four default databases: master, model, msdb and tempdb.

SQL Server Databases

Security

Managing security for your database server is extremely important. The Security node is below the Databases node in the Object Explorer. You can create Logins and assign Server roles for any database instance. In addition, you can assign role-based security to logins and users. The Server roles you create here have server-wide scope.

SQL Server Management Studio(SSMS)

Server Objects

The Server Objects node in SSMS has four sub-nodes: Backup devices, EndPoints, Linked Servers, and Triggers. A linked server is a method by which a SQL Server can talk to another ODBC database with a T-SQL statement. SQL Server EndPoints are a point of entry into SQL Server. It is a database object that defines a way in which the SQL Server can communicate over the network. All objects under Server Objects have server-wide scope.

SQL Server Management Studio(SSMS)

Replication

Replication is a set of technologies for copying and distributing data and database objects between databases and synchronizing databases. This is mainly used for maintaining consistency between databases.

Polybase

Polybase allows your SQL Server to query directly from other SQL Server, Oracle, MongoDB, Hadoop clusters, Teradata, Cosmos DB by installing client connection software using T-SQL separately. Polybase is used for data virtualization.

Query and Text Editor

Open a query editor by clicking on the New Query on the tool bar. Query editor lets you create, edit & execute Transact SQL (T-SQL) statements. It is equipped with IntelliSense support by auto-completing the script by suggesting variants. This makes writing & debugging code easier and faster.

Query Editor

Template Explorer

Template explorer provides templates for creating various database objects. You can browse the available templates in Template Explorer and open it into a code editor window. You can also create your own custom templates.

Open Template Explorer from View menu -> Template Explorer. The following displays Create Database template.

Template Explorer

Solution Explorer

Solution explorer is used to manage administration items such as scripts and queries. Open it from View -> Solution Explorer menu.

Template Explorer