Voice of the DBA

Author: way0utwest

Webinar: Unit Testing with SQL Server and tSQLt

I ran into Sebastian Meine at the PASS Summit a few weeks ago and we were talking testing. Sebastian is the founder and developer of tSQLt, which I really like using. We’ve done some teaching together and I’ve delivered a number of sessions on tSQLt at various events, but we wanted to get more people interested in testing code.

I had a session at PASS, which was very well attended. 150+ people came, which was stunning to me. I was expecting to see 20, and afterwards Sebastian and I started talking about what else we could do.

We’ve decided to do a webinar, but one driven by you. We are looking for you to ask questions about code you’d like tested, or which you’re unsure of how to approach. Leave a comment here, or put your question in the webinar registration. The details are:

Unit Testing in SQL Server with tSQLt
https://attendee.gotowebinar.com/register/7623481833734658561
Thurs, Nov 19, 2015 11:00 AM – 12:00 PM EDT

Join unit testing experts Steve Jones and Sebastian Meine for this exciting opportunity to learn about unit testing and the tSQLt framework to improve your T-SQL code quality and maintainability. If this day/time is not good for you, register anyway so you receive a link to the recording when it is available.

November 11, 2015
Updating Extended Properties
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

I wrote recently about adding extended properties. Updating them is very similar. There’s an analogous procedure called sp_updateextendedproperty that changes the value of properties.

The arguments are again, unintuitive, but the more I work with extended properties, the more comfortable I become. In this case, I have the same name and value, and then the level 0,1, 2 items with both a type and name.

I highly suggest, however, that you name your parameters, including the names in your calls so programmers running across the T-SQL aren’t depending on position for an understanding of the parameter.

If I look at the table from the previous post, I can update the value of my property with this code:
```
EXEC sys.sp_updateextendedproperty
  @name = 'PKException'
, @value = 0
, @level0type = 'schema'
, @level0name = 'dbo'
, @level1type = 'table'
, @level1name = 'SalesHeader_Staging' -- sysname
  ;
GO
```
However my property needs to exist. If I call this procedure with the wrong property, I get an error.

This means that you need to be sure that the property exists before you update it. Good code would have the error handling somewhere.

SQLNewBlogger

After writing the previous post, this one took only about ten minutes to do the typing. I’d been working with extended properties, so I had the code and just needed to take the screenshot.

Reference

A few items from BOL

sp_updateextendedproperty – https://msdn.microsoft.com/en-us/library/ms186885.aspx
November 11, 2015
Do You Have Scary Code?

I once worked in a company that had a VB6 application (this was a long time ago), which had been mainly written by three developers working at the company. Two of them left, but we still have one of the original developer and five or six others that had worked on the application for a year or more.

One day we were discussing changing a section of the application to add functionality. I was surprised to find that none of the developers wanted to work on the code. They were all “afraid” to make changes. Having been a developer and spent time digging through other people’s code, I was surprised. Certainly some tasks are difficult, but being afraid to change code?

I wish I’d been more knowledgeable then. Today I’d tell the developers the first thing they need to do is write tests. They need unit tests, or integration tests, but they need some way to determine if they are breaking functionality.

And if they do break something, that’s fine. Go fix the breakage. Refactor other code, write more tests if they are needed, and go for it. You learn by breaking things. Your tests protect you and let you refactor code. As much as I realize we don’t want to spend unnecessary time writing tests, we need something to examine our code as we write. We might as well use a testing framework to help. That way we’re not afraid to change the existing application.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn.

November 10, 2015
Poor Data Modeling – T-SQL Tuesday #72
This month Mickey Stuewe hosts the T-SQL Tuesday and she has a great topic. Data Modeling is something few people seem to do, especially developers, and it often can go wrong. That’s the topic, and I have a short story.

T-SQL Tuesday is a monthly blog party, on the second Tuesday of the month. The rules are on Mickey’s blog, and you can read them there, but essentially you write a post every month.

Or later.

The Aging Model

I once worked at company that shall rename nameless. We had a system built before my time which had a hierarchical set of entities. I can’t disclose the exact industry, but imagine that we had a list of items like this:
- Manufacturers of products
- Distributors of products
- Warehouse holding products
- Vendors selling products.
In this structure, we have a strict linkage where each item below the next is contained in the item above it. In other words, a manufacturer works with specific distributors and only those distributors. Distributors don’t work with other manufacturers.

I know this sounds contrived, and it is for a supply chain. However not for the industry in which I worked. So imagine we’re 100 years ago when power was more concentrated with supply chains.

This resulted in tables like this:
```
CREATE TABLE Manufacturers
  (
    manufacturerid INT
      IDENTITY(1, 1) CONSTRAINT manufacturer_PK PRIMARY KEY ( manufacturerid )
  , manufacturername VARCHAR(200)
  , primarycontactid INT
--  ...
  );
GO
CREATE TABLE Distributors
  (
    distributorid INT
      IDENTITY(1, 1) CONSTRAINT distributor_PK PRIMARY KEY ( distributorid )
  , manufacturerid INT
      CONSTRAINT Distributor_Manufacturer_FK
      FOREIGN KEY REFERENCES dbo.Manufacturers ( manufacturerid )
  , manufacturername VARCHAR(200)
  , PrimarySalesPersonid INT
--  ...
  );
GO
CREATE TABLE Warehouses
  (
    warehouseid INT
      IDENTITY(1, 1) CONSTRAINT warehouse_PK PRIMARY KEY ( distributorid )
  , distributorid INT
      CONSTRAINT Warehouse_Distributor_FK
      FOREIGN KEY REFERENCES dbo.Distributors ( distributorid )
  , warehouse VARCHAR(200)
  , regionid INT
--  ...
  );
GO
```
Each of these links to the item above it. This means that I might have a Manufacturer table like this:

manufacturerid manufacturername   …

————– —————-

1               Acme

2               Big Product Corp

With warehouses linked as shown.

warehouseid manufacturerid warehousename   …

———– ————– ————-

1            1              Denver Central

2            1              Kansas City East

3            2              Santa Fe

4            1              Billings Depot

This would mean that I used distributors that worked with a warehouse, and their data would be.

distributorid warehouseid distributorname …

————- ———– ————-

1            1            Denver City

2            1            Denver South

3            1            Aurora

4            2            Kansas City Distributors

5            3            New Mexico Wholesale

If I wanted to get a list of the distributors that carried a manufacturer’s products, I’d have to join through the warehouse table.
```
SELECT 
    manufacturerid
  , ...
 FROM dbo.Manufacturers AS m
 INNER JOIN dbo.Distributors AS d
 ON d.manufacturerid = m.manufacturerid
 INNER JOIN dbo.Warehouses AS w 
 ON w.distributorid = d.distributorid
 ...
```
Not a big deal, but we had 5 levels of this appear over time. Which means that queries that might need a higher level had to cross tables in between to join data. These also weren’t narrow tables, with a decent amount of meta data for each entity. Indexing helped, but certainly we needed better rollups of data, and performance suffered as the amount of legacy data grew.

What’s more, over time, we learned that business changes. A warehouse might start to work with multiple distributors, and our model couldn’t cope.

Eventually we embarked upon a project that set of link tables between entities, so that we had just IDs in a table that showed linkages where appropriate. This was ongoing when I left, but it was a major disruption and problem for the company.

I’ve tried to avoid embedding hard relationships based on business contracts in my models. Certainly some FKs make sense, like order details linked to a specific order, but many other relationships aren’t as tightly bound, and it is possible that they will change. It doesn’t cost me a lot in modeling to treat suspect relationships as many:many, and if application development does this from the start, it’s an easy architecture to incorporate in their development.

Data modeling is always a struggle as we often work with incomplete information because an organization will morph over time. It is, however, worth taking some time to think about the possible ways in which things might change in the future and allow for some flexibility in your model where possible.
November 10, 2015