3
votes
2answers
18 views
Does an index with multiple columns make a similar index redundant?
Let's say I have a table that looks like this:
CREATE TABLE Activity (
ActivityID int primary key identity(1,1) ,
ActivityName nvarchar(10),
InactiveFlag bit
)
with an index that looks ...
0
votes
1answer
6 views
Percona xtrabackup Prepare Fails
I made a backup and according to Percona it completed successfully. Now I'm trying to prepare it and having the following issue:
./xtrabackup_55 --defaults-file="/etc/my.cnf" ...
1
vote
1answer
14 views
Postgres 9.2 select multiple specific rows in one query
I have a table with three columns,
Id Serial.
Value real.
timein Timestamp.
I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two ...
0
votes
0answers
4 views
Managing version control of Erwin file changes with multiple environments
What is the easiest way to manage three environments (DEV, QA and PROD) within a single Erwin file or multiple Erwin files? The Erwin file is separated by subject area, so I'm not sure reverse ...
0
votes
1answer
11 views
JOIN to read first table and get possible relationship in the second table
Consider a main table as
CREATE TABLE groups
(
group_id int(11) NOT NULL AUTO_INCREMENT,
group_title varchar(255),
PRIMARY KEY(group_id)
) ENGINE=InnoDB
and a second table for relationship as
...
3
votes
1answer
42 views
How to get a row_number to have the behavior of dense_rank
I have a stored procedure will be used to page through data. One of the requirements of the procedure is to have a parameter that will be used to sort two columns of data, the sorting should be used ...
2
votes
1answer
24 views
Can I decrypt TDE transaction log files for use with 3rd party software?
Is there a way to decrypt TDE log files on disk for use with 3rd party software? I have the original key and certificates. I am trying to use the transaction files with a 3rd party tool in order to ...
0
votes
0answers
9 views
XML data source with XSD validation importing the same data despite different files
Please bear with me, I've never used XML as a data source, nor am I familiar with XML itself, so I'm not entirely familiar with the nomenclature.
I have a series of XML files with data to be imported ...
2
votes
2answers
54 views
Why do linked servers have a limitation of 10 branches in a CASE expression?
Why does this CASE expression:
SELECT CASE column
WHEN 'a' THEN '1'
WHEN 'b' THEN '2'
... c -> i
WHEN 'j' THEN '10'
WHEN 'k' THEN '11'
END [col]
...
1
vote
1answer
21 views
Generate List of Missing Relationships
I have two tables, CLUSER and CLUSERRX. CLUSER contains all the employees at my company and CLUSERRX contains a list of all the people in CLUSER with a specific workgroup and all of the users from ...
1
vote
2answers
35 views
View column has an 8000 character limit?
In SQL Server 2008 R2 we are using an updateable view to update a varchar(max) column. When we update the column with text and then check the number of characters, it is showing a length of 8000 ...
5
votes
0answers
46 views
Troubleshooting SOS_SCHEDULER_YIELD wait
Running our corporate ERP (Dynamics AX 2012), I noticed our production environment seemed much slower than our development systems.
After performing the same activities in both the development and ...
1
vote
2answers
29 views
Is it possible to remove a database server from a cluster resource, but keep it running on the node
I was wondering if anyone knew whether it was possible to delete the cluster resource, but keep all of the disks and sql instance running on the node instead? So if I was to delete the cluster ...
0
votes
0answers
33 views
Cannot run job NT Authority\System error [duplicate]
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'domainname\servername$'.
I receive this error when I try to run the job. I have full permissions; I have also tried to run this job under ...
2
votes
2answers
44 views
Performance effect of indexing foreign keys
My database is for an HR application with performance reviews and I am interested in whether I should index my foreign key.
There are two tables involved. The first is a ratings table with 2 columns: ...
0
votes
0answers
8 views
How to do an in-place upgrade of SQL Server 2005 Express to SQL Server 2008 R2 Express
I would like to upgrade my SQL Server 2005 Express instance which is installed using this command:
SQLEXPR.EXE/qs /action=INSTALL /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQLEngine /INSTANCENAME=ABCD ...
0
votes
1answer
37 views
Migration to databases Domain Users do not have access
I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be ...
1
vote
2answers
28 views
MySQL version confusion
How is this possible:
I installed mysql-5.6.11-osx10.7-x86.dmg which is supposed to be version Mac OS X ver. 10.7 (x86, 32-bit), DMG Archive (32 bit!)
But upon SHOW VARIABLES LIKE "%version%"; I get ...
0
votes
1answer
20 views
dimension table with “like” condition
In an effort to scale some existing code, we are removing logic from CASE statements and putting it in dimension/lookup tables. Thus instead of CASE WHEN 'F' THEN 'Female' WHEN 'M' THEN 'Male' is now ...
1
vote
1answer
44 views
How do I turn off ALTER DATABASE script output in VS 2012?
I am using SQL Server 2005 in conjunction with Visual Studio 2012 and a SSDT database project.
When I publish my VS project and generate the script to run against my database, VS includes the ...
-1
votes
0answers
21 views
How can I delete duplications with one query? [closed]
Is there any way to delete duplications with one, or only two queries? I'm not good with SQL.
Now I'm trying this solution:
$le = mysql_query('SELECT * FROM tbl_answers');
while($i = ...
1
vote
1answer
28 views
How would I change this relational model to fit a nosql database?
Because of things beyond my control, I have to switch from a relational database to MongoDB for the advertising platform I'm helping to build. I've got a relational schema sketched out, but I'm unsure ...
2
votes
1answer
29 views
How can I include more query in one query?
These are my tables:
tbl_answers =>
aid
qid
answer
uid
dateposted
emailnotify
namedisplay
status
isbestanswer
tbl_questions =>
qid
question
...
0
votes
1answer
19 views
Separate table by often changed / not often changed data?
I have a application which querys different gameservers, and stores for example if the server is online, and how many players are connected. But of course there is also data which is changed rarely.
...
1
vote
0answers
38 views
Is there a way to generate an execution plan for a stored procedure before executing it?
We have a very large (many millions of rows) table that contains some (few million) rows with invalid values in particular columns.
A stored procedure exists to correct the invalid records but it ...
0
votes
1answer
15 views
Does MySQL reserve memory for MEMORY tables based on max_heap_table_size
Currently I have a set of large MEMORY tables, and have previously calculated how much space I believe each will require and assigned max_heap_table_size accordingly so that the sum is not greater ...
0
votes
1answer
2 views
How to achieve this sql server replication scenario?
I want to achieve below illustrated scenario via sql server replication.
Client A and Client B are client databases containing same article Table A. This article needs to be published by Client A ...
1
vote
1answer
28 views
Migration of create statement from SQL Server to MySQL
I have a table that have the following table structure
CREATE TABLE [dbo].[ASAT](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ASATID] AS ('ASAT'+right('00000000000000000000'
...
1
vote
0answers
31 views
Why is dynamic pivoting not supported?
TL;DR What is the reason that in 2013 we still can't define a VIEW that displays some pivoted data with a dynamic column set.
TL;DR/2 Is pivoting data a denormalization technique?
First: Not going ...
-1
votes
0answers
22 views
How to correct my Oracle SYSTIMESTAMP?
My database's timestamp has 8 second difference with my OS system time (as well as time from the internet). This caused some bug in my code. How to correct this?
0
votes
2answers
30 views
Usage of foreign key when the referenced table data will not change
I have to design a table where user has 10 fields and 6 are dropdown. Some are single selections and some multiple. Dropdown data is supposed to change very rarely.
For e.g., the user is presented ...
0
votes
2answers
38 views
How to quickly startup/shutdown Oracle 11?
I am wondering what is the quickest way to properly startup/shutdown a Oracle DB daemon (Oracle 11.2, installed on a test machine).
I need it for C/C++ programs that use the OCI/Pro*C API.
Currently ...
0
votes
0answers
33 views
How to improve the performance for SQL Server Count(distinct()) and Sum() functions? [closed]
The below one is my query. It's taking 12 seconds for the execution process. The count(distinct()) and SUM() functions are taking long time for execution. I tried it after create the non-cluster index ...
-1
votes
1answer
25 views
data warehouse construction from mdf and ldf file via etl
I have sample data warehouse stored in the form of mdf and ldf format. I want to use any ETL tool to extract data and then load datawarehouse on the specified database with username, password and ...
0
votes
0answers
15 views
Oracle Grid Control
How can I implement all the mentioned below points for monitoring in Oracle Grid Control?
Datasource Monitoring
JMS Monitoring
Composites (State (Stop/Retired/Undeployed)
Physical servers (Virtual ...
0
votes
0answers
9 views
Would Mongo's ObjectID work well in InnoDB's clustered index?
Mongo's ObjectID is defined like this:
ObjectId is a 12-byte BSON type, constructed using:
a 4-byte value representing the seconds since the Unix epoch,
a 3-byte machine identifier,
...
1
vote
3answers
27 views
Creating a global temp table in MySQL
I am working on a MySQL stored procedure.
I need to create a temp table and I want to access this temp table whenever I execute this stored procedure.
I know we can't access the temp table in ...
0
votes
2answers
36 views
Is there a good reason to store big text data outside database?
I was analyzing my MySQL database performance and found that tables containing text fields are just huge, larger than 1Gb.
This data is only used when loading single record (like article or blog ...
1
vote
2answers
53 views
How to attach an .mdf file that was not properly detached?
One of our clients had a SQL Server 2000 database, on a system which is no longer running. They have no idea what was in the database. They have given me the .mdf and .ldf files, and want us to ...
1
vote
0answers
20 views
Job accessing linked server yields error 7391
I created a stored procedure which queries data from a linked server to insert into a local table.
I tested this stored procedure by directly executing it; it works fine.
I have to run this stored ...
-1
votes
0answers
38 views
Unexpected index scan against partitioned table
I have a set of partitioned tables with CIX on (ID, DATE) and partitioned on DATE.
There is another identical set of non-partitioned tables with CIX on ID.
Case A: T1 and T2 are non-partitioned ...
0
votes
0answers
18 views
Properly index magento mysql tables to improve specific query performance
I am running magento community edition and its db performance is horrible. I have the following query:
SELECT `width_idx`.`value`, COUNT(DISTINCT width_idx.entity_id) AS `count` FROM ...
0
votes
0answers
29 views
Simple SQL statement [migrated]
I've been over-thinking this too much. Let's say I have a table TEST(refnum VARCHAR(5))
|refnum|
--------
| 12345|
| 56873|
| 63423|
| 12345|
| 56873|
| 12345|
I ...
-4
votes
1answer
35 views
Oracle database and data has been corrupted. Is there a way to get it back? [closed]
The Oracle database has been corrupted and both the data and backups were lost in the corruption. Is there anything I can do to recover my data? It would be terrible if it were all lost.
3
votes
1answer
34 views
checksum(newid()) as primary key - what happens if a collision occurs?
Inspecting a rather critical database that is used by some software on my system, I found that one of the tables had a primary key on the column Id, where Id is calculated using checksum(newid()). ...
2
votes
1answer
33 views
Best strategies to have a backup of an RDS MySQL database
I have an AWS RDS MySQL database. I'm not a DBA, so my knowledge is limited.
I wish to design strategies to have zero data loss in case of a failure, in terms of replication or data backup.
I know ...
1
vote
0answers
28 views
Merging two Access tables into one
I have mostly identical tables, however there are slightly differences like missing columns in older ones. I want to merge 5 tables into one. Is there any easy way to do this?
7
votes
2answers
115 views
Is there any way to prevent the memo structure from being pruned?
We know that the memo structure is pruned and some expensive alternative plans are discarded during optimization. I was wondering if there is any way to prevent this and let the optimizer just ...
4
votes
1answer
38 views
How to dive into the PostgreSQL's optimizer and trace each step it takes to optimize the query?
Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Does PostgreSQL have the similar structure which records steps the ...
0
votes
1answer
37 views
MySQL Workbench - question about creating FK relationships
Forgive me a little new at databases and I came across something while working with Workbench that I have a question on.
In a very simple testing database, with three tables: Company, Department, ...
