Sure, there is a function in SQL Server 2005 called row_number()
Here's an example:
WITH [EMPLOYEE ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE)
SELECT * FROM [EMPLOYEE ORDERED BY ROWID] WHERE ROWID =4
I'd advise looking up the documentation for it, as there are several use cases.
No there is no direct hidden column like Oracle having (rowid)
this is what we do:
We manually added a column with name as ROWID and set its datatype to timestamp, which is a database-wide unique number that gets updated every time a row gets updated.
Note that timestamp get updated, when you update that row, but in Oracle the rowid does not change, when we update.
Gurpreet S. Gill
you can use a Globally unique identifier(GUID) and the newid function
B. Use NEWID in a CREATE TABLE statement
This example creates cust table with a uniqueidentifier data type, and uses NEWID to fill the table with a default value. In assigning the default value of NEWID(), each new and existing row has a unique value for the cust_id column.
-- Creating a table using NEWID for uniqueidentifier data type. CREATE TABLE cust ( cust_id uniqueidentifier NOT NULL DEFAULT newid(), company varchar(30) NOT NULL, contact_name varchar(60) NOT NULL, address varchar(30) NOT NULL, city varchar(30) NOT NULL, state_province varchar(10) NULL, postal_code varchar(10) NOT NULL, country varchar(20) NOT NULL, telephone varchar(15) NOT NULL, fax varchar(15) NULL ) GO -- Inserting data into cust table. INSERT cust (cust_id, company, contact_name, address, city, state_province, postal_code, country, telephone, fax) VALUES (newid(), 'Wartian Herkku', 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', NULL, '90110', 'Finland', '981-443655', '981-443655') INSERT cust (cust_id, company, contact_name, address, city, state_province, postal_code, country, telephone, fax) VALUES (newid(), 'Wellington Importadora', 'Paula Parente', 'Rua do Mercado, 12', 'Resende', 'SP', '08737-363', 'Brazil', '(14) 555-8122', '') INSERT cust (cust_id, company, contact_name, address, city, state_province, postal_code, country, telephone, fax) VALUES (newid(), 'Cactus Comidas para Ilevar', 'Patricio Simpson', 'Cerrito 333', 'Buenos Aires', NULL, '1010', 'Argentina', '(1) 135-5555', '(1) 135-4892') INSERT cust (cust_id, company, contact_name, address, city, state_province, postal_code, country, telephone, fax) VALUES (newid(), 'Ernst Handel', 'Roland Mendel', 'Kirchgasse 6', 'Graz', NULL, '8010', 'Austria', '7675-3425', '7675-3426') INSERT cust (cust_id, company, contact_name, address, city, state_province, postal_code, country, telephone, fax) VALUES (newid(), 'Maison Dewey', 'Catherine Dewey', 'Rue Joseph-Bens 532', 'Bruxelles', NULL, 'B-1180', 'Belgium', '(02) 201 24 67', '(02) 201 24 68') GO
No, that's not the same as Oracle's ROWID feature. In Oracle, every table has a column called "ROWID" which is automatically created and maintained by the server. It's soft of the same idea as a SQL Server IDENTITY column, but it's automatically added to the table by the server.
Pro SQL Server 2005, available now
--<kevin delafield@discussions..microsoft.com> wrote in message news:firstname.lastname@example.org...
sql server 2005 definitely has a row id feature.
i've actually used it for paging application data.
see my post above.
I want to add
i) Having uniqueidentifier datatype, which we need to add manually as
ROWID uniqueidentifier NOT NULL DEFAULT newid()
this is unique in the whole database as of GUID.
II) Having timestamp datatype, which we need to add manually as
in the first option the GUID remains constant, but in the second case the timestamp changed whenever the update command is executed for that row.
i) ORACLE dont need these things, it having Pseudo coloumn called ROWID.
this is unique in the whole database.
so there is no direct pseudo or hidden column in sql server to do the same as rowid for oracle.
we manually need to add column to the table, to do the rowid work, depending upon the type of rowid we can select the case I or II for SQL server.
we are facing the same problem, as we were shifting form SQL Base to ORACLE as well as SQL Server, so for SQL Server we use the timestamp datatye.
Gurpreet S. Gill
In SQL Server 2000, you can acheive this by following query
SELECT (SELECT COUNT(i.au_id)
FROM pubs..authors i
WHERE i.au_id >= o.au_id ) AS RowID,
au_fname + ' ' + au_lname AS 'Author name'
FROM pubs..authors o
ORDER BY RowID
but this is not advisable, for the query which will retrive lots of records, if you want for display purpose then try to get the same via front end.
Both these pseudo-columns are not available in SQL Server. Oracle pseudo-columns do not take additional space in the database and only materialized at the time of query execution. If you want mimic something like rowid you will have to persist it. So you will have additional storage cost, possibly index storage etc. You can also get something like row id using DBCC PAGE command it is not what you are looking for and it is not in any easy queryable format. Others have posted about various techniques to get the information.But the real question is what are you doing with something like rownum or rowid? Are you using rownum for paging results? If so, then there are techniques using IDENTITY column or TOP which will perform faster in SQL Server. The ROW_NUMBER solution might be straight-forward to convert but it is not the best in terms of performance. As for rowid, it is hard to say. If you are using it as a key then it is wrong usage because you need to model data based on the natural keys and keys that you can rely on. Physical locators can change if the table is recreated or moved or reorganized for example. So please explain your usage further to suggest the correct migration strategy.
The application paging is the issue, though. We need to show a page full of records like this:
1 record one
2 record two
3 record three
The example given above works but in reverse order. Can anyone fix it? See below
create table mytab (id int)
insert into mytab values(1)
insert into mytab values(5)
select (select count(i.id) from mytab i where i.id >= o.id) as rownum, o.id as id from mytab o order by rownum