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.
7
votes
1answer
2k views
Reasons for occasionally slow queries?
We are running MySQL 5.1 on Windows Server 2008 R2.
We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to ...
4
votes
3answers
854 views
Problem with MySQL subquery
Why does this query DELETE FROM test WHERE id=(SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1); sometimes delete 1 row, sometimes 2 rows and sometimes nothing?
If I write it in this ...
14
votes
2answers
4k views
Find highest level of a hierarchical field: with vs without CTEs
note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database.
I ...
35
votes
4answers
5k views
What is the difference between select count(*) and select count(any_non_null_column)?
I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table.
What are the differences between these ...
10
votes
2answers
2k views
How do I map an IS-A relationship into a database?
Consider the following:
entity User
{
autoincrement uid;
string(20) name;
int privilegeLevel;
}
entity DirectLoginUser
{
inherits User;
string(20) username;
string(16) ...
9
votes
1answer
1k views
How to get the MAX row
In SQL Server I've always found it a pain to get the max rows for a dataset, I'm looking for a list of the methods to retrieve the max rows with some guidance on performance and maintainability.
...
2
votes
2answers
3k views
Local database vs Amazon RDS
I am doing large INSERT into a local MySQL database
I am at 2,000,000 rows and am really starting to notice the slowdown now. I always heard that MySQL wasn't very scalable
I expect this database to ...
8
votes
2answers
263 views
Decision criteria on when to use a non-dbo schema vs a new Database
I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out ...
44
votes
4answers
4k views
Why use both TRUNCATE and DROP?
In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them.
Many of my colleagues (almost ...
3
votes
1answer
3k views
Performance implications of using OPENQUERY in a view
Please see this http://stackoverflow.com/questions/7753380/performance-implications-for-using-openquery-in-a-view question on stackoverflow, it's a cross over subject and would love a dba's POV
6
votes
1answer
1k views
Deadlock error isn't returning the deadlock SQL
Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I am getting this error randomly when one of my ...
3
votes
1answer
172 views
Limiting database security
A number of texts signify that the most important aspects offered by a DBMS are availability, integrity and secrecy. As part of a homework assignment I have been tasked with mentioning attacks which ...
-6
votes
1answer
723 views
How do I convert this Access query for SQL Server? [closed]
I have a Query used in Access which does not work in SQL Server as it stands:
SELECT
mstPatient.PatientID,
mstPatient.PatientID AS PKNO,
...
12
votes
5answers
470 views
Needing A Database Design Book
I am designing a database and it has so many relationships among my tables and i need a book that teaches database design very well.I am looking for a book where table relationships simple and complex ...
28
votes
8answers
5k views
Is there a tool like Microsoft's “SQL Server Profiler” for MySQL?
While developing on MySQL I really miss being able to fire up a profiler. I find SQLyog is a good enough replacement for Query Analyzer but have not found a tool that works like SQL profiler.
For ...
7
votes
3answers
456 views
More Databases Per Sql Server 2005 Instance
Our company currently hosts about 400 databases per SQL Server 2005 instance. 500-600 databases and we see performance start to deteriorate.
Database size ranges from 50mb to 40-60gb in the extreme, ...
15
votes
3answers
2k views
Traversing tree-like data in a relational database using SQL
Is there a way to traverse tree data in SQL? I know about 'connect by' in Oracle, but is there another way to do this in other SQL implementations? I'm asking because using 'connect by' is easier ...
11
votes
5answers
590 views
Why do primary keys have names of their own?
From a mathematical view, granted that a table has at most one primary key, it seems to be a shortsighted design decision to refer to primary keys by some arbitrary name instead of a simple table ...
5
votes
2answers
869 views
'Conversion failed' error with ORDER BY CASE expression
I have the following Stored Procedure that works great EXCEPT when I set the 'OrderBy' to 'OrderNumber'.
I get the following error:
Conversion failed when converting the nvarchar value 'SK11270' to ...
3
votes
2answers
461 views
Concurrently update with limit mysql
I want to know if concurrently UPDATE .... LIMIT N will not overlap with more than one client querying the db.
After UPDATE ... LIMIT N the client will make a SELECT with some client_id assigned.
I ...
4
votes
1answer
523 views
How to get text for default language
I have so tables:
and so data at Language table:
and so data at Text table:
I have to return text for requested language if it exists and text for default language if it does not exist. Is it ...
0
votes
3answers
7k views
SQL Server 2008 R2 High memory usage
I have SQL Server 2008 R2 that runs multiple jobs over night, the problem is SQL Server will eat up all RAM and CPU, then it will not release the RAM resources after the job finishes, as a workaround ...
152
votes
14answers
35k views
Can MySQL reasonably perform queries on billions of rows?
I am planning on storing scans from a mass spectrometer in a MySQL database and
would like to know whether storing and analyzing this amount of data is remotely
feasible. I know performance varies ...
41
votes
7answers
1k views
Does SQL Server read all of a COALESCE function even if the first argument is not NULL?
I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process:
...
9
votes
3answers
4k views
PostgreSQL multi-column unique constraint and NULL values
I have a table like the following:
create table my_table (
id int8 not null,
id_A int8 not null,
id_B int8 not null,
id_C int8 null,
constraint pk_my_table primary key (id),
constraint ...
5
votes
2answers
3k views
Learning to Optimize SQL Queries and Understand Execution Plans - Resources?
I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team.
They ...
4
votes
4answers
2k views
MySQL: Index when joining to tables not being used (Performance optimizing question)
I need to optimize the following query:
SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM blogposts
JOIN articles ON articles.blogpost_id = blogposts.id
WHERE ...
17
votes
4answers
3k views
Why does MySQL not have hash indices on MyISAM or InnoDB?
I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
12
votes
1answer
2k views
Why is using a table variable more than twice as fast as a #temp table in this specific case?
I was looking at the article here
Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for ...
9
votes
1answer
381 views
Why does NOT IN with a set containing NULL always return FALSE/NULL?
I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything.
...
4
votes
4answers
4k views
Building Dynamic Oracle Where Clause
I'm working on application that uses dynamic query to do a select statement based on user input, after discussing security with DBAs they want me to convert my dynamic select statement into Stored ...
2
votes
3answers
1k views
Disable trigger for just one table
Is it possible to disable a trigger momentarily but for just one table.
For example I have table, TableA with an on insert, update and delete trigger. I also have a table B with the same triggers but ...
1
vote
3answers
383 views
SQL Subquery , One to Multiple rows
Following query I have written returns the result as expected, example 121350 rows.
SELECT dbo.Articles_in_Consignment.Consignment_id,
dbo.ORDER_D.Orders_boxsize,
...
7
votes
5answers
2k views
Query Running Differently on SQL 2005 vs SQL 2008R2
At my office, we have a query that is pretty ugly, but runs pretty well in production and in the development environment (20sec, and 4sec respectively). However in our testing environment it takes ...
6
votes
5answers
459 views
Why is the SELECT clause listed first?
I'm not sure if this question is on-topic - if not, feel free to close. Also, if it'd be a better question for another StackExchange site, I can ask it elsewhere.
I was wondering why SQL places the ...
5
votes
3answers
665 views
Passing in the table into a stored procedure
Is it possible to pass in the name of a table into a stored procedure?
For example, suppose you have several views of the same table. They all have the exact same structure.
You want a store ...
5
votes
2answers
673 views
Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios
I need to keep track of deleted items for client synchronization needs.
In general, is it better to add a tombstone table and a trigger that tracks when a row was deleted from the server database - ...
4
votes
4answers
498 views
Group data by non-unique keys by distinct time range
I have data in my table like following...
PROJ_NBR STATUS START_DT
AL20 AC 1/14/2010 4:31
AL20 AC 1/14/2010 4:32
AL20 AC ...
3
votes
2answers
71 views
Algorithmic order of table indexes for table operations
I have not been able to find a clear indication of the impact table indexes have on the algorithmic order of table operations.
I am not interested in the nuts & bolts of specific implementations; ...
3
votes
3answers
879 views
Test if any fields are NULL
I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column.
I need something like
SELECT * FROM ...
2
votes
2answers
114 views
Shrinking SQL Server 2000 database
I apologize for the long question. I am trying to learn a lot on the fly as I have become the defacto SQL Admin. I have been tasked with moving a production database to a new server. My goal is to ...
2
votes
0answers
80 views
How to Audit for Access with Cross Database Ownership Chaining
I've just joined the data team of a new company and we have a large SQL Server environment (100+ instances) and cross database ownership chaining is on by what I gather is a necessity. I'm looking for ...
2
votes
2answers
188 views
SQL Data aggregation
I have a problem with sql data aggregation problem.here is the sample sql.I am using oracle database.
select pm.city cityid, to_char(pm.sampledate, 'yyyy-MM') timepoint, count(distinct me.id) ...
0
votes
3answers
155 views
MySQL data view
I have a table that looks like this:
+----------------------------------------+
|Name | kode | jum |
+----------------------------------------+
| aman |kode1 ...
0
votes
1answer
2k views
Set permissions on all objects for SQL users
I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to ...
17
votes
3answers
2k views
Performance difference for COALESCE versus ISNULL?
I've seen a lot of people use the COALESCE function in place of ISNULL. From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when ...
9
votes
4answers
777 views
Is it possible to increase query performance on a narrow table with millions of rows?
I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
7
votes
2answers
213 views
Mirroring the Distributor
Has anyone had success with mirroring the distribution database? We have a dedicated server as a Distributor. It handles all our push replication from prod to reporting. We'd like to build an ...
4
votes
2answers
216 views
Introducing an 'age' category in a patients database
I want to do the following in my DB related to patients:
when patient inserts his birthday it shows his age category as: infants, children, adult, elderly... etc. to help me classify my patients
how ...
3
votes
3answers
935 views
NULLs in a composite primary key - SQL Server
I'm trying to work out the best way of creating SQL Server primary keys, foreign keys and constraints to accurately represent my data model in LINQ / Entity Data Objects. Let's assume - for the ...
