I am using a denormalized table that collects approximately 35,000 rows per day.
The data are denormalized in an automated fashion out of our production database. One of the major motivations for denormalizing the data is that the C# -based UI utilizes many complex, non persistent calculations that, for quality assurance, we didn't want to recreate through views.
At the moment, we don't have any indexes on this table, but are exploring adding them. I'm a statistician by trade and am still trying to wrap my head around all of the indexing nuances. The big question I have is about the consequences of indexing both an INT and a VARCHAR that are perfectly correlated.
That is, the two fields, InstrumentId (INT) and InstrumentName (VarChar(50)) are recorded from the same normalized table. We included both in the denormalized data so that we could display the name without a join but also query on the INT. (The database has about 200 unique instruments)
Even though we included the INT for the purposes of querying, sometimes we are lazy and like to query on the VarChar because it is easier to validate the condition.
As we start adding indexes, I'm curious what the impact would be of indexing both the INT and VARCHAR as separate, non-clustered indexes. Considerations include speed, storage, fragmentation, etc.
Is adding both as indexes a reasonable approach, or is it something that could create headaches down the road? References to reading material that discuss these issues are appreciated.
I've looked at this question, which discusses the option of choosing one or the other, but I'm struggling to find references about using both.
Since you have a denormalized table with repeating values, it's advisable to create a clustered column store index first. Not only it will improve the query performance, but also it will reduce the size of the table.
After creating the clustered column store index, you can add the indexes on the go as per the executed queries. Such an approach is better than blindly creating indexes from the very beginning. In order to identify the missing indexes, you can you the built-in tools such as Actual Execution Plan which will show warnings for any missing indexes.