I often go out to clients and have to do some quick analysis of the client’s SQL Servers. Below is one of the SQL scripts that I’ll use when I want some to give some quick feedback on whether the files and their properties are configured properly. Not a very tricky script but something that … Continue reading Database File Information
Change Trusted State of Foreign Keys
Ever want to change those foreign keys created with NOCHECK to CHECK? Not exactly an earth shattering requirement but having all of the foreign keys trusted helps keep the relationships in your databases solid. Exactly what a good foreign key should be doing. Changing the foreign key to trusted does have the risk of uncovering … Continue reading Change Trusted State of Foreign Keys
UPDATE – ORDER BY and Deadlocking
I was working on a deadlocking issue a few days ago at a client The transaction that was continuously getting deadlocked utilized three SQL statements to checkout and return rows from a queue for the client’s application. Before going into the problem here’s a little code to setup the tables for the examples: The SQL … Continue reading UPDATE – ORDER BY and Deadlocking
DMV Version of sp_who2
I put together a presentation on Dynamic Management Views (DMVs) a few months back and one of queries I put together for that presentation was a version of sp_who2 that provided similar results but used DMVs instead. The query has most of the same columns as sp_who2 with some additional items that I found useful … Continue reading DMV Version of sp_who2
The OVER() Clause
Thought I'd share a cool trick I picked up a while back with the OVER() clause. Typically you’d use this clause with the PARTITION BY or ORDER BY arguments with the ranking functions released with SQL Server 2005. The OVER() clause though can also be used to do aggregations with a SELECT statement without a … Continue reading The OVER() Clause
Search Cache For Execution Plans
Updated content at - Really Search Cache For Execution Plans Sometimes when I am working with clients I need to take a look at execution plans for different stored procedures. Rather than digging around with SQL Profiler or executing the stored procedures with what I think might be the parameters I like to '”return to the … Continue reading Search Cache For Execution Plans
Raw Data
Every now and then I need to put together demonstrations. I don’t usually like to “invent” data and like to look for sources of raw data on the internet. A friend forwarded me a link to http://www.google.org/flutrends. The site has a download for current and historical flu trends by state. Cool site and even cooler … Continue reading Raw Data
Using Twitter
I am putting this together mainly to introduce Twitter as part of social computing to some of co-workers. Only a couple of us at my consulting firm utilize Twitter and I am hoping that this blog will inspire a few more people to give it a try and “feel the flow” of the twitterverse. When … Continue reading Using Twitter
Find Duplicate Foreign Keys
A few weeks back I was working on a process that would utilize foreign keys to crawl from table to table to export data from a production database to an archive database. More on some of what went into that process probably at some point in the future. When the project was released to production … Continue reading Find Duplicate Foreign Keys
Scripting Object Level Permissions
A while ago while making changes to a client database, I needed to determine all of the object level permissions that were in the database I was working on. The system stored procedure sp_helprotect turned out to be pretty useful in obtaining this information. The procedure accepts four parameters: They do pretty much what would … Continue reading Scripting Object Level Permissions
Strate SQL 