4
votes
1answer
228 views

What program to use to change settings on db servers?

Having decided to set up a Master-Slave replication I found this article, which seems exactly what I want. So I fired up the MySQL Console in Navicat to give everything a try, and the commands do ...
31
votes
3answers
33k views

What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?

I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the ...
5
votes
1answer
1k views

Using integrated security over VPN

We've got a client that we're going to allow to use one of our applications. Staff on site regularly connect to our network via VPN. The .Net application that connects to the SQL Server uses ...
11
votes
2answers
15k views

kill all queries - MySQL

Sometimes during a SNAFU I have to run kill query xxxxxxx twenty or thirty times. Any sort of kill all command I am missing? On account of how I don't like typing.
4
votes
3answers
784 views

Separate databases for front-end and back-end

The site I am working on has a lot of backend/cron activity, so there are between 5 and 15 queries being executed every second of the day. This can slow down page loads by a second or more. I was ...
10
votes
5answers
5k views

When should nonclustered indexes be stored on separate filegroups?

I have heard that storing indexes on a different filegroup and drive increases performance in a database because the drive doesn't have to go back and forth between the index and the data to which the ...
5
votes
2answers
6k views

What is the best practice for storing image varbinary(max) data in a database table?

I have a table that stores images that range in size between 16-100 KB each. Since the images are so small, I've taken Microsoft's advice and not used the FILESTREAM data type. The table is ...
4
votes
2answers
313 views

How do I store data with a query that's approximated?

I'm trying to find a way to store my data with fast access (better than O(n)). My database consists of data (4096 byte strings) that represents some information about some items. The problem is, that ...
3
votes
1answer
1k views

SQL: transfer database schema

If i want to transfer schema ownership to another schema i know that i need to use something like: ALTER SCHEMA destinationschema TRANSFER sourceschema.objectname; But how is it different from: ...
3
votes
1answer
1k views

pgAdmin Query Tool throws error when I try to run multiple statements

I'm very new to PostgreSql and still learning the ropes. I have a really long script for setting up a new copy of my database. It creates functions, runs them, does some inserts and a bunch of other ...
3
votes
2answers
712 views

Combining retail sales and payment OLAP cubes

I'm using SQL Server 2005 Standard Edition Analysis Services (SSAS). I'm trying to build cubes from a retail OLTP database that can answer these kinds of questions: List down all products that were ...
3
votes
1answer
2k views

Amazon SimpleDB or Amazon RDS?

I'm at a deciding point in my project of whether to implement the database using SimpleDB or Amazon RDS. The database needs to be accessed through an iPhone app and as well through a website. Here are ...
4
votes
1answer
863 views

What are the differences between Oracle UCM and Oracle Content Server?

Whenever I do searches on them, I keep seeing them in search results together. What are the differences between Oracle UCM and Oracle Content Server? Are they just two names for the same thing? Is ...
5
votes
1answer
2k views

Is there a way to edit the System Database Directory for DB2 to remove a database?

We have a case where we want to try to get rid of a database. The database still exists on disk and when we catalog it, it re-appears in the Database Manager. But when we try to do a DROP on it, it ...
1
vote
2answers
237 views

create a FTS in sql2005 on multile fields in multiple tables?

How can I create a Full Text Search catalog on 3 fields where one is in a second table?
1
vote
1answer
1k views

How to ensure that SQL Jobs run in the correct order within a minimal maintenance window?

From this question I learned about a script which creates jobs for backing up databases, checking database integrity, optimizing indexes, and cleaning files. According to the documentation, this ...
3
votes
1answer
92 views

Pure (My)SQL transformations

I'm trying to promote the usage of a database versioning system (http://code.google.com/p/flyway/) on the project I'm currently working on - and to that end when we make changes to the database, we're ...
1
vote
1answer
612 views

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

Ok, I originally started a question a ways back called "What are the best practices for ETL of data from one OLTP system to another?". And as you can probably tell from most of my questions so far, ...
3
votes
3answers
798 views

How bad is a tempdb drive failure (with multiple tempdb files on different drives)?

I'm getting a new 16 core server for my SQL Server 2005 database. I would like to create 16 files for my temp DB, and put each one on a separate drive. If one of these drives fails, can SQL Server ...
2
votes
1answer
870 views

What is the best practice for backing up a database in Simple mode?

I am trying to create a SQL Job for backing up my database. I want to have: A weekly full backup A daily differential backup Something that will clean up backups that are older that 14 days I ...
2
votes
1answer
752 views

access all_mview or user_mviews from app code

I need to access the user_mviews or all_mviews table to get meta information about materialized view in my app. Which grants are necessary to read the user_mviews table?
5
votes
4answers
1k views

how to migrate data from spreadsheet to SQL server management

I want to know how to migrate data from spreadsheet to SQL Server.
1
vote
1answer
101 views

Help on writing server-side trace

I need help on writing a server-side trace to check logins to try to determine if and when vendor accounts are used. The purpose is to remove sa access. These accounts sure have sa but I need to ...
0
votes
1answer
4k views

Maintenance plan delete backup

I succeeded in making a backup through maintenance plan. All I need now is to find how to delete a folder (containing old backup) through the same maintenance plan.
17
votes
5answers
49k views

How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist

I have a SQL server 2008 instance running. Unfortunately during testing I deselected the sysadmin rights for my login and now can't readd it (because I don't have sysadmin rights). There are no other ...
3
votes
2answers
2k views

What are the benefits of using a different tablespace for a new user schema?

The tables in the schema are intended to be used read only and is only updated once each three month. My question refers to performance maintenance (backup/restore, export/import) What about the ...
25
votes
3answers
37k views

How do I assign an entire Active Directory group security access in SQL Server 2008?

I would like to use integrated security with my internal application which is all on a domain. Unfortunately, I've never been able to get this to work well. I would like to assign an entire Exchange ...
10
votes
5answers
5k views

Group By hour over large dataset

Using MS SQL 2008 I am selecting an averaged field from 2.5 million records. Each record represents one second. MyField is an hourly average of those 1 second records. Of course the server CPU hits ...
7
votes
2answers
3k views

How to effectively query and aggregate a normalized SQL db?

In relation to Most efficient way to return multiple aggregates in a single stored proc? I have an email type application and want to select all the messages (inbox) for a user. The problem is that I ...
6
votes
2answers
671 views

Most efficient way to return multiple aggregates in a single stored proc?

What is the best or most efficient way to get multiple aggregate result values? Basically I have an email app and wanted to get all the numbers of messages for each type of folder (inbox, sent, ...
5
votes
4answers
539 views

How much will using a varchar(15) PK affect my tables performance?

I am parsing log file lines from a legacy, proprietary application into a nice easy to query DB. The log lines have no unique integer ID. They do have a UNIX timestamp as an 8 character hex string. ...
3
votes
3answers
665 views

Should I create a maintenance plan for my database or use jobs?

I tried creating a Maintenance Plan on my SQL Server 2008 database and was impressed by some of the things that are available out of the box. However, after it was created, it seemed difficult to ...
2
votes
2answers
3k views

DB2 Trigger: only update current row

I've created following trigger : CREATE TRIGGER probeer2 AFTER INSERT ON libtn01leb.kofax_release FOR EACH ROW UPDATE libtn01leb.kofax_release SET RRNR='bleh' This trigger will update EVERY ...
5
votes
1answer
14k views

Query execution time in Oracle SQL Developer

I know I can use set timing on to see how long it took to execute a query after the execution is finished. But is it possible to see current execution duration in Oracle SQL Developer while the query ...
4
votes
4answers
355 views

Do cross-joins get optimised by the client library?

Note This is deliberately database agnostic, I'm interested in how different implementations, er, differ. I came across Joins are for lazy people on SO, and it's made me question an assumption I'd ...
3
votes
2answers
4k views

Stored procedure parameters using tsql functions?

Environment sql server 2005 sp3 I have a stored proc that takes an INT as input. I want to CAST a CHAR to an INT during the call to the stored proc. it seems I cannot do that. I get a syntax error ...
5
votes
2answers
299 views

Database replication with ability to revert to previous states

I want to setup a development database such that it'll initially be a mirror of the database used in production. At fixed points during the day (maybe midnight or something) and/or manually, I want ...
0
votes
1answer
1k views

Entity Relationship Model for linked tables

Certainly a noobish question, but I'll ask it anyway. Perhaps someone else will be thankful. ^^ I am getting warmed up with MySQL and I try to understand the concepts of creating well structured ...
6
votes
1answer
3k views

How to design a schema for a site that accepts logins from multiple sites (e.g. facebook connect, google, flickr, etc..)? [closed]

I'm building a site ("The Site") and I'm considering how to design the DB schema for it. I have a few scenarios that will affect the outcome: The user can choose to register a native account on the ...
13
votes
5answers
2k views

What features of Oracle make it an attractive choice for small projects?

Given Oracle's licensing handling[a] (and, to a lesser extent, cost) I have always been wondering what the deciding factors would be for choosing Oracle over PostgreSQL or MySQL. My company is nearly ...
13
votes
2answers
5k views

Trigger: move deleted rows to archive table

I have a small (~10 rows) table called restrictions in my PostgreSQL database, where values are deleted and inserted on a daily basis. I would like to have a table called restrictions_deleted, where ...
2
votes
3answers
2k views

How do you restore a particular table in MySQL?

One of our users has deleted a table by mistake. How can I restore only that table?
6
votes
4answers
529 views

Purpose of IDs in a User Table Database

In a table, such as a user table, where there will only be 1 row with a for any particular user, why have an ID field, let alone as the primary key?
4
votes
1answer
367 views

How do I use the POSIX character classes upper and lower to search text in MySQL?

I would like to search a body of text to find out when a specific word has been upper cased. MySQL has a REGEXP function with the POSIX character class [[:upper:]], but for the life of me I can't get ...
5
votes
2answers
10k views

How do I find the median value of a column in MySQL?

I can only imagine doing this with two database queries. The first finds the number of rows in the database, and the second selects with an ORDER BY on the column I am interested in and LIMIT X, 1 ...
6
votes
4answers
2k views

Duplicate database and keep it up to date - Replication or SSIS?

I would like to duplicate a database, on the same server, and keep it up to date either by running a scheduled service once a day or having SQL Server 2008 take care of this internally. We don't need ...
48
votes
4answers
20k views

What is the difference between select count(*) and select count(any_non_null_column)?

I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table. What are the differences between these ...
2
votes
1answer
1k views

master to multi-slave replication

I am looking at replication of the master database (PostgreSQL) and this is the configuration I have to setup master -- hot-standby (hs) | | --- read only slave (r1) | | --- read only slave (r2) ...
2
votes
2answers
3k views

Sp_helptext Error

I am using sp_helptext to list some stored procs instead of going through the ui. I am having a problem with the naming convention we have chosen to adopt. I have a stored proc nameed [Test.FooBar]. ...
14
votes
4answers
5k views

Make SqlClient default to ARITHABORT ON

First things first: I'm using MS SQL Server 2008 with a database at compatibility level 80, and connecting to it with .Net's System.Data.SqlClient.SqlConnection. For performance reasons I've created ...

15 30 50 per page