 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008BigInt datatype
In of my tables, 4000 rows are added everytime an insertion is made. I want to add a RowID field of datatype "bigint" so that while deleting I can pick the RowID and delete a row. I don't want to keep this RowID field a Primary Key. There are thre other columns that are combinedly forming Primary Key. I want to know whether "bigint" data type selection is good in this case as after some years the RowID sizes may get considerably bigger. I am afraid if the system crashes at that time. RP For some years I think INT will be ok as well :-)))) > In of my tables, 4000 rows are added everytime an insertion is made. I > want to add a RowID field of datatype "bigint" so that while deleting [quoted text clipped - 5 lines] > case as after some years the RowID sizes may get considerably bigger. > I am afraid if the system crashes at that time. Only you can answer that. Books Online documents the range for both int as well as bigint. You need to estimate how many rows you will add over time and based on that consider whether int is enough for the foreseable future or if you need to go with bigint.  SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > In of my tables, 4000 rows are added everytime an insertion is made. I > want to add a RowID field of datatype "bigint" so that while deleting [quoted text clipped - 5 lines] > case as after some years the RowID sizes may get considerably bigger. > I am afraid if the system crashes at that time. > > I want to know whether "bigint" data type selection is good in this > > case as after some years the RowID sizes may get considerably bigger. > > I am afraid if the system crashes at that time.- Hide quoted text - Here are the sizes: INTEGER or INT - A 32-bit signed integer value. The range of INTEGER is -2147483648 to 2147483647. BIGINT - A 64-bit signed integer value. The range of BIGINT is -9223372036854775808 to 9223372036854775807. Int is probably big enough for most applications having 2 billion positive values. You could seed it at the minvalue if there was any concern and start from the bottom end and work up. :o) If for some reason neither of these will work, the UniqueIdentifier datatype is probably the one you'll want to use. If the value isn't a primarykey, then changing the value out won't be an issue in the future either. Start with INT. In 10 years convert it to a BIGINT. And 30000 years after that convert it to UniqueIdentifier. :o) Seriously, only you know how much data will be in this table, so only you can calculate which datatype will do the trick. -Eric Isaacs Why not use the primary key for deletes? Adding a surrogate key makes sense if it will be used for joins, but just for deletes doesn't seem to provide any benefits. You would have to add unique constraint to maintain data integrity, which drags behind maintenance cost for the unique index used to enforce the constraint. Plamen Ratchev http://www.SQLStudio.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.