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.
Showing posts with label functionality. Show all posts
Showing posts with label functionality. Show all posts
Tuesday, March 27, 2012
Monday, March 19, 2012
Functionality of SQL Server Errorlog
What is the functionality of SQL Server 2000 Enterprise Manager Errorlog?
Thanks,It is a text file where SQL Server writes severe error and other relevant system messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:0BB3C8A0-BE81-42BA-A300-30D17CC31DBB@.microsoft.com...
> What is the functionality of SQL Server 2000 Enterprise Manager Errorlog?
> Thanks,
Thanks,It is a text file where SQL Server writes severe error and other relevant system messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:0BB3C8A0-BE81-42BA-A300-30D17CC31DBB@.microsoft.com...
> What is the functionality of SQL Server 2000 Enterprise Manager Errorlog?
> Thanks,
Labels:
database,
enterprise,
errorlog,
functionality,
manager,
microsoft,
mysql,
oracle,
server,
sql
Functionality of SQL Server Errorlog
What is the functionality of SQL Server 2000 Enterprise Manager Errorlog?
Thanks,It is a text file where SQL Server writes severe error and other relevant sy
stem messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:0BB3C8A0-BE81-42BA-A300-30D17CC31DBB@.microsoft.com...
> What is the functionality of SQL Server 2000 Enterprise Manager Errorlog?
> Thanks,
Thanks,It is a text file where SQL Server writes severe error and other relevant sy
stem messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:0BB3C8A0-BE81-42BA-A300-30D17CC31DBB@.microsoft.com...
> What is the functionality of SQL Server 2000 Enterprise Manager Errorlog?
> Thanks,
Labels:
database,
enterprise,
errorlog,
functionality,
manager,
microsoft,
mysql,
oracle,
server,
sql
Functionality of DATEPART
Functionally, is there any difference between DATEPART for m, d, and yyyy, verses simply using MONTH, DAY, and YEAR functions respectively?
For all intents and purposes, they seem identical, so is there any performance considerations using one verses another?I don't know of any material difference between them.
-PatP
For all intents and purposes, they seem identical, so is there any performance considerations using one verses another?I don't know of any material difference between them.
-PatP
Labels:
database,
datepart,
functionality,
functionally,
functions,
microsoft,
mysql,
oracle,
respectivelyfor,
server,
simply,
sql,
verses,
yyyy
Functionality Like Pivot Chart
There is a view on our server that has fields for project name and then 25
months, each row contains the descriptive name and the forecasted budget in
each month. I need to create a chart showing the total budget for month and
a running total for the year to date. In Excel this is easy with a Pivot
Chart, how do I do this in SSRS 2005?
months, each row contains the descriptive name and the forecasted budget in
each month. I need to create a chart showing the total budget for month and
a running total for the year to date. In Excel this is easy with a Pivot
Chart, how do I do this in SSRS 2005?
TIA
DeanDiscover the joy of the 'matrix control' or as I like to think of it:
A design time pivot table, but much less fun to use.
You just set the groups on the columns or rows. Simple. You can even have (right click menu) subtotals.
If you need to have 'other data' displayed, you will need to fiddle about with the Inscope, and create phantom groupings.
see this:
http://www.sqlskills.com/blogs/liz/2006/07/21/ReportingServicesGettingTheMatrixToDisplayTwoSubtotalsForTheSameGroup.aspx
green bar matrix:
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx|||Can the matrix control display data as a CHART?|||http://msdn2.microsoft.com/en-us/library/aa964128(SQL.90).aspx
Sunday, February 26, 2012
Full-text SharePoint
Hi all,
I'm looking for in-depth information on the search functionality of
SharePoint 2003. As it is using the SQL full-text search, I post my question
to this newsgroup.
The main goal is to figure out how I can add the functionality of rating
documents to the SharePoint search, in order to make high-rated documents
show up higher in the search results.
Kind regards,
Jeroendb
Jeroen,
I think what you're looking for in SharePoint 2003 is called "best bets",
see "Planning Your Information Structure Using Microsoft Office SharePoint
Portal Server 2003" at:
http://www.microsoft.com/technet/pro...c0861881x.mspx and
under "Planning Keywords and Keyword Best Bets".
While you can do much the same thing using SQL Server, it's can be a bit
complex to get a scalable solution. How would you determine what is a
"highly-rated document" based upon a keyword or phrase search entered by the
searcher?
Regards,
John
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:OHe$OhCZEHA.2480@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm looking for in-depth information on the search functionality of
> SharePoint 2003. As it is using the SQL full-text search, I post my
question
> to this newsgroup.
> The main goal is to figure out how I can add the functionality of rating
> documents to the SharePoint search, in order to make high-rated documents
> show up higher in the search results.
> Kind regards,
> Jeroendb
>
|||Thanks John, for your quick reply.
By rating I mean that I would like to let the end-user rate the documents he
finds. I.e. documents with an average rating of > 50% should show up higher
in the search hierarchy. Documents that are never rated or searched on
should be discarded after a certain period of time.
This probably is too much for SharePoint ?
Kind regards,
Jeroendb
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OCB1l7JZEHA.3112@.tk2msftngp13.phx.gbl...
> Jeroen,
> I think what you're looking for in SharePoint 2003 is called "best bets",
> see "Planning Your Information Structure Using Microsoft Office SharePoint
> Portal Server 2003" at:
> http://www.microsoft.com/technet/pro...c0861881x.mspx
and
> under "Planning Keywords and Keyword Best Bets".
> While you can do much the same thing using SQL Server, it's can be a bit
> complex to get a scalable solution. How would you determine what is a
> "highly-rated document" based upon a keyword or phrase search entered by
the[vbcol=seagreen]
> searcher?
> Regards,
> John
>
> "Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
> news:OHe$OhCZEHA.2480@.tk2msftngp13.phx.gbl...
> question
documents
>
|||You're welcome, Jeroen,
Yes, this can be done using SQL FTS and adding a new column to your
FT-enabled table called "ExtendedRank" and then adding an value to the RANK
value in the CONTAINSTABLE or FREETEXTTABLE query and then ordering the
results by the new column "Extended Rank". Below is an example of the query
and table structure:
CREATE TABLE FTSTable (
KeyCol int IDENTITY (1,1) NOT NULL
CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
TextCol text NULL,
ImageCol image NULL,
ExtCol sysname NULL,
Keyword varchar(500),
KeywordWeight int,
TimeStampCol timestamp NULL
) ON [PRIMARY]
go
-- Note, the column KeywordWeight may need to be re-define as FLOAT
depending upon the type of calculations you are planning on doing.
-- Insert data...
INSERT FTSTable values('This is random data for row 1', 0xFFFFFFFF, 'zip',
'random,data,row', 1, NULL)
INSERT FTSTable values('The brown fox jumped over the fence. row 2',
0xFFFFFFFF, 'xls', 'brown,fox,fence', 10, NULL)
INSERT FTSTable values('Mary had a little lamb and its fleece was white',
0xFFFFFFFF, 'ppt', 'random,data,row', 0, NULL)
INSERT FTSTable values('Moby Dick is considered on of the greatest books
available.', 0xFFFFFFFF, 'txt', 'moby,dick,book', 50, NULL)
INSERT FTSTable values('This has more to do this short text that with rows',
0xFFFFFFFF, 'htm', 'short,text,row', 100, NULL)
INSERT FTSTable values('Taglines would be helpful here as I could get more
text added', 0xFFFFFFFF, 'pdf', 'tagline,helpful,text', 22, NULL)
INSERT FTSTable values('Winston Churchill was one of the greatest speakers
of all time', 0xFFFFFFFF, 'xml', 'winston,churchill,great,speakers', 1000,
NULL)
go
-- Note, that KeyCol rows 5 and 6 have very high values in the KeyWordWeight
column
-- Test FTS with containstable and freetexttable
SELECT FTS.KeyCol, FTS.TextCol, FTS.Keyword, FTS.KeywordWeight, CT.[RANK]
from FTSTable AS FTS
JOIN containstable(FTSTable,*,'text') as CT ON CT.[KEY] = FTS.KeyCol
ORDER by CT.[RANK] DESC
-- as expected two rows returned: KeyCol 6 and 5
-- caculated ExtendedRank = Rank + KeywordWeight
SELECT FTS.KeyCol, FTS.TextCol, CT.[RANK], FTS.KeywordWeight,
FTS.KeywordWeight + CT.[RANK] as ExtendedRank
from FTSTable AS FTS
JOIN containstable(FTSTable,*,'text') as CT ON CT.[KEY] = FTS.KeyCol
ORDER by ExtendedRank DESC -- CT.[RANK] DESC
/* -- Returns: as expected two rows returned: KeyCol 6 and 5 with HIGHEST
ExtendedRank first!!
KeyCol TextCol RANK
KeywordWeight ExtendedRank
-- ---- -- -
-- --
5 This has more to do this short text that with rows 48
100 148
6 Taglines would be helpful here as I could get more 48
22 70
(2 row(s) affected)
*/
Additionally, the above method is very scallable as you do not have to join
your FT-enabled table with another FT-enabled table in order to get the
"best bet" to the top of the resultset. This method could also be extended
and use a true "computed column" and support more complex calculations as
well. I don't know if this is "too much for SharePoint", but this is very
doable in SQL Server!
Regards,
John
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:#Ik6EwOZEHA.3988@.tk2msftngp13.phx.gbl...
> Thanks John, for your quick reply.
> By rating I mean that I would like to let the end-user rate the documents
he
> finds. I.e. documents with an average rating of > 50% should show up
higher[vbcol=seagreen]
> in the search hierarchy. Documents that are never rated or searched on
> should be discarded after a certain period of time.
> This probably is too much for SharePoint ?
> Kind regards,
> Jeroendb
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OCB1l7JZEHA.3112@.tk2msftngp13.phx.gbl...
bets",[vbcol=seagreen]
SharePoint[vbcol=seagreen]
> and
> the
rating
> documents
>
|||Jeroen,
Did you find the below SQL FTS solution to provide the "functionality of
rating documents to the SharePoint search, in order to make high-rated
documents show up higher in the search results"? If so, how would you let
the end-user rate the documents he
finds? You programmatically set the column KeywordWeight to some value and
then use this a as multiplier (vs. just adding the value to the CT.[RANK]
value) and then ordering by the computed column: ExtendedRank.
If you're interested I've been able to extend this concept to use the
Keyword column to further extend it to a "keyword specific" solution
without joining to another table and without using cursor processing and
therefore is very scalable... Let me know if you would be interested in this
alternative approach to "best bet", i.e., ranking specific rows higher than
other rows in the CONTAINSTABLE or FREETEXTTABLE resultset.
Regards,
John
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OGnTQvPZEHA.3132@.TK2MSFTNGP10.phx.gbl...
> You're welcome, Jeroen,
> Yes, this can be done using SQL FTS and adding a new column to your
> FT-enabled table called "ExtendedRank" and then adding an value to the
RANK
> value in the CONTAINSTABLE or FREETEXTTABLE query and then ordering the
> results by the new column "Extended Rank". Below is an example of the
query
> and table structure:
> CREATE TABLE FTSTable (
> KeyCol int IDENTITY (1,1) NOT NULL
> CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
> TextCol text NULL,
> ImageCol image NULL,
> ExtCol sysname NULL,
> Keyword varchar(500),
> KeywordWeight int,
> TimeStampCol timestamp NULL
> ) ON [PRIMARY]
> go
> -- Note, the column KeywordWeight may need to be re-define as FLOAT
> depending upon the type of calculations you are planning on doing.
> -- Insert data...
> INSERT FTSTable values('This is random data for row 1', 0xFFFFFFFF, 'zip',
> 'random,data,row', 1, NULL)
> INSERT FTSTable values('The brown fox jumped over the fence. row 2',
> 0xFFFFFFFF, 'xls', 'brown,fox,fence', 10, NULL)
> INSERT FTSTable values('Mary had a little lamb and its fleece was white',
> 0xFFFFFFFF, 'ppt', 'random,data,row', 0, NULL)
> INSERT FTSTable values('Moby Dick is considered on of the greatest books
> available.', 0xFFFFFFFF, 'txt', 'moby,dick,book', 50, NULL)
> INSERT FTSTable values('This has more to do this short text that with
rows',
> 0xFFFFFFFF, 'htm', 'short,text,row', 100, NULL)
> INSERT FTSTable values('Taglines would be helpful here as I could get more
> text added', 0xFFFFFFFF, 'pdf', 'tagline,helpful,text', 22, NULL)
> INSERT FTSTable values('Winston Churchill was one of the greatest speakers
> of all time', 0xFFFFFFFF, 'xml', 'winston,churchill,great,speakers', 1000,
> NULL)
> go
> -- Note, that KeyCol rows 5 and 6 have very high values in the
KeyWordWeight
> column
> -- Test FTS with containstable and freetexttable
> SELECT FTS.KeyCol, FTS.TextCol, FTS.Keyword, FTS.KeywordWeight, CT.[RANK]
> from FTSTable AS FTS
> JOIN containstable(FTSTable,*,'text') as CT ON CT.[KEY] = FTS.KeyCol
> ORDER by CT.[RANK] DESC
> -- as expected two rows returned: KeyCol 6 and 5
> -- caculated ExtendedRank = Rank + KeywordWeight
> SELECT FTS.KeyCol, FTS.TextCol, CT.[RANK], FTS.KeywordWeight,
> FTS.KeywordWeight + CT.[RANK] as ExtendedRank
> from FTSTable AS FTS
> JOIN containstable(FTSTable,*,'text') as CT ON CT.[KEY] = FTS.KeyCol
> ORDER by ExtendedRank DESC -- CT.[RANK] DESC
> /* -- Returns: as expected two rows returned: KeyCol 6 and 5 with HIGHEST
> ExtendedRank first!!
> KeyCol TextCol RANK
> KeywordWeight ExtendedRank
> -- ---- --
-
> -- --
> 5 This has more to do this short text that with rows 48
> 100 148
> 6 Taglines would be helpful here as I could get more 48
> 22 70
> (2 row(s) affected)
> */
> Additionally, the above method is very scallable as you do not have to
join[vbcol=seagreen]
> your FT-enabled table with another FT-enabled table in order to get the
> "best bet" to the top of the resultset. This method could also be extended
> and use a true "computed column" and support more complex calculations as
> well. I don't know if this is "too much for SharePoint", but this is very
> doable in SQL Server!
> Regards,
> John
>
> "Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
> news:#Ik6EwOZEHA.3988@.tk2msftngp13.phx.gbl...
documents[vbcol=seagreen]
> he
> higher
> bets",
> SharePoint
http://www.microsoft.com/technet/pro...c0861881x.mspx[vbcol=seagreen]
bit[vbcol=seagreen]
by
> rating
>
I'm looking for in-depth information on the search functionality of
SharePoint 2003. As it is using the SQL full-text search, I post my question
to this newsgroup.
The main goal is to figure out how I can add the functionality of rating
documents to the SharePoint search, in order to make high-rated documents
show up higher in the search results.
Kind regards,
Jeroendb
Jeroen,
I think what you're looking for in SharePoint 2003 is called "best bets",
see "Planning Your Information Structure Using Microsoft Office SharePoint
Portal Server 2003" at:
http://www.microsoft.com/technet/pro...c0861881x.mspx and
under "Planning Keywords and Keyword Best Bets".
While you can do much the same thing using SQL Server, it's can be a bit
complex to get a scalable solution. How would you determine what is a
"highly-rated document" based upon a keyword or phrase search entered by the
searcher?
Regards,
John
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:OHe$OhCZEHA.2480@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm looking for in-depth information on the search functionality of
> SharePoint 2003. As it is using the SQL full-text search, I post my
question
> to this newsgroup.
> The main goal is to figure out how I can add the functionality of rating
> documents to the SharePoint search, in order to make high-rated documents
> show up higher in the search results.
> Kind regards,
> Jeroendb
>
|||Thanks John, for your quick reply.
By rating I mean that I would like to let the end-user rate the documents he
finds. I.e. documents with an average rating of > 50% should show up higher
in the search hierarchy. Documents that are never rated or searched on
should be discarded after a certain period of time.
This probably is too much for SharePoint ?
Kind regards,
Jeroendb
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OCB1l7JZEHA.3112@.tk2msftngp13.phx.gbl...
> Jeroen,
> I think what you're looking for in SharePoint 2003 is called "best bets",
> see "Planning Your Information Structure Using Microsoft Office SharePoint
> Portal Server 2003" at:
> http://www.microsoft.com/technet/pro...c0861881x.mspx
and
> under "Planning Keywords and Keyword Best Bets".
> While you can do much the same thing using SQL Server, it's can be a bit
> complex to get a scalable solution. How would you determine what is a
> "highly-rated document" based upon a keyword or phrase search entered by
the[vbcol=seagreen]
> searcher?
> Regards,
> John
>
> "Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
> news:OHe$OhCZEHA.2480@.tk2msftngp13.phx.gbl...
> question
documents
>
|||You're welcome, Jeroen,
Yes, this can be done using SQL FTS and adding a new column to your
FT-enabled table called "ExtendedRank" and then adding an value to the RANK
value in the CONTAINSTABLE or FREETEXTTABLE query and then ordering the
results by the new column "Extended Rank". Below is an example of the query
and table structure:
CREATE TABLE FTSTable (
KeyCol int IDENTITY (1,1) NOT NULL
CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
TextCol text NULL,
ImageCol image NULL,
ExtCol sysname NULL,
Keyword varchar(500),
KeywordWeight int,
TimeStampCol timestamp NULL
) ON [PRIMARY]
go
-- Note, the column KeywordWeight may need to be re-define as FLOAT
depending upon the type of calculations you are planning on doing.
-- Insert data...
INSERT FTSTable values('This is random data for row 1', 0xFFFFFFFF, 'zip',
'random,data,row', 1, NULL)
INSERT FTSTable values('The brown fox jumped over the fence. row 2',
0xFFFFFFFF, 'xls', 'brown,fox,fence', 10, NULL)
INSERT FTSTable values('Mary had a little lamb and its fleece was white',
0xFFFFFFFF, 'ppt', 'random,data,row', 0, NULL)
INSERT FTSTable values('Moby Dick is considered on of the greatest books
available.', 0xFFFFFFFF, 'txt', 'moby,dick,book', 50, NULL)
INSERT FTSTable values('This has more to do this short text that with rows',
0xFFFFFFFF, 'htm', 'short,text,row', 100, NULL)
INSERT FTSTable values('Taglines would be helpful here as I could get more
text added', 0xFFFFFFFF, 'pdf', 'tagline,helpful,text', 22, NULL)
INSERT FTSTable values('Winston Churchill was one of the greatest speakers
of all time', 0xFFFFFFFF, 'xml', 'winston,churchill,great,speakers', 1000,
NULL)
go
-- Note, that KeyCol rows 5 and 6 have very high values in the KeyWordWeight
column
-- Test FTS with containstable and freetexttable
SELECT FTS.KeyCol, FTS.TextCol, FTS.Keyword, FTS.KeywordWeight, CT.[RANK]
from FTSTable AS FTS
JOIN containstable(FTSTable,*,'text') as CT ON CT.[KEY] = FTS.KeyCol
ORDER by CT.[RANK] DESC
-- as expected two rows returned: KeyCol 6 and 5
-- caculated ExtendedRank = Rank + KeywordWeight
SELECT FTS.KeyCol, FTS.TextCol, CT.[RANK], FTS.KeywordWeight,
FTS.KeywordWeight + CT.[RANK] as ExtendedRank
from FTSTable AS FTS
JOIN containstable(FTSTable,*,'text') as CT ON CT.[KEY] = FTS.KeyCol
ORDER by ExtendedRank DESC -- CT.[RANK] DESC
/* -- Returns: as expected two rows returned: KeyCol 6 and 5 with HIGHEST
ExtendedRank first!!
KeyCol TextCol RANK
KeywordWeight ExtendedRank
-- ---- -- -
-- --
5 This has more to do this short text that with rows 48
100 148
6 Taglines would be helpful here as I could get more 48
22 70
(2 row(s) affected)
*/
Additionally, the above method is very scallable as you do not have to join
your FT-enabled table with another FT-enabled table in order to get the
"best bet" to the top of the resultset. This method could also be extended
and use a true "computed column" and support more complex calculations as
well. I don't know if this is "too much for SharePoint", but this is very
doable in SQL Server!
Regards,
John
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:#Ik6EwOZEHA.3988@.tk2msftngp13.phx.gbl...
> Thanks John, for your quick reply.
> By rating I mean that I would like to let the end-user rate the documents
he
> finds. I.e. documents with an average rating of > 50% should show up
higher[vbcol=seagreen]
> in the search hierarchy. Documents that are never rated or searched on
> should be discarded after a certain period of time.
> This probably is too much for SharePoint ?
> Kind regards,
> Jeroendb
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OCB1l7JZEHA.3112@.tk2msftngp13.phx.gbl...
bets",[vbcol=seagreen]
SharePoint[vbcol=seagreen]
> and
> the
rating
> documents
>
|||Jeroen,
Did you find the below SQL FTS solution to provide the "functionality of
rating documents to the SharePoint search, in order to make high-rated
documents show up higher in the search results"? If so, how would you let
the end-user rate the documents he
finds? You programmatically set the column KeywordWeight to some value and
then use this a as multiplier (vs. just adding the value to the CT.[RANK]
value) and then ordering by the computed column: ExtendedRank.
If you're interested I've been able to extend this concept to use the
Keyword column to further extend it to a "keyword specific" solution
without joining to another table and without using cursor processing and
therefore is very scalable... Let me know if you would be interested in this
alternative approach to "best bet", i.e., ranking specific rows higher than
other rows in the CONTAINSTABLE or FREETEXTTABLE resultset.
Regards,
John
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OGnTQvPZEHA.3132@.TK2MSFTNGP10.phx.gbl...
> You're welcome, Jeroen,
> Yes, this can be done using SQL FTS and adding a new column to your
> FT-enabled table called "ExtendedRank" and then adding an value to the
RANK
> value in the CONTAINSTABLE or FREETEXTTABLE query and then ordering the
> results by the new column "Extended Rank". Below is an example of the
query
> and table structure:
> CREATE TABLE FTSTable (
> KeyCol int IDENTITY (1,1) NOT NULL
> CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
> TextCol text NULL,
> ImageCol image NULL,
> ExtCol sysname NULL,
> Keyword varchar(500),
> KeywordWeight int,
> TimeStampCol timestamp NULL
> ) ON [PRIMARY]
> go
> -- Note, the column KeywordWeight may need to be re-define as FLOAT
> depending upon the type of calculations you are planning on doing.
> -- Insert data...
> INSERT FTSTable values('This is random data for row 1', 0xFFFFFFFF, 'zip',
> 'random,data,row', 1, NULL)
> INSERT FTSTable values('The brown fox jumped over the fence. row 2',
> 0xFFFFFFFF, 'xls', 'brown,fox,fence', 10, NULL)
> INSERT FTSTable values('Mary had a little lamb and its fleece was white',
> 0xFFFFFFFF, 'ppt', 'random,data,row', 0, NULL)
> INSERT FTSTable values('Moby Dick is considered on of the greatest books
> available.', 0xFFFFFFFF, 'txt', 'moby,dick,book', 50, NULL)
> INSERT FTSTable values('This has more to do this short text that with
rows',
> 0xFFFFFFFF, 'htm', 'short,text,row', 100, NULL)
> INSERT FTSTable values('Taglines would be helpful here as I could get more
> text added', 0xFFFFFFFF, 'pdf', 'tagline,helpful,text', 22, NULL)
> INSERT FTSTable values('Winston Churchill was one of the greatest speakers
> of all time', 0xFFFFFFFF, 'xml', 'winston,churchill,great,speakers', 1000,
> NULL)
> go
> -- Note, that KeyCol rows 5 and 6 have very high values in the
KeyWordWeight
> column
> -- Test FTS with containstable and freetexttable
> SELECT FTS.KeyCol, FTS.TextCol, FTS.Keyword, FTS.KeywordWeight, CT.[RANK]
> from FTSTable AS FTS
> JOIN containstable(FTSTable,*,'text') as CT ON CT.[KEY] = FTS.KeyCol
> ORDER by CT.[RANK] DESC
> -- as expected two rows returned: KeyCol 6 and 5
> -- caculated ExtendedRank = Rank + KeywordWeight
> SELECT FTS.KeyCol, FTS.TextCol, CT.[RANK], FTS.KeywordWeight,
> FTS.KeywordWeight + CT.[RANK] as ExtendedRank
> from FTSTable AS FTS
> JOIN containstable(FTSTable,*,'text') as CT ON CT.[KEY] = FTS.KeyCol
> ORDER by ExtendedRank DESC -- CT.[RANK] DESC
> /* -- Returns: as expected two rows returned: KeyCol 6 and 5 with HIGHEST
> ExtendedRank first!!
> KeyCol TextCol RANK
> KeywordWeight ExtendedRank
> -- ---- --
-
> -- --
> 5 This has more to do this short text that with rows 48
> 100 148
> 6 Taglines would be helpful here as I could get more 48
> 22 70
> (2 row(s) affected)
> */
> Additionally, the above method is very scallable as you do not have to
join[vbcol=seagreen]
> your FT-enabled table with another FT-enabled table in order to get the
> "best bet" to the top of the resultset. This method could also be extended
> and use a true "computed column" and support more complex calculations as
> well. I don't know if this is "too much for SharePoint", but this is very
> doable in SQL Server!
> Regards,
> John
>
> "Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
> news:#Ik6EwOZEHA.3988@.tk2msftngp13.phx.gbl...
documents[vbcol=seagreen]
> he
> higher
> bets",
> SharePoint
http://www.microsoft.com/technet/pro...c0861881x.mspx[vbcol=seagreen]
bit[vbcol=seagreen]
by
> rating
>
Labels:
database,
full-text,
functionality,
in-depth,
microsoft,
mysql,
ofsharepoint,
oracle,
search,
server,
sharepoint,
sql
Friday, February 24, 2012
Full-Text Search and Output Parameters
Hi,
I'd like to incorporate search functionality (SQL Server 2005 Full-Text Search) into a web application, so I want to be able to return a paged list of results based on the user's search terms. I already have a parameterized stored procedure that returns a list of products when a category ID is supplied. I modified this procedure to use a different input parameter (@.SearchTerms), but I'd still like to return the number of records, as in the original stored procedure.
However, I'm getting this error: Invalid object name 'ProductEntries'.
Here's the original stored procedure:
ALTER PROCEDURE dbo.GetProductsByCategoryID
(
@.CategoryID INT,
@.PageIndex INT,
@.NumRows INT,
@.CategoryName VARCHAR(50) OUTPUT,
@.CategoryProductCount INT OUTPUT
)
AS
BEGIN
SELECT @.CategoryProductCount = (SELECT COUNT(ProductID)
FROM Products
WHERE Products.CategoryID = @.CategoryID)
SELECT @.CategoryName = (SELECT CategoryName
FROM Categories
WHERE Categories.CategoryID = @.CategoryID)
DECLARE @.startRowIndex INT;
SET @.startRowIndex = (@.PageIndex * @.NumRows) + 1;
WITH ProductEntries AS (
SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS Row, ProductID, CategoryID, Description, ProductImage, UnitCost
FROM Products
WHERE CategoryID = @.CategoryID
)
SELECT ProductID, CategoryID, Description, ProductImage, UnitCost
FROM ProductEntries
WHERE Row BETWEEN
@.startRowIndex AND @.startRowIndex + @.NumRows - 1
END
And here's the modified one:
ALTER PROCEDURE dbo.GetSearchResults
(
@.SearchTerms VARCHAR(200),
@.PageIndex INT,
@.NumRows INT,
@.ProductCount INT OUTPUT
)
AS
BEGIN
SELECT @.ProductCount = (SELECT COUNT(ProductID)
FROM ProductEntries)
DECLARE @.startRowIndex INT;
SET @.startRowIndex = (@.PageIndex * @.NumRows) + 1;
WITH ProductEntries AS (
SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS Row, ProductID, CategoryID, Description, ProductImage, UnitCost
FROM CONTAINSTABLE (Products, *, @.SearchTerms, 25) AS c, Products p
WHERE c.[KEY] = p.ProductID
)
SELECT ProductID, CategoryID, Description, ProductImage, UnitCost
FROM ProductEntries
WHERE Row BETWEEN
@.startRowIndex AND @.startRowIndex + @.NumRows - 1
END
I thought I might be getting this error because SELECT @.ProductCount occurs before the ProductEntries table is created, but when I move that SELECT statement further down, I still get the same error.
How can I get the value of @.ProductCount in this scenario so that I can display it in the UI of the web app?The first to do would be to directly query the ProductEntries table from both a Stored Procedure and then as an individual statement. In 99% of cases, this solves the problem.
A possible and common reason for your error may have to do with ownership properties of the table and their association, or lack thereof, to the account that you are using to create the stored procedure.
I haven't used SQL Server for a few months now, as I've just recently started working on an Oracle project, and I cannot recall exactly the relationship between CREATE and EXECUTE on stored procedures and which of the above explicitly allows access to the underlying objects that will be used by the stored procedure.
Regards,|||Hi Robert,
Thanks for your suggestions. Unfortunately, I could not get the procedure to work. After further research, I decided not to rely on SQL Server Full-Text Search for my site's search engine because it's really not practical in a shared hosting environment. So, I think I'll put this one to rest and check out the MSN Search SDK (http://search.msn.com/developer).
Anyway, thanks again for your reply -- it was much appreciated.
I'd like to incorporate search functionality (SQL Server 2005 Full-Text Search) into a web application, so I want to be able to return a paged list of results based on the user's search terms. I already have a parameterized stored procedure that returns a list of products when a category ID is supplied. I modified this procedure to use a different input parameter (@.SearchTerms), but I'd still like to return the number of records, as in the original stored procedure.
However, I'm getting this error: Invalid object name 'ProductEntries'.
Here's the original stored procedure:
ALTER PROCEDURE dbo.GetProductsByCategoryID
(
@.CategoryID INT,
@.PageIndex INT,
@.NumRows INT,
@.CategoryName VARCHAR(50) OUTPUT,
@.CategoryProductCount INT OUTPUT
)
AS
BEGIN
SELECT @.CategoryProductCount = (SELECT COUNT(ProductID)
FROM Products
WHERE Products.CategoryID = @.CategoryID)
SELECT @.CategoryName = (SELECT CategoryName
FROM Categories
WHERE Categories.CategoryID = @.CategoryID)
DECLARE @.startRowIndex INT;
SET @.startRowIndex = (@.PageIndex * @.NumRows) + 1;
WITH ProductEntries AS (
SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS Row, ProductID, CategoryID, Description, ProductImage, UnitCost
FROM Products
WHERE CategoryID = @.CategoryID
)
SELECT ProductID, CategoryID, Description, ProductImage, UnitCost
FROM ProductEntries
WHERE Row BETWEEN
@.startRowIndex AND @.startRowIndex + @.NumRows - 1
END
And here's the modified one:
ALTER PROCEDURE dbo.GetSearchResults
(
@.SearchTerms VARCHAR(200),
@.PageIndex INT,
@.NumRows INT,
@.ProductCount INT OUTPUT
)
AS
BEGIN
SELECT @.ProductCount = (SELECT COUNT(ProductID)
FROM ProductEntries)
DECLARE @.startRowIndex INT;
SET @.startRowIndex = (@.PageIndex * @.NumRows) + 1;
WITH ProductEntries AS (
SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS Row, ProductID, CategoryID, Description, ProductImage, UnitCost
FROM CONTAINSTABLE (Products, *, @.SearchTerms, 25) AS c, Products p
WHERE c.[KEY] = p.ProductID
)
SELECT ProductID, CategoryID, Description, ProductImage, UnitCost
FROM ProductEntries
WHERE Row BETWEEN
@.startRowIndex AND @.startRowIndex + @.NumRows - 1
END
I thought I might be getting this error because SELECT @.ProductCount occurs before the ProductEntries table is created, but when I move that SELECT statement further down, I still get the same error.
How can I get the value of @.ProductCount in this scenario so that I can display it in the UI of the web app?The first to do would be to directly query the ProductEntries table from both a Stored Procedure and then as an individual statement. In 99% of cases, this solves the problem.
A possible and common reason for your error may have to do with ownership properties of the table and their association, or lack thereof, to the account that you are using to create the stored procedure.
I haven't used SQL Server for a few months now, as I've just recently started working on an Oracle project, and I cannot recall exactly the relationship between CREATE and EXECUTE on stored procedures and which of the above explicitly allows access to the underlying objects that will be used by the stored procedure.
Regards,|||Hi Robert,
Thanks for your suggestions. Unfortunately, I could not get the procedure to work. After further research, I decided not to rely on SQL Server Full-Text Search for my site's search engine because it's really not practical in a shared hosting environment. So, I think I'll put this one to rest and check out the MSN Search SDK (http://search.msn.com/developer).
Anyway, thanks again for your reply -- it was much appreciated.
Labels:
application,
database,
full-text,
functionality,
incorporate,
microsoft,
mysql,
oracle,
output,
parameters,
return,
search,
server,
sql,
web
Subscribe to:
Posts (Atom)