Tribal SQL

I am honored to announce that a chapter I wrote has been published in a book. Nope – it’s not the additional Appendix that I sent the author of Everyone Poops, but is instead Tribal SQL. Edited and produced by the infamous Jen McCown and published by Red Gate the books is on sale now. If … [Read more…]

5 minute book review: The Art of SQL

I have a simple algorithm for picking books: I buy until my wife tells me I have to sleep outside I try to read books that take a practical and interesting approach. For technical books about databases the quality is really uneven. You have your query tuning books which are good overviews of how DBMS … [Read more…]

Out of space error when moving tempdb

Quirk in SQL Server to do with sizing tempdb that I ran into today: SQL Server checks the current location when you move tempdb to see if there is enough space not the new location. From: Configuring Database Files for Optimal Performance The below script will move TempDB from its current location to a folder … [Read more…]

Triggers Part 3: FAQ and FOP Continued

This is part of a 3 part epic mini-series on triggers, view Part 1 and Part 2. How many triggers should you have per table? Ideally zero.  If you have any then there should be one.  There is no guarantee on the ordering of trigger firings, they normally fire based on their age – newly-added … [Read more…]

Triggers Part 2: Facts and Frequently-Occuring-Problems (FOP)

This is part of a 3 part epic mini-series on triggers, view Part 1 and Part 3. When are triggers fired, and how many times? For your standard missionary position trigger, they are fired once per batch. So if you have an update statement that affects 57 rows, then the DML AFTER trigger fires *once* … [Read more…]

Triggers Part 1: Introduction to madness, plus whores

This is part of a 3 part epic mini-series on triggers, view Part 2 and Part 3. According to the standard developer canon about databases you should avoid triggers like you avoid wearing your “I love goto statements” t-shirt at the company Christmas party (again).  Knowledge of triggers is verboten, almost as bad as saying … [Read more…]

Running SQL cross servers

If you work with more than one database and have the constant need to run the same query across multiple servers, the undocumented stored procedure sp_MSforeachdb can be useful; see below example comparing stored procedures across servers using a simple checksum to tell if they are the same exact contents. sp_msforeachtable is another oldie but … [Read more…]