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:
>
Showing posts with label creation. Show all posts
Showing posts with label creation. Show all posts
Friday, February 24, 2012
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
>> --
>>
>>
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
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
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
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
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 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
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.
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);
GO
USE [Database]
GO
/****** 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])
GO
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 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 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.
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.
>>
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
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
>
> Its rebuild the existing indexes not creating
>
> sp_helpindex 'tablename'
>
> "BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
> news:62D2D737-3906-40D5-9AF1-10437FE7C23E@.microsoft.com...
>
>
|||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...[vbcol=seagreen]
> 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:
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
>
> Its rebuild the existing indexes not creating
>
> sp_helpindex 'tablename'
>
> "BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
> news:62D2D737-3906-40D5-9AF1-10437FE7C23E@.microsoft.com...
>
>
|||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...[vbcol=seagreen]
> 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:
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
>
> Its rebuild the existing indexes not creating
>
> sp_helpindex 'tablename'
>
> "BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
> news:62D2D737-3906-40D5-9AF1-10437FE7C23E@.microsoft.com...
>
>|||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...[vbcol=seagreen]
> 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:
>
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
>
> Its rebuild the existing indexes not creating
>
> sp_helpindex 'tablename'
>
> "BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
> news:62D2D737-3906-40D5-9AF1-10437FE7C23E@.microsoft.com...
>
>|||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...[vbcol=seagreen]
> 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:
>
Subscribe to:
Posts (Atom)