Tuesday, March 27, 2012
general comment is required
I am trying to implement master detail kind of functionality. table1 has
primary key t1 and table2 has primary key t2. I have not placed any
relationship on these tables. these tables are free tables.
what will happen if I place foreign key will search sort become faster ?
assume data is perfect with/without relationship, so main purpose of having
foreign key relationship is almost equall to 0.
your comments are required.
Kishorkishor (kishor@.discussions.microsoft.com) writes:
> I am trying to implement master detail kind of functionality. table1 has
> primary key t1 and table2 has primary key t2. I have not placed any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become faster ?
> assume data is perfect with/without relationship, so main purpose of
> having foreign key relationship is almost equall to 0.
You don't add foreign keys fort sorts to make faster (and I find it
difficult a imagine where an FK would affect the speed of a sort). You
add foreign-key constraints to enforce referential integrity.
If you have, say, an Orders and an OrderDetails table, the Order table
would have OrderID as its primary key, and OrderDeatails would have a
two-column of OrderID and some other column (e.g. RowNo or ProductID).
Furthermore OrderDetails would have a FK constraint to say that any
OrderID must be an existing OrderID in Orders. This constraint applies
both when inserting into OrderDetails and when deleting from Orders.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, there will be a difference in performance.
BOL states that a FK relationship b/w 2 tables indicates that the 2 tables
have been optimized to be combined in a query that uses the keys as its
criteria.
Ideally, u shud maintain an index on the FK columns for even better
performance of ur queries. I doubt if the index is maintained in the 2 cases
mentioned (with & without FK), will there be any speed difference or not?
Probably, not.
- R
"kishor" wrote:
> Hi,
> I am trying to implement master detail kind of functionality. table1 has
> primary key t1 and table2 has primary key t2. I have not placed any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become faster ?
> assume data is perfect with/without relationship, so main purpose of havin
g
> foreign key relationship is almost equall to 0.
> your comments are required.
> Kishor
>
>|||If a FK relationship makes sense there is always a benefit for using it
and keeping therefore referential integrity, but just using a FK does
NOT mean do use an index. FK can be also place on columns which aren=B4t
indexed, so you should be aware not to mix that up. But I hope that you
are in the design phase and not the reengineering phase, because your
thoughts should take place in the designing phase.
HTH, jens Suessmeyer.|||kishor wrote:
> Hi,
> I am trying to implement master detail kind of functionality. table1 has
> primary key t1 and table2 has primary key t2. I have not placed any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become faster ?
> assume data is perfect with/without relationship, so main purpose of havin
g
> foreign key relationship is almost equall to 0.
> your comments are required.
> Kishor
You say you assume the data is perfect but how will you enforce that
rule without a foreign key? If you enforce it only in your application
then you have to duplicate that integrity feature for each application
or data entry screen. A bug in any one place could affect all other
users of the data. When you come to write queries against the data, the
server won't see a referential integrity constraint so it won't be able
to use that constraint to help construct a better query plan.
Always declare the constraints that are required to enforce your
business rules.
David Portas
SQL Server MVP
--|||"kishor" <kishor@.discussions.microsoft.com> wrote in message
news:D8FDB80A-F7E6-4600-8BED-B20A80CD5815@.microsoft.com...
> Hi,
> I am trying to implement master detail kind of functionality.
table1 has
> primary key t1 and table2 has primary key t2. I have not placed
any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become
faster ?
> assume data is perfect with/without relationship, so main purpose
of having
> foreign key relationship is almost equall to 0.
> your comments are required.
> Kishor
>
kishor,
Establishing a foreign key constraint is done for the purpose of
enforcing referential integrity. These constraints should always
been done where cardinality indicates it. Referential integrity is
one of the key features of an RDBMS, avoiding its use would be
substantially less than ideal (I would almost go so far as to say,
"Why have an RDBMS if you don't bother with referential
integrity?").
The foreign key constraint may be enforced by the use of an index
(in SQL Server, it usually is). Indexes can improve performance on
some types of queries (SELECT), and the query optimizer may or may
not use that index on those queries depending on *many* other
factors. Indexes may degrade performance on some types of queries
(UPDATE, INSERT, DELETE). But these considerations are entirely
beside the consideration of enforcing referential integrity.
RI provides for database consistency. Without it, your database
may, without any warning, enter an "inconsistent state". If you
encounter such an error in your database(quite likely without
enforced RI, IMO), I wish you luck in fixing it.
Sincerely,
Chris O.
Friday, March 9, 2012
function naming and execution
I am converting a database from Oracle to SQL-Server 2005 and in that context I needed to implement a function in the database. My database default uses the dbo schema and the function is there too. I define a function called "Translate" and wanted to use it like this :
SELECT
translate(Phonenumber, '0123456789 ()+-', '0123456789') as Phonenumber
FROM
Customer
but what pussled my was that in order to make this work I had to prefix the translate method with "dbo." like this :
SELECT
dbo.translate(Phonenumber, '0123456789 ()+-', '0123456789') as Phonenumber
FROM
Customer
as all "objects" I'm using resides in the dbo schema - why must I prefiks only the function and not the table?Yes, functions will defintely need the owner / schema prefix.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
function index
Thank,
dcExplain more clearly what you want to do.|||You have to create a computed column with the function you want indexed, then index the computed column. Then you have to change all of your applications to query the new column. Simple, eh?
In short, Function Based Indexes don't really exist in SQL Server, but they are pretty nice things to have.
create index ind on table (upper(name))|||Function based indicies (where the indexed value is the result of one or more function calls) were the most dearly missed feature of Clipper when I converted to SQL Server. They are lovely things when you don't have to deal with most of the complexities that a real relational database engine is intended to solve!
Unfortunately, they don't scale, introduce all kinds of "functional dependancies", lead to really interesting collation questions, etc. A Function Based Index is certainly convenient in many ways, but it is a scaling/supportability nightmare in search of its next victim!
-PatP
Friday, February 24, 2012
FullText Search SQLserver2005 using VS2005 Server Explorer
Hello,
I am using SQLServer Express and Visual Studio 2005 to create the website. I would like to implement FullText search, but have never done it before. I have looked at the msdn documentation on FullText search in SQLServer 2005 herehttp://msdn2.microsoft.com/en-us/library/ms142519.aspx.
I cannot seem to figure out how to use a FT search using Visual Studio. Can someone please help me configure my database and then explain how I can run queries based on user input to return data?
Hi KJAK,
KJAK:
I am using SQLServer Express and Visual Studio 2005 to create the website.
In the SQL Express Edition, you have to do some more work the in the other SQL editions.
KJAK:
I cannot seem to figure out how to use a FT search using Visual Studio.
Use the Sql Server Management Studio Express. Open the Template Explorer, select the Full-text folder and select theCreate Full-text Index template. Fill in the needed information and run it. Then select theCreate Full-text Catalog template and do the same.
BTW. Sql-BOL is your friend
|||
What I have now figured out is that I will not be able to implement FULL-TEXT search on this particular site. I am using a shared hosting provider and they do not provide me access via the Sql Server Management Studio Express because they do not permit remote connections. I have some documentation that may help me get around this issue to configure the database, but since the database is provided by the host, it may not work.
I have marked your reply as answered as a way to thank you for your help and will keep this post in mind when I am working with dedicated server websites.
Thanks for your reply!
KJAK
KJAK:
I have some documentation that may help me get around this issue to configure the database, but since the database is provided by the host, it may not work.
Is your DataBase transmitted to the ISP sql server, or its a dynamically attached DataBase in your App_Data folder?
In the second case, AFAIK Full-text search wount works, because you need access to the Fulltext-Catalog directory to populate your index.
|||
The database is located on the hosts network. They provide a client that I connect to in which I can define tables, stored procedures, schema's, et cetera. It does not provide me access to the catalogs and is limited in what I can do all together. I have not yet spoken to the host to see if they can explain how to do it or whether I can do it or not.
|||
KJAK:
It does not provide me access to the catalogs and is limited in what I can do all together.
You may change to a ISP they will give you a dedicated SQL server. Or live with this restrictions
FullText Search on Multiple Language
I am trying to implement a FullText search for a table which contains
translations for many different languages. However, different languages
utilized different word breaker for the FullText search to work properly for
that language but I can only assign a single word breaker to a table (for
example, traditional chinese uses Chinese(Taiwan) word breaker, simplified
chinese uses Chinese(PRC), etc).
To overcome this problem, I used horizontal partitioning to split the
table into smaller tables according to its various languages; each assigned
with the word breader for that language. And I try to insert, update, delete
the data in all the tables thru a view which union all the smaller tables.
This seems to work well.
Next, I created a storeprocedure to do a full-text search by searching
each smaller table and appending the result.to a temp table
Example
CREATE TABLE #Temp
(
searchresult ntext
)
INSERT INTO #Temp (searchresult)
SELECT translation FROM translation_german WHERE CONTAINS(*, @.searchstring)
INSERT INTO #Temp (searchresult)
SELECT * FROM translation_japanese WHERE CONTAINS(*, @.searchstring)
INSERT INTO #Temp (searchresult)
SELECT * FROM translation_chinese WHERE CONTAINS(*, @.searchstring)
:
The problem is when I do a full-text search for a chinese string in the
table with german word break, it will give the error#7619 ('A clause of the
query contained only ignored words.') and terminate the storeprocedure
immediately. There doesn't seem to be anyway to ignore the error and
continue to the search in the next table(s).
1. Does anyone has anyway to handle the error#7619 in such a way that it
will not terminate the Storeprocedure but continue to search the next table?
OR
2. Is way any other method to implement a full-text search for a table
containing multiple languages (with word breaker correctly implemented)
other then the one described above?
Many thanks in advance
Royston
Royston,
Could you post the full output of -- SELECT @.@.version -- as this would be
most helpful in understanding your environment and providing you with
answers.
First of all, and assuming (for now) that you're using SQL Server 2000, you
do not need to split your table into multiple smaller tables, (one for each
language), as SQL Server 2000 supports multiple collation per column and SQL
Full Text Indexing supports different "Languages for Word Breaker" per
column. Therefore, you can have one column per language and set the
collation and "Language for Word Breaker" at a per column level in one
table. This is one reason I'm requesting the @.@.version info.
In regards to error 7619, when FT Searching for a Chinese string in the
table with German word break, you will need to capture the language before
you issue the FTS query and then run it against the appropriate table (or
column) to avoid this error. Note, that in SQL Server 2005 (currently, still
in beta), you will be able to store multiple languages in one column, and
then issue language specific queries based upon the LCID of the language.
Just curious, why are you running a FT Search query for a Chinese string in
the table with German?
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Royston" <royston@.earth9.com> wrote in message
news:euxMNcUKFHA.2212@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> I am trying to implement a FullText search for a table which contains
> translations for many different languages. However, different languages
> utilized different word breaker for the FullText search to work properly
for
> that language but I can only assign a single word breaker to a table (for
> example, traditional chinese uses Chinese(Taiwan) word breaker, simplified
> chinese uses Chinese(PRC), etc).
> To overcome this problem, I used horizontal partitioning to split the
> table into smaller tables according to its various languages; each
assigned
> with the word breader for that language. And I try to insert, update,
delete
> the data in all the tables thru a view which union all the smaller tables.
> This seems to work well.
> Next, I created a storeprocedure to do a full-text search by searching
> each smaller table and appending the result.to a temp table
> Example
> CREATE TABLE #Temp
> (
> searchresult ntext
> )
> INSERT INTO #Temp (searchresult)
> SELECT translation FROM translation_german WHERE CONTAINS(*,
@.searchstring)
> INSERT INTO #Temp (searchresult)
> SELECT * FROM translation_japanese WHERE CONTAINS(*, @.searchstring)
> INSERT INTO #Temp (searchresult)
> SELECT * FROM translation_chinese WHERE CONTAINS(*, @.searchstring)
> :
> The problem is when I do a full-text search for a chinese string in
the
> table with german word break, it will give the error#7619 ('A clause of
the
> query contained only ignored words.') and terminate the storeprocedure
> immediately. There doesn't seem to be anyway to ignore the error and
> continue to the search in the next table(s).
> 1. Does anyone has anyway to handle the error#7619 in such a way that
it
> will not terminate the Storeprocedure but continue to search the next
table?
> OR
> 2. Is way any other method to implement a full-text search for a table
> containing multiple languages (with word breaker correctly implemented)
> other then the one described above?
> Many thanks in advance
> Royston
>
>
>
>
|||Hi John,
Yes, I am using SQL Server 2000. I am currently using a single column to
store all the various translations, therefore I need to do a FT search for a
Chinese string in the German table (actually, all the smaller tables). The
logic for insertion, update, and delete is simpler with this method (normal
query thru a view will do). Adding of new language requires only addition of
a new smaller table and a quick update to the view. There's no change to the
existing storeprocedures as the storeprocedures access the translation thru
a view.
Here's my simplified sample schema,
Create Table [English_Table]
(
string_id int primary key,
english_text ntext,
lastupdated_by int,
lastupdated_on datetime,
search_id uniqueidentifier not null Unqiue
)
Create Table [Translation_German_Table]
(
string_id int,
culture varchar(5),
translation ntext,
lastupdated_by int,
lastupdated_on datetime,
search_id uniqueidentifier not null Unqiue
Constraint pk_german Primary Key (string_id., culture)
Check (culture = 'de-DE')
)
Create Table [Translation_Chinese_Table]
(
string_id int,
lang_id varchar(5),
translation ntext,
lastupdated_by int,
lastupdated_on datetime,
search_id uniqueidentifier not null Unqiue
Constraint pk_chinese Primary Key (string_id, culture)
Check (culture = 'zh-CN')
)
Create View [Translation]
(
Select * From [Translation_German_Table]
Union All
Select * From [Translation_Chinese_Table]
)
Storing each language per column will require more complex logic on the
Client side to determine the current language of the translation and to
insert into/ update the appropriate column (addition of new language will
require adding a new column and may also require changes to existing logic).
I will still have to check the language of the search string to implement
the FT search on the correct column (this may pose a problem if the search
string is user input and I don't know what language the user is inputing).
Thanks,
Royston
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OENJA9XKFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Royston,
> Could you post the full output of -- SELECT @.@.version -- as this would be
> most helpful in understanding your environment and providing you with
> answers.
> First of all, and assuming (for now) that you're using SQL Server 2000,
you
> do not need to split your table into multiple smaller tables, (one for
each
> language), as SQL Server 2000 supports multiple collation per column and
SQL
> Full Text Indexing supports different "Languages for Word Breaker" per
> column. Therefore, you can have one column per language and set the
> collation and "Language for Word Breaker" at a per column level in one
> table. This is one reason I'm requesting the @.@.version info.
> In regards to error 7619, when FT Searching for a Chinese string in the
> table with German word break, you will need to capture the language before
> you issue the FTS query and then run it against the appropriate table (or
> column) to avoid this error. Note, that in SQL Server 2005 (currently,
still
> in beta), you will be able to store multiple languages in one column, and
> then issue language specific queries based upon the LCID of the language.
> Just curious, why are you running a FT Search query for a Chinese string
in[vbcol=seagreen]
> the table with German?
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
>
> "Royston" <royston@.earth9.com> wrote in message
> news:euxMNcUKFHA.2212@.TK2MSFTNGP12.phx.gbl...
contains[vbcol=seagreen]
> for
(for[vbcol=seagreen]
simplified[vbcol=seagreen]
the[vbcol=seagreen]
> assigned
> delete
tables.[vbcol=seagreen]
searching[vbcol=seagreen]
> @.searchstring)
> the
> the
> it
> table?
table
>
|||Royston, I am not sure what you suggest would work.
First, I don't think you can do Full text search on view, in other word, do
select * from View1 where contains (data,'some search string')
Second, you would still have to choose ONE word breaker on the indexed data
column, so if you have Germany and Chinese in the same column, What word
breaker can you choose?
I am running the similar problem, in my case, I want to full text search on
chinese and english,but struggling to find a solution for it.
Any suggest are welcome.
--Xin Chen
"Royston" <royston@.earth9.com> wrote in message
news:uspzi$cKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Hi John,
> Yes, I am using SQL Server 2000. I am currently using a single column
to
> store all the various translations, therefore I need to do a FT search for
a
> Chinese string in the German table (actually, all the smaller tables). The
> logic for insertion, update, and delete is simpler with this method
(normal
> query thru a view will do). Adding of new language requires only addition
of
> a new smaller table and a quick update to the view. There's no change to
the
> existing storeprocedures as the storeprocedures access the translation
thru
> a view.
> Here's my simplified sample schema,
> Create Table [English_Table]
> (
> string_id int primary key,
> english_text ntext,
> lastupdated_by int,
> lastupdated_on datetime,
> search_id uniqueidentifier not null Unqiue
> )
> Create Table [Translation_German_Table]
> (
> string_id int,
> culture varchar(5),
> translation ntext,
> lastupdated_by int,
> lastupdated_on datetime,
> search_id uniqueidentifier not null Unqiue
> Constraint pk_german Primary Key (string_id., culture)
> Check (culture = 'de-DE')
> )
> Create Table [Translation_Chinese_Table]
> (
> string_id int,
> lang_id varchar(5),
> translation ntext,
> lastupdated_by int,
> lastupdated_on datetime,
> search_id uniqueidentifier not null Unqiue
> Constraint pk_chinese Primary Key (string_id, culture)
> Check (culture = 'zh-CN')
> )
> Create View [Translation]
> (
> Select * From [Translation_German_Table]
> Union All
> Select * From [Translation_Chinese_Table]
> )
> Storing each language per column will require more complex logic on
the
> Client side to determine the current language of the translation and to
> insert into/ update the appropriate column (addition of new language will
> require adding a new column and may also require changes to existing
logic).[vbcol=seagreen]
> I will still have to check the language of the search string to implement
> the FT search on the correct column (this may pose a problem if the search
> string is user input and I don't know what language the user is inputing).
> Thanks,
> Royston
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OENJA9XKFHA.3336@.TK2MSFTNGP10.phx.gbl...
be[vbcol=seagreen]
> you
> each
> SQL
before[vbcol=seagreen]
(or[vbcol=seagreen]
> still
and[vbcol=seagreen]
language.[vbcol=seagreen]
string[vbcol=seagreen]
> in
> contains
languages[vbcol=seagreen]
properly[vbcol=seagreen]
> (for
> simplified
> the
> tables.
> searching
in[vbcol=seagreen]
of[vbcol=seagreen]
that[vbcol=seagreen]
> table
implemented)
>
|||Hi Xin Chen,
No, I am not doing the FT search on the view. My solution is to utilize
horizontal partitioning to split my translation table into multiple child
tables each storing only a particular language, and then assign a different
word breaker to each of the child table according to the language it is
storing. I am using a view to union all the child tables so that I can
access all the child tables thru the view as though it is a single table
(that is, I can insert, update, delete via the view instead of referencing
the child tables). Note that I implemented a check on the primary key column
of each of the child table, this will allow the view to know where to
insert/ update or delete the row referenced in the view. I have tried this
and it seems to work.
However, to implement FT search, I created a temporary table and for each
child table I will do a FT search with the search string and append the
result to the temporay table (like the example below). The problem I
encountered is if the search string contain a particular language string
like chinese string, and the storeprocedure is doing a FT search on a child
table with german word breaker, the storeprocedure will generate an error
#7619 ('A clause of the query contained only ignored words.') and terminate
execution. There doesn't seem to be any way I can catch the exception in the
storeprocedure so as to ignore the error and continue the search on the next
child table (that is, the one with the correct word breaker). As suggested
by John, it seems the only way to resolve this is to check the language of
the search string beforehand somehow and direct the FT search to the
respective child table. This shall work, but I am hoping if anyone know how
to catch the error#7619 exception and prevent the storeprocedure from
terminating since it may be difficult to determine the language of the
search string without restricting the user input.
Simplified Sample Code Example,
CREATE TABLE #Temp
(
searchresult ntext
)
INSERT INTO #Temp (searchresult)
SELECT translation FROM translation_german WHERE CONTAINS(*,@.searchstring)
INSERT INTO #Temp (searchresult)
SELECT * FROM translation_japanese WHERE CONTAINS(*, @.searchstring)
INSERT INTO #Temp (searchresult)
SELECT * FROM translation_chinese WHERE CONTAINS(*, @.searchstring)
Regards,
Royston
"Xin Chen" <xchen@.xtremework.com> wrote in message
news:eNm4kjfKFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Royston, I am not sure what you suggest would work.
> First, I don't think you can do Full text search on view, in other word,
do
> select * from View1 where contains (data,'some search string')
> Second, you would still have to choose ONE word breaker on the indexed
data
> column, so if you have Germany and Chinese in the same column, What word
> breaker can you choose?
> I am running the similar problem, in my case, I want to full text search
on[vbcol=seagreen]
> chinese and english,but struggling to find a solution for it.
> Any suggest are welcome.
> --Xin Chen
> "Royston" <royston@.earth9.com> wrote in message
> news:uspzi$cKFHA.1284@.TK2MSFTNGP14.phx.gbl...
column[vbcol=seagreen]
> to
for[vbcol=seagreen]
> a
The[vbcol=seagreen]
> (normal
addition[vbcol=seagreen]
> of
> the
> thru
> the
will[vbcol=seagreen]
> logic).
implement[vbcol=seagreen]
search[vbcol=seagreen]
inputing).[vbcol=seagreen]
> be
2000,[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
> before
> (or
> and
> language.
> string
> languages
> properly
split[vbcol=seagreen]
update,[vbcol=seagreen]
> in
> of
storeprocedure[vbcol=seagreen]
> that
next
> implemented)
>
Full-text search for a new language (MSSQL Server 2005)
I want to implement a full-text search support for texts of my native
language in MS SQL Server 2005.
But my language (Ukrainian) is not supported. So i would like to implement a
module for this language by myself.
Unfortunately i've failed to find what API i have to implement. Can anybody
help me?
--
Tkx.Hi
Ask this in the SQL Server 2005 newsgroups:
http://communities.microsoft.com/ne...p=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ihor Orobchuk" wrote:
> Hi gurus.
> I want to implement a full-text search support for texts of my native
> language in MS SQL Server 2005.
> But my language (Ukrainian) is not supported. So i would like to implement
a
> module for this language by myself.
> Unfortunately i've failed to find what API i have to implement. Can anybod
y
> help me?
> --
> Tkx.
>
>|||Will Russian work for you? I realize they aren't the same language but they
have a common root and a lot of the language rules are the same (from what I
understand).
Other than that check out
http://msdn.microsoft.com/library/d...
efint_4b76.asp
and
http://msdn.microsoft.com/library/d...
efint_4b76.asp
and
http://msdn.microsoft.com/library/d...enario_1bg3.asp
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ihor Orobchuk" <Ihor.Orobchuk@.eleks.com> wrote in message
news:euC$51YoFHA.3256@.tk2msftngp13.phx.gbl...
> Hi gurus.
> I want to implement a full-text search support for texts of my native
> language in MS SQL Server 2005.
> But my language (Ukrainian) is not supported. So i would like to implement
> a module for this language by myself.
> Unfortunately i've failed to find what API i have to implement. Can
> anybody help me?
> --
> Tkx.
>|||Ihor,
While Ukrainian is not (yet?) a supported FTS language (a subset of
languages supported by SQL Server), the following is a list of supported FTS
languages (so far) in SQL Server 2005 Beta3:
select @.@.version -- SQL Server 2005 - 9.00.1187.07
select * from Sys.Fulltext_Languages
/* -- returns:
lcid name
-- ----
2052 Simplified Chinese
1028 Traditional Chinese
1030 Danish
1031 German
2057 British English
1033 English
3082 Spanish
1036 French
1040 Italian
1041 Japanese
1042 Korean
0 Neutral
1043 Dutch
1045 Polish
1046 Brazilian
2070 Portuguese
1049 Russian
1053 Swedish
1054 Thai
1055 Turkish
3076 Chinese (Hong Kong SAR, PRC)
5124 Chinese (Macau SAR)
4100 Chinese (Singapore)
(23 row(s) affected)
*/
Are you interested in doing this as an exercise or do you have actual
Ukrainian customer-base of SQL Server 2005 FTS-enabled applications in mind?
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:1E8AAD8A-EFD2-4975-B9AC-B5AF11B49216@.microsoft.com...
> Hi
> Ask this in the SQL Server 2005 newsgroups:
> http://communities.microsoft.com/ne...p=sqlserver2005
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Ihor Orobchuk" wrote:
>
Full-text search across multiple tables
We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.
As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.
Thanks.
John Fleming
jfleming@.misicompany.com
You might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegr oups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com
>
Sunday, February 19, 2012
Full-text search across multiple tables
We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.
As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.
Thanks.
John Fleming
jfleming@.misicompany.comYou might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegroups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com
>
Full-text search across multiple tables
We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.
As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.
Thanks.
John Fleming
jfleming@.misicompany.comYou might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegroups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com
>
Full-text search across multiple tables
We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.
As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.
Thanks.
John Fleming
jfleming@.misicompany.com
You might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegr oups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com
>
Full-text search across multiple tables
We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.
As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.
Thanks.
John Fleming
jfleming@.misicompany.comYou might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegr oups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com