SQL Server 2005 Isolation Levels

1) Read Uncommited
2) Read commited with locking
3) Read commited with snapshot
4) Repeatable Read
5) Snapshot
6) Serializable

Difference Between varchar and nvarchar

Varchar means Variable-length Character string. Nvarchar will store Unicode characters. Both will be used all most for the same purpose but with little difference. Varchar will store the 8-bit data in database where as Nvarchar will be stored as 16-bit data in Database.
In Sql server 2005

The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.
Varchar (MAX) and Nvarchar (MAX). Varchar (MAX) can hold max of 2,147,483,648 characters and NVARCHAR (MAX) can hold 1,073,741,823 characters. We use Varchar instead of TEXT and NTEXT which cannot be used to passed as variables in a stored procedure where as Varchar (MAX) or Nvarchar (MAX) cannot have such restriction.
If you're in the process of migrating an existing data design for SQL Server 2005, it might make sense to migrate some TEXT / NTEXT fields to VARCHAR (MAX) / NVARCHAR (MAX) types when appropriate.

If you need Unicode support for a given data type, either now or soon enough, go with NVARCHAR. If you're sticking with 8-bit data for design or storage reasons, go with VARCHAR. Note that you can always migrate from VARCHAR to NVARCHAR at the cost of some room -- but you can't go the other way 'round. Also, because NVARCHAR involves fetching that much more data, it may prove to be slower depending on how many table pages must be retrieved for any given operation.

varchar uses 8 bits per character. nvarchar is UNICODE, a double-byte character set, requiring 16 bits to store each character. UNICODE can handle languages that are difficult to impossible with single-byte
character sets such as ASCII. If you must support many languages UNICODE is a very clean way to do it.

How to Create a temporary table

Creating a temporary table is virtually the same as creating a normal table. The main exception is the naming of the table. A hash ('#') character as the first character in the table name denotes that it is a temporary table.
There are two types of temporary tables, local and global.
A local temporary table has a single hash ('#') at the start of its name. A local temporary table is visible only to the user who created it and is destroyed automatically when that user disconnects.
A global temporary table is denoted by a name starting with two hashes (i.e. '##'). A global temporary table is visible to all users and is deleted automatically when the last user who has referenced the table disconnects.
An example of creating a local temporary table:

create table #foo
(
    CarIndex  smallint,
    CarType   varchar(20)
)

An example of creating a global temporary table:

create table ##baz
(
    CarIndex  smallint,
    CarType   varchar(20)
)

INDEX in Sql Server 2005

Indexes may be either Clustered or Non-Clustered

Clustered Index 

Every table can have one and only Clustered Index because index is built on unique key columns and the key values in data rows is unique. It stores the data rows in table based on its key values. Table having clustered index also called as clustered table.

Non-Clustered Index

It has structure different from the data rows. Key value of non clustered index is used for pointing data rows containing key values. This value is known as row locator. Type of storage of data pages determines the structure of this row Locator. Row locator becomes pointer if these data pages stored as a heap. As well as row locator becomes a clustered index key if data page is stored in clustered table.

Both of these may be unique. Wherever we make changes to the data table, managing of indexes is done automatically.

SQL Server allows us to add non-key column at the leaf node of the non clustered index by passing current index key limit and to execute fully covered index query.

Automatic index is created wherever we create primary key, unique key constraints to table.

The Query Optimizer

Query Optimizer indexes to reduce operations of disk input-output and using of system resources when we fire query on data. Data manipulation Query statements (like SELECT, DELETE OR UPDATE) need indexes for maximization of the performance. When Query fires the most efficient method for retrieval of the data is evaluated among available methods. It uses table scans or index scans.

Table scans uses many Input-output operations, it also uses large number of resources as all rows from the table are scanned.

Index scan used for searching index key columns to find storage location.
The index containing fewer columns results in to faster query execution and vice-versa.

Creating an Index

1. Connect to Northwind database from Object Explorer, right click on the Customers table to create an index and click on modify.
 2. Click on Index/Keys from Table Desinger Menu on top or right click on any column and click on Index/Keys.
3. Click on Add from Indexes/Keys dialog box.
4. From Selected Primary/Unique Key or Index list, select the new index and set properties for the index in the grid on right hand side.

• Now just specify other settings if any for the index and click Close.
• When we save the table, the index is created in the database.

Creating Unique Index

SQL Server permits us to create Unique Indexes on columns which are unique to identify. (like employee’s Reference ID, Email-id etc.) We use set of columns to create unique index.

• Right-click on the Customers and click Modify in Object Explorer.
• Now, click on Indexes/Keys from Table Designer menu and click on Click Add.
• The Selected Primary/Unique Key or Index list displays the automatically generated name of the new index.  
• In the grid, click on Type, from the drop-down list, Choose Index.
• Under Column name,we can choose columns we want to index and click on OK. Maximum we can setup 16 columns. For optimum performance, it is recommended that we use one or two columns per index. For every column we values of these columns are arranged in ascending or descending order.  
• In the grid, click Is Unique and select select Yes.

• Null is treated as duplicate values. So, it is not possible to create unique index on one column if it contains null in more than one row. Likewise index cannot be created on multiple columns if those columns contains null in same row.
• Now select Ignore duplicate keys option. If it is required to ignore new or updated data that will lead to creation of duplicate key in the index (with the INSERT or UPDATE statement).
• When we save the table, the index is created in the database.

Creating Clustered Index

table can have only one clustered index. In Clustered index logical order and physical order of the index key values is identical of rows in the table.

• In the Object Explorer click on the Northwind database, right click on the Customres to create an index and click on modify.
• Now we have Table Designer for the table.
• From the Table Designer menu, click Indexes/Keys and from Indexes/Keys dialog box, click Add. 
• Now from Selected Primary/Unique Key or Index list, Select the new index
• In the grid, select Create as Clustered, and choose Yes from the drop-down list to the right of the property. 
• When we save the table, the index is created in the database.