The Bit Bucket

I regularly find myself writing repetitive code in T-SQL. Some things are best done by just creating code snippets but we’ve also added several others to our free SDU Tools for developers and DBAs.

One straightforward one is DropTemporaryTableIfExists.

This just wraps all that’s needed to remove a temporary table if it exists. The nice thing with this procedure is that you can call it before creating a temporary table, and call it again after you finish using the temporary table, as shown in the main image above.

2018-07-25

At the end of the month, I’ll be delivering a pre-conference session in Auckland on Friday 31st of next month (August). Would love to see you there. Details are as follows:

Developing SQL Server Applications that Perform

So many SQL Server applications today are so slow, even simple ones, yet the product is capable of amazing performance, even in tier-1 organizations. Greg spends his life working with SQL Server developers (from the smallest startup software houses to many large tier-1 organizations), working with them to get their applications flying.

2018-07-24

I often see people creating databases in SQL Server and not specifying the name of defaults they are applying to columns. They define a column like this:

And there are general reasons why this makes sense. For example, a column can only have one default, so what does the name matter anyway?

There are two reasons:

Dropping columns

In SQL Server, you’ll find that if you go to drop either of those columns, you’ll see something like this:

2018-07-23

Another book I’ve read recently while sitting on a few planes is Blockchain - by Samuel Rees.

I’ve seen some big claims in the titles of books but this one had me intrigued:

The Ultimate Beginner Through Advanced Guide on Everything You Need to Know About Investing in Blockchain, Cryptocurrencies, Bitcoin, Ethereum and the Future of Finance

That’s quite a claim. I was really hoping this book would provide a great amount of detail given it’s ‘beginner through advanced’ guide claim.

2018-07-20

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