CODEWITHSUNDEEP
sqlsql-servert-sql
RaviLobo
RaviLobo

Reputation: 508

Fetching data from multiple columns of same type in SQL Server.

I have a SQL Server table with 3 similar columns: Telephone 1, Telephone 2, and Telephone 3. User will provide a telephone number, and SQL should fetch the data in the least possible time in an optimum way from one of the 3 columns. Telephone number can exist in any of the 3 columns.

I'm thinking of one or two options:

  1. Creating a 4th column combining the 3 telephone columns. And, doing a search on the concatenated value.

  2. May be a child table with only the 3 telephone columns with a CLUSTERED index.

Is there a better way? (Im sure there's one.) I know we can do a hash of 3 columns and do a faster search. I don't know much about hash. Has anyone worked on a similar situation?

Upvotes: 0

Views: 80

Answers (2)

Dave Mason
Dave Mason

Reputation: 4936

I totally agree with other answer(s) that involve normalizing the data. That is probably the best solution. However, if you are stuck with the existing data model, you could try a stored proc like the one below. I have assumed that you are looking for an exact match.

CREATE PROC FindPersons
    @PhoneNumber VARCHAR(16)
AS
BEGIN
--Create a temp table here with a column that matches the PK 
--of your main table (the one with the 3 phone number colums).
--I'll assume that a phone number search can return multiple rows.
CREATE TABLE #Persons (
    PersonId INT NOT NULL
)

--Just in case the temp table gets populated with a lot of records.
CREATE INDEX IDX_Persons_Id ON #Persons(PersonId)

INSERT INTO #Persons
SELECT pt.PersonId
FROM PersonsTable pt
WHERE pt.Telephone1 = @PhoneNumber

--If the above statement inserts zero rows,
--try again on the 2nd phone column.
--Depending on your business needs, you may
--want to run it regardless.
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO #Persons
    SELECT pt.PersonId
    FROM PersonsTable pt
    WHERE pt.Telephone2 = @PhoneNumber

    --If the above statement inserts zero rows,
    --try again on the 3rd phone column.
    --Depending on your business needs, you may
    --want to run it regardless.
    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO #Persons
        SELECT pt.PersonId
        FROM PersonsTable pt
        WHERE pt.Telephone3 = @PhoneNumber
    END
END

--Select data from the main table.
SELECT pt.*
FROM PersonsTable pt
--PK column from main table is indexed.  The join should perform efficiently.
JOIN #Persons p
    ON p.PersonId = pt.PersonId

END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270371

Well, you can do a search by doing:

where @USERNUMBER in (telephone1, telephone2, telephone3)

However, databases in general find it difficult to optimize such queries.

The right solution is to normalize the data. That is, create a new table, maybe called something like PersonTelephones that would have, among other columns, a PersonId, and a TelephoneNumber. Then, you are not limited to just one number.

This table can be indexes on the telephone numbers to optimize searches on the column.

Upvotes: 3

Related Questions