Born to Thrive

One of the abilities being checked on the 70-561 exam is the ability of handling special data types: Binary Large Objects (BLOBs), Filestream, Spatial, Table Value Parameters. From my experience I can tell that developers more of less are familiar with Large Objects whether it's BLOB (Varbinary (Max)) or CLOB (Varchar(Max)). But filestream, spatial (comprehensive support for geography and geometry) and table value parameters are new features of Microsoft SQL 2008, and as such they're still fairly new for  most .NET developers. And if to look closely at those new features, they can very useful for all developers working with MS SQL 2008 database. Below are the links to the information on those new features from the SQL Server 2008 perspective.

• BLOB
• Filestream
• Spatial
• Table value parameters

I, however, have focused on .NET code that would "handle special data types", primairly by writing code that would demonstrate how to read and write data stored by special data types. I have combinec BLOB with Filestream as BLOB is commonly used by developers so I didn't think it would deserve a separate attanetion. The code will use a sample database created in SQL Server 2008 with filestream enabled. Note that specified filestream folder must not exist during creation of the database.
[Database script]

The script creates a database with 3 tables - TABLE_FS will be used to demonstrate filestream by inserting a photo; TABLE_SPATIAL will be used to demonstrate spatial types - geography and geometry; USERS will be used to store username and hashed password to demonstrate using table type parameters.
[Reading Filestream]

[Writing Filestream]

[Reading Spatial]

[Writing Spatial]

[Testing table type parameters]

After having looked at the code, you can notice that there's a few things a developer needs to remember when using the aforementioned special data types. For example, table with filestream requires column with uniqueidentified type and also reading and writing data to filestream requires a transaction context. If you go to a filestream folder, you will see there files - without extensions. If you copy it and change extension, you will get an identical copy of the file you wrote to database. As far as spatial types, I used a linestring object, which represents a line connecting a sequence of points, and function STLength to demonstrate diffrences of the line in geography and geometry realm. Table type parameter was used within user defined function that checked if given username and password are correct by comparing it with the records in USER table. It uses DataTable to store records to pass to the function. Password is hashed with salt using private key and SHA512 algorithm. So there is not really a lot to remember or a lot of new things a developer have to learn - most of them are straightforward, however there are few important things that you have to remember like using transaction context, uniqueidentifier column, or using BinaryReader to get a value from a stream of a spatial data type column, but I don't think this topic now will be a killer on the exam.