时间:2020-09-03来源:www.pcxitongcheng.com作者:电脑系统城
CREATE TABLE dbo.TEST
(
ID INT IDENTITY(1,1) ,
NAME VARCHAR(32)
);
ALTER TABLE dbo.TEST ADD ID1 INT IDENTITY(10,1)
USE AdventureWorks2014;
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST')
BEGIN
DROP TABLE test;
END
GO
CREATE TABLE dbo.TEST
(
ID INT IDENTITY(1,1) ,
NAME VARCHAR(32)
);
INSERT INTO dbo.TEST
( NAME )
SELECT 'K1' UNION ALL
SELECT 'K2' UNION ALL
SELECT 'K3' UNION ALL
SELECT 'K4' UNION ALL
SELECT 'K5' UNION ALL
SELECT 'K6';
SET IDENTITY_INSERT dbo.TEST ON;
GO
INSERT INTO dbo.TEST
( ID, NAME )
SELECT 13, 'k13';
GO
SET IDENTITY_INSERT dbo.TEST OFF;
GO
DBCC CHECKIDENT(test)
DBCC CHECKIDENT('test', RESEED ,9);
INSERT INTO dbo.TEST
( NAME )
SELECT 'K9' UNION ALL
SELECT 'K10' UNION ALL
SELECT 'K11' UNION ALL
SELECT 'K12' UNION ALL
SELECT 'K13' ;
SELECT * FROM dbo.TEST;
CREATE TABLE dbo.TEST
(
ID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(32)
);
USE AdventureWorks2014;
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST')
BEGIN
DROP TABLE test;
END
GO
CREATE TABLE dbo.TEST
(
ID INT IDENTITY(1,1) ,
NAME VARCHAR(32)
);
INSERT INTO dbo.TEST
( NAME )
SELECT 'K1' UNION ALL
SELECT 'K2' UNION ALL
SELECT 'K3' UNION ALL
SELECT 'K4' UNION ALL
SELECT 'K5' UNION ALL
SELECT 'K6';
SET IDENTITY_INSERT dbo.TEST ON;
GO
INSERT INTO dbo.TEST
( ID, NAME )
SELECT 13, 'k13';
GO
SET IDENTITY_INSERT dbo.TEST OFF;
GO
DBCC CHECKIDENT('test', RESEED ,9);
GO
DBCC CHECKIDENT(test);
GO
INSERT INTO dbo.TEST
( NAME )
SELECT 'K9' UNION ALL
SELECT 'K10' UNION ALL
SELECT 'K11' UNION ALL
SELECT 'K12' UNION ALL
SELECT 'K13' ;
SELECT * FROM dbo.TEST;
CREATE TABLE dbo.TEST
(
ID INT ,
NAME VARCHAR(32)
);
--这种语法是不允许的
ALTER TABLE dbo.TEST ALTER COLUMN ID IDENTITY(10,1)
--事务回滚导致标识列自增跳号
INSERT INTO dbo.TEST
( NAME )
SELECT 'K1' UNION ALL
SELECT 'K2' UNION ALL
SELECT 'K3' UNION ALL
SELECT 'K4' UNION ALL
SELECT 'K5' UNION ALL
SELECT 'K6';
BEGIN TRAN
INSERT INTO dbo.TEST
( NAME )
SELECT 'K7';
ROLLBACK TRAN;
INSERT INTO dbo.TEST
( NAME )
SELECT 'KKK';
SELECT * FROM dbo.TEST;
USE AdventureWorks2014;
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST_TRAN')
BEGIN
DROP TABLE TEST_TRAN;
END
GO
CREATE TABLE dbo.TEST_TRAN
(
ID INT IDENTITY(1,1) PRIMARY KEY,
TRN_NAME VARCHAR(32)
);
DECLARE @row_index INT;
SET @row_index =1;
BEGIN TRAN
WHILE @row_index <=10
BEGIN
INSERT INTO TEST_TRAN
VALUES('TRANS_1');
SET @row_index +=1;
WAITFOR DELAY '00:00:01';
END
COMMIT TRAN;
DECLARE @row_index INT;
SET @row_index =1;
BEGIN TRAN
WHILE @row_index <=10
BEGIN
INSERT INTO TEST_TRAN
VALUES('TRANS_2');
SET @row_index +=1;
WAITFOR DELAY '00:00:01';
END
COMMIT TRAN;
SELECT name ,
is_identity ,
seed_value ,
increment_value ,
last_value
FROM sys.identity_columns
WHERE object_id = OBJECT_ID('TEST');
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE VIEW sys.identity_columns AS
SELECT object_id = id,
name = name,
column_id = colid,
system_type_id = xtype,
user_type_id = utype,
max_length = length,
precision = prec,
scale = scale,
collation_name = convert(sysname,CollationPropertyFromId(collationid,'name')),
is_nullable = sysconv(bit, 1 - (status & 1)), -- CPM_NOTNULL
is_ansi_padded = sysconv(bit, status & 2), -- CPM_NOTRIM
is_rowguidcol = sysconv(bit, status & 8), -- CPM_ROWGUIDCOL
is_identity = sysconv(bit, status & 4), -- CPM_IDENTCOL
is_filestream = sysconv(bit, status & 32), -- CPM_FILESTREAM
is_replicated = sysconv(bit, status & 0x20000), -- CPM_REPLICAT
is_non_sql_subscribed = sysconv(bit, status & 0x40000), -- CPM_NONSQSSUB
is_merge_published = sysconv(bit, status & 0x80000), -- CPM_MERGEREPL
is_dts_replicated = sysconv(bit, status & 0x100000), -- CPM_REPLDTS
is_xml_document = sysconv(bit, 0),
xml_collection_id = sysconv(int, 0),
default_object_id = sysconv(int, 0),
rule_object_id = sysconv(int, 0),
seed_value = IdentityProperty(id, 'SeedValue'),
increment_value = IdentityProperty(id, 'IncrementValue'),
last_value = IdentityProperty(id, 'LastValue'),
is_not_for_replication = sysconv(bit, status & 0x10000), -- CPM_ID_REPL
is_computed = sysconv(bit, status & 16), -- CPM_COMPUTED
sysconv(bit, 0) as is_sparse,
sysconv(bit, 0) as is_column_set
FROM sys.syscolpars
WHERE number = 0 -- SOC_COLUMN
AND (status & 4) = 4 -- CPM_IDENTCOL
AND has_access('CO', id) = 1
GO
CREATE TABLE test1(id INT IDENTITY(1,1), name VARCHAR(10))
CREATE TABLE test2(id INT IDENTITY(1,2), name VARCHAR(10))
CREATE TABLE test3(id INT IDENTITY(1,3), name VARCHAR(10))
2023-03-19
Centos 7.9安装MySQL8.0.32的详细教程2023-03-19
一步步教你利用Mysql存储过程造百万级数据2023-03-19
利用Mysql定时+存储过程创建临时表统计数据的过程问题描述:MYSQL version 5.6.8command 表结构 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 CREATE TABLE command ( ID INT NOT NULL, NAME VARCHAR(16), D...
2023-03-19
一、Galera Cluster 二、基础环境搭建 三、加入配置参数启动集群 四、 测试 五、ProxySql...
2023-03-17