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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 34

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 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