Manually attach database in SQL Server

SQL server 2005, does not come with default / sample databases. so it can be downloaded and installed from

After downloading, the challenge for the new developer is that how to associate database with existing SQL server installed on system.

To associate the database, follow below steps:

right click on database and select “Attach” option.

Click on Attach option of Database

Get Login Session count, Session Mode, User Name, Machine name in SQL Server

In SQL server sys.dm_exec_sessions Stores the login information about users like username, authentication mode (Windows or SQL), login time, machine name and therefore we can easily determine the current state of SQL server like Session count, User name, machine name etc.

SQL Server Pivot Table Example

A pivot table is a frequently used method of summarizing and displaying especially report data by means of grouping and aggregating values.
Pivot tables are easily created by office users using Microsoft Excel or MS Access.
Since pivot table enables report builders and BI (Business Intelligence) specialists empower their presentation of reports and increase the visibility and understandability of mined data, pivot tables are common and preferred widely.

Pivot tables display data in tabular form. The pivot table formatting is not different than a tabular report formatting.
But the table columns are formed by the report data itself.

Microsoft SQL Server has introduced the PIVOT and UNPIVOT commands as enhancements to t-sql with the release of MS SQL Server 2005.

Between Clause problem in Date comparison – SQL Server

My seniors suggested me not to use the Between clause for Datetime. But whenever i tried to found the reason not to use Between clause nobody gave satisfactory answer. One day i caught in a problem where Between clause was not working. 🙂 I was very happy. you must be thinking that why the man is happy even if his code fails ? This time i got the answer of my question.

Why not to use the Between clause in Date comparison?

I will explain this problem with below example.

Install SQL Server 2005 in Windows XP

This article will describe that how to install the SQL server 2005 on Windows XP. Normally a wrong step in installation for beginners results in non working SQL Server instance on his windows XP. To install the SQL Server 2005 on Windows XP follow below steps:

The firts screen will be the license agreement screen, accept the terms and proceed.

SQL Server 2005 Accept License
SQL Server 2005 Accept License

The next screen will check the prerequisites for SQL Server 2005.

SQL Server 2005 Prerequisites
SQL Server 2005 Prerequisites

Case statement in Where Clause – SQL Server – Conditional Where clause

In few scenario, we might need to perform conditional where clause. Some time the condition can be written well in OR with Where clause but in some scenario it is not possible.

so this time, i have come up with one more article on writing the conditional where clause.


DECLARE @Student Table (Stream Varchar(20), [Name] Varchar(20), isAllow BIT )

INSERT Into @Student
('Computer Engineering','Minal Zaa', 0)

INSERT Into @Student
('Civil Engineering','Manoranjan Sahoo', 1)

INSERT Into @Student
('scientist','Santosh Karemore', 1)

INSERT Into @Student
('BCom','Rahul jha', 0)

    WHEN Stream = 'Computer Engineering' THEN 1
    WHEN isAllow = 1 THEN 1
END = 1


Case statement in Where Clause

In above code, we have checked that if stream is ‘Computer Engineering’ then display the record or if IsAllow bit is true then display the record. Same query can be written using OR operator but here i wanted to demonstrate the use of Case statement in Where Clause.

Execute dynamic Query in SQL Server

This script demonstrates that how to execute the dynamic query in the MS Server 2005.

Declare @Query nvarchar(4000)
SET @Query = 'Select GETDATE()'
EXEC sp_executesql @Query

Output will be like ‘2010-07-16 17:42:38.733’

Note : datatype supported for dynamic query is “ntext/nchar/nvarchar”
EXEC sp_executesql is responsible to run the dynamic query.