how to find out SQL server index that enforcing a uniqueness constraint

Dear all,

as I know if an index enforcing a uniqueness constraint, I can't remove the index even if it show up as an used index.

how to find that out buy script/tools? the output should include database name and table name, index name and indexed column.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
Are you dealing with a failed transaction, or a direct insert?
In a direct insert, you know the table, using ssms, explore the defined index and use show create index to see if how it is defined.

If this started recently, that means the index was recently added.
The issue might be secondary.

Can you post the SQL error?
arnoldCommented:
Sys.index, sys.index_columns
Is_unique_constraint=1 from the sys.index.
marrowyungSenior Technical architecture (Data)Author Commented:
"Are you dealing with a failed transaction, or a direct insert?"

no., I am thinking something in advance.  as in the last post we discussed, we should not remove a unique index as this might create some business logic problem and therefore I want to search it out and ignore it, I don't want to drop that index.

"Sys.index, sys.index_columns
 Is_unique_constraint=1 from the sys.index. "

so there are 2x system table ? those not DMV
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

arnoldCommented:
Not sure what you mean, if you are altering a schema you have to drop all indexes that are of columns being altered.

I've not look through, but to identify which unique constraint without referring to the error, you may have to know which columns are part of the same index,

Yes, the is_unique_constraint is a column in the sys.index table.
He two tables can be joined on one common column blanking on the column name.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"Not sure what you mean, if you are altering a schema you have to drop all indexes that are of columns being altered."

any script to show out which index is unique index with index name, table name and database name ? I am not going to drop it except programmer said please drop it. this is what I want

I read this : http://www.sqlskills.com/blogs/paul/indexes-from-every-angle-how-can-you-tell-if-an-index-is-being-used/

it said "Here’s the catch: you need to consider an entire business cycle before deciding whether an index can be removed or not. An index may only be used once a month for reporting, or an ‘executive’ query so make sure you cover all possible times that an index may be used. And even if an index isn’t used, make sure it’s not enforcing a uniqueness constraint, as the query optimizer may need the index to exist."

this is exactly what I hear from you all and that's why I want to find the unique index as it can be the business logic it is here to make sure that no duplicate record was inserted and I want to find it out and I don't drop it.
arnoldCommented:
you should not be dropping indexes. This isn't a task to be performed.

I am not sure I understand the scenario you are ....... Considering.

The sequence is analogous/metaphor combination.


An engineer laysout what/where bolts and nuts to be used/placed.
People on the assembly line can during assembly see that there is an unforseen issue requiring additional fasteners omitted/not referenced by the engineer, so the issue is brought up no agreement is reached to add/remedy the situation.
The same people are not nor can not decide that an extra screw/bolt in their opinion serves no purpose and remove or not install it.

Use the query design tool in ssms, add the sys.index from the master db and then add the sys.index_columns
It should auto configure the relationship reference, then look through the information and see if and how to .........

If your firm/developer/architect are you should ..........

I understand your interest, but you seem to try and envision scripts for scenario that is improbable.
I.e, someone tells you to drop an index, unique index is one you wish to isolate, as Scott pointed in the prior question a cluster index is important, will you lso want to check for that?

When one is in search of a solution without a defined problem, one can end up running in circles.

Try the following, find all indexes that are not unique and the columns to which they apply, then try to find unique indexes, clustered, non clustered, etc.
Then randomly pick a column from a table and see whether there is any index or that column.
....

I think this will get you familiar with these tables and their ........
Perhaps to do what you want.
marrowyungSenior Technical architecture (Data)Author Commented:
"you should not be dropping indexes. This isn't a task to be performed.

 I am not sure I understand the scenario you are ....... Considering."

so I should drop no index? I am talking about dropping unused index.

"If your firm/developer/architect are you should .........."

HAHAHHA, I am not !

"will you lso want to check for that?
"

this might be the business logic but I might need to ask any script to show all type of index each table and database are using.

"Try the following, find all indexes that are not unique and the columns to which they apply, then try to find unique indexes, clustered, non clustered, etc.
 Then randomly pick a column from a table and see whether there is any index or that column."

why that complicate....

why not find information of all index, name , type, column,,, then find unique index, clustered, non clustered, and their column information, then try to combine index , and then look at missing index stats and see what else we can add? easier, right?

"When one is in search of a solution without a defined problem, one can end up running in circles."

yeah I knew what you mean but it is not my case, I found this for reference:

http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db

select 
    o.name as ObjectName, 
    i.name as IndexName, 
    i.is_primary_key as [PrimaryKey],
    SUBSTRING(i.[type_desc],0,6) as IndexType,
    i.is_unique as [Unique],
    Columns.[Normal] as IndexColumns,
    Columns.[Included] as IncludedColumns
from sys.indexes i 
join sys.objects o on i.object_id = o.object_id
cross apply
(
    select
        substring
        (
            (
                select ', ' + co.[name]
                from sys.index_columns ic
                join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
                where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 0
                order by ic.key_ordinal
                for xml path('')
            )
            , 3
            , 10000
        )    as [Normal]    
        , substring
        (
            (
                select ', ' + co.[name]
                from sys.index_columns ic
                join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
                where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 1
                order by ic.key_ordinal
                for xml path('')
            )
            , 3
            , 10000
        )    as [Included]    

) Columns
where o.[type] = 'U' --USER_TABLE
order by o.[name], i.[name], i.is_primary_key desc

Open in new window


sth like this but need to check carefully.
arnoldCommented:
Thursday my comment was unclear if you can interact with your db architect, developer to enhance your understanding of the environment and their consideration, thought process etc.


It is not advisable for you to look at whether an index is worthy or not worthy to exist.

It is not complicated.  Look at the query designer, combine the two tables, on a test server if available.

Then try the different categories you mentioned.

You could then look at the example from the external site.
marrowyungSenior Technical architecture (Data)Author Commented:
"Thursday my comment was unclear if you can interact with your db architect, developer to enhance your understanding of the environment and their consideration, thought process etc.


 It is not advisable for you to look at whether an index is worthy or not worthy to exist."

yeah, give the index to them so that they can review if the index is need for their application as mostly it is related to business logic and I can only read the DMV and get advise!

safe to do .... ? use the word advise is very good .
"It is not advisable for you to look at whether an index is worthy or not worthy to exist.
"

we can simply see how often the insert/update/seek has been done on that index, right?

this is a very supportive statement.

"It is not complicated.  Look at the query designer, combine the two tables, on a test server if available."

see execution plan you mean.

"Then try the different categories you mentioned."

what is that mean ?
arnoldCommented:
No, the suggestion deals with query designer and the sys.undex, sys.index_columns and other server tables/views that you can use to diagnose/review the performance of the ........

My tasks are broader than yours, making sure systems are tuned, server roles,etc.

The links you found in the prior question checking system stats to get what the SQL server suggest is a missing index, now this question is from the other point to which your "removal" of index deals with his to identify an index that is a waste of resources.  I do not know how as I've never looked .....nor could given the ...... DBS in use are third party application backend.

An index is not there for insert , update, delete. It's there to speed up data retrieval from the table where the index is defined opined with others as needed..
marrowyungSenior Technical architecture (Data)Author Commented:
I think I know why scott tell me to crearly define the clustered index first:

http://www.sqlskills.com/blogs/paul/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed/

"Heap: A heap does not impose any logical ordering on the records within it, so the only way to find a particular record within a heap is to do a full table scan or to know the physical location of the record (i.e. which record slot on which data page) – called the physical record ID or physical RID. Clearly doing a full table scan every time a query needs to go from a non-clustered index record back to a heap is not feasible, so each non-clustered index record stores the physical RID of the corresponding heap record."

if no clustered index, physical RID lookup can be introduced. which Is an extra lookup!

and logical RID probably means we check/go to the clustered index page via the non clustered  index.
marrowyungSenior Technical architecture (Data)Author Commented:
"An index is not there for insert , update, delete. It's there to speed up data retrieval from the table where the index is defined opined with others as needed.. "

yeah, I knew, that's why if we check the write more than read, we hold the index creation or drop it, right?

"if no clustered index, physical RID lookup can be introduced. which Is an extra lookup!
"

yeah, table scan as no clustered index created.

I am reading this too: http://www.sqlskills.com/blogs/paul/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed/

very useful as this means non clustered index need to reference back to clustered index anyway and then we go to the heap/based table.

"My tasks are broader than yours, making sure systems are tuned, server roles,etc."

all DBA should do this from time to time, yeah it also based on the role/SOW.

"No, the suggestion deals with query designer and the sys.undex, sys.index_columns and other server tables/views that you can use to diagnose/review the performance of the ........"

I am sorry, what suggest you are talking about
?
marrowyungSenior Technical architecture (Data)Author Commented:
anyway to see if the table is mainly on write/update/ or read ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.