Most Popular
1500 questions
25
votes
1 answer
SQL-Server: Why do we use FILE = 1 in scripts for restoring a database
Recently when I came across the script for restoring a database, I got a doubt on why we have to use "FILE = 1"? Can't we restore a database without that statement!? Basically, what it is used for?
Sunil Kumar Machineni
- 251
- 1
- 3
- 3
25
votes
2 answers
Can two sessions create #temp tables with the same name?
I am creating a temporary table (#myTable) and using a cursor. Does this create a problem when concurrent users are accessing the cursor through my application? Does it allow me to create separate temp tables with the same name?
Following is the…
sujith karivelil
- 567
- 1
- 6
- 14
25
votes
3 answers
SQL Server shows database in recovery
Today, after a power failure, one database (with Recovery: full) shows "In Recovery" in SSMS. So:
myDatabase (In recovery) (database status: recovery, Shutdown)
After finish, the "recovery process" the database shows the name myDatabase without…
Andrés Falcón
- 367
- 1
- 3
- 6
25
votes
4 answers
How to exclude NULL values inside CONCAT MySQL?
If I have this - tadd is the Address table:
CONCAT(tadd.street_number, ' ',
tadd.street_name,', ',
tadd.apt_number,', ',
tadd.city,', ',
tadd.postal_code,', ',
tadd.country) AS…
ed-ta
- 453
- 2
- 6
- 10
25
votes
1 answer
Rolling sum / count / average over date interval
In a database of transactions spanning 1,000s of entities over 18 months, I would like to run a query to group every possible 30-day period by entity_id with a SUM of their transaction amounts and COUNT of their transactions in that 30-day period,…
tufelkinder
- 353
- 1
- 3
- 7
25
votes
5 answers
Are junction tables a good practice?
Say I have a large table that holds the user's info and another table that holds several locations. Then I use another table that holds the user_id and the location_id.
In order to retrieve the data I have to use Left Join query. Doesn't that make…
Marios Frixou
- 373
- 1
- 3
- 6
25
votes
2 answers
Is there a simple way in PL/pgSQL to check if a query returned no result?
I'm currently experimenting a bit with PL/pgSQL and want to know if there is a more elegant way to do something like this:
select c.data into data from doc c where c.doc_id = id and c.group_cur > group_cur order by c.id desc limit 1;
EXCEPTION
…
icefex
- 417
- 1
- 5
- 9
25
votes
4 answers
Grant permissions to run a SQL Server job
I have a job on my SQL Server 2005 instance that I want to allow any database user to run.
I'm not worried about security, since the input to the job's actual work comes from a database table. Just running the job, without adding records to that…
Shahar Mosek
- 403
- 1
- 4
- 6
25
votes
3 answers
How to reinitialise /var/lib/mysql files?
Due to a mishap I deleted the entire /var/lib/mysql directory. Since the database did not contain anything important, I do not want to go through the hassle of restoring it from an old backup, but instead create the directory structure from scratch.…
Konrad Gajewski
- 353
- 1
- 4
- 10
25
votes
1 answer
How to atomically replace table data in PostgreSQL
I want to replace the entire contents of a table, without affecting any incoming SELECT statements during the process.
The use case is to have a table which stores mailbox information that is regularly extracted, and needs to be stored in a…
Clarkey
- 425
- 2
- 5
- 6
24
votes
1 answer
What exactly can SQL Server 2014 execute in batch mode?
When a columnstore index is being used in a query SQL Server is able to use batch mode. Documentation is thin on what can run in batch mode and what can't. Please look at the following (motivating) query plan where a surprising number of things…
usr
- 7,330
- 5
- 32
- 58
24
votes
1 answer
What limits the number of connections?
As per https://devcenter.heroku.com/articles/heroku-postgres-legacy-plans the connection limit is 500
As per https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server "Generally, PostgreSQL on good hardware can support a few hundred…
Neil McGuigan
- 8,423
- 4
- 39
- 56
24
votes
2 answers
How to catch and handle only specific Oracle exceptions?
From this and this i guess, that there is no predefined Named System Exceptions for ORA-00955.
How can I rewrite the following to catch only the error ORA-00955?
begin
EXECUTE IMMEDIATE 'CREATE SEQUENCE S_TEST START WITH 1 INCREMENT BY…
bernd_k
- 12,211
- 23
- 75
- 111
24
votes
1 answer
Grant access to all tables of a database
I recently wanted to share regular access rights with one user of a server and I realized that a simple CREATE USER and GRANT ALL ON DATABASE commands didn't let him run a simple SELECT on the data.
I would like to grant rights to all tables from a…
d33tah
- 389
- 1
- 2
- 10
24
votes
6 answers
DELETE command not completing on 30,000,000 row table
I have inherited a database and am looking to clean and speed it up. I have a table that contains 30,000,000 rows, many of which are junk data inserted due to an error on behalf of our programmer. Before I add any new, more optimized indexes, I…
bafromca
- 551
- 1
- 3
- 9