39

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?

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
LittleBobbyTables - Au Revoir
  • 32,008
  • 25
  • 109
  • 114

3 Answers3

76

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) .

Michael Goldshteyn
  • 71,784
  • 24
  • 131
  • 181
  • 7
    That's fantastic -- I'd give you 2 more upvotes for a "Nice Answer" if I could. I **would** like to point out that it does not work in Query Analyzer itself, but works just as described in a stored procedure. We must use this feature only for good, and not for screwing with DBAs... – LittleBobbyTables - Au Revoir Oct 29 '10 at 18:22
  • 12
    No let's screw with DBA's :-) Msg 208, Level 16, State 1, Line 2792874 – Stephen Turner Oct 29 '10 at 19:14
  • 3
    ... and is still undocumented. – Michael Goldshteyn Oct 02 '12 at 18:34
  • 2
    I raised a bug with MS, we shall see how far it gets. It will probably get rejected as 'By Design' https://connect.microsoft.com/SQLServer/feedback/details/1426523 – Martin Brown Jun 12 '15 at 15:01
  • 2
    `Thank you submitting this, but this is not considered an error. Some key words are reserved for future use or because they are used internally. LINENO is not supported for customer use.` – Martin Smith Jul 19 '15 at 18:29
  • Any way to RETRIEVE the lineno? – JJS May 19 '16 at 16:01
  • 1
    This is documented now https://msdn.microsoft.com/en-us/library/aa977931(v=vs.71).aspx – Jmaurier Jun 14 '17 at 17:09
  • @Jmaurier That’s for FoxPro, not T-SQL. And it has different behavior than T-SQL’s `LINENO`. – binki Jul 10 '17 at 18:13
  • This is another nasty bug of not indicating LineNo is a reserved word that can not be used in SQL statement. It wasted me hours to figure it out! – Yu Shen Feb 22 '18 at 06:37
  • 1
    WOW!... you "wasted me hours to figure it out"? The fact that it appears in the "Reserved Word" color wasn't substantial enough information to give you the clue that it would need to be enclosed in brackets to be used? – Jeff Moden Aug 29 '19 at 12:36
0

Select [LineNo] from Table

instead of Select LineNo from Table

Carlo Delos Santos
  • 1
  • 2
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 01 '23 at 23:25
-2

Depending on where you use it, you can always use [LineNo]. For example:

select LnNo [LineNo] from OrderLines.
juzraai
  • 5,693
  • 8
  • 33
  • 47
Joakim Lundvall
  • 1