ALTER Table
Changes table according to instructions within ALTER statements, instead of attempting to CREATING the table again
Data Control Language
Used to grant/revoke database permissions to/from users and groups
Data Definition Language
Used for creating tables, relationships, and other structures
Data Manipulation Language
Used for queries and data modification
SQL persistent stored modules (SQL PSM)
Programming code that may include SQL statements in modules for reuse
ANSI/ISO standard embedding procedural programming functionality into SQL
Transaction control language
Used to mark transaction boundaries and control transaction behaivior
Non-clustered index
Seperation structure is created for index values
Each value points to an actual records storage location of table data
Clustered index
Sort and store data rows based on index key value
One per table that is physically stored in file
Cursors
Extension to result sets that allow processing one row at a time from result set
SQL injection attack
When data from a user is used to modify a sql statement
Inserting SQL data into form fields rather than "clean" values in the aim of gaining access/adversely modifying data inside the database.
Ex: User inputs into "First Name" field in web application th
Object name
Server_Name.DB_Name.Schema.Object_Name
ServerName, DBName, Schema, Object separated with periods.
Ex: Yorktown.SCA_Bird.dbo.tblPerson
Authorizing
Grant user permissions to database objects
Authenticating
Create login for a user
Database security
Ensures that only authorized users can perform authorized activities at authorized times
Limits certain actions on certain users
Database administation
Person or office specific to DBs and their apps
Data administration
Function managing all data assets in an organization
Checkpoint
Point of synchromization between database and changes in memory
Rollback/rollforward recovery
Periodically save database and keep DB change log since last save
Reprocessing recovery
DB goes back to known point, then manually reprocesses workload from there using application (NOT DBMS)
Incrimental backup
Backup changes since previous backups of any type
Differental backup
Backup changes since last full backup
Full backup
Complete backup of everything
How is a function called?
By name from another sql statement or stored procedure
Table valued funtions
Return a table
- can be used in FROM/WHERE/HAVING clauses
Create function definition defines what table columns will be returned
Aggregate functions
Performs a calculation on set of values and returns a single value
Scalar functions
Returns a single value
What may stored procedures invoke?
Triggers
Stored procedures can be called by
User or DB admins
Stored procedures are assigned to
DBs
Stored procedures can be called FROM
Programs written in standard languages
Scripting languages
SQL command prompt
Advantages of stored porcedures
Greater security because they are always stored on the DB server
Decreased network traffic
SQL can be optimized in DMBS compiler
Code sharing
Stored procedures can issue
INSERT, UPDATE, DELETE, and MERGE commands
Triggers can call
Stored procedures
Trigger is called
When INSERT, UPDATE, or DELETE commands are issued
Trigger is assigned to
Table or view`
AFTER trigger
Perform other tasks after standard actio9ns of triggering statement are performed
May not be assigned to views
INSTEAD OF triggers
Override standard actions of triggering statement: INSERT UPDATE or DELETE
Triggers can be used for
Providing default values
Enforcing data constraints
Updating views
Performing referential integrity actions
When trigger is fired, DBMS supplies
Old and new value for update
New values for inserts
Old values for deletions
Triggers can issue
INSERT, UPDATE, and DELETE commands
Concurrent transactions
2+ transactions that appear to users as they are being processed against a database at the same time
In reality, CPU can execute only one instruction at a time
Concurrency control
Ensures that one user's work does not inappropriately influence another user's work
Concurrency problems
Lost updates
Inconsistent reads that leads to lost updates
Serialized transactions
No lost update problems
Locking (ISOLATION)
Transactions are executed sequentially instead of concurrently
transactions are allowed to obtain locks as necessary, but once the first lock is released, no other lock can be obtained
Resource locking
Prevents multiple applications from obtaining copies of same record when record is about to be changed
Way to stop an inconsistent read
Implicit locks
Placed by DBMS
Almost all locking is implicit
Explicit locks
Issued by application program
Lock granularity
Size of a locked resource
- Large granularity is easy to manage but frequently causes conflicts
Exclusive lock (write lock)
Prohibits other users from reading locked resource
Shared lock (read lock)
Allows other users to read locked resource, but they cant make changes to it
Range lock
Applied to set of records
Can prevent inserting, deleting, or updating to set of records
Deadlock (deadly embrace)
When two transactions are waiting on resource that transaction holds
Breaking a deadlock
There are usually algorithms in place to detect a deadlock
Timeouts
DBMS aborts one of the transactions and rolls back partially completed work
Optimistic locking
Assumes that no transaction conflict will occur
Pessimistic locking
Assumes that a conflict will occur
- Locks are issued before transaction is processed, and then the locks are released
Transaction boundary markers
BEGIN TRANSACTION
COMMIT TRANSACTION
ROLLBACK TRANSACTION
Consistent transaction
Transaction either creates new and valid state of data or returns to previous state
Isolation
Achieved by locks and is used to ensure read consistency
Read uncommitted
Specifies that statememnts can read rows that have been modified by other transactiuons but not yet committed
Dirty read may occur
Repeatable read
Species that statements cant read data that has been modified but not yet committed by other transactions
Prevents dirty and non-repeatable reads
Serializable
Statements cannot read data that has been modified but not yet committed by other transactions
Dirty read issue
Read a record that has not been committed
Non-repeatable read issue
2 reads of a row have different values
Phantom read issue
2 reads of a set of records are different in terms of number of rows returned
-User A runs the same query twice.
-In between, User B runs a transaction and commits.
-All the rows in the query have the same value before and after, but different rows are be
INSERT with IdentityID
Inserting into a table with an auto-incrementing identity field (i.e. CustomerID) don't include insert into ID field because the identity will auto increment
INSERT without IdentityID
When inserting into a table with an auto-incrementing identity field (i.e. CustomerID), you can override ID field insert by using WITHOUT IdentityID
Truncate vs. Delete
Truncate is used to remove all data from a table, while leaving
the table structure itself in the database. The SQL TRUNCATE TABLE statement does not use
an SQL WHERE clause to specify conditions for the data deletion�all the data in the table is
always r
ON UPDATE
Trigger constraint determining when the trigger should fire.
Would fire on update of relevant table
ON DELETE
Trigger constraint determining when the trigger should fire.
Would fire on delete of data from relevant table
Global variables
Variables with scope outside of a function or stored procedure, can be accessed from outside.
Examples include @@ROWCOUNT
Local variables
Variables with scope inside a function, trigger, or stored procedure, can only be accessed from within their relevant module.
Examples include @X, @Y, variable declared by a user.
How to call stored procedure/function/trigger
Stored Procedure: EXEC __________
Function: SELECT function_name(_________,_________,______ etc.)
Trigger: SELECT, DELETE, UPDATE
CHECK constraint
Ensures that the value in a column meets a certain expectation
What are the three module types of programs?
User-defined functions, stored procedures, triggers
Server Roles (Security)
dbcreator
securityadmin
serveradmin
sysadmin
Database Roles (Security)
db_datareader
db_datawriter
db_securityadmin
db_owner
Atomic transactions
Either all the actions in a transaction occur or none of them do
Domain
In SQL, describes and constrains values in column
Examples include VARCHAR, CHAR, INT, DECIMAL, MONEY, DATETIME, DATE, etc.
Range
In SQL, explicitly constrains values in column in addition to any datatype domain constraints.
Example: CHECK value on INT column limiting values from 1 to 1000
ALTER Table
Changes table according to instructions within ALTER statements, instead of attempting to CREATING the table again
Data Control Language
Used to grant/revoke database permissions to/from users and groups
Data Definition Language
Used for creating tables, relationships, and other structures
Data Manipulation Language
Used for queries and data modification
SQL persistent stored modules (SQL PSM)
Programming code that may include SQL statements in modules for reuse
ANSI/ISO standard embedding procedural programming functionality into SQL
Transaction control language
Used to mark transaction boundaries and control transaction behaivior
Non-clustered index
Seperation structure is created for index values
Each value points to an actual records storage location of table data
Clustered index
Sort and store data rows based on index key value
One per table that is physically stored in file
Cursors
Extension to result sets that allow processing one row at a time from result set
SQL injection attack
When data from a user is used to modify a sql statement
Inserting SQL data into form fields rather than "clean" values in the aim of gaining access/adversely modifying data inside the database.
Ex: User inputs into "First Name" field in web application th
Object name
Server_Name.DB_Name.Schema.Object_Name
ServerName, DBName, Schema, Object separated with periods.
Ex: Yorktown.SCA_Bird.dbo.tblPerson
Authorizing
Grant user permissions to database objects
Authenticating
Create login for a user
Database security
Ensures that only authorized users can perform authorized activities at authorized times
Limits certain actions on certain users
Database administation
Person or office specific to DBs and their apps
Data administration
Function managing all data assets in an organization
Checkpoint
Point of synchromization between database and changes in memory
Rollback/rollforward recovery
Periodically save database and keep DB change log since last save
Reprocessing recovery
DB goes back to known point, then manually reprocesses workload from there using application (NOT DBMS)
Incrimental backup
Backup changes since previous backups of any type
Differental backup
Backup changes since last full backup
Full backup
Complete backup of everything
How is a function called?
By name from another sql statement or stored procedure
Table valued funtions
Return a table
- can be used in FROM/WHERE/HAVING clauses
Create function definition defines what table columns will be returned
Aggregate functions
Performs a calculation on set of values and returns a single value
Scalar functions
Returns a single value
What may stored procedures invoke?
Triggers
Stored procedures can be called by
User or DB admins
Stored procedures are assigned to
DBs
Stored procedures can be called FROM
Programs written in standard languages
Scripting languages
SQL command prompt
Advantages of stored porcedures
Greater security because they are always stored on the DB server
Decreased network traffic
SQL can be optimized in DMBS compiler
Code sharing
Stored procedures can issue
INSERT, UPDATE, DELETE, and MERGE commands
Triggers can call
Stored procedures
Trigger is called
When INSERT, UPDATE, or DELETE commands are issued
Trigger is assigned to
Table or view`
AFTER trigger
Perform other tasks after standard actio9ns of triggering statement are performed
May not be assigned to views
INSTEAD OF triggers
Override standard actions of triggering statement: INSERT UPDATE or DELETE
Triggers can be used for
Providing default values
Enforcing data constraints
Updating views
Performing referential integrity actions
When trigger is fired, DBMS supplies
Old and new value for update
New values for inserts
Old values for deletions
Triggers can issue
INSERT, UPDATE, and DELETE commands
Concurrent transactions
2+ transactions that appear to users as they are being processed against a database at the same time
In reality, CPU can execute only one instruction at a time
Concurrency control
Ensures that one user's work does not inappropriately influence another user's work
Concurrency problems
Lost updates
Inconsistent reads that leads to lost updates
Serialized transactions
No lost update problems
Locking (ISOLATION)
Transactions are executed sequentially instead of concurrently
transactions are allowed to obtain locks as necessary, but once the first lock is released, no other lock can be obtained
Resource locking
Prevents multiple applications from obtaining copies of same record when record is about to be changed
Way to stop an inconsistent read
Implicit locks
Placed by DBMS
Almost all locking is implicit
Explicit locks
Issued by application program
Lock granularity
Size of a locked resource
- Large granularity is easy to manage but frequently causes conflicts
Exclusive lock (write lock)
Prohibits other users from reading locked resource
Shared lock (read lock)
Allows other users to read locked resource, but they cant make changes to it
Range lock
Applied to set of records
Can prevent inserting, deleting, or updating to set of records
Deadlock (deadly embrace)
When two transactions are waiting on resource that transaction holds
Breaking a deadlock
There are usually algorithms in place to detect a deadlock
Timeouts
DBMS aborts one of the transactions and rolls back partially completed work
Optimistic locking
Assumes that no transaction conflict will occur
Pessimistic locking
Assumes that a conflict will occur
- Locks are issued before transaction is processed, and then the locks are released
Transaction boundary markers
BEGIN TRANSACTION
COMMIT TRANSACTION
ROLLBACK TRANSACTION
Consistent transaction
Transaction either creates new and valid state of data or returns to previous state
Isolation
Achieved by locks and is used to ensure read consistency
Read uncommitted
Specifies that statememnts can read rows that have been modified by other transactiuons but not yet committed
Dirty read may occur
Repeatable read
Species that statements cant read data that has been modified but not yet committed by other transactions
Prevents dirty and non-repeatable reads
Serializable
Statements cannot read data that has been modified but not yet committed by other transactions
Dirty read issue
Read a record that has not been committed
Non-repeatable read issue
2 reads of a row have different values
Phantom read issue
2 reads of a set of records are different in terms of number of rows returned
-User A runs the same query twice.
-In between, User B runs a transaction and commits.
-All the rows in the query have the same value before and after, but different rows are be
INSERT with IdentityID
Inserting into a table with an auto-incrementing identity field (i.e. CustomerID) don't include insert into ID field because the identity will auto increment
INSERT without IdentityID
When inserting into a table with an auto-incrementing identity field (i.e. CustomerID), you can override ID field insert by using WITHOUT IdentityID
Truncate vs. Delete
Truncate is used to remove all data from a table, while leaving
the table structure itself in the database. The SQL TRUNCATE TABLE statement does not use
an SQL WHERE clause to specify conditions for the data deletion�all the data in the table is
always r
ON UPDATE
Trigger constraint determining when the trigger should fire.
Would fire on update of relevant table
ON DELETE
Trigger constraint determining when the trigger should fire.
Would fire on delete of data from relevant table
Global variables
Variables with scope outside of a function or stored procedure, can be accessed from outside.
Examples include @@ROWCOUNT
Local variables
Variables with scope inside a function, trigger, or stored procedure, can only be accessed from within their relevant module.
Examples include @X, @Y, variable declared by a user.
How to call stored procedure/function/trigger
Stored Procedure: EXEC __________
Function: SELECT function_name(_________,_________,______ etc.)
Trigger: SELECT, DELETE, UPDATE
CHECK constraint
Ensures that the value in a column meets a certain expectation
What are the three module types of programs?
User-defined functions, stored procedures, triggers
Server Roles (Security)
dbcreator
securityadmin
serveradmin
sysadmin
Database Roles (Security)
db_datareader
db_datawriter
db_securityadmin
db_owner
Atomic transactions
Either all the actions in a transaction occur or none of them do
Domain
In SQL, describes and constrains values in column
Examples include VARCHAR, CHAR, INT, DECIMAL, MONEY, DATETIME, DATE, etc.
Range
In SQL, explicitly constrains values in column in addition to any datatype domain constraints.
Example: CHECK value on INT column limiting values from 1 to 1000