Problem
After some recent SQL Server performance tuning, I noticed that one of my large core tables (important) has a column with a text data type. I had no idea that this table had a BLOB (binary large object) data type. After running some quick queries on the table\column and doing some quick analysis I have found that none of the entries exceed a few hundred characters. After talking to the development team, they indicated that a 500 character limit is enforced on the front end.
With this being said, I clearly do not need a column with the text data type and could use a varchar or nvarchar (Unicode) column with a width of 500. Since I was not aware of this particular BLOB column in one of my large core tables (important), I suspect some other tables in my environment have column text, image, ntext, etc data types. How can I easily find these columns on a per table basis without having to review them in SQL Server Enterprise Manager or SQL Server Management Studio?
Solution
BLOB data types (text, image, ntext, etc) may be a performance and storage issue in your environment especially if they are not needed. If you notice that queries that have the BLOB data types are performing poorly, it is worth trying to understand how the data is used and some alternatives to address the issue. As such, review the following scripts that query the corresponding system objects in SQL Server 2005 and SQL Server 2000 to determine if columns are using a BLOB data type:
SQL Server 2005 |
| SELECT o.[name], o.[object_id ], c.[object_id ], c.[name], t.[name] FROM sys.all_columns c INNER JOIN sys.all_objects o ON c.object_id = o.object_id INNER JOIN sys.types t ON c.system_type_id = t.system_type_id WHERE c.system_type_id IN (35, 165, 99, 34, 173) AND o.[name] NOT LIKE ‘sys%’ AND o.[name] <> ‘dtproperties’ AND o.[type] = ‘U’ GO |
SQL Server 2000 |
| SELECT o.[name], o.[id], c.[id], c.[name], t.[name] FROM dbo.syscolumns c INNER JOIN dbo.sysobjects o ON c.id = o.id INNER JOIN dbo.systypes t ON c.xtype = t.xtype WHERE c.xtype IN (35, 165, 99, 34, 173) AND o.[name] NOT LIKE ‘sys%’ AND o.[name] <> ‘dtproperties’ AND o.xtype = ‘U’ GO |
SQL Server System Data Type Lookup Values |
As a point of reference, below outlines the SQL Server system data type lookup values:
|
Next Steps
- Check out some of the databases in your development and test environments to see if they are using text, image, ntext, etc data types unexpectedly. If so, consider researching the issue further to see if the BLOB data type is really needed.
- Conduct some analysis on the tables\columns to find out an appropriate data type and length.
- Contact your development team to find out what data length is supported in the front end and middle tier of the application. In addition, validate with the development team that they are open to changing the data type and length as well as pick their brains in terms of any potential issues prior to testing.
- Make the application and database changes then test your applications to ensure they functionally operate as expected. In addition, take a peek at the performance for some of the queries to see their improvement.
- If you are unsure about the benefits of changing unneeded BLOB data types to varchar or nvarchar data types consider the following items as you test your applications and note these impacts in your environment:
- Performance improvements
- Storage reduction
- Improved backup and recovery times
- Check out this related tip – How to get length of data in Text, NText and Image columns
