Showing posts with label key. Show all posts
Showing posts with label key. Show all posts

Tuesday, March 27, 2012

general comment is required

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.
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.

Monday, March 26, 2012

Gaps in my key ID field

Greetings,

I am new to SQL Server. I've created a database with a key ID field that is set to automatically increment. Well, after adding records I've got some gaps in my numbering and want to renumber from 1 to eof.

What is the best way to do this in SQL Server 2005?

Thank you.

I have covered it in the thread below. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2072438&SiteID=1

|||Thank you!|||

Elizabeth Davis wrote:

Thank you!

I am glad I could help.

Friday, February 24, 2012

Fulltext Search or Like Search

Hi,
I need to search a Items catalog that has a field that is key word
seperated by commas. Would a fulltext search be more productive than a like
searcn. If the use keys in Bic Black Pens then that is all it should bring
up but if they key in pens it should only get pens. With the like I have been
getting despensers. I don't know exactly how Fulltext search would work for
this.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-search/200611/1
Hello MattIrwin
Full text would treat words as words and not return misses as in you case.
You can also use full text to use a thesaurus and have much more complicated
searches, i.e. Pen AND NOT Fountain. Which is much more difficult with LIKE
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi,
> I need to search a Items catalog that has a field that is key word
> seperated by commas. Would a fulltext search be more productive than a
> like
> searcn. If the use keys in Bic Black Pens then that is all it should
> bring up but if they key in pens it should only get pens. With the
> like I have been getting despensers. I don't know exactly how Fulltext
> search would work for this.
>
|||Full text for English will break words at white space. So bic black pen
would be broken as three words, bic, black and pen. Dispensers would be
broken as dispensers. In a free text search this would match with
dispensers, dispenser, dispenser's, and dispensers.
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
"MattIrwin via droptable.com" <u28166@.uwe> wrote in message
news:6926b9c20e067@.uwe...
> Hi,
> I need to search a Items catalog that has a field that is key word
> seperated by commas. Would a fulltext search be more productive than a
> like
> searcn. If the use keys in Bic Black Pens then that is all it should
> bring
> up but if they key in pens it should only get pens. With the like I have
> been
> getting despensers. I don't know exactly how Fulltext search would work
> for
> this.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-search/200611/1
>
|||You need to use a word breaker which breaks the words in the form you want,
Greek I take it. Otherwise I think like is your best bet.
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
"Allan Ebdrup" <ebdrup@.noemail.noemail> wrote in message
news:%23aVerjWNHHA.3424@.TK2MSFTNGP02.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uo8ZEAyBHHA.4892@.TK2MSFTNGP04.phx.gbl...
> I would like to do the opposite.
> When searching for "pdagog" I also want to return results that contain
> "dagplejepdagog" or "pdagogmedhjlper" is ther no other way to
> accomplish this than using LIKE '%pdagog%'.
> Performance using LIKE is very poor, how do I improve performance?
> Kind Regards,
> Allan Ebdrup
>
|||Allan wrote on Thu, 11 Jan 2007 11:11:51 +0100:

> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:uo8ZEAyBHHA.4892@.TK2MSFTNGP04.phx.gbl...
> I would like to do the opposite.
> When searching for "pdagog" I also want to return results that contain
> "dagplejepdagog" or "pdagogmedhjlper" is ther no other way to
> accomplish this than using LIKE '%pdagog%'.
> Performance using LIKE is very poor, how do I improve performance?
The second example is already possible:
SELECT * FROM Table WHERE CONTAINS(*,'pdagog*')
However, the first example isn't. As Hilary has pointed out, there may be a
language dependent wordbreaker that will split the words as you require.
Another option, if you don't mind increasing the storage requirements, is to
index on a reversed version of the data too. Store a copy of the data in
another column in reverse order, such as
KEY Word RWord
1 dagplejepdagog gogadpejelpgad
and create the FTI on both Word and RWord, then you could use:
SELECT * FROM Table WHERE CONTAINS(*,'pdagog* or gogadp*')
However, this won't work when the term you are searching for is in the
middle of a word.
Dan
|||You could be correct here, my knowledge of these languages is not great. The
ligature or digraph commonly occurs in Greek words transliterated into
English, but these do appear more German or Scandinavian.
You might want to check out this document for more info.
http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/
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
"tbh" <femdev@.newsgroups.nospam> wrote in message
news:%23rGPgouOHHA.3544@.TK2MSFTNGP03.phx.gbl...
> "Greek"? looks a bit more like a scandinavian language, doesn't it?
> i think Allan is looking for inflectional forms or compound words
> containing a stem-word in question. i'm interested too (in my case for
> German).
> can anyone point us to good documentation on which languages are supported
> and other details? there is some info here:
> http://msdn2.microsoft.com/en-US/library/ms142507.aspx
> but i would want to learn more.
> cheers,
> Tim Hanson
>

full-text search failed with one million of key words (Sqlserver20

Hello,
I have to make big tests with sql server 2005 express (i'm waiting credits
to buy a full license).
I detected a big problem with my full-text search, and i don't know if it's
a bad use of Sql Server or if it's impossible to do it!
Here's my use case :
a table like this :
primary-key | data
1 | toto value1
2 | toto value2
3 | toto value3
...
1000001 | toto value1000001
So my table contains one millions of rows. Also, my index contains one
million+one key words : "toto" and all the "valueXXXX".
if i make those requests :
select count(primary-key) from table where contains(data,'"toto"') -> ok,
1000001
select count(primary-key) from table where contains(data,'"value5321"') ->
ok, 1
select count(primary-key) from table where contains(data,'"t*"') -> ok,
1000001
select count(primary-key) from table where contains(data,'"v"') -> KO, error
message "too complex request ect". I was expected 1000001.
Can someone help me ? I'm sure sql server 2005 can do it, but i'm also sure
i forgot an option...
Note : i work on a database who contains several millions rows, and
sometimes, users need to make some insert/update of several thousands of
rows. So they need to make very big requests to check the insert/update.
And i'm not english, so sorry for the mistakes ;-)
Thanks to all !
Hi Europa_Lili,
I assume the query you're submitting (the one that fails) actually looks like
select count(primary-key) from table where contains(data,'"v*"')
(i.e. with a * after v, meaning it's a prefix query)
SQL 2005 FTS will throw this error when the prefix identifies too many
distinct keywords that match it. What are the semantics of the query you're
trying to accomplish?
Another advice: if every row of your table contains "toto", then there's no
point in indexing it since querying for it provides no value. It will just
unnecessarily increase the size of your index. You may consider adding it to
the list of noise words. See SQL Books Online for information on noise word
files and how to change them (you'll need to re-index your data).
"Europa_Lili" wrote:

> Hello,
> I have to make big tests with sql server 2005 express (i'm waiting credits
> to buy a full license).
> I detected a big problem with my full-text search, and i don't know if it's
> a bad use of Sql Server or if it's impossible to do it!
> Here's my use case :
> a table like this :
> primary-key | data
> --
> 1 | toto value1
> 2 | toto value2
> 3 | toto value3
> ...
> 1000001 | toto value1000001
> So my table contains one millions of rows. Also, my index contains one
> million+one key words : "toto" and all the "valueXXXX".
> if i make those requests :
> select count(primary-key) from table where contains(data,'"toto"') -> ok,
> 1000001
> select count(primary-key) from table where contains(data,'"value5321"') ->
> ok, 1
> select count(primary-key) from table where contains(data,'"t*"') -> ok,
> 1000001
> select count(primary-key) from table where contains(data,'"v"') -> KO, error
> message "too complex request ect". I was expected 1000001.
> Can someone help me ? I'm sure sql server 2005 can do it, but i'm also sure
> i forgot an option...
> Note : i work on a database who contains several millions rows, and
> sometimes, users need to make some insert/update of several thousands of
> rows. So they need to make very big requests to check the insert/update.
> And i'm not english, so sorry for the mistakes ;-)
> Thanks to all !
>
|||Hello,
thanks a lot for your answer.
Research with "toto" was just a test. In real life, my database contains
some informations (text, colours, ect) about picture files . Pictures come
from big old books numerisation. (Sometimes, we've got 500 to 1000 pictures
for only one book) And the table i want to query looks like this :
primary-key | data-text | file name
| localization
1 | page 1 of a book toto | new_york_library_522 |
C:/newyork
2 | page 2 of a document | paris_library_81
| D:/library_paris 3 | image from a old
book | new_york_library_523 | C:/newyork
......
Here is a exemple of what i have to do :
we receive 500 000 files (all the old books of one library). All the files
have a name like this "NAME_LIBRARY_XXXXXXXXX" where X is a number.
And i need to select all these files to make some update actions in the
database.
I can't use the date.
so i need a query like "select * from mytable where
contains(file-name,'"NAME_LIBRARY_*"')".
And it don't work, because there's 500 000 results.
Another idea to help me ?
Have a good day and thanks for all
"denistc" wrote:
[vbcol=seagreen]
> Hi Europa_Lili,
> I assume the query you're submitting (the one that fails) actually looks like
> select count(primary-key) from table where contains(data,'"v*"')
> (i.e. with a * after v, meaning it's a prefix query)
> SQL 2005 FTS will throw this error when the prefix identifies too many
> distinct keywords that match it. What are the semantics of the query you're
> trying to accomplish?
> Another advice: if every row of your table contains "toto", then there's no
> point in indexing it since querying for it provides no value. It will just
> unnecessarily increase the size of your index. You may consider adding it to
> the list of noise words. See SQL Books Online for information on noise word
> files and how to change them (you'll need to re-index your data).
> "Europa_Lili" wrote:

Sunday, February 19, 2012

FullText Search

I have create a table called tblcatalog with colums id(identity,primary key) and contents(varchar(100))

I have then created a full text catalog on that table and populated it.

Then i wrote the following query
"select contents from tblcatalog where contains(contents,'sample data')"
It is fetching 0 records even though u have 5 records with entries "sample data"

Can anyone tell me the solution immediately

bye
shankyImmediately? Have you tried everything you could think of before asking for help? It'll take me 20 minutes or so to setup FullText Search, and will take you probably much less to experiment with it.|||when you say you populated it did you create an initial full population?|||Forgive me if this is basic to you. Just trying to be thorough. :)
In Enterprise Manager, right click on your table. Choose 'Full Text Index Table' (MS Search service must be started for this to appear.) Choose 'Start full population.' Rerun your query. If it works, you must not have really done a full population yet. In that case you may want to set up incremental or full populations using the 'Schedules..' option on the same context menu...
If not, I have no clue what the problem is! :)

Fulltext Search

Dear Aall,
How can we search for all the key words? Is it possible to write a single
query with Freetext or Contains?
Example: if I search for 'Microsoft has released a new Operating System' I
should get the result which contains all the Key words in the phrase we
search.
Here in this example I should get the result set which contains all the key
words 'Microsoft', 'Released', 'Operating', 'System' in any order by
ignoring the noise words automatically.
Please give us your valid suggetions on this regards..
Thanks in advance
SathianHi Sathian,
Try:
http://www.experts-exchange.com/Dat...members/jtkane/
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:dg8b86$cbn$1@.ns2.fe.internet.bosch.com...
> Dear Aall,
> How can we search for all the key words? Is it possible to write a single
> query with Freetext or Contains?
> Example: if I search for 'Microsoft has released a new Operating System'
> I
> should get the result which contains all the Key words in the phrase we
> search.
> Here in this example I should get the result set which contains all the
> key
> words 'Microsoft', 'Released', 'Operating', 'System' in any order by
> ignoring the noise words automatically.
> Please give us your valid suggetions on this regards..
>
> Thanks in advance
> Sathian
>

Fulltext Search

Dear Aall,
How can we search for all the key words? Is it possible to write a single
query with Freetext or Contains?
Example: if I search for 'Microsoft has released a new Operating System' I
should get the result which contains all the Key words in the phrase we
search.
Here in this example I should get the result set which contains all the key
words 'Microsoft', 'Released', 'Operating', 'System' in any order by
ignoring the noise words automatically.
Please give us your valid suggetions on this regards..
Thanks in advance
Sathian
Hi Sathian,
Try:
http://www.experts-exchange.com/Data..._20705253.html
The code may not be perfect, but with a bit of modification, you should be
able to get it to work for you to search for all the key words.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:dg8b86$cbn$1@.ns2.fe.internet.bosch.com...
> Dear Aall,
> How can we search for all the key words? Is it possible to write a single
> query with Freetext or Contains?
> Example: if I search for 'Microsoft has released a new Operating System'
> I
> should get the result which contains all the Key words in the phrase we
> search.
> Here in this example I should get the result set which contains all the
> key
> words 'Microsoft', 'Released', 'Operating', 'System' in any order by
> ignoring the noise words automatically.
> Please give us your valid suggetions on this regards..
>
> Thanks in advance
> Sathian
>