Results 1 to 4 of 4
  1. #1
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48

    What is the difference between unique key and index in SQL Server?

    Hi everyone,

    we know that creating indexes are increasing the searching and query performances.
    Depend on that I have two questions;

    1- In SQL Server there is two options, As you can see in attachment I put,
    After we set the unique property of a field "Yes", from the type, are we have two options which are "Unique Key" and "index".
    Which one should be choosen to increase the performance? Is there any differences?


    Because I did not understand, why there is separate option to choose Unique Key, after we have already set "Yes" for unique property?

    Attached Thumbnails Attached Thumbnails indexquestion.png  

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,065
    A primary key is not always a single field, it can be a composite key across two or more fields..
    An index doesn't have to be on a single field, and an index doesn't have to be unique.

    An index won't always improve performance!
    Have a read here https://www.access-programmers.co.uk...ssues.291269/# for a detailed performance discussion

    Because of all of the above it gives you the options.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Thanks Minty. I got it. I check the link

  4. #4
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    818
    A primary key in SQL server is a clustered index, meaning the records in the table are physically stored as the index dictates. So, there can only be one clustered index on a table. A nonclustered index is a pointer to the data, and the key for the index can be one or more columns with either textual or numeric data. On a related note, it’s beneficial to keep the clustered index as narrow as possible (1 column) because it is used by every other index.
    For a query, ideal would be to include as many columns as are used in the query (= covering index), but this would mean a lot of overhead. Therefore you can use Included columns to a nonclustered index . This adds additional columns that aren’t part of the nonclustered index key but are returned with the index to fulfill query needs without an additional lookup.
    Another thing you need to consider is the fill-factor: how much initial extra space are you going to give on each index page. For databases that get frequent updates/insert you need to leave more space (fill factor of 60 to 70 percent). This way there won't be as much page splits when new records are added, but the index grows larger. For databases that are mere lookup databases, you can take a fillfactor of 90 or more. This will give a smaller number of pages.

    see https://www.giantstride.gr/sql-indexing-part2/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-02-2014, 05:59 PM
  2. Replies: 2
    Last Post: 12-01-2012, 07:10 AM
  3. Replies: 1
    Last Post: 08-19-2012, 09:55 PM
  4. Creating a unique index on two fields
    By Paul H in forum Database Design
    Replies: 3
    Last Post: 11-03-2011, 10:15 AM
  5. Eerror No unique index found
    By newtoAccess in forum Database Design
    Replies: 4
    Last Post: 12-10-2010, 08:30 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums