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

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:

SQL
Copy
1CREATE TABLE SampleTable (
2    ExampleColumn VARCHAR(2)
3);

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:

SQL
Copy
1-- Change to a more efficient data type
2ALTER TABLE SampleTable
3ALTER 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.
Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
1DECLARE @var nvarchar(1) -- too small variant length type
2DECLARE @var1 nchar(1) -- fixed length type
3DECLARE @var2 varchar(1) -- too small variant length type
4DECLARE @var3 char(1)-- fixed length type
5DECLARE @var4 nvarchar(2) -- too small variant length type
6DECLARE @var5 nchar(2) -- fixed length type
7DECLARE @var6 varchar(2) -- too small variant length type
8DECLARE @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
See Also

Other Resources

Analysis Rules