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)

7
votes
1answer
197 views

Deleting large amounts (4 million rows) of mysql data efficiently, on a very regular basis

We have a mysql table that at any given time has about 12 million rows. We need to delete old data to keep the size of the table somewhat manageable. Right now we are running this query daily, at ...
7
votes
2answers
1k views

Database size - MDF too large?

I'm maintaining a SQL Server 2005 database which hosts approximately 2.9Tb of data (2 x 1.45Tb - I have a RAW schema and an ANALYSIS schema so basically two copies of the data ingested). The recovery ...
7
votes
2answers
4k views

Oracle 11g: performance improvements of inserts

I have a table of 500 millions of rows (and growing) I did the following to improve performance of inserts: On database side: dropped all indexes and constraints disabled logging On application ...
7
votes
2answers
923 views

PAGE compression in SQL Server effect on running queries

I am planning to apply PAGE compression to some of the big tables in my database (DW to be precise). These tables are fairly big with over 15 Billion rows. When I applied the compression in test ...
7
votes
3answers
4k views

How to Best Implement nearest neighbour search in mysql?

So, in short, What should be the data type of latitude and longitude? What SQL command I should call to get the first 100 nearest restaurants for example? Detail: I have 100k biz record each with ...
7
votes
4answers
505 views

SQL Server 2008 and extended properties

In SQL Server, what do you guys use extended properties for? Some articles and blogs are suggesting they be used for self-documentation of the database and its objects but I can't see this being used ...
7
votes
2answers
3k views

Subqueries' aliases same as main queries' aliases

I have an SQL query whose aliases are the same as some of its subquery's aliases. For example: select * from ROOM r where ... ( select * from ROAD r ...
7
votes
2answers
4k views

Table not getting created under dbo schema

When creating tables in SSMS, I've realized that if you execute the following statement: CREATE TABLE [tableName]; the table will be created under your own schema (and not dbo). So in order to ...
7
votes
3answers
1k views

Oracle's left join and where clauses errors

CREATE TABLE "ATABLE1" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); CREATE TABLE "ATABLE2" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); ...
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 ...
7
votes
1answer
447 views

Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
7
votes
3answers
2k views

SQL Server 2008 datetime index performance bug

We are using SQL Server 2008 R2, and have a very large (100M+ rows) table with a primary id index, and a datetime column with a nonclustered index. We are seeing some highly unusual client/server ...
7
votes
1answer
80 views

sysschedules datatype choices

I am Curious. Why does TSQL save the values in msdb.dbo.sysschedules for date and times as ints instead of using datetime. I assume the reason dates back to something in SQL Server 2000. Was it a ...
7
votes
6answers
3k views

Maximum value from multiple tables

Consider the following situation: we have T1, T2, & T3 tables in which saved id and price of products. Now we need to find the id of products which have maximum price of all 3 tables. I have this ...
7
votes
2answers
640 views

Convert units of measurement

Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes. Unit Conversion Table The unit ...
7
votes
2answers
942 views

Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?

I have a table that stores available appointments for teachers, allowing two kinds of insertions: Hourly based: with total freedom to add unlimited slots per day per teacher (as long as slots don't ...
7
votes
2answers
663 views

Filtering data ordered by rowversion

I have a SQL table of data with the following structure : CREATE TABLE Data( Id uniqueidentifier NOT NULL, Date datetime NOT NULL, Value decimal(20, 10) NULL, RV timestamp NOT NULL, ...
7
votes
1answer
494 views

computed columns, index, clustered index and covering index?

We all know the we can only have one clustered view per table, cool. But apparently you don't have values of computed columns on leaf even when using a clustered index and they are computed each time. ...
7
votes
3answers
167 views

Optimize select on subquery with COALESCE(…)

I have a large view that I use from within an application. I think I've narrowed down my performance problem, but I'm unsure how to fix it. A simplified version of the view looks like this: SELECT ...
7
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 ...
7
votes
1answer
488 views

selecting more fields causes catastrophic failure

I have a connection to a linked server from sql server 2012 to pervasive sql. When I do select field1, field2, field3 from mytable everything works! However, when I do select field1, field2, field3, ...
7
votes
1answer
2k views

Unexplained InnoDB timeouts

I've been seeing some very basic updates timing out lately and have not been able to determine the cause. An example: //# Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 UPDATE photos ...
7
votes
1answer
269 views

Designing Simple Schema for Disaggregation of Demand Forecast

I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case: I have a parent-child hierarchy of products (example, Raw ...
7
votes
2answers
521 views

Different results rebuilding an index online and offline

I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages. When I run ...
6
votes
4answers
242 views

Which SQL standard is most broadly and completely supported?

I want to concentrate on one of the SQL language specs for learning and reference purposes. Which version of the SQL standard is most broadly supported across DBMS'es so that I can focus my time and ...
6
votes
4answers
453 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
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 : ...
6
votes
5answers
10k views

In Oracle how do I save a sequence.nextval in a variable to be reused in multiple inserts?

I'm writing a script to populate some tables with data for testing. I would like to write something like the following but I don't know how to do it (I'm Oracle 11g) SET ENABLED_USER_ID = ...
6
votes
3answers
2k 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 ...
6
votes
3answers
2k views

Do you know an easy way to generate one record for each hour of the past 12 hours?

I have a report that shows the count of events for the past 12 hours, grouped by the hour. Sounds easy enough, but what I am struggling with is how to include records that cover the gaps. Here is an ...
6
votes
2answers
586 views

Is there any difference between putting a column alias at the start or the end of the column definition?

I've always seen and written my column aliases as SELECT 1 as ColumnName but today came across a query that used SELECT ColumnName = 1 Is there any difference in how these two queries get ...
6
votes
1answer
249 views

how do I insert a default row?

If I create a table with an identity column as primary key, and all the other columns have default values, for example create table rr (id int identity(1,1) primary key, dt datetime default ...
6
votes
3answers
792 views

SQL Server 2008 mirrored Database with manual failover, need reporting database

Our current setup is a principal database used in production with a manual failover mirrored database for HA. I need to setup a reporting database as well. What would be the best approach? Shall I ...
6
votes
4answers
40k views

Combine column from multiple rows into single row

I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something ...
6
votes
2answers
1k views

Is DB2 more reliable and stable compared to PostgreSQL or MySQL?

Always i was using MySQL or PostgreSQL for my projects such as Web/Desktop apps. I had data loss few times and thanks to old backup i was always up and running. But for a recent project for Airport, ...
6
votes
3answers
3k views

Restrict update on certain columns. Only allow stored procedure to update those columns

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...
6
votes
2answers
11k views

How to do a case sensitive search in WHERE clause?

I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings. Any idea on how to do a case sensitive search in SQL query?
6
votes
5answers
4k views

SQL Select taking too much time to execute

It's a simple select from a temporary table, left joining an existing table on its primary key, with two sub selects using top 1 referring the joined table. In code: SELECT TempTable.Col1, ...
6
votes
4answers
673 views

Returning rows where all non-NULL values are equal

Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say: WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL this ...
6
votes
5answers
4k views

What is the easiest way to get started using databases with real data?

I have a project that could benefit from using a database, but I have no experience with databases, don't have access to a server, and have relatively little experience working with things living ...
6
votes
3answers
1k views

Can you prevent more than X number of rows being updated in SQL Server?

Is there a way to configure SQL Server to limit the number of rows than an update statement can modify? Say I wanted the limit to be 30,000 rows and someone fired an update that would modify 45,000 ...
6
votes
5answers
379 views

Are these good indexing guidelines?

I use indexes regularly but it's still hard for me in certain conditions to know if they are helping or hurting. There are a few guidelines I follow, but I am not sure if they are good nor am I sure ...
6
votes
2answers
4k views

How to use variables inside a select (SQL Server)?

If I want to compute a column and use the result in more than 1 column, how do I do this without making the computing twice? Example: SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as ...
6
votes
3answers
4k views

LIKE to select the independent existence of the word whereever in the text

I have a table with the following texts and the key word i am searching for is 'Search'.So i have written a query SELECT [ID] ,[TextValue] FROM [dbo].[SearchLike] WHERE [TextValue] LIKE ...
6
votes
1answer
3k views

ORDER BY using custom priorities for text columns

This is a sample table: name | cat ---------- hugo | sal bob | mgr mike | dev jeff | mgr john | dev Both name and cat being Varchar. cat reflects a category for these names, but I like to assign ...
6
votes
2answers
295 views

Help with this query

I have a person table. I also have a car table and books table. The car and book table both have a foreign key back person. I am trying to count the number of books and cars each person has: I try: ...
6
votes
3answers
298 views

Query performance

Why SELECT Barraportfolioname FROM portfolio WHERE id IN (SELECT DISTINCT i1.portfolioid FROM Import i1 LEFT OUTER JOIN Import i2 ON ...
6
votes
3answers
227 views

Find material for improvement in Microsoft BI

Which websites, books etc. can you recommend me to improve my basic knowledge in Microsoft Business Intelligence 2008, SSAS, SSIS and SSRS? Please remember that I'm a newbie in technical BI tool. If ...
6
votes
2answers
2k views

SQL Server alter table to change TEXT to NVARCHAR(MAX) greatly increasing database size

I need to update a SQL Server database thats about 18GB in size to change a significant number of TEXT columns to NVARCHAR(MAX). The problem I'm having is after executing all the alter table ...
6
votes
2answers
2k views

How do I query to pull all data in row based MIN and MAX service date?

I'm very new to Oracle SQL especially in the Pentaho report builder. It lets you do some things and block others. PL/SQL is not available so I must do this in a query. I need to be able to pull ...