Indexes on Computed columns and ADO

OK maybe this crosses the SQLServer forum (apologies and dlet me know if so) BUT:

I have a composite PK/index with a computed column (basically CASTing a bit value). The index is fine in SQL2K however when I try to access the table through an ADO recordset (to a VBA form) the Datasheet control is locked.

Seems like a behind the scene ADO cursor-type/lock-type issue. ANY input is welcome I AM UNDER THE PRODUCTION GUN.

TIAYou can try to set the "arithmetic abort" property to on (in SQlServer>properties>connection tab).

I had the same problem, in one of my application, except there I was logging on to the server via ODBC drivers, not OLEDB (as ADO usualy does).

I hope it works. Good luck!

IONUT|||Originally posted by zarathustra
OK maybe this crosses the SQLServer forum (apologies and dlet me know if so) BUT:

I have a composite PK/index with a computed column (basically CASTing a bit value). The index is fine in SQL2K however when I try to access the table through an ADO recordset (to a VBA form) the Datasheet control is locked.

Seems like a behind the scene ADO cursor-type/lock-type issue. ANY input is welcome I AM UNDER THE PRODUCTION GUN.

TIA

Try what happens if you lose the computed column in the primary key and replace it by the column(s) that the computed column was based on. There should be no need to have a computed column in the primary key.

I must admit that I also had trouble with ADO when using computed columns and I quickly ended up avoiding computed columns, not wanting to spend the time to find how to solve the problems.|||Originally posted by Ad Dieleman
Try what happens if you lose the computed column in the primary key and replace it by the column(s) that the computed column was based on. There should be no need to have a computed column in the primary key.

I must admit that I also had trouble with ADO when using computed columns and I quickly ended up avoiding computed columns, not wanting to spend the time to find how to solve the problems.

I agree about not having the PK contain a computed val but IXs can't have bit values. Hence the comp'd field. I've got it working now. I've seen issues as well with computed fields/ADO and connection settings like ARITHABORT etc... joy.

thanks to you both.

Indexes :-

hi,
one small ques on indexes, i added one composite index on table to avoud clus index scan(cost 70%) , when i checked executioin plan it is showing me like clus index seek using the above defined index + bookmark lookup operator also..
so which soln should i go for clus index scan or bookmark operator using clus index seek (for composit index0
--
SanjuRun the query both ways in Query Analyzer and look at the Total Plan cost
... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju

Indexes :-

hi,
one small ques on indexes, i added one composite index on table to avoud clu
s index scan(cost 70%) , when i checked executioin plan it is showing me lik
e clus index seek using the above defined index + bookmark lookup operator a
lso..
so which soln should i go for clus index scan or bookmark operator using clu
s index seek (for composit index0
SanjuRun the query both ways in Query Analyzer and look at the Total Plan cost
... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju

Indexes :-

hi,
one small ques on indexes, i added one composite index on table to avoud clus index scan(cost 70%) , when i checked executioin plan it is showing me like clus index seek using the above defined index + bookmark lookup operator also..
so which soln should i go for clus index scan or bookmark operator using clus index seek (for composit index0
Sanju
Run the query both ways in Query Analyzer and look at the Total Plan cost
.... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju

Indexes

I have cluster index created on a composite primary key (acct_key,period).
I would like to create a non-cluster index on the acct_key field, since
there are numerous sql statements that extract single value from this field.
Please let me know if cluster index has a composite primary key neither
field should be in a non-cluster index?A) If you already have a clustered index on (acct_key, period), there is no
reason to create a non-clustered index on acct_key.
B) Non-clustered indexes always contain the columns from the clustered
index.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
> I have cluster index created on a composite primary key (acct_key,period).
> I would like to create a non-cluster index on the acct_key field, since
> there are numerous sql statements that extract single value from this
> field.
> Please let me know if cluster index has a composite primary key neither
> field should be in a non-cluster index?
>|||> A) If you already have a clustered index on (acct_key, period), there is no reason to crea
te a
> non-clustered index on acct_key.
... unless you do it to cover queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23VCUMJ49FHA.3980@.TK2MSFTNGP14.phx.gbl...
> A) If you already have a clustered index on (acct_key, period), there is n
o reason to create a
> non-clustered index on acct_key.
> B) Non-clustered indexes always contain the columns from the clustered ind
ex.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> ... unless you do it to cover queries.
You'd have to have a pretty wide table for that to make a difference --
the clustered index already covers every possible query...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||* every possible query that uses acct_key, that is.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> You'd have to have a pretty wide table for that to make a difference --
> the clustered index already covers every possible query...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Hmm, yes, assuming this is the only column to be in the nc index (which I no
w see was the case,
re-reading the OP). I was thrown off a bit by this:

I have difficulties understanding what "extract single values from this fiel
d" means. My thinking
was that the NC index could cover queries where the restriction is for some
other column than the
first column in the CL index (to enable nc ix scan instead of cl ix scan).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bXUAbC%23FHA.4004@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
>* every possible query that uses acct_key, that is.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>

Indexes

I have cluster index created on a composite primary key (acct_key,period).
I would like to create a non-cluster index on the acct_key field, since
there are numerous sql statements that extract single value from this field.
Please let me know if cluster index has a composite primary key neither
field should be in a non-cluster index?A) If you already have a clustered index on (acct_key, period), there is no
reason to create a non-clustered index on acct_key.
B) Non-clustered indexes always contain the columns from the clustered
index.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
> I have cluster index created on a composite primary key (acct_key,period).
> I would like to create a non-cluster index on the acct_key field, since
> there are numerous sql statements that extract single value from this
> field.
> Please let me know if cluster index has a composite primary key neither
> field should be in a non-cluster index?
>|||> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
> non-clustered index on acct_key.
... unless you do it to cover queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23VCUMJ49FHA.3980@.TK2MSFTNGP14.phx.gbl...
> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
> non-clustered index on acct_key.
> B) Non-clustered indexes always contain the columns from the clustered index.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
>> I have cluster index created on a composite primary key (acct_key,period).
>> I would like to create a non-cluster index on the acct_key field, since
>> there are numerous sql statements that extract single value from this field.
>> Please let me know if cluster index has a composite primary key neither
>> field should be in a non-cluster index?
>>
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
>> A) If you already have a clustered index on (acct_key, period), there is
>> no reason to create a non-clustered index on acct_key.
> ... unless you do it to cover queries.
You'd have to have a pretty wide table for that to make a difference --
the clustered index already covers every possible query...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||* every possible query that uses acct_key, that is.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
>> A) If you already have a clustered index on (acct_key, period), there is
>> no reason to create a non-clustered index on acct_key.
>> ... unless you do it to cover queries.
> You'd have to have a pretty wide table for that to make a difference --
> the clustered index already covers every possible query...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Hmm, yes, assuming this is the only column to be in the nc index (which I now see was the case,
re-reading the OP). I was thrown off a bit by this:
>> I would like to create a non-cluster index on the acct_key field, since
>> there are numerous sql statements that extract single value from this field.
I have difficulties understanding what "extract single values from this field" means. My thinking
was that the NC index could cover queries where the restriction is for some other column than the
first column in the CL index (to enable nc ix scan instead of cl ix scan).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bXUAbC%23FHA.4004@.TK2MSFTNGP14.phx.gbl...
>* every possible query that uses acct_key, that is.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
>> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
>> non-clustered index on acct_key.
>> ... unless you do it to cover queries.
>> You'd have to have a pretty wide table for that to make a difference -- the clustered index
>> already covers every possible query...
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>

Indexes

I have cluster index created on a composite primary key (acct_key,period).
I would like to create a non-cluster index on the acct_key field, since
there are numerous sql statements that extract single value from this field.
Please let me know if cluster index has a composite primary key neither
field should be in a non-cluster index?
A) If you already have a clustered index on (acct_key, period), there is no
reason to create a non-clustered index on acct_key.
B) Non-clustered indexes always contain the columns from the clustered
index.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
> I have cluster index created on a composite primary key (acct_key,period).
> I would like to create a non-cluster index on the acct_key field, since
> there are numerous sql statements that extract single value from this
> field.
> Please let me know if cluster index has a composite primary key neither
> field should be in a non-cluster index?
>
|||> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
> non-clustered index on acct_key.
... unless you do it to cover queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23VCUMJ49FHA.3980@.TK2MSFTNGP14.phx.gbl...
> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
> non-clustered index on acct_key.
> B) Non-clustered indexes always contain the columns from the clustered index.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> ... unless you do it to cover queries.
You'd have to have a pretty wide table for that to make a difference --
the clustered index already covers every possible query...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||* every possible query that uses acct_key, that is.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> You'd have to have a pretty wide table for that to make a difference --
> the clustered index already covers every possible query...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
|||Hmm, yes, assuming this is the only column to be in the nc index (which I now see was the case,
re-reading the OP). I was thrown off a bit by this:
[vbcol=seagreen]
I have difficulties understanding what "extract single values from this field" means. My thinking
was that the NC index could cover queries where the restriction is for some other column than the
first column in the CL index (to enable nc ix scan instead of cl ix scan).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bXUAbC%23FHA.4004@.TK2MSFTNGP14.phx.gbl...
>* every possible query that uses acct_key, that is.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>