First published on MSDN on Jan 19, 2019
Hello Team,
This week I worked on a service request where our customer asked about the compatibility feature using schemas and partitioning, for this reason and thanks to near 100% compatibility of SQL Server we have the option to create schemas and split tables among different filegroups. I would like to share with you the example provided explaining how to do it.
[code language="SQL"]
CREATE DATABASE SchemasExample
GO
USE SchemasExample
GO
-- =============================
-- Review the files per database
-- ===============================
SELECT * FROM SYS.sysFILES
-- ===============================
-- Create FileGroups
-- ===============================
ALTER DATABASE SchemasExample
ADD FILEGROUP Data01;
GO
ALTER DATABASE SchemasExample
ADD FILEGROUP Data02;
GO
-- ========================
-- Add File per FileGroup
-- ========================
ALTER DATABASE SchemasExample
ADD FILE
(
NAME = Data01_01,
SIZE = 50MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
)
TO FILEGROUP Data01;
ALTER DATABASE SchemasExample
ADD FILE
(
NAME = Data02_01,
SIZE = 50MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
)
TO FILEGROUP Data02;
-- ========================
-- Create Schemas
-- ========================
CREATE SCHEMA MyData2018
CREATE SCHEMA MyData2019
-- ========================
-- Create Tables
-- ========================
CREATE TABLE MyData2018.Sales
( [MY_VALUE] [int] NOT NULL, Total float ,
CONSTRAINT [PK_TBL_PARTITION] PRIMARY KEY CLUSTERED ([MY_VALUE] ASC)
) ON Data01
CREATE TABLE MyData2019.Sales
( [MY_VALUE] [int] NOT NULL, Total float ,
CONSTRAINT [PK_TBL_PARTITION] PRIMARY KEY CLUSTERED ([MY_VALUE] ASC)
) ON Data02
-- ========================
-- Add data....
-- ========================
TRUNCATE TABLE MyData2018.Sales
TRUNCATE TABLE MyData2019.Sales
DECLARE @nValues AS INTEGER = 0
DECLARE @nFormat AS INTEGER = 0
begin transaction
WHILE @nFormat <10
BEGIN
SET @nFormat = @nFormat +1
SET @nValues = 0
WHILE @nValues<=90000
BEGIN
SET @nValues =@nValues+1
insert into [MyData2018].[Sales] (my_value,Total)
values(CONVERT(int,CONVERT(varchar(20),@nformat)+CONVERT(varchar(20),@nValues)),RAND())
insert into [MyData2019].[Sales] (my_value,Total)
values(CONVERT(int,CONVERT(varchar(20),@nformat)+CONVERT(varchar(20),@nValues)),RAND())
END
END
commit transaction
-- ========================
-- Retrieve data
-- ========================
SELECT COUNT(*) FROM [MyData2018].[Sales]
SELECT COUNT(*) FROM [MyData2019].[Sales]
-- ========================
-- Retrieve total data
-- ========================
SELECT COUNT(*) FROM ( SELECT my_value,Total FROM [MyData2018].[Sales]
union all
SELECT my_value,Total FROM [MyData2019].[Sales]) Total
[/code]
Enjoy!