Author Archives: SQLGator
Question of the Day Five
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!
Question of the Day Four
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!
Question of the Day Three
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!
Question of the Day Two
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!
Question of the Day One
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!
Saving Time with SQL Server Migration Assistant for Oracle
After using the SQL Server Migration Assistant for Oracle on a current project to migrate an Oracle database to SQL Server, I have learned a couple of little tricks that I would like to pass on to you. These are mainly time savers so hopefully they will make you more productive in your project.
The first problem I noticed is that there was no easy way to copy projects nor was there a way to save a project file as another name, which would have allowed me to copy the project. Why would I want to copy a project? There are many settings such as column type mappings that will stay the same for my project and are very time-consuming to enter, if you are modifying the defaults. In my migration, I want to do many iterations in my development and acceptance environments with different SQL Server database names such as Round1, Round2, or Round3 and then use Red Gate Compare and Data Compare to verify that all of the schema and data from the migration are consistent in my migration. This gives me a warm and fuzzy feeling before I turn over the data to the testers.
However with each iteration, I must create a new project manually. Why? Because SSMA does not refresh to recognize new databases in the existing projects. That is very annoying. It only shows what is there at the time the project is created. Even if you reconnect to the SQL Server, it does not refresh.
If you know ahead of time that you will do many iterations, you could however create several databases before starting with the SSMA and then copy the project folder from the first project that you create manually. You must then rename the folder to the new project name and the corresponding project file (with an o2ssproj file extension) contained within the project folder. This would allow you to simply switch to the new database. You must also keep in mind that you need to double-click on the Oracle schema and make sure the target schema is selected as your new database. This will not happen automagically as you choose a new database name in the SQL Server metadata explorer.
Another option is to create new projects as you create the databases and then copy in the object-container.mappings and/or project-container.mappings files from the first project folder into the new project folder. This will allow you copy those settings into the new project and save you some time. I hope you find this useful. Enjoy.
Migration Tedium Pays Off
Today I am going through the tedium of checking row counts and randomly verifying data in round two of our conversion from Oracle to SQL Server. This is very monotonous and it would be incredibly easy to tell my boss that everything checked out ok. Who would notice if I skipped a few of these tests? Me. That thought would always be in the back of my head, is this data really the same data that was in Oracle or are we missing some data. It would be my Tell Tale Heart (Edgar Allen Poe references always make for a great blog post)!
Being a database administrator is never about glamour, but admittedly there are more entertaining parts of the job. However, do not overlook these minute details or you might be facing the opportunity to update your resume. Enjoy!
Duplicate Columns
Yesterday I needed to find several tables in a database that contained a certain column name. It is usually easy if you have only a few tables but this database has almost 2000 tables. I needed to use some T-SQL skills.
I consulted with the SQLHelp hash tag on twitter because it has been a while since I did this last and I knew someone there would remember how to do it quicker than I could. Robert Davis, MCM extraordinaire (blog |twitter) had the following line:
SELECT object_name(object_ID) FROM sys.columns WHERE NAME = ‘<column name>’
While Bill Fellows (blog|twitter) had a different approach:
SELECT TABLE_NAME FROMinformation_schema.COLUMNS ISC WHERE isc.COLUMN_NAME =‘<column name>’
Both worked very well and here they are here for you and for me when I need to remember how I did it. Enjoy!
SQL Saturday Submission Completed
My first full-length session submission for SQL Saturday has been input. Even if I do not get selected, I am moving forward in the right direction towards the completion of my goals for 2012. The topic, Best Practices Are Not Just Hypothetical, explores the best practice settings available during the installation that will set your server off on the right foot from the very beginning.
Stay tuned to see if my topic is selected for SQL Saturday Orlando to be held in Lake Mary, Florida, on September 29, 2012. Come on out and hopefully at the very least I can entertain you. Wish me luck! Enjoy!
Sunday Funday
Today’s installment of Sunday Funday brings you a great little Tic-Tac-Toe game with T-SQL exercise by Adam Haines found on SQL Server Central. Enjoy!
SQL Swampland 