Примеры операций на Transact-SQL

== Выбрать случайные несколько записей ==
SELECT TOP 10 * FROM MyTable
ORDER BY NewID()
ID: 02793
== Отсортировать результат случайным образом ==
SELECT * FROM MyTable
ORDER BY NewID()
ID: 02794
== Доступ к внешнему серверу по IP адресу ==
SELECT * FROM [123.123.123.123].MyExternalDatabase.dbo.MyTable
ID: 02795
== Посчитать события по месяцам ==
SELECT
 YEAR([MyDateFiled]),
 MONTH([MyDateFiled]),
 COUNT(*)
FROM [SomeTable]
GROUP BY
 YEAR([MyDateFiled]),
 MONTH([MyDateFiled])
ID: 02796
== Пронумеровать строки в результате запроса ==
Придётся сделать Inner Join на самого себя....
SELECT count(*) AS ID, c1.sys_id
FROM MyTable c1
INNER JOIN MyTable c2 ON c1.Sys_id >= c2.sys_id
GROUP BY c1.sys_id
ORDER BY id
ID: 02797
== Узнать автоинкрементное поле после вставки ==
INSERT INTO MyTable
 (Field1, Field2, Field3)
VALUES
 ('Value for field1', 'Value for field2', 0)
SELECT @@identity AS 'New number for inserted row'
Вообще-то правильнее использовать Identity_Scope(), но разница будет только если на таблице стоит триггер:
INSERT INTO MyTable
 (Field1, Field2, Field3)
VALUES
 ('Value for field1', 'Value for field2', 0)
SELECT identity_scope() AS 'New number for inserted row'
ID: 02798
== Вставить сразу несколько строк в таблицу одним запросом ==
INSERT INTO MyTable
 (Field1, Field2, Field3)
SELECT Value1, Value2, Value3
UNION ALL
SELECT Value4, Value5, Value6
...
ID: 02800
== Удалить данные из таблицы и обнулить автоинкремент ==
TRUNCATE TABLE MyTable
ID: 02801
== Удалить временную таблицу по имени ==
Причём не выдавать ошибку если такой таблицы нет.
Execute('if exists
 (select * from tempdb..sysobjects
 where id = OBJECT_ID(tempdb..'+@TableName+')) drop table '+@TableName)
ID: 02802
== Добавить расширенное свойство к таблице ==
EXEC sp_addextendedproperty 'PropertyName', 'PropertyValue', 'user', dbo, 'table', MyTable, 'column', MyField
ID: 02803
== Прочитать расширенное свойство таблицы ==
SELECT * FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'user','dbo','table', 'MyTable', 'column', 'MyIndex')
ID: 02804
== Получить список индексов таблицы ==
SELECT i.name AS index_name
FROM sysobjects o
INNER JOIN sysindexes i ON o.id = i.id
WHERE o.xtype = 'U' AND i.indid > 0 AND
 i.indid < 255 AND INDEXPROPERTY(i.id, i.name, 'isStatistics') = 0 AND
 o.name = @TableName
ORDER BY i.indid
ID: 02805
== Получить размеры таблиц ==
Если в таблицах есть ключи, то работает такой код:
SELECT SubString(o.name, 1, 30) AS Table_Name,
 i.[rows] AS Number_of_Rows
FROM sysobjects o
LEFT OUTER JOIN sysindexes i ON o.id = i.id
WHERE o.xtype = 'u' AND i.indid < 2
ORDER BY o.name
ID: 02806
== Проверить существует ли индекс ==
 if exists(
 SELECT *
 FROM sysobjects o
 INNER JOIN sysindexes i
 ON o.id = i.id
 WHERE o.xtype = 'U' AND
 i.indid > 0 AND
 i.indid < 255 AND
 INDEXPROPERTY(i.id, @IndexName, 'isStatistics') = 0 AND
 o.name = @TableName)
 Set @exists=1
 else
 Set @exists=0
ID: 02807
== Проверить есть ли значение в таблице ==
If Exists (Select * From MyTable Where Field1=1)
Begin
 Update MyTable
 Set Field2=666
 Where Field1=1
End
Else
Begin
 Insert into MyTable (Field1, Field2)
 Values (1, 666)
End
Соответственно отсутствие значения проверяется:
If not Exists (Select * From MyTable Where Field1=1)
Begin
 Insert into MyTable (Field1, Field2)
 Values (1, 666)
End
Else
Begin
 Update MyTable
 Set Field2=666
 Where Field1=1
End
ID: 02808
== Проверка на ошибки исполнения конструкции SQL ==
Сразу после выполнения инструкции можно проверить на наличие ошибок при её выполнении путём проверки глобальной переменной @@error:
INSERT INTO MyTable
VALUES (10)
if @@error <> 0
 Print 'Record was not inserted'
ID: 02809
== Пример использования курсоров ==
Вот пример использования курсора для прохода по каждой записи взапросе и операции с этой записью:
Declare @MyVar int
Declare c cursor for
Select MyField From MyTable Where MyField2=1
/*
 c - переменная курсора
 Select ... выражение к каждой записи которого посредством курсора можно достучаться
*/
Open c --открываем курсор, с этой строкой будет выполнен приведенный выше Select
Fetch Next From c Into @MyVar -- вытаскиваем первую запись и присваиваем значение MyField в переменную @MyVar
While @@Fetch_Status = 0 -- проверяем не последняя ли это запись, а точнее - смогли ли мы возвратить запись
 Begin
 Print @MyVar -- что-то делаем с полученными данными
 Fetch Next From c Into @MyVar -- вытаскиваем следующую запись и присваиваем значение MyField в переменную @MyVar
 End
Close c -- закрываем курсор, закрываем запрос, ОПЕРАТОР ОБЯЗАТЕЛЕН!
Deallocate c -- уничтожаем переменную, ОПЕРАТОР ОБЯЗАТЕЛЕН!
Если курсор не должен "отслеживать" изменения в таблице по ходу своей работы и не должен менять значение таблицы желательно добавление опций предотвращающих блокирование объектов на время работы курсора:
Declare c cursor
Local Fofward_Only Static Read_Only
for
Select MyField From MyTable Where MyField2=1
ID: 02810
== Выполнение SQL заданного строкой ==
Declare @sql varchar(8000)
Set @sql='SELECT * FROM MyTable'
Exec (@sql)
ID: 02811
== Преодоление барьера в 8000 символов в динамическом SQL ==
Declare @sql1 varchar(8000), @sql2 varchar(8000)
Set @sql1='Select * From MyTable'
Set @sql2='Where MyField=Something'
Exec (@sql1+@sql2)
ID: 02812
== Подождать 1 секунду средствами SQL ==
Waitfor Delay '000:00:01'
ID: 02816
== Целочисленное деление ==
Select Cast((@dividend-(@dividend % @divisor))/@divisor as bigint)
ID: 02817
== Остаток от деления ==
SELECT @dividend % @divisor
ID: 02818
== Проверка битовых значений ==
1. Проверить чтобы одно или более значений битовых полей было 1
SELECT * FROM MyTable WHERE (MyBitField1 | MyBitField2 | MyBitField3)=1
2. Проверить чтобы все значения битовых полей были равны 1
SELECT * FROM MyTable WHERE (MyBitField1 & MyBitField2 & MyBitField3)=1
ID: 02819
== Узнать текущую дату/время ==
GetDate()
Пример:
SELECT GetDate()
ID: 02820
== Сгенерировать новый GUID ==
NewID()
ID: 02821
== Вычленение подстроки из строки ==
SELECT Substring('Какая-то строка', 7, 3)
ID: 02823
== Замена подстроки на другую ==
SELECT Replace('Какая-то строка', 'Строка которую заменить', 'Строка на которую заменить')
ID: 02824
== Местоположение символа в строке ==
CHARINDEX('#', @TableName)
ID: 02825
== Выделить подстроку(@result) после подстроки(@substr) в строке(@str) ==
 declare @i int
 declare @result varchar(4000)
 Set @substr=Replace(@substr, '%', '[%]')
 Set @substr=Replace(@substr, '_', '[_]')
 Set @i=Patindex('%'+@substr+'%', @str)
 if @i>0
 Set @Result=right(@str,len(@str)-@i)
 else
 Set @Result=@str
 Return @Result
ID: 02826
== Выделить подстроку(@result) перед подстрокой(@substr) в строке(@str) ==
 declare @i int
 declare @result varchar(4000)
 Set @substr=Replace(@substr, '%', '[%]')
 Set @substr=Replace(@substr, '_', '[_]')
 Set @i=Patindex('%'+@substr+'%', @str)
 if @i>0
 Set @Result=left(@str,@i-1)
 else
 Set @Result=@str
 Return @Result
ID: 02827
== Узнать количество строк участвовавших в последнем SQL запросе ==
Хранится в переменной @@rowcount
ID: 02828
== Узнать имя текущего SQL сервера ==
Хранится в переменной @@servername
ID: 02829
== Пример вызова хранимой процедуры ==
Exec master..xp_cmdshell 'dir'
ID: 02830
== Пример вызова хранимой процедуры c передачей переменной ==
Declare @MyVariable varchar(50)
Set @MyVariable='dir'
Exec master..xp_cmdshell @MyVariable
ID: 02831
== Пример вызова хранимой процедуры с указанием переменных ==
Declare @MyVariable1 varchar(50), @MyVariable2 varchar(50)
Select @MyVariable1='dir', @MyVariable2='test'
Exec MyStroredProcedure
 @StroredProcedureVariable1=@MyVariable1,
 @StroredProcedureVariable2=@MyVariable2
ID: 02832
== Пример вызова хранимой процедуры с возвращаемой переменной ==
Declare @MyInVariable varchar(50) --переменная для ввода данных
Declare @MyOutVariable varchar(50) --переменная для вывода данных
Set @MyVariable='dir'
Exec MyStoredProcedure
 @InternalInVar=@MyInVariable,
 @InternalOutVar=@MyOutVariable output
Select @MyOutVariable
Обратите внимание что переменные для вывода данных всегда "присваиваются" внутренним переменным при вызове, хотя логика работы как раз обратная - при выполнении процедуры значению внешней переменной @MyOutVariable присваивается значение внутренней (внутрипроцедурной) переменной @InternalOutVar.
ID: 02833
== Пример вызова динамического SQL с возвращаемой переменной ==
Declare @sql nvarchar(4000)
Declare @ParmDefinition nvarchar(4000)
Set @ParmDefinition = N'@InParameter varchar(9), @Count int output'
Set @Sql=N'Select @count=count(*) From MyTable with (nolock)'
Set @Sql=@Sql+N'WHERE MyField = @InParameter'
Exec sp_executesql @sql, @ParmDefinition, @count=@result output, @InParameter=@MyInParam
Select @result
; Примечание: заменить nvarchar на varchar нельзя!
ID: 02834
== Сгенерировать собственную ошибку ==
Raiserror('This is error!', 18, 1)
ID: 02837
== Вернуть дату без временной части ==
cast(floor(cast(@Date as float)) as datetime)
ID: 02839
== Вернуть только время без части даты ==
cast(cast(@Date as float)-floor(cast(@Date as float)) as datetime)
ID: 02840
== Место занимаемое таблицей ==
Exec master..sp_spaceused MyTable
ID: 02843
== Узнать ширину столбца ==
Select COL_LENGTH(@TableName,@FieldName)
ID: 02844
== Послать Email ==
Exec master.dbo.xp_sendmail
 @recipients='nevzorov@yahoo.com',
 @Subject='MS SQL Test',
 @message='This is email body!'
ID: 02845
== Получить список полей таблицы ==
SELECT * FROM tempdb.information_schema.columns
ID: 02846
== Выполнить DOS команду на серверном компьютере ==
Exec master..xp_cmdshell 'Dir c:\*.*'
ID: 02847
== Освободить transaction log ==
BACKUP LOG MyDatabaseName WITH NO_LOG
ID: 02856
== Удалить прилинкованный сервер ==
EXEC sp_dropserver @LinkedServerName, 'droplogins'
ID: 02857
== Узнать путь к прилинкованной файловой базе данных ==
SELECT @FileName=Datasource FROM master..sysservers
WHERE srvname=@LinkedServerName
ID: 02858
== Прилинковать MS Access базу данных как удалённый сервер ==
Exec sp_addlinkedserver @LinkedServerName, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', @DatabaseName, NULL, NULL
Exec sp_addlinkedsrvlogin @LinkedServerName, 'false'
ID: 02859
== Прилинковать MS Excel книгу как удалённый сервер ==
Exec sp_addlinkedserver @LinkedServerName, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', @DatabaseName, NULL, 'Excel 8.0'
Exec sp_addlinkedsrvlogin @LinkedServerName, 'false'
ID: 02860
== Вывести список активных пользователей ==
Exec sp_Who
Exec sp_Who2
ID: 02862
== Прочитать файл ==
Оказывается стандартная процедура sp_readerrorlog может читать любой файл с сервера.
EXEC sp_readerrorlog 1, 'C:\ntldr'
ID: 02864
----
Author: Vit (www.delphist.com, www.drkb.ru, www.unihighlighter.com, www.nevzorov.org)
Category:Базы данных
Category:MS SQL Server
Category:T-SQL