An acronym for "Structured Query Language", 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 access privileges.
0
votes
1answer
17 views
Hazards of Upgrading SQL Server Express 2008 to R2
A client of mine reached the 4GB database limit on a SQL Server 2008 Express.
As a test I installed the R2 version on another machine and restored a backup of the database.
I'd like to upgrade the ...
0
votes
1answer
48 views
Simplify subqueries with join
I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data.
...
0
votes
2answers
23 views
how important are mysql's innodb logs?
I'm basically concerned about the following two settings, these are my default settings :
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
I haven't changed them for 2 reasons : one, ...
3
votes
2answers
23 views
Given two known statements, how can I replicate deadlock?
I have two statements (an update against every row in one table (call it table A) and a delete on another table that looks up rows in table A) that I know are causing occasional deadlocks. It seems ...
-1
votes
0answers
53 views
2 Outer Joins on Same Table? [closed]
Here is a question which has been boggling me for few days now, and I searched and searched but couldn't find any convincing answer !
Simple question, why is it restricted to have 2 Outer Joins in ...
3
votes
1answer
85 views
Need to divide single column into multiple columns
My table has data with a single column:
1
2
3
4
5
6
7
8
9
10
And I want output like this:
1 2 3 4 5-first row
6 7 8 9 10 - second row
Another has two columns like this:
Column 1 ...
1
vote
1answer
24 views
Column of generic/variable type?
In SQL, I would like to create a table which can contain generic data types. This types can change from every row. The solution I could come up to is:
CREATE TABLE TagValue
(
tag VARCHAR(64),
...
-1
votes
1answer
68 views
How to select a row on one distinct attribute? [closed]
I want to select * from A A but only where A.attr1 is distinct. How can I do this?
0
votes
1answer
21 views
Constraint based rules engine
I am evaluating software systems which configure products that tout constraint based versus rules based engines. Can someone explain (with maybe an example or two) to a non-expert what the difference ...
2
votes
4answers
204 views
High CPU usage on SQL server - Slow queries [closed]
Our MS SQL Server is using about 95% of the CPU-power.
After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
3
votes
0answers
26 views
High CPU usage on SQL server - Slow queries [duplicate]
Our MS SQL Server is using about 95% of the CPU-power.
After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
0
votes
0answers
37 views
Why does selecting top 1 from composite index DESC also used to partition by month not select the top value?
We have very large database table that I need to select the value of the max id. Given the table size SELECT MAX(id) FROM tableA will not perform well enough.
Table definition (with columns omitted ...
0
votes
0answers
25 views
How to test if any attribute values are different in duplicate tables?
I've got some ETL that I'm attempting to test. I want to test the canon data against another set which I run ETL on. My goal is to see if any values in any of the fields change.
The first test is ...
0
votes
1answer
31 views
MySQL replication as both master and slave (replication loop?)
I've got two MySQL servers, one as a master in the replication process and the other as the slave. I was wondering if anything bad would happen if I made the current master a slave to the current ...
0
votes
0answers
8 views
Get turno.tipo, maquina.patente and operador.nombre and status, complex query
I've this DB design I'm trying to build a query where I should get turno.tipo, maquina.patente, operador.nombre and detalle_emisores.revisado` but it's a bit complex to my knowledge. I started this:
...
0
votes
2answers
15 views
Error Code : 1248 Every derived table must have its own alias in MySQL
i have problem here,
when im trying to select with query below, i got error message
Error Code : 1248 Every derived table must have its own alias
SELECT B.BRANCH_CODE, B.BRANCH_NAME, ...
-1
votes
2answers
59 views
Infinite Loop While Running Query
When I run this query from the command line, it continuously loops. I'm not sure why this is occurring. Could anyone explain this to me, and please offer a fix.
I will be using a query similar to ...
2
votes
1answer
74 views
If foreign keys/cascade deletes are bad, why use a database-server with that feature?
I noticed wordpress/rails etc do not use foreign keys constraint or cascade deletes features from database. Instead they handle this in PHP/Ruby/scripting level!
I have read this and this. Most ...
0
votes
2answers
52 views
How to compare an attribute value of one table to all attribute values of another table?
I want to get all values of A.A and compare each to all values of B.A before determining whether or not to display the A record in the result set. I would only display an A record if the value of an ...
0
votes
0answers
38 views
Automated query log
I would like to ask for some opinions or recommendations or guidelines on how to create an automatic logging system for the executed querys on a MySQL database.
Some details:
its a php+mysql ...
0
votes
0answers
21 views
How to/advice for hosting a multi-user database on the cloud with specific objectives
I have developed an application that users will be able to download and create an account with through our company. This application will always be connected to the internet. I have been testing the ...
2
votes
3answers
79 views
Is there a way, in a single SQL statement to ensure that all items in a list are present in some column?
What I'm looking for is a way of verifying that all of a list of items (let's say 'FOO', 'BAR' and 'BAZ') are all in a given table.column. This would be something like SELECT something FROM sometable ...
1
vote
1answer
69 views
Fastest way to insert 30 thousand rows in SQL Server
I have to insert 30000 ids in a temporary table ##MyTempTable that has only one column (ID int) and I would like to know which way I could do it fastest.
I have tried 30000 normal inserts
insert ...
2
votes
1answer
42 views
delete rows in 3 tables with on delete cascade
For a relation 0..n between two tables (t1,t2), the mpd generates an intermediate table (t3) with two fk.
Let two constraints on the t3 table, each one with "on delete cascade", if I do:
delete ...
2
votes
1answer
83 views
How to partition a very large table with limited db space?
I have a very big table. I want to partition it, but I cannot.
Database size: 1TB, Free space 200GB
Table:
Size: 165 columns (row lengh 4216 KB, no LOBs), 500 million rows, 600GB of data.
...
4
votes
2answers
73 views
Difference Between SP with BEGIN/END and without BEGIN/END
I have found some SP(stored procedure) written as,
CREATE PROCEDURE [dbo].[XXX]
(
-- Parameters
)
BEGIN
--- Actual Work
END
and some as
CREATE PROCEDURE [dbo].[XXX]
(
-- Parameters
)
--- ...
-1
votes
1answer
25 views
BULK INSERT in SQL
I have a text file , entries of which i want to insert into a table in my DB.
My query looks like this:
BULK INSERT myDepartment FROM 'C:\myDepartment-c-t.txt'
WITH (
DATAFILETYPE = 'char',
...
1
vote
3answers
49 views
adding ORDER BY decreases speed by 15x
I have a simply query:
SELECT DISTINCT images.*
FROM `images`
WHERE `images`.`user_id` IN (SELECT following_id
FROM `follows`
...
0
votes
0answers
44 views
Optimizing a query with ORDER BY in a derived table
The query below takes too long to execute (58 seconds). If I run two more queries like this with different nid values, the second and third query take much more time to execute. How can I optimize it?
...
0
votes
0answers
36 views
The SQL Server (MSSQLSERVER) service terminated with service-specific error 1814 (0x716) [closed]
My server tempDB size was around 26 GB, I tried to change the path for this by using below script.
USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = ...
1
vote
2answers
47 views
Database reporting alerts
I am about to make a reporting/alerting mechanism that will periodically examine state of one of my databases that is constantly updated and send reports/fire alerts if needed depending on the data in ...
7
votes
2answers
134 views
Does MySQL cache queries?
I'm interfacing a MySQL database with PDO and executing an extensive SQL query. Normally, it takes about 1500ms; I still need to optimize it. When I run the PHP script twice with a short interval in ...
0
votes
2answers
47 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
36 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 ...
4
votes
4answers
76 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 ...
0
votes
0answers
20 views
What Has To Be Considered In Order To Keep A Database Consistent?
I have developed a Winforms application which handles database requests, with the database lying on a local network server. Therefor I used LINQ to SQL.
Now I read about how to use LINQ's support for ...
1
vote
1answer
60 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
0answers
24 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
26 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
38 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
55 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 ...
2
votes
3answers
28 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 ...
1
vote
4answers
91 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
50 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
60 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
39 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
71 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
52 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
49 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 ...
0
votes
1answer
37 views
Displaying multiple data in a column [closed]
I have the following table structure:
Table A: id, rname, idB, idS
1, Learning, 11, 22
Table B: idB, Bname
11, Astronomy
111, Building
Table S: idS, Sname
22, Google
222, Freddy
Table ...
