0

I have table like below:

  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]

I have created Partition Function like below:

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

Then, I have created Partition Scheme for it:

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

Now, When I am applying the partition scheme to this table, I want to apply explicit conversion of [dateTimeColumn1] column of datetime data type to INT Data Type. But when I tried it, I got syntax error:

 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));

Can you guys please let me know

  1. how explicit conversion can be implemented in such scenarios.
  2. Also would it perform better when I will perform explicit conversion of datetime column or char(8) column to INT Column for partition. Thank you for your help.
Murali Dhar Darshan
  • 285
  • 1
  • 17
  • Why not just use the correct data type (which by preference these days would be `datetime2` not `datetime`) for everything, rather than weirdly trying to work with `int`s? – Damien_The_Unbeliever Nov 20 '18 at 07:33
  • Well, we thought it would perform better when we will typecast from datetime column to INT column. Also, we have char(8) column in some other table based on which we will apply partition. – Murali Dhar Darshan Nov 20 '18 at 07:38

0 Answers0