I have fairly large fact table (2 billion records, approx 120 GB). This table is not partitioned and the queries are very slow to respond. I am planning to partition the table and indexes. The table has an identity column which is the primary key and has a clustered index on it. There are other non-clustered indexes on it but I won't go in the details much here. The column, I am trying to partition is not part of primary key but is not null and this is creating a slight dilemma for me. I have two options.
I add this column as part of primary key i.e. composite primary key. Since the first column is identity, the combination would always be unique which means I don't have to worry about the applications accessing the table. The clustered index will automatically be partition aligned and other indexes can also be partition aligned.
The seconds option is to remove the clustered index on the identity column and make it unique non clustered. This index cannot be partitioned aligned since partition key is not part of it and hence would have to sit on one drive. Then create a clustered index on the partition key column which can be partition aligned and so all the other non clustered indexes.
Our DBA is in favour of second option since he doesn't want to change primary key. I am worried about the performance hit in option 2 since the index is not partition aligned.
I would appreciate any feedback plus any other method you would have used in such situation.