-- Just a few examples of some ALTER and ADD SQL Statements -- Usable for SQL 2005 / 2008 -- By Albert - v. 0.1 -- 1. Example ADD a Filegroup and a File to a database: -- ---------------------------------------------------- ALTER DATABASE SALES ADD FILEGROUP [INDEX] ALTER DATABASE SALES ADD FILE ( NAME = Sales_Index, FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\sales_index.ndf', SIZE = 12000MB, MAXSIZE = 15000MB, FILEGROWTH = 100MB ) TO FILEGROUP [INDEX] GO -- 2. Example Drop (optional) and Add a PK constraint to a Table: -- -------------------------------------------------------------- -- 2.1 pk, and it corresponds to a nonclustered index on filegroup INDEX ALTER TABLE CUSTOMERS DROP CONSTRAINT [pk_cust_id] ALTER TABLE CUSTOMERS ADD CONSTRAINT [pk_cust_id] PRIMARY KEY NONCLUSTERED (cust_id) on [INDEX] -- 2.2 pk, and it corresponds to a clustered index on filegroup SALES_DATA ALTER TABLE CUSTOMERS DROP CONSTRAINT [pk_cust_id] ALTER TABLE CUSTOMERS ADD CONSTRAINT [pk_cust_id] PRIMARY KEY CLUSTERED (cust_id) on [SALES_DATA] -- 3. Example statements to DROP and ADD a Foreign Key Constraint: -- --------------------------------------------------------------- ALTER TABLE ORDERS DROP CONSTRAINT FK_CUST_ID ALTER TABLE ORDERS ADD CONSTRAINT FK_CUST_ID FOREIGN KEY (CUST_ID) REFERENCES CUSTOMERS(CUST_ID) -- 4. Example statements to Disable and Enable a Foreign Key Constraint: -- --------------------------------------------------------------------- ALTER TABLE ORDERS NOCHECK CONSTRAINT FK_CUST_ID ALTER TABLE ORDERS CHECK CONSTRAINT FK_CUST_ID -- 5. Example statements to Disable and Enable a Trigger on a Table: -- ----------------------------------------------------------------- -- 5.1 Example of a very simple trigger on ORDER tables at an INSERT/UPDATE, that will update the STOCK table: CREATE TRIGGER orders_INSERT ON orders FOR INSERT, UPDATE AS UPDATE stock SET in_stock=in_stock-INSERTed.amount FROM stock, INSERTed WHERE stock.item_id=INSERTed.item_id -- 5.2 Disable just one Trigger on a Table: DISABLE TRIGGER tr_employeeaudit ON EMPLOYEE ENABLE TRIGGER tr_employeeaudit ON EMPLOYEE -- 5.3 Enabling or Disabling ALL Triggers on a Table: DISABLE TRIGGER ALL ON EMPLOYEE ENABLE TRIGGER ALL ON EMPLOYEE -- 6. Example statements to Create or modify an Index: -- --------------------------------------------------- -- create clustered index: CREATE CLUSTERED INDEX indx_employee_empname ON EMPLOYEE(EMP_NAME) CREATE CLUSTERED INDEX indx_employee_empname ON EMPLOYEE(EMP_NAME) ON SALES_DATA -- filegroup SALES_DATA CREATE CLUSTERED INDEX indx_employee_empname ON EMPLOYEE(EMP_NAME) WITH FILLFACTOR=80 -- create non-clustered index: CREATE NONCLUSTERED INDEX indx_employee_empname ON EMPLOYEE(EMP_NAME) CREATE NONCLUSTERED INDEX indx_employee_empname ON EMPLOYEE(EMP_NAME) ON SALES_INDEX -- filegroup SALES_INDEX CREATE NONCLUSTERED INDEX indx_employee_empname ON EMPLOYEE(EMP_NAME) WITH FILLFACTOR=80 -- crete index with "include" a column CREATE INDEX IX_SAMPLE_TITEL ON SAMPLES(Title,Revision) INCLUDE (ANNOTATION) -- Some additional options or clauses: CREATE NONCLUSTERED INDEX [Custom_idx_setvalue_Input_processscopedate] ON [dbo].[SetValue_Input] ( [ProcessScopeDate] ASC, [ObjID_CPortfolio] ASC ) INCLUDE ( [ModelID],[ProcessStatus]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINEHordeCleaned= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [INDEXES] -- Reorganize an Index: ALTER INDEX ON REORGANIZE -- Reorganize All indexes on a Table: ALTER INDEX ALL ON REORGANIZE -- Rebuild an Index: ALTER INDEX ON REBUILD or DBCC DBREINDEX(Table_Name,index_name,80) -- Rebuild All indexes on a Table: ALTER INDEX ALL ON REBUILD DBCC DBREINDEX(Table_Name,'',80) -- 7. Add, Drop, Modify a Column to or from a table: -- ------------------------------------------------- ALTER TABLE table_name ADD Column_Name datatype NULL|NOT NULL (e.g. ALTER TABLE EMPLOYEES ADD SALARY decimal(7,2) null) ALTER TABLE table_name DROP COLUMN column_name ALTER TABLE Table_Name ALTER COLUMN Column_Namer datatype NULL|NOT NULL (e.g. ALTER TABLE EMPLOYEES ALTER COLUMN SALARY DECIMAL (5, 2) ; -- 8. Some examples on how to 'dynamically' make create statements: -- ---------------------------------------------------------------- select 'ALTER TABLE '+OBJECT_NAME(parent_object_id)+' NOCHECK CONSTRAINT '+name FROM sys.foreign_keys select 'ALTER TABLE '+OBJECT_NAME(parent_object_id)+' CHECK CONSTRAINT '+name FROM sys.foreign_keys select 'ALTER TABLE '+OBJECT_NAME(parent_object_id)+' DROP CONSTRAINT '+name FROM sys.foreign_keys SELECT 'DISABLE TRIGGER ALL ON '+OBJECT_NAME(parent_id) from sys.triggers SELECT 'ENABLE TRIGGER ALL ON '+OBJECT_NAME(parent_id) from sys.triggers