Re: Index for username/password

Cecil (cecilkain0@xxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Login failed for user NT AUTHORITYNETWORK SERVICE
    ... having a bad day and isn't able to logon to something. ... It's seems difficult to find out which table the CRM app is trying to ... Then again, if you get a single login failure when you start SQL Server, ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: User ASPNET in SQL Server 2000
    ... and turn off anonymous access. ... a logon box will pop up if the user cannot ... >While I love integrated security in SQL Server, ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: User ASPNET in SQL Server 2000
    ... I want to logon to the virtual catalog from the internet ... as the windows user then I want to logon to the SQL Server ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Run As Command
    ... Declare @VCHCOMMAND Char, ... if you are using BACKUP DATABASE command to backup the database then ... to use a seperate logon for the destination server. ... to SQL server bacause SQL Server cannot see that domain. ...
    (microsoft.public.sqlserver.security)
  • Re: User ASPNET in SQL Server 2000
    ... I want to logon to the virtual catalog from the internet ... as the windows user then I want to logon to the SQL Server ...
    (microsoft.public.dotnet.framework.aspnet.security)