CODEWITHSUNDEEP
sql-serverazure-sql-databasessms
Vikrant
Vikrant

Reputation: 1187

I am not able to see Create partition wizard in SQL Server Management Studio V18 for Azure SQL

I want to do horizontal partitioning in my SQL Server. Some of the tutorials say I need to click the table -> Storage -> Click Partition. something like below.

enter image description here

Issue: though, for my case, I am only seeing "Manage compression". I am not seeing "Create partition", In addition, I am not able to see "FileGroups" at the database level.

I am using Azure SQL

No Partition option showing up:

enter image description here

No File group option visible:

enter image description here

Upvotes: 0

Views: 926

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89316

SSMS has that wizard blocked, as Azure SQL Database doesn't support using non-primary filegroups for partition schemes. You can provide feedback for SSMS here.

The DDL for partitioning in Azure SQL Database is the same as for regular SQL Server, so you can write it by hand, or use the SSMS wizard against a local SQL Server to generate the script. Just remember to point all partitions to the primary filegroup.

Minimally on Azure SQL Database, this looks like:

CREATE PARTITION FUNCTION pf(INT)  
AS RANGE RIGHT FOR VALUES (1, 100, 1000);  

CREATE PARTITION SCHEME ps  
AS PARTITION pf  
ALL TO ([Primary]);  

create table PartitionedTable(id int identity primary key, a int)
on ps(id);

Technically tables aren't partitioned. Tables are comprised of one or more indexes, each of which may be stored on a filegroup or a partition scheme. To see all your partitioned tables (here defined as a table whose main index is stored on a partition scheme), run a query like:

select schema_name(t.schema_id) schema_name, t.name table_name, ds.name partition_scheme
from sys.tables t
join sys.indexes i
  on t.object_id = i.object_id
join sys.data_spaces ds
  on i.data_space_id = ds.data_space_id
where i.type in (0,1,5) --heap, CI, CCI
  and ds.type = 'PS' --partition scheme

Upvotes: 1

Related Questions