The SQL COUNT() function is a handy tool for telling us how many rows would be returned in a query. We can pass a column name to the function or we can pass the asterisk (*) wildcard to indicate all columns.
If a column contains NULL values, we could get different results, depending on whether we use the column name or the asterisk (*).
SQL Server 2022 introduced the LEAST() function, which at first glance, may seem to do the same thing as the already existing MIN() function.
However, these are two separate functions with two separate purposes.
Continue readingIn SQL Server the VARP() function returns the statistical variance for the population for all values in the specified expression.
In SQL Server the STDEVP() function returns the statistical standard deviation for the population for all values in the specified expression.
In SQL Server the STDEV() function returns the statistical standard deviation of all values in a specified expression.
So we can use this function to get the standard deviation of all values in a column.
Continue readingSQL Server has a GROUPING_ID() function that returns the level of grouping based on its arguments.
We pass one or more column expressions to the function, and it computes the level of grouping. The column expressions must match the ones provided by the GROUP BY clause.
In SQL Server, the GROUPING() function is used to distinguish the null values that are returned by ROLLUP, CUBE, or GROUPING SETS from standard null values.
Basically it tells us whether a specified column expression in a GROUP BY list is aggregated or not.
The GROUPING() function returns either 1 or 0 (1 indicates that the column expression is aggregated and 0 indicates that it’s not).
SQL Server has a CHECKSUM_AGG() function that allows us to get the checksum of the values in a group. This can be useful for detecting changes in the values in a column.
In SQL Server, the APPROX_PERCENTILE_DISC() function calculates and returns an approximate percentile based on a discrete distribution of the column values.
We pass the desired percentile to the function when we call it.
Continue readingIn SQL Server, the APPROX_PERCENTILE_CONT() function calculates and returns an approximate percentile based on a continuous distribution of the column value. This is an interpolated value from the set of values in a group based on percentile value and sort specification.