Row ID trigger

Anyone knows how to make a good trigger to create a row id on a table every time a new row is inserted? This is to maintain row id sequence by not setting the row id field as an auto identity which may be in broken sequence after a while.
Thanks.just use something like "select max(rowID)+1" in the update trigger. Although you'll have to careful of your isolation level.|||I tried what you said in INSERT trigger and it did'nt work.
Why?

Thanks.|||What's to know about isolation level?
Thanks.|||If you have a low isolation level then, potentially, two inserts would get the same result for select max()

What "doesn't work"?|||How can the low isolation level be taken care of?

Sorry, I tested the trigger in enterprise manager and at first didn't see the rowid appear until I refreshed the table, which made me believe the trigger wasn't working.

Thanks.|||"which may be in broken sequence after a while. "

Why do you think so, IMHO, it cannot be broken. If you don't like identity you can use
so-called "table with current id's for all tables" and update it through sps or udfs|||You need to have a quick read of the isolation levels. Basically the lower the level the less database locks you have - although that means you have more concurrency. The higher the level the more locks you have so the "safer" code like select max() becomes - but your concurrency drops. You can influence the isolation level using commands such as "SET ISOLATION LEVEL" or by using query hints. If you're using the select max() technique then I'd suggest you look at the UPDLOCK hint.|||Could you explain how to implement a UDF on the rowid field to update it at each insertion? I tried and failed to implement a UDF on the field but succeeded with a trigger.
Thanks.|||>> Could you explain how to implement a UDF on the rowid field to update it at each insertion? I tried and failed to implement a UDF on the field but succeeded with a trigger.

How are you trying to use a UDF? They're just a way of batching SQL, they still need to be called...like a stored procedure. So you'd still need to put them in a trigger.

>> how can they get out of sequence
Easily! Insert, rollback, Insert. Where can I buy a MCSDBA for $2 please?

Row ID

Does SQL maintain it's own Row ID's for records in a table? I was wondering
if there was a row id property that I could query on because the particular
table does not have any columns of type identifier or guid. If there is su
ch a property, what is the
correct syntax to use in the where clause?
Thanks in advanceSQL doesn't expose any physical row identifiers for use in queries. The way
to uniquely identify a row is, of course, by its primary key. If your table
doesn't have a primary key then you should add one.
David Portas
--
Please reply only to the newsgroup
--|||SQL Server does maintain a row id, but in the physical model which cannot be
accessed directly using a t-SQL query.
To access a row in a table using a SQL query you must have an set of column
values in a row which can uniquely identify a row in your table. The correct
approach to do this would be to define a primary key in your table; then
you'll be able to use these key column(s) in your WHERE clause.
- Anith
( Please reply to newsgroups only )|||Good thing Joe Celko doesn't frequent this group
"liz" <anonymous@.discussions.microsoft.com> wrote in message
news:2B33F68F-3091-4162-9A60-60BC2D599260@.microsoft.com...

quote:

---

> Does SQL maintain it's own Row ID's for records in a table? I was

---

wondering if there was a row id property that I could query on because the
particular table does not have any columns of type identifier or guid. If
there is such a property, what is the correct syntax to use in the where
clause?

quote:

---

> Thanks in advance

---

|||"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
news:#Hzqcz42DHA.536@.tk2msftngp13.phx.gbl...

quote:

---

> Good thing Joe Celko doesn't frequent this group

---

Who?

Row ID

Does SQL maintain it's own Row ID's for records in a table? I was wondering if there was a row id property that I could query on because the particular table does not have any columns of type identifier or guid. If there is such a property, what is the correct syntax to use in the where clause
Thanks in advanceSQL doesn't expose any physical row identifiers for use in queries. The way
to uniquely identify a row is, of course, by its primary key. If your table
doesn't have a primary key then you should add one.
--
David Portas
--
Please reply only to the newsgroup
--|||SQL Server does maintain a row id, but in the physical model which cannot be
accessed directly using a t-SQL query.
To access a row in a table using a SQL query you must have an set of column
values in a row which can uniquely identify a row in your table. The correct
approach to do this would be to define a primary key in your table; then
you'll be able to use these key column(s) in your WHERE clause.
--
- Anith
( Please reply to newsgroups only )|||Good thing Joe Celko doesn't frequent this group :)
"liz" <anonymous@.discussions.microsoft.com> wrote in message
news:2B33F68F-3091-4162-9A60-60BC2D599260@.microsoft.com...
> Does SQL maintain it's own Row ID's for records in a table? I was
wondering if there was a row id property that I could query on because the
particular table does not have any columns of type identifier or guid. If
there is such a property, what is the correct syntax to use in the where
clause?
> Thanks in advance|||"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
news:#Hzqcz42DHA.536@.tk2msftngp13.phx.gbl...
> Good thing Joe Celko doesn't frequent this group :)
Who?