StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POGUID column used to update a table - should it be indexed?
text
Body
copied!<p>I'm working on a database where I retrieve the ID of my record after I've added it by saving and retrieving a known GUID. My application runs on both SQL Server and Oracle, so I can't use @@IDENTITY*.</p> <p>Say for example, I add a new address:</p> <pre><code>ID (identity column in SQL Server;sequenced/trigger column in Oracle) ... address data... (street, town, postcode, etc.) GUID </code></pre> <p>I get the ID back by doing the following:</p> <pre><code>1. INSERT INTO ADDRESS (... address details, GUID = {some new GUID value}) 2. SELECT ID FROM ADDRESS WHERE GUID = {my GUID value} 3. UPDATE ADDRESS SET GUID = NULL WHERE GUID = {my GUID value} </code></pre> <p>At part 1, I'm adding the address details and setting the GUID value in the database; at part 2, I'm getting my ID back and at part 3, I'm removing the GUID value from the database (to avoid the very unlikely event of a duplicate GUID).</p> <p>When I look at the SQL Server 2008 Estimated Execution Plan for the third bit, it shows the following path if I have no index on the GUID column:</p> <p><img src="https://i.stack.imgur.com/GBSnS.jpg" alt="Image shows the estimated execution plan where there's no index on the GUID column"></p> <p>and the following path if I have an index on the GUID column:</p> <p><img src="https://i.stack.imgur.com/oMW37.jpg" alt="Image shows the estimated execution plan where there's an index on the GUID column"></p> <p>My question is: I realise that the scan shown in the first image is not as good as the seek shown in the second image, but would the fact that the GUID column is essentially empty 99.999% of the time mean that I shouldn't index the field, because it would be fragmented all the time and would waste resources? Or would the index still help because it would easily show me where my one-and-only GUID is that I just added, rather than having to do a whole scan for it?</p> <p><strong>In summary: Given the fact that the GUIDs are added and then immediately removed, is there any point in indexing the GUID column?</strong></p> <p>*In fact, the design is not my own, so I'm unable to use a different method; however, you should see that the design of my particular database does not affect my overall question.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload