MySQL: How to decrement an unsigned int column?

You can use a case clause

update tbl1 set col1 =  (case when col1 <= 3 then 1 else (col1 - 3) end);

As @RDFozz described :

With the OP's listed code, col1 - 3 must be computed, and since it's an unsigned integer, the result must be an unsigned integer. This code prevents the computation from happening unless the result would actually be an unsigned integer.

UPDATE :

Another possible way as suggested by @kondybas

update tbl1 set col1 = IF(col1<=3, 1, col1-3);


You need a tally table or calendar table that includes all years (that you want to report on).  Call the table Allyears or something like that.  As a field, you could put something like yearstart datetime and populate it with January 1 of each year (consider yearend as well).  Then simply join the Allyears table to TestDates and display the years you want.  Note, you have to decide if they were a customer on January 1, do you count them for the year?  Or did they have to be a customer on Dec 31st to be counted.  That will determine how you should create the table and join it.

Apparently, I'm the only one who has trouble posting Sql code, the site or my company's security doesn't allow the edits when they contain code (sometimes).


How to design database for polymorphic relationships

I would design a meta-data/value based model, as:

Or, where formality is enforced,

Being AttributeCode and ValueCode unique, textual, human-readable identifiers, alongside the corresponding AttributeId and ValueId.

For instance, the hypothetical meta-attribute length, physically stored with AttributeId = 1, would (should!) be referenced in specific queries as AttributeCode = 'LENGTH'.

(of course, AttributeCode and ValueCode could compose the PKs... but I personally prefer PKs columns to be integer-only)


Column statistics are created automatically as needed by SQL Server when the AUTO_CREATE_STATISTICS database option is ON and also by an explict CREATE STATISTICS statement.

With the AUTO_CREATE_STATISTICS database option, the query optimizer will create single column stats during query compilation as needed on columns specified in query predicates when a stats histogram on the leading column doesn't already exist and that information is useful in plan selection (i.e. non-trivial plan).

Statistics may also be created beforehand using CREATE STATISTICS. Unlike auto-created statistics, CREATE STATISTICS allows one to create statistics on multiple columns to provide better estimates for compound predicates (e.g. FirstName = 'John' and LastName = 'Doh').


A column is missing from the Select Top 1000 Rows result

First I would do as Andriy M said in the comments, try doing a refresh on the view.

If that doesn't work, drop and recreate to a new window. I know the "have you tried turning it off and on again" answer isn't cool. But it's a pretty good catch all.


Keeping "SQL Trace was started" and "SQL Trace stopped" from filling up my SQL logs

Please enable Trace Flag 3688 and restart the instance. This should prevent these messages from appearing in the SQL Server Error Log


Jump to a given line number in pgAdmin III

Following Vérace's comment, I filed a feature request: https://redmine.postgresql.org/issues/1429


Percona xtrabackup documentation indicates that you should point xtrabackup_55 to the backup-my.cnf file taken with your backup, rather than your live my.cnf file.

If you're doing the prep on the same server as the backup was taken, I'm not sure if it will make a difference.


Connect to oracle 11 on a corporate network via oracle developer 17

EDBDEV is a TNS alias.

If you search for a file called tnsnames.ora, that should contain an entry similar to below:

EDBDEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = edbdev.mycompany.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EDBDEV)
    )
  )

Based on that, you can fill in the required connection details.


mysql workbench can't write to sql history file

The sql_history directory under ~/.mysql/workbench was owned by root. I'm not sure how that happened in a default install.

However, running sudo chown -R <username>:<group> ~/.mysql/workbench/sql_history did the trick


Managing Security for a Log Shipped db across two (unconnected) domains

but we have no access at all to their domain.

Your Windows user won't work - since you don't have access to the domain or the domains are not trusted.

What you can do?

Ask the vendor to create a SQL Server user with enough rights. Assign appropriate roles, then take that SID and create a login on the secondary server. Remember that log shipping works on the database level!

e.g. Below is pseudocode that will help you:

use LS_PRIMARY
go
create user LS_User without LOGIN;
go
--- give permissions e.g. data reader ,etc

-- get the SID
SELECT sid from sys.database_principals
where type ='S' and name = 'LS_User'

--- on the secondary server (your side)

use master
go
create login LS_User with PASSWORD = 'yourSecurePassword',
SID = 0x125896541236980KIN -- This is the SID from above query


How to match string in varchar field from one table with other table?

What client language are you using?  That is where you need to split a sentence into "words" and where you will need to put the output back together after fetching the individual meanings.  Do not try to do it in SQL.


How to JOIN a table with itself while using a sub-query?

You use a subselect query as basis for t1 and t2

Like

SELECT
    t1.TagID AS TagID1, t2.TagID AS TagID2, COUNT(1)
FROM
    (SELECT
        *
    FROM
        TagMap
    WHERE
        TagID IN (SELECT
                TagID
            FROM
                Tags
            WHERE
                Articles > 50)) AS t1
        JOIN
    (SELECT
        *
    FROM
        TagMap
    WHERE
        TagID IN (SELECT
                TagID
            FROM
                Tags
            WHERE
                Articles > 50)) AS t2 ON t1.ArticleID = t2.ArticleID
        AND t1.TagID <> t2.TagID
GROUP BY t1.TagID , t2.TagID


Column store index on a large fact table

move to SQL Server 2014 that offers updateable clustered columnstore index.
if stuck to 2012, use partition switch to update the nonclustered columnstore index.


How to get the summary(sum) from Name wise and product wise

This is a long one, but it should work for you, accomplishing what you want.

The Setup

USE [test];
GO;

CREATE TABLE dbo.PaymentCollector
    (
    PaymentCollectorID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , CollectorName VARCHAR(100) NULL
    );

CREATE TABLE dbo.Product
    (
    ProductID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , ProductName VARCHAR(100) NULL
    );

CREATE TABLE dbo.Payment
    (
    PaymentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , PaymentCollectorID INT NOT NULL REFERENCES dbo.PaymentCollector(PaymentCollectorID)
    , ProductID INT NOT NULL REFERENCES dbo.Product (ProductID)
    , Amount DECIMAL(7,2) NOT NULL
    );

INSERT INTO dbo.PaymentCollector
(CollectorName)
VALUES ('John')
    , ('Anna')
    , ('Lee')
    , ('Andrew');

INSERT INTO dbo.Product
(ProductName)
VALUES ('Card')
    , ('Loan')
    , ('OD');

INSERT INTO dbo.Payment
(PaymentCollectorID, ProductID, Amount)
VALUES (1,1,100.00)
    , (2,3,50.00)
    , (4,1,200.00)
    , (1,2,30.00)
    , (3,2,40.00)
    , (1,3,10.00)
    , (2,2,100.00)
    , (1,3,250.00)
    , (3,2,40.00)
    , (4,3,60.00);

Query 1 - Horizontal

WITH CTE_Data AS
    (
    SELECT P.PaymentCollectorID
        , P.ProductID
        , SUM(Amount) AS Total
    FROM dbo.Payment AS P
    GROUP BY P.PaymentCollectorID
        , P.ProductID
    )
    , CTE_AllList AS
    (
    SELECT PC.PaymentCollectorID
        , PR.ProductID
    FROM dbo.PaymentCollector AS PC
        CROSS JOIN dbo.Product AS PR
    )
SELECT PC.CollectorName
    , PR.ProductName
    , C.Total
FROM CTE_AllList AS A
    LEFT OUTER JOIN CTE_Data AS C ON C.PaymentCollectorID = A.PaymentCollectorID AND C.ProductID = A.ProductID
    INNER JOIN dbo.Product AS PR ON PR.ProductID = A.ProductID
    INNER JOIN dbo.PaymentCollector AS PC ON PC.PaymentCollectorID = A.PaymentCollectorID;

Query 2 - Vertical (using Pivot)

WITH CTE_Data AS
    (
    SELECT P.PaymentCollectorID
        , P.ProductID
        , SUM(Amount) AS Total
    FROM dbo.Payment AS P
    GROUP BY P.PaymentCollectorID
        , P.ProductID
    )
    , CTE_AllList AS
    (
    SELECT PC.PaymentCollectorID
        , PR.ProductID
    FROM dbo.PaymentCollector AS PC
        CROSS JOIN dbo.Product AS PR
    )
    , CTE_Pivot AS
    (
    SELECT PC.CollectorName
        , PR.ProductName
        , C.Total
    FROM CTE_AllList AS A
        LEFT OUTER JOIN CTE_Data AS C ON C.PaymentCollectorID = A.PaymentCollectorID AND C.ProductID = A.ProductID
        INNER JOIN dbo.Product AS PR ON PR.ProductID = A.ProductID
        INNER JOIN dbo.PaymentCollector AS PC ON PC.PaymentCollectorID = A.PaymentCollectorID
    )
    SELECT CollectorName, [Card], [Loan], [OD]
    FROM CTE_Pivot
        PIVOT (SUM(Total) FOR ProductName IN ([Card], [Loan], [OD])) AS PT;


How is a composite database different from an ESB in that both provide a single point of access for multiple data sources

Consider data virtualization solutions like Composite to be one level below the ESB in an enterprise architecture.  A virtual view in Composite can bridge n data sources transparently to the code in any given service.  So while you COULD implement all that multi-source joining at the ESB level, it would be much more complex, probably slower, and ultimately tightly couple your services to your data stores.  DV provides an abstraction layer so that you properly separate concerns in your architecture and allow your ESB to encapsulate business logic without getting into the dirty details of data storage locations.


what is more important on a query execution plan, cardinality or selectivity?

Since one is the function of the other, they are equally important.

Implementation details differ between various DBMSes, but in general the process that collects table and index statistics calculates cardinality for key columns and stores these values wherever it keeps statistic information, usually some catalog tables.

For example, in addition to the full table cardinality (180 in your example for table tickets) it may gather and store cardinalities (i.e. the number of distinct values) for some columns. This allows the optimizer to estimate selectivity for those columns, dividing the column cardinality by the table cardinality.

Many DBMSes also maintain histograms for key columns, that is, cardinalities of specific values in these columns. In your example the engine might store among other statistics the fact that there are 10 rows with 'Canada' in the country column, 18 rows with 'USA', 8 rows with 'Brazil' etc.

Similarly, statistics for an index would contain not only the total number of (potentially) non-unique key values, but also the number of distinct key values for the entire key and also often for each "sub-key"*, allowing the optimizer to compute selectivity when needed.

* As an example, for a non-unique index containing columns A, B, and C the engine would store the full index cardinality (which is equal to the table cardinality), and the distinct counts of combinations (A, B, C), (A, B), and (A).


When an object is created, it is assigned an owner. The owner is
normally the role that executed the creation statement. For most kinds
of objects, the initial state is that only the owner (or a superuser)
can do anything with the object. To allow other roles to use it,
privileges must be granted.
The right to modify or destroy an object is always the privilege of the owner only.

http://www.postgresql.org/docs/current/static/ddl-priv.html


Is there a way to programmatically script all objects associated with a given table?

This is a technique in C# using Server Management Objects - I don't know of a way to do it in pure T-SQL.


Creating a partition on my soft-delete indicator column

The following is a sample script I just created that shows how to create a table that contains an IsDeleted Field.

After the table is created and filled with 10,000 rows, I add a partitioning function and scheme, and some filegroups and files to contain the partitions.  In real life, you would want to create these filegroups and files on dedicated LUNs to take advantage of extra I/Os and piecemeal restore.

I then alter the table so it makes use of the partitioning function, thereby moving the existing rows into the appropriate filegroup.  Since you cannot perform an 'ALTER TABLE...ALTER CONSTRAINT` to alter the primary key to use the partitioning function, you are forced to drop the primary key and recreate it with the new partitioning function.  This does mean the clustered index will be turned into a heap until you can create the new clustered index with the partitioning function.

USE Test;
GO
CREATE TABLE IsDeletedTest
(
    IsDeletedTestID INT NOT NULL CONSTRAINT PK_IsDeletedTest PRIMARY KEY
        CLUSTERED IDENTITY(1,1)
    , IsDeleted BIT NOT NULL CONSTRAINT DF_IsDeletedTest_IsDeleted
                DEFAULT ((0))
);
GO
/* INSERT 10,000 rows into IsDeletedTest */
INSERT INTO IsDeletedTest (IsDeleted) VALUES (CAST(RAND() * 2 AS INT));
GO 10000
ALTER DATABASE Test ADD FILEGROUP IsDeleted0FG;
ALTER DATABASE Test ADD FILE
    (NAME='IsDeletedFile0', FILENAME='C:\SQLServer\Data\IsDeletedFile0.mdf')
    TO FILEGROUP IsDeleted0FG;
ALTER DATABASE Test ADD FILEGROUP IsDeleted1FG;
ALTER DATABASE Test ADD FILE
    (NAME='IsDeletedFile1', FILENAME='C:\SQLServer\Data\IsDeletedFile1.mdf')
    TO FILEGROUP IsDeleted1FG;
ALTER DATABASE Test ADD FILEGROUP IsDeleted2FG;
ALTER DATABASE Test ADD FILE
    (NAME='IsDeletedFile2', FILENAME='C:\SQLServer\Data\IsDeletedFile2.mdf')
    TO FILEGROUP IsDeleted1FG;
GO
/* Create a partition function using a BIT datatype for the valid values
     of 1 and 0 */
CREATE PARTITION FUNCTION IsDeletedPF (BIT)
AS RANGE LEFT FOR VALUES (0,1);

/* Create a partition scheme that implements the partition function */
CREATE PARTITION SCHEME IsDeletedPS AS PARTITION IsDeletedPF
    TO (IsDeleted0FG, IsDeleted1FG, IsDeleted2FG);

/* MOVE the clustered index into the various filegroups
    according to partition scheme */
ALTER TABLE IsDeletedTest DROP CONSTRAINT PK_IsDeletedTest
    WITH (MOVE TO IsDeletedPS(IsDeleted));
GO
ALTER TABLE IsDeletedTest ADD CONSTRAINT PK_IsDeletedTest
    PRIMARY KEY CLUSTERED (IsDeleted, IsDeletedTestID)
    WITH (
                     ONLINE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON,
             FILLFACTOR=100 /* adjust this as necessary */
                 )
    ON IsDeletedPS(IsDeleted);

/* Show the movement of a single row from partition 1 to partition 2 */
SELECT IsDeleted, COUNT(1) FROM dbo.IsDeletedTest GROUP BY IsDeleted;
SELECT O.Name, PS.partition_number, PS.row_count
FROM sys.dm_db_partition_stats PS
    INNER JOIN sys.objects O ON PS.object_id = O.object_id
WHERE O.Name = 'IsDeletedTest';
UPDATE dbo.IsDeletedTest
    SET IsDeleted = CASE WHEN IsDeleted = 0 THEN 1 ELSE 0 END
    WHERE IsDeletedTestID = 10000;
SELECT IsDeleted, COUNT(1) FROM dbo.IsDeletedTest GROUP BY IsDeleted;
SELECT O.Name, PS.partition_number, PS.row_count
FROM sys.dm_db_partition_stats PS
    INNER JOIN sys.objects O ON PS.object_id = O.object_id
WHERE O.Name = 'IsDeletedTest';
GO

DROP TABLE IsDeletedTest;
DROP PARTITION SCHEME IsDeletedPS;
DROP PARTITION FUNCTION IsDeletedPF;
ALTER DATABASE Test REMOVE FILE IsDeletedFile0;
ALTER DATABASE Test REMOVE FILE IsDeletedFile1;
ALTER DATABASE Test REMOVE FILE IsDeletedFile2;
ALTER DATABASE Test REMOVE FILEGROUP IsDeleted0FG;
ALTER DATABASE Test REMOVE FILEGROUP IsDeleted1FG;
ALTER DATABASE Test REMOVE FILEGROUP IsDeleted2FG;

I just ran this and got the following results:

As you can see, the table had an almost perfect distribution of rows in the IsDeleted = 0 and IsDeleted = 1 state.  Updating a row from 0 to 1 resulted in the row moving to the other partition.

I hope this helps show how to alter a table to take advantage of partitioning on an IsDeleted soft-delete BIT field.

When I ran the wizard to partition this table, with the option to script to a new query window, I got this code:

USE [Test]
GO
BEGIN TRANSACTION
ALTER TABLE [dbo].[IsDeletedTest] DROP CONSTRAINT [PK_IsDeletedTest];

ALTER TABLE [dbo].[IsDeletedTest] ADD  CONSTRAINT [PK_IsDeletedTest] PRIMARY KEY NONCLUSTERED
(
    [IsDeletedTestID] ASC
) WITH (
    PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , IGNORE_DUP_KEY = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    )
ON [PRIMARY]

CREATE CLUSTERED INDEX [ClusteredIndex_on_IsDeletedPS_635194638000795374] ON [dbo].[IsDeletedTest]
(
    [IsDeleted]
) WITH (
    SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    )
ON [IsDeletedPS]([IsDeleted])

DROP INDEX [ClusteredIndex_on_IsDeletedPS_635194638000795374] ON [dbo].[IsDeletedTest]

COMMIT TRANSACTION

As far as I can tell, this code looks like it doesn't work.  It drops the PK, then recreates it, without using the partitioning function.  It then creates a clustered index, using the partitioning function, but then immediately drops it!


MySQLDump issue with a special characters

You should read about string literals in MySQL.

There you’ll learn that a backslash character is used to escape some special strings, and to have literal backslashes in your strings (as in Windows pathnames), you have to double them:

INSERT INTO test_user VALUES ('new1','C:\\newfolderone.xlsx');

The complete list of characters in need of a special treatment is at the above link.

However, it is best to have the intermediate language deal with such problems. For instance, in PHP you would call PDO::quote; other platforms and languages provide similar facilities.


Join two tables and create a new one in SQL

You need to UNION ALL the two table entries
Then take the SUM(Category) by GROUP BY ID, Name

So the query will be

SELECT ID, Name, SUM(Category) AS Category
FROM  (
    SELECT ID, Name, Category FROM Table1
    UNION ALL
    SELECT ID, Name, Category FROM Table2
) GROUP BY ID, Name

You can insert the entries into the third table.


You only need a CASE expression in the ORDER BY, to divide the rows into two groups, so the ones with class 'A' or 'B' are put in first group (1) and the all the others in the second (2):

ORDER BY
    CASE WHEN class IN ('A', 'B') THEN 1 ELSE 2 END,
    rank ;


The issue is that the default deployment options when using Script or Deploy actions are set up to avoid dropping objects so as to minimize the risk of data loss. Schema Compare, on the other hand, is targeted at an interactive mode where the actions (Add, Delete, Update) are much clearer so this doesn't occur. To generate the Drop syntax as expected, add the property shown below (from the SqlPackage.exe documentation) to your call.

Property: /p:DropObjectsNotInSource= {True | False}

Default: False

Description: Specifies whether objectsthat do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.


I need to reference the same two columns multiple times in where clause

Having your schema would be helpful in helping with your specific query. Without the full schema, here are some pointers for how you might write a query to get your results.

Assuming you have a single dbo.SurveyResults table, where there is some ID (survey_id) that is used to identify all the results from a given user's responses, you could use logic that is something like this:

Count all the "Question 35"s that have an answer of Y, and there also exists an answer for questions 4, 16, or 24 that also has an answer of Y.

That query would look something like this:

SELECT COUNT(DISTINCT r.survey_id)
FROM dbo.SurveyResults AS r
WHERE r.jq_nbr = 35
AND r.answer = 'Y'
AND EXISTS (SELECT 1
            FROM dbo.SurveyResults AS r2
            WHERE r2.survey_id = r.survey_id
            AND r2.jq_nbr IN (4,16,24)
            AND r2.answer = 'Y'
            );

With a slightly different schema, perhaps there is a "header" table (dbo.Survey) that has has one row per user-completed survey, and another table (dbo.SurveyResults) that is a child table that has one row per question answered. With this, you can use a slightly different "plain English" description, which might generate slightly different logic:

Count all the user-completed surveys where the respondent has answered "Y" to question 35, and also answered "Y" to at least on of questions 4,16, or 24.

That query might look like this:

SELECT COUNT(s.survey_id)
FROM dbo.Survey AS s
WHERE EXISTS (SELECT 1
            FROM dbo.SurveyResults AS r1
            WHERE r1.survey_id = s.survey_id
            AND r1.jq_nbr = 32
            AND r1.answer = 'Y'
            )
AND EXISTS (SELECT 1
            FROM dbo.SurveyResults AS r2
            WHERE r2.survey_id = s.survey_id
            AND r2.jq_nbr IN (4,16,24)
            AND r2.answer = 'Y'
            );


Fastest tool or technique for occasionally syncing Postgres 9.6 databases

Occasionally I need to sync latest data and schema from production to staging db server. What tools or techniques do you recommend if syncing speed is a priority? The sync doesn't need to be in realtime.

Well, normally in such a schemas you have to wipe the stating. You didn't say that explicitly but it's critical to your question. If you have people writing to staging and developing with staging then you can't just update it and assume that only the master wrote to it. In such a case your own method is to wipe and reload.

In order to that faster, you can simply, back up the data-directory or use pg_basebackup. Then when you need to reset the database,

Shut down the database,
Restore the data directory.
Copy over the wal files from the archive into ./pg_wal
Restart the server.

Note you'll have to keep around all of the WAL logs since your last base backup.

This is pretty well documented in the PostgreSQL 11 docs here,


Use a value for a Postgresql setting in a trigger

You want current_setting:

SELECT current_setting('pubsubpull.request_id');


Getting past corrupted binary log "Error in Log_event::read_log_event():"

Well much to my surprise the script I mentioned did find a good starting position and I was able to successfully recover a large portion of the binary log after the problem point.

Not the most elegant script but in case it's of use to anyone else.  Note it is very slow restarting a new process to scan byte by byte but got me results.

And yes I recognize the potential of infinite looping by never finding anything, might want to add additional check based on start position vs filesize

#!/bin/bash
BINLOG=$1
START=$2

RET=1
while [ $RET -eq 1 ]; do
    # This is relying on the mysqlbinlog found in your path
    # You'll want to make sure this resolves to a binary version matching the mysqld that generated the binlog
    mysqlbinlog --start-position=$START $BINLOG > /dev/null 2>&1
    RET=$?

    START=$(( START + 1 ))
    if [[ $(( START % 1000 )) -eq 0 ]]; then
        echo $START
    fi
done
GOODSTART=$(( START -1 ))

echo "FOUND GOOD START AT $GOODSTART"

# Saving it to a file in case youre running it overnight, script is very slow.
echo $GOODSTART > ${BINLOG}.goodstartposition


Arabic characters not displaying in oracle database

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE OPEN;

ALTER DATABASE CHARACTER SET INTERNAL_USE AR8MSWIN1256;

SHUTDOWN IMMEDIATE;

 -- or SHUTDOWN NORMAL;

STARTUP;


There is no way to distinguish the meaning of 01/02/2011 and 02/01/2011 if you have this values mixed in the same column. It is that simple.


SQL Query, get count() to output null instead of 0

Use NULLIF.
SELECT name, NULLIF(count(order_id), 0)
FROM customers
LEFT JOIN  orders
ON customers.name = orders.customername
GROUP BY name;


SDO_GEOM.SDO_INTERSECTION between a line and a polygon returns what?

Someone answered this question for me and it is as I suspected.
SDO_INTERSECTION will return any lines fully contained within a polygon, as well as those parts of intersecting lines, which are within the polygon.
In other words, it will return the geometry of everything in red in the example image.


Where does [reflection.assembly] reside?

https://support.timextender.com/hc/en-us/articles/210437663-Error-Could-not-load-file-or-assembly-Microsoft-SqlServer-ConnectionInfo-
Cause
You have not installed the SQL Shared Management Objects (SMO). They are needed to create tables and other SQL objects from TX.
Resolution
Download the SQL Shared Management Objects from the SQL Server Feature Packages and install it. The download sites are linked below - the file you need is called SharedManagementObjects.msi.
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
SQL Server 2005
SQL Server 2017
Many thanks to Tobias Eld and my apologies to the net for not finding this resource before I posted this question.


You can do it using a single statement.

CREATE TABLE T (val decimal(18,3));

INSERT INTO T VALUES (1.25);
INSERT INTO T VALUES (12.526);
INSERT INTO T VALUES (1000.25);

SELECT val FROM T;

|      val |
| -------: |
|    1.250 |
|   12.526 |
| 1000.250 |

ALTER TABLE T MODIFY COLUMN val bigint;

SELECT * FROM T;

|  val |
| ---: |
|    1 |
|   13 |
| 1000 |

dbfiddle here


Relational DBMS is not ideally suited to this type of application.  RDBMS is great for tracking and enforcing referential integrity for relations between two entities, THING A and THING B.  What you want to do is to track relationships between THING A and every other thing in your database (or lots of other things, at least).

Your options, each of which include some form of compromise, include:

Sparse Foreign Key Columns: Create an activity table that has mutually exclusive foreign key columns that point to the various kinds of activity that you want to track.  This is a fully 3NF way of doing things, but it still requires new columns to be added for new kinds of activities and it results in sparse columns, which people try to avoid as a rule.
Forrest of Tables: Create individual activity tables each of which track a particular type of activity.  You can then create a query or view that uses UNION ALL to bring the various kinds of activity together into one place for querying.  This is also very by the book with respect to normal forms, but you end up with a database cluttered with activity tables and you have to create a new table and alter a view whenever you add a new type of activity.
Data Driven Joins: Use a single activity table that captures the type of the activity and the instance of the activity in one place.  You would need to have more sophisticated logic built for retrieval of the activity details, since this approach foregoes formal foreign keys.  There is no declarative referential integrity and the approach smells a lot like EAV, which makes many people's skin crawl.  On the other hand, you wind up with a single table, no sparse columns, and the addition of a new activity type requires no schema changes, only a tweak to one piece of code - the one that reads type of activity and knows what to do to go and retrieve the details of that activity.

You need to look at where your sensitivities are going to lie.  How many types of activity will you ultimately be tracking?  How often are new types of activity going to be added to the mix?  Are you prepared to endure the scorn of colleagues when they see you've implemented a solution with sparse columns (or worse) EAV?  When you answer these questions you'll be able to pick the approach which makes the most palatable compromises for your application.


Are these custom module Drupal tables lacking foreign keys? mysqldump with table structures without data is included

The answer is yes, they are lacking foreign keys, the previous dba was lacking all referential integrity and was using an application stage enforcement of the dba logic (as discussed here: http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html). The previous dba must have been inexperienced and possibly used this method:

  For storage engines other than InnoDB, it is possible when defining a column to use a REFERENCES tbl_name(col_name) clause, which has no actual effect, and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table

However as me joining the project has proven - it is not necessarily a good way of doing it.

Given the sensitive nature of this project and the later desire to have all kinds of distributed systems accessing the db it has been decided that the overhead of having foreign key constraints is worth it for security when others may be programming and accessing the db.

Whilst my comment on my question shows that it is possible for mysqldump to strip out important stuff and leave one with a misunderstanding, in this case there was no indexing and no foreign key relationships (someone else showed me, I will include how they found the answer if I can catch and get them to tell me).

From what I've been reading and seeing, Drupal does not really commonly use foreign keys, but that's another story altogether.


Ok, there are a few things to explain first.

(1) The Connect to Server prompt within the Login properties is only asking for authentication. You cannot specify a database name within the Server Name field of that window in order to connect to it. For the server name you specify either server or server\instance name. You would connect to a specific database by setting this via the Connection Properties tab:

(2) Folks that reference using the <browser for more...> option within the login portion should understand that in a default installation of SQL Server 2012 the Browser service is disabled. So even on a local installation you will not be able to see your server via this method.

Ok, now if you need to find out the "name" of the SQL Server instance you want to connect to you can follow any of the methods below (this is not an exhaustive list, just the most common ones to use):

SQL Server Configuration Manager

Via the Start Menu you would find SQL Server Configuration Manager and open it to look at the Services to see the list of SQL Server components installed on the server. You can also access this, if on the server, via the Computer Management console (you may actually see it show up twice if more than one version of SQL Server is installed, as there is on my  machine):

PowerShell

A simple one liner can provide the same information you will see in SSCM or the Services Management Console (services.mcs) if you are local to the server:

If you happen to be remote you can try to use this command but it will not work if proper access is not granted: Get-Service *SQL* -Computer Server1

To connect to the instances you find with SSMS if you see the services listed as SQL Server (MSSSQLSERVER) this is a default instance and if you are on the physical server you can use the server name, simply put a period (.), or localhost.

If you find a named instance it will be noted as SQL Server (instance name), as on my machine I have a named instance of SQL14 so I would put localhost\SQL14 since I am on my machine. If I had this on a server and was connecting remotely it would be the actual server name instead of localhost.


The performance of mysql fulltext depend on order of keyword

https://dev.mysql.com/doc/refman/5.6/en/fulltext-fine-tuning.html#fulltext-optimize

set GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE `table`;

Please run that, time your variations again, and report back.

If it fails to help, report a bug at bugs.mysql.com .


How to do Mysql migration from 5.0 (320bit windows) to 5.6 (64 bit windows)?

You use mysqldump.

Step 1. Dump your original 32 bit database. mysqldump my32bitdb [options...] > mydb.sql

Step 2. Copy the file mydb.sql to your new server

Step 3. Load the 32 bit database mysql -u root -p my64bitdb < C:\path\to\mydb.sql

With 10GB of data, it shouldn't take much more than ~ 1hr.

For reference, see here and here.


Error with my trigger. What should I do?

Oracle doesn't have an inserted (or a deleted or an updated) table.  That's something that SQL Server provides in a trigger.  The Oracle substring function is also substr not substring.

My guess is that you just want

IF :new.fsa IS NULL
THEN
  :new.fsa := SUBSTR(:new.POSTAL_CODE, 0, 3);
END IF;


So, I found out the answer far far away in the documentation.

SELECT json_object(array_agg(language), array_agg(uri)) FROM A will give you the expected result.


Simple Query intermittently returning 0 results

Improve the situation by adding a composite index:

INDEX(awarded, awarded_date)

Is there some other process that is doing bulk changes to awarded?  Perhaps not transaction-safe?


Mutual authentication via SSL Oracle 11g

Problem was solved via using orapki for creating wallets and sertificates for client and server. This manual helped me to solve the problem.


How to setup a SQL Cluster between 2 sites in active passive mode without shared storage

It depends on what type of replication. If you can access only 1 Primary DB at a time then its active/passive. If you can access both then its active/active.

Overview : https://docs.microsoft.com/en-us/sql/database-engine/sql-server-business-continuity-dr?view=sql-server-2017

Details : Microsoft SQL Server 2017 Licensing guide


Get SSMS to show the time a query completed at?

Hitting F4 also enables properties view, which shows the start time, finish time, and run time.

Also, if you Include Client Statistics (under Query menu), you can get a history of when you ran the query, along with various statistics such as run time and number of rows.

Update, 2019-10-14: SSMS 18 now includes the completion time in the Messages pane of the results tab.


How to avoid a race condition with cross db proc calls? and other concerns

There is nothing in your SELECT that dictates order at all. It is also not protected from two sessions reading the same row. To see that it is not safe:

Create a throwaway table with a key column.

CREATE TABLE dbo.GeneratedIDs(ID INT PRIMARY KEY);

Run this code in a loop from two different sessions:

SET NOCOUNT ON;

DECLARE @DECLARE @CID INT, @CodeOUt VARCHAR(30);

SELECT @CID = CodeNumbersID, @CodeOut = CodeValue
  FROM dbFoo.dbo.CodeNumbers
  WHERE IsUsed = 0

UPDATE dbFoo.dbo.CodeNumbers
  SET IsUsed = 1
  WHERE CodeNumbersID = @CID

INSERT dbo.GeneratedIDs SELECT @CID;

GO 100000

Check the output for these - they'll happen:

  Msg 2627, Level 14, State 1
  Violation of PRIMARY KEY constraint 'PK_hexcode'. Cannot insert duplicate key in object 'dbo.GeneratedIDs'. The duplicate key value is (<some value>).

Or you might see deadlocks if you wrap the SELECT/UPDATE in an explicit transaction:

  Msg 1205, Level 13, State 52
  Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

To get around this problem, and to ensure that the ID you get is the lowest one available, you could do this:

BEGIN TRANSACTION;

SELECT TOP (1) @CID = ...
FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
WHERE IsUsed = 0
ORDER BY CodeNumbersID;

UPDATE ...

COMMIT TRANSACTION;

Note that I added an explicit transaction and also XLOCK/HOLDLOCK hints to prevent two simultaneous sessions from reading the same row. Of course, this has an impact on concurrency (which, unfortunately, is exactly what you want and need here).

Other ways to do this include just updating the row and then using a table variable to capture values from the OUTPUT clause:

DECLARE @x TABLE(CodeOut VARCHAR(30), CID INT);

;WITH x AS
(
  SELECT TOP (1) CodeNumbersID, CodeValue, IsUsed
    FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
    WHERE IsUsed = 0
    ORDER BY CodeNumbersID
)
UPDATE x SET IsUsed = 1
  OUTPUT inserted.CodeValue, inserted.CodeNumbersID INTO @x;

SELECT @CodeOut = CodeOut, @CID = CID FROM @x;

Per Paul's update, yes, you could also do this without the table variable:

;WITH x AS
(
  SELECT TOP (1) CodeNumbersID, CodeValue, IsUsed
    FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
    WHERE IsUsed = 0
    ORDER BY CodeNumbersID
)
UPDATE x
  SET @CodeOut = x.CodeValue, @CID = x.CodeNumbersID, IsUsed = 1;

(Though I am not all that fond of this syntax; not sure why. Might be the same reason I always forget it exists.)

You could change the caller to expect a resultset instead of two output parameters, but that is extra work too. In both cases you'd still need to ensure you got the lowest ID available, which likely means a CTE with a SELECT and the same hints. Some discussion here. I also talk about a similar approach in this blog post but I didn't get into anything about concurrency and two sessions trying to delete the same row at the same time. Obviously in that case only one of them can win, but with an UPDATE they could both manage to be successful (at least in theory).

To make things easier, you could relax the restriction that the "next" ID handed out is the lowest ID available. But you still need the isolation through the hints to ensure that two simultaneous sessions don't happen to read the same value (which could happen regardless of ordering). Hopefully with a suitable index these won't destroy concurrency.


Understanding Explain Plan on Composite Index

"c" is just being used as an in-index filter.  It jumps to the part of the index where "a"=1 and scans it until "a" becomes >1.  For all of those rows it filters out anything where "c" is not true, based on the value of "c" found in the index.

It is unfortunate that the EXPLAIN syntax doesn't distinguish a column used for jumping to a specific part of the index (or stopping in index scan at the end of the relevant values), from one just used for filtering within the index.  But EXPLAIN doesn't understand the internals of indexes, it just knows that "c" is being used, it doesn't know how efficiently it is being used.


Log shipping Primary database log backup job failing/stuck

How can i troubleshoot this further?

Since you can't make it happen every time a backup occurs, I'd run procmon and filter against the folder you're sending the log backups. When it occurs, stop the procmon and investigate what applications are taking locks on the file. My guess, though, is going to be antivirus or some other filter driver filesystem watcher. The fact it happens more frequently on larger files is a giveaway.

If you know it's going to fail, or is currently having issues you could use process explorer and search for the file in the handles and see what processes are touching it.

This is happening at the windows/driver level, not the SQL Server application level.


Log file is about to be larger than disk, query still executing

If you run out of space the Query will rollback. You need to use transactions and delete the rows in batches and depending on what type of recovery model the database is in you will need to do LOG backups or Checkpoints.

Here is a stored procedure i built to remove milions of rows from an AX database in a safe way. Note that if your database is using a DPM system or something like that you will have to figure out someway to handle the LOG backups. Feel free to use it and edit it as you please.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Daniel Björk
-- Create date: 2017-07-27
-- Description: Delete rows in batches
-- =============================================
CREATE PROCEDURE csp_DeleteINVENTSUMLOGTTS
    -- Input parameters
    @DeleteOlderThanDays INT,
    @MaxDeletedRows INT = -1,
    @MaxTimeMinutes INT = -1,
    @BatchSize INT = 25000,
    @LogLevel INT = 1 -- 0 = OFF, 1 = Only Total Values, 2 = Verbose
AS
BEGIN
    SET DEADLOCK_PRIORITY LOW;

    -- Logical Default Values
    DECLARE @StartMessage AS NVARCHAR(MAX);
    DECLARE @StartTime DATETIME = GETUTCDATE();
    DECLARE @DeletedRows INT = 1;
    DECLARE @TotalDeletedRows INT = 0;
    DECLARE @Error int = 0
    DECLARE @ErrorMessage NVARCHAR(max)

    -- Print startup parameters
    IF (@LogLevel >= 1)
    BEGIN
        SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTime,120) + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(schemas.name) FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)

        SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + 'Parameters:'  + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + '@DeleteOlderThanDays = ' + ISNULL(CAST(@DeleteOlderThanDays AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + '@DataAreaIdParm = ' + ISNULL(CAST(@DataAreaIdParm AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + '@MaxDeletedRows = ' + ISNULL(CAST(@MaxDeletedRows AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + '@MaxTimeMinutes = ' + ISNULL(CAST(@MaxTimeMinutes AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + '@BatchSize = ' + ISNULL(CAST(@BatchSize AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + '@LogLevel = ' + ISNULL(CAST(@LogLevel AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)

        PRINT @StartMessage
    END

    -- Validate input parameters
    IF (@DeleteOlderThanDays < 0)
    BEGIN
        SET @ErrorMessage = 'Invalid value of @DeleteOlderThanDays' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
    END
    IF (@MaxDeletedRows < -1)
    BEGIN
        SET @ErrorMessage = 'Invalid value of @DeleteOlderThanDays' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
    END
    IF (@MaxTimeMinutes < -1)
    BEGIN
        SET @ErrorMessage = 'Invalid value of @MaxTimeMinutes' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
    END
    IF (@BatchSize <= 0)
    BEGIN
        SET @ErrorMessage = 'Invalid value of @BatchSize' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
    END

    -- Delete Logic
    WHILE (@DeletedRows > 0
            AND (DATEADD(Minute, @StartTime, @MaxTimeMinutes) > GETUTCDATE() OR @MaxTimeMinutes = -1)
            AND (@MaxDeletedRows > @TotalDeletedRows OR @MaxDeletedRows = -1))
      BEGIN
       BEGIN TRANSACTION

        -- Delete from InventSumLogTTS
        DELETE TOP (@BatchSize)
        FROM [dbo].InventSumLogTTS
        WHERE
        DATEDIFF(DAY, @StartTime, DATEADD(DAY,@DeleteOlderThanDays, DATESTATUS)) < 1

        -- Compute totals
        SET @DeletedRows = @@ROWCOUNT;
        SET @TotalDeletedRows = @TotalDeletedRows + @DeletedRows

        IF (@LogLevel >= 2)
        BEGIN
            PRINT 'Rows deleted so far: ' + CAST(@TotalDeletedRows AS NVARCHAR(32))
            PRINT 'Total runtime in sec so far: ' + CAST(DATEDIFF(Second, @StartTime, GETUTCDATE()) AS NVARCHAR(32))
        END

       COMMIT TRANSACTION

      -- Only needed if you are using Recovery Model Simple or you have a small log disk that might fill up
      -- CHECKPOINT;    -- if simple
      -- BACKUP LOG ... -- if full
    END

    IF (@LogLevel >= 1)
    BEGIN
        PRINT 'Total rows deleted: ' + CAST(@TotalDeletedRows AS NVARCHAR(32))
        PRINT 'Total time in sec: ' + CAST(DATEDIFF(Second, @StartTime, GETUTCDATE()) AS NVARCHAR(32))
    END
END
GO


Replication with encrypted columns, does the 2nd server have to have the same master key and symmetric key or is it done automatically?

With ordinary column encryption (not AlwaysEncrypted), the columns are simple varbinary columns, with no special configuration or metadata.  So replication of the cyphertext "just works".

To decrypt on the subscriber you would need the symmetric key, either by Creating Identical Symmetric Keys on Two Servers, or by restoring a backup of the publisher database containing the symmetric key before initializing the subscription (and restoring the Certificate or Master Key that encrypts the symmetric key).


Why does Type Resolution fail when using SELECT DISTINCT instead of SELECT on PostgreSQL?

Without the DISTINCT, the type of the second part of the UNION ALL is unknown:
SELECT pg_typeof(NULL);

 pg_typeof
-----------
 unknown
(1 row)

This gets resolved to integer.
If there is a DISTINCT, PostgreSQL has to resolve the type of NULL earlier on: to eliminate duplicates, it has to know which equality operator to use. Lacking other information, it chooses text as the preferred type of the string category.
Then you get the error because integer and text cannot be reconciled.
The documentation describes the type choice for the equality operator as follows:

If any input arguments are unknown, check the type categories accepted at those argument positions by the remaining candidates. At each position, select the string category if any candidate accepts that category. (This bias towards string is appropriate since an unknown-type literal looks like a string.)


The script is missing a left join to sys.types to grab table type information, which also requires execute permissions to use (alternatively join sys.table_types, you'll get the same data). Try the following:

SELECT  DB_NAME() AS 'DBName' ,
    p.[name] AS 'PrincipalName' ,
    p.[type_desc] AS 'PrincipalType' ,
    p2.[name] AS 'GrantedBy' ,
    dbp.[permission_name] ,
    dbp.[state_desc] ,
    CASE WHEN [dbp].[class_desc] = 'DATABASE' THEN 'DATABASE'
         ELSE COALESCE(so.[name], t.name)
    END AS 'ObjectName' ,
    CASE WHEN [dbp].[class_desc] = 'DATABASE' THEN 'DATABASE'
         ELSE COALESCE(so.[type_desc], N'TYPE')
    END AS 'ObjectType'
FROM    [sys].[database_permissions] dbp
        LEFT JOIN [sys].[all_objects] so ON dbp.[major_id] = so.[object_id]
        LEFT JOIN [sys].[database_principals] p ON dbp.[grantee_principal_id] = p.[principal_id]
        LEFT JOIN [sys].[database_principals] p2 ON dbp.[grantor_principal_id] = p2.[principal_id]
        LEFT JOIN [sys].[types] t ON dbp.major_id = t.user_type_id
WHERE   p.[name] = 'db_executor';


Specific changes to glibc 2.28 that affect PostgreSQL

I'm not sure what the minor change were but even minor changes affect how the index stores data on disk, any difference would result in a collision and/or corruption when using a index that had the old sort method.  data is could be looked-up and/or stored in the wrong places..

This requires drop and rebuilding of the index
The source code to glibc can be found here

https://packages.debian.org/buster/glibc-source

Postgresql did add a new feature I think in 10 ICU
which allows the creation of custom Collation tables.
https://www.2ndquadrant.com/en/blog/icu-support-postgresql-10/

Which could be used to avoid these problems in the future


Why max_allowed_packet is larger in mysqldump than mysqld in my.cnf?

The max_allowed_packet variable limits the size of a single result set. In the [mysqld] section, any normal connection can only get 1M worth of data in a single query.
In mysqldump you typically produce "extended INSERT" queries, where you list multiple rows within the same INSERT command. It's better, then, to have this variable set high.

The above is a brief explanation. Now, as for your question "What is the point?" -- there is no clear answer. The my-XXXXX.cnf set of files are long since outdated, and have their issues, including recommendations which are commonly not agreed upon (like the amount of memory you should allocate to the innodb_buffer_pool_size). So take it as a general starting point. In particular, these settings relate to systems with very low memory available (like 128M RAM), so the numbers don't really apply to today's hardware.

One could argue that you won't necessarily import the dump into same server, so possible to another server with higher setting for max_allowed_packet.

As a side note, I usually set max_allowed_packet to very high values, like 32M or 64M. As opposed to other variables like sort_buffer_size, with max_allowed_packet you only pay for what you need. So a connection will not allocate the full 64M if it doesn't have to.

A 1M limit means you could have some row with some TEXT or BLOB, possibly larger than 1M, and in such case, you would actually be unable to read that row.


Giving selective permissions in a database

You'll have to grant them appropriate permissions at the database level to indicate what they should be able to do. Then grant ALTER permission (or make them the owner) on the schemas in which they can do these things.

GRANT CREATE TABLE, CREATE PROCEDURE TO [AD\dbusersgroup_limitedrights]
GRANT ALTER ON SCHEMA::[DIS] TO [AD\dbusersgroup_limitedrights]


$ oerr ora 40573
40573, 00000, "Invalid use of PL/SQL JSON object type."
// *Cause:  An attempt was made to use a PL/SQL JavaScript Object Notation
//          (JSON) object type in an invalid context.
// *Action: Use PL/SQL JSON object types only inside PL/SQL.

Example:

set serveroutput on
declare
  l_objects tmp_011040_t2;
  l_index pls_integer;
begin
  l_objects := tmp_011040_t2();
  l_objects.extend(1);
  l_objects(l_objects.last) := JSON_OBJECT_T.parse('{"a":"1","b":"1"}');
  l_objects.extend(1);
  l_objects(l_objects.last) := JSON_OBJECT_T.parse('{"a":"2","b":"2"}');
  l_index := l_objects.first;
  while (l_index is not null)
  loop
    dbms_output.put_line(l_objects(l_index).get_string('a'));
    l_index := l_objects.next(l_index);
  end loop;
end;
/

1
2

PL/SQL procedure successfully completed.


How can I get hours worked totaled for a column?

If you group by the punch in and out times you will ensure your aggregates do not take place at the user level. Take that out and add a sum around your timediff to aggregate the time worked. Also, Username, unless it is unique and corresponds to Emp Number may cause grouping issues.

SELECT
    `ohrm_user`.`emp_number`,
    `ohrm_attendance_record`.`punch_in_user_time`,
     `ohrm_attendance_record`.`punch_out_user_time`,
     `ohrm_user`.`user_name`,
     SUM(time_to_sec(TIMEDIFF (punch_out_user_time,punch_in_user_time)) /60) AS time_to_sec_TIMEDIFF_punc,
     SEC_TO_TIME(SUM(TIME_TO_SEC(ohrm_attendance_record.punch_out_user_time) - TIME_TO_SEC(ohrm_attendance_record.punch_in_user_time))) AS SEC_TO_TIME_SUM_TIME_TO_S,
     TIMEDIFF(MIN(ohrm_attendance_record.punch_in_user_time),
     MAX(ohrm_attendance_record.punch_out_user_time)) AS TIMEDIFF_MIN_ohrm_attenda
FROM
     `ohrm_attendance_record` `ohrm_attendance_record`
      INNER JOIN `ohrm_user` `ohrm_user` ON
     (`ohrm_user`.`emp_number` = `ohrm_attendance_record`.`employee_id`)
WHERE ( `ohrm_user`.`user_name` LIKE 'mel%' )
AND ( `ohrm_attendance_record`.`punch_in_user_time` > '2015-06-29 00:00:00' )
AND ( `ohrm_attendance_record`.`punch_out_user_time` < '2015-07-01 00:00:00' )
GROUP BY `ohrm_user`.`emp_number`,
         --REMOVE -`ohrm_attendance_record`.`punch_in_user_time`,
         --REMOVE -`ohrm_attendance_record`.`punch_out_user_time`,
         `ohrm_user`.`user_name`
ORDER BY `ohrm_user`.`user_name`,
         `ohrm_attendance_record`.`punch_in_user_time`


Recover SA Password - PSTools - Not working

Here is a technet article that has the detailed steps.

In a nutshell you need to start SQL server in single user mode and connect using sqlcmd to add your account. This should get you what you need as long as you have admin rights at the OS level.

One note is that if the sqlagent is configured to start up automatically you should disable that before you start SQL server in single user mode so the connection isn't taken by the sqlagent.


As you don't mention it, I suspect your application is not connecting with

MultiSubnetFailover=True

SqlClient Support for High Availability, Disaster Recovery


Query Amount of entries per column from multiple tables + provide table name

A UNION (or perhaps a UNION ALL, for reasons that I will note below) is a reasonable way to do this. There are a couple of ways to write the UNION. You did the first, which is to aggregate at each individual table level and then UNION the aggregated results. Is there a possibility that two different tables will have the same object in them? That is, something like this:

"Schema".table_1
OBJART
'Renoir'
'Picasso'

"Schema".table_2
OBJART
'Rodin'
'Picasso'

If this is the case, your query above would actually discard one of the sub-results because of how UNION scans the resultsets it is UNIONing for duplicates and discards them . In other words, this subquery

SELECT objart, COUNT(*) AS "Number of objects" FROM "SchemaA".table_1

Will produce this resultset given the data above:

objart   Number of objects
'Renoir'   1
'Picasso'  1

And this query:

SELECT objart, COUNT(*) AS "Number of objects" FROM "SchemaA".table_2

Will produce this resultset:

objart   Number of objects
'Rodin'   1
'Picasso'  1

However, because UNION scans input resultsets and discards duplicates, you will lose one of the 'Picasso' rows and the overarching UNION query will result in this:

objart   Number of objects
'Renoir'   1
'Picasso'  1
'Rodin'    1

I think what you probably want is this:

objart   Number of objects
'Renoir'   1
'Picasso'  2
'Rodin'    1

Also, a sidenote -- can objart ever be NULL? If so, and if you don't want to count NULL rows, I wouldn't use COUNT(*) because that counts all of the rows in the table. COUNT(objart) would work better as it will only count non-NULL values. Yes, it might work either way but to be safe I always like to be very explicit that I am telling the DB to count the number of non-NULL values for a particular column and not the number of rows in a table overall. e.g.:

SELECT objart, COUNT(objart) AS "Number of objects" FROM "SchemaA".table_1

So, there is another way that might be a bit easier. UNION ALL all of the data in the underlying tables and then compute the aggregate on top of them. Here is the query for that:

WITH subquery_art AS
(SELECT objart FROM "SchemaA".table_1
UNION ALL
SELECT objart FROM "SchemaA".table_2
UNION ALL
SELECT objart FROM "SchemaA".table_3
UNION ALL
SELECT objart FROM "SchemaA".table_4
UNION ALL
SELECT objart FROM "SchemaA".table_5)
SELECT objart, COUNT(objart) AS "Number_of_objects"
FROM subquery_art
GROUP BY objart
ORDER BY objart;

If you want to add the table name that you're sourcing data from, here's how to do it:

WITH subquery_art AS
(SELECT 'table1' as table_name, objart FROM "SchemaA".table_1
UNION ALL
SELECT 'table2', objart FROM "SchemaA".table_2
UNION ALL
SELECT 'table3', objart FROM "SchemaA".table_3
UNION ALL
SELECT 'table4', objart FROM "SchemaA".table_4
UNION ALL
SELECT 'table5', objart FROM "SchemaA".table_5)
SELECT table_name, objart, COUNT(objart) AS "Number_of_objects"
FROM subquery_art
GROUP BY table_name, objart
ORDER BY table_name, objart;

EDIT: Fixed some misspellings of 'Renoir'


COUNT() ignores NULL values, and you can use DISTINCT in COUNT() as well.

Try:

  , COUNT(DISTINCT CASE WHEN eep.EepGender='M' THEN eep.eepEEID ELSE NULL END)

(assuming that eep.eepEEID is the unique ID for an employee).

You're doing a unique count of the employee's ID (for males) and NULL (for not males); the NULLs are ignored, and the DISTINCT means that each unique ID will only be counted once.


Is data encrypted by ENCRYPTBYPASSPHRASE() protected by the service master key?

No, this is not true, and there is an easy proof. On one server, create a database, and store some data using ENCRYPTBYPASSPHRASE():

CREATE DATABASE blat;
GO
USE blat;
GO
CREATE TABLE dbo.mort(floob INT, splunge VARBINARY(64));
GO
INSERT dbo.mort VALUES
  (1, ENCRYPTBYPASSPHRASE(N'kerplunk', N'secret')),
  (2, ENCRYPTBYPASSPHRASE(N'kerplunk', N'hidden'));

Now, backup the database:

BACKUP DATABASE blat TO DISK = 'C:\wherever\blat.bak' WITH INIT;

Now, on a different server in a different domain or wherever you feel comfortable believing that there are no hidden attachments to some service master key, restore the database:

RESTORE DATABASE blat FROM DISK = 'C:\wherever\blat.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'blat' TO 'C:\somepath\blat.mdf',
  MOVE 'blat' TO 'C:\somepath\blat.ldf';

Finally, retrieve the data from the restored copy, and observe that you can still decrypt it:

USE blat;
GO
SELECT floob, prying_eyes = CONVERT(NVARCHAR(4000),
  DECRYPTBYPASSPHRASE(N'kerplunk', splunge))
FROM dbo.mort;

Results should be:

floob   prying_eyes
-----   -----------
1       secret
2       hidden

So, this means that if a user gets your data and knows the pass phrase (e.g. if you've stored it somewhere in the same database), they can decrypt all of the data without ever worrying about keys or anything else. You might infer from this that ENCRYPTBYPASSPHRASE() is not a very safe way to store passwords, unless you never store the pass phrase a user entered, or unless you use other tools and methods on top of this (scope creep).

As a side note, I will confess that I have absolutely no idea how ENCRYPTBYPASSPHRASE() actually works internally, other than the fact that it uses Triple DES (3DES) encryption. It is non-deterministic - so it might use something like NEWID() or RAND() internally - your guess is as good as mine. I do know that if you run the following, you will get a different binary value each time, and if you take any of the individual output values and ran DECRYPTBYPASSPHRASE() against that value, you'd still end up back with the original:

SELECT ENCRYPTBYPASSPHRASE(N'banana', N'turkey');
GO 5

In fact, you can try this on your own system. Here is one of the binary values I generated, and if you reverse the process, you should get turkey too:

SELECT CONVERT(NVARCHAR(4000), DECRYPTBYPASSPHRASE(N'banana',
  0x010000007854E155CEE338D5E34808BA95367D506B97C63FB5114DD4CE687FE457C1B5D5))


Can the view "v$database_block_corruption" get refresh after the corrupted block get fixed?

It is not neccessary. You can leave the corruption in place if you have correctly marked the affected blocks as corrupt. But you must accept that tools such as RMAN will alert you about these blocks every time you perform an action on them. e.g. whenever you take a backup of this datafile.


Automatic Reboot after installing SQL Server 2012?

You can safely click OK and it won't reboot it.  The instance will even be up, online and accessible without the reboot.

It's always good practice to reboot after a fresh installation or patches as when you try to perform another patch or installation down the road, the installer will more than likely fail of the 'Restart Required' portion of the checks.


SSAS - how to script the General settings? where are them stored? how can they be queried?

The settings that you are looking for are stored in msmdsrv.ini

\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Config\msmdsrv.ini

or <your Analysis service custom path>\OLAP\Config\msmdsrv.ini

Note: Above path applies for Default installation.

Its a good practice to create a backup of that file, if you are changing any config value.


How to delete duplicate records in my database?

I'm assuming that you have no foreign key constraints on the phone column and you have a Primary Key defined:

DELETE FROM foo_schema.bar_table
WHERE primary_key_column NOT IN (SELECT MAX(primary_key_column)
                                        FROM foo_schema.bar_table
                                        GROUP BY phone)

This should group all records by the column phone, and drop all the duplicates but the one with the max PK. Hope that helps.

Please try this on a dummy table before attempting this on your actual table.


Usage of aggregate function with when self-joining a table

Assuming a table like:

CREATE TABLE player
    ( player_id int not null primary key
    , weight int not null
    , nationality char(2) not null -- just any additional attribute
    );

insert into player (player_id, weight, nationality)
values (1,70,'SE'), (2,75,'RU'), (3,60,'US');

The following query appears to work in sqllite (as far as I can tell this is valid SQL99 since p1.weight, p1.nationality is functionally dependent of p1.player_id. Most vendors still implement the more restrictive SQL92 rule for group by). Also note that I prefer ansi join over "," joins, I find them easier to read.

select p1.player_id, p1.weight, p1.nationality
from player p1
cross join player p2
group by p1.player_id
having p1.weight > avg(p2.weight);

having is just syntactic sugar for the chattier:

select player_id, weight, nationality
from (
    select p1.*, avg(p2.weight) as avg_weight
    from player p1
    cross join player p2
    group by p1.player_id
) as t
where weight > avg_weight;

Another variant is to join against a derived table, pretty much the same query as your original:

select p1.*
from player p1
cross join (
  select avg(weight) as avg_weight
  from player
) as p2
where p1.weight > p2.avg_weight;

On the other hand you can use window functions to achieve the same thing:

select player_id, weight, nationality
from (
    select p.*
         , avg(weight) OVER () as avg_weight
    from player p
)
where weight > avg_weight;

You can find an example at: db-fiddle


DB Restart Taking too long, site is down

Without seeing any additional information, here is my best guess why MySQL is trying to shutdown for a long time: I suspect mysqld no longer has a socket file.

About 1.5 years ago, I answered mysql restart issue after move database. I learned that mysqld depends on the presence of a socket file. If there is no socket, mysqld just draws lots of dots on the screen and then gives up.

In that old post, I wrote three instructions

kill mysqld_safe
kill mysqld
start mysqld

Please go do this now ...

I have discussed MySQL behavior with the socket file (or lack of it) 4 years ago (See my older post mysql restart won't kill child processes on CentOS)


One time database shrink catch-22 solution?

Simple recovery model doesn't "turn off the log" - so generally, unless you're going to break any of these activities into very small chunks, just pre-size the log large enough to deal with the largest rebuilds, and don't bother messing with the recovery model.

Also, and again generally, unless your data will never grow again, freeing up the space temporarily is of little benefit (given that I don't know what you mean by "reasonable"). You're going to need to leave free space on the drive, in case the database grows again, right? Why shrink it only to have it grow again? Are you going to provide short-term rental on that space until the database grows? Are you just trying to avoid free space % alerts?

Suggestion: give these posts a good read...

Why Does the Transaction Log Keep Growing or Run Out of Space?
Sql Server - Best Practices for Growing Database Files
SHRINKFILE best practices and experience
I Need to Shrink My Database - I just freed a lot of space
Reducing the size of a database
When is it OK to shrink a Database?
Database running out of space


mysql 8 information_schema.processlist -> correct approach to cache it?

(I agree that Threads_running would be better.)

As for the Processlist, simply have a single program doing:

Collect processlist
parse and analyze it (there is probably no reason to have each worker do the same processing here)
store the minimal amount of info in some table dedicated to this purpose.  This is probably a single UPDATE statement.  (Not a much heavier TRUNCATE.)
Repeat forever -- Do not repeat every second; do it continually or with a slight delay in the loop.

The reason for not using a cron job:  When the server is too busy, you could get a new copy of the job starting before the first finishes.  It is much better to simply avoid that situation rather than use transactions (or whatever) to deal with it.

MEMORY should be fine, but might not be as fast as InnoDB.  The simple UPDATE is minimally invasive, so the storage engine should not matter much.

No transactions; minimal overhead.

Probably step 2 boils down to the equivalent of SHOW GLOBAL STATUS LIKE 'Threads_running';

If it takes more effort to measure how "busy" the server is than to simple forge ahead, you are wasting your time.

(A side note:  REPLACE is worse than INSERT in this case -- REPLACE does a DELETE, but you have already TRUNCATEd!?)


Return total number of rows and selected (aggregated) data

As I understand the question, you don't need a window function. Aggregate functions do the job:

count() in the lowest level (-> row_ct).
sum() the resulting row_ct in the next level (-> total_row_ct).

SELECT row_to_json(selected_records)::text AS data
FROM  (
   SELECT array_to_json(array_agg(row_to_json(records))) AS data
        , sum(row_ct) AS total_row_ct
   FROM (
      SELECT landing_path_id
           , sum(entrances) AS entrances
           , count(*) AS row_ct
      FROM   report_la
      WHERE  profile_id = 3777614
      GROUP  BY landing_path_id
      LIMIT  10
      ) records
   ) selected_records;
I also included landing_path_id so the resulting data makes sense.
SQL Fiddle.
Window function?
A window function (count(*) over ()) does not seem to be what you want, since you don't have unaggregated rows.
You could add to the inner subquery:
count(*) OVER ()

.. to get the count of distinct landing_path_id, which is one other possible number that might be of interest. But that doesn't seem to be what you meant by "the total number of rows from that records select".
Or you could add  to the inner subquery:
sum(count(*)) OVER ()

.. to get the total count with every landing_path_id redundantly, but that would seem pointless. Just mentioning that to demonstrate it's possible to run a window function over the result of an aggregate function in a single pass. Details for that:

How will the order of operations in this query affect my results?
How do I get the aggregate of a window function in Postgres?
Need separate columns that calculate percentage

Updated question
Your result, just without total_count in the records subquery. Now accounting for the LIMIT in the inner SELECT. Even though a maximum of 10 distinct landing_path_id is selected, all qualifying landing_path_id are counted.
To get both in one scan and reuse count and sum separately I introduce a CTE:
WITH cte AS (
  SELECT sum(entrances) AS entrances
       , count(*) over () AS total_count
  FROM   report_la
  WHERE  profile_id = 3777614
  GROUP  BY landing_path_id
  LIMIT  10
  )
SELECT row_to_json(selected_records)::text AS data
FROM  (
   SELECT (SELECT total_count FROM cte LIMIT 1) AS total_count
        , array_to_json(array_agg(row_to_json(records))) AS data
   FROM  (SELECT entrances FROM cte) records
   ) selected_records;
If you don't care about the attribute name, you can have that cheaper with a subquery:
SELECT row_to_json(selected_records)::text AS data
FROM  (
   SELECT min(total_count) AS total_count
        , array_to_json(array_agg(row_to_json(ROW(entrances)))) AS data
   FROM (
      SELECT sum(entrances) AS entrances
           , count(*) over () AS total_count  -- shouldn't show up in result
      FROM   report_la
      WHERE  profile_id = 3777614
      GROUP  BY landing_path_id
      LIMIT  1
      ) records
   ) selected_records;
You get the default attribute name f1 instead of entrances, since the ROW expression does not preserve the column name.
If you need a certain attribute name, you could cast the row to a registered type. (Ab-)using a TEMP TABLE to register my row type for the session:
CREATE TEMP TABLE rec1 (entrances bigint);

...
        , array_to_json(array_agg(row_to_json(ROW(entrances)::rec1))) AS data
...
This would be a bit faster than the CTE. Or, more verbose but without cast:
...
        , array_to_json(array_agg(row_to_json(
                   (SELECT x FROM (SELECT records.entrances) x)))) AS data
...
Detailed explanation in this related answer:

Select columns inside json_agg

SQL Fiddle.


Why does sql server prefer the nonclustered index over the clustered index?

If SQL Server has two indexes to choose from, both of which satisfy ("cover") the query and provide the best possible path to locating and/or sorting the rows, you should consider it to be a coin flip. It's not, though... I believe there was some research done here (maybe by me, here and here)  that showed it picked the most recent one created or first one alphabetically or something that is otherwise arbitrary.

However, if the coin flip as we'll call it involves the choice between a non-clustered index and a clustered index, and again both indexes properly satisfy the query, SQL Server will always lean toward the non-clustered. Why? Because it's guaranteed to be no wider than the clustered index. The edge case where it is exactly the same width as the clustered index is not a consideration.

You should look at the costs involved with each execution plan, and confirm that the costs SQL Server estimates for the non-clustered index are <= those for the clustered index. If you can show a counter-example, where the non-clustered index is chosen even though its estimated costs are higher than the clustered, please do.


How to only update part of a column based on conditions?

I don't fully understand the question, but I believe what you're looking for is a MERGE statement.  A rough example from memory:

MERGE INTO table1 t1
USING table2 t2
ON (t1.id=t2.id)
WHEN MATCHED THEN UPDATE SET
    t1.color = t2.color
WHEN NOT MATCHED THEN INSERT
    (t1.ID, t1.color) VALUES (t2.ID, t2.color);

If you don't want to override the colors, you can simply remove the 'WHEN MATCHED' part.


The SUM() OVER does not fail, it does exactly what it is supposed to do. When you have an aggregate like SUM() with OVER (... ORDER BY something), there is a default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

This means that the sum goes over all rows that belong to the same partition (same Model in this case) and have the Counts value greater or equal to the Counts value of the current row. In case of ties, you'll see this behaviour.

To resolve ties, you have to include in the ORDER BY of the OVER part, one or more columns that can, alone or together with (Model, Counts), uniquely identify rows. So the primary key or a unique column is good. If there is a unique constraint on (Model, PartID), you could use PartID. The expression would become:

SUM(Counts) OVER (PARTITION BY Model ORDER BY Counts DESC, PartID ASC)
  AS Cumulative_Counts


Advisability of using STATISTICS_NORECOMPUTE

It's really a situational thing that you want to look at per-table or per-index, and you really need to find out what's in production before taking any action. When in doubt, use what's in production in the other environments, too, even if it means using a bunch of crazy settings. You just can't get a good feel for how production will behave if things are different in test or dev.

Anyway, the general recommendation to leave auto-update stats turned on (STATISTICS_NORECOMPUTE = OFF, which is the default) is for safety reasons, because if this is turned off and nothing is manually updating the stats, the result could be really horrendous execution plans that never change after they're first created (and don't get invalidated for other reasons later on).

You said auto-update stats is turned off for most indexes (I think I originally misread that as all, not most). For the indexes with auto-update stats still enabled, does this setting make sense given the activity on those tables? I would expect that these are higher-activity tables. It's possible a lot of work went into figuring that out, and it may be worth keeping (or strongly considering) those settings. At the very least, make a note of which stats these are, because that information could come in handy down the road.

Thinking about it more, I will say that the current strategy does make sense. Is it better than leaving auto-update stats on for everything? It seems someone thought so, to the point that it was worth the ease-of-management tradeoff of having an associated SQL Agent job.

If the idea was to have fresh stats available without blocking queries (like this), you could consider turning auto-update back on for everything, and then also turn on AUTO_UPDATE_STATISTICS_ASYNC as well. Then probably change the job schedule to run once/week instead of daily, as you still do want stats updated WITH FULLSCAN periodically.

I might just leave it, though, as you probably have bigger fish to fry if the indexes themselves are different between environments, and the stats rebuilds aren't too painful. What's there now does make sense; you just need to make things consistent across environments. It's probably marginally better than the simpler settings I suggested, at the expense of more work being done. But find out what's in production, tend towards using that, and move on to more important things; revisit this when you're at the point of needing to more finely tune performance -- the best stats in the world won't save a query that's missing a critical index.


select two tables and print the columns side by side with matching key in hive

SELECT 'table_1' AS table_1
    ,'table_2' AS table_2
    ,'id_1' AS table_1_pk
    ,'id_2' AS table_2_pk
    ,t1.id_1 table_1_pk_value
    ,t2.id_2 AS table_2_pk_value
    ,'name_1' AS table_1_col
    ,'name_2' AS table_2_col
    ,t1.name_1 AS table_1_value
    ,t2.name_2 AS table_2_value
    ,CASE
        WHEN t1.name_1 = t2.name_2
            THEN '0'
        WHEN t1.name_1 <> t2.name_2
            THEN '1'
        END AS flag
FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.id_1 = t2.id_2;


I know of no hacks to get SQL Server 2000 to install on an existing Windows 10 installation. There are probably forces against you from both within the setup program (which looks for certain things on the host system) and within the operating system (which prevents many flavors of older software from getting installed, mostly for security reasons but also compatibility, stability, etc). I have tried in the past and even with compatibility override options I came across the same failures every time.

A couple of ideas:

Use a virtual machine. This really is the simplest and most obvious solution. Most (if not all) editions of Windows 10 now support Hyper-V natively; if not, there is always VMWare Workstation (not free) and Oracle VirtualBox (free).
Install 2000 then upgrade. Not really practical, especially if you want to use the Windows 10 installation you already have, but if you were doing this from scratch, you could install XP/Vista/Win7, all of which should allow you to install 2000, and then upgrade. Take care, though, because some methods of upgrading to Windows 10 will relegate all of your programs and services to Windows.old, and they won't run any more.

In either case, firewall the crap out of that thing, because the most recent security patches for 2000 are hard to find, and the ones that I found wouldn't install anyway...


Enforce Data integrity on an Email column in a database

This is way too big a task if you wanted to do it properly. If you are able to use SQL CLR though this is how we do it at our company:

using System;
using System.Net.Mail;
using Microsoft.SqlServer.Server;

namespace Functions
{
    public static class Utilities
    {
        [SqlFunction]
        public static bool IsValidEmail(string email)
        {
            if (string.IsNullOrEmpy(email))
                return false;

            bool isValid = false;
            try
            {
                // use the validation provided by the System.Net.Mail.MailAddress class
                var mailAddress = new MailAdress(email);
                isValid = true;
            }
            catch(FormatException)
            {
                isValid = false;
            }
            return isValid;
        }
    }
}

And then assuming you deploy it as dbo.fn_IsValidEmail you can use it in your check constraint like you would any other scalar function like so:

create table dbo.tbl
(
    id int identity not null,
    email nvarchar(256) not null,
    constraint ck_tbl_isValidEmail check (dbo.fn_IsValidEmail (email))
);

If your column allows NULL then you will need to change the first part of the function to this instead:

if (email == null)
    return true;
if (email == string.Empty)
    return false;

Documentation:

System.Net.Mail.MailAddress

Microsoft.SqlServer.Server.SqlFunctionAttribute

CLR Functions


show variables - Unknown column 'value' in 'where clause'

SHOW does not have that syntax.  Simply do

SHOW VARIABLES LIKE 'character_set_database';

and don't include the 'value'.


Does MariaDB Support Materialized views?

Not natively, no, but see Flexviews: https://mariadb.com/kb/en/mariadb/documentation/clients-and-utilities/flexviews/


Sounds like a bug. Try scripting from the GUI directly and see if your syntax is wrong somewhere.
Since the problem still occurs when running the script by itself, it doesn't have anything to do with the fact that it is running from a job. My guess is that the EMC application isn't properly reporting back to the dbo.emc_run_backup stored procedure that is has finished.
If that's the case, I'll bet scripting to CLI (ddbmsqlsv.exe) instead of T-SQL might behave differently. (You would need to change the job step to CmdExec instead of T-SQL.)
This may be a version problem, from what I can tell DDBoost didn't officially add support for SQL 2016 until version 3.5:

Changes in the Microsoft application agent 3.5
The following list provides the changes in the Microsoft application agent 3.5:

Added support for SQL Server 2016

Can't find a public link, but this is from the EMC® Data Domain® Boost for Enterprise Applications and EMC ProtectPoint™ Microsoft Application Agent Release Notes for Release 3.5.x.x (EMC document #81497).
Even if it worked before, a recent update of SQL 2016 may have broken it.
So my recommendation would be to upgrade to 3.5.0.1 or 4.0.
If you are unable to do that, all I can recommend is opening a support case with EMC.


What permission should be enabled to create user?

Requires ALTER ANY LOGIN or ALTER LOGIN permission on the server and ALTER ANY USER permission on the database.


If the roles that users can play are quite distinct, then yes you're on the right track.  Where they have fields in common, then put them in the shared Accounts table.  If that leaves just a few fields in the various role tables, you should probably ditch them and just have a single Accounts table with some spare columns.

The trade-off is greater complexity with more tables but (potentially) better performance with a few narrow table than a single wide and long table.  My normal inclination would be to start with a simpler model and then optimize for performance only if necessary, but given the pain in restructuring a database, going with the more flexible model you've described from the beginning is probably worthwhile.


How to improve the liveness of a system when having a long-running transaction blocking multiple reading transactions

SQL Server offers Read Committed Snapshot Isolation and readers don't blocker writers and vice versa. As Mustaccio said though, the real culprit or problem here is that an update really shouldn't take hours. When done properly, with good table design that includes proper normalization, changing date should take a matter of seconds.


Pivoting some row data into columns (SQL Server 2008 R2)

Ok, here's my two pence:

SELECT header.Col1,
       MAX((CASE WHEN details.ordinal=1 THEN details.Col2 END)) AS row1,
       MAX((CASE WHEN details.ordinal=2 THEN details.Col2 END)) AS row2,
    -- ... and so on..
       MAX((CASE WHEN details.ordinal=99 THEN details.Col2 END)) AS row99

FROM (
      --- For each Col1, enumerate all the rows and return Col2 as well:
      SELECT Col1, Col2,
             ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Dumb_ID) AS ordinal
      FROM someTable
      ) AS details

--- join this to the header:
INNER JOIN someTable AS header ON details.Col1=header.Dumb_ID

--- One row per header:
GROUP BY header.Col1;

Admittedly, not the prettiest code I've ever written. How it works:

The "header" in this case is the row where Dumb_ID='1A!'.
"details" contains one row for each record that can be joined to the header ON header.Dumb_ID=details.Col1, in this example rows where Col1='1A!'.
The "details" subquery is given an ordinal, a ROW_NUMBER(), that enumerates each row (partitioned by Col1).
"header" and "details" are joined and everything is output by aggregating the result, so we get a single record for each "header"
Each "detail" is assigned to its own column using a CASE and the ordinal number. We need to use MAX() (or any other aggregate function, really) here so it'll work with the aggregate.

I hope I've understood your question correctly. Also, note that this is ad-hoc coded, without testing. Let me know how it works out.


The only way I see with such a design is to self join the table several times. This means a JOIN for each column required in the output.

Notes:

The sample used is available toward the end and in this SQL Fiddle.
You will have to figure out what the value of FormElement means when writing the query
An age value has been added to it (bigger sample).
Expect bad performances with such a design on a large data set
An index on FormElement and Entry including Value or filtered indexes on FormElement may help.
It uses 1 subquery for each object because it helps see the separation between self joins.
I suppose there is always a row for each object from the form (hence the INNER JOIN)
I replace isMale by a CASE statement and Male or Female. It may help you.
Views may help writing shorter queries if some combination are often used.

People between 30 and 40

For example, if you want people between 30 and 40:

SELECT name.Entry
    , Name = name.[Value]
    , Sex = CASE WHEN sex.[Value] = 'True' THEN 'Male' ELSE 'Female' END
    , Age = age.[Value]
FROM (
    SELECT [Entry], [Value] FROM data WHERE [FormElement] = 'Textbox1'
) name
INNER JOIN (
    SELECT [Entry], [Value] FROM data WHERE [FormElement] = 'Checkbox1'
) sex
    ON name.Entry = sex.Entry
INNER JOIN (
    SELECT [Entry], [Value] FROM data WHERE [FormElement] = 'Textbox2'
) age
    ON name.Entry = age.Entry
WHERE age.[Value] > 30 AND age.[Value] < 40
;

Output:

Entry   Name        Sex     Age
1       Joe Smith   Male    32
3       Lucy Smith  Female  35

Name starting with a J

For people with a name with a J:

SELECT name.Entry
    , Name = name.[Value]
    , Sex = CASE WHEN sex.[Value] = 'True' THEN 'Male' ELSE 'Female' END
    , Age = age.[Value]
FROM (
    SELECT [Entry], [Value] FROM data WHERE [FormElement] = 'Textbox1'
) name
INNER JOIN (
    SELECT [Entry], [Value] FROM data WHERE [FormElement] = 'Checkbox1'
) sex
    ON name.Entry = sex.Entry
INNER JOIN (
    SELECT [Entry], [Value] FROM data WHERE [FormElement] = 'Textbox2'
) age
    ON name.Entry = age.Entry
WHERE name.[Value] LIKE 'j%'
;

Output:

Entry   Name            Sex     Age
1       Joe Smith       Male    32
2       Jane Johnson    Female  25

Sample data used

CREATE TABLE data
    ([Entry] int, [FormElement] varchar(9), [Value] varchar(12))
;

INSERT INTO data
    ([Entry], [FormElement], [Value])
VALUES
    (1, 'Checkbox1', 'True'),
    (1, 'Textbox1', 'Joe Smith'),
    (1, 'Textbox2', '32'),
    (2, 'Checkbox1', 'False'),
    (2, 'Textbox1', 'Jane Johnson'),
    (2, 'Textbox2', '25'),
    (3, 'Checkbox1', 'False'),
    (3, 'Textbox1', 'Lucy Smith'),
    (3, 'Textbox2', '35')
;


Using composite indexes with date ranges

It's hard to tell if this will hurt or improve performance but adding a composite index on start_date and end_date shouldn't worsen your query, the index will either be used or not used. You didn't mention your RDBMS but I don't think there's a big chance your engine's optimizer will pick a worse plan (but there is always a possibility I guess).

That being said, there might a slight impact on the plan generation time as your optimizer has an extra index to consider, and unused indexes could hurt overall performance since they need to be maintained when updating/inserting.

Whether or not the index will help your query is hard to tell, it depends on the selectivity of your query and cardinality statistics. The optimizer of your RDBMS should pick the fastest way to get your data, and even if an index exists on the columns your query selects on a full scan might still be the better option, especially since you are selecting all fields.

Why is selectivity important?

When an index is used, the index points to the actual record and the record might need to be fetched using the pointer. Most RDBMS work that way.

See for example the Oracle documentation

  In general, index access paths should be used for statements that
  retrieve a small subset of table rows, while full scans are more
  efficient when accessing a large portion of the table

or the SQL Server documentation

  In many cases, the optimizer forces a table scan for queries with
  result sets of approximately 5 percent, although the table scan
  becomes more efficient than index access at selectivities of 8 to 10
  percent.

Why is select * important

If you fetch all fields from a table your database engine will have to fetch the actual record using the pointer stored in the index and you lose the option of using included columns (if your RDBMS supports them) or covering indexes.

See for example this article for SQL server (but the principle goes for a lot of vendors): Using Covering Indexes to Improve Query Performance

  However, under some circumstances, the overhead associated with
  nonclustered indexes may be deemed too great by the query optimizer
  and SQL Server will resort to a table scan to resolve the query.

and

  The observed improvement is due to the fact that the nonclustered index
  contained all of the required information to resolve the query. No Key
  Lookups were required.


migrating tables from Postgres to SQL Server 2008

We found a solution for this that ended up being super-fast: using a wizard in our ETL tool Pentaho Data Integration (Kettle).  Open a new job then Tools -> Wizard -> Copy Tables.  I wouldn't be surprised if other ETL tools had similar wizards.


Really? DBMS_JOB has a procedure called CHANGE.

declare
  jobno number;
begin
  dbms_job.submit(jobno, 'begin null; end;');
end;
/
commit;

select job, what from user_jobs;

       JOB WHAT
---------- ------------------------------
         1 begin null; end;

begin
  dbms_job.change(1, 'begin null; commit; end;', null, null);
end;
/
commit;

select job, what from user_jobs;

       JOB WHAT
---------- ------------------------------
         1 begin null; commit; end;


Increase TempDB Files, Shrink VLF's with FCI

There should be no negative impact on performance esp when you have encountered PFS page contention. Just make sure you dont blindly over create files.

This script from Tigertoolbox (Microsoft Tiger Team) will help.

Depending on the version of sql server you are using, you might need or might not need below trace flag :

Trace Flag 1118 – Full Extents Only (tells SQL Server to NOT use “mixed extents” and use “full extents”)

I have not listed Trace Flag 1117 (Grow all files in a filegroup equally) as it affects instance wide. Its better to pre-allocate tempdb files and make sure that they are ALL sized equal.

Finally, make sure that you have set autogrowth to a sensible value.

Few good links to refer :

Troubleshooting: Tempdb Contention
sp_whoisactive: Analyzing Tempdb Contention


Query not returning results if selecting by indexed columns (Mysql 5.6)

I wonder what would happen if you ran OPTIMIZE TABLE product; on your MySQL server. OPTIMIZE TABLE will “rebuild” the physical storage of your table, including the indexes.

https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html


In order for AC->B to be a partial key dependency, it has to satisfy these conditions.

AC must be a candidate key. (Not necessarily the primary key.)
One of these functional dependencies must hold.

A->B, or
C->B

So the first question I'd ask myself is, "Is AC a candidate key of F?"


When should the IGNORE_DUP_KEY option be used on an index?

It's definitely an atypical setting to enable for an index. I don't think you're going to find many people who jump up and talk about using it.
Of course, there are a couple helpful posts on the subject by Paul White:

IGNORE_DUP_KEY slower on clustered indexes
A creative use of IGNORE_DUP_KEY

If your concern is around upsert patterns (or something similar), this article by Michael Swart is really informative:

SQL Server UPSERT Patterns and Antipatterns


How do I group results in batches of consecutive rows with the same 'module'

The question is which of the pdates for a module do you want to use for ordering purposes?

If you are happy to use the largest, you could use:

select module from logging order by max(pdate);

--- EDIT

I think I get it now - you probably want something like this:

select module
from( select module,
             sum(module_step) over (order by pdate rows unbounded preceding)
                 as batch_number,
             pdate
      from( select module,
                   case lag(module) over (order by pdate) when module then 0
                        else 1 end as module_step,
                   pdate from logging) )
group by module, batch_number
order by batch_number;


Is there any point to SQL partitioning if I index properly and avoid full-table scans?

Partitioning has a few other benefits as SQL Server almost sees it as a brand new table.  Thus it will use more threads and run concurrent scans on the GAM/SGAM/etc. pages to find your data.  It'll also lock only that partition if it needs to escalate which is a nice to have, assuming you are partition aligned.  So if you're running a query that hits multiple partitions and you have partition elimination working, it will be able to do the metadata lookups and scans on all those in parallel.

It gives you the benefit of having a particular index for the top (or first) partition when it is queried so heavily but drop the index or add other ones for historic data.  This way you can maximize your index usage that way as well.

You can even compress data per partition if you'd like, although you're using newer versions of SQL Server you might get some good compression per partition or just table.

There could be more but those are the main ones I can think of.


SQL Server Service Accounts Max Character Length

Within SQL Server, the datatype for the name column in sys.database_principles is sysname, which is essentially nvarchar(128). However, the sAMAccountName attribute in Active Directory specifies the login name must be 20 or fewer characters.

A similar question was posted within the forums on SQLServerCentral:

  https://www.sqlservercentral.com/forums/topic/max-length-of-ad-names-for-security


SELECT to only see Offsetting Transactions in table?

I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos.

There is no Buy in your table. You only have Sell and Purchase.

Below is a good starting point for you to get (Purchase,Sell) combo :

;with temptrades
as
(select *, row_number() over (partition  by PortfolioName, [transaction]
order by PortfolioName, [transaction]) as rowNumber
    from tmpTrades
)
select  portcode,cusip, PortfolioName,  [transaction]
 from temptrades
where  rowNumber = 1
order by portcode

SQLFiddle here.


Can't connect to MySQL from remote computer

I believe this can be resolved by setting the "bind-address " to 0.0.0.0 or the specific IP address of your win-10 computer.
this is done by accessing the MySQL config file and physically typing in the desired ip.
the file path is usually:
/etc/mysql/my.cnf

0.0.0.0 will allow access from all networks.


Problems splitting and aggregating data in SQL

As I mentioned in comments on your original question, one option is to leave the original records alone, and have a separate table that you use to tie "buy"s to matching "sell"s.

CREATE TABLE reconcile
     ( id INT AUTO_INCREMENT
      ,buy_order_id INT
      ,sell_order_id INT
      ,amount INT);

Then, create a stored procedure something like this:

CREATE PROCEDURE reconcile_buy_sell()
BEGIN
  DECLARE c_buy CURSOR FOR
  SELECT t.OrderID, (t.`Amount` - rec.rec_amount) as `Amount`
    FROM MyTable t
           INNER JOIN (SELECT buy_order_id, SUM(`amount`) as rec_amount
                         FROM reconcile
                        GROUP BY buy_order_id
                      ) rec ON (t.OrderId = rec.buy_order_id)
   WHERE t.OrderType = 'Buy'
     AND t.`Amount` > rec.rec_amount
   ORDER BY t.`Time`
  ;

  DECLARE c_sell CURSOR FOR
  SELECT t.OrderID, (t.`Amount` - rec.rec_amount) as `Amount`
    FROM MyTable t
           INNER JOIN (SELECT sell_order_id, SUM(`amount`) as rec_amount
                         FROM reconcile
                        GROUP BY sell_order_id
                      ) rec ON (t.OrderId = rec.sell_order_id)
   WHERE t.OrderType = 'Sell'
     AND t.`Amount` > rec.rec_amount
   ORDER BY t.`Time`
  ;

  DECLARE buy_id, sell_id, buy_amount, sell_amount INT;
  DECLARE done INT DEFAULT FALSE;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN c_buy;
  OPEN c_sell;

  FETCH c_buy INTO buy_id, buy_amount;
  FETCH c_sell INTO sell_id, sell_amount;

  WHILE NOT done
  DO
    IF buy_amount < sell_amount THEN
      INSERT INTO reconcile (buy_order_id, sell_order_id, `amount`)
      VALUES (buy_id, sell_id, buy_amount);
      SET sell_amount = sell_amount - buy_amount;
      SET buy_amount = 0;
    ELSE
      INSERT INTO reconcile (buy_order_id, sell_order_id, `amount`)
      VALUES (buy_id, sell_id, sell_amount);
      SET buy_amount = buy_amount - sell_amount;
      SET sell_amount = 0;
    END IF;

    IF buy_amount = 0 THEN
      FETCH c_buy INTO buy_id, buy_amount;
    END IF;

    IF sell_amount = 0 THEN
      FETCH c_sell INTO sell_id, sell_amount;
    END IF;
  END WHILE;

  CLOSE c_buy;
  CLOSE c_sell;
END;

[Note: code is untested, and I'm not a MySQL expert - treat this as pseudocode, and feel free to correct any errors found.]

The two cursors will iterate through the "buy"s and "sell"s in the order in which they were entered. They only return orders where the full amount of the "buy" or "sell" has not already been reconciled in the reconcile table, and only return the unreconciled portion of the amount for those rows.

In the WHILE loop, we add rows to reconcile. If the amount of the current "buy" is less that the amount of the current "sell", then the full (remaining) amount for the buy goes into thereconcile` row; otherwise (whether the "sell" amount is less that or equal to the "buy" amount), we can use the amount for the sell.

We reduce the remaining amount for the current "sell" (or "buy", whichever is more) by the remaining amount for the "buy" (or "sell") we just used up, and set the "buy" (or "sell") amount to 0. If the amounts are the same, they'll both be 0 now.

Then, for whichever amounts are zero, we pull in the next record from the cursor.

When either cursor is out of records (including the degenerate case, where one or the other cursor returned no records), we stop reconciling.

NOTE: For performance purposes, it may be worth adding a reconciled bit column to MyTable (or whatever you actually call the table your CSV data gets imported to), and marking rows where the full amount has been reconciled as reconciled = TRUE. This could speed up locating the rows that aren't reconciled.


What are the limits of Postgresql's large object facility?

These 3 points are correct. We might add

A large object cannot exceed 4TB for PostgreSQL 9.3 or newer, or 2GB for older versions. This is based on the release notes:

  E.11.3.5. Data Types

  Increase the maximum size of large objects from 2GB to 4TB (Nozomi Anzai, Yugo Nagata)

Also, the binary contents are sliced into tiny chunks of 2000 bytes in pg_largeobject. There is one row per chunk, so when importing large contents (large by today's standards), the number of rows in this table tend to grow quickly.
Although that does not imply a hard limit, users should be aware of that for performance reasons.


Synonyms will not cause the use of tempdb. There, however, may be a misunderstanding by your DBA as to the location of the database behind the synonym.

Queries across databases can use a 3 part name to reference a database object: database.schema.objectname.  Queries across linked servers require an additional part, the linked server name.  In my experience, because of this, almost all references to external servers use a synonym. It saves a bunch of typing (note: that is not the reason to use synonyms). However, it is a lot more common to see not use a synonym where referencing a local database.

I speculate this may have lead the DBA to assume that, because you are using a synonym, you are referencing an external server.

Results of queries against an external server do impact tempdb, as this is where it stores the data. So, if the DBA misunderstood the actual relation of the two database, being on the same server, they could have given you incorrect information.


Postgres COPY command gets Error: out of memory

DETAIL:  Cannot enlarge string buffer containing 1073741632 bytes by
  349 more bytes.
  CONTEXT:  COPY column_name line 13275136

According to the error message, the offending row at this line number in the file is too large to be imported. COPY uses an internal buffer per row that can only grow to 1GB. It's impossible to import or export larger rows with COPY in existing versions of PostgreSQL (up to and including version 12)