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:
