Some Jargon from the SQL Server World

Not long ago I wrote about how to write well about SQL Server, and one major point was that jargon should be used sparingly and carefully.  Tied to that, I thought it might be beneficial to collect ten of the more commonly used jargon phrases in the world of SQL Server literature.  In doing so, I hoped to provide a concise list of terms that people joining this community might wish to be familiar with.  I also thought it would serve, as a reminder to the seasoned database professional that these were jargon and might not be understood outside our community, many even have an entirely different meaning in other communities.

To expand slightly on what I said in my previous post, jargon is not always a bad thing.  Sometimes there simply is no non-technical word to express a concept, or the technical term expresses it much more succinctly.  Some in sociology even say that jargon can help with group identity and group cohesion.  Yet, it can make communicating with those outside of the group, or new to the group, difficult.  Even those well versed in the terminology can find reading something which overuses jargon tiring and difficult to follow.  Jargon does not necessarily need to be avoided, especially when the target audience should be familiar with it, but it should be used sparingly and with care.

Here, I am focusing on terms which are relatively common within the SQL community, but which would likely be unfamiliar to those outside of it.  I am intentionally avoiding terms like IAM/PFS[1] that are relatively rarely used in the community.  I am also skipping words like table, row, and query that are amoungst the first things anyone working with SQL Server will learn.  Instead I am focusing on the words that are common but not elementary within the SQL Server community or those that are so common that people readily forget they are jargon, and I am not trying to be comprehensive even there.

1.    DBA  – The job title held by many who work with SQL Server is largely unknown outside the technical community.  Even spelling it out as “Database Administrator” has earned me quizzical looks when I talk to people outside of the computer professions.  I generally describe myself as a programmer when asked unless I know the other person actually has a technical background.  The abbreviated form also overlaps with multiple other terms, such as “doing business as” in the business/legal communities and “Doctor of Business Administration”.

2.    Normalization – Normalization is a way to organize tables and the relations between them to help reduce redundancy.  Under the right circumstances, normalization can help reduce the size of the data files, guarantee certain types of internal consistency, and make the database more flexible for future changes.  However, it can, under certain circumstances, come at a performance penalty and potentially some increased complexity in programming since it frequently involves creating more tables which must be joined together.  The term normalization is shared with many other fields such as sociology and statistics, where it refers to a way to remove certain types of statistical errors.

Normalization is a deep topic and is discussed in some detail in articles such as:

The Road to Professional Database Development: Database Normalization by Peter Larsson

Stairway to Database Design Level 9: Normalization by Joe Celko

3.    3NF – The Third Normal form is the normal form most often sought after for practical database implementations.  It was loosely summed up by E.F. Codd saying “Every Non-key attribute must provide a fact about the key, the whole key, and nothing but the key.”

4.    Codd – Speaking of E.F. Codd, many serious students of relational databases are likely to be familiar with his name.  He was a researcher for IBM who is credited with inventing the relational model for databases and created much of the foundations of the theory behind relational databases.  The phrase “so help me Codd” is also occasionally, jokingly, added to the description of 3NF.  His name is also attached to the Boyce-Codd Normal Form (BCNF, sometimes called 3.5NF).

5.    RDBMS – Relational Database Management System.  MS SQL Server is a prime example of this, but so are Oracle, MySQL, SQLite and others.  Relational databases are based, although sometimes loosely and with compromises, on the relational model first described by Codd.  Relational Databases can be contrasted with OODBMS[2] like ZODB and Key-Value Stores like BigTable.

6.    ACID – Atomicity, Consistency, Isolation, Durability.  ACID describes properties of a reliable database transaction.  To perhaps oversimplify, if a database is ACID compliant then either an entire transaction completes and is committed or none of that transaction is committed.  In SQL Server, the database engine provides mechanisms to ensure that transactions are ACID compliant.[3]   ACID is often contrasted with the BASE[4]consistency model, which describes some types of NoSQL databases.

More information at:
DBAs on ACID by Tony Davis

7.    PowerShell – PowerShell is a command line shell and scripting tool.  It permits access to the .NET framework including SMO, which permits relatively easy control of SQL Server.  There are many online resources for PowerShell, including:

Introduction to Windows PowerShell for the SQL Server DBA by Edwin Sarmiento

Exceptional PowerShell DBA Pt1 – by Laerte Junior

 

8.    CRUD – Create, Read, Update, and Delete.  They are four of the basic functions of any persistent storage system, including databases.  This comes up frequently in the context of describing an application that does CRUD work on a database, especially if that application does nothing but provide a convenient front end or GUI for users to perform CRUD operations on the database.

9.    GUI – Graphical User Interface.  This of course is a term that is used widely in tech circles, and programming circles in particular.  Most users, especially the technically sophisticated ones will already be familiar with this term, but not all.

10.  ETL– Extract, transform, and load.  When discussing databases, this is generally the process of preparing a data source and then loading it into the database or data warehouse.  I have occasionally seen this used in reference to removing information from a database and presenting it in a more user friendly format, but that seems to be a less common use.

Talking about SQL Server in particular, SSIS is one of the main ETL tools used.  But practically any programming language can be used for the purpose.  I tend to make extensive use of Python for ETL tasks that involve substantial preprocessing of the data.


[1] Index Allocation Map/Page Free Space.  I only really learned this one myself when I was investigating some surprising results on a performance test.

[2] Object Oriented Database Management System

[3] There are some subtleties here though.  Handling Constraint Violations and Errors in SQL Server by Phil Factor has details on some of the factors that affect rolling back transactions.

[4] Basically Available, Soft State, Eventual Consistency.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s