Chittibabu

The following are a wide range of tips and tricks I have collected over the years working with various versions or Microsoft's SQL Server. I always enjoy finding little snippets of code which are both simple and powerful. Why write complicated routines when one or two lines of code can do it?
Although you may very well find the following code useful, I don't really want small posts like this from detracting from the overall purpose of my blog, which is to help me develop my writing skills. I am therefore going to create a section on this bloggie called my Code Notebook which will hopefully serve two purposes:

1. It will provide me with an area to note down and share pieces of code which I am proud of creating and,
2. It will keep the main focus of the bloggie on improving my writing.

So without further ado, I present to you my SQL Server Tips and Tricks...

Date Only

Quite a useful function for producing or saving only the date part of the smalldatetime datatype. This can be useful if you only need to store the date or you need to group some results by dateonly.

Select
Convert([smalldatetime],floor(Convert([float],getdate(),(0))),(0))

Produce a Row or Table Checksum

This can be useful for checking if any of the contents of a row or table has changed without saving the entire contents of row or table.

Table Checksum
Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK)
Row Checksum
Select CheckSum_Agg(Binary_CheckSum(*)) From Table Where Column = Value

Reindex an entire database

EXEC [sp_MSforeachtable] @command1 = "RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')"

Count Character in a String

This very simple statement allows you to count how many times a certain character appears in a string.

Select Len('///xxx') - Len(Replace('///xxx', '/', ''))

Single & Multi User Mode

By default, a database is set to Multi User Mode, allowing more than one connection to connect to the database at a time. However, it may prove useful at times, for example database maintenance, to restrict the database to only 1 connection. This allows that 1 user to carry out work on the database without any locks.

ALTER DATABASE MyDatabase SET SINGLE_USER

ALTER DATABASE MyDatabase SET MULTI_USER

Smart Date Ranges

Find records which date falls somewhere inside the current week.

where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ), 0 )

Find records which date occurred last week.

where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )

Returns the date for the beginning of the current week.

select dateadd( week, datediff( week, 0, getdate() ), 0 )

Returns the date for the beginning of last week.

select dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )

Drop all Database Connections

Although this could be a very dangerous script to run, it is very useful when you need to do some maintenance on a database and there are some rouge connections open stopping you from doing so. The script will run through all users currently connection to the specified database and kill their connection.

Use Master
Go
Declare @dbname sysname
Set @dbname = 'name of database you want to drop connections from'
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End

If anyone know  more tips or tricks please share them with me...