使用@@IDENTITY代替SCOPE_IDENTITY()

引自以下回答:

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement. SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use. IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into.

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

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

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

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

临时表滥用。

特别是这类事情:

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中看到过这种形式的代码页。

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

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

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

有一张桌子

code_1
value_1
code_2
value_2
...
code_10
value_10

而不是有3个表

Code, value和code_value

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

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