July 13th, 2011 
When working with meta data tables it can be very useful to track when people make changes to the data. How do you track any change against any number of columns for any table? I have implemented a fairly simple solution that uses triggers and stores all changes as XML into one change log table. [...]
Archive for the ‘T-SQL’ Category
Noir SQL… Or, a Hardboiled Approach to Getting the Job Done
You can tell a lot about my state of mind by the books I’m reading. Lately, it’s Urban Fantasy with a Noir feel to it. Specifically, I’m reading Mike Carey’s Felix Castor series, and I just finished a book by Richard Kadrey called Sandman Slim: A Novel. I love the anti-hero. The protagonist who is [...]
Advance Search – SQL Server
As a BI Consultant, I am frequently going into new databases and writing queries against unfamiliar data sources. I have created a stored procedure that goes against the system views to help quickly find a key word in a Table, View, Columns or Stored Procedure. Once the procedure has been created you can execute it [...]
Posted in SQLServerPedia Syndication, T-SQL 
Tags: SQL, SQL 2008, SQLServerPedia Syndication, T-SQL 
No Comments »Use a Common Table Expression and the ROW_NUMBER() Function to Eliminate Duplicate Rows
Or, removing duplicates with panache… I think of them as rogue tables. They’re quick and dirty and cause you a world of hurt before it’s all over. We’ve all got them. Like those photos from college that guarantee you’ll never run for public office, rogue tables are best left hidden. But, you’re always wondering when [...]
Getting Schooled on Dynamic PIVOT
I wrote a post about Deconstructing Pivot. With my newfound confidence, I decided to tackle dynamic pivots. This is a common scenario where you need to PIVOT, but you don’t know exactly what you’re going to end up with. Basically, you want to allow all of the possible column headers to come back with the [...]
Deconstructing PIVOT
I’m intimidated by PIVOT. I’ve had a heck of a time wrapping my head around it, which is shameful, because Junior Accountants have been making pivot charts in Excel for years. They get it, so why can’t I? Well, I’ve got a few theories… Anyway, I finally got into a situation where I couldn’t avoid [...]
Trusted Foreign Keys
Hey friends! Long time, no see. I know, you’re wondering where I’ve been. Lately, I’ve had the luxury of taking some time to work on Microsoft certifications. I just got through 70-448, Microsoft SQL Server 2008, Business Intelligence Development and Maintenance. Now, I’m working on 70-433, Microsoft SQL Server 2008, Database Development. I’ll admit… I [...]
Posted in Database Development, Performance, T-SQL Tags: Database Development, Foreign Key, SQLServer, T-SQL Comments Off