MS QUERY SQL: Create Database Objects

FOREIGN KEY

A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Ex: column INT FOREIGN KEY REFERENCES TABLE(COLUMN) Used to prevent actions that would destroy links between tables. Prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

SPs

- Can be used to read and modify data. - To run an SP Execute or Exec is used, cannot be used with SELECT statement. - Cannot JOIN a SP in a SELECT statement. - Can use Table Variables as well as Temporary Tables inside an SP. - Can create and use Dynamic SQL. - Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP. - Can use used with XML FOR clause. - Can use a UDF inside a SP in SELECT statement. - Cannot be used to create constraints while creating a table. - Can execute all kinds of functions, be it deterministic or non-deterministic.

UDF (functions)

- Can only read data, cannot modify the database. - Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER). - Can JOIN a UDF in a SELECT statement. - Cannot use a Temporary Table, only Table Variables can be used. - Cannot use a Dynamic SQL inside a UDF. - Cannot use transactions inside a UDF. - Cannot be used with XML FOR clause. - Cannot execute an SP inside a UDF. - Can be used to create Constraints while creating a table. - Cannot execute some non-deterministic built-in functions, like GETDATE()

CONSTRAINTS______

...

DATA TYPES_______

...

VIEW

A view is a virtual table. May be bound to original table using WITH SCHEMABINDING

UDF EXAMPLES CONTINUED

CREATE FUNCTION fnGetEmployeesByCity (@sCity VARCHAR(30)) RETURNS TABLE AS RETURN ( SELECT FirstName, LastName, Address FROM Employees WHERE City = @sCity ) GO

SCHEMABINDING

Binds the view or function to original view to the COLUMNS selected. 1) You can not change the collation of a database with schemabound objects. 2) You can not use SELECT * in a schemabound view. 3) You can not run sp_refreshview on a schemabound view. You do get a rather unhelpful error though. 4) You can make any change to the table that do not affect the structure of the bound columns. 5) You can find out if an object is schemabound by looking at the column is_schema_bound in sys.sql_modules or the system function OBJECTPROPERTY(object_id, 'is_schema_bound'). 6) If you reference a view or function in a schemabound view or function then that view or function must also be schemabound. 7) Objects that are bound (tables/views) can not be dropped while a schemabound object references them

COUNT_BIG(*)

COUNT_BIG(*) returns the number of items in a group. This includes NULL values and duplicates. COUNT_BIG (ALL expression) evaluates expression for each row in a group and returns the number of nonnull values. COUNT_BIG (DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values. View definition must not contain COUNT, while creating an Indexed View. Instead, COUNT_BIG can be used.

UDF EXAMPLES

CREATE FUNCTION fnNeedToReorder (@nReorderLevel INT, @nUnitsInStock INT, @nUnitsOnOrder INT) RETURNS VARCHAR(3) AS BEGIN DECLARE @sReturnValue VARCHAR(3) IF ((@nUnitsInStock + @nUnitsOnOrder) - @nReorderLevel) < 0 SET @sReturnValue = 'Yes' ELSE SET @sReturnValue = 'No' RETURN @sReturnValue END GO

UPDATE (function)

Conta

UPDATED

Contains the rows as they were before the UPDATE statement

INSERTED

Contains the rows being inserted

DCL

Data Control Language (DCL) statements. Some examples: GRANT - gives user's access privileges to database REVOKE - withdraw access privileges given with the GRANT command

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples: CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed COMMENT - add comments to the data dictionary RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples: SELECT - retrieve data from the a database INSERT - insert data into a table UPDATE - updates existing data within a table DELETE - deletes all records from a table, the space for the records remain MERGE - UPSERT operation (insert or update) CALL - call a PL/SQL or Java subprogram EXPLAIN PLAN - explain access path to data LOCK TABLE - control concurrency

DML TRIGGERS vs CHECK Constraint______

Data Manipulation Language are used AFTER a query/command is given. Check Checks during original transaction.

CLUSTERED INDEX

Highlights a key for each page leaf in table. Searches nearby adjacent indexed values guaranteed to be physically adjacent. Primary Key automatically forms one if none exists.

DATETIME

January 1, 1753 - December 31, 9999

XML

Non-Relational Data that uses inherent order, represents containment hierarchy, and useful when the structure might change drastically.

CREATE

Only One Object of the same name can be created.

Calculated Column

SUM(COLUMN_1 + COLUMN_2)

Stored Procedures

Simple/Complex formulas. Not to be confused with CURSOR functions.

INDEXED VIEWS

Table Index has priority unless using FROM Table1, WTIH (NOEXPAND) OPTION (EXPAND VIEWS) forces SQL to use the Index from Tables.

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. COMMIT - save work done SAVEPOINT - identify a point in a transaction to which you can later roll back ROLLBACK - restore database to original since the last COMMIT SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

CHECK

The CHECK constraint is used to limit the value range that can be placed in a column. Ex: CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes') If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

DEFAULT

The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified.

UNIQUE

The UNIQUE constraint makes column a Candidate Key like a Primary Key. Can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Further note on INDEXED VIEWS

The steps required to create an indexed view are critical to the successful implementation of the view: Verify the setting of ANSI_NULLS is correct for all existing tables that will be referenced in the view. Verify ANSI_NULLS is set correctly for the current session as shown in the table in "Using SET Options to Obtain Consistent Results" before creating any new tables. Verify ANSI_NULLS and QUOTED_IDENTIFIER are set correctly for the current session as shown in the table in "Using SET Options to Obtain Consistent Results" before creating the view. Verify the view definition is deterministic. Create the view using the WITH SCHEMABINDING option. Verify your session's SET options are set correctly as shown in the table in "Using SET Options to Obtain Consistent Results" before creating the unique clustered index on the view. Create the unique clustered index on the view.

PRIMARY KEY

Uniquely identifies each record in a database table. Only ONE can exist at the same time in a table. Must contain UNIQUE values. Cannot contain NULL values.

TRIGGER for VIEWS (instead of)

Used to Create View On actions like SELECT

ALTER/DROP TABLE

Used to Modify or dispose of a table/Procedure/DML

COLUMN

Vertical Rows with a distinct Header in a given table.

SPATIAL

geometry:: STAsText() STIntersection() geography::

VARCHAR

nvarchar allows for non-standard character input.