Results 1 to 2 of 2
  1. #1
    Join Date
    May 2010
    Location
    Hyderabad, India
    Posts
    16

    Unanswered: Can We create both clustered and Non clustered indexes on same column

    I have a table EXT_MEEI_SQL with following info

    Column_name Type Length
    ----------- ---- ----------- -
    MEDICAID_ID char 12
    MMS_SEQ_NO int 4
    SUB_SEQ_NO datetime 8
    SQL_LINE1 varchar 255
    SQL_LINE2 varchar 255
    MESQ_ID numeric (IDENTITY) 9

    INDEXES:

    index_name (index_keys) index_description
    ---------- ----------------- ----
    EXTX_MESQ_PRIMARY (MESQ_ID) clustered, unique
    EXTX_MESQ_ID (MESQ_ID) nonclustered
    MEEI_SQL_IDX_MEDMMS (MEDICAID_ID, MMS_SEQ_NO) nonclustered

    while inserting data into SQL table deadlock is occurring..Is it because of these clustered and non clustered index on the same column (MESQ_ID)?
    Last edited by reddy_546; 07-11-10 at 09:13.
    -PavanKumar M Reddy

  2. #2
    Join Date
    Jun 2010
    Posts
    51
    Why do you have clustered and non-clustered on the same column anyway? I don't see any difference in first 2 constraints apart from 1 being clustered and other being non-clustered.

    Also,
    Identity cols are not good candidate for clustered index for various reasons. I suggest you have clustered index on combination of unique col(s) and have non-clustered index MESQ_ID.

    Thanks,
    Meet

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •