SQL Server : Displaying records with Sequential Numbering / Group wise Sequential Number / specific range of records in SQL Server using ROW_NUMBER() function.

One of my SQL buddy called yesterday and asked me, how to display the records with Serial Numbers in SQL Server.  I gave the solution over phone which I want to share with you guys too. :)

Assume that we have a table with the following records.

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 




How to display the records with record number :

Now, let me show you how to display the same records with Serial Number (Record Number) using ROW_NUMBER() function.

SELECT
ROW_NUMBER() OVER (ORDER BY [CityName] ASC ) AS 'Record Number',
[CityName],[StateName]
FROM
[DBO].[City] WITH (NOLOCK)
GO

You can see the records with serial number as below.

How to display group wise record number:

In a similar way, we can reset the record numbers based on group. Here, I am going to display the record numbers based on StateName wise.


SELECT
ROW_NUMBER() OVER ( PARTITION BY [StateName] ORDER BY [CityName]) AS 'Record Number', [CityName],[StateName]
FROM
[DBO].[City] WITH (NOLOCK)
ORDER BY
[StateName],[CityName]


Now,  see the group wise record number result below.

How to display specific range of records from the result set:

Sometimes, It may require to display the records for some specific range. Let us say, I want to display the records range between 5 to 10.


WITH CTECity (RecordNumber,CityName, StateName)
AS
(SELECT
ROW_NUMBER() OVER (ORDER BY [CityName] ASC ) AS RecordNumber,
[CityName],[StateName]
FROM
[DBO].[City] WITH (NOLOCK)
)
SELECT
[CityName],[StateName]
FROM
CTECity
WHERE
[RecordNumber] BETWEEN 5 AND 10
GO

Now, See the result below and you can compare the result with my first example.

 
 
 
 
 
 
 
Note :

(a). This example will work only in SQL Server 2005 and above versions.
(b). ROW_NUMBER() function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
(c). For my last example, I used Common Table Expression (CTE) which was introduced in SQL Server 2005 and it’s a "temporary result set" that exists only within the scope of a single SQL statement.

Convert SQL Server Table Data Into Comma Separated Values (CSV) Format

--=============================================
--Creating a table to store some sample records
--=============================================

CREATE TABLE [DBO].[MEmployee]
(
[EmpCode] INT IDENTITY (1,1) NOT NULL,
[EmpName] VARCHAR(100) NOT NULL,
[Active] BIT NOT NULL CONSTRAINT DF_MEmployee_Active DEFAULT 1, --1. ACTIVE 0. INACTIVE
CONSTRAINT [PK_MEmployee] PRIMARY KEY ([EmpCode]),
CONSTRAINT [CK_MEmployee_Active] CHECK ([Active] IN (0,1))
)
GO

--==================================================
--Inserting some test records into [MEmployee] Table
--==================================================

INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Siva',1)
GO
INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Parvathi',1)
GO
INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Surya',1)
GO
INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Satya',1)
GO
INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Ajay',0)
GO

--======================================
--Selecting the records from [MEmployee]
--======================================

SELECT * FROM [DBO].[MEmployee]
GO

The output would be :











--====================================================
--Generating the records in the Comma Separated Format
--====================================================

DECLARE @Result VARCHAR(MAX)
--==============================================
--To display the field heading in the first row
--==============================================
SET @Result = 'EmpCode,EmpName,Active'
SELECT
@Result = COALESCE(@Result + '|','') + CONVERT(VARCHAR(10),[EmpCode]) +','+[EmpName]+','+CONVERT(VARCHAR(2),[Active])
FROM
[DBO].[MEmployee]

PRINT @Result

See the result as shown below:





Note :
1. COALESCE() returns the first NOTNULL expression among its arguments.
2. "|" is used for row delimiter.

How to Call External Applications like MS Excel, MS Word etc., from SQL Server Management Studio (SSMS)

Many of us are thinking that SQL Server Management Studio (SSMS) is used only for database administration, but also can be used to call the external applications like MS Excel, MS Word, Calculator etc.,

For this example, let me explain, how to call MS Excel from SSMS.

Step 1 : Open SQL Server Management Studio(SSMS)

Step 2 : Go to Tools --> External Tools Menu (As shown below)













Step 3 :

(i) In the Title text box, type the name you want to appear in the Menu contents List. eg. "Excel"

(ii) In the Command text box, type the program name. Include the path to the executable file.
For example "C:\Program Files\Microsoft Office\Office12\EXCEL.EXE"

(iii) In the Arguments text box, type the program arguments if necessary.

(iv) In the Initial directory text box, type the program's initial directory if necessary.

(v) Click OK





















Go to Tools Menu, Now you can see "Excel" menu is added (As shown below).















Click and see the magic, the similar way you can add other your frequently used applications in SSMS. I hope this will help you to save your valuable time.