資料類型 (Transact-SQL)Data types (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

SQL ServerSQL Server 中,每個資料行、區域變數、運算式和參數都有相關的資料類型。In SQL ServerSQL Server, each column, local variable, expression, and parameter has a related data type. 資料類型是指定物件所能保留之資料類型的屬性,這些資料類型包括整數資料、字元資料、貨幣資料、日期和時間資料、二進位字串等。A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.

SQL ServerSQL Server 提供一組系統資料來定義搭配 SQL ServerSQL Server 所能使用的所有資料類型。supplies a set of system data types that define all the types of data that can be used with SQL ServerSQL Server. 您也可以在 Transact-SQLTransact-SQLMicrosoftMicrosoft .NET Framework.NET Framework 中定義您自己的資料類型。You can also define your own data types in Transact-SQLTransact-SQL or the MicrosoftMicrosoft .NET Framework.NET Framework. 別名資料類型是以系統提供的資料類型為基礎。Alias data types are based on the system-supplied data types. 如需別名資料類型的詳細資訊,請參閱 CREATE TYPE (Transact-SQL)For more information about alias data types, see CREATE TYPE (Transact-SQL). 使用者定義型別會從您利用 .NET Framework.NET Framework 支援的程式設計語言所建立之類別的方法和運算子來取得它們的性質。User-defined types obtain their characteristics from the methods and operators of a class that you create by using one of the programming languages support by the .NET Framework.NET Framework.

當運算子結合的兩個運算式有不同的資料類型、定序、有效位數、小數位數或長度時,結果的性質取決於下列各點:When two expressions that have different data types, collations, precision, scale, or length are combined by an operator, the characteristics of result are determined by the following:

  • 結果的資料類型,取決於輸入運算式的資料類型所套用的資料類型優先順序規則。The data type of the result is determined by applying the rules of data type precedence to the data types of the input expressions. 如需詳細資訊,請參閱資料類型優先順序 (Transact-SQL)For more information, see Data Type Precedence (Transact-SQL).
  • 當結果資料類型為 charvarchartextncharnvarcharntext 時,結果的定序會完全由定序優先順序的規則決定。The collation of the result is determined by the rules of collation precedence when the result data type is char, varchar, text, nchar, nvarchar, or ntext. 如需詳細資訊,請參閱定序優先順序 (Transact-SQL)For more information, see Collation Precedence (Transact-SQL).
  • 結果的有效位數、小數位數和長度會隨著輸入運算式的有效位數、小數位數和長度而不同。The precision, scale, and length of the result depend on the precision, scale, and length of the input expressions. 如需詳細資訊,請參閱有效位數、小數位數和長度 (Transact-SQL)For more information, see Precision, Scale, and Length (Transact-SQL).

SQL ServerSQL Server 提供 ISO 相容性的資料類型同義字。provides data type synonyms for ISO compatibility. 如需詳細資訊,請參閱資料類型同義字 (Transact-SQL)For more information, see Data Type Synonyms (Transact-SQL).

資料類型類別Data type categories

SQL ServerSQL Server 中的資料類型組織成下列類別:Data types in SQL ServerSQL Server are organized into the following categories:

精確數值Exact numerics Unicode 字元字串Unicode character strings
近似數值Approximate numerics 二進位字串Binary strings
日期和時間Date and time 其他資料類型Other data types
字元字串Character strings

SQL ServerSQL Server 中,以儲存體的性質為基礎,某些資料類型指定為屬於下列群組:In SQL ServerSQL Server, based on their storage characteristics, some data types are designated as belonging to the following groups:

  • 大型數值資料類型:varchar(max)nvarchar(max)Large value data types: varchar(max), and nvarchar(max)

  • 大型物件資料類型:textntextimagevarbinary(max)xmlLarge object data types: text, ntext, image, varbinary(max), and xml

    注意

    sp_help 會傳回 -1 作為大型數值和 xml 資料類型的長度。sp_help returns -1 as the length for the large-value and xml data types.

精確數值Exact numerics

bigintbigint numericnumeric
bitbit smallintsmallint
decimaldecimal smallmoneysmallmoney
intint tinyinttinyint
moneymoney

近似數值Approximate numerics

floatfloat realreal

日期和時間Date and time

datedate datetimeoffsetdatetimeoffset
datetime2datetime2 smalldatetimesmalldatetime
datetimedatetime timetime

字元字串Character strings

charchar varcharvarchar
texttext

Unicode 字元字串Unicode character strings

ncharnchar nvarcharnvarchar
ntextntext

二進位字串Binary strings

binarybinary varbinaryvarbinary
imageimage

其他資料類型Other data types

cursorcursor rowversionrowversion
hierarchyidhierarchyid uniqueidentifieruniqueidentifier
sql_variantsql_variant xmlxml
空間幾何類型Spatial Geometry Types 空間幾何類型Spatial Geography Types
tabletable

另請參閱See also

CREATE PROCEDURE (Transact-SQL)CREATE PROCEDURE (Transact-SQL)
CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)
DECLARE @local_variable (Transact-SQL) EXECUTE (Transact-SQL)DECLARE @local_variable (Transact-SQL) EXECUTE (Transact-SQL)
運算式 (Transact-SQL)Expressions (Transact-SQL)
函式 (Transact-SQL)Functions (Transact-SQL)
LIKE (Transact-SQL)LIKE (Transact-SQL)
sp_droptype (Transact-SQL)sp_droptype (Transact-SQL)
sp_help (Transact-SQL)sp_help (Transact-SQL)
sp_rename (Transact-SQL)sp_rename (Transact-SQL)