SQL Server Answer

SQL Server 2005 Isolation Levels

2010-04-15T14:36:00.000+05:30

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

2010-04-03T17:01:00.003+05:30

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

2010-03-31T15:57:00.000+05:30

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

2010-03-31T15:55:00.000+05:30

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?

2009-12-01T17:38:00.002+05:30

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?

2009-12-01T17:38:00.001+05:30

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?

2009-12-01T17:35:00.004+05:30

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?

2009-12-01T17:35:00.003+05:30

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?

2009-12-01T17:35:00.001+05:30

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?

2009-12-01T17:34:00.002+05:30

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.

What are different normalization forms?

2009-12-01T17:34:00.001+05:30

1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
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
If attributes do not contribute to a description of the key, remove them to a separate table. 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.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

2009-12-01T17:28:00.000+05:30

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.

Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of cursors. Here is an example:

If you have to give a flat hike to your employees using the following criteria:

Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike

In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:

UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END

Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing, check out the 'My code library' section of my site or search for WHILE.

Explian different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?

2009-12-01T17:27:00.001+05:30

Types of backups you can create in SQL Sever 7.0+ are Full database backup, differential database backup, transaction log backup, filegroup backup. Check out the BACKUP and RESTORE commands in SQL Server books online

How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?

2009-12-01T17:26:00.000+05:30

SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal confuguration mode. Check out SQL Server books online for more parameters and their explanations.

Explain CREATE DATABASE syntax

2009-12-01T17:25:00.002+05:30

Many of us are used to craeting databases from the Enterprise Manager or by just issuing the command: CREATE DATABAE MyDB. But what if you have to create a database with two filegroups, one on drive C and the other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%? That's why being a DBA you should be familiar with the CREATE DATABASE syntax. Check out SQL Server books online for more information.

What is a deadlock and what is a live lock? How will you go about resolving deadlocks?

2009-12-01T17:25:00.001+05:30

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.

A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks" in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.

What are constraints? Explain different types of constraints.

2009-12-01T17:24:00.001+05:30

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

For an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"

Whar is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a t

2009-12-01T17:23:00.000+05:30

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

What's the difference between a primary key and a unique key?

2009-12-01T17:22:00.001+05:30

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, where are 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.

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

2009-12-01T17:21:00.000+05:30

What are defaults? Is there a column to which a default can't be bound?

2009-12-01T17:16:00.001+05:30

A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them. See CREATE DEFUALT in books online.

Define candidate key, alternate key, composite key

2009-12-01T17:15:00.001+05:30

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called composite key.

What is Replication? What is the need to have the replication? What are the enhancements made to SQL Server 2005 related to the replication?

2009-12-01T17:10:00.001+05:30

“Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.” In short, replication is all about having multiple copies of the same database. We need replication when we need to distribute data to and from different locations. Generally we have a master copy of data. There will be multiple slaves (Clients) located at various locations which need to be replicated. We use replication for a variety of reasons. Load balancing is sharing the data among a number of servers and distributing the query load. Offline processing is one of the main reasons. In this scenario we need to modify the data on the database that is not connected to the network. The last reason may be to have a back-up to the database in case of failure to the existing database. Let us see the enhancements of SQL server 2005 database related to replication.

· Database Mirroring – Database Mirroring is moving the transactions of database from one SQL Server database to another SQL server database on a different SQL Server.

· Replication Management topology (RMO) – RMO is a new construct in SQL Server 2005. It is a .NET Framework library that provides a set of common language runtime classes for configuring, managing, and scripting replication, and for synchronizing Subscribers.

What is Database Partitioning in SQL Server 2005?

2009-12-01T17:09:00.000+05:30

SQL Server 2005 provides a new capability for the partitioning of tables across file groups in a database. Partitioning a database improves performance and simplifies maintenance. By splitting a large table into smaller, individual tables, queries accessing only a fraction of the data can run faster because there is less data to scan.

Explain the word “federated” in terms of database servers and databases

2009-12-01T17:08:00.000+05:30

The word “federated” in terms of database servers means SQL Server 2005 shares the database processing load across a group of servers by horizontally partitioning the data in a SQL Server database. These servers are managed independently, but cooperate to process the database requests from the applications; such a cooperative group of servers is called a federation. In terms of databases it means the databases can achieve very high levels of performance only if the application sends each SQL statement to the member server that has most of the data required by the statement. This is referred to as collocating the SQL statement with the data required by the statement. Collocating SQL statements with the required data is not a requirement unique to federated servers.