Blog
Latest Posts
-
SQL CONVERT Function (Transact SQL)
Use the SQL CONVERT function to change an input expression from one data type into another specified data type. For example, a decimal value to an integer or a date value to a string. Description The SQL CONVERT function is one of the SQL functions to change the data type of a variable or an…
-
SQL REPLACE Function (Transact SQL)
SQL REPLACE function evaluates an input string for a specified substring pattern and replaces these substrings with another specified string value. Use it to substitute one set of characters for another with a character based value. Description The REPLACE function’s purpose is string manipulation. It replaces string patterns within a string with a new string.…
-
SQL DATEADD Function (Transact SQL)
Use SQL DATEADD to add days, weeks, months, or any timespan specified by datepart to a date to get another. The function returns the modified date. Description The function adds or deletes a specified time period from the date value. The period value can be a year, month, day, week, hour, minute, second, etc. A…
-
SQL DATEDIFF Function (Transact SQL)
The SQL DATEDIFF function calculates and returns the difference between two date values. The value returned is an integer. You can use DATEDIFF to calculate a wide variety of calendar calculation by varying the datepart parameter. Description Use SQL DATEDIFF to return the difference between the two dates based on a specified date part. The…
-
SQL CAST Function (Transact SQL)
The SQL CAST function converts an input expression of one data type into another data type. Description Data types of values often convert to meet different format requirements in SQL queries. Sometimes these are implicit conversions. For example, while multiplying a decimal value with an integer value, the SQL engine first converts the integer value…
-
Database Normalization – in Easy to Understand English
Database normalization is a process used to organize a database into tables and columns. There are three main forms: first normal form , second normal form, and third normal form. The main idea is each table should be about a specific topic and only supporting topics included. Take a spreadsheet containing the information as an…
-
SQL SUBSTRING Function (Transact SQL)
Description The SQL SUBSTRING function extracts a part of input expression or a ‘substring’ and returns it. The starting position and the substring’s length are passed as arguments of the SUBSTRING function. The SUBSTRING function is used to return characters from within another string. The general form of the SUBSTRING function is SUBSTRING(value, position, length)…
-
SQL STUFF Function (Transact SQL)
Description The SQL STUFF function deletes a specified number of characters from a character expression and replaces them with another substring. In other words, it inserts a string into another string. The new string value, position to insert the new string, and the length of the new string are passed as arguments in the function.…
-
SQL RIGHT Function (Transact SQL)
Description The SQL RIGHT function returns the specified number of rightmost characters of a given character expression. The rightmost characters are those at the “end” of the string. SQL RIGHT Usage Notes The character expression as the first argument of the RIGHT function can be a constant string, a variable string, or a database column.…
-
SQL LEFT Function (Transact SQL)
Description The SQL LEFT() function returns the specified number of characters starting from the left side of a given character expression. LEFT The LEFT function is used to return either the beginning or ending portion of a string. LEFT will return the beginning characters; whereas, RIGHT is used to return the ending characters. The general…
-
SQL UPDATE Statement (Transact SQL)
Use the SQL UPDATE statement to change data within a SQL Server data table’s columns. In this article, let’s explore using the UPDATE statement. We discuss some best practices, limitations, and wrap-up with several examples. This is the third article in a series of articles. You can start at the beginning by reading Introduction to…
-
SQL LEN (Transact SQL)
The SQL LEN function returns the number of characters within a string. Keep in mind the count of characters returned does not include training spaces. Description The SQL LEN function return the length of a string (number of characters). It does not include trailing spaces. The LEN function returns the number of characters in a…
-
SQL COUNT Function
The SQL COUNT function is an aggregate function used to count rows. Use it alone within a SELECT statement to return a count of all rows within a table, or with a GROUP BY to provide a count of rows within each group. Use COUNT(*) to count every record in the grouping or COUNT(expression) to count every record where expression’s result isn’t NULL. Use DISTINCT with…
-
Foreign Key vs Primary Key – What is the Difference?
This article will teach you the difference between a primary key and foreign key. This article will also teach you why both of these keys are important when it comes to the maintenance of a relational database structure. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks…
-
How to Use SQL Variables in Queries
Learning how to use SQL Variable in queries is a step in the right direction towards making your queries more flexible for you and others to use. Why “hardcode” when you can parameterize? In this video and the following article, we’ll take you step-by-step through the process of changing a query’s filter criteria into a…
-
Top 5 Stored Procedure Performance Tips You Can Use
Knowing how to optimize stored procedures is important, so stick around and I’ll show top five stored procedure performance tips you can use today. Hey, this is Kris from EssentialSQL. Let’s dig into some super easy tips you can use to speed up your stored procedures. Before we begin, let me point out that the…
-
Order of Execution in SQL Explained
Knowing the order of execution in SQL helps you better understand SQL’s “hair pulling” errors! As you start to work with SQL you find that some of the errors don’t make sense or you wonder why you’re able to use a column alias in the ORDER BY clause but not in a join condition. As…
-
Automatically Format Your SQL
Hey, welcome back to another SQL Minute. Today we’re going to learn how to code like a boss by delving into formatting your code. Now, some of you may have some rules you use to format code. Others may just type it out and wonder how they should format it. What I’m going to do…
-
Use SQL Comments to Code Like a Boss
In this video we’ll walk you though how to use comments within a query. We’ll go over block as well as inline comments. Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about SQL Server! Once you have watched the video check out the sample…
-
An Ultimate Guide to Write an SQL Query
The knowledge of SQL has always been in demand. Even if you don’t have a job as a data analyst or any other relevant position, knowing how to write basic SQL queries can be a useful addition to your resume. There are plenty of resources to learn about SQL, including our informative Essential SQL blog.…
-
SQL APPROX_COUNT_DISTINCT Function
Use the Approximate Count Distinct function, APPROX_COUNT_DISTINCT, to return an estimate distinct count of values withing rows. It is best suited for very large tables where performance matters over precision. The latest 2019 version of SQL Server introduced many functions to the system and enriched the database engine in order to make it work faster…
-
SQL Less Than or Equal To
The SQL Less Than Or Equal To comparison operator (<=) is used to compare two values. It returns TRUE if the first value is less than the second. If the second is less, it returns FALSE. You can also test for Less than by using <. Here are a couple of examples: 10 <=5 FALSE 5 <= 20 TRUE 10 <= 10 TRUE Here is…
-
SQL Greater Than or Equal To Comparison Operator
The SQL Greater Than or Equal To comparison operator (>=) is used to compare two values. It returns TRUE if the first value is greater than or equal to the second. If the second is greater, it returns FALSE. You can also test for greater than or equal to by using >=. Here are a couple of examples: 10 >= 5 TRUE …
-
SQL DECIMAL Data Type
Use the SQL Server DECIMAL data type to define columns that have fixed precision and scale. Unlike FLOAT, DECIMAL data type has fixed decimal places. Use DECIMAL data type and NUMERIC data type keywords interchangeably to define the type. DECIMAL(precision, scale) When defining, the DECIMAL data type provides both precision and scale. The precision defines the total number of decimal digits to store…
-
SQL Server FLOAT Data Type
Use the SQL Server FLOAT data type to define columns, variables, and parameters storing floating-point numbers. By floating point, we mean, numbers that have no fixed decimal place. Consider using SQL FLOAT when working with scientific values. Unlike DECIMAL, the FLOAT type handles a wide range of numbers: Here is the range expressed in scientific notation: Sign Max Value Minimum Value Negative – 1.79E+308 -2.23E-308 Positive 1.79E+308 …
-
VARCHAR Data Type
Use the SQL Server VARCHAR data type to define columns, variables, and parameters variable length characters. VARCHAR types are variable in length. They do not take up more memory than the characters stored. This differs from the CHAR type, which always occupies the full amount defined. The VARCHAR type stores up to 8000 characters with each character taking one byte. …
-
SQL INT Data Type
Use the SQL Server INT data type to define columns, variables, and parameters storing whole numbers. The INT data type stores a number in the range -2,147,483,648 to 2,147,483,647. If you need to store a larger integer value, consider using BIGINT. In addition to being used for numbers, INT is also used to define primary and foreign keys. Use it to create…
-
SQL DATETIME Data Type
Use the SQL Server DATETIME data type to define columns, variables, and parameters storing a date with the time of day. The DATETIME data type stores both the date and time. The allowed dates span from January 1, 1753 to December 31, 9999. The time component ranges from 00:00:00 through 23:59:59.997. Here is an example of a valid DATETIME value: 2017-11-23 11:30:34 Which represents November, 23rd 2017…
-
SQL BIT Data Type
Use the SQL BIT data type to define columns, variables, and parameters value of 1, 0, or NULL. Given their Yes/No nature, designer us the BIT type with flag and indicator columns: Converting BIT Values The string values TRUE and FALSE convert to BIT: TRUE is converted to 1 and FALSE to 0. Converting any non-zero value promotes BIT to 1. Examples Here is SQL…
-
NVARCHAR Data Type
Use the SQL Server NVARCHAR data type to define columns, variables, and parameters variable length characters. NVARCHAR types are variable in length. They take up more memory than the characters stored. This differs from the CHAR type, which always occupies the full amount defined. They store up to 4000 characters with each character taking two bytes and are is well suited for storing extended character…
-
SQL MIN Function
The SQL MIN function returns the smallest value within a table or group. Throughout this section we’ll use the HumanResource.Employee table for our examples: Using the data above, MIN calculates the smallest SickLeaveHours amount for the entire table: When used with GROUP BY, MIN returns the largest value within a group. Here is a similar query showing the minimum SickLeaveHours by JobTitle: Additional SQL MIN Resources …
-
SQL MAX Function
The SQL MAX function returns the largest value within a table or group. In this section, we’ll use the HumanResource.Employee table for our examples: Using the data above, MAX calculates the largest SickLeaveHours amount for the entire table: When used with GROUP BY, MAX returns the largest value within a group. You can see this in this query showing the maximum SickLeaveHours by JobTitle: Additional SQL MAX Resources To learn…
-
SQL AVG Function
The SQL AVG function returns the average value within a table or group. In this section, we’ll use the HumanResource.Employee table for our examples: Using sample data above, AVG calculates the average SickLeaveHours amount for the entire table: When used with GROUP BY, AVG returns the average value within a group. You can see this in this query showing the maximum SickLeaveHours by JobTitle: Additional SQL AVG Resources To learn more,…
-
SQL Less Than Comparison Operator
The SQL Less Than comparison operator (<) is used to compare two values. It returns TRUE if the first value is less than the second. If the second is less, it returns FALSE. You can also test for Less than or equal to by using <=. Here are a couple of examples: 10 < 5 FALSE 5 < 20 TRUE 10 < 10 FALSE Here…
-
SQL Data Types
In SQL the columns of a table are defined to store a specific kind of value such as numbers, dates, or text; these are called data types. With over thirty types of SQL server data types to choose from, data types can seem intimidating, but in reality, there are just a few commonly used in…
-
SQL Greater Than Comparison Operator
The SQL Greater Than comparison operator (>) is used to compare two values. It returns TRUE if the first value is greater than the second. If the second is greater, it returns FALSE. You can also test for greater than or equal to by using >=. Here are a couple of examples: 10 > 5 TRUE 5 > 20 FALSE 10 >…
-
SQL SELECT AS
Rename Columns with SQL SELECT AS You can use a form of SQL SELECT AS to rename columns in your query results. So far you’ve seen where queries results return results named after the table columns. This is fine for most cases, but once you start working with expressions, you’ll see this doesn’t work well.…
-
Window Functions in SQL – What are they?
The purpose of this article is to introduce you to SQL window functions and some of the high-level concepts. In SQL, a window function refers to a function, such as sum or average, which acts upon a result set’s rows relative to the current row. There are a lot of details to cover, but you’ll…
-
SQL COALESCE Function and NULL
Knowing when to use the SQL COALESCE function is a lifesaver when you’re dealing with NULL. As you know, NULL is a tricky concept, and it seem what ever NULL “touches” in an expression, it renders the result NULL. So, when you’re dealing with NULL, how can you break out of the cycle? That is,…
-
SQL Server Hierarchical Query using the hierarchyid type
In this SQL minute we’ll look at how to create a SQL server hierarchical query using the hierarchyid data type. This is a two-part article. In part one you were introduced to the hierarchyid type. In this article we dig deeper into the hierarchyid functions. If you’re studying for the 70-761 exam, you’ll find there aren’t…
-
Use hierarchyid to query Hierarchical data
In this SQL minute we’ll look at how you can use hierarchyid to query hierarchy data. This is a two-part article. In this article we look at how to do one query to get portion of the hierarchy. Next week, we will look to see how to query the entire tree. For this problem we…
-
Can I have a CASE Statement in the WHERE Clause?
I’m commonly asked whether whether I can have a CASE Statement in the WHERE Clause. There are a number of examples using the CASE WHEN construct in SQL, such as the SELECT columns or in ORDER BY clauses, but we tend to forget CASE can be used wherever an expression is expected. Where Can I…
-
Basic SQL: A Business User’s Guide to Writing Queries
Most people are familiar enough with basic SQL to be able to provide a simple explanation of it. Unfortunately, few know how to use the language or apply it to their business goals. Many see SQL as an advanced language intended for use by data professionals and programmers. In reality, it’s a fairly simple language…
-
SQL Pivot Table – Learn to Create in Six Steps
In this episode of SQL Minute I want to share with you how to create a pivot or cross tab chart. I’ll show you the steps using an example from the adventure works database. I get so many questions on how to create these! Once you get the steps down, you’ll see they are not…
-
Left Join versus Right Join Comparison
I recently put together a lesson on the difference between left join versus right join operators. Outer joins are a great way to match tables, while allowing still including rows from one table that don’t necessarily match to another table. The good news is that when it comes to right outer joins or, its twin,…
-
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…
-
What is a Non-Equi Join in SQL and What is its Purpose?
A non-equi join can be used to solve some interesting query problems. You can use a non-equi join to check for duplicate value or when you need to compare one value in a table falls within a range of values within another. You can learn more about this this video, it’s transcript follows: What is…
-
How to Make a Join Easier to Read using Table Aliases
I recently put together a lesson on table aliases and multi-table joins. You’ll find that as you write more complicated joins, that the SQL can become harder to read, as you’ll need to qualify column with table names to avoid ambiguity. To make you SQL more compact and more readable you can create table aliases.…
-
How to use the Choose Function with Select
In this video we’ll walk you though how to use the CHOOSE function with SELECT. You’ll see how the function works and then a practical example using some sample data. Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about SQL Server! Once you have…
-
Common Data Types used in SQL Server
In this video we’ll walk you though the common data types used in SQL Server; you’ll see examples of each type’s values, and how to define them. Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about SQL Server! Once you have watched the video…
