In database management systems (DBMSs) such as SQLite, indexes can be used to optimize the performance of queries, especially for larger datasets where querying can become time-consuming. An index allows the database to locate rows more quickly without scanning every row in a table.
This article explains how to create an index in SQLite, along with a simple example.
Continue readingThe good thing about having IDENTITY columns in SQL Server is that they automatically generate a unique value for every row that’s inserted into a table. This saves us from having to insert our own unique values, and I’m not going to complain about that.
But obvious question you might ask; What if I need to know the value that was generated for each column I just inserted?
Fortunately, the solution may be easier than you think!
Continue readingIf you’re getting SQL Server error 137 that goes something like “Must declare the scalar variable…“, it’s probably because you’re referring to a variable that hasn’t been declared.
If the variable has been declared, it’s possible you’re referring to it incorrectly in the code.
When we use a variable in SQL Server, we must declare the variable first.
To fix this issue, declare the variable. Also be sure to use the right syntax when using it.
Continue readingIf you’re getting MySQL error 1066 which reads something like “1066 (42000): Not unique table/alias: ‘products’” when using the HANDLER statement in MySQL, it could be that you’re trying to open a table that’s already open.
If this is the case, be sure to close the table before trying to open it again. Or simply continue working without opening the table again.
Continue readingMySQL provides us with the ability to create server-side prepared statements, which are precompiled SQL queries that can be executed multiple times with different arguments.
We can use the performance schema to return a list of all prepared statements in the server, along with useful information about each prepared statement.
Continue readingIf you’re getting an error that reads something like “1052 (23000): Column ‘name’ in field list is ambiguous” in MySQL, it looks like you could be referencing a column name in a query without qualifying it with the table name.
This can happen when you perform a join between tables that use the same name for one or more columns.
To fix this issue, be sure to qualify column names with the table names when performing joins across tables.
Continue readingAny time you prepare to delete data from a database, you will probably wonder whether or not to log the deleted data somewhere… just in case.
One way to do this is to construct a SELECT statement to identify the rows you want to delete, and then use it to copy those rows into a separate log table, before doing the actual delete. If you’re using SQL Server, another option is to use the OUTPUT clause. This article discusses the later.
MySQL provides us with the ability to create multiple prepared statements and run them as many times as we like, while changing the parameter values with each run.
While prepared statements are only available to the session that created them, they are stored in the server. So it’s quite possible that a server could have a build up of lots of prepared statements hanging around once different users have created them.
Continue readingThere are many ways to find the primary key column in SQL Server, but sometimes we might need to identify the name of the primary key’s index.
For example, we may want to identify the index’s name if we need to disable the primary key for some reason (we disable primary keys by disabling their index).
The query below can help if you need to find the name of a primary key’s index for a given table.
Continue readingIf you’re getting an error that reads “Syntax ‘*’ is not allowed in schema-bound objects” in SQL Server, it appears that you’re using the asterisk wildcard (*) to select all columns when trying to define a schema-bound object (like a schema-bound view).
As the error message states, this is not allowed.
To fix this issue, explicitly name each column.
Continue reading