The ‘Database’ Archives
Posted January 27, 2019 by

How To Use ROW_NUMBER Function In SQL Server

How To Use Default Constraint In SQL Server

ROW_NUMBER, analytic and ranking function in SQL Server. Assigns sequence number to table rows in incrementing integer values starting at 1 for the first row. Assigned row numbers act as temporary value to result set not persistent. ROW_NUMBER function works with ORDER BY clause to sort the rows in the defined order then numbers the […]

Category Database, SQL Server

Posted January 20, 2019 by

How To Split Delimited String In SQL Server Using STRING_SPLIT

How To Use Default Constraint In SQL Server

STRING_SPLIT, a table-valued function (TVF), splits delimited list into a single column table rows. SQL Server 2016 added STRING_SPLIT function, simplifying the problem of splitting rows, delimited by a separator into multiple rows of single values. Furthermore, Normalization Forms never recommends storing a delimited row in a table. In this case, a denormalized table can […]

Category Database, SQL Server

Posted January 15, 2019 by

How To Save Query Execution Plan In SQL Server

How To Use CHARINDEX Function In SQL Server

Query execution plan in SQL Server, in short, a result of query optimizer’s attempt to produce the least cost-effective and efficient plan for an executed query. SQL Server database engine uses advanced algorithms, available statistics, cardinality estimations to produce the most suitable plan. Particularly, the execution plan shows, how your submitted queries were executed by […]

Category Database, SQL Server

Posted January 12, 2019 by

How To Use Unique Constraint In SQL Server

How To Use CHARINDEX Function In SQL Server

Unique constraint prevents duplicates and ensures that there is only one distinct value of their own in a column. Perhaps, the most widely used constraint in a database to maintain data integrity is Primary key. Though, practically a table can have only one primary key. Therefore, to enforce further uniqueness in other columns we use […]

Category Database, SQL Server

Posted December 31, 2018 by

How To Use Encrypted View In SQL Server

How To Use CHARINDEX Function In SQL Server

A VIEW is a virtual table defined by a query. VIEWs are essentially similar to other database objects whose structure along with query is stored in the database. VIEWs can have complex queries with multiple joins, nested views or ad-hoc queries. Most importantly, a VIEW is benefited because we can expose a limited number of […]

Category Database, SQL Server

Posted December 29, 2018 by

How to Use NTILE Ranking Function In SQL Server

How To Use CHARINDEX Function In SQL Server

NTILE is a ranking function in SQL Server to distribute rows into a specified number of groups and assign a number to each group in incremental order starting with One. NTILE function works with ORDER BY clause to sort the rows in the specified order and splits them into groups. Let us assume we have […]

Category Database, SQL Server

Posted December 16, 2018 by

How To Use Case Statement In Order By Clause

How To Use CHARINDEX Function In SQL Server

A CASE statement is an expression to evaluate different conditions and return a scalar value when a condition is met. If none of the condition evaluated to TRUE it will return a value from ELSE block which is optional. ORDER BY clause used to sort the values in either ascending or descending order. A practical […]

Category Database, SQL Server

Posted December 15, 2018 by

How To Use FIRST_VALUE & LAST_VALUE Function In SQL Server

How To Use CHARINDEX Function In SQL Server

SQL Server 2012 version has ended a drought of insufficient analytical functions in SQL Server. Analytical functions like LEAD, LAG, FIRST_VALUE and LAST_VALUE that made querying and reporting easy – especially in the Business Intelligence domain. Performing analytical operations before these functions was a tedious task; Writing complex queries needed nested queries and self-joins resulting […]

Category Database, SQL Server

Posted December 3, 2018 by

Beginner’s Guide To Dynamic Data Masking In SQL Server

How To Use CHARINDEX Function In SQL Server

Dynamic Data Masking (DDM), a valuable feature added in SQL Server 2016. Hiding sensitive data from unauthorized users is achievable using DDM. Most importantly, Masking of data and encryption are two different ways of securing data. Dynamic Data Masking uses different strategies to hide data – benefiting by not modifying stored data as Encryption feature […]

Category Database, SQL Server

Posted November 26, 2018 by

How To Use STRING_AGG – Concat Multiple Row Values In SQL Server

How To Use CHARINDEX Function In SQL Server

Concat multiple row values in SQL Server is a well-known familiar problem. Every database developer happened to write concatenation string queries for reporting or general purpose. Numerous methods including XML PATH, COALESCE function, Recursive CTE been used to achieve desired results.

Category Database, SQL Server

Posted November 25, 2018 by

NULLIF – TSQL Function in SQL Server

How To Use CHARINDEX Function In SQL Server

NULLIF is a function applied to return a NULL value if specified two expressions (expression1= expression2) are equal. NULLIF function is an ANSI function, it is available with other RDBMS vendors like ORACLE, MySQL, Postgres and among others. NULLIF Function can be useful in cases when applications are using different indicators to represent a Missing […]

Posted November 23, 2018 by

TRY…CATCH Error & Exception Handling in SQL Server

Implementing acceptable Error Handling logic in T-SQL code is important to avoid unexpected errors breaking the system. Exception Handling using TRY…CATCH block in Microsoft SQL Server is fairly identical to exception handling in .NET and Java programming languages. Basic Fundamental of TRY…CATCH block is a group of statements can be enclosed in a BEGIN TRY…END […]