Reading Time: 2 minutesThe DELETE and TRUNCATE statements are both meant to delete content from a table. Since that’s true, it begs the question: What’s the difference? In this very brief tutorial, we’ll walk through the main differences between DELETE and TRUNCATE and explain when you should choose to use one over the Continue Reading
Querying data
SQL Server COALESCE System Function: How It Works
Reading Time: 2 minutesThe COALESCE system function offers a great way to check expressions for NULL. The syntax for COALESCE is very simple. It looks like this: COALESCE(<expr1>,<expr2>,<expr3>,<expr4>,….<exprN>) As you can see, all we do is outline a comma-separated list of expressions to the function. SQL Server will evaluate each expression, from left Continue Reading
Can we create indexes in table variables? YES WE CAN!
Reading Time: 2 minutesIt is absolutely possible to create indexes within a table variable in Microsoft SQL Server. The key is to add the indexes as part of the table variable definition Take a look at this regular table definition (which does not have any indexes placed on it yet): CREATE TABLE Books Continue Reading
SQL Server WINDOW Clause: Explained!
Reading Time: 2 minutesThe SQL Server WINDOW clause is a new feature introduced in Microsoft SQL Server 2022. It can make the syntax of a window query much more readable. In this brief tutorial, we’ll walk through how the WINDOW clause works and see how it can be very helpful. Start with a Continue Reading
How to calculate age in SQL: Use THIS simple script!
Reading Time: 7 minutesOne interesting task you may be given as a data professional is to calculate a person’s age based on their date of birth. In this brief tutorial, we’ll discuss a simple script you can use to derive the number of years, months, and days that a person has aged since Continue Reading
SQL Server ISNULL: Explained with Examples
Reading Time: 3 minutesThe SQL Server ISNULL system function is one of the most useful functions you will use in your work as a data professional. The ISNULL function is meant to help you deal with NULLs in SQL Server. It’s meant to be used as an error handling tool of sorts. The Continue Reading
SQL Server ANSI_NULLS Setting: What does it do?
Reading Time: 2 minutesThe SQL Server ANSI_NULLS setting can change the outcome of comparisons to NULL in your database applications. Consider the following table:Â What do you think will be returned by the following query?: SELECT * FROM Employees WHERE MiddleName = NULL Here’s the result: Nothing at all! By default, anything compared Continue Reading
SQL Server Week Number: Use this ONE Simple Function!
Reading Time: 2 minutesSQL Server makes it very easy to gather the week number of a specific date. Maybe you need to know what week of the year a certain date falls within. The DATEPART system function is what we can use to gather that information. Using the DATEPART system function The date Continue Reading
SQL Server QUALIFY: Doesn’t Exist, Do THIS Instead
Reading Time: 3 minutesSQL Server has many useful window functions available to us that can make the task of gathering aggregate/ranking/offset data very easy. One tool that you might have heard of is the QUALIFY clause. This tool can be used to filter the result of a window function. The problem is that Continue Reading
DELETE Statement with a JOIN: How it’s done
Reading Time: 4 minutesIt is very easy to write a DELETE statement with a JOIN. In this very brief tutorial, we’ll walk through how it’s done. We’ll go over just these two topics: Write it as a SELECT statement first Convert your query to a DELETE statement Everything in this tutorial can also Continue Reading