Question about the Sql server search algorithm

Hello,

My question is probably fairly easy for an experienced sql server person, but I have three parts to my question:

Suppose you have a database with 100,000 records in a table named ClientInformation.
One of the columns "ClientId" in ClientInformation is 4 characters long, VARCHAR(4). Each character position can contain a letter of the alphabet. ClientId has a unique key constraint on the column, so no duplicate Id's are allowed.
Below is sample of 4 records in this table, so you have a visual idea of what I am talking about.

ClientInformation

ClientId    FirstName    LastName
ADEF        Mary              Qyutiasil
SSXZ         Jimmy            Locosmith
TREF         Buck              Simpson
ARR        Zelfo              Zambrusa

So here is my question Part A. Before I insert a new record into this table, I need to validate that the ClientId is a unique value. What is the best way to do this in SQL?

Part B: How could I modify the sql in Answer A, in order to only search for records where the client ID begins with the letter A ?

Part B : Does anyone know about the internal search used in Sql server? How efficient is it? For example if I am searching specifically for the client ID "GRBB", how does the sql server search go about searching for this ClientId in the most effiecient way without having to search all of the one million records?
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
A: If you change the property of ClienId to UNIQUE, then the database will inform the user + plus will prevent duplicate entry. Make it unique and then you don't have to add any additional codes. In part C below, you read about index (which inherently all index fields are unique), so if you take care of index, making unique becomes irrelevant.

B: If it is not UNIQUE already, Try:
ALTER TABLE ClientInformation ADD CONSTRAINT  ClientIdUnique UNIQUE (ClientId)

C: On the search, The fields being searched need to be indexed. One million records may take up to 30 minutes to search and locate a record, but with index it needs no more than 32 tries (vs. 1,000,000 tries).  There are many different indexing techniques. The following link shows an example of one: http://en.wikipedia.org/wiki/B-tree

The code to add index is:
CREATE UNIQUE NONCLUSTERED INDEX (ClientIdIndex)
  ON dbo.ClientInformation(ClientId)

Mike
0
brgdotnetcontractorAuthor Commented:
No, you see a script will run, which will attempt to make inserts into the ClientId column. So a check for uniqueness needs to be performed using a sql statement.

In other words, nobody will manually do an insert into the ClientId column using query analyzer. It will all be done through a script run.
0
Dung DinhDBA and Business Intelligence DeveloperCommented:
Hi,

About part A and B,  you should use CONSTRAINT UNIQUE and use BEGIN TRY... CATCH... to catch error message while inserting data into ClientInformation table
IF OBJECT_ID('ClientInformation') IS NOT NULL
   DROP TABLE ClientInformation
GO   
CREATE TABLE ClientInformation (ClientID varchar(4),FirstName varchar(50),LastName varchar(50))
GO
ALTER TABLE ClientInformation ADD CONSTRAINT ClientID_Uniqueness UNIQUE(ClientID) 
GO
INSERT INTO ClientInformation
VALUES('ADEF','Mary','Qyutiasil'),
('SSXZ','Jimmy','Locosmith')

Open in new window


After that, if you want to validate  that the ClientId is a unique value, use BEGIN TRY... CATCH...
BEGIN TRY
    BEGIN TRANSACTION InsertClientID
    INSERT INTO ClientInformation
        VALUES('ADEF','Mary','Qyutiasil')
    COMMIT TRANSACTION InsertClientID
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
    ROLLBACK TRANSACTION InsertClientID
    
END CATCH;

Open in new window


If ClientID is duplicated, error message will be thrown
Msg 50000, Level 14, State 1, Line 18
Violation of UNIQUE KEY constraint 'ClientID_Uniqueness'. Cannot insert duplicate key in object 'dbo.ClientInformation'.


For Part C, the efficient way to search in SQL Database is to use Index structure.  SQL Server provides us an Index structure to arrange data orderly. Understand simply, you imagine that Index structure of SQL Engine likes Index pages of your book. Whenever, you want to read a page or any part of the book, you will use Index pages to know where you need to jump. Otherwise, if there is no Index pages, you must scan all of pages of your book to know where you want. Search algorithm of SQL Engine works like that. However, you have to create Index structure before SQL Engine can use it. For more detail here
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.