Author: {coding}Sight

A community platform for IT specialists

How to Automate the Process of SQL Server Database Schema Synchronization

{coding}Sight
How to Automate the Process of SQL Server Database Schema Synchronization
2.5 (50%) 2 vote[s]

The goal

Keeping two databases in sync is a task we often encounter when working on SQL database development and maintenance. One particular case is this – the versions of a database present on testing and development environments need to be consistently synchronized to make sure that tests are running on the most recent version. This should be done by detecting schema changes in the development version of a database and then automatically synchronizing them with the version from the testing environment on a scheduled basis.

How to achieve it

We’ll look into how automated synchronization of two SQL databases can be achieved by combining a schema comparison tool with PowerShell scripts and scheduling. Read More

Counting references to a record in a table via Foreign Keys

{coding}Sight
Counting references to a record in a table via Foreign Keys
3.7 (73.33%) 3 vote[s]

I have recently needed to solve the task for my own purpose: to calculate the number of external records linked by a foreign key for each record in a table (File). The task was solved for the specific structure of the File table, but if necessary, the solution can be reworked to a universal one.

I’ll clarify that the solution was developed for an unloaded database, without millions of records and an every minute update, so there was not much concern about the performance.

Read More

XML performance tips

{coding}Sight
XML performance tips
3.8 (75%) 4 vote[s]

Parsing data from XML using XQuery is a routine practice. In order to do this most effectively, little effort is required.

Suppose we need to parse data from the disk file with the following structure:

Use BULK INSERT, if you need to read data from a file:

Read More