Sql-Server

I’ve pointed out in previous blog posts that I’m not a fan of ORMs. What I’m even less of a fan of is code-first design, particularly in combination with an ORM like the Entity Framework.

It might seem cool and shiny and if you are just whipping up a small proof of concept or test app, please feel free to do it, but the minute you think of doing it for enterprise systems, just don’t. And if you have colleagues wanting to do this, please just say no.

2017-12-05

Here’s a quick T-SQL test for you.

Without looking below to see the answer first, try to guess what each of these statements will produce as output:

And to slightly distract you from checking out the answers yet, here is another wise-looking owl who is thinking about the answers, and warning you not to look further down the page yet:

Anyway, here’s what happens when you run this T-SQL in SQL Server:

2017-12-04

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 320 characters anyway).

2017-11-29

Many years back, I was invited to chair a course accreditation panel for a local TAFE (Technical and Further Education) course. They had started to offer a computing-related 3 year diploma, and the hope was that it wasn’t too far below the 3 year degrees offered at local universities. One part of that accreditation process involved me discussing the course with the staff members who were teaching it.

After talking to almost all the staff, what struck me was how similar they all were. In the requirements for the course, there was a standard that each staff member needed to meet, but there was also a requirement for the group of staff to be diverse enough to have broad knowledge of the industry. There was no individual staff member that you could identify as not being at the appropriate standard, but almost all of them had exactly the same background, career progression, etc.

2017-11-28

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

I had the great pleasure to record another SQL Down Under podcast last week with the Power BI general manager Kamal Hathi.

In the show, Kamal and I discuss the current state and potential futures for Power BI, its relationship to SQL Server Reporting Services, and its development and extensibility models.

You’ll find the show here: https://podcast.sqldownunder.com

I hope you enjoy it.

Note: We had a few unexpected audio issues with the recording. Sorry about that. We’ll do better next time :-) It’s still pretty good and I’ll still think you’ll find it interesting.

2017-11-24

Everyone working with databases knows that having too many indexes can be a problem. Indexes need to be modified whenever the data in the associated tables need to be modified, so yes it can slow down data inserts, updates, and deletes.

How big a problem is too many indexes?

Overall, I’m not as concerned about this as many other people seem to be. I always want to focus on what the system is spending its time doing, and on most systems that I work on, that’s reading, not writing.

2017-11-24

I often see people using SQL Server Management Studio (SSMS) and doing very repetitive editing tasks that could easily be carried out by using the selection and changing of rectangular regions of code.

The simplest example of doing this is to insert a bit of text on a number of rows. Take the following code as an example:

I’ve got the skeleton of a list of columns in a CREATE TABLE statement but let’s assume that I’m a “comma in front” person and want to put a few spaces and a comma, etc. in front of each column after the second. I could just put the cursor on the InvoiceID line and type what I wanted, then do the same again on the next line. I could do it on the first line, then select and copy it, and insert it into the front of every other line. But what I should do is put the cursor in front of InvoiceID, and while holding Alt-shift, use the down arrow to select the beginning of every line, then just type what I want.

2017-11-23