Small identity columns

Identity
Identity
We frequently talk about dealing with outgrowing INT identity columns. What we don’t talk about all that often is small tables. Where we don’t even need an INT. An IDENTITY column can be any of the following data types tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0). INT and BIGINT we talk about all the time, so how about the others? Let’s start with some information about them, shall we?

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.

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.

54321
(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *