lookup table - clustered index; primary key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

lookup table - clustered index; primary key

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-11-26 : 16:01:03

I have this table with over 20,000 unique rows in them (software names like 'Adobe Acrobat Reader xx.x'). I have the table scripted below. I want to query this table and get the DisplayNameID to query another table with 5 columns of ID numbers. Other queries may query the DisplayNameID field to get the DisplayName from it. I originally made a non-clustered primary key and a non-clustered index on DisplayName as can be seen. But I am not sure if that is the best way. I know there is disagreement, but I am looking for thoughts on this. Some of the tables on this database are hundreds of millions of rows, so I want to make this efficient. Here is the table script:

CREATE TABLE [dbo].[ARP_DisplayNames] (
[DisplayNameID] int IDENTITY(1, 1) NOT NULL,
[DisplayName] varchar(255) NULL,
CONSTRAINT [PK__ARP_Disp__AB444FF432767D0B]
PRIMARY KEY NONCLUSTERED ([DisplayNameID] ASC)
WITH ( PAD_INDEX = OFF,
FILLFACTOR = 100,
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
DATA_COMPRESSION = NONE )
 ON [PRIMARY]
)
ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE);
GO

CREATE NONCLUSTERED INDEX [idx_ARP_DisplayNames_DisplayName]
ON [dbo].[ARP_DisplayNames]
([DisplayName])
WITH
(
PAD_INDEX = OFF,
FILLFACTOR = 100,
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
DATA_COMPRESSION = NONE
)
ON [PRIMARY];
GO

With DisplayName being up to 255 characters (in practice 145), I was afraid it would be too long to make a good clustered index and the other column was just an identifying integer. So I am not sure which way to go.

Duane

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-26 : 16:15:38

Have you checked execution plans?

Correct--no point in clustering on the text, just cluster PK on the ID column. You would link to the ID column anyway, and the clustered index could also include the name column (thus creating a covering index)..but with only 20K rows, not sure that would make any difference since it is so small.

If you add the name field to the PK, it would effectively be a covering index ..but not sure of any performance gain with that few rows.

Also, should avoid putting table names as part of the column names. DisplayNameID should be in any table that needs to link to the DisplayName table, but the DisplayName ID column should simply be called "ID" or something without the actual table name included in the field name.

Poor planning on your part does not constitute an emergency on my part.

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-11-26 : 16:20:41

Thank you. Some excellent thoughts here.

Duane

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-29 : 19:23:26

Of course we would never use IDENTITY since it has no validation or verification. Why would a product name ever be NULL? There are bar codes on the boxes; sure makes a lot more sense than a count of the PHYSICAL insertion attempts to the disk. If you know the length of a string and allow more than is needed, you will get garbage.

I am still trying to make sense of "display_name_id"; it is not the name, it is an identifier for a name. An attribute can be a name or an id, but not some weird mix of both. Here is my guess at proper data element names and sizes:

CREATE TABLE ARP_DisplayNames
(upc CHAR(15) NOT NULL PRIMARY KEY,
product_name VARCHAR(150) NOT NULL);

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources