If I query rows from a SQL Server table, I don’t always want all the rows returned. In particular, I might not want any duplicates. I can remove them by using DISTINCT.
Here’s a simple example. I want a list of the sizes that products can come in, so I execute this query:
Note that although I get a list of sizes, I get a row returned for every row in the table. If I add DISTINCT to the query, look at the effect that it has:
2019-05-20
I spend a lot of time reviewing database designs at customer sites. So often, I come across bizarre decisions that have been taken in the choice of data types within the databases. One problem that I see all the time is the way that email addresses have been stored.
The first thing you need to decide is whether an email address is really one data element or more.
2019-05-17
This is part 5 in a series of posts:
Today, though, I want to look at how applications should deal with deadlocks.
In the last post, I talked about what deadlocks are (under the covers) and how SQL Server “resolves” them.
2019-05-16
I carry out a lot of basic health checks on SQL Server databases. One issue that I come across fairly regularly is that the databases contain indexes that have been disabled.
Most of the time, when I investigate why that’s happened, the customer has used some sort of ETL process and has disabled the indexes to improve loading speed. But then something went wrong, and the indexes didn’t get rebuilt (i.e. re-enabled).
2019-05-15
I’ve lost count of the number of computer systems that I’ve worked with over the years, that insist that everyone has at least two names. Yet there are many people in the community who only have a single name. I’m not just talking about people like Cher who might well have a stage name but then have another name on her passport. I’m talking about people who really only have one name.
2019-05-14
When I need to display a potentially large number of rows on a screen, I often need to show them in pages. This is called paginating the rows.
For example, if I’m showing bank transactions, I might want to show 25 per page. The challenge is that I also need to be able to ask for a specific page number. So my query becomes: give me page 7 where there are 25 rows per page. In that case, I want to skip the first 150 rows, then ask for the next 25 rows.
2019-05-13
One of my Kiwi buddies who specializes in Power BI is Reza Rad. I was pleased to see he had a set of eBooks now on Power BI but was especially happy to see he had a book called Pro Power BI Architecture.
There are lots of books around to discuss how to use Power BI but there’s been a real lack of books on architecting solutions using Power BI. So if you want to learn to develop dashboards or reports, this isn’t the book for you. Reza has other books for that.
2019-05-10
This is part 4 in a series of posts:
Today, though, I want to look at the next potential issue, and that’s deadlocks.
I often hear people interchangeably using the words lock, block, and deadlock.
Locks are a standard feature of systems that are designed for concurrency. Nothing nasty to see here. When they become a problem is in two situations:
2019-05-09
A while back I wanted to do some testing of Azure Database for PostgreSQL and I needed a database to work with. So I thought I’d try to migrate a basic WideWorldImporters database across to PostgreSQL.
I wrote scripts to do the main migration but then decided that I wanted to change the names of the columns. While a large number of people use PascalCased names in SQL Server, it’s really, really common to use snake_cased names in PostgreSQL. Object names in PostgreSQL are also limited to 63 characters. I needed to alter these names while generating the scripts and I didn’t want to do that manually.
2019-05-08
I was part of an interesting email chain today. It started with a guy complaining that in SQL Server Management Studio 18x, the database diagram tool had been removed.
Now I was disappointed to see the tool gone. Mind you, I didn’t ever think it was a great diagramming tool. I would have hoped they might have replaced it by a better one instead of just removing it.
2019-05-07
