Be careful with Open Table
July 29th, 20073
Be careful with Open Table
July 29th, 20073

Yes, you were already aware of 40 or 50 reasons not to use Open Table. I have one more.

Say you are creating a table with a composite key that includes a binary column. An example I came across recently was a table that captures additional data not covered by CDC (e.g. username). I created this table:

CREATE TABLE dbo.CaptureDetails
  ActionType INT NOT NULL,
  Username VARCHAR(32) NOT NULL,
  PRIMARY KEY(StartLSN, EndLSN, ActionType)

I actually use cdc as the schema, not dbo, and populate this via a trigger on the CDC change table, but for ease of reproduction, let's just jimmy some data in there:

INSERT dbo.CaptureDetails(StartLSN, EndLSN, ActionType, Username) 
SELECT 0x0000001A000001360013, 0x0000001A000001360012, 2, 'sa'
UNION ALL SELECT 0x0000001A0000013A0003, 0x0000001A0000013A0002, 2, 'sa'
UNION ALL SELECT 0x0000001A0000013B0004, 0x0000001A0000013B0002, 3, 'sa'
UNION ALL SELECT 0x0000001A0000013B0004, 0x0000001A0000013B0002, 4, 'sa'
UNION ALL SELECT 0x0000001A000001490013, 0x0000001A000001490012, 2, 'sa'
UNION ALL SELECT 0x0000001A0000014C0003, 0x0000001A0000014C0002, 2, 'sa'
UNION ALL SELECT 0x0000001A000001560003, 0x0000001A000001560002, 2, 'sa'
UNION ALL SELECT 0x0000001A000001570003, 0x0000001A000001570002, 2, 'sa'
UNION ALL SELECT 0x0000001A000001590004, 0x0000001A000001590002, 3, 'sa';

Now, right-click this table in Object Explorer, and choose "Open Table." Pick any row where c = 2, and change the data from sa to what?. Close the table and then run Open Table again.

Whoops! You updated more than one row. Strange, huh?

Note that while I found this testing CDC in Katmai, this symptom is equally destructive in SQL Server 2005. I filed a Connect item (#289541) but, well, Connect died.

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

3 Responses

  1. AaronBertrand says:

    As for the 40 or 50 reasons not to use Open Table, if you are familiar with Connect, start here:*/
    In addition to the problem I outline there, I also reference a whole slew of Connect items that mention Open Table.

  2. AaronBertrand says:

    Good news, SJ!  In SQL Server 2008, Open Table is being replaced by the following menu items:
    EDIT n2 ROWS
    n1 and n2 will be configurable via Tools | Options.
    If you choose the SELECT option, it will launch a new query window, with SELECT TOP <column list> FROM <selected table>.  There will be a nice big comment in there to add an ORDER BY so that which n1 rows you receive is not arbitrary, and so that it is not misleading users into thinking TOP has some meaning without ORDER BY.
    If you choose the EDIT option, you will get the datagrid like you get with Open Table today.  In this case, there isn't a good way to make it very obvious that if the user wants a specific set of rows, they should switch to the SQL view and add an ORDER BY clause.

  3. SJ says:

    Actually, I would love to see the list of 40-50 reasons to not use Open Table.  My programmers and Jr. DBA do this all the time and it annoys the heck out of me.  And then they complain of performance.  
    Really, I would love to find a way to remove this menu item from the SSMS.  Know of anything?
    <a href=""><img src="; alt="Mathom Solutions Blog-IT" style="border:0"></a>