1
vote
0answers
127 views

PostGIS - Effects of polygons with large number of verticies [closed]

I have a large table that stores, among other attributes, a geometry type. I have a GIST index on this attribute. However, when I perform even the most basic queries [say, select count(*) from table ...
1
vote
2answers
711 views

Oracle Databases - need to export nightly for backup/DR purposes

I know nothing about Oracle databases, so if my question's path seems odd, feel free to tell me the right way to do things. I have 2 ways I see currently that I can backup a server running 3 Oracle ...
52
votes
10answers
9k views

Why do people recommend not using the name “Id” for an identity column?

I was taught not to use the name Id for the identity column of my tables, but lately I've just been using it anyways because it's simple, short, and very descriptive about what the data actually is. ...
1
vote
1answer
131 views

Sql Server Resource Governor - track IO

I know I can use the performance counters to track Resource Governor CPU and Memory Usage. Is it possibly to track the I/O usage for each work load group? I haven't seen anything specific or any ...
10
votes
2answers
289 views

Index fragmentation while continuously processing

SQL Server 2005 I need to be able to continuously process about 350M records in a 900M record table. The query I'm using to select the records to process becomes badly fragmented as I process and I ...
7
votes
2answers
4k views

Index on primary key not used in simple join

I have the following table and index definitions: CREATE TABLE munkalap ( munkalap_id serial PRIMARY KEY, ... ); CREATE TABLE munkalap_lepes ( munkalap_lepes_id serial PRIMARY KEY, ...
10
votes
1answer
20k views

How to control the excessive use of ram by SQL Server?

The database server that I am using is running 6 different SQL Server instances. It has 48 GB RAM. And one of them is consuming more than 10 GB of RAM, total consumption is 20 GB for now. The RAM ...
1
vote
2answers
632 views

Managing features for a real estate agency [duplicate]

I have a database table real_estate where the basic information is held (id, status, sku, address, building_type, sales_status) and now I want to attach features to that real_estate table. The data ...
1
vote
1answer
551 views

Migrating data in SQL Server from source to target with schema change at target

We have a strange scenario. We have a source database running on SQL Server 2005 where we push data to a target database running on SQL Server 2008 R2. The source database table has 5 columns. The ...
5
votes
4answers
5k views

Prevent reset of auto_increment id in Innodb database after server restart

I recently read that because of how InnoDB recalculates the AUTO_INCREMENT value when the server restarts, any records on the high end of the ID list may have their IDs reused. Normally, this isn't a ...
3
votes
2answers
1k views

Select total number of records from common table expression

We are using common table expressions to temporarily save the search results. WITH Results ( ------ ) AS ( select --- ) SELECT * FROM ...
1
vote
1answer
812 views

Which Optimistic Concurrency Control method will work out best in the long run?

These are the options I've thought of: DotNet Dave: UPDATE mytable set f1=newval where id=666 and f1 := f1ValueFromSelect Charlie Checksum: UPDATE mytable set f1=newval where id=666 and ...
3
votes
3answers
529 views

What are the design alternatives when having multiple “type” tables

I am working on a database design and see myself with lots of *_type tables (e.g. user_type, product_type, etc.) where the structure of these tables is the same, basically: user_type ( id int pk ...
7
votes
1answer
309 views

Table order in Join Query

if i say: Table1 left join Table2 is it the same as saying Table2 right join Table1 ? In other words, should i expect to get the same results from 2 identical queries where the only thing that is ...
3
votes
2answers
25k views

How do I troubleshoot loss of connection with SQL server

I have an application that intermittently has a break in the connection between it and the SQL Server instance it requires to function. What is a good way to go about troubleshooting this type of ...
1
vote
1answer
985 views

SQL Server 2008 R2 - VLDB Backup Strategies

I have a 200GB database running on SQL Server 2008 R2 Enterprise Edition. I just recently partitioned it in order to archive 11 months of history, so it was pushing 400GB prior to archiving. Our ...
8
votes
1answer
18k views

What exactly is binding in DB2?

I recently crossed over from being a Java developer to an actual DBA in our company. I am learning the ropes, so to speak, about being a DBA (which is actually somewhat of a new position for our ...
6
votes
1answer
644 views

Using an RDBMS for querying tens of Terabytes of time-series data?

I stumbled over an algorithmic / data-structures question over on SO, which I'll short quote: (...) opinions regarding best of breed data structures to be used for indexing time-series (aka ...
1
vote
1answer
475 views

file not found - how to remove its configuration?

I run oracle 10.2.0.5. I have the following: SQL> select file#, status, error, recover, tablespace_name, name 2 from v$datafile_header 3 where recover='YES' 4 or (RECOVER IS NULL AND ...
1
vote
1answer
290 views

GUID as reason for MySQL import slowness

We faced MySQL restores DB VERY slow: ~3 min for 15 MB dump. This is a new dedicated server with a lot of RAM. We were told the most possible reason is GUID is uded as primary key in many tables: ...
6
votes
1answer
2k views

Service Broker & AlwaysOn Availability Groups: Odd Transmission Queue Behavior

I have also posted this question on my blog: http://www.sqldiablo.com/2012/04/15/service-broker-alwayson-availability-groups-odd-transmission-queue-behavior/. I’ve been working on a project over the ...
1
vote
0answers
290 views

Cannot Check if the Access Database Engine is Installed and Configured

All, I want to check if the Access Database Engine is installed and linked to SQL Server. On my development machine (x64-bit) the following queries both show the Access Engine to be available as a ...
2
votes
1answer
592 views

SQL Server 2008 R2 .mdfs write protected

I had to replace my hard drive, XP pro sp3, and all my applications - including SQL Server 2008 R2 developer edition. My databases are all on a separate physical drive. I find I cannot move or copy ...
2
votes
1answer
314 views

How does MySQL coerce types during joins?

Why do these two queries behave differently: mysql> explain select FOOBAAR_hhs.relationship_group from FOOBAAR_hhs left join FOOBAAR on FOOBAAR_hhs.relationship_group = FOOBAAR.relationship_group ...
2
votes
1answer
144 views

One table or several tables?

I have several types of writings: articles, projects, reviews, etc. I have a table containing paragraphs (fields id (INT) and partext (TEXT)). Each writing consists of several (ordered) paragraphs. ...
1
vote
1answer
482 views

SQL Server Merge Replication - Safely shrink transaction log

I have a couple of SQL databases running in merge replication. My publishers HDD is full due to the transaction log of the database being replicated which is causing my snapshot agent to fail (out of ...
1
vote
1answer
3k views

My SQL 5.6.4 DATETIME column milliseconds is not getting stored

Beginning from MySQL 5.6.4, milliseconds support is provided for date columns like DATETIME etc. I just created a table to check if its working: create table testdata(startime DATETIME(5)); ...
3
votes
2answers
594 views

City Distance Map Database Design

Well I am just a db beginner, I want to design something like this I want to keep simple as possible, well I will enter all values[Cities, Distance etc.,] manually, because my cities are limited. ...
0
votes
1answer
160 views

Postgres Client - save table sorting

Is there a Postgres client (for Windows) that can save customized sorting when viewing tables? Obviously I could write and save a query for this, but perhaps there's a client that can do this for me. ...
5
votes
5answers
2k views

What normal form does a surrogate key violate?

I have following question: "What normal form does a surrogate key violate?" My thought was the 3rd normal form, but I'm not quite sure it's just an assumption i am making. Could someone explain that ...
2
votes
1answer
4k views

How can I move LOB_DATA ( varbinary/image/text) to different Filegroups

LOB_DATA : varbinary(max),varchar(max), image , text columns. I have 4 tables with large amount of LOB_DATA in it. I would like to move LOB_DATA in these tables to different File group in a different ...
3
votes
1answer
77 views

How to avoid listing FIELD/IN twice?

I have SELECT field(foo,'this','that','bar','baz') FROM t WHERE f1 IN ('this','that','bar','baz') How can I avoid listing ('this','that','bar','baz') twice?
4
votes
4answers
12k views

Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping

I'm building an inventory database that stores IT hardware, such as desktop computers, laptops, switches, routers, mobile phones, etc. I'm using supertype/subtype pattern, where all devices are stored ...
5
votes
1answer
1k views

Concurrency with Select-conditional Insert/Update/Delete - PostgreSQL

I have an intervals table which stores a start date and an end date for variables variable_id (four columns : id, start, end, variable_id). Clients compute new intervals for one variable and store ...
0
votes
1answer
5k views

How can I select the latest record having one state where no later records exist with any other state?

I want to select the last record (by datetime) having a given state where no later records exist with any other state for each given "widget". widget state timestamp ------ ----- ...
1
vote
2answers
2k views

Do I need SQL_NO_CACHE for write intensive query?

When I establish a connection to DB, I have to write about 30,000 records with an INSERT statement. I've read in a book that I should do it ! Questions Do I need to do SQL_NO_CACHE ? Is it gonna ...
1
vote
1answer
160 views

Which should be used after messing up clustered index? Update statistics, Reindex or Reorganize

If I have a clustered single column index, e.g FooId in SQL Server 2008, 2012 and I know I have messed up the order of the rows to not have sequential order of the FooId column anymore, which ...
0
votes
1answer
166 views

The sort column must be the last column used in the index - MongoDB Indexing Advice

MongoDB docs advices that: The sort column must be the last column used in the index Here is the example: db.foo.ensureIndex({a: 1, b: 1, c: 1}) Good: find(a=1).sort(a) find(a=1).sort(b) ...
1
vote
1answer
708 views

collection partitioning in mongodb

Is there any semantic like table partitioning in MongoDB? My data is not that much that it would need sharding. but data is not important(but not completely useless) after several months.
5
votes
2answers
3k views

Implementing an encrypted table in SQL Azure

I'm moving an App to Azure and need to achieve PCI compliance for a couple columns in a single table. I realize I can encrypt the data before I store it, but I was wondering if there was a feature in ...
4
votes
2answers
3k views

What happens when InnoDB hits its tablespace autoextend max?

The latest version of High Performance MySQL (3rd ed.) recommends that one: ...place an upper limit on the tablespace's size to keep it from growing very large, because once it grows, it doesn't ...
30
votes
1answer
9k views

What's the point of WHERE 1=2 for SELECT INTO table query

If we want to create a new table from an existing one in SQL Server we can do the following SELECT * into Table2 from Table1 WHERE 1=2 Whats the point of where clause? I tried it without the where ...
12
votes
2answers
14k views

Subqueries' aliases same as main queries' aliases

I have an SQL query whose aliases are the same as some of its subquery's aliases. For example: select * from ROOM r where ... ( select * from ROAD r ...
2
votes
1answer
1k views

Oracle SQL Loader - load file where record is composed of “n” delimiters

How would you write a control file for Oracle SQL Loader that treats each record as being composed of 3 commas? I have read the documentation, however I cannot find how to handle this scenario, ...
2
votes
1answer
3k views

MySQL Workbench ERROR 1064

I have a problem with my database. I have created the model and now I want to sync it with my database on my WAMP Server (local) but it keeps giving me an error. I searched for the cause of the ...
15
votes
1answer
25k views

Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity

I really need some mysql expertize. I am a newbi to mysql and I am seeing some server crash of my db in the past 1 week. I am using mysql 5.1.36 on Ubuntu. This is a dedicated mysql server with Dual ...
2
votes
1answer
323 views

How to determine the size of a SQL Server database for a specific date in the past?

Microsoft SQL Server 2008 R2 Standard What I would like to find out is what the size of the DB was at specific dates in the past. I don't have backups going back that far. The current log may or ...
5
votes
1answer
5k views

SELECT with large WHERE IN clause taking long time

I'm pretty new at all this so be gentle :). I have large medical DB where I need to extract data for patients based on selected doctor(s). Right now I build a patient list by SELECT patID FROM ...
5
votes
1answer
320 views

Oracle: DB *partial* constraint?

I am pretty new to Oracle and the database world. I am not sure if the title makes any sense at all, so I'll just go ahead and explain my situation. I currently have a table at hand that records ...
2
votes
2answers
867 views

In trying to setup back up device, net use command throws operating system error 53 when passing in a unc

So I'm trying to use XP_CMDSHELL to set up my drive for using a backup. I already have the drive mapped, but per ...

15 30 50 per page