Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

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:

> 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