can unique index be created if the include col makes the index unique?

create index idx_tab1_col1col2

on dbo.tab1([col1])

include([col2])

on tab1_index

My question is this. I require this index to be created. col1 doesn't have a lot of unique values but col2 is our pk and adding col2 to the index makes it unique. Can I make this a unique index then? Also, can someone explaing me a bit as to why it's possible or y not? Thank you very much for your help.

I dare say that you could have created the index as unique in less time than it took to navigate to here and write the question. But you didn't so let's try it together.

Create Table dbo.tab1 (Col1 varchar(5) null, Col2 int not null primary key)

Insert Into dbo.tab1
Select 'aaaaa', 1
Union
Select 'bbbbb', 2
Union
Select 'ccccc', 3
Union
Select 'ccccc', 4
Union
Select 'ddddd', 5

create unique index idx_tab1_col1col2
on dbo.tab1([col1])
include([col2])

Msg 1505, Level 16, State 1, Line 2
CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.tab1' and index name 'idx_tab1_col1col2'. The duplicate key value is (ccccc).
The statement has been terminated.

So, no, you can't. Uniqueness must exist within the indexed fields without regard to any included columns.

|||Include column can not make index unique. Because include column is not part of index key.|||Hey Robert. Yes, you are correct but at that time, I was in middle of implememnting a checkdb process for my company, which was never in place. Well, thank you very much for your time and effort and the answer.

Can There be a single Index

Can we make a Index on 2 or more tables

Table a:
Col1 int
col2 varchar

Table b:
Col1 int
col2 varchar

can you have a single index for two tables a and b on the column Col1.
Is this possible in SQL-Server.

As far as i know you can make an index only on one Table.

can the index be shared by two tables?
index y which is created is shared by
table a , table b and table c

I like the idea of a union an putting it in a veiw and having a index
but i dont want to make 100 views for a special index.

first of all i want to know wether the index is only on one table can be
on multiple tables.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!vinay bhushan <bhushanvinay@.mail.com> wrote in message news:<409b0dac$0$204$75868355@.news.frii.net>...
> Can we make a Index on 2 or more tables
> Table a:
> Col1 int
> col2 varchar
> Table b:
> Col1 int
> col2 varchar
> can you have a single index for two tables a and b on the column Col1.
> Is this possible in SQL-Server.
> As far as i know you can make an index only on one Table.
> can the index be shared by two tables?
> index y which is created is shared by
> table a , table b and table c
> I like the idea of a union an putting it in a veiw and having a index
> but i dont want to make 100 views for a special index.
> first of all i want to know wether the index is only on one table can be
> on multiple tables.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

No - an index exists on one table (or indexed view) only. If you have
a specific indexing problem, then you could post more details and
someone may be able to suggest a solution.

Simon|||vinay bhushan (bhushanvinay@.mail.com) writes:
> Can we make a Index on 2 or more tables
> Table a:
> Col1 int
> col2 varchar
> Table b:
> Col1 int
> col2 varchar
> can you have a single index for two tables a and b on the column Col1.
> Is this possible in SQL-Server.

By means of an indexed view, yes.

What an indexed means in practice is that you materialize the view,
so it still really one table under the covers. But you don't have
the chores to keep it updated. SQL Server takes care of that for you.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Is your question because of a particular problem? Are you getting a
performance issue joining the two table together?

Alicia

http://www.sqlporn.co.uk :o)

can tables themselves be fragmented? Index ID 0?

When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
CoryIndex ID of 0 means that the table is a heap. In other words the table does
not have a clustered index. The only way to remove fragmentation is to
create a clustered index on that table or to export all the data, truncate
it and import it all back in again. Index ID of 1 is always the clustered
index. You will never have both only one or the other.
Andrew J. Kelly SQL MVP
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
> of my indexes with ID's 1 and on up appear to be fine, it's just these ID
> 0's that look bad.
>
> thanks,
> Cory
>
>|||Hey Andy, I'm wondering how exporting & re-importing the data helps? Doesn't
simply creating a clustered index & then dropping the clustered index
provide an effective defrag of the heap?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table
> does not have a clustered index. The only way to remove fragmentation is
> to create a clustered index on that table or to export all the data,
> truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>|||In addition, it is worth explaining what you mean by fragmentation.
When the physical order doesn't match the logical order is one type of fragm
entation this is
impossible for the datapages of a heap table (index 0) as there is no order.
This is also known as
external fragmentation.
Another is then you have lots of free spaces on the pages and extents that t
he heap/index is using.
This is also known as internal fragmentation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table doe
s not have a clustered
> index. The only way to remove fragmentation is to create a clustered index
on that table or to
> export all the data, truncate it and import it all back in again. Index I
D of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>

can tables themselves be fragmented? Index ID 0?

When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
Cory> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0.
Judging by what value? See my other post. There's no order for rows in a hea
p, so scan density etc.
are meaningless for a heap.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:%23NBNJIr9GHA.3344@.TK2MSFTNGP03.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>|||Hi Cory,
These tables to which you refer are heap tables, those that do not have a
clustered index defined on them. The data pages for heaps are not stored in
any particular order, which would explain why the fragmentation values are
high. Consider creating an appropriate clustered index on these tables if
you want to reduce the observed fragmentation.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Cory Harrison" wrote:

> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>

can tables themselves be fragmented? Index ID 0?

When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
Cory
Index ID of 0 means that the table is a heap. In other words the table does
not have a clustered index. The only way to remove fragmentation is to
create a clustered index on that table or to export all the data, truncate
it and import it all back in again. Index ID of 1 is always the clustered
index. You will never have both only one or the other.
Andrew J. Kelly SQL MVP
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
> of my indexes with ID's 1 and on up appear to be fine, it's just these ID
> 0's that look bad.
>
> thanks,
> Cory
>
>
|||Hey Andy, I'm wondering how exporting & re-importing the data helps? Doesn't
simply creating a clustered index & then dropping the clustered index
provide an effective defrag of the heap?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table
> does not have a clustered index. The only way to remove fragmentation is
> to create a clustered index on that table or to export all the data,
> truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>
|||In addition, it is worth explaining what you mean by fragmentation.
When the physical order doesn't match the logical order is one type of fragmentation this is
impossible for the datapages of a heap table (index 0) as there is no order. This is also known as
external fragmentation.
Another is then you have lots of free spaces on the pages and extents that the heap/index is using.
This is also known as internal fragmentation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table does not have a clustered
> index. The only way to remove fragmentation is to create a clustered index on that table or to
> export all the data, truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>

can tables themselves be fragmented? Index ID 0?

When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
Cory
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0.
Judging by what value? See my other post. There's no order for rows in a heap, so scan density etc.
are meaningless for a heap.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:%23NBNJIr9GHA.3344@.TK2MSFTNGP03.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>
|||Hi Cory,
These tables to which you refer are heap tables, those that do not have a
clustered index defined on them. The data pages for heaps are not stored in
any particular order, which would explain why the fragmentation values are
high. Consider creating an appropriate clustered index on these tables if
you want to reduce the observed fragmentation.
Kind Regards
Andrew Pike
SQL Server DBA
UBS IB
"Cory Harrison" wrote:

> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>

can tables themselves be fragmented? Index ID 0?

When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
CoryIndex ID of 0 means that the table is a heap. In other words the table does
not have a clustered index. The only way to remove fragmentation is to
create a clustered index on that table or to export all the data, truncate
it and import it all back in again. Index ID of 1 is always the clustered
index. You will never have both only one or the other.
--
Andrew J. Kelly SQL MVP
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
> of my indexes with ID's 1 and on up appear to be fine, it's just these ID
> 0's that look bad.
>
> thanks,
> Cory
>
>|||Hey Andy, I'm wondering how exporting & re-importing the data helps? Doesn't
simply creating a clustered index & then dropping the clustered index
provide an effective defrag of the heap?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table
> does not have a clustered index. The only way to remove fragmentation is
> to create a clustered index on that table or to export all the data,
> truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
>> fragmented for index ID 0. I'm assuming this is the table itself? If
>> so, what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG.
>> All of my indexes with ID's 1 and on up appear to be fine, it's just
>> these ID 0's that look bad.
>>
>> thanks,
>> Cory
>>
>|||In addition, it is worth explaining what you mean by fragmentation.
When the physical order doesn't match the logical order is one type of fragmentation this is
impossible for the datapages of a heap table (index 0) as there is no order. This is also known as
external fragmentation.
Another is then you have lots of free spaces on the pages and extents that the heap/index is using.
This is also known as internal fragmentation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table does not have a clustered
> index. The only way to remove fragmentation is to create a clustered index on that table or to
> export all the data, truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly fragmented for index ID
>> 0. I'm assuming this is the table itself? If so, what do I do about it? I can't enter this ID
>> into DBCC INDEXDEFRAG. All of my indexes with ID's 1 and on up appear to be fine, it's just
>> these ID 0's that look bad.
>>
>> thanks,
>> Cory
>>
>|||The idea would be that you would export with a select that had an order by.
Then once you truncate the table and import it back in you will not have any
forwarding pointers, gaps in the pages etc. The data may not be in physical
order like you would have at the leaf node of a clustered or non-clustered
index but it should be decent when thru. How long it stays that way is a
different story:). That process does not guarantee lack of fragmentation
from a data ordering standpoint but it will clean up the gaps, forwarding
pointers and if there is enough contiguous room in the data files it will be
mostly contiguous as well.
--
Andrew J. Kelly SQL MVP
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:uRIghjN9GHA.4632@.TK2MSFTNGP02.phx.gbl...
> Hey Andy, I'm wondering how exporting & re-importing the data helps?
> Doesn't simply creating a clustered index & then dropping the clustered
> index provide an effective defrag of the heap?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Index ID of 0 means that the table is a heap. In other words the table
>> does not have a clustered index. The only way to remove fragmentation is
>> to create a clustered index on that table or to export all the data,
>> truncate it and import it all back in again. Index ID of 1 is always the
>> clustered index. You will never have both only one or the other.
>> --
>> Andrew J. Kelly SQL MVP
>> "Cory Harrison" <charrison@.csiweb.com> wrote in message
>> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>> When I run DBCC SHOWCONTIG I see several tables who appear to be
>> horribly fragmented for index ID 0. I'm assuming this is the table
>> itself? If so, what do I do about it? I can't enter this ID into DBCC
>> INDEXDEFRAG. All of my indexes with ID's 1 and on up appear to be fine,
>> it's just these ID 0's that look bad.
>>
>> thanks,
>> Cory
>>
>>
>

can tables themselves be fragmented? Index ID 0?

When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
Cory> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0.
Judging by what value? See my other post. There's no order for rows in a heap, so scan density etc.
are meaningless for a heap.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:%23NBNJIr9GHA.3344@.TK2MSFTNGP03.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>|||Hi Cory,
These tables to which you refer are heap tables, those that do not have a
clustered index defined on them. The data pages for heaps are not stored in
any particular order, which would explain why the fragmentation values are
high. Consider creating an appropriate clustered index on these tables if
you want to reduce the observed fragmentation.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Cory Harrison" wrote:
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>