Rob Farley - All Comments

Behind the scenes of PowerShell and SQL

Rob Farley — Tue, 12 Feb 2013 00:11:53 GMT

Every year, PowerShell increases its stranglehold on the Windows Server system and the applications that

Incorrect Results with Indexed Views

Paul White: Page Free Space — Tue, 05 Feb 2013 22:18:54 GMT

Microsoft have released a fix for incorrect results returned when querying an indexed view: The problem

Joins in single-table queries

Rob Farley — Tue, 11 Dec 2012 00:05:34 GMT

Tables are only metadata. They don’t store data. I’ve written something about this before, but I want

A T-SQL Tip: Working calculations

Rob Farley — Tue, 13 Dec 2011 00:46:17 GMT

T-SQL Tuesday again and this month is on T-SQL Tips (thanks Allen !). In some ways it’s a tough topic,

When is a SQL function not a function?

Rob Farley — Tue, 08 Nov 2011 00:01:23 GMT

Should SQL Server even have functions? (Oh yeah – this is a T-SQL Tuesday post , hosted this month by

Enforcing Uniqueness for Performance

Paul White: Page Free Space — Thu, 28 Jul 2011 15:38:57 GMT

Just a quick blog entry today, leading up to the next one (which is going to be *awesome*). A little

Using SQL Execution Plans to discover the Swedish alphabet

Rob Farley — Mon, 03 Jan 2011 05:54:54 GMT

SQL Server is quite remarkable in a bunch of ways. In this post, I’m using the way that the Query Optimizer

A Year of Tuesdays: T-SQL Tuesday Meta-Roundup

Adam Machanic — Tue, 07 Dec 2010 18:43:28 GMT

Just over a year ago I kicked off T-SQL Tuesday , "a recurring, revolving blog party." The idea was simple:

PASS Summit North America 2010

Rob Farley — Sat, 13 Nov 2010 21:23:03 GMT

My first PASS Summit didn’t let me down. LobsterPot Solutions sent two people to PASS this year – I was

Managing to get time away

Rob Farley — Tue, 10 Aug 2010 03:32:51 GMT

Conference season is coming up, and this means time away from work. Something not quite as trivial as

Christian’s book – not just Brent’s

Rob Farley — Tue, 29 Jun 2010 04:55:12 GMT

I like to joke with Christian Bolton ( @christianbolton ) about the time when he got accused of claiming

T-SQL Tuesday #002: The Roundup

Adam Machanic — Sun, 28 Mar 2010 19:58:20 GMT

According to T-SQL Tuesday rules as ratified by me in the first and second T-SQL Tuesday posts, the T-SQL

A CASE study in SARGability

Rob Farley — Sat, 20 Feb 2010 07:54:58 GMT

(Reposted from my msmvps.com blog ) A recent discussion on Twitter about a query that Denny Cherry was

SARGable functions in SQL Server

Rob Farley — Sat, 20 Feb 2010 07:51:13 GMT

(Reposted from my msmvps.com blog ) SARGable is an adjective in SQL that means that an item can be found

re: The Query Optimizer’s handling of Relationships for T-SQL Tuesday #003

Rob Farley — Sun, 14 Feb 2010 22:51:39 GMT

@Joe - Thanks for that. I didn't realise that, and it makes for some interesting thoughts. I do wish that SQL Server did it that way - perhaps at some point it might, or maybe that's just a pipe dream.

@RobLobbe - Sorry, we'll have to disagree on that one. I've seen too many situations where the Query Optimizer has been able to run a query so much better because a Foreign Key is in place. I've also seen too many situations where a database without FKs has referential integrity issues, and I would always want my Test and Production systems to be identical. Thanks for commenting though - it's always good to hear other perspectives.

re: The Query Optimizer’s handling of Relationships for T-SQL Tuesday #003

Rob Lobbe — Sun, 14 Feb 2010 22:35:34 GMT

Foreign Keys are a DESIGN tool. And should be treated as such. As far as the Query Optimizer goes, it should NEVER be an issue in my production database. FK's should be removed, as they only cause overhead to day-to-day processing.

It the feature of an application is to 'display an error for every FK breach' the vendor's going to get an earful for a shoddy product. Likewise if the product is relying on a cascade delete, what else are they 'guessing' at - especial if I want audit's and history retention.

In a development, or testing environment, sure put them in. They make sure the application doesn't break the rules, but from an optimizer standpoint I'm not concerned with this overhead - the final product will be that much better, without them.

re: The Query Optimizer’s handling of Relationships for T-SQL Tuesday #003

Joe Celko — Sun, 14 Feb 2010 19:06:30 GMT

FYI

Sybase and other SQLs implement the PK-FK relationships as pointer chains that connect the FK to the single PK value in the entire schema. They think of the schema as a whole. You can see the advantages of this for CASCADE, JOIN and other operations.

SQL Server and other lesser SQLs implement each table separately as if they were files, so the PK value is repeated over and over in the FK columns. To get good join performance, you usually index FKs in such products.

SQLs that use hashing actually want longer keys because they hash with fewer collisions.

re: The Query Optimizer’s handling of Relationships for T-SQL Tuesday #003

Jeremiah Peschka — Wed, 10 Feb 2010 13:34:15 GMT

I'm guessing you're referring to the difference between using a surrogate key and a superkey. I make that guess because if your PK is on a natural candidate key, it wouldn't be a question.

As long as the unique index has all included columns for queries (effectively making it a second clustered index), I would say that it's probably a better candidate for the FK than the artificial PK.

While we're frequently told we should use surrogate PKs, isn't it more important to balance logic and reason (referencing the natural candidate key) with convenience (referencing the surrogate key). A pure relational theorist, of course, would say to use the natural key. I've used with both and haven't seen appreciable differences in application performance. These days the choice comes down to what my predecessor has done.

Almost T-SQL Tuesday #003:

Michael Coles: Sergeant SQL — Wed, 10 Feb 2010 05:47:27 GMT

Looks like I just missed the deadline for T-SQL Tuesday #003 [oh well, whatcha gonna do]. The other day

T-SQL Tuesday #3: Database Relationships

Allen White — Wed, 10 Feb 2010 02:32:59 GMT

In my first T-SQL Tuesday submission I thought I'd mention something that we often take for granted when