Database Administrators Stack Exchange Newsletter – Monday, January 9, 2023

Top new questions this week:

Why does SQL Server sometimes estimate that joining onto an empty table will increase the row count?

I recently came across an issue where tSQLt tests were taking a long time to run. The procedure under test was doing a 38 table (!) join (with 37 faked tables and a table valued parameter). Only two …

sql-server performance execution-plan tsqlt

asked by Martin Smith Score of 6 answered by Paul White Score of 4

CTE Error when installing sp_WhoIsActive

I have several identical (near as I can tell) SQL Servers where I’ve recently added sp_WhoIsActive (showing some folks how much I like this tool) but one of them will not let me create the stored …

sql-server ssms cte sp-whoisactive

asked by RandomCorpSQLDev Score of 5 answered by Martin Smith Score of 8

postgresql matching or converting utf-8 variant strings

Postgres 13 I am looking for a way to search UTF-8 text that may have variant character representations ( what is the proper term for this? ie 𝐋𝐈𝐅𝐄 vs life ) within postgresql. I am running into …

postgresql collation postgresql-13 utf-8 string-searching

asked by unomi Score of 4 answered by Daniel Vérité Score of 3

String Manipulation of the Result from Recursive CTE

Good afternoon everyone I found just one post here within the last year about this, but it doesn’t help my situation. I have been working with MySQL and trying to improve my knowledge of recursive CTE….

mysql recursive string-manipulation substring

asked by nicodemus Score of 3 answered by David Spillett Score of 2

SQL Server data confidentiality in case of intrusion of machine host

I have to premit that i am a programmer, and i am trying to learn a bit about DBA even if i don’t have the same background. I am trying to understand what security provide, and how to use, encryption …

sql-server security transparent-data-encryption

asked by Skary Score of 3 answered by Sean Gallardy Score of 6

How to limit the amount of values within a postgres table which are updated

using the following sample: CREATE TABLE tbl( x integer, y integer ) INSERT INTO tbl SELECT g AS x FROM generate_series(1, 10) AS g; Which gives: x | y —-+— 1 | 2 | 3 | …

postgresql update limits

asked by baxx Score of 3 answered by Ergest Basha Score of 4

Can't read data out of XML stored in table column

I have some data stored in a table in XML format: <AccountTypes xmlns=””> <Id>1003</Id> <Id>2</Id> <Id>3</Id> <Id>1004</Id> &…

sql-server xml

asked by ChrisF Score of 2 answered by Martin Smith Score of 5

Greatest hits from previous weeks:

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the “last inserted id” from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I don’…

postgresql sequence

asked by Jonas Score of 85

Connect to sql plus from command line using connection string

Let’s say I have a Oracle database. I have a username = x, password = y, database = z. Also I know the port = a, SID = b, Hostname = c. So how do I need to connect correctly? I used many options like:…

oracle sqlplus string

asked by Edvinas Score of 62 answered by Hanspeter Oberlin Score of 108

Why is SQL Server consuming more server memory?

SQL Server is consuming 87.5 % of my server RAM. This recently caused a lot of performance bottlenecks such as slowness. I researched this issue. One common solution I could find on the internet is to …

sql-server sql-server-2008-r2 memory

asked by kombo Score of 43 answered by Thomas Stringer Score of 57

Writing select result to a csv file

We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don’t have this option. I …

sql-server-2008 t-sql

asked by Sky Score of 49 answered by RK Kuppala Score of 60

ALTER TABLE – Rename a column

This is driving me nuts! I want to rename a column from read-more to read_more in my blog table I tried all this: ALTER TABLE blog RENAME COLUMN read-more to read_more; ALTER TABLE blog CHANGE …

mysql mysql-5.5 mysql-5

asked by Bojan Score of 110 answered by Ulrich Thomas Gabor Score of 147

Update table using values from another table in SQL Server

I have 2 table in my database. Table #1 ————————————————————————- | name | family | phone | email | gender | phone2 | address | birthdate | ———–…

sql-server t-sql sql-server-2014 join update

asked by John Doe Score of 21 answered by Martin Smith Score of 37

Force drop db while others may be connected

I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -force flag, that will drop all connections and then the DB. How can I …

postgresql maintenance

asked by Alex Score of 214 answered by filiprem Score of 310

Can you answer these questions?

Postgres sorted join with millions of rows, low cardinality, slow query – time to partition tables?

I have an API that is used to query for data about programs and other forms of content. We have a few tables that I need to join on to fullfil an ad-hoc query, and I am running into trouble speeding …

query-performance join postgresql-performance partitioning order-by

asked by user208213 Score of 1

How to automatically add TLS certificates to Oracle wallets?

When accessing external webservices via HTTPS via the Oracle database you need to add the corresponding certificate to a database wallet to prevent a certificate validation error. When one creates an …

oracle ssl certificate wallet

asked by WayneNani Score of 1 answered by pmdba Score of 0

postgres ts_headline function is not returning matches it should during full text search

Background: I’ve been using Postgres full-text search and it has met my needs quite well. Though there is some unexpected behavior that I cannot seem to wrap my head around. It has to do with the full-…

postgresql full-text-search

asked by Jac Frall Score of 1