When I first start to work with a database that I haven’t seen before, one of the things that I check immediately is the type of data being stored. The list of existing data types often gives me interesting insights into the design of the database, and how it interacts with application code.
The list of data types also shows me details of the consistency (or lack of) in the design.
2018-01-10
Is 2018 be a leap year?
Was 2000 a leap year?
Will 2100 be a leap year?
When I was a young student at school, we learned that leap years were every four years. Then as I got older, I learned that there was more to it than that.
It’s important to understand that any calendar is just an approximation, and there is a lot more to calendars than most people realize.
2018-01-03
When you first start working with SQL Server, you’re often working with smaller databases and every backup seems to happen quickly. SQL Server is fast at creating backups.
When you get to larger databases, backups start to take a while. Fortunately, backup is one of the commands that sets a percentage complete value that can be seen in the sys.dm_exec_requests system view.
And when the databases get even larger, the question quickly becomes:
2017-12-27
Over the years, I’ve had a surprising number of questions on how to right-align a set of numbers using T-SQL.
The first concept that people seem to miss is that numbers aren’t strings. So there’s no concept of actually aligning a number, it’s only a string representation of a number that can be right-aligned or left-aligned. Or how it’s displayed in a client application.
But if you really want to create a string that has right-aligned numbers, then left padding of the number is what you want.
2017-12-22
I’ve mentioned that I work on a lot of financial systems. Masking and trimming the production databases so they can be used in development is important, yet I see many issues with masking.
A few months back, I was working on a SQL Server database from a large financial client, and the database was supposed to have been masked. However, what they missed was that the XML metadata associated with images stored in the database still had the actual client’s details. Any developer in the organization could have retrieved private details of all the members of the fund.
2017-12-13
I’ve mentioned that I love it when SQL Server gets new T-SQL functionality.
A useful function that was added in SQL Server 2016 was STRING_SPLIT. You can see it in action in the main image for this post.
It’s another function that is great as far as it goes, but when I was a kid, this would be described as “nice try but no cigar”.
It works, and it’s fast, so what’s missing. I think three things:
2017-12-06
When designing databases, one question that comes up all the time is how large columns should be.
For numbers, the answer is always big enough but not too big. This week I’ve been working at a site where the client numbers were stored in int columns. Given the clients are Australians and the Australian Bureau of Statistics Population Clock says there are just under 25 million of us, an integer seems a pretty safe bet, given it can store positive numbers up over two billion. It’s hard to imagine that number being exceeded, but I’ve seen people deciding that it needs to be a bigint. I doubt that. Even if we count all our furry friends, we aren’t going to get to that requirement.
2017-11-27
I had the great pleasure to record another SQL Down Under podcast last week with the Power BI general manager Kamal Hathi.
In the show, Kamal and I discuss the current state and potential futures for Power BI, its relationship to SQL Server Reporting Services, and its development and extensibility models.
You’ll find the show here: https://sqldownunder.com/podcast
I hope you enjoy it.
Note: We had a few unexpected audio issues with the recording. Sorry about that. We’ll do better next time :-) It’s still pretty good and I’ll still think you’ll find it interesting.
2017-11-24
Many people know that I have an issue with ORMs (object relational mappers). It’s worth spending a blog post to describe why.
Unfortunately, I spend my life on the back end of trying to deal with the messes involved. The following are the key issues that I see:
Potentially horrid performance
I’ve been on the back end of this all the time. There are several reasons. One is that the frameworks generate horrid code to start with, the second is that they are typically quite resistant to improvement, the third is that they tend to encourage processing with far too much data movement.
2017-11-07
It seems like anyone that’s talked to me this year has heard me going on and on about how Power Query is just the nicest new bit of tech that’s come from Microsoft lately. We cover it as part of our one-day Power BI Core Skills training class and as part of day 2 in our BI Core Skills classes that we recently updated for SQL Server 2014. Out of all the technologies that people see during that week, Power Query is the one that seems to promote the biggest discussions.
2014-06-28
