I wish query tuning was easy. Today, it’s not. One day machines will do it better than us, but today, doing it well is still somewhat of an artistic pursuit. You can teach most people the basics, but it takes a long time to get a really good “feel” for what needs to be done. However, something that I seem to differ with a lot of people on, is about how many indexes is too many?
2019-07-18
Most SQL Server tables should have a clustered index. There are some exceptions to this but it’s a pretty general rule, and if in doubt, you should follow it. (Note that this is not the same as having a primary key).
I regularly come across tables without clustered indexes for all the wrong reasons. So, in our free SDU Tools for developers and DBAs, we added a tool that can look for user tables that don’t have a clustered index. No surprise, it’s called ListUserHeapTables because a table without a clustered index is a heap.
2019-07-17
I continued to be stunned at how banks don’t get security.
Had a call just now from a sales guy from the my bank. It’s annoying enough that they call at night, but I really don’t like it when they want to confirm who you are before they can talk to you.
Told him, Sorry, can’t do. Have no idea who you are.
Why would I tell personal details to some guy who just calls out of the blue, claiming to be from my bank?
2019-07-16
The TOP operator is used to restrict the number of rows being returned. If you’re familiar with other database engines, you might have used a LIMIT operator. TOP is a bit like that.
You can see that in the first query shown below:
If you look at the bottom query, you can see that it can also be used to limit the rows to a percentage instead of a number of rows.
2019-07-15
I’ve mentioned previously that I’ve come across Graeme Simsion previously in his role as a well-known data modeller, based in Melbourne here in Australia. I’ve recorded a podcast with him many years ago, on my SDU Podcast series. So perhaps I have a slight bias towards him as an author.
I was so excited to see the endless well-deserved congratulations he’s received for his initial Rosie Project book. I thoroughly enjoyed that book.
2019-07-12
I noticed a post the other day by one of my friends and fellow MVP Aaron Bertrand where he mentioned that he used to scoff at the TRIM() function, at least until he read the documentation.
Almost as long as I’ve worked with SQL Server (since 1992), I’ve heard people complaining about the lack of a TRIM() function. No-one could understand why we had to keep writing both LTRIM() and RTRIM() to get the required result. So it wasn’t surprising that we were all happy in SQL Server 2017 when TRIM() finally got added.
2019-07-11
When you create a SQL Server stored procedure, some session-related settings are part of the configuration of the procedure. The two most common problems with this are related to ANSI_NULLS and QUOTED_IDENTIFIER.
When procedures are scripted, SQL Server tools also script the settings for the session, but what often happens is that the person creating the procedure, just executes the CREATE PROC statement and doesn’t execute the SET statements before it. When they do this, you can end up with procedures that don’t have the settings you expect.
2019-07-10
I’m writing this post more to remind myself next time I run into the same problem, but hopefully it’ll help someone else too.
curl is a useful utility but the Windows version of it certainly has some quirks. Often though, that leads to error messages that aren’t helpful at all.
I kept running into an error where it complained that HTTPS protocol was not supported in libcurl. When I tried HTTP, I saw the same error.
2019-07-09
The T-SQL IN operator allows you to choose from a list of matching values.
In the top case shown below:
I’ve said where StateName is one of QLD, NSW, or VIC.
Now see that’s exactly the same as if I had written the option shown at the bottom where I said:
where StateName equals QLD or StateName equals NSW or StateName equals VIC.
The two options work exactly the same and mean the same thing, so having an IN operator is logically equivalent to having a whole lot of predicates joined by OR operators.
2019-07-08
In an earlier post, I showed how to create a function that worked out if a date was a weekday or not. In this example, I’ve used that function to create a more complex function that adds a number of weekdays to a given date.
In Power Query, go to the Edit Queries option and choose to add a new query. (You could also start by duplicating an existing query). Then on the View menu, choose the Advanced Editor and replace any code that’s there with this:
2019-07-05
