最常见的SQL反模式是什么?

同一查询中的相同子查询。

我最担心的是450列的访问表，这些表是由总经理最好的朋友狗美容师的8岁儿子整理的，还有那个不可靠的查找表，它之所以存在，是因为有人不知道如何正确地规范化数据结构。

通常，这个查找表是这样的:

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

我已经记不清有多少客户的系统依赖于这种可恶的东西了。

我一直对大多数程序员倾向于在数据访问层混合他们的ui逻辑感到失望:

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

通常，程序员这样做是因为他们想要将数据集直接绑定到一个网格上，而且在服务器端使用SQL Server格式比在客户端使用SQL Server格式更方便。

像上面所示的查询是非常脆弱的，因为它们将数据层与UI层紧密耦合在一起。最重要的是，这种编程风格彻底阻止了存储过程的可重用性。

select some_column, ...
from some_table
group by some_column

假设结果将按some_column排序。我在Sybase上看到过这种情况，其中假设成立(目前)。

使用SQL作为美化的ISAM(索引顺序访问方法)包。特别是嵌套游标，而不是将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中值之间的中断，因为内部循环结束了。这可能是控制中断报告中的一个因素。

此外，应用程序中的排序通常是不允许的。

我最不喜欢的是

1. 在创建表、scpros等时使用空格。我可以接受CamelCase或under_scores，单数或复数，大写或小写，但必须引用一个表或列[有空格]，特别是如果[它是奇怪的间隔](是的，我遇到过这种情况)真的让我很恼火。

2. 规范化的数据。一个表不需要完全标准化，但是当我遇到一个包含员工当前评估分数或主要信息的表时，它告诉我，我可能需要在某个时候创建一个单独的表，然后尝试保持它们同步。我将首先将数据规范化，然后如果我看到非规范化有帮助的地方，我会考虑它。

3. 过度使用视图或游标。视图是有目的的，但是当每个表都包装在一个视图中时，它就太多了。我有几次不得不使用游标，但通常你可以使用其他机制。

4. 访问。程序可以是反模式吗?我们公司有SQL Server，但很多人使用它是因为它的可用性、“易用性”和“对非技术用户的友好性”。这里有太多东西要讲，但如果你在类似的环境中，你知道。

• join的FROM TableA, TableB WHERE语法而不是FROM TableA INNER JOIN TableB ON语法

• 假设查询将以某种方式返回，而不放入ORDER BY子句，因为这是在查询工具中测试时显示的方式。

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

1. 盲目相信用户输入
2. 没有使用参数化查询
3. 明文密码

我需要把我自己目前最喜欢的放在这里，只是为了使列表完整。我最喜欢的反模式是不测试查询。

这适用于以下情况:

1. 您的查询涉及多个表。
2. 您认为您有一个查询的最优设计，但不需要测试您的假设。
3. 您接受第一个有效的查询，不知道它是否接近优化。

任何针对非典型或不充分数据进行的测试都不算数。如果它是一个存储过程，将测试语句放入注释中并保存它，并保存结果。否则，将其与结果一起放入代码中的注释中。

我发现，在性能方面，有两点是最重要的，并且可能会有很大的成本:

• 使用游标而不是基于set 表达式。

• 使用相关子查询，当a 连接到派生表可以执行 李的工作。< / p > < / >

• 改变的观点-观点被改变太频繁，没有通知或理由。这种改变要么在最不合适的时候被注意到，要么更糟，是错误的，永远不会被注意到。也许您的应用程序会崩溃，因为有人为该列想出了更好的名称。作为一条规则，视图应该在维护与消费者的契约的同时扩展基表的用途。修复问题，但不要添加特性或更糟糕的更改行为，因为那样会创建一个新视图。为了避免与其他项目共享视图，在平台允许的情况下，使用CTEs。如果您的商店有一个DBA，您可能无法更改视图，但在这种情况下，您的所有视图都将过时或无用。

• Paramed -查询可以有多个目的吗?也许吧，但下一个读到它的人直到深度冥想才会知道。即使您现在不需要它们，即使它“只是”用于调试，您也有可能需要它们。添加参数可以降低维护时间，保持干燥。如果你有一个where子句，你应该有参数。

• 无case -的case

  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/ rbdb系统提供了许多非常有用的特性(事务、复制)，即使对于非标准化的数据也是如此。磁盘空间很便宜，有时操作/过滤/搜索获取的数据比编写1NF模式更简单(更容易的代码，更快的开发时间)，并处理其中的所有麻烦(复杂的连接，讨厌的子选择等)。

我发现过度标准化的系统通常是不成熟的优化，特别是在开发的早期阶段。

(再想想……http://writeonly.wordpress.com/2008/12/05/simple-object-db-using-json-and-python-sqlite/)

也许这不是一个反模式，但它惹恼了我，当某些数据库的DBA(好吧，我在这里说的是Oracle)用Oracle风格和代码约定编写SQL Server代码，当它运行如此糟糕时抱怨。受够了游标Oracle的人!SQL是基于设置的。

把东西放在临时表中，特别是那些从SQL Server切换到Oracle的人有过度使用临时表的习惯。只需使用嵌套的选择语句。

像这样将冗余表连接到查询中:

select emp.empno, dept.deptno
from emp
join dept on dept.deptno = emp.deptno;

我只是把这个放在一起，基于一些SQL响应这里在SO。

认为触发器之于数据库就像事件处理程序之于OOP是一种严重的反模式。有一种看法是，任何旧的逻辑都可以放入触发器中，当一个事务(事件)在表上发生时被触发。

不正确的。最大的区别之一是触发器是同步的——而且是完全同步的，因为它们在集合操作上是同步的，而不是在行操作上。在OOP方面，正好相反——事件是实现异步事务的有效方法。

使用@@IDENTITY代替SCOPE_IDENTITY()

引用自这个答案:

• @@IDENTITY返回当前会话中为所有作用域的任何表生成的最后一个标识值。这里需要小心，因为它是跨作用域的。您可以从触发器获得一个值，而不是当前语句。
• SCOPE_IDENTITY返回当前会话和当前作用域中为任何表生成的最后一个标识值。通常是你想用的。
• IDENT_CURRENT返回在任何会话和任何范围内为特定表生成的最后一个标识值。这允许您指定需要从哪个表中获取值，以防上面两个表不是您所需要的(非常罕见)。如果您想获取未插入记录的表的当前IDENTITY值，则可以使用此方法。

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

或者，把所有内容都塞进一行。

临时表滥用。

特别是这类事情:

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)

不要从查询中构建临时表，只是为了删除不需要的行。

是的，我在生产db中看到过这种形式的代码页。

对于存储时间值，应该只使用UTC时区。不应使用当地时间。

有一张桌子

code_1
value_1
code_2
value_2
...
code_10
value_10

而不是有3个表

Code, value和code_value

你永远不知道什么时候你可能需要10对以上的代码，价值。

如果只需要一对，就不会浪费磁盘空间。

在他们职业生涯的前6个月学习SQL，在接下来的10年里从不学习其他任何东西。特别是没有学习或有效地使用窗口/分析SQL特性。特别是over()和partition by的使用。

窗口函数，如聚合 函数时，对对象进行聚合 定义的行集(组)，但是 而不是返回一个值 组，窗口函数可以返回 每个组有多个值

有关窗口函数的概览，请参阅O'Reilly SQL烹饪书附录A。

没有使用With子句或适当的连接并依赖子查询。

反模式:

select
...
from data
where RECORD.STATE IN (
SELECT STATEID
FROM STATE
WHERE NAME IN
('Published to test',
'Approved for public',
'Published to public',
'Archived'
))

with valid_states as (
SELECT STATEID
FROM STATE
WHERE NAME IN
('Published to test',
'Approved for public',
'Published to public',
'Archived'
)
select  ... from data, valid_states
where data.state = valid_states.state

最好的:

select
...
from data join states using (state)
where
states.state in  ('Published to test',
'Approved for public',
'Published to public',
'Archived'
)

重新使用一个“死”字段来做一些它不打算做的事情(例如在“传真”字段中存储用户数据)-尽管作为一个快速修复非常诱人!

回复:使用@@IDENTITY代替SCOPE_IDENTITY()

两者都不能用;使用输出代替

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

使用主键作为记录地址的代理，使用外键作为嵌入在记录中的指针的代理。

我最喜欢的SQL反模式:

在非唯一列上使用JOIN，并使用SELECT DISTINCT修剪结果。

创建连接多个表的视图，只是为了从一个表中选择少数列。

 CREATE VIEW my_view AS
SELECT * FROM table1
JOIN table2 ON (...)
JOIN table3 ON (...);


SELECT col1, col2 FROM my_view WHERE col3 = 123;

我看到视图定义是这样的:

CREATE OR REPLACE FORCE VIEW PRICE (PART_NUMBER, PRICE_LIST, LIST_VERSION ...)
AS
SELECT sp.MKT_PART_NUMBER,
sp.PRICE_LIST,
sp.LIST_VERSION,
sp.MIN_PRICE,
sp.UNIT_PRICE,
sp.MAX_PRICE,
...

视图中大约有50个列。有些开发人员以不提供列别名而折磨他人为傲，因此必须计算两个位置的列偏移量，以便能够找出视图中对应的列。

我见过太多人死死抓着IN (...)不放，却完全忘记了EXISTS。有关一个好例子，请参见Symfony Propel ORM。

没有注释的存储过程或函数…

正如我所听到的那样，应用程序连接是指从两个或多个表中取出一组行，然后用一对嵌套的for循环将它们连接到(Java)代码中。这给系统(应用程序和数据库)带来了负担，必须识别整个叉乘，检索它并将其发送给应用程序。假设应用程序可以像数据库一样快地过滤叉乘(不确定)，只是更快地削减结果集意味着更少的数据传输。

编写查询的开发人员没有很好地了解SQL应用程序(包括单个查询和多用户系统)的快慢。这包括对以下方面的无知:

• 物理I/O最小化策略，因为大多数查询的瓶颈是I/O而不是CPU
• 不同类型的物理存储访问对性能的影响(例如，大量的顺序I/O将比大量的小型随机I/O更快，尽管如果你的物理存储是SSD的话，这种影响就小了!)
• 如果DBMS产生了一个糟糕的查询计划，如何手工调优查询
• 如何诊断糟糕的数据库性能，如何“调试”缓慢的查询，以及如何读取查询计划(或EXPLAIN，取决于您选择的DBMS)
• 在多用户应用程序中优化吞吐量和避免死锁的锁定策略
• 批处理和处理数据集的其他技巧的重要性
• 表和索引设计，以最佳平衡空间和性能(例如覆盖索引，尽可能保持索引小，将数据类型减少到所需的最小大小，等等)