A Summary of why you should aggressively phrase out SQL IMAGE, TEXT and NTEXT

In my earlier post, I demonstrated the poor Sql Performance for Blobs (up to 4x worst). These are thankfully depreciated in SQL Server 2005[1] and soon may no longer exist. By Blobs I mean the following data types:

• Text
• nText
• Image

So in theory, no one should be using those for new development since 2005 – well, people have this horrible habit of not reading documentation – which keeps me very gainfully employed!

 

Some additional motivations to purge code of these are simple, they require much more complex coding to access – for example, reading an image from a column return in myReader (SqlDataReader) [2]:

 // Create a file to hold the output.
fs = new FileStream("employee" + emp_id + ".bmp", FileMode.OpenOrCreate, FileAccess.Write);
bw = new BinaryWriter(fs);
startIndex = 0;
retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
while (retval == bufferSize)
{
  bw.Write(outbyte);
  bw.Flush();
  startIndex += bufferSize;
  retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
}
bw.Write(outbyte, 0, (int)retval);
bw.Flush();
bw.Close();
fs.Close();

On the other hand, you can get the “image” in one click with:

• byte[] row[“VarBinaryDataColumn”]

or if you prefer it as a stream:

 

using (MemoryStream memStream = new MemoryStream((byte[])row["VarBinaryDataColumn"]))
{

} 

as for nText and Text, it’s the very simple:

• (string) row[“VarcharMaxColumn”]
• (string) row[“NVarcharMaxColumn”]

varbinary(max) allows storage of up to 2GB in one column…. but, for those folks that store HD movies in SQL Server, this is not sufficient…

 

The last item is the slick FILESTREAM feature on SQLServer --- which allows you to store bulky item in the file system and not in the database using the filestream class.  .Net Framework 3.5 adds a SqlFileStream class with some slickness because you can update fragments without having to read and rewrite the entire piece of data. See http://msdn.microsoft.com/en-us/library/cc716724.aspx for an example.

 

So the bottom line is simple:

• Blobs do not perform as well as the alternatives introduced in SQL Server 2005
• Blobs are more complex to code in C#, Java, VB etc, and thus more expensive to both initially write and test
• Last, but critical, they are going bye-bye!