Two int fields or one varchar field

[Mr Black]

I am setting up a database that will receive a lot of data from two
separate telephone centers, the log table will in a short time have
over 1 million lines, and I was wondering if I should use 1 identify
field or two:

case 1:
[Id] [int] IDENTITY (1, 1) NOT NULL
[ServerId] [int] NOT NULL

case 2:
[Id] [varchar(20)] IDENTITY NOT NULL

Where in case 1 I would just use a combination of Id and ServerId to
identify the line, where in case 2 I would have the Id field a varchar
that would look something like A-000001, A-000002 for server 1 and
B-000001, B-000002 for server 2

Which solution will be faster when searching for a record when the
will have over 1 million lines?

[David Portas]

This is possibly the wrong question to ask. In both cases your number
appears to be just an arbitrary, internally generated code, so why would you
actually *want* to search on it if it doesn't contain any useful
information?

In general, if the cardinality of the column is the same in both cases it
should be faster to search on a NUMERIC or INTEGER than a VARCHAR of a
similar size.

--
David Portas
------------
Please reply only to the newsgroup
--

[David Portas]

Re-reading your question, I think you really wanted to know about searching
on the server column. I suggest you put the server id in its own column.
This is good DB design and is easier than trying to parse it out from the
number.

--
David Portas
------------
Please reply only to the newsgroup
--

[Greg D. Moore \(Strider\)]

"David Portas" <RE****************************@acm.org> wrote in message
news:Aa********************@giganews.com...

This is possibly the wrong question to ask. In both cases your number
appears to be just an arbitrary, internally generated code, so why would you actually *want* to search on it if it doesn't contain any useful
information?

In general, if the cardinality of the column is the same in both cases it
should be faster to search on a NUMERIC or INTEGER than a VARCHAR of a
similar size.

I'll also point out that 1 million rows isn't that large of a DB for SQL
Server anyway and sorting should be fairly quick in either case.

--
David Portas
------------
Please reply only to the newsgroup
--