Question about the Sql server search algorithm
Posted on 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?