Regarding portability, there's a pretty nice program over at www.spaceprogram.com that will migrate a db from any platform to any other platform while keeping the identity columns intact.
http://www.spaceprogram.com/products_prompand.html Travis ---- Original Message ---- From: [EMAIL PROTECTED] Sent: 2002-01-04 To: SQL <[EMAIL PROTECTED]> Subject: Re: Questions 1. I use identity columns all the time, unless the table is likely to have enough rows that I might hit the limit. In Microsoft SQL Server, identity columns are of datatype int, which means I would have to exceed 2 billion rows. However, that's more likely than it sounds, because the dbms won't reseed the identity column values if you delete rows. Someone made the comment that it makes your code non-portable. That is true to a certain extent, but in all my years of being a DBA and doing web development, I've never needed to switch platforms. If I ever do, it will be such a huge project that the conversion of identity columns will be the least of my worries. It is something to consider, but it's not like you'll be switching from Oracle to SQL Server on a weekly basis. Whenever I need an artificial key and I can't use an identity column, I use UUID values. MS SQL Server has a special datatype of uniqueidentifier that you can use, and you can use the SQL function NewID() as the default value for the column to provide identity-like functionality. If you ever have to convert from one platform to another, you can choose to go with either the binary or string form of the UUID data. 2. Basically, if you hit the limit of the identity column, you can't add any more rows to the table because you'll get an error returned. While I've never actually encountered the error, I suspect it will be a duplicate primary key violation. 3. When none of the columns are unique, or if they are unique but might change (those infamous "management directives" have caused that more than once), I go to point #1 and add an artificial key to the table. Why not just allow the unique columns to change? Laziness, complexity, interdependent systems outside the dB. Pick any two. |-----------------------------+-------------------------------------------| |Eric A. Laney |Nodding the head does not row the boat. | |Systems Engineer | | |LAN Optimization Team | | |Verizon Data Services | Irish Proverb| |Voice: 813.978.4404 | | |Pager: 888.985.8519 | | |-----------------------------+-------------------------------------------| |---------+---------------------------> | | "CT, Loo" | | | <[EMAIL PROTECTED]| | | my> | | | | | | 2002-01-03 22:18| | | Please respond | | | to sql | | | | |---------+---------------------------> >------------------------------------------------------------------------------------------------------------------------------| | | | To: SQL <[EMAIL PROTECTED]> | | cc: | | Subject: Questions | >------------------------------------------------------------------------------------------------------------------------------| Dear SQL gurus, I have some questions on database design and hope all experts out there can give me some ideas/advice. Please advise/comment on the followings:- 1. Should one use the identity field as primary key in designing a database? If yes, isn't it has a limitation when the number reaches its limits? 2. What would happens to the database when the identity field's value reaches its limits? 3. Any advice on selecting a primary key for a table where none of the columns has unique value? Thanks in advance. Kind regards, CTLoo ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
