Database Administrators Stack Exchange Community Digest

Top new questions this week:

Changing database compatibility from legacy CE to 120

Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008) From DEV point of ...

sql-server query-performance sql-server-2014 optimization cardinality-estimates  
asked by BeginnerDBA 3 votes
answered by Oreo -1 votes

Is there any way I can speed up this large full-table query?

I have a query that selects from only one table and with one WHERE filter. However it takes a very long time to execute and even times out occasionally. This is likely because it is filtering about 4 ...

sql-server query-performance  
asked by datadawg2000 3 votes
answered by David Browne - Microsoft 14 votes

Can backups done through external clients cause memory pressure on SQL Server?

Developer shoved into DBA role here, we recently migrated from 2008 to 2019 and are having memory related problems on the new server. Every 3 to 6 days the OS will start paging and CPU usage would ...

sql-server backup memory sql-server-2019  
asked by diffie 3 votes

SQL Server - Prevent Clustered Index Scan on a specific table

My database contains a specific table that is quite large (250+M rows, 100+GB data space). Generating a clustered index scan on this table is always a bad idea. This table has multiple indexes and ...

sql-server query-performance sql-server-2016 execution-plan  
asked by cao 2 votes
answered by David Browne - Microsoft 1 vote

Best approach for extending a foreign relationship for an existing table

I'm not sure the title accurately reflects my question. I have an existing Rate table that has an identity column key and contains a [Rate] column. col1 + col2 + col3 don't uniquely identify a row: ...

sql-server database-design  
asked by David Clarke 2 votes

How to control Segmentation min/max data_id on a non-clustered ColumnStore index

Given a simple row-based table without a PK but with a row-based clustered index like so: create clustered index [CX_PropertyValue] ON [dbo].[PropertyValue] ([PropertyId], [Value]) Then I wish to ...

sql-server columnstore sql-server-2019  
asked by crokusek 2 votes
answered by Paul White 9 4 votes

How to Pivot Multiple Columns in SQL Server

I've read many posts about using pivot to get the data in the format I want, but the more I read the more confused I get. I have this data: That I'm trying to get into a format similar to this: ...

sql-server pivot  
asked by Ted O'Brien 2 votes
answered by David Browne - Microsoft 4 votes

Greatest hits from previous weeks:

How to get the current date without the time part

In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0

sql-server sql-server-2005  
asked by Piers Myers 88 votes
answered by gbn 126 votes

How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora

I want to connect to an oracle database located on another host using sqlplus. This page suggested adding an item on my tnsnames to conenct to that database local_SID = (DESCRIPTION = (ADDRESS ...

oracle sqlplus  
asked by Louis Rhys 84 votes
answered by René Nyffenegger 89 votes

How to update one table based on another table's values on the fly?

I have a table in the name of ips as below: CREATE TABLE `ips` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `begin_ip_num` int(11) unsigned DEFAULT NULL, `end_ip_num` int(11) unsigned DEFAULT ...

mysql update  
asked by ALH 43 votes
answered by Cade Roux 76 votes

oracle - list users with access to certain tables

I'm sure this has been asked before but I can't seem to find the relevant details for the following. Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it ...

oracle select permissions role  
asked by dgf 29 votes
answered by Philᵀᴹ 35 votes

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 233 votes
answered by Jack says try 336 votes

How do I list all databases and tables using psql?

I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool. When I log in with psql --username=postgres, how do I list all databases and tables? I ...

postgresql tools psql command-line  
asked by Jonas 1258 votes
answered by Frank Heikens 1663 votes

Create a MySQL database with charset UTF-8

I'm new to MySQL and I would like to know: How can I create a database with charset utf-8 like I did in navicat? create mydatabase; ...seems to be using some kind of default charset.

mysql collation utf-8  
asked by user3397998 162 votes
answered by shellbye 270 votes

Can you answer these questions?

When to break down attributes in tables into sub-tables for normalisation

I am currently developing a data model. This model contains an entity "Asset", which has many numerical attributes, volume, quantity, price, etc... I also have string attributes that are part of the ...

asked by jackharrison 1 vote
answered by Michael Green 0 votes

AWS iops vs disk iops?

Recently devops guy from our company told me that our local mysql stage environment are much faster than aws, because local disk has 10k iops, but our aws rds has only 500 iops. As far as I ...

mysql aws cloud  
asked by ogbofjnr 1 vote
answered by Gordan Bobic 0 votes

Creating a TYPE that is an Array within a Function (or a predefined Array TYPE) in PostgreSQL

I am researching migration of a major system from Oracle to PostgreSQL. Getting into coding functions now, and am looking to see if there is a compatible object in PostgreSQL where, dynamically ...

postgresql datatypes array domain  
asked by user210170 1 vote
answered by Erwin Brandstetter 0 votes
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, 110 William Street, 28th floor, New York, NY 10038