28
votes
5answers
5k views

Why is there still a varchar data type?

Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem) After working with ...
0
votes
1answer
2k views

V$SQL table elapsed time

Does the elapsed_time column in the v$sql table indicate the time per query exeuction or is it a sum of all executions?
2
votes
1answer
489 views

Using the special Dual table to read sequences

It there a performance impact when using the dual table to read a sequence' nextval over an direct insert? I'm currently optimizing the performance in a large IT system and what I've found is that ...
15
votes
4answers
24k views

Logical operators OR AND in condition and order of conditions in WHERE

Let's examine these two statements: IF (CONDITION 1) OR (CONDITION 2) ... IF (CONDITION 3) AND (CONDITION 4) ... If CONDITION 1 is TRUE, will CONDITION 2 be checked? If CONDITION 3 is FALSE, will ...
10
votes
5answers
12k views

Cannot drop nonexistent constraint and cannot create it either

While testing some migration scripts with a copy of production data (scripts run fine with development data) I found a curious situation. A CONSTRAINT has changed so I'm issuing DROP + ADD commands: ...
1
vote
2answers
2k views

How to set addtime(current_timestamp, '15:00:00') as MySQL timestamp column default value?

I want to set `addtime(current_timestamp, '15:00:00') as the default value of one MySQL table column. I tried to do this with the following command but failed: mysql> alter table mytable change c1 ...
4
votes
2answers
204 views

Best way(s) to time ad hoc queries and stored procedures

What are the most accepted ways to time your queries, whether they are ad hoc or stored procedures? Is it more accurate to use SQL Profiler than SET STATISTICS TIME ON?
3
votes
1answer
173 views

What are some efficient ways in using COUNT(*) with WHERE

Instead of calling COUNT(*) with a WHERE clause, is there anything one can do to make this work more efficiently on the server? I would like to get this count on complicated joins, just wondering the ...
2
votes
1answer
4k views

MySQL : Is there a command similar to FLUSH STATUS to reset GLOBAL STATUS?

I'm trying to run some load tests on my MySQL server and check a bunch of relevant GLOBAL STATUS variables after each run. Is there an easy way to flush GLOBAL STATUS variables like the regular FLUSH ...
1
vote
1answer
1k views

Conversion of varchar data to datetime failing

We are in the process of moving data from a legacy table (all varchar fields) into a strongly typed counterpart cue cheering As part of this effort, we are taking data from the base Entity table and ...
1
vote
1answer
328 views

Grant access for any logged in user for common database

In SQL server 2008, I have one database that is a common database that is accessed thru stored procedures in all other databases. Each of the other databases have their own login. I need a way that ...
1
vote
3answers
4k views

Why are my SQL Server 2008R2 databases not mirroring?

I'm trying to set up database mirroring in SQL Server 2008R2. I've taken a full backup and a transactional backup from my principal database and I've restored both WITH NORECOVERY. However, my mirror ...
4
votes
2answers
816 views

Will creating partitions reduce locking and how do we implement this in sql-server?

A client wants our application to process more data faster so arranged a meeting with their dba to discuss options. This application generates quite a lot of data that is used for reporting. Before ...
11
votes
5answers
22k views

MySQL server has gone away obstructing import of large dumps

I am trying to import a large sql dump (2GB) to my local mysql on my mac. I have been able to do this in the past (I was using MAMP), but now I get a ERROR 2006 (HY000) at line 7758: MySQL server ...
2
votes
2answers
505 views

Clustered monotonically increased index insert performance

I have a table with field Id (bigint, IDENTITY) as primary key and clustered index on it. I inserted 400 rows and saw execution plan. I got: the relative query cost for this insertion 36% and for this ...
0
votes
3answers
309 views

Does running a COUNT() statement using NOCOUNT ON; consume much

On millions of rows.. Is it worth caching the total amount or turning NOCOUNT OFF; instead of requesting it each time on that much data? Sql Server 2008 R2 Also, is running a COUNT() statement on ...
5
votes
3answers
312 views

Can this method for unit testing a stored procedure be improved?

When I have written unit tests for my stored procedures, they usually look something like this: /* Unit test for stored procedure which runs a query */ /* Define #temp table with structure of query ...
3
votes
2answers
2k views

sys.database_files / sys.filegroups VS sys.sysfiles / sys.sysfilegroups

These catalog views seem to show the exact same data. What is the point of having multiple system views that seem to show the same thing? Here is how I proved it is the same data: select ...
13
votes
2answers
7k views

Check constraint only one of three columns is non-null

I have a (SQL Server) table that contains 3 types of results: FLOAT, NVARCHAR(30), or DATETIME (3 separate columns). I want to ensure that for any given row, only one column has a result and the other ...
12
votes
1answer
1k views

What types of corruption can DBCC CheckDB miss?

This question was prompted by this earlier post and my having a database filed away for future investigation that was restored following: BACKUP 'BrokenDatabase' detected an error on page (1:123456) ...
6
votes
2answers
276 views

Row padding to get more performance

I have read a document (Designing Highly Scalable OLTP Systems) that describe a trick how to get more performance: Because rows are small (many fit a page) multiple locks may compete for one ...
2
votes
1answer
171 views

Change tables from one user to another

I created some objects in MS SQL Server 2008 using SubSonic 3. It created them based on the user specified in connection string like: test_user.table1 but for some reason I am not able to test_user ...
4
votes
1answer
439 views

Can Oracle Text query return the terms found?

I am querying an Oracle 10g database. The database contains a table with documents that has an oracle text index. I query the database using a query like SELECT SCORE(1), title from news WHERE ...
6
votes
2answers
219 views

SQL Server Internal Inconsistency on GROUP BY

I recently recovered a database from a hard-drive failure using DBCC CHECKDB WITH REPAIR ALOW DATA LOSS. Now I can select records from a table-valued function to retrieve sales records, but using a ...
2
votes
2answers
2k views

Materialized views use older dbms_job instead of scheduler?

Very new to this topic - using 11g. When I create a materialized view, a matching job (not a scheduler job) is created. But it's my understanding that dbms_job is being replaced by dbms_scheduler. ...
2
votes
1answer
232 views

Where can I find information on shredding xml with .nodes?

I recently saw this answer on SO which uses the "local-name" value while shredding xml using .nodes(). I've never seen this before: select T.N.value('local-name(.)', 'varchar(128)') as Name, ...
2
votes
1answer
551 views

What are the pro's and con's of using PostgreSQL vs MySQL for binary data?

I read that MySQL has binary safe blobs, but PostgreSQL does not. So far what I've found suggests that "binary safe" is something that really relates to functions and handling of data, not the data ...
1
vote
2answers
158 views

Functioning of Oracle's Server result Cache

I am reading the Oracle concepts doc and have a doubt [about general cache mechanism ] : PL/SQL function code can include a request to cache its results. Upon invocation of this function, the ...
1
vote
2answers
262 views

MySQL for Administrators course from Oracle: worth it?

This is a question probably experienced MySQL admins can answer, but is not really a technology question. I am looking into MySQL Administraton course from Oracle/MySQL AB. It seems interesting as ...
10
votes
2answers
14k views

What is the reason of Host 'host_name' is blocked in MySQL?

When I use incorrect password to connect to mysql server, and the test count is over the max_connect_errors, and I find the Aborted_connects is also arise as the count I test, but the host is still ...
5
votes
3answers
1k views

Indexing - Uniqueidentifier Foreign Key or Intermediary mapping table?

Looking for some expert views on this guys - I'm not a DBA by trade so would appreciate any advice. Comments on the rest of the schema aren't required as it is purely fabricated to illustrate my ...
10
votes
3answers
4k views

Is there a way to access temporary tables of other sessions in postgres?

I'm working with a Windows application that uses a (local) postgres Database and stores some information in a temporary table. I'd like to have a look at the temporary table, but pgadmin and dbVis ...
13
votes
5answers
3k views

Is ROLLBACK a fast operation?

Is it true that RDBMS systems are optimized for COMMIT operations? How much slower/faster are ROLLBACK operations and why?
11
votes
3answers
13k views

MySQL table_cache and Opened_tables

I have seen people use the comparison of Open_tables and Opened_tables to assess whether the table_cache is too small in MySQL. However, I believe that Opened_tables is cumulative across uptime, so ...
48
votes
7answers
10k views

Why shouldn't we allow NULLs?

I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL. I don't remember why this was the case though. All I can seem to ...
1
vote
1answer
5k views

Cleaning Oracle In-Active Sessions

There a are huge number of in-active sessions persisting in our database.How can we clean these in-active sessions? Database - Oracle 10g R2 with ASM DB Size - 5TB SGA - 78GB PGA - 10GB Maximum ...
2
votes
2answers
586 views

Get timing on individual queries in Anonymous code block

I have a set of queries that I need to run in succession, carrying the result from the previous query into the next. My only need is the execution time on the last query in the list, but SET TIMING ON ...
15
votes
2answers
17k views

Changing column width

Will increasing a column (nvarchar) width necessarily drop the table? In other words, can the width be changed in a production environment with active users? I figured that if the size is increasing ...
1
vote
1answer
201 views

MySQL duplicate data entry issue

I know this issue can be solved using unique key constraint on column and some insert command variation. But I exactly want to know that at last why the following approach goes wrong sometimes and a ...
6
votes
3answers
299 views

Things to consider with a bigger table

I have a table that has around 12 billion rows (row size 32 B) which grows each month by 300 million rows. I have setup partitions for each month and each month has 8 data files (since I currently ...
3
votes
3answers
4k views

What are the mandatory/required oracle 11g tablespaces?

Ok, what are the mandatory tablespaces in oracle 11g. My research has lead me to believe there are three of them: System, Sysaux, and Undo. However since I am taking the OCA and all the ...
9
votes
2answers
3k views

Updating 700 million rows to same value

I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows. I don't have admin access, or access to an admin, so this needs to be accomplished with ...
0
votes
3answers
2k views

Figuring out database timezone

Does anybody know of a way to alter a time zone on a SQL database? Is there any tool that could find the objects using the date and time?
3
votes
3answers
810 views

How and what do you backup for a simple Oracle DB?

Background: I am in the following situation: We have a JIRA instance running on a dedicated server machine (Windows Server). It uses Oracle XE to store all the data. The JIRA instance and a Oracle ...
5
votes
2answers
7k views

can I resolve a deadlock with the rowlock hint?

I have a large delete stored proc and I've reproduced a deadlock in a situation where the deletes would not have deleted anything. It looks like the part of the stored proc that hit a deadlock was ...
3
votes
3answers
269 views

How do I load just the data from SQL Server 2005 backup into a 2008 R2 database?

I created a 2008 native database and I have the structures in place but need to load data from a 2005 backup.
10
votes
1answer
645 views

Strange behaviour with Computed Columns in SQL-Server

Whilst reading through my 70-433 exam book, I thought of something that I can see not working, yet I believe it does. The passage read something like: The column must also be marked as PERSISTED, ...
1
vote
1answer
531 views

Applying MySQL 5.1 bin log to MySQL 5.5

Is it possible to apply MySQL 5.1 bin log to MySQL 5.5. I am assuming that bin log is portable between two machines (same OS, Architechture but different machines running same version of MySQL)
9
votes
2answers
1k views

What is the purpose of SET NULL in Delete/Update Foreign Keys constraints?

I am probably being narrow minded, but if I create a foreign key constraint and a row gets updated or deleted, I lose that connection if the child table's column gets set to NULL. What is the purpose ...
1
vote
1answer
134 views

Update Table with Network Client Connection Status, I need help with DB design. SQL 2008 Ex R2

I have C# client application that updates a Server Database, SQL 2008 Express R2. The client application send a ping to a group of devices once every 30 seconds and displays their status then updates ...

15 30 50 per page