|
Posted by Erland Sommarskog on 12/28/05 16:10
Cecil (cecilkain0@yahoo.com) writes:
> Does this make sense for a logon table:
>
> CREATE TABLE Logon
> (
> ID INT NOT NULL IDENTITY PRIMARY KEY,
> name VARCHAR(15) NOT NULL,
> password VARCHAR(15) NOT NULL
> )
> GO
> CREATE UNIQUE INDEX IX_Logon_Name ON Logon(name)
> CREATE INDEX IX_Logon_NameAndPassword ON Logon(name,password)
> GO
>
> I do want the name to be unique but also will search frequently on both
> name & password. Is this how it should be done? I don't fully
> understand the difference between placing a single index in name &
> password VS one on both name & password.
I don't see the purpose of the ID column? Why not make the name the primary
key?
The index on (name, password) does not seem very useful here. Usually an
index on the form (uniquecolumn, othercolumn) is not meaningful, but it
can be sometimes, to achieved so-called covered queries. But as long as
the table does not have lots of other columns, it's difficult to see a
case for it here.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|