Apply Case statement in Where clause in SSMS

Here i will explain, how can we apply Case statement in Where clause.
Look on the example given below.

I am having a Table called DIM_DATE.
This table contain 4 column (DATE, MONTH_OF_YEAR ,WEEK_OF_YEAR,YEAR)

Now look on the below query :-

DECLARE  @Rpt_Parm_Is_Week BIT

        ,@Rpt_Parm_Month INT

        ,@Rpt_Parm_Week INT

           

SELECT DATE,MONTH_OF_YEAR ,WEEK_OF_YEAR,YEAR FROM DIM_DATE

 WHERE YEAR=2015

           

 AND MONTH_OF_YEAR = CASE WHEN @Rpt_Parm_Is_Week = 1 THEN  MONTH_OF_YEAR ELSE @Rpt_Parm_Month  END

           

 AND WEEK_OF_YEAR =  CASE WHEN @Rpt_Parm_Is_Week = 0 THEN WEEK_OF_YEAR ELSE @Rpt_Parm_Week END    

Now   will pass some value into those parameter.

 @Rpt_Parm_Is_Week = 1

 @Rpt_Parm_Month = 5

 @Rpt_Parm_Week=21

 Output: Will get only one week(21) value for particular month(5) in 2015 year.

Now   will pass fasle value into @Rpt_Parm_Is_Week parameter.

 @Rpt_Parm_Is_Week = 0

 @Rpt_Parm_Month = 5

 @Rpt_Parm_Week=21

Output: Will get whole one month(5) record for 2015 year.

STUFF function in SQL Server

This function is used to replace new string from the start position of Given string and replace the Number of character in Given string.

Example: Select Stuff ('Pritam', 3, 3, '***')

Here we have given a string "Pritam" and want to replace "***". In middle of this 2 string we have used 3,3.

The first 3 will count the character position of "Pritam". Then It will replace the next 3 character from position 3 and insert "***".(Each * for each position)

Now we will do more research on Stuff function.

As Microsoft mentioned:

If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the start position is 0, a null value is returned. If the length to delete is longer than the first string, it is deleted to the first character in the first string.

An error is raised if the resulting value is larger than the maximum supported by the return type.

Select Stuff ('Pritam', 0, 3, '***') ---> Output: NULL

Select Stuff ('Pritam',-1, 3, '***') ---> Output: NULL

Select Stuff ('Pritam',6, 6, '***') ---> Output: Prita***

Select Stuff ('Pritam',1, 10, '***') ---> Output: ***

Select Stuff ('Pritam', 6,3, '******************') 

Output: Prita******************

Difference between Stored Procedure and Function in SQL Server

1. Stored Procedures are compiled when it runs first time in Database but, Function is compiled and executed every time when it is called.

2. Stored Procedures can have input/output parameters whereas Functions can have only input parameters.

3. Stored Procedures, we can declare both DDL/DML operation But, In Function we can't.

4. We can execute function inside Stored procedure whereas we can't execute  Stored procedure inside function.

5. We can declare Error handling (Try. Catch) inside Stored procedure But, for Function we can't.

6. Stored Procedures cannot be embedded in a SELECT statement whereas Function Can.

7. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can.

Concept of INDEX in SQL Server

A table without Primary key(Index) or Foreign key(Index) is called Heap Table.

Here data s are store in no particular order.

First we will know the storage architecture of Normal column and INDEX column in a table.

Now will see the Scanning style of 2 different case of  Normal column and INDEX column.

In Normal column it will always do full scan of table.When the search item is match with existing data then it will scan till last for search any other similar value. But, in INDEX column it search the data by Depth first search(DFS).

For example we have a table Student_Details (Name,Stream,Semester). Now i am inserting some data into it. Its look like below picture.

Now if want to search particular data from  Student_Details table..then it will full scan the entire table and give the output. (If Search data is matching with Existing data, then also it will full scan all the rows in  Student_Details table)

In above picture It get search item in 1st attempt but it goes for full scan.

So if this table having more records then it will take more time to process, which is inefficient.

To solve such kind of performance issue the concept INDEX came. INDEX find the data without touching all rows in a table (without full scan of table). 

There are 2 types of Index.

1. Cluster Index (Physical)

2. Non Cluster Index (Logical)

Cluster Index: It sorts the data with respect to the  raw data column from base table, that's why we can create only single Cluster Index in a table. The leaf nodes of a clustered index contain the data pages.

So, when we search any data from table, then data request will go to Cluster Index pages 

and directly goes to base data table. It is more faster than other Data scan.

For example, Now we Search all the data for a name called "Anurag Das". Like below picture without full table scan it will search "Anurag Das" by 2 steps. Compare to full scan (11 times),it is more efficient.

Why only one clustered index in a table?

Because a clustered index keep the same order in which the rows will be stored on disk, having more than one clustered index on one table is impossible. 

Now will go for another INDEX called Non Cluster.

Non Cluster INDEX:  Here before raw data layer, one more data layer called "Pointer Data layer/Reference Data layer" is created. So, when we search any data from table then data request will go to Non Cluster index pages after that goes to Reference (Pointer) data layer and from there it goes to base data table.

It is slower than Cluster Data scan because it is having a extra layer.

It holds actually a pointer to the data in rows so we can have as many non-clustered indexes in a table. Now highest range For Non Cluster index 999 on SSMS 2012 version and 249 for SSMS 2005.

The way of Non Cluster Index scan is as picture given below.

Deploy SSRS Report using RS utility (rs.exe)

To deploy the report Automated, we have to follow some steps given below.

Prerequisites:

1. Get the .exe files from local or Online. https://msdn.microsoft.com/en-us/library/ms162839.aspx

2. DOS needs to be installed.

Step 1: Go to the folder where you save this .exe files. (Here you can see 3 types of file- .exe, .exe.cofig, .cmd)

 

Step 2: Now Copy the .rdl file from your report solution and paste these in a same folder.

Step 3: Right click on "UserSSRSS.exe.config" file and "Edit" it through notepad. Edit as per picture given below.

Step 4: Now Right click on ".cmd" file and "Edit" it through notepad. Edit as per picture given below.

 

here PAD1 is the variable for report path.

Step 4: Now go to CMD and follow the picture in below.

Here you need to give the cmd file name follow by DB server name and DB name. Then Press Enter button.

Output: Now you can get a screen like this. "Deploy Report Completed"