USE [master]
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'Sales_Retail')
BEGIN
CREATE DATABASE [Sales_Retail]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Sales_Retail', FILENAME = N'D:\DataSQL2014\Sales_Retail.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ),
FILEGROUP [Sales2007]
( NAME = N'Sales2007', FILENAME = N'D:\DataSQL2014\Sales2007.ndf' , SIZE = 10240KB , MAXSIZE = 1048576KB , FILEGROWTH = 1024KB ),
FILEGROUP [Sales2008]
( NAME = N'Sales2008', FILENAME = N'D:\DataSQL2014\Sales2008.ndf' , SIZE = 10240KB , MAXSIZE = 1048576KB , FILEGROWTH = 1024KB ),
FILEGROUP [Sales2009]
( NAME = N'Sales2009', FILENAME = N'D:\DataSQL2014\Sales2009.ndf' , SIZE = 10240KB , MAXSIZE = 1048576KB , FILEGROWTH = 1024KB ),
FILEGROUP [Sales2010]
( NAME = N'Sales2010', FILENAME = N'D:\DataSQL2014\Sales2010.ndf' , SIZE = 10240KB , MAXSIZE = 1048576KB , FILEGROWTH = 1024KB ),
FILEGROUP [Sales2011]
( NAME = N'Sales2011', FILENAME = N'D:\DataSQL2014\Sales2011.ndf' , SIZE = 10240KB , MAXSIZE = 1048576KB , FILEGROWTH = 1024KB ),
FILEGROUP [Sales2012]
( NAME = N'Sales2012', FILENAME = N'D:\DataSQL2014\Sales2012.ndf' , SIZE = 10240KB , MAXSIZE = 1048576KB , FILEGROWTH = 1024KB ),
FILEGROUP [Sales2013]
( NAME = N'Sales2013', FILENAME = N'D:\DataSQL2014\Sales2013.ndf' , SIZE = 73728KB , MAXSIZE = 1048576KB , FILEGROWTH = 1024KB ),
FILEGROUP [Sales2014]
( NAME = N'Sales2014', FILENAME = N'D:\DataSQL2014\Sales2014.ndf' , SIZE = 235520KB , MAXSIZE = 1048576KB , FILEGROWTH = 1024KB ),
FILEGROUP [Sales2015]
( NAME = N'Sales2015', FILENAME = N'D:\DataSQL2014\Sales2015.ndf' , SIZE = 1048576KB , MAXSIZE = 1048576KB , FILEGROWTH = 1024KB ),
( NAME = N'Sales2015_1', FILENAME = N'D:\DataSQL2014\Sales2015_1.ndf' , SIZE = 168960KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Sales_Retail_log', FILENAME = N'D:\DataSQL2014\Sales_Retail_log.mdf' , SIZE = 634880KB , MAXSIZE = 2048GB , FILEGROWTH = 10240KB )
END
GO
GO
ALTER DATABASE [Sales_Retail] SET RECOVERY SIMPLE
GO
ALTER DATABASE [Sales_Retail] SET MULTI_USER
GO
ALTER DATABASE [Sales_Retail] SET PAGE_VERIFY CHECKSUM
GO
USE Sales_Retail
GO
CREATE PARTITION FUNCTION SalesRetailRange(Datetime)
AS RANGE LEFT FOR VALUES('2007-01-01'
,'2008-01-01'
,'2009-01-01'
,'2010-01-01'
,'2011-01-01'
,'2012-01-01'
,'2013-01-01'
,'2014-01-01')
GO
--- Create a partition scheme
CREATE PARTITION SCHEME SalesRetailScheme AS PARTITION SalesRetailRange TO ([Sales2007],[Sales2008]
,Sales2009,Sales2010,Sales2011,Sales2012,Sales2013,Sales2014,Sales2015)
GO
IF OBJECT_ID('Sales') IS NOT NULL
DROP TABLE Sales
GO
CREATE TABLE Sales
(
SalesOrderID int NOT NULL IDENTITY (1, 1) NOT FOR REPLICATION,
OrderDate datetime NOT NULL, -- Partion column
DueDate datetime NULL,
ShipDate datetime NULL,
Status tinyint NULL,
SalesOrderNumber nvarchar(50),
CustomerID nvarchar(50),
SalesPersonID nvarchar(50),
TerritoryID nvarchar(50),
BillToAddressID nvarchar(50),
ShipToAddressID nvarchar(50),
ShipMethodID nvarchar(50),
CreditCardID nvarchar(50),
CreditCardApprovalCode nvarchar(15) NULL,
CurrencyRateID nvarchar(50),
SubTotal nvarchar(50),
TaxAmt nvarchar(50),
Freight nvarchar(50),
TotalDue nvarchar(50),
Comment nvarchar(128) NULL,
CarrierTrackingNumber nvarchar(25) NULL,
OrderQty nvarchar(50) NULL,
UnitPrice nvarchar(50) NULL,
UnitPriceDiscount nvarchar(50) NULL,
LineTotal nvarchar(50),
SnapshotDate Datetime
) ON SalesRetailScheme([OrderDate])
GO
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)