Reading Time: < 1 minute “If I have a query that I run frequently, why should I save it as a View in the database instead of just simply saving it as a .SQL script file and running the query when I need it?” There are two main benefits to saving your query as a Continue Reading
SQL Server DELETE vs. TRUNCATE: What’s the difference?
Reading Time: 2 minutes The 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
SQL Server COALESCE System Function: How It Works
Reading Time: 2 minutes The 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 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
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