Different Ways to Compare SQL Server Tables Schema and Data

SQL Server provides us with different solutions to replicate or archive a database table or tables to another database, or the same database with different names. As an SQL Server Developer or Database Administrator, you may face situations when you need to check that the data in these two tables are identical, and if, by mistake, the data is not replicated between these two tables, you need to synchronize the data between the tables. In addition, if you receive an error message, that breaks the data synchronization or replication process, due to schema differences between the source and destination tables, you need to find an easy and fast way to identify the schema differences, ALTER the tables to make the schema identical in both sides and resume the data synchronization process. Read More

Identifying and Fixing Forwarded Records Performance Issue

Before going through the Forwarded Records performance issue and resolving it, we need to review the structure of the SQL Server tables.

Table Structure Overview

In SQL Server, the fundamental unit of the data storage is the 8-KB Pages. Each page starts with a 96-byte header that stores the system information about that page. Then, the table rows will be stored on the data pages serially after the header. At the end of the page, the row offset table, that contains one entry for each row, will be stored opposite to the sequence of the rows in the page. This row offset entry shows how far the first byte of that row is located from the start of the page.

Read More

Microsoft SQL Operations Studio: Configuration and Querying

In my previous article, Microsoft SQL Operations Studio: Understanding and Installation, we discussed deeply the need for a new SQL Server development and administration graphical user interface tool that can replace the default SQL Server user interface tool, the SQL Server Management Studio. We discussed deeply the new features and the advantages of SQL Operations Studio over the SQL Server Management Studio tool and how to download and install it to the Windows machine.

In this article, we will see the different configuration of the SQL Operations Window and how we can perform our daily tasks with it.

Read More

Microsoft SQL Operations Studio: Understanding and Installation

SQL Server Management Studio is considered as the default integrated graphical user interface tool that has been used for many years to configure, manage, monitor and administrate the SQL Server instances hosted on the local machines, on remote servers or in the cloud by all SQL Server administrators and developers. It provides us with editing, debugging and deploying environment for many languages including T-SQL, XML, MDX and DMX languages. Due to the fact that Microsoft SQL Server can be installed now on the Linux platform, and that the SQL Server Management Studio tool is not compatible with any operating system outside Microsoft Windows, the need for a new cross-platform graphical user interface appears.

Read More

SQL Server Triggers: Understanding and Alternatives

The SQL Server trigger is a special type of stored procedures that is automatically executed when an event occurs in a specific database server. SQL Server provides us with two main types of triggers: the DML Triggers and the DDL triggers. The DDL triggers will be fired in response to different Data Definition Language (DDL) events, such as executing CREATE, ALTER, DROP, GRANT, DENY, and REVOKE T-SQL statements. The DDL trigger can respond to the DDL actions by preventing these changes from affecting the database, perform another action in response to these DDL actions or recording these changes that are executed against the database. Read More

SQL Server Index Backward Scan: Understanding, Tuning

Table indexing strategy is one of the most important performance tuning and optimization keys. In SQL Server, the indexes (both, clustered and nonclustered) are created using a B-tree structure, in which each page acts as a doubly linked list node, having an information about the previous and the next pages. This B-tree structure, called Forward Scan,  makes it easier to read the rows from the index by scanning or seeking its pages from the beginning to the end. Although the forward scan is the default and heavily known index scanning method, SQL Server provides us with the ability to scan the index rows within the B-tree structure from the end to the beginning. This ability is called the Backward Scan. In this article, we will see how this happens and what are the pros and cons of the Backward scanning method. Read More

Methods to Rank Rows in SQL Server: ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE()

SQL Server provides us with a number of window functions that help us to perform calculations across a set of rows, without the need to repeat the calls to the database. Unlike the standard aggregate functions, the window functions will not group the rows into a single output row, they will return a single aggregated value for each row, keeping the separate identities for those rows. The Window term here is not related to the Microsoft Windows operating system, it describes the set of rows that the function will process.

Read More