Most Popular
1500 questions
65
votes
2 answers
Reference column alias in same SELECT list
I'm converting an old MS-Access-based system to PostgreSQL. In Access, fields that were made up in SELECTs could be used as parts of equations for later fields, like this:
SELECT
samples.id,
samples.wet_weight / samples.dry_weight - 1 AS…
wizpig64
- 753
- 1
- 5
- 5
65
votes
5 answers
Write differences between varchar and nvarchar
Currently in our SQL Server 2012 database, we're using varchar, and we'd like to change that nvarchar. I've generated a script to do that.
My question is are there any differences in how SQL Server writes to varchar columns vs. nvarchar columns? We…
Chris L
- 941
- 1
- 7
- 10
65
votes
8 answers
Is it possible to quickly create/restore database snapshots with PostgreSQL?
First of all, I'm a developer, not a DBA or sysadmin; please be gentle :)
I'm working on an application workflow where a single user action will trigger complex changes in the database - creating hundreds of records in some tables, updating hundreds…
Zilk
- 1,141
- 2
- 9
- 13
65
votes
6 answers
Why not use a table instead of a materialized view?
I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some parameter. If view is saved as a physical table,…
jrara
- 5,393
- 20
- 58
- 65
65
votes
3 answers
When to use views in MySQL?
When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table?
One reason that I would prefer to use views is that the database schema has been developed by our administrator from within…
David LeBauer
- 3,162
- 8
- 32
- 34
64
votes
8 answers
Restore mysql database with different name
How can we restore mysql database with different name from mysqldump file.
I dont't want to open dump file and edit it. Any other better methods?
Praveen Prasannan
- 1,546
- 6
- 25
- 38
64
votes
1 answer
What event information can I get by default from SQL Server?
I often see questions where people want to know if a certain thing happened, or when it happened, or who performed the action. In a lot of cases, SQL Server just doesn't track this information on its own. For example:
Who last executed stored…
Aaron Bertrand
- 181,950
- 28
- 405
- 624
64
votes
6 answers
About single threaded versus multithreaded databases performance
H2 is a single threaded database with a good reputation regarding performance. Other databases are multi-threaded.
My question is: when does a multi-thread database become more interesting than an single thread database? How many users? How many…
Jérôme Verstrynge
- 1,481
- 5
- 23
- 27
64
votes
4 answers
Which is faster, InnoDB or MyISAM?
How can MyISAM be "faster" than InnoDB if
MyISAM needs to do disk reads for the data?
InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?
jcho360
- 2,009
- 8
- 24
- 31
64
votes
1 answer
What is the meaning of filtered in MySQL explain?
As described here in the MySQL docs:
The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the…
Iman Tumorang
- 695
- 1
- 6
- 12
64
votes
4 answers
Does running pg_dump on live db produce consistent backups?
I have a 3GB database that is constantly modified and I need to make backups without stopping the server (Postgres 8.3).
My pg_dump runs for 5 minutes. What if the data is modified during the process? Do I get consistent backups? I don't want to…
Roman
- 793
- 1
- 6
- 7
64
votes
2 answers
How does ORDER BY FIELD() in MySQL work internally
I understand how ORDER BY clause works and how the FIELD() function works.
What i want to understand is how the both of them work together to sort.
How are the rows retrieved and how is the sort order derived
+----+---------+
| id | name …
itz_nsn
- 806
- 1
- 7
- 7
63
votes
2 answers
MATCH FULL vs MATCH SIMPLE in foreign key constraints
I've noticed the clauses MATCH SIMPLE and MATCH FULL in phpPgAdmin, but I can't find a description in the docs.
The default is MATCH SIMPLE. How do they function?
user32234
63
votes
3 answers
How to make sqlplus output appear in one line?
I have a table with 100 columns. When selecting data in SQL Plus the output wraps, making it difficult to read.
What I'd rather like is either a horizontal scroll bar to appear or somehow send the output to less
I run following statements in SQLPlus…
Kshitiz Sharma
- 3,357
- 9
- 33
- 35
63
votes
2 answers
Cast to date is sargable but is it a good idea?
In SQL Server 2008 the date datatype was added.
Casting a datetime column to date is sargable and can use an index on the datetime column.
select *
from T
where cast(DateTimeCol as date) = '20130101';
The other option you have is to use a range…
Mikael Eriksson
- 22,295
- 5
- 63
- 106