I spend a lot of time reviewing SQL Server T-SQL code from developers. There are quite a few issues that I see regularly, and one of these relates to UNION.
In SQL Server, a UNION statement is actually a UNION DISTINCT (but we’re not allowed to put the word DISTINCT in the syntax). The alternative is a UNION ALL. Often a UNION ALL is what’s needed, and using a UNION leads to unnecessary performance problems.
2018-01-29
I keep doing work at sites where none of the database code is stored in version control (source control) systems. I keep wondering why that is.
At a recent site, all the source code was in individual files just sitting in a single folder. That’s just not sensible.
I’m left wondering why it is that almost every team that I see working with higher-level languages just assumes that some form of source control would be used, yet it’s almost the opposite when I’m working with data teams.
2018-01-26
It’s likely that everyone who uses SQL Server Management Studio (SSMS) knows how to tell how long a query ran for. You can see it in the bottom right of the status bar when a query finishes.
But one question that often comes up with a long-running query is when did my query finish?
That’s not in the status bar and many people don’t seem to be aware that you can find it out.
2018-01-25
Today’s post is about one of our free SDU Tools that helps you with what you’d think would be a simple task in T-SQL but isn’t.
TrimWhitespace is used to remove leading and trailing whitespace characters in T-SQL strings. As well as spaces, it will remove carriage returns, linefeeds, and tabs.
You can see its action in the image above.
You can also see it in action here:
2018-01-24
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
For many years now, it has been important to script the configuration and deployment of systems, particularly virtual machines. Infrastructure as Code is now a common requirement but as the required configuration has become more complex, scripting in language like PowerShell has become more difficult.
It’s all very well to write code to add say a network adapter, but how do you check the current state of the machine?
This leads to ever-more complex code and this is where my concerns start. Writing code for creating infrastructure needs the same discipline that writing any other code does. This includes code quality, coding conventions, error handling, source code control and versioning. Yet, who is writing this code?
2018-01-19
Two key combinations used by SQL Server T-SQL developers every day are Ctrl-C and Ctrl-V for copy and paste.
But many users of SQL Server Management Studio (SSMS) don’t realize that it has a clipboard ring and can deal with several objects in the clipboard at the same time.
Let’s see an example.
In this screen shot, I’ve opened a query window with the source code of the AnalyzeTableColumns procedure from SDU Tools.
2018-01-18
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
