Multiple indexes using same column

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 2000 Forums

SQL Server Development (2000)

Multiple indexes using same column

Author

Topic

CtrlAltDel
Starting Member

17 Posts

Posted - 2004-06-09 : 12:52:22

I have a table defined something like this :

CREATE TABLE test (
site_ref VARCHAR(4) NOT NULL,
acc_no VARCHAR(8) NOT NULL,
payroll_no VARCHAR(7) NOT NULL,
ts_id INT NOT NULL,
we_date SMALLDATETIME NOT NULL
)

Now suppose my lookup tables were :

CREATE TABLE account (
site_ref VARCHAR(4) NOT NULL,
acc_no VARCHAR(8) NOT NULL,
acc_name VARCHAR(50) NOT NULL
)

CREATE TABLE payroll (
site_ref VARCHAR(4) NOT NULL,
payroll_no VARCHAR(7) NOT NULL,
payroll_name VARCHAR(50) NOT NULL
)

CREATE TABLE timesheet (
site_ref VARCHAR(4) NOT NULL,
ts_id INT NOT NULL,
we_date SMALLDATETIME NOT NULL,
hours NUMERIC(9, 6) NOT NULL
)

account has a composite primary key of site_ref and acc_no, payroll has a composite primary key of site_ref and payroll_no, and timesheet has a composite primary key of site_ref, ts_id and we_date. If I were to index test, so that each foreign key was indexed (so site_ref and acc_no in a composite index, site_ref and payroll_no, and site_ref, ts_id and we_date), would there be a performance penalty by having the test.site_ref field appearing in each index?

--
David Keaveny

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-09 : 13:22:36

Great post...I love when we get DDL..

I don't understand why site_ref is in the parental tables though...

But why not test it...


USE Northwind
GO

CREATE TABLE account99 (
	  site_ref VARCHAR(4) NOT NULL
	, acc_no VARCHAR(8) NOT NULL
	, acc_name VARCHAR(50) NOT NULL
	, PRIMARY KEY(site_ref,acc_no)
)
GO

CREATE TABLE test99 (
	  site_ref VARCHAR(4) NOT NULL
	, acc_no VARCHAR(8) NOT NULL
	, payroll_no VARCHAR(7) NOT NULL
	, ts_id INT NOT NULL
	, we_date SMALLDATETIME NOT NULL
	, PRIMARY KEY (site_ref, acc_no, payroll_no, ts_id, we_date)
	, FOREIGN KEY (site_ref, acc_no) REFERENCES account99(site_ref, acc_no)
)
GO

sp_help test99
GO
--[CTRL+K] to turn on Show Execution Plan

SELECT a.* FROM test99 t JOIN account99 a ON a.site_ref = t.site_ref AND a.acc_no = t.acc_no
GO

CREATE INDEX test99_ix1 ON test99(site_ref, acc_no)
GO  

SELECT a.* FROM test99 t JOIN account99 a ON a.site_ref = t.site_ref AND a.acc_no = t.acc_no
GO

--   Both are still an index scan 1st one because of the order of the columns
-- , second because there's no other predicate and it would still need to scan
--

DROP TABLE test99
DROP TABLE account99

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-09 : 13:31:51

Or we could go for some index intersection


USE Northwind
GO


CREATE TABLE account99 (
	  site_ref VARCHAR(4) NOT NULL
	, acc_no VARCHAR(8) NOT NULL
	, acc_name VARCHAR(50) NOT NULL
	, PRIMARY KEY(site_ref,acc_no)
)
GO

CREATE TABLE test99 (
	  site_ref VARCHAR(4) NOT NULL
	, acc_no VARCHAR(8) NOT NULL
	, payroll_no VARCHAR(7) NOT NULL
	, ts_id INT NOT NULL
	, we_date SMALLDATETIME NOT NULL
	, PRIMARY KEY (site_ref, acc_no, payroll_no, ts_id, we_date)
	, FOREIGN KEY (site_ref, acc_no) REFERENCES account99(site_ref, acc_no)
)
GO

CREATE TABLE payroll99 (
	  site_ref VARCHAR(4) NOT NULL
	, payroll_no VARCHAR(7) NOT NULL
	, payroll_name VARCHAR(50) NOT NULL
	, PRIMARY KEY (site_ref, payroll_no)
)
GO

CREATE INDEX test99_ix1 ON test99(acc_no)
CREATE INDEX test99_ix2 ON test99(payroll_no)
GO  

SELECT a.* 
  FROM test99 t 
  JOIN account99 a 
    ON a.site_ref = t.site_ref 
   AND a.acc_no = t.acc_no
  JOIN payroll99 p 
    ON p.site_ref = t.site_ref 
   AND p.payroll_no = t.payroll_no
 WHERE a.acc_no = '1'
   AND p.payroll_no = '1'
GO

DROP TABLE test99
DROP TABLE account99
DROP TABLE payroll99
GO

Brett

8-)

CtrlAltDel
Starting Member

17 Posts

Posted - 2004-06-10 : 07:03:40

quote:
I don't understand why site_ref is in the parental tables though...

Well the site_ref column indicates which server the data was originally received from (this database is an aggregate of data from our UK and Eire servers), so it has to appear in almost every table, as, for example, the same account number could be used in UK and Eire for different accounts.

Since site_ref is not at all selective (only 3 possible values ATM), I should properly have made it the last column in each index.

Hmm, I tried your index intersection idea, and if I leave site_ref out of test99_ix1 and test99_ix2, the QO does a clustered index seek on test99; however, if I add site_ref to the indexes, it uses a clustered index scan. To me, this suggests that I don't want site_ref in anything other than the primary key - am I reading this right?

--
"Life is like a sewer - what you get out of it depends on what you put into it"; Tom Lehrer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources