March 15, 2005 at 3:02 pm
hi all
it's a long story but I'm trying to find out what some of the pitfalls are in using ROWID to identify a particular record in a table (why not use a PKey? there isn't one - just messy combinations of unique indexes...).
I'm a bit wary of relying on the physical location of a record in a table - it flies in the face of Codd's theories. but it's lame schemas like this one that I'm sure MS is copying Oracle's lead and including it.
any suggestions, comments, 2 cents worth, etc most welcome.
thanx
barry.b
March 15, 2005 at 10:01 pm
forgive me, my bad
seems that I've muddled RowID with (RowNum similar to the pseudo-column ROWNUM in Oracle)
(but I could sworn I read about RowId on the SQLServerCentral daily update...)
sorry ppl
March 16, 2005 at 5:51 am
Barry,
It seems as though you're referring to the new ROW_NUMBER() function that is/will be provided in SQL Server 2005.
I don't know of any pitfalls to using it. You're right that it flies in the face of relational theory however it is still useful if (for example) you are ordering the records - a common occurence of course.
Its particularly useful in your case where there has been a bad schema design.
In your situation I would be wary of using it without an OVER clause because then you would be relying on SQL Server always returning the rows in the same order - the unique indexes would play havoc here.
If you want to know more about ROW_NUMBER(), I've written a bit about it here: http://blogs.conchango.com/jamiethomson/archive/2005/02/16/1025.aspx
Hope this helps!
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
March 22, 2005 at 11:09 pm
Correct me if I'm wrong here, but didn't you say that you have a number of unique keys on the same table? Does that not then imply multiple candidate keys with the designer stopping short of assigning a PK. Why bother with a physical location of a row when you have potentially multiple ways to retrieve a single specific row? Why wasn't the PK assigned? I have run into cases where the person who created the database created unique indexes instead of PKs merely because that person didn't want the indexes clustered and had the impression that all PKs in MS SQL Server had to be clustered. In that case it was merely a lack of knowledge on the person's part that resulted in a database with no PKs defined.
Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply