| title | SERVERPROPERTY (Transact-SQL) | ||||||
|---|---|---|---|---|---|---|---|
| description | SERVERPROPERTY returns property information about the server instance | ||||||
| author | VanMSFT | ||||||
| ms.author | vanto | ||||||
| ms.reviewer | randolphwest, amvin87 | ||||||
| ms.date | 07/20/2022 | ||||||
| ms.prod | sql | ||||||
| ms.prod_service | database-engine, sql-database, synapse-analytics, pdw | ||||||
| ms.technology | t-sql | ||||||
| ms.topic | reference | ||||||
| f1_keywords |
|
||||||
| helpviewer_keywords |
|
||||||
| dev_langs |
|
||||||
| monikerRange | >= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current |
[!INCLUDE sql-asdb-asdbmi-asa-pdw]
Returns property information about the server instance.
:::image type="icon" source="../../database-engine/configure-windows/media/topic-link.gif" border="false"::: Transact-SQL Syntax Conventions
SERVERPROPERTY ( 'propertyname' )
Important
The [!INCLUDEssde_md] version numbers for [!INCLUDEssNoVersion] and [!INCLUDEssSDSfull] are not comparable with each other, and represent internal build numbers for these separate products. The [!INCLUDEssde_md] for [!INCLUDEssSDSfull] is based on the same code base as the [!INCLUDEssDEnoversion]. Most importantly, the [!INCLUDEssde_md] in [!INCLUDEssSDSfull] always has the newest SQL [!INCLUDEssde_md] bits. Version 12 of [!INCLUDEssSDSfull] is newer than version 15 of [!INCLUDEssNoVersion].
[!INCLUDEsql-server-tsql-previous-offline-documentation]
An expression that contains the property information to be returned for the server. propertyname can be one of the values below. Use of a propertyname that is invalid or not supported on that version of the Database Engine will return NULL.
| Property | Values returned |
|---|---|
BuildClrVersion |
Version of the [!INCLUDEmsCoName][!INCLUDEdnprdnshort] common language runtime (CLR) that was used while building the instance of [!INCLUDEssNoVersion]. NULL = Input isn't valid, an error, or not applicable. Base data type: nvarchar(128) |
Collation |
Name of the default collation for the server. NULL = Input isn't 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 isn't 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] 'Azure SQL Edge Developer' indicates the development only edition for Azure SQL Edge 'Azure SQL Edge' indicates the paid edition for Azure SQL Edge 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 = [!INCLUDEssSDS] or [!INCLUDEssSDW] -1461570097 = Azure SQL Edge Developer 1994083197 = Azure SQL Edge 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 Enterprise editions.) 4 = Express (This is returned for Express, Express with Tools, and Express with Advanced Services) 5 = [!INCLUDEssSDS] 6 = [!INCLUDEssDW] 8 = [!INCLUDEssSDSMIfull] 9 = Azure SQL Edge (This is returned for all editions of Azure SQL Edge) 11 = Azure Synapse serverless SQL pool Base data type: int |
FilestreamConfiguredLevel |
The configured level of FILESTREAM access. For more information, see filestream access level. Base data type: int |
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. Base data type: int |
FilestreamShareName |
The name of the share used by FILESTREAM. NULL = Input isn't valid, an error, or not applicable. Base data type: nvarchar(128) |
HadrManagerStatus |
Applies to: [!INCLUDEssSQL11] and later. Indicates whether the [!INCLUDEssHADR] manager has started. 0 = Not started, pending communication. 1 = Started and running. 2 = Not started and failed. NULL = Input isn't valid, an error, or not applicable. Base data type: int |
InstanceDefaultBackupPath |
Applies to: [!INCLUDEssSQL2019] and later. Name of the default path to the instance backup files. |
InstanceDefaultDataPath |
Applies to: [!INCLUDEssSQL11] through current version in updates beginning in late 2015. Name of the default path to the instance data files. Base data type: nvarchar(128) |
InstanceDefaultLogPath |
Applies to: [!INCLUDEssSQL11] through current version in updates beginning in late 2015. Name of the default path to the instance log files. Base data type: nvarchar(128) |
InstanceName |
Name of the instance to which the user is connected. Returns NULL if the instance name is the default instance, if the input isn't valid, or error. NULL = Input isn't 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 wasn't installed. Base data type: int |
IsBigDataCluster |
Introduced in [!INCLUDEssSQL2019] beginning with CU4. Returns 1 if the instance is SQL Server Big Data Cluster; 0 if not. Base data type: int |
IsClustered |
Server instance is configured in a failover cluster. 1 = Clustered. 0 = Not Clustered. NULL = Input isn't valid, an error, or not applicable. Base data type: int |
IsExternalAuthenticationOnly |
Applies to: Azure SQL Database and Azure SQL Managed Instance. Returns whether Azure AD-only authentication is enabled. 1 = Azure AD-only authentication is enabled. 0 = Azure AD-only authentication is disabled. 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 aren't installed. NULL = Input isn't valid, an error, or not applicable. Base data type: int |
IsHadrEnabled |
Applies to: [!INCLUDEssSQL11] and later. [!INCLUDEssHADR] is enabled on this server instance. 0 = The [!INCLUDEssHADR] feature is disabled. 1 = The [!INCLUDEssHADR] feature is enabled. NULL = Input isn't 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 Always On 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 isn't valid, an error, or not applicable. Base data type: int |
IsLocalDB |
Applies to: [!INCLUDEssSQL11] and later. Server is an instance of [!INCLUDEssExpress] LocalDB. NULL = Input isn't valid, an error, or not applicable. Base data type: int |
IsPolyBaseInstalled |
Applies to: [!INCLUDEssSQL2016]. Returns whether the server instance has the PolyBase feature installed. 0 = PolyBase isn't 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 isn't valid, an error, or not applicable. Base data type: int |
IsTempDbMetadataMemoryOptimized |
Applies to: [!INCLUDEssSQL2019] and later. Returns 1 if tempdb has been enabled to use memory-optimized tables for metadata; 0 if tempdb is using regular, disk-based tables for metadata. For more information, see tempdb Database. Base data type: int |
IsXTPSupported |
Applies to: SQL Server ([!INCLUDEssSQL14] and later), [!INCLUDEssSDS]. Server supports In-Memory OLTP. 1 = Server supports In-Memory OLTP. 0 = Server doesn't supports In-Memory OLTP. NULL = Input isn't 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 isn't 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 isn't valid, an error, or not applicable. Base data type: nvarchar(128) |
NumLicenses |
Unused. License information isn't preserved or maintained by the [!INCLUDEssNoVersion] product. Always returns NULL. Base data type: int |
PathSeparator |
Applies to: [!INCLUDEssSQL2017] and later. Returns \ on Windows and / on LinuxBase data type: nvarchar |
ProcessID |
Process ID of the [!INCLUDEssNoVersion] service. ProcessID is useful in identifying which Sqlservr.exe belongs to this instance. NULL = Input isn't valid, an error, or not applicable. Base data type: int |
ProductBuild |
Applies to: [!INCLUDEssSQL14] beginning October 2015. The build number. Base data type: nvarchar(128) |
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. Base data type: nvarchar(128) |
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. Base data type: nvarchar(128) |
ProductMinorVersion |
Applies to: [!INCLUDEssSQL11] through current version in updates beginning in late 2015. The minor version. Base data type: nvarchar(128) |
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. Base data type: nvarchar(128) |
ProductUpdateReference |
Applies to: [!INCLUDEssSQL11] through current version in updates beginning in late 2015. KB article for that release. Base data type: nvarchar(128) |
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 isn't 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) |
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, hasn't 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';
GOIf the local server name has been changed from the default server name at installation time, @@SERVERNAME returns the new name.
Starting with SQL Server 2019 CU1, the ServerName property of the SERVERPROPERTY function returns the Windows server name as it is saved, compared to previous major versions that used uppercase. This behavior changed back to uppercase between CU9 and CU12, but starting from CU13 the server name will return as it is saved.
If the Windows server name contains any lowercase characters, this change of behavior may cause differences between the ServerName property of the SERVERPROPERTY function, and @@SERVERNAME (uppercase versus lowercase), even if there is no name change for the server.
Consider you have a server named as server01, with a SQL Server instance named INST1. The following table summarizes the change of behavior between different builds of SQL Server 2019:
| SQL Server 2019 build | SERVERPROPERTY('ServerName') | Additional information |
|---|---|---|
| RTM | SERVER01\INST1 |
Returns the ServerName property in uppercase |
| CU1 – CU8 | server01\INST1 |
Returns the ServerName property as is, without changing to uppercase |
| CU9 – CU12 | SERVER01\INST1 |
Returns the ServerName property in uppercase |
| CU 13 and later | server01\INST1 |
Returns the ServerName property as-is, without changing to uppercase |
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