Indexing/Unique Key

This is my first post here, hopefully you folks can help me.
I have the typical Invoice Header and Invoice Detail Fact Tables.
Ive read that you should declare a clustered index on all of the
foreign dimension keys in a fact table (for SQL Server).
However, that wouldnt be unique as a customer can be invoiced for the
same part on the same day, etc.. right?
So what do people typically do - declare a unique clustered index on
the Degenerate dimensions of Invoice Number and Invoice Line Number?
What is the best index to put on these fact tables?
Thanks,
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Wareho...9
0.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=890048Nile,
there is no rule that says detail level fact tables must have the
combination of keys be unique. Though on a summary level fact table the
rule is they must be unique.
I believe you can make a non-unique clustered index on 2000 with no ill
effects.
Personally, nowadays I always create a single integer key at the front
of txn level fact tables and this is the primary key and then I just
create indexes on the columns in the fact tables as required......I
should note that I have not built a decent sized DW on 2000....I've
been doing this on other databases with bit mapped indexes...I did this
on 7 and it all worked ok after some coaxing.....I hope to get back
into the world of building the odd reasonably sized DW on sql server in
the not too distant future...
Peter|||"" wrote:
> Nile,
> there is no rule that says detail level fact tables must have
> the
> combination of keys be unique. Though on a summary level fact
> table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000
> with no ill
> effects.
> Personally, nowadays I always create a single integer key at
> the front
> of txn level fact tables and this is the primary key and then
> I just
> create indexes on the columns in the fact tables as
> required......I
> should note that I have not built a decent sized DW on
> 2000....I've
> been doing this on other databases with bit mapped indexes...I
> did this
> on 7 and it all worked ok after some coaxing.....I hope to
> get back
> into the world of building the odd reasonably sized DW on sql
> server in
> the not too distant future...
> Peter
Thanks for your reply. Ill probably create a composite primary key
on that unique identity column plus a date key (smallint) so that the
index can be used. That approach was documented on msdn - what do you
think?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Wareho...9
0.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=892551|||Peter Nolan wrote:
> Nile,
> there is no rule that says detail level fact tables must have the
> combination of keys be unique. Though on a summary level fact table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000 with no ill
> effects.
> Personally, nowadays I always create a single integer key at the front
> of txn level fact tables and this is the primary key and then I just
> create indexes on the columns in the fact tables as required......I
> should note that I have not built a decent sized DW on 2000....I've
> been doing this on other databases with bit mapped indexes...I did this
> on 7 and it all worked ok after some coaxing.....I hope to get back
> into the world of building the odd reasonably sized DW on sql server in
> the not too distant future...
> Peter
>
Peter, why bother to create a primary key on a fact table at all? Its
never used for lookup, so its a wasted index.
My $0.02
- rick|||Hi Rick,
this was discussed at some length on dwlist
(datawarehousing.com)...many people say they see no use for a unique
key on a fact table...
However, over the last few years at some of the sites I have worked on
we have found some great uses for putting a single integer key on the
front of a fact table. We do this and use it often. (But we don't
publish what we use it for.)
Best Regards
Peter|||"" wrote:
> Hi Rick,
> this was discussed at some length on dwlist
> (datawarehousing.com)...many people say they see no use for a
> unique
> key on a fact table...
> However, over the last few years at some of the sites I have
> worked on
> we have found some great uses for putting a single integer key
> on the
> front of a fact table. We do this and use it often. (But we
> don't
> publish what we use it for.)
> Best Regards
> Peter
Can you please let me know what you use it for?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Wareho...9
0.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=898374

Indexing/Unique Key

This is my first post here, hopefully you folks can help me.
I have the typical Invoice Header and Invoice Detail Fact Tables.
Ive read that you should declare a clustered index on all of the
foreign dimension keys in a fact table (for SQL Server).
However, that wouldnt be unique as a customer can be invoiced for the
same part on the same day, etc.. right?
So what do people typically do - declare a unique clustered index on
the Degenerate dimensions of Invoice Number and Invoice Line Number?
What is the best index to put on these fact tables?
Thanks,
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=890048
Nile,
there is no rule that says detail level fact tables must have the
combination of keys be unique. Though on a summary level fact table the
rule is they must be unique.
I believe you can make a non-unique clustered index on 2000 with no ill
effects.
Personally, nowadays I always create a single integer key at the front
of txn level fact tables and this is the primary key and then I just
create indexes on the columns in the fact tables as required......I
should note that I have not built a decent sized DW on 2000....I've
been doing this on other databases with bit mapped indexes...I did this
on 7 and it all worked ok after some coaxing.....I hope to get back
into the world of building the odd reasonably sized DW on sql server in
the not too distant future...
Peter
|||"" wrote:
> Nile,
> there is no rule that says detail level fact tables must have
> the
> combination of keys be unique. Though on a summary level fact
> table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000
> with no ill
> effects.
> Personally, nowadays I always create a single integer key at
> the front
> of txn level fact tables and this is the primary key and then
> I just
> create indexes on the columns in the fact tables as
> required......I
> should note that I have not built a decent sized DW on
> 2000....I've
> been doing this on other databases with bit mapped indexes...I
> did this
> on 7 and it all worked ok after some coaxing.....I hope to
> get back
> into the world of building the odd reasonably sized DW on sql
> server in
> the not too distant future...
> Peter
Thanks for your reply. Ill probably create a composite primary key
on that unique identity column plus a date key (smallint) so that the
index can be used. That approach was documented on msdn - what do you
think?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=892551
|||Peter Nolan wrote:
> Nile,
> there is no rule that says detail level fact tables must have the
> combination of keys be unique. Though on a summary level fact table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000 with no ill
> effects.
> Personally, nowadays I always create a single integer key at the front
> of txn level fact tables and this is the primary key and then I just
> create indexes on the columns in the fact tables as required......I
> should note that I have not built a decent sized DW on 2000....I've
> been doing this on other databases with bit mapped indexes...I did this
> on 7 and it all worked ok after some coaxing.....I hope to get back
> into the world of building the odd reasonably sized DW on sql server in
> the not too distant future...
> Peter
>
Peter, why bother to create a primary key on a fact table at all? Its
never used for lookup, so its a wasted index.
My $0.02
- rick
|||Hi Rick,
this was discussed at some length on dwlist
(datawarehousing.com)...many people say they see no use for a unique
key on a fact table...
However, over the last few years at some of the sites I have worked on
we have found some great uses for putting a single integer key on the
front of a fact table. We do this and use it often. (But we don't
publish what we use it for.)
Best Regards
Peter
|||"" wrote:
> Hi Rick,
> this was discussed at some length on dwlist
> (datawarehousing.com)...many people say they see no use for a
> unique
> key on a fact table...
> However, over the last few years at some of the sites I have
> worked on
> we have found some great uses for putting a single integer key
> on the
> front of a fact table. We do this and use it often. (But we
> don't
> publish what we use it for.)
> Best Regards
> Peter
Can you please let me know what you use it for?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=898374
sql

Indexes Enabled or Disabled?

Hey folks,
SQL Server 2000 SP3/4
How can I determine if an index is enabled or disabled? The information must
be kept somewhere, but I can't seem to find it. Not in sysindexes,
information_schema, sp_helpindex.
Easy to get in SQL 2005 mind you.
--
Thanks,
Scott H.Well, it's been a while, but I don't think you can disable an index on SQL
Server 2000, can you?
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:D12CF217-6C70-4483-B07A-3E00F0A475AA@.microsoft.com...
> Hey folks,
> SQL Server 2000 SP3/4
> How can I determine if an index is enabled or disabled? The information
> must
> be kept somewhere, but I can't seem to find it. Not in sysindexes,
> information_schema, sp_helpindex.
> Easy to get in SQL 2005 mind you.
>
> --
> Thanks,
> Scott H.|||> Well, it's been a while, but I don't think you can disable an index on SQL
> Server 2000, can you?
Nope. That ability was introduced in SQL Server 2005.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uFrZOVhqIHA.552@.TK2MSFTNGP06.phx.gbl...
> Well, it's been a while, but I don't think you can disable an index on SQL
> Server 2000, can you?
>
>
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:D12CF217-6C70-4483-B07A-3E00F0A475AA@.microsoft.com...
>> Hey folks,
>> SQL Server 2000 SP3/4
>> How can I determine if an index is enabled or disabled? The information
>> must
>> be kept somewhere, but I can't seem to find it. Not in sysindexes,
>> information_schema, sp_helpindex.
>> Easy to get in SQL 2005 mind you.
>>
>> --
>> Thanks,
>> Scott H.
>|||Ok, that's what I thought. So that makes it easy.
SELECT *, Enabled = 1
FROM sysindexes;
:-)
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:OOgQMfhqIHA.2292@.TK2MSFTNGP03.phx.gbl...
>> Well, it's been a while, but I don't think you can disable an index on
>> SQL Server 2000, can you?
> Nope. That ability was introduced in SQL Server 2005.
>|||On Tue, 29 Apr 2008 12:21:43 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>Well, it's been a while, but I don't think you can disable an index on SQL
>Server 2000, can you?
Doesn't look that way from the docs. ALTER INDEX had not been
introduced yet.
Roy Harvey
Beacon Falls, CT|||Good thinking, Aaron!
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eZZRWhhqIHA.4476@.TK2MSFTNGP04.phx.gbl...
> Ok, that's what I thought. So that makes it easy.
> SELECT *, Enabled = 1
> FROM sysindexes;
> :-)
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:OOgQMfhqIHA.2292@.TK2MSFTNGP03.phx.gbl...
>> Well, it's been a while, but I don't think you can disable an index on SQL Server 2000, can you?
>> Nope. That ability was introduced in SQL Server 2005.
>>
>