All Questions

1
vote
1answer
18 views

Foreign Keys with ON DELETE SET NULL are still deleted when TRUNCATE CASCADE is called on the foreign table in Postgres

Maybe I am missing something here: CREATE TABLE public.example_table ( id integer UNIQUE ); CREATE TABLE public.foreign_table ( id integer, example_table_id integer, CONSTRAINT ...
0
votes
1answer
22 views

psql table description

in psql if i do \d+, I get something like Schema | Name | Type | Owner | Size | Description --------+-------------------------+----------+--------+------------+----...
1
vote
3answers
73 views

Find first non-matching character between two strings

Posting this with solution provided. The use case could be framed in a number of ways: Return all characters that match at the start of two strings, until they do not match How many characters match ...
15
votes
1answer
904 views

Why does a simple loop result in ASYNC_NETWORK_IO waits?

The following T-SQL takes about 25 seconds on my machine with SSMS v17.9: DECLARE @outer_loop INT = 0, @big_string_for_u VARCHAR(8000); SET NOCOUNT ON; WHILE @outer_loop < 50000000 BEGIN SET ...
0
votes
1answer
25 views

Last value in two levels of group by in MySQL

I have the following data set: 2018-01-02 Oranges 1479 1480 2018-01-03 Oranges 1478 1504 2018-01-04 Oranges 1507 1494 2018-01-05 Oranges 1510 1551 2018-01-02 Apples 2150 2196 ...
-1
votes
0answers
8 views

JDBC: pooled connection not seeing new row

The problem here is easy to reproduce: 1) First a request creates a row. The front-end receives the row-ID. 2) Then another request is sent to read the row's data using the ID (like 200 ms ...
0
votes
0answers
22 views

MongoDB: Approach for storing frequently modified references

Need a piece of advice for storing a large number of references between Mongo entities that are frequently updated. Some background: 1 event can have 5000 logs. 1 event can have 10 assets associated ...
0
votes
1answer
16 views

How to manually set a return value to a subquery with multiple results

I have a subquery which can return multiple results if a customer has duplicated sales codes. I currently LIMIT 1 to just return the most recent but ... Is there away to return NULL or "DUPLICATE" ...
1
vote
2answers
37 views

I have a beginning balance I need to calculate ending balance running total by month rows

I have been searching this site and others trying to find an answer to this. I tried various things but cannot get my head around an answer so here goes. Here my data: What I need is to start with ...
0
votes
1answer
20 views

Update Statement on one table takes locks on other tables

Given the following statement on the AdventureWorks database: UPDATE Sales.SalesOrderDetail SET OrderQty = 100 WHERE ModifiedDate >= '2014-01-01' If I open another query window and look at the ...
0
votes
0answers
12 views

Using LTREE type when the final node has ~1000 descendants, which exist under other final nodes

Is it inappropriate to use LTREE type when the final node has ~1000 descendants, which exist under other final nodes? My data is essentially a directory tree, with the final level containing text ...
0
votes
0answers
6 views

Generate Postgresql schema using generator like an ORM (like ActiveRecord or Ecto etc)

I'm wondering if there is a software (a generator, a web page, an executable, a command line, etc.) that allows me to generate a Postgresql scheme since I'm not an expert and I wouldn't know where to ...
2
votes
0answers
44 views

Failure to create availability group

I"m having a problem creating a new Availability group in a windows cluster. I have 3 nodes in the cluster. Two nodes are running as a FCI and a single stand-alone node that's a member of the windows ...
1
vote
0answers
15 views

Postgres Row Level Security policy optimizes poorly compared to inline version

I have a query which looks like this: SELECT post.id, post.author_id, post.published_at, post.content FROM post WHERE post.group_id = 1 ORDER BY post.published_at DESC, post.id LIMIT 5; ...
2
votes
2answers
124 views

Permission on Database

I have a login user called TestUser created at the server level that is assigned to a read only role, now I need to create a user for the TestUser login on MyDb USE [MyDB] GO CREATE USER [Test] FOR ...
2
votes
1answer
57 views

Issue with adding a database to an Availability Group

I am having an issue with a particular AG where I am adding a DB (test123 below) to the AG and on primary it will show everything OK as follows: However, on secondary it shows with an exclamation ...
0
votes
1answer
30 views

Incorrect GrantedQueryMemory(KB) Value in SSMS Dashboard report

Just checking by to see if someone else has witnessed the same and tried a fix or has been fixed: Issue: I ran below query on stackoverflow2013 database select ParentId as [Post Link], count(...
0
votes
2answers
37 views

How to group multiple ROW as ARRAY

This is my current Query: SELECT innings_no, Player_Name, sum(NoBalls) as NB FROM ( SELECT A.innings_no, A.bowler, ...
0
votes
0answers
9 views

large dump: mysqldump: Got error: 2013: Lost connection to MySQL server

I need to dump to a text file a large database, which comprises one laaaarge table. I'm ding it like this: # mysqldump -p --single-transaction --quick --max-allowed-packet=2147483648 --tab=/mnt/...
1
vote
1answer
18 views

scheduler_count in Azure SQL DB

I'm trying to find a scheduler_count for my Azure SQL DB, so that I can get sp_Blitz to run. On all other platforms, scheduler_count can be read from sys.dm_os_sys_info, but this dmv is not available ...
3
votes
1answer
68 views

Fastest way to extract full table in Postgres

I'm trying to dump a table with 50M records to a file, and my goal is to reduce the time in which this action is performed. I usually use the COPY metrics TO 'metrics.csv' DELIMITER ',' CSV; This ...
0
votes
1answer
17 views

Having issue with window function COUNT counting duplicates

I have a query like so: select distinct(line_items.email) as email, count(orders.order_id) over(partition by orders.order_id) as num_orders from line_items inner join orders on ...
1
vote
0answers
13 views

How to handle schema changes in source systems when running an ETL?

How do people typically handle schema changes in source systems when running an ETL as part of an EDW? For example when a column you were operating on disappears and your ETL breaks.
0
votes
0answers
17 views

Azure SQL Query Analysis

I'm having a problem with long elapse times for queries in Azure SQL PaaS, running on the Standard Tier. We have some small External tables as a vertical shard to the database, and shared across a ...
0
votes
0answers
9 views

Should I use a composite key of member #, VIN, and transaction #, or just have the primary key of transaction #?

I'm trying to come up with a data model, and I'm stuck with this one table. It's named Rental_History, and it tracks the rental history of vehicles for a car rental service. My issue is this: ...
1
vote
1answer
54 views

Query Index question

I am trying to figure out how I can index this query below. The table is 155 columns. It has a clustered index on the column which is not in the query. The query is dynamically created from out of the ...
1
vote
0answers
10 views

Migrating Access Saved Import Specifications to SSIS Package

We currently receive a multi-hundred column flat file from a vendor, and the tech prior to me had created an import specification in Microsoft Access to automatically split the file into the right ...
0
votes
1answer
17 views

How to extract multiple key value pairs into distinct columns

I have a database that is outside my control, and am pulling data from it using Power BI for reporting. The database is a list of pages on a website, where each page is a new row, and the columns all ...
1
vote
0answers
9 views

dtexec returns immediately without any messages

I'm trying to setup a command-line call to dtexec to validate packages, using the project model. I'm calling dtexec like this: dtexec /val /proj my.ispac /pack my.dtsx /rep v Though the real ...
2
votes
1answer
20 views

How would I undo or edit the last line of a multi-line query in mysql?

I often make mistakes when entering multi-line queries in mysql. Is there an easier way to undo the last line instead of retyping the entire query? mysql> SELECT age, name -> FROM people ...
1
vote
2answers
27 views

How can I lock row for reading/updating in DB2?

I am trying to resolve a race condition in my application. I have a table that is used as a work queue and many threads and read/update it. The following query is executed from application. First it ...
2
votes
0answers
73 views

Database design for ~ 2.7B records per year / 26.000 data points every five minutes

The current situation We're currently looking into a new product that will send device data back to us to interpret. These are the numbers we're looking at: Devices will most likely send data every ...
1
vote
0answers
52 views

Designing a table for large amount of rows. No relationships

I am looking to design a database that'll allow me to store references to files. The table is pretty simple: Id (GUID)(PK) Path (nvarchar(250)) IsPublic (bit) FileType (int) The files can be ...
0
votes
0answers
15 views

one to many relation how to create a new row with mysql trigger

i'm really new to mysql triggers so...i have 3 tables: a,b and c. table a is related with table b with a one to many relaion and table c is the table that get the triggered infromation from the 2 ...
0
votes
1answer
27 views

List of databases which a user can access in SQL 2008 R2

Is it possible to create a list of all the databases where a specific user can access? For example, My user is 'Domain\MarySmith'. This user can access on a single database or can access many ...
2
votes
3answers
54 views

Identify proper index for group and order by date and hour (2 columns)

PostgreSQL 10.4 I have a table that has already an index on date column. Current query plan is doing a Bitmap Heap Scan using the existing date index. I would like to add a new index for this query, ...
0
votes
1answer
35 views

don't the users SYS and SYSTEM share the table dba_users?

While being logged in as SYS, I created a new user. I ran select username from dba_users order by username; and I was able to see the user that i just created; Then, I logged as SYSTEM, I ran the ...
0
votes
1answer
21 views

5M+ rows on a view

Hy, I have the following problem: Have 1 header row on main table which is the key on the rest of my structure. 4 rows on a customer table 198 rows on a market table 90 rows on a variety table ...
2
votes
1answer
54 views

sp_blitzCache against one stored procedure

Recently we started using sp_BlitzCache to help us tune our queries. We found one SP that had some issues with it and we were able to refactor it successfully. I have a copy of the output of ...
3
votes
0answers
18 views

Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?

I have a big table (30 millions rows) defined like this: CREATE TABLE `drawing` ( `id` int unsigned NOT NULL, `artist` int unsigned NOT NULL, `imageUrl` text NOT NULL, `uploadDate` timestamp NOT ...
0
votes
0answers
13 views

Oracle dblink to MS SQL Server

Can I create a dblink to a SQL Server from Oracle, in the same I would if I was creating a dblink from Oracle to Oracle? I've tried, but get a message saying - connection has closed as partner has ...
2
votes
0answers
20 views

Enabling Kerberos for Microsoft SQL Server Reporting Services

I am trying to enable Kerberos for MS SQL Server Reporting Services. I am fairly familiar with Windows security and how it works. However, I am new to Scale Out Deployment and Clustering of Windows ...
0
votes
0answers
16 views

Hibernate does not close idle session

I cannot kill session in idle status on postgres 10 This caused a very big problem during some test and fill postgres connection pull. After close connection, the session is in idle state and wont ...
0
votes
1answer
21 views

Intellisense Not Working in SSMS 2016

I am working with an Azure SQL Server database in SSMS 2016. I have Intellisense enabled in Tools -> Options -> Text Editor -> Transact-SQL -> Intellisense, however I have never had Intellisense ...
1
vote
1answer
43 views

How to get all dates belonging to a timestamp range

I have a tsrange as input. For example: tsrange('2019-03-04 20:00'::timestamp, '2019-03-06 20:00'::timestamp) I want to generate all dates belonging to this range. I started with the following, ...
3
votes
0answers
25 views

Slow Connection to Azure SQL Database using SQL Server Authentication

In SSMS, when connecting to any of our Azure SQL Databases using service accounts and SQL server authentication, the connecting itself can take between 10 to 15 seconds to establish. Once connected, ...
0
votes
1answer
24 views

Do you connect an application to the load balancer name or the cluster name?

My DBA delivered a 2 server WSCF for me to connect my sharepoint farm to. I have a load balancer name and a windows cluster name, and 2 server names. I realize I need to connect either to the ...
0
votes
0answers
4 views

PostgreSQL streaming replication - secondary access

I have read that the streaming replication can be set up so the secondary receives the read only queries, but it is not clear to me how that is "turned on"? With Informix I used a connection manager ...
2
votes
0answers
10 views

Where should I put my system databases in WindowsCore container

We're looking into migrating our SQL Server environment to use containers. I've successfully built a WindowsCore image with SQL Server installed. The instance is accessible with the data files using ...
1
vote
1answer
20 views

Select row with more of one type than another in a separate table

I have two tables. I'm attempting to select customers that have bought more of one type of product than another with at least one of the first type. Let's say for example that I want to get all ...

15 30 50 per page