2022 Developer Survey is open! Take survey.

New answers tagged

0 votes

How to convert data separated pipe to rows?

The problem here is that you have violated 1NF by storing multiple values in a single tuple. You are going to have to parse this into rows. You can use the built in STRING_SPLITfor this. select * from ...
user avatar
-1 votes

Logging Which Columns Have Been Read Or Written To

Not exactly what you are looking for but might help: Have a look at Brent Ozars sp_BlitzIndex: exec sp_BlitzIndex @DatabaseName='AdventureWorks2012', @mode=2; GO It will show you date and ...
user avatar
  • 31
0 votes

Creating new table for new fields

There's three ways I can think of that you can go about this, in order of preference: You have your application run the appropriate SQL scripts to alter the corresponding tables to add the new fields ...
user avatar
  • 24.3k
1 vote

How to deal with invoices that reference products that don't exist any more?

1: What is the best way to store data of a product in invoice? Is it by bringing the id of the product, or by bringing all info related to the product? Actually is it a good practice to store id of ...
user avatar
0 votes

one table is related to two other tables is that a good idea?

It is ok. Doctrine calls this approach Inheritance Mapping. https://www.doctrine-project.org/projects/doctrine-orm/en/2.11/reference/inheritance-mapping.html
user avatar
  • 422
1 vote
Accepted

Get the difference for a column value by all its previous rows data

Something like this, may be? sum(case transaction_type when 'debit' then -1 else 1 end * amount) over (partition by vault_id order by id rows unbounded preceding and current row)
user avatar
  • 21.2k
2 votes
Accepted

Junction table or denormalize?

A junction table isn't going to affect your performance here, especially with a process that only runs at a frequency of every hour. NoSQL doesn't buy you any advantage here either, and IMO should ...
user avatar
  • 24.3k
1 vote
Accepted

Data lake or data warehouse first?

There's a lot of similar and overlapping terms these days (Data Lake, Data Swamp, Data Warehouse, etc) that I wouldn't get too hung up over, IMO. Data Lakes are informal places to centralize different ...
user avatar
  • 24.3k
1 vote
Accepted

Marking/retrieving a long array of numbers as "true/false" in postgres

Both, bit and bit varying type can store a bit mask of 10 million bits (the upper limit seems to be 83886080). But in order to be able to set/get bits in there, you need to pre-initialize the value. ...
user avatar
10 votes
Accepted

Do datatypes of columns affect query performance?

Yes. And probably a lot. You never gain performance using character(n)(alias char(n)). You don't gain anything at all because that type is outdated, mostly useless, and discouraged. Related: Would ...
user avatar
1 vote

The implications of lack on indexes on supporting a large production database

As J.D. has mentioned indexes (or lack of) can have a significant impact on query level performance and it certainly warrants further investigation. In a nutshell the more that SQL can efficiently use ...
user avatar
2 votes

The implications of lack on indexes on supporting a large production database

Typically it's a good idea to index on the fields in the predicates (JOIN, WHERE, and HAVING clauses) of the queries commonly executed on your server or the queries that require executing with the ...
user avatar
  • 24.3k
2 votes

Foreign key refencing multiple primary keys

A FOREIGN KEY constraint can only ever reference a single target table by design. Maybe partitioning or inheritance fits your use case? If not, here are possible workarounds: Without master table You ...
user avatar
2 votes
Accepted

Azure Database Performance Throttling - Use Replication or?

Depending on which flavour of Azure SQL is being used you may be able to implement Resource Governor or equivalent. This allows you to allocate a portion of the hardware to each workload. The large ...
user avatar

Top 50 recent answers are included