Худшие методы (MS SQL Server) - использование неуточненных имен - Программные продукты

Источник: SQL exercises

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

Использование уточненных имен

Никогда не забывайте уточнять названия объектов именем владельца объекта. Это улучшает производительность, по крайней мере, в силу двух причин. Прежде всего, когда Вы определяете имя объекта, не уточняя его именем владельца, SQL Server будет сначала искать объект, принадлежащий текущему работающему пользователю. Если такой объект не будет найден, будет использован объект с тем же самым именем, но принадлежащий dbo. Так, если пользователь по имени John подключился к Northwind и выполняет SELECT OrderID FROM Orders, SQL Server будет искать объект John.Orders в Northwind, и в случае его отсутствия будет использован dbo.Orders. Если бы он вместо этого выполнил запрос SELECT OrderID FROM dbo.Orders, SQL Server не нужно было бы искать объект по имени John.Orders. Это не может существенно повлиять на производительность, но ведь важно также избежать использования неожиданного объекта. Представьте, что приложение авторизуется как пользователь John и выполняет оператор SELECT OrderID FROM Orders. Это работает прекрасно до того дня, пока кто-то под логином John не решит создать таблицу с именем John.Orders (по той или иной причине). Теперь приложение внезапно начнет возвращать OrderIDs из новой таблицы John.Orders, и эту ошибку можно очень долго искать.

Другая причина повышения производительности в результате уточнения имен объекта именем владельца заключается в том, что есть хороший шанс, что SQL Server будет многократно использовать планы выполнения для операторов, записанных именно таким образом. Например, если Вы используете sp_executesql, чтобы выполнить динамические операторы SQL, Вы действительно использовать полностью уточненные имена объектов, чтобы SQL Server повторно использовал планы выполнения. Полностью уточненное имя объекта означает определение всех его четырех частей, то есть. Имя_сервера.имя_базы.имя_владельца.имя_объекта. Если Вы опустите любую из них, будет использоваться значение по умолчанию. Сервер по умолчанию и база данных - это конечно сервер и база данных, на которых выполняется запрос, а имя_владельца по умолчанию - это, как и ранее, dbo. Но даже при том, что используется имя по умолчанию, SQL Server не будет повторно использовать план выполнения для операторов, иначе специфицирующих уточненные имена объектов. Вы можете увидеть это сами в SQL Profiler, выполнив следующий небольшой скрипт. Стартуйте Profiler и подключите его к вашему серверу. Добавьте все события в классе событий Stored Procedures (хранимые процедуры) и удалите все другие события.

DECLARE @sql nvarchar(200)
DECLARE @params nvarchar(100)
DECLARE @intvar int

-- Создание оператора, используя полностью уточненные имена
SET @sql = N'SELECT OrderID, CustomerID, EmployeeID FROM dbo.Orders WHERE EmployeeID = @empid'
SET @params = N'@empid int'
SET @intvar = 3
EXEC sp_executesql @sql, @params, @empid = @intvar

-- Выполните тот же оператор еще раз, повторно используя предыдущий план выполнения
-- Замечание: SP:ExecContextHit в Profiler
SET @intvar = 4
EXEC sp_executesql @sql, @params, @empid = @intvar

-- Тот же оператор, но не использующий полностью уточненные имена
-- Замечание: здесь нет SP:ExecContextHit в Profiler
SET @sql = N'SELECT OrderID, CustomerID, EmployeeID FROM Orders WHERE EmployeeID = @empid'
SET @params = N'@empid int'
SET @intvar = 4
EXEC sp_executesql @sql, @params, @empid = @intvar

Когда Вы запустите скрипт, то должны увидеть SP:CacheInsert, когда выполнен первый запрос, но когда он выполняется снова, используя только измененное значение @empid, Вы вместо этого увидите SP:ExecContextHit. Это означает, что SQL Server смог повторно использовать план выполнения. Наконец, когда третий оператор выполнен, Вы снова увидите SP:CacheInsert, и это даже при том, что результат запроса не изменился по сравнению с предыдущим случаем. Причина, как я объяснил выше, заключается в том, что второй оператор не уточняет имя Orders так, как это делалось в первом операторе.

Указание имени владельца важно не только для выполнения оператора SELECT. Не менее важно, возможно, даже более важно, уточнять объекты именем владельца при их создании. Например, если бы Джон создал таблицу, не указывая ее владельца, то он сам стал бы этим владельцем. Это означает, что любой другой пользователь, который захочет выполнить оператор SELECT для этой таблицы, должен уточнять имя таблицы его именем, делая выборку из нее. Если бы они также никогда не забывали уточнять имена объектов, это была бы не такая большая проблема, но так как мы знаем, что люди ленивы (или не знают, что означает уточнение имени), они, вероятно, будут это делать далеко не всегда. Итак, когда Джон создает свою таблицу, его оператор должен с CREATE TABLE dbo.TableName ... Есть, конечно, исключения, когда Вы действительно хотите создать таблицу с Джоном (John) в качестве владельца, но в таких случаях он должен указать это, то есть написать CREATE TABLE John.TableName ... Почему это важно? Представьте, что Джон создает эту таблицу в базе данных на тестовом сервере и сохраняет оператор в виде скрипта. Позже, когда эта таблица должна быть создана в рабочей базе данных, запустить скрипт мог кто-то иной, в результате чего таблица получит другого владельца.

Еще более важно уточнять имена объектов, используемых внутри хранимой процедуры. В противном случае, объекты, которые не уточнены именем владельца и упоминаются в операторах SELECT, INSERT, UPDATE и DELETE, по умолчанию будут приписаны владельцу хранимой процедуры, а не человеку, выполняющему ее. Опять таки, эти объекты могли бы быть теми, что Вы и хотите, но всегда лучше явно уточнять имя. Если процедура используется как механизм контроля доступа к данным в таблицах (то есть прямой доступ к таблицам ограничен, и все пользователи должны использовать процедуры, которые выбирают из этих таблиц данные), вы можете столкнуться с другой проблемой. Если имена для этих таблиц, доступ к которым ограничен, не будут уточнены в процедуре, то только создателю процедуры будет разрешен к ним доступ.

Наконец, если Вы не уточните имена объектов, используемых в процедурах, содержащих операторы CREATE/ALTER/DROP TABLE, TRUNCATE TABLE, CREATE/DROP INDEX, UPDATE STATISTICS, и командах DBCC, Вы можете разрушить систему. Причиной является то, что разрешение имен объектов происходит во время исполнения; при этом используется имя пользователя, который выполняет процедуру в качестве владельца по умолчанию для объектов, имена которых не полностью уточнены. Представьте, что пользователь Джейн имеет таблицу по имени Orders, где она хранит информацию своих заказов. Теперь ей нужно быстро и легко удалить эту информацию, поэтому она создает примерно такую процедуру:

CREATE PROCEDURE TruncateOrders
AS
BEGIN
TRUNCATE TABLE Orders
END

Она тестирует процедуру, и та работает прекрасно, ее таблица Orders очищается. Потом она переносит процедуру в рабочую среду. Когда однажды Джону потребуется удалить данные из таблицы Orders, принадлежащей Джейн, он выполняет эту процедуру (выполняя EXEC Jane.TruncateOrders). Догадайтесь, что случится? Ну, если есть таблица с именем John.Orders, то она будет очищена, а если нет, то будет очищена dbo.Orders, поскольку dbo - это значение по умолчанию, которое использует SQL Server, когда не находит объект, принадлежащий текущему пользователю.

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

Christoffer Hedgate (оригинал: Worst Practice - Not Qualifying Objects With The Owner)
Перевод: Моисеенко С.И.
Оригинал перевода

Ссылки по теме