The Bit Bucket

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

Betteridge’s law of headlines says that any headline that asks a question can be answered “no”. Well, contrary to that law, the TLDR answer to this is “yes”. I’m endlessly reading information that says that if your referential integrity is being checked in your OLTP application that’s providing data for your data warehouse, that you don’t need to then check it within the data warehouse. Sadly, this is often naïve thinking, for a number of reasons.

2018-11-19

I’m trying a number of different categories of books lately, because I’m getting through far more than previously. One category I thought it would be interesting to pursue where the conversation/influence areas. One that caught my eye was Exactly what to say: The Magic Words for Influence and Impact by Phil M Jones.

This was quite interesting but I’m not sure if I liked it or not.

In this book, Jones takes you through a number of situations, where people often say the wrong thing and make things worse, or where they don’t even know what to say, and he gives formulas for how to structure responses that turn the situation back around the way you want, to keep heading to the right outcome.

2018-11-16

SQLCMD mode changes how queries are executed in SQL Server Management Studio (SSMS). When using this mode, you can work with options that aren’t normally part of SQL Server T-SQL scripts.

Some installation scripts also require SQLCMD mode and will fail if it’s not enabled.

Here’s an example of running a query against 3 servers within the same script:

First we open a new query window, then on the Query menu, we choose SQLCMD Mode.

2018-11-15

Anyone who’s worked with SQL Server for any length of time has probably run into collation issues. They will have seen something like this:

SQL Server Error: Cannot resolve the collation conflict

In many cases, it will be a difference between the collation in tempdb and their own database, they’ve created temporary tables, and tried to join them to tables in their database. And it goes bang !

While you can fix it in the query by specifying a COLLATE clause, it’s a pain in the neck. If you use temp tables, you really should have this when working with them so your application isn’t dependent upon the server’s collation.

2018-11-14