adding a computed column and partition scheme

• cmoy

  Ten Centuries

  Points: 1034

  More actions

  September 23, 2019 at 1:56 am

  #3682363

  Never mind, I figured it out, I need to persist the computed column in order for things to partition.

  Actually, the way I did this was to first add the persisted computed column, then dropped the primary key clustered index and made it into a nonclustered, then create the clustered index on the computed column using the partition scheme.

  --==============================

  so I have a table like this:

  CREATE TABLE Collection(CollectionID bigint, Name varchar(100) CONSTRAINT [PK_Collection] PRIMARY KEY CLUSTERED )

  I created a partition function and scheme to put this table into 10 different filegroups.  I intend to divvy this out by using a modulus function, CollectionID % 10.

  So what I want to do is to add a computed column to the table and use my partition scheme function, but I keep getting a syntax error.  How do I resolve this?

  ALTER TABLE [dbo].[Collection]

  ADD PartitionColumn AS (CollectionID % 10 )

  ON Collection_PartitionScheme(PartitionColumn);

   

  • This topic was modified 5 years, 2 months ago by  cmoy.

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  September 24, 2019 at 2:10 am

  #3682792

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.