Posts

Going to Dublin

June 5, 2007

I still lack the time to blog about proper hardcore SQL stuff, but I do want to make a quick post to let you all know about a great conference that I’ll be attending and speaking at. I’m talking about the Irish Microsoft Technology Conference, to be held in Dublin, Ireland later this week (June 7th, to be precise). I am particularly excited about this conference, not only because it marks my first speaking engagement outside the Netherlands, but also because (unlike some other conferences I have attended) there is actually a pretty decent number of SQL Server related…

Correcting my mistake

May 18, 2007

Hugo Kornelis

No Comments

Probably the worst thing that can happen to a budding technical book author, is to find an error, even if it’s a minor one, in the submitted material just after the deadline for corrections closes. Figures that it should happen to me, eh? Back in November last year, Adam Machanic asked me to contribute a chapter on spatial data for his book, Expert SQL Server 2005 Development. I have cursed myself for accepting his proposal several times, but I must admit that now all the hard work is done and the book has finally hit the shelves, I am…

Can you trust your constraints?

March 29, 2007

Hugo Kornelis

SQL Server allows you to temporarily disable your CHECK and FOREIGN KEY constraints. I would not recommend this as part of your regular production schedule, but there are certainly cases where this comes in handy. One example is if you’re copying thousands of rows from another table, and you already know with 100% certainty that the data doesn’t violate any constraint. The overhead of checking could impact performance of your process; in the worst case making the execution time exceed your maintenance window. Disabling the constraints can result in a tremendous reduction of execution time. Another example would…

Back with a vengeance!

March 29, 2007

Hugo Kornelis

It’s been quite a while since my last blog post. Almost three months, in fact. In case you have been wondering if I was ill, dead, or abducted by aliens – not at all. The reality was far worse: Adam Machanic has coerced me into contributing a chapter on spatial data for his excellent book “Expert SQL Server 2005 Development”, due out in May (rather than the original plan to ship in March, possibly due to a certain author of a certain chapter not meeting any of his deadlines…). Anyway, the chapter is finished and I now finally find…

The Beatles versus the Stones – the explanation

January 16, 2007

Hugo Kornelis

No Comments

On December 31 of last year, I posted this brain teaser, promising to post the answer “in a few days”. Apparently, 15 is a few J. In case you have forgotten what the puzzle was about and are too lazy to click the link above, the bottom line is that I created and populated two tables, with the same schema but different content. One held the first and last name of each of the Beatles, the other held first and last name of each of the Rolling Stones. I then queried both tables, using the same query in both…

The Beatles versus the Stones

December 31, 2006

Hugo Kornelis

Here’s a nice brain teaser, just before the end of the year. Despite the title, it is related to SQL Server, not to music! A common misconception amongst SQL Server users is that a clustered index on a table will ensure that data is returned in the order implied by that index. I have lost count of the number of times I had to disprove this notion. Of course, there are many cases where the rows returned by a query will be in the order of the clustered index. Here’s a quick illustration, using the lineup that The…

The table scan from hell

November 3, 2006

Hugo Kornelis

Greg Linwood, a fellow SQL Server MVP, has started a series of articles in which he attempts to prove that having a clustered index on each table is not a good practice. However, he has failed to include the effects of fragmentation into account, so I decided to run some tests for myself. One of those test had rather upsetting results. First a bit of background, for those of you who have never read a book by Kalen Delaney. A table without clustered index (also known as a “heap”) can be read in two ways: using a bookmark lookup…

Just stuff it!

October 12, 2006

Hugo Kornelis

I’m sure you’ve all heard it, and probably even said it, many times: “SQL Server sucks at string manipulation”. And with good reason – it is true. But not quite as true as many seem to believe. I notice that many people who complain about SQL Server lacking string manipulation are themselves unaware of the string functions that SQL Server does have. Most know LIKE, LEFT, RIGHT, and SUBSTRING. Some also know CHARINDEX, maybe even REPLACE. But how many of you know and use PATINDEX, REVERSE, QUOTENAME, and LTRIM, to name just a few? The string function that…

The prime number challenge – great waste of time!

September 23, 2006

Hugo Kornelis

No sane person would even consider using SQL Server to construct a list of prime numbers. So just to prove that I’m not sane (as if there could be any doubt!), this post will be about finding prime numbers. First a bit of history. Ward Pond wrote about efficient ways to populate a table with one million GUIDs. I posted a comment with a slightly more efficient algorithm. And that was quickly followed by a new post from Ward, tweaking my syntax even further. And that’s when Denis the SQL Menace lived true to his name by posting this…

Snapshot isolation: A threat for integrity? (Part 4)

September 15, 2006

Hugo Kornelis

In the previous parts of this series, I have shown how SQL Server prevents violations of foreign key constraints by silently disabling snapshot isolation. I have also demonstrated how you can use the same technique inside your trigger code, to keep snapshot isolation from damaging your custom integrity rules. Now, in the final part of this series, I will investigate some less common techniques for preserving integrity. Note that I would normally not recommend any of these techniques, but I do see them often enough in newsgroup postings. Apparently, they are used. First, I’ll set up the tables again.…

Posts

Going to Dublin

Correcting my mistake

Can you trust your constraints?

Back with a vengeance!

The Beatles versus the Stones – the explanation

The Beatles versus the Stones

The table scan from hell

Just stuff it!

The prime number challenge – great waste of time!

Snapshot isolation: A threat for integrity? (Part 4)

Hugo Kornelis

Subscribe via Email

Meta

Archives