Home
%3CLINGO-SUB%20id%3D%22lingo-sub-369166%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2361%3A%20Using%20Schemas%20in%20Azure%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-369166%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Jan%2019%2C%202019%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20Hello%20Team%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20This%20week%20I%20worked%20on%20a%20service%20request%20where%20our%20customer%20asked%20about%20the%20compatibility%20feature%20using%20schemas%20and%20partitioning%2C%20for%20this%20reason%20and%20thanks%20to%20near%20100%25%20compatibility%20of%20SQL%20Server%20we%20have%20the%20option%20to%20create%20schemas%20and%20split%20tables%20among%20different%20filegroups.%20I%20would%20like%20to%20share%20with%20you%20the%20example%20provided%20explaining%20how%20to%20do%20it.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bcode%20language%3D%22SQL%22%5D%20%3CBR%20%2F%3E%20CREATE%20DATABASE%20SchemasExample%20%3CBR%20%2F%3E%20GO%20%3CBR%20%2F%3E%20USE%20SchemasExample%20%3CBR%20%2F%3E%20GO%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Review%20the%20files%20per%20database%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20SELECT%20*%20FROM%20SYS.sysFILES%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Create%20FileGroups%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20ALTER%20DATABASE%20SchemasExample%20%3CBR%20%2F%3E%20ADD%20FILEGROUP%20Data01%3B%20%3CBR%20%2F%3E%20GO%20%3CBR%20%2F%3E%20ALTER%20DATABASE%20SchemasExample%20%3CBR%20%2F%3E%20ADD%20FILEGROUP%20Data02%3B%20%3CBR%20%2F%3E%20GO%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Add%20File%20per%20FileGroup%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20ALTER%20DATABASE%20SchemasExample%20%3CBR%20%2F%3E%20ADD%20FILE%20%3CBR%20%2F%3E%20(%20%3CBR%20%2F%3E%20NAME%20%3D%20Data01_01%2C%20%3CBR%20%2F%3E%20SIZE%20%3D%2050MB%2C%20%3CBR%20%2F%3E%20MAXSIZE%20%3D%2050MB%2C%20%3CBR%20%2F%3E%20FILEGROWTH%20%3D%2010MB%20%3CBR%20%2F%3E%20)%20%3CBR%20%2F%3E%20TO%20FILEGROUP%20Data01%3B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20ALTER%20DATABASE%20SchemasExample%20%3CBR%20%2F%3E%20ADD%20FILE%20%3CBR%20%2F%3E%20(%20%3CBR%20%2F%3E%20NAME%20%3D%20Data02_01%2C%20%3CBR%20%2F%3E%20SIZE%20%3D%2050MB%2C%20%3CBR%20%2F%3E%20MAXSIZE%20%3D%2050MB%2C%20%3CBR%20%2F%3E%20FILEGROWTH%20%3D%2010MB%20%3CBR%20%2F%3E%20)%20%3CBR%20%2F%3E%20TO%20FILEGROUP%20Data02%3B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Create%20Schemas%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CREATE%20SCHEMA%20MyData2018%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CREATE%20SCHEMA%20MyData2019%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Create%20Tables%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CREATE%20TABLE%20MyData2018.Sales%20%3CBR%20%2F%3E%20(%20%5BMY_VALUE%5D%20%5Bint%5D%20NOT%20NULL%2C%20Total%20float%20%2C%20%3CBR%20%2F%3E%20CONSTRAINT%20%5BPK_TBL_PARTITION%5D%20PRIMARY%20KEY%20CLUSTERED%20(%5BMY_VALUE%5D%20ASC)%20%3CBR%20%2F%3E%20)%20ON%20Data01%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CREATE%20TABLE%20MyData2019.Sales%20%3CBR%20%2F%3E%20(%20%5BMY_VALUE%5D%20%5Bint%5D%20NOT%20NULL%2C%20Total%20float%20%2C%20%3CBR%20%2F%3E%20CONSTRAINT%20%5BPK_TBL_PARTITION%5D%20PRIMARY%20KEY%20CLUSTERED%20(%5BMY_VALUE%5D%20ASC)%20%3CBR%20%2F%3E%20)%20ON%20Data02%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Add%20data....%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20TRUNCATE%20TABLE%20MyData2018.Sales%20%3CBR%20%2F%3E%20TRUNCATE%20TABLE%20MyData2019.Sales%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20DECLARE%20%40nValues%20AS%20INTEGER%20%3D%200%20%3CBR%20%2F%3E%20DECLARE%20%40nFormat%20AS%20INTEGER%20%3D%200%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20begin%20transaction%20%3CBR%20%2F%3E%20WHILE%20%40nFormat%20%26lt%3B10%20%3CBR%20%2F%3E%20BEGIN%20%3CBR%20%2F%3E%20SET%20%40nFormat%20%3D%20%40nFormat%20%2B1%20%3CBR%20%2F%3E%20SET%20%40nValues%20%3D%200%20%3CBR%20%2F%3E%20WHILE%20%40nValues%26lt%3B%3D90000%20%3CBR%20%2F%3E%20BEGIN%20%3CBR%20%2F%3E%20SET%20%40nValues%20%3D%40nValues%2B1%20%3CBR%20%2F%3E%20insert%20into%20%5BMyData2018%5D.%5BSales%5D%20(my_value%2CTotal)%20%3CBR%20%2F%3E%20values(CONVERT(int%2CCONVERT(varchar(20)%2C%40nformat)%2BCONVERT(varchar(20)%2C%40nValues))%2CRAND())%20%3CBR%20%2F%3E%20insert%20into%20%5BMyData2019%5D.%5BSales%5D%20(my_value%2CTotal)%20%3CBR%20%2F%3E%20values(CONVERT(int%2CCONVERT(varchar(20)%2C%40nformat)%2BCONVERT(varchar(20)%2C%40nValues))%2CRAND())%20%3CBR%20%2F%3E%20END%20%3CBR%20%2F%3E%20END%20%3CBR%20%2F%3E%20commit%20transaction%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Retrieve%20data%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20SELECT%20COUNT(*)%20FROM%20%5BMyData2018%5D.%5BSales%5D%20%3CBR%20%2F%3E%20SELECT%20COUNT(*)%20FROM%20%5BMyData2019%5D.%5BSales%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Retrieve%20total%20data%20%3CBR%20%2F%3E%20--%20%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20SELECT%20COUNT(*)%20FROM%20(%20SELECT%20my_value%2CTotal%20FROM%20%5BMyData2018%5D.%5BSales%5D%20%3CBR%20%2F%3E%20union%20all%20%3CBR%20%2F%3E%20SELECT%20my_value%2CTotal%20FROM%20%5BMyData2019%5D.%5BSales%5D)%20Total%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5B%2Fcode%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Enjoy!%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-369166%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jan%2019%2C%202019%20Hello%20Team%2CThis%20week%20I%20worked%20on%20a%20service%20request%20where%20our%20customer%20asked%20about%20the%20compatibility%20feature%20using%20schemas%20and%20partitioning%2C%20for%20this%20reason%20and%20thanks%20to%20near%20100%25%20compatibility%20of%20SQL%20Server%20we%20have%20the%20option%20to%20create%20schemas%20and%20split%20tables%20among%20different%20filegroups.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-369166%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eazure%20sql%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Emanaged%20instance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eschemas%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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!