All Questions

Filter by
Sorted by
Tagged with
0
votes
1answer
440 views

Is it possible to spin up a free development / test instance of an AWS Redshift server to run test queries on?

I have a traditional MS SQL Server background and am interested in looking into AWS Redshift. Microsoft offers a free download of their development version installer of SQL Server, and I was wondering ...
0
votes
1answer
28 views

Why is FROM VALUES massively faster than visiting a table once?

Given this setup, CREATE TABLE t(foo) AS SELECT 'foobar'::text; Why is this query, EXPLAIN (ANALYZE, TIMING OFF) SELECT generate_series(1, 1000000), upper(foo) FROM ( VALUES ('foobar') ) AS t(foo)...
0
votes
1answer
180 views

Is it advisable to asynchronously update the summary table every time a transaction is deleted?

Given three tables: (a) Product (b) Transaction Head (c) Transaction Detail We need to create a query that fetches a list of products with associated latest ordered quantity and latest transaction ...
0
votes
0answers
7 views

What does the sql code look like to count the amount of matches between two tables

I have two tables (USER1 and USER2) they each contain a user's email, first and last name, interest department and project ID I have the following questions: 1.) What would the sql code look like to ...
2
votes
1answer
39 views
+200

Powershell SMO changing database context from master when setting database to SINGLE_USER

I am trying to set a database to single user using SQL Server SMO and have the code below. This code is an abridged excerpt from a larger restore script $Credential = Get-Credential [System....
0
votes
0answers
14 views

Timestamp to indicate the latest event writing in a table

I want to know if there is any way to find out when the latest event was writing to a table in SQL Server. Assuming the table does not have a created_at/updated_at columns and CDC and Change Capture ...
2
votes
1answer
113 views

Snapshot Folder Location for Replication with Clustered Instance

I am assigned to create a transactional replication with a SQL Server clustered instance for reporting purposes. I'm planning to use the Distributor and the Subscriber on the same box. Regarding the ...
-1
votes
0answers
6 views

Compare today's and yesterday's data in MySQL

I have the following table: location eventdate value loc1 2021-11-28 1 loc2 2021-11-28 2 loc1 2021-11-29 1 loc2 2021-11-29 3 And I'd like to compare the values from yesterday and today to get ...
-1
votes
0answers
17 views

Timestamp to indicate the latest event writing in a table

I want to know if there is any way to find out when the latest event was writing to a table in SQL Server. Assuming the table does not have a created_at/updated_at columns and CDC and Change Capture ...
0
votes
1answer
369 views

Should a TeamMembers table have a relationship with the Student Table or the Enrolment Table?

I'll keep it nice and short and simply link to the ERD: Users can be assigned a role, one of those is student. So for the sake of this, Students are the same as Users! A Student can be enrolled in ...
1
vote
3answers
68 views

What are use cases for Recursive CTEs?

I am looking for examples of why you would use a recursive CTE, but the dozens of examples I have found in my web searching basically reduce to two: Generating a sequence Iterating through an ...
3
votes
2answers
99 views

How can you replace the last occurrence of a character in a string (text column)?

Let's say I have a text field with this value in Postgres: 'bar$foo$john$doe$xxx' I'd like to replace just the last occurrence of the dollar ($) character with another character, for example '-'. ...
0
votes
0answers
22 views

Unable to restrict user from built in SQL Server Functions

We have a SQL Server (Standard) database with a user whom we are attempting to restrict permissions to only CRUD Operations to certain tables (CREATE, SELECT, UPDATE, DELETE) and only allow connect ...
0
votes
1answer
3 views

problem installing mariadb using mysql_secure_installation

I started secure install of MySQL with sudo mysql_secure_installation command, and I got that message: Securing the MySQL server deployment. Enter password for user root: Error: Can't connect to ...
17
votes
6answers
268k views

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 ...
0
votes
0answers
4 views

Google Cloud SQL - Database instance storage size increased dramatically everyday

I have a database instance (MySQL 8) on Google Cloud and since 20 days ago, the instance's storage usage just keep increasing (approx 2Gb every single day!). But I couldn't find out why. What I have ...
0
votes
0answers
10 views

postgresql performance issue with multiple EXISTS

explain select count(*) from (SELECT tcy.clientid, tcy.projectid, max(tcy.lastmodifieddate) AS projectupdatedat FROM test_cycles tcy JOIN projects ...
1
vote
1answer
54 views

Isolation level with SQL Server and Azure Sql Database Read-Only replica

I read here and here that, when connecting to a Sql Server read-only replica, the isolation level used is ALWAYS SNAPSHOT. It says it ignores SET TRANSACTION ISOLATION LEVEL statement and any locking ...
0
votes
1answer
11 views

Mongo node with smaller priority has not stepped out

I have a three nodes replica and I prefer a 10.0.0.5 as a primary. I have just realized during a performance test that 10.0.0.2 is primary instead. I thought it should have step out automatically ...
0
votes
1answer
420 views

SSRS2016 external parameters for mobile report

How can external parameters be passed to a SSRS 2016 mobile report in order to pre-select values / settings for the report? The same scenario is possible for paginated reports as it always was. It's ...
2
votes
1answer
199 views

sp_addUser through sp_execute (SQL) in master db

The task is to automate restoring a group of databases, fixing orphaned users, and granting SELECT/INSERT/UPDATE/DELETE permissions. use master go ALTER DATABASE [myDatabase] SET SINGLE_USER WITH ...
0
votes
0answers
4 views

Getting the max ID of integer columns in databases in Postgres 9.6/12

I am looking to get the max ID of each integer column in our databases servers. The function I currently use: CREATE OR REPLACE FUNCTION intpkmax() RETURNS TABLE(schema_name name, table_name name, ...
0
votes
0answers
22 views

Database Design With Multiple Payment Methods And Membership Types

I want to design my database similarly to something like this: Better way to design payment database with multiple payment methods But my database is a little more complex where instead of buying 1 ...
0
votes
0answers
13 views

Database Design With Multiple Payment Methods And Membership Types

I want to design my database similarly to something like this: https://stackoverflow.com/questions/57510457/better-way-to-design-payment-database-with-multiple-payment-methods But my database is a ...
0
votes
1answer
227 views

MS SQL: get permissions of a fixed server role

I need to get a list of MS SQL Server fixed roles and their permissions. There is a query to get all server-level permissions: SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY ...
0
votes
2answers
13 views

ERD - How to add an attribute to one side of the recursive relation?

The simplified scenario for the ERD A company has employees (each has unique id emp_id) Each employee is supervised by another employee A supervising employee has a special attribute SID Here is my ...
-2
votes
1answer
18 views

Query running slow

Trying to run below query on PG and it's running very slow..plz help tune it..query is simple..indexes tried already but not helping SELECT DISTINCT ABC.Keya as PG FROM ObV.PY AS JPS ...
21
votes
5answers
12k views

Is it still best practice to avoid using the default ports for SQL Server?

Historically, it has been recommended not to use the default ports for connections to SQL Server, as part of security best practice. On a server with a single, default instance, the following ports ...
1
vote
2answers
42 views

Best way to store encrypted API identifiers (MariaDB)

So for max security I have to encrypt some string keys which I need to call specific actions of an API, at a very regular basis of the use of an application, so performance should also be considered. ...
4
votes
1answer
2k views

Slow postgres query that uses quick sort instead of hash-aggregate

I'm on Postgres 9.5 and working with a web analytics database that records visitor traffic. I'm trying to optimize a very slow query that gives me a count of the unique people that visited a given ...
0
votes
3answers
613 views

Select repeating number x times and then increment

I need a select query that can generate z amount of rows starting from 1, repeating x times and then increment to the next number. For example 10 rows repeating 4 times would result in the following: |...
1
vote
4answers
34k views

Opening a .backup file for PostgreSQL

I'm going to warn you that whoever explains this needs to talk to me like I'm five years old, because this is something way, way beyond my level of expertise. In short, I have what I think is a ...
2
votes
1answer
176 views

Execute SQL conditionally based on database Compatibility Level

The Problem I have a script that I need to execute on both SQL Server 2016 and SQL Server 2019. If executing on Server 2019+, I want to use syntax that is only supported on that version (specifically, ...
25
votes
4answers
70k views

Truncated 200GB table but disk space not released

I have only 2GB left, so I need to remove this history table. This table now is empty but the database disk space not released. And the database file is 320GB.
0
votes
1answer
11 views

Should I design my firestore database with different collections for each userId or one collection for all userIds?

I have a Firestore db where I have setup the structure: registrations/[userId]/symptoms/[documentId]/[some kind of document] I did like this because the user should only read its own symptoms, and ...
0
votes
1answer
217 views

Exporting an Excel Spreadsheet containing a pivot table and charts to MySQL

I am looking at moving a large Excel spreadsheet to an SQL database - most likely MySQL. The excel spreadsheet contains 12 worksheets, with one of them being the Master, and the other 11 set up to ...
0
votes
0answers
12 views

Is there a name for a relation with FDs whose LHS includes part or whole of the primary key and some other attributes?

Take a relation like R(A,B,C,D) with the following FDs: AB -> C BC -> D The primary key of the relation is AB because AB -> ABCD holds. However I can't determine in what way D is dependent on ...
2
votes
2answers
6k views

Does MS Access have anything equivalent to triggers?

I'm developing some code that will eventually talk to an Oracle 10g database. While doing this, I am also redesigning the relevant database tables. I'm not a database pro, and I don't have sufficient ...
1
vote
1answer
41 views

Postgres ERROR: could not compare Unicode strings

I have a database on my development machine where everything runs just fine. I copied the database to the server using file system level backup. Most queries run fine and fast. But I got problems with ...
6
votes
3answers
227 views

How can I calculate all grouping permutations of an input string in SQL?

Given an input like "ABC" generate a query that calculates all potential splits of 0 or more of the given string, Desired output, A B C A BC AB C ABC Given an input like "ABCD&...
1
vote
1answer
460 views

dockerized postgresql listing no clusters

I'm upgrading an existing dockerized postgres:9.6 to 10. There are plenty of good Q/As on DBA and SO that go the path of pg_upgradecluster, but it is not working for me. When I start a vanilla pg:10 ...
1
vote
1answer
40 views

Is there anyway in sql server transactional replication to not drop the target tables?

I have transactional replication setup between Server1 database and Server2 database on few tables. Now the problem is when I start the replication for the first time, it drops the table on target ...
0
votes
2answers
28 views

How to make a restricted view in oracle?

I had to provide data for an application schema, but there is a requirement that the applications connecting using that schema user cannot query more than five rows at a time, the application queries ...
0
votes
0answers
3 views

paritition exchange without validation mysql 8

I have a table in production facing about 1M inserts per day,partitioned on a date column. This table is queried as well but the inserts are important for application. Have a small script that runs ...
0
votes
1answer
2k views

From RAC to Standby RAC With Active Dataguard

I have oracle database 11.2.0.3 with RAC feature , with 2 node on oracle linux 6.4 : ol6-112-rac1 and ol6-112-rac2 I want to create active data guard with RAC feature from this database , these ...
2
votes
1answer
205 views

What's the difference between a thread vs. a process?

Specifically regarding MySQL, what is the difference between a thread and a process? The documentation seems to refer to them interchangeably at times, and it even throws in the term "session" too. ...
0
votes
1answer
467 views

Debuging import of a large dump file to MySQL

I'm trying to import a 50GB dump file to AWS RDS instance and have an issue. I get the following error: ERROR 2013 (HY000) at line 15850: Lost connection to MySQL server during query In the beaning ...
0
votes
0answers
23 views

emails sent by a phantom job: find a sender job

Every night at the same time I receive some mails like that: DESCRIPTION: Error: 50000 Severity: 16 State: 1 [ERROR]:DWHDumpProcess_XXX job is either failed or not in good state. Please check. Return ...
0
votes
1answer
5 views

Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

After I changed both table collition into utf8mb4_unicode_ci, the database still show this error: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for ...
1
vote
3answers
267 views

MySQL Optimization Guidance Needed

UPDATE 6/23/2020: Haven't noticed any odd CPU spikes lately, but noticed that the MySQL memory footprint grows indefinitely until it goes OOM and is killed by the kernel. Where should I start looking ...

15 30 50 per page
1
2 3 4 5
1867