Press "Enter" to skip to content

Category: T-SQL Tuesday

UPDATE FROM Clause Usage

Published 2016-01-13 by Kevin Feasel

Rob Farley shows us UPDATE FROM:

1. It can let you access data in the columns of those tables, to use in predicates or expressions.

2. It can let you filter the data in the base table, by only allowing rows which match, such as when using an inner join or right outer join.

3. It can cause rows in the base table to be returned multiple times, if multiple rows in the joined table match a single row in the base table.

4. It can introduce NULL rows, if a full or right outer join is being done (or a left outer join with the base table second) and there are rows in the joined table that don’t match any rows in the base table.

This is a useful bit of T-SQL-specific syntax, but it’s a sharper edge than most UPDATE statements.  For a look back in history, Hugo Kornelis wanted to deprecate this syntax with the release of SQL Server 2008 (though MERGE has its own bugs and “Won’t Fix” problems, so in retrospect, perhaps it’s best that we still have UPDATE FROM).

Comments closed

T-SQL Tuesday #72

Published 2015-11-11 by Kevin Feasel

Mickey Stuewe hosted T-SQL Tuesday this month.  Her topic:  data modeling gone wrong.  A few choice posts on the topic follow.

Mickey herself looks at a case in which surrogate keys didn’t quite do the trick:

One of the problems I’ve seen with careless use of surrogate keys are the duplication of natural keys. Quite often it’s overlooked that the natural key still needs to have a unique constraint. Without it, the reporting team ends up having to use MAX or DISTINCT to get the latest instance of the natural key, or SSIS packages are needed to clean up the duplicates. This can be compounded with many-to-many tables.

Surrogate keys are not replacements for natural keys; they are physical implementation mechanisms to make your life easier.

Rob Farley wants you to think about design and whether your warehouse is built in a way that helps the business:

Many data professionals look at a data warehouse as a platform for reporting, built according to the available data sources. I disagree with this.

The models within a data warehouse should describe the business. If it doesn’t, it’s a data model gone wrong.

What is the central thing that your business does? What is the main interest point? What do you need to look after? For me, this forms the core of the warehouse.

Thomas Rushton says name your stuff right.  Picking the right name can be difficult.  “Field1” probably isn’t the right name, though.

2 Comments