Most Popular
1500 questions
29
votes
2 answers
Querying JSONB in PostgreSQL
I have a table, persons, which contains two columns, an id and a JSONB-based data column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support).
Now, supposed it contains two records:
1, { name:…
Golo Roden
- 422
- 1
- 4
- 9
29
votes
4 answers
Index performance for CHAR vs VARCHAR (Postgres)
In this answer (https://stackoverflow.com/questions/517579/strings-as-primary-keys-in-sql-database) a single remark caught my eye:
Also keep in mind that there's often a very big difference between a
CHAR and a VARCHAR when doing index…
LetMeSOThat4U
- 503
- 1
- 7
- 12
29
votes
1 answer
Cannot locally import UTF-8 encoded SQL database on Mac
I was importing a confirmed UTF-8 encoded SQL database into Sequel Pro on Mac and got thrown this error halfway through:
An error occurred when reading the file, as it could not be read in
the encoding you selected (Autodetect - Unicode…
smokerockspunchcops
- 291
- 1
- 3
- 4
29
votes
2 answers
"Recheck Cond:" line in query plans with a bitmap index scan
This is a spin-off from comments to the previous question:
Postgres 9.4.4 query takes forever
Using PostgreSQL 9.4, there always seems to be a Recheck Cond: line after bitmap index scans in query plans output by EXPLAIN.
Like in the EXPLAIN output…
Erwin Brandstetter
- 175,982
- 27
- 439
- 600
29
votes
1 answer
Why 0 is equal to empty string?
I need some help in finding why the following T-SQL statement returns 1 (true):
SELECT IIF( 0 = '', 1, 0)
I guess someone has change an ANSI option like SET ANSI_NULLS or something else that is causing the behavior.
My issue is that I am joining…
gotqn
- 4,016
- 11
- 48
- 84
28
votes
7 answers
Fastest way to check if InnoDB table has changed
My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance.
My application does about 500-1000 CHECKSUM TABLE…
Jacket
- 540
- 2
- 6
- 13
28
votes
4 answers
The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'
I have a question about XTP_CHECKPOINT.
I'm using SQL Server 2014. I have a database that is in SIMPLE recovery model mode. It is also being replicated.
There are no open transactions. I've run DBCC OPENTRAN and it returns:
"No active open…
user3469285
- 661
- 2
- 10
- 19
28
votes
9 answers
What is the most efficient method of performing the FIZZBUZZ test in SQL Server?
Is there a more efficient method of obtaining a list of numbers from 1 to 49 with a column containing the words FIZZ when the number can be evenly divided by 3, BUZZ when the number can be evenly divided by 5, and FIZZBUZZ when the number can be…
Hannah Vernon
- 70,041
- 22
- 171
- 315
28
votes
3 answers
What is the "Chaos" Isolation level and when should it be used?
ADO.NET documentation shows the possibility of setting the transaction level for a SQL transaction to Chaos. It sounds unpleasant, but if the feature is there, presumably it has some legitimate use.
The SET TRANSACTION ISOLATION LEVEL command in BOL…
MatthewMartin
- 382
- 1
- 3
- 8
28
votes
3 answers
Why would a table use its primary key as a foreign key to itself
Looking through a database, I came across a table that used its primary key as a foreign key to itself.
I've seen that a table can have a foreign key to itself to build a hierarchy structure, but it would use another column to reference the primary…
Aaroninus
- 459
- 1
- 5
- 10
28
votes
3 answers
Is it possible to modify an existing trigger definition in MySQL?
I am wondering if it is possible to update a trigger definition in MySQL. For example, I have a trigger T and I want to add some new functionality to it.
My assumption is that I need to drop and recreate it.
What are the best practices in the…
Alex
- 413
- 2
- 5
- 7
28
votes
3 answers
Are RANK() and DENSE_RANK() deterministic or non-deterministic?
According to official Microsoft BOL DENSE_RANK is nondeterministic (RANK()). But according to Ranking Functions by Itzik Ben-Gan "... the RANK() and DENSE_RANK() functions are always deterministic". Who is right?
What I have found so…
Pavel Nefyodov
- 632
- 8
- 18
28
votes
2 answers
Can you use COUNT DISTINCT with an OVER clause?
I'm trying to improve the performance of the following query:
UPDATE [#TempTable]
SET Received = r.Number
FROM [#TempTable]
INNER JOIN (SELECT AgentID,
RuleID,
…
Kenneth Fisher
- 24,127
- 12
- 61
- 114
28
votes
3 answers
How to ORDER BY typical software release versions like X.Y.Z?
Given a "SoftwareReleases" table:
| id | version |
| 1 | 0.9 |
| 2 | 1.0 |
| 3 | 0.9.1 |
| 4 | 1.1 |
| 5 | 0.9.9 |
| 6 | 0.9.10 |
How do I produce this output?
| id | version |
| 1 | 0.9 |
| 3 | 0.9.1 |
| 5 | 0.9.9 …
Chris Betti
- 477
- 1
- 5
- 11
28
votes
4 answers
Giving file system access to the SQLSERVERAGENT virtual account
I'm trying to grant the NT Service\SQLSERVERAGENT Windows account file system access, based on the marked answer in this question. I believe it's a virtual service account, and it does not show up in the control panel -> user accounts area.
How do I…
Ryan
- 493
- 2
- 5
- 8