Guide for CTE in SQL Server

The Common Table Expression aka CTE in SQL Server provides a temporary result set in T-SQL. You can refer to it within a SQL Select, SQL Insert, SQL Delete, or SQL Update statement.

The option is available from SQL Server 2005 onwards, helping the developers write complex and long queries involving many JOINs, aggregation, and data filtering. Usually, developers use subqueries for writing T-SQL codes, and SQL Server stores these CTE in memory temporally until the query execution finishes. Once the query is finished, it is removed from memory.

CodingSight - Guide for CTE in SQL Server
Read More

An Overview of the PRINT Statement in SQL Server

The SQL PRINT statement serves to display the user-defined message. For example, you are developing a script with T-SQL Loops, and you want to display some specific message on each iteration of a loop. Then you can use the PRINT statement. Or, you can use it when developing a script with conditional statements. In that case, you ensure that the condition evaluated by the statement performs the correct step or generates the correct output. The PRINT statement can also be used to track the process of a T-SQL query or a stored procedure or to return the customized message.

CodingSight - PRINT Statement
Read More

SQL Server Delete Statement: How to Remove One or Multiply Rows from the Table

The right application of the DELETE statement for data removal is crucial, and it involves lots of issues. Still, there are standard practices of the DELETE statement usage that simplify all such tasks.

This article will explore some of the professional life scenarios to equip you with the most helpful tips to use the DELETE statement correctly. You can remove data from a table in different ways. Explore the difference between DELETE and TRUNCATE in SQL Server that has been covered with practical examples.

CodingSight - SQL Server DELETE – Removing One or More Rows from a Table
Read More

CREATE TABLE MySQL vs T-SQL with Syntax Examples

Are you a T-SQL developer learning the basics of MySQL? Then, one of the things you might want to learn is MySQL CREATE TABLE statement. Besides, the fastest way to learn a new SQL database platform is by comparing its common functionality and syntax.

That’s what we are going to do today. But the full syntax is a lot. So, we will only cover 5 basic points to get you up and running with MySQL CREATE TABLE command.

CodingSight - Top 5 MySQL CREATE TABLE Syntax for T-SQL Developers
Read More

Use Cases for SQL Server MERGE Statement: Syncing Online and History Tables

INTRODUCTION

The SQL Server MERGE statement is an incredibly useful tool for carrying out DML operations based on comparing two tables or two data sets. Usage of this statement is actually like performing multiple operations in a single statement.

This article will explore three use cases that border on ensuring data between an online table and a history table in sync.

Read More

Your Ultimate Guide to SQL Join: CROSS JOIN – Part 3

CROSS JOIN is in the spotlight. This article finishes our small series of SQL JOIN-related publications. If you missed the previous two articles, refer to them as follows:  

SQL Server CROSS JOIN is the simplest of all joins. It implements a combination of 2 tables without a join condition. If you have 5 rows in one table and 3 rows in another, you get 15 combinations. Another definition is a Cartesian Product.

Now, why would you want to combine tables without a join condition? Hang on a bit because we are getting there. First, let’s refer to the syntax.

Read More

Add Columns to an Existing Table in SQL Server Database

Introduction

A table is a two-dimensional logical structure and the fundamental means of storing data in relational database management systems. Its ‘row and column’ format is very much like the organization of the spreadsheet. 

Each new record introduced to a table is a row (also called a record or tuple), while rows are grouped into a finite set of columns (also called fields or attributes). Each column has a name and data type, and it serves as a reference for displaying result sets when querying data from relational database systems.

In this article, we explore the current options letting us add new columns to an existing table in SQL Server database. The use case may arise on many occasions. Particularly, it happens when there are updates for an application, and they demand adding new columns.

Read More