COUNT_BIG() is for counting big range TIP #50

 

Problem:- How to count large number of rows for specific condition ?

Solution

Suppose you have a large table which having more than millions of records and you want to count no of rows for specific condition  which itself contain more than 100 millions records.

Now in this case when we use COUNT function then we will get arithmetic overflow error message.

Now in such situation we have another function which can count this big ranges specially numeric data types.

The use of this is similar as COUNT function

For example you have to write

SELECT COUNT_BIG(1) FROM  tblMyLargeTable

I hope it may help you some time

Thanks & Enjoy

RJ !!!

How to get records who has phonetic similarity - a hidden feature in SQL SERVER TIP #49

Sometimes , It may required that you want a query who can provide you result which have phonetic similarity.

Lets understand this by example

Suppose you want to search a record whose pronunciation or phonetic sound are similar or close to then in that case you can use SOUNDEX property of  SQL SERVER which is a hidden feature and rarely used.

To understand it lets jump into the example.

Suppose you need to find records from people  table of adventureworks database whose firstname sound like “JOHN”

so to achieve this we will write following query

SELECT  FirstName,LastName
FROM Person.Person
WHERE SOUNDEX(FirstName)= SOUNDEX('John')

It will return all those records whose firstname sounds like “John”

please see below screen for more detail

I hope it may help you somewhere.

Enjoy !!!

RJ!!

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

 

I hope it may helpful you somewhere enjoy !!!

RJ!!!

CONCAT a valuable function TIP #47

 

This is one  the new feature in SQL Server 2012 by which you can concatenate values.

The USP of the function is that it handle the NULL values also.

means if you are concatenating values in which null also exists then it handle those null value also means it concatenates only those values which does not have null.

For example

Suppose I want to concatenate the column like product name, product number, color and class of Product table of adventureWorks database and it might be possible that class & color may have null value. So we don’t need to do extra care of  those nullable column. We can write simple query as shown below

I hope this feature may help you somewhere in your technical life.

Thanks & 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 . Just kidding

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]

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

 

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

Option 3:-

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

 

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.

 

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

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

Sort by Last Name when @sortby value  = 2

You can add any condition according to your business need.

Thanks

Rj !!