Name already in use
sql-docs / docs / t-sql / statements / set-nocount-transact-sql.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
SET NOCOUNT (Transact-SQL)
Stops the message that shows the count of the number of rows affected by a [!INCLUDEtsql] statement or stored procedure from being returned as part of the result set.
. image type=»icon» source=»../../includes/media/topic-link-icon.svg» border=»false». Transact-SQL syntax conventions
When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
SET NOCOUNT ON prevents the sending of DONEINPROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain [!INCLUDEtsql] loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.
To view the current setting for this setting, run the following query.
Requires membership in the public role.
The following example prevents the message about the number of rows affected from being displayed. In the following example, «(5 rows affected)» will only be returned to clients from the first SELECT statement.
Оптимизация хранимых процедур в SQL Server
Доброго дня, хабрачеловек. Сегодня я бы хотел обсудить с вами тему хранимых процедур в SQL Server 2000-2005. В последнее время их написание занимало львиную долю моего времени на работе и чего уж тут скрывать – по окончанию работы с этим делом осталось достаточно информации, которой с удовольствием поделюсь с тобой %пользовательимя%.
Знания, которыми я собираюсь поделиться, к сожалению,(или к счастью) не добыты мной эмперически, а являются, в большей степени, вольным переводом некоторых статей из буржуйских интернетов.
Итак, как можно понять из названия речь пойдет об оптимизации. Сразу оговорюсь, что все действия, которые я сейчас буду описывать, действительно дают существенный(некоторые больший, некоторые меньший) прирост производительности.
Данная статья не претендует на полное раскрытие темы оптимизации, скорее это собрание практик, которые я применяю в своей работе и могу ручаться за их эффективность. Поехали!
1. Включай в свои процедуры строку — SET NOCOUNT ON: С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.
CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
—Здесь код процедуры
SELECT column1 FROM dbo.TblTable1
—Перключение SET NOCOUNT в исходное состояние
SET NOCOUNT OFF;
GO
2. Используй имя схемы с именем объекта: Ну тут думаю понятно. Данная операция подсказывает серверу где искать объекты и вместо того чтобы беспорядочно шарится по своим закромам, он сразу будет знать куда ему нужно пойти и что взять. При большом колличестве баз, таблиц и хранимых процедур может значительно сэкономить наше время и нервы.
SELECT * FROM dbo.MyTable —Вот так делать хорошо
— Вместо
SELECT * FROM MyTable —А так делать плохо
—Вызов процедуры
EXEC dbo.MyProc —Опять же хорошо
—Вместо
EXEC MyProc —Плохо!
3. Не используй префикс «sp_» в имени своих хранимых процедур: Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.
4. Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *): Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже:
IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = ‘MyTable’ AND type = ‘U’)
5. Используй TRY-Catch для отлова ошибок: До 2005 сервера после каждого запроса в процедуре писалось огромное колличество проверок на ошибки. Больше кода всегда потребляет больше ресурсов и больше времени. С 2005 SQL Server’ом появился более правильный и удобный способ решения этой проблемы:
BEGIN TRY
—код
END TRY
BEGIN CATCH
—код отлова ошибки
END CATCH
Заключение
В принципе на сегодня у меня всё. Еще раз повторюсь, что здесь лишь те приёмы, которые использовал лично я в своей практике, и могу ручаться за их эффективность.
SET NOCOUNT ON ИСпользование
Запрещает вывод количества строк, на которые влияет инструкция Transact-SQL или хранимая процедура, в составе результирующего набора.
Такая информация очень даже может пригодиться при отладке, но потом, скорее всего, будет не нужна. Прописав SET NOCOUNT ON вы отключите вывод количества строк, тем самым увеличите производительность за счет сокращения объема трафика, особенно если у вас в процедуре есть циклы или процедура содержит несколько выражений сразу, но не возвращает большого количества строк.
Для подсчета строк, если вам это необходимо, лучше использовать @@rowcount. Одна из причин: если вы используете где-то в проекте DataAdapter или Command, то NOCOUNT может все поломать.
Стоит заметить, что если у вас на сервере NOCOUNT по умолчанию имеет одно значение, а в процедуре вы пишите другое, то локальное значение приоритетнее. Значение по умолчанию можно установить в Management Studio (Сервис-Параметры-Выполнение запроса-SQL Server-Дополнительно):
Ну и на англоязычном StackOverflow есть много интересных вопросов и ответов, касающихся NOCOUNT и его поведения, советую ознакомиться.
SQL SET NOCOUNT ON
When SET NOCOUNT is ON, the message that indicates the number of rows that are affected by the T-SQL statement is not returned as part of the results. When SET NOCOUNT is OFF, the count is returned.
It is used with any SELECT , INSERT , UPDATE , and DELETE statement.
You may have noticed many times whenever you perform any select, insert, update, delete commands or execute any stored procedure, they returns output as results but also displays the message as number of rows affected rows because of SQL Set NOCOUNT OFF. By default, SET NOCOUNT is set to OFF.
For example, if you update 5 records in any table, then SQL will return the message as 5 records affected, that happens because of NOCOUNT is by default set to OFF. So it returns the message which is the count of number of affected rows by the T-SQL query.
By using this SQL Server SET NOCOUNT ON, you can stop the messages. Which really imporves the performance. For any stored procedures that contains bunch of statements that do not return much actual data, or for procedures that contains loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
Lets look at an example using SET NOCOUNT ON/OFF in SQL, Before that we create a sample table named as DepartmentDetails.
SET NOCOUNT OFF
Lets see the default behaviour of SET NOCOUNT, when SET NOCOUNT is set to OFF.
We will insert few records into table DepartmentDetails as SET NOCOUNT by default is set to OFF, so it returns the message that indicates the number of rows that are affected by the T-SQL statement (Insert statement).
As you can see, it returns number of rows that are affected by the T-SQL statement, is 6 rows affected.
Lets see, when you select a records from table, as SET NOCOUNT is still set to OFF so again it will returns a records in grid as result and number of affected rows as message in messages tab.
You can see the message 6 rows affected under messages tab.
SET NOCOUNT ON
When SET NOCOUNT is ON, the message that indicates the number of rows that are affected by the T-SQL statement is not returned as part of the results.
Lets remove an existing records from table first as inserting same records again will give you primary key violation error, or you can insert different records, then execute the same insert script again with setting a SET NOCOUNT ON to see the behaviour of SET NOCOUNT when it set to ON.
Once you remove the records from table, then modify insert script by setting SET NOCOUNT to ON as given below.
Once you execute the above T-SQL, this time it does not returns the count of affected rows.
Lets see, for select statement and you can see there are no affected rows count message under messages tab.
SET NOCOUNT ON in Stored Procedure
Lets see an example of using SET NOCOUNT ON in Stored Procedure.
First we create a simple stored procedure that returns the number of records in departmentdetails table.
In following stored procedure get_depatmentCount, SET NOCOUNT is set to OFF.
Lets execute the Stored procedure, and you see it returns the total counts as result and in messages tab it returns the number of affected rows as shown in below screenshots.
Lets alter the above stored procedure and set NOCOUNT to ON.
After modifying stored procedure for SET NOCOUNT ON. Lets execute it and you can see, this time it does not returns the message for number of affected rows.