Hi need help to decide the indexers

Hello,

I have a situation here...

where we are inserting data of candidate...which includes fields...

1. First Name, Middle Name, Last Name ... (varchar(50))

2. Passport ID (no duplicates allowed but may be null )

3. Telephone number... ( may be null but no duplicates allowed )

4. Date of Birth ( mandatory )

5. Email id .......(no duplicates allowed but may be null )

The problem is that we already have about 1 lakh enteries in the database..

and it is expected to grow with time...

We need to define indexers such that it doesnot take time for searching or while inserting a new datafield into the database.

I hope that i am clear.

since, i suspect that that as it grows it will become more and more slow...!

This is database to store "resumes of different candidates".

So have decided...that

1. name 2. Passport no. 3. Email id: 4. Contact number

be the fields to prevent duplicacy of candidate data...

This involves checking for candidates existence every time anybody tries to make a new entry into database.

Please help to decide over the indexers...

Any good suggestions are welcomed...

Thanks for the reply...

\With regards,

Girish R. Pawar

You could easily have dupicates for Name. I would most certainly break the name into at least LastName and FirstName.

Two candidates 'could' be sharing a contact telephone number, so your 'no duplicates' constraint may be a problem.

I will often have an 'Age' field on the input form (BUT it isn't saved to the database) -it acts as a 'cross-check' against the DOB. DOB is so critical in some situations, we want to catch the obvious 'transpositions' immediately.

Your choices of indexes seem reasonable.

hi

can anyone tell me in what situation you should force a hash join or merge
join option in a query?
When it gives you better performance than the plan the optimizer choose, and
when you have done everything possible to encourage the optimizer to find
the best plan on its own, like building better indexes.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ukZYFz1HFHA.1172@.TK2MSFTNGP12.phx.gbl...
> can anyone tell me in what situation you should force a hash join or merge
> join option in a query?
>
>
|||Or updating your statistics with
EXEC sp_updatestats
However, make sure the actual execution is better than the plan offerred by
the Optimizer, not just the estimated plan.
Sincerely,
Anthony Thomas

"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u8iL$51HFHA.1396@.TK2MSFTNGP10.phx.gbl...
When it gives you better performance than the plan the optimizer choose, and
when you have done everything possible to encourage the optimizer to find
the best plan on its own, like building better indexes.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ukZYFz1HFHA.1172@.TK2MSFTNGP12.phx.gbl...
> can anyone tell me in what situation you should force a hash join or merge
> join option in a query?
>
>

hi

can anyone tell me in what situation you should force a hash join or merge
join option in a query?When it gives you better performance than the plan the optimizer choose, and
when you have done everything possible to encourage the optimizer to find
the best plan on its own, like building better indexes.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ukZYFz1HFHA.1172@.TK2MSFTNGP12.phx.gbl...
> can anyone tell me in what situation you should force a hash join or merge
> join option in a query?
>
>|||Or updating your statistics with
EXEC sp_updatestats
However, make sure the actual execution is better than the plan offerred by
the Optimizer, not just the estimated plan.
Sincerely,
Anthony Thomas
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u8iL$51HFHA.1396@.TK2MSFTNGP10.phx.gbl...
When it gives you better performance than the plan the optimizer choose, and
when you have done everything possible to encourage the optimizer to find
the best plan on its own, like building better indexes.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ukZYFz1HFHA.1172@.TK2MSFTNGP12.phx.gbl...
> can anyone tell me in what situation you should force a hash join or merge
> join option in a query?
>
>

hi

can anyone tell me in what situation you should force a hash join or merge
join option in a query?When it gives you better performance than the plan the optimizer choose, and
when you have done everything possible to encourage the optimizer to find
the best plan on its own, like building better indexes.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ukZYFz1HFHA.1172@.TK2MSFTNGP12.phx.gbl...
> can anyone tell me in what situation you should force a hash join or merge
> join option in a query?
>
>|||Or updating your statistics with
EXEC sp_updatestats
However, make sure the actual execution is better than the plan offerred by
the Optimizer, not just the estimated plan.
Sincerely,
Anthony Thomas
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u8iL$51HFHA.1396@.TK2MSFTNGP10.phx.gbl...
When it gives you better performance than the plan the optimizer choose, and
when you have done everything possible to encourage the optimizer to find
the best plan on its own, like building better indexes.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ukZYFz1HFHA.1172@.TK2MSFTNGP12.phx.gbl...
> can anyone tell me in what situation you should force a hash join or merge
> join option in a query?
>
>

Help: Selecting from two tables: odd situation

Hi, I have two tables setup as shown below:

Table name,"config"
+------+------+
| config_name | config_value |
+------+------+
| default_style | 1 |
| *other names | *other values |
+------+------+

Table name,"styles"
+------+------+
| style_id | style_name |
+------+------+
| 1 | Purple Hue |
| *other names | *other values |
+------+------+

Now, for my question:
I need to select everything (*) from the config table
and the 'style_name' from the styles table where style_id of the styles table is equal to the value of default_style of the config table.

I got as far as this query
"SELECT config.*, styles.style_name FROM `config`, `styles` WHERE config.default_style = styles.style_id LIMIT 1"

But obviously that will not work.

I know I can do this with two queries, but I am optimizing and I would like to reduce this down to 1 query, is this even possible to do with one query?

Thanks.left outer join|||Is this the same as a left join? I have heard of left join, but never of a left outer join.

Just an alias or what?|||yeah, it's the same

some databases allow the OUTER keyword to be optional

HELP...Splitting a string in T-SQL

I have a situation where I am querying the master.dbo.sysaltfiles to return
the path to the datafiles. What I am really interested in is the path...not
the filenames.
Example:
select @.DataPath = FileName From master.dbo.sysaltfiles WHERE name =
@.CurrentDB
This returns: M:\Microsoft SQL Server\CurrentDB.mdf
What I need is just the path:
Example:
M:\Microsoft SQL Server\
I was looking for something like VB's split string or something to remove
the filename.
What can I do to achieve the desired result?
Thanks!
RonThere are plenty of string functions in T-SQL that will help with this.
Check out REVERSE, CHARINDEX and SUBSTRING.
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:%23Yp4navNGHA.2884@.TK2MSFTNGP12.phx.gbl...
> I have a situation where I am querying the master.dbo.sysaltfiles to
> return the path to the datafiles. What I am really interested in is the
> path...not the filenames.
> Example:
> select @.DataPath = FileName From master.dbo.sysaltfiles WHERE name =
> @.CurrentDB
> This returns: M:\Microsoft SQL Server\CurrentDB.mdf
>
> What I need is just the path:
> Example:
> M:\Microsoft SQL Server\
> I was looking for something like VB's split string or something to remove
> the filename.
> What can I do to achieve the desired result?
>
> Thanks!
> Ron
>|||Excellent. If possible could you give me a sample of how to use them to
achieve what I'm going for?
Thanks,
Ron
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OY9JLgvNGHA.3984@.TK2MSFTNGP14.phx.gbl...
> There are plenty of string functions in T-SQL that will help with this.
> Check out REVERSE, CHARINDEX and SUBSTRING.
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:%23Yp4navNGHA.2884@.TK2MSFTNGP12.phx.gbl...
>|||Here is an example
declare @.c varchar(50)
select @.c ='M:\Microsoft SQL Server\CurrentDB.mdf'
select left(@.c,(len(@.C) -CHARINDEX('',reverse(@.c)))+1)
Just replace @.c with your field name
http://sqlservercode.blogspot.com/|||Thank you so much!
I'm under the gun so I needed to "learn" quickly. I appreciate your help!
Ron
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1140535404.793871.132130@.g47g2000cwa.googlegroups.com...
> Here is an example
> declare @.c varchar(50)
> select @.c ='M:\Microsoft SQL Server\CurrentDB.mdf'
> select left(@.c,(len(@.C) -CHARINDEX('',reverse(@.c)))+1)
> Just replace @.c with your field name
> http://sqlservercode.blogspot.com/
>|||Hi There,
I hope this help to solve the problem.
Select identity(int,1,1) Seq into Seq From sysobjects
Select * from seq
Select Substring(String,1,Max(seq)) From
(
Select * From Seq ,(
Select 'c:\aa\bb\cc\dd\ee' As String) S
Where substring(String,seq,1) = '\'
) SS Group By String
Drop Table Seq
With Warm regards
Jatinder Singh