All Questions

Filter by
Sorted by
Tagged with
5
votes
1answer
12k views

What I can do to speed up Key Lookup in my Query

I am using SQL2005 and against them I calling query simila to this declare @art table (id int primary key, naziv varchar(35) null, sifra varchar(15) null, jm int null); insert ...
4
votes
3answers
11k views

Want to order on two columns but not the usual way

I have a table as below ID userID Date 1 2273 22/08/2011 2 2274 24/08/2011 3 2275 26/08/2011 4 2273 26/08/2011 5 2273 26/08/2011 6 2271 26/08/2011 And want result as ...
2
votes
3answers
458 views

VLDB - SQL SERVER versus VFoxPro

I must create an DBF for an application which will collect data from 5 millions sensors on every minute, so I will have 5 millions records every minute. I was thinking to use SQL server because I ...
2
votes
2answers
973 views

Full Name search query

We currently have a solution for this in production, but it's pretty bad. I'd like a fresh perspective on it please. We have a PERSON table with an ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, and ...
1
vote
2answers
2k views

Replication error on SQL Server 2008

I'm doing merge replication between two SQL Server 2008 machine. But, when I attempt to do any modification on subscriber system, I get the following error. The merge process could not enumerate ...
7
votes
4answers
424 views

What are the performance implications of running multiple smaller DBs instead of a single larger DB on a server?

Our database architecture allows multiple 'customers' to exist in the same database, yet we split them into mulitple database for administrative reasons [patching, backup, etc.] Question 1 What ...
1
vote
1answer
1k views

Running query against SQL Server with FreeTDS's bsqldb returns blank output

Here is the query I ran select CAST(PageGuid as varchar(max)) + ',' + CAST(TenantGuid as varchar(max)) + '/' + CAST(OriginalFileGuid as varchar(max)) from Pages This worked with tsql but when run ...
5
votes
1answer
2k views

Foreign Key constraint on fixed value field

Note: I am a developer... I have an Asset table that has many codes that are foreign keys into a second table OutlineFiles. The OutlineFiles table has Type and Code as the primary key. The foreign ...
6
votes
3answers
26k views

Oracle 10g: Properly using ora_rowscn to detect table row changes (ie, inserts, updates, deletes)

Some research into checking when a table's records were last updated, modified, or deleted has lead me to the pseudo column known as ora_rowscn. First, I do this: select max(ora_rowscn) from ...
0
votes
2answers
1k views

FBI on function with concatenated string

I have an index in place (from days of old) that I'm curious about. It's on a basic person table and the function is upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME") When trying ...
2
votes
1answer
510 views

Oracle Database Block Size

One of our Production database has a rather peculiar block size of 16KB. This database was created about 2 years ago and at that time the size of the db was approx 1TB only.However over the past 2 ...
2
votes
1answer
316 views

Shortest Query to Combine Count and Unique (Oracle)

I have the following query which works for me: select count(*) from ( select UNIQUE col1, col2, col3 from my_table where cond1 = 'A' and cond2 = 'B' and cond3 = 'C' ) Is it possible ...
5
votes
5answers
3k views

MySQL slow query log: huge query time, no lock time, minuscule number of rows scanned

I have a slow query log item with the following: Query_time: 55 Lock_time: 0 Rows_sent: 739 Rows_examined: 739 No one in my group can explain it besides a guess about packet loss. Is there a ...
3
votes
1answer
1k views

Trusted Constraints and NOT FOR REPLICATION

Version and Build: SQL Server 2005 SP4 (9.0.5000) As a disclaimer of sorts, I am asking this question to generate some discussion and/or debate on the topic. I don't know if there is really a ...
4
votes
1answer
83 views

Can you control what tags are used in an XML column?

We allow our developers to use XML columns to store data. We want to provide standard names to XML tags. As a DBA, can we prevent the users from adding XML tags that are not predefined?
5
votes
1answer
1k views

T-sql: Select most specific match from table

Apologies for the horrible title. I couldn't come up with a better way to briefly describe the problem. Here's the scenario: Users can set up service instructions to be attached to a work order ...
4
votes
2answers
1k views

Oracle 10g Storage Migration

We need to migrate the storage of our production database. What is the appropriate method of doing so and what specific/generic precautions do we need to take?? Database Configuration: Volume Manager ...
3
votes
2answers
3k views

Oracle 10g SGA sizing Help

Help in sizing oracle 10g database sga We've an Oracle 10g r2 database, size = 4.5TBASM is used to manage the storage. It uses "External Redundancy" option on "Raw Device" (i.e. no os file system ...
17
votes
2answers
27k views

SQL Server Join/where processing order

After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
2
votes
1answer
743 views

Can you trigger an action when a view is created?

Is it possible to create a trigger that fires when a view is created on a user schema? I want to create a meta data table which will hold all the views and a listing containing what they are supposed ...
20
votes
1answer
23k views

mysqldump with INSERT … ON DUPLICATE

I want to merge data from one database to another. So I create dump with mysqldump and then import it to another database (with same tables structure). I don't have any problems (such as duplicate ...
2
votes
1answer
1k views

Issue with SQL Server 2008 replication

I'm doing merge replication between two SQL Server 2008 machine.But,when I attempt to do any modification on system,I got the following error. The Merge Agent failed because the schema of the article ...
4
votes
1answer
649 views

Locking, deadlocking workaround while process data at the same table

My database has a table: tableX. Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate. At the ...
6
votes
3answers
479 views

When you select from a table on an indexed column and order by that column in the opposite direction of the index, does the index help

Let's say I have a large table of transactions with a date field and an index on that field in ascending order. If I select that field from the table and order by in reverse order is it able to ...
4
votes
1answer
12k views

How do you tell which host is close to being blocked in MySQL?

I have a two part question: In cases were this error comes up, is there a way to see if a host is close to being blocked? And fire off an alert? PDOException: SQLSTATE[HY000] [1129] Host `hostname` ...
13
votes
1answer
5k views

Optimising BCP performance for BLOB data

I'm the process of planning the live migration of a 2TB database to partitioned tables. The system is broadly speaking a document store, the majority of the space being allocated to LOBs of between ...
9
votes
2answers
9k views

SQL Server: how to avoid sysadmin rights but give adequate rights

I have a collegue who want to get an access to one SQL Server 2008 instance. I'll have to give him rights to this instance. He should have rights to do is e.g. Add and modify server logins Add and ...
6
votes
1answer
3k views

MySQL 'LOAD DATA INFILE' : Can't replace entry when foreign key exists

Here's a minimal case for my database scheme: CREATE TABLE `test` ( `id` int(11) NOT NULL, `name` varchar(22) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT ...
70
votes
6answers
67k views

What risks are there if we enable read committed snapshot in sql-server?

I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there? eg. Will this affect recovery of the database? Is there anything ...
5
votes
1answer
2k views

View SQL 2008 sp_cursorexecute Underlying Query and Execution Plan

I'm performance tuning a Dynamics AX application and see in a SQL trace a long-running, high-I/O query of the form exec sp_cursorexecute 1073742882 ... When I try and run that query in a new SQL ...
4
votes
4answers
739 views

Cannot reproduce client SQL 2008R2 error: subqueries are not allowed

EDIT: My question is not "how do I work around the failure?", my question is "why is the failure occurring?" We have a client that reports error "1046 subqueries are not allowed in this context. Only ...
4
votes
3answers
156 views

Is there any way to temporarily enforce columnar uniqueness on items currently being inserted?

Is there any way to temporarily enforce columnar uniqueness on items currently being inserted? For instance, I'm splitting payments and charges to their own tables from a previously unified "...
6
votes
1answer
567 views

MySQL Replace — Query or in Shell?

I have numerous tables with some funky character replacement going on. I found some information on running an update to replace these, but it's in numerous tables and numerous fields. update <...
3
votes
1answer
49k views

How to display a number with fixed number of digits before the decimal?

I am looking for result 0123.56 and the following query should have worked. SQL> select to_number(123.56,'0999.99') from dual; select to_number(123.56,'0999.99') from dual * ERROR ...
13
votes
1answer
4k views

Amount of data per node in Neo4j

I need to store substantial amounts of data per node in Neo4j. The data is Unicode chunks of text. Actually not every node will have big chunks, but many of them will. I waded through the ...
3
votes
4answers
61k views

SQL Server How to Set up Maintenance plan to shrink log

So I have a log file which grows very large after I run an SSIS job daily, sometimes up to 1220MB. In my database options I have set my recovery model to "FULL". When it is to FULL and I perfrom ...
4
votes
1answer
2k views

oracle failsafe - how to start instance in nomount

Using Oracle Failsafe, how can a instance be started up in nomount mode? If the instance is shutdown via sqlplus, the windows cluster manager just starts it up again. If the instance is shutdown via ...
4
votes
1answer
393 views

Is SQL Server Replication transactionally 'safe' across all published tables?

We have a system that has one of its datasources still on SQL Server 2000. In order to achieve higher availability, we are looking to setup transactional replication from this 2000 server to a 2008 ...
4
votes
4answers
16k views

Clear all tables with one DELETE

Is it possible to clear all tables with one request? I have tried the following with no result. DELETE a,b,c,d,e,f,g,h,i,j,k,l,m,n,o FROM jos_bet_details as a, jos_bet_1x2 as b, jos_bet_1x2_best as c,...
7
votes
1answer
2k views

Application Settings in Database

In the past I have stored all generic settings and configuration items such as: custom application title, home address, version, debug, etc.. all in a "settings.php" file that I would include into all ...
8
votes
4answers
240 views

SQL Server: what is the reason to favor customized database roles over fixed ones?

This may be a bit vague, but I was pondering how much DBA's use fixed database roles controlling security of databases. So the question is, is there a reason to favor customized database roles over ...
5
votes
1answer
2k views

SQL Server 2008 R2 reindex job reorganise getting stuck

I am currently experiencing this job getting stuck in my nightly maintenance solution - ALTER INDEX [PK_ThirdPartySettleCalled] ON [evo_archive].[dbo].[ThirdPartySettleCalled] REORGANIZE WITH (...
91
votes
5answers
55k views

When to use TINYINT over INT?

In general, I always use Ints. I know that in theory this is not the best practice, though, since you should use the smallest data type that will be guaranteed to store the data. For example, it's ...
4
votes
3answers
7k views

SQL Server: does ddladmin rights contain datareader and datawriter rights to the database?

Does db_ddladmin fixed database role include db_datareader and db_datawriter rights, so that you don't have to grant those rights explicitly?
4
votes
2answers
3k views

How do I force SQLite to materialize a subquery?

Because SQLite sometimes evaluates functions like random() more than once if they appear in a subquery, which may be a bug, eg: select op, op from (select abs(random())%10 as op from (select 1)); 3|1 ...
5
votes
3answers
2k views

How do I row count into groups?

I have records with a bit field that are in an order how do I group them in sets of 3. For example if my first three rows have bit fields 0,1,0 I want them in set 0, the next 3 are 1,0,0 I want them ...
7
votes
1answer
765 views

Terabyte data set

I have the feeling that most of the database systems originate in the '80s and stereotypically implement tables, ACID transactions and constraints. They were build having in mind the scarcity of ...
6
votes
1answer
18k views

oracle format specifiers: to_number vs to_char

SQL> select TO_NUMBER(123.56,'999.9') from dual; select TO_NUMBER(123.56,'999.9') from dual * ERROR at line 1: ORA-01722: invalid number SQL> select TO_CHAR(123.56,'999.9') ...
11
votes
3answers
7k views

How to delay SQL Server startup

When we boot SQL Server, SAN drives might not be availiable yet. Is there a way to delay SQL Server start by 1 minute? Right now we have to restart SQL Server after we boot the server.
2
votes
1answer
2k views

SQL Server Service Broker and Deadlocks

I am reading through SQL server books online for the first team whilst learning how to use the service broker and I came across this statement: The application that implements the service reads the ...

15 30 50 per page