· 8 years ago · Mar 13, 2018, 04:58 PM
1IF (SCHEMA_ID('Auth') IS NULL)
2BEGIN
3 EXEC ('CREATE SCHEMA [Auth] AUTHORIZATION [dbo]')
4END;
5GO
6
7ALTER DATABASE [IsolationExample]
8ADD FILEGROUP [Auth]
9GO
10
11ALTER DATABASE [IsolationExample]
12ADD FILE (
13 NAME = N'IsolationExample_Auth', FILENAME = N'E:MSSQLDataIsolationExample_Auth.ndf', SIZE = 512MB , MAXSIZE = UNLIMITED, FILEGROWTH = 512MB
14 )
15TO FILEGROUP [Auth]
16GO
17
18IF EXISTS (SELECT 1 FROM sys.partition_schemes WHERE [name] = 'PS_Tenant_Isolation') DROP PARTITION SCHEME [PS_Tenant_Isolation];
19IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE [name] = 'PF_Tenant_Isolation') DROP PARTITION FUNCTION [PF_Tenant_Isolation];
20
21IF NOT EXISTS (SELECT 1 FROM sys.partition_functions WHERE [name] = 'PF_Tenant_Isolation')
22BEGIN
23CREATE PARTITION FUNCTION [PF_Tenant_Isolation] ([int])
24 -- RANGE RIGHT values will be SPLIT based on external logic to add the correct values (1,2,3 ...) that corresponds with [Tenant].[TenantId].
25 AS RANGE RIGHT FOR VALUES (0);
26END;
27GO
28
29IF NOT EXISTS (SELECT 1 FROM sys.partition_schemes WHERE [name] = 'PS_Tenant_Isolation')
30BEGIN
31CREATE PARTITION SCHEME [PS_Tenant_Isolation]
32 AS PARTITION [PF_Tenant_Isolation]
33 ALL TO ([Auth]);
34END;
35GO
36
37DROP TABLE IF EXISTS [Auth].[Tenant];
38IF OBJECT_ID('[Auth].[Tenant]', 'U') IS NULL
39BEGIN
40 CREATE TABLE [Auth].[Tenant] (
41 [TenantId] [int] IDENTITY(1,1) NOT NULL
42 ,[TenantAlias] [varchar](3) NOT NULL
43 ,[TenantName] [varchar](256) NOT NULL
44 ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC)
45 ,CONSTRAINT [UQ_Tenant_TenantAlias] UNIQUE NONCLUSTERED ([TenantAlias] ASC)
46 ,CONSTRAINT [UQ_Tenant_TenantName] UNIQUE NONCLUSTERED ([TenantName] ASC)
47 ) ON [PS_Tenant_Isolation]([TenantId]);
48END;
49GO
50
51DROP TABLE IF EXISTS [Auth].[Tenant];
52IF OBJECT_ID('[Auth].[Tenant]', 'U') IS NULL
53BEGIN
54 CREATE TABLE [Auth].[Tenant] (
55 [TenantId] [int] IDENTITY(1,1) NOT NULL
56 ,[TenantAlias] [varchar](3) NOT NULL
57 ,[TenantName] [varchar](256) NOT NULL
58 ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC)
59 ) ON [PS_Tenant_Isolation]([TenantId]);
60 CREATE UNIQUE NONCLUSTERED INDEX [UQ_Tenant_TenantAlias] ON [Auth].[Tenant]([TenantAlias] ASC) ON [PS_Tenant_Isolation]([TenantId]);
61 CREATE UNIQUE NONCLUSTERED INDEX [UQ_Tenant_TenantName] ON [Auth].[Tenant]([TenantName] ASC) ON [PS_Tenant_Isolation]([TenantId]);
62END;
63GO
64
65DROP TABLE IF EXISTS [Auth].[Tenant];
66IF OBJECT_ID('[Auth].[Tenant]', 'U') IS NULL
67BEGIN
68 CREATE TABLE [Auth].[Tenant] (
69 [TenantId] [int] IDENTITY(1,1) NOT NULL
70 ,[TenantAlias] [varchar](3) NOT NULL
71 ,[TenantName] [varchar](256) NOT NULL
72 ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC)
73 ) ON [PS_Tenant_Isolation]([TenantId]);
74 CREATE UNIQUE NONCLUSTERED INDEX [UQ_Tenant_TenantAlias] ON [Auth].[Tenant]([TenantAlias] ASC) ON [Auth];
75 CREATE UNIQUE NONCLUSTERED INDEX [UQ_Tenant_TenantName] ON [Auth].[Tenant]([TenantName] ASC) ON [Auth];
76END;
77GO
78
79DROP TABLE IF EXISTS [Auth].[Tenant];
80IF OBJECT_ID('[Auth].[Tenant]', 'U') IS NULL
81BEGIN
82 CREATE TABLE [Auth].[Tenant] (
83 [TenantId] [int] IDENTITY(1,1) NOT NULL
84 ,[TenantAlias] [varchar](3) NOT NULL
85 ,[TenantName] [varchar](256) NOT NULL
86 ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC, [TenantAlias] ASC, [TenantName] ASC)
87 ) ON [PS_Tenant_Isolation]([TenantId]);
88END;
89GO
90
91CREATE TABLE [Auth].[Tenant] (
92 [TenantId] [int] IDENTITY(1,1) NOT NULL
93 ,[TenantAlias] [varchar](3) NOT NULL
94 ,[TenantName] [varchar](256) NOT NULL
95 ,[Other] INTEGER
96 ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC, [TenantAlias] ASC, [TenantName] ASC)
97) ON [PS_Tenant_Isolation]([TenantId]);
98
99INSERT INTO Auth.Tenant ( TenantAlias, TenantName )
100VALUES ( 'A', 'Apple' ),
101 ( 'A', 'Apple' ),
102 ( 'B', 'Banana' );
103
104SELECT *
105FROM Auth.Tenant
106
107UPDATE Auth.Tenant
108 SET TenantName = 'Orange'
109WHERE TenantID = 2;
110
111SELECT *
112FROM Auth.Tenant
113
114DROP TABLE Auth.Tenant;
115
116IF ( OBJECT_ID( 'Auth.Tenant', 'U' ) IS NULL )
117BEGIN
118 --DROP TABLE Auth.Tenant;
119 CREATE TABLE Auth.Tenant
120 (
121 TenantID INTEGER IDENTITY( 1, 1 ) NOT NULL,
122 TenantAlias VARCHAR( 3 ) NOT NULL,
123 TenantName VARCHAR( 256 ) NOT NULL
124 );
125
126 ALTER TABLE Auth.Tenant
127 ADD CONSTRAINT PK__Tenant
128 PRIMARY KEY CLUSTERED ( TenantID )
129 ON [Auth];
130
131 ALTER TABLE Auth.Tenant
132 ADD CONSTRAINT UQ__Tenant__TenantAlias
133 UNIQUE ( TenantAlias )
134 ON [Auth];
135
136 ALTER TABLE Auth.Tenant
137 ADD CONSTRAINT UQ__Tenant_TenantName
138 UNIQUE ( TenantName )
139 ON [Auth];
140
141 INSERT INTO Auth.Tenant ( TenantAlias, TenantName )
142 VALUES ( 'A', 'Apple' ),
143 ( 'B', 'Banana' );
144END;
145GO
146
147IF ( OBJECT_ID( 'Auth.TenantData', 'U' ) IS NULL )
148BEGIN
149 --DROP TABLE Auth.TenantData;
150 CREATE TABLE Auth.TenantData
151 (
152 TenantDataID INTEGER IDENTITY( 1, 1 ) NOT NULL,
153 TenantID INTEGER NOT NULL,
154 Other BIT
155 );
156
157 ALTER TABLE Auth.TenantData
158 ADD CONSTRAINT PK__TenantData
159 PRIMARY KEY CLUSTERED ( TenantID, TenantDataID )
160 ON [PS_Tenant_Isolation]( TenantID );
161
162 ALTER TABLE Auth.TenantData
163 ADD CONSTRAINT FK__TenantData__Tenant
164 FOREIGN KEY ( TenantID )
165 REFERENCES Auth.Tenant ( TenantID );
166
167 INSERT INTO Auth.TenantData ( TenantID, Other )
168 VALUES ( 1, 1 ),
169 ( 2, 0 );
170END;
171GO
172
173SELECT *
174FROM Auth.Tenant;
175
176SELECT *
177FROM Auth.TenantData;
178
179SELECT ObjectName = OBJECT_NAME( ps.object_id ),
180 IndexName = i.name,
181 DataSpaceName = ds.name,
182 ps.partition_number, ps.row_count
183FROM sys.dm_db_partition_stats ps
184INNER JOIN sys.indexes i
185 ON ps.object_id = i.object_id
186 AND ps.index_id = i.index_id
187INNER JOIN sys.data_spaces ds
188 ON i.data_space_id = ds.data_space_id
189WHERE ps.object_id IN ( SELECT OBJECT_ID( 'Auth.Tenant' )
190 UNION ALL
191 SELECT OBJECT_ID( 'Auth.TenantData' ) );
192
193DROP TABLE Auth.TenantData;
194DROP TABLE Auth.Tenant;