For Part 2 of the Handy SQL Server Function Series I decided to tackle parsing useful information from the @@VERSION function, because I am an idiot. It turns out I was confused about CHARINDEX() vs. PATINDEX() and it pretty much invalidated my original solution. All is not lost though, this mistake turned out to be informative for me, and hopefully for you.
Referring back to the "Version" view in the prelude I started with the following query to extract the version number:
SELECT DISTINCT SQLVersion, SUBSTRING(VersionString,PATINDEX('%-%',VersionString)+2, 12) VerNum
FROM VERSION
I used PATINDEX() to find the first hyphen "-" character in the string, since the version number appears 2 positions after it, and got these results:
SQLVersion VerNum
----------- ------------
2000 8.00.2055 (I
2005 9.00.3080.00
2005 9.00.4053.00
2008 10.50.1600.1
As you can see it was good enough for most of the values, but not for the SQL 2000 @@VERSION. You'll notice it has only 3 version sections/octets where the others have 4, and the SUBSTRING() grabbed the non-numeric characters after. To properly parse the version number will require a non-fixed value for the 3rd parameter of SUBSTRING(), which is the number of characters to extract.
The best value is the position of the first space to occur after the version number (VN), the trick is to figure out how to find it. Here's where my confusion about PATINDEX() came about. The CHARINDEX() function has a handy optional 3rd parameter:
CHARINDEX (expression1 ,expression2 [ ,start_location ] )
While PATINDEX():
PATINDEX ('%pattern%',expression )
Does not. I had expected to use PATINDEX() to start searching for a space AFTER the position of the VN, but it doesn't work that way. Since there are plenty of spaces before the VN, I thought I'd try PATINDEX() on another character that doesn't appear before, and tried "(":
SELECT SQLVersion, SUBSTRING(VersionString,PATINDEX('%-%',VersionString)+2, PATINDEX('%(%',VersionString))
FROM VERSION
Unfortunately this messes up the length calculation and yields:
SQLVersion VerNum
----------- ---------------------------
2000 8.00.2055 (Intel X86)
Dec 16 2008 19:4
2005 9.00.3080.00 (Intel X86)
Sep 6 2009 01:
2005 9.00.4053.00 (Intel X86)
May 26 2009 14:
2008 10.50.1600.1 (Intel X86)
Apr
2008 10.50.1600.1 (X64)
Apr 2 20
Yuck. The problem is that PATINDEX() returns position, and SUBSTRING() needs length, so I have to subtract the VN starting position:
SELECT SQLVersion, SUBSTRING(VersionString,PATINDEX('%-%',VersionString)+2, PATINDEX('%(%',VersionString)-PATINDEX('%-%',VersionString)) VerNum
FROM VERSION
And the results are:
SQLVersion VerNum
----------- --------------------------------------------------------
2000 8.00.2055 (I
2005 9.00.4053.00 (I
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
Ummmm, whoops. Turns out SQL Server 2008 R2 includes "(RTM)" before the VN, and that causes the length to turn negative.
So now that that blew up, I started to think about matching digit and dot (.) patterns. Sadly, a quick look at the first set of results will quickly scuttle that idea, since different versions have different digit patterns and lengths.
At this point (which took far longer than I wanted) I decided to cut my losses and redo the query using CHARINDEX(), which I'll cover in Part 2.2.
So to do a little post-mortem on this technique:
- PATINDEX() doesn't have the flexibility to match the digit pattern of the version number;
- PATINDEX() doesn't have a "start" parameter like CHARINDEX(), that allows us to skip over parts of the string;
- The SUBSTRING() expression is getting pretty complicated for this relatively simple task!
This doesn't mean that PATINDEX() isn't useful, it's just not a good fit for this particular problem. I'll include a version in the next post that extracts the version number properly.
UPDATE: Sorry if you saw the unformatted version of this earlier, I'm on a quest to find blog software that ACTUALLY WORKS.
Just a quick post I should've done yesterday but I was recovering from SQL Saturday #48 in Columbia, SC, where I went to some really excellent sessions by some very smart experts. If you have not yet attended a SQL Saturday, or its been more than 1 month since you last did, SIGN UP NOW!
While searching the OBJECT_DEFINITION() of SQL Server system procedures I stumbled across the DEFAULT_DOMAIN() function in xp_grantlogin and xp_revokelogin. I couldn't find any information on it in Books Online, and it's a very simple, self-explanatory function, but it could be useful if you work in a multi-domain environment. It's also the kind of neat thing you can find by using this query:
SELECT OBJECT_SCHEMA_NAME([object_id]) object_schema, name
FROM sys.all_objects
WHERE OBJECT_DEFINITION([object_id]) LIKE '%()%'
ORDER BY 1,2
I'll post some elaborations and enhancements to this query in a later post, but it will get you started exploring the functional SQL Server sea.
UPDATE: I goofed earlier and said SQL Saturday #46 was in Columbia. It's actually SQL Saturday #48, and SQL Saturday #46 was in Raleigh, NC.