|
|
 |
 |
 |
 |
About rownum in sql server
Hi Guys, In sql server there is not rownum like oracle, if I want delete rows less than a number, is there any efficent way to delete? Also, how about goto statement, is that ok to use it? I mean is there any limitation to use it? Thanks.
> Hi Guys, In sql server there is not rownum like oracle, if I want delete
> rows
> less than a number
Less than what number? What does rownum = 5000 mean? A
-----------------------------------------------Reply-----------------------------------------------
for example, I want to delete first 1000 rows in the table, how to do efficiently? "Aaron Bertrand [SQL Server MVP]" wrote:
> > Hi Guys, In sql server there is not rownum like oracle, if I want delete > > rows > > less than a number > Less than what number? What does rownum = 5000 mean?
> A
> for example, I want to delete first 1000 rows in the table,
How do you expect to identify the first 1000 rows? If you don't care which rows, then you can just say: SET ROWCOUNT 1000;
DELETE YourTable;
SET ROWCOUNT 0;
If you care which rows, then you must be able to identify them somehow. Do
you have a DATETIME column on the table maybe (e.g. Created_Date), or an
IDENTITY column?
A
-----------------------------------------------Reply-----------------------------------------------
On 9 Mar, 21:26, Iter <I @discussions.microsoft.com> wrote: > for example, I want to delete first 1000 rows in the table, how to do
> efficiently?
A table has no order so "first 1000" is illogical - both in Oracle and SQL Server - unless you have something that defines the order. ROWNUM without ORDER BY gives you an undefined result in Oracle - and it isn't standard SQL either. So let's assume the key of your table is key_col, you can delete the
first 1000 rows ordered by key_col as follows:
DELETE FROM tbl
WHERE key_col IN
(SELECT key_col
FROM
(SELECT key_col, ROW_NUMBER() OVER (ORDER BY key_col)
FROM tbl) AS t (key_col, row_num)
WHERE row_num <= 1000);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
-----------------------------------------------Reply-----------------------------------------------
On 9 Mar, 21:05, Iter <I @discussions.microsoft.com> wrote: > Hi Guys, In sql server there is not rownum like oracle, if I want delete rows
> less than a number, is there any efficent way to delete? Also, how about goto
> statement, is that ok to use it? I mean is there any limitation to use it?
> Thanks.
DELETE FROM tbl WHERE key_col <= (SELECT MAX(key_col) FROM (SELECT key_col, ROW_NUMBER() OVER (ORDER BY key_col) FROM tbl) AS t (key_col, row_num) WHERE row_num <= 1000); --
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
-----------------------------------------------Reply-----------------------------------------------
On Fri, 9 Mar 2007 13:05:13 -0800, Iter <I @discussions.microsoft.com> wrote: >Hi Guys, In sql server there is not rownum like oracle, if I want delete rows >less than a number, is there any efficent way to delete? Also, how about goto >statement, is that ok to use it? I mean is there any limitation to use it? >Thanks. A *lot* of SQLServer tables are built with identity columns that, among other things, give you the equivalent of a rownum. J.
-----------------------------------------------Reply-----------------------------------------------
On 10 Mar, 00:03, JXStern <JXSternChange @gte.net> wrote: > On Fri, 9 Mar 2007 13:05:13 -0800, Iter > <I@discussions.microsoft.com> wrote:
> >Hi Guys, In sql server there is not rownum like oracle, if I want delete rows
> >less than a number, is there any efficent way to delete? Also, how about goto
> >statement, is that ok to use it? I mean is there any limitation to use it?
> >Thanks.
> A *lot* of SQLServer tables are built with identity columns that,
> among other things, give you the equivalent of a rownum.
> J.
SQL Server's IDENTITY is nothing like equivalent to ROWNUM in Oracle. IDENTITY is a column property that causes a default value to be generated when rows are inserted. ROWNUM is a "pseudo-column" that behaves like a function and is computed at query time. ROWNUM is much closer to the ROW_NUMBER() function than an IDENTITY column. --
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
|
 |
 |
 |
 |
|
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc