This article will provide an overview of the SQL Join and cover all of the SQL join types including inner, self, cross and outer. For inner joins we’ll be discussing Equi and Theta joins.

The Case statement in SQL is mostly used in a case with equality expressions. The SQL Case statement is usually inside of a Select list to alter the output. What it does is evaluates a list of conditions and returns one of the multiple possible result expressions.

When we’re developing solutions, we can sometimes forget useful commands we can use in T-SQL that make it convenient to remove data, eliminate objects, or carefully remove data. We look at three of these commands with a few examples of where we might consider using them in development, or in rare production cases. While they may offer us speed and convenience in some cases, we also look at some situations where they may not be the best tool to use.

A client recently discovered a discrepancy on one of our reports that showed an improvement in performance metrics but was inaccurate. Our reports came from a software tool, which showed the average performance throughout the day. It derived this number from periodic checks and the frequency changed, which affected our report. When we showed an improvement in the metrics on a report, the client showed us that the frequency change may have impacted this, not necessarily any improvement in performance. In situations where we’re measuring values and comparing them to other values, how can we prevent a change in measurement from impacting our reports? Read more »

SQL Server supports table valued functions, what are functions that return data in the form of tables.

JOIN operations in SQL Server are used to join two or more tables. However, JOIN operations cannot be used to join a table with the output of a table valued function.

APPLY operators are used for this purpose.

Sequence objects are used to sequentially generate numeric values. They were introduced in SQL Server 2012.

Sequence objects are similar to the IDENTITY column in any SQL table. However, unlike the IDENTITY column, they are independent and are not attached to any table. Sequence objects are used both independently and within the DML statements i.e. INSERT, UPDATE and DELETE.

This article will take a detailed look at sequence objects.

SQL Server provides us with two methods in T-SQL to assign a value to a previously created local SQL variable. The first method is the SET statement, the ANSI standard statement that is commonly used for variable value assignment. The second statement is the SELECT statement. In addition to its main usage to form the logic that is used to retrieve data from a database table or multiple tables in SQL Server, the SELECT statement can be used also to assign a value to a previously created local variable directly or from a variable, view or table.

This articles gives you a performance comparison for NOT IN, SQL Not Exists, SQL LEFT JOIN and SQL EXCEPT.

Read more »

SQL server provides us with comparison operators to modify subqueries. This article will start with the definition of universal quantification, with a quick brush up with real life logical examples.