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
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
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
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
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
A good performance tuning trick in SQL Server 2008 from the SQL Programmability team and a response reminding everyone that it isn't a silver bullet from Joe Sack. While cool, this item doesn't count towards my blogging goal for the year. Too bad, still a cool thing to play with.
Have you ever needed the definition of a view, stored procedure, or user defined function? I sure hope you have - otherwise reading any more of this posting will be more than pointless. For those of you still with me, have you ever also not wanted to find that definition without have to browse through … Continue reading I Don’t Want To Use the Object Browser (sp_helptext)
Occasionally, I run across procedures that mimic the functionality of the IDENTITY property. There are always various reasons for these procedures to exist, some valid and some not quite so. Recently while trying to tune one of these procedures that someone had added interesting locking hints to a creative rewrite to the procedure was suggested. … Continue reading Incrementing Values
Often times I get to write calculations where the it is necessary to divide one value from another. Simple little math equations. But in many of these cases, the denominator has the chance to be valued at zero (0). And for these, I usually write my SQL statements similar to the following: A simple case … Continue reading Divide By Zero
I read a couple of NULL articles back-to-back today from SQLServerCentral. The first had to do wit the Four Rules of NULL and the second was NULL Versus NULL. Both were pretty good and picked up on how SQL Server uses and characterizes NULLs. I can think of a handful or fifty times that the … Continue reading It’s All About The NULL
Strate SQL 