Работа с базой данных MS SQL средствами Go для начинающих
В данный момент язык Go становится все популярнее и популярнее с каждым днем. На Хабре все чаще появляются статьи на тему, которые интересно читать не только прожженным спецам-програмистам, но и системным администраторам.
Я работаю системным администратором и проявляю интерес к Go, так как нам часто приходится писать скрипты на bash (shell) для автоматизации своих действий и увеличения времени на поедание печенек и заливания кофе в наш щупленький организм.
Хотелось бы поделиться небольшим опытом о том, как не программист писал небольшую программу на Go.
Приступим.
В компании, в которой я работаю есть некая программа, которая используется всеми. В ее основе лежит база на сервере MS SQL 2008. В прекрасный момент (для меня не очень) шеф заявляет, что надо написать программу, в которой нужные люди будут смотреть определенный данные из той самой базы.
Для работы с базами данных есть пакеты database/sql и code.google.com/p/odbc. Их и будем использовать. Так же не забываем, что надо установить ODBC драйвер, через который и будем работать с MS SQL. Для Windows это делается через Odbcad32.exe путем добавления клиентского DSN. Для Linux немного сложнее, но это не входит в рамки статьи. Думаю, Google вам поможет.
Вот таким у нас получается список пакетов, которым будем пользоваться на первом этапе.
Пробуем соединиться с базой и выполнить запрос, дабы достать нужные нам данные.
Как видно из программы, все не так сложно и даже такие люди, как я (мало понимаем в программировании), могут постепенно учиться писать на Go.
Правда, есть один нюанс. При разворачивании сервера баз данных MS SQL Server обычно не трогают настройки кодировок и она в большей части мест стоит windows1251. Это доставляет некое неудобство, так как в Go все работает в UTF8. В связи с этим мы будем применять дополнительные пакеты, не входящие в состав Go, для перекодировки windows1251 в UTF8.
Поэтому, если вы запустите нашу программу, то увидите белеберду в консоли вместо русских букв. Чтобы это избежать, давайте воспользуемся пакетами golang.org/x/text/encoding/charmap, golang.org/x/text/transform
Видим и радуемся, что все читается и данные из базы вытаскиваются. Хочу заметить, если в запросе на выходе будут два или больше столбцов, например, фамилия, имя и отчество, то не забываем их указывать в rows.Scan именно в том порядке, что и в SQL запросе.
Чему мы научились после прочтения этой статьи? Получили базовые знания для работы с базами данными. Дальше можно будет их обрабатывать, сортировать или выводить в браузере и т.д. и т.п. Статья не рассчитана на опытных программистов и написана специально для людей, которые только начинают изучать довольно интересный язык Go.
Mssql go что это
В предыдущих случаях сначала создавалась база данных, а затем в эту БД добавлялась таблица с помощью отдельных команд SQL. Но можно сразу совместить в одном скрипте несколько команд. В этом случае отдельные наборы команд называются пакетами (batch).
Каждый пакет состоит из одного или нескольких SQL-выражений, которые выполняются как оно целое. В качестве сигнала завершения пакета и выполнения его выражений служит команда GO .
Смысл разделения SQL-выражений на пакеты состоит в том, что одни выражения должны успешно выполниться до запуска других выражений. Например, при добавлении таблиц мы должны бы уверены, что была создана база данных, в которой мы собираемся создать таблицы.
Например, определим следующий скрипт:
Вначале создается бд internetstore. Затем идет команда GO, которая сигнализирует, что можно выполнять следующий пакет выражений. И далее выполняется второй пакет, который добавляет в нее две таблицы — Customers и Orders.
What is the use of GO in SQL Server Management Studio & Transact SQL?
SQL Server Management Studio always inserts a GO command when I create a query using the right click «Script As» menu. Why? What does GO actually do?
12 Answers 12
It is a batch terminator, you can however change it to whatever you want 
Since Management Studio 2005 it seems that you can use GO with an int parameter, like:
The above will insert 10 rows into mytable . Generally speaking, GO will execute the related sql commands n times.
The GO command isn’t a Transact-SQL statement, but a special command recognized by several MS utilities including SQL Server Management Studio code editor.
The GO command is used to group SQL commands into batches which are sent to the server together. The commands included in the batch, that is, the set of commands since the last GO command or the start of the session, must be logically consistent. For example, you can’t define a variable in one batch and then use it in another since the scope of the variable is limited to the batch in which it’s defined.
GO is not a SQL keyword.
It’s a batch separator used by client tools (like SSMS) to break the entire script up into batches
Answered before several times. example 1
Just to add to the existing answers, when you are creating views you must separate these commands into batches using go , otherwise you will get the error ‘CREATE VIEW’ must be the only statement in the batch . So, for example, you won’t be able to execute the following sql script without go
Go means, whatever SQL statements are written before it and after any earlier GO, will go to SQL server for processing.
In the above example, statements before GO 1 will go to sql sever in a batch and then any other statements before GO 2 will go to sql server in another batch. So as we see it has separated batches.
I use the GO keyword when I want a set of queries to get committed before heading on to the other queries.
One thing I can add is, when you have some variables declared before the GO command you will not be able to access those after the GO command. i.e
Update
I see, people requesting when to use the Go command, so I thought, I should add why I use the Go command in my queries.
When I have huge updates in the tables and I usually run these updates while going off from work (which means, I wouldn’t be monitoring the queries), since it is convenient to come the next day and find the tables ready for other operations.
I use Go command when I need to run long operations and want to separate the queries and complete part of the transactions such as:
Executing above queries will individually commit the modifications without resulting in huge roll-back logs formation. Plus if something fails on third query, you know first 2 queries were properly executed and nothing would be rolled-back. So you do not need to spend more time updating/deleting the records again for the previously executed queries.
To sum it up in just one sentence, "I use the GO command as a check point as in the video games." If you fail after the check point (GO command), you do not need to start over, rather your game starts from the last check point.
Code says to execute the instructions above the GO marker. My default database is myDatabase, so instead of using myDatabase GO and makes current query to use herDatabase
One usage that I haven’t seen listed is Error Resilience. Since only the commands between two GOs are run at a time, that means a compile error in one command can be separated from others. Normally any compile errors in a batch cause the entire thing to not be executed.
In above, ‘here’ will not be printed because of the error in the ‘sel’ statement.
Now, adding a GO in the middle:
You get an error for ‘sel’ as before, but ‘here’ does get output.
tldr; In most cases nowadays GO is mostly IMO optional. Using GO is best in LARGE transaction batches where you would have compiled many different scripts together in a large script and don’t want errors where similar variables are used and so that parts of the transaction is committed to the server when desired instead of all of the script being rolled back due to an error.
LARGE TRANSACTION 1 —> Runs Successfully
GO; —> Is in the server
LARGE TRANSACTION 2 —> Runs Successfully
GO; —> Is in the server
LARGE TRANSACTION 3 —> Errors
GO; —> Without the other GO statements this would rollback Transaction 1 & 2
Not sure the best way to provide this SO wise however I do feel like what I’ve read so far doesn’t really sum it all up and include an example that I’ve come across.
As stated many times before GO simply "commits" a batch of commands to the server.
I think understanding sessions also helps with understanding the necessity (or optionality) of the GO statement.
(This is where my technicality may fail but the community will point it out and we can make this answer better)
Typically developers are working in a single session and typically just executing simple statements to the database. In this scenario GO is optional and really. all one would do is throw it at the end of their statements.
Where it becomes more helpful is probably an option given by Jamshaid K. where you would have many large transactions that you would want committed in turn instead of all transactions being rolled back when one fails.
The other scenario where this also becomes helpful (which is the only other spot I’ve experienced it) is where many small transactions are compiled into one large script. For example
Dev 1 makes script 1
Dev 2 makes script 2
Dev 1 makes script 3
In order to deploy them a python script is written to combine the scripts so Script Master = script1 + script 2 + script 3.
GO statements would be required in the 3 scripts otherwise there could be errors where the scripts use conflicting variables or if script 3 fails the transactions from scripts 1 and 2 would be rolled back.
Now this process is probably archaic given current CI/CD solutions out there now but that would probably be another scenario where I could see GO being helpful/expected.
What does the GO statement do in SQL Server?
The GO statement from SQL Server caused me great curiosity and I don’t really know how to use it properly.
I noticed that queries with or without GO don’t return errors and seem to work the same, so what is the purpose of it and why should I use it?
And what is the difference between semicolons ; and GO at the end of a query?
5 Answers 5
GO is not a part of the TSQL language. It’s a batch separator used by SQLCMD and SSMS.
GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.
SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.
A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.
It originated with the command line interfaces, and persists in sqlcmd to this day. You can write a batch over multiple lines, and the batch isn’t sent to the server until you type go .
You can configure what to use for a batch separator in SSMS. GO is the default, but it can be just about anything else.
It splits the command into individual batches.
For instance, variables’ scoped ends with GO .
; separates commands but doesn’t end scope.
GO separates batches and ends scope.
You may think of it as if the whole command got split by GO and each part got executed per se.
But this doesn’t work:
These are the explanations:
- ; = Is a statement terminator. Is not mandatory but it might be one day. for now you MUST use it in two situations:
- In a Common Table Expression (CTE), where the CTE is not the first statement in the batch.
- When you issue a Service Broker statement and the Service Broker statement is not the first statement in the batch.
GO = Is a batch separator. It tells to SQL Server "stop here and execute all the previous code before moving on". Is not mandatory but there is a feature for you: if you write GO 10 , GO 100 , GO 1000 it will execute the same batch of code 10 , 100 , 1000 times
GO means nothing to SQL Server itself, but is interpreted by the tools (such as SSMS or SQLCMD) that send your scripts to SQL Server. Some other tools may not understand ‘GO’. Some tools interpret GO slightly differently. In SSMS each batch is performed in the same session so transactions can span batches, at least some of the command line tools included with SQL Server do not maintain the same session for each batch in a file, so an open transaction will be rolled back when GO is encountered.
GO has been well addressed in other answers. Let me add some about semi-colon:
A long long time ago, this wasn’t something we used. But the SQL standard (ANSI/ISO SQL) specified that a SQL statement should end with semi-colon. So at a certain version, MS allowed it to comply with ANSI SQL. But it was, and mostly still is, a no-op. I.e., it makes no difference if you have it or not
I wasn’t involved when the SQL language was invented (I was a kid), but I can imagine that semi-colon was specified as a statement terminator to simplify parsing of SQL. I.e., when you submit SQL to the db engine, it first has to parse (understand what you said, basically) the statement(s). You can probably imagine that is might be easier for the parsing code in the engine if it could look for some certain character that means "end-of-statement". Hence semi-colon. But as I noted, SQL Server didn’t use semi-colon for parsing.
Now, with time, some new language elements that was introduced required that the preceding statement was terminated with semi-colon (WITH as in CTE and THROW being two examples). Without it, SQL Server couldn’t parse (understand what you said) and you got a error generated from the parsing phase.