Let us continue our question of the day series with day nine.

Where does referential integrity mean?

Referential integrity means that when a table references another table the foreign key must always point to a real and valid row in the corresponding table.  In other words, if you have an order table and an order line item table and you delete the order table, there should not be an order line item records for that order.  Referential integrity ensures that those related records are update or deleted accordingly.  The tables are in essence synchronized.

Enjoy!

Let us continue our question of the day series with day eight.

Where are different types of triggers?

There are two types of triggers, Data Manipulation Language triggers (DML) and Data Definition Language triggers (DDL).  DDL triggers are fired whenever Drop Table, Alter Table, Create Table or Login events.  DML triggers are fired instead of or after the triggering event.

Enjoy!

Let us continue our question of the day series with day seven.

Where are SQL Server usernames and passwords stored?

SQL Server usernames and passwords are stored in the System Catalog Views: sys,server_principals and sys.sql_logins.  Keep in mind that the passwords are encrypted.

Enjoy!

Let us continue our question of the day series with day six.

What are the authentication modes available for SQL Server?

There are two authentication modes available:  Windows mode and mixed mode.  In other words, active directory authentication (Windows) and SQL Server (mixed).  There is no option to allow only SQL Server authentication, if you choose mixed mode then you are allowing both options.  Active directory authentication is the more secure of the two options.

Enjoy!

Let us continue our question of the day series with day five.

What is ACID?

ACID is an acronym that is used in relational databases to determine whether it meets architecture criteria.  The acronym stands for Atomicity, Consistency, Isolation, and Durability.  When we evaluate the architecture or design of the relational database all four of these attributes of the acronym must be met in order for the database to be considered reliable.

Atomicity is an all-or-none proposition in regards to transaction which are a unit of work to be committed or rolled back.

Consistency will guarantee that a transaction will never remain unfinished in your database.

Isolation keeps transactions separated from the database and from each other until they are committed or rolled back.

Durability guarantees that the database will track all changes so that if any sort of termination occurs the server can recover.

Enjoy!

Let us continue our question of the day series with day four.

What is the difference between a primary key and a unique key?

The unique key and the primary key designation both enforce that the particular column is unique.  However, the primary key will create a clustered index using that column and a unique key will create a non-clustered index.  This is the fundamental difference in the two.  Another minor difference is that the unique key allows you to have one null value and the primary key does not allow any use of the null value.

Enjoy!

Today we continue our Question of the Day series with question number three.  In case you are just joining us, we are doing a question a day as an exercise in SQL Learning.

What is fill factor?

My first response to this question in normal day-to-day conversation is that fill factor is a great SQL guy that I follow on twitter.   All joking aside, fill factor is an option that will determine how full to create each index page.  In other words, when the index page is filling up and does not have enough room to insert a new row, then SQL Server will generate a new index page and move some of the rows from the last index page to this new page.  This is known as a page split.  If we use the fill factor option, then the server will reserve a set amount of space on each index page and thus will reduce the number of page splits.  The default value for fill factor is 0.

Enjoy!

Today we continue our series with our second question of the day.

What is the difference between a clustered and non-clustered index?

In simplest terms, a clustered index is the actual data, reordering the way that the records are physically stored.  Because of this, each table can only contain one clustered index.

However, a non-clustered index is more like a pointer to the actual record.  The logical order of the non-clustered index does not usually match how the data is stored physically.

There is also a third option, the heap, which is a table without an index at all.  Without an index, the server would have to read through all of the records in order to find the data queried.  Enjoy!

For the next week, I will be on vacation and then when I return I have a big project that needs my full attention.  However, being dedicated to blogging daily I thought it might be an interesting little journey for remainder of the month of August would be to post a question of the day with the answer.  I am not trying to steal any thunder from the SQL Server Central QotD where you test your skills for points against the world.  This will be simply an exercise in SQL learning.

So let us begin with day one…

What is normalization?

In simplest terms it is the organizing of data in a relational database to reduce the duplication of data.  In order to accomplish this we would split one large record entity into smaller tables that are linked by their relationship.  For example, if you had a simple database that stored orders of widgets.  You would have a table that contained customer data and a table for order information.  You would not want to store customer contact information in each record of the order table.  There would be one customer record and then possibly multiple order records linked by a customer ID field to ensure a relationship and preventing you from redundant customer contact information in each order record.  However, to the user who receives an invoice that contains his order and his contact information the data seems to be one contiguous unit.

Just in case you did not know, this theory was pioneered by E. F. Codd in 1970.  Enjoy!