Beginning T-SQL Flashcards
What is T-SQL
It is Microsoft implementation of ANSI SQL.
How is T-SQL used?
It is used to extract or modify the data stored in a SQL Server database.
What are the 5 main components of T-SQL
Data Definition Language (DDL) Data Manipulation Language (DML) Data Control Language (DCL) Transaction Control Language (TCL) Control-of-flow statements
What is SQL Server?
It is Microsoft’s Relational Database Management System (RDBMS)
What is the purpose of and RDBMS?
It stores data in tables according to the relational model
What is a SQL Server Database?
It is a container that holds several types of objects in organized fashion
What does ERP means?
Enterprise Resource Planning System
Application vs ERP
Usually, and application uses one database; while an ERP uses one database for all enterprise applications in a coorporation.
What are the most common 10 objects in a database?
Table, Views, Stored Procedures, User-defined functions, Indexes, Constantes, Triggers, Types, Plan Builds, Sequences, Synonyms, Assemblies.
What is the purpose of a table?
It holds the data that we store and extract.
What is a View?
It is a stored query definition.
What is the purpose of a View?
To simplify writing T-SQL statements or to secure data access.
What is a Stored Procedure?
It is a Script that can include queries, DDL, statements to create/modify objects, and programming logic.
What type of data can a Store Procedure return?
Tabular data
What is a user-defined function?
It is similar to a store procedure, but they cannot affect anything outside of the function.
What type of data can a User-Defined Function return?
Tabular Data or a Single Value
What is an Index?
It is a structure that assists the database engine when locating rows.
What are Constraints?
Rules controlling the behavior of tables and columns, and the data that can be stored in a column.
What are Triggers?
It is a special type of stored procedure that fires when something happens in the database.
What are two examples that can activate a trigger?
When a record is inserted
When an object is created
What is a Type?
Each columns in a db has rules of what type of data it can contain
What are Plan Guides?
It is an advanced feature that overrites the SQL Server behavior for a particular query.
What are Sequences?
They are containers holding incrementing numbers
What are Synonyms?
They are Nicknames or Aliases for database objects
What are Assemblies?
They are referenced to database objects created in a .Net language using CLR Integrations.
What is CLR Integration?
Common Language Runtime (CLR) Integration
Normalization
It is a set of Rules and Principles that can be applied to a relational table to have Efficient use of Disk Space and Efficient Manipulation of Data.
