USE tempdb
GO


-- Clean up objects from any previous runs.
IF object_id(N'Person.Contact','U') IS NOT NULL
DROP TABLE Person.Contact
GO


IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Person')
DROP SCHEMA Person
GO


-- Create a sample schema and table.
CREATE SCHEMA Person
GO


CREATE TABLE Person.Contact(
FirstName nvarchar(60),
LastName nvarchar(60),
Phone nvarchar(15),
Title nvarchar(15)
)
GO


-- Populate the table with a few rows.
INSERT INTO Person.Contact
VALUES(N'James',N'Smith',N'425-555-1234',N'Mr')
INSERT INTO Person.Contact
VALUES(N'James',N'Andersen',N'425-555-1111',N'Mr')
INSERT INTO Person.Contact
VALUES(N'James',N'Andersen',N'425-555-3333',N'Mr')
INSERT INTO Person.Contact
VALUES(N'Christine',N'Williams',N'425-555-0000',N'Dr')
INSERT INTO Person.Contact
VALUES(N'Susan',N'Zhang',N'425-555-2222',N'Ms')
GO


-- Show that there are no statistics yet on the Person.Contact table.
sp_helpstats N'Person.Contact', 'ALL'
GO


-- Implicitly create statistics on LastName.
SELECT * FROM Person.Contact WHERE LastName = N'Andersen'
GO


-- Show that statistics were automatically created on LastName.
sp_helpstats N'Person.Contact', 'ALL'
GO


-- Create an index, which also creates statistics.
CREATE NONCLUSTERED INDEX Phone on Person.Contact(Phone)
GO


-- Show that creating the index created an associated statistics object.
sp_helpstats N'Person.Contact', 'ALL'
GO


-- Create a multi-column statistics object on first and last name.
CREATE STATISTICS FirstLast ON Person.Contact(FirstName,LastName)
GO


-- Show that there are now three statistics objects on the table.
sp_helpstats N'Person.Contact', 'ALL'
GO


-- Display the statistics for LastName.
DBCC SHOW_STATISTICS (N'Person.Contact', LastName)
GO


-- If you take the name of the statistics object displayed by
-- the command above and subsitute it in as the second argument of
-- DBCC SHOW_STATISTICS you can form a command like the following one
--(the exact name of the automatically created statistics object
-- will typically be different for you).
DBCC SHOW_STATISTICS (N'Person.Contact', _WA_Sys_00000002_6B022529)
-- Executing the above command illustrates that you can show statistics by
-- column name or statistics object name.
GO


-- The following displays multi-column statistics. Notice the two
-- different density groups for the second rowset in the output.
DBCC SHOW_STATISTICS (N'Person.Contact', FirstLast)


--´ë±Ô¸ð Å×ÀÌºí¿¡ ´ëÇØ ¿Ïº®ÇÏ°Ô Ã¤¿öÁø È÷½ºÅä±×·¥À» º¸±â
USE AdventureWorks
GO


IF EXISTS (SELECT * FROM sys.stats
          WHERE object_id = object_id( 'Sales.SalesOrderHeader')
          AND name = 'TotalDue')
   DROP STATISTICS Sales.SalesOrderHeader.TotalDue
GO


CREATE STATISTICS TotalDue ON Sales.SalesOrderHeader(TotalDue)
GO


DBCC SHOW_STATISTICS(N'Sales.SalesOrderHeader', TotalDue)


-- °è»êµÈ ¿­ ÀÚµ¿ Åë°è--------------------------------------------------
USE AdventureWorks
GO


-- Remove all statistics for Sales.SalesOrderHeader
DECLARE c CURSOR FOR
SELECT name FROM sys.stats
WHERE object_id = object_id( 'Sales.SalesOrderHeader')
AND auto_created <> 0 AND user_created <> 0


DECLARE @name NVARCHAR(255)


OPEN c
FETCH next FROM c INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC ( 'drop statistics Sales.SalesOrderHeader.'+ @name)
  FETCH NEXT FROM c INTO @name
END
CLOSE c
DEALLOCATE c


-- Query Sales.SalesOrderHeader with an expression equivalent
-- to the TotalDue computed column,
-- ((isnull(([SubTotal]+[TaxAmt])+[Freight],(0))).
SELECT *
FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID
AND (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) > 200000.00
ORDER BY TotalDue DESC


-- List the created statistics. Observe that statistics
-- are created for TotalDue even though it is not explicitly
-- referenced in the query.
sp_helpstats 'Sales.SalesOrderHeader'