Auto Create Statistics and Auto Update Statistics

Auto Create Statistics and Auto Update Statistics
5 (100%) 2 vote[s]

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

Several Ways to Insert Split Delimited Strings in a Column

Several Ways to Insert Split Delimited Strings in a Column
3.5 (70%) 4 vote[s]

In this article, I will demonstrate several ways to split the delimited string and insert it in into a column of a table in SQL Server. You can do it using the following methods:

  1. Convert delimited string into XML, use XQuery to split the string, and save it into the table.
  2. Create a user-defined table-valued function to split the string and insert it into the table.
  3. Split the string using STRING_SPLIT function and insert the output into a table. Read More

Learn Basic Data Analysis with SQL Window Functions

Learn Basic Data Analysis with SQL Window Functions
4 (80%) 8 vote[s]

This article is about T-SQL (Transact-SQL) Window functions and their basic use in day-to-day data analysis tasks.

There are many alternatives to T-SQL when it comes to data analysis. However, when improvements over time and introduction of Window functions are considered, T-SQL is capable of performing data analysis on a basic level and, in some cases, even beyond that. Read More

T-SQL SET Operators Part 2: INTERSECT and EXCEPT

T-SQL SET Operators Part 2: INTERSECT and EXCEPT
3.5 (70%) 10 vote[s]

In my previous article, I explained the basics of set operators, their types, and prerequisites for their use. I also talked about UNION and UNION ALL operators, their usage and differences.

In this article, we’re going to learn the following:

  1. EXCEPT and INTERSECT operators.
  2. Difference between INTERSECT and INNER JOIN.
  3. The detailed explanation of INTERSECT and EXCEPT with an example.

EXCEPT and INTERSECT operators were introduced in SQL Server 2005. Both are set operators used to combine the result sets generated by two queries and retrieve the desired output. Read More

How to Properly Use the T-SQL IsNumeric Function

How to Properly Use the T-SQL IsNumeric Function
3.8 (75%) 4 vote[s]

This article is focused on the T-SQL (Transact-SQL) IsNumeric function and its proper use in day-to-day SQL scripting tasks.

We will also see why it is important to understand how and why IsNumeric can be used – both incorrectly and correctly.

There may be some better alternatives to IsNumeric depending on the context. However, in the cases we’re going to cover in this article, I see this function as the best possible choice. Read More

T-SQL Datetime Data Type

T-SQL Datetime Data Type
3.3 (66.67%) 3 vote[s]

Introduction

Data types are attributes that specify the kind of data that objects such as columns, local variables, expressions, and parameters can hold. Across the RDBMS world, data types are typically grouped into string, numeric, and date data types.

T-SQL supports 6 date and time data types namely:

  1. Datetime
  2. Smalldatetime
  3. Date
  4. Time
  5. Datetime2
  6. Datetimeoffset

The first two data types are considered as legacy versions of the newer ones. In this article, we focus on the date data types and, specifically, on the datetime and datetime2 data types available in SQL Server. Table 1 gives details of the various date and time data types available in SQL Server. Read More

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

Advanced SQL: Variations and Different Use cases of T-SQL Insert Statement
3.6 (72%) 5 vote[s]

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

Basic and Complex Uses of Not Equal Comparison Operator in T-SQL

Basic and Complex Uses of Not Equal Comparison Operator in T-SQL
3.7 (74.29%) 7 vote[s]

This article is focused on the T-SQL Not Equal comparison operator (<>) and its uses in basic to slightly complicated SQL scripting tasks.

The article also highlights the importance of understanding the correct use of Not Equal comparative operator with expressions.

The Not Equal comparative operator is a very common operator used in T-SQL, however, it is important to understand how to use it effectively in different scenarios.

Read More

T-SQL SET Operators Part 1: UNION and UNION ALL

T-SQL SET Operators Part 1: UNION and UNION ALL
3 (60%) 2 vote[s]

In SQL Server, we can combine the same type of data from multiple tables using SET operators. After combining multiple SQL statements, it returns one result set. Following is the list of T-SQL SET operators:

  1. UNION
  2. UNION ALL
  3. INTERSECT
  4. EXCEPT

To use SET operators, we must follow a number of rules:

  1. The result set of both queries must have the same number of columns.
  2. The data type of columns retrieved by the top and bottom queries must be the same.
  3. If we want to sort the final result set, the ORDER BY clause must be at the end of the query.
  4. The positional ordering of the columns returned by the top and bottom queries must be same. Read More

Using Trace Flag 3226 to Suppress Log Backup Logging

Using Trace Flag 3226 to Suppress Log Backup Logging
3 (60%) 1 vote[s]

Introduction

Every backup operation in SQL Server is written to the SQL Server Error log. This includes Transaction Log Backups even when they occur as part of a Transaction Log Shipping Configuration. Sometimes logging the entire Log Backup can be a nuisance in the SQL Server Error Log and needs to be managed. Trace Flag 3226 is used to suppress such logging and we will demonstrate how this can be done in this article.

Read More