The ‘SQL Server’ Archives
Posted February 19, 2019 by

How To Use CONCAT_WS Function In SQL Server

How To Use BCP In SQL Server

CONCAT_WS, a string Concatenation function With Separator (WS). A fresh addition to SQL Server 2017. CONCAT_WS function takes multiple string arguments (at least two) and separator as an input. Furthermore, joins multiple strings together into a single string – connected by a Separator. While CONCAT_WS is an extension to CONCAT function with an added separator.

Category Database, SQL Server

Posted February 15, 2019 by

How To Get Object Dependencies In SQL Server

How To Use BCP In SQL Server

Programming languages have objects that rely on another object. A rather similar principle applies to a database. Multiple types of object dependencies exist in a database including relationships between tables, primary-foreign keys, constraints and schema bindings. Therefore Object dependencies become critical in a growing database and have to be handled with caution.

Category Database, SQL Server

Posted February 9, 2019 by

How To Use TRANSLATE Function In SQL Server

How To Use TRANSLATE Function In SQL Server

TRANSLATE, a string manipulation function, to substitute set of characters in a string with another set of characters. In the background, it works on a SINGLE CHARACTER substitution approach. Taking a single character specified in a string and replacing it with another set of character. Furthermore, advancing to the next character and so forth.

Category Database, SQL Server

Posted January 31, 2019 by

How To Use LEAD and LAG Function In SQL Server

How To Use BCP In SQL Server

SQL Server supports numerous helpful analytical functions to perform statistical and computational operations. Analytical functions are straightforward to use and replace complex code, nested queries and self joins which are tough to scale and optimize for best performance. Moreover, in this tech-recipe post, we will learn more on LEAD and LAG analytic functions. LEAD and […]

Category Database, SQL Server

Posted January 27, 2019 by

How To Use ROW_NUMBER Function In SQL Server

How To Use BCP 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 BCP 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 IF…ELSE Statement 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 IF…ELSE Statement 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 IF…ELSE Statement 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 IF…ELSE Statement 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 IF…ELSE Statement 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 IF…ELSE Statement 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