The Altered View - A view that is altered too often and without notice or reason. The change will either be noticed at the most inappropriate time or worse be wrong and never noticed. Maybe your application will break because someone thought of a better name for that column. As a rule views should extend the usefulness of base tables while maintaining a contract with consumers. Fix problems but don't add features or worse change behavior, for that create a new view. To mitigate do not share views with other projects and, use CTEs when platforms allow. If your shop has a DBA you probably can't change views but all your views will be outdated and or useless in that case. The !Paramed - Can a query have more than one purpose? Probably but the next person who reads it won't know until deep meditation. Even if you don't need them right now chances are you will, even if it's "just" to debug. Adding parameters lowers maintenance time and keep things DRY. If you have a where clause you should have parameters. The case for no 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

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)我不知道这是否是一个“官方的”反模式，但我不喜欢并试图避免在数据库列中使用字符串文字作为魔法值。

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())

没有格式标识符

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

我最喜欢的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;