I have a SQL Server table (SQL Server 2012 SP3 Standard edition) that stores a bunch of configuration information (basically text blobs) for different organizations. The schema is something like this:
[ConfigurationID] INT IDENTITY (1,1) NOT NULL, [OrganizationID] INT NOT NULL, [TimestampUtc] DATETIME NOT NULL, [ConfigurationData] NVARCHAR (MAX) NOT NULL, [ChangedBy] NVARCHAR (256) NOT NULL, [Comment] NVARCHAR (MAX) NOT NULL, [ChangeType] INT NOT NULL
TimestampUtc will always be increasing (I won't ever be INSERTing "back-dated" entries into the table), and the rows won't ever be UPDATEd (I'm only INSERTing new rows). For some
OrganizationIDs there will be lots of rows, for some very few, and a new row for any
OrganizationID may be INSERTed at any time.
If needed, I can guarantee uniqueness of
TimestampUtc (but it would be great to have a solution that didn't need that).
INSERTs are relatively rare (at most dozens of times per day, but typically much less than that), reads are very frequent (essentially on every web request to my application).
My goals are:
- Getting the
ConfigurationDatawith the latest
TimestampUtcfor a given
OrganizationIDshould be extremely fast regardless of the size of the table
- INSERT performance doesn't matter too much, but I'd like to avoid horrible index fragmentation if at all possible (so my first idea of a unique clustered index on
OrganizationID ASC, TimestampUtc DESCis probably not a great idea).
I know I can denormalize and just store the latest
ConfigurationData in one table and the historical log of previous values in another table, but is it possible to meet my goals with just one table? What's the best way to do it? (I.e. what's the best index structure? do I need to change anything about the table schema, etc.?)