What is the schema search path search_path? The manual: […] tables are often referred to by unqualified names, which consist of just the table name. The system determines which table is meant by following a search path, which is a list of schemas to look in. Bold emphasis mine. This explains identifier resolution. The “current … Read more
They don’t exist in MySQL do they? Just use a temp table: CREATE PROCEDURE my_proc () BEGIN CREATE TEMPORARY TABLE TempTable (myid int, myfield varchar(100)); INSERT INTO TempTable SELECT tblid, tblfield FROM Table1; /* Do some more stuff …. */ From MySQL here “You can use the TEMPORARY keyword when creating a table. A TEMPORARY … Read more
You have to create a temp table that mirrors the original table’s schema but with the column order that you want, then copy the contents of the original to temp. Delete the original and rename the temp. This is what SQL Management Studio does behind the scenes. With a schema sync tool, you can generate … Read more
You can use INFORMATION_SCHEMA.TABLES to retrieve information about your database tables. As mentioned in the Microsoft Tables Documentation: INFORMATION_SCHEMA.TABLES returns one row for each table in the current database for which the current user has permissions. The following query, therefore, will return the number of tables in the specified database: USE MyDatabase SELECT COUNT(*) FROM … Read more
Column order had a big performance impact on some of the databases I’ve tuned, spanning Sql Server, Oracle, and MySQL. This post has good rules of thumb: Primary key columns first Foreign key columns next. Frequently searched columns next Frequently updated columns later Nullable columns last. Least used nullable columns after more frequently used nullable … Read more
You cannot use the BACKUP DATABASE command to backup a single table, unless of course the table in question is allocated to its own FILEGROUP. What you can do, as you have suggested, is export the table data to a CSV file. Now in order to get the definition of your table you can ‘Script … Read more
Yes, SELECT COUNT(*) FROM TableName
Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax. To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty … Read more
Multiple tables help in the following ways / cases: (a) if different people are going to be developing applications involving different tables, it makes sense to split them. (b) If you want to give different kind of authorities to different people for different part of the data collection, it may be more convenient to split … Read more
Your understanding is correct. You would do this in many cases. One example is in a relationship like OrderHeader and OrderDetail. The PK in OrderHeader might be OrderNumber. The PK in OrderDetail might be OrderNumber AND LineNumber. If it was either of those two, it would not be unique, but the combination of the two … Read more