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.
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
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
4answers
5k 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
7answers
44k views
How do I Create Tablespace in Oracle 11g?
I am not too savvy in Oracle, and I have been trying to create a TABLESPACE, which keep on giving me an error message of invalid file name. Below is the syntax that I have used:
SQL> create ...
6
votes
2answers
262 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
3answers
327 views
SQLITE: A problem of tags and products
I'm searching for a way to create a query to do the following:
Let's consider 3 tables:
products: List of products
tags: list of tags
tag_ties: table used to associate a tag to a product
Let's ...
6
votes
3answers
2k views
How to differentiate between SQL and PL/SQL?
I know the question might sound too stupid, but I never understood this part.
SQL*Plus works with both SQL and
PL/SQL. How do I know whether some
code is SQL or PL/SQL? If my code has
a for ...
6
votes
6answers
390 views
Find “n” consecutive free numbers from table
I have some table with numbers like this (status is either FREE or ASSIGNED)
id_set number status
-----------------------
1 000001 ASSIGNED
1 000002 FREE
1 000003 ...
6
votes
1answer
164 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
14k views
How to select the first row of each group?
I have a table like this:
ID | Val | Kind
----------------------
1 | 1337 | 2
2 | 1337 | 1
3 | 3 | 4
4 | 3 | 4
I want to make a SELECT that will return just the ...
6
votes
3answers
604 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
2answers
837 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
4answers
11k views
Can I see Historical Queries run on a SQL Server database?
Someone was running a query on our SQL Server database remotely and their system crashed.
They have no backup of that query and want to see what was run on the server.
Is it possible to find this ...
6
votes
5answers
2k 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
3answers
296 views
SQL Server: Worse performance on a new server
We've been on a dedicated server (single quad-core, 6 GB RAM) and are moving to a new dedicated server (2x hex-core, 32 GB RAM). Both are Windows Server 2008, SQL Server 2008. The performance on the ...
6
votes
4answers
257 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
309 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
2k 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
466 views
Is there a standard implementation of a Users Database?
I need to implement basic personalized user functionality for my website. Is there a standard structure for databases of this type? Like is it common practice to have all user info and data in a ...
6
votes
3answers
2k 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
3answers
275 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
191 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
1answer
2k 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
745 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 ...
6
votes
1answer
120 views
selecting where two columns are in a set
This might be a silly question, and my suspicion is that I can't do this, but is there a construct in SQL that would allow me to do something like the following:
SELECT whatever WHERE col1,col2 IN ...
6
votes
2answers
467 views
How to keep history of SQL Server stored procedure revisions
Note: I am not asking about full version control.
Is there any way automatically to keep a history of stored procedures on SQL Server.
Similar to how Google Docs automatically keeps a history of ...
6
votes
1answer
142 views
Are there any fully declarative implementations of SQL
I was recently bitten by a bug ( in my code ) where these two queries had drastically different runtimes:
select * from smalltable st
inner join bigtable bt on st.btid = bt.btid
select * from ...
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 ...
6
votes
3answers
869 views
SQL Server 2008 R2 High CPU
We are having a SQL Server R2 Web Edition database, based on Windows Server 2008 R2 with a strong CPU and 32GB RAM DDR3.
Our database works very hard (we run around 600,000 queries in 1 minute) and ...
6
votes
3answers
1k views
Why is this Full Outer Join not working?
I've used Full Outer Joins before to get my desired results, but maybe I don't fully understand the concept because I am not able to accomplish what should be a simple join.
I have 2 tables (which ...
6
votes
2answers
163 views
Do agile software development approaches apply to SQL?
I would like to learn if agile software development methods/principles/patterns are valid to SQL programming as well. If yes, where would be a good place to start learning about that? Are there any ...
6
votes
1answer
300 views
Loyalty reward - record for every day of a week
I have a table that contains every login a user makes. I am trying to make a loyalty system that rewards users if they've logged in every day for a week.
So, the real question, how do I get every ...
6
votes
1answer
733 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?
6
votes
2answers
979 views
How to effectively query and aggregate a normalized SQL db?
In relation to Most efficient way to return multiple aggregates in a single stored proc?
I have an email type application and want to select all the messages (inbox) for a user. The problem is that I ...
6
votes
2answers
288 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 ...
6
votes
1answer
287 views
How do I preserve the order of the original elements in an unnested array?
Given the string
'I think that postgresql is nifty'
, I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details ...
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
871 views
For a MySQL query, how do you determine physical and logical I/O?
I'm used to having a showplan in Microsoft SQL and have gotten lazy with using tools to tune MySQL queries. How can I determine physical/logical I/O for a query in MySQL? (EXPLAIN shows the index ...
6
votes
3answers
135 views
Primary Key efficiency
If I need a table to hold Point of Sales transactions, and am told I need to store:
Country ID
Store Number
POS Terminal Number
Transaction Date
Item Code
Teller ID
Another Field
More Fields
Now, ...
6
votes
1answer
224 views
Is there an execution difference between a JOIN condition and a WHERE condition?
Is there a performance difference between these two example queries?
Query 1:
select count(*)
from table1 a
join table2 b
on b.key_col=a.key_col
where b.tag = 'Y'
Query 2;
select ...
6
votes
1answer
95 views
What happens behind the scenes when adding a nullable column in SQL Server?
This question came up today. When you add a column that accepts null values to the end of a table what exactly happens? Does the table get fully rebuilt similar to what happens when you change column ...
6
votes
2answers
149 views
Is it worth partitioning a Table by single value in Oracle?
Normally, if you have a column you frequently query a table by, you ought to stick an index on it. But is it worth also partitioning a table by this column, if all possible values are known in ...
6
votes
1answer
2k views
SQL Server 2000 : List and Disable all triggers
I have to do a database cleanup for one of our databases on a sql server 2000 environment. The cleanup basically includes the disabling of all triggers, deletion of all old data, enabling of the ...
6
votes
1answer
174 views
How do I optimize large table so queries that target only recent data perform optimaly?
So I have this table that is ever growing. Most queries are targeting just recent data, say one month old. I suppose this is common problem but I have no idea how it can be solved.
I am open to ...
6
votes
6answers
2k 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 ...
6
votes
3answers
1k views
Why performance degradation with query hint FIRST_ROWS(1000)? [closed]
I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input.
In case the user supplies very unselective arguments, the query takes a lot of ...
6
votes
1answer
190 views
32-bit or 64-bit for development and deployment?
I'm creating a Windows desktop application and want to include a database. I've chosen SQL Server Compact 4 but when I download it there are two versions (32-bit and 64-bit). My development machine is ...
6
votes
2answers
473 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
3answers
149 views
Is it possible to see the (completed) SQL Statements executed by a specific session?
Is it possible to see a list of all past SQL statements executed by a specific session (or number of sessions)?
For example, the following query gives me all currently executing statements from all ...
6
votes
2answers
348 views
Is there a better way to create dynamic time averaged series over a dynamic frequency?
I've got a series of tables with lots of high precision data collected from various devices. The intervals that they were collected on varies and even wanders over the time series. My users want the ...
