Rare is the time do we have to delete the data row by row. But…. Recently I needed to delete some xml fields in a database as they were no longer required. It was a massive amount of xml; the table itself was 50,000 rows and nearly 300gb, and I needed to delete roughly 9000 rows.
I decided to delete a row at a time. Whilst this contradicts how sql works, I did not want to fill up the log with one massive delete. And as the database has its log backed up every 15 minutes, this will ensure that there is free space in the log for new deletes. So we’ll delete a row every 2 minutes and check that there is space in the log before deleting. If the log is looking full we’ll check again in 5 minutes until there is space to run another delete. So I can run the script and leave it to run knowing that the script won’t kill the database.
The script is below with an example using AdventureWorksDW2014.
When I learned to play the guitar, the first thing I did was obviously buy a guitar. Then I got a guitar teacher. And then I practised, and then practised, and when I had finished practising I practised some more. I made many mistakes. I got frustrated. But I never gave up. And over time I got better, made less mistakes, picked up some tips and tricks to make playing easier. I developed an ear for notes. I understood scales and keys and relative minors. And now I’m good. not great, but I can play a bunch of Smashing Pumpkins songs and really have a lot of fun playing the guitar without really putting too much effort into practising because I put so much effort in the beginning.
90% of my learning was based on practice. 10% was based on reading books and putting it into practice. The books came some time after buying a guitar. To be clear: I did not learn the pentatonic scale and it’s different position on the fretboard by reading books. I put the theory into practice. To really hammer the point home: I did not learn how to play the guitar by picking up a book and reading it. It was merely a guide to point me in the right direction.
So how does this relate to databases? Continue reading
Today I am going to talk about the stored proc sp_executesql and the benefits it has over EXEC @SQL. Microsoft actually recommend that you use the sp_executesql stored procedure instead of the EXECUTE statement. This is because of something called parameter substitution, which makes Sp_executesql a far more efficient method of executing dynamic SQL. Continue reading
SQL Relay 2014 resumes in the UK next week. SQL Relay is a series of 1 day events that are run throughout the UK. Continue reading
Hey all, it’s been a busy few weeks and though I’ve had plenty of blog posts scheduled I thought I’d make some updates with what’s been going on. Continue reading
Very recently I had to delete a 600 partitions from a single measure group in a cube. This is a high number of partitions, but to make matters worse, it was 600 random partitions out of 1,000 or so partitions already there. So, I could’ve spent the best part of a day picking through which partitions to delete, or I could use SQL to script out the xmla for me and then execute the xmla manually. Ideally I would have done this in PowerShell. And I probably will if I have to do something like this again. But seeing as it is a one shot, I decided to write it in T-SQL. At any rate, I’ve posted the T-SQL below. Continue reading
Yesterday I posted a script that will drop all of the foreign keys in your database in a very inelegant, but super effective way. Today’s script is slightly more sophisticated in that you can print out the CREATE statements for the foreign keys before running the drop. The script will print out the commands rather than running them, so you can script them out and run them whenever you want.
Before I get into this, I’m hoping that you’re aware that deleting foreign key constraints is a bad idea, and unless you have a good reason to do so, or at least have the ability to re-apply them, then leave them be. Continue reading
Been working from home lately, and this morning I needed to alter my password to a RDP session inside another RDP session. So I needed to find a way to press CTRL+ALT+DELETE without the first session receiving it. I had hoped that the control panel might help, but sadly not:
One suggestion that worked for me is to use the on screen keyboard (OSK, osk.exe).
But don’t press CTRL+ALT+DEL on the OSK exclusively. It does not work. You have to press CTRL+ALT on your physical keyboard and then press DEL on the OSK in the nested RDP session.