If you want to get into the fields of database development, database administration or data science, there is one skill you should have that will give you an advantage and make it easier to learn the tools necessary to do your job.
This is a skill most database professionals already had before stepping into their respective fields.
The skill is the ability to code.
If you know how to code, you already have many of the knowledge and skills you need to be a great database developer.
Don’t take my word for it. Here’s what other developers had to say:
My friend Bert Wagner over at www.BertWagner.com had this to say when asked if he knew how to code before being a database developer:
“Yes I had experience with development first. I’m guessing that’s the path most people take since querying data is usually a means to another end (pulling data into an app, etc…). Only audience I can think of that might not have a programming background first would be analysts or business users. They may be more comfortable with tools like Excel but have a need to pull their own data, so they start learning SQL.”
I also spoke with Kris Wenzel from www.EssentialSQL.com. He had this to say when asked if he knew how to code before moving into the field of database design and development:
“Yes, I learned to code first. I learned to code when I was in high school on a TRS-80 back in 1979. When I graduated in 1983 from high school, I was a pretty good coder but got much better when I earned my Computer Engineering degree. That is when I learned about relational database theory”
A TRS-80?! Kris is an ‘OG’ for real.
Even if you don’t know how to code, and SQL is the first computer language you are trying to learn, don’t sweat it. This blog is dedicated to teaching the complete novice everything they need to know about querying and developing SQL Server databases.
In this tutorial, we will talk about why the ability to code is a very important skill to have if you want to become a database professional.
If you know how to code, you will already understand:
- Decision structures like WHILE loops and IF/ELSE statements
- Outsourcing code to functions or stored procedures
- Data Types
- Generally how to write “clean code”
Let’s break down each of these topics one at a time:
1. Decision structures like WHILE loops and IF/ELSE statements
In any programming language, there are going to be times when you need to choose a path of code to follow based on if some condition is TRUE or FALSE.
This is true for loops, too. The way a loop works is if some condition is true, you continue looping through a specific block of code. As soon as the condition becomes false, you stop looping and move on to the next line of code after your loop.
In regular programming languages like C++ or C#, decision structures are one of the very first things you learn. Decision structures in SQL are not much different.
Of course, the syntax might be a bit different, but that’s true for most languages. The syntax for an ‘IF‘ statement in C++ is different from an ‘IF‘ statement in Python, for example. It shouldn’t surprise you that an ‘IF‘ statement in SQL is different from either of those.
But again, the “idea” is the same.
If you need a tutorial on WHILE loops and the IF/ELSE decision structures in SQL, you should definitely check out these tutorials:
SQL Server WHILE loop: A decision structure you should know
The IF…ELSE IF….ELSE Statement: Everything You Should Know
2. Outsourcing code to functions or stored procedures
The purpose of a function in regular programming languages is to provide a way to run a block of code repeatedly, on demand. If you find yourself repeating the same (or similar) chunk of code when writing an application, that’s usually a clue that you should write it out as a function instead and just call it when you need it.
SQL Server has a few different tools to accomplish that same goal, including:
- User defined functions
- Stored procedures
Let’s talk about each of those:
User Defined Functions
A user defined function is a database object used to save a lengthy or complicated query so that it can be ran repeatedly using simple commands.
A user defined function will save a single query. If you find yourself writing a very long or complicated query that you know will need to be ran again at some point, you should definitely think about writing it as a user defined function.
Take the following query as an example:
This query basically figures out how many products a specific customer bought, and how much they have spent in total at our store.
But see how long and complex that query is?
If we want to run this query again later, for a different customer, we’ll need to rewrite the same query over again! Wouldn’t it just be easier to save this query as a database object? Check this out:
That long query is now saved as an object called ‘getTotalCustomerPurchases‘. If we need to run that same query, we just call the function!
Notice we also created a parameter. If we want to run the same query for a different customer, we just pass in that customer’s ID!:
Want to learn more about user defined functions? I got you:
SQL Server User Defined Functions: A Complete Guide.
Stored Procedures
The concept of a function in programming languages like Java or C# is more in line with the the idea of stored procedures in SQL Server.
While user defined functions are meant to store a single query, stored procedures are meant to store lots of code and work you need performed.Â
A stored procedure is a database object used to store complex SQL code that can be ran easily and repeatedly using simple commands. They can take parameters as input, as well as return values or a result set to the caller as output parameters.
Let’s think of an example of where a stored procedure would be useful. Say you have two tables in your database called Customers and ContactInfo. Here they are:
Info from Customers table:
The Customers table is meant to store the most basic information we have about a customer, which is just their unique customer ID, and their first and last name.
Info from ContactInfo table:
The idea with this table is to store the contact information we have for each customer. The CustID value is what links the two tables together.
So if we need to get all the information we have on Cindy Thatcher, for example, we get her CustID from the Customers table and use that ID to figure out her phone number, email address and home address from the ContactInfo table.
Stored procedures can save us time and energy
Think about what this means if we need to add a new customer to our system. We would need to add them to the Customers table first, then add a new corresponding row to the ContactInfo table, making sure the ID values match.
The task of “inserting data into two tables and making sure the CustID’s correspond correctly” might be a bit complicated. What if we outsourced all that work to a stored procedure?
Then to do all that work, we just need to call the stored procedure:
We created a new stored procedure called “addNewCustomer“. As parameters to that stored procedure, we pass the person’s first and last name, followed by the contact information we have for that person. In my example, the only contact information we have is their phone number. I guess they didn’t want to give use their email or home address (I get it).
The definition of the stored procedure contains all that work of adding that information to the two tables, and making sure the CustID values match correctly.
But I digress. If you already know what a function is from your knowledge as a coder, you will already be familiar with the concept of encapsulation, and you will already know terms like “input parameter” and “return value“.
These are basic tools and terms any coder would already understand.
Want to see the definition of our stored procedure? Check out the full tutorial on SQL Server Stored Procedures:
Stored Procedures: The Ultimate Guide for Beginners
3. Data Types
Of course, it’s great to know a thing or two about data types as both a coder and a database developer.
When you understand data types, you also understand the creation and use of variables. You are likely to create and use variables as a database developer, too, especially when writing stored procedures.
When you are first learning about data types as a coder, you learn how data types are basically constraints. For example, you are limited in how big of a number you can store in the int data type. If you exceed that limit, you need to change your data type to something bigger.
Also, you understand how you simply can’t store certain kinds of data in certain data types. For example, you can’t store the word “spagetti” in an int data type.
You learn how there are several different kinds of data types suited for many different situations, and it’s your responsibility to choose the best one.
As a database developer, this is also very true. It’s very important to choose the most appropriate data type for the kind of data you need to store. This will keep your database from wasting space, and will help your queries perform faster.
I have a FREE GUIDE that outlines The Top 10 data types you should know as a database developer. You should check it out
4. Generally how to write “clean code”
The last thing you learn (I hope) as a coder is how to write clean code.
It’s very important to make sure your code is readable and can be understood by anyone who reads it after you.
You learn about proper naming conventions, and how a good name ought to describe exactly what it is you are trying to do. For example, if you need to store the price of a product in a variable, a good name for your variable could be “ProductPrice“. A terrible name would be “num1“.
Remember the stored procedure we created earlier called “addNewCustomer“? What if we had called it “doStuff“? You would need to read through the stored procedure to learn what the heck it does! But the name “addNewCustomer” is very descriptive. It wouldn’t take much effort to guess what the stored procedure is used for.
If you write any type of code, whether it’s Python, C#, Ruby or SQL, I guarantee someone is going to need to read and understand your code later. As a developer, you have a responsibility to make sure your code can be easily understood, and the best way to do that is to learn how to write clean code.
Take a look at Robert Martin’s book Clean Code. This book discusses the importance of non-repeating code, as well as many other best practices when it comes to programming. Your coworkers and your future self will thank you. Get it today!
Final Thoughts
If you are a coder, and are thinking about getting into the field of database development or administration, but aren’t sure if you have the knowledge or skills to succeed, let me tell you that you do.
The concepts and principles you learn as a coder are extremely transferrable to the field of database development.
It should go without saying that many software applications are closely tied with SQL Server. Can you imagine how valuable you would be if you knew how to write software solutions in a programming language AND knew how to create, populate, and query a database that ties to it?! You’d be a rock star.Â
Next Steps:
Leave a comment to let me know what you think! I’d be happy to hear your thoughts.
One HUGE topic you should understand is indexes. They are the key to making sure our queries return data efficiently. Take a look at the full beginner-friendly tutorial on clustered indexes to learn more:
SQL Server Clustered Index: The Ultimate Guide for the Complete Beginner
Once you understand clustered indexes, make sure you also understand non-clustered indexes:
Nonclustered Index in SQL Server: A Beginner’s Guide
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!
