Hello database experts,
In SQL Server is an option to update statistics before detaching a database, both using Transact-SQL and management studio wizard.
The purpose and advantage of updating statistics is clear.
My question is that, why should we update statistics before detaching a database? Is there any performance issue in recovery of database while attaching? or something else?
My query to get a ranked leaderboard works on MYSQL 5.7 but gives me errors now that I upgraded to MYSQL 8.0
mysql> SELECT username,score,level, FIND_IN_SET(score,(SELECT GROUP_CONCAT(score ORDER BY score ASC)FROM highscore WHERE game = 0 AND level = 4 AND user = 10 )) AS rank FROM highscore WHERE game = 0 AND level = 4 AND user = 1 ORDER BY rank LIMIT 10;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax
to use near 'rank FROM highscore WHERE game = 0 AND level = 4 AND user = 1 ORDER BY rank LIMI' at line 1
I am familiar with SQL Server Activity Monitor. What all I know about it, have been learned by my own experinced. I decided to learn all details about it, and googled many keywords but most of results were a brief. I did not find a comprehensive article or document in detail.
Can anyone send me a well documented link?
For example the task states vary in different articles; Or what head blocker value is (the concept is clear but not the value; I thouth it is SPID but is not)?
After some extra research I found a complete list of state from different sources:
Running - The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS). Background - The session is running a background task, such as deadlock detection. Rollback - The session has a transaction rollback in the process. Pending - The session is waiting for a worker thread to become available. Runnable - The session's task is in the runnable queue of a scheduler while waiting to get a time quantum. Spinloop - The session's task is waiting for a spinlock to become free. Suspended - The session is waiting for an event, such as I/O, to complete. Sleeping - A session in the sleeping state means a client connection without an active query. Dormant - SQL Server is resetting the session. Same as Sleeping, except Dormant also indicates that the SPID has been reset after completing an RPC event. (Replication SPIDs show "DORMANT" when waiting.)
Get create table statements for a database under a schema.
SELECT TABLE_NAME, 'CREATE TABLE '||TABLE_NAME|| ' ('||STRING_AGG(CONCAT( C1, C2, C3, C4, C5, C6 ),', ')||')' AS QUERY FROM ( SELECT C.TABLE_NAME, '"'||C.COLUMN_NAME||'"' || ' ' || CASE WHEN DATA_TYPE='ARRAY' THEN LTRIM(UDT_NAME,'_')||'' ELSE DATA_TYPE END AS C1, CASE WHEN CHARACTER_MAXIMUM_LENGTH > 0 THEN '(' || CHARACTER_MAXIMUM_LENGTH || ')' END AS C2, CASE WHEN NUMERIC_PRECISION > 0 AND NUMERIC_SCALE < 1 THEN NULL END AS C3, CASE WHEN NUMERIC_PRECISION > 0 AND NUMERIC_SCALE > 0 THEN NULL END AS C4, CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' END AS C5, CASE WHEN COLUMN_DEFAULT IS NOT NULL AND COLUMN_DEFAULT NOT LIKE 'nextval%' THEN ' DEFAULT' END || ' ' || REPLACE(COLUMN_DEFAULT, '::CHARACTER VARYING', '') AS C6 FROM INFORMATION_SCHEMA.COLUMNS C, INFORMATION_SCHEMA.TABLES T WHERE C.TABLE_CATALOG='tpch' AND T.TABLE_CATALOG='tpch' AND T.TABLE_SCHEMA='public' AND C.TABLE_NAME=T.TABLE_NAME AND C.TABLE_SCHEMA='public' AND T.TABLE_TYPE IN ('BASE TABLE') ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION ) AS STRING_COLUMNS GROUP BY TABLE_NAME
I realize that all available tables are below existing connection, which is the default database. And the default database must be choosen at the connection time. But yes, all databases could be listed in any connection.