Solved

Question about the Sql server search algorithm

Posted on 2014-11-30
3
230 Views
Last Modified: 2014-11-30
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?
0
Comment
Question by:brgdotnet
3 Comments
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 230 total points
Comment Utility
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
 
LVL 2

Author Comment

by:brgdotnet
Comment Utility
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
 
LVL 7

Accepted Solution

by:
Dung Dinh earned 270 total points
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 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

9 Experts available now in Live!

Get 1:1 Help Now