SQL - Week 1

Null Data

- Values that are indeterminate (you do not know (and probably don't need to know) the value)

Microsoft SQL Server

- Microsoft SQL server is a relational database management system. - Supports structured query language and comes with its own implementation of the SQL language which is *transact-sql (T-SQL)* - Has an integrated environment to handle SQL databases, which is the SQL server management studio (SSMS)

The tempdb Database

- One of the key working areas for your server - The objects within it are temporary -- Whenever you issue a complex or large query that SQL Server needs to build interim tables to solve, it does so in tempdb. -- Whenever you create a temporary table of your own, it is created in tempdb, even though you think you're creating it in the current database. -- Whenever there is a need for data to be stored temporarily, it's probably stored in tempdb.

Defaults

- Provides a default column value for a new record when you did not specify it.

Rules

- Rules provide restriction information about what can go into a table. - Unlike constraints, rules aren't bound to a particular table. - They are independent objects that can be bound to multiple tables

User-defined functions

- Similar to sprocs, except that they: -- *Can return a value of most SQL Server data types.* Excluded return types include text, ntext, image, cursor, and timestamp. -- *Can't have side effects.* Basically, they can't do anything that reaches outside the scope of the function, such as changing tables, sending e- mails, or making system or database parameter changes. - UDFs are similar to the functions that you would use in a standard programming language. - All variables (except table variables used as parameters) passed into the function are passed in by value.

SQL

- Stands for *Structured Query Language.* - A standardized language which is used for managing relational databases. - With SQL, you can modify databases, add, update, or delete rows of data, retrieve subsets of information from a database, etc. - Relational databases like MySQL databases, Oracle, MS SQL Server, Sybase, etc. use SQL. - Queries and other SQL operations are written as statements.

Tables

- The data in an RDBMS is stored in database objects which are called as tables. A table is basically a collection of related data entries. - A table is the most common and simplest form of data storage in a relational database. - Each table definition also contains the metadata (descriptive information about data) that describes the nature of the data it is to contain. Each column has its own set of rules about what can be stored in that column. - A table consists of domain data (columns) and entity data (rows).

The database itself

- The highest-level object -- Most other objects in a SQL Server are children of the database object. - Include at least a set of table objects - An RDBMS, such as SQL Server, may have multiple databases on one server. - System databases: -- master: keeps track of the system as a whole -- model: forms a template for any new database that you create. -- msdb: where the SQL Agent process stores any system tasks. -- tempdb: the key working areas for your server - All of these need to be installed for your sever to run properly

The model Database

- The model on which a copy can be based. - Forms a template for any new database that you create. - You can alter the model database if you want to change what standard, newly created databases look like. - it's a required database and must be left on the system; you cannot delete it.

Rules for Naming

- The name of your object must start with any letter - The name can be up to 128 characters for normal objects and 116 for temporary objects. - Any names that are the same as SQL Server keywords or contain embedded spaces must be enclosed in double quotes ("") or square brackets ([]).

Sequences

- To provide a source of sequential numbers that can be accessed by any number of processes, guaranteeing that no two will retrieve the same next value at the same time. - To produce unique values on demand.

Users and Roles

- User represents an identifier for someone to log in to the SQL Server. - Users, in turn, belong to one or more roles. - Different roles have different rights to perform certain actions in SQL Server

The msdb Database

- where the SQL Agent process stores any system tasks. -- If you schedule backups to run on a database nightly, there is an entry in msdb. - Other major subsystems in SQL Server make similar use of msdb.

Types of DBMS

- Hierarchical DBMS - Network DBMS - Relational DBMS (We focus on this type in this class) -- Contains multiple *tables* of data with rows and columns that relate to each other. - Object-Oriented DBMS

Filegroups

- Every SQL Server database has two operating system files: a data file and a log file. - Data files contain data and objects such as tables, indexes, stored procedures, and views. - Log files contain the information that is required to recover all transactions in the database. - Data files can be grouped together in filegroups for allocation and administration purposes.

The master Database

- Every SQL Server has the master database. - It holds a special set of tables (system tables) that keeps track of the system as a whole. -- For example, when you create a new database on the server, an entry is placed in the sysdatabases table in the master database. - All extended and system-stored procedures are stored in this database. - Since almost everything that describes your server is stored in here, this database is critical to your system and cannot be deleted.

Data Types

- Exact numerics - Unicode character strings - Approximate numerics - Binary strings - Date and time - Other data types - Character strings (See slides 33 - 41 for more details)

Different Types of Conversion

- Explicit and implicit. (See slide 44 for details)

User-Defined Data Types

- *User-defined data types* are based on the system *data types* in Microsoft SQL Server. - *User-defined data types* can be used when several tables must store the same *type* of *data* in a column and you must ensure that these columns have exactly the same *data type*, length, and NULLability.

Views

- A *view* can be described as virtual table which derived its data from one or more than one table columns. - It doesn't contain any data of its own - They can be created using tables of the same database or different databases. - It is used to implement the security mechanism in the SQL Server. -- to control what the user of the view see

DBMS

- A Database Management System (DBMS) is a software that is used to manage the Database. - A DBMS basically serves as an interface between the database and its end-users or programs, allowing users to retrieve, update, and manage how the information is organized and optimized.

RDBMS

- A RDBMS is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd in 1970. - RDBMS is the basis for SQL, and all other modern database systems -- MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

Full-Text Catalogs

- A full-text catalog is a logical container for a group of full-text indexes. - Full-text catalogs are mappings of data that speed the search for specific blocks of text within columns that have full-text searching enabled.

Stored procedure

- A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. - An ordered series of Transact-SQL statements bundled up into a single logical unit. - Sprocs offer several advantages over just sending individual statements to the server: 1.) Are referred to using short names, rather than a long string of text, therefore less network traffic is required. 2.) Are pre-optimized and precompiled, saving a small amount of time each time the sproc is run. 3.) Encapsulate a process, usually for security reasons or just to hide the complexity of the database. 4.) Can be called from other sprocs, making them reusable

Triggers

- A trigger is an object that exists only within the framework of a table. Triggers are pieces of logical code that are automatically executed when certain things (such as inserts, updates, or deletes) happen to your table. - Mainly used for either copying data as it is entered or checking the update to make sure that it meets some criteria.

Indexes

- An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. - An index works much like the index does in the back of an encyclopedia. There is some sort of lookup (or "key") value that is sorted in a particular way - Once you have the key, you can look up the actual information you were after. - Classification: -- Clustered: The table on which the clustered index is based is physically sorted according to that index. -- Non-clustered: This kind of index points to some other value that will let you find the data.

Constraints

- Confine the data in your table to meet certain conditions. - A constraint exists only within a table. - Both constraints and triggers are possible solutions to data integrity issues.

Data Type Conversion

- Data types can be converted in the following scenarios: - When data from one object is moved to, compared with, or combined with data from another object, the data may have to be converted from the data type of one object to the data type of the other. - When data from a Transact-SQL result column, return code, or output parameter is moved into a program variable, the data must be converted from the SQL Server system data type to the data type of the variable.

Diagrams

- Database diagrams graphically show the structure of the database. -- tables -- the column names in each table -- the relationships between tables - Using database diagrams you can create and modify tables, columns, relationships, and keys. - Additionally, you can modify indexes and constraints.

The transaction log

- Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction. - The transaction log is a critical component of the database. If there is a system failure, you will need that log to bring your database back to a consistent state. - Although the data is certainly read in from database file itself, any changes you make don't initially go to the database itself. Instead, they are written serially to the transaction log. - When the database is issued a checkpoint, all the changes in the log are propagated to the actual database file. - The database is in a random access arrangement, but the log is serial in nature. While the random nature of the database file allows for speedy access, the serial nature of the log allows things to be tracked in the proper order.

What makes up a SQL Server Databse?

Data and more! The functions of RDBMS: - store data - manage data - restricting the data that can go into the system - facilitating getting data out of the system.