Databases - TopAnswers

TopAnswers Databases

टेक्-मराठी

db fiddle, Markdown export, Firebird

dbfiddle firebird

Arioch Arioch

External verification of `SIGNBYCERT()` signatures from SQL Server 2016 3

sql-server-2016

NReilingh NReilingh

Why do some rows returned by sys.dm_exec_query_profiles have “???” for the physical operator name?

sql-server-2016

Joe Obbish Joe Obbish

13 [Batch mode adapters](https://dba.stackexchange.com/questions/97650/what-exactly-can-sql-server-2014-execute-in-batch-mode) (places in a query plan in which row processing switches to batch processing or the other way around) show up as `???` in the DMV with a `thread_id` of 0. However, the example query doesn't use batch processing so that isn't the cause here.

Joe Obbish

How do I write "proper" cursors? 1

sql-server

Hannah Hannah

9 `CURSOR_STATUS` provides a nice way of ensuring the cursor is ready and contains rows.

Hannah

Why does MySQL GROUP BY conflict with the SQL Standards?

mysql

rahularyansharma rahularyansharma

6 > Standard SQL would reject your query because you **can not SELECT non-aggregate fields** that are **not part of the GROUP BY clause** in an aggregate query

ypercubeᵀᴹ

2 > Is MySQL breaking the standard by allowing this? How?

Quassnoi

1 **Short answer**

Johan

Cursoring over sys.databases skips databases 8

sql-server-2016

searle1986 searle1986

16 `sys.databases` is a complex view. On SQL Server 2016 it is:

Paul White

JSONB: text contains array value?

postgresql

Anonymous 1800 Anonymous 1800

3 The expression `attributes->>'owning_groups'`

Truilus

Breaking infinite loop in recursive CTE 25

sql-server

artaxerxe artaxerxe

8 Jack's solution is good for scenarios where loops can only be encountered at the beginning of traversing the hierarchies.

Andriy M

4 With a slightly simplified case:

Jack Douglas

SELECT TOP 1 from a very large table on an index column is very slow, but not with reverse order (“desc”)

sql-server-2014

TiffanyP TiffanyP

4 The Clustered Index Scan shows 423,723 logical reads to return the first row, taking 1926 ms: [![NUTS](https://i.stack.imgur.com/1AGv4.png)][1] This seems rather a lot to locate the first row in index order. Most likely your **ghost cleanup** task is running a long way behind, or has stopped. You should check the `ghost_record_count` for the clustered index in [`sys.dm_db_index_physical_stats`](https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql) and monitor changes over time. The **ordered scan** from the end of the index that is seeing constant delete activity has to scan over an awful lot of ghosted records before it finds the first 'alive' row to return. This explains the extra logical reads. A seek down the b-tree to the lowest value of the index will encounter much fewer ghosted records. Another performance-affecting factor is that the scan itself becomes responsible for removing the ghost records as mentioned in [Inside the Storage Engine: Ghost cleanup in depth](https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/) by Paul Randal. You should check that trace flag 661 (disable ghost cleanup) is not active. ### Solutions * You may find that running [sp_clean_db_free_space][2] provides relief. * Changing the process that deletes rows from that end of the index to use a `PAGLOCK` hint [would enable ghost cleanup on the spot](https://blogs.msdn.microsoft.com/sqljourney/2012/07/27/an-in-depth-look-at-ghost-records-in-sql-server), which could well solve the problem as well. If the ghost cleanup process has stopped completely, the most effective solution is normally to restart the SQL Server instance. You should also ensure that the SQL Server is running one of the latest Cumulative Updates. There have been many ghost cleanup bugs over the years. In your specific case: >It turned out the problem was caused by another test database on the same server. That test database was restored with "data loss", and is corrupt. Surprisingly, the ghost cleanup process apparently was stuck in that database. Once we deleted that corrupted database from SMSS, the problem resolved by itself (took a long time and might have caused DB to lockup for a short while). [1](https://i.stack.imgur.com/1AGv4.png): https://i.stack.imgur.com/1AGv4.png [2](https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql): https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql [3](https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/): https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/ [4](https://blogs.msdn.microsoft.com/sqljourney/2012/07/27/an-in-depth-look-at-ghost-records-in-sql-server): https://blogs.msdn.microsoft.com/sqljourney/2012/07/27/an-in-depth-look-at-ghost-records-in-sql-server [5](https://support.microsoft.com/en-nz/help/920093/tuning-options-for-sql-server-when-running-in-high-performance-workloa): https://support.microsoft.com/en-nz/help/920093/tuning-options-for-sql-server-when-running-in-high-performance-workloa

Paul White

Show me the median! Show me the Mediaaan! Shooow meee the Meediaaaan! =D 9

mysql

anoldmaninthesea anoldmaninthesea

chat

transcript

Josh Darnell

LOL

PeterVandivier

for the transcript though: if you've arrived here from GlobalSign internal documentation, you are morally obligated to @-reply/ping me from this message    

![blob](/image?hash=9281c078b714ebed1d91068e852139218bb4738546b82428b935e482717d8008)

PeterVandivier

:wave:

Josh Darnell

👋 hey Postgres nerds!

Josh Darnell

Well.  Let's wave to be sure we're being polite.

PeterVandivier replying to Josh Darnell

i wonder if anyone will ever follow it :-P

Josh Darnell replying to PeterVandivier

Haha that's a lovely circular reference.

PeterVandivier

in a pleasing (possibly ironic) symmetry, the link in that screencap is actually to the chat message in this room where i started rubber-ducking :sweat_smile:

PeterVandivier

...but it's the explanation I put in the documentation I did for it ¯\\\_(ツ)_/¯  
  
![blob](/image?hash=2e179e8867756fa56cf6b16502b25eee3f44c24fc6e1275469b84786ddf8d762)

PeterVandivier

my working theory is this: because there's a single element in the `row()` returned by `jsonb_array_elements()`, then `->>` gets lazily assigned. `with ordinality` adds a second element to the `row()` returned and forces the error. sadly this isn't supported by my attempt at a repro...  
  
<>https://dbfiddle.uk/?rdbms=postgres_10&fiddle=02994ab816288a1f0aba1287ab299d21

Andriy M

I wonder, if there was a `->>`operator for `record`, how would it know which `jae` to pick to apply the operator? Because I'm guessing in the above query the `jae` in `jae->>'a'` resolves as `jae.jae`.  In other words, it's the `jae` column alias, not the `jae` table alias.  The operator applies to the column because there's no such operator for `record` (the table alias), but what if there was?

PeterVandivier

:-/   ¯\\\_(ツ)_/¯  
  
> "_just add the column alias, too_"
  
<>https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3e095ae88f1559181450e8d219224e69&hide=255

PeterVandivier

wondering if that warrants a bug report :thinking:

PeterVandivier

wtaf

PeterVandivier

it's the _alias_ that changes the resultset

PeterVandivier

<>https://dbfiddle.uk/?rdbms=postgres_10&fiddle=9c5e0d9d1772851cfc7373e5620ce3b1&hide=127

PeterVandivier

omg it gets grosser

PeterVandivier

`with ordinality` isn't a drop-in, it changes the darn structure of the resultset

PeterVandivier

aw eff this noise  
  
<>https://dbfiddle.uk/?rdbms=postgres_10&fiddle=85b5f12cab1053c8af83d0a68569bb66&hide=31

Josh Darnell

You're welcome!

kus replying to Josh Darnell

thank you, Josh

Josh Darnell

https://dbfiddle.uk/?rdbms=db2_11.1&fiddle=8ae9d459bfc003158a4258647045094e  
  
It looks like, in DB2, that could be selecting from the vendor-specific "dual" table:  
  
```  
SELECT 'potato', 'bananas' 
FROM SYSIBM.DUAL
```  
  
or using the `VALUES` clause to define and build a table expression:
  
```  
SELECT t.c1, t.c2 
FROM (VALUES ('potato', 'bananas')) t (c1, c2);
```

Josh Darnell replying to kus

https://stackoverflow.com/questions/3732422/select-from-nothing

kus

this is valid query in sql server 2019 Linux  https://dbfiddle.uk/?rdbms=sqlserver_2019l&fiddle=e504210afbcffe28e949a8efdd745a3a but not in db2 https://dbfiddle.uk/?rdbms=db2_11.1&fiddle=169ee4b22bab1d7bf3fc76b363a9192f

kus

Good morning guys, how do I write `select 'apple', 'banana'` in db2?

Andriy M

Morning

John K N

Morning

Andriy M

No worries, happy to help

FoggyFinder

thank you. Seems like this is exactly what I was looking for

Andriy M

Same suggestions elsewhere: https://sqlskull.com/2020/12/18/get-row-counts-for-all-tables-in-sql-server/

Andriy M

https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/

Andriy M

I personally don't know of any way but I might be unaware if there is one. I vaguely recollect a discussion about there being something in metadata that gives a rough estimation, but I'm not sure

FoggyFinder

yep

Andriy M replying to FoggyFinder

You mean you want to check all tables in a single instruction?

FoggyFinder

so there is no build-in function and I have to enumerate all of tables anyway?

Andriy M

Or as part of a condition in a query (or the only condition, as necessary):  
```  
...  
WHERE  
  ...  
  AND EXISTS (SELECT ...)    
  ...
```

Andriy M

```  
IF EXISTS (SELECT ...)  
BEGIN      
  ...  /* do what you need to do */
END  
```

Andriy M

I usually use EXISTS

FoggyFinder

I don't want to execute "Select Top 1  ..." and so on for all tables despite there are not too much

FoggyFinder

MSSQL: Is there is a way to check whether any table contains any data?

Andriy M

Morning

Josh Darnell

Morning.

John K N

morning

Josh Darnell

Morning!

Andriy M

Morning

Tom V

Morning

Andriy M

Morning

Jack Douglas

Morning

John K N

Morning Heapers 2.0

Josh Darnell

LOL wow.