clustered index or simple constraint in a fact table?

Hi,
What do you recommand to insure the integrity of a fact table?
a cluster index?
or a constraint?
or an SQL script?
do you recommand to allways having a clustering index on a fact table? or
just on the dimension tables and 1 index by linked column in the fact table?
I don't want to test each possibility, so I want your help ;)
Jerome.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 2004-02-28What do you mean by integrity? A clustered index does not have to be unique.
There are very few times I will have any table without a clustered index, DW
or OLTP.
Linked column? Do you mean a foreign key?
Ray Higdon MCSE, MCDBA, CCNA
--
"Jj" <willgart@._A_hotAmail_A_.com> wrote in message
news:O06B%23Zj$DHA.3184@.TK2MSFTNGP09.phx.gbl...
> Hi,
> What do you recommand to insure the integrity of a fact table?
> a cluster index?
> or a constraint?
> or an SQL script?
> do you recommand to allways having a clustering index on a fact table? or
> just on the dimension tables and 1 index by linked column in the fact
table?
> I don't want to test each possibility, so I want your help ;)
> Jerome.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.600 / Virus Database: 381 - Release Date: 2004-02-28
>|||Also, these links may help
http://www.microsoft.com/sql/techin...performance.asp
Ray Higdon MCSE, MCDBA, CCNA
--
"Jj" <willgart@._A_hotAmail_A_.com> wrote in message
news:O06B%23Zj$DHA.3184@.TK2MSFTNGP09.phx.gbl...
> Hi,
> What do you recommand to insure the integrity of a fact table?
> a cluster index?
> or a constraint?
> or an SQL script?
> do you recommand to allways having a clustering index on a fact table? or
> just on the dimension tables and 1 index by linked column in the fact
table?
> I don't want to test each possibility, so I want your help ;)
> Jerome.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.600 / Virus Database: 381 - Release Date: 2004-02-28
>|||Yes, sorry, I talk about a unique clustered index.
Actually my fact table has a primary key which used all the foreign key
columns.
I've set all my indexes in a separate file to improove the performance, but
because Most part of the time, SQL Server used the cluster index instead-of
using the other indexes, the indexes files are not used, so no performance
improovment..
If I drop the cluster index and keep only the indexes associated with each
foreign key cloumn, the queries are not rightly optimized and SQL don't used
some indexes.
Fact table
- ForeKey1 --> idx 1
- ForeKey2 --> idx 2
- ForeKey3 --> idx 3
- ForeKey4 --> idx 4
- Measure 1
- Measure 2
My usage is not an OLAP cube, but only a reporting tool.
Well, I'm disapointed when I see how SQL Server optimize the queries. Also
the query plan never represent the real value of the result produce by the
query but my statistics are created and updated.
So I work to optimize all of this.
Thanks for your guide.
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> a crit dans le message de
news:%233W1VNl$DHA.2632@.TK2MSFTNGP12.phx.gbl...
> What do you mean by integrity? A clustered index does not have to be
unique.
> There are very few times I will have any table without a clustered index,
DW
> or OLTP.
> Linked column? Do you mean a foreign key?
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Jj" <willgart@._A_hotAmail_A_.com> wrote in message
> news:O06B%23Zj$DHA.3184@.TK2MSFTNGP09.phx.gbl...
or
> table?
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 2004-02-28|||It depends on your queries as to what will be used. Be aware that clustered
indexes sit at the leaf level of the data so you can not split them up. How
are you determining that the queries are not optimized? The best way I've
found for this is to use the "set statistics IO on" command and look at
logical IO, which is the number of times a page is hit (not number of
pages). You might post your queries and we can look at why optimizer may
choose one over the other.
Ray Higdon MCSE, MCDBA, CCNA
--
"Jj" <willgart@._A_hotAmail_A_.com> wrote in message
news:OhQeYss$DHA.2432@.TK2MSFTNGP11.phx.gbl...
> Yes, sorry, I talk about a unique clustered index.
> Actually my fact table has a primary key which used all the foreign key
> columns.
> I've set all my indexes in a separate file to improove the performance,
but
> because Most part of the time, SQL Server used the cluster index
instead-of
> using the other indexes, the indexes files are not used, so no performance
> improovment..
> If I drop the cluster index and keep only the indexes associated with each
> foreign key cloumn, the queries are not rightly optimized and SQL don't
used
> some indexes.
> Fact table
> - ForeKey1 --> idx 1
> - ForeKey2 --> idx 2
> - ForeKey3 --> idx 3
> - ForeKey4 --> idx 4
> - Measure 1
> - Measure 2
> My usage is not an OLAP cube, but only a reporting tool.
> Well, I'm disapointed when I see how SQL Server optimize the queries. Also
> the query plan never represent the real value of the result produce by the
> query but my statistics are created and updated.
> So I work to optimize all of this.
> Thanks for your guide.
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> a crit dans le message de
> news:%233W1VNl$DHA.2632@.TK2MSFTNGP12.phx.gbl...
> unique.
index,
> DW
> or
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.600 / Virus Database: 381 - Release Date: 2004-02-28
>|||there is too many queries
because the users can creates the queries they want on the fly.
there is some predefined reports, and I'll optimize these specific reports
so a user can filter on 1 or more columns, links 1 or more dimension tables
etc...
Also I've a lot of distinct count measures
I'll check the IO stats this week
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> a crit dans le message de
news:eDtNUXt$DHA.2216@.TK2MSFTNGP10.phx.gbl...
> It depends on your queries as to what will be used. Be aware that
clustered
> indexes sit at the leaf level of the data so you can not split them up.
How
> are you determining that the queries are not optimized? The best way I've
> found for this is to use the "set statistics IO on" command and look at
> logical IO, which is the number of times a page is hit (not number of
> pages). You might post your queries and we can look at why optimizer may
> choose one over the other.
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Jj" <willgart@._A_hotAmail_A_.com> wrote in message
> news:OhQeYss$DHA.2432@.TK2MSFTNGP11.phx.gbl...
> but
> instead-of
performance
each
> used
Also
the
> index,
table?
fact
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 2004-02-28