September 6, 2016

SSIS - Lookup Transformation Different Cache Mode

SSIS Lookup component plays vital role to search existing record in the Data Flow. There are three different cache modes available in this transformation and it is very important to know/understand how these modes affects the performance of the package.

1) Full Cache
2) Partial Cahche
3) No Cache

By default, we will see Lookup component will select Full cache. Once the 

July 5, 2016

SQL Server Query Optimization

In order to optimize the long-running queries, we need to take following steps:

1) Make sure to use correct data type and length for the table columns.

2) Always try to get the necessary columns, never use SELECT * FROM tblName.
SELECT col1, col2 FROM tblName

3) Don't use sub-queries. Try to make temp table and then use it where it is necessary.

4) Try to use filter with WHERE clause to get necessary rows/records.

5) Don't use JOIN unless it is necessary, try only using INNER JOIN.

6) Try avoiding LEFT JOIN, use Not Exists/Exists if needed.

7) Don't use ORDER BY clause unless it is needed.

8) Instead of using CURSOR,  always use a loop

9) Make sure to use right Index, and remove unwanted Index.

10) Use Execution Plan on SQL Server Management Studio, and identify where the execution plan is using Index scan, Index seek, lookup etc and use the suggested index.

11) Try to partition the large tables, and if the table is extremely large try to partition the index as well.

May 4, 2016

Getting Nth Position Char Index

I had to write a function which will return NthCharIndex when text, subtext and position to return its char index. Below function will help to get it.

@text    nvarchar(MAX),
@subText nvarchar(MAX),
@numCharIndex INT
DECLARE @subTextIndex INT
DECLARE @lastSubTextIndex INT
DECLARE @count INT = 0

SET @subTextLen = LEN(@subText)
SET @startPos = 1
SET @lastSubTextIndex = 0

SET @subTextIndex = CHARINDEX(@subText, @text, @startPos)

WHILE @subTextIndex > 0
IF @count = @numCharIndex

SET @lastSubTextIndex = @subTextIndex
SET @startPos = @subTextIndex + @subTextLen
SET @subTextIndex = CHARINDEX(@subText, @text, @startPos)
SET @count = @count + 1

RETURN @lastSubTextIndex


SELECT [Control].[GetNthCharIndex]('Avesh Dhakal', 'a', 1)

Output: 1

SELECT [Control].[GetNthCharIndex]('Avesh Dhakal', 'a', 2)

Output: 9

SELECT [Control].[GetNthCharIndex]('Avesh Dhakal', 'a', 3)

Output: 11

March 14, 2016

SSRS: The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is '162'. The expected version is '147'.

My laptop has 2 SQL Server instances namely SQLServer2008R2 and SQLServer2012. Today I messed up 2008 R2 reporting services when I was trying to configure 2008 R2 Reporting Services using SQLServer2012 Reporting Services Configuration Manager because for some reason 2008R2 Reporting Services Configuration Manager was denying to connect with the 2008R2 reporting server.

When I was trying to open report server through browser it was throwing an error "The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is '162'. The expected version is '147'.". It made me really frustrated. On ReportServer DB, I found a table named [ReportServer].dbo.[ServerUpgradeHistory]. It contains information about the Server Upgrade. I updated 162 to 147 and it worked well.

SET tbl.ServerVersion = '162'
FROM [ReportServer].dbo.[ServerUpgradeHistory] tbl
WHERE UpgradeID = 3

February 16, 2016

Return Date Part from DATETIME - SQL Server

Most of the time we need date part only from datetime format in SQL Server. 

1) Old method

2) On SQL Server 2008 and higher

Above two methods helps to keep original date format and returns date part only from datetime.

3) If you want in specific date format (US date format)

February 1, 2016

SSRS Default Date Parameters

Default date value in SSRS will help to provide the date/time range (Start and End Date) as needed. This will help to reduce the report execution time, it also helps end users because they don't need to provide date range every time they run the report.

For Example:

1)Beginning of current month
=DateSerial(Year(Date.Now), Month(Date.Now), 1)

2)End of current month
=DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, DateSerial(Year(Date.Now), Month(Date.Now), 1)))

3) Beginning of  last month

=DateAdd(DateInterval.Month, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))

4) End of last month
=DateAdd(DateInterval.DAY, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))

5) End of last month 11 PM
=DateAdd(DateInterval.Hour, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))

1) Double click on the date parameters.

2) Click on "Default Values". Select "Specify values" and click on Add Button and then function (fx)

3) Then, provide the formula and click OK.