CRUD is backbone of every platform applications either Desktop, Web or Mobile apps. Basic concept of CRUD is similar in all databases RDBMS and NOSQL . Operations like Select, Insert, Update and Delete have there different performance levels on system; where Insert is on Top and Delete is on Bottom.
As my first post on this blog, I will share my knowledge about the hidden magic table which is working behind Update query. It will clear some misconception, see below two SQL Server Queries
UPDATE client set name='Daffodil Software Ltd'
Result : (7 row(s) affected)
UPDATE client set name='DBCC Pvt Ltd' where Company_id=111
Result : Error
Msg 229, Level 14, State 5, Line 6
The SELECT permission was denied on the object 'client', database 'TestDb', schema 'dbo'.
I have a table client with 7 records in my test database and a user “Appuser”. Appuser has no permission to select on table but it has permission to update records on name column. When our Appuser executed the 1st query, it executed successfully and updated 7 records. But when it executed the 2nd query it gave an error message 229. This error is related to the “Select” permission.
I learned a few things from above queries:-
1. Update uses “SELECT” when we use WHERE clause, which implies updating some specific records only.
2. When we update all records, no magic table will work.
3. Update with 'where' clause is slower because it is executing two queries.
4. We can update all records without select permission into table.
CRUD Performance Level.
We should design our application by considering CRUD operation's performance level .
1. Minimize Delete, More update and Most inserts.
2. We should do soft deletes instead of hard deletes from database. Hard deletes and more updates create fragmentation issues in database and then database will be subjected to performance issues, then it will require periodic maintenance. More maintenance more downtime, only leading to frowns on the client side.
3. Now-a-days storage has become cheaper so we should not worry about the size and cost of storage.
CRUD functions form the backbone of database and storage. And I am sure the above query will resolve a lot many glitches that many database workers come across. I hope you enjoyed reading the blog and to stay posted with the most latest news around Web, Mobile and Cloud, please subscribe using the subscribe button to your right.