Outside of the technical aspects and proposed work-around (using
VARCHAR(27) columns) discussed in @Joe's answer, I question the "need to create [a] wide denormalized table" as expressed by the O.P. Unless there is some odd technical requirement that all these columns must be in a single table, I would suggest / recommend spreading them out across as many "sibling" tables as necessary. Sibling tables being tables that:
- have a 1-to-1 relationship with each other,
- all have the exact same Primary Key,
- only one has the
IDENTITY column (and no FK to the others)
- the rest have a Foreign Key (on the PK column) pointing to the PK of the table that has the
Here you are splitting he logical row across two or more physical tables. But that is essentially what normalization is anyway, and what relational databases are designed to handle.
In this scenario you do incur some extra space used by duplicating the PK, and some additional query complexity due to the need to either
INNER JOIN the tables together (frequently but not always, unless all
SELECT queries use all columns, but that doesn't usually happen) or create an explicit Transaction to
UPDATE them together (
DELETE can be handled via
ON DELETE CASCADE set on the FK).
HOWEVER, you get the benefits of having a proper data model with proper, native datatypes, and no trickery that could have unforeseen consequences later on. Even if using
VARCHAR(27) allows this to work on a technical level, pragmatically I don't think storing decimals as strings is in your / the project's best interest.
So, if you are only "needing" a single table due to not realizing that a single logical entity does not need to be represented physically in a single container, then do not attempt to force all of this into a single table when it will work gracefully across multiple tables.
The example below illustrates the basic concept:
CREATE TABLE tempdb.dbo.T1
[ID] INT NOT NULL IDENTITY(11, 2) PRIMARY KEY,
[Col2] DATETIME NOT NULL DEFAULT (GETDATE())
CREATE TABLE tempdb.dbo.T2
[ID] INT NOT NULL PRIMARY KEY
FOREIGN KEY REFERENCES tempdb.dbo.T1([ID]) ON DELETE CASCADE,
CREATE PROCEDURE #TestInsert
SET NOCOUNT ON;
DECLARE @InsertedID INT;
INSERT INTO tempdb.dbo.T1 ([Col1])
SET @InsertedID = SCOPE_IDENTITY();
INSERT INTO tempdb.dbo.T2 ([ID], [Col3], [Col4])
VALUES (@InsertedID, NEWID(), @Val4);
IF (@@TRANCOUNT > 0)
SELECT @InsertedID AS [ID];
EXEC #TestInsert 'aa', 454567678989;
EXEC #TestInsert 'bb', 12312312312234;
INNER JOIN tempdb.dbo.T2
ON T2.[ID] = T1.[ID];
ID Col1 Col2 ID Col3 Col4
11 aa 2017-07-04 10:39:32.660 11 44465676-E8A1-4F38-B5B8-F50C63A947A4 454567678989
13 bb 2017-07-04 10:41:38.180 13 BFE43379-559F-4DAD-880B-B09D7ECA4914 12312312312234
DECIMAL(26, 8) NULLfields into a table, without page compression or decimal compression. Enabling vardecimal but not page compression, the overhead jumps to over 1 K. There's an outside chance that you'll be able to store more fields per page without vardecimal, depending on your values.