I have one large table that is partitioned (table name: Trans
). At the moment this table is to be created on 32 partitions. This table will contain approximately 300 million records and data older than 14 days will be deleted daily. One of the columns in this table is a reference to a table that will contain up to 5 million records (table name: Sens
) and I also want it to be partitioned. I would like to ask you about:
Will it be a problem that both tables will use the same partitioning function? So the
Sens
table would also be distributed over 32 partitions and would be save on the same files as theTrans
table. Is this a good approach ?The
Trans
table has aPK
based on two columnsTranID (Identity (1,1))
andParititionID
. At the moment,FK
to a smaller table ('Sens') is based on only one column -SenID
. The smaller table also has to be partitioned. What will be the difference in the approach / efficiency / speed of operation if thePK
in theSens
table will only be on theIDENTITY (1,1)
column instead of theIDENTITY (1,1)
column and thepartition column
, i.e.
ALTER TABLE [dbo].[Sen]
ADD CONSTRAINT [PK_SenID]
PRIMARY KEY CLUSTERED ([SenID] ASC) ON [PRIMARY];
-- or
ALTER TABLE [dbo].[Sen]
ADD CONSTRAINT [PK_SenID]
PRIMARY KEY CLUSTERED (
[SenID] ASC,
[PartitionID]
) ON [psTrans]([PartitionID])
- Have you ever try to have partition column which is computed ? I am thinking about choose partition according to new column which is computed base on other column in table:
CAST(HASHBYTES('MD5', [othercolumnInTable]) AS tinyint) % 32