Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
40
votes
1 answer

Postgres function assign query results to multiple variables

I need to assign values to 2 variable as below in Postgres function. a := select col1 from tbl where ... b := select col2 from tbl where ... How can I assign 2 values to 2 variables in one line command? Like a,b := select col1,col2 from tbl where…
Xianlin
  • 567
  • 2
  • 6
  • 10
40
votes
1 answer

SQL Server: deadlocked on lock communication buffer resources

What could be possible reason for this deadlock type? (not deadlock in general) Lock communication buffer resources Is this indicated system is low in memory and buffers count ran out of limit? Detailed Error: Transaction (Process ID 59) was…
usman shaheen
40
votes
2 answers

Check existence with EXISTS outperform COUNT! ... Not?

I've often read when one had to check existence of a row should always be done with EXISTS instead of with a COUNT. Yet in several recent scenarios I've measured a performance improvement when using count. The pattern goes like this: LEFT JOIN ( …
Serge
40
votes
4 answers

Formatting T-SQL in SSMS 2012

According to this Microsoft document: http://msdn.microsoft.com/en-us/library/ms174205.aspx I am supposed to be able to use ctrl+K then ctrl+D to format my SQL documents in SQL Server Management Studio 2012 but when I use that combo I get the…
user11512
40
votes
6 answers

Error Code 1117 Too many columns; MySQL column-limit on table

I have a table with 1699 columns and when I'm trying to insert more columns I get, Error Code: 1117. Too many columns In this table I have only 1000 rows. For me the most important thing is the number of columns. Are there any limitations on the…
Mokus
  • 1,017
  • 4
  • 15
  • 17
40
votes
7 answers

List all permissions for a given role?

I've searched around all over and haven't found a conclusive answer to this question. I need a script that can give ALL permissions for an associated role. Any thoughts, or is it even possible? This gets me CLOSE - but I can't seem to flip it…
elgabito
  • 831
  • 1
  • 7
  • 8
40
votes
2 answers

Optimising plans with XML readers

Executing the query from here to pull the deadlock events out of the default extended events session SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'), '',…
Martin Smith
  • 84,644
  • 15
  • 245
  • 333
40
votes
4 answers

Why does MySQL not have hash indices on MyISAM or InnoDB?

I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up with that?
Alex
40
votes
1 answer

intermittent MySQL crashes with error "Fatal error: cannot allocate memory for the buffer pool"

Added on edit, 2013-05-29: Because this is a long question and discussion, here's a short summary of the question and the solution. I had problems running MySQL and Apache on a small Linux server (1 GB of memory). Apache kept demanding more…
Teemu Leisti
  • 503
  • 1
  • 4
  • 9
40
votes
3 answers

SQL Server's "Total Server Memory" consumption stagnant for months with 64GB+ more available

I have run into an odd issue where SQL Server 2016 Standard Edition 64-bit has seemed to have capped itself off at precisely half of the total memory allocated towards it (64GB of 128GB). The output of @@VERSION is: Microsoft SQL Server 2016…
PicoDeGallo
  • 1,564
  • 1
  • 20
  • 30
40
votes
3 answers

pgAdmin 4 version 3 fails to open a second time

I just installed pgAdmin 4 version 3.0.0 for macOS Sierra, replacing the pgAdmin that came bundled with Postgres 10.3 running locally on the same Mac. I opened the pgAdmin 4.app app icon, which caused Safari browser to come to the front with a tab…
Basil Bourque
  • 10,806
  • 20
  • 59
  • 92
40
votes
6 answers

How do you tune MySQL for a heavy InnoDB workload?

Assuming a production OLTP system with predominantly InnoDB tables What are the common symptoms of a mistuned/misconfigured system? What configuration parameters do you most commonly change from their defaults? How do you spot potential bottlenecks…
Riedsio
  • 1,377
  • 1
  • 14
  • 18
40
votes
4 answers

LocalDB v14 creates wrong path for mdf files

Recently, I upgraded LocalDB from version 13 to 14 using the SQL Server Express installer and this instruction. After the installation, I stopped the existing default instance (MSSQLLOCALDB) of version 13 and created a new one, which automatically…
feO2x
  • 503
  • 1
  • 4
  • 6
40
votes
3 answers

Unexpected scans during delete operation using WHERE IN

I've got a query like the following: DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN ( SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL ) tblFEStatsBrowsers has got 553 rows. tblFEStatsPaperHits…
Mark S. Rasmussen
  • 1,465
  • 1
  • 14
  • 20
40
votes
6 answers

Does `COUNT` discard duplicates?

My professor taught me this SQL statement: SELECT COUNT(length) FROM product will return 2 with the following dataset: product |id | length | code | |-------------------| | 1 | 11 | X00 | | 2 | 11 | C02 | | 3 | 40 | A31 | She…
Jules Lamur
  • 511
  • 1
  • 4
  • 7
1 2 3
…
99 100