CODEWITHSUNDEEP
sqlsql-serverperformancet-sqlindexing
c00000fd
c00000fd

Reputation: 22275

Does it matter for DB efficiency what number is SQL Server's index column?

I'm creating a new SQL Server 2008 database and I was always wondering, efficiency-wise, does it matter where I place the index column?

For instance, this:

--ID is primary key
CREATE TABLE tbl (ID INT, dtIn DATETIME2, dtOut DATETIME2, Type INT)
INSERT tbl VALUES
(1, '01:30', '02:00', 1),
(2, '02:30', '03:00', 1),
(3, '10:30', '11:00', 2)

CREATE INDEX idx_Type ON tbl(Type)

versus this:

--ID is primary key
CREATE TABLE tbl (ID INT, Type INT, dtIn DATETIME2, dtOut DATETIME2)
INSERT tbl VALUES
(1, 1, '01:30', '02:00'),
(2, 1, '02:30', '03:00'),
(3, 2, '10:30', '11:00')

CREATE INDEX idx_Type ON tbl(Type)

Upvotes: 0

Views: 109

Answers (2)

Sankara
Sankara

Reputation: 1479

Well , it sounds interesting, depends on how you implement

Single column index -it does not matter 

Multi-column index - order of the column does matter in the index,
                     but not in the table

Ben has a proof here

Upvotes: 1

Lloyd Banks
Lloyd Banks

Reputation: 36659

Nope, column placement in a table has nothing to do with the amount of resources used

Upvotes: 0

Related Questions