Row_Number in SQL Server

Row_Number (window function) was introduced in SQL Server 2005.

Row_Number generates a run-time column in the result set which generates sequential number to each row according to the column used in order by clause.

Syntax

Row_Number() over (order by col1, col2, ....n partition by col1, col2, ....n)

• Order By is mandatory. Row Number is assigned in the result set based on the column provided in Order By clause.
• Partition By is optional which groups the result set based on the column provided in Partition By clause wherein each group, the sequence starts with 1.

Let's create an Employee table on which we would see how it works.

create table tblEmployee(
    EmpId char(7)
    , FirstName varchar(50)
    , LastName varchar(50)
    , Gender char(1)
)

insert into tblEmployee
select 'EMP0001', 'Sandeep', 'Mittal', 'M' union all
select 'EMP0003', 'Abhay', 'Kumar', 'M' union all
select 'EMP0005', 'Priya', 'Gupta', 'F' union all
select 'EMP0002', 'Reema', 'Gupta', 'F' union all
select 'EMP0004', 'Ritesh', 'Kumar', 'M'

Now, we would generate a sequential number for all the employees using Row_Number.

select  EmpId, FirstName, LastName, Gender
        , row_number() over (order by EmpId) as RowNum
from    tblEmployee

OUTPUT

Check out the result set where RowNum is generated based on EmpId as provided in Order By.

Multiple columns can be used in the Order By clause.

Let's take an another example where we would use multiple columns in Order By clause.

select  EmpId, FirstName, LastName, Gender
        , row_number() over (order by FirstName, LastName) as RowNum
from    tblEmployee

OUTPUT

In the next example, we will see how Partition By clause works

select  EmpId, FirstName, LastName, Gender
        , row_number() over (partition by Gender order by EmpId) as RowNum
from    tblEmployee

OUTPUT

Check out the result set where RowNum is generated starting from 1 for each group i.e. Gender which is provided in Partition By clause

In Partition By clause also, we can use multiple columns as in Order By clause

Create EndPoint in SQL Server

In previous article, we learnt What is endpoint in SQL Server

In this article, we would learn how to create an Endpoint in SQL Server

Before creating an endpoint, we need to reserve an HTTP Namespace to expose the endpoint.

EXEC sp_reserve_http_namespace N'http://localhost:80/sql'

While creating endpoint you might face the below error

See how to resolve this error:
Msg 6004, Level 16, State 12, Line 1 User does not have permission to perform this action.

Reserved HTTP Namespace can be deleting using below command

sp_reserve_http_namespace N'https://localhost:80/sql'

Let's First create a function which we would expose using the endpoint

Create Function fn_EndPoint()
Returns Varchar(100)
AS
Begin
    Return 'My End Point'
End

Now lets create an endpoint that would expose the function "fn_EndPoint" that we created above.

Create Endpoint MyEndpoint
STATE = STARTED
AS HTTP
(                                 
    path='/sql/',
    AUTHENTICATION=(INTEGRATED),
    PORTS=(CLEAR),
    SITE ='localhost'
)
FOR SOAP
(             
    WEBMETHOD 'http://localhost/'.'GetEndPoint'         
   (     
        name='DatabaseName.dbo.fn_EndPoint',
        SCHEMA = STANDARD
    ),   
    WSDL = DEFAULT,
    BATCHES=DISABLED,
    DATABASE='DatabaseName'
)

In the next article, we will see how to start or stop endpoint in SQL Server

End Points in Sql Server

A SQL Server endpoint is the point of entry into SQL Server, or a way to connect to SQL Server instance.

Endpoints in SQL Server are similar to Web Services that expose database access over HTTP.
There are 5 system endpoints that are automatically created and that can’t be dropped (you can only start or stop them), one for each protocol

• TCP/IP
• Shared Memory
• Named Pipe
• VIA

One endpoint for the dedicated administrator connection (DAC).

Query to list all end points in SQL Server

select * from sys.endpoints

OUTPUT

Endpoints which have ID less then 65536 are system endpoints.

In the next article we will see how to Create Endpoint in SQL Server

System End Points can't be dropped, however they can be started or stopped.

To see how to start or stop an endpoint in SQL Server, read related blog post:
How to start or stop an endpoint in SQL Server

How to start or stop an endpoint in SQL Server

In earlier posts, we learn

• What is End Point in SQL Server? 
• How to create End Point in SQL Server

In this post, we will see how to start or stop a SQL End Point

Let's first query sys.endpoints catalog view and see all the end points exists

select * from sys.endpoints

OUTPUT

To stop an endpoint:

ALTER ENDPOINT endpoint_name STATE = STOPPED

To start an endpoint:

ALTER ENDPOINT endpoint_name STATE = STARTED

SQL Server : Msg 6004, Level 16, State 12, Line 1 User does not have permission to perform this action.

You might get the following error while creating an End point in SQL Server.

This error is generated when a user is trying to create an endpoint but do not has the access.

Resolution: Contact Server Administrator and ask him/her to grant the Create Endpoint permission

Query to grant Create Endpoint permission

Grant Create Endpoint to username