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
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
Default Values in Stored Procedure Parameters: Do you know this rule?
Reading Time: 4 minutes It is very easy to set up a default value for an input parameter in a stored procedure. When we establish a default value for an input parameter, it becomes optional to provide a value for that parameter when calling the procedure. In that event, the value used for the Continue Reading
SQL Server WAITFOR DELAY: Explained
Reading Time: < 1 minute The WAITFOR DELAY system function is the best tool we have in Microsoft SQL Server to introduce a pause in our SQL code. The syntax is extremely simple. We just run the WAITFOR DELAY statement on its own, like this: WAITFOR DELAY ’00:00:10′ You can see how in single quotes, Continue Reading