All Questions

Filter by
Sorted by
Tagged with
18
votes
3answers
47k views

How to use prepared statements within MySQL stored procedures?

I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable ...
5
votes
2answers
5k views

tsql: table data size won't shrink after large row deletion

i have a table with 12 months+ data in it. I backup the table then delete all rows keeping only rows inserted in the last month. But the table size won't shrink, nor the underlying physical file. ...
8
votes
4answers
7k views

How do I do a complex GROUP BY in MySQL?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want ...
5
votes
1answer
6k views

How do I make MySQL auto increment in random order?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to create a new column for each key that would be an integer such that ...
7
votes
1answer
2k views

Nonclustered index column order

I have a nonclustered index (called NCIDX1) on col1, col2 of a table. I included col3, col4, col5, col6 as included columns in this order. If I run the following query: select col1,col2 from ...
9
votes
4answers
45k views

Why does MySQL say I'm out of memory?

I was trying to execute a fairly large INSERT...SELECT in MySQL with JDBC, and I got the following exception: Exception in thread "main" java.sql.SQLException: Out of memory (Needed 1073741824 bytes) ...
4
votes
1answer
648 views

Impact of an Asynchronous Mirror / Full Recovery Model on Performance

For an asynchronous mirror in SQL Server 2008 R2, using the full recovery model is required. Assuming that the network and disk IO on both sides of the Mirror can keep up with the transaction logs ...
14
votes
1answer
10k views

sp_WhoIsActive and get_plans -- Actual Plan?

Does Adam Machanic's sp_WhoIsActive return the actual query plan or the estimated plan when run with @get_plans=1 ?
5
votes
1answer
224 views

When refreshing materialised views does the index automatically refresh

When refreshing materialised views does the index automatically refresh
30
votes
5answers
10k views

Why use an int as a lookup table's primary key?

I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string). I understand that using a ...
5
votes
1answer
455 views

Error moving SQL Server 2000 to SQL Server 2005 NOLOCK Error

I need to move a database from the SQL Server 2000 (32 bit) to SQL Server 2005.But,When I attacted my database to SQL Server 2005,it's gives me error.(NOLOCK error) Also , After I running dbcc check ...
4
votes
1answer
10k views

Referencing PL/SQL variables in FOR loop

I have written a PL/SQL script to find the size of a long column in a table. Just to make the script generic I am passing the table Name and column name as variables , but I am getting an error saying ...
20
votes
7answers
10k views

Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

I've got about a billion rows of data in a table with a name and an integer in the range 1-288. For a given name, every int is unique, and not every possible integer in the range is present--so there ...
6
votes
7answers
21k views

Can't Login to SQL Server 2008 when running single user mode

I'm following the MS documentation here to migrate logins from one instance of SQL Server to another as part of the process described here. This involves putting the server into single user mode (...
9
votes
1answer
1k views

Materialized views in oracle

How are materialized views implemented in oracle? I've created a materialized view in oracle. What I now see is a quite normal table with a job. Are materialized views translated into tables & ...
8
votes
1answer
6k views

Oracle materialized view refresh schedule

Since I see that it is possible to specify an expression like sysdate + 1, here are my questions: Is it possible to specify the exact time of the refresh for materialized views in oracle? Is it ...
23
votes
3answers
56k views

How do I install pgcrypto in PostgreSQL 8.4?

I'm using Ubuntu Server 10.10 and I have installed PostgreSQL 8.4 using apt-get install postgresql. I would like to use the built-in sha1() function, but it seems that I have to install pgcrypto first....
5
votes
1answer
7k views

Using a Parameter Query to generate Charts in MS Access Reports

I am using Microsoft Access 2007 and I have a query which looks something like this; PARAMETERS Identifier TEXT(255); SELECT DateDiff('h', (SELECT Min(SampleDateTime) FROM Specimen WHERE UserID=[...
11
votes
2answers
1k views

A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?

We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box. I normally do this by copying .mdf and log files over and attaching them but I'm not able ...
42
votes
3answers
77k views

Execution Plan Basics -- Hash Match Confusion

I am starting to learn execution plans and am confused about how exactly a hash match works and why it would be used in a simple join: select Posts.Title, Users.DisplayName From Posts JOIN Users on ...
16
votes
1answer
2k views

Sql Anywhere 11: Restoring incremental backup failure

We want to create remote incremental backups after a full backup. This will allow us to restore in the event of a failure and bring up another machine with as close to real time backups as possible ...
11
votes
5answers
29k views

User Permissions Messed Up following a Backup -> Restore Operation

I had to move several SQL Server 2008 databases to our new db server so I backed them all up (to .bak files), copied these files over to the new box and restored them (all done using SQL Management ...
3
votes
0answers
1k views

An advice to design a fact table with a scd as a dimension

I have a problem of conception and I would like to get your ideas on how I'll be able to do what I am aiming. My goal is to create a datawarehouse, with three dimensions and one fact table so far. ...
4
votes
3answers
2k views

How does NHibernate handle execution plan?

I heard from a podcast that there are no ORMs that have a good solution for execution plan reuse. It will lead to increased execution plan cache which affects the performance. How does NHibernate ...
6
votes
1answer
4k views

When should I use Full-Text Search?

When should I use Full-Text Search instead of using a LIKE search, such as SELECT * FROM myTABLE WHERE myCOLUMN LIKE '*something'
6
votes
1answer
19k views

Running a stored procedure across db Link

I have a script whose sole propose is just to transfer data from one schema to another via a db link. Part of the process however is that after the data has been loaded, a stored procedure needs to be ...
9
votes
3answers
4k views

Specify Connection in Management Studio T-SQL Query

When adding users are roles to DB servers I often use the "Script this action" function from the GUI. I then just go to "Connection :: Change Connection" to do the same on my other servers. Is there ...
9
votes
2answers
2k views

Resources for SQL Server developer migrating to Oracle

What resources can you recommend for a SQL Server developer wanting to learn Oracle basics? I am looking for a comprehensive whitepaper or blog post describing differences between these systems and ...
3
votes
5answers
5k views

Best way to automate running several MySQL commands [closed]

We have several core db tables on our system and allow our employees access to these tables via views that just show them the data they need to see. We created these views in a new db specific to ...
3
votes
2answers
4k views

Database mirroring - Witness only

In SQL Server 2008 R2 Express the only mirroring alternative is "Witness only", however I can't find a good explenation to what this is. I would like to have a database on a virtual server and then ...
6
votes
2answers
4k views

Rebuild large Oracle 10g index with insufficient TEMP space?

One of my (Oracle 10g) tables has grown over time to about 840 million rows. An index (DATE, NUMBER(38)) on it occupies 38GB at present. I'd like to change the index (I think it could make good use of ...
3
votes
1answer
302 views

Can I use dbms_redefinition with bulk data transformations for better results?

Here is a situation for bulk loading in Oracle: Schema A { T1, T2, T3, T4} Schema B { TR1, TR2} Where schema B is re-factored from schema A. Both exist in same DB. Schema A has terabytes of data ...
7
votes
3answers
730 views

MyISAM Performance: Join Decomposition

in High Performance MySQL on page 159 they talk about breaking up complex queries into simple ones: Converting SELECT * FROM tag JOIN tag_post ON tag_post.tag_id=tag.id JOIN post ON tag_post.post_id=...
4
votes
2answers
3k views

SQL Server DR Plan - Test Documentation

I have been tasked with creating a document for the testing of our DR/BC Plan for our SQL Servers. We are using DB Mirroring (tier-1) and transaction log shipping (tier-2) and for tier-3 databases we ...
3
votes
2answers
762 views

How to create a user with a particular user_id?

Is it possible to specify the user_id of a user while creating it? SQL> create user user1 identified by user1; User created. SQL> select user_id, username from all_users where username='USER1'...
5
votes
1answer
2k views

Oracle query against SQL Server database giving inconsistent results

We have multiple Oracle 11.2.0.2 databases with og4odbc database links to a SQL Server 2008 database. A particular table in the SQL Server database has two Varchar(36) columns. The following ...
2
votes
1answer
259 views

Why am I not seeing any benefits of parallel execution when parallel clause is used?

I have these two methods of creating a table and populating with rows. The first one uses a parallel (degree 3) clause, so I expected the inserts to be much faster. But I timed the time taken for a) ...
1
vote
1answer
2k views

SQL Server 2008 R2 - how to repair System Views

I have SQL Server 2008 R2 database, which is corrupted. Dbcc checkdb command gives result: Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while collecting facts. ...
2
votes
2answers
845 views

SQL 2008 - DBCHECK Failed

I am using SQL Server 2008 and have a Maintenance Plan setup that does a DBCHECK before the nightly backup. This has been working fine for months. It has started failing. If I run a DBCHECK on the ...
5
votes
3answers
108k views

SQL Server 2008 R2 (Suspect) mode - how to repair?

I have SQL Server 2008 R2 database in Suspect mode. I tried to fix it running this query: EXEC sp_resetstatus ‘yourDBname’; ALTER DATABASE yourDBname SET EMERGENCY DBCC checkdb(’yourDBname’) ALTER ...
3
votes
1answer
263 views

How do you convert IBM DB2's recursive increment to MySQL version?

DB2 Queries: WITH TABLE1 (YEAR_END) as( VALUES (2011)) , TABLE2 (YEAR_END) AS ( VALUES(2011)) , TABLE3 (YEARS) AS ( SELECT 0+2008 FROM TABLE1 T1, TABLE2 T2 union ...
9
votes
1answer
15k views

Breaking and Restoring a mirror

I have to do some maintenance on a secondary node of a mirror. Since this is the result of the site becoming slow while the mirror re-established after a brief network outage I want to be safe and ...
4
votes
1answer
3k views

nest SELECT in INSERT

I'm trying to do something like: INSERT INTO stories (vid, ip, body, timestamp) VALUES ( (SELECT vid+1 FROM stories ORDER BY vid DESC LIMIT 1), INET_ATON('127.0.0.1'), 'test', NOW(...
3
votes
3answers
2k views

ms sql differential backup size jumps

We are doing a full backup once a week and a daily diff between that. The total db is 80Gb, the diffs start out under 1Gb, then gradually grow to approximately 3-5Gb which is ok, but then all of a ...
5
votes
3answers
4k views

Port number change

We are planning on setting a static port for SQL Server 2005/2008 failover clustering. Any guide on what port to choose/select for 4 nodes of each cluster?(Active/Active) I am also thinking some ...
34
votes
7answers
49k views

What are reasons **NOT** to use the MEMORY storage engine in MySQL?

I recently discovered that MySQL has a "memory" engine that I wasn't aware of (most of my database work is for hobby projects so I learn what I need as I go). It seems like this option should give me ...
12
votes
3answers
1k views

Executing same request from C# VS SSMS give different execution time

I have a request like this one SELECT [EstimateId], [CreationUserId], [EstimateStatusValueId], [LanguageId], [LocationId], [EstimatorUserId], [FilterUnitSystemTypeId], [EstimateNumber], [...
5
votes
1answer
3k views

Migration from DB2 to MySQL

Does any one have experience on migration from DB2 to MySQL ? Please share with me. I tried googling, but I have not received much information on this.
44
votes
6answers
38k views

In MySQL, does the order of the columns in a WHERE clause affect query performance?

I am having performance issues on certain database queries that have large possible result sets. The query in question, I have three ANDs in the WHERE clause Does the order of the clauses matter? ...
5
votes
1answer
12k views

How do I calculate values based on the previous row after skipping the first 12 rows?

I'm looking for help to write this query. I have included some sample data below. My initial premise is that all the values from the 13th row onward will be dynamic and the first 12 rows will be ...

15 30 50 per page