Database

Difference between UNIQUE constraint and PRIMARY key

1) By default Primary Key will generate Clustured Index
whereas Unique Key will Generate Non-Clustured Index.

2) Primary Key is a combination of Unique and NOT NULL Constraints so it can’t
have duplicate values or any NUll
Whereas for Oracle UNIQUE Key can have any number of NULL whereas for SQL
Server It can have only one NULL

A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table.

When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

* The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.

Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.

Difference Clustered and Nonclustered indexes

Clustered

·         Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.

·         The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

Nonclustered

·         Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.

·         The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

·         You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries. 

How to find duplicate records in a table
select name, count(name) as times from contacts group by name, phone having times>1;

Explain the difference between a hot backup and a cold backup and the benefits associated with each?


Cold Backup- We can take the Backup while DB(eg. Oracle) is down.
Hot Backup-We can take the Backup while DB(eg. Oracle) is running.

Cold backup is a physicalbackup. During a cold backupthe databaseis closed and not available to users. All files of the databaseare copied (image copy). The datafiles do not change during the copy so the databaseis in sync upon restore.
Used when:Service level allows for some down time for backup
Hot backup is a physical backup. In a hot backup the database remains open and available to users. All files of the database are copied (image copy). There may be changes to the database as the copy is made and so all log files of changes being made during the backupmust be saved too. Upon a restore, the changes in the log files are reapplied to bring the databasein sync. Used when:A full backupof a databaseis needed
Service level allows no down time for the backup


Difference between TRUNCATE, DELETE and DROP commands
DELETE
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
DELETE FROM emp WHERE name = 'CLERK';

TRUNCATE
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
TRUNCATE TABLE emp;

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
DROP TABLE emp;