SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length

Introduction

Always specify a length when using VARCHAR or NVARCHAR types for columns, variables, or parameters to prevent inconsistency and potential data loss.

Description

In SQL Server, failing to define the length for VARCHAR or NVARCHAR can lead to unintended issues because the system automatically assigns a default length of 1 character. In certain scenarios, the length may default to 30 characters, leading to inconsistency.

For example:

-- Example of problematic query
DECLARE @name VARCHAR;
SET @name = 'John Doe';
SELECT @name;

This query assigns the value ‘John Doe’ to the variable @name . However, because no length is specified for VARCHAR , only the first character ‘J’ is stored and returned.

Unintentional data truncation, leading to data loss or inaccurate results.
Increased risk of bugs and errors, particularly in queries expecting more data.

How to fix

Specify a length for VARCHAR or NVARCHAR data types to prevent unintended data truncation and inconsistency.

Follow these steps to address the issue:

1.Review any declared VARCHAR or NVARCHAR variables, columns, or parameters in your SQL scripts without a specified length.

2.Determine the appropriate length for the data type based on the expected size of the data. This should align with your application’s requirements.

3.Update the SQL declarations to include the determined length. Replace instances of VARCHAR or NVARCHAR with the correct syntax, specifying the length explicitly.

4.Test your updates to ensure no data truncation or related errors occur and that the application behaves as expected.

For example:

-- Corrected query with specified length
DECLARE @name VARCHAR(50);
SET @name = 'John Doe';
SELECT @name;

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

5 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

DECLARE @var nvarchar(20)
DECLARE @var1 nchar(11)
DECLARE @var2 varchar
DECLARE @var3 nvarchar

DECLARE @var4 [VARCHAR]
DECLARE @var5 [NVARCHAR]

DECLARE @var41 [VARCHAR](30)
DECLARE @var51 [NVARCHAR](30)

DECLARE @var6 [VARCHAR]
DECLARE @var7 [NVARCHAR] -- IGNORE:SA0080

Analysis Results

	Message	Line	Column
1	SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length.	3	14
2	SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length.	4	14
3	SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length.	6	14
4	SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length.	7	14
5	SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length.	12	14

Analysis Rules