Команды t sql. Семь смертных грехов программиста на T-SQL. Что такое курсор

В этом материале мы с Вами рассмотрим основы программирования на языке T-SQL , узнаем, что это за язык, какими основными возможностями он обладает, какие конструкции включает и, конечно же, в процессе всего этого я буду приводить примеры кода.

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

Язык программирования T-SQL

Transact-SQL (T-SQL ) – расширение языка SQL от компании Microsoft и используется в SQL Server для программирования баз данных.

SQL Server включает много конструкций, компонентов, функций которые расширяют возможности языка SQL стандарта ANSI, в том числе и классическое программирование, которое отличается от обычного написания запросов.

И сегодня мы с Вами рассмотрим ту часть основ языка T-SQL, которая подразумевает написание кода для реализации некого функционала (например, в процедуре или функции ), а не просто какого-то запроса к базе данных.

Примечание! Код я буду писать в окне запроса среды SQL Server Management Studio, о том, как установить SQL Server и Management Studio в редакции Express мы с Вами разговаривали вот .

Переменные в T-SQL

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

Существует две разновидности переменных в T-SQL — это локальные и глобальные. Локальные переменные существуют только в пределах сеанса, во время которого они были созданы, а глобальные используются для получения информации о SQL сервере или какой-то меняющейся информации в базе данных.

Локальные переменные объявляются с помощью ключевого слова DECLARE и начинаются со знака @ . Как и во многих языках программирования, переменные в T-SQL должны иметь свой тип данных. Типов данных в SQL сервере достаточно много мы их подробно рассмотрели в справочнике, который я упоминал чуть выше.

Для присвоения значения переменной можно использовать команды SET или Select .

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

• @@ROWCOUNT – хранит количество записей, обработанных предыдущей командой;
• @@ERROR – возвращает код ошибки для последней команды;
• @@SERVERNAME — имя локального SQL сервера;
• @@VERSION — номер версии SQL Server;
• @@IDENTITY — последнее значение счетчика, используемое в операции вставки (insert ).

Теперь для примера давайте создадим две переменной с типом данных INT, присвоим им значения, первой с помощью команды SET, а второй с помощью команды Select, затем просто выведем на экран эти значения, а также выведем и значение переменной @@VERSION, т.е. узнаем версию SQL сервера.

DECLARE @TestVar1 INT DECLARE @TestVar2 INT SET @TestVar1 = 1 SELECT @TestVar2 = 2 SELECT @TestVar1 AS [Переменная 1], @TestVar2 AS [Переменная 2], @@VERSION AS [Версия SQL Server]

Пакеты

Пакет в T-SQL — это команды или инструкции SQL, которые объединены в одну группу и при этом SQL сервер будет компилировать, и выполнять их как одно целое.

Для того чтобы дать понять SQL серверу, что Вы передаете пакет команд необходимо указывать ключевое слово GO после всех команд, которые Вы хотите объединить в пакет.

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

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

Т.е. мы видим, что у нас вышла ошибка, связанная с тем, что переменная @TestVar1 у нас не объявлена.

Условные конструкции

Эти конструкции подразумевают ветвление, т.е. в зависимости от выполнения или невыполнения определенных условий инструкции T-SQL будут менять свое направление.

IF…ELSE

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

DECLARE @TestVar1 INT DECLARE @TestVar2 VARCHAR(20) SET @TestVar1 = 5 IF @TestVar1 > 0 SET @TestVar2 = "Больше 0" ELSE SET @TestVar2 = "Меньше 0" SELECT @TestVar2 AS [Значение TestVar1]

IF EXISTS

Данная конструкция позволяет определить наличие записей определенных условием. Например, мы хотим знать есть ли в таблице те или иные записи и при обнаружении первого совпадения обработка команды прекращается. По сути это то же самое, что и COUNT(*) > 0.

К примеру, мы хотим проверить есть ли записи со значение id >=0 в таблице test_table, и на основе этого мы будем принимать решение, как действовать дальше

CASE

Данная конструкция используется совместно с оператором select и предназначена она для замены многократного использования конструкции IF. Она полезна в тех случаях, когда необходимо проверять переменную (или поле ) на наличие определенных значений.

DECLARE @TestVar1 INT DECLARE @TestVar2 VARCHAR(20) SET @TestVar1 = 1 SELECT @TestVar2 = CASE @TestVar1 WHEN 1 THEN "Один" WHEN 2 THEN "Два" ELSE "Неизвестное" END SELECT @TestVar2 AS [Число]

BEGIN…END

Эта конструкция необходима для создания блока команд, т.е. например, если бы мы хотели выполнить не одну команду после блока IF, а несколько, то нам бы пришлось писать все команды внутри блока BEGIN…END.

Давайте модифицируем наш предыдущий пример (про IF EXISTS ) так, чтобы при наличии записей id > = 0 в таблице test_table, мы помимо присвоения значения переменной @TestVar, выполним еще и update, т.е. обновление неких данных в этой же таблице, а также выведем количество строк, которые мы обновили, используя глобальную переменную @@ROWCOUNT.

DECLARE @TestVar1 VARCHAR(20) DECLARE @TestVar2 INT SET @TestVar2 = 0 IF EXISTS(SELECT * FROM test_table WHERE id > = 0) BEGIN SET @TestVar1 = "Записи есть" UPDATE test_table SET column1 = 5 WHERE id > = 0 SET @TestVar2 = @@ROWCOUNT END ELSE SET @TestVar1 = "Записей нет" SELECT @TestVar1 AS [Наличие записей], @TestVar2 AS [Затронуто строк:]

Циклы T-SQL

Если говорить в общем о циклах, то они нужны для многократного повторения выполнения команд. В языке T-SQL есть один цикл WHILE с предусловием , это означает, что команды начнутся, и будут повторяться до тех пор, пока выполняется условие перед началом цикла, также выполнение цикла можно контролировать с помощью ключевых слов BREAK и CONTINUE .

DECLARE @Cnt INT = 1, @result INT = 0, @CountRow INT SELECT @CountRow = COUNT(*) FROM test_table WHILE @Cnt <= @CountRow BEGIN SET @Cnt += 1 SET @result += 1 IF @Cnt = 20 BREAK ELSE CONTINUE END SELECT @result AS [Количество выполнений цикла:]

В данном примере мы сначала, конечно же, объявляем переменные (Cnt и result мы сразу инициализируем, таким способом можно задавать значения переменных, начиная с SQL Server 2008 ). Затем узнаем, сколько строк в таблице test_table и после этого проверяем, если количество строк в таблице больше или равно нашему счетчику, то входим в наш тестовый цикл. В цикле мы увеличиваем значение счетчика, записываем результат и снова проверяем, если наш счетчик достиг уже значения 20, то мы его принудительно завершим, если нет, то пусть работает дальше, до того как значение счетчика станет больше или равно количеству строк в таблице или до 20, если в таблице строк больше.

Комментарии

Они нужны для того, чтобы пояснять, делать заметки в коде, так как если код большой и сложный, то через некоторое время Вы можете просто забыть, почему именно так и для чего Вы написали тот или иной участок кода. В языке T-SQL бывают однострочные (—Текст) и многострочные комментарии (/*Текст*/).

Команды T-SQL

GOTO

С помощью этой команды можно перемещаться по коду к указанной метке. Например, ее можно использовать тогда когда Вы хотите сделать своего рода цикл, но без while.

DECLARE @Cnt INT = 0 Metka: --Устанавливаем метку SET @Cnt += 1 --Прибавляем к переменной 1 if @Cnt < 10 GOTO Metka --Если значение меньше 10, то переходим к метке SELECT @Cnt AS [Значение Cnt =]

WAITFOR

Команда может приостановить выполнение кода на время или до наступления заданного времени. Параметр DELAY делает паузу заданной длины, а TIME приостанавливает процесс до указанного времени. Значение параметров задается в формате hh:mi:ss

DECLARE @TimeStart time, @TimeEnd time SET @TimeStart = CONVERT (time, GETDATE())--Узнаем время WAITFOR DELAY "00:00:05"--Пауза на 5 секунд SET @TimeEnd = CONVERT (time, GETDATE())--Снова узнаем время --Узнаем, сколько прошло времени в секундах SELECT DATEDIFF(ss, @TimeStart, @TimeEnd) AS [Прошло Секунд:]

RETURN

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

DECLARE @Cnt INT = 1, @result varchar(15) /*Если значение Cnt меньше 0, то следующие команды не выполнятся, и Вы не увидите колонку [Результат:]*/ IF @Cnt < 0 RETURN SET @result = "Cnt больше 0" SELECT @result AS [Результат:]

PRINT

Для передачи служебного сообщения можно использовать команду PRINT . В Management Studio это сообщение отобразится на вкладке «Сообщения» (Messages ).

DECLARE @Cnt INT = 10, @TestVar varchar(100) IF @Cnt > 0 SET @TestVar = "Значение переменной Cnt больше 0 и равняется " + CAST(@Cnt AS VARCHAR(10)) ElSE SET @TestVar = "Значение переменной Cnt меньше 0 и равняется " + CAST(@Cnt AS VARCHAR(10)) PRINT @TestVar

Транзакции

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

Этот механизм необходим для того, чтобы обеспечить целостность данных, т.е. допустим, у Вас есть процедура, которая перечисляет деньги с одного счета на другой, но может возникнуть ситуация при которой деньги снялись со счета, но не поступили на другой счет. К примеру, SQL инструкция, которая осуществляет снятие денег, отработала, а при выполнении инструкции, которая зачисляет деньги, возникла ошибка, другими словами, деньги снялись и просто потерялись. Чтобы этого не допускать, все SQL инструкции пишут внутри транзакции и тогда если наступит такая ситуация все изменения будут отменены, т.е. деньги вернутся на счет обратно.

Узнаем что у нас в таблице (id = IDENTITY) SELECT * FROM test_table --Начинаем транзакцию BEGIN TRAN --Сначала обновим все данные UPDATE test_table SET column1 = column1 - 5 --Затем просто добавим строки с новыми значениями INSERT INTO test_table SELECT column1 FROM test_table --Если ошибка, то все отменяем IF @@error != 0 BEGIN ROLLBACK TRAN RETURN END COMMIT TRAN --Смотрим что получилось SELECT * FROM test_table

В этом примере, если бы у нас в момент добавления данных (INSERT) возникла ошибка, то UPDATE бы отменился.

Обработка ошибок — конструкция TRY…CATCH

В процессе выполнения T-SQL кода может возникнуть непредвиденная ситуация, т.е. ошибка, которую необходимо обработать. В SQL сервере, начиная с SQL Server 2005, существует такая конструкция как TRY…CATCH , которая может отследить ошибку.

BEGIN TRY DECLARE @TestVar1 INT = 10, @TestVar2 INT = 0, @result INT SET @result = @TestVar1 / @TestVar2 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS [Номер ошибки], ERROR_MESSAGE() AS [Описание ошибки] END CATCH

В этом примере возникла ситуация что происходит деление на ноль (как Вы знаете делить на 0 нельзя ) и так как наш блок кода был помещен в конструкцию TRY у нас возникло исключение, при котором мы просто получаем номер ошибки и ее описание.

Я думаю для основ этого достаточно, если Вы хотите более подробно изучить все конструкции языка T-SQL, то рекомендую прочитать мою книгу «Путь программиста T-SQL », в которой уже более подробно рассмотрен язык T-SQL, у меня все, удачи!

• Перевод

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

Существует три базовых правила для написания такого T-SQL кода, который будет работать хорошо. Они кумулятивные – выполнение всех этих правил окажет положительное влияние на код. Пропуск или изменение любого из них – скорее всего приведет к отрицательному влиянию на производительность вашего кода.

• Пишите, исходя из структуры хранения данных: если вы храните данные типа datetime, используйте именно datetime, а не varchar или что-нибудь еще.
• Пишите, исходя из наличия индексов: если на таблице построены индексы, и они должны там быть, пишите код так, чтобы он мог использовать все преимущества, предоставляемые этими индексами. Убедитесь, что кластерный индекс, а для каждой таблицы он может быть только один, используется наиболее эффективным образом.
• Пишите так, чтобы помочь оптимизатору запросов: оптимизатор запросов – восхитительная часть СУБД. К сожалению, вы можете сильно затруднить ему работу, написав запрос, который ему «тяжело» будет разбирать, например, содержащий вложенные представления – когда одно представление получает данные из другого, а то из третьего – и так далее. Потратьте свое время для того, чтобы понять как работает оптимизатор и писать запросы таким образом, чтобы он мог вам помочь, а не навредить.

Использование неправильных типов данных

Не верите? Давайте посмотрим на этот запрос:

SELECT e.BusinessEntityID, e.NationalIDNumber FROM HumanResources.Employee AS e WHERE e.NationalIDNumber = 112457891;
Хорошо написан и очень прост. Он должен покрываться индексом, созданным на этой таблице. Но вот план выполнения:

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

Правильно. Это предупреждение (новое в SQL Server 2012) о том, что выполняется преобразование типов, влияющее на план выполнения. Вкратце – это потому, что в запросе используется неверный тип данных:

SELECT e.BusinessEntityID, e.NationalIDNumber FROM HumanResources.Employee AS e WHERE e.NationalIDNumber = "112457891";
И мы получаем вот такой план выполнения запроса:

И здесь используются только две операции чтения, вместо четырех. И да, я понимаю, что сделал и так быстро выполняющийся запрос чуть-чуть более быстрым. Но что было бы, если бы в таблице хранились миллионы строк? Ага, тогда-то я стал бы героем.

Используйте правильные типы данных.

Использование функций при составлении условий соединения и в выражениях WHERE

SELECT a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID FROM Person.Address AS a WHERE "4444" = LEFT(a.AddressLine1, 4) ;
Эта функция, LEFT, получает в качестве аргумента столбец, что выливается в этот план выполнения:

В результате, осуществляется 316 операций чтения, чтобы найти нужные данные, и это занимает 9 миллисекунд (у меня очень быстрые диски). Все потому что ‘4444’ должно сравниться с каждой строкой, возвращенной этой функцией. SQL Server не может даже просто просканировать таблицу, ему необходимо выполнить LEFT для каждой строки. Однако, вы можете сделать нечто вроде этого:

SELECT a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID FROM Person.Address AS a WHERE a.AddressLine1 LIKE "4444%" ;
И вот мы видим совершенно другой план выполнения:

Для выполнения запроса требуется 3 операции чтения и 0 миллисекунд. Ну или пусть будет 1 миллисекунда, для объективности. Это огромный прирост производительности. А все потому что я использовал такую функцию, которая может быть использована для поиска по индексу(ранее это называлось sargeable – непереводимое, в общем-то, слово: SARG – Search Arguments –able, если функция SARGeable – в нее можно передавать столбец в качестве аргумента и все равно будет использоваться Index Seek, если не SARGeable – увы, всегда будет использоваться Index Scan - прим. переводчика ). В любом случае, не используйте функции в выражениях WHERE или условиях поиска, либо используйте только те, которые могут быть использованы в условиях поиска по индексу.

Использование Multi-statement UDF

По сути, они загоняют вас в ловушку. На первый взгляд, этот чудесный механизм позволяет нам использовать T-SQL как настоящий язык программирования. Вы можете создавать эти функции и вызывать их одну из другой и код можно будет использовать повторно, не то что эти старые хранимые процедуры. Это восхитительно. До тех пор пока вы не попробуете запустить этот код на большом объеме данных.

Проблема с этими функциями заключается в том, что они строятся на табличных переменных. Табличные переменные – это очень крутая штука, если вы используете их по назначению. У них есть одно явное отличие от временных таблиц – по ним не строится статистика. Это отличие может быть очень полезным, а может … убить вас. Если у вас нет статистики, оптимизатор предполагает, что любой запрос, выполняющийся к табличной переменной или UDF, возвратит всего одну строку. Одну (1) строку. Это хорошо, если они действительно возвращают несколько строк. Но, однажды они возвратят сотни или тысячи строк и вы решите соединить одну UDF с другой… Производительность упадет очень-очень быстро и очень-очень сильно.

Пример достаточно велик. Вот несколько UDF:

CREATE FUNCTION dbo.SalesInfo () RETURNS @return_variable TABLE (SalesOrderID INT, OrderDate DATETIME, SalesPersonID INT, PurchaseOrderNumber dbo.OrderNumber, AccountNumber dbo.AccountNumber, ShippingCity NVARCHAR(30)) AS BEGIN; INSERT INTO @return_variable (SalesOrderID, OrderDate, SalesPersonID, PurchaseOrderNumber, AccountNumber, ShippingCity) SELECT soh.SalesOrderID, soh.OrderDate, soh.SalesPersonID, soh.PurchaseOrderNumber, soh.AccountNumber, a.City FROM Sales.SalesOrderHeader AS soh JOIN Person.Address AS a ON soh.ShipToAddressID = a.AddressID ; RETURN ; END ; GO CREATE FUNCTION dbo.SalesDetails () RETURNS @return_variable TABLE (SalesOrderID INT, SalesOrderDetailID INT, OrderQty SMALLINT, UnitPrice MONEY) AS BEGIN; INSERT INTO @return_variable (SalesOrderID, SalesOrderDetailId, OrderQty, UnitPrice) SELECT sod.SalesOrderID, sod.SalesOrderDetailID, sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderDetail AS sod ; RETURN ; END ; GO CREATE FUNCTION dbo.CombinedSalesInfo () RETURNS @return_variable TABLE (SalesPersonID INT, ShippingCity NVARCHAR(30), OrderDate DATETIME, PurchaseOrderNumber dbo.OrderNumber, AccountNumber dbo.AccountNumber, OrderQty SMALLINT, UnitPrice MONEY) AS BEGIN; INSERT INTO @return_variable (SalesPersonId, ShippingCity, OrderDate, PurchaseOrderNumber, AccountNumber, OrderQty, UnitPrice) SELECT si.SalesPersonID, si.ShippingCity, si.OrderDate, si.PurchaseOrderNumber, si.AccountNumber, sd.OrderQty, sd.UnitPrice FROM dbo.SalesInfo() AS si JOIN dbo.SalesDetails() AS sd ON si.SalesOrderID = sd.SalesOrderID ; RETURN ; END ; GO
Отличная структура. Она позволяет составлять очень простые запросы. Ну, например, вот:

SELECT csi.OrderDate, csi.PurchaseOrderNumber, csi.AccountNumber, csi.OrderQty, csi.UnitPrice FROM dbo.CombinedSalesInfo() AS csi WHERE csi.SalesPersonID = 277 AND csi.ShippingCity = "Odessa" ;
Один, очень простой запрос. Вот его план выполнения, так же очень простой:

Вот только выполняется он 2,17 секунды, возвращает 148 строк и использует 1456 операций чтения. Обратите внимание, что наша функция имеет нулевую стоимость и только сканирование таблицы, табличной переменной, влияет на стоимость запроса. Хм, правда что ли? Попробуем посмотреть что скрывается за оператором выполнения UDF с нулевой стоимостью. Этот запрос достанет план выполнения функции из кэша:

SELECT deqp.query_plan, dest.text, SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1, (deqs.statement_end_offset - deqs.statement_start_offset) / 2 + 1) AS actualstatement FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE deqp.objectid = OBJECT_ID("dbo.CombinedSalesInfo");
И вот что там происходит на самом деле:

Ого, похоже здесь скрывается еще несколько этих маленьких функций и сканов таблиц, которые почти, но все-таки не совсем, ничего не стоят. Плюс оператор соединения Hash Match, который пишет в tempdb и имеет немалую стоимость при выполнении. Давайте посмотрим план выполнения еще одной из UDF:

Вот! А теперь мы видим Clustered Index Scan, при котором сканируется большое число строк. Это уже не здорово. Вообще, во всей этой ситуации, UDF кажутся все менее и менее привлекательными. Что если мы, ну, я прямо не знаю, просто попробуем напрямую обратиться к таблицам. Вот так, например:

SELECT soh.OrderDate, soh.PurchaseOrderNumber, soh.AccountNumber, sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID JOIN Person.Address AS ba ON soh.BillToAddressID = ba.AddressID JOIN Person.Address AS sa ON soh.ShipToAddressID = sa.AddressID WHERE soh.SalesPersonID = 277 AND sa.City = "Odessa" ;
Теперь, выполнив этот запрос, мы получим абсолютно те же самые данные, но всего за 310 миллисекунд, а не за 2170. Плюс, SQL Server выполнит всего 911 операций чтения, а не 1456. Честно говоря, очень просто получить проблемы с производительностью, используя UDF

Включение настройки «Работай быстрее!»: использование «Грязных чтений»

Когда вы используете READ_UNCOMMITTED или NO_LOCK в своих запросах, вы сталкиваетесь с грязными чтениями. Все понимают, что это означает, что вы можете прочитать «собака» а не «кошка», если в этот момент выполняется, но еще не завершилась операция обновления. Но, кроме этого, вы можете получить большее или меньшее количество строк, чем есть на самом деле, а так же дубликаты строк, поскольку страницы данных могут перемещаться во время выполнения вашего запроса, а вы не накладываете никаких блокировок, чтобы избежать этого. Не знаю как у вас, но в большинстве компаний в которых я работал, ожидали, что большая часть запросов на большинстве систем будут возвращать целостные данные. Один и тот же запрос с одними и теми же параметрами, выполняемый к одному и тому же множеству данных, должен давать один и тот же результат. Только не в том случае, если вы используете NO_LOCK. Для того, чтобы убедиться в этом я советую вам прочесть этот пост .

Необоснованное использование хинтов в запросах

Например, множество людей считает, что LOOP JOIN – это лучший способ соединения таблиц. Они приходят к такому выводу, поскольку он наиболее часто встречается в небольших и быстрых запросах. Поэтому они решают принудительно заставить SQL Server использовать именно LOOP JOIN. Это совсем не сложно:

SELECT s. AS StoreName, p.LastName + ", " + p.FirstName FROM Sales.Store AS s JOIN sales.SalesPerson AS sp ON s.SalesPersonID = sp.BusinessEntityID JOIN HumanResources.Employee AS e ON sp.BusinessEntityID = e.BusinessEntityID JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID OPTION (LOOP JOIN);
Этот запрос выполняется 101 миллисекунду и совершает 4115 операций чтений. В общем-то неплохо, но если мы уберем этот хинт, тот же самый запрос выполнится за 90 миллисекунд и произведет всего 2370 чтений. Чем более загружена будет система, тем более очевидной будет эффективность запроса без использования хинта.

А вот еще один пример. Люди часто создают индекс на таблице, ожидая, что он решит проблему. Итак, у нас есть запрос:

SELECT * FROM Purchasing.PurchaseOrderHeader AS poh WHERE poh.PurchaseOrderID * 2 = 3400;
Проблема опять-таки в том, что когда вы выполняете преобразование столбца, ни один индекс не будет адекватно использоваться. Производительность падает, поскольку выполняется сканирование кластерного индекса. И вот, когда люди видят, что их индекс не используется, они делают вот что:

SELECT * FROM Purchasing.PurchaseOrderHeader AS poh WITH (INDEX (PK_PurchaseOrderHeader_PurchaseOrderID)) WHERE poh.PurchaseOrderID * 2 = 3400;
И теперь они получают сканирование выбранного ими, а не кластерного, индекса, так что индекс «используется», правда ведь? Но вот производительность запроса изменяется – теперь вместо 11 операций чтения выполняется 44 (время выполнения у обоих около 0 миллисекунд, поскольку у меня реально быстрые диски). «Использоваться»-то он используется, но совсем не так как предполагалось. Решение этой проблемы заключается в том, чтобы переписать запрос таким образом:

SELECT * FROM Purchasing.PurchaseOrderHeader poh WHERE PurchaseOrderID = 3400 / 2;
Теперь количество операций чтения упало до двух, поскольку используется поиск по индексу – индекс используется правильно.

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

Использование построчной обработки результата выполнения запроса (‘Row by Agonizing Row’ processing)

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

BEGIN TRANSACTION DECLARE @Name NVARCHAR(50) , @Color NVARCHAR(15) , @Weight DECIMAL(8, 2) DECLARE BigUpdate CURSOR FOR SELECT p. ,p.Color ,p. FROM Production.Product AS p ; OPEN BigUpdate ; FETCH NEXT FROM BigUpdate INTO @Name, @Color, @Weight ; WHILE @@FETCH_STATUS = 0 BEGIN IF @Weight < 3 BEGIN UPDATE Production.Product SET Color = "Blue" WHERE CURRENT OF BigUpdate END FETCH NEXT FROM BigUpdate INTO @Name, @Color, @Weight ; END CLOSE BigUpdate ; DEALLOCATE BigUpdate ; SELECT * FROM Production.Product AS p WHERE Color = "Blue" ; ROLLBACK TRANSACTION
В каждой итерации мы совершаем две операции чтения, а количество продукции, отвечающей нашим критериям, исчисляется сотнями. На моей машине, без нагрузки, время выполнения составляет больше секунды. Это совершенно неприемлемо, тем более что переписать этот запрос очень просто:

BEGIN TRANSACTION UPDATE Production.Product SET Color = "BLUE" WHERE < 3 ; ROLLBACK TRANSACTION
Теперь выполняется всего 15 операций чтения и время выполнения составляет всего 1 миллисекунду. Не смейтесь. Люди часто пишут такой код и даже хуже. Курсоры – это такая штука, которую следует избегать и использовать только там, где без них нельзя обойтись – например в задачах обслуживания, где вам надо «пробегать» по разным таблицам или базам данных.

Необоснованное использование вложенных представлений

Вот, например, последовательность простых запросов, определяющих представления:

CREATE VIEW dbo.SalesInfoView AS SELECT soh.SalesOrderID, soh.OrderDate, soh.SalesPersonID, soh.PurchaseOrderNumber, soh.AccountNumber, a.City AS ShippingCity FROM Sales.SalesOrderHeader AS soh JOIN Person.Address AS a ON soh.ShipToAddressID = a.AddressID ; CREATE VIEW dbo.SalesDetailsView AS SELECT sod.SalesOrderID, sod.SalesOrderDetailID, sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderDetail AS sod ; CREATE VIEW dbo.CombinedSalesInfoView AS SELECT si.SalesPersonID, si.ShippingCity, si.OrderDate, si.PurchaseOrderNumber, si.AccountNumber, sd.OrderQty, sd.UnitPrice FROM dbo.SalesInfoView AS si JOIN dbo.SalesDetailsView AS sd ON si.SalesOrderID = sd.SalesOrderID ;
А вот здесь автор текста забыл указать запрос, но он приводит его в комментариях (прим. переводчика):
SELECT csi.OrderDate FROM dbo. CominedSalesInfoView csi WHERE csi.SalesPersonID = 277
В итоге наш запрос выполняется 155 миллисекунд и использует 965 операций чтения. Вот его план выполнения:

Выглядит неплохо, тем более, что мы получаем 7000 строк, так что вроде бы все в порядке. Но что, если мы попробуем выполнить вот такой запрос:

SELECT soh.OrderDate FROM Sales.SalesOrderHeader AS soh WHERE soh.SalesPersonID = 277 ;
А теперь запрос выполняется за 3 миллисекунды и использует 685 операций чтения – довольно-таки сильно отличается. И вот его план выполнения:

Как вы можете убедиться, оптимизатор не в силах выкинуть все лишние таблицы в рамках процесса упрощения запроса. Поэтому, в первом плане выполнения есть две лишние операции – Index Scan и Hash Match, собирающий данные воедино. Вы могли бы избавить SQL Server от лишней работы, написав этот запрос без использования представлений. И помните – этот пример очень прост, большинство запросов в реальной жизни намного сложнее и приводят к гораздо большим проблемам производительности.

В комментариях к этой статье есть небольшой спор, суть которого в том, что Грант (автор статьи), похоже выполнял свои запросы не на стандартной базе AdventureWorks, а на похожей БД, но с несколько иной структурой, из-за чего план выполнения „неоптимального“ запроса, приведенного в последнем разделе, отличается от того, что можно увидеть, проводя эксперимент самостоятельно. Прим. переводчика.
Если где-то я был излишне косноязычен (а я это могу) и текст труден для понимания, или вы можете мне предложить лучшую формулировку чего бы то ни было - с радостью выслушаю все замечения.

SQL - это аббревиатура выражения Structured Query Language (язык структурированных запросов). SQL основывается на реляционной алгебре и специально разработан для взаимодействия с реляционными базами данных.

SQL является, прежде всего, информационно-логическим языком, предназначенным для описания хранимых данных, их извлечения и модификации. SQL не является языком программирования. Вместе с тем конкретные реализации языка, как правило, включают различные процедурные расширения.

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

• DDL (Data Definition Language) - операторы определения данных
• DML (Data Manipulation Language) - операторы манипуляции данными
• DCL (Data Control Language) - операторы определения доступа к данным
• TCL (Transaction Control Language) - операторы управления транзакциями

SQL является стандартизированным языком. Стандартный SQL поддерживается комитетом стандартов ANSI (Американский национальный институт стандартов), и соответственно называется ANSI SQL.

Многие разработчики СУБД расширили возможности SQL, введя в язык дополнительные операторы или инструкции. Эти расширения необходимы для выполнения дополнительных функций или для упрощения выполнения определенных операций. И хотя часто они очень полезны, эти расширения привязаны к определенной СУБД и редко поддерживаются более чем одним разработчиком. Все крупные СУБД и даже те, у которых есть собственные расширения, поддерживают ANSI SQL (в большей или меньшей степени). Отдельные же реализации носят собственные имена (PL-SQL, Transact-SQL и т.д.). Transact-SQL (T-SQL) – реализация языка SQL корпорации Microsoft, используемая, в частности, и в SQL Server.

Запросы на выборку данных (оператор SELECT)

SELECT – наиболее часто используемый SQL оператор. Он предназначен для выборки информации из таблиц. Чтобы при помощи оператора SELECT извлечь данные из таблицы, нужно указать как минимум две вещи - что вы хотите выбрать и откуда.

Выборка отдельных столбцов

SELECT

FROM Product

В приведенном выше операторе используется оператор SELECT для выборки одного столбца под названием Description из таблицы Product. Искомое имя столбца указывается сразу после ключевого слова SELECT, а ключевое слово FROM указывает на имя таблицы, из которой выбираются данные.

Для создания и тестирования данного запроса в Management Studio выполните следующие шаги:

Выборка нескольких столбцов

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

SELECT , InStock

FROM Product

Выборка всех столбцов

Помимо возможности осуществлять выборку определенных столбцов (одного или нескольких), при помощи оператора SELECT можно запросить все столбцы, не перечисляя каждый из них. Для этого вместо имен столбцов вставляется групповой символ “звездочка” (*). Это делается следующим образом.

SELECT *

FROM Product

Сортировка данных

В результате выполнения запроса на выборку данные выводятся в том порядке, в котором они находятся в таблице. Для точной сортировки выбранных при помощи оператора SELECT данных используется предложение ORDER BY. В этом предложении указывается имя одного или нескольких столбцов, по которым необходимо отсортировать результаты. Взгляните на следующий пример.

FROM Product

ORDER BY InStock

Это выражение идентично предыдущему, за исключением предложения ORDER BY, которое указывает СУБД отсортировать данные по возрастанию значений столбца InStock.

Сортировка по нескольким столбцам

Чтобы осуществить сортировку по нескольким столбцам, просто укажите их имена через запятую. В следующем коде выбираются три столбца, а результат сортируется по двум из них - сначала по количеству, а потом по названию.

SELECT IdProd, , InStock

FROM Product

ORDER BY InStock,

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

Указание направления сортировки

В предложении ORDER BY можно также использовать порядок сортировки по убыванию. Для этого необходимо указать ключевое слово DESC. В следующем примере продукция сортируется по количеству в убывающем порядке плюс по названию продукта.

SELECT IdProd, , InStock

FROM Product

ORDER BY InStock DESC ,

Ключевое слово DESC применяется только к тому столбцу, после которого оно указано. В предыдущем примере ключевое слово DESC было указано для столбца InStock, но не для Description. Таким образом, столбец InStock отсортирован в порядке убывания, а столбец Description в возрастающем порядке (принятым по умолчанию).

SQL (Structured Query Language) - это универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных (язык структурированных запросов).

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

В настоящее время широко распространенны следующие спецификации SQL:

В данной статье будет рассмотрена спецификация Transact-SQL, которая используется серверами Microsoft SQL. А так как база у всех спецификаций SQL одинаковая, то большинство команд и сценариев с легкостью переносятся на другие типы SQL.

Определение

Transact-SQL - это процедурное расширение языка SQL компаний Microsoft. SQL был расширен такими дополнительными возможностями как:

• управляющие операторы,
• локальные и глобальные переменные,
• различные дополнительные функции для обработки строк, дат, математики и т.п.,
• поддержка аутентификации Microsoft Windows

Язык Transact-SQL является ключом к использованию SQL Server. Все приложения, взаимодействующие с экземпляром SQL Server, независимо от их реализации и пользовательского интерфейса, отправляют серверу инструкции Transact-SQL.

Опытная база данных

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

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

Создание базы данных USE master CREATE DATABASE TestDatabase GO -- Создание таблиц USE TestDatabase CREATE TABLE Users (UserID int PRIMARY KEY, UserName nvarchar(40), UserSurname nvarchar(40), DepartmentID int, PositionID int) CREATE TABLE Departments (DepartmentID int PRIMARY KEY, DepartmentName nvarchar(40)) CREATE TABLE Positions (PositionID int PRIMARY KEY, PositionName nvarchar(40), BaseSalary money) CREATE TABLE (CustomerID int PRIMARY KEY, CustomerName nvarchar(40), CustomerAddress nvarchar(255)) CREATE TABLE (OrderID int PRIMARY KEY, CustomerID int, UserID int, text) GO -- Заполнение таблиц USE TestDatabase INSERT Users VALUES (1, "Ivan", "Petrov", 1, 1) INSERT Users VALUES (2, "Ivan", "Sidorov", 1, 2) INSERT Users VALUES (3, "Petr", "Ivanov", 1, 2) INSERT Users VALUES (4, "Nikolay", "Petrov", 1, 3) INSERT Users VALUES (5, "Nikolay", "Ivanov", 2, 1) INSERT Users VALUES (6, "Sergey", "Sidorov", 2, 3) INSERT Users VALUES (7, "Andrey", "Bukin", 2, 2) INSERT Users VALUES (8, "Viktor", "Rybakov", 4, 1) INSERT Departments VALUES (1, "Production") INSERT Departments VALUES (2, "Distribution") INSERT Departments VALUES (3, "Purchasing") INSERT Positions VALUES (1, "Manager", 1000) INSERT Positions VALUES (2, "Senior analyst", 650) INSERT VALUES (1, "Alex Company", "606443, Russia, Bor, Lenina str., 15") INSERT VALUES (2, "Potrovka", "115516, Moscow, Promyshlennaya str., 1") INSERT VALUES (1, 1, 1, "Special parts") GO

Примечание. В Microsoft SQL Server 2000 запросы выполняются в приложении Query Analyzer. В Microsoft SQL Server 2005 запросы выполняются в SQL Server Management Studio.

В результате работы сценария на SQL сервере будет создана база данных TestDatabase с пятью пользовательскими таблицами: Users, Departments, Positions, Local Customers, Local Orders.

Элементы синтаксиса

Директивы сценария

Директивы сценария - это специфические команды, которые используются только в MS SQL. Эти команды помогают серверу определять правила работы со скриптом и транзакциями. Типичные представители: GO - сигнализирует SQL-серверу об окончании сценария, EXEC (или EXECUTE) - выполняет процедуру или скалярную функцию.

Комментарии

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

• -- - строковый комментарий исключает из выполнения только одну строку, перед которой стоят два минуса.
• /* */ - блоковый комментарий исключает из выполнения целый блок команд, заключенный в указанную конструкцию.

Типы данных

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

• Числа - для хранения числовых переменных (int, tinyint, smallint, bigint, numeric, decimal, money, smallmoney, float, real).
• Даты - для хранения даты и времени (datetime, smalldatetime).
• Символы - для хранения символьных данных (char, nchar, varchar, nvarchar).
• Двоичные - для хранения бинарных данных (binary, varbinary, bit).
• Большеобъемные - типы данных для хранения больших бинарных данных (text, ntext, image).
• Специальные - указатели (cursor), 16-байтовое шестнадцатиричное число, которое используется для GUID (uniqueidentifier), штамп изменения строки (timestamp), версия строки (rowversion), таблицы (table).

Примечание. Для использования русских символов (не ASCII кодировки) испольюзуются типы данных с приставкой "n" (nchar, nvarchar, ntext), которые кодируют символы двумя байтами. Иначе говоря, для работы с Unicode используются типы данных с "n".

Примечание. Для данных переменной длины используются типы данных с приставкой "var". Типы данных без приставки "var" имеют фиксированную длину области памяти, неиспользованная часть которой заполняется пробелами или нулями.

Идентификаторы

Идентификаторы - это специальные символы, которые используются с переменными для идентифицирования их типа или для группировки слов в переменную. Типы идентификаторов:

• @ - идентификатор локальной переменной (пользовательской).
• @@ - идентификатор глобальной переменной (встроенной).
• # - идентификатор локальной таблицы или процедуры.
• ## - идентификатор глобальной таблицы или процедуры.
• - идентификатор группировки слов в переменную.

Переменные

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

Объявление переменной выполняется командой DECLARE, задание значения переменной осуществляется либо командой SET, либо SELECT:

USE TestDatabase -- Объявление переменных DECLARE @EmpID int, @EmpName varchar(40) -- Задание значения переменной @EmpID SET @EmpID = 1 -- Задание значения переменной @EmpName SELECT @EmpName = UserName FROM Users WHERE UserID = @EmpID -- Вывод переменной @EmpName в результат запроса SELECT @EmpName AS GO

Примечание. В этом примере используется группировка слов в переменную - конструкция воспринимается как одна переменная, так как слова заключены в квадратные скобки.

Операторы

Операторы - это специальные команды, предназначенные для выполнения простых операций над переменными:

• Арифметические операторы: "*" - умножить, "/" - делить, "%" - модуль от деления, "+" - сложить, "-" - вычесть, "()" - скобки.
• Операторы сравнения: "=" - равно, ">" - больше, "<" - меньше, ">=" - больше или равно, "<=" меньше или равно, "<>" - не равно.
• Операторы соединения: "+" - соединение строк.
• Логические операторы: "AND" - и, "OR" - или, "NOT" - не.

Cистемные функции

Спецификация Transact-SQl значительно расширяет стандартные возможности SQL благодаря встроенным функциям:

• Агрегативные функции- функции, которые работают с коллекциями значений и выдают одно значение. Типичные представители: AVG - среднее значение колонки, SUM - сумма колонки, MAX - максимальное значение колонки, COUNT - количество элементов колонки.
• Скалярные функции- это функции, которые возвращают одно значение, работая со скалярными данными или вообще без входных данных. Типичные представители: DATEDIFF - разница между датами, ABS - модуль числа, DB_NAME - имя базы данных, USER_NAME - имя текущего пользователя, LEFT - часть строки слева.
• Функции-указатели- функции, которые используются как ссылки на другие данные. Типичные представители: OPENXML - указатель на источник данных в виде XML-структуры, OPENQUERY - указатель на источник данных в виде другого запроса.

Примечание. Полный список функций можно найти в справке к SQL серверу.

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

USE TestDatabase -- Использование агрегативной функции для подсчета средней зарплаты SELECT AVG(BaseSalary) AS FROM Positions GO -- Использование скалярной функции для получения имени базы данных SELECT DB_NAME() AS GO -- Использование скалярной функции для получения имени текущего пользователя DECLARE @MyUser char(30) SET @MyUser = USER_NAME() SELECT "The current user""s database username is: "+ @MyUser GO -- Использование функции-указателя для получения данных с другого сервера SELECT * FROM OPENQUERY(OracleSvr, "SELECT name, id FROM owner.titles") GO

Выражения

Выражение - это комбинация символов и операторов, которая получает на вход скалярную величину, а на выходе дает другую величину или исполняет какое-то действие. В Transact-SQL выражения делятся на 3 типа: DDL, DCL и DML.

• DDL (Data Definition Language)- используются для создания объектов в базе данных. Основные представители данного класса: CREATE - создание объектов, ALTER - изменение объектов, DROP - удаление объектов.
• DCL (Data Control Language)- предназначены для назначения прав на объекты базы данных. Основные представители данного класса: GRANT - разрешение на объект, DENY - запрет на объект, REVOKE - отмена разрешений и запретов на объект.
• DML (Data Manipulation Language)- используются для запросов и изменения данных. Основные представители данного класса: SELECT - выборка данных, INSERT - вставка данных, UPDATE - изменение данных, DELETE - удаление данных.

USE TestDatabase -- Использование DDL CREATE TABLE TempUsers (UserID int, UserName nvarchar(40), DepartmentID int) GO -- Использование DCL GRANT SELECT ON Users TO public GO -- Использование DML SELECT UserID, UserName + " " + UserSurname AS FROM Users GO -- Использование DDL DROP TABLE TempUsers GO

Управление выполнением сценария

В Transact-SQL существуют специальные команды, которые позволяют управлять потоком выполнения сценария, прерывая его или направляя в нужную логику.

• Блок группировки - структура, объединяющая список выражений в один логический блок (BEGIN … END).
• Блок условия - структура, проверяющая выполнения определенного условия (IF … ELSE).
• Блок цикла - структура, организующая повторение выполнения логического блока (WHILE … BREAK … CONTINUE).
• Переход - команда, выполняющая переход потока выполнения сценария на указанную метку (GOTO).
• Задержка - команда, задерживающая выполнение сценария (WAITFOR)
• Вызов ошибки - команда, генерирующая ошибку выполнения сценария (RAISERROR)

Динамическое конструирование выражений

Итак, поняв основы Transact-SQL и попрактиковавшись на простых примерах, можно перейти к более сложным структурам. Обычно базы данных создаются и заполняются с помощью сценариев (скриптов) - хотя визуальный редактор прост в обращении, но им никогда быстро и без недочетов не создашь большую базу данных и не заполнишь ее данными. Если вспомнить начало статьи, то опытная база данных как раз создавалась и заполнялась с помощью сценария. Сценарий - это одно или более выражений, объединенных в логический блок, которые автоматизируют работу администратора.

Обычно сценарии пишутся как универсальное средство для выполнения стандартных задач, поэтому в них применяется динамическое конструирование логики - в запросы и команды вставляются переменные, а не конкретные названия объектов, что позволяет быстро изменять параметры скрипта.

USE master -- Задание динамических данных DECLARE @dbname varchar(30), @tablename varchar(30), @column varchar(30) SET @dbname = "TestDatabase" SET @tablename = "Positions" SET @column = "BaseSalary" -- Использование динамических данных EXECUTE ("USE " + @dbname + " SELECT AVG(" + @column + ") AS FROM " + @tablename) GO

Выборка данных

В языках SQL выборка данных из таблиц осуществляется с помощью команды SELECT:

SELECT <названия колонок или *> FROM <название таблицы>

По умолчанию в команде SELECT используется параметр ALL, который можно не указывать. Если в команде указать параметр DISTINCT, то в результат попадут только уникальные (неповторяющиеся) записи из выборки.

Для того, чтобы изменить имена объектов в командах к SQL-серверу, используется команда AS. Использование этой команды помогает сокращать длину строки запроса, а так же получать результат в более удобочитаемом виде.

Выбрать все записи из таблицы Local Customers SELECT * FROM -- Выбрать уникальные записи колонки UserName из таблицы Users SELECT DISTINCT UserName FROM Users

Фильтрация данных осуществляется с помощью команды WHERE, в которой используются следующие операторы и команды сравнения: =, <, >, <=, >=, <>, LIKE, NOT LIKE, AND, OR, NOT, BETWEEN, NOT BETWEEN, IN, NOT IN, IS NULL, IS NOT NULL. В общем виде команда SELECT с фильтром выглядит так:

SELECT <названия колонок или *> FROM <название таблицы> WHERE <условие>

В строке сравнения разрешается использовать подстановочные символы:

• % - любое количество символов;
• _ - один символ;
• - любой символ, указанный в скобках;
• [^] - любой символ, не указанный в скобках.

Фильтрация позволяет использовать подзапросы, то есть конструировать запрос из нескольких подзапросов:

Выбрать записи колонки PositionID из таблицы Positions, где BaseSalary < 600 SELECT PositionID FROM Positions WHERE BaseSalary < 600 -- Выбрать все записи из таблицы Users, у кого имя Ivan или Andrey SELECT * FROM Users WHERE UserName IN ("Ivan", "Andrey")

Для сортировки данных в выборке используется командаORDER BY, но следует учесть, что эта команда не сортирует данные типа text, ntext и image. По умолчанию сортировка производится по возрастанию, поэтому параметр ASC в этом случае можно не указывать:

SELECT <названия колонок или *> FROM <название таблицы> WHERE <условие> ORDER BY <названия колонок>

Для того, чтобы ограничить количество строк в результате запроса, используется командаTOP:

SELECT TOP [количество строк] <названия колонок или *> FROM <название таблицы> WHERE <условие> ORDER BY <названия колонок>

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

• AVG(колонка) - среднее значение колонки;
• COUNT(колонка) - количество не NULL элементов колонки;
• COUNT(*) - количество элементов запроса;
• MAX(колонка) - максимальное значение в колонке;
• MIN(колонка) - минимальное значение в колонке;
• SUM(колонка) - сумма значений в колонке.

Примеры использования команд ORDER, TOP и функций агрегирования:

Выбрать 3 первые уникальные записи колонки UserName из таблицы Users, -- отсортированных по возрастанию UserName SELECT DISTINCT TOP 3 UserName FROM Users ORDER BY UserName -- Найти величину максимального оклада в организации SELECT MAX(BaseSalary) FROM Positions -- Найти сотрудников, у кого максимальный оклад в организации SELECT * FROM Users WHERE PositionID IN (SELECT PositionID FROM Positions WHERE BaseSalary IN (SELECT MAX(BaseSalary) FROM Positions))

Группировка данных

SQL позволяет производить группировку данных по определенным полям таблицы. Чтобы сгруппировать данные по какому-нибудь параметру, в SQL-запросе необходимо написать команду GROUP BY, в которой указать имя колонки, по которой производится группировка. Колонки, упомянутые в команде GROUP BY, должны присутствовать в команде SELECT, а так же команда SELECT должна содержать функцию агрегирования, которая будет применена к сгруппированным данным.

Найти количество работников в каждом отделе (сгруппировать работников по -- идентификатору отделов и сосчитать количество записей в каждой группе) SELECT DepartmentID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID

Чтобы отфильтровать строки в запросе с группировкой применяется специальная команда HAVING, в которой указывается условие фильтрации. Колонки, по которым производится фильтрация, должны присутствовать в команде GROUP BY. Команда HAVING может использоваться и без GROUP BY, в этом случае она работает аналогично команде WHERE, но она разрешает применять в условиях фильтрации только функции агрегирования.

Найти количество работников в первом отделе (сгруппировать работников по -- идентификатору отделов, сосчитать количество записей в каждой группе и -- вывести в результат только отдел с идентификатором равным 1) SELECT DepartmentID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID HAVING DepartmentID = 1 -- Найти количество работников с определенной должностью в каждом отделе -- (сгруппировать работников по идентификатору должностей и отделов и -- сосчитать количество записей в каждой группе), а также сосчитать -- количество работников в каждом отделе и общее количество работников SELECT DepartmentID, PositionID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID, PositionID WITH ROLLUP

Команда группировки также может дополняться оператором WITH CUBE, который дополняет формирует всевозможные комбинации из группируемых колонок: если есть N колонок, то получится 2^N комбинаций.

Найти количество работников с определенной должностью в каждом отделе -- (сгруппировать работников по идентификатору должностей и отделов и -- сосчитать количество записей в каждой группе), а также сосчитать -- количество работников по каждой должности, по каждому отделу и -- общее количество работников SELECT DepartmentID, PositionID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID, PositionID WITH CUBE

Функция агрегирования GROUPING позволяет определить, была ли запись добавлена командами ROLLUP и CUBE, или это запись получена из источника данных.

Найти количество работников в каждом отделе (сгруппировать работников по -- идентификатору отделов и сосчитать количество записей в каждой группе) -- а так же пометить дополнительные строки, несуществующие в источнике данных SELECT DepartmentID, COUNT(UserID) AS "Number of users", GROUPING(DepartmentID) AS "Added row" FROM Users GROUP BY DepartmentID WITH ROLLUP

Еще одна команда группировки COMPUTE позволяет группировать данные и выводить по ним отчет в разные таблицы. То есть команда GROUP BY с операторами ROLLUP и CUBE группирует данные и дописывает в таблицу дополнительны строки с отчетом, а команда COMPUTE группирует данные, разрывая исходную таблицу на несколько подтаблиц, а также формирует подтаблицы с отчетами. Команда COMPUTE может использоваться в двух режимах:

• как простая функция агрегирования, выводящая результат в отдельную таблицу;
• с параметром BY как команда группировки, разрезающая таблицу на несколько подтаблиц

Команда COMPUTE с параметром BY может использоваться только совместно с командой ORDER BY, причем столбцы сортировки должны совпадать со столбцами группировки.

Вывести таблицу пользователей компании, а также посчитать их количество SELECT * FROM Users COMPUTE COUNT(UserID)

Соединение таблиц

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

Соединять таблицы в SQL можно двумя способами: вертикально и горизонтально.

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

Найти всех пользователей с именем Ivan и соединить результат с -- результатом от запроса "Найти всех пользователей с фамилией Petrov" -- дублирующие записи исключить SELECT * FROM Users WHERE UserName = "Ivan" UNION SELECT * FROM Users WHERE UserSurname = "Petrov"

Горизонтальное соединение производится путем сцепки нескольких таблиц по ключевым колонкам. Самое простое горизонтальное соединение выполняется с помощью команды INNER JOIN, которая сцепляет таблицы, выбирая строки по ключевому полю, которое встречается в обоих таблицах.

SELECT <названия колонок или *> FROM <таблица_1> INNER JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Чтобы выполнить сцепление по всем полям левой таблицы, независимо, есть ли такие записи в правой таблице, необходимо использовать команду LEFT JOIN. Эта команда соединяет таблицы, выбирая все строки из левой таблицы, а отсутствующие данные правой таблицы заполняются значением NULL.

SELECT <названия колонок или *> FROM <таблица_1> LEFT JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Команда RIGHT JOIN аналогична предыдущей, разница заключается лишь в том, что она соединяет таблицы, выбирая все строки из правой таблицы, а отсутствующие данные левой таблицы заполняются значением NULL.

SELECT <названия колонок или *> FROM <таблица_1> RIGHT JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Команда FULL JOIN объединяет в себе левое и правое сцепление, то есть она соединяет таблицы, выбирая строки из обоих таблиц, а отсутствующие данные заполняются значением NULL.

SELECT <названия колонок или *> FROM <таблица_1> FULL JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Последняя и редкоиспользуемая команда соединения таблиц - это CROSS JOIN. Эта команда сцепляет таблицы без использования ключевого поля, а результат - это комбинация из всевозможных строк исходных таблиц.

SELECT <названия колонок или *> FROM <таблица_1> CROSS JOIN таблица_2

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

SELECT * FROM Users INNER JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

Изменение данных

Прежде, чем рассказывать о командах изменения данных, нужно пояснить особенность диалекта Transact-SQL. Как видно из самого названия, этот механизм основан на транзакциях, то есть на последовательности операций, объединенных в один логический модуль, будь то запрос на выбоку данных, изменения данных или структуры таблиц. На время транзакции все используемые в сценарии данные блокируются, что позволяет избежать несоотвествия данных во время начала работы с таблицей и завершением сценария.

За транзакции в Transact-SQL отвечает структура BEGIN TRANSACTION ... COMMIТ TRANSACTION. Эту структуру использовать необязательно, но тогда все команды сценария являются необратимыми, то есть нельзя сделать "откат" к предыдущему состоянию. Полная структура блока транзакций:

BEGIN TRANSACTION [имя транзакции] [операции] COMMIТ TRANSACTION [имя транзакции] или ROLLBACK TRANSACTION [имя транзакции]

Ниже приведен пример использования этого блока:

Утановить всем сотрудникам новый оклад BEGIN TRANSACTION TR1 UPDATE Positions SET BaseSalary = 2500000000000000 IF @@ERROR <> 0 BEGIN RAISERROR("Error, transaction not completed!",16,-1) ROLLBACK TRANSACTION TR1 END ELSE COMMIT TRANSACTION TR1

Для вставки данных в таблицы SQL-сервера используется команда INSERT INTO:

INSERT INTO [название таблицы] (колонки) VALUES ([значения колонок])

Вторая часть комнады является необязательной для MS SQL Server 2003, но MS JET SQL без этого слова будет выдавать ошибку синтаксиса. Вставка обычно производиться целострочно, то есть в комнаде указываются все колонки таблицы и значения, которые нужно в них занести. Если же колонка имеет значение по умолчанию или разрешает пустое значения, то в команде вставки эту колонку можно не указывать. Команда INSERT INTO также разрешает указывать вносимые данные не по порядку следования колонок, но в этом случае нужно обозначить используемый порядок колонок.

В таблицу Users вставить строку с данными UserID = 9, UserName = "Nikolay", -- UserSurname = "Gryzlov", DepartmentID = 4, PositionID = 2. INSERT INTO Users VALUES (9, "Nikolay", "Gryzlov", 4, 2) -- В таблицу Users вставить строку с данными UserID = 10, UserName = "Nikolay", -- UserSurname = "Kozin", DepartmentID - значение по умолчанию, PositionID - не указано. INSERT Users VALUES (10, "Nikolay", "Kozin", DEFAULT, NULL) -- В таблицу Users вставить строку с данными UserName = "Angrey", UserSurname = "Medvedev", -- UserID = 11, остальные значения по умолчанию INSERT INTO Users (UserName, UserSurname, UserID) VALUES ("Angrey", "Medvedev", 11)

Для того, чтобы изменить значение ячейки таблицы, используется команда UPDATE:

UPDATE [название таблицы] SET [имя колонки]=[значение колонок] WHERE [условие]

Обновление (изменение) значений в таблице можно производить безусловно, с условием или с выборкой данных из другой таблицы.

Установить всем должностям зарплату в 2000 единиц. UPDATE Positions SET BaseSalary = 2000 -- Должностям с идентификатором 1 установить зарплату в 2500 единиц. UPDATE Positions SET BaseSalary = 2500 WHERE PositionID = 1 -- Должностям с идентификатором 2 уменьшить зарплату на 30%. UPDATE Positions SET BaseSalary = BaseSalary * 0.7 WHERE PositionID = 2 -- Установить всем должностям зарплату, равную (30 000 разделить на количество -- сотрудников в организации) UPDATE Positions SET BaseSalary = 30000 / (SELECT COUNT(UserID) FROM Users)

Удаление данных производится командой DELETE:

DELETE FROM [название таблицы] WHERE [условие]

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

Удалить пользователя с идентификатором 10 -- В режиме отладки рекомедуется использовать команду SELECT, -- чтобы знать, какие данные будут стерты: -- SELECT UserID FROM Users WHERE UserID = 10 DELETE FROM Users WHERE UserID = 10 -- Удалить всех польователей отдела Production DELETE Users FROM Users INNER JOIN Departments ON Users.DepartmentID = Departments.DepartmentID WHERE Departments.DepartmentName = "Production" -- Удалить всех пользователей DELETE FROM Users

Примечание! В примере для фильтрации данных применено сцепление таблиц. Хотя в команде перечисляются несколько таблиц, удаление данных будет произведено только из той таблицы, которая указана после слова DELETE.

Более быстрая команда для очистки таблицы - это TRUNCATE TABLE.

TRUNCATE TABLE [название таблицы]

Пример удаления всех данных:

Очистить таблицу Users TRUNCATE TABLE Users

Transact-SQL позволяет использовать временные таблицы, то есть таблицы, которые создаются в памяти сервера на время работы пользователя с базой данных. Временные таблицы могут иметь любое имя, но начинаться обязаны с символа #.

Создать временную таблицу #TempTable, в которую скопировать содержание -- колонки UserName таблицы Users SELECT UserName INTO #TempTable FROM Users -- Выбрать все записи временной таблицы #TempTable SELECT * FROM #TempTable

Хранимые процедуры и функции

Хранимые процедуры и функции представляют собой набор SQL-операторов, которые можно сохранять на сервере. Если сценарий сохранен на сервере, то клиентам не придется повторно задавать одни и те же отдельные операторы, вместо этого они смогут обращаться к хранимой процедуре. Ситуации, когда хранимые процедуры особенно полезны:

• Многочисленные клиентские приложения написаны на разных языках или работают на различных платформах, но должны выполнять одинаковые операции с базами данных.
• Безопасность играет первостепенную роль. Хранимые процедуры используются для всех стандартных операций, что обеспечивает совместимость и безопасность среды, а процедуры гарантируют надлежащую регистрацию каждой операции. При таком типе установки приложения и пользователи не получают непосредственный доступ к таблицам базы данных и могут выполнять только конкретные хранимые процедуры.
• Необходимо снизить сетевой трафик между клиентом и сервером. Объем пересылаемой информации между сервером и клиентом существенно снижается, но увеличивается нагрузка на систему сервера баз данных, так как в этом случае на стороне сервера выполняется большая часть работы по обработке данных.

Пример создания хранимой процедуры и хранимой функции:

Создание функции обновления зарплат CREATE PROCEDURE usp_UpdateSalary AS UPDATE Positions SET BaseSalary = 2000 GO -- Создание функции получения имени пользователя CREATE FUNCTION usf_GetName (@UserID int) RETURNS varchar(255) BEGIN IF @UserID IS NULL SET @UserID = 1 RETURN (SELECT UserName + " " + UserSurname FROM Users WHERE UserID = @UserID) END GO -- Обновление зарплат EXEC TestDatabase.dbo.usp_UpdateSalary -- Получение имени пользователя с идентификатором 2 SELECT TestDatabase.dbo.usf_GetName(2)

Итак, хранимые процедуры и функции дают следующие преимущества:

• производительность;
• общая логика для всез запросов;
• уменьшение трафика;
• безопасность - доступ пользователю дается не к таблице, а к процедуре;

Производительность

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

• Избегать NOT - команды отрицания выполняются в несколько этапов, что увеличивает нагрузку на сервер.
• Избегать LIKE - этот оператор сравнения применяет более мягкие шаблоны сравнения, чем оператор =, что увеличивает необходимое число этапов фильтрации.
• Применять точные шаблоны поиска - применение подстановочных символов увеличивает время выполнения запроса, так как для проверки всех вариантов подстановки требуется дополнительные ресурсы сервера.
• Избегать ORDER - команда сортировки требует упорядочивания строк таблицы вывода, что задерживает получение результата.

Виталий Бочкарев