10 Beginner SQL Tips

Friday, January 18th, 2008

Since it hasn’t been all that long since I’ve been dabbling in SQL (a little bit over a year), I’ve decided to share some tips that would be useful to beginners working on their first query statement. Here are 10 tips in no particular order.

1 - Take The Time To Design Each Of Your Tables

Forecast what it is you’ll be using this table for. Devise a naming system that you’ll adhere to for all your column names. (eg. firstSecond or First_Second). Prepare for expansion and try to keep your layout as clean and concise as you can.

2 - One Query Instead of Two

In most cases (I’m not sure about exceptions, so I’m using the word “most” on purpose), it’s always best to use the least amount of queries that you need. More queries means more work on the backend which, when compounded, might lead to a server running like molasses.

3 - Join Tables To Make One Statement

Since we’re aiming for as little queries as we can, learn how to use the JOIN command to join tables with column values that can be linked together. It’s a little daunting at first, but once you get the hang of it, you’ll use it often.

4 - Validate Data Before

We simply can’t rely on the web visitors to enter each and every form field correctly. You should make sure most forms have enough validation and if needed, develop some sort of CAPTCHA system to deter spammers. Data that goes in clean will lead to less headaches later on.

5 - Deter SQL Injection

In short, SQL Injection is a attack where the malicious user inputs data into your form with the intent of skewing your SQL query. Protect yourself by blocking or encoding characters that have the potential to be used maliciously. Apostrophes are the biggest culprit in my experience.

6 - SELECT What You Need

You should be avoiding using “SELECT *” in your queries unless you’re using each data column after the query. Selecting specific columns will help speed up your SQL queries.

7 - Use More Tables

Don’t be afraid to use more tables. Sometimes, data is better off stored in another table, and referenced by another using a unique identifier. This can help keep your data clean and easier to expand.

8 - You Should Have a Unique Field

Uniques fields are great to have in the event that you don’t have a difference between two rows of data. Sometimes, you’ll need to delete a row and you’ll have problems if it doesn’t have any unique data.

9 - Bit Data Type

I’ve experienced problems where I’ve been able to insert “True” and “False” into a “bit” column on one server, but not on the other. Be sure to get into the habit of using 1 for True, and 0 for False or you might find yourself having to rewrite a lot of code.

10 - Specify Lengths

When you specify your column’s data type, be sure to specify a length (if applicable). For example: varchar(5). I’ll be honest, I’m not sure why, but I imagine there is some benefit or else you wouldn’t have the option.

Hope that helps, happy SQL’ing.