分区如何创建 [英] partitions how to create

问题描述

我们在sql中有2个表我们要分区

表1和表2它们是相同的模式。

表1我们收到一个数据流并喜欢说1小时数据然后将其移到表2. 

表2是按日期分区并存储所有数据。我们每晚创建一个新的日期分区来接收传入的日期数据。

表2有一个日期时间列，该列被编入索引并存储在分区方案中，并且正常工作 

表1我们尝试将其分区间隔15分钟，因此我们可以截断分区，因为它们比1小时更长。 

我们正在考虑创建提前分区，因为我们无法创建分区，因为表正在从流中接收数据。我想如果我尝试做一个ALTER PARTITION FUNCTION [...]（）SPLIT RANGE（...）;将阻止
表被插入。 因此，让所有分区预先确定并在手工创建之前会很好。 

因为我们要保留1小时的数据，我们可以有15分钟的分区...所以0 ，15,30,45。如果时间在0-15分钟之间是在0分区，如果在15-30之间它将在15分区，依此类推。 

我的问题是，如果表列是日期时间列，如何创建要存储在此分区上的表列索引？

例如，我们有像这样的行

UserGlobalId CreatedDateTime

783594 2019-04-04 19：40：25.9080000

822226 2019-04-04 18：05：25.1660000

157619 2019-04-04 17：20：25.0000000

并有像这样的分区

0表示0< 15

15表示15 <30

30表示30 <45

45 45>

如何将数据放入适当的分区？

解决方案

如果你想要一小时表的四个分区，你可以这样做：

创建分区功能四分之一（int）作为价值范围（15,30,45）
创建分区方案季度宿舍分区四分之一ALL TO（"PRIMARY"）
 
 
 CREATE TABLE hourdata（id int identity NOT NULL，
 insertdate datetime2（3）NOT NULL DEFAULT sysdatetime（），
 minute as datepart（minute，insertdate）PERSISTED，
 UNIQUE CLUSTERED（id，minute） 
）ON季度（分钟）
 

我非常怀疑这是一个好主意。我认为按照完整的日期时间值分区会更好，并且在白天的某个时刻你必须添加和删除分区，这意味着这意味着停机时间。

we have 2 tables in sql we would like to partition

Table 1 and Table 2 they are the same schema.

Table 1 we receive a data stream and like to keep say 1 hr of data then move it to table 2. 

Table 2 is partition by date and stores all the data. We create a new date partition nightly to receive the incoming days data.

Table 2 has a datetime column that is indexed and stored on the partition scheme and is working ok 

Table 1 we are trying to partition it by 15 minute intervals so we can truncate the partitions as they are older then 1 hr. 

We are thinking to create the partitions ahead of time since we would not be able to create partitions as the table is receiving data from the stream. I'm think if I try to do an ALTER PARTITION FUNCTION [...]() SPLIT RANGE (...); is going to block that table from being inserted.  So it would be nice to have all the partitions predetermine and created before hand. 

So since we want to keep 1 hr of data we can have 15 minute partitions... so 0, 15, 30, 45. if the time is between 0-15minutes is in the 0 partition, if is between 15-30 it would be in 15 partition and so forth. 

Question I have is how can I create the table column index to be stored on this partition if the table column is a datetime column?

So for example we have rows like 

UserGlobalId CreatedDateTime
783594 2019-04-04 19:40:25.9080000
822226 2019-04-04 18:05:25.1660000
157619 2019-04-04 17:20:25.0000000

and have partitions like 

0 for 0 <15

15 for 15<30

30 for 30<45

45 for 45 >

how can I get the data to fall into the appropriate partitions?

解决方案

If you want four partitions for your one-hour table, you can do this:

CREATE PARTITION FUNCTION quarterparts (int) AS RANGE RIGHT FOR VALUES (15, 30, 45)
CREATE PARTITION SCHEME quarterscheme AS PARTITION quarterparts ALL TO ("PRIMARY")


CREATE TABLE hourdata (id int identity NOT NULL, 
                       insertdate datetime2(3) NOT NULL DEFAULT sysdatetime(),
                       minute as datepart(minute, insertdate) PERSISTED,
                       UNIQUE CLUSTERED(id, minute)
) ON quarterscheme(minute)

I am very much in doubt that this is a good idea though. I think it would be better to have partitions by the full datetime value, and at some point during the day you will have to add and remove partitions, taking the downtime this implies.

这篇关于分区如何创建的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！