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
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.
This article focuses on developing a basic understanding of how to use one of the most common Transact-SQL date functions: DATEADD, DATEDIFF, and DATEPART.
In this article, I also stressed the importance of properly using these date functions in daily date manipulations followed by some interesting scenarios in which these date functions can be used in a collaborative way to solve slightly complex date calculations.
Since these functions are primarily used in date manipulations, let us first try to understand what we mean by date manipulation.
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 concept of snapshot replication is simple. It generates and distributes the snapshot of schema and data of articles, appeared at a specific time and updates the changes on subscriptions. When we create snapshot replication, SQL Server creates a Snapshot Agent Job which generates a snapshot of publication database objects. Snapshot is stored on either network location or on the hard drive. You can define the interval to generate the snapshot using the SQL Job schedule. Read More
In this article, I am going to demonstrate how to create transactional replication.
Transactional replication generated a snapshot of publication DB objects and data within it. After the snapshot is generated, all the data changes and schema changes occurred on the publisher database are delivered to subscriber databases. In transactional replication, data and schema changes are almost real-time, hence transactional replication can be used to offloading reports and sometimes can be used as DR Site.
This article provides a walkthrough of database unit testing a stored procedure which contains a utility procedure within it.
In this article, I am going to discuss a database unit testing scenario when a main stored procedure depends on a utility procedure and the main procedure needs to be unit tested in order to make sure that the requirements are met. The key is to ensure that a unit test can only be written for a single unit of code which means we need one unit test for the main procedure and another unit test for the utility procedure. Read More
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
Real-world database applications need to make multiple requests from the front end to the database in order to carry out all sorts of functions.
If an application is data-intensive, such as the ones used in banks or airports etc, the number of data trips can be huge. Each request to a database utilizes bandwidth and requires time to execute. Without the table-valued parameters, a front application needs to make multiple data trips in order to manipulate multiple rows of data. However, with table-valued parameters, multiple rows can be inserted, updated and deleted from a database using a single parameterized command that takes a table-valued parameter. Read More
A database can have hundreds of thousands of records. It is easy to insert and select these records via database management systems like SQL Server or MySQL etc. However, it is not easy to display thousands of records on a single webpage or in a desktop application. Space and memory constraints make it difficult to display a huge number of records all at once.
A common solution to such a problem is to implement paging. (Note, this is not memory paging implemented by operating systems) Paging in programming refers to displaying data via a series of pages. A random Google search may result in thousands of results. Google uses paging to display these results. If you scroll down the Google page with search results you would see the following:
Here you can see the number of pages that the search result is divided into. You can click the Next link to see more pages.
In this article, we will see how OFFSET FETCH NEXT operators can be used to implement paging on front-end applications. We will begin with a simple example using the OFFSET FETCH NEXT operator and will then see how it can be practically used with the help of a stored procedure.