Share via

  • Article

SERVERPROPERTY (Transact-SQL)

Returns property information about the server instance in SQL Server 2008 R2.

Topic link iconTransact-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 Microsoft .NET Framework common language runtime (CLR) that was used while building the instance of SQL Server.

    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 SQL Server 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 SQL Server is currently running.

    For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster.

    On a stand-alone instance of SQL Server, this value remains constant and returns the same value as the MachineName property.

    NoteNote
    If the instance of SQL Server 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, or an error.

    Base data type: nvarchar(128)

    Edition

    Installed product edition of the instance of SQL Server. Use the value of this property to determine the features and the limits, such as maximum number of CPUs that are supported by the installed product. 64-bit versions of the Database Engine append (64-bit) to the version.

    Returns:

    'Data Center Edition'

    'Desktop Engine' (Not available for SQL Server 2005 and later versions.)

    'Developer Edition'

    'Enterprise Edition'

    'Enterprise Evaluation Edition'

    'Express Edition'

    'Express Edition with Advanced Services'

    'Personal Edition' (Not available for SQL Server 2005 and later versions.)

    'Small Business Server Edition'

    'Standard Edition'

    'Web Edition'

    'Windows Embedded SQL'

    'Workgroup Edition'

    Base data type: nvarchar(128)

    EditionID

    Is an identification number that represents the installed product edition of the instance of SQL Server. Use the value of this property to determine features and limits, such as maximum number of CPUs that are supported by the installed product.

    -978676123 = Data Center

    -1253826760 = Desktop

    -2117995310 = Developer

    -1592396055 = Express

    1804890536 = Enterprise

    610778273= Enterprise Evaluation

    -133711905= Express with Advanced Services

    -323382091 = Personal

    -1960233010 = Small Business Server

    -1534726760 = Standard

    1293598313 = Web

    -3250176541 = Windows Embedded SQL

    1333529388 = Workgroup

    Base data type: bigint

    EngineEdition

    Database Engine edition of the instance of SQL Server installed on the server.

    1 = Personal or Desktop Engine (Not available in SQL Server 2005 and later versions.)

    2 = Standard (This is returned for Standard, Small Business Server, Web and Workgroup.)

    3 = Enterprise (This is returned for Enterprise, Enterprise Evaluation, Data Center, and Developer.)

    4 = Express (This is returned for Express, Express with Advanced Services, and Windows Embedded SQL.)

    5 = SQL Azure

    Base data type: int

    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.

    Base data type: nvarchar(128)

    IsClustered

    Server instance is configured in a failover cluster.

    1 = Clustered.

    0 = Not Clustered.

    NULL = Input is not valid, or an error.

    Base data type: int

    IsFullTextInstalled

    The full-text component is installed with the current instance of SQL Server.

    1 = Full-text is installed.

    0 = Full-text is not installed.

    NULL = Input is not valid, or an error.

    Base data type: int

    IsIntegratedSecurityOnly

    Server is in integrated security mode.

    1 = Integrated security. (Windows Authentication)

    0 = Not integrated security. (Both Windows Authentication and SQL Server Authentication.)

    NULL = Input is not valid, or an error.

    Base data type: int

    IsSingleUser

    Server is in single-user mode.

    1 = Single user.

    0 = Not single user

    NULL = Input is not valid, or an error.

    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 SQL Server 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 SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.

    NULL = Input is not valid, or an error.

    Base data type: nvarchar(128)

    NumLicenses

    Unused. License information is not preserved or maintained by the SQL Server product. Always returns NULL.

    Base data type: int

    ProcessID

    Process ID of the SQL Server service. ProcessID is useful in identifying which Sqlservr.exe belongs to this instance.

    NULL = Input is not valid or an error.

    Base data type: int

    ProductVersion

    Version of the instance of SQL Server, in the form of 'major.minor.build'.

    Base data type: nvarchar(128)

    ProductLevel

    Level of the version of the instance of SQL Server.

    Returns one of the following:

    'RTM' = Original release version

    'SPn' = Service pack version

    'CTP', = Community Technology Preview version

    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 SQL Server.

    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.

    FilestreamConfiguredLevel

    The configured level of FILESTREAM access. For more information, see filestream access level Option.

    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 Option.

Return Types

sql_variant

Remarks

ServerName Property

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.

Version Properties

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.

Examples

The following example uses the SERVERPROPERTY function in a SELECT statement to return information about the current server. This scenario is useful when there are multiple instances of SQL Server installed on a Windows server, and the client must open another connection to the same instance used by the current connection.

SELECT CONVERT(sysname, SERVERPROPERTY('servername'));
GO

The following example uses the SERVERPROPERTY function in a SELECT statement to return version information about the product.

SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition;
GO