The Bit Bucket

When I first start working with any SQL Server system, one of the first things I want to look at is how the server has been configured.

More particularly, what I’m interested in is:

What has been changed from the default value?

None of the standard SQL Server system views, however, provides me with this info. We do have the sys.configurations view. It shows you the configurations and what’s been chosen, but it doesn’t show you what the default value was:

2018-04-11

In  a recent blog post, I wrote about the ANSI_NULLS option for tables and after that I had several more questions related to NULL values.

Note that I mentioned NULL values, not values of NULL, and not values equal to NULL. That’s because “being NULL” is a state that a value is in, not itself a value. That’s why our queries say IS NULL and not = NULL.

Now because of that, many developers see values that  are NULL as a pain in the neck as they have to be dealt with separately, unlike other values. This can lead to many poor design decisions but I want to start today with the decision to use magic values.

2018-04-10

I’m involved in a lot of performance tuning work for SQL Server based applications. These are mostly your typical OLTP accounting, financial, or record keeping applications. One thing that constantly surprises me is that many developers and DBAs really aren’t sure about where their tuning efforts need to be focused.

In particular, what really surprises me is how much focus most people have on write/update behavior and how little on read behavior. I think there’s a perception problem.

2018-04-09

In the last year or so, there has been a quiet revolution going on with how Microsoft delivers training and certification.

Previously, the main option was Microsoft Official Curriculum (MOC) courses delivered by Certified Learning Partners. For some years, I’ve been saying that I don’t see that as the longer-term model for Microsoft. I believe that’s for three reasons:

• The learning experiences team in Microsoft have needed to be a profit center.
• The product groups want as much information out there as possible and as free as possible.
• The creation and delivery processes for MOC courses don’t lend themselves well to constantly-evolving information.

That has to lead to real challenges within the company.

2018-04-06

I’ve mentioned a number of times how useful I think the Object Explorer Details panel is in SQL Server Management Studio.

Another option in that panel that might not be so obvious is the sorted navigation. Here’s an example.

I’ve opened WideWorldImporters in Object Explorer, and clicked on the Tables node:

I then hit F7 to open the Object Explorer Details pane, and click the Name heading to sort the table list:

2018-04-05

There are a few places in T-SQL where instead of a time value, you need to work with a duration.

An example is if I want to sleep for a short period. In T-SQL, we can do that with the WAITFOR statement, used like this:

This command would cause SQL Server to wait for 10 minutes. One of the challenges though, is how do I create this duration string in a programmatic way? For example, how do I create the string ‘00:02:00’ if I’m starting with a value of 120 seconds?

2018-04-04

Perhaps I’m getting old and philosophical but I recall the days when the IT industry prided itself on accuracy. We worried when things didn’t work properly and kept working on them till they did work.

When did that change?

In a previous blog post, I’ve wrote about the evil “Oops something went wrong” syndrome that seems to have infected many Sofware as a Service applications.

The screenshot above though, is the outcome after a Sony TV performed its routine software upgrade. Let me paraphrase it’s message for you: What it basically said is:

2018-04-03

If you’ve hung around the SQL Server community for any length of time, you’ll have heard the endless jibes about the AutoShrink option for databases. Using it is almost always a bad idea. In fact, shrinking databases is typically not a great idea but the AutoShrink option is considered universally bad and there are calls for it to be removed as an option.

We often joke that “auto shrink” should be renamed to “auto fragment my filesystem”, because that’s typically the outcome.

2018-04-02

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.

2018-03-30

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:

2018-03-29