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

Содержание номера за Август 2009 год

SQL Server

Август 2009
№ 8 (104)

Editorial

Шон МакКоун

21 знак до рая 

Programming

Майкл Коулс

Попался! Агрегатные функции SQL и значение NULL

Дон Кили

Безопасность в мире CLR внутри SQL Server 

DB Design & Warehousing

Кален Делани

Комбинирование структур планов TEMPLATE и SQL  

Other

Роберт Шелдон

SQL Server 2008: задача «Профилирование данных» служб SSIS. Часть 1

21 знак до рая

Хотя я и играл с powershell почти год, мне это средство никогда не казалось серьезным инструментом. На конференции Tech Ed этого года я обратил внимание на некоторые особенно мощные средства, появившиеся в SQL Server 2008, и надо сказать, что мое отношение радикально изменилось, могу даже сказать, что я влюбился (платоническая любовь, понимаете) в этот инструмент.

Хочу сразу сказать, что способ реализации power­shell провайдера в форме мини­оболочки вызывает критику. Возможно, эти люди правы. Есть вещи, которые вы не сможете сделать. Я не стану останавливаться на деталях, они достаточно хорошо документированы в блогах. Но я собираюсь рассказать о том, что вы можете сделать с помощью powershell в среде SQL Server. И я не оговорился, когда сказал SQL Server, а не SQL Server 2008. Дело в том, что вы можете подключиться к любому экземпляру, будь то Yukon или SQL2K и выполнять powershell команды применительно к этому экземпляру. Я думаю, что тут могут быть некоторые ограничения, вроде необходимости иметь как минимум SQL2K SP4 и, возможно, SQL2K5 SP1, но это я оставляю вам на самостоятельное исследование. Идея в том, что вы не ограничены доступом только к SQL Server 2008. Итак, я не стану нудно перечислять мелкие детали работы, вместо этого я покажу пару примеров.

Чтобы получить список всех таблиц и число записей в них: откройте powershell в SSMS (SQL Server Management Studio) и введите такую команду, я использую Northwind.

cd default\databases\Northwind\tables

Точно также как во времена DOS, вы переходите по структуре баз данных, как по папкам. Теперь:

gci | sort-object -Property RowCount -desc | format-table Schema, Name, Rowcount -autosize

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

Здорово, не правда ли? Вы получаете все таблицы и число записей в них без использования курсора, динамического SQL или временной таблицы. Теперь давайте наложим фильтр. Та же самая команда...

gci *customer* | sort-object -Property RowCount -desc | format-table Schema, Name, Rowcount -autosize

Только теперь вы видите параметр *customer* в ее тексте. Это уточнение говорит, что нас интересуют только таблицы, в имени которых есть строка ‘cus­to­mer’. Это аналогично условию “like ‘%custo­mer%’” при работе с T­SQL.

Пока все прекрасно?

Еще пример, эта возможность меня просто приводит в восторг.

Начнем с того же места, вот отсюда:

cd default\databases\Northwind\tables

Теперь введем такую команду:

gci | % {$_.Script()}

Всего 21 символ, что же они делают?

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

Итак, все то же самое, только результат идет в файл:

gci | % {$_.Script()} | out­file C:\Table.txt

Вот и все. Точно также как и раньше, вы можете указать фильтр для определения области применения команды:

gci *customer* | % {$_.Script()} | out-file C:\Table.txt

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

Серьезно, как это на ваш взгляд? Нет никакой необходимости выполнять все эти операции вручную. Да и сам путь к получению результата стал гораздо короче. Написать программу, которая делает такие же вещи средствами SMO/DMO или SQL окажется гораздо сложнее и проблематичнее. Именно поэтому мало кто берется за эту работу. Но теперь всего 21 символ (включая пробелы) и задача решена. Точно также вы сможете создать скрипт для любого объекта БД, процедуры, представления… Все, что нужно сделать, — это перейти в соответствующий «каталог». И в каждом из них вы сможете сделать ‘dir’, чтобы увидеть, что вы можете сделать.

Я не стану пытаться написать массивное руководство по powershell, моя задача была увлечь вас интересными возможностями. Удачи!

Попался! Агрегатные функции SQL и значение NULL

Введение

На днях я обратил внимание на большое количество вопросов о том, как агрегатные функции SQL обрабатывают значения NULL, появившихся в форуме по статье «Understanding the difference between IS NULL and =NULL» на SQL Server Central. Данная статья была написана, чтобы помочь ответить на некоторые из этих вопросов, объясняя, каким образом агрегатные функции SQL обращаются со значениями NULL, и описать некоторые другие «ловушки» при работе с агрегатными функциями. Каждый раздел статьи завершается сводкой «ловушек», на которые следует обращать внимание при использовании этих функций.

Стандарт ANSI SQL­92 определяет пять агрегатных функций (которые в стандарте SQL­92 также упоминаются как функции, оперирующие с наборами). SQL­значения NULL обрабатываются внутри системы особым образом, о котором проектировщику/программисту базы данных нужно знать при использовании этих функций. Данная статья описывает, каким образом агрегатные функции ANSI SQL­92 работают со значениями NULL в соответствии со стандартом ANSI SQL­92.

В тексте статьи я ссылаюсь на стандарт ANSI SQL­92, а код примеров требует наличия образца базы данных Northwind из комплекта Microsoft SQL Server. Все примеры выполнялись на SQL Server 2000.

Агрегатные функции

ANSI SQL­92 определяет пять агрегатных функций. Для всех функций над наборами, принимающих название столбца, за кулисами происходит следующий процесс:

• Применяется выражение поиска (предложение WHERE).

• К каждой строке результатов шага 1 применяется выражение значения (скалярные функции над столбцом наподобие COALESCE()).

• Из результатов исключаются значения NULL.

Примечание:COUNT() — это частный случай, который мы подробно рассмотрим ниже.

ANSI SQL­92 оговаривает, что при исключении функциями, оперирующими с наборами, значений NULL возвращается предупреждающее сообщение. Вы можете включить эту настройку, воспользовавшись командой SET ANSI_WARNINGS ON. Настройка SET ASNI_WARNINGS ON приводит к следующему сообщению об ошибке: «Warning: Null value is eliminated by an aggregate or other set operation».

SET ASNI_WARNINGS ON также влияет на то, как SQL Server обрабатывает деление на ноль, арифметическое переполнение и на поведение при усечении символьных/двоичных данных.

Перед тем как изменять настройку SET ANSI_WARNINGS, обязательно ознакомьтесь со следующей статьей в MSDN: http://msdn.microsoft.com/library/default.asp?url=/library/en­us/tsqlref/ts_set­set_6d2r.asp

Ловушка:SET ANSI_WARNINGS ON оказывает влияние на несколько аспектов поведения SQL Server. Обязательно посмотрите http://msdn.microsoft.com/library/default.asp?url=/library/en­us/tsqlref/ts_set­set_6d2r.asp перед изменением этой настройки.

Дополнительные источники

• MSDN, «SET ANSI_WARNINGS». http://msdn.microsoft.com/library/default.asp?url=/library/en­us/tsqlref/ts_set­set_6d2r.asp.

• Стандарт ANSI SQL­92 (Черновик). http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt.

Безопасность в мире CLR внутри SQL Server

Одним из главных преимуществ использования общеязыковой среды выполнения (CLR) .NET, работающей в любом окружении, является разграничение доступа кода (code access security, CAS). CAS поддерживает схемы авторизации как по пользователю, так и по коду, для предотвращения использования различных приманок и других атак. Но как эта схема безопасности сосуществует с новыми усовершенствованиями безопасности в SQL Server 2005? По умолчанию ваш .NET-код достаточно безопасен, но эти две схемы безопасности легко могут столкнуться лбами и стать причиной ваших проблем. В этой статье я кратко рассмотрю концепцию CAS и несколько новых функций безопасности в SQL Server 2005, а затем расскажу, как заставить эти две системы работать на вас, а не против вас.

Возможность создания хранимых процедур и других программных модулей с использованием C#, VB или любого другого языка .NET долго являлась одной из самых привлекательных и ожидаемых функций SQL Server 2005. И разработчики и администраторы баз данных наконец­то могут сбросить оковы Transact­SQL (T­SQL) и C++ и использовать для написания хранимых процедур настоящий, эффективный язык!

В то же время, изучение выполняемого в пространстве памяти базы данных .NET­кода пугает некоторых людей до смерти, в особенности некоторых администраторов, которые ответственны за целостность данных и обеспечение максимально возможной стабильности работы сервера. Мысли о том, что код, который имеет полный доступ к .NET Framework и Win32 API, приводят многих администраторов к клятвам, что такой код будет работать на сервере только «через их труп». Я беседовал на конференциях и проводил много семинаров, и спрашивал слушателей и клиентов пугает ли их .NET­код на сервере и почему. Вот несколько типичных причин:

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

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

• Размывание границ между данными и кодом, в частности по поводу новой возможности создания пользовательских типов данных в .NET­коде.

• Еще один способ «засорения» сервера кодом, хотя хранимые процедуры OLE­автоматизации (SP_OS*) и системы командного процессора (command shell system) (xp_cmdshell) доступны уже давно.

С практической точки зрения код .NET Framework в SQL Server 2005, который часто называют SQLCLR, потому что он основан на .NET Common Language Runtime (CLR), это просто очередной программный модуль, который существует и выполняется в SQL Server. Да, он новый, но это просто код. Это не plug­in, заменяющий T­SQL, который все еще лучше в вопросах доступа к данным. Но SQLCLR­код открывает множество новых возможностей для создания сложных приложений для баз данных. Рано или поздно почти каждый администратор столкнется с необходимостью использовать такой код и будет вынужден сделать конечное решение, «впускать» ли его в базу данных.

В этой статье я рассмотрю один из главных вопросов, касающихся SQLCLR­кода: как его обезопасить? На самом деле, я сознательно смешал два разных понятия: безопасность и надежность. Безопасность означает обеспечение защиты данных, а надежность — обеспечение защиты SQL Server; надежность часто путают с безопасностью. Хотя моя главная цель в этой статье — обсуждение безопасности, я расскажу немного и о надежности.

Я предполагаю, что вы уже знакомы с преимуществами и основами использования .NET­кода в SQL Server 2005, в том числе со следующими темами:

• Сборки как единицы упаковки (packaging), развертывания (deployment) и контроля версий.

• Основы разграничения доступа кода .NET.

• Новые функции безопасности в SQL Server 2005.

Другими словами, это не вводная статья о SQLCLR.

Комбинирование структур планов TEMPLATE и SQL

DOWNLOAD

Структуры планов TEMPLATE допускают две подсказки оптимизации: PARAMETERIZATION SIMPLE и PARAMETERIZATION FORCED, дающие вам некоторый контроль над возможностью автоматической параметризации, предоставляемой SQL Server 2005. (За дополнительной информацией об автоматической параметризации обратитесь к статье «Контролируем параметризацию» в журнале «SQL Server для администраторов».) Эти подсказки, являющиеся фактически единственными, которые может использовать структура плана TEMPLATE, заставляют SQL Server всегда применять один и тот же план для всего «класса» запросов и рассматривать константные значения в запросах так, как если бы они были параметрами. Однако в структурах планов типа SQL или OBJECT могут быть использованы многие другие подсказки; в действительности, любую подсказку, доступную в предложении OPTION, можно использовать как часть структуры плана SQL или OBJECT. Здесь мы взглянем на то, как SQL Server определяет подходящие структуры планов для запроса, затем рассмотрим пример комбинирования планов TEMPLATE для контроля автоматической параметризации с планами SQL, которые допускают указание других всевозможных подсказок обработки (query hint). Применение планов TEMPLATE дает вам больше контроля над тем, как ядро реляционной базы данных SQL Server будет обрабатывать запрос, что позволяет обеспечить лучшую производительность, чем ядро запросов могло бы сделать это самостоятельно.

SQL Server 2008: задача «Профилирование данных» служб SSIS. Часть 1

С помощью новой задачи «Профилирование данных», которая появилась в версии SQL Server 2008, многое можно сделать, чтобы гарантировать достоверность данных, импортируемых средствами служб SSIS. И кроме того, можно разработать систему, способную выполнять действия, необходимые для коррекции наиболее часто встречающихся проблем. Боб Шелдон показывает, с чего начать.

Одно из новшеств версии SQL Server 2008 — это задача «Профилирование данных», которая является компонентом потока управления служб SQL Server Integ­ration Services (SSIS). Эта задача позволяет проанализировать данные, хранящиеся в базе данных SQL Server, и оформить результаты анализа в виде XML­отчетов, которые можно сохранить в файле или в переменной служб SSIS. Настроив профили одного или нескольких типов, поддерживаемых этой задачей, можно создать отчет, который содержит подробные сведения, вроде хранящихся в столбце минимального и максимального значений или количественное и процентное содержание значений null в указанном столбце.

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

В этой статье я рассматриваю пример пакета служб SSIS, который включает задачу «Профилирование данных». Эта задача настроена для создания отчета на основе профильного типа «Включение значений» (Value Inclusion), который обеспечивает подробную информацию, позволяющую определить, можно ли использовать один или несколько столбцов в таблице­источнике в качестве внешнего ключа. Хотя профиль такого типа, возможно, представляется не слишком полезным в пакете служб SSIS, он, как ни странно, может сыграть довольно важную роль при загрузке данных, потому что информацию, обеспечиваемую этим профилем, можно использовать для сравнения данных источника с корректными данными в целевой базе данных, чтобы оценить допустимость данных источника.

Предполагается, что вы обладаете практическими познаниями в области сервера SQL Server, языка Transact­SQL и служб SSIS, а весь исходный программный код для решения служб SSIS можно получить на нашем сайте.

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