Tips and Tricks

What's the earliest date that can be stored in a DATETIME data type?

DATETIME data types can store dates from January 1, 1753 through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds

get nth highest value

SELECT intEmpID,fltBasicPay

FROM TB_PayParticulars_Lcl_Mst A

WHERE 5=(SELECT COUNT (DISTINCT fltBasicPay) FROM TB_PayParticulars_Lcl_Mst

WHERE A.fltBasicPay<=fltBasicPay)

select fltBasicPay from TB_PayParticulars_Lcl_Mst t

where 5= (select count(fltBasicPay)

from (select distinct fltBasicPay from TB_PayParticulars_Lcl_Mst)b

where t.fltBasicPay<=fltBasicPay)

Send mail via SP.

Create a profile in Managementà Database mail. Give the email address, server name and all details to create an acoount.

EXEC msdb.dbo.sp_send_dbmail

      @profile_name = 'Anju',

      @recipients = @email,

      @copy_recipients='anju.e@cgi.com',

      @body = @msg,

      @subject = 'Account information is updated',

      @body_format ='HTML'

What's the maximum number of parameters can a SQL Server 2000 stored procedure have?

The maximum number of parameters a stored procedure in SQL Server can have is 1,024, which is also the maximum number of columns a table can have.

NULLIF

This function returns a null values if the first parameter is equal to the second parameter

Eg: Suppose a table has a field chvname and it has values like A,B,C,D

    Select NULLIF(chvname,’A’) from table_1 will give the output as

NULL

B

C

D

SET NOCOUNT

Controls the message showing the number of rows affected by a query.

SET NOCOUNT ON– does not display the number of rows

SET NOCOUNT OFF-displays the number of rows affected

SET ROWCOUNT

Suppose there’s an update statement with a where clause. Actually the update statement will update the values of 7 rows of a table and if we set SET ROWCOUNT 4 then, only the first 4 rows will be affected by the update statement.

set rowcount 4

update table_6 set chvname='ab' where intid<15

NEW_ID()

Used to assign a new value to an uniqueidentifier variable.

DECLARE @myid uniqueidentifier

SET @myid = NEWID()

PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)

 

To get the distinct values concatenated of a varchar column in a table

SELECT substring(Ms, 1, datalength(Ms) / 2 - 1)

FROM (SELECT yourFieldName + ';' AS [text()]

FROM yourTableName

FOR XML PATH('')) AS T(Ms)