125

I just overlaid the Microsoft.VisualStudio.Shell.Interop.8.0.dll in C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\PublicAssemblies with the copy from ..\PrivateAssemblies\Interop and the IDE opens. (Thanks to Mitch for discovering the offending DLL name.) This looks like a popular Q & A today, so I'm glad to know this ...


65

As Ronan Ariely reccomends, an official solution has been published which recommends removing a line from ssms.exe.config. In C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe.config: <?xml version="1.0" encoding="utf-8"?> <configuration> <!-- ...snip... --> <runtime> <!-- ......


61

You can not not use transactions in SQL Server (and probably any other proper RDBMS). In the absence of explicit transaction boundaries (begin transaction ... commit) each SQL statement starts a new transaction, which is implicitly committed (or rolled back) after the statement completes (or fails). Transaction simulation suggested by the person who ...


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 ...


34

Think of index design like a sliding switch. You can move this red triangle switch knob anywhere along the line that you want: I don't usually measure it in terms of size - I usually think of it in terms of index quantity, but size would be fine as well. It sounds like your DBA thinks the switch is too far over to the right - that you've added too many ...


33

Datetime is not precise to the level of 1 millisecond. What you are asking for is not possible unless you change to a different datatype (i.e. datetime2). Documentation Important quote: Accuracy Rounded to increments of .000, .003, or .007 seconds


31

Most of the information you will need is going to be in the execution plan ( and the plan XML). Take this query: SELECT COUNT(val) As ColA, COUNT(val2) As ColB, COUNT(val) + COUNT(val2) As ColC FROM dbo.TableA; The execution plan (opened with sentryone plan explorer) shows what steps it went through: With the stream aggregate aggregating ...


31

The documentation for SET NOCOUNT says: SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant ...


30

I up-voted Brent's answer, as his scenario could definitely muddy the water on whether the backup contained TDE data. However, if you've had TDE enabled for a while, it seems that RESTORE FILELISTONLY (Transact-SQL) might provide the information you're after. There is a column on the result set called TDEThumbprint which "Shows the thumbprint of the ...


29

This is known issue and there is new official workaround which published by the SSMS developers team. You can view the open ticket at Microsoft feedback system in the following link: https://feedback.azure.com/forums/908035-sql-server/suggestions/37502512-ssms2018-installed-but-will-not-run Short history: The first workaround which was presented was to ...


28

There are a lot of different ways to do this. I don't usually recommend inserting into a #temp table, since any tempdb load or autogrowth may impact the results, and I definitely don't recommend using a @table variable, since modifications to those are forced serial (no parallel plan can be used), which may change actual query times. Variable Assignment ...


28

One possible scenario that very much amuses me: The rows were originally written when the database didn't have Read Committed Snapshot (RCSI), Snapshot Isolation (SI), or Availability Groups (AGs) enabled RCSI or SI was enabled, or the database was added into an Availability Group During the deletions, a 14-byte timestamp was added to the deleted rows to ...


28

when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB The article you linked to is misleading at best, and incorrect in some places. I think the author was attempting to over-simplify some complicated things, and in doing so went a little too far. SQL Server doesn't move data from memory (the buffer ...


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 ...


27

It might help to rewrite the query like this, so it is obvious that the 2 joins are different, i.e. the joins are to different subsets (of the same table): FROM maintable INNER JOIN secondarytable ON maintable.id1 = secondarytable.a_id1 INNER JOIN table1 ON secondarytable.id2 = table1.id3 AND ...


26

The BOL description of recursive CTEs describes the semantics of recursive execution as being as follows: Split the CTE expression into anchor and recursive members. Run the anchor member(s) creating the first invocation or base result set (T0). Run the recursive member(s) with Ti as an input and Ti+1 as an output. Repeat step 3 until an empty set is ...


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 ...


26

indexing against case insensitive strings yet the case of the data is persisted. How does this actually work? This is actually not a SQL Server specific behavior, it's just how these things work in general. So, the data is the data. If you are speaking about an index specifically, the data needs to be stored as it is else it would require a look-up in the ...


25

Sorry to say but your table structure is difficult to work with considering what you want to do. There are various ways you can probably get the result, one way would be to use UNPIVOT and PIVOT, but it's ugly. You could start by UNPIVOTing the data in TB1 from your columns to rows: select * from tb1 t1 unpivot ( val for col in (A001, A002, A003) )...


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 ...


25

You can use xml:space = "preserve" on the nodes where you want to keep the space. Using xml:space is "only a signal of intent" but SQL server is kind to us here. For one node declare @X xml = '<root> <element xml:space = "preserve"> </element> <element> </element> </root>' select @X; Result: <root> <...


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

You are ordering rows of data, not each column separately. The row (10, 1) comes before the row (1, 10) because of your ORDER BY clause. The Value2 only comes into play when there is a tie on the first column. As further explained by Hellion, as far as the database is concerned, the pair (10, 1) is an indivisible unit: it's not two values, it's one set (...


22

Summary SQL Server uses the correct join (inner or outer) and adds projections where necessary to honour all the semantics of the original query when performing internal translations between apply and join. The differences in the plans can all be explained by the different semantics of aggregates with and without a group by clause in SQL Server. Details ...


22

Indexes remain compressed when rebuilding / reorganizing them. Create table and compressed index CREATE TABLE DBO.TEST_INDX(id int, bla varchar(255)); CREATE INDEX IX1 ON dbo.TEST_INDX(id) WITH (DATA_COMPRESSION = PAGE); Check Compression SELECT i.name, p.data_compression_desc FROM sys.partitions P INNER JOIN sys.indexes I ON I.object_id = P....


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 ...


21

SQL Server chooses to scan the heap tables on the inner side of the loops joins using row-level locks. A full scan would normally choose page-level locking, but a combination of the size of the table and the predicate means the storage engine chooses row locks, since that appears to be the cheapest strategy. The cardinality misestimation deliberately ...


19

While Brent's answer is correct for for all practical purposes, and this is not something I've ever seen someone worry about, it is possible for multiple invocations of a stored procedure in a session to affect each other through a session-scoped #temp table. The good news is it's extremely unlikely to happen in the wild because 1) #Temp tables declared ...


19

This is documented in UPDATE (Transact-SQL): SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column. In your code example, sum is the (unwise) name of a column, not an aggregate. db<>fiddle demo


19

To produce this warning: The maximum used memory must be less than 5% of the granted memory; AND The query must use the regular (not small) resource semaphore To use the regular resource semaphore the query must: Have granted memory over 5MB (5120 KB, 640 x 8KB pages); OR Have a total estimated plan cost of over 3 units and not be a trivial plan Server ...


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