59 votes
Accepted

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

I generally abhor SELECT * in production code, and I've been in a situation where its use led to massive amounts of rework later. Your case does look like a fair use of it though. The place where I ...
Rusty's user avatar
  • 716
58 votes
Accepted

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

writing to tempdb is faster than an actual table not in tempdb It's true. There are two IO enhancements in TempDb. Writes to a table in a user database must have their log records flushed to disk ...
David Browne - Microsoft's user avatar
54 votes
Accepted

SQL Server's "Total Server Memory" consumption stagnant for months with 64GB+ more available

I bet you've configured the virtual CPUs in a way that some of the CPU nodes and/or memory nodes are offline. Download sp_Blitz (disclaimer: I'm one of the authors of that free open source script) ...
Brent Ozar's user avatar
49 votes
Accepted

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

Downsides of Transparent Data Encryption compared to Always Encrypted: Only protects data at rest - backups and data files are "safe" but data in motion or in memory is vulnerable Whole database only ...
Aaron Bertrand's user avatar
45 votes
Accepted

varchar(255) or varchar(256)?

Size each and every column appropriately. Do NOT use a "standard" size for each column. If you only need 30 characters, why create a column that can handle 255? I'm so glad you're not advocating ...
Hannah Vernon's user avatar
  • 70k
39 votes

Why would a SELECT query cause writes?

In some cases Query Store can cause writes to occur as an effect of a select statement, and in the same session. This can be reproduced as follows: USE master; GO CREATE DATABASE [Foo]; ALTER ...
James Lupolt's user avatar
  • 4,258
37 votes

Enable TLS 1.2 for SQL Server 2016 database mail

TLS1.2 is the only version of TLS considered secure now (March 2019). It took considerable time and effort to discover that there are 2 essential, additional settings which are required to get this ...
Zeek's user avatar
  • 481
37 votes

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

What’s a good use of select * in production? IMO only things like this: eg create table #foo(a int, b int, c int, d int) ... select * from #foo or with q as ( select a, b, c from ... ) ...
David Browne - Microsoft's user avatar
35 votes

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

There are a lot of reasons to consider an upgrade to SQL Server 2016. Most of the time, these reasons vary depending on the version of SQL Server one is coming from. This won't be an exhaustive list, ...
Mike Walsh's user avatar
  • 18.2k
35 votes

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

You can name the constraint inline: CREATE TABLE tblTest( -- -- Gender int CONSTRAINT DF_tblTest_Gender DEFAULT 3, -- ) ; As the CREATE TABLE msdn page shows: DEFAULT ... To maintain ...
ypercubeᵀᴹ's user avatar
35 votes
Accepted

datetime2(0) vs datetime2(2)

The size of dateTime2(0), dateTime2(1), dateTime2(2), dateTime2(3) use the same amount of storage. (6 Bytes) Would I be correct in saying that I might as well go with dateTime2(3) and gain ...
Dan Guzman's user avatar
  • 28.2k
35 votes

SQL Server clears plan cache and execution stats periodically

First, get the exact times when the plan cache is being cleared. Here's the easiest way to do it - it should run nearly instantly, and won't block anyone: SELECT TOP 1 creation_time FROM sys....
Brent Ozar's user avatar
33 votes
Accepted

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? ...
Erik Darling's user avatar
  • 40.8k
30 votes

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

There look to be three different optimizer rules that can perform the DISTINCT operation in the above query. The following query throws an error which suggests that the list is exhaustive: SELECT ...
Joe Obbish's user avatar
  • 32.2k
30 votes
Accepted

STRING_SPLIT with a multiple-character separator?

Well, you can always use REPLACE to add a single-character delimiter to the argument before passing it in. You just need to choose a character that is unlikely/impossible to appear in the actual data. ...
Aaron Bertrand's user avatar
30 votes
Accepted

Blue icon with question mark - what does it mean?

Enabling these Windows Firewall rules did the trick for me Windows Management Instrumentation (WMI-In) Windows Management Instrumentation (DCOM-In) These two rules are predefined and you can enable ...
D M's user avatar
  • 433
30 votes
Accepted

What will happen if I drop an existing partition scheme/function from a table with (huge volume of records)?

You can't drop partition schemes or functions that are in use. USE tempdb CREATE PARTITION FUNCTION yourmom ( INT ) AS RANGE LEFT FOR VALUES ( 10, 20, 30, 40, 50 ); CREATE PARTITION SCHEME ...
Erik Darling's user avatar
  • 40.8k
30 votes
Accepted

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

If you have a small-enough number of (StationID, ParameterID) pairs, then try a query like this: select StationID, ParameterID, m.DateTime LastDate from StationParameter sp cross apply ( ...
David Browne - Microsoft's user avatar
27 votes

Blue icon with question mark - what does it mean?

I believe the blue question mark indicates that SSMS does not know if the service is running or not, by way of a WMI call. I'm quite certain this icon replaced the blank or white circle icon of prior ...
John Eisbrener's user avatar
27 votes
Accepted

Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups

The @CleanupTime is always specified for a specific backup job. For example, if you create a Full backup job, a Differential backup job and a Transaction Log backup job, then the @CleanupTime always ...
John K. N.'s user avatar
  • 17.8k
27 votes
Accepted

Temporarily moving a SQL Server 2016 database to SQL Server 2017 and then moving back. Is it possible?

No, this is not possible.SQL Server 2017 backups cannot be restored by any earlier version of SQL Server ref Also, regarding detatching and reattaching per the docs: After being attached to SQL ...
S3S's user avatar
  • 3,578
27 votes
Accepted

Why is casting a DATE field to VARCHAR datatype non-deterministic and is there a way to make it deterministic?

CAST is not deterministic because date format may change based on the server settings (i.e. under compatibility before 110 default date format is 0 => "Dec 12 2019 2:11PM" and output dependeds on ...
Piotr Palka's user avatar
  • 1,591
26 votes

Why would a SELECT query cause writes?

There's another time when this may happen, and that's with an automatic stats update. Here's the XE session we'll be looking at: CREATE EVENT SESSION batches_and_stats ON SERVER ADD EVENT ...
Erik Darling's user avatar
  • 40.8k
26 votes
Accepted

How did my colleague get his own schema?

As a rule, you should explicitely specify dbo schema if you want to create the object in this schema. As you are db_owner, your default schema is dbo, so there is no problem when you don't specify ...
sepupic's user avatar
  • 11k
25 votes

varchar(255) or varchar(256)?

Others have already pointed out that that the number of bytes required to store the length is fixed. I wanted to focus on this part in your question: Does it matter anymore at this point? You have ...
Joe Obbish's user avatar
  • 32.2k
24 votes

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

Every time I reboot windows, for some databases comes out this error. (OS Error 21 - Device not ready) This is due to a disk being offline or not being online either at the time SQL Server started, ...
Sean Gallardy's user avatar
24 votes

Why does a subquery reduce the row estimate to 1?

This definitely seems like unintended behavior. It is true that cardinality estimates do not need to be consistent at each step of a plan but this is a relatively simple query plan and the final ...
Joe Obbish's user avatar
  • 32.2k
24 votes
Accepted

Why does a subquery reduce the row estimate to 1?

This cardinality estimation (CE) issue surfaces when: The join is an outer join with a pass-through predicate The selectivity of the pass-through predicate is estimated to be exactly 1. Note: The ...
Paul White's user avatar
  • 84.2k
24 votes
Accepted

The certificate chain was issued by an authority that is not trusted

On connection pop up click on options Then make sure the "Trust server certificate" is checked
Ashkan S's user avatar
  • 356
24 votes
Accepted

Improper use of logical operators has led to poor query performance

In order to find out if two conditions are truly equivalent, you could try building the truth table for each of them and see if the two tables are identical. Here is how you could go about building ...
Andriy M's user avatar
  • 23k

Only top scored, non community-wiki answers of a minimum length are eligible