syscomments table uses multiple rows. Why?

Question

I was writing a script that kept giving me errors. After tracking it down I found that the syscomments table stores its contents in multiple rows if the information gets to long past varchar(8000) i believe.

Why does the syscomments table break the data up into multiple rows instead of 1 single larger row? performance?

Answer 1

The code of a stored proc, view, etc is stored in the comments field. This column is type VarChar. The limit for VarChar is 8000.

So if you have a piece of code more than 8000 chars, it has to be broken down into multiple lines.

This is not changed to text or blob because we do searches on this column. You cannot do many string functions to search, substitute, etc on this TEXT data type so it was left as varchar.

Look here for a sample on searching the SysComments

http://codebank.wordpress.com/2007/03/06/search-stored-procedures-for-text-with-sp_grep/

Answer 2

syscomments.text is nvarchar(4000) documented here:sys.syscomments (Transact-SQL), which was the best you could do back in the day.

However, you can use the newer sys.sql_modules documented here: sys.sql_modules (Transact-SQL), which has a definition column which is nvarchar(max).

for searching use:

DECLARE @Search nvarchar(500)
SET @Search=N'your text here'
SELECT DISTINCT
    o.name AS Object_Name,o.type_desc --, m.definition
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY 2,1

if you are pre SQL Server 2005 you can search using the query here: http://stackoverflow.com/questions/1540396/sql-server-table-population-source/1540473#1540473

Answer 3

sys.syscomments, like all catalog metadata views, is a view not a table. You can look into the actual view definition:

sp_helptext 'sys.syscomments'

and you'll see that the actual text of the object definition is obtained using CROSS APPLY on an internal rowset (ie. a relational operator). So there are no multiple rows in the base tables, the definition is simply split for you on-the-fly into multiple rows so that one single large text modules can be presented as multiple nvarchar(4000) chuncks.

On the other hand if you check the definition of the other view sys.sql_modules :

sp_helptext 'sys.sql_modules'

you'll see that the module text is obtained through the scalar function OBJECT_DEFINITION.

As a general rule, there can never be any performance benefit from splitting a single field into multiple rows.