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.