The Bit Bucket

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

Another simple tip for today. In SQL Server Management Studio (SSMS), query results are normally shown at the bottom of the query window.

This can greatly reduce the screen real estate both for the query, and for viewing the results.

In Tools, Options, Query Results, SQL Server, Results to Grid, there is an option to Display results in a separate tab. This can be very useful and generally you will also want to choose the extra option to Switch to results tab after the query executes.

2018-11-29

Ever since we’ve been shipping our free SDU Tools for developers and DBAs, we’ve been getting interesting requests from users for additions to the tools. Today’s tool is one of the stranger requests but for the odd situation where you need it, I’m sure we will have saved you a lot of work. That tool is NumberToRomanNumerals.

The person who requested it was outputting values that need to go into media trailers, pretty much as you see Roman numerals in the tail end of many movies.

2018-11-28

One of the real beauties of the Internet is its global nature. But ever since we’ve had it, people keep trying to ring-fence certain locations, and make applications location-aware. While geolocation can be useful, over-dependence upon it is a real pain in the neck.

The first situation where this is painful is in media restrictions. Companies are still trying to enforce country and region boundaries for media licensing.

We need to get past this.

2018-11-27

I often deal with quite large databases. There are only two things about big databases that trouble me. One is queries that need to read the whole database to find anything. The other is big databases that are still designed like small databases.

Every week, I still see massive SQL Server databases where all of the data is in a single PRIMARY filegroup. That’s a problem but often the people looking after the databases can’t do much to improve that situation.

2018-11-26

Damon Edwards had a session recently where he said claimed that Without Self-Service Operations, the Cloud is Just Expensive Hosting 2.0. There is much in his session that I completely agree with, and have been concerned about for quite a while. I see it more in terms of the adoption of Platform as a Service (PaaS) offerings.

I spend most of my consulting/mentoring time in larger organizations, many are large financial organizations. In every one now, there is a person heading up a “Cloud Transformation” project, but none of these companies mean the same thing when they talk about these types of projects.

2018-11-23

SQL Server 2008 added the ability to work with spatial data by the additional of the geometry and geography data types. When they first were added, there was no tools support for working with them, and all we had was direct manipulation of their internal binary storage.

Here’s an example:

I’ve defined a variable named @Shape of type GEOMETRY. I’ve then assigned a shape to it, based on a polygon formed by a set of points. If you look carefully, you’ll notice that it’s a square.

2018-11-22

I’m often checking out what the primary keys are for tables that I’m working on. One of our free SDU Tools for developers and DBAs does just that. it’s ListPrimaryKeyColumns.

You can see how to execute it in the main image above. The procedure takes these parameters:

@DatabaseName sysname - This is the database to process @SchemasToList nvarchar(max) - a comma-delimited list of schemas to include (ie: ‘Sales,Purchasing’) or the word ‘ALL’ @TablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: ‘Customers,Orders’) or the word ‘ALL’

2018-11-21

Banks, Councils, and Government Departments are often lecturing customers about protecting against identity theft, yet they often a indirect potential cause of that threat.

Sending to Old Addresses

This one really frustrates me. When we change the postal address for one of our accounts, they almost always send a letter to our old street address. I can imagine why they think that’s a sensible idea, but if we’ve already left that address, what they are doing is sending our private details to whoever now occupies the house.

2018-11-20