Prime Study Hub

The COUNT_BIG is an aggregate function in SQL server which returns the number of items within a group. This function works like COUNT function but the only difference is the data type of its return values. The COUNT_BIG function return type is bigint , COUNT function return type is int .  Syntax COUNT_BIG ( { [ [ ALL | DISTINCT ] expression ] | * } ) ALL  - It is the default in COUNT_BIG function. This applies the aggregate function to all values. DISTINCT - This returns the number of unique nonnull values. Expression - It is an expression of any type but does not support aggregate functions or subqueries. * - This denotes that all the rows should be counted, including the duplicate rows and null values and returns the total number of rows in a statement. Let's look into an example, SELECT COUNT_BIG(*) AS "Work Order Count" FROM [AdventureWorks2019].[Production].[WorkOrder] Result: ALL SELECT COUNT_BIG(ALL WorkOrderID) AS "Work Order Count" FROM [Adventur...

APPROX_COUNT_DISTINCT is an aggregate function in SQL Server which returns the approximate number of distinct values. This is very efficient with large number of rows.  Syntax APPROX_COUNT_DISTINCT (expression) The return types of this function is bigint . And expression type can be of any type, except image, sql_variant, ntext, or text - as per Microsoft documentation .  Let's look into an example, SELECT APPROX_COUNT_DISTINCT([ProductID]) AS Approx_Distinct_Product FROM [AdventureWorks2019].[Production].[WorkOrder]; The above statement returns the approximate number of different products from WorkOrder table. This is very powerful with billions of rows without any high memory utilization or performance issues.

The ROW_NUMBER() function assigns the integer value to each row of the returned result set which starts at 1 and increments by 1. Let's look into the syntax, ROW_NUMBER() OVER ( [PARTITION BY exp1, exp2 ... expn] ORDER BY col1,col2...coln) The ROW_NUMBER function must have an OVER clause with ORDER BY. ORDER BY clause is mandatory PARTITION BY clause is optional Let's look into an example, SELECT TOP 10 [PersonType] ,[FirstName] ,[LastName] , ROW_NUMBER() OVER(ORDER BY FirstName) as RowNum FROM [AdventureWorks2019].[Person].[Person] For the above example, we are using the [Person] table from [AdventureWorks2019] database. This statement returns the temporary row number for each row based on the order of first name column. Using ROW_NUMBER function with PARTITION BY clause The PARTITION BY clause breaks up the result set into partitions. Once the data is partitioned, row number is reset to 1 when the partition changes. SELECT [ProductID] ,[Name] ...

COL_NAME is a predefined SQL server function that returns the name of a table column using table and column identification numbers. COL_NAME (table_id, column_id) COL_NAME function takes two parameters which is table and column identification numbers like specified in the above statement. Let's look into an example of AdventureWorks2019 database, SELECT TOP (1000) [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[EmailPromotion] ,[AdditionalContactInfo] ,[Demographics] ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks2019].[Person].[Person] Here is the above statement result set, Let's see how to retrieve the column name FirstName using the COL_NAME function, SELECT COL_NAME(OBJECT_ID('Person.Person'), 5) AS ColumnName; /* COL_NAME (table_id, column_id) */ The OBJECT_ID function returns the identification number of a specified tab...

CONTAINS is a predicate used to perform a full-text search in one or more full-text indexed columns. Let's look into an example, SELECT [Description] FROM [Sales].[SpecialOffer] WHERE CONTAINS([Description], 'discount'); /* CONTAINS(Column_Name, Search_Text) */ The above statement returns the data which has  discount  word in column  [Description] .  And CONTAINS predicate is not case sensitive. CONTAINS text search is very quick and easy to search a word or phrase.   Note: CONTAINS full-text search works in a full-text indexed column. If you face any errors, have a look at this link here . Using CONTAINS with <multiple columns> SELECT [Description],[Type] FROM [Sales].[SpecialOffer] WHERE CONTAINS(([Description],[Type]), 'discount'); /* CONTAINS((Column_Name1,Column_Name2,...), Search_Text) */ The above CONTAINS query searches the word discount in both [Description] and [Type] columns and returns the result that matches the exact word. Using C...

 IS NOT NULL is the opposite of the IS NULL predicate, which is useful to retrieve the values that doesn't have NULL value in an expression. Let's look into an example of AdventureWorks2019 database, SELECT [BusinessEntityID] ,[DepartmentID] ,[ShiftID] ,[StartDate] ,[EndDate] ,[ModifiedDate] FROM [AdventureWorks2019].[HumanResources].[EmployeeDepartmentHistory] WHERE [EndDate] IS NOT NULL Above statement returns the  EmployeeDepartmentHistory which doesn't have NULL EndDate values.

 IS NULL is a predicate which is useful to identify the expression has NULL values.  Let's look into an example of AdventureWorks2019 database, SELECT [BusinessEntityID] ,[DepartmentID] ,[ShiftID] ,[StartDate] ,[EndDate] ,[ModifiedDate] FROM [AdventureWorks2019].[HumanResources].[EmployeeDepartmentHistory] WHERE [EndDate] IS NULL Above statement returns the EmployeeDepartmentHistory which has NULL EndDate value.

 To resolve this issue, we need to enable the full-text indexing on table. Follow the steps below to resolve the issue. Step1: Go to your table and right-click and choose Full-Text index option and select Define Full-Text Index Step2: Choose next in Full-text indexing wizard Step3: Click next (A unique constraint will be created automatically) Step4: Choose the column which you want full-text indexing then click next Step5: Click next Step6: Click next again Step7: Click next Step8: Click Finish - Your full-text indexing will be ready Step9: TADA! Step10: Resolved!!!

What is TABLESAMPLE? It is a clause used in the SQL SELECT statement to retrieve the random number of data from a table without any specific order. There are three ways to achieve this, By Percentage By No of rows By Seed number By Percentage TABLESAMPLE percentage will return the percentage of the data pages of a table. Let's look into an example of the  AdventureWorks2019 database with the customer table of 19,820 rows. SELECT *FROM Sales.Customer TABLESAMPLE(10 PERCENT); You may assume that TABLESAMPLE will return the 10 percentage of total rows which is 19,820 but it doesn't. It will return the percentage of data pages not total number of rows. Let's look into an below example, Example 1: Example 2: As you can see in the above examples, when we executed the query in the first example returned 3,135 rows and the second example returned 2,475 rows. TABLESAMPLE will return the percentage of data pages not the percentage of total rows of a table. By No of rows SELECT ...