SQL Server Section

Introduction With SQL databases, a frequent task is to insert a record into a table that includes an Identity Field. Following this insertion, retrieving this newly generated ID for subsequent operations becomes necessary. Identity Field Importance Firstly, this process is important in scenarios where maintaining a track of records and establishing links between them is required. For instance, in user management systems, it’s crucial to keep track of each user’s unique ID after their information is entered into the database. This ID then serves as a key reference for all future interactions and transactions related to that user. The ability…

Introduction SQL fat pipes are a performance issue that can occur in SQL Server databases. They occur when a query scans or seeks a large number of rows from a table, causing the query to take a long time to execute. Symptoms of SQL fat pipes A few symptoms can indicate that you have a SQL fat pipe issue. These include: Long query execution times High CPU usage High memory usage High I/O wait times How to identify SQL fat pipes To identify SQL fat pipes, you can use the SQL Server EXPLAIN PLAN statement. This statement will show you…

Introduction A common and useful feature in SQL is the ability to use a “SELECT FROM a SELECT” query, often referred to as a subquery or inner query. This post aims to explain this concept, showing how it can be both powerful and practical for complex data retrieval tasks. What is a “SELECT FROM a SELECT” Query? A “SELECT FROM a SELECT” query, or subquery, is a SQL technique where an inner SELECT statement is nested within an outer SELECT statement. This allows for more complex queries than can be achieved with a single SELECT statement. Why Use Subqueries? Complex…

Introduction The SQL LIKE command is used in a WHERE clause to search for a specified pattern in a column. It’s commonly used in string comparison to find matches in text data within the database. There are two wildcards used with LIKE: %: Represents zero, one, or multiple characters. _: Represents a single character. Example of an SQL Query Using LIKE Let’s consider a complex SQL example using LIKE with comments explaining each part: — Query to find employees with specific name patterns and ordering results SELECT EmployeeID, — Selects the 'EmployeeID' column Name, — Selects the 'Name' column Department,…

Introduction As a SQL Server professional, you’re likely familiar with the concept of database deployment and the tools that facilitate it. Among these tools, SqlPackage.exe is a versatile command-line utility that enables you to manage database objects, automate deployment processes, and streamline your data management workflow. What is SqlPackage.exe? SqlPackage.exe, formerly known as SSDT (SQL Server Data Tools) Package Manager, is an essential tool for SQL Server developers and administrators. It enables you to create, manage, and deploy database objects, including tables, views, stored procedures, and user-defined functions, to SQL Server instances. Why Use SqlPackage.exe? The benefits of using SqlPackage.exe…

Introduction JSON, or JavaScript Object Notation, has become the de facto standard for data interchange. Its flexibility and human-readable format make it ideal for representing complex data structures. However, integrating JSON data into traditional relational databases can be challenging. SQL Server, the leading relational database management system (RDBMS), has introduced native support for JSON data, allowing developers to seamlessly store, manipulate, and query JSON data alongside their existing relational data. This integration empowers developers to leverage the strengths of both JSON and SQL for their applications. Storing JSON Data in SQL Server SQL Server stores JSON data in specialised NVARCHAR…

Introduction In SQL, Common Table Expressions (CTEs) are a powerful tool for structuring complex queries and enhancing data analysis. Introduced in the SQL:1999 standard, CTEs have gained immense popularity among developers due to their versatility and ease of use. What are CTEs? CTEs, or Temporary Table Expressions, have emerged as a fundamental tool in the repertoire of SQL developers. Their primary function is to break down intricate SQL queries into smaller, self-contained units, effectively transforming complex operations into a series of manageable steps. This approach enhances the readability and maintainability of queries and promotes modular programming, making it easier to…

Introduction In database administration, housekeeping routines play a critical role in maintaining the optimal performance and integrity of databases. These routines involve tasks such as optimizing tables, defragmenting indexes, and purging obsolete data. However, executing these tasks while active database connections exist can lead to performance degradation and potential data corruption. To address this concern, it’s crucial to terminate idle or unnecessary connections to the database before initiating housekeeping routines. Motivating Factors for Connection Termination Terminating idle or unnecessary connections to a database before running housekeeping routines offers several compelling advantages: Reduced Database Load: Active connections consume valuable database resources to…

Introduction In many cases, it becomes necessary to move SQL Server log files to a different location. This can be done for various reasons, such as optimizing disk space utilization or ensuring data security. There are two primary methods for moving SQL Server log files: Using SQL Server Management Studio (SSMS) Using SQL Server Transact-SQL (T-SQL) commands In this article, we’ll explore both methods and provide step-by-step instructions for each. Method 1: Using SQL Server Management Studio (SSMS) Launch SSMS and connect to the SQL Server instance containing the database whose log files you want to move. Expand the Databases…

Introduction In any SQL Server instance, user logins play a crucial role in controlling access to databases and granting permissions to perform specific actions. However, as databases evolve and user roles change, maintaining accurate and consistent user login mappings can become a challenge. In my workplace, we have a lot of test databases. We have to fix up SQL user access on our development machines all of the time. Since Lock-Down and with our move to a hybrid working environment, our developers can’t just connect to those databases across a fast network connection and we have to copy the databases…