Delete the 'first' record from a table in

Question

Is it possible to delete the 'first' record from a table in SQL Server, without using any WHERE condition and without using a cursor?

Answer 1

Define "First"? If the table has a PK then it will be ordered by that, and you can delete by that:

DECLARE @TABLE TABLE
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Data NVARCHAR(50) NOT NULL
)

INSERT INTO @TABLE(Data)
SELECT 'Hello' UNION
SELECT 'World' 

SET ROWCOUNT 1
DELETE FROM @TABLE
SET ROWCOUNT 0

SELECT * FROM @TABLE

If the table has no PK, then ordering won't be guaranteed...

Answer 2

What do you mean by «'first' record from a table» ? There's no such concept as "first record" in a relational db, i think.

Using MS SQL Server 2005, if you intend to delete the "top record" (the first one that is presented when you do a simple "*select * from tablename*"), you may use "delete top(1) from tablename"... but be aware that this does not assure which row is deleted from the recordset, as it just removes the first row that would be presented if you run the command "select top(1) from tablename".