In a database, what is the difference betwen a key and an index?

Question

For example, in SQLServer, if I have an index with 'unique' set on it, how is that different from a key?

How do I know when I want to use a Key vs. an Indexed field?

Answer 1

A key uniquely identifies a row in a table. An index is the order of rows based a field in a table. A table can have multiple indexes, because an index can just be a certain order of a set fields the system uses to search on and then looks up the actual row. Technically, there can only be one key the system uses to identify a row. Most of the time, this is also the primary index, but it doesn't have to be.

Answer 2

Unique is a constraint forced on your data, whereas an index will help in ordering your rows physically (clustered index) or logically (non-clustered index) in the database for faster retrieval.

Answer 3

An field which has unique values is, essentially, a key. However, a key is used to uniquely identify a row in a table, while an index is used to sort, or group, the rows in the table. A key should not change once it has been initially set, as it might be referenced to elsewhere in your database. An indexed field, however, can change freely.

Answer 4

Keys are used to maintain data integrity, indexes are used to maintain database performance. Think about whatever problem you're trying to solve and that will lead you in the right direction.

Answer 5

A key should be the business model's unique identifier for the row. A uniquely indexed column could be considered an "alternate key." Generally SQL servers will create a unique index for primary keys automatically.

I should also mention that you can index non-unique columns; indices such as these are used for speeding up queries over the indexed columns.

Answer 6

A key will always have an index behind the scene If you create a Primary Key, SQL Server will create a clustered unique index to support that

Even when you create a unique constraint SQL Server will also create an index

And of course the difference between a unique constraint and a primary key is that the unique constraint allows at least 1 NULL value (1 on sql server more than one on Oracle) You can only have 1 primary key and many (249 on sql server) unique constraints on a table

Answer 7

A key identifies the row stored in the database. An index is a structure like the one at the one at the end of a book. At the end of a book you see several pages with words and where you can find those words. Those pages are an index and the same is the case for a database. The index contains key and their locations. It helps you finding the location of rows. In case of a book the index tells you on which page you can find the word. The database index has the same function.

As many mention the index is implemented with b-trees. However this is only an implementation detail. There are many ways to implement an index.

Answer 8

Key has by default a cluster index in MS SQL Server.

Key is unique value for each row.

Index use to maximize your database performance. You can create indexes according to your requirement. there are different kind of indexes e.g.

• Bitmap index
• Dense index
• Sparse index
• Covering Index

Answer 9

A key (declaration) is a constraint. It prevents rows from being entered into the table if any key data is missing, or if the key data duplicates a row that's already in the table.

An index is a data structure that allows rapid lookup of a row or rows given values for the columns (fields) used in the index. Indexes can be used to speed up certain other kinds of queries as well. For example, a merge join makes use of indexes on the two columns that make up the join condition, if those indexes are both there. Whether your DBMS will do a merge join depends on the query optimizer, the size of the tables, and the presence of the needed indexes.

To confuse the issue a little, some of the literature refers to the columns that an index uses as "index keys". The same literature usually refers to primary keys and foreign keys as "logical keys". That same literature will often refer to keys as logical features of a table, while indexes are called physical features of the table.

Most DBMSes will create an index for you when you declare a primary key. There are two reasons for this behavior. The first is that detecting duplicates without an index takes a long time on a big table. The second is that you will presumably be doing lots of lookups based on the primary key, and those lookups run much faster with an index.

Answer 10

A key is a field or set of fields that uniquely identifies each row. Typically, there will be one key that is designated as the primary key, and nowadays this seems to be one field that is frequently meaningless by itself (much like a page number, which is important only in identifying what's on the page).

It's possible to have a table with a key for another table, which is called a foreign key. For example, in an online store's database, there may be a table for orders, and each order will have an associated customer, so each row in the orders table has a foreign key for the customer table.

An index is a data structure based on one or more fields in a table, which allows rapid access to the table based on those fields. The index may identify individual rows (for example, an index of the customer email address), or may indicate groups (for example, tax-exempt status of customers). It is used much like the index of a book, or like a reverse directory (such as a phone book ordered by telephone number).

A key is mostly a way to think about the database (although it's usually possible to tell the database about keys, so the database can reject rows with duplicate keys, for example). An index is a way to make the database work faster.