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 […]

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 […]

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 […]

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 […]

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 […]

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.

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 […]

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 […]

IDENTITY property in SQL Server creates an identity type column. It generates auto-incrementing values in table by defining seed and increment values, works much like SEQUENCE object in SQL Server and Oracle. However, IDENTITY property is table dependent and SEQUENCE object works independently from the table.

SQL Server 2016 edition has included an awe-inspiring feature in Database engine that is DROP IF EXISTS along with a bunch of superior features. Option DROP IF EXISTS is used when we need to verify if an object exists in a database before creating/ dropping it. In this situation, we need to first drop existing […]

Database developers often name stored procedures with the SP_ prefix in SQL Server. The SP_ prefix helps other developers quickly identify an object as a stored procedure. Although there have been many debates over whether or not user created objects should be named with the sp_ prefix following the naming convention standards, we should not […]

With the introduction of SQL Server 2012, a useful feature was included: EXECUTE WITH RESULT SETS. This feature allows us to modify the column names and column data types of the result sets returned by a stored procedure without actually modifying the stored procedure code.