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)

4
votes
2answers
664 views

Multiple JOIN of 6 tables via 5 database instances

I'm currently reengineering my corporates user management and created a table which lists all users of all the 5 database instances. Next step is, that I need to write a query which shows me all the ...
0
votes
1answer
478 views

Does this error message indicate a SQL injection vulnerability?

I just took over management for a webservice and today I got a user report about this error. Now I'm no sql injection expert but does this error make this attack possible? Microsoft OLE DB Provider ...
0
votes
0answers
51 views

Any recommendation on tuning following query

My query has lot of select statements and unions. I think it is querying database multiple times and combining data. Is there a way we can eliminate multiple passes to the database? I would like to ...
2
votes
1answer
1k views

The job failed. The owner (pc\user) of job FULL DB BACKUP does not have server access

SQL Server 2008R2 is failing to execute log backup hourly. Date 5/2/2013 10:22:19 PM Log Job History (LOG BACKUP) Step ID 0 Server pc Job Name LOG BACKUP Step ...
2
votes
1answer
10k views

MySQL Insert into table, where name = id, if not exist, then insert name and use that ID

I am producing a MySQL database that will store results for a particular sport. I have a table containing people, (PERSONID, FIRSTNAME, LASTNAME), another table containing animals names (ANIMALID, ...
1
vote
2answers
429 views

Is it possible to build an UNDO framework for postgres?

I was thinking of a table which would automatically log all transactions made to other tables and the command to undo that modifications. So every time you issue an UNDO() command you would read the ...
6
votes
2answers
3k views

tempdb logs on same drive as tempdb data or with other logs? [duplicate]

For many reasons I only have 3 hard drives (RAIDed and in an Always-On AG) for all my database files: D: Data E: Logs F: Tempdb Should the tempdb log file go on F: with the data file(s) or on E:? ...
0
votes
1answer
60 views

Wrong return results

I'm trying to grab all the rows that have a risk of critical or high, with the discription or synopsis or solution or cve like password. But it keeps showing all rows not just rows with a risk of ...
-3
votes
1answer
134 views

What will the sql statement “DELETE FROM sales” do?

DELETE FROM sales; And if there are no other uncommitted transactions on the sales table. Which statement is true about the DELETE statement? It removes all the rows in the table and deleted rows ...
0
votes
1answer
323 views

How to profile a particularly slow or inefficient query

I'm currently writing queries for my database but this is the first time I've dealt with large data sets of any sort, and I'm now finding that I need to improve the performance of my queries. ...
1
vote
1answer
231 views

Relative cost of two similar queries involving XML columns

I have two queries as shown below which do the same thing. Here xmlcolumn is a column with datatype XML. I use these queries to search for a string anywhere in the XML column. I checked the execution ...
3
votes
2answers
1k views

How can I create different sets of sequential values based on another column in a table?

I've got a table that holds two types of notes. Collection Notes and Delivery Notes. They are identical data structures, hence using the same table. CREATE TABLE Notes ( Id int IDENTITY(1,1) NOT ...
0
votes
1answer
378 views

xml queries with exist and contains giving unexpected results

I have a query as shown below. DECLARE @tbl TABLE ( id INT, col XML ) INSERT INTO @tbl VALUES (1,'<Root> <Row> <User>xyz</User> ...
1
vote
2answers
837 views

Query for master in Postgres replication

I'm trying to find out if there is a way to query for the master, from a slave PostgreSQL server that has been set up with server replication. From a slave, I can: SELECT pg_is_in_recovery() And ...
0
votes
1answer
94 views

Chaining System Query in MySQL

I have a table which maintains a user chain Here is the table structure +------+--------+ | id | user | +------+--------+ | 2 | 4 | | 10 | 4 | | 11 | 4 | | 1 | 2 | ...
0
votes
3answers
1k views

Space used by databases file through linked server

I need to find out space used by database file for multiple databases through linked server(from one central server to multiple client SQL servers). I created view on client size, for all databases: ...
0
votes
2answers
895 views

SQL Server Maintenance Plan TSQL query to log the backup databases

Does anyone know how to use the TSQL task in SQL Server 2008 Maintenance Plan to log (insert) the databases that where backed-up? Imagine a Maintenance Plan that does the backup for DB1,DB2 and DB3. ...
2
votes
1answer
527 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 ...
4
votes
1answer
1k views

ORDER BY optimization for a large table

I am currently aggregating posts from different social networks. Currently I have 50-100 million posts from Facebook, Twitter, Youtube, Instagram , Pinterest. Consider a table posts posts { id ...
1
vote
1answer
766 views

Count number of common column names among all tables in a database

Is there any way to compare every table with each other in a database, listing the number of common columns between each tables? I prefer using join over subquery and information_schema.columns over ...
2
votes
1answer
142 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 ...
1
vote
3answers
29k 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 ...
5
votes
2answers
286 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
vote
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 ...
1
vote
2answers
60 views

Need logic to collate data

I have a table in the below structure. phone bigint answer_time datetime I should group all phone numbers, and its total number of attempts and mention its no:of time answered and no:of time not ...
0
votes
1answer
84 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
230 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, ...
0
votes
1answer
511 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. ...
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 ...
1
vote
3answers
2k views

Workarounds for setting default schema for domain group?

I am currently working on a project for a client, they have asked me to change the database from Oracle to SQL Server. So we migrated the database across to SQL Server 2008, when this was migrated ...
-1
votes
1answer
303 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?
3
votes
0answers
38 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
1answer
584 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
800 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
3answers
769 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
785 views

Query is not using indexes on third table in left join

My query is not using indexes on third table(pci promotion_coupon_images) . i was tried with using index and force index in query but there is no change in result. any body please suggest me how ...
-1
votes
1answer
2k views

Conditionally INSERTING columns into Table

using SQL Azure and like to insert a data to a table using dynamic Query. No of columns in select statment is dynamic. It will be based on some conditions. Ex: Salary, Code. How can I create a ...
2
votes
1answer
840 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. ...
2
votes
3answers
1k views

performance of multi field primary key or 'contrived' 'semi artificial' key

This is not a question regarding the benefits or otherwise of using an artificial auto increment key in any given table against using a multi-field 'primary key'. That discussion (or argument) can ...
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
1answer
4k 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 ...
4
votes
2answers
141 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 ) --- ...
2
votes
1answer
7k 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 ...
1
vote
2answers
103 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 ...
0
votes
2answers
7k views

Oracle Undo tablespace users

Can anybody help me on "what all users are using the undo tablespace and how much" in oracle database 11g r2. any pointers would be much appreciated.
1
vote
2answers
76 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
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 ...
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 ...