When designing a data schema to store a variety of entities it can be common to require a selection of flags to be stored. There are different ways in which the data can be designed to accommodate this which I wanted to look at here. Examples of these fields could be an ‘Is Active’ flag […]
When processing data there is frequently a need to rank the outputs. This could be to limit a selection of records to return, or provide a type of leader-board for reporting. There are a number of ranking functions within the SQL language which we’ll look at here with examples and outputs. We’ll start with some […]
When outputting data through a procedure it’s a common use case to add fields which have been calculated from existing data. One way to achieve this is using the CASE statement, however we don’t always need that level of complexity, particularly when we’re looking for a binary result such as ‘Yes’ / ‘No’. An alternative […]
When developing database solutions we’ll typically spend some time considering security requirements, which will result (in part) with database roles being assigned to users or groups of users. Whilst security considerations will likely be focused on what folks can do in our databases, there’s a separate impact on what they can see in them too. I’ve had more than […]
Last time out we looked at how data can be persisted even with a ROLLBACK being executed. Here we’re going to take that and look at an example of using it in action. We’ll generate some data – which may be good or bad – and try to add it to a table. If there’s any bad data […]
We recently looked at the impact of rolling back transactions on statistics and I thought it would be worth following this up to look at some other objects to see how they behave when a rollback occurs. When rolling back transactions we expect any changes to be rolled back. This isn’t always the case with data, just […]
Last week we started to at the use of constraints to support with data integrity in our databases. As a refresher on our introduction from last time: Data integrity helps give us assurance that our data is accurate, complete, and free from errors or inconsistencies. Some of this comes from making sure that data is correct […]
Data integrity helps give us assurance that our data is accurate, complete, and free from errors or inconsistencies. Some of this comes from making sure that data is correct before it enters are databases, but there are some tools we have in our databases which can help with this too – Constraints! Before we jump […]
I recently saw an Office Hours (at sea) Q&A from Brent where he was asked if statistics were rolled back along with a transaction. It’s a great question which I’d never come across so thought it would be worth looking into. TL;DR: They aren’t, but they may need to be updated when the data is next queried. […]
Last time out we looked at modifying data in a view, but this only worked when we had a view referencing a single table. Here we’ll look at how we can achieve the same result for a view which references multiple tables. This one will require a little more work for us in the form of some extra […]
Andy Brownsword 