Примеры с SQL

Прочитал интересную статью со сборником частых ошибок, которые допускают при написании SQL-запросов.

Для себя могу отметить следующее (далее пункты соответствуют пунктам в статье):

1. Правильность типа данных очень важна.
2. SELECT * - зло!
3. Alias нужны всегда и для всех объектов.
4. Нельзя ориентироваться на порядок колок в таблице.
5. Коварный NULL (его необходимо учитывать при сравнениях).
6. Константы даты только YYYYMMDD (раньше писал с дефисами, больше не буду).
7. Интересные решения по фильтрации по дате - вычисляемое поле и индекс.
8. Нельзя преобразовывать колонки в WHERE.
9. Всегда необходимо указывать правильный тип констант (строки в кавычках).
10. Для поиска с начала только LIKE.
11. Для констант в UNICODE всегда N (иначе будет приведение к ANSI).
12. COLLATE очень важен.
13. Для LIKE c %Value% можно использовать BINARY COLLATE.
14. Имена всех объектов писать строго как в БД (иначе м.б. проблемы с COLLATE).
15. Внимание с CHAR и VARCHAR (т.к. есть отличия в сравнении по LIKE).
16. Важно указывать размерность типа (просто VARCHAR = VARCHAR(1), но в CAST/CONVERT будет VARCHAR(30)).
17. ISNULL и COALESCE (первый приводит к меньшему типу, второй к большему, но работает медленнее).
18. Внимательнее с математическими операциями - результат зависит от типов операндов.
19. UNION vs UNION ALL (первый медленно, но может выполняться параллельно, второй быстро но последовательно).
20. Внимательнее с подзапросами в SELECT - могут быть лишние чтения.
21. В подзапросах может вернуться более одного результата (тогда ошибка).
22. CASE WHEN - может привести многократному вычислению одних и тех же операторов.
23. Scalar func - зло (особенно с параметрами и вычислениями).
24. Внимательнее с VIEWs (могут быть проблемы с производительностью, если нужны не все колонки).
25. CURSORs - зло (только если по-другому нельзя).
26. Пока нет STRING_CONCAT - XML оптимальный вариант (90% случаев).
27. SQL Injection - использовать sp_executesql с параметрами, вместо ручной склейки строки.

PS: Для отображение численных характеристик выполнения запроса можно использовать SET STATISTICS TIME, IO ON перед запросом и OFF после.

Коллега по работе прислал ссылку на интересную, а главное крайне полезную статью.
Кратко: без установленного исправления KB #2969896 в SQL Server 2012 и 2014 при перестроении индексов в режиме online может произойти повреждение индексов и даже потеря данных в таблице.

В очередной раз хочу отметить важность установки обновлений, а также резервного копирования.

• Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server.
  • Часть первая
  • Часть вторая
  • Часть третья
  • Часть четвертая
  • Часть пятая
• Рефакторинг схем баз данных
• Игра со списком условий
• Инструмент для построения условий
• Использование OUTER LOOP JOIN
• Liquibase - система контроля версий для Баз данных
• Типичные взаимные блокировки в MS SQL и способы борьбы с ними
• Microsoft SQL Server Data Tools
• История про msdb размером в 42 Гб

Иногда на прямо на SQL-сервере бывает необходимо узнать от имени какого пользователя выполняется тот или иной запрос. В этом случае на выручку приходит функция:

print SYSTEM_USER -- Пользователь, под которым подключились к базе данных

Чаще всего я использую данную функцию как временное (отладочное) решение, когда необходимо определить какой пользователь создал или изменил запись в таблице. Для этого достаточно в таблицу добавить поле (NOT NULL) и указать для него в качестве значения по умолчанию SYSTEM_USER.

UPD: Появилось только 2005 MSSQL (было исправлено после уточнения в комментариях)

We've caught both SCOPE_IDENTITY() and @@IDENTITY returning the incorrect value after doing an insert. It appears to be some combination of our database settings, the number of rows in the tables, and the particulars of the query plan.

https://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value

То есть в 2008 SQL-сервере SCOPE_IDENTITY() (а также @@IDENTITY) иногда может и "пошутить" - вернуть некорректное значение. Ошибка проявляется при некоторых настройках базы, и определенном кол-ве записей в таблице (>1000000). Вроде как устранено в пакете обновления SP3.

Небольшая подборка статей по нововведениям MS SQL-Server 2012 (Denali):

• Новое в SQL Server Management Studio (SSMS)
• Модификация возвращаемого набора данных
• Обработка ошибок
• Новый оператор Offset
• Новый объект Sequence
• Автономные базы данных (Contained Database)

Довольно часто возникает ситуация, когда прислали какие-то данные в Excel-файле и их надо загрузить в базу данных (например, MS SQL) для последующей обработки или хранения.

Раньше в таких случаях, я пользовался штатной возможностью SQL-сервера дли импорта данных, но иногда проще и быстрее использовать знание SQL и возможностей самого Excel. Надо просто написать запрос на вставку (обновление и т.д.) одной записи в необходимую таблицу базы данных, а затем с помощью Excel-функции СЦЕПИТЬ (в английской версии CONCATENATE) собрать в последней колонке первой строки Excel-таблицы запрос на вставку (используя подготовленный пример) и содержащий необходимые данные из этой таблицы. Потом достаточно "протянуть" эту формулу до последней строки таблицы, скопировать получившиеся данные в среду выполнения sql-запросов и выполнить получившиеся запросы.

Данный нехитрый способ частенько выручает меня в последнее время.