Can I use NUMERIC instead of BIGINT?

Question

I have an old database table with column, which type is BIGINT. There's a lot of stored procedures and views that use that table and that column.

For some reason I need to change the type of that column to NUMERIC(38,0).

Is it safe to do it? Should I cast in any stored procedure and view existing BIGINT to NUMERIC(38,0)?

Answer 1

According to me numeric data type is identical with decimal which represents a fixed precision number, which will scale numeric data from -10^38 +1 through 10^38 –1

I don't think that the number types you mention are using fixed precision number and therefore BIGINT is probably the most efficient way to store the number especially if you want to perform some computation in your application.

I don't see really any use for computation with those number and therefore you may even use a string of appropriate length which requires more space in the database but you may be able to allow grouping characters in the numbers.

using BIGINT datatype instead of string you can create efficient indexes.

As you write you're already using numeric datatype and therefore if you upgrade to SQL 2008R2 / 2012 you should consider switching to BIGINT as you don't need fraction in your number. The BIGINT data type is intended for use when integer values might exceed the range that is supported by the int data type.

EDIT:

You can change the data type from BIGINT to NUMERIC(38,0) but be ensure that a Arthimetic overflow error shouldn't occur while converting.

Answer 2

Yes, it is.

According to this table on MSDN an numeric(38,0) has an higher capacity than a bigint.

I calculated the maximum values based on the numbers in the matrix:

9223372036854775808 (bigint, 2^63-1, 8 bytes)

1000000000000000000000000000000000000000 (numeric(38,0), 10^38–1, 17 bytes)