Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
55
votes
3 answers

Why does InnoDB store all databases in one file?

It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by default). I understand that InnoDB will map the…
Googlebot
  • 4,521
  • 24
  • 66
  • 95
55
votes
7 answers

If a person's name is Null then how would it break the database?

I was reading this article on BBC. It tells a story of a person named Jenifer Null and how she faces day to day problems while using online databases like booking plane tickets, net banking etc. I am not well versed in databases and I do not use it…
Souradeep Nanda
  • 685
  • 1
  • 5
  • 8
54
votes
5 answers

What does the GO statement do in SQL Server?

The GO statement from SQL Server caused me great curiosity and I don't really know how to use it properly. I noticed that queries with or without GO don't return errors and seem to work the same, so what is the purpose of it and why should I use…
Danilo Matrangolo Marano
  • 651
  • 1
  • 5
  • 7
54
votes
1 answer

postgresql: how do I dump and restore roles for a cluster?

Where are roles stored in a cluster, and how do I dump them? I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors: psql:mydump.sql:3621: ERROR: role "myrole" does not exist So apparently the dump of…
Rob Bednark
  • 2,163
  • 5
  • 21
  • 22
54
votes
3 answers

The database principal owns a schema in the database, and cannot be dropped message

I am trying to delete a principal from the database but can't because it owns a schema. When I go to edit the user, however, the box to uncheck schemae is blue and unremovable. How can I remove the principal from these schemas?
rsteckly
  • 901
  • 1
  • 7
  • 12
54
votes
2 answers

Is it a bad practice to have several mutually exclusive one-to-one relationships?

Say, a table car has one-to-one relationship to tables electric_car, gas_car, and hybrid_car. If a car is electric_car, it can no longer appear in gas_car or a hybrid_car, etc. Is there anything wrong with such design? Some problems that may occur…
Arthur Tarasov
  • 811
  • 1
  • 8
  • 13
54
votes
1 answer

Indexes: integer vs string performance if the number of nodes is the same

I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is searching for very specific attributes on a model. I…
Chris Cirefice
  • 643
  • 1
  • 5
  • 7
54
votes
4 answers

Check if a user exists in a SQL Server database

I'm working with SQL Server 2012. I want to check if a user exists before adding it to a database. This is what I have tested: USE [MyDatabase] GO IF NOT EXISTS (SELECT name FROM [sys].[server_principals] WHERE name…
VansFannel
  • 1,853
  • 5
  • 23
  • 36
53
votes
3 answers

How do I get back some deleted records?

I mistakenly deleted around 2,000,000 records from a remote SQL Server 2008 table. The server is not granting me access to the backup files on the server side. Is there any way to get back these records?
user755
53
votes
2 answers

What is the Maintenance DB asked by pgAdmin?

pgAdmin dialog for adding new database connections asks for a "Maintenance DB". In order to be able to connect, I set it to the database I want to connect (and also, I have the rights to connect). So, why is it named "Maintenance DB" instead of "DB"…
jgomo3
  • 664
  • 1
  • 5
  • 9
53
votes
5 answers

How to efficiently copy millions of rows from one table to another in Postgresql?

I have two database tables. One contains hundreds of millions of records. Lets call that one history. The other one is calculated on daily basis and I want to copy all of its records into the history one. What I did was to run: INSERT INTO history…
Milovan Zogovic
  • 1,423
  • 3
  • 15
  • 20
53
votes
3 answers

Huge slowdown to SQL Server query on adding wildcard (or top)

I've got a zoo of 20 million animals which I track on my SQL Server 2005 database. About 1% of them are black and about 1% of them are swans. I wanted to get details of all the black swans and so, not wanting to swamp the results page I did: select…
stovroz
  • 631
  • 5
  • 6
53
votes
2 answers

Does a re-index update statistics?

I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is: Does a re-index update the statistics? We found discussions online arguing both that it does and that it doesn't.
Thor Erik
  • 645
  • 1
  • 5
  • 6
53
votes
6 answers

Troubleshooting high CPU usage from postgres and postmaster services?

I'm using an open source (RHEL 6.2) based machine running SIEM software. When I run the top command, I see postgres and postmaster both with 96% CPU usage. Is there a way to pin-point or see what causing these service to stack up?
asadz
  • 635
  • 1
  • 5
  • 6
53
votes
2 answers

Optimizing bulk update performance in PostgreSQL

Using PG 9.1 on Ubuntu 12.04. It currently takes up to 24h for us to run a large set of UPDATE statements on a database, which are of the form: UPDATE table SET field1 = constant1, field2 = constant2, ... WHERE id = constid (We're just overwriting…
xyzzyrz
  • 651
  • 1
  • 6
  • 8
1 2 3
…
99 100