clustered indexes

Hello,
Does anyone know if a clustered index location dictates
where the actual table data is stored? Can the clustered
index be located on a data file other than the one the
table resides on?
Thanks,
DianeYes it does dictate where the data is stored.
Moving the clustered index to a different FileGroup will move the data
--
HTH
Ryan Waight, MCDBA, MCSE
"Diane" <dfleming@.webmd.com> wrote in message
news:07ec01c3a794$ef088fe0$a501280a@.phx.gbl...
> Hello,
> Does anyone know if a clustered index location dictates
> where the actual table data is stored? Can the clustered
> index be located on a data file other than the one the
> table resides on?
> Thanks,
> Diane|||> Can the clustered
> index be located on a data file other than the one the
> table resides on?
No, essentially, the clustered index IS the data. If you move the clustered
index to a different filegroup, the data will also be on that filegroup.|||Hi,
This might explain a bit further.
Creating a clustered index dictates how the data is to be
stored, and affects behind the scenes pages.
Its sort of like having a manual filing system sorted
alphabetically in a filing cabinet. However it will always
be in order.
It works by ordering the pages in you datafile to come in
a spcific order. So the guys were right, just a bit brief.
If you want further info my email (in reverse) is
ku.oc.elcannip@.nalon.retep
Peter
>--Original Message--
>Hello,
>Does anyone know if a clustered index location dictates
>where the actual table data is stored? Can the clustered
>index be located on a data file other than the one the
>table resides on?
>Thanks,
>Diane
>.
>|||Ever noticed that you can never critique your own work.
Apoligies if it sounded condecending, it wasn't suppost to.
Peter
>--Original Message--
>Hi,
>This might explain a bit further.
>Creating a clustered index dictates how the data is to be
>stored, and affects behind the scenes pages.
>Its sort of like having a manual filing system sorted
>alphabetically in a filing cabinet. However it will
always
>be in order.
>It works by ordering the pages in you datafile to come in
>a spcific order. So the guys were right, just a bit brief.
>If you want further info my email (in reverse) is
>ku.oc.elcannip@.nalon.retep
>Peter
>
>>--Original Message--
>>Hello,
>>Does anyone know if a clustered index location dictates
>>where the actual table data is stored? Can the
clustered
>>index be located on a data file other than the one the
>>table resides on?
>>Thanks,
>>Diane
>>.
>.
>

clustered index and nonclustered index

I have a table with 2 indexes:
1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
2 - nonclustered, unique, unique key located on PRIMARY
the second index it's over three columns (all int).
If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
hope than a "select * from table" returns the following:
1, 3, 7, 7
2, 3, 5, 6
but instead i get the following:
2, 3, 5, 6
1, 3, 7, 7
why this happen ? should'nt the PK clustered index order the results by the
first column? how can i make that results can be like the first case?
thanks in advance
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
>
The table's physical design does not guarantee any logical ordering to
results. The only way to get ordered results is to use an ORDER BY clause
in the query.
David
|||With SQL, there is NO guarantee about the order of data retrieval UNLESS you
explicitly request the order by using the ORDER BY statement.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
> thanks in advance
>
|||I have found that doing a reindex seems to order the rows correctly.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
> thanks in advance
>

clustered index and nonclustered index

I have a table with 2 indexes:
1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
2 - nonclustered, unique, unique key located on PRIMARY
the second index it's over three columns (all int).
If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
hope than a "select * from table" returns the following:
1, 3, 7, 7
2, 3, 5, 6
but instead i get the following:
2, 3, 5, 6
1, 3, 7, 7
why this happen ? should'nt the PK clustered index order the results by the
first column? how can i make that results can be like the first case?
thanks in advance"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
>
The table's physical design does not guarantee any logical ordering to
results. The only way to get ordered results is to use an ORDER BY clause
in the query.
David|||With SQL, there is NO guarantee about the order of data retrieval UNLESS you
explicitly request the order by using the ORDER BY statement.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
> thanks in advance
>|||I have found that doing a reindex seems to order the rows correctly.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
> thanks in advance
>|||byteman,
As mentioned by others, the only way to guarantee any sorting is to use
the ORDER BY clause.
If the four columns you describe are the entire table, then the
optimizer could use the clustered index to satisfy the query, or use
index 2. Both cover the query. Index 1 covers the query because the leaf
level includes all table column. Index 2 covers the query because the
clustered index key is always included in a nonclustered index.
HTH,
Gert-Jan
byteman wrote:
> I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by th
e
> first column? how can i make that results can be like the first case?
> thanks in advance

clustered index and nonclustered index

I have a table with 2 indexes:
1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
2 - nonclustered, unique, unique key located on PRIMARY
the second index it's over three columns (all int).
If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
hope than a "select * from table" returns the following:
1, 3, 7, 7
2, 3, 5, 6
but instead i get the following:
2, 3, 5, 6
1, 3, 7, 7
why this happen ? should'nt the PK clustered index order the results by the
first column? how can i make that results can be like the first case?
thanks in advance"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
>
The table's physical design does not guarantee any logical ordering to
results. The only way to get ordered results is to use an ORDER BY clause
in the query.
David|||With SQL, there is NO guarantee about the order of data retrieval UNLESS you
explicitly request the order by using the ORDER BY statement.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
> thanks in advance
>|||I have found that doing a reindex seems to order the rows correctly.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
> thanks in advance
>|||byteman,
As mentioned by others, the only way to guarantee any sorting is to use
the ORDER BY clause.
If the four columns you describe are the entire table, then the
optimizer could use the clustered index to satisfy the query, or use
index 2. Both cover the query. Index 1 covers the query because the leaf
level includes all table column. Index 2 covers the query because the
clustered index key is always included in a nonclustered index.
HTH,
Gert-Jan
byteman wrote:
> I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by the
> first column? how can i make that results can be like the first case?
> thanks in advance