Browsing the archives for the T-SQL category
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 [...]
I’ve been a SQL Practitioner since 1996 when 6.5 was the hot item. Along the way I’ve learned a bunch of stuff about SQL Server. Without a doubt, the more I learn, the more I know what I don’t know. Being humbled on a regular basis is probably a good thing, but sometimes it’s just [...]
Today’s post will demonstrate how to partition an existing table without disturbing the existing data in the table by taking advantage of it’s clustered index. SQL Server Books Online (BOL) is laced with interesting tidbits of information but sometimes falls short of delivering on the specifics. Let’s face it, there’s no way to demonstrate every [...]
I recently had a situation where I needed to compare table columns between the production table and a test/development table. Chances are if I needed to do it someone else will to. I like using the EXCEPT SET OPERATOR. Some people might prefer to use an OUTER JOIN to determine the column differences. With the [...]
In several of my production databases I have to deal with tables that have hundreds of millions of records. Querying these tables are challenging; doing updates to them can be nightmarish. I recently had to add a column to a table that had 65 million records. The table is an aggregate table so the new [...]
Today’s post is going to be short and sweet. About a year ago I put together a procedure that allows me to see the run times of all of the SQL Agent jobs on a database server. Some might argue that I got a little carried away when I added the standard deviation and variance [...]