The ‘Computer programming’ Archives
Posted June 26, 2017 by

Performance Tip: Avoid Using the SP_ Prefix in Stored Procedures

feature-sql1-618x350

Database developers often name stored procedures with the SP_ prefix in SQL Server. The SP_ prefix helps other developers quickly identify an object as a stored procedure. Although there have been many debates over whether or not user created objects should be named with the sp_ prefix following the naming convention standards, we should not […]

Posted June 20, 2017 by

Execute WITH RESULT SETS Feature in SQL Server

feature-sql1-618x350

With the introduction of SQL Server 2012, a useful feature was included: EXECUTE WITH RESULT SETS. This feature allows us to modify the column names and column data types of the result sets returned by a stored procedure without actually modifying the stored procedure code.

Posted June 5, 2017 by

Creating an Index with DROP_EXISTING = ON in SQL Server

feature-sql1-618x350

Creating an index in SQL Server is a moderately simple task that improves the query performance. We can create a clustered as well as a non-clustered index which can be either unique or non-unique as per requirement.

Posted May 29, 2017 by

How to Use the TRY_CAST Function in SQL Server

feature-sql1-618x350

The TRY_CAST function in SQL Server is used to cast value from its existing data type to a specified target data type. This occurs only if the operation is successful. It returns NULL if the conversion fails. TRY_CAST is an extended version of the CAST function. This tutorial explains how to use TRY_CAST in SQL […]

Posted March 6, 2017 by

SET ANSI_NULLS (ON | OFF) in SQL Server

feature-sql1-618x350

The ANSI_NULLS database SET option controls the behavior of Equal (=) and comparison operators (!=) in queries while dealing with NULL records. It is an ISO standard to decide for a query action to conclude how SQL Server should handle the comparison with NULLs.

Posted February 28, 2017 by

SET CONCAT_NULL_YIELDS_NULL in SQL Server

feature-sql1-618x350

How often do we think of NULL values while concatenating NULLs with string values? String concentration with NULL values can give us some unexpected results if we are not aware of CONCAT_NULL_YIELDS_NULL database SET options. CONCAT_NULL_YIELDS_NULL options control whether concatenation results shall be treated as NULL or empty string values.

Posted January 30, 2017 by

Logical Query Processing in SQL Server

feature-sql1-618x350

To learn the basics of SQL Server, you must have an understanding of how an SQL Server query engine executes an SQL query. T-SQL involves both logical and physical query processing. Physical query processing is about how a database engine processes a query. Logical query processing is a conceptual flow, an order of the clauses […]

Posted January 23, 2017 by

COALESCE and ISNULL Functions in SQL Server

feature-sql1-618x350

SQL Server has introduced multiple ways to handle NULL values. The functions that are most used to handle NULLs are COALESCE and ISNULL in SQL Server. These functions are used to find Non-NULL values from a list of arguments. In this post, we will see the uses of the ISNULL and COALESCE functions and their […]

Posted January 5, 2017 by

A Beginner’s Guide to Sequence Objects in SQL Server

database-admin

SQL Server has supported SEQUENCE objects since the inception of SQL Server 2012 and later editions. Oracle database has supported SEQUENCE objects from the beginning. You can read the archive post on Tech-Recipes using SEQUENCE in Oracle.

Posted November 27, 2016 by

Microsoft SQL Server: Estimated vs. Actual Execution Plans

SQL Server Estimated vs Actual Execution Plan

Let’s assume you are constructing a new house, and you have a contract with a builder. After thoughtful examination of the area, height, and materials needed, he estimates it will take 120 days to build a new home. After constructing the home, it actually took 200 days to built it. There is a difference between […]