I once had a job interview with a large group of employees at a company. They were arranged around a table, with me at one end. The interview was a round robin affair, with someone asking me a question and me answering. They would take turns, going around the table for a couple hours. Not the easiest or hardest interview I've had, but one of the more interesting.
The first question from the gentleman to my left was, "What does ACID mean?". My answer: "Isolated, Consistent, ..."
I don't have a problem taking a few seconds to think, but I have that internal clock, much like an American football NFL quarterback. Mine is not the 5 second one many QBs have, but I know that after about 20 seconds, people are wondering if I'll answer at all. Or if I'm still focused on the interview. If I go 30 seconds, I might have blown the interview. What would you do here? Do you know the other terms? More importantly, can you answer at a level beyond the words of the acronym?
My response after 10 seconds or so was to admit I couldn't remember the word. I could google for the meaning, but I did know this applies to relational database transactions, intending to ensure that we always have a known state for our data. We know that each transaction must full complete or be completely undone. We can't have the classic issue of depositing money in one account and failing to remove it from another. We also cannot have other transactions interfere with other's work. This means a transaction on a piece of data must complete before the next transaction can modify it. We also must ensure that if the system were to crash, our database could not restart with data in an unknown state. Therefore, in SQL Server, we write to the log first, ensuring the transaction is complete before we can be sure the data changes are hardened. There are more details you could add to my answer, but this is a core foundational part of relational databases.
I have never been asked this question in another interview, but I do think this is one of those core concepts that helps me understand and explain other parts of SQL Server, and even of how to build software. I think understanding this will help you answer other interview questions with more depth and knowledge. Hopefully, you'll have a better answer than I did, with the actual words that make up the acronym.
Every day, out in the various online forums devoted to SQL Server, and on Twitter, the same types of questions come up repeatedly: Why is this query running slowly? Why is SQL Server ignoring my index? Why does this query run quickly sometimes and slowly at others? My response is the same in each case: have you looked at the execution plan? More »
Any DBA who is trying to find the cause of an intermittent problem with a server or database dreams of being able to use a query or procedure take a snap of the relevant variables at the point when the problem occurred. Laerte takes an example of a slow-running query hogging resources to show that you can run queries when a WMI alert is fired, and save the results for later inspection, whenever it happens. More »
Learn effective and scalable database design techniques in a SQL Server 2016 and higher environment. This book is revised to cover in-memory online transaction processing, temporal data storage, row-level security, durability enhancements, and other design-related features that are new or changed in SQL Server 2016. Get your copy today from Amazon.
Yesterday's Question of the Day
(by Mike Tanner):
In mathematics, multiplication and division are said to be commutative, meaning that it doesn't matter which order the operation is performed in.
The way SQL Server performs numeric calculations follows clear rules, including operator precedence and data type conversion precendence. Usually what SQL does is what you'd expect in mathematics, however, sometimes there can be unexpected results.
In this example
create table #t (a real, b int, c int);
insert into #t select 2,3,4;
select a*b/c + a*(b/c) as Total from #t;
What the value of Total?
It's easy enough to cut and paste and try, but can you get the right answer yourself without running the code?
The precedence of * and / is equal, so it goes from left to right. The issue here is with implicit data type conversion.
For the first part, we have effectively (2.0 * 3) / 4 = 1.5
that is, the conversion to real affects 2.0 * 3 giving 6.0, and the divisor 4 is implicitly converted to real, so we get the mathematically expected answer of 1.5.
Brackets over-ride the order, so for the second calculation we have 2.0 * (3 / 4). 3 and 4 are both integers, so the result of 3/4 must be an integer, and the result is 0 (fractional parts are truncated).
? on Parsing an XML field
Can I parse an XML field as below,
For example parse out "Review Type"
<Parameter Name="Addendum2" StringValue="True" />
view time out
- I am having a view on mutiple tables across 4 databases, and one of the table is having 6 million...
I would like to understand about column arrangement in multi table relationship as mentioned below
1. Job Planning Header
2. Job Planning...
Solution to Polymorphism?
- I am looking for a solution to one of the Code Smells listed in Phil Factor's list, (https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-code-smells/#using-a-polymorphic-association) and would...
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.