Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Time for a ROWID? Expand / Collapse
Author
Message
Posted Tuesday, July 26, 2011 9:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:35 AM
Points: 33,102, Visits: 15,213
Comments posted to this topic are about the item Time for a ROWID?






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1148795
Posted Tuesday, July 26, 2011 11:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 5, 2012 3:30 AM
Points: 138, Visits: 351
In my database designs my default choice for all tables is for them to have a surrogate key, a TIMESTAMP togethe with Last Modified UserID. For the surrogate key I mostly use IDENTITY, but in one project, with offline datasets, I have used GUIDs.
This surrogate key is used as the Clustered Primary Key. I name those fields ID (identity), TS (time stamp) and US (user stamp) in all tables. The data access layer of my application uses this for some low-level plumbing in managing the data on behalf of the business layer. Besides those fields tables containing top-level business objects (customer, order, etc) will have a unique key (mostly a human-friendly code, either textual or numeric, with appropriate index and constraints).
I don't even think about primary/foreign key methods when adding new functionality to the business layer: the underlying tables will get the above mentioned fields. The users will never see these values and their unique keys could be updated without breaking any relations in the schema (I learned the hard way that the cost to build functionality (coding a cascading update over the foreign keys, having to maintain this code when foreign keys are added, performance if a very large set is updated) for the user being able to occasionally make changes to their customer codes etc, is far outweighed by the small overhead of having a fairly static separate index on this separate key-field.

I consider identifying and tracking (auditing) as part of the data-storage solution and any implementation of features I no longer have to worry about in my application developments are welcome to me (in the end I am an application architect/developer).

So, if the database-server provides these functions (ROWID provides just one of them, but rudimentary auditing using timestamps and last-modified-by-user-account as well) I would seriously considering updating my schema and data-access layer of the application to make use of them sooner rather than later.

Only very occasionally I create a table that deviates from this pattern, mostly when it doesn't contain business-data, but some other arbitrary data and only when those fields would actually hamper some other application-requirement (performance, space-requirments, etc). So it would be nice if these featured could be turned off when I wanted to, but would be there by default.
Post #1148841
Posted Wednesday, July 27, 2011 12:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 10, 2012 5:51 AM
Points: 2, Visits: 17
Little remark... All non-unique non-clustered indexes are created with a sysem internal unique id, 8 bytes in size, so that sql could differentiate each row. These id "columns" aren't visible or accessible.

If they would have to do anything they could just make this column available for use without modifying. Whatever the column's value, it is good enough for any task (it doesn't have to be "pretty" like a IDENTITY(1,1) column is).
Post #1148849
Posted Wednesday, July 27, 2011 1:25 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:12 AM
Points: 1,610, Visits: 5,482
I'm not sure increasing the row length in SQL would help much--after all, the largest data types are stored in off-row data pages anyway, and a properly normalised database surely shouldn't need so many of the smaller data types that you'd need to increase the row length?
Post #1148869
Posted Wednesday, July 27, 2011 1:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 5:18 AM
Points: 28, Visits: 178
Larger pages sound kind of inevitable at *some* point in the future, and I can't imagine they'd change the basic concepts we deal with.

However, ROWIDs would be a different kettle of fish. For one thing, introducing a user-visible "pointer" might destroy the distinction between heaps and clustered indexes. SQL Server RIDs are hidden for a reason: they're an implementation detail and exposing them would severely limit the amount of data reorganisation that can be done under the covers; for clustered indexes I don't believe they even get used much internally (CheckDB is probably an exception).

One could turn the argument sideways and propose that all tables, whether heaps or clustered indexes, should always get an automatically-generated bigint identity column, whether you want it or not. It would certainly force a more consistent design vocabularly among users if that were the case, but it would be sure to have many detractors. Ultimately I guess we all just want the freedom to shoot ourselves in the foot with our own choice of weapon instead of an officially mandated one!
Post #1148886
Posted Wednesday, July 27, 2011 1:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 5, 2012 3:30 AM
Points: 138, Visits: 351
paul.knibbs (7/27/2011)
I'm not sure increasing the row length in SQL would help much--after all, the largest data types are stored in off-row data pages anyway, and a properly normalised database surely shouldn't need so many of the smaller data types that you'd need to increase the row length?

I don't think Steve was thinking of allowing for larger records, but rather lowering overhead-costs associated with managing fewer pages in the page-pool if the I/O costs for loading a larger page is negligible on modern hardware.
Microsoft would have to do some bench-marking to see if the vast majority of applications running on typical hardware would not be negatively impacted by a larger page-size if top-end hardware may benefit. Maybe even allow the sysadmin to choose the optimum page-size for his hardware when installing the server (if Microsoft did a good job with their source-code, they may be able to compile the server-kernel for different page-sizes).
Post #1148888
Posted Wednesday, July 27, 2011 2:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 42,495, Visits: 35,566
the.komplikator (7/27/2011)
Little remark... All non-unique non-clustered indexes are created with a sysem internal unique id, 8 bytes in size, so that sql could differentiate each row. These id "columns" aren't visible or accessible.


Errr, no...

Non-unique clustered indexes get a uniquifier, which is 4 bytes (an int) added to rows that have duplicate values for the clustering key.
Nonclustered indexes get either the clustering key (complete with uniquifier) if the base table has a clustered index, or the 8-byte RID (file_id, page_id, slot_index) if the base table is a heap.
If the nonclustered index is unique, that's present only in the leaf level, if the nonclustered index is not unique that is present in the leaf and non-leaf levels.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1148896
Posted Wednesday, July 27, 2011 3:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 10, 2012 5:51 AM
Points: 2, Visits: 17
GilaMonster (7/27/2011)

Errr, no...

Non-unique clustered indexes get a uniquifier, which is 4 bytes (an int) added to rows that have duplicate values for the clustering key.
Nonclustered indexes get either the clustering key (complete with uniquifier) if the base table has a clustered index, or the 8-byte RID (file_id, page_id, slot_index) if the base table is a heap.
If the nonclustered index is unique, that's present only in the leaf level, if the nonclustered index is not unique that is present in the leaf and non-leaf levels.


Right. No more early-in-the-morning posts for me :)
Post #1148911
Posted Wednesday, July 27, 2011 6:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 5:18 AM
Points: 28, Visits: 178
Back on the secondary subject of larger pages, I guess the obvious argument against them would be buffer cache wastage. If you have hundreds of often-used lookup/dimension tables with only a few narrow rows in each, quite a lot of buffer cache will essentially be useless, with the wasted amount being proportional to the page size. A similar argument will apply where there have been lots of page splits.
Post #1149083
Posted Wednesday, July 27, 2011 7:08 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 5, 2014 12:21 PM
Points: 78, Visits: 193
We also use a unique record ID field in many of our tables. Very handy for updates and confidence that you are updating the exact record you just selected. Does this not serve the same purpose as a 'ROWID'?

Concerning increasing the row length, I'm reminded of the flat files on the mini-computer back in 1982 which I started on. I thought part of the idea of a "relational" database was to decrease row length for faster access and flexibility. However, I admit I am not an expert on the guts of SQL.








Wallace Houston
Sunnyland Farms, Inc.

"We must endeavor to persevere."
Post #1149122
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse