4 Ways to Create a UNIQUE Constraint in SQL Server

by

A UNIQUE constraint is a rule that we can apply to one or more columns in a database table to ensure that the values in those columns are unique across all rows.

In SQL Server we have a few options when it comes to creating a UNIQUE constraint. But it’s usually done when we create the table or alter it. That is, we include the constraint code in the CREATE TABLE statement or the ALTER TABLE statement.

We can also create a UNIQUE index, which can be used in place of the previous methods, but can have the same effect.

Let’s check out several ways to create a UNIQUE constraint in SQL Server.

Read more

Fix “Cannot create index on view because it uses the aggregate COUNT. Use COUNT_BIG instead.” in SQL Server (Error 10138)

by

If you’re getting an error that reads something like “Cannot create index on view “demo.dbo.SalesSummary” because it uses the aggregate COUNT. Use COUNT_BIG instead” in SQL Server, it’s because you’re trying to create an index on a view that uses the COUNT() function.

Read more

Clustered vs Nonclustered Index: What’s the Difference?

by

Indexes can play a crucial role in optimizing SQL query performance. A well designed index can help a query perform much faster than it otherwise might. While there are many types of indexes, two common index types are clustered and nonclustered indexes.

Understanding the difference between clustered and nonclustered indexes can have a significant impact on our database design and query optimization strategies.

In this article, we’ll take a look at clustered and nonclustered indexes as implemented in SQL Server, explore their characteristics, and examine when to use each type.

Read more

What is an ER Diagram?

by

An entity relationship (ER) diagram (also known as an ERD for Entity-Relationship Diagram) is a visual representation of the relationships between entities within a system. ER Diagrams are often used when designing relational databases. An ERD serves as a blueprint for designing a database by depicting entities, their attributes, and the relationships between them.

ER diagrams help in conceptualizing the structure of a database and serve as a communication tool between database designers, developers, and stakeholders.

Read more

Understanding the CONTINUE Keyword in SQL Server WHILE Loops

by

WHILE loops are a fundamental construct in T-SQL (and in programming in general) for executing repetitive tasks. Within these loops, the CONTINUE keyword serves as a powerful control flow tool, allowing developers to skip iterations and optimise loop execution.

This article explores the usage and benefits of the CONTINUE keyword in SQL Server WHILE loops.

Read more

Fix “Computed column … in table … is not allowed to be used in another computed-column definition” (Error 1759)

by

If you’re getting an error that reads something like “Computed column ‘c2’ in table ‘t1’ is not allowed to be used in another computed-column definition” in SQL Server, it appears that you’re trying to create a computed column that uses another computed column.

We can’t use other computed columns in our computed column definitions.

Read more

Indexed Views in SQL Server: A Complete Guide

by

Indexed views can significantly enhance the performance of complex queries in SQL Server. However, they come with several prerequisites and considerations that need to be carefully understood before implementation.

In this article, I provide an overview of indexed views, discussing their utility, prerequisites, and best practices, along with performance considerations and a simple example.

Read more

What Does Deterministic Mean in SQL?

by

In SQL, a deterministic expression or function always returns the same result when given the same input. Conversely, a nondeterministic expression or function may return different results even with the same input due to factors like system time, randomization, or external data changes.

Below is a quick overview of the differences between deterministic and nondeterministic functions/expressions.

Read more