Database Administrators Stack Exchange Newsletter

Top new questions this week:

Why is this stream aggregate necessary?

Check out this query. It's pretty simple (see the end of the post for table and index definitions, and a repro script): SELECT MAX(Revision) FROM dbo.TheOneders WHERE Id = 1 AND 1 = (SELECT 1); ...

sql-server group-by aggregate sql-server-2017 database-internals  
asked by jadarnel27 9 votes
answered by Martin Smith 10 votes

What does it means when you have "sql_exit_invoked" in System Health?

I'm having problems with one of my SQL Server 2016 Standard servers. I have 8 production servers and this one is the only one who crashes randomly without having any trace in the log. I have ...

sql-server sql-server-2016 extended-events  
asked by Danielle Paquette-Harvey 8 votes
answered by jadarnel27 4 votes

Is there any difference between a predicate using a literal value or a parameter value when that value will always be the same?

I have a query that always filters on a status, is there a performance benefit of one of these ways over the other? (This is in the context of an ad-hoc query. The datatype of UserStatus is int) ...

sql-server performance sql-server-2016  
asked by JeremyWeir 7 votes
answered by Zane 11 votes

How to split XML array into separate rows (while upholding consistency)

I am working on the database dump of this exact stack exchange section. While I am working on it I have encountered one issue that I am currently unable to solve. In the XML File Posts.xml the ...

sql-server xml xquery  
asked by Chessbrain 6 votes
answered by Randi Vertongen 8 votes

Why would a Clustered Index Seek return a higher "Actual Number of Rows" than there are rows in the underlying table?

I'm troubleshooting an issue related to SQL Server (Azure SQL Database technically) occasionally choosing a bad execution plan, presumably due to skewed stats. sp_updatestats fixes it every time, ...

sql-server optimization execution-plan azure-sql-database  
asked by Todd Menier 6 votes
answered by Forrest 6 votes

What's the reason for using double inner join in this SQL statement?

I am looking at this legacy SQL query. The bit I am not able to get is why it's inner joining same table twice on the same columns. I am talking about Table1 and Table1 joined with alias ...

asked by Mathematics 5 votes
answered by ypercubeᵀᴹ 17 votes

Find groups with exactly one value: COUNT(DISTINCT x) = 1 vs MIN(x) = MAX(x)

Given this data: gid | val 1 | a 1 | a 1 | a 2 | b 3 | x 3 | y 3 | z the following queries return groups (gid) that contain exactly one distinct value (val): SELECT gid FROM t GROUP ...

sql-server query-performance relational-division  
asked by Salman A 5 votes
answered by Joe Obbish 7 votes

Greatest hits from previous weeks:

How can I move a database from one server to another?

How can I move MySQL tables from one physical server to another? Such as this exact scenario: I have a MySQL server that uses innodb table and is about 20GB size. I want to move it to a new server, ...

mysql mysqldump migration  
asked by John 122 votes
answered by David Hall 75 votes

Possible to make MySQL use more than one core?

I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help Setup The servers are quite hefty with an ...

mysql performance innodb mysql-5.5 tuning  
asked by gbn 105 votes

How do you mysqldump specific table(s)?

How can I dump a specific table or set of tables without including the rest of the db tables?

mysql backup mysqldump  
asked by markdorison 356 votes
answered by RolandoMySQLDBA 474 votes

mysql: Show GRANTs for all users

MySQL's SHOW GRANTS shows the permissions of the current user. Is there a way to log in as root and show the permissions of all users?

mysql permissions  
asked by Adam Matan 74 votes
answered by Shlomi Noach 40 votes

How to write a query to find all tables in a db that have a specific column name

I've got a database with about 100 tables and I need to build a join query to get specific data from two of them. I know one but not the other. Basically I need something like: select ...

asked by Joe Essey 10 votes

Why use WHERE 1 or WHERE 1=1?

Usually, if conditions are not required in our query statements, we don't use a WHERE clause. But I see a WHERE 1 clause being used in many places, even where other conditions are not present. Why ...

asked by ursitesion 42 votes
answered by Twinkles 31 votes

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?

mysql innodb myisam  
asked by ilhan 217 votes
answered by poelinca 140 votes

Can you answer these questions?

Unable to LOAD from XML: The used command is not allowed with this MySQL version

I'm having difficulty loading records from XML on OSX using the command load xml local infile 'records.xml' into table Badges rows identified by '<row>'; and I keep getting the error: Error ...

mysql mysql-5.7  
asked by John M. 1 vote

Downgrading PostgreSQL 10 to 9.4

Are there any particular precautions I should take when downgrading PG 10 to 9.4? I have a database on Heroku (PG 9.4) which I'm going to upgrade to PG 10.6. In case something goes wrong, I want to ...

postgresql postgresql-9.4 upgrade postgresql-10 downgrade  
asked by pmichna 1 vote
answered by Arkhena 0 votes

Unable to join secondary replica to the availability group by SQL Server error 47106

We were trying to create availability group with 3 synchronous replicas, where we couldn't give the names specified in /etc/hosts as node specifiers in the create availability group in the primary ...

sql-server availability-groups linux sql-server-2017  
asked by Aqib 1 vote
You're receiving this message because you subscribed to the Database Administrators newsletter.
Unsubscribe from this newsletter       Edit email settings       Leave feedback       Privacy
Stack Overflow

Stack Overflow, 110 William Street, 28th floor, New York, NY 10038