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


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.

 

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

Enjoy!

Comments (0)

Skip to main content