8

Trying to clarify a bit: The danger isn't that somebody else will get to SQL Server's sensitive data (the data in your databases). It is the other way around. Say you have an Excel sheet with sensitive information in it. This document is deleted. The bits are still on disk. SQL Server grows a database file and gets that disk space. Somebody uses DBCC PAGE to ...


7

SQL Server's string handling functions are a bit inconsistent when dealing with long strings, but if you take care to make sure input to be searched is NVARCHAR(MAX) then the REPLACE() function will work over data longer than 8,000 bytes (4,000 characters for 'N'-types). This means that you can manipulate long NTEXT values by casting them to NVARCHAR(MAX) ...


6

Partitioning adds the partition number as an implied leading key to partitioned indexes. Your clustered index on: [MANDT] ASC, [MBLNR] ASC, [MJAHR] ASC, [ZEILE] ASC becomes: [<partition_id>], [MANDT] ASC, [MBLNR] ASC, [MJAHR] ASC, [ZEILE] ASC That index cannot deliver the order needed for: SELECT TOP (4000) * FROM dbo.MS ORDER BY MANDT, MBLNR, MJAHR, ...


6

El Filter The reason your plan changed is because the cardinality estimate for how many rows will pass through the filter operator changed pretty drastically. This is sort of a tough thing to estimate generally -- you're using a left join to find rows that don't exist in #activitiesfiles -- which is often better expressed using NOT EXISTS. FROM [TALBOTS_RAW]...


6

The question doesn't contain a reproduction script, but this error often arises because an implied predicate makes the join predicate redundant. In other words, a logical implication in your query specification turns the inner join into a logical cross product (after simplification). This is not necessarily a bad thing (as people tend to assume cross ...


5

SQL Server licensing (PDF license guide) requires that you license all cores (all cores installed on the physical server, or all vCores visible in the VM), up to the max limit of the SQL Server edition. In the case of SQL Server Web Edition, the enforced CPU maximum is the lesser of 4 sockets or 16 cores. If your server has 16 cores, SQL Server will use all ...


4

Nope. You can see the full feature list here. There were several things opened up for 2016 in Standard with the release of SP1. TDE was not one of them.


3

In case you could use CLR functions, you can use this one: [Microsoft.SqlServer.Server.SqlFunction] [return: SqlFacet(MaxSize = -1)] public static SqlString fnReplace(SqlString value, SqlString oldValue, SqlString newValue) { return new SqlString(((string)value).Replace(oldValue.ToString(), newValue.ToString())); } Using your sample data this is the ...


2

The question is tagged with SQL Server 2016. This means you have FOR JSON. JSON encoding properly escapes special characters, including linefeeds. If the consumer can accept JSON you're done. If not a bit of PowerShell / Python / whatever will be able to replace \r\n with @@, and convert to CSV, more easily than TSQL will. Here's the setup. I've copied all ...


2

The best way to see how many CPU's the SQL server is using is to check sys.dm_os_schedulers or the startup message in the errorlog, which will not report on any CPU's that have been hot added. SELECT count (cpu_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 1048576 AND is_online = 1


2

Given that you were delivered a scripted out assembly instead of a DLL (and the developer wasn't kind enough to sign it, that we know of at the moment), that won't be super easy to sign at the OS-level (the preferred method) as you would first need to create a real binary file out of that assembly (there are ways, just not built into Windows). So, in this ...


2

You could consider a Full File Backup strategy. This is primarily of benefit in your scenario if the database is using the full recovery model. This alternative can be more challenging to set up and maintain, but offers advantages that seem relevant to your needs. Quoting from the documentation link above: Benefits of File Backups File backups offer the ...


2

You could just do a full backup less frequently, then regular differential and/or transaction log backups. These would be very small if little has changed. The size of the diffs will grow larger and larger as more and more data changes. They are not incremental, they include all changes since the last full backup. Restoring would take a little longer, as you ...


2

I am not going to mark any file or file group as read only. Will I still be able to do a partial backup on just the files No. A minimal restore sequence includes all read/write filegroups. And so "a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files" if an old ...


2

First off, SQL Server does not encrypt data by default, neither at rest nor at move. TLS/SSL can be used to encrypt data at move, at rest can either use TDE or column-based encryption. What's more, one can use disk encryption such as Bitlocker to protect the disks against physical attacks. Always Encrypted is also an option for cases in which one doesn't ...


2

The log can't be reused until a database checkpoint writes all the dirty pages to the data files, and automatic checkpoints don't happen on a fixed schedule: The time interval between automatic checkpoints can be highly variable. A database with a substantial transaction workload will have more frequent checkpoints than a database used primarily for read-...


2

Typically the WHERE clause is processed before the GROUP BY, so think about it in terms of which will have the most effect. If you've got 1,000,000 rows in your table and your WHERE clause will filter most of them out, then index for the WHERE clause (first), and specifically on the columns that will be most effective at filtering out more rows. If the WHERE ...


1

Just to clarify a minor thing, SQL Server doesn't usually create timeouts, rather the calling client is what creates the timeout when it exceeds its wait time threshold while waiting for a response from SQL Server. This threshold is configured by the calling client itself, and can vary from one application to another. The only exception to this, is when SQL ...


1

How does this lead to information disclosure when SQL server encrypts the data at rest? It doesn't. SQL Server encrypts data at rest using Transparent Data Encryption (TDE), and TDE disables Instant File Initialization. Some feature usage, such as Transparent Data Encryption (TDE), can prevent Instant File Initialization. Database Instant File ...


1

I will use system dynamic management view sys.dm_os_schedulers. Take a not of status and is_online column, which will tell you which CPU's are being used by SQL Server. Please note that SQL Server 2016 Web edition has limitations about 'Maximum compute capacity'. See Microsoft documentation Editions and supported features of SQL Server 2016.


1

Check the SQL Server Error log - a message is written into the log when the SQL Service starts up, for example, this is from my laptop this morning :- SQL Server detected 1 sockets with 4 cores per socket and 8 logical processors per socket, 8 total logical processors; using 8 logical processors based on SQL Server licensing. This is an informational message;...


1

Grouping is best done with a sorted input, so we really want to maintain ordered reading from the index. This means that inequality columns cannot be used, as this will mess up the ordering. Equality columns should still come first. Breaking down your current query, we can try and estimate which columns would be most useful: WHERE varcharColumn <> '' --...


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