3 Tips for Writing Good Code as a SQL Developer
SQL Server Developers are under-rated.
That’s right! I’m a DBA and I said, “SQL Developers are under-rated.” Dedicated SQL Developers help I.T. teams by writing efficient code that gets just the data that is needed and in a way that leverages how the database engine works best. How do you ensure you’re doing great work for your company and building code that will stand the test of time?
I’m so glad you asked!
Tip #1: Be continuously learning how to write better T-SQL
Focus on continuously learning so you’re not stuck repeating less than ideal ways of writing T-SQL. So many people get to a functional level in a skill area and then stagnate, thinking that they’ve learned enough in that area. That’s a great way to start building mediocre code for your company. That code will need to be re-written sooner rather than later. That code will be re-written by someone else – someone who IS continuously learning and improving.
For example, some people learn about CTEs (Common Table Expressions) and then their code is full of them. They have their place, but they also have their place! Don’t use them everywhere. A common myth about CTEs is that they only get called once. That’s simply not true.
Another example I’ve seen is writing code that calls a view that then calls multiple other views. This will result in SQL Server doing more work than is necessary to get your results because often you aren’t interested in all the columns in all the views that are called. You are likely to only need some of the columns some of the time.
To improve your T-SQL game, check out these people who have resources for learning T-SQL: ItzIk Ben-Gan, Kendra Little, Kathi Kellenberger, Elizabeth Noble, Erik Darling
Tip #2: Learn what indexes are available as well as when and how to use them
SQL Server has clustered and non-clustered row-store indexes, clustered and non-clustered columnstore indexes, spatial indexes, and filtered indexes.
These all have situations in which they should or shouldn’t be used. Learn those situations. For example, the first rule of normalization is that a table should have a Primary Key. Yet, I see a lot of databases with tables without Primary Keys! Non-clustered row-store indexes are great for helping SQL Server access commonly queried columns and columns in joins and WHERE clauses.
Columnstore indexes are fantastic, when used appropriately. If you have a table that is primarily, or only a reporting table, and is not frequently updated, Clustered and non-clustered columnstore indexes can be of great benefit to your queries. IF you apply them to tables that have a lot of changes, they will, however, cause performance degradation.
Don’t over-index tables. Look at index usage information so you know in what ways the index is or isn’t being used. Take a look at the reads versus writes and carefully consider whether an index is helping your queries, or hurting them by being heavily changed by INSERT, UPDATE, DELETE, versus being heavier on reads. Do the work of index consolidation thoughtfully before applying new indexes.
There are lots of places to start with learning indexes. Some of the people I mentioned above will have resources about proper indexing for SQL Server. Here is one place to start.
Tip #3: Learn how to design SQL Server tables
Data types should be used appropriately and should match the data types of other corresponding columns in other tables so that data type conversions don’t happen when queries execute. If you choose a data type like VARCHAR, and then try to join that on an NVARCHAR column in another table, you’re going to cause an implicit conversion. Those hurt performance.
Also, think carefully about what columns of data are going into a table so that proper normalization is used. An online transaction processing system where speed of insert, update, delete is important, will likely suffer a variety of performance issues if you have not separated groups of logically related columns into their own tables. I’ve seen Customer tables stuffed with every piece of customer related data imaginable and the table is 30,40,50 columns wide. This wreaks havoc on insert update and delete operations when data isn’t properly normalized.
A key resource on table design is Louis Davidson. You can check out his blog here or a book here.
If you do these three things, you and your company will spend less time re-writing code to address performance problems. The company will also experience less pain from deciding to live with poor performance instead re-writing code. In other words, the initial code you write will have longevity. It can be written once and not be touched again for re-writes for a long time.
Next Steps To Take
- Grab a learning resource from an above link that will help you write better T-SQL.
- Do an internet search on “Indexes Kendra Little” or add in any of the above names to that index search and then dive in to learn about indexes.
- Read Louis Davidson’s blog or buy one of his many books.
- If you have questions or comments, leave me a comment on this post, send me an email at [email protected], message me on Twitter or DM me on LinkedIn.