Using isnull in where clause is expensive in SQL Server

The statement was similar to this :
(This is just an example)

Select * from CustomerMaster where StateId = isnull(@StateID, [StateId]);

After replacing isnull with ‘Case’  like below, the statement executed faster and the stored procedure ran in few milli seconds.

Select * from CustomerMaster where StateId = (Case @StateID when null then [StateId] else @StateID End);

Assembly

The IL code and resources, such as bitmaps and strings, are stored on disk in an executable file called an assembly

declare bool value in c# .Net

bool val = true;

How many Clustered Index can be created on a single table?

Only one

How many Non Clustered Index can be created on a single table?

So the correct answer is you can have upto 999 non clustered index per table from SQL Server 2008 onwards but in pervious versions this was limited to 249.

Explain different constraints to maintain data integrity in SQL Server?

Check constraints:
Check constraints will be useful to limit the range of possible values in a column.
We could create check constraints at two different levels
a) Column-level check constraints are applied only to the column and cannot reference data in another other column
b) Table-level check constraints can reference any column within a table but cannot reference columns in other tables
Default constraints:
Default constraints enable the SQL Server to write default value to a column when user doesn’t specify a value.
Unique constraints:
A unique constraint restricts a column or combination of columns from allowing duplicate values.
Primary key constraints:
Primary key constraints will allow a row to be uniquely identified. This will perform by primary key on the table.
Foreign key constraints:
Foreign keys constraints will ensure that the values that can be entered in a particular column exist in a specified table.

Indexes in SQL Server

What is Index?
Indexes are database objects designed to improve query performance.
By applying indexes to one or more columns in table or views, we could see faster data retrieval from these tables.
Explain the structure of Index in SQL server?
An index is structured by the SQL Server Index Manager as a balanced tree (or Btree). A B-tree is similar to an upside-down tree, means with the root of the tree at the top, the leaf levels at the bottom, and intermediate levels in between.
Each object in the tree structure is a group of sorted index keys called an index page.
All search requests begin at the root of a B-tree and then move through the tree to the appropriate leaf level.
What are the different types of indexes in SQL Server?
There are two types of indexes
• Clustered index
• Non Clustered index
Both types of indexes are indexes are structured as B-Trees.
Explain the difference between clustered index and non clustered index?
Clustered index:

• A clustered index contains table records in the leaf level of the B-tree.
• There can be only one clustered index on a table or view, because the clustered index key physically sorts the table or view.

Non Clustered index:

• A non clustered index contains a bookmark to the table records in the leaf level.
• If a clustered index exists on a table, a non clustered index uses it to facilitate data lookup.
• We could create 249 non clustered indexes on a single table.