Most Popular
1500 questions
79
votes
7 answers
Writing a simple bank schema: How should I keep my balances in sync with their transaction history?
I am writing the schema for a simple bank database. Here are the basic specifications:
The database will store transactions against a user and currency.
Every user has one balance per currency, so each balance is simply the sum of all…
Nick Chammas
- 14,670
- 17
- 75
- 121
79
votes
1 answer
MERGE a subset of the target table
I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning:
It is important to specify only the columns from the…
KutuluMike
- 1,589
- 2
- 14
- 13
78
votes
6 answers
How do I efficiently get "the most recent corresponding row"?
I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the rows of another table.
I have a table, inventory…
Tom Ellis
- 1,599
- 3
- 15
- 14
78
votes
3 answers
What is the difference between a connection and a session?
What is the difference between a connection and a session and how they are related?
jrara
- 5,333
- 20
- 56
- 65
75
votes
8 answers
MAXDOP setting algorithm for SQL Server
When setting up a new SQL Server, I use the following code to determine a good starting point for the MAXDOP setting:
/*
This will recommend a MAXDOP setting appropriate for your machine's NUMA memory
configuration. You will need to evaluate…
Hannah Vernon
- 70,041
- 22
- 171
- 315
74
votes
3 answers
What are the drawbacks with using UUID or GUID as a primary key?
I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this design since the UUID/GUID is quite large and they…
Jonas
- 32,975
- 27
- 61
- 64
74
votes
5 answers
Is there a name for this database schema of key values?
We process a routine data feed from a client who just refactored their database from a form that seems familiar (one row per entity, one column per attribute) to one that seems unfamiliar to me (one row per entity per attribute):
Before: one column…
prototype
- 853
- 1
- 7
- 6
73
votes
4 answers
Function Performance
Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company.
One of the things I would like to do is move some of the application…
Derek Downey
- 23,440
- 11
- 78
- 104
73
votes
7 answers
How to list all views in SQL in PostgreSQL?
How do I list all views for a database using an SQL command in PostgreSQL?
I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g.,
SELECT ...;
my_view_1
my_view_2
my_view_3
I'm running…
Rob Bednark
- 2,163
- 5
- 21
- 22
73
votes
10 answers
Why do people recommend not using the name "Id" for an identity column?
I was taught not to use the name Id for the identity column of my tables, but lately I've just been using it anyways because it's simple, short, and very descriptive about what the data actually is.
I've seen people suggest prefixing Id with the…
Rachel
- 8,477
- 20
- 50
- 74
72
votes
1 answer
What is the best way to reduce the size of ibdata in mysql?
I have some Production servers whose ibdata files increase in size day by day.
It has already consumed 290GB of space.
The tables in the servers are mostly InnoDB and there are high read and write requests.
The log file size also increasing. There…
Abdul Manaf
- 9,677
- 16
- 71
- 84
72
votes
6 answers
Disable MySQL binary logging with log_bin variable
Default MySQL config file /etc/mysql/my.cnf installed by some debian package using APT often set log_bin variable, so binlog are enabled:
log_bin = /var/log/mysql/mysql-bin.log
When I want to disable binary logging on such installation, comment out…
Nicolas Payart
- 2,368
- 5
- 27
- 35
72
votes
7 answers
What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE?
There is a long and quite elucidating answer on the differences between
TIMESTAMP WITH TIME ZONE
-vs-
TIMESTAMP WITHOUT TIME ZONE
available in the SO post Ignoring time zones altogether in Rails and PostgreSQL.
What I would like to know is: Are…
Marcus Junius Brutus
- 3,309
- 7
- 29
- 44
71
votes
6 answers
What are the differences between NoSQL and a traditional RDBMS?
What are the differences between NoSQL and a traditional RDBMS?
Over the last few months, NoSQL has been frequently mentioned in the technical news. What are its most significant features relative to a traditional RDBMS? At what level (physical,…
Spredzy
- 2,248
- 2
- 20
- 25
71
votes
6 answers
How restore a specific database from backup using mongorestore command
I created a backup of all my databases using mongodump command. Now I want to restore a specific database using mongorestore command.
How is this possible, I use this command: --db option then mongodb doesn't restore a specific database.
pankaj choudhary
- 841
- 2
- 8
- 4