Write SQL Faster
I must admit that sometimes I can be impatient.
Watching other people write a SQL Query can be one of those moments.
When watching someone who is not that experienced yet, I just have to suppress that feeling.
But if you write Queries on a daily basis, speed can be important.
So in this post I will tell you about some of the tricks I use to speed up writing Queries.
Alias your tables
Always using an Alias for your table is a good idea.
There are three good reasons, but first let's clear up what a Table Alias is.
Let's say you need to write a Query on a Table called Items.
You could start of with something like this:
from Items i
Do notice that I have put an "i" after the Table name.
This is what is called an Alias and it is like an abbreviation.
Now if you want to replace the * with some Column Names, you will notice the first advantage.
Just type "i." and you will see that intellisense now becomes an even better friend.
Normally, intellisense will show you everything you could type and that can be a lot.
Using the Alias and a dot, you now only get the available Columns for the Table:
from Items i
Using an Alias is even more handy when you need to Join some tables. Compare these two:
from Items
inner join ItemPrices on Items.ItemCode = ItemPrices.ItemCode
select ItemCode, Description, syscreated
from Items i
inner join ItemPrices ip on i.ItemCode = i.ItemCode
Ok, it might not seem like a lot now, but it gets better.
If you would run these on an Exact Globe database you would get an error because ItemCode and Description are in both Items and ItemPrices.
Now you would need to go back and specify i.ItemCode and i.Description anyway.
And that takes much more time than when you would have done that straight away.
A query often starts easy with just one table, but most often you will be using a Join pretty soon after that.
And then you will need to Alias everything anyway.
So why not make it a common practice to Alias everything straight from the start?
Also, when you want to check which Columns you might need from the ItemPrices Table, you can simply address them with the Alias:
select *
from Items i
inner join ItemPrices ip on i.ItemCode = ip.ItemCode
-- A Better solution:
select ip.*
from Items i
inner join ItemPrices ip on i.ItemCode = ip.ItemCode
Ok, enough about Aliasses and let's see about some other tricks.
Some tips to improve your speed
Click to select
If you want to select a single word, like a Column Name, simply Double Click on that word. No need to use your mouse to select it, or use Shift plus the Arrow Key.
Don't Click to select
If you want to select a whole line, just make sure your cursor is on that line and click Ctrl+C. Now go to where you want to copy that line and hit Ctrl+V. There you go, as you can see there is no need to select the whole line.
Triple Click to select
You would rather select the whole line so you can see what you are about to copy? Well, good point so just Triple Click on any word in the line. Again no need to use your mouse to select it, or use Shift plus the Arrow Key.
Use Clipboard HIstory
So you copied line 1 and then line 2, and now you need to copy line 1 again? Don't bother to select line 1, just simpy hit Ctrl+Shift+V. That will copy line 2 again first, but then hit V again (while still holding Ctrl+Shift). Yes, now it copies line 1 instead of 2. In other words, Ctrl+Shift+V gives you access to your clipboard memory. And by hitting V again, you can go back to what you had on the clipboard before.
Use LIne Numbers and Ctrl+G
Do you have a long query and it takes too much time with the Arrow Keys to go up or down? Does switching between your Keyboard and Mouse slows you down? Why not enable Line Numbers from Tools, Options, Text Editor, All Languages? After that, simpy use Ctrl+G and specify to which line you want to jump.
That is it for today!
Well, here you have some tips to improve your speed while writing Queries.
Of course it is better to write a good Query slow than to write a bad Query really fast.
But why not be good and fast? It makes writing Queries also a lot more fun!
So enjoy these tips and let me know if you have any other tips and tricks you use!
You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!