Lesson Learned #61: Using Schemas in Azure SQL Managed Instance

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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!