Skip to content

SQL Server#

No errors raised for overflowing data when using CHAR and VARCHAR data types in SQL Server

It’s not always possible to predict when problems will occur with your database’s architecture and it’s always a shock when it does, especially because most of the unexpected problems occur after a deploy to production (in this scenario I’ve inherited the database, I wasn’t the one to design it, or at least not this failing part).

The latest problem I’ve faced was with data which was 9 characters long attempted to be stored into a CHAR(8) column. Have a look and test the below script to see what happens:

Finding locked tables in SQL Server

Sometime this week I executed a DELETE query with an explicit BEGIN TRANSACTION on our DEV environment, the moved to another session to check some other results and forgot the transaction hanging. A few minutes later I left for home and by the time I was close to home a developer called me on my personal phone.

He was very stressed saying that he can’t do a SELECT TOP 1 * FROM OurBigTable and what can he do about it? It was at that point that I figured that I forgot the transaction open, without any COMMIT or ROLLBACK executed.

Split string in SQL Server with Recursive CTE

I decided to spend my time productively while I was monitoring a database restore, so I wanted to see if I could create a recursive CTE that could do a split string in SQL Server into words (or tokens), based on a certain delimiter.

And so I built the recursive CTE you can see next. Because I’m not 100% sure if the order of the words will be consistent throughout multiple executions I also added a “LVL” column to enforce the order of the rows, so that you can be 100% certain that the way the result is displayed is the actual order of the words in the phrase.

How to find queries while tracing in SQL Server

Let’s say you’re working on a DEV database and you’re trying to get some information from SQL Server Profiler about the number of READS or WRITES that your queries are making and / or CPU time spent or other metrics. On a database with a lot of users running queries on the DB at the same time this can be difficult. But luckily there’s an easy fix to find those queries.

How to use a SQL trigger for the wrong reason

A couple of days ago I came onto a question on Stackoverflow asking how to create a trigger to impose constraints. The question asked about a SQL trigger where the OP (original poster) asked for, and I quote:

” I understand the idea of triggers but a bit confused on how to use it to impose constraints.

For example I have two tables: a student table and book_order table that shows what book a student orders. I want to create a trigger that will check that a student with a book order cannot be deleted from the student table.”