Database Management Systems
In MySQL, prepared statements are a feature that allows us to execute the same SQL query multiple times with different parameters. They can improve performance, make our code cleaner, and help defend against SQL injection attacks.
In this article, we’ll explore prepared statements in MySQL, with examples and practical tips on how to use them effectively. We’ll also cover the importance of deallocating statements to free up resources.
Continue readingThe HANDLER statement in MySQL provides a low-level mechanism to directly access storage engine-level functionality, bypassing some of the overhead associated with regular SQL queries. It can be especially useful for traversing a database in high-performance scenarios.
HANDLER is available for MyISAM and InnoDB tables, and can be used as a faster alternative to the SELECT statement.
If you’re getting an error that reads “Invalid column name ‘$action’” in SQL Server, it could be that you’re using the $action argument of the OUTPUT clause for an operation that doesn’t support this argument.
Or it could be that the operation does support the argument, but you’re referencing it with the wrong syntax.
The $action argument can only be used with MERGE operations, and it must be done correctly.
To fix this issue, be sure to only use the $action argument in MERGE operations and to reference it with the correct syntax.
The MERGE statement in SQL Server allows us to perform INSERT, UPDATE, and DELETE operations in a single query. This makes it an efficient way to synchronize two tables, typically between a source and a target, based on a defined condition. Rather than running separate queries to handle insertions, updates, and deletions, we can combine all of these operations into one statement; the MERGE statement.
If you’re getting SQL Server error 2715, which reads something like “Column, parameter, or variable #1: Cannot find data type SERIAL“, it appears that you’re trying to define a column to have a data type that’s not supported in SQL Server.
To fix this issue, be sure to use a supported data type for all columns.
Continue readingMost relational database management systems support the TRUNCATE statement, and PostgreSQL is no exception.
That said, PostgreSQL has a few differences in the way its TRUNCATE statement works when compared to many other RDBMSs.
In this article, we’ll explore the various features of PostgreSQL’s implementation of the TRUNCATE statement, along with examples to demonstrate.
SQL rank functions can be handy things to have when analysing data. Most major RDBMSs implement a similar bunch of ranking functions, usually with the same names. These rank functions allow us to assign rankings to rows based on specific criteria.
In this article, we’ll look at six commonly used SQL ranking functions, and observe how they differ. We’ll throw them all together into a single query and see their results side by side.
The rank functions in question are: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), PERCENT_RANK(), and CUME_DIST().
If you’re getting SQL Server error 10713 that reads “A MERGE statement must be terminated by a semi-colon (;)“, it’s because you’re running a MERGE statement without terminating it with a semi-colon.
The MERGE statement requires a semi-colon at the end of it in order to correctly terminate the statement.
To fix this issue, put a semi-colon at the end of your MERGE statement.
When we run an UPDATE statement in SQL, we might not always be interested in which rows or values were updated. But there may be times when we need to examine this data, or log it somewhere like in a separate table.
This is where SQL Server’s OUTPUT clause can help tremendously.
When we have a column with composite data in PostgreSQL, we can access each field by using dot notation. This is true whether we select the data or update it. This means that we can use dot notation to update individual fields within a composite column.
And if we need to update all fields at once, we can use the ROW constructor.