Sql Server Bigint or Decimal(18,0) for Primary Key

SQL Server BIGINT or DECIMAL(18,0) for primary key

DATALENGTH is casting to varchar before counting bytes. So your max value is < 100000.

The 9 bytes can be proved with this. sys.columns has a max_length column (decimal is fixed length so it is always 9 bytes, before you ask otherwise)

CREATE TABLE dbo.foo (bar decimal(18,0))
GO
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('foo') 
GO
DROP TABLE dbo.foo
GO

For legacy reasons, decimal(18, 0) was often used as a surrogate for "64 bit integer" before bigint was added with SQL Server 2000.

decimal(18, 0) and bigint are roughly the same in range: decimal is one byte more at 9 bytes as per the documentation

On top of that, plain integer will be fractionally (maybe not measurable) faster then decimal. Saying that, if expect to have more then 4 billion rows in the next year or 5, then the performance should matter. If it doesn't, then just use int

choosing primary key datatype numeric (18,0)

I would guess that the SQL Server database was originally designed in a version prior to SQL Server 2000, and this was the only way they could get an 'integer' bigger than the standard int. Since then, a bigint would have been more appropriate.

How can we create primary key as a decimal data type, and can it be auto increment

You may have a decimal as a primary key, but it must have a scale of 0.

SQL Server will handle the auto-incrementing.

Example:

CREATE TABLE my_table (
     id decimal(18,0) identity primary key, 
     some_column varchar(200)
);

Reference: http://beyondrelational.com/modules/2/blogs/28/posts/10331/sql-server-what-are-the-data-types-supported-in-identity-columns.aspx

See your master plan in action here: http://sqlfiddle.com/#!3/e491d/1/0

What is the best datatype to store 18 digit numeric value in MySQL

Use BIGINT UNSIGNED. It can support at least 19 digits of precision. The maximum value is 18446744073709551615.

There's no advantage to using DECIMAL, FLOAT, or DOUBLE in this case, because you say the values have no scale.

But you do need 18 digits of precision, so the rounding behavior of FLOAT/DOUBLE may not do what you need.

What is the difference between numeric(19,0) and bigint in POSTGRES?

BIGINT range is -9223372036854775808 to 9223372036854775807, so you can't store a number greater than 9223372036854775807, but NUMERIC (19, 0) can do.

Please find the following example:

CREATE TABLE TestTable (NumVal NUMERIC (19,0), IntVal BIGINT);

INSERT INTO TestTable (NumVal, IntVal) VALUES
('9223372036854775808', 9223372036854775807);

SELECT * FROM TestTable;

Here you can't store 9223372036854775808 in to BIGINT, but you can store the same value or greater than the value to NUMERIC (19, 0)

db<>fiddle for the same.

Bigint vs varchar datatype. Should I match source system?

Your data warehouse shouldn't be using the Business Keys as the primary identifier regardless of the data type. Data Warehouses should use Surrogate Keys that are unique to the Data Warehouse, so that you can handle situations where you need to have multiple instances of the same Dimensional data, want to keep a history of the Dimensional data, the source system re-uses a now deleted Business Key or any number of other instances where the original Business Key causes problems in your DWH.

Personally, I would implement Surrogate Keys for your existing DWH tables and then you will have no issues incorporating the new data alongside the old.

SQL Identify Auto Increment from a certain number

No. BigInt is a 8 byte integer value.
Note: Assuming as Microsoft SQL Server.

---

Related Topics