Hello All,
Could someone help provide a query that I can run to
determine the primary key and unique columns for any
given table ?
Thanks in Advance,
AkintoyeHere's an example:
SELECT TC.TABLE_SCHEMA AS TableOwner,
TC.TABLE_NAME,
TC.CONSTRAINT_TYPE,
TC.CONSTRAINT_NAME,
KCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
ON TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE IN
(
'PRIMARY KEY',
'UNIQUE'
)
ORDER BY TC.TABLE_SCHEMA, TC.TABLE_NAME, TC.CONSTRAINT_NAME, KCU.COLUMN_NAME
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Akintoye Olorode" <akintoye_olorode@.iwaysoftware.com> wrote in message
news:4366775c$1@.ibixwebf.ibi.com...
> Hello All,
> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
> Thanks in Advance,
> Akintoye
>|||"Akintoye Olorode" <akintoye_olorode@.iwaysoftware.com> wrote in
news:4366775c$1@.ibixwebf.ibi.com:
> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
You're looking for simething like this? (SQL Server 2005):
select ccu.column_name from
information_schema.constraint_column_usage ccu inner join
information_schema.table_constraints tc
on (tc.constraint_name = ccu.constraint_name)
where
(tc.constraint_type in ('unique','primary key')) and
(tc.table_schema + '.' + tc.table_name = <schema>.<table_name> )
I guess that would be approximately like this in SQL Server 2000 (Not
tested):
select ccu.column_name from
information_schema.constraint_column_usage ccu inner join
information_schema.table_constraints tc
on (tc.constraint_name = ccu.constraint_name)
where
(tc.constraint_type in ('unique','primary key')) and
(tc.table_name = <table_name> )
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in
news:Xns9700F370E9A4Dolekristianbangaas@.
207.46.248.16:
(...)
Ooops, stayed a littlebit too long in my outbox :( Sorry folks.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
Showing posts with label unique. Show all posts
Showing posts with label unique. Show all posts
Friday, March 30, 2012
Information Schema Query Question
Hello All,
Could someone help provide a query that I can run to
determine the primary key and unique columns for any
given table ?
Thanks in Advance,
Akintoyeselect t.table_name, t.constraint_type, t.constraint_name,
c.column_name
from information_schema.TABLE_CONSTRAINTS t
join information_schema.CONSTRAINT_COLUMN_USAGE c ON t.constraint_name
= c.constraint_name
where constraint_type IN ('UNIQUE', 'PRIMARY KEY')
and t.table_name = <Table Name
Akintoye Olorode wrote:
> Hello All,
> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
> Thanks in Advance,
> Akintoyesql
Friday, March 23, 2012
Indx newbie: please help
Consider the following
--------------------
ASSIGNMENT
- Index : Integer, Unique, NotNull, Primary Key
- Start : DateTime, , NotNull,
- End : DateTime, , ,
- Other : ...
TASK
- Assignment : Integer, Unique, NotNull, Primary Foreign Key
- SubIndex : Integer, , NotNull, Primary Key
- Type : Char(3), , NotNull, Foreign Key
- Start : DateTime, , NotNull,
- End : DateTime, , ,
- Other : ...
TASK_TYPE
- Code : Char(3), Unique, NotNull, Primary Key
- Description : VarChar(20), , NotNull,
--------------------
As u can c the idea is simple...
...an assignment has an INDEX as PK, a start date, an end date and other fields;
...an assignment can have one or more tasks; the relationship is 1:N and is identifying (see next point)
...a task has a SUB INDEX inside the assignment; that is the PK is the assigment it belongs to (also a FK) and an index for that assignment
...a task has also a TYPE, which is a FK to the TASK_TYPE table
Consider also that...
...ASSIGNMENT contains > 1 millions rows
...TASK contains < 10 rows for each assignment (so an average of 5 millions rows)
...TASK_TYPE contains < 10 rows
As far as I know SQL-server creates a CLUSTERED INDEX for any PK, that is
ASSIGNMENT (Index)
TASK(Assigment, SubIndex)
TASK_TYPE(Code)
Do I have to add any other NON CLUSTERED INDEX? I would say I should add the following:
TASK(Assignment)
TASK(Type)
But of course TASK(Assignment) is already part of the CLUSTERED INDEX ASSIGNMENT(Assignment, SubIndex), so I shouldn't add it, right?
What about TASK(Type)?
Or maybe there's a completely different solution?
My main problem is due to the fact that TASK has a composite PK wher one field is also a FK.
Any advice is welcome.
thanks a lot :-)what, exactly, is your main problem? you didn't say what it was :)
i don't think you need any additional indexes, since TASK_TYPE should always be handled in memory (but then, i am not a DBA, eh ;))
by the way, it's not a good idea to name a column "index" as this is a reserved word in a lot of languages|||I'd simply like to know how where to put indexes :)|||okay, put an index on task.type
;)|||I would put the index in the database. Someone might need it, and it would be very embarrassing, if you left it at home.
Joking aside, is there a particular query that is giving problems? Remember that excessive indexing can hurt data modification processes.
--------------------
ASSIGNMENT
- Index : Integer, Unique, NotNull, Primary Key
- Start : DateTime, , NotNull,
- End : DateTime, , ,
- Other : ...
TASK
- Assignment : Integer, Unique, NotNull, Primary Foreign Key
- SubIndex : Integer, , NotNull, Primary Key
- Type : Char(3), , NotNull, Foreign Key
- Start : DateTime, , NotNull,
- End : DateTime, , ,
- Other : ...
TASK_TYPE
- Code : Char(3), Unique, NotNull, Primary Key
- Description : VarChar(20), , NotNull,
--------------------
As u can c the idea is simple...
...an assignment has an INDEX as PK, a start date, an end date and other fields;
...an assignment can have one or more tasks; the relationship is 1:N and is identifying (see next point)
...a task has a SUB INDEX inside the assignment; that is the PK is the assigment it belongs to (also a FK) and an index for that assignment
...a task has also a TYPE, which is a FK to the TASK_TYPE table
Consider also that...
...ASSIGNMENT contains > 1 millions rows
...TASK contains < 10 rows for each assignment (so an average of 5 millions rows)
...TASK_TYPE contains < 10 rows
As far as I know SQL-server creates a CLUSTERED INDEX for any PK, that is
ASSIGNMENT (Index)
TASK(Assigment, SubIndex)
TASK_TYPE(Code)
Do I have to add any other NON CLUSTERED INDEX? I would say I should add the following:
TASK(Assignment)
TASK(Type)
But of course TASK(Assignment) is already part of the CLUSTERED INDEX ASSIGNMENT(Assignment, SubIndex), so I shouldn't add it, right?
What about TASK(Type)?
Or maybe there's a completely different solution?
My main problem is due to the fact that TASK has a composite PK wher one field is also a FK.
Any advice is welcome.
thanks a lot :-)what, exactly, is your main problem? you didn't say what it was :)
i don't think you need any additional indexes, since TASK_TYPE should always be handled in memory (but then, i am not a DBA, eh ;))
by the way, it's not a good idea to name a column "index" as this is a reserved word in a lot of languages|||I'd simply like to know how where to put indexes :)|||okay, put an index on task.type
;)|||I would put the index in the database. Someone might need it, and it would be very embarrassing, if you left it at home.
Joking aside, is there a particular query that is giving problems? Remember that excessive indexing can hurt data modification processes.
Wednesday, March 21, 2012
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
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
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
Monday, March 19, 2012
Indexing non-unique data
I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.
The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).
Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.|||I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.
The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).
Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?
I think custured index should do the trick,its my opinion...I feel clustered index are best for low selectiviy columns,i.e. the column which have many duplicates values. But see what the gurus suggest...|||You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.
But can't we make the foreign key column clustered index? I mean making the unique key not a clustered index...only a unique key column|||Hi Istaks
Welcome to the forum
Not a guru but some musings:
Well - a clustered index determines the physical order storage of data. So - it is useful if placed on an incrementing field as far as insertion of data is concerned as there will be no page splits based on insertion. It is also useful if you are likely to use >, < or between comparisons in a where condition on the clustered field.
The former is not the case. Inequality operators are rarely used on identities so Id go with no too :)|||So on table(B) create a clustered index on the unique key and a non-clustered index on the foreign key?
All selections from this table will be based on the foreign key.|||If ALL selects on this table will reference the foreign key and you will not be searching for individual records, then you would get a performance boost from using a clustered index on your foreign key.
The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).
Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.|||I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.
The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).
Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?
I think custured index should do the trick,its my opinion...I feel clustered index are best for low selectiviy columns,i.e. the column which have many duplicates values. But see what the gurus suggest...|||You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.
But can't we make the foreign key column clustered index? I mean making the unique key not a clustered index...only a unique key column|||Hi Istaks
Welcome to the forum
Not a guru but some musings:
Well - a clustered index determines the physical order storage of data. So - it is useful if placed on an incrementing field as far as insertion of data is concerned as there will be no page splits based on insertion. It is also useful if you are likely to use >, < or between comparisons in a where condition on the clustered field.
The former is not the case. Inequality operators are rarely used on identities so Id go with no too :)|||So on table(B) create a clustered index on the unique key and a non-clustered index on the foreign key?
All selections from this table will be based on the foreign key.|||If ALL selects on this table will reference the foreign key and you will not be searching for individual records, then you would get a performance boost from using a clustered index on your foreign key.
Monday, March 12, 2012
Indexing a view that contains text or ntext
I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?
Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced in
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:
> I've created a view and started to create my first index (unique, clustered)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the largest
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I can
> do? Any suggestions?
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?
Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced in
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:
> I've created a view and started to create my first index (unique, clustered)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the largest
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I can
> do? Any suggestions?
Indexing a view that contains text or ntext
I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced i
n
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:
> I've created a view and started to create my first index (unique, clustere
d)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the larges
t
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I ca
n
> do? Any suggestions?
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced i
n
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:
> I've created a view and started to create my first index (unique, clustere
d)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the larges
t
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I ca
n
> do? Any suggestions?
Indexing a view that contains text or ntext
I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced in
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:
> I've created a view and started to create my first index (unique, clustered)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the largest
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I can
> do? Any suggestions?
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced in
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:
> I've created a view and started to create my first index (unique, clustered)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the largest
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I can
> do? Any suggestions?
Wednesday, March 7, 2012
Indexes design
If I have a table with Col1 + Col2 as PK. But most of the queries use Col1, Col2, Col3 and Col4 in the queries, should I create another unique index on this combination? If not, my queries do an index scan. Just wondering is there a general rule for this kind of situation as I have a lot of such cases in my database.depends. most of the time multi column indexes are not a good idea. if i remember correctly for multi column indexes to work at all, the query has to search the columns in the table in the order that you defined the columns in the index.
index scans are ok, it is table scans you have to worry about.
are you experiencing a performance issue?|||Thrasymachus- I agree, we have to include all the columns in the index in the where clause to avoid a index scan. But the table is huge and yes, I am experiencing performance issues. I know I could make them run better with a seek.|||what's huge? tens of millions of records?
what's the ddl like? lots of fields? big fields?
And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition.
What's the query look like?|||3 million records. Table has 9 columns with 2 varchar(512) and a UniqueIdentifier.
Not sure what you mean by "And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition."|||3 million is not that big.
If your index is created like so
CREATE INDEX MyIndex On Table1 (col1,col2,col3)
Then your query should go like
SELECT *
FROM Table1
Where col1 = @.param1
AND col2 = @.param2
AND col3 = @.param3
same order, see.
ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values.|||I am not sure if the order of the columns in the index and the query really matters. QO is smart enough to re-phrase your query to use the index. But if your query's where clause is on col1 and col3 while your index is on (col1, col2 and col3) then there will be a scan versus a seek.|||I develop in sql 2k now, but I just looked at my old design book for sql 7 I have laying around to make sure and this part I did confirm. things might have changed. The other thing I remeber from an ex-colleague of mine who was the sql guru I ever knew.
"ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values."|||I am not sure if the order of the columns in the index and the query really matters. QO is smart enough to re-phrase your query to use the index. But if your query's where clause is on col1 and col3 while your index is on (col1, col2 and col3) then there will be a scan versus a seek.
The notion that the WHERE clause search arguments have to be in the "same order " as the columns defined in the key of the index is rubbish.
Also, the term "index scan" to me means examining EVERY row of an index because to find a match on a search criteria. In your example, "where clause is on col1 and col3 while your index is on (col1, col2, col3)", given that you don't have any datatype mismatch issues, and given that the index is chosen as the access path, and given that the search argument operator is "=", and given that the index is large enough, then you will KEY POSITION on col1, and scan the rest of the index for col3 matches. So, every row of the index containing col1 value is "scanned", but no more than that.|||KSherlock- I agree with you. I have the same understanding as yours. We got side tracked into this discussion about column order. My original question is a little different. Still looking for some comments/suggestions on it.|||As for this part:
"ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values."
I will refer you to Database Design on SQL Server 7 by certification insider press page 173.
As for the other thing. This kind of backs me up (taken from http://www.sql-server-performance.com/composite_indexes.asp). I have forgot a little of it:
"A composite index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Springfield'" will use the index, but the query "WHERE STATE = 'MO'" will not use the index. [6.5, 7.0, 2000] Updated 11-15-2004"
So heres mud in your eye Sherlock.|||Check me if I have this straight:
Col1 and Col2 make up the PK
And most queries use all 4 Col1 Col2 Col3 and Col4? Or is that "or"? After all, if you specify col1 and col2, specifying col3 and col4 would be a bit redundant, eh?
If you specify values for Col1 and Col2, you should be seeking on the PK. Are you using functions on the columns? Like
where upper(col1) = upper(some search argument)
The above will cause a table scan every time. Since the PK is usually clustered, you may be seeing this just represented as a clustered index scan. It is really the same thing.|||And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition
...
"A composite index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Springfield'" will use the index, but the query "WHERE STATE = 'MO'" will not use the index. [6.5, 7.0, 2000] Updated 11-15-2004"
So heres mud in your eye Sherlock.
Uhhh, yea. Whatever you say...|||...most of the time multi column indexes are not a good idea...Huh? Please present your theories as such, not as a "proven fact", man. You want the guy to loose his job just because you couldn't resist making a statement?
...ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values...Good point, but ... how should I say it? It's orthodox concept for a table that is NEVER a part of an action query. Introduction of indexes based on "best practices for SELECT queries" ONLY, - is a recepie for your action queries as a potential bottleneck.
index scans are ok, it is table scans you have to worry about.
are you experiencing a performance issue?|||Thrasymachus- I agree, we have to include all the columns in the index in the where clause to avoid a index scan. But the table is huge and yes, I am experiencing performance issues. I know I could make them run better with a seek.|||what's huge? tens of millions of records?
what's the ddl like? lots of fields? big fields?
And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition.
What's the query look like?|||3 million records. Table has 9 columns with 2 varchar(512) and a UniqueIdentifier.
Not sure what you mean by "And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition."|||3 million is not that big.
If your index is created like so
CREATE INDEX MyIndex On Table1 (col1,col2,col3)
Then your query should go like
SELECT *
FROM Table1
Where col1 = @.param1
AND col2 = @.param2
AND col3 = @.param3
same order, see.
ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values.|||I am not sure if the order of the columns in the index and the query really matters. QO is smart enough to re-phrase your query to use the index. But if your query's where clause is on col1 and col3 while your index is on (col1, col2 and col3) then there will be a scan versus a seek.|||I develop in sql 2k now, but I just looked at my old design book for sql 7 I have laying around to make sure and this part I did confirm. things might have changed. The other thing I remeber from an ex-colleague of mine who was the sql guru I ever knew.
"ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values."|||I am not sure if the order of the columns in the index and the query really matters. QO is smart enough to re-phrase your query to use the index. But if your query's where clause is on col1 and col3 while your index is on (col1, col2 and col3) then there will be a scan versus a seek.
The notion that the WHERE clause search arguments have to be in the "same order " as the columns defined in the key of the index is rubbish.
Also, the term "index scan" to me means examining EVERY row of an index because to find a match on a search criteria. In your example, "where clause is on col1 and col3 while your index is on (col1, col2, col3)", given that you don't have any datatype mismatch issues, and given that the index is chosen as the access path, and given that the search argument operator is "=", and given that the index is large enough, then you will KEY POSITION on col1, and scan the rest of the index for col3 matches. So, every row of the index containing col1 value is "scanned", but no more than that.|||KSherlock- I agree with you. I have the same understanding as yours. We got side tracked into this discussion about column order. My original question is a little different. Still looking for some comments/suggestions on it.|||As for this part:
"ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values."
I will refer you to Database Design on SQL Server 7 by certification insider press page 173.
As for the other thing. This kind of backs me up (taken from http://www.sql-server-performance.com/composite_indexes.asp). I have forgot a little of it:
"A composite index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Springfield'" will use the index, but the query "WHERE STATE = 'MO'" will not use the index. [6.5, 7.0, 2000] Updated 11-15-2004"
So heres mud in your eye Sherlock.|||Check me if I have this straight:
Col1 and Col2 make up the PK
And most queries use all 4 Col1 Col2 Col3 and Col4? Or is that "or"? After all, if you specify col1 and col2, specifying col3 and col4 would be a bit redundant, eh?
If you specify values for Col1 and Col2, you should be seeking on the PK. Are you using functions on the columns? Like
where upper(col1) = upper(some search argument)
The above will cause a table scan every time. Since the PK is usually clustered, you may be seeing this just represented as a clustered index scan. It is really the same thing.|||And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition
...
"A composite index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Springfield'" will use the index, but the query "WHERE STATE = 'MO'" will not use the index. [6.5, 7.0, 2000] Updated 11-15-2004"
So heres mud in your eye Sherlock.
Uhhh, yea. Whatever you say...|||...most of the time multi column indexes are not a good idea...Huh? Please present your theories as such, not as a "proven fact", man. You want the guy to loose his job just because you couldn't resist making a statement?
...ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values...Good point, but ... how should I say it? It's orthodox concept for a table that is NEVER a part of an action query. Introduction of indexes based on "best practices for SELECT queries" ONLY, - is a recepie for your action queries as a potential bottleneck.
Subscribe to:
Posts (Atom)