Database mail log using TSQL (Just Learned)

SYSMAIL_ALLITEMS is a view in MSDB database is used to check database mail log .
Contains one row for each message processed by Database Mail. This is really helpfull view to troubleshoot notifications .

Count String Occurrence SQL Server

Sometime we have to count the occurrence of any specific string like :

Declare @Input NVarchar(Max)
Set @Input = 'A92GPGCatherineGeorgeB2VZ9VCARMENNAVARROARAUZ'

Here we have to count the occurrence.
so we can get this by using simple script :

Select (LEN(@Input) - LEN(REPLACE(@Input, '', ''))) / LEN('')

Thanks...

Null value is eliminated by an aggregate or other SET operation

This warning comes usually while using aggregated function on column containing one or more NULL values.

To understand run following code :

Declare @R Int

Set @R = (Select SUM(N) From (Select NULL AS N UNION Select 1) aa)

---- OUTPUT
----- Warning: Null value is eliminated by an aggregate or other SET operation.
 
There are two solutions to get rid of this warning :
1. SET ANSI_WARNINGS OFF
Turn Off the warning using command.
 
2. ISNULL
Use ISNULL function on column.
e.g :
 
Declare @R Int

Set @R = (Select SUM(ISNULL(N,0)) From (Select NULL AS N UNION Select 1) aa)

--- OUTPUT
--- Command(s) completed successfully.

I like to use second method always , what you use ?
Note : Make sure to use the ISNULL function before using aggregate function otheriwe you will face the same warning again.

 

How to increase progarmmer productivity

http://www.blogya.in/how-to-increase-programmers-productivity

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. :)