I want to pull logs from the server, if the test ended up with FAILED status, tell me please what the signature of a function or fixture should look like, which will be called after each test and have information about its status.

I run the script like this

py.test -s -vv -l --alluredir allure / main.py

The driver used for the database connection should have connection settings that govern transaction behavior. The pyodbc driver for python, for example, has the “autocommit” setting. With autocommit set to “True”, the SQL Server session will autocommit transactions - each statement that opens a transaction will autocommit that transaction as it completes. Using the pyodbc driver, if autocommit is set to “False” the session will have implicit_transactions ON. A number of statements implicitly begin a transaction (unless the session is already within a transaction) in this mode. Note that some SELECT statements may avoid starting a transaction if they do not specify a table. Implicit transactions do not commit automatically when the statement which began them completes. Implicit transactions require a commit or rollback. More information about pyodbc here - but if using a different driver look for that driver’s documentation. https://github.com/mkleehammer/pyodbc/wiki/Database-Transaction-Management

More information about implicit transactions here… https://docs.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=sql-server-ver16

Let's use a string trick. Try this:

SELECT LENGTH("0,1,1") - LENGTH(REPLACE("0,1,1", "1", ""));

It returns 2.

So:

SELECT LENGTH(A) - LENGTH(REPLACE(A, "1", "")) AS A_count,
       LENGTH(B) - LENGTH(REPLACE(B, "1", "")) AS B_count,
       LENGTH(C) - LENGTH(REPLACE(C, "1", "")) AS C_count
    FROM ...

If you had a bit string, see BIT_COUNT(). If you have a character string with 0s and 1s, BIT_COUNT(convert(my_str, 2, 10));

Consider writing a Stored Function to encapsulate the tricky code.

I was able to fix this by praying and then: Alter the table 'msdb.dbo.sysssispackages' to allow column 'packagedata' to allow null values.

In my case here this error was on a clustered database server, so I failed back to the node that was not upgraded yet and was able to alter the table.

If this had been a stand along server, I could have copied the msdb database file and log file to test server and attached it to get it mounted and made the changes. Then it would be as simple as to detach the database and copying it back to the original server, then try the startup/upgrade again.

Next time I upgrade a sql 2014 instance to (SP3-CU4-GDR - KB4583462) - 12.0.6433.1, I will likely alter this table a head of the upgrade.

Creating and dropping is basically equivalent to VACUUM FULL. The lock will be held for less time, but that is because changes not locked out during the copy will be discarded when the drop is done, so that is probably not much of a win. If the new table is a tiny fraction of the old one, the WAL generated (either way) should be small.

The most efficient alternative is probably not to get in a situation where VACUUM FULL is needed in the first place. Knowing how you got here could be important to getting out of it. For example, maybe just reindexing some of the indexes would be sufficient.

By default, SQL Server allows a maximum of 32767 concurrent connections which is the maximum number of users that can simultaneously log in to the SQL server instance.

I am developing 10 web apis that will query the sql server using their own logins (so max 10 logins, plus admin/devs). Expected number of concurrent logins is not beyond 20.

However each login (api) can make several (maybe 500) concurrent query requests. So effectively 10 apis * 500 req = 5000 concurrent query requests. There will be times when there are less or no requests.

Assuming there is sufficient memory and disk io capability, I am planning the cpu requirements.

I understand sql request is assigned to the worker thread and based on number of processors there is a certain number of default worker threads. Presently my dev machine has 24 processors so the default max worker thread is 832.

Assuming cost threadhold (of 40) may be exceeded by the queries, which means the sql server may decide to use parallelism (max dop).

Assuming MAX DOP = 1 then 832 requests can be handled at a time.

Assuming MAX DOP = 4 then 208 requests can be handled at a time.

Any query requests beyond this will have to wait until it is allocated a worker thread.

So to ensure peak load of 5000 requests can be satisfied, is it correct to estimate that I will require roughly atleast 145 cpus?

((145-4)*32)+512 = 5024

After running the query, I am getting 4 rows of output. How to transfer the same into an Excel file. I know I can do this manually, but there should be a method.

Unable to figure out how to access command line interface so that I can try with this code as per the above StackExchange post:

    sqlite> .mode csv sqlite> .output test.csv sqlite> select * from tbl1; sqlite> 
.output stdout

Where is tnx_order coming from? You have additional parenthesise and you can’t return asc or desc within the case statement.

You might want

ORDER BY CASE WHEN tnx_order THEN txn.id ELSE NULL END ASC
, CASE WHEN !tnx_order THEN txn.id ElSE NULL END DESC
LIMIT 1;

If tnx_order is a variable then you’d probably be better off having two different queries with the alternate order by clauses and have your application decide which to run. This would better take advantage of indexes to provide no sort plans.

I'm investigating this statement performance on SQL Server 2019 (I've extracted the important part):

   UPDATE 
        m
   SET
        A1 = src.A1,
        A2 = src.A2,
        A3 = src.A3,
        A4 = src.A4,
        A5 = src.A5
   FROM 
      MyTable m 
      JOIN SourceTable src ON m.Id = src.Id
   WHERE 
        A1 <> src.A1
    OR  A2 <> src.A2
    OR  A3 <> src.A3
    OR  A4 <> src.A4
    OR  A5 <> src.A5

• There's no triggers
• Row count is fairly small (around 200k-300k)
• Table is mostly read from, but frequently
• The A1/5 aren't under index, they're mostly nvarchar(50) or similar
• No replication setup

Is it worth it to do the WHERE part to prevent the unnecessary updates? If not - which use-case would make it worth it?

Sql server attach prerequisite states that a db must be detached before trying to attach it to another instance ( https://docs.microsoft.com/en-us/sql/relational-databases/databases/attach-a-database?view=sql-server-ver15#Prerequisites )

However this migration link ( https://docs.microsoft.com/en-us/sql/database-engine/install-windows/choose-a-database-engine-upgrade-method?view=sql-server-ver15#migrate-to-a-new-installation ) recommends an attach without any mention about detach. Example:

What is the implication at attempting an attach without detach?

I have some data like this:

And I want to compare the string values in both columns on the folowing condition: they're similar (assign some value, like 1) if they share at least 2 chars. Otherwise, they're not similar.

So in the example, PPS and PSP would be similar.

How can this substring comparison be achieved?

I know one approach would be to extract substrings and manually compare them, but it feels hacky and I don't know the maximum number of chars that can occur.