Skip to content

2017#

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.

Data Science Azure VM

It seems Microsoft is offering a complete ready to go software bundle for any Data Scientist in the form of a Azure Virtual Machine called the “Data Science Virtual Machine“.

Just looking at the list of pre-installed software, you have some of the common modern languages and frameworks already in the VM, so all you need to do is connect to your data source and start working:

Watch out for Julia

I first heard of Julia from Kaggle’s “First Steps With Julia” competition and started looking around some of its features while on julialang.org and at that point I thought that it was maybe a replacement for R or even a more hardcore version of R developed especially for scientists.

But recently I read a blog post from Microsoft’s Cortana Intelligence and Machine Learning Blog on Julia – “Julia – A Fresh Approach to Numerical Computing” and I have to say that I feel like the tide will turn in a year or so and we will see a rapid adoption of Julia in the Machine Learning community.