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

Suppose, there is a new addition of an employee in the organization and we, as SQL data developers, would have to insert this new record into the above table using INSERT SQL Statement. Below is one such example shown.

假设组织中有一个新员工，作为SQL数据开发人员，我们将不得不使用INSERT SQL Statement将这个新记录插入到上表中。 下面是一个这样的例子。

INSERT INTO Demovarchar VALUES('Newton Hamilton', 'Isaac','M','Design Head',69)

Oops, SQL Server encountered an error and terminated the statement saying string or binary data would be truncated. This has occurred because, column LastName varchar(10) can hold up to 10 characters and here we are attempting to insert a new record with string length(‘Newton Hamilton’) which is clearly greater than 10 characters. As a quick fix, we can alter the table and increase the data type of the SQL varchar column, say to varchar(50) to insert the new row. Execute the below script to ALTER and INSERT a new record into the table. Additionally, you can use LEN() and DATALENGTH() functions to determine the number of characters and the storage size in bytes respectively that are stored in the varchar column.

糟糕，SQL Server遇到错误，并终止了声明字符串或二进制数据将被截断的语句。 发生这种情况的原因是，LastName varchar（10）列最多可容纳10个字符，并且在此我们尝试插入一个字符串长度（'Newton Hamilton'）明显大于10个字符的新记录。 作为快速解决方案，我们可以更改表并增加SQL varchar列的数据类型，对varchar（50）说，以插入新行。 执行以下脚本以ALTER并将新记录插入表中。 此外，您可以使用LEN（）和DATALENGTH（）函数来确定分别存储在varchar列中的字符数和以字节为单位的存储大小。

ALTER TABLE Demovarchar 
ALTER COLUMN LastName varchar(50) 
INSERT INTO Demovarchar VALUES('Newton Hamilton', 'Isaac','M','Design Head',69)
SELECT * FROM Demovarchar

We observed above how we can set or alter the string length in the SQL varchar column to meet the business needs. However, consider a scenario, where we are unsure of the data size that is going to be loaded into our SQL tables, in such circumstances, inspecting and altering data type size for each and every column is not a viable choice. One of the options to handle this could be is to set the string length on the higher bar in the SQL Server varchar column (provided you have a rough estimation of what length of the string column would be approximately).

上面我们观察了如何在SQL varchar列中设置或更改字符串长度以满足业务需求。 但是，请考虑以下情况：我们不确定要加载到SQL表中的数据大小，在这种情况下，检查和更改每一列的数据类型大小并不是一个可行的选择。 解决此问题的一种方法是在SQL Server varchar列的较高栏中设置字符串长度（前提是您对字符串列的长度大约是粗略的估计）。

An important point to keep in consideration, we can use string length up to varchar(8000) only as this is the maximum number of characters that SQL varchar(n) data type can hold. So in cases when there are chances that the string length of the varchar column might exceed 8000 bytes, using varchar(8001) or anything higher will result into an error. One short example demonstrating this fact is shown below.

需要考虑的重要一点，我们只能使用不超过varchar（8000）的字符串长度，因为这是SQL varchar（n）数据类型可以容纳的最大字符数。 因此，在varchar列的字符串长度可能超过8000个字节的情况下，使用varchar（8001）或更高的值将导致错误。 一个简短的例子证明了这一事实，如下所示。

DECLARE @name AS varchar(8001) = 'john parker d''souza';  
SELECT @name Name

SQL Server 2005 got around this limitation of 8KB storage size and provided a workaround with varchar(max). It is a non-Unicode large variable-length character data type and can store a maximum of 2^31-1 bytes (2 GB) of non-Unicode characters.

SQL Server 2005克服了8KB存储大小的限制，并提供了varchar（max）解决方法。 它是非Unicode大变长字符数据类型，最多可以存储2 ^ 31-1字节（2 GB）的非Unicode字符。

When I got first introduced to the concepts of varchar(n) and SQL varchar, the common question like any other beginner I had, was why can’t we simply declare a column of data type varchar(8500) or higher, since we have varchar(max) that takes care of storage up to 2GB and why are we supposed to either use varchar(<=8000) or varchar(max)? I got my answers on a little research that SQL Server uses page to store data and the size of each page is 8KB(excluding page header, row offsets size). If the data to be stored is less than or equal to 8000 bytes, varchar(n) or varchar(max) stores it in-row. However, if the data exceeds the 8000 byte size then it is treated as a Large Object(LOB) and they are not stored in-row but in separate LOB pages(LOB_DATA). Row in such case will only have a pointer to the LOB data page where the actual data is present and SQL Server automatically assigns an over-flow indicator to the page to manipulate data rows. In nutshell, if you know the data might exceed 8000 byte, it is a better option to use varchar(max) as the data type.

当我第一次介绍varchar（n）和SQL varchar的概念时，像我遇到的其他任何初学者一样，常见的问题是为什么我们不能简单地声明数据类型为varchar（8500）或更高的列，因为varchar（max）最多可处理2GB的存储，为什么我们应该使用varchar（<= 8000）或varchar（max）？ 我得到了有关SQL Server使用页面存储数据并且每个页面的大小为8KB（不包括页眉，行偏移量大小）的一些研究的答案。 如果要存储的数据小于或等于8000个字节，则varchar（n）或varchar（max）会将其存储在行中。 但是，如果数据超过8000字节大小，则将其视为大对象（LOB），它们不会存储在行中，而是存储在单独的LOB页（LOB_DATA）中。 在这种情况下，行将仅具有一个指向实际数据存在的LOB数据页的指针，并且SQL Server自动向该页分配一个溢出指示器以操纵数据行。 简而言之，如果您知道数据可能超过8000个字节，则最好使用varchar（max）作为数据类型。

We can refer to the DMV sys.dm_db_index_physical_stats to see what kind of page allocation (IN_ROW_DATA data/LOB_DATA/ ROW_OVERFLOW_DATA) is performed. You can also check out this link in case you want detailed explanation on how SQL Server exercises row and page limits with both varchar(n) and varchar(max) data types.

我们可以参考DMV sys.dm_db_index_physical_stats来查看执行哪种页面分配（IN_ROW_DATA数据/ LOB_DATA / ROW_OVERFLOW_DATA）。 如果需要有关SQL Server如何使用varchar（n）和varchar（max）数据类型行使行和页限制的详细说明，也可以查看此链接 。

Let’s quickly jump over to SSMS and see how we can use varchar(max). Execute the following script to insert 1 record where StringCol column value in each row is 15,000 B characters (i.e. 15,000 bytes).

让我们快速跳转到SSMS，看看如何使用varchar（max）。 执行以下脚本以插入1条记录，其中每行的StringCol列值是15,000个B字符（即15,000个字节）。

CREATE TABLE Demovarcharmax
    (
      ID INT IDENTITY(1, 1) ,
      StringCol VARCHAR(MAX)
    )
INSERT  INTO Demovarcharmax(StringCol) VALUES(REPLICATE(CAST('B' AS VARCHAR(MAX)), 15000))
SELECT Id, StringCol,len(StringCol) AS LengthOfString FROM Demovarcharmax

One limitation of using varchar(max) is we cannot create an index that has a varchar(max) as a key column, instead, it is advisable to do a Full-text index on that column.

使用varchar（max）的一个限制是我们不能创建以varchar（max）作为键列的索引，而是建议在该列上进行全文本索引。

A quick note to make – From here to the last leg of this article, we will mention varchar in place of varchar(n). Do NOT consider it as the varchar with default value = 1.

快速说明–从这里到本文的最后一站，我们将用varchar代替varchar（n）。 不要将其视为默认值为1的varchar。

To learn some more interesting differences between varchar(n) and varchar(max) in SQL Server, consider going through this article, Comparing VARCHAR(max) vs VARCHAR(n) data types in SQL Server.

要了解SQL Server中varchar（n）和varchar（max）之间一些更有趣的区别，请考虑阅读本文， 比较 SQL Server中的VARCHAR（max）与VARCHAR（n）数据类型 。

SQL Server 2019 CTP的UTF-8支持 (UTF-8 support with SQL Server 2019 CTP )

Before we dig in what SQL Server 2019 preview feature has to offer for SQL varchar, let’s quickly look at one more interesting data type – ‘nvarchar’ first. Like SQL Server varchar [(n|max)], we have SQL nvarchar [(n|max)], the prefix n in nvarchar denotes Unicode, i.e. it stores both Unicode and non-Unicode data. The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar. You can go through this link to learn more about nvarchar in SQL Server.

在我们深入研究SQL varchar提供SQL Server 2019预览功能之前，让我们快速看一下另一个有趣的数据类型-首先是'nvarchar'。 像SQL Server varchar [（n | max）]一样，我们有SQL nvarchar [（n | max）]，nvarchar中的前缀n表示Unicode，即它存储Unicode和非Unicode数据。 varchar和nvarchar之间的主要区别在于它们的存储方式，varchar存储为常规8位数据（每个字符1个字节），nvarchar存储数据每个字符2个字节。 由于这个原因，nvarchar最多可以容纳4000个字符，并且占用的空间是SQL varchar的两倍。 您可以通过此链接来了解有关SQL Server中的nvarchar的更多信息。

With the public preview of SQL Server 2019, Microsoft has announced the support for UTF-8 character encoding to the existing data types (varchar and char). For those, who are not aware of UTF-8, it stands for Unicode Transformation Format and is a Unicode-based encoding that supports many languages. The 8 in UTF-8 means it uses 1 byte (8-bits) to represent a character in memory. Likewise, UTF-16 uses 16 bits (2 bytes) to represent a character. We will limit the scope of this new SQL Server 2019 CTP enhancement to ‘SQL varchar’ only in this article.

在SQL Server 2019的公开预览中，Microsoft宣布了对现有数据类型（varchar和char）的UTF-8字符编码的支持。 对于那些不知道UTF-8的人，它代表Unicode转换格式，是一种支持多种语言的基于Unicode的编码。 UTF-8中的8表示它使用1个字节（8位）表示内存中的字符。 同样，UTF-16使用16位（2个字节）表示一个字符。 仅在本文中，我们将此新SQL Server 2019 CTP增强功能的范围限制为``SQL varchar''。

This enhancement has the following impact in SQL Server: is

此增强功能在SQL Server中具有以下影响：是

1. Until SQL Server 2019 CTP, SQL varchar data type had the capacity to store only Non-Unicode data and with this preview, we can now create a varchar column to store Unicode data under UTF-8 enabled collations (_UTF8). UTF-8 is allowed in the varchar datatypes and is enabled when creating or changing an object’s collation to a collation with the UTF8 suffix. This helps in minimizing character conversion issues.

   在SQL Server 2019 CTP之前，SQL varchar数据类型只能存储非Unicode数据，并且通过此预览，我们现在可以创建一个varchar列以在启用UTF-8的归类（_UTF8）下存储Unicode数据。 varchar数据类型中允许使用UTF-8，并且在创建对象的归类或将其归类为具有UTF8后缀的归类时启用。 这有助于最大程度地减少字符转换问题。

2. UTF-8 support for varchar data type provides substantial storage savings depending on the character set in use. For eg, using an UTF-8 enabled collation, changing the column data type from nvarchar(20) to varchar(20) offers a significant drop in storage requirements since nvarchar(20) requires 40 bytes for storage and varchar(20) needs 20 bytes for the same Unicode string.

   根据所使用的字符集，对varchar数据类型的UTF-8支持可节省大量存储空间。 例如，使用启用了UTF-8的排序规则，将列数据类型从nvarchar（20）更改为varchar（20）会大大降低存储要求，因为nvarchar（20）需要40个字节来存储，而varchar（20）需要20个字节同一Unicode字符串的字节数。

Important side note – Since this enhancement is still in preview, we can expect more progressions on this front in the near future. However, existing Unicode (UTF-16) data types (nchar, nvarchar and ntext) remain unchanged in SQL Server 2019 preview.

重要的旁注–由于此增强功能仍在预览中，因此我们预计在不久的将来会在这方面取得更多进展。 但是，现有的Unicode（UTF-16）数据类型（nchar，nvarchar和ntext）在SQL Server 2019预览中保持不变。

在SQL Server 2019 CTP中与SQL varchar排序规则
(Collation with SQL varchar in SQL Server 2019 CTP
)

Collation in SQL Server defines configurations to determine various rules like case sensitivity, accent sensitivity, sorting, character types and width etc. Understanding all these properties and how do they work with your data become very important. Collation can be set at server, database, expression or column level. UTF-8 supports database-level or column-level collation in SQL Server 2019 CTP and is enabled when you create or change Database or column collation to a collation with UTF8 suffix.

SQL Server中的排序规则定义配置来确定各种规则，例如区分大小写，重音符号，排序，字符类型和宽度等。了解所有这些属性以及它们如何与数据一起使用变得非常重要。 可以在服务器，数据库，表达式或列级别设置排序规则。 UTF-8在SQL Server 2019 CTP中支持数据库级别或列级别的排序规则，并且在您将数据库或列排序规则创建或更改为带有UTF8后缀的排序规则时启用。

If you execute the below query against SQL Server 2019 CTP, you will be able to see all the UTF-8 supported collations on your instance of SQL Server using function (fn_helpcollations()).

如果对SQL Server 2019 CTP执行以下查询，则可以使用函数（fn_helpcollat​​ions（））在SQL Server实例上查看所有UTF-8支持的归类。

SELECT Name, Description 
FROM fn_helpcollations() 
WHERE Name like '%UTF8';

With SQL Server 2019 preview version, we can assign Unicode collations (UTF-8 supported) as well for SQL varchar columns using the COLLATE clause while declaring the varchar column. This way, specific collation is applied to the particular column’s data without impacting the rest of the database.

在SQL Server 2019预览版中，我们还可以在声明varchar列的同时使用COLLATE子句为SQL varchar列分配Unicode归类（支持UTF-8）。 这样，特定的排序规则将应用于特定列的数据，而不会影响数据库的其余部分。

Since we are dealing with SQL Server varchar data type in this post, let’s see how Column Collation with SQL varchar datatype works. Execute the code below to alter the SQL Server varchar Column Collation from one collation type to _UTF8 suffix. You can read more on Database Collation from here.

由于本文中涉及的是SQL Server varchar数据类型，因此让我们看看使用SQL varchar数据类型的列排序是如何工作的。 执行以下代码，将SQL Server varchar列排序规则从一种排序规则类型更改为_UTF8后缀。 您可以从此处阅读有关数据库整理的更多信息 。

CREATE TABLE demovarcharcollate
	  (ID   int PRIMARY KEY,  
	   Description varchar(50) COLLATE LATIN1_GENERAL_100_CI_AS_SC NOT NULL  
	  );  
	ALTER TABLE demovarcharcollate 
	ALTER COLUMN Description varchar(50) COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8 NOT NULL;  

SQL varchar在数据转换和数据显示中的作用 (Role of SQL varchar in data conversions and data display)

SQL Server varchar is widely used in displaying data in the desirable formats using Convert and Cast functions in SQL Server. Real data deals with a mix of data types and it has to be compatible with each other (i.e. belong to the same data type), before we make comparisons to them. SQL Server supports both implicit and explicit conversions.

SQL Server varchar被广泛用于使用SQL Server中的Convert和Cast函数以所需格式显示数据。 实际数据处理多种数据类型，并且在与它们进行比较之前，它们必须彼此兼容（即属于同一数据类型）。 SQL Server支持隐式和显式转换。

• Note:注意：SQL CAST and SQL CONVERT function overview to get more information on how we can perform these conversions for data compatibility.SQL CAST和SQL CONVERT函数概述，以获取有关如何执行这些转换以实现数据兼容性的更多信息。

With an incessant need of formatting and displaying data in the required output, SQL varchar comes really handy. As a SQL developer myself, I find it extremely straightforward to use convert/cast with varchar data type to make assignments or transformations on data, especially for the date fields.

迫切需要在所需的输出中格式化和显示数据，SQL varchar非常方便。 作为一名SQL开发人员，我发现使用带有varchar数据类型的转换/广播来对数据进行分配或转换非常简单，尤其是对于日期字段。

I am using table FactInternetSales from Sample DB AdventureWorksDW2017 to show how this feature works. You can refer to any table with some datetime and money/float fields for the practice purpose. The following script converts two datetime columns to SQL varchar types with style 102 and 107 to display the data in the format yyyy.mm.dd and Mon dd, yyyy respectively. Also, the SalesAmount column with Money as a data type is converted to varchar and style 3 is applied to display the amount with commas as shown in the screenshot below. Additionally, say, we would want to see data for the orders placed in the year 2010 only, using the CAST function to convert datetime column to varchar data, the string comparison is performed in the WHERE clause. You can also go over SQL convert date to find more information on date conversion formats and styles.

我正在使用Sample DB AdventureWorksDW2017中的表FactInternetSales来显示此功能的工作方式。 出于练习目的，您可以引用带有某些日期时间和钱/浮点数字段的任何表。 以下脚本将两个日期时间列转换为样式为102和107SQL varchar类型，以分别以yyyy.mm.dd和Mon dd，yyyy格式显示数据。 同样，将Money作为数据类型的SalesAmount列转换为varchar，并应用样式3以逗号显示金额，如下面的屏幕快照所示。 另外，例如，我们只想查看2010年下订单的数据，使用CAST函数将datetime列转换为varchar数据，则在WHERE子句中执行字符串比较。 您还可以遍历 SQL转换日期 以找到有关日期转换格式和样式的更多信息。

SELECT OrderDate, CONVERT(varchar, OrderDate, 102) AS FormattedOrderDate,
ShipDate, CONVERT(varchar(12), ShipDate, 107) AS FormattedShipDate,
SalesAmount, convert(varchar,salesamount, 3) AS FormattedAmount
FROM FactInternetSales
WHERE CAST(OrderDate AS varchar) LIKE '%2010%' 

使用CAST和CONVERT函数对SQL varchar的字符串长度的影响 (Impact on string length of SQL varchar with CAST and CONVERT functions)

SQL Server stores long string data in the commonly used varchar data type and it becomes helpful to know the expected and maximum lengths of the strings to display the results in the UI. Copy and execute the below code, where we are passing a long string in an unspecified length varchar variable (@demovarchar) and also in another variable with a defined varchar length (@demovarcharwithcast). Microsoft takes 30 as the default length for SQL Varchar (with unspecified varchar length) in the SQL Server when it is used with CAST and CONVERT functions. In our case, even though the length of the string was 52, it returned 30 as the length as shown in the last result output.

SQL Server以常用的varchar数据类型存储长字符串数据，这有助于了解预期的字符串长度和最大长度，以在UI中显示结果。 复制并执行以下代码，我们在一个未指定长度的varchar变量（@demovarchar）和另一个具有定义的varchar长度的变量（@demovarcharwithcast）中传递一个长字符串。 Microsoft与CAST和CONVERT函数一起使用时，SQL Server中SQL Varchar的默认长度（未指定varchar长度）为30。 在我们的例子中，即使字符串的长度为52，它也会返回30作为长度，如最后一个结果输出所示。

One important point to note here is that when an unspecified length varchar field is created, the default length of such field is 1 (shown in red color below). When varchar length is unspecified and is used with CAST or CONVERT functions, the CAST or CONVERT returns n=30 as the default string length of this conversion (marked in blue color below).

这里要注意的重要一点是，当创建未指定长度的varchar字段时，该字段的默认长度为1（下面以红色显示）。 当未指定varchar length并与CAST或CONVERT函数一起使用时，CAST或CONVERT返回n = 30作为此转换的默认字符串长度（下面以蓝色标记）。

DECLARE @demovarchar varchar = 'We are learning SQL varchar in this SQLShack article'
DECLARE @demovarcharwithcast AS varchar(60) =  'We are learning SQL varchar in this SQLShack article'
SELECT DATALENGTH('We are learning SQL varchar in this SQLShack article') AS 'LenOFStringPassed'
 
SELECT DATALENGTH(@demovarchar)  AS 'DefaultVarcharLength'
 
SELECT DATALENGtH(CAST(@demovarcharwithcast AS varchar(60))) AS 'VarcharLengthSpecifiedWithCast'
SELECT DATALENGTH(CAST(@demovarcharwithcast AS varchar)) AS 'DefaultVarcharLengthWithCast'

使用SQL varchar的存储和性能注意事项 (Storage and performance considerations using SQL varchar)

Data types like varchar, char and nvarchar are all used to store string data in SQL Server. SQL varchar stores variable string length whereas SQL char stores fixed string length. This means SQL Server varchar holds only the characters we assign to it and char holds the maximum column space regardless of the string it holds.

诸如varchar，char和nvarchar之类的数据类型都用于在SQL Server中存储字符串数据。 SQL varchar存储可变的字符串长度，而SQL char存储固定的字符串长度。 这意味着SQL Server varchar仅保留我们分配给它的字符，而char保留最大的列空间，而不管其包含的字符串。

Because of the fixed field lengths, data is pulled straight from the column without doing any data manipulation and index lookups against varchar are slower than that of char fields. CHAR is better than VARCHAR performance wise, however, it takes unnecessary memory space when the data does not have a fixed-length. So in cases where disk size is not an issue, it is recommended to use CHAR.

由于字段长度固定，因此无需进行任何数据操作即可直接从列中提取数据，并且针对varchar的索引查找比char字段慢。 CHAR比VARCHAR性能更好，但是，当数据没有固定长度时，它将占用不必要的内存空间。 因此，在磁盘大小不成问题的情况下，建议使用CHAR。

In simple words, say we have a column with varchar(150) = ‘SQLShack’ – This will take 8 bytes(sqlshack) + 2 bytes for the length information = 10 bytes in actual and for column with char(150) = ‘SQLShack’ – This will consume whole 150 bytes on disk, regardless of what we pass as a string. The below example shows how CHAR uses the maximum allotted space (150) to fit in the string passed and how varchar column uses only the needed space.

用简单的话来说，假设我们有一列具有varchar（150）='SQLShack'–这将花费8个字节（sqlshack）+ 2个字节来获取长度信息= 10个字节，而对于带有char（150）='SQLShack的列'–这将占用磁盘上的全部150个字节，而不管我们作为字符串传递什么。 下面的示例显示CHAR如何使用最大分配空间（150）来容纳传递的字符串，以及varchar列如何仅使用所需的空间。

DECLARE @demochar CHAR(150) = 'This is the char value' 
DECLARE @demovarchar VARCHAR(150) = 'This is the varchar value'
 
SELECT 'Starting ' + @demochar + ' finishing' AS 'CHAR DATA'
SELECT 'Starting ' + @demovarchar + ' finishing' AS 'VARCHAR DATA'

Bottom line is to use the data type that fits our need. You can use SQL varchar when the sizes of the column vary considerably, use varchar(max) when there are chances that string length might exceed 8000 bytes, use char when the sizes of the column are fixed and use nvarchar if there is a requirement to store Unicode or multilingual data.

底线是使用适合我们需要的数据类型。 当列的大小相差很大时，可以使用SQL varchar；当字符串长度可能超过8000个字节时，可以使用varchar（max）；当列的大小固定时，可以使用char；如果需要，可以使用nvarchar。存储Unicode或多语言数据。

结论
(Conclusion
)

Data types play a fundamental role in database design but they are often overlooked. A good understanding and accurate use of data types ensure correct nature and length of data is populated in the tables. The intention of this tip is to help you gain an understanding of basic characteristics and features of SQL Server varchar along with its performance and storage aspects in SQL Server. We also covered recent advancements in SQL varchar in the SQL Server 2019 Preview.

数据类型在数据库设计中起着基本作用，但常常被忽略。 对数据类型的充分理解和正确使用可确保在表中填充正确的数据性质和长度。 本技巧的目的是帮助您了解SQL Server varchar的基本特征和特性，以及SQL Server中的性能和存储方面。 我们还在SQL Server 2019预览版中介绍了SQL varchar的最新进展。

也可以看看 (See also)

You can check out these other articles to continue your learning on SQL datatypes.

您可以查看其他文章，以继续学习SQL数据类型。

• Understanding the GUID data type in SQL Server 了解SQL Server中的GUID数据类型
• Spatial data types in SQL Server SQL Server中的空间数据类型

翻译自: https://www.sqlshack.com/sql-varchar-data-type-deep-dive/