How to create partition on an existing non-partitioned table in synapse dedicated sql pool?

Question

Is it possible to create partition on an existing non-partitioned table in synapse dedicated sql pool?

Answer 1

Hi,

The solution is simply to create a partitioned table with the same structure using CTAS statement -> DROP the old table -> Rename the new table to have the old name

CREATE TABLE [dbo].[T]  
(  
    ID int NOT NULL  
,   Txt nvarchar(20) NOT NULL  
)  
WITH (DISTRIBUTION = HASH(ID))  
GO  
  
INSERT T(ID, Txt) VALUES (1,'Ronen')  
INSERT T(ID, Txt) VALUES (2,'Ariely')  
INSERT T(ID, Txt) VALUES (2000,'Ariely')  
GO  
SELECT * FROM T  
GO  
  
------------------------------------------  
-- Step 1: Create a partitioned table with the same structure using CTAS statement  
CREATE TABLE dbo.T1  
    WITH (  
		DISTRIBUTION = HASH(ID),  
		CLUSTERED COLUMNSTORE INDEX,  
		PARTITION ([ID] RANGE LEFT FOR VALUES(222)))  
AS   
SELECT * FROM T  
GO  
  
-- Step 2: Confirm the data in the new table  
SELECT * FROM T1  
GO  
  
-- Step 3: Drop old table and rename new table  
DROP TABLE T  
GO  
RENAME OBJECT T1 TO T;  
GO  
------------------------------------------ All is ready!  
SELECT * FROM T  
GO  
  
------------------------------------------   
-- Clean the table  
DROP TABLE T  
GO  

Answer 2

Hello @Neetu Neetu ,

Thanks for the question and using MS Q&A paltform.

Switch from non-partitioned table to partitioned table is not supported in Azure Synapse, as check constraints are not supported to enforce the range of values in a table.

For more details, refer to Partitioning tables in Azure Synapse.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is jhow you can be part of Q&A Volunteer Moderators