Одним из вопросов, все чаще обсуждаемых мной в последние дни с клиентами или администраторами/разработчиками баз данных, является создание такой политики компании, которая бы описывала ряд стандартов, которым должны следовать при создании хранимых процедур для SQL server. С одной стороны, политика стандартов уровня компании или подразделения не должна быть столь ограничительной или "высеченной на камне", чтобы душить всякий творческий потенциал, который часто необходим для решения требований бизнеса, стоящих перед разработчиками. С другой стороны, она должна обеспечить такие рекомендации, которые ограничивали стиль кодирования таким образом, чтобы он не создавал проблем безопасности, падения производительности или проблем обслуживании в будущем.
Хотя всесторонняя политика является слишком большой, чтобы быть описанной в этой статье, Вы могли бы сделать небольшую электронную книгу только по стандартам Transact-SQL, а я бы с удовольствием обсудил те вопросы, относящиеся к стилю, которые чаще всего вызывают падение производительности на сайтах моих клиентов.
Непосредственно передаваемый запрос по сравнению с хранимыми процедурами
Обычные дебаты в мире разработки связаны с использованием хранимых процедур по сравнению с запросами, непосредственно содержащимися в коде приложения. Хотя эти дебаты ведутся с тех пор, когда появились хранимые процедуры и вероятно будут продолжаться и после того, как я уйду, я считаю, что во многих больших средах с тысячами пользователей и терабайтами данных хранимые процедуры действительно дают выигрыш в производительности по сравнению с запросами.
SQL Server компилирует все операторы хранимой процедуры в единый план выполнения. Это лучше выполняется при обработке непосредственно запроса, но возможности повторного использования и перекомпиляции все же, как мне кажется, немного лучше для хранимых процедур, чем для запросов. Другое большое преимущество хранимых процедур состоит в том, что хранимые процедуры обрабатываются на сервере, предотвращая большие и повторяющиеся передачи данных туда и обратно по сети между клиентами и сервером при решении сложных проблем. Я также полагаю, что хранимая процедура обеспечивает лучший уровень безопасности между приложением или пользователем и таблицами или представлениями, к которым они адресуются. В прошлом, работая с большими системами, я обнаружил, что проще реализовать безопасность, основанную на разрешениях на выполнение хранимых процедур, присвоенных ролям базы данных, чем предоставление разрешений на выборку данных из всех моих таблиц. Наконец, я принадлежу к лагерю, который полагает, что хранимые процедуры обеспечивают буфер обслуживания между приложением и таблицами. Этот буфер дает возможность производить изменения схемы, не требуя непосредственного изменения в приложении, "скрывая" эти изменения в хранимых процедурах. Как показывает опыт, зачастую проще написать скрипт, который будет выполнять поиск и замену имени столбца в тысяче хранимых процедур, чем выполнить поиск во множестве модулей кода, внесения изменений, и необходимости повторной компиляции и переустановки новых модулей.
Использование табличных переменных, временных таблиц и постоянных рабочих таблиц
Использование временных рабочих пространств в SQL Server, прежде всего, достигается четырьмя различными способами: курсорами, табличными переменными, временными таблицами, и постоянными рабочими таблицами. В то время как разработчики должны стремиться уменьшить использование этих временных рабочих пространств, используя способность Сервера SQL выполнять операции над множествами записей вместо построчной обработки с помощью курсоров, зачастую временные рабочие пространства используются по другим причинам в процессе выполнения запроса. Когда дело обстоит именно так, от разработчика требуется понимание назначения курсоров, табличных переменных, временных таблиц и постоянных рабочих таблиц, и только тогда применять их, если такие варианты использования памяти нельзя заменить производной таблицей, коррелирующим подзапросом или оператором UNION.
Использование табличных переменных является новой функциональностью в SQL Server 2000 и обещает уменьшать число операций ввода/вывода, связанных с использованием временных таблиц. Вообще говоря, табличные переменные не всегда предотвращают дисковые операции ввода-вывода, поскольку табличная переменная при увеличении ее размеров все же может использовать базу данных tempdb. Табличные переменные действительно создают проблемы производительности при больших наборах данных и должны использоваться только в тех случаях, когда табличная переменная содержит менее 3000 строк данных. Это ограничение на производительность зачастую уменьшает эффективность табличных переменных в больших системах.
Временные таблицы - полезный и часто используемый инструмент, используемый для создания табличной структуры и при необходимости сохранения данных в памяти. Имена локальных временных таблиц, доступных только в текущем подключении или текущему пользователю, должны начинаться с символа #. Локальные временные таблицы удаляются при отключении пользователя от компьютеров, на которых запущен Microsoft SQL Server, или когда завершается выполнение хранимой процедуры, создавшей эти таблицы.
Имена глобальных временных таблиц, доступных любым подключением или пользователем, должны начинаться с двойного символа # (##). Глобальные временные таблицы видимы любому пользователю после их создания; и они удаляются тогда, когда завершаются все процессы, ссылающиеся на такую таблицу. Рекомендуется, чтобы разработчики следовали некоторым важным рекомендациям при использовании временных таблиц.
* Создать временную таблицу непосредственно перед тем, как это необходимо, и выполнять любые операции с таблицей в непосредственной близости к тому моменту, в который она была создана. Это поможет избежать ненужной перекомпиляции.
* Всегда удаляйте (DROP) локальную временную таблицу, когда она уже не нужна. Рекомендуется удалять таблицу сразу после завершения ее использования или, по крайней мере, в конце хранимой процедуры, что является хорошим стилем программирования, связанным с уборкой мусора. Отказ от удаления временных таблиц может привести к утечке ресурсов памяти, что оказывает отрицательное влияние на производительность системы в целом.
* Включайте в таблицу только те столбцы и строки, в которых Вы фактически нуждаетесь, но не более.
Временные таблицы могут постоянно находиться в основной памяти, что обеспечивает более быстрый доступ к хранящимся в них данным по сравнению с извлечением той же самой информации с диска. Эта способность временных таблиц храниться в памяти зависит от объема данных, находящихся в таблице. По мере роста размеров временных таблиц и поскольку на использование памяти сервера оказывает давление множественность выполняемых процессов и т.д., способность SQL Server хранить всю временную таблицу в памяти уменьшается. Это заставляет SQL Server больше обращаться к tempdb для сохранения данных, что может увеличить размер tempdb и вызвать конфликты в этой базе данных.
Временные таблицы долго ассоциировались с плохим выполнением запросов. Хотя это и обычно для временных таблиц, что обусловлено наличием дисковых операций ввода/вывода при работе с временными объектами и конфликтов за ресурсы tempdb, вызываемых многочисленными временными объектами, они, как и курсоры, могут оказаться выгодными при определенных обстоятельствах. Не стоит принижать роль использования временных таблиц. Они оказываются предпочтительными для больших наборов данных по сравнению с табличными переменными. Однако это справедливо только в том случае, когда постоянные рабочие таблицы не могут использоваться из-за конфликтов с данными (когда многочисленные пользователи пытаются использовать одну и ту же постоянную таблицу для работы с различными данными) или когда количество данных, помещаемых во временную таблицу, достигает сотен или тысяч строк.
Когда большие наборы данных требуется где-то временно сохранять, вместо временной таблицы часто лучше использовать постоянную рабочую таблицу.
Постоянные рабочие таблицы являются хорошей альтернативой табличным переменным и временным таблицам для процессов, которые не должны использовать рабочую таблицу совместно с другими процессами. Эти постоянные рабочие таблицы следует создавать с помощью оператора SELECT…INTO, чтобы избежать журнализации, которая выполняется при использовании оператора INSERT.
См. BOL: Temporary Tables, and CREATE TABLE.
Возвращение большего количества данных, чем это необходимо (например, SELECT * …),
Чтобы улучшить производительность запроса, следует ограничивать число строк и столбцов, возвращаемых запросом. По мере роста размера таблицы обязательно следует включать в запросы предложение WHERE с высокой степенью селективности, чтобы уменьшить число строк до управляемого размера. Кроме того, чтобы уменьшить сетевой трафик и потребности в памяти для возвращенного набора данных, число столбцов, возвращаемых запросом, должно быть ограничено только теми столбцами, которые необходимы для выполнения операций запроса.
Сокращение числа столбцов, возвращаемых отдельными запросами, увеличивает возможность того, что для таких запросов может быть генерировано покрытие индексами. Покрытие индексами может заставить оптимизатор отказаться от дорогостоящих поисков закладок (bookmark lookups). Часто при настройке хранимой процедуры сокращение числа столбцов, возвращаемых запросом, устраняет проблемы произвдительности, связанные с поисками закладок.
Дополнительная выгода сокращения числа столбцов, возвращаемых запросом, при отказе от SELECT * состоит в изоляции запроса от изменений схемы таблиц, на которые есть ссылки в запросе. Дополнительные столбцы, возвращаемые запросами, которые не учтены некоторыми клиентскими приложениями, могут вызывать вывод на экран клиента сообщений об ошибке.
Хранимые процедуры, имена которых начинаются с sp_
Настоятельно рекомендую, чтобы Вы не создавали никаких хранимых процедур, используя sp_ как префикс. Сервер SQL всегда ищет хранимую процедуру, начинающуюся sp_, в следующем порядке:
1. Хранимая процедура в базе данных master.
2. Хранимая процедура, основанная на любых предоставленных квалификаторах (имя базы данных или имя владельца).
3. Хранимая процедура, используя dbo в качестве владельца, если Вы не указали другого владельца.
Поэтому, даже если разработанная пользователем хранимая процедура с префиксом с sp_ существует в текущей базе данных, сначала всегда проверяется база данных master. Это справедливо, даже если хранимая процедура квалифицирована именем базы данных. Следует также отметить, что, если какая-нибудь разработанная пользователем хранимая процедура имеет имя, совпадающее с именем системной хранимой процедуры, то разработанная пользователем хранимая процедура никогда не будет выполняться.
Другая проблема, связанная с префиксом sp_ в имени пользовательской хранимой процедуры, заключается в том, что вызов хранимой процедуры первоначально пропустит скомпилированный план выполнения в кэше, что может вызвать задержку из-за блокировок компиляции.
Намеки (хинты - hint) оптимизатору
Оптимизатор SQL Server на основе оценки стоимости динамически определяет стратегию обработки запроса, основанную на текущей структуре таблицы/индекса и данных. Это динамическое поведение может быть преодолено с помощью хинтов оптимизатора, забирая некоторые решения из рук оптимизатора и инструктируя его использовать определенную стратегию обработки. Это делает поведение оптимизатора статическим, и не позволяет ему динамически обновлять стратегию обработки при изменении структуры таблицы или индекса, а также изменения данных.
Разработчикам не следует помещать хинты оптимизатора в свои коды. Они должны позволить оптимизатору SQL Server определять наилучший способ выполнения запроса и не пытаться навязать ему тот вариант, который они полагают лучшим.
См. BOL: Query Tuning Recommendations, Hints, Locking Hints, and SET TRANSACTION ISOLATION LEVEL.
Использование Системных Объектов в Коде
Часто разработчики испытывают желание обратиться к системным таблицам для поиска информации о базе данных. Поскольку эта практика не приносит никакого дополнительного прироста производительности, Microsoft постоянно заявляет, что они сохраняют за собой право изменять системные таблицы без предупреждения. Эта необъявленная возможность Microsoft может сделать любые разработанные пользователем хранимые процедуры или запросы неработающими после того, как будет установлен очередной сервиспак, фикс или выполнен переход к новой версии. Разработчикам следует воздерживаться от доступа к системным таблицам в промышленном коде.
Microsoft реализует ряд представлений в стандарте ANSI на протяжении нескольких последних версий SQL Server, чтобы помочь разработчикам достичь их целей в получении информации из системных таблиц SQL Server, непосредственно не обращаясь к самим таблицам. Эти представления ANSI гарантировано будут работать при установке сервиспаков, фиксов и переходе на новую версию. Разработчики должны использовать эти представления ANSI всякий раз, когда им необходимо обратиться к системным объектам.
Имеющиеся представления схемы
- CHECK_CONSTRAINTS
- COLUMN_DOMAIN_USAGE
- COLUMN_PRIVILEGES
- COLUMNS
- CONSTRAINT_COLUMN_USAGE
- CONSTRAINT_TABLE_USAGE
- DOMAIN_CONSTRAINTS
- DOMAINS
- KEY_COLUMN_USAGE
- PARAMETERS
- REFERENTIAL_CONSTRAINTS
- ROUTINE_COLUMNS
- ROUTINES
- SCHEMATA
- TABLE_CONSTRAINTS
- TABLE_PRIVILEGES
- TABLES
- VIEW_COLUMN_USAGE
- VIEW_TABLE_USAGE
- VIEWS
Пример запроса к системной таблице
if exists (select * from dbo.sysobjects where id = object_id(N'[tmplnSameweek]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
Drop table [tmplnSameweek]
Пример запроса к представлению ANSI
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tmplnSameweek' AND TABLE_TYPE = 'BASE TABLE')
DROP TABLE dbo. TmplnSameweek
Использование системных объектов станет еще большей проблемой, когда будет выпущен релиз SQL Server 2005 и разработчики обнаружат, что часть написанного ими кода, который они писали в предположении неизменности столбцов системных объектов, вызывает ошибки, поскольку эти столбцы были удалены или переименованы. Лично я сталкиваюсь с этим в некоторых запросах, с помощью которых я получал экспертные оценки. В течение долгого времени я выяснял, какие биты находятся в некоторых из этих зарезервированных столбцов, и использовал это знание, чтобы получить дополнительную информацию для моих оценок. Эти столбцы исчезли, и мне придется потратить дополнительное время, чтобы обновить мои оценочные скрипты, отказываясь от этой дополнительной информации в преддверии выхода версии 2005.
См. BOL: Information Schema Views
Использование полностью квалифицированных имен
В SQL Server 7.0 и 2000 только одна копия любого конкретного плана хранимой процедуры обычно находится в кэше в данный момент времени. Осуществление этого требует преобразования в последовательную форму (сериализации) некоторых частей процесса компиляции, и эта синхронизация достигается, в частности, посредством блокировок компиляции. Если одновременно на многих подключениях запускается одна и та же хранимая процедура, и всякий раз, когда она запускается, должна быть затребована блокировка компиляции на эту хранимую процедуру, то возможно, что идентификаторы системного процесса (SPIDs) могут начать блокировать друг друга, поскольку каждый из них пытается затребовать наложение эксклюзивной блокировки компиляции на данный объект.
Перекомпиляция хранимой процедуры является одним из объяснений блокировок компиляции, накладываеых на хранимую процедуру или триггер. Еще одной причиной возникновения блокировок компиляции является выполнение следующих условий:
* Пользователь, который выполняет хранимую процедуру, не является ее владельцем.
* Имя хранимой процедуры не является полностью квалифицированным с указанием имени владельца объекта.
Если существующий план найден, SQL Server повторно использует находящийся в кэше план и фактически не компилирует хранимую процедуру. Однако отсутствие указания на владельца вынуждает SQL выполнять второй поиск в кэше и запрашивать эксклюзивную блокировку компиляции, прежде чем обнаружится, что существующий кэшированный план выполнения может быть повторно использован. Запрос на блокировку и выполнение поиска, а также другая работа, которая необходима, чтобы добраться до выполнения, может вызвать задержку, вполне достаточную для того, чтобы блокировки компиляции привели к тупиковой ситуации. Это особенно справедливо для большого числа пользователей, не являющихся владельцами хранимой процедуры, когда они одновременно запускают ее на выполнение, не указывая при вызове имя владельца. Отметьте, что, даже если Вы не видите, SPIDs, которые ожидают блокировок компиляции, отсутствие квалификации владельца может вызвать задержку выполнения хранимой процедуры и чрезмерное использование времени центрального процессора.
Другая проблема, возникающая при использовании не полностью квалифицированных имен объектов, состоит в том, что когда многочисленные пользователи выполняют хранимую процедуру, для каждого пользователя создаются дополнительные планы выполнения, если объекты не полностью квалифицированы. Это может вызвать дополнительную нагрузку на процедурный кэш, если большое количество планов выполнения должно быть создано, чтобы удовлетворить многочисленных пользователей.
Согласование столбцов в запросе с индексами
Как и при согласовании столбцов индекса с наиболее часто используемыми столбцами в предложении WHERE, разработчики должны стремиться сопоставлять столбцы поиска в запросе с крайними слева столбцами в индексе, когда это возможно. Индексы бесполезны, если крайний левый столбец в предложении WHERE является крайним правым столбцом индекса. Создание индекса и разработка предложения WHERE должны быть согласованы в процессе создания кода, а не рассматриваться как два независимых этапа работы.
См. BOL: Designing an Index.
Предложение DISTINCT
Не используйте предложение DISTINCT, если оно не является абсолютно необходимым в запросе. Включайте DISTINCT только тогда, когда вы знаете, что будут возвращены дубликаты строк, и вам необходимо избавиться от них. Если оптимизатор не может подобрать индекс для выполнения команды, предложения DISTINCT могут вызвать создание промежуточной рабочей таблицы, которая может дорого обойтись с точки зрения производительности.
См. BOL: Eliminating Duplicates with DISTINCT, and Using DISTINCT.
UNION против UNION ALL
Если Вас не беспокоят дубликаты данных или же вы знаете, что данные не будут содержать дубликатов, используйте оператор UNION ALL, а не оператор UNION. Оператор UNION должен выполнить сортировку или хэширование результирующего набора перед его возвращением, что значительно снижает производительность запроса.
См. BOL: Combining Results with UNION, Guidelines when Using UNION, UNION Operator, and Using UNION with Other Transact-SQL Statements.
Предикат IN
Если запрос содержит предикат IN, который представляет собой список постоянных значений, упорядочивайте значения на основе частоты их появления во внешнем запросе. Так как вычисление предиката будет прекращаться, как только найдется совпадение с любым из списка значений, перемещая те из них, которые появляются чаще в наборе данных, вы будете ускорять выполнение запроса и возможно уменьшите количество ЧТЕНИЙ, выполняемых при выполнении запроса.
См. BOL: Subqueries with IN, IN, List Search Conditions, and Subquery Fundamentals.
Избегайте неявных преобразований типов данных
При использовании переменных для фильтрации данных в предложении WHERE, следует избегать сравнений на двух различных типах данных, которые вынудят SQL Server выполнять неявное преобразование типа данных. Если возможно, опишите переменную с типом данных, соответствующим типу данных столбца, по которому выполняется фильтрация. Отрицательным примером может служить установка для столбца типа данных INTEGER, а затем его использование для фильтрации по типу данных float или decimal. Неявное преобразование дорого обходится и может вызвать значительную нагрузку при больших наборах данных.
См. BOL: Data Type Conversion, CAST and CONVERT, and Conversion Functions.
Использование Курсора
Если возможно, избегайте использования курсоров SQL Server. Курсоры вообще потребляют много ресурсов SQL Server, а также снижают производительность и масштабируемость ваших приложений. Если Вам необходимо выполнить построчные операции, постарайтесь найти другой метод решения задачи. Некоторые варианты состоят в том, чтобы вместо этого выполнить задачу на клиенте, использовать временные или производные таблицы, использовать коррелированные подзапросы, а также оператор CASE. В случае необходимости можно использовать множественные запросы.
Чаще чем курсоры, применяются методы, не использующие курсоры, которые могут решить те же задачи, что и курсор SQL Server.
Если Вы действительно полагаете, что курсор необходим, пробуйте уменьшить число обрабатываемых записей, чтобы минимизировать перегрузку, связанную с использованием курсора. Один из способов сделать это состоит в том, чтобы переместить записи, которые должны быть обработаны, сначала во временную таблицу, а уже потом создать курсор для использования записей из временной, а не из оригинальной таблицы. Это, конечно, предполагает, что подмножество записей, которые будут вставлены во временную таблицу, существенно меньше, чем число записей в оригинальной таблице. Чем меньше число обрабатываемых записей, тем быстрее будет выполнен обход курсора.
Если Вы не имеете никакого выбора, кроме как использовать курсоры в вашем приложении, постарайтесь разместить базу данных tempdb в SQL Server на отдельном физическом устройстве для достижения лучшей производительности. Это важно потому, что курсоры используют tempdb для временного хранения своих данных. Чем быстрее ваш дисковый массив, тем быстрее будет ваш курсор.
Кроме того, если Вы не имеете другого выбора, кроме как использовать курсор вашего приложения на стороне сервера, старайтесь использовать FORWARD-ONLY или FAST-FORWARD, READ-ONLY курсор. Работая с обходимыми в одном направлении данными только для чтения, используйте опцию FAST_FORWARD вместо опции FORWARD_ONLY, поскольку она имеет внутреннюю оптимизацию производительности по скорости. Этот тип курсора создает наименьшую нагрузку на SQL Server. Если Вы не можете использовать курсор FAST_FORWARD, то рассмотрите возможность применения следующих курсоров в указанном порядке, пока не найдете тот, который подойдет вам наилучшим образом. Порядок определяется характеристиками производительности от самого быстрого к самому медленному: dynamic, static и keyset.
Использование курсоров может уменьшить параллелизм и привести к ненужным захватам и блокировкам. Поможет избежать этого использование курсора READ_ONLY, если он применим. Если же вам требуется выполнить обновления, попробуйте использовать опцию курсора OPTIMISTIC, чтобы уменьшить блокировки. Старайтесь избегать опции курсора SCROLL_LOCKS, который снижает параллелизм.
Если создаваемая транзакция содержит курсор (старайтесь избегать этого при любой возможности), убедитесь, что число строк, изменяемых курсором, является небольшим. Это связано с тем, что на модифицируемые строки могут быть наложены блокировки до завершения или прерывания транзакции. При увеличении числа модифицируемых строк возрастает число блокировок, что увеличивает вероятность конфликта блокировок на сервере, пагубно сказывающегося на производительности.
Доступные для поиска аргументы
Способность повышения производительности запроса за счет надлежащей индексации зависит от способности оптимизатора использовать эти индексы. Условие поиска, используемое для столбца или столбцов в предложении запроса WHERE , определяет, может оптимизатор использовать индексы или нет. Разработчики должны стремиться создавать запросы, которые являются SARGable. SARG или поисковый аргумент является предложением запроса, которое оптимизатор может использовать в сочетании с индексами, созданными на таблицах поиска, чтобы выполнить фильтрацию результирующих наборов, возвращаемых запросами.
Оптимизатор запросов SQL Server имеет небольшой список условий, при которых оптимизатор не сможет использовать индексы при построении плана выполнения запроса. Следует избегать следующих ситуаций.
1. Выражения в поисковых столбцах:
- Математические операции, например: qty+1> 100
- Функции даты/времени, например: DATEPART (mm, Datecolumn) = 5
- Строковые функции, например: LEFT(Column,2) = 'GR'
2. Некоторые операторы LIKE, например: LIKE '%'
3. NOT LIKE, IS NOT NULLS, and NOT EXISTS
4. Операции CONVERT и CAST
Комментарии
Комментирование исходных кодов всегда является хорошей практикой программирования. Разработчики должны включать комментарии в свой исходный код, чтобы объяснить назначение кода или логики следующей ниже программы или программного блока. Используйте комментарий в стиле /* … */, для комментирования блока кода, или "--" (два дефиса), для комментирования одной строки. Хотя вы сегодня легко понимаете назначение фрагмента кода, другим это может оказаться непонятным. Или, в чем я часто убеждаюсь, что, понимая то, что я сделал два года назад, при пересмотре фрагмента кода с целью внести изменения, я забывал, почему применил тот или иной прием. Спустя годы, я уяснил для себя одну вещь, которая неизменно оказывается справедливой для всех компаний, с которыми я работал, - разработчики приходят и уходят. Работать с кодами, которые Вы никогда не видели прежде, было бы намного легче, и незначительная логическая ошибка или программная проблема разрешалась быстрее, если бы разработчик потратил несколько мгновений и объяснил большие фрагменты кода.
Например:
/* комментарий для цикла while */
WHILE (…)
BEGIN
Statement 1
Statement 2
-- ROD 12/12/2000 комментарий для одной строки, почему я что-то изменил
Statement 3
END
Другой момент, который следует рассмотреть, это включение заголовочного комментария для каждой хранимой процедуры, чтобы разъяснить использование процедуры для будущих разработчиков. Следующий пример блока комментария следует использовать как структуру заголовка во всех хранимых процедурах.
/********** Хранимая процедура или имя запроса: имя хранимой процедуры ***
** Имя файла: имя файла, содержащего код запроса
** Описание: Описание хранимой процедуры
**
**
**
**
**
** Безопасность: пользователь dbo. Роль XXXX_User имеет разрешение EXECUTE.
** Пользователи не имеют разрешения на select, update, insert или delete для
** доступа к таблицам.
**
** Разрешения устанавливаются скриптом, прилагаемым к файлу хранимой процедуры
**
** Возврашаемые значения: @intErrorCode
**
** Вызов: dbo.sp_name
**
**
** Вызовы: dbo.sp_name, и т.д.
**
**
** Целевая структура:
** База данных: имя базы данных
** Таблица: имя таблицы ** Столбцы: Column1 SMALLINT
** Column2 VARCHAR
** и т.д..
**
** Исходная структура:
** База данных: имя базы данных
** Таблица: имя таблицы
** Столбцы: Column1 SMALLINT
** Column2 VARCHAR
** и т.д.
**
** Автор: John Doe
** Дата: 10/28/04
********************************
** История изменений
********************************
** Дата: Автор: Описание:
** -------- ---------- -------------
** 03/28/02 John Doe Исходная версия.
** 04/03/02 John Doe Изменение структуры запроса,
** вызванное контролем изменений #x
**
***********************************/
См. BOL: Using Comments, -- (Comment), and /*...*/ (Comment)
Длина столбца таблицы
Разработчики должны стремиться поддерживать столбцы как можно короче. Рассмотрите возможность использование INTEGER вместо DECIMAL, NUMERIC или BIGINT, SMALLDATETIME вместо DATETIME, SMALLINT или TINYINT вместо INTEGER, SMALLMONEY вместо MONEY и BIT в качестве флага вместо INTEGER.
Использование типов данных меньшей размерности позволит увеличить эффективность хранения данных. Меньшие типы данных эквивалентны меньшим размерам строк, что позволяет разместить больше строк на странице данных и соответственно уменьшить число необходимых страниц. Это не только позволит экономить дисковое пространство, но и увеличит производительность за счет меньшего числа считываемых страниц при выполнении запроса и необходимого объема памяти для их размещения. Кроме того, это уменьшает объем информации, посылаемой клиенту по запросу.
Выбор типов данных часто диктуется требованиями клиента и первоначальным созданием базы данных. Создатели базы данных и разработчики должны хорошо ориентироваться в имеющихся типах данных, чтобы эффективно выбирать их для определения столбцов.
См. BOL: Field Length, Data Types, Data Type Precedence, Precision, Scale, and Length, and Data Type Synonyms
Ключи кластеризованного индекса
Обычно для кластеризованных индексов следует использовать маленькие единичные столбцы. Большие ключи или многостолбцовые ключи на кластеризованном индексе могут серьезно повлиять на производительность операторов INSERT и размер некластеризованных индексов, поскольку они должны ссылаться на ключ кластеризованного индекса.
См. BOL: Estimating the Size of a Table with a Clustered Index, Clustered Indexes, Using Clustered Indexes, and CREATE INDEX
Заключение
С написанием каждого документа стандартов, будут появляться исключения к пунктам, содержавшимся в этом документе, в процессе разработки одного или более программных проектов. Пункты, противоречащие "лучшим методам", содержащимся в документе стандартов, представляют собой исключения, и пока имеется мало случаев, в которых эти исключения необходимы, в большинстве случаев пункт может быть переделан, чтобы соответствовать "лучшим методам". Разработчики и администраторы баз данных должны свободно обсуждать проблемы, которые им кажутся исключениями из "лучших методов" принятой политики, ПРЕЖДЕ ЧЕМ они фактически включат работу в некоторый код и, соответственно, допустят создание исключение, если это потребуется. Если исключение гарантировано, тогда необходим простой комментарий, написанный выше кода, объясняющий, почему это исключение было сделано, чтобы предотвратить вопросы, возникающие при пересмотре кода.
Исключения, которые являются обычными в среде некоторой компании, могут быть включены в политику, чтобы стать "Лучшими методами", продвигаемыми компанией. Не все среды подобны и то, что не приемлемо в одной компании, возможно, необходимо принимается в другой в силу обстоятельств, не управляемых отдельными администраторами или разработчиками баз данных. Часто администраторы базы данных и разработчики должны делать допущения на проект базы данных, что бизнес требования могут создать ситуацию, когда код выйдет из обычно принимаемых стандартов или исключений, что просто обусловлено ограничениями, необходимыми для чрезвычайной гибкости в хранимой процедуре. Большинство "лучших методов", которые являются общепринятыми сегодня, подходит для большинства компаний, однако, при создании любой политики необходима гибкость.
Randy Dyess (оригинал: Common Transact-SQL Performance Coding Errors)
Перевод: Моисеенко С.И.
Оригинал перевода