T-SQL Tuesday #149: T-SQL Advice you’d give your younger self

My advice to share

To be fair, when I saw the T-SQL Tuesday invitation, my first thought was, hmm I wouldn’t know what to advise. So before I wrote this blogpost I first took the time to think about the topic and dig deep into my memory, trying to remember situations that I’ve been in and where I could have used the knowledge that I currently have.

There are a couple of T-SQL related topics where I would like to give some advise to my younger self:

Limiting data

When developing new data loads don’t use too much data, it sounds like development 101 right? Though, when I started as a Business Intelligence Developer, I tended to use the following TSQL Command:

SELECT * 
FROM TABLE

Without overthinking about the amount of data that needed to be transferred over the network. By now, I finally realized that during development a limited set of data is a good starting point (limited amount of records). Additionally, make sure to limit the number of columns, only select the columns you need, and remove everything that isn’t required. By now, when starting development I typically use something like:

SELECT TOP 100 
	  t.id
	, t.column1
	, t.column2
	, t.column5
	, t.column67
FROM TABLE AS t

Data types

Over the years, I have been challenging myself to think more critically about which data type to use in which situation, keeping business growth in mind. For example VARCHAR(MAX) vs VARCHAR(100), maybe a very easy choice but when just starting I wasn’t thinking about the potential impact of these kinds of decisions. Another example can be the use of VARCHAR vs NVARCHAR.

Advice to myself, choose your data types wisely and don’t oversize. Secondly, if you need to choose between VARCHAR and NVARCHAR, only choose NVARCHAR when you absolutely need to (in case you need Unicode characters). It takes more space on disk than VARCHAR.

Working in batches

I would also advise my younger self that there are alternatives for CURSORS, using a WHILE loop might be a better solution. When using WHILE loops, just make sure to build in logic to determine when your loop needs to be terminated. Ask me why I make this remark 😀

General Querying

The last piece of advise that I would give my younger self, make sure that you keep indexing in mind when developing and writing queries. When I just started as a BI Developer, I was just writing queries without keeping indexes in mind. After finding out, I could optimize queries to work in a matter of seconds instead of minutes/hours by making minor changes to my queries.