If you are in the process of learning how to query and develop SQL Server databases, congratulations, you are learning a skill that is extremely valuable in today’s data driven climate.
There are many great tools you can use when it comes to querying and developing Microsoft SQL Server databases, and there are many best practices and rules you should do your best to follow. In this brief tutorial, we’ll discuss the top 7 best practices you should follow when it comes to querying and developing SQL Server databases.
Here’s the list:
- Don’t use “SP” as the first letters of the name of your stored procedures
- Outline parameter names when you call your stored procedures
- Don’t use Unicode data types if you don’t need to
- Every table should have a primary key
- Define the nullability of your columns
- Don’t use ” SELECT * ” in production code
- Repeated queries should be saved as either a View or a Function
Let’s talk about each.
1. Don’t use “SP” as the first letters of stored procedure names
Part of being a good developer is making sure you use clear and descriptive names for your objects or variables.
It may be tempting to start of your stored procedures with the letters “SP” to help indicate that the object is, in fact, a Stored Procedure. When you do this, it actually makes your stored procedures slightly less efficient.
When you execute a stored procedure that starts with “SP“, SQL Server will think you are trying to execute a system stored procedure because that is what all system stored procedures start with. All system stored procedures live in the Master database, so SQL Server will start there when trying to find the stored procedure to execute.
But it’s not there. SQL Server will not find your user-defined procedure in the Master database, so then it falls back to the database you are currently connected to and look for the stored procedure there.
And it was there the whole time! By starting our procedures with “SP“, we are basically making SQL Server do extra work that we know it doesn’t need to do! So save SQL Server from wasting it’s time and start your procedures off with something other than “SP“! If you want to be descriptive, maybe start them with “PROC” instead.
2. Outline parameter names when you call your stored procedures
Also along the lines of best practices in stored procedures, did you know that you don’t need to outline parameter names when you call your stored procedures?
Take a look at this simple procedure that inserts data into a Customers table:
Here’s an example of calling the stored procedure:
(Wondering how the CustID column is getting populated? It uses the IDENTITY property)
Notice when we called this procedure, we outlined the names of the input parameters, along with their values. If you wanted to, you could leave off the parameter names and just outline the values. Like so:
Understand that when you do this, you must outlined all the parameters in the correct order. That is, the order they are defined in the stored procedure.
If you outline parameter names and mix up the order, there’s no problem. Like this:
We wanted Shannon to be the first name and Mary to be the last name (This person has a double-name, more commonly referred to as two first names). Since we outlined parameter names, SQL Server knows what we want and works correctly.
However, take a look at this call:
This is another double-name. Since we didn’t outline parameter names, we better be sure that we want Elizabeth to be the first name and Jane to be the last name because that is what SQL Server is going to assume.
But just looking at this call, I don’t know which name is supposed to be the first and which is supposed to be the last. It could go either way. If we had outlined parameter names, the guesswork would have been eliminated! So do yourself and your coworkers a favor and just outline parameter names. It simply makes things more obvious.
Learn more: Stored procedure with parameters: A guide for beginners
3. Don’t use Unicode data types if you don’t need to
When I was a newbie at SQL Server, I would often find tutorials that always used Unicode data types to store character string data without any clear reason why.
And since my code worked fine, I didn’t question it. But now that I know a thing or two about data types, I understand how Unicode data types can drastically increase the amount of space your data takes up in memory!
Unicode data types (which are just NCHAR and NVARCHAR) use twice as much space to store each character of a character string as the regular character data types (CHAR and VARCHAR).
NCHAR and NVARCHAR use 2 bytes to store each character of a character string, while CHAR and VARCHAR use 1 byte. So again, if you think about it, that means every character string stored with a Unicode data type uses twice as much space as it would have if you used a regular character string data type instead.
This begs the question: “What should I use Unicode data types for?”
I’m glad you asked. Unicode data types are ideal when you want to store character string values in many languages. The regular character string data types (CHAR and VARCHAR) can only store values in English and one other language.
If you know your data only needs to be stored in English, for example, it would be a huge waste of space to use NCHAR or NVARCHAR data types!
By the way, do you know the difference between CHAR and VARCHAR?
4. Every table should have a primary key
When you create your tables, it’s smart to give them a primary key constraint.
Primary key constraints are a great way to enforce uniqueness among the rows in your table. One of the things that makes a table relational is that all rows in the set are unique.
Also, when you establish a primary key constraint on a column, SQL Server will automatically create a clustered index using that column as the clustered index key.
One of the best ways to improve read performance of your tables is to give them clustered indexes. And again, if you create a primary key constraint on your table, a clustered index will be created automatically for you.
Not sure what a Clustered Index is? Click the link to find out.
5. Define the nullability of your columns
When you create columns in your tables, you need to think about what values can/should be stored in those columns. Specifically, think about if it makes sense to allow NULL values to exist in the column.
Think about the following Employees table:
When you don’t specify the nullability of a column (that is, NULL or NOT NULL), SQL Server will set the nullability to NULL by default. That is, values of NULL are allowed in the column. So since none of the columns in our Employees table specify anything about the nullability, they all default to NULL (except for the EmplID column because it’s a Primary Key column, and Primary Key columns are automatically set to NOT NULL).
Ok, cool. But for example, would a value of NULL make sense in the LastName column? Every person alive ought to have a last name, which means it would be very unusual to have an employee in our database with no last name. I’d say that if an employee has NULL as their last name value, then someone screwed up when entering the data.
We probably should have specified NOT NULL as the nullability for this column.
But for the MiddleName column, a nullability of NULL makes sense. Some people simply don’t have a last name. For those people, a value of NULL in the MiddleName column would make perfect sense.
Bottom line: Think about if your columns should/shouldn’t allow NULL!
Learn more: SQL Server NULL: Are you making these 7 mistakes?
6. Don’t use ” SELECT * ” in production code
The star operator (*) is great for troubleshooting and researching the data in your tables. But for God’s sake, don’t use it in production code.
The star operator will return all columns in a table or object. In real-life production code, you rarely need to pull all columns in a result set. You usually only need a subset.
If you use the star operator and pull all column when you really didn’t need to, you are asking SQL Server to do work that you really don’t need it to do! Instead of using the star operator, just outline the columns you need for the work you’re trying to do. This is a very simple way to increase the efficiency of your queries.
The only place where the use of the star operator is acceptable is in the IF EXISTS decision structure. In that structure, SQL Server is smart enough to know that you don’t really care what the column values are. You just want to know if rows simply exist for the inner query.
Not sure what the IF EXISTS decision structure is? Click the link to learn more.
7. Repeated queries should be saved as either a View or a Function
Finally, every good developer knows the golden rule when it comes to writing new code: Don’t repeat yourself!
SQL Server has a few great tools we can use to basically save complex queries as objects in our database. Views are great for saving a SELECT statement as an object in the database. The View can be as crazy and complicated as you need it to be. At the end of the day, you will be able to call that complex query much easier if it’s saved as a View.
User defined functions are great if you want a value to be returned to the caller. That value can be a single value or an entire result set.
Next Steps:
Leave a comment if you found this tutorial helpful!
Many of the topics discussed in this tutorial can be found in the book T-SQL Fundamentals by Itzik Ben-Gan.
This book has been a great resource for me in my journey to learning T-SQL. I’m confident this book will also help you if you want to step into the Data Science field and understand the fundamentals of the T-SQL language. You won’t regret owning this book, trust me. Get it today!
We also talked a great deal about stored procedures. Check out the full beginner-friendly tutorial if you need to know what a stored procedure is and how we use them:
SQL Server Stored Procedures: The Ultimate Guide for Beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
