Database Administrators Stack Exchange Newsletter – Monday, February 6, 2023

Top new questions this week:

If you disable a non-clustered index, are the statistics still used?

tl;dr – As the title states – If I disable a non-clustered index on a table, are the linked statistics for that index still used? I’m aware there are many questions about “dropping vs disabling …

sql-server index sql-server-2017

asked by Chad Baldwin Score of 10 answered by Zikato Score of 17

SQL server Cardinality Estimation for LIKE query

I have this statistics histogram vector for my non-clustered index made on LastName column of a table named AspNetUsers. If I run a query as SELECT * FROM dbo.AspNetUsers WHERE LastName = ‘Baker’ it …

sql-server sql-server-2017 cardinality-estimates like

asked by Dhanuka Jayasinghe Score of 5 answered by Martin Smith Score of 4

What are these single letter SQL keywords?

I am looking at list of SQL keywords here www.postgresql.org/docs/current/sql-keywords-appendix.html There are some single letter keywords there. I cannot find any information about them. …

postgresql syntax sql-standard

asked by Pratik Deoghare Score of 4 answered by ypercubeᵀᴹ Score of 5

Does SQL guarantee that `SELECT *` in a subquery will be optimized to prevent a full scan?

I am exploring extended events query tracking and have question on couple of odd queries like following: Example 1: select top 10 * from ( SELECT [id] ,[date] ,[ordnum] , […

sql-server sql-server-2019

asked by variable Score of 4 answered by Paul White Score of 12

Differences in PostgreSQL 14 collation behavior on Linux and Mac

I have PostgreSQL installed on my Mac and Ubuntu Server as shown below: atsweb=# select version(); version …

postgresql ubuntu collation mac-os-x

asked by Christian Alis Score of 4 answered by Christian Alis Score of 0

Does a query that is suspended due to an ongoing ASYNC_NETWORK_IO cause blocking?

I am looking at the dm_exec_sessions and requests DMVs on a production server. There are some queries with status ‘suspended’, a duration of 12 minutes, and a wait type of ASYNC_NETWORK_IO. I …

sql-server sql-server-2019 blocking waits

asked by variable Score of 4 answered by Paul White Score of 11

ALTER timestamptz column to timestamp without “converting” data?

It seems that altering a column from timestamp without time zone to timestamp with time zone converts the existing data based on the current session time zone at the time of the alter statement. See …

postgresql alter-table timezone

asked by Padraic Renaghan Score of 4 answered by Erwin Brandstetter Score of 2

Greatest hits from previous weeks:

How to add the Debug button to SSMS v18?

The Debug button is present on this version of SSMS: But it is not present on version 18, preview 4: I have tried in several ways to add the Debug button to my SSMS but not successful. Is there a …

sql-server ssms

asked by Marcello Miorelli Score of 78 answered by Pred Score of 69

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 Score of 121 answered by Shlomi Noach Score of 50

Optimizing queries on a range of timestamps (two columns)

I use PostgreSQL 9.1 on Ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my …

postgresql index optimization postgresql-9.1 explain

asked by Stephane Rolland Score of 143 answered by Erwin Brandstetter Score of 243

How to insert values into a table from a select query in PostgreSQL?

I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int). Now I want to insert a row into items_ver …

postgresql insert

asked by Jonas Score of 355 answered by Jack Douglas Score of 511

Granting access to all tables for a user

I’m new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, and DELETE privileges on a low privileged user and enable those grants to apply to all …

postgresql best-practices postgresql-9.1

asked by PlaidFan Score of 200 answered by dezso Score of 217

Database cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery

I have received a database file and the instructions for loading it is to install SQL Server 2005 and then attach it using SQL Server Management Studio. After installing everything, I tried to attach …

sql-server ssms attach

asked by MxLDevs Score of 13 answered by Mike Fal Score of 11

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 Score of 524 answered by RolandoMySQLDBA Score of 712

Can you answer these questions?

PostgreSQL Logical Replication Trigger to add destination key column

I am using PostgreSQL Logical Replication to replicate data from multiple source databases into single one for analytical purposes. Data from the source database have the same schema and therefore …

postgresql replication primary-key

asked by Stavros Koureas Score of 1

How to store similar individual and mass entities in a database

I want to store IT devices which can be fixed assets (e.g. notebok, PC, printers, etc.), low value fixed assets (e.g. mobiles, printers, etc.), part devices (SSD, CPU, RAM, etc.), consumables (…

database-design

asked by sada Score of 1

Faster alter table column type in RDS Postgres?

has anyone figured out faster way of doing postgres 14 column type change in RDS? We currently have a table that is running out of int4 space and multiple columns needs to be converted to int8. Table …

postgresql postgresql-performance amazon-rds

asked by anttik Score of 1 answered by Laurenz Albe Score of 0