All Questions

0
votes
0answers
14 views

Ιndexed view after replication

I am trying to find a smart (no redundunt dataflow) way to aggregate data on views/tables to be used from our reporting tools. One given is that the reporting database is different from the oltp one, ...
4
votes
1answer
190 views

SQL Server: are backups between same major/minor version but different build compatible among them?

For example a backup executed on SQL Server 10.50.6000.34 can always be restored on SQL Server 10.50.4000.0 or 10.50.2500.0. All of them indicates an SQL Server 2008 R2 (10.50.xxxx.xx) but different ...
-1
votes
1answer
29 views

Does Lazy Writer spend resources on flushing out pages from the buffer pool?

In MS SQL Server, does Lazy Writer spend resources to make pages out of the buffer pool after periodically checking free buffer list and marking some clean pages as free? Or free marked clean pages ...
0
votes
0answers
39 views

Kimball Data Warehouse: Denormalize Hierarchy Attributes

I have many different flat files. We have Property Owners, who own Properties, which are under a Tax Assessment (Tag Code). Property Owners flat file does not have SSN/Id, so sometimes same property ...
-1
votes
1answer
32 views

Showing the report date wise in SQL SERVER REPORTING SERVICES

I have MS SQL database having multiple records for each date. Now I want to fetch these data and prepare a report showing the sum of all multiple records in a single row for each date. Could anyone ...
1
vote
2answers
53 views

Empty closing XML tag instead of self-closing tag

My query is USE ... SELECT @JNo=jour_no from WB_PMS..tblJournal WHERE jour_scode=@Jcode IF EXISTS (SELECT 1 FROM WB_PMS..tblArticle WHERE jour_no=@JNo AND art_no=@ArtNo) BEGIN SET @XML= ...
0
votes
0answers
33 views

Some question about FBI and Index on unique column

Anyone can explain to me these issues: Which function-based-index(FBI) should I create and not? I heard from someone active in this forum that we should avoid create FBI as RTRIM, TO_CHAR, UPPER, ...
0
votes
0answers
24 views

Is it possible to queue PostgreSQL logical replication messages from multiple sources?

I want to use multiple "forward" PostgrSQL instances which serve distinct applications but share overlapping subsets of the same schema to update a single master which holds a full version of the ...
0
votes
0answers
18 views

SQL Throw with TableErrorLog

I read Raiserror is being deprecated in SQL Server, our team uses an error log table. How would I change our previous code to utilize throw? I want to learn the appropriate modification for legacy ...
0
votes
1answer
12 views

MySQL synchronization

I have a cloud-based MySQL instance (slave) and am planning a solution where a remote instance (master) will be based on a ship. The ship will go in and out of international waters and therefore will ...
0
votes
1answer
21 views

Sqlplus not using NLS_LANG env variable

I am trying to login to an Oracle 10g database vía sqlplus. In the nls_session_parameters I need NLS_DATE_LANGUAGE in American, but I always have Spanish. In the database server my NLS_LANG is ...
0
votes
1answer
29 views

Creating login/user for Always on Environment

How can i use by making changes to below 2 scripts to run in the environments with Always ON enabled. Below are working fine but fails for an always on enabled instance or AG with readable secondaries....
1
vote
0answers
23 views

Modelling a database for a shopping list - should I represent the scenario with a “redundant” relationship?

[I may provide some pictures later] I'm facing this issue in modeling an entity-relationship diagram. I'll have a shopping list, where for each item the user inserts, it'll be associated with one ...
16
votes
1answer
1k views

Why Do My Nonclustered Indexes Use More Space When I Delete Rows?

I have a large table with 7.5 billion rows and 5 indexes. When I delete roughly 10 million rows, I notice that the nonclustered indexes seem to increase the number of pages they're stored on. I ...
1
vote
2answers
46 views

How to cast (a) a date value recorded as a VARCHAR to (b) a specific DATE format for (c) use in an ORDER BY clause

How would I cast a column value (a "non-MySQL" DATE string retained as a VARCHAR) to a "MySQL date" so it can be used for sorting? I have dates in the format of 06-02-2019 (6 feb) but MySQL itself ...
1
vote
1answer
37 views

How to (a) rewrite a query that JOINs three tables and (b) make its execution more efficient

I have three tables (A, B and C) with various data regarding mining facilities in them. But only one table (C) has two columns with coordinates. My final goal is to produce a table which pulls similar ...
1
vote
5answers
294 views

Filter by using in for SQL Server

I have the below table Col1 Col2 1 ACA,ABS,PHR 2 PHR 3 MCM 4 ABC Now I want to filter the data from this so if I have a filter parameter as say 'ABS,MCM' I ...
-1
votes
2answers
143 views

Queries key look up

SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col25... FROM Tabl1 WHERE ( Col30 IS NOT NULL AND Col28 <> @P0 AND DATEDIFF( dd, Col31, ...
1
vote
2answers
24 views

How to implement a dynamic referral program?

Example: Currently, we will provide 100 credits each to both the referrer and referred. Then in the future, we might want to implement a different referral program. Such as change credits to 200 ...
0
votes
0answers
43 views

High Availability Group configuration failure

Trying to configure a High Availability Group using SQL Server 2017 without clustering, but it's failing when I try to add the secondary replica with the following error message: Failed to join ...
0
votes
2answers
20 views

postges: psql csv import fails although file exists

Trying to import a csv file as follows: psql -h 127.0.0.1 -d jira_statistics -U admin -p 5432 -c "COPY bugs FROM '/home/centos/bugs.csv' delimiter '|' csv;" Password for user admin: ERROR: could ...
1
vote
2answers
51 views

What is the best way to calculate year over year inflation?

My ultimate goal is to calculate money from previous years as 2019 dollars. I've got these numbers from the BLS, and create the CPI_U in sql like so: create table CPI_U (year int, dec decimal(4,...
1
vote
0answers
28 views

Order of logical decoding output on Postgres 11

Given the following table in PostgreSQL 11.1, what is the order of logical decoding output rows? CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY ,name TEXT ) ; CREATE TABLE t2 ( t2_id INTEGER ...
0
votes
0answers
25 views

One way replication between 2 sql servers on different networks

I would like to replicate between 2 sql servers that exist in different networks. This link is not relevant to my case as the servers are not connected: https://docs.microsoft.com/en-us/sql/relational-...
0
votes
1answer
21 views

File Share Witness with 4 Node Cluster

I am building a cluster of 4 nodes along with a file share witness. Every month due to server updates the server that holds the file share will need to be rebooted. My question is while the file ...
1
vote
1answer
33 views

Setting up a many-to-many relationship based off of two other many-to-many relationships

I have 3 main top level entities: Project (PK: Id) Owner (PK: Id) Product (PK: Id) Here are the rules: A Project can have many Owners. An Owner can have many Projects. A Project can have many ...
1
vote
1answer
23 views

Added a new listener to my 'listeners.ora' file. How can I connect directly to it with SQLPlus? (skipping tnsnames.ora for now)

I updated my listeners.ora file to include a new listener on port 1523. I then started the listener with lnsrctl start. I'd now like to connect to the database on that port via SQLPlus without ...
1
vote
0answers
15 views

Design table that solve a service payment or a package service payment?

I am learning and practicing some database designing and case study on Selling Services on Internet. In my requirement, user can buy a post service and add more extra option if they need. In fact, ...
1
vote
0answers
19 views

FOR XML could not serialize the data for node 'col_definition'

looking for some help. I have just started looking at the Blitz stats but getting the following error. This is whilst running sp_BlitzIndex. I am using Version 7.2 (dated 28/01/2019). FOR XML ...
0
votes
1answer
18 views

csv import fails without throwing an error

Ι am trying to import a csv file into an existing mysql 5.7 table. Here is the command (the db is in docker with bind mount for persistence) mysql -h 127.0.0.1 -uroot -prootpass --local_infile=1 ...
0
votes
0answers
11 views

Is it possible to make thosunds of concurrent transactions even if the max_connections is set to 100 in Postgres?

This is maybe a dummy queestion but I couldn't understand to have thousands of concurrent transactions in a database even if the max_connections=100. Definition of max_connections from postgres ...
0
votes
0answers
35 views

SQL Server Execute Permission Denied

I have a small app which calls a stored procedure over ODBC using a sysadmin user with login and password. The problem is that some (Windows Domain) users get an error saying execute permissions were ...
0
votes
2answers
29 views

Select records with the same first column value and different second column value? SQL Server

Assume we have a SQL Server table as the following: ContentId | TagId -------------------- 1 2 1 3 2 1 2 2 2 3 1002 1 ...
-4
votes
0answers
25 views

Q: optimization and performance [on hold]

I have sql server up and running now but it appears that sql is using quite a bit of memory. How can I check the settings and make sure I am running at optimal performance. Also, do you have any ...
0
votes
1answer
23 views

Is possible to use ORDER BY with IF ELSE condition?

Here I have some data. I want to show the records which have fav=1 at fast, then I want to show the data where the date is greater than the current date in ascending order(date) and at last the ...
0
votes
0answers
18 views

How to implement database versioning just like Rest API using dotnet core postgresql?

We are working on a Social App and wanted to implement back-end API with versioning feature and hoping database versioning as well. We decided to write the back-end with Dot.Net Core and the database ...
0
votes
0answers
24 views

ORA-16706: Data Guard broker worker process not available

Database version is 12.1.0.1. We have one primary database and two physical standby database and dataguard also configured. Dataguard sync is fine working between primary and physical standby.(No lag)...
0
votes
1answer
27 views

PostgreSQL. Shared_memory and sessions

I have a standalone server which is running Jira and PostgreSQL 9.6. I have noticed that PostgreSQL has shared_buffers parameter is equal 128MB where RAM is 32GB. Because server is shared with ...
2
votes
1answer
58 views

Splitting a year into pieces

I want to split a given year into given number of date range pieces using SQL. Following points need to be considered while splitting it: Given year will always start from 01-01 and end at 12-31 and ...
-1
votes
1answer
18 views
0
votes
1answer
25 views

How to delete all but the latest entry per customer in the fastest way?

I have a table containing around 4 million unique customers (with customerId each), but each customer can have multiple entries. There is no primary key. Each row has a creation dateTime saved as ...
1
vote
1answer
58 views

Sql server deadlock when insert and update in one transaction

I'm using Azure SQL + Java + Spring Boot 2. Currently I'm trying to understand the reason why deadlocks occur. In transaction I'm doing insert and later update on the same table by different rows. ...
0
votes
2answers
26 views

Can any database table name support double quotes "?

I need to know whether any database supports double quotes as a part of their table name. I am especially interested in JDBC compliant databases. And if they do, can I please know how?
0
votes
0answers
37 views

How to utilize an INDEX when filtering a VIEW?

edit: Double-posted this to Stack Overflow where I received some great input. Worth a look. I have a very simple query that executed surprisingly slow and the reasons turned out that it was doing a ...
0
votes
1answer
22 views

Why do I get an error when filtering subSelect?

select "masters".*, (select avg (rating) from "reviews" where "master_id" = "masters"."id") as rating from "masters" where rating > 3 [42703] ERROR: column "rating" does not exist
1
vote
2answers
45 views

Why is a COUNT query faster than a result set query?

Suppose I have two similar queries, with complex joins; query 1 returns rows and columns: SELECT col1, col2, col3, ...etc FROM MyBigTable LEFT JOIN AnotherTable1 On ... etc LEFT JOIN ...
0
votes
0answers
27 views

add index and change int data type to bigint in mysql 5.7 with 1.3 billion records the fastest way

how do i do this without downtime? or at least minimal downtime (3hrs max) Is there any way to tweak my.cnf config to at least make this process of indexing and change data type faster? Below is the ...
1
vote
0answers
25 views

Error writing to file c: windows\syswow64\mfc100.dll during installation of SQL Server 2014

During installation of SQL Server 2014 there is error appears Error writing to file c: windows\syswow64\mfc100.dll verify that you've access to that directory as you can see below image, I navigate ...
3
votes
1answer
97 views

pg_dump custom archive and create backup date

I have archive file with custom format created by pg_dump. Does it contain the backup creation date? I can't create backups by myself. I receive the archived files over specific network channel. ...
0
votes
0answers
12 views

LOAD DATA CONCURRENT LOCAL INFILE from another machine

I am managing a write heavy Mysql DB and the bulk load is the predominant workload that is happening in my system. Monitoring Logs are extracted as a raw file(each file 100MB) in the application ...

15 30 50 per page