SqlServer notes : Inbuilt reports provided by Sqlserver itself

March 13, 2014

In Object Explorer

 

right click on server name and select Reports->Standard Reports->

Performance -Top Queries by Average CPU Time


sqlserver : Taking script along with data for a table

September 16, 2013

create table script along with data:

right click on db

tasks->generate script

select tables

selected advanced button

under general category

check for types of data to scriptTable Script with Data

it has 3 options
1. data only
2. schema only(by default)
3. schema and data

choose 3rd option to get table script along with
data .


Sqlserver : Search query

August 13, 2013

You want to create a report (Search)

which has different filters :

 

Based on the filters u have to generate the report.

 

User can select all filters or none of the filters and can click on Search button to generate the button

 

Ur Stored Procedure should have that many input parameters (similar to filters) for getting the recordset.

Input parameters should accept null values

 

CREATE PROCEDURE spDoSearch
   @FirstName varchar(25) = null,
   @LastName varchar(25) = null,
   @Title varchar(25) = null
AS
  BEGIN
      SELECT ID, FirstName, LastName, Title
      FROM tblUsers
      WHERE
        (@FirstName IS NULL OR FirstName = @FirstName) AND
        (@LastNameName IS NULL OR LastName = @LastName) AND
        (@Title IS NULL OR Title = @Title)
END

 

 

Second method

 

WHERE
    (FirstName = ISNULL(@FirstName, FirstName)
    OR COALESCE(@FirstName, FirstName, '') = '')
AND (LastName = ISNULL(@LastName, LastName)
    OR COALESCE(@LastName, LastName, '') = '')
AND (Title = ISNULL(@Title, Title)
    OR COALESCE(@Title, Title, '') = '')

second method:

 

 


Sqlserver : Five methods converting rows to columns

August 12, 2013

Source : http://sqlserveradvisor.blogspot.in/2009/03/sql-server-convert-rows-to-columns.html

 

Post with five methods for converting rows to columns.

From the classic ‘CASE’ construction to the newer PIVOT and ROW_NUMBER() OVER (ORDER BY) functions.

Be careful!
Some methods presented here can have a severe negative impact on performance. Especially the in-line function with a cursor should be used with the utmost precaution.Check the query plan and the statistics i/o (see this post) of your queries before putting the code into your production environment!

1. use a CASE statement

SELECT DATEPART(YEAR,orderdate),
SUM(CASE WHEN DATEPART(q,orderdate) = 1 THEN orderamount ELSE 0 END) AS Qtr1,
SUM(CASE WHEN DATEPART(q,orderdate) = 2 THEN orderamount ELSE 0 END) AS Qtr2,
SUM(CASE WHEN DATEPART(q,orderdate) = 3 THEN orderamount ELSE 0 END) AS Qtr3,
SUM(CASE WHEN DATEPART(q,orderdate) = 4 THEN orderamount ELSE 0 END) AS Qtr4,
SUM(orderamount) AS Total
FROM Orders
— additional where clause goes here…
GROUP BY DATEPART(YEAR,orderdate)

2. use the COALESCE function

DECLARE @AllValues VARCHAR(4000)

SELECT @AllValues = COALESCE(@AllValues + ‘,’, ”) + HandlingCode
FROM OrdersDetails
WHERE OrderNumber = @OrderNumber

3. use ROW_NUMBER() OVER (ORDER BY)

SELECT OrderNumber, OrderDate,
–get the special handling codes and show them as columns, max of 3 (agreed by users)
(SELECT HandlingCode
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY HandlingCode) AS ROWNUMBER,HandlingCode
FROM OrdersDetails
WHERE OrdersDetails.OrderNumber = Orders.OrderNumber
) HandlingCode
WHERE ROWNUMBER = 1) HandlingCode1,
(SELECT HandlingCode
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY HandlingCode) AS ROWNUMBER,HandlingCode
FROM OrdersDetails
WHERE OrdersDetails.OrderNumber = Orders.OrderNumber
) HandlingCode
WHERE ROWNUMBER = 2) HandlingCode2,
(SELECT HandlingCode
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY HandlingCode) AS ROWNUMBER,HandlingCode
FROM OrdersDetails
WHERE OrdersDetails.OrderNumber = Orders.OrderNumber
) HandlingCode
WHERE ROWNUMBER = 3) HandlingCode3
FROM Orders
WHERE OrderNumber = @OrderNumber

4. Use an inline function

SELECT OrderNumber, OrderDate, fn_GetHandlingCodes (OrderNumber)
FROM Orders
WHERE OrderNumber = @OrderNumber

CREATE FUNCTION dbo.fn_GetHandlingCodes (@OrderNumber INT)
RETURNS VARCHAR(1200)
AS
BEGIN
DECLARE @HandlingCode VARCHAR(20)
DECLARE @ReturnValue  VARCHAR(4000)

— use that fastest cursor methods: local fast_forward
DECLARE code_cursor CURSOR LOCAL fast_forward FOR
SELECT HandlingCode
FROM OrdersDetails
WHERE OrderNumber = @OrderNumber
AND NOT HandlingCode IS NULL — filled

SET @ReturnValue = ”  — set to non null

OPEN code_cursor
FETCH NEXT FROM code_cursor  INTO @HandlingCode
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @ReturnValue = @ReturnValue + @HandlingCode + ‘, ‘

IF LEN (@ReturnValue) > 1000 BREAK — avoid overflow

FETCH NEXT FROM code_cursor INTO @HandlingCode
END

CLOSE code_cursor
DEALLOCATE code_cursor

— remove last delimiter
IF LEN(@ReturnValue) > 1 SET @ReturnValue = SUBSTRING(@ReturnValue,1,LEN(@ReturnValue)-2)

RETURN @ReturnValue
END

5. Use a pivot

USE AdventureWorks
GO

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

 


Float vs. Decimal data types in Sql Server

July 22, 2013

http://josephfluckiger.blogspot.in/2009/06/float-vs-decimal-data-types-in-sql_25.html

 

This is an excellent article describing when to use float and decimal. Float stores an approximate value and decimal stores an exact value.

In summary, exact values like money should use decimal, and approximate values like scientific measurements should use float.

Here is an interesting example that shows that both float and decimal are capable of losing precision. When adding a number that is not an integer and then subtracting that same number float results in losing precision while decimal does not:

DECLARE @Float1 float, @Float2 float, @Float3 float, @Float4 float;
SET @Float1 = 54;
SET @Float2 = 3.1;
SET @Float3 = 0 + @Float1 + @Float2;
SELECT @Float3 – @Float1 – @Float2 AS “Should be 0”;

Should be 0
———————-
1.13797860024079E-15

 

When multiplying a non integer and dividing by that same number, decimals lose precision while floats do not.

DECLARE @Fixed1 decimal(8,4), @Fixed2 decimal(8,4), @Fixed3 decimal(8,4);
SET @Fixed1 = 54;
SET @Fixed2 = 0.03;
SET @Fixed3 = 1 * @Fixed1 / @Fixed2;
SELECT @Fixed3 / @Fixed1 * @Fixed2 AS “Should be 1”;

Should be 1
—————————————
0.99999999999999900

AT 7:51 AM


sqlserver string split function into table

April 25, 2013

 

select * from [StringToTable](‘1,2,3′,’,’)
CREATE FUNCTION [dbo].[StringToTable]
(
@inputString nvarchar(max),
@separator char (1)
)
RETURNS @ResultTable TABLE ( [String] nvarchar(max) )
AS
BEGIN

DECLARE @stringToInsert nvarchar (max)

WHILE LEN(@inputString) > 0
BEGIN
SET @StringToInsert = LEFT(
@inputString,
ISNULL(NULLIF(CHARINDEX(@separator, @inputString) – 1, -1),
LEN(@inputString)
)
)
SET @InputString = SUBSTRING(@InputString,

ISNULL
(NULLIF
(CHARINDEX(@separator, @InputString),
0),
LEN(@InputString)) + 1,
LEN(@InputString))

INSERT INTO @ResultTable
(
[String]
)
VALUES
(
@StringToInsert
)

END

RETURN
END


Sqlserver notes : Pivot and Unpivot (converting horizontal rows to vertical columns)

April 24, 2013

Source :

 

http://www.sqlservercentral.com/blogs/vivekssqlnotes/2012/04/14/pivot-and-unpivot-table-in-sql-server/

 

http://shivasoft.in/blog/sql/sqlserver/sql-server-pivot-table-example/

 

http://www.c-sharpcorner.com/Blogs/9783/how-to-use-pivot-table-in-sql-server.aspx

 

Pivot Table:- Pivot tables are used to summarize and display the data, specially in case of report data by means of aggregating the values. Pivot table can be used if we want to display the unique values of the column of a table as the columns of another table. It turns the unique values of a specified column into another table columns.

The syntax for the Pivot is given below:-

SELECT non-pivoted column,
firstpivotedcolumn AS column name,
secondpivotedcolumn AS column name,
lastpivotedcolumn AS column name
FROM
(SELECT query that produces the data>)
AS aliasforsourcequery
PIVOT
(
aggregation function(column being aggregated)
FOR
column that contains the values that will become column headers
IN ( firstpivotedcolumn,secondpivotedcolumn,
last pivoted column)
) AS aliasforthepivottable (optional ORDER BY clause)

For example, suppose we have a table called tbl_student which contains the columns studentname, grade and marks. The query for creating this table and inserting data is given below:-

Syntax for creating the database:-

Create database DB_Pivot

Query for creating table:-

Create table tbl_student (studentname nvarchar(200), grade nvarchar(10), marks int)

Query for inserting the data into the table:-

Insert into tbl_student (studentname,grade,marks)
Select ‘Vivek Johari’,’I’,30
Union All
Select ‘Vivek Johari’,’II’,20
Union All
Select ‘Vivek Johari’,’III’,35
Union All
Select ‘Vivek Johari’,’IV’,40
Union All
Select ‘Vivek Johari’,’V’,45
Union All
Select ‘Avinash Dubey’,’I’,30
Union All
Select ‘Avinash Dubey’, ‘II’, 2
Union All
Select ‘Avinash Dubey’, ‘III’ ,35
Union All
Select ‘Chandra Singh’, ‘I’, 30
Union All
Select ‘Chandra Singh’, ‘II’, 20
Union All
Select ‘Chandra Singh’, ‘III’, 35
Union All
Select  ‘ Pankaj Kumar’, ‘I’, 33
Union All
Select ‘ Pankaj Kumar’, ‘II’, 29

Now if we want to see the data in the table tbl_student, it will looks like shown below:-

Select  *  from tbl_student

Suppose we want to display the data as shown below:-

Studentname       I            II               III             IV                  V
Vivek Johari          30        20           35          40              45
Chandra Singh      30        20           35
Avinash Dubey      30        20           35
Pankaj Kumar       33         29

Then we can either use the Select……… Case statement or the Pivot command.
In this article I am going to show the use of the Pivot operator to display data as shown above:-

Select studentname, [I], [II], [III], [IV] , [V]
from
( Select grade, studentname, marks from tbl_student) as sourcetable
Pivot (  avg(marks) for grade in ([I],[II],[III],[IV],[V])) as pivotable order by V desc,IV desc,III desc,II desc,I desc

Or we can use the given below query also:-

Select studentname, [I], [II], [III], [IV] , [V] from tbl_student
Pivot  (  avg(marks) for grade in ([I],[II],[III],[IV],[V])) as pivotable order by V desc,IV desc,III desc,II desc,I desc

Both the query will gives the same result. In the first query we use the Derived table as the Source table and in the 2nd query we use the table tbl_student as the source table.

Unpivot table:- Unpivot table is reverse of Pivot table as it rotate the columns of a table into the value of a column. For example, suppose we have a table say tbl_stdmarksdata whose structure us given below:-

Create table tbl_stdmarksdata  (studentname nvarchar(100), I int, II int, III int, IV int, V int)

Query for inserting data in this table is given below:-
Insert into tbl_stdmarksdata (studentname,I,II,III,IV,V)
Select ‘Vivek Johari’,30,20,35, 40, 45
Union All
Select ‘Chandra Singh’,30,20,35,44, 80
Union All
Select ‘Avinash Dubey’,30,25,35,20, 39
Union All
Select ‘Pankaj Kumar’,33,29,30, 60, 50
After insert,  the data in the table :-
select * from tbl_stdmarksdata
The Query for the Unpivot table will be as follow:-
select studentname,Marks,Grade
from tbl_stdmarksdata
unpivot
(Marks for Grade in (I,II,III,IV,V) ) as tblunpvt