Truncate Database/ Delete data from all tables of database

-- disable all constraints
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- delete data in all tables
EXEC sp_MSForEachTable 'DELETE FROM ?'

-- enable all constraints
exec sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

Taking a SQL Server Database Off-Line/ON-Line

Sometimes you will want to remove all access to a database for a period of time without detaching the database or deleting it. One option to achieve this is to take the database off-line. You can do this using standard SQL Server tools or Transact-SQL.
Using SQL Server Tools

The easiest way to take a SQL Server database off-line is using the graphical user interface tools provided. If you are using either SQL Server Management Studio or Enterprise Manager, you can simply right-click the name of a database. This causes a context-sensitive menu to appear. Within the "All Tasks" or "Tasks" submenu is the "Take Offline" option. Clicking this option takes the database off-line. You can return the database to an operational status by selecting "Bring Online" from the same menu.
ALTER DATABASE Command

The preferred manner in which to take a database off-line using Transact-SQL statements is with the ALTER DATABASE command. This command allows database administrators to modify database settings, files and filegroups.
Taking a Database Off-Line

The T-SQL for taking a database off-line is similar to that for setting restricted access mode. The simplest variation of the command is the following statement:
ALTER DATABASE database-name SET OFFLINE

The above command attempts to take the named database off-line immediately. If a user or a background process is currently connected to the database the command cannot be completed. In this situation, the ALTER DATABASE statement will be blocked and will wait until all connections are closed. This ensures that no transactions are rolled back unexpectedly. During the period of blocking, no new connections to the database will be permitted.

NB: Background processes that are running against SQL Server may cause the command to be indefinitely blocked, as can internal processes. The AUTO_UPDATE_STATISTICS_ASYNC in particular should be checked. If set to ON, the statement will be blocked.
Failing When Blocked

If you run the ALTER DATABASE command whilst users or processes may be connected, but you do not wish the command to be blocked, you can execute the statement with the NO_WAIT option. This causes the command to fail with an error.
ALTER DATABASE database-name SET OFFLINE WITH NO_WAIT
Forcing Disconnection of Users and Processes

In some cases it is appropriate to run the ALTER DATABASE command and allow it to be blocked until all of the current connections are closed. For SQL Servers that are used only internally to an organisation, each user can be contacted and asked to disconnect. Once they have, the command will complete automatically and the database will be inaccessible.

In other situations, for example a publicly accessible web site database, it may not be possible to ask users to disconnect. In these cases you may automatically disconnect users and to roll back their active transactions. This is achieved using the ROLLBACK clause.

The ROLLBACK clause can be used to immediately disconnect users or can be provided with a number of seconds to pause before taking the database off-line. To force the immediate disconnection of general users and processes and the rolling back of their transactions, use the following command:
ALTER DATABASE database-name SET OFFLINE WITH ROLLBACK IMMEDIATE

If desired, you can specify that there should be a pause before disconnecting the users. During this period any attempts to connect to the database are refused but existing connections are given a number of seconds to complete their processing. The following example statement pauses for two minutes before disconnecting users:
ALTER DATABASE database-name SET OFFLINE WITH ROLLBACK AFTER 120 SECONDS

Bringing a Database On-Line

Once you have completed any maintenance work and want to resume the use of the database, you can bring it back on-line using the ALTER DATABASE command:
ALTER DATABASE database-name SET ONLINE

Multiple Active Result Sets (MARS) in SQL Server 2005

MARS is newly added future in SQL 2005. In earlier version from sql2005 user are not allowed to run more than one SQL batch on an open connection at the same time but sql2005 allows the user to run more than one SQL batch on an open connection at the same time.
private void MARS_Off()
{
SqlConnection conn = new SqlConnection("Server=serverName;
Database=adventureworks;Trusted_Connection=yes;");

string sql1 = "SELECT * FROM [Person].[Address]";
string sql2 = "SELECT * FROM [Production].[TransactionHistory]";

SqlCommand cmd1 = new SqlCommand(sql1, conn);
SqlCommand cmd2 = new SqlCommand(sql2, conn);
cmd1.CommandTimeout = 500;
cmd2.CommandTimeout = 500;
conn.Open();
SqlDataReader dr1 = cmd1.ExecuteReader();
// do stuff with dr1 data
conn.Close();

conn.Open();
SqlDataReader dr2 = cmd2.ExecuteReader();
// do stuff with dr2 data
conn.Close();
}

Multiple Active Result Sets (MARS) in SQL Server 2005

MARS is newly added future in SQL 2005. In earlier version from sql2005 user are not allowed to run more than one SQL batch on an open connection at the same time but sql2005 allows the user to run more than one SQL batch on an open connection at the same time.
private void MARS_Off()
{
SqlConnection conn = new SqlConnection("Server=serverName;
Database=adventureworks;Trusted_Connection=yes;");

string sql1 = "SELECT * FROM [Person].[Address]";
string sql2 = "SELECT * FROM [Production].[TransactionHistory]";

SqlCommand cmd1 = new SqlCommand(sql1, conn);
SqlCommand cmd2 = new SqlCommand(sql2, conn);
cmd1.CommandTimeout = 500;
cmd2.CommandTimeout = 500;
conn.Open();
SqlDataReader dr1 = cmd1.ExecuteReader();
// do stuff with dr1 data
conn.Close();

conn.Open();
SqlDataReader dr2 = cmd2.ExecuteReader();
// do stuff with dr2 data
conn.Close();
}

What is the DEADLOCK ?

A deadlock is a situation in which two transactions conflict with each other and the only resolution is to cancel one transaction.

How to find out column names and their datatypes in a given table using a query in SQL Server?

Using the table tablename, write the query as follows -
Select * from information_schema.columns where table_name = tablename

What is the DEADLOCK ?

A deadlock is a situation in which two transactions conflict with each other and the only resolution is to cancel one transaction.

How to find out column names and their datatypes in a given table using a query in SQL Server?

Using the table tablename, write the query as follows -
Select * from information_schema.columns where table_name = tablename

What is the EXISTS operator in SQL Server ? OR What is the EXISTS keyword in SQL Server ?

When you use EXISTS, you do not really returned data - instead you return a simple TRUE/FALSE regarding the existence of data that meets the criteria established in the query that the EXISTS statement is operated against.
EXISTS simply test whether the inner query returns any row. If it does, then the outer query proceeds, if not, the outer query does not executes, and the entire SQL statement returns nothing.

The EXISTS condition is considered "to be met " if the subquery returns at least one row.

The syntax for EXISTS condition is -
SELECT columns FROM sometable WHERE EXISTS (subquery)

What is a Subquery ?

A Subquery is a normal T-SQL query that is nested inside another query. They are created using parentheses when you have a SELECT statement that serve as the basis for the either part of the data or the condition in another query.
Subqueries are generally used to fill one of couple of needs -

1. Break a query up into a series of a logical steps.
2. Provide a listing to be the target of a WHERE clause together with [IN|ESISTS|ANY|ALL].
3. TO provide a lookup driven by each individual record in a parent query.
Eg.
SELECT SelectList FROM SomeTable WHERE SomeColumn = (SELECT SingleColumn FROM SomeTable WHERE Condition that results in only one row returned)

What is the EXISTS operator in SQL Server ? OR What is the EXISTS keyword in SQL Server ?

When you use EXISTS, you do not really returned data - instead you return a simple TRUE/FALSE regarding the existence of data that meets the criteria established in the query that the EXISTS statement is operated against.
EXISTS simply test whether the inner query returns any row. If it does, then the outer query proceeds, if not, the outer query does not executes, and the entire SQL statement returns nothing.

The EXISTS condition is considered "to be met " if the subquery returns at least one row.

The syntax for EXISTS condition is -
SELECT columns FROM sometable WHERE EXISTS (subquery)

What is a Subquery ?

A Subquery is a normal T-SQL query that is nested inside another query. They are created using parentheses when you have a SELECT statement that serve as the basis for the either part of the data or the condition in another query.
Subqueries are generally used to fill one of couple of needs -

1. Break a query up into a series of a logical steps.
2. Provide a listing to be the target of a WHERE clause together with [IN|ESISTS|ANY|ALL].
3. TO provide a lookup driven by each individual record in a parent query.
Eg.
SELECT SelectList FROM SomeTable WHERE SomeColumn = (SELECT SingleColumn FROM SomeTable WHERE Condition that results in only one row returned)

How to find nth highest salary from Employee table in SQL Server?

SELECT TOP 1 salary FROM (SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC) a ORDER BY salary

How to find nth highest salary from Employee table in SQL Server?

SELECT TOP 1 salary FROM (SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC) a ORDER BY salary

What is the IDENTITY property in SQL Server?

The IDENTITY property enables you to use system generated values in your tables column. It is similar to the auto number datatype in MS ACCESS. You are allowed a single column in each table with the IDENTITY property. Typically, IDENTITY column generates system assigned keys. To enforce entity integrity, you must uniquely identify each row in a table. If no natural column or set of column does this, you might went to create an IDENTITY column.
You can use the IDENTITY property if the column to which it is being assigned is an integer or compatible with an integer. Therefore you can use the following datatypes - tinyint, bigint, smallint, numeric, integer, decimal.

You can use numeric and decimal only if they have scale of 0 (zero). It must also not allowed to NULL.

How to convert timestamp data to date data (datetime datatype) in SQL Server?

The name timestamp is a little misleading. Timestamp data has nothing to do with dates and times and can not be converted to date data. A timestamp is a unique number within the database and is equivalent to a binary(8)/varbinary(8) datatype. A table can have only one timestamp column. Timestamp value of a row changes with every update of the row. To avoid the confusion, SQL Server 2000 introduced a synonym to timestamp, called rowversion.

What is the IDENTITY property in SQL Server?

The IDENTITY property enables you to use system generated values in your tables column. It is similar to the auto number datatype in MS ACCESS. You are allowed a single column in each table with the IDENTITY property. Typically, IDENTITY column generates system assigned keys. To enforce entity integrity, you must uniquely identify each row in a table. If no natural column or set of column does this, you might went to create an IDENTITY column.
You can use the IDENTITY property if the column to which it is being assigned is an integer or compatible with an integer. Therefore you can use the following datatypes - tinyint, bigint, smallint, numeric, integer, decimal.

You can use numeric and decimal only if they have scale of 0 (zero). It must also not allowed to NULL.

How to convert timestamp data to date data (datetime datatype) in SQL Server?

The name timestamp is a little misleading. Timestamp data has nothing to do with dates and times and can not be converted to date data. A timestamp is a unique number within the database and is equivalent to a binary(8)/varbinary(8) datatype. A table can have only one timestamp column. Timestamp value of a row changes with every update of the row. To avoid the confusion, SQL Server 2000 introduced a synonym to timestamp, called rowversion.

What is the CASCADE action in SQL Server?

By default, you can not delete a record or update the referenced column in a referenced table if that record is referenced from the dependent table. If you want to be able to delete or update such records, then you need to set up a CASCADE action for the delete and/or update.

What is the CASCADE action in SQL Server?

By default, you can not delete a record or update the referenced column in a referenced table if that record is referenced from the dependent table. If you want to be able to delete or update such records, then you need to set up a CASCADE action for the delete and/or update.