Alter table add column ошибка

I’m trying to add a column to one of of my database tables, but there is a syntax error
and I can’t seem to find the problem…

My current database table looks like this:

component   +  tag_id  +  item_id
------------|----------|-----------
com_content |    23    |    2642
com_content |    26    |    3481
com_content |    35    |    1868
com_content |    85    |    5827
com_content |    89    |    7882

I want it to look like this, where ‘id’ is auto increment and all columns part of the primary key

 id  +  component   +  tag_id  +  item_id
-----|--------------|----------|-----------
  1  |  com_content |    23    |    2642
  2  |  com_content |    26    |    3481
  3  |  com_content |    35    |    1868
  4  |  com_content |    85    |    5827
  5  |  com_content |    89    |    7882

This is my query:

DROP PRIMARY KEY
ALTER TABLE gitags_items
ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST
PRIMARY KEY (id,component,tag_id,item_id)

However I’m getting this error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY KEY ALTER TABLE gitags_items ADD COLUMN id INT NOT NULL AUTO_INC' at line 1

Any help/pointers would be much appreciated

Источник

According to Postgres docs the correct syntax is:

ALTER TABLE <your_table>
    ADD COLUMN <column name> <column type>,
    ADD COLUMN <column name> <column type>,
    ADD COLUMN <column name> <column type>;

Have a look at Postgres tutorials on PostgreSQL ADD COLUMN: Add One Or More Columns To a Table.

I’ve set up a minimal example that reproduces your error:

CREATE TABLE newtable (id int);

ALTER TABLE newtable
    ADD COLUMNS f1 float8, f2 int;

ERROR: syntax error at or near «float8»
LINE 2: ADD COLUMNS f1 float8, f2 int; ^

ALTER TABLE newtable
    ADD COLUMN f1 float8, f2 int, f3 text;

ERROR: syntax error at or near «f2»
LINE 2: ADD COLUMN f1 float8, f2 int, f3 text;

Using the correct syntax:

ALTER TABLE newtable
    ADD COLUMN f1 float8,
    ADD COLUMN f2 int,
    ADD COLUMN f3 text;
✓

db<>fiddle here

Источник

6 August 2019

Phil Factor explains the problems you might encounter when adding a non-nullable column to an existing table or altering a column that contains NULL values to be non-nullable. He demos a migration script that can deploy such changes safely. You might also learn that in an archaic form of the Scots language, used in Cumberland, the number 17 is «tiny bumfit»; I think the tiny bumfit bus goes from Penrith to Carlisle.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

It is a common ritual when designing a database to add or remove NULL constraints, but there are a couple of problems that can cause you grief when you are making changes to already-populated tables. This can happen when you try to add a new column that can’t accept NULL values, or to change an existing, nullable column into a NOT NULL column. SQL Prompt will warn you (EI028) if it detects code that will attempt to add a NOT NULL column to an existing table, without specifying a default value.

I’ll demonstrate these problems and then show you how to develop build scripts that apply these sorts of alterations. I’ll show how these can work regardless of whether you’re building a new version of the table from scratch, with the alterations, or if you need to update an existing table so that it incorporates these changes.

Adding a NOT NULL column to a populated table

We have a table, CountingWords, in which we record the words used to count, in Old Welsh. We have ambitions to count all the way up to 20, but currently only know how to count to 10.

/* we create a table. Just for our example, we create the words

used to count from one to ten in old Welsh.

(Na, nid wyf yn siaradwr Cymraeg ond rwy’n hoffi gwneud

rhywfaint o ymchwil ieithyddol gyda’r nos)

I’d like to do from eleven to twenty as well eventually

so I’ll add them and leave them NULL.

Here is the initial build script, with guard clauses

of course. We’ll re-create every time it is run.

With a few modifications we can make it so it only runs once

which is safer if you have reckless colleagues in your shop.

IF Object_Id(‘dbo.CountingWords’) IS NOT NULL DROP TABLE dbo.CountingWords;

—we script version 1 of our table of counting words

CREATE TABLE dbo.CountingWords

(

TheValue INT NOT NULL,

Word NVARCHAR(30) NULL,

CONSTRAINT CountingWordsPK PRIMARY KEY (TheValue)

);

INSERT INTO dbo.CountingWords (TheValue, Word)

VALUES

(1, ‘Un’), (2, ‘Dau’), (3, ‘Tri’), (4, ‘Pedwar’),

(5, ‘Pump’), (6, ‘Chwech’), (7, ‘Saith’), (8, ‘Wyth’),

(9, ‘Naw’), (10, ‘Deg’), (11, NULL), (12, NULL),

(13, NULL), (14, NULL), (15, NULL), (16, NULL),

(17, NULL), (18, NULL), (19, NULL), (20, NULL);

Listing 1: Un, Dau, Tri – version 1 of the CountingWords table

Having released the first version of this table, we realize quickly that we really should have recorded the name of the language, so we alter the design of the table to add a TheLanguage column, which cannot accept NULLs.

ALTER TABLE dbo.CountingWords ADD TheLanguage NVARCHAR(100) NOT NULL;

Immediately SQL Prompt warns us of the danger:

If we ignore SQL Prompt’s warnings and execute this, we will get an error.

Msg 4901, Level 16, State 1, Line 34
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'TheLanguage' cannot be added to non-empty table 'CountingWords' because it does not satisfy these conditions.

The error message is explicit, and it is easily fixed by defining a DEFAULT constraint so that SQL Server can insert a default value for this new column, for each row.

ALTER TABLE dbo.CountingWords ADD TheLanguage NVARCHAR(100) NOT NULL

DEFAULT ‘Old Welsh’;

Listing 2: Specifying a default when adding a NOT NULL column

Put simply, if a column is being added, and NULL values aren’t allowed, then you must provide a value to put into every row. As our table only had one language right now, ‘old welsh’, that wasn’t too hard.

Of course, we’ll want to record how to count in other languages too, such as Manx, Cornish or Cumbrian, so to enforce some data integrity, we need instead to create a parent table called Location that defines each of the languages, and where they were first recorded.

Let’s get that out the way. For this table, we can’t just drop and recreate the table without data-loss, and we’ll get an error anyway once our CountingWords table references this table via a FOREIGN KEY constraint. We need to take precautionary steps. I’ll use a simple, but rather strange, technique that ensures there is no damage, in terms of data loss, if the code is re-run. This script is obliged to run as several batches because CREATE TABLE statements must be at the start of a batch, and it is hard to execute code conditionally across batches.

IF Object_Id(‘dbo.Location’) IS NOT NULL SET NOEXEC ON;

—cunning way of only executing a section

—of code on a condition. until the next SET NOEXEC OFF

—we script version 1 of our table of counting words

—sadly the create table statement has to be at the start of a batch

CREATE TABLE dbo.Location

(

TheLanguage NVARCHAR(30) NOT NULL,

Description VARCHAR(100) NOT NULL DEFAULT »,

CONSTRAINT LanguageKey PRIMARY KEY (TheLanguage)

);

—now we insert the row we need for our existing data

INSERT INTO dbo.Location (TheLanguage) VALUES (‘Old Welsh’);

SET NOEXEC OFF;

Listing 3: Version 1 of the Location table

Of course, now we also need to modify the CountingWords table so that its TheLanguage column is a FOREIGN KEY, referencing the new Location table, but we’ll deal with that a little later.

Altering a nullable column to make it non-nullable

Quickly, we decide that allowing NULL values in TheWord column of CountingWords was a design mistake that we want to fix. We already learned that, if the table contains data, SQL Server won’t let us make a column non-nullable unless we provide a default value for it, in this case just a blank string.

ALTER TABLE CountingWords ADD CONSTRAINT WordConstraint DEFAULT » FOR Word;

ALTER TABLE CountingWords ALTER COLUMN Word NVARCHAR(30) NOT NULL;

Msg 515, Level 16, State 2, Line 58
Cannot insert the value NULL into column 'Word', table 'PhilFactor.dbo.CountingWords'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

Listing 4: Failed attempt to make the Word column NOT NULL

Aiee! We still can’t make column non-nullable, even though we’ve told SQL Server what to insert for NULL columns! First, we must remove any existing NULLs explicitly, by updating all the rows with the default value:

UPDATE CountingWords SET Word = DEFAULT WHERE Word IS NULL;

ALTER TABLE CountingWords ALTER COLUMN Word NVARCHAR(30) NOT NULL;

Listing 5: Updating existing rows with the default value before making a column NOT NULL

So, that worked fine.

Rolling out all the changes

It’s time to roll out all these changes to people who only have version 1 of the design, where there was no Location table, and where CountingWords had no TheLanguage column and a nullable Word column.

Rolling out the new Location table, since it’s the first version of that table, is no real problem (see Listing 3). However, rolling out the changes to the new CountingWords table requires adding a TheLangauge column, which does not allow NULLs, and changing the Word column to be NOT NULL, in both cases avoiding the problems we already discussed.

We also want our migration script to work regardless of whether we’re updating an existing v1 of CountingWords, or we need to build v2 of the table from scratch. Also, we don’t want the script to cause any harm or trigger an error if it’s accidentally re-run.

As a bonus, in either case, we’ll also need to make the TheLangauge column in CountingWords a FOREIGN KEY that auto-updates in response to updates or deletes on the parent key, as well as alter the PRIMARY KEY. As a final step, we’ll add in the Old Welsh words for 11-20, which we previously didn’t know.

Here is the migration script that will either migrate CountingWords from v1 to v2 or else create v2 from scratch, and which will cause no harm if accidentally re-run. Before you try it, either drop the CountingWords table, or rerun Listing 1 to reestablish v1 of the table.

—we now script version 2

IF Object_Id(‘dbo.CountingWords’) IS NULL

BEGIN

—we script version 2 of our table of counting words if it

—doesn’t already exist

CREATE TABLE dbo.CountingWords

(

TheValue INT NOT NULL,

Word NVARCHAR(30) NOT NULL CONSTRAINT WordConstraint DEFAULT »,

TheLanguage NVARCHAR(30) NOT NULL

CONSTRAINT LanguageConstraint REFERENCES dbo.Location(TheLanguage)

ON DELETE CASCADE

ON UPDATE CASCADE

CONSTRAINT CountingWordsPK PRIMARY KEY(TheValue, TheLanguage)

);

END;

ELSE /* else we need to add a column and change the primary key

constraint */

BEGIN

IF NOT EXISTS — only run if the column does not exist

(

SELECT * FROM sys.columns

WHERE name LIKE ‘TheLanguage’

AND object_id = Object_Id(‘dbo.CountingWords’)

)

BEGIN

— first we need to add the language column

ALTER TABLE CountingWords ADD TheLanguage NVARCHAR(30) NOT NULL

DEFAULT ‘Old Welsh’ CONSTRAINT LanguageConstraint

REFERENCES dbo.Location(TheLanguage)

ON DELETE CASCADE

ON UPDATE CASCADE ;

END

—now we need to alter the primary key

ALTER TABLE CountingWords DROP CONSTRAINT CountingWordsPK;

ALTER TABLE CountingWords —and add the new version

ADD CONSTRAINT CountingWordsPK

PRIMARY KEY(TheValue, TheLanguage);

IF NOT EXISTS — do we need to add the default and remove the nulls?

(SELECT * FROM sys.default_constraints

WHERE name LIKE ‘WordConstraint’)

BEGIN

ALTER TABLE CountingWords ADD CONSTRAINT WordConstraint DEFAULT » FOR Word;

/* You can specify NOT NULL in ALTER COLUMN only if the column contains no null values.

The null values must be updated to some value before the ALTER COLUMN NOT NULL is allowed,*/

UPDATE CountingWords SET Word = DEFAULT WHERE Word IS NULL;

END;

IF NOT EXISTS —now finally we can make it not null

(SELECT * FROM sys.columns

WHERE name LIKE ‘word’ AND is_nullable = 0)

ALTER TABLE CountingWords ALTER COLUMN Word NVARCHAR(30) NOT NULL;

END;

IF EXISTS —do we need to add in the welsh words we didn’t know

(SELECT * FROM dbo.CountingWords

WHERE TheLanguage LIKE ‘Old Welsh’ AND word LIKE »

)

—yes we need to add those words to replace those pesky blanks

UPDATE CountingWords

SET Word = welsh.word

FROM CountingWords AS cw

INNER JOIN

(

VALUES (‘Un ar ddeg’, 11), (‘Deuddeg’, 12), (‘Tri ar ddeg’, 13),

(‘Pedwar ar ddeg’, 14), (‘Pymtheg’, 15), (‘Un ar bymtheg’, 16),

(‘Dau ar bymtheg’, 17), (‘Deunaw’, 18), (‘Pedwar ar bymtheg’, 19),

(‘Ugain’, 20)

) AS welsh (word, meaning)

ON welsh.meaning = cw.TheValue;

Listing 6: A safe migration script for v2 of CountingWords

If it wasn’t for trying to ensure that the migration script worked in all circumstances, it would have been a lot simpler. I just hate build scripts that can only be run in particular circumstances.

Let’s now test it out by adding the counting words from one to twenty in a different language/region:

DECLARE @Language NVARCHAR(30) = ‘West Cumbrian’;

IF NOT EXISTS

(SELECT * FROM dbo.Location

WHERE TheLanguage LIKE @Language

) INSERT INTO dbo.Location (TheLanguage)

VALUES (@Language);

/* and now we can add in any other ways of counting up to

twenty that we want */

IF NOT EXISTS (SELECT * FROM CountingWords WHERE Thelanguage LIKE @Language)

INSERT INTO CountingWords (TheValue, Word, TheLanguage)

SELECT TheValue, word, @Language

FROM

(

VALUES (‘yan’, 1), (‘tyan’, 2), (‘tethera’, 3), (‘methera’, 4),

(‘pimp’, 5), (‘sethera’, 6), (‘lethera’, 7), (‘hovera’, 8),

(‘dovera’, 9), (‘dick’, 10), (‘yan-a-dick’, 11), (‘tyan-a-dick’, 12),

(‘tethera-dick’, 13), (‘nethera-dick’, 14), (‘bumfit’, 15),

(‘yan-a-bumfit’, 16), (‘tyan-a-bumfi t’, 17), (‘tithera-bumfit’, 18),

(‘methera-bumfit’, 19), (‘giggot’, 20)

) AS f (word, TheValue);

Listing 7: Yan, Tyan, Tethera – counting to 20 in West Cumbria

Now I’ve changed my mind. It shouldn’t be called ‘Old Welsh’, but ‘Archaic Welsh’. What a shame, but then we can now test out our foreign key constraint.

UPDATE dbo.Location SET TheLanguage = ‘Archaic Welsh’ WHERE TheLanguage LIKE ‘Old Welsh’

SELECT * FROM location

SELECT * FROM CountingWords

Listing 8: Cascading updates after changing the language

As if by magic, all the references have changed. I now have a database I can use!

I’ve provided a FillCountingWordsTable script, containing a full set of counting words for a variety of 47 recorded locations and languages, that you can use for more extensive testing. Apologies to Stateside friends and relatives that I left out the several Indian tribes who used the same counting rhymes. It turned out on investigation that they’d been taught knitting by the British colonists, and they’d thought that the words used to count the stitches were part of the magic.

Conclusion

We’ve just set up a rather elaborate demonstration of how to avoid some of the problems of altering tables that are already populated with data. In this example, the problems involve the use of NULL values, and happen when you try to add a new column that can’t accept NULL values, or to change an existing, nullable column into a NOT NULL column, when there are existing NULL values in the column. In the first case, you simply add a DEFAULT constraint to the column first, with a value that isn’t NULL, and in the second case you remove the NULL values first by updating the table.

These are techniques for making changes to existing tables, which is why I elaborated the demonstration to illustrate how to go about doing both of these operations as part of a resilient script that can be run regardless of whether it is a fresh build or a migration, and that can be re-run without any detrimental effects.

Tools in this post

Bulk Formatting of the SQL Server SQL Files

How to apply SQL formatting styles as part of an automated process, using the SQL Prompt command line formatter, with examples of bulk applying styles from the command prompt, PowerShell or a DOS batch.

Article

SQL Prompt Tip: how to control when the suggestion box pops up

By default, SQL Prompt shows code auto-completion suggestions automatically, and continuously. Phil Factor shows how to control this behavior, for the times when you need it to be a little less intrusive, such as when working through more intricate coding problems that require careful thought.

Webinar

More Gems from the SQL Prompt Treasure Chest

Join Microsoft MVP Grant Fritchey and Owen Standage from the Redgate SQL Prompt Development Team to discover even more of the features you may not be using and become the captain of your SQL Prompt ship.

Источник

Describe the bug
ALTER TABLE ADD COLUMN query returns error when the column already exists in the table even with keyword If Not Exists.

How to reproduce

Which ClickHouse server version to use
19.3.5
Queries to run that lead to unexpected result
ALTER TABLE "test" ADD COLUMN IF NOT EXISTS "foo1" String, ADD COLUMN IF NOT EXISTS "foo2" String

Expected behavior
The query should return success if column foo1 exists when expression IF NOT EXISTS is available.

Error message and/or stacktrace
Here is an example stack trace with the column name and table name mocked out.

/usr/bin/clickhouse-server(StackTrace::StackTrace()+0x16) [0x6f12636]
/usr/bin/clickhouse-server(DB::Exception::Exception(std::__cxx11::basic_string<char, std::char_traits, std::allocator > const&, int)+0x22) [0x3399b02]
/usr/bin/clickhouse-server(DB::AlterCommand::apply(DB::ColumnsDescription&, DB::IndicesDescription&, std::shared_ptrDB::IAST&, std::shared_ptrDB::IAST&) const+0x11c7) [0x697aa57]
/usr/bin/clickhouse-server(DB::AlterCommands::apply(DB::ColumnsDescription&, DB::IndicesDescription&, std::shared_ptrDB::IAST&, std::shared_ptrDB::IAST&) const+0x1ad) [0x697aded]
/usr/bin/clickhouse-server(DB::MergeTreeData::checkAlter(DB::AlterCommands const&, DB::Context const&)+0x1c1) [0x65e15d1]
/usr/bin/clickhouse-server(DB::StorageMergeTree::alter(DB::AlterCommands const&, std::__cxx11::basic_string<char, std::char_traits, std::allocator > const&, std::__cxx11::basic_string<char, std::char_traits, std::al
locator > const&, DB::Context const&)+0x21a) [0x653075a]
/usr/bin/clickhouse-server(DB::InterpreterAlterQuery::execute()+0x52d) [0x693e2fd]
/usr/bin/clickhouse-server() [0x643beb3]
/usr/bin/clickhouse-server(DB::executeQuery(std::__cxx11::basic_string<char, std::char_traits, std::allocator > const&, DB::Context&, bool, DB::QueryProcessingStage::Enum, bool)+0x81) [0x643dd21]
/usr/bin/clickhouse-server(DB::TCPHandler::runImpl()+0x4a6) [0x33a9236]
/usr/bin/clickhouse-server(DB::TCPHandler::run()+0x2b) [0x33aa40b]
/usr/bin/clickhouse-server(Poco::Net::TCPServerConnection::start()+0xf) [0x704907f]
/usr/bin/clickhouse-server(Poco::Net::TCPServerDispatcher::run()+0x16a) [0x704945a]
/usr/bin/clickhouse-server(Poco::PooledThread::run()+0x77) [0x7125967]
/usr/bin/clickhouse-server(Poco::ThreadImpl::runnableEntry(void*)+0x38) [0x7121828]
/usr/bin/clickhouse-server() [0xacbf28f]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8064) [0x7f4c73b7f064]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7f4c731a762d]

Источник

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.

Источник