T-SQL Tuesday #006: “What About BLOB?”

Invitation and roundup from Michael Coles.

MSDN conveniently defines Large Object (“LOB”) data types for us: “LOB data types are those that exceed the maximum row size of 8 kilobytes (KB).”

There have been a several improvements in LOB data functionality in SQL Server 2008 (there were even some in SQL Server 2005).  In 2008 the XML, GEOMETRY, GEOGRAPHY data types can all hold 2.1 GB of data.  CLR data types can also hold up to 2.1 GB of data.  So the question of the day is how do you use LOB data?  Here are a few possible starting points:

  • LOB data storage, optimization, limitations, “under-the-hood”
  • Indexing, querying, optimization, tricks, tips, performance tuning of LOB data
  • Interesting uses/projects for LOB data types:
    • The MAX data types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX))
    • XML
    • GEOMETRY/GEOGRAPHY (spatial)
    • CLR data types
  • FILESTREAM hints, tips, tricks, .NET SqlFileStream Class

The only rule is that your topic has to involve SQL Server’s LOB data types in some form.  If you want to demonstrate handling LOB data in .NET, for instance, go for it.  If you want to demonstrate Oracle LOB data handling, this might not be the place to do it (although a comparison of the two might be interesting…) 🙂