What exactly does the T-SQL "LineNo" reserved word do?

Question

I was writing a query against a table today on a SQL Server 2000 box, and while writing the query in Query Analyzer, to my surprise I noticed the word LineNo was converted to blue text.

It appears to be a reserved word according to MSDN documentation, but I can find no information on it, just speculation that it might be a legacy reserved word that doesn't do anything.

I have no problem escaping the field name, but I'm curious -- does anyone know what "LineNo" in T-SQL is actually used for?

Answer 1

OK, this is completely undocumented, and I had to figure it out via trial and error, but it sets the line number for error reporting. For example:

LINENO 25  SELECT * FROM NON_EXISTENT_TABLE 

The above will give you an error message, indicating an error at line 27 (instead of 3, if you convert the LINENO line to a single line comment (e.g., by prefixing it with two hyphens) ):

Msg 208, Level 16, State 1, Line 27 Invalid object name 'NON_EXISTENT_TABLE'. 

This is related to similar mechanisms in programming languages, such as the #line preprocessor directives in Visual C++ and Visual C# (which are documented, by the way).

How is this useful, you may ask? Well, one use of this it to help SQL code generators that generate code from some higher level (than SQL) language and/or perform macro expansion, tie generated code lines to user code lines.

P.S., It is not a good idea to rely on undocumented features, especially when dealing with a database.

Update: This explanation is still correct up to and including the current version of SQL Server, which at the time of this writing is SQL Server 2008 R2 Cumulative Update 5 (10.50.1753.0) .