Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
20
votes
3 answers

PostgreSQL: difference between collations 'C' and 'C.UTF-8'

In PostgreSQL, what is the difference between collations C and C.UTF-8? Both show up in rows of pg_collation. Is it perhaps the case that C.UTF-8 is the same as C with encoding UTF-8 regardless or what the actual encoding of a database is?
rookie099
  • 358
  • 1
  • 2
  • 9
20
votes
2 answers

How do you enable SQL Server 2019's result set caching?

SQL Server 2019 CTP 2.3 added a few new columns to sys.databases, including is_result_set_caching_on: It's not documented yet in the What's New in SQL Server 2019, nor in the ALTER DATABASE page. I tried using the same syntax as Accelerated…
Brent Ozar
  • 42,952
  • 47
  • 220
  • 375
20
votes
2 answers

T-SQL query using completely different plan depending on number of rows I'm updating

I have a SQL UPDATE statement with a "TOP (X)" clause, and the row I'm updating values in has about 4 billion rows. When I use "TOP (10)", I get one execution plan that executes almost instantly, but when I use "TOP (50)" or larger, the query never…
SqlRyan
  • 1,228
  • 2
  • 17
  • 24
20
votes
2 answers

Accent Sensitive Sort

Why do these two SELECT statements result in a different sort order? USE tempdb; CREATE TABLE dbo.OddSort ( id INT IDENTITY(1,1) PRIMARY KEY , col1 NVARCHAR(2) , col2 NVARCHAR(2) ); GO INSERT dbo.OddSort (col1, col2) VALUES (N'e',…
Aram
  • 203
  • 1
  • 5
20
votes
2 answers

MongoDB terminates when it runs out of memory

I have the following configuration: a host machine that runs three docker containers: MongoDB Redis A program using the previous two containers to store data Both Redis and MongoDB are used to store huge amounts of data. I know Redis needs to…
Simone Bronzini
  • 303
  • 1
  • 2
  • 6
20
votes
2 answers

Can't update "CO2" to "CO₂" in table row

Given this table: CREATE TABLE test ( id INT NOT NULL, description NVARCHAR(100) COLLATE Modern_Spanish_CI_AS NOT NULL ); INSERT INTO test (id, description) VALUES (1, 'CO2'); I've realised I can't fix a typographic issue: SELECT * FROM…
Álvaro González
  • 1,079
  • 5
  • 16
  • 30
20
votes
3 answers

What is it called when you search the middle of a string instead of the beginning?

I am trying to polish up my vocabulary to better communicate with my fellow developers. We have several places in the site where we are debating if we should search for a string from the beginning 'running%' vs anywhere in the string '%running%. I…
danielson317
  • 343
  • 2
  • 10
20
votes
3 answers

How can I return multiple rows of records in PL/pgSQL

I am trying to return multiple records using RECORD data type, is there a way I can append to RECORD and add/append a new value with each iteration to this RECORD. that is, I want to append to rec so that rec becomes a set of rows when the loop is…
hky404
  • 313
  • 1
  • 2
  • 8
20
votes
2 answers

Make Postgres database temporarily read-only (for performing volume snapshots)

The PostgreSQL built-in backup mechanism isn't always very suitable. Sometimes, you want to put the application in a quiescent state, because it has external data with which you want to backup at the same time you back up the PG data. But the only…
Otheus
  • 594
  • 1
  • 3
  • 13
20
votes
2 answers

Restore database excluding FILESTREAM data

Context We are developing a system with a large-ish database in the bottom. It is an MS SQL database running on SQL Server 2008 R2. The total size of the database is about 12 GB. Out of these, approximately 8.5 GB is in a single table BinaryContent.…
Julian
  • 303
  • 1
  • 2
  • 8
20
votes
5 answers

SQL Server Error 8632 due to over 100,000 entries in WHERE clause

My problem (or at least the error message) is very similar to query processor ran out of internal resources - extremely long sql query. My customer is working with an SQL select-query, containing a where-clause with exactly 100,000 entries. The…
Dominique
  • 521
  • 1
  • 5
  • 19
20
votes
2 answers

What's the overhead of updating all columns, even the ones that haven't changed

When it comes to updating a row, many ORM tools issue an UPDATE statement that sets every column associated to that particular entity. The advantage is that you can easily batch the update statements since the UPDATE statement is the same no matter…
Vlad Mihalcea
  • 907
  • 3
  • 9
  • 22
20
votes
2 answers

Is commit necessary after DML operation in Function/Procedure?

I wonder to know if it is necessary to write commit after insert/delete/update in function/procedure? Example: create or replace function test_fun return number is begin delete from a; return 0; end; or procedure create or replace procedure…
kupa
  • 1,707
  • 14
  • 39
  • 55
20
votes
2 answers

DEFAULT CONSTRAINT, worth it?

I usually design my databases following next rules: Nobody else than db_owner and sysadmin have access to the database tables. User roles are controlled at application layer. I usually use one db role to grant access to the views, stored…
McNets
  • 23,749
  • 10
  • 48
  • 88
20
votes
4 answers

Database design: how to handle the "archive" problem?

I'm pretty sure a lot of applications, critical applications, banks and so on do this on a daily basis. The idea behind all that is: all the rows must have a history all links must stay coherent it should be easy to make requests to get "current"…
Olivier Pons
  • 327
  • 2
  • 10
1 2 3
…
99 100