DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

2
votes
1answer
143 views

Is it ok to let users add new columns and tables to your database?

I'm basically making a database of databases. An administrative User can go in and hit "Create Database" (which is basically a table) and set up the column types and how many columns. Other users can ...
0
votes
2answers
5k views

How to use GROUP BY after the Having clause Mysql

Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions All the accounts which is not ...
2
votes
3answers
33k views

How to write a query to find all tables in a db that have a specific column name

I've got a database with about 100 tables and I need to build a join query to get specific data from two of them. I know one but not the other. Basically I need something like: select ...
2
votes
3answers
3k views

Nonclustered index is faster than clustered index?

Both tables have same structure and 19972 rows in each table. for practicing indexing, i created both tables having same structure and created clustered index on persontb(BusinessEntityID) and ...
0
votes
1answer
85 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
534 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
1answer
237 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, ...
5
votes
2answers
297 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 ...
5
votes
1answer
3k 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
391 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
324 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?
1
vote
1answer
1k 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 ...
3
votes
0answers
39 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 ...
2
votes
1answer
546 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
1answer
594 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
2answers
2k 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
828 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 ...
3
votes
1answer
3k 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
2k views

How to compare an attribute value of one table to all attribute values of another table? [closed]

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 ...
3
votes
3answers
794 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 ...
2
votes
1answer
8k 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 ...
3
votes
1answer
5k 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
884 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
143 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
vote
2answers
77 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
1answer
184 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
2k 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
104 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 ...
11
votes
2answers
4k 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
90 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
119 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
415 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
2k 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 ...
2
votes
1answer
3k 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
57 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
1k 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
2k 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 ...
9
votes
4answers
11k 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
112 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
672 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
202 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
877 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
70 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
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
1answer
142 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
1k 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) ) ...
4
votes
2answers
6k views

Reduce Clustered Index seek cost SQL Server 2008 R2

I am running a query and it has records in the hundreds of thousands, 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 ...
0
votes
1answer
2k 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
766 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
2k 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; ...