Common Questions Archives

Here is a list of common questions I’ve been asked by my students in class or via email.

Whether you’re looking to find out the difference between of primary an foreign key or want to know what is a data dictionary, you’ll find answers to many common problems.

If you have a question that I haven’t answered, please ask! You can contact me here. I’m always looking to see what you guys need helps with, as it helps me learn! I love helping!

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters. You can get started using these free tools using my guide Getting Started Using SQL Server

Can I have a CASE Statement in the WHERE Clause?

A very common question I get asked is whether I can have a CASE Statement in the WHERE Clause. There are a number of examples of CASE being used in SELECT columns, or in ORDER BY that we tend to forget CASE can be used wherever an expression is expected. Where Can I use a […]

SQL Stored Procedures – why do we need them?

I recently put together a lesson on why we need SQL stored procedures. It is part of my Stored Procedures Unpacked course. SQL Stored procedures are a great way to encapsulate logic securely, while allowing client program an easy way to execute it. The good news is that writing SQL stored procedures isn’t as hard […]

Error 3154 – Restore SQL database is terminating abnormally

As the word “Restore” suggests, this Microsoft SQL server error 3154 is associated with SQL Backup. Even though SQL Administrators have vast experience in setting and regulating of SQL database and its backup, they get stuck when they experience SQL database error 3154 because this error is displayed when users try to restore SQL database (DB) […]

How do I handle a “Error converting data type” error?

A reader recently asked about a error converting data type error they received. Since this is a common issue, especially when numeric data is stored withing VARCHAR datatypes, I thought you would appreciate the answer I shared with them. I’m using Windows 10 and SQL SMS 2014. When I run a query, I get the […]

How to include a single quote in a SQL query

In this video we’ll walk you though how to include a single quote in a SQL query; we’ll show how to escape the single quote, so it is treated as text rather than text delimiters. Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about […]

What is the difference between a primary and unique key?

In this article we learn the difference between a primary and unique key, and why both are important to maintaining a relational database structure. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL […]

What is the Difference Between Merge and Update?

When modifying one table based on the contents of another it may be tempting to simply use the merge statement, but before you throw away all other forms of modifying data, I think it’s important for you to know the difference between merge and update. What is the Difference Between MERGE and UPDATE? Both the […]

Know When to Use Cast versus Convert

In this article, we will be exploring the CAST and CONVERT functions to understand whether there are any significant differences worth knowing when we want to data from one type to another. Since the CAST and CONVERT can be used interchangeably in most situations, you may wonder if one function is better than the other. […]

What is the Difference between Inner and Outer Joins?

Both inner and outer joins can be used to combine data from two or more tables; however, there are some key differences! Read this article to find out each command’s strengths and when to use them. To get the most of this and our other lessons be sure to practice using the examples! All the […]

What Is the Difference Between a Join and a UNION?

Both Joins and UNIONS can be used to combine data from two or more tables. Read this article to find out each command’s strengths and when to use them. To get the most of this and our other lessons be sure to practice using the examples! All the examples for this lesson are based on […]

Subqueries versus Joins

Joins and subqueries are both be used to query data from different tables and may even share the same query plan, but there are many differences between them. Knowing the differences and when to use either a join or subquery to search data from one or more tables is key to mastering SQL. All the examples […]

What is the Difference between TOP and OFFSET & Fetch?

Difference between top and offset and fetch

Both TOP and OFFSET & FETCH can be used to limit the number of rows returned. OFFSET and FETCH can return similar results to top, but there are differences which may influence which method is best for you to use in your given situation. All the examples for this lesson are based on Microsoft SQL […]

Foreign and Primary Key Differences (Visually Explained)

In this article we learn the difference between a primary key and a foreign key, and why both are important to maintaining a relational database structure. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started […]

SQL DISTINCT and TOP in Same Query

This article is inspired by a series of questions that one of my readers, Nan, recently sent me regarding DISTINCT, TOP, and ORDER BY. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using […]

Is it possible to use Joins, Subqueries, and Unions in one Query?

A reader recently asked me to help them solve a problem combining data from multiple tables into a single result. What was interesting was that the solution used the three ways to combine data that we have recently covered, namely: JOINS, set operators, and subqueries. Given the comprehensive nature of this problem I thought it […]

What is a SQL Server Data Dictionary?

What is a Data Dictionary? In SQL Server the data dictionary is a set of database tables used to store information about a database’s definition. The dictionary contains information about database objects such as tables, indexes, columns, datatypes, and views. The data dictionary is used by SQL server to execute queries and is automatically updated […]

SQL Skills are Valuable, Learn why!

Are you serious about learning SQL? If so, then get started with free tools using my Guide Getting Started Using SQL Server. Is SQL a Valuable Skill… is it Sought Out? If I was to say that SQL is not a sought out skill would you believe me? Is it possible to safely say that since databases […]

Query Plans in SQL

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server. Let’s get started and learn about a query plan. What is a Query Plan? A query plan is a set of steps that […]

What is a Database Trigger?

What is a Database Trigger? A database trigger is special stored procedure that is run when specific actions occur within a database. Most triggers are defined to run when changes are made to a table’s data. Triggers can be defined to run instead of or after DML (Data Manipulation Language) actions such as INSERT, UPDATE, […]

Learn about Stored Procedures

What is a Stored Procedure? A stored procedure is a group of one or more database statements stored in the database’s data dictionary and called from either a remote program, another stored procedure, or the command line. Stored procedure are commonly called SPROCS, or SP’s. Stored procedure features and command syntax are specific to the […]

SQL Truncate and Delete

In SQL Server there are a couple a ways you can delete rows from a table. You can use the TRUNCATE and DELETE command. Though the end result of both commands is the same, there are very important differences you should know about. Whats the Difference between Truncate and Delete? The TRUNCATE command is like a DELETE […]

What is the difference between WHERE and HAVING clauses?

In this article learn when to use WHERE and HAVING. Both perform similar functions, but for different purposes! All the examples for this article are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server. How do Where and Having Differ? […]

What is the Difference between ORDER and GROUP BY?

Both the GROUP and ORDER BY clauses are used in organizing data. Find out which of the two is used to sort the data and which is used for counting and summing up. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started […]

How do I combine results from several SQL tables (hint: there are three ways)

Many times in a relational database the information you want to show in your query is in more than one table. This begs the question “How do you combine results from more than one table?” All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can […]

Use SQL Server to Sort Alphanumeric Values

Sort Alphanumeric Values with SQL Server Recently a reader asked me how to sort an alpha numeric field in SQL in natural order. Alphanumeric values are commonly found and don’t sort naturally using numeric methods. When we have a numeric fields we expect values to be sorted in ordinal order, for example: 1,2,5,7,10,11,15,20,21 However when […]

What is a Database NULL Value?

What is a Null Value? In databases a common issue is what value or placeholder do you use to represent a missing values. In SQL, this is solved with null. It is used to signify missing or unknown values. The keyword NULL is used to indicate these values. NULL really isn’t a specific value as much as it […]

What is a Relational Database View?

A database view is a searchable object in a database that is defined by a query. Though a view doesn’t store data, some refer to a views as “virtual tables,” you can query a view like you can a table. A view can combine data from two or more table, using joins, and also just […]

Ten Things a Junior DBA Should Learn

There is more to being a Junior DBA than knowing SQL. The DBA works at the intersection of the database, server, operations group, and developers. A DBA must understand concepts from all these facets of IT as well as be able to draw upon knowledge of their production environment to troubleshoot performance, hardware, and software […]

What are the Top Ten Databases?

Everyone has their favorite DBMS, is your favorite one of the top databases? Though some non-relational products have entered the top ten, the relational paradigm remains firmly entrenched in the database world. However specialized database types such document store databases and wide column stores are gaining in popularity to become top databases. Top of the […]

Database Indexes Explained

A database index allows a query to efficiently retrieve data from a database. Indexes are related to specific tables and consist of one or more keys. A table can have more than one index built from it. The keys are a fancy term for the values we want to look up in the index. The […]

What is a Database Table?

A relational database is made up of several components, of which the table is most significant. The database table is where all the data in a database is stored, and without tables, there would not be much use for relational databases. Overall Structure of a Database Table A database consists of one or more […]

Database Management System Main Parts

The main objective of a database management system is to store, retrieve, and process data. In order to do this in a safe and efficient manner, several major components work together to achieve this aim. They are: Databases Security Data Dictionary Storage Engine Query Processor

What are the Major Part of a SQL DB?

A SQL database is used to store and retrieve data. The database is housed in a database server and largely controlled by a database management system. All SQL databases, whether they MS SQL Server, MySQL, Oracle, or Progress have several components in common. They are: Tables Indexes Views Stored Procedures Triggers It is these various […]

SQL ACID Explained

What are the ACID Database Properties? The ACID database properties define SQL database key properties to ensure consistent, safe and robust database modification when saved. ACID is an acronym that helps to remember the fundamental principles of a transnational system. ACID stands for Atomic, Consistent, Isolation, and Durability. Here are some informal definitions: Atomic – In a transaction […]