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
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
SQL Server Database Mail has been introduced in SQL Server 2005. Database Mail is a component that can send emails using SQL Server Engine. Using Database Mail, an administrator or a developer can send query output to an end user. DBAs can configure it to get email alerts and notifications. Database Mail uses SMTP (Simple Mail Transfer Protocol) to deliver emails to recipients.
In this article, I am going to demonstrate how to configure SQL Server Database Mail. 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
This article is a walk-through of creating a stored procedure through test-driven database development (TDDD) in order to meet a reporting requirement that cannot be fulfilled by using a database view.
This article also provides some useful hints about cases of preferring stored procedure over database view as a potential object that is going to fulfill the business requirement(s). Read More
In my previous articles, I explained how to create and configure the FILESTREAM feature in SQL server instance. Moreover, I demonstrated how to create a table that has a FILESTREAM column and hot to insert and delete the data from it.
In this article, I am going to explain how to backup and restore the FILESTREAM-enabled dataase. Moreover, I am going to demonstrate how to restore FILESTREAM filegroup without making database offline. Read More
In my previous article, I described how to configure FILESTREAM in SQL Server, create FILESTREAM-enabled database and tables. Moreover, I demonstrated how to insert and delete data from the FILESTREAM table.
In this article, I am going to demonstrate how to insert multiple files in a FILESTREAM table using T-SQL. Read More
FILESTREAM has been introduced by Microsoft in 2008. The purpose was to store and manage unstructured files more effectively. Before FILESTREAM was introduced, the following approaches were used to store the data in SQL server:
Interview is the most significant step of SQL database administrator recruitment. During the interview, a technical person or interviewer asks questions about the technical experience of a candidate. The purpose of these questions is to understand candidate’s experience about SQL Server. The answers to the questions will measure your knowledge and experience. If you cannot prove your experience in this interview, most probably the decision on your employment will be negative. First of all, you should be calm during the interview. This advice is the most significant key to success. The first question of an interview is about your past experience and skills. Being relevant to this question, you should describe your old and current job responsibilities and skills. You must be sure that you demonstrate all of your knowledge and experience to the interviewer. Because this introduction makes the first impact on your potential employer. Read More