All Questions

7
votes
2answers
699 views

Is a “‘Junction’ Table” the same as a Weak Entity?

Is a “‘Junction’-Table” basically just the table that sits between two entities that have a many-to-many (M-M) relationship?
7
votes
4answers
9k views

Property PopulationStatus is not available for FullTextCatalog… How to fix?

I've created a new catalog (case insensitive, make default catalog), when I try to right click > properties to add tables I get this error: Property PopulationStatus is not available for ...
3
votes
1answer
3k views

A nonrecoverable I/O error occurred on file after DB Shrink

After shrinking an SQL Server database & files, I encountered an error message saying "A nonrecoverable I/O error occurred on file" while performing database backup. Why am I getting this error?
8
votes
4answers
5k views

TSQL: Find Queries causing too many SQL Compilations and SQL ReCompilations seperately

I want to find out what is causing the high SQL Compilations (not re-compilations) I am seeing in performance monitor counters. Here is my take on it: If I am seeing a lot of SQl compilations, then ...
2
votes
2answers
2k views

Does the target for Double-Take replication require an Oracle license? [closed]

Where would Vision Solution's Double-Take product fall with regard to Oracle Licensing? It is both a backup and a replication of the database. Oracle's Software Investment Guide has a section on ...
4
votes
2answers
1k views

Difference between storing a DBUnit file in version control and using Liquibase for Database Version control?

What is the difference between storing a DBUnit database-loadable xml file in version control, and using Liquibase for database version control? I'm relatively familiar with DBUnit having used it in ...
18
votes
3answers
1k views

How to store 'n' days of web server logs in Sql Server?

For faster reporting and performance analysis, we want to insert our web server logs into Sql Server. This will allow us to see traffic patterns, issues, slowdowns in near real-time. We have a daemon ...
2
votes
2answers
2k views

How to get a user's friends' names?

I have a users table and friends table. The user with ID 1 has 4 friends (with IDs 2,3,4,13). I want to get names and surnames of users 2,3,4 and 13. I can to it with standard queries but it will slow ...
6
votes
2answers
9k views

Is Oracle DB licensing needed for a test environment?

I'm planning to setup an Oracle Standard Edition instance on a server in our test environment. Do I need to purchase a license for this?
10
votes
1answer
6k views

Database Mirroring with TDE

I have the requirement of mirroring a few databases and also using transparent data encryption (TDE) on them as our data must be encrypted while 'at rest'. I have setup TDE on both the principal and ...
2
votes
1answer
2k views

What are the best practices for ETL of data from one OLTP system to another?

First off, I want to post that I am a developer and not a DBA, so I am trying to approach this from the standpoint of what is the best thing to do, rather than just assuming I can do everything ...
2
votes
3answers
3k views

How to choose the last recorded added without sorting

I've got this simple table: RecordID (int PK), UserID (int FK), DTime (datetime), Length (int) I need to run a simple query: SELECT TOP(1) * FROM uTable WHERE UserID = @userid ORDER BY DTime DESC ...
3
votes
3answers
1k views

Priority Inbox Database Design

I was just wondering, what would be the table structure for a messaging system similar to Gmail's priority inbox? If you're unfamiliar with the system, it prioritises messages based on the type of ...
7
votes
1answer
892 views

Alerting on Database Mirroring Events

I am looking for the actions one must take to cause Threshold Alerts to occur for a single mirrored database on an instance of SQL Server 2008 containing multiple mirrored databases. FYI: This is ...
9
votes
1answer
5k views

SQL Server Deadlock Graph - Table, Page or Row Lock?

Is there any way to decipher if a lock in a deadlock graph is Table, Page or Row level? I have all the information I need from the graph, including the Isolation Level, (2) but I really want to know ...
6
votes
2answers
981 views

How to retrieve granted privileges without access to the database “mysql”?

As a MySQL user without access to the mysql database, is it possible to retrieve my privileges?
7
votes
6answers
2k views

SQL Server 2005 Huge DB issue

My DB files are growing very fast ( designer is not me and just a weekly report makes 7 GB growth ). And finally, I don't have enough space in the disk. And number of un-reported weeks counting up. ...
26
votes
7answers
14k views

Any Open Source / free ETL out there? [closed]

I was using Pentaho Data Integration even before Pentaho bought it and call it that. I have the last free version. I went on their website recently to see if they had released another version only to ...
11
votes
1answer
7k views

PostgreSQL: COUNT(*) uses a sequential scan, not index

Why does PostgreSQL sequentially scans the table for COUNT(*) query, while there is a very small and indexed primary key?
4
votes
2answers
129 views

How hard would it be to have PostgreSQL run a test to adjust the cost parameters?

By using EXPLAIN one can see the cost of running a query. The cost is in artificial units, and they are based on pre-defined parameters. 1 is one disk fetch and a CPU cycle is 0.01 units, I believe. ...
11
votes
2answers
8k views

Why does this query cause a deadlock?

Why does this query cause a deadlock? UPDATE TOP(1) system_Queue SET [StatusID] = 2, @ID = InternalID WHERE InternalID IN ( SELECT TOP 1 InternalID FROM system_Queue WHERE ...
4
votes
1answer
2k views

Oracle 10.2.0.1.0 listener process stopped

When I am checking my servers I see that there is an error about listener process stopped. I logged in and checked the status of listenere. please see the below status; LSNRCTL> status Connecting ...
4
votes
3answers
1k views

Most suitable DB Architecture for real-time high-volume processing API

I've got a service that should support real-time interaction between many users (thousands real-time). I use .NET 4 with MSSQL 2008 on IIS 7.5. The DB consists of only a few tables without high usage ...
33
votes
7answers
66k views

How can I tell if a SQL Server database is still being used?

We're looking to decommission a SQL Server instance which has a couple databases still remaining on it. How can I tell if they are still being used by users or a web application? I found a forum ...
5
votes
2answers
1k views

More table or more database for better performance?

Lets say 1 website need 10 database table to work and i want a database pool that 40.000+ website will be inside. Which option is better for server performance? 1) 400 databases each have 1000 table?...
7
votes
4answers
14k views

Database design for a time tracking application

I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user to enter for each day of the week the amount of time he ...
29
votes
6answers
30k views

PostgreSQL: Force data into memory

Is there a systematic way to force PostgreSQL to load a specific table into memory, or at least read it from disk so that it will be cached by the system?
5
votes
2answers
1k views

PostgreSQL relation size does not sum up

I have a very large table, with blob fields, called data. I've tried to figure out why it isn't cached well and repeated SELECTs are quite slow: => SELECT pg_size_pretty(pg_total_relation_size('...
17
votes
6answers
4k views

Redgate SQL Compare vs Visual Studio 2010 Premium/Ultimate database project

I currently use the Visual Studio Professional Edition which has the database project as a project template, but some of its features are not available, for example Schema Compare tool. The schema ...
9
votes
5answers
8k views

What is the most efficient way to alter a column definition in a table with millions of rows

I need to change a column from NOT NULL to NULL in a table that contains millions of rows. I've tried a simple alter table Table1 ALTER COLUMN Column1 XML NULL but it takes forever. So here are my ...
2
votes
2answers
14k views

Restore MySQL DB from its folder

Some time ago, I've built an webapp on top of WAMP server. At that time, I didn't do any export/dump of my MySQL DB but I've did a copy of the WAMP folder. Does anyone have any ideas about how I can ...
2
votes
1answer
567 views

SQL server 2008 Analysis Services DSO properties

We are planning to do Sharepoint and project server 2007 databases migration. The share point website also processes cubes. So we will be moving SSAS cubes from SQL 2005 to 2008 and then re-pointing ...
9
votes
3answers
918 views

Speed impact of using varchar in SQL Server 2008

In the old days, using varchar over char was a big no no, since the varchar was stored on a different page than the rest of the row. Is this still the case today with SQL Server 2008? Will I see any ...
2
votes
2answers
421 views

Recreate indexes and constraint when redefining table with DBMS_REDEFINITON

I have a table A witht the following characteristics: multiple indexes constraints (null, unique, check, foreign key) 2 triggers (before update) primary key If I were to redefine the table with ...
4
votes
3answers
15k views

How to convert a Top 1 subquery using outer table alias to Oracle?

I have the following SQL Server query select (select top 1 b2 from BB b where b.b1 = a.a1 order by b2) calc, a1, a2 from AA a where a2 = 2; which I can rewrite using analytic functions ...
12
votes
4answers
3k views

When to use XML data type

I'm responsible for creating a database on a project. We have fields that are rarely going to have a value (1 in every 10,000 records) and I'm trying to work out the best way to store this in the ...
1
vote
1answer
1k views

Select statement to retrieve xml output as below

Could someone provide me some clues or solution to retrieve record sets like below? Note: I read the msdn documentation but leads me no where but hair loss :( Just for the assumption think i have 2 ...
5
votes
3answers
858 views

Can I have create an InnoDB table without a PK? If so, would a secondary index be used as the clustered index?

Is it possible to create an InnoDB table without a Primary Key? Since InnoDB tables are structured as clustered index around the PK, what would be the structure of a table without PK? Would that ...
4
votes
1answer
3k views

How do I horizontally partition an oracle database table, and should I?

We have a tenanted data warehouse that we are doing reporting on. Queries are beginning to take a long time, and we're looking at options to reduce this. There are two thoughts at the moment. Create ...
5
votes
3answers
226 views

Import 2 column CSV files to some sort of DB… preliminary planning

Every day I have a CSV file generated by a script. It has two columns. Column 1 is a name, column 2 is the size of their mailbox. I have a years worth of these files. I would like to be able to ...
4
votes
3answers
777 views

How can I fix the Oracle syntax of this update by the result of a select in Oracle 10g?

I have a problem with an update statement, that I developed under 11g, when running under 10g. I reduced the problem to: Create table test ( col1 number(10) ); insert into test values (1); ...
29
votes
7answers
212k views

How do I Create Tablespace in Oracle 11g?

I am not too savvy in Oracle, and I have been trying to create a TABLESPACE, which keep on giving me an error message of invalid file name. Below is the syntax that I have used: SQL> create ...
2
votes
2answers
516 views

Database Mirroring Failure Reasons

I was wondering if there is any information on what scenarios might cause a DB Mirror to have to be recreated? I am attempting to research how sturdy the technology is and under what scenarios do we ...
4
votes
2answers
717 views

What is the best practice for mapping from natural keys to integer-based keys? (ETL)

This question originates more from an "enterprise architecture" point of view. (Reason being, our company is just starting to get into data management, and actually having DBA's -- I know, horrific ...
4
votes
4answers
1k views

Is tuning the innodb_buffer_pool_size important on Solaris ZFS?

We're running a moderate size (350GB) database with some fairly large tables (a few hundred million rows, 50GB) on a reasonably large server (2 x quad-core Xeons, 24GB RAM, 2.5" 10k disks in RAID10), ...
8
votes
1answer
10k views

MySQL high availability, failover and replication with Latency

We are in the process of implementing a new CMS (Drupal 6.x) that runs on MySQL. We have two data centers -- primary and secondary -- with known latency between them. We're unsure which version of ...
3
votes
1answer
357 views

In a recruiting website, should I force a choice between one table for jobseekers and employers or 2 separate tables?

I'm working on a recruiting web site and I was wondering what would be best between: A User table, the employer accounts are linked to a company (therefore when companyID is null then it's a job ...
3
votes
2answers
343 views

Comparing two XML query plans

Hopefully a simple one, if I have two (fairly complex, 30-40 node) .sqlplan files (from the same SQL text) what is a good way to compare them side by side? Or just have it tell me what it did ...
16
votes
2answers
9k views

SQL Server 2008 - Partitioning and Clustered Indexes

So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario. Comments about how we ...
96
votes
11answers
78k views

SQL: SELECT All columns except some

Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table. Something like: SELECT * -...

15 30 50 per page