Богдан Стефанюк: дописи з тегом sql

Удаления дубликатов строк

Wed, 13 Nov 2019 17:29:28 -0400

Недавно мне попалась интересная задачка по SQL.
У вас есть таблица с 2 колонками: Id, Name. Но на колонках нет никаких ограничений. И получилось так что там если дубли и нам надо их удалить.

Пример таблички

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

; WITH DuplicatesForDelete(Row) AS (
SELECT ROW_NUMBER() OVER(PARTITION BY t1.Id, t1.Name ORDER BY Id, Name) AS Row 
FROM TableWithDuplicates t1
)
DELETE FROM DuplicatesForDelete WHERE Row > 1

Статья о том что такое оконные функции (OVER)

Полезные SQL скрипты: запросы, которые страдают от блокировок

Tue, 05 Nov 2019 17:04:49 -0400

SELECT TOP 10
    [Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count,
    [Total Time Blocked] = total_elapsed_time - total_worker_time,
    [Execution count] = qs.execution_count,
    [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
        (CASE
        WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
        ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2),
    [Parent Query] = qt.text,
    [DatabaseName] = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average Time Blocked] DESC;

Получить информацию по индексу в таблице

Fri, 25 Oct 2019 06:27:24 -0400

DECLARE @TableName varchar(255) = 'LeadInFolder'
DECLARE @TableIndexName varchar(255) = 'Icy1fjQeGXuR5svQcM20sAenE8U'

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@TableName), NULL, NULL, NULL) ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)
WHERE i.name = @TableIndexName

Полезные SQL скрипты: неиспользуемые индексы

Mon, 21 Oct 2019 04:57:03 -0400

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

SELECT DatabaseName = DB_NAME(),
    TableName = OBJECT_NAME(s.[object_id]),
    IndexName = i.name,
    user_updates,
    system_updates,
    'alter index [' +OBJECT_SCHEMA_NAME(i.object_id, DB_ID())+ '].['+i.name+'] ON ['+OBJECT_NAME(s.[object_id])+'] DISABLE' as [Disable],
    'exec sp_rename ''['+OBJECT_SCHEMA_NAME(i.object_id, DB_ID())+'].['+OBJECT_NAME(s.[object_id])+'].['+i.name+']'',''disable_'+i.name+''',''INDEX''' as [Rename]
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id and
                            s.index_id  = i.index_id
WHERE s.database_id = DB_ID() and
    OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 and
    s.user_seeks   = 0 and
    s.user_scans   = 0 and
    s.user_lookups = 0 and
    i.is_disabled  = 0 and
    i.is_unique = 0 and
    i.is_primary_key = 0 and
    i.type_desc <> 'HEAP'
order by user_updates + system_updates desc

Полезные SQL скрипты: поиск foreign key по БД

Sat, 28 Sep 2019 06:19:02 -0400

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'

Полезные SQL скрипты: текущая ситуация на сервере

Fri, 27 Sep 2019 06:21:00 -0400

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

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

Небольшая заметка об индексах в ms sql

Sat, 21 Sep 2019 06:20:03 -0400

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Изучение SQL: рекурсивные запросы

Mon, 16 Sep 2019 16:00:05 -0400

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

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

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

Tue, 20 Aug 2019 05:39:15 -0400

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

Изучение SQL: ключевое слово GO

Fri, 02 Aug 2019 04:34:05 -0400

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

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

CREATE DATABASE TestDB
GO

USE TestDB

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

Изучение SQL: EXCEPT и INTERSECT

Sun, 28 Apr 2019 11:28:28 -0400

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

Изучение SQL: UNION

Sat, 27 Apr 2019 14:19:22 -0400

Оператор 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.

Изучение SQL: Операторы GROUP BY и HAVING

Tue, 23 Apr 2019 10:40:43 -0400

Для группировки данных существуют 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

Изучение SQL: триггеры

Wed, 27 Mar 2019 14:01:33 -0400

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

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

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

AFTER — выполняется после выполнения действия. Определяется только для таблиц. Можно определять несколько действий через запятую.
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

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

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

Добавление — виртуальная табличка INSERTED.
Удаление — виртуальная табличка DELETED.
Изменение — содержит 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