Most Popular
1500 questions
21
votes
3 answers
Common Table Expression (CTE) benefits?
From msdn :
Unlike a derived table, a CTE can be self-referencing and can be
referenced multiple times in the same query.
I'm using CTEs quite a lot, but I've never thought deeply about the benefits of using them.
If I reference a CTE multiple…
Royi Namir
- 1,223
- 3
- 13
- 26
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
3 answers
What are the best practices regarding lookup tables in relational databases?
Lookup tables (or code tables, as some people call them) are usually a collection of the possible values that can be given for a certain column.
For example, suppose we have a lookup table called party (meant to store information about political…
Nishant
- 869
- 2
- 13
- 20
21
votes
2 answers
PostgreSQL: Pass table as argument in function
I am discovering TYPE in PostgreSQL. I have a TABLE TYPE that some table must respect (interface). For example:
CREATE TYPE dataset AS(
ChannelId INTEGER
,GranulityIdIn INTEGER
,GranulityId INTEGER
,TimeValue TIMESTAMP
,FloatValue…
jlandercy
- 357
- 1
- 4
- 10
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
- 6
- 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
- 18
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,582
- 7
- 24
- 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
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
- 32,975
- 27
- 61
- 64
21
votes
6 answers
How to use COUNT with multiple columns?
How to use multiple columns with a single COUNT?
Assume that there is a table demo with these data:
id | col1 | col2 |
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
1 |'alice' | 'book1'|
2 |'bob' | 'book1'|
3 |'alice' | 'book2'|
4 …
andy
- 331
- 1
- 2
- 7
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
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,967
- 5
- 45
- 62
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,761
- 4
- 26
- 57
21
votes
2 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
- 11
- 33
- 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,041
- 22
- 171
- 315