How to Write T-SQL Queries Like a Pro

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

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

Using Working Folder to Source Control Database

Using Working Folder to Source Control Database
4.2 (83.33%) 6 votes

This article is a walkthrough of how to use the working folder option of source control for managing SQL Server databases.

In this article, I am also underlining some of the benefits and limitations of using a working folder as compared to other available options to use with source control.

Let us discuss some key concepts before delving into the technical details of this article. Read More

Generating Test Data in SQL Server

Generating Test Data in SQL Server
4 (80%) 9 votes

When testing the functionality of your application or the performance of a specific stored procedure or an ad-hoc query in the development environment, you need to have data stored in your development databases typical or similar to the data stored in the production databases. This is because the performance of a query that is processing 50 records will be different from the performance of the same query that is processing 50M rows. Restoring a copy of the production database to the development database server for testing purposes is not always a valid option, due to the critical data that is stored in these databases and should not be open for all employees to see, unless you are developing a new application and there is no production database yet.

The best and most secure alternative is to fill the development database tables with testing data. Test data generation is useful for testing the performance of the application or a new functionality without changing the production data. There is no single straight-forward way to generate test data that will fit all scenarios, especially when you need to generate large amount of data to test the performance of complex queries and transactions in which you should cover all possible combinations of testing cases. Read More

SQL Server IntelliSense and Autocomplete

SQL Server IntelliSense and Autocomplete
4.2 (84%) 10 votes

Starting from SQL Server 2008, Microsoft introduced a new feature in the SQL Server Management Studio that helps the database developers and the database administrators writing the T-SQL commands faster by reducing the typing effort and providing a quick access to the syntax information via listing all available database objects with their properties. This feature is called IntelliSense.
Read More

Different Ways to Compare SQL Server Tables Schema and Data

Different Ways to Compare SQL Server Tables Schema and Data
4.4 (88%) 10 votes

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

Jump to Start Test-Driven Database Development (TDDD) – Part 1

Jump to Start Test-Driven Database Development (TDDD) – Part 1
3.3 (66.67%) 3 votes

The most common approach to developing database solutions is to start creating database objects based on business requirements which is also known as “Conventional Database Development”.

In this article, we are going to explore the implementation of such approaches as conventional database development and test-driven database development on the particular examples.

To begin with, have a closer look at the conventional database development.

Read More

Getting Started with Mssql-cli Command-Line Query Tool

Getting Started with Mssql-cli Command-Line Query Tool
5 (100%) 1 vote

A recent announcement on the release of several SQL Server tools has raised expectations across various groups. Product requirements and business are almost always a trade-off, and striking the right balance in a product in terms of the toolset is a sign of a successful product. After testing the SQL Operations Studio, I feel that it’s a promising tool for many developers, administrators, and DevOps specialists. In my opinion, the mssql-cli tool adds another feature to SQL Server in order to make it a leading database product.

Read More

Art of Isolating Dependencies and Data in Database Unit Testing

Art of Isolating Dependencies and Data in Database Unit Testing
Rate this post

All the database developers more or less write database unit tests that not only help in detecting bugs early but also save a lot of time and efforts when the unexpected behavior of database objects becomes a production issue.

Nowadays, there are a number of database unit testing frameworks such as tSQLt along with third-party unit testing tools including dbForge Unit Test.

On the one hand, the benefit of using third-party testing tools is that the development team can instantly create and run unit tests with added features. Also, using a testing framework directly gives you more control over the unit tests. Therefore, you can add more functionality to the unit testing framework itself. However, in this case, your team must have time and a certain level of expertise to do this.

This article explores some standard practices that can help us to improve the way we write database unit tests.

Read More