Ошибка alter table alter column - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

There are a variety of causes for the message shown in the title of this post. Manually creating statistics for a column is one such cause. This post shows how that works, and what you need to do to get around the error. Columns that have manually-created statistics attached cannot have their properties modified without first dropping the stats object – this is to ensure the stats object accurately reflects the content of the column. SQL Server returns an error message stating “ALTER TABLE ALTER COLUMN failed because one or more objects access this column.” I ran into this limitation recently when attempting to modify the datatype for a column from varchar to nvarchar. This database has auto create statistics disabled. As a result, manually creating statistics objects is critical to ensuring good query performance.

Have a failed column or three!

The Error Message

When attempting to modify a column that has a manually created statistics object attached, you receive the following “ALTER TABLE ALTER COLUMN failed” error message:

Msg 5074, Level 16, State 1, Line 36
The statistics '<name>' is dependent on column '<col>'.
Msg 4922, Level 16, State 9, Line 36
ALTER TABLE ALTER COLUMN <col> failed because one or more objects access this column.

Interestingly, if SQL Server has auto-created a stats object on a column, and you subsequently modify that column, you receive no such error. SQL Server silently drops the statistics object, and modifies the column. The auto-created stats object is not automatically recreated until a query is executed that needs the stats object. This difference in how auto-created stats and manually created stats are treated by the engine can make for some confusion.

Ads by Google, Paying the Rent:

The Script

Consider the following minimally complete and verifiable example code that can be used to reproduce the problem:

SET NOCOUNT ON;

USE master;

—Create a new, blank database for our test

IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = ‘test_stats_alter’)

BEGIN

ALTER DATABASE test_stats_alter SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE test_stats_alter;

END

CREATE DATABASE test_stats_alter;

ALTER DATABASE test_stats_alter SET AUTO_CREATE_STATISTICS OFF;

ALTER DATABASE test_stats_alter SET AUTO_UPDATE_STATISTICS OFF;

USE test_stats_alter;

CREATE TABLE dbo.stats_test

(

id int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED

, d varchar(30) NOT NULL

);

CREATE TABLE dbo.dates

(

d varchar(30) NOT NULL

)

—Insert a bunch of rows to allow the query optimizer to perform actual work.

INSERT INTO dbo.stats_test (d)

SELECT CONVERT(datetime, DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(2)), ‘1900-01-01T00:00:00’))

FROM sys.syscolumns sc1

CROSS JOIN sys.syscolumns sc2

INSERT INTO dbo.dates (d)

SELECT CONVERT(datetime, DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(2)), ‘1900-01-01T00:00:00’))

FROM sys.syscolumns sc1

—Manually create a stats object

CREATE STATISTICS stats_test_st1

ON dbo.stats_test(d)

WITH FULLSCAN, NORECOMPUTE;

—Attempt to alter the column with the manual stats object defined.

—This will fail with Msg 5074, Level 16, State 1, Line xx

ALTER TABLE dbo.stats_test

ALTER COLUMN d nvarchar(30) NOT NULL;

SQL Server returns this error:

Msg 5074, Level 16, State 1, Line 47
The statistics 'stats_test_st1' is dependent on column 'd'.
Msg 4922, Level 16, State 9, Line 47
ALTER TABLE ALTER COLUMN d failed because one or more objects access this column.

Let’s continue on:

—drop the stats object

DROP STATISTICS dbo.stats_test.stats_test_st1;

—Allow SQL Server to automatically create statistics

ALTER DATABASE test_stats_alter SET AUTO_CREATE_STATISTICS ON;

ALTER DATABASE test_stats_alter SET AUTO_UPDATE_STATISTICS ON;

—Coerce SQL Server into automatically creating a stats object.

—This is a complex enough query that SQL Server recognizes a

—stats object would be helpful for good performance.

SELECT st.id

, st.d

INTO dbo.stats_test_output

FROM dbo.stats_test st

LEFT JOIN dbo.dates d ON st.d = d.d

WHERE st.d > ‘2017-06-01T00:00:00’;

—See if SQL Server in fact created an auto-stats object on

—the column.

SELECT *

FROM sys.stats st

INNER JOIN sys.objects o ON st.object_id = o.object_id

WHERE o.name = ‘stats_test’;

╔════════════╦════════════════════════════════╦══════════════╗
║    name    ║              name              ║ auto_created ║
╠════════════╬════════════════════════════════╬══════════════╣
║ stats_test ║ PK__stats_te__3213E83FF58F8430 ║            0 ║
║ stats_test ║ _WA_Sys_00000002_21B6055D      ║            1 ║
╚════════════╩════════════════════════════════╩══════════════╝

—attempt to alter the table, which succeeds.

ALTER TABLE dbo.stats_test

ALTER COLUMN d nvarchar(30) NOT NULL;

—check to see if the auto-created stats object still exists

SELECT *

FROM sys.stats st

INNER JOIN sys.objects o ON st.object_id = o.object_id

WHERE o.name = ‘stats_test’;

The auto-created stats object has been silently dropped:

╔════════════╦════════════════════════════════╦══════════════╗
║    name    ║              name              ║ auto_created ║
╠════════════╬════════════════════════════════╬══════════════╣
║ stats_test ║ PK__stats_te__3213E83FF58F8430 ║            0 ║
╚════════════╩════════════════════════════════╩══════════════╝

In Summary

Manually adding statistics objects can be a blessing for performance, however you need to recognize the limitations this creates for future object modifications.

If you have auto_create_statistics turned off, you probably want to update your stats objects on a regular basis to ensure good performance. See my statistics update job for details about how to do that.

Read the other articles in our series on SQL Server Internals.

If you found this post useful, please
consider donating a small amount
to help keep the lights on and site running.

Источник

Remove From My Forums

Question
Hello Gurus,

I am trying to alter column data type length but its throwing me an error

ALTER TABLE AR

ALTER COLUMN ph
VARCHAR(25)

ERROR: The object ‘df_ar_PH’ is dependent on column ‘ph’.

ALTER TABLE ALTER COLUMN phone2 failed because one or more objects access this column.

Answers

you will need to drop and recreate the index.

once you drop the constraints and index , you can alter the column .

Then re create constraints and index.
- Edited by
  
  Wednesday, November 14, 2012 4:38 PM
- Proposed as answer by
  Kalman Toth
  Tuesday, November 20, 2012 3:32 PM
- Marked as answer by
  Iric Wen
  Thursday, November 22, 2012 9:04 AM
this might help :

http://stackoverflow.com/questions/5727155/drop-a-column-from-table-problem-sql-server-2008
- Proposed as answer by
  Kalman Toth
  Tuesday, November 20, 2012 3:32 PM
- Marked as answer by
  Iric Wen
  Thursday, November 22, 2012 9:04 AM

Источник

I have an existing DATETIME column in SQL Server 2016. I need to write a DDL script to change it to DATE type.

When I run the following script, I get an error message. Not sure how do I fix it…

ALTER TABLE [dbo].[TBL_MyTable] 
    DROP CONSTRAINT [PK__TBL__3F4E83EE204C47AA];

ALTER TABLE [dbo].[TBL_MyTable] 
    ALTER COLUMN myDate DATE;

ALTER TABLE [dbo].[TBL_MyTable] 
    ADD CONSTRAINT [PK__TBL__3F4E83EE204C47AA];

Error message:

Msg 5074, Level 16, State 1, Line 2
The object ‘PK__TBL__3F4E83EE204C47AA’ is dependent on column ‘myDate’.

Msg 4922, Level 16, State 9, Line 2
ALTER TABLE ALTER COLUMN myDate failed because one or more objects access this column.

Table definition:

CREATE TABLE "dbo"."TBL_MyTable"
(
     column1,
     myDate datetime NOT NULL,,
     column2,
     column3,

     CONSTRAINT PK__TBL__3F4E83EE204C47AA 
         PRIMARY KEY (column1, myDate, column2, column3)
)
GO

CREATE UNIQUE INDEX PK__TBL__3F4E83EE204C47AA 
    ON "dbo"."TBL_MyTable" (column1, myDate, column2, column3)
GO

Thanks.

Источник

Remove From My Forums

Question
Hi

when I’m upgrading table schema with alter statement

I’m getting error like this

ALTER TABLE ALTER COLUMN [access-id] failed because one or more objects access this column

can Anybody tell the solutiuon plz.

Thank u .

vizai

Answers

If you have any View /UDF With SchemaBinding on this Base table , you are not allowed to change or Drop columns or Table Object..

If you want to alter the table first you need to remove the SchemaBinding in all the views/UDF and change the column on Base table then recreate the Views/UDFs With SchemaBinding.

If you have any Indexed Views when you remove the SchemaBinding all the Index on the Views will be removed, so you have to create them too..

Источник

It is always annoying when we get a request to change the datatype of an existing column for many reason. The changes are due to business reason or bad design or anything. But, when we change to accommodate, we need to do lots of analysis to ascertain the impact of the change.

Here, we are going to explain a case, changing datatype size varchar (n) to varchar(max). This blog ONLY explains the context of changing the datatype not the impact of the change in terms of performance.


Create Table ColumnDatatypeChange (Col1 int,Col2 varchar(1))
Insert into ColumnDatatypeChange Select 1 , 'L'
create Index IX_ColumnDatatypeChange on ColumnDatatypeChange(Col1) include(Col2)
Alter Table ColumnDatatypeChange Alter column Col2 Varchar(MAX) -- This statement will fail

On executing the below snippet, we get the below error message. (Please note that an index is dependent on column)

Error Message:
Msg 5074, Level 16, State 1, Line 8
The index ‘IX_ColumnDatatypeChange’ is dependent on column ‘Col2’.
Msg 4922, Level 16, State 9, Line 8
ALTER TABLE ALTER COLUMN Col2 failed because one or more objects access this column.

However, when we do the change from MAX to 8000, it gets executed without any issue.


Create Table ColumnDatatypeChange (Col1 int,Col2 varchar(1))
Insert into ColumnDatatypeChange Select 1 , 'L'
create Index IX_ColumnDatatypeChange on ColumnDatatypeChange(Col1) include(Col2)
Alter Table ColumnDatatypeChange Alter column Col2 Varchar(8000) -- This statement will succeed

Reason:

The reason for this behavior is due to the change from non-max type to a max type. SQL Server considers this change as dropping a column and adding a new column. Internally, the non-max and max types do not share the same storage types (Row-Overflow vs. LOB allocation units). Hence, this change needs to update every row and move the data from dropped column to newly added column. However, if you see for non-max type change, they share the same storage (either in-row or in the row-overflow allocation unit), so no issues with in-place update, that mean, no need to drop the dependent(index in this case).

Resolution:

1. Drop the index and change the column size followed by adding the index back to the table


Drop Index IX_ColumnDatatypeChange on ColumnDatatypeChange
Alter Table ColumnDatatypeChange Alter column Col2 Varchar(MAX)
create Index IX_ColumnDatatypeChange on ColumnDatatypeChange(Col1) include(Col2)

2. Analyze and try to avoid the size to MAX (if possible)

Thanks for your read and looking for your feedback!!!

Источник

The Error Message

The Script

In Summary

Question

Answers

Question

Answers

Возможно, вам также будет интересно: