Execute the following Microsoft SQL Server T-SQL database scripts in Management Studio Query Editor to demonstrate the use of the DISTINCT keyword. DISTINCT is a very expensive operation.

-- Using DISTINCT in COUNT - QUICK SYNTAX

SELECT TotalRows=COUNT(*), ProductColors=COUNT(DISTINCT Color),

       ProductSizes=COUNT(DISTINCT Size)

FROM AdventureWorks2008.Production.Product

/* TotalRows      ProductColors     ProductSizes

      504         9                 18                */
------------

-- Applying SQL Server DISTINCT to eliminate duplicates in table or result set

SELECT DISTINCT Size, Color

INTO ProdSizeColorCombination

FROM AdventureWorks2008.Production.Product

-- (68 row(s) affected)

------------

-- SQL DISTINCT usage for colors - SQL Server distinct - Select distinct
SELECT DISTINCT ProductColors = COALESCE(Color, 'N/A')
FROM AdventureWorks2008.Production.Product
ORDER BY ProductColors
/*
ProductColors
Black
Blue
Grey
Multi
N/A
Red
Silver
Silver/Black
White
Yellow */
------------
 
-- SQL DISTINCT for order years - SQL Server select distinct on one column
SELECT DISTINCT [Year]=YEAR(OrderDate)
FROM AdventureWorks2008.Sales.SalesOrderHeader
ORDER BY [Year]
GO
/*
Year
2001
2002
2003
2004 */
------------

-- T-SQL COUNT DISTINCT usage - sql count distinct / unique rows
SELECT

      AllProducts=                        COUNT(*),

      ProductsWithSubcategories =         COUNT(ProductSubcategoryID),

      UniqueSubcategories =               COUNT(DISTINCT ProductSubcategoryID),

      ProductsWithColor =                 COUNT(ALL Color),

      UniqueColors =                      COUNT(DISTINCT Color)

FROM AdventureWorks2008.Production.Product

/*

AllProducts ProductsWithSub. UniqueSubcategories ProductsWithColor UniqueColors

504         295               37                  256                  9 */

------------

------------

-- T-SQL SELECT DISTINCT complex usage - create table with distinct ProductID-s

------------

SELECT RowID=ROW_NUMBER() OVER (ORDER BY ProductID),          -- SELECT FROM SELECT

       ProductID

INTO ProdInv                                                  -- Table create

FROM (SELECT DISTINCT ProductID

      FROM AdventureWorks2008.Production.ProductInventory ) x  -- Derived table

-- (425 row(s) affected)

SELECT TOP (7) * FROM ProdInv

/* RowID    ProductID

      1           1

      2           2

      3           3

      4           4

      5           316

      6           317

      7           318   */

------------