Примеры операций на 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)