Database backups, integrity checks, and performance optimizations are core regular tasks of DBAs. The client data is very important for a DBA to manage the database backup and make sure the integrity of the backups. So if something goes wrong with a production database, it can be recovered with minimum downtime. The database integrity checks are also important because, in the case of database corruption, it can be corrected with minimum downtime and data loss. Managing database performance is also important. Managing database performance is a combination of multiple tasks. Read More
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.
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:
- When a new index is created on a column.
- 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
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:
- Convert delimited string into XML, use XQuery to split the string, and save it into the table.
- Create a user-defined table-valued function to split the string and insert it into the table.
- Split the string using STRING_SPLIT function and insert the output into a table. Read More
T-SQL SET Operators Part 2: INTERSECT and EXCEPT
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:
- EXCEPT and INTERSECT operators.
- Difference between INTERSECT and INNER JOIN.
- 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
Advanced SQL: Variations and Different Use cases of T-SQL Insert Statement
In my previous article, I demonstrated:
- Insert the output of the table-valued function in the SQL table.
- Insert the output of the table-valued function that is created on the remote database server.
In this article, I am going to demonstrate:
- Copy data between two tables, created in a different schema.
- Copy data between two tables, created in different databases on the same server.
- Copy data between two tables created, in different databases resides the different server (Cross server query)
T-SQL SET Operators Part 1: UNION and UNION ALL
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:
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
To use SET operators, we must follow a number of rules:
- The result set of both queries must have the same number of columns.
- The data type of columns retrieved by the top and bottom queries must be the same.
- If we want to sort the final result set, the ORDER BY clause must be at the end of the query.
- 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
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.
Advanced SQL: Insert output of the parameterized table-valued function in SQL table
In this article, I am going to demonstrate the following:
- How to insert the output of a table-valued function in a SQL table.
- 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
Auto-Email System to Send Database Summary Report
Database monitoring is the most essential job of any database administrator. Big organizations and companies have multiple database servers that are located either in the same data center or in the geographically different data centers. Read More
SQL Server Database Replication
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