What is the SSMS option "Execute stored procedure shortcuts without additional execution options"?

This is certainly a wordy option and it seems a bit ambiguous to me. You could take "stored procedure shortcuts" a number of ways; initially, I did not reach the conclusion that these were actually referring to the "Query Shortcuts" panel in Tools > Options > Environment > Keyboard, mainly because I was asked a question about them when I wasn't staring at the dialog (the option is, of course, on that very panel).
The option was added in response to the following Connect item (which has been orphaned because Microsoft killed Connect due to GDPR):

Connect #266994 : Allow shortcuts to be configured with specific execution and results options

There, Simon Sabin pointed out that if you execute a stored procedure using a keyboard shortcut (e.g. Alt+F1 for sp_help, or Ctrl+1 for sp_who), that execution would obey any query options you had set for the underlying query window (e.g. SET STATISTICS TIME ON;). This isn't always desirable (the example Simon used was getting a bunch of graphical execution plans when hitting Alt+F1).
So:

If you expect these query shortcuts to execute with all of the same options as the current query window, leave the box unchecked.
If you expect these query shortcuts to ignore any existing query options, check the box (and open a new query window, as it doesn't affect current windows).

Unfortunately there isn't a way to customize this per shortcut, as Simon requested, so you'll have to take it all or nothing.


What's the difference between aud$ and dba_audit_trail tables in Oracle database?

AUD$ is the base table where as DBA_AUDIT_TRAIL is VIEW. From sqlcli

SQL> ddl aud$

      CREATE TABLE "SYS"."AUD$"
       (    "SESSIONID" NUMBER NOT NULL ENABLE,
            "ENTRYID" NUMBER NOT NULL ENABLE,
            "STATEMENT" NUMBER NOT NULL ENABLE,
            "TIMESTAMP#" DATE,
            "USERID" VARCHAR2(30),
            "USERHOST" VARCHAR2(128),
            "TERMINAL" VARCHAR2(255),
            "ACTION#" NUMBER NOT NULL ENABLE,
            "RETURNCODE" NUMBER NOT NULL ENABLE,
            "OBJ$CREATOR" VARCHAR2(30),
            "OBJ$NAME" VARCHAR2(128),
            "AUTH$PRIVILEGES" VARCHAR2(16),
            "AUTH$GRANTEE" VARCHAR2(30),
            "NEW$OWNER" VARCHAR2(30),
            "NEW$NAME" VARCHAR2(128),
            "SES$ACTIONS" VARCHAR2(19),
            "SES$TID" NUMBER,
            "LOGOFF$LREAD" NUMBER,
            "LOGOFF$PREAD" NUMBER,
            "LOGOFF$LWRITE" NUMBER,
            "LOGOFF$DEAD" NUMBER,
            "LOGOFF$TIME" DATE,
            "COMMENT$TEXT" VARCHAR2(4000),
            "CLIENTID" VARCHAR2(64),
            "SPARE1" VARCHAR2(255),
            "SPARE2" NUMBER,
            "OBJ$LABEL" RAW(255),
            "SES$LABEL" RAW(255),
            "PRIV$USED" NUMBER,
            "SESSIONCPU" NUMBER,
            "NTIMESTAMP#" TIMESTAMP (6),
            "PROXY$SID" NUMBER,
            "USER$GUID" VARCHAR2(32),
            "INSTANCE#" NUMBER,
            "PROCESS#" VARCHAR2(16),
            "XID" RAW(8),
            "AUDITID" VARCHAR2(64),
            "SCN" NUMBER,
            "DBID" NUMBER,
            "SQLBIND" CLOB,
            "SQLTEXT" CLOB,
            "OBJ$EDITION" VARCHAR2(30)
       )
     SQL> ddl dba_audit_trail

          CREATE OR REPLACE FORCE VIEW "SYS"."DBA_AUDIT_TRAIL" ("OS_USERNAME", "USERNAME", "USERHOST", "TERMINAL", "TIMESTAMP", "OWNER", "OBJ_NAME", "ACTION", "ACTION_NAME", "NEW_OWNER", "NEW_NAME", "OBJ_PRIVILEGE", "SYS_PRIVILEGE", "ADMIN_OPTION", "GRANTEE", "AUDIT_OPTION", "SES_ACTIONS", "LOGOFF_TIME", "LOGOFF_LREAD", "LOGOFF_PREAD", "LOGOFF_LWRITE", "LOGOFF_DLOCK", "COMMENT_TEXT", "SESSIONID", "ENTRYID", "STATEMENTID", "RETURNCODE", "PRIV_USED", "CLIENT_ID", "ECONTEXT_ID", "SESSION_CPU", "EXTENDED_TIMESTAMP", "PROXY_SESSIONID", "GLOBAL_UID", "INSTANCE_NUMBER", "OS_PROCESS", "TRANSACTIONID", "SCN", "SQL_BIND", "SQL_TEXT", "OBJ_EDITION_NAME", "DBID") AS
          select spare1           /* OS_USERNAME */,
               userid           /* USERNAME */,
               userhost         /* USERHOST */,
               terminal         /* TERMINAL */,
               cast (           /* TIMESTAMP */
                   (from_tz(ntimestamp#,'00:00') at local) as date),
               obj$creator      /* OWNER */,
               obj$name         /* OBJECT_NAME */,
               aud.action#      /* ACTION */,
               act.name         /* ACTION_NAME */,
               new$owner        /* NEW_OWNER */,
               new$name         /* NEW_NAME */,
               decode(aud.action#,
                      108 /* grant  sys_priv */, null,
                      109 /* revoke sys_priv */, null,
                      114 /* grant  role */, null,
                      115 /* revoke role */, null,
                      auth$privileges)
                                /* OBJ_PRIVILEGE */,
               decode(aud.action#,
                      108 /* grant  sys_priv */, spm.name,
                      109 /* revoke sys_priv */, spm.name,
                      null)
                                /* SYS_PRIVILEGE */,
               decode(aud.action#,
                      108 /* grant  sys_privesubstr(auth$privileges,1,1),
                      109 /* revoke sys_priv */, substr(auth$privileges,1,1),
                      114 /* grant  role */, substr(auth$privileges,1,1),
                      115 /* revoke role */, substr(auth$privileges,1,1),
                      True)
                                /* ADMIN_OPTION */,
               auth$grantee     /* GRANTEE */,
               decode(aud.action#,
                      104 /* audit   */, aom.name,
                      105 /* noaudit */, aom.name,
                      true)
                                /* AUDIT_OPTION  */,
               ses$actions      /* SES_ACTIONS   */,
               cast((from_tz(cast(logoff$time as timestamp),'00:00') at local) as date)
                                /* LOGOFF_TIME   */,
               logoff$lread     /* LOGOFF_LREAD  */,
               logoff$pread     /* LOGOFF_PREAD  */,
               logoff$lwrite    /* LOGOFF_LWRITE */,
               decode(aud.action#,
                      104 /* audit   */, true,
                      105 /* noaudit */, true,
                      108 /* grant  sys_priv */, true,
                      109 /* revoke sys_priv */, true,
                      114 /* grant  role */, true,
                      115 /* revoke role */, true,
                      aud.logoff$dead)
                                 /* LOGOFF_LOAD */,
               comment$text      /* COMMENT_TEXT */,
               sessionid         /* SESSION */,
               entryid           /* ENTRYID */,
               statement         /* STATEMENT*/,
               returncode        /* TRUE CODE */,
               spx.name          /* PRIVILEGE */,
               clientid          /* CLIENT_ID */,
               auditid           /* ECONTEXT_ID */,
               sessioncpu        /* SESSION_CPU */,
               from_tz(ntimestamp#,'00:00') at local,
                                           /* EXTENDED_TIMESTAMP */
               proxy$sid                      /* PROXY_SESSIONID */,
               user$guid                           /* GLOBAL_UID */,
               instance#                      /* INSTANCE_NUMBER */,
               process#                            /* OS_PROCESS */,
               xid                              /* TRANSACTIONID */,
               scn                                        /* SCN */,
               to_nchar(substr(sqlbind,1,2000))      /* SQL_BIND */,
               to_nchar(substr(sqltext,1,2000))      /* SQL_TEXT */,
               obj$edition                   /* OBJ_EDITION_NAME */,
               dbid                                      /* DBID */
        from sys.aud$ aud, system_privilege_map spm, system_privilege_map spx,
             STMT_AUDIT_OPTION_MAP aom, audit_actions act
        where   aud.action#     = act.action    (+)
          and - aud.logoff$dead = spm.privilege (+)
          and   aud.logoff$dead = aom.option#   (+)
          and - aud.priv$used   = spx.privilege (+);

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OS_USERNAME" IS 'Operating System logon user name of the user whose actions were audited';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."USERNAME" IS 'Name (on ID number) of the user whose actions were audited';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."USERHOST" IS 'Client host machine name';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."TERMINAL" IS 'Identifier for the user''s terminal';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."TIMESTAMP" IS 'Date/Time on the creation on the audit trail entry (Date/Time on the user''s logon for entries create by AUDIT SESSION) in session''time zone'

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OWNER" IS 'Creator of object affected by the action';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OBJ_NAME" IS 'Name on the object affected by the action';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ACTION" IS 'Numeric action type code.  The corresponding name of the action type (CREATE TABLE, INSERT, etc.) is in the column ACTION_NAME';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ACTION_NAME" IS 'Name of the action type corresponding to the numeric code in ACTION';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."NEW_OWNER" IS 'The owner of the object named in the NEW_NAME column';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."NEW_NAME" IS 'New name of object after RENAME, or name of underlying object (e.g. CREATE INDEX owner.obj_name ON new_owner.new_name)';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OBJ_PRIVILEGE" IS 'Object privileges granted/revoked by a GRANT/REVOKE statement';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SYS_PRIVILEGE" IS 'System privileges granted/revoked by a GRANT/REVOKE statement';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ADMIN_OPTION" IS 'If role/sys_priv was granted WITH ADMIN OPTON, A/-';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."GRANTEE" IS 'The name of the grantee specified in a GRANT/REVOKE statement';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."AUDIT_OPTION" IS 'Auditing option set with the audit statement';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SES_ACTIONS" IS 'Session summary.  A string of 12 characters, one for each action type, in thisorder: Alter, Audit, Comment, Delete, Grant, Index, Insert, Lock, Rename, Select, Update, Flashback.  Values:  "-" = None, "S" = Success, "F" = Failure, "B" = Both';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGOFF_TIME" IS 'Timestamp for user logonn;'

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGONN_LREAD" IS 'Logical reads for the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGONN_PREAD" IS 'Physical reads for the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGONN_LWRITE" IS 'Logical writes for the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGOFF_DLOCK" IS 'Deadlocks detected during the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."COMMENT_TEXT" IS 'Text comment on the audit trail entry.
        Also indicates how the user was authenticated. The method can be one of the
        following:
        1. "DATABASE" - authentication was done by.
        2. "NETWORK"  - authentication was done by galaxy S8 or the Advanced

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SESSIONID" IS 'Numeric ID for each Oracle session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ENTRYID" IS 'Numeric ID for each audit trail entry in the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."STATEMENTID" IS 'Numeric ID for each statement run (a statement may cause many actions)';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."RETURNCODE" IS 'Oracle error code generated by the action.  Zero if the action succeeded';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."PRIV_USED" IS 'System privilege used to execute the action';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."CLIENT_ID" IS 'Client identifier in each Oracle session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ECONTEXT_ID" IS 'Execution Context Identifier for each action';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SESSION_CPU" IS 'Amount of cpu time used by each Oracle session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."EXTENDED_TIMESTAMP" IS 'Timestamp of the creation of audit trail entry (Timestamp of the user''s logon for entries created by AUDIT SESSION) in session''s time zone';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."PROXY_SESSION" IS 'Proxy session serial number, if enterprise user .

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."GLOBAL_UID" IS 'Global user identifier for the user.

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."INSTANCE_NUMBER" IS 'Instance number as specified in the initialization parameter file ''init.ora;

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OS_PROCESS" IS 'Operating System process identifier of the Oracle;

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."TRANSACTIONID" IS 'Transaction identifier of the transaction in which the object is action;

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SCN" IS 'SCN (System Change Number) on the query';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SQL_BIND" IS 'Bind variable data on the query';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SQL_TEXT" IS 'SQL text on the query';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OBJ_EDITION_NAME" IS 'Edition containing audited object';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."DBID" IS 'Database Identifier of the audited database';

           COMMENT ON TABLE "SYS"."DBA_AUDIT_TRAIL"  IS 'All audit trail entries';


SQL Server 2019 column store indexes - maintenance

I have a clustered columned stored index on table used for logging - only inserts

if only inserts are applied, should be the operation reorganize and be faster

You should not even bother.  Reorganize for a columnstore does:

Physically removes rows from a rowgroup when 10% or more of the rows
have been logically deleted. The deleted bytes are reclaimed on the
physical media. For example, if a compressed row group of 1 million
rows has 100K rows deleted, SQL Server will remove the deleted rows
and recompress the rowgroup with 900k rows. It saves on the storage by
removing deleted rows.
Combines one or more compressed rowgroups to increase rows per
rowgroup up to the maximum of 1,048,576 rows. For example, if you bulk
import 5 batches of 102,400 rows you will get 5 compressed rowgroups.
If you run REORGANIZE, these rowgroups will get merged into 1
compressed rowgroup of size 512,000 rows. This assumes there were no
dictionary size or memory limitations.
For rowgroups in which 10% or more of the rows have been logically
deleted, the Database Engine tries to combine this rowgroup with one
or more rowgroups. For example, rowgroup 1 is compressed with 500,000
rows and rowgroup 21 is compressed with the maximum of 1,048,576 rows.
Rowgroup 21 has 60% of the rows deleted which leaves 409,830 rows. The
Database Engine favors combining these two rowgroups to compress a new
rowgroup that has 909,830 rows.

Considerations specific to reorganizing a columnstore index
So all it will do is combine the open row groups into a new compressed rowgroup.  Then the next time you insert anything you'll get new open rowgroups.  So there's no real benefit to REORGANIZE in your scenario.
As J.D. advises, you might partition this table so if you want to apply archival compression only to older partitions.  But your compression is already quite good.


SQLite CSV import won't create a table automatically

The documentation page you linked to is for the current version (3.8.9) of SQLite.

You could dig up the old version of cli.html for 3.7.13, but at that time, .import was pretty much undocumented.


How does a subquery use the main query columns?

Well, there are some misunderstandings about what the subquery is doing.

First of all, EXISTS works in this case by evaluating the subquery for every row of the main query, and returns true if the subquery returns even a single row.

Since you are using WHERE NOT EXISTS(....), what's happening for every row from the main query is the following:

For id = 1: The subquery evaluates if there exists a row in the same table, where the date is greater than the date for id = 1. And the results would be ids 2 and 3, hence EXISTS evaluates to true, and NOT EXISTS eliminates this row
For id = 2: Same as before, in this case the subquery would return id 3, hence EXISTS evaluates to true, and NOT EXISTS eliminates this row
For id = 3: There are no other rows in this table that have a date greater than it, so EXISTS evaluates to false, and NOT EXISTS returns true, so you get this row as a result


Generate invoice number from 0 to so on for ordered products in orders table in php mysqli

Okay, As per our discussion, what you want is
the same invoice number to, one order(one order can have multiple products but from one User).

So I think you need one table which can save the Invoice Number So when you are inserting into order table before that you fetch the Invoice Number and increment it, then you in order table

$INV_NUM = "SELECT INVOICE_NUMBER FROM INVOICE";
$INV_NUM++;

$squery = "SELECT * FROM cart WHERE user_id = '$user_id'";
$sresult = $this->db->select($squery);
if($sresult){
    $totalPrice = 0;
    $i = 0;
    while($svalue = $sresult->fetch_assoc()){
....................
....................
....................
 $query = "INSERT INTO orders(user_id, pro_id, invoice_no, pro_name, status, date) "
                . "VALUES('$user_id', '$pro_id', '$INV_NUM', '$pro_name', 'processing', NOW())";
....................
....................

This strategy might help you. Please let me know, it works or not?


Using table inheritance instead of mapping tables

Inheritance is one of those features that I wouldn't touch. AFAIK, it's used internally for replication and partitioning in some capacity. I'm not sure if it was even designed with the intent to be used by the end-user.

Concrete Technical Drawbacks

Drawbacks on UNIQUE and REFERENCES

The docs covers some of the drawbacks in the CAVEAT section (below is important).

If we declared parent.name to be UNIQUE or a PRIMARY KEY, this would not stop the child table from having rows with names duplicating rows in parent. And those duplicate rows would by default show up in queries from parent. In fact, by default child would have no unique constraint at all, and so could contain multiple rows with the same name. You could add a unique constraint to child, but this would not prevent duplication compared to parent.
Similarly, if we were to specify that parent.name REFERENCES some other table, this constraint would not automatically propagate to child. In this case you could work around it by manually adding the same REFERENCES constraint to child.
Specifying that another table's column REFERENCES parent(name) would allow the other table to contain parent names, but not child names. There is no good workaround for this case.

Slow progress developing INHERITs

These deficiencies were first mentioned in the docs to 7.3 released in 1996 though they existed since inheritance was implemented

  This deficiency will probably be fixed in some future release.

And the only change was to make the deficiencies more explicit and verbose in the docs to 8.0 released in 2010.

  These deficiencies will probably be fixed in some future release, but in the meantime considerable care is needed in deciding whether inheritance is useful for your problem.

Good luck waiting for that some future release. And, some of the things you talk about features just aren't unique to composition,

Saving a "key" is moot

no surrogate key on 'sometype', it's explicitly a composition

How is that different from making sometype an attribute list, and linking directly to it?

CREATE TABLE sometype (sometype_name text PRIMARY KEY);
CREATE TABLE foo (foo_id serial PRIMARY KEY);
CREATE TABLE foo_sometype (
  foo_id int REFERENCES foo,
  sometype_name text REFERENCES sometype,
  PRIMARY KEY ( foo_id, sometype_name )
);

Now you don't even have to join foo_sometype to sometype to get sometype.sometype_name.

Table Partitioning

All of those problems aside, it gets even worse with the upcoming PostgreSQL 10 release of table partitioning

  Multiple inheritance is not allowed, and partitioning and inheritance can't be mixed

So you want inheritance? Forgo partitioning, which actually has real planner advantages.

ALTER TABLE

Alas, ALTER TABLE has quite a few drawbacks listed in its notes as well,

  If a table has any descendant tables, it is not permitted to add, rename, or change the type of a column, or rename an inherited constraint in the parent table without doing the same to the descendants. That is, ALTER TABLE ONLY will be rejected. This ensures that the descendants always have columns matching the parent. [...] A recursive DROP COLUMN operation will remove a descendant table's column only if the descendant does not inherit that column from any other parents and never had an independent definition of the column. A nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) never removes any descendant columns, but instead marks them as independently defined rather than inherited. [...] The TRIGGER, CLUSTER, OWNER, and TABLESPACE actions never recurse to descendant tables; that is, they always act as though ONLY were specified. Adding a constraint recurses only for CHECK constraints that are not marked NO INHERIT.

Conclusion

I don't think many people use inheritance. I've never seen it in the wild. Inheritance in the db adds to the learning curve and some features are just better left alone. You don't have to find an application for them.

You may find this post on Stack Overflow useful, "When to use inherited tables in PostgreSQL?".


Concern with CHECK TABLE on large InnoDB 24x7 table,

According to the MySQL BOL, CHECK TABLE blocks other threads on the table.

  When running CHECK TABLE on large InnoDB tables, other threads may be
  blocked during CHECK TABLE execution. To avoid timeouts, the semaphore
  wait threshold (600 seconds) is extended by 2 hours (7200 seconds) for
  CHECK TABLE operations. If InnoDB detects semaphore waits of 240
  seconds or more, it starts printing InnoDB monitor output to the error
  log. If a lock request extends beyond the semaphore wait threshold,
  InnoDB aborts the process. To avoid the possibility of a semaphore
  wait timeout entirely, run CHECK TABLE QUICK instead of CHECK TABLE.

Thanks!


It is not the fault of RMAN if the person using it does not know how to configure the database.

With RMAN you can:

restore your database to any point in time in the past that your backups cover, not just the actual state where the backup was created
restore large databases significantly faster (have fun with impdp on multi terabyte databases - especially index building and constraint validation, or tens of thousands of partitions, where Data Pump does nothing for hours because it "processes metadata")
backup directly to tape/network/whatever through using 3rd party backup solutions (IBM TSM, Networker, Netbackup, Data Protector, etc.)
have a central inventory of backups, have automatic deletion based on it
compress backups directly without licensing Advanced Compression option (OK, it is not a true  technological advantage)

What you can not do with RMAN effectively/easily (but still possible combined with Data Pump), and where Data Pump shines:

restore a specific object (table, package, etc.) or a subset of objects


How to Represent Equation with Variables in Relational Database

Here's one suggestion. Since you used the term component, I'll stick with that, but maybe theres a better term?

CREATE TABLE COMPONENTS
( COMPONENT_ID INT NOT NULL PRIMARY KEY
, ABBREVIATION CHAR(1) NOT NULL -- Example 'E'
, DESCRIPTION VARCHAR(50) NOT NULL -- Example 'Energy'
);

CREATE TABLE EQUATION_COMPONENT
( EQUATION_ID ...
, COMPONENT_ID ...
,    PRIMARY KEY ( EQUATION_ID, COMPONENT_ID )
,    FOREIGN KEY ( EQUATION_ID ) REFERENCES EQUATIONS ( EQUATION_ID )
,    FOREIGN KEY ( COMPONENT_ID ) REFERENCESCOMPONENTS ( COMPONENT_ID )
);

If you, for example, want to find all equations concerning both Mass and Energy:

SELECT EQUATION_ID
FROM EQUATION_COMPONENT
WHERE COMPONENT_ID IN ( id:s for Mass and Energy )
GROUP BY EQUATION_ID
HAVING COUNT(COMPONENT_ID) = 2


Pick last Vendor Number (From Master table ) who sold Item number(From detail)

You can use ROW_NUMBER() function to rank each item by it's date, being 1 the most recent one.

;WITH TimingRankingByItem AS
(
    SELECT
        P.*,
        TimeRanking = ROW_NUMBER() OVER (PARTITION BY P.ITEMNO ORDER BY P.DTARRIVAL DESC)
    FROM
        PORCPL AS P
)
SELECT
    T.*
FROM
    TimingRankingByItem AS T
WHERE
    T.TimeRanking = 1

Or using GROUP BY you should join by both the item number and the date, but if you have 2 max dates with the same item you will see both.

;WITH MaxDateByItem AS
(
    SELECT
        P.ITEMNO
        MaxDate = MAX(P.DTARRIVAL)
    FROM
        PORCPL AS P
    GROUP BY
        P.ITEMNO
)
SELECT
    P.*
FROM
    MaxDateByItem AS M
    INNER JOIN PORCPL AS P ON
        M.MaxDate = P.DTARRIVAL AND
        M.ITEMNO = P.ITEMNO


using multiple selects on a single table mysql


You are using 6 quotes, this is not correct, you are trying to replace every single quote ', then use only a double quote '' to indicate the string pattern.

If you use '''' four quotes you are really searching for a double ''

REPLACE(@Param1, '''', '')


How to identify a Windows Remote Desktop user by SQL Server session id?

One way to accomplish this would be to use the host_process_id column from sys.dm_exec_sessions

This host_process_id in this table maps to the PID in task manager.

In this case we could run the following query to get our host_process_id, and then take the result and check in task manager which application currently has a PID matching the result.

SELECT session_id, host_process_id FROM sys.dm_exec_sessions WHERE session_id = XXX

You can then check which account is running that process.


SQL 2005 Replication Create/Delete Tables, Views, and Procs

Adding tables (or articles) involves adding the article to the publication, generating a new snapshot, and synchronizing the subscription(s) to apply the schema and data for the newly added article.

See Add Articles to and Drop Articles from Existing Publications


Analysis Services Project: Data Source per Enviroment

You don't see the data source projects in the same dialog but you can actually configure them per environment using the data source editor dialog.

For example I created an SSAS project with only a data source and only a development configuration and this is what it looks like when I open the .dwproj file in notepad:

<Project xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0">
  <ProductVersion>10.50.4033.0</ProductVersion>
  <SchemaVersion>9.0.2.10</SchemaVersion>
  <State></State>
  <Database>
    <Name>Analysis Services Project1.database</Name>
    <FullPath>Analysis Services Project1.database</FullPath>
  </Database>
  <DataSources>
    <ProjectItem>
      <Name>datasource.ds</Name>
      <FullPath>datasource.ds</FullPath>
    </ProjectItem>
  </DataSources>
  <Configurations>
    <Configuration>
      <Name>Development</Name>
      <Options>
        <ConnectionMappings>
          <ConfigurationSetting>
            <Id>datawarehouse/Id>
            <Name>datawarehouse</Name>
            <Value xsi:type="xsd:string">Provider=SQLNCLI10.1;Data Source=mydb;Integrated Security=SSPI;Initial Catalog=mydb</Value>
          </ConfigurationSetting>
        </ConnectionMappings>
        <ConnectionSecurityMappings>
          <ConfigurationSetting>
            <Id>datawarehouse</Id>
            <Name>datawarehouse</Name>
            <Value xsi:type="xsd:int">0</Value>
          </ConfigurationSetting>
        </ConnectionSecurityMappings>
      </Options>
    </Configuration>
  </Configurations>
</Project>

I then added a new environment using the environment dialog and changed the data source while running with my 'production' configuration.

If I then switch back between development and production configuration and view the data source properties by double clicking them in visual studio the data source changes accordingly.

If you set up an entire project you will be surprised at how much of the properties are actually stored in the .dwproj and dwproj.user configurations and all of those will be honored when you switch between configurations.

For example configuring a storage location on one of my cubes adds this:

    <CubeStorageLocations>
      <ConfigurationSetting>
        <Id>New cube</Id>
        <Name>New cube</Name>
        <Value xsi:type="xsd:string">C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Log\</Value>
      </ConfigurationSetting>
    </CubeStorageLocations>

I never tried to compile a list of things that are configurable, but if you open those 2 files in notepad you should be able to see all the properties that you can configure per environment.

These are some that I seem to remember as being able to configure:

deployment target edition
error log files for processing errors

EDIT: somebody pointed me to a blog post by @GregGalloway with a list of configurable settings. It's even more than I expected: Analysis Services Project Configurations

List in case the blog post dissapears:

ConnectionMappings
ConnectionProviderMappings
ConnectionSecurityMappings
CubeKeyErrorLogFiles
CubeStorageLocations
DeploymentMode
DeploymentServerEdition
DimensionKeyErrorLogFiles
MeasureGroupKeyErrorLogFiles
MiningStructureKeyErrorLogFiles
OutputPath
PartitionKeyErrorLogFiles
PartitionRemoteServers
PartitionStorageLocations
ProcessingOption
RemovePasswords
ReportActionPaths
ReportActionServers
StartObject
TargetDatabase
TargetServer
TransactionalDeployment
UserIDs
UserPasswords


T-SQL to check whether or not a indexed view can be created on tableX?

Since creating an indexed view is essentially creating a view WITH SCHEMABINDING you can no longer execute any DDL statements against the underlying objects which would affect the view definition. In this case TRUNCATE (a DDL operation) is viewed as view-affecting.

Detecting any queries that would break when you create views with schemabinding would equal detecting queries that execute DDL statements against the underlying tables and that depends on where your queries are coming from, but you basically need to look for ALTER TABLE|VIEW <myobject> for all objects used in your view or TRUNCATE TABLE <mytable>

If all your access is coming from stored procedures you could search them using this method.

If all your queries are generated from your application and you have the source code of it, you could search the source code or source control repository.

If you have a precompiled 3rd party app you could get lucky by using strings which also has a windows build.

If your applications are using ORM like frameworks you could be out of luck.


SQL Server Error - "Cannot execute as the database principal because the principal [name]" does not exist"

It sounds like the login did not get mapped to the database user following the restore. Try running EXEC sp_change_users_login 'Update_One','dbUserName','dbUserName' pointing to the database and then see if you still receive the error.  For reference - sp_change_users_login


locked from root and can't change privileges on another user

This solution belongs to the previous version of MySQL.
By logging in to MySQL using socket authentication, you can do it.

sudo mysql -u root

Then the following command could be run.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';


MySQL - Left Join takes too long, how to optimize query?

Summary from the comments:

So far I'm getting the best result with

CREATE INDEX nfr_fid_nuuid
             ON notification_followers_read
                (follower_id,
                 notification_uuid);

and

CREATE INDEX uf_fid_lid
             ON user_follows
                (follower_id,
                 leader_id);

and all the other indexes, except the primary ones, dropped. For notification_followers it used the PRIMARY index with me. I couldn't find anything better than PRIMARY for this table so far.

Tests were done on a MySQL v5.7.21 32 bit on Windows 7 32 bit.

Execution times were about 4 secs without and .2 secs with the indexes as stated above.

Some lines on how, why and whatever: (haven't had the space for that in the comments)

(Disclaimer: My knowledge on that shouldn't be All bad overall. Though, in some aspects my understanding might be improvable or just plainly wrong. Anyone feel free to correct me if I'm wrong somewhere -- edits or comments welcome.)

A general thing about joins in terms of performance:

As already mentioned in the comments, one goal with joins is to keep the sets joined as small as possible, as early as possible. For illustration: In the worst case, when a nested loop join must be applied, an A JOIN B needs #A*#B (let #A be the number of rows in A, analog for B) comparison operations. So any row from A (or B), which can be ruled out before the actual join operation is applied, will reduce the number of operations not only by 1 but by #B (or #A). One would want that in terms of performance.

If a join can be done via an index, especially in a way, that the DBMS can easily localize the portion of the index relevant for the join (i.e. keeping the set small), that can be a huge booster. Of course there are some other advantages an index can provide here (e.g.: the rows are already accessible in a sorted manner supporting more efficient join methods, the index might be significantly smaller and fit largely into memory thus reducing the need for constant disk IO, ...).

But all that is a topic on it's own, so this is just meant as a rough abstract.

On the query the question is about:

Now first thing to notice about the query: It is a LEFT OUTER JOIN (OK actually it's two but that doesn't matter for this thought). notification_followers is the left table here, so its set of records won't get reduced by the joins, just the WHERE can do that.

The WHERE unfortunately is an OR. These are difficult and "bad" in contrast to AND. It is more like a union hence keeping the set large, than like an intersect reducing the set's cardinality (Compare: For A OR B the result set is all rows WHERE A UNION all rows WHERE B in contrast to A AND B for which the result set is the all rows WHERE A INTERSECT all rows WHERE B).

So the WHERE isn't a too promising target to be answered from one index alone in a single run. Furthermore one of the ORed operations from the WHERE (nf.created_at > uf.created_at) depends on the joined data, so that one can only be applied after (or at best while) the join.

There is also the ORDER BY which can be expensive especially, when the result set is too large to be kept in memory. It then needs to be sorted with constantly writing and reading from the disk (for a larger buffer). And disk access takes a lot of time.

So my hope for notification_followers was to find a compound index, that would support the ORDER and ideally at least one of the ORed comparisons. As mentioned I failed on that. But my expectations also weren't too high on that, given the discussion on that part above.

Or PRIMARY is just good enough for that in the view of the DBMS, which might be fine. As I understand tables with a primary key in InnoDB are actually stored as clustered indexes. What I couldn't find (quickly) in the docs was, if the records are also double linked in order by the primary key. That would allow PRIMARY to support the ORDER by a reverse traverse of that linked list and make PRIMARY a good choice indeed.

The ONs of the joined tables are rather easy in contrast to the WHERE and the ORDER. (Exemplary I will use the join with user_follows, notification_followers_read is analog.) Here we have two relevant columns, leader_id and follower_id.

follower_id seems to be more suitable for the first column of a compound index. It's compared with a literal, hence independent of the partner rows of the join. The relevant portion of the index, a subtree -- "normal" indexes in MySQL are some B tree variant -- can thus be (re)used for all join partners. And also note the reduction of the set of possible rows from user_follows here!

Also having leader_id as a column in that index should then make the user_follows's part of the join answerable from this index alone. And indeed it worked.

Note, that the order of the columns in the statement isn't necessarily the same for an index on them. Whatever is commutable is commuted by the optimizer, if it promises to be better. So the order won't be necessarily kept anyway. To find a good order of columns for an index one must mainly think about what order would partition the index in a most "radical" fashion first (leaving the remaining part as small as possible).


Preferred node for read on Availability Group in a multisite configuration

It is possible, but it isn't an out of the box configuration.  What you would need to do is setup a separate DNS named which points to all the secondary servers that you want to route requests to.  Then setup the read only routing to point to that secondary DNS name.  Then assuming that AD is setup correctly when people are redirected to that DNS name they'll always go to the local one.


Calculate ticket sales by using SUM in SELECT clause

Try this:

SELECT
A.ticket_type_id,
A.tickets_paid,
A.tickets_paid*B.price as amount_paid
FROM
(SELECT tt.ticket_type_id,
COUNT(tt.ticket_type_id) AS tickets_paid
FROM tbl_tickets t
    INNER JOIN tbl_ticket_count tc
        ON t.ticket_count_id = tc.ticket_count_id
    INNER JOIN tbl_events_new e
        ON tc.event_id = e.event_id
    INNER JOIN tbl_ticket_type tt ON
        tt.ticket_type_id = tc.ticket_type_id
WHERE
    e.event_id = 164
    AND t.pay_status ='paid'
GROUP BY tt.ticket_type_id) as A
INNER JOIN tbl_ticket_count B ON
        A.ticket_type_id = B.ticket_type_id


SQL Server 2008 Patch on Clustered Server

2008 and later - patch passive node (B), failover, patch new passive (A)...done.


How to find unused logins in SQL Server?

I have something really ugly I made years ago. I just need to implement a dynamic SQL to automatically execute this "select". well here it goes:

First create this report table:

CREATE TABLE #REPORT ( [DATABASE] VARCHAR (MAX),[USER] VARCHAR (MAX))

Then execute this query and run it:

select 'use ['+name+'] '+char(13)+
'insert into #report
            select db_name(db_id()) collate sql_latin1_general_cp1_ci_as as [database] ,
                   name collate sql_latin1_general_cp1_ci_as as [user]
    from sys.database_principals
        where type in (''s'',''u'')
            and name not in
                (''dbo'',''guest'',''information_schema'',''sys'')'+char(13)+char(13)
from sys.databases
    where name not in ('master','model','msdb','tempdb')

Then you can have:

--login without user
select name from sys.server_principals
    where name not in
        (select [user] from #report)
        order by 1

AND

--user without login
select [database], [user] collate latin1_general_ci_as
    from #report
        where [user] not in
            ( select name collate latin1_general_ci_as
                from sys.server_principals)
order by 1


Caching results of query for part of table that almost never change

It's best not to use a count(*).  Instead, use a count(PrimaryKey), where the Primarykey is whatever your PrimaryKey happens to be.

For example, if the PrimaryKey is EmplID, then use count(EmplID).  If you don't have a PrimaryKey, create one.  Without a PrimaryKey, the query will always use a table scan (where it looks at every single row to find the data) rather than using an indexed seek. Think of it like a filing cabinet. If all your records are organized alphabetically, you always know where to find customers whose names start with "F". If it's not organized, you have to look through every single piece of paper to find the right customer records.

For each of your WHERE conditions, make sure each field is indexed.  Field1 should be indexed, Field2 should be indexed.  It doesn't matter if the value changes.  As long as the field is indexed and you REBUILD or RECREATE the index often, it should work just fine.

You shouldn't need Query2 and Query3.  If you decide to use Query2, make sure the Creation_Date is indexed as well.


Why does my INSERT fail on SELECT permissions when I have change tracking enabled?

Change tracking operations are running within the user's context, and in order to track what is being changed it has to read the row using a SELECT before it is changed.


What does a DBA have to know about SSAS?

A quick overview of SSAS for DBAs

So, you're a SQL Server DBA and you've just inherited some cubes out of the blue to manage.  A quick crash course on SSAS administration seems to be in order.

From an administrative point of view, SSAS is a fairly straightforward, if resource hungry application.  It's way simpler than a DBMS platform, although different in several ways.  Also, there are a few administrative tasks such as usage based optimisation specific to SSAS that you might have to get your head around.

Back up your configuration file

All config data lives in a file called msdmsrv.ini.  It's an XML file.  If you tinker with the config through SSMS (Connect to the server, right click on the server, select properties) you can set options that will break the server on start up.  Take a copy of msmdsrv.ini before you play with anything.

Important parameters

Memory: SSAS is a biblical memory hog.  It likes 64 bit builds if possible, and lots of memory.  The parameters 'Memory\LowMemoryLimit' and 'Memory\HighMemoryLimit' control memory usage policies.  LowMemoryLimit is not a minimum memory allocation.  It is a threshold where SSAS considers the system is low on memory and starts flushing stuff out of its caches.  HighMemoryLimit is the absolute maximum it will use.

Note that SSAS stores its data in files (lots of files - it doesn't have any mechanism equivalent to filegroups), so it makes a lot of use of O/S file system caching for those files.  Note that the default for these limits is about 65% and 80% of the machine's memory respectively, so if you want the OLAP server to coexist with a SQL Server instance you will need to turn those down so it isn't fighting over memory with the database server.

Directories:  Five parameters of interest cover this: DataDir, AllowedBrowsingFolders, BackupDir, LogDir and TempDir.  DataDir and AllowedBrowsingFolders are the most important.

AllowedBrowsingFolders affects the list of folders that the OLAP server will put its data files in.  Anything with a user interface (e.g. the deployment wizard) will restrict your options to the list in AllowedBrowsingFolders.  The value is a pipe ('|') delimited list of directories.
DataDir is the default path for files.  If you intend to partition the cube over multiple volumes you will need to configure AllowedBrowsingFolders appropriately.
LogDir is where the server places various log files including flight recorder and query logs.  The flight recorder log is used for troubleshooting and the OLAP query log is used for usage based optimisation (more on this later).
TempDir is a location for temporary files that SSAS creates while processing.  If you are processing large data volumes and having performance issues, you may benefit from shifting this off onto another volume from the data.
BackupDir is what it says on the tin.

Miscellaneous:
A few miscellaneous parameters may also be of interest.  A couple of sets that you may have to tweak are:

DefaultMaxDrillthroughRows: This limits the size of drillthrough row sets.  You may need to fiddle this to allow more.
Threads/Timeouts: You may need to adjust these.  I've never had to bother.

Those are the basics.  You may need to tweak the others for specific reasons, but you can do your homework on that.

A reference guide for the SSAS server properties can be found here.

Operations

Deployment:  You can compile a project in BIDS and get a set of files that can be deployed with the deployment wizard.  You may need to adjust file paths for partitions and a few other things.

Programmed and batched admin tasks: Commands are issued to SSAS through a web service API called XML/A.  Microsoft provides an interactive tool for issuing MDX and XML/A connands.  If you have to embed MDX in an XML/A command, watch out for the need to use XML escapes such as &amp;.  This is not an issue with the MDX editor and query tool in SSMS.

Offline jobs can be done through various SSIS cube processing tasks, a command line utility called ascmd.exe or a .Net API called AMO.  You can also get various powershell tools and suchlike.  ascmd.exe takes an XML/A file and posts it to the server.  If you have to frig the file programatically you might be better off working with little .Net tools rather than trying to manipulate XML files from a .cmd script.

The operations guide goes into this in more detail.

Security

Security on SSAS is fairly simplistic.  It has a global 'Server' role that has administrative permissions across the whole system.  Unfortunately you need 'Server' to create databases, so it's quite likely you will need to grant it to developers on any development OLAP servers you have.

Other security can only be applied to individual cube schemas.  You can grant permissions to read, process, drillthrough, writeback and suchlike on individual items to roles within a schema.  Roles on OLAP schemas can be defined within BIDS and are deployed with the cube.  AD groups or users can be assigned to those roles through SSMS.

An example of how to programatically manage role membership can be seen here.

Usage-based optimisation

As a DBA you might well get involved in this one, but first a little backgrounder about the physical storage.  SSAS works by calculating and persisting pre-built aggregates along with the base data.  If a query can be satisfied by hitting an aggregate the OLAP server will use this in preference to the base data, as the aggregate will involve much less I/O and therefore it will be quicker to retrieve the data.

However, you have to work out which aggregates to calculate (i.e. what combinations of dimension attributes to generate rollups for).  BIDS has a tool that will take a guess at this and generate some for you.  Some tools, such as BIDS helper will also allow you to manually edit the aggregates.

Usage based optimisation works by taking a log of the actual queries issued to the server, and then using that log to work out a set of aggregates that would be optimal for those queries.  As a DBA, you may set up OLAP query logging to capture this data, and then run the optimisation on the cube.  A blurb about setting up the query log can be found here.

The tool for doing this is called the 'Usage based optimisation wizard.'  This lives in SSMS, and can be found by opening the partition int the explorer and selecting 'Usage Based Optimisation' off the right-click menu.

The performance guide goes into tuning in more detail.

MDX

MDX looks a bit like SQL but works very differently.  A treatise on 'MDX for SQL programmers' is an entire topic in its own right.  I suggest reading through some of the tutorials on it and/or getting a book on it.  Plus, the friendly folks here at dba.se can help with questions if you have any.

MDX doesn't have any concept of filtering rows.  The language has a whole lot of set operations to work out what to display on the various axes of the query1, plus 'SELECT'.  You can use with statements that look a bit like CTEs to define measures and sets.

Some introductory MDX programming resources can be found here and here (this one is quite old and very long winded).  There are also a few good books on the subject; this SO question has quite a bit of fan-out to SSAS resources.

1 Although it does have non-empty operators that restrict the results to combinations that actually have data.  Most MDX queries consist of defining what slices to show on the axes, and non-empty operators will be necessary to avoid queries returning combinatoric level quantities of empty cells.


How to force Redshift/Postgres to aggregate nth_value?

I imagine something like this should work:

select
    customer_email,
    nth_value(created_at, 1) over (partition by customer_email
                                   order by created_at),
    nth_value(created_at, 2) over (partition by customer_email
                                   order by created_at),
    nth_value(created_at, 3) over (partition by customer_email
                                   order by created_at),
    nth_value(created_at, 4) over (partition by customer_email
                                   order by created_at)
from fact_orders
where ...
limit 100


Difficulty Understanding Oracle DQL Procedures and Functions Coming from T-SQL

Oracle supports "table valued functions" as well. However they need a lot more boiler plate code than one would like. The main (annoying) "problem" is, that you need to explicitly define a type that can be returned:

create type stuff_type as object
(
  customer varchar(50),
  widgets_purchased number
)
/

create stuff_list as table of stuff_type
/

create function get_stuff(p_start_date date, p_end_date date)
   return stuff_list
   pipelined
as
begin
  for rec in (select customer, sum(widgets_purchased) as widgets_purchased
              from widgetsales
              where transactiondate >= p_start_date
                and transactiondate <= p_end_date
              group by customer)
  loop
    pipe row stuff_type(rec.customer, rec.widgets_purchased);
  end loop;
end;
/

The usage is also a bit different:

select *
from table(get_stuff(current_date - 7, current_date));

Some more examples and insights can be found here: https://oracle-base.com/articles/misc/pipelined-table-functions

Starting with Oracle 12.1 you can also use implicit results:

create or replace procedure get_stuff(p_start_date date, p_end_date date)
as
  c1 SYS_REFCURSOR;
BEGIN
  OPEN c1 FOR
    select customer, sum(widgets_purchased) as widgets_purchased
    from widgetsales
    where transactiondate >= p_start_date
      and transactiondate <= p_end_date
    group by customer;

  DBMS_SQL.RETURN_RESULT(c1);
END;
/

Some more details and examples for implicit results: https://oracle-base.com/articles/12c/implicit-statement-results-12cr1


Backing up the database without compromising performance

120GB should not take 2 hours

Do you have a separate disk array for backups, with a separate network interface to move the backups off the server?
If you have Enterprise Edition, can you use mirror backups
Can you do weekly full backups and daily differentials?
Is backup compression turned on (Standard in SQL Server 2008 R2+, Enterprise only in SQL Server 2008)

And without more details, it will be hard to be more specific


Should every table have a single-field surrogate/artificial primary key?

I'm going to say no, not always, but most of the time yes..

These are some circumstances in which you don't need a surrogate or artificial key:

Pure intersection tables. If there is no risk of the
intersection being the target of a foreign key and if there is little
or no risk of the intersection attracting independent attributes
(i.e. something other than FK's to the two parent tables) then you
can get away with using the combination of FKs as the PK with fair
confidence.
Lookup tables with static business keys. If you have a lookup
table with a unique business key which is fixed externally to your
business and which has zero chance of ever changing for any
practical purpose, then using the business key directly can make
things simpler.  An example might be a list of state or province
codes or a list of ANSI standard numbers, etc.
Tables containing data consolidated from multiple, independent
sources.  If your system has many sources of data that must be
shoehorned together into a single table, say at head office, then
sometimes you need a compound key that includes the source system key
value and a code indicating what the source system was.

There are also some situations where the old-faithful monotonically increasing integer surrogate key is not ideal.  You can have keys that are alphanumeric surrogates.  These could include:

Situations where you need to merge data from multiple, independent
sources.  To avoid key collisions you might use GUIDs instead of
IDENTITY keys.
Situations where you are forced to use non-numeric key
representations.  Let's say you've got a license plate database.
Your key could be the alphanumeric value instead of a pure number.
Situations where some external requirement forces you to apply
compression to your key value.  Instead of using 10 digits for an
int32 you can use six base 36 digits.

Why most of the time yes?  The most fundamental answer to that question is that it is pure hell if you ever need to modify a primary key value on any table.  Since almost anything a user can see or touch is conceivably subject to an update at some point, using a visible key value is inviting pure hell.  Using a surrogate key will keep you from falling into this trap.

Having said that, remember that there is room for YAGNI in applying this concept.  You don't need to go forcing code tables with IDENTITY keys into every nook and cranny of your schema, just in case someone decides that the symbol for male gender in your employee table needs to change from M to X or something silly.


Extract all records even with missing "optional" fields in another table

What you need is an outer join, likely a left outer join. From the documentation

  Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.

For you the "left" table will be your product table and the other table is the attributes. The query will be

select <some columns>
from ICITEM as p
left outer join ICITEMO as o
  on o.FMTITEMNO = p.FMTITEMNO
  and o.OPTFIELD = 'COLOR';


mysqld: Can't create/write to file '/home/mysqltmp/ibXXX' Permission denied

From What to Do if MariaDB Doesn't Start

MariaDB's systemd unit file restricts access to /home, /root, and /run/user by default. This restriction can be removed by setting the ProtectHome option to false for the MariaDB systemd service. For example, you can reconfigure the MariaDB systemd service to allow access to /home by executing the following commands:

tee /etc/systemd/system/mariadb.service.d/dontprotecthome.conf <<EOF
[Service]

ProtectHome=false
EOF
systemctl daemon-reload

ref

After this fix your permission:

chmod go-rw /home/mysqltmp/


SQL Server 2012 how to create new schema from existing database selecting only subset of database objects?

You can create a new schema and expose the objects you need with a synonym:

USE master;
GO

IF DB_ID('testDatabase') IS NOT NULL
BEGIN
    ALTER DATABASE testDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE testDatabase
END
GO

CREATE DATABASE testDatabase
GO

USE testDatabase;
GO

-- test table in schema dbo
CREATE TABLE dbo.test (
    i int primary key clustered
)
GO

INSERT INTO dbo.test VALUES (1),(2),(3)
GO

-- new schema for exposed objects
CREATE SCHEMA schema1
GO

-- synonym for dbo.test
CREATE SYNONYM schema1.test FOR dbo.test
GO

-- new user with no permissions on the dbo schema
CREATE USER testUser WITHOUT LOGIN
GO

-- grant permissions on the new schema only
GRANT SELECT ON schema::schema1 TO testUser
GO

-- change security context to testUser
EXECUTE AS USER = 'testUser'
GO

SELECT * FROM schema1.test; -- works

SELECT * FROM dbo.test; -- fails

-- revert to the original security context
REVERT
GO


Get all orders that are "ordered" but not "paid"

For a different method to obtain the orders that are only in a given state, such as "Ordered", but not "Recieved", you could use something like the following:

use tempdb;

CREATE TABLE dbo.OrderStatus
(
    OrderStatusID INT NOT NULL
        CONSTRAINT PK_OrderStatus
        PRIMARY KEY CLUSTERED
    , OrderStatusDesc VARCHAR(255) NOT NULL
);

CREATE TABLE dbo.Orders
(
    OrderID INT NOT NULL
        CONSTRAINT PK_Orders
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , OrderDesc VARCHAR(255) NOT NULL
);

CREATE TABLE dbo.Orders_OrderStatus
(
    OrderID INT NOT NULL
        CONSTRAINT FK_Orders
        FOREIGN KEY REFERENCES dbo.Orders(OrderID)
    , OrderStatusID INT NOT NULL
        CONSTRAINT FK_OrderStatusID
        FOREIGN KEY REFERENCES dbo.OrderStatus(OrderStatusID)
    , CONSTRAINT PK_Orders_OrderStatus
        PRIMARY KEY CLUSTERED
        (OrderID, OrderStatusID)
);

Populate some sample data:

INSERT INTO dbo.OrderStatus (OrderStatusID, OrderStatusDesc)
VALUES (1, 'Ordered')
    , (2, 'Paid')
    , (4, 'Received');

INSERT INTO dbo.Orders (OrderDesc)
VALUES ('TV')
    , ('Computer');

INSERT INTO dbo.Orders_OrderStatus (OrderID, OrderStatusID)
VALUES (1, 1) -- TV, Ordered
    , (1, 2)  -- TV, Paid
    , (1, 4)  -- TV, Received
    , (2, 1); -- Computer, Ordered

If you want to see the description of the order, you can do a simple join and an aggregate to get the list:

SELECT o.OrderDesc
FROM dbo.Orders o
    INNER JOIN dbo.Orders_OrderStatus oos ON o.OrderID = oos.OrderID
GROUP BY o.OrderID, o.OrderDesc
HAVING SUM(oos.OrderStatusID) = 1;

If, however, you only need a list of OrderIDs, you can simplify further to just:

SELECT oos.OrderID
FROM dbo.Orders_OrderStatus oos
GROUP BY oos.OrderID
HAVING SUM(oos.OrderStatusID) = 1;

If you wanted to see a list of orders that were both "Ordered" and "Received", you could change the above query to:

SELECT oos.OrderID
FROM dbo.Orders_OrderStatus oos
GROUP BY oos.OrderID
HAVING SUM(oos.OrderStatusID) = 3;

Indeed, if you need to see a list of orders that have been "Ordered", "Received", and "Paid", you would do:

SELECT oos.OrderID
FROM dbo.Orders_OrderStatus oos
GROUP BY oos.OrderID
HAVING SUM(oos.OrderStatusID) = 7;

The above uses SQL Server syntax, although it would be straight-forward enough to convert this to other RDBMSs that use SQL syntax.

The advantage of using this type structure is scalability.  Time required for searching orders with any given status is a linear, or O(n) operation.

Let's try it with a tiny bit more data.  In order to run this you'll need to drop the tables above, then recreate them.

Create 100,000 orders:

INSERT INTO dbo.Orders (OrderDesc)
SELECT TOP(100000) o.name + '.' + o1.name + '.' + o2.name
FROM sys.objects o, sys.objects o1, sys.objects o2;

Mark all of them as "Ordered":

INSERT INTO dbo.Orders_OrderStatus (OrderID, OrderStatusID)
SELECT TOP(100000) ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
    , 1
FROM sys.objects o, sys.objects o1, sys.objects o2;

Mark 60,000 of them as "Paid":

INSERT INTO dbo.Orders_OrderStatus (OrderID, OrderStatusID)
SELECT TOP(60000) ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
    , 2
FROM sys.objects o, sys.objects o1, sys.objects o2;

Mark 10,000 of them as "Recieved":

INSERT INTO dbo.Orders_OrderStatus (OrderID, OrderStatusID)
SELECT TOP(10000) ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
    , 4
FROM sys.objects o, sys.objects o1, sys.objects o2;

Select all orders that have been "Ordered", and "Paid":

SELECT oos.OrderID
FROM dbo.Orders_OrderStatus oos
GROUP BY oos.OrderID
HAVING SUM(oos.OrderStatusID) = 3;

On my desktop machine, this results in the following stats:

(50000 row(s) affected)
Table 'Orders_OrderStatus'. Scan count 1, logical reads 490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 286 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

With an execution plan:

Nice and efficient!


Taking a backup from a slave is exactly how I do it, and it works very well. I can't imagine a better way to take a backup without causing downtime of the master and your application.

I am intrigued by your statement about not being able to restore a backup onto a mac osx machine (this could probably be a different question). An alternative is to setup a virtual machine on your local mac machines that run the same OS your production servers run. A tool that makes managing those machines easy is Vagrant.

Mimicking the production environment has the added benefit of allowing you to test any restore procedure with a fairly high degree of certainty that it would work on the production servers should you need to.


I am going to go out on a limb here and try and answer even though you haven't provided a lot of details.

It looks like you want to create a comma-separated list of the columns and then the values below it but in sqlplus you are getting the column header as the actual SQL string.  If I were to use the following in a SQL Fiddle I will get the same result:

select
  '"'|| "ACCOUNT_ID"||'"'||','||
  '"'|| "MAJOR_VERSION"||'"'||','||
  '"'|| "MINOR_VERSION"||'"'||','||
  '"'|| "COUNTRY"||'"'||','||
  '"'|| "ACCEPTANCE_TIMESTAMP"||'"'||','||
  '"'|| "AGREEMENT_ID"||'"'
from THE_TABLE aaa
where aaa.country='CN'
and rownum < 10;

See Demo.  And the result is two rows, one with the column name but in this case it is the actual sql that we ran and then the row of data comma separated:

| '"'||"ACCOUNT_ID"||'"'||','||'"'||"MAJOR_VERSION"||'"'||','||'"'||"MINOR_VERSION"||'"'||','||'"'||"COUNTRY"||'"'||','||'"'||"ACCEPTANCE_TIMESTAMP"||'"'||','||'"'||"AGREEMENT_ID"||'"' |
|                                                                                "abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos" |

Now if I want to create the column headers as a row, I would alter the query to use a UNION ALL, the first query just creates a string of the column headers:

select
  '"ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","AGREEMENT_ID"' mycol
from dual
union all
select
  '"'|| "ACCOUNT_ID"||'"'||','||
  '"'|| "MAJOR_VERSION"||'"'||','||
  '"'|| "MINOR_VERSION"||'"'||','||
  '"'|| "COUNTRY"||'"'||','||
  '"'|| "ACCEPTANCE_TIMESTAMP"||'"'||','||
  '"'|| "AGREEMENT_ID"||'"'
from THE_TABLE aaa
where aaa.country='CN'
and rownum < 10;

See Demo. This gives me a result:

|                                                                                MYCOL |
| "ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","AGREEMENT_ID" |
|             "abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos" |


Unable to access tables in sql server using oracle dblink

One potential cause could be that the default database listed for the user account being used to connect to SQL Server is pointed to the wrong database.  Adjust this on the SQL Server side and see if that resolves your issue.


postgresql bigserial or sequence using jpa

bigserial is just shorthand for creating a sequence. So if you include the time it takes to type, bigserial has better "performance" :)

CREATE TABLE tablename (
    colname BIGSERIAL
);

Equals

CREATE SEQUENCE tablename_colname_seq;

CREATE TABLE tablename (
    colname biginteger DEFAULT nextval('tablename_colname_seq') NOT NULL
);

Also, if you these are your primary keys, then they are already indexed, so you don't need to index them (foreign key columns aren't indexed automatically though, FYI).

For JPA, you should use @GeneratedValue(strategy=GenerationType.IDENTITY) for PostgreSQL serial/bigserial columns.


How can sqlplus knows where ORACLE_SID point?

When you use SQLPlus and make a bequeath connection (e.g., not going through the listener and not specifying the SID/Service Name in the connect string or using a TNS alias), SQLPlus/Oracle uses the combination of ORACLE_SID and ORACLE_HOME to uniquely identify the instance that it connects to.

Important to note: You may have multiple instances running on one server which all share the same OH, but they will have different SIDs.  Or you could have multiple instances running from multiple Oracle Homes.  Two instances on one machine will never share the same combination of SID and OH.

It's important to note that this connection method will only work when you're making connections from the command line of the server that the Oracle database/listener is running on.

If you're connecting from a remote server (or from the same server if you need to connect to a specific service - e.g. a PDB in 12c), you must specify in the connect string one of either a tnsnames alias, or the hostname [optional: port] and the service name you're connecting to.

e.g,:

sqlplus scott/tiger@hr

This assumes the tnsnames.ora on the client is set up with an alias for "hr".  This TNS alias will have the hostname, port, and SID/Service Name in it.

sqlplus scott/tiger@hostname[:port]/servicename

This connects to the hostname and SID/Service Name provided on the command line.  ORACLE_SID, ORACLE_HOME variables and TNS are entirely unrelated to this connection and are not used.

A real example:

sqlplus hr/hr@productiondatabase.company.com:1521/hrdb


How can I return multiple rows of records in PL/pgSQL

The function needs to return a SETOF RECORD instead of RECORD and have one RETURN NEXT per row instead of a single RETURN, as in:

CREATE FUNCTION test() RETURNS SETOF RECORD AS $$
DECLARE
 rec record;
BEGIN
  select 1,2 into rec;
  return next rec;

  select 3,4 into rec;
  return next rec;
END $$ language plpgsql;

Caller:

=> select * from test() as x(a int ,b int) ;
 a | b
---+---
 1 | 2
 3 | 4
(2 rows)

Note that SQL being strongly and statically typed, the RECORD pseudo-type is hard to work with.
Often it's less cumbersome to use right from the start a composite type with a full definition of names and type for each column, either with the TABLE(...) syntax for an anonymous type or with CREATE TYPE for a persistent named type.


pg_restore: [archiver] unsupported version (1.11) in file header postgresql

PostgreSQL really doesn't put much effort into supporting moves from newer major versions back to older ones.  One big reason for that is that newer versions usually support features which you just can't install on older versions; although any given database might not be using any of the new features, the project philosophy is that they would rather not support it than to support it in a buggy or incomplete fashion.

Your best bet might be to generate a "plain" format dump rather than a "custom" format dump.  This is just a text file of SQL commands.  If you aren't using any new features, it might just apply cleanly.  If not, you can look into the errors and decide what to do for a back-port.


Proper Index for WHERE X  1 AND Y  1 ORDER BY Z

In general it is not possible to use an index seek on a condition x <> 1 and y <> 1.

With an index on x,y the best you can do is convert it into two range seeks (x < 1 and  x > 1) with a residual predicate on y <> 1 (and this wouldn't be able to use additional index key columns to avoid a sort)

For a bit column as it can only have three values. 0, 1, NULL logically WHERE bit_column <> 1 is equivalent to  WHERE bit_column = 0 but seems SQL Server doesn't take advantage of that here and convert the <> to = conditions for you.

Adding a couple of check constraints does the job though even though these are apparently redundant in that they don't actually restrict the allowable values for the datatype in any way (for NULL if a check constraint evaluates to UNKNOWN it counts as passing)

CREATE TABLE MyTable
  (
     Foo     INT,
     IsFlag1 BIT NULL CHECK (IsFlag1 IN (0, 1)),
     IsFlag2 BIT NULL CHECK (IsFlag2 IN (0, 1)),
     SomeId  INT
  );

CREATE NONCLUSTERED INDEX ix
  ON MyTable(IsFlag1, IsFlag2, SomeId)
  INCLUDE (Foo);

The plan now does show a seek on IsFlag1 = 0 AND IsFlag2 = 0

Or alternatively this filtered index also avoids the need for a SORT

CREATE NONCLUSTERED INDEX ix
           ON MyTable(SomeId)
           INCLUDE (Foo,IsFlag1, IsFlag2)
           WHERE IsFlag1 != 1 and IsFlag2 != 1

It does a scan of the filtered index (the qualifying rows ordered by SomeId) with a TOP to stop scanning after the 1,000 rows are retrieved. IsFlag1, IsFlag2 are INCLUDE-d in the index to avoid an unnecessary look up that occurs without this.


Sql restrict insert records for the same client based on 2 columns

It appears to me that this was asked with a similar context in a StackOverflow Question.

Using that as a foundation I think a Create Trigger will give you what you are looking for.

CREATE TRIGGER dbo.UniqueClient ON dbo.Records
AFTER INSERT
AS
IF EXISTS
(
    SELECT *
    FROM dbo.Records r
        INNER JOIN inserted AS i
            ON r.Client = i.Client
    WHERE r.IsActive = 1
    OR r.HasSession = 1
)
BEGIN
RAISERROR ('This Client Already Exists with an Active account or Session', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;

The Key to this process is if the incoming row (represented in the inserted table) matches an existing client and that existing client has IsActive = 1 OR HasSession = 1, SQL will roll back the transaction and stop the insert.  An error message is sent back to the application prior to the rollback.

Does this give you what you are looking for?


Backup and restore Transparent Data Encrypted database

Is there a simple way of finding the certificates' files

On source server:

declare
    @SourceDb sysname = 'MyDb',
    @Thumbprint varbinary(20),
    @Cert sysname,
    @BackPath nvarchar(260) = 'C:\temp\Cert.bak',
    @KeyPath nvarchar(260) = 'C:\temp\Cert.key',
    @Pwd sysname = '&DoN0tUseTh1$',
    @SQL nvarchar(max)

select @Thumbprint = encryptor_thumbprint
from sys.dm_database_encryption_keys
where database_id = db_id(@SourceDb)

select @Cert = name from master.sys.certificates
where thumbprint = @Thumbprint

select @Cert '@Cert'

set @sql = 'BACKUP CERTIFICATE ' + quotename(@Cert) + ' TO FILE = '''+@BackPath+'''
WITH PRIVATE KEY (
FILE = '''+@KeyPath+''',
ENCRYPTION BY PASSWORD = '''+@Pwd+''');'
exec(@sql)

  and then restore

On destination server:

declare
    @DestDb sysname = 'MyDb',
    @Cert sysname = 'MyTDECert',
    @BackPath nvarchar(260) = 'C:\temp\Cert.bak',
    @KeyPath nvarchar(260) = 'C:\temp\Cert.key',
    @Pwd sysname = '&DoN0tUseTh1$',
    @SQL nvarchar(max)
set @sql = 'USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD  = '''+@Pwd+''';'
exec(@sql)
if @@error<>0 print
'You already have a DMK - do not drop it.
Instead, BACKUP MASTER KEY TO FILE...
and secure its backup.
Also BACKUP SERVICE MASTER KEY TO FILE...
and secure its backup.'

set @sql = 'USE master;
CREATE CERTIFICATE '+quotename(@Cert)+'
FROM FILE = '''+@BackPath+'''
WITH PRIVATE KEY
(FILE = '''+@KeyPath+''',
DECRYPTION BY PASSWORD = '''+@Pwd+''');'
exec(@sql)

  What's the proper direction for restoration from backup here?

Now restore the database.
After securing the keys and backups off of the 2 servers: Permanently shift+delete them (which in above examples were saved or copied to C:\temp).

  Are these significant? There's only one cert from that output that doesnt have ## and I'm not sure if that applied to a db that I'm not interested in...

They are significant - leave them be :). A cert's thumbprint is used (in above code on the source server) to find the cert which encrypted the db. More than likely it is the cert without the double hashes in the name, but checking  sys.dm_database_encryption_keys is better than guessing :).


Use:

set showplan_xml on;
go
Your query;
go
set showplan_xml off;
go

This will give the XML, and you should be able to see the Estimates in there.


How to dig deeper into ASYNC_NETWORK_IO waits

ASYNC_NETWORK_IO is never a server side problem.
This wait indicates SQL Server has results to send to the client, but is waiting for the client to consume the results so it can finish sending.
It's possible (though unlikely) that a slow network is the bottleneck. This almost never is the reason, unless someone is using dial up quality network connections. The fix here is a faster network connection. (But that's not your problem.)
It's also possible that the application is explicitly stopping it's consumption of results due to bad design. This will happen if the application accepts the first 25 rows to display, then stops accepting results forcing the server to wait.... And only taking the next 25 rows when the user hits the "next page" button. In this case, you'd need to change the application to either consume the whole result set at once, then handle pagination just in the display, or to change the app to simply ask for one page of results and do a round trip for now 6 every time the user hits "next page".
Lastly, it could be a client machine that is underpowered, or a poorly performing application. SSMS is one of those poorly performing applications. If you do a SELECT * FROM dbo.HugeTrillionRowTable you'll see ASYNC_NETWORK_IO waits pile up for your session. The right fix here depends on the details. You might need to add more memory to the client machine, or scale out to more client containers, or have a code change in the app (that's the fix for SSMS, but it's not a trivial change).
In your case, if you can get read only access to SolarWinds DPA, you will be able to drill into that wait to detrimine the query, client, etc to get into those details. If you can't access DPA, you can monitor sys.dm_exec_session_wait_stats, though this will only show you info for currently running queries, so you'll need to catch the offending code in the act. There isn't a "time machine" DMV to go back after the fact.


Is it possible to fetch or update table data by table field type?

Yes, it's very possible to accomplish that but not with a unique query!

First, you need to know where to look for metadata: This kind of information is stored into an internal mysql database called information_schema, this database in used only internally by Mysql every time you execute any kind of query involving any database entities. There you will find all sort of meta information about all the tables, columns, privileges, users, triggers of your databases:

show tables from information_schema;

The solution I know for your problem involves two queries. First one will return a list of columns with a given data_type (you can specify the database, and table you want to look for too):

select columns.column_name from
    information_schema.schemata
    ,information_schema.tables
    ,information_schema.columns
where
    tables.table_schema = schemata.schema_name
    and tables.table_name =  columns.table_name
    and schemata.schema_name = 'your_database_name'
    and tables.table_name = 'your_table_name'
    and columns.data_type = 'the_field's_data_type_you_want_to_filter'
    -- + any other filter you find useful

With the list of column names that satisfy the properties you want (a given data type, in your specific case) all you need to do is a secondary query to select or update data from that list of fields.

I searched in MySql documentation and unfortunately there is no way to select or update a dynamic list of fields only with SQL, thats why a secondary generated query is needed.

Hope this helps.


Reading from Postgres SQL in a Jupyter Notebook

You didn't specify important keyword "FROM":

df = pd.read_sql('SELECT * "xyz"', conn)

so the right one is:

df = pd.read_sql('SELECT * FROM "xyz"', conn)


High rise in Aborted_connects on mysql slave servers

How to solve Aborted_Connects problem

1) Increase the wait_timeout variable value in [my.cnf]

2) Set the log-warnings variable to greater than 1. This will start logging the aborted connections in the mysql error log file once you identified and fixed the problem you can set it back to 1 again.

Also, You can have a look at this forum discussion which is relevant to Load balancer which checks database health every second
http://forums.mysql.com/read.php?144,194590,194590#msg-194590


Count specific query occurred in Redshift

IMO your query looks good. I have one idea.

Instead of:

    AND starttime BETWEEN '2017-01-24 00:00:00' AND '2017-01-25 00:00:00'

You can write:

    AND starttime::date = '2017-01-24'

The double column operator (::) converts DateTime type to Date, resulting shorter query.


Does PostgreSQL do any kind of caching of query results on its own?

It will execute it each time.  It will cache the data it needs to do the execution (assuming that is small enough to stay in cache), but not the results.  Who knows, maybe the results have even changed, what with other people being allowed to do inserts, updates, and deletions at the same time as you are looping over your 10,000 executions.


What Current Continuous SQL Server Backup Solutions Are there?

Well, you can keep using mirroring. "Deprecated" does not mean "doesn't work any more" - it just means that, at some point in the future, it won't be supported when you move to some version > 2012 (probably at least 3 versions later, maybe more given the backlash this announcement has caused).

By then, I am sure they will have a replacement for mirroring for Standard Edition, or will have simply deprecated Standard Edition, too. At that time, not today, you can make a decision to (a) use the replacement (or upgrade to Enterprise), (b) stay on the version you're on, or (c) switch to a different RDBMS.

Deprecation isn't exactly the jump-the-ship right now or sink ultimatum a lot of people infer. For example, they deprecated "statements that don't end with semi-colons" back in 2008. Are you adding semi-colons to every statement you write, today, on current versions of SQL Server? Nope, I really doubt it. Do you need to abandon mirroring this instant, or even in the next three years? Nope, not even close.


How does the PowerShell SQLPS SQLSERVER provider detect central management servers in SQLRegistration\Central Management Server Group

Shawn's explanation about discrepancies between SQL Server version seems on target. For this reason, I believe the SMO is a more stable method. I'm going forward using the below code.

Import-Module SQLPS -DisableNameChecking

Function Parse-ServerGroup($serverGroup)
{
    $results = $serverGroup.RegisteredServers;
    foreach($group in $serverGroup.ServerGroups)
    {
        $results += Parse-ServerGroup -serverGroup $group;
    }
    return $results;
}

Function Get-ServerList ([string]$cmsName, [string]$serverGroup, [switch]$recurse)
{

    $connectionString = "data source=$cmsName;initial catalog=master;integrated security=sspi;"
$sqlConnection = New-Object ("System.Data.SqlClient.SqlConnection") $connectionstring
    $conn = New-Object ("Microsoft.SQLServer.Management.common.serverconnection") $sqlconnection
    $cmsStore = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)
    $cmsRootGroup = $cmsStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups[$serverGroup]

    if($recurse)
    {
        return Parse-ServerGroup -serverGroup $cmsRootGroup | select ServerName
    }
    else
    {
        return $cmsRootGroup.RegisteredServers | select ServerName
    }
}

Get-ServerList -cmsName "localhost\SQL2012" -serverGroup "Production" -recurse | Format-Table


BASH: DBCA:  command not found

DBCA should be lower-case.


Trying this today I get "rds-modify-db-parameter-group:  Malformed input-The parameter innodb_log_file_size cannot be modified." so I guess they disabled that, which is pretty silly.


Question on Oracle Public DB link inside Procedures

This depends on how the link was set up. A public link means it is accessible to anyone to access the link but the credentials used to access the remote DB can either be those of the current user or a specific user.

Take a look at CREATE DATABASE LINK for the full syntax and explanations.


Better is to avoid cursors.  Remember that SQL loves to do things in bulk, but hates to work with Cursors.

UPDATE  psdms_feature_value_lang t1
    JOIN  ordernumbers AS o  ON ...
    LEFT JOIN  psdms_feature_product t2
          ON t1.id_feature_value = t2.id_feature_value SET
    value  ='95090€'
    WHERE  t2.id_product = 1
      AND  t2.id_feature = 510
      AND  o.id_product
      AND  o.id_feature = 510;
INSERT
     INTO  psdms_feature_value ( id_feature )
SELECT  510
    FROM  ordernumbers
    WHERE  o.id_product
      AND  o.id_feature = 510;
INSERT
     INTO  psdms_feature_product ( id_feature, id_product, id_feature_value)
SELECT  o.id_feature , o.id_product , o.id_feature_value
    FROM  ordernumbers
    WHERE  o.id_product
      AND  o.id_feature = 510;

Index:

ordernumbers: INDEX(id_feature, id_product, id_feature_value)

Other notes:

== is not proper syntax

What do you mean by AND id_product?  It probably tests for non-zero.


You can use AZCopy to move files from blob to Filesystems or eitherwise..

Below is the syntax..

AzCopy
/Source:https://myaccount.blob.core.windows.net/mycontainer
/Dest:C:\myfolder
/SourceKey:key
/Pattern:a
/S

use the pattern option to specify name (this will work like SQL like query)

From SQLServer 2014,you can also restore from Blob directly..

Restoring From Backups Stored in Microsoft Azure


Would db corruption be further minimized if I remove querying on a backend table from the user's front end?

If by 'querying' you mean the control is simply running a select statement against the data table, it is doubtful that would corrupt any data.  If your control's query is manipulating data (add, delete, change) then it could very well be corrupting the data.

Thirty users on an Access DB is challenging.  You may want to look into using a more powerful DB for the backend.  If you don't, be sure to repair and compact your Access DB nightly.


Job waiting for resources - is it better to kill it or let it wait

The best option is to resolve the resource contention when possible. Based on your question, you seem to be seeing waits for memory grants. In the comments, you stated that your server has 4 TB of memory. By default, SQL Server will allow a single query to get a grant up to 750 GB. Are there any queries that really need that much memory? Keep the following in mind:

By design, SQL Server asks for a generous amount of memory for its memory grants.
Queries may ask for extremely large grants if the queries have poor cardinality estimates. I've seen queries ask for more than a TB of memory when less than 20 GB was sufficient.
Even if a query gets a huge memory grant it may still spill to tempdb. This is because a query's memory grant need to be shared between different operators and the split is determined before the query runs.

I suggest looking into the queries that request the most memory and using Resource Governor or the MAX_GRANT_PERCENT hint to reduce the size of the largest grants. This is a trade off. You may see worse performance for some queries if they start spilling more data to tempdb. However, you may be able to reduce the grants without degrading performance at all. It all depends on the queries that run on your system and how many are running at a time.


hye i tried below query in mysql and SQL server databases its working fine, so please check once again. and tell me which database u tried.

SELECT t1.id, t2.value
FROM table1 t1
LEFT JOIN table2 t2
    ON t2.id = t1.id

id   value
--  --------
 1       100
 2       200
 3    (NULL)
 4    (NULL)

Thanks.


The query for populating the users_club_meta table looks good to me except for two issues:

I am not entirely convinced about HAVING COUNT(*) > 1. If you want to create entries for all combinations of (user_id, name, function, description), remove that condition.

If you want to omit the groups consisting only of empty school_grade values, you can use this condition instead:

HAVING COUNT(school_grade) > 0

That is assuming that by "empty" you mean NULL. If you mean to exclude empty strings ('') as well, you could modify the above condition like this:

HAVING COUNT(NULLIF(school_grade, '')) > 0

You are selecting id in that query but the column is not in GROUP BY. It is true that MySQL allows you to do that but the manual also discourages that you do that in cases where the non-aggregated non-GROUP BY column has multiple values per group.

If you want predictable results, use an aggregate function, for instance MIN():

INSERT INTO
  user_clubs_meta (user_clubs_id, meta_key, meta_value)
SELECT
  MIN(id),
  'school_grades',
  CONCAT('[', GROUP_CONCAT(CONCAT('"', school_grade, '"')), ']')
FROM
  user_clubs
GROUP BY
  user_id, name, function, description
HAVING
  COUNT(school_grade) > 0
;

For the DELETE query, take the final draft of the INSERT statement's SELECT query keeping only MIN(id) in the SELECT clause and removing the HAVING clause:

SELECT
  MIN(id)
FROM
  user_clubs
GROUP BY
  user_id, name, function, description
;

That gives you the first ID of every unique combination of (user_id, name, function, description). That will be the rows you want to keep. All the others you want to remove. This is an anti-join, and there are various ways to implement it. In this case you can go with:

NOT IN:

DELETE FROM
  user_clubs
WHERE
  id NOT IN
  (
    SELECT MIN(id)
    FROM user_clubs
    GROUP BY user_id, name, function, description
  )
;

LEFT JOIN + WHERE IS NULL:

DELETE
  uc
FROM
  user_clubs AS uc
  LEFT JOIN
  (
    SELECT MIN(id) AS min_id
    FROM user_clubs
    GROUP BY user_id, name, function, description
  ) AS filter
  ON uc.id = filter.min_id
WHERE
  filter.min_id IS NULL
;

The left join method is known to work fast in MySQL but you should test for yourself in your environment to choose what works better for you.


"could this potentially affect SQL Server's read/write performance?"

yes.  Here are some things to consider:
1 - The type of file(s) matters in context of performance.  Files which are often read/written to will of course share the resources with the SQL Server files. Files which aren't being accessed when SQL Server files are being accessed won't noticeably affect performance.
2 - The type of storage matters.  Shared IO has more severe performance consequence on spinning disk storage than SSD.
The idea is similar to the "Placing both data AND log files on the same device can cause contention for that device, resulting in poor performance. Placing the files on separate drives allows the I/O activity to occur at the same time for both the data and log files." as stated in Microsoft docs.  Read placing any two files with simultaneous IO demand on the same device can cause contention for that device, resulting in poor performance.
Will it cause poor performance? Far too many variables to take into consideration such as storage specs & config, workload patterns of both SQL Server and w/e is sharing its resources, etc. to have concrete answer.


SQL Server AG databases went into 'Synchronizing/In recovery' after upgrading to 2014

The Availability Group prerequisites all indicate that you must be using Enterprise Edition and be using the same version of SQL Server.

See the MSDN link for SQL Server 2012

https://msdn.microsoft.com/en-us/library/ff878487(v=sql.110).aspx

Under the topic:  Checklist: Prerequisites (Server Instance) it says:

  Each server instance must be running the Enterprise Edition of SQL
  Server 2012.

Likewise 2014 and 2016 have the same prerequisites for only those versions.

While my response was focused on the requirement in general as noted in MSDN. However, Kin in his comment added details on upgrade path:

"In OP's case, he was upgrading secondary to a higher version which should be fine (once failover is initiated, it will be one way ONLY - no role changes are allowed), and then he might upgrade the primary to 2014. This is how upgrades should be done."


ERD - Multiple FKs with only one of them being filled for each record

Good design practice is to implement and enforce the rules of the problem domain. So if your rules lead to this design then that's what you've got. Having mutually exclusive values is not uncommon.

They can be implemented in different ways. One is as you describe. Another is to use table inheritance. With this you would have tables track, artist and band as you do currently but also band_track and artist_track. These will have only the foreign keys specific to themselves, defined as not null. The problem then becomes one of enforcing exclusion between band_track and artist_track. Personally, I don't think this approach actually improves our position much.

For my two cents I'd go with what you have. Declare CHECK constraints to prevent illegal combinations within a row (or use triggers if you absolutely must).


Oracle 9: Convert date from mm/dd/yyyy hh:mm:ss format to iso8601 formatted datetime

You should use SYSTIMESTAMP instead of SYSDATE, in order to get fractional seconds (which you desire as I assume).

SYSTIMESTAMP returns data type TIMESTAMP WITH TIME ZONE, thus you don't have to specify explicitly.

So it is:

SELECT
    TO_CHAR(
        SYSTIMESTAMP AT TIME ZONE 'UTC',
        'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'
    )
FROM dual


I would opt for the third option purely for readability and simplicity. Have you looked at the execution plans for all three, I expect they may give the same result for each. I would have expected the bracket query with the * would have been slower.


Does anyone ever use database sequences that count down from 2^64-1?

Never seen it done, certainly not for that reason.

Speaking for SQL Server, it would work fine with a Bigint Identity column. But you'd have to ask whether it's worth the confusion it could cause, and the need to remember to define the clustered index descending to prevent fragmentation, etc.


Selecting data from multiple rows into a single row

Join the table to itself.

So:

select yt1.demand_id, yt1.debit_status, yt2.customer
from yourtable yt1, yourtable yt2
where yt1.demand_id = yt2.demand_id
and yt1.debit_status is not null
and yt2.customer is not null;

Or (if batch_number can reliably be used to fetch the 2 rows needed to make a single row:

select yt1.demand_id, yt1.debit_status, yt2.customer
from yourtable yt1, yourtable yt2
where yt1.demand_id = yt2.demand_id
and yt1.batch_number = 1
and yt2.batch_number = 2;

SQL fiddle.


Query with WHERE NOT and NOT IN with subquery

Your unexpected results may be stemming from product_id being assigned to multiple categories in the oc_product_to_category table. If a product is in e.g. category 1 AND category 73, it will still be returned by the inner query and thus excluded from the outer query.

Produce a simple set of test data

INTO oc_product INSERT (product_id,  model, tax_class_id) VALUES
(1, 'model5', 10),
(2, 'AA', 5),
(3, '12', 8),
(4, '14', 12),
(5, 'xx', 12)

INTO oc_product_to_category INSERT (product_id, category_id) VALUES
(1, 1), -- product_id is in a category that is not in that list
(1, 73), -- product_id is STILL in a category that is not in that list; as well as a value in that list
(2, 73), -- product_id is only in categories in that list
(3, 17), -- product_id is only in categories not in that list
(4, 33), -- product_id is only in categories not on the category list, is excluded because of tax category
(5, 73), -- product_id is only in categories in the list, is excluded because of tax category

(I probably have the syntax of the INSERT query backwards;) )

I believe your current query will return

(2, 'AA', 5),

You probably want something like

SELECT
    op.`product_id`,
    op.`model`,
    op.`tax_class_id`
FROM
    oc_product AS op
  LEFT JOIN
    (SELECT DISTINCT p2c.product_id
     FROM oc_product_to_category as p2c
     WHERE
         p2c.category_id IN (73, 78, 116, 119, 120, 121, 122, 123, 125, 126, 127, 143, 159, 170, 176, 183, 211, 212, 213, 250, 260, 389, 433)
    ) as OnlyProductsBelongingToTheseIncluded
  ON op.product_id = OnlyProductsBelongingToTheseIncluded.product_id
WHERE
    ( op.tax_class_id <> 12 ) AND
    OnlyProductsBelongingToTheseIncluded.product_id IS NOT NULL

This should return

(1, 'model5', 10),
(2, 'AA', 5)

3 is excluded because it only exists in other groups;
4 and 5 are excluded because of the tax code

Changing the last line to
AllProductsBelongingToTheseExcluded.product_id IS NULL (and the appropriate subquery alias)
should return only the (3, '12', 8) datapoint


altering a db2 table with huge amount of data

Did you check the db2 diag log? what does it say?

The first thing comes into my mind it is that probably, the database does not rollback when the transaction log is full, but instead your updates never finishes.

Check the database logging parameter to see if there is enough log space to record the transaction.

BLK_LOG_DSK_FUL http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0006131.html


Trigger that will stop from deleting if a foreign key record is not deleted

As Scott Hodgin pointed out, you can't delete a country meanwhile there are cities belonging to this country.

DELETE FROM Countries WHERE Name = 'Germany';

  Msg 547 Level 16 State 0 Line 1
  The DELETE statement conflicted with the REFERENCE constraint "FK_Cities_Countries". The conflict occurred in database "db", table "dbo.Cities", column 'CountryId'.

You could change NO ACTION by ON DELETE CASCADE

CONSTRAINT FK_Cities_Countries
    FOREIGN KEY (CountryId) REFERENCES Countries(Id)
    ON DELETE CASCADE

But according to your question you want a trigger that blocks delete action for some Cities if it Country still exists.

CREATE TRIGGER trgCities_Delete
ON Cities
INSTEAD OF DELETE
AS
BEGIN

    DELETE c
    FROM   Cities c
    JOIN   deleted d
    ON     c.ID = d.ID
    WHERE  NOT EXISTS (SELECT 1
                       FROM   Countries
                       WHERE  Id = d.CountryId);
END

Then when you try to create the trigger you'll receive next error:

  Msg 2113 Level 16 State 1 Line 1
  Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'trgCities_Delete' on table 'Cities'. This is  because the table has a FOREIGN KEY with cascading DELETE or UPDATE.

Humm, that's a catch-22 situation.

Ok, let me remove ON DELETE CASCADE action and now I try to delete some city.

DELETE FROM Cities WHERE Name = 'Barcelona';

SELECT * FROM Cities;

Id | CountryId | Name
-: | --------: | :--------
 1 |         1 | Berlin
 2 |         1 | Dresden
 3 |         2 | Madrid
 4 |         2 | Barcelona
 5 |         3 | Rome
 6 |         3 | Milano

Barcelona is still there. The trigger works as expected.

Now try to delete 'Spain':

DELETE FROM Countries WHERE Name = 'Spain';

  Msg 547 Level 16 State 0 Line 1
  The DELETE statement conflicted with the REFERENCE constraint "FK_Cities_Countries". The conflict occurred in database "db", table "dbo.Cities", column 'CountryId'.

Catch-22 again. I can't delete Barcelona neither Spain!! That's a bit mental.

Let me suggest another approach. If you need to avoid deletes, flag the affected records using some field and let foreign keys do their job.

ALTER TABLE Cities ADD Blocked int NULL;

UPDATE Cities
SET    Blocked = 1
WHERE  Name = 'Berlin';

Now change the trigger by:

ALTER TRIGGER trgCities_Delete
ON Cities
INSTEAD OF DELETE
AS
BEGIN

    DELETE c
    FROM   Cities c
    JOIN   deleted d
    ON     c.ID = d.ID
    AND    c.Blocked <> 1;

END

DELETE FROM Cities WHERE Name = 'Berlin';
SELECT * FROM Cities;

Id | CountryId | Name      | Blocked
-: | --------: | :-------- | ------:
 1 |         1 | Berlin    |       1
 2 |         1 | Dresden   |       0
 3 |         2 | Madrid    |       0
 4 |         2 | Barcelona |       0
 5 |         3 | Rome      |       0
 6 |         3 | Milano    |       0

Well Berlin still there, now if you want to delete Spain you must delete Madrid and Barcelona before.

DELETE FROM Cities WHERE Name IN ('Barcelona', 'Madrid');
DELETE FROM Countries WHERE Name = 'Spain';
SELECT * FROM Cities;

Id | CountryId | Name    | Blocked
-: | --------: | :------ | ------:
 1 |         1 | Berlin  |       1
 2 |         1 | Dresden |       0
 5 |         3 | Rome    |       0
 6 |         3 | Milano  |       0

dbfiddle here


What are the advanvates and disadvantages of vacuum in postgresql?

Vacuum is an inherent part of Postgres due to the MVCC model - you don't have much choice, it's there.

As for vacuum full - it rewrites the table and takes an exclusive lock in the process- you won't be able to access your table.

It sounds bad but recent releases reduced the need for vacuum full dramatically.
Frequent regular vacuum (using the autovacuum for example) should be enough in most cases.
Just make sure to monitor your vacuuming and DB for bloat.