The ‘Database’ Archives
Posted March 31, 2019 by

How To Schedule SQL Agent Job Using T-SQL Script

How To Schedule SQL Agent Job Using T-SQL Script

SQL Server agent job is a windows service based scheduler. Agent job is used to schedule and automate SQL scripts, administrative tasks, SSIS packages and various other tasks using SQL agent job tool. This component is integrated along with SSMS and available under Object explorer as SQL Server Agent.

Category Database, SQL Server

Posted March 24, 2019 by

Pagination using ROW_NUMBER with a CTE In SQL Server

Pagination In SQL Server-ROW_NUMBER with CTE

ROW_NUMBER with a CTE (Common Table Expression) is used for Pagination in query results. This was a go-to method with SQL Server 2008. Later, SQL Server 2012 introduced the OFFSET-FETCH option to simplify pagination using queries with improved performance. Pagination is a way to show results into a small number of rows and pages. Instead […]

Category Database, SQL Server

Posted March 16, 2019 by

How To Refresh Intellisense Cache In SQL Server

EXCEPT and INTERSECT – SET Operator In SQL Server

Microsoft SQL Server software is bundled with exceptional features. Whenever we begin typing queries, SQL Server automatically starts suggesting you the schema names, table name and columns. Many more suggestions are offered to make developers’ work easy. All these suggestions and auto-completes are nothing but SQL Server’s Intelligence cache feature.

Category Database, SQL Server

Posted March 10, 2019 by

Beginner’s Guide To LIKE In SQL Server

Beginner’s Guide To LIKE In SQL Server

SQL Server LIKE operator is used for pattern and wildcard text search. Using LIKE with WHERE clause we can filter out rows by either matching partial text or using a wildcard as a placeholder for a character. We can use a single character or multiple character search. Possible use of LIKE operator is when we […]

Category Database, SQL Server

Posted March 4, 2019 by

How To Use LEN and DATALENGTH Function In SQL Server

How To Use LEN and DATALENGTH Function In SQL Server

Counting the number of characters or size of a string in SQL Server is possible with LEN and DATALENGTH function. Both these functions are used interchangeably. While we need to be careful while using these functions in production code. Basically, both the function sounds similar although each has a distinct purpose.

Category Database, SQL Server

Posted February 24, 2019 by

How To Use Default Constraint In SQL Server

How To Use BCP In SQL Server

Constraints in SQL database maintains data integrity and accuracy. SQL Default Constraint is used to assign a default value to a column when no value is specified. Using Default constraint we can have a meaningful value instead of storing NULL or Empty value. It reduces the presence of NULLs when applicable by improving data consistency.

Category Database, SQL Server

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