Tag: sql server

Basics of Running T-SQL Statements from Command Line using SQLCMD

Haroon Ashraf
Basics of Running T-SQL Statements from Command Line using SQLCMD
3 (60%) 1 vote

This article is about developing a basic understanding of sqlcmd utility to run T-SQL commands directly from the command prompt without the need of SSMS (SQL Server Management Studio).

The article also highlights the importance of using a lightweight sqlcmd utility to perform some basic database tasks that would otherwise require getting connected to the database through a pre-installed database tool such as SSMS (SQL Server Management Studio) or SSDT (SQL Server Data Tools) followed by getting it ready to run SQL scripts against the desired database(s).

The sqlcmd utility can be a real time-saver for database developers and DBAs since they can straight away run the required SQL scripts from the command-line.

Read More

Essentials and usage of NOLOCK hint in SQL Server

Esat Erkeç
Essentials and usage of NOLOCK hint in SQL Server
4.3 (86.67%) 3 votes

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.

Read More

Query Store: Showing the Impact of Indexes on Inserts

Kenneth Igiri
Query Store: Showing the Impact of Indexes on Inserts
3.8 (75%) 4 votes

Introduction

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

How to Write T-SQL Queries Like a Pro

Ahmad Yaseen
How to Write T-SQL Queries Like a Pro
5 (100%) 5 votes

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

Creating and Deploying Multiple Versions of Database through Schema Snapshots

Haroon Ashraf
Creating and Deploying Multiple Versions of Database through Schema Snapshots
5 (100%) 1 vote

Overview

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.

Read More

SQL Server Database Replication

Nisarg Upadhyay
SQL Server Database Replication
3.8 (75%) 4 votes

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

How to Proactively Gather SQL Server Indexes Fragmentation Information

Ahmad Yaseen
How to Proactively Gather SQL Server Indexes Fragmentation Information
4.2 (83.33%) 6 votes

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.

Read More

Multiple Ways to Remove Duplicates from SQL Tables

Haroon Ashraf
Multiple Ways to Remove Duplicates from SQL Tables
3.8 (76%) 5 votes

Overview

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.

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.7 (73.33%) 3 votes

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