Clustering Key

Question

Clustering Key

VastSQL

SSC-Dedicated

Points: 36097
More actions
August 7, 2016 at 6:50 am

#328639

Have a table with below structure
Column_nameTypeLength
AGENT_ID float8
AGENT_CODEnvarchar510
ENGnvarchar510
we have a procedure which is the one that will be accessing this table by the column AGENT_CODE, the table is having a clustered index on column AGENT_CODE is that correct or its better to have a clustered index on AGENT_ID and a Non-Clustered Index on AGENT_CODE? AGENT_ID is having duplicate values.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

VastSQL (8/7/2016)
Have a table with below structure
Column_nameTypeLength
AGENT_ID float8
AGENT_CODEnvarchar510
ENGnvarchar510
we have a procedure which is the one that will be accessing this table by the column AGENT_CODE, the table is having a clustered index on column AGENT_CODE is that correct or its better to have a clustered index on AGENT_ID and a Non-Clustered Index on AGENT_CODE? AGENT_ID is having duplicate values.

Notwithstanding the fact that 510 characters is a very large 'code', I'd say that Agent_Code is the better CI, based on the information provided.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

Phil Parkin (8/7/2016)
VastSQL (8/7/2016)
Have a table with below structure
Column_nameTypeLength
AGENT_ID float8
AGENT_CODEnvarchar510
ENGnvarchar510
we have a procedure which is the one that will be accessing this table by the column AGENT_CODE, the table is having a clustered index on column AGENT_CODE is that correct or its better to have a clustered index on AGENT_ID and a Non-Clustered Index on AGENT_CODE? AGENT_ID is having duplicate values.
Notwithstanding the fact that 510 characters is a very large 'code', I'd say that Agent_Code is the better CI, based on the information provided.

Thanks Phil,

I have the same concern you pointed out ,AGENT_CODE is 510 character. If in future more procedures or code is accessing this table ,Do you think CI key should be changed?

The AGENT_CODE value have so far never gone beyond 5 characters where the dataype is nvarchar(255)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

VastSQL (8/7/2016)
Phil Parkin (8/7/2016)
VastSQL (8/7/2016)
Have a table with below structure
Column_nameTypeLength
AGENT_ID float8
AGENT_CODEnvarchar510
ENGnvarchar510
we have a procedure which is the one that will be accessing this table by the column AGENT_CODE, the table is having a clustered index on column AGENT_CODE is that correct or its better to have a clustered index on AGENT_ID and a Non-Clustered Index on AGENT_CODE? AGENT_ID is having duplicate values.
Notwithstanding the fact that 510 characters is a very large 'code', I'd say that Agent_Code is the better CI, based on the information provided.
Thanks Phil,
I have the same concern you pointed out ,AGENT_CODE is 510 character. If in future more procedures or code is accessing this table ,Do you think CI key should be changed?
The AGENT_CODE value have so far never gone beyond 5 characters where the dataype is nvarchar(255)

If you can, I would change the data type to something more reasonable. Does it really need to be NVARCHAR? Something like Varchar(10) sounds safe enough, given current usage (though, of course, you need to make sure that such a change is in line with business/application requirements, etc.)

If this table is frequently being accessed directly by Agent_Code, having Agent_Code as the CI makes sense.

What is your concern here?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 4

Phil Parkin (8/7/2016)
VastSQL (8/7/2016)
Phil Parkin (8/7/2016)
VastSQL (8/7/2016)
Have a table with below structure
Column_nameTypeLength
AGENT_ID float8
AGENT_CODEnvarchar510
ENGnvarchar510
we have a procedure which is the one that will be accessing this table by the column AGENT_CODE, the table is having a clustered index on column AGENT_CODE is that correct or its better to have a clustered index on AGENT_ID and a Non-Clustered Index on AGENT_CODE? AGENT_ID is having duplicate values.
Notwithstanding the fact that 510 characters is a very large 'code', I'd say that Agent_Code is the better CI, based on the information provided.
Thanks Phil,
I have the same concern you pointed out ,AGENT_CODE is 510 character. If in future more procedures or code is accessing this table ,Do you think CI key should be changed?
The AGENT_CODE value have so far never gone beyond 5 characters where the dataype is nvarchar(255)
If you can, I would change the data type to something more reasonable. Does it really need to be NVARCHAR? Something like Varchar(10) sounds safe enough, given current usage (though, of course, you need to make sure that such a change is in line with business/application requirements, etc.)
If this table is frequently being accessed directly by Agent_Code, having Agent_Code as the CI makes sense.
What is your concern here?

Thanks Phil,

Have already requested for datatype change with application team, AGENT_ID to in and AGENT_CODE to varchar(10). Datatype was the only concern and awaiting their response.