2
votes
1answer
387 views

IDENTITY_INSERT seems stuck

I might be forgetting something, but essentially, I'm struggling with IDENTITY_INSERT. I'm trying to move data between tables, both of which have an identity column (similar schema). SET ...
11
votes
3answers
6k views

Why is an aggregate query significantly faster with a GROUP BY clause than without one?

I'm just curious why an aggregate query runs so much faster with a GROUP BY clause than without one. For example, this query takes almost 10 seconds to run SELECT MIN(CreatedDate) FROM MyTable WHERE ...
2
votes
1answer
68 views

Would appreciate some advice on using external data with SQL Server 2005

I'm a web developer with a bit of DB experience, but this one is beyond my knowledge. We're trying to set up a new website. The data - products, prices, descriptions etc - are supplied by an external ...
1
vote
1answer
834 views

Copy SQL table to other server identity becomes int

I just built an Umbraco website. This uses a not-that-small SQL Server db. I copied this DB using the export data function built in in SQL Server 2008. This copies all the tables and their content ...
1
vote
1answer
2k views

How to recover the innodb when mysql data dir moved from one system to another system?

I have transfer the mysql data dir from one linux box to another box. When i start the mysql server. I can find that the innodb based DB are corrupted. So, may i know how to recover the corrupted ...
2
votes
1answer
646 views

Reverse engineer Oracle database in visio

Trying to reverse engineer tables added into a database but when i connect, I get strange Chinese symbols with a lot of apex stuff. I dont see anywhere the tables that i added, what am i doing ...
2
votes
2answers
1k views

SQL Server: in what situation can snapshot restore be blocked after SET SINGLE_USER?

In my tests, i have the following script: USE master; ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE [TestDB] FROM DATABASE_SNAPSHOT = 'TestDBSnapshot' In some ...
3
votes
1answer
1k views

Should I use DynamoDB or stick with MySQL via webservice for my mobile app syncs

I asked this earlier at Stack Overflow but a user advised me to ask it here since this website is more relevant to database related questions. I made an Android app, name of which I'll not mention at ...
4
votes
1answer
1k views

Difference between table fillfactor and index fillfactor

In Postgres, one can set fillfactor for an index as well as a table. What is the difference? How to decide values for either. What are the usecases? I am trying to cluster a spatial relation on a ...
1
vote
0answers
424 views

How to dump an advanced revelation database?

Retail store I work with has an ancient inventory/accounting/receiving system that is based off Advanced Revelation. They run the DOS and Windows clients. I'd like to get a dump of their data to ...
2
votes
1answer
6k views

How do I fix this corrupted page?

My database is in simple recovery mode. One page got corrupted and I only have backups created using simple backup. When I run this: DBCC PAGE ('myDB', 1, 952663, 3) I get: DBCC PAGE error: ...
8
votes
2answers
2k views

Is there a way to grant a user access to only all non system views of database?

In SQL Server, I have a user in a particular database and I've been asked to grant them access to all of the non-system views of the database only. I believe this can be done by editing securables of ...
4
votes
3answers
3k views

Linked servers login failure: Impersonate works, Username/password fails

I'm trying to set up a linked server between 2 instances of Sql Server 2008 R2. The login works perfectly when I select the impersonate option. However when I try to use the remote user/password ...
4
votes
1answer
11k views

SQL Server Activity Monitor - Resource Waits - Logging > 1400 ms/sec, how?

I have an SQL Server 2008 R2 server. I open "Activity Monitor", and inside the "Resource Waits" category, I see that the "Logging" category is taking up more than 1400 ms/sec. That doesn't seem to be ...
12
votes
1answer
1k views

Monumental difference in execution time between queries when using RECOMPILE query hint

I have two almost identical queries running on the same SQL Server 2005 instance: The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application ...
1
vote
1answer
3k views

Resolving frequent LGWR switch events recorded in Event log with heavy dblink/materialized view activity - Oracle 11g XE

We use a student information system, PowerSchool, marketed by Pearson. PowerSchool uses Oracle as a backend database, and we have the need to create some of our own reporting views. Pearson says if ...
7
votes
2answers
8k views

How to apply a theme to SQL Server Management Studio 2008?

Has anyone had any success with, for example, applying a 'dark' them to the Text Editor component of of SSMS 2008? There was an old trick for 2005 which is no longer operational and has not been ...
7
votes
1answer
9k views

Why does a query run slower in a Stored Procedure than in the Query window?

I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure? Here's what my query looks ...
1
vote
0answers
75 views

How to write up the specs for the server that we will need

I am a dev. and have written basic app. which will be used by customers this app. Basically, I present them screen to select their excel file which has some billing info. we are analyzing this billing ...
4
votes
1answer
889 views

How are binlogs updated for MySQL temporary tables?

If I use CREATE TEMPORARY TABLE, can I write and update this MySQL table and know that none of it will be stored to the binlogs? If I use data from the temp table to write to a non-temp table, is the ...
8
votes
3answers
19k views

How to do a case sensitive search in WHERE clause?

I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings. Any idea on how to do a case sensitive search in SQL query?
1
vote
3answers
248 views

getting error while starting memecached service with ndb engine

i am installed memcached library by following the steps in the internet. now there is a binary file created in bin folder of mysql now i exicuting the command memcached i am getting error like this ...
6
votes
2answers
6k views

SQL query slow-down from 1 second to 11 minutes - why?

Question: I'm porting the below query (listing tables by foreign key dependencies) to PostGreSql. WITH Fkeys AS ( SELECT DISTINCT OnTable = OnTable.name ,AgainstTable = ...
7
votes
3answers
718 views

What happens to non clustered index when included column is updated using update statement?

Question on non clustered index with included columns (DB - MS SQL Server). I read blog Optimized Non-clustered Index Maintenance which gives information on query plans when update statements is ...
2
votes
1answer
1k views

SQL on Mac OS X

I'm looking for something like "Oracle database express" to be used on a mac. I hope this question won't offend anyone though. I'm absolute beginner on sql. Your suggestions are most welcome.
5
votes
1answer
5k views

Create a new column in a view and assign it a data type

here is my query: CREATE VIEW VIEW2 AS SELECT [Column1], (NULL) AS column2 VARCHAR(10) FROM VIEW1; I get an error in the third line. If I leave the query like this, without specifying the data ...
5
votes
2answers
803 views

Running each SQL Server service under different Windows accounts

Besides the Database Engine, SQL Server has some additional Services, namely: SQL Server Integration Services SQL Server Analysis Services SQL Server Reporting Services SQL Server Agent Should ...
9
votes
2answers
1k views

100 TeraBytes Capacity Database - Resources and Time Estimates

I am working on a 'back of the envelope' calculation for a 100TB reporting database setup. I am seeking out thoughts from the experts here. Proposed environment: Storage Capacity ~ 100TB Tables ~ ...
1
vote
3answers
484 views

Big size of database logs SQL Server 2008

I have a database which is running under Microsoft SQL Server 2008. Now, I have seen that the log of the database (the .ldf file) is growing to big size. The database file (.mdf) has a size of 630MB ...
2
votes
4answers
1k views

Very small MySQL table keeps crashing

I have a MyISAM MySQL table that keeps crashing. The table has only 16 rows and is very rarely written to (maybe once a month). I have a medium-sized site that typically has about 500 pageviews per ...
3
votes
1answer
880 views

Upgrade SQL Server database Using Compatibility Level

I've made a backup of a SQL Server database running on SQL Server 2008. I've restored that backup into a SQL Server 2012 instance. I've set the compatibility level on the restored instance to SQL ...
2
votes
1answer
443 views

How do we create spatial index on views if it is possible?

Is it possible to create spatial index on sql server 2008? If yes how do we create it on table with schemabinding?
1
vote
1answer
294 views

How should I distribute my tables for better performance from fragmentation point of view

I have a database on SQL Server 2008 R2 with about 20 non-indexed tables that are continuously undergoing INSERTs AND DELETEs. There are about 400,000 records @ 4KB/record being inserted or deleted ...
5
votes
1answer
11k views

Why is LIKE more than 4x faster than MATCH…AGAINST on a FULLTEXT index in MySQL?

I'm not getting this. I've got a table with these indexes PRIMARY post_id INDEX topic_id FULLTEXT post_text Table has (only) 346 000 rows. I am trying to perform 2 queries. SELECT ...
0
votes
1answer
181 views

Recommended Data profiling tool for BI developer

What recommended data profiling tool to retrieve a lot of information and enable to visulize the data into graphical statistics? The data is coming from the source database.
1
vote
1answer
696 views

SQL Server 2008 R2 Express with management tools installation failure

I've been trying to install SQL Server 2008 R2 with tools. I've downloaded thisn version and it just won't work! I've had some problems installing Visual Studio SP1 due to silverlight4 being ...
2
votes
1answer
372 views

select for update gives error on indexed column

here is my code: mysql_query("start transaction"); $q="select max(id) from test for update"; $r=mysql_query($q); $row=mysql_fetch_array($r); for ($k=0;$k<100000;$k++) { $q="insert into test values ...
1
vote
1answer
446 views

Right Tool for Data Mapping in Relation to Business Intelligence

When you are working as a BI developer you also need to create a data mapping between dimensional modeling and source system. What kinda application to visualize a data mapping?
1
vote
1answer
521 views

Why is the output of this query being returned like this?

I have a T-SQL query : select CAST(0 as float(53)),CONVERT(nvarchar(max),CAST(0 as float(53)),128) the output of which is 0,0.0E0 Can some one explain why it is not 0,0 ? The database is SQL ...
0
votes
1answer
188 views

Sensible data management tool for importing and exporting of large data files

I am looking for some sensible suggestions on data management tools for large data files. I have been experimenting aith excel and access only to find that access has limitations on import files size ...
9
votes
3answers
8k views

Inventory database structure when inventory items have varying attributes

I'm building an inventory database to store enterprise hardware information. The devices the database keeps track of range from workstations, laptops, switches, routers, mobile phones, etc. I'm using ...
4
votes
2answers
2k views

“freeing items” taking forever on inserts, updates and deletes

Recently I migrated all my MySQL database tables from MyISAM engine to InnoDB. I'm using the DotCloud MySQL service as my server (http://docs.dotcloud.com/services/mysql/). After the migration, ...
2
votes
1answer
297 views

Replication and REPAIR TABLE

When running a repair table on a corrupted or damaged table, will this guarantee replication to break? Documentation says "in the event that a table on the master becomes damaged and you use REPAIR ...
8
votes
3answers
3k views

Comparing Columns that can contain NULLS - is there a more elegant way?

I know that you can't compare a value to NULL and expect a result without adding something like in the following code... SELECT * FROM A INNER JOIN B ON A.ID = B.ID WHERE A.STRING ...
12
votes
4answers
11k views

What is an audit table?

What are audit tables? How are they useful? I came across them reading this article.
1
vote
2answers
394 views

changing a range into discrete values for a field in oracle

I have a data table that contains records like this: ZIP Ground Air 010-041 005 305 150-163 004 304 As I have it right now, it's actually an excel file but I eventually need to ...
1
vote
1answer
179 views

what are the checklist when looking at MySQL database for first time?

What should I put on my checklist when I look at MySQL database for the first time? I would appreciate your help. Thanks.
1
vote
1answer
383 views

How to propagate a grandparent relationship throughout a tree?

I've got a MySQL table with columns id and parent, wherein each row has another row as a parent, except for some special root rows with parent=0. There are no cycles or broken relations. In order to ...
3
votes
3answers
1k views

Speed up search across multiple columns

We have a table: user_info (user_id,domain_id,first_name,middle_name,last_name,work_place,.,.,.) Now, we have a user search that search across three columns first_name,last_name and work_place. ...
5
votes
2answers
236 views

Is there a reason/benefit I am not aware of for this kind setup?

I now manage a MySQL database that was designed and created by another. I am confused about a certain setup that the original developer created.. He did not use auto increments for table IDs. ...

15 30 50 per page