Updating log table in an effective manner There is a table with compound key:
t_count

+ pageType VARCHAR(32) ascii_bin
+ pageId VARCHAR(255) utf8_bin
+ count INT

PRIMARY(pageType, pageId)

Its purpose is to track how many times each page uniquely defined by ((pageType, pageId) has been viewed. So a query should either insert a new row or increment count on the existing one. How to do it in an effective manner in MySQL?
The table will be updated from multiple concurrent threads serving pages for different clients. So the effectiveness is measured in low to no locking and the ability of the query design to operate in highly concurrent environment.
Database version is:

10.1.48-MariaDB-0+deb9u2

Do you need the count as you go along? If not,  drop the count column. Insert a row for each event as it happens. Aggregate the rows afterwards in some async batch process. Maybe have a monotonic system-generated number if you only want to touch each event once. If you use a datetime instead of an integer you can get aggregates across pages over time as well as over pages.
Up front I'm going to say I'm no expert on MySQL's internals. Take the following as unproven until you've researched the documentation and tested it in your own environment for yourself.
The type of workload you describe can be slow because each write requires the previous thread's write to complete so it knows what new value to write. If the previous thread wrote value 6 my thread has to read that 6 in order to increment it to 7, and the thread after me has to wait on my write so it can read 7 and increment it to 8. In the worst case, with a single page being served by all clients, the system becomes, essentially, single-threaded at this count.
By performing inserts instead of updates the inter-thread dependency is removed. Each thread can write its event when it's ready to. Throughput is limited only by the ability of the storage to persist bytes.
This approach treats the receiving table as a log. You want to choose an implementation which facilitates this. Likely there will be no indexes on this table as they increase the number of IOs required per event and are a further source of possible contention. Different storage engines have different support and performance characteristics for un-sequenced, store-it-anywhere-you-can writes.


Oracle impdp - Importing file from remote server Am I missing something or is it not possible to import a dump (.dmp) file using impdp to a database on another server other than where it was created?  All of my investigations lead me to believe that this cannot be done...that the dump file needs to reside locally on the server where the data is to be imported or NFS mounted so it appears to be local.  This seems to be a capability that the old "exp/imp" utilities used to have, but no longer exist.  I know you can move data using impdp and the REMOTE_LINK option but in order to use this, the data must physically reside in a schema within the database instance on the remote side for it to be copied.  It can't reside in a dump/exported file.

For example.  I have Server "A" and Server "B".  Each of them with an Oracle instance on it.  On "A", I perform an export using expdp of schema "TESTDATA" to a dumpfile named "testdata.dmp" where it is store on "A".  At some point in the future I would like to restore the contents of the "TESTDATA" dump file (testdata.dmp) to a new schema (TESTDATA2) on server "B".

At this point, is it true that my only options are to:

Copy testdata.dmp to server "B" and perform an import directly on server "B"
NFS mount the directory containing testdata.dmp on server "A" from server "B" so the dmp file appears local to server "B" and then perform the import.
Create a temporary schema (TMPSCHEMA) on server "A", import the test.dmp file to the temporary schema using the REMAP_SCHEMA option in impdp, and then perform an impdp on server "B" using the REMOTE_LINK option pulling the data from TMPSCHEMA on "A"

Either I'm missing something here, or Oracle left a huge gap in functionality between impdp and imp.

From Oracle Data Pump utilities:

Dump files are read and written directly by the server and, therefore, do not require any data movement to the client.

impdp/expdp unlike imp/exp does not move the data. They only invoke DBMS_DATAPUMP package and actual data movement is done by the Oracle instance. So data pump can access external data the same way as all other Oracle procedures - loading files via directory object or SELECT data via dblink.


Workbench freezes on connecting after Ubuntu 18.04 upgrade I recently upgraded to Ubuntu 18.04 and started having this issue, I can connect to my local mysql server via the terminal fine using

mysql -u root -p

however when I try to use mysql workbench it just gets stuck on connecting and wb.log displays the following:

11:39:07 [INF][   WBContext UI]: Initializing workbench context UI with these values:
    base dir: /usr/share/mysql-workbench
    plugin path: /usr/lib/mysql-workbench/plugins
    struct path: /usr/share/mysql-workbench/grt
    module path: /usr/lib/mysql-workbench/modules
    library path: /usr/share/mysql-workbench/libraries
    user data dir: /home/<REPLACED USERNAME>/.mysql/workbench
    open at start:
    open type:
    run at startup:
    run type:
    Force SW rendering: No
    Force OpenGL: No
    quit when done: No
11:39:07 [INF][      WBContext]: WbContext::init
11:39:08 [INF][            WBA]: Looking for extension modules for WBA...
11:39:08 [INF][            WBA]: 0 extension modules found
11:39:08 [WRN][            grt]: /usr/share/mysql-workbench/modules/data/mysql_rdbms_info.xml:1401: link 'om.mysql.rdbms.mysql.driver.native_sshtun' <object > key=owner could not be resolved
11:39:08 [WRN][            grt]: /home/<REPLACED USERNAME>/.mysql/workbench/connections.xml:27: link '6d8ecd74-acad-11e8-9c35-408d5cdbf11f' <object GrtObject> key=owner could not be resolved
11:39:08 [INF][      WBContext]: System info:
    MySQL Workbench Community (GPL) for Linux/Unix version 8.0.12 CE build 13312926 (64 bit)
    Configuration Directory: /home/<REPLACED USERNAME>/.mysql/workbench
    Data Directory: /usr/share/mysql-workbench
    Cairo Version: 1.15.10
    OS: Ubuntu 18.04 x86_64
    CPU: 8x Intel(R) Xeon(R) CPU E3-1230 v5 @ 3.40GHz (3700.971MHz) - 15.62GiB RAM
No video adapter info available
    Distribution: Ubuntu 18.04.1 LTS

    Fips mode enabled: no

11:39:08 [INF][    WQE backend]: Found auto-save workspace /home/<REPLACED USERNAME>/.mysql/workbench/sql_workspaces/local-1.autosave
11:39:09 [INF][SQL Editor Form]: Opened connection 'local' to (Ubuntu) version 5.7.23-0ubuntu0.18.04.1

When I edit the connection in mysql workbench and click "Test Connection" i get the following result:

So here is a few things I have tried so far:

Completely purged mysql-server, mysql-commons, mysql-client etc.
Reinstalled mysql-server
Logged into the mysql server via terminal and modified the plugin for root user to use mysql_native_password
Tried creating a new custom user and tried logging into that, once again it works via terminal but not via MySQL Workbench.
Ran apt update, apt upgrade
Downloaded latest MySQL Workbench from their website and installed that.

It wouldn't even allow me to connect to a fresh db install after mysql server reinstall.

These are the versions I'm working on:
MYSQL Server version: 5.7.23-0ubuntu0.18.04.1 (Ubuntu)
Linux Kernel Version: 4.15.0-33-generic
Distribution: Ubuntu 18.04.1 LTS
MYSQL Client Version: mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper

After further investigation I managed to resolve this issue, it seems that after the upgrade my settings must've got screwed up. The connection that wasn't even the one I was attempting to use which was a bit strange.

I went into the paths that mentioned in the log:

11:39:08 [WRN][            grt]: /usr/share/mysql-workbench/modules/data/mysql_rdbms_info.xml:1401: link 'om.mysql.rdbms.mysql.driver.native_sshtun' <object > key=owner could not be resolved
11:39:08 [WRN][            grt]: /home/<REPLACED USERNAME>/.mysql/workbench/connections.xml:27: link '6d8ecd74-acad-11e8-9c35-408d5cdbf11f' <object GrtObject> key=owner could not be resolved

And deleted the 'value' tags as well as the connection that was throwing the error and restarted mysql workbench. After the restart some of the connections I had setup were gone (which I can just recreate) but it seems that I can connect fine now.


RAND in T-sql function Can  any one  tell why t-sql  not allowing using RAND function in side a function

SELECT  CAST (RAND() * 10000000 as varchar(20))

the error is

Invalid use of a side-effecting operator 'rand' within a function.

I found this at http://zarez.net/?p=2192 (author not immediately evident) - how to cheat around this.  RAND() may count as "a side-effecting operator" because it updates the seed-number that the RAND() function uses, ready to produce the next "random" number.  Here's the cheat:

CREATE VIEW Get_RAND
AS
SELECT RAND() AS MyRAND
GO

You can read a value from a view like that in a function, but maybe there's some deep reason not to... or on the other hand, maybe it was forbidden in the first place without a good reason?


Verifying password policy on existing users I've recently come into an environment where a lot of databases logins do not have the enforce_password_policy flag enabled.
An upcoming audit is necessitating the verification of these logins' passwords.

I used the following query to obtain a list of logins and whether the flags are on or off.

select
    @@SERVERNAME as servername,
    name,
    IS_SRVROLEMEMBER('sysadmin', name) as SYSADMIN,
    type_desc,
    create_date,
    is_policy_checked,
    is_disabled,
    password_hash,
    PWDCOMPARE(name, password_hash) as UsernameAsPassword
FROM sys.sql_logins

However, this doesn't tell me if the passwords actually adhere to the password policy, as the flag is only relevant when creating a user.

Is there a known way to test existing users for password policy compliance?
I have no access to the old passwords, and I would prefer a method that doesn't require them.

This may not be popular among your users, but I believe the only way you can know for sure is to force a password change for every SQL login with CHECK_POLICY = ON. This will generate a set of ALTER LOGIN commands with blank passwords, you can update the query giving them all a common password or manually update each one with individual passwords - just make sure they meet your policy. Of course you need to be sure that the password policy is as complex as you expect, and that it is enabled (Control Panel > Administrative Tools > Local Security Policy > Account Policies > Password Policy > Password must meet complexity requirements).

SELECT N'ALTER LOGIN ' + QUOTENAME(name)
  + N' WITH PASSWORD = N'''' MUST_CHANGE, CHECK_POLICY = ON;'
  FROM sys.sql_logins
  --WHERE is_policy_checked = 0;

Steve Jones wrote about this a while back. Note that - due to what I uncovered below - you can't rely on is_policy_checked = 1 to mean that the password actually meets your current policy, since the login could have been created with a hashed password (in which case the plain text password can't be checked) or while the local complexity policy was disabled (which still leads to is_policy_checked = 1).

Another approach I thought would work would be to try to create a copy of every login with their current password_hash and with CHECK_POLICY = ON, and make a note of every one that fails. However, this can not work - even with CHECK_POLICY = ON, it doesn't perform any validation of an already-hashed password. I'll include the code for posterity - but, by design, the policy simply can't be checked.

SELECT N'BEGIN TRY
  CREATE LOGIN ' + QUOTENAME(N'copy_of_' + name)
    + N' WITH PASSWORD = '
    + CONVERT(NVARCHAR(255), password_hash, 1)
    + ' HASHED, CHECK_POLICY = ON;
  DROP LOGIN ' + QUOTENAME(N'copy_of_' + name) + ';
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 15118
    PRINT N''' + REPLACE(name, '''', '''''')
      + N' was not complex enough.'';
END CATCH'
FROM sys.sql_logins;

Personally, I think this is a bug. If the syntax allows me to create a login using a hashed password, and I can stipulate that that password must meet my complexity policy, it should generate an error or warning that the policy was not, in fact, checked.

UPDATE: I filed a bug against this behavior.

CHECK_POLICY is a no-op with CREATE LOGIN ... PASSWORD ... HASHED


Only backup drive and differential backups Is it possible to restore the differential backup without a full backup, but having backup of the entire hard disk?

Example:

2017-01-01: DIFF backup
2017-01-02: entire hard disk backup (NO FULL backup)
2017-01-03: DIFF backup
2017-01-04: DIFF backup

Any suggestions?

No, differential backups cannot be applied on their own and cannot be applied to a database that has been fully brought online, which one from the entire disk backup would be.  You need to restore a full backup first, with norecovery, then the differential.

You would also only apply one differential backup, not several, the last diff backup will contain all of the data changed since the last full backup.


Why does this plan cost even though there are no rows? I execute a Batch-Operation. The whole execution needs about 60s. The following statement needs 75.6% of the whole Batch-Operation:

update WW_Pos set LieferantID=null where LieferantID in (select Ident from deleted)

But there are no rows in WW_Pos for this where-condition. As you can see in the execution-plan, the Update-Operation of the clustered index would take 99% of the costs of the plan:

and the Plan-Tree shows there are no Actual Rows for the Update-Operation:

LieferantID is not the Clustered index (but it is in another index WW_Pos_LieferantID). Nevertheless, it has to update the clustered index?

Why does this Update-Statement take about 40 seconds, even though there are no records to update?

I hope someone can help me understand this behavior.

The following statement needs 75.6% of the whole Batch-Operation

This is an estimate. The percentage costs are always estimates, even in a post-execution ('actual') execution plan. The 75.6% figure represents the estimated cost of this plan as a proportion of the estimated cost of the entire batch.

Given the disparity between estimated rows and the number actually encountered at runtime, this number means nothing. In fact, these numbers are often no better than a very loose hint, because the optimizer's cost model probably bears very little relation to the actual costs of execution in your particular hardware environment, even where the cardinality estimates happen to be exactly correct.

  As you can see in the execution-plan, the Update-Operation of the clustered index would take 99% of the costs of the plan

Same explanation as above. According to the optimizer's cost model, if the query had actually produced the same number of rows as estimated, the Clustered Index Update would account for 99% of the estimated cost of running this statement.

  LieferantID is not the Clustered index (but it is in another index WW_Pos_LieferantID). Nevertheless, it has to update the clustered index?

An update always has to modify the clustered index, since the clustered index is the table. Changes must be made in the base table as well as any nonclustered indexes (which are just copies of a subset of the underlying table data).

  Why does this Update-Statement take about 40 seconds, even though there are no records to update?

Hard to say from the information provided. This may be a trigger plan (after or instead of update) assuming you do not have a table called [deleted], in which case it is possible you are timing the complete update statement, and not just the execution of this trigger code.

To address an extra question raised in comments to the question, the extra operators in the execution plan are to detect non-updating updates (a change to a nonclustered index that does not actually change the stored value).


How would you store permutations of a set of simulation parameters, optimizing for storage and efficiency of parsing? I'm going to run a bunch of simulations using an application I wrote in the near future, and I'm having trouble designing my database around this.

Here's how it works. Each simulation has 6 parameters that can vary, and each parameter has ~10 options. So we have about 1 million permutations of this to somehow store in the DB. The simplest way I can think is to have a column for each parameter, with an additional column for my primary key (ExperimentID). I could then access the parameters for a given experiment directly by doing a simple

SELECT * FROM Parameters WHERE ExperimentID = (givenID)

Each parameter will be stored in the DB as an int, which will be interpreted by my application as the necessary parameters at runtime.

A more complex, but I think faster, way to do it is to take advantage of the speed of binary shifting. Instead of storing the parameters directly, I'd store the information about each parameter in 4 bits of an unsigned int. I don't actually know how ints are stored (binary wise) in the DB, so that's the major hold up.

Any advice on what to do in this situation? Also, I don't yet have these columns, so if I decided to go with the first option, how would I fill a table with all of the permutations of a set of parameters?

Why do you think that obscuring real data into a bit-masked field will be faster? You are only talking about 1 million rows and 24 bytes per row. That really isn't much, especially since you are only doing singleton / seek operations.

A better optimization would be to use TINYINT fields (range of 0 - 255 ; byte in .NET). Using TINYINT instead of INT (and assuming a FILLFACTOR** of 100) would result in:

the table would effectively be 10 MB instead of 28 MB (1 byte per each of 6 fields + 4 bytes for the INT [ID] field = 10 bytes per row).
you would get 806 rows per 8060-byte data page instead of 287 with six INT fields

The main efficiencies gained here are:

quicker initial lookup time for 2244 records. How so? With 1 million rows, 287 rows per 8k data page equates to 3484 pages, while 806 rows per 8k data page equates to only 1240 pages. Once a page is read to get the first row's values, it should stay in the buffer pool (i.e. memory) for subsequent lookups (i.e. the other rows on that same data page). More rows per page means fewer pages to read from disk--2244 fewer pages in this case. But once all of the pages are in the buffer pool, the lookups should be the same speed whether it was 806 rows or 287 rows per page. So, 2244 lookup operations out of the 1 million are slightly faster. Certainly not bad since there was no cost associated with this optimization, but just wanted it to be put into perspective.
less memory required to hold the entire table in memory. this will make it more likely to remain in memory.

Lastly, it is definitely possible to squeeze this data into an even smaller representation. But at this point it is 10 bytes per row with no complication added to the processing / coding and all of the values are human readable. There isn't a lot of room to go below 10 bytes per row, but even if it got down to 7 bytes per row (INT IDENTITY + 3 bytes for Parameters, which should be the minimum required to handle these ranges), I am not sure the added complexity would be worth it (i.e. diseconomies of scale).

CREATE TABLE dbo.Experiment
(
  ExperimentID INT NOT NULL IDENTITY(1, 1),
  Parameter1  TINYINT NOT NULL,
  Parameter2  TINYINT NOT NULL,
  Parameter3  TINYINT NOT NULL,
  Parameter4  TINYINT NOT NULL,
  Parameter5  TINYINT NOT NULL,
  Parameter6  TINYINT NOT NULL,
  CONSTRAINT [PK_Experiment] PRIMARY KEY (ExperimentID) WITH (FILLFACTOR = 100) ON [Tables]
)
ON [Tables];

And you can populate it using the following (which assumes two parameters of 0 - 4, two parameters of 0 - 7, and two parameters of 0 - 9 ; based on comments on the Question):

;WITH Five(num) AS
(
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
), Eight(num) AS
(
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
  UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
), Ten(num) AS
(
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
  UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
  UNION ALL SELECT 9
)
--INSERT INTO dbo.Experiment
--     (Parameter1, Parameter2, Parameter3, Parameter4, Parameter5, Parameter6)
SELECT f1.[num] AS [Parameter1],
       f2.[num] AS [Parameter2],
       e1.[num] AS [Parameter3],
       e2.[num] AS [Parameter4],
       t1.[num] AS [Parameter5],
       t2.[num] AS [Parameter6]
FROM   Five f1
CROSS JOIN Five f2
CROSS JOIN Eight e1
CROSS JOIN Eight e2
CROSS JOIN Ten t1
CROSS JOIN Ten t2;

The query above only generates 160k permutations, not 1 million, again based on comments on the Question. But it should be should be fairly easy to figure out how to adjust it for different ranges of values for any combination of the six parameters.

** FILLFACTOR: Rows are stored on data pages that are roughly 8060 bytes. Smaller rows = more rows that fit into that fixed size container. FILLFACTOR is how much space to reserve (when creating / rebuilding an index) for new rows or updates that take up more space due to variable length fields requiring more bytes for the new version of the row. A value of 100 means that there is no reserved space, that 100% of the 8060 should be taken up by current rows. In this scenario there are only fixed length fields and no out of sequence inserts.


What are the benefits of packages over standalone procedures and functions? Looking for a canonical answer to the question of why packages should be preferred in an Oracle database over stand alone procedures and functions.

Benefits of Packages

Logical Grouping – Methods that work together can be put into a cohesive unit rather than just logically coupled but physically separate.

Secure Private Methods - Functions and Procedures can be made private to the package and only be used within it.  This makes the public surface simpler and more secure.

Privilege Management – Permissions can be granted once for a group of procedures that work together rather than separately for each procedure/function required.

Secure Wrapping - Wrapped packages are more difficult to unwrap than wrapped functions/procedures.

Simplified Naming – A larger namespace allows names that are simpler and can be re-used in other packages.

Better Performance – Packages can be compiled and are loaded into memory in entirety rather than piecemeal as other methods.  This benefit if it exists at all is minimal compared to the other benefits.

Reduced Invalidation – Changing a package body does not invalidate dependencies as changing a function or procedure does.

Unique Features - Package Variables, Package Constants, Initialization, Session State, Package Comments, and Overloaded Methods.

References:
11.2 Concepts Guide
Ask Tom Question
StackOverflow.com Question on Package Performance
Unwrapping PL/SQL Presentation (pdf)


Easy answer is to do this in a function in the built-in VBA editor in Microsoft Access.

Assuming you have two tables, Source and Dest that both have 3 columns, col1, col2, and col3.

Sub SplitIt()

    Dim rstSource As Recordset
    Dim rstDest As Recordset
    Dim Items() As String

    ' use dbOpenSnapshot to open the source table READ-ONLY
    Set rstSource = CurrentDb.OpenRecordset( _
            "SELECT col1, col2, col3 FROM Source;" _
            , dbOpenSnapshot)
    ' use dbOpenDynaset to open the destination table READ-WRITE
    Set rstDest = CurrentDb.OpenRecordset( _
            "SELECT col1, col2, col3 FROM Dest;" _
            , dbOpenDynaset)

    With rstSource
        ' .BOF is Beginning of the table
        ' .EOF is End of the table
        ' Checking if both are false means there are records in the
        ' source table
        If Not (.BOF And .EOF) Then
            ' get the first record from the source table
            .MoveFirst
            Do
                ' if col3 is NULL (empty)
                If Nz(!col3, "") = "" Then
                        ' add a new record into the destination table
                        ' with data from the source table for col1 and col2
                        rstDest.AddNew
                        rstDest!col1 = rstSource!col1
                        rstDest!col2 = rstSource!col2
                        ' set col3 to NULL (empty)
                        rstDest!col3 = Null
                        ' save the new record
                        rstDest.Update
                Else ' if col3 IS NOT NULL
                    ' convert col3 into an array of strings
                    Items = split(Nz(!col3, ""), ";")
                    ' loop through the array of strings
                    For a = LBound(Items) To UBound(Items)
                        rstDest.AddNew
                        rstDest!col1 = rstSource!col1
                        rstDest!col2 = rstSource!col2
                        ' col3 is set to the current item in the array
                        rstDest!col3 = Items(a)
                        rstDest.Update
                    Next a
                End If
                ' load the next record from the source table
                .MoveNext
            ' repeat until the end of the source table is reached
            Loop Until .EOF
        End If
        ' close the source table
        .Close
    End With
    ' close the destination table
    rstDest.Close

End Sub

Then run the SplitIt() function from the Immediate Window.  Press CTRL-G then type:

SplitIt

and press [Enter]

As an aside; there is an excellent resource on understanding For...Next code at http://msdn.microsoft.com/en-us/library/5z06z1kb.aspx


How to use OR in IIF statement in row visibility? How to use OR in IIF statement in SSRS

=IIF((Fields!txtCurrentSubjectReportName.Value="",
      Fields!txtSubjectArchivedName.Value,
      Fields!txtCurrentSubjectReportName.Value) = "School Life"
      OR
      "My goal for this term..."
      OR
      "Student Reflective Comment", True, False)

Its throwing error

=IIF(Fields!txtCurrentSubjectReportName.Value="",
     Fields!txtSubjectArchivedName.Value,
     Fields!txtCurrentSubjectReportName.Value) = "School Life"
 OR
 IIF(Fields!txtCurrentSubjectReportName.Value="",
     Fields!txtSubjectArchivedName.Value,
     Fields!txtCurrentSubjectReportName.Value) = "My goal for this term..."
 OR
 IIF(Fields!txtCurrentSubjectReportName.Value="",
     Fields!txtSubjectArchivedName.Value,
     Fields!txtCurrentSubjectReportName.Value) = "Student Reflective Comment"

The result is boolean itself and does not need in additional IIF().


Server role with permission only to create server login, not alter one I am working with Microsoft SQL Server Management Studio and I want to create a server role that has permissions to create server login but only create, not alter!

Does anyone know what is the permissions name?

CREATE and ALTER are tied together into ALTER ANY. What is the actual goal? If you're going to give a person the rights to create logins, why block from alter? Whatever you think they might try to do, they could just create an additional login instead of changing an existing one, or drop/create the original.

You could possibly block ALTER LOGIN commands via a DDL trigger depending on the caller, but that is quite different from permissions.


How can I pass the table name as an argument I have this code

ALTER PROCEDURE [dbo].[tablepass12]
    (@TableName nvarchar(20))
AS
BEGIN
    DECLARE @SQL VARCHAR(8000)

    SELECT @SQL = 'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM "'+@TableName+' -o d:\processing\'+@TableName+'.csv'

    EXEC MASTER..XP_CMDSHELL @SQL
END;

When execute it, I get this error

  Msg 105, Level 15, State 1, Server ICB3110\SQLEXPRESS, Line 1
  Unclosed quotation mark after the character string 'AIR_LINE
      '.

  Msg 102, Level 15, State 1, Server ICB3110\SQLEXPRESS, Line 1
  Incorrect syntax near 'AIR_LINE
      '.

As the error states:

  Unclosed quotation mark

Your string is:

'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM "'+@TableName+' -o d:\processing\'+@TableName+'.csv'

Once the @TableName variable is replaced with its value, the string gets rendered as:

'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM "AIR_LINE -o d:\processing\AIR_LINE.csv'

If you look at the -Q parameter, it is malformed as:

"SELECT * FROM "AIR_LINE

You just need to move the double-quote (") to the other side of the variable (it should be to the left of the -o, not to the right of the FROM):

'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM '+@TableName+'" -o d:\processing\'+@TableName+'.csv'


Access operator added by extension inside a schema I am writing an application that uses postgres. Each microservice uses its own schema inside the database.

So connect to the database schema I use this jdbc string:

jdbc:postgresql://${db.host}/${db.name}?currentSchema=${schema}

This kind of works, so each sql statement can use the tables inside the schema without the danger of accidentally accessing the other services schema.

But in one schema I'd like to use the earthdistance extension. I only managed to install it without setting a schema and when I connect to the DB without the currentSchema parameter I can access the <@> operator to calculate the distance.
But with the parameter postgres complains that it's not able to find the operator.

Is there a way to access the operator that is in the public namespace, when I connected with the currentSchema parameter?

I'm kinda lost here :-(

You could schema qualify the operators when you invoke them.

For example, instead of writing

SELECT current <@> destination

You can write:

SELECT current operator(public.<@>) destination

See Operator Invocations for more information


PostgreSQL/Psycopg2 indexing array in the size of thousands I will have an array the size of 16,380, and I want to index them so I can call aggregate functions over them using 16,380 indices.

For example, I have a table:

+----+-------+----------------------------+
| id | stuff |   large_array_of_values    |
+----+-------+----------------------------+
|  0 |     5 | {1.0, NULL, 3.0, 4.0, ...} |
|  1 |     2 | {2.0, 3.0, 4.0, 5.0, ...}  |
|  2 |     1 | {3.0, 4.0, 5.0, NULL, ...} |
+----+-------+----------------------------+

I want to select all the values from large_array_of_values column with specific index, say index 15,123 and apply an aggregate function such as population standard deviation across index 15,123 of every row in the table.

E.g., in the above table the population standard deviation should be 0.5 across index 1 of each array on every row (NULL, 3.0, 4.0) which ignores NULL values in PostgreSQL.

My main question is how efficient is indexing such a large number of values in PostgreSQL?

I dont' think indexes will help. I am not sure why, but I am just being experimental...

First, we create a table with your structure, and fill it with random data (10.000 rows, and a vector of 1.000 columns).

 CREATE TABLE t
 (
     id integer /* PRIMARY KEY */,
     stuff integer,
     large_array_of_values float[]
 ) ;

 CREATE OR REPLACE FUNCTION random_vector() RETURNS float[] AS
 $$
     select
        array_agg(random())
     from
        generate_series (1, 1000)
 $$
 LANGUAGE SQL ;

 INSERT INTO t
    (id, stuff, large_array_of_values)
 SELECT
    id, random()*10000,  random_vector()
 FROM
        generate_series(1, 10000) AS i(id) ;

At this point we create one sample index for values at index [32] of the vector (plus the id!):

 CREATE INDEX
    idx_32 ON t(id, (large_array_of_values[32]));

Now, we ask PostgreSQL to analyze the following query and explain it:

 EXPLAIN ANALYZE
 SELECT
    avg(large_array_of_values[32])
 FROM
    t
 WHERE
    id BETWEEN 5000 and 7500
     AND (large_array_of_values[32]) > 0.32 ;

 | QUERY PLAN                                                                                |
 | :---------------------------------------------------------------------------------------------------------------------- |
 | Aggregate  (cost=46.94..46.95 rows=1 width=8) (actual time=54.871..54.871 rows=1 loops=1)                               |
 |   ->  Bitmap Heap Scan on t  (cost=4.91..46.89 rows=17 width=32) (actual time=0.392..1.204 rows=1732 loops=1)           |
 |         Recheck Cond: ((id >= 5000) AND (id <= 7500) AND (large_array_of_values[32] > '0.32'::double precision))        |
 |         Heap Blocks: exact=20                                                                                |
 |         ->  Bitmap Index Scan on idx_32  (cost=0.00..4.91 rows=17 width=0) (actual time=0.364..0.364 rows=1732 loops=1) |
 |               Index Cond: ((id >= 5000) AND (id <= 7500) AND (large_array_of_values[32] > '0.32'::double precision))    |
 | Planning time: 0.405 ms                                                                                |
 | Execution time: 55.013 ms                                                                                |

dbfiddle here

The query plan is not using the index (idx_32) to perform an index-only scan, which I guess is what you wanted, even if idx_32 is a covering index for such a query.

VACUUMing, to make sure that the visibility map was up-to-date didn't have any effect. I couldn't find any explicit restrictions on index-only scans having to refer to columns (and not Indexes on Expressions), but it appears that they aren't used in this second case.

Comparison with a non-vector column

 CREATE TABLE t
 (
     id integer /* PRIMARY KEY */,
     stuff integer,
     a_value float
 ) ;

 INSERT INTO t
    (id, stuff, a_value)
 SELECT
    id, random()*10000,  random()
 FROM
    generate_series(1, 10000) AS i(id) ;

 CREATE INDEX idx_value ON t(id, a_value);

 VACUUM ANALYZE VERBOSE t ;

In this case, the covering index is actually used, and you get an "index-only scan".

 EXPLAIN ANALYZE
 SELECT
    avg(a_value)
 FROM
    t
 WHERE
    id BETWEEN 5000 and 7500 AND (a_value > 0.1)

 | QUERY PLAN                                                                                |
 | :---------------------------------------------------------------------------------------------------------------------------- |
 | Aggregate  (cost=103.67..103.69 rows=1 width=8) (actual time=1.139..1.140 rows=1 loops=1)                                     |
 |   ->  Index Only Scan using idx_value on t  (cost=0.29..98.05 rows=2251 width=8) (actual time=0.026..0.655 rows=2247 loops=1) |
 |         Index Cond: ((id >= 5000) AND (id <= 7500) AND (a_value > '0.1'::double precision))                                   |
 |         Heap Fetches: 0                                                                                |
 | Planning time: 0.184 ms                                                                                |
 | Execution time: 1.179 ms                                                                                |

dbfiddle here


Applying Service Pack 3 in SQL 2012 in Windows 2012 R2 Do I need to remove the possible owner node in cluster while applying SQL Service Pack 3 in SQL 2012 in Windows 2012 R2 . MS article says its recommended to do avoid corruption. Is it mandatory to do it in Win 2012 R2 ?

Do I need to remove the possible owner node in cluster while applying SQL Service Pack 3 in SQL 2012 in Windows 2012 R2 .

I suppose you are referring to This support article. The purpose of this support article is if you are trying to install Service pack on cluster without disturbing the current cluster configuration(active nodes) and you want to keep things online.

I suggest take application downtime and apply service pack in that case you don't have to remove any possible owners from the list. I always consider this better way of applying SP, although MS provides you way to update the passive node of cluster without disturbing active node.

This article also helps you when you add node to cluster with RTM version while cluster is on higher SP level, then you have to apply SP on passive node in that case after adding node make sure the added node is not in possible owner list and go ahead and apply patch on passive nodes added so as to make it identical to all nodes.


Attach metadata to a stored procedure Is it possible to attach metadata to a stored procedure in Postgres 11?
In particular, I have database migrations with version numbers. So when my migration script runs and might override the current stored proc with CREATE OR REPLACE PROCEDURE, I'd like to be able to attach metadata to the stored proc, with the a newer version number.
Also, how can this be queried afterwards?

For this there is a COMMENT SQL command:
COMMENT ON PROCEDURE whatever IS 'some text'

To retrieve it later use the function obj_description().


No data inserted with this WHERE NOT EXISTS expression? I want to move data from MySQL to PostgreSQL. I am using mysql.connector and psycopg2 for querying the two databases. When I run WHERE NOT EXISTS for my PostgreSQL DB, it's doesn't insert the data from MySQL.

The subject table doesn't have a unique column so I am using WHERE NOT EXISTS instead of ON CONFLICT.

curr_msql.execute('''SELECT scode, sname sunits FROM subject_table''')

 for row in curr_msql:
            curr_pgsql.execute('''INSERT INTO subject (created, modified, code, name, units)
            SELECT current_timestamp, current_timestamp, %s, %s, %s
            WHERE NOT EXISTS(SELECT code FROM subject WHERE code = code)
            ''', (row['scode'], row['sname'], row['sunits']))

I am not getting any error when running the query but the data are not getting inserted.

Your condition compares the column value to itself:

WHERE NOT EXISTS(SELECT code FROM subject WHERE code = code)

Boils down to:

WHERE NOT EXISTS(SELECT FROM subject WHERE code IS NOT NULL)

IOW, it's always true as soon as there is a single row in the table with code IS NOT NULL. I don't think that's intended.

Guess you want something like:

'''INSERT INTO subject (created, modified, code, name, units)
   SELECT current_timestamp, current_timestamp, %s, %s, %s
   WHERE NOT EXISTS(SELECT code FROM subject WHERE code = %s)'''
, (row['scode'], row['sname'], row['sunits'], row['scode']))

And that's not fit for concurrent write load. I suggest to create the missing UNIQUE constraint on (code) and use INSERT .. ON CONFLICT ... DO NOTHING if at all possible. If the table is big, you'll want an index on code for performance, anyway.


Select first row (grouping) + add aggregate function First have a look at this question on StackOverflow.

I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query.

How can I combine the answer using DISTINCT with an aggregation...

I've tried:

SELECT DISTINCT ON (name, zonedistrict_id)
       ST_Union(geom) as geom, gid, name, zonedistrict_id, zonestyle_id, longname
FROM zones
ORDER BY name, zonedistrict_id, zonestyle_id;

Which screams:

  column "zones.gid" must appear in the GROUP BY clause or be used in an aggregate function

Which is strange, because if I remove ST_Union(geom) as geom,, the query works. But it's absent of the union'd geometry.

Answer to question
SELECT DISTINCT ON (name, zonedistrict_id)
       ST_Union(geom) as geom, gid, name, zonedistrict_id, zonestyle_id, longname
FROM   zones
GROUP  BY gid, name, zonedistrict_id, zonestyle_id, longname
ORDER  BY name, zonedistrict_id, zonestyle_id;
It depends on what you are actually trying to achieve and what version of Postgres you are using and the table definition of the underlying table. You chose not to disclose any of that.
This gives you one distinct row per (name, zonedistrict_id), but geom is only the aggregate over (gid, name, zonedistrict_id, zonestyle_id, longname)
In older versions you had to list every non-aggregated column of the SELECT list in the GROUP BY list. This changed somewhat with PostgreSQL 9.1. I quote the release notes:

Allow non-GROUP BY columns in the query target list when the primary
key is specified in the GROUP BY clause (Peter Eisentraut)

My guess
However, I suspect you secretly want this:
SELECT DISTINCT ON (name, zonedistrict_id)
       ST_Union(geom) OVER (PARTITION BY name, zonedistrict_id) AS geom
     , gid, name, zonedistrict_id, zonestyle_id, longname
FROM   zones
ORDER  BY name, zonedistrict_id, zonestyle_id;
Using a ST_Union(geom) as aggregate window function. So geom is actually the aggregate over (name, zonedistrict_id), which seems more likely.
This should be possible since the PostGis manual tells us about its aggregate functions:

... can be used just like any other sql aggregate function such as sum, average.

And SQL aggregate functions can be used as window functions.


Postgresql a strange tsquery behavior select  to_tsvector('english', 'Ice-cream') @@ to_tsquery('english', 'Ice<->cream');

is True. While:
select  to_tsvector('english', 'iDream  Ice-cream  iScream') @@ to_tsquery('english', 'iDream<->Ice<->cream<->iScream');

is False.
For my understand. Both add same word before and after the match one. It will keep the same answer.

That's because full-text search treats hyphenated words specially:
SELECT to_tsvector('english', 'iDream  Ice cream  iScream');

               to_tsvector
------------------------------------------
 'cream':3 'ice':2 'idream':1 'iscream':4
(1 row)

The numbers behind the lexemes mark the position they had in the original text (cream is the third word, and so on). That is used for phrase search.
SELECT to_tsvector('english', 'iDream  Ice-cream  iScream');

                      to_tsvector
--------------------------------------------------------
 'cream':4 'ice':3 'ice-cream':2 'idream':1 'iscream':5
(1 row)

You see that the original hyphenated word is at the second position, and the parts are represented as following the hyphenated word.
So ice cream is not the same as ice-cream for PostgreSQL full text search.
In the first case, ice immediately follows idream, but not in the second case. That is why your query returns FALSE.
Look at what the parser does:
SELECT alias, token, lexemes FROM ts_debug('english', 'iDream  Ice-cream  iScream');

      alias      |   token   |   lexemes
-----------------+-----------+-------------
 asciiword       | iDream    | {idream}
 blank           |           |
 asciihword      | Ice-cream | {ice-cream}
 hword_asciipart | Ice       | {ice}
 blank           | -         |
 hword_asciipart | cream     | {cream}
 blank           |           |
 asciiword       | iScream   | {iscream}
(8 rows)

Perhaps the solution you are looking for would be to ignore hyphenated words and just keep their parts:
CREATE TEXT SEARCH CONFIGURATION en_no_hyphen
   (COPY = english);

ALTER TEXT SEARCH CONFIGURATION en_no_hyphen
   DROP MAPPING FOR asciihword, hword;

SELECT to_tsvector('en_no_hyphen', 'iDream  Ice-cream  iScream')
       @@ to_tsquery('en_no_hyphen', 'iDream<->Ice<->cream<->iScream');

 ?column?
----------
 t
(1 row)


MySQL duplicate entry error opon import to new server I do have a DB on MySQL Galera 5.6.34 with a table that contains an index on ID,date. The table is fully functional and I have used repair and check to verify the table. However, upon backup/restore the importing DB-Server (MySQL 5.7.17, not Galera) tells me there is a duplicate entry and stops importing:

  gunzip < DB_170206_040001.sql.gz | mysql -u root -p staging

  ERROR 1062 (23000) at line 3800: Duplicate entry '329610-2011-03-27 03:00:00' for key 'user_id'

If I empty that table prior to the backup, everything works fine.

Is there a command to "ignore" upon import, or even better to resolve the problem at the live server?

Per this response: mysqldump with INSERT ... ON DUPLICATE

It would be best to take a new backup with the --replace option, if you are certain that this will not destroy relational integrity of your dataset. This will drop the row that is creating conflicts and instead insert the row that is signalling the error and stopping the restore.

The issue is that you, somehow, have ended up with multiple rows with the same value in a column that enforces uniqueness. How this happened in the Galera server is unclear.

It may be useful to post the results of the following:

mysql> select max(t.count) from (select count(user_id) as count from <db>.<table> group by user) t;

If any row returns a value other than 1, then there's a relational problem on the originating server. If the value is equal to 1, there's more to explore on the restore-server in why it believes there is an issue. It could be that the backup was taken hot, and there are missing transactions which do not yield a consistent dataset.


How to get the parameter values of a stored procedure that's in the middle of execution? Is there a way to get the values of the parameters that were passed in to a stored procedure while the stored procedure is in the middle of execution? (I'm trying to debug an actively running stored procedure.)

I know I can see the queries of the stored procedure that are currently being executed using the sys.dm_exec_requests and sys.dm_exec_sql_text DMVs but they don't show the values of the parameters being used.

For SQL Server 2016 you need to have the query profiling infrastructure enabled in advance with trace flag 7412 or an extended events session capturing query_thread_profile (and be on at least  SQL Server 2016 SP1) but then can use

WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select query_plan.query('//ParameterList')
from sys.dm_exec_query_statistics_xml(@session_id)

and see the ParameterRuntimeValue and ParameterCompiledValue from the execution plan of the statement being executed.

For SQL Server 2019+ the infrastructure is enabled by default so the above will just work.

This might not be all the parameters of the stored proc however as it will only include params used by the execution plan.


Grouping similar rows together based on time I have a table that records all the changes done to a model by a user. The table looks something like this:  (SQLFiddle)

In the table an action_hash is generated based on the model, model_id and timestamp, which effectively generates a unique identifier of an action.

I want to to display the data from this table to my users as an "activity feed". At first I was querying the database like so:

-- Get the last 5 actions (for pagination)
SELECT `action_hash` from `changes` GROUP BY `action_hash` ORDER BY `timestamp` DESC LIMIT 5;

-- Get all the rows for this last 5 action
SELECT * FROM `changes` WHERE `action_hash` IN ('a1a2913ac13c34afd957c1646243d8fa', 'af024d2e0b56c8af6506e8f755bb1e39', '33ccb3d33e4add95486a58dd5b33495a', 'b2fcb43f711ea6e047b4610334e546c8', '85f21b48085cce9ff7bab9c0b26dfb8f')

(SQLFiddle)

I would get a result like above. Then my backend code would go through the results in the last query and group the rows by hash, and parse them to be human readable while still maintaining the pagination.

The problem with this method is that some rows are so similar showing them as separate changes creates too much noise in the activity feed. The similar rows should somehow be grouped together.

The rows highlighted above should be grouped together uniquely identified in my final result (maybe with another hash?) because the changes occurring are happening to the same model (and model_id) within 15 minutes of each other. How can I write a query that shows this?

This is the result I'm looking for:

Note: action_group doesn't have to be integers, any string that indicate they associate with one another will do. I'd also would like to paginate my results based on the new action_group column.

I would start with a query that finds the latest row in each group of similar rows that are less than 15 minutes apart, and picks the latest five of those:

SELECT DISTINCT model, model_id, timestamp
FROM changes c1 WHERE (model, model_id) NOT IN
  (SELECT model, model_id FROM changes c2
   WHERE c2.timestamp > c1.timestamp
     AND c2.timestamp < c1.timestamp + INTERVAL 15 MINUTE)
ORDER BY timestamp DESC LIMIT 5;

To get a unique number for each group, you can use a variable:

SELECT model, model_id, timestamp, @num:=@num+1 grp
FROM (<query#1>) last, (SELECT @num:=0) init;

Using this query as a derived table (subquery in FROM clause), you can find all rows that belong to these groups:

SELECT c3.*, groups.grp action_group
FROM changes c3 JOIN (<query#2>) groups USING(model, model_id)
WHERE c3.timestamp <= groups.timestamp
  AND groups.timestamp < c3.timestamp + INTERVAL 15 MINUTE
ORDER BY c3.timestamp DESC;

The full query then becomes:

SELECT c3.*, groups.grp action_group
FROM changes c3 JOIN
    (SELECT model, model_id, timestamp, @num:=@num+1 grp
     FROM (SELECT DISTINCT model, model_id, timestamp
           FROM changes c1
           WHERE (model, model_id) NOT IN
               (SELECT model, model_id FROM changes c2
                WHERE c2.timestamp > c1.timestamp
                AND c2.timestamp < c1.timestamp + INTERVAL 15 MINUTE)
           ORDER BY timestamp DESC LIMIT 5) last,
           (SELECT @num:=0) init
    ) groups USING(model, model_id)
WHERE c3.timestamp <= groups.timestamp
  AND groups.timestamp < c3.timestamp + INTERVAL 15 MINUTE
ORDER BY c3.timestamp DESC;

If your changes table is very big, the above query might take some time.  Indexes on (model, model_id) and timestamp might help.  Also, it might be possible to rewrite the query to use JOIN instead of NOT IN.


Getting Max for partition column in clustered columnstore index SQL 2014 Enterprise
I have a datawarehouse table with ~ 800 million rows (total table size ~25 GB)
this table is partitioned by the import_date column (on daily basis)
it has a clustered columnstore index on it
when I execute a simple SELECT MAX(t.import_date) FROM stat.t_auftragsstatistik t it does an CLUSTERED COLUMNSTORE INDEX SCAN over ~150 million rows / 33156 pages
you can find the execution plan at https://www.brentozar.com/pastetheplan/?id=ryuskalug

On the other hand, when I SELECT COUNT(*) FROM stat.t_auftragsstatistik AS ta WHERE ta.import_date = '20170201' (which is the most recent date) it returns me ~375k rows and needs only 6 page read to gather this information.

Question:

why is the Max() so slow (~7 seconds) and reads so much data (with nonclustered index on import_date it would do a simple index scan in reverse order and stop after the first match (-> 3 reads))
why does it ignore the partitioning (even if I would partition on e.g. weekly basis, it would have only to scan the latest partition with at least one row)

To address your second question first, it looks like SQL Server may not have access to that optimization or may not always apply it as we would like it to. I created a yearly partitioned heap with 9 million rows and ran the same query as you. It would be nice if SQL Server looped backwards through the partition function and stopped when it found data in a partition. However, the query optimizer did a full table scan instead. I think that it's much more common to put an index on a partitioned column, so it would be natural to attribute a fast SELECT MAX(PARTITIONED_COLUMN) query to a partitioning scheme instead of an underlying index. If it would help I can post my code.
To address the first question, is 7 seconds really that slow to process 800 million rows? Your query runs in parallel so you should get batch mode processing. However, because you are on SQL Server 2014 you will not get aggregate pushdown. Sunil has a blog post here which works through a similar example.

The picture below shows and aggregate query that processes 10 million rows and computes a single aggregate SUM of Quantity sold from the SALES table
SELECT SUM (Quantity) FROM  SALES
SQL Server 2014 scans these 10 million rows in batches (e.g. 900 rows) and sends these batches to Aggregate operator to compute the aggregate.  The picture below shows 10 million rows moving from SCAN node to the Aggregate node.
In SQL Server 2016, the aggregate operator itself is pushed to the SCAN node (i.e. closer to the source of the data) and the aggregate is computed there for compressed rowgroups. The picture below shows that 0 rows moved from SCAN node to the AGGREGATE node. This is because the aggregate was computed at the SCAN node.

For the COUNT(*) query, you are explicitly filtering on a single partition. I would expect SQL Server to use partition pruning to only get the relevant rowgroups. Overall it should be faster and require less resources than the MAX() query does because that query will hit all partitions.
I suspect that you asked about the MAX() query mostly out of curiosity. The easiest way to make that query go faster is to upgrade to SQL Server 2016. For something more immediate, you could take a look at your rowgroups and make sure that they're of a good size. If you have fewer rowgroups I would expect the query to go somewhat faster. Alternatively, you could write a function to find the maximum value in a way that takes advantage of partitioning on the table.


Help with a multi-language medical database My project is to design a database for medicines in many countries.

Medicines have various criteria like composition, price, manufacturer.
Database will be multi-language; related to each country.
Database will increase in size over time.
Database will have more than just other data on medicines so it will become more complex over time.

Should I make a separate database for each country?

I use SQL Server 2008.

No, you should not split this out into different databases. This will cause more issues and complexity further down the line, for reporting cross country, possibly requiring cross database ownership chaining, etc.

You can achieve your requirements by a process of normalization. Many people say they have normalised a database by looking at the required attributes and splitting them into separate entities by "feel". However, proper normalization is a much more formal, structured approach and is applicable for good database design. There is a very good Wikipedia article at http://en.m.wikipedia.org/wiki/Database_normalization and Itzik Ben-Gans book T-SQL Querying also goes into lots of detail with good examples.

Also recommend that you validate your normalization with a full ERD (Entity Relationship Diagram) Your normalization and ERD are also very useful design documents for your projects documentation library.


System-level way to enforce that reserved words may not be used for Oracle column names? Is there a way in Oracle 12c to enforce that reserved words / keywords may not be used as column names? I am aware that it is not good practice to use keywords in this way. However from what we have observed, tables are able to be created with a column name of the reserved word "password" in our local Oracle, but the customer found that the table was was not being created on their Oracle environment.

A complication here is that the tables are being created by a Java application which uses Hibernate to create the tables and these are not simply DDL statements. I have seen the related question: Oracle: Add Reserved Word as Column Name and I realise that the use of reserved words is permitted inside quotes, but I was wondering is there an system level "strict" mode that prevents the creation of tables with reserved words?

You could create a system trigger like this:

create or replace trigger no_reserved_words
   BEFORE CREATE OR ALTER ON SCHEMA
DECALRE

  CURSOR Cols IS
  SELECT r.*
  FROM V$RESERVED_WORDS r
      JOIN ALL_TAB_COLUMNS ON keyword = UPPER(column_name)
  where RESERVED = 'Y'
      and owner = ora_dict_obj_owner
      and table_name = ora_dict_obj_name;

BEGIN
   IF ora_dict_obj_type = 'TABLE' THEN
      FOR aCol in Cols LOOP
         RAISE_APPLICATION_ERROR(-20010, aCol.keyword ||' not allowed as column name');
      END LOOP;
   END IF;
END;

BEGIN

However, there are several problems. As user with ADMINISTER DATABASE TRIGGER system privilege (e.g. DBA) ignores the RAISE_APPLICATION_ERROR, it he can do it anyway.

Another problem is: What so you consider as "reserved word"? Check this example:

SELECT *
FROM V$RESERVED_WORDS r
WHERE keyword IN ('PASSWORD','NAME', 'DATE', 'SYSDATE')

+-------------------------------------------------------------+
|KEYWORD |LENGTH|RESERVED|RES_TYPE|RES_ATTR|RES_SEMI|DUPLICATE|
+-------------------------------------------------------------+
|NAME    |4     |N       |N       |N       |N       |N        |
|SYSDATE |7     |N       |N       |N       |Y       |N        |
|PASSWORD|8     |N       |N       |N       |N       |N        |
|DATE    |4     |Y       |N       |N       |N       |N        |
+-------------------------------------------------------------+

You see DATE is clearly reserved, but PASSWORD has the same attributes like NAME which should be no problem, I assume.


Can I do anything about a very long running KILL command? I've got a table with 103M+ records in it and takes up 70GB on the disk.  I decided to DELETE (in one statement) 70% of the records.

As expected, this took an awfully long time to run.  I decided to KILL that DELETE command before it finished (although it had been running for approx. 350K seconds, as shown by SHOW PROCESSLIST).

The command (as shown by SHOW PROCESSLIST) has now changed to 'Killed' with the state now being 'freeing items'.  It is still running, now with a time of 450K seconds.

My question is this, is there anything that I can do to actually stop the command from running?  To stop it from doing the freeing?  Will restarting mysqld itself have that effect?

Is there anyway to judge how far through its freeing process it has gotten?

I ask because, it is actually using a lot of the machine's resources and is having an impact on the performance of the rest of the application.

show processlist;
| Command | Time   | State         | Info
| Killed  | 449822 | freeing items | DELETE FROM *****

As requested, here is the create table and delete statements in question:

 DELETE FROM adc_fetched_documents WHERE id <= 73097292

CREATE TABLE `adc_fetched_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `adc_fetch_run_id` int(20) NOT NULL,
  `adc_linked_from_id` int(20) DEFAULT NULL,
  `adc_fetched_document_blob_id` int(20) DEFAULT NULL,
  `adc_scraped_property_id` int(20) DEFAULT NULL,
  `adc_duplicate_document_id` int(20) DEFAULT NULL,
  `status` int(11) NOT NULL,
  `document_type_id` int(4) NOT NULL,
  `fetched_at` datetime NOT NULL,
  `checksum` varchar(256) DEFAULT NULL,
  `http_response_code` int(11) DEFAULT NULL,
  `content_type` varchar(256) DEFAULT '',
  `url` text,
  `http_header` text,
  `search_frag_id` int(11) DEFAULT NULL,
  `adc_property_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `FK_ADC_DUPLICATE_OF` (`adc_duplicate_document_id`),
  KEY `FK_ADC_FETCHED_DOCUMENT_BLOB` (`adc_fetched_document_blob_id`),
  KEY `FK_ADC_FETCHED_DOCUMENT_SCRAPED` (`adc_scraped_property_id`),
  KEY `FK_ADC_URL_FROM_RUN` (`adc_fetch_run_id`),
  KEY `FK_SCR_URL_LINKED_FROM` (`adc_linked_from_id`),
  KEY `index_adc_fetched_documents_on_adc_fetch_run_id` (`adc_fetch_run_id`),
  KEY `index_adc_fetched_documents_on_adc_linked_from_id` (`adc_linked_from_id`),
  KEY `index_adc_fetched_documents_on_adc_fetched_document_blob_id` (`adc_fetched_document_blob_id`),
  KEY `index_adc_fetched_documents_on_adc_scraped_property_id` (`adc_scraped_property_id`),
  KEY `index_adc_fetched_documents_on_adc_duplicate_document_id` (`adc_duplicate_document_id`),
  KEY `index_adc_fetched_documents_on_document_type_id` (`document_type_id`),
  KEY `index_adc_fetched_documents_on_checksum` (`checksum`),
  KEY `index_adc_fetched_documents_on_search_frag_id` (`search_frag_id`),
  KEY `index_adc_fetched_documents_on_adc_property_id` (`adc_property_id`),
  CONSTRAINT `adc_fetched_documents_fetch_run` FOREIGN KEY (`adc_fetch_run_id`) REFERENCES `adc_fetch_runs` (`id`),
  CONSTRAINT `adc_fetched_documents_parent` FOREIGN KEY (`adc_linked_from_id`) REFERENCES `adc_fetched_documents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `adc_fetched_documents_scraped_property` FOREIGN KEY (`adc_scraped_property_id`) REFERENCES `adc_scraped_properties` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=107925926 DEFAULT CHARSET=latin1

As for my it's taken so long to perform the DELETE, I'm not too worried about for now.  I assume that there are various variables that need tweaking.

Many thanks in advance for any help.

First of all, thank you posting the DELETE query and the table layout
With regard to your question, there is nothing you can really do because a rollback is being done via the UNDO tablespace inside ibdata1 (it should have grown immensely). If you kill the mysqld process and restart it will just pickup where it left off as part of the crash recovery cycle.
Here is how you can handle large and small deletes in the future:
LARGE DELETEs
Instead of deleting 70% of the table and create lots of rollback info, try copying the tables you want to keep.
CREATE TABLE adc_fetched_documents_new LIKE adc_fetched_documents;
INSERT INTO adc_fetched_documents_new
    SELECT * FROM adc_fetched_documents
    WHERE id > 73097292
;
ALTER TABLE adc_fetched_documents RENAME adc_fetched_documents_zap;
ALTER TABLE adc_fetched_documents_new RENAME adc_fetched_documents;
TRUNCATE TABLE adc_fetched_documents_zap;

Whenever, you delete 70% or more of the table, do it like this.
SMALL DELETEs
If you are deleting smaller chunks, perhaps you can do it with a DELETE JOIN:
CREATE TABLE deletion_ids (id INT NOT NULL PRIMARY KEY (id)) ENGINE=InnoDB;
INSERT INTO deletion_ids
    SELECT id FROM adc_fetched_documents
    WHERE id <= 73097292
;
DELETE B.* FROM deletion_ids A INNER JOIN adc_fetched_documents B USING (id);

Give it a Try !!!


Why is datalength of decimal 5 regardless of precision? I'm trying to better understand numeric types in SQL and have read that the decimal type will always require 17 bytes. However, the MS Docs list a table indicating the amount of space used depends on the decimal's precision. So I tried to test it using the datalength function.

create table tbl_TestDec(dec1 decimal(19,4), dec2 decimal(20,4), dec3 decimal(9,4))

insert into tbl_TestDec
select 1, 1, 1

select datalength(dec1), datalength(dec2), datalength(dec3) from tbl_TestDec

This outputs:

5   5   5

I was expecting either 9  13  5 or 17  17  17. I'm using SQL Server 2005. Are all decimals vardecimal or am I misunderstanding the datalength function?


SQL Server FullText search on non-persisted columns Let's say I have a table with 3 columns: Id, ColA and ColB. Then I created a FullText index:

CREATE FULLTEXT INDEX ON [Table]
(
    ColA LANGUAGE 1033,
    ColB LANGUAGE 1033
) KEY INDEX [PK__table_id]

However, I figured that if I do a query like this: ... WHERE CONTAINS(*, '"this" AND "that"') it will return only results that contain the words "this" and "that" in the same column (ie, either ColA contains both words or ColB contains both words, but "this" in ColA and "that" in ColB won't match).

As a solution I found I can do this:

ALTER TABLE [Table] ADD
    Combo AS [ColA] + CHAR(32) + [ColB]

CREATE FULLTEXT INDEX ON [Table]
(
    Combo LANGUAGE 1033
)

Notice that the computed column is NOT persisted.

The question is: is there any drawback with having a full-text index on a non-persisted computed column? I couldn't find any information related to this and don't have the resources to do performance tests other than in production. But it seems to work completely fine in my tests.

The only reference I found to this is in MSDN and but it doesn't say much.

There isn't any kind of drawback with having a full-text index on a non-persisted column. Creating an index and putting a non-persisted column, the column will be automatically persisted on disk. You can try by yourself creating a nonclustered index and putting a non-persisted column. After this, you can use the command DBCC PAGE for looking the entire data of the nonclustered index and you will see the non-persisted column there.

Have one thing on mind. the non-persisted column stills non-persisted on the clustered-index.


Is it bad to use NULL in foreign key column to mean something (e.g. this is the default data)? I have tried searching for good guidance on this already, but without much luck. Still, apologies in advance if this is duplicated elsewhere.

The Problem

In a nutshell, we pay external contractors to work on cases for our clients. We already have tables with contractor and client information in our SQL Server database. Going forward we'd like to store accounting and billing info in there too. Pay rates can differ for each client and contractor, but usually each client has a general "default" pay rate that applies to most contractors.

Option A

The initial proposal was to create a new table with the following basic design:

clientContractorPay

clientID - foreign key to client table
contractorID - foreign key to contractor table
basePay - pay rate for this client-contractor combination
... - several more (10+ and likely to grow) columns with supplemental pay rate details
A unique index (on clientID and contractorID) to optimize lookup and also prevent multiple rows for a given client-contractor combination.

Contractor-specific pay rates would naturally be linked to the relevant contractor (and client). General (default) pay for a client would be stored in a row where contractorID is NULL. This is to avoid duplicating the same default pay for all contractors that don't have specific exceptions. For example:

| clientID | contractorID | basePay | ... |
===========================================
|    1     |      75      |   12.5  | ... |
|    1     |      82      |   15.5  | ... |
|    1     |     NULL     |   10.0  | ... |  /* Default pay rate for client 1 cases */

Option B

However, one of our senior devs has strong reservations about Option A. Their main argument is that using NULL in the contractorID column to mean "this is the default pay rate row" is unintuitive and/or confusing. In other words, it's bad to assign meaning to NULL values.

Their counter proposal was to duplicate these new pay rate columns in the client table. The data stored there would indicate the default pay for each client, while contractor-specific exceptions would still live in the new table above.

What To Do?

It seems clear both proposals would work just fine, but I have my own reservations about the second. Mainly it seems wrong to store the same type of data (client-contractor pay rate details) in multiple places, not to mention more complex logic to read/write this data. I also don't like duplicating these new columns in both tables, since it would force us to add any future pay rate columns to both tables.

However, I can see my colleague's point about potentially misusing NULL in this case. At the very least, it's not immediately obvious that rows with a NULL contractorID contain default pay rates.

It's been far too long since my database programming courses, so I'm not sure what the current best practice for this type of entity relationship is? I'm open to whatever is best long term, and would appreciate any expert guidance, especially with links to additional resources.

Thank you in advance!

There is a third design option which is to separate your pay rates data into a separate table with no client or contractor information. This table contains every unique pay rate across all clients, contractors etc.

You then create a linking table to link clients, contractors and pay rates, plus add a column to your client table that references the ID of the default pay rate for that client.

This is a rough diagram of what I mean:

This should avoid the negatives of your previous options- you're not giving NULL value rows meaning (not that this is inherently wrong, but there are risks to data integrity by using NULL to infer a relationship to other data), and you're also not storing multiple copies of the same data or blurring the entities in your data model together.

This design also means you can reuse existing pay rates across multiple clients/contractors without duplicating data.


Sql Server Replication requires the actual server name to make a connection to the server I get the following message when I want to create a Remote Distributor.
First i delete my whole configuration of replication then after i take a first step to create new replication and i get below error for creating a Distributor....

I already do this but still same error........

sp_dropserver 'NODE-XYZ';
GO
sp_addserver 'NODE-XYZ', local;
GO

when run this command to get actual name then i get 'NULL' value

SELECT @@SERVERNAME
GO

You need to restart your instance after doing sp_addserver in order to get a proper @@SERVERNAME response. This is from BOL:

  The local definition takes effect only after the Database Engine is restarted. Only one local server can be defined in each instance of the Database Engine.


How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist I have a SQL server 2008 instance running. Unfortunately during testing I deselected the sysadmin rights for my login and now can't readd it (because I don't have sysadmin rights).

There are no other sysadmin accounts for the instance except SA.

I was set up for just Windows Authentication so I hacked the LoginMode in the registry to 2 so I could login as SA using Sql Authentication. This does indeed set the login mode to Mixed, however the SA user is disabled by default and I can't reenable it because I am don't have sysadmin rights.

How do I enable the SA login so I can get in and reassign the sysadmin to my normal account? Is there a registry setting for this too or is it stored in the master database?

An actual backdoor into SQL Server does exist that does not require restarting and/or rebooting anything into single-user mode. I have done this on systems where I did not have access but needed to check stuff.

Download PSexec tools from here. Place this on the server and then in a command prompt execute this command:psexec -i -s SSMS.exe, or sqlwb.exe

This will open up SSMS as the system account that has sysadmin access to the instance of SQL Server. This is done during installation of SQL Server, however I have heard that this will not be so with SQL 2012.


Take 1 row of data from MySQL and Split the Results into a Table I have a mysql database that looks like this:

proj_id | job1_name | job1_type | job1_time | job2_name | job2_type | job2_time | ...
--------+-----------+-----------+-----------+-----------+-----------+-----------| ...
      1 | John      | Roof      |        10 | Jane      | Floor     |         2 | ...

Actual table has these three fields (name, type, and time) for up to 5 jobs.

I need the results to display in a table like this:

proj_id |  Name  |   Type   | Time
--------+--------+----------+------
      1 | John   | Roof     |   10
      1 | Jane   | Floor    |    2

How can I accomplish this?

thanks for all the help!!

Jay

Using Union, you can get the results as you want. But I'm not sure whether it is a good solution.

SELECT `proj_id`,`job1_name` AS 'Name',`job1_type` AS 'Type',`job1_time` AS 'Time'
FROM `job`
WHERE 1
UNION
SELECT `proj_id`,`job2_name` AS 'Name',`job2_type` AS 'Type',`job2_time` AS 'Time'
FROM `job`
WHERE 1

Why you are having 5 jobs data in different fields. You can add job_id for the reference and other fields as common.


SQL Server DMV to show if database is used What is the best indicator in finding out whether a database is utilized or accessed?

I tried index statistics and  based on I/O utilization but none seem to be a good indicator.

Problem: I have a lot of databases on a development environment and due to space issues we are trying to decommission those that are not used without actually messing with the ones that are really needed.

SQL Server doesn't really track database access in the way you want, at least going backward (you can set up things like server-side trace, extended events, auditing, etc. going forward).

There is one ballpark thing you can use: DMVs that track index usage and procedure/trigger/query stats. For example:

;WITH d AS
(
  SELECT d = database_id, name FROM sys.databases
  WHERE state = 0 AND database_id BETWEEN 5 AND 32766
),
index_usage(d,lsk,lsc,llk,lupd) AS
(
  SELECT database_id, MAX(last_user_seek), MAX(last_user_scan),
    MAX(last_user_lookup), MAX(last_user_update)
  FROM sys.dm_db_index_usage_stats
  WHERE database_id BETWEEN 5 AND 32766
  GROUP BY database_id
),
proc_stats(d,lproc) AS
(
  SELECT database_id, MAX(last_execution_time)
    FROM sys.dm_exec_procedure_stats
    WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
),
trig_stats(d,ltrig) AS
(
  SELECT database_id, MAX(last_execution_time)
    FROM sys.dm_exec_trigger_stats
    WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
),
query_stats(d,lquery) AS
(
  SELECT t.[dbid], MAX(s.last_execution_time)
    FROM sys.dm_exec_query_stats AS s
    CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
    WHERE t.[dbid] BETWEEN 5 AND 32766
    GROUP BY t.[dbid]
)
SELECT d.name,i.lsk,i.lsc,i.llk,i.lupd,p.lproc,t.ltrig,q.lquery
FROM d LEFT OUTER JOIN index_usage AS i ON d.d = i.d
LEFT OUTER JOIN proc_stats  AS p ON d.d = p.d
LEFT OUTER JOIN trig_stats  AS t ON d.d = t.d
LEFT OUTER JOIN query_stats AS q ON d.d = q.d;

Note that these statistics aren't completely reliable, since you may not have any stored procedures, and the queries found in sys.dm_exec_query_stats may reference more than one database, and may not ever reflect the one you are concerned about.

Also, they are reset when SQL Server is restarted, or a database is detached / attached or restored, or when a database is auto-closed, and can also depend in some cases on the plans still being in the cache (which another database could completely take over within minutes). So if you are looking into the past, unless you know none of these things have happened for an entire business cycle, I wouldn't rely on just these numbers to determine whether a database is used (there could also be automated processes that are making a database look current, even if you don't care that those automated processes will fail when you remove the database).

Another note is that certain index access may not be tracked in the index usage views; for example, in SQL Server 2014, which adds memory-optimized tables, activity against those hash indexes are not captured this way (and the views where you think the activity would be captured, like sys.dm_db_xtp_hash_index_stats, do not include any date/time columns). If you are using SQL Server 2014 and in-memory OLTP ("Hekaton"), you may want to add some research to cover those objects (in case they are the only ones being referenced in a database).

And one more note is that the queries captured by sys.dm_exec_query_stats could be false positive. For example, if your database has filestream/filetable, you will see these queries being run by the system occasionally:

select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
from [database].[sys].[filetable_updates_<some_id>] with (readpast) order by table_id

So, you may want to add additional filtering to the above query to filter those out (as long as the filter doesn't accidentally filter out queries you do care about). This is probably a safe addition to that derived table:

AND t.[text] NOT LIKE N'%oplsn_fseqno%'

In the end, the safest thing to do in a dev environment is to take the databases you're not sure about offline for a week. If nobody complains, back them up, and drop them. If it takes more than a week for someone to notice they're missing, you can always restore them (there or elsewhere).

I've blogged about this a bit as well:

https://sqlblog.org/2008/05/06/when-was-my-database-table-last-accessed


What is the Best Database Design for multiple form which have some similar column, but some of it does have extra column that other form does not have I'm planning to develop a Shopfloor Database System. What are the best database design pattern should i apply to my system. Each of the process have their own form and own field, but most of the field set are similar with each other process, just some of the process may have additional extra field. In which, it was difficult for me to create just one table to keep all of the process data entry.

Below are the sample of my process and the field column that required for each of the process.

Which are the recommend database design should i apply to this? Should I just create one table to keep all those similar column field data and for the rest of the other different column name I just move it to other table with a foreign key?

You have several options:

Create one table with lots of columns

This is what Gerard H. Pille suggested in the comments: create a single table, with all possible columns for all possible processes. Where a process does not have a value for a given column, set the value to NULL.

Pros:

Relatively easy to query and understand

Cons:

Meaning of NULL in a column becomes questionable: Is there no defined value for the column for this process, or does this column not apply to this process.
Table may require update with new processes, or changes to existing processes.
Many DBMSes have a maximum number of columns per table; if you have enough processes and unique columns per process, you could hit that limit.
More difficult to enforce referential integrity at the DB level, especially if certain columns should not be NULL for certain processes, but aren't used in every process.

One table per process

Here, you would have a different table for each process. These tables would presumably have some columns in common in every table; some that only appear in some of the tables; and some unique to a given table.

Pros:

Meaning of NULL in any column is clear.
Enforcing referential integrity is relatively easy.
Less likely to have problems with hitting maximum number of columns.
Easy to query within a given process.

Cons:

Have to create a new table for every new process.
Changes that impact all processes may require the same basic updates to all process tables.
Somewhat painful to query data from all processes.
Harder to maintain a unique global process ID, without a central table to work off.

Base process table, with EAV table

Create a base Process table to store the data common to all processes (proc date, time in/out, qty start). Then, create a table to hold all other attributes for each process. This table would have three basic columns:

the ID of the base process record (a foreign key pointing to the base Process table)
The name or ID of the attribute being stored (e.g. mc#, yld, epotek batch #)
the actual value for the given attribute

Pros:

Only store attributes that belong to the base Process table's process type
Changes to base process confined to one table; changes to other processes require no DB structural changes
No issue with hitting maximum number of columns.

Cons:

requires more disk space to store (in most cases)
More difficult to query, especially if WHERE clause needs to check values of several different attributes stored in EAV table
More difficult to enforce data integrity at the DB level (to ensure that EAV attributes are set for every base process row of a given process type, you basically need a metadata table that defines which attributes are required and which are optional, and you have to check that after each insert/update of a relevant base Process row is complete, or some similar process). If you do want to allow multiple entries for certain attributes, it becomes even more difficult to ensure your distributed Process row is valid.

Hybrid approach

Create a base process table with all column common to all processes. Create full or partial process-specific tables, with columns unique to a given process. you could even allow an EAV table for attributes that are always optional, or possibly that are new to the system.

Pros:

This can help prevent problems with the number of columns in a table.
Changes to the base process data happen in one place.
Maintaining data integrity should be easier.
Most querying is relatively simple.

Cons:

Have to create a new table for every new process.
Changes that move columns that were previously present in some process-specific tables to the base process table require a fair amount of maintenance work.

Other considerations

If you have a large number of fields that are rarely needed for sorting/searching, but just for display in reporting, then storing certain values as JSON (basically, a data structure materialized as a string value) may work well. Some DBMSes have been extended to explicitly handle JSON data, and even to allow it to be indexed.

Also, some databases may allow some sort of table inheritance. I don't know much about this, but if you have a lot of processes that are the same except for one or two unique data fields, this might allow you to set up a series of table types, all built off your base table; this could make maintenance of a "one table per process" solution easier, if a change to the base process table type would essentially flow through all the process-specific tables.

All well and good. However, in practice, the details of your specific application and of your specific data will really drive this.

If most columns are present in most processes, then a single table with a lot of columns may make a lot of sense.

If your processes are frequently in flux, a base table plus an EAV table may be the only sane solution; otherwise, you may find yourself constantly needing to alter your databases structures.

If the number of rows you'll have for certain processes will be much more significant than the rows for other processes, then it may make sense for the more significant processes to have all columns in the base process table, and to use extra tables or an EAV for the processes that will appear less frequently.

Note that different DBMSes will have their own strengths and weaknesses; some may perform better than others for a given set-up.

You also have to take your application into account. If you're already going to have to make a specific form/page for each process (reusing elements where possible, of course), then the idea of one table per process may make very good sense; If, on the other hand, you'll have a central form with the data common to all processes, and will simply list each additional attribute and value at the bottom of the common form, an EAV table would match your structure logically.

Ultimately, what's best for you will come down to the specifics of the problems your application is intended to resolve; the amount of time you expect to spend in maintenance on it; the size of maintenance windows available; the performance needs of your specific situation; and your (or your company's) preferred coding style and approach.

Obviously, we haven't seen all of your processes. If, however, almost all possible columns are used by multiple processes, and if there are no more than two or three non-common columns per process, and if your set of processes is well-defined (so new columns are unlikely to be needed), I would be inclined to use a single Process table, and have all the columns in it. Again, note all the caveats in the preceding sentence, and realize that's a personal preference - others might pick a different direction.


Jobs on the Mirror are running against the Primary I have a mirrored set of jobs between the my current primary and mirrored failover SQL Server setup.  In the past, the jobs on the mirrored server, when they are enabled, fail as they are not able to open the local database since it's in a restoring state.  However I'm now seeing the jobs succeed and are getting forwarded to the current primary databases.
I've seen this behavior for the SQL Server Driver that .Net uses so it will allow for an automatic reconnect if a failover occurs.  I haven't seen this behavior before on a SQL Job and I can't seem to find any documentation around it.
I've found two other mentions of this behavior ( one , two ) with work arounds, however I'm having a hard time locating documentation on this feature so I know if it was a configuration change that I made or if it was enabled with an updates I installed.
Is this capability documented in any of Microsoft's references?
The jobs are T-SQL steps, and they are reading and (successfully) writing.  Some are inline T-SQL and others call SPs in the target databases.  I'm using standard mirroring and not availability groups.

It seems there are many who has faced this issue in the past and they all have implemented one or the other work-around to get rid of it. Not documented at Microsoft site or presented by any known SME(Subject Matter Expert).
First of all, I would say database mirroring is deprecated and will not be supported by Microsoft in near future, so you should plan to change it.

This feature will be removed in a future version of Microsoft SQL
Server. Avoid using this feature in new development work, and plan to
modify applications that currently use this feature. Use Always On
availability groups instead.

Having said above, I think there are few interesting work arounds which could be used to avoid this issue:

To disable SQL Server Agent jobs for the database which are part of
database mirroring. This can be done using code given at end:
To use database name as a first step for jobs running on secondary database, since database will be restoring or stand-by state. Job will fail.
Stop SQL Server Agent if all the jobs are related to databases part of database mirroring.
Get the value of mirroring role from msdb.sys.database_mirroring and include this in your job step. Read more here how to do it.

To disable job steps, you may use below script if there are many jobs:
DECLARE @sql nvarchar(max)
    ;WITH cte AS (
    SELECT  j.job_id,
            j.[name]
    FROM msdb.dbo.sysjobs j
    WHERE j.[name] like 'Job_Name%'
    )
    SELECT @sql = (
        SELECT 'EXEC msdb.dbo.sp_update_job @job_id = '''+CAST(job_id as nvarchar(max))+''', @enabled = 0;'+CHAR(10)
        FROM cte
        FOR XML PATH('')
    )
    EXEC sp_executesql @sql

Hope this helps.


User-defined function returns first character only although RETURN VARCHAR(10) is defined? Everyone,
I cannot figure out what is missing in the below user-defined function:
CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money)
RETURNS VARCHAR(10)
AS
BEGIN
    IF (@Salary < 30000) RETURN 'Low';
    ELSE IF (@Salary <= 50000) RETURN 'Average';
    ELSE RETURN 'High';
    RETURN '';
END;

SELECT dbo.ufn_GetSalaryLevel(100440)

The output is including only the first character of the 'Low'/'Average'/'High' return values:
-----------------------------------------------------------------------
H

(1 row affected)

Why is MS SQL Server Management Studio not taking the VARCHAR(10) into account?
Thanks in advance!
P.s. Please find my output below - I cannot figure out why it does not work as expected?

P.s.2: the function has a single parameter, I seem not to find another function with the same name... Below you can find the code related to the function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
    -- Add the parameters for the function here
    <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
    -- Declare the return variable here
    DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

    -- Add the T-SQL statements to compute the return value here
    SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function
    RETURN <@ResultVar, sysname, @Result>

END
GO

@@Version results

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019
13:48:23 Copyright (C) 2019 Microsoft Corporation Express Edition
(64-bit) on Windows 10 Enterprise 10.0  (Build 18363: ) 150

This is a bug with SQL Server inline functions in 2019 RTM.
DECLARE @Salary MONEY = 100440;
SELECT dbo.ufn_GetSalaryLevel(@Salary)

The expression in the constant scan (with CONVERT_IMPLICIT changed to CONVERT to make it runnable) is
SELECT CONVERT(VARCHAR(10), CASE
                              WHEN CASE
                                     WHEN CASE
                                            WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                              THEN ( 1 )
                                            ELSE ( 0 )
                                          END = ( 0 )
                                          AND CASE
                                                WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
                                                  THEN ( 1 )
                                                ELSE ( 0 )
                                              END = ( 0 )
                                       THEN ( 1 )
                                     ELSE
                                       CASE
                                         WHEN CASE
                                                WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                  THEN ( 1 )
                                                ELSE ( 0 )
                                              END = ( 0 )
                                              AND CASE
                                                    WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
                                                      THEN ( 1 )
                                                    ELSE ( 0 )
                                                  END = ( 1 )
                                           THEN ( 1 )
                                         ELSE
                                           CASE
                                             WHEN CASE
                                                    WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                      THEN ( 1 )
                                                    ELSE ( 0 )
                                                  END = ( 1 )
                                               THEN ( 1 )
                                             ELSE ( 0 )
                                           END
                                       END
                                   END = ( 0 )
                                THEN ''
                              ELSE CONVERT(VARCHAR(1), CASE
                                                         WHEN CASE
                                                                WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                  THEN ( 1 )
                                                                ELSE ( 0 )
                                                              END = ( 0 )
                                                              AND CASE
                                                                    WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
                                                                      THEN ( 1 )
                                                                    ELSE ( 0 )
                                                                  END = ( 0 )
                                                              AND CASE
                                                                    WHEN CASE
                                                                           WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                             THEN ( 1 )
                                                                           ELSE ( 0 )
                                                                         END = ( 0 )
                                                                         AND CASE
                                                                               WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
                                                                                THEN ( 1 )
                                                                               ELSE ( 0 )
                                                                             END = ( 1 )
                                                                      THEN ( 1 )
                                                                    ELSE
                                                                      CASE
                                                                        WHEN CASE
                                                                               WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                                THEN ( 1 )
                                                                               ELSE ( 0 )
                                                                             END = ( 1 )
                                                                          THEN ( 1 )
                                                                        ELSE ( 0 )
                                                                      END
                                                                  END = ( 0 )
                                                           THEN 'High'
                                                         ELSE CONVERT(VARCHAR(4), CASE
                                                                                WHEN CASE
                                                                                WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                                THEN ( 1 )
                                                                                ELSE ( 0 )
                                                                                END = ( 0 )
                                                                                AND CASE
                                                                                WHEN CONVERT(MONEY, @Salary, 0) <= ( $50000.0000 )
                                                                                THEN ( 1 )
                                                                                ELSE ( 0 )
                                                                                END = ( 1 )
                                                                                AND CASE
                                                                                WHEN CASE
                                                                                WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                                THEN ( 1 )
                                                                                ELSE ( 0 )
                                                                                END = ( 1 )
                                                                                THEN ( 1 )
                                                                                ELSE ( 0 )
                                                                                END = ( 0 )
                                                                                THEN 'Average'
                                                                                ELSE CONVERT(VARCHAR(7), CASE
                                                                                WHEN CASE
                                                                                WHEN CONVERT(MONEY, @Salary, 0) < ( $30000.0000 )
                                                                                THEN ( 1 )
                                                                                ELSE ( 0 )
                                                                                END = ( 1 )
                                                                                THEN 'Low'
                                                                                ELSE NULL
                                                                                END, 0)
                                                                                END, 0)
                                                       END, 0)
                            END, 0)

The branch with the literal High has a CONVERT(VARCHAR(1) that truncates it.
Disable inlining for that function
CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money)
RETURNS VARCHAR(10)
WITH INLINE=OFF
AS

or install the latest CU to fix this

or cast all string literals in the function to VARCHAR(10) explictly (the varchar(1) is due to the RETURN ''

or rewrite the function to be less procedural. The expression generated by the inling is pretty complex in the original code and much simpler with the below (it is able to constant fold dbo.ufn_GetSalaryLevel(100440) to 'High' at compilation time).

    CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money)
    RETURNS VARCHAR(10) AS
    BEGIN
    RETURN CASE
             WHEN @Salary < 30000
               THEN 'Low'
             WHEN @Salary <= 50000
               THEN 'Average'
             ELSE 'High'
           END;

    END;