The Bit Bucket

Do you (or someone you know) need to learn to write T-SQL properly? Or perhaps you need to be more confident with joins, aggregations, CTEs, and more?

Our latest online on-demand course Writing Queries for SQL Server is now available, and at introductory pricing of just $95 USD.

It has detailed and comprehensive intro level coverage of the T-SQL language, and follows professional coding standards throughout. It’s also not just a set of videos; it includes extensive hands-on labs and quizzes to make sure the learning is great.

2019-01-15

Back to the transaction table

Two weeks ago, I wrote about the issues with a large transaction table where only a handful of the rows were unfinalized, and that we would never use an index to find all the rows that were finalized. But we’d certainly want an index defined for the ones that weren’t. If you haven’t read that post, I’d suggest you do so before continuing to read. You’ll find it here.

2019-01-14

I mentioned in a previous post about how one of my colleagues Orin Thomas is a prolific reader and every time I talk to him, he suggests more books that I should read. Another one in that category was No Ordinary Disruption One by Richard Dobbs, James Manyika, and Jonathan Woetzel. The sub-title is The four forces breaking all the trends.

The authors claim that our intuition on how the world works could be very wrong. I suspect that the degree of change in the accuracy of our perception is a recent, and accelerating thing. In the book, they are summarizing years of research they have done at the McKinsey Global Institute.

2019-01-11

When you get to a large number of query windows or other documents open as tabs in SQL Server Management Studio (SSMS), it can start to be difficult to keep track of them, and to find them when needed.

It’s not too bad when you can immediately find the tab that you want in the drop-down list:

But if you have more tabs than are shown in this drop-down list or if, like me, you often end up with many of them without names (as they are temporary), it can get very hard to find the few that you are mainly referring to.

2019-01-10

In our free SDU Tools for developers and DBAs, there’s another useful scripting tool. It scripts out the server permissions that have been granted to logins. It’s called ScriptServerPermissions.

One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.

But if you just want to create a script, that works too.

By default, it lists permissions for all logins, but you can also provide a list of the logins that you’re interested in, as a comma-delimited list, to the @LoginsToScript parameter.

2019-01-09

I don’t know if it’s considered some sort of modern trend, but what is it with applications now that just swallow errors instead of dealing with them? Is there an edict within these companies that errors should get shown, so they can argue their app doesn’t have errors?

I’m working with a SaaS app right now. It does editing. Sometimes when I save, it just doesn’t save. No error, just nothing saved. Or every now and then, I find the order of what I’ve entered just gets changed. Again, no error, but the order was changed.

2019-01-08

Clustered indexes vs heaps

OK so you know that a table can either be a heap or it can have a clustered index.  The first question of course, is which should I use?

If you have any doubt at all, put a clustered index on the table. Most SQL server tables will end up performing better that way. There are a few scenarios like log files where heaps will be preferable but if you’re not sure, start with a clustered index.

2019-01-07

I’ve also tackled a number of non-technical and non-scientific books recently. On that I’d heard good things about was Shrill (Notes from a loud woman) by Lindy West.

First note about this book is that it’s full of NSFW (not suitable for work) words, so be warned if that would offend you.

As well as being loud, Lindy has spent her life being big. I’d say that’s shaped her world view as much as anything else. Having been big much of my own life, I can directly relate to so many things she says.

2019-01-04

SQL Server execution plans can become quite large. That makes them hard to navigate because you are endlessly scrolling around the results pane in SQL Server Management Studio (SSMS).

The pane does have some zoom features. Note that if I right-click in the whitespace, I get these options:

So I can zoom in and out, set a custom zoom level, or zoom until the entire plan fits. Generally though, that would make the plan too small to read, as soon as you have a complicated plan.

2019-01-03

In our free SDU Tools for developers and DBAs, there’s a tool that makes it easy to create a script for creating SQL Server database users.  No surprise, it’s called ScriptDatabaseUsers.

One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.

But if you just want to create a script, that works too.

2019-01-02