Несколько полезных запросов которые должны быть под рукой у программиста SQL!
1.Склеить в одну строку данные в столбце
WITH T as
(SELECT 1 as ID , 'TEST1' as NAME
UNION ALL
SELECT 1 as ID , 'TEST2' as NAME
UNION ALL
SELECT 1 as ID , 'TEST3' as NAME
UNION ALL
SELECT 2 as ID , 'XXX' as NAME
UNION ALL
SELECT 2 as ID , 'YYY' as NAME
)
SELECT DISTINCT T1.ID,
(SELECT STUFF((SELECT ', ' + T2.NAME
FROM T T2
WHERE T1.ID = T2.ID
FOR XML PATH('')) ,1,1,'')) NAME
FROM T T1
С сортировкой
with T as
(
select 1 as ID_Сотрудник, N'Здание 1' as Строение, N'101' as Номер_комнаты
UNION ALL
select 1 as ID_Сотрудник, N'Здание 2' as Строение, N'102' as Номер_комнаты
UNION ALL
select 2 as ID_Сотрудник, N'Здание 2' as Строение, N'201' as Номер_комнаты
UNION ALL
select 2 as ID_Сотрудник, N'Здание 1' as Строение, N'202' as Номер_комнаты
),
TP as
(
select distinct ID_Сотрудник , Строение from T
)
SELECT DISTINCT T1.ID_Сотрудник,
SUBSTRING(
(
SELECT ',' + T2.Строение AS 'data()'
FROM TP T2
WHERE T1.ID_Сотрудник = T2.ID_Сотрудник
ORDER BY T2.Строение
FOR XML PATH('')
), 2 , 9999) Строение,
SUBSTRING(
(
SELECT ',' + T2.Номер_комнаты AS 'data()'
FROM T T2
WHERE T1.ID_Сотрудник = T2.ID_Сотрудник
ORDER BY T2.Строение
FOR XML PATH('')
), 2 , 9999) Номер_комнаты
FROM T T1
Начиная с SQL Server 2017 доступна функция STRING_AGG
SELECT STRING_AGG( ISNULL(Строение, ' '), ',') As СтроениеFrom Test
2.Динамический запрос с параметрами
DECLARE @ParmDefinition nvarchar(500);
DECLARE @name nvarchar(500);
DECLARE @max_VALUEOUT varchar(30);
SET @name = N'TEST';
SET @SQLString = N'SELECT @max_VALUEOUT = max(ID)
FROM (SELECT 1 as ID , ''TEST1'' as NAME UNION ALL SELECT 8 as ID , ''TEST2'' as NAME UNION ALL SELECT 5 as ID , ''TEST3'' as NAME UNION ALL SELECT 2 as ID , ''XXX'' as NAME) T
WHERE NAME like (''%''+@name +''%'')'
SET @ParmDefinition = N'@name varchar(100), @max_VALUEOUT varchar(30) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @name = @name, @max_VALUEOUT=@max_VALUEOUT OUTPUT;
SELECT @max_VALUEOUT as MAX_VALUE;
3. Курсор, построчная обработка
DECLARE @NAME VARCHAR(MAX)
DECLARE @ID INT
DECLARE @RESULT VARCHAR(MAX)
DECLARE db_cursor CURSOR FOR
SELECT NAME, ID
FROM
(
SELECT 1 as ID , 'TEST1' as NAME
UNION ALL
SELECT 1 as ID , 'TEST2' as NAME
UNION ALL
SELECT 1 as ID , 'TEST3' as NAME
UNION ALL
SELECT 2 as ID , 'XXX' as NAME
UNION ALL
SELECT 2 as ID , 'YYY' as NAME
) T
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name , @ID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RESULT = @name + '_' + CAST(@ID AS VARCHAR(MAX))+ '.sql'
SELECT @RESULT
FETCH NEXT FROM db_cursor INTO @name , @ID
END
CLOSE db_cursor
DEALLOCATE db_cursor
3. Аналитические функции
WITH T as
(
SELECT 'North America' as SalesGroup, 'United States' as Country, 22000 as AnnualSales UNION ALL
SELECT 'North America', 'Canada', 32000 UNION ALL
SELECT 'North America', 'Mexico', 28000 UNION ALL
SELECT 'Europe', 'France', 19000 UNION ALL
SELECT 'Europe', 'Germany', 22000 UNION ALL
SELECT 'Europe', 'Italy', 18000 UNION ALL
SELECT 'Europe', 'Greece', 16000 UNION ALL
SELECT 'Europe', 'Spain', 16000 UNION ALL
SELECT 'Europe', 'United Kingdom', 32000 UNION ALL
SELECT 'Pacific', 'Australia', 18000 UNION ALL
SELECT 'Pacific', 'China', 28000 UNION ALL
SELECT 'Pacific', 'Singapore', 21000 UNION ALL
SELECT 'Pacific', 'New Zealand', 18000 UNION ALL
SELECT 'Pacific', 'Thailand', 17000 UNION ALL
SELECT 'Pacific', 'Malaysia', 19000 UNION ALL
SELECT 'Pacific', 'Japan', 22000
)
SELECT
SalesGroup,
Country,
AnnualSales,
ROW_NUMBER() OVER(ORDER BY AnnualSales DESC) AS RowNumber,
RANK() OVER(ORDER BY AnnualSales DESC) AS BasicRank,
DENSE_RANK() OVER(ORDER BY AnnualSales DESC) AS DenseRank,
NTILE(3) OVER(ORDER BY AnnualSales DESC) AS NTileRank,
COUNT(AnnualSales) OVER(PARTITION BY SalesGroup) AS CountryCount,
SUM(AnnualSales) OVER(PARTITION BY SalesGroup) AS TotalSales,
AVG(AnnualSales) OVER(PARTITION BY SalesGroup) AS AverageSales,
COUNT(AnnualSales) OVER(PARTITION BY SalesGroup ORDER BY AnnualSales DESC ROWS 2 PRECEDING) AS CountryCount,
SUM(AnnualSales) OVER(PARTITION BY SalesGroup ORDER BY AnnualSales DESC ROWS 2 PRECEDING) AS TotalSales,
AVG(AnnualSales) OVER(PARTITION BY SalesGroup ORDER BY AnnualSales DESC ROWS 2 PRECEDING) AS AverageSales,
FIRST_VALUE(AnnualSales) OVER(PARTITION BY SalesGroup ORDER BY AnnualSales DESC) AS HighestSales,
LAST_VALUE(AnnualSales) OVER(PARTITION BY SalesGroup ORDER BY AnnualSales DESC) AS LowestSales,
LAG(AnnualSales, 1) OVER(PARTITION BY SalesGroup ORDER BY AnnualSales DESC) AS PreviousSale,
LEAD(AnnualSales, 1) OVER(PARTITION BY SalesGroup ORDER BY AnnualSales DESC) AS NextSale
FROM
T;
4.Разбить строку на несколько строк по разделителю
ALTER FUNCTION [dbo].[fnSplit](
@sInputList VARCHAR(MAX) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(MAX))
BEGIN
DECLARE @sItem VARCHAR(MAX)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
SELECT T.ITEM
FROM [dbo].[fnSplit] ('TEST1,TEST2,TEST3',',') T
Начиная с 2005 сервера, более удобный вариант через XML
declare @TextFilter varchar(100)
SET @TextFilter = 'AAA,BBB,CCC,XXX'
SELECT Split.a.value('.', 'VARCHAR(100)') AS String FROM (SELECT cast('<A>'+ replace(@TextFilter,',','</A><A>')+ '</A>' as xml) AS String ) AS A CROSS APPLY String.nodes ('/A') AS Split(a)
--разбивка строк для SQL 2016
select value
from string_split(replicate('test,',200),',')
Начиная с 2005 сервера, более удобный вариант через XML
declare @TextFilter varchar(100)
SET @TextFilter = 'AAA,BBB,CCC,XXX'
SELECT Split.a.value('.', 'VARCHAR(100)') AS String FROM (SELECT cast('<A>'+ replace(@TextFilter,',','</A><A>')+ '</A>' as xml) AS String ) AS A CROSS APPLY String.nodes ('/A') AS Split(a)
--разбивка строк для SQL 2016
select value
from string_split(replicate('test,',200),',')
WITH T AS
(
SELECT 1 as ID , 'TEST1' as NAME
UNION ALL
SELECT 1 as ID , 'TEST2' as NAME
UNION ALL
SELECT 1 as ID , 'TEST3' as NAME
UNION ALL
SELECT 2 as ID , 'XXX' as NAME
UNION ALL
SELECT 2 as ID , 'YYY' as NAME
)
SELECT ID , NAME
FROM T
FOR XML PATH('row') , ROOT('SampleXML')
DECLARE @mytable table (RawXml XML)
Insert into @mytable (RawXml)
select N'
<Rows>
<Row OrderNumber="2">
<Cell ColumnName="agrPerson" Value="77" />
<Cell ColumnName="norma" Value="" />
<Cell ColumnName="condition" Value="" />
<Cell ColumnName="denyDelete" Value="false" />
<Cell ColumnName="denyMove" Value="false" />
<Cell ColumnName="denyAdd" Value="" />
<Cell ColumnName="controlTerm" Value="true" />
</Row>
<Row OrderNumber="3">
<Cell ColumnName="agrPerson" Value="80" />
<Cell ColumnName="norma" Value="" />
<Cell ColumnName="condition" Value="" />
<Cell ColumnName="denyDelete" Value="false" />
<Cell ColumnName="denyMove" Value="false" />
<Cell ColumnName="denyAdd" Value="" />
<Cell ColumnName="controlTerm" Value="true" />
</Row>
</Rows>'
SELECT
x.XmlCol.value('@ColumnName', 'nvarchar(255)') AS ColumnName,
x.XmlCol.value('@Value', 'nvarchar(255)') AS Value ,
x.XmlCol.query('.') XmlText
x.XmlCol.query('.') XmlText
FROM @mytable b
cross apply b.RawXml.nodes(N'//Row/Cell') x(XmlCol)
cross apply b.RawXml.nodes(N'//Row/Cell') x(XmlCol)
declare @xml xml ='<doc type="3974" id="8604558" /><doc type="3974" id="8684450" /><doc type="3669" id="9684365" />'
select @xml.query('doc[@type=9200692 or @type=24774530 or @type=3974][1]').value('(/doc/@id)[1]', 'nvarchar(max)')
5.1.UNPIVOT используя CROSS APPLY
SELECT p.ProductID, p.ProductNumber, p.Color, x.[key], x.val
FROM Production.Product AS p
CROSS APPLY (
VALUES ('SafetyStockLevel', p.SafetyStockLevel),
('ReorderPoint', p.ReorderPoint),
('StandardCost', p.StandardCost),
('ListPrice', p.ListPrice),
('Weight', p.[Weight])
) AS x([key], val);
6.Проверить индексы на таблице
SELECT
TableName = t.name,
IndexName = ind.name,
IndexId = ind.index_id,
ColumnId = ic.index_column_id,
ColumnName = col.name,
ind.*,
ic.*,
col.*
FROM
sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
WHERE
ind.is_primary_key = 0
AND ind.is_unique = 0
AND ind.is_unique_constraint = 0
AND t.is_ms_shipped = 0
ORDER BY
t.name, ind.name, ind.index_id, ic.index_column_id
7. Когда необходим exclusive access на базу используем опцию
WITH ROLLBACK IMMEDIATE-- Вывести базу в оффлайн
ALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
-- Ввести базу онлайн
ALTER DATABASE [myDB] SET ONLINE
GO8. Найти все объекты содержащие текст.
select distinct [ObjectName] = asm.ObjectName
from
(
select [ObjectName] = object_name(object_id),
[Index] = PATINDEX(@SearchString01, asm.[definition]),
[Definition] = asm.[definition]
from sys.all_sql_modules as asm
where asm.[definition] like @SearchString01
) asm
where asm.ObjectName not like '%test%'
order by ObjectName;
9. Сохранить бинарный объект в файл (документ, картинка, аудио).
--exec sp_configure 'show_advanced_options', 1
--Reconfigure
--go
--exec sp_configure 'Ole Automation Procedures',1
--Reconfigure
--go
--Use the following TSQL to extract all the blobs or images.
DECLARE @SQLIMG VARCHAR(MAX),
@IMG_PATH VARBINARY(MAX),
@FID1 VARCHAR(100),
@TIMESTAMP VARCHAR(MAX),
@ObjectToken INT
DECLARE IMGPATH CURSOR FAST_FORWARD FOR
--The following line will select which record/image you want to extract.
--So change the following line to select your record.
select FID1, FPHOTO from FB_TEST...TAPCCHMAINPHOTO WHERE FID1 = 1893
OPEN IMGPATH
FETCH NEXT FROM IMGPATH INTO @FID1,@IMG_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
--Set the path where you want to save the file and also the extension of the file.
--The file name will be the timestamp at which it was extracted.
SET @TIMESTAMP = 'C:\photos\' + @FID1 + '.JPG'
PRINT @TIMESTAMP
PRINT @SQLIMG
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
FETCH NEXT FROM IMGPATH INTO @FID1,@IMG_PATH
END
CLOSE IMGPATH
DEALLOCATE IMGPATH
10. Перенаправить результат в таблицу .
DELETE player
WHERE datemodified < @startOfYear
OUTPUT id, name, surname datemodifier, COMPRESS(info)
INTO dbo.inactivePlayers ;
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
ModifiedDate = GETDATE()
OUTPUT inserted.BusinessEntityID,
deleted.VacationHours,
inserted.VacationHours,
inserted.ModifiedDate
INTO EmployeeHistory ;
11. Вывести SQL результат в HTML
SELECT (
SELECT 'Field1' AS th FOR XML PATH(''),
TYPE
),
(
SELECT 'Field2' AS th FOR XML PATH(''),
TYPE
),
(
SELECT 'Filed3' AS th FOR XML PATH(''),
TYPE
)
UNION ALL
SELECT (
SELECT p.field1 AS 'td' FOR XML PATH(''),
TYPE
),
(
SELECT p.field2 AS 'td' FOR XML PATH(''),
TYPE
),
(
SELECT p.field3 AS 'td' FOR XML PATH(''),
TYPE
)
FROM (
SELECT field1,
field2,
field3
FROM (
SELECT 'aaaa' field1,
'test1' field2,
10 field3
UNION ALL
SELECT 'cccc' field1,
'test2' field2,
10 field3
UNION ALL
SELECT 'dddd' field1,
'test3' field2,
10 field3
) T
)p
FOR XML PATH('tr')
12. Пакетный запрос на обновление\удаление данных
-- Note that we use the clustered index for performance
-- and to reduce locking
DECLARE @BatchSize INT = 1000;
DECLARE @UpToID INT = (SELECT MIN(SalesOrderID) FROM sales.SalesOrderHeader);
DECLARE @EndID INT = (SELECT MAX(SalesOrderID) FROM sales.SalesOrderHeader);
WHILE @UpToID < @EndID
BEGIN
UPDATE S
SET Comm = 1
FROM sales.SalesOrderHeader S
WHERE SalesOrderID >= @UpToID AND SalesOrderID < @UpToID + @BatchSize ;
SET @UpToID = @UpToID + @BatchSize;
END
13. Подготовить параметризованный запрос, выполнить и удалить план
Declare @P1 int;
Exec sp_prepare @P1 output,
N'@P1 nvarchar(128), @P2 nvarchar(100)',
N'SELECT database_id, name FROM sys.databases WHERE name=@P1 AND state_desc = @P2';
Exec sp_execute @P1, N'tempdb', N'ONLINE';
EXEC sp_unprepare @P1;
14. Время работы сервера
SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END
15. Информация о соединениях ,тип авторизации Kerberos или нет
SELECT sys.dm_exec_connections.session_id AS SPID,
sys.dm_exec_connections.connect_time AS Connect_Time,
DB_NAME(dbid) AS DatabaseName,
loginame AS LoginName,
sys.dm_exec_connections.auth_scheme as Auth_Scheme,
sys.dm_exec_connections.net_transport AS Net_Transport,
sys.dm_exec_connections.protocol_type as Protocol_Type,
sys.dm_exec_connections.client_net_address as Client_Net_Address,
sys.dm_exec_connections.local_net_address as Local_Net_Address,
sys.dm_exec_connections.local_tcp_port as Local_TCP_Port
FROM sys.sysprocesses
Right Outer JOIN sys.dm_exec_connections
ON sys.sysprocesses.spid=sys.dm_exec_connections.session_id
Order By Auth_Scheme, Net_Transport
16. Информация о связанных серверах
-- list of remote/linked servers
SELECT * FROM sys.servers
-- linked server logins
EXEC master.sys.sp_helplinkedsrvlogin
SELECT
serv.NAME,
serv.product,
serv.provider,
serv.data_source,
serv.catalog,
prin.name,
ls_logins.uses_self_credential,
ls_logins.remote_name
FROM
sys.servers AS serv
LEFT JOIN sys.linked_logins AS ls_logins
ON serv.server_id = ls_logins.server_id
LEFT JOIN sys.server_principals AS prin
ON ls_logins.local_principal_id = prin.principal_id
17. Создать локальный связанный сервер для тестирования удаленных запросов
--Авторизация Windows
EXEC sp_addlinkedserver
@server = 'self',
@srvproduct = '',
@provider = 'SQLNCLI',
@datasrc = NULL,
@location = NULL,
@provstr = 'DRIVER={SQL Server};Server=(local); Initial Catalog=master;Integrated Security=SSPI;'
--авторизация SQl Server
EXEC sp_addlinkedserver
@server = 'self',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = NULL,
@location = NULL,
@provstr = 'DRIVER={SQL Server};Server=(local); Initial Catalog=master;uid=sa;pwd=mypwd;'
18. Информация о свободном месте на диске
SELECT DISTINCT dovs.logical_volume_name AS LogicalName,
dovs.volume_mount_point AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC
OPTION (RECOMPILE)
--Объекты файловой группы
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id
AND i.data_space_id = 1 -- Filegroup
--размер на диске
SELECT database_id, db_name(database_id), f.file_id, volume_mount_point, total_bytes/1024/1024 total_bytes_mb, available_bytes /1024/1024 available_bytes_mb
FROM sys.database_files AS f
CROSS APPLY sys.dm_os_volume_stats(DB_ID(f.name), f.file_id);
--размеры файлов
SELECT NAME , df.physical_name , SIZE /128 SIZE_MB
FROM sys.database_files AS df
--количество строк по таблицам
SELECT tbl.name , 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 SCHEMA_NAME(tbl.schema_id)='dbo'
order by p.rows desc
OPTION (RECOMPILE);
--размер базы tempdb
SELECT
name AS FileName,
size*1.0/128 AS FileSizeinMB,
CASE max_size
WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file will grow to a maximum size of 2 TB.'
END,
growth AS 'GrowthValue',
'GrowthIncrement' =
CASE
WHEN growth = 0 THEN 'Size is fixed and will not grow.'
WHEN growth > 0 AND is_percent_growth = 0
THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'
END,*
FROM tempdb.sys.database_files;
21. Отсортировать значения в строке
declare @source varchar(max) = ',310,180,227,281,304,288,301,321,'
declare @delimiter varchar(20) = ','
SELECT @delimiter+STUFF((SELECT @delimiter + Txt
FROM
(
select x.value Txt , row_number () over (order by try_convert(int,x.value )) rnum
from string_split(@source,@delimiter) x
where x.value<>''
) t
order by rnum
FOR XML PATH('')) ,1,1,'')+@delimiter AS Txt
22. Запрос со смещением
-- переменная для указания смещения
Declare @RowSkip As int
-- переменная для указания кол-ва возвращаемых строк
Declare @RowFetch As int
-- кол-во строк для пропуска
Set @RowSkip = 50
-- кол-во строк для возврата
Set @RowFetch = 100;
with tblSample as
(
select row_number()over(order by column_id) num
from sys.columns t
)
-- вывод строк с 50 по 150
Select *
From tblSample
Order by num
Offset @RowSkip Row
Fetch Next @RowFetch Rows Only;
22. Обновление SCD 2
ALTER PROCEDURE [dbo].[P_Load_Currencies] as
BEGIN
INSERT INTO [dim].[Currencies]
([CurrencyName]
,[CurrencyIsoCode]
,[IsEffectiveRow]
,[RowEffectiveTimestamp]
,[RowExpirationTimestamp])
SELECT [CurrencyName] , CurrencyIsoCode , 'Y' , getdate() , '9999-12-31 23:59:59'
FROM
(
MERGE [dim].[Currencies] AS trg
USING (SELECT [Name] , CurrencyIsoCode FROM [DWH_Stage].[stg].[Currency]) AS src ([Name], CurrencyIsoCode)
ON (trg.CurrencyIsoCode = src.CurrencyIsoCode)
WHEN MATCHED and [IsEffectiveRow] = 'Y' AND trg.[CurrencyName] <> src.[Name] THEN
UPDATE SET trg.[IsEffectiveRow] = 'N',
trg.[RowExpirationTimestamp] = getdate()
WHEN NOT MATCHED THEN
INSERT ( [CurrencyName] , CurrencyIsoCode , [IsEffectiveRow], [RowEffectiveTimestamp], [RowExpirationTimestamp])
VALUES (src.[Name], src.CurrencyIsoCode, 'Y' , '1900-01-01 00:00:00' , '9999-12-31 23:59:59')
OUTPUT $action, src.[Name], src.CurrencyIsoCode
) as CT (Action
,[CurrencyName]
,[CurrencyIsoCode]) WHERE Action = 'UPDATE';
END
23.Вставка, удаление и обновление одной инструкцией MERGE
MERGE INTO dbo.energydata WITH (HOLDLOCK) AS target
USING dbo.temp_energydata AS source
ON target.webmeterID = source.webmeterID
AND target.DateTime = source.DateTime
WHEN MATCHED THEN
UPDATE SET target.kWh = source.kWh
WHEN NOT MATCHED BY TARGET THEN
INSERT (webmeterID, DateTime, kWh)
VALUES (source.webmeterID, source.DateTime, source.kWh)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
24. Обновить планы выполнения всех объектов в базе
DECLARE C CURSOR FOR (SELECT [name] FROM sys.objects WHERE [type] IN ('P', 'FN', 'IF'));
DECLARE @name SYSNAME;
OPEN C;
FETCH NEXT FROM C INTO @name;
WHILE @@FETCH_STATUS=0 BEGIN
EXEC sp_recompile @name;
FETCH NEXT FROM C INTO @name;
END;
CLOSE C;
DEALLOCATE C;select t.name , 'ALTER TABLE ['+ t.name + ' ' + '] ADD ' + + IIF (c.name is null, 'ДатаОбновления', NULL ) + ' DATETIME ;' + 'ALTER TABLE [dbo].['+t.name+'] ADD CONSTRAINT [DF_'+t.name+'_ДатаОбновления] DEFAULT (getdate()) FOR [ДатаОбновления]'
from sys.tables t
left join sys.all_columns c on t.object_id = c.object_id and c.name = 'ДатаОбновления'
26. Список столбцов в таблице
create table #ColumnList(TABLE_QUALIFIER sysname null,TABLE_OWNER sysname null,TABLE_NAME sysname null,COLUMN_NAME sysname null,DATA_TYPE sysname null,TYPE_NAME sysname null,PRECISION int null,LENGTH int null,SCALE int null,RADIX int null,NULLABLE bit null,REMARKS nvarchar(4000) Null,SQL_DATA_TYPE int null,SQL_DATETIME_SUB int null,CHAR_OCTET_LENGTH int null,ORDINAL_POSITION int null,IS_NULLABLE char(3) null,SS_DATA_TYPE int null)insert into #ColumnListexec sp_columns 'campaign'select *from #ColumnList
26. Список активных джобов
SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;
27. текст процедуры
SELECT DISTINCT
o.name AS Object_Name,
o.type_desc,
definition
FROM sys.sql_modules m
INNER JOIN
sys.objects o
ON m.object_id = o.object_id
WHERE o.name = 'sp_WhoIsActive'
P\S
Подробнее о подсказках в запросе (хинты)
