How to specify not null contraints for the fields of composite types in postgres

The correct syntax for a check constraint for a composite type would look like this:
create domain address_domain as address
check (
  (value).city is not null and
  (value).address_line is not null and
  (value).zip_code is not null
);

Let's check:
melkij=> create table test_address_domain (a address_domain);
CREATE TABLE
melkij=> insert into test_address_domain values (('foo', 'bar', 11));
INSERT 0 1
melkij=> insert into test_address_domain values (('foo', 'bar', null)); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"
melkij=> insert into test_address_domain values (('foo', null, 11)); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"
melkij=> insert into test_address_domain values ((null, 'bar', 11)); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"
melkij=> insert into test_address_domain values (null); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"

But pay attention to the last line. It seems that this is not what you want. Try allow explicitly:
melkij=*> create domain address_domain2 as address
check (
  value is null or (
  (value).city is not null and
  (value).address_line is not null and
  (value).zip_code is not null
));
CREATE DOMAIN
melkij=*> create table test_address_domain2 (a address_domain2);
CREATE TABLE
melkij=*> insert into test_address_domain2 values (null); -- works now
INSERT 0 1


Is it better to have one instance of a continuously running agent job or to fire off the job multiple times throughout the day?

There is a certain amount of overhead each time a job starts--reads and writes on msdb to track the state and schedule calculation, create a session, etc.  So from an efficiency standpoint, it would be better to run it in a loop.  This will also make it run more closely to every second, because when a job starts, it takes some time for the job to actually get to the point of executing code.  So if anything results in a random .25 second delay in the job actually getting to the point of executing the code, then you may have a significant gap between executions of the code.  I think (someone please correct me if I'm wrong) that it has to go through the entire login process, so a delay could be caused in the authentication process due to network issues.

The downside of running it in a loop is that you'll have to handle any errors in the code.  If the job runs every second, you can configure a failure notification on the job, and if there are problems you'll get an email.  If it runs in a loop, you'll need handle errors and notification in the code, and also monitor the job to ensure that it is running.


Cannot shutdown a shutdown connection MySQL Enterprise Monitor

There is really nothing you can do once a shutdown starts. Why ?

According to the MySQL Documentation on the Shutdown Process

  2 The server creates a shutdown thread if necessary.

  Depending on how shutdown was initiated, the server might create a
  thread to handle the shutdown process. If shutdown was requested by a
  client, a shutdown thread is created. If shutdown is the result of
  receiving a SIGTERM signal, the signal thread might handle shutdown
  itself, or it might create a separate thread to do so. If the server
  tries to create a shutdown thread and cannot (for example, if memory
  is exhausted), it issues a diagnostic message that appears in the
  error log:

Error: Can't create thread to kill server

  3 The server stops accepting new connections.

  To prevent new activity from being initiated during shutdown, the
  server stops accepting new client connections by closing the handlers
  for the network interfaces to which it normally listens for
  connections: the TCP/IP port, the Unix socket file, the Windows named
  pipe, and shared memory on Windows.

It sounds like you are past Stage 3 because you got Connection refused: connect

In addition, note what may have triggered Stage 2:

  If shutdown is the result of receiving a SIGTERM signal, the signal thread might handle shutdown itself, or it might create a separate thread to do so.

What could be that trigger ? Stage 1 says regarding Windows

  A server running as a service on Windows shuts down when the services manager tells it to.

At this point, you have most likely reached Stage 4, which would go on to terminate currently active connections.

CONCLUSION

Maybe a Bug Report or Trouble Ticket would be in order. Just make sure you have done your due diligence in terms of finding out if the Service Manager died or that human error or lack of memory on the Windows box was ruled out.


Union implemented with Hash Match operator

I cannot recall seeing a hash match (union) operator in the wild so I can't speak authoritatively as to when they are better than the alternatives. It's possible to force one using the { CONCAT | HASH | MERGE } UNION query hint but let's try to create a real example. Quote from the documentation referenced in the question:

  For the union operator, use the first input to build the hash table (removing duplicates). Use the second input (which must have no duplicates) to probe the hash table, returning all rows that have no matches, then scan the hash table and return all entries.

So how can we create a query with a hash match (union) operator as the option with the lowest cost? Well, hash join tends to scale much better with parallelism than merge join so a query running in parallel could help tip the scales towards hash match. We need the second input to have no duplicates so a unique constraint on a table could help, but unique constraints are implemented as indexes so that will help a merge join as well. Perhaps giving the hash table lots of duplicates will favor the hash match over the concatenation option because we'll be doing a smaller effective sort?

After some trial and error, one approach that works on my machine is to insert one million rows with 10000 distinct values into one table and one million distinct values into another table. Sample code:

CREATE TABLE X_NUM_SMALL (ID INT NOT NULL);
GO
INSERT INTO X_NUM_SMALL WITH (TABLOCK)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
GO 100

CREATE TABLE X_NUM_1000000_UQ (ID INT NOT NULL);

INSERT INTO X_NUM_1000000_UQ WITH (TABLOCK)
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

ALTER TABLE X_NUM_1000000_UQ
ADD CONSTRAINT UC_X_NUM_1000000 UNIQUE (ID);

SET STATISTICS IO, TIME ON;

The following query has a hash match (union) operator with a total estimated cost of 12.3812 units:

SELECT *
FROM X_NUM_SMALL

UNION

SELECT *
FROM X_NUM_1000000_UQ
OPTION (MAXDOP 4);

Adding a MERGE UNION hint to the query increases the cost just barely to 12.6551 optimizer units. Exchanging that hint for a CONCAT UNION hint increases the cost further to 17.2215 optimizer units.

I did a few test runs and usually the hash match (union) operator query was just barely faster than the others. Here are results from the most recent run:

╔════════════╦══════════╦══════════════╗
║ UNION TYPE ║ CPU TIME ║ ELAPSED TIME ║
╠════════════╬══════════╬══════════════╣
║ HASH       ║      657 ║         2279 ║
║ MERGE      ║      312 ║         2375 ║
║ CONCAT     ║      906 ║         2459 ║
╚════════════╩══════════╩══════════════╝


PostgreSQL - retrieving max primary key column to insert a new row - any alternatives?

Postgres has the serial datatype which matches SQL Server's IDENTITY or MySQL's AUTO_INCREMENT.

Internally it is shorthand for a SEQUENCE but does that matter? It acts like IDENTITY/AUTO_INCREMENT:

  The data types serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

  is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Edit,

I think what OP means is "is there SCOPE_IDENTITY or such" in PostgreSQL. Yes.
You'd need currval or another one


You need to have already declared the table that the foreign key references, before you can define a foreign key that references it.

Once you declare the second table, you can then declare the first table.  Tested here on MySQL 5.5.27.

If you need to bypass the validation, you can do this:

SET FOREIGN_KEY_CHECKS = 0;
-- declare tables
SET FOREIGN_KEY_CHECKS = 1;

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks


SQL+ Select highest 5 number of entries and display in ascending order

No, that does not work fine for selecting highest entries. Your query grabs the first 5 rows in any order, and orders them by commission, it is a very common mistake with rownum.

You need to order by first and after that you can use rownum:

select * from
(
  SELECT LAST_NAME, COMMISSION_PCT
  FROM EMPLOYEES
  WHERE JOBID = 'SALES' order by commission_pct desc
)
where rownum <= 5
order by commission_pct;

Or:

select last_name, commission_pct from
(
  SELECT LAST_NAME, COMMISSION_PCT, rank() over (order by commission_pct desc) as rn
  FROM EMPLOYEES
  WHERE JOBID = 'SALES'
)
where rn <= 5
order by commission_pct;

Or 12c+:

select * from (
select last_name, commission_pct
FROM EMPLOYEES WHERE JOBID = 'SALES'
order by commission_pct desc fetch first 5 rows only)
order by commission_pct;


How to perform asynchronous cascade slave replication of PostgreSQL database?

It's Postgres or PostgreSQL, not PostGre.  The method you want is streaming replication or Point In Time Recovery (PITR) which is supported in 9.0 and above.  It's covered in the docs.  Read up on it, and then if you have any questions, come on back with them


SSRS, Limit Fixed number of Columns in Matrix within a Tablix - issue in sorting matrix data

I have managed to resolve this issue, thought i'll be helpful for others. The order needs to be on the main tablix and not on the inner group or Matrix.


How to avoid IX deadlock in check constraint

If you don't provide an index on column N, SQL Server cannot check if the target value exists efficiently, and must scan the table. The scan continues until a match is found, or scans the entire table if no match exists (your target case). This is terribly inefficient, and happens per row.
At the default locking read committed isolation level, the scan also usually means acquiring and releasing shared locks on every row while they are tested for a match.
Depending on the order in which rows are inserted (and so exclusively locked) on different connections, the competing activities will lead to extensive shared-exclusive blocking or deadlocking.
So you need a nonclustered index on N, such as:
CREATE NONCLUSTERED INDEX [IX dbo.T1 N]
ON dbo.T1 (N);

With an index
You may still encounter deadlocks when the index is present (as you have seen). This is because when the table is small, SQL Server may choose to scan the nonclustered index rather than seeking to the desired value of N and then seeking for id > @id OR id < @id.
This can lead to blocking or deadlocks in a similar way to the original case. The exact interactions are a little more complex because SQL Server will normally choose to insert the new row in the clustered index and nonclustered index before checking the constraint using the scalar function. (It is possible to avoid this in the demo scenario by forcing SQL Server to insert into the nonclustered index after checking the constraint, but I don't want to get into that.)
A workaround
Let me just say that all but the most expert practitioners should avoid enforcing check constraints with scalar functions. There are just too many quirks and hidden gotchas. You will likely end up with invalid data when constraints don't work as expected, or indeed even fire at all.
Bear in mind that most people think they are a great deal more expert than is truly the case. That said, and for educational value only, it is possible to avoid the deadlock scenario in the demo by forcing seek access on the nonclustered index.
I have also added a READCOMMITTEDLOCK hint because validating constraint must be done using the most recent committed values, not row versions. The demo fails to ensure uniqueness when read committed snapshot isolation or snapshot isolation is in use, because the function may read out-of-date data.
CREATE FUNCTION dbo.fn_CheckN
(
    @id integer,
    @n integer
)
RETURNS integer
WITH SCHEMABINDING
AS
BEGIN
    RETURN
        CASE WHEN EXISTS
            (
                SELECT 1
                FROM dbo.T1 AS T
                    WITH (READCOMMITTEDLOCK, FORCESEEK)
                WHERE T.N = @n
                AND T.Id != @id
            )
            THEN 0
            ELSE 1
        END;
END;

This is still not robust code, and not fit for production use. It does address the issue shown in the question.
Depending on the real requirements, you might be able to use triggers, denormalized constraints, or an indexed view instead.
Other remarks

HOLDLOCK does not mean hold locks. SQL Server always holds locks long enough to guarantee correctness given the query specification and configuration settings (including isolation level). HOLDLOCK is a synonym for SERIALIZABLE. It is an advanced option for expert users to specify serializable isolation semantics for a particular object, while using a different level on other objects in the same transaction.
TABLOCK ensures only object-level locks are taken. TABLOCKX ensures only exclusive object-level locks are taken. Both will tend to completely serialize access to the specified object, which is great for avoiding contention — but only because you now have no concurrency at all.
The clustered index identity pattern will create a very hot page at the end of the base table. Concurrent inserts will all be targeting that one page, resulting in latch contention, and a reduction in throughput.
Your demo code specifies MARS but you are not using it. Instead, you create a new connection for each single insert. I realize it is just a quick test rig, but still.

Examples of scalar function check constraints not behaving as expected (some links are quite old, but the described behaviour is still current):

Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates by Alex Kuznetsov
Using a UDF in a check constraint to check validity of history windows (start - end date windows) by Tony Rogerson
Be careful with constraints calling UDFs by Tibor Karaszi
Snapshot isolation: A threat for integrity? by Hugo Kornelis
Another Hidden Parallelism Killer: Scalar UDFs In Check Constraints by Erik Darling
Is there any benefit to SCHEMABINDING a function beyond Halloween Protection? by me

Note also that scalar functions used in check constraints are not eligible for inlining on SQL Server 2019+.


How do I connect to Oracle Server without installing Oracle Client on client machines?

use jdbc JDBC Driver Downloads or maybe
ODAC for Windows


SQL Server TDE - add master key and certificate

From my knowledge, you cannot have multiple encryption database encryption keys as the Server stores a Server Master key automatically when SQL Server is first installed. It will use this for decryption (among other things) of database keys.

When you have created a database key (which is implicit) it will store a copy in the Master db, if you issue another create statement that will replace the existing key.

You should be creating a backup of the DMK and storing this somewhere secure, there is nothing to stop you having multiple copies of this key backup.

MSDN should tell you everything you need to know in greater detail.

http://technet.microsoft.com/en-us/library/bb964742.aspx

you will be able to create multiple certificates for external software etc. you can generate these keys yourself or get them from external providers

http://msdn.microsoft.com/en-us/library/ms187798.aspx


Postgresql server causing a lot of IO activity

If you have added a lot of data and later deleted it, most likely Postgres starts auto-vacuuming process on your tables.

Auto-vacuum is designed to be rather gentle and not put heavy load on running server - otherwise it will significantly reduce system performance if it was serving live data.

Since your database is mostly empty and does not have any clients, you can fix this by performing full vacuum:

VACUUM FULL VERBOSE ANALYZE;

On a few hundred megabytes database it should not take more than a few minutes, and should stop any further activity on your database.

However, it is not recommended to perform full vacuum on production database because it locks tables as it goes - do simple VACUUM instead, or rely on auto-vacuum.


Encrypted data using MS SQL SERVER 2008 R2 ENTERPRISE EDITION. Can we decrypt data using 2008 R2 STANDARD VERSION?

Since the only way to encrypt backups natively in SQL Server 2008 R2 is to use Transparent Data Encryption. You will need to back up the TDE certificate and private key used for TDE in the master database and restore it in the master database of either an Enterprise Edition of SQL Server which is equal to or higher version number or an instance of Developer Edition of equal or higher version. A free option for you is to install SQL Server 2016 Developer Edition and restore to that.

If you used third party software, you will need to refer to the documentation for that product.

If this were SQL Server 2012 or above, then what matters more is the version, which will be in the format 12.0.4422.0, not the edition and also if you have used Enterprise only features in your database. Use the SQL statement below to check:

SELECT * FROM sys.dm_db_persisted_sku_features;

If the above SQL returns any rows, then you can't restore on Standard, but there are other options. You can backup databases on Enterprise and restore them to Developer Edition, which supports all Enterprise features, if you want, but you can't restore a backup from a higher version and restore it to a lower version. If the database is less than 10GB, then you can also restore it to an instance of SQL Server Express, which is free and will support restoring encrypted backups but will not support taking encrypting backups. You will also need to backup the certificate and private key from the master database on your Enterprise system and restore it to your target system assuming you are not using an Extensible Key Management system.


How to rewrite a query with MAX in WHERE clause

Summary
Add the index:
CREATE INDEX index_name
ON TableName
    (companyid DESC, id DESC)
INCLUDE
    (active);

Try this rewrite:
SELECT COUNT_BIG(*)
FROM
(
    SELECT TOP (9223372036854775807)
        TN1.active
    FROM TableName AS TN1
    WHERE
        TN1.id =
        (
            SELECT MAX(TN2.id)
            FROM TableName AS TN2
            WHERE
                TN2.companyid = TN1.companyid
        )
) AS SQ1
WHERE
    SQ1.active = 2;

Detail
The original query specification appears to be:

How many rows are there in the table where active = 2; and the id column equals the highest id value across all rows within the same companyid (without the active = 2 restriction, for clarity).

There is a way to get these results with a single scan of a suitable index.
For example, given the table:
CREATE TABLE #TableName
(
    companyid integer NOT NULL,
    id integer NOT NULL,
    active integer NOT NULL
);

A few rows of sample data:
INSERT #TableName
    (companyid, id, active)
VALUES
    (1, 1, 2),
    (1, 2, 2),
    (1, 3, 0),
    (2, 2, 2),
    (2, 3, 2),
    (2, 3, 0);

With the index:
CREATE INDEX index_name
ON #TableName
    (companyid DESC, id DESC)
INCLUDE
    (active);

A query (based on the original) to find rows without the predicate (active = 2) is:
SELECT
    TN1.active
FROM #TableName AS TN1
WHERE
    TN1.id =
    (
        SELECT MAX(TN2.id)
        FROM #TableName AS TN2
        WHERE
            TN2.companyid = TN1.companyid
    );

The execution plan for that query features a single scan of the index, despite the query referencing the same table twice:

The basic idea is to scan the index in key order, returning the row(s) with the highest id within each group of companyid values.
Unfortunately, we cannot simply add the predicate active = 2 to that query without breaking the optimizer transformation that gives us the single scan. The problem (if you're interested) is that the new predicate gets pushed down the plan tree, resulting in a shape that no longer matches the 'group by top' transform.
One workaround adds a meaningless TOP specification around the part of the query we want transformed, preventing the new predicate being pushed past it:
SELECT COUNT_BIG(*)
FROM
(
    SELECT TOP (9223372036854775807)
        TN1.active
    FROM #TableName AS TN1
    WHERE
        TN1.id =
        (
            SELECT MAX(TN2.id)
            FROM #TableName AS TN2
            WHERE
                TN2.companyid = TN1.companyid
        )
) AS SQ1
WHERE
    SQ1.active = 2;

The execution plan is similar to before, with an extra Top (the meaningless, bigint.max one we added), a Filter for the active = 2 predicate, and a final Stream Aggregate to count the rows:

I cannot promise you will get this plan shape with the real data, or that it will be faster. It all rather depends on the number and distribution of the rows.
The new Top also has the effect of disfavouring a parallel plan (the group by top is parallel-compatible, but a global top is not; parallelism would have to stop and restart around it).
Nevertheless, until a better option comes along, or more detail is provided, it is something you could try.


because resources is itself a parent table of further tables

As an alternative, you could model the ownership of resources the same way you would model the membership of users and groups:

I think this model might allow for more flexibility depending on other possible business requirements for ownership of resources.

create table users(
    userId   int not null primary key
  , userName varchar(100)
  /* plus other user columns */
);
create table groups(
    groupId   int not null primary key
  , groupName varchar(100)
  /* plus other group columns */
);

create table groupUsers(
    groupUsersId int identity(1,1) not null primary key
  , groupId int not null
  , userId  int not null
  , constraint fk_groupUsers_Users
      foreign key(userId) references users(userId)
  , constraint fk_groupUsers_Groups
      foreign key(groupId) references groups(groupId)
);

and resources like:

create table resources(
    resourceId int not null primary key
  /* plus other resource columns */
);

create table userResources (
    userResourcesId int identity(1,1) not null primary key
  , userId     int not null
  , resourceId int not null
  , constraint fk_userResources_Users
      foreign key(userId) references users(userId)
  , constraint fk_userResources_Resources
      foreign key(resourceId) references resources(resourceId)
  /* if resource can only link to 1 user */
  , constraint uq_userResources_resourceId
      unique (resourceId)
);

create table groupResources (
    groupResourcesId int identity(1,1) not null primary key
  , groupId    int not null
  , resourceId int not null
  , constraint fk_groupResources_Groups
      foreign key(groupId) references groups(groupId)
  , constraint fk_groupResources_Resources
      foreign key(resourceId) references resources(resourceId)
  /* if resource can only belong to 1 group */
  , constraint uq_groupResources_resourceId
      unique (resourceId)
);

If you want to use check constraints to enforce that a resourceId is only in one of userResources and groupResources you could use something like this:

go
create function udf_resourceId_in_groupResources (@resourceid int)
returns bit as
    begin;
    if exists (select 1 from groupResources g where g.resourceId = @resourceId)
        return 1;
    return 0;
    end;
go
alter table userResources
  add constraint chk_ResourceId_notin_groupResources
    check (dbo.udf_resourceId_in_groupResources(resourceId) = 0)
go
create function udf_resourceId_in_userResources (@resourceid int)
returns bit as
    begin;
    if exists (select 1 from userResources u where u.resourceId = @resourceId)
        return 1;
    return 0;
    end;
go
alter table groupResources
  add constraint chk_ResourceId_noin_userResources
    check (dbo.udf_resourceId_in_userResources(resourceId) = 0);
go


Duplicate key error should never happen.  When it does, then probably

The Master and Slave were not in sync (perhaps for a long time)
You are writing to the Slave.

Never write to a Slave.  Recommend setting readonly=ON on the Slave.  This will (mostly) prevent accidental writes.  However, user 'root' (or any SUPER user) will bypass the readonly check.

As Aaron mentioned, percona.com has a tool for checking consistency between Master and Slave.  They have another tool for syncing them.


Should Query Tuning be Proactive or Reactive?

Both, but mostly proactive

It's important to test during development against realistic volumes and quality of data. It's unbelievably common to have a query running on a developers 100 or 1000 rows then fall flat with 10 million production rows.

It allows you to make notes too about "index may help here". Or "revisit me". Or "will fix with new feature xxx in next DB version".

However, a few queries won't stand the test of time. Data distribution changes or it goes exponential because the optimiser decides to use a different join type. In this case, you can only react.

Saying that, for SQL Server at least, the various "missing index" and "longest query" DMV queries can indicate problem areas before the phone call

Edit: to clarify...

Proactive doesn't mean tune every query now. It means tune what you need to (frequently run) to a reasonable response time. Mostly ignore the weekly 3am Sunday report queries.


manual execution of specific remote query with postgresql_fdw

For the time being, it seems that the best available option is to create a view on the remote server that encapsulates the query needing to be "pushed down".  postgres_fdw is happy to define and use foreign tables backed by views on the remote, and regular old query optimization within the view does the Right Thing.  For instance, given

CREATE VIEW id_ranges AS
SELECT 'url_strings'::text AS tbl,
    min(url_strings.id)::bigint AS lo,
    max(url_strings.id)::bigint AS hi
   FROM url_strings
UNION
 SELECT 'captured_pages'::text AS tbl,
    min(captured_pages.url)::bigint AS lo,
    max(captured_pages.url)::bigint AS hi
   FROM captured_pages
UNION
 -- ... several more like that ...

on the remote, and a FOREIGN TABLE of the same name on the local server,

SELECT lo, hi FROM id_ranges WHERE tbl = 'url_strings';

the existing pushdown optimization will send the WHERE constraint to the remote, and the remote will scan only one table (making use of indexes if possible) and send back a single-row result.


Check Oracle Service is Started from Batch Command

You can actually start that service, while you are waiting on it to start in another session. Even if both starts occur at the same time, Windows should be able to handle that correctly, and end with the service being up. Or, you sleep. What time ? Put something, and test. Ain't that hard, no ?


Your answer to number 5 is lacking a GROUP BY (corresponding total quantity).

If your course requires you to return sets (as opposed to multi-sets) you may need to eliminate potential duplicates from some of those queries. Your results may not show duplication now, due to the data in the tables, but to be correct for different states of the database (different contents) you may need to add DISTINCT, unless constraints exist to make it unnecessary.

Some of your queries could equally be expressed using joins - if you have covered those, it might be a good idea to use some.


The reason is that the cost-based optimizer has a plan in memory from a previous select, where the number of records for a given organization, namely 'LOL', was much smaller, some 14000.  When the plan is in memory, the CBO doesn't care about the parameter values anymore.  Use:

WHERE organization || '' = 'BA'


I also had the same issue using Mariadb 10.4 master-slave replication.
I was using the setting innodb_thread_concurrency in the config file with a non-zero value. I updated it to:

innodb_thread_concurrency = 0

And now it's working again.


Debate on which id to use as reference when quering a one to many table

Option One, Make a new table referencing the id of the Parent/Child Relationship

Pros

If you need to obfuscate the data, or generate a GUID, or a custom id
for an external department working with the data, this might be a valid option to consider.

Cons

It's not useful for any other situation that this first person can think of.

Option Two, Use the Child Id

Pros

The number is already there.
The parent id is assumed to be in the same table as the child id which is convienent
This is probably the simplest way to go.

Cons

Unlike option one, whoever receives the query will then know the child id. In most cases it's fine. But could potentially be a security risk?

Examples:

Option Three, Use the existing Parent and Child ID to generate a new one

Pros

You get an id number that has the data for the Parent and the Child
A query can generate this number on the fly, no need to store it.

Cons

Security wise you are giving away two id numbers
You can potentially reach a limit. Padding with zeros means that once there are enough entries in the database, which may or may not ever happen, you will hit the upper limit on the number you generate and thus potentially risk creating duplicate ids

Example

Parent id = [1 to 300]
Child id = [1 to 3000]
Concat = Parentid + string formated child id that always shoes 4 numerals (0000)

10001 First possible entry [1] + [000] + [1]
3003000 Last possible entry [300] + [3000]

If in the future the child has 10023 as an id you might get

    110023
    is this
    [1] + [10023]
    or is it
    [11] + [0023]

It may not matter if the result is always exported from the database, but if you need to return data to the database based on using these ids, then it might cause issues.

Interesting similar questions

Which is better: identity columns or generated uinique id values?


This installation of MySQL is already upgraded to 5.7.25?

Actually here is a solution that worked for me:
https://techierg.blogspot.com/2019/06/ubuntu-1804-mysql-this-installation-of.html
Quote:

Briefly, the solution is
Edit the file /var/lib/dpkg/info/mysql-server-5.7.postinst

sudo nano /var/lib/dpkg/info/mysql-server-5.7.postinst

At line # 370, comment out the echo ALTER statement which would like

#echo "ALTER USER 'root'@'localhost' IDENTIFIED WITH 'auth_socket';"                 >> "$initfile"
Restart the VM, then...

Run dpkg configure again and the problem will be solved.

sudo dpkg --configure -a
That solved it for me and saved me hours of research.
The article refers to this link here:
https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1817374/comments/8


Why does Postgres ORDER BY seem to halfway ignore leading underscores?

As you haven't defined a different collation for your column in question, it uses the database-wide one, which is en_US.UTF8 - just like on my test box.  I observe the exact same behaviour, take it as a consolation :)

What we see is apparently a case of the variable collation elements.  Depending on the character and the collation, a number of different behaviours is possible.  Here the underscore (and the hyphen and some others, too) are used only for breaking ties - 'a' and '_a' are equivalent in the first round, then the tie between them is resolved by taking the underscore into account.

If you want to sort with ignoring the underscores (and hyphens, question marks and exclamation marks in my example), you can define an ordering on an expression:

SELECT *
FROM (VALUES ('a'),
             ('b1'),
             ('_a'),
             ('-a'),
             ('?a'),
             ('!a1'),
             ('a2')
     ) t (val)
ORDER BY translate(val, '_-?!', '');

In my experiments adding a new value to the list often changes the order between otherwise equal items, showing they are treated really equal.


Why is my database using Partitioning? ( I know it's not )

According to this documentation, this will return a value if the database has any of the following:

Partitioned tables
Partitioned indexes
Partition schemes
Partition functions

So check for and remove any partition schemes or functions, even if they aren't in use. You can do this in the SSMS UI (Database > Storage > Partition Schemes/Functions), or by querying the following system views:

sys.partition_schemes
sys.partition_functions


Find top by values by percents using range in SQL Server

Another way of doing it would be

WITH CTE
     AS (SELECT ID,
                PKI,
                ROW_NUMBER() OVER ( ORDER BY PKI) AS RN,
                COUNT(*) OVER()                   AS Cnt
         FROM   myTable)
SELECT ID,
       PKI
FROM   CTE
WHERE  RN BETWEEN 0.5 * Cnt AND 0.75 * Cnt


Consider that MySQL has a 31MB source code download. PostgreSQL has a 16MB source code download. SQLite has a 1.45MB source code download - please at least start by comparing like with like - comparing 300K with 600 MB isn't fair!

While it's true that SQLite can be made smaller, it's also possible to do the same with MySQL - there is an embedded version (I've never used it, so can't speak to its size).

MySQL also comes with a bunch of utilities (for backing up and checking integrity for example and there are many more - don't have a running system handy to go through them all).

Furthermore, MySQL supports a bunch of storage engines - SQLite does not.

And now the biggie - MySQL supports concurrent updates - SQLite does not. As D. Richard Hipp himself says, "SQLite is not designed to compete with Oracle. SQLite is designed to compete with fopen()".

I think he's being modest - his achievement with SQLite is impressive - but it is not a multi-user RDBMS. MySQL can certainly compete in many spheres that Oracle moves in which SQLite could never do - can you imagine running a telecoms system without concurrent updates? Just my 0.02c.

[EDIT in response to the comment of @jynus]

Yes, indeed, those figures are very interesting.

SQLite 212,680 lines of code
PostgreSQL 727,260 lines of code
MySQL 2,380,079 lines of code

Seeing as all of them are written in C/C++, it is a fairer comparison of size than MB of download.

It would be even more interesting to tease out what, exactly, these "lines of code" figures correspond to.

I.e. one could ask why SQLite has the broad functionality it does on 1/10 of the MySQL code base?

Or how come PostgreSQL is so much more competent an RDBMS than MySQL with 1/3 of the code?

And/or how many of the client tools and other assorted "fluff" are included in the MySQL source code line count?

Also, given that C++ should encapsulate more functionality per line than C, this would suggest that MySQL code is even less "efficient" (by the crude measure of lines of code).

(BTW, I'm putting on fire-retardant clothing as we speak! :-) )

Great topic for discussion - not sure how long it would last here though, more's the pity!


Error Code 1117 Too many columns; MySQL column-limit on table

Why would you need to create a table with even 20 columns, let alone 2000 ???

Granted, denormalized data can prevent having to do JOINs to retrieve many columns of data. However, if you have over 10 columns, you should stop and think about what would happen under the hood during data retrieval.

If a 2000 column table undergoes SELECT * FROM ... WHERE, you would generate large temp tables during the processing, fetching columns that are unnecessary, and creating many scenarios where communication packets (max_allowed_packet) would be pushed to the brink on every query.

In my earlier days as a developer, I worked at a company back in 1995 where DB2 was the main RDBMS. The company had a single table that had 270 columns, dozens of indexes, and had performance issues retrieving data. They contacted IBM and had consultants look over the architecture of their system, including this one monolithic table. The company was told "If you do not normalize this table in the next 2 years, DB2 will fail on queries doing Stage2 Processing (any queries requiring sorting on non-indexed columns)." This was told to a multi-trillion dollar company, to normalize a 270 column table. How much more so a 2000 column table.

In terms of mysql, you would have to compensate for such bad design by setting options comparable to DB2 Stage2 Processing. In this case, those options would be

max_allowed_packet
tmp_table_size
max_tmp_tables
max_heap_table_size
max_length_for_sort_data
max_sort_length
sort_buffer_size
myisam_max_sort_file_size
myisam_sort_buffer_size

Tweeking these settings to make up for the presence of dozens, let alone hundreds, of columns works well if you have TBs of RAM.

This problem multiplies geometrically if you use InnoDB as you will have to deal with MVCC (Multiversion Concurrency Control) trying to protect tons of columns with each SELECT, UPDATE and DELETE through transaction isolation.

CONCLUSION

There is no substitute or band-aid that can make up for bad design. Please, for your sake of your sanity in the future, normalize that table today !!!


ALTER DATABASE MODIFY FILEGROUP [filegroup] AUTOGROW_ALL_FILES

It seems there is no way around it.  This is per an offline conversation with Paul Randal from SQL Skills.  So that would mean I would need to either have no other open sessions on the db, or set it to single user mode prior to running the set statement.  Thanks for the assistance Paul.


Passing parameters to a procedure executed by DBMS_SCHEDULER

When you create a program with CREATE_PROGRAM procedure, you can specify how many arguments it expects with number_of_arguments parameter.

If, for some reason, you want to modify some attribute (number_of_arguments in our case) for an existing Scheduler object (program in our case), you don't need to drop the object and re-create it with new attribute value again, you can just use SET_ATTRIBUTE procedure instead:

  dbms_scheduler.set_attribute(
      name => 'my_program'
    , attribute => 'number_of_arguments'
    , value => 1);

Once you've created the program and all of its attributes are set, you can define the arguments of the program (if there are any) with DEFINE_PROGRAM_ARGUMENT procedure:

dbms_scheduler.define_program_argument(
    program_name => 'my_program'
  , argument_position => 1
  , argument_type => 'VARCHAR2'
  , default_value => 'STARTING'
);

Here, we defined the program argument and its default value. We could have left the default value unspecified, but in this case we would have to set the value of the argument using SET_JOB_ARGUMENT_VALUE procedure. For example, if you created the job and specified the associated program as follows:

dbms_scheduler.create_job(
    job_name => 'call_my_program'
  , program_name => 'my_program'
  ...
);

You would have to set the value of the argument in the program associated with the job this way:

dbms_scheduler.set_job_argument_value(
    job_name => 'call_my_program'
  , argument_position => 1
  , argument_value => 'STARTING');

If you have defined the default value of an argument for a program with DEFINE_PROGRAM_ARGUMENT procedure and also set the value of the argument with SET_JOB_ARGUMENT_VALUE procedure, the argument value set with SET_JOB_ARGUMENT_VALUE procedure will override the default argument value set with DEFINE_PROGRAM_ARGUMENT procedure.


I also saw this in my own environment along with a few other messages:

  SQL Server has encountered 1 occurrence(s) of cachestore flush for
  the 'Bound Trees' cachestore (part of plan cache) due to some
  database maintenance or reconfigure operations.
  SQL Server has encountered 1 occurrence(s) of cachestore flush for
  the 'SQL Plans' cachestore (part of plan cache) due to some database
  maintenance or reconfigure operations.
  SQL Server has encountered 1 occurrence(s) of cachestore flush for
  the 'Object Plans' cachestore (part of plan cache) due to some
  database maintenance or reconfigure operations.

It turns out there were multiple databases with the AutoClose setting turned on.

You can check your own database with this command:

SELECT DATABASEPROPERTYEX('DatabaseName','IsAutoClose')

Once we turned the setting off, the messages went away and performance improved. For further reading as to why auto close is a bad idea:

Worst Practice: Allowing AutoClose on SQL Server
SQL Server Best Practices: AutoClose should be off


You could use xp_cmdshell, or you could use xp_regread. Since xp_regread is 'safer' (I mean, you don't have to change any configuration options to use it), you could do something like this:

DECLARE @cpu_speed_mhz int,
        @cpu_speed_ghz decimal(18,2);

EXEC master.sys.xp_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                            @key = 'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
                            @value_name = '~MHz',
                            @value = @cpu_speed_mhz OUTPUT;

SELECT @cpu_speed_ghz = CAST(CAST(@cpu_speed_mhz AS DECIMAL) / 1000 AS DECIMAL(18,2));

I tested this on a few different computers and it seemed to get me what I needed. It may work for you as well.

Just remember that xp_regread is undocumented, and may blah blah blah like Microsoft ever deprecates anything anyway.

Hope this helps!


How to get the SQL_ID that is blocking another SQL?

Locks belong to transactions, not SQL statements.

SQL statements do not block other SQL statements on their own. Transactions may block other transactions.

A transaction may consist of multiple SQL statements.

The database can return the current (v$session.sql_id) and previous (v$session.prev_sql_id) SQL statement for a session.

If the blocking session is idle, its sql_id will be null.

If the lock that blocks another transaction was placed by not the current and not the previous SQL, but by one before them, then you are out of luck, you will not find it in such views.

Even if the lock that blocks another transaction was placed by the current or the previous SQL of the blocking session, there is no direct evidence provided about it. You need to examine the SQLs, you need to know what they do, and using that information, decide if really those SQLs placed the lock that blocks another transaction (session).

A simple query like that may provide adequate results for simple cases, but it can not provide a guaranteed answer to what you asked generally.

You can do that with tracing and carefully reading and analyzing the trace files.


Why Does SSIS Oracle Source by Attunity return DT_WSTR for Oracle CHAR(1) Column?

I believe I figured this out, but if anyone has better documentation or expertise, please do post a separate answer, and I will vote it up and accept it.
What I have discovered is that Oracle PL/SQL has a means for expressing the concepts of "byte semantics" and "character semantics" in PL/SQL when declaring character data types.
See the Length Semantics for Character Datatypes heading in this Oracle Documentation Web Page where it states:

Consider the size of characters when you specify the column length for character datatypes. You must consider this issue when estimating space for tables with columns that contain character data.
The length semantics of character datatypes can be measured in bytes or characters.
Byte semantics treat strings as a sequence of bytes. This is the default for character datatypes.
Character semantics treat strings as a sequence of characters. A character is technically a codepoint of the database character set.

While reviewing my Oracle source database, I am now noticing that the fields that have been imported as DT_WSTR are in fact declared using the PL/SQL syntax xxxCHAR(n CHAR) - where the the second "CHAR" is explicitly stating the character semantics - rather than the default byte semantics.
Thus, in my Oracle database, I believe I need to treat a column with database data type CHAR(1 CHAR) as a character datatype using character semantics, and I believe that means that the field can accommodate 1 character regardless of the byte length of the character set in use.
Given the above, SSIS (via the Microsoft Oracle Connector by Attunity) will promote non-NCHAR/NVARCHAR data types to DT_WSTR (as opposed to DT_STR).
And, following the above, I will need NCHAR and NVARCHAR SQL Server database data types when importing columns expressed using character semantics.
Once I do this, I will no longer have to convert DT_WSTR data types to DT_STR in my SSIS data flows (which was becoming quite tedious).
Note, I was already using NCHAR and NVARCHAR for fields declared as such on the Oracle side - expecting to provide the proper storage for these types on the SQL Server side.
UPDATE: Here is the docs.oracle.com web page that shows the syntax I describe as follows:

CHAR
You use the CHAR datatype to store fixed-length character data. How the data is represented internally depends on the database character set. The CHAR datatype takes an optional parameter that lets you specify a maximum size up to 32767 bytes. You can specify the size in terms of bytes or characters, where each character contains one or more bytes, depending on the character set encoding. The syntax follows:
CHAR[(maximum_size [CHAR | BYTE] )]


mysql directory grow to 246G after one query, which failed due to table is full

The table simply does not exist. How can you verify this ?

Goto the operating system and run the following

cd /var/lib/mysql/crs
ls -l CRS_PAIR.frm

If that file does not exist, the table does not exist. If that is the case, then what's all that 246G bloated space ? You need to look the following

InnoDB Architecture

Take a closer look at ibdata1 ( system tablespace ). What does it comprise of ?

Data Dictionary
Double Write Buffer (support data consistency; used for Crash Recovery)
Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
Rollback Segments
Undo Space (where the most uncontrolled growth can happen)

What you are the victim of is a large transaction that cannot be rolled back effectively.

I discussed InnoDB wild growth back on April 23, 2013 : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

You are probably wondering....

But I got a Table Full Error !!!

Think about what you were doing

You create an empty InnoDB table via DDL (it's technically a temp table at this point)
You attempted to load that empty table with 246G of BLOB/TEXT. There are tons of transactional records (for MVCC and Transaction Isolation Support) being accumulated in the interim.
Since you logically ran out of space due to BLOB/TEXT records in a single transaction, the query would have to rollback and the temp table would vanish, leaving no .frm to show for it.. What you are left with is a 246G undo log entry that could not hold records as wide as your query demands. InnoDB is notorious for not reclaiming space (See my very first StackOverflow post ever from October 29, 2010 on how to reclaim that wasted space).

CAVEAT #1

Table Full Errors for InnoDB is not that far-fetched because it is a logical failure to manipulate the Storage Engine. In your case, it would be due to BLOB/TEXT fields passing through logs and a Log Buffer that simply not big enough. I have another post about how you can get a "Table Full" Error when consuming all the Rollback Segments (How to solve "The table ... is full" with "innodb_file_per_table"?)

CAVEAT #2

If you insist on running that query and trying to make the table, you will have to expand the Transaction Logs (innodb_log_file_size) and the Log Buffer Size (innodb_log_buffer_size) (Look back at the InnoDB Architecture at the Log Buffer and the two Log Files).

My Post about doing so
Where my inspiration for that post came from (someone else's 5 years old post)


Yes, DTutil is still going to be the best option. I have a post, SSIS Package Extract from MSDB on how to use dtutil + the data in the packages table to generate the call(s) to export to disk. Instead of the file destination, you would substitute sourceserver. If you've used folders to organize your packages, then you'll need to create them beforehand.

Script posted here for posterity. This applies for 2008+ packages or 2012/2014 package that have used the package deployment model. With 2005, sysssispackagefolder would be replaced with sysdtspackages90 and sysssispackagefolders with sysdtspackagefolders90

;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.dbo.sysssispackages P
)
SELECT
    -- assumes default instance and localhost
    -- use serverproperty('servername') and serverproperty('instancename')
    -- if you need to really make this generic
    -- File extraction command line
    'dtutil /sourceserver localhost /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;".\' + P.PackageName +'.dtsx"' AS cmd
    -- Move Package command line
    -- 'dtutil /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /SOURCEUSER srcUserName /SOURCEPASSWORD $Hj45jhd@X /DestServer <servername> /MOVE SQL;"'+ F.FolderPath + '\' + P.PackageName + '" /DESTUSER destUserName /DESTPASSWORD !38dsFH@v' AS cmd
FROM
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
 WHERE
     F.FolderPath <> '\Data Collector'


Why are collations specified at the column level?

Unless MySQL / MariaDB does something that I'm unaware of (being that I typically focus on SQL Server): because encoding (i.e. "character set") is handled separately, column-level collation should simply be a default used for those operations that you mentioned (creating indexes, queries, etc), and comparisons, so that you don't need to specify the collation each and every time (strings cannot be either compared or sorted if the specific rules for doing so are not provided). That would not only greatly complicate queries, it would make it nearly impossible to provide consistency across queries as not having it declared at the column level (or at least at the DB level) would leave it to query developers to remember the intention of the data modeler, and that's certainly not a recipe for success ;-).

If not specified at the column level, collation needs to at least be specified at the database level or even the instance level (and I believe there are RDBMSs that do specify at either level, and maybe don't even allow for specifying at the column level).

Collation just needs to be specified somewhere, else you can't even rebuild an index (let alone create it) without providing the collation. And, allowing the granularity of specifying collation at the column level allows for maximum flexibility as different string columns within a table, or between tables in the same database, can easily have different needs / requirements.

And, just as a side note: for RDBMSs such as SQL Server that combine encoding along with collation in a singular "Collation" (and no way to separate the two), it is a necessity to specify at the column level as it indicates the encoding (at least for VARCHAR / CHAR / TEXT column; NVARCHAR / NCHAR / NTEXT are always UTF-16 LE).


Identify performance bottleneck in SQL Server, checked the usual suspects

I'm going to be brutally honest here: those are not the right metrics to look at, and wherever you're looking that gives you those metrics is wildly outdated. Those are not the usual suspects.

In the year 2018 (and indeed, for the last several years), the way you diagnose a slow SQL Server is by using wait stats. SQL Server is constantly tracking what queries are waiting on, and you can see it in the DMV sys.dm_os_wait_stats.

My favorite way to visualize that is with sp_BlitzFirst @SinceStartup = 1. (Disclaimer: it's my favorite because I wrote it, and it's free & open source.)

Try editing your answer to include a screenshot of your wait stats from sp_BlitzFirst, and we may be able to get you a lot closer.


How do you combine the input of three parameters into one?

I want this query, when the or case is selected and just one formulation N'9870-532' is selected to return only that formulation and of course the other components relating to that formulation.

Query:

SELECT
Test_Matrix.tblTestMatrix.ResultsFolderNumber AS Project, Test_Matrix.tblTestMatrix.Request, Test_Matrix.tblTestMatrix.Release, Test_Matrix.tblTestMatrix.TestName, Test_Matrix.tblTestMatrix.Formulation,
Test_Matrix.tblTestMatrix.Container, Test_Matrix.tblTestMatrix.Closure, Test_Matrix.tblTestMatrix.Shipper, Test_Matrix.tblTestMatrix.Label_Front, Test_Matrix.tblTestMatrix.Label_Back,
Test_Matrix.tblTestMatrix.OtherComp1 + '' + Test_Matrix.tblTestMatrix.OtherComp2 + '' + Test_Matrix.tblTestMatrix.OtherComp3 AS Other, Test_Matrix.tblTestMatrixHeader.FilePath AS FullPath
FROM Test_Matrix.tblTestMatrixHeader INNER JOIN Test_Matrix.tblTestMatrix ON Test_Matrix.tblTestMatrixHeader.ResultsFolderNumber = Test_Matrix.tblTestMatrix.ResultsFolderNumber AND Test_Matrix.tblTestMatrixHeader.Request = Test_Matrix.tblTestMatrix.Request AND
Test_Matrix.tblTestMatrixHeader.Release = Test_Matrix.tblTestMatrix.Release
WHERE (Test_Matrix.tblTestMatrix.Formulation = N'9870-532') OR
      (Test_Matrix.tblTestMatrix.Container = '') OR
      (Test_Matrix.tblTestMatrix.Closure = '') OR
      (Test_Matrix.tblTestMatrix.Shipper = '') OR
      (Test_Matrix.tblTestMatrix.Label_Front = '') OR
      (Test_Matrix.tblTestMatrix.Label_Back = '') OR
      (Test_Matrix.tblTestMatrix.OtherComp1 = '') OR
      (Test_Matrix.tblTestMatrix.OtherComp2 = '') OR
      (Test_Matrix.tblTestMatrix.OtherComp3 = '')
ORDER BY Project, Test_Matrix.tblTestMatrix.Request, Test_Matrix.tblTestMatrix.Release

Results:

The return is not containing only Formulation 9870-532 and other associated items, seen above you have 9670-532 as a formulation item.

Note: I answered instead of comment because a comment was too long.


How many IP numbers are there for a SQL server active active cluster setup also a active passive setup?

You are correct

For Multi Instance SQL Cluster (active/active )

1 IP for Windows Cluster

2 IP for SQL Server Virtual Network Name (SQL instance on each node)

1 IP for MSDTC (this is optional however required if you use distributed transaction like linked server)

2 IP for public network (1 per node)

2 IP for heartbeat network (1 per node)

for Single Instance single cluster ( active/passive)

in above instead of two you will only need one IP address for SQL Server virtual network name


Maybe this will be a good starting point, just click on the blue XML to show the execution paths that have been cached.

    SELECT TOP 100
    cp.objtype
    , st.objectid
    , cp.usecounts
    , ISNULL(qp.query_plan,'') [ActualQueryPlan CLick to Open]
    , DB_NAME(st.dbid) AS [DB_NAME]
    , OBJECT_NAME(st.objectid,st.dbid) AS [OBJECT_NAME]
    , st.text [SQLText]
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
    LEFT OUTER JOIN sys.procedures sp ON Sp.object_id = st.objectid
    ORDER BY cp.usecounts DESC


Can a table have multiple editioning views in Oracle RDBMS 11

You can only have one editioning view per table in each edition.

To explain, some background. Edition-based redefinition (EBR) brought two new schema objects:

Editions
Editioning views

Editions extend the namespace for an object. This prefixes the schema. So the fully-qualified name of an object in Oracle Database from 11.2 is:

edition_name.schema_name.object_name

But you can't explicitly reference the edition_name. It is part of your session settings. When you create an editionable object, it goes in your current edition.

For example, the following creates a new user, editions enables it, creates the table "abc" and the editioning view "ABC":

grant create session, create table, create view, unlimited tablespace to
  usr identified by usr;
alter user usr enable editions;

create edition e1;

grant use on edition e1 to usr;

conn usr/usr

create table "abc" (
  c1 int,
  c2 int
);
create or replace editioning view abc as
  select * from "abc";

select object_name, object_type, edition_name
from   user_objects;

OBJECT_NAME   OBJECT_TYPE   EDITION_NAME
abc           TABLE         <null>
ABC           VIEW          ORA$BASE

If you try and create a new version of the view in the edition e1, you'll get an error:

create or replace editioning view e1.abc as
  select c1 from "abc";

ORA-01917: user or role 'E1' does not exist

This is because you're currently in the edition ora$base. The default for every database.

To create a new version of the view, you need to switch your session to edition e1:

alter session set edition = e1;

create or replace editioning view abc as
  select * from "abc";

select object_name, object_type, edition_name
from   user_objects;

OBJECT_NAME   OBJECT_TYPE   EDITION_NAME
abc           TABLE         <null>
ABC           VIEW          E1

select object_name, object_type, edition_name
from   user_objects_ae;

OBJECT_NAME   OBJECT_TYPE   EDITION_NAME
abc           TABLE         <null>
ABC           VIEW          E1
ABC           VIEW          ORA$BASE

As you see, you now have two views "ABC". One in each edition.

If, still in E1, you try and create a new editioning view using "abc", you'll get an error:

create or replace editioning view abc_new as
  select * from "abc";

ORA-42300: an Editioning view is already defined on this table


SELECT multiple times based on changed dates

Does using a sub query on the same table, something like this, work for you?

select * from yourtable where storedatetime = (
select max(storedatetime) from yourtable )


Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping

Physical implementation of subtyping in a database is a complex issue.  Unless you have a situation where it offers compelling advantages (see below for one or two examples) it adds complexity into implementation while providing relatively little value.

Having done this with really complex subtyping (applicaitons and sentences on a court case management system, disparate combined-risk commercial insurance contract structures) I guess I have some observations on this.  Some significant corner cases are:

If the total number of database fields across the subtypes is relatively low (say: less than 100) or there is significant commonality between subtypes then splitting the subtypes out into separate physical tables is probably of little value.  It will add significant overhead to reporting queries and searches.  In most cases it's best to have a single table and manage your subtyping within the application.  (Probably the closest to your problem)
If your subtyping is very disjoint, and different subtypes have type-dependent data structures hanging off them (i.e. child tables or more complex structures), then subtype tables make sense.  In this case, each subtype probably has relatively little commonality within the application (i.e. there is probably a whole subsystem within the application dedicated to that subtype).  Most reporting and querying will probably occur within a given sub-type, with cross-type queries mainly being restricted to a handful of common fields.  (Court case management system)
If you have a large number of subtypes with disparate attributes and/or a requirement to make this configurable then a generic structure and supplementary metadata may be more appropriate.  See this SO posting for a rundown on some possible approaches.  (Insurance policy administration system)
If you have a very large number of fields with little commonality across your sub-types and little requirement to query across sub-type tables (i.e. nothing much in the way of multi-way outer joins against your sub-type tables) then sub-type tables may help to manage the column sprawl.  (Pathologically complex version of your problem)
Some O/R mappers may only support a particular approach to managing sub-classes.

In most cases physical sub-type tables in a DB schema are a bit of a solution in search of a problem, as they potentially have undesirable side-effects.

In your case, I assume you have a relatively modest number of sub-types and a manageable number of attributes.  Your diagram and question don't indicate any intention to hang child tables off the records.  I would suggest that you consider going with the first option suggested above and maintaining one table and manage the sub-typing within your application.


Actually, if you place an if then block in every trigger, you could effectively shutdown all triggers. Here is such a code block

IF @TRIGGER_DISABLED = 0 THEN
    ...trigger body
END IF;

In the mysql environment, you could

run SET @TRIGGER_DISABLED = 1;
do your data maintenance
run SET @TRIGGER_DISABLED = 0;

So your trigger for table A should look like this:

BEGIN
    IF @TRIGGER_DISABLED = 0 THEN
    IF (OLD.status != 1 AND NEW.status = 2) THEN
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
        END IF;
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
        END IF;
    ELSEIF (NEW.status != 3) THEN
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id;
        END IF;
    END IF;
    END IF;
END

So your trigger for table B should look like this:

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
    FOR EACH ROW
    BEGIN
        IF @TRIGGER_DISABLED = 0 THEN
            CALL sp-set-comment_count(NEW.`gid`);
        END IF;
    END;

If you want the triggers for table A to launch but not to table B, then add the code block only to table B's trigger.


How pass a local variable in Json path during data extraction using Json_value function in sql sever select statement

You should cast @id to varchar and concate it. like this.

(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+cast(@id as varchar)+'"***.value')) Agency,

Complete query is. Hope this will help you.

declare @id INT
set @id = 69
select
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+cast(@id as varchar)+'"***.value')) Agency,
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) Protocal
from actions_new a
left join action_data ad on ad.id_ref = a.id
where baid = 12;

Thanks!


How to pivot on multiple columns in SQL Server?

Here is one way of getting the result set you want without doing the multiple joins.  It takes a little more setup and uses two pivot operations instead of one, but avoids the multiple joins.

I admit that I had to look it up, but Ken O'Bonn had a great article.  https://blogs.msdn.microsoft.com/kenobonn/2009/03/22/pivot-on-two-or-more-fields-in-sql-server/

/** Build up a Table to work with. **/
DECLARE @T TABLE
    (
    ID INT NOT NULL PRIMARY KEY
    , hProp INT NOT NULL
    , iDayOfMonth INT NOT NULL
    , dblTargetPercent DECIMAL(6,4) NOT NULL
    )

INSERT INTO @T
(ID, hProp, iDayOfMonth, dblTargetPercent)
VALUES (117,10,5,0.1400)
        , (118, 10, 10, 0.0500)
        , (119, 10, 15, 0.0100)
        , (120, 10, 20, 0.0100)

/** Create a CTE and give us predictable names to work with for
    date and percentage
    **/
;WITH CTE_Rank AS
    (
    SELECT ID
        , hProp
        , iDayOfMonth
        , dblTargetPercent
        , sDateName = 'iDateTarget' + CAST(DENSE_RANK() OVER (PARTITION BY hPRop ORDER BY iDayOfMonth) AS VARCHAR(10))
        , sPercentName = 'dblPercentTarget' + CAST(DENSE_RANK() OVER (PARTITION BY hPRop ORDER BY iDayOfMonth) AS VARCHAR(10))
    FROM @T
    )
SELECT hProp
    , iDateTarget1 = MAX(iDateTarget1)
    , dblPercentTarget1 = MAX(dblPercentTarget1)
    , iDateTarget2 = MAX(iDateTarget2)
    , dblPercentTarget2 = MAX(dblPercentTarget2)
    , iDateTarget3 = MAX(iDateTarget3)
    , dblPercentTarget3 = MAX(dblPercentTarget3)
    , iDateTarget4 = MAX(iDateTarget4)
    , dblPercentTarget4 = MAX(dblPercentTarget4)
FROM CTE_Rank AS R
    PIVOT(MAX(iDayOfMonth) FOR sDateName IN ([iDateTarget1], [iDateTarget2], [iDateTarget3], [iDateTarget4])) AS DayOfMonthName
    PIVOT(MAX(dblTargetPercent) FOR sPercentName IN (dblPercentTarget1, dblPercentTarget2, dblPercentTarget3, dblPercentTarget4)) AS TargetPercentName
GROUP BY hProp


Completely disable all support for a certain CHAR SET in MySQL?

Sessions can negotiate its own character set (SET names). That was a problem because, at least for some time, the PHP connector selected its own character set disregarding the MySQL server preferred one (default_character_set).

There are several things that people have done to handle that. If possible, fix the client configuration or enforce it on connection. That is the preferred method. Other people have decided to let the client take control of the encoding -Wikipedia does that, storing everything into blobs, but they had very specific requirements, I would not recommend it in general.

If you really want to go in your direction, you must recompile MySQL with the chosen -DDEFAULT_CHARSET and -DWITH_EXTRA_CHARSETS=none.


how to get list of months between two dates in db2

Your date format is ambiguous but given your expected answer is January, I'll assume your start date is 2015-01-02 and end date is 2015-01-12. The following recursive query should do what you need:

WITH months(i, d) AS (
    VALUES (1, DATE('2015-01-02'))

    UNION ALL

    SELECT i + 1, d + 1 MONTH
      FROM months
     WHERE i < 10000
       AND d + 1 MONTH <= DATE('2015-01-12')
)
SELECT monthname(d)
  FROM months

The "i" term in the recursive element is simply there to suppress the "possibly infinite" warning that occurs otherwise. If you're not bothered by the warning, you could just remove it.

This will work on DB2 for LUW and I think it'll work on DB2 for z/OS but that'll depend on whether they've finally added support for VALUES or not; if they haven't just use SELECT foo FROM SYSIBM.SYSDUMMY1 in the base case instead).


How can I setup a Login which can run an automated Database Refresh without giving away too much priv?

You need to use impersonation.

Let's assume that loginB has elevated privileges.  Your stored procedure will have an "Execute as" line:

execute as login = 'yourdomain\loginB';
backup database @dbname to disk.......
revert;

You must grant impersonation on loginB to loginA.  Also, loginA must have execute permissions on the stored procedure.
MSDN reference for EXECUTE AS:  https://technet.microsoft.com/en-us/library/ms188304(v=sql.105).aspx


Why is using a table variable more than twice as fast as a #temp table in this specific case?

The output of SET STATISTICS IO ON for both looks similar

SET STATISTICS IO ON;
PRINT 'V2'
EXEC dbo.V2 10
PRINT 'T2'
EXEC dbo.T2 10

Gives

V2
Table '#58B62A60'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

Table '#58B62A60'. Scan count 10, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

T2
Table '#T__ ... __00000000E2FE'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

Table '#T__ ... __00000000E2FE'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

And as Aaron points out in the comments the plan for the table variable version is actually less efficient as whilst both have a nested loops plan driven by an index seek on dbo.NUM the #temp table version performs a seek into the index on [#T].n = [dbo].[NUM].[n] with residual predicate [#T].[n]<=[@total] whereas the table variable version performs an index seek on @V.n <= [@total] with residual predicate @V.[n]=[dbo].[NUM].[n] and so processes more rows (which is why this plan performs so poorly for larger number of rows)

Using Extended Events to look at the wait types for the specific spid gives these results for 10,000 executions of EXEC dbo.T2 10

+---------------------+------------+----------------+----------------+----------------+
|                     |            |     Total      | Total Resource |  Total Signal  |
| Wait Type           | Wait Count | Wait Time (ms) | Wait Time (ms) | Wait Time (ms) |
+---------------------+------------+----------------+----------------+----------------+
| SOS_SCHEDULER_YIELD | 16         | 19             | 19             | 0              |
| PAGELATCH_SH        | 39998      | 14             | 0              | 14             |
| PAGELATCH_EX        | 1          | 0              | 0              | 0              |
+---------------------+------------+----------------+----------------+----------------+

and these results for 10,000 executions of EXEC dbo.V2 10

+---------------------+------------+----------------+----------------+----------------+
|                     |            |     Total      | Total Resource |  Total Signal  |
| Wait Type           | Wait Count | Wait Time (ms) | Wait Time (ms) | Wait Time (ms) |
+---------------------+------------+----------------+----------------+----------------+
| PAGELATCH_EX        | 2          | 0              | 0              | 0              |
| PAGELATCH_SH        | 1          | 0              | 0              | 0              |
| SOS_SCHEDULER_YIELD | 676        | 0              | 0              | 0              |
+---------------------+------------+----------------+----------------+----------------+

So it is clear that the number of PAGELATCH_SH waits is much higher in the #temp table case. I'm not aware of any way of adding the wait resource to the extended events trace so to investigate this further I ran

WHILE 1=1
EXEC dbo.T2 10

Whilst in another connection polling sys.dm_os_waiting_tasks

CREATE TABLE #T(resource_description NVARCHAR(2048))

WHILE 1=1
INSERT INTO #T
SELECT resource_description
FROM sys.dm_os_waiting_tasks
WHERE session_id=<spid_of_other_session> and wait_type='PAGELATCH_SH'

After leaving that running for about 15 seconds it had gathered the following results

+-------+----------------------+
| Count | resource_description |
+-------+----------------------+
|  1098 | 2:1:150              |
|  1689 | 2:1:146              |
+-------+----------------------+

Both of these pages being latched belong to (different) non clustered indexes on the tempdb.sys.sysschobjs base table named 'nc1' and 'nc2'.

Querying tempdb.sys.fn_dblog during the runs indicates that the number of log records added by the first execution of each stored procedure was somewhat variable but for subsequent executions the number added by each iteration was very consistent and predictable. Once the procedure plans are cached the number of log entries are about half those needed for the #temp version.

+-----------------+----------------+------------+
|                 | Table Variable | Temp Table |
+-----------------+----------------+------------+
| First Run       |            126 | 72 or 136  |
| Subsequent Runs |             17 | 32         |
+-----------------+----------------+------------+

Looking at the transaction log entries in more detail for the #temp table version of the SP each subsequent invocation of the stored procedure creates three transactions and the table variable one only two.

+---------------------------------+----+---------------------------------+----+
|           #Temp Table                |         @Table Variable              |
+---------------------------------+----+---------------------------------+----+
| CREATE TABLE                    |  9 |                                 |    |
| INSERT                          | 12 | TVQuery                         | 12 |
| FCheckAndCleanupCachedTempTable | 11 | FCheckAndCleanupCachedTempTable |  5 |
+---------------------------------+----+---------------------------------+----+

The INSERT/TVQUERY transactions are identical except for the name. This contains the log records for each of the 10 rows inserted to the temporary table or table variable plus the LOP_BEGIN_XACT/ LOP_COMMIT_XACT entries.

The CREATE TABLE transaction only appears in the #Temp version and looks as follows.

+-----------------+-------------------+---------------------+
|    Operation    |      Context      |    AllocUnitName    |
+-----------------+-------------------+---------------------+
| LOP_BEGIN_XACT  | LCX_NULL          |                     |
| LOP_SHRINK_NOOP | LCX_NULL          |                     |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1  |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc1  |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2  |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc2  |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst |
| LOP_COMMIT_XACT | LCX_NULL          |                     |
+-----------------+-------------------+---------------------+

The FCheckAndCleanupCachedTempTable transaction appears in both but has 6 additional entries in the #temp version. These are the 6 rows referring to sys.sysschobjs and they have exactly the same pattern as above.

+-----------------+-------------------+----------------------------------------------+
|    Operation    |      Context      |                AllocUnitName                 |
+-----------------+-------------------+----------------------------------------------+
| LOP_BEGIN_XACT  | LCX_NULL          |                                              |
| LOP_DELETE_ROWS | LCX_NONSYS_SPLIT  | dbo.#7240F239.PK__#T________3BD0199374293AAB |
| LOP_HOBT_DELTA  | LCX_NULL          |                                              |
| LOP_HOBT_DELTA  | LCX_NULL          |                                              |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst                          |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1                           |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc1                           |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2                           |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc2                           |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst                          |
| LOP_COMMIT_XACT | LCX_NULL          |                                              |
+-----------------+-------------------+----------------------------------------------+

Looking at these 6 rows in both transactions they correspond to the same operations. The first LOP_MODIFY_ROW, LCX_CLUSTERED is an update to the modify_date column in sys.objects. The remaining five rows are all concerned with object renaming. Because name is a key column of both affected NCIs (nc1 and nc2) this is carried out as a delete/insert for those then it goes back to the clustered index and updates that too.

It appears that for the #temp table version when the stored procedure ends part of the clean up carried out by the FCheckAndCleanupCachedTempTable transaction is to rename the temp table from something like #T__________________________________________________________________________________________________________________00000000E316 to a different internal name such as #2F4A0079 and when it is entered the CREATE TABLE transaction renames it back. This flip flopping name can be seen by in one connection executing dbo.T2 in a loop whilst in another

WHILE 1=1
SELECT name, object_id, create_date, modify_date
FROM tempdb.sys.objects
WHERE name LIKE '#%'

Example Results

So one potential explanation for the observed performance differential as alluded to by Alex is that it is this additional work maintaining the system tables in tempdb that is responsible.

Running both procedures in a loop the Visual Studio Code profiler reveals the following

+-------------------------------+--------------------+-------+-----------+
|           Function            |    Explanation     | Temp  | Table Var |
+-------------------------------+--------------------+-------+-----------+
| CXStmtDML::XretExecute        | Insert ... Select  | 16.93 | 37.31     |
| CXStmtQuery::ErsqExecuteQuery | Select Max         | 8.77  | 23.19     |
+-------------------------------+--------------------+-------+-----------+
| Total                         |                    | 25.7  | 60.5      |
+-------------------------------+--------------------+-------+-----------+

The table variable version spends about 60% of the time performing the insert statement and the subsequent select whereas the temporary table is less than half that. This is inline with the timings shown in the OP and with the conclusion above that the difference in performance is down to time spent performing ancillary work not due to time spent in the query execution itself.

The most important functions contributing towards the "missing" 75% in the temporary table version are

+------------------------------------+-------------------+
|              Function              | Inclusive Samples |
+------------------------------------+-------------------+
| CXStmtCreateTableDDL::XretExecute  | 26.26%            |
| CXStmtDDL::FinishNormalImp         | 4.17%             |
| TmpObject::Release                 | 27.77%            |
+------------------------------------+-------------------+
| Total                              | 58.20%            |
+------------------------------------+-------------------+

Under both the create and release functions the function CMEDProxyObject::SetName is shown with an inclusive sample value of 19.6%. From which I infer that 39.2% of the time in the temporary table case is taken up with the renaming described earlier.

And the largest ones in the table variable version contributing to the other 40% are

+-----------------------------------+-------------------+
|             Function              | Inclusive Samples |
+-----------------------------------+-------------------+
| CTableCreate::LCreate             | 7.41%             |
| TmpObject::Release                | 12.87%            |
+-----------------------------------+-------------------+
| Total                             | 20.28%            |
+-----------------------------------+-------------------+

Temporary Table Profile

Table Variable Profile


The second expression is syntactically invalid (at least on its own, it could be a valid part of some other expression).

sal := sal + 300;

assigns a value to the local variable sal that is 300 more than the current value of that variable.

sal = sal + 300

does not change the value of sal.  It compares sal to sal + 300 and evaluates to a boolean.  But it would only be valid in something like an if statement or in a SQL statement, i.e.

IF( sal = sal + 300 )

or

SELECT *
  FROM emp
 WHERE sal = sal + 300

Neither of those really make sense, though, since that is, by definition, not going to evaluate to true.


Main problem was that I could not run psql command under no user.
Output I received was:

psql: FATAL:  role "any role" does not exist

any role means that I tried to run psql under each user on my system(root,postgresql,my_user)

After all I haven't figured out what the problem was, I decided to reinstall PostgreSQL.

And this process turned out rather hard to.

So if you have problems with deleting and reinstalling PostgreSQL, read on.

Here what I had to do.

echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" > /etc/apt/sources.list.d/pgdg.list

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt-get update

now we get error about missing postgresql-common
to fix it we need to install above package
lets determine its version

sudo apt-cache policy postgresql-common

and install it

sudo apt-get install postgresql-common=151.pgdg12.4+1

we can get error that postgresql-client-common not installed
so we need to do the same
determine it version

sudo apt-cache policy postgresql-client-common

and install

sudo apt-get install postgresql-client-common=151.pgdg12.4+1

after this we can install postgresql like this

sudo apt-get install postgresql-9.3 postgresql-contrib-9.3


SSIS Dataflow Task issue, moving data from SQL Server 2008 to MySQL via ADO.Net

That's either a problem with MySql or with the driver you're using. All SSIS knows is that the driver says 'I can't do that'.

The place to start is by manually inserting as record of that kind into the destination db and looking for a more meaningful error.


Accessing unsharded database in a MongoDB shard cluster without going through mongos?

Mongo will happily let you directly access any database on any individual node you hit. If that database is sharded, you can still access it but will only get results from that shard. This also means that you can have unsharded databases on a node and it'll return results from that unsharded database.

Yes, it is safe to directly access the replicaset members for that unsharded database.

It's also possible to do it through the MongoS process same as the sharded access. This may make code-paths easier, though it does add a bit of latency to the entire call.

Database is a feature of Nodes.
Sharding is a feature of Databases.
Replica status is a feature of Databases.
MongoS and Config servers represent the management layer needed to do Sharding.

A database that is not sharded does not need MongoS or Config, and can be directly accessed safely.


How do you properly remove "REQUIRE SSL" from a single user?

What you are looking for does not exist in MySQL 5.5

Unfortunately, the ALTER USER command for MySQL 5.6 is limited. All you can do is

ALTER USER user@host PASSWORD EXPIRE;

In MySQL 5.7, you could run the ALTER USER command as follows

ALTER USER user@host REQUIRE NONE;

When it comes to MySQL 5.5, you did the most expedient way possible. Great !!!

A more politically correct way would have been to do the following:

DROP USER user@host;
CREATE USER user@host;
GRANT ... on ... TO user@host IDENTIFIED BY '...';

Otherwise, I commend you for doing what was needed.


This has nothing to do with pivoting, you simply want to replicate your three columns conditionally three times, according to the number of groups. A series of CASE expressions would do the job perfectly:

SELECT
  InvoiceRef,
  Group1Date   = CASE Group1 WHEN 'Y' THEN Date   END,
  Group1Value1 = CASE Group1 WHEN 'Y' THEN Value1 END,
  Group1Value2 = CASE Group1 WHEN 'Y' THEN Value2 END,
  Group2Date   = CASE Group2 WHEN 'Y' THEN Date   END,
  Group2Value1 = CASE Group2 WHEN 'Y' THEN Value1 END,
  Group2Value2 = CASE Group2 WHEN 'Y' THEN Value2 END,
  Group3Date   = CASE Group3 WHEN 'Y' THEN Date   END,
  Group3Value1 = CASE Group3 WHEN 'Y' THEN Value1 END,
  Group3Value2 = CASE Group3 WHEN 'Y' THEN Value2 END
FROM
  dbo.atable
;


Data Backup and Restoring the same if requried in SQL SERVER 2008

You can't without export/import.

A single, shared database implies all data is linked and requires full transactional/referential integrity. So the standard backup/restore won't work because you can't cleanly (or at all?) do row-level partial actions.

You're export/import needs to be transactional safe too, and foreign keys need considered

To make things simpler, you really need a database per tenant.
See Decision criteria on when to use a non-dbo schema vs a new Database for more discussion
If you can't, then you have to live to export/import


optimize design for a table with 50 columns

I believe it's necessary to refer what kind of data your table is storing. Let's say that from C1-C10 you're keeping one group of data and it may be possible that the data in C1-C10 may be redundant (ie, it's possible that you may have the same row of data from C1-C10 throughout your Master Table). Hence, it's wasteful to have redundant data because you're wasting storage space to store the same repeating row of data.

So, what your friend advising you to do is called Normalization. Normalization is a process in which we systematically examine relations for anomalies and, when detected, remove those anomalies by splitting up the relation into two new, related, relations. Normalization can also be thought of as a trade-off between data redundancy and performance. Normalizing a relation reduces data redundancy but introduces the need for joins when all of the data is required by an application such as a report query.

Recall, the Relational Model consists of the elements: relations, which are made up of attributes.

A relation is a set of attributes with values for each attribute such that:

Each attribute (column) value must be a single value only.
All values    for a given attribute (column ) must be of the same
data type.
Each    attribute (column) name must be unique.
The order of attributes    (columns) is insignificant
No two tuples (rows) in a    relation can be    identical.
The order of the tuples (rows) is insignificant.

So, if there's a possibility that the data may be redundant, you may need to separate it to a different table as mentioned by your friend. It will be a pain to join the table together but consider it as a tradeoff towards performance and storage capacity.


This appears to be a problem with a recent XAMPP release, my suggestion is to roll back to a previous XAMPP version.

That being said, the phpMyAdmin way of fixing it would be:

Comment out the pma__tracking line in config.inc.php. You may also need to comment out the pma__column_info line as well. This will allow you to start phpMyAdmin without the constant error messages.
Start phpMyAdmin normally
Run the included sql/create_tables.sql (making modifications if needed to match the XAMPP naming scheme)
Remove the comments in config.inc.php

Of course, I can't say whether that will also work for XAMPP.


Merge Statement + Replication difference for SQL Server 2012

Can you check the article property -->Statement Delivery --> Update delivery format -- what is it set to ?

One more thing to add -- please verify if any column that is part of a unique constraint is updated. If this is true, this means SQL Server is implementing the Update as a Deferred Update, which means as a pair of DELETE/INSERT operations. This "deferred update" causes replication to send a pair of DELETE/INSERT statements to the subscribers. refer : here

To make sure that the Updates are NOT executed as a pair of Delete and Insert statements you can enable trace flag 8207 on Publisher Server.


Online Schema migration in MySQL : When to start?

I think that Percona's pt-online-schema-change might be a better solution for you as it can do an ALTER with very little downtime. It does, however, require adding a TRIGGER to the table.

Runnig DDL on your table will lead you into rebuilding your table, mysql is a row based engine so you will suffer the index rebuild this can be quite expensive depending on your engine, table complexity is factor as well.


Azure database - unable to locate in my Azure account

The connection information suggest the SQL instance is simply a local SQL Server instance installed in the VM like any other software so you don't use the Azure portal to manage it. Instead, ssh into the VM and run systemctl status mssql-server --no-pager to see if mssql-server.service is listed.

The instance could also be a docker database container, in which case you can use docker ps to list the docker containers running on the machine.


PostgreSQL: psql command-line client does not show all schemas

It's not the client application that makes the results differ, but the database user you're connected with.

In 9.3, schemata returns only the schemas that the session's user owns, either directly or indirectly through a granted role.

The doc says:

  The view schemata contains all schemas in the current database that
  are owned by a currently enabled role.

Note that it was changed in later versions to be less restrictive.

See What permissions are required to return rows from information_schema.schemata? (9.3)
and How to check if PostgreSQL public schema exists? on stackoverflow


SSIS Validation slow. OLTP OLAP running on same server

With some offline conversations, Zane was able to determine that if they could keep a connection open, it would hasten the validation.

To that end, I suggested they change the property on the Connection Manager to flip the RetainSameConnection property to True from its default.


SSDT schema compare, removing and then renaming stored procedure result is incorrect

The issue was that stored_procedure.sql still had stored_procedureV2 as procedure name. Then SSDT actually does a correct compare by saying: stored_procedureV2.sql is unchanged and stored_procedure.sql is removed.


Table with persisted computed CLR originated data marked with has_unchecked_assembly_data = 1, but DBCC CHECKTABLE does not unset this same flag

OP here, answering my own (and first) question!

With help from Sean Gallardy, using EXTENDED_LOGICAL_CHECKS pointed me to inconsistencies with the persisted computed column's data. I first tried recreating the primary key (clustered index), but this didn't actually fix anything (an extreme and weird solution).

I also tried using DBCC CHECKTABLE ('{table}', REPAIR_ALLOW_DATA_LOSS), but the same errors were returned again. I don't believe anything was repaired.

My first solution was to drop and recreate the persisted computed column. This worked.

But, thinking that was somewhat "extreme" as well (considering the table has 10M+ rows and it takes forever to do this because there is also an index against this column), I was curious about a more direct solution.

Since there were only two rows reported by DBCC CHECKTABLE with their page and row references I looked into DBCC PAGE. Using this command with precision got me the actual row data/row identity and when I executed the CLR method that drives the computed column, there was indeed an inconsistency.

Basic actual and inconsistent table data:

OrderLineId  QtyOrd  QtyCancl  QtyBack  QtyPick  QtyPack  QtyShip  FulfillId
    1234567       4        12        0        0        0        0          0
    4567890       4        12        0        0        0        0          0

FulfillId in this case should be 1 (representing cancelled), but these two table rows indicate 0 (open/unfulfilled) for both rows.

I ran the actual row data through the CLR function using a SELECT statement:

select
  ol.OrderLineId,
  ol.QtyOrd, ol.QtyCancl, ol.QtyBack, ol.QtyPick, ol.QtyPack, ol.QtyShip,
  ol.FulfillId [CurrentFulfillId],
  CLR(ol.QtyOrd, ol.QtyCancl, ol.QtyBack, ol.QtyPick, ol.QtyPack, ol.QtyShip) [ComputedFulfillId],
from
  OrderLines [ol]
where
  ol.OrderLineId in (1234567, 4567890)

This returned essentially the exact same information, with ComputedFulfillId also resolving to 0 (open). So I retested the CLR code directly in a .Net console application, and given the explicit values that should have been passed in the SQL call, the result was the expected value 1 (cancelled).

I revised the SELECT statement to include a call to the CLR method using explicit constant values instead of the values from the dubious rows:

SELECT ..., CLR(4, 12, 0, 0, 0, 0) [ForcedFulfillId] FROM OrderLines [ol] ...

And the ForcedFulfillId column contained the correct value of 1.

Using DBCC PAGE to find the exact row identities and updating the rows in question forced the computed column data to be recomputed. One final DBCC CHECKTABLE returned no errors and everything was good again.

So rather than drop and recreate the whole computed column, using DBCC PAGE with information from DBCC CHECKTABLE({table}) WITH EXTENDED_LOGICAL_CHECKS allowed me to fix the original "has_unchecked_assembly_data=1" issue causing ALTER ASSEMBLY errors.

WHEW

Sorry for the long-winded explanation - wanted to make sure I documented my travels for the next poor soul.


Full and differential backups are taking way longer only from one server

Resolved. There was antivirus software trying to scan backup file as it was being created. Once sysadmin fixed it, differential backup finished within minutes as opposed to taking 2-3 hours.


OpenWRT doesn't provide anything that I know of anyway to spindown hard drives. From the documentation,

  Unfortunately there is no solution that works always for everybody. The first option to try is provided by the package hd-idle, which will spin down any disk accessible via the SCSI layer. Install it and test if it can spindown the hard disk (let us assume that your disk is /dev/sda):

The other solution is hdparm -y. So what you're doing is using something to tell your drives when to spin down. The idea is you now either

Script a daemon to monitor for when use stops (such as in the case of unmount),
Periodically tell the hard drive to spin itself down and hope it doesn't wake.

If PostgreSQL wakes the drive up, that's absolutely to be expected. Like any decent database, there is always maintenance to be done, and even when you're not accessing that database it will autovacuum. You may consider

Disabling autovacuum (which will help). But, then you'll have to manually VACUUM. Even disabling autovacuum, I have no idea if the PostgreSQL daemon will wake back up the drive.
Shutting down PostgreSQL before you set the hard drives to spindown.


Strange Issue with Linked server and using IF exists

The whole query is validated by the engine before it runs, so the IF statement cannot prevent that message for a table that does not exist.

You could use dynamic sql inside the IF.

if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name = 'MonthlyReportData')
begin
  exec('Insert into [ServerB].[DB_B].dbo.Monthlydata
    select  *,getdate() from
    [ServerA].[DB_A].dbo.monthlydata')
end

After clarification and full sample

Declare @sql varchar(4000)
Declare @dblocation varchar(200)
declare @dbname varchar(200)

Declare Getdata cursor for
select  dblocation,id
from
temp

open Getdata
fetch next from Getdata into @dblocation,@dbname

while @@FETCH_STATUS=0
begin
if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name = 'MonthlyReportData')
begin
 set @Sql='

 Insert into [serverb].[db_a].dbo.data

 select  *,getdate() from '
 +quotename(@dblocation)+'.'+quotename(cast(@dbname as
 varchar(20)))+'.dbo.MonthlyReportData
 end'

 exec(@sql)
end

fetch next from Getdata into @dblocation,@dbname
end

close Getdata
deallocate Getdata


Find user who kicked off a SQL Agent job at runtime

This seems to be available in the msdb.dbo.sysjobhistory table under the message column.

We can use some code to extract the text based on the "Executed as user" text:

SELECT          [j].[name] AS [JobName],
                CASE
                    WHEN LEFT([h].[message], 17) = 'Executed as user:' THEN
                        SUBSTRING([h].[message], 19, CHARINDEX('.', [h].[message], 1) - 18)
                    WHEN [h].[message] LIKE '%invoked by%' THEN
                        SUBSTRING(
                                     [h].[message],
                                     CHARINDEX('invoked by ', [h].[message]) + 11,
                                     CHARINDEX(
                                                  '.',
                                                  SUBSTRING(
                                                   [h].[message],
                                                   CHARINDEX('invoked by ', [h].[message]) + 11,
                                                   99
                                                   )
                                              ) - 1
                                 )
                    ELSE
                        [h].[message]
                END,
                [h].[message]
FROM            [msdb].[dbo].[sysjobs]       AS [j]
    INNER JOIN  [msdb].[dbo].[sysjobhistory] AS [h]
                ON
                [j].[job_id] = [h].[job_id]
WHERE           [j].[enabled] = 1   --Only Enabled Jobs
ORDER BY        [JobName];


sysarticles system view - what does a status of 57 mean?

I'm not sure what that status value means, but most of my articles are listed with status 57 and they are working just fine.

I'm guessing that there's a bitwise value of 32 which is missing (why else go from 16 to 64) and I'm guessing that 24 should actually be 32 as 24 isn't a valid bitwise value.

57 = 1+8+16+32

I'll see if I can get it confirmed that the 24 should actually be 32.


Services don't have MySQL Server. How to run it?

You may have not installed MySQL using the wizard or skipped that part.

You have full installation instructions in order to add MySQL server as a Windows service on the reference manual, basically:

Ensure that MySQL is stopped (mysqladmin shutdown)
Install the server as a service (mysqld --install), optionally setting a name for it

More details on the aforementioned manual page.