{"id":878,"date":"2021-04-15T08:00:00","date_gmt":"2021-04-15T05:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=878"},"modified":"2023-02-28T22:48:01","modified_gmt":"2023-02-28T19:48:01","slug":"giati-na-kanoyme-partition-ena-pinaka-kai-pos-g","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/giati-na-kanoyme-partition-ena-pinaka-kai-pos-g\/","title":{"rendered":"Why partition a table and how to do it in SQL Server"},"content":{"rendered":"

As we know a relational database mainly consists of entities called tables.<\/p>\n\n\n\n

Arrays have no logical limit to the size they can reach. There are cases when their size can become unmanageable.<\/p>\n\n\n\n

Where does the problem start?<\/h4>\n\n\n\n

When a table gets too big, the times for some future increase rebuild the indexes<\/strong>, as well as of statistics<\/strong>.<\/p>\n\n\n\n

As if this were not enough a large table will slow down the database during backup execution.<\/p>\n\n\n\n

Finally, in a very large table the times to make seek records will also be increased. This results in quite high I\/O processes on the physical disks.<\/p>\n\n\n\n

That's where table partitioning comes in<\/h4>\n\n\n\n

Table partitioning is a technology in RDBMS<\/strong>(relational database management systems), which allows a table to be physically divided into several filegroups<\/strong>. These can be on different drives. <\/p>\n\n\n\n

The separation into different filegroups<\/strong> it is done by selecting some field which is usually of date type.<\/p>\n\n\n\n

How does table partitioning work?<\/h4>\n\n\n\n

In SQL Server the first thing we do is build new<\/strong> filegroups<\/strong>, depending on how we want to implement the partitioning (e.g. by year, quarter, month, etc.). This step is not necessary as we can use the existing Primary filegroup for all partitions. <\/p>\n\n\n\n

Then we'll have to make one partition<\/strong> function<\/strong> that we set the limits for each e.g. from 1\/1\/2016 to 31\/12\/2016.<\/p>\n\n\n\n

We continue with its creation partition scheme<\/strong> where we define which filegroups belong to this group (we can define only the Primary filegroup).<\/p>\n\n\n\n

In the end we have two options. Let's make one new table on this scheme<\/strong> which will already be partitioned, copying the entries afterwards. Otherwise, in the table we already have and is not partitioned, let's do it drop the clustered index<\/strong> and make a new one above in the partitioned scheme<\/strong>. <\/p>\n\n\n\n

Of course both options are correct. In the first we have the disadvantage that we need double the space and in the second that if something goes wrong we may be led to restore.<\/p>\n\n\n\n

What does it offer us?<\/h4>\n\n\n\n

In addition to increased performance (if we use different storage for the datafiles belonging to separate filegroups), it allows us to partition old years with the command switch<\/strong> let's do them archive<\/strong> in historical tables. However the command switch<\/strong> gives us another option, to delete instant entries without going through the transaction log by switching the partition with the entries we want to delete, to a staging table and then doing it truncate<\/strong>. It also gives us the possibility of filegroups from previous years to turn them into read-only mode<\/strong>. This will enable us not to have to take a daily backup, saving total time from the backup.<\/p>\n\n\n\n

Let's see step by step what we have to do.<\/p>\n\n\n\n

First we create the filegroups from the properties of the base, we will also define datafiles for each filegroup:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Partition function<\/h4>\n\n\n\n

We create it partition function<\/strong> setting limits from where to when each partition will be defined. We select the field that will be the criterion, as usual it is datetime.<\/p>\n\n\n\n

Choosing range right<\/strong> we declare values as the minimum limit<\/strong> eg 20120101. <\/p>\n\n\n\n

If we chose range left<\/strong> (it's the default) we should set upper limits<\/strong> this time eg 20121231.<\/p>\n\n\n\n

This means that in this example it has only a single limit. In a record with date 20130101 we would have 2 options. <\/p>\n\n\n\n

By choice range right<\/strong> it would be in the filegroup that has 20120101 as the minimum, while with the option range left<\/strong> it would be in the default filegroup, since the upper limit is 20120131.<\/p>\n\n\n\n

Let's build a working example of the whole process:<\/p>\n\n\n\n

DECLARE @boundary1 DATETIME\nDECLARE @boundary2 DATETIME\nDECLARE @boundary3 DATETIME\nselect @boundary1='20120101', @boundary2='20130101',@boundary3='20140101'\nCREATE PARTITION FUNCTION tade_func(DATETIME) as \nrange right for values (@boundary1, @boundary2, @boundary3)<\/pre>\n\n\n\n
Partition scheme<\/h4>\n\n\n\n
We continue by creating the partition scheme by defining which filegroups belong to the partition function we created. Alternatively, we can use the Primary filegroup only:<\/p>\n\n\n\n
CREATE PARTITION SCHEME tade_scheme as\npartition tade_func to\n(\n[F2000],\n[F2012],\n[F2013],\n[F2014]\n)<\/pre>\n\n\n\n
Test data<\/h4>\n\n\n\n
For example, let's create a table declared in the scheme we created so that it is partitioned from the beginning:<\/p>\n\n\n\n
CREATE TABLE pelatis  \n(\n\tid INT IDENTITY(1,1),\n\tonoma varchar(20),\n\tepitheto varchar(20),\n\tcreate_dr DATETIME,\n\tprimary key (create_dr)\n)\nON tade_scheme(create_dr)<\/pre>\n\n\n\n
We fill it with records with a loop:<\/p>\n\n\n\n
declare  @i int;\nset @i = 365;\nwhile (@i>0)\nbegin\ninsert into pelatis values('Stratos','Ma',DATEADD(dd,@i,'2016\/01\/01'));\nset @i=@i-1;\nend<\/pre>\n\n\n\n
In which partition are the records located?<\/h4>\n\n\n\n
With the following query we can see in which partition each record is:<\/p>\n\n\n\n
select $PARTITION.tade_func(create_dr) as part_num,* from pelatis<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In a similar way, with a where we can see only a specific partition, e.g. 1:<\/p>\n\n\n\n
select * from dbo.pelatis where $PARTITION.tade_func (create_dr) = 1<\/pre>\n\n\n\n
How do we add new partitions<\/h4>\n\n\n\n
We can add filegroup for the new year in this way (after we have first created the filegroup and defined the datafile):<\/p>\n\n\n\n
ALTER PARTITION SCHEME tade_scheme NEXT USED [F2019]<\/pre>\n\n\n\n
ALTER PARTITION FUNCTION tade_func() SPLIT RANGE ('20190101')<\/pre>\n\n\n\n
How to remove partitions<\/h4>\n\n\n\n
We can remove a filegroup by merging so that the records go to the previous one:<\/p>\n\n\n\n
ALTER PARTITION FUNCTION tade_func() MERGE RANGE ('20100101')<\/pre>\n\n\n\n
The use of the SWITCH command to transfer documents to a history table and also delete data immediately<\/h4>\n\n\n\n
Let's create a history table defined in another filegroup:<\/p>\n\n\n\n
CREATE TABLE pelatis_hist  \u2013table creation for filegroup\n(\n       id INT IDENTITY(1,1),\n       onoma varchar(20),\n       epitheto varchar(20),\n       create_dr DATETIME,\n       primary key (create_dr)\n)\nON [prior]<\/pre>\n\n\n\n
With the following command that it belongs from the table to historical records, it will be transferred (switch) to the historical table within a few seconds:<\/p>\n\n\n\n
ALTER TABLE [dbo].[pelatis]\nSWITCH PARTITION 1 TO [dbo].[pelatis_hist]\nGO\n\nselect * from pelatis_hist<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We would follow the same procedure if we simply want to immediately delete the records belonging to a partition. The difference is that after the switch of the partition to another table we would also run the command truncate<\/strong> which would delete the entire table of old entries immediately without affecting the productive table:<\/p>\n\n\n\n
truncate table [dbo].[pelatis_hist];<\/pre>\n\n\n\n
How do we convert an existing table into partitioned without using a new table<\/h4>\n\n\n\n
In case we want to convert the table directly from unpartitioned to partitioned we will have to make one nonclustered<\/strong> index to datetime<\/strong> field where the partition will be made, let's do drop<\/strong> the constraint<\/strong> of the primary key, drop<\/strong> the clustered index<\/strong> in the primary key and creating a new one clustered index<\/strong> over the scheme<\/strong> that we have made (the process may take several hours):<\/p>\n\n\n\n
create nonclustered index nc_pelatisDT on dbo.pelatis(create_dr)\n\nALTER TABLE dbo.pelatis DROP CONSTRAINT [PK__pelatis___C6EE964AA629B42F]\n\ndrop index [PK__pelatis___C6EE964AA629B42F] on dbo.pelatis\n\ncreate clustered index pk_pelatis on dbo.pelatis(create_dr) on [tade_scheme](create_dr)<\/pre>\n\n\n\n
Create a new partitioned table and transfer the records from the old one<\/h4>\n\n\n\n
If we prefer the solution of creating an already partitioned table and transferring it from the old table to the new one, we do the following:<\/p>\n\n\n\n
We make a table in scheme<\/strong> which we made for the partition:<\/p>\n\n\n\n
CREATE TABLE pelatis_temp \n(\n       id INT IDENTITY(1,1),\n       onoma varchar(20),\n       epitheto varchar(20),\n       create_dr DATETIME,\n       primary key (create_dr)\n)\n\nON tade_scheme(create_dr)<\/pre>\n\n\n\n
We pass the records to the new table bit by bit:<\/p>\n\n\n\n
SET IDENTITY_INSERT pelatis_temp ON\nINSERT INTO pelatis_temp(id,onoma,epitheto,create_dr)\nSELECT * FROM pelatis p\nwhere  create_dr between '2012\/01\/01' and '2013\/01\/01'<\/pre>\n\n\n\n
We close the application's access at this point in order to transfer the last records that have not been transferred:<\/p>\n\n\n\n
SET IDENTITY_INSERT pelatis_temp ON\nINSERT INTO pelatis_Temp(id,onoma,epitheto,create_dr)\nSELECT * FROM pelatis p\nwhere  not exists (select * from pelatis_temp t where p.id = t.id)<\/pre>\n\n\n\n
We rename the tables:<\/p>\n\n\n\n
exec sp_rename 'pelatis','pelatis_old'\n\nexec sp_rename 'pelatis_temp','pelatis'<\/pre>\n\n\n\n
How to see what is contained in each partition<\/h4>\n\n\n\n
Before restoring the application, let's see how the separation has been done with the following query:<\/p>\n\n\n\n
SELECT distinct\n       p.object_id,\n       o.name AS table_name,\n       p.partition_number,\n       p.rows,\n       au.total_pages,\n       au.total_pages \/ 128 AS total_size_mb,\n       au.type_desc,\n       p.data_compression_desc,\n       g.name AS [filegroup_name],\n       RVL.value AS left_range_boundary,\n       RVR.value AS right_range_boundary\n       --PF.[name], RV.boundary_id, RV.[value]\nFROM sys.partitions AS p (nolock)\n       LEFT JOIN sys.objects AS o (nolock)\n             ON o.object_id = p.object_id\n       LEFT JOIN sys.indexes i (nolock)\n             ON p.object_id = i.object_id\n                    AND p.index_id = i.index_id\n       LEFT JOIN sys.allocation_units AS au (nolock)\n             ON p.hobt_id = au.container_id\n       LEFT JOIN sys.filegroups AS g (nolock)\n             ON g.data_space_id = au.data_space_id\n       LEFT JOIN sys.partition_schemes AS PS (nolock)\n             ON ps.data_space_id = i.data_space_id\n       LEFT JOIN sys.partition_functions AS PF (nolock)\n             ON PF.function_id = ps.function_id            \n       LEFT JOIN sys.partition_range_values AS RVL (nolock)\n             ON RVL.function_id = PF.function_id\n                    AND RVL.boundary_id + 1 = p.partition_number\n       LEFT JOIN sys.partition_range_values AS RVR (nolock)\n             ON RVL.function_id = PF.function_id\n                    AND RVR.boundary_id = p.partition_number\nWHERE 1=1\nand p.object_id in (object_id('pelatis'),object_id('pelatis_hist'))\nAND p.index_id = 1\nORDER BY table_name, partition_number\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Sources:<\/h2>\n\n\n\n