UPDATE STATISTICS options fail in Amazon RDS SQL Server

It is true that either sysadmin OR db_owner privileges are required to use those options, per SQL code.  We know we do not have sysadmin privileges.  For internal tables, sysadmin permissions would be required.  Since temp tables are not internal, this does not apply.  So that leaves one possibility - we must have db_owner privileges.  Do we?  In the SQL Code, it is being evaluated not at the current DB context, but of the database of the object we are attempting to alter:  TempDB.

Now that we know why this fails, we can evaluate possible workarounds.
1. If Amazon RDS grants privileges to modify the tempdb database roles, adding the user to db_owner would do the trick.  I haven't tested this, but based on this article believe it is likely doable.  It would be the easiest and perhaps most elegant solution.
2. Populate the temp table with junk that matches the stats profile you're targeting, update stats with NORECOMPUTE, then truncate and populate/use it in the way originally intended.

PROOF:
Login with sysadmin; verify

SELECT SUSER_SNAME() [CurrentLogin], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()) GO

Create test database and use it

CREATE DATABASE [Test] GO USE [Test] GO

Confirm that this issue doesn't happen with sysadmin

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t; GO CREATE TABLE #t (id INT NOT NULL; GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO

Create a user that doesn't have sysadmin privileges and use it

CREATE LOGIN [UpdateStatsLogin] WITH PASSWORD = 'DontPeek!'
CREATE USER [UpdateStatsUsr] FROM LOGIN [UpdateStatsLogin]
SETUSER 'UpdateStatsUsr' GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME())

Repro the error

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t GO CREATE TABLE #t (id INT NOT NULL); GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO

Demonstrate that db_owner of the current database doesn't work

SETUSER GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO ALTER ROLE [db_owner] ADD MEMBER [updatestatsusr] GO SETUSER 'updatestatsusr' GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO

Repro the error

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t GO CREATE TABLE #t (id INT NOT NULL); GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO

Now add to db_owner in tempdb switch back to sysadmin & verify

SETUSER GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO USE TEMPDB GO CREATE USER [updatestatsusr] FROM LOGIN [updatestatslogin] GO ALTER ROLE [db_owner] ADD MEMBER [updatestatsusr] GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO

And watch the error disappear

USE [Test] GO SETUSER 'updatestatsusr' GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t GO CREATE TABLE #t (id INT NOT NULL); GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO

Remove from db_owner in Test to show that it's tempdb that matters

SETUSER GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO ALTER ROLE [db_owner] DROP MEMBER [UpdateStatsUsr] GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t GO CREATE TABLE #t (id INT NOT NULL); GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO


First of all are the containers visible to one another i.e. can you ping the mysql box from the webserver?

If not it is a visibility issue, try creating a docker network to put the two containers within.

First create a docker network:

docker network create lamp-network

You can see this by the following command:

docker network ls

Now start your two containers with the additional flag:

--net lamp-network

Now try pinging and seeing if you have connection, if so then MySQL should work, although depending on setup you may have to comment out the bind-address from the config.


How to do partition on a non clustered index column?

Strictly speaking creating a non-clustered index on a partitioning scheme is done exactly as the clustered index, much as Thomas already showed you:

CREATE NONCLUSTERED INDEX <indexname> ON <tablename>
(<columns>)
INCLUDE (<columns>)
ON <partitioning scheme>;

Having a non-aligned non-clustered index is usually not recommend, see Special Guidelines for Partitioned Indexes:

  Memory limitations can affect the performance or ability of SQL Server to build a partitioned index. This is especially the case when the index is not aligned with its base table or is not aligned with its clustered index, if the table already has a clustered index applied to it.

Similar memory problems also manifest in queries, not only during build, specially if you use versions prior to SQL Server 2012.

But the real problem is the futility of partitioning a non-clustered index by a boolean. Aside from the general issues with misunderstanding partitioning and abusing this very expensive and invasive feature (see How To Decide if You Should Use Table Partitioning), partitioning on a boolean is non-sense, especially on a column that appear to be very volatile (changes often) and highly skewed (far more 0s that 1s). Filtered indexes are probably what you're after. See Filtered Indexes vs. Table Partitioning.

And, finally, having an 'isLocked' column is never a sign of healthy data modeling. In fact a big code smell. You probably need to read about application locks and/or using tables as queues.


Is there a way to stop MySQL Replication on the master?

There does not exist a STOP MASTER; command nor is there a manual mechanism from the Master to stop Replication. You would have to go to each Slave and run on of the following:

STOP SLAVE; (Kills the IO Thread and SQL Thread)
STOP SLAVE IO_THREAD; (Kills the IO Thread only)

Running either of these will get you the following:

Clean Recording of the Replication Coordinates in master.info

Master_Log_File
Read_Master_Log_Pos
Relay_Master_Log_file
Exec_Master_Log_Pos

The IO Thread is what communicates with the Master. Killing the IO Thread on the Master Side using the KILL command would abort the IO Thread on each Slave. That could corrupt the Clean Recording of Replication Coordinates.


All the information you need is in the catalog, have a look at the tables:

information_schema.table_constraints
information_schema.key_column_usage
information_schema.constraint_column_usage

However, if you want something more visual have a look at:

http://schemaspy.sourceforge.net/

It will reverse engineer your schema and produce html output


Mariadb/docker error : Can't init tc log

I ended up deleting the tc.log file in /var/lib/mysql. When I started mysql again, it created a new tc.log and started up.

sudo rm -f /var/lib/mysql/tc.log


SQL Server: trigger for insert update and delete caught when no data been changed?

You are relying on the presence of rows in either inserted or deleted (or both). What happens when no rows are affected? The trigger still fires.

CREATE TABLE dbo.floob(a int);
INSERT dbo.floob(a) VALUES(1);
GO

CREATE TRIGGER dbo.TRfloob
ON dbo.floob
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  IF    NOT EXISTS (SELECT 1 FROM inserted)
    AND NOT EXISTS (SELECT 1 FROM deleted)
  BEGIN
    PRINT 'Strange, unknown operation!';
  END
END
GO

UPDATE dbo.floob SET a = 2 WHERE a = 2;
GO

To avoid this kind of thing, typically people start their trigger with something like:

IF     NOT EXISTS (SELECT 1 FROM inserted)
   AND NOT EXISTS (SELECT 1 FROM deleted)
BEGIN
  RETURN;
END

Checking @@ROWCOUNT is popular too, but I find it far more brittle.


Invalid data types in restore headeronly resultset

First your first questions

I would use tinyint for the BYTE(1) in this case they told us the possible values are 1 or 0. BIT may also work. You could also try BIT. But uint64 is an unsigned 64 Byte integer. BIGINT is signed, so the max value is lower. So technically speaking a DECIMAL(20,0) or greater precision would be used here. But in later versions of that same article this is a BIGINT (For SQL Server 2008 R2 and SQL Server 2012) so I am sure you are fine with BIGINT here. If you get enough disk space and time to create a database big enough to compress to a value that blows BIGINT you can test this theory out someday ;-)
No undesired behavior if you go with SMALLINT/BIGINT/DECIMAL(20,0)
I am not sure I understand your question, but I believe the answer is conversion if you are asking what I think you are asking but this is potentially just an oops
I'm not sure why those datatypes are in the documentation but you've chosen good logical approximations.

Then the last question

I hate to shove off on this one, but I'm kind of going to do that. There are a lot of great restore scripts out there on the internet for different scenarios. You haven't fully described yours so not sure I can comment on the efficiency/elegance but you are right to read the headers to determine what you do next. Some questions to ask yourself:

Are you looking at things like the date to ensure you restore the latest? Are you looking at things like full/diff/log backups and accounting for them in the restore? What purpose is this for? Restoring a dev environment? Or for a production restore? If a dev restore, I like to go more automated. If a prod restore I like to have a script that eliminates some "oops" factor from a critical production restore but not automate so much of it that it makes it easy to forget to do a critical step or do something like backup the tail of the log. I'd search for restore scripts and see what others have done, ask yourself these questions and incorporate what you like.

I also am not sure you need to know if the file is compressed or what the compressed size is. Those facts shouldn't be terribly necessary for a restore script since SQL just handles the restore of a compressed backup for you. You don't have to tell SQL it is compressed. So you may just drop those columns altogether and only take what you require from the header to perform you restore.


MS SQL 2008 Databse in Suspect mode and CHECKDB returns error: Object ID 3 has chain linkage mismatch

The repair is unrepairable (linkage issue in system table), so you would need to restore it from a good known backup to a point in time (provided the DB is in full recovery and you were taking log backups) you did the disk expansion.

You can try repair allow data loss as last resort.


Create a grouping based on chains of pairs

That's a complicated puzzle.  I've found a solution that starts with a list of all identifiers.  Then for each identifier, it creates an array of identifiers that it is paired with.  It iteratively expands that array (called "members") until all pairs are included.

; with  recursive list as
        (
        select  id1 as id
        from    pairs
        union
        select  id2
        from    pairs
        )
,       cte as
        (
        select  id
        ,       ARRAY[id] as members
        from    list
        union all
        select  cte.id
        ,       members || ARRAY[p.id1, p.id2]
        from    cte
        join    pairs p
        on      (
                    cte.members @> ARRAY[p.id1]
                    and not cte.members @> ARRAY[p.id2]
                )
                or
                (
                    not cte.members @> ARRAY[p.id1]
                    and cte.members @> ARRAY[p.id2]
                )
        )
select  id
,       min(v) as min_id
from    cte
cross join lateral
        unnest(cte.members) v
group by
        id
order by
        id
;

Example at SQL Fiddle.  Hopefully someone else can contribute a simpler, more elegant, or better performing solution!


What could possibly cause a numeric overflow when enqueuing an Oracle AQ message?

Turns out this problem has nothing to do with message properties, and perhaps not even with the messages themselves. The cause here is actually a bug in Oracle, which you hit when the value of

select max(greatest(object_id, data_object_id)) from dba_objects;

reaches 2^31. There is actually a corresponding patch available from Oracle:

Patch 14837395: AQ CALLS FAIL WITH NUMERIC OVERFLOW WHEN DB OBJECT NUMBERS GREATER THAN 2^31


Transaction log is full (due to NOTHING)... but this database is in simple recovery mode

Run the below script to check the size, max size and growth increments of your log files.

SELECT
    @@SERVERNAME AS [Server],
    db.name AS [Database],
    mf.name AS [File],
    CASE mf.[type_desc]
        WHEN 'ROWS' THEN 'Data File'
        WHEN 'LOG' THEN 'Log File'
    END AS [FileType],
    CAST(mf.[size] AS BIGINT)*8/1024 AS [SizeMB],
    CASE
        WHEN mf.[max_size] = -1 THEN 'Unlimited'
        WHEN mf.[max_size] = 268435456 THEN 'Unlimited'
        ELSE CAST(mf.[max_size]*8/1024 AS NVARCHAR(25)) + ' MB'
    END AS [MaxSize],
    CASE [is_percent_growth]
        WHEN 0 THEN CONVERT(VARCHAR(6), CAST(mf.growth*8/1024 AS BIGINT)) + ' MB'
        WHEN 1 THEN CONVERT(VARCHAR(6), CAST(mf.growth AS BIGINT)) + '%'
    END AS [GrowthIncrement]
FROM sys.databases db
LEFT JOIN sys.master_files mf ON mf.database_id = db.database_id

If your max and current size are equal this could be causing your issue. If so, try this command:

ALTER DATABASE [db_name] MODIFY FILE (NAME = 'file name', MAXSIZE = <new max size> GB);


How to do failover between AG setup for FCI-FCI servers

When you said it needs to be set as "async" as per limitation, are you talking about SQL limitation or limitation on your side because of, for example, network latency ?
I'm pretty sure you can build you AG in Sync mode if you want so, I will presume it's limitation on your side.
To answer your questions:
If the primary node for SQL1\inst1 goes down, the cluster will failover to the other node. (just like regular FCI) - Same thing for you second instance in DC2
As you AG will be in Async, the best approch to plan a failover from your DC1 to your DC2 without any data loss would be to
1- Have a maintenance windows where there will not be too much activity
2- Switch your AG to Sync mode and let it sync
3- Once sync (you can check dmv to make sure they are sync), trigger a failover within SQL (Using the Gui in SSMS or TSQL)
4- Once the failover is completed, set the AG back to Async
Note that in Async, you cannot enable Automatic failover so if ever your FCI from DC1 fail (problem with the shared storage for example) while being primary, it won't failover to your DC2 and manually failover to it may cause data loss.


The single best thing you can do to reduce disk I/O load is to ensure that your database fits comfortably in RAM after allocating RAM to everything else running on the system.  If this is the case, your disk I/O will be primarily WAL flushes on commit, which is mostly sequential I/O.

The second thing you can do is address the shared_buffers and effective_cache_size settings.  If the system is exclusively running PostgreSQL I like to set shared_buffers low, and effective_cache_size high and adjust from there.  If the system is running other programs too, shared_buffers should be set much higher because it reserves RAM exclusively for PostgreSQL.  Note that the OS disk cache is often faster than the PostgreSQL cache, but the Pg cache is more full-featured so there is a real balance.  Measure, test, repeat.

A third setting to be aware of is work_mem.  This setting should be adjusted only with care, because it is per operation.  If you set it too low, joins and sorts will write to disk.  If you set it too high, it will squeeze out memory that could be used for caching data.  Again, look for query performance patterns and move forward.


Session in a limbo state - Killed/Rollback

You could try to kill the windows process that was spawned with xp_cmdshell as that process is probably running and not handled by killing the spid from SQL Server. The way you describe it I would guess the ftp program is still running.

This is because the kill command doesn't kill programs that were started out of process.

You could simulate the scenario like this:

In a SQL Query window start a process:

xp_cmdshell 'notepad.exe'

and then after killing that spid I get the same output in sp_who2 as you

54      RUNNABLE                        Administrator   hostname      . master  KILLED/ROLLBACK     0   0   07/24 14:23:02  Microsoft SQL Server Management Studio - Query  54      0

But I still have a running notepad.exe process:

C:\Users\Administrator>tasklist | find "notepad"
notepad.exe                   4676 Services                   0      3 788 K

Once I kill that notepad.exe process my SQL Server spid is gone too.

If you want to find out what process you need to kill process explorer should show it as a child process of sqlserver.exe like this (you might need to start procexp.exe as administrator):


How to send parameters to BCP inside dynamic sql using sp_executesql

I think you'll have to externalize those parameters when generating the dynamic SQL.  Something like:

DECLARE @REPORTID Nvarchar(500) = 'SomeID_Resend2019-02-20 11:38:24.040';
DECLARE @Path varchar(500) = '\\server\folder\_FileName.csv';

DECLARE @SQL nvarchar(500);

SET @SQL
    = N'master.dbo.xp_cmdshell ''BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = ''''' + @REPORTID + '" queryout ' + @Path + ' -t, -T -N -S SERVERNAME\INSTANCENAME''';
PRINT @SQL
--EXEC sp_executesql @SQL

The PRINT statement reveals the generated dynamic SQL

master.dbo.xp_cmdshell 'BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = ''SomeID_Resend2019-02-20 11:38:24.040" queryout \\server\folder\_FileName.csv -t, -T -N -S SERVERNAME\INSTANCENAME'


join table and count them SQL based on segment

SELECT
    Question,
    SUM(
        CASE  --Case for each column
            WHEN h."Children 0-5" = 'Children 0-5' THEN 1
            Else 0
        END) as "Children 0-5",
    SUM(
        CASE
           WHEN h."Children 6-12" = 'Children 0-5' THEN 1
           Else 0
        END as "Children 0-5"* from household h
 INNER JOIN survey s
 ON h.user = s.user
 GROUP BY s.question, answer

Ok doing this from my brain rather than testing but here is the idea.  Use case statements to turn your string data into a sum-able number.  Then group by the question to aggregate the answers.

If I were you (and you could change the data) I would get away from verbose string fields and make things like the age groups Y, N or even better 1, 0.


Postgres primary key sorted in the reverse order. Will it use the index efficiently?

From Postgres documentation: Chapter 11. Indexes (note that the same holds even for really old versions like 8.3 chapter 11. Indexes):

  By default, B-tree indexes store their entries in ascending order with nulls last. This means that a forward scan of an index on a column x produces output satisfying ORDER BY x (or more verbosely, ORDER BY x ASC NULLS LAST). The index can also be scanned backward, producing output satisfying ORDER BY x DESC (or more verbosely, ORDER BY x DESC NULLS FIRST, since NULLS FIRST is the default for ORDER BY DESC).

Now, whether this index will be used for a particular statement, it really depends on the statement. For a query that returns the whole table or a large part of it:

SELECT *
FROM tablex
ORDER BY tablexID DESC ;

it will have to scan the whole table anyway, so the optimizer may decide that it's cheaper to just read the whole table and then sort it in descending order (and not use this index).

If it's a query like this though, I would bet that it would use only the index (because it doesn't need any other data but the IDs that are stored in the index):

SELECT tablexID
FROM tablex
WHERE tablexID <= 5000
ORDER BY tablexID DESC ;

So, whether an index will be used or not, really depends on the statement that you are executing, the cache settings, the types of joins, the conditions you have, all the indices that are available/relative, the statistics of the tables and indices (sizes, distribution, cardinality, etc), the version of Postgres (different version means different optimizations), the phase of the moon and possibly a lot of other factors I'm forgetting.

To check which indices, if any, will be used for particular statement at a particular time, you can see its execution plan using EXPLAIN:


Slave Missing Data From Master, Manual Insertion Issues?

My recommendations would be to invest some time in learning how to use the pt-table-checksum and pt-table-sync tools from the Percona toolkit. This takes much of the work out of your hands in a much more reliable way.

https://www.percona.com/downloads/percona-toolkit/LATEST/


How do I start over? (newbie goof: now I need to start fresh with MySQL)

See the manual.  There is a 'simple' procedure for starting mysqld with --skip-grant-tables.  This lets you in.  Change the root password once you are in.


Will this RedGate backup plan interfere with a new differential backup plan I want to create?

If I setup a differential backup plan and someone comes along and
  manually creates a backup without specifying COPY_ONLY then it's going
  to throw the plan off, right?

Yes, your next differential backup will have a differential base LSN of the full backup the other person performed so your restore chain would require that backup.

  Secondly, we currently have a RedGate backup solution in place that
  uses the code below. I'm not familiar with how the master..sqlbackup
  proc it uses works so I am wondering if it's possible for me to leave
  this in place if I setup a differential plan alongside it or if I
  would need to disable this.

If you add a differential backup in parallel to this, your differentials will still require the RedGate backups in order to restore the DIFF because the RG backups will reset the differential base LSN.

  Why would I do this? Well, we're migrating to the cloud and I'd like
  to setup differential backups for the purpose of minimizing downtime
  as we move the database. But I'd just as soon not mess with the
  existing backup plan if I don't have to. And if I have to then I have
  to, but would rather not just in case something goes awry with my
  backup plan in the process.

  EDIT: And I can already imagine someone might ask why I don't just use
  RedGate to do the diff backup plan. First, I have no experience with
  it and second we won't have RedGate on the new server.

If you don't have RedGate on the new server, how are you planning to restore FULL the backups taken by RedGate in order to restore your differentials? My understanding is that restoring a backup taken by RedGate requires RedGate to be installed. I could be wrong (limited experience with RG) but this is how most third-party backup tools for SQL Server work, usually because of custom encryption or compression that is applied to the backups.

Without additional info available, I would suggest one of the following:

Replace RG on the existing server now with whatever you're planning to use in Azure as an end-state (unless you're using Managed Backups in Azure).
Look at Log Shipping to keep your Azure server in sync with on-premise until you're ready to cut over. You would have to disable any RedGate log backups that are in place.


IMPORT TABLESPACE is hanging in the 'System lock' state

I think your table must be very big. I have recently encountered the same problem when I trying to import a 700G table.

Check the error log, I found that the import is in progress stage 1

InnoDB: Phase I - Update all pages

Check the official documentation.
https://dev.mysql.com/doc/refman/8.0/en/tablespace-copying.html

When ALTER TABLE ... IMPORT TABLESPACE is run on the destination instance, the import algorithm performs the following operations for each tablespace being imported:

Each tablespace page is checked for corruption.
The space ID and log sequence numbers (LSNs) on each page are updated
Flags are validated and LSN updated for the header page.
Btree pages are updated.
The page state is set to dirty so that it is written to disk.

I think it takes a lot of time to update the LSN of each page.

check the MySQL work log
https://dev.mysql.com/worklog/task/?id=5522

Import algorithm
================
We scan the blocks in extents and modify individual blocks rather than using
logical index structure.

foreach page in tablespace {
  1. Check each page for corruption.

  2. Update the space id and LSN on every page  --I think this is what "InnoDB: Phase I - Update all pages" does
     * For the header page
       - Validate the flags
       - Update the LSN

  3. On Btree pages
     * Set the index id
     * Update the max trx id
     * In a cluster index, update the system columns
     * In a cluster index, update the BLOB ptr, set the space id
     * Purge delete marked records, but only if they can be easily
       removed from the page
     * Keep a counter of number of rows, ie. non-delete-marked rows
     * Keep a counter of number of delete marked rows
     * Keep a counter of number of purge failure
     * If a page is stamped with an index id that isn't in the .cfg file
       we assume it is deleted and the page can be ignored.
     * We can't tell free pages from allocated paes (for now). Therefore
       the assumption is that the free pages are either empty or are logically
       consistent. TODO: Cache the extent bitmap and check free pages.

   4. Set the page state to dirty so that it will be written to disk.
}


Select parent with a specific child with ordering by a column with no grouping by this column

You concern yourself for nothing. "it's a bottleneck since of each row processing" is an assumption you make, and not necessarily what the optimizer will choose to do, even if it looks to you that you write it that way.

SQL is a declarative language, and as such you tell the engine what you want done, and not how you want it done, like you do in imperative languages.

The query optimizer, especially in SQL Server, is a mind blowing wizard in moving things around, considering alternatives, and understanding what it is that you want, regardless of how you write it. It doesn't get it 100% right, but it is damn good.

Write your query, use several syntax like you did is never a bad idea, and test it against a representative data set. If you see performance challenges, examine the execution plan, or post it here so people can help you.

HTH


Security implications of guest account and database chaining on sql server?

It does not open up directly to an "attack".
It just means that any user from Database 1 (Kdb) can also access database 2 (Ydb).
What's usually more critical is, when you have users with DDL-Permissions (create views, procedures) - they will also be able to access objects in database2. Maybe even more, than plain guests can. That depends on the object owners and permissions you are planning to grant to guest(s).

Under SQL Server before 2012, there was one little security hole, described here:
Security-issue: guest-guest impersonation, but this is a very limited scenario


I found the answer myself after reading through loads of documentation from Oracle. The answer comes in two parts.

First, my original question can be solved by using XMLTable:

SELECT
  x.ID            AS "MEASUREMENT_ID",
  x.GUID          AS "MEASUREMENT_GUID",
  a.COLUMN_VALUE  AS "MESSAGE"
FROM SPERF_XML_RAW x,
  XMLTable('/PerfInfo/EventLog/EventRecord/@Provider'
            PASSING x.DATA_XML) a;

Note that COLUMN_VALUE is a pseudocolumn that can be used in conjunction with XMLTable

As additional information I would like to add the following. You can create multiple columns with the following syntax:

SELECT
    x.ID,
    x.GUID,
    res."Provider",
    res."TimeCreated"
FROM SPERF_XML_RAW x,
    XMLTABLE('/PerfInfo'
      PASSING x.DATA_XML
      COLUMNS
         "Provider"     VARCHAR2(32 CHAR)   PATH '/PerfInfo/EventLog/EventRecord',
         "TimeCreated"  TIMESTAMP           PATH '/PerfInfo/EventLog/EventRecord') res;

But Oracle will throw ORA-19279: XPTY0004 when one or more columns return multiple nodes.

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
19279. 00000 -  "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"
*Cause:    The XQuery sequence passed in had more than one item.
*Action:   Correct the XQuery expression to return a single item sequence.

The solution to THAT problem can be found below:

SELECT
  x.ID              AS "MEASUREMENT_ID",
  x.GUID            AS "MEASUREMENT_GUID",
  res."TIMECREATED",
  res."LOGNAME",
  res."LEVEL",
  res."ID",
  res."PROVIDER",
  res."MESSAGE"
FROM SPERF_XML_RAW x,
   XMLTable('/PerfInfo'
      PASSING x.DATA_XML
      COLUMNS
        "EventRecords" XMLTYPE PATH 'EventLog') rec,
   XMLTABLE ('EventLog/EventRecord'
       PASSING rec."EventRecords"
       COLUMNS
        "LOGNAME"         VARCHAR(32 CHAR)      PATH '@LogName',
        "ID"              NUMBER                PATH '@ID',
        "LEVEL"           CHAR(1 CHAR)          PATH '@Level',
        "PROVIDER"        VARCHAR2(128 CHAR)    PATH '@Provider',
        "TIMECREATED"     VARCHAR2(32 CHAR)     PATH '@TimeCreated',
        "MESSAGE"         CLOB                  PATH 'text()') res;

This will probably fail once more as soon as your document has too many levels, and I have not included a document with namespaces, which will most likely cause this to explode as well.

All information was found in the XML DB Developers guide: https://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb03usg.htm#g1055369


I don't know how to read many to many relationships

The many-to-many nature of the relationship is expressed in the Physical diagram by breaking the relationship down into a pair of many-to-one relationships.  A many-to-one relationship can be implemented using a foreign key that references a primary key.

Project-Employee contains a foreign key (presumably EmployeeID) that references the primary key of the Employee table (presumably ID).  That is why there is only one Employee allowed on that end on the upper line.

Note in passing that if you were to implement using some kind of non-relational DB, the diagram on the left would be more useful than the one on the right.

Also note in passing that, in the classical literature on DB design,  the one on the left would have been called the "Conceptual Model" and the one on the right would have been called the "Logical Model".  The physical model would have included DBMS specific features like tablespaces, indexes, and clusters.  Times change, and terminology changes in ways that can be bewildering to those who learned it the original way.


Storing repetetive file metadata in databse

I will be storing this kind of data for many different directory structures that very often have the same file metadata. I do not want to have many repeated entries in the database, but I also do not want to have a 5 column Primary key.

In order to normalize, you need to do is accommodate for LVM, and partition information. Then you should only have to keep inode, and meta data, (stat and lsattr if you want to be super anal). You may or may not want emulate hierarchy in path. We do in this example. The repetitive meta-data isn't an issue.

CREATE TABLE physical_volume (
  pv_id      int PRIMARY KEY,
  hw_uud     uuid
);

CREATE TABLE volume_group (
  vg_id int PRIMARY KEY
);

CREATE TABLE pv_vg (
  pv_id int REFERENCES physical_volume,
  vg_id int REFERENCES volume_group,
);

CREATE TABLE logical_volume (
  lv_id int PRIMARY KEY,
  name  text
);

CREATE TABLE partition (
  partition_id int PRIMARY KEY
  fs           text,
  mount_attr   jsonb,
);

CREATE TABLE files (
  file_id       int PRIMARY KEY
  partition_id  int REFERENCES partition
  inode         int,
  .. repetitive metadata
  UNIQUE (partition_id, inode)
);

CREATE TABLE path (
  path_id            int PRIMARY KEY,
  parent_path        int REFERENCES path
  file_id            int REFERENCES files
  name               text
);


The solution I'm moving ahead with is to have an smallint[] which esentially gives me a total of 65k unique values / bits. I will then store the index of the bits that are 'on'. This won't be very efficent in terms of storage if densely populated. I could use int4 for higher indexes but this would consume even more space.
I can query it for off bits using @> operator.
I should be able to index this with a GIN index.


Grant a login of Sql server authentication the same permission of a Windows group login with Windows authentication?

At the database level, you could simply add both to the same database role. Of course, when you create the role, you'll have to grant it all the same permissions as the existing group, but you only have to do that once.

At the server level, to do that without re-applying all of the permissions (which you could probably script without a whole lot of effort - you can get some ideas here), you'll have to wait for SQL Server 2012, unless you can use a fixed server role.


I'll answer your question in an orthodox manner, with a twist of heresy:
Orthodoxy: You shouldn't store data in a column in a relational database that isn't "atomic."
Heresy: In your specific situation, you could -- maybe -- consider this blob of JSON to be an atomic object.
Years ago, Chris Date said it like this:

"A relation is said to be in first normal form (abbreviated 1NF) if and only if it satisfies the condition that it contains scalar values only"
 Date, C.J. An Introduction to Database Systems, 6th edition (Addison-Wesley, 1995)

Later, he took a somewhat softer stance:

"1NF just means each tuple in the relation contains exactly one value, of the appropriate type, for each attribute. Observe in particular that 1NF places no limitations on what those attribute types are allowed to be."
Date, C. J. Database Design and Relational Theory: Normal Forms and All That Jazz (OReilly Media, 2012)

The "exactly one value" I'm arguing for here is "exactly one JSON object" (which could, in turn contain a JSON array).
Storing things in JSON in a column is a bad idea if you need to the DBMS to manipulate it in any way, since, of course, it can't be properly indexed like properly normalized data can be... but, arguably, if you really really really don't intend for the DBMS to do anything with what you're storing other than write and read it, the case could be made to store a chunk of JSON in a single column, claiming the JSON array of values to be a single atomic value.
The big objection, I think, to doing this, is when it's done out of a lack of familiarity with the relational model or out of laziness or naivete.  Obviously, there are a lot of ways it could be done wrong, but I'd suggest that there's nothing inherently wrong about storing a chunk of JSON in a database, As Long As You Know What You're Doing.™
And, of course, you could use a MySQL FULLTEXT index on it, now that those are supported in InnoDB as of (MySQL 5.6).


What happens when an auto grow event is triggered to the database?

Yes, autogrow is expensive, and yes, many transactions will need to wait for it to complete. Search this site and you will see many experts advising to avoid it and many ways to minimize the impact.

The only cons for not using autogrow and instead pre-sizing the file to fill the disk:

you can't lease out that space in the meantime (if you know the database will grow, why are you waiting to fill the space?)
if you have to grow again, you'll be stuck (but you'd be the same stuck as if you grew incrementally, you just wouldn't get any warning)

I would suggest pre-sizing your files in such a way that there is room for one or two autogrow events before they fill the disk (or exceed quota or what have you). This way you can set up alerting on those events and potentially react in time to prevent downtime. Users shouldn't be waiting for autogrow events unless you are responding to an emergency (and you should obviously avoid those).


Data Collector Component Name Doesn't Change After Updating SQL Server Name

I cloned an MS SQL server, and changed the name of the machine and SQL
  server, but the old name continues to show up as a component name in
  the disk usage data collector, which I believe is what's causing that
  job to fail

  However, SELECT @@SERVERNAME brings up a NULL value, despite
  restarting the server.

  How do I change the component name?

Give this a shot since you state SELECT @@SERVERNAME brings up a NULL value, despite restarting the server.

1. Run the below with the local as the second argument. . .

TSQL

SP_DROPSERVER
GO
SP_ADDSERVER 'NewServerName', local
GO

2. Restart SQL Server (if you can restart the server OS meaning power cycle it)

3. Run the below once it's back up and confirm . . .

TSQL

SELECT @@SERVERNAME
SELECT SERVERPROPERTY('ServerName')

4. If the values are correct, then rerun your SSIS process and see if that resolves it as well.

SP_ADDSERVER

  [ @local = ] 'LOCAL' Specifies that the server that is being added as a local server. @local is varchar(10), with a default of NULL.
  Specifying @local as LOCAL defines @server as the name of the local
  server and causes the @@SERVERNAME function to return the value of
  server.

  SQL Server Setup sets this variable to the computer name during
  installation. By default, the computer name is the way users connect
  to an instance of SQL Server without requiring additional
  configuration.

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


Postgres: "show max_connections;" output a different value from postgresql.conf file

As the current value is stored in postgresql.auto.conf, it was changed using ALTER SYSTEM and thus has priority over any value in postgresql.conf.
You can either use ALTER SYSTEM again to increase the value:
alter system set max_connections = 250;

which will adjust the value stored in postgresql.auto.conf
Or you can remove the entry from postgresql.auto.conf by using:
alter system reset max_connections;

Then the value from postgresql.conf will be used.
In either case, you have to restart Postgres to apply the new value.


The answer to another person's question about gz.cpgz file-types suggests that it may have been compressed twice. Other searches show people having problems opening them but this is down to software not expecting files to be zipped up twice in their cases.

You don't say what operating system you are on but, from what you've posted, I'll take a guess that you have access to the file command to help identify what type file you have.

file database-name.mysql.gz will show that you have a file that can be unzipped using gunzip as you've already done.

Now run file database-name.mysql.gz.cpgz to see what this file's type is. If it comes back as gzip file again then you can gunzip it again.

A MySQL backup is just a text file, so if it reports it as so then you're safe to check it out further. Since it's over 300MB I wouldn't recommend opening it in your text editor. Instead use the head command to check out the top few lines:
head <yourfile.sql>

If you're Windows user 7zip will be able to tell you if the gz.cpgz is an archive or not.


SQL Server query across several linked servers - abysmal performance

The poor performance is because you are using linked server.  There is a constant difference of opinion between developers and database admins.  Database admins know linked server connections are slow.  Developers want them because they solve problems for them.  I have been both a developer and a database administrator and I remember as a developer, I pushed to get linked server added.  As a database admin for the past 20 years, I have trouble shot poor performance thousands of times to find a linked server connection is the cause.

Use transactional replication to get real time data to your report server.  This will eliminate the 24 hour delay and it will eliminate your need to use linked server.  You will also have a performance gain on your source servers because read only report queries won't be competing with live transactions that run your company.


Maintenance Plan fails but the query it generates Runs

The SQL Server Agent account has both share and local security permissions right?

I would also ensure that you have given the SQL Server Agent appropriate network permissions -- it really sounds like a permission issue.


Vendor Wants to Run MSDB job every 5 minutes for Business Application

IMO your vendor is actually on the right track.

An application layer job would require him to redo a lot of out-of-the-box SQL Agent features (eg. logic not to run a job that is already running, come up with a security and credentials store solution, integrate job results with error reporting and results tracking in SQL etc etc etc etc). And, most importantly, provide a backup/HA/DC solution for the scheduling. You do not want your disaster recovery story to be 'and after you finish recovering the standby server go create these 50 NT scheduler tasks'. So he is right in pushing back against using the system scheduler, it has nothing of the features and capabilities the Agent has.

He is even more so right in pushing back against triggers. Replacing a periodic job with a synchronous trigger increases request latency, increases cohesion and coupling (think schema changes...), increases downtime risk due to sync issues (trigger error->app error vs. job error->fix and retry later), dramatically increases deadlock problems (due to cross updates between tracked/trackee) and has many many more problems.

The easiest solution is indeed a Agent job, and msdb is by no means reserved for DBAs. A more fancy solution would be to use conversation timers and internal activation, which would have some advantages over Agent jobs, primarily due to containment (everything is inside the app DB, think mirroring failover scenarios), but I can totally understand if your vendor is reluctant to try out something that requires a very specific know-how. BTW I do hope you don't mean one job every 5 min per DB.

As for sysadmin permissions: the name of the game is code signing. You can give any permission to specific procedures, inspected and validated by you, by signing them with your private certificate.


How to move old data to cheap hard drive

Here is a better architecture:

Create a new tablespace on the slow drive.
Set the storage parameters seq_page_cost and random_page_cost higher on that new tablespace so that the PostgreSQL optimizer knows that the disks are slower.
Partition the big time series tables by time ranges (use the same boundaries for all affected tables) so that you end up with a couple of dozen partitions for each.
Move the old partitions to the slow tablespace.

Then you still have all the data accessible.

Use PostgreSQL v11 or better for partitioning.


"Cluster nodes" command showing different status on different Redis Cluster nodes

It turns out there was a firewall issue on both the servers.
After stopping the firewall(systemctl stop firewalld), the cluster is consistent.


Performance comparison in ways a SSIS package can be run

Performance should be the same between running it as a job, via DTEXEC or BIDS.

If the package is run on the SQL Server is uses SQL Servers's buffer pool as it queries the data, then SSIS will use it's own memory space to do work.

If the package is run on the client machine it'll use the SQL Server's buffer pool and memory on the client machine.

Running packages on other machines is usually slower, especially if there is a lot of data to move as the data has to be moved over the network.


How to create a secondary read-only reporting database with < 15 minute latency

Transactional Replication is typically used to off-load reporting to another server/instance and can be near real-time in a best case scenario. The benefit of Transactional Replication is that you can place different indexes on the subscriber(s) to optimize reporting. You can also choose to replicate only a portion of the data if only a subset is needed for reporting.

With Transactional Replication you will need Standard Edition or higher for the Publisher and Subscribers can be Express Edition or higher.

Have a look at Transactional Replication to get started.

If you have any questions, let me know. I hope this helps.


LC_COLLATE refers to a name of locale from the operating system, whereas COLLATE refers to a collation that should exist in pg_catalog.pg_collation.
pg_catalog.pg_collation is originally populated in the template databases when the PostgreSQL instance is created (by initdb).
Specifically it's the SQL function pg_import_system_collations() that should do that. From the doc (emphasis mine)

pg_import_system_collations ( schema regnamespace ) → integer

Adds collations to the system catalog pg_collation based on all the
locales it finds in the operating system. This is what initdb uses;
see Section 23.2.2 for more details. If additional locales are
installed into the operating system later on, this function can be run
again to add collations for the new locales. Locales that match
existing entries in pg_collation will be skipped. (But collation
objects based on locales that are no longer present in the operating
system are not removed by this function.) The schema parameter would
typically be pg_catalog, but that is not a requirement; the collations
could be installed into some other schema as well. The function
returns the number of new collation objects it created.

For some reason you don't have en-US in pg_collation while it's valid from your operating system, so you seem to be in the case where the advice is to run
pg_import_system_collations() manually in the databases that need it.


Suddenly Slow Execution Plan for Stored Proc

This issue is called parameter sniffing.

Later versions of SQL Server give you more options in dealing with it such as OPTION (RECOMPILE) or OPTIMIZE FOR hints.

You might try declaring variables in the stored procedure, assigning the parameter values to the variables and using the variables in place of the parameters as it sounds as though most of the time you are getting a reasonably satisfactory plan.

Normally the most catastrophically bad plans are those compiled for parameters with very high selectivity but ran with parameters with low selectivity.

Assuming the plan generated is more robust with this approach and satisfactory for all parameter values then the advantage of this approach over that suggested by JNK is that it does not incur a compilation cost for every call.

The disadvantage is that for some executions run time might be greater than for a plan tailored specifically for those parameter values so it is a trade off of compile time vs execution time.


How to model (a many to many) to many relationship

The tables student and major are good.

However following improvements can/should be made.

Merge the FKs in MajorStudent to a PK, getting rid of the id
Save a StudentFK and MajorFK rather than a MajorStudentFK in Exam, well one can discuss about this though but it'll be faster and in my consideration NOT redundant
Grade is not normalized. All attributes are supposed to be atomic and grade is not. Either make 4 columns (bad) or use the following

Exam(id[PK], studentId[FK], MajorId[FK])
Grades(id[PK], examId[FK], value)

I don't understand why one exam comes with four grades though.


My main concern, regardless of the migration mechanism used, would be for the consistency of the migrated data.   How are you going to ensure that you don't miss changes while the data is transferred.

One of the classic Informix tools could be DB-Export, which (by default) locks the database so it isn't being changed while the export occurs.  It gives you the schematic and unloaded (text) data files.

Another option would be to make an archive of the existing system and then restore that into a new (temporary) instance, and you could then run your migration against the temporary instance without affecting the working instance.  But you'd still have to worry about later changes.

If your existing machine is not too woefully under-powered, you should be OK running the export as you suggested, but you still face the issue of ensuring consistency of the migrated data if users are changing the source system while you are migrating.


How to optimize table size by deleting columns or just emptying them

Short answer:  I doubt if it is worth doing either.

Medium answer:  It depends on a lot of things.  You could calculate SUM(LENGTH(col1)) + SUM(LENGTH(COL2)) + ... to get a crude estimate.  But (as the long answer explains) that savings may be an over- or under-estimate in the case of NULLing.  In the case of DROPping, it is likely to be an underestimate of the shrinkage.

Long answer:

(It would help if you had provided SHOW CREATE TABLE.

In MySQL, VARCHAR(512) will take a little bit of space even if empty or NULL.  At most it will take 2050 bytes since it is utf8mb4, but it is unlikely to really be that big.

Since you have a number of large VARCHAR columns, they may be placed in an off-row storage that is handled in a different way.  This depends on how bulky all the columns are and what the ROW_FORMAT is.  I am assuming you are using InnoDB; MyISAM works quite differently.

Because of the way setting a column to NULL works, you may not save as much space as you would calculate.

DROP COLUMN is a drastic approach.  It will take a long time because it will copy the table over and rebuild all the indexes.  But, in doing so, it will defragment both the data and the indexes, squeezing out more space than simply NULLing would do.  The DROP approach might reduce the table size more than you would compute.

But... If you have innodb_file_per_table = 0, no space will be returned to the OS by either method!  It might even increase the space used!  This is because a copy of the table is needed during the DROP.

With innodb_file_per_table = 1, you will recoup the freed space, but only after needing space for a copy of the table.

Why do you want to shrink the table?  My above answers assume you are getting low on disk space.

Note DROP does the same as OPTIMIZE TABLE, with the additional feature of removing the column.

If you are hoping to speed things up by shrinking the table, forget it.


Only Filtered condition gets deleted from index after datatype change using SSMS

When you script the index you need to make sure the scripting option is not set to SQL Server 2005.

  Tools / Options / SQL Server Object Explorer / Scripting / Script for server version

I suspect this is the problem (and it happened to me, too).


PostgreSQL error: "subquery uses ungrouped column"

You could use a subselect:

SELECT q.date,
       q.total_message,
       q.room_id,
       (SELECT seller_id
        FROM chat_room cr
        WHERE cr.id = q.room_id
        LIMIT 1) seller_id,
       (SELECT count(1)
        FROM "order" o
        WHERE o.room_id = q.room_id) total_order,
       (SELECT sum(total_amount)
        FROM "order" o
        WHERE o.room_id = q.room_id
        and DATE(o.created_at) = q.date) total_order_amount
FROM (SELECT DATE(cm.created_at) as "date",
             COUNT(1) as total_message,
             room_id,
      FROM chat_message cm
      GROUP BY DATE(cm.created_at), cm.room_id) AS q
ORDER BY q.date;


Getting the metadata about a Query and a Stored procedure in SQL Server

SET FMTONLY ON can be used to return just column information, no rows will be processed or returned.

  Returns only metadata to the client. Can be used to test the format of
  the response without actually running the query. (BOL)


What is the logic of a query when using like with and/or

Underscore means "a single wildcard character". I'd suggest this is affecting your results. Try doing;

select * from subtitles
where field_name like '%subtitle\_u%' escape '\'
or field_name like '%subtitle\_v%' escape '\'


The alert log will show what user added a data file to your DR tablespace, but most likely they logged on as SYS / AS SYSDBA instead of a user that has SYSDBA privileges (one more reason to assign roles to users, especially DBAs.  The alert log is usually in $ORACLE_BASE/diag/${ORACLE_UNIQ_NAME}/${ORACLE_SID}/trace/alert_${ORACLE_SID} (I may have those ORACLE_UNIQ_NAME and ORACLE_SID backwards).

To clean up the DR TEMP tablespace, just create a new TEMP tablespace TEMP2, alter the database to use the new temporaray tablespace, drop the old TEMP tablespace, then rename the new temp tablespace TEMP2 to TEMP:

create temporary tablespace temp2
  tempfile '/some_dir_path/temp2_01.dbf' size 30g,
  tempfile '/some_dir_path/temp2_02.dbf' size 30g,
  tempfile '/some_dir_path/temp2_03.dbf' size 30g
;

alter database default temporary tablespace temp2;

drop tablespace temp including contents and datafiles;

alter tablespace temp2 rename to temp;


Is Audit plugin available for Community edition?

Based on your error, you just need to grant the insert privilege to the 'root2' user:

GRANT INSERT ON *.* TO 'root2'@'10.80.16.236'

(you'll need to modify this based on your actual 'root2' user)

In terms of whether there is a plugin available, technically the mysql audit log plugin is only for the enterprise edition, and not the community edition.

That said you can usually use the one supplied by Percona or Mariadb with MySQL (there may be others).

I use the Percona one with my 5.5 databases, and the Mariadb one with my 5.7 databases, as I couldn't get the Percona version to work with 5.7.

You install them in exactly the same way. Download them, and place the file in your plugins directory first,then

Percona
    INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Mariadb
    INSTALL PLUGIN audit_log SONAME 'server_audit.so;


SQL Error: Must be GROUPed or used in aggregate function

You use fund_acct_reg_view.fund_acct_no fund_acct, in SELECT but use pr_line_item.fund_acct_no in GROUP BY. Use fund_acct_no from the same table in both cases.


Since you really only have to keep track of times between stations on each route, you only need to keep the start time of each route, the rest can be calculated easily by storing the time delta value for each line stop (the time between the current station and the last station ), instead of keeping time data for each route_stop. You also need to maintain the order of the stops on the line, and if it's a circular route you simply put the stops twice into the chain with different ordering numbers ( so each stop on a circular route is inserted twice into the route_station relation table with different order number ).

You can of course keep the time for each stop if you want, but that seems redundant and makes it harder adding stops to a route, since you'd then have to recalculate all bus stop times, instead of simply adding the new stop and updating the delta time of the next stop.

I'd probably start with a data model like this ( but of course this needs to be expanded if you want to add information about the buses and drivers etc ) :

lines (id, name, ...)

routes (id, name, line_id, ...)

stops (id, location)

line_stops (id, line_id, stop_id, order, time_delta)

route_start_times (id, route_id, start_time)


drop postgresql schema public without losing extensions

I question your desire to routinely drop the public schema, but it should be no problem if you do it like this:

create a new schema extension and put it on the search_path

use CREATE EXTENSION ... SCHEMA extension to create the extension objects in that schema

I personally use the public schema for extensions and put my data in a different schema.


Is there a way to see the query that is running by this Excel connection?

Once you've gotten Excel to finish opening the first time, you can get the query in Excel in a number of ways.  One approach is:

Right-click on any cell in the results
Go to the "Table" sub-menu
Click "Edit Query"

You may also want to go into the "Connection Properties" withing Excel and uncheck the "Refresh data when opening the file" option if you would like to be able to open the file quickly, and then refresh the data on demand.

If you don't want to wait on Excel to finish loading results, you can get the query text using sp_WhoIsActive (more details on that next).

  Is there a way to find out execution status, i.e. what percentage is done?

Not exactly.  You can run sp_WhoIsActive to see information about the running query (including the query text!).

One possible solution to get an "estimated progress" would be to run the query in SSMS after executing the following statement:

SET STATISTICS IO ON;

This will give you the number of reads done by the query for that specific execution.  Next time the query is running, you can compare that to the "reads" column returned by sp_WhoIsActive to see approximately how far along you are.

This isn't an extremely reliable approach if the number of rows returned by the query changes significantly, or if various circumstances cause the execution plan for the query to change.  But if the query is stable, this could give you a general idea of how far a long the query is.


In what order does postgresql handle queries which have conflicting locks?

I can't talk to postgresql directly. However, most rdbms in this situation would process the requests in the order they were received. That means a query can only execute if there is no other query that was submitted earlier and is either holding or waiting for a lock that is not compatible with the newly requested lock.

The reason is, that otherwise a query requesting an exclusive lock might never get executed ("starvation") if there is a high enough number/frequency of read requests coming in that keep sneaking by the waiting query.


Postgres functional indexes that returns an array and used in joins

To me it seems that the requirements are not very clear. And we don't have a definition (CREATE TABLE) of the two tables, so I'll first write my assumptions of what the tables are what they should be:

    CREATE TABLE value
      ( active boolean not null,
        version_no int not null,
        thing_id int not null,
        data text,
        CONSTRAINT thing_version_UQ
            UNIQUE (thing_id, version_no)
      ) ;

Whether you have or not that UNIQUE constraint is not sure but from the text it seems that each time the value of data is changed for a "thing", a new version is inserted. And we can't have 2 rows with same thing and version so if the constraint isn't there, it should added.
There is only one active version per thing. It's not at all obvious if this restriction is enforced but it can be, with a partial index:

CREATE UNIQUE INDEX only_one_active_version_per_thing_PUQ
    ON value
        (thing_id)
    WHERE (active) ;

Then we have the consideration for a new references table.

First, references is a reserved keyword. I strongly suggest that reserved words should not be used in a database, table, column (or any other object) name. The same goes for VALUES and VALUE. These are not reserved in Postgres but they are in SQL. I would find an insert like the following confusing at first sight:

insert into values values
    (true,   3,  7,  'active-value'),
    (true,   2,  6,  'other-thing'),
    (false,  2,  7,  'old-value'),
    (false,  1,  7,  'first-value') ;

For the actual suggestion, I don't see why you can't use simple queries. To request a specific thing and version:

SELECT active, date
FROM value
WHERE (thing_id, version_no) = (2, 1) ;

and to request for the active version of a thing:

SELECT version, date
FROM value
WHERE active
  AND thing_id = 2 ;

You could easily write a query or a function which covers both cases, if you need to. But having an extra table doesn't serve any purpose in my opinion.


Row Level Trigger for insert/update to a table

In MySQL you need to make deferent trigger for updating and Inserting;
DELIMITER $$

CREATE TRIGGER before_ITEM _insert
BEFORE INSERT
ON ITEM FOR EACH ROW
BEGIN
   SET NEW.Total = NEW.Quantity * NEW.Price;
END $$

DELIMITER ;

CREATE TRIGGER before_ITEM _UPDATE
BEFORE UPDATE
ON ITEM FOR EACH ROW
BEGIN
   SET NEW.Total = NEW.Quantity * NEW.Price;
END $$

DELIMITER ;

But for that purpose you can use generated Columns like
CREATE TABLE ITEM (
ItemNo        int not null,
category      varchar(20) not null,
Quantity      int not null,
Price       decimal(10,2) not null,
Total       decimal(10,2) GENERATED ALWAYS AS (Quantity * Price),
CONSTRAINT pk_item primary key (ItemNo)
);


Migrating database live while being severely handicapped

If you can, it would be better to do views and triggers rather than bilateral replication of data between both sides.

So I would modify your plan slightly:

Create a sane schema
Create an emulation of the old schema using views.
Create triggers on the views to write to the new schema instead of the old.

The question is what comes after this.  Since you are building a new tool, it is good to be in maintenance mode.  This means, I think, only fixing the slowest of the queries that cause you problems.


Best way to store products of Multiple Fields of different information

If you have 6 entity types with different attributes then they should be stored in 6 different tables.

If they have attributes that are largely overlapping then you might consider storing multiple different types of entities in the same table.


Can you make SSL and SSL-only (hostssl) connections to PGPool?

I'm not sure about pgPool, but pgBouncer (another connection pool) can use SSL by using Stunnel.

Check your functional requirements to see if pgBouncer can solve your problem. Maybe pgPool can also use Stunnel, you could give it a try.


SELECT UNION from two views of the same table

A UNION by default will filter out duplicate rows (essentially adding an implicit DISTINCT) which means comparing the rows output to filter them.

If you have duplicate rows that you need filtering out then you may need to cast the JSON type into something else (can you cast it to a string type? - I'm not familiar with postgres and JSON support).

If you do not have duplicate rows to worry about (or you in fact want to keep them) then use UNION ALL instead of a plain UNION and the comparisons will not be necessary. In fact you are likely to find that UNION ALL is more efficient, it is better to use UNION ALL at all times except when you explicitly need duplicate row removal.


SQL Temporal Tables Include Current State

Temporal tables support the FOR SYSTEM_TIME syntax. One of its options effectively does the UNION for you. From the documentation:

SELECT * FROM my_table
FOR SYSTEM_TIME ALL;

  ALL   Returns the union of rows that belong to the current and the history table.


You can identify database snapshots in sys.databases by the column source_database_id being not null.

Join it to sys.master_files and you're done:

SELECT mf.database_id,
    dbs.source_database_id,
    mf.type_desc,
    mf.name,
    mf.physical_name
FROM sys.master_files AS mf
INNER JOIN sys.databases AS dbs
    ON mf.database_id = dbs.database_id
WHERE source_database_id IS NOT NULL

In case you need to filter for one specific source database, you can add that predicate to the WHERE clause.


SQL Parallel Stored Procedure execution using CLR - Performance

Has anyone used CLR for parallelizing Stored Procedures in Critical OLTP Production workloads ?

Parallelizing? Yes. For "critical OLTP Production" systems, not really. This is not to say that you can't do it, but you are entering rather dangerous territory here given that this goes beyond the typical caution of accessing external resources (usually web services) that might not return quickly and hence might "hijack" a thread until SQL Server forces that thread to yield.

  Is there a threshold for number of parallel threads that can be opened before the overhead makes things worse ?

I do not believe so. Also, I am not sure that the thread count by itself is the overriding issue here. Have you also checked for blocking as you increase the execution count? More thoughts below..

  However, when I run multiple (5, 10, 15) main SPs concurrently (each of which analyzes different claims) , performance starts to taper as concurrency increases.

So, here are some points to consider:

SQLCLR aside, isn't what you are seeing, to a degree, merely the nature of resource management / concurrency? As you start to increase concurrency you see a gain, then at some point the amount of each additional gain decreases until it becomes 0, and then increasing concurrency further actually has the opposite effect? (i.e. economies of scale / diseconomies of scale)

Yes, the additional operations are being done on different sets of rows, but each thread still requires its own connection / session. Have you checked the connection count in sys.dm_exec_connections? I'm not sure how many cores you have, but there is a DMV, sys.dm_os_schedulers, that shows the visible scheduler count and that is how many active query threads you can have at any given point in time. If, let's say, you have 32 schedulers, then it won't process 100 threads faster than 50.

Execute the following query to get the details, and review the documentation for the DMV, linked above, to better understand the values being returned by the various fields:

SELECT * FROM sys.dm_os_schedulers WHERE [status] = N'VISIBLE ONLINE';

As you exec that query while your process is running, how do the values change for the various "task" and "worker" count fields, especially as you increase the number of concurrent executions of the SQLCLR stored procedure?
Since you cannot use the Context Connection with multithreading, you are making regular / external connections. That would imply that the SQLCLR stored procedure does not need to be local (well, depending on how it is being used in this process). It might be worth exploring moving this SQLCLR stored procedure to a different server where it wouldn't be competing for threads. Adding to the point made above about only having so many threads to use, one needs to consider that each thread you request is really taking 2 from the CPU, right? One thread in .NET and one thread in SQL Server. Of course, looking at that Code Project page, I don't see any way to pass in a Connection String, so maybe it is just connecting to the (local) instance? (I don't have time at the moment to sign up just to look at the source code).
One thing I noticed while looking at the usage description on that Code Project page was that it seems to store properties in static variables, given that separate execution of stored procedures are able to maintain state between those executions. While it is possible that the Parallel_Declare proc could be used to manage the separation callers, SQLCLR uses a shared App Domain model: App Domains are created per each DB / Assembly owner combination. If you are using the same stored proc for all of this, then that is running in the same App Domain and ALL sessions are sharing the same memory and hence the same static variables. Are you sure that multiple executions of the same stored procedure with different SQL isn't overwriting concurrent executions that have already been started?

While it might be coded properly to handle this, you might want to consider creating separate databases just to hold copies of this Assembly to run each separate set of executions. Who knows, you might also get better thread management out of it as well. This is probably necessary whether you run it locally or on a remote instance.
Finally, given the limited description of the operation, have you considered implementing this setup in your own .NET app? You could write a console application that handles the multi-threading and handles the workflow as you need it to be: multiple, synchronous sections of asynchronous calls. This would remove the various risks you currently face trying to do this within SQLCLR, while ensuring that you can run it remotely. Then, you can run multiple instances of the console app, each to work on different sets of claims, without worrying about shared memory (as each instance of the console app would have its own App Domain) and without taking any threads away from SQL Server to manage it.


how to decrypt one encrypted column with lost password

In order to decrypt the column that is encrypted by symmetric key you would have to create the exact same symmetric key on the new database.

Since symmetric keys cannot be backed up, in order to use them on another database you would have to provide them 2 attributes that have to be specified when creating a new symmetric key on a different database to decrypt a column.

Those two attributes are KEY_SOURCE and IDENTITY_VALUE

After you have specified these attributes you need to encrypt that key, with one of the following: password, certificate, another symmetric key, asymmetric key, or some third party provider. In your case is certificate, therefore you need to create a certificate from certificate(original DB) that you supposedly backed up already. Such as:

CREATE  CERTIFICATE NewDBCertificate
FROM FILE = 'E:\OldDBCertificate.crt'
WITH PRIVATE KEY(
FILE = 'C:\OldDBCertificateKey.pvk',
DECRYPTION BY PASSWORD = N'P@$$W0RD')

This DECRYPTION BY PASSWORD is password that you specified when you were making a certificate backup.

And last step is creating an actual symmetric key, with the same KEY_SOURCE and IDENTITY_VALUE as the one on original DB, with certificate(that you just created) specified encryption.

CREATE SYMMETRIC KEY NewSymmetricKey
WITH ALGORITHM = AES_256, --Note that algorithm has to be the same
KEY_SOURCE = 'PassPhrase used in Original Symmetric Key',
IDENTITY_VALUE = 'Phrase used in Original symmetric key'
ENCRYPTION BY CERTIFICATE NewDBCertificate

So in summary,

Find the name of symmetric key and see which certificate is used for encryption
Make sure you have certificate backup somewhere.
Create certificate on a new DB from the backup
Create new symmetric key using the same KEY_SOURCE & IDENTITY_VALUE and encrypt it using the newly created certificate


If you detach a database from an instance, you will need to perform an OS-level delete of the file.  The safer approach is to drop the database instead.

What I suggest is taking a final backup of the database after you put it into Read Only mode (as this will ensure no activity is occurring during the backup), after which remove it from you system by way of a Drop Database command.

The full set of commands would look similar to the following:

-- Use master db to ensure you don't have an active connection to the db you wish to affect
USE [master]
GO

-- This will kill any active transactions, but will force the database into a Read-Only state
ALTER DATABASE [db_name] SET READ_ONLY WITH ROLLBACK IMMEDIATE
GO

BACKUP DATABASE [db_name] -- Fill in more options here or use the UI to take a backup if you chooose
GO

-- This will kick out all connections from the database allowing you to drop it.
ALTER DATABASE [db_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- Drop the database (which automatically removes the files from the OS)
DROP DATABASE [db_name]
GO

After this, you'll want to look for any Jobs that ran scripts against the database.  I would suggest you just wait to see what fails (after which you can script out/delete the job) as there are numerous ways a job can reference a database (not all of which are easy to identify).

Finally, you'll want to remove any users from the instance that only had access to this database.  This script should identify whom those users are, though Max's version is much cleaner (I didn't realize he posted an approach until after I edited my answer to include this):

DECLARE @ExecString NVARCHAR (4000)

-- Create Empty Table in a very lazy manner
SELECT  name, principal_id, CAST('' AS NVARCHAR(128)) as database_name
INTO ##tmp_AllDBUsers
FROM sys.server_principals
WHERE 1 = 2

-- Declare Cursor to iterate through all DBs on the instance
DECLARE dbCursor CURSOR
FOR
        SELECT name
        FROM sys .databases

DECLARE @name NVARCHAR (128)
OPEN dbCursor
FETCH NEXT FROM dbCursor
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @ExecString =
    'USE [' + @name + '];
    INSERT INTO ##tmp_AllDBUsers
    SELECT sp.name, sp.principal_id, DB_NAME()
    FROM sys.server_principals sp INNER JOIN sys.database_principals dp
        ON sp.sid = dp.sid'

    EXEC(@ExecString)

    FETCH NEXT FROM dbCursor
    INTO @name
END

-- Close and deallocate the cursor because you've finished traversing all it's data
CLOSE dbCursor
DEALLOCATE dbCursor

-- Show all logins that do not belong to a server-level role nor have access to any databases
SELECT sp.*
FROM sys.server_principals sp LEFT JOIN ##tmp_AllDBUsers adu
    ON sp.principal_id = adu.principal_id
WHERE adu.principal_id IS NULL
    AND sp.principal_id NOT IN (SELECT member_principal_id
                            FROM sys.server_role_members)
    AND TYPE IN ('S', 'U', 'G')

-- cleanup
DROP TABLE ##tmp_AllDBUsers


"0.002" sounds like the Query cache came into play.

When the QC is turned on exactly the same SELECT is run a second time before any changes happen to the table(s) involved, the resultset is looked up in a Hash (called the Query Cache).  This will take on the order of 1ms regardless of how long the query normally takes.

Or...

Since you are using MyISAM, virtually any operation that touches the same table(s) will 'lock' the table and cause a normally fast query to stall for as long as the other query (or queries) take.  Switching to InnoDB is likely to help.

If you would like to discuss it further, please provide the query, SHOW CREATE TABLE and EXPLAIN SELECT ..., SHOW VARIABLES LIKE 'query%';,   SHOW GLOBAL STATUS LIKE 'Qc%';  The infomation in those may lead to eliminating one or the other of the above explanations.


The following are the limitations of SQL Server Express:

4 GB max database size (10 GB for 2008 R2)
Single CPU socket
1 GB of RAM
No SQL Server Agent service

When you say you are having a "problem in setup", what is the problem?  The only thing in the above limitations I can think would error out is if you tried to restore a database that is greater than 4 GB (or 10 GB for R2), or if the setup application is trying to do something with SQL Server Agent.

Conversely, the setup application could potentially be just doing a basic check on the edition of SQL Server (through @@version) and then bombing out when it finds Express.  Hard telling not knowing your specific issue.


SQL Connector for Azure Key Vault - multiple key vaults and keys per instance

In SQL Server 2016 Enterprise Edition, is it possible to link a single
  instance to more than one Azure key vault / EKM provider? If so, how
  would I add an additional provider? Any security considerations?

You don't add an additional provider, the provider is simply a reference to a DLL on the SQL Server that provides the interface to your EKM provider.

You add a credential to interact with the key vault (using the FOR CRYPTOGRAPHIC PROVIDER option) and associate that credential with a login. If you have additional key vaults, you add additional credentials and additional logins.

  Also, is it possible to encrypt each individual database with
  different encryption keys, or is the same encryption key used for all
  databases?

(Assuming you mean TDE) Yes. You create a database encryption key, scoped to the DB in question, protected by a certificate or server asymmetric key per database. You already get a different encryption key per DB and you can even protect the DEK using a different cert or asymmetric key per database.

Scenario:
I want to protect 2 databases with TDE using individual Asymmetric Keys provided by different Azure Key Vaults.

Steps:

Create Cryptographic Provider for Azure Key Vault
Create Credential1 for accessing AKV1
Associate Credential1 with sysadmin user
Create Asymmetric Key1 from AKV1
Create Login1 from Asymmetric Key1
Remove Credential1 from sysadmin user
Add Credential1 to Login1
Configure TDE on DB1 using Asymmetric Key1
Create Credential2 for accessing AKV2
Associate Credential2 with sysadmin user
Create Asymmetric Key2 from AKV2
Create Login2 from Asymmetric Key2
Remove Credential2 from sysadmin user
Add Credential2 to Login2
Configure TDE on DB2 using Asymmetric Key2


Querying 'random' Oracle data via openquery in SQL Server returning the same data for multiple runs

In the query you have it appears as though you are grabbing the first two hundred rows, and then sorting those by dbms_random.value.

This would be due to Oracle's query precedence which performs the query and then sorts the results. You would want to randomly sort the results first and then return the first two hundred rows. Using a subquery should provide you with the data that you want.

select * from openquery(ORA_DB, 'select * from
                                (
                                   select XX_NUMBER, XX_CODE, UNIT, XX_serial, XX_date, XX_area, building, location, XX_status, owner_code, qa_XX_no
                                   from qa_eligible_XX
                                   where qa_XX_no = 4
                                   and owner_code <> ''G''
                                   and last_trans_code = ''OL''
                                   and XX_cycle = ''90 - 120 Periodic''
                                   and XX_date < sysdate - 90
                                   order by dbms_random.value
                               )
                               where rownum <= 200 ')


Please see this article for details.  Level of concurrency is controlled by isolation level you chose in SQL server or in your connection. Unless specified or changed Read committed is the default isolation level when you install SQL server and do not overwrite in your transaction.

  Transactions specify an isolation level that defines the degree to
  which one transaction must be isolated from resource or data
  modifications made by other transactions. Isolation levels are
  described in terms of which concurrency side effects, such as dirty
  reads or phantom reads, are allowed.

That being said despite any isolation level you chose data modification is always protected.

  Choosing a transaction isolation level does not affect the locks that
  are acquired to protect data modifications. A transaction always gets
  an exclusive lock on any data it modifies and holds that lock until
  the transaction completes, regardless of the isolation level set for
  that transaction. For read operations, transaction isolation levels
  primarily define the level of protection from the effects of
  modifications made by other transactions.

You can still encounter lost updates if you not at least at Repeatable read isolation level.  See this article for details about repeatable read.

This is a good article explaining isolation level with examples.


How to convert hex chars to byte array in mysql

There is no such thing as an "array" in MySQL.

Perhaps you want UNHEX(SHA1("stuff")) ?  That will provide a 20 bytes, and could be stored, for example, into a BINARY(20) column.


For the side question of sequences, you could do that like this:

For the 42nd shard, use

CREATE SEQUENCE my_seq START WITH 43 INCREMENT BY 1000;

Since all shards have different starting values, the sequence values cannot collide, and you have room for adding more shards.

To consistently change schemas across all shards, use a distributed transaction. Run the following on each shard:

BEGIN;
ALTER TABLE ... ;
/* other schema changes */
PREPARE TRANSACTION 'some_name';

Once this has completed on all shards, execute the following on each shard:

COMMIT PREPARED 'some_name';

If the PREPARE TRANSACTION fails on some database, undo the transaction on each shard with

ROLLBACK PREPARED 'some_name';

This will only work well if your database transactions are all short, because otherwise processing will hang for a long time. It is also beneficial to run the changesbon all shards in parallel, so that processing is not blocked for longer than necessary.

All this assumes that you are not running any other distributed transactions, because otherwise these distributed transactions can easily deadlock with your schema modification in a way that PostgreSQL cannot resolve automatically.


1000's of SQL Update Statements taking forever to complete

Try modifying your query so it isn't overwriting values with the same value.
It will reduce logging.

UPDATE t1
SET t1.fExclPrice = 521.95, t1.fInclPrice = 1
FROM dbo._evPriceListPrices AS t1
INNER JOIN dbo.StkItem AS t2 ON t1.iStockID = t2.StockLink
WHERE t2.Code = '108085R'
AND t1.iPriceListNameID = 1 AND t1.iWareHouseID = 5
--Dont overwrite records with the same values it generates extra logging
AND
(
    t1.fExclPrice IS NULL
    OR t1.fExclPrice <> 521.95
    OR t1.fInclPrice IS NULL
    OR t1.fInclPrice <> 1
)


Uniqueness constraint in union of two columns' values

A trigger solution might work but I'd prefer to normalize the design and have simple constraints:

The table that is now:

CREATE TABLE t (
 id INT PRIMARY KEY,
 t_primary TEXT,
 t_secondary TEXT
) ;

would become:

CREATE TABLE tt (
 id INT,
 t_no SMALLINT NOT NULL,
 t_text TEXT,
 PRIMARY KEY (id, t_no)
 UNIQUE (t_text),
 CHECK (t_no IN (1,2))
) ;

Then your inserts

INSERT INTO t (id, t_primary, t_secondary) VALUES (1, 'a', 'b');
INSERT INTO t (id, t_primary, t_secondary) VALUES (2, 'x', 'y');
INSERT INTO t (id, t_primary, t_secondary) VALUES (3, 'a', 'z');
INSERT INTO t (id, t_primary, t_secondary) VALUES (4, 'z', 'a');

would become:

INSERT INTO tt (id, t_no, t_text) VALUES (1, 1, 'a'), (1, 2, 'b');
INSERT INTO tt (id, t_no, t_text) VALUES (2, 1, 'x'), (2, 2, 'y');
INSERT INTO tt (id, t_no, t_text) VALUES (3, 1, 'a'), (3, 2, 'z');  -- will fail
INSERT INTO tt (id, t_no, t_text) VALUES (4, 1, 'z'), (4, 2, 'a');  -- will fail

Finally you can have the original output with:

CREATE VIEW t AS
SELECT
    a.id,
    a.t_text as t_primary,
    b.t_text as t_secondary,
FROM tt AS a
  JOIN tt AS b
    ON a.id = b.id AND a.t_no = 1 AND b.t_no = 2 ;

SELECT *
FROM t ;


Logging all changes made to MySQL database

There are several tools available to serve the purpose. Following are my favorites:

SQLYog
DB Ninja

Here SQLYog is paid tool, howover it has only windows version available. If you choose this one then its following features will do the job for you:

Schema and Data Synchronization
Visual Data Compare

If you prefer web based interface then DBNinja is great tool. You can compare two database schema easily and fix the differences. following is the example screenshot from their official page:


Create gist index on cube array with 512 dimensions

It can't find a place to store the index tuple, probably because the tuple is larger than a page and so can't fit on any page.  You could recompile with a larger blocksize, e.g. ./configure --with-blocksize=32, but then you will just run into a different limitation, probably INDEX_SIZE_MASK, which is not so easy to work around.

So, you just can't do this.  But if you could, an index into a 512 dimensional cube is not likely to be all that useful anyway.  It will probably be slower than a seq-scan would be, or at least not faster by very much.


I would index geoid10 on both tables.  If you can commit partial results, commit every 1000 to 10,000 updates.  You should be able to do the updates in a stored procedure.  This may reduce the overhead for lock contention.


According to the annotations in your ERD diagram, I understand that:

A Client may have many Projects
A Project may have many Designers

If that is true, then these 2 tables should not be consolidated.

Likewise, depending on your definition of WhyClientChoseUs and ClientLossReason, there are reasons to keep both.  (E.g. you may have a reason WhyClientChoseUs and later need to add the ClientLossReason.  Not uncommon on big projects.)

But, because the client may continue with one project but abandon another, the Project may be a better place to keep that information.

But answers to these kinds of question do depend on your definitions.


Executing a Stored Procedure from a Trigger

You will need to post the code for your trigger and the stored procedure as well as the table definitions in order to get the best assistance.  I was able to put together this quick sample which works for me.

When you are doing the try/catch, I assume you are logging the catch to a table somewhere?

/** Cleanup **/
IF OBJECT_ID('Temp1') IS NOT NULL
    DROP TABLE Temp1;

IF OBJECT_ID('Temp2') IS NOT NULL
    DROP TABLE Temp2;

IF OBJECT_ID('TempTest') IS NOT NULL
    DROP PROCEDURE TempTest;
GO

/** Create our objects **/
CREATE TABLE Temp1 (ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, TextValue VARCHAR(100) NULL);

CREATE TABLE Temp2
    (
        ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
        , Temp1ID INT NULL
        , TextValue VARCHAR(100) NULL
    );
GO

CREATE PROCEDURE TempTest (@ID AS INT)
AS
BEGIN
    INSERT INTO Temp2 (Temp1ID, TextValue)
    SELECT ID
           , TextValue
    FROM Temp1
    WHERE ID = @ID;

END;
GO

CREATE TRIGGER trgTemp1After
ON Temp1
AFTER INSERT
AS
DECLARE @ID INT;

DECLARE curTest CURSOR READ_ONLY LOCAL STATIC FORWARD_ONLY FOR
    SELECT ID
    FROM INSERTED;

OPEN curTest;

FETCH NEXT FROM curTest
INTO @ID;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC TempTest @ID;

    FETCH NEXT FROM curTest
    INTO @ID;
END;

CLOSE curTest;
DEALLOCATE curTest;
GO

/** Insert into Temp1
    Ideally, we should see the same entry appear in Temp2
    **/
INSERT INTO Temp1 (TextValue)
VALUES ('This is a test');

SELECT ID
       , TextValue
FROM Temp1;

SELECT ID
       , Temp1ID
       , TextValue
FROM Temp2;


Need some insights on 'index_page' variables in Innodb_Metrics Table

How big are the rows?  I'm surprised that the "splits" numbers are similar.

"Merge" probably refers to what may happen after DELETEing a row or UPDATEing a row such that the row is smaller.  The block contains less.  If that shrunken block is next to another block that is also rather shrunken, then it behooves InnoDB to "merge" the two blocks together.  This won't happen if all you are doing is INSERTs.

"Attempts" vs "Successful" -- Suppose some row is locked.  Or something else is going on that would be disrupted by the "merge".  It would be better to abort the merge, than to mess up the 'real' operation.

What is the typical size of the TEXT column?  It could be stored in the row, or it could be stored elsewhere.  The "merges" could be happening in the off-record storage.


Maybe the following approach would be of help (the parent tables Uld and Flight should be created first; test server running PostgreSQL 9.3)

create table Uld ( uldno varchar(10) primary key);

create table flight (flightid varchar(32) primary key);

create table LPN (
  id integer primary key
, uldno varchar(10) references Uld(uldno)
, flight varchar(32) references Flight(flightid)
, unique (uldno, flight)
);

Test data:

insert into Flight values ('A'),('B'),('C'),('D');

insert into Uld values ('AAA12345ef'),('BBB12345gh')
, ('CCC12345ij'),('DDD12345kl');

insert into LPN values
(1, 'AAA12345ef' ,'A'),
(2, 'AAA12345ef' ,'B'),
(3, 'AAA12345ef' ,'C'),
(4, 'BBB12345gh' ,'A'),
(5, 'BBB12345gh' ,'D'),
(6, 'BBB12345gh' ,'C'),
(7, 'CCC12345ij' ,'A'),
(8, 'CCC12345ij' ,'B'),
(9, 'CCC12345ij' ,'C'),
(10, 'DDD12345kl' ,'A');

We get (something like):

+------------+
| table: Uld |
+------------+
| uldno [PK] |
+------------+
|AAA12345ef  |
|BBB12345gh  |
|CCC12345ij  |
|DDD12345kl  |
+------------+

+-------------------------------+
| table: LPN                    |
+-----------------+-------------+
| id | uldno [FK] | flight [FK] |
+-----------------+-------------+
|1   | AAA12345ef |A            |
|2   | AAA12345ef |B            |
|3   | AAA12345ef |C            |
|4   | BBB12345gh |A            |
|5   | BBB12345gh |D            |
|6   | BBB12345gh |C            |
|7   | CCC12345ij |A            |
|8   | CCC12345ij |B            |
|9   | CCC12345ij |C            |
|10  | DDD12345kl |A            |
+-----------------+-------------+

+---------------+
| table: Flight |
+---------------+
| flightid [PK] |
+---------------+
|A              |
|B              |
|C              |
|D              |
+---------------+

Testing:

-- eg which Ulds are on flight A?
select uldno
from LPN
where flight = 'A';

The data types for LPN.id and Flight.flightid should be adjusted as per requirements.  This layout should also work if there are several LPNs per uldno (if they have the same flightid in the LPN table).
It may aid your understanding of "keys" if you (also) regard them as "constraints" (~ rules that define certain limitations see eg https://docs.oracle.com/cd/E11882_01/server.112/e40540/datainte.htm#CNCPT322).


Find the highest graded student using Tuple Relational Calculus

Given the schema Students(id:integer,grade:integer), you can solve the problem in tuple relational calculus by using the negation operator (¬).

{T1.id | ∃T1 ∈ Students ¬(∃T2 ∈ Students (T2.grade > T1.grade))}

This would return the id of all students in T1 where there is no student in T2 with a higher grade.

Because T1 and T2 are from the same relation, this effectively returns the set of students with the top grade.

If there are no ties for the top grade, it will return just one id.

It looks like you were thinking in terms of relational algebra rather than tuple relational calculus.

Tuple relational calculus does not have a set difference operator, so you can't find the maximum by subtracting all non-maximums.

Reference

The Solutions Manual for the third edition of Database Management Systems by Ragu Ramakrishnan and Johannes Gerke helped me solve this.

Question 4.3 asks you to solve some problems using this schema:

Suppliers(sid:integer,sname:string,address:string)
Parts(pid:integer,pname:string,color:string)
Catalog(sid:integer,pid:integer,cost:real)

Question 4.3.11 is similar to yours.

  Find the pids of the most expensive parts supplied by suppliers named Yosemite Sham.

The solution looks like this:

{T | ∃T1 ∈ Catalog(∃X ∈ Suppliers
(X.sname = 'Yosemite Sham' ∧ X.sid = T1.sid) ∧ ¬(∃S ∈ Suppliers
(S.sname = 'Yosemite Sham' ∧ ∃Z ∈ Catalog
(Z.sid = S.sid ∧ Z.cost > T1.cost))) ∧ T.pid=T1.pid)}

The solution here is more complicated because of the joins between Suppliers and Catalog, but the essence of the solution is the same.

Elide the joins to get this:

T1 ∈ Catalog ...
(... ¬(... ∃Z ∈ Catalog
(... Z.cost > T1.cost)))

In the reference solution, free variable T has the same pid as bound variable T1.

Taking the pid of T1 directly is a simpler way to get the same result.


How to change the path of temp file,redo log file when restore an rman backup on another machine?

You can either use a pfile and set the following in the pfile, or you can use an spfile and do a startup nomount to set the following variables. You can have as many directories as you want the pattern will still be: '','','','','',''...

alter system set db_file_name_convert='/foo/','/bar/' scope=spfile;
alter system set log_file_name_convert='/foo/','/bar/' scope=spfile;

You can also specify the redo on the clone/restore command.

DUPLICATE DATABASE TO mytest BACKUP LOCATION '/mybackup/mytest'
LOGFILE
       GROUP 1 ('/mybackup/mytest/redo01a.log',  '/mybackup/mytest/redo01b.log') SIZE 256M REUSE,
       GROUP 2 ('/mybackup/mytest/redo02a.log',  '/mybackup/mytest/redo02b.log') SIZE 256M REUSE,
       GROUP 3 ('/mybackup/mytest/redo03a.log',  '/mybackup/mytest/redo03b.log') SIZE 256M REUSE
NOFILENAMECHECK;


My initial reaction is that you should have everything you need in a standard Microsoft SQL Server installation. Integration Services to do the ETL from your flat files into SQL DB engine. Then the T-SQL language and for your calculations and operations, .NET for more advanced operations. Analysis Services and Report Server engines for the online analysis/drilldowns and for producing reports for clients and staff. Couple that with MS Office, Excel and PowerPivot, and you'll have a complete suite of Business Intelligence tools for all your needs and more.

Just my 2c

Good luck!


Is my db too big for my server? Should I go nosql or stick with relational db management?

I would like to echo @Thronk 's remarks about not having enough information to fully answer your question.

However, take a look at this article by a guy who sold his business to eBay and is still senior there.

This suggests to me that you would be far better off redesigning/refactoring your database according to proper relational principals rather than jumping on the NoSQL bandwagon which will involve more work and greater risk. As @Thronk points out, 22GB is not a large system.

If you're using 5.6, try using the performance_schema (p_s) to pick the low-hanging fruit first, which may give you the breathing space you need to properly redesign.

Don't be afraid to come back here with design questions and problem SQL - don't forget that the more detail you put into your question, the better your answers will be. It may be that some reindexing may help - again, don't hesitate to ask here. p.s. welcome to the forum!


Does this query always correctly retrieve the types of array parameters

I think you want format_type(typid oid, typmod integer).

Given:

CREATE SCHEMA my_schema;

CREATE FUNCTION my_schema.my_proc_16831(numeric[])
RETURNS numeric[] LANGUAGE sql AS $$
SELECT $1;
$$;

You can get proc details with:

SELECT
  p.parameter_name,
  format_type((p.udt_schema||'.'||p.udt_name)::regtype, -1)
FROM information_schema.parameters AS p
INNER JOIN information_schema.routines
  USING (specific_catalog, specific_schema, specific_name)
WHERE specific_schema = 'my_schema'
  AND parameter_mode = 'IN'
  AND routine_name = 'my_proc_16831';

... though this removes the portability of using information_schema, since you're relying on Pg specific behaviour. It also assumes the typmod is always -1 because PostgreSQL doesn't keep track of the typmod for functions - e.g. numeric(18,2) becomes just numeric as a function argument.

At least this way you don't need to use your LIKE hack, though, or relying on changeable assumptions about array internal type names.

BTW, you can use pg_proc and pg_type directly instead. This might be worth doing if performance is critical, though otherwise information_schema will be more cross-version portable.

That gives you the equivalent:

SELECT
  proargnames[argpos] AS arg_name,
  format_type(proargtypes[argpos],-1) AS arg_type
FROM pg_proc p
INNER JOIN pg_namespace n ON (p.pronamespace = n.oid)
CROSS JOIN LATERAL generate_subscripts(proargtypes, 1) argpos
WHERE n.nspname = 'my_schema'
  AND p.proname = 'my_proc_16831'
/* AK: suggested additional clause
      AND argpos > 0
*/
    ORDER BY p.proname, argpos;


It was because mysqladmin didn't compress the the data.
when you open it, you will see nice query and code. and also easy to study.

if after compress, the query will stick in 1 line. and hard to study.


You can view the backup history by using the tables in msdb, backupset and backupmediafamily.  I use this query to see the history of all backups for specific databases.

Note that if you backup to multiple files, each file will appear on it's own line.

SELECT MF.physical_device_name AS BackupFilePath
        , BackupType = CASE WHEN BS.[type] = 'D' THEN 1
                            WHEN BS.[type] = 'L' THEN 2
                            WHEN BS.[type] = 'I' THEN 5
                            ELSE NULL
                            END
        , BackupType_Desc = CASE WHEN BS.[type] = 'D' THEN 'FULL'
                            WHEN BS.[type] = 'L' THEN 'LOG'
                            WHEN BS.[type] = 'I' THEN 'DIFF'
                            ELSE NULL
                            END
        , BS.position AS Position
        , BS.[database_name] AS DatabaseName
        , BS.first_lsn AS FirstLSN
        , BS.last_lsn AS LastLSN
        , BS.checkpoint_lsn AS CheckPointLSN
        , BS.database_backup_lsn AS DatabaseBackupLSN
        , BS.backup_start_date
        , BS.backup_finish_date
        , DATEDIFF(MINUTE, BS.backup_start_date, BS.backup_finish_date) AS BackupDuration_Min
        , ROUND(BS.backup_size/1024.0/1024.0, 3) AS BackupSize_MB
        , ROUND(BS.compressed_backup_size/1024.0/1024.0, 3) AS BackupSizeComprssed_MB
    FROM msdb.dbo.backupset AS BS
        LEFT OUTER JOIN msdb.dbo.backupmediafamily AS MF ON MF.media_set_id = BS.media_set_id;


How to connect two attributes without a PK (MySQL)

You're correct in that the most common way to join two tables is to use a foreign key referencing a primary key. But it is not a requirement. The SQL will work (i.e. will by syntactically correct) for any two columns you wish to use in your JOIN clause, as long as the columns' types can be coerced into something comparable. The question is, will the comparison be meaningful (i.e. semantically correct)?

As an example you may choose to store shoe size and IQ for various employees. Then the query .. inner join .. on employee.shoesize = manager.IQ is a syntactically correct statement but is (likely!) meaningless.

Also think about what it means to join two tables. Conceptually it means you take each row from the first table in turn and combine it with every row from the second table. You retain those combined rows for which the join condition (the "ON" clause) is true and discard those for which it is false.

Back to your particular example. Do the meanings of Table1.Charge and Table2.Charge allow them to be compared and still satisfy the business rules of the system? Will the values in Table1.Charge be sufficiently unique to ensure only those rows which are meant to be matched will be matched? As a counter-example, if all charges were a standard amount then every row from Table1 would match every row from Table2.

If your can answer both questions as "yes" then go for it. If not you'll have to re-design.


Select where value in row is different and update

update cartographic_text_vm
set primary_key = new.primary_key
from
    (select *
     from cartographic_text_vm
     where (fid, ckpt) in
         (select fid, min(ckpt)
          from cartographic_text_vm
          group by fid)
    ) new
where cartographic_text_vm.fid = new.fid
  and cartographic_text_vm.primary_key != new.primary_key;