Azure: построение кросс-запросов для различных БД в MS Azure SQL Server

 MS Azure SQL Server с точки зрения взаимодействия с ним крайне похож на классический MS SQL Server и во многих аспектах повторяет функциональность. Однако быстрое и удобное создание linked server пока не доступно в Azure версии. В качестве решение можно воспользоваться способом с подключением внешнего источника данных:

-- Производим очистку данных
DROP EXTERNAL TABLE tblSource
DROP EXTERNAL DATA SOURCE RemoteSource
DROP DATABASE SCOPED CREDENTIAL sourceCredential
DROP MASTER KEY

-- Настраиваем соединение
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your_password';

CREATE DATABASE SCOPED CREDENTIAL sourceCredential
WITH IDENTITY = 'your_login',
SECRET = 'your_password';

CREATE EXTERNAL DATA SOURCE RemoteSource
WITH
(
    TYPE=RDBMS,
    LOCATION='your_server.database.windows.net',
    DATABASE_NAME='your_db_name',
    CREDENTIAL= sourceCredential
);

CREATE EXTERNAL TABLE dbo.tblSource(
    [Id] [int] NOT NULL,
    [Name] [nvarchar](2000) NOT NULL
)
WITH
(
    DATA_SOURCE = RemoteSource
);

-- Производим выборку
SELECT * 
FROM dbo.tblSource

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

SQL: Функция для получения по подстрок из строки с разделителями

Возникла задача по разбиения строкового столбца, в котором есть разделители, например, точка с запятой на отдельные столбцы. Для этого из оригинальной строки нужно получать подстроки с учетом порядкового номера/индекса. Сделать это можно, написав sql-функцию следующего вида:

CREATE FUNCTION [dbo].[f_GetValueFromSeparatedStringByIndex]
(
    @InputString nvarchar(MAX),
    @Index int,
    @Separator nvarchar(1) = ';'
)
RETURNS nvarchar(255)
AS
BEGIN
    DECLARE @Result nvarchar(255) = NULL

    SELECT @Result = TRIM(Value)
    FROM (
        SELECT TRIM(value) as Value, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as OrderNumber
        FROM string_split(@InputString, @Separator)
    ) as t
    WHERE OrderNumber = @Index

    RETURN  @Result
END

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

Иногда запрос может выполняться очень долго, т.к. не может получить доступ к вычислительным ресурсам. Такое часто встречается при большом количестве одновременных обращений из разных источников. Получить список всех запросов, ожидающих доступ к ресурсам, в MS SQL Server можно следующим образом:

SELECT  wt.session_id, 
        ot.task_state, 
        wt.wait_type, 
        wt.wait_duration_ms, 
        wt.blocking_session_id, 
        wt.resource_description, 
        es.[host_name], 
        es.[program_name] 
FROM  sys.dm_os_waiting_tasks  wt  
INNER  JOIN sys.dm_os_tasks ot ON ot.task_address = wt.waiting_task_address 
INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id 
WHERE es.is_user_process =  1 

SQL: Остановка длительных запросов

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

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

   SELECT conn.session_id, host_name, program_name,
       nt_domain, login_name, connect_time, last_request_end_time 
   FROM sys.dm_exec_sessions AS sess
   JOIN sys.dm_exec_connections AS conn
      ON sess.session_id = conn.session_id
   

2. Получение списка всех запущенных запросов с индентификатором открытой сессии и временем запуска:
   

   SELECT  sqltext.TEXT,
           req.session_id,
           req.status,
           eq.start_time,
           req.command,
           req.cpu_time,
           req.total_elapsed_time
   FROM sys.dm_exec_requests req
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
   

3. Получить информацию о том, какие активные соединения в настоящее время установлены с SQL Server, можно так:
   

   sp_who2 'active'
   

4. Завершение пользовательского процесса по идентификатором сеанса:
   

   KILL 'your_session_id'
   

SQL: получение информации о всех таблицах

При проектировании больших баз данных я всегда стараюсь давать всем таблицам максимально понятные названия, а также, если это позволяет сделать БД, создавать описания к таблицам. Это очень удобно, когда таблиц становится очень много, а ты сам уже начинаешь забывать для чего та или иная таблица, а также при введении в курс дел нового разработчика. Однако прокликивать все таблиц, чтобы прочитать описание, очень не удобно. В рамках MS SQL Server может быть удобен следующий скрипт, который выводит список названий таблиц, их схему, дату создания и дату последнего изменения таблицы, а также количество записей и описание:

SELECT schema_name(tab.schema_id) as schema_name,
       tab.name as table_name, 
       tab.create_date as created,  
       tab.modify_date as last_modified, 
       p.rows as num_rows, 
       ep.value as comments 
FROM sys.tables tab
    INNER JOIN (SELECT DISTINCT 
                        p.object_id,
                        sum(p.rows) rows
                FROM sys.tables t
      INNER JOIN sys.partitions p on p.object_id = t.object_id 
                GROUP BY p.object_id, p.index_id) p
        on p.object_id = tab.object_id
    LEFT JOIN sys.extended_properties ep 
        on tab.object_id = ep.major_id
        and ep.name = 'MS_Description'
        and ep.minor_id = 0
        and ep.class_desc = 'OBJECT_OR_COLUMN'
ORDER BY schema_name, table_name

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

SELECT o.NAME, i.rowcnt 
FROM sysindexes AS i
 INNER JOIN sysobjects AS o ON i.id = o.id 
WHERE i.indid < 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
ORDER BY o.NAME

SQL: генерация списка дат и недель

Иногда для решения задачи бывает удобно получить список недель или последовательность дат для дальнейшего объединения с другими данными из БД. Получить список дат в указанном диапазоне можно таким образом (применительно к T-SQL):

DECLARE @StartDate date = '20190101'
DECLARE @EndDate date = '20191231'

;WITH calendarDates as
( 
   SELECT dt = @StartDate

   UNION ALL

   SELECT DATEADD(DAY, 1, dt)
   FROM calendarDates
   WHERE DATEADD(DAY, 1, dt) <= @EndDate
)

SELECT *
FROM calendarDates
ORDER BY dt
OPTION (MAXRECURSION 0)

Если нужно получить список всех дат начала недели, то пригодится такой трюк:

DECLARE @StartDate date = '20190101'
DECLARE @EndDate date = '20191231'
DECLARE @WeekStartDate date =  DATEADD(DAY, 2 - DATEPART(ISO_WEEK, @StartDate), CAST(@StartDate AS DATE))
DECLARE @WeekEndDate date = DATEADD(DAY, 8 - DATEPART(ISO_WEEK, GETDATE()), CAST(GETDATE() AS DATE))

;WITH calendarDates as
( 
   SELECT dt = @WeekStartDate

   UNION ALL

   SELECT DATEADD(DAY, 7, dt)
   FROM calendarDates
   WHERE DATEADD(DAY, 7, dt) <= @EndDate
)

SELECT *
FROM calendarDates
ORDER BY dt
OPTION (MAXRECURSION 0)

SQL: быстрый подсчет количества записей в таблицах

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

Способ №1:

SELECT SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('tblYourTableName')   
 AND (index_id=0 or index_id=1)

Способ №2:

SELECT CONVERT(bigint, rows)
FROM sysindexes
WHERE id = OBJECT_ID('tblYourTableName')
 AND indid < 2

Способ №3:

SELECT CAST(p.rows AS float)
FROM sys.tables AS tbl
 INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
 INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int) 
            AND p.index_id=idx.index_id
WHERE ((tbl.name=N'tblYourTableName'
 AND SCHEMA_NAME(tbl.schema_id)='dbo'))

SQL: Использование функции RAND внутри другой функции

Не так давно писал на T-SQL генератор данных, включающий в себя функцию со случайным значением шума. Наткнулся на то, что функция RAND() не может быть использована в рамках UDF (user defined function), выдавая при этом сообщение "Invalid use of a side-effecting operator ‘rand’ within a function". Удалось найти достаточно интересны способ обхода данного ограничения. 

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

CREATE VIEW v_RandomizeView
AS
SELECT RAND() AS Result

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

CREATE FUNCTION f_Randomize()
RETURNS float
AS
BEGIN
   RETURN 2 * (SELECT TOP 1 Result FROM v_RandomizeView) - 1
END
GO

Таблица соответствия типов данных для SQL Server и CLR

Ниже приведена таблица соответствия типов данных для SQL Server и CLR (оригинал доступен по ссылке)

SQL Server data type          CLR data type (SQL Server)    CLR data type (.NET Framework)  
varbinary                     SqlBytes, SqlBinary           Byte[]  
binary                        SqlBytes, SqlBinary           Byte[]  
varbinary(1), binary(1)       SqlBytes, SqlBinary           byte, Byte[] 
image                         None                          None

varchar                       None                          None
char                          None                          None
nvarchar(1), nchar(1)         SqlChars, SqlString           Char, String, Char[]     
nvarchar                      SqlChars, SqlString           String, Char[] 
nchar                         SqlChars, SqlString           String, Char[] 
text                          None                          None
ntext                         None                          None

uniqueidentifier              SqlGuid                       Guid 
rowversion                    None                          Byte[]  
bit                           SqlBoolean                    Boolean 
tinyint                       SqlByte                       Byte 
smallint                      SqlInt16                      Int16  
int                           SqlInt32                      Int32  
bigint                        SqlInt64                      Int64 

smallmoney                    SqlMoney                      Decimal  
money                         SqlMoney                      Decimal  
numeric                       SqlDecimal                    Decimal  
decimal                       SqlDecimal                    Decimal  
real                          SqlSingle                     Single  
float                         SqlDouble                     Double  

smalldatetime                 SqlDateTime                   DateTime  
datetime                      SqlDateTime                   DateTime 

sql_variant                   None                          Object  
User-defined type(UDT)        None                          user-defined type     
table                         None                          None 
cursor                        None                          None

MS SQL Server: How to decrease ldf size

Not so long a go I found that one of my MS SQL DB Servers ran out of disk space. The source of the problem was very huge ldf file size of one of the databases. I needed to free disk space so I wanted to make ldf smaller. Neither database nor files shrink did help. Nevertheless I found a simple way to do the job:

• Open 'Options' of the database with big ldf.
• Change recovery model to Simple. Full is set by default.
• Try to shrink your log files again.

After these steps you will have you ldf decreased. Now you can change the log file growth policy (I recomend it to grow by Mb not percentage) and change recovery model back to Full if needed.

SQL: Row value constructor

In MS SQL Server (starting from 2008) it is possible to use a row valued constructor as a derived table. The possible scenarios are:

select distinct * from (values (1), (1), (1), (2), (5), (1), (6)) as X(a)

select * 
from (values (1, 'User1'),
             (2, 'User2'),
             (3, 'User3'),
             (4, 'User4')) AS [Users] ( [Id], [Login] )

SQL: Удаление множественных пробелов внутри строки

Небольшой трюк для удаления множественных пробелов в строке в виде готовой функции для MS SQL Server:

CREATE FUNCTION TrimExtraSpaces 
(
 @Str nvarchar(MAX)
)
RETURNS nvarchar(MAX)
AS
BEGIN
RETURN (SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@str,' ','<>'),'><',''),'<>',' '))));
END
GO

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

SQL: Получение списка недель с номерами

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

DECLARE @YearNumber char(4) = 2015

DECLARE @Weeks TABLE 
(
   WeekNumber int,
   StartOfWeek datetime,
   EndOfWeek datetime
);

DECLARE @WeekNumber int = 1;
WHILE YEAR(DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNumber) + (@WeekNumber-1), 6)) <= @YearNumber
BEGIN
    INSERT INTO @Weeks
    VALUES  (
                @WeekNumber,
                DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNumber) + (@WeekNumber-1), 6),
                DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNumber) + (@WeekNumber-1), 5)
            )

    SET @WeekNumber = @WeekNumber +1;
END

SELECT *
FROM @Weeks

SQL: перекомпилляция всех хранимых процедур

Иногда после изменений структуры таблиц БД приходится вносить соответствующие изменения в хранимые процедуры, при этом всегда можно забыть что-либо забыть. Узнаете об этом вы только после того, как вызовете данную хранимую процедуру. Для проверки того, что все хранимые процедуры после изменений будут, как минимум, запускаться можно использовать следующий трюк - выполнение перекомпилляции всех имеющихся хранимых процедур. Для этого ниже приведен код хранимой процедуры под MS SQL Server, если какая-то процедур на сможет быть перекомпиллирована будет выдано сообщение об ошибке:

CREATE PROCEDURE p_RecompileAllStoredProcedures
(
    @Verbose bit = 0
)
AS
BEGIN    
DECLARE @SQL NVARCHAR(MAX)
DECLARE @ProcedureName NVARCHAR(255)           

DECLARE Procedures CURSOR FOR
    SELECT NAME, OBJECT_DEFINITION(o.object_id)
    FROM sys.objects AS o 
    WHERE o.[type] = 'P' AND o.name <> 'p_Kladr_Upgrade'
    ORDER BY o.name

OPEN Procedures
FETCH NEXT FROM Procedures
INTO @ProcedureName, @SQL
WHILE @@FETCH_STATUS = 0 
BEGIN       
    SET @SQL = 'ALTER ' + RIGHT(@SQL, LEN(@SQL) - (CHARINDEX('CREATE', @SQL) + 6))

    IF @Verbose <> 0 
 PRINT @ProcedureName

    EXEC(@SQL)

    FETCH NEXT FROM Procedures
    INTO @ProcedureName, @SQL
END
CLOSE Procedures
DEALLOCATE Procedures
END

SQL: Получение всех предков элемента в дереве, отсортированных по порядку

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

Id

PId

Name

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

;WITH up as
( 
 SELECT Id, PId, 1 as level  
 FROM  tblSomeTable h  
 WHERE Id = @Id  
 UNION ALL  
 SELECT h.Id, h.Pid, up.level + 1 as level  
 FROM tblSomeTable h  
  INNER JOIN up  
   ON h.Id = up.PId  
) 

SELECT Id  
FROM up  
ORDER BY level DESC

SQL: Транслиттеризация на уровне SQL

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

CREATE FUNCTION f_Transliterate (@str nvarchar(4000))
RETURNS nvarchar(4000) AS
BEGIN

DECLARE @str_lat varchar(8000)
DECLARE @rus varchar(100), @lat1 varchar(100), @lat2 varchar(100), @lat3 varchar(100)

SET @rus = 'абвгдеёжзийклмнопрстуфхцчшщъыьэюя'
SET @lat1 = 'abvgdejzzijklmnoprstufkccss"y''ejj'
SET @lat2 = '   oh j      h hhh  hua'
SET @lat3 = '             h   '
 
DECLARE @i int, @pos int, @ch varchar(2)
SET @i = 1
SET @str_lat = ''

WHILE @i <= len(@str)
BEGIN
 SET @ch = substring(@str, @i, 1)
    SET @pos = charindex(lower(@ch), @rus)

    IF @pos > 0
    begin
      IF ascii(upper(@ch)) = ascii(@ch)
        SET @str_lat = @str_lat + upper(substring(@lat1, @pos, 1)) + rtrim(substring(@lat2, @pos, 1)) + rtrim(substring(@lat3, @pos, 1))
      ELSE
        SET @str_lat = @str_lat + substring(@lat1, @pos, 1) + rtrim(substring(@lat2, @pos, 1)) + rtrim(substring(@lat3, @pos, 1))
    END
    ELSE
  SET @str_lat = @str_lat + @ch
  SET @i = @i + 1
 END

  RETURN @str_lat
END

SQL: Drop default constraint

Here is a useful stored procedure for deleting default contraint of some table column withou knowing its name (code is valid for MS SQL):

CREATE PROCEDURE p_DropDefaultContratintForColumn
(
    @TableName nvarchar(255),
    @ColumnName nvarchar(255)
)
AS
DECLARE @sql nvarchar(255)

IF EXISTS(   SELECT d.name 
             FROM sys.all_columns c
              JOIN sys.tables t ON t.object_id = c.object_id
              JOIN sys.schemas s ON s.schema_id = t.schema_id
              JOIN sys.default_constraints d ON c.default_object_id = d.object_id
             WHERE t.name = @TableName
                 AND c.name = @ColumnName
                 AND s.name = SCHEMA_NAME())
BEGIN
    select  @sql = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + d.name 
    FROM sys.all_columns c
             JOIN sys.tables t ON t.object_id = c.object_id
             JOIN sys.schemas s ON s.schema_id = t.schema_id
             JOIN sys.default_constraints d ON c.default_object_id = d.object_id
       WHERE t.name = @TableName
             AND c.name = @ColumnName
             AND s.name = SCHEMA_NAME()
    exec    sp_executesql @sql
END

SQL: Drop all stored procedures in database

Here is a useful SQL script to drop all stored procedures in the selected database. The code is valid for MS SQL.

DECLARE @ProcName varchar(500)

DECLARE cur CURSOR
FOR SELECT Name FROM sys.procedures
OPEN cur
     FETCH NEXT FROM cur INTO @ProcName
     WHILE @@fetch_status = 0
     BEGIN
          EXEC ('DROP PROC ' + @ProcName)   
          FETCH NEXT FROM cur INTO @ProcName
     END
CLOSE cur
DEALLOCATE cur

SQL: Check column existance

To check column existance in MS SQL you can do the following:

SELECT * 
FROM [INFORMATION_SCHEMA].[COLUMNS] 
WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName

SQL: Drop constraints

Sometimes before dropping the table one has to drop the constraints first. This useful stored procedure can drop the constraints for the selected table fro you:

CREATE PROCEDURE p_DropAllTableConstraints
(
    @TableName nvarchar(255)
)
AS
    DECLARE @sql nvarchar(255)
    WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = DB_NAME() and table_name = @TableName)
    BEGIN
        select  @sql = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
        from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
        where   constraint_catalog = DB_NAME() and
                table_name = @TableName
        exec    sp_executesql @sql
    END
GO