Is there a way to check whether my nonclustered indexes are rebuilt while I rebuild my clustered index

udhayan d 181 Reputation points
2023-01-31T13:30:36.9866667+00:00

Hi There,

Is there a way to check whether my nonclustered indexes are rebuilt or not while I rebuild my clustered index.

I just cant rely on create date and modified date in sys.all_objects.

I am using create unique clustered index..with (drop_existing=ON) with the same clustering key and theoretically, it should not rebuild the non clustered indexes, but I just want to confirm that in lower environment and I could not find a way to validate it.

After rebuilding the clustered index, modified date of all the non clustered index shows the same datetime stamp as clustered index

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,481 questions
0 comments No comments
{count} votes

Accepted answer
  1. Zahid Butt 556 Reputation points
    2023-01-31T16:11:44.9066667+00:00

    Hi Udhayan,

    There is no way to get modification date for non clustered index.

    sys.all_objects does not show create or modify date for non clustered index.

    sysobjects shows creation date for all indexes but not modification date.

    You can do this by implementing trigger. Please check for help https://littlekendra.com/2016/12/13/index-create-and-last-modified-dates-in-sql-server/

    Regards,

    Zahid


1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 100.1K Reputation points MVP
    2023-01-31T22:36:51.09+00:00

    You could run sys.db_db_index_physical_stats on your indexes before and after running th CREATE INDEX statement for the clustered index. If the output changes, the non-clustered indexes were indeed rebuild.

    2 people found this answer helpful.