A Interview question How to determine what value you have updated of a column ? A output clause example #TIP 48

 

Problem:-

Sometimes it may require that whatever you have updated you want to know what was the previous value of that column?

It was asked by many interviewer in the interview how to avoid trigger or how to know what was previous value before update ?

Solution:-

Now to achieve this the basic step is before updating the record select those value and have it in a temporary variable.

But We are doing here by a new feature (not exactly new introduced in SQL Server 2005) which is OUTPUT CLAUSE

Suppose I have tbluser table in which a user record with id 1 having userame “staff”

Now I have to update this in capital letter. To achieve this we have to write following query

UPDATE tblUser
SET UserName = ‘STAFF’
OUTPUT INSERTED.UserName,
   deleted.UserName
WHERE userId= 1
   

When you run it you will get output as shown below

Output_Clause

 

I hope it may helpful you somewhere enjoy !!!

RJ!!!

Find first not null value from different columns TIP # 47

 

Problem:

Sometimes, it might be possible that we need not null value only from particular columns and if all column have null value then we provide a default value.

Lets understand this by a general and very interesting example

suppose a friend come to your house and you want to give him a treat then you check your first column or we can say first option “Is there any thing to eat ?” if that value is null then you go for second column or we can say second option “ Is there any thing to drink ?”

If that value is also null then you will choose 3rd or default option and ask friend to give you treat.

Isn’t it simple Smile. Just kidding Open-mouthed smile

Let’s understand  now with adventurework’s product table.

Suppose, We want to fetch productId, productname,product number, and any property (either color, class) and if both the column (color, class) are null then need to display “No Property found” in the column value.

so  I wrote following query  to achieve this

SELECT PRODUCTID ,
      Name,
     ProductNumber,
COALESCE(Color,class,’No Property found’) As productProperty
FROM [Production].[Product]

COALESCE

so if you observer above figure you will find in the records where color found color value appear and if color value is null and class value found the class value appear and if both color and class value is null then we provide simple value which is “No Property found”

I hope this may help you.

Thanks  & Enjoy

RJ !!

Performance tips – How to determine last statistics update in table ? TIP #46

Hi,

For maintenance purpose we need to determine when the stats last update and analyze the data and if data is out dated then we need to update the stats.

To determine this we need to run the following scripts

SELECT OBJECT_NAME(s.object_id) AS [ObjectName]
      ,s.name AS [StatisticName]
      ,STATS_DATE(s.object_id, [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats s
INNER JOIN sys.objects obj ON obj.object_id = s.object_id
AND obj.type IN (‘U’,’V’)
ORDER BY STATS_DATE(s.object_id, [stats_id]) Asc,obj.name

 

For example I run the following code in adventurework2012

Last_Updated_Stats

 

So according to update status date we do update statistics of those tables in database.

I hope this tip will help you.

Enjoy !!!

RJ

3 different way to find row count of a table ? TIP #45

 

Problem:-

Many times you want to know how many rows exists in the particular tables.

So let me share 3 different way to know this thing

Solution:-

Let me share 3 different options

Option 1:-

You all aware of this term which is Count function

SELECT COUNT(1)  As Rows FROM [Person].[Person]

Option 2:-

Sp_Space used is another way to determine rows in table as shown in below figure

No_Of_Rows_Sp_Space_used

Option 3:-

it is little bit tricky but you will enjoy seeing this. We count the row number from cluster index

Partitionrownumber

 

I hope you enjoyed.

Thanks & Enjoy !!!

RJ

First_value & Last_Value according to group set is that easy ? tip #44

 

Problem:- 

Most of the time we require data in which we require first value and last value from different group of rows. Now how easy we can get result this is one of the challenge for us.

Solution:-

Lets understand this by an example. Suppose you have a sales table in which you maintain daily sales. Now your want a result sent in which you know what is first sale of the day and what is last sale of the day.

SQL SERVER 2012 provides you facility to achieve this task easily with First_Value & Last_Value function.

The syntax of first_value & Last value is exactly same as Row_Number, Dense_Rank & Rank_function.

See below example in which I have used Adventureworks SalesOrderHeader table.

Now if you see below snap I took a random date 2005-07-12 and fetched record and highlighted is first row & Last Row.

So on date 2005-07-2012 Sales order have 3953.9884 as a frist value and  772.5036 as a second value.

Date_First_Last_example1 

Now above specific result  we can achieve by first_Value & Last_Value function of SQL SERVER 2012 as shown below

First_Value_Last_Value_Rj

so, In this way you can achieve the first_Value & last_Value from a group of rows

I hope this may help you somewhere.

Enjoy !!!

RJ

Interview Question- How conditionally sort the records ? TIP#43

 

Problem:-

Can we sort records according to particular condition ?

Solution:-

Most of the time developer faces this challenge of sorting records conditionally. I know  many of us faces this question in interview.

Lets understand this by an example.

Suppose I have a person table in database and  I want to sort the records while fetching from database. Records sorting is depended on a variable which is passed by the consumer from front end.

So if Sort variable 1 then we have to sort the records by  first Name

If Sort variable is 2 then we have to sort the records by Last Name

Else we have to sort by Middle Name

To achieve this I have created following stored procedure

CREATE PROCEDURE proc_SortPerson
@SortBy  TINYINT    –  if one then sort by first name if 2 sort by last name else sory by middlename
AS
BEGIN
  SELECT *
  FROM [Person].[Person] WITH(NOLOCK)
  ORDER BY (CASE  @SortBy WHEN 1 THEN FirstName
                       WHEN 2 THEN LastName
                       ELSE MiddleName
                       END)
END
GO

Now when  I execute this by specific parameter result sorted according to that variable value.

See below snaps for proof of concept

SortByFirstName

Sort by Last Name when @sortby value  = 2

sortbyLastName

You can add any condition according to your business need.

Thanks

Rj !!

Boost query performance with avoiding basic mistake with Select statement. Performance Tip – #42

 

Problem:-

My query is slow what are the basic things I can do to get good performance without going for indexes.

Solution:-

May be this solution help you which I am describing here or it is possible you already aware of it.

 

(a) Avoid function in column :-The most basic tip is avoid any function in where clause which applied on table column.

lets understand this by few examples

Suppose your query written as follows

SELECT * FROM person.person WHERE Substring(FirstName,1,1)= ‘K’

OR

SELECT * FROM person.person WHERE LEFT(FirstName,1)= ‘K’

(b ) Avoid implicit conversion:-  Try to provide exact data type to avoid implicit conversion.

For example if we have firstname column in table having data type nvarchar(50) and we are comparing it with Varchar(100) then at the time of query it convert implicitly.

Lets understand with following query

DECLARE @Name AS VARCHAR(50)
SET @Name = ‘K%’
SELECT * FROM Person.Person WHERE FirstName Like @name

When we run above query we get following query plan

implicitConversion

If you see highlighted yellow it shows that query implicitly converted for changing data type

Now above query can be written as follow to avoid implicitly conversion

DECLARE @Name AS NVARCHAR(50)
SET @Name = N’K%’
SELECT * FROM Person.Person WHERE FirstName Like @name

Now when you run above query  you will get following query plan

NoConversion

So if you see there is no extra conversion in above figure.

I hope this small tips help you in  performance.

Thanks

RJ

Dense_Rank is exact same but it avoid gaps in ranking TIP#39

 

As discussed in last tip #37 Dense rank having exactly same syntax like Rank the only difference between rank & dense rank is that “dense_Rank fill the gap or avoid gap in ranking means if two row set have same ranking then just after record will have just next ranking.

I am taking same example as we discussed in 27 and using Dense_Rank function.

The syntax is as follow

SELECT  Dense_Rank() OVER (Partition BY column name Order by column name ASC/DESC) , other columns FROM tableName

Now example

dense_rank

 

I will club all this ranking function in next tip and discuss difference.

Enjoy!!

RJ

Rename column by SQL command TIP #38

 

Recently, One of my friends asked How to rename a column by SQL command frankly speaking I am not used too of using this command I prefer UI interface.

Lets understand this by an example.

Suppose I have a users table in which there is a column with name status which should be statusId but by typo mistake I added satus column.

Now below is command to rename column

SP_RENAME ‘table.columnName’,’newcolumnname’,’COLUMN’

See below snap to resolve above problem of renaming satus to statusId

sp_rename_Column

Enjoy!!!

RJ

Ranking by Rank() function then what about Row_NUBER() TIP #37

 

In last post we have discussed Row_Number() function. In similar way we have RANK function. By the name it is clear that we provide  some kind of ranking for rows.

It syntax is exact same like ROW_NUMBER().

Now the question comes in mind if it is same then why SQL SERVER introduce new function.

so there is slightly difference

ROW_NUMBER() will generate sequential number regardless of  duplicate rows in partition.

While  RANK() will generate sequential number for unique row in partition and use same sequence for duplicate and skip sequence which lies between duplicate.

For example in  below query we want to know total dues rank according to  territory and  modified date.

so we write following

SELECT TerritoryID,TotalDue,ModifiedDate,SalesOrderNumber ,RANK() OVER (Partition by ModifiedDate,TerritoryID ORDER BY TotalDue) ranks FROM [Sales].[SalesOrderHeader]

When we run it we get following result

rankk

enjoy !!!

RJ