For those of you who have programs which work with November CTP (CTP-5) and February CTP (CTP-6) and use the Geography data type, you should already be aware of Isaac’s posts on this subject: Latitude-Longitude Ordering and The Upcoming Geography Coordinate Order Swap—A FAQ.
The next CTP, most likely called the May CTP (CTP-6 Refresh), will contain the Geography data type coordinate order swap for WKT/WKB-based geographic coordinates. In order to make sure that your programs work correctly with all SQL Server 2008 CTP’s and RTM, you will need a way of detecting the version of SQL Server after which the change was made. The last publicly available SQL Server 2008 build was CTP-6, which had a product version number of 10.0.1300.13. The next publicly available SQL Server 2008 build will contain the Geography coordinate order swap, so this product version number (10.0.1300.13) should be used as the switch point.
The product version string is constructed as follows: <Major Version>.<Minor Version>.<Build>.<Revision>
So, how do you retrieve the product version number? One way to do this would be to issue the SQL query:
which will yield something like this:
Microsoft SQL Server code name “Katmai” (CTP) – 10.0.1300.13 (Intel X86) Feb 8 2008 00:06:52 Copyright (c) 1988-2007 Microsoft Corporation Developer Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1, v.744)
from which you can extract the product version number. You can also use the ProductVersion SERVERPROPERTY value to avoid parsing the entire @@version string:
which will yield something like the following:
In previous releases, SQL Server has had 00’s in the minor version number. Consequently, you should not rely on string comparison to do a numeric sort as 10.00.1300.13 would cause a difference in sort order than 10.0.1300.13.
If you are using .NET code, you can reuse the System.Version class for this purpose and extract the product version components from there. If you already have a check somewhere in your program to determine if you are running against SQL Server 2008, it might make sense to consolidate the version checks in one spot.