February 26, 2014

SQL ORDER BY clause

We all know that order by keyword is used to sort the output of the query either in ascending or descending order. By default, order by keyword sorts the result in ascending order. In order to sort the result in descending order all we need to use is "desc" keyword.

Ascending Order Example:

select column_a from table order by column_a

Result:
coloumn_a
1
2
3
4
5

Descending Order Example:

select column_a from table order by column_a desc

Result:
column_a
5
4
3
2
1

Manipulating Order By keyword:

Above, we have seen an example how to sort the specified column in an ascending or descending order. But, in the real world projects we face such situation where we have to sort in the specific way. Let's take above example. If we would like to see number 3 of column_a always on the top of the result. Then ,

select column_a from table
order by case when column_a = '3' then '1' else column_a end asc

Result:
column_a
3
1
2
4
5

February 25, 2014

SQL Server Reporting Service Subscription (Report Automation using Windows File Share)

For the report automation, the report can be delivered using two options:
1) Windows File Share
2) Email Subscription


Here, we will discuss using Windows File Share.



In the file name, it can be edited and make whatever is needed. Here, if you added after the file name @timestamp then after dumping the file you will be able to see date and time when the file was dumped.

The path where the reports are dumped must be provided in UNC (Uniform Naming Convention) format. It is \\\ .

Credentials to access the file share must be provided.

The Render format must be selected as needed.

Schedule the report as needed by clicking Select Schedule.



Parameter must be passed as required.

February 24, 2014

How to get SSRS Usage Report

In my personal experience, there will be number of report published in reporting services for the business analysis. Though, SQL Server Reporting Service has made life easy to pull the data for the analysis purpose, developer has to work hard to do information engineering and to make worthy looking for doing the analysis. There will be number of parameterized KPI reports which keeps changing and accordingly new reports will be deployed in the report server. It becomes necessary to know what reports and how often reports are being used.

As there will be many reports deployed in the report server, it is mandatory to know execution time of each report and at what time the report server takes maximum amount of time to run the report. Based on above mentioned things maintenance of the report can be done.

SQL Server Reporting Services captures all the necessary data related to the each report execution on a table called "ExecutionLog" in the ReportServer database. There is another table named "Catalog" which is associated with the table ExecutionLog, these two table will help to get the information of each report. You can get as much as information you want to get using these tables.

Below is the example:

SELECT 
Cat.Name AS Report_Name, 
AVG(Ex.TimeDataRetrieval) AS AVGQueryTime, 
MIN(Ex.TimeDataRetrieval) AS MINQueryTime, 
MAX(Ex.TimeDataRetrieval) AS MAXQueryTime, 
STDEV(Ex.TimeDataRetrieval) AS SDQueryTime, 
AVG(Ex.TimeProcessing) AS AVGProcessTime, 
MIN(Ex.TimeProcessing) AS MINProcessTime, 
MAX(Ex.TimeProcessing) AS MAXProcessTime, 
STDEV(Ex.TimeProcessing) AS SDProcessTime, 
AVG(Ex.TimeRendering) AS AVGRenderTime, 
MIN(Ex.TimeRendering) AS MINRenderTime, 
MAX(Ex.TimeRendering) AS MAXRenderTime, 
STDEV(Ex.TimeRendering) AS SDRenderTime, 
AVG(Ex.ByteCount) AS AVGReportrSize, 
AVG(Ex.[RowCount]) AS AVGRowCount, 
MIN(Ex.[RowCount]) AS MINRowCount, 
MAX(Ex.[RowCount]) AS MAXRowCount, 
COUNT(Ex.ReportID) AS [TotalExecutions],
Ex.Format, Ex.TimeStart
FROM ExecutionLog as EX 
INNER JOIN Catalog as Cat ON Ex.ReportID = Cat.ItemID 
GROUP BY Cat.Name, Ex.Format, Ex.TimeStart

February 20, 2014

SQL Server - Combining multiple row data into single row data with the help of comma

I had to prepare a report where multiple row data should be merged/combine into single row using separated by comma. I found the best solution to solve this is using STUFF function and FOR XML clause of SQL Server.

According to the Microsoft, "STUFF function helps to inserts a string into another string. First of all it deletes a specified length of characters of the first string at the start position and then inserts the second string into the first string at the start position."

Syntax:

STUFF(character_expression, start_length, replacewith_expression)

For Example:

SELECT STUFF('avesh', 2, 3, 'aastha')

Result:
aaasthah

Using FOR XML in select statement returns the result as in XML format where as select query returns results as a rowset. There are different modes in a FOR XML clause, here we will use PATH mode.


DECLARE @Customer TABLE (CustomerId int, CustomerName varchar(50), BranchName varchar(50))

INSERT INTO @Customer VALUES (1, 'Kale', 'drn branch')
INSERT INTO @Customer VALUES (1, 'kale', 'brt branch')
INSERT INTO @Customer VALUES (3, 'Sailo', 'ktm')

Without using STUFF function:

SELECT CustomerId, CustomerName, BranchName = (SELECT ',' + BranchName
    FROM @Customer AS x2 WHERE CustomerId = x.CustomerId    
     FOR XML PATH(''))
FROM @Customer AS x
GROUP BY CustomerId, CustomerName

ORDER BY CustomerID

Result:



Using STUFF function:

SELECT CustomerId, CustomerName, BranchName = STUFF((SELECT ',' + BranchName
    FROM @Customer AS x2 WHERE CustomerId = x.CustomerId    
     FOR XML PATH('')), 1, 1, '')
FROM @Customer AS x
GROUP BY CustomerId, CustomerName
ORDER BY CustomerID

Result: 

February 15, 2014

SQL Server - Four Part Name

The name of a database object can be a four-part name in the following form:

server_name.[database_name].[schema_name].object_name - Four part name

server_name.[database_name]..object_name - Schema name is omitted.

server_name..[schema_name].object_name - Database name is omitted.

server_name...object_name - Database and schema name is omitted.


server name:It can be either remote server name or linked server name.

Example:
SELECT * FROM DatabaseName.Master.dbo.sysobjects

database_name: If the object is at local instance of SQL Server then it will be SQL Server database name, or if the object is at linked server then it will be OLE DB catalog.

Example:
SELECT * FROM Master.dbo.sysobjects

schema_name: It will be the name of the schema that contains the object if the object is in a SQL Server database, or if the object is in linked server then it will be OLE DB schema name.

Example:
SELECT * FROM dbo.sysobjects

object_name: It is the name of the object.

Example:
SELECT * FROM sysobjects

February 12, 2014

Stored Procedure - QUOTED_IDENTIFIER ON/OFF, ANSI_NULL ON/OFF and NOCOUNT ON/OFF

Most of the SQl Developer uses these options while writing TSQL Stored Procedure/User defined function/Trigger. QUOTED_IDENTIFIER is implemented at the parse time where as ANSI_NULL and NOCOUNT is implemented at run time.

QUOTED_IDENTIFIER identifies the data based on single and double quotes. When the setting is set on, all the string that is defined inside double quotes will be interpreted as T-SQL object identifier like table name, procedure name, column name etc. This quoted identifier do not have to follow T-SQL rules and will be reserved keywords and can include characters not generally allower in TSQL identifiers. All the character set that is defined in the single quotes will be interpreted as a literal.

When QUOTED_IDENTIFIER is off, the string that is inside single or double quotation mark will be interpreted as literals.

For Example:

SET QUOTED_IDENTIFIER ON
CREATE TABLE "SELECT" ("TABLE" INT) -- SUCCESS

GO

SET QUOTED_IDENTIFIER ON
SELECT "SOMETEXT" AS VALUE -- FAILS BECAUSE SOMETEXT IS NOT A LITERAL


SET QUOTED_IDENTIFIER OFF
CREATE TABLE "SELECT" ("TABLE" INT) -- FAIL

GO

SET QUOTED_IDENTIFIER OFF
SELECT "SOMETEXT" AS VALUE -- FAILS BECAUSE "SOMETEXT" IS  A LITERAL

ANSI_NULL helps to handle the comparison operations with NULL values. When ANSI_NULL is ON, any comparison on a select statement using the equals (=) and not equals (<>) operators with NULL values returns zero rows even if there are null values in the column. it follows the ISO standard so you need to use IS NULl and IS NOT NULL so as to do comparison with NULL values.

When ANSI_NULL is OFF, then amu compasion with NULL value using the equals (=) and not equals (<>) operators with NULL values returns rows with NULL value for the equals operator and not NULL values for not equals operator. It does not follow ISO standard and returns true and false.

For Example:

SET ANSI_NULLS ON
IF NULL IS NULL
   PRINT 'TRUE'
ELSE
   PRINT 'FALSE'

Result: TRUE

SET ANSI_NULLS ON
IF NULL = NULL
   PRINT 'TRUE'
ELSE
   PRINT 'FALSE'

Result: FALSE

SET ANSI_NULLS OFF
IF NULL = NULL
   PRINT 'TRUE'
ELSE
   PRINT 'FALSE'

Result: TRUE

NOCOUNT helps to stop the count of rows affected during the execution of stored procedures.

For Example:

CREATE PROCEDURE USP_NOCOUNT

SET NOCOUNT OFF

AS
BEGIN

    DECLARE @TEMP TABLE(ID INT)
 
    INSERT INTO @TEMP VALUES(1)
    INSERT INTO @TEMP VALUES(2)

    SELECT * FROM @TEMP

END

Then, execute the stored procedure  USP_NOCOUNT

Exec USP_NOCOUNT

After execution, you will get message twice "1 row(s) affected".

CREATE PROCEDURE USP_NOCOUNT

SET NOCOUNT ON

AS
BEGIN

    DECLARE @TEMP TABLE(ID INT)
 
    INSERT INTO @TEMP VALUES(1)
    INSERT INTO @TEMP VALUES(2)

    SELECT * FROM @TEMP

END

Then, execute the stored procedure  USP_NOCOUNT

Exec USP_NOCOUNT

After execution, you will get message "Command(s) completed successfully".

February 10, 2014

SQL Server - Joins

INNER JOIN

This join will return the row that matches in both tables.

Select * from Table1 as tbl1
Inner Join Table2 as tbl2 on tbl1.column_name = tbl2.column_name

RIGHT OUTER JOIN

This join will return all the rows from right table along with the rows that matches in both tables. All the columns from left table which does not find match returns null value.
Select * from Table1 as tbl1
Right Outer Join Table2 as tbl2 on tbl1.column_name = tbl2.column_name

LEFT OUTER JOIN

This join will return all the rows from left table along with the rows that matches in both tables. All the columns from right table which does not find match returns null value.
Select * from Table1 as tbl1
Left Outer Join Table2 as tbl2 on tbl1.column_name = tbl2.column_name

FULL OUTER JOIN

This join returns all rows from both tables, matching up the rows wherever a match can be made and placing NULLs in the places where no matching row exists.
.

February 5, 2014

SQL Server - Concatenation

To concatenate two or more character or binary strings, + sign string operator should be used.

Syntax:

expression + expression

For Example:

SELECT 'Avesh' + ' Dhakal'

Result: Avesh Dhakal

When you need to concat two integer than first of all you need to conver/cast them into string and then concat them.

For Example:

SELECT CAST(1 AS VARCHAR(10)) + ' ' + CAST(2 AS VARCHAR(10))

Result: 1 2