Reading Time: 2 minutes It 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
Querying data
SQL Server WINDOW Clause: Explained!
Reading Time: 2 minutes The 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 minutes One 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 minutes The 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 minutes The 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 minutes SQL 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 minutes SQL 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 minutes It 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
SQL Server Covering Indexes: Explained with Examples
Reading Time: 6 minutes Indexes are extremely important when it comes to writing queries that return data in an efficient way. We not only want to make sure our queries return exactly the information we need, but we also want that information to be returned to us quickly. In this brief tutorial, we’ll discuss the Continue Reading
Don’t make this OUTER JOIN mistake!
Reading Time: 4 minutes The OUTER JOIN operations, such as LEFT JOIN and RIGHT JOIN, are very important to know when it comes to querying Microsoft SQL Server databases. There is one important factoid you should know if you are using an OUTER JOIN in combination with the IS NULL predicate. Let’s create a Continue Reading