July 2009 (3)
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
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?
The script source code (very well documented) and author (Michelle Ufford) can be found at http://sqlfool.com/2009/06/index-defrag-script-v30/
Here is a simple break down to how these snippets actually work
print 'Today - no time'
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))