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.
2
votes
1answer
121 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
571 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
204 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
225 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 ...
1
vote
2answers
138 views
How do I force a JOIN to use a specific index in MySQL?
I have a query that JOINs 2 tables; lineitem and part,
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem force index for join (l_pk),
part
where
(
...
1
vote
2answers
432 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
...
0
votes
3answers
344 views
Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?
I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote ...
0
votes
1answer
882 views
Minimum user rights for connecting MS Sql database remotedly
I have followed this tutorial for getting connected to a Microsoft SQL Server. I connect with ping functionality of Glassfish Server and thus I have to give some user rights to be able to even ping ...
9
votes
1answer
2k 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 ...
9
votes
1answer
2k 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
1k 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 ...
7
votes
5answers
589 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
198 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
6answers
700 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
4answers
2k 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
287 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
1answer
176 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 ...
6
votes
4answers
2k 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 :
...
6
votes
2answers
478 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 ...
5
votes
2answers
444 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.
5
votes
1answer
688 views
Changing ANSI_NULLS setting on a table
Can we use the following query to update ANSI_NULLS option.
sp_configure 'allow updates', 1
reconfigure with override
update sysobjects set status = status | 0x20000000 where name = 'tblClient'
...
5
votes
4answers
491 views
Multiple operations using WITH
Is there a way to execute multiple operations using the WITH statement?
Something like
WITH T AS
(
SELECT * FROM Tbl
)
BEGIN
OPEN P_OUTCURSOR FOR
SELECT * FROM T;
SELECT COUNT(*) INTO ...
5
votes
2answers
628 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
342 views
Dealing with temp tables when I have not control over db variables
I have no control over things like tmp_table_size and max_heap_table_size, and so as our tables grow the time taken by queries requiring temp tables is growing geometrically.
I am wondering if there ...
4
votes
2answers
115 views
Select longest continuous sequence
I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specifc field.
Consider the following table:
lap_id (Serial), lap_no (int), car_type ...
4
votes
2answers
133 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
3k 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') ...
4
votes
2answers
330 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 ...
3
votes
1answer
163 views
How to categorize rows by SUM of a column in mysql?
I have a table as
id num_items folder
1 4
2 33
3 74
4 41
5 24
6 34
7 46
8 55
9 11
I want to assign folder number to rows but limiting the number of items in each folder (e.g. ...
3
votes
3answers
308 views
SQL JOIN Syntax in MS SQL
I have been taught in my MSSQL classes, this is how to join two tables
select * from FirstTable A
JOIN SecondTable B
on A.ID= B.ID
Now in my professional life, I came across JOIN queries like ...
3
votes
5answers
3k views
Copying Oracle 11g express Database from one server to another
Basically, I am a MS-SQL developer,and relatively new to Oracle.
Even after great deal of research, I was unable to find any way of copying entire database from one server instance to another.
In ...
3
votes
2answers
1k views
SQL Server 2008 T-SQL select hanging, but not dead locked
This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally ...
3
votes
3answers
293 views
Transaction Log 16GB on 2MB database [duplicate]
Possible Duplicate:
Why is My SQL Server Transaction Log Growing or Running out of Space?
The transaction log in one of my client's databases is 16GB, when the database itself is only 2MB. ...
3
votes
2answers
334 views
Testing Query Speed
When I'm running a query for the first time, it takes x miliseconds to be executed. When I run it the second time it takes a lot less time. I assume that it is some caching involved in this mechanism, ...
3
votes
2answers
309 views
Unique time range or database schema for a scheduling system
I'm having to design a table which contains scheduled times for media to be displayed. These scheduled times should not overlap.
In my SheduledSlot table I have:
id integer PRIMARY KEY
begin ...
3
votes
2answers
475 views
A query submitted from different applications has differing DOP
I have a query which selects from multiple views and is fairly I/O heavy. If I execute this query using Management Studio it uses parallelism across most of the 16 CPUs and completes in under 10 ...
3
votes
1answer
2k views
How do I make MySQL auto increment in random order?
I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to create a new column for each key that would be an integer such that ...
2
votes
1answer
196 views
Select the top 5 records and the specific column from each table in the same database
I have 50+ tables in a database.
I don't want to use the following SQL on each table in a database.
select top 10 * from {table_name}
So, is it possible to display the first 10 records PLUS only ...
2
votes
2answers
234 views
ORDER BY clause is allowed over column that is not in SELECT list?
In theory, every statement in SQL Server create virtual table passed to next statement. In other words, when FROM and Where clause are finished, next step is SELECt columns from virtual group created ...
2
votes
1answer
172 views
SQL INSERT Query takes 2 seconds and sometimes much longer to execute
I know the question's title is bad, But I have an hard time figure it out my self...
sometimes this query runs in 2-3 seconds and sometimes it's takes even 30 seconds
I have a table named ...
2
votes
2answers
262 views
Query: All employee with sum of salary > 500
In my MYSQL Database COMPANY, I have a Table: Worker :
mysql> DESC `Worker`;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
...
2
votes
2answers
250 views
How to GROUP_CONCAT between given rows?
I have a table as
id diary
1 breakfast
2 walk
3 start
4 office works
5 office projects
6 end
7 taxi
8 start
9 preparing for meeting
10 doing the meeting
11 end
12 night
I want ...
2
votes
2answers
157 views
Design best practices for last tracking with indexed view
My tables structure is below :
TbDoc (ID int , ...)
TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...)
I want to have indexed view to get last TbDocActions columns for ...
2
votes
2answers
253 views
Find the largest databases on my server
I have several hundred databases running on the same MySQL server. How can I get a list sorted by the size of the databases?
2
votes
1answer
191 views
Flat query to Column structure
I am trying to structure a query for a calendar view.
I want each row to return the details for a person and what they are doing for 7 days (split into AM and PM)
So the result set might look like ...
2
votes
3answers
135 views
How to get all non deleted messages from one user
I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like:
CREATE TABLE IF NOT EXISTS ...
2
votes
1answer
73 views
SELECT for values in a table
I have this table structure:
id1 id2
1 2
1 3
2 1
2 4
id1 and id2 are not unique as you can see in the values. I need to retrieve those values that exists in the two columns ...
2
votes
2answers
414 views
Help with this SELECT in the same table
I have this table structure:
id1 id2
1 2
1 3
1 4
2 1
2 5
I need to build a SQL to select id2 where id1 is not in id2. For example if id1 = 1 then only id2=3 and id2=4. Any help? I ...
1
vote
1answer
22 views
Moving postgresql data directory
I moved postgresql's data directory by following the following steps:
Stop postgres
cp -a source_data_directory destination_data_directory
export PGDATA=destination_data_directory
Changing data ...
1
vote
3answers
132 views
How to model inheritance of two tables mysql
I have some tables where I store data and depending on the type of person (worker,civil) that did a job I want to store it a table 'event', now these guys rescue an animal (thereis a table animal).
...
