question

sakuraime avatar image
0 Votes"
sakuraime asked ErlandSommarskog commented

SQL Server 2019 Database scoped configuration

are there any good link / demo/ example for the following SQL Server 2019 Database


IDENTITY_CACHE
ISOLATE_SECURITY_POLICY_CARDINALITY
ACCELERATED_PLAN_FORCING
GLOBAL_TEMPORARY_TABLE_AUTO_DROP
VERBOSE_TRUNCATION_WARNINGS

thanks

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered sakuraime commented

Hi sakuraime,

For GLOBAL_TEMPORARY_TABLE_AUTO_DROP option, you can try to create a global temporary table and not use it. By default (GLOBAL_TEMPORARY_TABLE_AUTO_DROP is on), the global temporary table will be deleted automatically. If you create a global temporary table when GLOBAL_TEMPORARY_TABLE_AUTO_DROP is off, the global temporary table needs to be deleted using the DROP TABLE statement, or it will be deleted when the server is restarted.

Best Regards,
Amelia

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks and how frequent it will be deleted?

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

For the first, try this:

CREATE TABLE myidenttable(a int IDENTITY NOT NULL)
INSERT myidenttable DEFAULT VALUES

Next kill SQL Server through Task Manager and restart it. Insert a new row to the table. Notice the value. Then flip the setting of IDENTITY_CACHE and redo the exercise.

For the last, try:

CREATE TABLE myshortchar (a char(4))
INSERT myshortchar (a) VALUES ('Too long')

Then flip the switch for VERBOSE_TRUNCATION_WARNINGS and see the difference.

As for links, did you look at https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver15?

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks .. pretty clear for the VERBOSE_TRUNCATION_WARNINGS

but IDENTITY_CACHE , i see the difference , but I can't think of any use cases for this .

0 Votes 0 ·

but IDENTITY_CACHE , i see the difference , but I can't think of any use cases for this .

Then we are two! But there are people who get mighty upset when their IDENTITY columns jump 1000 and start to scream blue murder. I answered such a thread only a couple of days back. I told the poster that he should ignore the skipped values - or, if he need contiguous values. he should not use IDENTITY at all. But the poster completely ignored my comments and only wanted to know how to set the trace flag. (He was on SQL 2016 which does not have this scoped configuration.)

Up to SQL 2008, there was no cache for IDENTITY at all, but they added one whey the implemented sequences.

0 Votes 0 ·

of coz if user use the column as identify , why not want to be a continuous values? So why sql server skip the values by 1000 ??? what's the use case ?

0 Votes 0 ·
Show more comments