SQL SERVER – SQL Basics Video: Using Management Studio – SQL in Sixty Seconds #060

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

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.

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: Using Management Studio – Day 5 of 10

Available in Paperback (USA), Kindle (Worldwide) 

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

About these ads

SQL SERVER – SQL Basics Video: Code Comments – SQL in Sixty Seconds #059

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

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.

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: Code Comments – Day 4 of 10

Available in Paperback (USA), Kindle (Worldwide) 

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

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)

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)