SQLServerFast

The table scan from hell

Hugo Kornelis
9 Comments
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…
Read More

Just stuff it!

Hugo Kornelis
12 Comments
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…
Read More

The prime number challenge – great waste of time!

Hugo Kornelis
10 Comments
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…
Read More

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

Hugo Kornelis
5 Comments
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.…
Read More

Fun with ambiguous table names

Hugo Kornelis
1 Comment
Earlier today, I realised that Microsoft has forgotten to include some keywords in the list of reserved keywords. Now, a wise developer will still take care to omit those names when naming tables – but a bored developer can have loads of fun exploring the effects!   The keywords I am referring to are inserted and deleted. Everyone who ever coded a trigger knows that they refer to the pseudo-tables that hold the before and after image of all rows affected by the triggering DML statement. But since they’re not reserved keywords, it’s perfectly legal to name a column “inserted”.…
Read More

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

Hugo Kornelis
4 Comments
Last month, I showed how snapshot integrity can really mess up your triggers, promised you a workaround, and then went on holiday. Some events in my life kept me from posting the promised workaround for longer than intended, so I hope that you haven’t been holding your breath for it! J   The workaround is actually quite simple. Just remember the first part of this series, where I showed how SQL Server 2005 prevents violation of foreign key constraints when using snapshot isolation – it automatically and silently switches to a less concurrent isolation level. Of course, if Microsoft can…
Read More

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

Hugo Kornelis
1 Comment
In the first part of this series, I showed how SQL Server 2005 prevents violation of foreign key constraints when using snapshot isolation by automatically and silently using a less concurrent isolation level. In this part, I’ll show how snapshot isolation can be used to really mess up your data.   I’ll use the same sample tables I did in the first part, with one difference: only type A orders have to be for an existing customer; type B orders still need to have a customer ID included, but it doesn’t have to refer to an existing row in the…
Read More

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

Hugo Kornelis
5 Comments
One of the great new features of SQL Server 2005 is the snapshot isolation level. But exactly how safe is that feature? Can you still guarantee your data integrity if you use snapshot isolation level?   With most forms of data integrity, this is not an issue. But with referential integrity, it might be – after all, checking referential integrity usually requires the database to read data in other tables than the one being updated. Since readers and writers are supposed not to block each other if you use snapshot isolation, it’s easy to imagine a scenario where two concurrent…
Read More

Relating unrelated rows

Hugo Kornelis
3 Comments
A recurring question in the newsgroups is: “How do I output unrelated rows side by side?”   I’ll use the AdventureWorks database as an example. Suppose that my manager wants a report listing, for each order, all products ordered and all reasons the customer has for ordering from AdventureWorks. Here’s a sample of how the report should look.   SalesOrderNumber ProductID Reason SO51318 858 Other SO31518 870 Price SO31518 872 NULL SO31518 997 NULL SO31519 870 On Promotion SO31519 999 Price   Getting a list of all products ordered for each order is a trivial affair:   SELECT      soh.SalesOrderNumber, sod.ProductID…
Read More

Hugo Kornelis

Married, father of 2 children.

Awarded 13x MVP (2006-2016; 2019-now), in the areas SQL Server and Data Platform.

Subscribe via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Archives

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close