WAIT FOR DELAY

WAITFOR DELAY

SQL Server has an option to wait for delay which means that you can pause your SQL query code (batch) or pause stored procedure for a specified time (<24h) using delay option. There is also an option to specity time (WAITFOR TIME) which you can check in the link at the bottom.

select GETDATE()

waitfor delay '00:00:02';

go

select GETDATE()

waitfor delay '00:00:10';

go

select GETDATE()

Output

(No column name)

2012-06-28 17:06:45.693

(No column name)

2012-06-28 17:06:47.783

(No column name)

2012-06-28 17:06:57.810

case sensitive string comparison

case sensitive string comparison

In SQL Server when you compare strings sometimes 'A' equals 'a' and sometimes 'A' does not equal 'a'. This depends on collation setting. Collation decides how strings are compared; you can have many different collations but the most important factor is if collation you have is case sensitive or case insensitive. If one of the strings or fields is case sensitive than 'A' is not the same as 'a' but if both are case insensitive then 'A' equal 'a'.

 

Below are several examples explaining case sensitivity strings comparisons and results.

 

select CASE WHEN 'a'='a' THEN 'match' else 'doesn''t match' end

 

The result of the above sql is always 'match'

 

select CASE WHEN 'A'='a' THEN 'match' else 'doesn''t match' end

 

The result of the above sql is 'match' if database collation set to case insensitive and 'doesn't match if collation is case sensitive.

 

select CASE WHEN 'A' collate sql_latin1_General_CP1_cs_as ='a' THEN 'match' else 'doesn''t match' end

 

If this case we force case sensitive collation using collate command and the results of the above query is always 'doesn't match'

 

select CASE WHEN lower('A') collate sql_latin1_General_CP1_cs_as = lower('a') THEN 'match' else 'doesn''t match' end

 

To compare two strings and ensure that are case insensitive I often use lower function on both sides. In the example above I enforced case sensitivity using collate command but I used lower on both sides hence made is case insensitive so sql result is 'match'

Mail Status

GET DATABASE MAIL STATUS

1)  SELECT * FROM msdb.dbo.sysmail_allitems

GET DATABASE MAIL LOG STATUS

1)  SELECT * FROM msdb.dbo.sysmail_event_log

Email sending Format in sql server

Type 1

 

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'test',

    @recipients = 'test@mail.com',

    @body = 'The stored procedure finished successfully.',

    @subject = 'Automated Success Message' ;

 

Type 2

 

EXEC msdb.dbo.sp_send_dbmail 

    @profile_name = 'test',

    @recipients =  'test@mail.com',

    @query = 'SELECT COUNT(*) FROM Database.dbo.tablename

                  WHERE column1 > ''2004-04-30''

                  AND  DATEDIFF(dd, ''2004-04-30'', column2) < 2' ,

      @subject = 'Work Order Count',

    @attach_query_result_as_file = 1 ;

 

Type 3

 

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =

    N'<H1>Work Order Report</H1>' +

    N'<table border="1">' +

    N'<tr><th>Column name1</th><th> Column name2</th>' +

    N'<th> Column name3</th><th> Column name4</th><th> Column name5</th>' +

    N'<th> Column name6</th></tr>' +

    CAST ( ( SELECT td = wo.column1,       '',

                    td = p.column2, '',

                    td = p.column3, '',

                    td = wo.Column4, '',

                    td = wo.Column5, '',

                    td = (p.column6 - p.column4) * wo.Column2

              FROM Database.dbo.tablename1 as wo

              JOIN Database.dbo.tablename2  AS p

              ON wo.column1 = p.column1

              WHERE condition

              ORDER BY column1 ASC,

FOR XML PATH('tr'), TYPE 

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail 

    @recipients =  'test@mail.com',

    @subject = 'Work Order List',

    @body = @tableHTML,

    @body_format = 'HTML' ;

Database Mail

Database Mail Related All Concept Link

http://www.idevelopment.info/data/SQLServer/DBA_tips/Database_Administration/DBA_20.shtml

TRIGGER

TRIGGER BASIC OPERATION

IF EXISTS (SELECT 1 FROM inserted)

      BEGIN

            IF EXISTS (SELECT 1 FROM deleted)

                  BEGIN

                        SET @action = 'UPDATE';

                  END

            ELSE

                  BEGIN

                        SET @action = 'INSERT';

                  END

      End

ELSE

      BEGIN

            SET @action = 'DELETE';

      END

CHANGE ROW TO COLUMN

CHANGE ROW TO COLUMN USING SIMPLE PIVOT

 

Declare @t table(id int identity(1,1), strfrag varchar(8))

insert into @t values

('1'), ('>'), ('100');

select * from @t

select [1],[2],[3] from @t

pivot (

    max(strfrag)

    for id in ([1],[2],[3])

) as pt;

SPLIT A STRING DELIMITER USING fUNCTION

SPLIT A STRING DELIMITER USING fUNCTION

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))       

returns @temptable TABLE (items varchar(8000))       

as       

begin       

    declare @idx int       

    declare @slice varchar(8000)       

 

    select @idx = 1       

        if len(@String)<1 or @String is null  return       

      

    while @idx!= 0       

   begin       

        set @idx = charindex(@Delimiter,@String)       

        if @idx!=0       

            set @slice = left(@String,@idx - 1)       

        else       

            set @slice = @String       

          

        if(len(@slice)>0)  

            insert into @temptable(Items) values(@slice)       

 

        set @String = right(@String,len(@String) - @idx)       

        if len(@String) = 0 break       

    end   

return       

end  

CONSTRAINT

FIND OUT WHAT ARE CONSTRAINTS ARE AVAILABLE IN OUR DATABASE

use Databasename

select c.name as c_name, t.name as t_name

from sys.key_constraints c

join sys.tables t on t.object_id = c.parent_object_id --where t.name='Tablename'