By: Ameena Lalani | Updated: 2018-12-17 | Comments (3) | Related: > SQL Server Management Studio
Problem
Insert, update and delete statements in SQL Server modify data and if this is intentional, that's good, but what if you merely want to check the syntax of the query first and accidently run the statements in SQL Server Management Studio (SSMS). Worse yet, you run them in the wrong database or server. DBAs work in multiple SQL Server environments and work with the several query windows at the same time, hence there is a chance of making an error, especially if you are under pressure. What can we do to minimize potential issues?
Solution
Before executing a query there are a few things we can do to double check the query before execution or prevent accidental execution.
Parse Query in SQL Server Management Studio
One thing you can do is use the parse functionality to check the syntax of a query. You can do this using the parse query button (shown in the image below) in SSMS or using Ctrl + F5. This only validates the syntax of the query, but does not check if the object you are referencing is valid.
Type the following statement in SSMS window then parse the query.
USE AdventureWorks2017; GO -- First PARSE the query then EXECUTE the Query SELECT * FROM fn_helpcollations;
When the query is parsed, it will output the message "Commands completed successfully", but when we execute the command, it outputs the error message, "Parameters were not supplied for the function 'fn_helpcollations'". The lesson learned here is that we cannot always rely on the parse functionality of SSMS to give us all of the information we need. Therefore, we have to make use of other methods to make sure we don't run queries unintentionally.
Using IntelliSense in SQL Server Management Studio
SQL Server Management Studio's IntelliSense feature is very powerful and helps with writing your code, so I suggest that you have this feature turned on. In SSMS 17.x, if the syntax of your query is incorrect, you get a red squiggly line under the error and if you hover over the line you get the error message. This only works if IntelliSense is on.
In the following code example, a function has been called without the parameters which is invalid code. SSMS IntelliSense visually cues us with a red squiggly line under the offending code and shows the error message when you hover over it.
If you are using an older version of SSMS or IntelliSense is turned off, you will not get these visual cues indicating a problem with the query.
Color Coding SQL Server Connections in SSMS
Another way to safeguard from running queries in the wrong environment is to color code the SQL Server connections. Change the properties of SQL Server registration and pick a custom color for the connection of each SQL Server.
Here is how the color-coded SQL Server connections look when connected using SSMS.
You can learn more about this in this tip.
Using SET NOEXEC ON
At a very high level, SQL Server Query execution consists of two parts: compilation and execution. Setting SET NOEXEC ON before the query guaranties that the SQL Server engine will check the syntax and object reference errors (during compilation), but it will not execute the actual query.
In the following code example, when you execute the command, SQL Server only does the compilation of the code and returns the message whether it is successful or a failure. In case of a compilation failure, SQL Server gives you a message why the query failed to compile.
Let's see another example to make sure that code does not execute when using SET NOEXEC ON. I turned on the "Include Actual Execution Plan" (Ctrl + M) feature to show if the statement actually executes. Just running the highlighted code in the below screenshot returns the query result and also the Execution Plan tab.
If the code is run using SET NOEXEC ON, you can see it only returns the message "Commands completed successfully" and there is no Execution Plan tab.
Using SET NOEXEC is useful for debugging your T-SQL statements that are part of larger set of code.
SET NOEXEC ON; GO SELECT name, database_id FROM sys.databases; SET NOEXEC OFF; GO
Using Condition Logic with SET NOEXEC ON
I personally like to use the following statement at the top of my SQL scripts. Therefore, if I am not on a development server, then the SET NOEXEC ON will protect me from running the code inadvertently in my production environment. In this example if the instance is not SQLDEV1 or SQLDEV2 then SET NOEXEC ON will be used.
IF @@SERVERNAME NOT IN('SQLDEV1', 'SQLDEV2') SET NOEXEC ON;
GO
You would need to add your instance names to the list.
Always using BEGIN TRANSACTION
Similar to the SET NOEXEC ON, you could also make sure that all of your queries always use a transaction. This way you can double check the results before committing or rolling back the transaction if needed.
Here is sample code.
BEGIN TRANSACTION DELETE FROM dbo.TestTable WHERE country = 'US' -- COMMIT TRANSACTION -- ROLLBACK TRANSACTION
After the query runs, if things are not what you expected or if you ran the query in the wrong environment you can issue the ROLLBACK TRANSACTION line. This will rollback the transaction and get you back to where you started.
If everything is fine, you could issue the COMMIT TRANSACTION line to save the work.
Default Commands in a New SSMS Query Window
This method provides some security against unintentional query execution, especially if we are working in development and production environment simultaneously. Basically, we want every new query window to open with the "Begin Transaction" command. So whatever query we execute in this session, it will not commit and we would need to commit or rollback the statements. You have to be mindful that you will need to explicitly commit or rollback the query execution, otherwise this will leave the transaction open and could block other processes. Be mindful that if you execute a query that takes a long time to complete and then you rollback the query this could take a long time to complete as well.
To add the default code to a new query window, we need to modify the SQLFile.sql and this file is located at "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql\".
I highlighted the folder for the version of SQL Server. This can be these values:
- 140 is the folder for SQL Server 2017
- 130 is the folder for SQL Server 2016
- 120 is the folder for SQL Server 2014
- 110 is the folder for SQL Server 2012
Initially the SQLFile.sql is blank. We can type in commands such as following and save the file. Note: You may need to open the file as an administrator to have the correct permissions to edit the file.
Now when you use the New Query button in SSMS or type CTRL + N to open a new query you will see these commands in the query window. Unfortunately, if you right click on a database in SSMS and select New Query this trick does not work.
You can also modify the SQLFile.sql with the "SET NOEXEC ON" command instead of BEGIN TRANSACTION and this will be at the top of each new query window or add some other comments to help you. Also, you could add the condition logic above to use NOEXEC if it is not a particular instance that you are connected to.
You can learn more about this in this tip.
Permissions and Different Logins
Most DBAs have elevated permissions in SQL Server and hence small mistakes can do a lot of damage. There are many different options available to DBAs to protect their systems and themselves from running the wrong queries in the wrong environment. Some of these options were discussed in this tip, but we still need to pay attention to what we are doing. We should test and choose options that are suitable for our environment.
One thing you can do is use different accounts when connecting to production environments versus development environments. For the account used to connect to production every day, the permissions are limited and if you need to run a command in production you would need to login with a different account that has the elevated permissions.
Next Steps
- Review Microsoft documentation on NOEXEC here.
- Learn about SET statements that change the behavior of current session settings.
- How to change the color of different connections in SSMS is explained in the tip.
- This tip explains modifying the SQLFile.sql file.
About the author
Ameena Lalani is a MCSA on SQL Server 2016. She is a SQL Server veteran and started her journey with SQL Server 2000. She has implemented numerous High Availability and Disaster Recovery solutions.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-12-17
