In this article, we will explore User-defined SQL Server Types and their usage.
Read more »
SQL commands
Learn SQL: SQL Injection
November 2, 2020SQL Injection is a well-known technique used to attack SQL-based applications. In this article, we’ll focus on examples showing how you could exploit database vulnerabilities using this technique, while in the next article we’ll talk about ways how you can protect your application from such attacks.
Read more »
Learn SQL: Non-Equi Joins in SQL Server
September 29, 2020Non-Equi join in SQL Server sounds like something abstract (and fancy), but it’s not so abstract (and fancy) at all. The same stands for equi joins. After reading this article, I hope you’ll agree on that with me. Today’ I’ll try to explain what they are and when you should use them. So, let’s start.
Read more »
Identify unused tables of SQL databases
September 24, 2020In this article, I am going to demonstrate how we can identify the unused tables of a given SQL Database. As a database administrator, we must maintain the tables and objects of the SQL Database. In my organization, when we add a new column or change the data type of a column, we export the data of the existing table in the backup table. Often, we forgot to review and maintain those backup tables. So as a solution, we decided to create a SQL Job that populates the list of unused tables and email the list to the DBA Team for review.
Read more »
Learn SQL: SQL-Related Jobs
September 1, 2020In today’s article, we’ll take a look at 4 common SQL-related jobs and roles and explain what kind of tasks you could expect at each of these roles and what types of queries you’ll possibly use to solve these tasks. If you’re already in any of these roles, you’re probably familiar with this, but if you’re new to the world of SQL, you could find this very useful to decide in which way you want your career to develop.
Read more »
T-SQL scripts to copy or remove files from a directory in SQL Server 2019
August 21, 2020Sometimes database professionals need to perform specific tasks at the operating system level. These tasks can be like copying, moving, deleting files and folders. A use case of these tasks might be removing the old backup files or copying backup files to a specific directory after a particular time. In SQL Server, we can use xp_cmdshell extended stored procedure to execute commands directly in the Windows command prompt(CMD). You need a sysadmin role or proxy account configured to use this extended procedure. We can also use the SSIS package for the file transfer, but it also requires you to build a package with the relevant tasks.
Read more »
Learn MySQL: Create and drop temp tables
August 18, 2020In this article, we are going to learn how to create a temp table and further drop these tables. The temporary tables are session-specific tables that are created within the session. When the session closes, the table is dropped by itself. A temporary table exists within a session only; therefore, a table created by one session is not visible to another session. For example, Session ID 501 has created a temporary table then session ID 502 cannot access it or manipulate it. Two different sessions can have a temporary table with the same name.
Read more »
Diving deep with complex Data Structures
July 29, 2020In my previous article, Understanding common Data Structures, I have mentioned the most commonly used data structures in software programming. In this article, let us get into some more details about the other data structures that are a bit complex than the ones already discussed but also used quite often while designing software applications. Here, we will look into the following data structures.
Read more »
Learn SQL: Naming Conventions
July 28, 2020A naming convention is a set of unwritten rules you should use if you want to increase the readability of the whole data model. Today, I’ll try to explain how you should formulate your naming convention and, maybe even more important, why should you do it and what is the overall benefit from using it.
Read more »
Understanding the SQL MERGE statement
July 27, 2020In this article, I am going to give a detailed explanation of how to use the SQL MERGE statement in SQL Server. The MERGE statement in SQL is a very popular clause that can handle inserts, updates, and deletes all in a single transaction without having to write separate logic for each of these. You can specify conditions on which you expect the MERGE statement to insert, update, or delete, etc.
Read more »
An introduction to SQL tables
July 17, 2020In this article, we will learn the concept of SQL tables and then work on how we can create tables with different techniques in SQL Server.
Read more »
Understanding common Data Structures
July 15, 2020In this article, I am going to walk you through the concepts of the common Data Structures that every student, colleague working with computers should be aware of. Data Structure forms an integral part of any system or database design. It is a very interesting and intuitive concept that you can apply anywhere. Through this article, I aim to introduce the beginners to the concepts of Data Structures and brush up the same for colleagues who have already been associated with the industry for years. This will also help you understand some database concepts more easily once you have a grasp over these concepts.
Read more »
SQL UPDATE syntax explained
July 10, 2020This article will get you familiar with the SQL UPDATE syntax and demonstrate how this statement can be used for modifying data using T-SQL. Data modification side of DML language in T-SQL includes three statements used for modifying data in SQL Server and those are: INSERT, UPDATE, and DELETE. The focus here will be on the UPDATE statement explicitly.
Read more »
Learn SQL: SQL Best Practices for Deleting and Updating data
July 7, 2020Deleting and updating data is very common, but if performed without taking care, which could lead to inconsistent data or data loss. Today, we’ll talk about SQL best practices when performing deletes and updates. We’re not talking about regular/expected changes, but rather about manual changes which will be required from time to time. So, let’s start.
Read more »
Explore the SQL query Hint OPTION (FAST N)
July 2, 2020In this article, we will introduce SQL queries hint and will analyze the OPTION(Fast ‘N’) hint in detail.
Read more »
SQL SUBSTRING function and its performance tips
June 30, 2020In this article, we will briefly explain the SUBSTRING function and then focus on performance tips about it.
Read more »
Learn SQL: SQL Server Cursors
June 22, 2020SQL Server cursors are one common topic on the Internet. You’ll find different opinions when to use them and when not to do it. Today, we’ll also talk about them and answer the question when (not) to use them.
Read more »
An overview of Logon triggers in SQL Server
June 19, 2020This article gives you an overview of Logon triggers in SQL Server and its usage to control SQL Server authentication.
Read more »
How to rollback using explicit SQL Server transactions
June 17, 2020In this article, we will explore the process of rollback an explicit SQL Server transaction. We will also explore the difference between explicit and implicit transactions.
Read more »
Learn SQL: Intro to SQL Server loops
June 15, 2020Loops are one of the most basic, still very powerful concepts in programming – the same stands for SQL Server loops. Today, we’ll give a brief info on how they function and introduce more complex concepts in upcoming articles of this series.
Read more »
SQL Server Choose() function introduction and examples
June 5, 2020In the article, a CASE statement in SQL, we explored one of the important logical expressions – the Case statement in SQL Server to return the values based on the specified condition. Today, we will talk about another useful logical Choose() function in SQL Server 2012 onwards and its comparison with the Case statement.
Read more »
Lever T-SQL for Pinpoint Control of ORDER BY in a Stored Procedure
June 1, 2020Introduction
The T-SQL ORDER BY clause sorts SQL Server SELECT statement result sets, and it becomes important when we build stored procedures. Unfortunately, the syntax offers no flexible way to directly control the ORDER BY clause behavior with argument values. This means we don’t have an easy way to control the specific column or columns that the ORDER BY clause sorts. Additionally, SQL Server does not offer a flexible way to directly control the ascending or descending order of any ORDER BY clause column with argument values. Of course, we can certainly hard-code the ORDER BY clause in a stored procedure, but this approach becomes fixed in stone. We could try a dynamic SQL solution, involving a stored procedure code that dynamically builds and executes SQL Server statements inside a stored procedure. However, this technique becomes tricky, and it can lead to SQL injection attacks. Other techniques might rely on CASE statements, and their complexity can become overwhelming as the column count grows. This article spotlights a clean, efficient, pinpoint T-SQL stored procedure technique that directly sorts one, some, or all SELECT statement result set columns. The technique avoids dynamic SQL, and it operates directly in a stored procedure. The article also shows how to set the ascending or descending sort order of specific columns.
Read more »
Learn SQL: SQL Server export to Excel
May 20, 2020In the previous article, Learn SQL: SQL Server Pivot Tables, we’ve discussed how to create a report using a PIVOT table query. We’ve created such a report directly in the SQL Server. In this article, we’ll do the same, but this time we’ll use SQL Server export to Excel. So, let’s start.
Read more »
Learn SQL: SQL Server Pivot Tables
May 11, 2020In the previous few articles in this series, we’ve set the foundations on how to create a report. We’ll take one step further and see how to use the SQL Server PIVOT table operator. We’ll start from the simple query and slowly progress towards dynamic SQL and PIVOT. Let’s start.
Read more »
Lever T-SQL to dynamically define duplicate SQL Server database table rows
May 11, 2020Introduction
Lever T-SQL to handle duplicate rows in SQL Server database tables article highlighted T-SQL features that detect and handle duplicate SQL Server table rows. The techniques work well, but they rely on fixed duplicate row definitions. This article extends those techniques, showing how to define duplicate rows in a dynamic way.
Read more »