Comprehensive Index Information

Hi,

I am writing an in house utility to attempt to compare different
aspects of databases.
I am currently writing the queries to list all of the indexes in the
database (including primary key indexes at present - I may move these
and compare separately at some point).

I would like the following information, in one result set if possible:

Table Name
Index Name
Column Name
Column Position
Unique?

Now on Oracle, this is easily done with the following query:

SELECT IND.TABLE_NAME, IND.INDEX_NAME, IND.COLUMN_NAME,
IND.COLUMN_POSITION, COL.UNIQUENESS
FROM USER_IND_COLUMNS IND,
USER_INDEXES COL
WHEREIND.INDEX_NAME = COL.INDEX_NAME
ORDER BY 1, 2, 3, 4, 5

I have been trying for over an hour now to get the equivalent, and I
really cannot figure it out. If anybody can come up with this then I
would greatly appreciate it!

Many Thanks,

PaulPaul (paulwragg2323@.hotmail.com) writes:

Quote:

Originally Posted by

I am writing an in house utility to attempt to compare different
aspects of databases.

Before you go too far, pay a visit to http://www.red-gate.com and
if SQL Compare meets your needs.

Quote:

Originally Posted by

I am currently writing the queries to list all of the indexes in the
database (including primary key indexes at present - I may move these
and compare separately at some point).
>
I would like the following information, in one result set if possible:
>
Table Name
Index Name
Column Name
Column Position
Unique?

SELECT tablename = t.name, indexname = i.name,
colname = c.name, pos = ic.index_column_id,
indextype = i.type_desc, isunique = i.is_unique
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.index_columns ic ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
JOIN sys.columns c ON t.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE i.is_hypothetical = 0

There are probably more columns should include in the output, but I
levae that as an exercise.

Note: the above works in SQL 2005 only. Next time, please specify which
version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

Thankyou very much for this. Of course, as usual I stupidly forgot to
post the version. Sorry about that. Really I need something that will
work on both SQL Server 2000 and SQL Server 2005.

Thanks for the link - unfortunately this is more of an exercise for
the time being and so we are not willing to spend money on a tool at
present!

Thanks for the help - if you do know something that will work on both
versions that would be good.

Paul|||Paul (paulwragg2323@.hotmail.com) writes:

Quote:

Originally Posted by

Thankyou very much for this. Of course, as usual I stupidly forgot to
post the version. Sorry about that. Really I need something that will
work on both SQL Server 2000 and SQL Server 2005.

Then you need to work against sysobjects, sysindexes, sysindexkeys and
syscolumns. The query will be similar, but you need to filter for
statistics, since in SQL 2000 statistics and indexes live in sysindexes.

These are documented in Books Online, and since this is an exercise for you,
I leave you there. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland.

Complicated Update query based on existing data

OK folks, may have a tough one or perhaps just not thinking it through
well. I need to create a SQL query or queries that updates two columns
based on some business rules. Here's an example of the data:
GroupID complete_num first_num second_num InUse biggest
965423 1.0 1 0
965423 2.0 2 0
965423 3.0 3 0 X
965423 3.1 3 1
965423 3.2 3 2 X
324554 1.0 1 0
324554 2.0 2 0 X X
123456 0.1 0 1
123456 0.2 0 2 X X
Hopefully the above even vaguely lines up for you. The last two
columns are currently blank. The representation above is how I would
like them to look after the queries run. So for the above data, you
have a group id that links all records for one set together. I need to
have the "InUse" box updated with a value of "X" for the highest number
that has zero in the second_num column for a group. I also need the
biggest column set to "X" for the largest number in a particular group,
so 3.1 is larger than 3.0. Keep in mind, I have separated the
complete_num into two columns as there could be a "decimal" value of
"10" which is higher than "1". They are not the same as complete_num
is not really a decimal numeric representation. It's used
programatically for other things. Seperating into two separate columns
allows better sorting of data as complete_num is varchar and first and
second num are integer. You will also see the case, 123456,where there
is no zero in the second_num column. In this case, the highest
second_num value will have both set to "X". Any help would be
appreciated and SQL queries are prefered over any procedures/functions
as this is a one time query I need to run against the database. If you
need further clarification or more examples, please let me know.
Thanks.
JRAlso note that there is of course a unique incremental column in the
table. For arguements sake we can say U_ID. Also these records could
be in any order by default in the database. Not necessarily grouped
together as shown above.|||JR (jriker1@.yahoo.com) writes:
> OK folks, may have a tough one or perhaps just not thinking it through
> well. I need to create a SQL query or queries that updates two columns
> based on some business rules. Here's an example of the data:
> GroupID complete_num first_num second_num InUse biggest
> 965423 1.0 1 0
> 965423 2.0 2 0
> 965423 3.0 3 0 X
> 965423 3.1 3 1
> 965423 3.2 3 2 X
> 324554 1.0 1 0
> 324554 2.0 2 0 X X
> 123456 0.1 0 1
> 123456 0.2 0 2 X X
> Hopefully the above even vaguely lines up for you. The last two
> columns are currently blank. The representation above is how I would
> like them to look after the queries run. So for the above data, you
> have a group id that links all records for one set together. I need to
> have the "InUse" box updated with a value of "X" for the highest number
> that has zero in the second_num column for a group. I also need the
> biggest column set to "X" for the largest number in a particular group,
> so 3.1 is larger than 3.0.
It is always a good idea for this sort of question to include CREATE
TABLE statements for the table, and the sample data as INSERT statements.
That makes it easy to copy-and-paste into a query tool, to develop a
tested solution.
Thus, this is an untested solution:
BEGIN TRANSACTION
UPDATE tbl
SET biggest = 0,
inuse = 0
UPDATE tbl
SET biggest = 1
FROM tbl a
WHERE EXISTS (SELECT *
FROM (SELECT GroupID,
biggest = MAX(100000 * first_num + second_num)
FROM tbl
GROUP BY GroupID) AS big
WHERE a.big = big.GroupID
AND a.first_num * 1000000 + a.second_num = big.biggest)
UPDATE tbl
SET inuse = 1
FROM tbl a
JOIN (SELECT GroupID, first_num = MAX(first_num)
FROM tbl
WHERE second_num = 0
GROUP BY GroupID) AS inuse ON a.GroupID = inuse.GroupID
AND a.first_num = inuse.first_num
AND a.second_num = 0
UPDATE tbl
SET inuse = 1
FROM tbl a
WHERE a.biggest = 1
AND NOT EXISTS (SELECT *
FROM tbl b
WHERE a.GroupID = b.GroupID
AND a.insue = 1)
COMMIT TRANSACTION
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland. SQL Query Analyser is complaining about a syntax
problem with the '=' on "biggest = MAX(100000 * first_num +
second_num)" in the second update and "JOIN (SELECT GroupID,
first_num = MAX(first_num)" in the ghird.|||JR,
I think you can say this more simply: Update InUse to X
for the largest second_num value in the group *counting 0
as the largest*, and the largest first_num value in the case of
a tie. Update biggest to X for the largest first_num value
in the group, and the largest second_num value in the case
of a tie.
There is a more compact solution than Erland's in SQL Server 2005:
with T2 as (
select
*,
rank() over (partition by GroupID order by first_num desc,
case when second_num = 0 then 2147364827 else second_num end desc)
as rk1,
rank() over (partition by GroupID order by second_num desc,
first_num desc) as rk2
from #T
)
update T2 set
InUse = case when rk1 = 1 then 'X' else InUse end,
biggest = case when rk2 = 1 then 'X' else biggest end
where rk1 = 1 or rk2 = 1
Steve Kass
Drew University
JR wrote:

>OK folks, may have a tough one or perhaps just not thinking it through
>well. I need to create a SQL query or queries that updates two columns
>based on some business rules. Here's an example of the data:
>GroupID complete_num first_num second_num InUse biggest
>965423 1.0 1 0
>965423 2.0 2 0
>965423 3.0 3 0 X
>965423 3.1 3 1
>965423 3.2 3 2 X
>324554 1.0 1 0
>324554 2.0 2 0 X X
>123456 0.1 0 1
>123456 0.2 0 2 X X
>
>Hopefully the above even vaguely lines up for you. The last two
>columns are currently blank. The representation above is how I would
>like them to look after the queries run. So for the above data, you
>have a group id that links all records for one set together. I need to
>have the "InUse" box updated with a value of "X" for the highest number
>that has zero in the second_num column for a group. I also need the
>biggest column set to "X" for the largest number in a particular group,
>so 3.1 is larger than 3.0. Keep in mind, I have separated the
>complete_num into two columns as there could be a "decimal" value of
>"10" which is higher than "1". They are not the same as complete_num
>is not really a decimal numeric representation. It's used
>programatically for other things. Seperating into two separate columns
>allows better sorting of data as complete_num is varchar and first and
>second num are integer. You will also see the case, 123456,where there
>is no zero in the second_num column. In this case, the highest
>second_num value will have both set to "X". Any help would be
>appreciated and SQL queries are prefered over any procedures/functions
>as this is a one time query I need to run against the database. If you
>need further clarification or more examples, please let me know.
>Thanks.
>JR
>
>|||On 9 Apr 2006 07:36:13 -0700, JR wrote:

>OK folks, may have a tough one or perhaps just not thinking it through
>well. I need to create a SQL query or queries that updates two columns
>based on some business rules. Here's an example of the data:
(snip)
Hi JR,
If these columns should be calculated based on the data, why store them
at all? I would only recommend that if updates are very infrequent,
queries of the data are very frequent and the table is large, or if
these columns need to capture a moment in time and stay unchanged after
that, even if the underlying data does change.
In one of the latter cases, use Erland's suggestion. Otherwise, drop the
columns from the table and set up a view instead:
CREATE VIEW ChooseGoodName
AS
SELECT a.GroupID, a.complete_num, a.first_num, a.second_num,
CASE WHEN a.first_num = b.first_num
AND a.second_num = 0
THEN 'X' -- Latest X.0
WHEN a.first_num = 0
AND a.second_num = b.second_num
THEN 'X' -- Latest 0.Y
ELSE ''
END AS InUse,
CASE WHEN b.first_num = a.first_num
AND b.second_num = a.second_num
THEN 'X'
ELSE ''
END AS biggest
FROM YourTable AS a
INNER JOIN (SELECT GroupID, complete_num, first_num, second_num
FROM YourTable AS c
WHERE NOT EXISTS (SELECT *
FROM YourTable AS d
WHERE d.GroupID = c.GroupID
AND ( d.first_num > c.first_num
OR ( d.first_num = c.first_num
AND d.second_num > c.second_num))
) ) AS b
ON b.GroupID = a.GroupID
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||JR (jriker1@.yahoo.com) writes:
> Thanks Erland. SQL Query Analyser is complaining about a syntax
> problem with the '=' on "biggest = MAX(100000 * first_num +
> second_num)" in the second update and "JOIN (SELECT GroupID,
> first_num = MAX(first_num)" in the ghird.
Yes, as I said the code was untested for reasons I explained. I assume
that you are able to weed out trivial syntax errors on your own. If not,
please include CREATE TABLE statements and the sample data in INSERT
statements, to make testing easy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||As requested, here is a table creation script and some minimal data to
limit the length of the message. Also Hugo's view is great however
since this data is for one time use, would imagine updates to the
existing data would be preferable over introducing a new view into the
mix. If not updating the data directly based on the view would now be
a simple matter when you introduce the Id from the original table into
the view.
CREATE TABLE [abcd].[dbo].[TBL1
(Id,GroupId,complete_num,first_num,secon
d_num)] (
[Id] int NOT NULL,
[GroupID] nvarchar (60) NULL,
[complete_num] varchar (255) NULL,
[first_num] integer,
[second_num] integer,
[InUse] varchar (2) NULL,
[biggest] varchar (2) NULL,
)
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(91510,ABC1235,2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(89377,ABC1235,2.1,2,1);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(89371,ABC1235,2.2,2,2);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1310,M123456,1.0,1,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1309,M123456,2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1311,M123456,3.0,3,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1312,M123456,4.0,4,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1315,M123456,5.0,5,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1318,M123456,6.0,6,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1319,M123456,7.0,7,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1317,M123456,8.0,8,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(5342,M123456,9.0,9,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(5346,M123456,10.0,10,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(5756,M123456,11.0,11,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(6315,M123456,12.0,12,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(6604,M123456,13.0,13,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(6920,M123456,14.0,14,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1002,M123456,15.0,15,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(4023,ARDFO32,0.1,0,1);|||Your programs will be total nightmares and crap until you learn how to
design a schema.
Look at the DDL; do you really have a NCHAR(60) grpoup identifier? In
Chinese' Wel;l;, since you allowed it, you will get one! Why do you
have redundant split attributes (i.e. first_num || second_num =
complete_num)? Let's spit on normalization!! I also love the clear,
meaningful names of the data elements. Tell us what a thing is, not
its sequential order inside another column. Logical not physical
descriptions.
There are no keys, no constraints. This is not a table at all! And
you invented your own syntax for CREATE TABLE.
Your idea of updatind computed columns is a way to mimic punch cards.
Back in the 1950-60's we had to store those things in the physical
card, like you are doing now.
Making a guess, if you normalized your schema, had a key and followed
the baisc data modeling rules, would this nightmare look more like
this? Better names that show subordination (well, Foobar is a dummy
name, but that is all you gave us)
CREATE TABLE Foobar
(group_id CHAR(6) NOT NULL
CHECK (group_id LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')
section_nbr INTEGER DEFAULT 1 NOT NULL
CHECK (section_num >= 0),
subsection_nbr INTEGER DEFAULT 0 NOT NULL
CHECK (subsection_num >= 0),
PRIMARY KEY (group_id, section_nbr, subsection_nbr));
Do you need a constaint to assure that the subsections are in sequence?
Is there a check digit rule in the group_id? 90% of the work in RDBMS
is done in the DDL!!
SQL does not have links; it has REFERENCES and grouping. Totally
different concepts, based on sets and not pre-RDBMS file and pointer
systems. Rows are nothing whatsoever like records.
Now, the answer to your question is a VIEW, not a "punch cards and bit
flags" solution via updates.
CREATE VIEW InUseFoobar (group_id, section_nbr, subsection_nbr)
AS
SELECT group_id, MAX(section_nbr), 0
FROM Foobar
WHERE subsection = 0
GROUP BY group_id) ;
CREATE VIEW MaxFoobar (group_id, section_nbr, subsection_nbr)
AS
SELECT group_id, section_nbr, MAX(subsection_nbr)
FROM Foobar AS F1, InUseFoobar AS U1
WHERE F1.group_id = .U1.group_id
AND F1.section_nbr = .U1.section_nbr
GROUP BY group_id, section_nbr ;
<<untested>>|||JR (jriker1@.yahoo.com) writes:
> As requested, here is a table creation script and some minimal data to
> limit the length of the message. Also Hugo's view is great however
> since this data is for one time use, would imagine updates to the
> existing data would be preferable over introducing a new view into the
> mix. If not updating the data directly based on the view would now be
> a simple matter when you introduce the Id from the original table into
> the view.
Below is a tested version of my script. I'm not sure that I understand
the syntax errors you mentioned; I did not get these. I include
your original script, with small changes. Note that I've made inuse
and biggest into bit columns; I did this as I used 0 and 1 in my script.
Beware that things get wrapped in news transport!
CREATE TABLE [dbo].[TBL1]
(
[Id] int NOT NULL,
[GroupId] nvarchar (60) NULL,
[complete_num] varchar (255) NULL,
[first_num] integer,
[second_num] integer,
[inuse] bit NULL,
[biggest] bit NULL,
)
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (9151
0,'ABC1235',2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (8937
7,'ABC1235',2.1,2,1);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (8937
1,'ABC1235',2.2,2,2);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1310
,'M123456',1.0,1,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1309
,'M123456',2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1311
,'M123456',3.0,3,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1312
,'M123456',4.0,4,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1315
,'M123456',5.0,5,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1318
,'M123456',6.0,6,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1319
,'M123456',7.0,7,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1317
,'M123456',8.0,8,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (5342
,'M123456',9.0,9,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (5346
,'M123456',10.0,10,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (5756
,'M123456',11.0,11,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (6315
,'M123456',12.0,12,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (6604
,'M123456',13.0,13,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (6920
,'M123456',14.0,14,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1002
,'M123456',15.0,15,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (4023
,'ARDFO32',0.1,0,1);
go
BEGIN TRANSACTION
UPDATE TBL1
SET biggest = 0,
inuse = 0
UPDATE TBL1
SET biggest = 1
FROM TBL1 a
WHERE EXISTS (SELECT *
FROM (SELECT GroupId,
biggest = MAX(100000 * first_num + second_num)
FROM TBL1
GROUP BY GroupId) AS big
WHERE a.GroupId = big.GroupId
AND a.first_num * 100000 + a.second_num = big.biggest)
UPDATE TBL1
SET inuse = 1
FROM TBL1 a
JOIN (SELECT GroupId, first_num = MAX(first_num)
FROM TBL1
WHERE second_num = 0
GROUP BY GroupId) AS inuse ON a.GroupId = inuse.GroupId
AND a.first_num = inuse.first_num
AND a.second_num = 0
UPDATE TBL1
SET inuse = 1
FROM TBL1 a
WHERE a.biggest = 1
AND NOT EXISTS (SELECT *
FROM TBL1 b
WHERE a.GroupId = b.GroupId
AND b.inuse = 1)
COMMIT TRANSACTION
SELECT * FROM TBL1
go
drop table TBL1
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Complex SQL query

Hi,

I'm doing a report with a group of queries but right now is very slow, so I need to do it faster. These are not the real tables but will help:

The report needs to show the total products for every combination of ADDRESS and PRODUCT_TYPE. Assume these are the tables:

ADDRESS: ADDRESS_ID, ADDRESS_NAME
PRODUCT_TYPE: PRODUCT_TYPE_CODE, PRODUCT_TYPE_NAME
ORDER: ORDER_ID, DATE, ORDER_STATUS
ORDER_LINE: ORDER_ID, ORDER_PRODUCT_TYPE, PRODUCT_TOTAL
(This is an special table to handle the stock)
STOCK_INFO: STOCK_ACTUAL, DATE_UPDATED

This is what I'm doing in code (asp):

1. Retrieve all the address (and put it in array)
2. Retrieve all the product types (and put it in array)
3. Using double "FOR" I build the query for every combination of Address and ProductType

This is still slow (and is even better than before) and I would like to put everything in just 1 query and get this data ready to show in HTML

Address Product Type 1 Product Type2 Product Type3
Address1 TotProdType11 TotProdType21 TotProdType31
Address2 TotProdType12 TotProdType22 TotProdType32
.....

I'll really appreciate any help. And also any better idea to do these is welcome (is just I don't have to much knowledge in very complex queries)

Thanks in advance

Moving to Transact-SQL forum...|||I don't see any relationship between Address table and the Product_Type at all. How is it related ?|||

use a CROSS JOIN in SQL server if you want a combination of all products and addresses.

eg

select a.address, Address p.Product Type from address a cross join product_type p

Note that if you are wanting a cartesian product here, you should specify no join criteria, as you want every address and product combination. That should be much quicker than doing it in client side code. However, you will need some kind of join to get the totals for each product, as a cross joins blindly combines all rows from 1 table to all the rows from another. I need further clarification here.

You will then have to turn the results into a pivot table. In SQL 2005, use the PIVOT function, in SQL 2003 and earlier, you will need to use a case statement:

SELECT a.address,

CASE

WHEN p.Product_type = 'Product A' -- whatever first product type is

THEN ..... -- your code, I think from your example you want a sum() here

WHEN p.Product_type = 'Product A' --

THEN

etc

END

from.......

Hope that helps

from address a cross join product_type p

GROUP BY a.address

Complex query problem

Hello!

I have a query that queries two tables (including a self join on one of them) and returns a result set that almost (but not quite) gives me what I want , and was wondering if someone could give me some pointers. Rather than show the whole query (complex), I'll show the result set and describe what i want:

Tab1.efID Tab1.VID Tab2.efID Tab2.VID
$00046342 7 $00046342 8
$00046342 7 $00046342 19
$00046342 18 $00046342 19

I want to amend the query so that it returns a count of the distinct rows of Tab1.efID,Tab1.VID - from the above result set, it should return just a count of the first and third rows, i.e 2

The statement SELECT DISTINCT Tab1.efID,Tab1.VID would return the two rows, but obviously SELECT COUNT(DISTINCT Tab1.efID,Tab1.VID) doesn't work.

SELECT COUNT(DISTINCT Tab1.efID + CAST(Tab1.VID AS VARCHAR(2))) does work, but i thought perhaps there may be a more elegant solution - anyone have any pointers?

Cheers
GregSee your other thread (http://www.dbforums.com/t1008216.html) asking this question.

-PatP

Complex queries using WHERE and mix of OR and AND

How do you effectively mix OR and AND together? I have the query below for m
y
SEARCH page. I would like the users to have the option of selecting one fiel
d
to search with OR selecting pairs of fields together to search the database
with. The problem is, with the SQL statement below, OR works (select 1 field
to search with) but AND does not (if I use more than 1 field to search with,
the search returns all entries in the database.
Could someone pls point out to me what I'm doing wrong? I'd really
appreciate it.
SELECT vNXX, vLN, vMN, vDT, vYR, vAGNT, vORD, vSAVE
FROM salesdb
WHERE (vNXX LIKE 'varNXX' AND vLN LIKE 'varLINE') OR (vMN LIKE 'varMONTH'
AND vAGNT LIKE 'varAGENT'AND vYR LIKE 'varYEAR' AND vSAVE LIKE 'varSAVE') OR
(vMN LIKE 'varMONTH' AND vYR LIKE 'varYEAR' AND vSAVE LIKE 'varSAVE') OR (vM
N
LIKE 'varMONTH' AND vYR LIKE 'varYEAR') OR (vNXX LIKE 'varNXX') OR (vLN LIKE
'varLINE') OR (vMN LIKE 'varMONTH') OR (vDT LIKE 'varDATE') OR (vYR LIKE
'varYEAR') OR (vAGNT LIKE 'varAGENT') OR (vORD LIKE 'varORD') OR (vSAVE LIKE
'varSAVE')
ORDER BY vMN DESC, vDT DESC, vYR DESC> AND does not (if I use more than 1 field to search with,
> the search returns all entries in the database.
Could you post a working example of this so that we can understand what you
mean. There's no reason why you can't use as many ANDs and ORs as you need
in a WHERE clause. AND takes precedence over OR unless you use brackets to
alter the order of evaluation.
David Portas
SQL Server MVP
--|||Hi David,
First I formatted your SQL using www.sqlinform.com .
Then I have seen that some conditions are not logic, e.g. using
(
vNXX LIKE 'varNXX'
AND vLN LIKE 'varLINE'
)
together with
(
vNXX LIKE 'varNXX'
)
because this condition is true independent from the value of vLN. You
will need to code your SQL in a different way.
Regards
Guido
SELECT vNXX, vLN, vMN, vDT, vYR, vAGNT, vORD, vSAVE
FROM salesdb
WHERE
(
vNXX LIKE 'varNXX'
AND vLN LIKE 'varLINE'
)
OR
(
vMN LIKE 'varMONTH'
AND vAGNT LIKE 'varAGENT'
AND vYR LIKE 'varYEAR'
AND vSAVE LIKE 'varSAVE'
)
OR
(
vMN LIKE 'varMONTH'
AND vYR LIKE 'varYEAR'
AND vSAVE LIKE 'varSAVE'
)
OR
(
vMN LIKE 'varMONTH'
AND vYR LIKE 'varYEAR'
)
OR
(
vNXX LIKE 'varNXX'
)
OR
(
vLN LIKE 'varLINE'
)
OR
(
vMN LIKE 'varMONTH'
)
OR
(
vDT LIKE 'varDATE'
)
OR
(
vYR LIKE 'varYEAR'
)
OR
(
vAGNT LIKE 'varAGENT'
)
OR
(
vORD LIKE 'varORD'
)
OR
(
vSAVE LIKE 'varSAVE'
)
ORDER BY vMN DESC, vDT DESC, vYR DESC

Complex queries run slow after SP4 installed

My company's production SQL Server 2000 becomes very slow after SP4 is
installed.
We have figured out that several complex queries which only took 2 seconds
to complete under SP3a now take more than 10 minutes to finish in SP4! The
sympton is similar to the following KB:
http://support.microsoft.com/kb/826906/#XSLTH3120121122120121120120
Has anyone experienced similar problems?
Where can I get the hotfix?
regards
ArthurArthur,
I had the same problem. Product support recommended I install build
2145, but that really didn't help in my case. I wound up re-writing my
queries to not use views -- if you can, I recommend you do the same. Queries
on the base tables are still (mostly) fast.
Regards,
Jonathan
"Arthur" wrote:
> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/826906/#XSLTH3120121122120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>
>|||Dear Jonathan
Thanks for your response.
The bad thing is that over 90% of our stored procedures or queries are based
on views and thus rewritting and testing all of them is just impossible.
Do you think I can fall back to SP3 safely (suppose I have the database
backup before the upgrade)?
regards
Arthur
"Jonathan Levine" <myfoo2@.nospam.nospam> wrote in message
news:C2490F12-DBF9-4854-9F78-06FE833FD131@.microsoft.com...
> Arthur,
> I had the same problem. Product support recommended I install build
> 2145, but that really didn't help in my case. I wound up re-writing my
> queries to not use views -- if you can, I recommend you do the same.
Queries
> on the base tables are still (mostly) fast.
> Regards,
> Jonathan
> "Arthur" wrote:
> > My company's production SQL Server 2000 becomes very slow after SP4 is
> > installed.
> >
> > We have figured out that several complex queries which only took 2
seconds
> > to complete under SP3a now take more than 10 minutes to finish in SP4!
The
> > sympton is similar to the following KB:
> >
> > http://support.microsoft.com/kb/826906/#XSLTH3120121122120121120120
> >
> > Has anyone experienced similar problems?
> >
> > Where can I get the hotfix?
> >
> > regards
> >
> > Arthur
> >
> >
> >|||Arthur,
"Arthur" wrote:
> Do you think I can fall back to SP3 safely (suppose I have the database
> backup before the upgrade)?
If you can roll back, I would do so ASAP. The problem just gets worse
and worse as the queries get longer (I had queries that took 3 or 4 minutes
in SP3a that took several hours in SP4).
-- J|||This may or may not apply to you but we experienced some problems recently
where queries that run fast on my computer were taking forever on the
client's site.
We discovered that SQL Server was reporting an error about parallel queries.
We weren't doing any but we configured the server to only use 1 CPU instead
of "all available processors" and the problem went away.
Good luck
Richard Speiss
"Arthur" <arthurw@.newgroup.nospam> wrote in message
news:u9pGzO9lFHA.2156@.TK2MSFTNGP14.phx.gbl...
> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/826906/#XSLTH3120121122120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>

Complex queries run slow after SP4 installed

My company's production SQL Server 2000 becomes very slow after SP4 is
installed.
We have figured out that several complex queries which only took 2 seconds
to complete under SP3a now take more than 10 minutes to finish in SP4! The
sympton is similar to the following KB:
http://support.microsoft.com/kb/8269...22120121120120
Has anyone experienced similar problems?
Where can I get the hotfix?
regards
Arthur
Arthur,
I had the same problem. Product support recommended I install build
2145, but that really didn't help in my case. I wound up re-writing my
queries to not use views -- if you can, I recommend you do the same. Queries
on the base tables are still (mostly) fast.
Regards,
Jonathan
"Arthur" wrote:

> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/8269...22120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>
>
|||Dear Jonathan
Thanks for your response.
The bad thing is that over 90% of our stored procedures or queries are based
on views and thus rewritting and testing all of them is just impossible.
Do you think I can fall back to SP3 safely (suppose I have the database
backup before the upgrade)?
regards
Arthur
"Jonathan Levine" <myfoo2@.nospam.nospam> wrote in message
news:C2490F12-DBF9-4854-9F78-06FE833FD131@.microsoft.com...
> Arthur,
> I had the same problem. Product support recommended I install build
> 2145, but that really didn't help in my case. I wound up re-writing my
> queries to not use views -- if you can, I recommend you do the same.
Queries[vbcol=seagreen]
> on the base tables are still (mostly) fast.
> Regards,
> Jonathan
> "Arthur" wrote:
seconds[vbcol=seagreen]
The[vbcol=seagreen]
|||Arthur,
"Arthur" wrote:
> Do you think I can fall back to SP3 safely (suppose I have the database
> backup before the upgrade)?
If you can roll back, I would do so ASAP. The problem just gets worse
and worse as the queries get longer (I had queries that took 3 or 4 minutes
in SP3a that took several hours in SP4).
-- J
|||This may or may not apply to you but we experienced some problems recently
where queries that run fast on my computer were taking forever on the
client's site.
We discovered that SQL Server was reporting an error about parallel queries.
We weren't doing any but we configured the server to only use 1 CPU instead
of "all available processors" and the problem went away.
Good luck
Richard Speiss
"Arthur" <arthurw@.newgroup.nospam> wrote in message
news:u9pGzO9lFHA.2156@.TK2MSFTNGP14.phx.gbl...
> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/8269...22120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>

Complex queries run slow after SP4 installed

My company's production SQL Server 2000 becomes very slow after SP4 is
installed.
We have figured out that several complex queries which only took 2 seconds
to complete under SP3a now take more than 10 minutes to finish in SP4! The
sympton is similar to the following KB:
http://support.microsoft.com/kb/826...122120121120120
Has anyone experienced similar problems?
Where can I get the hotfix?
regards
ArthurArthur,
I had the same problem. Product support recommended I install build
2145, but that really didn't help in my case. I wound up re-writing my
queries to not use views -- if you can, I recommend you do the same. Querie
s
on the base tables are still (mostly) fast.
Regards,
Jonathan
"Arthur" wrote:

> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/826...122120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>
>|||Dear Jonathan
Thanks for your response.
The bad thing is that over 90% of our stored procedures or queries are based
on views and thus rewritting and testing all of them is just impossible.
Do you think I can fall back to SP3 safely (suppose I have the database
backup before the upgrade)?
regards
Arthur
"Jonathan Levine" <myfoo2@.nospam.nospam> wrote in message
news:C2490F12-DBF9-4854-9F78-06FE833FD131@.microsoft.com...
> Arthur,
> I had the same problem. Product support recommended I install build
> 2145, but that really didn't help in my case. I wound up re-writing my
> queries to not use views -- if you can, I recommend you do the same.
Queries[vbcol=seagreen]
> on the base tables are still (mostly) fast.
> Regards,
> Jonathan
> "Arthur" wrote:
>
seconds[vbcol=seagreen]
The[vbcol=seagreen]|||Arthur,
"Arthur" wrote:
> Do you think I can fall back to SP3 safely (suppose I have the database
> backup before the upgrade)?
If you can roll back, I would do so ASAP. The problem just gets worse
and worse as the queries get longer (I had queries that took 3 or 4 minutes
in SP3a that took several hours in SP4).
-- J|||This may or may not apply to you but we experienced some problems recently
where queries that run fast on my computer were taking forever on the
client's site.
We discovered that SQL Server was reporting an error about parallel queries.
We weren't doing any but we configured the server to only use 1 CPU instead
of "all available processors" and the problem went away.
Good luck
Richard Speiss
"Arthur" <arthurw@.newgroup.nospam> wrote in message
news:u9pGzO9lFHA.2156@.TK2MSFTNGP14.phx.gbl...
> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/826...122120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>

complex queries and performance

i have a question on the above mention... say if i need to run a batch
procedure with very complex and multiple set base CRUD functionalites, does
it make any difference if i break them down into 1 store procedure per query
or multiple query per procedure. Presumbly everything is set to run with no
lock, does it make any difference?It usually depends. Consider recompilations: what needs to be
recompiled if you refresh statistics on one table? What needs to be
recompiled when you add an index on one table? How much time does
recompilation take?