Showing posts with label figure. Show all posts
Showing posts with label figure. Show all posts

Friday, March 23, 2012

How many users will a single CPU handle? (relates to licensing)

To figure out which licensing option for SQL to recommend to our customers,
we need to know how many processors we need. On MSDN I ran through a couple
of capacity planning tools, but it keeps saying I need 2 CPUs, which costs
40K if you go with the Processor license (versus Server with User CALs). If i
could figure out how many users require one CPU, this would help me. You know
of any tools or capacity planning logic to help me?
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:363399
In article <C05A85E8-DF34-4EA0-82AD-7E415DF3C79C@.microsoft.com>, Emma
Nelson <EmmaNelson@.discussions.microsoft.com> wrote:

> To figure out which licensing option for SQL to recommend to our customers,
> we need to know how many processors we need. On MSDN I ran through a couple
> of capacity planning tools, but it keeps saying I need 2 CPUs, which costs
> 40K if you go with the Processor license (versus Server with User CALs). If i
> could figure out how many users require one CPU, this would help me. You know
> of any tools or capacity planning logic to help me?
This is what I used:
http://www.microsoft.com/sql/howtobuy/faq.asp
Vessel
|||"Emma Nelson" <EmmaNelson@.discussions.microsoft.com> wrote in message
news:C05A85E8-DF34-4EA0-82AD-7E415DF3C79C@.microsoft.com...
> To figure out which licensing option for SQL to recommend to our
customers,
> we need to know how many processors we need. On MSDN I ran through a
couple
> of capacity planning tools, but it keeps saying I need 2 CPUs, which costs
> 40K if you go with the Processor license (versus Server with User CALs).
Note, that's if you require Enterprise Server. Standard Server is only
$5K/cpu last I checked.

> If i
> could figure out how many users require one CPU, this would help me. You
know
> of any tools or capacity planning logic to help me?
Well, sounds like you've already done the capacity planning. Not sure what
you're looking for differently.
Perhaps there's tuning in your app you can do to be less CPU intensive?
sql

How many users will a single CPU handle? (relates to licensing)

To figure out which licensing option for SQL to recommend to our customers,
we need to know how many processors we need. On MSDN I ran through a couple
of capacity planning tools, but it keeps saying I need 2 CPUs, which costs
40K if you go with the Processor license (versus Server with User CALs). If i
could figure out how many users require one CPU, this would help me. You know
of any tools or capacity planning logic to help me?In article <C05A85E8-DF34-4EA0-82AD-7E415DF3C79C@.microsoft.com>, Emma
Nelson <EmmaNelson@.discussions.microsoft.com> wrote:
> To figure out which licensing option for SQL to recommend to our customers,
> we need to know how many processors we need. On MSDN I ran through a couple
> of capacity planning tools, but it keeps saying I need 2 CPUs, which costs
> 40K if you go with the Processor license (versus Server with User CALs). If i
> could figure out how many users require one CPU, this would help me. You know
> of any tools or capacity planning logic to help me?
This is what I used:
http://www.microsoft.com/sql/howtobuy/faq.asp
--
Vessel|||I have read through this Web site and called MS Sales reps. Question still
remains... thanks for responding though.
"Vessel" wrote:
> In article <C05A85E8-DF34-4EA0-82AD-7E415DF3C79C@.microsoft.com>, Emma
> Nelson <EmmaNelson@.discussions.microsoft.com> wrote:
> > To figure out which licensing option for SQL to recommend to our customers,
> > we need to know how many processors we need. On MSDN I ran through a couple
> > of capacity planning tools, but it keeps saying I need 2 CPUs, which costs
> > 40K if you go with the Processor license (versus Server with User CALs). If i
> > could figure out how many users require one CPU, this would help me. You know
> > of any tools or capacity planning logic to help me?
> This is what I used:
> http://www.microsoft.com/sql/howtobuy/faq.asp
> --
> Vessel
>|||"Emma Nelson" <EmmaNelson@.discussions.microsoft.com> wrote in message
news:C05A85E8-DF34-4EA0-82AD-7E415DF3C79C@.microsoft.com...
> To figure out which licensing option for SQL to recommend to our
customers,
> we need to know how many processors we need. On MSDN I ran through a
couple
> of capacity planning tools, but it keeps saying I need 2 CPUs, which costs
> 40K if you go with the Processor license (versus Server with User CALs).
Note, that's if you require Enterprise Server. Standard Server is only
$5K/cpu last I checked.
> If i
> could figure out how many users require one CPU, this would help me. You
know
> of any tools or capacity planning logic to help me?
Well, sounds like you've already done the capacity planning. Not sure what
you're looking for differently.
Perhaps there's tuning in your app you can do to be less CPU intensive?

Wednesday, March 21, 2012

How many rows can a given processor handle

Hi all,
I know this is a very general question but if anyone can give me even a
ballpark figure that would be a big help.
I have a database that in a single table has between 75 million and 150
million. These are the absolute maximum values.
The datatypes are all very simple and there is about 8 columns. This table
links to various other tables through standard relationships.
There are 6 other table.
When a join occurs, it occurs between at most, 3 tables.
My question -: Can a dual Xeon 3.06 dedicated sql server with 3Gb ram and
SCSI disks handle this dealing with 100 users at a time?
If not, could anyone suggest a spec that could
Thanks for any advice you can share
SimonWell, it all depends on what kind of joins and what other operations
needed.
Here're some estimate, 8 columns, 2 bytes each, so total 16 bytes per
row. Say it has 150 millions rows, that's
150,000,000 * 16 = 2,400,000,000 byte = 2.3 GB
Assume on average each user retrieves 1.5 millions rows (that's still
alot), then you should have enough memory to handle 100 users. CPU won't
be a problem unless you do some complicated calculations. I am more
worry about the disk performance since unless you design index right,
there may be table scans, which will really hurt the performance.
Eric Li
SQL DBA
MCDBA
Simon Harvey wrote:

> Hi all,
> I know this is a very general question but if anyone can give me even a
> ballpark figure that would be a big help.
> I have a database that in a single table has between 75 million and 150
> million. These are the absolute maximum values.
> The datatypes are all very simple and there is about 8 columns. This table
> links to various other tables through standard relationships.
> There are 6 other table.
> When a join occurs, it occurs between at most, 3 tables.
> My question -: Can a dual Xeon 3.06 dedicated sql server with 3Gb ram and
> SCSI disks handle this dealing with 100 users at a time?
> If not, could anyone suggest a spec that could
> Thanks for any advice you can share
> Simon
>|||Thanks for the reply Eric,
If anyone happens to have experience of using very large databases I would
really appreciate any information on the sort of machines that are being
used to run them
Thanks all
Simon|||here
http://www.microsoft.com/sql/evalua...ies/default.asp
Bojidar Alexandrov|||and "the one"
[url]http://www.microsoft.com/resources/casestudies/CaseStudy.asp?CaseStudyID=11205[/ur
l]
Bojidar Alexandrov|||For a large DB, like > 100 GB DB, most of the time, disk/memory is more
a concern than CPU, unless there're alot of complicated floating point
calculations, like financial risk analysis/scientific modeling,
otherwise, a 4 CPU config. should be good enough.
Memory is cheap now, get as much as you can, I was working on a 600 GB
DB before, and it run happily on a 8 GB machine.
Get SAN if you have $$$, or RAID 10, or multiple SCSI disk controllers,
partition your DB into different file groups and put different group on
different disk, if possible. There are just too many different ways, all
depend on how big your budget is.
Or you can get multiple machines, partition your DB and chain those
machines together.
There's no one-size-fit-all solution, you need a seasoned DBA to look at
your requirement.
Eric Li
SQL DBA
MCDBA
Simon Harvey wrote:

> Thanks for the reply Eric,
> If anyone happens to have experience of using very large databases I would
> really appreciate any information on the sort of machines that are being
> used to run them
> Thanks all
> Simon
>|||Thanks all.
Thats all really helpful. Cheers
Simon

How many rows can a given processor handle

Hi all,
I know this is a very general question but if anyone can give me even a
ballpark figure that would be a big help.
I have a database that in a single table has between 75 million and 150
million. These are the absolute maximum values.
The datatypes are all very simple and there is about 8 columns. This table
links to various other tables through standard relationships.
There are 6 other table.
When a join occurs, it occurs between at most, 3 tables.
My question -: Can a dual Xeon 3.06 dedicated sql server with 3Gb ram and
SCSI disks handle this dealing with 100 users at a time?
If not, could anyone suggest a spec that could
Thanks for any advice you can share
Simon
Well, it all depends on what kind of joins and what other operations
needed.
Here're some estimate, 8 columns, 2 bytes each, so total 16 bytes per
row. Say it has 150 millions rows, that's
150,000,000 * 16 = 2,400,000,000 byte = 2.3 GB
Assume on average each user retrieves 1.5 millions rows (that's still
alot), then you should have enough memory to handle 100 users. CPU won't
be a problem unless you do some complicated calculations. I am more
worry about the disk performance since unless you design index right,
there may be table scans, which will really hurt the performance.
Eric Li
SQL DBA
MCDBA
Simon Harvey wrote:

> Hi all,
> I know this is a very general question but if anyone can give me even a
> ballpark figure that would be a big help.
> I have a database that in a single table has between 75 million and 150
> million. These are the absolute maximum values.
> The datatypes are all very simple and there is about 8 columns. This table
> links to various other tables through standard relationships.
> There are 6 other table.
> When a join occurs, it occurs between at most, 3 tables.
> My question -: Can a dual Xeon 3.06 dedicated sql server with 3Gb ram and
> SCSI disks handle this dealing with 100 users at a time?
> If not, could anyone suggest a spec that could
> Thanks for any advice you can share
> Simon
>
|||Thanks for the reply Eric,
If anyone happens to have experience of using very large databases I would
really appreciate any information on the sort of machines that are being
used to run them
Thanks all
Simon
|||here
http://www.microsoft.com/sql/evaluat...es/default.asp
Bojidar Alexandrov
|||and "the one"
http://www.microsoft.com/resources/c...eStudyID=11205
Bojidar Alexandrov
|||For a large DB, like > 100 GB DB, most of the time, disk/memory is more
a concern than CPU, unless there're alot of complicated floating point
calculations, like financial risk analysis/scientific modeling,
otherwise, a 4 CPU config. should be good enough.
Memory is cheap now, get as much as you can, I was working on a 600 GB
DB before, and it run happily on a 8 GB machine.
Get SAN if you have $$$, or RAID 10, or multiple SCSI disk controllers,
partition your DB into different file groups and put different group on
different disk, if possible. There are just too many different ways, all
depend on how big your budget is.
Or you can get multiple machines, partition your DB and chain those
machines together.
There's no one-size-fit-all solution, you need a seasoned DBA to look at
your requirement.
Eric Li
SQL DBA
MCDBA
Simon Harvey wrote:

> Thanks for the reply Eric,
> If anyone happens to have experience of using very large databases I would
> really appreciate any information on the sort of machines that are being
> used to run them
> Thanks all
> Simon
>
|||Thanks all.
Thats all really helpful. Cheers
Simon

How many rows can a given processor handle

Hi all,
I know this is a very general question but if anyone can give me even a
ballpark figure that would be a big help.
I have a database that in a single table has between 75 million and 150
million. These are the absolute maximum values.
The datatypes are all very simple and there is about 8 columns. This table
links to various other tables through standard relationships.
There are 6 other table.
When a join occurs, it occurs between at most, 3 tables.
My question -: Can a dual Xeon 3.06 dedicated sql server with 3Gb ram and
SCSI disks handle this dealing with 100 users at a time?
If not, could anyone suggest a spec that could
Thanks for any advice you can share
SimonWell, it all depends on what kind of joins and what other operations
needed.
Here're some estimate, 8 columns, 2 bytes each, so total 16 bytes per
row. Say it has 150 millions rows, that's
150,000,000 * 16 = 2,400,000,000 byte = 2.3 GB
Assume on average each user retrieves 1.5 millions rows (that's still
alot), then you should have enough memory to handle 100 users. CPU won't
be a problem unless you do some complicated calculations. I am more
worry about the disk performance since unless you design index right,
there may be table scans, which will really hurt the performance.
Eric Li
SQL DBA
MCDBA
Simon Harvey wrote:
> Hi all,
> I know this is a very general question but if anyone can give me even a
> ballpark figure that would be a big help.
> I have a database that in a single table has between 75 million and 150
> million. These are the absolute maximum values.
> The datatypes are all very simple and there is about 8 columns. This table
> links to various other tables through standard relationships.
> There are 6 other table.
> When a join occurs, it occurs between at most, 3 tables.
> My question -: Can a dual Xeon 3.06 dedicated sql server with 3Gb ram and
> SCSI disks handle this dealing with 100 users at a time?
> If not, could anyone suggest a spec that could
> Thanks for any advice you can share
> Simon
>|||Thanks for the reply Eric,
If anyone happens to have experience of using very large databases I would
really appreciate any information on the sort of machines that are being
used to run them
Thanks all
Simon|||here
http://www.microsoft.com/sql/evaluation/casestudies/default.asp
Bojidar Alexandrov|||and "the one"
http://www.microsoft.com/resources/casestudies/CaseStudy.asp?CaseStudyID=11205
Bojidar Alexandrov|||For a large DB, like > 100 GB DB, most of the time, disk/memory is more
a concern than CPU, unless there're alot of complicated floating point
calculations, like financial risk analysis/scientific modeling,
otherwise, a 4 CPU config. should be good enough.
Memory is cheap now, get as much as you can, I was working on a 600 GB
DB before, and it run happily on a 8 GB machine.
Get SAN if you have $$$, or RAID 10, or multiple SCSI disk controllers,
partition your DB into different file groups and put different group on
different disk, if possible. There are just too many different ways, all
depend on how big your budget is.
Or you can get multiple machines, partition your DB and chain those
machines together.
There's no one-size-fit-all solution, you need a seasoned DBA to look at
your requirement.
--
Eric Li
SQL DBA
MCDBA
Simon Harvey wrote:
> Thanks for the reply Eric,
> If anyone happens to have experience of using very large databases I would
> really appreciate any information on the sort of machines that are being
> used to run them
> Thanks all
> Simon
>|||Thanks all.
Thats all really helpful. Cheers
Simonsql

Friday, February 24, 2012

How I join in this case?

Hello
I would like to join Stats.ClientID with Clients.ClientID using an inner
join to get hold of Clients.Client
but I cant figure out how....... (thanks to Rebecca for providing help
earlier)
SELECT
MonthAdded AS StartDate
,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
,ClientID
,Total AS Visitors
FROM
(
SELECT
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121 ) AS
MonthAdded
,Stats.ClientID
,COUNT(*) AS Total
FROM
Stats
GROUP BY
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121)
, Stats.ClientID
) vwResultsLike this perhaps:
SELECT
MonthAdded AS StartDate
,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
,C.ClientID
,Total AS Visitors
FROM
(...
... ) vwResults
JOIN Clients AS C
ON vwResults.client_id = C.client_id ;
David Portas
SQL Server MVP
--|||Try this:
SELECT
Client
,MonthAdded AS StartDate
,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
,ClientID
,Total AS Visitors
FROM
(
SELECT
Clients.Client,
CONVERT(DATETIME,CONVERT(CHAR(7),stat.Created,121) + '-01 23:59:59',121
) AS
MonthAdded
,Stats.ClientID
,COUNT(*) AS Total
FROM
Stats, Client
WHERE
Stats.ClientID = Clients.ClientID
GROUP BY
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121)
, Stats.ClientID
, Clients.Client
) vwResults|||ahh thx...
doh, more coffee for me
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128499953.118747.188950@.g44g2000cwa.googlegroups.com...
> Like this perhaps:
> SELECT
> MonthAdded AS StartDate
> ,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
> ,C.ClientID
> ,Total AS Visitors
> FROM
> (...
> ... ) vwResults
> JOIN Clients AS C
> ON vwResults.client_id = C.client_id ;
> --
> David Portas
> SQL Server MVP
> --
>|||If you want the end date to have the last part of the day,
Take off the time part from the convert, and take away 3ms from the EndDate.
This will give you the full scope of the month and not miss all but 3ms of
the StartDay.
SELECT
MonthAdded AS StartDate
,DATEADD(ms,-3 ,DATEADD(m,1,MonthAdded)) AS EndDate
,ClientID
,Total AS Visitors
FROM
(
SELECT
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121 ) AS
MonthAdded
,Stats.ClientID
,COUNT(*) AS Total
FROM
Stats
GROUP BY
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121)
, Stats.ClientID
) vwResults
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23la1mJYyFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hello
> I would like to join Stats.ClientID with Clients.ClientID using an inner
> join to get hold of Clients.Client
> but I cant figure out how....... (thanks to Rebecca for providing help
> earlier)
>
> SELECT
> MonthAdded AS StartDate
> ,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
> ,ClientID
> ,Total AS Visitors
> FROM
> (
> SELECT
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121 )
AS
> MonthAdded
> ,Stats.ClientID
> ,COUNT(*) AS Total
> FROM
> Stats
> GROUP BY
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121)
> , Stats.ClientID
> ) vwResults
>|||Hi again Rebecca
I got to work now perfectly, but now I need to add another view.... that
shows for "each w" (and works even next year) :S
Tried some but it left a mess......
is it possible to do such grouping with some modifications?
so startdate is (for this w as an example)
2005-10-03 2005-10-09
TIA
/Lasse
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4343a346$0$134$7b0f0fd3@.mistral.news.newnet.co.uk...
> If you want the end date to have the last part of the day,
> Take off the time part from the convert, and take away 3ms from the
EndDate.
> This will give you the full scope of the month and not miss all but 3ms of
> the StartDay.
>
> SELECT
> MonthAdded AS StartDate
> ,DATEADD(ms,-3 ,DATEADD(m,1,MonthAdded)) AS EndDate
> ,ClientID
> ,Total AS Visitors
> FROM
> (
> SELECT
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121 ) AS
> MonthAdded
> ,Stats.ClientID
> ,COUNT(*) AS Total
> FROM
> Stats
> GROUP BY
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121)
> , Stats.ClientID
> ) vwResults
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:%23la1mJYyFHA.3236@.TK2MSFTNGP14.phx.gbl...
23:59:59',121 )
> AS
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. You have been toldthis several times in this
newsgroup.
Instead of computing things like this, why don't you build a reporting
calendar table with date ranges? Instead of storing temporal data in
strings, why don't you use temproal data types? Oh, the standard
syntax in SQL is CAST(), not CONVERT(). The prefix "vw" is eitehr a
short hand for "VolksWagen" or a violationof ISO-11179 rules for naming
data elements.|||easier with "testing-code" i guess
CREATE TABLE #Test (
SomePk int identity(1,1) NOT NULL,
Person char(1) NOT NULL,
Datecreated datetime NOT NULL
)
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-03')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-05')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-06')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-11')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-14')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-15')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-16')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-10-03')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-10-04')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-10-05')
SELECT * FROM #Test
/*
Desired result:
Startdate Enddate Person Count
2005-10-03 2005-10-09 A 5
2005-10-10 2005-10-16 A 4
2005-10-17 2005-10-23 B 3
or if above is too complicated/impossible:
Year (?) Month (?) Person Count
2005 40 A 5
2005 41 A 4
2005 40 B 3
*/
DROP TABLE #Test
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1128607316.078911.121310@.z14g2000cwz.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. You have been toldthis several times in this
> newsgroup.
> Instead of computing things like this, why don't you build a reporting
> calendar table with date ranges? Instead of storing temporal data in
> strings, why don't you use temproal data types? Oh, the standard
> syntax in SQL is CAST(), not CONVERT(). The prefix "vw" is eitehr a
> short hand for "VolksWagen" or a violationof ISO-11179 rules for naming
> data elements.
>|||I don't understand why your first desired result has two rows for the
same month. Going by your second example this seems to be a mistake. I
think you want to GROUP BY DATEDIFF(MONTH, '/* some fixed date */,
datecreated). For the dates join to a Calendar table.
As posted your schema and data makes no sense. Why the duplicate rows?
You need a unique constraint on (person,datecreated).
David Portas
SQL Server MVP
--|||David,
since they are different ws. nothing to do with months
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128608758.392918.172520@.g49g2000cwa.googlegroups.com...
> I don't understand why your first desired result has two rows for the
> same month. Going by your second example this seems to be a mistake. I
> think you want to GROUP BY DATEDIFF(MONTH, '/* some fixed date */,
> datecreated). For the dates join to a Calendar table.
> As posted your schema and data makes no sense. Why the duplicate rows?
> You need a unique constraint on (person,datecreated).
> --
> David Portas
> SQL Server MVP
> --
>

How i get "rank" in this one

Hello
I have this query that works good, but I cant figure out how to get the rank
and order by it desc.
SELECT * FROM Companies WHERE CONTAINS(*,'something')
how I add rank to this one?
TIA
/Lasse
you can't you need to use the contains table syntax for this, ie
SELECT * FROM Companies join ContainsTable(Companies, *, 'something') as FT
on FT.[key]=Companies.pk
where pk is the primary key/unique key of the table you are full text
indexing.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23b1BtZ8yEHA.3808@.tk2msftngp13.phx.gbl...
> Hello
> I have this query that works good, but I cant figure out how to get the
rank
> and order by it desc.
> SELECT * FROM Companies WHERE CONTAINS(*,'something')
> how I add rank to this one?
> TIA
> /Lasse
>
|||Hilary,
hmm, one problem, when i search for both Firstname and Lastname using OR it
doesnt really get the "most correct" values first, they get the highest rank
but...... and if I use AND nothing shows since no columns contain all
"keywords"
is there a way to solve this?
/Lasse
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uTfQ9M9yEHA.1196@.TK2MSFTNGP15.phx.gbl...
> you can't you need to use the contains table syntax for this, ie
> SELECT * FROM Companies join ContainsTable(Companies, *, 'something') as
FT
> on FT.[key]=Companies.pk
> where pk is the primary key/unique key of the table you are full text
> indexing.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:%23b1BtZ8yEHA.3808@.tk2msftngp13.phx.gbl...
> rank
>
|||Hilary,
this one returns rank=128
5472 NULL 803 21 GVLE Gvle Valbogatan 35 Andersson & Edlund Sverige
5472
this one rank=112
3714 Sandvikens Kommun Kultur och Fritid 811 80 SANDVIKEN Sandviken
Rita Andersson Sverige 3714
when i search for 'rita OR andersson'
not sure i understand how that first one got higher value
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OYVRKr9yEHA.748@.TK2MSFTNGP14.phx.gbl...
> Hilary,
> hmm, one problem, when i search for both Firstname and Lastname using OR
it
> doesnt really get the "most correct" values first, they get the highest
rank[vbcol=seagreen]
> but...... and if I use AND nothing shows since no columns contain all
> "keywords"
> is there a way to solve this?
> /Lasse
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uTfQ9M9yEHA.1196@.TK2MSFTNGP15.phx.gbl...
as[vbcol=seagreen]
> FT
the
>
|||Lasse,
Adding RANK is easy, understanding the results is a bit more difficult as
you will need to supply additional information about your environment in
order to understand the RANK values that are returned from your FTS queries.
First of all, you should review the SQL Server 2000 BOL title "Full-text
Search Recommendations" and the next to last paragraph on RANK.
Another factor is the number of rows in your table must be "statistically
significantly", i.e.. at least 10,000 rows in order for the query specific
RANK values to return the executants results. If you are testing with a very
small table, then this can explain why the rank values are not what you are
expecting.
As for searching [both Firstname and Lastname using OR it doesn't really get
the "most correct" values first, they get the highest rank
but...... and if I use AND nothing shows since no columns contain all
"keywords"], this is a different problem as SQL Server 2000 (correct?) does
not search across columns, see KB articles:
286787 (Q286787) FIX: Incorrect Results From Full-Text Search on Several
Columns
http://support.microsoft.com/default...b;en-us;286787
294809 (Q294809) FIX: Full-Text Search Queries with CONTAINS Clause Search
Across Columns
http://support.microsoft.com/default...b;en-us;294809
Note, while the above KB articles are specific to SQL Server 7.0, they also
apply to SQL Server 2000 as SQL Server 7.0's behavior was fixed to
correspond to the default SQL Server 2000 behavior.
Finally, one way to join multiple columns in one CONTAINSTABLE query and
influence the rank value of one column over another via the weight parameter
is the following:
use Northwind
SELECT e.LastName, e.FirstName, e.Title, e.Notes, B.[KEY], B.[RANK] as
B_RANK, A.[RANK] as A_RANK
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, the AND condition between the Primary key (PK) values as you can also
make this an OR clause, but you may need to use a DISTINCT parameter to
remove duplicate results. Keep in mind, that you still need a "statistically
significantly" number of rows to get the best results.
Regards,
John
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:O#mzCy9yEHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hilary,
> this one returns rank=128
> 5472 NULL 803 21 GVLE Gvle Valbogatan 35 Andersson & Edlund
Sverige
> 5472
> this one rank=112
> 3714 Sandvikens Kommun Kultur och Fritid 811 80 SANDVIKEN Sandviken
> Rita Andersson Sverige 3714
> when i search for 'rita OR andersson'
> not sure i understand how that first one got higher value
>
>
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:OYVRKr9yEHA.748@.TK2MSFTNGP14.phx.gbl...
> it
> rank
> as
> the
>