Decimal Identity Column
-
I have inherited a SQL server system. Many of the tables use a decimal(19,0) data type for the identity column instead of the integer data type.
What are the advantages/disadvantages with using the decimal data type instead of the integer data type for the identity column?
Thanks
-
The advantage is a larger range of possible values before you reach the max possible value for the data type.
The disadvantage is increased storage space in table and index pages, and the associated increased disk I/O and query cost this creates.
Do you know the history of how the DB was designed ? It's possible the table DDL was created by a data modelling tool and Decimal was set as a default type - could be that the only reason it is Decimal (versus Int or Bigint) is that no-one really questioned what the data modelling tool was generating. I've seen that happen several times.
-
It is also possible that the database was originally developed in SQL Server 6.5 or 7.0 before the BIGINT datatype was available.
-
Would it be advantagous to carefully convert the identity columns to BIGINT?
-
The storage size of the decimal(19,0) is 9 bytes vs. 8 bytes for bigint, so it would save some storage space, but you wouldn't save that much space unless the table is very large. If you can convert to int, you might get a bigger savings in space.
I haven't done any testing of indexes on bigint vs. decimal(19,0), but my guess it that the performance difference would be minor.
I doubt that the performance difference would be noticeable.
-
From Books On line, for three data types, the storage bytes and maximum value are listed below. Also provided are the number of years before the maximum value is reached, based on needing a new value each second - 31,536,000 seconds per year.
Data type: Decimal(19,0)
Bytes: 9
Maximum: 9,999,999,999,999,999,999
Years: 317,097,919,837
Data type: BigInt
Bytes: 8
Maximum: 9,223,372,036,854,775,807 ( 2^63-1 )
Years: 292,471,208,677
Data type: Integer
Bytes: 4
Maximum: 2,147,483,647
Years: 68
Even at 10 inserts per second, the integer datatype will be adequate for almost 7 years at which time, you will need to reassign the identify values.
There would be a significant performance advantage with integer instead of Decimal(19,0) because of the 4 instead of 9 bytes of storage but not a great difference between BigInt and Decimal(19,0) (8 versus 9 bytes of storage)
SQL = Scarcely Qualifies as a Language
-
You must have a really old copy of BOL, Carl
Here's a copy of what BOL says about the decimal data type... decimal and numeric
Numeric data types with fixed precision and scale.
decimal[(p[, s])] and numeric[(p[, s])]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).
p (precision)
Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38.
s (scale)
Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.
Precision Storage bytes 1 - 9 5 10-19 9 20-28 13 29-38 17 --Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply