An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

3
votes
1answer
2k views

Atypical problem with ORA-01445

I have problem with following query Q1: SELECT ADDRESS.STREET FROM MYSALES.MYADDRESS ADDRESS INNER JOIN MYSALES.MYCOUNTRY MCOUNTRY ON MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID INNER JOIN ...
1
vote
2answers
731 views

COUNT with set DIFFERENCE and UNION

Fairly simple question that I can't seem to find an answer to - I'm working with unions and differences, and I would like to perform a COUNT on the results. Currently I'm having to pipe out to a file ...
1
vote
1answer
77 views

Verify if I can use a given directory

Admin creates an Oracle directory: CREATE OR REPLACE DIRECTORY EXAMPLE AS '\\server\share\test'; GRANT READ, WRITE ON DIRECTORY EXAMPLE TO JOHN; Admin can test whether the directory exists: SELECT ...
3
votes
0answers
282 views

How unsafe is it to change the data source of repl_distributor on a publisher

I have a long story that I will condense to a few bullet points that leads up to my question Reporting Server (SQL 2008) is distributor/subscriber cluster for a SQL 2000 Transactional Publication ...
3
votes
1answer
2k views

Why does altering a table and adding a foreign key create an extra constaint?

Edit: It was a default constraint as a result of setting a default value when adding the column. The name gave it away when starting with 'DF__'. Anyway, solved it by adding the column and manually ...
2
votes
1answer
5k views

sql server management studio backup error

hello, i want a back up for my database in sql server management studio. i am using ms sql. but when i try to back up , i take the following error: =================================== Backup failed ...
3
votes
2answers
2k views

SQL Server - In Recovery

Was uploading a few hundred thousand rows in my SQL 2008 R1 database and have come in this morning to an "In Recovery" message next to the database name in SQL Management Studio. I've tried a DBCC ...
2
votes
1answer
918 views

Extract some columns data from one table and insert into multiple tables

I have a table with 16,000 records and need to get out some columns and then insert values in multiples tables. For example, suppose I have the following tables tbl_1: id, name, lastname tbl_2: id, ...
2
votes
3answers
515 views

Refactoring Series of UNIONs in single table (SQL/SYBASE)

I am trying to query a database and reformat the rows/columns that I am interested in. Each row in the current table (EQ) represents a field in a form. In reality, there are several hundred FieldNum ...
2
votes
2answers
2k views

Deleting Entries from Table not reducing my DB file

Iam usind INNODB Engine with innodb_table_per_file , so every table has it's own .ibd file instead of ibdata1 file. When i Entered 512 Record in my users table its size get increased to 200kb from 96 ...
1
vote
1answer
132 views

Best solution for search scenario

I have the database structure: | Tag | | Article_Tag | | Article | --------------- --------------- ----------------- id tag_id ...
1
vote
0answers
37 views

How does MySQL 5.1 use indexes?

I've read the docs for MySQL v5.1.x on how about the indexes are used, but I'm not sure I understood it properly. Suppose having the following MySQL table: CREATE TABLE IF NOT EXISTS ...
2
votes
1answer
557 views

Cube Designer Aggregation Wizard Optimization Zero Percent Every Time

I am pretty new to SSAS. I have a cube (SSAS 2008 R2) that once had aggregations that has been created using the Aggregation Design wizard. When changing schema a few dimensions, i encountered an ...
4
votes
2answers
2k views

Dynamic access to record column in plpgsql function

How can I address a column from a record in a plpgsql function dynamically? In the following snippet I have access to a variable entity.colname that contains the column that should be checked in the ...
1
vote
2answers
350 views

How to DELETE duplicate entries on a field

I have a huge table that has 1,990,546 users with numerous data. Sometimes I get email lists and dump them into this table also. I want to delete users with duplicate email addresses. However, I ...
1
vote
1answer
147 views

Help me convert SQL statement to MySQL

I have an application called Moodle version 1.9 and was migrated from Windows platform and Microsoft SQL Server to Linux and MySQL. Now, when I click save changes the SQL query statement from Custom ...
24
votes
5answers
6k views

Why does ORDER BY not belong in a View?

I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with) I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the ...
1
vote
1answer
613 views

2 similar queries in large sql server table - 1 takes too long

I have a huge table in my database that contains distances between cities. This enables my application to find nearby cities around the world when a starting city is selected. It contains 4 columns ...
4
votes
1answer
118 views

repeatable read and nolock

Does anyone know what the net effect of having a block of t-sql with a transaction isolation level of repeatble read and then inside having a select with nolock. Does the nolock become invalid ...
9
votes
3answers
2k views

Used Memory not freed up after a SQL BULK Insert / BCP Export

I have created a very basic SQL table as following CREATE TABLE [dbo].[TickData]( [Date] [varchar](12) NULL, [Time] [varchar](12) NOT NULL, [Symbol] [varchar](12) NOT NULL, [Side] [varchar](2) ...
2
votes
1answer
313 views

ETA: translate Oracle SQL queries to MySQL

task: replace Oracle 11g with MySQL current setup: Oracle 11g SE One about 80 tables stored procedures, PL/SQL are in place but out of scope for this question client applications do not use a ...
4
votes
2answers
897 views

Log Reader replication agents are constantly restarting

I am recreating (push) transactional replication from a SQL 2000 instance to a SQL 2008 instance which I had to break in order to do a db migration. The 2008 instance is both the distributor and a ...
4
votes
2answers
193 views

Databases entering “In recovery” mode on restore

I am facing an issue while restoring the database. I have many dump files in a folder and I try to restore them one by one to a database xyz, perform some update operation and continue my restore ...
1
vote
1answer
743 views

ON DELETE cascade alternate

I am trying to create a database for 2 Tables like: Table1 : tb1-id(pk) | tb2-id | other Table2 : tb2-id(pk) | other FOREIGN KEY Table2(tb2-id) REFERENCES Table1(tb1-id) ON DELETE ...
3
votes
1answer
369 views

Connecting Linked Server

Hi I want to fetch data from MS SQL DB of a Remote server using Linked Servers. I have linked the servers. I am fetching data as follows. select * from [IP of server].[Database Name].[dbo].[Table ...
4
votes
2answers
1k views

SQL Server Memory Paging Issue

We have a dedicated SQL server running Windows Server 2003 R2 Standard x64 SP2 and SQL Server 2005 (64-bit) Standard Edition with 16GB RAM. Every night we're experiencing severe performance issues ...
4
votes
4answers
12k views

T SQL Table Valued Function to Split a Column on commas

I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value. Ex: "one,two,three,four" would return a new table ...
10
votes
1answer
10k views

Stored Procedure to return dynamically created table data

Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
4
votes
1answer
636 views

MongoDB optimization on high data

I have a mongoDB collection with 42M registers with 1M new registers every day. We found a low performance on mongoDB querying. showing in mongostat the fields: qr|qw ar|aw with values of 300-500. ...
3
votes
1answer
2k views

Management Studio all buttons disabled

I get an odd problem in my SQL R2. Although I can attach a new db to my db instance, but all buttons on the tool menu panel are disabled. So I cannot execute any sql query statements. It is really ...
0
votes
1answer
191 views

AlwaysOn SQL Server setup questions

I just have a few questions regarding the setup of Always On HA, and Clusters. When you setup an AG, and it gets to setting up your IP address for the cluster how can i switch from using DHCP to a ...
0
votes
0answers
42 views

Resource to learn writing most efficient and highly optimized sql queries [duplicate]

Possible Duplicate: Learning to Optimize SQL Queries and Understand Execution Plans - Resources? Can some one point me to a great book/great books or some really solid reference/references ...
3
votes
2answers
619 views

Optimizing real-time Database table in Microsoft SQL server 2008

We are using a web interface to monitor our jobs state and its started and completed timestamps. We have >200,000 job instances running on each day for different job run-users. We maintain the state ...
2
votes
2answers
1k views

difference between having many non clustered index with single columns and with combination of many columns

Folks I am a newbie to DB design stuffs. I am wondering what would be the difference and performance difference between having designed many non clustered index with single columns and non ...
3
votes
3answers
2k views

Cannot write to mysql database while select query in process

Running Mysql 5.1.57 on InnoDB, and have a database locking issue. I have two separate sessions connected to a single database MySQL. On the first session, I run a long SELECT query (technically a ...
1
vote
1answer
418 views

Help with getters & setters in PL/SQL

I am not a db guy, and I have a very beginner's knowledge bout PL/SQL. That being clear now the question: I have 2 tables one file level & another batch level. In a day, only 1 file_id is ...
4
votes
2answers
138 views

why SELECT is still using CPU & DiskIO?

I have a large database, about 30GB. In order to delete some data from a table (most data is in this table, simply because the # of rows), I have to do delete in batch. However, if I delete in too ...
2
votes
1answer
769 views

MySQL: AVG of defined variable per month

I need help with the following code: SELECT DISTINCT userid , count( userid ) as login_count FROM ( SELECT DISTINCT userid , ...
4
votes
2answers
468 views

Does a cluster index provide more benefits than pre-sorting the load file and creating non-cluster index?

Informix 11.70.TC4DE: CREATE TABLE cluster_tbl ( fk_id INT, data CHAR(2048) ); LOAD FROM "presorted.ld" INSERT INTO cluster_tbl; CREATE UNIQUE CLUSTER INDEX cl_idx ON cluster_tbl(fk_id); ...
2
votes
1answer
240 views

Best approach to design a database model for this situation? Clients and records

We need to store the following information for each client: Three types of record per file, for example (obviously, with real data): HDR2007022399999900000000000Soft/XX 0.0 XXX ...
3
votes
1answer
221 views

Get profiles values where message.sender_user_id = profile.user_id

I have this tables: default_messages CREATE TABLE IF NOT EXISTS `default_messages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL, `message` text ...
1
vote
1answer
769 views

Oracle 10g: query takes long time and adding index does not help [closed]

I have a query which takes indefinite time, taking almost 60% of CPU time for 3 hours then simply hangs the PLSQL process. What I do is: Get "explain plan" for the query I noticed a "table scan" in ...
3
votes
1answer
4k views

The server principal “<mydbuser>” is not able to access the database “<mydatabase>” under the current security context

When I try to install a new database, I'm getting this error from SQL Server 2008. The server principal "<mydbuser>" is not able to access the database "<mydatabase>" under the current ...
3
votes
1answer
480 views

Why am I getting Table scan on a partitioned table query?

I have table Inventory_Break partitioned on IR_ID (int). I also created a non-clustered index for IR_ID on the table. When I run the following: SELECT * FROM dbo.INVENTORY_BREAK WHERE IR_ID ...
4
votes
1answer
21k views

CASE statement with IS NULL and NOT NULL

Is there any better way to write the lines below in SQL Server 2005? CASE WHEN (ID IS NULL) THEN 'YES' WHEN (ID IS NOT NULL) THEN 'NO' END AS ID_Value,
2
votes
2answers
1k views

sql bulk delete is much slower?

I have a DB that is about 3.5GB. 2 tables use most spaces. each table has about 2.8M rows. now I need to delete about 1.9M rows from each table. using the batch delete DELETE TOP (n) syntax, then ...
0
votes
1answer
3k views

How to give SQL Server Job Owner permission to Proxy a Credential

I've created a Credential Cred1 (DOMAIN\USER1 with the secret = domain password) I've created a SQL Agent job with owner to NT AUTHORITY\SYSTEM The job needs to proxy as credential Cred1 So when I ...
0
votes
3answers
5k views

Database is not accessible after 'taking it offline' process failed

I was trying to detach the DB when it gave me an error that it is currently in use. Hence, I tried to take my DB offline but it failed saying 'an exception occured while executing a transact SQL ...
6
votes
2answers
8k views

Learning to Optimize SQL Queries and Understand Execution Plans - Resources?

I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team. They ...
2
votes
2answers
204 views

MySQL : UNION script help

I have two tables which have roughly the same data but we could not mix them together due to the nature of the data input and presentation and to keep the table size small. We run the followign ...