Consider the following:
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David
|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
c2
> other
> for
> plan.
> that
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>
2012年3月19日星期一
Any way to force a recompile on a statement within a User Defined Function?
Consider the following:
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
--
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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> > Consider the following:
> >
> > TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> > There is a clustered index on (c1, c3), nonclustered index on (c2)
> >
> > The UDF contains a SELECT statement of the form:
> > select c1 from TableA where c2 = @.InputParm
> >
> > TableA contains about 300000 rows and there are some values for column
c2
> > that have a cardinality of over 30000. So obviously if this value gets
> > passed into the UDF, a Clustered Index Scan is probably best. On the
> other
> > hand if a parm gets passed for a value that has a few corresponding rows
> for
> > c2 then it is best that the nonclustered index on c2 be used.
> >
> > The problem is that when the UDF is first invoked with a parm that has a
> > high number of corresponding rows for c2, the execution plan uses a
> > clustered index SCAN and all subsuquent calls use this same execution
> plan.
> > Ideally I would like to have this UDF defined to recompile each call so
> that
> > I get the best execution plan for each call.
> >
> > If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> > (doesn't appear to be valid for functions), or I would use dynamic SQL
> > (since Functions cannot access temp tables I cannot use this approach
> > either).
> >
> > Any ideas would be appreciated.
> >
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
--
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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> > Consider the following:
> >
> > TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> > There is a clustered index on (c1, c3), nonclustered index on (c2)
> >
> > The UDF contains a SELECT statement of the form:
> > select c1 from TableA where c2 = @.InputParm
> >
> > TableA contains about 300000 rows and there are some values for column
c2
> > that have a cardinality of over 30000. So obviously if this value gets
> > passed into the UDF, a Clustered Index Scan is probably best. On the
> other
> > hand if a parm gets passed for a value that has a few corresponding rows
> for
> > c2 then it is best that the nonclustered index on c2 be used.
> >
> > The problem is that when the UDF is first invoked with a parm that has a
> > high number of corresponding rows for c2, the execution plan uses a
> > clustered index SCAN and all subsuquent calls use this same execution
> plan.
> > Ideally I would like to have this UDF defined to recompile each call so
> that
> > I get the best execution plan for each call.
> >
> > If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> > (doesn't appear to be valid for functions), or I would use dynamic SQL
> > (since Functions cannot access temp tables I cannot use this approach
> > either).
> >
> > Any ideas would be appreciated.
> >
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>
Any way to force a recompile on a statement within a User Defined Function?
Consider the following:
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
c2[vbcol=seagreen]
> other
> for
> plan.
> that
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
c2[vbcol=seagreen]
> other
> for
> plan.
> that
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>
2012年3月8日星期四
Any special way to create an index
Hi All,
I am not very experienced in creating indexes. Is there a preferred way
one should follow. I created come indexes using EM. When I do that, and
hit save on the table, does the SQL server at that point generate the
indexes?
Thanks for your comments/help.
*** Sent via Developersdex http://www.examnotes.net ***yes.
Or you can run a script from the query analyzer.
CREATE INDEX MyNewIndex ON TableName (ColumnName)
this is a very simple example. Look up "Create Index" in books on line (BOL)
for more detailed explanation
Greg Jackson
PDX, Oregon|||For what it's worth, within the Enterprise Manager's table designer, after
adding entries for your indexes, you can click the "save change script"
button instead of the "save" button, and it will display a dialog with the
SQL scripts that would be applied. You may want to save off the script, if
you are going to re-apply the indexes on other occasions.
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:Oq6yebVXFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I am not very experienced in creating indexes. Is there a preferred way
> one should follow. I created come indexes using EM. When I do that, and
> hit save on the table, does the SQL server at that point generate the
> indexes?
> Thanks for your comments/help.
> *** Sent via Developersdex http://www.examnotes.net ***
I am not very experienced in creating indexes. Is there a preferred way
one should follow. I created come indexes using EM. When I do that, and
hit save on the table, does the SQL server at that point generate the
indexes?
Thanks for your comments/help.
*** Sent via Developersdex http://www.examnotes.net ***yes.
Or you can run a script from the query analyzer.
CREATE INDEX MyNewIndex ON TableName (ColumnName)
this is a very simple example. Look up "Create Index" in books on line (BOL)
for more detailed explanation
Greg Jackson
PDX, Oregon|||For what it's worth, within the Enterprise Manager's table designer, after
adding entries for your indexes, you can click the "save change script"
button instead of the "save" button, and it will display a dialog with the
SQL scripts that would be applied. You may want to save off the script, if
you are going to re-apply the indexes on other occasions.
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:Oq6yebVXFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I am not very experienced in creating indexes. Is there a preferred way
> one should follow. I created come indexes using EM. When I do that, and
> hit save on the table, does the SQL server at that point generate the
> indexes?
> Thanks for your comments/help.
> *** Sent via Developersdex http://www.examnotes.net ***
2012年2月23日星期四
any impact if create index and then drop index
hi,
i want to create index to speed up one operation (DELETE) and the drop the
index to resume.
any side effects or impacts on the db?
On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
>hi,
>i want to create index to speed up one operation (DELETE) and the drop the
>index to resume.
>any side effects or impacts on the db?
>
Hi Mullin,
Creating the index will take some time and will also lock parts of your
data. Dropping the index doesn't take much time (unless it is a clustered
index).
However, why would you want to drop the index after the delete?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||because want to add an index on the third-party product for a temp
operation. don't know any impacts on that product if exist an index
persistently.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fkdoq0p5sb73abfmke0phv999kjaa2k4p9@.4ax.com... [vbcol=seagreen]
> On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
the
> Hi Mullin,
> Creating the index will take some time and will also lock parts of your
> data. Dropping the index doesn't take much time (unless it is a clustered
> index).
> However, why would you want to drop the index after the delete?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Wed, 1 Dec 2004 15:47:04 +0800, Mullin Yu wrote:
>because want to add an index on the third-party product for a temp
>operation. don't know any impacts on that product if exist an index
>persistently.
Hi Mullin,
An extra index will:
* Slightly decrease performance of inserts, deletes and updates that
affect the column(s) used in the index,
* Speed up those queries that can use this index,
* Slightly increase the size of databases and backups,
* Have no effect on any other operation.
All this on the assumption that you're talking about a nonclustered index.
A clustered index has more impact. (But since building and dropping a
clustered index is quite costly, I don't think you'd consider this for
your temp index).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
i want to create index to speed up one operation (DELETE) and the drop the
index to resume.
any side effects or impacts on the db?
On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
>hi,
>i want to create index to speed up one operation (DELETE) and the drop the
>index to resume.
>any side effects or impacts on the db?
>
Hi Mullin,
Creating the index will take some time and will also lock parts of your
data. Dropping the index doesn't take much time (unless it is a clustered
index).
However, why would you want to drop the index after the delete?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||because want to add an index on the third-party product for a temp
operation. don't know any impacts on that product if exist an index
persistently.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fkdoq0p5sb73abfmke0phv999kjaa2k4p9@.4ax.com... [vbcol=seagreen]
> On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
the
> Hi Mullin,
> Creating the index will take some time and will also lock parts of your
> data. Dropping the index doesn't take much time (unless it is a clustered
> index).
> However, why would you want to drop the index after the delete?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Wed, 1 Dec 2004 15:47:04 +0800, Mullin Yu wrote:
>because want to add an index on the third-party product for a temp
>operation. don't know any impacts on that product if exist an index
>persistently.
Hi Mullin,
An extra index will:
* Slightly decrease performance of inserts, deletes and updates that
affect the column(s) used in the index,
* Speed up those queries that can use this index,
* Slightly increase the size of databases and backups,
* Have no effect on any other operation.
All this on the assumption that you're talking about a nonclustered index.
A clustered index has more impact. (But since building and dropping a
clustered index is quite costly, I don't think you'd consider this for
your temp index).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
any impact if create index and then drop index
hi,
i want to create index to speed up one operation (DELETE) and the drop the
index to resume.
any side effects or impacts on the db?On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
>hi,
>i want to create index to speed up one operation (DELETE) and the drop the
>index to resume.
>any side effects or impacts on the db?
>
Hi Mullin,
Creating the index will take some time and will also lock parts of your
data. Dropping the index doesn't take much time (unless it is a clustered
index).
However, why would you want to drop the index after the delete?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||because want to add an index on the third-party product for a temp
operation. don't know any impacts on that product if exist an index
persistently.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fkdoq0p5sb73abfmke0phv999kjaa2k4p9@.
4ax.com...
> On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
>
the[vbcol=seagreen]
> Hi Mullin,
> Creating the index will take some time and will also lock parts of your
> data. Dropping the index doesn't take much time (unless it is a clustered
> index).
> However, why would you want to drop the index after the delete?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 1 Dec 2004 15:47:04 +0800, Mullin Yu wrote:
>because want to add an index on the third-party product for a temp
>operation. don't know any impacts on that product if exist an index
>persistently.
Hi Mullin,
An extra index will:
* Slightly decrease performance of inserts, deletes and updates that
affect the column(s) used in the index,
* Speed up those queries that can use this index,
* Slightly increase the size of databases and backups,
* Have no effect on any other operation.
All this on the assumption that you're talking about a nonclustered index.
A clustered index has more impact. (But since building and dropping a
clustered index is quite costly, I don't think you'd consider this for
your temp index).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
i want to create index to speed up one operation (DELETE) and the drop the
index to resume.
any side effects or impacts on the db?On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
>hi,
>i want to create index to speed up one operation (DELETE) and the drop the
>index to resume.
>any side effects or impacts on the db?
>
Hi Mullin,
Creating the index will take some time and will also lock parts of your
data. Dropping the index doesn't take much time (unless it is a clustered
index).
However, why would you want to drop the index after the delete?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||because want to add an index on the third-party product for a temp
operation. don't know any impacts on that product if exist an index
persistently.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fkdoq0p5sb73abfmke0phv999kjaa2k4p9@.
4ax.com...
> On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
>
the[vbcol=seagreen]
> Hi Mullin,
> Creating the index will take some time and will also lock parts of your
> data. Dropping the index doesn't take much time (unless it is a clustered
> index).
> However, why would you want to drop the index after the delete?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 1 Dec 2004 15:47:04 +0800, Mullin Yu wrote:
>because want to add an index on the third-party product for a temp
>operation. don't know any impacts on that product if exist an index
>persistently.
Hi Mullin,
An extra index will:
* Slightly decrease performance of inserts, deletes and updates that
affect the column(s) used in the index,
* Speed up those queries that can use this index,
* Slightly increase the size of databases and backups,
* Have no effect on any other operation.
All this on the assumption that you're talking about a nonclustered index.
A clustered index has more impact. (But since building and dropping a
clustered index is quite costly, I don't think you'd consider this for
your temp index).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
any impact if create index and then drop index
hi,
i want to create index to speed up one operation (DELETE) and the drop the
index to resume.
any side effects or impacts on the db?On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
>hi,
>i want to create index to speed up one operation (DELETE) and the drop the
>index to resume.
>any side effects or impacts on the db?
>
Hi Mullin,
Creating the index will take some time and will also lock parts of your
data. Dropping the index doesn't take much time (unless it is a clustered
index).
However, why would you want to drop the index after the delete?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||because want to add an index on the third-party product for a temp
operation. don't know any impacts on that product if exist an index
persistently.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fkdoq0p5sb73abfmke0phv999kjaa2k4p9@.4ax.com...
> On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
> >hi,
> >
> >i want to create index to speed up one operation (DELETE) and the drop
the
> >index to resume.
> >
> >any side effects or impacts on the db?
> >
> Hi Mullin,
> Creating the index will take some time and will also lock parts of your
> data. Dropping the index doesn't take much time (unless it is a clustered
> index).
> However, why would you want to drop the index after the delete?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 1 Dec 2004 15:47:04 +0800, Mullin Yu wrote:
>because want to add an index on the third-party product for a temp
>operation. don't know any impacts on that product if exist an index
>persistently.
Hi Mullin,
An extra index will:
* Slightly decrease performance of inserts, deletes and updates that
affect the column(s) used in the index,
* Speed up those queries that can use this index,
* Slightly increase the size of databases and backups,
* Have no effect on any other operation.
All this on the assumption that you're talking about a nonclustered index.
A clustered index has more impact. (But since building and dropping a
clustered index is quite costly, I don't think you'd consider this for
your temp index).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
i want to create index to speed up one operation (DELETE) and the drop the
index to resume.
any side effects or impacts on the db?On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
>hi,
>i want to create index to speed up one operation (DELETE) and the drop the
>index to resume.
>any side effects or impacts on the db?
>
Hi Mullin,
Creating the index will take some time and will also lock parts of your
data. Dropping the index doesn't take much time (unless it is a clustered
index).
However, why would you want to drop the index after the delete?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||because want to add an index on the third-party product for a temp
operation. don't know any impacts on that product if exist an index
persistently.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fkdoq0p5sb73abfmke0phv999kjaa2k4p9@.4ax.com...
> On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
> >hi,
> >
> >i want to create index to speed up one operation (DELETE) and the drop
the
> >index to resume.
> >
> >any side effects or impacts on the db?
> >
> Hi Mullin,
> Creating the index will take some time and will also lock parts of your
> data. Dropping the index doesn't take much time (unless it is a clustered
> index).
> However, why would you want to drop the index after the delete?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 1 Dec 2004 15:47:04 +0800, Mullin Yu wrote:
>because want to add an index on the third-party product for a temp
>operation. don't know any impacts on that product if exist an index
>persistently.
Hi Mullin,
An extra index will:
* Slightly decrease performance of inserts, deletes and updates that
affect the column(s) used in the index,
* Speed up those queries that can use this index,
* Slightly increase the size of databases and backups,
* Have no effect on any other operation.
All this on the assumption that you're talking about a nonclustered index.
A clustered index has more impact. (But since building and dropping a
clustered index is quite costly, I don't think you'd consider this for
your temp index).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
2012年2月11日星期六
ANSI_PADDING woes !
I've got a table that I've added a computed column to. That column is part
of an index. As soon as I attempt to insert a row I get the infamous:
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods
I've tried creating the table with ansi_padding on and off... and with the
session doing the insert on and off...nothing seems to work...what is
causing this?
Thanks for any insight...really stuck and need to get through this to lock
down some input issues...
Hi Tim
I think that the connection that you are trying to insert does not have the
correct ANSI_PADDING setting rather than anything else.
If Ansi_Padding is on when you create the index and insert everything is ok.
create database TESTDB
go
use TESTDB
gO
SET ANSI_PADDING ON
go
create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
ID2 )
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
insert into tST1 ( ID1, ID2 )
select 1, 3
go
select * from TST1
go
If it is off when you try to insert but on otherwise, you get your error
message
SET ANSI_PADDING Off
go
insert into tST1 ( ID1, ID2 )
select 2, 3
go
/*
Msg 1934, Level 16, State 1, Line 1
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods.
*/
If it was off when you create the table and index it will give an error
creating the index
use master
go
drop database TESTDB
go
create database TESTDB
go
use TESTDB
gO
SET ANSI_PADDING Off
go
create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
ID2 )
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
/*
Msg 1934, Level 16, State 1, Line 1
CREATE INDEX failed because the following SET options have incorrect
settings: 'ANSI_PADDING'. Verify that SET options are correct for use with
indexed views and/or indexes on computed columns and/or query notifications
and/or xml data type methods.
*/
If it is on when creating the index you can insert if ANSI_PADDING is on:
SET ANSI_PADDING On
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
insert into tST1 ( ID1, ID2 )
select 3, 3
go
But not if it is off
SET ANSI_PADDING Off
go
insert into tST1 ( ID1, ID2 )
select 4, 3
go
/*
Msg 1934, Level 16, State 1, Line 1
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods.
*/
-- Checking the user options when off and on
-- When off
SELECT @.@.OPTIONS
-- 5480
SET ANSI_PADDING ON
go
SELECT @.@.OPTIONS
-- 5496
-- 16 is the USER_OPTION value to set!!
HTH
John
"Tim Greenwood" wrote:
> I've got a table that I've added a computed column to. That column is part
> of an index. As soon as I attempt to insert a row I get the infamous:
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods
> I've tried creating the table with ansi_padding on and off... and with the
> session doing the insert on and off...nothing seems to work...what is
> causing this?
> Thanks for any insight...really stuck and need to get through this to lock
> down some input issues...
>
>
|||"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
> I've got a table that I've added a computed column to. That column is
> part of an index. As soon as I attempt to insert a row I get the
> infamous:
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods
> I've tried creating the table with ansi_padding on and off... and with the
> session doing the insert on and off...nothing seems to work...what is
> causing this?
> Thanks for any insight...really stuck and need to get through this to lock
> down some input issues...
What was the ANSI_PADDING setting when you created the table and when you
later created the computed column?
|||I stated in the original message I have created the table both ways...and
in each instance tried with it on /off for the session doing the insert.
"Mike C#" <xyz@.xyz.com> wrote in message
news:Ozq7VKxhIHA.5900@.TK2MSFTNGP02.phx.gbl...
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
> What was the ANSI_PADDING setting when you created the table and when you
> later created the computed column?
>
|||Hi
If you check my script out in the other reply it seems to be the session
where you do the insert.
John
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%239lAvkDiIHA.2416@.TK2MSFTNGP04.phx.gbl...
>I stated in the original message I have created the table both ways...and
>in each instance tried with it on /off for the session doing the insert.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:Ozq7VKxhIHA.5900@.TK2MSFTNGP02.phx.gbl...
>
|||"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%239lAvkDiIHA.2416@.TK2MSFTNGP04.phx.gbl...
>I stated in the original message I have created the table both ways...and
>in each instance tried with it on /off for the session doing the insert.
Ahh missed that part. So change it in your session/connection where you're
trying to perform the INSERT.
|||Yes the connection was it...thank you!! I wasn't aware that the connection
had properties different from the session itself...I mean if my connection
did not have ANSI_PADDING enabled and I entered SET ANSI_PADDING ON I would
have thought that would change everything for the existing
session/connection both.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5B951B45-CAEE-4FAB-90AB-4CC90BB024DB@.microsoft.com...[vbcol=seagreen]
> Hi Tim
> I think that the connection that you are trying to insert does not have
> the
> correct ANSI_PADDING setting rather than anything else.
> If Ansi_Padding is on when you create the index and insert everything is
> ok.
> create database TESTDB
> go
> use TESTDB
> gO
> SET ANSI_PADDING ON
> go
> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
> ID2 )
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> insert into tST1 ( ID1, ID2 )
> select 1, 3
> go
> select * from TST1
> go
> If it is off when you try to insert but on otherwise, you get your error
> message
> SET ANSI_PADDING Off
> go
> insert into tST1 ( ID1, ID2 )
> select 2, 3
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods.
> */
> If it was off when you create the table and index it will give an error
> creating the index
> use master
> go
> drop database TESTDB
> go
> create database TESTDB
> go
> use TESTDB
> gO
> SET ANSI_PADDING Off
> go
> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
> ID2 )
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> CREATE INDEX failed because the following SET options have incorrect
> settings: 'ANSI_PADDING'. Verify that SET options are correct for use with
> indexed views and/or indexes on computed columns and/or query
> notifications
> and/or xml data type methods.
> */
> If it is on when creating the index you can insert if ANSI_PADDING is on:
> SET ANSI_PADDING On
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> insert into tST1 ( ID1, ID2 )
> select 3, 3
> go
> But not if it is off
> SET ANSI_PADDING Off
> go
> insert into tST1 ( ID1, ID2 )
> select 4, 3
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods.
> */
> -- Checking the user options when off and on
> -- When off
> SELECT @.@.OPTIONS
> -- 5480
> SET ANSI_PADDING ON
> go
> SELECT @.@.OPTIONS
> -- 5496
> -- 16 is the USER_OPTION value to set!!
> HTH
> John
> "Tim Greenwood" wrote:
|||Hi Tim
The SET command would only set the ANSI_PADDING for the current connection
you would need to use sp_configure to set the USER_OPTIONS value, which will
change the properties for any subsequent new connection.
John
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:u7mcB1HiIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Yes the connection was it...thank you!! I wasn't aware that the
> connection had properties different from the session itself...I mean if my
> connection did not have ANSI_PADDING enabled and I entered SET
> ANSI_PADDING ON I would have thought that would change everything for the
> existing session/connection both.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:5B951B45-CAEE-4FAB-90AB-4CC90BB024DB@.microsoft.com...
>
of an index. As soon as I attempt to insert a row I get the infamous:
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods
I've tried creating the table with ansi_padding on and off... and with the
session doing the insert on and off...nothing seems to work...what is
causing this?
Thanks for any insight...really stuck and need to get through this to lock
down some input issues...
Hi Tim
I think that the connection that you are trying to insert does not have the
correct ANSI_PADDING setting rather than anything else.
If Ansi_Padding is on when you create the index and insert everything is ok.
create database TESTDB
go
use TESTDB
gO
SET ANSI_PADDING ON
go
create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
ID2 )
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
insert into tST1 ( ID1, ID2 )
select 1, 3
go
select * from TST1
go
If it is off when you try to insert but on otherwise, you get your error
message
SET ANSI_PADDING Off
go
insert into tST1 ( ID1, ID2 )
select 2, 3
go
/*
Msg 1934, Level 16, State 1, Line 1
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods.
*/
If it was off when you create the table and index it will give an error
creating the index
use master
go
drop database TESTDB
go
create database TESTDB
go
use TESTDB
gO
SET ANSI_PADDING Off
go
create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
ID2 )
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
/*
Msg 1934, Level 16, State 1, Line 1
CREATE INDEX failed because the following SET options have incorrect
settings: 'ANSI_PADDING'. Verify that SET options are correct for use with
indexed views and/or indexes on computed columns and/or query notifications
and/or xml data type methods.
*/
If it is on when creating the index you can insert if ANSI_PADDING is on:
SET ANSI_PADDING On
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
insert into tST1 ( ID1, ID2 )
select 3, 3
go
But not if it is off
SET ANSI_PADDING Off
go
insert into tST1 ( ID1, ID2 )
select 4, 3
go
/*
Msg 1934, Level 16, State 1, Line 1
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods.
*/
-- Checking the user options when off and on
-- When off
SELECT @.@.OPTIONS
-- 5480
SET ANSI_PADDING ON
go
SELECT @.@.OPTIONS
-- 5496
-- 16 is the USER_OPTION value to set!!
HTH
John
"Tim Greenwood" wrote:
> I've got a table that I've added a computed column to. That column is part
> of an index. As soon as I attempt to insert a row I get the infamous:
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods
> I've tried creating the table with ansi_padding on and off... and with the
> session doing the insert on and off...nothing seems to work...what is
> causing this?
> Thanks for any insight...really stuck and need to get through this to lock
> down some input issues...
>
>
|||"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
> I've got a table that I've added a computed column to. That column is
> part of an index. As soon as I attempt to insert a row I get the
> infamous:
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods
> I've tried creating the table with ansi_padding on and off... and with the
> session doing the insert on and off...nothing seems to work...what is
> causing this?
> Thanks for any insight...really stuck and need to get through this to lock
> down some input issues...
What was the ANSI_PADDING setting when you created the table and when you
later created the computed column?
|||I stated in the original message I have created the table both ways...and
in each instance tried with it on /off for the session doing the insert.
"Mike C#" <xyz@.xyz.com> wrote in message
news:Ozq7VKxhIHA.5900@.TK2MSFTNGP02.phx.gbl...
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
> What was the ANSI_PADDING setting when you created the table and when you
> later created the computed column?
>
|||Hi
If you check my script out in the other reply it seems to be the session
where you do the insert.
John
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%239lAvkDiIHA.2416@.TK2MSFTNGP04.phx.gbl...
>I stated in the original message I have created the table both ways...and
>in each instance tried with it on /off for the session doing the insert.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:Ozq7VKxhIHA.5900@.TK2MSFTNGP02.phx.gbl...
>
|||"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%239lAvkDiIHA.2416@.TK2MSFTNGP04.phx.gbl...
>I stated in the original message I have created the table both ways...and
>in each instance tried with it on /off for the session doing the insert.
Ahh missed that part. So change it in your session/connection where you're
trying to perform the INSERT.
|||Yes the connection was it...thank you!! I wasn't aware that the connection
had properties different from the session itself...I mean if my connection
did not have ANSI_PADDING enabled and I entered SET ANSI_PADDING ON I would
have thought that would change everything for the existing
session/connection both.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5B951B45-CAEE-4FAB-90AB-4CC90BB024DB@.microsoft.com...[vbcol=seagreen]
> Hi Tim
> I think that the connection that you are trying to insert does not have
> the
> correct ANSI_PADDING setting rather than anything else.
> If Ansi_Padding is on when you create the index and insert everything is
> ok.
> create database TESTDB
> go
> use TESTDB
> gO
> SET ANSI_PADDING ON
> go
> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
> ID2 )
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> insert into tST1 ( ID1, ID2 )
> select 1, 3
> go
> select * from TST1
> go
> If it is off when you try to insert but on otherwise, you get your error
> message
> SET ANSI_PADDING Off
> go
> insert into tST1 ( ID1, ID2 )
> select 2, 3
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods.
> */
> If it was off when you create the table and index it will give an error
> creating the index
> use master
> go
> drop database TESTDB
> go
> create database TESTDB
> go
> use TESTDB
> gO
> SET ANSI_PADDING Off
> go
> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
> ID2 )
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> CREATE INDEX failed because the following SET options have incorrect
> settings: 'ANSI_PADDING'. Verify that SET options are correct for use with
> indexed views and/or indexes on computed columns and/or query
> notifications
> and/or xml data type methods.
> */
> If it is on when creating the index you can insert if ANSI_PADDING is on:
> SET ANSI_PADDING On
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> insert into tST1 ( ID1, ID2 )
> select 3, 3
> go
> But not if it is off
> SET ANSI_PADDING Off
> go
> insert into tST1 ( ID1, ID2 )
> select 4, 3
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods.
> */
> -- Checking the user options when off and on
> -- When off
> SELECT @.@.OPTIONS
> -- 5480
> SET ANSI_PADDING ON
> go
> SELECT @.@.OPTIONS
> -- 5496
> -- 16 is the USER_OPTION value to set!!
> HTH
> John
> "Tim Greenwood" wrote:
|||Hi Tim
The SET command would only set the ANSI_PADDING for the current connection
you would need to use sp_configure to set the USER_OPTIONS value, which will
change the properties for any subsequent new connection.
John
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:u7mcB1HiIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Yes the connection was it...thank you!! I wasn't aware that the
> connection had properties different from the session itself...I mean if my
> connection did not have ANSI_PADDING enabled and I entered SET
> ANSI_PADDING ON I would have thought that would change everything for the
> existing session/connection both.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:5B951B45-CAEE-4FAB-90AB-4CC90BB024DB@.microsoft.com...
>
ANSI_PADDING woes !
I've got a table that I've added a computed column to. That column is part
of an index. As soon as I attempt to insert a row I get the infamous:
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods
I've tried creating the table with ansi_padding on and off... and with the
session doing the insert on and off...nothing seems to work...what is
causing this?
Thanks for any insight...really stuck and need to get through this to lock
down some input issues...Hi Tim
I think that the connection that you are trying to insert does not have the
correct ANSI_PADDING setting rather than anything else.
If Ansi_Padding is on when you create the index and insert everything is ok.
create database TESTDB
go
use TESTDB
gO
SET ANSI_PADDING ON
go
create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
ID2 )
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
insert into tST1 ( ID1, ID2 )
select 1, 3
go
select * from TST1
go
If it is off when you try to insert but on otherwise, you get your error
message
SET ANSI_PADDING Off
go
insert into tST1 ( ID1, ID2 )
select 2, 3
go
/*
Msg 1934, Level 16, State 1, Line 1
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods.
*/
If it was off when you create the table and index it will give an error
creating the index
use master
go
drop database TESTDB
go
create database TESTDB
go
use TESTDB
gO
SET ANSI_PADDING Off
go
create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
ID2 )
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
/*
Msg 1934, Level 16, State 1, Line 1
CREATE INDEX failed because the following SET options have incorrect
settings: 'ANSI_PADDING'. Verify that SET options are correct for use with
indexed views and/or indexes on computed columns and/or query notifications
and/or xml data type methods.
*/
If it is on when creating the index you can insert if ANSI_PADDING is on:
SET ANSI_PADDING On
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
insert into tST1 ( ID1, ID2 )
select 3, 3
go
But not if it is off
SET ANSI_PADDING Off
go
insert into tST1 ( ID1, ID2 )
select 4, 3
go
/*
Msg 1934, Level 16, State 1, Line 1
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods.
*/
-- Checking the user options when off and on
-- When off
SELECT @.@.OPTIONS
-- 5480
SET ANSI_PADDING ON
go
SELECT @.@.OPTIONS
-- 5496
-- 16 is the USER_OPTION value to set!!
HTH
John
"Tim Greenwood" wrote:
> I've got a table that I've added a computed column to. That column is part
> of an index. As soon as I attempt to insert a row I get the infamous:
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods
> I've tried creating the table with ansi_padding on and off... and with the
> session doing the insert on and off...nothing seems to work...what is
> causing this?
> Thanks for any insight...really stuck and need to get through this to lock
> down some input issues...
>
>|||"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
> I've got a table that I've added a computed column to. That column is
> part of an index. As soon as I attempt to insert a row I get the
> infamous:
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods
> I've tried creating the table with ansi_padding on and off... and with the
> session doing the insert on and off...nothing seems to work...what is
> causing this?
> Thanks for any insight...really stuck and need to get through this to lock
> down some input issues...
What was the ANSI_PADDING setting when you created the table and when you
later created the computed column?|||I stated in the original message I have created the table both ways...and
in each instance tried with it on /off for the session doing the insert.
"Mike C#" <xyz@.xyz.com> wrote in message
news:Ozq7VKxhIHA.5900@.TK2MSFTNGP02.phx.gbl...
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
>> I've got a table that I've added a computed column to. That column is
>> part of an index. As soon as I attempt to insert a row I get the
>> infamous:
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or xml data type methods
>> I've tried creating the table with ansi_padding on and off... and with
>> the session doing the insert on and off...nothing seems to work...what
>> is causing this?
>> Thanks for any insight...really stuck and need to get through this to
>> lock down some input issues...
> What was the ANSI_PADDING setting when you created the table and when you
> later created the computed column?
>|||Hi
If you check my script out in the other reply it seems to be the session
where you do the insert.
John
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%239lAvkDiIHA.2416@.TK2MSFTNGP04.phx.gbl...
>I stated in the original message I have created the table both ways...and
>in each instance tried with it on /off for the session doing the insert.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:Ozq7VKxhIHA.5900@.TK2MSFTNGP02.phx.gbl...
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
>> I've got a table that I've added a computed column to. That column is
>> part of an index. As soon as I attempt to insert a row I get the
>> infamous:
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or xml data type methods
>> I've tried creating the table with ansi_padding on and off... and with
>> the session doing the insert on and off...nothing seems to work...what
>> is causing this?
>> Thanks for any insight...really stuck and need to get through this to
>> lock down some input issues...
>> What was the ANSI_PADDING setting when you created the table and when you
>> later created the computed column?
>|||"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%239lAvkDiIHA.2416@.TK2MSFTNGP04.phx.gbl...
>I stated in the original message I have created the table both ways...and
>in each instance tried with it on /off for the session doing the insert.
Ahh missed that part. So change it in your session/connection where you're
trying to perform the INSERT.|||Yes the connection was it...thank you!! I wasn't aware that the connection
had properties different from the session itself...I mean if my connection
did not have ANSI_PADDING enabled and I entered SET ANSI_PADDING ON I would
have thought that would change everything for the existing
session/connection both.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5B951B45-CAEE-4FAB-90AB-4CC90BB024DB@.microsoft.com...
> Hi Tim
> I think that the connection that you are trying to insert does not have
> the
> correct ANSI_PADDING setting rather than anything else.
> If Ansi_Padding is on when you create the index and insert everything is
> ok.
> create database TESTDB
> go
> use TESTDB
> gO
> SET ANSI_PADDING ON
> go
> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
> ID2 )
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> insert into tST1 ( ID1, ID2 )
> select 1, 3
> go
> select * from TST1
> go
> If it is off when you try to insert but on otherwise, you get your error
> message
> SET ANSI_PADDING Off
> go
> insert into tST1 ( ID1, ID2 )
> select 2, 3
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods.
> */
> If it was off when you create the table and index it will give an error
> creating the index
> use master
> go
> drop database TESTDB
> go
> create database TESTDB
> go
> use TESTDB
> gO
> SET ANSI_PADDING Off
> go
> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
> ID2 )
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> CREATE INDEX failed because the following SET options have incorrect
> settings: 'ANSI_PADDING'. Verify that SET options are correct for use with
> indexed views and/or indexes on computed columns and/or query
> notifications
> and/or xml data type methods.
> */
> If it is on when creating the index you can insert if ANSI_PADDING is on:
> SET ANSI_PADDING On
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> insert into tST1 ( ID1, ID2 )
> select 3, 3
> go
> But not if it is off
> SET ANSI_PADDING Off
> go
> insert into tST1 ( ID1, ID2 )
> select 4, 3
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods.
> */
> -- Checking the user options when off and on
> -- When off
> SELECT @.@.OPTIONS
> -- 5480
> SET ANSI_PADDING ON
> go
> SELECT @.@.OPTIONS
> -- 5496
> -- 16 is the USER_OPTION value to set!!
> HTH
> John
> "Tim Greenwood" wrote:
>> I've got a table that I've added a computed column to. That column is
>> part
>> of an index. As soon as I attempt to insert a row I get the infamous:
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or
>> xml data type methods
>> I've tried creating the table with ansi_padding on and off... and with
>> the
>> session doing the insert on and off...nothing seems to work...what is
>> causing this?
>> Thanks for any insight...really stuck and need to get through this to
>> lock
>> down some input issues...
>>|||Hi Tim
The SET command would only set the ANSI_PADDING for the current connection
you would need to use sp_configure to set the USER_OPTIONS value, which will
change the properties for any subsequent new connection.
John
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:u7mcB1HiIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Yes the connection was it...thank you!! I wasn't aware that the
> connection had properties different from the session itself...I mean if my
> connection did not have ANSI_PADDING enabled and I entered SET
> ANSI_PADDING ON I would have thought that would change everything for the
> existing session/connection both.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:5B951B45-CAEE-4FAB-90AB-4CC90BB024DB@.microsoft.com...
>> Hi Tim
>> I think that the connection that you are trying to insert does not have
>> the
>> correct ANSI_PADDING setting rather than anything else.
>> If Ansi_Padding is on when you create the index and insert everything is
>> ok.
>> create database TESTDB
>> go
>> use TESTDB
>> gO
>> SET ANSI_PADDING ON
>> go
>> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1
>> *
>> ID2 )
>> go
>> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
>> go
>> insert into tST1 ( ID1, ID2 )
>> select 1, 3
>> go
>> select * from TST1
>> go
>> If it is off when you try to insert but on otherwise, you get your error
>> message
>> SET ANSI_PADDING Off
>> go
>> insert into tST1 ( ID1, ID2 )
>> select 2, 3
>> go
>> /*
>> Msg 1934, Level 16, State 1, Line 1
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or
>> xml data type methods.
>> */
>> If it was off when you create the table and index it will give an error
>> creating the index
>> use master
>> go
>> drop database TESTDB
>> go
>> create database TESTDB
>> go
>> use TESTDB
>> gO
>> SET ANSI_PADDING Off
>> go
>> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1
>> *
>> ID2 )
>> go
>> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
>> go
>> /*
>> Msg 1934, Level 16, State 1, Line 1
>> CREATE INDEX failed because the following SET options have incorrect
>> settings: 'ANSI_PADDING'. Verify that SET options are correct for use
>> with
>> indexed views and/or indexes on computed columns and/or query
>> notifications
>> and/or xml data type methods.
>> */
>> If it is on when creating the index you can insert if ANSI_PADDING is on:
>> SET ANSI_PADDING On
>> go
>> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
>> go
>> insert into tST1 ( ID1, ID2 )
>> select 3, 3
>> go
>> But not if it is off
>> SET ANSI_PADDING Off
>> go
>> insert into tST1 ( ID1, ID2 )
>> select 4, 3
>> go
>> /*
>> Msg 1934, Level 16, State 1, Line 1
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or
>> xml data type methods.
>> */
>> -- Checking the user options when off and on
>> -- When off
>> SELECT @.@.OPTIONS
>> -- 5480
>> SET ANSI_PADDING ON
>> go
>> SELECT @.@.OPTIONS
>> -- 5496
>> -- 16 is the USER_OPTION value to set!!
>> HTH
>> John
>> "Tim Greenwood" wrote:
>>
>> I've got a table that I've added a computed column to. That column is
>> part
>> of an index. As soon as I attempt to insert a row I get the infamous:
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or
>> xml data type methods
>> I've tried creating the table with ansi_padding on and off... and with
>> the
>> session doing the insert on and off...nothing seems to work...what is
>> causing this?
>> Thanks for any insight...really stuck and need to get through this to
>> lock
>> down some input issues...
>>
>
of an index. As soon as I attempt to insert a row I get the infamous:
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods
I've tried creating the table with ansi_padding on and off... and with the
session doing the insert on and off...nothing seems to work...what is
causing this?
Thanks for any insight...really stuck and need to get through this to lock
down some input issues...Hi Tim
I think that the connection that you are trying to insert does not have the
correct ANSI_PADDING setting rather than anything else.
If Ansi_Padding is on when you create the index and insert everything is ok.
create database TESTDB
go
use TESTDB
gO
SET ANSI_PADDING ON
go
create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
ID2 )
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
insert into tST1 ( ID1, ID2 )
select 1, 3
go
select * from TST1
go
If it is off when you try to insert but on otherwise, you get your error
message
SET ANSI_PADDING Off
go
insert into tST1 ( ID1, ID2 )
select 2, 3
go
/*
Msg 1934, Level 16, State 1, Line 1
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods.
*/
If it was off when you create the table and index it will give an error
creating the index
use master
go
drop database TESTDB
go
create database TESTDB
go
use TESTDB
gO
SET ANSI_PADDING Off
go
create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
ID2 )
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
/*
Msg 1934, Level 16, State 1, Line 1
CREATE INDEX failed because the following SET options have incorrect
settings: 'ANSI_PADDING'. Verify that SET options are correct for use with
indexed views and/or indexes on computed columns and/or query notifications
and/or xml data type methods.
*/
If it is on when creating the index you can insert if ANSI_PADDING is on:
SET ANSI_PADDING On
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
insert into tST1 ( ID1, ID2 )
select 3, 3
go
But not if it is off
SET ANSI_PADDING Off
go
insert into tST1 ( ID1, ID2 )
select 4, 3
go
/*
Msg 1934, Level 16, State 1, Line 1
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods.
*/
-- Checking the user options when off and on
-- When off
SELECT @.@.OPTIONS
-- 5480
SET ANSI_PADDING ON
go
SELECT @.@.OPTIONS
-- 5496
-- 16 is the USER_OPTION value to set!!
HTH
John
"Tim Greenwood" wrote:
> I've got a table that I've added a computed column to. That column is part
> of an index. As soon as I attempt to insert a row I get the infamous:
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods
> I've tried creating the table with ansi_padding on and off... and with the
> session doing the insert on and off...nothing seems to work...what is
> causing this?
> Thanks for any insight...really stuck and need to get through this to lock
> down some input issues...
>
>|||"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
> I've got a table that I've added a computed column to. That column is
> part of an index. As soon as I attempt to insert a row I get the
> infamous:
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods
> I've tried creating the table with ansi_padding on and off... and with the
> session doing the insert on and off...nothing seems to work...what is
> causing this?
> Thanks for any insight...really stuck and need to get through this to lock
> down some input issues...
What was the ANSI_PADDING setting when you created the table and when you
later created the computed column?|||I stated in the original message I have created the table both ways...and
in each instance tried with it on /off for the session doing the insert.
"Mike C#" <xyz@.xyz.com> wrote in message
news:Ozq7VKxhIHA.5900@.TK2MSFTNGP02.phx.gbl...
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
>> I've got a table that I've added a computed column to. That column is
>> part of an index. As soon as I attempt to insert a row I get the
>> infamous:
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or xml data type methods
>> I've tried creating the table with ansi_padding on and off... and with
>> the session doing the insert on and off...nothing seems to work...what
>> is causing this?
>> Thanks for any insight...really stuck and need to get through this to
>> lock down some input issues...
> What was the ANSI_PADDING setting when you created the table and when you
> later created the computed column?
>|||Hi
If you check my script out in the other reply it seems to be the session
where you do the insert.
John
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%239lAvkDiIHA.2416@.TK2MSFTNGP04.phx.gbl...
>I stated in the original message I have created the table both ways...and
>in each instance tried with it on /off for the session doing the insert.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:Ozq7VKxhIHA.5900@.TK2MSFTNGP02.phx.gbl...
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
>> I've got a table that I've added a computed column to. That column is
>> part of an index. As soon as I attempt to insert a row I get the
>> infamous:
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or xml data type methods
>> I've tried creating the table with ansi_padding on and off... and with
>> the session doing the insert on and off...nothing seems to work...what
>> is causing this?
>> Thanks for any insight...really stuck and need to get through this to
>> lock down some input issues...
>> What was the ANSI_PADDING setting when you created the table and when you
>> later created the computed column?
>|||"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%239lAvkDiIHA.2416@.TK2MSFTNGP04.phx.gbl...
>I stated in the original message I have created the table both ways...and
>in each instance tried with it on /off for the session doing the insert.
Ahh missed that part. So change it in your session/connection where you're
trying to perform the INSERT.|||Yes the connection was it...thank you!! I wasn't aware that the connection
had properties different from the session itself...I mean if my connection
did not have ANSI_PADDING enabled and I entered SET ANSI_PADDING ON I would
have thought that would change everything for the existing
session/connection both.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5B951B45-CAEE-4FAB-90AB-4CC90BB024DB@.microsoft.com...
> Hi Tim
> I think that the connection that you are trying to insert does not have
> the
> correct ANSI_PADDING setting rather than anything else.
> If Ansi_Padding is on when you create the index and insert everything is
> ok.
> create database TESTDB
> go
> use TESTDB
> gO
> SET ANSI_PADDING ON
> go
> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
> ID2 )
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> insert into tST1 ( ID1, ID2 )
> select 1, 3
> go
> select * from TST1
> go
> If it is off when you try to insert but on otherwise, you get your error
> message
> SET ANSI_PADDING Off
> go
> insert into tST1 ( ID1, ID2 )
> select 2, 3
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods.
> */
> If it was off when you create the table and index it will give an error
> creating the index
> use master
> go
> drop database TESTDB
> go
> create database TESTDB
> go
> use TESTDB
> gO
> SET ANSI_PADDING Off
> go
> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
> ID2 )
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> CREATE INDEX failed because the following SET options have incorrect
> settings: 'ANSI_PADDING'. Verify that SET options are correct for use with
> indexed views and/or indexes on computed columns and/or query
> notifications
> and/or xml data type methods.
> */
> If it is on when creating the index you can insert if ANSI_PADDING is on:
> SET ANSI_PADDING On
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> insert into tST1 ( ID1, ID2 )
> select 3, 3
> go
> But not if it is off
> SET ANSI_PADDING Off
> go
> insert into tST1 ( ID1, ID2 )
> select 4, 3
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods.
> */
> -- Checking the user options when off and on
> -- When off
> SELECT @.@.OPTIONS
> -- 5480
> SET ANSI_PADDING ON
> go
> SELECT @.@.OPTIONS
> -- 5496
> -- 16 is the USER_OPTION value to set!!
> HTH
> John
> "Tim Greenwood" wrote:
>> I've got a table that I've added a computed column to. That column is
>> part
>> of an index. As soon as I attempt to insert a row I get the infamous:
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or
>> xml data type methods
>> I've tried creating the table with ansi_padding on and off... and with
>> the
>> session doing the insert on and off...nothing seems to work...what is
>> causing this?
>> Thanks for any insight...really stuck and need to get through this to
>> lock
>> down some input issues...
>>|||Hi Tim
The SET command would only set the ANSI_PADDING for the current connection
you would need to use sp_configure to set the USER_OPTIONS value, which will
change the properties for any subsequent new connection.
John
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:u7mcB1HiIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Yes the connection was it...thank you!! I wasn't aware that the
> connection had properties different from the session itself...I mean if my
> connection did not have ANSI_PADDING enabled and I entered SET
> ANSI_PADDING ON I would have thought that would change everything for the
> existing session/connection both.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:5B951B45-CAEE-4FAB-90AB-4CC90BB024DB@.microsoft.com...
>> Hi Tim
>> I think that the connection that you are trying to insert does not have
>> the
>> correct ANSI_PADDING setting rather than anything else.
>> If Ansi_Padding is on when you create the index and insert everything is
>> ok.
>> create database TESTDB
>> go
>> use TESTDB
>> gO
>> SET ANSI_PADDING ON
>> go
>> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1
>> *
>> ID2 )
>> go
>> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
>> go
>> insert into tST1 ( ID1, ID2 )
>> select 1, 3
>> go
>> select * from TST1
>> go
>> If it is off when you try to insert but on otherwise, you get your error
>> message
>> SET ANSI_PADDING Off
>> go
>> insert into tST1 ( ID1, ID2 )
>> select 2, 3
>> go
>> /*
>> Msg 1934, Level 16, State 1, Line 1
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or
>> xml data type methods.
>> */
>> If it was off when you create the table and index it will give an error
>> creating the index
>> use master
>> go
>> drop database TESTDB
>> go
>> create database TESTDB
>> go
>> use TESTDB
>> gO
>> SET ANSI_PADDING Off
>> go
>> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1
>> *
>> ID2 )
>> go
>> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
>> go
>> /*
>> Msg 1934, Level 16, State 1, Line 1
>> CREATE INDEX failed because the following SET options have incorrect
>> settings: 'ANSI_PADDING'. Verify that SET options are correct for use
>> with
>> indexed views and/or indexes on computed columns and/or query
>> notifications
>> and/or xml data type methods.
>> */
>> If it is on when creating the index you can insert if ANSI_PADDING is on:
>> SET ANSI_PADDING On
>> go
>> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
>> go
>> insert into tST1 ( ID1, ID2 )
>> select 3, 3
>> go
>> But not if it is off
>> SET ANSI_PADDING Off
>> go
>> insert into tST1 ( ID1, ID2 )
>> select 4, 3
>> go
>> /*
>> Msg 1934, Level 16, State 1, Line 1
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or
>> xml data type methods.
>> */
>> -- Checking the user options when off and on
>> -- When off
>> SELECT @.@.OPTIONS
>> -- 5480
>> SET ANSI_PADDING ON
>> go
>> SELECT @.@.OPTIONS
>> -- 5496
>> -- 16 is the USER_OPTION value to set!!
>> HTH
>> John
>> "Tim Greenwood" wrote:
>>
>> I've got a table that I've added a computed column to. That column is
>> part
>> of an index. As soon as I attempt to insert a row I get the infamous:
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or
>> xml data type methods
>> I've tried creating the table with ansi_padding on and off... and with
>> the
>> session doing the insert on and off...nothing seems to work...what is
>> causing this?
>> Thanks for any insight...really stuck and need to get through this to
>> lock
>> down some input issues...
>>
>
订阅：
博文 (Atom)