Microsoft VS 2015 - PerfTips

Microsoft recently released RTM version of VS 2015. As part of this release, there is an important feature which helps doing performance analysis of application called PerTips

What are PerfTips?

As you step over lines of code or run from breakpoint to breakpoint, the debugger will display a PerfTip (tooltips with performance information) in the editor at the end of the code line indicating how long the program was running during the previous step or since the last breakpoint. All you need to do is look at it!

The ≤ sign indicates that there is some debug overhead included in the PerfTip and that this number is showing you an upper bound of how long the code took to run. The upper bound allows you to identify which sections of code are running fast enough, and which sections of code may need further investigation. Notice in the screenshot above, that the PerfTip is shown after the line of code that will be executed next. If you hover over the PerfTip you will notice that it is a link:

When you click on the link it brings the Diagnostic Tools window into focus so that you can see the history of PerfTip values on the Events break track.

 

Sending HTML Table in Email Using SQL Server

•    Today I am going explain how we can send html table email from SQL server.  One of my client have requirement they want employee’s list are highlight with some background color when employee‘s basic salary is more than 15000 in Email.

•  Instead of going to application level I will manage things from SQL server and I have used SQL dbmail functionality which is available in SQL Server full version.

•  SQL DB email function, first we need to create profile name in SQL Server and need to configure with our domain.

•  First I have declare tables

DECLARE @tableHTML NVARCHAR(MAX)

• From Above query I have get Employees in html tables structure which include Basic Salary and  I have took those employee in “Red” background whose Basic Salary is more than 15,000 and rest of employee have white background.

• Generated  “tableHTML” looks like following in HTML form

• Now I need to send email of above template from SQL Server. We can use following query to send email to receipt

EXEC msdb.dbo.sp_send_dbmail

    @profile_name='Nilay',

    @recipients = 'qa1@orangewebtech.com',

    @from_address = 'nilay@orangewebtech.com',

    @subject = 'Employee List',

    @body = @tableHTML,

    @body_format = 'HTML';

• We can also used SQL Jobs for email list of employees on specified duration.

Rules(BCD) for Validate Regular Expression

B- There are three types of brackets used in regular expression.

      Square Bracket    - Specify the character which needs to be matched

      Curly Bracket “{” - Specify how many character

       Para thesis          - Specify for Grouping

C -Carrot “^” the start of regular expression

D –Dollar “$” the end of the regular expression

Eg. ^[a-g]{3}$

Above example will allow to enter character between a to g with maximum three character.

REPLACE SPECIAL CHARACTER FROM STRING IN SQL

• In my project, I have one .csv file and I load csv file into SQL server tables where SQL server table column have some time NVARCHAR(which include special character) columns

• Some time it got some special character in my table column (ex. In my alphanumeric employee code have ‘#’ or ‘?/’), I need to replace all special character with no space

•  I have option with replace function to remove unwanted space but it seems I need write series of replace function.

SELECT REPLACE( REPLACE( REPLACE( REPLACE(@str, '/', '' ), '#', '' ));

•  I need to create dynamic function which replaces all special character with no space.

      DECLARE @str VARCHAR(25)

      SET @str = '(NIL)AY-MISTRY''l123'

     SELECT PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str )

     WHILE PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str ) > 0

    SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX(               '%[~,@,#,$,%,&,*,(,)]%', @str ), 1 ),''),'-',' ')

     SELECT @str

• Above query we can also convert into SQL function to return no space string value.

• More about PATINDEX (Ref. Link)

  http://technet.microsoft.com/en-us/library/ms188395%28v=sql.105%29.aspx

How to use Pivot table in SQL

• Here I am going explain how and when to use PIVOT function in SQL

•  Let’s start with one example. I have one Salary table which contains three columns Emp_ID, Label_Name and Amount.  Label_Name have all salary perk for particular employee.

CREATE TABLE #SalaryDetail

(

   Emp_ID     NUMERIC,

   Label_Name VARCHAR(255),

   Amount     NUMERIC(18,2)

)

•   After that let’s fill some in data in Salary Detail table.

      INSERT INTO #SalaryDetail VALUES (1,'Basic_Salary',15000)

      INSERT INTO #SalaryDetail VALUES (1,'HRA',2000)

      INSERT INTO #SalaryDetail VALUES (1,'Conv_Allowane',800)

      INSERT INTO #SalaryDetail VALUES (2,'Basic_Salary',20000)

      INSERT INTO #SalaryDetail VALUES (2,'HRA',2000)

      INSERT INTO #SalaryDetail VALUES (2,'Conv_Allowane',800)

      Result

• The above script gives you the list of columns in string format with commas. Example:[Basic_Salary],[HRA],[Conv_Allowance]. If you pass this dynamic query to your pivot, then your pivot columns will display dynamically
• DECLARE @query AS NVARCHAR(MAX)
  DECLARE @pivot_cols NVARCHAR(1000);
  SELECT @pivot_cols =
          STUFF((SELECT DISTINCT '],[' + Label_Name
                 FROM #SalaryDetail
                 ORDER BY '],[' + Label_Name
                 FOR XML PATH('')
                 ), 1, 2, '') + ']';

•   The below SQL script creates the stored procedure which returns pivot table as output.
• SET @query =
  'SELECT * FROM
  (
      SELECT EMP_ID,Label_Name,amount
      FROM #SalaryDetail
  )Salary
  PIVOT (SUM(amount) FOR Label_Name
  IN ('+@pivot_cols+')) AS pvt'
  
  
  EXECUTE (@query)
  
  
  Output: