Hi all,
Please consider the following table structure.
CREATE TABLE [dbo].[Transactions](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TransactionStatus] [varchar](100) NOT NULL,
[TransactionDate] [datetime2](7) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Txn_Status] ON [dbo].[Transactions]
(
[TransactionStatus] ASC,
[TransactionDate] 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) ON [PRIMARY]
GO
Question 1.
If I had frequent updates to IX_Txn_Status - would this affect the fragmentation level of the clustered index on the table? If it does, what would be the best way to determine the fill-factor for the clustered index? If not, are auto-increment integer clustered indexes should always have a 100% fill-factor?
Question 2.
If I had deletes on the table (on the top of the updates to IX_Txn_status) would that fragment the clustered index and if so, at what extent (let's suppose I have 1 delete for 100 inserts)?
Thank you!!
__________________________
Allzu viel ist ungesund...
Q1) Potentially, if the length of the varchar update was longer than the original status length. For example, if the first status was 'New' but a later status was 'Updated', since the varchar got longer, that could cause fragmentation.
Best would be to encode the Status as a tinyint (1 = New, 2 = Updated, etc.), then that wouldn't be an issue, and it would be far less bytes to store.
Q2) Yes. There will be fragmentation because you will be leaving gaps where rows have been deleted.
The clustering key should be ( TransactionDate, Id ) not just ( Id ). The vast majority of tables would benefit from having a clustered key that is not the identity value. It's a terrible mistake to automatically made ident the clus key.
The id can still be the PK if you like, it should just be nonclustered.
Hopefully you just look for a specific status or two. In that case, a filtered index can provide the status lookup without using up too much space and causing too much fragmentation.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 23, 2020 at 7:41 pm
Thanks for your response.
__________________________
Allzu viel ist ungesund...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply