/
PARTITION NOTES
119 lines (102 loc) · 3.67 KB
/
PARTITION NOTES
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
use master
if exists
(Select name from sys.Databases
Where name = 'Employeepartition')
Begin
Drop Database EmployeePartition
End
Go
Create Database EmployeePartition
Go
Alter Database EmployeePartition
Add Filegroup Employees1995Group
Go
Alter Database EmployeePartition
Add File
(
name='Employees1995',
filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Employees1995File.ndf',
maxsize=200mb,
FileGrowth=5mb
) to filegroup Employees1995Group
go
Alter Database EmployeePartition
Add Filegroup Employees2000Group
Go
Alter Database EmployeePartition
Add File
(
name='Employees2000',
filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Employees2000File.ndf',
maxsize=200mb,
FileGrowth=5mb
) to filegroup Employees2000Group
go
Alter Database EmployeePartition
Add Filegroup Employees2005Group
Go
Alter Database EmployeePartition
Add File
(
name='Employees2005',
filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Employees2005File.ndf',
maxsize=200mb,
FileGrowth=5mb
) to filegroup Employees2005Group
go
Alter Database EmployeePartition
Add Filegroup Employees2010Group
Go
Alter Database EmployeePartition
Add File
(
name='Employees2010',
filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Employees2010File.ndf',
maxsize=200mb,
FileGrowth=5mb
) to filegroup Employees2010Group
go
Alter Database EmployeePartition
Add Filegroup Employees2015Group
Go
Alter Database EmployeePartition
Add File
(
name='Employees2015',
filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Employees2015File.ndf',
maxsize=200mb,
FileGrowth=5mb
) to filegroup Employees2015Group
--create partition function and partition schema
use EmployeePartition
Create partition function fx_HireDate(Date) --emplyee table tells us the data type (date, not datetime)
As range left --outer leftmost date (range right would be the starting date) Range left is the termination date(?) The following would have worked better with RANGE RIGHT
for values('19941231','19991231','20041231','20091231') --YOu have to have one less date in the values than in the partitions for overflow. 5 groups, 4 partitions
--partition schema
Create partition Scheme sch_HireDate
As partition fx_HireDate
To(Employees1995Group,Employees2000Group,Employees2005Group,Employees2010Group,Employees2015Group)
--Copy (script to clipboard and paaste) Employee Table from MetroAlt. Erase all of the extra stuff and take the identity (1,1) OUT. Create the same ON THE PARTITION SCHEMA (sch_HireDate)
CREATE TABLE [dbo].[Employee](
[EmployeeKey] [int] NOT NULL,
[EmployeeLastName] [nvarchar](255) NOT NULL,
[EmployeeFirstName] [nvarchar](255) NULL,
[EmployeeAddress] [nvarchar](255) NOT NULL,
[EmployeeCity] [nvarchar](255) NOT NULL,
[EmployeeZipCode] [nchar](5) NOT NULL,
[EmployeePhone] [nchar](10) NULL,
[EmployeeEmail] [nvarchar](255) NOT NULL,
[EmployeeHireDate] [date] NOT NULL,
)on sch_HireDate(EmployeeHireDate)
--insert from MetroAlt Employee to our new partitioned Employee
go
insert into Employee (EmployeeKey, EmployeeLastName, EmployeeFirstName, EmployeeAddress, EmployeeCity, EmployeeZipCode, EmployeePhone, EmployeeEmail, EmployeeHireDate)
select EmployeeKey, EmployeeLastName, EmployeeFirstName, EmployeeAddress, EmployeeCity, EmployeeZipCode, EmployeePhone, EmployeeEmail, EmployeeHireDate from MetroAlt.dbo.Employee
--selecting the entire new table
select * from Employee
--selecting just the first partition (1995 to 1999)
Select * from Employee
Where $partition.Fx_Hiredate(EmployeeHireDate)=2
--selecting just the first partition (2000 to 2004)
Select * from Employee
Where $partition.Fx_Hiredate(EmployeeHireDate)=3