Queries, Cursors, Views  «Prev  Next»
Lesson 2Queries, Cursors, and Views Prerequisites
ObjectivePrerequisites for taking this course.

Prerequisites for Queries, Cursors, and Views in SQL-Server 2019

To embark on the journey of mastering queries, cursors, and views in SQL Server 2019, certain prerequisites are essential. These foundational elements equip you with the necessary skills and knowledge to delve into the more advanced aspects of SQL Server effectively.
  1. Basic Understanding of SQL: The cornerstone of your journey is a solid grasp of Structured Query Language (SQL). This includes familiarity with basic SQL syntax, the ability to write simple queries (SELECT, INSERT, UPDATE, DELETE), and an understanding of how to work with tables and databases.
  2. Relational Database Concepts: A thorough understanding of relational database principles is crucial. This includes knowledge of how data is structured in tables, the concept of primary and secondary keys, and the relationships between different tables (like one-to-one, one-to-many, and many-to-many relationships).
  3. Data Retrieval and Manipulation: Before delving into the complexities of cursors and views, you should be comfortable with data retrieval and manipulation. This means being proficient in writing complex queries involving JOINs, GROUP BY, HAVING, and WHERE clauses, and understanding how to manipulate data using SQL functions and operators.
  4. Database Design and Normalization: A foundational understanding of database design and normalization is important. This includes knowledge of how to organize data within tables and how to design a database schema so that it is efficient, reduces data redundancy, and ensures data integrity.
  5. Basic Programming Concepts: While not strictly necessary, a basic understanding of programming concepts like loops, conditions, and variables will be beneficial, especially when dealing with cursors which are used for iterating over query results.
  6. Familiarity with SQL Server Management Studio (SSMS): Proficiency in using SQL Server Management Studio or a similar interface is important as it is the main tool used for interacting with SQL Server. This includes understanding how to connect to a database server, execute SQL scripts, and use the interface to browse database objects.
  7. Awareness of SQL Server-Specific Features: Each version of SQL Server introduces new features and enhancements. Familiarity with the features specific to SQL Server 2019, or at least a willingness to learn them, is essential.

Once these prerequisites are in place, you will be well-prepared to study more advanced topics like creating and using views (which are virtual tables), writing and optimizing queries for performance, and understanding the use and implications of cursors in SQL Server 2019. This preparation lays the groundwork for effective learning and application of these advanced database concepts.

Verify that you have the Proper Background for this course.

Queries, Cursors and Views is an introductory-level course. It does require the following, however:
  1. Experience Using the Microsoft Windows NT Server network operating system
  2. Familiarity with the definition, theory, and underlying function of relational databases
  3. Understanding of basic ANSI SQL statements.
  4. Completion of Creating a Database, the first course in this series, or equivalent understanding of relational concepts, basic Transact-SQL statements, and how to create databases and tables.
In the next lesson, what is required to take this course will be discussed.

Set-Based Queries

SQL Server is designed to handle data in sets. SQL is a declarative language, meaning that the SQL query describes the problem, and the Query Optimizer generates an execution plan to resolve the problem as a set.
Iterative T-SQL code is code that acts upon data one row at a time instead of as a set. It is typically implemented via cursors and forces the database engine to perform thousands of wasteful single-row operations, instead of handling the problem in one larger, more efficient set. The performance cost of these single-row operations is huge. Depending on the task, SQL cursors perform about half as well as set-based code, and the performance differential grows with the size of the data. This is why set-based queries, based on an obvious physical schema, are so critical to database performance.
A good physical schema and set-based queries set up the database for excellent indexing, further improving the performance of the query