Database Administrators Stack Exchange Community Digest

Top new questions this week:

Best Way to Determine if a Table has a Long Running Transaction

I need to be able to iterate all tables and issue a TRUNCATE command, however, I don't want the loop to be held up by blocking. Essentially, I am looking for the best way to determine if a SCH-M lock ...

sql-server sql-server-2019 locking blocking  
user avatar asked by Ross Bush Score of 4
user avatar answered by Paul White Score of 7

Are unique filtered indexes considered an antipattern for enforcing constraints?

I have a table, such as the following. Let's called it Fools. FoolID FoolValue IsActiveFoolValue 1 Ultra Foolish 0 1 Super Foolish 1 2 Quite Foolish 1 A business rule is that for each FoolID, we ...

sql-server sql-server-2019 constraint unique-constraint filtered-index  
user avatar asked by J. Mini Score of 2
user avatar answered by Charlieface Score of 7

DELETE Query with IN condition having performance issues in production

Introduction Let's say we have the tables as shown in the diagram, Corresponding DDL: CREATE TABLE Manager ( manager_id INT NOT NULL, manager_name CHAR(50) NOT NULL, PRIMARY KEY (...

postgresql postgresql-performance  
user avatar asked by crypticEncoder Score of 2
user avatar answered by Charlieface Score of 0

Automatic index creation - too many

When we have the auto tuning set to ON for index creation, the Azure SQL Hyperscale db has created about 10 indexes for some of our large tables. In analyzing the indexes, some of them differ only in ...

performance index azure-sql-database  
user avatar asked by Anand Sowmithiran Score of 2
user avatar answered by Aleksey Vitsko Score of 3

The specified @server_name ('') does not exist

We have an SQL Server 2019 instance (let's call it 'sample_instance') with server collation of 'Turkish_CI_AS'. When I try to create a job, SSMS creates the part of the script that adds the target ...

sql-server sql-server-2019 jobs collation msdb  
user avatar asked by halid.sert Score of 2

Mysql Crashing on: [FATAL] Semaphore wait has lasted > 600 seconds

Once a day or two we get the bellow error and the mysql crashingt: 2024-07-14T05:31:59.327696Z 0 [ERROR] [MY-012872] [InnoDB] [FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash ...

mysql innodb mysql-8.0  
user avatar asked by idan ahal Score of 1

Decrease high response time when reading large table in MySQL

I have a table with around 80~ million rows called parts. I have a simple query like this SELECT line_code, part_number, compressed_part_number, ...[Others columns] FROM parts WHERE (@partNumber = ...

amazon-rds mysql-8.0 amazon-rds-aurora  
user avatar asked by fluid undefined Score of 1
user avatar answered by nbk Score of 0

Greatest hits from previous weeks:

Can I see Historical Queries run on a SQL Server database?

Someone was running a query on our SQL Server database remotely and their system crashed. They have no backup of that query and want to see what was run on the server. Is it possible to find this ...

sql-server logs  
user avatar asked by user87094 Score of 49
user avatar answered by Mark Storey-Smith Score of 21

Select multiple values in LIKE Operator

I have a SQL query given below, I want to select multiple value using like operator. Is my Query correct? SELECT top 1 employee_id, employee_ident, utc_dt, rx_dt FROM employee INNER JOIN ...

sql-server sql-server-2008 like operator  
user avatar asked by l.lijith Score of 21
user avatar answered by Andriy M Score of 28

Difference between Sharding And Replication on MongoDB

I am just confused about how Sharding and Replication work. According to the definitions I found in the documentation: Replication: A replica set in MongoDB is a group of mongod processes that ...

user avatar asked by Saad Saadi Score of 129
user avatar answered by Philipp Score of 180

How to show the column names of a table?

The case is simple: You have a MySQL database where you have only an SQL query interface and you want to know the database structure with queries. You can list tables with show tables; command, but ...

user avatar asked by mico Score of 19
user avatar answered by mico Score of 7

How do I find PostgreSQL's data directory?

I forgot how I started PostgreSQL the last time (it was months ago) and I don't remember where the data directory is located. The postgres command seems to require the location of the data directory. ...

postgresql mac-os-x  
user avatar asked by randomguy Score of 203
user avatar answered by Peter Eisentraut Score of 244

Merge two SELECT queries with different WHERE clauses

I have one table of services. I need to merge two SELECT queries. Both have different where clauses. For example SELECT U_REGN as 'Region', COUNT(callID) as 'OpenServices', SUM(CASE WHEN ...

user avatar asked by TheSarfaraz Score of 10
user avatar answered by Michael Green Score of 18

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've seen a WHERE 1 clause being used in many places, even where other conditions are not present. ...

user avatar asked by ursitesion Score of 61
You're receiving this message because you subscribed to the Database Administrators community digest.
Unsubscribe from this community digest       Edit email settings       Leave feedback       Privacy
Stack Overflow

Stack Overflow, 14 Wall Street, 20th Floor, New York, NY 10005