| title | SERVERPROPERTY (Transact-SQL) | Microsoft Docs | ||||||
|---|---|---|---|---|---|---|---|
| ms.custom |
|
||||||
| ms.date | 03/02/2017 | ||||||
| ms.prod | sql-non-specified | ||||||
| ms.reviewer | |||||||
| ms.suite | |||||||
| ms.technology |
|
||||||
| ms.tgt_pltfrm | |||||||
| ms.topic | language-reference | ||||||
| f1_keywords |
|
||||||
| dev_langs |
|
||||||
| helpviewer_keywords |
|
||||||
| ms.assetid | 11e166fa-3dd2-42d8-ac4b-04f18c612c4a | ||||||
| caps.latest.revision | 128 | ||||||
| author | BYHAM | ||||||
| ms.author | rickbyh | ||||||
| manager | jhubbard | ||||||
| ms.workload | Active |
[!INCLUDEtsql-appliesto-ss2008-all_md]
Returns property information about the server instance.
Transact-SQL Syntax Conventions
SERVERPROPERTY ( 'propertyname' )
propertyname
Is an expression that contains the property information to be returned for the server. propertyname can be one of the following values.
| Property | Values returned |
|---|---|
| BuildClrVersion | Version of the [!INCLUDEmsCoName][!INCLUDEdnprdnshort] common language runtime (CLR) that was used while building the instance of [!INCLUDEssNoVersion]. NULL = Input is not valid, an error, or not applicable. Base data type: nvarchar(128) |
| Collation | Name of the default collation for the server. NULL = Input is not valid, or an error. Base data type: nvarchar(128) |
| CollationID | ID of the [!INCLUDEssNoVersion] collation. Base data type: int |
| ComparisonStyle | Windows comparison style of the collation. Base data type: int |
| ComputerNamePhysicalNetBIOS | NetBIOS name of the local computer on which the instance of [!INCLUDEssNoVersion] is currently running. For a clustered instance of [!INCLUDEssNoVersion] on a failover cluster, this value changes as the instance of [!INCLUDEssNoVersion] fails over to other nodes in the failover cluster. On a stand-alone instance of [!INCLUDEssNoVersion], this value remains constant and returns the same value as the MachineName property. Note: If the instance of [!INCLUDEssNoVersion] is in a failover cluster and you want to obtain the name of the failover clustered instance, use the MachineName property. NULL = Input is not valid, an error, or not applicable. Base data type: nvarchar(128) |
| Edition | Installed product edition of the instance of [!INCLUDEssNoVersion]. Use the value of this property to determine the features and the limits, such as Compute Capacity Limits by Edition of SQL Server. 64-bit versions of the [!INCLUDEssDE] append (64-bit) to the version. Returns: 'Enterprise Edition' ‘Enterprise Edition: Core-based Licensing’ 'Enterprise Evaluation Edition' ‘Business Intelligence Edition’ 'Developer Edition' 'Express Edition' 'Express Edition with Advanced Services' 'Standard Edition' 'Web Edition' 'SQL Azure' indicates [!INCLUDEssSDS] or [!INCLUDEssDW] Base data type: nvarchar(128) |
| EditionID | EditionID represents the installed product edition of the instance of [!INCLUDEssNoVersion]. Use the value of this property to determine features and limits, such as Compute Capacity Limits by Edition of SQL Server. 1804890536 = Enterprise 1872460670 = Enterprise Edition: Core-based Licensing 610778273= Enterprise Evaluation 284895786 = Business Intelligence -2117995310 = Developer -1592396055 = Express -133711905= Express with Advanced Services -1534726760 = Standard 1293598313 = Web 1674378470 = SQL Database or SQL Data Warehouse Base data type: bigint |
| EngineEdition | [!INCLUDEssDE] edition of the instance of [!INCLUDEssNoVersion] installed on the server. 1 = Personal or Desktop Engine (Not available in [!INCLUDEssVersion2005] and later versions.) 2 = Standard (This is returned for Standard, Web, and Business Intelligence.) 3 = Enterprise (This is returned for Evaluation, Developer, and both Enterprise editions.) 4 = Express (This is returned for Express, Express with Tools and Express with Advanced Services) 5 = [!INCLUDEssSDS] 6 - [!INCLUDEssDW] Base data type: int |
| HadrManagerStatus | Applies to: [!INCLUDEssSQL11] through [!INCLUDEssCurrent]. Indicates whether the [!INCLUDEssHADR] manager has started. 0 = Not started, pending communication. 1 = Started and running. 2 = Not started and failed. NULL = Input is not valid, an error, or not applicable. |
| InstanceDefaultDataPath | Applies to: [!INCLUDEssSQL11] through current version in updates beginning in late 2015. Name of the default path to the instance data files. |
| InstanceDefaultLogPath | Applies to: [!INCLUDEssSQL11] through current version in updates beginning in late 2015. Name of the default path to the instance log files. |
| InstanceName | Name of the instance to which the user is connected. Returns NULL if the instance name is the default instance, if the input is not valid, or error. NULL = Input is not valid, an error, or not applicable. Base data type: nvarchar(128) |
| IsAdvancedAnalyticsInstalled | Returns 1 if the Advanced Analytics feature was installed during setup; 0 if Advanced Analytics was not installed. |
| IsClustered | Server instance is configured in a failover cluster. 1 = Clustered. 0 = Not Clustered. NULL = Input is not valid, an error, or not applicable. Base data type: int |
| IsFullTextInstalled | The full-text and semantic indexing components are installed on the current instance of [!INCLUDEssNoVersion]. 1 = Full-text and semantic indexing components are installed. 0 = Full-text and semantic indexing components are not installed. NULL = Input is not valid, an error, or not applicable. Base data type: int |
| IsHadrEnabled | Applies to: [!INCLUDEssSQL11] through [!INCLUDEssCurrent]. [!INCLUDEssHADR] is enabled on this server instance. 0 = The [!INCLUDEssHADR] feature is disabled. 1 = The [!INCLUDEssHADR] feature is enabled. NULL = Input is not valid, an error, or not applicable. Base data type: int For availability replicas to be created and run on an instance of [!INCLUDEssNoVersion], [!INCLUDEssHADR] must be enabled on the server instance. For more information, see Enable and Disable AlwaysOn Availability Groups (SQL Server). Note: The IsHadrEnabled property pertains only to [!INCLUDEssHADR]. Other high availability or disaster recovery features, such as database mirroring or log shipping, are unaffected by this server property. |
| IsIntegratedSecurityOnly | Server is in integrated security mode. 1 = Integrated security (Windows Authentication) 0 = Not integrated security. (Both Windows Authentication and [!INCLUDEssNoVersion] Authentication.) NULL = Input is not valid, an error, or not applicable. Base data type: int |
| IsLocalDB | Applies to: [!INCLUDEssSQL11] through [!INCLUDEssCurrent]. Server is an instance of [!INCLUDEssExpress] LocalDB. NULL = Input is not valid, an error, or not applicable. |
| IsPolybaseInstalled | Applies to: [!INCLUDEssCurrent]. Returns whether the server instance has the PolyBase feature installed. 0 = PolyBase is not installed. 1 = PolyBase is installed. Base data type: int |
| IsSingleUser | Server is in single-user mode. 1 = Single user. 0 = Not single user NULL = Input is not valid, an error, or not applicable. Base data type: int |
| IsXTPSupported | Applies to: SQL Server ([!INCLUDEssSQL14] through [!INCLUDEssCurrent]), [!INCLUDEssSDS]. Server supports In-Memory OLTP. 1= Server supports In-Memory OLTP. 0= Server does not supports In-Memory OLTP. NULL = Input is not valid, an error, or not applicable. Base data type: int |
| LCID | Windows locale identifier (LCID) of the collation. Base data type: int |
| LicenseType | Unused. License information is not preserved or maintained by the [!INCLUDEssNoVersion] product. Always returns DISABLED. Base data type: nvarchar(128) |
| MachineName | Windows computer name on which the server instance is running. For a clustered instance, an instance of [!INCLUDEssNoVersion] running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server. NULL = Input is not valid, an error, or not applicable. Base data type: nvarchar(128) |
| NumLicenses | Unused. License information is not preserved or maintained by the [!INCLUDEssNoVersion] product. Always returns NULL. Base data type: int |
| ProcessID | Process ID of the [!INCLUDEssNoVersion] service. ProcessID is useful in identifying which Sqlservr.exe belongs to this instance. NULL = Input is not valid, an error, or not applicable. Base data type: int |
| ProductBuild | Applies to: [!INCLUDEssSQL14] beginning October, 2015. The build number. |
| ProductBuildType | Applies to: [!INCLUDEssSQL11] through current version in updates beginning in late 2015. Type of build of the current build. Returns one of the following: OD = On Demand release a specific customer. GDR = General Distribution Release released through windows update. NULL = Not applicable. |
| ProductLevel | Level of the version of the instance of [!INCLUDEssNoVersion]. Returns one of the following: 'RTM' = Original release version 'SPn' = Service pack version 'CTPn', = Community Technology Preview version Base data type: nvarchar(128) |
| ProductMajorVersion | Applies to: [!INCLUDEssSQL11] through current version in updates beginning in late 2015. The major version. |
| ProductMinorVersion | Applies to: [!INCLUDEssSQL11] through current version in updates beginning in late 2015. The minor version. |
| ProductUpdateLevel | Applies to: [!INCLUDEssSQL11] through current version in updates beginning in late 2015. Update level of the current build. CU indicates a cumulative update. Returns one of the following: CUn = Cumulative Update NULL = Not applicable. |
| ProductUpdateReference | Applies to: [!INCLUDEssSQL11] through current version in updates beginning in late 2015. KB article for that release. |
| ProductVersion | Version of the instance of [!INCLUDEssNoVersion], in the form of 'major.minor.build.revision'. Base data type: nvarchar(128) |
| ResourceLastUpdateDateTime | Returns the date and time that the Resource database was last updated. Base data type: datetime |
| ResourceVersion | Returns the version Resource database. Base data type: nvarchar(128) |
| ServerName | Both the Windows server and instance information associated with a specified instance of [!INCLUDEssNoVersion]. NULL = Input is not valid, or an error. Base data type: nvarchar(128) |
| SqlCharSet | The SQL character set ID from the collation ID. Base data type: tinyint |
| SqlCharSetName | The SQL character set name from the collation. Base data type: nvarchar(128) |
| SqlSortOrder | The SQL sort order ID from the collation Base data type: tinyint |
| SqlSortOrderName | The SQL sort order name from the collation. Base data type: nvarchar(128) |
| FilestreamShareName | The name of the share used by FILESTREAM. NULL = Input is not valid, an error, or not applicable. |
| FilestreamConfiguredLevel | The configured level of FILESTREAM access. For more information, see filestream access level. |
| FilestreamEffectiveLevel | The effective level of FILESTREAM access. 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. |
sql_variant
The ServerName property of the SERVERPROPERTY function and @@SERVERNAME return similar information. The ServerName property provides the Windows server and instance name that together make up the unique server instance. @@SERVERNAME provides the currently configured local server name.
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
If the local server name has been changed from the default server name at installation time, @@SERVERNAME returns the new name.
The SERVERPROPERTY function returns individual properties that relate to the version information whereas the @@VERSION function combines the output into one string. If your application requires individual property strings, you can use the SERVERPROPERTY function to return them instead of parsing the @@VERSION results.
All users can query the server properties.
The following example uses the SERVERPROPERTY function in a SELECT statement to return information about the current instance of [!INCLUDEssNoVersion_md].
SELECT
SERVERPROPERTY('MachineName') AS ComputerName,
SERVERPROPERTY('ServerName') AS InstanceName,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel;
GO