// T-SQL Tuesday 158

This months T-SQL Tuesday is number 158 (wow, tempo fugit) and are hosted by Raul Gonzalez - you can read the original invitation here or by clicking the image below.

The topic of this month is if there exists a “worst practice” that actually has some use case in the real world.

Interesting topic indeed - and I’ll have the following to bring to the table.

Cursors for bulk loading data 😱

Now this should get some of you up from the chair. The worst practice in any data warehouse is to use a cursor for loading data to the dwh staging area (or any other area for that matter).

But - I’ve done it once - in a very special setup.

The situation

The client had an existing data warehouse that worked as it should and providing the business with the insights they needed. But, the client approached me with a new task:

We have some handheld scanners that we use to track the employees whereabouts, and we need that data in the data warehouse before it ends in the OLTP system for tracking. Can you help us?

The details are that they had a fairly large group of guards that had to scan checkpoint QR codes to show that they had been at their checkpoints during the day. These scanners needed live reporting (this was before GPS was low priced) in order to be able to find all personal quick and easy. The OLTP system could not handle the live reporting at this point.

There was a hell of alot business logic to be implemented to the scanning events - if this has happened at one scan, then wait for the next scan and check for this. Or check if this has happened before if this is the current event.

The implementation

The scanners was uploading data through a SIM card base in the cars of the guards. This data then ended up in a CSV file to be handled upon arrival.

Here we ended up using cursors to read the data.

With the demand from the business logic to look both forward and backwards in the records, we used a cursor to, row-by-row, read the lines of scanning and “react” to the events.

The data was then plotted in a SSRS report (yes this is old) with shape vectors drawing a map of the city/area and dots for each checkpoint and each guard.

This was done before the newest window functions in T-SQL was implemented (like lag and lead and the partition over with rows between unbound precedding etc.)

Given todays technology and T-SQL language

If I was to do this again today, I would use the before mentioned T-SQL functionalities to do the business logic in a bulk operation, instead of row-by-row.

And I’m really sure that this is also a capability in Power BI with the new windows functions in DAX. But that is a completely different blogpost.

☕