Convert rows into comma separated values column – SQL Server

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags , with Leave a comment on Convert rows into comma separated values column – SQL Server

Convert rows into comma separated values column – SQL Server

Converting rows values into comma separated column value is required lots of time:

example, i want to convert :

Name
------------------------------
India
USA
Japan
China
Switzerland

to

Continue reading “Convert rows into comma separated values column – SQL Server”

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

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.

Continue reading “Get Login Session count, Session Mode, User Name, Machine name in SQL Server”

SQL Server Pivot Table Example

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with 9 Comments on SQL Server Pivot Table Example

Example and tutorial of using PIVOT in SQL Server

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.

Continue reading “SQL Server Pivot Table Example”

Between Clause problem in Date comparison – SQL Server

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with 2 Comments on Between Clause problem in Date comparison – SQL Server

SQL Server Date Comparison Tips : Never user Between clause with DateTime in 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.

Continue reading “Between Clause problem in Date comparison – SQL Server”

Get Table information (Column Name, Data Type) in SQL Server

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with 6 Comments on Get Table information (Column Name, Data Type) in SQL Server

Get Table information like Column Name, Data Type, Character length, Default Values etc in SQL Server

To get the Table in information in SQL Server,  we can use below Query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Users'
ORDER BY ORDINAL_POSITION

Where’ Users’ is Table Name
Output is shown in below snap:

SQL Server INFORMATION_SCHEMA.COLUMNS
SQL Server INFORMATION_SCHEMA.COLUMNS

Continue reading “Get Table information (Column Name, Data Type) in SQL Server”

Create new User in SQL Server 2005

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with 1 Comment on Create new User in SQL Server 2005

Create new User in SQL Server 2005

Login into SQL Server and right click on security tab.

Select New -> Login.

SQL Server Security folder
SQL Server Security folder

Continue reading “Create new User in SQL Server 2005”

Install SQL Server 2005 in Windows XP

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with Leave a comment on Install SQL Server 2005 in Windows XP

Steps to Install SQL Server 2005 – Standard edition 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

Continue reading “Install SQL Server 2005 in Windows XP”

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

Using Case statement in Where Clause in SQl Server. This script is used to create the 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

Author posted by Jitendra on Posted on under category Categories SQL, SQL Server and tagged as Tags with Leave a comment on Execute dynamic Query in SQL Server

How to execute the dynamic query in microsoft SQL Server 2005.

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

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with Leave a comment on Difference in “varchar” and “nvarchar” in SQL Server

What is the difference between varchar and nvarchar in MS 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