All Questions

Filter by
Sorted by
Tagged with
5
votes
2answers
1k views

Separate Address Table, Latitude, Longitude

We are designing a DB for a limited functionality social networking application. Users (Registered Users), Retail Outlets, etc are several entities we need to store. We need to store Address of each ...
1
vote
1answer
2k views

Usefulness of Foreign Key WITHOUT reference option

Sorry, if this is naive question, but I searched a lot and did not get a vivid answers. Consider a simple InnoDB FOREIGN KEY without ON DELETE or ON UPDATE I think this will avoid INSERT of a value ...
4
votes
1answer
4k views

How can I prevent gaps in a MySQL table AUTO_INCREMENT field?

For example, I have a table for user registration: CREATE TABLE IF NOT EXISTS test_user ( user_id int(10) unsigned NOT NULL AUTO_INCREMENT, user_name varchar(50) NOT NULL, PRIMARY KEY (...
0
votes
1answer
96 views

map non unique attribute to a unique attribute

I have a mapping table that uses a non unique attribute. I need a way to map that attribute to one of the attributes it is referring too, to get the unique attribute. Here is an example of what i am ...
2
votes
1answer
2k views

MySQL using trigger instead of check

Is there any way that I could use trigger instead of check? like I have CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER test BEFORE UPDATE ON apply FOR EACH ROW BEGIN ...
3
votes
1answer
215 views

Managing schema changes between transactional and analytical databases

How would you manage changes in a transactional database that affect an analytical database? Environment: Transactional database is a current, or near current version of MySQL. Size of the ...
9
votes
2answers
2k views

PostgreSQL Complete Database Backup [duplicate]

I've been trying to figure out how I can completely backup my PostgreSQL database. I have not seen any resource that explains how to do a complete backup. I am aware of the pg_dump and pg_dumpall ...
8
votes
2answers
1k views

Do agile software development approaches apply to SQL?

I would like to learn if agile software development methods/principles/patterns are valid to SQL programming as well. If yes, where would be a good place to start learning about that? Are there any ...
2
votes
2answers
932 views

Attempting to avoid SORT in query planner

I have a query that is used VERY frequently in my application. Without modifying the query itself, I'm trying to optimize it by toying with different indexing schemes. Here's the query (basically): ...
4
votes
1answer
343 views

Merge Replication Large Table

We have a two SQL Server instances that use merge replication to keep in sync. There is a single table that potentially can add millions of rows in between syncronisations (which is a relative small ...
2
votes
1answer
2k views

Escaping a single quote in SQL Server

The recommendations I have come across advise that single quotes in SQL Server should be escaped using two single quotes. Thus Ravi's becomes Ravi''s. Another alternative is to use parameters. In ...
13
votes
5answers
12k views

What's a good tool for visualizing SQL Agent jobs? [closed]

I find the built-in MS SQL Studio tools for managing Agent jobs to be a little frustrating. What tools out there have you found helpful? Offhand, there are three things I'd like to see in such a ...
1
vote
1answer
85 views

Is it safe to change the name of a database?

I have a SQL Server database which has data for several customers/devices. We are changing the prefix of our website (asp.net) and our application dynamically loads the proper connection string based ...
5
votes
2answers
373 views

How to find out what is causing my Identity column to keep going up

I have a table with an int identity as the PK. Something is causing it to increment, and I'd like to know what that is. To give a quick example, yesterday I inserted a record and got ID 41,773. Today ...
3
votes
2answers
3k views

What is the difference between a SQL Login and a Database User in terms of security?

I am currently working with a consulting agency to set up a new production environment. They have set up a separate SQL login for each database. However, there is no corresponding database user ...
7
votes
2answers
4k views

Using Foreign Key and References for ON DELETE CASCADE in mysql

I was thinking that these two ways for creating foreign keys are the same CREATE TABLE child1 ( id int(11) not null auto_increment, parent_id int(11) REFERENCES parent_table(parent_id) ON DELETE ...
4
votes
1answer
106 views

Issue with CAST using linked servers (possible bug?)

So I've got a some SQL Server 2008 DBs arranged in a star schema. Server A is the one users submit queries to. On Server A there is a DPV called Detection that's basically a UNION of table Detection ...
3
votes
1answer
12k views

How can I call a remote User Defined Function with Parameters?

We have one SQL server which contains a link to another server that is on a different box. I would like to create a user-defined-function on ServerA which returns the value of the udf on ServerB, ...
2
votes
1answer
109 views

Is there a restriction which prevents you from creating a user and a role with the same name?

I have a (SS2005) database with a user named MY_APPLICATION_USER. I'm trying to create a role in that database named MY_APPLICATION_USER. When I try to do that using this script: /****** Object: ...
2
votes
1answer
1k views

PostgreSQL partitioning query performance inexplicably bad

I find this a bit mysterious. The cost of getting the maximum value out of just the partition parent table (which is empty) is very cheap: evlampts=# explain select max(timestamp) from only mcdata; ...
0
votes
1answer
55 views

Start Windows Service If Replication Fails

Setup: We have SQL Server Management Studio 2005 SP3 on all of our Servers. We have 1 Main Server, and 1 Secondary Server that has the database from the Main Server being replicated on it. (If it ...
2
votes
1answer
1k views

Number of locks exceeded when adding foreign key constraint

I'm adding a foreign key constraint to table a (17mil rows) that references table b (15mil rows): >ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b (id); ERROR 1206: The total number of locks ...
4
votes
2answers
2k views

Is this procedure to Failover to Logshipped secondary and Fail back again procedure correct?

I have a primary database in Amsterdam (AMDB), and a secondary in London (LNDB). I want to fail over from AMDB, to LNDB and then back again, without having to make another full backup (because the ...
1
vote
2answers
8k views

How do I determine why my stored procedure is getting deadlocked?

We have a stored procedure that our application uses to query data (no write operations are performed). I have the complete staging database copied to my local version of Sql Server developer edition. ...
0
votes
2answers
556 views

shall I use multiple separete tables or one table with multiple partition on oracle

I have an entity lets call it Response, and another entity called ResponseType, Response entity contains the ResponseType; so when I am building the physical model I confused with how to implement it; ...
0
votes
1answer
730 views

Database Design: What is the best structure for storing site offices and locations?

Background I am creating a database driven web application for the management team at the place I work. Part of this application requires fairly specific locations to be linked with where equipment ...
2
votes
2answers
14k views

SSIS SQL Server 2008 errors

I just want to know how to import data from source to SQL Server 2008 database using SSIS. I have a simple txt file (actually it's from this blog : http://blog.sqlauthority.com/2008/02/06/sql-server-...
2
votes
2answers
3k views

Postgresql 9 table lock resolution

I have an ALTER TABLE statement that according to pg_stat is "waiting", and I'm wondering if there is an easy fix to allow the statement to execute? From my limited understanding I think it's a ...
3
votes
1answer
1k views

mysql, changing tables from myisam to ndb

Gents, Converting a table from myisam to ndb via: ALTER TABLE piwik_log_profiling ENGINE NDBCLUSTER; This will fail with: BLOB column 'query' can't be used in key specification with the used ...
0
votes
1answer
49 views

Copying a subscribed database to the publisher server

I have two servers. I have installed a merge replication in the publisher (Server A) and I have added two subscriptions with the same database name. - One on the publisher itself and the seconde one ...
2
votes
3answers
6k views

Reference a newly created computed column in the same query where we create it

SELECT (SUBSTRING(Column1,1,3)) AS ComputedColumn WHERE ComputedColumn !="dec" INTO Table2 FROM Table1; This is an example where I have a computed column (which is essentially created by using an ...
3
votes
2answers
9k views

Deadlock on Insert/Update

Using SQL Server 2008 R2: I'm currently attempting to track down the cause of a deadlock issue we have. Not sure where to turn. Here's the setup. TABLE Scores: id INT IDENTITY(1,1), first_name ...
19
votes
4answers
13k views

SQL Server database on an SSD - any advantage to a separate file for every table?

I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
2
votes
2answers
280 views

Overhead of DBMS_Profiler

If I use DBMS_Profiler to profile a long running (5 hour) PL/SQL script, how much longer should I expect the script to run with profiling? I realize this will be dependent on what the script is doing,...
1
vote
1answer
14k views

How to set up tns listeners for Multiple Oracle Installations on One machine

So I have two oracle installations on a development/testing machine (10g and 11g, respectively). The platform is RHEL4. Now I need to have both these oracle up and have tns listeners for them ...
3
votes
2answers
12k views

Innodb UPDATE performance

We recently switched one of our tables to innodb and now we are experiencing very slow UPDATE execution times. An update which used to take 0.010-0.030 seconds can now take over 70 seconds. Some ...
1
vote
4answers
234 views

Database growth causing issues sending backup over network

We take a full database backup on our primary SQL Server, and send that file over our network to a remote site where it is recovered on a backup SQL Server. We use this backup database for slow ...
4
votes
1answer
912 views

Sql Server Index Data File Drive Location

As part of my ongoing saga of migrating from one SAN to another, the new SAN vendor said that I need to have my index data file in the same drive as my primary data file. Otherwise, given the tools ...
4
votes
1answer
2k views

Can SHOW TABLE STATUS query performance on MySQL be improved ? - Amazon RDS

I have a fairly large database on the Amazon Relational Database Service (RDS) running on MySQL 5.1.57. There are 900+ tables, so that running the "SHOW TABLE STATUS" query can take up to 30 seconds. ...
3
votes
2answers
3k views

MySQL connection count much larger than query count

I'm maintaining a relatively simple LAMP server. It does how ever have a some what high load. What I'm noticing is that in a one hour time span there are roughly 50k connections made to the mysql ...
2
votes
2answers
2k views

Cancel SQL Job execution make automatic rollback

I have a SQL SP which has a set a transaction. All the statements handled inside the TRY CATCH block. If we explicitly cancel the job by close the button in the JOb execution window, will it get into ...
9
votes
1answer
4k views

Schema designing for handling multiple payment gateways

This is more of a question that requires feedback. I'm designing a database that handles multiple payment gateways. A payment gateway mostly requires a table for order details before making the ...
1
vote
1answer
766 views

Database Tuning Advisor can't find tables across two databases

I've got a Python script which accesses two SQLServer 2008 databases. If I run it and take a trace to capture all events that occur as a result of the script, and then put that trace through the ...
2
votes
3answers
1k views

Which database is best for deeply embedded database or through C DLL?

I want a deeply embedded database. Deeply embedded means that the server is started by an application and closed by the application with no tcp/ip or 0 port. The main features of consideration are: ...
2
votes
1answer
2k views

How can I use a MySQL query in an openquery query on Microsoft SQL Server.?

I have the following query which works fine in MySQL: DELETE sb FROM stockbatches sb INNER JOIN batchestodelete btd ON sb.batch_batch = btd.batchno AND sb.batch_part = btd.batchpart ...
3
votes
5answers
30k views

How to check the status of SQL Server?

I need to check the status of SQL Server. When I say status its not the status for each database, but rather for the whole SQL Server. I need to know what are the queries running in the server and ...
4
votes
4answers
9k views

How to create rolling backups for SQL Server 2008 R2

I want to run daily backups for a set of databases to disk such that I always have 3 days’ worth of backups kept for each database, with the oldest backup being deleted or overwritten each day. I set ...
1
vote
1answer
1k views

How do I restore a SQL backup, while excluding a specific table?

How do I exclude a specific table during a SQL backup RESTORE operation? My question is specific to restoring a backup sql file. I am using Putty and bash server.
3
votes
2answers
2k views

Move Oracle data to and from MySQL with the help of PHP?

My application is in PHP which by default uses MySQL db. I have connected MySQL & Oracle dbs with PHP. Now I want to move data from Oracle db into Mysql db through PHP. Is it possible to do? Is ...
8
votes
1answer
1k views

How are deadlocks detected and reported in an RDBMS?

I was given this essay type question during an interview but did not get the job. The full question was as follows: How are deadlocks detected and reported in an RDBMS? What are the transaction ...

15 30 50 per page