10 Questions
What is a requirement for creating a function in SQL Server?
The function name cannot begin with a special character
What is the only type of statement that can operate with functions in SQL Server?
SELECT statements
What happens when a function is called in SQL Server?
It is compiled
What is a requirement for functions in SQL Server?
They must return a value or result
What type of functions are defined by the system in SQL Server?
System functions
What is an example of a built-in string function in SQL Server?
SUBSTRING
What is a characteristic of user-defined functions in SQL Server?
They are user-created
What type of function can be used to calculate values and manipulate data in SQL Server?
System function
What statement cannot be used in functions in SQL Server?
CATCH statement
What is a benefit of using built-in functions in SQL Server?
They save time while performing specific tasks
Study Notes
Transaction Control
- Successful statements are committed, while failed statements are rolled back
- Implicit Transaction Mode: each DML statement is automatically committed or rolled back
- Explicit Transaction Mode: allows defining the beginning and end points of a transaction
Transaction Control Commands
- BEGIN TRANSACTION: indicates the start of a transaction
- COMMIT: saves changes permanently to the database
- ROLLBACK: cancels modifications and reverts to the previous state
- SAVEPOINT: creates a point in a transaction that can be rolled back to
- RELEASE SAVEPOINT: removes a savepoint
- SET TRANSACTION: gives a transaction a name and defines its properties
Auto Rollback Transaction
- If an error occurs during a transaction, no modifications are made to the database
- Remaining statements in the transaction are not executed
Triggers
- Special type of stored procedure that runs automatically in response to an event
- Types of triggers:
- DDL triggers: respond to DDL events (CREATE, ALTER, DROP)
- DML triggers: respond to DML events (INSERT, UPDATE, DELETE)
- Logon triggers: respond to logon events
Advantages of Triggers
- Protect data integrity
- Enforce referential integrity
- Keep tables in sync
- Useful for event logging and auditing
Stored Procedures
- Combination of SQL statements that perform a specific task
- Advantages:
- Code reusability
- Faster execution
- Reduces network traffic
- Improves data security
- Easy to maintain
Stored Procedure Syntax
- CREATE PROCEDURE procedure_name AS BEGIN sql_statement END
- Parameters are added as comma-separated arguments after the procedure name
Functions
- Database objects that contain a set of SQL statements to perform a specific task
- Functions always return a single value or a table
- Rules for creating functions:
- Must have a name that doesn't start with a special character
- Can only use SELECT statements
- Must return a value
- Can only use input parameters
- Cannot use TRY and CATCH statements
Types of Functions
- System Functions: built-in functions supported by the server (e.g. AVG, COUNT, SUM, MIN, DATE)
- User-Defined Functions: created by the user to perform a specific task
This quiz covers different SQL transaction modes, including auto-commit, implicit and explicit transaction modes, and their uses in database management.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free
