Ode to MAX
For the upcoming Canadian Launch Tour, we'll (of course) be covering various aspects of SQL Server 2005. This includes the improvements made to the Database Engine to incorporate various Transact-SQL enhancements. Now, as a developer who occasionally fulfills the role of database administrator, I'm a big fan of what the SQL Server team has done to incorporate these Transact-SQL enhancements. Not only will it make my life easier when writing queries, it will provide me with some new features I didn't have before. One of my favourite new features is something called the unified large object programming model. Now, don't let the name scare you. In fact, I certain you're going to love this.
We work with strings all the time. So much so that you'll often hear developers & DBAs say, "Life is but a string." Unfortunately, strings have some a particular downside; they can be difficult to work with once they become rather large. (We're talking a few MBs/GBs large...) No where was this more evident than with earlier versions of SQL Server. In a previous life - that is, prior to SQL Server 2005 - we were forced to ask ourselves the following question when designing schema: Just how long might this string be? The reason for this was due to the fact that strings that exceeded 8 KB had to be stored in unique data types inside SQL Server. Typically, these data types were text, ntext, and image. Moreover, there was a unique manner in which to deal with these data types which was dynamically different from those used for "smaller" strings (i.e. > 8 KB). This forced us to adjust and learn new ways to handle these data types.
In SQL Server 2005, the Database Engine now supports a unified large object programming model. Centering on this feature is the new max specifier. Through this specifier, developers may now utilize data types like nvarchar with max as its specified length (i.e. nvarchar(max)). Doing so allows expands the storage capabilities of these date types to support the same size strings as text, ntext, and image values up to 2 GB. Essentially, giving us the best of both worlds. Awesome stuff! And what's more, developers may continue to use these data types in the exact same manner as before. Sweet! I can continue to use the various T-SQL string functions I have come to know & love.
Oh, SQL Server 2005. How I love thee. Let me count the ways...
