Stephen Hill's Bloggie Writing about my interests; Programming and Formula One.

SQL Server Tips and Tricks

2 years ago

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 you know of any more tips or tricks please share them with me...

Previous Comments

  1. Name Rajni Padhiyar Friday 10 January 2003 1:33 PM

    Nice Tips,
    Thanks

  2. Name gwaine Wednesday 24 September 2008 5:19 PM

    good stuff.

    How about the following to generate the TSQL to fix the login/user mapping when moving a database to a different server (you need to copy and paste the results into a new query window and run it to make the changes):

    select 'exec sp_change_users_login ''auto_fix'', ''' + name + ''''
    from sysusers
    where islogin = 1
    and name not in ('dbo', 'guest')


  3. Name Azhagupandian Thursday 04 December 2008 4:16 PM

    Very very useful tips for me

    Thanks

  4. Name Stephen Hill Monday 08 December 2008 12:30 PM

    @Azhagupandian

    No Problem :)

  5. Name Rajni Padhiyar Monday 05 January 2009 10:06 AM

    Very Useful tips

  6. Name Murugesh Friday 27 March 2009 6:11 AM

    Really all useful tips, here i go with another tip which might be useful.

    select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1,0)) -- gives the last day of the current year.

  7. Name Phex Thursday 07 May 2009 11:38 PM

    Very Nice :-) Thanks

  8. Name Rakesh Tuesday 19 May 2009 12:01 PM

    Date Only...

    Consider using the following

    DateAdd(d, 0, DateDiff(d, 0, Getdate()))

    - R

  9. Name Anusha Saturday 30 May 2009 12:39 PM

    Very nice tips.. Waiting for next

  10. Name Sandip Deshmukh Saturday 03 October 2009 6:27 AM

    all are very useful tips for me
    can i get more tips for preaparion of Interview.
    please....

    thanks

  11. Name Tuan Chung Thursday 11 March 2010 1:13 PM

    Very usefull tips. Thanks.

    Can you possibly tell me how to process row by row without using cursor??

    Thanks in advance

Leave a Comment