SQL сентябрь 2004

Содержание номера за Сентябрь 2004 год

Editorial

Правило 80/20

Майкл Оти (Michael Otey)

Разработка программного обеспечения — сложный бизнес. И чем больше и сложнее проект, тем менее вероятно, что он будет завершен вовремя. В больших программных проектах повторно возникающие отсрочки не только распространены, но и вполне ожидаемы. Однако отсрочки перестанут преследовать проект разработки приложения, если вы будете следовать правилу 80/20.

Вильфредо Парето, итальянский экономист, в начале 1900х годов сформулировал правило 80/20, описывающее закон распределения доходов: 20 процентов населения любой нации обладают 80 процентами богатств. Сегодня правило 80/20 применимо к поразительно большому числу различных ситуаций, включая и разработку программ. В мире программного обеспечения первые 20 процентов работы дают 80 процентов результатов, но зато оставшиеся 20 процентов требуют 80 процентов всего времени разработки проекта.

Легко попасть в ловушку, устанавливая слишком оптимистические сроки выполнения проекта исходя из прогресса начальной стадии. Большинство проектов сначала развиваются очень быстро. Этот ранний прогресс может наблюдаться практически во всех подлежащих разработке задачах проекта. Однако планирование сроков завершения проекта на основании этого раннего прогресса неминуемо приведет к двум вещам, которых сотрудники подразделений ИТ должны стремиться избежать: обещать слишком много, а сделать слишком мало. Хотя начало проекта продвигается очень быстро, настоящие трудности встречаются именно на стадии его завершения. Большинство проектов увязает в мириадах окончательных доработок и проблем внедрения. Первые потери возникают, когда быстрый прогресс внезапно прекращается. Неспособность выполнить проект в установленные сроки подрывает веру руководства в ИТ, что в конечном счете приводит к разладу в отношениях между подразделением ИТ и высшим руководством.

В то же время, если обещать меньше, а сделать больше, то можно не только оправдать все ожидания, но и превзойти их. Ваша проблема будет состоять в том, чтобы объяснить свои сдержанные оценки времени выполнения различных задач, но это значительно лучше, чем оправдываться, почему ваша команда не закончила работы вовремя. Следуя правилу 80/20, вы сможете безошибочно полагать, что последние 20 процентов проекта займут в 4 раза больше времени, чем первые 80 процентов. Первая часть типичного цикла разработки начинается с легко исполнимых целей, таких как сбор требований, проектирование баз данных, построение пользовательского интерфейса. Современный инструментарий разработки, такой как Visual Studio, существенно облегчает эти работы. Однако основная масса работ по проекту состоит в том, чтобы дойти до сути проблем, которые необходимо разрешить для подготовки приложения к промышленной эксплуатации. Код конечного продукта качественно отличается от тех кодов, которые встречаются в технических книгах, журналах и Webдокументах Microsoft, где обычно отсутствуют коды обработки ошибок и защитные коды. Коды обработки ошибок не так уж просты и занимают много места, что затрудняет их публикацию. Кроме того, они индивидуальны для каждого приложения. Тем не менее, хорошие коды обработки ошибок и защитные коды позволят вашим приложениям адекватно реагировать на массу непредсказуемых ситуаций, включая ошибочные действия пользователей и неожиданные значения данных.

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

Programming

Ускорение разработки серверного и промежуточного слоев приложений с помощью сценариев SQL

Эймит Фаднис (Ameet Phadnis)

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

Каждая из пяти стандартных хранимых процедур имеет свое предназначение.

Get — для получения списка из таблицы.
GetSingle — для получения из таблицы единичной записи.
Add — для добавления записи в таблицу.
Update — для обновления редактируемой записи.
Delete — для удаления единичной записи.

До того как я написал свой первый генератор хранимых процедур, я обычно создавал все эти процедуры вручную. Поэтому для базы данных из 10 таблиц мне приходилось вручную писать 50 хранимых процедур. Это отнимало время, и я уставал, вновь и вновь выполняя одну и ту же работу. Кроме того, в случае сложных/больших таблиц это приводило к ошибкам типа ID10T (IDIOT), то есть опечаткам. Итак, я начал подумывать о проектировании процедуры SQL, которая будет генерировать все эти хранимые процедуры за меня. Первый генератор хранимых процедур, который я написал, пользовался системными таблицами. Однако системные таблицы могли измениться в будущих версиях SQL Server, но тогда я узнал, что SQL Server предоставляет большинство сведений о данных через представления информационной схемы (information schema view). И я приступил к написанию другого генератора хранимых процедур, который был лучше организован и пользовался представлениями информационной схемы. В этой статье я объясню другой способ доступа к метаданным и приведу в качестве примера генератор процедуры Insert.

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

Тот же принцип построения хранимых процедур применяется и при разработке функций для бизнесприложений и функций слоя доступа к данным, которые обращаются к хранимым процедурам. Ниже приведены некоторые примеры построения генератора хранимых процедур и генератора функций VB.

Бесплатные загружаемые файлы всего генератора хранимых процедур и генератора функций DNN VB можно скачать с сайта www.etekglobalInc.com.

Доступ к метаданным — системные таблицы

SQL Server хранит информацию о данных в системных таблицах. Он также предоставляет для доступа к этой информации системные хранимые процедуры и представления информационной схемы. Применение системных таблиц требует глубоких знаний о столбцах этих таблиц.

Чаще всего используются следующие системные таблицы.

SysObjects: эта таблица хранит информацию обо всех объектах базы данных. Объекты идентифицируются в столбце xtype. Некоторые из значений xtypes таковы: U — пользовательская таблица, P — хранимые процедуры, C — проверяемое ограничение, F — ограничение внешнего ключа и т. д. К примеру, чтобы получить все таблицы базы данных, можно написать такой оператор:

SELECT * FROM sysObjects where xtype = 'U'

SysColumns: эта таблица хранит информацию обо всех столбцах всех таблиц. В таблице SysColumns столбец id определяет взаимоотношения столбцов и таблиц. Кроме того, столбец xtype описывает тип данных столбцов. Он ссылается на таблицу sysTypes. Предположим, что имеется таблица tblUsers. Нам хотелось бы получить список столбцов этой таблицы, их типы данных и размеры. Оператор SQL будет выглядеть примерно так:

SELECT syscolumns.name columnname, systypes.name datatype, syscolumns.length, syscolumns.prec from SysObjects JOIN syscolumns ON SysObjects.id
= SysColumns.id JOIN systypes ON syscolumns.xtype = sysTypes.xtype Where SysObjects.name = 'Users'

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

SELECT name from Systypes

SysForeignKeys: эта таблица содержит информацию, относящуюся к отношениям внешнего ключа в базе данных. Способ ее хранения следует пояснить. В таблице SysForeignkeys имеются 4 важных столбца:

Fkeyid: содержит ID, связанный с таблицей SysObjects. Это значение ID относится к той таблице, которая содержит внешний ключ.
rkeyID: содержащееся в нем значение ID относится к ссылочной таблице, у которой есть первичный ключ.
Fkey: этот столбец действительно ссылается на столбец, который является внешним ключом в таблице. Он связан со столбцом colid таблицы sysColumns.
Rkey: ссылается на первичный ключ в первичной таблице. Он связан со столбцом colid таблицы sysColumns.

Чтобы выбрать все таблицы, которые зависят от таблицы Users, следует воспользоваться следующим оператором выборки:

SELECT ChildTable.tablename, ChildTable.columnname

FROM sysforeignkeys

JOIN

(Select SysObjects.Name tablename, sysObjects.id, sysColumns.name columnname, sysColumns.colid FROM SysObjects

JOIN

sysColumns ON SysObjects.id = sysColumns.id

Where sysobjects.xtype = 'U') PrimaryTable ON sysforeignkeys.rkeyID = PrimaryTable.ID

AND sysforeignkeys.rkey = PrimaryTable.colid

JOIN

(Select SysObjects.Name tablename, sysObjects.id, sysColumns.name columnname, sysColumns.colid

FROM SysObjects

JOIN

sysColumns ON SysObjects.id = sysColumns.id

Where sysobjects.xtype = 'U') ChildTable ON sysforeignkeys.fkeyID = ChildTable.ID

AND sysforeignkeys.fkey = ChildTable.colid

WHERE PrimaryTable.tablename = 'Users'

Доступ к метаданным — системные хранимые процедуры

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

Sp_Tables: эта хранимая процедура возвращает названия всех таблиц. К примеру, чтобы получить названия всех созданных пользователями таблиц в базе данных XYZ, воспользуемся таким оператором SQL:

EXEC sp_tables NULL, dbo, XYZ, "'TABLE'"

Sp_columns: эта хранимая процедура возвращает все столбцы указанной таблицы. Рассмотрим приведенный выше пример. Главным преимуществом этой хранимой процедуры является то, что она скрывает сложность всех соединений, необходимых для получения типов данных и названий столбцов, которую мы наблюдали в разделе системных таблиц. Для получения всех столбцов таблицы Users следует вызвать эту процедуру следующим образом:

Exec sp_columns 'Users'

Sp_datatype_info: эта хранимая процедура возвращает информацию обо всех типах данных. Так выглядит ее синтаксис:

EXEC sp_datatype_info

Доступ к метаданным — представления информационной схемы

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

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

SELECT TABLE_NAME from Information_schema.Tables WHERE TABLE_TYPE = 'BASE TABLE'

Information_schema.Columns: возвращает информацию о столбцах таблицы. Оператор SQL, возвращающий название столбцов, их тип данных и размеры, выглядит так:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'Users'

Для типов данных, имеющих фиксированный размер, таких как int, datetime, CHARACTER_MAXIMUM_LENGTH возвратит неопределенное значение.

Information_schema.TABLE_CONSTRAINTS: возвращает информацию обо всех ограничениях. Пользователи могут получить сведения о конкретной таблице. Ограничения идентифицируются столбцом CONSTRAINT _ TYP E. К примеру, чтобы получить все ограничения таблицы Users, напишите следующий оператор SQL:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Where Table_name = 'Users'

Чтобы получить ограничение первичного ключа для таблицы Users, напишите такой оператор SQL:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Where Table_name = 'Users' AND CONSTRAINT_TYPE = 'PRIMARY KEY'

Information_Schema.CONSTRAINT _COLUMN_USAGE: возвращает информацию о столбце и связанных с ним ограничениях. К примеру, выше мы получали информацию о PRIMARY KEY CONSTRAINT для таблицы Users, но нам хотелось бы увидеть имя столбца. Оператор SQL будет выглядеть так:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME = 'PK_USERS'

Комбинируя Information_schema.Table_constraints и Information_schema._CONSTRAINT_COLUMN_USAGE, получим следующее:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = 'Users' AND CONSTRAINT_TYPE = 'PRIMARY KEY'

Information_schema.REFERENTIAL_CONSTRAINTS: возвращает информацию об ограничениях внешнего ключа. Предположим, что в базе данных имеются две таблицы. Одна — таблица Users, а другая — UserRoles. У таблицы Users имеется столбец UserID, на который делаются ссылкииз таблицы UserRoles. Оператор SQL для получения информации о внешнем ключе пишется так:

SELECT fkey.Table_name, fkey.Column_name FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rkey JOIN Information_schema.REFERENTIAL_CONSTRAINTS Ref on rkey.CONSTRAINT_NAME = Ref.Unique_Constraint_Name JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fkey ON Ref.CONSTRAINT_NAME = fkey.CONSTRAINT_NAME WHERE rkey.Table_Name = 'Users'

Этот оператор даст название таблице и столбцам, которые ссылаются на столбец UserID таблицы Users.

Information_Schema.ROUTINES: Возвращает информацию о хранимых процедурах и функциях. Оператор SQL для получения сведений обо всех хранимых процедурах вашей базы данных имеет вид:

SELECT * FROM Information_Schema.ROUTINES Where Routine_type = 'PROCEDURE'

Information_Schema.PARAMETERS: Возвращает информацию о параметрах хранимой процедуры. Оператор SQL для получения сведений о параметрах хранимой процедуры AddUser такой:

SELECT PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM Information_Schema.PARAMETERS Where Specific_name = 'AddUser'

В этом разделе пояснялось, как применять некоторые из представлений информационной схемы для извлечения информации о базе данных.

Запускаем в работу

Этот раздел содержит два примера. Первый пример объясняет, как создать хранимую процедуру Delete для вашей таблицы, а второй — как построить функцию объявлений VB, используя созданные хранимые процедуры. Вы можете сами создать хранимые процедуры Insert, Update, Get и GetSingle или можете обратиться к разделу ссылок за информацией о загрузке бесплатного кода.

Понимание триггеров INSTEAD OF в Microsoft SQL Server 2000

Дайнеш Марси (Dinesh Murthy)

В данной статье обсуждается понимание триггеров INSTEAD OF в Microsoft SQL Server 2000. Эта тема будет охвачена в полном объеме, а точнее, мы поговорим о том, что представляет собой триггер INSTEAD OF, рассмотрим его синтаксис и простые примеры. Также мы рассмотрим его отличия от обычной реализации триггеров.

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

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

Мы также рассмотрим некоторые аспекты влияния на производительность, специфику и накладные расходы, связанных с реализацией триггеров INSTEAD OF. И в заключение мы обсудим ряд соображений безопасности.

Итак, давайте заглянем в суть дела. Что же именно представляет собой триггер INSTEAD OF? Это новая функциональность, введенная в SQL Server 2000. Фактически, существует и другое новое действие, вызывающее срабатывание триггера, называемое триггерами AFTER. Мы не будем обсуждать триггеры AFTER в этой статье, поскольку их рассмотрение выходит за ее рамки, заинтересованные читатели могут самостоятельно заглянуть в справочную систему SQL Server Books Online для получения дополнительной информации.

Триггеры INSTEAD OF заменяют действие поумолчанию для оператора, к которому приписан триггер. Другими словами, они осуществляют действие, содержащееся в операторе CREATE TRIGGER, вместо выполнения исходных операторов INSERT, UPDATE или DELETE. Итак, вместо фактического выполнения оператора, запускающего триггер, происходит выполнение того, что указано в определении самого триггера. Как видите, это является расширением первоначально существовавших функциональных возможностей триггеров. При этом исходное действие, вызывающее срабатывание триггера, не модифицируется и не изменяется, оно остается как есть, что наряду с триггерами AFTER является двумя новыми функциями, которые можно реализовать.

Other

Устройство User Mode Scheduler в SQL Server 2000

Кен Хендерсон (Ken Henderson)

Кен Хендерсон рассказывает о User Mode Scheduler (UMS) SQL Server 2000, требующий от разработчиков написания эффективного кода, который достаточно часто освобождает ресурсы системы в соответствующих местах. UMS предоставляет больше контроля и позволяет масштабировать сервер лучше, чем это возможно с помощью планировщика (scheduler) Windows.

Вплоть до версии 6.5 SQL Server использовал возможности, заложенные в Windows, для планирования рабочих потоков, переключения между ними и обработки многозадачной работы в целом. Это срабатывало довольно хорошо и позволяло SQL Server извлекать пользу из тяжелых уроков, относящихся к масштабируемости Windows и эффективному использованию процессора. Между версиями 6.5 и 7.0, однако, стало очевидным, что SQL Server начинает биться в «потолок масштабируемости». Его способности обрабатывать тысячи одновременно подключенных пользователей и эффективно масштабировать системы с более чем четырьмя процессорами препятствовал тот факт, что планировщик Windows рассматривал SQL Server как любое другое приложение. Вопреки существующему мнению, в то время SQL Server 6.5 не использовал скрытых API для достижения требуемых уровней масштабируемости. Он применял общие примитивы (primitive) для потоков и их синхронизации, использующиеся во всех многопоточных приложениях Windows и Windows, планировал подключение и отключение рабочих потоков SQL Server к процессору(ам) точно так же, как это делалось для любого другого процесса. Ясно, что подобный, универсальный, подход не являлся наиболее оптимальным решением для высокопроизводительных приложений, подобных SQL Server, поэтому команда разработчиков SQL Server начала искать
способы оптимизировать процесс планирования.

Цели разработки UMS

Некоторые цели были определены в начале этого исследования. Возможности планирования необходимы:

для поддержки волокон (fiber), нового свойства Windows NT 4.0 и абстрактной работы с ними, с тем чтобы ядро обработки данных не нуждалось в отдельном коде для режима потоков (thread mode) и режима волокон (fiber mode);
чтобы избежать причин переключения потока в режим ядра, когда это возможно;
чтобы избежать переключения контекста потока, насколько это возможно;
для поддержки асинхронного ввода/вывода и абстрактной работы с ним таким образом, чтобы ядро машины обработки данных не нуждалось в отдельном коде для версий Windows, не поддерживающих асинхронный файловый ввод/вывод (например Windows 9x и Windows ME).

В результате было решено, что SQL Server 7.0 должен осуществлять свое собственное планирование. После принятия этого решения был создан компонент User Mode Scheduler (UMS). UMS выступает в качестве тонкой прослойки между сервером и операционной системой. Он находится в файле, называемом UMS.DLL, и разработан для поддержки модели программирования, очень похожей на работу с планировщиком потоков Win32 и асинхронной моделью ввода/вывода. Программисты, знакомые с одним, сразу же почувствуют себя «как дома» и с другим. Его основной функцией является поддержка как можно большего количества планируемых процессов SQL Server в пользовательском режиме. Это означает, что UMS пытается обязательно избежать переключений контекста, поскольку они затрагивают ядро. Переключения контекста стоят дорого и ограничивают масштабируемость. В патологических ситуациях процесс может затрачивать больше времени на переключение контекста потоков, чем на саму работу.

(Возврат на основную страницу)