To ensure uniqueness there is a composite PK (clustered) containing:
为了确保唯一性,有一个复合PK(集群)包含:
[timestamp] [datetime2]
[userId] [varchar](36)
[cost_type] [varchar](20)
There are two more columns in the table:
表格中还有两列:
[cost_cent] [bigint] NULL
[consumption_cent] [bigint] NULL
Composite clustered primary keys are not ideal (incl. varchar) but what is the alternative?
复合群集主键不理想(包括varchar)但是替代方案是什么?
Having a heap table with a non clustered primary key? Additionally add another clustered index? But on what column? There is no identity column.
拥有非聚集主键的堆表?另外添加另一个聚集索引?但在什么专栏?没有标识列。
Background: there is a constant insert/update on this table via Merge statements. Table size is ~50 million rows
背景:通过Merge语句在此表上有一个常量插入/更新。表大小约为5000万行
Queries will use the PK with a time range mainly.
查询将主要使用具有时间范围的PK。
1 个解决方案
#1
1
Your index size is 58 bytes,i don't see a big issue with this size..
你的索引大小是58字节,我没有看到这个大小的大问题..
there is a constant insert/update on this table via Merge statements
通过Merge语句在此表上有一个常量插入/更新
if you go with existing setup of composite key(since 56 bytes is not that huge) ,updating primary key is a red flag,since
如果你使用复合键的现有设置(因为56字节不是那么大),更新主键是一个红色标志,因为
1.You may see some fragmentation
2.update/delete commands will also have to touch non clustered indexes
1.您可能会看到一些碎片2.update / delete命令也必须触及非聚簇索引
Some more options i would experiment with,since 50 million is not much huge
我会尝试更多的选择,因为5000万并不是很大
- Leave this table as heap and add a non clustered index with timestamp column as leading column and rest of the columns needed for a query as included columns .When you leave this table as heap,try answering the following questions yourself to see if leaving this table as heap helps you
将此表保留为堆,并添加一个非聚集索引,其中timestamp列为前导列,其余列为查询所需的列。当您将此表保留为堆时,请尝试自己回答以下问题以查看是否离开此表堆可以帮助你
- Will you ever need to join this table to other tables?
您是否需要将此表连接到其他表?
- Do you need a way to uniquely identify a record?
您需要一种方法来唯一识别记录吗?
2.I would also try adding an identity column and make it as primary key..
2.我还会尝试添加一个标识列并将其作为主键。
#1
1
Your index size is 58 bytes,i don't see a big issue with this size..
你的索引大小是58字节,我没有看到这个大小的大问题..
there is a constant insert/update on this table via Merge statements
通过Merge语句在此表上有一个常量插入/更新
if you go with existing setup of composite key(since 56 bytes is not that huge) ,updating primary key is a red flag,since
如果你使用复合键的现有设置(因为56字节不是那么大),更新主键是一个红色标志,因为
1.You may see some fragmentation
2.update/delete commands will also have to touch non clustered indexes
1.您可能会看到一些碎片2.update / delete命令也必须触及非聚簇索引
Some more options i would experiment with,since 50 million is not much huge
我会尝试更多的选择,因为5000万并不是很大
- Leave this table as heap and add a non clustered index with timestamp column as leading column and rest of the columns needed for a query as included columns .When you leave this table as heap,try answering the following questions yourself to see if leaving this table as heap helps you
将此表保留为堆,并添加一个非聚集索引,其中timestamp列为前导列,其余列为查询所需的列。当您将此表保留为堆时,请尝试自己回答以下问题以查看是否离开此表堆可以帮助你
- Will you ever need to join this table to other tables?
您是否需要将此表连接到其他表?
- Do you need a way to uniquely identify a record?
您需要一种方法来唯一识别记录吗?
2.I would also try adding an identity column and make it as primary key..
2.我还会尝试添加一个标识列并将其作为主键。