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.

Example:

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

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

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

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

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

SELECT
   Stream,
   [Name]
FROM
   @Student
WHERE
CASE
    WHEN Stream = 'Computer Engineering' THEN 1
    WHEN isAllow = 1 THEN 1
END = 1

Output:

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.

Difference in “varchar” and “nvarchar” in SQL Server

This article explains the difference between varchar and nvarchar datatype in SQL Server.

nVarchar Varchar
varchar stores UNICODE data.  Means it store UNICODE or multilingual data. Varchar Stores ASCII data
UNICODE requires 2 bytes for each character you store. This Means single row of a single nvarchar column can only be nvarchar (4000) Because because of the row size limitations of Sql Server is the same as the page size limit, which is 8060 bytes ASCII only requires 1 byte for each character.
This means a single row of a single varchar column can be varchar(8000) because of the row size limitations of Sql Server is the same as the page size limit, which is 8060 bytes