How to force Query Optimizer to do what I want ?

I'm really going nuts. I have the following two tables:
Table1: 20 Mio Records, 2 Fields
Table2: 600'000 Records, 25 Fields
and the following query:
SELECT Fields FROM Table1 t1, Table2 t2
WHERE t1.id = t2.id AND t1.Searchword = 'productdesc' AND t2.status = 12 AND
t2.category <> 3
ORDER BY field1, field2, field3
The Query optimizer decides to filter (and sort!!) table 2 first and then
join it to table 1. this takes up to 10 minutes. If it would go the other wa
y
round and first query table 1 using the Searchword criteria and then joint
the result set to table2, the query would take maybe 1-2 seconds. I updated
statistics, all indices are there. What the hell is going on ?
BTW: If I remove the ORDER BY, it takes just 1 seconds two. Very strange...
HOW can I force the query optimizer to do it right ? I tried FORCEPLAN ON,
that didn't help. I assume since table 1 has so many rows and just 2 fields,
it decides it has very poor selectivity (which is actually wrong) and uses
the smaller table 2 instead. I tried JOIN ON, doesn't help.
Any help is greatly appreciated !
mbrtal. Enterprise Windows Application Development.Please be more specific about your problem. Give the _exact_ query (for this
kind of performance trouble shooting it is essential to know which columns
you select and order by. "Fields" and "field1, field2, field3" just doesn't
convey that kind of information) and give the DDL for you tables _and_
indexes. (See www.aspfaq.com/5006)
Jacco Schalkwijk
SQL Server MVP
"mbrtal" <mbrtal@.discussions.microsoft.com> wrote in message
news:20ACC629-4052-45E9-8C00-59A45CC1C226@.microsoft.com...
> I'm really going nuts. I have the following two tables:
> Table1: 20 Mio Records, 2 Fields
> Table2: 600'000 Records, 25 Fields
> and the following query:
> SELECT Fields FROM Table1 t1, Table2 t2
> WHERE t1.id = t2.id AND t1.Searchword = 'productdesc' AND t2.status = 12
> AND
> t2.category <> 3
> ORDER BY field1, field2, field3
> The Query optimizer decides to filter (and sort!!) table 2 first and then
> join it to table 1. this takes up to 10 minutes. If it would go the other
> way
> round and first query table 1 using the Searchword criteria and then joint
> the result set to table2, the query would take maybe 1-2 seconds. I
> updated
> statistics, all indices are there. What the hell is going on ?
> BTW: If I remove the ORDER BY, it takes just 1 seconds two. Very
> strange...
> HOW can I force the query optimizer to do it right ? I tried FORCEPLAN ON,
> that didn't help. I assume since table 1 has so many rows and just 2
> fields,
> it decides it has very poor selectivity (which is actually wrong) and uses
> the smaller table 2 instead. I tried JOIN ON, doesn't help.
> Any help is greatly appreciated !
> --
> mbrtal. Enterprise Windows Application Development.
>|||mbrtal,
You say about Table1 that "it [sql-server] decides it has very poor
selectivity (which is actually wrong)". How do you know that? If the
table has high selectivity and you have updated the statistics WITH
FULL_SCAN, then SQL-Server will judge the selectivity correctly. Why do
you think the different access path will result in a 30,000 percent
performance gain if you haven't seen SQL-Server execute the query this
way?
If you want to test the performance and see the query plan when Table1
is accessed first, then make sure Table1 is mentioned first (which is
already the case) and add the query hint OPTION (FORCE ORDER) to the
query.
But I have to agree with Jacco. Please post the actual query and
(simplified) DDL. Some information we are missing now:
- What indexes are present, and are they clustered?
- What tables do field1, field2 and field3 originate from?
- What data type definition do the columns Table1(SearchWord),
Table2(Status) and Table2(Category) have?
- Does Table1(id) have the same data type and size as Table2(id)?
- etc. etc.
If SQL-Server does not choose the 'obvious' plan, there there is
probably a very good reason for it. The limited information you posted
will not reveal the necessary details...
Gert-Jan
mbrtal wrote:
> I'm really going nuts. I have the following two tables:
> Table1: 20 Mio Records, 2 Fields
> Table2: 600'000 Records, 25 Fields
> and the following query:
> SELECT Fields FROM Table1 t1, Table2 t2
> WHERE t1.id = t2.id AND t1.Searchword = 'productdesc' AND t2.status = 12 A
ND
> t2.category <> 3
> ORDER BY field1, field2, field3
> The Query optimizer decides to filter (and sort!!) table 2 first and then
> join it to table 1. this takes up to 10 minutes. If it would go the other
way
> round and first query table 1 using the Searchword criteria and then joint
> the result set to table2, the query would take maybe 1-2 seconds. I update
d
> statistics, all indices are there. What the hell is going on ?
> BTW: If I remove the ORDER BY, it takes just 1 seconds two. Very strange..
.
> HOW can I force the query optimizer to do it right ? I tried FORCEPLAN ON,
> that didn't help. I assume since table 1 has so many rows and just 2 field
s,
> it decides it has very poor selectivity (which is actually wrong) and uses
> the smaller table 2 instead. I tried JOIN ON, doesn't help.
> Any help is greatly appreciated !
> --
> mbrtal. Enterprise Windows Application Development.

How to fit table on one page

Hello, I have a report that displays about 200 data records. There is no any
group or subtotal in the report, but just plain data. It is built in a
table. The report always generates two pages when it is ran, even though I
checked "fit table on one page if possible" box on the properties of the
table. How can I set up the report to display the 200 records on one page?
Thanks in advance!Hi Jean,
You can change the Page Height property in the Report Properies dialogue to
a larger number to fit the rows. This is true if you are rendering the
report in HTML.
--
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
(http://www.apress.com/book/bookDisplay.html?bID=365)
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:F077EAB2-1B32-49FD-A81F-098E5B37034D@.microsoft.com...
> Hello, I have a report that displays about 200 data records. There is no
> any
> group or subtotal in the report, but just plain data. It is built in a
> table. The report always generates two pages when it is ran, even though I
> checked "fit table on one page if possible" box on the properties of the
> table. How can I set up the report to display the 200 records on one page?
> Thanks in advance!
>|||Hi Hean,
You can increase the size of your Page Height Property in Report
Properties/Layout tab. This should work if you are rendering in HTML.
--
Rodney Landrum
Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:F077EAB2-1B32-49FD-A81F-098E5B37034D@.microsoft.com...
> Hello, I have a report that displays about 200 data records. There is no
> any
> group or subtotal in the report, but just plain data. It is built in a
> table. The report always generates two pages when it is ran, even though I
> checked "fit table on one page if possible" box on the properties of the
> table. How can I set up the report to display the 200 records on one page?
> Thanks in advance!
>|||Sorry, I meant Jean. :)
Rodney
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:F077EAB2-1B32-49FD-A81F-098E5B37034D@.microsoft.com...
> Hello, I have a report that displays about 200 data records. There is no
> any
> group or subtotal in the report, but just plain data. It is built in a
> table. The report always generates two pages when it is ran, even though I
> checked "fit table on one page if possible" box on the properties of the
> table. How can I set up the report to display the 200 records on one page?
> Thanks in advance!
>|||Rodney,
It works! Thanks a lot!
Jean
"Rodney Landrum" wrote:
> Sorry, I meant Jean. :)
> Rodney
> "Jean" <Jean@.discussions.microsoft.com> wrote in message
> news:F077EAB2-1B32-49FD-A81F-098E5B37034D@.microsoft.com...
> > Hello, I have a report that displays about 200 data records. There is no
> > any
> > group or subtotal in the report, but just plain data. It is built in a
> > table. The report always generates two pages when it is ran, even though I
> > checked "fit table on one page if possible" box on the properties of the
> > table. How can I set up the report to display the 200 records on one page?
> >
> > Thanks in advance!
> >
>
>sql

how to fine a hole in a records?

Hi all!

I need your help to realize algorithm for stored proc or trigger.

tool: MS SQL server 2000, T-SQL

TABLE:
[unique_id] [mynumber] [week]

[unique_id] - bigint,primary key, identity auto-increnment
[week] - int, 1-53, week number
[mynumber] - int, 1 - 7, for every week, daily record one per day, up
to 7 per week

so, for every week we have a mynumber from 1 to 7
or nothing (if no records for that day),

we can insert or delete mynubers in any order, at will

EXAMPLE:

week 1, mynumber 1,2,3 - so if we insert a new record, mynumber value
= 4
week 2, mynumber 1,2,3,5,7 - so next mynumber = 4

QUESTION:

How to use _only_ T-SQL find a missed numbers for particular week when
I'm insert a records?

Thanks.
ChapaiPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Rows are not records and you have no relational key in your
pseudo-code. Ignoring that the design is fundamentally bad because you
should be using temporal datatypes for temporal data, your table should
have looked like this:

CREATE TABLE Foobar
(week_nbr INTEGER NOT NULL
CHECK(week_nbr > 0),
day_nbr INTEGER NOT NULL
CHECK(day_nbr BETWEEN 1 AND 7),
PRIMARY KEY(week_nbr, day_nbr));

>> for every week we have a day_nbr from 1 to 7 or nothing (if no
record [sic] for that day), we can insert or delete day_nbr in any
order, at will . . How to use _only_ T-SQL to find a missed number for
particular week when I am inserting records [sic]? <<

This is a little ugly looking, but it is fast.

CREATE PROCEDURE InsertNewFoobar (@.new_week_nbr INTEGER)
BEGIN
DECLARE @.new_day_nbr INTEGER;
SET @.new_day_nbr
= CASE WHEN 1 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 1
WHEN 2 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 2
WHEN 3 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 3
WHEN 4 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 4
WHEN 5 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 5
WHEN 6 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 6
WHEN 7 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 7
ELSE NULL END;

INSERT INTO Foobar (week_nbr, day_nbr)
VALUES (@.new_week_nbr, @.new_day_nbr);
-- if you have 7 days already, then you get a primary key violation
-- you gave no specs on how to handle it

END:
In Standard SQL, the CASE expression could be in the VALUES () list|||Chapai (racecar@.mail.ru) writes:
> tool: MS SQL server 2000, T-SQL
> TABLE:
> [unique_id] [mynumber] [week]
> [unique_id] - bigint,primary key, identity auto-increnment
> [week] - int, 1-53, week number
> [mynumber] - int, 1 - 7, for every week, daily record one per day, up
> to 7 per week

I don't see the point with unique_id. Judging from your description
(week, mynumber) is unique. Then they should be the primary key.

> we can insert or delete mynubers in any order, at will
> EXAMPLE:
> week 1, mynumber 1,2,3 - so if we insert a new record, mynumber value
>= 4
> week 2, mynumber 1,2,3,5,7 - so next mynumber = 4
> QUESTION:
> How to use _only_ T-SQL find a missed numbers for particular week when
> I'm insert a records?

Search Google or the subject "Thinking about code or SP", a recent
thread in microsoft.public.sqlserver.programming for a whole range of
suggestions to a similar problem.

Since this problem is constrained to 1-7, here is a more simple-minded
solution:

SELECT MIN (n)
FROM (SELECT n = 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7) n
WHERE NOT EXISTS (SELECT *
FROM weeks w
WHERE w.weekno = @.weekno
AND n.n = w.mynumber)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi!

Thanks, I already find the same solution with while

create proc stupidproc ( @.week as int )
as

declare @.mynumber as int, @.availablenumber as int
set @.mynumber = 1

WHILE @.mynumber < 8
begin
IF NOT EXISTS ( select mynumber from MYTABLE

where mynumber = @.mynumber and [week]=...@.week)

begin
set @.availablenumber = @.mynumber
break
end
else
set @.mynumber = @.mynumber + 1

CONTINUE
end

select @.availablenumber|||Hi!

Erland Sommarskog wrote:

> I don't see the point with unique_id. Judging from your description
> (week, mynumber) is unique. Then they should be the primary key.

Just personal rule - always get a unique,independent id. I use sql in
web development - so to create a lists, etc.

> Search Google or the subject "Thinking about code or SP", a recent
> thread in microsoft.public.sqlserver.programming for a whole range of
> suggestions to a similar problem.
that was my second step. But it take a lot of wasted time.

> Since this problem is constrained to 1-7, here is a more
simple-minded
> solution:
> SELECT MIN (n)
> FROM (SELECT n = 1
> UNION ALL
> SELECT 2
> UNION ALL
> SELECT 3
> UNION ALL
> SELECT 4
> UNION ALL
> SELECT 5
> UNION ALL
> SELECT 6
> UNION ALL
> SELECT 7) n
> WHERE NOT EXISTS (SELECT *
> FROM weeks w
> WHERE w.weekno = @.weekno
> AND n.n = w.mynumber)
Cool. That is elegant. thanks.
Select x union all - that's nice.|||>> already find the same solution with while .. <<

Wrong. You have a proprietary, procedural answer that is
computationally equal to what I gave you. This is a BIG difference and
until you can see this, you will always be a 3GL programmer writing in
3GL programs in some proprietary, non-portable SQL dialect.

The whole point of non-procedural languages is that you tell it WHAT
you want and it figures oiut HOW to do it. Looping is a HOW and not a
WHAT.|||Hi!

--CELKO-- wrote:
> Wrong. You have a proprietary, procedural answer that is
> computationally equal to what I gave you.

yeah, right. I'm agree with you and I'm appreciate for your ideas and
help. But my version is more flexible and compact. what I gonna do with
your algorithm if I need more than 7 numbers? what about 50? 100?
Using a "while" I need to change only one variable and code are still
readable.
Easy to change, easy to support.
Portability is not important at all, especially for me, I'm a web
developer, so MS SQL cover all my (and my customers) needs. MS Access
cover the rest.

> until you can see this, you will always be a 3GL programmer writing
in
> 3GL programs in some proprietary, non-portable SQL dialect.
Ok. This is a real world. If you use ASP/VBscript/C#/.NET - Oracle
hosting are too expensive, Mysql useless.

> The whole point of non-procedural languages is that you tell it WHAT
> you want and it figures oiut HOW to do it. Looping is a HOW and not
a
> WHAT.
Nope. The whole point of non-procedural languages, and all other
programming languages - is to help you to make a money quickly. ;-)|||>> But my version is more flexible and compact. <<

No, your procedural coding is weak, too. Here is your algorithm in
SQL/PSM, which you can translate into dialect.

CREATE PROCEDURE StupidProc (IN my_week INTEGER)
LANGUAGE SQL
BEGIN
DECLARE answer_nbr INTEGER;
SET answer_nbr = 1;
WHILE answer_nbr < 8
DO IF NOT EXISTS
(SELECT *
FROM Foobar
WHERE day_nbr = answer_nbr
AND week_nbr = my_week)
THEN RETURN answer_nbr;
ELSE SET answer_nbr = answer_nbr + 1;
END IF;
END WHILE;
RETURN answer_nbr; -- 8 is an error
END;

The use of extra variables and the hidden GOTO's in BREAK and CONTINUE
would cost you points in any freshman programming class.

>> what I gonna do with your algorithm if I need more than 7 numbers?
what about 50? 100? <<

Use a Sequence table instead of a constructed table expression. Here
is a general version with pure Standard SQL

SELECT MIN (n)
FROM (SELECT seq FROM Sequence WHERE seq <= :n)
EXCEPT
(SELECT day_nbr
FROM Weeks AS
WHERE W.week_nbr = my_weeknbr) AS N(n);

>> The whole point of non-procedural languages, and all other
programming languages - is to help you to make money quickly. ;-) <<
And the only way you can do this is with bad programming??|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1105201718.985293.286980@.c13g2000cwb.googlegr oups.com...
>> >> The whole point of non-procedural languages, and all other
> programming languages - is to help you to make money quickly. ;-) <<
> And the only way you can do this is with bad programming??

Of course... that way the customer keeps coming back to you... until they
figure out they're paying too much. :-)|||Chapai (racecar@.mail.ru) writes:
> Just personal rule - always get a unique,independent id. I use sql in
> web development - so to create a lists, etc.

Not sure that I see the point, but as long as you have a UNIQUE constraint
on the real primary key, that's alright. Without the UNIQUE constraint,
you're putting the integrity of your database at stake.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi!

--CELKO-- wrote:
> No, your procedural coding is weak, too. Here is your algorithm in
> SQL/PSM, which you can translate into dialect.

I have a better idea for you - just put your code into query analyzer
and try to execute it. Oops! It does not work? Why?
Open your eyes and read the group name. DB2? Super-duper-sql-theory?
Microsoft does not support this standard in sql server 2000.
Period.

> The use of extra variables and the hidden GOTO's in BREAK and
CONTINUE
> would cost you points in any freshman programming class.
Show me other faster and better way to use "while" in ms sql server
2000.
I'm wait.

> Use a Sequence table instead of a constructed table expression. Here
> is a general version with pure Standard SQL
aha. Hundreds sets like set @.i1 = 1, @.i2=2, tables, temporary tables,
cross calls, .. Sure. Sommarskog's sample was finer and more
interesting.

> And the only way you can do this is with bad programming??
It works? Works, fast? Fast. Simple? Simple. What else? Portability.
Strict adherence to standards. Ok. I'm not a student with ideas and not
an old professor with grey bolls - I'm MS web developer with hourly
rate.
Holy wars linux vs windows, c vs pascal. :-) You work on salary - right?|||Hi!

Erland Sommarskog wrote:

> Not sure that I see the point, but as long as you have a UNIQUE
constraint
> on the real primary key, that's alright. Without the UNIQUE
constraint,
> you're putting the integrity of your database at stake.

I'm a web developer. :-) Tomorrow customer can says to change
everything, or major part of logic. And I will get just couple hours to
realize that. other paradigm.|||Chapai (racecar@.mail.ru) writes:
>> Not sure that I see the point, but as long as you have a UNIQUE
>> constraint on the real primary key, that's alright. Without the UNIQUE
>> constraint, you're putting the integrity of your database at stake.
> I'm a web developer. :-) Tomorrow customer can says to change
> everything, or major part of logic. And I will get just couple hours to
> realize that. other paradigm.

If you work under these cirumstances, it's even more important to
have your constraints right. Stressed development, unevitably leads
to bugs creeping in. Constraint is a means of preventing at least some
of these bugs causing bad data to be persisted.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi!

Erland Sommarskog wrote:

> If you work under these cirumstances, it's even more important to
> have your constraints right. Stressed development, unevitably leads

Ok. How you can link other tables without unique constraint?
Most fields are not unique, so, easy to get one guaranteed simple
unique field to reference. Or you know the other method?

table from discussed example (up to 7 workouts_num per week, for every
trainer/customer):

workout(workout_id, workout_num , workout_week, program_id, client_id)
and need to organise relations to table
program(program_id,program_name,trainer_id), table
exrcise(exercise_id,exercise_name),
table exercise_workout_link(workout_id,exercise_id)|||Chapai (racecar@.mail.ru) writes:
> Ok. How you can link other tables without unique constraint?

That's kind of difficult. Then again, I suggested that it was a
UNIQUE constraint that you should add to your table.

> Most fields are not unique, so, easy to get one guaranteed simple
> unique field to reference. Or you know the other method?
> table from discussed example (up to 7 workouts_num per week, for every
> trainer/customer):
> workout(workout_id, workout_num , workout_week, program_id, client_id)
> and need to organise relations to table
> program(program_id,program_name,trainer_id), table
> exrcise(exercise_id,exercise_name),
> table exercise_workout_link(workout_id,exercise_id)

It looks as if foreign-key constraint from workout to progam would be
possible. But not knowing the business rules, that is of course impossible
to tell.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

how to find records in database using select

I need to be able to find certain data as the user has submitted that data twice and delete one record,except that have hundreds of tables and don't know the table where she would have submitted the data, but I have some other key info that I can start with.

My question is,how do i select a record from the database if I don't know the table it comes from?

Could somebody give me an expamle please?How did they submit the data? Do you know the data submitted? You have to have something to go on.|||Can you pose the question exactly as the teacher wrote it in your assignment? Just type it verbatim into a posting, and you'll get a much better quality answer.

-PatP

How to find record with xml data containing value...

Hello all,
SQL2005
I am newbe in XPath and XQuery
I have to find in table with xml field all records where value of attribute
contains string (e.g person name)
drop table dbo.bbb;
create table dbo.bbb(
p1 int not null identity,
p2 xml,
primary key(p1) );
insert into dbo.bbb values
('<root><item werte="d1"/><item werte="a2"/></root>');
insert into dbo.bbb values
('<root><item werte="b1"/><item werte="b2"/></root>');
select *
from dbo.bbb
where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
this query give me records with @.werte containing "a" but only if it is in
first item,
I have to find records with @.werte containing "a" regardless of item
position.
Can anybody help me write this query?
Regards
YaroOK, I know
select *
from dbo.bbb
where
p2.exist('/root/item/@.werte[contains(.,"a")]') = 1
Yaro
Uytkownik "Yaro" <yarok_delthisdes_@.op.pl> napisa w wiadomoci
news:e12vcm$nq4$1@.83.238.170.160...
> Hello all,
> SQL2005
> I am newbe in XPath and XQuery
> I have to find in table with xml field all records where value of
> attribute contains string (e.g person name)
> drop table dbo.bbb;
> create table dbo.bbb(
> p1 int not null identity,
> p2 xml,
> primary key(p1) );
> insert into dbo.bbb values
> ('<root><item werte="d1"/><item werte="a2"/></root>');
> insert into dbo.bbb values
> ('<root><item werte="b1"/><item werte="b2"/></root>');
> select *
> from dbo.bbb
> where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
> this query give me records with @.werte containing "a" but only if it is
> in first item,
> I have to find records with @.werte containing "a" regardless of item
> position.
> Can anybody help me write this query?
> Regards
> Yaro

How to find record with xml data containing value...

Hello all,
SQL2005
I am newbe in XPath and XQuery
I have to find in table with xml field all records where value of attribute
contains string (e.g person name)
drop table dbo.bbb;
create table dbo.bbb(
p1 int not null identity,
p2 xml,
primary key(p1) );
insert into dbo.bbb values
('<root><item werte="d1"/><item werte="a2"/></root>');
insert into dbo.bbb values
('<root><item werte="b1"/><item werte="b2"/></root>');
select *
from dbo.bbb
where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
this query give me records with @.werte containing "a" but only if it is in
first item,
I have to find records with @.werte containing "a" regardless of item
position.
Can anybody help me write this query?
Regards
Yaro
OK, I know
select *
from dbo.bbb
where
p2.exist('/root/item/@.werte[contains(.,"a")]') = 1
Yaro
Uytkownik "Yaro" <yarok_delthisdes_@.op.pl> napisa w wiadomoci
news:e12vcm$nq4$1@.83.238.170.160...
> Hello all,
> SQL2005
> I am newbe in XPath and XQuery
> I have to find in table with xml field all records where value of
> attribute contains string (e.g person name)
> drop table dbo.bbb;
> create table dbo.bbb(
> p1 int not null identity,
> p2 xml,
> primary key(p1) );
> insert into dbo.bbb values
> ('<root><item werte="d1"/><item werte="a2"/></root>');
> insert into dbo.bbb values
> ('<root><item werte="b1"/><item werte="b2"/></root>');
> select *
> from dbo.bbb
> where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
> this query give me records with @.werte containing "a" but only if it is
> in first item,
> I have to find records with @.werte containing "a" regardless of item
> position.
> Can anybody help me write this query?
> Regards
> Yaro

How to find Rank(return Top 3 Country)

Hi All,
These are the records I've in my table. I need to return top 3 countries (rank) by Sales.
Record Date Country Sales
1 01.01.04 AU 80%
2 01.01.04 IN 92%
3 01.01.04 CH 50%
4 01.01.04 USA 75%
5 01.01.04 SG 25%
The result set should be:
Record Date Country Sales
2 01.01.04 IN 92%
1 01.01.04 AU 80%
4 01.01.04 USA 75%
How to write query for that?
Thanx
Rgds
K.Senthil Kumar
SELECT TOP 3 Record, Date, Country, Sales
FROM your_table
ORDER BY Sales DESC
Jacco Schalkwijk
SQL Server MVP
"K.Senthil Kumar" <K.Senthil Kumar@.discussions.microsoft.com> wrote in
message news:6EB6A9BF-7A6D-4B10-8F9D-CC9087946489@.microsoft.com...
> Hi All,
> These are the records I've in my table. I need to return top 3 countries
(rank) by Sales.
> Record Date Country Sales
> 1 01.01.04 AU 80%
> 2 01.01.04 IN 92%
> 3 01.01.04 CH 50%
> 4 01.01.04 USA 75%
> 5 01.01.04 SG 25%
>
> The result set should be:
> Record Date Country Sales
> 2 01.01.04 IN 92%
> 1 01.01.04 AU 80%
> 4 01.01.04 USA 75%
> How to write query for that?
> Thanx
> Rgds
> K.Senthil Kumar

How to find Rank(return Top 3 Country)

Hi All,
These are the records I've in my table. I need to return top 3 countries (r
ank) by Sales.
Record Date Country Sales
1 01.01.04 AU 80%
2 01.01.04 IN 92%
3 01.01.04 CH 50%
4 01.01.04 USA 75%
5 01.01.04 SG 25%
The result set should be:
Record Date Country Sales
2 01.01.04 IN 92%
1 01.01.04 AU 80%
4 01.01.04 USA 75%
How to write query for that?
Thanx
Rgds
K.Senthil KumarSELECT TOP 3 Record, Date, Country, Sales
FROM your_table
ORDER BY Sales DESC
Jacco Schalkwijk
SQL Server MVP
"K.Senthil Kumar" <K.Senthil Kumar@.discussions.microsoft.com> wrote in
message news:6EB6A9BF-7A6D-4B10-8F9D-CC9087946489@.microsoft.com...
> Hi All,
> These are the records I've in my table. I need to return top 3 countries
(rank) by Sales.
> Record Date Country Sales
> 1 01.01.04 AU 80%
> 2 01.01.04 IN 92%
> 3 01.01.04 CH 50%
> 4 01.01.04 USA 75%
> 5 01.01.04 SG 25%
>
> The result set should be:
> Record Date Country Sales
> 2 01.01.04 IN 92%
> 1 01.01.04 AU 80%
> 4 01.01.04 USA 75%
> How to write query for that?
> Thanx
> Rgds
> K.Senthil Kumar

How to find odd numbers?

Hi
What is the best/easiest way to find records where a certain number is odd?
I need to search for some records in one of our databases and one of the
criterias is that a number is odd.
Regards
SteenSELECT ...
FROM ...
WHERE number %1 = 1
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%231LOEH$FFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hi
> What is the best/easiest way to find records where a certain number is
> odd?
> I need to search for some records in one of our databases and one of the
> criterias is that a number is odd.
> Regards
> Steen
>|||On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>SELECT ...
>FROM ...
>WHERE number %1 = 1
Hi Roji,
I think you meant to write
WHERE number % 2 = 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep, thanks Hugo..
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.4ax.com...
> On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>>SELECT ...
>>FROM ...
>>WHERE number %1 = 1
> Hi Roji,
> I think you meant to write
> WHERE number % 2 = 1
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||...thanks guys...that was the function I was looking for. I just couldn't
find it in BOL.
Regards
Steen
Roji. P. Thomas wrote:
> Yep, thanks Hugo..
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.4ax.com...
>> On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>> SELECT ...
>> FROM ...
>> WHERE number %1 = 1
>> Hi Roji,
>> I think you meant to write
>> WHERE number % 2 = 1
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)

How to find odd numbers?

Hi
What is the best/easiest way to find records where a certain number is odd?
I need to search for some records in one of our databases and one of the
criterias is that a number is odd.
Regards
Steen
SELECT ...
FROM ...
WHERE number %1 = 1
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%231LOEH$FFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hi
> What is the best/easiest way to find records where a certain number is
> odd?
> I need to search for some records in one of our databases and one of the
> criterias is that a number is odd.
> Regards
> Steen
>
|||On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:

>SELECT ...
>FROM ...
>WHERE number %1 = 1
Hi Roji,
I think you meant to write
WHERE number % 2 = 1
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Yep, thanks Hugo..
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.4ax.com...
> On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>
> Hi Roji,
> I think you meant to write
> WHERE number % 2 = 1
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||...thanks guys...that was the function I was looking for. I just couldn't
find it in BOL.
Regards
Steen
Roji. P. Thomas wrote:[vbcol=seagreen]
> Yep, thanks Hugo..
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.4ax.com...

How to find odd numbers?

Hi
What is the best/easiest way to find records where a certain number is odd?
I need to search for some records in one of our databases and one of the
criterias is that a number is odd.
Regards
SteenSELECT ...
FROM ...
WHERE number %1 = 1
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%231LOEH$FFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hi
> What is the best/easiest way to find records where a certain number is
> odd?
> I need to search for some records in one of our databases and one of the
> criterias is that a number is odd.
> Regards
> Steen
>|||On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:

>SELECT ...
>FROM ...
>WHERE number %1 = 1
Hi Roji,
I think you meant to write
WHERE number % 2 = 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep, thanks Hugo..
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.
4ax.com...
> On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>
> Hi Roji,
> I think you meant to write
> WHERE number % 2 = 1
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||...thanks guys...that was the function I was looking for. I just couldn't
find it in BOL.
Regards
Steen
Roji. P. Thomas wrote:[vbcol=seagreen]
> Yep, thanks Hugo..
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.
4ax.com...

How to find missing records from tables involving composite primary keys

Table 1

Code	Quarter
500002	26
500002	27
500002	28
500002	28.5
500002	29

Table 2

Code	Qtr
500002	26
500002	27

I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS

Can anybody help me with how to compare the two tables to find the records not present in Table 2

That is i need this result

Code	Quarter
500002	28
500002	28.5
500002	29

I have come up with this solution

select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)

i need to know if there is some other way of doing the same

Thanks in Advance

Jacx

You can use the following query too...

Select
A.Code,
A.Quarter
From
[Table 1] A
Left Outer Join [Table 2] B on A.Code = B.Code And A.Quarter = B.Quarter
Where
B.Code is NULL

|||

Using NOT EXISTS is the fastest way to solve your problem. Use query below:

select t1.scrip_cd, t1.Qtr

from Table1 as t1

where not exists(

select * from Table2 as t2

where t2.scrip_cd = t1.scrip_cd and t2.Qtr = t1.Qtr

)

How to Find if locks exists when deleting records from a table

Hi,
Please do help me in finding whether any locks exists when records from a table is being removed(deleted).
I want a step by step procedure.
regards,
Raj
Hi,
1. Login to Query analyzer
2. Execute SP_WHO and identify the process id (SPID) in whcih you are
deleting the contents of table. Check for cmd column , it will show "DELETE"
3. After getting the SPID, Execute SP_LOCK <SPID> to get the lock
information
In the type column you will have diffrent types of lock types like;
DB = Database
IDX = Index
PG = PAGE
KEY = Key
TAB = Table
EXT = Extent
Thanks
Hari
MCDBA
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:713607B3-ED2C-4FD6-8076-909FF3A8AF18@.microsoft.com...
> Hi,
> Please do help me in finding whether any locks exists when records from a
table is being removed(deleted).
> I want a step by step procedure.
> regards,
> Raj

How to Find if locks exists when deleting records from a table

Hi,
Please do help me in finding whether any locks exists when records from a ta
ble is being removed(deleted).
I want a step by step procedure.
regards,
RajHi,
1. Login to Query analyzer
2. Execute SP_WHO and identify the process id (SPID) in whcih you are
deleting the contents of table. Check for cmd column , it will show "DELETE"
3. After getting the SPID, Execute SP_LOCK <SPID> to get the lock
information
In the type column you will have diffrent types of lock types like;
DB = Database
IDX = Index
PG = PAGE
KEY = Key
TAB = Table
EXT = Extent
Thanks
Hari
MCDBA
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:713607B3-ED2C-4FD6-8076-909FF3A8AF18@.microsoft.com...
> Hi,
> Please do help me in finding whether any locks exists when records from a
table is being removed(deleted).
> I want a step by step procedure.
> regards,
> Raj

How to find duplicate records by tree field?

I have a table named student, there are tree fields GradeID, ClassID, Seat
in it.
How can I fild the duplicate record by the tree fields?
Perhaps this will help.
SELECT GradeID, ClassID, Seat, count(*) as Rows
FROM Students
GROUP BY GradeID, ClassID, Seat
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 5 Oct 2006 05:45:22 +0800, "ad" <flying@.wfes.tcc.edu.tw>
wrote:

>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
>How can I fild the duplicate record by the tree fields?
>
|||try this
select Count(*),GradeID, ClassID, Seat
from dbo.tablename
group by GradeID, ClassID, Seat
having count(*) > 1
the count will show you how many rows are duplicated for each instance...
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
> How can I fild the duplicate record by the tree fields?
>
|||Thanks,
The tabe's primary is PID, how can I show the duplicate rows with PID.

> select Count(*),GradeID, ClassID, Seat
> from dbo.tablename
> group by GradeID, ClassID, Seat
> having count(*) > 1
> the count will show you how many rows are duplicated for each instance...
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>

How to find duplicate records by tree field?

I have a table named student, there are tree fields GradeID, ClassID, Seat
in it.
How can I fild the duplicate record by the tree fields?Perhaps this will help.
SELECT GradeID, ClassID, Seat, count(*) as Rows
FROM Students
GROUP BY GradeID, ClassID, Seat
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 5 Oct 2006 05:45:22 +0800, "ad" <flying@.wfes.tcc.edu.tw>
wrote:

>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
>How can I fild the duplicate record by the tree fields?
>|||try this
select Count(*),GradeID, ClassID, Seat
from dbo.tablename
group by GradeID, ClassID, Seat
having count(*) > 1
the count will show you how many rows are duplicated for each instance...
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
> How can I fild the duplicate record by the tree fields?
>|||Thanks,
The tabe's primary is PID, how can I show the duplicate rows with PID.

> select Count(*),GradeID, ClassID, Seat
> from dbo.tablename
> group by GradeID, ClassID, Seat
> having count(*) > 1
> the count will show you how many rows are duplicated for each instance...
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>

How to find duplicate records by tree field?

I have a table named student, there are tree fields GradeID, ClassID, Seat
in it.
How can I fild the duplicate record by the tree fields?Perhaps this will help.
SELECT GradeID, ClassID, Seat, count(*) as Rows
FROM Students
GROUP BY GradeID, ClassID, Seat
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 5 Oct 2006 05:45:22 +0800, "ad" <flying@.wfes.tcc.edu.tw>
wrote:
>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
>How can I fild the duplicate record by the tree fields?
>|||try this
select Count(*),GradeID, ClassID, Seat
from dbo.tablename
group by GradeID, ClassID, Seat
having count(*) > 1
the count will show you how many rows are duplicated for each instance...
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
> How can I fild the duplicate record by the tree fields?
>|||Thanks,
The tabe's primary is PID, how can I show the duplicate rows with PID.
> select Count(*),GradeID, ClassID, Seat
> from dbo.tablename
> group by GradeID, ClassID, Seat
> having count(*) > 1
> the count will show you how many rows are duplicated for each instance...
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>>I have a table named student, there are tree fields GradeID, ClassID, Seat
>>in it.
>> How can I fild the duplicate record by the tree fields?
>