10
votes
3answers
2k views

Testing stored procedure scalability

I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level ...
4
votes
3answers
2k views

How to sync the data between two identical DB

The thing is I've two identical databases, one is in my localhostserver at my laptop and the other one is on the office main server. Is there any automatic way or software that can synchronize the ...
2
votes
1answer
341 views

What system information is stored in the primary file in SQL Server?

Suppose I have created a new database in SQL Server with a file group FG1 and marked this as the default. All my user tables etc are now created in FG1. However I also created a file that is part of ...
1
vote
1answer
233 views

Oracle Application Server 10g Installation Process

I downloaded Oracle Application Server 10g from this Oracle site. It has 4 .cpio files for the Linux installation. How do I unzip and install it on VMWare?
1
vote
2answers
43 views

Trying to better understand how SELECT rights are managed in a/my SQL Server db

Sorry, I'm a security noob: I've been asked to provide write a script that grants our users that belong to some yet unidentified domain group SELECT access to a table called "NewTable1." To identify ...
2
votes
3answers
1k views

How does an OLAP cube database differ from an RDBMS?

Is a cube database, simply an RDBMS—such as PostgreSQL or MySQL—configured for OLAP via a star or snowflake schema? Or is a cube database different from an RDBMS, and if so how?
4
votes
4answers
2k views

How can I generate an Entity-Relationship diagram and SQL DDL from natural language text?

Good morning, Using LaTeX or another text-macro tool/library, how can I use natural language text to describe relationships, then generate an Entity-Relationship Diagram and generate SQL DDL to ...
4
votes
1answer
207 views

Strictly only RAM matters?

I have to work on a project which would have a extra large table with billions of records but only two fields one is id and another is text. And id would be set to BIGINT and PK then if 8+2 (extra 2 ...
5
votes
5answers
3k views

How much memory is needed to keep a table cached? (SQL Server 2008)

I run long and complex queries which mostly use one big table - 8GB, 40M rows. AFAIK, all/most rows are used in each query. I'm seeing lots of IO in the activity monitor - for the first query and for ...
5
votes
4answers
4k views

Benefits of table level locking

What are the benefits of table level locking which is used by the MyISAM storage engine? Row level locking has lots of benefits like concurrent updates and reads that do not lock the table. Edit Its ...
3
votes
2answers
3k views

Consequences of changing NLS_TIMESTAMP_FORMAT

I administer a 9.2.0.8 database where the NLS_TIMESTAMP_FORMAT is set to 'DD-MON-RR HH.MI.SSXFF AM';. This leads to odd results when using the CAST TO TIMESTAMP function: select CAST('14-SEP-2011' ...
5
votes
3answers
3k views

Why is SSMS inserting new rows at the top of a table not the bottom?

Whenever I manually insert a row into a table in SQL Server Management Studio 2008 (the database is SQL Server 2005) my new row appears at the TOP of the list rather than the bottom. I'm using ...
1
vote
2answers
190 views

Workload analysis before migration

Does anybody have a recommendation on which performance counters to collect and/or templates to use on the profiler before migrating SQL databases? The purpose is to evaluate the workload and make ...
0
votes
1answer
546 views

How to avoid table crash?

Couple of days back I had a table with 7GB of data and it was showing 63MB of overhead in phpMyAdmin and I mistakenly started its optimization and after working of more than 40 mins it corrupted the ...
3
votes
3answers
2k views

When do hash indexes become reasonable?

MySQL natively doesn't support hash indexes. So, making a pseudo hash column and creating an index on a hash column needs some thinking. And it seems to be widely understood that if the text field is ...
1
vote
1answer
379 views

Simple select statement not returning correct data

I am trying to return data, where I want the item name (items.name), vendor name (vendors.name), url of the price (prices.url), and the price (prices.price) from 3 different tables (items,vendors and ...
4
votes
3answers
1k views

For every insert statement I see sp_executesql in SQL Server Profiler

Working on tuning our database and queries for one of our products, I decided to pop open Profiler in the QA environment and see what it shows. I saw for every insert I called, a call was made to ...
1
vote
1answer
5k views

Set permissions on all objects for SQL users

I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to ...
2
votes
1answer
422 views

Best Practice for Folder strucure?

I don't like my data files in C:\Program Files... , especially with a new system that has a smallish SSD as C: which holds mainly the operating system. So on a recent install I put my data files into ...
1
vote
1answer
413 views

General Question on how to setup db's for a social networking site

I am currently responsible for designing the entire database setup for a start up website. It is quite a responsibility and I want to get it right, not only for the site, but for my own experience. ...
1
vote
4answers
302 views

Database backups

I have 10gb RDBMS (SQL Server). I would like to back it up as often as possible, but it seems that I will need an awful lot of space to make this happen on the order of several times per day. How ...
3
votes
1answer
9k views

Equivalent functions in MySQL that exist in SQL Server

In an EXIT HANDLER block, what are the MySQL equivalents of OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) ERROR_MESSAGE() Edit What I want to do. In SQL Server I'd have CREATE PROC ...
2
votes
2answers
3k views

Understanding time format of the EXPLAIN command - Postgres

When I run the EXPLAIN ANALYZE command on a given query, I'm having a difficult time interpreting the outputted time value. For example (actual time=8163.890..8163.893). Do the internal decimals ...
1
vote
3answers
437 views

Select only neighbour rows who pass a where-test

I want to apply a selection process to my data, that will not include rows which should be accepted by my WHEREclause but who follow after a row which fails the WHERE. Example. I have a list of gps ...
1
vote
2answers
692 views

What is com_set_option?

As the title says, I'm wondering what is this command and why am I seeing a large amount of this query on my database? It's currently 13.30% of my total queries. I've tried googling this but there's ...
2
votes
2answers
1k views

Determining maximum table size, type of engine and type of indexes supported

How many types of indexes are available with SQL Server 2005 and SQL Server 2008? MySQL has a variety of options like: Hashed, BTree, etc. What are the choices in SQL Server? I also want to determine ...
1
vote
2answers
302 views

Adding a new slave slowing down previous replicas?

I have a master I was going to start slaving to a new server to do some tests on. This master is currently being slaved to 3 other boxes. After bringing the new slave online it became clear it ...
2
votes
4answers
3k views

how to turn off delimiters in generated SQL scripts for data types in SQL Server Management Studio

We are a software engineering firm. We use SQL Server Management Studio (SSMS) to generate the few scripts we need to build and upgrade our databases. While it does an OK job, it bugs me that it ...
6
votes
1answer
3k views

When is an application using too many sessions?

I'm working on a client-server application that uses OCI to communicate with Oracle. In the past our application often treated sessions as an infinite resource, quite possibly to the detriment of ...
1
vote
3answers
2k views

MySQL: Trying to update to 5.5

I've followed this instructions, but after mysqld --skip-grant-tables --user=mysql, I'm getting this: 110913 20:29:18 [Warning] Can't create test file /var/lib/mysql/my-laptop.lower-test 110913 ...
3
votes
3answers
4k views

What's the difference between Linked Server solution and SSIS solution?

When I want to move data between two databases (e.g source: Oracle, destination: SQL Server), I think I have two options: Linked Server and SQL Server Integration Services. But is there any benefit ...
4
votes
1answer
1k views

Determining max database and table size supported and present size

One of my MySQL databases has been running on a single PC from the last four years. Here is my setup: MySQL 5.5.14 Windows XP on NTFS partition All of my tables are using InnoDB engine Here are ...
0
votes
4answers
714 views

Why Clustered Index property is enabled on few columns and not on others?

I heard that there can be only one Clustered Index? I may be wrong, but I am sure I read somewhere. In one of my SQL Server 2005 table, I have following columns: SerialNum ------- BigInt -------- ...
1
vote
2answers
378 views

What does it mean by '4M blocks' or '4G blocks' in Oracle?

The following 2 sentences are excerpt from Oracle documentation. You can easily fine them by google them directly. My question is, 4G and 4M are the unite of digital information storage. What's the ...
0
votes
1answer
445 views

Depicting permissions to access table via E -R diagram

I am designing a database schema for a web application. Its a first time I am designing a database with a commercial aim. I have started with drawing E-R diagrams after going through requirements ...
0
votes
1answer
238 views

Why SQL Server replication related jobs do not show up under the jobs folder in SSMS?

I found some SQL Server replication-related jobs that do not show up under the jobs folder in SSMS. Can anybody explain why this is, please? Thanks.
2
votes
2answers
368 views

Differences between development and live database setups

I was wondering if there are any best practice recommendations on setting up databases in a dev environment versus a live environment with regards to configuration. Should they be as similar as ...
9
votes
1answer
1k views

Do Oracle databases use Java internally?

My question is fairly simple. Is Java used internally by Oracle in their DBMSs?
6
votes
3answers
609 views

Is there a need to keep refreshing the indexes from time-to-time?

This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL. I have created a fresh database and the tables, at present, have no records. I have created ...
17
votes
4answers
13k views

How to design a database for storing a sorted list?

I am looking to store a sorted list inside a database. I want to perform the following operations efficiently. Insert(x) - Insert record x into the table Delete(x) - Delete record x from the table ...
1
vote
1answer
97 views

sql 2005 - trace capturing all events

I am trying to capture all statements/SPs and RPCs (either they finish or fail) on a SQL 2005 server using SQL trace. Are the following events enough? Exceptions Stored Procedure RPC:Completed ...
0
votes
1answer
162 views

sql 2000 trace : sp_releaseschemalock

For a SQL 2000 server, is it bad to see too many exec sp_releaseschemalock 1 events in a SQL trace file?
3
votes
1answer
1k views

How do you gain access to embedded MySQL database via localhost command line?

We run an application (Windows 2003 server) and would like to have database connectivity -- contacted the company and they do not have any documentation on how to do so. A quick glance at the ...
1
vote
2answers
3k views

How to find the fitting version of pgadmin III?

If you have different versions of PostgreSQL database servers you need a pgadmin III version with the respective pg_dump and pg_restore included. I am running pgadmin III on a windows machine. For ...
0
votes
1answer
666 views

How to create a backup schema script including System Tables in Sql Server 2008 R2?

I need to back up my entire DB including one table that's allocated in "Tables -> System Tables". When I try to generate the sql scripts to back up my DB this table isn't mapped in this scripts. Do I ...
0
votes
1answer
2k views

Reindexing Partitioned Table Indexes

Historic Reporting Server running SQL 2008 R2. I'm trying to tweak the fill factor. I have a job that records the fragmentation when the jobs runs in the early hours. For every table that had high ...
1
vote
1answer
54 views

Replication : Which subscription changed a row

Is it possible to query the replication metadata to see which subscription caused a change to a specific row in a table?
1
vote
1answer
266 views

DB Integrity Check Lock Database

Hi all i'm using The Integrity Check script from http://ola.hallengren.com to check through my database of roughly 65 million records. I was just wondering if this will lock my database? In terms of ...
39
votes
4answers
69k views

Possible to make MySQL use more than one core?

I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer then DBA for MySQL so need some help Setup The servers are quite hefty with an ...
4
votes
2answers
1k views

Creating a GroupBy query to include a result for when no results match

Let's say I need a query to see sales results by Month and I want the result-set to include results for each month whether there were any sales in that month or not. And let's say my table "sales" ...

15 30 50 per page