Poor Index Naming Convention

[bruce-dunwiddie]

This may seem silly, but indexes that were created to support foreign key constraints seem to have been named in this database with an FK_* prefix, which I do not think sets a good example for a preferred naming convention.

Example:

CREATE NONCLUSTERED INDEX [FK_Application_Cities_StateProvinceID] ON [Application].Cities
(
[StateProvinceID] ASC
);

This naming convention is normally used exclusively for foreign key constraints. This naming convention is also used for foreign key constraints in this database.

I would suggest renaming these indexes to follow an IX_* prefix naming convention.

So many people use these sample databases as a "Microsoft best practices", that I would like to prevent having to argue and fix these things in the field down the road in customer created databases.

References for preferred naming standards:

http://sqlmag.com/blog/sql-server-index-naming-guidelines

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/431e5cf1-56b2-4a03-a7d9-183891973946/what-should-be-the-naming-convention-for-indexes?forum=transactsql

http://stackoverflow.com/questions/2783495/sql-server-index-naming-conventions

http://blog.sqlauthority.com/2012/05/21/sql-server-renaming-index-index-naming-conventions/

[bruce-dunwiddie]

There are 63 occurrences of this in the WideWorldImporters Sample Database.

[jodebrui]

Hi Bruce,

Thanks for the feedback.

There are different schools of thought on how to name indexes – there is not a single best practice that works for everyone. As the documentation of the sample mentions, it is designed using one set of guidelines, it is not the only way you can build a database.
If we change the naming of the index to IX_* then folks used to FK_* (to align with key names) would object.

The person who created the sample database is an experienced database developer. Their practice is to always create an index for a foreign key, and align with the name of the key, in the same way that indexes supporting primary key and unique constraints are aligned with the constraints.
As was also pointed out by one of the respondents for the stackoverflow thread you quote, one of the benefits is that you can easily find missing indexes for your foreign keys.

I realize that different folks follow different best practices, and unfortunately we cannot accommodate all in the sample DB.

Thanks,
Jos

From: Bruce Dunwiddie [mailto:notifications@github.com]
Sent: Friday, July 15, 2016 9:29 AM
To: Microsoft/sql-server-samples sql-server-samples@noreply.github.com
Subject: [Microsoft/sql-server-samples] Poor Index Naming Convention (#71)

This may seem silly, but indexes that were created to support foreign key constraints seem to have been named in this database with an FK_* prefix, which I do not think sets a good example for a preferred naming convention.

Example:

CREATE NONCLUSTERED INDEX [FK_Application_Cities_StateProvinceID] ON [Application].Cities
(
[StateProvinceID] ASC
);

This naming convention is normally used exclusively for foreign key constraints. This naming convention is also used for foreign key constraints in this database.

I would suggest renaming these indexes to follow an IX_* prefix naming convention.

So many people use these sample databases as a "Microsoft best practices", that I would like to prevent having to argue and fix these things in the field down the road in customer created databases.

References for preferred naming standards:

http://sqlmag.com/blog/sql-server-index-naming-guidelineshttps://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fsqlmag.com%2fblog%2fsql-server-index-naming-guidelines&data=01%7c01%7cjodebrui%40microsoft.com%7c5c6a526956f548fdb59908d3accd1274%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=13Gj5ywL2CBAruOVU3Ku%2bA6UJnzNowWE9OvNAyasje4%3d

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/431e5cf1-56b2-4a03-a7d9-183891973946/what-should-be-the-naming-convention-for-indexes?forum=transactsqlhttps://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fsocial.msdn.microsoft.com%2fForums%2fsqlserver%2fen-US%2f431e5cf1-56b2-4a03-a7d9-183891973946%2fwhat-should-be-the-naming-convention-for-indexes%3fforum%3dtransactsql&data=01%7c01%7cjodebrui%40microsoft.com%7c5c6a526956f548fdb59908d3accd1274%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=UnyD88KGEYPMaM4pFFwDfergbUP2LSIxkSjt1qEILAw%3d

http://stackoverflow.com/questions/2783495/sql-server-index-naming-conventionshttps://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fstackoverflow.com%2fquestions%2f2783495%2fsql-server-index-naming-conventions&data=01%7c01%7cjodebrui%40microsoft.com%7c5c6a526956f548fdb59908d3accd1274%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=HIqOS7lRnvhhgDIRz7nQx82suSwv%2fEFszRIZimoSb84%3d

http://blog.sqlauthority.com/2012/05/21/sql-server-renaming-index-index-naming-conventions/https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fblog.sqlauthority.com%2f2012%2f05%2f21%2fsql-server-renaming-index-index-naming-conventions%2f&data=01%7c01%7cjodebrui%40microsoft.com%7c5c6a526956f548fdb59908d3accd1274%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=mLFf0%2b4tSLCmP3hifbL2eupeWRAWL9PIS%2fCnPm5DIhY%3d

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHubhttps://github.com//issues/71, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AMuA9gLai5yLhzS_xdRyMsf70V-1Ggdcks5qV7UwgaJpZM4JNkjM.