Valthor's DB Blog

select * from valthor.druzin

Cleaning up your orphans


Ok, so, you just found out that there's a lot of garbage data in your database, garbage orphan data which violates your foreign key references. 

You need to clean this up a.s.a.p. but writing a bottom up deletion / update script for each of the tables which have these foreign keys is going to be a tedious and time consuming task.
But don't worry, here's a script that will do your heavy lifting for you.

It will generate the delete / update statements for you, and order them in such a fashion that the tables that have no children get cleaned up first, and then ending with the ones that have children.
The first code block here only generates the delete script but the second one actually will execute the deletes automatically with a fast forward cursor ( so be careful ).


Are your cascading constraints slow ?


If you've been thorough with your database design, you've set up bunch of foreign key constraints on your tables to enforce data integrity, and some of them are even cascading ones. 
You then might find out that the performance up those pescy cascading delete statements are badly affecting the performance of your system. This is because you've probably not defined indexes on those cascading columns (which is something you should probably do).

Then this following script comes in handy, since it actually generates a number of index creation scripts for each of the cascading columns which don't have indexes on them as of now. This is very useful when you're troubleshooting performance in a large system.


SQL Server performance metrics

When troubleshooting performance problems on a SQL server, being able to collect some basic performance metrics easily is key. 

This can actually be achieved using a sql script directly from SSMS, without the need of using perfmon or any third party tool. 

The script below does this for you, collecting data in 10 second internvals, calculating the average value over each interval.  It will give you whole bunch of metrics which can be useful, from Page Life Expectancy, Buffer cache hit ratio, memory grants pending etc etc.


Comma delimeted string as tabular rows


Ok, so you're working with SQL Server and you've got yourself a long string with comma delimeted numbers, and desperately need to join those numbers to your table's columns. You're thinking that you'd wish that you could convert your string into a narrow table with a column and rows containing each comma seperated value.
Who hasn't had that problem, right ?

Well, here's a tiny function that turns excatly that kind of string into a excatly that kind of table ( it also works for simple strings ). It uses SQLServers XML processing to accomplish this, nothing too fancy or complicated but a really nice tool to have in your toolbox.