游标sql server_了解游标并将其替换为SQL Server中的JOIN

游标sql server

Relational database management systems including SQL Server are very good at processing data in sets.

包括SQL Server在内的关系数据库管理系统非常擅长处理集合中的数据。

However, if you want to process data on row-by-row basis rather than in sets, cursors are your only choice. Unfortunately, cursors are extremely slow and so where possible should be replaced with JOINS.

但是，如果要按行而不是按组处理数据，则游标是唯一的选择。 不幸的是，游标非常慢，因此应尽可能用JOINS代替。

为什么光标慢 (Why cursors are slow)

To populate a cursor, database tables are iterated on row-by-row basis rather than in sets. While a cursor is being populated, the table being iterated is locked. Row operations can take a long time to execute depending upon the type of task being performed on each row. While the cursor is open, a table cannot be accessed or updated by the other users. This makes cursor-based operations extremely. Therefore, cursors can be avoided wherever they can by the set based operations.

为了填充游标，数据库表是按行而不是按集合进行迭代的。 当游标被填充时，被迭代的表被锁定。 根据在每一行上执行的任务类型，行操作可能需要很长时间才能执行。 打开游标时，其他用户无法访问或更新表。 这使得基于游标的操作变得极为重要。 因此，可以通过基于集合的操作来尽可能避免游标。

In this article, we will see how cursors process data and look at how and when we can replace cursors with JOINS.

在本文中，我们将看到游标如何处理数据，以及如何以及何时可以用JOINS替换游标。

准备伪数据 (Preparing dummy data)

Let’s start by creating some dummy data to work with.

让我们从创建一些虚拟数据开始。

 
CREATE Database company;
 

Next, we need two tables “department” and “employee” with the department table having two columns: id and dep_name, and the employee table having four columns: id, name, dep_id, and salary.

接下来，我们需要两个表“ department”和“ employee”，其中Department表具有两列：id和dep_name，而employee表具有四列：id，name，dep_id和薪水。

The dep_id column of employee table will hold values from the id table of the department table, with a one too many relations between the department and employee tables. Remember this is not a perfectly normalized data table as we just want some data to execute example queries on.

员工表的dep_id列将保存部门表的ID表中的值，部门表与员工表之间的关系过多。 请记住，这不是一个完全标准化的数据表，因为我们只希望一些数据在其上执行示例查询。

USE company;
 
 
CREATE TABLE department
(
    id INT PRIMARY KEY,
    dep_name VARCHAR(50) NOT NULL,
    
 )
 
CREATE TABLE employee
(
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    dep_id INT NOT NULL,
    salary INT NOT NULL
 
</