Category: SQL Server

Auto Create Statistics and Auto Update Statistics

Nisarg Upadhyay
Rate this post

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

SSRS Reports Development in Simple Terms

Haroon Ashraf
SSRS Reports Development in Simple Terms
4.7 (93.33%) 3 vote[s]

The article is dedicated to the fundamentals of SQL Server Reporting Services (SSRS) development and aimed at beginners and professionals interested in database development.

A direct method in the form of a walkthrough is used to discuss the core concepts and their implementation in regards to SQL Server Reporting Services (SSRS).

The main focus of the article is to give the basic concepts of reports development rather than discuss the latest SQL Server Reporting Services (SSRS) versions and their features.

Read More

Several Ways to Insert Split Delimited Strings in a Column

Nisarg Upadhyay
Several Ways to Insert Split Delimited Strings in a Column
4.3 (86.67%) 3 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

Haroon Ashraf
Learn Basic Data Analysis with SQL Window Functions
4 (80%) 6 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

Nisarg Upadhyay
T-SQL SET Operators Part 2: INTERSECT and EXCEPT
3.8 (75.56%) 9 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

Haroon Ashraf
How to Properly Use the T-SQL IsNumeric Function
4.7 (93.33%) 3 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

Kenneth Igiri
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

Nisarg Upadhyay
Advanced SQL: Variations and Different Use cases of T-SQL Insert Statement
3.8 (75%) 4 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

How to Automate the Process of SQL Server Database Schema Synchronization

{coding}Sight
How to Automate the Process of SQL Server Database Schema Synchronization
2.5 (50%) 2 vote[s]

The goal

Keeping two databases in sync is a task we often encounter when working on SQL database development and maintenance. One particular case is this – the versions of a database present on testing and development environments need to be consistently synchronized to make sure that tests are running on the most recent version. This should be done by detecting schema changes in the development version of a database and then automatically synchronizing them with the version from the testing environment on a scheduled basis.

How to achieve it

We’ll look into how automated synchronization of two SQL databases can be achieved by combining a schema comparison tool with PowerShell scripts and scheduling. Read More

SQL Vulnerability Assessment

Mohammed Moinudheen
SQL Vulnerability Assessment
3.4 (68%) 5 vote[s]

SQL Vulnerability Assessment is a feature available in the latest versions of SQL Server Management Studio (SSMS). This feature is very easy to use and it will show you all the security vulnerabilities and deviations in your SQL database. This is something you can run on your most critical databases to ensure you’re properly following strict security practices and that your client’s databases are in safe hands. In this article, we will describe the process of running these scans against your databases. With the amount of data growing with each year, database security is an important aspect every DBA needs to take care of. The consequences of data breaches are severe, so they may affect your future as a DBA and severely damage your firm’s reputation. Read More