Auto Create Statistics and Auto Update Statistics

Statistics comprises lightweight objects that are used by SQL Server Query optimizer to determine the optimal way to retrieve data from the table. SQL Server optimizer uses the histogram of column statistics to choose the optimal query execution plan. If a query uses a predicate which already has statistics, the query optimizer can get all the required information from the statistics to determine the optimal way to execute the query. SQL Server creates statistics in two ways:

  1. When a new index is created on a column.
  2. If the AUTO_CREATE_STATISTICS option is enabled.

In this article, Auto Create Statistics and Auto Update Statistics options are analyzed. They are database specific and can be configured using SQL Server management studio and T-SQL Query. Read More

Advanced SQL: Variations and Different Use cases of T-SQL Insert Statement

In my previous article, I demonstrated:

  1. Insert the output of the table-valued function in the SQL table.
  2. Insert the output of the table-valued function that is created on the remote database server.

In this article, I am going to demonstrate:

  1. Copy data between two tables, created in a different schema.
  2. Copy data between two tables, created in different databases on the same server.
  3. Copy data between two tables created, in different databases resides the different server (Cross server query)

Read More

Basics of Running T-SQL Statements from Command Line using SQLCMD

This article is about developing a basic understanding of sqlcmd utility to run T-SQL commands directly from the command prompt without the need of SSMS (SQL Server Management Studio).

The article also highlights the importance of using a lightweight sqlcmd utility to perform some basic database tasks that would otherwise require getting connected to the database through a pre-installed database tool such as SSMS (SQL Server Management Studio) or SSDT (SQL Server Data Tools) followed by getting it ready to run SQL scripts against the desired database(s).

The sqlcmd utility can be a real time-saver for database developers and DBAs since they can straight away run the required SQL scripts from the command-line.

Read More

MERGE: Updating Source and Target Tables Located on Separate Servers

What is the MERGE statement?

Using the MERGE statement, we can change data in a target table based on data in a source table. Using it, we can execute INSERT, UPDATE and DELETE on the target tables within a single query block. It joins both tables using columns, common in both tables like the primary key. Based on how column data matches, changes apply to data of target table. The following image illustrates how “MERGE” works:

MERGE statement in SQL Server Read More