SQL Server Performance

small question regarding indexes and foreign constraint

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Jan 21, 2010.

  1. shankbond New Member

    Hi,
    I have a basic question here:
    I have two tables( dbo.some_table, dbo.some_other_table), there is a columna in dbo.some_table, I have created index on this column by the following statement.
    CREATE CLUSTERED INDEX PK__some_table_columna ON dbo.some_table (columna DESC);
    Now I am creating a foreign key constraint on dbo.some_other_table referencing the columna, but now I cannot get Constraint added
    ALTER TABLE dbo.some_other_table
    ADD CONSTRAINT FK__some_other_table_columnb FOREIGN KEY (columnb) REFERENCES dbo.some_table (columna);
    GO

    I get the following Error:
    Msg 1776, Level 16, State 0, Line 1
    There are no primary or candidate keys in the referenced table 'dbo.some_table
    ' that match the referencing column list in the foreign key 'FK__some_other_table_columnb'.

    But When I use this query to create an index, then the foreign key works with no problems:)
    CREATE UNIQUE CLUSTERED INDEX PK__some_table_columna ON dbo.some_table (columna DESC);
    Can some one Please help me understand the problem?
    Any help shall be appreciated!
  2. preethi Member

    Hi
    You would have seen the work UNIQUE makes the difference.
    I am just sending the reason. I am not sure whether it is logically organized. I am sure there will be some document for you to read.
    In SQLServer you can;t make many to many relationship directly. Only one - to - many and one-to-one relationships are allowed. That means the table should have the key to uniquely identify the row on the primary table.
    Even if you create a clustered index is is not guaranteed unique in SQL Server. When you create a clustered index without the unique clause SQL Server assumes it as non unique and adds an internal id to make it unique.
    Primary keys are guaranteed as unique. (By default they are clustered but not mandatory) Usually relationships are made against primary keys.
    Hope this helps.
  3. FrankKalis Moderator

    To expand on preethi: Usually you would establish a relationship on a PK-FK pair. A PK is by definition unique, so there is no problem creating the FK constraint. A Clustered Index is NOT unique unless you create it with the UNIQUE keyword and because a nonunique Clustered Index does allow duplicates, the FK creation statement complains and fails. Also, a Clustered Index is a physical construct while a PK is a logical one. It just happens to be in SQL Server that a PK is implemented as a unique Clustered Index. Of course, provided that no other Clustered Index exists on that table before. If so, it's "just" a unique nonclustered index.
  4. shankbond New Member

    [quote user="preethi"]Primary keys are guaranteed as unique. (By default they are clustered but not mandatory) Usually relationships are made against primary keys. [/quote]
    Hi,
    Is it true that Constraints (Primary/ Unique) are logical rather than indexes(which are physical)?
    If Yes Does that makes any sense, If I create an index( physical) rather than Primary key Constraint( logical index). In other words will I be gaining some performance benefits?
    Please Reply.
  5. Adriaan New Member

    Both unique constraints and primary keys are created as an index. They can both be satisfy the requirement as the referred key for an FK constraint.
  6. shankbond New Member

    [quote user="Adriaan"]Both unique constraints and primary keys are created as an index[/quote]
    Hi,
    Sorry but I am a little confused now; then what is the point of creating an index, as creating constraints also make an index?
    Please reply!
  7. FrankKalis Moderator

    Caution: At least in SQL Server does creating constraints not alwaysalso result in creating an index under the covers to enforce theconstraint. In your PK-FK example, for instance. When creating theconstraint, SQL Server will not also create an index on the referencingcolumns in the child table. However, in most cases you will want tohave an index on this set of column(s) to improve performance.
    A constraint is something that you "lives" in your logical data model. Say, you determine that the attributes "a, b, c" uniquely identify each single instance in your entity "foo". That may be the reason why you choose the attributes "a, b, c" to be the PRIMARY KEY of the entity "foo". Now, when you physically implement this logical model, you "just" create this PRIMARY KEY constraint and from there on it is up to the database engine how it enforces this constraint. While most systems will create an index to enforce this constraint, they could potentially enforce this constraint in any way they like as long as it is enforced.
    An index on the other hand is a physical object that has no meaning in your logical model. An index is a convenient way to provide a fast access path to the data.
  8. preethi Member

    [quote user="FrankKalis"]Caution: At least in SQL Server does creating constraints not alwaysalso result in creating an index under the covers to enforce theconstraint. In your PK-FK example, for instance. When creating theconstraint, SQL Server will not also create an index on the referencingcolumns in the child table. However, in most cases you will want tohave an index on this set of column(s) to improve performance.[/quote]
    Agreed. I was referring only about unique key and primary key constraints.
    Thanks for the clarification.
  9. shankbond New Member

    [quote user="Adriaan"]Both unique constraints and primary keys are created as an index[/quote]
    Hi,
    Sorry but I am a little confused now; then what is the point of creating an index, as creating constraints also make an index?
    Please reply!
  10. preethi Member

    [quote user="shankbond"]Is it true that Constraints (Primary/ Unique) are logical rather than indexes(which are physical)?[/quote]
    constraint is a logical concept but effectively you do the same thing when you create a constraint and when you create an index. But how you create and index and how you create a constraint are the things which make things different.
    To add/drop a constraint you need to alter the table. But for index, you don't have to alter the table.
    I certain cases after creating the table you may not be able to alter it. If you have a situation, better to go with an index.
  11. moh_hassan20 New Member

    To create a FK that reference field(s) , that field should be UNIQUE: either by creating Primary key or by creating UNIQUE index on that field.
    It doesn't matter the index to be clustered or not.
    just, for info, it is better for clustered index to be ASC for performance, otherwise create unique non clustered index
  12. shankbond New Member

    [quote user="moh_hassan20"]just, for info, it is better for clustered index to be ASC for performance, otherwise create unique non clustered index [/quote]
    Hi,
    I created a DESC index because only recent most data in the table will be used mostly, in my view this will increase the performance gain a little. Can You please explain me what are the Pros and Cons of using ASC/DESC indexes or some reference link. That would be of great help!
  13. moh_hassan20 New Member

    [quote user="shankbond"]what are the Pros and Cons of using ASC/DESC indexes or some reference link.[/quote]
    Excerpts from :http://www.mssqltips.com/tip.asp?tip=1337
    creating an index in ascending or descending order does not make a bigdifference when there is only one column, but when there is a need tosort data in two different directions one column in ascending order andthe other column in descending order the way the index is created doesmake a big difference.

Share This Page