SQL Server

3 Ways to use ALL in SQL Server

by

You might be familiar with the ALL option in SQL Server. Perhaps you’ve used it along with the UNION operator to include any duplicates that might be returned in the result set.

But did you know that ALL can also be used in two other contexts?

ALL can be used in the following three contexts:

  • As an argument to the SELECT clause.
  • As an argument to the UNION clause.
  • As a logical operator when comparing a scalar value with a single-column set of values.

Examples of each of these contexts follows.

Read more

SQL Server ALL Operator Explained

by

In SQL Server, the ALL operator can be used with a subquery to compare a scalar value with a single-column set of values returned by the subquery.

It’s also true that the SELECT clause and UNION operator both accept an ALL argument, although this usage has a different purpose (allows duplicates in the result set).

Below are examples of using the ALL operator with a subquery.

Read more

How to Find a Database’s ANSI_NULLS Setting in SQL Server (T-SQL)

by

SQL Server has an ANSI_NULLS setting that determines how NULL values are evaluated when compared to another value with the Equals (=) and Not Equal To (<>) comparison operators.

While it’s true that you can change the ANSI_NULLS setting at the session level (using SET ANSI_NULLS), each database also has its own ANSI_NULLS setting.

You can check your database to see whether its ANSI_NULLS setting is ON or OFF.

To do this with T-SQL, you can either use the sys.databases catalog view or the DATABASEPROPERTYEX() function.

Read more

Find Out Why an Email Failed to Send in SQL Server (T-SQL)

by

If you’re trying to send email using Database Mail in SQL Server, but it fails to send, you can check the sysmail_event_log view to see why it failed.

The sysmail_event_log view returns one row for each Windows or SQL Server message returned by the Database Mail system. By “message”, I don’t mean the actual mail message. I mean a message such as the error message that explains why the mail failed.

You can also use the sysmail_configure_sp stored procedure to determine what types of messages are logged.

Read more