SA0016 : Use of very small variable length type (size 1 or 2)

Introduction

Using very small variable length data types can lead to inefficient storage.

Description

Variable length data types in SQL Server can lead to increased storage space and inefficiencies when used with sizes of 1 or 2. It is generally more storage-efficient to use fixed length data types for such small sizes.

Inefficient use of variable length data type:

CREATE TABLE SampleTable (
    ExampleColumn VARCHAR(2)
);

In this example, using VARCHAR(2) can require more storage space than using a fixed length type like CHAR(2) , which could lead to wasted space in the database.

Increased storage consumption for very small data sizes.
Potentially impacts performance due to unnecessary data space handling.

How to fix

Use fixed length data types like CHAR for sizes less than 3 to ensure efficient storage and improved performance.

Follow these steps to address the issue:

1.Identify the columns in your tables using variable length data types, such as VARCHAR , with defined sizes smaller than 3.

2.Change these columns to use the equivalent fixed length data type, such as CHAR , to improve storage efficiency.

3.Modify the table definitions in your database to reflect these changes. This may involve altering existing tables if they are already created.

For example, to modify a table with an inefficient data type:

-- Change to a more efficient data type
ALTER TABLE SampleTable
ALTER COLUMN ExampleColumn CHAR(2);

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

2 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

DECLARE @var nvarchar(1) -- too small variant length type
DECLARE @var1 nchar(1) -- fixed length type
DECLARE @var2 varchar(1) -- too small variant length type
DECLARE @var3 char(1)-- fixed length type
DECLARE @var4 nvarchar(2) -- too small variant length type
DECLARE @var5 nchar(2) -- fixed length type
DECLARE @var6 varchar(2) -- too small variant length type
DECLARE @var7 char(2) -- fixed length type

Analysis Results

	Message	Line	Column
1	SA0016 : Use of very small variable length type (size 1 or 2).	1	22
2	SA0016 : Use of very small variable length type (size 1 or 2).	3	22
3	SA0016 : Use of very small variable length type (size 1 or 2).	5	23
4	SA0016 : Use of very small variable length type (size 1 or 2).	7	22

Analysis Rules