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.
Types of Commands
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:
- DDL: Data Definition Language.
- DML: Data Manipulation Language.
- DCL: Data Control Language.
- TCL: Transactional Control Language.
- DQL: Data Query Language.
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 (Data Definition Language):
DDL commands are used to change the structure of database objects.
The DDL commands are:
- CREATE: to create objects in the database.
- ALTER: to alter objects in the database.
- DROP: to delete objects in the database.
- TRUNCATE: to delete all the rows from a table and free the space containing the table. This is like a reset command.
Now, let’s see what each of these commands looks like in practice.
CREATE:
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.
ALTER:
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.
DROP:
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;
TRUNCATE:
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 (Data Manipulation Language):
DML commands allow you retrieve, store, modify, delete, insert and update data that populates your database.
The DML commands are:
- SELECT: allows you to retrieve data from a table, selecting the data that you’re interested in using filters and other options.
- INSERT: allows you to insert new data into an existing table, whether to initially populate it or add new records.
- UPDATE: allows you to update existing data in an already populated table.
- DELETE: allows you to delete all the records from an existing table.
Now let’s take a look at each of these and how you might use them.
SELECT:
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.
INSERT:
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);
UPDATE:
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;
DELETE:
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 (Data Control Language):
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:
- GRANT: allows you to assign access privileges to a user.
- REVOKE: allows you to remove or revoke access privileges from a user.
- DENY: allows you to deny access privileges to a user.
GRANT:
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:
- LOGIN
- DATABASE
- OBJECT
- ROLE
- SCHEMA
- USER
An example of using the GRANT command could be:
GRANT EXECUTE ON Cloud TO jioller;
REVOKE:
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 (Transactional Control Language):
TCL commands are used to manage and control Transact-SQL transactions on the database.
The TCL commands are:
- COMMIT: used to save the transactions carried out to that point.
- ROLLBACK: allows you to restore the database to a point prior to running the most recent set of commands, in other words, back to the most recent COMMIT.
- SAVE TRANSACTION: used to set a savepoint within a transaction.
Now let’s take a look at how each of these commands is used.
COMMIT:
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;
ROLLBACK:
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.
Conclusion
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!
