Most Popular
1500 questions
21
votes
3 answers
Large (>22 trillion items) geospatial dataset with rapid (<1s) read query performance
I'm in the process of designing a new system for a large geospatial data set that will require rapid read query performance. Therefore I want to see if anyone thinks it is possible or has experience/advice about suitable DBMSs, data structure, or…
Azwok
- 395
- 2
- 10
21
votes
8 answers
Why would a primary key value change?
I have been researching the concept of ROWGUID's recently and came across this question. This answer gave insight, but has led me down a different rabbit hole with the mention of changing the primary key value.
My understanding has always been…
5crammed
- 321
- 2
- 3
- 8
21
votes
3 answers
Implementing subtype of a subtype in type/subtype design pattern with mutually exclusive subclasses
Introduction
In order for this question to be useful for future readers I will use the generic data model to illustrate the problem I face.
Our data model consists of 3 entities, which shall be labeled as A, B and C. In order to keep things simple,…
AlwaysLearningNewStuff
- 575
- 1
- 7
- 19
21
votes
14 answers
Capitalize only the first letter of each word of each sentence in SQL Server
I want to capitalize only the first letter of each word of each sentence in a SQL column.
For example, if the sentence is:
'I like movies'
then I need the output:
'I Like Movies'
Query:
declare @a varchar(15)
set @a = 'qWeRtY kEyBoArD'
select…
Marin Mohanadas
- 478
- 4
- 7
- 19
21
votes
5 answers
Is there a concept of an "include file" in SQL Server?
I have a set of scripts that need to be run in a certain order. I would like to create a "master file" that lists all of the other files and their correct order. Basically like an include file from C++ or ASP/VBScript.
Jonathan Allen
- 3,612
- 7
- 25
- 25
21
votes
5 answers
SQL: What is slowing down INSERTs if not CPU or IO?
We have a database for a product that is write-heavy. We just bought a new server machine with a SSD to help. To our surprise, the insertions were not faster than on our old machine with much slower storage. During benchmarking we noticed that the…
Djof
- 313
- 1
- 2
- 7
21
votes
2 answers
psql: FATAL: Peer authentication failed for user
I just installed PostgreSQL 9.4 on Ubuntu 15.10.
I created a user with createuser -P myuser
I created a database with createdb -O myuser mydatabase
I edited pg_hba.conf and added local mydatabase myuser md5
I restarted PostgreSQL with sudo service…
Daniel
- 327
- 1
- 2
- 7
21
votes
6 answers
Are there any good and free tools for managing a PostgreSQL database?
Before, I have used phpmyadmin to manage an MySQL database, but now I would like to manage a PostgreSQL database. The PostgreSQL database is on a server, but not a webserver, so I don't use PHP.
Are there any good and free tools for managing a…
Jonas
- 33,945
- 27
- 62
- 64
21
votes
2 answers
Implementation of a many-to-many relationship with total participation constraints in SQL
How should I implement in SQL the scenario depicted in the following Entity-Relationship diagram?
As it is shown, every A entity type occurrence must be related to at least one B counterpart (indicated by the double connecting lines), and vice…
John
- 375
- 1
- 2
- 7
21
votes
4 answers
Effect of an index on update statements where update column is not in an index
I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute.
While tuning my database to improve performance, I keep coming across this situation that seems to contradict…
Ryan
- 335
- 1
- 2
- 7
21
votes
5 answers
Why does this query become drastically slower when wrapped in a TVF?
I have a fairly complex query which runs in just a few seconds on its own, but when wrapped in a table-valued function, it's far slower; I've not actually let it finish, but it's run for up to ten minutes without ending. The only change is…
Jon of All Trades
- 5,987
- 7
- 48
- 63
21
votes
2 answers
Why must TVPs be READONLY, and why can't parameters of other types be READONLY
According to this blog parameters to a function or a stored procedure are essentially pass-by-value if they aren't OUTPUT parameters, and essentially treated as a safer version of pass-by-reference if they are OUTPUT parameters.
At first I thought…
Erik
- 4,833
- 4
- 28
- 57
21
votes
3 answers
Keyboard shortcut to execute statement at cursor in pgAdmin
In MySQL Workbench, one can use the keyboard shortcut Ctrl + ENTER to execute the statement at cursor (delineated with semi colons).
Is there a similar shortcut in pgAdmin?
For SQL queries that span on only one line, I use a voice command in Dragon…
Franck Dernoncourt
- 2,083
- 13
- 34
- 52
21
votes
4 answers
What's up with the collation of some columns in sys.databases?
I'm attempting to run an UNPIVOT on various columns contained in sys.databases across various versions of SQL Server, ranging from 2005 to 2012.
The UNPIVOT is failing with the following error message:
Msg 8167, Level 16, State 1, Line 48
The type…
Hannah Vernon
- 70,928
- 22
- 177
- 323
21
votes
2 answers
PostgreSQL LIKE query on ARRAY field
Is there any way to have a Postgres LIKE query on a ARRAY field?
Currently I want something like that:
SELECT * FROM list WHERE lower(array_field) LIKE '1234%'
Currently lower is not needed that much. However it should find ONE matching field…
Christian Schmitt
- 443
- 2
- 5
- 13