Over the last few posts, we’ve discussed DDL and DML. This week I want to actually discuss the other 2 types of languages with SQL Server – DCL and TCL.
DCL is the Data Control language. These are the commands that give security permission to a person – or group – to get things done. For example, some of those commands are as follows:
- GRANT – gives permission to an object
- REVOKE – removes access to an object
- DENY – completely rejects permission to an object
For most of you out there, you won’t have to worry about this set of commands. They are all taken care of for you by your friendly DBA. Yes, those folks who have to deal with keeping the SQL Server running at peak capacity also have to deal with setting up permissions for everyone.
And our last group of commands is grouped under Transaction Control Language (TCL). It’s simply the commands that deal with transactions in SQL Server – something that should be near and dear to all database developer’s hearts.
The commands are as follows:
- BEGIN TRANSACTION – the start of the transaction
- COMMIT TRANSACTION – everything is good. Save the transaction.
- ROLLBACK TRANSACTION – Error Will Robinson! Something went wrong, act like this transaction never happened.
- SAVE TRANSACTION – save point time!
If you are writing – or deleting – data from a database, you should be using transactions on a consistent basis. If you haven’t been working within transactions when dealing with SQL Server, why not?
Transactions are a great way to group a bunch of INSERTs or DELETEs or both so that everything is complete for a process or none of them are complete. This lets your database have data integrity – always a great idea for databases.
And with that, we are now done with our sets of commands for databases. Next time, we’ll discuss transactions and different things you can do with them to make your lives a bit easier in the data world.
This week’s song is a topic near and dear to my heart – data integrity. Well, kinda. Integrity anyway.