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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now