Most Popular
1500 questions
19
votes
2 answers
SQL Server VARCHAR Column Width
Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255), when VARCHAR(30) will probably do.
I consistently see agreement that there's a…
Eric J.
- 458
- 3
- 12
19
votes
9 answers
Version control for database objects
The database our developers are working on is too large (have a lot of database objects). We have to control db objects changes (change management). Our company cannot have a person who would be responsible for db changes only. So we need a source…
garik
- 6,572
- 10
- 41
- 56
19
votes
2 answers
Interview SQL question
Given a table 'employees'
employee_id | salary | department_id
-------------+--------+---------------
Only using SQL find all the variants of employee-transfers from one department to another, so that average salary in both 'departure' and…
Alexander
- 353
- 2
- 6
19
votes
4 answers
Should the index on an identity column be nonclustered?
For a table with identity column, should a clustered or non-clustered PK/unique index be created for the identity column?
The reason is other indexes will be created for queries. A query which uses a nonclustered index (on a heap) and returns…
u23432534
- 1,495
- 4
- 20
- 30
19
votes
5 answers
How to examine PostgreSQL server's SSL certificate?
Suppose there is a PostgreSQL server running and it has SSL enabled. Using "standard" Linux and PostgreSQL tools, how can I examine its SSL certificate?
I'm hoping for output similar to what you would get from running openssl x509 -text .... And…
csd
- 540
- 1
- 4
- 11
19
votes
2 answers
Unexpected gaps in IDENTITY column
I'm trying to generate unique purchase order numbers that start at 1 and increment by 1. I have a PONumber table created using this script:
CREATE TABLE [dbo].[PONumbers]
(
[PONumberPK] [int] IDENTITY(1,1) NOT NULL,
[NewPONo] [bit] NOT NULL,
…
Ege Ersoz
- 342
- 1
- 3
- 9
19
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
- 313
- 1
- 2
- 6
19
votes
2 answers
Naming conflict between function parameter and result of JOIN with USING clause
Given this setup in current Postgres 9.4 (from this related question):
CREATE TABLE foo (ts, foo) AS
VALUES (1, 'A') -- int, text
, (7, 'B');
CREATE TABLE bar (ts, bar) AS
VALUES (3, 'C')
, (5, 'D')
, (9, 'E');
db<>fiddle here…
Erwin Brandstetter
- 156,199
- 19
- 384
- 527
19
votes
2 answers
Behavior of Varchar with spaces at the end
When I use a Varchar with spaces it ignores the spaces at the end.
ex:
declare @X varchar(50)
This...
set @X= 'John'
...is the same as...
set @X= 'John '
It considers these to be equal. How can I cause the system to recognize these…
AMH
- 333
- 1
- 2
- 7
18
votes
1 answer
Understanding a notification system
I have been looking into how to build a notification system on SE and elsewhere and found myself drawn to the solution that is the accepted answer here: https://stackoverflow.com/questions/9735578/building-a-notification-system
which uses this…
user45623
- 298
- 1
- 2
- 12
18
votes
3 answers
How to upgrade PostgreSQL from version 8.4 to 9.4?
I want to upgrade my PostgreSQL from version 8.4 to 9.4.
The documentation is not very clear to me.
Will I lose my old databases if I do the upgrade?
How can I backup my old databases if I am to lose them after the upgrade?
How can I upgrade my…
Alex Jolig
- 549
- 1
- 5
- 12
18
votes
1 answer
Postgres: check disk space taken by materialized view?
I know how to check the size of indexes and tables in Postgres (I'm using version 9.4):
SELECT
relname AS objectname,
relkind AS objecttype,
reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_class
WHERE…
Richard
- 323
- 1
- 4
- 10
18
votes
3 answers
Automating failover in PostgreSQL 9.1
How does one setup two identical servers for automatic failover in PostgreSQL 9.1.
OS
Centos 5
PostgreSQL 9.1 compiled from source
The postgres user account exists on both machines and has a ssh passwordless key to connect to both machines. …
Craig Efrein
- 9,508
- 12
- 53
- 94
18
votes
1 answer
Empty Strings: Why or when is '' equal to ' '?
Who can explain why
select case when '' = ' ' then 1 else 0 end, LEN(''), LEN(' '), DATALENGTH(''), DATALENGTH(' ');
yields
----------- ----------- ----------- ----------- -----------
1 0 0 0 1
The funny…
bernd_k
- 11,821
- 23
- 73
- 108
18
votes
6 answers
Securing DB passwords
Looking at the structure of most PHP/MySQL-based websites I've seen, it appears that it's not terribly difficult to discern the database password if you dig a bit, as there's invariably a setup or configuration file someplace that stores the…
Kaji
- 937
- 1
- 9
- 16