SQL SERVER – SQL Basics Video: Joining Tables – SQL in Sixty Seconds #058

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

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

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.

SQL in Sixty Seconds Video

We have attempted to explain the same subject in simple words over in following video.

Action Item

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

Read the related blog post: SQL Basics: Joining Tables – Day 3 of 10

Available in Paperback (USA), Kindle (Worldwide) 

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

About these ads

SQL SERVER – SQL Basics Video: Running SQL Code – SQL in Sixty Seconds #057

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

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

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.

Of course, as shown in the two earlier queries in the video, we can put square brackets around any table. In fact, the 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.

SQL in Sixty Seconds Video

We have attempted to explain the same subject in simple words over in following video.

Action Item

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

Read the related blog post: SQL Basics: Running SQL Code – Day 2 of 10

Available in Paperback (USA), Kindle (Worldwide) 

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

SQL SERVER – SQL Basics Video: Data and Information in Businesses – SQL in Sixty Seconds #056

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

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

Databases have been around since cavemen were drawing stick figures of their family’s on the rock walls. A database is a collection of related information. In the last 20 years what has improved greatly is we can get the information we need instantly from databases. For example if we ran a test promotion in Florida that we were thinking about running around the world we would want to know how well the promotion affected sales. In this case we want to compare the sales gains in Florida to all other areas. There may be millions of sales in Florida and billions of sales everywhere else. That is far too much data for a human ledger. We need a system that can both collect and pull out this information for us. SQL Server is a Database Management System (DBMS) that (if we know how to talk to it) will be our best business friend.

SQL in Sixty Seconds Video

We have attempted to explain the same subject in simple words over in following video.

Action Item

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

Read the related blog post: SQL Basics: Data and Information in Businesses – Day 1 of 10

Available in Paperback (USA), Kindle (Worldwide) 

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

SQL SERVER – SQL Basics: SQL 2012 Certification Path – Day 10 of 10

This is the 10th 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).

In training hundreds of people over that last decade to get their Microsoft certification, to my surprise the biggest benefit you get is something else. The most common benefit my students tell me about is that they get invited to far more job openings after getting certified than they did before. When the stack or resumes gets sorted down, they need a proven reason to keep yours on the short list.

The Current SQL Certification Paths

The new breakdown of the Microsoft SQL 2012 certification model offers new incentives and rewards. The entry level certification in the 2012 model starts with one of three certification tests.

The Querying test 70-461 is the new Developer entry level test. The Admin and BI entry tests are the 70-462 and the 70-463. The most important part of BI is data warehouses since that is the center of all your BI data.

SQL Development is a much larger topic than SQL Administration. There are two advanced levels of Development certifications. The 70-464 is Developing Microsoft SQL Server 2012 Databases and the 70-465 is Designing DB Solutions for SQL Server 2012 Databases.

The 70-462 test is the for the SQL Admin certification, but there is no advanced Admin certification for 2012.

Business Intelligence is another large topic and has with it an advanced track. To start the BI track you will need to pass the Implementing Data Warehouses certification to achieve the entry level BI certification. To advance beyond the entry level BI certification you can take the Implementing Data Models & Reports (70-466) and then the Designing BI Solutions (70-467) to achieve the advanced BI certifications.

The Microsoft SQL Server 2012 certification model only rewards you for advancing within a certification track if you cover all the main tests first. If you pass the 461, 462, and 463 tests your certification level advances to a  Microsoft Certified Solutions Associate (MCSA).

Once you achieve your MCSA and the two advanced level Dev certifications (464 and 465) the certification level advances you again to a SQL Dev MCSE (MCSE stands for Microsoft Certified Solutions Expert).

If you achieve your MCSA and both advanced BI certifications you then become a SQL BI MCSE.

To summarize, there are three base level tests for Dev(Queries), Admin and BI (Data Warehouse). But in the SQL 2012 model there are more skill related tests for the Dev and BI and combinations of the exams that can reward you with higher premium certifications.

EXAM EXAM EXAM CERTIFICATION
70-461 Querying 2012 Certification
70-462 Administering Database 2012 Certification
70-463 Implementing Data Warehouses 2012 Certification
70-462 70-463 70-463  Microsoft Certified Solutions Associate (MCSA)
70-462 70-464 70-465 Advanced Dev 2012
70-463 70-466 70-467 Advanced BI 2012
MCSA 70-464 70-465 SQL Dev MCSE or  Microsoft Certified Solutions Expert
MCSA 70-466 70-467 SQL BI MCSE or  Microsoft Certified Solutions Expert

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: What Are Filegroups – Day 9 of 10

This is the 9th 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).

Using Filegroups

There are many advantages to using filegroups to manage the database workload. A filegroup may contain many datafiles, and the properties of all the datafiles can be managed simultaneously with a filegroup.

Primary and Secondary Filegroups

A primary filegroup contains the primary datafile (mdf) and possibly secondary datafiles (ndf). All system tables are allocated to the primary filegroup.

A secondary filegroup (also called a user-defined filegroup) contains secondary datafiles (ndf) and database objects.

The default filegroup contains objects which were created without an assigned filegroup. The primary filegroup is the default filegroup unless another filegroup is specified.

Logfiles are never part of a filegroup. We learned about logfiles in yesterdays post. The logfile tracks all the changes that have taken place since the last database backup, whereas datafiles have the file extension .mdf or .ndf, logfiles always have the .ldf extension.

In the figure below we have one datafile called RatisCo_Data.mdf in the SQL folder of the C drive (C:\SQL\RatisCo_Data.mdf). Since we didn’t specify any filegroups, SQL Server automatically placed it in the primary filegroup for us. We also created a logfile in the same location (C:\SQL\RatisCo_Log.ldf). That file was not placed inside a filegroup, since logfiles are never part of a filegroup.

Our next example will create one datafile in the primary filegroup and two datafiles in the secondary filegroup (also known as the user-defined filegroup).

We can accomplish this with the following steps:

  1. Create one mdf (main datafile) in the primary filegroup.
  2. Create two ndfs (secondary datafiles) in a user-defined filegroup called Order_Hist located on a separate drive.
  3. Create the ldf (log datafile) on a separate drive.

Each data file has its properties set in its own set of parenthesis.  If you have two parentheses after a filegroup name then that filegroup will have two datafiles (like the [OrderHist] filegroup in the code below). The framework for the code is seen here.

If our goal is to put the mdf on the C: the two ndf files on the D: and the log on the E: then our code would be completed by doing the following:

CREATE DATABASE RatisCo
ON PRIMARY
(NAME = RaticCo_Data, FILENAME = 'C:\SQL\RatisCo_Data1.mdf'),
FILEGROUP [OrderHist]
(NAME = RaticCo_Hist1, FILENAME = 'D:\SQL\RatisCo_Hist1.ndf'),
(
NAME = RaticCo_Hist2, FILENAME = 'D:\SQL\RatisCo_Hist2.ndf')
LOG ON
(NAME = RaticCo_Log, FILENAME = 'E:\SQL\RatisCoLog.ldf')
GO

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

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)