SQL SERVER – SQL Basics: Database Datafiles and Logfiles – Day 8 of 10

This is the 8th post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

Logging Data Changes

In my experience, many students do not find the concept of datafile and logfile activity an intuitive one. So we will ease into it with an example that we have found helps students grasp this topic more quickly. But first we need a little explanation as to why SQL Server uses logfiles.

We know that SQL Server stores its data much like other applications, in files which are saved to a persistent drive. But a distinguishing feature of SQL Server is its robust ability to keep track of things. The security and safety of the data and reliability of the system are SQL Server’s top priorities. Therefore, you can imagine that logging activity, which tracks every transaction made in the database, is a pretty big deal. Examples where logging saves the day generally involve some type of database restore or recovery need. Once a database backs itself up, we are generally assured a reliable mechanism we can use to restore the system in case something unfavorable happens. Suppose we notice bad data has come into the system through one of the periodic feeds. In fact, this data is so problematic that the team decided we must restore the database back to the point a week ago. This gets us back to a time before the bad data began entering the system. The periodic database backup is built using information provided by the logfile. Logfiles keep track of the database transactions and help ensure data and system integrity, in case a system recovery is ever necessary.

Ok, now we’re ready to tackle datafiles and logfiles. A datafile is fairly straightforward – it contains all the current data. Suppose we’ve been making changes to JProCo’s Employee table. If we could peek into the datafile, we would find data identical with the result of SELECT * FROM Employee. However; it wouldn’t tell us that an hour ago, we deleted an employee record, or that today at 9:45 AM the manager added a new employee record to the table.

We sometimes compare the datafile to getting information from the ATM. Usually we are happy with the data the ATM shows us (i.e., the current balance), and it always provides us the data rapidly. But if we need to look back and see deposit or withdrawal information, the ATM can’t help us. To see the transaction detail which has led to the current account balance, we need to look at our bank statement. Logfiles are just like the transaction history shown in a bank statement, where we can find a separate transaction entry for every purchase, deposit, or withdrawal made. The two identically structured WordPad files are going to help us visualize the transaction activity retained by the logfile.

 

These are pretty small files, just 1 KB each. We made make some significant edits to Document A, which we won’t make to Document B. Not only will the documents differ visually, but when we see that the changes cause Document A’s size to expand to 6 KB, it’s clear that Document A and Document B are no longer identical files.

 

Where my classes tend to find the “ah-ha” moment is when we actually see the changes in Document A being removed one by one as we use Edit and Undo to backtrack and see the edits disappear. Similarly, if we delete a few words one by one, the Undo operation will backtrack and make each word reappear one by one. What the application is doing is traversing through the log of changes and accessing memory to find all of those changes. If the changes weren’t logged, they wouldn’t be available.

 

At the end of the demonstration, Document A has been returned to its beginning state, it contains the information identical to Document B and we’ve saved the file in order to clear the logged changes from memory. Thus, Document A and B are each 1 KB in size at the end but just prior to saving Document A, we make another interesting “ah-ha” observation. On the surface, both documents appear identical.  However; when we compare the size of the two files, Document A is many times larger than Document B. In my classroom demos, the file grows to 250 KB with relatively few clicks. The log tracks changes made to the document from the last time it was saved up until the current moment.

 

At this point, an expert DBA would understandably be bored silly with this demo. However; over the years, we’ve found this the fastest way to ramp up students new to the abstract concept of the work done by logfiles.

Document A’s condition at the beginning and end of the demo (i.e., 1 KB and reflecting the data “This File is Small.”) serves as a comparison to the datafile. Because the file was saved at the beginning of the demo, and then again at the end, the document showed just the current state of the data. This has nothing to do with tracking data which was added or deleted along the way. The datafile’s purpose is to reflect the current state of the database.

Database Datafiles and Logfiles

Step 1. Pretend we have a brand new database with one table (Employee) which contains zero records. There are no records in the JProCo database, so there are no records in the datafile, and since we haven’t made any changes to the database, there are zero records in the logfile.

 

Step 2. Now data starts coming into the JProCo database. You add one new record for Alex Adams to the Employee table. So now we have one record in the datafile and one record in the logfile.

 

Step 3. You then add another record (Barry Brown). Two records are now in JProCo, so two records are in the datafile and two records in the logfile. So, we have two pieces of data and two entries in the logfile reflecting those changes.

 

Step 4: The next step updates an existing record. Employee 2 is coming back from leave, so we’re going to change his status from “On Leave” to “Active.” There will still be two records in the database, so the datafile will contain two records. But there will be three records in the logfile since we made three changes since the last time we backed up the database.

 

Step 5: The database is backed up nightly at midnight. After the three earlier changes happen, suppose it’s after midnight and the database backup has just finished running. At 12:05AM there would still be two records in the JProCo database so we would have two records in the datafile. During most backup processes, the changes contained in the logfile are sent to the backup file. The logfile is emptied as part of the backup process, so zero records remain in the logfile immediately after each backup.

 

Step 6: On day 2, we insert Lee Osako’s record (the third record added to Employee). At this point we have three records in the datafile. The logfile has been empty since the backup, and this change now adds one record to the logfile.

 

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – SQL Basics: Database Careers – Day 7 of 10

This is the 7th post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

The Live System

The live system is the one that interacts with our customers and must stay up during all business hours which is often 24-7 in today’s global business world. These databases should be designed to collect the data in transactions that are needed to do business.

 

This is often called the Online Transaction Processing database (or OLTP database). This OLTP system must respond immediately to user requests through a proper interface (such as an ATM). The ATM is accessing a database based on the customer’s request. The OLTP System is the SQL Server that is holding our OLTP database.

During peak business hours the OLTP system can slow down and cause customer delays. If we were to run a query for a business meeting during this peak time we could slow the live system even further. For this reason any testing or analysis is often done on a copy of the database. This way company work and research can get done without interfering with the customer’s usage of the same database.

Database Developers and Database Administrators

Both the Database Developer and the Database Administrator have their career revolving around live OLTP systems. To build the database that works with ATMs the developer needed to know all the information that would be collected by and shown to the ATM. Based on this specification they can build the correct tables and write the correct queries. Once that is up and running then these tables and queries get used over and over again by each customer who uses an ATM.

 

If the SQL Server goes down then this can cause the ATM to shut down. We need alerts to let us know when the system is running slowly or going down. When this happens we need to react quickly to restore the system. It is the job of the Database Administrator to monitor and protect live database systems.

The Analytical System

It’s very common for the business to want to see a query about how things are going. Running a query on the live server would only cause to slow down the OLTP system. There is a need to copy the database to a new SQL Server so it can be analyzed. Once it is analyzed by a SQL professional it will need to become a report that other people in the company can view. The data from this database is often turned into an Excel spreadsheet or an internal web page.

The OLTP system may be taking millions of transactions per day but you might only copy the latest data over once per day. This new system will not need to specialize in transactions but instead need to be very fast at analyzing queries. This new offline system is known as the Online Analytical Processing Database (OLAP). This is where data turns into information.

 

So there are three major steps to turning data from your OLTP system into information via the OLAP system.

1. Copy the live data to an OLAP system.

2. Run the analysis on the data that is needed.

3. Deliver the data in reports to the right people.

The BI Developer

The process of copying the live OLTP database into an OLAP database to deliver information is known as a “Business Intelligence” Solution (Often called BI).

The BI developer does not work on live data but instead analyzes data that came from the live system. The BI developer uses a suite of tools that is part of SQL Server to move data, analyze it into information, and build company reports.

 

To tool used to move data from one system to another is called SQL Server Integration Services (SSIS). The tool used to analyze the data is called SQL Server Analysis Service (SSAS). The tool that turns this information into human readable reports that can be posted or e-mailed is called SQL Server Reporting Services (SSRS). These three tools make up the BI suite for the BI Developer to use. An Administrator or Developer can move on to BI once they are certified or have significant experience. Joes 2 Pros will have a BI certification series ready for you by December 2013.

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Basics: SQL Code Generators – Day 6 of 10

This is the 6th post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

Almost everyone these days feels comfortable with using a “point and click” process to get things done with a computer. Only a small percentage of us have ventured into writing code or working with command line utilities to create the programs that perform these tasks. Whether we write code or use clicks, what really matters is getting the job done. Computers will respond to their instructions, regardless of how they receive them. Whether we copy a file with a drag and drop operation, or use the copy command in a console window, the end result is the same; the file has been copied from one place to another.

With the SQL Server Management Studio User Interface (SSMS UI), we can create a table by writing code in a query window, or use the “point and click” method. Point and click allows us to create a table without writing any code ourselves. It is much easier to send someone the code script to accomplish a task with SQL Server than it is to send step-by-step instructions on how to point and click their way to accomplish the same task. For this reason, code script is the most common method used to consistently and reliably deploy new databases and objects the exact same way on many systems.

It is much faster to run code that has already been written and tested by someone else than it is to accurately and consistently navigate the user interface. There are times when creating long constructions of code is faster with the SSMS UI than it is to write out each line of code by hand. In addition, some people simply prefer to point and click their way to creating objects in SQL Server.

In either case, it is possible to instruct SQL Server to convert all the point and click steps into T-SQL code scripts. There is a handy feature within SQL Server known as a “Code Generator” which automatically generates T-SQL scripts to perform specific actions for objects that already exist in the database, including the database itself.

Automatic Scripting

To demonstrate how code generators work in SQL Server Management Studio let’s start by expanding the dbBasics database folder in the Object Explorer and looking at some of the tables it contains.

There are two tables in the dbBasics database which appear to be duplicates.

There is a [Shopping List] table (with a space between the words), as well as a [ShoppingList] table (without a space between the words). When we look at the two queries above our suspicions are confirmed. In addition to having similar names, these two tables share the exact same data and we don’t need both of them in the database at the same time.

One way to solve this dilemma is by using the point and click method. We can right-click on the [Shopping List] table, and then choose the Delete option from the pop up menu. This will open a Delete Object dialog box that gives us several choices. We are NOT going to click OK yet but want to go over the buttons first. We can click the ‘OK’ button to confirm we want the table deleted, or we can press the ‘Cancel’ button to exit out of the operation. What many SQL users are not aware of is that when we press the OK button, SQL Server will actually execute T-SQL code in the background, relieving us from having to write the code to delete this table ourselves.

What if we are asked to help delete this table on a server that someone else owns, and that we are not allowed to access directly? This is a common situation. When a SQL Server is being used in a production environment, very few people have access for security purposes. Since our job is to provide the code that will be run on this production SQL Server, it will be up to someone else to perform the action to delete the [Shopping List] table either with code or a point and click solution.

Years of personal experience tells us that if we try to talk someone through all of the point and click steps necessary to accomplish this task there is a chance for a misunderstanding. A simple misunderstanding when deleting a table could have an immediate negative effect on mission critical readiness, which is definitely not a desirable outcome. It will be far easier and safer to create this action as a script that we could send to the company’s SQL Operations team. This would allow another person to run the scripted action on their machine with the exact same results as the code we have already safely tested. This is also a better result as the other person can complete their work with just one execution step.

A task like the one just described is a great use of the code generator feature. The SSMS UI, as the T-SQL code generated for these types of actions will always be consistent and safe. It is the exact same code that SQL Server runs when we perform a task via the point and click method.

We can discover how to use the code generator by going through each of the point and click steps (plus one to generate code). Instead of pressing OK as the last step to delete the table, we will press ‘Cancel’ to prevent the table from being deleted until we test the code that has been generated for us by SQL Server.

We have been asked to write a script that can be used to delete the Members table at some point in the future. Of course, we are tasked to write this script when we are already in the middle of an important test. The quickest and safest way to write this new script is to use the SSMS UI to generate the code for us.

The first step is to right-click the Members table of the dbBasics database then choose the Delete option from the popup menu. This will open a Delete Object dialog box giving us several options to choose from.

Warning: DO NOT left-click the OK button during any of these steps as it will instruct SQL Server to execute the code which will delete the Members table, preventing this exercise to work correctly.

The next step is to find the Script dropdown menu button located at the top of the right-hand pane in the Delete Object dialog box (figure below). Simply left-click on the dropdown menu arrow and then choose the Script Action to New Query Window option. Finish the process by left-clicking on the Cancel button to exit the dialog box and view the code generated in the new query window.

A new query window will open in SSMS containing the code generated to delete the Members table of the dbBasics database. An example of what this code will look like is shown here:

USE [dbBasics]
GO
/****** Object: Table [dbo].[Members] Script Date: 8/29/2013 2:15:19 PM ******/
DROP TABLE [dbo].[Members]
GO

We can verify that this code works properly by clicking on the ‘! Execute’ button to run the code. When this is complete it will be necessary to refresh the Tables folder inside the dbBasics database (right-click the Tables folder, then select the Refresh option in the popup menu). Once this folder is refreshed we can visibly see that the Members table no longer exists in the dbBasics database.

The SSMS code generator is very useful in these types of situations, as it will quickly produce reliable code to perform basic database maintenance actions. It also comes in handy for those times when it is difficult to remember exactly what syntax is necessary to write certain T-SQL code. It can even serve as an excellent learning tool to show what code will accomplish certain actions that we have yet to work with.

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Basics: Using Management Studio – Day 5 of 10

This is the 5th post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

By now you have written several queries. This means you have opened SQL Server Management Studio and then opened a query window to write your code. Once it came time to execute your code you can do so by pressing F5 or clicking the Execute button. Management Studio connects to your server and provides you handy tools to manage your databases. You even have the Object Explorer to browse all the parts of your server to see what is present. But what if one of your windows is gone? Or what if you are doing a presentation and the fonts are too small to be seen in the back of the room? You can customize the look and feel of Management Studio to your liking. This blog post will show you how to navigate the common windows in Management Studio.

Management Studio Windows

Today is a normal day at the office. We are working in Management Studio and have a query window open and we are ready to write a query. We may want to explore the server so we also have the Object Explorer open on the left.

 

It is possible that you need to look at something in the Object Explorer and cannot find it. It is possible to move and resize windows in Management Studio and Object Explorer is the most commonly used window. If we were to click the pushpin icon in Object Explorer as shown in the 1st figure below the entire window will collapse to the left  as seen in the 2nd figure below.

 

Collapsing Object Explorer can be useful if we need to increase the size of the query window to view a long line of code. To open Object Explorer simply click on it against the left margin and it will open over the query window. The pushpin icon will be lying on its side. To pin it beside the query window simply click the pushpin icon again and Management Studio will return to the configuration. If we need to quickly look at Object Explorer but not open it back up completely we can hover the mouse over the Object Explorer on the left margin and the window will appear. As soon as we remove the mouse the Object Explorer will collapse again. This automatic opening and closing of the window is called auto hide.

We can also cause Object Explorer to float with a click and hold on the title bar, dragging it, and letting it go in a new location. This can even be outside the constraints of the Management Studio desk top.

 

It is possible to snap the Object Explorer in each of the four compass headings in the editing area of Object Explorer. When we click and hold the title bar a compass appears. If the mouse pointer is hovered over one of the nine points on the compass and released, the Object Explorer will snap into the corresponding position. In the figure below the nine positions are numbered and we will choose position 2.

 

On the compass, position 9 will fill the entire editing area, position 6 will snap the Object Explorer to the right half of the editing area, and position 2 will snap it to the right quarter of the editing area.

 

Go ahead and play with this tool by snapping Object Explorer to different positions on the compass. When you are done, we can set the screen back to the default settings by selecting Window > Reset Window Layout. A popup window will appear asking if we are sure we want the default settings, select Yes.

 

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Basics: Code Comments – Day 4 of 10

This is the 4th post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

Code Comments

Old classic movies utter this famous phrase “Gentlemen, this is off the record”. In the movies this is used when talking to the press and letting them know a certain comment or two will be said, however; it is not meant for publication in the media. Sometimes, we want to use words or phrases within a query window that we want SQL Server to ignore when executing the code.

Fortunately, SQL Server allows us to write words or phrases that are “off the record”, with a coding technique called commenting. When writing a query in the SQL Server query window, we can use special symbols to instruct the query engine to ignore these portions of the code when the query is run.

These special symbols also allow comments to visually look different than the rest of our code, which makes it much easier for the human eye to distinguish from the rest of the code as being something unique, and indicates this portion has a special purpose meant specifically for a human to read and for the computer to ignore.

Thus the main purpose of commenting techniques is to allow us to write words or phrases that are often descriptive notes or warnings of what the code is doing, when to run it and why it is necessary. This is a big benefit when other people, such as our team members, supervisors, or software testers are reviewing the code for their own purposes. In other words, comments are commonly used as a mini informational ‘readme’ file allowing other people reading it to learn more about the code being used.

Another great use of commenting techniques is to troubleshoot or test smaller segments of code without needing to modify or delete surrounding blocks of code that are around the segment we want to run by itself. This is a more specialized use of comments that every good SQL Developer needs to know.

Single-Line Comments

How can we make just one line in a query window not run, while the other lines of code still run as expected? Typing two hyphen signs ‘–’, one after the other without a space, will instruct SQL Server to ignore every character that follows it on the same line. This coding technique is known as a single-line comment.

In the figure below we see a query with a single-line comment written as the first line, describing the purpose of the code below it. The comment in this example shows other developers and testers what action the code is expected to accomplish. Indeed, when we run this code block, the result set contains all the fields from the Employee table and the Location table based on the matching LocationID field using the INNER JOIN keyword.

--This query joins the Employee and Location tables together.
SELECT *
FROM Employee AS em
INNER JOIN Location AS lo
ON em.LocationID = lo.LocationID

 

However; the description is overly simple, as it does not explain why the two tables are being joined together, and what service or department might need the results from this query.

It is important to use comments wisely, by considering who, what, when, where, and why for describing the purpose of the code. The challenge is to write notes that balance being descriptive and brief, so anyone else that may read it days, weeks, months or even years from now can understand why it exists and what it is meant to achieve.

A better example of how comments can be used to describe code in a work environment is shown in the figure below. While adding in more detail by indicating who the code is written for (HR), the specific purpose of why the code exists (Employee Report), and also draws attention to which fields from each table need to be included in the results (all Employee fields and the Address, City, and State Location fields).

Since the comments in this example take up two lines in the query window, it is necessary to start each new line of comments with a new set of double hyphen signs, so SQL Server will know to ignore the first two lines of the query.

--This gets all fields from Employee table
--plus Address, City, & State from the Location table
SELECT em.*, lo.Street, lo.City, lo.[State]
FROM Employee AS em
INNER JOIN Location AS lo
ON em.LocationID = lo.LocationID

It is also possible to use single-line comments for testing specific segments of code. This commenting technique gives us the ability to temporarily prevent a segment of the code block from being able to execute, while the remaining code we want to inspect closer is able to run.

Let’s say that while building the code for the HR Employee Report, something was not working right. We suspect there is a problem with the join to the Location table and believe that the code for the Employee table segment is correct. We want to test our theory by only running the segment of code with the SELECT statement for the Employee table without having to rewrite it as a separate query or deleting any of the code in the INNER JOIN segment.

This can be accomplished by commenting out the last two lines of the code, which will instruct the SQL Server query engine to ignore these two lines of code and only run the first two lines of code. The following code will not execute the INNER JOIN for the Location table and will only return the records from the Employee table, as shown in the figure below.

SELECT *
FROM Employee AS em
--INNER JOIN Location as lo
--ON em.LocationID = lo.LocationID

Multi-Line Comments

There are times when we want to run part of a query or just a few lines for a quick test. Once the test is over we want all of the code back in its original state. For these purposes many SQL developers choose to highlight (select) only the segment of code they are interested in executing and ignore the remaining code block.

This next code example demonstrates how it is possible to run a segment of code that is part of a larger code block without using any commenting techniques.

Notice that the SELECT and FROM lines of the query are highlighted in blue for the Employee table. While the INNER JOIN is part of our query, since it is not highlighted (selected), this segment of code will not run when the segment of code that is highlighted in blue gets executed. When we run the highlighted segment of the query, we see that only the fields from the Employee table are returned. The segment of code with the INNER JOIN is ignored by the query engine.

--This query joins the Employee & Location tables together
SELECT *
FROM Employee AS em
INNER JOIN Location AS lo
ON em.LocationID = lo.LocationID

When we want to comment out many consecutive lines of code, we have two different commenting techniques we can use to accomplish this goal. Use single-line comments for each and every line to be prevented from running, or use the more efficient multi-line commenting technique to disable small or large segments of code with an easy to use beginning and ending sign.

The first option is to use single-line comments by placing the double hyphen sign at the beginning of each line we want to prevent running when the query is executed. We used this technique in the previous section, so the following code should look familiar.

SELECT *
FROM Employee AS em
--INNER JOIN Location as lo
--ON em.LocationID = lo.LocationID

The second option is to use multi-line comments by placing a ‘/*’ (forward slash, asterisk with no spaces) sign at the start of the segment to be commented out and then close the comment with a ‘*/’ (asterisk, forward slash with no spaces). Let’s see how this works by using the same query we have been practicing with. The results of using this multi-line commenting technique are the same as those returned by using single-line comments.

SELECT *
FROM Employee AS em
/*INNER JOIN Location as lo
ON em.LocationID = lo.LocationID*/

In this example of commenting out only two lines of code, typing two sets of ‘–‘ signs, is about the same amount of work as typing a matching set of ‘/*’ and ‘*/’ signs. If this is the case, why choose one technique over the other?

The amount of effort to type the ‘–‘ signs for a single-line comment increases dramatically as the number of continuous lines to be commented out grows. What if we wanted to disable the last 300 lines of code? Typing ‘/*’ and ‘*/’ signs one time each, is definitely easier than typing the ‘–‘ sign 300 times to achieve the exact same result. Unlike the double hyphen, which can only instruct SQL Server to ignore one line of code at a time, the ‘/*’ ‘*/’ signs (delimiters) are more efficient for multi-line commenting as there can be an infinite number of lines of code between the opening and closing delimiters.

Imagine if we had needed to comment out these 300 lines of code for testing purposes and as soon as we completed our test, it was necessary to uncomment the same 300 lines of code for the next member of our team to review. Removing one set of opening and closing ‘/*’ ‘*/’ delimiters is much simpler and faster than removing 300 sets of double hyphens!

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Basics: Joining Tables – Day 3 of 10

This is the 3rd post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

Relational Data

When was the last time we received a vague answer to a question? For most of us, it happens every day. Let’s say we asked someone where they worked. We are anticipating a response that may include a city name or address, except the answer we actually get is, “I work at headquarters”. While this is an accurate answer, it is not the detailed answer we wanted to know.

After detecting a slight New England accent from James Newton, we decide to look him up in the Employee table and discover that he works at LocationID 2. In what city or state is this mysterious LocationID 2 located? A quick query of the Location table shows us the following data.

SELECT *
FROM Location

Now each time we see an employee listed for LocationID 2 we know the street, city and state information for where they work. Why not just store this information in the Employee table? In fact, why not put all of our data in one giant table so we only have one place to go to get our information? That is a common interview question about database design so let’s review a few database design best practices to start building our interviewing arsenal.

One reason is a lot of space in memory is saved by not replicating all three data items for each employee in a table. Another reason for having location fields only in the Location table is that it saves us time as well. For example: What would happen if the office at LocationID 2 physically moved from Boston to a new building in nearby Cambridge? If all the data were in a giant table, then we would have to update every street, city and state for each employee individually. This is a very tedious task that leaves a great deal of room for errors and inconsistency in how the data is entered. Placing the LocationID field only in the Location table means all employees with LocationID 2 map to an update we can make just once.

So, how do we find an employee’s address if the information is spread between two tables? Each table has a LocationID field inside it. We can then use a two-part identifier, to distinguish them as the Employee.LocationID field corresponds to the Location.LocationID field.

Look at Alex Adams and Barry Brown in the following figure. These employees both work at LocationID 1. If we were new to the company and only had access to the Employee table, we would not have enough detailed information to send a parcel to Alex Adams. What if we put two tables next to one another on our screen? By physically drawing a line from the Employee.LocationID field to the Location.LocationID field we can get more location details for each employee. LocationID 1 is located at 111 First ST in Seattle, WA.

What about a global company with locations in all 50 states and over 100 different countries? We will have many records in our Location table and probably will not be able to look at both tables very efficiently on one screen.

How can we effectively see information in two different tables at the same time? Our ultimate goal is to show the Employee and Location information in one result set. Since we have not learned the code on how to do this yet, it is not shown in this figure. The results shown are the goal of the upcoming example.

Inner Joins

So far, we have learned that each query can have only one result set and will only allow a single FROM clause. How can we place two tables in one FROM clause? We can include many tables in one FROM clause by using a JOIN clause. The most common type of a join is called the INNER JOIN.

An INNER JOIN clause allows us to join multiple tables in a single query, although it requires a specific condition in order for it to work correctly. We must ensure that the INNER JOIN statement has two tables with at least one common or overlapping field. We already know the Employee and Location tables share a common field (LocationID). The relationship is between Employee.LocationID and Location.LocationID, so we instruct SQL Server that the INNER JOIN is on this field and voila! We have combined two tables into one result set.

Every time a value is found in Employee.LocationID, the inner join will searche for the matching record in the Location.LocationID field. If a match is found, data from both tables are displayed as a single record. Both tables will show all their fields if we type SELECT * at the beginning of our query.

SELECT *
FROM Employee INNER JOIN Location
ON Employee.LocationID = Location.LocationID

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Basics: Running SQL Code – Day 2 of 10

This is the 2nd post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

Running SQL Code

When we run SQL code, it is often a series of SQL statements created by someone else. Still we are often tasked with adding to the code to customize it for our system or testing the code and making suggestions. For this reason the SQL Admin must know basic SQL coding skills. This section will focus on the most common types of queries. If being great at all types of queries is important to you, then we recommend a deeper study with the Joes 2 Pros SQL Queries series starting with Volume 1 for beginners and work through to Volume 5.

Single Table Queries

When we want information from a database, we write a query, such as SELECT filename(s) FROM TableName
. In English we might say, “Show us the information!”

Let’s write a simple query. We will use the dbBasics database context and request all records and fields from the ShoppingList table as part of the result set. The keyword SELECT requests information to be displayed and the asterisk (*) sign is a shortcut for all the field names. The keyword FROM chooses a specific table.

We are able to run this query to retrieve the information we want by either clicking on the ! Execute button in the toolbar above the query window, or by pressing the F5 button on our keyboard (some keyboard configurations require pressing the fn (function) + F5 key simultaneously).

Basic Query Syntax

A group of students in one of my weekend classes helped to write a neatly summarized guide listing the meaning of words in a basic SQL query. Here is the code sample:

USE dbBasics         --choose DATABASE Context
GO                   --complete USE statement
SELECT *             --choose Field(s) to display
FROM ShoppingList    --choose the Table

Now is a good time to point out that words can be placed in a query window that have nothing to do with the SQL code itself. We can write non-SQL words and notes like “I wrote this query today” above the SELECT statement. In order to do this we must tell SQL Server to ignore this text since it is not intended to be code. In fact, it should be non-executing code known as comments. To make comments, we need to begin the line with two hyphen signs, one after the other with no spaces between them. Change the database context back to JProCo with the drop-down window in the toolbar.

Use this example of code:

-- I wrote this query today
SELECT * FROM Location

The preceding code runs fine in JProCo. SQL Server ignores the first line because of the double hyphens. The comment is there only for the benefit of the human reading it. This is a useful way to make notes that later provide us, or our team with key hints or details explaining what the code is attempting to accomplish.

Table names can optionally have square brackets around them and it will not change the result set. Changing back to the dbBasics context, we can run the following two queries. Although one query uses square brackets and the other does not use brackets, they will both operate identically by returning a result set.

SELECT * FROM [ShoppingList]
SELECT * FROM ShoppingList

When coding, we rarely use square brackets because it requires extra typing. The only time it is helpful is when we can’t tell when a table name starts or stops. For this demo, the dbBasics database has two identical tables named ‘ShoppingList’ and ‘Shopping List’. The latter contains a space in its name, which is generally a bad naming practice for any database object.

For situations like this we must use square brackets as a delimiter. Without these delimiters, SQL Server will think the table is named ‘Shopping’ and there is a command named ‘List’, which it does not recognize and will result in an error message.

Of course, as shown in the two earlier queries, we can put square brackets around any table. In fact, code generated automatically by SQL Server always creates these delimiters for every object in the database. The only time we must use the square bracket delimiters is when table names are separated by a space, have the same name as a SQL Server keyword (bad idea), or are otherwise named in a way that is not obvious or easily interpreted by SQL Server.

We can easily correct this error message by placing square bracket delimiters around the Shopping List table name. Once this is complete, the query will run without an error  and we will get the same seven records in our result set as the first query using the ShoppingList table (not the [Shopping List] table).

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (http://blog.sqlauthority.com)