A3-Implement functions and aggregate data

Question 1

What is SARGable?

Answer 1

Search ARGument, and it means that the predicate can be executed using an index seek

Question 2

What can prevent a predicate from becoming SARGable in general?

Answer 2

The most general form, from a SARGable perspective, for a predicate is: to, on one side have a column, and on the other side not an expression on a column.

So, In short it’s mostly about having no functions on the column and no implicit conversions of the column.

Question 3

What can prevent an predicate becoming SARGable concretly?

Answer 3

• Any function on a column will prevent an index seek from happening, even if the function would not change the column’s value or the way the operator is applied.
• The other requirement for a predicate to be SARGable, for SQL Server at least, is that the column and expression are of the same data type or, if the data types differ, such that the expression will be implicitly converted to the data type of the column.
  
  • Exeptions: Comparing a DATE column to a DATETIME value would normally implicitly convert the column to DATETIME (more precise data type), but that doesn’t cause index scans. In general though conversions should be explicit not left up to SQL SERVER;
• The majority are fine.
  
  • EXIST and IN with a subquery are treated like joins, which may or may not use indexes depending on the join type chosen.
  • LIKE is a slight special case. Predicates with LIKE are only SARGable if the wildcard is not at the start of the string.

Question 4

SARGable expressions and performance?

Answer 4

SARG is an acronym of sorts for “search argument”. In plain english, a SARGable query is where you can use an index seek, because you’re looking for a single value or range of values in the index. The opposite of a SARGable query is when you perform some type of function or calculation on each row in the table, and match the result of this calculation to your search criteria. When you can’t use an index to look for a single value or range, you’re performing a table scan or index scan, which means you’re traversing the entire table or index, which is a costly operation if there are a lot of rows.

Of course, the entire performance benefit from SARGable expressions comes from having an index that covers the columns you’re querying in the first place. Without such an index, you will automatically end up with a table scan or index scan anyway.

Remarks:

• Any function used as a predicate will prevent an index seek from happening.
• Using an index is much more efficient than not using an index because SQL Server is not required to access the underlying table.
• Using Contains instead of like will improve performance for string indexing

Question 5

What is the syntax of DATEFROMPARTS​() function

Answer 5

DATEFROMPARTS(year, month, day)
Arguments:

year-An integer expression that specifies a year.

month-An integer expression that specifies a month, from 1 to 12.

day-An integer expression that specifies a day.

Remarks:

For invalid arguments, DATEFROMPARTS will raise an error. DATEFROMPARTS returns null if at least one required argument has a null value.

Question 6

What is the syntax of DATEADD()

Answer 6

DATEADD (datepart , number , date )

datepart-The part of date to which DATEADD adds an integer number.

number-An expression that can resolve to an int that DATEADD adds to a datepart of date.

date-An expression that can resolve to one of the following values:date; datetime; datetimeoffset;datetime2; smalldatetime; time

Question 7

What is the syntax, arguments and meaning of EOMONTH?

Answer 7

DEF: This function returns the last day of the month containing a specified date, with an optional offset.;

SYNTAX: EOMONTH( start_date [, month_to_add] );

Arguments:

start_date-A date expression that specifies the date for which to return the last day of the month.

month_to_add-An optional integer expression that specifies the number of months to add to start_date.

If the month_to_add argument has a value, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date. If this addition overflows the valid range of dates, then EOMONTH will raise an error.

Question 8

What is datetime, datetime2, datetimeoffset?

Answer 8

datetime2-Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision;

• When using variables even though they can be specified at datetime for example SQL SERVER will implicitly convert it to datetime2

datetime-Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock

• datetime values are rounded to increments of .000(-1,+1), .003(-1,+1), or .007(-2,+1) seconds, as shown in the following table.

datetimeoffset:

• Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

Question 9

What is smalldatetime?

Answer 9

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

Important it is recommended:

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

Question 10

What are aggregate functions?

Answer 10

An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT(*), aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement.

All aggregate functions are deterministic. In other words, aggregate functions return the same value each time that they are called, when called with a specific set of input values.

The OVER clause may follow all aggregate functions, except the STRING_AGG, GROUPING or GROUPING_ID functions.

Use aggregate functions as expressions only in the following situations:

• The select list of a SELECT statement (either a subquery or an outer query).
• A HAVING clause.

Question 11

AVG-DEF, syntax, remarks

Answer 11

DEF: This function returns the average of the values in a group. It ignores null values.

Syntax: AVG ( [ALL | DISTINCT] expression ) [OVER ( [ partition_by_clause] order_by_clause ) ]

Remarks:

• If the data type of expression is an alias data type, the return type is also of the alias data type. However, if the base data type of the alias data type is promoted, for example from tinyint to int, the return value will take the promoted data type, and not the alias data type.
• AVG () computes the average of a set of values by dividing the sum of those values by the count of nonnull values. If the sum exceeds the maximum value for the data type of the return value, AVG() will return an error.
• AVG is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses.

Question 12

MIN-DEF, syntax, remarks

Answer 12

DEF:Returns the minimum value in the expression. May be followed by the OVER clause.– Aggregation Function Syntax: MIN ( [ALL | DISTINCT] expression ) –

Analytic Function Syntax: MIN ( [ALL] expression ) OVER ( [] [] )

Remarks:

• MIN ignores any null values.
• With character data columns, MIN finds the value that is lowest in the sort sequence.
• MIN is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses.

Question 13

COUNT_BIG-DEF, syntax, remarks

Answer 13

DEF: This function returns the number of items found in a group. COUNT_BIG operates like the COUNT function. These functions differ only in the data types of their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

– Aggregation Function Syntax: COUNT_BIG ( { [[ ALL | DISTINCT] expression ] | * } )​

– Analytic Function Syntax:COUNT_BIG ( [ALL] { expression | * } ) OVER ( [] )​

Remarks:

COUNT_BIG(*) returns the number of items in a group. This includes NULL values and duplicates.

COUNT_BIG (ALL expression) evaluates expression for each row in a group, and returns the number of nonnull values.

COUNT_BIG (DISTINCT expression) evaluates expression for each row in a group, and returns the number of unique, nonnull values.

COUNT_BIG is a deterministic function when used without the OVER and ORDER BY clauses. COUNT_BIG is nondeterministic when used with the OVER and ORDER BY clauses

Question 14

TOP-def, syntax, remarks

Answer 14

def: Limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server 2019 (15.x). When you use TOP with the ORDER BY clause, the result set is limited to the first N number of ordered rows. Otherwise, TOP returns the first N number of rows in an undefined order. Use this clause to specify the number of rows returned from a SELECT statement. Or, use TOP to specify the rows affected by an INSERT, UPDATE, MERGE, or DELETE statement.
syntax: [TOP (expression) [PERCENT] [WITH TIES] ]

Best Practices:

In a SELECT statement, always use an ORDER BY clause with the TOP clause. Because, it’s the only way to predictably indicate which rows are affected by TOP.

Use OFFSET and FETCH in the ORDER BY clause instead of the TOP clause to implement a query paging solution. A paging solution (that is, sending chunks or “pages” of data to the client) is easier to implement using OFFSET and FETCH clauses. For more information, see ORDER BY Clause (Transact-SQL).

Use TOP (or OFFSET and FETCH) instead of SET ROWCOUNT to limit the number of rows returned. These methods are preferred over using SET ROWCOUNT for the following reasons:

• As a part of a SELECT statement, the query optimizer can consider the value of expression in the TOP or FETCH clauses during query optimization. Because you use SET ROWCOUNT outside of a statement that runs a query, its value can’t be considered in a query plan.

Question 15

AT TIME ZONE-def, syntax, remarks

Answer 15

def: converts an inputdate to the corresponding datetimeoffset value in the target time zone. When inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate is in the target time zone. If inputdate is provided as a datetimeoffset value, then AT TIME ZONE clause converts it into the target time zone using the time zone conversion rules.
syntax: inputdate AT TIME ZONE timezone
remarks: Works with windows registry on the local time machine and is time aware of daylight saving time.

Question 16

TODATETIMEOFFSET def, syntax, arguments

Answer 16

def: Returns a datetimeoffset value that is translated from a datetime2 expression;
syntax: TODATETIMEOFFSET ( expression , time_zone );

arguments:

expression: Is an expression that resolves to a datetime2 value;

time_zone:Is an expression that represents the time zone offset in minutes (if an integer), for example -120, or hours and minutes (if a string), for example ‘+13:00’. The range is +14 to -14 (in hours). The expression is interpreted in local time for the specified time_zone.​

remarks: Does not take account of daylight saving time changes.

Question 17

SWITCHOFFSET def, syntax, remarks

Answer 17

def: Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.
syntax: SWITCHOFFSET ( DATETIMEOFFSET, time_zone );

remarks:

Use SWITCHOFFSET to select a datetimeoffset value into a time zone offset that is different from the time zone offset that was originally stored. SWITCHOFFSET does not update the stored time_zone value.

SWITCHOFFSET can be used to update a datetimeoffset column.

Using SWITCHOFFSET with the function GETDATE() can cause the query to run slowly. This is because the query optimizer is unable to obtain accurate cardinality estimates for the datetime value. To resolve this problem, use the OPTION (RECOMPILE) query hint to force the query optimizer to recompile a query plan the next time the same query is executed. The optimizer will then have accurate cardinality estimates and will produce a more efficient query plan.

Question 18

What are Analytic Functions ?

Answer 18

Analytic functions calculate an aggregate value based on a group of rows. Unlike aggregate functions, however, analytic functions can return multiple rows for each group. Use analytic functions to compute moving averages, running totals, percentages or top-N results within a group.​

SQL Server supports these analytic functions:

• CUME_DIST (Transact-SQL)
• FIRST_VALUE (Transact-SQL)
• LAG (Transact-SQL)
• LAST_VALUE (Transact-SQL)
• LEAD (Transact-SQL)
• PERCENT_RANK (Transact-SQL)
• PERCENTILE_CONT (Transact-SQL)
• PERCENTILE_DISC (Transact-SQL)

Question 19

DATETIMEOFFSETFROMPARTS?

Answer 19

def: Returns a datetimeoffset value for the specified date and time arguments. The returned value has a precision specified by the precision argument, and an offset as specified by the offset arguments.
syntax: DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

Question 20

What are deterministic and nondeterministic functions? def

Answer 20

Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same. For example, the function AVG always returns the same result given the qualifications stated above, but the GETDATE function, which returns the current datetime value, always returns a different result.

There are several properties of user-defined functions that determine the ability of the SQL Server Database Engine to index the results of the function, either through indexes on computed columns that call the function, or through indexed views that reference the function. The determinism of a function is one such property. For example, a clustered index cannot be created on a view if the view references any nondeterministic functions

Question 21

How to determine if a function is deterministic or not?

Answer 21

We can determine if a function is deterministic with the isdeterministic property.

IsDeterministic can be accesed by the OBJECTPROPERTY function. The OBJECTPROPERTY function requires the OBJECTID(dbo.tablename) of the function and the property name to be passed as parameters.

EX: SELECT OBJECTPROPERTY(OBJECTID(‘dbo.FullName’),’IsDeterministic’)

Remarks:

• All agregate functions are deterministic;
• All built in string functions are deterministic;
• Datetime functions are never deterministic because they always return a different value

Question 22

OBJECT_DEFINITION-def, arguments syntax, remarks

Answer 22

def: Returns the Transact-SQL source text of the definition of a specified object.
syntax: OBJECT_DEFINITION ( object_id ) object_id-Is the ID of the object to be used. object_id is int, and assumed to represent an object in the current database context.

remarks:

OBJECT_DEFINITION applies to the following object types:

• C = Check constraint
• D = Default (constraint or stand-alone)
• P = SQL stored procedure
• FN = SQL scalar function
• R = Rule
• RF = Replication filter procedure
• TR = SQL trigger (schema-scoped DML trigger, or DDL trigger at either the database or server scope)
• IF = SQL inline table-valued function
• TF = SQL table-valued function
• V = View

Question 23

OBJECTPROPERTY-def, syntax, remarks

Answer 23

def-Returns information about schema-scoped objects in the current database. For a list of schema-scoped objects, see sys.objects (Transact-SQL). This function cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.

syntax: OBJECTPROPERTY ( id , property )
remarks: The Database Engine assumes that object_id is in the current database context. A query that references an object_id in another database will return NULL or incorrect results.

OBJECTPROPERTY(view_id, ‘IsIndexable’) may consume significant computer resources because evaluation of IsIndexable property requires the parsing of view definition, normalization, and partial optimization. Although the IsIndexable property identifies tables or views that can be indexed, the actual creation of the index still might fail if certain index key requirements are not met.

OBJECTPROPERTY(table_id, ‘TableHasActiveFulltextIndex’) will return a value of 1 (true) when at least one column of a table is added for indexing. Full-text indexing becomes active for population as soon as the first column is added for indexing.

Question 24

TIMEFROMPARTS-def, syntax, remarks

Answer 24

def: Returns a time value for the specified time and with the specified precision.
syntax: TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
remarks: TIMEROMPARTS returns a fully initialized time value. If the arguments are invalid, then an error is raised. If any of the parameters are null, null is returned. However, if the precision argument is null, then an error is raised.

The fractions argument depends on the precision argument. For example, if precision is 7, then each fraction represents 100 nanoseconds; if precision is 3, then each fraction represents a millisecond. If the value of precision is zero, then the value of fractions must also be zero; otherwise, an error is raised.

Question 25

What are aggregate and analytic functions?

Answer 25

Aggregate functions perform a calculation on a set of values and return a single value. They are allowed in the select list or the HAVING clause of a SELECT statement. You can use an aggregation in combination with the GROUP BY clause to calculate the aggregation on categories of rows. Use the OVER clause to calculate the aggregation on a specific range of value. The OVER clause cannot follow the GROUPING or GROUPING_ID aggregations.

All aggregate functions are deterministic, which means they always return the same value when they run on the same input values

Analytic functions compute an aggregate value based on a group of rows. However, unlike aggregate functions, analytic functions can return multiple rows for each group. You can use analytic functions to compute moving averages, running totals, percentages, or top-N results within a group.

Question 26

What are ranking and rowset?

Answer 26

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.

Rowset functions return an object that can be used like table references in an SQL statement.

Question 27

What are sclar functions and what are it’s categories?

Answer 27

def:Operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid.

Categories of scalar functions:

• Configuration Functions-Return information about the current configuration;
• Conversion Functions-Support data type casting and converting;
• Cursor Functions-Return information about cursors;
• Date and Time Data Types and Functions-Perform operations on a date and time input values and return string, numeric, or date and time values;
• JSON Functions Validate, query, or change JSON data.
• Logical Functions Perform logical operations;
• Mathematical Functions Perform calculations based on input values provided as parameters to the functions, and return numeric values.
• Metadata Functions Return information about the database and database objects.
• Security Functions Return information about users and roles;
• String Functions Perform operations on a string (char or varchar) input value and return a string or numeric value.
• System Functions Perform operations and return information about values, objects, and settings in an instance of SQL Server.
• System Statistical Functions-Return statistical information about the system;
• Text and Image Functions-Perform operations on text or image input values or columns, and return information about the value.

Question 28

What are deterministic functions?

Answer 28

SQL Server built-in functions are either deterministic or nondeterministic. Functions are deterministic when they always return the same result any time they are called by using a specific set of input values. Functions are nondeterministic when they could return different results every time they are called, even with the same specific set of input values.

Question 29

How does function collation work?

Answer 29

Functions that take a character string input and return a character string output use the collation of the input string for the output.

Functions that take non-character inputs and return a character string use the default collation of the current database for the output.

Functions that take multiple character string inputs and return a character string use the rules of collation precedence to set the collation of the output string.

Question 30

DATEDIFF_BIG def, syntax, remarks

Answer 30

def: This function returns the count (as a signed big integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.
syntax: DATEDIFF_BIG ( datepart , startdate , enddate ) ;

Remarks:

Use DATEDIFF_BIG in the SELECT , WHERE, HAVING, GROUP BY and ORDER BY clauses.

DATEDIFF_BIG implicitly casts string literals as a datetime2 type. This means that DATEDIFF_BIG does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

Specifying SET DATEFIRST has no effect on DATEDIFF_BIG. DATEDIFF_BIG always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.

DATEDIFF_BIG may overflow with a precision of nanosecond if the difference between enddate and startdate returns a value that is out of range for bigint.

Question 31

DATEDIFF-def, syntax, remarks

Answer 31

def: This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.
syntax: DATEDIFF ( datepart , startdate , enddate )

remarks:

• returns an interger value;
• DATEDIFF uses the time zone offset component of startdate or enddate to calculate the return value.

​

Question 32

SYSDATETIMEOFFSET-def, syntax, remarks

Answer 32

def: Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.
syntax: SYSDATETIMEOFFSET ( )

remarks:

• Transact-SQL statements can refer to SYSDATETIMEOFFSET anywhere they can refer to a datetimeoffset expression.
• SYSDATETIMEOFFSET is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

Question 33

SYSUTCDATETIME-def, syntax, remarks

Answer 33

def: Returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits.

SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE.

SYSDATETIMEOFFSET includes the system time zone offset.

SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET can be assigned to a variable of any one of the date and time types.

syntax: SYSUTCDATETIME ( )

remarks:

Transact-SQL statements can refer to SYSUTCDATETIME anywhere they can refer to a datetime2 expression.

SYSUTCDATETIME is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

Question 34

GETUTCDATE () def, syntax, remarks

Answer 34

def: Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time). This value is derived from the operating system of the computer on which the instance of SQL Server is running.
syntax: GETUTCDATE()

remarks:

Transact-SQL statements can refer to GETUTCDATE anywhere they can refer to a datetime expression.

GETUTCDATE is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

Question 35

What is SOUNDEX()? def, syntax, remarks

Answer 35

def: Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.
syntax: SOUNDEX ( character_expression ):

remarks:

SOUNDEX converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken in English. The first character of the code is the first character of character_expression, converted to upper case. The second through fourth characters of the code are numbers that represent the letters in the expression. The letters A, E, I, O, U, H, W, and Y are ignored unless they are the first letter of the string. Zeroes are added at the end if necessary to produce a four-character code. For more information about the SOUNDEX code.