If you have worked with database, you can work with SQL Server, but do you really understand how SQL server work? So today we will see how SQL Server work ?
SQL Server work
- SQL DB: This is your SQL Server database, every things have saved on that.
- Data_change: This is a memory use to save data in session, when you have change database with insert, update, delete statement.
- Trans_Log: Transaction Log use to save the changes in database. Example if you have a data table with one million raw and you want to delete all raw of the table. You will use DELETE statement to do that, the DELETE statement will help you delete delete one million raw data but it will record one million raw data you just delete to transaction log. In spite of you have delete one million raw data of the table and it have done, but the transaction log have save one million raw data it, so if you want to restore that data, you can go to transaction log to get data and insert it to table. Transaction log will save all data change, though the statement successful or not successful. Example if you query run a half and it have error, a half data change it will save to the transaction log.
- First, when you use some query to change database like delete, update, insert some things new to the database, all data have changed is in the Data_change
- Second, with three statement delete, update, insert it will work line by line, it mean if you want update data for one million raw, it will run from one to one million, so if one raw have done, it will save to Trans_Log.
- Third, if the query to change data is success, it will save to the SQL DB, if it fails it will not save to the SQL DB.
If the Trans_Log is full, it will free up data based on stack.
Some time you don't want to save data change to the Trans_log when delete, you can use Truncate to delete, but the Truncate will delete all your data table and space saved of table, if you want to use the table again you must create it again, be careful when use Truncate statement.
Tags:
mlops