Results 1 to 10 of 10

Thread: "SELECT rowguidcol" vs. "SELECT <actual name>"

  1. #1
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64

    Question "SELECT rowguidcol" vs. "SELECT <actual name>"

    I understand that I can do this is SQL Server:

    CREATE TABLE dbo.test1 (
    Col1 int IDENTITY,
    ColGUID uniqueidentifier rowguidcol NOT NULL CONSTRAINT DF_test1_ColGUID DEFAULT NEWSEQUENTIALID()
    )

    INSERT INTO dbo.test1 DEFAULT VALUES

    SELECT rowguidcol FROM dbo.test1
    SELECT ColGUID FROM dbo.test1

    I get this result:

    ColGUID
    ------------------------------------
    ED2D1EF0-0408-DD11-A8EB-001AA0D389DB

    ColGUID
    ------------------------------------
    ED2D1EF0-0408-DD11-A8EB-001AA0D389DB

    Here's the question: Is there any performance difference between using the actual column name and "rowguidcol"?

    I would like to be able to script a trigger that uses the rowguidcol column. If I could use rowguidcol instead of the actual column name, I would not have to hard-code the column name in the trigger.

    Thanks,
    Rob

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Run both queries in same batch and compare cost in execution plan.

  3. #3
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64

    Match

    The execution plan matches exactly, but that doesn't always tell the whole story.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    At least get some idea.

  5. #5
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64

    Going with rowguidcol

    None of the tests we've done shows that using rowguidcol instead of the actual column name performs any differently.

    We're going to use rowguidcol in update triggers. If we find any additional information, I will update this thread.

  6. #6
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    If there is any performance gain, I would expect it at compile time, so you can try to get the parse time estimate using

    SET STATISTICS TIME ON

    and run the query.

  7. #7
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64

    Statistics

    The results aren't too interesting. Let me know if you think there is a different way to run this to get better information.

    SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 1 ms.
    ColGUID
    ------------------------------------
    997DE886-140B-DD11-A8EB-001AA0D389DB


    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 1 ms.
    ColGUID
    ------------------------------------
    997DE886-140B-DD11-A8EB-001AA0D389DB


    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 1 ms.

  8. #8
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    CPU time 0 ms means the plan was already in cache, so you may retry it by flushing the cache first

    you can use

    dbcc flushprocindb <dbid>

  9. #9
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64

    With FlushProcInDB

    Here is the result using :

    ColGUID
    ------------------------------------
    997DE886-140B-DD11-A8EB-001AA0D389DB

    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 1 ms.

    ColGUID
    ------------------------------------
    997DE886-140B-DD11-A8EB-001AA0D389DB

    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 1 ms.


  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    So they don't have any difference in performance point of view, people can use either one they like.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •