sql示例_PARSEONLY SQL命令概述和示例

sql示例

This article explores the uses of the SET PARSEONLY SQL command for SQL Server queries.

本文探讨了SET PARSEONLY SQL命令在SQL Server查询中的用法。

介绍 (Introduction)

Developers write complex and lengthy SQL scripts. The high-level tasks in executing a SQL query are parsed, compile and execute.

开发人员编写复杂而冗长SQL脚本。 执行SQL查询中的高级任务将被解析，编译和执行。

• Parse: In this phase, SQL Server parses the syntax of a T-SQL. For a successful query execution. It should have valid syntax. It also checks for the variable declaration, query identifiers. We can also parse query using SSMS query parser(CTRL+F5) 解析：在此阶段，SQL Server解析T-SQL的语法。 为成功执行查询。 它应该具有有效的语法。 它还检查变量声明，查询标识符。 我们还可以使用SSMS查询解析器（CTRL + F5）解析查询

Query parser icon might vary in a different version of SSMS. In the following screenshot, we see the query parser icon in SSMS 18.4.

在不同版本的SSMS中，查询解析器图标可能会有所不同。 在以下屏幕截图中，我们在SSMS 18.4中看到了查询解析器图标。

For example, it highlights incorrect syntax in the following query.

例如，它在以下查询中突出显示了不正确的语法。

SELECT * A

Similarly, the following query has valid syntax, but we did not declare the scalar variable @x. In the message tab, we get a message – Must declare the scalar variable @x.

同样，以下查询具有有效的语法，但是我们没有声明标量变量@x。 在消息选项卡中，我们收到一条消息– 必须声明标量变量@x 。

SELECT * from A
where A=@x

In case the query contains multiple errors, it lists all error messages along with the line numbers.

如果查询包含多个错误，它将列出所有错误消息以及行号。

• Compile: In this phase, SQL Server checks for the objects, whether they exist or not. It also checks user permissions. During the compilation phase, SQL Server also optimizes the query execution and comes with an optimized execution plan using available indexes and statistics 编译：在此阶段，SQL Server将检查对象是否存在。 它还检查用户权限。 在编译阶段，SQL Server还优化了查询执行，并提供了使用可用索引和统计信息的优化执行计划。
• Execute: Once SQL Server completed parse and compilation, SQL Server executes query batch and returns output to the client 执行： SQL Server完成解析和编译后，SQL Server执行查询批处理并将输出返回给客户端

Suppose you are writing a complicated script and press F5 by mistake. You must hit the cancel button to stop this execution, but it might have done damages for you. For example, you specified a drop table statement for dropping a few tables, but if it satisfies certain conditions. We want to prevent such accidental query execution.

假设您正在编写一个复杂的脚本，然后误按F5键。 您必须按下“取消”按钮才能停止执行，但它可能会对您造成损害。 例如，您指定了一个drop table语句来删除一些表，但前提是要满足某些条件。 我们要防止这种意外查询执行。

SQL Server provides PARSEONLY and NOEXEC session-level configuration. Let’s explore PARSEONLY in this article.

SQL Server提供了PARSEONLY和NOEXEC会话级配置。 让我们在本文中探讨PARSEONLY 。

PARSEONLY SQL命令 (PARSEONLY SQL Command)

You might have noticed we use the Go statement for query execution in SQL Server. The Go statement works as a batch separator. SQL Server processes one batch request at a time. We might use multiple batches in a single query session, but these batches are independent of each other.

您可能已经注意到我们使用Go语句在SQL Server中执行查询。 Go语句用作批处理分隔符。 SQL Server一次处理一个批处理请求。 我们可能在一个查询会话中使用多个批次，但是这些批次彼此独立。

We can use the PARSEONLY statement in a query batch, and SQL Server does not compile and execute the statement.

我们可以在查询批处理中使用PARSEONLY语句，而SQL Server不会编译和执行该语句。

• • SET PARSEONLY ON: We can specify this statement and only parse phase is executed
    SET PARSEONLY ON：我们可以指定此语句，并且仅执行解析阶段
  • SET PARSEONLY OFF: If we have enabled PARSEONLY using SET PARSEONLY ON, we can disable the query execution behavior using SET PARSEONLY OFF
    SET PARSEONLY OFF：如果使用SET PARSEONLY ON启用了PARSEONLY，则可以使用SET PARSEONLY OFF禁用查询执行行为
  • We can use it anywhere in a batch
    我们可以在任何地方批量使用它
  • We cannot use any conditional behavior such as Case and IF statements with PARSEONLY
    我们不能在PARSEONLY中使用任何条件行为，例如Case和IF语句

Let’s understand the PARSEONLY statement behavior using examples.

让我们使用示例了解PARSEONLY语句的行为。

示例1：默认查询行为SQL Server (Example 1: Default query behavior SQL Server)

Execute the following T-SQL batch, and it completes all phases of a SQL Query, i.e., Parse compiles and execute.

执行以下T-SQL批处理，它完成了SQL查询的所有阶段，即Parse编译和执行。

DECLARE @Emp TABLE (Empid INT);
Insert into @Emp values (1)
SELECT Empid FROM @Emp;
Go

You get the expected output once the query finishes.

查询完成后，您将获得预期的输出。

示例2：为查询批处理指定PARSEONLY SQL命令 (Example 2: specify PARSEONLY SQL Command for query batch )

In this example, we use SET PARSEONLY ON before query batch and SET PARSEONLY OFF after query batch finishes. As stated earlier, it should only execute the parse phase for this batch. Let’s execute this and observe the behavior.

在此示例中，我们在查询批处理之前使用SET PARSEONLY ON ，在查询批处理完成之后使用SET PARSEONLY OFF 。 如前所述，它仅应对此批处理执行解析阶段。 让我们执行此操作并观察行为。

SET PARSEONLY ON;
DECLARE @Emp TABLE(Empid INT);
INSERT INTO @Emp
VALUES(1);
SELECT Empid
FROM @Emp;
GO
SET PARSEONLY OFF;

We did not get any output for this query.

我们没有得到该查询的任何输出。

示例3：指定具有解析错误的PARSEONLY SQL命令 (Example 3: specify PARSEONLY SQL Command with parsing error)

In this example, we intentionally modified the T-SQL batch so that it gives an error during the parse phase.

在此示例中，我们有意修改了T-SQL批处理，以便在解析阶段出现错误。

SET PARSEONLY ON;
DECLARE @Emp TABLE(Empid INT);
INSERT INTO @Emp
VALUES(1);
SELECT Empid
FROM @Emp_new;
SET PARSEONLY OFF;
GO

We get the error message because the table variable @Emp_new does not exist. This way, the query executes in the parse phase only.

我们收到错误消息，因为表变量@Emp_new不存在。 这样，查询仅在解析阶段执行。

示例4：使用不存在的对象指定PARSEONLY SQL命令 (Example 4: Specify PARSEONLY SQL Command with objects that do not exist)

In this example, we use a select statement, but the table does not exist. You can see a red line below the object that shows it does not exist in the current database. As stated earlier, SQL Server checks for object existence during the compile phase. We specify SET PARSEONLY ON for skipping the compile and execute phase. Due to this reason, query complete parse phase and do not give any error.

在此示例中，我们使用select语句，但是该表不存在。 您可以在对象下面看到一条红线，表明该对象在当前数据库中不存在。 如前所述，SQL Server在编译阶段检查对象是否存在。 我们指定SET PARSEONLY ON来跳过编译和执行阶段。 由于这个原因，查询完成了解析阶段，并且没有给出任何错误。

SET PARSEONLY ON;
SELECT *
FROM [AdventureWorks].[HumanResources].[test];
GO
SET PARSEONLY OFF;

示例5：多个批处理和PARSEONLY SQL命令行为 (Example 5: Multiple batches and PARSEONLY SQL Command behavior)

示例5a：具有PARSEONLY语句的批处理1和不具有PARSEONLY的批处理2 ( Example 5a: Batch 1 with PARSEONLY statement and Batch 2 without PARSEONLY)

Look at the following query. In batch 1, we used the SET PARSEONLY statement ON and disable SET PARSEONLY after batch finishes.

查看以下查询。 在批处理1中，我们使用SET PARSEONLY语句ON并在批处理完成后禁用SET PARSEONLY。

--Batch 1
SET PARSEONLY ON;
SELECT *
FROM [AdventureWorks].[HumanResources].[test];
GO
SET PARSEONLY OFF;
--Batch 2
 
SELECT top 1 *
FROM [AdventureWorks].[HumanResources].vEmployeeDepartment;
GO

It does not report an error in batch 1 (parse only) and returns output for another batch.

它不会报告批次1中的错误（仅解析），并返回另一批次的输出。

示例5b：具有PARSEONLY语句为ON但没有PARSEONLY语句为OFF的批处理1 (Example 5b: Batch 1 with PARSEONLY statement ON but without PARSEONLY statement OFF)

In this example, we use the PARSEONLY ON statement but did not turn it off. Once we turn PARSEONLY ON, it disables the compile and executes phase for all batches in the session. Therefore, SQL Server does not execute another batch as well.

在此示例中，我们使用PARSEONLY ON语句，但未将其关闭。 一旦我们将PARSEONLY设置为ON，它将禁用编译并为会话中的所有批处理执行阶段。 因此，SQL Server也不会执行另一个批处理。

--Batch 1
SET PARSEONLY ON;
SELECT *
FROM [AdventureWorks].[HumanResources].[test];
GO
--Batch 2
 
SELECT top 1 *
FROM [AdventureWorks].[HumanResources].vEmployeeDepartment;
GO

Let’s twist the query a little bit. In the following query, We turn on and off PARSEONLY in a single batch. We did not use the Go statement so that SQL Server can understand different batch. Once we execute this query, SQL Server does parse and skips the query execution because PARSEONLY OFF does not have any impact on the current batch.

让我们扭曲一下查询。 在以下查询中，我们将单批打开和关闭PARSEONLY。 我们没有使用Go语句，因此SQL Server可以理解不同的批处理。 一旦执行此查询，SQL Server就会解析并跳过查询执行，因为PARSEONLY OFF不会对当前批处理产生任何影响。

--Batch 1
SET PARSEONLY ON;
SELECT *
FROM [AdventureWorks].[HumanResources].[test];
--Batch 2
SET PARSEONLY OFF;
SELECT top 1 *
FROM [AdventureWorks].[HumanResources].vEmployeeDepartment;
GO

示例5C：通过动态执行使PARSEONLY语句生效 (Example 5C: effect PARSEONLY statement ON with dynamic execution )

Look at the below queries. We specify PARSEONLY ON (batch 1) and PARSEONLY OFF (batch 2) statements inside EXEC.

查看以下查询。 我们在EXEC中指定PARSEONLY ON（批次1）和PARSEONLY OFF（批次2）语句。

EXEC(N'SET PARSEONLY OFF;');
PRINT 'Parse & Execute';
GO
EXEC(N'SET PARSEONLY ON;');
PRINT 'Parse & Execute - 1';
GO

It prints a message for both the batches. PARSEONLY statement does not have any impact in this case.

它为两个批次打印一条消息。 在这种情况下，PARSEONLY语句没有任何影响。

Now, we add another batch in this session. This batch contains PARSEONLY statement inside the IF statement. Execute these batches once in a new query window of SSMS.

现在，我们在此会话中添加另一批。 该批处理在IF语句中包含PARSEONLY语句。 在SSMS的新查询窗口中一次执行这些批处理。

EXEC(N'SET PARSEONLY OFF;');
PRINT 'Parse & Execute';
GO
EXEC(N'SET PARSEONLY ON;');
PRINT 'Parse & Execute - 1';
GO
IF (1 > 0)
BEGIN
  SET PARSEONLY ON
  PRINT 'Parse & Execute - 2'
END;
Go

It executes both batch 1 and 2 but parse batch but only parse batch 3 due to specified PARSEONLY statement.

它执行批处理1和2，但解析批处理，但由于指定的PARSEONLY语句，仅解析批处理3。

Now, turn off PARSEONLY in the third batch of a similar session and execute the query.

现在，在类似会话的第三批中关闭PARSEONLY并执行查询。

EXEC(N'SET PARSEONLY OFF;');
PRINT 'Parse & Execute';
GO
EXEC(N'SET PARSEONLY ON;');
PRINT 'Parse & Execute - 1';
 GO
IF (1 > 0)
BEGIN
  SET PARSEONLY OFF
  PRINT 'Parse & Execute - 2'
END;
Go

This time it only prints a message for batch 3. Previously, we turn on PARSEONLY in this session; therefore, its impact still exits for previous batches. Due to this, SQL Server does not execute batch 1 and 2. In batch 3, we turn off PARSEONLY; therefore, it executes and prints a message.

这次只打印批次3的消息。以前，我们在此会话中打开PARSEONLY； 因此，它的影响仍然存在于以前的批次中。 因此，SQL Server不会执行批处理1和2。在批处理3中，我们关闭了PARSEONLY；而在批处理3中，则关闭了。 因此，它执行并打印一条消息。

Without making any changes in queries, execute it again, and you get the following output. This time it executes all batches and prints messages.

在不对查询进行任何更改的情况下，再次执行它，您将获得以下输出。 这次它将执行所有批处理并打印消息。

结论 (Conclusion)

In this article, we explored the uses of the PARSEONLY SQL Command for excluding compile and execute phase of a query. It might be useful for query debugging or test purposes.

在本文中，我们探讨了PARSEONLY SQL Command的使用，以排除查询的编译和执行阶段。 对于查询调试或测试目的，它可能很有用。

翻译自: https://www.sqlshack.com/the-parseonly-sql-command-overview-and-examples/

sql示例