Most Popular
1500 questions
31
votes
5 answers
Why use an int as a lookup table's primary key?
I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string).
I understand that using a nvarchar(50) rather than an int would use way more…
Jaco Briers
- 413
- 1
- 4
- 6
31
votes
4 answers
tuning postgresql for large amounts of ram
I have two identical servers (in terms of hardware), they are both standard installations of windows server 2008 r2, with minimal software installed (basically my code and required stuff like jvm etc).
On the one server, I am running sql server…
user85116
- 419
- 1
- 4
- 3
31
votes
2 answers
Varchar(max) field cutting off data after 8000 characters
I have a field to store some data, the field is declared as varchar(max). To my understanding this should be storing 2^31 - 1 characters but when I enter some content over 8000 chars it cuts the rest off.
I have verified that all the data is…
webnoob
- 605
- 1
- 6
- 16
31
votes
3 answers
Eliminate Key Lookup (Clustered) operator that slows down performance
How can I eliminate a Key Lookup (Clustered) operator in my execution plan?
Table tblQuotes already has a clustered index (on QuoteID) and 27 nonclustered indexes, so I am trying not to create any more.
I put the clustered index column QuoteID in…
Serdia
- 687
- 1
- 8
- 15
31
votes
2 answers
Subqueries' aliases same as main queries' aliases
I have an SQL query whose aliases are the same as some of its subquery's aliases.
For example:
select *
from ROOM r
where ...
(
select *
from ROAD r
where ...
)
This works fine, as the…
IcySnow
- 517
- 2
- 6
- 7
31
votes
2 answers
PostgreSQL UPSERT issue with NULL values
I'm having an issue with using the new UPSERT feature in Postgres 9.5
I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can be nullable.
Below I have created a smaller…
Shaun McCready
- 413
- 1
- 4
- 6
31
votes
4 answers
How do I get a list of all the partitioned tables in my database?
How do I get a list of all the partitioned tables in my database?
Which system tables/DMVs should I be looking at?
RK Kuppala
- 2,437
- 1
- 22
- 24
31
votes
2 answers
UPSERT with ON CONFLICT using values from source table in the UPDATE part
Given:
CREATE TABLE A (
PK_A INT8 NOT NULL,
A INT8,
PRIMARY KEY (PK_A)
);
CREATE TABLE B (
PK_B INT8 NOT NULL,
B INT8,
PRIMARY KEY (PK_B)
);
This query:
insert into table_b (pk_b, b)
select pk_a,a from table_a
on conflict (b) do update set…
Tony Indrali
- 311
- 1
- 3
- 3
31
votes
5 answers
Archiving of old data
We're currently running into some performance problems since our database is getting too big. There are data stored from the last 10 years and I don't see a reason why the data older than 2 years have to be stored in the same tables as the new…
xeraphim
- 597
- 2
- 5
- 11
31
votes
2 answers
Why is :r SQLCMD command marked as wrong in Post Deployment Script?
I have worked a couple of times with post deployment scripts and always, intuitively used the build action "PostDeploy", because that is what it is.
Now for the first time I try to follow the built-in instruction from the script's template to use…
Magier
- 4,787
- 8
- 43
- 87
31
votes
3 answers
Constraint to enforce "at least one" or "exactly one" in a database
Say we have users and each user can have multiple email addresses
CREATE TABLE emails (
user_id integer,
email_address text,
is_active boolean
)
Some sample rows
user_id | email_address | is_active
1 | alice@bar.com | t
1 …
Kevin Burke
- 805
- 2
- 9
- 19
31
votes
2 answers
I/O requests taking longer than 15 seconds
Usually our weekly full backups finish in about 35 minutes, with daily diff backups finishing in ~5 minutes. Since tuesday the dailies have taken almost 4 hours to complete, way more than should be required. Coincidentally, this started happening…
Mark S. Rasmussen
- 1,465
- 1
- 14
- 20
31
votes
6 answers
SQL Server compatibility with New TLS Standards
The major browsers are moving beyond SSL3.0 and TLS1.0 .
The PCI Security Council has declared an end-of-life date for these
protocols to be considered sufficiently strong encryption.
We need to move away from these protocols, to use newer and…
Mark Goldfain
- 413
- 1
- 4
- 7
31
votes
2 answers
Automatic aging-out (deletion) of old records in Postgres
Does Postgres have any features to support aging-out old records?
I want to use Postgres for logging, as a sort of queue, where records (log events) older than two weeks are automatically deleted.
Basil Bourque
- 10,806
- 20
- 59
- 92
30
votes
2 answers
Does Postgres preserve insertion order of records?
For example when I'm using query which returns record ids
INSERT INTO projects(name)
VALUES (name1), (name2), (name3) returning id;
Which produce output:
1
2
3
Will this ids point to corresponding inserted values?
1 -> name1
2 -> name2
3 -> name3
Sergey
- 403
- 1
- 4
- 4