Chris Crowe's Blog

Looking for some new sounds – checkout this freebee

Chris Crowe — Fri, 17 Jul 2009 20:40:37 GMT

Click to download new music, completely free, brought to you by your music loving friends at Windows. You'll see (but not hear) some ads on your screen in addition to the album's cover art, which is how we (they) keep your new tunes from costing you a cent.

Well worth a listen to checkout some new bands and artists….

Listen online, download MP3 or WMA

http://sponsoredsongs.reverbnation.com/windows?utm_source=windows_myspace_page&utm_medium=button_link&utm_campaign=ms_windows&utm_content=thousand_more

An great TSQL index defrag script

Chris Crowe — Tue, 14 Jul 2009 19:39:16 GMT

I have been doing some more looking at SQL Server indexes lately and came across a great script that may be of use to others as well.

So what can this index defrag script do?

Schedule it to run with the default settings; it works “right out of the box” with no additional configuration necessary
Run this one script from a centralized database for all databases on a server
Run this script for a specific database or table
Configure custom threshold limits and the point at which a rebuild should be performed (instead of a reorganize)
Defrag individual partitions
Log its actions and the duration of the defrag
Run in “commands only” mode (@executeSQL = 0, @printCommands = 1)
Customize performance parameters such as @maxDopRestriction and @defragDelay to minimize impact on the server
Schedule specific indexes to only be defragged on weekends, or every other day

The script source code (very well documented) and author (Michelle Ufford) can be found at http://sqlfool.com/2009/06/index-defrag-script-v30/

Some simple T-SQL code to strip time from a date and return other dates

Chris Crowe — Mon, 13 Jul 2009 22:55:04 GMT

declare @dt datetime
set @dt = getdate()
 
print 'Now'
print @dt
print ''


print 'Today - no time'
print dateadd(D, 0, datediff(D, 0, @dt))
print ''

print 'Today – 23:59:59.997'
print dateadd(ms,-3, dateadd(D, datediff(D, 0, @dt),1))
print ''

print 'Start of this week - Starts on MONDAY'
print dateadd(wk,datediff(wk,0, @dt),0) 
print ''


print 'End of this week - Starts on MONDAY 23:59:59.997'
print dateadd(ms,-3, dateadd(wk,datediff(wk,0,@dt),7))
print ''

print 'Start of this week - Starts on SUNDAY'
print dateadd(wk,datediff(wk,0,getdate()),-1) 
print ''


print 'End of this week - Starts on SUNDAY 23:59:59.997'
print dateadd(ms,-3,dateadd(wk,datediff(wk,0,@dt),5)) 
print ''


print 'Start of Month'
print dateadd(mm,datediff(mm,0,@dt),0) 
print ''


print 'Last day of current Month - 23:59:59.997'
print dateadd(ms,-3, dateadd(mm,datediff(mm,0,@dt)+1,0))
print ''

Here is a simple break down to how these snippets actually work

print 'Today - no time'
print dateadd(D, 0, datediff(D, 0, @dt))
print ''

First off we return the number of days between the date/time passed and 0 so we get the number of whole days (we have lost the time)

Today is July 14, 2009 (as an example) which using this code

print datediff(D, 0, '2009-07-14 03:34:21')

This will return 40006

We then add 40006 days to 0 and this returns the date 2009-07-14.

print dateadd(D, 0, 40006)

Jul 14 2009 12:00AM

So adding it together we get

print dateadd(D, 0, datediff(D, 0, @dt))

You will notice that some of the functions have a –3 as part of the code, this is because we want to return the last actual time that SQL Server can reference on a particular day. SQL Server can store the maximum time as 23:59:59.997. That is because SQL Server is only accurate to 3 milliseconds. If we used 23:59:59.999 it would actually roll over to the next day.

So if we were wanting to return the last time for today’s date/time we can use the following

print DATEADD(ms, - 3, DATEADD(D, DATEDIFF(D, 0, GETDATE()), 1))