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.
11
votes
3answers
642 views
Is table aliasing a bad practice?
I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type:
SELECT t1.id, t2.stuff
FROM
someTable t1
...
11
votes
6answers
1k views
Is polling the only way for updating app's data from a database?
An application needs to have data as more freshly updated from a database as possible. In such a case, is there any other way for getting the data, besides of a timer based requesting (polling) the ...
11
votes
5answers
610 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 ...
11
votes
2answers
823 views
System disk run out of space when running heavy SQL queries on SQL Server 2012
I'm quite new to SQL Server 2012, I would be grateful if someone can help.
I have restored a copy of a huge database to SQL Server 2012 and I tried to run some simple queries against it.
I'm trying ...
11
votes
4answers
524 views
Creating centralized DBA database
We have 200+ servers and I am thinking about creating a centralized DBA database where it logs backup, errors, space jobs and generate reports on SSRS. I am not sure where to start and I would highly ...
11
votes
2answers
2k views
What is the best way to get a random ordering?
I have a query where I want the resulting records to be ordered randomly. It uses a clustered index, so if I do not include an order by it will likely return records in the order of that index. How ...
11
votes
1answer
1k views
Patterns for setting up sharding for SQL Server 2008 R2, in order to handle large datasets?
I want to handle a large dataset (> 1 billion rows) in SQL Server 2008 R2. I heard that if you set up "sharding" or "horizontal partitioning", it makes it quicker to handle large datasets as it breaks ...
11
votes
1answer
519 views
What does collation mean in database parlance?
I am learning sqlite from a book which has mentioned collation and collating sequence multiple times. What does it mean exactly in the database world ?
11
votes
2answers
2k views
MySQL: Tree-Hierarchical query
SUB-TREE WITHIN A TREE in MySQL
In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor ...
11
votes
3answers
533 views
Why does my ORDER BY sort two tables before the EXCEPT (slow) and not after (fast)?
SQL server 2008 R2 query optimizer puzzle
We have two tables, both containing 9 million rows. 70.000 rows are different, the others are the same.
This is fast, 13 seconds,
select * from bigtable1
...
10
votes
3answers
2k views
Does “WHERE 1=1” usually have an impact on query performance?
I recently saw the question "where 1=1 statement"; a SQL construct I have used often in constructing dynamic SQL in an effort to write cleaner code (from the perspective of the host language).
...
10
votes
4answers
3k views
How do I copy a table with SELECT INTO but ignore the IDENTITY property?
I have a table with identity column say:
create table with_id (
id int identity(1,1),
val varchar(30)
);
It's well known, that this
select * into copy_from_with_id_1 from with_id;
results in ...
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) ...
10
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.
...
10
votes
1answer
2k views
How do I read Query Cost, and is it always a percentage?
I'm currently studying for SQL 70-433 (the Microsoft Certification exam), and I'm getting very confused about the "query cost" performance metric.
According to any documentation I could find via ...
10
votes
1answer
583 views
Generate an exception with a Context
When PostgreSQL throws an exception, there is a line "CONTEXT" like:
ERROR: INSERT has more target COLUMNS than expressions
LINE 3: ...
...
10
votes
3answers
423 views
Use MySQL to regularly do multi-way joins on 100+ GB tables?
Background:
I’ve created a web application that I would like to be able to scale reasonably well. I know I'm not Google or Twitter, but my app uses a fairly large amount of data for each user and thus ...
9
votes
3answers
334 views
Are these two queries logically equivalent?
Are these two queries logically equivalent?
DECLARE @DateTime DATETIME = GETDATE()
Query 1
SELECT *
FROM MyTable
WHERE Datediff(DAY, LogInsertTime, @DateTime) > 7
Query 2
SELECT *
FROM ...
9
votes
4answers
904 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 ...
9
votes
3answers
3k views
How do you clear out all old query plans from within Microsoft SQL Server?
We have an off the shelf application that uses a Microsoft SQL database. Within this application we pick and choose various selection criteria for each report. This application then runs these ...
9
votes
5answers
1k views
How do I query this 20 million record view faster?
For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...
9
votes
3answers
3k views
Should I use SQL JOIN or IN Clause?
I have a question about best approach. I am not sure which approach is best when data is considered variable in size.
Consider the following 3 TABLES:
EMPLOYEE
EMPLOYEE_ID, EMP_NAME
PROJECT
...
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 ...
9
votes
2answers
283 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 ...
9
votes
1answer
412 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.
...
9
votes
5answers
924 views
Efficiently select beginning and end of multiple contiguous ranges in Postgresql query
I've got about a billion rows of data in a table with a name and an integer in the range 1-288. For a given name, every int is unique, and not every possible integer in the range is present--so there ...
9
votes
2answers
231 views
How many Sproc Parameters is too many?
I've just started writing a Stored Procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...
9
votes
3answers
277 views
Query Performance Tuning
When you finish writing a query/stored proc/function, what's the most informative way to quickly get some performance parameters? Do you run the query and view the actual execution plan? If so, what ...
9
votes
3answers
723 views
Updating a table with millions of records, its been 4 days
I am currently updating a table with millions of records, its been 4 days and query is still executing.
I checked the activity monitor its shows that query is running.
In event log there is no ...
9
votes
1answer
3k views
Varchar(Max) field cutting off data after 8000 characters SQL Server 2008
I have a field to store some data, the field is declared as varchar(Max). To my understanding this should be storing 2^31 - 1 characters but when I enter some content over 8000 chars it cuts the rest ...
9
votes
1answer
3k 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
1answer
7k views
Difference between Hash, Merge and Loop join?
In SQL Server you can specify the join hints:
HASH JOIN
MERGE JOIN
LOOP JOIN
What is the definition of those three join hints, and when should each be used?
9
votes
3answers
2k views
Why is an aggregate query significantly faster with a GROUP BY clause than without one?
I'm just curious why an aggregate query runs so much faster with a GROUP BY clause than without one.
For example, this query takes almost 10 seconds to run
SELECT MIN(CreatedDate)
FROM MyTable
WHERE ...
9
votes
3answers
1k views
Best way to design tournament database
I'm creating a webpage for placing bets on all matches of the upcoming Euro 2012 football tournament. Need some help deciding what approach to take for the knockout phase.
I have created a mockup ...
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 ...
9
votes
2answers
3k views
Slow Performance Inserting Few Rows Into Huge Table
We have a process that takes data from stores and updates a company-wide inventory table. This table has rows for every store by date and by item. At customers with many stores, this table can get ...
9
votes
2answers
627 views
Treating certain Arabic characters as identical
In Arabic we have characters like ا (alef) and أ (alef with hamza).
Users write them interchangeably and we want to search them interchangeably. SQL Server treats them as separate characters. How ...
9
votes
5answers
2k views
Group By hour over large dataset
Using MS SQL 2008 I am selecting an averaged field from 2.5 million records. Each record represents one second. MyField is an hourly average of those 1 second records. Of course the server CPU hits ...
9
votes
1answer
3k 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 ...
8
votes
4answers
4k views
Methods of speeding up a huge DELETE FROM <table> with no clauses
Using SQL Server 2005.
I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is ...
8
votes
3answers
900 views
Used Memory not freed up after a SQL BULK Insert / BCP Export
I have created a very basic SQL table as following
CREATE TABLE [dbo].[TickData](
[Date] [varchar](12) NULL,
[Time] [varchar](12) NOT NULL,
[Symbol] [varchar](12) NOT NULL,
[Side] [varchar](2) ...
8
votes
2answers
172 views
Postgres query plan of a UDF invocation written in pgpsql
It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed via a UDF (using EXPLAIN). So, how do I get a hold of the query plan for a particular ...
8
votes
4answers
21k views
How to update a table from a another table
I have one table A has column (id, field_1,field_2),
and another table B has column (id,field_2)
Now I want to merge table B to A, that means i want to update field_2 in table A to value of table B. ...
8
votes
3answers
1k views
Storing date as integer (numeric), what are the advantages
Question 1
I am working with a system where date is stored as integer (actual numeric(8,0)) and I have noticed that other systems also store date as int such as cisco in this thread. Example
...
8
votes
2answers
525 views
Optimization: Moving variable declarations to the top of your procedure
While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity.
One thing the DBA mentioned was I should ...
8
votes
2answers
149 views
Non-Clustered Indexes - keys and nonkeys
I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated.
Here's my theory from the query I've just optimised, through a process of trial and ...
8
votes
3answers
8k views
Notification on long-running query or deadlock in SQL Server 2008 R2?
I'd like to know if there is a way to send a notification on deadlock? If so what queries would be required. I understand that SQL Server takes care of deadlocks, I simply would like information on ...
8
votes
3answers
794 views
What are the alternatives for an ORDER BY clause in a View?
This question just had to be in this site :)
ORDER BY is forbidden to use in a view, as I understood because of the possibility for multiple order by's when using this view.
I know that there are ...
8
votes
2answers
171 views
Where can I find the SQL standard document?
Where can I find a legal copy of the ISO SQL 2008 standard?
8
votes
2answers
12k views
How do I use currval() in PostgreSQL to get the last inserted id?
I have a table:
CREATE TABLE names (id serial, name varchar(20))
I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I ...
