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

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

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

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

2- Обзор Transact-SQL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Синтаксис:

IF THEN Job 1; END IF;

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

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

4.2- Цикл WHILE

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

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

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

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

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

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

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

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

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

Assign_Value_Example

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

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

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

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

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

Синтаксис:

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

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

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

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

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

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

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

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

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

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

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

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

7- Курсор (Cursor)

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

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

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

Синтаксис

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

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

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

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

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

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

The results run the example:

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

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

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

TryCatch_Example

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

Pезультат

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Drop Procedure: DROP PROCEDURE

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

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

Get_Employee_Infos

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

Mark a savepoint in transaction: save transaction name_of_savepoint

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

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

Transaction_Example1

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

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

12- Trigger

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

• TODO Link!

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

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

Язык программирования T-SQL

Transact-SQL (T-SQL ) – расширение языка SQL от компании Microsoft и используется в SQL Server для программирования баз данных.

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

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

Примечание! Код я буду писать в окне запроса среды SQL Server Management Studio, о том, как установить SQL Server и Management Studio в редакции Express мы с Вами разговаривали вот .

Переменные в T-SQL

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

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

Локальные переменные объявляются с помощью ключевого слова DECLARE и начинаются со знака @ . Как и во многих языках программирования, переменные в T-SQL должны иметь свой тип данных. Типов данных в SQL сервере достаточно много мы их подробно рассмотрели в справочнике, который я упоминал чуть выше.

Для присвоения значения переменной можно использовать команды SET или Select .

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

• @@ROWCOUNT – хранит количество записей, обработанных предыдущей командой;
• @@ERROR – возвращает код ошибки для последней команды;
• @@SERVERNAME — имя локального SQL сервера;
• @@VERSION — номер версии SQL Server;
• @@IDENTITY — последнее значение счетчика, используемое в операции вставки (insert ).

Теперь для примера давайте создадим две переменной с типом данных INT, присвоим им значения, первой с помощью команды SET, а второй с помощью команды Select, затем просто выведем на экран эти значения, а также выведем и значение переменной @@VERSION, т.е. узнаем версию SQL сервера.

DECLARE @TestVar1 INT DECLARE @TestVar2 INT SET @TestVar1 = 1 SELECT @TestVar2 = 2 SELECT @TestVar1 AS [Переменная 1], @TestVar2 AS [Переменная 2], @@VERSION AS [Версия SQL Server]

Пакеты

Пакет в T-SQL — это команды или инструкции SQL, которые объединены в одну группу и при этом SQL сервер будет компилировать, и выполнять их как одно целое.

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

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

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

Т.е. мы видим, что у нас вышла ошибка, связанная с тем, что переменная @TestVar1 у нас не объявлена.

Условные конструкции

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

IF…ELSE

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

DECLARE @TestVar1 INT DECLARE @TestVar2 VARCHAR(20) SET @TestVar1 = 5 IF @TestVar1 > 0 SET @TestVar2 = "Больше 0" ELSE SET @TestVar2 = "Меньше 0" SELECT @TestVar2 AS [Значение TestVar1]

IF EXISTS

Данная конструкция позволяет определить наличие записей определенных условием. Например, мы хотим знать есть ли в таблице те или иные записи и при обнаружении первого совпадения обработка команды прекращается. По сути это то же самое, что и COUNT(*) > 0.

К примеру, мы хотим проверить есть ли записи со значение id >=0 в таблице test_table, и на основе этого мы будем принимать решение, как действовать дальше

DECLARE @TestVar VARCHAR(20) IF EXISTS(SELECT * FROM test_table WHERE id > = 0) SET @TestVar = "Записи есть" ELSE SET @TestVar = "Записей нет" SELECT @TestVar AS [Наличие записей]

CASE

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

DECLARE @TestVar1 INT DECLARE @TestVar2 VARCHAR(20) SET @TestVar1 = 1 SELECT @TestVar2 = CASE @TestVar1 WHEN 1 THEN "Один" WHEN 2 THEN "Два" ELSE "Неизвестное" END SELECT @TestVar2 AS [Число]

BEGIN…END

Эта конструкция необходима для создания блока команд, т.е. например, если бы мы хотели выполнить не одну команду после блока IF, а несколько, то нам бы пришлось писать все команды внутри блока BEGIN…END.

Давайте модифицируем наш предыдущий пример (про IF EXISTS ) так, чтобы при наличии записей id > = 0 в таблице test_table, мы помимо присвоения значения переменной @TestVar, выполним еще и update, т.е. обновление неких данных в этой же таблице, а также выведем количество строк, которые мы обновили, используя глобальную переменную @@ROWCOUNT.

DECLARE @TestVar1 VARCHAR(20) DECLARE @TestVar2 INT SET @TestVar2 = 0 IF EXISTS(SELECT * FROM test_table WHERE id > = 0) BEGIN SET @TestVar1 = "Записи есть" UPDATE test_table SET column1 = 5 WHERE id > = 0 SET @TestVar2 = @@ROWCOUNT END ELSE SET @TestVar1 = "Записей нет" SELECT @TestVar1 AS [Наличие записей], @TestVar2 AS [Затронуто строк:]

Циклы T-SQL

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

DECLARE @Cnt INT = 1, @result INT = 0, @CountRow INT SELECT @CountRow = COUNT(*) FROM test_table WHILE @Cnt <= @CountRow BEGIN SET @Cnt += 1 SET @result += 1 IF @Cnt = 20 BREAK ELSE CONTINUE END SELECT @result AS [Количество выполнений цикла:]

В данном примере мы сначала, конечно же, объявляем переменные (Cnt и result мы сразу инициализируем, таким способом можно задавать значения переменных, начиная с SQL Server 2008 ). Затем узнаем, сколько строк в таблице test_table и после этого проверяем, если количество строк в таблице больше или равно нашему счетчику, то входим в наш тестовый цикл. В цикле мы увеличиваем значение счетчика, записываем результат и снова проверяем, если наш счетчик достиг уже значения 20, то мы его принудительно завершим, если нет, то пусть работает дальше, до того как значение счетчика станет больше или равно количеству строк в таблице или до 20, если в таблице строк больше.

Комментарии

Они нужны для того, чтобы пояснять, делать заметки в коде, так как если код большой и сложный, то через некоторое время Вы можете просто забыть, почему именно так и для чего Вы написали тот или иной участок кода. В языке T-SQL бывают однострочные (—Текст) и многострочные комментарии (/*Текст*/).

Команды T-SQL

GOTO

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

DECLARE @Cnt INT = 0 Metka: --Устанавливаем метку SET @Cnt += 1 --Прибавляем к переменной 1 if @Cnt < 10 GOTO Metka --Если значение меньше 10, то переходим к метке SELECT @Cnt AS [Значение Cnt =]

WAITFOR

Команда может приостановить выполнение кода на время или до наступления заданного времени. Параметр DELAY делает паузу заданной длины, а TIME приостанавливает процесс до указанного времени. Значение параметров задается в формате hh:mi:ss

DECLARE @TimeStart time, @TimeEnd time SET @TimeStart = CONVERT (time, GETDATE())--Узнаем время WAITFOR DELAY "00:00:05"--Пауза на 5 секунд SET @TimeEnd = CONVERT (time, GETDATE())--Снова узнаем время --Узнаем, сколько прошло времени в секундах SELECT DATEDIFF(ss, @TimeStart, @TimeEnd) AS [Прошло Секунд:]

RETURN

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

DECLARE @Cnt INT = 1, @result varchar(15) /*Если значение Cnt меньше 0, то следующие команды не выполнятся, и Вы не увидите колонку [Результат:]*/ IF @Cnt < 0 RETURN SET @result = "Cnt больше 0" SELECT @result AS [Результат:]

PRINT

Для передачи служебного сообщения можно использовать команду PRINT . В Management Studio это сообщение отобразится на вкладке «Сообщения» (Messages ).

DECLARE @Cnt INT = 10, @TestVar varchar(100) IF @Cnt > 0 SET @TestVar = "Значение переменной Cnt больше 0 и равняется " + CAST(@Cnt AS VARCHAR(10)) ElSE SET @TestVar = "Значение переменной Cnt меньше 0 и равняется " + CAST(@Cnt AS VARCHAR(10)) PRINT @TestVar

Транзакции

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

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

Узнаем что у нас в таблице (id = IDENTITY) SELECT * FROM test_table --Начинаем транзакцию BEGIN TRAN --Сначала обновим все данные UPDATE test_table SET column1 = column1 - 5 --Затем просто добавим строки с новыми значениями INSERT INTO test_table SELECT column1 FROM test_table --Если ошибка, то все отменяем IF @@error != 0 BEGIN ROLLBACK TRAN RETURN END COMMIT TRAN --Смотрим что получилось SELECT * FROM test_table

В этом примере, если бы у нас в момент добавления данных (INSERT) возникла ошибка, то UPDATE бы отменился.

Обработка ошибок — конструкция TRY…CATCH

В процессе выполнения T-SQL кода может возникнуть непредвиденная ситуация, т.е. ошибка, которую необходимо обработать. В SQL сервере, начиная с SQL Server 2005, существует такая конструкция как TRY…CATCH , которая может отследить ошибку.

BEGIN TRY DECLARE @TestVar1 INT = 10, @TestVar2 INT = 0, @result INT SET @result = @TestVar1 / @TestVar2 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS [Номер ошибки], ERROR_MESSAGE() AS [Описание ошибки] END CATCH

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

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

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