Database Basics-Part I

Primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Primary key doesn't allow NULLs, but unique key allows one NULL only.

From SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL along with (1,0) 

If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.  

 IDENTITY columns and timestamp columns can't have defaults bound to them. 

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.  

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable.  

The level at which a transaction is prepared to accept inconsistent data is termed the isolation level. The isolation level is the degree to which one transaction must be isolated from other transactions. A lower isolation level increases concurrency, but at the expense of data correctness. Conversely, a higher isolation level ensures that data is correct, but can affect concurrency negatively.

1. Read uncommitted (the lowest level). 2. Read committed (SQL Server default level). 3. Repeatable read. 4. Serializable (the highest level).

CREATE INDEX myIndex ON myTable(myColumn)

What type of Index will get created after executing the above statement?

Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.

à 1 Clustered index and 249 non-clustered index per table

Database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints. The sum of the number of all these objects in a database cannot exceed 2,147,483,647.

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. 

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance.