How can I tell what edition of SQL Server runs on the machine?
select @@version
Sample Output
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7600: )
If you just want to get the edition, you can use:
select serverproperty('Edition')
To use in an automated script, you can get the edition ID, which is an integer:
select serverproperty('EditionID')
- -1253826760 = Desktop
- -1592396055 = Express
- -1534726760 = Standard
- 1333529388 = Workgroup
- 1804890536 = Enterprise
- -323382091 = Personal
- -2117995310 = Developer
- 610778273 = Enterprise Evaluation
- 1044790755 = Windows Embedded SQL
- 4161255391 = Express with Advanced Services
How to check whether the installed instance is full SQL Server or just SQL Server Express
You can look at the installed instances in the registry key:
Software\Microsoft\Microsoft SQL Server\InstalledInstances
This will contain all the installed instances, e.g. on my system:
MSSQLSERVER
SQLEXPRESS
Go into this registry key with this value:
Software\Microsoft\Microsoft SQL Server\Instance Names\SQL
to get the actual instance name that you need in the next step.
Now if you go look at the registry key:
Software\Microsoft\Microsoft SQL Server\(InstanceName)\Setup\Edition
there you have a value of e.g. Express
for a SQL Server Express, or Developer Edition
or something else. That should tell you if you have Express
or another, "full" edition of SQL Server
How to check SQL Server version
Following are possible ways to see the version:
Method 1: Connect to the instance of SQL Server, and then run the following query:
Select @@version
An example of the output of this query is as follows:
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009
10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Express
Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
Method 2: Connect to the server by using Object Explorer in SQL Server Management Studio. After Object Explorer is connected, it will show the version information in parentheses, together with the user name that is used to connect to the specific instance of SQL Server.
Method 3: Look at the first few lines of the Errorlog file for that instance. By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG
and ERRORLOG.n
files. The entries may resemble the following:
2011-03-27 22:31:33.50 Server Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
As you can see, this entry gives all the necessary information about the product, such as version, product level, 64-bit versus 32-bit, the edition of SQL Server, and the OS version on which SQL Server is running.
Method 4: Connect to the instance of SQL Server, and then run the following query:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Note This query works with any instance of SQL Server 2000 or of a later version
How can I determine installed SQL Server instances and their versions?
At a command line:
SQLCMD -L
or
OSQL -L
(Note: must be a capital L)
This will list all the sql servers installed on your network. There are configuration options you can set to prevent a SQL Server from showing in the list. To do this...
At command line:
svrnetcn
In the enabled protocols list, select 'TCP/IP', then click properties. There is a check box for 'Hide server'.
SQL Server file names vs versions
The mapping is (along with links to build lists):
Major Version / Compatibility Level | Version Label | File Version |
---|---|---|
80 | SQL Server 2000 | 8.00.xxxx |
90 | SQL Server 2005 | 9.00.xxxx |
100 | SQL Server 2008 | 10.00.xxxx |
105 | SQL Server 2008 R2 | 10.50.xxxx |
110 | SQL Server 2012 | 11.00.xxxx |
120 | SQL Server 2014 | 12.00.xxxx |
130 | SQL Server 2016 | 13.00.xxxx |
140 | SQL Server 2017 | 14.00.xxxx |
150 | SQL Server 2019 | 15.00.xxxx |
160 | SQL Server 2022 | 16.00.xxxx |
How do I check for the SQL Server Version using Powershell?
Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTimeout 3
http://msdn.microsoft.com/en-us/library/cc281847.aspx
SQL server shows different versions when seen from about screen and from query
You can see version of SQL Server on local computer by using Help\About
. but when run select @@Version
, you see version of SQL Server database engine that login on it. In other word you run SSMS from local computer and Help\About
show version of SSMS. and then login to sql server database engine of your server, Select @@Version
show version of sql server on the server.
Related Topics
Why Select Top Clause Could Lead to Long Time Cost
Efficient Way to Convert Second to Minute and Seconds in SQL Server 2005
Postgresql Query to Detect Overlapping Time Ranges
What Is "Structured" in Structured Query Language
Why Use the Between Operator When We Can Do Without It
What Is the Ms SQL Server Capability Similar to the MySQL Field() Function
Linq to SQL Every Nth Row from Table
Using a Having Clause in an Update Statement
Is Order in a Subquery Guaranteed to Be Preserved
How to Script Out Stored Procedures to Files
Minimizing SQL Queries Using Join with One-To-Many Relationship
Set Time Portion of a Datetime Variable
SQL Server 2005: Insert Multiple Rows with Single Query
Pairwise Array Sum Aggregate Function