All Questions

Filter by
Sorted by
Tagged with
13
votes
6answers
90k views

create incremental number in oracle sql query

how to create incremental number in oracle sql query without create any table ? I have tried using "with" clause, but I failed to get the expected result. I am using oracle 10g here is the code that ...
10
votes
3answers
2k views

What are best practices for safely permanently deleting a database?

We have an "organic" environment, meaning people piled code on code for ten years with minimal oversight or documentation. The server I use has several databases which I believe are no longer being ...
4
votes
3answers
10k views

How to import a varbinary(max) column with bulk import?

I have a varbinary(max) column that I need to bulk import. The data I'm putting into this column is a byte[] in C#. How do I format the data in the comma-delimited file used for bulk import? In ...
30
votes
5answers
37k views

In SQL, is it composite or compound keys?

About SQL (Computing/Databases): When we have two or more fields in a table that, together, identify its records uniquely, what's the proper way of calling them? Composite or compound keys? I've ...
4
votes
2answers
3k views

MySQL Replication Lag Behaving Erraticly

I've got a vanilla MySQL replication, using "MIXED" mode. The mysql slave lag calculation behaves very strangely - one moment it is 0 and the one after it it's 3630 seconds (or a similar number), ...
8
votes
2answers
453 views

Am I violating any NF rule on my database design?

I am a newbie on creating database... I need to create it for my recruitment web application. My application needs to schedule screenings, exams and interviews of applicants and save result in the ...
6
votes
1answer
2k views

Oracle concatenation of queries and function index usage

I have a problem with something I can't understand about indexes in Oracle 11g. We can create test data with: create table test2(field1 varchar2(100),field2 varchar2(100),field3 number,field4 ...
5
votes
2answers
276 views

PostgreSQL: Displaying inheritance relationship?

I want to know if a certain PostgreSQL table is a part of any inheritance relationship (i.e. is a parent or a child of other table(s)). Is there a query for that? Where is the inheritance data stored?...
37
votes
6answers
5k views

The smallest backup possible … with SQL Server

Daily we ship our SQL Server backups across the WAN. We need to minimize the size of these backups so it does not take forever. We don't mind if our backup process takes a bit longer; as it stands ...
18
votes
2answers
2k views

What can we do in MySQL 5.0 Replication to address bandwidth concerns?

I am developing an application to run on the client PC (Win) which is configured with a MySQL server 5.1 instance that will act as read-only slave to the remote master. The remote master has dozens of ...
5
votes
4answers
18k views

Sqlplus parameterized query via file

I would like to issue a parameterized to Oracle via sqlplus with all the the parameters provided in a text file. Is there a way to do this? The query has more parameters than is reasonable for a ...
11
votes
5answers
21k views

PostgreSQL: Creation date of a table

I've recently finished a project, during which many DB tables were created. Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables. Is there a ...
8
votes
4answers
954 views

Suggestion for Bulk Data Import

We are working on a project which requires import data from Excel spreadsheet on daily basis. The data will be import from pre-define template and what we're thinking, first we will upload data in ...
5
votes
3answers
1k views

Suggestion required for Large Database Optimization and performance

We are creating a large database with estimated growth rate 200 million+ records per annum. The database is use for analysis purpose and we will generate different tabular and graphical reports from ...
8
votes
3answers
2k views

Suggestion for Large SQL Server Database Design

We are creating a database in MSSQL 2008 R2 Standard where we will be storing a large number of records. We estimate 200 million+ records in one table annually and we are primarily INSERTing with very ...
10
votes
2answers
14k views

How to insert an IP-address into an inet column in PostgreSQL?

I would like to insert an IP-address into a column that has type inet. In what format can I insert the data? is it only binary or is there any way I can insert from text e.g. "192.168.1.082"? Are ...
6
votes
2answers
2k views

How can I corrupt a table in SQL Server?

I have to write a program that detects corrupted tables and tries to repair them. For this I need to corrupt tables regularly. Is there anyway to corrupt tables manually?
7
votes
3answers
3k views

DB2 passwords with special characters

Our DB2 authentication is integrated with active directory, so the allowed passwords are controlled by it's policy. We have some users that are not being able to connect to the database through ...
6
votes
5answers
663 views

SQL Server Backup Process Tuning

I am working for a small company that has 20 full time web sites. And another 20-30 micro sites. We are having issues with the sites freezing every day after the SQL Server Agent backup is running. ...
7
votes
1answer
376 views

How many disk seeks does it take to write one row on SQLite?

As far as I understand, there can be one disk-seek per rotation of a harddisk, therefore I am interested to know how many disk seeks are needed for writing a row to an SQLite table, including locking ...
1
vote
2answers
2k views

How fast should I expect to load data on a high performance computer?

I am considering a model where I use PostgreSQL COPY to copy data from a file into a table. I was wondering what kind of performance to expect on high-end hardware. An interval in MB/s would be nice ...
4
votes
2answers
4k views

How do you copy a table from MySqlServer_A to MySqlServer_B?

I have a daily task that collects data from 3 MySql Servers and then pushes that aggregate data back out to each of the servers deleting the previous days data. All servers are MySql. I would like to ...
7
votes
2answers
2k views

MySQL connection doesn't work anymore

I had two MySQL servers running (127.0.0.1:3306 and 127.0.0.1:3307). Now I get this strange error: mysql_connect(): Lost connection to MySQL server at 'reading initial communication packet' I can ...
19
votes
1answer
13k views

Does mysqldump export indices, by default?

I played around a little with mysqldump and I was wondering, if it does export indices (FULLTEXT, INDEX,...) by default. I read up on it and I found this option: --disable-keys, -K which suggests, ...
38
votes
4answers
41k views

Hard and Fast rule for include columns in index

Is there any hard and fast rule to decide what columns and in which order it should be put in Included in non clustered index. I was just reading this post https://stackoverflow.com/questions/1307990/...
10
votes
4answers
32k views

Optimising join on large table

I am trying to coax some more performance out of a query that is accessing a table with ~250-million records. From my reading of the actual (not estimated) execution plan, the first bottleneck is a ...
6
votes
1answer
2k views

Strange SQL Server 2008 behavior while using XLOCK and ROWLOCK

I wrote this script without COMMIT in a new Query window (Query1): BEGIN TRAN SELECT [RegionID], [RegionDescription] FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK) WHERE RegionID = 3 and in ...
3
votes
2answers
355 views

looking for a lighter alternative to cognos express [closed]

I have a client that has about 10 staff. They want to add business intelligence services to an existing MySql database. It is a database containing purchasing and readership data for publications ...
6
votes
2answers
2k views

How to change SQL sever configuration manager settings using TSQL?

SQL server configuration manager is used to configure certain settings like connection protocols, service start up, etc... Is it possible to make these changes that is done in SQL server ...
6
votes
1answer
2k views

How to fix discrepancies between publisher and subscription data?

I have a transactional replication between two SQL Server 2008 SP1 servers, both on Windows 2003 Server. I discovered that there are discrepancies between some of the tables of the main database and ...
4
votes
2answers
1k views

Using desktop computers to manage an SQLite database on a fileserver?

Sometimes, it can take months to get a proper database server up and running in large companies. They have all kinds of policies, which means that this process takes months. An alternative in the ...
11
votes
1answer
4k views

noexpand hint for non-enterpise edition and performance

I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says: Indexed views can be created in any ...
6
votes
2answers
4k views

SELECT statement with an explicit list of columns and then an asterisk (*)

SQL Server allows to use SELECT statements similar to: SELECT FirstName, LastName, City, * FROM dbo.Customers When I try to execute such a query on Oracle database, I get the following error: ORA-...
2
votes
1answer
18k views

How to backup and restore a database to a different location and name via command line only

I have a database that I want to copy from one location to another (maybe even to a different computer) via the command line only. The name of the database on the new location should not be identical ...
7
votes
2answers
2k views

Using totals on aggregates to improve performance

I have two tables: details and totals of these details. Details (Slow solution): select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , ...
2
votes
1answer
93 views

What commands are available to visualize the data pages on SQL Server?

From msdn: The page is the fundamental unit of data storage in SQL Server. An extent is a collection of eight physically contiguous pages. Extents help efficiently manage pages. This ...
2
votes
1answer
1k views

Auto Increment Index Field For Create and Update

I'm trying to create an unique auto increment index field that is re-generated on both inserts and updates. I need it so that a client can send a server an integer and get back all outdated (created ...
28
votes
4answers
327k views

How to update a table from a another table

I have one table A has column (id, field_1,field_2), and another table B has column (id,field_2) Now I want to merge table B to A, that means i want to update field_2 in table A to value of table B. ...
6
votes
1answer
4k views

Do I need to enumerate the columns used in a materialized view when creating the MV log?

I'm currently creating materialized view logs on a whole bunch of tables in order to support materialized views with FAST REFRESH. The materialized views we're creating contain approx 30 columns from ...
4
votes
1answer
1k views

Linked tables versus stored procedure performance in MySQL

Suppose I have a sub-form, in datasheet view for multiple records, that gives the user full CRUD control over a particular MySQL table. (The records are filtered by the parent-form). Option 1 is to ...
4
votes
1answer
2k views

Ms-access ODBC calls for linked table behaviour

I'm curious if anyone knows how ms-access goes about generating ODBC calls for linked tables. The reason I ask is that I'm trying to make the front-end of a system super efficient and too minimise ...
2
votes
1answer
637 views

Which NoSQL database(s) should be used for advertising platform? [closed]

What NoSQL database or combination of databases best meets these criteria? Very fast writes and reads Persistent data that won't get lost in a crash Plays nice with PHP Works well in the cloud (EC2) ...
5
votes
2answers
907 views

Best design for adding a folder/file relationship

I was given a database that has Projects and Media_files. They were in a simple 1 to many relationship. I now need to add Folders to Projects. Media_files can be placed in Folders, and Folders ...
5
votes
1answer
360 views

Migrating large database

What is a quick way of migrating 75G database to the cluster? I tried to test the backup and restore and it took more than 2 hrs. Business owners won't accept the long outage. Any idea how I can make ...
2
votes
1answer
2k views

Design of fact table(s) and dimensions tables for data warehouse

Hi i'm newbie in Datawarehousing, how would you model this in a Data Warehouse: i wish design the Data Warehouse which give the answers of statistics relating to a baseball league For players in ...
4
votes
2answers
4k views

How to mark an item as “Expired” when it's ExpiryDate value is in the past?

I have a table of items, each of which has a Status and ExpiryDate. What is the best way of enforcing this: when ExpireyDate > DateTime.Now update Status to "expired" I could have a stored ...
5
votes
3answers
2k views

What could be the reason for disallowing a sub query in the values clause?

For example SQL> create table dates(d date); Table created. SQL> insert into dates select sysdate from dual; 1 row created. SQL> select * from dates; D --------- 28-MAY-11 SQL> ...
5
votes
3answers
731 views

What are the sharding heuristics for MySQL - number of records per table & number of tables per instance?

Designing an object store for ~10B objects and using mySQL for storing metadata. We know we need to shard and are looking for general heuristics to scale. Would appreciate pointers and hear about the ...
51
votes
4answers
164k views

How to run psql on Mac OS X?

I installed PostgreSQL on a computer with Mac OS X using the One click installer. Then I try to access PostgreSQL using the psql command, but it doesn't seem to be available. I get this message: ...
3
votes
2answers
1k views

Mapping SQL Server integer to Oracle Datatype

Well I know, that an SQL Server integer is best represented by NUMBER(10). But sometimes I have migrate some maintenance scripts to Oracle and I wonder if I must carefully map integer to NUMBER(10) ...

15 30 50 per page