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)
