About This Blog

.net & SQL Samples, programming tips and tricks, performance tips, guidelines, and best practices

Friday 30 December 2011

Monday 26 December 2011

Dynamic Sorting in SSRS

Introduction
Many times while developing any report we come across a situation where we need to SORT the TABLE columns or the columns in the GROUP dynamically. There are many well known ways to achieve this. However, here I will demonstrate a not-so-known way -
 Implementation
Assuming that the sorting is to be done on 3 columns – Col1,Col2 & Col3. But the ORDER is undecided until runtime. Now, what we can do is -
  1. Define 3 Report parameters. Say - @SortCol1Name,@SortCol2Name & @SortCol3Name of STRING type.
  2. Pass the name of the columns which need to sorted in the required order. Say -
    • @SortCol1Name = “ColumnX”,@SortCol2Name = “ColumnY” and @SortCol3Name = “ColumnZ”
    • OR @SortCol1Name = “ColumnY”,@SortCol2Name = “ColumnZ” and @SortCol3Name = “ColumnX”
    • OR any preferred order
  3. In the Sorting Option of the Properties dialog box of Table or Group, set the sort expression as under -
    • =Fields(Parameters!SortCol1Name.Value).Value
    • =Fields(Parameters!SortCol2Name.Value).Value
    • =Fields(Parameters!SortCol3Name.Value).Value
1
This finally gets converted to –Fields!ColumnX.Value,Fields!ColumnY.Value & Fields!ColumnZ.Value if we have the passed the following values for the parameters - @SortCol1Name = “ColumnX”,@SortCol2Name = “ColumnY” and @SortCol3Name = “ColumnZ”
Conclusion
With this trick, we can easily set the sort expression dynamically. However, there is a limitation that we can not set the Sort Direction dynamically. I could not find out a way for it. If you have any trick to get it done with this, please leave your suggestion as a comment to this post.

Wednesday 7 December 2011

How many times the T-SQL inside the CTE is executed?

Introduction

Common Table Expression (CTE) has become very popular these days. However, many users of CTE still have a myth that the t-sql written inside the CTE will be executed only once irrespective of the number of times it is referred in the subsequent CTEs or the related query.
However, this is not correct. It is a pure misconception. And let’s try to prove this with a simple example.

Proof
The Query
;WITH CTE1 AS (
SELECT
* 
FROM
dbo.Employees
),CTE2 AS (
SELECT
*
FROM
CTE1
)
SELECT
C1.EmployeeID,
C1.Name,
C2.EmployeeID,
C2.Name,
C3.EmployeeID,
C3.Name
FROM
CTE1 C1
INNER JOIN CTE2 C2
ON C2.EmployeeID = C1.EmployeeID
INNER JOIN CTE1 C3
ON C3.EmployeeID = C1.EmployeeID

Execution Plan

CTE

Please have a look at the execution plan. It clearly shows that even though the t-sql to fetch the data from the table is written only once inside CTE1, the Scan is done thrice. This is because CTE1 is referred thrice in the final query -
  1. Two times CTE1 is directly used.
  2. Once CTE1 is used indirectly via CTE2.
Conclusion
The CTE should be thought of a view that is defined for the current query only. At the time of execution, the query optimizer will replace all the direct/indirect use of CTE with the actual query in the same way as it does for Views.

Wednesday 30 November 2011

Using OFFSET and FETCH

 

Introduction

Many times while developing our applications we feel the need of pagination, where our User Interface (UI) has to list a number of records and fetching them all at once and listing is not a feasible option because of the following reasons -

  1. High utilization of the network bandwidth which if on a higher side might even choke up the bandwidth.
  2. User is not guaranteed to see the latest details in a multi user environment.
  3. High need of RAM on local machine for caching/processing.

So, the solution which is generally implemented in this situation was to fetch only the relevant records from the backend. Until Denali the following were the options used to counter this situation -

  1. Before SQL 2005 – ORDER BY clause in combination with TOP
  2. From SQL 2005 onwards – ROW_NUMBER() function with a WHERE clause

And from Denali, we can use ORDER BY clause in combination with OFFSET and FETCH

Implementation

Let’s see how we can get the same output using all of the 3 ways explained above and try to fetch records from 3 to 4 assuming page size to be 2.

Total Records are as under

AllData

1. ORDER BY + TOP

DECLARE @PageNo AS INT
DECLARE @PageSize AS INT
 
SET @PageNo = 2
SET @PageSize = 2
 
SELECT
  * 
FROM (SELECT
        TOP (@PageSize) *
      FROM (SELECT 
              TOP (@PageNo * @PageSize) *
            FROM 
              dbo.DemoTable DT
            ORDER BY
              ID ASC) X
      ORDER BY
        X.ID DESC) Y
ORDER BY
  Y.ID ASC


Output


Output


 


2. ROW_NUMBER() + WHERE



DECLARE @PageNo AS INT
DECLARE @PageSize AS INT
 
SET @PageNo = 2
SET @PageSize = 2
 
;WITH Data AS (  
SELECT
  *,
  ROW_NUMBER()OVER(ORDER BY DT.ID ASC) Rno
FROM
  dbo.DemoTable DT
)
SELECT 
  ID,NAME,CITY
FROM
  Data
WHERE
  Rno BETWEEN ((@PageNo - 1) * @PageSize) + 1 AND ((@PageNo - 1) * @PageSize) + @PageSize


Output


Output


 


3. FETCH + OFFSET



DECLARE @PageNo AS INT
DECLARE @PageSize AS INT
 
SET @PageNo = 2
SET @PageSize = 2
  
SELECT 
  *
FROM 
  dbo.DemoTable DT
ORDER BY 
  DT.ID
OFFSET ((@PageNo - 1) * @PageSize)) ROWS
FETCH NEXT @PageSize ROWS ONLY
Output


Output


Performance


I did a small test using all the 3 ways and have found the Denali (OFFSET and FETCH) way the best performing one followed by the ROW_NUMBER().


 


Conclusion


I would prefer using the Denali way just for 2 simple reasons -



  • Simplicity of code

  • Better performance

Remarks



  1. The Denali code is based on SQL Server Denali CTP 1 and might change after further releases.

Tuesday 29 November 2011

Workarounds for SSRS 2008 Font Rendering Issue in ReportViewer 10.0


Problem
A report developed using SSRS 2008 either through Visual Studio 2010 or BIDS 2008 does not renders properly when viewed in the Report Viewer 10.0 via Remote Desktop or any 3rd party applications using Terminal Services in the background.
Remote Rendering



Local Rendering



Workarounds
  1. Change the Screen Resolution to 1024 X 768 or any 4:3 aspect ratio for both the Remote machine as well as the local machine.
  2. Export the report as PDF and then take the print outs if required.
  3. If the above two solutions does not work for you, unfortunately you will have to look back to SSRS 2005.
Hope, this saves you some time of searching.

Saturday 1 October 2011

How to find out which Table is not having rows in SQL Server?

 

Introduction

Many times while tuning our production databases we might try to find out the list of tables not having even a single row of data. Today, I am going to show a simple script which could be used to get a list of tables having ZERO rows.

Script

USE DBName --Change this to the DB Name you want to script for.
GO
 
DECLARE @TableRowCount TABLE
( 
    TableName VARCHAR(255), 
    RowCnt INT 
) 
   
INSERT @TableRowCount 
  EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1),COUNT(*) FROM ?' 
 
SELECT 
    * 
FROM 
  @TableRowCount 
WHERE
    RowCnt = 0     
ORDER BY 
  RowCnt 
 
    

Friday 30 September 2011

How to find a string value in all the string columns of a table/view in SQL Server ?

 

Introduction

I am sure many times we all might have come across situations where we need to search/find a string value in all the string columns of a given table/view in SQL Server and return the matching rows from that table.

Unfortunately, we do not have any straight forward way to do this till date AFAIK. Hence, the below script can prove to be quite handy in this situation -

USE DBName --Replace with the DB in which the table resides
GO
 
--Declare variables and initialize them
DECLARE @TableSchema AS VARCHAR(50) = 'SchemaName' --Replace this with the name of Schema of the Table/View
DECLARE @TableName AS VARCHAR(50) = 'TableName' --Replace this with the name of the Table/View to search
DECLARE @SearchString AS VARCHAR(50) = 'SearchString' --Replace this with actual SearchString
 
DECLARE @Qry AS NVARCHAR(MAX)
DECLARE @Columns AS VARCHAR(MAX)
 
--Prepare the columns
SET @Columns = STUFF((
SELECT 
  '+' + CASE WHEN IS_NULLABLE = 'YES' THEN 'ISNULL(' + C.COLUMN_NAME + ','''')' ELSE C.COLUMN_NAME END
FROM 
  INFORMATION_SCHEMA.COLUMNS C
WHERE
  C.TABLE_SCHEMA = COALESCE(@TableSchema,C.TABLE_SCHEMA)
  AND C.TABLE_NAME = COALESCE(@TableName,C.TABLE_NAME)
  AND C.DATA_TYPE IN ('CHAR','NCHAR','NTEXT','NVARCHAR','TEXT','VARCHAR')
FOR XML PATH('')),1,1,'')
 
--Prepare the Query
SET @Qry = N' SELECT ' +
            '  * ' +
            ' FROM ' +
            @TableSchema + '.' + @TableName +
            ' WHERE  ' +
              @Columns + ' LIKE ''%' + @SearchString + '%'''  
 
--Execute the Query
EXEC SP_EXECUTESQL @Qry

Please note that the above script works only for the following column types - CHAR,NCHAR,NTEXT,NVARCHAR,TEXT,VARCHAR


Njoy searching….

Monday 22 August 2011

T-SQL to find Fragmented Indexes


Fragmentation of Indexes is one of the reason for low performing queries resulting in a poor application performance.
Today, I will present a simple script which will help in identifying the level of fragmentation in a Database.
--Replace this with the name of the Database for which we want to find the fragmentation.
USE <DBName> 
GO
 
DECLARE @DBName AS VARCHAR(10) = 'DBName'
DECLARE @DBID AS INT = DB_ID(@DBName)
DECLARE @AllowedFragmentation AS INT = 70 --A acceptable value in Percent(%) for fragmentation.
DECLARE @Qry AS VARCHAR(MAX)
 
SELECT
--@DBID [DBID],
--@DBName DBName,
PS.OBJECT_ID ObjectID,
COALESCE(T.name,V.name) ObjectName,
PS.index_id,
I.name IndexName,
PS.page_count AS TotalPages,
(PS.page_count * 8)/1024.0 as TotalMB,
((PS.page_count * 8)/1024.0) * (PS.avg_fragmentation_in_percent/100) as ReclaimableMB,
PS.avg_fragmentation_in_percent AvgFragmentationPercent
FROM
sys.dm_db_index_physical_stats (@DBID, NULL, NULL, NULL, NULL) AS PS
INNER JOIN sys.indexes AS I
ON PS.OBJECT_ID = I.OBJECT_ID
AND PS.index_id = I.index_id  
LEFT JOIN sys.tables T
ON T.object_id = I.object_id
LEFT JOIN sys.views V
ON V.object_id = I.object_id
WHERE
PS.database_id = @DBID
AND PS.avg_fragmentation_in_percent > @AllowedFragmentation 
ORDER BY    
PS.avg_fragmentation_in_percent DESC 

Here, I have considered 70% fragmentation as an acceptable level of fragmentation.

Hope, this helps.

Friday 29 July 2011

Lookup functions in SSRS 2008 R2 - III

Introduction

This is the final post to discuss the last Lookup function Multilookup(). The other two – Lookup() and LookupSet() have already been discussed in my previous posts.

Syntax

Multilookup(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound. The only difference from the previous lookup functions is that, here this is a VariantArray.

destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter. 

result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression for each row. 

Note, If multiple matches are found, the value from the first matching row will be returned for all the values in the source expression. And we can not use any aggregate functions in the result_expression.

Dataset – The Dataset in which we want to look the values into.

ReturnsA VariantArray, or Nothing if there is no match.

Example

Continuing with the same example from the previous posts of Employees & Departments, suppose we want to display the name of the departments to which the Employee is related to, we can use the following expression -

Table Structure of Employees Table

ID EmployeeID DepartmentIDs
1 1 1,2,3
2 2 2,3

Table Structure of Departments Table

ID Name
1 D1
2 D2
3 D3

Required Output

ID EmployeeID Departments
1 1 D1,D2,D3
2 2 D2,D3

To get the Departments, we can use the following expression in SSRS -

=Join(MultiLookup(Split(Fields!DepartmentIDs.Value,","),
   Fields!ID.Value,Fields!Name.Value,"Departments")),
   ",")

Here, we have used 3 functions -



  1. Split() – To convert the comma separated DepartmentIDs into a value array.

  2. Multilookup() – To find the Name of departments for the matching ID.

  3. Join() – Prepare the comma separated string for the names returned by the Multilookup() as array.

Conclusion


So, with this we have covered all the 3 lookup functions provided in SSRS 2008 R2 and have learned how to make best use of them to fetch data from multiple datasets.

Wednesday 27 July 2011

Lookup functions in SSRS 2008 R2 - II

 

Introduction

In my last post on Lookup functions related to SSRS 2008 R2, I had explained Lookup(). It is used to fetch the first matching value from the other DataSet. Now, what if we want all the matching values from the other DataSet. Here, the LookupSet() function comes handy. Let’s try to understand this.

Syntax

LookupSet(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound.

destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter. 

result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression for each row. 

Note, If multiple matches are found, all the values from the matching rows will be returned. And we can not use any aggregate functions in the result_expression.

Dataset – The Dataset in which we want to look the values into.

ReturnsA VariantArray, or Nothing if there is no match.

Example

Continuing with the same example from the last post of Employees & Departments, suppose we want to display the name of all the departments to which the Employee is related to, we can use the following expression -

=JOIN(LookupSet(Fields!DepartmentID.Value, Fields!ID.Value, Fields!Name.Value, "Departments"),",")

Here, we have used 2 functions of SSRS,



  1. LookupSet() – To get an Variant array of the matching values. Here, the DepartmentID field will be matched with the ID field of the Departments Dataset and the Name from all the matching rows will be returned.

  2. JOIN() – To join all the elements of the array as a comma separated string.

Hope, I have explained clearly the use of LookupSet() funciton. Next, I will blog about the last Lookup function – MultiLookup().