How to create clustered index on TimeStamp column without being primary key?

[osudude]

Would like to create the target serilog table with a clustered index on TimeStamp column but timestamp should not be a primary key as it is not-guaranteed to be unique. How do you do this with serilog code?

Serilog.Sinks.MSSqlServer 5.1.3
.NET Core 2.0

[Roggus-zz]

@osudude you can only have ONE clustered index for a ms sql server table and this clustered index should be based on the table key columns, in this case the ID column. about clustered vs non-clustered index.

However, what I think your looking for is to speed up select from the dbo.log table and that can be made easilly with a non-clustered index. You can do it yourself (if you got the correct permissions on sql server) or contribute to this repo to add it as an option.

Assuming you log table name is dbo.log this is the way to add the index for Timestamp column

CREATE NONCLUSTERED INDEX [NCIX_Log_Timestamp] ON [dbo].[Log]( [TimeStamp] ASC )
WITH 
(
	PAD_INDEX = OFF, 
	STATISTICS_NORECOMPUTE = OFF, 
	SORT_IN_TEMPDB = OFF, 
	DROP_EXISTING = OFF, 
	ONLINE = OFF, 
	ALLOW_ROW_LOCKS = ON, 
	ALLOW_PAGE_LOCKS = ON, 
	FILLFACTOR = 80
)
GO

Basicly everything in WITH statement is default except fill factor that should be set to higher than default value. This handle the using of page when storing data in the index.

Also, if your table is large today creating a new index on it might be time consuming and performence heavy. If you get stuck on this, please let me know and I can give you some tips on how to proceed with re-creating the table etc.

I use a non-clustered index on level column to easilly filter out Information to view only errors, concider adding this as well if you think this will be useful.

I would love having creating these or any of these index as an option when creating the table with autoCreateSqlTable set to true. It is so much easier to add index when table is empty then when filled with milions of records!

[osudude]

Thanks for the tips..... In my case it would be nice to have the clustered index on TimeStamp / AppName (custom column) since the majority of the time the rows will be queried by these two columns. The stock ID column is not helpful in my case as I have another custom column that is a sequential guid that is generated by the app to serve as a unique identifier.

[Roggus-zz]

You can of course have Timestamp as clustered index, but then you would end up with a larger size of your index since a datetime type uses more bytes to store data then an INT data type. 8 bytes instead of 4 for INT.

Clustered index are also always referenced in other non-clustered index to point to the actual row id (internal row id in storage engine).

Wheater its a clustered or non-clustered index has nothing with actual performence to do in query performence as long as you create smart index. If you feel you need to query fast on timestamp, like WHERE Timestamp > '2019-01-01' etc, a non-clustered index on that column will be as fast a clustered index. However, if you use Timestamp as column for your clustered index, it might take longer time to read this index since it will be larger in size by default. However it might be ms in difference. Depence also on log table size, and average row size etc.

I would recommend always use INT or BIGINT to primary key column, and as a rule create your clustered index on your primary key, GUID as primary key is even worst, because it´s larger, much larger then both INT and datetime data type. GUID is 16 bytes per row, instead of INT thats 4 bytes. When your clustered index is done, add index to other columns that you know is going to be queried and used in WHERE statments, like Timestamp, Level, mayby your Application name column etc.

We also has some GUID primary key in some legacy system and are seeing big difference between querying these against newer system where we choice INT datatype.

You can of cource have GUID column in your table, but not to use it as primary key. Yes, your are violating Codd, but it´s worth it.

[ckadluba]

When letting MSSqlServer sink create the schema for you (autoCreateSqlTable = true), there is no way to create a clustered index for a non-primary key column. If you need that, you have to create your table in another way.