difference between Top And Top with ties in Sql server

In this article, I will explain the use of the TOP WITH TIES Clause in SQL Server. In SQL Server, TOP WITH TIES,additional rows will be included if their values match, or tie, the values of the last row. So let's take a look at a practical example of how to use the TOP WITH TIES Clause in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

The WITH TIES can be used only with the following clause:

• Select Statemnet
• Order by clause is necessary for using this clause
• PERCENT clause

Creating a table in SQL Server

Now we create a table named employee using:

Create table Employee

(

EmpID int,

EmpName varchar(30),

EmpSalary int

)

The following is the sample data for the employee Table:

Top Clause

The SELECT TOP N query always returns exactly N records. The following example defines the TOP clause.

Example

SELECT [EmpID]

      ,[EmpName]

      ,[EmpSalary]

  FROM [master].[dbo].[Employee]

  Go

 Select   top(3)  [EmpName]  from [Employee]

order by [EmpName]

Output

Problem

In the above example, we can encounter the situation in which use of the top clause returns exactly N records and drops any record(s) arbitrarily that has the same value as the last record in the result set. Suppose the employee table has EmpName Rahul and the table contains one more EmpName with the same name, but it will not be in the result since they are ignored by the TOP clause.

Select Query Using TOP WITH TIES Clause

To see those recrods add the WITH TIES clause:

SELECT [EmpID]

      ,[EmpName]

      ,[EmpSalary]

  FROM [master].[dbo].[Employee]

  Go

 Select  top(3) [EmpName] from [Employee]

order by [EmpName]

 Go

 Select   top(3) with ties [EmpName]  from [Employee]

order by [EmpName]

Output

Recently, on this blog, I published an article on SQL SERVER – Interesting Observation – TOP 100 PERCENT and ORDER BY; this article was very well received because of the observation made in it. One of the comments suggested the workaround was to use clause WITH TIES along with TOP and ORDER BY. That is not the correct solution; however, but the same comment brings up the question regarding how WITH TIES clause actually works.

First of all, the clause WITH TIES can be used only with TOP and ORDER BY, both the clauses are required. Let us understand from one simple example how this clause actually works. Suppose we have 100 rows in the table and out of that 50 rows have same value in column which is used in ORDER BY; when you use TOP 10 rows, it will return you only 10 rows, but if you use TOP 10 WITH TIES, it will return you all the rows that have same value as that of the last record of top 10 — which means a total of 50 records.

refer below link for more understanding:-

http://blog.sqlauthority.com/2009/12/23/sql-server-order-by-clause-and-top-with-ties/