DevOps: Human testing is still critical – and make sure they can spell

I've spent a lot of time lately working with automated testing systems.

Unit tests are great for checking blocks of code, etc. but integration tests and functionality tests are critical to projects. An area that is still tricky though is UI testing. There are some really good tools for this.

Selenium has been very popular and works well if you access it programmatically. There are good framework libraries for .NET and we've been using those. Selenium also had a pretty good IDE that could be used for recording but it's pretty much adrift now as it was based on an older version of Firefox and doesn't work on the latest versions. You could install it on an older version for testing but those versions aren't safe to use so the recommendation is to avoid it. Working with it programmatically is not that hard though.

This article shows a number of alternatives: https://www.guru99.com/selenium-alternatives.html

I've tried quite a few now but had pretty mixed results, so this area is still evolving. I had fairly good results with Katalon Studio but ended up quite frustrated with trying to use its recorder. So I ended up back to coding Selenium directly.

One thing that's been very clear to me though, is that no matter how good all this automated UI testing is though, you still need humans to do it as well. You can see examples above of basic UI issues.

Here's an example of a UI logic error:

It's also very clear that the humans that do this need to be able to spell.

No matter how good looking your application is, if it's full of spelling errors typos, or graphical errors, it won't look professional.

Shortcut: Filters in Object Explorer within SQL Server Management Studio

If you are working with databases with large numbers of objects, the contents of Object Explorer in SQL Server Management Studio can start to become a bit overwhelming. I have to admit that I don’t understand why it doesn’t offer an option to group by schema. That would be helpful.

But you can at least filter by things like schema, when you need to work with a specific set of objects. You’ll notice that if you click on the database name, that the filter in the toolbar is grayed out, but if you click on a node below that like Tables, you can click on the toolbar filter icon. You can also right-click the node and choose to filter:

You then are presented with these options:

That will work to give me just the tables in the Warehouse schema but notice that the operator is Contains. There are other options:

So I could see all the tables except those that are in this schema. Note that Creation Date has even more options:

I can then also use this for other interesting queries such as “Just show me tables that have been created since 1st March:

 

SDU Tools: Julian Day Number to Date in T-SQL (and reverse)

Working with dates and times in database systems and programming languages has always been "interesting", at least interesting in the sense that the old Chinese curse meant when it said "may you live in interesting times".

One of the curious variations though is the use of Julian day numbers. It's a count of the number of days since the beginning of the Julian period: https://en.wikipedia.org/wiki/Julian_day

SQL Server doesn't currently have a built-in function for converting to or from these so we added functions to our free SDU Tools for developers and DBAs. You can use the functions directly from our tool kit, or use them as examples of how to write these functions.

JulianDayNumberToDate and DateToJulianDayNumber are the two functions that allow you (in T-SQL) to easily change between the two. The range of values that is allowed is between 1721426 ('00010101') and 5373120 ('99990101').

You can see JulianDayNumberToDate in the image above, and this is DateToJulianDayNumber:

You can see them both in action here:

For more information on joining our insiders team to get all our free tools and resources, follow the link here:

http://sdutools.sqldownunder.com

 

 

 

Opinion: Don't write CREATE UNIQUE INDEX (ok, well not too often)

The CREATE INDEX statement is used to do exactly what its name says, it creates an index. But when you say CREATE UNIQUE INDEX, you are doing more than that; you are enforcing a business rule that involves uniqueness.

I have a simple rule on this. Wherever possible business rules like uniqueness, check values, etc. should be part of the design of the table, and not enforced in an external object like an index.

So, rather than a unique index, I’d rather see a unique constraint on the underlying table.

But that’s where real life steps in. I see two scenarios that lead me to occasionally use CREATE UNIQUE INDEX.

SQL Server and NULL

I mentioned the other day that SQL Server’s handling of NULL is pretty good. The area where it really falls down though is how uniqueness is treated with NULL values. If I create a unique constraint (or a unique index) on a table, SQL Server enforces the uniqueness but it also insists that only one row can be NULL. That’s not correct and it’s treating a value that’s NULL like it is a value.

If a column is nullable and is constrained as unique, the database engine should only enforce uniqueness on rows that do contain a value. The SQL Server variation of this is really painful and I wish it could be changed.

To work around the SQL Server limitation, you can CREATE UNIQUE INDEX on your table but make it a filtered index with a WHERE clause that makes it apply only to non-NULL values. This allows us to apply the rule of “if there’s a value, then it’s unique”.

INCLUDE Columns

When SQL Server creates a unique constraint, it creates an index to support that constraint. With other non-clustered indexes in SQL Server, we have the option to INCLUDE columns (ie: they are only present at the leaf level of the index) to help to create covering indexes, for performance reasons.

SQL Server doesn’t allow us to add included columns to a unique constraint. So this is another reason why I might say CREATE UNIQUE INDEX because I then get the opportunity to INCLUDE columns as well.

Wish List

In a perfect world, I’d prefer to have unique constraints that worked properly, and could also have included columns, all as part of the table design, not as external indexes.

SQL: Mirroring SQL Server backups is a fault-intolerant option

There are a number of words related to SQL Server that are overloaded. One of those is "mirroring".

Mirroring backups has nothing to do with other concepts like database mirroring.

Mirroring backups allow SQL Server to send the same backup to more than one location at the same time. The Books Online page for the BACKUP command describes the clause this way:

"MIRROR TO <backup_device> [ ,…n ] Specifies a set of up to three secondary backup devices, each of which mirrors the backups devices specified in the TO clause. The MIRROR TO clause must specify the same type and number of the backup devices as the TO clause. The maximum number of MIRROR TO clauses is three."

Up to three secondary backup devices can be used concurrently. I've seen people who assume that this means that if one of their backups fails (perhaps because they've run out of disk space), that at least they will have one or more other backups.

What that page doesn't spell out is that if any one of the backups fail, then all the backups fail.

This makes mirrored backups a fault-intolerant option. 99% of the time, I think you should avoid it. Create a single backup then copy it if that's what's needed.

 

 

DevOps: Keep moving forward not backwards when deploying to production

I was asked a curious question the other day: “Are all changes made to production systems actually deployments?”

I wish the answer was yes, but unfortunately patches do get applied directly to production systems without going through standard deployment processes.

Everyone understands the scenario. Code has been deployed using the normal processes, then someone has something that simply must be fixed urgently in the productions systems. At that point, you have two choices:

  • The traditional choice is to urgently patch the production server, then reapply those changes back into the development process.
  • The ideal DevOps approach is that an urgent patch is made in earlier environments then deployed through the normal processes.

The reason that people opt for the traditional choice above is that going back through the processes simply takes too long in most organizations. The problem with this option is that there’s no guarantee that the change that was made in production will be reapplied the same way when it goes back into the normal processes. Worse, it might not be applied at all. Even worse, if there are multiple production servers, it might be applied differently to each of those as well.

Image by Alice Achterhof

Then you have a real mess to deal with. Deployments start to break, and because they are then painful, they become less frequent and more risky, and then they break even more. It’s a horrible cycle that you can easily get into.

The aim when working with DevOps is to streamline the process for doing this to the point where you won’t hesitate to have it go through the normal deployment processes. You urgently patch and deploy the patch instead.

Shortcut: Extended Properties for SQL Server Objects

I started working with SQL Server in 1992, but all through the 1980’s and 1990’s, I was also working with Progress 4GL. I thought it was the best of the character-based 4GLs but unfortunately, they did a poor job of migrating to Windows and we decided to stop using the product.

One thing that I used to love with Progress though is that the metadata for each column in the database was much richer than what is present in SQL Server. In fact, Microsoft Access was probably closer to it in that regard. It’s something I really missed when moving to SQL Server. In Progress, when I defined a column, I could also define things like:

  • The name that would be displayed as a prompt on an input screen for this column
  • The format that data in this column would be displayed in
  • Any non-default input format or masking
  • And so on

Having this type of information in the database and stored in a consistent form can greatly reduce the amount of boilerplate code that needs to be written in applications.

SQL Server does have a concept of extended properties but what I think is missing is a set of “well-known” properties such as “Description”. The SQL Server team is starting to use these properties now for things like classifying (or labeling) data for security purposes in versions where built-in engine support isn't available.

I’d like to see them used more often. Here’s an example:

I often come across indexes in databases and when I ask why the index was created, no-one knows why. That also means that they are scared to remove the index. How much easier would this type of thing be if we had standard properties for each index that described why it was added?

We can already do this, but I just wish there were agreed standards for this.
As an example though, I tend to name indexes that are just present to support foreign keys, with the same name as the foreign key. That then matches how primary key indexes are named. In the WideWorldImporters database, you can see the naming convention. For the Sales.InvoiceLines table, here are the keys:

And here are the indexes:

It’s pretty obvious which indexes are there to support foreign keys. (Note we made a conscious decision not to index the Application.People related foreign key).

But for other indexes, how would you know why they are there? We included that info in our code generation, by using extended properties. To see this, right-click one of these indexes, and choose Properties:

On the Extended Properties page, you can see a description of why this index was created:

I’d like to see much richer metadata for each object in SQL Server and I suspect we might have to do that with a set of extended properties. I’d like to see a standard set of these defined. Even better would be a richer metadata store within the database engine.

SDU Tools: Count Words in T-SQL

Some of the simpler tools that we've added to our free SDU Tools collection over the last year or so, have turned out to be quite useful.

A good example of this is CountWords.

This function takes a T-SQL string, removes the punctuation, excess whitespace, etc. and then counts the number of words contained. There are a few things that can still trick it (avoiding that would need very detailed langauge parsing) but it's quite good.

You can use it directly from our toolkit, or use the tool as an example of how to write the function in T-SQL.

You can see it in the image above, and you can see it in action here:

For more information on joining our insiders team to get all our free tools and resources, follow the link here:

http://sdutools.sqldownunder.com

 

Opinion: Forced updates and tempdb

One of the changes that has happened with Windows in recent years is the concept of forced updates. Basically, you're going to get updates from now on, like it or not. Generally that's a good thing. You can delay them for a little while but not for that long. In the Advanced options update Updates, you can see this:

So you aren't going to delay them for that long.

Now what does this have to do with tempdb I hear you ask?

Well, tempdb gets recreated each time SQL Server starts, and that's normally in two situations:

  • You shutdown and restart the computer that it's installed on
  • You restart the SQL Server service

Now Windows 10 out of the box changes that behavior. If you shut it down, and start it up again, you'll find that objects that you had in tempdb are still there. That's because a shutdown and power up are no longer the same as a restart. Choosing shutdown actually hibernates the computer and power up just brings it back from hibernation.

So SQL Server didn't get restarted.

I generally find this when I go to create a demo table in tempdb, just after restarting my machine, only to find the table is already there.

You can change that by this setting in the power options for Windows 10:

Note that I've chosen to not have fast start-up enabled. When you turn it off, shutting down actually does a shutdown.

OK, so we can see how this affects SQL Server, but what does it have to do with forced updates?

Well it's because after Microsoft applies a forced update, I keep finding settings like this "automagically" reset for me, back to the value that I didn't want. I really wish they would not do this.

 

 

SQL: Why ANSI_NULLS matters for SQL Server Tables

Recently, I posted a link to show how to turn on ANSI_NULLS for  a table. It's not normally easy to change that but we added a procedure to our free SDU Tools to make it easy.

But one of the comments I received was a question basically saying "OK, you've shown how to change it but you haven't mentioned why it matters in the first place".

Fair enough. So that's the topic of today's post.

Many developers (and database people) get confused about the handling of NULL values. When we talk about a value being NULL, what we're really saying is that it has no value. This is quite different to a number being zero, or a string being empty (or zero-length).

It's also why we can't compare a value to NULL by using an equals sign (or not equals), but by saying IS NULL or IS NOT NULL instead. Being NULL is a state that a variable or column is in, not a value that it holds.

It's worth noting that some database engines still don't even get this right. And SQL Server was one of those in its past.

Here's an example. In the WideWorldImporters database, there is a Sales.SpecialDeals table. It has many columns but when it was shipped, it had two rows. Let's look at those.

Note that if I try to select the rows where StockItemID = NULL, I get no rows but if I compare where StockItemID IS NULL, I get the rows.

This is because a WHERE clause returns rows where the value of the predicate is TRUE. But with ANSI_NULLS on, NULL does not equal NULL. A comparison of NULL and NULL returns NULL.

You can see how the comparison works with and without ANSI_NULLS ON here:

SET ANSI_NULLS OFF takes us back to the bad old days whereas SET  ANSI_NULLS ON gives us ANSI standard SQL behavior on this. We don't want our tables declared with ANSI_NULLS OFF. (Or worse, I've seen some tables declared that way and others not).

You can read more here:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql