Most Popular
1500 questions
120
votes
3 answers
Is it a bad practice to always create a transaction?
Is it a bad practice to always create a transaction?
For example, it is a good practice to create a transaction for nothing but one simple SELECT?
What is the cost of creating a transaction when it is not really necessary?
Even if you are using an…
elranu
- 1,303
- 2
- 9
- 7
120
votes
5 answers
Best database and table design for billions of rows of data
I am writing an application that needs to store and analyze large amounts of electrical and temperature data.
Basically I need to store large amounts of hourly electricity usage measurements for the past several years and for many years to come for…
Gecata
- 1,303
- 3
- 9
- 5
119
votes
3 answers
What is faster, one big query or many small queries?
I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then in the application sometimes, we only need to use only 1 column.
So would it be faster…
sudo.ie
- 1,291
- 2
- 9
- 5
119
votes
4 answers
Why are numbers tables "invaluable"?
Our resident database expert is telling us that numbers tables are invaluable. I don't quite understand why. Here's a numbers table:
USE Model
GO
CREATE TABLE Numbers
(
Number INT NOT NULL,
CONSTRAINT PK_Numbers
PRIMARY KEY…
Jeff Atwood
- 2,364
- 2
- 21
- 15
117
votes
5 answers
How to safely change MySQL innodb variable 'innodb_log_file_size'?
So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB:
mysql> show variables like…
Derek Downey
- 23,440
- 11
- 78
- 104
114
votes
11 answers
How to run psql on Mac OS X?
I installed PostgreSQL on a computer with Mac OS X using the One click installer. Then I try to access PostgreSQL using the psql command, but it doesn't seem to be available.
I get this message:
psql
-bash: psql: command not found
Do I have to…
Jonas
- 32,975
- 27
- 61
- 64
113
votes
3 answers
ALTER TABLE - Rename a column
This is driving me nuts!
I want to rename a column from read-more to read_more in my blog table
I tried all this:
ALTER TABLE blog RENAME COLUMN read-more to read_more;
ALTER TABLE blog CHANGE COLUMN 'read-more' 'read_more' VARCHAR(255) NOT…
Bojan
- 1,133
- 2
- 7
- 4
113
votes
6 answers
SQL Server Management Studio 18 won't open (only splash screen pops up)
I just installed SSMS 18 GA on a computer with only VS2019 installed, and when I try to open SSMS the splash screen will come up, but then the process exits.
Running ssms with the -log parameter reveals an error message:
CreateInstance failed for…
Mitch
- 2,638
- 2
- 18
- 24
111
votes
3 answers
What is the default order of records for a SELECT statement in MySQL?
Suppose you have the following table and data:
create table t (
k int,
v int,
index k(k)
) engine=memory;
insert into t (k, v)
values (10, 1),
(10, 2),
(10, 3);
When issuing select * from t where k = 10 with no order…
daisy
- 1,328
- 3
- 11
- 14
110
votes
5 answers
Storing vs calculating aggregate values
Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly?
For example, suppose I have widgets which users can rate (see schema below). Each time I display a widget I could…
BenV
- 4,893
- 7
- 39
- 38
109
votes
6 answers
Safest way to perform mysqldump on a live system with active reads and writes?
I'm not sure if this is true but I remember reading if you run the following command in linux
mysqldump -u username -p database_name > backup_db.sql
while reads and writes are being made to a database then the dump may contain errors.
Are there…
user784637
- 1,225
- 2
- 9
- 7
108
votes
6 answers
Difference between database vs user vs schema
What is the difference (if any) between a database, a user, and a schema?
Ravi
- 1,545
- 4
- 15
- 20
107
votes
2 answers
Connect to SQL Server with Windows Authentication in a different domain
I am trying to connect to a remote SQL Server on a VPN in a different domain. When I enter the Server name on the SQL Server and choose Additional Connection Parameters to add some extra stuff needed by my school:
Integrated Security=SSPI; User…
stergosz
- 1,195
- 2
- 9
- 7
107
votes
3 answers
What is the search_path for a given database and user?
I can see the current search_path with:
show search_path ;
And I can set the search_path for the current session with:
set search_path = "$user", public, postgis;
As well, I can permanently set the search_path for a given database with:
alter…
user664833
- 1,869
- 2
- 20
- 19
107
votes
7 answers
Why use both TRUNCATE and DROP?
In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them.
Many of my colleagues (almost all of whom are much more experienced than I am)…
user606723
- 1,526
- 4
- 14
- 16