The syscomments table uses multiple rows. What for?

Question

I wrote a script that kept giving me errors. After tracking, I found that the syscomments table stores its contents in several rows, if the information falls into the varchar (8000) past, I think.

Why does the syscomments table split data into multiple rows instead of one single row? performance?

Answer 1

syscomments.text registered here nvarchar (4000): sys.syscomments (Transact-SQL) , which was the best you could do in the old days.

However, you can use the new sys.sql_modules described here: sys.sql_modules (Transact-SQL) , which has a definition that is nvarchar(max) .

for searching:

 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: SQL Server table data source

Answer 2

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

So, if you have a code snippet of more than 8000 characters, you need to break it into several lines.

This is not changed to text or blob because we are searching in this column. You cannot perform many string functions for searching, replacing, etc. This data type is TEXT, so it remains as varchar.

Check here for an example of SysComments search.

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

Answer 3

sys.syscomments , like all directory metadata, is a view, not a table. You can see the definition of the actual view:

 sp_helptext 'sys.syscomments' 

and you will see that the actual text of the object definition is obtained using CROSS APPLY in the internal rowset (i.e. the relational operator). Thus, there are no several rows in the base tables, the definition simply breaks down into several lines on the fly, so that one large text module can be represented as several nvarchar (4000) chuncks.

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

 sp_helptext 'sys.sql_modules' 

You will see that the text of the module is obtained through the scalar function OBJECT_DEFINITION .

As a rule, there can never be any performance benefit from breaking a single field into multiple lines.