With Ties Option in SQL Server

When we use with Ties Option, SQL Server Outputs all the Tied rows irrespective of limit we impose.

Let's understand with below example.

Test data:

CREATE TABLE #TEST
(
Id INT,
Name VARCHAR(10)
)
Insert Into #Test
select 1,'A'
Union All
Select 1,'B'
union all
Select 1,'C'
union all
Select 2,'D'

Output:

Id	Name
1	A
1	B
1	C
2	D

Let's Check with out With Ties Option

Select Top (1) Id, Name From
#TEST
Order By Id ;

Output: (Output of above query not guaranteed every time. )

Id	Name
1	B

Let's run same query With Ties Option

Select Top (1) With Ties Id, Name From
#test
Order By Id ;

Output: ( 1 is present 3 times in the table )

Id	Name
1	A
1	B
1	C

Here is some more example:

Select Top (1) With Ties Id, Name From
#test
Order By Id ;

Output: 

Id	Name
1	A