-
"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
-
Run both queries in same batch and compare cost in execution plan.
-
Match
The execution plan matches exactly, but that doesn't always tell the whole story.
-
-
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.
-
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.
-
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.
-
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>
-
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.
-
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
-
Forum Rules
|
|