Currently I have a windows failover cluster with a single node only, because the second node will only become available on an production go live date. Prior to this, I was wondering if it would be possible to create the AlwaysOn Availability Group with just the Primary replica. This is in order that I could bring a listener online, in order address various configurations, in order to reduce the tasks required on the go live date. Thank you.

I understand that if we are using HDD then it will be advisable to store the data file and transaction log on different drives (which point to different disks).

However, if the server is using SAN, then whether it's still advisable to put them in different drives?

I have checked with our infra team, our SAN is using flash cards (there are 16 drives in a storage pool where we create logical volumes which is distributed to connected servers).

I'm trying to store the German ß in SQL Server, but it results in ß. For example "Gleimstraße" is stored as "Gleimstraße"

But this also happens for other characters:

• "König-Karl-Stra­ße" stored as "König-Karl-Straße"
• "Quai André-Citroën" stored as "Quai Andrà Citroën"
• "Carrer dels Adreçadors stored as "Carrer dels Adreçadors"

I looked at the database collation and it shows SQL_Latin1_General_CP1_CI_AS. I then Googled and found https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.Collation.html .

But I have no idea if my existing collation is causing the issue, or what I need to do to be able to store the special characters like ö, ß, é, ë, ç, ñ, í.

UPDATE 1

Indeed, I'm storing the strings in an nvarchar column. I send the data to the database via my .NET application. This is what I have in my web.config as the connection string:

UPDATE 2

I read values from a JSON file which I've passed into a Newtonsoft.Json.Linq.JArray and then pass it to a function, like below:

    InsertStreetId(item.SelectToken("location.street").Value(Of String))
Public Shared Function InsertStreetId(ByVal street As String) As Integer

Dim streetId As Integer
    Dim myConnection As SqlConnection = GetConnection()
    Dim cmd As New SqlCommand("INSERT INTO geo_streets(streetname) VALUES (@streetname)" +
                         ";SELECT CAST(scope_identity() as int);", myConnection)
    cmd.Parameters.Add(New SqlParameter("@streetname", street))

    Try
        myConnection.Open()
        streetId = CInt(cmd.ExecuteScalar)
    Catch ex As Exception
    Finally
        myConnection.Close()
    End Try
    Return streetId
End Function

I have a csv that contains Boolean variables (yes,no,blanks). How can I recode blanks so that it'd integrate in the data type. Or fo i have to change data type for blanks to be readable?

I am having issue with Python code leaving idle connections to SQL Server while having open transactions in them.

Background

Recently, we had some major blockings on the SQL Server database due to Python connections. The login used has read-only access to SQL Server. The Python users are performing only reads on the database so they do not necessarily need to open transaction. However, it seems there is some default setting in Python that creates the open transaction. While users tend to leave their connections open, so we end up having resources locked sometimes from these idle connections.

Question: I manage SQL Server database and developers own Python code. Need suggestion on how developers can change their settings in Python to avoid transactions for read-only queries. One of the solution I have is to kill their connections when a particular job needs to run so as to avoid blocking but I do not prefer this approach.

Either of your shown queries should return two rows, as expected:

db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=f9d066ef2a3155dfd9995aaf36896f80

If you see a different result, then the likely cause is index corruption. Test with:

SELECT id, created_at
FROM collection
WHERE (created_at + interval '1 ms', id)
    < ('2022-05-23 23:07:22.592','d35c6bb8-06dd-4b86-b5c6-d123340520e2')
ORDER BY created_at + interval '1 ms' DESC, id DESC
LIMIT 3;

That "disables" index support and gets the result of a sequential scan. See:

• https://dba.stackexchange.com/questions/292185/text-column-compares-equal-to-where-clause-but-does-not-select-matching-row/292200#292200

If so, fix with:

REINDEX TABLE collection;

Or just the involved index on (created_at, id).

I have hardly ever seen index corruption myself. Typically, there is a troubling root cause, like failing hardware (RAM, storage) or a very old version of Postgres. Try to find and fix the cause.

This might be a good time for a backup of your database first.

Related:

• https://dba.stackexchange.com/questions/219782/efficient-pagination-for-big-tables/219810#219810

SQL Server adds this 14 byte overhead as internal information. You need to use special command to see the overhead, as noted by MBuschi.

Note that the overhead is not added until you modify a row, after you turned on RCSI.

These 14 bytes is a combo of two pieces of information:

1. XSN (Transaction Sequence Number), which identifies the transaction that performed the modification. This is 6 bytes.
2. RID, a pointer to the versioned row. This is 8 bytes.

https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide#space-used-in-data-rows

I'm working in a SQL Server 2016 database that is not of my design nor can I fundamentally change the security structure. I know it is likely trash/worst practice but I have to play the hand I am dealt at the moment. I'm trying to restrict access to a schema to only members of a role, but everyone seems to have db_datareader membership. Here are the details:

We will be ETL'ing some new data into their own tables within this existing database. All these "new" tables need to be access restricted so only certain users can view. Here is my thinking (using fake names):

1. Create new schema called 'secret_schema'
2. Create 2 new roles: 'secret_schema_owner' (owner = dbo) and 'secret_schema_reader' (owner = 'secret_schema_owner')
3. Set owner of 'secret_schema' to 'secret_schema_owner' role
4. GRANT SELECT ON SCHEMA::secret_schema TO secret_schema_reader

The goal here is that only members of the 'secret_schema_reader' role will be able to view the data in tables within the 'secret_schema' schema. The problem is that the prior managers of this database just granted users 'db_datareader' to the database and that seems to trump my attempted role security.

Do I have any options besides a total overhaul of the security/permissions structure?

You're missing the OS access permissions for the oracle OS account.

Make sure the OS user oracle can run it before trying to run it from DBMS_SCHEDULER.

(Adjust account name according to the username => 'xxx' settings)

Let's suppose we have:

select
    o_orderpriority,
    count(*) as order_count
from
    orders
where
    o_orderdate>='01/07/1993'and o_orderdate

from TPC-H benchmark. These are the tables:

CREATE TABLE LINEITEM
 ( 
L_ORDERKEY INTEGER REFERENCES ORDERS(O_ORDERKEY),
L_PARTKEY INTEGER REFERENCES PART(P_PARTKEY),
L_SUPPKEY INTEGER REFERENCES SUPPLIER(S_SUPPKEY),
L_LINENUMBER INTEGER,
L_QUANTITY INTEGER,
L_EXTENDEDPRICE NUMERIC (12,2),
L_DISCOUNT NUMERIC (12,2),
L_TAX NUMERIC (12,2),
L_RETURNFLAG CHAR (1),
L_LINESTATUS CHAR (1),
L_SHIPDATE DATE ,
L_COMMITDATE DATE ,
L_RECEIPTDATE DATE ,
L_SHIPINSTRUCT CHAR (25),
L_SHIPMODE CHAR (10),
L_COMMENT CHAR (44),
L_PARTSUPPKEY CHAR (20) REFERENCES PARTSUPP(PS_PARTSUPPKEY)
) 

and

CREATE TABLE ORDERS
 ( 
O_ORDERKEY INTEGER PRIMARY KEY ,
O_CUSTKEY INTEGER REFERENCES CUSTOMER(C_CUSTKEY),
O_ORDERSTATUS CHAR (1),
O_TOTALPRICE NUMERIC (12,2),
O_ORDERDATE DATE ,
O_ORDERPRIORITY CHAR (15),
O_CLERK CHAR (15),
O_SHIPPRIORITY INTEGER,
O_COMMENT CHAR (79)
) 

Explain Analyze returns:

"Finalize GroupAggregate  (cost=734234.44..734235.71 rows=5 width=24) (actual time=14682.540..14683.017 rows=5 loops=1)"
"  Group Key: orders.o_orderpriority"
"  InitPlan 1 (returns $1)"
"    ->  Nested Loop  (cost=0.45..91327112.22 rows=19995359 width=0) (actual time=0.803..0.804 rows=1 loops=1)"
"          ->  Seq Scan on lineitem  (cost=0.00..2144850.95 rows=19995359 width=4) (actual time=0.240..0.240 rows=1 loops=1)"
"                Filter: (l_commitdate < l_receiptdate)"
"          ->  Memoize  (cost=0.45..4.99 rows=1 width=4) (actual time=0.560..0.561 rows=1 loops=1)"
"                Cache Key: lineitem.l_orderkey"
"                Cache Mode: logical"
"                Hits: 0  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB"
"                ->  Index Only Scan using orders_pkey on orders orders_1  (cost=0.43..4.98 rows=1 width=4) (actual time=0.555..0.555 rows=1 loops=1)"
"                      Index Cond: (o_orderkey = lineitem.l_orderkey)"
"                      Heap Fetches: 1"
"  ->  Gather Merge  (cost=734229.43..734230.60 rows=10 width=24) (actual time=14682.524..14682.999 rows=15 loops=1)"
"        Workers Planned: 2"
"        Params Evaluated: $1"
"        Workers Launched: 2"
"        ->  Sort  (cost=733229.41..733229.42 rows=5 width=24) (actual time=14676.302..14676.303 rows=5 loops=3)"
"              Sort Key: orders.o_orderpriority"
"              Sort Method: quicksort  Memory: 25kB"
"              Worker 0:  Sort Method: quicksort  Memory: 25kB"
"              Worker 1:  Sort Method: quicksort  Memory: 25kB"
"              ->  Partial HashAggregate  (cost=733229.30..733229.35 rows=5 width=24) (actual time=14676.265..14676.266 rows=5 loops=3)"
"                    Group Key: orders.o_orderpriority"
"                    Batches: 1  Memory Usage: 24kB"
"                    Worker 0:  Batches: 1  Memory Usage: 24kB"
"                    Worker 1:  Batches: 1  Memory Usage: 24kB"
"                    ->  Result  (cost=0.00..732048.00 rows=236260 width=16) (actual time=7272.919..14638.858 rows=191224 loops=3)"
"                          One-Time Filter: $1"
"                          ->  Parallel Seq Scan on orders  (cost=0.00..732048.00 rows=236260 width=16) (actual time=7272.917..14628.994 rows=191224 loops=3)"
"                                Filter: ((o_orderdate >= '1993-07-01'::date) AND (o_orderdate < '1993-10-01'::date))"
"                                Rows Removed by Filter: 4808776"
"Planning Time: 6.722 ms"
"Execution Time: 14683.864 ms"

• How can I speed up this query (and its subquery) using indexes, materialized views and/or other stuff?
• If I change char(n) datatype to text, would I get better performances?

EDIT

CREATE INDEX o_orderdate_c_idx ON orderdate
    USING btree (o_orderdate ASC NULLS LAST)
ALTER TABLE orderdate CLUSTER ON o_orderdate_c_idx
"Finalize GroupAggregate  (cost=734234.44..734235.71 rows=5 width=24) (actual time=9026.877..9027.505 rows=5 loops=1)"

"  Group Key: orders.o_orderpriority"

"  InitPlan 1 (returns $1)"

"    ->  Nested Loop  (cost=0.45..91327077.11 rows=19995351 width=0) (actual time=0.025..0.025 rows=1 loops=1)"

"          ->  Seq Scan on lineitem  (cost=0.00..2144850.65 rows=19995351 width=4) (actual time=0.010..0.010 rows=1 loops=1)"

"                Filter: (l_commitdate < l_receiptdate)"

"          ->  Memoize  (cost=0.45..4.99 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)"

"                Cache Key: lineitem.l_orderkey"

"                Cache Mode: logical"

"                Hits: 0  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB"

"                ->  Index Only Scan using orders_pkey on orders orders_1  (cost=0.43..4.98 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)"

"                      Index Cond: (o_orderkey = lineitem.l_orderkey)"

"                      Heap Fetches: 1"

"  ->  Gather Merge  (cost=734229.43..734230.60 rows=10 width=24) (actual time=9026.871..9027.498 rows=15 loops=1)"

"        Workers Planned: 2"

"        Params Evaluated: $1"

"        Workers Launched: 2"

"        ->  Sort  (cost=733229.41..733229.42 rows=5 width=24) (actual time=9023.327..9023.328 rows=5 loops=3)"

"              Sort Key: orders.o_orderpriority"

"              Sort Method: quicksort  Memory: 25kB"

"              Worker 0:  Sort Method: quicksort  Memory: 25kB"

"              Worker 1:  Sort Method: quicksort  Memory: 25kB"

"              ->  Partial HashAggregate  (cost=733229.30..733229.35 rows=5 width=24) (actual time=9023.294..9023.295 rows=5 loops=3)"

"                    Group Key: orders.o_orderpriority"

"                    Batches: 1  Memory Usage: 24kB"

"                    Worker 0:  Batches: 1  Memory Usage: 24kB"

"                    Worker 1:  Batches: 1  Memory Usage: 24kB"

"                    ->  Result  (cost=0.00..732048.00 rows=236260 width=16) (actual time=4222.574..8991.249 rows=191224 loops=3)"

"                          One-Time Filter: $1"

"                          ->  Parallel Seq Scan on orders  (cost=0.00..732048.00 rows=236260 width=16) (actual time=4222.572..8981.901 rows=191224 loops=3)"

"                                Filter: ((o_orderdate >= '1993-07-01'::date) AND (o_orderdate < '1993-10-01'::date))"

"                                Rows Removed by Filter: 4808776"

"Planning Time: 3.580 ms"

"Execution Time: 9027.807 ms"

EDIT 2

                      Table "public.orders"
     Column      |     Type      | Collation | Nullable | Default 
-----------------+---------------+-----------+----------+---------
 o_orderkey      | integer       |           | not null | 
 o_custkey       | integer       |           |          | 
 o_orderstatus   | character(1)  |           |          | 
 o_totalprice    | numeric(12,2) |           |          | 
 o_orderdate     | date          |           |          | 
 o_orderpriority | character(15) |           |          | 
 o_clerk         | character(15) |           |          | 
 o_shippriority  | integer       |           |          | 
 o_comment       | character(79) |           |          | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (o_orderkey)
    "o_orderdate_c_idx" btree (o_orderdate) CLUSTER
Foreign-key constraints:
    "orders_o_custkey_fkey" FOREIGN KEY (o_custkey) REFERENCES customer(c_custkey)
Referenced by:
    TABLE "lineitem" CONSTRAINT "lineitem_l_orderkey_fkey" FOREIGN KEY (l_orderkey) REFERENCES orders(o_orderkey)

and EXPLAIN(ANALYZE, BUFFERS, SETTINGS) returns:

"Finalize GroupAggregate  (cost=734234.44..734235.71 rows=5 width=24) (actual time=13542.359..13542.849 rows=5 loops=1)"
"  Group Key: orders.o_orderpriority"
"  Buffers: shared hit=19 read=638299"
"  InitPlan 1 (returns $1)"
"    ->  Nested Loop  (cost=0.45..91327077.11 rows=19995351 width=0) (actual time=0.681..0.682 rows=1 loops=1)"
"          Buffers: shared hit=2 read=4"
"          ->  Seq Scan on lineitem  (cost=0.00..2144850.65 rows=19995351 width=4) (actual time=0.187..0.187 rows=1 loops=1)"
"                Filter: (l_commitdate < l_receiptdate)"
"                Buffers: shared read=1"
"          ->  Memoize  (cost=0.45..4.99 rows=1 width=4) (actual time=0.492..0.492 rows=1 loops=1)"
"                Cache Key: lineitem.l_orderkey"
"                Cache Mode: logical"
"                Hits: 0  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB"
"                Buffers: shared hit=2 read=3"
"                ->  Index Only Scan using orders_pkey on orders orders_1  (cost=0.43..4.98 rows=1 width=4) (actual time=0.487..0.487 rows=1 loops=1)"
"                      Index Cond: (o_orderkey = lineitem.l_orderkey)"
"                      Heap Fetches: 1"
"                      Buffers: shared hit=2 read=3"
"  ->  Gather Merge  (cost=734229.43..734230.60 rows=10 width=24) (actual time=13542.294..13542.783 rows=15 loops=1)"
"        Workers Planned: 2"
"        Params Evaluated: $1"
"        Workers Launched: 2"
"        Buffers: shared hit=19 read=638299"
"        ->  Sort  (cost=733229.41..733229.42 rows=5 width=24) (actual time=13537.578..13537.579 rows=5 loops=3)"
"              Sort Key: orders.o_orderpriority"
"              Sort Method: quicksort  Memory: 25kB"
"              Buffers: shared hit=17 read=638295"
"              Worker 0:  Sort Method: quicksort  Memory: 25kB"
"              Worker 1:  Sort Method: quicksort  Memory: 25kB"
"              ->  Partial HashAggregate  (cost=733229.30..733229.35 rows=5 width=24) (actual time=13537.542..13537.543 rows=5 loops=3)"
"                    Group Key: orders.o_orderpriority"
"                    Batches: 1  Memory Usage: 24kB"
"                    Buffers: shared hit=3 read=638295"
"                    Worker 0:  Batches: 1  Memory Usage: 24kB"
"                    Worker 1:  Batches: 1  Memory Usage: 24kB"
"                    ->  Result  (cost=0.00..732048.00 rows=236260 width=16) (actual time=6079.412..13496.465 rows=191224 loops=3)"
"                          One-Time Filter: $1"
"                          Buffers: shared hit=3 read=638295"
"                          ->  Parallel Seq Scan on orders  (cost=0.00..732048.00 rows=236260 width=16) (actual time=6079.411..13485.183 rows=191224 loops=3)"
"                                Filter: ((o_orderdate >= '1993-07-01'::date) AND (o_orderdate < '1993-10-01'::date))"
"                                Rows Removed by Filter: 4808776"
"                                Buffers: shared hit=3 read=638295"
"Planning:"
"  Buffers: shared hit=223 read=20"
"Planning Time: 5.517 ms"
"Execution Time: 13543.511 ms"