Understanding Common Table Expressions (CTEs): A Beginner’s Guide

Common table expressions (CTEs) are a feature that we can use in SQL to help simplify complex queries and enhance readability. They can help with things like code readability, performance, recursive queries, and more.

This article explores what CTEs are, their syntax, types, use cases, and best practices, along with examples to help illustrate their usage.

Continue reading

Fix “Cannot create index on view because it contains the DISTINCT keyword” in SQL Server (Error 10100)

If you’re getting an error that reads “Cannot create index on view “demo.dbo.vDistinctPrice” because it contains the DISTINCT keyword…” etc etc in SQL Server, it appears that you’re trying to create an index on a view that contains the DISTINCT keyword.

As the error message alludes to, this is not allowed.

Continue reading

Understanding SQL Data Types: A Comprehensive Guide

Structured Query Language (SQL) is the backbone of relational database management systems (RDBMSs), enabling users to query, manipulate, and define data. One of the most fundamental concepts in SQL, and one that all SQL developers should understand, is the data type.

Whenever we create a column in SQL, we must define its data type. Similarly, when we create a variable, we define its data type.

So, why is the data type so important? Let’s find out.

Continue reading

2 Ways to Check the Default Fill Factor in SQL Server

Fillfactor is an option that allows us to fine tune index data storage and performance in SQL Server. It determines the percentage of space on each leaf-level page to be filled with data, therefore reserving the remainder on each page as free space for future growth.

The default fillfactor can be set as a configuration option. SQL Server sets this value to 0, and so this is what’s used when we create an index without specifying a fillfactor. However, it’s possible to change this value, and so at some stage we might want to check to see what the current value is.

Below are two ways to check the fill factor configuration option.

Continue reading

Fixing Error 258 “Cannot call methods on nvarchar(max)” When a Subquery Uses FOR XML in SQL Server

If you’re running a subquery that uses FOR XML to produce XML in SQL Server, but you’re getting error 258, which reads “Cannot call methods on nvarchar(max)” it might be that you’re trying to query the XML in the outer query, but your inner query isn’t outputting the XML as an actual xml type.

The FOR XML result must be an xml type in order to process the result on the server.

To fix this issue, make sure the subquery outputs the result using the xml data type.

Continue reading

What is a Junction Table in SQL?

A junction table, also known as an associative table or a bridge table, is a table used to manage many-to-many relationships between two other tables in a relational database.

In SQL, many-to-many relationships cannot be directly implemented using just two tables because each table’s foreign key can only relate to one other table’s primary key. Instead, a junction table is created to “link” the two tables together.

Continue reading

Fix Error “The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions…” in SQL Server (Error 1033)

If you’re getting an error that reads “The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified” in SQL Server, it appears that you’re using the ORDER BY clause in a place that it must also be accompanied by a TOP, OFFSET or FOR XML clause, but you’re not including one of those clauses.

To fix this issue, be sure to include one of those clauses in your query if you need to use the ORDER BY clause.

Continue reading