I’m sure you’ve all heard about the Union operator, it joins result sets together into a single record set. I haven’t used UNION very often but it’s definitely one of those operations that you are happy for when you need it. The thought of having to write a bunch of temporary table, select/insert queries to get the same data is not an appealing one.
Along with UNION there are two other similar operations that perform logic upon those result sets instead of just pushing them together and returning them. These operations are EXCEPT and INTERSECT. Some of you will probably be laughing at me when I tell you that I wasn’t aware of the EXCEPT and INTERSECT operators. Well you don’t have to laugh any more because now I know! Simply put these operators do much the same thing as UNION except that they perform comparisons between the results of two queries and produce a result set based on the existence of the records in both tables. Good stuff.
Hopefully that title got your attention. When I started reading about some of the things that can cause the query optimizer to ignore your indexes it really caught my attention. Below are a few seemingly innocuous things that you can do in your sql statements to force the query optimizer to happily ignore your indexes.
- Use a NOT operator in your WHERE clause – That’s right, if you don’t want to use indexes just drop a NOT operator in your WHERE clause
- When Using An OR operator, reference columns that are not indexed – If you specify columns in an OR operator that are not indexed, all indexes that are associated with columns in the or clause will be ignored.
- Put a leading wildcard character in your WHERE clause – If you do something along the lines of “select NAME from school_records WHERE name is like %XX%”, the index that you put in to speed up searches of the names will do absolutely no good.
There will probably be MANY more things that I will discover as time goes along related to nuances of indexes but I thought these were interesting because they seem really harmless. I think it’s useful to know that how you go about logically selecting data can impact query performance in potentially drastic ways
This shocked me a bit because I’ve never run into a problem with the evaluation of the boolean operators. I suspect this is because I make liberal use of parenthesis when I’m writing out SQL statements. However boolean operators are evaluated in the following order : NOT, AND, OR. As I said it’s not a huge problem to deal with, a few parenthesis will fix any issues that you might encounter, but it could create some interesting problems if you aren’t aware of it and the parenthesis keys on your keyboard is broken.
The new year always brings with it the optimism that can only come with a perceived “clean slate”, a chance to start over and actually make good on your commitments to yourself. The ability to prove to yourself and others that if you commit to something you will have the integrity to follow through, this time. In the spirit of this notion, I have identified a couple of technical areas where I really want to spend some time getting a deeper, more complete understanding.
One of the these things is SQL Server. I spend a lot of time working with SQL Server and I have a good understanding of SQL Server but as stated previously I would like to spend some time getting a deeper understanding. The great thing about doing something like this is that it tends to generate a lot of interesting things to write about. So I’m going to start a new series of blog posts called the Sql Server Notes. To give myself a framework on which to learn these new things I am going to be working towards the SQL Server Database Developer Certification. I think this will provide a good study guide for me to gain the sort of deeper level of understanding I am looking for, and it will give me something to show for it when I’m done. Please note, the focus here is to really gain an understanding of SQL Server and the Certification is a secondary benefit of the study.
The posts in this series might not be very long, and they will probably be known to most people, but they will be things that I either find important to know, problems I find in the reading/study materials that I am using, or other random bits of information. I will try to make at least one posting a week and keep it up until I have finished my studies. Hopefully we can all learn something new, if not, at least I will have worked on my writing skills and put up a good amount of updates to the site.