Каковы наиболее распространенные анти-шаблоны SQL? [закрытый]

вот мой топ 3.

номер 1. Не удалось указать список полей. (Изменить: чтобы предотвратить путаницу: это правило производственного кода. Это не относится к одноразовым сценариям анализа-если только я не являюсь автором.)

SELECT *
Insert Into blah SELECT *

должно быть

SELECT fieldlist
Insert Into blah (fieldlist) SELECT fieldlist

номер 2. Используя курсор и цикл while, когда цикл while с переменной цикла будет делать.

DECLARE @LoopVar int

SET @LoopVar = (SELECT MIN(TheKey) FROM TheTable)
WHILE @LoopVar is not null
BEGIN
  -- Do Stuff with current value of @LoopVar
  ...
  --Ok, done, now get the next value
  SET @LoopVar = (SELECT MIN(TheKey) FROM TheTable
    WHERE @LoopVar < TheKey)
END

номер 3. DateLogic через строковые типы.

--Trim the time
Convert(Convert(theDate, varchar(10), 121), datetime)

должны будь

--Trim the time
DateAdd(dd, DateDiff(dd, 0, theDate), 0)

---

Я видел недавний всплеск "один запрос лучше, чем два, amiright?"

SELECT *
FROM blah
WHERE (blah.Name = @name OR @name is null)
  AND (blah.Purpose = @Purpose OR @Purpose is null)

этот запрос требует двух или трех разных планов выполнения в зависимости от значений параметров. Только один план выполнения генерируется и вставляется в кэш для этого текста sql. Этот план будет использоваться независимо от значения параметров. Это приводит к периодическому снижению производительности. Гораздо лучше написать два запроса (один запрос на каждый предполагаемый план выполнения).

• читаемые человеком поля пароля, ей-богу. Само собой разумеется.

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

• переработка SQL-генерируемых значений PK.

• сюрприз никто не упомянул в Бог-стол еще. Ничего не говорит "органический" как 100 столбцов битовой флаги, большие строки и целые.

• то есть "Я скучаю .ini файлы" картина: хранить CSVs, труба строки с разделителями или другой синтаксический анализ обязательные данные в больших текстовых полях.

• и для MS SQL server использование курсоры на всех. Есть и получше способ выполнения любой заданной задачи курсора.

отредактировано, потому что их так много!

не нужно копать глубоко для этого: не используя подготовленные заявления.

использование бессмысленных псевдонимов таблиц:

from employee t1,
department t2,
job t3,
...

делает чтение большого оператора SQL намного сложнее, чем это должно быть

var query = "select COUNT(*) from Users where UserName = '" 
            + tbUser.Text 
            + "' and Password = '" 
            + tbPassword.Text +"'";

1. слепо доверяя вводу пользователя
2. не используя параметризованные запросы
3. пароли открытым текстом

мои bugbears-это 450 таблиц доступа к столбцам, которые были собраны 8-летним сыном лучшего друга управляющего директора dog groomer и хитрой таблицей поиска, которая существует только потому, что кто-то не знает, как правильно нормализовать структуру данных.

Как правило, эта таблица поиска выглядит следующим образом:

ID INT,
Name NVARCHAR(132),
IntValue1 INT,
IntValue2 INT,
CharValue1 NVARCHAR(255),
CharValue2 NVARCHAR(255),
Date1 DATETIME,
Date2 DATETIME

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

те, которые мне больше всего не нравятся

1. использование пространства при создании таблиц, хранимые процедуры и т. д. Я в порядке с CamelCase или under_scores и единственными или множественными числами и прописными или строчными буквами, но необходимость ссылаться на таблицу или столбец [с пробелами], особенно если [ это странно разнесено] (да, я столкнулся с этим) действительно раздражает меня.

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

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

4. открыть. Может ли программа быть анти-паттерн? У нас есть SQL Server на моей работе, но многие люди используют доступ из-за его доступности, "простоты использования" и "дружелюбия" к нетехническим пользователям. Здесь слишком много всего, чтобы вникать, но если вы были в подобной среде, вы знаете.

чрезмерное использование временных таблиц и курсоров.

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

для хранения значений времени следует использовать только часовой пояс UTC. Местное время использовать не следует.

используя @ @ IDENTITY вместо SCOPE_IDENTITY ()

цитата из ответ:

• @ @ IDENTITY возвращает последнее значение идентификатора, созданное для любой таблицы в текущем сеансе, во всех областях. Вы должны быть осторожны здесь, так как это в разных областях. Вы можете получить значение из триггера, вместо вашего текущего оператора.
• функция scope_identity возвращает последнее значение идентификатора созданное для любой таблицы в текущем сеансе и текущей области. Вообще то, что вы хотите использовать.
• функция ident_current возвращает последнее значение идентификатора, созданное для указанной таблицы в любом сеансе и области. Это позволяет указать, из какой таблицы вы хотите получить значение, в случае, если два выше не совсем то, что вам нужно (очень редко). Вы можете использовать это, если хотите получить текущее значение идентификатора для таблицы, в которую вы не вставили запись в.

повторное использование "мертвого" поля для чего - то, для чего оно не предназначалось (например, хранение пользовательских данных в поле "факс") - очень заманчиво, как быстрое исправление!

select some_column, ...
from some_table
group by some_column

и предполагая, что результат будет отсортирован по some_column. Я видел это немного с Sybase, где предположение выполняется (на данный момент).

SELECT FirstName + ' ' + LastName as "Full Name", case UserRole when 2 then "Admin" when 1 then "Moderator" else "User" end as "User's Role", case SignedIn when 0 then "Logged in" else "Logged out" end as "User signed in?", Convert(varchar(100), LastSignOn, 101) as "Last Sign On", DateDiff('d', LastSignOn, getDate()) as "Days since last sign on", AddrLine1 + ' ' + AddrLine2 + ' ' + AddrLine3 + ' ' + City + ', ' + State + ' ' + Zip as "Address", 'XXX-XX-' + Substring(Convert(varchar(9), SSN), 6, 4) as "Social Security #" FROM Users

или, запихивая все в одну строку.

• The FROM TableA, TableB WHERE синтаксис для соединений, а не FROM TableA INNER JOIN TableB ON

• делая предположения, что запрос будет возвращен отсортированным определенным образом, не помещая предложение ORDER BY, просто потому, что это было так, как оно появилось во время тестирования в инструменте запроса.

Мне нужно поставить мои любимые здесь, просто чтобы сделать список полным. Мой любимый антипаттерн не проверяя ваши запросы.

это применимо, когда:

1. ваш запрос включает в себя более одной таблицы.
2. вы думаете, что у вас есть оптимальный дизайн для запроса, но не утруждайте себя проверкой своих предположений.
3. вы принимаете первый запрос, который работает, без понятия о том, является ли он даже близким к оптимизированному.

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

изучение SQL в первые шесть месяцев своей карьеры и никогда не узнать ничего другого в течение следующих 10 лет. В частности, не изучая или эффективно используя оконные / аналитические функции SQL. В частности, использование over () и partition by.

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

посмотреть O'Reilly SQL Cookbook приложение a для хорошего обзора оконных функций.

противоположный взгляд: чрезмерная одержимость нормализацией.

большинство систем SQL / RBDBs предоставляют множество функций (транзакции, репликация), которые весьма полезны, даже с ненормализованными данными. Дисковое пространство дешево, и иногда может быть проще (более простой код, более быстрое время разработки) манипулировать / фильтровать / искать извлеченные данные, чем писать схему 1NF и справляться со всеми неприятностями в ней (сложные соединения, неприятные подзапросы и т. д.).

Я нашел чрезмерно нормализованные системы часто являются преждевременной оптимизацией, особенно на ранних стадиях разработки.

(больше мыслей о нем... http://writeonly.wordpress.com/2008/12/05/simple-object-db-using-json-and-python-sqlite/)

временное злоупотребление таблицей.

конкретно такого рода вещи:

SELECT personid, firstname, lastname, age
INTO #tmpPeople
FROM People
WHERE lastname like 's%'

DELETE FROM #tmpPeople
WHERE firstname = 'John'

DELETE FROM #tmpPeople
WHERE firstname = 'Jon'

DELETE FROM #tmpPeople
WHERE age > 35

UPDATE People
SET firstname = 'Fred'
WHERE personid IN (SELECT personid from #tmpPeople)

не построить временную таблицу из запроса, только чтобы удалить строки, которые вам не нужны.

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

Я просто собрал это вместе, основываясь на некоторых ответах SQL здесь на SO.

Это серьезный антипаттерн, чтобы думать, что триггеры относятся к базам данных, как обработчики событий к ООП. Существует такое восприятие, что просто любая старая логика может быть помещена в триггеры, чтобы быть уволена, когда транзакция (событие) происходит на столе.

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

1) я не знаю, что это "официальный" анти-шаблон, но мне не нравится и я стараюсь избегать строковых литералов как магических значений в столбце базы данных.

пример из таблицы MediaWiki 'image':

img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", 
    "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
img_major_mime ENUM("unknown", "application", "audio", "image", "text", 
    "video", "message", "model", "multipart") NOT NULL default "unknown",

(Я просто замечаю другой корпус, другое дело, чтобы избежать)

Я разрабатываю такие случаи, как поиск int в таблицах ImageMediaType и ImageMajorMime с первичными ключами int.

2) преобразование даты / строки, которое зависит от конкретного NLS настройки

CONVERT(NVARCHAR, GETDATE())

без идентификатора формата

идентичные подзапросы в запросе.

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

• The !Paramed-может ли запрос иметь более одной цели? Возможно, но следующий человек, который прочитает его, не будет знать до глубокой медитации. Даже если вы не нуждаетесь в них прямо сейчас, скорее всего, вы будете, даже если это "просто" для отладки. Добавление параметры понижают продолжительность технического обслуживания и держат вещи сухим. Если у вас есть предложение where, у вас должны быть параметры.

• дело ни в коем случае -

  SELECT  
  CASE @problem  
    WHEN 'Need to replace column A with this medium to large collection of strings hanging out in my code.'  
      THEN 'Create a table for lookup and add to your from clause.'  
    WHEN 'Scrubbing values in the result set based on some business rules.'  
      THEN 'Fix the data in the database'  
    WHEN 'Formating dates or numbers.'   
      THEN 'Apply formating in the presentation layer.'  
    WHEN 'Createing a cross tab'  
      THEN 'Good, but in reporting you should probably be using cross tab, matrix or pivot templates'   
  ELSE 'You probably found another case for no CASE but now I have to edit my code instead of enriching the data...' END  
  

хранимые процедуры или функции без каких-либо замечаний...

помещая вещи во временные таблицы, особенно люди, которые переключаются с SQL Server на Oracle, имеют привычку злоупотреблять временными таблицами. Просто используйте вложенные инструкции select.

два я нахожу наиболее, и может иметь значительную стоимость с точки зрения производительности являются:

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

• использование коррелированных подзапросов, когда a соединение с производной таблицей может сделать работа.

разработчики, которые пишут запросы, не имея хорошего представления о том, что делает приложения SQL (как отдельные запросы, так и многопользовательские системы) быстрыми или медленными. Это включает в себя незнание:

• физические стратегии минимизации ввода-вывода, учитывая, что узким местом большинства запросов является ввод-вывод, а не процессор
• perf влияние различных видов физического доступа к хранилищу (например, много последовательных операций ввода-вывода будет быстрее, чем много небольших случайных операций ввода-вывода, хотя и меньше, если ваш физический хранилище-это SSD!)
• как вручную настроить запрос, если СУБД создает плохой план запроса
• как диагностировать низкую производительность базы данных, как "отлаживать" медленный запрос и как читать план запроса (или объяснять, в зависимости от выбранной СУБД)
• стратегии блокировки для оптимизации пропускной способности и предотвращения взаимоблокировок в многопользовательских приложениях
• важность дозирования и других трюков для обработки наборов данных
• таблицы и индекс чтобы наилучшим образом сбалансировать пространство и производительность (например, охватывая индексы, сохраняя индексы малыми, где это возможно, уменьшая типы данных до минимального необходимого размера и т. д.)

использование SQL в качестве прославленного пакета ISAM (Indexed Sequential Access Method). В частности, вложенность курсоров вместо объединения операторов SQL в один, хотя и больший, оператор. Это также считается "злоупотреблением оптимизатором", поскольку на самом деле оптимизатор мало что может сделать. Это может быть объединено с неподготовленными заявлениями для максимальной неэффективности:

DECLARE c1 CURSOR FOR SELECT Col1, Col2, Col3 FROM Table1

FOREACH c1 INTO a.col1, a.col2, a.col3
    DECLARE c2 CURSOR FOR
        SELECT Item1, Item2, Item3
            FROM Table2
            WHERE Table2.Item1 = a.col2
    FOREACH c2 INTO b.item1, b.item2, b.item3
        ...process data from records a and b...
    END FOREACH
END FOREACH

правильным решением (почти всегда) является объединение двух операторов SELECT в один:

DECLARE c1 CURSOR FOR
    SELECT Col1, Col2, Col3, Item1, Item2, Item3
        FROM Table1, Table2
        WHERE Table2.Item1 = Table1.Col2
        -- ORDER BY Table1.Col1, Table2.Item1

FOREACH c1 INTO a.col1, a.col2, a.col3, b.item1, b.item2, b.item3
    ...process data from records a and b...
END FOREACH

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

кроме того, сортировка в приложении, как правило, нет-нет.

использование первичных ключей в качестве суррогата для адресов записей и использование внешних ключей в качестве суррогата для указателей, встроенных в записи.