SQL

Big data has significantly evolved since its inception in the late 2000s. Many organizations quickly adapted to the trend and built their big data platforms using open-source tools like Apache Hadoop. Later, these companies started facing trouble managing the rapidly evolving data processing needs. They have faced challenges handling schema level changes, partition scheme evolution, and going back in time to look at the data.  I faced similar challenges while designing large-scale distributed systems back in the 2010s for a…

SQL stored procedures are sets of SQL statements saved and stored in a database. They can be executed on demand to perform data manipulation and validation tasks, reducing the need to write repetitive SQL code for common operations. Stored procedures are helpful in database management by promoting efficiency and reusability. In addition, they support enhanced database security and maintainability. In this article, we will discuss how to create and execute SQL stored procedures, common use cases, and best practices. As…

When working with databases, it’s common to encounter issues like redundant data and inconsistent updates. Second normal form is a database normalization step that builds on first normal form (1NF) to create cleaner and more efficient tables.  Understanding 2NF is critical for anyone working in database design or data management, and it lays the foundation for higher normalization forms like third normal form (3NF). In this article, we’ll explore how 2NF works and how to transform tables to meet 2NF…

Master Database File (MDF) is the primary database file in MS SQL Server that stores all the data, including views, tables, stored procedures, foreign keys, and primary keys. Sometimes, while opening the MDF file, you may face an issue where the SQL Server database cannot be opened and encounter an error, like the one given below: SQL Server error 5171: MDF is not a primary database file Unable to open the file xxxxx.mdf. Operating system error 5: (Access is denied)”…

Understanding DuckDB for Data Privacy and Security Data privacy and security have become critical for all organizations across the globe. Organizations often need to identify, mask, or remove sensitive information from their datasets while maintaining data utility. This article explores how to leverage DuckDB, an in-process analytical database, for efficient sensitive data remediation. Why DuckDB? (And Why Should You Care?) Think of DuckDB as SQLite‘s analytically gifted cousin. It’s an embedded database that runs right in your process, but it’s…

This exciting release of Boolean Data Type Lab for Oracle Database 23c AI introduces native support for the boolean data type, a game-changer for database developers. With this enhancement, you can natively represent true, false, and null values, simplifying data modeling and making SQL and PL/SQL development more efficient. To illustrate, let’s recreate a scenario involving a Student Information System, where we’ll track whether students are currently enrolled in courses using the new boolean data type. This feature enhances developer…

Managing orphaned users in SQL Server is critical to database security and administration. Orphaned users occur when a database user exists without a corresponding login in the master database. This mismatch can disrupt access and lead to potential security vulnerabilities. In this article, we’ll explore what orphaned users are, how to detect them, and methods to resolve them, including updated examples and scenarios. Background To connect to an SQL Server database, a user must have a valid login in the…

SQL Server databases occasionally enter “In Recovery” mode, which can often catch database administrators off guard. This status occurs during a restart, database restore, or unexpected shutdown, as SQL Server replays or undoes incomplete transactions to maintain data integrity. While this process is typically automatic, it can sometimes take longer than expected — or even appear stuck — leaving administrators unsure of how to proceed. If you’ve encountered this issue, don’t worry. This article will help you understand what’s happening…

Asentinel-orm is a lightweight ORM tool built on top of Spring JDBC, particularly JdbcTemplate.  Thus, it possesses most of the features one would expect from a basic ORM, such as SQL generation, lazy loading, etc. By leveraging the JdbcTemplate, it means it allows participation in Spring-managed transactions, and it can be easily integrated into any project that already uses JdbcTemplate as a means to interact with the database. Since 2015, asentinel-orm has been successfully used in several applications and continually improved as…