官网http://msdn.microsoft.com/library/ms130214
@@ROWCOUNT (Transact-SQL)
返回受上一语句影响的行数。 如果行数大于 20 亿,请使用ROWCOUNT_BIG。
sql server中的 SET NOCOUNT ON 的含义
SET
使返回的结果中不包含有关受
语法
SET
注释
当
SQL Server中OBJECT_ID()函数的语法如下:
OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ] object_name' [ ,'object_type' ] )1、其中,"[]"表示可选部分。
2、参数object_name ' 是要使用的对象。
object_name 的数据类型为varchar 或 nvarchar。如果 object_name 的数据类型为 varchar,则它将隐式转换为 nvarchar。可以选择是否指定数据库和架构名称。
3、参数object_type是 架构范围的对象类型。
object_type 的数据类型为 varchar 或 nvarchar。如果 object_type 的数据类型为 varchar,则它将隐式转换为 nvarchar。
USE AdventureWorks; GO IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL DROP TABLE dbo.AWBuildVersion; 括号里的'U‘是什么意思,为什么有些是写’P‘的,还有别的
U 表示是对象是表
P 表示对象是存储过程
SELECT * FROM SYSOBJECTS WHERE TYPE='U'
Object type:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
GO (Transact-SQL)
GO [count]
GO 不是 Transact-SQL 语句;它是可由 sqlcmd 和osql 实用工具以及 SQL Server Management Studio 代码编辑器识别的命令。
SQL Server 实用工具将 GO 解释为应该向 SQL Server 实例发送当前批 Transact-SQL 语句的信号。当前批语句由上一 GO 命令后输入的所有语句组成,如果是第一条 GO 命令,则由即席会话或脚本开始后输入的所有语句组成。
GO 命令和 Transact-SQL 语句不能在同一行中。 但在 GO 命令行中可包含注释。
用户必须遵照使用批处理的规则。 例如,在批处理中的第一条语句后执行任何存储过程必须包含 EXECUTE 关键字。局部(用户定义)变量的作用域限制在一个批处理中,不可在 GO 命令后引用。
USE AdventureWorks2012; GO DECLARE @MyMsg VARCHAR(50) SELECT @MyMsg = 'Hello, World.' GO -- @MyMsg is not valid after this GO ends the batch. -- Yields an error because @MyMsg not declared in this batch. PRINT @MyMsg GO SELECT @@VERSION; -- Yields an error: Must be EXEC sp_who if not first statement in -- batch. sp_who GO
SQL Server 应用程序可以将多个 Transact-SQL 语句作为一个批发送到 SQL Server 的实例来执行。然后,该批中的语句被编译成一个执行计划。程序员在 SQL Server 实用工具中执行特殊语句,或生成 Transact-SQL 语句的脚本在 SQL Server 实用工具中运行时,使用 GO 作为批结束的信号。
如果基于 ODBC 或 OLE DB API 的应用程序试图执行 GO 命令,会收到语法错误。SQL Server 实用工具从不向服务器发送 GO 命令。
以下示例创建两个批。 第一个批只包含一条USEAdventureWorks2012 语句,用于设置数据库上下文。其余的语句使用局部变量。因此,所有局部变量声明必须组成一个批。为此,必须在最后一条引用此变量的语句之后才使用GO 命令。
USE AdventureWorks2012; GO DECLARE @NmbrPeople int SELECT @NmbrPeople = COUNT(*) FROM Person.Person; PRINT 'The number of people as of ' + CAST(GETDATE() AS char(20)) + ' is ' + CAST(@NmbrPeople AS char (10)); GO
sys.objects (Transact-SQL)
Column name | Data type | Description |
---|---|---|
name | sysname | Object name. |
object_id | int | Object identification number. Is unique within a database. |
principal_id | int | ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership. Is NULL if there is no alternate individual owner. Is NULL if the object type is one of the following: C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint PK = PRIMARY KEY constraint R = Rule (old-style, stand-alone) TA = Assembly (CLR-integration) trigger TR = SQL trigger UQ = UNIQUE constraint |
schema_id | int | ID of the schema that the object is contained in. Schema-scoped system objects are always contained in the sys or INFORMATION_SCHEMA schemas. |
parent_object_id | int | ID of the object to which this object belongs. 0 = Not a child object. |
type | char(2) | Object type: AF = Aggregate function (CLR) C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint FN = SQL scalar function FS = Assembly (CLR) scalar-function FT = Assembly (CLR) table-valued function IF = SQL inline table-valued function IT = Internal table P = SQL Stored Procedure PC = Assembly (CLR) stored-procedure PG = Plan guide PK = PRIMARY KEY constraint R = Rule (old-style, stand-alone) RF = Replication-filter-procedure S = System base table SN = Synonym SO = Sequence object SQ = Service queue TA = Assembly (CLR) DML trigger TF = SQL table-valued-function TR = SQL DML trigger TT = Table type U = Table (user-defined) UQ = UNIQUE constraint V = View X = Extended stored procedure |
type_desc | nvarchar(60) | Description of the object type: AGGREGATE_FUNCTION CHECK_CONSTRAINT CLR_SCALAR_FUNCTION CLR_STORED_PROCEDURE CLR_TABLE_VALUED_FUNCTION CLR_TRIGGER DEFAULT_CONSTRAINT EXTENDED_STORED_PROCEDURE FOREIGN_KEY_CONSTRAINT INTERNAL_TABLE PLAN_GUIDE PRIMARY_KEY_CONSTRAINT REPLICATION_FILTER_PROCEDURE RULE SEQUENCE_OBJECT SERVICE_QUEUE SQL_INLINE_TABLE_VALUED_FUNCTION SQL_SCALAR_FUNCTION SQL_STORED_PROCEDURE SQL_TABLE_VALUED_FUNCTION SQL_TRIGGER SYNONYM SYSTEM_TABLE TABLE_TYPE UNIQUE_CONSTRAINT USER_TABLE VIEW |
create_date | datetime | Date the object was created. |
modify_date | datetime | Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered. |
is_ms_shipped | bit | Object is created by an internal SQL Server component. |
is_published | bit | Object is published. |
is_schema_published | bit | Only the schema of the object is published. |
约束(Constraint)
约束(Constraint)是Microsoft SQL Server提供的自动保持数据库完整性的一种方法,定义了可输入表或表的单个列中的数据的限制条件。在SQL Server 中有5 种约束:
主关键字约束(Primary Key Constraint)
外关键字约束(Foreign Key Constraint)
惟一性约束(Unique Constraint)
检查约束(Check Constraint)
缺省约束(Default Constraint)