MSSQL Оконные функции - Пример ROWNUMBER OVER PARTITION BY

Оконные функции — это такие функции которые на основе окна (набора записей) вычисляют скалярное (одиночное) значение. Это значение определяется с помощью инструкции OVER.

Пример:
select regioncode, aolevel, offname,
 rownumber() over(partition by regioncode order by aolevel, offname) as rownum
 from s_adr_fias
order by regioncode, aolevel, offname;


В данном примере будет сделана выборка в которой для каждого regioncode в поле rownum будет нумерация отсортированных значений aolevel, offname.

OFFSET FETCH пример применения в TSQL

Фильтр OFFSET FETCH в языке TSQL интересен тем что в отличии от фильтра TOP позволяет пропускать заданное количество строк ROWS.

Пример:
select regioncode, aolevel, offname from s_adr_fias
order by regioncode, aolevel, offname
offset 100 rows fetch next 50 rows only;


Данный пример демонстрирует выборку из набора отсортированного по условию ORDER BY (по полям regioncode, aolevel, offname) 50 строк данных пропустив первые 100 строк.

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

P.S. На больших объёмах, даже с учетом самой оптимальной индексации, данный фильтр является довольно ресурсоемким.

TSQL - Обновление одной таблицы с Join по второй (MSSQL - Update with join)

Пример обновления данных в одной таблице с одновременным сопоставлением и проверкой условия по второй таблице при помощи JOIN:
update cc
	set cc.ActivateState=2
 from ClientsCards cc
	left join ClientsCardsHeads cch 
 on cch.CardUid=cc.Uid
	where cc.ActivateState=0 and cch.State=3


ClientsCards cc — Первая таблица в которой производится изменение.

ClientsCardsHeads cch — Вторая таблица с данными которой производится сверка.

Пример MERGE в MSSQL T-SQL

Простой пример MERGE для TSQL.

В примере подразумевается, что мы оперируем двумя одинаковыми по структуре таблицами (в реальной жизни это условие вовсе не обязательно).
Таблицы TargetTableи SourceTable — состоят всего из двух полей:
Id — некий идентификатор.
Name — Название объекта.

В примере ниже мы проверяем для всех записей из SourceTable — существует ли в таблице TargetTable запись с аналогичным Id и если существует, то меняем для этой записи поле Name в TargetTable, а если не существует то добавляем запись в TargetTable из SourceTable целиком.


MERGE TargetTable AS target  -- таблица которую будем менять, таблица приемник

USING (SELECT Id, Name FROM SourceTable) AS source 

ON (target.Id = source.Id)  -- условие по которому сопоставляем источник и приемник

WHEN MATCHED AND target.Name!=source.Name -- Если такой уже есть, то проверяем не совпадает ли Name и если не совпадает то меняем

    THEN UPDATE SET target.Name = source.Name -- обновляем

WHEN NOT MATCHED  -- если такого Id нет в таблице target то добавляем

    THEN INSERT VALUES(source.Id, source.Name) -- добавление записи


Вот и всё.

Установка русской кодировки на уже созданную базу данных (смена COLLATION)

Полезный пример изменения кодировки (COLLATION) на уже созданной базе данных.
В данном примере устанавливается русская кодировка: Cyrillic_General_CI_AS

USE master;
GO

-- Переводим БД в single-user mode
ALTER DATABASE [имя_базы_данных]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- Меняем кодировку COLLATION
ALTER DATABASE [имя_базы_данных]
COLLATE Cyrillic_General_CI_AS ;
GO

-- Переводим БД обратно в multi-user mode
ALTER DATABASE [имя_базы_данных]
SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO  
 
--Проверяем настройки COLLATION.
SELECT name, collation_name
FROM sys.databases
WHERE name = 'имя_базы_данных';
GO


Читать дальше →

Получение полей таблицы в MSSQL - TSQL

Этот запрос возвращает набор полей таблицы со всеми характеристиками. Метод также применим и замечательно работает с представлениями VIEW.
SELECT 
   ORDINAL_POSITION
  ,COLUMN_NAME
  ,DATA_TYPE
  ,CHARACTER_MAXIMUM_LENGTH
  ,IS_NULLABLE
  ,COLUMN_DEFAULT
FROM   
  INFORMATION_SCHEMA.COLUMNS 
WHERE   
  TABLE_NAME = 'Название таблицы' 
ORDER BY 
  ORDINAL_POSITION ASC; 



Читать дальше →