Aaron Akin

March 18, 2009

Finding the major version number of your SQL Server instance

Filed under: SQL — Aaron Akin @ 8:38 pm

Occasionally, I need to determine which version of SQL Server I am connected to within a script so that I can write the proper code for that version. I’m never really concerned with the build number or which service pack I have installed. Instead, all I want is the major version number (7, 8, 9, 10, etc).

One of the difficult things about something that seems like it should be so simple is that some of the ways to check the version number only exist on certain versions. Other ways give you way more info than you really need.

Recently, I was digging through a system stored procedure, which is always the best place to find cool code, and I came across the undocumented system function @@MICROSOFTVERSION which is used internally by Microsoft.

It returns a seemingly random number, but you can use a bitwise AND operator to find the major version number.

SELECT @@MICROSOFTVERSION / 0x01000000

This will return the following based on your version of SQL Server:

7 (SQL 7 )
8 (SQL 2000)
9 (SQL 2005)
10 (SQL 2008)

This will work on any version of SQL Server as of now, but since it is undocumented, it’s always possible that it could be removed in the future.

Aaron

Advertisements

2 Comments »

  1. Excellent website. Plenty of helpful info here. I’m sending it to several friends ans additionally sharing in delicious. And naturally, thanks on your effort!

    Comment by scrap prices — August 6, 2012 @ 12:59 am | Reply

  2. How i can learn the sql server database version major (used 8,9,10) without restore database ? thnks, regards. 🙂

    Comment by mehmet — January 31, 2013 @ 8:39 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: