/*
	============================================================================
	File:		0140 - Querying partitioned tables.sql

	Summary:	This script demonstrates the benefits of partitioning when
				querying the data

	Date:		September 2019

	SQL Server Version: 2014 / 2016 / 2017 / 2019
------------------------------------------------------------------------------
	Written by Uwe Ricken, db Berater GmbH

	This script is intended only as a supplement to demos and lectures
	given by Uwe Ricken.  
  
	THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
	ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
	TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
	PARTICULAR PURPOSE.
	============================================================================
*/
USE demo_db;
GO

SELECT * FROM sys.indexes
WHERE	OBJECT_ID = OBJECT_ID(N'dbo.CustomerOrders', N'U');
GO

CREATE UNIQUE CLUSTERED INDEX cuix_CustomerOrders_Id
ON dbo.CustomerOrders (Id);
GO

SELECT Id, OrderDate
FROM	dbo.CustomerOrders
WHERE	OrderDate >= '20120101'
		AND OrderDate < '20130101';
GO

SELECT Id, OrderDate
FROM	dbo.CustomerOrders
WHERE	OrderDate >= CAST('20121201' AS DATE)
		AND OrderDate < CAST('20130201' AS DATE)
OPTION	(QUERYTRACEON 9130);
GO

DBCC SHOW_STATISTICS(N'dbo.CustomerOrders', N'cuix_CustomerOrders_Id');
GO

DBCC SHOW_STATISTICS(N'dbo.CustomerOrders', N'_WA_Sys_00000005_35BCFE0A');