Easy Steps to Understanding SQL

Easy Steps to Understanding SQL

March 10, 2017

SQL is one of the most popular languages for communicating with RDBMs. While the basics of reading/writing data (SELECT, INSERT, DELETE) are straight forward, making use of things like stored procedures and triggers are more advanced topics that we will cover in this article. Specifically, we will look at stored procedures, functions, views, triggers and the advantages of using each to read/write data.

Stored Procedures

Stored procedures are essentially just packaged SQL commands that you can execute with EXEC. They are similar to bash scripts and provide users the ability to perform multiple reads/writes in a single command.

Although stored procedures can't be used in queries themselves, they have their advantages. They are arguably more secure as application logic is separated from changing logic. If an app calls a stored procedure updateUser() to update a user object then it doesn't have to know the details behind what's changing on the backend. It simply calls updateUser(). This makes for better separation of app logic and improves security.Another advantage is performance. Stored procedures can be faster because of precompiled execution. They can also be reused which improves development efficiency.

Triggers

Triggers are like stored procedures in that multiple reads/writes can happen in one command. The main difference between triggers and stored procedures is that triggers are tied to events. They listen for things like insert, update, delete and run based on those events occurring. Unlike stored procedures, you can't explicitly call a trigger. It must be tied to another database event.

There are advantages and disadvantages to triggers. While triggers make for great automated reactions to other events, they can be rather difficult to maintain. Unexpected things can start happening when you have hidden triggers defined that you forget about. By not having to explicitly call a trigger, developers can quickly forget what's happening with seemingly simple read/write operations.

Views

Views are like virtual tables. They can be queried like real tables but are stored on the database server as a SQL statements. Views give you the ability to encapsulate very complex joins, calculations, etc. into custom tables that are easier to query.

Views also have security benefits. Similar to stored procedures, users can query views without knowing the underlying tables or datasets they represent. This also makes it easier to change schemas and data models without breaking existing app logic. Users can still access the same view even though the underlying schema may change.

Functions

Functions can be written to perform additional computations inline with regular SQL statements. Functions are fundamentally different from stored procedures because they can't change data or create permanent changes to the database itself. They are essentially helper functions that make your database queries easier to manage.

Conclusion

Although stored procedures, functions, triggers, views etc. add more complexity to a seemingly intuitive language, it's important to remember that it's all SQL at the end of the day. If you're comfortable with writing plain old SQL statements then these are simply ways to more efficiently package those statements and improve performance.


Join the conversation...



Submit

understanding sql fully takes awhile. you have to understand the difference between things like stored procedures and triggers, when to use them and why. this is a great read for someone looking for intro to sql articles and lays out the key differences between sql functionality. but NoSQl is really the way to go.
Posted by Jeff on April 23, 2017
pick your poison :). this was a great read thanks for the simplified take on more advanced SQl topics. definitely more to it than just select statements ha
Posted by Sara Porter on March 16, 2017
triggers aren't that bad...:) thanks this helped...
Posted by Sundari Mittal on March 10, 2017


More by the stackchief