Have you ever wanted to get a running total of all of the descendants of each tree node? This sort of thing is useful, especially if you don’t want to pull back an entire object graph just to compute the count of a child collection.``` IF OBJECT_ID(’tempdb..#categories’) IS NOT NULL DROP TABLE #categories;
IF OBJECT_ID(’tempdb..#product_categories’) IS NOT NULL DROP TABLE #product_categories;
CREATE TABLE #categories (CategoryId INT, ParentCategoryId INT); CREATE TABLE #product_categories (CategoryId INT, ProductId INT);
Last year, Jeff Blankeburg came up with the idea of Contribupendence Day. To save you from visiting Jeff’s site and reading another blog post, Contribupendence day is a day when we acknowledge the people who help out our community. Update: It’s completely remiss of me not to tag the person who tagged me.Carey Payette singled me out this year. I can’t say enough great things about her community involvement and the time she puts in to running the Central Ohio dot Net Developers Group.
Let’s say that you want to clear out your old backup history. Why would you want to do that? Let’s also assume that your maintenance plans (yes, maintenance plans) have not been cleaning up after themselves. If this has happened over a small period of time, you can just do this:``` EXEC sp_delete_backuphistory DATEADD(m, -1, GETDATE());
But what if you have a lot of backup history? Trying to delete two years of backup history will slow things down incredibly on your server and take a very very long time.
SQL SERVER Deploying Databases From Visual Studio Team System Database EditionGrant Fritchey has written an article about using Vistual Studio Team System Database Edition to deploy databases and database changes. If you haven’t taken a look at Data Dude, now is the time to do so, it’s a great product! Declarative Database Development This is a nice introduction into how to develop using Data Dude (VSTS Database Edition).
DEVELOPMENT Fluent NHibernate And You Josh over at Computerist Solutions has a quick introduction via code snippets to Fluent NHibernate.
Colin Stasiuk asked a great question: Why are you going to the PASS Summit? But, more importantly, Colin hits on the all important question: how do you decide which sessions to attend? This is going to be my second PASS Summit, so I’m not an expert on picking sessions, but I have an idea based on what worked well last year. Last year, I had an exhaustive list of sessions picked out.
SQL SERVER Why Object Databases will always be Tomorrow’s Technology Tony Davis (editor at Simple-Talk) writes the guest editorial over at SQL Server Central. He makes a great case why object databases don’t fit with most business needs (and why they likely never will). (Thanks to Grant Fritchey for finding this one.) East Iowa SQL Saturday – Call for Speakers! That’s right, the East Iowa SQL Saturday is coming up and they need speakers.
Less reading is a good thing when you’re reading from a physical disk. During some recent performance tuning, I stumbled across a little gem of a stored procedure that was producing between 4,000 and 11,000 physical reads every time it was called. The name? GetStateList. It returns a list of state abbreviations. I’m not proud to say that I wrote it, but I have to come clean: I wrote it. So, how did I fix this travesty of my youthful ignorance?
SQL SERVER Denis Gobo provides a double dose of dynamic SQL hotness via Changing exec to sp_executesql doesn’t provide any benefit if you are not using parameters correctly and Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec. Following his tips in here will net you some considerable benefits in how your dynamic SQL behaves and performs in your production applications. The best part is that he provides a huge amount of example code to back up everything he’s saying.
Let’s say that you have a column that contains some goofy data and it looks like this: USA/Ohio USA/Indiana USA/Iowa UK/Scotland UK/England So, how would you typically split such a string to only get the part after the ‘/’? Well, here’s how I did it:``` IF OBJECT_ID(’tempdb..#tmp’) IS NOT NULL DROP TABLE #tmp; GO
CREATE TABLE #tmp ( s VARCHAR(255) ); GO
INSERT INTO #tmp (s) VALUES (‘USA/Ohio’), (‘USA/Indiana’), (‘USA/Iowa’), (‘UK/Scotland’), (‘UK/England’);
I have a lucky, and rare, chance coming my way in about a week: I will be given the opportunity to design and install the production SQL servers at work. Here’s the fun part – I’ve never done this before, at least not on a production machine. What best practices, secret tricks, or hare-brained schemes do you, my fellow DBAs, follow when you put together a new SQL Server installation? This could be anything from drive configuration, something about SANs, trace flags, whatever.