| Data Type | Range | Size |
| TINYINT | 0 to 255 | 1 Byte |
| SMALLINT | -2^15 (-32,768) to 2^15-1 (32,767) | 2 Bytes |
| DECIMAL/NUMERIC(1-9,0) | See below | 5 Bytes |
| DECIMAL/NUMERIC(10-19,0) | See below | 9 Bytes |
| DECIMAL/NUMERIC(20-28,0) | See below | 13 Bytes |
| DECIMAL/NUMERIC(29-38,0) | See below | 17 Bytes |
The number of values available with a NUMERIC or DECIMAL is based on the precision. The precision is the number of columns available in the number. So when p = 2 (NUMERIC(2,0)) then the values are -99 to 99. When p = 7 (NUMERIC(7,0)) then the values are -9,999,999 to 9,999,999. Honestly, the size to number of values for NUMERIC and DECIMALs isn’t really worth it for non-decimal values. So let’s concentrate on TINYINT and SMALLINT.
So, we CAN use TINYINT or SMALLINT for an identity column. But should we?
As an example let’s look at the SalesHeader table from AdventureWorks2014. To be fair, this is a big table, and AdventureWorks is fairly well designed. I don’t expect much, but let’s see.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
CREATE TABLE [Sales].[SalesOrderHeader]( [SalesOrderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [RevisionNumber] [tinyint] NOT NULL, [OrderDate] [datetime] NOT NULL, [DueDate] [datetime] NOT NULL, [ShipDate] [datetime] NULL, [Status] [tinyint] NOT NULL, [OnlineOrderFlag] [dbo].[Flag] NOT NULL, [SalesOrderNumber] AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***')), [PurchaseOrderNumber] [dbo].[OrderNumber] NULL, [AccountNumber] [dbo].[AccountNumber] NULL, [CustomerID] [int] NOT NULL, [SalesPersonID] [int] NULL, [TerritoryID] [int] NULL, [BillToAddressID] [int] NOT NULL, [ShipToAddressID] [int] NOT NULL, [ShipMethodID] [int] NOT NULL, [CreditCardID] [int] NULL, [CreditCardApprovalCode] [varchar](15) NULL, [CurrencyRateID] [int] NULL, [SubTotal] [money] NOT NULL, [TaxAmt] [money] NOT NULL, [Freight] [money] NOT NULL, [TotalDue] AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))), [Comment] [nvarchar](128) NULL, [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL, [ModifiedDate] [datetime] NOT NULL) |
For a total of 520 bytes per row. Note: This is just the total of the column sizes. There are several other things that come into play when calculating the actual required space for a given row.
The columns that are related to another table with an Identity column are:
Continue reading at SQLStudies.com.
Continue reading on SQLStudies.com.