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
Category: SQL Server
Creating Customer-Focused SSRS Reports with Parameters
This article talks about how to create a basic customer-focused report using SQL Server Reporting Services (SSRS).
The article also highlights the importance of utilizing the full potential of the SSRS reporting technology by using parameters in SSRS reports – for example, to meet business specifications.
The main focus of this article is to prepare the readers who already have a T-SQL background to create SSRS reports for customers. In this way, the customers can run these reports based on their desired input values rather than on default values provided by report developers. Read More
How to Document Your SQL Server Database
The process of documenting a SQL Server database is a complete and continuous process that should start during the database design and development phases and continue during all database related life cycles in a way that ensures having an up-to-date version of the database documentation that reflects reality at any point in time. If performed properly, the generated database documentation file will contain an up to date and complete list for the database objects and a brief description for these database objects. Read More
Parameter Sniffing Primer
Introduction
Developers are often told to use stored procedures in order to avoid the so-called ad hoc queries which can result in unnecessary bloating of the plan cache. You see, when recurrent SQL code is written inconsistently or when there’s code that generates dynamic SQL on the fly, SQL Server has a tendency to create an execution plan for each individual execution. This may decrease overall performance by:
-
Demanding a compilation phase for every code execution.
-
Bloating the Plan Cache with too many plan handles that may not be reused.
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
SSRS Reports Development in Simple Terms
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.
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
Learn Basic Data Analysis with SQL Window Functions
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
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
How to Properly Use the T-SQL IsNumeric Function
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