T-SQL Tuesday this month was all about LOB (large object) data. Thanks to all the great bloggers out there who participated! The participants this month posted some very impressive articles with information running the gamut from Reporting Services to SQL Server spatial data types to BLOB-handling in SSIS.
One thing I noticed immediately was a trend toward articles about spatial data (SQL Server 2008 Geography and Geometry data types, a very fun topic to explore if you haven’t played around with it yet!) So without further ado, and in no particular order, here’s the roundup:
· Joseph Richberg was first across the finish line with a discussion of a project in which he loads PDF files into SQL Server. Be sure to check out how he detects changes in large PDF files during the ETL process: http://josef-richberg.squarespace.com/journal/2010/5/10/t-sql-tuesday-6-what-about-blob.html.
· Prolific SQL Server author and blogger Stacia Misner posted an excellent tutorial on combining spatial data with other relational data and surfacing it all with Reporting Services. If you plan to do anything with spatial data in SQL Server 2008, BOOKMARK THIS NOW! http://blog.datainspirations.com/2010/05/11/sql-server-2008-r2-reporting-services-the-word-is-but-a-stage-t-sql-tuesday-006/.
· SQL Server guru Pinal Dave posted a great resource post for spatial data from around the world complete with demonstration queries at http://blog.sqlauthority.com/2010/05/11/sql-server-spatial-database-queries-what-about-blob-t-sql-tuesday-006/. This is one to bookmark folks!
· First of all, kudos to Jason Brimhall on that crazy picture of a blobfish! As a bonus, Jason gave us a great article on optimizing queries when LOB data is involved -- the discussion of the sys.dm_db_index_physical_stats DMV (plus the sample code) is worth the bookmark! http://jasonbrimhall.info/2010/05/11/t-sql-tuesday-006-a-blobbing-we-will-go/.
· Chad Miller grabbed my attention with his fascinating article on using Powershell to move BLOB data around. I haven’t done much with Powershell to date, but I’m going to use his article as my jumping off point for getting my hands dirty with this powerful scripting language. Check it out! http://sev17.com/2010/05/t-sql-tuesday-006-blobs-filestream-and-powershell/.
· Paul Randal provided the inside scoop on the negative performance effects of LOB data when you shrink your databases. DBAs in particular need to check this one out! http://sqlskills.com/BLOGS/PAUL/post/Why-LOB-data-make-shrink-run-slooooowly-(T-SQL-Tuesday-006).aspx.
· John “SSIS Master” Welch explored how SSIS handles LOB data. Check out his links to previous articles on how to move files to/from LOB columns using the Import Column and Export Column transformations. http://agilebi.com/cs/blogs/jwelch/archive/2010/05/11/t-sql-tuesday-006-lobs-in-the-ssis-dataflow.aspx.
· Stefan Bauer posted a nice article about setting up, configuring and testing Filestream data at http://stef-bauer.com/2010/05/10/t-sql-tuesday-006-configure-filestream-storage-for-blobs/.
· Michael Zilberstein wrote a nice article about some experiments he did to answer a question he had about LOB data and locking. http://sqlblog.com/blogs/michael_zilberstein/archive/2010/05/11/24999.aspx.
· Rob “Thunder From Down Under” Farley followed up his great article on exploded data with the Reporting Services map control with some pointers on how to get data ESRI shapefile data loaded into SQL Server to begin with. http://sqlblog.com/blogs/rob_farley/archive/2010/05/11/spatial-data-from-shapefiles-for-t-sql-tuesday-006.aspx.
· Steve “SQL Ninja” Jones took us on a trip in his wayback machine with a nice article about the history of LOB data and some ideas for how you can get the best performance out of your large object data: http://www.sqlservercentral.com/blogs/steve_jones/archive/2010/05/11/put-your-left-lob-in_2C00_-put-your-left-lob-out_2620_.aspx.
· Vamsi “the SQL Slugger” (!) posted hints for LOB data optimization at http://sqlslugger.com/archive/2010/05/t-sql-tuesday-006-what-about-blob/.
· I rounded out this month with a sample spatial database that I use in live demos and presentations. I created the database from the U.S. Census Bureau’s Tiger/Line database, and it’s definitely a good way to start playing with spatial data without the pain of creating your own spatial database from scratch. http://sqlblog.com/blogs/michael_coles/archive/2010/05/11/t-sql-tuesday-006-tiger-line-spatial-data.aspx.
Enjoy all the great articles and here’s looking forward to another great T-SQL Tuesday next month!