Need help 2

Need Help 2
I am currently converting an access DB to MS SQL.
I am currently trying to figure out how to code this
I need the count of all rows in colum TERM that is > 1 when its grouped by the COLUM NAME
Anyone got any Ideas,I do not know the specifics of MS SQL, however a general approach to your problem might be

select column, count(*)
from table
where term > 1
group by student;

Need Help 2

I am currently converting an access DB to MS SQL.
I am currently trying to figure out how to code this
I need the count of all rows in colum TERM that is > 1 when its grouped by the COLUM NAME
Anyone got any Ideas,i'm not sure wheter i get it right.

hope that it was the thing that you ask for.

Select TERM,Count(<table key>) as Cnt from <Table \name>
Wher TERM > 1
Group by TERM.

If I didn't get it right then just forget about it.
:D|||select count(*) from tbl
where term in
(
select term from tbl group by name having count(*) > 1
)

Need Extra Rows

Here is the basic sql I am trying to implement:
select classid, count(*) as [COUNT], dtmready from unit
where rmpropid = '123'
group by classid, dtmready
order by dtmready;
Here is my result set:
A1 3 2006-07-01 00:00:00.000 LUP
A1 10 2006-08-15 00:00:00.000 LUP
A1 11 2006-09-15 00:00:00.000 LUP
A1 10 2006-10-15 00:00:00.000 LUP
A1 10 2006-11-01 00:00:00.000 LUP
A1 10 2006-11-30 00:00:00.000 LUP
A1$ 2 2006-11-01 00:00:00.000 LUP
A1$ 2 2006-11-30 00:00:00.000 LUP
A2$ 3 2006-07-01 00:00:00.000 LUP
A3$ 3 2006-08-15 00:00:00.000 LUP
A3$ 2 2006-09-15 00:00:00.000 LUP
A3$ 2 2006-10-15 00:00:00.000 LUP
B1 1 2006-04-14 16:50:46.910 OTHER
B1 5 2006-07-01 00:00:00.000 LUP
B1 26 2006-08-15 00:00:00.000 LUP
B1 24 2006-09-15 00:00:00.000 LUP
B1 25 2006-10-15 00:00:00.000 LUP
B1 10 2006-11-01 00:00:00.000 LUP
B1 8 2006-11-30 00:00:00.000 LUP
B1$ 3 2006-09-15 00:00:00.000 LUP
B1$ 4 2006-10-15 00:00:00.000 LUP
B1$ 2 2006-11-01 00:00:00.000 LUP
B1$ 4 2006-11-30 00:00:00.000 LUP
B2$ 5 2006-08-15 00:00:00.000 LUP
B2$ 3 2006-09-15 00:00:00.000 LUP
B2$ 1 2006-10-15 00:00:00.000 LUP
B3$ 1 2006-09-15 00:00:00.000 LUP
B3$ 2 2006-10-15 00:00:00.000 LUP
T1 3 2006-05-19 00:00:00.000 LUP
T1 7 2006-06-30 00:00:00.000 LUP
T1$ 2 2006-06-30 00:00:00.000 LUP
If you notice for the most classids, the earliest dtmready is > today. What
I
need is to return an additional row when the earliest dtmready is after toda
y.
The desired rows would be:
A1 0 (today's date)
etc
Background: I am running SQL Server 2000 SP4 and the results of the query ar
e
returned to a Java program at a level where I do not have the ability to
create a new row. So, it would be ideal if I could create the sql that
returns a row with a dtmready of today with a count of 0 for each classid
that has a minimum dtmready > today.Hi
The easiest way to do this is to use a calendar table see
http://www.aspfaq.com/show.asp?id=2519 then your query would be
SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
CROSS JOIN dbo.calendar c
LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
u.rmpropid = '123'
GROUP BY v.classid, c.dt
ORDER BY c.dt
The cross join will get all date and classid combinations.
John
"michaelloveusa" <u20878@.uwe> wrote in message news:5ec904b492d8c@.uwe...
> Here is the basic sql I am trying to implement:
> select classid, count(*) as [COUNT], dtmready from unit
> where rmpropid = '123'
> group by classid, dtmready
> order by dtmready;
> Here is my result set:
> A1 3 2006-07-01 00:00:00.000 LUP
> A1 10 2006-08-15 00:00:00.000 LUP
> A1 11 2006-09-15 00:00:00.000 LUP
> A1 10 2006-10-15 00:00:00.000 LUP
> A1 10 2006-11-01 00:00:00.000 LUP
> A1 10 2006-11-30 00:00:00.000 LUP
> A1$ 2 2006-11-01 00:00:00.000 LUP
> A1$ 2 2006-11-30 00:00:00.000 LUP
> A2$ 3 2006-07-01 00:00:00.000 LUP
> A3$ 3 2006-08-15 00:00:00.000 LUP
> A3$ 2 2006-09-15 00:00:00.000 LUP
> A3$ 2 2006-10-15 00:00:00.000 LUP
> B1 1 2006-04-14 16:50:46.910 OTHER
> B1 5 2006-07-01 00:00:00.000 LUP
> B1 26 2006-08-15 00:00:00.000 LUP
> B1 24 2006-09-15 00:00:00.000 LUP
> B1 25 2006-10-15 00:00:00.000 LUP
> B1 10 2006-11-01 00:00:00.000 LUP
> B1 8 2006-11-30 00:00:00.000 LUP
> B1$ 3 2006-09-15 00:00:00.000 LUP
> B1$ 4 2006-10-15 00:00:00.000 LUP
> B1$ 2 2006-11-01 00:00:00.000 LUP
> B1$ 4 2006-11-30 00:00:00.000 LUP
> B2$ 5 2006-08-15 00:00:00.000 LUP
> B2$ 3 2006-09-15 00:00:00.000 LUP
> B2$ 1 2006-10-15 00:00:00.000 LUP
> B3$ 1 2006-09-15 00:00:00.000 LUP
> B3$ 2 2006-10-15 00:00:00.000 LUP
> T1 3 2006-05-19 00:00:00.000 LUP
> T1 7 2006-06-30 00:00:00.000 LUP
> T1$ 2 2006-06-30 00:00:00.000 LUP
> If you notice for the most classids, the earliest dtmready is > today.
> What I
> need is to return an additional row when the earliest dtmready is after
> today.
> The desired rows would be:
> A1 0 (today's date)
> etc
> Background: I am running SQL Server 2000 SP4 and the results of the query
> are
> returned to a Java program at a level where I do not have the ability to
> create a new row. So, it would be ideal if I could create the sql that
> returns a row with a dtmready of today with a count of 0 for each classid
> that has a minimum dtmready > today.|||Hi John. Thanks for the response. I do not think I will be allowed to create
a calendar table (at least in ant reasonable amount of time), so do you have
any other ideas on how this might be accomplished?
Mike
John Bell wrote:[vbcol=seagreen]
>Hi
>The easiest way to do this is to use a calendar table see
>http://www.aspfaq.com/show.asp?id=2519 then your query would be
>SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
>FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
>CROSS JOIN dbo.calendar c
>LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
>u.rmpropid = '123'
>GROUP BY v.classid, c.dt
>ORDER BY c.dt
>The cross join will get all date and classid combinations.
>John
>
>[quoted text clipped - 52 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1|||Hi
You could do it as a temporary or derived table, but this would be extra
processing.
John
"michaelloveusa via droptable.com" <u20878@.uwe> wrote in message
news:5eeb370967a4c@.uwe...
> Hi John. Thanks for the response. I do not think I will be allowed to
> create
> a calendar table (at least in ant reasonable amount of time), so do you
> have
> any other ideas on how this might be accomplished?
> Mike
> John Bell wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1|||Thanks for your help John. I figured out a way to do it with a union.
Mike
John Bell wrote:[vbcol=seagreen]
>Hi
>You could do it as a temporary or derived table, but this would be extra
>processing.
>John
>
>[quoted text clipped - 27 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1

Need Extra Rows

Here is the basic sql I am trying to implement:
select classid, count(*) as [COUNT], dtmready from unit
where rmpropid = '123'
group by classid, dtmready
order by dtmready;
Here is my result set:
A1 3 2006-07-01 00:00:00.000 LUP
A1 10 2006-08-15 00:00:00.000 LUP
A1 11 2006-09-15 00:00:00.000 LUP
A1 10 2006-10-15 00:00:00.000 LUP
A1 10 2006-11-01 00:00:00.000 LUP
A1 10 2006-11-30 00:00:00.000 LUP
A1$ 2 2006-11-01 00:00:00.000 LUP
A1$ 2 2006-11-30 00:00:00.000 LUP
A2$ 3 2006-07-01 00:00:00.000 LUP
A3$ 3 2006-08-15 00:00:00.000 LUP
A3$ 2 2006-09-15 00:00:00.000 LUP
A3$ 2 2006-10-15 00:00:00.000 LUP
B1 1 2006-04-14 16:50:46.910 OTHER
B1 5 2006-07-01 00:00:00.000 LUP
B1 26 2006-08-15 00:00:00.000 LUP
B1 24 2006-09-15 00:00:00.000 LUP
B1 25 2006-10-15 00:00:00.000 LUP
B1 10 2006-11-01 00:00:00.000 LUP
B1 8 2006-11-30 00:00:00.000 LUP
B1$ 3 2006-09-15 00:00:00.000 LUP
B1$ 4 2006-10-15 00:00:00.000 LUP
B1$ 2 2006-11-01 00:00:00.000 LUP
B1$ 4 2006-11-30 00:00:00.000 LUP
B2$ 5 2006-08-15 00:00:00.000 LUP
B2$ 3 2006-09-15 00:00:00.000 LUP
B2$ 1 2006-10-15 00:00:00.000 LUP
B3$ 1 2006-09-15 00:00:00.000 LUP
B3$ 2 2006-10-15 00:00:00.000 LUP
T1 3 2006-05-19 00:00:00.000 LUP
T1 7 2006-06-30 00:00:00.000 LUP
T1$ 2 2006-06-30 00:00:00.000 LUP
If you notice for the most classids, the earliest dtmready is > today. What I
need is to return an additional row when the earliest dtmready is after today.
The desired rows would be:
A1 0 (today's date)
etc
Background: I am running SQL Server 2000 SP4 and the results of the query are
returned to a java program at a level where I do not have the ability to
create a new row. So, it would be ideal if I could create the sql that
returns a row with a dtmready of today with a count of 0 for each classid
that has a minimum dtmready > today.Hi
The easiest way to do this is to use a calendar table see
http://www.aspfaq.com/show.asp?id=2519 then your query would be
SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
CROSS JOIN dbo.calendar c
LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
u.rmpropid = '123'
GROUP BY v.classid, c.dt
ORDER BY c.dt
The cross join will get all date and classid combinations.
John
"michaelloveusa" <u20878@.uwe> wrote in message news:5ec904b492d8c@.uwe...
> Here is the basic sql I am trying to implement:
> select classid, count(*) as [COUNT], dtmready from unit
> where rmpropid = '123'
> group by classid, dtmready
> order by dtmready;
> Here is my result set:
> A1 3 2006-07-01 00:00:00.000 LUP
> A1 10 2006-08-15 00:00:00.000 LUP
> A1 11 2006-09-15 00:00:00.000 LUP
> A1 10 2006-10-15 00:00:00.000 LUP
> A1 10 2006-11-01 00:00:00.000 LUP
> A1 10 2006-11-30 00:00:00.000 LUP
> A1$ 2 2006-11-01 00:00:00.000 LUP
> A1$ 2 2006-11-30 00:00:00.000 LUP
> A2$ 3 2006-07-01 00:00:00.000 LUP
> A3$ 3 2006-08-15 00:00:00.000 LUP
> A3$ 2 2006-09-15 00:00:00.000 LUP
> A3$ 2 2006-10-15 00:00:00.000 LUP
> B1 1 2006-04-14 16:50:46.910 OTHER
> B1 5 2006-07-01 00:00:00.000 LUP
> B1 26 2006-08-15 00:00:00.000 LUP
> B1 24 2006-09-15 00:00:00.000 LUP
> B1 25 2006-10-15 00:00:00.000 LUP
> B1 10 2006-11-01 00:00:00.000 LUP
> B1 8 2006-11-30 00:00:00.000 LUP
> B1$ 3 2006-09-15 00:00:00.000 LUP
> B1$ 4 2006-10-15 00:00:00.000 LUP
> B1$ 2 2006-11-01 00:00:00.000 LUP
> B1$ 4 2006-11-30 00:00:00.000 LUP
> B2$ 5 2006-08-15 00:00:00.000 LUP
> B2$ 3 2006-09-15 00:00:00.000 LUP
> B2$ 1 2006-10-15 00:00:00.000 LUP
> B3$ 1 2006-09-15 00:00:00.000 LUP
> B3$ 2 2006-10-15 00:00:00.000 LUP
> T1 3 2006-05-19 00:00:00.000 LUP
> T1 7 2006-06-30 00:00:00.000 LUP
> T1$ 2 2006-06-30 00:00:00.000 LUP
> If you notice for the most classids, the earliest dtmready is > today.
> What I
> need is to return an additional row when the earliest dtmready is after
> today.
> The desired rows would be:
> A1 0 (today's date)
> etc
> Background: I am running SQL Server 2000 SP4 and the results of the query
> are
> returned to a java program at a level where I do not have the ability to
> create a new row. So, it would be ideal if I could create the sql that
> returns a row with a dtmready of today with a count of 0 for each classid
> that has a minimum dtmready > today.|||Hi John. Thanks for the response. I do not think I will be allowed to create
a calendar table (at least in ant reasonable amount of time), so do you have
any other ideas on how this might be accomplished?
Mike
John Bell wrote:
>Hi
>The easiest way to do this is to use a calendar table see
>http://www.aspfaq.com/show.asp?id=2519 then your query would be
>SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
>FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
>CROSS JOIN dbo.calendar c
>LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
>u.rmpropid = '123'
>GROUP BY v.classid, c.dt
>ORDER BY c.dt
>The cross join will get all date and classid combinations.
>John
>> Here is the basic sql I am trying to implement:
>[quoted text clipped - 52 lines]
>> returns a row with a dtmready of today with a count of 0 for each classid
>> that has a minimum dtmready > today.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Hi
You could do it as a temporary or derived table, but this would be extra
processing.
John
"michaelloveusa via SQLMonster.com" <u20878@.uwe> wrote in message
news:5eeb370967a4c@.uwe...
> Hi John. Thanks for the response. I do not think I will be allowed to
> create
> a calendar table (at least in ant reasonable amount of time), so do you
> have
> any other ideas on how this might be accomplished?
> Mike
> John Bell wrote:
>>Hi
>>The easiest way to do this is to use a calendar table see
>>http://www.aspfaq.com/show.asp?id=2519 then your query would be
>>SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
>>FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
>>CROSS JOIN dbo.calendar c
>>LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
>>u.rmpropid = '123'
>>GROUP BY v.classid, c.dt
>>ORDER BY c.dt
>>The cross join will get all date and classid combinations.
>>John
>> Here is the basic sql I am trying to implement:
>>[quoted text clipped - 52 lines]
>> returns a row with a dtmready of today with a count of 0 for each
>> classid
>> that has a minimum dtmready > today.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Thanks for your help John. I figured out a way to do it with a union.
Mike
John Bell wrote:
>Hi
>You could do it as a temporary or derived table, but this would be extra
>processing.
>John
>> Hi John. Thanks for the response. I do not think I will be allowed to
>> create
>[quoted text clipped - 27 lines]
>> classid
>> that has a minimum dtmready > today.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1

Need distinct rows from table based on (col1) only - overall required multiple c

Need rows of specific table having distinct col1, but along with col1 I also need col2,col3, .... But distinct is required on only col1.

I tried ---
Select col1, col2, ... , coln from table1 where ( select distinct col1 from table1)

But result contain repetitive col1 rows due to wrong syntax of mine. Also tried exist, union, right join but failed finally ....

give your some site:
one is mser.net,another is msdnx.com,search by google in the site....

Need basic SQL query help

Trying to select unique records from a database. A combination of 2 variables make the record unique. Need to export all rows of the return. I have below rudimentary sql skills (select/where) and cannot figure this one out. The database has ~ 180k rows, of which there are only ~ 50k unique records.

The data is on a single table, 15 seperate columns. It is a table containing network router data. The existing table set up shows the historical progression of changes/updates to these routers. I am trying to determine the latest records of the routers in question. Since some of the IP addresses have been used multiple times (moved from site A to site B for example), there are multiple dupe records that I do not want.
The two fields I need to use to identify unique records are:
serial_IP and site_id
In addition to that, I would also like to know how to format "max date" query, so that I get the latest information. I can do that manually if I must via a sort when I export the data, but it would be nice to know how to do that in the future.

thanks

noclueAs those two columns make a unique key on the table, there is possibility of use of a DISTINCT keyword, such as

SELECT DISTINCT serial_IP, site_id FROM your_table;

However, regarding your next request (selecting records with the latest date), there's need to use a subquery:SELECT DISTINCT t.serial_IP, t.site_id
FROM your_table t
WHERE t.date_column = (SELECT max(t1.date_column)
FROM your_table t1
WHERE t1.serial_IP = t.serial_IP
AND t1.site_id = t.site_id
);The DISTINCT keyword might, or might not be needed in this case: if table has only one record per "date_column", you won't need that. If there are multiple records per every "date_column" value, you'll still need it to eliminate multiple records.

Need all rows within each group to be shown on 1 page

Hi,

I have data that is grouped by a code number. One of the code numbers have over 600 rows, while other code numbers have around 10 to 20 rows within it.

When I run the report the code number that has over 600 rows gets split over 2 pages while the other code numbers each get their own page.

How do I make it so that when I run the report the code number that has over 600 rows gets all displayed on the 1 page instead of being split over 2?

Thanks

Ben.

can anyone help please?|||

Hi ben22,

I'm afraid there is not really a functionality in Reporting Services that does this. I know this is a lack in Reporting Services.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi Ben

Do you just want it to display on 1 page in the browser or
do you require this for printing aswell?

If it is for viewing purposes only, you can try to set the
'Interactive Page size' in the report properties to have a higher width.

G

|||need it both for display in the browser and for printing, as when it gets exported to excell it needs to be in the same sheet instead of being expanded over 2. This is because a certain value in the row gets summed at the end and I need that sum to include all the rows within the group, as when it splits over 2 pages I get two different sums where they should be altogether.|||

Hi ben,

As mentioned before, it is not possible to make the page size dynamic for printing and viewing at the same time.

You mentioned that you receive 2 sum results. It is possible to only have only a total line at the end in case of on each page.

For example:
When you use a Table you can set the total in the Table footer. Even if the table overlaps multiple pages, it will only be displayed at the last page.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Sorry, I should of explain things a bit better before. By groups I meant I have 1 group in the table. This group breaks the data up by the unique field assigned to each data item and uses a group footer to do the sum. For example:

I have 800 cars, 600 are red 100 are blue and 100 are green. So when I run the report is looks like this

Page 1

RED

Name | Model | Price

Car1 | c1 | 20000

Car2 | c2 | 30000

…

Car400 | c400 | 15000

-

Total Price: 3000000

Page 2

RED

Name | Model | Price

Car401 | c401 | 20000

Car402 | c402 | 30000

…

Car600 | c600 | 15000

-

Total Price: 1500000

Page 3

BLUE

Name | Model | Price

Car1 | c1 | 10000

Car2 | c2 | 30000

…

Car100 | c100 | 40000

-

Total Price: 1000000

Page 4

GREEN

Name | Model | Price

Car1 | c1 | 25000

Car2 | c2 | 17000

…

Car100 | c100 | 40000

-

Total Price: 1200000

As you can see there are 4 pages. It splits the RED cars up over 2 pages giving the 2 different sums ie: cars 1 – 400 with the sum 3000000 and then cars 401 – 600 with the sum 1500000. I want my report to work so that the RED cars are all together and not on different pages, so altogether there are only 3 pages. So to look like this:

Page 1

RED

Name | Model | Price

Car1 | c1 | 20000

Car2 | c2 | 30000

…

Car600 | c600 | 15000

-

Total Price: 4500000

Page 2

BLUE

Name | Model | Price

Car1 | c1 | 10000

Car2 | c2 | 30000

…

Car100 | c100 | 40000

-

Total Price: 1000000

Page 3

GREEN

Name | Model | Price

Car1 | c1 | 25000

Car2 | c2 | 17000

…

Car100 | c100 | 40000

-

Total Price: 1200000

|||

Hi,

It is not possible to force a group on 1 page but it is possible to have only 1 total per group. You need to set the Color field in the expresion of the group. When you then look at the group footer, you will see that the total is done on group base.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Geert Verhoeven wrote:

It is not possible to force a group on 1 page but it is possible to have only 1 total per group. You need to set the Color field in the expresion of the group. When you then look at the group footer, you will see that the total is done on group base.

Sorry but I dont really understand what you have said or how to implement it

|||

Hi,

What I mean is that you can not force the contents of a group to be on 1 page. Although the problem you have that the total is displayed on each page can be avoided by using the footer of the group. When you set the total in the group of the footer, only at the end of the group a total will be displayed and not at the end of each page.

Hope this is more clear.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||im using a group a footer and it still displays the total at the end of each page, not at the end of the group

Need alittle help with sql statemtn stored procedure

Here is what I am trying to do, If i type to query the stored procedure with two parameters and the two parameters are null, return all rows, else do the query based on the two parameters. Thanks in advance.

Create PROCEDURE [dbo].[Admin_RetrieveCustomerOrders] @.Yearnumint,@.monthnumintASBEGINSET NOCOUNT ON;SELECT o.intOrderId, o.vcCustomerID, o.decTotal, o.dtOrdered,u.vcFirstName, u.vcLastName,(SELECTCOUNT(d.intOrderID)FROM tblOrderDetails dWHERE d.intOrderID = o.intOrderID)AS TotalProductsFROM tblOrders oINNERJOIN tblUserInformation uON o.vcCustomerID = u.vcCustomerIDWHERE YEAR(o.dtOrdered)=@.yearnumANDMONTH(o.dtOrdered)=@.monthnumORDER BY o.dtOrderedDESC END

Thanks Again

Joshua

I think this may work

Essentially you need to modify you WHERE clause to also allow null for the parameters

WHERE (YEAR(o.dtOrdered)=@.yearnumOR @.yearnumisNULL)AND (MONTH(o.dtOrdered)=@.monthnumOR @.monthnumisNULL)

Give the above ago and let us know if it works. 

Need advice on searching text fields

I have a table with 2 million + rows. I need to allow the user to do a quer
y
such as is shown below. I have full text search enabled on this database,
and have built a text search catalog for it. The problem is, that I keep
getting timeouts on the query. Granted, I can increase the timout value to
a
value that works, but I need this to come back fairly quick. I need advice
on how to set up this application. Think of eBay. You can search on any
term. They have to have millions of rows in their database also. How do
they return results so quick? They have to be doing very similar query logi
c?
Select * From [Table] Where ([Field1] + [Field2] Like '%term1%')
AND ([Field1] + [Field2] Like '%term2%')Does the LIKE operator utilize the full-text search engine? Perhaps you be
using the CONTAINS and FREETEXT functions instead.
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:88ECA16B-12D3-4AE9-ABDB-E666A633F381@.microsoft.com...
>I have a table with 2 million + rows. I need to allow the user to do a
>query
> such as is shown below. I have full text search enabled on this database,
> and have built a text search catalog for it. The problem is, that I keep
> getting timeouts on the query. Granted, I can increase the timout value
> to a
> value that works, but I need this to come back fairly quick. I need
> advice
> on how to set up this application. Think of eBay. You can search on any
> term. They have to have millions of rows in their database also. How do
> they return results so quick? They have to be doing very similar query
> logic?
> Select * From [Table] Where ([Field1] + [Field2] Like '%term1%')
> AND ([Field1] + [Field2] Like
> '%term2%')|||Oh, I assumed that 'Like' utilized the full text engine. Maybe I'm wrong.
I'll try the other two verbs and see what happens.
"JT" wrote:

> Does the LIKE operator utilize the full-text search engine? Perhaps you be
> using the CONTAINS and FREETEXT functions instead.
> "Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
> news:88ECA16B-12D3-4AE9-ABDB-E666A633F381@.microsoft.com...
>
>|||Perhaps you're not taking advantage of the full text search with the
LIKE and the concatenated columns.
Try using FREETEXT instead of LIKE
... where ( FREETEXT (Field1, 'term1') or FREETEXT(Field2, 'term1') )
and ( FREETEXT (Field1, 'term2') or FREETEXT(Field2, 'term2') )
Brian Kitt wrote:
> I have a table with 2 million + rows. I need to allow the user to do a qu
ery
> such as is shown below. I have full text search enabled on this database,
> and have built a text search catalog for it. The problem is, that I keep
> getting timeouts on the query. Granted, I can increase the timout value t
o a
> value that works, but I need this to come back fairly quick. I need advic
e
> on how to set up this application. Think of eBay. You can search on any
> term. They have to have millions of rows in their database also. How do
> they return results so quick? They have to be doing very similar query lo
gic?
> Select * From [Table] Where ([Field1] + [Field2] Like '%term1%')
> AND ([Field1] + [Field2] Like '%term2%')[/colo
r]|||Even with the full-text search functioning, you may not get the crisp
response time you are looking for. After all, eBay has more processing power
at their disposal than 95% of us do.
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:13DF6525-9595-41EB-B413-844EC57A09F2@.microsoft.com...
> Oh, I assumed that 'Like' utilized the full text engine. Maybe I'm wrong.
> I'll try the other two verbs and see what happens.
> "JT" wrote:
>|||Brian,
I can assure that T-SQL LIKE does NOT use the same technology as the
Full-Text Search (FTS) predicates of CONTAINS or FREETEXT !!
-- John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:13DF6525-9595-41EB-B413-844EC57A09F2@.microsoft.com...
> Oh, I assumed that 'Like' utilized the full text engine. Maybe I'm wrong.
> I'll try the other two verbs and see what happens.
> "JT" wrote:
>|||Brian,
While you may not have the processing power of eBay, you can get good
performance out of FTS with a bit of tuning on both the FT Indexing (Change
Tracking with Update Index in Background) and with using CONTAINSTABLE or
FREETEXTTABLE with the Top_N_Rank parameter and limiting your results to the
top 2000 by RANK, for example:
SELECT TOP 200 T.* FROM TableWithFTColumn as T,
CONTAINSTABLE(TableWIthFTColumn,*,'John'
,300) as CT
WHERE T.key=CT.key AND T.a > 5
ORDER BY CT.rank
Where 300 is the Top_N_by_RANK value. See KB artilce 240833 "FIX: Full-Text
Search Performance Improved via Support for TOP" at
http://support.microsoft.com//defau...kb;EN-US;240833 for more
details. Also, review SQL Server 200 Books Online (BOL) title "Full-text
Search Recommendations" for more tips!
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"JT" <someone@.microsoft.com> wrote in message
news:O2tY0S8sFHA.1940@.TK2MSFTNGP14.phx.gbl...
> Even with the full-text search functioning, you may not get the crisp
> response time you are looking for. After all, eBay has more processing
> power at their disposal than 95% of us do.
> "Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
> news:13DF6525-9595-41EB-B413-844EC57A09F2@.microsoft.com...
>

Need advice on Designing a VLDB OLTP Database

Hi,
I am trying to design a sql2005 Database with 4 tables of 35 million
rows. we need to update fields in one of the table by joining with
other three. Also we need to delete the roughly 2 million rows daily
from these tables as new data is added. Please suggest if breaking all
these tables into different databases is better or having them all in
one single database is better?. Also the joining keys are varchar
fields. So any suggestions on indexing?
thanks
KrisPartitioning is your friend.
If your daily data sets are in different partitions, the drop can be
metadata-only. That is very fast. Essentially, you are truncating the
partition. To make this work, you have to have indexes aligned with the
partitioning function. Read all about partitioning in BOL.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<krishnasingaraju@.gmail.com> wrote in message
news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
> Hi,
> I am trying to design a sql2005 Database with 4 tables of 35 million
> rows. we need to update fields in one of the table by joining with
> other three. Also we need to delete the roughly 2 million rows daily
> from these tables as new data is added. Please suggest if breaking all
> these tables into different databases is better or having them all in
> one single database is better?. Also the joining keys are varchar
> fields. So any suggestions on indexing?
> thanks
> Kris
>|||> Partitioning is your friend.
Yes, very well said (I like it). Also reference DPV's (Distributed Partition
Views).
In a DPV you create n databases and link them together with a view. While
I've heard of multiple partitions on one server and that t performed well, a
DPV can be split up onto multiple nodes of an active/active cluster. There
is one important point, which will be in BOL, make sure that the data is
arranged so that at least 80% of the data you need comes from one
partition/server, or performance could actually be worse.
Jay
> If your daily data sets are in different partitions, the drop can be
> metadata-only. That is very fast. Essentially, you are truncating the
> partition. To make this work, you have to have indexes aligned with the
> partitioning function. Read all about partitioning in BOL.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> <krishnasingaraju@.gmail.com> wrote in message
> news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
>> Hi,
>> I am trying to design a sql2005 Database with 4 tables of 35 million
>> rows. we need to update fields in one of the table by joining with
>> other three. Also we need to delete the roughly 2 million rows daily
>> from these tables as new data is added. Please suggest if breaking all
>> these tables into different databases is better or having them all in
>> one single database is better?. Also the joining keys are varchar
>> fields. So any suggestions on indexing?
>> thanks
>> Kris
>|||Given the size info, I'm not sure you really want to even consider DPV.
Linchi
"Jay" wrote:
> > Partitioning is your friend.
> Yes, very well said (I like it). Also reference DPV's (Distributed Partition
> Views).
> In a DPV you create n databases and link them together with a view. While
> I've heard of multiple partitions on one server and that t performed well, a
> DPV can be split up onto multiple nodes of an active/active cluster. There
> is one important point, which will be in BOL, make sure that the data is
> arranged so that at least 80% of the data you need comes from one
> partition/server, or performance could actually be worse.
> Jay
> > If your daily data sets are in different partitions, the drop can be
> > metadata-only. That is very fast. Essentially, you are truncating the
> > partition. To make this work, you have to have indexes aligned with the
> > partitioning function. Read all about partitioning in BOL.
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> >
> >
> > <krishnasingaraju@.gmail.com> wrote in message
> > news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
> >> Hi,
> >>
> >> I am trying to design a sql2005 Database with 4 tables of 35 million
> >> rows. we need to update fields in one of the table by joining with
> >> other three. Also we need to delete the roughly 2 million rows daily
> >> from these tables as new data is added. Please suggest if breaking all
> >> these tables into different databases is better or having them all in
> >> one single database is better?. Also the joining keys are varchar
> >> fields. So any suggestions on indexing?
> >>
> >> thanks
> >> Kris
> >>
> >
>
>

Need a faster paging in a wesite search result page

have over million rows in the our table and we are looking forward to increase the speed of our query .Any ideas?

set ANSI_NULLS OFFset QUOTED_IDENTIFIER OFFGOALTER PROCEDURE [dbo].[mainSearch] @.startRowIndexint,@.maximumRowsint,@.rowCountint out,@.countedRowint,@.QUERY nvarchar(400)ASSELECT _ID,_NAME,_TYPE,_CREATEDATE,ESTATETYPE,ESTATEDISPLAYPRICE,ESTATEDISPLAYPRICECURRENCY,ESTATEDISTRICT,ESTATECITY,ESTATEROOMCOUNT,NUMBEROFPICTURES FROM (SELECT ROW_NUMBER() OVER (ORDER BY _CREATEDATE DESC) AS ROWRANK,*FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@.QUERY)as KEY_TBLON ADTBL._ID = KEY_TBL.[KEY]Where (_DELETIONSTATUS=0))AS RANKEDADDSWHERE ROWRANK > @.startRowIndex AND ROWRANK <= @.startRowIndex + @.maximumRows -1if(@.countedRow < 1)SET @.rowCount =(SELECT COUNT(_ID) FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@.QUERY)as KEY_TBLON ADTBL._ID = KEY_TBL.[KEY]Where _DELETIONSTATUS=0)else SET @.rowCount = @.countedRowRETURN

Do you have any idea about the Full Text Indexing?

|||

I don't know if you designed the table but have you also checked to ensure that all the proper indexes have been added ? You also might want to look at the query execution plan to see which part is consuming most of the execution time such as a hash or merge join etc.