316

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 2017 installs the Microsoft Visual C++ 2017 Redistributable (x86) and (x64) and the SQL Server installation tries to install the Microsoft Visual C++ 2015 ...


55

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 AS P JOIN Production.TransactionHistory AS TH ON TH.ProductID = P.ProductID GROUP BY P.ProductID; GO CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.TH (ProductID) ...


36

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 not just use a clustered column store index instead? That seems to be the ideal solution to your need to index every possible column combination. Columnstore ...


34

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 collations, which are the _100_ series as they have newer / more complete sort weights and linguistic rules than the older series with no version number in the name ...


28

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 developers on the SQL Server team. Anyway, the current optimizer design does not actively seek to avoid unnecessary sorts per se. This is most often encountered ...


27

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 Server 2017, the database is available immediately and is automatically upgraded. This prevents the database from being used with an older version of the ...


26

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 indexes. Most of the indexes will never be used but will have to be taken into consideration by the optimizer. It is possible that the optimizer will choose a sub-...


26

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 execute subqueries directly, so they are always unrolled or converted to an apply. The semantics of CASE are such that a THEN expression should only be evaluated ...


25

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(The system cannot find the path specified.) On 2017 CU 13 (14.0.3048.4), it results in the service crashing. You've already mentioned that in the latest ...


24

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 possibly be NULL when used as a vector aggregate) the datatype of MIN(ID) is regarded as nullable (it can still return NULL when used as a scalar aggregate ...


24

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 fancy graph with 0 millisecond latency on it and then point a stapler at you. Instead, ask them about the network path to the SAN. Get speeds, if it's ...


22

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 < 101; If it's happening in the SUM, you need to convert Amount to a BIGINT. SELECT COUNT(*) AS [Records], SUM(CONVERT(BIGINT, t.Amount)) AS [Total] FROM ...


21

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 Assuming you wish to totally remove SQL Server, you should # Stop the service systemctl stop mssql-server.service sudo add-apt-repository --remove "$(curl ...


21

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. So these fail: SELECT name WHERE 1 = 1; SELECT x = 1 WHERE id > 0; Msg 207, Level 16, State 1 Invalid column name 'name'. Msg 207, Level 16, State ...


20

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 when Connect was retired: Please fix the "String or binary data would be truncated" message to give the column name The latest comment there, from Microsoft, ...


19

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 the index on #right to match the order of the filters in the join removes the sort (for me): CREATE CLUSTERED INDEX IX ON #right (c, a, b, d, e, f, g, h) ...


17

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 both of these clauses as independent: <query expression> ::= [ <with clause> ] <query expression body> [ <order by clause> ] [ <...


17

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 any text editor that allows you to find the text "GETDATE()" and replace it with "[dbo].[getlocaldate]()" Run the edited SQL file in Azure SQL to create your ...


17

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 exception is Deferred Name Resolution but that applies to tables, not columns: Deferred name resolution can only be used when you reference nonexistent table ...


16

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 and memory limitations, but the price is right. If it's for production, and you absolutely need to run your own VM, you'll need to buy a production license (...


16

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 should generally not assume that individual cost estimates will strongly correlate with runtime performance on a particular hardware configuration. The point ...


16

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 = 4 /*always false*/ In that case zero rows go into the aggregate but it still emits one as the correct semantics are to return NULL in this case. This is a ...


15

What would be the best way to implement this change? I would work the other way around. Convert all your timestamps in the database to UTC, and just use UTC and go with the flow. If you need a timestamp in a different tz, you can create a generated column using AT TIME ZONE (as you did above) that renders the time stamp in that specified TZ (for the app). ...


15

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 solution Review your windows performance counters for your disks that the warning messages are pointing to, specifically: Disk avg. read time Disk avg. write time ...


15

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 constraint wouldn't have helped anyway. A few methods that avoid rewriting the table's data: CAST the column as NVARCHAR(260) in all codes that uses it. The ...


14

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 of enterprise edition and lot of money by running it on client OS. Plus I believe such issues are mostly bugs so I would suggest you to apply latest SQL ...


14

This is what I call Manual Halloween Protection. You can find an example of it being used with an update statement in my article Optimizing Update Queries. One has to be a bit careful to preserve the same semantics, for example by locking the target table against all concurrent modifications while the separate queries execute, if that is relevant in your ...


14

Index Hint You can use an index hint to do that: SELECT * FROM dbo.Users AS u WITH (INDEX = ix_definitely_an_index) WHERE u.Reputation = 2; The downsides are: Potentially changing a lot of code If you rename an index, this breaks If you change an index definition, it might not be the best index to use anymore Plan Guides You can also use a Plan Guide, ...


14

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 proposed in Joe's answer. ...results in SQL Server rewriting the entire table (and using 2x table size in log space) I'm going to respond to the two parts of ...


13

2017, May: SQL Server 2017 hasn't been released yet (only previews) so no licensing or pricing is available yet (the name v.Next should have been a clue!). 2017, Dec: SQL Server 2017 for Linux was released in October and the Developer Edition is free for development purposes, as in previous versions.


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