视图: 相当于将一个表用一个视图来存储
1 2 COPY create view vw_1 as select top 100 emp.Employee_Id as 员工编号, emp.Employee_Name as 员工姓名, dept.Department_Name as 部门 from Employees as emp inner join Departments as dept on emp.Department_Id= dept.Department_ID order by dept.Department_ID / / 前提得有top select * from vw_1 drop view vw_1
–注意:在子查询,或视图等中不能使用order by子句,除非指定了top语句。
聚合索引和索引: 创建主键的时候就有了聚合索引 所以不需要创建 聚合索引是确定一条数据在数据集中的位置 存储记录是物理上连续存在 非聚合索引 :逻辑上的连续性 查看索引:select * from sys.indexes
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 COPY select * from Employeesupdate Employees set Salary= Salary-1000 where Employee_Id= 1 update Employees set Salary= Salary+ 1000 where Employee_Id= 2 begin transaction declare @sumerrors int = 0 update Employees set Salary= Salary-1000 where Employee_Id= 1 set @sumerrors = @sumerrors + @@ERROR update Employees set Salary= Salary+ 1000 where Employee_Id= 2 set @sumErrors = @sumErrors + @@error if @sumerrors = 0 begin commit end else begin rollback end ALTER TABLE Employees add constraint xx CHECK (Salary< 100000 ) 约束的名字交 xx 如果这个约束一开始表就违反了 就会报错ALTER TABLE Employeesdrop constraint xx 是删除这个约束 没有修改约束的命令. 先增加一个新的约束,再删除旧的约束。