Declare DataType When Creating Computed Column in SQL Server

When creating a computed column in SQL Server, be sure to use CONVERT (or CAST) to specify the datatype of the computed column, otherwise SQL Server will make the choice for you. It may not make the choice that you expected.

Here is a very simple example that shows a computed column with two possible values: 0 and 1. With no covert, SQL Server defaults to int. By using convert, we can use bit. This simple example shows that using CONVERT drops the storage requirement from 4 bytes to a single bit.

Code

SET NOCOUNT ON;USE tempdb;GOIF EXISTS (SELECT * FROM sys.tables WHERE [object_id] = OBJECT_ID('dbo.TestTable'))
   DROP TABLE dbo.TestTable;GOCREATE TABLE dbo.TestTable (
   TestId      INT         IDENTITY,
   TestName    VARCHAR(25) NOT NULL,
   LamboNoConvert AS (
       CASE
       WHEN TestName LIKE 'Lamborghini%'
       THEN 1
       ELSE 0
       END
   ),
   LamboConvert AS (
       CASE
       WHEN TestName LIKE 'Lamborghini%'
       THEN CONVERT(bit, 1)
       ELSE CONVERT(bit, 0)
       END
   )
);GO
INSERT INTO dbo.TestTable (TestName)VALUES
   ('Lamborghini Aventador'),
   ('McLaren F1');
SELECT * FROM dbo.TestTable;
SELECT
   name                      AS ColumnName,
   TYPE_NAME(system_type_id) AS TypeNameFROM sys.columnsWHERE [object_id] = OBJECT_ID('dbo.TestTable');


Results

TestId      TestName                  LamboNoConvert LamboConvert
----------- ------------------------- -------------- ------------
1           Lamborghini Aventador     1              1
2           McLaren F1                0              0

ColumnName      TypeName
--------------- --------
TestId          INT
TestName        VARCHAR
LamboNoConvert  INT
LamboConvert    BIT

Computed Column References:

• Exploring Indexed Persisted Computed Columns
• More on Indexed Persisted Computed Columns