SQL Server : 일괄 처리 문 (즉, "GO"사용)이 좋은 이유는 무엇입니까?

SQL Server : 일괄 처리 문 (즉, "GO"사용)이 좋은 이유는 무엇입니까?

---

SQL Server GO 에서는 일괄 구분 기호로 간주됩니다 .

내 질문은 : 배치 구분 기호가있는 이유는 무엇입니까? 어떤 이점이 있으며 왜 사용하고 싶습니까?

예 : 다음과 같이 SQL 코드에서 자주 사용되는 것을 보았지만 모범 사례로 간주되는 이유를 알 수 없습니다. 내가 말할 수있는 한 모든 GO문장 없이도 코드가 동일 할 것입니다 .

USE AdventureWorks2012;
GO
BEGIN TRANSACTION;
GO
IF @@TRANCOUNT = 0
BEGIN
    SELECT FirstName, MiddleName 
    FROM Person.Person WHERE LastName = 'Adams';
    ROLLBACK TRANSACTION;
    PRINT N'Rolling back the transaction two times would cause an error.';
END;
ROLLBACK TRANSACTION;
PRINT N'Rolled back the transaction.';
GO

(출처 : technet 문서 ) :

---

예에서는 전혀 쓸모가 없습니다.

그러나 많은 문이 일괄 처리에서 유일한 문이어야합니다.

같은 CREATE PROCEDURE.

또한 종종 스키마를 변경 한 후 (예 : 기존 테이블에 새 열 추가) 새 스키마를 사용하는 문은 다른 일괄 처리에서 별도로 컴파일해야합니다.

일반적으로로 구분 된 개별 배치를 제출하는 대안 GO은 다음을 사용하여 자식 배치에서 SQL을 실행하는 것입니다.EXEC

---

으로 TechNet의 말씀 , GO그것은 SQL 유틸리티에 대한 SQL 일괄 처리의 끝을 의미한다. 예를 들어 SQL Server Management Studio에서 일괄 구분 기호를 발견하면 지금까지의 모든 텍스트가 독립적 인 SQL 쿼리임을 알고 있습니다.

우리는 소프트웨어에서 유사한 기술을 사용합니다. 우리는 모든 procs, 스키마 스크립트, 데이터 변환 등을 SQL 스크립트 파일 (소스 제어에 체크인)에 보관합니다. 설치 프로그램이 이러한 스크립트 파일 중 하나를 읽을 때 GO는 파서에게 "이미 읽은 SQL을 실행할 수 있습니다"라고 알려줍니다.

일괄 구분 기호에 대한 좋은 기능 GO은 일반적으로 오류를 발생시키는 동일한 스크립트에 두 개의 SQL 쿼리를 함께 포함 할 수 있다는 것입니다. 예를 들어, 동일한 스크립트 파일에서 동일한 저장 프로 시저를 삭제하고 다시 생성 해보십시오.

if exists (select * from sys.procedures where name = 'sp_test')
    drop procedure sp_test

create procedure sp_test as
begin
    select 1
end

위의 코드를 실행하면 오류가 발생합니다.

메시지 156, 수준 15, 상태 1, 프로 시저 sp_test, 줄 5 'begin'키워드 근처의 구문이 잘못되었습니다.

SSMS는 오류를 표시합니다.

구문이 잘못되었습니다. 'CREATE PROCEDURE'는 일괄 처리에서 유일한 문이어야합니다.

배치 구분 기호를 사용하면이 오류를 해결할 수 있습니다.

if exists (select * from sys.procedures where name = 'sp_test')
    drop procedure sp_test
GO
create procedure sp_test as
begin
    select 1
end

예를 들어 소스 제어에서 단일 SQL 스크립트로 저장 프로 시저 또는 함수를 유지하려는 경우 매우 편리합니다. 우리는이 패턴을 자주 사용합니다.

할 수있는 또 다른 흥미로운 일은 쿼리를 여러 번 실행하는 데 사용하는 것입니다.

INSERT INTO MyTable (...) ...
GO 10 -- run all the above 10 times!

As the answers to this SO question demonstrate, you can also configure it to whatever you want. If you want to mess with your co-workers, set the batch separator to something like "WHERE" instead of "GO". Fun! :)

---

Like Martain said, statements such as CREATE PROCEDURE must be the only ones in a batch.

For example, I use batch separators whenever I create stored procedures and add permissions to a certain user. If I left out the 'go' then I would end up with a stored procedure that grants rights every time it runs. This way I can write them at the same time and be sure that I'm not writing stored procedures that break when I call them. For example

create procedure [procedurename]
(parameters)
as begin

select prefname, lastname from people

end

go

grant execute on [procedurename] to [username]

---

What is the point of having a batch separator?

Having read many of the answers, and contributed to comments, here is what I think.

The real question is "What is the point of having a batch?"

There are 2 implications of batching that have some meaning, and there is an additional usage of go that can be useful:

1. All statements in a batch are compiled into a single execution plan

How this impacts you, as a SQL developer, I don't know. But there it is. The implication of this is that you can't have some statements within the same batch. For example, you cannot ALTER a table to add a column, then select that column in the same batch - because while compiling the execution plan, that column does not exist for selecting.

I think there is an open argument as to whether SQL Server should be able to detect this by itself without requiring developers to include go statements in their scripts. Further, the docs say ODBC connections may never issue a go command. It is not clear to me how a script run through ODBC would behave if it included the ALTER / SELECT example just given.

2. Locally declared variables exist only within the scope of the batch in which they were declared

These two points combined kind of suck. I have a script that creates and alters DB structures (tables, procedures, etc) and I want to declare variables at the start of the script that will be used to govern the behaviour of the script overall. As soon as I need to wrap up a batch (due to, say, an ALTER statement - see my point 1, above), those "config" variables fall out of scope and can't be used further down the script. My workaround is to create a table, persist the config variables into the table, then read from that table all the way through my script, then drop the table at the end (in case anyone else is facing this).

This second implication can actually be used to advantage - if your script is doing a lot of work and you simply want to clear out all your local variables, you can simply include a GO statement and then declare new variables (ie. and re-use the same names, if that's what you want).

3. GO has an optional parameter (named "count") which tells the server to repeat the batch actions multiple times

This usage seems to be nice additional functionality added on to the GO statement. I believe the initial or primary function of GO relates more to the compilation of a single execution plan, as mentioned in point 1 - otherwise the keyword may as well be something like REPEAT 10 - but repeat what? The batch. Without GO signifying a batch, a repeat command could only ever repeat the prior single statement. Therefore GO is a nice way to repeat batches.

Reference

All of this comes from trying to understand the MS documentation on GO. Many of the other answers - here, and on other questions - pick at pieces of the documentation but I think the documentation itself fails to really explain why there is a benefit to batching in the first place - hence my contribution to an already well-commented question.

Addendum

After writing the above, I did find the Rules for Using Batches mentioned by Microsoft in the GO documentation. The linked page explains that an execution plan consists of multiple statements. It also says that individual statements can be re-compiled into a new execution plan (ie by SQL Server, while processing the batch, automatically). So for example, following a statement to CREATE TABLE you might have an INSERT into that table. That INSERT statement will be recompiled after the table has been created in the prior statement.

This re-enforces the idea that SQL Server probably could detect those scenarios where an ALTER to a table is followed by a SELECT and that it needs to re-compile the SELECT (see my point 1 above), and possibly this is exactly what happens if using ODBC (see point 1 above).

None of this new information alters the 3 points given above. The link I just gave contains additional reading and ends with "the rules", which are these:

• CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must start the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.

• A table cannot be changed and then the new columns referenced in the same batch.

• If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.

참고URL : https://stackoverflow.com/questions/20711326/sql-server-what-are-batching-statements-i-e-using-go-good-for