Show line numbers in SQL Server Management Studio Query Editor

← Prev

I am sure you have seen the query editor in SQL Server Management Studio. A Query Editor in SSMS is where you write queries (scripts), functions, procedures etc. and execute it. The Query Editor does not show line numbers, by default. I’ll show you how to show/display line numbers in SSMS query editor.

See this image first. I am creating a stored procedure using the query editor. By default, the query editor does not show line numbers.

Image

SQL Server Management Studio Query Editor

But I want to know how many lines of code I have written. Like this,

Image

SSMS Query Editor with line numbers

Well, why do I need line numbers at the first place?

The answer is simple. It makes debuging easy. Let us assume, you are working on a long script, be it a query or a stored procedure and while executing the query it throws an error message at Line 73 and 80. See the below.

Image

Error message in SQL Server

So, if you have the line numbers option activated, you can easily locate the error in the script. This is just one reason.

Great! So, how do I activate this option? Here are few simple steps you need to follow.

1) Open SQL Server Management Studio.

2) From the top menu, choose Tools and select Options….

Image

Options in SQL Server Management Studio

3) In the Options window, select Text Editor and click Transact-SQL.

Image

SSMS Text Editor Transact-SQL option

4) Finally, under Settings options check the Line numbers checkbox. Click the Ok button.

Note: If you are using an older version like SQL Server 2008, then the "Line numbers" checkbox will be under the Display options.

Image

SSMS Query Editor Line numbers option

The changes that you have made in the Options window, will reflect in all the Query windows. Now, if you open a new query window, it will show line number starting from 1.

If you do not want to see the line numbers, see the 4th step and uncheck the Line numbers checkbox.

That's it. Happy coding.


1) How to find and remove Duplicate rows in a Table using SQL Server ROW_NUMBER() and CTE: Duplicate rows in tables can be very annoying for DBA’s and programmers, as it raises many uncomfortable questions about the authenticity of data in a database. The matter gets worse when company auditors complain about irregularities in the balance sheet etc.

2) How to convert Rows into Columns using SQL Server PIVOT OR how to use PIVOT in SQL Server: Ever wondered how you can convert data from rows to columns in SQL Server. We are talking about an SQL query, which will transform records from multiple rows into columns. Using SQL Server PIVOT, we can efficiently rotate a table’s data to show a summarized result.

3) Insert Multiple rows with a Single INSERT Statement using SQL Server Table Value Constructor: While managing an Inventory management System for an organization, I have came across a situation where I had to perform bulk upload on a table in SQL Server. Bulk upload requires inserting multiple rows of data in a table.

4) How to Convert Month Number in a Date to Month Name in SQL Server: Let us assume I have a Sales table with sales data for each month. Every day sales is stored in the table with columns such as date, quantity, price etc. I want to get the total sales for every month. Now since I have a column with “date” data type, I want to convert the month number in the date to Month Name (like February, March etc.). Find out how this is done in SQL Server.

5) SQL Server CHARINDEX Function with Examples: The primary use of an SQL Server CHARINDEX function is to find the first or starting location of an expression or characters in a given string. To make it simple, it is like searching a specified character or characters in a string.

← Previous