Get the dates of the ‘x’ occurrence of the Weekday within the Month

You may want to find for example the date of the 4th Saturday in each month for a given year. This function came out of answering the question here: I’ve created it as a Table Valued Function so you can bind it into any query you wish.   create function dbo.tvfn_dates_of_day_in_month_position ( @year_start date, […]

SQL Saturday 467 – Precon – Query processing and internals

Im not doing a tremendous amount of public speaking this year and concentrating on more professional matters,   however im pleased to say that on Friday 5th December i will be presenting a day long pre-con on Query Processing and internals at SQL Saturday 467 in Southampton. We will be spending the whole day looking […]

SQL Unit Testing – dynamic connection configuration

I have recently joined the SQL unit testing bandwagon and love it, especially the pretty instant feedback when a code change impacts some other area in an unexpected way. Until now I have liked to believe I developed quality code, however in the face of a plethora of tests, I have to accept that was […]

Using ADO.NET from F# – Examples

ADO.NET is dead and replaced many years ago but this week I needed to write a bit of ADO.NET against SQL Server to test out Prepared statements and server side cursors for an application that requires to support it. Here is the fully working example that covers off the bits I needed: You need to […]

SSDT SQLCLR integration : a case of the cart before the horse

I’ve very recently needed to delete an XEvent session’s asynchronous file target as part of tearing down the session. Native support is non-existent. What better than a simple SQLCLR stored proc. Having first tried to accurately derive the file name from the session created time based on details by Jonathan Kehayias’ (blog) post an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target, I […]

Getting out of the ground with TSQL Smells.

At SQLBits I had a number of conversations with a number of people over TSQL Smells, my open source project for static code analysis of SQL Code. The general opinion was that although the concept was sound, the process of getting it running ( and developing it further ) was not documented. Im sure that […]

The maths most SAN engineers do not understand in context of Database Servers

I suppose I write this post to mock some of the things I keep seeing in the industry today. Directly Attached Storage (DAS) has moved on dramatically in the past 5 years, a single SSD has the capability of up to 525MBytes/sec throughput (SATA/SAS 6Gbits/sec), 700MBytes/sec (M.2) and then there is PCIe connected flash operating […]

Increasing ColumnStore compression ratio for archival and “Cold storage”

At my present client, part of the task I have to under take is to re-engineer the “Cold Storage”. This is ~20Terabytes of compressed CSVs going back over 15 years or so. Presently these are held on aging and creaking centera servers that are way past end of life. The solution probably made sense 10 […]

My spaced used scripts

I suppose everyone has a set of scripts they lean on to help make sense of it all. These scripts are my version of sp_spaceused, one to query a specific table and the other at db level. Admittedly these are rough around the edges, that said they quickly allow me to break down a table […]

Please use my filtered index

Not long ago we had a requirement to timeout asynchronous application tasks not completing within an expected period (defined per task type). There is a high hit rate on the table with the number of pending modules at any one time in the 100s, and for this reason timeout processing needed to cause minimal blocking […]