Search:

SQL Server Articles

Submit Article

Home » Articles » SQL Server » System Functions

RSS Feeds

SERVERPROPERTY Function

Posted By: Sarita Patel Category: SQL Server Views: 2337

This article explains about SERVERPROPERTY function in sql server with examples.

SERVERPROPERTY function returns property information about the server instance.

Syntax of SERVERPROPERTY Function :

SERVERPROPERTY (propertyname)

propertyname is an expression containing the property information to be returned for the server. It can be one of below values.

Property Name
Returned Values
Collation
The name of the default collation for the server.
Returns NULL if invalid input or error.
Base data type: nvarchar
Edition
The edition of the Microsoft® SQL Server™ instance installed on the server.
Returns:
'Desktop Engine'
'Developer Edition'
'Enterprise Edition'
'Enterprise Evaluation Edition'
'Personal Edition'
'Standard Edition'
Base data type: nvarchar(128)
Engine Edition
The engine edition of the SQL Server instance installed on the server.
1 = Personal or Desktop Engine (MSDE 2000)
2 = Standard
3 = Enterprise (returned for Enterprise, Enterprise Evaluation, and Developer)
Base data type: int
InstanceName
The name of the instance to which the user is connected.
Returns NULL if the instance name is the default instance, or invalid input or error.
Base data type: nvarchar
IsClustered
The server instance is configured in a failover cluster.
1 = Clustered.
0 = Not Clustered.
NULL = Invalid input, or 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 = Invalid input, or error.
Base data type: int
IsIntegratedSecurityOnly
The server is in integrated security mode.
1 = Integrated Security.
0 = Not Integrated Security.
NULL = Invalid input, or error.
Base data type: int
IsSingleUser
The server is in single user mode.
1 = Single User.
0 = Not Single User
NULL = Invalid input, or error.
Base data type: int
IsSyncWithBackup
The database is either a published database or a distribution database, and can be restored without disrupting transactional replication.
1 = True.
0 = False.
Base data type: int
LicenseType
Mode of this instance of SQL Server.
PER_SEAT = Per-seat mode
PER_PROCESSOR = Per-processor mode
DISABLED = Licensing is disabled.
Base data type: nvarchar(128)
MachineName
Windows NT 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 Server, it returns the name of the virtual server.
Returns NULL if invalid input or error.
Base data type: nvarchar
NumLicenses
Number of client licenses registered for this instance of SQL Server, if in per-seat mode.
Number of processors licensed for this instance of SQL Server, if in per-processor mode.
Returns NULL if the server is none of the above.
Base data type: int
ProcessID
Process ID of the SQL Server service. (ProcessID is useful in identifying which sqlservr.exe belongs to this instance.)
Returns NULL if invalid input or error.
Base data type: int
ProductVersion
The version of the instance of SQL Server, in the form of 'major.minor.build'.
Base data type: varchar(128)
ProductLevel
The level of the version of the SQL Server instance.
Returns:
'RTM' = shipping version.
'SPn' = service pack version
'Bn', = beta version.
Base data type: nvarchar(128).
ServerName
Both the Windows NT server and instance information associated with a specified instance of SQL Server.
Returns NULL if invalid input or error.
Base data type: nvarchar

Return type of SERVERPROPERTY function is a sql_variant.

Examples of SERVERPROPERTY Function :

Example 1 : Use of SERVERPROPERTY function in select clause

SELECT SERVERPROPERTY('Edition')

Output

Enterprise Evaluation Edition

Above example returns edition of sql server installed in system.

Previous Post:

SCOPE_IDENTITY Function

Next Post:

SESSIONPROPERTY Function

Didn't find what you were looking for? Find more on SERVERPROPERTY Function Or get search suggestion and latest updates.

Sarita Patel author of SERVERPROPERTY Function is from United States.

View All Articles

Other Interesting Articles in SQL Server:

Please enter your Comment ** Comment should be atleast 30 Characters. Please put code inside [Code] your code [/Code]. Please login to post comment

No Comment Found, Be the First to post comment!

SQL Server
- View All
- Aggregate Functions
- Analytical Functions
- Configuration Function...
- Constraints
- Cursor
- Cursor Functions
- Data Control Language
- Data Definition Lang
- Data Manipulation Lang
- Data Type
- Database
- Date Time Functions
- Functions
- Index
- Interview FAQ
- Joins
- Math Functions
- Meta Data Functions
- Miscellaneous
- Operator
- Rowset Functions
- Security Functions
- Select Query
- Sequence
- Stored Procedures
- String Functions
- Sub Query
- System Functions
- System Statistical Fun...
- Text and Image Functio...
- Transaction Control La...
- Trigger
- View
Assembly Language
Artificial Intelligence
C Programming
C++ Programming
Visual C++
OOAD
Cobol
Java
Asp.net MVC
Rest and WCF Services
Entity Framework
Knockout.Js
Unix / Linux / Ubuntu
Networking
OOPs Concept
HTML
Dos
SQL
System Analysis & Design
Gadgets
Internet
CSS
Javascript
.Net Framework
Asp.net
C#
VB.Net
Python
Perl
Oracle
Software Engineering
RDBMS Terms
AJAX Framework
Design Pattern
UML
WPF
WCF
SEO
PowerShell
Visual Studio
WWF
BizTalk Server
Azure
General
Testing
Online Certifications
PHP
My SQL
LinQ
Project Management
Silverlight
XML
MS Office
Windows OS
DHTML
Sharepoint

RSS Feeds:	Articles \| Forum \| New Users \| Activities \| Interview FAQ \| Poll \| Hotlinks
Social Networking:	Hall of Fame \| Facebook \| Twitter \| LinkedIn
Terms:	Terms of Use \| Privacy Policy \| Contact us

Property Name	Returned Values
Collation	The name of the default collation for the server. Returns NULL if invalid input or error. Base data type: nvarchar
Edition	The edition of the Microsoft® SQL Server™ instance installed on the server. Returns: 'Desktop Engine' 'Developer Edition' 'Enterprise Edition' 'Enterprise Evaluation Edition' 'Personal Edition' 'Standard Edition' Base data type: nvarchar(128)
Engine Edition	The engine edition of the SQL Server instance installed on the server. 1 = Personal or Desktop Engine (MSDE 2000) 2 = Standard 3 = Enterprise (returned for Enterprise, Enterprise Evaluation, and Developer) Base data type: int
InstanceName	The name of the instance to which the user is connected. Returns NULL if the instance name is the default instance, or invalid input or error. Base data type: nvarchar
IsClustered	The server instance is configured in a failover cluster. 1 = Clustered. 0 = Not Clustered. NULL = Invalid input, or 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 = Invalid input, or error. Base data type: int
IsIntegratedSecurityOnly	The server is in integrated security mode. 1 = Integrated Security. 0 = Not Integrated Security. NULL = Invalid input, or error. Base data type: int
IsSingleUser	The server is in single user mode. 1 = Single User. 0 = Not Single User NULL = Invalid input, or error. Base data type: int
IsSyncWithBackup	The database is either a published database or a distribution database, and can be restored without disrupting transactional replication. 1 = True. 0 = False. Base data type: int
LicenseType	Mode of this instance of SQL Server. PER_SEAT = Per-seat mode PER_PROCESSOR = Per-processor mode DISABLED = Licensing is disabled. Base data type: nvarchar(128)
MachineName	Windows NT 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 Server, it returns the name of the virtual server. Returns NULL if invalid input or error. Base data type: nvarchar
NumLicenses	Number of client licenses registered for this instance of SQL Server, if in per-seat mode. Number of processors licensed for this instance of SQL Server, if in per-processor mode. Returns NULL if the server is none of the above. Base data type: int
ProcessID	Process ID of the SQL Server service. (ProcessID is useful in identifying which sqlservr.exe belongs to this instance.) Returns NULL if invalid input or error. Base data type: int
ProductVersion	The version of the instance of SQL Server, in the form of 'major.minor.build'. Base data type: varchar(128)
ProductLevel	The level of the version of the SQL Server instance. Returns: 'RTM' = shipping version. 'SPn' = service pack version 'Bn', = beta version. Base data type: nvarchar(128).
ServerName	Both the Windows NT server and instance information associated with a specified instance of SQL Server. Returns NULL if invalid input or error. Base data type: nvarchar