Aggregate Functions

COUNT vs COUNT_BIG

Both COUNT and COUNT_BIG are SQL Aggregate Functions. Both COUNT and COUNT_BIG are Deterministic functions when used without the OVER and ORDER BY clauses.But when used with the OVER and ORDER BY clauses, both COUNT and COUNT_BIG are non-deterministic functions. Logic - COUNT(), COUNT_BIG() - Provides the Count of rows that includes Nullable/Duplicate Values (Simply… Continue reading COUNT vs COUNT_BIG

Advertisement
String Functions

REPLACE Vs. STUFF

REPLACE To Replace all occurrences of the given pattern in a string. It Executes till it replaces all occurrences of the given pattern in a string.Syntax - REPLACE ( String_Expression , String_Pattern , String_Replacement ) Example - DECLARE @String_Expression VARCHAR(50)='Input:-)String:-)ForExample'; DECLARE @String_Pattern VARCHAR(10)=':-)'; DECLARE @String_Replacement VARCHAR(1)=''; SELECT @String_Expression AS INPUT_STRING, REPLACE ( @String_Expression, @String_Pattern, @String_Replacement… Continue reading REPLACE Vs. STUFF

Basic Logics

Logical and Writing Query Processing Order

Physical (Writing) Query Processing Order SELECT DISTINCT <column-list> FROM <left_table> <join_type> JOIN <right_table> ON <join_condition> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> ORDER BY <order_by_list> Logical Query Processing Order FROM clause JOIN clause,ON clause,APPLY clause WHERE clause GROUP BY clause and AGGREGATE Functions CUBE | ROLLUP | GROUPING SETS HAVING clause SELECT clause,UNION clause DISTINCT… Continue reading Logical and Writing Query Processing Order