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

Question

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:

Creating a 4th column combining the 3 telephone columns. And, doing a search on the concatenated value.
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?

Thank you thunderblaster for editing!

RaviLobo
– RaviLobo

2014-03-09 03:33:18 +00:00
Commented Mar 9, 2014 at 3:33 — RaviLobo
– RaviLobo, Commented Mar 9, 2014 at 3:33

Gordon Linoff · Accepted Answer · 2014-03-06 02:38:02Z

3

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.

answered Mar 6, 2014 at 2:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RaviLobo Over a year ago

Thank you Gordon. It is a MS CRM database. It's COTS product. So, no scope for data model change.

Fuck StackOverflow · Accepted Answer · 2014-03-06 03:04:52Z

0

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

answered Mar 6, 2014 at 3:04

Fuck StackOverflow

5,0462 gold badges26 silver badges25 bronze badges

2 Comments

Fuck StackOverflow Over a year ago

Oh, and it goes without saying that you will have indexes on Telephone1, Telephone2, and Telephone3.

RaviLobo Over a year ago

Thank you DMason for the script.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related