Lesson 1

This module contains information about queries that are needed as a basis for the rest of the modules in this course.
Depending on your prior experience with SQL Server 2012, some of this information may already be familiar to you.

In Microsoft SQL Server 2019, data retrieval can be performed through either single queries or batch queries. Understanding the differences between these two methods is key to optimizing the performance and efficiency of database interactions.

1. Single Queries A single query refers to executing a single SQL command. It involves a single round trip to the database, where the command is processed and the results are returned.
   • Advantages: Single queries are straightforward and easy to construct, understand, and debug. They are ideal for simple operations where you're dealing with a small amount of data.
   • Disadvantages: Executing many single queries sequentially can result in high latency, as each query requires a separate round trip to the database. This can be inefficient when processing a large number of operations.
2. Batch Queries: Batch queries refer to the execution of multiple SQL commands grouped together and sent to the database in a single "batch". SQL Server processes the entire batch of commands and then returns the combined results.
   • Advantages: Batch queries reduce the number of round trips to the database, which can significantly improve performance, particularly when dealing with a large number of operations. They can offer transactional control if you want all commands in a batch to succeed or fail as a unit. If one command fails, the entire batch can be rolled back, maintaining data consistency.
   • Disadvantages: Batches can be complex to construct, understand, and debug, especially if they contain many commands. Batches lock resources for a longer time, potentially leading to contention issues in a multi-user environment. If an error occurs during the execution of a batch, SQL Server stops the execution of the remaining commands in that batch.

Comparative Summary:

1. Performance: Batch queries generally provide better performance for large numbers of operations because they reduce network round trips. However, single queries may be more performant for small operations due to their simplicity.
2. Complexity: Single queries are simpler and easier to handle, while batch queries can be complex, especially when they involve many commands.
3. Error Handling: Single queries provide straightforward error handling as each query is independent, while an error in a batch query can terminate the execution of the entire batch.
4. Transactional Control: Batch queries provide greater transactional control, allowing multiple operations to be treated as a single unit, which can be crucial for maintaining data consistency.

Deciding whether to use single queries or batch queries will depend on the specific needs of your project, including the volume of data, the complexity of operations, and the performance requirements.

After completing this module, you will be able to

1. Describe how to execute your queries
2. Compare and contrast batch queries with single queries
3. Describe the purpose of and how to use system catalogs in your queries
4. Write logic within your queries
5. Describe how to construct SQL statements dynamically

In SQL Server, a view is a virtual table based on the result-set of an SQL statement. Views are important database objects that have several characteristics:

1. Virtual Tables Views do not store data physically; they are virtual tables that provide a result set of rows and columns from one or more tables.
2. Security Mechanism Views can be used as a security mechanism to restrict access to the underlying base tables. Users can be granted permission to access the view without having direct access to the base tables.
3. Simplified Complexity Views can encapsulate complex queries with joins and calculations, presenting a simple interface to users or applications. This simplification can make data more understandable and accessible.
4. Data Abstraction Views provide a level of abstraction; they can hide the complexity of data, such as the joining of multiple tables and the inclusion of business logic.
5. Updateable Some views are updateable, meaning you can use them to insert, update, or delete data, which will then be reflected in the base tables. However, there are restrictions; for instance, an updateable view must include the primary key of the table it is derived from, and it cannot contain certain complex SQL operations like JOINs, DISTINCT, GROUP BY, etc.
6. Consistency Views can ensure a consistent, unchanged interface to underlying data, even if the schema of the underlying tables changes (to a certain extent).
7. Computed Columns Views can include computed columns that do not exist within the base tables. These columns can use functions to calculate values.
8. Indexes Indexed views, also known as materialized views, can improve query performance. SQL Server can create a unique clustered index on a view, which physically stores the view result set in the database, much like a table.
9. Reusability Views can be used in other database operations and in multiple applications, reducing the need to define the same query logic repeatedly.
10. Compatibility Views can help achieve compatibility with older applications if the structure of underlying tables changes, by maintaining the original interface in the view.
11. Logical Structure Although views do not store data, they can turn complex queries into a simple logical structure which can be treated as if it were a table.

Each view is a query stored as an object in the database, which is why they are sometimes referred to as "named queries." Views can be very powerful tools in SQL Server for both database administrators and developers.

There are a number of topics in SQL Server that depend on other knowledge, which is a challenge in organizing a course like this. For example, there are things you are about to learn how to do in queries that would be easier if you knew about

1. user-defined functions,
2. or scripting, or
3. variables,

but at the same time it is difficult to teach about functions without knowing a few T-SQL statements to put in there. Besides, some of these statements are things that might help a beginning user see some real possibilities, so I am going to start in on some more interesting things here as an introudction. Some of the concepts in this module are going to challenge you with a new way of thinking. You already had a taste of this dealing with joins, but you have not had to deal with the kind of depth that I want to challenge you with in this module. Even if you do not have that much procedural programming experience, the fact is that your brain has a natural tendency to break complex problems down into their smaller subparts (sub-procedures, logical steps) as opposed to solving them whole, for example as a set. While SQL Server 2012 supports procedural language concepts now more than ever, my challenge to you is to try to see the question as a whole first. Be certain that you cannot get it in a single query.
In the next lesson, you will learn how to execute your queries.