Blog
Latest Posts
-
Common Table Expressions – The Ultimate Guide
The Common Table Expressions or CTE’s for short are used within SQL Server to simplify complex joins and subqueries, and to provide a means to query hierarchical data such as an organizational chart. In this article, we’ll introduce you to common table expressions, the two types of the SQL CTEs, and their uses. In addition,…
-
Data Modeling Principles in Action
In this puzzle, we’re going to learn how to do some basic data modeling. Many of you have expressed an interest in learning more about data modeling and database design. I figure we could start with a simple case study and let it evolve from there. Solving puzzles is a great way to learn SQL. …
-
Primary Key vs Unique Key – What is the Difference?
Primary Key vs Unique Key. It really not about one or the other. You actually may need both a primary key and unique key in your table. In this article we learn the difference between a primary key and unique key, and why both are important to maintaining a relational database structure. All the examples…
-
What is a SQL Server Data Dictionary?
What is a Data Dictionary? The SQL Server data dictionary stores information about a database’s definition. The dictionary contains information about database objects such as tables, indexes, columns, datatypes, and views. The SQL Server DBMS uses the data dictionary to execute queries. The data dictionary is kept up-to-date as the database objects are changed. All…
-
Is it possible to use Joins, Subqueries, and Unions on Multiple Tables?
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…
-
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…
-
What Is the Difference Between a Join and a UNION?
Use joins and UNIONS 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 are sure to practice using the examples! All the examples for this lesson are based on Microsoft SQL Server…
-
SQL Union, Intersect, and Except – The Ultimate Guide
The SQL UNION, SQL INTERSECT, and SQL EXCEPT clauses are used to combine or exclude like rows from two or more tables. They are useful when you need to combine the results from separate queries into one single result. They differ from a join in that entire rows are matched and, as a result, included…
-
SQL ANY and ALL Operators
SQL ANY and ALL keywords are used in subquery comparisons to compare a set of value against all values in the result or any one value in the set. Comparison operators such as greater than, less than, equal, and not equal can be modified in interesting ways to enhance comparisons done in conjunction with WHERE…
-
SQL IN Operator
The SQL In operator compare a column to a list. In this article we’ll see how to use In and NOT IN with list generated using subqueries. SQL IN Operator and NOT IN Operator Review The SQL IN operator is considered a membership type. The membership type allows you to conduct multiple match tests compactly…
-
Derived Tables
A derived table is a subquery in the from clause. It returns a table as its result. Unlike other subqueries you name them. In this articles I’ll show you to use derived tables, a how to use their special abilities to work around SQL limitations. Things you can do with Derived Table Subqueries. There are…
-
Correlated Subqueries in SQL
Correlated subqueries are used to tie an inner query’s criteria to values within the outer query. They are powerful technique to avoid “hard coding” values. In this article look at a couple example and compare a correlated sub query to a join. All the examples for this lesson are based on Microsoft SQL Server Management…
-
SQL Bill of Materials Example
In this puzzle we’ll explore a SQL bill of materials example. Companies use a BOM (Bill of Materials) to itemize the components and sub assemblies used to construct their products. Solving puzzles is a great way to learn SQL. Nothing beats practicing what you’ve learned. Once you have figured out the puzzle, post you answer…
-
Subqueries versus Joins
Use both Joins and subqueries to query data from different tables. Though they may even share the same query plan, 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 for this…
-
SQL Subqueries – The Ultimate Guide
SQL subqueries make it possible for you to write queries that are more dynamic, and data driven. Think of them as query within another query. In this article I’ll introduce you to subqueries and some of their high-level concepts. I will show you how to write a SQL subquery as part of the SELECT statement. Subqueries return two types of results: …
-
What is the Difference between 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. Let’s explore their differences and learn What is the TRUNCATE command in SQL.…
-
SQL DELETE Statement (Transact SQL)
Use the DELETE statement to remove rows from a SQL Server data table. In this article we’ll explore how to use the DELETE statement. We discuss some best practices, limitations, and wrap-up with several examples. This is the fourth article in a series of articles. You can start at the beginning by reading Introduction to SQL…
-
SQL ACID Database Properties Explained
What are the ACID Database Properties? The ACID 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 – Transaction acting on…
-
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…
-
SQL MERGE Statement (Transact SQL)
Introduction to the MERGE Statement and SQL Server Data Modification The MERGE statement is used to make changes in one table based on values matched from anther. It can be used to combine insert, update, and delete operations into one statement. In this article, we’ll explore how to use the MERGE statement. We discuss some…
-
Data Manipulation with SQL – The Ultimate Guide
In this article, we’ll discover data manipulation statements used to modify data in the SQL server using the INSERT, UPDATE, DELETE, and MERGE statements. These are also called DML (Data Manipulation Language) statements. In this article you’ll learn to use these data modification statements to add, modify, and remove data. Finally, we’ll wrap up the…
-
TSQL Variables in Stored Procedures
I recently put together a lesson on how to work with TSQL variables. When you create stored procedures, you can encapsulate logic securely, and part of this process is using TSQL variables to temporarily store and manipulate values. After watching this video you’ll be able to declare TSQL variables and understand how they are used…
-
User Defined Function versus Stored Procedure
We are going to show you the difference between a stored procedure and a user defined function in SQL. This way you can get on the right path in starting your scripting skills. This article is based on my video What’s the Difference Between a Stored Procedure and User Defined Function? Is there a difference…
-
Debugging Stored Procedures in SQL Server
I recently put together a lesson on debugging stored procedures. When you debug stored procedures, you’re able to run their code line by line, to easily troubleshoot logic issues. If you’re not familiar with debugging stored procedures you’ll want to watch this introductory video; as it will get you acquainted with some of the main…
-
Stored Procedures: the ultimate guide.
What is a Stored Procedure? A stored procedure is a group of one or more database statements housed in the database’s data dictionary and called from either a remote program, another stored procedure, or the command line. We commonly call them SPROCS, or SP’s. Stored procedure features and command syntax are specific to the database…
-
WHILE Statement – Using in SQL Server
After reading this article you will understand the basics of using the WHILE statement to write a loop within a stored procedure. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters. Start learning SQL today using these free tools with my guide Getting Started…
-
What is a Database Cursor?
After reading this article you will understand what a database cursor is, see an example, and understand when to use them in a stored procedure. 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…
-
Dynamic SQL – Build using Stored Procedures
After you have read this article, you will get to learn the basics of a dynamic SQL, how to build statements based on variable values, and how to execute constructed statements using sp_executesql and EXECUTE() from within a stored procedure. All examples found in this lesson are based on the Microsoft SQL Server Management Studio…
-
Use IF ELSE Statements to Program a Stored Procedure
After reading this article you will understand the basics of programming a stored procedure using IF ELSE statements; most importantly how keywords, such as BEGIN, END, IF and ELSE affect the order of execution within your stored procedures. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample…
-
SQL LOWER Function (Transact SQL)
The SQL LOWER function converts the input character data to a lower case. Description The LOWER function takes character expression as an input and converts it into a lower case text. It helps to perform a case-insensitive search in the database. The SQL search is case-sensitive. The value Lower and lower are different. The Lower…
-
SQL YEAR Function (Transact SQL)
The SQL YEAR function evaluates the input date and returns the year part as an integer.Description The YEAR function works the same as the DATEPART (year, date) and returns the year part of the specified date. SQL YEAR Usage Notes The only argument for the YEAR function is the date. It can be a database…
-
SQL CEILING Function (Transact SQL)
The SQL CEILING function returns the smallest integer value greater than or equal to the input value. Description The CEILING function evaluates the right side of the decimal value and returns an integer value that is least greater than or equal to the input value. CEILING is another SQL function for approximating numerical values like…
-
Using NULLIF with Missing Values
Using NULLIF to compare missing values to NULL is handy. It become especially useful when you find yourself working with a column contains both empty and NULL text. NULLIF provide handy and compact way to compare both types of data to NULL itself. If you like what you are seeing, then why not get some…
-
Replace Missing Values in your Queries using ISNULL
When working with NULL, you’ll often find yourself wanting to replace these “missing” values, with something else. Just use ISNULL to do so. In this video I’ll walk through some of the example and you’ll see it is pretty easy to use. If you like what you are seeing, then why not get some super…
-
SQL UPPER Function (Transact SQL)
The SQL UPPER function converts the input character expression to an upper case and returns it. Description The UPPER function reads the input expression and converts lowercase characters to uppercase. SQL UPPER Function Usage Notes The character expression is the only argument for the UPPER function. It is the text to be converted to an…
-
SQL FLOOR Function (Transact SQL)
The SQL FLOOR function returns the largest integer that is smaller or equal to the input expression. Description The FLOOR function helps for approximating numeric values. It rounds the numeric expression to the value that is the largest number less than or equal to the input number. SQL FLOOR Usage Notes The FLOOR function takes…
-
How to use C# with SQL
In this post we’ll look at how to use C# with SQL. In this tutorial, we will discuss the process for creating a very simple C# console application that connects to a SQL Server database. You will learn the most basic steps needed to use C# with SQL. You will see how we can gather…
-
SQL PATINDEX Function (Transact SQL)
The SQL PATINDEX function searches an input expression for a given pattern. It returns the starting index of the first occurrence of the pattern found in the input expression. Description The PATINDEX function is very similar to the LIKE function in SQL. However, it returns the starting position of the first occurrence of the pattern…
-
SQL ISNUMERIC Function (Transact SQL)
The SQL ISNUMERIC function checks the input expression for a valid numeric data type. It returns 1 if the input value is numeric and 0 if it is not. Description The ISNUMERIC function in SQL tells if the input expression can be converted to a numeric value or not. It returns an integer as output…
-
SQL RAND Function (Transact SQL)
The SQL RAND function generates a random number between the range of 0 and 1. The number can be greater than equal to zero but less than one. Description The RAND function gives a completely random number if no seed value is specified. The RAND function produces a repetitive random number for all the successive…
-
SQL DATEPART Function (Transact SQL)
The SQL DATEPART function returns the specified part of the input date. Description The SQL DATEPART function returns an integer value that indicates the part of the date specified by the user. The interval to be retrieved can be a date, year, hour, minute, etc. Returns an integer which represents the specified part of a…
-
SQL @@VERSION (Transact SQL)
The SQL @@VERSION global variable returns the version of the currently installed SQL server. It includes the SQL server’s system and the build information. Description All the details required for upgrade and installations of new features in SQL Server are provided by the @@VERSION function. The information includes SQL Server version, processor architecture, SQL Server…
-
SQL RTRIM Function (Transact SQL)
The SQL RTRIM function trims all the trailing spaces from the right side of the input character expression. Description The RTRIM function returns a character expression after removing all the blank spaces from the right side. SQL RTRIM Usage Notes The only argument to the RTRIM function is the character expression that is to be…
-
SQL LTRIM Function (Transact SQL)
The SQL LTRIM function removes the leading blank spaces from the input character expression. Description The LTRIM function returns a character expression after removing all the blank spaces from the left side of the input expression. SQL LTRIM Usage Notes The input expression should be explicitly convertible to a varchar value. It can be a…
-
SQL CHARINDEX Function (Transact SQL)
The SQL CHARINDEX function returns the starting position of a character expression in another character expression. Description The CHARINDEX function helps with string data manipulation like some other SQL functions of SUBSTRING, REPLACE, CONCAT, etc. The CHARINDEX function finds the beginning of a character expression in a given character expression. The result of the CHARINDEX…
-
SQL GETDATE Function (Transact SQL)
The SQL GETDATE function returns the current timestamp of the database as a datetime value. Description The current timestamp derives from the operating system on which the SQL server is running. The GetDATE function returns the timestamp in the format YYYY-MM-DD hh:mm:ss:mmm. SQL GETDATE Usage Notes The return data type of the GETDATE function is…
-
SQL ROUND Function (Transact SQL)
The SQL ROUND function rounds a numeric value up to a specified number of decimal places or precision. Description The SQL ROUND function helps to handle numeric data according to the requirements. Some other SQL functions for this purpose are CEILING, FLOOR, etc. If we do not want to display complete numeric values, we can…
-
SQL NULLIF Function (Transact SQL)
Use the SQL NULLIF function to compare two input expressions and returns NULL if both are equal. Description Like the COALESCE function, the NULLIF function is a control flow function in SQL. It checks if the specified expressions are equal. It is a simpler version of a CASE expression with the condition comparing two input…
-
How To Find Duplicates in SQL using Window Functions
If you want to find duplicate rows in SQL, you can go two routes. The first is to use the GROUP BY and HAVING to identify “duplicate” groups of data, or you can use a easy to understand window function to not only identify the duplicates but also its primary key. In this video I’ll…
-
SQL ISNULL Function (Transact SQL)
Use the SQL ISNULL function to evaluate the input expression for a NULL value. If it is NULL, then ISNULL replaces it with another specified value. Description The ISNULL function helps to deal with the NULL values in database records. If we want to view a column with NULL values, we can handle them using…
