Most Popular
1500 questions
35
votes
7 answers
Copy complete structure of a table
Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say:
select * into dbo.table2 from dbo.table1;
This is just a simple copy of the table; all of the indexes / constraints are…
jcho360
- 1,999
- 8
- 23
- 31
35
votes
4 answers
CPU clock speed versus CPU core count - higher GHz, or more cores for SQL Server?
We are beginning to provision a set of physical servers for a virtual cluster of SQL Server 2016 nodes within VMware. We will be utilizing Enterprise Edition licenses.
We plan on setting up 6 nodes, but there is a bit of a debate on what the ideal…
PicoDeGallo
- 1,564
- 1
- 20
- 30
35
votes
2 answers
DBA first day in a new job - check backups and security - How? what else should be checked?
Generally when I start in a new environment, I tend to check where are the backups, when the last full was taken, when was the last restore applied and I check the security too.
The way I do this is via T-SQL.
Check the backups
;with Radhe…
Marcello Miorelli
- 16,170
- 52
- 163
- 300
35
votes
2 answers
'Column reference is ambiguous' when upserting element into table
I am using PostgreSQL as my database. And I need to create an entry in the database, and if it's already exists, just update its fields, but one of the fields should be updated only if it's not set.
I've used info from this question:…
serge1peshcoff
- 525
- 2
- 6
- 9
35
votes
2 answers
How to store one-byte integer in PostgreSQL?
In PostgreSQL documentation, it is said that integer data types can be stored in either two-, four- or eight-byte space. One of the columns of a table in my database contains a one-byte integer value and I want it to be stored in a one-byte data…
ukll
- 781
- 1
- 7
- 13
35
votes
2 answers
Find Queries that Are Causing Waits
Given a certain kind of wait, how do you find which queries are causing those waits safely on a production SQL 2008 R2 Server?
In this particular case I am wondering about async_network_io.
Kyle Brandt
- 2,345
- 8
- 29
- 37
35
votes
8 answers
What is the difference between an INNER JOIN and an OUTER JOIN ?
I am new to SQL and wanted to know what is the difference between those two JOIN types?
SELECT *
FROM user u
INNER JOIN telephone t ON t.user_id = u.id
SELECT *
FROM user u
LEFT OUTER JOIN telephone t ON t.user_id = u.id
When should I use one or…
Julien
- 509
- 1
- 4
- 7
35
votes
5 answers
How do you test for race conditions in a database?
I try to write database code to make sure that it's not subject to race conditions, to make sure that I've locked the correct rows or tables. But I often wonder: Is my code correct? Is it possible to force any existing race conditions to manifest? I…
xenoterracide
- 2,851
- 5
- 30
- 33
35
votes
4 answers
how to track progress of a large postgres dump
Is there a way to see the progress of a pg_dump operation on a big db (ie > 1GB)? adding the -v option just dumps text on the screen, but doesn't give me much meaningful tracking information.
abbood
- 453
- 1
- 4
- 7
35
votes
3 answers
Why does this query work?
I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c.
Why does this query not return an exception?
select * from table_a where name in (select name from table_b);
From what I understand, Oracle sees this as
select * from…
eagerMoose
- 523
- 1
- 5
- 11
35
votes
1 answer
How do I set a SQL Server Unicode / NVARCHAR string to an emoji or Supplementary Character?
I want to set a Unicode string variable to particular character based on its Unicode code point.
I want to use a code point beyond 65535, but the SQL Server 2008 R2 database has a collation of SQL_Latin1_General_CP1_CI_AS.
According to Microsoft's…
Riley Major
- 1,893
- 1
- 19
- 33
35
votes
6 answers
PostgreSQL: How to create full copy of database schema in same database?
How I can copy my public schema into the same database with full table structure, data, functions, fk, pk and etc.
My version of Postgres is 8.4
P.S. I need to copy schema NOT database
sigra
- 453
- 1
- 4
- 5
35
votes
4 answers
Double entry bookkeeping database design
I'm creating accounting software. I need to enforce double entry bookkeeping. I have the classical problem of one row per transaction versus two rows.
Let's take an example and see how it would be implemented in both scenarios.
Consider account Cash…
Dmitry Kudryavtsev
- 509
- 1
- 5
- 7
34
votes
2 answers
How do you show SQL executing on an Oracle database?
How can you show the SQL that is currently executing on an oracle db?
Extra information that would be useful would include user, session id etc.
Clarkey
- 1,063
- 3
- 12
- 19
34
votes
2 answers
Timeseries: SQL or NoSQL?
I don't care about the general differences between SQL and NoSQL (or their traditional differences).
I am currently looking at altering the storage of our internal time series. They all contain financial data from a number of different sources.…
Nicolas
- 443
- 1
- 5
- 6