Most Popular
1500 questions
30
votes
3 answers
ALTER primary key column from INT to BIGINT in production (MySQL 5.6.19a)
Some INNODB tables in our production database are about to hit the INT AUTO_INCREMENT limit of 2147483647 and we need to alter them to BIGINT otherwise writes will start failing.
The tables are in a production MySQL 5.6.19a database running on…
Mark Hansen
- 403
- 1
- 4
- 7
30
votes
2 answers
How to rollback when 3 stored procedures are started from one stored procedure
I have a stored procedure that only executes 3 stored procedures inside them. I am only using 1 parameter to store if the master SP is successful.
If the first stored procedure works fine in the master stored procedure, but the 2nd stored procedure…
user2483342
- 441
- 1
- 5
- 4
30
votes
2 answers
Best practice to shrink Tempdb in a production environment
What is best practice to use when shrinking Temporary db in SQL Server 2008?
Is it risky to use the following?
use tempdb
GO
DBCC FREEPROCCACHE -- clean cache
DBCC DROPCLEANBUFFERS -- clean buffers
DBCC FREESYSTEMCACHE ('ALL') -- clean system…
Mcol
- 449
- 1
- 4
- 4
30
votes
4 answers
Why does transaction log continue to grow in Simple recovery mode with nightly backups
Before immediately marking as duplicate, I have read Mike Walsh's Why Does the Transaction Log Keep Growing or Run Out of Space?, but I don't think it gave an answer to my situation. I looked through a dozen or so similar questions, but the…
DerekCate
- 438
- 1
- 4
- 7
30
votes
2 answers
Why would SELECT * be magnitudes faster than SELECT foo?
Consider a table of values and hashes, like so:
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
|…
dotancohen
- 1,085
- 6
- 16
- 27
30
votes
3 answers
Why query_cache_type is disabled by default start from MySQL 5.6?
We've upgraded to MySQL 5.6 and start seeing the loading of db server increased significantly, and finally found out the query_cache_type is defaulted to off start from 5.6.
We enabled it again and see the loading decrease, why this value is being…
Yoga
- 549
- 3
- 7
- 15
30
votes
5 answers
Column vs Field: have I been using these terms incorrectly?
I feel kind of embarrassed here, I've always used the terms "column" and "field" completely interchangeably, which recently caused some confusion in a technical discussion.
I was told, though, that this wasn't correct, that it should be…
BradC
- 9,964
- 8
- 48
- 88
30
votes
2 answers
Multiple primary keys in PostgreSQL
I have the following table:
CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);
When I…
mostafa
- 319
- 1
- 3
- 4
30
votes
4 answers
How to clone a user in SQL Server 2008 R2?
Is there a way to clone users' security and permissions in Microsoft SQL Server, preferably using the SQL Server Management Studio GUI?
sweetritz
- 361
- 1
- 5
- 11
30
votes
2 answers
What is the safest way to switch the binlog format at runtime?
Because of the following warning in mysqld.log:
[Warning] Unsafe statement written to the binary log using statement
format since BINLOG_FORMAT = STATEMENT. The statement is unsafe
because it uses a LIMIT clause. This is unsafe because the set…
quanta
- 1,006
- 9
- 22
- 36
30
votes
3 answers
Understanding time format of the EXPLAIN command - Postgres
When I run the EXPLAIN ANALYZE command on a given query, I'm having a difficult time interpreting the outputted time value. For example (actual time=8163.890..8163.893). Do the internal decimals represent repeating characters?? Sorry, this may be a…
Jmoney38
- 1,155
- 5
- 13
- 22
30
votes
2 answers
How to duplicate huge postgres table?
I have huge postgres table (10GB of data - 160M records). Table is static and there are no write operations on it performed. I want to duplicate it, perform writes, reindex it and then with single fast transaction delete the old one and rename the…
Milovan Zogovic
- 1,423
- 3
- 15
- 20
30
votes
2 answers
Why does SQL Server require the datatype length to be the same when using UNPIVOT?
When applying the UNPIVOT function to data that is not normalized, SQL Server requires that the datatype and length be the same. I understand why the datatype must be the same but why does UNPIVOT require the length to be the same?
Let's say that I…
Taryn
- 9,676
- 3
- 45
- 74
30
votes
3 answers
How to choose a collation for international database?
I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the query's results is ordering it according to the user's language during the query itself (because there are more than one…
Tae
- 457
- 1
- 5
- 8
30
votes
3 answers
Stored procedures vs. inline SQL
I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why.
I'd like to know the technical reasoning for this (in a way that I can explain…
webdad3
- 572
- 1
- 7
- 14