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

Information about SQL Server version

 Try using below sql script to find the detail information about SQL server Version.

SELECT SERVERPROPERTY('MachineName') AS Host,
    SERVERPROPERTY('InstanceName') AS Instance,
    DB_NAME() AS DatabaseContext,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    CASE SERVERPROPERTY('IsClustered')
        WHEN 1 THEN 'CLUSTERED'
    ELSE 'STANDALONE' END AS ServerType,
@@VERSION AS VersionNumber;

Output: