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
Jump to Start Test-Driven Database Development (TDDD) – Part 3
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.
Test-driven database development (TDDD) Recap
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
Compare Execution Plans in SQL Server
Database Administrator always makes an effort to tune SQL Server query performance. The first step in tuning query performance is to analyze the execution plan of a query. Upon some conditions, SQL Server Query Optimizer can create different execution plans. At this point, I would like to add some notes about SQL Server Query Optimizer. SQL Server Query Optimizer is a cost-based optimizer that analyzes execution plans and decides the optimal execution plan for a query. The significant keyword for the SQL Server Query Optimizer is an optimal execution plan which is not necessarily the best execution plan. That’s why, if SQL Server Query Optimizer tries to find out the best execution plan for every query, it takes extra time and it causes damage to SQL Server Engine performance. Read More
Fixing Data Loss Using Log Shipping with Delayed Recovery
Introduction
Transaction Log Shipping is a very well-known technology used in SQL Server to maintain a copy of the live database in the Disaster Recovery Site. The technology depends on three key jobs: the Backup Job, the Copy Job, and the Restore Job. While the Backup job runs on the Primary Server, the Copy and Restore jobs run on the Secondary Server. Essentially the process involves periodic transaction log backups to a share from which the Copy Job moves same to the Secondary Server; subsequently, the Restore Job applies the log backups to the secondary server. Before all this starts, the Secondary Database must be initialized with a full backup from the Primary server restored with NORECOVERY option.
Export data from SQL Server to Excel and Text file via using SSIS package
While using the SSIS data flow, we can perform ETL task that can be used for data migration, data offloading, design and implementation of the data warehouse.
In this article, I explain how to export data from an SQL table to excel and text file by using the SSIS data flow task.
In this demo, I will perform through the following steps:
1. Create an SSIS package and create ADO.Net connection manager.
2. Create data flow task that has ADO.NET source to populate data from SQL Server. Data will be written to excel file and text file hence configure flat file destination and excel destination.
3. Execute SSIS package and verify that data was written to excel and text files.
Read More
Understanding Difference between EXCEPT and NOT IN Operator
Both EXCEPT and NOT IN operators are used to filter records from a table based on a specific criterion. In this article, we will look at these operators in detail and will investigate the differences between them. Read More
Structured Query Language – Importance of learning SQL
Computer programming language is a set of detailed instructions for computers or machines for performing specific actions. Through a programming language, we can control the behavior and output of a computer via accurate algorithms. A programming language is also called a computer language or programming system. The computer performs with various programming languages, such as SQL, Java, C++, Python, etc.. These languages allow computers and machines processing the large and complex data more effectively and quickly. That is the reason we find them valuable to organizations, particularly in the creation of system database management services. Read More
SQL Server IntelliSense and Autocomplete
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
Jump to Start Test-Driven Database Development (TDDD) – Part 2
We discussed the basics of test-driven database development (TDDD) with examples and compared it with traditional database development in the first part of this article.
In the second part, we are going to move beyond basics to focus on a more realistic scenario of meeting report requirements by using TDDD.
A quick recap of TDDD at this point is handy in understanding how it can help us to achieve such goals.
Exporting Oracle Table to Excel Worksheet
In this article, I will explain how we can divide and export data of an Oracle database table into multiple worksheets of an Excel file with help of the SQL Server integration service package.
Often, DBAs asked to generate Ad-Hoc reports from a database. Recently I have been asked to generate a report from the database. They want me to provide a report in multiple worksheets of an Excel file. There are different ways to do it but the query was resource-intensive. Hence I want to extract data from the database with one execution and decided to use the SQL Server integration services package. SQL Server data tools have one component called Conditional Split Transformation which can be used to achieve the task. Read More