RE: Non Clustered Index on Phone column

Old Hand

Points: 309

August 18, 2018 at 5:30 am

#2002362

Eirikur Eiriksson - Saturday, August 18, 2018 5:14 AM
sunilkmr284 - Saturday, August 18, 2018 3:48 AM
Dear All,
I have a Lead table with below fields.
LeadId int (Not a primary key)
Telephone
...
...Table have 4 millions records
Client always search the data with the Telephone number.
I have created only Non clustered index on Telephone Number field.
Should I have also added the primary key constraint on leadId?
If I add the primary key on LeadId column, performance will be increase or not?
Can you post the full DDL (create table) script and information on what of the data you want to bring back and how the users will do the search?
😎
There is a big difference between handling small result sets and large result sets produced by very generic queries such as searching for anything starting with one or two digits.

USE ContactDB
GO

/****** Object: Table [dbo].[tbl_Lead] Script Date: 08/18/2018 16:56:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_Lead](
    [LeadID] [int] IDENTITY(1,1) NOT NULL,
    [Company Name] [varchar](100) NULL,
    [Title] [varchar](50) NULL,
    [First Name] [varchar](50) NULL,
    [Last Name] [varchar](50) NULL,
    [Address1] [varchar](100) NULL,
    [Address2] [varchar](100) NULL,
    [Address3] [varchar](100) NULL,
    [Town] [varchar](50) NULL,
    [County] [varchar](50) NULL,
    [PostCode] [varchar](20) NULL,
    [Telephone] [varchar](20) NULL,
    [Email] [varchar](150) NULL
) ON [PRIMARY]

SET ANSI_PADDING OFF
GO

Searching
CREATE PROCEDURE [dbo].[Lead_Get]
    @PhoneNo varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

select * from tbl_lead where Telephone=@PhoneNo
end