Ok, Scott... here you go. This creates a Clustered Index as described by you. Neither you nor the OP described what the datatype or content of the "Client" column should be so I made a guess. Let me know if I did it wrong according to what you were expecting.
The end result is >98% fragmentation at the end of the second day even though the inserts are done in exactly the same order as the Clustered Index keys on a daily basis.
Even if you insert for 1000 days (meaning plenty of rows so that fragmentation shouldn't be quite as bad as time wears on) and then do an index rebuild, you're still going to hit 6% fragmentation on the next day of inserts. If you're one of the poor folks that believes that you should REORGANIZE the index when you hit 5%, then you'll be REORGANIZING the Clustered Index EVERY TIME you run index maintenance. If you believe that you should wait until 10%, then it will "only" take 22 days until you need to REORGANIZE
--=====================================================================================================================
-- Create and populate a table to support the test.
-- It simply contains a list of "Clients" that will be used to populate the test table.
--=====================================================================================================================
--===== Create the support table and populate it with a unique list of 1,000 clients.
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
,cteClients AS (
SELECT TOP (1000)
Client = 'Client'+RIGHT(CONVERT(VARCHAR(10),ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))+10000,4)
FROM E1 a, E1 b, E1 c, E1 d, E1 e
)
SELECT Client
INTO dbo.UniqueClientList
FROM cteClients
;
--=====================================================================================================================
-- Create a table to store logical fragmentation values for each iteration of the "day test"
--=====================================================================================================================
CREATE TABLE dbo.FragLevel
(
Counter INT
,FragPercent FLOAT
)
;
--=====================================================================================================================
-- Setup the test table and the clustered index on the test table
--=====================================================================================================================
--===== Create the test table
CREATE TABLE dbo.TestTable
(
Client VARCHAR(10)
,SomeAscendingDate DATE
,RowNum INT IDENTITY(1,1)
,OtherColumns CHAR(200) DEFAULT 'X' --Simulates other columns in the table.
)
;
--===== Create the Clustered Index that Scott described.
CREATE CLUSTERED INDEX IXC_TestTable
ON dbo.TestTable (Client,SomeAscendingDate,RowNum)
;
--=====================================================================================================================
-- Run the test. For each day in a given range of dates, insert the Client and the DATE.
-- Not only will the DATE be in ascending order, but we'll also make sure the CLIENT is inserted
-- in ascending order each day just to try to eliminate fragmentation and to prove that it's all
-- for naught.
--=====================================================================================================================
--===== Make sure the test and frag measurement tables are empty at the beginning for repeated runs
TRUNCATE TABLE dbo.TestTable;
TRUNCATE TABLE dbo.FragLevel;
--===== Local variables and presets
DECLARE @Counter INT = 1
,@Days INT = 7
;
WHILE @Counter <= @Days
BEGIN
--===== Add just 1 entry per Client per day
INSERT INTO dbo.TestTable
(Client,SomeAscendingDate)
SELECT Client
,SomeAscendingDate = DATEADD(dd,@Counter,'Jan 2019') --Inherently sorted by the day loop.
FROM dbo.UniqueClientList
ORDER BY Client --Note that we're even inserting in the same order as the leading column of the CI.
;
--===== Measure and save the fragmentation level for this day (counter).
INSERT INTO dbo.FragLevel
(Counter,FragPercent)
SELECT Counter = @Counter
,FragPercent = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.TestTable'),NULL,NULL,'DETAILED')
WHERE index_level = 0 --Limit the return to just the most important level, the Leaf Level
;
--===== Bump the counter
SELECT @Counter += 1
;
END
;
--=====================================================================================================================
-- Let's look at the framentation. Notice that we not only got fragmentation on the first day
-- but the second day went right straight to a massively fragmented state (> 98%).
-- This is because this CI is what is known as a "Sequential Silo" (my name for it)
--=====================================================================================================================
SELECT *
FROM dbo.FragLevel
;
--===== For test cleanup when done.
-- DROP TABLE UniqueClientList,FragLevel,TestTable
Here are the results...
The reason for the massive fragmentation is because there are daily inserts and, although we're inserting in the same order as the Clustered Index keys on a daily basis, we're not inserting in the Clustered Index order on an overall basis.
I'm working on a larger example to demonstrate why you may have missed the fragmentation in the past.
Just following through in kind.