Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [sql-server-2016]

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

Major releases:

  1. 13.0.1601.5 - RTM, June 2016
  2. 13.0.4001.0 - Service Pack 1 (SP1), released November 2016

SQL Server 2016 will leave mainstream support on 2021-07-13 and extended support on 2026-07-14.

2804 questions
43
votes
7 answers

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 it but would feel cheap if I did. My use case is…
J.D.
  • 40,776
  • 12
  • 62
  • 141
41
votes
2 answers

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 differences will be between Always Encrypted & the…
RoastBeast
  • 679
  • 2
  • 7
  • 13
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,554
  • 1
  • 20
  • 30
40
votes
4 answers

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 Enterprise Edition (64-bit) on Windows Server 2012 R2…
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
37
votes
2 answers

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 storing the data in memory?
J.D.
  • 40,776
  • 12
  • 62
  • 141
37
votes
3 answers

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 writes, such as a hash table, spool, or sort that…
James Lupolt
  • 4,278
  • 5
  • 31
  • 46
34
votes
3 answers

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 the internet, however they apply to Oracle and…
user129291
31
votes
4 answers

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 based on the unique key of the table and some kind of…
Joe Obbish
  • 32,976
  • 4
  • 74
  • 153
29
votes
5 answers

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 alternating values. I'll use the GetNums function to…
Joe Obbish
  • 32,976
  • 4
  • 74
  • 153
29
votes
1 answer

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 upgrade is a "nice to have" In the spirit of a few…
Mike Walsh
  • 18,278
  • 6
  • 50
  • 74
28
votes
2 answers

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?
user37362
28
votes
2 answers

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 and I blogged about it here. The new operator can…
Joe Obbish
  • 32,976
  • 4
  • 74
  • 153
28
votes
3 answers

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 FREEPROCCACHE and DBCC DROPCLEANBUFFERS manually (except for…
jitbit
  • 765
  • 1
  • 8
  • 15
28
votes
1 answer

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 million rows deleted 1.9 GiB TempDB used 14 minutes…
James Lupolt
  • 4,278
  • 5
  • 31
  • 46
27
votes
1 answer

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 Col LIKE N'%�%' Returns Col A B C Ƕ Ƿ Ǹ SELECT…
Martin Smith
  • 87,941
  • 15
  • 255
  • 354
1
2 3
…
99 100