enrique_aeo
asked on
sql server 2012 Understanding DISTINCT
Hi experts:
i am reading about Understanding DISTINCT, but i do not understand
Some queries may improve performance by filtering out duplicates prior to execution of SELECT clause
i am reading about Understanding DISTINCT, but i do not understand
Some queries may improve performance by filtering out duplicates prior to execution of SELECT clause
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Peter Chan
"Distinct" will ensure that no duplicated rows will appear in the resulted recordset, as every row is distinct one!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
can you give me an example with code t-sql
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
Using distinct means if you use Distinct against any select statement it means that it will return you unique record.
It means combination of all column which is a row will be unique (distinct)
Since using distinct removes duplicate records hence number of record reduces which leads toward performance increase.
We can also remove duplicate by Group by.
Using distinct means if you use Distinct against any select statement it means that it will return you unique record.
It means combination of all column which is a row will be unique (distinct)
Since using distinct removes duplicate records hence number of record reduces which leads toward performance increase.
We can also remove duplicate by Group by.
ASKER
use Northwind
go
set statistics io on
SELECT
p.ProductName,
c.ContactName
FROM
(
SELECT DISTINCT
o.CustomerId,
od.ProductId
FROM Orders o JOIN [Order Details] od ON o.OrderID = od.OrderID
) x
JOIN Customers c ON c.CustomerId = x.CustomerId
JOIN Products p ON p.ProductID = x.ProductID
order by p.ProductName, c.ContactName
-------
SELECT DISTINCT p.ProductName, c.ContactName
from Products p join [Order Details] od on p.ProductID = od.ProductID
join Orders o on od.OrderID = o.OrderID
join Customers c on o.CustomerID = c.CustomerID
set statistics io off
go
set statistics io on
SELECT
p.ProductName,
c.ContactName
FROM
(
SELECT DISTINCT
o.CustomerId,
od.ProductId
FROM Orders o JOIN [Order Details] od ON o.OrderID = od.OrderID
) x
JOIN Customers c ON c.CustomerId = x.CustomerId
JOIN Products p ON p.ProductID = x.ProductID
order by p.ProductName, c.ContactName
-------
SELECT DISTINCT p.ProductName, c.ContactName
from Products p join [Order Details] od on p.ProductID = od.ProductID
join Orders o on od.OrderID = o.OrderID
join Customers c on o.CustomerID = c.CustomerID
set statistics io off