multiple Index files

1. Is there any benifit to creating a distinct index file for each column to be indexe
Index file 1 - Field
Index File 2 - Field
Index file 3 - Field
Index file 4 - Field
2. Is there a benifit to creating multiple groups of indexes ie
Index file 1 - Field A, Field B, Filed
Index File 2 - Field B, Field
Index file 3 - Field B, FieldHi Mark.
There are benefits / limitations in taking either approach.
Multi-column indexes may "cover" more queries or join predicates than single
column queries and provide better performance that way.
Distinct indexes provide compact index width for fast join or seek
operations where query predicates or filter arguments are on those columns.
SQL Server can also intersect indexes during optimization which recovers
some of the benefits that would have come from multi-column indexes.
If your application is relatively read only (data mart / warehouse) & not
bound by disk space limitations you might find that the more indexes you
have the better, as there are simply more options for the optimizer to suit
more queries.
If your application is more of an online transaction processinng
application, you probably want to limit which indexes you add as each index
needs updating as rows are inserted, updated & deleted.
The Index Tuning Wizard can be helpful in determining which indexes are the
best for the database & queries you're using.
HTH
Regards,
Greg Linwood
SQL Server MVP
"MarkFirth" <anonymous@.discussions.microsoft.com> wrote in message
news:406C0C47-5B5C-45EF-93C2-DD721278F61F@.microsoft.com...
> 1. Is there any benifit to creating a distinct index file for each column
to be indexed
> Index file 1 - Field A
> Index File 2 - Field B
> Index file 3 - Field B
> Index file 4 - Field H
> 2. Is there a benifit to creating multiple groups of indexes ie:
> Index file 1 - Field A, Field B, Filed C
> Index File 2 - Field B, Field C
> Index file 3 - Field B, Field H
>|||Mark
It depends on what are you going to query and how.
If you issue SELECT Field A, Field B, Filed C FROM
SomeTable ORDER BY Field A, Field B, Filed C or
WHERE clause
so having covering index on these columns will gain perfomance improving.
"MarkFirth" <anonymous@.discussions.microsoft.com> wrote in message
news:406C0C47-5B5C-45EF-93C2-DD721278F61F@.microsoft.com...
> 1. Is there any benifit to creating a distinct index file for each column
to be indexed
> Index file 1 - Field A
> Index File 2 - Field B
> Index file 3 - Field B
> Index file 4 - Field H
> 2. Is there a benifit to creating multiple groups of indexes ie:
> Index file 1 - Field A, Field B, Filed C
> Index File 2 - Field B, Field C
> Index file 3 - Field B, Field H
>sql

Multiple DISTINCTS?

Hello,
Is it possible to constrain a query so that I receive only the duplicate rows (all column data matches)? For example:
SELECT DISTINCT COL1,
DISTINCT COL2,DISTINCT COL3
FROM TABLE
I did a search on multiple DISTINCTS but came up with nothing. Thanks in advance.DISTINCT applys to the whole row of the result set, not individual columns of it. When you do aSELECT DISTINCT col1, col2, col3
FROM myTableyou will get one row for any given combination of col1, col2, and col3. You can use DISTINCT within aggragate functions to get aggrigates of the unique values, but when applied against a result set, DISTINCT applies to the whole set (row).

-PatP|||uh, huh?

SELECT DISTINCT Col1, Col2, Col3

Will give you 1 unique row..

You want to only see dups?

SELECT Col1, Col2, Col3
FROM myTable99
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1

Is that what your after?|||Originally posted by Brett Kaiser
uh, huh?

SELECT DISTINCT Col1, Col2, Col3

Will give you 1 unique row..

You want to only see dups?

SELECT Col1, Col2, Col3
FROM myTable99
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1

Is that what your after?

------------------

Thanks for your suggestions. I am looking for a resultset of:

COL1 COL2 COL3
---------
brown tall dog
brown tall dog
brown tall dog

Thanks again for your help.|||Did the code give you what you wanted...I'm not sure...|||Originally posted by Brett Kaiser
Did the code give you what you wanted...I'm not sure...

Not yet, but I could be doing something wrong.

I've tried the following:
SELECT DISTINCT COL1, COL2, COL3
FROM myTABLE

and:
SELECT COL1, COL2, COL3
FROM myTABLE
GROUP BY COL1, COL2, COL3

and:
SELECT DISTINCT COL1, COL2, COL3
FROM myTABLE
HAVING COUNT(*) > 1

and I've tried:
SELECT COL1, COL2, COL3
FROM myTABLE
GROUP BY COL1, COL2, COL3
HAVING COUNT(*) > 1|||Well that should work...

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(25),Col2 varchar(25),Col3 varchar(25))
GO

INSERT INTO myTable99(Col1,Col2,Col3)
select 'Brown','Tall','Dog' UNION ALL
select 'Brown','Tall','Dog' UNION ALL
select 'Brown','Tall','Dog' UNION ALL
select 'Blonde','Small','Pussy cat' UNION ALL
select 'Red','Medium','Snapper Turtle'
GO

SELECT Col1, Col2, Col3
FROM myTable99
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1
GO

DROP TABLE myTable99
GO

Oh, are you looking for all three?|||SELECT * FROM myTable99 o WHERE EXISTS(
SELECT *
FROM myTable99 i
WHERE o.Col1 = i.Col1
AND o.Col2 = i.Col2
AND o.Col3 = i.Col3
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1)
GO

All three...|||Brett,
That should do it! Combined with the info in the links provided in this thread: http://www.dbforums.com/t991775.html

I should be able to put something together. I really do appreciate all your hard work.

Regards,
Americus Johnson

Thanks also to Pat!|||Hard Work...Lord no...

That's why I became a dba...

:D

PS Don't forget to take ALL of your animal freinds...ya might get lucky|||Originally posted by Brett Kaiser
Hard Work...Lord no...

That's why I became a dba...

:D

PS Don't forget to take ALL of your animal freinds...ya might get lucky

True dat.|||yup

multiple distinct and time...

Here comes the table:
Id_Prices | Shop | Product | Price | tStamp
----
--
1 eMall Pen 10 20.5.2005
2 eMall Pen 11 21.5.2005
3 eMall Pen 9 22.5.2005
4 webShop Pen 10 22.5.2005
5 webShop Pen 12 23.5.2005
6 InetShop Pen 10 20.5.2005
7 netOultel Pen 9 19.5.2005
8 netOultel Pencil 5 19.5.2005
9 netOultel Pencil 6 20.5.2005
table hold records of:
multiple PRICES (distinguished by date)
for various PRODUCTS
from various SHOPS
now...
A) I need to get only most curent (date) PRICES from all SHOPs
for specific PRODUCT e.q.
Pen eMall 22.5.2005 9
webShop 23.5.2005 12
InetShop 20.5.2005 10
netOutlet 19.5.2005 9
B) List of all products and their most recent prices...
Pen eMall 22.5.2005 9
webShop 23.5.2005 12
InetShop 20.5.2005 10
netOutlet 19.5.2005 9
Pencil netOultel 20.5.2005 6
Anybody can point me in the right direction how to aproach this...?
PS: DB table structure and normalization indexes omited for brewity
thanx for any hint... PetttHi ,
Try this :
create table #tmp
(
Id_Prices integer ,
Shop char(9),
Product char(6),
Price integer,
tStamp char (9)
) ;
---
insert into #tmp values (1, 'eMall','Pen',10,'20.5.2005');
insert into #tmp values (2, 'eMall','Pen',11,'21.5.2005');
insert into #tmp values (3, 'eMall','Pen',9,'22.5.2005');
insert into #tmp values (4, 'webShop','Pen',10,'22.5.2005');
insert into #tmp values (5, 'webShop','Pen',12,'23.5.2005');
insert into #tmp values (6, 'InetShop','Pen',10,'20.5.2005');
insert into #tmp values (7, 'netOultel','Pen',9,'19.5.2005');
insert into #tmp values (8, 'netOultel','Pencil',5,'19.5.2005');
insert into #tmp values (9, 'netOultel','Pencil',6,'20.5.2005');
---
select a.* , b.price
from
(
select product , shop , max (tstamp) tstamp
from #tmp
group by product , shop
) a ,
(
select *
from #tmp
) b
where a.shop= b.shop
and a.product = b.product
and a.tstamp = b.tstamp
-- and b.product = 'Pen'
group by a.product , a.shop , a.tstamp , b.price
order by 1
"Petr SIMUNEK" wrote:

> Here comes the table:
> Id_Prices | Shop | Product | Price | tStamp
> ----
--
> 1 eMall Pen 10 20.5.200
5
> 2 eMall Pen 11 21.5.200
5
> 3 eMall Pen 9 22.5.20
05
> 4 webShop Pen 10 22.5.2005
> 5 webShop Pen 12 23.5.2005
> 6 InetShop Pen 10 20.5.2005
> 7 netOultel Pen 9 19.5.200
5
> 8 netOultel Pencil 5 19.5.200
5
> 9 netOultel Pencil 6 20.5.200
5
>
> table hold records of:
> multiple PRICES (distinguished by date)
> for various PRODUCTS
> from various SHOPS
>
> now...
> A) I need to get only most curent (date) PRICES from all SHOPs
> for specific PRODUCT e.q.
> Pen eMall 22.5.2005 9
> webShop 23.5.2005 12
> InetShop 20.5.2005 10
> netOutlet 19.5.2005 9
> B) List of all products and their most recent prices...
> Pen eMall 22.5.2005 9
> webShop 23.5.2005 12
> InetShop 20.5.2005 10
> netOutlet 19.5.2005 9
> Pencil netOultel 20.5.2005 6
> Anybody can point me in the right direction how to aproach this...?
> PS: DB table structure and normalization indexes omited for brewity
> thanx for any hint... Pettt
>
>
>

multiple distinct and time...

Here comes the table:
Id_Prices | Shop | Product | Price | tStamp
----
--
1 eMall Pen 10 20.5.2005
2 eMall Pen 11 21.5.2005
3 eMall Pen 9 22.5.2005
4 webShop Pen 10 22.5.2005
5 webShop Pen 12 23.5.2005
6 InetShop Pen 10 20.5.2005
7 netOultel Pen 9 19.5.2005
8 netOultel Pencil 5 19.5.2005
9 netOultel Pencil 6 20.5.2005
table hold records of:
multiple PRICES (distinguished by date)
for various PRODUCTS
from various SHOPS
now...
A) I need to get only most curent (date) PRICES from all SHOPs
for specific PRODUCT e.q.
Pen eMall 22.5.2005 9
webShop 23.5.2005 12
InetShop 20.5.2005 10
netOutlet 19.5.2005 9
B) List of all products and their most recent prices...
Pen eMall 22.5.2005 9
webShop 23.5.2005 12
InetShop 20.5.2005 10
netOutlet 19.5.2005 9
Pencil netOultel 20.5.2005 6
Anybody can point me in the right direction how to aproach this...?
PS: DB table structure and normalization indexes omited for brewity
thanx for any hint... PetttHi
The difference between the first and second query is that you are
resistricting to be a single product. One possible solution would be:
CREATE TABLE #prices ( Id_Prices int, Shop varchar(20), Product varchar(20),
Price int , tStamp datetime )
INSERT INTO #prices ( Id_Prices, Shop, Product, Price, tStamp )
SELECT 1, 'eMall', 'Pen', 10, '20050520'
UNION ALL SELECT 2, 'eMall', 'Pen', 11, '20050521'
UNION ALL SELECT 3, 'eMall', 'Pen', 9, '20050522'
UNION ALL SELECT 4, 'webShop', 'Pen', 10, '20050522'
UNION ALL SELECT 5, 'webShop', 'Pen', 12, '20050523'
UNION ALL SELECT 6, 'InetShop', 'Pen', 10, '20050520'
UNION ALL SELECT 7, 'netOultel', 'Pen', 9, '20050519'
UNION ALL SELECT 8, 'netOultel', 'Pencil', 5, '20050519'
UNION ALL SELECT 9, 'netOultel', 'Pencil', 6, '20050520'
-- For Pen only
SELECT p.*
from #prices p
JOIN ( SELECT shop, product, Max(tstamp) as tstamp
from #prices GROUP BY shop, product ) q ON q.shop = p.shop AND q.product =
p.product AND p.tstamp = q.tstamp
WHERE p.product = 'Pen'
or alternatively:
SELECT p.*
from #prices p
WHERE tstamp = ( SELECT Max(tstamp)
from #prices q WHERE q.shop = p.shop AND q.product = p.product AND
q.product = 'Pen')
AND p.product = 'Pen'
-- For all products
SELECT p.*
from #prices p
JOIN ( SELECT shop, product, Max(tstamp) as tstamp
from #prices GROUP BY shop, product ) q ON q.shop = p.shop AND q.product =
p.product AND p.tstamp = q.tstamp
If you are using this join alot you may want to create a view.
John
"Petr SIMUNEK" wrote:

> Here comes the table:
> Id_Prices | Shop | Product | Price | tStamp
> ----
--
> 1 eMall Pen 10 20.5.200
5
> 2 eMall Pen 11 21.5.200
5
> 3 eMall Pen 9 22.5.20
05
> 4 webShop Pen 10 22.5.2005
> 5 webShop Pen 12 23.5.2005
> 6 InetShop Pen 10 20.5.2005
> 7 netOultel Pen 9 19.5.200
5
> 8 netOultel Pencil 5 19.5.200
5
> 9 netOultel Pencil 6 20.5.200
5
>
> table hold records of:
> multiple PRICES (distinguished by date)
> for various PRODUCTS
> from various SHOPS
>
> now...
> A) I need to get only most curent (date) PRICES from all SHOPs
> for specific PRODUCT e.q.
> Pen eMall 22.5.2005 9
> webShop 23.5.2005 12
> InetShop 20.5.2005 10
> netOutlet 19.5.2005 9
> B) List of all products and their most recent prices...
> Pen eMall 22.5.2005 9
> webShop 23.5.2005 12
> InetShop 20.5.2005 10
> netOutlet 19.5.2005 9
> Pencil netOultel 20.5.2005 6
> Anybody can point me in the right direction how to aproach this...?
> PS: DB table structure and normalization indexes omited for brewity
> thanx for any hint... Pettt
>
>|||thanx a lot guys...
It works like a charm of course... thanks again
PS: would you care to share with me how long experience with SQL do you have
?
(so I know how long the road is... :o))
thanx|||Hi
It is a never ending road... when you think you know it all something new
always turns up...and then there is the ageing process where you start to
forget what you have learn't anyhow.
John
"Petr SIMUNEK" wrote:

> thanx a lot guys...
> It works like a charm of course... thanks again
> PS: would you care to share with me how long experience with SQL do you ha
ve
> ?
> (so I know how long the road is... :o))
> thanx
>
>sql