Microsoft SQL-Server 2008 Final Released

It is about time! I have been anxiously awaiting the much overdue and delayed release of Microsoft SQL-Server 2008. Microsoft just released SQL-Server 2008 to manufacturing, and if you are lucky enough to have a MSDN / Technet subscription, the SQL-Server 2008 download files are supposed to be available now.

For those that have to wait a bit longer to get their hands on a copy of SQL-Server 2008, you can spend the time waiting studying up on what is new in SQL-Server 2008 and be ready to plan your migration from SQL-Server 2005. Microsoft has put the new SQL-Server 2008 Books Online up for everyone to download, and I suggest doing so if you want to get a jump start reading up on the new features and upgrade / migration implications.

Oh, how can I forget? You can always download the 180-day TRIAL VERSION of SQL2008 until you get the real thing purchased and installed. Or, certainly performing an evaluation prior to purchase is an option, but call me a SQL-Server groupie... my past experience with SQL-Server 6, 6.5, 7, 2000, and 2005 has always been a positive one in that I absolutely was thrilled with the improved functionality and features with each subsequent release, and overall the database management system has been quite stable even prior to a "service pack 1" or such. SQL2005 annoyed me with how Microsoft made mega-changes to their query-scripting tools from SSMS that effectively removed functionality that SQL2000 offered, but that was probably my only major gripe.

I have been looking forward to some of the new features in SQL-Server 2008 for quite some time, like native data-compression for backup files, and native data encryption improvements (like indexable encrypted fields - about time!). But, as always, Microsoft annoys me with how certain enticing features (like the compressed-backups) are only enabled with certain "versions" of the product - like the Enterprise or Data Center version or what have you. So, before you get TOO excited about a new feature, make sure that whatever new SQL-2008 feature you want to use is actually included in the version of the product you buy or develop for and/or deploy databases too.

Either way, some features to keep an eye out for that are quite cool (at least in my opinion) and available in all or some versions of the product include:

• native data-compression and/or compressed backups
  
• indexable encrypted columns
• LINQ integration
• log-stream compression (nice for when you are using log-shipping for mirroring between various participants)
• Star-Join query optimizations and other data-warehouse related optimizations
• Office2007 Integration (if you care - I know I surely do not)... now you can export directly to Word. Perhaps I should open my mind to this a bit, since it would make giving ad-hoc query results to end-users and managers a bit easier in that they can be in a form those persons are more comfortable with than a CSV or text-file dump. heh.
• A resource governor - not sure if I will use it, but hey, it is there.
• FILESTREAM storage. In today's day of all sorts of binary data (like music files, be it MP3 or OGG or WAV or whatever, and video files like MPEG-4, etc.) there needs to be an easy way to make these accessible from database applications. Well, FileStream Storage is the answer, as it enables SQL Server applications to store unstructured data, such as documents and images, on the file system. I will most definitely be using this feature.
• Sparse Columns and Column Sets : these are columns that are optimized for storing null values when you have at least 20-40% of the values in a column as NULL. I know for sure I can use this to my advantage.
• Spatial Data handling. Basically this is geometry and geography type data handling related. I need to research more, but it may be of use if you are dealing with map data, etc.
• "Wide Tables" - how about 30,000 columns in your table? That is now possible, along with up to 1000 indexes. That should take care of most any "need" imaginable. Part of me worries that people without a clue with "design" insanely poor SQL-Server database now by creating a one-giant-file (versus making it relational correct or normalized) just because they can easily do so. Let's hope I am wrong! Though, if this is the case, I guess I will have a lot of consulting work FIXING it all after the fact. lol.
• Full-Text Search catalogs are integrated into the DB (meaning, you can move your database and have the catalogs automatically come along for the ride -- this sure beats the old SQL2005 implementation).

Transact-SQL (T-SQL) improvements / enhancements:

• COMPOUND OPERATORS - WOOHOO! Being able to type @myIntVar += 5 sure beats the current need to type SET @myIntVar = @myIntVar + 5 just because the TIME SAVINGS TYPING LESS STUFF!
  
• Finally, better Data AND Time datatypes support - not just "DateTime" any more.
• Grouping-Sets : an extension to the GROUP BY clause which has interesting potential... I am already dreaming up ways to use it, since it allows multiple groupings in the same query.
• the new MERGE SQL-statement. Call it a "smart" combo insert/update command of sorts. Should simplify some of my code :)
• Row Constructors : SQL-Server 2008 now allows you to insert multiple values with a single INSERT statement. Again, it is about time, as this will save a LOT of typing for some programs.
  
• Table Value Parameters -- I am looking forward to this feature, as it gives you the ability to pass an entire table to a stored procedure. But, unless by some miracle Microsoft took user feedback into consideration since late Beta versions, this was only *inbound* into a Stored Procedure (i.e., read-only by the SP) and did not allow you to output a table value parameter (i.e., you could't pass updated information back through the parm). To me, this rather crippled the whole "feature", but perhaps SQL-Server 2008 Service Pack 1 or SQL-Server 2008 Service Pack 2 will implement this? (wishful thinking?!)

Now, keep in mind that I tend to focus on Programmability Enhancements to the Database Engine, and other software development related aspects of the SQL-2008 upgrade, so you may want to look into the rest of the "What's New in SQL-Server 2008" information in detail in those SQL Books Online and / or in the MSDN Library on the web (msdn.microsoft.com/en-us/library). Have fun with SQL-Server 2008!