Примеры операций на Transact-SQL
Материал из DRKB
Выбрать случайные несколько записей[править | править код]
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)