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

Содержание номера за Март 2009 год

SQL Server

Март 2009
№ 3 (99)

Editorial

Майкл Оти

Открытый код для SQL Server на CodePlex

DB Design & Warehousing

Джо Cелко

Временные (темпоральные) типы данных в SQL Server

Programming

Адам Мэкэник

Практикум. Основы компонента Service Broker: углубленное изучение. Часть 1

Other

А. Гладченко

Автоматическое управление диапазонами Identity в репликации слиянием

Открытый код для SQL Server на CodePlex

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

Еще до выхода SQL Server 2005, Microsoft начала постепенно перекладывать примеры БД, которые ранее входили в дистрибутив, на сайт CodePlex, связанный с проектом открытого кода, организованного Microsoft www.codeplex.com. На сайте можно теперь найти массу полезных вещей. Как непосредственно связанных с SQL Server, так и не имеющих к нему прямого отношения. Мне показались интересными следующие семь проектов.

1. AdventureWorks и AdventureWorksDW

Для тех, кто работает с SQL Server, первая остановка на CodePlex обычно связана с загрузкой примеров баз данных. Это AdventureWorks и AdventureWorksDW для SQL Server 2005 и SQL Server 2008 помещены на CodePlex в 32­bit и 64­bit вариантах.

2. AdventureWorks StoreFront

Собственно AdventureWorks представляет собой серверную часть приложения и демонстрирует только половину картины. Для получения полного представления, нужно скачать и клиентскую часть, построенную в виде веб­приложения AdventureWorks StoreFront.

3. Работа с большими бинарными объектами

Работа с большими бинарными объектами (LOB) всегда была непростым делом, возможно, потому что он отличается от стандартных приемов работы с реляционными данными, к тому же Microsoft постоянно меняет принципы работы с данными этого типа. На сайте можно найти несколько примеров того, как следует работать с типом varbinary(max) из кода хранимых процедур, см. страницу LOB на CodePlex.

4. Анализ производительности MDX-кода

Этот инструмент позволяет увидеть, как отдельные компоненты MDX запроса влияют на общую производительность. Для использования этого инструмента просто подключитесь к базе данных и введите MDX­запрос. После анализа MDX Script Performance Analyser выведет информацию о распределении времени для индивидуальных компонентов запроса.

5. Open DBDiff

Open DBDiff — утилита для определения различий между двумя БД SQL Server. Этот инструмент сравнивает схему двух БД и создает код, который способен привести их в одинаковое состояние. Open DB­Diff может синхронизировать таблицы, поля таблиц, индексы, обычные и XML, пользовательские типы данных, триггеры, функции, хранимые процедуры и пр. (В состав дистрибутива SQL Server 2005/2008 входит утилита table­diff.exe, которая выполняет сравнение содержимого таблиц и создает код, который синхронизирует данные в таблицах. — Прим. ред.)

6. Примеры SQL Data Services

Если вы не хотите остаться позади остальных при изучении cloud computing, рекомендую скачать примеры SQL Data Services (SDS), которые иллюстрируют, как работать с приложениями, исполняющимися в «облаке» (Microsoft cloud­based SDS). Вы найдете примеры SDS­кода на странице SDS­сайта.

•          CodePlex (http://www.codeplex.com/MSFTDB­Prod­Samples)

•          StoreFront (http://www.codeplex.com/MSFTEEProd­Samples)

•          LOB (http://www.codeplex.com/­MSFTEngProd­Samp­les/­Wiki/View.aspx?title=SS2005%21Large%20­Bi­na­ry­%20Object%20%28LOB%29%20Hand­ling&re­fer­ring­Title=Home)

•          Analyser (http://www.codeplex.com/mdxscriptperf)

•          Open DBDiff (http://www.codeplex.com/Open­D­Biff)

•          cloud­based (http://msdn.micro­soft.com/en­us/­sql­ser­ver/­dataservices/default.aspx)

•          SDS page (http://www.codeplex.com/ssdsexamples)

Временные (темпоральные) типы данных в SQL Server

Джо Cелко (Joe Celko)

В первой серии статей, посвященных приемам работы с временнми данными в SQL, Джо Селко рассматривает темпоральные (связанные со временем) типы данных SQL и мучительно пытается разобраться с тем фактом, что, невзирая на наличие в SQL стандартов ANSI/ISO для операций со временем, в продукции каждого поставщика есть какие-то отличия. Он разъясняет тайны таких вещей как часовые пояса, декретное (законодательно установленное) время, UTC, CUT, GMT, CE, DST и EST.

Среди основных языков программирования SQL единственный имеет встроенные типы данных для работы со временем. На самом деле, я не могу представить себе ничего иного, кроме как языки электронных таблиц, где существуют темпоральные типы данных! Большинство SQL­продуктов имело какие­либо временные типы данных до того, как первые их официальные версии были определены в стандарте SQL­92. Учитывая то, насколько даты важны для коммерческой работы, вы решили бы, что в COBOL к настоящему времени что­то бы имелось. В начале этого десятилетия у нас даже была «Проблема 2000» («Y2K Cri­sis») с кучей книг и статей о проблемах хранения только двух цифр года в датах в компьютерных данных. Я был одним из первых, кто писал об этом в 1981 году (INFORMATION SYSTEMS NEWS, 9 февраля, «Father Time Software Secrets Allows Updating of Da­tes»), и даже был процитирован в VANITY FAIR.

Наша эра

Календарь нашей эры (Common Era calendar) отличается нерегулярностью, и единственной стандартной единицей времени является секунда; годы, месяцы, недели, часы, минуты и т. п. не входят в состав метрической системы, хотя упоминаются в стандартах ISO как условные обозначения.

Примечание-Старые аббревиатуры AD (Anno Do­mini — латинский эквивалент выражения «после Рождества Христова») и BC («до пришествия Христова») в стандартах ISO были заменены на CE для нашей эры («Common Era») и BCE — до нашей эры («Before Com­mon Era») во избежание отсылок к религии. Что удивительно, единственная известная мне религиозная группа, использующая в своей литературе сокращения CE и BCE, — это Церковь Иисуса Христа святых Последнего дня (мормоны).

Сегодня, чтобы преобразовать ваши данные в соответствие со стандартом ISO­8601 «Элементы данных и форматы для обмена информацией — Обмен информацией — Представление дат и времени» («Data Elements and Interchange Formats — Infor­ma­tion Interchange — Representation of Dates and Times») как корпоративным стандартом и стандартом электронного обмена данными в управлении, торговле и на транспорте (EDIFACT) для электронных (EDI) документов, вам требуется установить программу. Это формат «гггг­мм­дд», который входит в состав стандартного SQL и войдет в другие стандартные языки программирования, когда в них добавятся темпоральные типы данных.

Полная метка времени в соответствии с ISO­8601 может быть либо местным временем, либо временем UTC. UTC — это обозначение для «универсального координированного времени» («Universal Coordinated Time»), заменившего GMT (не являвшееся стандартом в течение десятилетий), расшифровывающееся как «среднее гринвичское время» («Greenwich Mean Time») (если вы слушаете CNN, вы привыкли слышать термин UTC, если же вы слушаете BBC, вы привыкли к термину GMT).

В 1970 году интернациональной консультативной группой технических экспертов в Международном союзе электросвязи (International Telecommunication Union; ITU) была разработана система координированного всемирного времени. ITU поняли, что чтобы свести путаницу к минимуму, наилучшим решением было определить к использованию единую во всех языках аббревиатуру. Двумя альтернативными предложенными оригинальными аббревиатурами для «универсального координированного времени» были CUT (англ. Coordinated Universal Time) и TUC (фр. temps universel coordonn). Вариант UTC был выбран и как компромисс между французскими и английскими предложениям, и потому, что C в конце больше походит на индекс в UT0, UT1, UT2, а запись в математическом стиле — это всегда наиболее интернациональный подход.

Технически универсальное координированное время не есть то же самое, что среднее гринвичское время. GMT — это 24­часовая система астрономического времени, основанная на местном времени в английском Гринвиче. GMT может рассматриваться как эквивалент универсального координированного времени, когда не важны доли секунды. Как бы там ни было, согласно международному соглашению термин UTC рекомендован для всех хронометрических применений общего характера, а использование термина GMT не приветствуется.

Другая проблема состоит в том, что, помимо часовых поясов, нам следует также побеспокоиться о «декретном времени» («lawful time»). Это технический термин для обозначения времени, назначенного законом для коммерческой деятельности. Как правило, под этим имеется в виду, используете ли вы летнее/зимнее время. Девятого декабря 2007 года президент Венесуэлы Уго Чавес принял решение создать собственный уникальный часовой пояс, переведя часы на полчаса назад на постоянной основе. Это сделано без надобности, только лишь для того, чтобы поместить свою страну в часовой пояс, отличный от того, в котором расположены Соединенные Штаты. Схожая политическая чепуха проводилась и прежде, но обычно это влечет за собой переходы на летнее/зимнее (DST) время в течение года.

Необходимость применения времени UTC внутри базы данных и декретного времени для отображения и ввода не удовлетворена, в большинстве случаев, до сих пор. Системы электронного обмена данными (EDI) и реплицированные базы данных для сравнения временных меток должны использовать время UTC. В распределенной системе дата без указания часового пояса двусмысленна. Транзакция, созданная 1995­12­17 в Лондоне, может быть новее, нежели транзакция, созданная 1995­12­16 в Бостоне.

Временные типы данных стандартного SQL

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

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

Общепринятые термины и соглашения, относящиеся ко времени, также создают путаницу. Мы говорим о «часах», и используем этот термин, чтобы обозначить определенную точку дневного цикла («Поезд прибывает в 13 часов»), либо имеем в виду интервал времени, не связанный с другой единицей измерения («Поезд доберется туда за три часа»); число дней в месяце не одинаково; число дней в году не одинаково; недели не связаны с месяцами и т. п.

Для работы на коммерческой основе нам нужен тип данных DATE. Наличие типов данных TIME и TIME­STAMP также очень удобно. В стандартном SQL имеется набор типов данных для обозначения даты и времени (DATE, TIME и TIMESTAMP) и интервалов (INTER­VALs: DAY, HOUR, MINUTE и SECOND — секунда с десятичными долями).

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

До появления SQL Server 2008 работа с этими вещами была ужасна. SQL Server основывался на часах первой системы UNIX, где число с плавающей точкой хранило «данные даты» впереди и «данные времени» в конце. В основе всего этого лежали физические отсчеты аппаратного таймера на одной машине. Сегодня у нас есть наручные часы и будильники, которые сами настраивают себя с помощью радиосигналов точного времени Национального Института стандартов и технологий (NIST). Вообще говоря, существует два способа внутреннего представления временных данных. «Представление UNIX» основано на хранении одного длинного целого либо слова из 64 или более битов, в котором отсчитываются такты компьютерных часов, начиная с базовых даты и времени. Другое представление я буду называть «методом COBOL», поскольку оно использует раздельные группы разрядов для года, месяца, числа, часов, минут и секунд.

Способ Unix прекрасно подходит для вычислений, но ядро СУБД должно выполнять преобразования из внешних форматов, внутреннего формата и наоборот. Формат COBOL — противоположность; он хорош для отображения, но менее эффективен при расчетах.

К примеру, чтобы сократить TIMESTAMP до просто даты со временем, установленным в 00:00 часов, в SQL Server вы можете воспользоваться преимуществом внутреннего представления и написать:

CAST (FLOOR (CAST (mydate AS FLOAT)) AS datetime)

Точно так же следующий день можно найти при помощи такого выражения:

CAST (CEILING (CAST (mydate AS FLOAT)) AS datetime)

Отображаемые и внутренние форматы даты

Порядковые форматы даты ISO описаны в ISO­2711­1973. Их формат — это четырехзначный год, за которым следует цифровое обозначение дня в году (001­366). Год может быть усечен до года внутри века. Форматы даты ANSI описаны в ANSI X3.30­1971. Их форматы охватывают стандарт ISO, но добавляют четырехзначный год, за которым следует двухзначный месяц (01­12), после которого идет двузначный день внутри месяца (01­31). Этот вариант называется календарным форматом даты. Стандартный SQL использует, конечно, только формат «гггг­мм­дд». Порядковый формат лучше подходит для арифметических действий с датами; календарный формат более удобен для отображения. Мы вернемся к нему, когда будем обсуждать таблицу Calendar.

Многие программисты продолжают использовать какого­либо рода формат год­в­веке. В старые времена допускалось экономить место, когда такие вещи имели значение (т. е. когда на перфокартах было только 80 колонок). Программисты предполагали, что им не потребуется различать 1900 и 2000 годы, поскольку они слишком удалены друг от друга. Старые программы на COBOL, выполнявшие арифметику дат над этими форматами, возвращали ошибочные отрицательные результаты. Если бы в COBOL был тип данных DATE, а не программистам приходилось бы писать собственные подпрограммы, этого бы не случилось. Пользователи реляционных баз данных и программисты языков четвертого поколения могут над этим посмеиваться, ведь в их продуктах есть встроенные типы данных DATE.

Работа со временем

Большинство небольших баз данных живет и работает в единственном часовом поясе. Если у вас есть база данных, охватывающая более одного часового пояса, вы можете задуматься о хранении времени в UTC и добавлении числового столбца для записи смещения времени в местном часовом поясе. Часовые пояса начинаются с UTC, смещение которого равно нулю. Таким образом в стандартном SQL определена системная таблица часовых поясов. Существуют также стандартизованные ISO трехбуквенные обозначения мировых часовых поясов, такие как EST для восточного времени (Eastern Standard Time) в Соединенных Штатах. Смещение — это обычно положительное или отрицательное число часов, но существовало несколько странных поясов, на 15 минут выбивавшихся из ожидаемого стереотипа, которые в 1998 году были упразднены.

Теперь помимо всего этого вам следует принять во внимание летнее/зимнее время (Daylight Saving Ti­me), чтобы понять, что такое «декретное время» («law­ful time»), лежащее в основе юридических соглашений. Правительство США использует летнее/зимнее время на федеральных землях, расположенных внутри штатов, где летнее/зимнее время не применяется. Если источником временных меток являются аппаратные часы в компьютере, на котором хранится база данных, в течение года вы можете получить мешанину из пропусков во времени и повторяющихся временных отметок. Поэтому стандартный SQL внутренне использует UTC.

Следует применять «24­часовой» формат времени, который менее подвержен ошибкам, чем 12­часовой (AM/PM), поскольку вероятность неверно прочтения или записи в этом случае ниже. Этот формат проще сортировать вручную, и он меньше подвержен вычислительным ошибкам. Американцы в качестве разделителя часов, минут и секунд пользуются двоеточием; некоторые европейцы применяют точку (для них это не проблема, поскольку для обозначения десятичной точки они используют запятую).

Одной из основных проблем, связанных со временем, является то, что существует три его типа:

•          установленные события — fixed events («Он приезжает в 13:00»),

•          продолжительности — durations («Поездка займет три часа»),

•          интервалы — intervals («Поезд отходит в 10:00 и прибывает в 13:00»),

которые взаимосвязаны.

Стандартный SQL вводит тип данных INTERVAL, не существующий в явном виде в большинстве нынешних реализаций (Rdb от DEC и сейчас корпорации Oracle — исключение). INTERVAL — это единица длительности времени, в отличие от неподвижной во времени точки, указанной в днях, часах, минутах и секундах (стандарт FIPS­127 требует пяти десятичных позиций, что выходит далеко за рамки первоначальных возможностей SQL Server).

Существует два класса интервалов. Один класс, называемый интервалами месяц­год, обладает определенной явно или подразумеваемой точностью представления данных, которая не имеет других полей кроме года (YEAR) и месяца (MONTH), хотя использовать их оба необязательно. Другой класс, называемый интервалами день­время, имеет определенную или подразумеваемую точность представления, которая может включать любые поля, отличные от YEAR и MONTH, то есть DAY, HOUR, MINUTE и SECOND (с десятичными знаками после запятой).

Где узнать о темпоральном SQL

•          Ричард Т. Снодграсс (Richard T. Snodgrass) — профессор университета Аризоны (http://www.cs.ari­zo­na.edu/people/rts/), вся карьера которого основывается на темпоральных базах данных. Вы можете загрузить копию его книги, изданной Mor­gan­Kaufmann (Developing Time­Oriented Database Appli­cations in SQL http://www.cs.arizona.edu/people/rts/tdbbook.pdf), в PDF и получить SQL­код на веб­сайте университета.

•          Том Джонстон (Tom Johnston) также создал серию из приблизительно 30 статей, посвященных темпоральному SQL на http://www.dmreview.com (http://www.dmreview.com/cgi­bin/texis/scripts/dm­re­view­search/search.html?query=tom+john­ston
&Search­=Search), которая в ближайшем будущем, надеюсь, будет издана в виде книги.

Практикум. Основы компонента Service Broker: углубленное изучение. Часть 1

Адам Мэкэник (Adam Machanic)

Это второй из трех практикумов по Service Broker. Серия практикумов задумывалась так, чтобы статьи загружались в SQL Server Management Studio, прочитывались и выполнялись. Они путем опробования материала на практике имеют целью быстро дать читателю хорошую осведомленность о предмете. Поэкспериментируйте с ними и в общем случае используйте их как отправную точку по какому-то аспекту SQL Server. Фактический исходный код размещен в загружаемом файле с фрагментом текста в начале статьи.

В первой части* этой серии практикумов мы рассмотрели фундаментальные основы: настройку типов сообщений, контрактов, очередей и служб, а также отправку и ожидание сообщений. Эта вторая часть расширяет и продолжает первую. Мы познакомимся с некоторыми представлениями каталога, к которым вы можете выполнять запросы, чтобы выяснить, на что способен Service Broker, исследуем, каким образом Service Broker работает с транзакциями и блокировками, организуем маршрутизацию наших сообщений между базами данных и автоматическую обработку сообщений хранимыми про­цедурами.

По завершении этого второго workbench вы получите полное понимание всех наиболее широко используемых возможностей Service Broker, которые будете применять в своих проектах вновь и вновь. Оставшиеся немногочисленные интересные возможности будут рассмотрены в третьем выпуске этого мега­практикума, который скоро появится.

* См. Адам Мэкэник. Практикум. Компонент Service Broker: фундаментальные основы // SQL Server для профессионалов. 2009. № 2.

Автоматическое управление диапазонами Identity в репликации слиянием

А. Гладченко

Эта статья посвящена анализу работы механизма MS SQL Server 2000, обеспечивающего автоматическое разбиение диапазонов столбцов со свойством identity (далее, identity) для участвующих в репликации слиянием таблиц на издателе и подписчиках. Суть решаемой этим механизмом проблемы состоит в том, что значения identity могут устанавливаться и издателем и подписчиком самостоятельно. Часто такие столбцы, как identity используются в качестве внешнего ключа. Если тиражировать эти значения средствами репликации слиянием, может произойти нарушение ограничения внешнего ключа. Одинаковые значения, полученные на разных серверах, после слияния вызовут возникновение конфликта. Традиционно, проблема identity в репликации решается путем использования для этого опции NOT FOR REPLICATION и разделения его значений на не пересекающиеся диапазоны для разных база данных. Еще одним, кардинальным путем решения этой проблемы является замена свойства identity на uniqueidentifier. Существует и стандартное решение, позволяющее доверить разделение диапазонов компонентам репликации MS SQL Server, работающим на издателе. Информация об автоматически выделяемых диапазонах хранится в таблице MSrepl_identity_range базы данных распространителя.

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

1.         Включить опцию автоматического разделения диапазонов при создании публикации. Это можно сделать с помощью мастера «Create Publication Wizard» или используя системную хранимую процедуру sp_addmergearticle.

2.         Назначение начальных диапазонов значений для издателя и подписчиков.

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

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

Проще всего рассмотреть, как это все работает на живом примере.

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

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