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
427 views

How to Update same table on deletion in MYSQL?

In my Database I have a table: Employee with recursive association, an employee can be boss of other employee. Following is query I used to create table: CREATE TABLE IF NOT EXISTS `Employee` ...
2
votes
1answer
138 views

Select data based on records created_at column

I have two tables Keywords, ProjectReports: http://sqlfiddle.com/#!15/06ae3/1 As you can see in that example everything works fine, but I want to get more data from those 2 tables and I don't know ...
2
votes
2answers
417 views

Speed up bulkinsert on sql server

We're doing some testing and this involves adding 10.000 rows every 10seconds in a table that has 4 int columns and one of them is clustered index. When we run the app the first bulkcopy takes around ...
2
votes
1answer
175 views

How will the order of operations in this query affect my results?

I've written a query, and I'm kinda sure that it does what it want. I'm thinking, "huh, I wonder if I know how Postgres works?". I'm going to list a specific assumption about this query below, and ...
2
votes
2answers
376 views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
2
votes
3answers
2k views

In Microsoft SQL Server 2008, syntax generates the error “The Parallel Data Warehouse (PDW) features are not enabled.”

I have the following virtual column generated from an aggregate over a sorted partition, MIN(picture_id) OVER ( PARTITION BY [360_set] ORDER BY picture_id ASC ) However, when I execute that, I get ...
2
votes
0answers
317 views

Transform XPath map into XML document using relational data

Background Most modern databases have XML functions that can be used to extract data in an XML format. I want to avoid the task of manually calling XML functions to extract the data. This problem ...
2
votes
1answer
6k views

Multiple “GROUP_CONCAT”s with WHERE clause

In a table as id name type info 1 BMW car yes 2 Reno car no 3 IBM electronics no 4 Sony electronics yes 5 Mazda ...
2
votes
1answer
321 views

Restriction on self referencing on insert in MYSQL?

How to restrict insert on adding self referencing rows in a recursive relation table (a table contains foreign key points itself). mysql> SELECT * FROM Employee; ...
2
votes
2answers
2k views

Querying a database efficiently for a huge chunk of data

Here is my query SELECT * FROM ( SELECT a.*, rownum rnum FROM ( SELECT id, data FROM t ORDER BY id ) a ...
2
votes
2answers
1k views

CREATE INDEX for long columns

I am looking for alternative to create index on long column: create table line ( field_key integer not null, value varchar(4000), ... ); create index key_value_idx on line ...
2
votes
1answer
348 views

Is staggering reindexing jobs a good strategy?

I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes. ...
2
votes
1answer
392 views

SQL Server 08: Union over while

I've done some reading on this, and I keep seeing people say it's a bad idea, but hear me out! I have a stored procedure which needs to find a series of timeslots over a number of days. Due to the ...
2
votes
2answers
393 views

Check Index Fragementation Query

I'm completely stumped. I'm a relatively new SQL Server DBA so I'm at a loss with the following situation; hoping maybe someone had an idea. I have a new production SQL Server (Windows Server ...
1
vote
3answers
24 views

Allowing users to compile / run PLSQL and SQL

As a DBA - assuming my users are knowledgeable with PLSQL and SQL - why would I not grant my users privileges to compile and run their own PLSQL code. We have an MI database environment where the data ...
1
vote
1answer
56 views

optimize the query with big and low cardinality

I have big enough dataset and few joins to get them all with help next query: SELECT DISTINCT c.c_id FROM c_active z1 INNER JOIN cs c ON (z1.cv_id=c.cv_id) INNER JOIN indi i ON (c.m_id=i.m_id) INNER ...
1
vote
1answer
160 views

type recognition with MonetDB, *create* tables from CSVs

I am confused whether you can copy records into a new table in MonetDB. The official documentation seems to talk only about copying into existing tables. I have quite a large universe of billions of ...
1
vote
1answer
2k views

Is it possible to query value of SQLNET.EXPIRE_TIME parameter?

Can somebody advice whether it is possible to query for current value of SQLNET.EXPIRE_TIME parameter in Oracle 11 DB? Should select * from v$parameter return value of SQLNET.EXPIRE_TIME (if it ...
1
vote
1answer
1k views

Cascading Inserts in MySql

I have a users table that has a one to one relationship to a user_preferences table (primary foreign key user_id). When a new row is added to the users table (new id added), is there a way to setup ...
1
vote
1answer
203 views

SQL from/where or join?

I was wondering performance wise what would be faster a join or a from and where query. I am trying merge and update query but performance is terrible?
1
vote
3answers
1k views

sql server 2008 - controlling size of log file

I am getting this error. Msg 9002, Level 17, State 4, Line 2 The transaction log for database 'SalesDWH' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc ...
0
votes
1answer
87 views

Select post and concatenating all categories it belongs to

I have 3 DB tables POSTS +----------+----------+----------+ | id | title | content | +----------+----------+----------+ CATEGORIES +----------+----------+ | id | title | ...
0
votes
1answer
103 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 ...
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 ...
10
votes
1answer
6k views

Order by column should have index or not?

I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
9
votes
2answers
3k views

Updating 700 million rows to same value

I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows. I don't have admin access, or access to an admin, so this needs to be accomplished with ...
8
votes
3answers
11k views

How export a sql server 2008 diagram to PDF filetype?

I want to have a export from my database diagram to PDF or image types.How can I do this? I worked with sql server 2008 R2.
8
votes
2answers
1k views

How does using separate schemas affect SQL Server 2008 performance?

I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or ...
8
votes
6answers
2k views

How do you submit a List of items to a Stored Procedure to limit results? [duplicate]

Possible Duplicate: Passing array parameters to a stored procedure Lets say you want to filter results by a List of uniqueidentifiers such that your SQL statement would read: WHERE ...
7
votes
5answers
1k views

Database design: Dividing multiple identical tables, good or bad?

I am very new at SQL and databases in general. I only use them for the occasional homework so I haven't even tried to master them. I have seats at a theater, the seats are divided into 4 main areas ...
7
votes
2answers
339 views

Why do we have SHRINKDATABASE command in SQL Server

I have read across the internet where Finest Developer says shrinkdatabse should be avoided as it causes increase in fragmentation and decrease in performance . So i would like to know from you guys ...
7
votes
1answer
247 views

Table order in Join Query

if i say: Table1 left join Table2 is it the same as saying Table2 right join Table1 ? In other words, should i expect to get the same results from 2 identical queries where the only thing that is ...
7
votes
4answers
4k views

Do SQL Server stored procedures cache data results?

I have heard this from friends, but I never investigated whether this is true. Is it true that the data results of an executed query are stored in cache? I mean, if I have a stored procedure like : ...
7
votes
4answers
4k views

How do I do a complex GROUP BY in MySQL?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want ...
6
votes
4answers
515 views

Is a join optimized to a where clause at runtime?

When I write a query like this... select * from table1 t1 join table2 t2 on t1.id = t2.id Does the SQL optimizer, not sure if that is the correct term, translate that to... select * from table1 ...
6
votes
2answers
1k views

Database locking issues?

I have run into a transaction related issue on a SQL Server 2008 production database. A brief overview is that we have a website that has numerous concurrent users around the state, who do GUI type ...
6
votes
2answers
978 views

Using totals on aggregates to improve performance

I have two tables: details and totals of these details. Details (Slow solution): select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , ...
5
votes
1answer
2k views

Difference between count(*) and count(1) in mysql?

In MySql we can count the total number of records by using count(1) or count(*). Is there any technical difference between them?
5
votes
4answers
2k views

Checking if 2 tables ( on different servers ) have the same exact data

So it seems that the company who is hosting our SQL Servers has been having some trouble with the replication from the production server to the backup server... I believe some of the tables have been ...
5
votes
2answers
541 views

SQL Server configuration / specification recommendations and advice!

I am about to deploy a new server at work as follows: Dell T610 2 x Xeon 2.53 Quad core 24 GB RAM 1 x Perc H700 RAID controller with 1GB Non Volatile memory 8 x 300GB 15k SAS Hard drives I am looking ...
4
votes
2answers
286 views

Migrate all databases from one SQL Server 2005 to another

We have a production SQL Server 2005 server on which we are hosting 200 databases. We want to host these databases on our new server. On new server SQL Server 2005 is installed and is ready. I want ...
4
votes
1answer
214 views

Optimizing concurrent updates in Postgres

I'm running concurrent Postgres queries like this: UPDATE foo SET bar = bar + 1 WHERE baz = 1234 Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which ...
4
votes
1answer
163 views

Do SQL Server Views update their data types automatically based on what tables feed them?

Someone mistakenly created a bunch of nchar fields in database tables. I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside. In my ...
4
votes
1answer
162 views

Order by distance

If I have a query returning nearby cafes: SELECT * FROM cafes c WHERE ( ST_DWithin( ST_GeographyFromText( 'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')' ), ...
4
votes
1answer
1k views

SQL Server 2008 R2 Upgrade (Express to Standard) fails. No “Select Instance”

Hello there, i got stuck in the progress of upgrading 3 systems from SQL Server 2008 R2 Express Edition --> SQL Server 2008 R2 Standard Edition Problem: The Option "Select Instance" in the ...
4
votes
1answer
1k views

How do I get the aggregate of a window function in Postgres?

I have a table containing a two columns of permutations/combinations of integer arrays, and a third column containing a value, like so: CREATE TABLE foo ( perm integer[] NOT NULL, combo integer[] ...
4
votes
2answers
179 views

Checking a wide table for nulls [duplicate]

Possible Duplicate: Test if any fields are NULL I am new here. I have a table with 70-odd columns. Is there a way I can check the number of rows with at least one null value in any column ...
4
votes
1answer
6k views

oracle format specifiers: to_number vs to_char

SQL> select TO_NUMBER(123.56,'999.9') from dual; select TO_NUMBER(123.56,'999.9') from dual * ERROR at line 1: ORA-01722: invalid number SQL> select TO_CHAR(123.56,'999.9') ...
3
votes
3answers
78 views

How long to learn PostgreSQL?

I want to learn PostgreSQL and How to make the learning process efficient, but really don't know how long it would take even after googling my question. I used MySQL before and knew relational ...
3
votes
2answers
91 views

Slow fulltext search due to wildly innacurate row estimates

Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The database schema is ...