SARGable functions in SQL Server

SARGable is an adjective in SQL that means that an item can be found using an index (assuming one exists). Understanding SARGability can really impact your ability to have well-performing queries. Incidentally – SARGable is short for Search ARGument Able. If you have an index on phone numbers using LastName, followed by FirstName, including the suburb and address fields, you have something akin to the phone book. Obviously it becomes very easy to find people with the surname “Farley”, with the first name “Rob”, but often you want to search for people with the surname “Farley” with the first name … Continue reading SARGable functions in SQL Server

High ROI items for SQL Server 2008

To persuade your boss to embrace an upgrade to SQL 2008, you need to know which features have high Return On Investment. They may have seen presentations talking about features like Spatial, or MERGE (and been quite impressed), but they may well have left those presentations thinking about the effort that’s would be involved in rewriting applications to take advantage of these features. It’s all well and good to see your customers on a map, but someone has to make that spatial data appear somewhere. This post is a callout for features that will benefit you (and your boss) as … Continue reading High ROI items for SQL Server 2008

Missing Index in SQL Server 2008 – should try harder!

Ok, maybe I’m being a little harsh, but I just feel like it should be better. Let me show you the niceness of the way that missing indexes are handled in SQL Server 2008. Using AdventureWorks (not AdventureWorks2008) on a SQL Server 2008 install, if I show the Execution Plan from this simple query, I get a nice suggestion. My query… select productid, orderqty from sales.salesorderdetailwhere carriertrackingnumber = ‘FB88-4B92-82’; …could be improved through better indexing. It uses 1240 reads to get this data, which seems awful. The system shows me that it could be improved, and suggests an index. It’s … Continue reading Missing Index in SQL Server 2008 – should try harder!

Ctrl+1 in SSMS for sp_who, plus more

I did this by accident, but turns out it’s a really useful feature. I was just trying to zoom in on something using Zoomit, that really useful thing that I use whenever I’m presenting these days. But I didn’t have Zoomit running, and so SQL Server Management Studio ran sp_who. And it’s not even new. This is an old Query Analyzer thing – I just hadn’t come across it before. And it’s extensible! Go to Tools, Options, Environment, Keyboard. By default, sp_who is Ctrl+1, sp_lock is Ctrl+2 and sp_help is Alt+F1 (which runs on whatever you have highlighted). Then pick … Continue reading Ctrl+1 in SSMS for sp_who, plus more

Design Query in Editor bug

Ok, so real database developers don’t use the graphical “Design Query in Editor”… yeah, I know. Sure, there’s the odd time when you’re typing a query and you don’t have an Object Explorer (eg, in SSIS) and a moment of weakness sees you hit “Build Query” to save some typing, but in general I encourage people to write their queries in Management Studio SSMS and then copy them into the SSIS dialog. I was showing someone some of the frustrations I have with the graphical editor, and came across a real beauty – repeated predicates. I logged a bug at … Continue reading Design Query in Editor bug

Fuzzy in T-SQL

SQL Server gives you Fuzzy Lookups and Fuzzy Grouping, but only in SQL Server Integration Services. It’s not even on the list of SSIS enhancements for SQL Server 2008. This week at the MVP Summit, I was having a discussion about this with Jamie Thomson, and we had a chat with one of the T-SQL guys to suggest it. The response came back with “Log in on connect”, which I did just now. https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=338664 Please check it out and provide comments, votes, validations. All this will help persuade Microsoft to implement this useful feature. And tell your friends too! The … Continue reading Fuzzy in T-SQL