The Bit Bucket

For some years now, SQL Server Management Studio (SSMS) has had the ability to use code outlining, the same way that other Visual Studio applications can.

This can be very useful when you are trying to navigate around a large script file.

The simplest usage is to collapse or expand a region of code. Note that in the following script, code regions have been automatically added by SSMS:

This allows us to click on the outline handles, and collapse the code:

2018-07-19

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

One book that a number of my friends suggested that I read is:

Now I Know: The Revealing Stories Behind the World’s Most Interesting Facts - by Dan Lewis

I wasn’t sure what to make of this book as it just seemed to be a large collection of facts that Dan thought were interesting. Given I love trivia, I thought I’d try it.

Dan started an email list called “Now I know” back in 2010 with a handful of subscribers and grew that to over a hundred thousand. These are basically the topics that ended up being some of the most interesting.

2018-07-13

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

I have to admit to being a bit of a fan of Neil DeGrasse Tyson, so I was really looking forward to reading or listening (via Audible) to his book Astrophysics for People in  Hurry.

It’s always a bit of a tall order to try to cover something like Astrophysics in a short book. The title reminded of silly book titles like “Applied Multivariate Analysis and Calculus for non-Mathematicians”.

But I loved this book.

2018-07-06