How to be smart programmer

http://www.blogya.in/smart-programmer

Apply Row Number Function on Distinct Value

Sometime we need to apply the Row Number function on Distinct value .
Here is the example :

Declare @Table Table (COL1 Int , COL2 Int)

Insert Into @Table
Select 100, 5000
UNION ALL
Select 101, 5000
UNION ALL
Select 102, 5000
UNION ALL
Select 100, 7000
UNION ALL
Select 102, 8000
UNION ALL
Select 103, 9000

Suppose here we want to just list all values from COL1 with a extra Row Number column .
Output should be like :

Col1	Sr.No
100	1
101	2
102	3
103	4

To generate the Sequence number we need to use Row_Number function .

---- SIMPLE
Select COL1, ROW_NUMBER() Over(Order By COL1) As RN
From @Table
---Output

COL1 RN
-----------
100 1
100 2
101 3
102 4
102 5
103 6

Here we generate the Sequence Number for COL1, ohh but what is this happening , sequence number is also generating for duplicate records means for all the records of COL1.

Lets try use the DISTINCT to get the unique values:

---- WITH Distinct
Select DISTINCT COL1, ROW_NUMBER() Over(Order By COL1) As RN
From @Table

--- Output
COL1       RN
-----------
100         1
100         2
101         3
102         4
102         5
103         6

Output is same again.

Lets give a chance to GROUP BY clause ..

---- WITH Group By
Select COL1, ROW_NUMBER() Over(Order By COL1) As RN
From @Table
Group By COL1
--- Output
COL1        RN
----------- --------------------
100         1
101         2
102         3
103         4


Wow, its working now.
Means Group By works well with Row_Number Function.


I did not try where it can fails....

How to Alter Primary key ?

To modify a PRIMARY KEY constraint, we must first delete the existing PRIMARY KEY constraint and then re-create it with the new definition. :)

How Dangerous Null values are in Subquery

Today I came across a strange problem while using simple subquery, everything was right but no data was returning.


here is that query :

Declare @Seats Table (SeatNumber Varchar(4))
Declare @ExcludeSeats Table (SeatNumber Varchar(4))

Insert Into @Seats
Select '44K'
UNION
Select '44J'


Insert Into @ExcludeSeats
Select '44K'
UNION
Select NULL


Select * from @Seats
Select * from @ExcludeSeats

Select * From @Seats
Where SeatNumber Not In (Select SeatNumber From @ExcludeSeats)

-------------------------------------------------------------------
First table containing seat Numbers 44K & 44J and other containing 44K and a Null value.
And now requirement was to find out the seats that are available in table Seats but does not exists in table ExcludeSeats, I wrote a very simple query using a subquery and  run . I was surprised to see no data.

However, one row should return.

After sometime I got the problem of NULL value returning from subquery and when I filtered the NULL value, worked fine .

The query was after modified :


Select * From @Seats
Where SeatNumber Not In (Select SeatNumber From @ExcludeSeats Where SeatNumber Is Null)

So today learning is that If a subquery returns NULL value, the query failed.


Thanks

Replace word in Excel using Function

In Excel, the Substitute function replaces a set of characters with another.
The syntax for the Substitute function is:

= Substitute( text, oldtext, newtext, nthappearance )

e.g.

In a word "Rajeev ", we want to replace "ee" with "i" then formula will be :


=SUBSTITUTE(A1,"ee","i")

nth appearance is optional. It is the nth appearance of old_text that you wish to replace. If this parameter is omitted, then every occurrence of old_text will be replaced with new_text.

Fix the pick up cell reference in Excel formula.

Sometime we need to keep fix a cell reference while formula in Excel.
So here is a good article to learn the use of $ sing in formula.

http://www.mrexcel.com/articles/relative-and-absolute-formulas.php

Thanks

Use of Merge Statement

Merge Statement :  Heard many times and but think today to explore it. This is a new introduced statement in SQL Server 2008. A very helpful statement for developers and DBA.

So what we can do with this statement. Let's explore:

Suppose we have to create a Interest calculation script, we have a table Clients with ClientID and Amount cloumn, and another table that will store the Interest Amount . Here is the schema:

IF OBJECT_ID('tempdb..#Clients') Is Not Null Drop Table #Clients
IF OBJECT_ID('tempdb..#ClientInterest') Is Not Null Drop Table #ClientInterest

Create Table #Clients
(
ClientID Int,
Amount Int
)

Insert Into #Clients
Values (101,50000), (102,70000),(103,65000),(104,100000),(105,45000)


Create Table #ClientInterest
(
ClientID Int,
Amount Int,
Interest Int
)

Insert Into #ClientInterest (ClientID, Amount)
Values (101,50000), (102,63000),(103,41000),(108,150000)

-----Before Merge Statement
Select * From #Clients
Select * From #ClientInterest

What we have to do before Interest calculation  :
1. Add new client to ClientInterest Table
2. If Client exist already in ClientInterest Table , update the Amount column with Clients table, because client can increase/decrease the Amount.
3. If Client does not exist in Clients table but exist in ClientInterest Table then delete that to Client table, because don't have to calculate the Interest  on clients whose business has been  closed .

Means both tables should be sync before calculating Interest.

To get this, need to write separate INSERT, UPDATE ,DELETE statements on earlier versions, but now
in SQL Server 2008  we can go ahead with single statement.

Here is the solution of above requirement :


-----Merge

Merge #ClientInterest AS Target
USING #Clients As Source
ON (Target.ClientID = Source.ClientID)
When Matched Then
Update Set Target.Amount = Source.Amount
When Not Matched Then
Insert (ClientID,Amount )
Values (Source.ClientID,Source.Amount )
When Not Matched By Source Then
Delete
OUTPUT $action, Inserted.*, Deleted.*;

-----After Merge Statement
Select * From #Clients
Select * From #ClientInterest



This statement will insert new client , update already existing, and delete if not Clients table but in ClientInterest table.

Other best stuff in this , can use the OUTPUT clause to see the action taken.

So can says , really helpful statement for us. Minimize code and save time,  I did not check the performance of this statement , but read on MSDN works faster than separate statements .