Prime Study Hub

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 ...

By using FOR JSON clause we can simply format the query results to JSON output.  Let's look into an example, SELECT Id, ProductNo, ProductName from tbl_products FOR JSON AUTO; In the above statement, we have three columns (Id, ProductNo, ProductName) from the Products table with FOR JSON clause and AUTO mode to automatically return the JSON formatted output based on the order of SELECT statement. Output [ { "Id":1, "ProductNo":2, "ProductName":"Alexa Eco Dot" }, { "Id":2, "ProductNo":3, "ProductName":"Alexa Eco Dot1" }, { "Id":3, "ProductNo":4, "ProductName":"Alexa Eco Dot2" }, { "Id":4, "ProductNo":5, "ProductName":"Alexa Eco Dot3" } ]

Sequence is a unique set of numerical values 1, 2, 3, ... which are generated based on needs.  CREATE SEQUENCE sequence_name START WITH integer_value INCREMENT BY integer_value MINVALUE minimum_value MAXVALUE maximum_value CYCLE|NOCYCLE sequence_name : Unique name of the sequence. minimum_value: Minimum value of the sequence. maximum_value: Maximum value of the sequence. cycle: It continues the same sequence once it reaches the maximum value. Basically, once the maximum value is reached it resets the next value as minimum value. nocycle: Throws an error message when it reaches the maximum value. Example Let's create a sequence named "prod_sequence" like mentioned in below statement, CREATE SEQUENCE prod_sequence START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 5 NO CYCLE After the sequence has been created to see the properties of the sequence use below statement, SELECT * FROM sys.sequences WHERE name = 'prod_sequence'; Using Sequence The below...

Toast is a lightweight push notification used to provide hints to the users for the consequence of any user actions (eg. Form submission, Notification alert messages and etc.) Default Toasts have two parts basically, one is the header ( .toast-header ) and another is the body ( .toast-body ) which contains the actual message. See the Pen Bootstrap 5 Toasts by Prime Study Hub ( @PrimeStudyHub ) on CodePen . Colorful Toasts  You can change the background color of the Toasts by using the Bootstrap 5 background utilities (eg.  .bg-primary , .bg-secondary , .bg-info , .bg-danger , etc...). See the Pen Bootstrap 5 Colorful Toasts by Prime Study Hub ( @PrimeStudyHub ) on CodePen .