Blog - Page 25 of 25 - SQLServerFast

Fun with ambiguous table names

September 2, 2006

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”.…

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

August 25, 2006

Hugo Kornelis

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…

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

July 26, 2006

Hugo Kornelis

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…

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

July 21, 2006

Hugo Kornelis

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…

Relating unrelated rows

July 14, 2006

Hugo Kornelis

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…

Fun with ambiguous table names

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

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

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

Relating unrelated rows

SQL injection, and how to prevent it

To be where I need to be

Ups and downs

Home for now

Bad parameter sniffing, what is it and what can you do about it

Hugo Kornelis

Meta

Archives