September 3, 2012 at 12:07 pm
here i am having two tables and now i just search a word
DECLARE @alubm table
(
alid int,
cattyp varchar(10),
altitle varchar(30),
crdate datetime
)
insert into @alubm
select 1,'cri','bowler',GETDATE() union all
select 2 ,'cri','batsman',GETDATE() union all
select 3 ,'cri','fielders',GETDATE()
DECLARE @images table
(
imageid int,
alid int,
imagedescription varchar(30),
filenameimage varchar(30)
)
insert into @images
select 1,1,'cri1','bowler1' union all
select 2,1,'cri2','bowler2' union all
select 3,1,'cri3','bowler3' union all
select 4,2 ,'crib1','batsman1' union all
select 5,2 ,'crib2','batsman2' union all
select 6,2 ,'crib3','batsman3' union all
select 7,3 ,'crif1','fielders1' union all
select 8,3 ,'crif2','fielders2'
declare @value varchar(800)='cri'
select
a.alid ,
a.cattyp ,
a.altitle,
a.crdate ,
b.imageid ,
b.filenameimage,
b.imagedescription
from
@alubm a
inner join
@images b
on
a.alid=b.alid
where
a.cattyp=@value
or
b.imagedescription=@value
or
a.altitle=@value
here i used split funtion keywords 'the cri word'
declare @value varchar(800)='the cri word'
function name is split it will split values
the
cri
word
this words should be checked one by one in the select query
September 3, 2012 at 12:47 pm
Sorry, what's the question?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 3, 2012 at 11:28 pm
here i am writing a search proc some people suggest me to use indexing if u use indexing mean u can reduce time when we 1 lakh data also it will redue time for executing in ashort period of time
September 4, 2012 at 2:46 am
Indexing can reduce execution time, if the correct indexes are chosen and the code is written so that it can use indexes.
Maybe start with these:
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2012 at 3:40 am
Not sure what your question is...but if you want to know what index is, where to use them and what benefits they offer....check the following link
http://www.sqlservercentral.com/stairway/72399/
~ Lokesh Vij
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
September 4, 2012 at 8:35 am
If performance is a concern you should take a look at the link in my signature about splitting strings. Depending on what you are using for a splitter it may provide significant performance improvement.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 4, 2012 at 8:58 am
Sean Lange (9/4/2012)
If performance is a concern you should take a look at the link in my signature about splitting strings. Depending on what you are using for a splitter it may provide significant performance improvement.
Last I saw he was using the DelimitedSplit8K function.
September 4, 2012 at 9:00 am
Lynn Pettis (9/4/2012)
Sean Lange (9/4/2012)
If performance is a concern you should take a look at the link in my signature about splitting strings. Depending on what you are using for a splitter it may provide significant performance improvement.Last I saw he was using the DelimitedSplit8K function.
Hard to tell with so many posts all on the same topic. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 4, 2012 at 9:03 am
Sean Lange (9/4/2012)
Lynn Pettis (9/4/2012)
Sean Lange (9/4/2012)
If performance is a concern you should take a look at the link in my signature about splitting strings. Depending on what you are using for a splitter it may provide significant performance improvement.Last I saw he was using the DelimitedSplit8K function.
Hard to tell with so many posts all on the same topic. :w00t:
Basing it off his latest thread, you know the one.
September 4, 2012 at 11:32 am
hi
Sean Lange
and
Lynn Pettis
i know iam not worth for anything thats why spliting the proc also
i dont even no what is index but i used to create a table i used to add constraint for for primary key and a forign key but i search for index in google there iam having only how to create index
CREATE INDEX index_name
ON table_name (column_name1,column_name2...);
like this but i dont now how to use index that s i declare 2 table and i asked how to search a word using index
September 4, 2012 at 11:36 am
sivajii (9/4/2012)
i dont even no what is index
Did you read the articles I linked?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2012 at 12:00 pm
it read this
http://www.sqlservercentral.com/articles/Indexing/68439/
here i didn't understand
if there is an simple example means i can i understand easily
select * from sys.indexes
select * from sys.index_columns
any way thnks for proving related article
GilaMonster
September 4, 2012 at 12:09 pm
sivajii (9/4/2012)
it read thishttp://www.sqlservercentral.com/articles/Indexing/68439/
here i didn't understand
if there is an simple example means i can i understand easily
select * from sys.indexes
select * from sys.index_columns
any way thnks for proving related article
GilaMonster
Not sure what your question is. The article by Gail is a great article about the basics of indexing in sql server. But you posted queries against against system views. Those views explain the indexes and how they are defined. You can't possibly begin to digest and understand what this stuff means when you don't understand the basics.
You need to understand what an index is and what it does for you before you look at details about how they are defined. This is like trying to perform complex algebra before you learn addition and subtraction.
I know we have a language barrier, we can work with that but we don't even know what the question is.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 4, 2012 at 12:14 pm
Sean Lange (9/4/2012)
sivajii (9/4/2012)
it read thishttp://www.sqlservercentral.com/articles/Indexing/68439/
here i didn't understand
if there is an simple example means i can i understand easily
select * from sys.indexes
select * from sys.index_columns
any way thnks for proving related article
GilaMonster
Not sure what your question is. The article by Gail is a great article about the basics of indexing in sql server. But you posted queries against against system views. Those views explain the indexes and how they are defined. You can't possibly begin to digest and understand what this stuff means when you don't understand the basics.
You need to understand what an index is and what it does for you before you look at details about how they are defined. This is like trying to perform complex algebra before you learn addition and subtraction.
I know we have a language barrier, we can work with that but we don't even know what the question is.
The question goes back to this thread, http://www.sqlservercentral.com/Forums/Topic1353168-391-1.aspx, where he is trying to write and improve a search routine. The problem I see is that no index will help since the search criteria starts with a wildcard (%).
What it looks like is needed is full text indexing.
September 4, 2012 at 12:24 pm
Lynn Pettis (9/4/2012)
Sean Lange (9/4/2012)
sivajii (9/4/2012)
it read thishttp://www.sqlservercentral.com/articles/Indexing/68439/
here i didn't understand
if there is an simple example means i can i understand easily
select * from sys.indexes
select * from sys.index_columns
any way thnks for proving related article
GilaMonster
Not sure what your question is. The article by Gail is a great article about the basics of indexing in sql server. But you posted queries against against system views. Those views explain the indexes and how they are defined. You can't possibly begin to digest and understand what this stuff means when you don't understand the basics.
You need to understand what an index is and what it does for you before you look at details about how they are defined. This is like trying to perform complex algebra before you learn addition and subtraction.
I know we have a language barrier, we can work with that but we don't even know what the question is.
The question goes back to this thread, http://www.sqlservercentral.com/Forums/Topic1353168-391-1.aspx, where he is trying to write and improve a search routine. The problem I see is that no index will help since the search criteria starts with a wildcard (%).
What it looks like is needed is full text indexing.
I agree with full text indexing as the most likely viable solution.
I know that in one of the threads on this topic Gail suggested reading her article about catch-all queries. This is a classic example of why we don't like multiple posts on the same topic. Nobody can keep it all straight, your answers and responses are all over the place.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply