All Questions

Filter by
Sorted by
Tagged with
1
vote
1answer
27 views

Different collation between System and User databases in SQL Server

Recently, I have came across a situation where I found there is different SQL Collation is set for System databases and user databases on a sql instance in production. Like: Danish_Norwegian_CI_AS ...
0
votes
0answers
21 views

Best strategy to correctly index complex join query

This is the query running on MySQL 5.7 with INNODB: SELECT SQL_CALC_FOUND_ROWS calendar.id_calendar AS calendar_id, calendar.event_datetime AS calendar_event_datetime FROM users INNER ...
1
vote
2answers
36 views

Copy database removed source - Cannot acces mdf file

I attempted to copy a database, using Microsoft SQL Server Management Studio (SSMS), but right-clicking and following the wizard. I wanted to target server to be the same as the source, so I added "...
0
votes
0answers
21 views

Omit table name and dot in SELECT query

When I perform this query: SELECT `tablename`.* FROM `something`.`tablename` I get a table with column names that contain dots: tablename.c1 | tablename.c2 | tablename.c3 ---------------------------...
-3
votes
0answers
27 views

What better way monitoring active connection on Oracle DB?

What do you think is a better way to monitor active connection on some Oracle DB. We need history for see how our program work with DB. Today we use Prometheus+Grafana+Orcale_exporter. Oracle exporter ...
-2
votes
0answers
19 views

In mysql, error while creating foreign key “Referenced table has no candidate columns with a compatible type for…”

I create a reverse engineering model of the DB, and when I try to add the foreign key through the diagram (created by reverse engineering), I get this error: Referenced table has no candidate columns ...
0
votes
1answer
47 views

Can't help the engine to choose the correct execution plan

The stuff are pretty complex to share the original code (a lot of routines, a lot of tables), so I will try to summarize. Environment: SQL Server 2016 standard edition Objects: wide table with ...
1
vote
2answers
15 views

Does PostgreSQL btree_gin extension uses btree or gin data structure?

In order to define a GIN index on an array and scalar field (for example) you need to enable the btree_gin extension in PostgreSQL. Then you can define your indexes as a GIN index. However I don't ...
-2
votes
1answer
36 views

SQL Server wait problems [closed]

In my production SQL Server 2012 (Standard), top 4 waits are: CXPACKET SOS_SCHEDULER_YIELD LATCH_EX WRITELOG. And: MAXDOP :0 Cost Threshold for Parallelism: 5 I know here, parallelism is ...
1
vote
3answers
24 views

PostgreSQL (big data): search by tags and order by timestamp

We need to add a search feature on a large table (200M+ rows): item_id | tags | created_at | ... ------------------------------------------------------------------- ...
0
votes
0answers
23 views

Will a composite index containing an enum with few choices or a boolean increase or slow down query speed?

I have the following query: SELECT new DTOs.EntityDTOs.cropvariety.SeedCalendarResultDTO(u.id, u.crop, i.previewImageUrl, u.plantType, u.growthTemperature, u.hardiness, u.growthTime) FROM SeedRecord ...
0
votes
1answer
62 views

MSSQL Join between big and empty tables

I've got an issue with estimated cost and join predicate : SELECT c.Id FROM Table_A a LEFT JOIN Table_B b ON b.Id_A = a.Id LEFT JOIN Table_C c ON c.Id_B = b.Id WHERE a.Id = 2500 OPTION (RECOMPILE) ...
2
votes
1answer
22 views

calling scalar function to assign value to local variable

In a SQL Server stored procedure, if we declare a local variable and assign the value by calling a scalar function DECLARE @variable_name INT= dbo.scalarfuntion_name(@p1, @p2). Would it have any ...
0
votes
0answers
10 views

IBM DB2 z/OS - Do not UNLOAD DECIMAL rows exceeding a certain length

I have an IBM DB2 table where I want to UNLOAD data from so that I can load it into another DB2 table. The database is running on z/OS. Both tables have the same columns (and types), except one ...
0
votes
1answer
47 views

Database Engine Tuning Advisor drops index automatically on SQL Server 2016? [closed]

we are using SQL Server 2016 and currently we are facing some performance issues. Solarwinds tells us, that there is sometimes much wait time caused by a Lock "LCK_M_SCH_M". I was now able to check ...
2
votes
1answer
31 views

Availability Group Replica delay

looking for ideas for diagnosing the root cause of the following issue. We have 3 availability nodes Primary,Synchronous replica, Asynchronous replica. no load other than T-Log backups exists on the ...
1
vote
2answers
31 views

PostgreSQL remove numbers before first comma

I am new to SQL however, I am looking to update a table where column called "scrap_value" and rows contains a set of numbers e.g. {100000,125000,150000,175000,200000} I want to remove the first ...
0
votes
1answer
26 views

Filter the query result

I have one query by which I am getting duplicate ComapnyID and I want to filter that and remove duplicate ComapnyID but don't want change the order by report. table 1 reports Table 2 Company Here ...
0
votes
1answer
29 views

Clustered index and Non clustered index

This might be a silly question but i really want to know the internals.This is one of small table just to test as below I have create a table as above and 'name' column is primary key and clustered ...
0
votes
1answer
55 views

Non-clustered index covering a clustered index

I have a table that has the following indexes: IXC clustered index that includes a single column : A asc. IX1 that includes two columns : A asc, B asc. In this case ICX seems like a duplicate index ...
0
votes
0answers
23 views

Extract Image URLs from html text using Postgres sql Query

I have a column in postgres db in which html contain stored. Like this <p style="text-align: justify;"><strong><img alt="" src="https://images.unsplash.com/photo-1579121477063-...
1
vote
2answers
36 views

How to sort values starting with a letter followed up by a number?

I have a series of values, possibilities are : A1, A2, B1, B2, A100, A101, B100, B200, 1, 2, 3, 4 and so on. How can I sort it so that first the letter is sorted, and after that the number with the ...
0
votes
0answers
18 views

Is there any fastest way to shrink LOB data in SQL Server

We have a database of total size of 3886 GB. Out of which 778 GB is free. So total 3108 GB worth of data is present. Out of which 2458 GB is "LOB Data" and 400 GB is worth "Regular". Since the ...
0
votes
0answers
18 views

Login failed for user Domain\MachineName$ local Machine

We have some problems with 2017's sql servers. There are lots of errors with text like: Login failed for user Domain\MachineName$ Reason: Could not find a login matching the name provided. [...
0
votes
2answers
12 views

How to transfer the incremental MS Access table data to PostrgreSql Tables

I have an attendance punching data stored in MS Access database. It's increasing day by day basis. Can we convert the MS Access table data to the PostgreSql table on the incremental bases. What is ...
-1
votes
0answers
22 views

Combine a query with a subquery [closed]

Two queries that I use to produce an output consisting of many columns: TRANSFORM PackageID SELECT Process FROM Sheet1 GROUP BY PackageID PIVOT Process; I call the above Query1. Then to limit the ...
0
votes
0answers
27 views

How SQL Server decides in which data file data should get stored

We have a database with multiple filegroups. Out of which there is one filegroup which has one data file in one drive with autogrowth turned OFF and another data file in another drive with autogrowth ...
0
votes
0answers
10 views

AWS Quicksight - MySQL UNION from multiple databases

I'm using AWS Quicksight for an Analytics dashboard and I have multiple databases that have the same tables. I then set a Data Source for each database. Now, I've been trying to create a Data Set for ...
0
votes
0answers
12 views

I'm trying to fetch unique values from one column (registration_no) which has duplicate values in column(mobileno) and column(email)

This is my table - I want to get the unique registration_no's which have same mobileno, email and date_of_action. For instance row no 8,9 and rows 10,11,12,13
-1
votes
0answers
15 views

Postgres: handy way/function to compute bearing change from coordinates

I am going to compute point-based acceleration and bearing relative to true/magnetic north from the sequence of GPS points with timestamp and instantaneous speed. Here's how the dataset looks like: ...
1
vote
1answer
25 views

Slow bitmap heap scan on group/count query in a large table

I run an app with a database of PostgreSQL 10.10 I am optimizing a bunch of queries in our application, with great results so far, but there is one specific query that takes about 1 minute if not ...
0
votes
1answer
20 views

Generated DDL doesn't match with CREATE TABLE statement

I'm using MySQL with InnoDB engine. I create a new table that contains a foreign key, eg: CREATE TABLE rooms ( id INTEGER NOT NULL AUTO_INCREMENT, my_id VARCHAR(15), house_id INTEGER, ...
0
votes
1answer
9 views

mismatch between server schema content and exported sql rows

I am using MySQL 5.6.44 within phpMyAdmin on Go Daddy shared hosting and experimenting with some duplicate table data. I am trying to figure out, when I export my table from phpMyAdmin the exported ...
0
votes
1answer
20 views

SQL Server - SQL Agent Alerts - Alert 824 warning about old problems

I have alerts enabled on a SQL Server 2017 where corruption was encountered and repaired over a year ago. I have also confirmed that there is no new corruption on any database on this server. When ...
0
votes
1answer
41 views

Why T-log is not truncated after T-log backup?

I have a database myDB in full recovery model. I took a full backup and restored it on the test server. Afterwards, I checked the size of T-log. The size of t-log was 15600 MB and used space out of ...
0
votes
0answers
19 views

SQL Deadlock resolution

I am seeking help regarding a SQL deadlock I am trying to resolve. Please ignore the fact that the query -> 'Select * from ... Where In...' can be written in a much more straightforward way. I got as ...
-1
votes
0answers
18 views

Show different hour in database (phpmyadmin) and in the sql in php

If I try to show the time in php with $conn->query("SELECT @@SYSTEM_TIME_ZONE, @@TIME_ZONE, NOW();") the page shows me a different time than if I use the command SELECT @@SYSTEM_TIME_ZONE, @@...
1
vote
1answer
16 views

Importing a schema in Oracle with a different name

I exported an Oracle XE 18c database with: expdp USER1/pwd DIRECTORY=backups DUMPFILE=file.dmp LOGFILE=config_export.log SCHEMAS=USER1 Now, I created in a different machine another user and ...
1
vote
1answer
35 views

Unable To Restore Database From .BAK File

I am trying to restore my .bak file but I keep getting the error Restore of database 'DataWarehouse' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)System.Data.SqlClient.SqlError: ...
-2
votes
1answer
18 views

Can't manage to work with comparing days

In a table with persons and their birthdays, the ones whose birthday is in less than 7 days need to be displayed. I found some answers on the Internet but they all are at least 5 years ago with ...
0
votes
0answers
12 views

(MySQL) Finding the first, second, …, nth movie directed by a group of directors [duplicate]

There's a table titled "movies". The table can be found here. (Please scroll down to the bottom of the page to see the table). I want to find the list of first movies directed by each director in the ...
2
votes
1answer
52 views

Deadlock troubleshooting in SQLServer

Edit: In case it is relevant, we are connecting to the database via JDBC in a java webapp. We use autocommit=off for all of these operations, and deadlocks mean the job thread is not committed. I am ...
0
votes
0answers
9 views

Database schema for time based inventory system [closed]

Lately I have been thinking about a time-based inventory system for a hobby project. Lets say that I have multiple resources (to make it easy lets take hotel rooms as an example). There are multiple ...
1
vote
1answer
11 views

Database Mirroring - Will forcing the service only lose non-committed data?

I have a database mirroring session in high safety mode between Server1 (Primary) and Server2 (Secondary) According to this, synchronous data mirroring means every transaction committed on the ...
1
vote
2answers
40 views

Determining Whether Query Load is Too High for PostgreSQL Server

I just started experiencing some write-heavy load on a PostgreSQL 11.2 system running inside a Docker container on an Ubuntu 18.04 server. PostgreSQL is receiving about 1-4k INSERT queries/second. ...
0
votes
0answers
13 views

Master slave replication - DATETIME columns not replicating the time

I have master which is mariadb 5.5 and slave mariadb 10.3, the replication works fine except for the DATETIME columns which are syncing the date part but not the time. On master: LastLogin 2020-01-15 ...
0
votes
1answer
14 views

Fail to copy all PostgreSQL databases between servers although there is no error message

I want to copy all databases from PostgreSQL 9.3 on Server-1 (Windows 7) to an empty PostgreSQL 12.0 on Server-2 (Windows Server 2012 R2). The steps are as follows: On Server-1: C:\Program Files\...
1
vote
1answer
31 views

Create a view with distributed Lobs

I have two Oracle 18c instances (instance 1 with schema DB1 and instance 2 with schema DB2) and I know that there ist a support for distributed lobs since 12.2. So i did the following: CREATE TABLE ...
-3
votes
0answers
14 views

Three table join [closed]

I have 4 tables 1.product,2 purchase order,3 stock Item,4 sale order.I need to create a report which give result like this.[![Please look at images product name will be fetch from product table , ...
0
votes
1answer
68 views

Why is this SELECT creating temp tables?

When I run the following SELECT, it takes quite long, typically 30 or 40 minutes (I'm on Debian, MySQL 5.5.62): SELECT ga.currency, o.organisation_name, os.hostname, g.global_name, SUM(IF(ga....

15 30 50 per page