472
Part V: Enterprise Data Management
Result:Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64>
(Build 7601: Service Pack 1) (Hypervisor) (Build 7601: Service Pack 1)The fi rst line of the preceding result has the product version of SQL Server. In this example,
the SQL Server product version is 11.0.2100.60. The last line of the result can be confus-
ing. The last line reports the edition of SQL Server. In this example, it is SQL Server 2012
Developer Edition. However the service pack reported in the last line is the Windows ser-
vice pack level and not SQL Server service pack level. In this example, it is SP1 for Windows
Server 2008 R2. That is one of the reasons why not to use the SELECT @@VERSION com-
mand. Instead you can use the SERVERPROPERTY system function to determine the infor-
mation. The advantage of this method is that you can use the function as an expression
within a SELECT statement. The following example uses the SERVERPROPERTYfunction
to return the SQL Server product version, product level, and edition.SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition;Result:ProductVersion ProductLevel Edition
-------------- ------------ --------
11.0.2100.60 RTM Developer Edition (64-bit)In the preceding result, the ProductVersion indicates the SQL Server product version
number. The ProductLevel indicates the SQL Server product level. If a SQL Server service
pack is installed, the product level also indicates the service pack level. For example, if you
run the previous command against your SQL Server 2005 SP3 instance, the ProductLevel
returns SP3. The Edition indicates the SQL Server edition.Many of the confi guration properties do not take effect until SQL Server is restarted. For this reason, the General tab
in the SQL Server Properties (Confi gure) dialog box displays the current running values.Within code, many of the server properties are set by means of the sp_configure system
stored procedure. When executed without any parameters, this procedure reports the cur-
rent settings, as in the following code (with word-wrap adjusted to fi t on the page):EXEC sp_configure;c19.indd 472c19.indd 472 7/30/2012 5:42:46 PM7/30/2012 5:42:46 PM
http://www.it-ebooks.info