The Bit Bucket

Choosing the right database permission can be hard. I’ve lost count of the number of times I’ve heard a discussion like this:

I need to let Mary restore truncate one of the tables but I don’t want to give her permission to do it, in case she stuffs it up.

or

I need to let Paul restore this database but I don’t want him to be able to restore other databases, and I’m worried if I give him the permission, he might accidentally do something bad and I’ll be blamed for it.

2018-08-06

Hi Folks, we’re looking forward to doing a number of presentations across New Zealand starting around the end of this month.

Aug 27th (Mon): SQL Server User Group - Wellington (Things I wish Developers knew about SQL Server) Details here

Aug 28th (Tue): Data Management and Analytics Meetup - Wellington (A Comprehensive Look at What’s New in SQL Server 2017 - and ongoing product directions) Details here

Aug 31st (Fri): Pre-conference Day  for SQL Saturday - Auckland (Developing SQL Server Applications that Perform) Register here

2018-08-04

I don’t tend to read all that many books on US politics but I had heard interesting things about A Higher Loyalty - Truth, Lies, and Leadership by James Comey.

I was especially interested to hear this one on Audible, given the author was also the narrator. I really wanted to hear him explain his view on the situation.

Generally, I’d avoid a book like this because I was assuming it would just be a self-apologetic or self-aggrandizing account of recent events. What I found though was very, very different.

2018-08-03

In previous posts, I’ve been talking about how to use snippets in SQL Server Management Studio (SSMS) and how to create your own. There are several types of snippets and one of the special types of snippets that I want to mention are the “surround with” snippets.

If you look at the following block of code:

Imagine that you want to execute the four highlighted lines only when a condition is true. If I hit Ctrl-K and Ctrl-S while they are highlighted, I’m prompted with this:

2018-08-02

You can execute SQL Server Agent jobs from within T-SQL code. The procedure sp_start_job can do that.

That’s all well and good but notice that it doesn’t say “execute job”; it just says “start job”. The command starts the execution of a job but has no interest in when it completes.

Sometimes, you need to be able to start a job and wait for it to complete before taking a following action.

2018-08-01

Just a reminder that as well as the pre-conference day and the keynote, I’ll be presenting my Database on a Diet session at SQL Saturday in Auckland. I regularly work with some pretty sizable databases at tier-one financial (and other) organisations and they all struggle with the size of their databases. It’s easy for the size of a large database to get out of control.

In the example in this session, I’ll  describe the techniques I’ve used to reduce an operating 3.6TB database to just over 500GB, while increasing its performance. The session will include a deep dive into compression and other related techniques within SQL Server.

2018-07-31

Anyone who’s ever traced activity against a SQL Server will have no doubt seen a large number of commands where the procedure sp_reset_connection has been executed. Yet, this command won’t appear anywhere in the source code of the applications that are running.

As an example of why this occurs, one of the most common data access technologies that is used to connect applications to SQL Server is ADO.NET. It has a SqlConnection object that represents a connection that can be opened to a SQL Server instance. In the design of the SqlConnection class, the architects of it were grappling with two big issues:

2018-07-30

When I first heard that Satya Nadella had a book out, I was somewhat surprised as at the time, he had just taken over running Microsoft. Usually you don’t see books from CEOs until they’ve been in the role for quite a while and have become philosophical about things.

But given the impact I could see he would have, I was fascinated to read his book Hit Refresh.

It was actually quite a bit more than I expected. I really enjoyed the tales of his life and how it led up to his current role.

2018-07-27

In a previous post, I was discussing how outlining can be helpful with navigating around within a large T-SQL script file.

If you were trying to do that within a Microsoft Word document, the most common thing to use is bookmarks, and SQL Server Management Studio (SSMS) has them as well.

Bookmarks are simply placeholders within a script. (They can also apply to other types of document within SSMS). Where I find them very useful is when I’m working in two or three places within a long script at the same time. Perhaps I’m working on a function, and also on the code that calls the function. By using bookmarks, I’m not flipping endlessly around the script file, and can jump directly from placeholder to placeholder.

2018-07-26

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