functions – Page 96

How SQLite Max() Works

May 28, 2020May 6, 2020 by Ian

The SQLite max() function returns the maximum value from all values in a group.

The maximum value is the value that would appear last in a query that uses an ORDER BY clause on the same column.

How SQLite Avg() Works

May 28, 2020May 6, 2020 by Ian

The SQLite avg() function returns the average value of all non-NULL values within a group.

It accepts one argument, which is the value or group of values.

Combine SQLite Count() with GROUP BY to Add a “Count” Column to your Result Set

May 28, 2020May 6, 2020 by Ian

If you need to add a “count” column to the result set of a database query when using SQLite, you can use the count() function to provide the count, and the GROUP BY clause to specify the column for which to group the results.

How SQLite Count() Works

May 28, 2020May 6, 2020 by Ian

The SQLite count() function can be used to return the number of rows in a result set.

It can also be used to return the number of times a given column is not NULL in the result set.

It can be used in two ways. If you pass in the asterisk (*) wildcard character, it will return the total number of rows in the group. If you provide the name of a column, it will return the number of times that column is not NULL.

Valid Format String Substitutions for the SQLite Strftime() Function

January 28, 2021April 29, 2020 by Ian

The table below contains the valid format string substitutions that you can use with the strftime() function in SQLite.

How to make RAND() Deterministic in SQL Server

September 10, 2024February 29, 2020 by Ian

The RAND() function in SQL Server returns a pseudo-random float value from 0 through 1, exclusive.

This function can be deterministic or nondeterministic, depending on how it’s invoked.

What is @@SERVICENAME in SQL Server?

February 14, 2020 by Ian

In SQL Server, the @@SERVICENAME configuration function returns the name of the registry key under which SQL Server is running.

No argument is required. You can simply use it in a SELECT statement to return the registry key’s name.

Note that SQL Server runs as a service named MSSQLServer. The @@SERVICENAME function returns MSSQLSERVER if the current instance is the default instance. It returns the instance name if the current instance is a named instance.

Introduction to User-Defined Functions in SQL Server

February 14, 2020February 12, 2020 by Ian

SQL Server ships with a collection of built-in functions that enable you to perform a variety of operations. Each built-in function serves a specific purpose, and can’t be modified. If a function serves your purpose, you can go ahead and use it.

But what if you can’t find a function that serves your purpose?

Then it’s time to write your own user-defined function.

2 Ways to List All Table-Valued Functions in a SQL Server Database

February 11, 2020 by Ian

This article presents two ways to return a list of table-valued functions in a SQL Server database.

Remove SCHEMABINDING from a User-Defined Function in SQL Server

September 21, 2024February 10, 2020 by Ian

The purpose of schema binding a user-defined function (UDF) is to ensure that the base objects referenced in the UDF cannot be modified in a way that would affect the function’s definition.

This is fine, as long as you don’t need to make any changes to the underlying objects. But what if you do need to make changes?