index and key: February 2012

Friday, February 24, 2012

index database

In vfp database, i can use the command ' reindex' to index the database
again.
for SQL server, can i do that ?
thanks a lot
Can you explain what such a "reindex" is supposed to achieve? It is possible that you are looking
for the DBCC DBREINDEX or DBCC INDEXDEFRAG (2000) or for 2005 ALTER INDEX with either the REBUILD or
the REORGANIZE options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Agnes" <agnes@.dynamictech.com.hk> wrote in message news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database again.
> for SQL server, can i do that ?
> thanks a lot
>
|||Hi Agnes
You don't give the version of SQL Server you are using check out DBCC
DBREINDEX and INDEXDEFRAG in Books Online if you are using SQL 2000 also DBCC
SHOWCONTIG which has an example script to only reindex indexes that are
fragmented by a certain amount. If using SQL 2005 check out the ALTER INDEX
statement and the sys.db_index_physical_stats view.
John
"Agnes" wrote:

> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that ?
> thanks a lot
>
>
|||sp_MSforeachtable 'dbcc dbreindex(''?'')'
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
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that ?
> thanks a lot
>

index database

In vfp database, i can use the command ' reindex' to index the database
again.
for SQL server, can i do that '
thanks a lotCan you explain what such a "reindex" is supposed to achieve? It is possible
that you are looking
for the DBCC DBREINDEX or DBCC INDEXDEFRAG (2000) or for 2005 ALTER INDEX wi
th either the REBUILD or
the REORGANIZE options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Agnes" <agnes@.dynamictech.com.hk> wrote in message news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.g
bl...
> In vfp database, i can use the command ' reindex' to index the database ag
ain.
> for SQL server, can i do that '
> thanks a lot
>|||Hi Agnes
You don't give the version of SQL Server you are using check out DBCC
DBREINDEX and INDEXDEFRAG in Books Online if you are using SQL 2000 also DBC
C
SHOWCONTIG which has an example script to only reindex indexes that are
fragmented by a certain amount. If using SQL 2005 check out the ALTER INDEX
statement and the sys.db_index_physical_stats view.
John
"Agnes" wrote:

> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that '
> thanks a lot
>
>|||sp_MSforeachtable 'dbcc dbreindex(''?'')'
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
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that '
> thanks a lot
>

Index Creation Question

I have a table with structure
payment_id varchar(10)
date_received datetime
order_id_response_stat varchar(10)
order_response_error_msg varchar(10)
The payment_id was the clustered index and primary key.
The problem now is this field should allow duplicates so I need to change
the indexing on this table. These table is used mostly for INSERTS and will
have about 1 million rows in future. The only occasional select will be base
d
on payment_id and date_received fields.
I dont know which options will be best for me under this situation as I want
to get max perf gains be using an index.
Can anyone pls help me"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
> The payment_id was the clustered index and primary key.
> The problem now is this field should allow duplicates so I need to change
OK, so what's your new primary key? Think about data integrity first --
then consider performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thats my question.
I can either create a composite index on payment and date and as a primary
key.
Can create identidy colmun as primary key
But I want to make sure that creating these wont impact the performance.
Pls somebody help what to do and the best scenario
"Adam Machanic" wrote:

> "Anup" <Anup@.discussions.microsoft.com> wrote in message
> news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
> OK, so what's your new primary key? Think about data integrity first
--
> then consider performance.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||From an insert point of view, it might make more sense to put the date first
in the composite key, assuming that you insert the payments as they're made.
But that might have a negative impact on selects, if you retrieve the data
based on the payment id. You should probably experiment on your end to find
the best combination.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:6A5A06FA-3087-4E8E-B926-4C30C98710A0@.microsoft.com...[vbcol=seagreen]
> Thats my question.
> I can either create a composite index on payment and date and as a primary
> key.
> Can create identidy colmun as primary key
> But I want to make sure that creating these wont impact the performance.
> Pls somebody help what to do and the best scenario
> "Adam Machanic" wrote:
>

Index Creation Question

I have a table with structure
payment_id varchar(10)
date_received datetime
order_id_response_stat varchar(10)
order_response_error_msg varchar(10)
The payment_id was the clustered index and primary key.
The problem now is this field should allow duplicates so I need to change
the indexing on this table. These table is used mostly for INSERTS and will
have about 1 million rows in future. The only occasional select will be based
on payment_id and date_received fields.
I dont know which options will be best for me under this situation as I want
to get max perf gains be using an index.
Can anyone pls help me"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
> The payment_id was the clustered index and primary key.
> The problem now is this field should allow duplicates so I need to change
OK, so what's your new primary key? Think about data integrity first --
then consider performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thats my question.
I can either create a composite index on payment and date and as a primary
key.
Can create identidy colmun as primary key
But I want to make sure that creating these wont impact the performance.
Pls somebody help what to do and the best scenario
"Adam Machanic" wrote:
> "Anup" <Anup@.discussions.microsoft.com> wrote in message
> news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
> >
> > The payment_id was the clustered index and primary key.
> >
> > The problem now is this field should allow duplicates so I need to change
> OK, so what's your new primary key? Think about data integrity first --
> then consider performance.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||From an insert point of view, it might make more sense to put the date first
in the composite key, assuming that you insert the payments as they're made.
But that might have a negative impact on selects, if you retrieve the data
based on the payment id. You should probably experiment on your end to find
the best combination.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:6A5A06FA-3087-4E8E-B926-4C30C98710A0@.microsoft.com...
> Thats my question.
> I can either create a composite index on payment and date and as a primary
> key.
> Can create identidy colmun as primary key
> But I want to make sure that creating these wont impact the performance.
> Pls somebody help what to do and the best scenario
> "Adam Machanic" wrote:
>> "Anup" <Anup@.discussions.microsoft.com> wrote in message
>> news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
>> >
>> > The payment_id was the clustered index and primary key.
>> >
>> > The problem now is this field should allow duplicates so I need to
>> > change
>> OK, so what's your new primary key? Think about data integrity
>> first --
>> then consider performance.
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>>

Index creation problem.

Dear all,
When I try to drop and recreate an index in my development server. I got
this error.
'Corp_billing' table
- Unable to create index 'PK_Corp_billing'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]A column has been
specified more than once in the order by list. Columns in the order by list
must be unique.
Besides this what are hypothetical indexes and can I delete them at
production server.
Kind regards
Khurram ShahzadAccording to the error message you are trying to create a primary key,
but you specify column's name more then once. Make sure that each
column is specified only once.
As for you second question - hypothetical index is created by the
index tuning wizard. If I remember correctly the server deletes those
indexes after a while.
Adi|||Adi,
I used the following syntax and I am also getting same error while dropping
through Enterprise Manager.
drop index Corp_billing.Corp_billing_idx
I created multiple indexes one column but they are in combination with other
coulumns.
"Adi" <adico@.clalit.org.il> wrote in message
news:1122975887.001679.176620@.g44g2000cwa.googlegroups.com...
> According to the error message you are trying to create a primary key,
> but you specify column's name more then once. Make sure that each
> column is specified only once.
> As for you second question - hypothetical index is created by the
> index tuning wizard. If I remember correctly the server deletes those
> indexes after a while.
> Adi
>|||Can you post the create index (or drop index) statement that you are
trying to run? In the previous message you wrote about an error that
you get when you create an index. From this message I understand that
you have an error when you are trying to drop an existing index.
I don't think that you have a limitation on how many indexes can use a
column, but you can not use a column in the same index more then once.
Adi|||Command 1:
Drop index Corp_billing.corp_billing
Error:
Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in
the order by list must be unique.
Command 2:
CREATE CLUSTERED INDEX Corp_billing ON Corp_billing (bill_id)
WITH FILLFACTOR = 90
Error:
Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in
the order by list must be unique.
"Adi" <adico@.clalit.org.il> wrote in message
news:1122977315.810889.247310@.g49g2000cwa.googlegroups.com...
> Can you post the create index (or drop index) statement that you are
> trying to run? In the previous message you wrote about an error that
> you get when you create an index. From this message I understand that
> you have an error when you are trying to drop an existing index.
> I don't think that you have a limitation on how many indexes can use a
> column, but you can not use a column in the same index more then once.
>
> Adi
>|||The hypothetical indexes never get deleted when they remain... Go ahead and
delete them..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Khurram Shahzad" <Khurram.Shahzad@.360training.com> wrote in message
news:Oh36TL0lFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Dear all,
>
> When I try to drop and recreate an index in my development server. I got
> this error.
>
> 'Corp_billing' table
> - Unable to create index 'PK_Corp_billing'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]A column has
> been specified more than once in the order by list. Columns in the order
> by list must be unique.
>
> Besides this what are hypothetical indexes and can I delete them at
> production server.
>
> Kind regards
> Khurram Shahzad
>|||Khurram Shahzad <Khurram.Shahzad@.360training.com> wrote:
> Command 1:
> Drop index Corp_billing.corp_billing
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list.
> Columns in the order by list must be unique.
>
> Command 2:
> CREATE CLUSTERED INDEX Corp_billing ON Corp_billing (bill_id)
> WITH FILLFACTOR = 90
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list.
> Columns in the order by list must be unique.
>
> "Adi" <adico@.clalit.org.il> wrote in message
> news:1122977315.810889.247310@.g49g2000cwa.googlegroups.com...
>> Can you post the create index (or drop index) statement that you are
>> trying to run? In the previous message you wrote about an error that
>> you get when you create an index. From this message I understand
>> that you have an error when you are trying to drop an existing index.
>> I don't think that you have a limitation on how many indexes can use
>> a column, but you can not use a column in the same index more then
>> once. Adi
Do you maybe have some kind of trigger on that table that is responsible for
the similar looking error message?
robert|||Khurram Shahzad (Khurram.Shahzad@.360training.com) writes:
> Command 1:
> Drop index Corp_billing.corp_billing
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list. Columns
> in the order by list must be unique.
Line 2? Was there a blank line nefore the DROP INDEX statement or
what?
> Command 2:
> CREATE CLUSTERED INDEX Corp_billing ON Corp_billing (bill_id)
> WITH FILLFACTOR = 90
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list. Columns
> in the order by list must be unique.
Wait a minute, if you were not able to drop the index, how could you
then be able to run this command?
Could you post the CREATE TABLE statement for the table?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Index creation problem.

Dear all,
When I try to drop and recreate an index in my development server. I got
this error.
'Corp_billing' table
- Unable to create index 'PK_Corp_billing'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]A column has been
specified more than once in the order by list. Columns in the order by list
must be unique.
Besides this what are hypothetical indexes and can I delete them at
production server.
Kind regards
Khurram Shahzad
According to the error message you are trying to create a primary key,
but you specify column's name more then once. Make sure that each
column is specified only once.
As for you second question - hypothetical index is created by the
index tuning wizard. If I remember correctly the server deletes those
indexes after a while.
Adi
|||Adi,
I used the following syntax and I am also getting same error while dropping
through Enterprise Manager.
drop index Corp_billing.Corp_billing_idx
I created multiple indexes one column but they are in combination with other
coulumns.
"Adi" <adico@.clalit.org.il> wrote in message
news:1122975887.001679.176620@.g44g2000cwa.googlegr oups.com...
> According to the error message you are trying to create a primary key,
> but you specify column's name more then once. Make sure that each
> column is specified only once.
> As for you second question - hypothetical index is created by the
> index tuning wizard. If I remember correctly the server deletes those
> indexes after a while.
> Adi
>
|||Can you post the create index (or drop index) statement that you are
trying to run? In the previous message you wrote about an error that
you get when you create an index. From this message I understand that
you have an error when you are trying to drop an existing index.
I don't think that you have a limitation on how many indexes can use a
column, but you can not use a column in the same index more then once.
Adi
|||Command 1:
Drop index Corp_billing.corp_billing
Error:
Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in
the order by list must be unique.
Command 2:
CREATE CLUSTERED INDEX Corp_billing ON Corp_billing (bill_id)
WITH FILLFACTOR = 90
Error:
Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in
the order by list must be unique.
"Adi" <adico@.clalit.org.il> wrote in message
news:1122977315.810889.247310@.g49g2000cwa.googlegr oups.com...
> Can you post the create index (or drop index) statement that you are
> trying to run? In the previous message you wrote about an error that
> you get when you create an index. From this message I understand that
> you have an error when you are trying to drop an existing index.
> I don't think that you have a limitation on how many indexes can use a
> column, but you can not use a column in the same index more then once.
>
> Adi
>
|||The hypothetical indexes never get deleted when they remain... Go ahead and
delete them..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Khurram Shahzad" <Khurram.Shahzad@.360training.com> wrote in message
news:Oh36TL0lFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Dear all,
>
> When I try to drop and recreate an index in my development server. I got
> this error.
>
> 'Corp_billing' table
> - Unable to create index 'PK_Corp_billing'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]A column has
> been specified more than once in the order by list. Columns in the order
> by list must be unique.
>
> Besides this what are hypothetical indexes and can I delete them at
> production server.
>
> Kind regards
> Khurram Shahzad
>
|||Khurram Shahzad <Khurram.Shahzad@.360training.com> wrote:[vbcol=seagreen]
> Command 1:
> Drop index Corp_billing.corp_billing
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list.
> Columns in the order by list must be unique.
>
> Command 2:
> CREATE CLUSTERED INDEX Corp_billing ON Corp_billing (bill_id)
> WITH FILLFACTOR = 90
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list.
> Columns in the order by list must be unique.
>
> "Adi" <adico@.clalit.org.il> wrote in message
> news:1122977315.810889.247310@.g49g2000cwa.googlegr oups.com...
Do you maybe have some kind of trigger on that table that is responsible for
the similar looking error message?
robert
|||Khurram Shahzad (Khurram.Shahzad@.360training.com) writes:
> Command 1:
> Drop index Corp_billing.corp_billing
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list. Columns
> in the order by list must be unique.
Line 2? Was there a blank line nefore the DROP INDEX statement or
what?

> Command 2:
> CREATE CLUSTERED INDEX Corp_billing ON Corp_billing (bill_id)
> WITH FILLFACTOR = 90
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list. Columns
> in the order by list must be unique.
Wait a minute, if you were not able to drop the index, how could you
then be able to run this command?
Could you post the CREATE TABLE statement for the table?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Index creation problem.

Dear all,
When I try to drop and recreate an index in my development server. I got
this error.
'Corp_billing' table
- Unable to create index 'PK_Corp_billing'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]A col
umn has been
specified more than once in the order by list. Columns in the order by list
must be unique.
Besides this what are hypothetical indexes and can I delete them at
production server.
Kind regards
Khurram ShahzadAccording to the error message you are trying to create a primary key,
but you specify column's name more then once. Make sure that each
column is specified only once.
As for you second question - hypothetical index is created by the
index tuning wizard. If I remember correctly the server deletes those
indexes after a while.
Adi|||Adi,
I used the following syntax and I am also getting same error while dropping
through Enterprise Manager.
drop index Corp_billing.Corp_billing_idx
I created multiple indexes one column but they are in combination with other
coulumns.
"Adi" <adico@.clalit.org.il> wrote in message
news:1122975887.001679.176620@.g44g2000cwa.googlegroups.com...
> According to the error message you are trying to create a primary key,
> but you specify column's name more then once. Make sure that each
> column is specified only once.
> As for you second question - hypothetical index is created by the
> index tuning wizard. If I remember correctly the server deletes those
> indexes after a while.
> Adi
>|||Can you post the create index (or drop index) statement that you are
trying to run? In the previous message you wrote about an error that
you get when you create an index. From this message I understand that
you have an error when you are trying to drop an existing index.
I don't think that you have a limitation on how many indexes can use a
column, but you can not use a column in the same index more then once.
Adi|||Command 1:
Drop index Corp_billing.corp_billing
Error:
Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in
the order by list must be unique.
Command 2:
CREATE CLUSTERED INDEX Corp_billing ON Corp_billing (bill_id)
WITH FILLFACTOR = 90
Error:
Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in
the order by list must be unique.
"Adi" <adico@.clalit.org.il> wrote in message
news:1122977315.810889.247310@.g49g2000cwa.googlegroups.com...
> Can you post the create index (or drop index) statement that you are
> trying to run? In the previous message you wrote about an error that
> you get when you create an index. From this message I understand that
> you have an error when you are trying to drop an existing index.
> I don't think that you have a limitation on how many indexes can use a
> column, but you can not use a column in the same index more then once.
>
> Adi
>|||The hypothetical indexes never get deleted when they remain... Go ahead and
delete them..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Khurram Shahzad" <Khurram.Shahzad@.360training.com> wrote in message
news:Oh36TL0lFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Dear all,
>
> When I try to drop and recreate an index in my development server. I got
> this error.
>
> 'Corp_billing' table
> - Unable to create index 'PK_Corp_billing'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]A c
olumn has
> been specified more than once in the order by list. Columns in the order
> by list must be unique.
>
> Besides this what are hypothetical indexes and can I delete them at
> production server.
>
> Kind regards
> Khurram Shahzad
>|||Khurram Shahzad <Khurram.Shahzad@.360training.com> wrote:[vbcol=seagreen]
> Command 1:
> Drop index Corp_billing.corp_billing
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list.
> Columns in the order by list must be unique.
>
> Command 2:
> CREATE CLUSTERED INDEX Corp_billing ON Corp_billing (bill_id)
> WITH FILLFACTOR = 90
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list.
> Columns in the order by list must be unique.
>
> "Adi" <adico@.clalit.org.il> wrote in message
> news:1122977315.810889.247310@.g49g2000cwa.googlegroups.com...
Do you maybe have some kind of trigger on that table that is responsible for
the similar looking error message?
robert|||Khurram Shahzad (Khurram.Shahzad@.360training.com) writes:
> Command 1:
> Drop index Corp_billing.corp_billing
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list. Columns
> in the order by list must be unique.
Line 2? Was there a blank line nefore the DROP INDEX statement or
what?

> Command 2:
> CREATE CLUSTERED INDEX Corp_billing ON Corp_billing (bill_id)
> WITH FILLFACTOR = 90
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list. Columns
> in the order by list must be unique.
Wait a minute, if you were not able to drop the index, how could you
then be able to run this command?
Could you post the CREATE TABLE statement for the table?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Index creation in SQL Server 2005 Management Studio - Page locks disabled by default

When I create an index on a table using SQL Server Management Studio, the index has page locks disabled by default. I'd prefer to have page locks enabled by default so neither I nor any other developer has to remember to manually modify. How can I accomplish this?

Thanks,

Pat Brickson

exec sp_indexoption '<TableName>', 'DisAllowPageLocks','FALSE'|||

Thank you, that solves my problem for existing tables. How can I ensure that this is the default for all new tables as well?

The CREATE INDEX statement in T-SQL enables page level locks by default but if I create via Management Studio, they're disabled by default. Why is there a discrepency?

Thanks, Pat

|||

After testing, I find that this only affects indexes that already exist on the table. I'd like to ensure that any new indexes on this table (or any existing or new table for that matter) have page locks enabled. Unfortunately this stored procedure doesn't really help me any more than just manually altering the index's options via the index properties dialog in Management Studio.

Again, I appreciate any assistance. Any other ideas?

Thanks, Pat

|||

The default settings for new indexes are hard coded in the dialog to match the defaults in SQL Server if you don't specify any options. There is no way for end users to change these defaults in the dialog.

Thanks,
Steve

|||

Maybe I misundertand you, but isn't the default to allow page locks when accessing the database? That's the default when issuing a T-SQL CREATE INDEX statement when no options are specified.

I'm really not concerned with end users changing the default. I'm more interested in ensuring that an any index created via Management Studio by a developer or DBA will have page locks enabled. Since that doesn't seem to be the default when creating in management studio, is there a way for me to specify which defaults the indexes should take?

Thanks, Pat

|||

If the dialog defaults aren't matching the default behavior in T-SQL, that's not intentional. Please file a defect report for this on http://connect.microsoft.com. Defects reported by customers via the connect site carry extra weight when the development team is prioritizing future work, including service pack work.

Be sure to mention the version of management studio you are working with and that the dialog is not defaulting to the engine default.

Thanks,
Steve

Index creation in SQL Server 2005 Management Studio - Page locks disabled by default

Thanks,

Pat Brickson

exec sp_indexoption '<TableName>', 'DisAllowPageLocks','FALSE'|||

Thank you, that solves my problem for existing tables. How can I ensure that this is the default for all new tables as well?

The CREATE INDEX statement in T-SQL enables page level locks by default but if I create via Management Studio, they're disabled by default. Why is there a discrepency?

Thanks, Pat

|||

Again, I appreciate any assistance. Any other ideas?

Thanks, Pat

|||

Thanks,
Steve

|||

Maybe I misundertand you, but isn't the default to allow page locks when accessing the database? That's the default when issuing a T-SQL CREATE INDEX statement when no options are specified.

Thanks, Pat

|||

Be sure to mention the version of management studio you are working with and that the dialog is not defaulting to the engine default.

Thanks,
Steve

Index creation datetime

Hi,
How I find out the creation datetime of index (clustered
and nonclustered)?
Regards.
--
Farhan SoomroNo, SQL Server does not keep any track of index creation date., However, if
you have primary key constraint (which will have an index
clustered/non-clustered) then you can refer to crdate column of sysobjects
table to check the creation date of this constraint which will be same as
creation date of index on primary key.
--
-Vishal
"Farhan Soomro" <fsoomro@.chartlinks.com> wrote in message
news:0bcc01c35b62$5157d860$a501280a@.phx.gbl...
> Hi,
> How I find out the creation datetime of index (clustered
> and nonclustered)?
> Regards.
> --
> Farhan Soomro

Index Creation Date

Does Microsoft SQL Server keep a record of an index creation date and
last modified date in a system table somewhere?If the index is a primary key or unique constraint, then you can find
the create data in sysobjects - see sysobjects in Books Online for the
details. Otherwise, I don't think it's possible. MSSQL 2000 doesn't
store the modified date for objects, although 2005 does.

Simon|||hi
you can check for crdate in sysobjects table

just try linking sysindexes and sysobjects tables in master database. u
might get the solution

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***

Index Creation Before Bulk Insert

This weekend, I tried to run some large bulk insert statements followed by create index statements on the tables I inserted the data into. For some reason, it didn't finish the largest insert statement. However, the index for that table started creating. Then my log file grew to a half a terabyte. Now the cancel statement is taking forever. What am I doing wrong? Here is the part of the code.

..........

BULK INSERT dbo.bigtable FROM 'data' WITH (TABLOCK);

USE [Database]

/****** Object: Index [ix_ID] Script Date: 04/27/2007 14:34:41 ******/

CREATE CLUSTERED INDEX [ix_ID] ON [dbo].[bigtable]

(

[ID] ASC

)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PS_Year]([DatafileYear])

On a huge table, such as you seem to be describing, I think there is a lot of effort required to order the table data to conform to a CLUSTERED INDEX.

You may want to try a non-clustered index and see what sort of performance penalty you incur.

Just a suggestion...

Dan

|||

Are there any reason why create clustered index after bulk insert ?

If create clustered index after bulk insert, SQL Server need a lot of data and log space for sort/move data.

|||

To try to assess the performance gains of having a CLUSTERED index versus a Non-CLUSTERED index on the same columns, I made some changes today to some of my data tables, on which I did not earlier have a CLUSTERED index.

I used the same columns, same column order, in the CLUSTERED index as in a Non-CLUSTERED index that I deleted.

A typical table size for the 6 tables I changed is 100MB and 1,000,000 rows.

I then ran a process that has maybe 50-100 independent SQL queries that perform various SUMs and other aggregations of the data in these tables, subject to different WHERE clauses, etc. The GROUP BY aspects of these SUMs use the columns specified in the CLUSTERED index. (I have run this process many, many times, so I have reasonable statistics on its typical duration.)

The CLUSTERED indexes seemed to give a performance gain of approximately 10%.

So there seems to be some benefit, for tables of this size, to have a CLUSTERED index that is reasonably well designed. But the benefit seems to be modest, i.e., around 10%, instead of the substantial benefit gained by having ANY appropriate index, whether CLUSTERED or Non-CLUSTERED.

FYI.

Dan

Index Creation = 100% CPU Utilization

I have a large table in a production environment that I needs to have an
index created on it. Its already got a clustered index on it, and I need to
create a non-clustered index. On creation, the CPU usage hits 100%, which
brings response time of the application over reasonable limits. Is there any
way to limit the CPU Usage for this task, or is there any other workaround to
create this index without bringing the application offline?
SQL Server 2000 SP3
P4 - 4 x 1GB
4GB Memory
Thanks in adavance
Set the DOP at the server level to 2 processors. That way there will be two
for the index creation and 2 for everyone else. It will take longer for the
index to be created but it won't affect the other users as much.
Andrew J. Kelly SQL MVP
"g$" <g@.discussions.microsoft.com> wrote in message
news:EF7C237E-1135-4E9D-AB10-450B2C031815@.microsoft.com...
> I have a large table in a production environment that I needs to have an
> index created on it. Its already got a clustered index on it, and I need
to
> create a non-clustered index. On creation, the CPU usage hits 100%, which
> brings response time of the application over reasonable limits. Is there
any
> way to limit the CPU Usage for this task, or is there any other workaround
to
> create this index without bringing the application offline?
> SQL Server 2000 SP3
> P4 - 4 x 1GB
> 4GB Memory
> Thanks in adavance

Index Creation = 100% CPU Utilization

I have a large table in a production environment that I needs to have an
index created on it. Its already got a clustered index on it, and I need to
create a non-clustered index. On creation, the CPU usage hits 100%, which
brings response time of the application over reasonable limits. Is there any
way to limit the CPU Usage for this task, or is there any other workaround to
create this index without bringing the application offline?
SQL Server 2000 SP3
P4 - 4 x 1GB
4GB Memory
Thanks in adavanceSet the DOP at the server level to 2 processors. That way there will be two
for the index creation and 2 for everyone else. It will take longer for the
index to be created but it won't affect the other users as much.
--
Andrew J. Kelly SQL MVP
"g$" <g@.discussions.microsoft.com> wrote in message
news:EF7C237E-1135-4E9D-AB10-450B2C031815@.microsoft.com...
> I have a large table in a production environment that I needs to have an
> index created on it. Its already got a clustered index on it, and I need
to
> create a non-clustered index. On creation, the CPU usage hits 100%, which
> brings response time of the application over reasonable limits. Is there
any
> way to limit the CPU Usage for this task, or is there any other workaround
to
> create this index without bringing the application offline?
> SQL Server 2000 SP3
> P4 - 4 x 1GB
> 4GB Memory
> Thanks in adavance

index creation

Hi, Folks

I have got a big table containing huge data, I am trying to create a composite index on three columns, the three columns are varchar2 datatype, but I notice(and confirm) that the data in these columns are numeric. in other words, I may change their datatype to be Numeric, hence, I have got two choices to create index either upon varchar, or Nuerice, can you tell me which way has the better performance to retrive data ?

Any advice will be highly appreciated.

xiongOriginally posted by xli
Hi, Folks

I have got a big table containing huge data, I am trying to create a composite index on three columns, the three columns are varchar2 datatype, but I notice(and confirm) that the data in these columns are numeric. in other words, I may change their datatype to be Numeric, hence, I have got two choices to create index either upon varchar, or Nuerice, can you tell me which way has the better performance to retrive data ?

Any advice will be highly appreciated.

xiong
If all the values in the column are menat to be numeric, then NUMBER is a better choice than VARCHAR2. It will use fewer bytes, and comparisons will be faster.

Index creation

Dears,
I have question regarding to some nonclustered indexes that were created on
the database. The company that is that has created this database is saying
that these indexes were created by our own.
My question is that, can indexes be created some how without command CREATE
INDEX or through Enterprise Manger on table.
I'm doing also DBCC DBREINDEX on table, but this can't create new index as
far as I know?
Can you please give me a feedback based on your experience, how this exactly
happenned? And can I discover let's say when this index was created and
additional information bisides sysindexes, much descriptive information.
Thank you,
BaniSQL.Hi
Yes, when you create a UNIQUE CONSTRAINT sql server creates non-clustered
unique index to enforce uniqueness
Also ,when you create a PRIMARY KEY on the table SQL Server creates a
clustered index to enforce uniqueness
> I'm doing also DBCC DBREINDEX on table, but this can't create new index as
> far as I know?
Its rebuild the existing indexes not creating
> Can you please give me a feedback based on your experience, how this
> exactly
> happenned? And can I discover let's say when this index was created and
> additional information bisides sysindexes, much descriptive information.
sp_helpindex 'tablename'
"BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
news:62D2D737-3906-40D5-9AF1-10437FE7C23E@.microsoft.com...
> Dears,
> I have question regarding to some nonclustered indexes that were created
> on
> the database. The company that is that has created this database is saying
> that these indexes were created by our own.
> My question is that, can indexes be created some how without command
> CREATE
> INDEX or through Enterprise Manger on table.
> I'm doing also DBCC DBREINDEX on table, but this can't create new index as
> far as I know?
> Can you please give me a feedback based on your experience, how this
> exactly
> happenned? And can I discover let's say when this index was created and
> additional information bisides sysindexes, much descriptive information.
> Thank you,
> BaniSQL.|||Thanks for your reply,
with PK contraint (clustered index) is fine, my question is does FK fields
can create indexes? Becuase I have only FK constraints when I ran sp_help
against the table.
And for information regarding to the for the particular index index I'm
interested to find: date of creation, user etc. from MDF not LDF.
Best,
ProBani.
"Uri Dimant" wrote:
> Hi
> Yes, when you create a UNIQUE CONSTRAINT sql server creates non-clustered
> unique index to enforce uniqueness
> Also ,when you create a PRIMARY KEY on the table SQL Server creates a
> clustered index to enforce uniqueness
> > I'm doing also DBCC DBREINDEX on table, but this can't create new index as
> > far as I know?
> Its rebuild the existing indexes not creating
> > Can you please give me a feedback based on your experience, how this
> > exactly
> > happenned? And can I discover let's say when this index was created and
> > additional information bisides sysindexes, much descriptive information.
> sp_helpindex 'tablename'
>
> "BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
> news:62D2D737-3906-40D5-9AF1-10437FE7C23E@.microsoft.com...
> > Dears,
> > I have question regarding to some nonclustered indexes that were created
> > on
> > the database. The company that is that has created this database is saying
> > that these indexes were created by our own.
> > My question is that, can indexes be created some how without command
> > CREATE
> > INDEX or through Enterprise Manger on table.
> > I'm doing also DBCC DBREINDEX on table, but this can't create new index as
> > far as I know?
> > Can you please give me a feedback based on your experience, how this
> > exactly
> > happenned? And can I discover let's say when this index was created and
> > additional information bisides sysindexes, much descriptive information.
> >
> > Thank you,
> > BaniSQL.
>
>|||Hi
> with PK contraint (clustered index) is fine, my question is does FK
> fields
> can create indexes? Becuase I have only FK constraints when
No , it does not.
select * from master..sysdatabases
You need crdate column
"BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
news:EF5F8DB2-40BE-47E6-A21B-92C73C547DC1@.microsoft.com...
> Thanks for your reply,
> with PK contraint (clustered index) is fine, my question is does FK
> fields
> can create indexes? Becuase I have only FK constraints when I ran sp_help
> against the table.
> And for information regarding to the for the particular index index I'm
> interested to find: date of creation, user etc. from MDF not LDF.
> Best,
> ProBani.
> "Uri Dimant" wrote:
>> Hi
>> Yes, when you create a UNIQUE CONSTRAINT sql server creates non-clustered
>> unique index to enforce uniqueness
>> Also ,when you create a PRIMARY KEY on the table SQL Server creates a
>> clustered index to enforce uniqueness
>> > I'm doing also DBCC DBREINDEX on table, but this can't create new index
>> > as
>> > far as I know?
>> Its rebuild the existing indexes not creating
>> > Can you please give me a feedback based on your experience, how this
>> > exactly
>> > happenned? And can I discover let's say when this index was created and
>> > additional information bisides sysindexes, much descriptive
>> > information.
>> sp_helpindex 'tablename'
>>
>> "BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
>> news:62D2D737-3906-40D5-9AF1-10437FE7C23E@.microsoft.com...
>> > Dears,
>> > I have question regarding to some nonclustered indexes that were
>> > created
>> > on
>> > the database. The company that is that has created this database is
>> > saying
>> > that these indexes were created by our own.
>> > My question is that, can indexes be created some how without command
>> > CREATE
>> > INDEX or through Enterprise Manger on table.
>> > I'm doing also DBCC DBREINDEX on table, but this can't create new index
>> > as
>> > far as I know?
>> > Can you please give me a feedback based on your experience, how this
>> > exactly
>> > happenned? And can I discover let's say when this index was created and
>> > additional information bisides sysindexes, much descriptive
>> > information.
>> >
>> > Thank you,
>> > BaniSQL.
>>

Index creation

index create memory

Hi,
I am trying to find out more information about the option "Index Create
Memory"..Will setting the "Index Create Memory" to a larger value other than
the default will improve the reindexing performance ? Do you guys know where
i can find good documentation on that ?
Thanks,SQL Server Books Online has information on this option
Dylan kruger
"kneel" <kvatsan@.comcast.net> wrote in message
news:OUIcabPaEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to find out more information about the option "Index Create
> Memory"..Will setting the "Index Create Memory" to a larger value other
> than
> the default will improve the reindexing performance ? Do you guys know
> where
> i can find good documentation on that ?
> Thanks,
>|||I checked it already and i thought it wasnt highly informative..
"Dylan kruger" <carmellobear1@.hotmail.com> wrote in message
news:%2314kbiaaEHA.3996@.TK2MSFTNGP12.phx.gbl...
> SQL Server Books Online has information on this option
> Dylan kruger
> "kneel" <kvatsan@.comcast.net> wrote in message
> news:OUIcabPaEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> > I am trying to find out more information about the option "Index
Create
> > Memory"..Will setting the "Index Create Memory" to a larger value other
> > than
> > the default will improve the reindexing performance ? Do you guys know
> > where
> > i can find good documentation on that ?
> >
> > Thanks,
> >
> >
>

index create memory

Hi,
I am trying to find out more information about the option "Index Create
Memory"..Will setting the "Index Create Memory" to a larger value other than
the default will improve the reindexing performance ? Do you guys know where
i can find good documentation on that ?
Thanks,
SQL Server Books Online has information on this option
Dylan kruger
"kneel" <kvatsan@.comcast.net> wrote in message
news:OUIcabPaEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to find out more information about the option "Index Create
> Memory"..Will setting the "Index Create Memory" to a larger value other
> than
> the default will improve the reindexing performance ? Do you guys know
> where
> i can find good documentation on that ?
> Thanks,
>
|||I checked it already and i thought it wasnt highly informative..
"Dylan kruger" <carmellobear1@.hotmail.com> wrote in message
news:%2314kbiaaEHA.3996@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> SQL Server Books Online has information on this option
> Dylan kruger
> "kneel" <kvatsan@.comcast.net> wrote in message
> news:OUIcabPaEHA.2520@.TK2MSFTNGP12.phx.gbl...
Create
>

index create memory

Index count

How can I find the number of indexes created on a user database (Only
clustered and Non-clustered indexes in user tables - not system).
Thanks."DXC" <DXC@.discussions.microsoft.com> wrote in message
news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> How can I find the number of indexes created on a user database (Only
> clustered and Non-clustered indexes in user tables - not system).
>
Select count(*) from sysindexes
where id in (select id from sysobjects where type = 'U')
and indid <> 255
255 is Text column.|||Set objSqlServer to your sql connection
Sub ListIndexes(strDBName)
WSCript.Echo "Database:" & Trim(strDBName)
Set oDatabase = objSqlServer.Databases(Trim(strDBName))
For Each Table In oDatabase.Tables
If NOT Table.SystemObject Then
WSCript.Echo Table.Name & " (" & Table.Indexes.Count & " indexes)"
<--here is the actual count property
For Each Index in Table.Indexes
If NOT Index.StatisticsIndex Then
WSCript.Echo vbTab & Index.Name & " (Stat: " & Index.StatisticsIndex &
")"
For Each Column in Index.ListIndexedColumns( )
WSCript.Echo vbTab & vbTab & "[" & Column.Name & "]"
Next
WSCript.Echo vbSpace
End If
Next
End IF
Next
End Sub
Or... more to the point
Set objSqlServer = YourSQLServerConnection
Set oDatabase = objSqlServer.Databases(TheDatabase)
For Each Table In oDatabase.Tables
If NOT Table.SystemObject Then
WSCript.StdOout.WriteLine Table.Indexes.Count
End If
Next
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> How can I find the number of indexes created on a user database (Only
> clustered and Non-clustered indexes in user tables - not system).
> Thanks.|||Not quite, in SQL 2000 statistics also take up an indid value in
sysindexes, so you would need to eliminate them also.
I think you would need to use INDEXPROPERTY to weed them out:
WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
name, 'IsHypothetical') = 0
HTH
Kalen Delaney
www.solidqualitylearning.com
"rkusenet" <rkusenet@.yahoo.com> wrote in message
news:4321d67f$0$91790$892e7fe2@.authen.white.readfreenews.net...
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
>> How can I find the number of indexes created on a user database (Only
>> clustered and Non-clustered indexes in user tables - not system).
> Select count(*) from sysindexes
> where id in (select id from sysobjects where type = 'U')
> and indid <> 255
> 255 is Text column.
>|||So, is following the correct syntax '
SELECT COUNT(*) FROM sysindexes
WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
name, 'IsHypothetical') = 0
Thanks.
"Kalen Delaney" wrote:
> Not quite, in SQL 2000 statistics also take up an indid value in
> sysindexes, so you would need to eliminate them also.
> I think you would need to use INDEXPROPERTY to weed them out:
> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
> name, 'IsHypothetical') = 0
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "rkusenet" <rkusenet@.yahoo.com> wrote in message
> news:4321d67f$0$91790$892e7fe2@.authen.white.readfreenews.net...
> >
> > "DXC" <DXC@.discussions.microsoft.com> wrote in message
> > news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> >> How can I find the number of indexes created on a user database (Only
> >> clustered and Non-clustered indexes in user tables - not system).
> >>
> >
> > Select count(*) from sysindexes
> > where id in (select id from sysobjects where type = 'U')
> > and indid <> 255
> >
> > 255 is Text column.
> >
> >
>
>|||what does 'IsHypothetical' mean?|||Hypothetical indexes hold column level statistics. They are created by SQL
Server and used internally. They cannot be used directly as a data access
path.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
<ford_desperado@.yahoo.com> wrote in message
news:1126296886.681664.90670@.g47g2000cwa.googlegroups.com...
> what does 'IsHypothetical' mean?
>|||Did you try it? If you have a syntax error you will be told so.
You will also need the other conditions in the WHERE clause, not just these
two.
HTH
Kalen Delaney
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:700341BA-4C8D-4BE5-9970-DFC1151E6DBB@.microsoft.com...
> So, is following the correct syntax '
> SELECT COUNT(*) FROM sysindexes
> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY
> (ID,
> name, 'IsHypothetical') = 0
> Thanks.
> "Kalen Delaney" wrote:
>> Not quite, in SQL 2000 statistics also take up an indid value in
>> sysindexes, so you would need to eliminate them also.
>> I think you would need to use INDEXPROPERTY to weed them out:
>> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY
>> (ID,
>> name, 'IsHypothetical') = 0
>> HTH
>> Kalen Delaney
>> www.solidqualitylearning.com
>>
>> "rkusenet" <rkusenet@.yahoo.com> wrote in message
>> news:4321d67f$0$91790$892e7fe2@.authen.white.readfreenews.net...
>> >
>> > "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> > news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
>> >> How can I find the number of indexes created on a user database (Only
>> >> clustered and Non-clustered indexes in user tables - not system).
>> >>
>> >
>> > Select count(*) from sysindexes
>> > where id in (select id from sysobjects where type = 'U')
>> > and indid <> 255
>> >
>> > 255 is Text column.
>> >
>> >
>>
>>
>|||An index created by the Index Tuning Wizard; these are usually removed when
the IDW is done, but sometimes they manage to stick around.
HTH
Kalen Delaney
<ford_desperado@.yahoo.com> wrote in message
news:1126296886.681664.90670@.g47g2000cwa.googlegroups.com...
> what does 'IsHypothetical' mean?
>

index count

Is there a way to find out the total number of indexes
(clustered and non-clustered) on a given database, either
through the use of QA or EM? I'm currently running
sp_helpindex against all tables in my database and then
manually counting each index.
Thanks.SELECT COUNT(*) FROM sysindexes
WHERE indid BETWEEN 1 AND 254
Indexes with indid 0 are heaps (tables without a clustered index), indid 1
are clustered indexes, indid 2-254 are non clustered indexes and indid 255
are used for text/ntext/images columns
--
Jacco Schalkwijk
SQL Server MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> Is there a way to find out the total number of indexes
> (clustered and non-clustered) on a given database, either
> through the use of QA or EM? I'm currently running
> sp_helpindex against all tables in my database and then
> manually counting each index.
> Thanks.|||Also not that this will include statistics and hypothetical indexes. These can be filtered out using
INDEXPEROPERTY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
news:%23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> SELECT COUNT(*) FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> Indexes with indid 0 are heaps (tables without a clustered index), indid 1 are clustered indexes,
> indid 2-254 are non clustered indexes and indid 255 are used for text/ntext/images columns
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>> Is there a way to find out the total number of indexes
>> (clustered and non-clustered) on a given database, either
>> through the use of QA or EM? I'm currently running
>> sp_helpindex against all tables in my database and then
>> manually counting each index.
>> Thanks.
>|||select count(*) from (SELECT sysobjects.name, sysobjects.id,
sysindexkeys.indid
FROM sysindexes INNER JOIN
sysobjects ON sysindexes.id = sysobjects.id INNER JOIN
sysindexkeys ON sysindexes.id = sysindexkeys.id
GROUP BY sysobjects.name, sysobjects.id, sysindexkeys.indid) drv
ll give the current databases index count
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> Is there a way to find out the total number of indexes
> (clustered and non-clustered) on a given database, either
> through the use of QA or EM? I'm currently running
> sp_helpindex against all tables in my database and then
> manually counting each index.
> Thanks.|||Oops, forgot about those.
SELECT COUNT(*) FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> Also not that this will include statistics and hypothetical indexes. These
> can be filtered out using INDEXPEROPERTY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
>> SELECT COUNT(*) FROM sysindexes
>> WHERE indid BETWEEN 1 AND 254
>> Indexes with indid 0 are heaps (tables without a clustered index), indid
>> 1 are clustered indexes, indid 2-254 are non clustered indexes and indid
>> 255 are used for text/ntext/images columns
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
>> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>> Is there a way to find out the total number of indexes
>> (clustered and non-clustered) on a given database, either
>> through the use of QA or EM? I'm currently running
>> sp_helpindex against all tables in my database and then
>> manually counting each index.
>> Thanks.
>>
>|||Thanks guys, that was really helpful. Any ideas on how to
apply the same filter in SQL6.5, since the INDEXPROPERTY
function isn't available in 6.5?
Thanks again.
>--Original Message--
>Oops, forgot about those.
>SELECT COUNT(*) FROM sysindexes
>WHERE indid BETWEEN 1 AND 254
>AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
>AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
>message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
>> Also not that this will include statistics and
hypothetical indexes. These
>> can be filtered out using INDEXPEROPERTY.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jacco Schalkwijk"
<jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote in message news:%
23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
>> SELECT COUNT(*) FROM sysindexes
>> WHERE indid BETWEEN 1 AND 254
>> Indexes with indid 0 are heaps (tables without a
clustered index), indid
>> 1 are clustered indexes, indid 2-254 are non clustered
indexes and indid
>> 255 are used for text/ntext/images columns
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>> Is there a way to find out the total number of indexes
>> (clustered and non-clustered) on a given database,
either
>> through the use of QA or EM? I'm currently running
>> sp_helpindex against all tables in my database and
then
>> manually counting each index.
>> Thanks.
>>
>>
>
>.
>|||I forget how a text page is represented in 6.5 sysindexes. But... you won't
have hypothetical indexes and I don't think stats show up as a row in
sysindexes. It's been sooo long, I just don't remember.
But I think you'll be safe simply looking at user tables with an indid
between 1-255 (or 254 if a row is there for text/image)
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:171201c4ad4c$1569c6f0$a301280a@.phx.gbl...
> Thanks guys, that was really helpful. Any ideas on how to
> apply the same filter in SQL6.5, since the INDEXPROPERTY
> function isn't available in 6.5?
> Thanks again.
> >--Original Message--
> >Oops, forgot about those.
> >
> >SELECT COUNT(*) FROM sysindexes
> >WHERE indid BETWEEN 1 AND 254
> >AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
> >AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> >message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> >> Also not that this will include statistics and
> hypothetical indexes. These
> >> can be filtered out using INDEXPEROPERTY.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Jacco Schalkwijk"
> <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> >> wrote in message news:%
> 23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> >> SELECT COUNT(*) FROM sysindexes
> >> WHERE indid BETWEEN 1 AND 254
> >>
> >> Indexes with indid 0 are heaps (tables without a
> clustered index), indid
> >> 1 are clustered indexes, indid 2-254 are non clustered
> indexes and indid
> >> 255 are used for text/ntext/images columns
> >>
> >> --
> >> Jacco Schalkwijk
> >> SQL Server MVP
> >>
> >>
> >> "Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> >> Is there a way to find out the total number of indexes
> >> (clustered and non-clustered) on a given database,
> either
> >> through the use of QA or EM? I'm currently running
> >> sp_helpindex against all tables in my database and
> then
> >> manually counting each index.
> >>
> >> Thanks.
> >>
> >>
> >>
> >>
> >
> >
> >.
> >|||SQL 6.5 didn't have column stats (only those associated with indexes) and it
didn't have hypothetical indexes, so you don't need to check for either of
these conditions.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:171201c4ad4c$1569c6f0$a301280a@.phx.gbl...
> Thanks guys, that was really helpful. Any ideas on how to
> apply the same filter in SQL6.5, since the INDEXPROPERTY
> function isn't available in 6.5?
> Thanks again.
>>--Original Message--
>>Oops, forgot about those.
>>SELECT COUNT(*) FROM sysindexes
>>WHERE indid BETWEEN 1 AND 254
>>AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
>>AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
>>--
>>Jacco Schalkwijk
>>SQL Server MVP
>>
>>"Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>>message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
>> Also not that this will include statistics and
> hypothetical indexes. These
>> can be filtered out using INDEXPEROPERTY.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jacco Schalkwijk"
> <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote in message news:%
> 23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
>> SELECT COUNT(*) FROM sysindexes
>> WHERE indid BETWEEN 1 AND 254
>> Indexes with indid 0 are heaps (tables without a
> clustered index), indid
>> 1 are clustered indexes, indid 2-254 are non clustered
> indexes and indid
>> 255 are used for text/ntext/images columns
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
>> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>> Is there a way to find out the total number of indexes
>> (clustered and non-clustered) on a given database,
> either
>> through the use of QA or EM? I'm currently running
>> sp_helpindex against all tables in my database and
> then
>> manually counting each index.
>> Thanks.
>>
>>
>>
>>.

index count

Index count

Index corruption on table without key: what could be the reasons ?

Dear group,

we are running a SQL-Server Database which is about 30 GB large. The
purpose of this database is to contain periodic data from automatic
devices which insert values into some tables.

Unfortunately most of these tables don't have a key (and a key can only
be introduced when the application programmers have changed their
software). Tables have this structure

deviceno timestamp data

where we expect for every device and timestamp one row of data.

In the ongoing operation it happens that the index of this large table
gets corrupted and a select from this table yields 2 rows for some
devices.

In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.

After rebuild of the indexes the table is "clean" again.

What could cause the index corruption ?

Missing key?
Faulty application program ?
a combination of both ?

How can i prevent this from happening again, as long as there is no
updated database / application ?

I'd be grateful for any useful comment

Regards

UliJust to clarify a common misconception: key constraints are used to
enfore data integrity, and indexes are used to enhance performance.
While primary keys also introduce an index upon creation, they are not
the same thing; you can create indexes without having primary keys at
all (this is a technique that can be used in data warehouses, where the
normalization rules are a bit "looser"). Index fragmentation is
unrelated to the presence or absence of keys; it can be, however,
related to the underlying clustered index that is (by default)
associated with the primary key.

A couple of questions: do you have a clustered index on this table?
On what column is it located?

Do you know how to use the DBCC command SHOWCONTIG? It will help you
identify it is fragmentation, and where it is occurring.

http://www.sql-server-performance.c..._showcontig.asp

HTH,
Stu|||Hi

You do not say what the index is on, and whether it is a unique index.

You may want to read:
http://www.aspfaq.com/show.asp?id=2081
http://www.aspfaq.com/etiquette.asp?id=5006

John

<uli2003wien@.lycos.at> wrote in message
news:1126693834.052381.263790@.g44g2000cwa.googlegr oups.com...
> Dear group,
> we are running a SQL-Server Database which is about 30 GB large. The
> purpose of this database is to contain periodic data from automatic
> devices which insert values into some tables.
> Unfortunately most of these tables don't have a key (and a key can only
> be introduced when the application programmers have changed their
> software). Tables have this structure
> deviceno timestamp data
> where we expect for every device and timestamp one row of data.
> In the ongoing operation it happens that the index of this large table
> gets corrupted and a select from this table yields 2 rows for some
> devices.
> In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
> BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.
> After rebuild of the indexes the table is "clean" again.
> What could cause the index corruption ?
> Missing key?
> Faulty application program ?
> a combination of both ?
> How can i prevent this from happening again, as long as there is no
> updated database / application ?
> I'd be grateful for any useful comment
> Regards
> Uli|||(uli2003wien@.lycos.at) writes:
> we are running a SQL-Server Database which is about 30 GB large. The
> purpose of this database is to contain periodic data from automatic
> devices which insert values into some tables.
> Unfortunately most of these tables don't have a key (and a key can only
> be introduced when the application programmers have changed their
> software). Tables have this structure
> deviceno timestamp data
> where we expect for every device and timestamp one row of data.
> In the ongoing operation it happens that the index of this large table
> gets corrupted and a select from this table yields 2 rows for some
> devices.
> In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
> BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.
> After rebuild of the indexes the table is "clean" again.
> What could cause the index corruption ?
> Missing key?
> Faulty application program ?
> a combination of both ?

If the duplicates disappear after a DBCC DBREINDEX (or DROP + CREATE, then
it is the index that is corrupted.

I seem to recall that there is an issue with heap tables that could cause
this. (A heap table is a table that does not have a clustered index.)
Can you define the index as clustered? Even better if you can add UNIQUE to
enforce uniqueness. Then again, it sounds as if the application is able
to insert duplicates?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||dont ever make a single table in your whole life that doesn't have a
single-column identity primary key|||Please tell me you're joking.

Stu|||<uli2003wien@.lycos.at> wrote:

> Dear group,
> we are running a SQL-Server Database which is about 30 GB large. The
> purpose of this database is to contain periodic data from automatic
> devices which insert values into some tables.
> Unfortunately most of these tables don't have a key (and a key can only
> be introduced when the application programmers have changed their
> software). Tables have this structure
> deviceno timestamp data
> where we expect for every device and timestamp one row of data.
> In the ongoing operation it happens that the index of this large table
> gets corrupted and a select from this table yields 2 rows for some
> devices.
> In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
> BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.
> After rebuild of the indexes the table is "clean" again.
> What could cause the index corruption ?
> Missing key?
> Faulty application program ?
> a combination of both ?
> How can i prevent this from happening again, as long as there is no
> updated database / application ?
> I'd be grateful for any useful comment
> Regards
> Uli

Uli,

Depending on how often your devices are firing inserts and how the timestamp
column is defined, I would expect to find multiple rows for a single
deviceno and timestamp combination (especially if you're using
smalldatetime).

But if I understand you correctly, you drop and re-create an index (or use
DBCC REINDEX) and the SELECT you posted returns different results (and this
happens consistently). If that's the case, you need to call MS support:
that's not supposed to happen.

Craig|||<dbahooker@.hotmail.com> wrote in message
news:1127919145.122247.19780@.g49g2000cwa.googlegro ups.com...
> dont ever make a single table in your whole life that doesn't have a
> single-column identity primary key

This is amazingly poor advice.
|||why?

aren't all indexes slower if you have your clustered index on a
multi-column varchar field?

i mean.. INT IDENTITY EVERYWHERE|||no greg im serious

why is it bad advice?

i just dont believe in tables that dont have a single numeric primary
key.

I'm just too friggin tired to use 'A345FD' as a primary key anymore..
use INTs or BIGINTs if you want. i would rather use a bigint than a
varchar any day of the week

but.. i mean.. no keys?

are you drunk?|||(dbahooker@.hotmail.com) writes:
> no greg im serious
> why is it bad advice?
> i just dont believe in tables that dont have a single numeric primary
> key.
> I'm just too friggin tired to use 'A345FD' as a primary key anymore..
> use INTs or BIGINTs if you want. i would rather use a bigint than a
> varchar any day of the week
> but.. i mean.. no keys?
> are you drunk?

Greg is usually not drunk when he posts. In fact, I have never felt
any smell of liquor from his posts.

Having a one-column key in all tables is actually a really poor idea.
Typical example: an order has a one-column key, which is an orderid.
But a detail row on a order has a two column key: an orderid and a
row number (or a product id).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||<dbahooker@.hotmail.com> wrote in message
news:1129052916.884917.318460@.o13g2000cwo.googlegr oups.com...
> no greg im serious
> why is it bad advice?

Because it doesn't necessarily model reality.

> i just dont believe in tables that dont have a single numeric primary
> key.

So if you were designing a database of cars you'd create your own single
numeric primary key rather than VIN?

Or if you were designing a database for Books you'd eschew the ISBN and use
an Identity column?

> I'm just too friggin tired to use 'A345FD' as a primary key anymore..

Then I suggest you get some more sleep.

> use INTs or BIGINTs if you want. i would rather use a bigint than a
> varchar any day of the week
> but.. i mean.. no keys?

Who said anything about no keys. Certainly not I. It's not a table w/o
keys.

> are you drunk?

Not hardly.
|||Erland Sommarskog wrote:
> Having a one-column key in all tables is actually a really poor idea.
> Typical example: an order has a one-column key, which is an orderid.
> But a detail row on a order has a two column key: an orderid and a
> row number (or a product id).

A problem arises when you have more than a simple system. You have:

Project (ProjectNo)
Order (ProjectNo, OrderNo)
Items (ProjectNo, OrderNo, ItemNo)
Expediting Split components [1] (ProjectNo, OrderNo, ItemNo, CompSplit)
Expediting Split Deliveries (ProjectNo, OrderNo, ItemNo, CompSplit,
DelSplit)
In real world, expeditor has no idea that freight forwarder can't fit
1,000,000 bricks on one truck (don't laff, I've seen it happen) so MMTs
(Material Movement Ticket) are split (ProjectNo, OrderNo, ItemNo,
CompSplit, DelSplit, MMTNo)
The MMT may go onto a container, then later on is split into trucks
(that move at different speeds), or some items get held longer in
customs than others resulting in many MRRs (Material Received Report)
for one MMT, MMR Item has a key that looks like a complete table before
any meaningful data gets there.

Now, given that having 7 columns in a PK is stupid, do you:

a) Give a surrogate key to some tables and a natural key to others and
thereby be inconsistent or

b) Be consistent and use surrogate keys?

Given that no-one in their right mind would use just a surrogate key as
the unique constraint but also have a candidate key so no real
duplicates can arise. Also that these surrogate keys (Identity columns)
are *never* presented to the user, only the computer knows they exist.

[1] e.g. you order 1 x lifeboat but supplier later advises split
delivery of 1 x dingy, 6 x oars, 6 x life jackets, 1 x motor, etc.|||First, your advice indicates that you don't understand the difference
between an index and a key; since you seem to have a background in
Access programming, that's understandable because Access goes out of
its way to hide the distinction. However, in SQL Server, indexes are
very different than keys, with different purposes. A key is used to
establish and maintain data integrity; an index is used to expedite
data retrieval.

Second, (as others have pointed out) a surrogate key has problems. It
doesn't reflect reality and it cannot be used for data validation.
However, I will grant you that using a simple surrogate key (like an
integer) can have benefits in terms of performance, scalability, and
data maintenance; however, those benefits come with a cost.

Which leads to my third point: by specifying an absolute, you have
pigeonholed your designs. It's a bit like a mechanic insisting on
using a #2 Phillips head bit on a #3 hole; all you get is a stripped
out, screwed up design. Again, using surrogate keys with a clustered
index is NOT a bad idea in the right situation; but in the wrong
situation, it's a dumb idea.

Stu|||Trevor Best (nospam@.localhost.invalid) writes:
> Erland Sommarskog wrote:
>> Having a one-column key in all tables is actually a really poor idea.
>> Typical example: an order has a one-column key, which is an orderid.
>> But a detail row on a order has a two column key: an orderid and a
>> row number (or a product id).
> A problem arises when you have more than a simple system. You have:

Dunno. With something like 500 tables in the data model I maintain, I
guess that it qualifies as "more than a simple system". And, no, not
all of them have one-column keys.

> Project (ProjectNo)
> Order (ProjectNo, OrderNo)
> Items (ProjectNo, OrderNo, ItemNo)
> Expediting Split components [1] (ProjectNo, OrderNo, ItemNo, CompSplit)
> Expediting Split Deliveries (ProjectNo, OrderNo, ItemNo, CompSplit,
> DelSplit)
> In real world, expeditor has no idea that freight forwarder can't fit
> 1,000,000 bricks on one truck (don't laff, I've seen it happen) so MMTs
> (Material Movement Ticket) are split (ProjectNo, OrderNo, ItemNo,
> CompSplit, DelSplit, MMTNo)
> The MMT may go onto a container, then later on is split into trucks
> (that move at different speeds), or some items get held longer in
> customs than others resulting in many MRRs (Material Received Report)
> for one MMT, MMR Item has a key that looks like a complete table before
> any meaningful data gets there.
> Now, given that having 7 columns in a PK is stupid, do you:

I don't know the business domain well enough, so I prefer to not comment
on the details.

But I was once in the situation where I had a table with a four-column
key, and a need arose for a subtable with two more keys. So, I added
a surrogate to the existing table.

Much later I had reason to write code to maintain data in those tables,
and the UPDATE/INSERT for the subtable were complex. Because of the
surrogate key.

That surrogate key is no more by the way. (But for other reasons, two
other key levels have also gone away.)

On the top of my head I know we have a table with a five-column key. But
I would shudder for a seven-colunm key if it was the natural one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Friday, February 24, 2012

index database

index database

Index Creation Question

Index Creation Question

Index creation problem.

Index creation problem.

Index creation problem.

Index creation in SQL Server 2005 Management Studio - Page locks disabled by default

Index creation in SQL Server 2005 Management Studio - Page locks disabled by default

Index creation datetime

Index Creation Date

Index Creation Before Bulk Insert

Index Creation = 100% CPU Utilization

Index Creation = 100% CPU Utilization

index creation

Index creation

Index creation

Index creation

index create memory

index create memory

index create memory

Index count

index count

index count

Index count

Index count

Index corruption on table without key: what could be the reasons ?

index and key

Blog Archive

About Me