Most Popular
1500 questions
21
votes
3 answers
MongoDB: RAM requirements
Is it enough to have the entire index in memory/ram or does mongodb even try to allocate as much ram as possible to store even the data for fast reads?
I'd like to run mongodb + other applications and it looks like mongodb is the only one which does…
ledy
- 705
- 3
- 12
- 16
21
votes
3 answers
Get rows with most recent date for each different item
Let's say this is the sample date coming from a join of 2 tables. Database is Postgres 9.6
id product_id invoice_id amount date
1 PROD1 INV01 2 01-01-2018
2 PROD2 INV02 3 01-01-2018
3 …
Alin
- 325
- 1
- 2
- 7
21
votes
4 answers
Why is BULK INSERT Considered Dangerous?
I would like to understand why cyber-security teams in general (more than one organization I've dealt with) is dead set against granting BULK INSERT (e.g. TSQL) rights to applications and database programmers? I can't believe the "filling up the…
JackLThornton
- 313
- 1
- 2
- 5
21
votes
1 answer
Why is "select *" faster than "select top 500 *" in SQL Server?
I have a view, complicated_view -- there are a few joins and where clauses. Now,
select * from complicated_view (9000 records)
is faster, much faster, than
select top 500 * from complicated_view
We're talking 19 seconds vs. 5+ minutes.
The first…
user45867
- 1,739
- 5
- 24
- 41
21
votes
1 answer
Why does SQL Server "Compute Scalar" when I SELECT a persisted computed column?
The three SELECT statements in this code
USE [tempdb];
GO
SET NOCOUNT ON;
CREATE TABLE dbo.persist_test (
id INT NOT NULL
, id5 AS (id * 5)
, id5p AS (id * 5) PERSISTED
);
INSERT INTO dbo.persist_test…
Nick Chammas
- 14,810
- 17
- 76
- 124
21
votes
4 answers
Advice on diagnosing a "sometimes" slow query
I have a stored procedure which returns results from an indexed view via a covering index. Usually, it runs fast (~10ms), sometimes it can run up to 8 seconds.
Here's an example random execution (note: this isn't a slow one, but the query text is…
RPM1984
- 435
- 3
- 10
21
votes
3 answers
How to Dynamically change the database using TSQL
I'm having trouble with trying to dynamically change the context of SSMS to the database specified in dynamic SQL:
EXEC sys.sp_executesql N'USE db1 ' ;
It executes successfully however the database context of SSMS doesn't change.
I've tried a…
Mazhar
- 823
- 4
- 11
- 24
21
votes
1 answer
Hash keys probe and residual
Say, we have a query like this:
select a.*,b.*
from
a join b
on a.col1=b.col1
and len(a.col1)=10
Assuming the above query uses a Hash Join and has a residual, the probe key will be col1 and the residual will be len(a.col1)=10.
But while going…
TheGameiswar
- 2,999
- 4
- 30
- 50
21
votes
3 answers
Why doesn't InnoDB store the row count?
Everyone knows that, in tables that use InnoDB as engine, queries like SELECT COUNT(*) FROM mytable are very inexact and very slow, especially when the table gets bigger and there are constant row insertions/deletions while that query executes.
As I…
Radu Murzea
- 408
- 3
- 11
21
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
- 917
- 3
- 9
- 23
21
votes
1 answer
CTE: Get all parents and all children in one statement
I have this working CTE example.
I can select all grand-parents and all children.
But how can I select all grand-parents and all children in one statement?
In this example I want Grandfather, Father, Son as output if I give "Father" as input.
I use…
guettli
- 1,591
- 5
- 25
- 51
21
votes
3 answers
Why can it take up to 30 seconds to create a simple CCI rowgroup?
I was working on a demo involving CCIs when I noticed that some of my inserts were taking longer than expected. Table definitions to reproduce:
DROP TABLE IF EXISTS dbo.STG_1048576;
CREATE TABLE dbo.STG_1048576 (ID BIGINT NOT NULL);
INSERT INTO…
Joe Obbish
- 32,976
- 4
- 74
- 153
21
votes
2 answers
The certificate chain was issued by an authority that is not trusted
some time ago I installed SQL server 2016 developer edition on a windows 10 home edition environment (a laptop to be precise) and everything was fine.
then someone - an administrator on the box - decided without telling me, to rename said box.
after…
Marcello Miorelli
- 17,274
- 53
- 180
- 320
21
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
- 337
- 2
- 10
21
votes
2 answers
Returning a result set with multiple rows based on max date
I have a child table that is something like this:
[Cust Date Table]
| Customer ID | Some Date | Balance |
+-------------+------------+---------+
| 1 | 2012-04-30 | 20.00 |
| 1 | 2012-03-31 | 50.00 |
| 2 |…
Joe DBA
- 313
- 1
- 2
- 6