I have a simple table:
CREATE TABLE [dbo].[UserTestGroups]( [UserTestGroupId] [int] IDENTITY(1,1) NOT NULL, [Token] [nvarchar](100) NOT NULL, [TestId] [bigint] NOT NULL, [Group] [tinyint] NOT NULL, [InsertDate] [datetime] NOT NULL)
The table will have a relatively large number of inserts - initially 10,000 sessions with 8 rows per session for a total of 80,000 daily inserts. We expect that to increase significantly in the near future. Regardless, we try to make our platform as resilient as possible, and not just to be OK with current load.
The table will be used for reporting, which can be considered a secondary requirement.
Since we are using Entity Framework (Microsoft's ORM) all tables that are written to need a logical primary key, so I added an Identity column that I simply ignore. I don't like having composite keys, they tend to make nightmare with ORMs, so unless I absolutely have to, I add another identity column and make it the PK.
All the BI report's queries will be based on the Token, which is a sessionId - the GUID, so I created the clustered index on that
Our DBA, who is a SQL Server MVP, told me that having the clustered index on a GUID column will cause fragmentation and that I should create the clustered index on the
IDENTITY column, and create a nonclustered index for the
I don't understand, won't the nonclustered index have the same fragmentation problem? Why would duplicating the data into a new index be better than using a clustered index?
The token-Guid isn't the PK, the PK is a composition of all the columns together (including the
Currently (for I really don't know what reason) all GUID values are stored as
NVARCHAR(100), I don't know what's the historical reason-justification for that, but that is what we have.
Our DBA is overseas and our communication is a tweet long, so I can't get a proper answer until he comes back