SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

The Voice of the DBA

Interviews: What is ACID?

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.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

ADVERTISEMENT
Database DevOps

Database DevOps Demo Webinar

Learn how to automate your database deployments alongside your app code in this free demo webinar. Register now

SQL Monitor

Don’t just fix SQL Server problems, prevent them from happening

SQL Monitor helps teams looking after SQL Server be more proactive. Not only does SQL Monitor alert you to current issues, it gives you the information you need to stop them happening in the future. Download SQL Monitor now and get a 14 day free trial

Featured Contents

 

Stairway to U-SQL Level 17: Executing U-SQL Jobs with PowerShell

Mike McQuillan from SQLServerCentral.com

Automate your Data Lake with PowerShell! Learn how PowerShell can be used to execute U-SQL scripts, as well as a few other little tricks. More »


 

Free eBook: SQL Server Execution Plans, Second Edition

Press Release from Redgate

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 »


 

Investigating the Cause of SQL Server High CPU Load Conditions When They Happen

Additional Articles from SimpleTalk

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 »


 

From the SQLServerCentral Blogs - Help! My query is too fast!

Kenneth Fisher from SQLServerCentral Blogs

Said no one ever. Well, maybe. I have had occasions where I needed a brief pause in the middle of... More »


 

From the SQLServerCentral Blogs - Columnstore Indexes – part 112 (“Linked Servers”)

Niko Neugebauer from SQLServerCentral Blogs

Continuation from the previous 111 parts, the whole series can be found at http://www.nikoport.com/columnstore/. This blogpost is focusing on the Server... More »

Question of the Day

Today's Question (by Steve Jones):

I have a data set of football players and the total touchdown passes they threw in 2016. My data set looks like this:

> MyQBs[, c("Name", "TD"), drop=FALSE]
                 Name TD
1          Drew Brees 37
2          Joe Flacco 20
3       Blake Bortles 23
4       Aaron Rodgers 40
5        Carson Wentz 16
6        Kirk Cousins 25
7         Eli Manning 26
8       Carson Palmer 26
9    Matthew Stafford 24
10      Philip Rivers 33
11     Jameis Winston 28
12        Andy Dalton 18
13         Derek Carr 28
14       Sam Bradford 20
15     Russell Wilson 21
16        Andrew Luck 31
17         Matt Ryan+ 38
18         Cam Newton 19
19     Brock Osweiler 15
20 Ben Roethlisberger 29
21         Alex Smith 15
22     Trevor Siemian 18
23       Dak Prescott 23
24     Marcus Mariota 26
25       Tyrod Taylor 17
26          Tom Brady 28
27   Ryan Fitzpatrick 12
28     Ryan Tannehill 19
29   Colin Kaepernick 16

I plot these values with:

barplot(MyQBs$TD)

I get an image like this:

How do I add a horizontal line at the median TD value to the plot?

Think you know the answer? Click here, and find out if you are right.


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: R Language.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

ADVERTISEMENT

Pro SQL Server Relational Database Design and Implementation

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

Yesterday's Question (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?

Answer: 1.5

Explanation:

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).  

The overall result is 1.5 + 0 = 1.5.

Ref: 


» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2016 : SQL Server 2016 - Administration

DBCC CloneDatabase() - Morning All, I am using DBCC CloneDatabase to provide developers with a shell of a database to dev and testing purposes. Our...

Best way to keep users/logins/mappings syncrhronized in HAG between primary and secondary replicas? - We recently had a network issue and the availability groups failed over from the primary to the secondary.  The next...


SQL Server 2016 : SQL Server 2016 - Development and T-SQL

difference between early start date and recent end - can someone pls help with the code to get the difference in seconds between the recent end date and earliest...

ssrs 2016 pivot capability over tabular models - Hi, my tabular model has survey data with various dims including interviewer (employee), form, study, case #, questions etc.  And the core...


SQL Server 2014 : Administration - SQL Server 2014

Removing Cluster Disk - We have a two node cluster and we recently added a 1TB cluster disk and joined it to SQL Server....


SQL Server 2014 : Development - SQL Server 2014

Blocking but not sure why - I have a lead blocker with a "sleeping' SPID and wait type of "NULL", so its appears it is not...

Which is GOOD/BETTER out of two ways of writing a query ?? - Problem statement : I have 2 tables "DEPT" which contains department details and "EMP" which contains employee details. I am joining...


SQL Server 2012 : SQL 2012 - General

Job failing with maximum row size exceeds the allowed maximum of 8060 bytes. - Hi All, Noticed that our job is failing with below error message.not sure why this appearing now ,earlier its working fine...

SSMS 2016 and SSMS 2012 on same machine? - Dear all, I have installed SQL Server 2016 MS in my machine now I would to install also SSMS 2012. To...


SQL Server 2012 : SQL Server 2012 - T-SQL

Execution time Improvement - Knowing the problem, but not the solution - Hi all, A query I've got isn't running particulary fast (a bout 2 and a bit minutes). I know, in the...


SQL Server 2008 : SQL Server 2008 - General

? on Parsing an XML field - Hi Can I parse  an XML field as below, For example parse out "Review Type" Thanks Joe <Parameters xmlns="http://www.any.xsd"> <Parameter Name="Addendum2" StringValue="True" /> <Parameter...

view time out - I am having a view on mutiple tables across 4 databases, and one of the table is having 6 million...


SQL Server 2008 : T-SQL (SS2K8)

Mortgage amortization table - *** EDIT 2015-03-04: please be advised the code I posted originally below was in need of some improvement, and Solomon...


SQL Server 2008 : SQL Server Newbies

SQL server Max Memory setting is not working - Hello, i am trying to set Max server memory on the SQL server memory settings, here is my current system spec: Windows...


Reporting Services : SSRS 2012

Group by Month - I used to be the go-to-guy for SQL and SSRS in another life. But alas, I'm now manager who rarely...


Data Warehousing : Integration Services

Need to use a script data source to pull in 10,000+ characters of data in one Excel column - I have spent two days trying to set up a data source for an Excel file with a variety of...


Database Design : Design Ideas and Questions

Column Arrangement - Hello, 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...


SQLServerCentral.com : SQLServerCentral.com Website Issues

A user you are following, "...", has posted new content: but I'm not following this user - Today I received an email A user you are following, "...", has posted new content:but I'm not following this user "Thom A", has...


SQL Server 2005 : Administering

Cancel Query Rollback - Hi, I cancelled a long running update statement that was running against a large table and now sql server is rolling...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
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.
This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com