I came upon the need to calculate the number of weekdays between two dates. SQL Server offers several utilities for calculating dates, but nothing for this specific purpose. After staring at a calendar for some time and going through several revisions, I came up with a solution that seems to work pretty well. I do rely on 1 helper UDF, but it’s a pretty standard function that can easily be used elsewhere. Please note that I’ve designed the main function to be language independent—that is, it does not depend on @@DATEFIRST to be set any particular way.
Continue reading »
Sep 222008
A very common task for database developers taking reigns of an existing project is to normalize data. I needed a convenient way to split delimited values into a table for transformation purposes. After finding a wonderful algorithm, I decided to enhance it and transform it to a User-Defined Function in the process. Be aware that the function depends on a Tally table to exist – much like you might have with a Calendar table to join against.
Continue reading »
Here’s a quick little statement you can use to verify the size and growth settings for a SQL Server database. It doesn’t spell everything out for you, but if you’re aware of some common conventions (-1 = unlimited, 0 = no growth), it’s a fast and easy way to verify settings before issuing your ALTER DATABASE command. The filename is commented out by default, but provided just-in-case.
Continue reading »
Ever needed to round an arbitrary DATETIME to some specified increment (i.e. 5, 10 or 15 minutes)? This function can come in handy for such a task.
Continue reading »
Some time ago, I needed to sync down a list of AD users to use as a lookup (as opposed to querying LDAP on demand). I found this little gem: How to avoid 1000 rows limitation when querying active directory (AD) from SQL 2005 with using custom code. It works great, especially since I submitted a bug fix.
In any case, the process is very simple:
Continue reading »