Sql-Server

In our free SDU Tools for developers and DBAs, we have added many tools that help to manipulate dates.

When creating a date dimension (as part of dimensional modeling), you need to be able to get a list of all the dates between a start date and an end date. There are many other reasons why you might need to do this as well.

So we’ve added a table-valued function called DatesBetween to do just this. It takes a start date and an end date as parameters and returns all dates between. As well as the date values, it also numbers each of the dates.

2018-07-18

I spend a lot of time working in software houses. One of the nastiest things that I see again and again and again, is developers attempting to roll their own security and authentication mechanisms.

Spend a moment and think about how many security incidents the big companies (Google, Apple, Microsoft, etc.) have had over the years. Now think about how much effort they’ve put into doing it right, yet they still have issues at times.

2018-07-17

In a previous post, I wrote about how to determine if a set of incoming values for a row are different to all the existing values in the row, using T-SQL in SQL Server.

I later remembered that I’d seen a message by Adam Machanic a while back, talking about how FOR JSON PATH might be useful for this, so I did a little more playing around with it.

If you are using SQL Server 2016 or later, I suspect this is a really good option.

2018-07-16

Intellisense is one of the best things that’s ever been added to Visual Studio or to SQL Server Management Studio (SSMS). It’s hard to remember back to before it was added, or how we worked then.

I had a young friend from the United Kingdom who had just completed a Computer Science degree and one of the things that he was most proud of, is that he knew so many HTML tags and which attributes went with which tags. When I showed him HTML Intellisense in Visual Studio, I think he was about to cry.

2018-07-12

I spend a lot of time reviewing other people’s databases. Apart from looking at table designs, data types, keys, etc. I’m often interested in what the typical data in the table looks like.

In our free SDU Tools for developers and DBAs, we have added a special tool called AnalyzeTableColumns that makes this type of review easy. You point it at database, schema, and table, and it tells you about the table.

2018-07-11

There is a near-religious debate in the development and DBA communities about singular and plural table names. It’s pointless rehashing all the arguments but I want to spell out what I do, and why.

What’s driving this post is that I had a developer tell me that I was doing it inconsistently because even though I generally use plural names, that he found a table where I used a singular name. He thought this was inconsistent. It’s not, and this is why.

2018-07-10

I had a curious discussion on a mailing list the other day where the questioner was complaining that SQL Server Management Studio (SSMS) wasn’t working properly. He wanted to restore a backup, and his folders did not appear in the folder list to choose from. He could see them in Windows File Explorer but couldn’t see them when trying to restore a backup.

What he wasn’t understanding is that when you use SSMS to perform a restore, what you are doing is sending a command to SQL Server to tell it to do a restore. SQL Server is going to open the file and get the contents out of it, not via your local copy of SSMS.

2018-07-09

In our free SDU Tools for developers and DBAs, we have added many tools that help to convert data between different types and formats when working with T-SQL.

In some recent consulting work, I needed to convert data from BizTalk messages into other formats. To help make this easy, I added a function that converts from the Base64 (used by Biztalk messages and other applications) into varbinary.

Not surprisingly, we called it Base64ToVarbinary. And for completeness, we also added the reverse function: VarbinaryToBase64.

2018-07-05

I mentioned in an earlier post that I think the online version of Books Online (BOL) is now superior to the version that you can install locally.

I particularly like the way that the online books are now cross-version ie: each page covers all supported versions, instead of having a separate page for each version.

But one of the really big bonuses is that you now have the opportunity to change the documentation if you think it’s incorrect or you think it could be improved. Microsoft have placed all the documentation in a Git repository and you can change it. Doing so is easier than you might expect.

2018-07-05

If you have data in a SQL Server table and you want to know if any of the values in a row have changed, the best way to do that is by using the rowversion data type. (Note: this used to be called the timestamp data type in a rather unfortunate naming choice). I’ll talk more about it in another post.

But today I wanted to discuss the another issue. If I have an incoming row of data (let’s say @Parameter1, @Parameter2, @Parameter3, @Parameter4) and I want to know if the incoming values match the ones already in the table and update the table only if they are different, what’s the best way to do that/

2018-07-02