Data integrity and Constraints :

Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.
The Various Constrains are
1. Primary Key
2. Foreign Key
3. Unique
4. Check
5. Not Null

Identity :

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at
A GUID column also generates numbers, the value of this cannot be controled. Identity/GUID columns do not need to be indexed.

De-normalization :

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

Difference between DELETE and TRUNCATE commands :

* Delete command removes the rows from a table based on the condition that we provide with a WHERE clause.

* Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

Difference between HAVING and WHERE CLAUSE :

* Having Clause is basically used only with the GROUP BY function in a query.

* WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

Difference between clustered and a non-clustered index :

* Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index.

* In Non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

Different index configurations a table :

A table can have one of the following index configurations:
1. No indexes
2. A clustered index
3. A clustered index and many nonclustered indexes
4. A nonclustered indexMany nonclustered indexes

Difference between primary key and Unique key :

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, but unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

Basic functions for master, msdb, model, tempdb databases :

* The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.

*  The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

*  The tempdb holds temporary objects such as global and local temporary tables and stored procedures.

*  The model is essentially a template database used in the creation of any new user database created in the instance.

Join :

Join actually puts data from two or more tables into a single result set

Types of Joins :

There are three types of joins:
1. Inner Join
2. Outer Join
3. Cross Join

Command to rename a db :

sp_renamedb ‘oldname’ , ‘newname’

Stored procedure to add a linked server : 

sp_addlinkedserver, sp_addlinkedsrvlogin