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
Blog
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
Do you know this FACTOID about Foreign Key Constraints in SQL Server?
Reading Time: 3 minutes There is an interesting factoid you should know when it comes to working with foreign key constraints in SQL Server. Normally, foreign key constraints are linked to a Primary Key column in a parent table Take a look at the following Books and BookSales tables: CREATE TABLE Books ( BookID 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
How to make a column NOT NULL in SQL Server: Explained with Examples
Reading Time: 3 minutes You may find yourself needing the change the definition of a table or column from time-to-time while working as a database professional. One change you may need to make is changing a column from nullable to non nullable. It may have been decided that we actually don’t want NULL to appear Continue Reading
Difference between unique constraints and primary key constraints: Answered with examples
Reading Time: 4 minutes A primary key constraint and a unique constraint are both meant to enforce uniqueness in column(s). Since that’s true, it begs the question: What’s the difference? In this very brief tutorial, we’ll discuss the two main differences between primary key constraints and unique constraints. Difference # 1: Primary key constraints 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