You can get the following characteristics about your SQL Server:
- product version (for example, 12.x for SQL Server 2014)
- product level (RTM, Service Pack or CTP)
- edition (standard, enterprise, developer, etc.)
|
1 2 3 4 |
SELECT SERVERPROPERTY('ProductVersion') AS [Product Version], SERVERPROPERTY ('ProductLevel') AS [Product Level] , SERVERPROPERTY ('Edition') AS [Edition]; |
Product Version Product Level Edition
-------------------- -------------------- ------------------------------
12.0.2495.0 RTM Developer Edition (64-bit)
(1 row(s) affected)
The full list of server properties can be seen in MSDN article SERVERPROPERTY (Transact-SQL)
For the database you can get the current internal version number and compatibility level. With some extra effort you can get starting database version number, i.e. the version at which the database was created originally.
|
1 2 3 4 5 |
SELECT DATABASEPROPERTYEX('test', 'Version') as [Internal Version Number]; SELECT COMPATIBILITY_LEVEL FROM sys.databases WHERE NAME = 'test'; |
Internal Version Number
------------------------------
782
(1 row(s) affected)
COMPATIBILITY_LEVEL
-------------------
120
(1 row(s) affected)
To see the starting internal database number you should use DBCC DBINFO command.
|
1 2 3 |
DBCC TRACEON(3604); DBCC DBINFO('test'); |
DBINFO STRUCTURE:
DBINFO @0x000000000E29D8E0
dbi_version = 782 dbi_createVersion = 661 dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)
dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000)
...
Look at the first line starting with dbi_. The dbi_version is the current internal database version number (782 is SQL Server 2014), the dbi_createVersion is that of the moment of database creation (661 is 2008 R2). The list of these numbers can be found at this post.