Could the creation of an index break table dependencies?

Question

I ran a query using the sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats to determine what are my options for missing indexes. After analyzing the data I determined that I needed to create a non-clustered index for Table A.

Looking at Table A, I noted that there are 55 dependencies (Tables, SPs, Views) and 23 objects that are dependent on this table. My question is, if i create this non-clustered index on this table does it affect the dependencies and dependents of this table?

Moreover, if yes it affects the dependencies, how do I even attempt fix the dependencies?

Answer 1

Among the Available index types I could see only one kind of index that would be able to cause an error on an object that depends on the table and it's the Unique index.

According to the Unique Index Design Guidelines:

A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique.

It's an index that comes with a constraint that ensures the values are unique. Apart from that very specific situation, you don't have to worry about breaking a dependency for, as the doc says,

A nonclustered index is an index structure separate from the data stored in a table that reorders one or more selected columns. (emphasis mine)

As you can see, the nonclustered index is separeted from the table and doesn't alter the structure of the table itself, therefore, anything that uses that table shall not be negatively affected the way you're afraid of.

Here's a good start point: SQL Server Index Architecture and Design Guide

---

Lab: causing error on table dependency by creating an index

First, create a table that will have an object that depends on it:

CREATE TABLE MyTable(
    UserID INT NOT NULL IDENTITY PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Surname VARCHAR(50) NOT NULL
)

Now, crete an SP that is dependent on that table:

CREATE PROCEDURE InsertUser
@Name VARCHAR(50),
@Surname VARCHAR(50)
AS
BEGIN 
    INSERT INTO MyTable(Name, Surname)
    VALUES(@Name, @Surname);
END

Let's insert the user Paul Smith:

EXEC InsertUser 'Paul', 'Smith';

The procedure executes fine. Paul is a very common name and as it is now you could insert as many users named Paul as you wished. But you decided to create a Unique Index on the column Name:

CREATE UNIQUE INDEX AK_MyTable_Name ON MyTable (Name);

After that, you try to insert another user named Paul Jones:

EXEC InsertUser 'Paul', 'Jones';

But now you get an error:

Msg 2601, Level 14, State 1, Procedure InsertUser, Line 6 [Batch Start Line 17] Cannot insert duplicate key row in object 'dbo.MyTable' with unique index 'AK_MyTable_Name'. The duplicate key value is (Paul). The statement has been terminated.

The SP that depended on the table is now broken. That's the only situation I can imagine you could break an object that depends on a table by the creation of an index. If you're not creating a Unique Index now, you can relax and watch the performance of your new indexes.

Answer 2

When implementing filtered indexes, certain connection settings must be correct:

Otherwise

The Database Engine generates an error and rolls back INSERT, UPDATE, DELETE, or MERGE statements that change data in the index.

So if any of the dependent code does not have the above SET options correctly configured, adding a filtered index will break them. I unfortunately have had to learn the hard way to double check this when working on old systems with questionable stored procedure code :)

Answer 3

The very simple answer is no. However, before you create an index you must understand the workload, how many indexes are presently on the table, whether you can fulfill the new index my modifying an existing index etc.

Never create an index based on SQL Server missing index recommendations https://www.brentozar.com/archive/2017/08/missing-index-recommendations-arent-perfect/

As I mentioned in paragraph 1, you need to understand your workload and the benefit of the new index by analysing usage stats.