Most Popular
1500 questions
25
votes
3 answers
Strange behaviour with sample sizes for statistics updates
I've been playing around investigating sampling thresholds with statistics updates on SQL Server (2012) and noticed some curious behaviour. Basically the number of rows sampled seems to vary under some circumstances - even with the same set of data.…
Matthew McGiffen
- 552
- 6
- 11
25
votes
1 answer
Why does NOT IN with a set containing NULL always return FALSE/NULL?
I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything.
What's the best way to understand this? I thought…
newenglander
- 1,055
- 5
- 13
- 23
25
votes
3 answers
How do I cancel an app lock request in SQL Server?
The sp_getapplock stored procedure has the following return values:
0: The lock was successfully granted synchronously.
1: The lock was granted successfully after waiting for other incompatible locks to be released.
-1: The lock request timed…
Heinzi
- 3,145
- 2
- 29
- 42
25
votes
2 answers
Index vs. partition
Why is it not possible to achieve performance improvements with indexes only, so that other techniques like table partitioning becomes necessary? The question relates only to performance, of course different partitions can be put into different…
paweloque
- 1,089
- 2
- 14
- 23
25
votes
4 answers
Tracking stored procedure usage
Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
DForck42
- 3,048
- 3
- 34
- 65
25
votes
2 answers
Cardinality Estimate for LIKE operator (Local Variables)
I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and the query optimiser doesn't have to resort to…
Fza
- 652
- 1
- 7
- 19
25
votes
1 answer
Operator used tempdb to spill data during execution with spill level 2
I am struggling to minimise the cost of sort operation on a query plan with the warning Operator usedtempdbto spill data during execution with spill level 2
I have found several posts related to spill data during execution with spill level 1, but…
Marcello Miorelli
- 16,170
- 52
- 163
- 300
25
votes
3 answers
Was Management Studio removed from SQL Server 2016 installation media?
I just tried installing SQL Server 2016 Enterprise but I noticed that Management Studio was not installed by default. I also noticed there is a separate link to SSMS in the installation guide which points to…
Mark13426
- 579
- 1
- 7
- 11
25
votes
1 answer
Sequence - NO CACHE vs CACHE 1
Is there any difference between a SEQUENCE declared using NO CACHE and one declared using CACHE 1 in SQL Server 2012+?
Sequence #1:
CREATE SEQUENCE dbo.MySeqCache1
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999
NO…
marc_s
- 8,932
- 6
- 45
- 51
25
votes
2 answers
Can't default date to CURRENT_TIMESTAMP in MySQL 5.5
I am not able to set Current_timestamp as default value. My Mysql version is 5.5.47.
Query is
ALTER TABLE `downloads` ADD `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ;
While it is working fine on my local DB with mysql V5.6.56.
urfusion
- 353
- 1
- 4
- 9
25
votes
2 answers
Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL
If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished?
I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until the new one has finished, so I need to know when…
Amandasaurus
- 927
- 3
- 12
- 15
25
votes
3 answers
How do I specify that a column should be auto-incremented in pgAdmin?
I have started to learn pgAdmin III to manage a PostgreSQL database. But it wasn't an easy to use application.
If I create or have created a table with pgAdmin III, how can I add "auto-increment"-functionality on a column id that has type integer?
Jonas
- 32,975
- 27
- 61
- 64
25
votes
2 answers
How to get particular object from jsonb array in PostgreSQL?
I have a field called 'user' that holds a json array that roughly looks like this:
"user":
[{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}]
Now I want a query like:
select count from tablename where id = "1"
I'm not able to get the…
Rabi C Shah
- 251
- 1
- 3
- 3
25
votes
1 answer
What is the overhead for varchar(n)?
I wanted to ask for the meaning of this fragment from Postgres doc regarding varchar(n) type:
The storage requirement for a short string (up to 126 bytes) is 1 byte
plus the actual string, which includes the space padding in the case
of…
keypress
- 353
- 1
- 3
- 5
25
votes
2 answers
Max number of user connections
In SQL Server 2012 Standard edition, I know that the max number of user connections is 32,767. What should I do as a DBA if I am heading towards this number?
Currently there are 30,000 user connections, and this number is expected to increase.
sebeid
- 1,381
- 2
- 16
- 26