Working with Indexes on SSMS

Question

Working with Indexes on SSMS

sabyda

Old Hand

Points: 344
More actions
August 7, 2025 at 5:00 am

#4631856

Comments posted to this topic are about the item Working with Indexes on SSMS
Sabyasachi Mukherjee

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thomas Franz Hall of Fame Points: 3936 More actions · Answer 1

Seems to be an article based primary on the output of an AI - and even the caption of the article is wrong, since SSMS is just the GUI but the indexes are in/on a SQL Server database.

Lots of very low level informations with even more missing "it depends" or when I should use it.

Nothing against articles for beginners, but I doubt, that a beginner knows what a spatial column is or when / how to use a full text index :-). And the unreflected "rebuild your indexes regularly" may cause more problems than it fixes in the reality (there are lots of articles, why unreflected rebuilds are not the best idea with todays SSDs).

God is real, unless declared integer.

Mr. Brian Gale SSC-Insane Points: 24928 More actions · Answer 2

To add to what Thomas Franz said, there are also risks with indexes. In some cases, indexes can hurt SELECT performance. Also, the different types of indexes is a bit misleading. SQL Server, to the best of my knowledge, only has clustered or nonclustered indexes and most of the others (Unique, composite, filtered, not sure on XML, spatial, or full text) are just types of clustered/nonclustered indexes. For that matter, a clustered index is also unique by definition although if you don't include a unique column, a hidden uniquifier column is added automatically.

There is also a typo where it says "creating a nun-clustered index" and I'm going to remember that forever now. It's stuck in my brain.

I would also be cautious using the database tuning advisor for index creation. It can help, but it can also make a mess.

Also, filtered indexes should be used with caution - CRUD operations can result in scans of a filtered index even if the filter should exclude that CRUD operation from touching the index.

Also, you should be looking at execution plans for if the index is in use and if it is a good index. If you have key lookups (for example), you don't have a covering index.

Another fun thing is that an index is a copy of the database table on disk. So adding an index onto a 1 GB table means you now have a 2 GB table.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

AlexPixley Newbie Points: 3 More actions · Answer 3

Yeah, I hate typos like that. It oviously should have been "creating a nun-cloistered index".

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 4

Mr. Brian Gale wrote:

In some cases, indexes can hurt SELECT performance. .

Do you have a link on that subject?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)