A regular expression is a rule which defines how characters can appear in an expression. It’s a sequence of character or text which determines the search pattern. In SQL databases, selecting values based on regular expressions defined in the WHERE condition can be very useful. Following are a few use cases of how you can use regular expressions. Read More
Category: SQL Server
Switching Out Table Partitions: A Walkthrough
Introduction
A few years ago we were tasked with a business requirement for card data in a specific format for the purpose of something called “reconciliation”. The idea was to present the data in a table to an application which would consume and process the data which would have a retention period of six months. We had to create a new database for this business need and then create the core table as a partitioned table. The process described here is the process we use to ensure that data older than six months is moved out of the table in a clean fashion. Read More
Creating Professional SSRS Report based on Stored Procedure
This article talks about creating a professional SSRS report based on a stored procedure to meet the business specification.
Additionally, the readers of this article will learn how to implement the report procedure correctly to run a report according to its frequency. Read More
AlwaysOn Availability Groups: Quorum
SQL Server AlwaysOn Availability Groups is Microsoft’s latest technology for addressing the High Availability and Disaster Recovery needs of organizations using SQL Server. One big advantage of AlwaysOn is the ability to address both HA and DR in one implementation. The key benefits of AlwaysOn that we have experienced are as follows: Read More
How to Write Stored Procedures for Professional SSRS Reports
In this article, we’ll talk about professional SQL Server Reporting Services reports, how to create stored procedures for these reports to meet business requirements, and the benefits of using stored procedures to run behind SSRS reports.
About Professional SSRS Reports
Let’s first get familiar with professional SSRS reports.
Simple Definition
A professional SSRS report is a report created with the standard practices of report development in mind and which has been thoroughly tested to be capable of fulfilling business requirements.
In other words, a professional SSRS report is very carefully designed, developed, tested, and deployed to the target environment to make sure it serves the purpose and is beneficial to the business. Read More
Calculate the median by using Transact SQL
The statistical median is the value which separates a dataset into halves – one comprises greater values, and the other comprises lesser ones. For a specified dataset, it can be considered as the “middle” value. For example, in the dataset {1, 3, 3, 4, 5, 6, 7, 8, 9}, the median is 5, which is fourth largest, and fourth smallest number in the dataset.
To calculate the median of any dataset, we first need to arrange all values from the dataset in a specific order. After arranging the data, we must determine the middle value of the specified dataset. If the dataset contains an odd number of values, than the middle value of the entire dataset will be considered as a median. Read More
Database Normalization: A Primer
The Relational Model of data management was first developed by Dr. Edgar F. Codd in 1969. Modern relational database management systems (RDBMSes) are aligned with the paradigm. The key structure identified with RDBMS is the logical structure called a “table”. Tables are primarily composed of rows and columns (also called records and attributes or tuples and fields). In a strict mathematical sense, the term table is actually referred to as a relation and accounts for the term “Relational Model”. In mathematics, a relation is a representation of a set.
The expression attribute gives a good description of the purpose of a column – it characterizes the set of rows associated with it. Each column must be of a particular data type and each row must have some unique identifying characteristics called “keys”. Data change is typically more efficient when done using the relational model while data retrieval may be faster with the older Hierarchical Model which has been redefined in model NoSQL systems. Read More
Understanding SQL Server Always Encrypted
Security is one of the most important requirements for a data-driven system. Encryption is one of the ways to secure the data. Wikipedia defines encryption as:
“Encryption is the process of encoding a message or information in such a way that only authorized parties can access it and those who are not authorized cannot.”
In SQL Server 2016, Microsoft introduced an encryption feature called Always Encrypted. In this article, we will see what Always Encrypted is, and how it can be used to encrypt and decrypt data, with the help of simple examples.
Update SQL Server statistics using a database maintenance plan
Database backups, integrity checks, and performance optimizations are core regular tasks of DBAs. The client data is very important for a DBA to manage the database backup and make sure the integrity of the backups. So if something goes wrong with a production database, it can be recovered with minimum downtime. The database integrity checks are also important because, in the case of database corruption, it can be corrected with minimum downtime and data loss. Managing database performance is also important. Managing database performance is a combination of multiple tasks. Read More
Creating Customer-Focused SSRS Reports with Parameters
This article talks about how to create a basic customer-focused report using SQL Server Reporting Services (SSRS).
The article also highlights the importance of utilizing the full potential of the SSRS reporting technology by using parameters in SSRS reports – for example, to meet business specifications.
The main focus of this article is to prepare the readers who already have a T-SQL background to create SSRS reports for customers. In this way, the customers can run these reports based on their desired input values rather than on default values provided by report developers. Read More