blocks|key|1548029|text|我必须对一个有很多列的非常大的表进行重复数据删除，速度很重要。因此，我使用这个方法，它适用于任何表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1548030|delete+T+from+
(select+Row_Number()+Over(Partition+By+BINARY_CHECKSUM(*)+order+by+%25%25physloc%25%25+)+As+RowNumber,+*+From+MyTable)+T
Where+T.RowNumber+>+1|code-block|syntax|javascript|1548031|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I have to dedupe a very big table with many columns and speed is important. Thus I use this method which works for any table:

<pre><code>delete T from 
(select Row_Number() Over(Partition By BINARY_CHECKSUM(*) order by %%physloc%% ) As RowNumber, * From MyTable) T
Where T.RowNumber &gt; 1
</code></pre>

blocks|key|1547713|text|查看新的ROW_NUMBER函数。它是这样工作的：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1547714|SELECT+ROW_NUMBER()+OVER+(ORDER+BY+EMPID+ASC)+AS+ROWID,+*+FROM+EMPLOYEE|code-block|syntax|javascript|1547715|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/de-de/library/ms186734.aspx^0|4|A|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Check out the new <a href="http://msdn.microsoft.com/de-de/library/ms186734.aspx" rel="noreferrer">ROW_NUMBER</a> function. It works like this:

<pre><code>SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE
</code></pre>

blocks|key|1332368|text|如果你只想对一个小的数据集进行基本的行号，那么下面这样的方法如何呢？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1332369|SELECT+row_number()+OVER+(order+by+getdate())+as+ROWID,+*+FROM+Employees|code-block|syntax|javascript|1332370|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

if you just want basic row numbering for a small dataset, how about someting like this?

<pre><code>SELECT row_number() OVER (order by getdate()) as ROWID, * FROM Employees
</code></pre>

blocks|key|1547963|text|如果要对表中的行进行永久编号，请不要使用SQL+Server的RID解决方案。它的性能将比在旧的386上访问更差。对于SQL+Server，只需创建一个IDENTITY列，并将该列用作聚集主键。这将在表上放置一个永久的、快速的Integer+B-Tree，更重要的是，每个非聚集索引都将使用它来定位行。如果您试图在SQL+Server中进行开发，就好像它是Oracle一样，那么您将创建一个性能很差的数据库。你需要针对引擎进行优化，而不是假装它是一个不同的引擎。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1547964|此外，请不要使用NewID()将GUID填充到主键中，这会降低插入性能。如果必须使用GUID，请使用NewSequentialID()作为列默认值。但是INT仍然会更快。|1547965|另一方面，如果您只是想对查询得到的行进行编号，那么可以使用RowNumber+Over()函数作为查询列之一。|1547966|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

If you want to permanently number the rows in the table, Please don't use the RID solution for SQL Server. It will perform worse than Access on an old 386. For SQL Server simply create an IDENTITY column, and use that column as a clustered primary key. This will place a permanent, fast Integer B-Tree on the table, and more importantly every non-clustered index will use it to locate rows. If you try to develop in SQL Server as if it's Oracle you'll create a poorly performing database. You need to optimize for the engine, not pretend it's a different engine.

also, please don't use the NewID() to populate the Primary Key with GUIDs, you'll kill insert performance. If you must use GUIDs use NewSequentialID() as the column default. But INT will still be faster. 

If on the other hand, you simply want to number the rows that result from a query, use the RowNumber Over() function as one of the query columns.

blocks|key|1332411|text|ROWID是Oracle表中的隐藏列，因此，对于SQL+Server，请构建自己的列。添加一个名为ROWID的列，其默认值为NEWID()。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1332412|如何实现：Add+column,+with+default+value,+to+existing+table+in+SQL+Server|1332413|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/92082/add-column-with-default-value-to-existing-table-in-sql-server^0|1Q|7|0|5|1R|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|S|8|@]|D|@$9|T|A|U|1|V]]|E|$]]|$1|H|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]]]

ROWID is a hidden column on Oracle tables, so, for SQL Server, build your own. Add a column called ROWID with a default value of <code>NEWID()</code>. 

How to do that: <a href="https://stackoverflow.com/questions/92082/add-column-with-default-value-to-existing-table-in-sql-server">Add column, with default value, to existing table in SQL Server</a>

blocks|key|1547908|text|请参阅SQL+server中的http://msdn.microsoft.com/en-us/library/aa260631(v=SQL.80).aspx时间戳与DateTime列不同。它用于唯一地标识数据库中的一行，而不仅仅是一个表，而是整个数据库。这可用于乐观并发。例如，在(ModifiedTimeStamp=@Original_ModifiedTimeStamp和GUID=@Original_GUID的位置更新作业集Name=@Name和XCustomData=@XCustomData|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1547909|ModifiedTimeStamp确保您正在更新原始数据，如果对行进行了另一次更新，它将失败。|1547910|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/aa260631(v=SQL.80).aspx^0|F|1R|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

Please see <a href="http://msdn.microsoft.com/en-us/library/aa260631(v=SQL.80).aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/aa260631(v=SQL.80).aspx</a>
In SQL server a timestamp is not the same as a DateTime column. This is used to uniquely identify a row in a database, not just a table but the entire database.
This can be used for optimistic concurrency. for example
UPDATE [Job] SET [Name]=@Name, [XCustomData]=@XCustomData WHERE ([ModifiedTimeStamp]=@Original_ModifiedTimeStamp AND [GUID]=@Original_GUID

the ModifiedTimeStamp ensures that you are updating the original data and will fail if another update has occurred to the row.

blocks|key|1332453|text|我从MS+SQL+example中取了这个例子，你可以看到@ID可以与integer或varchar或其他任何东西互换。这与我正在寻找的解决方案相同，所以我将其与大家分享。享受吧！！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1332454|--+UPDATE+statement+with+CTE+references+that+are+correctly+matched.
DECLARE+@x+TABLE+(ID+int,+Stad+int,+Value+int,+ison+bit);
INSERT+@x+VALUES+(1,+0,+10,+0),+(2,+1,+20,+0),+(6,+0,+40,+0),+(4,+1,+50,+0),+(5,+3,+60,+0),+(9,+6,+20,+0),+(7,+5,+10,+0),+(8,+8,+220,+0);
DECLARE+@Error+int;
DECLARE+@id+int;

WITH+cte+AS+(SELECT+top+1+*+FROM+@x+WHERE+Stad=6)
UPDATE+x+--+cte+is+referenced+by+the+alias.
SET+ison=1,+@id=x.ID
FROM+cte+AS+x

SELECT+*,+@id+as+'random'+from+@x
GO|code-block|syntax|javascript|1332455|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I took this example from MS SQL example and you can see the @ID can be interchanged with integer or varchar or whatever. This was the same solution I was looking for, so I am sharing it. Enjoy!!

<pre><code>-- UPDATE statement with CTE references that are correctly matched.
DECLARE @x TABLE (ID int, Stad int, Value int, ison bit);
INSERT @x VALUES (1, 0, 10, 0), (2, 1, 20, 0), (6, 0, 40, 0), (4, 1, 50, 0), (5, 3, 60, 0), (9, 6, 20, 0), (7, 5, 10, 0), (8, 8, 220, 0);
DECLARE @Error int;
DECLARE @id int;

WITH cte AS (SELECT top 1 * FROM @x WHERE Stad=6)
UPDATE x -- cte is referenced by the alias.
SET ison=1, @id=x.ID
FROM cte AS x

SELECT *, @id as 'random' from @x
GO
</code></pre>

blocks|key|1548102|text|您可以通过以下方式获取ROWID：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1548103|1.创建一个带有自动增量字段的新表|1548104|2.使用Row_Number分析函数根据您的需求获取序列。我更喜欢使用它，因为在您希望row_id以升序或降序方式显示特定字段或字段组合的情况下，它会很有帮助|1548105|Sample:Row_Number()+Over()(按深度分区不按sal描述排序)|1548106|上面的示例将根据每个department.Partition的最高工资给出序列号，可选，您可以根据需要删除它|1548107|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

You can get the ROWID by using the methods given below :

1.Create a new table with auto increment field in it 

2.Use Row_Number analytical function to get the sequence based on your requirement.I would prefer this because it helps in situations where you are you want the row_id on ascending or descending manner of a specific field or combination of fields

Sample:Row_Number() Over(Partition by Deptno order by sal desc)

Above sample will give you the sequence number based on highest salary of each department.Partition by is optional and you can remove it according to your requirements

blocks|key|1548125|text|请尝试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1548126|select+NEWID()|offset|length|style|CODE|1548127|来源：https://docs.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql|1548128|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql^0|0|0|E|0|3|2B|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@$D|T|E|U|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|V|8|@]|9|@$D|W|E|X|1|Y]]|A|$]]|$1|J|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]]]

Please try 

<code>select NEWID()</code>

Source: <a href="https://docs.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql</a>

What's the equivalent of Oracle's RowID in SQL Server?

Equivalent of Oracle's RowID in SQL Server

在SQL Server中，什么相当于甲骨文的RowID？

问相当于SQL Server中的Oracle的RowID
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问相当于SQL Server中的Oracle的RowIDEN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问相当于SQL Server中的Oracle的RowID
EN