For this months T-SQL Tuesday contribution I want to share best T-SQL advice I would give younger self.
This months T-SQL Tuesday is hosted by Camila Henrique. Camila invites us to share advice about T-SQL (aka Transact-SQL) we would give our younger selves.
You can read the original invitation by clicking on this link about the T-SQL Tuesday 149 invitation or on the image below.
When I first read this invitation, I had so many thoughts in my head. For example, advice about SQL Server internals. Which I have done a lot of work with over the years.
In reality, the list is large. However, there is one piece of T-SQL advice I could give my younger self which definitely can help others. Which is simply the below.
When doing inserts, updates or delete statements using T-SQL always, and I mean always, run them inside a transaction.
You can do this in different ways in SQL Server. One of the most popular ways is to start an explicit transaction by using the begin transaction command. From there you can either commit the transaction if it is good or issue a rollback command to undo the transaction.
In reality, a lot of us who have worked with SQL Server for a long time have a story relating to an issue caused by a rogue update or delete statement. For example, an issue caused by a where clause not included when an update or delete statement was issued. I know because I am one of those with a story.
With this in mind, I urge everybody to use transactions. It takes little effort to do this and prevent issues. Due to the fact that it is a lot easier to issue a rollback if there is a problem than it is to scour around for another fix.
It also helps reduce the number of potential SQL Server issues you have to deal with. Like the zombie spid one I posted about before.
On a side note, you can use transactions with Azure Synapse Analytics SQL Pools as well. You can read more about that in detail in a Microsoft document called ‘Transactions (Azure Synapse Analytics)‘.
Final words about Best T-SQL advice I would give younger self
I hope that the best T-SQL advice I would give my younger self also helps prevent future issues for some of you. Apologies to anybody who this brings back bad memories for because I know a lot of others in the community have stories relating to this.
Of course, if you have any comments or queries about this post feel free to reach out to me.
[…] Kevin Chant has good advice: […]