I’m really happy with the additions we made to our free SDU Tools for developers and DBAs that are going out tonight:
DatesBetween - table-valued function to return dates between two dates DateDimensionColumns - table-valued function to return typical date dimension columns for a date (useful for data warehouses)
JulianDayNumberToDate - converts a Julian day number to a date DateToJulianDayNumber - converts a date to a Julian day number
2018-02-28
If you’ve ever used SQL Server Reporting Services, you’ll notice that when you drag a database column into a table, it auto-magically converts the name of the column into a more English-readable name.
Image from MSDN
Notice how when the LineTotal column has been dragged into the table, the heading has been set to Line Total with a space. What it is doing is taking a Pascal-cased or camel-cased name and separating the words with spaces, based upon where the capital letters are.
2018-02-28
In an earlier post, I described the difference between state-based deployments and migration-based deployments. What I want to talk about today are two of the main issues that can arise in a migration-based deployment and what to do about one of them.
When you are using a migration-based deployment technique, you are storing all the scripts that take a database from its current state to the desired final state. While tools like Ready Roll make this is a relatively easy way to perform migrations, it suffers from a few key problems:
2018-02-23
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:
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
