Table Designs

Question 0

Creating table

Answer 0

IF OBJECT_ID('schemaname.tblname', 'U') IS NOT NULL
   DROP TABLE schemaname.tblname'

CREATE TABLE schemaname.tblname
(
Colname datatype NULL/NOT NULL,
Colname2 datatype NULL/NOT NULL,
Colname3 datatype NULL/NOT NULL
);

Question 1

Naming tbls

Answer 1

means tbl is temporary
## means creating a global tbl
Can use letter or underscore
Tbl can store 1024 columns

Question 2

Alter tbl com

Answer 2

Change column name
Add a new column
Delete column
Enable/disable triggers
Switch tbl to another partition

ALTER TABLE tblname
ADD col datatype NULL/NOT NULL

ALTER TABLE tblname
DROP COLUMN colname

ALTER TABLE tblname
ALTER COLUMN colname whatever u want 2 do

Question 3

Dropping tbl

Answer 3

It removed tbl definitions
Days
Indexes, triggers, constraints, permissions

Any VIEWS or STORED PROCEDURED have to be dropped separately

DROP TABLE tblname
note: this will ONLY fail if foreign key constraint exists.

To delete data but not tbl definition use
TRUNCATE TABLE tblname