Lesson Learned #43: Using partitioning option for Azure SQL database

Published 03-13-2019 07:00 PM 4,301 Views
First published on MSDN on May 07, 2018
Some days ago, I have been working on advisory service request where our customer asked for partitioning option for Azure SQL database. They found the guidelines but when they tried to implement it, they got the error message: 'filegroup' is not supported in this version of SQL Server.

Trying to answering the question, partition is supported on Azure SQL Database but you couldn’t specify the filegroup. All partitions need to be created using the same filegroup (PRIMARY).

Please, see the following example:
-- ====================================
-- Create the database.
-- ====================================
CREATE DATABASE dbPartition (EDITION = 'standard', SERVICE_OBJECTIVE ='S3' )

-- ====================================
-- Create the partition function.
-- ====================================
CREATE PARTITION FUNCTION PF_HASH_BY_VALUE (BIGINT) AS RANGE LEFT
FOR VALUES (100000, 200000, 300000, 400000, 500000, 600000, 700000, 800000, 900000)

SELECT * FROM sys.partition_functions

-- ====================================
-- Create the schema partition.
-- ====================================
CREATE PARTITION SCHEME PS_HASH_BY_VALUE
AS PARTITION PF_HASH_BY_VALUE
ALL TO ([PRIMARY]);
GO

SELECT * FROM sys.partition_schemes

-- ====================================
--  Configure the Distribution data.
-- ====================================
SELECT
MY_VALUE,
$PARTITION.PF_HASH_BY_VALUE(MY_VALUE) AS HASH_IDX
FROM
(
VALUES
(1),
(100001),
(200001),
(300001),
(400001),
(500001),
(600001),
(700001),
(800001),
(900001)
) AS TEST (MY_VALUE);
GO

-- ====================================
-- Create the table, add some data and review its Distribution.
-- ====================================

CREATE TABLE [TBL_PARTITION]
( [MY_VALUE] [bigint] NOT NULL,
CONSTRAINT [PK_TBL_PARTITION] PRIMARY KEY CLUSTERED ([MY_VALUE] ASC)
) ON PS_HASH_BY_VALUE ([MY_VALUE])

insert into [TBL_PARTITION] (my_value) values(100001)
insert into [TBL_PARTITION] (my_value) values(200001)
insert into [TBL_PARTITION] (my_value) values(300001)
insert into [TBL_PARTITION] (my_value) values(400001)
insert into [TBL_PARTITION] (my_value) values(500001)
insert into [TBL_PARTITION] (my_value) values(600001)
insert into [TBL_PARTITION] (my_value) values(700001)
insert into [TBL_PARTITION] (my_value) values(800001)
insert into [TBL_PARTITION] (my_value) values(900001)

insert into [TBL_PARTITION] (my_value) values(100002)
insert into [TBL_PARTITION] (my_value) values(200002)
insert into [TBL_PARTITION] (my_value) values(300002)
insert into [TBL_PARTITION] (my_value) values(400002)
insert into [TBL_PARTITION] (my_value) values(500002)
insert into [TBL_PARTITION] (my_value) values(600002)
insert into [TBL_PARTITION] (my_value) values(700002)
insert into [TBL_PARTITION] (my_value) values(800002)
insert into [TBL_PARTITION] (my_value) values(900002)

SELECT
MY_VALUE,
$PARTITION.PF_HASH_BY_VALUE(MY_VALUE) AS HASH_IDX
FROM
( SELECT MY_VALUE FROM [TBL_PARTITION] )  AS TEST (MY_VALUE);

-- ====================================
-- Review the partition distribution.
-- ====================================
SELECT object_name(object_id),* FROM sys.dm_db_partition_stats where object_name(object_id)='TBL_PARTITION'



Enjoy!
2 Comments
Occasional Visitor

Hi Jose,

 

Do we loose the performance benefit of partitioning by configuring same PRIMARY filegroup?

 

Regards,

Naveen

Occasional Visitor

id really be interested too to know the answer to Naveens question.

I am trying to partition on an SQL Server in azure (managed instance). Does this apply here too?

Otherwise I would not know where to place the required files as in https://docs.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-i...

 

Best Regards

Nippur

%3CLINGO-SUB%20id%3D%22lingo-sub-369039%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2343%3A%20Using%20partitioning%20option%20for%20Azure%20SQL%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-369039%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%20May%2007%2C%202018%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20Some%20days%20ago%2C%20I%20have%20been%20working%20on%26nbsp%3Badvisory%20service%20request%20where%20our%20customer%20asked%20for%26nbsp%3Bpartitioning%20option%20for%20Azure%20SQL%20database.%20They%20found%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fpartitions%2Fcreate-partitioned-tables-and-indexes%3Fview%3Dsql-server-2017%23Restriction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20guidelines%20%3C%2FA%3E%20but%20when%20they%20tried%20to%26nbsp%3Bimplement%20it%2C%20they%20got%20the%20error%20message%3A%26nbsp%3B'filegroup'%20is%20not%20supported%20in%20this%20version%20of%20SQL%20Server.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Trying%20to%20answering%20the%26nbsp%3Bquestion%2C%20partition%20is%20supported%20on%20Azure%20SQL%20Database%20but%20you%20couldn%E2%80%99t%20specify%20the%20filegroup.%20All%20partitions%20need%20to%20be%20created%20using%20the%20same%20filegroup%20(PRIMARY).%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Please%2C%20see%20the%20following%20example%3A%20%3CBR%20%2F%3E%3CBLOCKQUOTE%3E--%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%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Create%20the%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%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20CREATE%20DATABASE%20dbPartition%20(EDITION%20%3D%20'standard'%2C%20SERVICE_OBJECTIVE%20%3D'S3'%20)%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%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Create%20the%20partition%20function.%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%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20CREATE%20PARTITION%20FUNCTION%20PF_HASH_BY_VALUE%20(BIGINT)%20AS%20RANGE%20LEFT%20%3CBR%20%2F%3E%20FOR%20VALUES%20(100000%2C%20200000%2C%20300000%2C%20400000%2C%20500000%2C%20600000%2C%20700000%2C%20800000%2C%20900000)%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20SELECT%20*%20FROM%20sys.partition_functions%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%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Create%20the%20schema%20partition.%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%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20CREATE%20PARTITION%20SCHEME%20PS_HASH_BY_VALUE%20%3CBR%20%2F%3E%20AS%20PARTITION%20PF_HASH_BY_VALUE%20%3CBR%20%2F%3E%20ALL%20TO%20(%5BPRIMARY%5D)%3B%20%3CBR%20%2F%3E%20GO%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20SELECT%20*%20FROM%20sys.partition_schemes%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%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%26nbsp%3B%20Configure%20the%20Distribution%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%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20SELECT%20%3CBR%20%2F%3E%20MY_VALUE%2C%20%3CBR%20%2F%3E%20%24PARTITION.PF_HASH_BY_VALUE(MY_VALUE)%20AS%20HASH_IDX%20%3CBR%20%2F%3E%20FROM%20%3CBR%20%2F%3E%20(%20%3CBR%20%2F%3E%20VALUES%20%3CBR%20%2F%3E%20(1)%2C%20%3CBR%20%2F%3E%20(100001)%2C%20%3CBR%20%2F%3E%20(200001)%2C%20%3CBR%20%2F%3E%20(300001)%2C%20%3CBR%20%2F%3E%20(400001)%2C%20%3CBR%20%2F%3E%20(500001)%2C%20%3CBR%20%2F%3E%20(600001)%2C%20%3CBR%20%2F%3E%20(700001)%2C%20%3CBR%20%2F%3E%20(800001)%2C%20%3CBR%20%2F%3E%20(900001)%20%3CBR%20%2F%3E%20)%20AS%20TEST%20(MY_VALUE)%3B%20%3CBR%20%2F%3E%20GO%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%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Create%20the%20table%2C%20add%20some%20data%20and%20review%20its%20Distribution.%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%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CREATE%20TABLE%20%5BTBL_PARTITION%5D%20%3CBR%20%2F%3E%20(%20%5BMY_VALUE%5D%20%5Bbigint%5D%20NOT%20NULL%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%20PS_HASH_BY_VALUE%20(%5BMY_VALUE%5D)%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(100001)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(200001)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(300001)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(400001)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(500001)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(600001)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(700001)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(800001)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(900001)%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(100002)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(200002)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(300002)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(400002)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(500002)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(600002)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(700002)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(800002)%20%3CBR%20%2F%3E%20insert%20into%20%5BTBL_PARTITION%5D%20(my_value)%20values(900002)%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20SELECT%20%3CBR%20%2F%3E%20MY_VALUE%2C%20%3CBR%20%2F%3E%20%24PARTITION.PF_HASH_BY_VALUE(MY_VALUE)%20AS%20HASH_IDX%20%3CBR%20%2F%3E%20FROM%20%3CBR%20%2F%3E%20(%20SELECT%20MY_VALUE%20FROM%20%5BTBL_PARTITION%5D%20)%26nbsp%3B%20AS%20TEST%20(MY_VALUE)%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%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20--%20Review%20the%20partition%20distribution.%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%3D%3D%3D%3D%3D%20%3CBR%20%2F%3E%20SELECT%20object_name(object_id)%2C*%20FROM%20sys.dm_db_partition_stats%20where%20object_name(object_id)%3D'TBL_PARTITION'%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Enjoy!%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-369039%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20May%2007%2C%202018%20Some%20days%20ago%2C%20I%20have%20been%20working%20on%26nbsp%3Badvisory%20service%20request%20where%20our%20customer%20asked%20for%26nbsp%3Bpartitioning%20option%20for%20Azure%20SQL%20database.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-369039%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%20Database%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epartition%20function%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epartitioning%20option%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esys%20dm_db_partition_stats%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esys%20partition_functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1283704%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%2343%3A%20Using%20partitioning%20option%20for%20Azure%20SQL%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1283704%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jose%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20we%20loose%20the%20performance%20benefit%20of%20partitioning%20by%20configuring%20same%20PRIMARY%20filegroup%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ENaveen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1288143%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%2343%3A%20Using%20partitioning%20option%20for%20Azure%20SQL%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288143%22%20slang%3D%22en-US%22%3E%3CP%3Eid%20really%20be%20interested%20too%20to%20know%20the%20answer%20to%20Naveens%20question.%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20partition%20on%20an%20SQL%20Server%20in%20azure%20(managed%20instance).%20Does%20this%20apply%20here%20too%3F%3C%2FP%3E%3CP%3EOtherwise%20I%20would%20not%20know%20where%20to%20place%20the%20required%20files%20as%20in%26nbsp%3B%3CFONT%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fpartitions%2Fcreate-partitioned-tables-and-indexes%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fpartitions%2Fcreate-partitioned-tables-and-indexes%3Fview%3Dsql-server-ver15%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3C%2FP%3E%3CP%3ENippur%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Mar 13 2019 07:00 PM
Updated by: