ch17 performance.sql

SQL+Server实用简明教程电子教案和实例源文件

sp_who
sp_monitor
USE AdventureWorks
SELECT *
FROM Production.Product

USE AdventureWorks
DBCC CHECKDB
GO
 


DROP TABLE employees
DROP TABLE emp_orders 


USE ElecTravelCom
CREATE TABLE employees(
emp_ID INT NOT NULL,
birthdate DATETIME,
hiredate DATETIME,
salary MONEY)
GO
CREATE TABLE emp_orders(
order_ID INT NOT NULL,
emp_ID INT NOT NULL,
total MONEY,
amount INT)





USE ElecTravelCom
DECLARE @i INT
DECLARE @emp_ID INT
DECLARE @birthdate DATETIME
DECLARE @hiredate DATETIME
DECLARE @salary MONEY
SET @i = 1
SET @birthdate = GETDATE()
SET @hiredate = GETDATE()
SET @salary = 100.00
WHILE @i < 3001
BEGIN
	INSERT INTO employees(emp_ID, birthdate, hiredate, salary)
	VALUES(@i, @birthdate, @hiredate, @salary)
	SET @i = @i + 1
END



SELECT * FROM emp_orders WHERE order_ID in (1511, 2678)
DELETE FROM emp_orders


USE ElecTravelCom
DECLARE @i INT, @j INT
SET @i = 3000
SET @j = 10
WHILE @j > 0
BEGIN
	IF @i > 0
	BEGIN
		INSERT INTO emp_orders(order_ID, emp_ID, total)
		VALUES(@i, @j, 5)
		SET @i = @i - 1
	END
	ELSE
	BEGIN
		SET @j = @j -1
		SET @i = 3000
	END
END
GO
UPDATE emp_orders SET amount = 3 WHERE order_ID in (1511, 2678)




















USE ElecTravelCom
CREATE UNIQUE INDEX i_employees_emp_ID ON employees(emp_ID)
CREATE INDEX i_emp_orders_order_ID ON emp_orders(order_ID)
CREATE INDEX i_emp_orders_emp_ID ON emp_orders(emp_ID)



USE ElecTravelCom
SELECT employees.emp_ID, employees.birthdate, emp_orders.total
FROM employees, emp_orders
WHERE employees.emp_ID = emp_orders.emp_ID AND emp_orders.amount = 3






USE ElecTravelCom
GO
DROP INDEX employees.i_employees_emp_ID
DROP INDEX emp_orders.i_emp_orders_order_ID
DROP INDEX emp_orders.i_emp_orders_emp_ID
GO
CREATE UNIQUE CLUSTERED INDEX ci_employees_emp_ID ON employees(emp_ID)
CREATE CLUSTERED INDEX ci_emp_orders_emp_ID ON emp_orders(emp_ID)
GO
DROP INDEX employees.ci_employees_emp_ID
DROP INDEX emp_orders.ci_emp_orders_emp_ID



USE ElecTravelCom
SELECT employees.emp_ID, employees.birthdate, emp_orders.total
FROM employees, emp_orders
WHERE employees.emp_ID = emp_orders.emp_ID AND emp_orders.amount = 3