Most Popular
1500 questions
22
votes
3 answers
Bad Practice to Store Calculated Data in Database?
Is it bad practice to store calculated data in each row, or is it better to calculate at the application layer with every read from the database.
Storing in the database avoids the need to calculate multiple times, but if an error is made then data…
Adam Thompson
- 459
- 1
- 4
- 11
22
votes
3 answers
SQL Server Distributed Availability Group databases not syncing after a server reboot
We're getting ready to perform a large upgrade on our SQL Servers and are noticing some unusual behavior with Distributed Availability Groups that I'm trying to resolve before moving forward.
Last month, I upgraded a remote secondary server from…
Taryn
- 9,676
- 3
- 45
- 74
22
votes
1 answer
How do I query the running pg_hba configuration?
I want to test if a replication connection is authorized by pg_hba.conf on the provider before issuing the replication-starting command, and don't know how. (I have access to both unix and postgresql shells on both nodes)
For the non-replication…
victorjtfranco
- 323
- 1
- 2
- 5
22
votes
2 answers
Extended event duration Millisecond or Microsecond?
There was a question in this forum about Extended Event module_end duration unit, which I answered. Details here.
Is it always in microseconds for all events?
SqlWorldWide
- 13,153
- 3
- 28
- 52
22
votes
5 answers
Anybody using HierarchyId in production? Is it reliable?
Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor recommend it, and Paul Nielsen advises against it…
A-K
- 7,244
- 3
- 32
- 50
22
votes
3 answers
Separate month and year columns, or date with day always set to 1?
I'm building a database with Postgres where there's going to be a lot of grouping of things by month and year, but never by the date.
I could create integer month and year columns and use those.
Or I could have a month_year column and always set…
David N. Welton
- 323
- 1
- 2
- 6
22
votes
1 answer
Creating a UNIQUE constraint from a JSON object
Lets take some example table peoples , that got only 2 fields: id and data(json).
SELECT data FROM peoples ;
{"name": "Adam","pos":"DBA","age":22 }
{"name": "Alice","pos":"Security","age":33 }
{"name": "Bob","pos":"Manager","age":42 }
I want to…
Chenko47
- 351
- 1
- 4
- 8
22
votes
1 answer
datetime2(0) vs datetime2(2)
According to the documentation datetime2 (Transact-SQL):
Storage size
6 bytes for precisions less than 3.
7 bytes for precisions 3 and 4.
All other precisions require 8 bytes.
The size of datetime2(0), datetime2(1), datetime2(2) use the same…
Zapnologica
- 779
- 4
- 9
- 19
22
votes
5 answers
Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?
Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns.
The database development started in the days of SQL Server 6.0.
One of the rules…
bernd_k
- 12,211
- 23
- 75
- 111
22
votes
2 answers
How to prevent statistics creation on a column?
I have a table with a column that I do not want statistics to be created or updated on. I get a better join cardinality estimate if I force the query optimizer to use density of statistics on the primary key as opposed to a statistics histogram on…
Joe Obbish
- 32,165
- 4
- 71
- 151
22
votes
1 answer
Clarification on MySQL innodb_flush_method variable
Let me begin by admitting that I'm very ignorant of the inner workings of hard disks. So when I read over the manual for the variable innodb_flush_method, it confused me. Can I get an explanation in layman's terms on the difference in O_DSYNC and…
Derek Downey
- 23,440
- 11
- 78
- 104
22
votes
4 answers
Where should you define foreign keys?
Is it better to define foreign keys in the database or in the code part of an application?
newuser
- 681
- 1
- 9
- 20
22
votes
1 answer
What is the difference between an after update and a before update in PostgreSQL
What is the difference between an after update and a before update in PostgreSQL? I couldn't understand the difference between after update and before update because it looks like the function was always executed before update.
So I made the…
oriaj
- 355
- 1
- 2
- 7
22
votes
2 answers
Why can't rows inserted in a CTE be updated in the same statement?
In PostgreSQL 9.5, given a simple table created with:
create table tbl (
id serial primary key,
val integer
);
I run SQL to INSERT a value, then UPDATE it in the same statement:
WITH newval AS (
INSERT INTO tbl(val) VALUES (1) RETURNING…
Jeff Turner
- 323
- 2
- 5
22
votes
4 answers
What is the default username and password to the oracle pre-built virtual machine?
I downloaded DeveloperDaysVM2016-06-02_13.ova from http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html and successfully loaded it into Virtual Box.
I logged in as "oracle" user and I can see the desktop.…
user1068636
- 421
- 1
- 6
- 11