sql-server - SQL Server 表分区中列的显式转换

Question

我有如下表：

  CREATE TABLE [dbo].[PartitionExample]
  (
     [dateTimeColumn1] [datetime] NOT NULL,
     CONSTRAINT [PK_PartitionExample] PRIMARY KEY CLUSTERED 
     (
      [dateTimeColumn1] ASC
     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = 
        OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]

我创建了如下分区函数：

  CREATE PARTITION FUNCTION DateRangePF (INT)
   AS RANGE RIGHT FOR VALUES ( 20180601,20180901,20181201,20190301)

然后，我为它创建了分区方案：

  CREATE PARTITION SCHEME DateRangePS
    AS PARTITION DateRangePF TO 
     (FG032018_SampleDB,FG062018_SampleDB,FG092018_SampleDB,
       FG122018_SampleDB,FG032019_SampleDB);

现在，当我将分区方案应用于此表时，我想将 datetime 数据类型的 [dateTimeColumn1] 列显式转换为 INT 数据类型。但是当我尝试它时，我得到了语法错误：

 ALTER TABLE [dbo].[PartitionExample] ADD  
  CONSTRAINT [PK_PartitionExample] PRIMARY KEY CLUSTERED 
   (
     dateTimeColumn1 ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = 
    ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) 
    ON DateRangePS(
     convert(INT, CONVERT(CHAR(8), dateTimeColumn1, 112));

你们能告诉我吗

1. 在这种情况下如何实现显式转换。
2. 当我将 datetime 列或 char(8) 列显式转换为 INT 列进行分区时，它的性能也会更好。谢谢您的帮助。

标签： sql-serverdatabase-designdata-partitioning