Cloud and Systems
Useful Transact-SQL Commands for SQL Server
December 22, 2022
In this article, we’ll share some useful Transact-SQL commands for SQL Server that you can use to perform basic tasks to manage your databases.
Before we start, this article is written for people who are just getting started with Transact-SQL and don’t have much experience. If you’re one of those people, you will hopefully find everything here really useful as we explain all the basics for managing SQL Server databases using T-SQL commands.
The first thing that’s important to understand is that SQL statements are atomic, which means that they’re non-divisible. This means that either a statement is executed in full or not at all. If there is an error, the system will simply roll back to how things were before.
The second thing that you need to know is that all SQL statements are not just made of commands but also other components, such as parameters and variables.
NOTE: This article is not meant to be a complete Transact-SQL manual. It is merely intended to serve as a starting point for beginners or people with little experience. We’re not going to cover every possible SQL command or go into too much detail in this article.
In SQL, there are various types of commands designed to do different things, including defining, manipulating or extracting data.
The different types of commands available are as follows:
The most important ones are probably DDL, DML and DCL, but we’ll touch on the others too. In this article, we’ll take a look at each one and see some examples of how they can be used.
DDL commands are used to change the structure of database objects.
The DDL commands are:
Now, let’s see what each of these commands looks like in practice.
The CREATE commands is used to create objects, such as tables, etc.
Usually, you’ll use it to create tables on existing databases and assign countless properties such as fields, data types, etc.
The reduce syntax for CREATE TABLE will look something like this:
CREATE TABLE <TableName>
( { <column_definition> } [ ,… n ] );
Which adding the necessary variables, would look like this:
CREATE TABLE Customers
(
ID smallint NOT NULL PRIMARY KEY
Name nvarchar(50)
Address nvarchar(200)
Telephone int
Email nvarchar(40)
Contact nvarchar(40)
Account int NOT NULL
);
If creating a database, you’ll use a similar syntax that includes the destination, sizes, etc.
Once you’ve created a database or table, the ALTER command allows you to make changes. This might mean changing the definition of the table, adding or removing columns or setting restrictions.
Most of the time, you’ll be using the ALTER TABLE command. However, the syntax varies depending on whether you’re altering a disk-based or memory-optimized table.
The DROP command allows you to delete objects from the database, such as data, tables, views, etc. The DROP TABLE command, for example, will delete the entire table and all its data, restrictions, etc.
NOTE: The views and procedures associated with a table need to be deleted separately using the DROP VIEW and DROP PROCEDURE commands.
The syntax for a command to delete a table would be the following:
DROP TABLE [IF EXISTS] <TableName>;
For example:
DROP TABLE IF EXISTS Customers;
The TRUNCATE command deletes all the rows from a table or all the specified partitions, allowing you to delete rows one by one.
The TRUNCATE TABLE command is very similar to a DELETE command that doesn’t use the WHERE condition. The advantage of using TRUNCATE TABLE is that it’s much quicker and requires less resources.
The syntax for the TRUNCATE command is:
TRUNCATE TABLE <TableName>;
For example:
TRUNCATE TABLE Customers;
DML commands allow you retrieve, store, modify, delete, insert and update data that populates your database.
The DML commands are:
Now let’s take a look at each of these and how you might use them.
The SELECT command is the most commonly used command with SQL because it’s used to extract data from the database.
It’s essentially a query command, allowing you to show only those entries or fields that you’re interested in.
The syntax of the SELECT command is as follows:
SELECT <Options> FROM <TableName>;
For example:
SELECT * FROM Customers;
In the example above, «SELECT *» will select all the columns in the table. If you only want to select specific columns, you would use the names of the columns separated by commas. For example:
SELECT ID, Name, Contact FROM Customers;
You can also use the WHERE condition to only show columns that meet specific criteria. For example, you might want to only select customers from a specific sector.
The syntax for the SELECT command using the WHERE condition is as follows:
SELECT <Options> FROM <TableName>
WHERE <Conditions>;
For example:
SELECT ID, Name, Contact FROM Customers
WHERE Sector = ‘Reseller’;
NOTE: Because you will use the SELECT command a lot, we’ll dedicate a separate article to it in order to go into more detail and provide many more examples of how to use it.
The INSERT command allows you to insert data into a table, adding information to existing rows.
The syntax for this command is as follows:
INSERT INTO <TableName> [(column1, column2,…columnN)]
VALUES (value1, value2,…valueN);
Where “columnX” refers to the columns where the data will be inserted and “valueX” is the data that will be inserted into the corresponding column.
Here’s an example of what the INSERT command might look like:
INSERT INTO Customers (ID, Name, Address, Telephone, Email, Contact, Account)
VALUES (723, Jotelulu, Plaza Pablo Ruiz Picasso 28020 Madrid, 911333712, Manuel Perez, 723723723723);
If you want to insert data into every column, you won’t need to specify the individual columns, in which case the syntax will look like this:
INSERT INTO <TableName>
VALUES (value1, value2, …, valueN);
For example:
INSERT INTO Customers
VALUES (723, Jotelulu, Plaza Pablo Ruiz Picasso 28020 Madrid, 911333712, Manuel Perez, 723723723723);
As we mentioned earlier, the UPDATE command allows you to update a table or make changes to it by substituting or changing existing data.
This command uses the WHERE condition to identify the data you want to change and then the SET condition to specify the changes you wish to make.
The UPDATE command is a very destructive command, so use it with great care. If you don’t filter the data correctly using the WHERE condition, you could change the wrong data, and if you forget to include the WHERE condition, you could delete all the data contained in the table.
The syntax for the UPDATE command is as follows:
UPDATE <TableName>
SET
<ColumnName> = { expression | DEFAULT | NULL }
{,…n}
WHERE <search_conditions>;
For example:
UPDATE Customers
SET Contact = ‘Manuel Perez’
WHERE ID = 73;
The DELETE command allows you to delete multiple rows by selected them using the WHERE condition. If you don’t use the WHERE condition, the command will delete all the entries in the table, leaving it empty.
The WHERE condition for this command is used in the same way as for the SELECT command.
If you wish to delete the entire table, the syntax would be as follows:
DELETE [FROM] <TableName>;
But, if you want to delete specific entries, the syntax would be:
DELETE [FROM] <TableName>
WHERE <Conditions>;
For example:
DELETE FROM Customers
WHERE ID = 123;
DCL commands are used to manage access, allowing you to create roles, assign permissions and set other privileges. These commands are used to keep your database secure.
The DCL commands are:
The GRANT command assigns access permissions for an element that can be protected using access/security controls. It is used to assign permissions to a specific element, and the syntax is as follows:
GRANT permission ON object TO user
For example:
GRANT { ALL [ PRIVILEGES ] }
permission [ ( columnX)
[ ON [ class :: ] securable ] TO principal [ ,…n ]
[ WITH GRANT OPTION ] [ AS principal ] ;
Where the classes are:
An example of using the GRANT command could be:
GRANT EXECUTE ON Cloud TO jioller;
The REVOKE command allows you to remove or revoke permissions that you had previously assigned using the GRANT command.
The syntax for this command is as follows:
REVOKE [ GRANT OPTION FOR ]
{
[ ALL [ PRIVILEGES ] ]
permission [ ( columnX
}
[ ON [ class :: ] securable ]
{ TO | FROM } principalX
[ CASCADE] [ AS principal ];
And an example might look like this:
REVOKE SELECT ON SCHEMA :: Customers TO Sellers;
TCL commands are used to manage and control Transact-SQL transactions on the database.
The TCL commands are:
Now let’s take a look at how each of these commands is used.
The COMMIT command marks the end of a transaction that has been carried out correctly, whether explicitly or implicitly. When you run a COMMIT command, all the changes to data that were being made by that atomic transaction become permanent in the database. Once this has been done, the resources that were used to maintain the transaction history are freed up.
The syntax for the COMMIT command is as follows:
COMMIT TRANSACTION [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ];
And an example would look like this:
COMMIT TRANSACTION;
And in this case, we would see the entire transaction from start to finish, when all has gone correctly:
BEGIN TRANSACTION;
DELETE FROM Customers WHERE ID = 13;
COMMIT TRANSACTION;
The ROLLBACK command is basically the opposite of the COMMIT command. If the COMMIT command is not run, in other words, if there has been a problem running commands as part of the transaction, this should be rolled back.
This is done using the ROLLBACK command, which reverts an explicit or implicit transaction back to the beginning or to a savepoint within the transaction.
The ROLLBACK command will delete all changes made since that savepoint, freeing up the resources used to maintain the transaction history.
It’s important to note that this doesn’t include changes to local variables or table variables.
The syntax for the ROLLBACK command is as follows:
ROLLBACK TRANSACTION [ transaction_name | @tran_name_variable ];
For example:
ROLLBACK TRANSACTION;
And in this case, we would see the entire transaction from start to finish, when everything has been done correctly:
BEGIN TRANSACTION @TransactionName
INSERT INTO ValueTable VALUES(1);
ROLLBACK TRANSACTION @TransactionName;
That concludes this foray into the world of useful Transact-SQL commands, and it certainly won’t be the only one. There are plenty of other commands to look at, and there is much more to be said about some of the ones we’ve covered here too.
In this article, we have introduced you to some useful Transact-SQL commands that you can use to create, modify or delete objects on SQL Server. We’ve covered what each of these commands is used for and even provided some examples of syntax.
We hope that this guide has been useful for those of you who are just starting to learn how to use SQL language for Microsoft SQL Server.
For those of you who would like to carry on learning more, we recommend checking out the following link, where you’ll find more SQL-related articles.
Also, you can find much more information on the Microsoft Learn site.
Thanks for reading!
