Quite often when debugging in SQL Server the error message will contain the line number where the error is being thrown. By default, most users do not have the line numbers turned on within SQL Management Studio which can be frustrating when trying to pin point the exact spot within the code where the error is occurring, especially when dealing with lengthy, complex queries. In fact, many new users may not even be aware that this feature is available and easy to activate.
To turn on line numbers within the query window in SQL Management Studio click on the Tools menu and select Options.
On the Options screen, expand Text Editor and then locate and expand Transact-SQL. Click on the General option under Transact-SQL to display the available options. On the right hand side of the screen you will see a check box labeled Line Numbers. Check this box and click OK.
Once the line numbers option has been enabled open a new query window and you should now see the individual line numbers for each line of code in the query window.
An example of this can be seen below:
When attempting to locate the a point of failure within your code when the line number is provided within the error message you can easily navigate to a specific line number by opening the Go To Line dialog box. To do this press CTRL + G.
This feature is more useful when you do not have the line numbers turned on but in the case of a very lengthy procedure or query using this can still save you some time if you don’t want to scroll through numerous lines of code.
Personally, I leave line numbers on for everything I am doing within SQL Management Studio. I find it to be very useful, not just for debugging, but also for keeping track of how my code is structured.