The Bit Bucket

When I’m running SQL Server Advanced T-SQL classes, we spend time discussing cursors. The general message is that most of the time, but not always, cursors are the wrong answer. The problem with cursors is that instead of telling SQL Server what you want it to work out (ie: declarative query), you are telling it how to work that out (ie: procedural query).

You’ll hear people say to never use cursors. This is also incorrect. There are occasions where I do use cursors. Mostly this is when I’m building utilities that do things like scripting objects in the database. I’d almost never use a cursor though, in general data processing running against SQL Server. You don’t want to be doing row by row processing against the server.

2018-12-12

I used to do a lot of work at the operating system and network level. I was always fascinated watching people use network trace tools when they were trying to debug a problem. The challenge was that they had no idea what was normal activity on the network, and what wasn’t.

The end result of this is that they’d then spend huge amounts of time chasing down what were really just red herrings.

2018-12-11

Old PC style databases and tools (like Access and DBase) and most 4GL databases tended to use the words Record and Field to describe the layout of tables. Relational SQL databases use the terms Row and Column. I endlessly hear discussions about if there’s a difference, and where that lies.

Some will argue, for example, that a field is a data value within a single record or row. That would make a field more like a cell in Excel, than a column in Excel.

2018-12-10

I’m a fan of Pat Flynn. If you haven’t listened to his Smart Passive Income podcast, and you have any interest in being self-sufficient without “working for the man”, Pat’s podcast would be a good start. Pat has people ask him about ideas though and he’s put his ideas on how to work out if an idea is worth pursuing in his book: Will It Fly? How to Test Your Next Business Idea So You Don’t Waste Your Time and Money.

2018-12-07

Well today WordPress on my blog site went up to version 5.0. I knew a new editor (Gutenberg) had been coming to replace the classic editor but I hadn’t had time to try it. So when it did the upgrade, I happily let it go and install the new editor.

Then I tried to write a post.

Oh my goodness, that was just a horrid, horrid experience. I can see what they’ve tried to do but it literally took me about five times longer than normal to write a single post.

2018-12-07

One of the advantages of SQL Server Management Studio (SSMS) is that it can be used to analyze queries, not just to execute them.

There are two basic types of query plan: estimated execution plans, and actual execution plans.

For a typical query, I can obtain the estimated execution plan, by hitting Ctrl-L, choosing the option in the Query menu, or clicking on the toolbar icon:

Let’s do this for the following query:

2018-12-06

I’m often reviewing existing databases and one of the first things I go looking for is the way they’ve used data types. In particular, I’m keen to know if they’ve used any deprecated data types (ie: ones that will/might be removed at some point).  In our free SDU Tools for developers and DBAs,  we added a procedure ListUseOfDeprecatedDataTypes to do just that, and to provide their details in a form that’s easy to consume programmatically if you need that.

2018-12-05

I’ve been in the IT industry a long, long time. One thing that I’ve never liked is case sensitivity in application development tools or in database languages.  And it’s creeping into more and more places.

I know that will offend some people but hear me out.

I think we’re stuck with case sensitivity in languages like C, C#, C++, Java, etc. because that was the easiest way to implement those languages in the first place. As soon as you decide that a language is case insensitive, you also have to decide the internal collation rules. For example, is the letter A the same as the letter a ? But then what about the letter á ?

2018-12-04

If I create a primary key (let’s say a CustomerID in a Customers table) in SQL Server, an index is automatically created to support that key. Primary keys must be unique and not null. SQL Server uses that index to ensure that the key is unique. Keep in mind that when I say “key”, I’m referring to one or more columns, not necessarily just one.

The same happens for unique keys. Again, it makes it easy for SQL Server to ensure the uniqueness.

2018-12-03

When I was growing up, we were endlessly shown Mother Teresa (now known as Saint Teresa of Calcutta by Catholics) as an example of a person who had devoted their life to the service of others, and did so in appalling situations. I knew that the myth surrounding this woman was very different to the reality and I’m surprised that I hadn’t previously read Christopher Hitchen’s book: Book Review: The Missionary Position: Mother Teresa in Theory and Practice.

2018-11-30