The following are some common interview questions that you are likely to encounter in every interview. I have covered all the main points in these questions. These questions are encountered by beginner and experienced developers in an interview. I hope this will help to clarify things for you for interviews.
 
Here we go.
 
What is the difference between “Stored Procedure” and “Function”?

1. A procedure can have both input and output parameters, but a function can only have input parameters.
2. Inside a procedure we can use DML (INSERT/UPDATE/DELETE) statements. But inside a function we can't use DML statements.
3. We can't utilize a Stored Procedure in a Select statement. But we can use a function in a Select statement.
4. We can use a Try-Catch Block in a Stored Procedure but inside a function we can't use a Try-Catch block.
5. We can use transaction management in a procedure but we can't in a function.
6. We can't join a Stored Procedure but we can join functions.
7. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section. But we can use a function anywhere.
8. A procedure can return 0 or n values (max 1024). But a function can return only 1 value that is mandatory.
9. A procedure can't be called from a function but we can call a function from a procedure.

What is difference between “Clustered Index” and “Non Clustered Index”?

1. A Clustered Index physically stores the data of the table in the order of the keys values and the data is resorted every time whenever a new value is inserted or a value is updated in the column on which it is defined, whereas a non-clustered index creates a separate list of key values (or creates a table of pointers) that points towards the location of the data in the data pages.
2. A Clustered Index requires no separate storage than the table storage. It forces the rows to be stored sorted on the index key whereas a non-clustered index requires separate storage than the table storage to store the index information.
3. A table with a Clustered Index is called a Clustered Table. Its rows are stored in a B-Tree structure sorted whereas a table without any clustered indexes is called a non-clustered table. Its rows are stored in a heap structure unsorted.
4. The default index is created as part of the primary key column as a Clustered Index.
5. In a Clustered Index, the leaf node contains the actual data whereas in a non-clustered index, the leaf node contains the pointer to the data rows of the table.
6. A Clustered Index always has an Index Id of 1 whereas non-clustered indexes have Index Ids > 1.
7. A Table can have only 1 Clustered Index whereas prior to SQL Server 2008 only 249 non-clustered indexes can be created. With SQL Server 2008 and above 999 non-clustered indexes can be created.
8. A Primary Key constraint creates a Clustered Index by default whereas A Unique Key constraint creates a non-clustered index by default.

What is the difference between the “DELETE” and “TRUNCATE” commands?

1. The DELETE command is used to remove rows from a table based on a WHERE condition whereas TRUNCATE removes all rows from a table.
2. So we can use a where clause with DELETE to filter and delete specific records whereas we cannot use a Where clause with TRUNCATE.
3. DELETE is executed using a row lock, each row in the table is locked for deletion whereas TRUNCATE is executed using a table lock and the entire table is locked for removal of all records.
4. DELETE is a DML command whereas TRUNCATE is a DDL command.
5. DELETE retains the identity of the column value whereas in TRUNCATE, the Identify column is reset to its seed value if the table contains any identity column.
6. To use Delete you need DELETE permission on the table whereas to use Truncate on a table you need at least ALTER permission on the table.
7. DELETE uses more transaction space than the TRUNCATE statement whereas Truncate uses less transaction space than DELETE statement.
8. DELETE can be used with indexed views whereas TRUNCATE cannot be used with indexed views.
9. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row whereas TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
10. Delete activates a trigger because the operation is logged individually whereas TRUNCATE TABLE can't activate a trigger because the operation does not log individual row deletions.

What is the difference between the “WHERE” clause and the “HAVING” clause?

1. WHERE clause can be used with a Select, Update and Delete Statement Clause but the HAVING clause can be used only with a Select statement.
2. We can't use an aggregate functions in the WHERE clause unless it is in a sub-query contained in a HAVING clause whereas we can use an aggregate function in the HAVING clause. We can use a column name in the HAVING clause but the column must be contained in the group by clause.
3. WHERE is used before the GROUP BY clause whereas a HAVING clause is used to impose a condition on the GROUP Function and is used after the GROUP BY clause in the query.
4. A WHERE clause applies to each and every row whereas a HAVING clause applies to summarized rows (summarized with GROUP BY).
5. In the WHERE clause the data that is fetched from memory depending on a condition whereas in HAVING the completed data is first fetched and then separated depending on the condition.

What is the difference between “Primary Key” and “Unique Key”?

1. We can have only one Primary Key in a table whereas we can have more than one Unique Key in a table.
2. The Primary Key cannot have a NULL value whereas a Unique Key may have only one null value.
3. By default, a Primary Key is a Clustered Index whereas by default, a Unique Key is a unique non-clustered index.
4. A Primary Key supports an Auto Increment value whereas a Unique Key doesn't support an Auto Increment value.

What is the difference between a “Local Temporary Table” and “Global Temporary Table”?

1. A Local Temporary Table is created by giving it a prefix of # whereas a Global Temporary Table is created by giving it a prefix of ##.
2. A Local Temporary Table cannot be shared among multiple users whereas a Global Temporary Table can be shared among multiple users.
3. A Local Temporary Table is only available to the current DB connection for the current user and are cleared when the connection is closed whereas a Global Temporary Table is available to any connection once created. They are cleared when the last connection is closed.

RDBMS

Relational Data Base Management Systems (RDBMS), that maintain data records and indices in tables. In a relational database, relationships between data items are expressed by means of tables.

Normalization

In relational database design, the process of organizing data to minimize redundancy is called normalization.

Different Normalization Forms

1NF: Eliminate Repeating Groups

Separate table for each set of related attributes and give each table a Primary key

2NF: Eliminate Redundant Data

If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key

All attributes must be directly dependent on the primary key.

BCNF: Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables

Stored Procedure

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

e.g. sp_helpdb, sp_renamedb etc.

Trigger

A trigger is a SQL procedure that initiates an action when an event occurs (INSERT, DELETE or UPDATE). A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table.

Nested Trigger:

            A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger.

View

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. The results of using a view are not permanently stored in the database. When we update a value in the view it will change the Original table also.

Different Types of Join

Mainly 4 kind of join , Cross Join, Inner Join, Outer Join, Self Join

Cross Join

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. Result set is the number of rows in the first table multiplied by the number of rows in the second table.

Inner Join

A join that displays only the rows that have a match in both joined tables is known as inner Join.

Outer Join

A join that includes rows even if they do not have related rows in the joined table is an Outer Join.

• Left Outer Join: In Left Outer Join all rows in the first table. Unmatched rows in the right table do not appear.
• Right Outer Join:  In Right Outer Join all rows in the second table. Unmatched rows in the left table are not included.
• Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.

Self Join

When one table joins to itself

Index

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. The users cannot see the indexes; they are just used to speed up queries. . Effective indexes are one of the best ways to improve performance in a database application.

Clustered Index

Clustered index is unique for any given table and we have only one clustered index on a table.

Non Clustered Index

Many non Clustered Indexes as we can on database.

Linked Server

With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.

Eg; sp_addlinkedserver, sp_addlinkedsrvlogin

Cursor

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

Collation

Collation refers to a set of rules that determine how data is sorted and compared.

Sub-query

Sub query is a SELECT statement that is nested within another T-SQL statement.

Read more http://soorajknair.com/main-topics-of-sql-server/