New answers tagged

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

Did you rule out using IDENTITY columns? LINK In a IDENTITY field, you specify the starting value and then how much to increment each time, the most common values are (1,1), which seems to match your criteria. It looks like you can add one after the fact too, tried it out with a temp table and it seemed to work, see example below: /* Add IDENTITY to ...


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


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


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


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


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


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.


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


0

I had same issue -- got around it by setting "Trust server certificate" to "True" in "Advanced Properties" of "Connection".


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


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


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.


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


0

You can get most of the information regarding operating system from DMV dm_os_sys_info - Be it memory or CPU or hyperthread. Please run the below T-SQL and you will have details: SELECT cpu_count, * FROM [sys].[dm_os_sys_info] Alternatively you may read from SQL Server errorlog as suggested by Stephen Morris. You may also refer to this thread for more ...


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


0

You can start population of full-text indexes using T-SQL, as outlined in the docs: ALTER FULLTEXT INDEX ON Production.Document START FULL POPULATION; If you want to do this for every full text index in the database, you can run a query like this to generate the script for every full-text index. The output from the below query can be copied into a ...


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


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


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


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


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


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


Top 50 recent answers are included