Последнее обновление: 24.06.2017
SQL Server является одной из наиболее популярных систем управления базами данных (СУБД) в мире. Данная СУБД подходит для самых различных проектов: от небольших приложений до больших высоконагруженных проектов.
SQL Server был создан компанией Microsoft. Первая версия вышла в 1987 году. А текущей версией является версия 16, которая вышла в 2016 году и которая будет использоваться в текущем руководстве.
SQL Server долгое время был исключительно системой управления базами данных для Windows, однако начиная с версии 16 эта система доступна и на Linux.
SQL Server характеризуется такими особенностями как:
Производительность. SQL Server работает очень быстро.
Надежность и безопасность. SQL Server предоставляет шифрование данных.
Простота. С данной СУБД относительно легко работать и вести администрирование.
Центральным аспектом в MS SQL Server, как и в любой СУБД, является база данных. База данных представляет хранилище данных, организованных определенным способом. Нередко физически база данных представляет файл на жестком диске, хотя такое соответствие необязательно. Для хранения и администрирования баз данных применяются системы управления базами данных (database management system) или СУБД (DBMS). И как раз MS SQL Server является одной из такой СУБД.
Для организации баз данных MS SQL Server использует реляционную модель. Эта модель баз данных была разработана еще в 1970 году Эдгаром Коддом. А на сегодняшний день она фактически является стандартом для организации баз данных.
Реляционная модель предполагает хранение данных в виде таблиц, каждая из которых состоит из строк и столбцов. Каждая строка хранит отдельный объект, а в столбцах размещаются атрибуты этого объекта.
Для идентификации каждой строки в рамках таблицы применяется первичный ключ (primary key). В качестве первичного ключа может выступать один или несколько столбцов. Используя первичный ключ, мы можем ссылаться на определенную строку в таблице. Соответственно две строки не могут иметь один и тот же первичный ключ.
Через ключи одна таблица может быть связана с другой, то есть между двумя таблицами могут быть организованы связи. А сама таблица может быть представлена в виде отношения ("relation").
Для взаимодействия с базой данных применяется язык SQL (Structured Query Language). Клиент (например, внешняя программа) отправляет запрос на языке SQL посредством специального API. СУБД должным образом интерпретирует и выполняет запрос, а затем посылает клиенту результат выполнения.
Изначально язык SQL был разработан в компании IBM для системы баз данных, которая называлась System/R. При этом сам язык назывался SEQUEL (Structured English Query Language). Хотя в итоге ни база данных, ни сам язык не были впоследствии официально опубликованы, по традиции сам термин SQL нередко произносят как "сиквел".
В 1979 году компания Relational Software Inc. разработала первую систему управления баз данных, которая называлась Oracle и которая использовала язык SQL. В связи с успехом данного продукта компания была переименована в Oracle.
Впоследствии стали появляться другие системы баз данных, которые использовали SQL. В итоге в 1989 году Американский Национальный Институт Стандартов (ANSI) кодифицировал язык и опубликовал его первый стандарт. После этого стандарт периодически обновлялся и дополнялся. Последнее его обновление состоялось в 2011 году. Но несмотря на наличие стандарта нередко производители СУБД используют свои собственные реализации языка SQL, которые немного отличаются друг от друга.
Выделяются две разновидности языка SQL: PL-SQL и T-SQL. PL-SQL используется в таких СУБД как Oracle и MySQL. T-SQL (Transact-SQL) применяется в SQL Server. Собственно поэтому в рамках текущего руководства будет рассматриваться именно T-SQL.
В зависимости от задачи, которую выполняет команда T-SQL, он может принадлежать к одному из следующих типов:
DDL (Data Definition Language / Язык определения данных). К этому типу относятся различные команды, которые создают базу данных, таблицы, индексы, хранимые процедуры и т.д. В общем определяют данные.
В частности, к этому типу мы можем отнести следующие команды:
CREATE : создает объекты базы данных (саму базу даных, таблицы, индексы и т.д.)
ALTER : изменяет объекты базы данных
DROP : удаляет объекты базы данных
TRUNCATE : удаляет все данные из таблиц
DML (Data Manipulation Language / Язык манипуляции данными). К этому типу относят команды на выбору данных, их обновление, добавление, удаление - в общем все те команды, с помощью которыми мы можем управлять данными.
К этому типу относятся следующие команды:
SELECT : извлекает данные из БД
UPDATE : обновляет данные
INSERT : добавляет новые данные
DELETE : удаляет данные
DCL (Data Control Language / Язык управления доступа к данным). К этому типу относят команды, которые управляют правами по доступу к данным. В частности, это следующие команды:
GRANT : предоставляет права для доступа к данным
REVOKE : отзывает права на доступ к данным
П ри переходе из MS SQL в MySQL, кроме миграции данных, вы должны также перенести код приложения, который находится в базе данных.
Ранее мы обсуждали, как с помощью инструмента WorkSQL Workbench.
В рамках миграции, он будет только конвертировать таблицы и копировать данные, но он не будет преобразовывать триггеры, представления и хранимые процедуры. Вы должны вручную преобразовать их в базе данных MySQL.
Для выполнения этого преобразования вручную, вы должны понимать основные различия между запросами MS SQL и MySQL.
Во время моего преобразования из Microsoft SQL Server в базу данных MySQL, я столкнулся со следующими операторами и запросами MS SQL, которые не были совместимы с MySQL, и я должен был преобразовать их, как показано ниже.
Основной синтаксис создания хранимых процедур отличается.
MS SQL Stored, синтаксис создания процедуры:
CREATE PROCEDURE . @someString VarChar(150) As BEGIN -- Sql queries goes here END
для MySQL синтаксис создания процедуры:
CREATE PROCEDURE storedProcedureName(IN someString VarChar(150)) BEGIN -- Sql queries goes here END
В коде MS SQL, я создал несколько временных таблиц, которые требуются для применения. Синтаксис для создания временной таблицы различается, как показано ниже.
MS SQL синтаксис создания временной таблицы:
CREATE TABLE #tableName(emp_id VARCHAR(10)COLLATE Database_Default PRIMARY KEY, emp_Name VARCHAR(50) COLLATE Database_Default, emp_Code VARCHAR(30) COLLATE Database_Default, emp_Department VARCHAR(30) COLLATE Database_Default)
MySQL синтаксис создания временной таблицы:
CREATE TEMPORARY TABLE tableName(emp_id VARCHAR(10), emp_Name VARCHAR(50), emp_Code VARCHAR(30), emp_Department VARCHAR(30));
Я использовал много условий в моих хранимых процедур и триггерах, которые не работали после преобразования в MySQL, поскольку синтаксис отличается, как показано ниже.
MS SQL условие IF Синтаксис:
If(@intSomeVal="") BEGIN SET @intSomeVal=10 END
MySQL условие IF Синтаксис:
IF @intSomeVal=""THEN SET @intSomeVal=10; END IF;
Другое общее использование, если условие, проверить, вернулся ли в запросе какие-либо строки или нет; и если он возвращает несколько строк, сделать что-то. Для этого я использовал IF EXISTS в MS SQL, который должен быть преобразован в MySQL команды IF, как описано ниже.
MS SQL IF EXITS Пример:
IF EXISTS(SELECT 1 FROM #tableName WITH(NOLOCK) WHERE ColName="empType") BEGIN -- Sql queries goes here END
MySQL эквивалент выше, используя при выполнении условия:
IF(SELECT count(*) FROM tableName WHERE ColName="empType") > 0 THEN -- Sql queries goes here END IF;
Использование функций данных внутри хранимой процедуры является довольно распространенным явлением. В следующей таблице приведены различия между MS SQL и MySQL данных, связанных функций.
| MS SQL Server | MySQL Server |
|---|---|
| GETDATE() | NOW() SYSDATE() CURRENT_TIMESTAMP() |
| GETDATE() + 1 | NOW() + INTERVAL 1 DAY CURRENT_TIMESTAMP +INTERVAL 1 DAY |
| DATEADD(dd, -1, GETDATE()) | ADDDATE(NOW(), INTERVAL -1 DAY) |
| CONVERT(VARCHAR(19),GETDATE()) | DATE_FORMAT(NOW(),’%b %d %Y %h:%i %p’) |
| CONVERT(VARCHAR(10),GETDATE(),110) | DATE_FORMAT(NOW(),’%m-%d-%Y’) |
| CONVERT(VARCHAR(24),GETDATE(),113) | DATE_FORMAT(NOW(),’%d %b %Y %T:%f’) |
| CONVERT(VARCHAR(11),GETDATE(),6) | DATE_FORMAT(NOW(),’%d %b %y’) |
В MS SQL хранимые процедуры, вы можете объявлять переменные где-то между “Begin” и “end”
Однако в MySql вам придется объявить их только после заявления хранимой процедуры “begin”. Декларация переменной в любой точке между не допускается.
В MS SQL, вы будете использовать SELECT, TOP, если вы хотите выбрать только первые несколько записей. Например, чтобы выбрать 1-ые 10 записей, вы сделаете следующее:
SELECT TOP 10 * FROM TABLE;
В MySQL, вам придется использовать LIMIT вместо TOP, как показано ниже.
SELECT * FROM TABLE LIMIT 10;
В MS SQL вы выполните следующие действия (функции Convert), чтобы преобразовать целое число в символ.
CONVERT(VARCHAR(50), someIntVal)
В MySQL, вы будете использовать функцию CAST для преобразования целого на символ, как показано ниже.
CAST(someIntVal as CHAR)
Если вы манипулируете множеством данных внутри хранимой процедуры, вы можете использовать выполнение некоторых конкатенации строк.
В MS SQL оператор конкатенации + символ. Пример такого использования показан ниже.
SET @someString = "%|" + @someStringVal + "|%"
В MySQL, если вы используете режим AnSi, то, он такой же, как и в MS SQL. т.е. + символ, будет работать для конкатенации.
Но, в режиме по умолчанию, в MySQL, мы должны использовать функцию CONCAT(“str1”, “str2”, “str3”.. “strN”).
SET someString = CONCAT("%|", someStringVal, "|%");
Недостаточно писать код хорошо читаемым: он также должен быстро выполняться.
Существует три базовых правила для написания такого T-SQL кода, который будет работать хорошо. Они кумулятивные – выполнение всех этих правил окажет положительное влияние на код. Пропуск или изменение любого из них – скорее всего приведет к отрицательному влиянию на производительность вашего кода.
Не верите? Давайте посмотрим на этот запрос:
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";
И мы получаем вот такой план выполнения запроса:
И здесь используются только две операции чтения, вместо четырех. И да, я понимаю, что сделал и так быстро выполняющийся запрос чуть-чуть более быстрым. Но что было бы, если бы в таблице хранились миллионы строк? Ага, тогда-то я стал бы героем.
Используйте правильные типы данных.
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 или условиях поиска, либо используйте только те, которые могут быть использованы в условиях поиска по индексу.
По сути, они загоняют вас в ловушку. На первый взгляд, этот чудесный механизм позволяет нам использовать 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;
Теперь количество операций чтения упало до двух, поскольку используется поиск по индексу – индекс используется правильно.
Хинты в запросах всегда должны применяться в последнюю очередь, после того как все остальные возможные варианты были опробованы и не дали положительного результата.
Вот типичный пример неудачного использования курсора. Нам надо обновить цвет продуктов, выбранных по определенному критерию. Он не выдуман – он базируется на коде, который мне однажды пришлось оптимизировать.
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
Этот раздел описывает основные различия в синтаксе языка SQL, используемого СУБД Firebird и MS SQL.
СУБД Firebird и MS SQL могут ссылаться на объекты базы данных (таблицы, поля и т.д.) по их именам напрямую, если имена объектов не содержат пробелы и другие недопустимые в прямой ссылке символы (например, нелатинские буквы). Для использования пробелов и других символов СУБД MS SQL использует квадратные скобки, [ и ] , а СУБД Firebird использует двойные кавычки, " . Еще одно различие - возможность использования в СУБД MS SQL схемы для ссылки на объект: база_данных.владелец_объекта.объект . СУБД Firebird не допускает такой нотации.
СУБД MS SQL использует регистро-зависимые имена объектов, если при установке Вы выбрали использование различения регистра символов; в противном случае, имена объектов регистро-независимы. Весело? Не очень...
СУБД MS SQL способна работать с идентификаторами, имена которых заключены в двойные кавычки, но по умолчанию эта возможность доступна только при доступе через OLE DB и ODBC, но не при доступе через DB-Library. По этой причине такую практику работы следует избегать.
СУБД MS SQL 7 и выше поддерживает обновляемые соединения (joins) (обновление, удаление, вставка). СУБД Firebird не распознает такой синтакс.
Типы даных, конечно, различаются. Хотя обе СУБД имеют общее подмножество наиболее часто используемых типов. Этот вопрос редко вызывает проблемы при переносе базы данных.
Различаются встроенные функции. Большинство из встроенных функций СУБД MS SQL можно заменить в СУБД Firebird использованием функций, определяемых пользователем (UDFs).
Различаются форматы указания строковых констант для дат. СУБД Firebird принимает строки различных форматов, вне зависимости от используемой платформы. СУБД MS SQL, в свою очередь, использует совмещение серверо-независимых, серверо-платформенных форматов и формата настройки клиентского соединения. Дополнительно, методы доступа СУБД MS SQL обычно вводят один или два уровня, в которых строковая константа может быть преобразована в дату тем или иным образом.
В СУБД MS SQL можно определять бОльшее количество переменных окружения, чем в СУБД Firebird , но наиболее общие можно найти и в СУБД Firebird (извлечение идентификатора и имени пользователя). Единственная важная переменная, которая отсутствует в СУБД Firebird , - это переменная, возвращающая количество строк последней операции (с версии 1.5 СУБД Firebird такая переменная введена - прим. перев.).
Важное различие было в том, что СУБД Firebird 1.0 не поддерживала оператор CASE СУБД MS SQL. Иногда можно было заменить его функциональность использованием хранимой процедуры. Начиная с версии 1.5, СУБД Firebird поддерживает использование оператора CASE .
Небольшое различие между СУБД еще и в том, что СУБД MS SQL не использует разделителей для операторов, что может служить источником трудно обнаруживаемых ошибок при переходе, особенно при использовании множества скобок. СУБД Firebird в скриптах требует завершать каждый оператор точкой с запятой (если не определен другой разделитель - прим. перев.), поэтому ошибки легче обнаружить.
Обе СУБД MS SQL и Firebird поддерживают комментарии, заключенные между разделителями /* и */ . СУБД MS SQL также поддерживает синтакс «два дефиса » -- для однострочного комментария. Некоторые утилиты для СУБД Firebird также поддерживают такой синтакс.
Оператор WHILE существует в обоих СУБД Firebird и MS SQL, но с некоторыми различиями. Не существует операторов BREAK или CONTINUE , но их можно эмулировать при помощи дополнительных конструкций. Так же есть небольшое различие в используемом синтаксисе: СУБД Firebird требует наличия ключевого слова DO после условия цикла. Сравните следующие эквивалентные части кода.
/* Синтакс Firebird. */ WHILE (i < 3) DO BEGIN i = i + 1; j = j * 2; END /* Синтакс MS SQL. */ WHILE (i < 3) BEGIN SET @i = @i + 1 SET @j = @j * 2 END
Оператор RETURN в СУБД MS SQL возвращает значение целочисленной переменной и прекращает выполнение. В СУБД Firebird существует оператор EXIT , который передает управление на заключительный END хранимой процедуры. Однако здесь нет скрытой возвращаемой переменной, поэтому, если Вам необходимо вернуть некоторое значение (что опционально в СУБД MS SQL), Вы должны явно объявить возвращаемую переменную в процедуре.
Оператор WAITFOR в СУБД MS SQL приостанавливает выполнение на некоторое время или до наступления указанного времени. Что-то подобное можно осуществить при помощи функций, определяемых пользователем (UDFs), в СУБД Firebird . Но в обоих СУБД использование такого оператора следует исключить, поскольку взаимодействие с клиентом полностью приостанавливается (в СУБД Firebird это может привести и к приостановке обслуживания всех соединений, а не только соединения, вызвавшего аналог указанного оператора - прим. перев.).
Стандартными операторами, имеющимися в обоих СУБД, являются SELECT , INSERT , UPDATE и DELETE . СУБД Firebird и MS SQL поддерживают их, но в СУБД MS SQL есть несколько нестандартных расширений этих операторов, о которых необходимо рассказать на случай их использования.
В СУБД Firebird оператор SELECT не позволяет использовать ключевое слово INTO для создания новой таблицы «на лету ». Вместо этого, ключевое слово INTO используется для связи результата запроса с переменной.
/* Синтаксис MS SQL для присваивания значения поля переменной. */ SELECT @my_state = state FROM authors WHERE auth_name = "John" /* Синтаксис Firebird. */ SELECT state INTO:state /* --> обратите внимание на ":" перед именем переменной */ FROM authors WHERE auth_name = "John"
В СУБД MS SQL 7 и выше в операторе SELECT можно указвать спецификатор TOP для органичения возвращаемого набора данных. Эта функция в настоящий момент находится в стадии разработки для СУБД Firebird . (Спецификаторы FIRST и SKIP в СУБД Firebird введены, начиная с версии 1.5. - прим. перев.)
Обе СУБД MS SQL и Firebird поддерживают обычный синтаксис оператора INSERT и оператора INSERT..SELECT .
Обе СУБД MS SQL и Firebird поддерживают обычный синтаксис оператора UPDATE . СУБД MS SQL также поддерживает синтаксис оператора UPDATE , в котором выполняется соединение (join) и производится обновление одной из таблиц соединения. Можно думать об этом как об условии WHERE на стероидах. Если такая функция очень нужна, то ее можно реализовать в СУБД Firebird с использованием представлений (views).
Обе СУБД MS SQL и Firebird поддерживают обычный синтаксис оператора DELETE . СУБД MS SQL также поддерживает оператор TRUNCATE TABLE , который более эффективен (но и опаснее), чем оператор DELETE . (СУБД MS SQL также поддерживает синтаксис оператора DELETE , в котором выполняется соединение. - прим. перев.)
/* Синтаксис MS SQL для удаления всех записей my_table. */ TRUNCATE TABLE my_table /* ...или... */ DELETE FROM my_table /* Синтаксис Firebird. */ DELETE FROM my_table
В СУБД Firebird в DSQL (dynamic SQL) транзакции «напрямую » не используются. Именованные транзакции в этом случае недоступны совсем. (Операторы DSQL выполняются в контексте транзакций, запущенных и контролируемых клиентским приложением.- прим. перев.) Синтаксис обоих СУБД поддерживает ключевое слово WORK для совместимости.
В большинстве случаев проблем с транзакциями не должно возникать: явное управление транзакциями «на месте » в СУБД MS SQL используется обычно из-за отсутствия поддержки управлением через исключения (exceptions).
В СУБД MS SQL есть глобальная переменная XACT_ABORT , которая управляет откатом транзакции при возникновении ошибки времени выполнения (run-time error). В противном случае Вам необходимо проверять значение переменной @@ERROR после выполнения каждого оператора.
В общем, большинство проблем, связанных с уровнями изоляции транзакций в СУБД MS SQL, пропадают при переходе на СУБД Firebird . Соперничество между «читателями » и «писателями » минимально за счет использования многоверсионной архитектуры (multigeneration architecture, MGA).
В СУБД MS SQL курсоры используются, в основном, для перемещения по результатам запросов, чтобы выполнить с этими результатами некоторые действия. Кроме синтаксиса, нет большой разницы для выполнения одной и той же задачи. Хотя существуют опции для перемещения вперед и назад, на практике используются, по большей части, однонаправленные курсоры.
/* Синтакс MS SQL. */ DECLARE my_cursor CURSOR FOR SELECT au_lname FROM authors ORDER BY au_lname DECLARE @au_lname varchar(40) OPEN my_cursor FETCH NEXT FROM my_cursor INTO @au_lname WHILE @@FETCH_STATUS = 0 BEGIN /* Сделать что-то интересное с @au_lname. */ FETCH NEXT FROM my_cursor END CLOSE my_cursor DEALLOCATE my_cursor /* Синтакс Firebird. */ DECLARE VARIABLE au_lname VARCHAR(40); ... FOR SELECT au_lname FROM authors ORDER BY au_lname INTO:au_lname DO BEGIN /* Сделать что-то интересное с au_lname. */ END
Заметьте, что СУБД MS SQL может размещать курсоры в переменных, и передавать эти переменные как параметры; это невозможно в СУБД Firebird .
