Особенности работы под SQL

C SQL-сервером существует три этапа работы:

• Проектирование базы - самое главное привести данные к нормализованному виду и обеспечить целостность данных с помощью встроенных ограничений. На эту тему рекомендуется как минимум прочитать:
  • Нормализация реляционных баз данных.
  • Денормализация: как нарушить правила и избежать последствий.
  • Наложение ограничений на значения данных.
  • Целостность данных.
  
  
• Программирование приложения.
  • Составление простейших SQL-запросов - задача вполне тривиальная, если не сказать скучная... Пока или запросы не становятся вложенными, или используется Group и агрегатные функции с обработкой их результатов в HAVING, или таблица из которой делается отбор, создается на лету, или сложный многоходовый JOIN таблиц, или сортировка по сложным критериям, или хитрый UNION, или надо подключить прямо в SQL внешний обьект и что-нибудь сделать им - например, экспортнуть результат во внешний файл и т.д. Чтобы разобратся со сложным запросом, в первую очередь, надо четко понимать последовательность выполнения SQL-запроса:
    • Первым всегда рассчитывается таблица, указанная во FROM (с учетом всех JOIN'ов) - на рисунках DerivedTable, JoinedTable, RowSetFunc и т.д.
    • Далее для каждой строки расчитанной на первом шаге таблицы применятеся выражение, указанное в WHERE
    • Далее таблица группируется по столбцам, указанным в GROUP
    • После результатов группировки строки таблица обрабатывается по выражению, указанному в HAVING
    • На последнем шаге каждого отдельно взятого СЕЛЕКТА - применяется DISTINCT.
    
    
  • Один из самых проблемных вопросов при SQL-программировании - это сортировки. В этом деле есть много хитростей и техник исполнения. Это наиболее чуствительная часть запроса, тк может ОЧЕНЬ легко поднять стоимость запроса до невероятныx высот.

    Различаются следующие типы сортировок: через временные таблы, в UNION, в динамике, управляемые сортировки в процедурах верхнего уровня, управляемые сортировки в функциях нижнего уровня через столбец VARIANT ...

    
    
  • Собственно технических приемов SQL-программирования существует бесчисленное множество. Вот несколько из них:
    • Обман оптимизатора.
    • Использование сериализации вместо временных таблиц.
    
    
  • Транзакции и блокировки. Для начала пару слов о транзакциях. Вот наиболее легкий для понимания и совершенно типичный пример необходимости применения транзакций. В данном случае он будет применен для наложения ограничений на целостность данных.

    В одном из своих проектов я разработал вот такую струтуру данных - это ее фрагмент. Таблички RelationMap (или CorrelateMap) - это отношение многие-ко-многим, о ней и пойдет речь. Это табличка соответстсвия между одной большой таблой слева над ней и другой большой таблой справа от нее. Эти две таблички выведены на форму, на которой юзер составляет соответствие между двумя большими таблами и нажатием кнопочек на форме заполняет табличку отношений MAP. Наша задача - обеспечить, чтобы в таблу MAP не попали неверные записи, с одной стороны и, с другой стороны в большой левой табле были ВСЕГДА помечены флагом MapStatus=1 те записи, для которых создано соответствие в табле отношений Map. Чтобы никогда не случилась вот такая ситуация. Иными словами - оба условия надо связать НАМЕРТВО. Только при соблюдении ОБОИХ этих условий данные имеют смысл, иначе они бессмысленны или другими словами - нарушена целостность данных. Для этого и существуют транзакции, те выполнение пакета программ в неразрывной последовательности. В данном случае я обеспечил это вот такой процедурой, в которой BEGIN TRANSACTION означает начало неразрывной последовательности, COMMIT - окончательное внесение изменений в данные, а ROLLBACK - откат транзакции.

    В этой табличке указателей есть еще несколько интересных моментов - во-первых она содержит дополнительное ограничение целостности IX_PR_ChangesMap, которое должно заблокировать дублирующее добавление данных - это задано параметром UNIQUE для вычисляемого поля ONE. Учтите, что при ошибках INSERT, в отличие от других SQL-операторов, не прерывает выполнение пакета команд, только @@RowCount зафиксирует отсутсвие добавленной записи. Другой интересный момент здесь в том, что вычисляемые поля в SQL имеют одну интересную особенность - совершенно в неожиданных местах они вдруг начинают неверно считатся и проги падают, так как и было показано на форме. Решается эта проблема установкой одной опции, которую задается не на уровне конкретной таблы или же сервера в целом, а для отдельной базы. Еще один интересный момент в этой процедуре - коррелированный оператор Delete.

    А что бы было, если бы такого уникального поля в таблице отношений многие-ко-многим не было? Однажды я совершил такую ошибку - и в таблице указателей оказались сотни тысяч дублирубщихся записей. Удалить их удалось только сложным коррелированным запросом. Вообще, чтобы осознать мощь SQL я попробовал составить тот же алгоритм удаления дублирующихся записей, но уже без использования коррелированного запроса. Взамен пяти строчек у меня получился вот такой огромный алгоритм с промежуточной таблицей и двумя вложенными курсорами, в которым внутренний курсор определялся в зависимости от параметров внешнего. Этот второй алгоритм работал в сотни раз медленнее первого пятистрочного алгоритма с коррелированным запросом. Вот такой по ходу дела получился аргумент в пользу коррелированных запросов...

  
  
• Администрирование. В принципе, SQL-сервер - штука чрезвычайно надежная (хотя и очень прожорливая в плане загрузки процессора). Сидит он тихонечко и слушает свой порт (обычно 1433). Что мне очень нравится, что любые опции, выбранные при инсталляции можно потом заменить в процессе работы. Обычно из года в год я наблюдаю одни и те же проблемы:
  • Репликация. Репликация LogShipping, Репликация SnapShot.
  • При работе на устаревших Windows'ах надо обновлять MDAC или MSDE. Рекомендуется вообще ставить текущую версию сервис-пака, доступную для загрузки отсюда.
  • Аутентификация и авторизация - Права на обьекты базы (особенно созданные разными юзерами под разными логинами)
  • Архивирование
  • Index Tuning
  • Сonnection string
  • Усечение журнала транзакций
  • DTS-пакеты. Что не тянут DTS: копирование ключей на основе IDENTITY. Еще про DTS можно почитать:  Перемещение DTS пакетов,  Приемы профессионалов DTS,  Выполнение пакета DTS из хранимой процедуры.

< THANKS ME>