When you declare your table columns, you must give each column a data type. Make sure this data type covers all necessary values you need to store. Once you define a data type, you can only store that type of value in the column.
For instance, you probably need decimal precision by 2-3 points in a column that stores a total order cost. If you assign this column as an integer, your database will only be able to store whole numbers with no decimal values. What happens with the decimals depends on your database platform. It could automatically truncate the values or give you an error. Either alternative could create a major bug in your application. Always account for what you need to store when you design your tables.
This includes writing queries—when you write your queries and pass parameter values to a stored procedure, the variable must be declared with the right data type. Variables that don’t represent the column data type will also throw errors or truncate data in the process.
8. Logical OR and AND Operations
When writing your queries, it’s easy to miss simple logic order. AND and OR statements can greatly change a data set. You can avoid common SQL bugs by either using parenthesis or organizing your statements to represent the logic that should be applied.
Let’s take a look at a SQL statement that mixes the AND and OR operators.
WHERE FirstName = 'John' AND LastName = 'Smith' OR CustomerId > 0
The goal for the statement above is to get any customers with a first and last name of “John” and “Smith” and the customer ID is greater than zero. However, since we mixed the AND statement with an OR, all records where the customer ID is greater than zero are returned. We can overcome this logic hurdle by using parenthesis. Let’s add them to the above statement.
WHERE (FirstName = 'John' OR LastName = 'Smith') AND CustomerId > 0
We’ve changed the logic for this statement. Now, the first parenthesis set returns all records where a customer’s first name is John or the last name is Smith. With this filter, we then tell SQL to only return values where the CustomerId is greater than zero.
These types of logic statements should be well tested before releasing them to production.
9. Subqueries Must Return One Record
Subqueries aren’t an optimal way to write SQL statements, but sometimes they can’t be avoided. When you use subqueries, they must always return one record or your query will fail.
Let’s take a look at an example.
(SELECT OrderId FROM Order o WHERE c.CustomerId = o.CustomerId)
FROM Customer c
In the query above, we get a list of Customer IDs from the Customer table. Notice that we get an Order ID from the Order table where the customer ID matches. If there is only one order, this query runs fine. However, if there is more than one order for the customer, the subquery returns more than one record and the query fails. You can avoid this issue by using the “Top 1” operator.
Let’s change the query to the following.
(SELECT Top 1 OrderId FROM Order o WHERE c.CustomerId = o.CustomerId ORDER BY OrderDate)
FROM Customer c
In the above query, we only pull one record and we order the records by date. This query gets the first order placed by the customer.
10. JOIN on Indexes
A table should always be well indexed, and you can take advantage of these performance enhancements by using the JOIN statement on columns assigned to an index. In the previous examples, we used the primary and foreign keys of a table. The primary key column is always an index, so you don’t need to worry about performance there. However, the foreign key should have an index on it as well.
Any JOIN statements you use should have an index set on the column. If there is no index, consider adding one to the table.
Relational databases are perfect for most back-end procedures, but you need to create the right SQL statement and optimize tables and queries for the best performance. Avoid these ten SQL pitfalls, and you’ll be on your way to creating a fast, efficient database for any small, medium, or large online business. Looking for a SQL programmer? Read this article to help you write a stellar SQL job description to attract the best pro for your project.
Upwork is a freelancing website where businesses of all sizes can find talented independent professionals across multiple disciplines and categories. Ready to let freelance experts help you get more done?Start today!
With the slogan, "There's more than one way to do it," Perl came on the scene as a flexible, powerful server-side language that could do more than one job. And for a number of years, Perl made very important contributions to modern web technology—especially for a language that was developed before the web even came to be. Read More
Are you planning to hire a software developer but aren't sure what qualification level you need? Read on to know the difference between junior, middle, and senior developers, their skills and areas of responsibility. Read More