Most Popular
1500 questions
94
votes
6 answers
Why would SET ARITHABORT ON dramatically speed up a query?
The query is a single select containing a lot of grouping levels and aggragate operations.
With SET ARITHABORT ON is takes less than a second, otherwise it takes several minutes. We have seen this behavior on SQL Server 2000 and 2008.
Jonathan Allen
- 3,582
- 7
- 24
- 25
94
votes
4 answers
How to drop all connections to a specific database without stopping the server?
I want to drop all connections (sessions) that are currently opened to a specific PostgreSQL database but without restarting the server or disconnecting connections to other databases.
How can I do that?
sorin
- 1,242
- 3
- 11
- 14
93
votes
4 answers
MySQL: Create index If not exists
Is there a way to create an index in MySQL if it does not exist?
MySQL does not support the obvious format:
CREATE INDEX IF NOT EXISTS index_name ON table(column)
ERROR 1064 (42000): You have an error in your SQL syntax;...
MySQL version (mysql -V)…
Adam Matan
- 11,659
- 29
- 80
- 95
92
votes
5 answers
Why can't RDBM's cluster the way NoSQL does?
One of the big plusses for nosql DBMS is that they can cluster more easily. Supposedly with NoSQL you can create hundreds of cheap machines that store different pieces of data and query it all at once.
My question is this, why can't relational…
fregas
- 1,051
- 1
- 9
- 7
92
votes
15 answers
Why is Database Administration so hard?
I know a lot of Database Administrators and they are all over 28-29 years old.
Is all database administration like that? I mean, is this about getting experience more than at least 7-8 years?
Or is being a database administrator so hard?
Soner Gönül
- 1,045
- 2
- 12
- 21
92
votes
4 answers
Are views harmful for performance in PostgreSQL?
The following is an excerpt from a book about db design (Beginning Database Design ISBN: 0-7645-7490-6):
The danger with using views is filtering a query against a view,
expecting to read a very small portion of a very large table. Any filtering…
ARX
- 1,419
- 2
- 14
- 15
92
votes
2 answers
How to get the current date without the time part
In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0
Piers Myers
- 1,033
- 1
- 9
- 9
91
votes
4 answers
Should I add an arbitrary length limit to VARCHAR columns?
According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT.
Should I add an arbitrary length limit to a name or address column?
Edit: Not a dupe of:
Would index lookup be noticeably faster with char vs…
Daniel Serodio
- 1,249
- 3
- 12
- 13
89
votes
6 answers
Do stored procedures prevent SQL injection?
Is it true that stored procedures prevent SQL injection attacks against PostgreSQL databases? I did a little research and found out that SQL Server, Oracle and MySQL are not safe against SQL injection even if we only use stored procedures. However,…
Am1rr3zA
- 1,523
- 1
- 14
- 10
87
votes
2 answers
Create index if it does not exist
I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?
This is a similar issue to the column creation one that is solved with…
GuidoS
- 1,037
- 1
- 7
- 7
87
votes
13 answers
How should I best name my timestamp fields?
When I am looking to create some timestamp fields (or other date/time style fields), what is the best way to name them? Should I just put record_timestamp?
garik
- 6,722
- 10
- 43
- 56
87
votes
4 answers
Why is my query suddenly slower than it was yesterday?
[Salutations]
(check one)
[ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer,
I have a (check all that apply)
[ ] query [ ] stored procedure [ ] database thing maybe
that was running fine (if applicable)
[ ] yesterday [ ] in…
Erik Darling
- 40,781
- 14
- 130
- 456
86
votes
4 answers
Monitoring Progress of Index Construction in PostgreSQL
Is there a way to monitor the progress of the creation of an index in PostgreSQL. I am creating an index on a large table and I would like to see how fast this is occurring.
Is there a way to monitor this?
myahya
- 961
- 1
- 6
- 4
85
votes
5 answers
Which database could handle storage of billions/trillions of records?
We are looking at developing a tool to capture and analyze netflow data, of which we gather tremendous amounts of. Each day we capture about ~1.4 billion flow records which would look like this in json format:
{
"tcp_flags": "0",
"src_as":…
somecallmemike
- 965
- 1
- 7
- 5
85
votes
10 answers
How do I use currval() in PostgreSQL to get the last inserted id?
I have a table:
CREATE TABLE names (id serial, name varchar(20))
I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I don't understand how to use it.
I have tried…
Jonas
- 32,975
- 27
- 61
- 64