Database development

Overview of SQL IIF Statement

June 12, 2019 by Rajendra Gupta

SQL Server 2012 introduced a new built-in logical function SQL IIF. It is a shorthand form of writing CASE statement logic or IF-ELSE logic.

SQL Between Operator overview and examples

June 11, 2019 by Rajendra Gupta

We extract data from SQL Server tables along with various conditions. Usually, we have data in large amounts and SQL Between operator helps to extract a specific range of data from this huge data. For example, suppose we want to know the product sales between Jan 2019 to May 2019. In this case, we can use this operator in a SQL query.

SQL Not Equal Operator introduction and examples

June 6, 2019 by Rajendra Gupta

This article explores the SQL Not Equal comparison operator (<>) along with its usage scenarios.

Identity function tutorial in SQL Server

June 3, 2019 by Rajendra Gupta

This article explores the Identity function in SQL Server with examples and differences between these functions.

Why you should cleverly name Database Objects for SQL Unit Testing

May 29, 2019 by Haroon Ashraf

This article is focussed on clever database object naming from both development and SQL unit testing point of view.

This article also highlights the importance of naming database objects going through different development transitions including the SQL unit testing phase due to the agile nature of requirements.

SQL Unit Testing: Working with exceptions

May 21, 2019 by Esat Erkec

With this article, we will complete our journey with SQL Unit Testing. But first, let’s remember the main theme of the SQL Unit Testing again briefly. With the help of the SQL Unit Testing, we can detect and find out the flaws and bugs in database projects. This reduces the possible loss of time and money and reputation during the use of the product. However, it should be kept in mind that no matter how many tests are carried out, there will still be some bugs in the project, but SQL unit tests provide a greater degree of confidence. The fact that there are no bugs or flaws in the application at that time does not mean that the application is perfect. We cannot test all combinations and probabilities of the database objects. That is why in real life, considering certain parameters (risk, priority, etc.) we must ensure that a “reasonably sufficient” test is done.

SQL IF Statement introduction and overview

May 20, 2019 by Rajendra Gupta

This article explores the useful function SQL IF statement in SQL Server.

SQL Server CONCATENATE Operations with SQL Plus (+) and SQL CONCAT functions

May 13, 2019 by Rajendra Gupta

This article explores SQL Server Concatenate operations using the SQL Plus (+) operator and SQL CONCAT function.

SQL ISNULL function

May 10, 2019 by Rajendra Gupta

This article explores the SQL ISNULL function to replace NULL values in expressions or table records with examples.

SQL CHARINDEX

May 9, 2019 by Rajendra Gupta

We use the SQL CHARINDEX function to find the position of a substring or expression in a given string. We might have a character in different positions of a string. SQL CHARINDEX returns the first position and ignores the rest of matching character positions in a string.

SQL Unit Testing Mocking Stored Procedures

May 8, 2019 by Esat Erkec

In this SQL Unit testing article, we will continue our journey with SQL unit testing. In the previous articles of this series (see the TOC at the bottom), we mentioned about SQL unit testing essentials and designing approaches as well and then we reinforce these notions with various tSQLt framework practical examples. In this article, we will continue to learn how to mock stored procedures in the tSQLt framework and we will provide it with two examples.

10 Most Common SQL Unit Testing Mistakes

May 7, 2019 by Haroon Ashraf

This SQL unit testing article is based on the fact that we often learn from the mistakes so the idea is to highlight the mistakes to understand them clearly so that they can be avoided straight away.

DATEADD SQL function introduction and overview

May 6, 2019 by Rajendra Gupta

This article explores the DATEADD SQL function and its usage scenarios with various examples.

How to use fake functions with SQL unit testing?

April 30, 2019 by Esat Erkec

In this article series, we are exploring SQL unit testing, in general, and also we are reinforcing details and topics with various practical examples. We are using the tSQLt framework because it is the quite powerful and handy tool in order to develop and code SQL unit tests.

SQL TRIM function

April 24, 2019 by Rajendra Gupta

In this article, we will review the new SQL TRIM function in SQL Server 2017 onwards as well as providing some information on strings functions that pre-date it like LTRIM AND RTRIM.

DATEPART SQL function

April 23, 2019 by Rajendra Gupta

This article explores the DATEPART SQL function and its use in writing t-SQL queries. In the previous article, SQL Convert Date Functions and Formats, we explored various data formats and convert them using SQL Convert function.

SQL Add Column operations

April 22, 2019 by Rajendra Gupta

This article explains the SQL add column operation into an existing SQL table. We will also explore different examples of SQL add column operations.

How to use fake tables in SQL unit testing?

April 22, 2019 by Esat Erkec

In this article on SQL unit testing, we will talk about how to isolate SQL unit tests from dependencies and how to use fake tables in SQL unit tests, so that we will able to develop more robust and less fragile SQL unit tests.

Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing

April 19, 2019 by Haroon Ashraf

This article talks about core concepts of test-driven database development followed by creating simple SQL unit tests with tSQLt based on this approach.

SQL Union vs Union All in SQL Server

April 18, 2019 by Rajendra Gupta

This article explains to the SQL Union and vs Union All operators in SQL Server. We will also explore the difference between these two operators along with various use cases.

SQL OUTER JOIN overview and examples

April 16, 2019 by Rajendra Gupta

This article will provide a full overview, with examples of the SQL Outer join, including the full, right and left outer join as well as cover the union between SQL left and right outer joins.

SQL Code Snippets in Azure Data Studio

April 16, 2019 by Rajendra Gupta

This article will fully cover the code snippet SQL developer productivity feature in Azure Data Studio including a list of available snippets and examples of how to create custom code snippets

INSERT INTO SELECT statement overview and examples

April 12, 2019 by Rajendra Gupta

This article covers the SQL INSERT INTO SELECT statement along with its syntax, examples, and use cases.

Methods to Insert Data into SQL Server

April 10, 2019 by Ed Pollack

Summary

There are a variety of ways of managing data to insert into SQL Server. How we generate and insert data into tables can have a profound impact on performance and maintainability! This topic is often overlooked as a beginner’s consideration, but mistakes in how we grow objects can create massive headaches for future developers and administrators.

SQL PARTITION BY Clause overview

April 9, 2019 by Rajendra Gupta

This article will cover the SQL PARTITION BY clause and, in particular, the difference with GROUP BY in a select statement. We will also explore various use cases of SQL PARTITION BY.