Some time ago I made to some large width SQL server tables providing for lots of spare fields consisting of various data types. These tables could be used for system stakeholders to add new things they needed to store against products in our ERP system over time without disturbing me for more fields. The SQL server description property for the column was used to populate the labels on the GUI forms dynamically with the meaning of the field. Thus there is no need for a separate table to hold the label information and that also means that the information is at hand when working in SQL.
At the time I used a TSQL snippet from the web to grab the descriptions. The snippet used the sys.columns, sys.extended_properties to gather the info required to for the GUI labels. The query has been causing problems over the years, running for up to 2 seconds is not uncommon. After finally getting a reason to revisit the forms, I went to look at the query plan for and uncovered the cause. A scan due to the where clause that containedWHERE OBJECT_NAME(c.object_id)=@TableName
To avoid calling the OBJECT_NAME function for each comparison row in the DB, I added the sys.objects table to create a lookup join that fetches the object id for the table owning the columns and now the query is running in sub-second time spans. This is a great improvement, resulting in a snappy GUI form open, unlike the sluggish experience the users were getting previously. This points out again how you have to take care calling functions for each row as it expensive, set operations almost always are the way to go!
The resulting improved stored procedure containing the optimised query for my reference is below. I wish I could reference and acknowledge the originator of this query, but it was in the days before I grew wise enough to retain references to the origin of code snippets in my TSQL comments.
TSQL to retrieve table column names and meta description for a SQL server table
CREATE PROCEDURE [SY_GetTableFields] (@TableName as varchar(255)) AS IF CAST(SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar),1,1) as int) >= 9 BEGIN -- This is a SQL 2005 machine SELECT [Table Name] = OBJECT_NAME(c.object_id), [Column Name] = c.name, [Description] = ex.value, c.object_id FROM sys.objects sobj JOIN sys.columns c ON c.object_id = sobj.object_id LEFT JOIN sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 AND ex.name = 'MS_Description' WHERE sobj.name= @TableName ORDER BY c.column_id END ELSE BEGIN SELECT [Table Name] = i_s.TABLE_NAME, [Column Name] = i_s.COLUMN_NAME, [Description] = s.value FROM INFORMATION_SCHEMA.COLUMNS i_s LEFT OUTER JOIN sysproperties s ON s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME) AND s.smallid = i_s.ORDINAL_POSITION AND s.name = 'MS_Description' WHERE OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0 AND i_s.TABLE_NAME = @TableName ORDER BY i_s.TABLE_NAME, i_s.ORDINAL_POSITION END