SERVERPROPERTY (Transact-SQL)SERVERPROPERTY (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

返回有关服务器实例的属性信息。Returns property information about the server instance.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

SERVERPROPERTY ( 'propertyname' )  

参数Arguments

propertyname propertyname
一个表达式,包含要为服务器返回的属性信息。Is an expression that contains the property information to be returned for the server. propertyname 可以是下列值之一 。propertyname can be one of the following values.

属性Property 返回的值Values returned
BuildClrVersionBuildClrVersion 在生成 SQL ServerSQL Server 实例时使用的 MicrosoftMicrosoft.NET Framework.NET Framework 公共语言运行时 (CLR) 的版本。Version of the MicrosoftMicrosoft.NET Framework.NET Framework common language runtime (CLR) that was used while building the instance of SQL ServerSQL Server.

NULL = 输入无效,出现错误或不适用。NULL = Input is not valid, an error, or not applicable.

基本数据类型:nvarchar(128) Base data type: nvarchar(128)
排序规则Collation 服务器的默认排序规则名称。Name of the default collation for the server.

NULL = 输入无效或错误。NULL = Input is not valid, or an error.

基本数据类型:nvarchar(128) Base data type: nvarchar(128)
CollationIDCollationID SQL ServerSQL Server 排序规则的 ID。ID of the SQL ServerSQL Server collation.

基本数据类型:int Base data type: int
ComparisonStyleComparisonStyle 排序规则的 Windows 比较样式。Windows comparison style of the collation.

基本数据类型:int Base data type: int
ComputerNamePhysicalNetBIOSComputerNamePhysicalNetBIOS 当前运行 SQL ServerSQL Server 实例的本地计算机的 NetBIOS 名称。NetBIOS name of the local computer on which the instance of SQL ServerSQL Server is currently running.

对于故障转移群集上的 SQL ServerSQL Server 群集实例,当 SQL ServerSQL Server 实例故障转移到故障转移群集中的其他节点时,该值将更改。For a clustered instance of SQL ServerSQL Server on a failover cluster, this value changes as the instance of SQL ServerSQL Server fails over to other nodes in the failover cluster.

SQL ServerSQL Server 的独立实例上,该值保持不变,并返回与 MachineName 属性相同的值。On a stand-alone instance of SQL ServerSQL Server, this value remains constant and returns the same value as the MachineName property.

注意: 如果 SQL ServerSQL Server 实例位于故障转移群集中,并且您要获取故障转移群集实例的名称,请使用 MachineName 属性。Note: If the instance of SQL ServerSQL Server is in a failover cluster and you want to obtain the name of the failover clustered instance, use the MachineName property.

NULL = 输入无效,出现错误或不适用。NULL = Input is not valid, an error, or not applicable.

基本数据类型:nvarchar(128) Base data type: nvarchar(128)
版本Edition 所安装的 SQL ServerSQL Server 实例的产品版本。Installed product edition of the instance of SQL ServerSQL Server. 使用该属性的值确定功能和限制,例如按 SQL Server 版本划分的计算能力限制Use the value of this property to determine the features and the limits, such as Compute Capacity Limits by Edition of SQL Server. 64 位版本的数据库引擎Database Engine向此版本追加(64 位)。64-bit versions of the 数据库引擎Database Engine append (64-bit) to the version.

返回:Returns:

“Enterprise Edition”'Enterprise Edition'

“Enterprise Edition:基于内核的许可”'Enterprise Edition: Core-based Licensing'

“Enterprise Evaluation Edition”'Enterprise Evaluation Edition'

“Business Intelligence Edition”'Business Intelligence Edition'

“Developer Edition”'Developer Edition'

“Express Edition”'Express Edition'

“Express Edition with Advanced Services”'Express Edition with Advanced Services'

“Standard Edition”'Standard Edition'

“Web Edition”'Web Edition'

“SQL Azure”表示 SQL 数据库SQL DatabaseSQL 数据仓库SQL Data Warehouse'SQL Azure' indicates SQL 数据库SQL Database or SQL 数据仓库SQL Data Warehouse

基本数据类型:nvarchar(128) Base data type: nvarchar(128)
EditionIDEditionID EditionID 表示 SQL ServerSQL Server 实例的已安装产品版本。EditionID represents the installed product edition of the instance of SQL ServerSQL Server. 使用该属性的值确定功能和限制,例如按 SQL Server 版本划分的计算能力限制Use the value of this property to determine features and limits, such as Compute Capacity Limits by Edition of SQL Server.

1804890536 = Enterprise1804890536 = Enterprise

1872460670 = Enterprise Edition:基于内核的许可1872460670 = Enterprise Edition: Core-based Licensing

610778273= Enterprise Evaluation610778273= Enterprise Evaluation

284895786 = Business Intelligence284895786 = Business Intelligence

-2117995310 = Developer-2117995310 = Developer

-1592396055 = Express-1592396055 = Express

-133711905= Express with Advanced Services-133711905= Express with Advanced Services

-1534726760 = Standard-1534726760 = Standard

1293598313 = Web1293598313 = Web

1674378470 = SQL 数据库或 SQL 数据仓库1674378470 = SQL Database or SQL Data Warehouse

基本数据类型:bigint Base data type: bigint
EngineEditionEngineEdition 服务器上安装的 数据库引擎Database Engine 实例的SQL ServerSQL Server版本。数据库引擎Database Engine edition of the instance of SQL ServerSQL Server installed on the server.

1 = Personal 或 Desktop Engine(不适用于 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 和更高版本。)1 = Personal or Desktop Engine (Not available in SQL Server 2005 (9.x)SQL Server 2005 (9.x) and later versions.)

2 = Standard(对 Standard、Web 和 Business Intelligence 返回该值。)2 = Standard (This is returned for Standard, Web, and Business Intelligence.)

3 = Enterprise(对 Enterprise、Developer 以及 Enterprise 版本返回该值。)3 = Enterprise (This is returned for Evaluation, Developer, and Enterprise editions.)

4 = Express(对 Express、Express with Tools 和 Express with Advanced Services 返回该值)4 = Express (This is returned for Express, Express with Tools, and Express with Advanced Services)

5 = SQL 数据库SQL Database5 = SQL 数据库SQL Database

6 = SQL 数据仓库SQL Data Warehouse6 = SQL 数据仓库SQL Data Warehouse

8 = 托管实例8 = Managed Instance

基本数据类型:int Base data type: int
HadrManagerStatusHadrManagerStatus 适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

指示 AlwaysOn 可用性组Always On availability groups管理器是否已启动。Indicates whether the AlwaysOn 可用性组Always On availability groups manager has started.

0 = 未启动,通信挂起。0 = Not started, pending communication.

1 = 已启动,正在运行。1 = Started and running.

2 = 未启动,已失败。2 = Not started and failed.

NULL = 输入无效,出现错误或不适用。NULL = Input is not valid, an error, or not applicable.
InstanceDefaultDataPathInstanceDefaultDataPath 适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x) 到当前版本 2015 年底开始的更新 。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through current version in updates beginning in late 2015.

实例数据文件的默认路径的名称。Name of the default path to the instance data files.
InstanceDefaultLogPathInstanceDefaultLogPath 适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x) 到当前版本 2015 年底开始的更新 。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through current version in updates beginning in late 2015.

实例日志文件的默认路径的名称。Name of the default path to the instance log files.
InstanceNameInstanceName 用户连接到的实例的名称。Name of the instance to which the user is connected.

如果实例名称是默认实例,或者输入无效或发生错误,则返回 NULL。Returns NULL if the instance name is the default instance, if the input is not valid, or error.

NULL = 输入无效,出现错误或不适用。NULL = Input is not valid, an error, or not applicable.

基本数据类型:nvarchar(128) Base data type: nvarchar(128)
IsAdvancedAnalyticsInstalledIsAdvancedAnalyticsInstalled 如果安装期间安装了高级分析功能,则返回 1;如果未安装,则返回 0。Returns 1 if the Advanced Analytics feature was installed during setup; 0 if Advanced Analytics was not installed.
IsClusteredIsClustered 服务器实例已配置在故障转移群集中。Server instance is configured in a failover cluster.

1 = 群集。1 = Clustered.

0 = 非群集。0 = Not Clustered.

NULL = 输入无效,出现错误或不适用。NULL = Input is not valid, an error, or not applicable.

基本数据类型:int Base data type: int
IsFullTextInstalledIsFullTextInstalled 全文和语义索引组件安装在 SQL ServerSQL Server 的当前实例上。The full-text and semantic indexing components are installed on the current instance of SQL ServerSQL Server.

1 = 已安装全文和语义索引组件。1 = Full-text and semantic indexing components are installed.

0 = 未安装全文和语义索引组件。0 = Full-text and semantic indexing components are not installed.

NULL = 输入无效,出现错误或不适用。NULL = Input is not valid, an error, or not applicable.

基本数据类型:int Base data type: int
IsHadrEnabledIsHadrEnabled 适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

在此服务器实例上启用 AlwaysOn 可用性组Always On availability groupsAlwaysOn 可用性组Always On availability groups is enabled on this server instance.

0 = AlwaysOn 可用性组Always On availability groups 功能已禁用。0 = The AlwaysOn 可用性组Always On availability groups feature is disabled.

1 = AlwaysOn 可用性组Always On availability groups 功能已启用。1 = The AlwaysOn 可用性组Always On availability groups feature is enabled.

NULL = 输入无效,出现错误或不适用。NULL = Input is not valid, an error, or not applicable.

基本数据类型:int Base data type: int

要创建可用性副本并使该副本在 SQL ServerSQL Server 实例上运行,必须在该服务器实例上启用 AlwaysOn 可用性组Always On availability groupsFor availability replicas to be created and run on an instance of SQL ServerSQL Server, AlwaysOn 可用性组Always On availability groups must be enabled on the server instance. 有关详细信息,请参阅启用和禁用 AlwaysOn 可用性组 (SQL Server)For more information, see Enable and Disable AlwaysOn Availability Groups (SQL Server).

注意: IsHadrEnabled 属性仅与 AlwaysOn 可用性组Always On availability groups 相关。Note: The IsHadrEnabled property pertains only to AlwaysOn 可用性组Always On availability groups. 其他高可用性或灾难恢复功能(如数据库镜像或日志传送)都不受此服务器属性影响。Other high availability or disaster recovery features, such as database mirroring or log shipping, are unaffected by this server property.
IsIntegratedSecurityOnlyIsIntegratedSecurityOnly 服务器处于集成安全性模式下。Server is in integrated security mode.

1 = 集成安全性(Windows 身份验证)1 = Integrated security (Windows Authentication)

0 = 非集成安全性。0 = Not integrated security. (Windows 身份验证和 SQL ServerSQL Server 身份验证。)(Both Windows Authentication and SQL ServerSQL Server Authentication.)

NULL = 输入无效,出现错误或不适用。NULL = Input is not valid, an error, or not applicable.

基本数据类型:int Base data type: int
IsLocalDBIsLocalDB 适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

服务器是 SQL Server ExpressSQL Server Express LocalDB 的实例。Server is an instance of SQL Server ExpressSQL Server Express LocalDB.

NULL = 输入无效,出现错误或不适用。NULL = Input is not valid, an error, or not applicable.
IsPolyBaseInstalledIsPolyBaseInstalled 适用于SQL Server 2017SQL Server 2017Applies to: SQL Server 2017SQL Server 2017.

返回服务器实例是否安装了 PolyBase 功能的结果。Returns whether the server instance has the PolyBase feature installed.

0 = 未安装 PolyBase。0 = PolyBase is not installed.

1 = 安装了 PolyBase。1 = PolyBase is installed.

基本数据类型:int Base data type: int
IsSingleUserIsSingleUser 服务器处于单用户模式下。Server is in single-user mode.

1 = 单个用户。1 = Single user.

0 = 非单个用户0 = Not single user

NULL = 输入无效,出现错误或不适用。NULL = Input is not valid, an error, or not applicable.

基本数据类型:int Base data type: int
IsXTPSupportedIsXTPSupported 适用对象:SQL Server(SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017)、SQL 数据库SQL DatabaseApplies to: SQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017), SQL 数据库SQL Database.

服务器支持内存中 OLTP。Server supports In-Memory OLTP.

1= 服务器支持内存中 OLTP。1= Server supports In-Memory OLTP.

0= 服务器不支持内存中 OLTP。0= Server does not supports In-Memory OLTP.

NULL = 输入无效,出现错误或不适用。NULL = Input is not valid, an error, or not applicable.

基本数据类型:int Base data type: int
LCIDLCID 排序规则的 Windows 区域设置标识符 (LCID)。Windows locale identifier (LCID) of the collation.

基本数据类型:int Base data type: int
LicenseTypeLicenseType 未使用。Unused. 许可证信息不由 SQL ServerSQL Server 产品保留或维护。License information is not preserved or maintained by the SQL ServerSQL Server product. 始终返回 DISABLED。Always returns DISABLED.

基本数据类型:nvarchar(128) Base data type: nvarchar(128)
MachineNameMachineName 运行服务器实例的 Windows 计算机名称。Windows computer name on which the server instance is running.

对于群集实例,即在 Microsoft 群集服务的虚拟服务器上运行的 SQL ServerSQL Server 实例,返回虚拟服务器的名称。For a clustered instance, an instance of SQL ServerSQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.

NULL = 输入无效,出现错误或不适用。NULL = Input is not valid, an error, or not applicable.

基本数据类型:nvarchar(128) Base data type: nvarchar(128)
NumLicensesNumLicenses 未使用。Unused. 许可证信息不由 SQL ServerSQL Server 产品保留或维护。License information is not preserved or maintained by the SQL ServerSQL Server product. 始终返回 NULL。Always returns NULL.

基本数据类型:int Base data type: int
ProcessIDProcessID SQL ServerSQL Server 服务的进程 ID。Process ID of the SQL ServerSQL Server service. ProcessID 对于标识属于该实例的 Sqlservr.exe 很有用。ProcessID is useful in identifying which Sqlservr.exe belongs to this instance.

NULL = 输入无效,出现错误或不适用。NULL = Input is not valid, an error, or not applicable.

基本数据类型:int Base data type: int
ProductBuildProductBuild 适用范围:2015 年 10 月开始的 SQL Server 2014 (12.x)SQL Server 2014 (12.x)Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) beginning October, 2015.

生成号。The build number.
ProductBuildTypeProductBuildType 适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x) 到当前版本 2015 年底开始的更新 。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through current version in updates beginning in late 2015.

当前版本的版本类型。Type of build of the current build.

返回下列项之一:Returns one of the following:

OD = 特定客户按需版本。OD = On Demand release a specific customer.

GDR = 通过 Windows 更新发布的常规分发版本。GDR = General Distribution Release released through windows update.

NULLNULL
= 不适用。= Not applicable.
ProductLevelProductLevel SQL ServerSQL Server 实例的版本级别。Level of the version of the instance of SQL ServerSQL Server.

返回下列项之一:Returns one of the following:

“RTM”= 原始发布版本'RTM' = Original release version

“SPn”= 服务包版本 'SPn' = Service pack version

“CTPn”= 社区技术预览版版本 'CTPn', = Community Technology Preview version

基本数据类型:nvarchar(128) Base data type: nvarchar(128)
ProductMajorVersionProductMajorVersion 适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x) 到当前版本 2015 年底开始的更新 。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through current version in updates beginning in late 2015.

主版本。The major version.
ProductMinorVersionProductMinorVersion 适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x) 到当前版本 2015 年底开始的更新 。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through current version in updates beginning in late 2015.

次版本。The minor version.
ProductUpdateLevelProductUpdateLevel 适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x) 到当前版本 2015 年底开始的更新 。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through current version in updates beginning in late 2015.

当前版本的更新级别。Update level of the current build. CU 表示累积更新。CU indicates a cumulative update.

返回下列项之一:Returns one of the following:

CUn = 累积更新 CUn = Cumulative Update

NULLNULL
= 不适用。= Not applicable.
ProductUpdateReferenceProductUpdateReference 适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x) 到当前版本 2015 年底开始的更新 。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through current version in updates beginning in late 2015.

该版本的知识库文章。KB article for that release.
ProductVersionProductVersion 实例 SQL ServerSQL Server 的版本,格式为“major.minor.build.revision” 。Version of the instance of SQL ServerSQL Server, in the form of 'major.minor.build.revision'.

基本数据类型:nvarchar(128) Base data type: nvarchar(128)
ResourceLastUpdateDateTimeResourceLastUpdateDateTime 返回资源数据库上次更新的日期和时间。Returns the date and time that the Resource database was last updated.

基本数据类型:datetime Base data type: datetime
ResourceVersionResourceVersion 返回版本资源数据库。Returns the version Resource database.

基本数据类型:nvarchar(128) Base data type: nvarchar(128)
ServerNameServerName Windows 服务器和与指定的 SQL ServerSQL Server 实例关联的实例信息。Both the Windows server and instance information associated with a specified instance of SQL ServerSQL Server.

NULL = 输入无效或错误。NULL = Input is not valid, or an error.

基本数据类型:nvarchar(128) Base data type: nvarchar(128)
SqlCharSetSqlCharSet 排序规则 ID 中的 SQL 字符集 ID。The SQL character set ID from the collation ID.

基本数据类型:tinyint Base data type: tinyint
SqlCharSetNameSqlCharSetName 来自排序规则的 SQL 字符集名称。The SQL character set name from the collation.

基本数据类型:nvarchar(128) Base data type: nvarchar(128)
SqlSortOrderSqlSortOrder 排序规则中的 SQL 排序顺序 IDThe SQL sort order ID from the collation

基本数据类型:tinyint Base data type: tinyint
SqlSortOrderNameSqlSortOrderName 来自排序规则的 SQL 排序顺序名称。The SQL sort order name from the collation.

基本数据类型:nvarchar(128) Base data type: nvarchar(128)
FilestreamShareNameFilestreamShareName FILESTREAM 使用的共享的名称。The name of the share used by FILESTREAM.

NULL = 输入无效,出现错误或不适用。NULL = Input is not valid, an error, or not applicable.
FilestreamConfiguredLevelFilestreamConfiguredLevel FILESTREAM 访问的配置级别。The configured level of FILESTREAM access. 有关详细信息,请参阅文件流访问级别For more information, see filestream access level.
FilestreamEffectiveLevelFilestreamEffectiveLevel FILESTREAM 访问的有效级别。The effective level of FILESTREAM access. 如果级别已更改,并且实例重新启动或计算机重新启动处于挂起状态,则该值可以不同于 FilestreamConfiguredLevel。This value can be different than the FilestreamConfiguredLevel if the level has changed and either an instance restart or a computer restart is pending. 有关详细信息,请参阅文件流访问级别For more information, see filestream access level.

返回类型Return Types

sql_variantsql_variant

RemarksRemarks

ServerName 属性ServerName Property

SERVERPROPERTY 函数的 ServerName 属性和 @@SERVERNAME 返回相似的信息。The ServerName property of the SERVERPROPERTY function and @@SERVERNAME return similar information. ServerName 属性提供 Windows 服务器和实例名称,两者共同构成唯一的服务器实例。The ServerName property provides the Windows server and instance name that together make up the unique server instance. @@SERVERNAME 提供当前配置的本地服务器名称。@@SERVERNAME provides the currently configured local server name.

如果安装时未更改默认服务器名称,则 ServerName 属性和 @@SERVERNAME 返回相同的信息。The ServerName property and @@SERVERNAME return the same information if the default server name at the time of installation has not been changed. 可以通过执行以下过程配置本地服务器的名称:The local server name can be configured by executing the following:

EXEC sp_dropserver 'current_server_name';  
GO  
EXEC sp_addserver 'new_server_name', 'local';  
GO  

如果在安装时已将本地服务器名称从默认服务器名称更改为其他名称,则 @@SERVERNAME 返回更改后的新名称。If the local server name has been changed from the default server name at installation time, @@SERVERNAME returns the new name.

版本属性Version Properties

SERVERPROPERTY 函数返回与版本信息有关的各个属性,而 @@VERSION 函数将输出合并为一个字符串。The SERVERPROPERTY function returns individual properties that relate to the version information whereas the @@VERSION function combines the output into one string. 如果应用程序需要各个属性字符串,则可以使用 SERVERPROPERTY 函数返回它们,而不是分析 @@VERSION 结果。If your application requires individual property strings, you can use the SERVERPROPERTY function to return them instead of parsing the @@VERSION results.

权限Permissions

所有用户都可以查询服务器属性。All users can query the server properties.

示例Examples

下面的示例在 SELECT 语句中使用 SERVERPROPERTY 函数返回有关当前 SQL ServerSQL Server 实例的信息。The following example uses the SERVERPROPERTY function in a SELECT statement to return information about the current instance of SQL ServerSQL Server.

SELECT  
  SERVERPROPERTY('MachineName') AS ComputerName,
  SERVERPROPERTY('ServerName') AS InstanceName,  
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('ProductVersion') AS ProductVersion,  
  SERVERPROPERTY('ProductLevel') AS ProductLevel;  
GO  

另请参阅See Also

SQL Server 2016 的版本和组件Editions and Components of SQL Server 2016