The main idea of the SQL Server locking mechanism is that it controls the consistency of transactions. According to this principle, if a process wants to perform insert, delete or update operations, SQL Server engine locks the row or rows and does not allow another process until the transaction is completed. Under some circumstances, this locking mechanism can lead to performance problems such as high concurrent process pressures. So you can experience the deadlock (Deadlock is a concurrency problem in which two transactions want to access the same data simultaneously) problems in your database. In this article, we will focus on how to avoid lock issues with the help of NOLOCK hint. First, let’s learn the main essentials and details of dirty read methodology because the NOLOCK hint can cause dirty reading.
It is common knowledge in database circles that indexes improve query performance either by satisfying the required result set entirely (Covering Indexes) or acting as lookups which easily direct the Query Engine to the exact location of the required data set. However, as experienced DBAs know, one should not be too enthusiastic about creating indexes in OLTP environments without understanding the nature of the workload. Using Query Store in SQL Server 2019 instance (Query Store was introduced in SQL Server 2016), it is quite easy to show the effect of an index on inserts. Read More
The skills of writing different types of SQL Server queries require you to have good knowledge in the SQL Server T-SQL language. T-SQL stands for Transact Structure Query Language, which is a database procedural programming language that is extending the SQL language for Microsoft SQL Server RDBMS product. Read More
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
This article talks about using database schema snapshots to maintain different versions of a database to be deployed to different environments.
Database schema snapshots are point-in-time copies of the current state of the database which are normally used to reconcile the differences when deploying changes from one environment to another environment.
This article will be focused on a particular scenario where database schema snapshots are more than just point-in-time copies of the database rather they are used to create fresh versions of specific environments.
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
Introduction to SQL Server Indexes
Microsoft SQL Server is considered as one of the relational database management systems (RDBMS), in which the data is logically organized into rows and columns that are stored in data containers called tables. Physically, the tables are stored as 8 KB pages that can be organized into Heap or B-Tree Clustered tables. In the Heap table, there is no sorting order that controls the order of the data inside the data pages and the sequence of pages within that table, as there is no Clustered index defined on that table to enforce the sorting mechanism. If a Clustered index is defined on one column of the group of table columns, the data will be sorted inside the data pages based on the values of the Clustered index key columns, and the pages will be linked together based on these index key values. This sorted table is called a Clustered table.
This article discusses two different approaches available to remove duplicate rows from SQL table(s) which often becomes difficult over time as data grows if this is not done on time.
The presence of duplicate rows is a common issue that SQL developers and testers face from time to time, however, these duplicate rows do fall into a number of different categories that we are going to discuss in this article.
This article focuses on a specific scenario, when data inserted into a database table, leads to the introduction of duplicate records and then we will take a closer look at methods for removing duplicates and finally remove the duplicates using these methods.
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:
The database is a critical and vital part of any business or organization. The growing trends predict that 82% of enterprises expect the number of databases to increase over the next 12 months. A major challenge of every DBA is to discover how to tackle massive data growth, and this is going to be a most important goal. How can you increase database performance, lower costs, and eliminate downtime to give your users the best experience possible? Is data compression is an option? Let’s get started and see how some of the existing features can be useful to handle such situations.
In this article, we are going to learn how the data compression solution can help us optimize the data management solution. In this guide, we’ll cover the following topics:
- An overview of compression
- Benefits of compression
- An outline about data is compression techniques
- Discussion of various types of data compression
- Facts about data compression
- Implementation considerations
- and more…