Disappearing Rows
Social media has created an Always Connected World for us. On the second day of the event after the learning was over, I noticed lots of notification from my friend on my various social media handle. He had connected with me on Twitter, Facebook, Google+, LinkedIn, YouTube as well SMS, WhatsApp on the phone, Skype messages and not to forget with a few emails. I right away called him up. Let us learn about ON UPDATE CASCADE and ON DELETE CASCADE.
If you think data of a row or column is disappeared from Excel file, check whether you have hidden it temporarily. You can find out this issue by searching for discrepancies in your rows and columns. In order to Unhide all data, use the visibility option of MS Excel. How to display or hide Row & Column Headers in Excel? By default, we can locate cells through worksheets with column heading at the top and row heading at the left. Sometimes, you may want to hide these headings. This article will talk about some tips for displaying or hiding headings in Microsoft Excel.
The problem was very unique – let us hear the problem in his own words.
“Pinal – we are in big trouble we are not able to figure out what is going on. Our product details table is continuously loosing rows. Lots of rows have disappeared since morning and we are unable to find why the rows are getting deleted. We have made sure that there is no DELETE command executed on the table as well. The matter of the fact, we have removed every single place the code which is referencing the table. We have done so many crazy things out of desperation, but no luck. The rows are continuously deleted in a random pattern. Do you think we have problems with intrusion or virus?”
After describing the problems he had pasted a few rants about why I was not available during the day. I think it will be not smart to post those exact words here (due to many reasons).
Well, my immediate reaction was to get online with him. His problem was unique to him and his team was all out to fix the issue since morning. As he said he has done quite a lot out in desperation. I started asking questions from audit, policy management and profiling the data. Very soon I realize that I think this problem was not as advanced as it looked. There was no intrusion, SQL Injection or virus issue.
Well, long story short first – It was a very simple issue of foreign key created with ON UPDATE CASCADE and ON DELETE CASCADE.
CASCADE allows deletions or updates of key values to cascade through the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed. ON DELETE CASCADE specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. ON UPDATE CASCADE specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. (Reference: BOL)
In simple words – due to ON DELETE CASCASE whenever is specified when the data from Table A is deleted and if it is referenced in another table using foreign key it will be deleted as well.
In my friend’s case, they had two tables, Products and ProductDetails. They had created foreign key referential integrity of the product id between the table. Now the as fall was up they were updating their catalogue. When they were updating the catalogue they were deleting products which are no more available. As the changes were cascading the corresponding rows were also deleted from another table. This is CORRECT. The matter of the fact, there is no error or anything and SQL Server is behaving how it should be behaving. The problem was in the understanding and inappropriate implementations of business logic.
What they needed was Product Master Table, Current Product Catalogue, and Product Order Details History tables. However, they were using only two tables and without proper understanding the relation between them was build using foreign keys. If there were only two tables, they should have used soft delete which will not actually delete the record, but just hide it from the original product table. This workaround could have got them saved from cascading delete issues. I will be writing a detailed post about the design implications etc in my future post as in above three lines I cannot cover every issue related to designing and it is also not the scope of the blog post. More about designing in future blog posts.
Once they learn their mistake, they were happy as there was no intrusion, but trust me sometime we are our own enemyand this is a great example of it. In tomorrow’s blog post we will go over their code and workarounds. Feel free to share your opinions, experiences and comments.
Reference: Pinal Dave (https://blog.sqlauthority.com)
How to display or hide Row & Column Headers in Excel?
By default, we can locate cells through worksheets with column heading at the top and row heading at the left. Sometimes, you may want to hide these headings. This article will talk about some tips for displaying or hiding headings in Microsoft Excel.
Display or hide Row & Column headings in active worksheet or all worksheets
One click to hide or show the Ribbon Bar/Formula Bar/Status Bar in Excel
Kutools for Excel’s Work Area utility can maximize the working area and hide the whole Ribbon Bar/Formula Bar/Status Bar with just one click. And it also supports one click to restore hidden Ribbon Bar/Formula Bar/Status Bar. Full Feature Free Trial 30-day!
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment..
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Display or hide Row & Column headings in active worksheet
If you want to display or hide the Row headings and Column headings in the active worksheet, you can do as follows:
Go to the Show group on the View tab, and uncheck the Headings option to hide the Row & Column headings, while check the Headings option to display the headings. See screenshot:
In our example, we uncheck the Headings option on the View tab, and both row heading and column heading are hidden. See screenshot:
Note: You can also click File (Office Button) > Options > Advanced, and then check or uncheck the Show row and column headers option to display/hide the both row heading and column heading. See screenshot:
Display or hide Row & Column headings in active worksheet or all worksheets
The above method can only display/hide the Row & Column headings in one worksheet at a time. But sometimes, you need to display/hide the Row & Column headings in all worksheets. Low action ibanez. Display/hide the headings in each worksheet one by one? No, that's too tedious! Here I will introduce Kutools for Excel's View Options utility to batch display/hide the Row & Column headings in all worksheets together.
1. Click Kutools > Show / Hide > View Options.
2. In the opening View Options dialog box, please uncheck the Rows & column headers option to hide the headings, and then click the Apply to all sheets button and Ok button successively. See screenshot:
Notes:
(1) For displaying the Row & column headings, please check the Rows & column headers option;
(2) For only displaying/hiding the Row & column heading in active worksheet, please do not click the Apply to All sheets button.
The View Options utility of Kutools for Excel makes it possible to show or hide most of Microsoft Excel settings quickly, such as Inner Tabs, Formula Bar, Status Bar, Windows in Taskbar, Gridlines, Page Breaks, Display Zeros, Vertical Scroll bar, Horizontal Scroll bar, Sheet Tab, … etc. It will save your time in looking for these settings when you need to show or hide them. Click to know more
Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Get It Now