Hot answers tagged

21 votes
Accepted

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

Since you're just looking for changes, you don't need a cryptographic hash function. You could choose from one of the faster non-cryptographic hashes in the open-source Data.HashFunction library by ...
user avatar
  • 72.4k
17 votes

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

I'm not sure if parallelism will be any / significantly better with SQLCLR. However, it is really easy to test since there is a hash function in the Free version of the SQL# SQLCLR library (which I ...
user avatar
14 votes
Accepted

CLR SQL vs T-SQL for queries

First off, just to be clear: SQLCLR / .NET / C# / VB.NET cannot query the database. Only T-SQL can query SQL Server. So in order for SQLCLR code to get data or interact with SQL Server in any way, it ...
user avatar
13 votes

Storing IP addresses - varchar(45) vs varbinary(16)

how to stored the actual IP address - in text or bytes format. Which is going to be better? Since "text" here refers to VARCHAR(45) and "bytes" refers to VARBINARY(16), I would say: neither. Given ...
user avatar
12 votes

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

This isn't a traditional answer, but I thought it would be helpful to post benchmarks of some of the techniques mentioned so far. I'm testing on a 96 core server with SQL Server 2017 CU9. Many ...
12 votes
Accepted

Access to the path 'c:\some\path' is denied for MSSQL CLR

I have permissions to the database, I'm dbo. I'm an admin on the server. I have permissions in the file system. None of that matters, typically. Unless you (or whoever coded the SQLCLR methods) ...
user avatar
11 votes

Security or performance risks using SQL CLR

The question, as Remus pointed out, is too generic to get an answer as the answer depends on the context of what functionality is to be used and how will it be used. Regarding "Security": If you ...
user avatar
11 votes
Accepted

What is the SQLCLR wait type in Activity Monitor within SQL Server Management Studio (SSMS)?

SQLCLR is the ability to run .NET code within SQL Server. When people speak of SQLCLR they are usually referring to the ability to write custom .NET code (Stored Procedures, Functions, Triggers, User-...
user avatar
11 votes
Accepted

Unknown,AppDomain 66 (master.sys[runtime].65) is Marked for Unload Due to Memory Pressure

Memory architecture was changed in SQL Server 2012 such that there was little need to worry about the MemToLeave setting anymore, especially if using 64-bit SQL Server. And, starting with SQL Server ...
user avatar
10 votes
Accepted

How can "HierarchyID" type work when "CLR" is disabled?

The clr enabled server configuration option only controls whether user assemblies can be run by the SQL Server instance. The hierarchyid, geometry and geography types are system CLR types. These are ...
user avatar
  • 72.4k
10 votes
Accepted

Does SQL Server support custom DOMAINs?

SQLCLR presents a surface area for creating fully custom data types. In fact, that is how SQL Server supports geometric and hierarchy data types. Since SQLCLR is based on the Microsoft.Net common-...
user avatar
  • 67.5k
10 votes
Accepted

Security implications of enabling CLR on shared instance

Given that we are dealing with a shared instance, it should be safe to assume that none of the logins for the "customer" DBs has sysadmin privileges or has been granted the CONTROL SERVER permission. ...
user avatar
9 votes
Accepted

Assembly 'system.web, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog

You can either use Uri.UnescapeDataString (in System), in which case you will also need to do a Replace('+', ' ') on the string before passing it to Uri.UnescapeDataString, or if you would rather not ...
user avatar
9 votes

SQL Parallel Stored Procedure execution using CLR - Performance

Has anyone used CLR for parallelizing Stored Procedures in Critical OLTP Production workloads ? Parallelizing? Yes. For "critical OLTP Production" systems, not really. This is not to say that you can'...
user avatar
9 votes
Accepted

SQL Server 2016 concurrency limitations? Tuning for db concurrency

Before anything else, I think you need to try a few things to cut down on that 12+ hour query: First thing I would check is the index. You have a GROUP BY on TypeNumber, SessionId, dbo....
user avatar
9 votes
Accepted

Does lightweight pooling disable built-in CLR facilities?

Yes. When running in fibre mode, everything that needs the CLR is unavailable. For example, both: SELECT FORMAT(GETDATE(), N'd', N'en-us'); and: DECLARE @g geometry; SELECT @g; ...return the (...
user avatar
  • 72.4k
8 votes

Assembly 'system.web, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog

As you have noted, System.Web is an unsupported library. In order to reference System.Web you will need to make a call to CREATE ASSEMBLY. It seems like you tried that, but how did you reference the ...
user avatar
8 votes
Accepted

How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

This appears to be a duplicate of this question: Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use? However, I do not feel ...
user avatar
8 votes
Accepted

SQL, SQLCLR objects & effective memory utilization

While waiting to hear back on a few questions that I posted in a comment on the Question, I will at least reiterate one of my questions: "What about your current stats leads you to suspect that SQLCLR ...
user avatar
8 votes

Compile TSQL stored procedures into assembly

No, there is no way to do this. And there is not much purpose in doing so anyway as you can easily decompile Assemblies with tools such as ILSpy. The two ways to protect your intellectual property ...
user avatar
8 votes
Accepted

Is it safe to use Strings instead of SqlStrings for CLR function input parameters?

Excellent question. As far as I am aware of, under those conditions (i.e. guaranteed no NULLs and no need for the extra functionality) there shouldn't be any specific concerns. This could be a ...
user avatar
7 votes

Migrating CLR Assemblies

How are you migrating the database? Copying a database to another server via Backup/Restore or Detach/Attach will include the Assemblies as well as the T-SQL wrapper objects that point to the code in ...
user avatar
7 votes

Security or performance risks using SQL CLR

SQLCLR assemblies can be installed with three levels of security access: SAFE | EXTERNAL_ACCESS | UNSAFE. This is amply documented, refer to CREATE ASSEMBLY and Designing Assemblies: Managing ...
user avatar
7 votes
Accepted

AppDomain 2 (mssqlsystemresource.dbo[runtime].1) is marked for unload due to memory pressure message in SQL Server Log

Without discounting anything stated in @Shanky's answer, it should be noted that the specific AppDomain being unloaded is in the [mssqlsystemresource] database. This is an internal database that ...
user avatar
7 votes
Accepted

Assembly is not authorized for PERMISSION_SET=UNSAFE when creating a CLR assembly

Your GRANT statement is incorrect. You only granted the ability to set Assemblies to EXTERNAL_ACCESS, not to UNSAFE. UNSAFE is less restricted than EXTERNAL_ACCESS, and so granting UNSAFE ASSEMBLY to ...
user avatar
7 votes
Accepted

Table with persisted computed CLR originated data marked with has_unchecked_assembly_data = 1, but DBCC CHECKTABLE does not unset this same flag

OP here, answering my own (and first) question! With help from Sean Gallardy, using EXTENDED_LOGICAL_CHECKS pointed me to inconsistencies with the persisted computed column's data. I first tried ...
user avatar
  • 131
7 votes

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

You can probably improve the performance, and perhaps the scalability of all the .NET approaches by pooling and caching any objects created in the function call. EG for Paul White's code above: ...
user avatar
7 votes
Accepted

CREATE ASSEMBLY System.DirectoryServices.AccountManagement.dll without enabling TRUSTWORTHY

No, an Asymmetric Key won't work, most likely because strong-naming assemblies changed in .NET 4.5 or somewhere around there, to now be Enhanced Strong Naming, which is not supported by SQL Server's ...
user avatar
7 votes

Apparently, my CLR assembly function is causing deadlocks?

What version(s) of SQL Server are you using? I do recall seeing a slight change in behavior in SQL Server 2017 not too long ago. I will have to go back and see if I can find where I made a note of it,...
user avatar
7 votes
Accepted

CLR Stored Procedure on Azure SQL Managed Instance error on execution: "Assembly in host store has a different signature than assembly in GAC"

One of the requirements / nuances of working with SQLCLR is that any assembly loaded into both the GAC and SQL Server must be the exact same version (i.e. down to the patch level, not just Major.Minor....
user avatar

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