Question about the Sql server search algorithm

Posted on 2014-11-30
Last Modified: 2014-11-30

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.


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?
Question by:brgdotnet
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 230 total points
ID: 40472841
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:

The code to add index is:
  ON dbo.ClientInformation(ClientId)


Author Comment

ID: 40472908
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.

Accepted Solution

Dung Dinh earned 270 total points
ID: 40473009

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
CREATE TABLE ClientInformation (ClientID varchar(4),FirstName varchar(50),LastName varchar(50))
ALTER TABLE ClientInformation ADD CONSTRAINT ClientID_Uniqueness UNIQUE(ClientID) 
INSERT INTO ClientInformation

Open in new window

After that, if you want to validate  that the ClientId is a unique value, use BEGIN TRY... CATCH...
    INSERT INTO ClientInformation
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Add different cell to otherwise similiar row 4 38
migration MS SQL database to Oracle 30 59
VBScript Write Column Headers 3 35
insert wont work in SQL 14 19
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question