Optimizing table/indices for getting the latest row (filtering by one additional column)

Question

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

The 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 ConfigurationData with the latest TimestampUtc for a given OrganizationID should 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 DESC is probably not a great idea).

Questions

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.?)

Answer 1

Given the low rate of inserts, your proposed index will be completely fine, and perfect for the usage goal.

Given a fresh index with fill factor 100%, and sufficient history for each organisation to fill a page, there will be a page split on the first subsequent insert for each organisation. But then there won't be another page split for that organisation until the new page is filled up.
Even these splits and fragmentation can be mitigated by starting with a <100 fill factor, and regular reorganise.

Aaron's thought regarding using non-clustered was probably because that would allow you to cluster on the always ascending identity column, ensuring the only splits and fragmentation you will get is in a very compact separate index. But I suspect he was only mentioning that in the context of you wanting very much to avoid fragmentation, and not as something really necessary in this scenario.

Answer 2

If you alter your table and add a flag for the active records you can use a filtered index, in this case a unique on OrganizationID where IsActive=1. Of course you had to update the currently active record for an Organization before you can insert the new active entry.

CREATE UNIQUE INDEX UI_test ON dbo.test (OrganizationID) WHERE IsActive=1

Answer 3

Since you said 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).INSERT performance doesn't matter too much, but I'd like to avoid horrible index fragmentation if at all possible

Alter your Table to add RowNumber column int,

Table1

[ConfigurationID]       INT IDENTITY (1,1) NOT NULL,
[OrganizationID]        INT NOT NULL,
[RowNumber]             INT  NULL,
[TimestampUtc]          DATETIME NOT NULL,
[ConfigurationData]     NVARCHAR (MAX) NOT NULL,
[ChangedBy]             NVARCHAR (256) NOT NULL,
[Comment]               NVARCHAR (MAX) NOT NULL,
[ChangeType]            INT NOT NULL

At the time of insert logic,

Begin Try
Begin Tran

// your Insert into Table1 first here

// After Insert here
;with CTE as
(
select OrganizationID,ROW_NUMBER()OVER(order by [TimestampUtc] desc)rownum
from Table1 where OrganizationID=@OrganizationID
)
update 
set RowNumber=c.rownum
from Table1 T
Inner join CTE C on t OrganizationID=c.OrganizationID
where t.OrganizationID=@OrganizationID

if (@@Trancount>0)
commit;

End try
Begin catch
if (@@Trancount>0)
rollback
end Catch

Create Clustered Index IX_CI_Org_rownum_Table1 on Table1([OrganizationID],[RowNumber])

At the time get latest record logic,

select * from Table1 where  
OrganizationID=@OrganizationID
and RowNumber=1

Create CI on ([OrganizationID],[RowNumber]) will be far narrower than ([OrganizationID],[TimestampUtc]) .So index fragmentation will be minimize or no frgementation.

Also you can see youself,how fetching top 1 or top 2,.. record is easy and very performant.

If this table will not contain multi lingual data then change NVARCHAR to VARCHAR,if possible.

I don't know why [ChangedBy] is NVARCHAR (256) .when it can be like VARCHAR(50 or 100) .