C-R-U-D The Basics

What is CRUD? Well, there are a lot of things I can think of when I use the term CRUD; however within the wonderful world of technology CRUD is an acronym that surprisingly enough, when I polled, didn’t really give a straight answer. That really surprised me; so with that said I will walk you through the basics of the concept.

C-reate

The letter “c” stands for create; more specifically inserting some form of data by various methods or means into a repository or holding take. I want to take this down to its simplest form which the below example will depict:

Inserting data into the PastHist table is as simple as the T-SQL query; but don’t just limit the thought of creation by this simplest form. You could have data being created through front end apps, SSIS packages, or many other methods.

R-ead

Depends on who you ask or talk to. The letter “r” can stand for read or retrieve. A simple retrieve statement can be a Select as below:

What is this he didn’t use a NOLOCK – don’t worry I can save that for another blog post but as you can see in its simplest form retrieving the data out of the table you just inserted into is not that cumbersome.

U-pdate

The letter “u” can mean update or modify if you will. Let’s see how easy it is to do a simple update statement below:

Let’s update the name for our record to be John where the id = 1

As you can see the data has been updated and John is now our data set in the name field column.

D-elete

The letter “d” stands for delete or destroy. Below is a simple script to delete the data out of the table:

There are some methods I could have used to remove the data; such as Truncate Table but this is not the time or place for me to distinguish between the two. I chose the delete method on the search criteria of id = 1

Results after the deletion:

Conclusion

This is breaking down C-R-U-D into its simplest form. Please do not limit your thinking to these very basic simple queries. Expound on it and your thought process on what all C-R-U-D entails. As with anything you find on the internet DO NOT take queries and just execute them blindly on any environment. Doing so makes you assume the risk.

What else are others saying?

I tell you what; check out what my other colleagues have to say on something they learned recently around C-R-U-D:

• Julie Koesmarno: Currently on sabbatical
• Jeffrey Verheul: Using OUTPUT clause to track changes
• Mickey Stuewe: Going Beyond The INSERT Statement

On a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.