2

I am confused to implement best practice, I have a table with a lot of fields which each of them need to be aggregated for analytics and reporting purposes, but sometimes we also require to get row basis records.

Would that be better to implement PRIMARY KEY + NON CLUSTERED COLUMNSTORE INDEX, or NOT NULL UNIQUE NONCLUTERED + Clustered columnstore index?

Alfin E. R.
  • 741
  • 1
  • 7
  • 24
  • Try both options with *your* data and *your* expected queries and see which option works out best? – Damien_The_Unbeliever May 03 '18 at 07:24
  • thank you Damien, some queries are selecting rows basis records, and some queries are doing aggregation through column basis. I kinda have difficulty to determine what's best support for both, sometimes aggregation is faster, but selecting rows is slower, sometimes selecting rows is faster, but aggregation is slower. – Alfin E. R. May 03 '18 at 07:37

1 Answers1

3

It all depends on the situation; how does your data look like and how are you going to search for that data.

Each table definitely needs a primary key. Whether the PK needs to be clustered or not depends if you have a column that is more eligeble to put a clustered index on. (A clustered index should be placed on a column whose value doesn't change and increases incrementally, meaning that the next value should be higher then the previous value).

Where to put indexes and what types of indexes, the order of the columns in your index and whether or not you'll need 'included columns' all depends on the situation and you'll need to do some analysis on that.

Frederik Gheysels
  • 56,135
  • 11
  • 101
  • 154
  • thank you Frederik, so it's definitely that a table need primary key ya? and yes the ID column that intended to be a primary key is increases incementally and no updates to that column. what do you think is a best scenario when whether to use Nonclustered or clustered columnstore index? in what example – Alfin E. R. May 03 '18 at 07:33
  • You can only have one clustered index per table since the clustered index is at the leaf-level and defines the storage order. So if your PK is already clustered, the other indexes need to be non-clustered – Frederik Gheysels May 03 '18 at 08:45