Sql-Server

The status bar at the bottom of a query window in SQL Server Management Studio contains a wealth of information in its default configuration.

The bottom left shows the connection state:

I’ll talk about connection management more in a later post. The bottom right also shows quite a bit:

In this case, it’s showing me that I’m connected to a server called SDUPROD; it’s running v14 of SQL Server (ie: SQL Server 2017); I’m logged on as GREGP50\Greg; my spid (or session ID) is 68; and I’m connected to the master database.

2018-02-22

I’m not a great fan of dynamic SQL (where you create a T-SQL command in code, then execute the command) but there are times when you just need to do it.

When creating dynamic SQL, it can get very painful trying to work out which strings are quoted and how many quotes you need.

To make this a bit easier, in our free SDU Tools, we added a function for QuoteString.

2018-02-21

A current trend that I can’t say that I love is constant churn within software applications. I have no interest to go back to the days where we got a new version of SQL Server or Power BI, etc. every few years.

It’s also not a case of who moved my cheese?

In fact, I thrive on change. However, I’ve now become really concerned about how anyone:

• Learns to use a complex application
• Remembers how to use a complex application when they don’t use it daily

I first really struck this issue with Azure. If I was teaching a class that used Azure, I could check every single lab on Sunday night, then Monday morning, the students would find it had all changed. That’s OK for an experienced person, but not OK for a learner.

2018-02-20

I’ve been very lucky over the years because I haven’t needed to wear glasses. Every now and then I’ve got some because I thought it might help with reading but then I find them more inconvenient than helpful. I’ve had one eye long-sighted and the other short-sighted. That’s been a really useful thing in day to day life.

However, where this comes unstuck is on modern laptops. There seems to be a current trend to pushing more and more pixels into the same size laptop screens but the applications aren’t helping to deal with that.

2018-02-15

Prior to SQL Server 2012: IDENTITY Columns

When we needed to automatically number rows in SQL Server prior to SQL Server 2012, one option was to use IDENTITY columns.

(Apologies to purists who think these were the spawn of the devil but real people did use them).

One of the challenges with IDENTITY columns was when you wanted to move data from table to table but needed to retain the same value from the IDENTITY column. To do that, we used SET IDENTITY_INSERT tablename ON; That was fine but an issue that arises, is that if the next IDENTITY value was going to be 245001 and you just inserted a row with 245001, what would happen with you turned IDENTITY_INSERT back off and tried to insert another row.

2018-02-14

I’ve concluded that many software vendors (particularly large ones) don’t understand how much support users of their software provide to each other, and how critical that support is.

The SQL and data communities are a good example of this. When someone has a problem and are wondering how to solve it, they don’t call Microsoft or Google or Oracle (or whichever vendor) first. If they’re lucky, they ask a colleague for help. But most will simply make a Google search (or yes a Bing search) to try to find an answer.

2018-02-13

I spend a lot of time in large organizations that have spent an absolute fortune on highly-available systems, yet when those systems fail over (just as they were designed to do), most of the applications in the building break.

Why?

Because the developers have assumed that nothing ever breaks and have written their code in a far too optimistic manner. Did they do their jobs?

No

Is it possible for their next layer of code to deal with, say, a server disappearing for a few seconds? Of course it is. But it’s not going to happen by accident. It’s even more important in a cloud-based world.

2018-02-09

One of the problems with applications that have highly-configurable user interfaces (UI) is that users can end up configuring them in ways they hadn’t intended, and then don’t know how to get back to where they were.

I remember the first time that I was at a session with a presenter from Microsoft showing the (at the time) new personalization options in ASP.NET. You could build a website and let the user determine how the site should be laid out, to suit themselves.

2018-02-08

One of the endless challenges when working with databases (and not just SQL Server databases) is processing dates and times.

While there are some good date-handling functions in the T-SQL language, there are many more gaps in what’s offered. This month, we’ve filled another one of those gaps.

In Australia, whether staff are religious or not, the Christian Easter is associated with a four-day weekend for most workers except those in retail ie: Friday (Good Friday), Saturday (Holy Saturday), Sunday (Easter Sunday), and Monday (Easter Monday). For many, it looks like this:

2018-02-07

Digital Transformation and Cloud Transformation are phrases that I hear bandied around at nearly every large organization that I currently doing consulting work for.

Yet, in so many cases, I can’t see the organization achieving the changes required. This is for two core reasons:

• The first is that the culture within the organizations is a major hurdle. There just isn’t enough flexibility to think outside the box about alternative ways to work.
• Worse (and probably more concerning), I see these companies taking advice on how to make these transformations from companies who don’t themselves “get it”.

An organization that is cloud-antagonistic internally, and stuck in an endless IT management quagmire, isn’t likely to make a good cloud transformation, and they’re certainly not going to be a successful partner to be able to help you to make a successful cloud migration or to implement a cloud transformation within your company.

2018-02-06