After reading this article, you will be able to the following:
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.
Performance monitoring and troubleshooting in SQL Server is a vast topic. In SQL Server 2005, dynamic management views, also known as DMV’s, have been introduced and became an essential helping tool for diagnosing SQL Server performance problems. At the same time, we can use dynamic management views for Azure SQL Database. Some of them can differ from SQL Server on-premise database but the logic of work is still the same. Microsoft has very good documentation about dynamic management views. The only thing, you need to be careful about the version and product validation of dynamic management views. Read More
As you know, the main responsibility of the database administrator lies in the monitoring of the SQL Server performance and intervening in determined time. You can find several SQL Server performance monitoring tools in the market but sometimes we need additional information about SQL Server performance to diagnosis and troubleshoot the performance issues. So we must have enough information about SQL Server Dynamic Management Views to handle issues about SQL Server.
Dynamic Management View (DMV) is a concept that helps us to discover SQL Server Engine performance metrics. DMV was first announced in SQL Server 2005 version and it continued in all versions of SQL Server afterward. In this post, we will talk about particular DMV whose database administrator must have enough information. This is sys.dm_os_wait_stats.
In the software programming world, there are several approaches used by developers that help them with effortless software development. Design patterns, object-oriented programming, test driven development are some of them. If we particularly mention the code reuse; “Reuse of a typed code. It is written once and can be used in most places. It is a basic concept of software engineering. It is a structure necessary for modular programming. ”
For this reason, user-defined functions in SQL Server help us to avoid rewriting of T-SQL queries. At the same time, they improve code readability. In this post, we will discuss the pros and cons (advantages and disadvantages) of the traditional and natively compiled scalar user-defined functions and make a simple performance test.
When we export data from SQL server table to excel file by using SQL Server integration service package, data in excel file column exports into text format. Even though the column values are Integer or Decimal, it is stored in text format. For example, I am retrieving the values of “CreditLimit” column from the product table. Data format changes automatically and stores in text format in excel file.
This article is intended for database administrators and system administrators who plan to configure the DBFS FUSE file-system on a Linux machine using the Docker containers. I would recommend reading the previous articles “Introduction to Docker and Docker toolbox” and “Docker and SQL Server on Linux”. This article assumes that the reader has a rough knowledge of the Docker Containers and SQL Server. Read More
The RANK, DENSE_RANK and ROW_NUMBER functions are used to retrieve an increasing integer value. They start with a value based on the condition imposed by the ORDER BY clause. All of these functions require the ORDER BY clause to function properly. In case of partitioned data, the integer counter is reset to 1 for each partition.
In this article, we will study the RANK, DENSE_RANK and ROW_NUMBER functions in detail, but before that, let’s create dummy data that these functions can be used on unless your database is fully backed up. Read More
In this article, I have explained how we can divide and export data of SQL Server database table into Oracle and MySQL databases. That process is based on the condition defined in SSIS as “conditional split transformation”. Conditional split transformation is like case statement of any programming language. Using conditional, we can redirect the output of a component in multiple destinations that is based on a condition defined in the conditional split component. Read More
This article is a walk-through of creating a report base on a database object developed and tested by using test-driven development (TDDD). Furthermore, some tips for improving database unit testing via TDDD will be discussed in this article too.
In simple words, TDDD is all about writing unit-test before the database object is even created. So, if we want to create a database object to satisfy some business requirement, it should be processed by creating a unit-test that ensures the object exists. Moreover, it has to be followed by another unit-test that will ensure the proper functioning of the database object (at the minimum) though that functioning check should only be limited to meet only the desired requirement. Read More