SQL varchar数据类型深入探讨

In this article we’ll review the SQL varchar data type including a basic definition and overview, differences from varchar(n), UTF-8 support, Collation, performance considerations and more.

在本文中，我们将介绍SQL varchar数据类型，包括基本定义和概述，与varchar（n）的区别，UTF-8支持，排序规则，性能注意事项等。

Data plays a crucial part in any organization and an attribute by which it is defined is called its data type. In simple words, data type states what kind of data any object, variable or expression can store. As a SQL developer, while creating a SQL table, we have to understand and decide what type of data will be contained by each and every column in a table. Like any other programming language, SQL also supports a gamut of data types that can hold integer data, date and time data, character data etc. and allows you to define data types of your own as well. SQL varchar is one of the best-known and most-used data types among the lot. In this article, we will walk through different facets of the SQL Server varchar in the SQL server.

数据在任何组织中都起着至关重要的作用，定义它的属性称为数据类型。 简而言之，数据类型说明任何对象，变量或表达式可以存储哪种数据。 作为SQL开发人员，在创建SQL表时，我们必须了解并确定表中的每一列将包含哪种数据类型。 与任何其他编程语言一样，SQL还支持多种数据类型，这些数据类型可以容纳整数数据，日期和时间数据，字符数据等，还允许您定义自己的数据类型。 SQL varchar是其中最著名和最常用的数据类型之一。 在本文中，我们将遍历SQL Server中SQL Server varchar的不同方面。

Below is the outline that we will cover in this block.

以下是我们将在本节中介绍的概述。

1. Introduction to the SQL Server varchar data type in SQL Server
   SQL Server中SQL Server varchar数据类型简介
2. Use of varchar for large blocks of text
   将varchar用于大块文本
3. What is new in SQL Server 2019 preview for varchar datatype?
   SQL Server 2019预览版中varchar数据类型的新增功能是什么？
4. Influence of collation on varchar SQL in SQL Server
   排序规则对SQL Server中的varchar SQL的影响
5. UTF-8 support with varchar in SQL Server 2019 CTP
   SQL Server 2019 CTP中的varchar支持UTF-8
6. SQL Server varchar for data conversions and data display
   SQL Server varchar用于数据转换和数据显示
7. Storage and performance considerations using SQL Server varchar
   使用SQL Server varchar的存储和性能注意事项
8. Impact on string length of SQL varchar with CAST and CONVERT functions
   使用CAST和CONVERT函数对SQL varchar的字符串长度的影响

Let’s move ahead and see the aforementioned in action.

让我们继续前进，看看前面的内容。

那么，SQL中的varchar是什么？ (So what is varchar in SQL?)

As the name suggests, varchar means character data that is varying. Also known as Variable Character, it is an indeterminate length string data type. It can hold numbers, letters and special characters. Microsoft SQL Server 2008 (and above) can store up to 8000 characters as the maximum length of the string using varchar data type. SQL varchar usually holds 1 byte per character and 2 more bytes for the length information. It is recommended to use varchar as the data type when columns have variable length and the actual data is way less than the given capacity. Let’s switch to SSMS and see how varchar works.

顾名思义，varchar表示变化的字符数据。 也称为可变字符，它是长度不确定的字符串数据类型。 它可以容纳数字，字母和特殊字符。 Microsoft SQL Server 2008（及更高版本）可以使用varchar数据类型存储最多8000个字符，作为字符串的最大长度。 SQL varchar通常每个字符包含1个字节，而长度信息则另外包含2个字节。 当列的长度可变并且实际数据小于给定容量时，建议使用varchar作为数据类型。 让我们切换到SSMS，看看varchar是如何工作的。

The following example creates three variables (name, gender and age) with varchar as the data type and different values being assigned to them. As evident from the result sets shown below, by default, the string length of the SQL varchar columns is 1 and it returns only the first value of the variables(rest of the string being truncated) when no string length is passed for the varchar data type. Function len() is used to determine the number of characters stored in the varchar column.

下面的示例创建三个变量（名称，性别和年龄），并将varchar作为数据类型，并为其分配不同的值。 从下面显示的结果集中可以明显看出，默认情况下，SQL varchar列的字符串长度为1，并且在不为varchar数据传递任何字符串长度的情况下，它仅返回变量的第一个值（字符串的其余部分被截断）类型。 函数len（）用于确定varchar列中存储的字符数。

DECLARE @name AS varchar = 'john parker d''souza';  
DECLARE @gender AS varchar = 'M'
DECLARE @age AS varchar = '23'
 
SELECT @name Name, @gender Gender ,@age Age
SELECT len(@name) namelen, len(@gender) genderlen, len(@age) agelen

SQL varchar（max）与varchar（n）有何不同？ (How SQL varchar(max) is different from varchar(n)?)

There are times where SQL developers (including myself) usually define varchar datatype without a length, and subsequently, are failed to insert string records in the SQL table, this is because SQL Server allocates 1 character space as the default value to the varchar column that is defined without any length. In practical scenarios, varchar(n) is used to store variable length value as a string, here ‘n’ denotes the string length in bytes and it can go up to 8000 characters. Now, let’s proceed further and see how we can store SQL varchar data with a string length into the column of a SQL table. Below script creates the table Demovarchar with some data in it. And the result screen shows records of 7 employees based on their departments, age etc.

有时候，SQL开发人员（包括我自己）通常定义不带长度的varchar数据类型，并且随后无法在SQL表中插入字符串记录，这是因为SQL Server将1个字符空间作为默认值分配给varchar列，定义没有任何长度。 在实际情况下，varchar（n）用于将可变长度值存储为字符串，这里的“ n”表示字符串长度（以字节为单位），最多可以包含8000个字符。 现在，让我们继续进行下去，看看如何将具有字符串长度SQL varchar数据存储到SQL表的列中。 下面的脚本创建表Demovarchar，其中包含一些数据。 结果屏幕显示了7名员工的记录，这些记录是基于他们的部门，年龄等而得出的。

CREATE TABLE Demovarchar
(
Id int NOT NULL IDENTITY(1,1),
LastName varchar(10),
FirstName varchar(10),
Gender varchar,
DepartmentName varchar(20),
Age int
)
INSERT INTO Demovarchar VALUES('Gilbert', 'Kevin','M','Tool Design',33)
INSERT INTO Demovarchar VALUES('Tamburello', 'Andrea','F','Marketing',45)
INSERT INTO Demovarchar VALUES('Johnson', 'David','M','Engineering',66)
INSERT INTO Demovarchar VALUES('Sharma', 'Bradley','M','Production',27)
INSERT INTO Demovarchar VALUES('Rapier', 'Abigail','F',	'Human Resources',38)
INSERT INTO Demovarchar VALUES('Martin', 'Kelly','F','Information Services',54)
INSERT INTO Demovarchar VALUES('Poland', 'Carole','F','Production Control',29)
SELECT * FROM Demovarchar