SQL Server数据库常用操作和对照

1. SQL Server数据映射参照

SQL Server 数据库引擎类型	.NET Framework 类型	SqlDbType 枚举	SqlDataReader SqlTypes 类型化访问器	DbType 枚举	SqlDataReader DbType 类型化访问器
bigint	Int64	BigInt	GetSqlInt64	Int64	GetInt64
binary	Byte[]	VarBinary	GetSqlBinary	Binary	GetBytes
bit	Boolean	Bit	GetSqlBoolean	Boolean	GetBoolean
char	String Char[]	Char	GetSqlString	AnsiStringFixedLength , String	GetString GetChars
date 1 （SQL Server 2008 及更高版本）	DateTime	Date 1	GetSqlDateTime	Date 1	GetDateTime
datetime	DateTime	DateTime	GetSqlDateTime	DateTime	GetDateTime
datetime2 （SQL Server 2008 及更高版本）	DateTime	DateTime2	无	DateTime2	GetDateTime
datetimeoffset （SQL Server 2008 及更高版本）	DateTimeOffset	DateTimeOffset	无	DateTimeOffset	GetDateTimeOffset
Decimal	小数	Decimal	GetSqlDecimal	Decimal	GetDecimal
FILESTREAM attribute (varbinary(max))	Byte[]	VarBinary	GetSqlBytes	Binary	GetBytes
FLOAT	Double	Float	GetSqlDouble	Double	GetDouble
image	Byte[]	Binary	GetSqlBinary	Binary	GetBytes
int	Int32	Int	GetSqlInt32	Int32	GetInt32
money	小数	Money	GetSqlMoney	Decimal	GetDecimal
nchar	String Char[]	NChar	GetSqlString	StringFixedLength	GetString GetChars
ntext	String Char[]	NText	GetSqlString	String	GetString GetChars
numeric	小数	Decimal	GetSqlDecimal	Decimal	GetDecimal
nvarchar	String Char[]	NVarChar	GetSqlString	String	GetString GetChars
real	Single	Real	GetSqlSingle	Single	GetFloat
rowversion	Byte[]	Timestamp	GetSqlBinary	Binary	GetBytes
smalldatetime	DateTime	DateTime	GetSqlDateTime	DateTime	GetDateTime
smallint	Int16	SmallInt	GetSqlInt16	Int16	GetInt16
smallmoney	小数	SmallMoney	GetSqlMoney	Decimal	GetDecimal
sql_variant	Object 2	Variant	GetSqlValue 2	Object	GetValue 2
text	String Char[]	Text	GetSqlString	String	GetString GetChars
time （SQL Server 2008 及更高版本）	TimeSpan	Time	无	Time	GetDateTime
timestamp	Byte[]	Timestamp	GetSqlBinary	Binary	GetBytes
tinyint	Byte	TinyInt	GetSqlByte	Byte	GetByte
uniqueidentifier	Guid	UniqueIdentifier	GetSqlGuid	Guid	GetGuid
varbinary	Byte[]	VarBinary	GetSqlBinary	Binary	GetBytes
varchar	String Char[]	VarChar	GetSqlString	AnsiString , String	GetString GetChars
xml	Xml	Xml	GetSqlXml	Xml	无

2. SQL Server修改表结构

2.1.1. 添加字段

ALTER TABLE 表名 ADD 新增字段名 字段类型 默认值;
ALTER TABLE TESTTABLE ADD TESTCOLUMN INT DEFAULT 0;

2.1.2. 删除字段

如果字段设置了默认值，会报错“由于一个或多个对象访问此列，ALTER TABLE DROP COLUMN失败。”，此时需要先删除字段的默认值约束。

ALTER TABLE 表名 DROP COLUMN 字段名;
ALTER TABLE TESTTABLE DROP COLUMN TESTCOLUMN;

2.1.3. 删除字段约束或键

ALTER TABLE 表名 DROP CONSTRAINT  约束名或键名;
ALTER TABLE TESTTABLE DROP CONSTRAINT DF__TESTTABLE__TESTCOLUMN__7AD3CDD7;

2.1.4. 修改字段类型

ALTER TABLE 表名 ALTER COLUMN 字段名 字段类型;
ALTER TABLE TESTTABLE ALTER COLUMN TESTCOLUMN VARCHAR(10);

2.1.5. 修改字段名

注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。

EXEC SP_RENAME '表名.原字段名','新字段名';
EXEC SP_RENAME 'TESTTABLE.TESTCOLUMN','TESTCOLUMN1';

2.1.6. 修改表名

EXEC SP_RENAME '原表名','新表名';
EXEC SP_RENAME 'TESTTABLE','TESTTABLE1';

3. 表和字段注释说明

3.1. 表操作:

3.1.1. 添加表注释说明

EXECUTE SP_ADDEXTENDEDPROPERTY N'MS_Description', N'表注释说明', N'SCHEMA', N'DBO',N'TABLE', N'表名', NULL, NULL;
EXECUTE SP_ADDEXTENDEDPROPERTY N'MS_Description', N'测试表', N'SCHEMA', N'DBO',N'TABLE', N'TESTTABLE', NULL, NULL;

3.1.2. 修改表注释说明

EXECUTE SP_UPDATEEXTENDEDPROPERTY 'MS_Description','修改表注释说明','SCHEMA','DBO','TABLE','表名',NULL,NULL;
EXECUTE SP_UPDATEEXTENDEDPROPERTY 'MS_Description','测试表1','SCHEMA','DBO','TABLE','TESTTABLE',NULL,NULL;

3.1.3. 删除表注释说明

EXECUTE SP_DROPEXTENDEDPROPERTY 'MS_Description','SCHEMA','DBO','TABLE','表名',NULL,NULL;
EXECUTE SP_DROPEXTENDEDPROPERTY 'MS_Description','SCHEMA','DBO','TABLE','TESTTABLE',NULL,NULL;

3.2. 字段操作:

3.2.1. 添加字段注释说明

EXECUTE SP_ADDEXTENDEDPROPERTY N'MS_Description', '需要注释的内容', N'SCHEMA', N'DBO', N'TABLE', N'表名', N'COLUMN', N'字段名';
EXECUTE SP_ADDEXTENDEDPROPERTY N'MS_Description', '测试字段', N'SCHEMA', N'DBO', N'TABLE', N'TESTTABLE', N'COLUMN', N'TESTCOLUMN';

3.2.2. 修改字段注释说明

EXECUTE SP_UPDATEEXTENDEDPROPERTY 'MS_Description', '修改注释的内容','SCHEMA','DBO','TABLE','表名','COLUMN','字段名';
EXECUTE SP_UPDATEEXTENDEDPROPERTY 'MS_Description', '测试字段123','SCHEMA','DBO','TABLE','TESTTABLE','COLUMN','TESTCOLUMN';

3.2.3. 删除字段注释说明

EXECUTE SP_DROPEXTENDEDPROPERTY 'MS_Description','SCHEMA','DBO','TABLE','表名','COLUMN','字段名';
EXECUTE SP_DROPEXTENDEDPROPERTY 'MS_Description','SCHEMA','DBO','TABLE','TESTTABLE','COLUMN','TESTCOLUMN';

4. 增加和取消字段自增

 SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }

如果任然显示"仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表'GY_MLWorkloadReport'中的标识列指定显式值。"，请检查SQL Server版本，详见:

set identity_insert on off 失效 不起作用_identity_insert 无效_oShenGun的博客-CSDN博客。或使用SSMS进行手动标识列修改。