SQL Server – Are SqlServer Sequences Guaranteed to Return Different Values โ€‹โ€‹for Concurrent Transactions?

The documentation states:

A double validation only takes place when a data record is completely filled out. In some cases, this can result in the same number being used for more than one record during creation, but then identified as a duplicate

I do NOT want to use this number in an insert query like this:

INSERT Test.Orders (OrderID, Name, Qty)  
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Tire', 2) ; 

I want to track this number in my web application and create an encrypted text from it. and save the encrypted code in the database. Therefore in my case there is no double check when pasting.

I need to know if two clients are likely to call NEXT VALUE FOR work at almost the same time and get the same values? or not? (I don't need to do that)

SQLServer: Remove trailing spaces from the char column if the value is retrieved by setting a parameter or configuring the server

I have a program that was only used with MySQL until yesterday. As of today I was told that the program must also be compatible with SQLServer. Since I use an ORM (Hibernate), almost everything went well, except for one CHAR Pillar.

In fact, MySQL and SQLServer (configured as they are on my computer) treat them differently CHAR Pillar. My program works well with the MySQL method CHAR, which is:

The length of a CHAR column is fixed to the length you have declared
when you create the table. The length can be any value from 0 to 255.
When CHAR values โ€‹โ€‹are saved, they are padded with spaces on the right
specified length. Trailing spaces are used when retrieving CHAR values
removed, unless the SQL mode PAD_CHAR_TO_FULL_LENGTH is activated.

On the other hand, SQLServer adds trailing spaces to the value when it is saved and does not remove them when it is retrieved.

So my question is: Am I forced to edit my program to deal with such differences, or is there a SQLServer parameter or configuration of the SQLServer server that I know nothing about to simulate MySQL handling CHAR?

SQL Server – SQLServer 2016 continuously increases the stolen memory

I have a suspicion. First, can you open support tickets at Microsoft?

The easiest way to check my suspicions is to collect ( SQLServer: storage node (*) stolen node storage (KB)) for both NUMA nodes and compare the sum with ( SQLServer: storage manager stolen server storage (KB)) . If my suspicions are correct, the discrepancy between the two – which apparently should always match – will be quite large if problems arise. The other telltale feature: up to N-1 SQLOS NUMA nodes can have this relationship (where N is the number of NUMA nodes).
(Database node storage) + (stolen node storage) + (free node storage)> (total node storage)

I describe the problem somewhat in these blog posts.

https://sql-sasquatch.blogspot.com/2018/07/sql-server-2016-memory-accounting.html
https://sql-sasquatch.blogspot.com/2018/10/sql-server-2016-memory-accounting-part.html

The basic problem with billing is that the buffer pool sometimes grows in such a way that buffer descriptor blocks are assigned by SQLOS node A, but the pages referenced in the BDBS actually come from SQLOS node B. The result of this condition is that part of the physical memory controlled by SQLOS is counted twice: on node A (where the BDBS live) the same memory is billed as (database node memory). AND accounted for on SQLOS node B as (Stolen Node Memory). This situation is confusing and inefficient … but it is not yet the full bloom of the problem.

The problem occurs completely if there is so much node B (stolen node storage) as node A (database node storage) that node B (database node storage) drops to ~ 2% from node B (target node storage). In this case, the rate of ( SQLServer: Buffer Manager Free List blocked / sec) rises – we saw 2000 / sec when this happened to us. SQL Server is to attempt to fix the (insufficient (database node storage)) problem on node B by truncating different types of cache on node B. But it can't !! Because the (stolen node storage) is not in one of the various expected cache types.

Temporary resolution: If (total node storage) (target node storage) but (database node storage) approaches 2% (target node storage), run DBCC DROPCLEANBUFFERS.

kb4536005 fixes this problem in SQL Server 2017 CU20 and SQL Server 2019 CU2.
https://support.microsoft.com/en-us/help/4536005/improvement-fix-incorrect-memory-page-accounting-that-causes-out-of-me

There is a similar one sounds Fix in SQL Server 2016 SP2 CU5, kb4470916.
https://support.microsoft.com/en-ca/help/4470916/fix-out-of-memory-error-occurs-when-database-node-memory-kb-drops-belo

However, I don't think kb4470916 solves the double accounting problem. While it can improve the SQL Server response to a single SQLOS node with (database node storage) at a threshold of ~ 2%, I think it leaves open the possibility of nudging the bear due to this double count. And that can be the situation you are in.

However, if the sum of (stolen node storage) between the two nodes always matches (stolen server storage) in the instance, you can forget all of this as if it were a bad dream. ๐Ÿ™‚

SQL Server – SQLServer: "order by" clause causes excessive locking

I can't seem to find an explanation for why this is happening, but while trying to fix a deadlock problem, I found that SQL Server locks far more records than required when I insert an "order by" clause into my query with the updlock and readpast Hints.

For context, I run a "Select Top 1" query on a table that is used to queue jobs in a multi-node application. I want to get the oldest job that has not yet been processed and that meets certain criteria that require joining some tables (all of these tables have the nolock Note) When I sort the list by creation date, all records that match the filter are blocked, but only one is returned. However, if I remove the sorting, only the desired record is blocked.

Any clues as to why this is happening?

java.sql.SQLException: No suitable driver for jdbc: sqlserver found when connecting to the Azure database

I am using an Azure database and have added all the necessary JAR files to my library. Here is my code, I'm not sure why it can not connect. Please advise.

 String connectionUrl =
                "jdbc:sqlserver://bcs430-final-project.database.windows.net:1433;"
                        + "database=OASIS ASSISTANT;"
                        + "user=farmingdale@bcs430-final-project;"
                        + "password=bcs430w!;"
                        + "encrypt=true;"
                        + "trustServerCertificate=false;"
                        + "loginTimeout=30;";




     String insertSql = "select * from dbo.BCS102 where crse = bcs102 ";

        ResultSet resultSet = null;

        try (Connection connection = DriverManager.getConnection(connectionUrl);
                PreparedStatement prepsInsertProduct = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);) {

            prepsInsertProduct.execute();
            // Retrieve the generated key from the insert.
            resultSet = prepsInsertProduct.getGeneratedKeys();

            // Print the ID of the inserted row.
            while (resultSet.next()) {
                System.out.println("Generated: " + resultSet.getString(1));
            }
        }
        // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        }

here is the mistake

java.sql.SQLException: No suitable driver found for jdbc:sqlserver://bcs430-final-project.database.windows.net:1433;database=OASIS ASSISTANT;user=farmingdale@bcs430-final-project;password=bcs430w!;encrypt=true;trustServerCertificate=false;loginTimeout=30;
    at java.sql.DriverManager.getConnection(DriverManager.java:689)
    at java.sql.DriverManager.getConnection(DriverManager.java:270)
    at SeniorProject.main(SeniorProject.java:35)