May 5, 2010 at 11:15 am
Hi,
I have given below the structure of the table I am dealing with. The schema that was given to me, which I am supposed to use has a record_number column which is a primary key although it is not useful at all. The reason for keeping record_number that was given was that it reduces the fragmentation. I do agree with that general principal but in this specific scenario, I believe it does not apply. The comments in "CREATE TABLE" statement give more details. Could you let me know whether the change I want to make for primary key is correct and if there is some documentation I can refer to which will further confirm that?
CREATE TABLE
/* Can have at the most 5 million records */
[dbo].[ATable]
(
[Record_Number] bigint IDENTITY(1,1) NOT NULL,
[Date_Created] datetime NOT NULL CONSTRAINT [ATable_Date_Created] DEFAULT (GETDATE()),
[Date_Modified] datetime NULL,
/* This can have at the most 5 *distinct* values*/
[A_GUID_Column] uniqueidentifier NOT NULL,
/* This can have as many distinct values as number of rows or even duplicates
but with A_GUID_Colum above, A_GUID_Column + A_Varchar_Column
will be unique
*/
[A_Varchar_Column] varchar(50) NOT NULL,
[Another_Varchar_Column] varchar(50) NOT NULL,
CONSTRAINT [ATable_pk] PRIMARY KEY CLUSTERED
(
--[Record_Number]
/* Given the fact that there can be only 5 *distinct*
GUIDs in table, I prefer to use following primary key
and drop [Record_Number] column altogether..
Does this change makes sense?
*/
[A_GUID_Column],[A_Varchar_Column]
)
ON [PRIMARY]
)
ON [PRIMARY]
Thanks in advance,
-Neel
May 5, 2010 at 11:46 am
So there are only 5 values for the GUID and you're proposing using it as the leading edge of the index?
No, that's a problem. You're going to find that the histogram for the stats for that index is going to be very weak and that's the main point that's going to allow the optimizer to use this index t find information within the system. You are better off with the other structure.
Assuming I understood what you were saying.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2010 at 12:08 pm
Did you mean "that's the main point that's *NOT* going to allow the optimizer to use this index t find information within the system". Because if it does, shouldn't I be actually use this structure?
From tests that I ran, I believe this index is being used in all the queries I am running on the data in this table. But this is only based on performance. Is there a way I can make SQL server tell which index it used for the query?
Thanks for your reply,
-Neel.
May 5, 2010 at 12:12 pm
If there are only five values for that guid, it's very unlikely you'll see improved performance from it.
You can tell what SQL Server is doing with indexes by looking at the execution plans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply