Hire the best SQL developers

Why hire a SQL developer?

Common SQL mistakes

SQL (structured query language) is a simple language, but it’s not so simple when you’re working with data sets that contain millions of records. When you work with mid- to large-size tables, writing top-performing SQL statements is a priority. Before you sit down to write your SQL statements for any platform—Oracle, SQL Server, MySQL, or any others—here are the top 10 common SQL programming mistakes to avoid.

1. Forgotten primary keys

Every table requires a primary key for performance. If you don’t have a primary key in any table, your tables don’t follow standard SQL requirements and performance suffers. Primary keys are automatically set as clustered indexes, which speeds up queries. They are always unique, so you can use an auto-incremented numeric value if you have no other column in the table that meets the unique requirement. Primary keys are your first step to relational databases.

They link to foreign keys in relational tables. For instance, if you have a table that has a list of customers, the “CustomerId” column would be unique to every customer. This could be your primary key column. Your CustomerId value would then be placed in the Orders table to link the two tables together. Always use a primary key in every table you create regardless of its size.

2. Poorly managed data redundancy

Data redundancy is good for backups but not for table data. Each table should contain a unique data set that doesn’t repeat data in other table locations. This is one of the more difficult ideas for a new SQL developer to follow. It’s very easy to forget normalization rules and repeat data across multiple tables for conveniences, but it’s not necessary and represents poor table design. For instance, suppose you have a customer table that contains the customer’s address. Since the address relates to the customer, it’s in the proper location. You then create an “Order” table and add the customer’s address in the Order table.

This type of data redundancy is poorly designed. The Customer and Order table can link together using relationships across primary and foreign keys. What happens when you forget to update the customer’s address in the Order table? The result is that you now have two addresses for the customer and you don’t know which one is accurate.

3. Avoid NOT IN or IN and use JOIN instead 

NOT IN and IN statements are not well optimized. They are convenient, but usually they can be replaced by a simple JOIN statement.

4. Forgotten NULL vs. empty string values

The NULL vs. empty string debate between database administrators has been ongoing for decades. You can choose to use NULL values when no value is present or you can use actual literal values such as zero-length strings or 0 integer values. What you use in the database should be uniform across all tables or queries can get messy. Remember that NULL values aren’t the same as, for instance, a zero-length string, so your queries must account for these values if there is no standard in your table design.

When you determine what you want to use, make sure your queries account for these values. For instance, if you allow NULLs for a user’s last name, you must query using the NULL filter (either NOT NULL or IS NULL) in your clauses to include or exclude these records.

5. The asterisk character in SELECT statements

Always define the columns you want to return in your queries. This standard is for performance and security reasons. Take the following query, for example. SELECT * FROM Customer The query returns all customer values including any sensitive data you might keep in the table. What if you keep the customer’s password or social security number in the table?

Hopefully, these values are encrypted, but even having the hashed value can help hackers. It’s also a performance issue if you have dozens of columns in the table. Instead of the above query, always define each column. The following statement is an example. SELECT CustomerId, FirstName, LastName FROM Customer The above statement defines each column and limits the size of the record set as well as what a hacker can see should there be a data breach.

6. Looping with too many cursors

Cursors, the looping structures in the SQL language, are the bane of database performance. They allow you to loop through millions of records and run statements against each one individually. While this may sound like an advantage, it can destroy database performance. Loops are common in programming languages, but they’re inefficient in SQL programming.

Most database admins reject SQL procedures with cursors implemented. It’s best to write the procedure in another way to avoid adversely affecting database performance if possible. Most cursors can be replaced by a well-written SQL statement. If you can’t avoid it, then cursors should be kept to scheduled jobs that run during off-peak hours. Cursors are used in reporting queries and data transformation jobs, so they can’t always be avoided. Just limit them as much as possible in production databases that perform daily queries against your database.

7. Data mismatches in field assignment procedures

When you declare your table columns, you must give each column a data type. Make sure this data type covers all the necessary values you need to store. Once you define a data type, you can only store that type of value in the column. 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.

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.

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. 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.

Tips and best practices for SQL programmers

Structured query language (SQL) is the primary language for databases. Whatever relational database platform you decide to use, you’ll need to use SQL to edit, insert, delete, and query records within the database. Each platform has small differences between their respective SQL syntax, but logic and development are still similar. The language is different than NoSQL, which has similarities but mainly different syntax. Here are some tips and best practices for any SQL development project.

1. Think about relationships

The SQL language is the language of relational databases. You need to build tables based on relationships without creating “orphans.” Orphans are tables that don’t have a relationship with any other table and are poor database design. Each table should have a relationship with another, so when you query the database for records, you can join the tables together through their designated relationship.

You build a relationship through primary and foreign keys. For instance, suppose you have a customer table and an order table. Without the customer ID in the order table, the order table is orphaned. By placing the customer ID in the order table, you now have a relationship between the two tables that you can use to create queries.

2. Always consider performance, even if the query seems simple

Just because your SQL data set has few records currently doesn’t mean the data set won’t grow significantly after you move it to production. You should join tables on indexes and always have a primary key in each table.

One common mistake is to run a poorly performing query in development when the system has no users querying and very few records. The query runs fast, because there’s no load on the database server. Then, when the query is promoted to production and run in a busy environment, the query performs poorly and undermines site performance. Always consider performance even if the query seems like it won’t need many resources from the database server.

3. Use table aliases for easier reading

Let’s say you’re a developer who needs to maintain someone else’s code. Or, you’re a business owner who needs to hire a SQL developer to fill in the gaps with a specific project. The SQL developer can pick up where another developer left off much quicker if the code uses aliases and is well formatted.

Aliases shorten the name of a table and make it easier to read and determine the logic in a SQL statement. When you build a database, always consider the way the code is written. It should be easy for you and another developer to determine its logic and data set. Poorly coded SQL code can lead to bugs should another developer need to edit it.

4. Be specific in your SELECT clauses

In any common SQL language, the asterisk ( * ) tells the database engine to return all columns within a table. There are several issues with this habit. The first issue is security. If a hacker is able to use a SQL injection attack on your database, it could leave every column available for theft. If you have customer passwords stored in the table, the attacker can expose passwords.

The second issue is performance. If you have a million records returned from a query, you affect performance when you return a couple dozen columns rather than the few that you need.

The third issue is that it’s difficult to determine what data will be returned. If you specify columns in your SELECT queries, you know exactly which ones are returned to the front-end application. For these three reasons, always specify the columns you want to return and avoid using the “all” or asterisk character in your queries.

5. Use NOCOUNT for large batch queries

When you perform ad hoc queries such as updates and inserts on your database, the engine first performs the action, then gives you a count of the number of records that were affected. This is great for one-off changes when you want to confirm that changes were made to a specific number of records, but it shouldn’t be done on queries that run regularly. When NOCOUNT isn’t used, the database must count the number of rows that were affected. This isn’t a necessary count with production queries that run regularly. Use NOCOUNT at the top of your stored procedures or ad hoc queries to improve performance.

6. Avoid dynamic SQL

SQL injection is one of the most common attacks on the web. This type of attack leads to severe data breaches that expose millions of records to an attacker. Extremely strategic SQL injection attacks can even elevate permissions for the attacker to give them administrative rights on the database server. Dynamic SQL is a type of coding that builds a SQL statement based on input from a user.

Typically, it’s done on the front-end application side, but some SQL developers use it too. Dynamic SQL should be avoided at all costs—it’s this type of development that leads to SQL injection exposure. If you absolutely need to use dynamic SQL, you should “scrub” data input and make good attempts to detect malformed SQL statements. You can use third-party tools or integrated tools available with some languages. For instance, the C# language has the TSqlParser class to help programmers identify SQL injection in a dynamic SQL environment.

7. Don’t forget Object-Level security

Security should always be a priority when designing a database regardless of the platform you use. Object-level permissions provide security based on the user and what the account can access. For instance, you want to use a separate user name for all of your databases and give them access to the database accordingly.

By using this method, if a hacker gains access to the account, they’ll only be able to access one database and not the entire server. You can be even more specific with object-level permissions. This tells the database the exact tables and even columns that the user can access. By using object-level permissions, you limit the amount of exposure if an attacker is successful at SQL injection. For instance, suppose you store social security numbers in the database. A public-facing user application should never have access to this information. You only want internal employees to have access. So, you create a public-facing user account and give it object-level permissions to the table, then access rights to specific columns, excluding the social security column. If a hacker gains access to this account, they won’t be able to view social security numbers, which limits your data breach exposure, risk, and damages.