Posted: September 14th, 2017

CSC554 WK#6 SQL Server Partition

CSC554 WK#6 SQL Server Partition

submit both SQL statements and query results of each question.

1. Use AdventureWorks2012, create a Range Right partition function along with four boundaries values – 01/01/2005, 01/01/2006, 01/01/2007, 01/01/2008. Put your initials in front of the Partition Function Name, for example, MC_Date(datetime).

2. Add appropriate number of filegroups and files. Each file has initial 1MB , maximum 5MB, filegrowth 1MB.
Note1: Put your initials in front of each FileGroup Name, for example, MCfg1. For example,
ALTER DATABASE AdventureWorks2012 ADD FILEGROUP MCfg1
Note2: Also put your initials in front of the file name and dataset name. For example,
ALTER DATABASE AdventureWorks2012
ADD FILE ( NAME = MCdata1, FILENAME = ‘C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAMCAWd1.ndf’,
SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB) TO FILEGROUP MCfg1

3. Creating a partition scheme to map each partition to a filegroup. Put your initials in front of the Partition Schema Name, for example, MC_PartScheme.

4. Creating a partitioned table called YourInitials_Sales.ReturnsArchive with the following columns.
ReturnID int IDENTITY NOT NULL,
ProductID int NOT NULL,
CustomerID int NOT NULL,
ReturnDate datetime NOT NULL,
ReturnReason char(20) NULL

5. Run the following script to inserting data into the partitioned table
— Insert test data into partitioned table
USE AdventureWorks2012
INSERT INTO YourInitials_Sales.ReturnsArchive(ProductID, CustomerID, ReturnDate, ReturnReason)
SELECT sod.ProductID, soh.CustomerID, soh.OrderDate, ‘Old goods’
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.OrderDate >= ’01/01/2003′
UNION ALL
SELECT sod.ProductID, soh.CustomerID, DATEADD(year, 4, soh.OrderDate), ‘New goods’
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.OrderDate < ’01/01/2003′
(121317 row(s) affected)

6. Viewing partition usage
a. How many rows in each partition?

b. Browse all data in partition 2.

c. Let’s merge partition 2 and 3 due to low volume of data.

d. How many rows in each partition after the merge?

e. Browse all data in partition 2.

f. Browse all data in partition 4.

g. Insert two rows into Partition 4.
Insert into Sales.ReturnsArchive Values(990,99999,getdate(),’not good fit’)
Insert into Sales.ReturnsArchive Values(991,99991,getdate(),’not good fit’)

h. Browse all data in partition 4.

i. Let’s split partition – put all 2010 rows in a new partition.

j. Continue after the above step: You need to add another partition (#5) to accommodate rows with 2010 date.

k. Let’s split partition – put all 2010 rows in a new partition.

l. How many rows in each partition after the split?

m. Browse all data in the new partition.

n. Please display the range value of each partition.

o. Remove everything you created
ORDER THIS ESSAY HERE NOW AND GET A DISCOUNT !!!