Recently we encountered an interesting performance problem on one of our SQL Server databases that process transactions at a serious rate. The transaction table used to capture these transactions became a hot table. As a result, the problem showed up in the application layer. It was an intermittent timeout of the session seeking to post transactions.
This happened because a session would typically “hold on” to the table and cause a series of spurious locks in the database.
Today’s article comes from a scenario we experienced in practice some time ago. We managed a card transaction system referred to as Postilion, and we had to update the data in a column containing IP addresses as part of the string with a new IP address. This was necessary because the IP address of the server hosting the solution would typically change due to switchover or the data restore to a UAT environment.
The data about the server was stored in the databases, and there was no way to update the same from the application without going row by row. Thus, we had to come up with an efficient solution using the LEN and SUBSTRING (or REPLACE) SQL Server functions.
The UPPER function is the SQL Server function that converts character expressions to upper case. It works when either an ad hoc string is passed to the function or a column name is passed.
The TRIM function of SQL Server is designed to remove leading and trailing spaces from a character string. A leading space is a space that occurs before the actual string. A trailing space occurs after.
There is a requirement in relational database systems that transactions must be durable. This is “D” in the ACID properties of transactions. The system must ensure that if a sudden crash happens, the transaction can be replayed. SQL Server fulfills this requirement by capturing all transactions in a physical file called a transaction log file.
In essence, every time a transaction is committed, SQL Server records changes produced by that transaction in a transaction log. Even if the transaction has not been persisted in the data file, it is available in the transaction log and can be replayed in the event of a sudden crash.
In database systems in general and in SQL Server in particular, functions are pieces of code that take zero or one input and return a single output or an array.
Table-Valued Functions which are typically user-defined can return an array, but in-built SQL Server functions are typically Scalar-Valued Functions. The third class of functions in SQL Server are Aggregate-Valued Functions. The MIN and MAX SQL Server functions are Aggregate-Valued Functions.
Read MoreSQL Server Express Edition is a free edition of SQL Server. Microsoft provides it for educational purposes as well as minor use cases. Students, hobbyists, and startups can use this edition to implement projects that do not require advanced features of SQL Server.
This article will examine the functionality of the SQL Server Express edition. But before we proceed to explore it, we’ll try to understand the concept of versions and editions in SQL Server.
Read MoreA table is a logical structure. When you create a table, you typically would not care which drives it sits on at the storage layer. However, if you are a database administrator, this knowledge may become essential if you need to move certain database portions to alternative storage or volume. Then, you might want definite tables to be on a particular volume or set of disks.
Filegroups in SQL Server offer that abstraction layer allowing us to control the physical location of our logical structures – tables, indexes, etc.
Read MoreDatafiles are physical objects that constitute the most important part of the database system since they contain actual data. You can think of a database as a collection of data files. An instance gives you the means of mounting and accessing such files.
Here, managing datafiles is understanding how to monitor and resize existing datafiles and how to add or remove the data files from a database.
Read MoreThe SQL Server MERGE statement is an incredibly useful tool for carrying out DML operations based on comparing two tables or two data sets. Usage of this statement is actually like performing multiple operations in a single statement.
This article will explore three use cases that border on ensuring data between an online table and a history table in sync.
Read More