Sql-Server

I spend a lot of time with developers and with ISVs (Independent Software Vendors). Often, I come across really weird table designs. A common problem is the creation of tables that hold many different types of objects.

If I look at one of your tables, and ask you what it holds, your answer shouldn’t start with “It depends”.

A worse design is one where the same discussion happens about columns. If I ask you what is held in the ObjectID column, and you say “that depends upon what’s in the ObjectType column”, you’d better be building some sort of utility, not a relational database to support an application. There are so many downsides to this type of design but that’s a topic for another day.

2018-03-12

I’ve recently been making use of SQL Clone from Redgate, at various client sites. I have to say that I really like it.

The basic concept is that you take an existing SQL Server database, you create an “image” from it, then you create database clones from that image.

Under the covers, it’s basically a differencing technology. The initial image is like an initial set of database files, and each clone is then files that are differenced from that. That makes it very easy to spin up clones, and to break them down again. While this isn’t really suitable for much in the way of performance or load testing, as everyone is sharing the same base, it’s perfect for general development.

2018-03-09

It’s great that SQL Server Management Studio has moved into the latest Visual Studio shell. Unfortunately, there are one or two things that are a little harder at first for people who want to use SSMS to write T-SQL. One that was driving me crazy was the scroll bar. Visual Studio tries to give so much information on that bar, about what’s changed, where the insertion carat is, etc. The problem with this is that I often now can’t even find the handle when I want to scroll the window. For example, how do you grab the handle with your mouse and slide the window when it looks like this?

2018-03-08

One of the first things that I often do when familiarizing myself with a database, is to get a list of all the tables, columns, and data types. This immediately tells me a lot about how the database was designed.

For example, have they made newbie mistakes like using float for storing amounts of money.

One of our free SDU Tools makes this easy.

In the image above you can see the ListAllColumnsAndDataTypes procedure in use.

2018-03-07

There is an old saying about not reinventing the wheel yet this is something that I see happening at client sites every day. I see two main reasons why this happens:

Discoverability

[caption id=“attachment_2809” align=“alignnone” width=“339”] Image by Nathan Dumlao[/caption]

There are so many tools and frameworks in this industry, that you can’t be expected to know them all. I remember when I worked a lot with the .NET framework. I’d go into client sites and see them designing and building classes that were already in the framework. Worse, the framework classes were usually very well designed and tested.

2018-03-06

I spend quite a bit of time tracing queries sent from applications to SQL Server.

Having now done this for a long time, I can recognize many of the data frameworks that are used by various developers. I think that if any of the developers ever spent much time looking at traces, they’d be amazed at how the requests they have made to the framework are translated to commands that are sent to SQL Server.

2018-03-05

In my last DevOps-related post, I discussed some of the issues that arise when using migration-based deployments.

Migration-based deployments are based on a series of T-SQL scripts that apply changes to your database from its current state to a desired final state.

One of the questions that arises though, is whether or not these T-SQL scripts should be idempotent ie:

Should the script be written so that you get the same outcome if you run the scripts multiple times?

2018-03-02

Whenever I need to work on a new laptop or server, or whenever I change versions of SQL Server Management Studio, I kick myself for not remembering to export my settings, so I can import them again.

I spend quite a bit of effort getting SSMS configured the way I want, so it only makes sense to save the settings. Saving them isn’t perfect but it’s far better than not having done it.

2018-03-01

I’m really happy with the additions we made to our free SDU Tools for developers and DBAs that are going out tonight:

DatesBetween - table-valued function to return dates between two dates DateDimensionColumns - table-valued function to return typical date dimension columns for a date (useful for data warehouses)

JulianDayNumberToDate - converts a Julian day number to a date DateToJulianDayNumber - converts a date to a Julian day number

SystemConfigurationOptionDefaults - view that shows system configuration options and their default values (and many other values) NonDefaultSystemConfigurationOptions - view that shows system configuration options that are not at their default values

2018-02-28

If you’ve ever used SQL Server Reporting Services, you’ll notice that when you drag a database column into a table, it auto-magically converts the name of the column into a more English-readable name.

[caption id=“attachment_2791” align=“alignnone” width=“429”] Image from MSDN[/caption]

Notice how when the LineTotal column has been dragged into the table, the heading has been set to Line Total with a space. What it is doing is taking a Pascal-cased or camel-cased name and separating the words with spaces, based upon where the capital letters are.

2018-02-28