SQL Server Reporting Services ~ MultiValued Parameters

This is a really good article on multivalued parameters in a stored procedure. Very good read for SQL Server Reporting Services.

Tech Updates

In SSRS reports we can have multi valued paremeters, which means we can select multiple values for that parameter and based on all those selected values result in the report is shown. We can write the SQL query to pass those parameter’s values and populate the data set, but sometimes it is required or to optimize the performance we need to use the stored procedure instead of direct sql query. This stored procedure will be taking the report paremeter as input and returing the result set to populate the data set.

In case of sql query as well as stored procedure, all selected values of the multi-value parameter will be passed as ‘,’ (comma) separated. So we should use IN clause in WHERE to impose the desired condition.

Problem: If we use stored procedure passing an input parameter which is multi-value parameter, SSRS will take all those selected multiple values…

View original post 398 more words

Advertisement

Monitoring SQL Server with Profiler ~ 5 Things to Avoid

SQL Profiler

Everyone loves Profiler and for good reason, it provides you with good data to help you make your SQL Server run even better. To quote FDR, “great power involves great responsibility.” Here is a brief list of things to avoid.

Run Profiler on the same server as SQL Server.

This adds too much overhead to the server, please don’t do it, EVER! Ideally, run it on a test server that has some good system resources to be able to capture and analyse all that data.

Select too many events to trace.

For example, selecting Batch Begin and Batch Completed. Choose only what you need.

Don’t filter the results.

Filtering saves you time crawling through the results and saves SQL Server time. This falls back on number 3, choose only what you need.

Don’t join the Performance Monitoring data with Profiler data.

Why chase down a problem, that is not the real cause of your slow server? While you may see a small performance boost on the server, you may be missing the elephant in the room.

SQL Server String PadLeft

I think this comes up a lot in day to day operations. Needing to pad the left hand side of a char/varchar field. T0 address this I made a handy dandy function that works similar to the padleft string function in .Net. There is a caveat to this code, if string length also sets the max length of string being returned. Below is the code, please test it for your application before putting it into production to make sure it has the behaviour that you need.

CREATE FUNCTION [dbo].[PadLeft](
    @StringLength INT,
@String VARCHAR(1000),
@PadChar CHAR(1)
)
RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @Result VARCHAR(1000)
    DECLARE @PadLength INT;
    SET @PadLength = @StringLength –LEN(@String)
    IF @PadLength <= 0
    BEGIN
        SET @Result =LEFT(@string,@StringLength)
    END
    ELSE
    BEGIN
        SET @Result =REPLICATE(@PadChar,@PadLength)+ @String
     END
    RETURN @Result
END

SQL Server CTE ~ Hooray for Common Table Expressions

What is a SQL Server CTE?

CTE stands for Common Table Expression.

A CTE is basically a subquery. So why is it special? This is why they are good:

• Separate a subquery from the body of the main query.
• Cleaner code – CTEs enable you to separate your logically congruent code segments.
• Can be used for improving the execution plan of your query.
• Can be used for recursive, hierarchical queries.

When aren’t they good? Almost never. Basically, if you don’t need a subquery don’t use a CTE. Also don’t bother using a CTE when you are performing a query with resources outside of SQL Server, there are negative performance issues in SQL Server 2005, but is fixed in SQL Server 2008. Here is everything you will need to know about CTEs.

CTE Basics

Books Online

Recursive CTE