Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [sql-server-2016]

SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
43 votes
7 answers
9k views

What's a good use case for SELECT * in production code?

Out of habit, I never use SELECT * in production code (I only use it with ad-hoc scrap queries, typically when learning the schema of an object). But I ran across a case now where I'm tempted to use ...
J.D.'s user avatar
  • 37.8k
41 votes
2 answers
20k views

In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption?

As I write this I am still awaiting the official release of SQL Server 2016 so that we can explore the usefulness of its "Always Encrypted" feature. I would just like to know what the specific ...
RoastBeast's user avatar
40 votes
3 answers
6k views

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 ...
PicoDeGallo's user avatar
  • 1,564
39 votes
4 answers
86k views

Blue icon with question mark - what does it mean?

The SQL Server instance is accessible and seems to be fine. Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) Mar 6 2017 14:18:16 Copyright (c) Microsoft Corporation ...
Marcello Miorelli's user avatar
37 votes
3 answers
9k views

Why would a SELECT query cause writes?

I've noticed that on a server running SQL Server 2016 SP1 CU6 sometimes an Extended Events session shows a SELECT query causing writes. For example: The execution plan shows no obvious cause for the ...
James Lupolt's user avatar
  • 4,258
36 votes
2 answers
8k views

Is it generally faster to select into a temp table than selecting into an actual table?

I thought I once read somewhere that writing to tempdb is faster than an actual table not in tempdb. Is this true in any capacity? I thought I recall it saying something special about tempdb and ...
J.D.'s user avatar
  • 37.8k
34 votes
3 answers
52k views

varchar(255) or varchar(256)?

Should I use varchar(255) or varchar(256) when designing tables? I've heard one byte is used for the length of column, or to store metadata. Does it matter anymore at this point? I saw some posts on ...
user avatar
31 votes
4 answers
3k views

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

As part of our ETL process, we compare rows from staging against the reporting database to figure out if any of the columns have actually changed since the data was last loaded. The comparison is ...
Joe Obbish's user avatar
  • 32.2k
29 votes
5 answers
8k views

Why does my SELECT DISTINCT TOP N query scan the entire table?

I've run into a few SELECT DISTINCT TOP N queries which appear to be poorly optimized by the SQL Server query optimizer. Let's start by considering a trivial example: a million row table with two ...
Joe Obbish's user avatar
  • 32.2k
29 votes
1 answer
3k views

What are Objective Reasons to Prefer SQL Server 2016 over Earlier Versions?

As Microsoft makes SQL Server version upgrades more frequently since the SQL Server 2005 or 2008 days, a lot of companies find it difficult to determine when an upgrade is a "must have!" and when an ...
Mike Walsh's user avatar
  • 18.2k
28 votes
3 answers
17k views

SQL Server clears plan cache and execution stats periodically

After upgrading SQL Server 2014 to 2016, the server keeps resetting cached execution plans and dm* views (like dm_exec_query_stats) etc. every couple of hours As if someone executes DBCC ...
jitbit's user avatar
  • 765
28 votes
1 answer
952 views

Is the eager spool operator useful for this delete from a clustered columnstore?

I'm testing deleting data from a clustered columnstore index. I noticed that there is a large eager spool operator in the execution plan: This completes with the following characteristics: 60 ...
James Lupolt's user avatar
  • 4,258
28 votes
2 answers
3k views

What is a "Partial Matching Index"?

I'm trying to learn more about the "foreign key references check" query plan operator introduced in SQL Server 2016. There's not a lot of information about it out there. Microsoft announced it here ...
Joe Obbish's user avatar
  • 32.2k
27 votes
2 answers
15k views

How to disable "Completion time:..." in SQL Server Messages window

Every query I run in SSMS append the annoying message: "Completion time:...". How can I disable that text?
user avatar
26 votes
2 answers
1k views

Why does a subquery reduce the row estimate to 1?

Consider the following contrived but simple query: SELECT ID , CASE WHEN ID <> 0 THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE) ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2) END AS ...
Joe Obbish's user avatar
  • 32.2k
26 votes
1 answer
17k views

Why does searching for LIKE N'%�%' match any Unicode character and = N'�' match many?

DECLARE @T TABLE( Col NCHAR(1)); INSERT INTO @T VALUES (N'A'), (N'B'), (N'C'), (N'Ƕ'), (N'Ƿ'), (N'Ǹ'); SELECT * FROM @T WHERE ...
Martin Smith's user avatar
  • 84.8k
24 votes
4 answers
53k views

Enable TLS 1.2 for SQL Server 2016 database mail

I have been puzzled by this issue for almost 1 week. Hopefully someone in our community has experienced the same issue and already found a solution. So here is my problem: As per our company policy, ...
jyao's user avatar
  • 3,063
24 votes
3 answers
47k views

How to add a Default constraint while creating a table? SQL Server

I am trying to create a new table with columns followed by their constraint as shown below. Create tblTest( columns.. .. .. Gender int, Constraint DF_tblTest_Gender Default 3 For Gender, .. .. .. ) ...
Dhruv Raj's user avatar
  • 241
24 votes
1 answer
1k views

Why does this MERGE statement cause the session to be killed?

I have the below MERGE statement which is issued against the database: MERGE "MySchema"."Point" AS t USING ( SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region" ...
Mr.Brownstone's user avatar
24 votes
1 answer
3k views

Why doesn't this query use an index spool?

I'm asking this question in order to better understand the optimizer's behavior and to understand the limits around index spools. Suppose that I put integers from 1 to 10000 into a heap: CREATE TABLE ...
Joe Obbish's user avatar
  • 32.2k
23 votes
7 answers
84k views

The operating system returned error 21 (The device is not ready.)

Every time I reboot Windows, for some databases I get this error: The operating system returned error 21(The device is not ready.) I checked the disk with chkdsk /r - no bad sectors. I executed ...
Max's user avatar
  • 471
22 votes
1 answer
23k views

datetime2(0) vs datetime2(2)

According to the documentation datetime2 (Transact-SQL): Storage size 6 bytes for precisions less than 3. 7 bytes for precisions 3 and 4. All other precisions require 8 bytes. The size of ...
Zapnologica's user avatar
21 votes
3 answers
762 views

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....
Joe Obbish's user avatar
  • 32.2k
21 votes
2 answers
77k views

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 - ...
Marcello Miorelli's user avatar
21 votes
1 answer
20k views

Performance comparison between using Join and Window function to get lead and lag values

I have a table with 20M rows, and each row has 3 columns: time, id, and value. For each id and time, there is a value for the status. I want to know the lead and lag values of a certain time for a ...
Jason's user avatar
  • 683
20 votes
2 answers
2k views

Why is AT TIME ZONE nondeterministic?

SQL Server 2016's AT TIME ZONE appears to be nondeterministic. However, I haven't been able to find documentation officially stating this or giving a rationale as to the reasoning behind it. Why is ...
Ben Gribaudo's user avatar
20 votes
1 answer
2k views

Nonclustered index storage on clustered columnstore

In SQL Server, a non-unique nonclustered index on a rowstore table incorporates the base object's bookmark (RID or clustering key) at all levels of the nonclustered index structure. The bookmark is ...
Paul White's user avatar
  • 85.5k
19 votes
1 answer
6k views

SQL Server--If logic in stored procedure and the plan cache

SQL Server 2012 and 2016 Standard: If I put if-else logic in a stored procedure to execute one of two branches of code, depending on the value of a parameter, does the engine cache the latest version?...
Nicole G.'s user avatar
  • 315
19 votes
3 answers
55k views

SQL Server - Select most recent record from each group when performance is critical

I run a SQL Server 2016 database where I have the following table with 100+ millions rows: StationId | ParameterId | DateTime | Value 1 | 2 | 2020-02-04 15:00:000 | 5....
Pedro Ludovico Bozzini's user avatar
18 votes
6 answers
65k views

SQL Server 2016 DB Mail Not Sending

I'm on SQL Server 2016 and having a blast... my DB Mail isn't sending and I'm running out of places to look. I double checked the SQL account permissions to the DBmail executable - it has read and ...
Dave's user avatar
  • 2,389
18 votes
2 answers
4k views

Disk space full during insert, what happens?

Today I discovered the harddrive which stores my databases was full. This has happened before, usually the cause is quite evident. Usually there is a bad query, which causes huge spills to tempdb ...
HoneyBadger's user avatar
18 votes
1 answer
2k views

Why does this derived table improve performance?

I have a query which takes a json string as a parameter. The json is an array of latitude,longitude pairs. An example input might be the following. declare @json nvarchar(max)= N'[[40.7592024,-73....
Michael B's user avatar
  • 519
17 votes
5 answers
5k views

Can ransomware embed itself in to a SQL backup file?

One of the best protections against ransomware is to back up all of your database files to a completely separate system. Which we have done. But one thought is the backup of the database could ...
icecurtain's user avatar
17 votes
1 answer
9k views

SQL Server 2016 Bad Query Plan locks up DB once a week

Once a week, for the past 5 weeks, around the same time of day (early morning, may be based on user activity when people are starting to use it), SQL Server 2016 (AWS RDS, mirrored) starts timing out ...
Professional Sounding Name's user avatar
17 votes
1 answer
1k views

Why is CHECKDB reading the transaction log file on a database with a memory optimized table?

tl;dr: why is CHECKDB reading the transaction log for a user database with memory optimized tables? It appears that CHECKDB is reading the transaction log file of the user database when it's checking ...
Josh Darnell's user avatar
  • 29.3k
17 votes
1 answer
1k views

Query strategies using SQL Server 2016 system-versioned temporal tables for Slowly-Changing Dimensions

When using a system-versioned temporal table (new in SQL Server 2016), what are the query authoring and performance implications when this feature is used to handle Slowly Changing Dimensions in a ...
Justin Grant's user avatar
16 votes
2 answers
3k views

SQL estimates are way off on DELETE statement with Triggers on huge tables

I'm working with Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 ...
Danielle Paquette-Harvey's user avatar
16 votes
2 answers
12k views

Why does dropping foreign keys take long?

I have made a script that, one at a time, deletes all the foreign keys from a database, just like this: ALTER TABLE MyTable1 DROP CONSTRAINT FK_MyTable1_col1 ALTER TABLE MyTable2 DROP CONSTRAINT ...
carlo.borreo's user avatar
  • 1,487
15 votes
2 answers
7k views

Strange performance problem with SQL Server 2016

We have a single instance of SQL Server 2016 SP1 running in a VMware virtual machine. It contains 4 databases, each for a different application. Those applications are all on separate virtual servers. ...
Emptyslot's user avatar
  • 459
15 votes
1 answer
3k views

Incorrect comparison of datetime and datetime2

I know that it is not a good practice to have an implicit type convert. But this is really unexpected behavior when a lower value can suddenly become higher. declare @LastSelectedDate DATETIME = '2021-...
Artashes  Khachatryan's user avatar
15 votes
2 answers
2k views

Cardinality estimation problem on inner join

I'm struggling to understand why row estimation is so terribly wrong, here is my case: Simple join - using SQL Server 2016 sp2 (same issue on sp1), dbcompatiblity=130. select ...
LeMaciek's user avatar
  • 231
15 votes
1 answer
760 views

Sort spills to tempdb but estimated rows equals to actual rows

On a SQL Server 2016 SP2 with max memory set to 25GB we have a query that executes about 80 times in a minute. The query spills about 4000 pages to tempdb. This causes for a lot of IO on the disk of ...
Frederik Vanderhaegen's user avatar
15 votes
2 answers
4k views

Neither DBCC FREEPROCCACHE nor DBCC FREESYSTEMCACHE('SQL Plans') do anything to free CACHESTORE_SQLCP memory

CACHESTORE_SQLCP Sql Plans takes up > 38 GB after a few days. We are already running with "optimize for ad hoc workloads" option on. (Entity Framework and custom reporting creates a lot of ad hocs!) ...
Professional Sounding Name's user avatar
14 votes
2 answers
2k views

Unclear update conflict

I have two questions: 1. Why do I get update conflict in this situation instead of just blocking: -- prepare drop database if exists [TestSI]; go create database [TestSI]; go alter database [TestSI] ...
Pavel Zv's user avatar
  • 183
14 votes
1 answer
6k views

How did my colleague get his own schema?

I have a stored procedure that: checks if a table exists and if so, drops it. creates that table again then fills that table with about 30 queries. When I (DB Owner) run this proc, everything works ...
steenbergh's user avatar
14 votes
2 answers
40k views

OLE DB provider "MSOLEDBSQL" with SQL Server not supported?

I have been using linked server with the old provider (SQLNCLI) without any issue, as recommended by Microsoft, I'm planning to switch to new provider (MSOLEDBSQL). I'm able to add linked server using ...
Shekar Kola's user avatar
  • 2,427
14 votes
1 answer
780 views

Why does NOLOCK make a scan with variable assignment slower?

I'm fighting against NOLOCK in my current environment. One argument I've heard is that the overhead of locking slows down a query. So, I devised a test to see just how much this overhead might be. I ...
Forrest's user avatar
  • 4,169
14 votes
1 answer
643 views

Incorrect behaviour of SQL Server 2016 with memory-optimized tables

Please have a look at the following SQL query: CREATE TYPE dbo.IN_MEMORY_TABLE_TYPE AS TABLE ( source_col INT NULL, target_col INT not NULL INDEX ix_InMemoryTable NONCLUSTERED (target_col) ) ...
Dzmitry Sauchanka's user avatar
14 votes
2 answers
12k views

Error updating temporal tables

I have implemented the System Versioned Temporal Tables on the table "Orders". The application is using different access patterns to modify the data in this table. There are direct statements coming ...
Danilo Braga's user avatar
  • 1,428
14 votes
1 answer
1k views

What is the maximum number of allowable bound actions for an extended event?

If you add "too many" actions to an event in an event session, you will receive this error: Msg 25639, Level 16, State 23, Line 1 The event, "[event name]", exceeds the number of allowable bound ...
Riley Major's user avatar
  • 1,903

1
2 3 4 5
56