8
votes
2answers
4k views

What exactly is a database engine?

I have gone through the definition on http://en.wikipedia.org/wiki/Database_engine several times: A database engine (or "storage engine") is the underlying software component that a database ...
7
votes
1answer
942 views

Why can't we perform joins in a distributed database like Bigtable?

From this answer to What database does Google use?, BigTable is not a relational database. It does not support joins nor does it support rich SQL-like queries. Is this obvious? I do not ...
6
votes
2answers
689 views

Table contains multiple names for same company

I have a table of monetary transactions that has a field for who the money was paid to or received from. Transactions are recorded using info from paper invoices, paper reciepts, internet reciepts, ...
3
votes
2answers
141 views

Get a count of shippable orders

I have two tables: orders and order_lines. I need a fast, inexpensive way to display a count for orders that are going to be shipping today. The order_lines table is associated with the orders table ...
2
votes
2answers
4k views

Getting current date as part of an Stored Procedure

I have the following SP: SELECT moncallAdd.FirstListing, DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart, DATEADD(...
8
votes
2answers
7k views

Performance of a Trigger vs Stored Procedure in MySQL

A post here on DBA.StackExchange (What are the best practices for triggers to maintain a revision number on records?) has spawned an interesting question (at least, interesting to me) regarding ...
3
votes
2answers
3k views

Why is mysqldump file so large?

We have a 2GB MySQL DB. We started a mysql dump of this DB this morning and the resulting dump file is 27GB! Can anyone shed some light on what's going on here? Questions Why is this file so large?...
3
votes
4answers
208 views

how to interpret log files with high transactions

I'm having this issues with too many logs files being generated. Over 60gb in the last 24 hours. It's not a massively high transactional website. What is the best way to read the log files and work ...
10
votes
2answers
8k views

Which DBMS is good for super-fast reads and a simple data structure?

I am developing a product that, as a part of it's operation, must track a large number of files/directories. The idea is to store stat information in a database then, on boot, create watches for each ...
5
votes
1answer
891 views

Transaction and data consistency during a failure

When a database begins a transaction, all statements executed in that transaction are isolated and atomic (and consistent and durable). These are pretty much the definition of a transaction. ...
4
votes
3answers
950 views

What are the 'free to use' workload generators available for Oracle? [closed]

A couple that I know about are http://www.dominicgiles.com/swingbench.html and http://hammerora.sourceforge.net/ Are there any others? I am looking for tools what can generate random workload (...
2
votes
3answers
2k views

What are the best practices for triggers to maintain a revision number on records?

I have an audit trail implemented on an application database that captures old value, new value, modification time, and user inserting it into another table. I'm now wanting to add a revision number ...
6
votes
1answer
6k views

Difference between EXEC() and sp_executesql with no parameters?

I just learned about the new sp_executesql stored procedure. I like the fact that there's a way to execute parameterized code from within SQL Server. But, I'm wondering what the difference is ...
5
votes
7answers
498 views

Policies RE database triggers in well-designed applications?

I've heard all kinds of horror stories around gremlins living in database triggers, and--worse--systems being brought down by the addition of a trigger that caused a chain of cascading ones. I'm ...
9
votes
4answers
1k views

overstating field size in database design

I have some fields for my tables that are strings and at the moment, most of the field size have pretty high character limits. For instance, 100 char for the street name. Is there a penalty for using ...
3
votes
1answer
214 views

Minimum rowcount for filtered index?

Creating an index always has trade-offs. Whenever you insert, update or delete, it has to perform those operations on the index as well. This is all pretty commonly known. But with the advent of ...
2
votes
4answers
114 views

Is there a Storage-Engine/DB that logs all changes and allows selective rollback of specific changes?

Is there a storage-engine or database that naturally support this use-case: 1) Key-Value storage. Small Fixed Length Key, and value within [1KB, 64KB] 2) Access is read-mostly 3) Latency more ...
27
votes
1answer
10k views

Designing a platform: one database or multiple databases?

We are building a web platform that incorporates multiple services, each with its own underlying data. These services are being built independently following the principles of Service-Oriented ...
8
votes
4answers
1k views

Guidance for using composite keys to identify rows

Is it good practice (or would it have any adverse effects) to use a set of 4 columns to identify a row as being unique (one being a foriegn key, the other three being float data types)? I'm attempting ...
2
votes
1answer
294 views

Where to save user data?

I hope this is the right stackexchange place to ask. Im really new using databases and I have a little question: Where to save the user data? I mean, if Im logged as "Foxandxss" on my app, and I want ...
15
votes
2answers
6k views

How to create an index to speed up an aggregate LIKE query on an expression?

I may be asking the wrong question in the title. Here are the facts: My customer service folk have been complaining about slow response times when doing customer lookups on the administration ...
4
votes
1answer
285 views

InnoDB Foreign key updating vs. manual in-application updating

I've been working on a "legacy" schema in MySQL Workbench that uses a lot of relations. All of the tables are MyISAM, however, and there are no foreign keys. However, I'm working on a new area of the ...
5
votes
2answers
2k views

SQL Server database forum design sample

I am doing a design for a database using SQL Server 2008 R2, basically it is a simple database design for a forum (just like games fans forums). Fortunately I am struggling with it as I didn't find ...
20
votes
1answer
791 views

What is the reasoning behind the CAP theorem?

http://en.wikipedia.org/wiki/CAP_theorem http://www.cs.berkeley.edu/~brewer/cs262b-2004/PODC-keynote.pdf I think it is not very straightforward why only two of Consistency Availability Partition ...
14
votes
3answers
4k views

Can I move rows between partitions by updating the partition key?

I would think that this would be a fairly simply question, but I've actually had a difficult time finding an answer for this. The question: Can you move rows of data within a partitioned table from ...
16
votes
3answers
10k views

What does horizontal scaling mean?

In database context, I have come across horizontal scalability as one of the advantages of the NOSQL databases. What does the term mean? http://en.wikipedia.org/wiki/CouchDB http://en.wikipedia.org/...
21
votes
3answers
11k views

Adding columns to production tables

What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online: The changes specified in ALTER TABLE are implemented immediately. If the ...
1
vote
1answer
2k views

Install SQL server 2008 R2 with VS 2010

Ok, I've installed SQL Server 2008 R2. I'm trying to add a "SQL Server Database" to an ASP MVC project. But it keeps telling me I need SQL Server for it to work. What do I need to do to setup the ...
3
votes
2answers
361 views

How to track how many times the data is read?

I want to track how many times records are read in a specific table. Let's say that we have this table ID Name 1 Cat 2 Dog 3 Fish I want to know how many times the record "...
4
votes
2answers
1k views

sp_repladdcolumn - is it possible to supply a list of Publications to add this column on?

Scenario We have an existing table in 3 different publications (going to 3 different subscribers), 1 of those publications does not publish all columns. All of these are transactional pull ...
0
votes
1answer
267 views

Using SSAS for retrieval data instead of using SSRS to make a report?

If you work with Reporting service in order to create a report and you need to retrieve data from the relational database. The datamodel of the relational database can be structured in a certain way ...
4
votes
3answers
767 views

Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction" Does Oracle DB work around this issue? After all, Oracle owns ...
3
votes
2answers
3k views

How do I group results in batches of consecutive rows with the same 'module'

I'm looking for a seemingly very simple SELECT. I have a logging table LOGGING of which three columns are of interest to me: MODULE (text), PDATE (date), PTEXT (text). Actually, I care only about ...
14
votes
3answers
2k views

Is there any point in undersizing VARCHAR columns?

Googling around there seems to be mixed reports whether the size of a VARCHAR2 column in Oracle impacts performance or not. I would like to give the question of VARCHAR size a little twist and hope ...
14
votes
3answers
3k views

How often to backup the master database?

The suggestion in BOL is fairly vague: Back up master as often as necessary to protect the data sufficiently for your business needs. We recommend a regular backup schedule, which you can ...
5
votes
2answers
2k views

Estimate average and median efficiently in Postgres?

I have a Postgres database with tables in the billion scale. So any aggregate functions such as count() and avg(), as well as "order by random()" are very time consuming. Postgres has pg_catalog which ...
5
votes
1answer
5k views

Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

I am Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction". I am going to have to update a program to allow deadlocks. Is it possible that SELECT statement ...
10
votes
8answers
393 views

Pioneers in Database Research

Who all people have made life changing contributions to database research? I want to know more about them.
7
votes
1answer
5k views

Find all ancestor nodes of a HierarchyId using a single SQL statement?

I'm trying to find a way to get all ancestor nodes of a given node using HierarchyID. Every solution I've seen using HierarchyID seems to use either a CTE or a variable. Is there a way to do this ...
13
votes
3answers
6k views

What does a best-practice SQL Server maintenance plan look like? [closed]

I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the ...
9
votes
2answers
9k views

Which performance counters can you look at on a SQL Server Instance to determine it's performance and over all health?

I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the ...
14
votes
10answers
1k views

What are the top 3 performance Issues that you encounter with your SQL Servers?

I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the ...
19
votes
8answers
8k views

Why is it a bad practice to allow everyone to use the sa login?

Even Microsoft discourages the use of SQL Server authentication mode, but our applications require it. I've read that it's a best practice to not let users use the sa login directly, instead using ...
1
vote
1answer
485 views

SQL Server sp_configure options?

Does anybody know where I can find a list of all the parameter options for sp_configure? It isn't really jumping out at me when I search online for possible variations and the capabilities of ...
5
votes
3answers
392 views

Using DTA vs. evaluating DMVs?

Currently I am confronted with a production SQL Server database where someone had added almost all missing index proposals from DTA. Form How to determine if an Index is required or necessary I have ...
0
votes
3answers
286 views

What all are the recoverable points of failure in an Oracle database?

I am testing a product against an Oracle database for failure scenarios. The product talks to Oracle through a session and maintains a few tables. I want to make sure that whenever Oracle recovers ...
2
votes
2answers
609 views

70-432 : SQL Server 2008 Implementation and Maintenance [closed]

Has anybody taken this certification exam? If so, how many years of experience do you have as a SQL Server DBA and how hard did you find the exam?
2
votes
1answer
1k views

Installing MySQL server 4.1 on Ubuntu 11.04

how do I need to install mySQL server 4.1 on Ubuntu 11.04 server for a legacy app to run (it breaks on mySQL 5). sudo apt-get mysql-server-4.1 doesn't work - "package is missing, obsoleted or ...
1
vote
2answers
2k views

Why a copy of my.ini of other MySQL installation not working on my PC

I have two MySQL installations on two different PCs not connected to each other. I made some changes in the my.ini file of PC 1 and tried using this file on PC 2, but MySQL service fails to start. Why ...
1
vote
1answer
99 views

Can I install an additional oracle database on one node of an oracle cluster (RAC)?

Let's say I have a two-node Oracle RAC environment. Can I install an additional Oracle database on one of the node? Thanks.

15 30 50 per page