T-SQL

How to Delete From Large Tables in Batches Without Growing the Transaction Log

29 April 2012

I was working with a colleague a few months back to find an efficient method for deleting the entire contents of a large table without using TRUNCATE or causing excessive log growth. There were reasons why we couldn’t temporarily remove the foreign keys which meant that TRUNCATE TABLE wasn’t an option so we had to [...]

Read the full article →

How to print very long strings and procedures

23 November 2011

Aaron Bertrand (blog | twitter) posted recently about the benefits of the OBJECT_DEFINITION() function when compared to INFORMATION_SCHEMA.ROUTINES when trying to search for a particular string in a very long procedure. He highights how useful the OBJECT_DEFINITION() function and sys.modules DMV are when searching for words or object references in such long SQL modules.  OBJECT_DEFINITION() [...]

Read the full article →

Test Driven Development (TDD) for Databases – Choosing a framework

8 October 2011

I’ve been a fan of test-driven development (TDD) for a long time. My primary skill set revolves around database design and development but I like to think that, whilst not a .Net expert, I have pretty good C# skills for a SQL bod. So I have had the opportunity to follow proper TDD methodolgies on several [...]

Read the full article →

Making RAISERROR work like PRINT

10 December 2010

One of my favourite bloggers, Brent Ozar posted recently on his favourite blog posts one of which was a really good article by Kendra Little on reasons to use RAISERROR instead of PRINT statements. The synopsis of Kendra’s post was that whilst we all learn early on in our development careers to use PRINT statements to debug [...]

Read the full article →

Storing Single Key/Value Settings

16 November 2010

I read an interesting post today by Arnie Rowland about a simple but clever way to ensure only one row in a table used for application settings or config items. I have found that tables like this can, if allowed to go unchecked, proliferate through a database which can cause minor, niggling maintenance issues and [...]

Read the full article →