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.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is cursors?

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.

In order to work with a cursor we need to perform some steps in the following order:

Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor

What is 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. It is possible to create an index on one or more columns of a table, and each index is given a name. 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. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index.
Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.

What is 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. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
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. A trigger that contains data modification logic within itself is called a nested trigger.

What is 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 accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. 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, sp_depends etc.