t-sql

2 Ways to Check the Default Fill Factor in SQL Server

by

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.

Read more

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

by

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.

Read more

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

by

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.

Read more

Using ON UPDATE SET DEFAULT in SQL Server

by

In SQL Server, foreign keys are essential for maintaining referential integrity between tables. When creating foreign keys, we have a number options available to us when it comes to defining what should happen in the event that the related data in the parent table changes. One such option is ON UPDATE SET DEFAULT.

This article will explain what this option does, provide an example of its usage, and offer a practical scenario where this feature can be useful.

Read more

Fix “Cannot create index on view because the view is not schema bound” in SQL Server (Error 1939)

by

If you’re getting an error that reads something like “Cannot create index on view ‘MyView’ because the view is not schema bound” in SQL Server, it sounds like you’re trying to create an index on a view that’s not schema bound.

Indexes can only be created on schema bound views.

To fix this issue, define the view as schema bound before creating the index.

Read more

Fix “Computed column … cannot be persisted because the column is non-deterministic” in SQL Server (Error 4936)

by

If you’re getting an error that reads something like “Computed column ‘c3’ in table ‘t1’ cannot be persisted because the column is non-deterministic” in SQL Server, it appears that you’re trying to persist a computed column that’s nondeterministic.

A nondeterministic function or expression returns a different result for the same input. If a column uses such an expression, then it will be nondeterministic. A computed column must be deterministic.

Read more

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