Voice of the DBA

Tag: T-SQL

Are there that many GUIDs?

Do a lot of people actually use GUIDs as Primary Keys? I haven’t used them much, and I would have thought that more people chose identity keys. It seems that most of the demos and examples I see from bloggers and speakers are constantly using identities.

However an informal survey from Peter Bromberg showed that four times as many people actually had GUIDs as their primary keys. The blog actually says that GUIDs are not a good choice, but I’m not sure I agree with that. You can use sequential GUIDs, and you can avoid making them the clustered key, so I think they can work as well as anything.

There’s nothing inherently wrong with GUIDs, and they should be unique across all of your rows. There have been some reported cases of duplicates, but for most practical purposes, especially in database work, you ought to be able to count on a GUID as unique. They even have the nice capability of being generated by clients, removing the need for an extra round trip when a client needs to insert multiple rows.

I typically don’t use them because they’re long, hard to remember and type, and hard to view on the screen. I can’t easily compare rows in multiple tables, and it’s easier for me to work with integers. I don’t recommend them, but if you are going to use them, be sure you understand the pros and cons, and use them appropriately.

Steve Jones

October 1, 2010
Common SQL Server Mistakes – Multi Row DML Triggers

How often have you seen someone write a DML trigger like this:

create trigger mytrigger on Mytable for insert as

declare @id int
select @id = id from inserted

update xx set yyy = zz
where id = @id

return

There seems to be this common misconception that a trigger fires for each change in a row (insert/update/delete), and that’s not true. As noted in Books Online, triggers fire once for the insert/update/delete. Typically this is an implicit transaction for the statement. If you have multiple statements inside an explicit transaction, the trigger fires once for each insert/update/delete statement in the transaction.

That means that if I change two rows and I have the trigger above, I won’t get the behavior I expect from the trigger. Let’s say that I want to update my inventory table each time I change an order. Imagine that I have this in my orders table:

and this in my inventory table.

If I now write this trigger:

alter trigger orders_update_inventory on orders
  for update
  as

  declare @qty int
     ,    @product int

  select @qty = a.qty – b.qty
    from inserted a
       inner join deleted b
         on a.orderid = b.orderid
  select @product = productid from inserted

  update inventory
   set onhand = onhand – @qty
   where productid = @product

return

and execute this:

update orders
  set qty = 2
   where orderid = ’59CD85CE-984C-4D33-9E23-5F6159848277′

I will find that my inventory table looks like this:

That appears to work, but what happens if we execute this?

update orders
  set qty = qty+1
  where[CustomerID int] = 2

Then we find that the inventory is

In this case productID 1 has had its inventory reduced by 1, but not product ID 2. Why not?
When the trigger fires, there are actually this data in the tables:

Inserted
OrderID OrderDate CustomerID int qty         productid
——– ———- ————– ———– ———–
59CD…    2010-09-22 3              3           1
01B6…    2010-09-22 2              2           2

and deleted

OrderID OrderDate CustomerID int qty         productid
——– ———- ————– ———– ———–
59CD85… 2010-09-22 2              2           1
01B66E… 2010-09-22 2              1           2

However the trigger, in setting the variables to the result of a query could have picked either of the rows, but only one row. SQL Server doesn’t guarantee order without an ORDER BY, so either product ID could have been chosen. As a result, only one of the products had the inventory updated.

A proper trigger would look like this

alter trigger orders_update_inventory on orders
  for update
  as

  select @qty = a.qty – b.qty
    from inserted a
       inner join deleted b
         on a.orderid = b.orderid
  select @product = productid from inserted

  update inventory
   set onhand = onhand – ( a.qty – b.qty)
   from inserted a
       inner join deleted b
         on a.orderid = b.orderid
   where inventory.productid = i.productid

return

Triggers should always be written to handle multiple rows, using the inserted and deleted tables for joins instead of variables. Even if you always just update single rows, coding this way will prevent issues if there is a multiple row change.

September 23, 2010
Community Direction
When Microsoft implemented Connect, I thought it was a great idea. It was a way for real users to submit bugs, and others to see those bugs, voting on them if they thought they were important. It would help Microsoft determine what features and bugs are important and perhaps allocate resources accordingly. However there was a fundamental problem with the system. People would see individual items, and could vote for them, but wouldn’t have an idea of what other items might be listed.

The work on SQL 11 is underway, and recently I got a note from Itzik Ben-Gan asking people to vote for windowing enhancements to the T-SQL language. I’m not sure exactly of all the places that these are useful, but Itzik is one of the smartest people I know and I tend to believe that if he finds these enhancements useful, they are likely going to make T-SQL easier to work with.

But are these items a priority? I am sure they are valuable, but are they more valuable than CREATE or REPLACE? IS it more of a priority than allowing SSMS add-ins? There are any number of enhancements that are listed, but most of us don’t have the time to dig through them all, or even try to determine how important they might be when weighed against other items.

Microsoft can do what they want, and they need to keep one eye on the sales generated from new features. However I wish that they’d reserve a slice of their development efforts for older features and get some community help in choosing which items to work on. I’d love to see a list of the items they are considering, maybe the top 20 features, and let us add votes to pick the 10 they can work on.

We may not sign the purchase orders, but us DBAs really like SQL Server and would appreciate improvements that make our jobs easier.

Steve Jones

PS: Here are Itzik’s items:
September 22, 2010
Table Variables and Transactions
I actually had a question of the day submitted on SQLServerCentral about table variables and transactions, but the person didn’t have a reference for it. So I had to go digging around to find one. It doesn’t seem to be documented in BOL, but numerous MVPs and MS employees have posted about this behavior (by design) in places.

Here’s the code I saw:
```
DECLARE @MyTable TABLE 

  ( MyIdentityColumn INT IDENTITY(1,1),
    MyCity NVARCHAR(50))
INSERT INTO @MyTable (MyCity) VALUES (N'Boston');
BEGIN TRANSACTION IdentityTest
INSERT INTO @MyTable (MyCity) VALUES (N'London')
ROLLBACK TRANSACTION IdentityTest
INSERT INTO @MyTable (MyCity) VALUES (N'New Delhi');
SELECT * FROM @MyTable mt
```
What do you expect from that? What about this code?
```
CREATE TABLE TranTest 
 ( MyIdentityColumn INT IDENTITY(1,1),
   MyCity NVARCHAR(50))
GO
INSERT INTO TranTest (MyCity) VALUES (N'Boston');
BEGIN TRANSACTION IdentityTest
INSERT INTO Trantest (MyCity) VALUES (N'London')
ROLLBACK TRANSACTION IdentityTest
INSERT INTO TranTest (MyCity) VALUES (N'New Delhi');
SELECT * FROM TranTest mt
```
In the second case, you’d expect two rows, right? Something like this:

However in the first case you get:

The insert with “London” isn’t rolled back. This is because the table variable doesn’t participate in transactions. You can see this with this update as well.
```
DECLARE @MyTable TABLE (MyIdentityColumn INT IDENTITY(1,1),
 MyCity NVARCHAR(50))
INSERT INTO @MyTable (MyCity) VALUES (N'Boston');
BEGIN TRANSACTION IdentityTest
UPDATE @MyTable SET MyCity = 'Denver'
ROLLBACK TRANSACTION IdentityTest
INSERT INTO @MyTable (MyCity) VALUES (N'New Delhi');
SELECT * FROM @MyTable mt
```
You might think this is a bug, after all, a transaction is supposed to capture changes and enforce the ACID principles. That is true, but a table variable isn’t a permanent change on the database. It’s a temporary object that exists only in memory, and only for the duration of the batch.

This means that if you want to persist anything from a table variable, you need to write it to a real table, which will enforce ACID principles.

Where do you need this? It’s extremely handy for capturing information about potential issues in a transaction, like logging, and returning them outside of the transaction (where/when you can store them in a real table).
September 21, 2010