Question

What is the difference between a primary key and a unique key?

Answer 1

The primary key and unique key both are essential constraints of the SQL. The main difference among them is that the primary key identifies each record in the table. In contrast, the unique key prevents duplicate entries in a column except for a NULL value. The following comparison chart explains it more clearly:

Primary Key

The primary key act as a unique identifier for each record in the table.

We cannot store NULL values in the primary key column.

We cannot change or delete the primary key column values.

Unique Key

We can store NULL value in the unique key column, but only one NULL is allowed.

The unique key is also a unique identifier for records when the primary key is not present in the table.

We can modify the unique key column values.

Answer 2

Primary key does not allow the null values but unique key allows one null value.

Primary key will create clustered index on column but unique key will create non-clustered index by default.

How can you increase SQL performance?

Following are tips which will increase your SQl performance:-

Every index increases the time takes to perform INSERTS, UPDATES, and DELETES, so the number of indexes should not be too much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.

Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.

Try to create indexes on columns that have integer values rather than character values.

If you create a composite (multi-column) index, the orders of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.

If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns. Create surrogate integer primary key (identity for example) if your table will not have many insert operations.

Clustered indexes are more preferable than nonclustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY. If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.

You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.