SELECT 
    f.name AS ForeignKey, 
    OBJECT_NAME(f.parent_object_id) AS TableName, 
    COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, 
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, 
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName 
FROM 
    sys.foreign_keys AS f 
INNER JOIN 
    sys.foreign_key_columns AS fc 
ON 
    f.OBJECT_ID = fc.constraint_object_id 
WHERE 
    f.name = 'FKnokgcq1rxh5Y7DLhgCqIZJ4KY'

С помощью этих скрипов можно посмотреть какие запросы в данных момент выполняются на сервере.

select *
from sys.sysprocesses 
where 
	spid > 50 and 
	spid <> @@spid and 
	status <> 'sleeping'
order by spid, ecid

select 
	session_id, 
	status, 
	wait_type, 
	command, 
	last_wait_type, 
	percent_complete, 
	qt.text, 
	total_elapsed_time/1000 as [total_elapsed_time, сек],
	wait_time/1000 as [wait_time, сек],
	(total_elapsed_time - wait_time)/1000 as [work_time, сек]
from sys.dm_exec_requests as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
where session_id >= 50 and session_id <> @@spid

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

Что из себя представляет индекс?

Внутри сервера они представлены в виде B-tree, где B означает сбалансированное, а не бинарное дерево.

Например мы хотим найти запись с Id = 2581.
Начиная с корня, выполняется поиск наименьшего значения ключа, большего или равного требуемому. Так мы найдем узел 18316, потом спустимся в узел 9031 и там мы увидим что есть прямая ссылка на лежащие данные по ключу 2581, после чего осуществляем вычитку данных.

Кластерный индекс

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

Не кластерный индекс

Структура такая же как и кластерного индекса, но с двумя отличиями:

1. Не изменяет физическое упорядочивание данных.
2. Страницы индекса состоят из ключа индекса и ссылки на строку.

SQL Server использует индекс для нахождения записей, совпадающих с условиями запроса.

Составной ключ в индексе

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

Также стоит помнить что индексы типа (Col1, Col2) и (Col2, Col1) разные.

Уникальные индексы

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

Статьи, которые советую почитать чтобы глубже разобраться в теме

1. Очень хорошая статья о всех типах индексов, когда их стоит создавать и как использовать
2. Индексы. Теоретические основы.

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

Общий вид рекурсивного запроса

WITH <имя> (<список столбцов>)
AS (
	SELECT    -- анкорная часть
	UNION ALL -- рекурсивная часть
        SELECT FROM <имя>
        WHERE <условие продолжения интерации>
)

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

Пример 1
У нас есть табличка, в которой лежат сотрудники, у каждого сотрудника есть руководитель, который указывается в колонку ParentId. Нам надо найти менеджера, в непрямом подчинении которого есть указанный нами человек.

Наполнение таблицы.

Код запроса:

;WITH OrgStructure AS 
(
	SELECT Id, ParentId, EmployeeType, EmployeeName
	FROM Employees
	WHERE EmployeeName = 'Jim' -- отправная точка, нам надо найти менеджера сотрудника Jim

	UNION ALL

	SELECT e.Id, e.ParentId, e.EmployeeType, e.EmployeeName
	FROM Employees as e
	JOIN OrgStructure as os
	ON e.Id = os.ParentId
)

SELECT * FROM OrgStructure
WHERE EmployeeType = 'manager' -- указываем что мы ищем менеджера.

Пример 2
Найдем первые 10 чисел Фибоначчи:

;WITH FIBONACHI AS 
(
	SELECT
		1 Iteration,
		1 SecondValue,
		2 CurrentValue
	UNION ALL

	SELECT
		Iteration + 1,
		SecondValue = CurrentValue,
		CurrentValue = SecondValue + CurrentValue
	FROM FIBONACHI
	WHERE Iteration < 10
)
SELECT CurrentValue FROM FIBONACHI

А также отличная статья которая раскрывает тему объединения данных.

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

Например, создание БД и добавления в нее таблиц:

CREATE DATABASE TestDB
GO

USE TestDB

CREATE TABLE Users
(
	Id uniqueidintefier PRIMARY KEY IDENTITY,
	FirstName varchar(50) NOT NULL
)

EXCEPT

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

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

SELECT Name, SecondName
FROM Contact
EXCEPT
SELECT Name, SecondName
FROM Employee

INTERSECT

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

Выведем контакты, которые являются нашими сотрудниками.

SELECT Name, SecondName
FROM Contact
INTERSECT
SELECT Name, SecondName
FROM Employee

Оператор UNION позволяет соединить две таблицы. Но в отличие от inner/outer join объединения соединяют не столбцы разных таблиц, а два однотипных набора в один.

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

Пример:

SELECT Name, Age 
FROM Contact
UNION 
SELECT Name, Age 
FROM Employees

Также мы можем отсортировать выборку. Для этого нужно ориентироваться на имена колонок из первой выборки.

SELECT Name, Age as FirstAge
FROM Contact
UNION 
SELECT Name, Age as SecondAge 
FROM Employees
ORDER BY FirstAge

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

Для группировки данных существуют 2 ключевых слова: GROUP BY и HAVING.

GROUP BY

Оператор GROUP BY определяет, как строки будут группироваться.

Например сгруппировать контакты по стране:

SELECT country, Count(*) AS UserByCountryCount
FROM Contacts
GROUP BY country

HAVING

Используется для фильтрации групп.

Например, найдем страны в которых больше 100 пользователей:

SELECT country, COUNT(*) AS UserByCountryCount
FROM Contacts
GROUP BY country
HAVING COUNT(*) > 100

Сегодня на работе была задача по созданию триггеров на таблицу БД.
Суть заключалась в том, что надо сделать механизм, при котором update/insert в одну таблицу, переносил данные в другую таблицу, а предыдущую удалял.

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

Типы триггеров.

1. AFTER — выполняется после выполнения действия. Определяется только для таблиц. Можно определять несколько действий через запятую.
2. INSTEAD OF — выполняется вместо действия (то есть по сути действие — добавление, изменение или удаление — вообще не выполняется). Определяется для таблиц и представлений. Можно определять только одно действие.

Создание простого триггера

CREATE TRIGGER User_Insert_Update_TR
ON Users
AFTER INSERT, UPDATE -- сработает как для INSERT так и для UPDATE скриптов.
AS
BEGIN
    INSERT UsersLog (UserId)
    SELECT Id FROM inserted
END

Управление триггером

Удаление

DROP TRIGGER User_Insert_Update_TR

Отключение

DISABLE TRIGGER User_Insert_Update_TR ON Users

Включение

ENABLE TRIGGER User_Insert_Update_TR ON Users

Работа со значениями запроса

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

1. Добавление — виртуальная табличка INSERTED.
2. Удаление — виртуальная табличка DELETED.
3. Изменение — содержит 2 таблички.
   INSERTED хранит строки после обновления.
   DELETED до обновления.

INSTEAD OF

Данный тип триггеров работает вместо операций с данными. Также его можно применять не только для таблиц но и для представлений.

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

CREATE TRIGGER User_Delete_TR
ON Users
INSTED OF DELETE
AS
BEGIN
    UPDATE Users
    SET IsActive = 0
    WHERE Id = (SELECT Id FROM deleted)
END