Generat Serial Number from Select Query T-SQL

There are times when you would want to generate your own serial numbers for a query. For eg: If we take the Customers table in Northwind database, the CustomerID is not numeric Eg: 'ALFKI'.

If you want to generate your own serial number for the Customers table while displaying the records, use the ROW_NUMBER(). As mentioned in BOL "ROW_NUMBER() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition"

Here is a query that will help you generate your own serial numbers for the CustomerID

SELECT (ROW_NUMBER() OVER ( ORDER BY CustomerID)) AS ID ,
CustomerID, ContactName, CompanyName, ContactTitle
FROM Customers

ROW_Number() is a magical function. It can generate serials but it requires the column based on which it generate the Order. Also it can repeat the sequence on every occurrence of the given condition.
ROW_Number() require Minimum 1 and maximum 2 data to do all the magic.

1. Column to decide the order. Multiple column can be used in csv format
2. Column to decide Partition on which Row_Number() will regenerate serial from 1.

Lets see this in action

We have tables State(stid,stname) and city(ctid, ctname)

;WITH CTE(STID,STNAME, CTID, CTNAME,STIID)AS(
SELECT *  FROM TBSTATES S JOIN TBCITIES C ON S.ID=C.STATEID
)
 
SELECT STNAME, (ROW_NUMBER() OVER( PARTITION BY STNAME ORDER BY CTNAME))CITI_SNO, CTNAME FROM CTE

This will give a column citi_sno with repetition when state will change.

Fill up the data in table and try yourself.

How to check if a date is a valid date in Sql Server 2005

At times, in our stored procedures or sql queries, we need to check if the date specified in the variables is a valid date.

Use the ISDATE() function.

The ISDATE() function determines whether the variable or the expression contains a valid date. It returns 1(true) if the input expression is a valid date; otherwise, it returns 0 (false).

For eg:
DECLARE @dt varchar(10)
SET @dt = '02/21/08'
SELECT ISDATE(@dt)
-- Returns 1

DECLARE @dt varchar(10)
SET @dt = '13/21/08'
SELECT ISDATE(@dt)
-- Returns 0 as 13 is not a valid month

How to send an email using SQL Server

With SQL Server 2005, there is no need to use MAPI client to send emails. Fellow developers who have used MAPI in the previous versions of SQL Server are well aware of the challenges it had. However in Sql Server 2005, we can now use the Database Mail to send emails.

[Note: I assume you have set up and configured Database Mail. If not, check this link Database Mail Configuration Stored Procedures to use various stored procedures required to configure Database Mail. As an alternative, you could also use the SQL Server Surface area configuration tool to configure Database Mail]

Use the following script to send a mail from your Sql Server

USE [YourDB]
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'admin@xyz.com; xyz@xyz.com; pqr@xyz.com',
@body = 'Just testing the mail',
@subject = 'Sending Mail using Database Mail' ;
GO

Documentation for all available options is available at msdn
sp_send_dbmail (Transact-SQL)