Handle special data types

There are some data types introduced to SQL Server for use in special cases, like storing large objects in a database, or accessing the same objects stored in the file system through a database, and for working with spatial data. Hopefully, you won’t have to be able to write complex queries with geographical data involved for this exam, but you definitely should be able to select the right data type from a list of possible candidates. In this post, we’ll prepare ourselves for these kind of questions.

There is a beautiful acronym, BLOB, which stands for Binary Large Object. BLOBs can be MP3, or video, etc. As long as something is large and binary, it is valid to call it a BLOB. There are two places (from the aspect of ADO.NET) to store BLOBs. The first one is a database, the second is the file system. To work with BLOBs stored in a database, you just have to make yourself familiar with the following SQL types:

  • varbinary(max): stored data is in binary format
  • nvarchar(max): stored data is in text format

When you are dealing with binary data, (stored in a field of the database, which is of type varbinary) you have some help in the form of the GetBytes method of the SqlDataReader. Also, SqlDataReader exposes the GetString method for retrieving BLOBs in a string format (which are stored in varchar/nvarchar columns). Even better, the SqlDataAdapter is smart enough to retrieve these values in the appropriate .NET type, so a field of varbinary data will be converted into a .NET byte array.

As I mentioned before, BLOBs can live in the file system as well. In this case, you retrieve these BLOBs using the FileStream type. Notice that these BLOBs are stored in the file system, but must access them through SQL server.

Another important new types (new in SQL Server 2008 and ADO.NET 3.5 SP1) are the ones dealing with spatial data. Geometry supports planar data, based on the Euclidean system, and Geography supports ellipsoidal data, which makes it an ideal candidate for working with GPS data.

Last but not least, there are the Table Value Parameters, also new in SQL Server 2008. This one lets you pass a TABLE value as a parameter to a stored procedure, a real round-trip killer feature you’d love to use.

One thought on “Handle special data types

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s