5/30/2018

Exclude 0 from value in SQL Server


Exclude 0 from value in SQL Server

 

Create Table #TempData

(

ColumnData Varchar(20)

)

 

Insert into #TempData values('063506038')

Insert into #TempData values('0063506038')

Insert into #TempData values('00063506038')

Insert into #TempData values('00000063506038')

 

 

Select * from #TempData

 

Select REPLACE(LTRIM(REPLACE( ColumnData,'0',' ')),' ','0')  From #TempData

Format Date in SSIS MM-DD-YYYY or MM/DD/YYYY DD-MM-YYYY or DD/MM/YYYY


Format Date in SSIS MM-DD-YYYY or MM/DD/YYYY DD-MM-YYYY or DD/MM/YYYY

Date Format MM-DD-YYYY:

RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2)

+"-"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +"-"+

(DT_STR,4,1252)DATEPART( "yyyy" , getdate() )

Date Format MM/DD/YYYY:

RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2)

+"/"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +"/"+

(DT_STR,4,1252)DATEPART( "yyyy" , getdate() )

Date Format DD-MM-YYYY:

RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +"-"+

RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2)

+"-"+(DT_STR,4,1252)DATEPART( "yyyy" , getdate() )

Date Format DD/MM/YYYY:

RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +"/"+

RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2)

+"/"+(DT_STR,4,1252)DATEPART( "yyyy" , getdate() )

5/25/2018

Mostly used Expression in SSIS



SSIS Mostly used expressions:

Here are few examples of expressions for SSIS.

Example
Description
"C:\\Project\\DataFile" + (DT_WSTR, 30)(DT_DBDATE)GETDATE() + ".csv"
This will provide a File path with current date in file name as C:\Project\ DataFile2018-05-25.csv
ROUND(Amount, 2)
Round of two decimal places for a given value/column
"C:\\XYZ\\Files\\" + (DT_WSTR,4)YEAR(DATEADD("dd", -1, GETDATE())) + "-"
    + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("dd", -1, GETDATE())), 2) + "-"
    + RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("dd", -1, GETDATE())), 2) + ".txt"
An expression, but this time deriving the file name based on yesterday’s date, useful for loading the previous day's data:
C:\XYZ\Files\2018-05-24.txt
ISNULL(ColumnName)||TRIM(ColumnName)==""?"Unknown": ColumnName
In this example, the statement determines that if the ColumnName is blank or NULL, it will be set to unknown. To make a Logical AND condition, use "&&" instead of the "||" operator.
(DT_DBTIMESTAMPOFFSET,3) "1999-10-11 20:34:52.123 -3:30" != (DT_DBDATE)"1999-10-12"
The system converts the expression,(DT_DBDATE)"1999-10-12", to DT_DBTIMESTAMPOFFSET. The example evaluates to TRUE because the converted expression becomes "1999-10-12 00:00:00.000 +00:00", which is not equal to the value of the other expression,(DT_DBTIMESTAMPOFFSET,3) "1999-10-11 20:34:52.123 -3:30".
(DT_I4) 3.57
Casts a numeric value to an integer.
(DT_STR,1,1252)5
Casts an integer to a character string using the 1252 code page.
(DT_WSTR,3)"Cat"
Casts a three-character string to double-byte characters.
(DT_DECIMAl,2)500
Casts an integer to a decimal with a scale of two.
(DT_NUMERIC,7,3)4000
Casts an integer to a numeric with a precision of seven and scale of three.
(DT_STR,50,1252)FirstName
Casts values in the FirstName column, defined with an nvarchar data type and a length of 50, to a character string using the 1252 code page.
(DT_BOOL)"True"
Casts the string literal "True" to a Boolean.
(DT_DBDATE) "1999-10-11"
Casts a string literal to DT_DBDATE.
(DT_DBTIME2, 5) "16:34:52.12345"
Casts a string literal to the DT_DBTIME2 data type that uses 5 digits for fractional seconds. (The DT_DBTIME2 data type can have between 0 and 7 digits specified for fractional seconds.)
(DT_DBTIMESTAMP2, 4) "1999-10-11 16:34:52.1234"
Casts a string literal to the DT_DBTIMESTAMP2 data type that uses 4 digits for fractional seconds. (The DT_DBTIMESTAMP2 data type can have between 0 and 7 digits specified for fractional seconds.)
(DT_DBTIMESTAMPOFFSET, 7) "1999-10-11 16:34:52.1234567 + 5:35"
Casts a string literal to the DT_DBTIMESTAMPOFFSET data type that uses 7 digits for fractional seconds. (The DT_DBTIMESTAMPOFFSET data typecan have between 0 and 7 digits specified for fractional seconds.)
DATEADD("Month", 1,GETDATE())
Adds one month to the current date.
DATEADD("day", 21, ModifiedDate)
Adds 21 days to the dates in theModifiedDate column.
DATEADD("yyyy", 2, (DT_DBTIMESTAMP)"8/6/2003")
Adds 2 years to a literal date.
DATEDIFF("dd", (DT_DBTIMESTAMP)"8/1/2003", (DT_DBTIMESTAMP)"8/8/2003")
Calculates the number of days between two date literals. If the date is in "mm/dd/yyyy" format, the function returns 7.
DATEDIFF("mm", (DT_DBTIMESTAMP)"8/1/2003",GETDATE())
Returns the number of months between a date literal and the current date.
DATEDIFF("Week", ModifiedDate,@YearEndDate)
Returns the number of weeks between the date in theModifiedDate column and theYearEndDate variable. IfYearEndDate has a date data type, no explicit casting is required.
DATEPART("month", (DT_DBTIMESTAMP)"11/04/2002")
Returns the integer that represents the month in a date literal. If the date is in mm/dd/yyyy" format, this example returns 11.
DATEPART("dd", ModifiedDate)
Returns the integer that represents the day in theModifiedDate column.
DATEPART("yy",GETDATE())
Returns the integer that represents the year of the current date.
DAY((DT_DBTIMESTAMP)GETDATE())==1||DAY((DT_DBTIMESTAMP)GETDATE())==15?1:2
Can be used to set the LoggingMode property of a package. The expression uses the DAY and GETDATE functions to get an integer that represents the day datepart of a date. If the day is the 1st or 15th, logging is enabled; otherwise, logging is disabled. The value 1 is the integer equivalent of theLoggingMode enumerator member Enabled, and the value 2 is the integer equivalent of the member Disabled. You must use the numeric value instead of the enumerator member name in the expression.
"PExpression-->Package: ("
+ @[System::PackageName] + ") Started:" + (DT_WSTR, 30) @[System::StartTime]
+ " Duration:" + (DT_WSTR,10) (DATEDIFF( "ss", @[System::StartTime] , GETDATE() ))
+ " seconds"
Can be used to set the Subjectproperty of a Send Mail task and provide a useful e-mail subject. The expression uses a combination of string literals, system variables, the concatenation (+) and cast operators, and the DATEDIFF and GETDATE functions. The system variables are the PackageNameand StartTime variables.
"Rows Processed: "
+ "\n" +" NASDAQ: "
+ (dt_wstr,9)@[nasdaqrawrows]
+ "\n" + " NYSE: "
+ (dt_wstr,9)@[nyserawrows]
+ "\n" + " Amex: " + (dt_wstr,9)@[amexrawrows]
Can be used to set theMessageSource property of a Send Mail task. The expression uses a combination of string literals, user-defined variables, and the concatenation (+) operator. The user-defined variables are namednasdaqrawrows,nyserawrows, andamexrawrows. The string "\n" indicates a carriage return.
DATEPART("weekday", GETDATE()) ==2?"notepad.exe":"mspaint.exe"
Can be used to set theExecutable property of an Execute Process task. The expression uses a combination of string literals, operators, and functions. The expression uses the DATEPART and GETDATE functions and the conditional operator.
(DATEPART("DW",GETDATE()) == 7) ? "SUNDAY" : (DATEPART("DW",GETDATE()) == 1) ? "MONDAY" : (DATEPART("DW",GETDATE()) == 2) ? "TUESDAY" : (DATEPART("DW",GETDATE()) == 3) ? "WEDNESDAY" : (DATEPART("DW",GETDATE()) == 4) ? "THURSDAY" : (DATEPART("DW",GETDATE()) == 5) ? "FRIDAY" : (DATEPART("DW",GETDATE()) == 6) ? "SATURDAY" : "NULL"
 Weekday name

Month Name and Day Name in SSIS expression


Month Name and Day Name in SSIS expression:

 

In many scenarios we need to evaluate month name or days name from date in SSIS expression, for an example, we have to pick file from a shared location where folder name is based on current month so in that case we need month name as per date to access that folder with month name as:

C:\abc\Files\2018\May\Monday\ABC.txt

So here you can see we have to access folder May and then if today is Monday then need to pick file from Monday folder so for this we can create a variable in SSIS package and use below expression to define path as:

"C:\\abc\\File\\ " + (DT_WSTR, 4) YEAR( Getdate()) +"\\"+

(MONTH(getdate()) == 1 ? "01. Jan" : MONTH(getdate()) == 2 ? "02. Feb" : MONTH(getdate()) == 3 ? "03. Mar" :

 

  MONTH(getdate()) == 4 ? "04. Apr" : MONTH(getdate()) == 5 ? "05. May" : MONTH(getdate()) == 6 ? "06. June" :

 

  MONTH(getdate()) == 7 ? "07. July" : MONTH(getdate()) == 8 ? "08. Aug" : MONTH(getdate()) == 9 ? "09. Sep" :

 

  MONTH(getdate()) == 10 ? "10. Oct" : MONTH(getdate()) == 11 ? "11. Nov" : MONTH(getdate()) == 12? "12. Dec":"")

 

+"\\"+ (DATEPART("dw",GETDATE())==1? "Sunday": DATEPART("dw",GETDATE())==2?"Monday":

 

DATEPART("dw",GETDATE())==3?"Tuesday": DATEPART("dw",GETDATE())==4?"Wednesday":

 

DATEPART("dw",GETDATE())==5?"Thursday": DATEPART("dw",GETDATE())==6?"Friday":

 

DATEPART("dw",GETDATE())==7?"Saturday":"" )

 

+"\\ABC.txt "

 
Here in above code, you can see I have used two functions to find Month name and Day name to create path.

Below are expressions:

Expressions for Month Name:

(MONTH(getdate()) == 1 ? "Jan" :

MONTH(getdate()) == 2 ? "Feb" :

MONTH(getdate()) == 3 ? "Mar" :

MONTH(getdate()) == 4 ? "Apr" :

MONTH(getdate()) == 5 ? "May" :

MONTH(getdate()) == 6 ? "Jun" :

MONTH(getdate()) == 7 ? "Jul" :

MONTH(getdate()) == 8 ? "Aug" :

MONTH(getdate()) == 9 ? "Sep" :

MONTH(getdate()) == 10 ? "Oct" :

MONTH(getdate()) == 11 ? "Nov" :

MONTH(getdate()) == 12? "Dec":"")


Expressions for Day Name:

DATEPART("dw",GETDATE())==1? "Sunday":

DATEPART("dw",GETDATE())==2?"Monday":

DATEPART("dw",GETDATE())==3?"Tuesday":

DATEPART("dw",GETDATE())==4?"Wednesday":

DATEPART("dw",GETDATE())==5?"Thursday":

DATEPART("dw",GETDATE())==6?"Friday":

DATEPART("dw",GETDATE())==7?"Saturday":""

 

5/17/2018

Query performance approach in SQL Server - Simplest way to improve query performance - Part 1


Simplest way to tune SQL Query Part 1

If your query is running slow than you can use following ways to improve its performance. These are my real time scenarios where I got success by implementing these methods to improve performance of the query.
Method 1: Avoid “Select * from”, use column name i.e. Select Column1, Column2…:

To fetch the result suppose if you are having 10 columns in your table and you need only 5 out of 10 for your result than use all 5 columns name only separated by comma in Select statement instead of using as:
Correct Method:

Select Emp.FirstName, Emp.LastName, Emp.Salary, Dept.DeptName

FROM #Employee As Emp

LEFT JOIN #Dept As Dept  ON Emp.DeptID=Dept.DeptID
Incorrect Method:

SELECT * FROM #Employee As Emp

LEFT JOIN #Dept As Dept  ON Emp.DeptID=Dept.DeptID

Method 2: Use Key columns (Index columns) for filter and join:
If you have indexes in your tables which you are querying or joining, then try to use those columns if possible for joining condition and use in where clause to filter data, this will increase performance of your query.

Method 3: Never use Cast or convert in Where clause and in Join condition:
Suppose if you have Date filter in your table and if you are using Cast or Convert operator while filtering your data than you should avoid it. It will make your query much slower. And same for Join condition also.

Method 4: Use CTE result set for big table or expression based table to join with other table:
Select Column1, Column2,.....ColumnN

From TableA

left join TableB  on TableA.ColumnN = TableB.ColumnN     

left join TableC  on TableB.ColumnN = TableC.ColumnN

left join TableD  on TableC.ColumnN = TableD.ColumnN

Where TableA.Column1 = 0 and TableA.Date >= '01/01/2018'

As per given above query example; we have four  tables to join with each other based on conditions so first table here is a big table where we have millions records and another table also big one. But you can see in Where clause, we have filters for TableA only so If we run this query, than it will take time to give output so we can re-write in an efficient way with CTE as:

WITH TableACte

(

Select Column1, Column2,.....ColumnN

From TableA

Where TableA.Column1 = 0 and TableA.Date >= '01/01/2018'

)

Select Column1, Column2,.....ColumnN

From TableACte

left join TableB  on TableACte.ColumnN = TableB.ColumnN     

left join TableC  on TableB.ColumnN = TableC.ColumnN

left join TableD  on TableC.ColumnN = TableD.ColumnN

NOTE: In my Scenario, My query was taking 12 minutes to execute and now with this approach, it is giving result in less than 1 minute as Data is in billions.

Method 5: Avoid Case or any expression i.e. If condition in Where and Join condition
If you are using Case or If condition in Where clause or Join condition in your query than it will make your query very slow if data is huge because for each row it will calculate condition and will apply for filter in Where clause and join condition. So find alternate ways to do same or you can use sub query to build derived column.

Method 6: Try to use non-text columns for where clause and Join condition, use only Key columns where Datatype is not varchar() or nvarchar().
Always best method to use non text columns for join condition and if possible for Where clause also for filtering data because Text value will take time to get filter and join data.

Stay Tune for more methods in next part....
 

Tableau interview questions and answers for experienced professionals

  Tableau Interview Questions and Answers for experienced professional 1. What is TABLEAU? Tableau  is the powerful and fastest visualizing ...