330 votes
Accepted

Help installing SQL Server 2017 - VS Shell installation has failed with exit code 1638

I experienced this problem as well with SQL Server 2017 Developer and it appears to be just bad planning on the part of the SQL Server installation package people. The problem is that Visual Studio ...
user avatar
  • 3,486
58 votes
Accepted

How do you create a view with SNAPSHOT_MATERIALIZATION in SQL Server 2017?

You can't. The feature is disabled in 2017 RTM. That said, you can... Using AdventureWorks: CREATE VIEW dbo.TH WITH SCHEMABINDING AS SELECT P.ProductID, COUNT_BIG(*) AS cbs FROM Production.Product ...
user avatar
  • 69.2k
37 votes
Accepted

SQL Server returns "Arithmetic overflow error converting expression to data type int."

For values larger than the INT max (2,147,483,647), you'll want to use COUNT_BIG(*). SELECT COUNT_BIG(*) AS [Records], SUM(t.Amount) AS [Total] FROM dbo.t1 AS t WHERE t.Id > 0 AND t.Id &...
user avatar
  • 33.4k
37 votes
Accepted

If a database only ever has one insert, is it bad to index every possible column combination?

Yes, it will influence initial plan compile time as the optimizer will have many extra access paths to the data to consider. Since you're on SQL Server 2017, loading once, and running reports, why ...
user avatar
  • 33.4k
35 votes

Which collation should I use for biblical Hebrew?

First: There is no distinction, collation-wise, between biblical Hebrew and modern Hebrew. We are just dealing with Hebrew. Second: Regardless of anything else, you want to use the newest set of ...
user avatar
31 votes
Accepted

Self referencing scalar function nesting level exceeded when adding a select

This is a bug in project normalization, exposed by using a subquery inside a case expression with a non-deterministic function. To explain, we need to note two things up front: SQL Server cannot ...
user avatar
  • 69.2k
31 votes

SQL Server has encountered occurences of I/O requests taking longer than 15 seconds

This is far less often a disk issue, and far more often a networking issue. You know, the N in SAN? If you go to your SAN team and start talking about the disks being slow, they're gonna show you a ...
user avatar
  • 33.4k
30 votes
Accepted

Why does changing the declared join column order introduce a sort?

Is this intentional? It is by design, yes. The best public source for this assertion was unfortunately lost when Microsoft retired the Connect feedback site, obliterating many useful comments from ...
user avatar
  • 69.2k
27 votes

If a database only ever has one insert, is it bad to index every possible column combination?

If you have N columns in a table, every possible column combination is 2^N-1 (removing the empty set). For 10 columns that would mean 1023 indexes, for 20 columns we end up with a whopping 1048575 ...
user avatar
  • 21.1k
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 ...
user avatar
  • 3,488
25 votes
Accepted

SQL Server 2017 crashes when backing up because filepath is wrong

I was able to reproduce this. On 2016, if I put an invalid path like that, I get this message: Cannot open backup device 'D:mapbenefits_LogBackup_2019-02-21_13-58-24.bak'. Operating system error 3(...
user avatar
  • 27.1k
24 votes
Accepted

Why does a DELETE query run in one format much longer than in another?

This part of the plan is the problem. Issue The correct behaviour if the subquery brings back any NULL is to return 0 rows from the NOT IN. Even if ID is not nullable (and therefore MIN(ID) cannot ...
user avatar
  • 77.9k
23 votes
Accepted

How do I totally remove SQL Server 2017 on Ubuntu?

These are actually very poor instructions on how to remove the Microsoft SQL Server. SQL Server installs these associated packages. mssql-server mssql-server-fts mssql-tools msodbcsql unixodbc-dev ...
user avatar
  • 55.7k
22 votes
Accepted

Increased memory to server, but do I need to change page-file size?

I believe the 2x size of RAM recommendation is outdated and unrealistic. If a server only has 32 GB of RAM it might make sense, but it's a waste of expensive disk space to make it 2x on a system ...
user avatar
  • 7,733
21 votes
Accepted

Why does this query, missing a FROM clause, not error out?

This statement is legal (in other words, no FROM is required): SELECT x = 1; SELECT x = 1 WHERE 1 = 1; -- also try WHERE 1 = 0; The trick is when you introduce a column name that clearly can't exist....
user avatar
20 votes
Accepted

Is there an efficient way to see the cause for "String or binary data would be truncated"?

There has been no change. SQL Server 2017 still offers up the same vague error message and does not provide any mechanism to discover the offending row/column. This Connect item had over 1,600 votes ...
user avatar
20 votes

Why does changing the declared join column order introduce a sort?

Can I eliminate the sort without changing the query (which is vendor code, so I'd really rather not...). I can change the table and indexes. If you can change the indexes, then changing the order of ...
user avatar
  • 27.1k
20 votes
Accepted

Error starting SQL Server 2017 service. Error Code 3417

I would start by saying that you are running SQL Server 2017 on Windows 10 (client OS) which is not supported see Hardware and Software requirements for SQL Server 2017. You are wasting the capability ...
user avatar
  • 18.3k
20 votes

Is it possible to do a partial transaction log backup with SQL Server 2017? I don't have enough disk space to do a full backup

No, you can't do partial log backups to split this work into multiple files. What you can do depends on your goals. If you need to maintain the log backup chain, your only option is to provision more ...
user avatar
  • 27.1k
19 votes
Accepted

"Warnings: Operation caused residual I/O" versus key lookups

The cost model used by the optimizer is exactly that: a model. It produces generally good results over a wide range of workloads, on a wide range of database designs, on a wide range of hardware. You ...
user avatar
  • 69.2k
19 votes
Accepted

SQL Server has encountered occurences of I/O requests taking longer than 15 seconds

We have a similar setup and recently encountered these messages in the logs. We are using a DELL Compellent SAN. Here are some things to check when receiving these messages that helped us find a ...
user avatar
  • 2,204
17 votes
Accepted

SQL Server Error, "Invalid usage of the option FIRST in the FETCH statement."

SQL Server has implemented the OFFSET and FETCH clauses as part of the ORDER BY clause, as pointed by the other answers and documented in their documentation. The SQL standard on the other side, has ...
user avatar
17 votes
Accepted

Changing the use of GETDATE() in the entire database

Use the SQL Server tool to export the database objects definition to a SQL file which should include: tables, views, triggers, SPs, functions, and so on Edit the SQL file (make a backup first) using ...
user avatar
  • 1,292
17 votes
Accepted

Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit?

Generally speaking, no. SQL Server compiles the whole batch at the current scope before execution so referenced entities have to exist (statement-level recompilations may also happen later). The main ...
user avatar
  • 69.2k
17 votes
Accepted

Non-yielding scheduler error in large memory server if the lock pages in memory option is disabled

We are seeing a lot of Non-yielding scheduler errors on a SQL Server VM with 504 GB of RAM. Reading this link on Microsoft Docs, it's recommended we enable Lock Pages in Memory. You're correct, also ...
user avatar
16 votes
Accepted

Getting "sqlcmd sqlcmd: command not found" in Linux?

There are two methods of resolving this. User-specific method advocated in the manual: add the directory to the environmental variable PATH. System-wide method: symlink the executable to a directory ...
user avatar
  • 55.7k
16 votes
Accepted

Installing Enterprise Edition in a virtual machine

Is it possible? Yes. Is it legal to use in production? No. What should I do instead? If it's for production, and your databases are 10GB or less, check out Express Edition. It's free, and has core ...
user avatar
  • 41.3k
16 votes
Accepted

Why is this stream aggregate necessary?

You can see the role of this aggregate if no rows match the WHERE clause. SELECT MAX(Revision) FROM dbo.TheOneders WHERE Id = 1 AND 1 = 1 /*To avoid auto parameterisation*/ AND Id%3 =...
user avatar
  • 77.9k
16 votes

Fast alter column NVARCHAR(4000) to NVARCHAR(260)

I don't know of a way to directly accomplish what you're looking for here. Note that the query optimizer isn't smart enough at this time to factor in constraints for memory grant calculations, so the ...
user avatar
  • 30.3k
16 votes

Fast alter column NVARCHAR(4000) to NVARCHAR(260)

Is there any way to alter the column data type as a metadata-only operation? I don't think so, this is how the product works right now. There are some really great workarounds to this limitation ...
user avatar
  • 27.1k

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