Skip to content

Commit eb23856

Browse files
sqlballssqlballs
authored
Create Create demoInternals_Partition_linux.sql
1 parent 45e8896 commit eb23856

File tree

1 file changed

+245
-0
lines changed

1 file changed

+245
-0
lines changed

‎Transaction Log Scripts/Create demoInternals_Partition_linux.sql‎

Lines changed: 245 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,245 @@
1+
/*============================================================
2+
// Source via Bradley Ball :: braball@micrsoft.com
3+
// MIT License
4+
// Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the ""Software""), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
5+
// The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
6+
// THE SOFTWARE IS PROVIDED *AS IS*, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY
7+
8+
==============================================================*/
9+
SET NOCOUNT ON;
10+
/*
11+
Create our Database
12+
That we will use for the
13+
Demo
14+
*/
15+
USE master;
16+
Go
17+
IF EXISTS(SELECT name FROM sys.databases WHERE Name=N'demoInternals_Partition')
18+
BEGIN
19+
alter database demoInternals_Partition set single_user with rollback immediate
20+
DROP Database demoInternals_Partition
21+
END
22+
23+
CREATE DATABASE demoInternals_Partition
24+
GO
25+
ALTER DATABASE demoInternals_Partition MODIFY FILE ( NAME = N'demoInternals_Partition_log', FILEGROWTH = 512KB )
26+
GO
27+
/*
28+
Add Filegroups
29+
*/
30+
ALTER DATABASE demoInternals_Partition
31+
ADD FILEGROUP FG1
32+
GO
33+
ALTER DATABASE demoInternals_Partition
34+
ADD FILEGROUP FG2
35+
GO
36+
ALTER DATABASE demoInternals_Partition
37+
ADD FILEGROUP FG3
38+
GO
39+
ALTER DATABASE demoInternals_Partition
40+
ADD FILEGROUP FG4
41+
GO
42+
/*
43+
Add Files and
44+
associate to filegroups
45+
*/
46+
ALTER DATABASE demoInternals_Partition
47+
ADD FILE
48+
(
49+
NAME=data_FG1,
50+
FILENAME='/var/opt/mssql/data/FG1.ndf'
51+
) TO FILEGROUP FG1;
52+
GO
53+
ALTER DATABASE demoInternals_Partition
54+
ADD FILE
55+
(
56+
NAME=data_FG2,
57+
FILENAME='/var/opt/mssql/data/FG2.ndf'
58+
) TO FILEGROUP FG2;
59+
GO
60+
ALTER DATABASE demoInternals_Partition
61+
ADD FILE
62+
(
63+
NAME=data_FG3,
64+
FILENAME='/var/opt/mssql/data/FG3.ndf'
65+
) TO FILEGROUP FG3;
66+
GO
67+
ALTER DATABASE demoInternals_Partition
68+
ADD FILE
69+
(
70+
NAME=data_FG4,
71+
FILENAME='/var/opt/mssql/data/FG4.ndf'
72+
) TO FILEGROUP FG4;
73+
GO
74+
75+
USE demoInternals_Partition
76+
GO
77+
/*
78+
Create Partition Function
79+
*/
80+
CREATE PARTITION FUNCTION compDemoPartFunc(INT)
81+
AS RANGE LEFT FOR VALUES(2000, 4000, 6000)
82+
GO
83+
/*
84+
Create Partition Scheme
85+
*/
86+
CREATE PARTITION SCHEME compDemoPS
87+
AS PARTITION compDemoPartFunc
88+
TO(fg1, fg2,fg3, fg4);
89+
90+
91+
/*
92+
Let's create a Clustered Index
93+
*/
94+
IF EXISTS(SELECT NAME FROM sys.tables WHERE name=N'myTable1')
95+
BEGIN
96+
DROP TABLE dbo.myTable1
97+
END
98+
99+
CREATE TABLE myTable1(
100+
myID INT IDENTITY(1,1),
101+
productName char(800) DEFAULT 'some product',
102+
productSKU varCHAR(500) DEFAULT 'Product SKU',
103+
productDescription varCHAR(max) DEFAULT 'Here is a Generic Production Description',
104+
Comments TEXT DEFAULT 'here are some genric comments',
105+
CONSTRAINT PK_myTable1_myID
106+
PRIMARY KEY CLUSTERED(myID)
107+
) ON compDemoPS(myID);
108+
109+
/*
110+
Let's populate our Clustered
111+
Index with some data
112+
*/
113+
DECLARE @i INT
114+
SET @i=0
115+
116+
BEGIN TRAN
117+
WHILE (@i<10000)
118+
BEGIN
119+
INSERT INTO myTable1 DEFAULT VALUES;
120+
SET @i = @i +1
121+
122+
END
123+
COMMIT TRAN
124+
125+
126+
/*
127+
Add another file group
128+
*/
129+
ALTER DATABASE demoInternals_Partition
130+
ADD FILEGROUP FG5
131+
GO
132+
/*
133+
Associate with a Physical File
134+
*/
135+
ALTER DATABASE demoInternals_Partition
136+
ADD FILE
137+
(
138+
NAME=data_FG5,
139+
FILENAME='/var/opt/mssql/data/FG5.ndf'
140+
) TO FILEGROUP FG5;
141+
GO
142+
143+
/*
144+
Alter Partition Scheme
145+
*/
146+
Alter PARTITION SCHEME compDemoPS
147+
NEXT USED FG5;
148+
149+
/*
150+
Alter Partition Function
151+
*/
152+
ALTER PARTITION FUNCTION compDemoPartFunc()
153+
SPLIT RANGE(10000)
154+
GO
155+
156+
/*
157+
Let's Add another 4000
158+
rows to watch the
159+
new Partition get
160+
Populated
161+
*/
162+
DECLARE @i INT
163+
SET @i=0
164+
165+
BEGIN TRAN
166+
WHILE (@i<4000)
167+
BEGIN
168+
INSERT INTO myTable1 DEFAULT VALUES;
169+
SET @i = @i +1
170+
171+
END
172+
COMMIT TRAN
173+
174+
175+
/*
176+
Add another file group
177+
*/
178+
ALTER DATABASE demoInternals_Partition
179+
ADD FILEGROUP FG6
180+
GO
181+
/*
182+
Associate with a Physical File
183+
*/
184+
ALTER DATABASE demoInternals_Partition
185+
ADD FILE
186+
(
187+
NAME=data_FG6,
188+
FILENAME='/var/opt/mssql/data/Internalsdata_FG6.ndf'
189+
) TO FILEGROUP FG6;
190+
GO
191+
192+
/*
193+
Alter Partition Scheme
194+
*/
195+
Alter PARTITION SCHEME compDemoPS
196+
NEXT USED FG6;
197+
198+
/*
199+
Alter Partition Function
200+
*/
201+
ALTER PARTITION FUNCTION compDemoPartFunc()
202+
SPLIT RANGE(14000)
203+
GO
204+
205+
/*
206+
Let's Add another 4000
207+
rows to watch the
208+
new Partition get
209+
Populated
210+
*/
211+
DECLARE @i INT
212+
SET @i=0
213+
214+
BEGIN TRAN
215+
216+
WHILE (@i<4000)
217+
BEGIN
218+
INSERT INTO myTable1 DEFAULT VALUES;
219+
SET @i = @i +1
220+
221+
END
222+
COMMIT TRAN
223+
224+
/*
225+
Let's Throw A
226+
Nonclusterd Index in
227+
and see the compression
228+
*/
229+
CREATE INDEX nci_demoInternals_Partition_myTable1 ON dbo.myTable1(productName);
230+
go
231+
232+
/*
233+
let's look at our data
234+
*/
235+
SELECT
236+
OBJECT_NAME(sp.object_id) AS tableName,
237+
si.name AS indexName,
238+
sp.partition_number,
239+
sp.rows,
240+
sp.data_compression_desc
241+
FROM
242+
sys.partitions sp
243+
JOIN sys.indexes si
244+
ON si.object_id=sp.object_id AND si.index_id =sp.index_id
245+
WHERE OBJECT_NAME(sp.object_id) ='myTable1'

0 commit comments

Comments
 (0)