Browsing the archives for the T-SQL category
This is one of those posts for myself. Sort of like a self-note. When things go wrong, especially unexpectedly, sometimes the only clue is the SQL Server default trace. Where is it? SELECT * FROM ::fn_trace_getinfo(0) Now you know… But do you have permissions to get to the file? That’s another issue. For a [...]
This post is actually less about parallel query plans and more about a recap of some lessons learned from a recent project. I run an in-house T-SQL training class for new hires and other employees who either need to get up to speed with T-SQL or have a personal desire to learn. In the very [...]
It doesn’t happen very often but I recently needed to add a default value to an existing column. Of course MSDN provided me everything I needed but figuring it out from the railroad diagrams took a few tries. To add to the confusion, the command for SQL Server Compact Edition is different than it is [...]
A simple question was posed today while giving a presentation on query plan analysis to colleagues at the office. What’s the difference between a composite (multi column) index and an INCLUDE covering index? At the time I was stumped. Fortunately I don’t have any problem admitting I don’t know something… it happens all the time. [...]
I had a situation happen at work today that I never would have guessed is possible. But since it happened, it is possible. It’s all about how I used a synonym for a table in a DELETE statement. Since SYNONYMS are not commonly used, at least not in my circle of friends, I’ll take a [...]
I’ve been watching some Webcasts by Ramesh Meyyappan at SQL Workshops on query performance tuning. This guy is a wizard… inspiring. Many of his techniques, which he just does like second nature, may require someone to slow things down to really articulate what he does. Sort of like watching an instant replay during a sports [...]
The objective of my last post, Testing HASHING to Speed Up Queries on Strings Part I, was to demonstrate query lookup performance when querying hash columns instead of the original string value. In my test I chose a small set of about 20,000 email addresses as the base test case. To recap the last post: [...]
It’s common knowledge that relational database engines do well indexing numeric data, particularly integer data. String data is a little more tricky and tends to take longer to search / index. My original intent of this post was to simulate how the SQL Server storage engine might handle statistics on an index, in particular, a [...]
SQL Server 2005 introduced many new switches and features in T-SQL. Often times new features are met with “Why would I need to do that?”. The reality is that the SQL Server development team are not adding features for nothing. One of my favorites is the OUTPUT clause. The OUTPUT clause can be used in [...]
I just wrapped up my first ever attendance at a SQL Saturday. I was not disappointed. But not only was I an attendee, but I was also a presenter (speaker) too. The event was held at the Microsoft campus in Charlotte. The facility is first class and the principal organizers, Peter Shire and the management [...]
A couple of weeks ago a colleague asked me how to determine the recovery model of a database using T-SQL. In other words, not using SSMS. For the purpose of this post I’m going to show how to determine the recovery model for a database using SSMS, and then answer the presented question. The following [...]
One way to find out if a transaction is causing a block is with the sp_who2 system stored procedure. The scope of this procedure is server-wide. It identifies all connections to the database, the login name, the application that is running, and in the context of locks and blocks, any SPIDs that are causing a [...]
Late last Friday afternoon I was working on a database quality issue that had been challenging me all day. Essentially I was looking for any distraction that would give the left side of my brain a break. Along comes two database colleagues to my rescue… These two gents came up to me with a somewhat [...]
If you’re a SQL developer or dba working for a small company it’s pretty easy to become the big fish in the small pond. But the downside is everything you learn is gained through the school of touch lessons. You probably don’t have regular exposure to high end SQL expertise, so your knowledge will only [...]
I was on my way out the door when a respected colleague bounded over with a quick question. He needed to query a column in a table that had a single quote embedded in the string. This is one of those little SQL tricks that can really slow you down. I don’t know when or [...]
Recently I’ve been working on performance issues relating to SELECT queries hitting some very large data warehouse stage tables. In one instance I encountered a table that had over 300M records where the SELECT statement that reads from the STAGE table had an estimated execution cost of 3500. I didn’t find this by accident… it [...]
Fix a bug… discover another… Hence the birth of this post. I had a production SQL Agent job that called a sub-job using the sp_start_job system stored procedure (step 1). The next step (step 2) involved a simple update of a configuration table date column. My assumption was that when step #1 finished, step #2 [...]
A colleague over the weekend sent a note out to the team requesting any kind of insight. It turns out that he had a job fail because an instance of the T-SQL ISNUMERIC built-in function behaved in an unexpected way. According to BoL: “ISNUMERIC returns 1 when the input expression evaluates to a valid numeric [...]