Case sensitivity in comparisons is an aspect of computing that I’m surprised is still so widespread. I can’t say it more clearly than this:
I’ve recently been working at a site where a new case-sensitive SQL Server system is being implemented. I cannot begin to describe what a poor idea I think this is.
In the end, all that a case sensitive system allows you to do is:
2018-01-23
In short: YES
(In contradiction to Betteridge’s Law of Headlines)
Every now and then, customers ask me if they really need to run DBCC CHECKDB. There was even a question that came up about this on a private mailing list full of people who really should already understand why. There is no mystery here.
DBCC CHECKDB checks the database for physical readability (are the pages intact and can they be read from the I/O system). This makes people wonder that if the I/O subsystem is already doing this, why does SQL Server need to do this?
2018-01-22
Ever since I started working with SQL Server back in 1992, the #1 requested feature that I kept hearing about was a separate date data type. SQL Server had a smalldatetime and a datetime but these included both date and time within the same type.
Having date and time often led to odd bugs or performance issues, where people didn’t realize that times were included and tried to work with the values as dates.
2018-01-17
Many database developers (and other developers) seem to regard the endless use of abbreviations as some badge of honor. Don’t be one of these people.
Avoid abbreviations almost all the time.
I’ve written before about my dislike for the EOMONTH T-SQL statement. Given the same version introduced names like DATETIMEOFFSETFROMPARTS, surely we didn’t have to save 3 characters and could have had ENDOFMONTH. (I heard it was named this way to match the Excel function but matching something from another language that was created a long time ago isn’t the right answer here).
2018-01-16
When I’m looking at a database schema for the first time, there are a number of tell-tale signs that give me the hint that the developers really haven’t done much work with SQL Server before. They’ve made a “newbie” mistake.
One of those is the extensive use of the float data type.
Most times that I see this, the developers have come from a C or Java background and they assume that something that needs a decimal point in it, needs to be float. There are some situations where float makes sense, but 99% of the time what they should have used was decimal.
2018-01-15
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
