All Questions
101,214
questions
0
votes
1
answer
37
views
For a SQL Server page, is there a distinction between being "in memory" and "in the buffer pool"?
The following excerpt is in Chapter 10 of T-SQL Querying by Itzik Ben-Gan. He is listing the benefits of In-Memory OLTP tables over traditional pages.
You can also dispense with the considerable ...
0
votes
1
answer
34
views
Better way to return list of FK "parent" tables?
Here's what I currently do in Postgres 9.6. (I know it's EOL; that's not my choice to make.)
I don't want the definitions, FK names, or any of the other clutter you get with \d <table>. Just ...
0
votes
0
answers
21
views
Security Updates for Microsoft SQL Server OLE DB Driver
Actually I have silly question. If I install below update during working hours then is it occurring any interrupt ? Also I have checked release notes. But I couldn't find anything.
Security Updates ...
0
votes
2
answers
28
views
What is the correct index on a large dataset with group by?
I've been managing this very large data set for mlb data in mysql for awhile and am trying to rework the indexes to increase efficiency.
I have several instances I need to manage where I need to sort ...
-1
votes
1
answer
26
views
Pivoting 2 columns into a rows
I hope one can help me with this issue, I have a list of personnel and each one of them have same/different skills I need someone to help me to create a SQL script to change it
I have these two ...
1
vote
0
answers
12
views
PostgreSQL - Table partitioned on date doesn't prune partitions when querying with a daterange
Our tables are partitioned by month on the dt date column
CREATE TABLE IF NOT EXISTS ta
(
link_dir text COLLATE pg_catalog."default" NOT NULL,
dt date NOT NULL,
tod time without ...
0
votes
0
answers
12
views
MySql: Ecommerce product variant structure
I have a problem with the database organization for ecommerce product variations. Googling, I found a lot of articles and every article says that if the product has more variations, it should be in a ...
0
votes
0
answers
26
views
Optimize MySQL server configuration insertion
We are currently doing many insert/update transactions on our Mysql 8 server.
The duration of these transactions is really long and the problem is that we cannot predicate them (business choice)
We ...
0
votes
0
answers
25
views
SQL-Query get best incidents by solution_time for each month [duplicate]
I have the following SQL-Query in Metabase:
SELECT
date_trunc('month', "resolvedAt") AS "resolvedAt",
(CAST(avg("solution_time") AS float) / CASE WHEN 3600.0 = 0 THEN ...
0
votes
0
answers
20
views
SQL-Query get best incidents by solution_time for each month
I have the following SQL-Query in Metabase:
SELECT
date_trunc('month', "resolvedAt") AS "resolvedAt",
(CAST(avg("solution_time") AS float) / CASE WHEN 3600.0 = 0 THEN ...
0
votes
2
answers
32
views
Azure Disk performance optimizaton in SQL VM
We are using Azure SQL VM and we have multiple data files of multiple databases stored on same drive (let's say Drive E: of size 3 TB) which is created from 4 * 512 GB disks + 4 * 256 GB disks.
I was ...
0
votes
0
answers
13
views
How MySQL detects that an index is automatically generated?
MySQL creates an index for any foreign key column. Detecting those automatically created indexes is important for unattended migrations on servers.
For example if I manually create a new index on the ...
-1
votes
0
answers
9
views
SAGE ODBC driver - minimum required folder permisions
I have a website which I want to connect to SAGE ACCDATA folder (which resides on a different server but same network to the intranet server) using their proprietary ODBC driver.
For security purposes ...
-2
votes
0
answers
8
views
2023-09-25 12:27:05,811 [8474] barman.server ERROR: Check 'WAL archive' failed for server 'pgdb'
[barman@karthik doc]$ barman --config /home/barman/barman-2/doc/barman.conf check pgdb
Server pgdb:
WAL archive: FAILED (please make sure WAL shipping is setup)
PostgreSQL: OK
superuser or standard ...
0
votes
0
answers
6
views
Noarchivelog mode in logical standby
We are having one Logical standby for reporting purpose. Due to space issue I am planning to change the logical to NOARCHIVELOG mode. If i do that what will be the impact on the logical side.
I havent ...
0
votes
0
answers
19
views
Explain the following ER diagram in English sentences [closed]
Explain the following ER diagram in English sentences
-2
votes
2
answers
69
views
Better Approach for CLUSTERED vs NONCLUSTERED Primary Key
Suppose I have a table with below columns:
Id int identity,
UserId
What would be the better approach:
Making Id PRIMARY KEY CLUSTERED
Creating NONCLUSTERED Index on UserId
OR
Making Id PRIMARY KEY ...
0
votes
1
answer
62
views
How do I query out the largest value using an index
I have a table with several terabytes of event data in a very simple (id, bucket_id, data, created_at) schema and there is an index like so
create index index_events_on_created_at_and_bucket_id
on ...
-3
votes
0
answers
14
views
Cant take administration over mobile phone account
Phone keeps changing or deleating location blocks numbers forwards calls its said my locations were not rooted? And badic functions seem go revert i get mogged out of accounts Help me learn about ...
0
votes
1
answer
13
views
How do we recurse through this table?
I have a simple family tree table that includes:
PIN Numeric, primary key
FATHER_PIN ID of father
MOTHER_PIN ID of mother
I want to recurse through the tree with:
WITH RECURSIVE
parent_of(PIN, ...
0
votes
0
answers
19
views
MySQL 5.7 - could not establish connection (every 2 months or so) - how to log / ID issue?
I have a v5.7.42 MySQL server (community) running on Ubuntu 20.04.6 LTS - My issue is about every two months or so , the site will show could not establish connection to database, service mysql status ...
0
votes
0
answers
15
views
I need explaination about Develop Detailed Table Schematics In Step 4, Chapter 20, The Data Warehouse Toolkit, 3rd Edition
Have anyone read The Data Warehouse Toolkit, 3rd Edition written by Ralph Kimball and his colleagues? I signed up for a Data Warehouse course at my uni and my prof told me and my group to read Chapter ...
0
votes
0
answers
22
views
Slow mysql load times for database statistics in database table
I have a short url website where i have questions.
On the url i have user statistics enabled for the urls and urls with 20.000 clicks and lower takes about 5 seconds to load all stats while urls with ...
1
vote
2
answers
74
views
Database design for a limited historical data per user
I need to store data related to timer sessions. My MySQL table looks like this:
user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
timer_start_time TIMESTAMP
timer_end_time TIMESTAMP
There ...
0
votes
0
answers
35
views
How to use things like Elasticsearch, Meilisearch, etc. alongside a main database (e.g. PostgreSQL)?
I could be missing something obvious because this seems like a fairly basic question, but I haven't been able to find any explicit guidance anywhere on how to integrate Meilisearch/Elasticsearch with ...
0
votes
0
answers
24
views
Problem to start sqlldr with parfile=parfile.par
If have installed Oracle Client 21.11 under Ubuntu. Sqlplus is working fine so far. Only when I try to use sqlldr in combination with parfile it is not working.
when I call
sqlldr user.../pw...@...
0
votes
1
answer
35
views
Best solution for storing metadata and data from documents?
First of all do I want to mention that I'm fairly new to databases and big data.
Background:
I'm working for a large company who wished to unify all their documentation.
At it's core of my project am ...
0
votes
1
answer
33
views
Understanding Full Backup Process in SQL Server: Do All Allocated Extents Get Backed Up?
I've been reading a blog post by Paul Randal on the SQL Server backup process, where he outlines the main phases of a full backup. One point he mentions is that during a full backup, the process reads ...
0
votes
1
answer
16
views
How to estimate the number of IOs in mysql
Suppose I have a table with fields id and b, and create a non-clustered index b. The tree of index b is 3 levels high. I select * from table where b < 1000 and need to return the table. How to ...
0
votes
1
answer
28
views
What is the purpose/use case of log shipping monitor server?
When configuring log shipping, under the restore section we can configure alert to get a notification if restore doesn't occur on the secondary in x minutes.
So then, what is the purpose/use case of ...
0
votes
1
answer
16
views
MariaDB 11.1.2 for create a custom column for row number
Below two sql was working fine with MariaDB 10.5.8 and MySQL 5.7
SET @curRow=0;
select @curRow := @curRow + 1 AS row_number, `users`.`name` from `users`
or
select ROW_NUMBER() OVER (ORDER BY users.id)...
0
votes
0
answers
20
views
Effective use of FREETEXTTABLE and CONTAINSTABLE for phone number search
Let's assume that I have several columns for phone numbers - home, work, mobile, etc. These columns are free text, so numbers can be entered in various formats like '0044123456789', '+44 0123 4567 789'...
1
vote
1
answer
17
views
How can I sort by "release" in Mysql, where e.g. 10.10 comes after 10.9, with non-numerical entries both before and after?
If I have a table as per https://www.db-fiddle.com/f/arPEdUty3U6AJEQfqYEyRc/1 with a list of releases:
INSERT INTO `releases` (`ReleaseID`, `ReleaseNumber`) VALUES
(1, '10.6'),
(2, '10.8'),
(3, '_TBD')...
1
vote
1
answer
32
views
Not a duplicate, but still getting Integrity constraint violation: 1062 Duplicate entry
I'm using MySQL (ver. 8.0.32)
I have a table whose schema looks like this:
CREATE TABLE `players` (
`id` bigint UNSIGNED NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE ...
-1
votes
0
answers
7
views
User A is unable to run a Stored Proc created by User 2
I have a single node Percona XtraDB cluster with MySQL Version 8.0.32. We have secure groups with users defined, roles with execute permissions on the database and roles mapped to the secure groups.
...
0
votes
1
answer
49
views
How is SQL server engine account able to access disk drive folder without permission?
I have recently changed the SQL engine account to mydomain\sqlengine.
This evening I setup SQL audit into F:\audits. I'm surprised to see the SQL server is able to create a file in this folder without ...
-1
votes
1
answer
44
views
SSRS 2019 not available/working
I installed 2019 SSRS on 2019 SQL Server and after I configured everything in the configuration manager, I can't see the services for SSRS under sql server config manager and also when I tried to run ...
0
votes
0
answers
15
views
Column ___ of relation ___ does not exist in csv migration -- is Liquibase ignoring the schema I configured?
edit: I fought this too long and went with SQL insert statements instead, since I was generating the CSV from a JS script it was easy to translate over. I think there's a bug in this loadData command. ...
1
vote
0
answers
15
views
SQL Server Jobs - fail on connection timeout
We have a LIVE set of 3 SQL Server instances on one network and a DEV set of 3 SQL Server instances on another. Being on different networks; LIVE cannot talk to DEV however the instances can talk to ...
1
vote
0
answers
22
views
What are the post steps after changing SQL engine and agent accounts?
In a default SQL installation the engine account is NT Service\MSSQLSERVER, and agent account is NT Service\SQLSERVERAGENT
In SQL security I can see them both listed as logins with sysadmin role.
If I ...
0
votes
0
answers
16
views
Is local DAC enabled by default and does it need to be accessed using -A / Admin:?
I'm trying to understand DAC. Generally this is important when a connection cannot be made to SQL server.
DAC allows and prioritises the admin to remotely connect to SQL server. To use this the remote ...
1
vote
2
answers
78
views
Old server name still showing
I renamed SQL instance
exec sp_dropserver 'oldname'
go
excec add_linkedserver 'newname'
Rebooted server
select @@servername shows new server, but, exec sp_helpserver shows old and instance names.
In ...
0
votes
1
answer
49
views
Order by problem caused by the '_' character
I'm using MSSQL Server 2019 and I've some data like;
| FileName |
|----------------|
| DummyData._0 |
| DummyData._1 |
| DummyData._10 |
| DummyData._100 |
| DummyData._101 |
| DummyData....
0
votes
0
answers
27
views
Sql-Server Performance Degradation on a specific DB
I have a production Sql Server 2019.150.4316.3 instance (2 Core licence) which holds two mid active databaes (~200 concurrent users) along with low active DBs, installed on a VM (64 GB Ram).
...
-1
votes
0
answers
21
views
Compressed data is corrupted in postgresql
In one of our environment, we have postgres 9.5 and while trying to fetch data from the table we recieved an error stating
ERROR: compressed data is corrupted.
We have recovered the data using our ...
0
votes
1
answer
36
views
How can I perform a sanity check to confirm data is consistent after a repair?
How can i check for data sanity after performing nodetool repair on cassandra to validate that data is consistent.
0
votes
0
answers
12
views
MS SQL Server Replication - stored procedures sp_refreshsubscriptions and sp_addsubscription blocks all tables of a publication with SCH-M lock
The setup:
A push transaction replication is configured for about 20 tables/articles at SQL2019 CU31 Standard. Some of the tables are permanently under a load of thousends of inserts per second. In ...
0
votes
2
answers
54
views
When can we downsize Azure Virtual machine for SQL Server?
For now our production SQL Server is Standard_D96ds_v5 Azure VM machine. We got request to check if we can downsize server to Standard_D48ds_v5. What measures will take into consideration to have ...
1
vote
1
answer
78
views
How to use WITH in a better way to parametrise a date
I love to use CTEs to create nice and clear queries. However, I'm pretty sure the query I created is really inefficient.
Is there a better way to do this and keep things clear ?
with first_date as (
...
-1
votes
0
answers
38
views
How to Sort the column while using Lag() function in mysql
Using Mysql ,I am trying to sort the 2nd column ,week 8 should be coming up on top instead of week 23, .Can someone please help me .
I am trying to roll up the values for every week for the columns ...