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)

8
votes
2answers
2k views

Does MySQL cache queries?

I'm interfacing a MySQL database with PHP Data Objects (PDO) and executing an extensive SQL query. Normally, it takes about 1500 ms; I still need to optimize it. When I run the PHP script twice ...
0
votes
2answers
77 views

Query keeps on executing

I'm executing below query, but it keeps on executing. In place of some time I have a time value. select distinct(col1) from table1 where col2 > 'some time' and col1 not in (select ...
0
votes
3answers
105 views

How to “analyze” a SQL query?

I'm not sure if I used the right term in my question. I am creating a prototype, which allows users to type in a raw SQL query, which will query the backend DB. I want to also make sure that users ...
5
votes
3answers
246 views

Backup not creating a new file

I run a daily backup of my database through command prompt: c:\sqlcmd -H localhost -Q "BACKUP DATABASE test TO DISK='c:\test.bak'" But the new backup replaces the previous backup. Please tell me ...
1
vote
1answer
949 views

Need to replace only first occurrence of string

I hope I worded the question right. Here are the specifics: This is regarding a MySQL database. I've inherited several hundred posts with custom fields and a unique excerpt. At this point I think I ...
0
votes
2answers
414 views

SQL Server 2012 linked server to SQL Server 2000

I am trying to select through an linked server from SQL Server 2012 to SQL Server 2000. The linked server has been setup and I can browser the target database through the linked server which ...
1
vote
1answer
2k views

In creating view use SQL Security definer or invoker?

CREATE VIEW syntax is CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name ...
-1
votes
0answers
50 views

Optimize SQL Select Command [closed]

Is there anyway to speed this up by optimizing/condensing the code? SELECT fullName, email_address, pos_desc, EXT FROM dbo.BTC_WEB_EMPLOYEE_VIEW WHERE grp_cde = 'STAFF' OR grp_cde = 'PROF' OR ...
4
votes
2answers
607 views

What is the best way to migrate multiple databases?

I want to migrate my database server to a new server. Right now I have a database server with Windows Server 2008 and now I am migrating to a new, separate server with Windows Server 2012. There are ...
3
votes
3answers
541 views

MYSQL wont use index in “IN(SELECT ..)” sub clause

I have two tables in MYSQL with target fields set as index / primary index already. Then I ran the below sql query. EXPLAIN SELECT charge FROM ec_provider_account_option WHERE ...
5
votes
4answers
4k views

Can I add a unique constraint that ignores existing violations?

I have a table which currently has duplicate values in a column. I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added. Can I create a ...
1
vote
2answers
91 views

interpreting how much data has been lost by analyzing dbcc output

We had a SAN outage and this corrupted several databases with no backups. We ran this to do a repair: EXEC sp_resetstatus 'test' ALTER DATABASE test SET EMERGENCY DBCC CheckDB ('test') ALTER ...
2
votes
1answer
341 views

Create a sub query for sum data as a new column in SQL Server

Suppose that I have a table named tblTemp which has this data: | ID | AMOUNT | ---------------- | 1 | 10 | | 1-1 | 20 | | 1-2 | 30 | | 1-3 | 40 | | 2 | 50 | | 3 | 60 | ...
0
votes
4answers
124 views

SQL Server transaction log growing without control

We have a database growing uncontrolled and filling the hard drive every 2 days. Database maximum log size is set to 6 gb but id doesn't seem to be working because it continues growing beyond 6 gb. ...
3
votes
1answer
445 views

Index on foreign key makes query extremely slow

We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem. The queried table (table3) has an indexed PK, three FK with indexes ...
0
votes
1answer
69 views

Need an idea for a complex SQL question

I have had my cup of coffee, but cannot come up with a good solution for the following problem - so I am here to pick your brain. ;-) Here is what I am trying to accomplish (for PostgreSQL): Got a ...
1
vote
1answer
71 views

Sql Server Server Group query

Scenario I have 4 front-end sql server. All of them have the same db instances. Data are replicated from back-end databases. Issue In SQL Management Studio I have registered the servers and I have ...
1
vote
0answers
45 views

What is a good way to Merge 2 range sets into a single view

The real purpose of this has to do with effectivity dates and contains additional keys and fields, but I simplified it for the sake of, well, simplicity. Sample: test_Widget Table theFirst ...
0
votes
0answers
117 views

Passing parameters through Excel to an Informix DB works through MS Query but fails when returned to Excel due to General Error

I require to query an informix database using parameters and Excel. When the query is executed in Microsoft Query the dataset is fetched and returned succesfully, when I attempt to return to Excel the ...
0
votes
1answer
88 views

Embedded select with rank

I am having the hardest time following the tutorials on this site regarding ranking with MySQL. I have a larger problem than others seem to show. My table is constantly updating with single answers ...
0
votes
1answer
467 views

HOW to “SUM” and “JOIN” for single query of a poll results?

I have a poll database as CREATE TABLE poll_answer answer_id, answer varchar(255), poll_id int(11), FOREIGN KEY(poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE, PRIMARY KEY(answer_id) ) ...
3
votes
2answers
3k views

Reduce Clustered Index seek cost SQL Server 2008 R2

I am running a query and it have records in lakhs, it takes more than 20 mins to fetch data. After running the execution plan i noticed that clustered index seek cost can be be the reason. How can I ...
0
votes
1answer
607 views

How would I store the result of a select statement so that I can reuse the results to join to different tables?

How would I store the result of a select statement so that I can reuse the results to join to different tables? This will also be inside a cursor. Below is some pseudo code, in this example I have ...
1
vote
1answer
422 views

Autogrow of database1_log file in database database1 took 1010871 milliseconds

in past i had a problem of that query been executed for 7 days. i couldnt find any solution. so i created again new database with indexes. Now i am running a simple query: UPDATE query query has ...
0
votes
1answer
792 views

Different dates Oracle 11g with TOAD

I have the following queries: SELECT to_date(to_char(to_date('01-FEB-1949'))) FROM DUAL; /*this returns 2/1/2049. */ SELECT to_date(to_char(to_date('01-FEB-1949'),'dd-MON-yyyy')) FROM DUAL; ...
4
votes
1answer
39 views

Verify Query Request Received

I have a software system which queries data via OLEDB from an external SQL Server 2005. The query request is sent every 15 minutes. I want to verify that the SQL Server is receiving the query ...
0
votes
2answers
1k views

Is there any way to force MySQL use Hash Join instead of Nested Loop Join?

According to the document MySQL Explain Output format, MySQL resolves all joins using a nested-loop join method. Is there any way to force MySQL to use a hash join?
0
votes
1answer
719 views

Changing the SGA and/or PGA in Oracle 11g affects the performance of the queries?

I am making a practice for school where one of the questions is if changing the SGA/PGA size would affect the queries performance and in what sense. I have been looking around for how to do that and ...
7
votes
1answer
951 views

Algorithm for finding the longest prefix

I have two tables. First one is a table with prefixes code name price 343 ek1 10 3435 nt 4 3432 ek2 2 Second is call records with phone numbers number time 834353212 10 ...
0
votes
2answers
237 views

Is there any manual for the visual explain in mysql? The output plan is unreadable

Just like this example, I cannot even tell the join ordering from it. And what does "ALL" and "ref" in the table mean? Is there any manual about the visual explain?
0
votes
0answers
676 views

Using something better than UNION for combining multiple queries

I have one big query which is based on 12 smaller queries that I have put together using UNION. A relative simple example of my query: (SELECT 'type1' AS 'type', fieldOne AS someField, ...
5
votes
1answer
165 views

Execute output from query

I've got this query that builds my drop login statements. As part two I want to execute this output. Ideally I want to do this in a sql job with two steps (generate and execute). Can this be done? ...
3
votes
3answers
7k views

Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?

Does the SQL Server CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN that evaluates to true? If it does go thru the entire set of conditions does that mean that the ...
0
votes
1answer
43 views

Bit column vs multiple join tables

I asked a question earlier about how I could define a many to many relationship between users and FOSes (fields of study). My original question was how could we know whether the user was the ...
0
votes
2answers
128 views

Aggregate query on multiple values of same field

I am using SQL-Server 2012, I have a table of credit card transactions as follows: CardScheme RespCode ========================= Visa Declined MasterCard Approved MasterCard Approved ...
6
votes
2answers
1k views

Turning OFF CHECK_POLICY by default

We moved from SQL Server 2000 to SQL Server 2005. The client software, which I can not change, creates a user without option CHECK_POLICY = OFF; After creating a user, I have to run the command ...
-2
votes
1answer
94 views

Handle vast amount of data in DB [closed]

Now my current database is working in SQL Server 2008 R2 and we are uploaded its only in 1 server. How can I migrate my existing database in to Hyper Table ? (My current database has huge amounts of ...
-2
votes
3answers
117 views

Load Balancing in SQL

We are implementing new database applications with large users and the developer thinks we need to look into doing some kind of load balanced SQL database.our server is slow due to vast amount of ...
14
votes
2answers
3k views

Is it a bad practice to always create a transaction?

Is it a bad practice to always create a transaction? For example, it is a good practice to create a transaction for nothing but one simple SELECT? What is the cost of creating a transaction when it ...
0
votes
1answer
628 views

Restoring database backup file gives access error

I am running SQL server 2008 and trying to restore from a backup file. Note: the backup file and folder and read/write access to "everyone". Running the command as "master" Running RESTORE ...
0
votes
2answers
120 views

How do you store tabular data in an RDBMS?

First: I really haven't got any idea how to google for this. If you got one, leave a comment. If I want to store arbitrarily large tables in a database, how should I set up my database tables? ...
2
votes
1answer
88 views

How to select nodes where all children is satisfied?

I have a tree structure of light bulbs. I want to turn on all the light bulbs starting from the leafs of the tree. A light bulb cannot be turned on unless all its immediate children are turned on. ...
4
votes
3answers
124 views

Check server activity with code

I would like to see the activity on of the SQL server. So I can run a query when the server load is low. I know there is an activity monitor in SQL server but my company doesn't want me to give the ...
2
votes
4answers
585 views

Shell: How to time a script running in SQLPlus and kill it after x amount of minutes?

I have a little tool which automatically runs a series of SQL scripts when and outputs to .XLS when an appropriate request is made. However, some SQL scripts need to be ran on a live database, and I ...
-1
votes
1answer
69 views

validate where each parent intermediary is also a parent to itself [duplicate]

I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT If table has any. I need to do is first of all get all parents ...
0
votes
1answer
2k views

pgAdmin3 can't connect properly to Postgres 9.2

I have installed pgadmin3 version 1.10.2, however when it connects to PostgreSQL server version 9.2, it says: Warning: This version of pgAdmin has only been tested with PostgreSQL version ...
0
votes
1answer
141 views

Multiple junction tables?

I am working on a system where you have users, and you have Fields of Study or 'fos' for short. Each user can have many fos and each fos can have many users. So we are dealing with a many to many ...
1
vote
1answer
779 views

Mysql query grouping by two columns for max date takes too long

I have table that looks like this: l_p l_a l_timestamp l_n l_act l_name 123 321 2011-1-1T01:00:00Z B q das 21 23 2012-1-1T01:00:00Z C q sd 123 ...
1
vote
1answer
380 views

Remove specific duplicates (all but latest)

When removing duplicate rows, using the below query from this tutorial, how would I go about forcing which of the found duplicates to remove? DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN ...
10
votes
3answers
1k views

Updating a table with millions of records, its been 4 days

I am currently updating a table with millions of records, its been 4 days and query is still executing. I checked the activity monitor its shows that query is running. In event log there is no ...