Seminar 7 - Database Design

Seminar 7 ========= Hierarchical data structures are very common in the real life: file system, menu, organization, etc. Tree like hierarchical data can be stored as an adjacency list. An adjacency list is a hierarchical data representation where each element (node) is stored in a table, and the...

Seminar 7 ========= Hierarchical data structures are very common in the real life: file system, menu, organization, etc. Tree like hierarchical data can be stored as an adjacency list. An adjacency list is a hierarchical data representation where each element (node) is stored in a table, and the relationships between elements are indicated using references. This structure is commonly used to represent hierarchical data in a relational database, where each element contains a link to its parent\'s identifier. XML stands for eXtensible Markup Language, was designed to store and transport data, in a both human - and machine -readable format. XML documents have a well -defined hierarchical structure. Hierarchical structure means that elements are organized in a tree -like fashion, with a single root element at the top. Each element is enclosed within an opening tag and a closing tag Elements can contain other elements, creating a parent -child relationship. This is what gives XML its hierarchical nature. An XML has exactly one root, and the other elements nested under this root. Elements define the structure and content within an XML document. They are enclosed within opening and closing tags, e.g., \content\. Elements can be nested within each other, creating a hierarchy. Attributes provide extra information about an element Attributes provide extra information about an element. They are always located within the element\'s opening tag. Attributes are written as name-value pairs, e.g.,\ \content\ FOR XML AUTO - Each column will be an attribute FOR XML PATH - Creates an XML document where each record is an element and each column is a nested element for a particular record. JSON (JavaScript Object Notation) is a lightweight data interchange format that is easy for humans to read and write, and easy for machines to parse and generate. It is a widely used format for structuring and transmitting data over the internet and is supported by a variety of programming languages. JSON data is represented as a collection of key-value pairs, where keys are strings and values can be strings, numbers, objects, arrays, boolean values, or null. The key-value pairs are enclosed in curly braces {}, making up an object, and arrays are ordered lists of values enclosed in square brackets \[\]. This hierarchical structure allows for complex data representation. Data types: string, number (integer or decimal), boolean (true/false), NULL (null), complex datatypes: array, object FOR JSON AUTO - Each column will be a key and each value will be a value FOR JSON PATH - Produces the same result then FOR JSON AUTO Seminar 7 recap =============== Stands for eXtensible Markup Language Defines and stores data in a shareable manner: used for information exchange between systems Tree-like hierarchy structure - Has exactly one root - Every item has exactly one parent - Every element has either additional child elements or a value. - Elements: - the building blocks of the xml. - An element starts with an opening element and ends with a closing element - Can contain a value or other elements - Attributes: - Extra information about an element - Key-value pairs included in the opening element Can be done with FOR XML AUTO/PATH placed right after the query FOR XML AUTO: every row of the query becomes an element, and the columns of the query become attributes for those elements. FOR XML PATH: every row of the query becomes an element, and the columns of the query become child elements for those elements. - The default elementname is \"Row\", it can be defined: FOR XML PATH(\'DesiredElementName\') - By default there is no root element, it can be defined: FOR XML PATH, ROOT(\'DesiredRootName\') - Naming columns is possible - A column name starting with @ instructs interpreter to produce attribute instead of element (Attributes must be specified first) - A column name including \"/\" instructs interpreter to produce a hierarchy JSON Stands for JavaScript Object Notation JSON data is represented as a collection of key-value pairs - An object is a collection of key-value pairs, enclosed by curly braces - An array is a collection of same-type items that can be atomic values (key-value pairs) or objects, enclosed by brackets Under SQL, JSON output can be produced with the FOR JSON AUTO or FOR JSON PATH - Produce an object for every row, and the objects form an array - FOR JSON PATH and FOR JSON AUTO without parameters produce the same results - Naming columns is possible, nested objects can be formed with dotted syntax, e.g. Address.Postalcode - By default, NULL values are not included in the output, this behavior can be overridden with INCLUDE\_NULL\_VALUES option - An extra root object can be included with the ROOT(\'desiredrootname\') option Seminar 8 ========= Subqueries can be used in the select statement as a scalar valued expression - One column - 0 or 1 row (in case of no rows it returns NULL) - Can be self contained or correlated Subqueries can be used in the FROM clause also. These are so-called table-valued subqueries. - Table alias must be used - Every column must have a name - Column names must be unique - Correlation is not possible - Join works as usual A Common Table Expression is a table valued subquery which can be used in the statement even multiple times. Offers better readability, reusability and makes possible recursive queries (see later at hierarchical data the last line before a CTE must be ended with ; The return value of an xml query contains exactly one row an one column → It can be used as a subquery in the select clause to query related data By default, the type of the return value of the subquery is nvarchar(max), it should be converted to XML data type. It can be done with cast or \"TYPE\" keyword The column alias (in this case: Details) for the subquery means the root element name. If you omit, there will be no root. The Parameter for the for XML PATH can be left empty. In this case there will be no enclosing element for the rows. If we have XML data (for example, in a file) and we want to transform them into relational data, we have to use XML MSSQL datatype and it\'s methods Declaration Assign value: - Constant value can be assigned with SET and SELECT - Value from a query can be assigned with SELECT Print value: - SELECT \@variable → Results in grid - Print \@variable → Results on the Messages pane XML data can be stored as character data, but to take advantage of the data structure, xml data type should be used A variable with XML data type has some methods which can be used for XML-specific operations. -.query(path) - SELECT \@BooksXML.query(\'/catalog/book/author\') -.value(\'(path)\[number\]\', \'data type\') - SELECT \@BooksXML.value(\'(/catalog/book/author)\[1\]\', \'varchar(100)\') -.nodes(path) as Table(Column) -.nodes usually used in the FROM clause, the return value van be used with XML methods only - Table and Column is an alias which can be used in the select statement - SELECT\ Book.value(\'\@id\', \'VARCHAR(50)\') AS BookID,\ Book.value(\'(author)\[1\]\', \'VARCHAR(100)\') AS Author,\ Book.value(\'(title)\[1\]\', \'VARCHAR(100)\') AS Title,\ Book.value(\'(genres)\[1\]\', \'VARCHAR(100)\') AS Genre\ FROM \@BooksXML.nodes(\'/catalog/book\') AS T(Book); The \"path\" is case-sensitive! If you are trying to find the general manager of a company (assuming it\'s not publicly known), you can start by asking an employee about their supervisor. Then, you continue to ask each supervisor the same question until you reach someone who does not have a supervisor. He/she will be the general manager. - The same thing happens at every step. - Every step uses the result of the previous step. - There is a stop condition which terminates the process This method is named \"recursion\", which is a basic concept in computer science and - among other purposes - can be used to work with hierarchical data.\ A recursion is a programming or mathematical concept where a function or algorithm calls itself to solve a problem by breaking it down into smaller, similar subproblems. Starting point\ UNION ALL is compulsory in case of recursive CTE-s\ Recursion: use reports to from previous step\ The recursion ends when no data found Seminar 8 recap =============== Subselects in the SELECT clause - Scalar and single valued: One column, 0 or 1 row - If no rows returned by the subquery, the results is NULL - Can be self contained but usually correlated Subselects in the FROM clause - Table valued subqueries: multiple columns multiple rows - Must be aliased - Correlation is not possible - Join works as usual - Column names must be setted up and unique (just like a table) Common Table Expressions - Can be declared right before the query with WITH clause - Can be considered as an alternative form of table valued subqueries, but reusable within the query. The rules are the same. - If runs in a batch (multiple commands/queries at the same time), the line right before the WITH should be ended with semicolon The result of a FOR XML AUTO/PATH query is scalar and single-valued (since it wraps up all the selected expressions into one single XML string) - Can be used in SELECT statement - Can be used for produce subhierarchies Rules - The return value by default a string (varchar): casting or the TYPE keyword should be used in the FOR XML for getting XML - If you want to produce one parent element for every row of the subquery row, give the element name as usual - An enclosing element (\"subroot\") can be generated for the subquery by assigning an alias name to the return value. It can also be achieved by the ROOT keyword within the subquery. Variable names start with @\ Declaration is compulsory, it can be done with the DECLARE keyword\ DECLARE \@variable\_name variable\_type\ Value assignment: - If the value comes from a query: SELECT \@variable\_name=value FROM tablename - Else: you can use the SET also: SET \@variable\_name=value All the lines which declare and use the variable should be run in a batch, because of the variable\'s scope For storing and querying XML data, XML Microsoft SQL Server Datatype can be used Methods for getting values from an XML-typed variable:\ SELECT clause methods: - Query(\'path\'): get all the elements and attributes under the given path. It has an XML-typed return value. - Value(\'(path)\[n\]\', \'type\'): get the value(s) under the given path, then get the nth and cast it as type. FROM clause method: - Nodes(\'path\') as T©: can be used along with other methods (query, value). It gets the elements and attributes under a given path as XML and returns it as an XML-typed column (C) which can be used by (for example) the Value method, so you can convert your XML into a relational format Recursion is a programming or mathematical concept where a function or algorithm calls itself to solve a problem by breaking it down into smaller, similar subproblems. In Microsoft SQL Server, recursion can be implemented using Common Table Expressions (CTEs). A recursive CTE consists of a query connected by UNION ALL, where the results derived from the CTE itself serve as the filtering criteria. Recursion ends when there are no results Seminar 9 ========= Because the \"Student\" user has no modification-level access to the regular databases on the faculty server, now we get to know the temporary tables. TempDB is a system database in Microsoft SQL Server. It is primarily used to store temporary data, such as temporary tables, indexes, and intermediate result sets generated during query processing. If we create a table with the name starting with \#, the table will be created in the Temporary database and can only be seen by the creating session. It disappears when that session ends. If we create a table with the name starting with \#\#, the table will be created in the Temporary database and can be seen by every session and disappears when all the referencing sessions end. In the context of databases, a session refers to a period during which a user or application interacts with a database. It starts when a connection is established and continues until the connection is terminated. During a session, various operations, queries, and transactions can be performed on the database. For creating tables CREATE TABLE command is used NULL means the column can hold NULL values\ NOT NULL is the opposite\ NULL is the default behaviour For modifying existing tables, the ALTER TABLE command is used - To add a column to an existing table: - ALTER TABLE table\_name ADD column\_name datatype \[NULL/NOT NULL\] - To modify a column in an existing table: - ALTER TABLE table\_name ALTER COLUMN column\_name datatype \[NULL/NOT NULL\] - To delete columns from an existing table: - ALTER TABLE table\_name DROP COLUMN\_name colum For dropping a table, the DROP TABLE command is used - DROP TABLE \[IF EXISTS\] table\_name Rules for naming tables and columns: - The table name must be unique, cannot be started with a number, only letters, numbers and underscores can be included - The column names in a table must be unique - For multipart table and column names, brackets should be used A constraint in a database is a named rule or condition applied to a set of one or more columns in a table, ensuring the data meets specific criteria, such as uniqueness or referential integrity. - Column level: - Create table Table\ (column datatype \[constraint constraint\_name\] constraint\_type constraint\_definition) - Table level: - Create table Tables\ ( \, constraint constraint\_name constraint\_type constraint\_definition) Types and usage: - NULL/NOT NULL: attach the NULL-NOT NULL keywords to column datatype at column definition. The default is NULL. Cannot be defined table level. - UNIQUE: only the keyword needed also - DEFAULT: attach the DEFAULT keyword and the desired default value. Cannot be defined table level. - CHECK: give a rule between parentheses. - PRIMARY KEY: give the desired column names between parentheses - FOREIGN KEY:\ FOREIGN KEY(colname) REFERENCES OtherTable(OthertableColname) - FOREIGN KEY Constraints are not supported in the case of \#tables so we will check this out later The primary key is often implemented by using monotonically increasing integer values. Most of the SQL servers are capable of automating such value assignments even without a PK CREATE TABLE table\_name ( col\_name INT IDENTITY\[(start\_value, increment)\]\ ALTER TABLE table\_name ADD column\_name datatype IDENTITY\[(start\_value, increment)\] Rules: - Only one can be defined per table - Cannot be used for modifying existing column - Identity columns are often defined as integer columns, but they can also be declared as a bigint, smallint, tinyint, or numeric or decimal as long as the scale (precision) is 0. Insert constant values: - Insert \[INTO\] table\_name \[(column name list)\] VALUES (value1, value2,...)\[, (value1, value2,...)\] Insert values from a result set: - Insert \[INTO\] table\_name \[(column name list)\] Select.... - The columns of the result set should be at least type-compatible than the columns should be inserted Save a query result set into a table with table creation - Select...\ INTO new\_table\_name\ From... - Columns must be named and the names must be unique If there is an Identity column, column name list should be used - Update columns with constant values for all rows - UPDATE table name SET column=value\[, column2=value2\] - Update columns with constant values for selected rows & filtering based on the updated table - UPDATE table name SET column=value\[, column2=value2\]\ WHERE \ - Update columns with constant values for selected rows & filtering based on multiple tables - UPDATE table name SET column=value\[, column2=value2\]\ FROM \\ WHERE \ \[GROUP BY\] \[HAVING\] Delete all rows from a table: - TRUNCATE TABLE tablename - DELETE FROM tablename Delete rows with filtering: DELETE FROM WHERE Seminar 9 recap =============== Recursion is a programming or mathematical concept where a function or algorithm calls itself to solve a problem by breaking it down into smaller, similar subproblems. In Microsoft SQL Server, recursion can be implemented using Common Table Expressions (CTEs). A recursive CTE consists of a query connected by UNION ALL, where the results derived from the CTE itself serve as the filtering criteria. Recursion ends when there are no results. A table with a name starting with single \# will be created in the temporary database and disappears when the creating session ends. Only the creating session sees this table. A table with a name starting with double \#\# will be created in the temporary database and disappears when all the referecing sessions end. This type of tables are visible for all users. Syntax: Column\_name datatype \[NULL/NOT NULL\] \[IDENTITY(start, offset)\] \[\\] Considerations: - Constraints: rules for the data accepted for the given column - DEFAULT default\_value, CHECK (rule), UNIQUE, PRIMARY KEY, FOREIGN KEY - Nullability is the default (except for columns defined with a PRIMARY KEY constraint), meaning that a column defined without NOT NULL can hold NULL values. IDENTITY columns - Are filled automatically with monotonously increasing numbers - An existing column cannot be set as identity with Alter Table.... Alter Column - Only one is allowed per table - Only whole number types are allowed to be set as identity A constraint that relies on more than one column value cannot be set in the column specification. Constraints can be defined on column level (see previous slide) and table\_level (except NULL/NOT NULL and DEFAULT). Definition: CONSTRAINT constraint\_name constraint\_type (constraint\_settings) Constraint settings by constraint type: - CHECK: Providethe rule as a boolean expression. - UNIQUE: List the columns in a comma-separated list - PRIMARY KEY: Specify the columns in a comma-separated list - FOREIGN KEY: Specify the column name(s) to be a foreign key, and attach: REFERENCES referenced\_table\_name(referenced\_column\_names) Seminar 10 ========== On-Premise Software: If a company installs and runs software on its own servers or computers located within its facilities, it is said to be using on-premise software. This is in contrast to cloud-based solutions where the software is hosted and accessed over the internet. Microsoft SQL Server can be deployed as an on-premise database management system. When organizations choose to install and run Microsoft SQL Server on their own servers, within their own data centers or on-premise infrastructure. In case of an on premise MSSQL Server, databases can be created with the CREATE DATABASE command. - CREATE DATABASE MyFirstDatabase - Alternatively, you can right-click on the \"Databases\" branch and add a database with wizard by clicking on the \"New Database\" item. In case of AzureSQL databases CREATE DATABASE also works, but it is a bit dangerous because the database will be created with the default settings (which can be very credit-demanding), although it can be modified within the database properties. - It is recommended to use Azure portal to set up new databases Both on premise and Azure SQL server support multiple databases on the same server but don\'t forget that under AzureSQL a new database means additional cost IF Boolean\_expression\ \[BEGIN\]\ sql\_statement \| statement\_block\ \[END\]\ \[ELSE\ \[BEGIN\]\ operations\ \[END\]\] In T-SQL (this is the name of the scripting language under MSSQL), BEGIN and END are used to define a code block. You have to use them if you have more than one operation. WHILE Boolean\_expression\ \[BEGIN\]\ sql\_statement \| statement\_block \| BREAK \| CONTINUE\ \[END\] BREAK is used for exit the loop\ CONTINUE is used for continue the execution with the next iteration A cursor is a database object that allows for the sequential processing of rows in a result set. It provides a way to iterate through the rows returned by a query, enabling row-level operations. Cursors are often used when it\'s necessary to perform complex, row-based logic that goes beyond the capabilities of SQL clauses. A user-defined function (UDF) in Microsoft SQL Server (MSSQL) is a custom, reusable code block created by users to perform specific tasks, returning a single value or a table result - Scalar valued function - Table valued function A stored procedure is a precompiled and stored collection of one or more SQL statements or procedural logic, which can be executed as a single unit. It is stored in a database and given a name, and it can be called multiple times with different parameters. They can return value by out parameters, or by the results of their operations. (e.g. a query result set) By default, the parameters declared for a stored procedure are input parameters, and you cannot alter the value of an input parameter. It can cause an error message (see below) or simply doesn\'t work. As we have seen, the CREATE PROCEDURE and the CREATE FUNCTION commands are used for creating a Function/Procedure - Since they are database objects, the name must be unique - If you want to modify an existing Function or Procedure, ALTER PROCEDURE and ALTER FUNCTION commands must be used - In case of procedures and functions, under MSSQL, CREATE OR ALTER can be used. This commands creates the proc/func if it doesn\'t exists and modifies if exists. - For deleting, DROP PROCEDURE and DROP FUNCTION can be used. Seminar 10 recap ================ Conditionals can be created with IF... ELSE. If the statement to be executed consists of more than one statement, statement blocks must be formed with BEGIN \... END.\ For a single statement, the use of BEGIN\...END is optional. LOOPS can be created with WHILE - The BREAK statement causes an exit from the loop, and the execution continues with the next statement after the loop. - The CONTINUE statement interrupts the current iteration, and the execution continues with the next one. If row-level operations are to be performed on the result set of a query in an SQL script, cursors must be used. Cursors enable iterating through the result set and performing the desired operations\ For example, print out the 3rd, the 5th and the 11th. genre name. There is no list indexing option like in Python, so we have to use a cursor Seminar 11 ========== Database design steps: 1\. Determine the purpose of the database 2\. Find and organize the required information 3\. Split the information into tables 4\. Turn information items into columns 5\. Specify primary keys 6\. Set up the table relationships 7\. Apply the normalization rules to avoid different types of anomalies In the world of designing databases, normalization is like a careful process to make sure the structure of the database works well for different types of questions and doesn\'t have any problems that could mess up the data. These problems, known as anomalies, might happen when you add, update, or delete information, and normalization helps prevent them. Anomalies: - Insertion anomaly: There are situations in which certain facts cannot be recorded at all. For example, if there is no separate Artist table, and the Artist name is stored directly in the Album table. In this case, we can\'t store Artists without albums. - Update anomaly: If the same information is stored in multiple rows, updating the data can cause logical inconsistencies. Let\'s say we have no genre table, we store genres as text in the Track table. If we try to change \"Rock and Roll\" genre to \"Rock & Roll\" genre, and forget to update all the rows it leads to inconsistencies. - Deletion anomaly: Under certain circumstances, the deletion of data which representing certain facts causes the deletion of data representing completely different facts. By continuing the previous example, if we delete all the tracks from a given genre, the genre name also disappears from the database. In 1970, Codd introduced the concept of normalization and, what is now referred to as the first normal form (1NF). Subsequently, Codd further defined the second normal form (2NF) and third normal form (3NF) in 1971. While there are additional normal forms, such as Boyce-Codd normal form (BCNF), achieving the third normal form (3NF) is typically sufficient for our purposes. Rule 0NF 1NF 2NF 3NF No duplicate rows ✓ ✓ ✓ ✓ Scalar columns X ✓ ✓ ✓ Every non-prime attribute has a full functional dependency on a candidate key X X ✓ ✓ Attributes depend only on whole candidate keys (non-transitive) X X X ✓ 1NF: scalar columns 2NF: Every non-key attribute should depend on the entire key. In our new genre table, both \'mediatype\' and \'trackid\' together form a candidate key. The candidate key consists of \'Album Title\' and \'Track\' together. However, \'Artist\' is dependent only on \'Album Title.\' 3NF: Dependencies are non-transitive. The track is dependent on the album, and the price is dependent on the track. The candidate key is the track itself, defining the genre. However, the \'genrename\' is only dependent on the \'genreid.\' Normalizing 1:N relations: - This table suffers from anomalies. For instance, if we delete Bill Clinton, we lose the role \'Politician.\' Similarly, if we attempt to change the \'Singer\' role to \'Musician\' and forget to update John Legend, this operation introduces inconsistencies. - Ideally, we should have a \'Roles\' table and a \'People\' table with a one-to-many (1:N) relationship between them. - We can extract unique role names into the \'Roles\' table and add an \'ID\' column. - Add a RoleId column to the People table and update it. Normalizing M:N relations: - If we have multiple values in a column 1NF is not satisfied, and the relationship is M:N. - First we need a list of individual genres. For this purpose, string\_split function can be used. - Add a primary key - Create and populate the junction table - Remove the columns containing stacked genres Change the column names to PascalCase and with a Select... Into create the Movies table - PascalCase means the first letter of every word is Uppercase, for example: StreamingProviderId - camelCase means the first letter of every word is Uppercase except the first, for example streamingproviderId - snake\_case means we use underscore between words, for example: Imdb\_Id - Now, we can set the primary key for the \'Movies\' table. To do this, let\'s explore a new ALTER TABLE syntax:\ ALTER TABLE \ add CONSTRAINT \ \ - After the \'Movies\' table has been created, you can set the primary key with the following command:\ Alter table Movies Add Constraint PK\_Movies PRIMARY KEY(ImdbID) Seminar 11 recap ================ We have been working on different normalisation tasks - Import data, examine input table - Obtain and set datatypes - Try to find key candidates - Extract tables: to get rid of redundancy - In case of 1:N, attach a foreign key and set appropriate values - In case of M:N, create and fill up a junction table - Insert data - Set relations Split up stacked column values:\ SELECT value FROM table\_name CROSS APPLY STRING\_SPLIT(table\_column\_to\_be\_splitted, \) Constraint types: - Keys: Primary Key, Foreign key - Value restrictions: NULL/NOT NULL, Check, Unique - Value substitution: DEFAULT Creation opportunities: - At table creation: - Column level: if the constraint is only based on one column. Can be named directly or let SQL server assign an automatic name. DEFAULT and NULL/NOT NULL can be only set on column level - Table level: must be named directly. - After creation - ALTER TABLE \ ADD CONSTRAINT Deletion opportunities - If the table dropped, all its constraints are also dropped - Constraints can be dropped selectively with ALTER TABLE \ DROP CONSTRAINT \ - NULL/NOT NULL can be set only with ALTER TABLE \ ALTER COLUMN - If the Constraint name was assigned automatically, it can be seen, for example, in the object explorer Seminar 12 ========== Before we start to create tables, it is recommended to decide what naming conventions will be used. - The primary key constraints usually have a name starting with PK\_ (e.g. PK\_Genre) - The names of FOREIGN KEY constraints usually start with FK\_, followed by the names of the source and target tables. (eg. FK\_Track\_Genre) A junction table usually has a name which consists of the names of the two connected tables Column naming conventions: - PascalCase means the first letter of every word is Uppercase, for example: StreamingProviderId - camelCase means the first letter of every word is Uppercase except the first, for example: streamingproviderId - snake\_case means we use underscore between words, for example: Imdb\_Id GO is a command used by SQL Server Management Studio and other SQL script tools. It\'s not part of the SQL language itself but helps in organizing and executing SQL statements effectively. When we run a script or a selected part of our script in SSMS, it\'s like running a \"batch\" of commands. However, sometimes it\'s not suitable because, for instance, after creating a table with \`SELECT INTO\`, the next statement might not \"see\" the table we just made. Using \`GO\` helps separate these batches, making sure each part works as expected. So GO is like saying, \"Pause here, finish what we started, and then continue.\" Where to use: At your current level of knowledge (coming from this course), essentially, GO can be used after every Data Definition Language (DDL) and Data Manipulation Language (DML) statement. (However, as we delve into more complex topics, there are more complex usage scenarios to consider -- next year) Don\'t use GO after variable declaration. If you declare a variable, don\'t use go until you have finished the usage of the variable. This is because the variable disappears when the batch ends, and the same applies to \'GO\'. If we intend to run our script repeatedly, it is necessary to ensure the deletion of tables created by the script because, during the second run, attempting to create a table that already exists causes an error. This is where the \'drop table if exists\' statement comes into play; it drops the table if it exists and does nothing if not. Usage: DROP TABLE IF EXISTS It is also important to make sure that tables are dropped in the correct order: first the tables that are referencing with foreign keys and then the referenced tables. View is a \"traditional\" SQL server object, that can be used to save query definitions and after creation it can be used as a table. Declaration:\ Create View \\ AS\ \ Usage: Select \* from \ Limitations: - Columns must be named, and the column names must be unique - A view cannot contain Order by (except if we use TOP also)