Author: Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.

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

Nisarg Upadhyay
T-SQL SET Operators Part 1: UNION and UNION ALL
4 (80%) 1 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

Advanced SQL: CROSS APPLY and OUTER APPLY

Nisarg Upadhyay
Advanced SQL: CROSS APPLY and OUTER APPLY
4.1 (82.86%) 7 vote[s]

In this article, we’ll look into the “APPLY” operator and its variations – CROSS APPLY and OUTER APPLY along with examples of how they can be used.

In particular, we will learn:

  • the difference between CROSS APPLY and the JOIN clause
  • how to join the output of SQL queries with table-evaluated functions
  • how to identify performance issues by querying dynamic management views and dynamic management functions.

Read More

Advanced SQL: Insert output of the parameterized table-valued function in SQL table

Nisarg Upadhyay
Advanced SQL: Insert output of the parameterized table-valued function in SQL table
3 (60%) 3 vote[s]

In this article, I am going to demonstrate the following:

  1. How to insert the output of a table-valued function in a SQL table.
  2. How to insert the output of a table-valued function which is created on the remote database server.

What is “Insert into” statement

In RDBMS, “Insert into” is one of the basic SQL statements. It is used to insert new records in a SQL table. Using the statement, we can perform the following tasks:

  • Insert new records in a table (Basic Insert).
  • Insert values of a specific column in a table.
  • Insert the output generated by a stored procedure in a SQL table. Read More

SQL Server Database Replication

Nisarg Upadhyay
SQL Server Database Replication
3.4 (68%) 5 vote[s]

Database replication is the technology to distribute data from the primary server to secondary servers. Replication works on Master-slave concept where Master database distributes data to one or multiple slave servers. Replication can be set up between multiple SQL Server instance on the same server, OR it can be set up between multiple database servers within same or geographically separated data centers. Read More

MERGE: Updating Source and Target Tables Located on Separate Servers

Nisarg Upadhyay
MERGE: Updating Source and Target Tables Located on Separate Servers
3.3 (65%) 4 vote[s]

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

How to create Snapshot Replication

Nisarg Upadhyay
How to create Snapshot Replication
3.3 (66.67%) 9 vote[s]

The concept of snapshot replication is simple. It generates and distributes the snapshot of schema and data of articles, appeared at a specific time and updates the changes on subscriptions. When we create snapshot replication, SQL Server creates a Snapshot Agent Job which generates a snapshot of publication database objects. Snapshot is stored on either network location or on the hard drive. You can define the interval to generate the snapshot using the SQL Job schedule. Read More

How to create Transactional Replication

Nisarg Upadhyay
How to create Transactional Replication
3.6 (72.5%) 8 vote[s]

In this article, I am going to demonstrate how to create transactional replication.

Transactional replication generated a snapshot of publication DB objects and data within it. After the snapshot is generated, all the data changes and schema changes occurred on the publisher database are delivered to subscriber databases. In transactional replication, data and schema changes are almost real-time, hence transactional replication can be used to offloading reports and sometimes can be used as DR Site.

Read More

Creating Database Maintenance Plans

Nisarg Upadhyay
Creating Database Maintenance Plans
4.1 (82.86%) 7 vote[s]

Using database maintenance plans, we can automate basic database administration activities. Maintenance plans are SQL Server integration service packages which are executed by SQL Server Agent job on configured schedule. Using maintenance plans, we can automate the following tasks.

  1. Shrink a database
  2. Backup a database
  3. Operator notification
  4. Update database statistics
  5. Perform database integrity check
  6. Execute SQL Server Agent jobs
  7. Execute T-SQL script
  8. Reorganize or rebuild indexes

Moreover, we can set up an alert to notify a DBA about the status of maintenance plan execution by enabling the Database Mail.

In this article, I am going to explain how to create a basic maintenance plan to perform database consistency check on a user database. Read More

Configuring Database Mail in SQL Server

Nisarg Upadhyay
Configuring Database Mail in SQL Server
3.8 (76.67%) 6 vote[s]

SQL Server Database Mail has been introduced in SQL Server 2005. Database Mail is a component that can send emails using SQL Server Engine. Using Database Mail, an administrator or a developer can send query output to an end user. DBAs can configure it to get email alerts and notifications. Database Mail uses SMTP (Simple Mail Transfer Protocol) to deliver emails to recipients.

In this article, I am going to demonstrate how to configure SQL Server Database Mail. Read More