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
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
In a recent blog post, I described converting strings to Proper Case and Title Case. And more recently, I described how to convert them to Pascal Case and Camel Case.
The final option (for now) in this set is conversion to Snake Case and Kebab Case.
In Snake Case, all words are lower-cased, then all spaces between words are replaced by underscores.
You can see it in this example:
2017-12-29
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 written before about how important it is to find code smells.
One of these relates to foreign keys. I’ve argued in previous posts about the importance of foreign keys but if you do have them in place, you need to index them.
When SQL Server creates a primary key, it creates an index under the covers to support the primary key. The index has the same name as the key. (And is one of the reasons why you should name your primary keys and not let the system do it for you). A primary key needs to be both unique and NOT NULL. So SQL Server creates an index so it can quickly check if a value already exists.
2017-12-20
I’ve often had requests for code to convert strings to ProperCase and I described that in a recent blog post, but another common request is for either PascalCase or camelCase.
In PascalCase, all spaces between words are removed, the first letter of each word is capitalized, and the other letters in each word are lower case.
You can see it in this example:
camelCase is similar except that the first letter of the first word is not capitalized. The first letter of subsequent words are capitalized:
2017-12-15
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 recently wrote about the tool we provide for splitting T-SQL delimited strings (like CSVs or comma-delimited strings). It outputs a row for every value.
Sometimes, however, I’m asked how I can get each value out into a separate column. Now that’s a little trickier without dynamic code because SQL Server wants to know the output metadata of the statement. SQL Server wants to know in advance what the output columns are called and what their data types are. This is especially important if you want to consume the output of the function in tools like SQL Server Integration Services (SSIS) or Biztalk.
2017-12-08
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
