8
votes
3answers
718 views

Is there a performance benefit to placing transaction log files on a separate drive?

There are many blog posts and best practice articles extolling the virtues of placing the SQL Server data file on one hard drive and the transaction log on another. The reason given is that that the ...
-3
votes
2answers
636 views

SQL Server equivalent to MySQL UNIQUE index?

In MySQL (5.1) I am able to create a Index on a field that is type UNIQUE. This ensures that the field contains only unique items. Is there something equivalent to that feature in SQL Server 2008? ...
1
vote
0answers
96 views

User defined content specific permissions with varying conditions. How to design tables? [closed]

I'm working on a privacy settings problem, which is proving to be cumbersome to design. Essentially I have: User created content User can define access groups for content. Access groups vary ...
8
votes
4answers
26k views

Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes

Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus. Here is a worst case example containing multiple ...
3
votes
4answers
3k views

migrating tables from Postgres to SQL Server 2008

(I fear what I wish to accomplish shall not be easy, but a lot of my googling around about it yielded rather dated results, so here it goes...) I have a Postgres 8.4.x database with over 100 tables. ...
3
votes
2answers
240 views

Will removing a 2nd log file break log shipping?

We have a large database 200GB+ to which we added a second log file to cater for growth during an upgrade. This is now no longer needed so we want to remove it. Normally this would be a simple ALTER ...
2
votes
1answer
77 views

Log Custom SQL Results During Maintenance Plan

I would like the last step of my Maintenance Plan to execute a simple custom SQL script (SELECT * FROM <SomeTable> WHERE <Condition>), and save the results to a file (Text, CSV, etc). Is ...
4
votes
2answers
178 views

ProcessID Owner with third party app

I am having problems trying to audit a third party app with a SQLServer backend. The app manages users and user access via a SQL table, using a single SQL login to access the databases. I am trying ...
6
votes
1answer
15k views

Cannot connect to DB2 database after restoring an offline backup

This is for DB2 9.7 Enterprise Server Edition on AIX. I had a full, offline backup taken of several databases within our instance in a performance environment. I wanted to restore these databases to ...
1
vote
2answers
970 views

Commercial database systems versus open source database systems [closed]

Can anyone tell me what are the reasons for buying a commercial database system and not using an open-source free version? The reason for my question is that: Commercial systems cost upwards of ...
5
votes
1answer
3k views

Bit vs. Boolean columns

Given that bit fields are just binary representations of data and need to be queried in slightly "strange" way. Does it actually provide any benefit using a bit field for a boolean value? From what I ...
5
votes
2answers
238 views

Group_by and having vs Joins when table is self joined 5-6 times

Now the situation due to which this question came to me. Schema is Table User_Read_Book user_id | book_id Now I want to get users who have read certain books. Say give me users who ...
2
votes
1answer
178 views

OVERLAPS for Informix

Does Informix have a function equivalent to OVERLAPS, which finds out whether or not two time intervals have any time in common?
3
votes
2answers
520 views

Editing built-in templates in Management Studio

I'm running SQL Server Management Studio 2008 (10.0.5500.0) and was attempting to edit the built-in template "Create T-SQL Trigger (New Menu)". I open the Template Explorer, right-click the template, ...
5
votes
1answer
5k views

mySQL query optimisation — multiple joins or select … where not in (select distinct…)?

Background I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
4
votes
2answers
2k views

DB2 Express-C on Mac OS X

I have followed all the steps described on the Getting Started with DB2 Express v9.7 e-book and installed DB2 Express-C on my Mac. The installation succeeded without a problem. I did setup OS X ...
2
votes
1answer
9k views

Reduce Size of SYSAUX

I am trying to reduce the size of our SYSAUX Table. Currently the largest four occupants are below. As you can see it is mostly the Server Manageability items. select occupant_desc, ...
2
votes
1answer
836 views

InnoDB - How to get top locked tables and rows which are locked

I was searching for a tool OR query which can give me top locked tables and which particular row is locked, Is it possible to get ?
2
votes
1answer
335 views

Redesigning a multi-instance MySQL server for performance and resiliency

I've got a single MySQL server which currently runs N separate MySQL instances on, which are rapidly growing beyond the capabilities of that box. Is it possible to have an active-active MySQL ...
1
vote
1answer
85 views

Have less tables with a lot of data or a number of tables with smaller amounts of data?

Lets say I have a number of tables that will have a type and status and I want the types/statues to be configurable by a end-user of the application (so I can't use enums here). There are 2 different ...
2
votes
2answers
164 views

Order by sub select

I have 3 tables: articles, paragraphs, notes An article may have many paragraphs, and a paragraph may have 0 or 1 note. Now I want to find all notes belong to an article: select * from notes where ...
7
votes
3answers
9k views

How to convert a MySQL database to PostgreSQL?

Is there any command line tool for converting the MySQL database to PostgreSQL? I'd also like to know if there is a way to convert the database using the normal mysqldump command.
4
votes
2answers
4k views

How to toggle a boolean field by sql in postgresql?

There is a field show which is a boolean type in postgesql. I want to write a sql to update that table, to toggle the value of show. If it's true, it becomes false, if it's false, it becomes true. ...
0
votes
1answer
268 views

How do oracle date time work?

I have following where condition in query. What the time is between T1 and T2 means it includes or excludes the boundary. Also between T1 and T1 means what?I am confused as both returns result. ...
2
votes
1answer
247 views

User Rights - Database Security

I am having following scenarios and i am having some issues and some questions. We have a DB Server on which we have some databases in these 2(two databases) are for my users. Now i have created 2 ...
4
votes
1answer
194 views

Spatial data modelling tools [closed]

Is there anything like MySQL workbench for PostGIS? Something that supports SQL output and geometry types would be nice. CLARIFICATION: I am trying to implement the Land Administration Domain ...
1
vote
1answer
2k views

“show create table” output with out “show”?

What I'd like to be able to get the SQL statement by 'show create table' as something I could do something with along the lines of insert into mytable (myTextColumn) (show create table blah) I've ...
8
votes
1answer
805 views

Why does the fill factor in SQL Server default to 0 (100%)?

I understand fill-factor, pages and index structure and I therefor understand why a 100% fill factor is a rare best practice. So why does it default to 0 (or 100% ) by default? Why not 90 or 95? Is ...
2
votes
0answers
572 views

How do I auto-fill data in Microsoft Access 2003?

I am creating a Purchase Order Form at work using Microsoft Access 2003 so that it can be saved and archived in a database and reduce paper costs. These are my current dilemmas: I am unable to ...
4
votes
2answers
523 views

Maintaining referential integrity in a booking system

I am developing a second version of a corporate training booking system on Microsoft SQL Server 2005. I have 3 tables (simplified for this question). Table 1- CourseSize table- this determines the ...
2
votes
2answers
1k views

How do I recreate a deleted logfile on an Oracle database?

For my database assignment we were looking into "database corruption". I was asked to delete the second redo log file, which I have done with the command: rm log02a.rdo this was in the ...
3
votes
3answers
1k views

SQL Join slower than subquery on View with a Function

My database has a view that calls a function. Joining on that view is surprisingly slow. Is there a way to speed up joining to a view like that? I've included 2 queries which illustrate the issue. ...
6
votes
1answer
486 views

How to set indexes on a table with a FILESTREAM ROWGUID?

I'm confused with the requirement for a ROWGUID column on a table with FILESTREAM. How is this column going to be used? Would it be okay if I used a different primary key (clustered) on the same ...
2
votes
1answer
600 views

Maintenance Plan Takes Too Long - Locks Tables - Indexing To Blame

System: I have a Maintenance Plan that Rebuilds Indexes for Several Tables (10 tables, 50M records total). Issue: During the Index Rebuild (~20 minutes), we fail to insert data into the DB ...
5
votes
2answers
3k views

No luck trying out Firebird 3.0

Today I though I'd give Firebird 3.0 a shot, but for some reason I can't seem to get it installed - at least not in a way that I can connect from a JDBC application. What I did was to follow this ...
5
votes
2answers
10k views

SQL Query to get last day of every week

Using MySQL, I need to query a table that has a datetime field. I need to count all the records for a particular week. Using date_format(colname, "%Y %V") returns the week #, but I need the last day ...
8
votes
2answers
31k views

How to create Sql Server login for a domain account?

All, I have Sql Server 2008 installed on a server (let's say Server1) in a Domain (let's say AD). I also have a domain account called AD\Sql1. This is not an Admin account on the Domain (AD), but I ...
2
votes
2answers
5k views

Acceptable size for MS Access Database?

I have developed an Access 2010 database. It has three main tables: HOUSES id, address, number_stories, garden, parking, imageAttachments (~1-4 per record), etc..., about 60 fields total ...
1
vote
1answer
274 views

drastically different runtimes in BIDS and the SSRS Web Portal

I have a report in a BIDS project that has been deployed to our SSRS Web Portal. On the web, it renders within seconds. In BIDS, it takes upwards of seven minutes. I can see that the query runs ...
3
votes
1answer
1k views

Sql Server 2008 Replication “Generate Scripts” option not available

All, On Sql Server 2008, I have Transaction Replication going from Server1 to Server3 (with Server2 as distribution). I have an option to "Generate Scripts" on Publication on Server1. But I do not ...
4
votes
4answers
4k views

Atrocious performance joining INSERTED and DELETED tables in a trigger

I've got an UPDATE trigger on a table that watches for a specific column changing from one specific value to any other value. When this happens, it updates some related data in another table via a ...
7
votes
2answers
9k views

Storing prices in SQLite, what data-type to use?

I am using SQLite and need to store prices. SQLite's REAL data-type says it uses floating-point which is unacceptable storage for prices. Is there a data-type besides TEXT that I can use to store ...
4
votes
1answer
1k views

Are resource_semaphore_query_compile waits causing 100% CPU on server?

Of late, the cpu usage on the db server is consistently at 100%. Even at 2 in the night when there is almost no traffic on the website and the load is negligible. While trying to find the root ...
1
vote
1answer
59 views

Handling newsletter mail subscribers data

A simple data structure for subscribers is email, name, phone, building. Currently, Building is a VARCHAR field, however data contained in the field is actually mostly numeric. How can I change the ...
3
votes
1answer
1k views

PostgreSQL 9 'pgstats wait timeout' problem

I'm asking about this here because in the PostgreSQL mailing lists no one answered me. In one of my servers (Windows 2008 R2 Standard/PostgreSQL 9.0.5 64 bits) the logs are being flooded with the ...
6
votes
1answer
17k views

How to free up disk space? which logs/directories to clean?

I want to free up the disk space on my Linux machine. I've drill down the space usage and found that the following directories have a big size /u01/app/11.2.0/grid/cv/log ...
6
votes
1answer
247 views

Why does Oracle keep working on MySQL 5.1

2 years ago the first version of Mysql 5.5 was released. What is the reason to continue working on MySQL 5.1 - the current release is Mysql 5.1.62 It looks like there are should be fundamental ...
3
votes
1answer
897 views

Oracle explain for cascading delete is wrong

My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it. If I hit the explain button on a statement like this ...
3
votes
1answer
3k views

How to create oracle linked server in oracle server itself?

Is it possible to create oracle linked server in other oracle server? I actually wanted to do this with sql server but in sql server I couldn't run long query (8000 limit). I have access to ...
3
votes
0answers
2k views

ORA-01017: invalid username/password; logon denied

I've come across the abnormal situation, and can't understand what happens. I am connecting databases using PL/SQL Developer 8.0.4.1514(I've also tried v7,but same) . Sometimes(When I am trying to ...

15 30 50 per page