September 9, 2013 at 10:16 am
I've been looking into Change Data Capture and it doesn't even look like a Primary Key gets created for the system table that's written to. Can you add an index to system tables? I've never even had to try this before, but now I'm curious.
September 9, 2013 at 11:02 am
firstly why you need to create index on system tables for CDC , it may not be relevant .
Secondly yes you can create index on system tables
September 9, 2013 at 11:18 am
No, you cannot create indexes on system tables. Are the CDC tables system tables though?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2013 at 11:52 am
Hey Gail, yeah, the tables that CDC creates and writes to are system tables.
September 9, 2013 at 11:55 am
They have the type 'S' in sys.objects?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2018 at 4:52 am
So... 5 years later.
No, Although the tables are created by CDC they are not system tables(They are 'u' in sys.tables). The tables are simply in a CDC schema. I have built a test project and added a non clustered index the normal way. This being said, my out of the box the 'Generated table' did have a clustered and non-clustered index before I got there to add my own one.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply