SERVERPROPERTY (Transact-SQL)

**APPLIES TO:** ![yes](media/yes.png)SQL Server (starting with 2008) ![yes](media/yes.png)Azure SQL Database ![yes](media/yes.png)Azure SQL Data Warehouse ![yes](media/yes.png)Parallel Data Warehouse

Returns property information about the server instance.

Topic link icon Transact-SQL Syntax Conventions

Syntax

SERVERPROPERTY ( 'propertyname' )  

Arguments

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]

8 = Managed Instance

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.

Return Types

sql_variant

Remarks

ServerName Property

The ServerName property of the SERVERPROPERTY function and [@@SERVERNAME](../../t-sql/functions/servername-transact-sql.md) return similar information. The ServerName property provides the Windows server and instance name that together make up the unique server instance. [@@SERVERNAME](../../t-sql/functions/servername-transact-sql.md) provides the currently configured local server name.

The ServerName property and [@@SERVERNAME](../../t-sql/functions/servername-transact-sql.md) 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](../../t-sql/functions/servername-transact-sql.md) returns the new name.

Version Properties

The SERVERPROPERTY function returns individual properties that relate to the version information whereas the [@@VERSION](../../t-sql/functions/version-transact-sql-configuration-functions.md) 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](../../t-sql/functions/version-transact-sql-configuration-functions.md) results.

[!NOTE]
We are aware of an issue where the version properties reported by SERVERPROPERTY are incorrect for Azure SQL Database. The version of the SQL Server database engine run by Azure SQL Database is always ahead of the on-premises version of SQL Server, and includes the latest security fixes. This means that the patch level is always on par with or ahead of the on-premises version of SQL Server, and that the latest features available in SQL Server are available in Azure SQL Database.

To programmatically determine the engine edition, use SELECT SERVERPROPERTY(’EngineEdition’). This query will return ‘5’ for standalone databases and ‘8’ for managed instances in Azure SQL Database.

We will update the documentation once this issue is resolved.

Permissions

All users can query the server properties.

Examples

The following example uses the SERVERPROPERTY function in a SELECT statement to return information about the current instance of SQL 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

Editions and Components of SQL Server 2016