The Bit Bucket

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

Intrusions into computer systems are happening all the time now. We need to address this issue as an industry, but it’s important to understand that the way we design databases plays a big role in the impacts that occur during intrusions.

If you don’t accept that you could have an intrusion, you are living in La La Land. (See https://en.wikipedia.org/wiki/Fantasy _prone_personality)

A bug in any one of the frameworks that you use, the code that you write, the protocols that you use, the operating system or hosting services that you use can potentially expose you to an intrusion.

2017-12-19

There were a number of new T-SQL functions introduced in SQL Server 2012. As I’ve mentioned before, I get excited when there are new T-SQL functions.

Some, I’m not so excited about. EOMONTH was in that category, not because of the functionality, but because of the name (wish it was ENDOFMONTH), and lack of symmetry (lack of a STARTOFMONTH or BEGINNINGOFMONTH).

One that I thought was curious was CONCAT. I thought “why on earth do we need a function to concatenate strings. I can already do that. But when I got into using it, I realized how wonderful it was.

2017-12-18

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

SQL Server databases have a reputation for being hard to test, or at least hard to test appropriately.

For good testing, and particularly for unit tests, you really want the following:

• Database in a known state before each test
• Database containing large amounts of (preferably masked) data (production-sized)
• Quick restore after each test before the next test

For most databases, this is hard to achieve. The restore after each test means that a normal database restore can’t be used. What I often see instead, is people using transactions to try to achieve this i.e. the process becomes:

2017-12-14

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

If I want accounting advice, it’s unlikely that I’d ask my dentist for that advice.

Many years ago, I created applications for food wholesalers. When the owners of these businesses decided to get a new or better computing system, invariably they’d speak to their accountants. I understand the reasons why that might seem logical to them at first, but what I saw when these clients did this, is that they invariably ended up with the wrong systems.

2017-12-12

Just after I was born, my mother and father called me Gregory. Ever since then, everyone has called me Greg. And that included my parents. To this day, my mother calls me Greg and so did my dad while he was alive (miss you dad).

However, every time I need to fill in an official form, I have to write Gregory. I could change that to Greg if I changed my name legally but I’m not going to do that. People who have had previous names will tell you that can add even more complexity.

2017-12-11

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