A Strange Operation Problem in SQL Server: -100/-100*10 = 0

Issue converting JSON string into decimal

This unusual format is a rather usual scientific notation Wikipedia, read section "E-notation"

You see the E and a number meaning exponent.

"1E2"  = 1 * 10^2 = 100
"1E-2" = 1 * 10^(-2) = 0.01

Try this out:

DECLARE @tbl TABLE(Numberstring VARCHAR(100));
INSERT INTO @tbl VALUES('100'),('1E2'),('1E-2'),('7E-05');
SELECT Numberstring
      ,CAST(Numberstring AS FLOAT) 
      ,CAST(CAST(Numberstring AS FLOAT) AS DECIMAL(20,10)) 
FROM @tbl;

The result

100     100     100.0000000000
1E2     100     100.0000000000
1E-2    0,01    0.0100000000
7E-05   7E-05   0.0000700000

You can see, that the FLOAT type itself will display the last one in the scientific notation, while the cast to DECIMAL will return the number you are expecting.

I'd be happy with an upvote, but you should accept Shawn's answer as it was earlier than mine :-D

SQL Server 2016 Enterprise poor performance

Not disregarding all your answers that were very useful and which I applied or will apply, the biggest problem was not easy to find.

The problem got worse in the days after our last messages.

As we are based on cloud, neither I nor the company that manages the infrastructure and gives us support has access to the physical hosts.

Something made me wonder when I noticed that some days the processor was on average at 20% and other days it was much higher, over 60%, when the workload, although never exactly the same, is similar. There are the same number of people performing more or less the same type of operations.

Earlier this week, users started to get stuck for several minutes and only the processor was strangled. I asked several users to log out (those who were spending more resources but still nothing out of the ordinary), I turned off various services linked to the database, and in the end nothing has changed. I asked the sysadmin that supports us and that can communicate with the guys of our cloud to remote to my machine to see what I was seeing and to help me find something, because I could not do better to find the problem.

The technician also did not find anything. He finally started to give me some reason that something else had to be causing this problem and was when he contacted the cloud. In the cloud, they did not realize anything, just that because there is configured load balancing between physical hosts, the VM that supports our SQL Server had been moved a few times that day between physical hosts. Fortunately, I told our technician exactly at what time the problems began to occur that day, which coincided with the time the VM had been moved the last time to one of the physical hosts from which it had not left the rest of the day.

If the technician had not followed closely this problem, this was going to be more one of those times when he could even talk to the cloud guys, but when they saw performance samples, they would not get anything, because once again the cloud only saw samples with CPU on the order of 40/50%, when in fact it was on average above 80% and often stuck at 100%.

Now the machine is standing on a physical host (not moving between hosts) and although we have not yet achieved the perfect performance, everyone is working and giving a lot more positive feedback, because the average CPU is about 20% with all our users and services.

In the meantime, we also put tempdb on another disk (it was on the Operating System disk) and we increased the files, to be more in agreement with the number of cores of the CPUs.

The number of cores were also adjusted based on the recommendations of sp_Blitz.

There was also an automatic routine that was running all day based on an old date ... and since it did not end in the morning when we arrived, and we have no way to check whether it is running or not, I still started to run manually. But probably the other was still running and were running twice during that time. We've changed the date to reduce the time it takes, and now it's late at night. But this was not the solution, as it was solved before many problems we had as the one described here.

We also managed to get the ERP assistant to schedule a meeting with the manufacturer, so we are going to show our system and look for suggestions, as well as clarify some doubts, as there are recommendations in the training videos that are contrary to most of recommendations, including Microsoft itself, such as Priority Boost on and Fill Factor 70%.

Since the application also has a maintenance screen, I'll look for the required periodicity of these maintenance, and what's left to do outside the application. My idea is to use Ola Hallengren's plans.

I believe that Thomas Kronawitter answer is absolutely correct and I'm applying it, however, I think this description can be important to other people that after following all the good practices still can't fix the problem because it can be in the physical hosts. Thanks Thomas.

Getting the minimum of two values in SQL

Use Case:

   Select Case When @PaidThisMonth < @OwedPast 
               Then @PaidThisMonth Else @OwedPast End PaidForPast

As Inline table valued UDF

CREATE FUNCTION Minimum
(@Param1 Integer, @Param2 Integer)
Returns Table As
Return(Select Case When @Param1 < @Param2 
                   Then @Param1 Else @Param2 End MinValue)

Usage:

Select MinValue as PaidforPast 
From dbo.Minimum(@PaidThisMonth, @OwedPast)

ADDENDUM:
This is probably best for when addressing only two possible values, if there are more than two, consider Craig's answer using Values clause.

Access is denied when attaching a database

Thank you for all of the comments. Some of you helped to lead me to the answer. Here's what I found:

It was an NTFS permission problem, and not a SQL problem. Further, it looks kind of bug-like (and it's repeatable).

The problem:
The account that I was using had full control NTFS permissions to the mdf and ldf files. However, it had those permissions through group membership (the Local Administrators group had permissions, and my account is a member of local admins). (I verified the permissions)

If I try to do the attach, connect to SQL Server as me (where I am in the admins group), it fails with the NTFS problem.

However, if I grant the same file permissions that the local admin group has directly to my Domain Account, then I can attach with no problems.

(oh, and yes, I checked the local groups on this machine, and I verified that my domain account is indeed a member of the local admins group).

So, it looks as though the error occurs because some code (either in SQL Server or Management Studio) checks for the permissions that the user account holds, but it doesn't go so far as to check group permissions that the user account inherits.

That sounds weird to me, but I can reproduce it over and over again, so I have concluded that it is the answer.

Update: I reported this as a bug: https://connect.microsoft.com/SQLServer/feedback/details/539703/access-denied-attaching-a-database-when-permissions-are-inherited

Reduce deadlock on PAGE level on update query on MS SQL

I finally have to do a workaround by using cusror in a stored procedure.

But it is still interesting that how the PAGE lock happen and how to resolve.

After some more search on Google, there are some other people have the same problem and they(from MSDN forum) suggest to turn off the parallelism in SQL Server 2005 but I never get a chance to try.