Posts

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’ve known Bob Ward for almost as long as I’ve been involved with SQL Server. (I first started in 1992). Bob is one of the constants in the SQL Server team, is deeply technical, and his continuing passion for the product is apparent.

I’ve been wanting to get Bob onto  a podcast for a long time and we finally managed to do so today.

Bob is currently a principal architect with the SQL Server engineering team, focused on architecture, customer success, and technical evangelism of SQL Server.

2017-12-09

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

A week or so ago, I wrote about how to use Snippets in SQL Server Management Studio (SSMS) when you can’t remember the syntax of how to create objects in T-SQL.

Snippets were added reasonably recently compared to templates, which are more appropriate when you are creating an entire script file for a new object. They are easy to use too yet because they don’t appear on the screen by default, many people don’t even realize that they are there. Let’s take a look.

2017-12-07

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 writing emails or online text, it’s become common to see UPPER CASE TEXT AS A FORM OF SHOUTING.

Yet I see IT systems every day that still have text that humans are meant to read in all upper-case or all lower-case. Please don’t do that to your users.

But how do you fix text like that? We have a tool for that.

The SDU Tools collection has a ProperCase function and a TitleCase function that you can use in T-SQL and stop your SQL Server based applications from shouting at your users.

2017-12-01

There are so many useful things in SQL Server Management Studio (SSMS) and daily, I come across users who aren’t aware of common useful functions.

A request that I hear all the time, is “I don’t like tabs but insert name of annoying colleague here decided he likes to use them. How do I remove them?” Now SSMS allows you to choose to use spaces instead of tabs (in Tools > Options) but that doesn’t fix existing tabs.

2017-11-30

I might be somewhat anal when it comes to database design. (Probably almost any type of coding). Perhaps it’s a mild form of  OCPD-behavior , but inconsistency frustrates me. (I’m told that OCPD is the correct term, and that people often apply OCD to that behavior incorrectly).

Worse, inconsistency leads to nasty, hard-to-find errors and your applications become more and more fragile.

If I’m holding an email address and I define it as 70 characters in one place, 100 in another, and 160 in yet another, I have the chance of random failures when I’m moving data around or assigning values from place to place. (For email addresses, I recently discussed why they should be 255 characters anyway).

2017-11-29

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