Does one of the normal forms require separation of repeating clusters of values into another table? Should the repeating values in the "Sales" table below be separated out into a second table as part of the process of database normalization? Note that none of the values are functionally dependent on each other; those values that are functionally dependent are already normalized out to other tables.  I'm just not sure if there's any benefit to separating the "clusters" of values in different domains out into a separate table since the table contains tens of millions of rows, but only a few thousand distinct combinations of these values:

LOCATION     PRODUCT         RECIPIENT     DATE       AMOUNT
New York     Cheesecake      Bill          1/1/2000   $25.00
New York     Cheesecake      Bill          2/1/2000   $42.00
New York     Cheesecake      Bill          3/1/2000   $17.00
Dallas       Cheesecake      Bill          4/1/2000   $15.00
Dallas       Cheesecake      Bill          5/1/2000   $17.00
Dallas       Cherry Pie      Bill          6/1/2000   $14.00
Dallas       Cherry Pie      Bill          7/1/2000   $13.00
Dallas       Cherry Pie      Sam           8/1/2000   $16.00

The table would be separated into a "Sale Types" table:

SALETYPE     LOCATION    PRODUCT       RECIPIENT
1            New York    Cheesecake    Bill
2            Dallas      Cheesecake    Bill
3            Dallas      Cherry Pie    Bill
4            Dallas      Cherry Pie    Sam

So that the original "Sales" table would look like this:

SALETYPE     DATE        AMOUNT
1            1/1/2000    $25.00
1            2/1/2000    $42.00
1            3/1/2000    $17.00
2            4/1/2000    $15.00
2            5/1/2000    $17.00
3            6/1/2000    $14.00
3            7/1/2000    $13.00
4            8/1/2000    $16.00

Since each unique combination of values appears approximately 1,000 times each on average, I believe that would save storage and potentially memory pressure, but my understanding of the underlying RDBMS memory optimizations is admittedly pretty limited.  Would that be considered database normalization though?

There is no normal form which would have you move those columns to a separate table. Normalization is a process in the logical design of tables that discusses functional dependency between a table's columns. If there is no un-resolved functional dependency there is no more normalization to perform.
Further, a logical table represents a class of objects, events or ideas from our business domain. There is a real-life thing that the table holds data for. What is that thing that your proposed "Sales Types" table would hold? Likely your business users would shrug at this question.
That said, during the physical implementation (distinct from the logical design before) you may choose to move columns between tables for reasons other than functional dependency. This can be confusing because the end results look the same - columns moved to new tables. The difference is why we move these columns. This will be important later when change happens. We need to know why we did someting so we will know why we may choose to undo it. We undo normalization if the business rules change. We undo physical implementation if the desired performance profile changes.
In the context of physical implementation your proposed table looks like a dictionary encoding scheme. There is nothing wrong with doing this. If your application benefits overall from this extra development work then go fot it.
Many DBMS offer compression natively. Column-oriented storage is particulary effective at this if your product offers this choice. By using native features you gain the performance benefits you seek without taking on the development and maintenance cost. I'd recommend you stress-test any native feature to see if it can deliver your desired performance.


Does DB2 LUW v10.5 Support Windows Authentication and How is it Set? We have a DB2 LUW v10.5 FP1 Express Edition. Can someone please confirm that this database supports Windows Authentication over the network (and if so, kindly provide some steps to do it) or does it only support Windows Authentication on the Server host?

DB2 supports various authentication models, special models for Microsoft Windows included. I recommend starting at DB2 and Windows Security in the DB2 Knowledge Center for your options and details.


SQL SERVER equivalent for Oracle DBA_USERS I'm working on conversion of Oracle queries to MSSQL queries. Following is query for Oracle -
SELECT USERNAME FROM DBA_USERS WHERE USERNAME = 'TESTUSER';

But in MSSQL, DBA_USERS is invalid object is thrown. Please suggest what is equivalent of the same in MSSQL.

The database management views are the newest addition to the SQL Server family as of 2005 and newer and replace a lot of tables that were previously used in older versions of SQL Server. A lot of old sys schema tables have comments which point this out as is stated on the page for sys.syslogins.

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Instead you would use the following database management views:

sys.server_principals or sys.sql_logins (instead of sys.syslogins)
sys.database_principals (instead of sys.sysusers)

There is a mapping page which I have linked in the comment, which helps you find the newer views for the relevant data.
All links point to Microsoft Pages in the official SQL Docs
General Catalog Views
There is a list of System Catalog Views (Transact-SQL) that are focused on various areas of SQL Server.
The sys.database_principals, sys.server_principals and sys.sql_logins for example are all part of the Security Catalog Views which can be found on the overview page.
Recommendation
If you are just starting your SQL Server journey, then I would recommend learning the new views, as the old ones are deprecated and could become unsupported.


Oracle structural synchronization I'm having two oracle databases. A is used for production, B is some kind of staging instance where people are preparing content that is brought to the production system over night. It is a fully working structural clone of A that will also give you a preview of the upcoming next release. While the overnight synchronization is completely automatized (by scripts), structural changes are first applied to B and are two days later applied to A. This depends on manual operations and is build up on sql and sh scripts.

I'm wondering if there are features that lead to better maintainability and can be utilized for manually triggered synchronization. What would be the a modern approach automatizing structural database changes brought from B to A with Oracle Database 11g?

Please excuse this vague question. Since I'm not experienced in this topic I'm looking more for a starting point on what to read than a full solution to my problem.

It doesn't seem to make sense to fully automate structural changes.  Presumably, there is some probability that changes are applied to B and testing reveals problems.  So a human ultimately needs to make a decision about whether to promote, right?

Assuming that your scripts are stored in source control, a process that pulls the scripts from source control and runs them in each of your environments (presumably you have at least dev and test instances in addition to production and staging) is generally the best solution.  That ensures that scripts are applied consistently in every environment.  You still need humans to do something to indicate that a particular set of changes are ready for the next environment but since that reflects a human's decision that testing of those changes is complete, that's hard to avoid.  And, of course, specific implementation details depend on what source control tool you're using and how your organization uses the tool.


How do I determine if a database is the principal in a mirroring setup? I have two database servers Server1 and Server2, configured with mirroring.  A single database, MirrorDB, is mirrored. There is another database on Server1 named OtherDB which is only present on Server1 and is not mirrored. OtherDB has a stored procedure named SP_Z which refers to a table in MirrorDB to compute some value.

When Server1 is the principal for MirrorDB the SP_Z stored procedure in OtherDB works perfectly, however when MirrorDB fails over to Server2 the SP_Z in OtherDB fails as it can not open MirrorDB.

How do I solve this problem?

At first I was expecting your error to be located at the point of retrieving SP_Z procedure. Sorry for being hasty.

This link gives an explanation on how to query database mirroring status of your database. So, you do the following:

DECLARE @MirroringRole int;
SET @MirroringRole = (SELECT mirroring_role
    FROM sys.database_mirroring
    WHERE DB_NAME(database_id) = N'DB_X');   -- your database name here
IF @MirroringRole = 2 -- Mirror
    -- connect to the failover partner server, using your database
ELSE IF @MirroringRole = 1 -- Principal
    -- connect to this server
END IF


MySQL show all grants I have over 100 system and admin users in my db server. Does anyone know how to show their grants by running a single query? Thank you very much.

No need to run a query - just input

SHOW GRANTS;

If you have sufficient privileges, you should get all their privileges.

A bit more fine-grained is the following query

mysql> SELECT grantee, table_catalog, table_schema, privilege_type, is_grantable
 FROM schema_privileges;

This way, you can do it by user, &c. BTW, you have to run this from the information_schema. This information

This page should also be of use.

Using the mysql schema, you can issue the query

mysql> SELECT Host, Db, User, Table_name, Column_name, Column_priv FROM columns_
priv;

From this page.


IS_SRVROLEMEMBER returning NULL for valid non windows user I have a service that executes a stored procedure to query the db for results. The stored procedure needs to use impersonation on the user who is logged in the client (since the service has its own separate credentials to connect to the db). The name of the user logged in the client is passed as a parameter (@loginUser) to the stored procedure.

If the @loginUser has sysadmin rights in the sql server, the procedure should not do any impersonation and simply proceed to query the table. The impersonation should only be used for users without sysadmin permissions.

To do that, I am using the following statement in the procedure.

DECLARE @HasSysadminRole BIT

SELECT @HasSysadminRole = IS_SRVROLEMEMBER('sysadmin', @loginUser)

IF @HasSysAdminRole = 0
  EXECUTE AS USER = @loginUser
...
...
...

IF @HasSysAdminRole = 0
  REVERT

The procedure is created on the db using the sa login.

Now, if I execute the procedure with a user who doesn't have sysadmin permissions, I get NULL as the value of  IS_SRVROLEMEMBER('sysadmin', @loginUser) even though the @loginUser is a valid user in the db and server.

If I run the same IS_SRVROLEMEMBER('sysadmin', @loginUser) statement in a query window in Sql mgmt studio, logged in as sa, I get the value 0.

I do not know why the sql server is behaving differently in these 2 circumstances.

I think you're confusing logins and users

logins exist in sys.server_principals at the Instance level
users exists in sys.database_principals in each database

Your EXECUTE AS is executed based in info in sys.server_principals but requires an entry in sys.database_principals.

Here, I have a login gbntest that maps to user gbntestU in my GBN database.
My stored procedure works OK

USE master
GO
CREATE LOGIN gbntest WITH PASSWORD = 'pwd'
GO
USE GBN
GO
CREATE USER gbnTestu FROM LOGIN gbntest;
GO

--0, OK
DECLARE @loginuser varchar(100) = 'gbntest';
SELECT IS_SRVROLEMEMBER('sysadmin'), IS_SRVROLEMEMBER('sysadmin', 'JoeBlow'), IS_SRVROLEMEMBER('sysadmin', @loginuser);
GO
--NULL, expected
DECLARE @loginuser varchar(100) = 'gbntestu';
SELECT IS_SRVROLEMEMBER('sysadmin'), IS_SRVROLEMEMBER('sysadmin', 'JoeBlow'), IS_SRVROLEMEMBER('sysadmin', @loginuser);
GO

CREATE PROC dbo.uspGBNTest @loginuser varchar(100)
AS
SELECT IS_SRVROLEMEMBER('sysadmin'), IS_SRVROLEMEMBER('sysadmin', 'JoeBlow'), IS_SRVROLEMEMBER('sysadmin', @loginuser);
GO
EXEC dbo.uspGBNTest 'gbntest' -- get 0, OK
EXEC dbo.uspGBNTest 'gbntestu'  --get NULL, expected
GO

So, what do these give in the stored proc?

SELECT
  SUSER_ID(@loginUser), -- sys.server_principals
  DATABASE_PRINCIPAL_ID(@loginUser) -- sys.database_principals (for completeness)

This will tell you if

@loginuser is set correctly For example, TheUser is sent when it should be DOMAIN\TheUser
the expected principal (@loginuser) exists at both the Instance and database level for IS_SRVROLEMEMBER to work


One SQL Server. One SSIS. Two versions of DTExec.exe? Is it possible to install and use the 2012 version of the DTExec.exe on a Server 2008R2/SSIS2008 box without installing SSIS 2012?  All jobs will be called from the command line, no server agent jobs. I see that the 2012 SSDT BI Tools download contains the DTexec.exe tools and it's required binaries. Would that work?

An example...

The path to the existing 2008 DTExec tool on a 2008R2 SQL Server is..

\\SQLSERVERNAME2008\Program Files (x86)\Microsoft SQL Server\105\DTS\Binn\DTExec.exe

Could we drop the 2012 DTExec and binaries from SSDT BI Tools onto that server into this path?

\\SQLSERVERNAME2008\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe

Will this work?  Do we have to install SSIS 2012? Why?  Why Not?  Any help you can provide will be useful.  Thanks all.

No.

SSD-BI edition is now a free download. That license allows you to develop SSIS packages that target a 2012 SQL Server Integration Services machine. That license does not grant you the right to use it for production-like usage.

Furthermore, when you try to run a package from the command line, you're going to be met with an exception stating that ~ this is only for invocation through Visual Studio. The only way to make that go away is by installing the SQL Server Integration Services Service which is part of the 2012/2014 installation media.


Do I need foreign keys in ORMs? Object Relational Mapping (take ActiveRecord and Rails, for example) allows a programmer to define relations between models, and callbacks on delete.

So my question is: do I still need to define foreign keys in my database? does a foreign key have other functions (speed for example?), or its only function is to preserve relational integrity?

Foreign keys can indeed speed up queries if they are trusted. If the engine knows that every value in ParentID in the Child table refers to a valid PK in Parents then it won't even look at that table to check.

However, relational constraints are a good idea in general regardless. One philosophy behind constraints in the database is to use them as a last-resort stop for bad data. Foreign keys are a good example of this.

If you depend entirely on your ORM for relation management, what happens if you change to a different framework? You still need to keep your existing data. What happens if for some reason you need to make changes to your relational model in the ORM and orphaned child records get put in with no parent?

Short answer - yes, you should still use keys and constraints in the database.


Call function where argument is a (sub)select statement My function takes an int4 as argument and returns a table:

SELECT * FROM test_function(545421); -- works fine

SELECT * FROM test_function(SELECT customerid
                            FROM tableX where id = 1); -- syntax error

How can I make this work?
Server PostgreSQL 9.3.1

1. Subquery expression
You can fix it with parentheses like @a_horse commented:
SELECT * FROM test_function((SELECT customerid FROM tableX where id = 1));
But this form is rather error-prone. Nothing in the code guarantees that the sub-select only returns a single row. We don't know whether id is unique and neither does Postgres (unless it looks up system catalogs). We have to rely on it and hope it won't break.
We could add LIMIT 1 to be sure, but then we probably should also add ORDER BY to get deterministic results ...
2. JOIN LATERAL
The modern syntax for this (Postgres 9.3+) would be a LATERAL join:
SELECT f.*
FROM   tableX t, test_function(t.customerid) f
WHERE  t.id = 1;

Which is short syntax for:
SELECT f.*
FROM   tableX t
CROSS  JOIN LATERAL test_function(t.customerid) f
WHERE  t.id = 1;

Exactly the same result as above, except for one subtle but possibly important difference:

If tableX has no row for t.id = 1, the function is never called and the query returns nothing (no row). If, on the other hand, a row is found with customerid IS NULL, the function is called with NULL input and we get whatever the function returns for NULL.
That's how it should be.

The form with the subquery expression cannot distinguish between these two cases. "No row" results in the same NULL value as customerid being NULL. The function is called with NULL input either way.
That's not how it should be.

The LATERAL query also does not break if id is not unique. And you can easily return additional columns from tableX if you want.


Delete/Modify row in one table based on a condition I have a table structure like

 create table EMPLOYE (
    CodeEmploye varchar2(100) not null,
    NAS varchar2(100),
    CONSTRAINT employe_pk primary key (CodeEmploye)
);

create table SALAIRE (
    CodeEmploye varchar2(100) not null,
    Mois number not null,
    CONSTRAINT salaire_pk primary key (CodeEmploye, Mois),
    CONSTRAINT salaire_code_employe_fk FOREIGN KEY(CodeEmploye) REFERENCES EMPLOYE(CodeEmploye)
);

I want to add a constraint where I should not be allowed to modify/delete a row in EMPLOYE table if the same employee exist in SALAIRE table.

What is the best way to do that ?

Prevent Delete

Add "on delete set null" to the FK constraint.  Since the column is NOT NULL, this process can't happen.

 CONSTRAINT salaire_code_employe_fk FOREIGN KEY(CodeEmploye)
    REFERENCES EMPLOYE(CodeEmploye)
    ON DELETE SET NULL

Prevent Modify

What you'll need to do is check for "can you update?" during the UPDATE process. (eg within a trigger)

One method would look something like:

declare
  type CodeEmploye_nt_t is table of SALAIRE.CodeEmploye%type;

  CodeEmploye_nt   CodeEmploye_nt_t;
begin
  select a.CodeEmploye
    bulk collect into CodeEmploye_nt
  from SALAIRE a
  where a.CodeEmploye in ( new.CodeEmploye, old.CodeEmploye )
  FOR UPDATE; -- prevent other from modifying important SALAIRE rows
              -- while you're in the middle of a transaction

  raise some_error;
exception
  when no_data_found then
    null;
end;


oracle XE on windows XP stopped working I know this will be greeted with derision, but yes I'm running Oracle XE on Windows XP.  Long story involving office policies and VMs.

Actually it's been working fine for a couple of years, but today I noticed I couldn't connect - I was getting ORA-12505, ORA-12541 and ORA-12560.

It soon transpired that the DB itself, not the listener, had stopped.  Looking in services.msc, 'OracleServiceDB' is listed as not running (even though it's set to start automatically).  Restarting it gives me a windows error: 'Error 1067 - the process terminated unexpectedly'.  The event viewer shows several instances of the DB stopping by itself.

The only change I can think of which might have caused it to break was the installation of windows XP service pack 3 a couple of days ago.

I'm running windows inside a VirtualBox VM.  There is plenty of disk space.

Any thoughts?

I looked in the log and found this:

Fatal NI connect error 12638, connecting to:
 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

 VERSION INFORMATION:
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 11.2.0.2.0 - Production
Time: 04-NOV-2013 17:48:03
Tracing not turned on.
Tns error struct:
  ns main err code: 12638

TNS-12638: Credential retrieval failed
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0

I ran the 'repair oracle installation' option from the setup.exe file which I used to install oracle in the first place.

So far, I've not had a recurrence and this seems to have fixed the problem.


Normalizing data past initial db design phase I wanted to poke at everyones brain to better understand when it makes sense to normalize a table that carries redundant data. I recently worked on a project at my startup and really question myself on whether it was a good idea. So the problem was something like this.

Our web app is a e-commerce web app. Our db contains a table that looks like this:

Store Product Table
-----------------------
- product_name
- price
- store_product_id
- coupon_name

The data looks something like this:

pk, product_name, price , store_product_id, coupon_name
1, 'Orange Juice', 10.0, 2, 'ORANGE15PERCENT',
2, 'Orange Juice', 10.0, 2, 'ORANGE50PERCENT',
3, 'Orange Juice', 10.0, 2, 'ORANGE70PERCENT'
4, 'Milk', 9.0, 89, 'MILK20PERCENT',
5, 'Milk', 9.0, 89, 'MILK50PERCENT'
6, 'Ham', 5.0, 45, '',
...
300, 'Jacket, 100.0, 90, 'JACK50PERCENT',

This is a relatively small table (~200 records). As you can tell there is a ton of redundancy. I saw redundancy and broke this out into two tables a Product table and Coupon table. This eliminated all the redundancy. I felt proud at first, but I wonder if it was really necessary. Should one always remove redundancy in a tables and break apart as two tables? Please note that this decision was made way past the initial db design phase. It's probably an easy decision to make when you are first designing things, but what about when you've built a codebase around a redundant table?

Note: I realize that there are pros to having redundant data if your table is a read heavy table, since this is a small table I don't think thats super relevant in this situation. Open to be proved wrong though.

Normalization exists to remove update anomalies. If a single fact changes in the real world, a single column of a single row should change in the model. In this way one source of inconsistency is eliminated. Pragmatically, normalization can be eased if

there are no updates
all the developers understand the situation and are competent to deal with it
there are good QA processes in place
inconsistencies are of little cost and easily fixed

.. amongst other considerations.

So, should you have normalized this particular table? In best DBA tradition I'll say "it depends."

Likely having a denormalized table causes problems right now. It may be the code is more complex, or silly errors slip in, or queries run slower or .. whatever. But there is a price and you know what it is. There is a risk that future events due to having a denormalized schema also will incur some cost. Maybe the wrong coupon is applied or too many rows get updated and revenue is lost. You know your business; you know what may go wrong and its severity.

Changing the schema and application will have a cost - direct effort and opportunity cost on features not implemented, releases delayed etc.

I'd say, if the discounted future cost of the current situation is greater than the implementation costs then do it. If not, add this item to the pile labeled "technical debt", monitor the situation, and get on with other stuff.


ODAC OLE DB provider "OraOLEDB.Oracle" for linked server supplied inconsistent metadata for a column I am using SQL Server 2014 linked server to connect to an Oracle 11gR2 (11.2.0.3) database, using the ODAC 12.1.0.2.4 Oracle OLE DB Provider. I have a stored procedure that lives in a SQL 2014 database and inserts data from a local table into a remote Oracle table via the linked server, using the following syntax:

insert into LinkedServerName..RemoteSchema.RemoteTable(column)
select column from localSQLServerTable

However, I receive the error below on the line of the insert:

  Msg 7356, Level 16, State 1, Procedure {MyProcName}, Line 1 The OLE
  DB provider "OraOLEDB.Oracle" for linked server "{MyLinkedServer}"
  supplied inconsistent metadata for a column. The column "{columnName}"
  (compile-time ordinal 23) of object "{remote table name}" was reported
  to have a "DBCOLUMNFLAGS_ISFIXEDLENGTH" of 16 at compile time and 0 at
  run time.

The column in question is defined in Oracle as CHAR(1 BYTE) data type.

Installed "64-bit ODAC 12c Release 4 (12.1.0.2.4) Xcopy for Windows x64", download name "ODAC121024Xcopy_x64.zip" from http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

Even though Oracle support note 207303.1 states that the 12c client works with 11gR2 client, this error seems to be caused by a compatibility issue between the 2 different versions.  To fix, I followed these steps:

Uninstall the 12c ODAC version, by opening an Admin command prompt, changing directories to the installed Oracle home folder, and running: uninstall.bat all d:\oracle, where d:\oracle is my Oracle home location.
Remove the PATH environment variables
Reboot
Install the 11.2.0.4 ODAC version "64-bit ODAC 11.2 Release 6 (11.2.0.4.0) Xcopy for Windows x64", download the "ODAC112040Xcopy_64bit.zip" option.  Make sure you install via Admin command prompt (the install command is: install.bat all d:\oracle11gr2 odac11gr2).  Download from: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
Add back the PATH environment variables for d:\oracle11gr2;d:\oracle11gr2\bin
Reboot again


Oracle Listener.ora ignored I have the following listener.ora file located in $ORACLE_HOME/network/admin/ :

TESTSID = (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID_NAME = TESTSID)
    )
  )

When I run dbstart, I am able to connect to some of the instances, but not the TESTSID one specified. Also, there is no error when I change that listener.ora to be an invalid entry.

When I run lsnrctl status, the other instances are listed. After running lsnrctl stop and lsnrctl start I can no longer connect to any of them and they are missing when I check the status again. I believe that this means that the database instances are registering with the listener. However, when I run status both before and after restarting the listener, both state the following (which is the correct file):

Listener Parameter File  /u01/app/oracle/product/12.0.1/dbhome_1/network/admin/listener.ora

How do I get it to work so that the listener.ora is the file that is used instead?

That is a TNS entry that should be in tnsnames.ora. Listener can't do anything with that. If you want to do static registration, this is what you should put in listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME = TESTSID)
    )
  )


Prevent a stored procedure from being executed outside SSMS I'm currently working on a stored procedure which modifies metadata for an application. This stored procedure can only be run when the application is offline. I didn't want developers to accidentally call it and break something, so I thought I was clever and inserted this block in the top of the stored procedure:

IF (APP_NAME() NOT LIKE '%SQL Server Management Studio%')
BEGIN
  RAISERROR(N'The stored procedure must be run interactively.', 10, -1, -1);
  RETURN;
END;

This works for normal executions, but when running over a local server group, the application name changes to ".Net SqlClient Data Provider".

I can't find another way to determine whether a query comes from SSMS. I can't discriminate based on user names because our applications use domain accounts. The host name isn't an option, either, because there are disaster scenarios in which accessing SSMS from the database server itself is the expected course of action (and I'd rather not surprise anyone during disaster recovery, even if it is unlikely).

Are there any better ways of determining if a query is coming from SSMS, either directly to one server or from a linked server group?

Instead of relying on shaky assumptions on the application name, why don't you just revoke permissions from those users that are not supposed to run the procedure?

You said that developers should not run it accidentally: revoke EXECUTE permissions from them and force them to use a different login to perform this task (if they are supposed to perform it, otherwise keep this privilege for you or the application administrator).


SQL Server 2019 standard edition CDC with SSIS standard edition The primary question is:

Are there any methods for using SQL Server standard edition CDC for data synchronization to cloud based non SQL server databases that don't require use of SSIS Enterprise Edition?

Related questions are:

Is there a manual process to do what the SSIS CDC Control Tasks automagically do?
OR - is there some other performant way (i.e. not using triggers) to sync data changes from SQL Server Standard Edition out to a non SQL Server cloud database?  Log shipping does not appear to be an option.

The background is:
After seeing that Microsoft enabled CDC in the standard editions of SQL Server 2016 sp1 and above, we built a SSIS package based on that to sync data from an on premise database out to a postgres database in the "cloud".
In testing from Visual Studio, everything works fine, but when we deploy the package to our SQL Server 2019 standard edition server, we run into an error that states:

The task "Mark Initial Load" cannot run on the installed edition of Integration Services. It requires Enterprise Edition (64-bit) or higher.

So it appears that while you can enable CDC on SQL Server standard editions, you can't actually make use of Microsoft's other tools to get any benefit from it without paying for enterprise edition anyway.

You should research DMS solutions. AWS DMS, Azure DMS, and HVR DMS are the three I'm familiar with. They use CDC, are not just for migrations, and are very good for on-going replication of data where you don't have primary keys on every table or you are going from SQL to something else. HVR is probably your best bet of these three since you're going from SQL to Postgres but I know there are additional DMS products out there. Here's a link to get you started:
https://www.hvr-software.com/platforms/postgresql/


SQL Server 2012 Express dying randomly I have a Windows 2008R2 box with SQL Server 2012 Express. The problem is that randomly (sometimes twice a day, sometimes once a month) the SQL Server dies leaving those messages in error log:

  Error: 49910, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
  Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
  Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

When I try to start the service again, it says:

  Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
  Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
  Error: 33086, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

After reboot of the machine it starts working again. How can I fix this problem?

ERROR 17300 is caused by SQL Server not being able to execute one of the system tasks.
It points to insufficient memory: http://technet.microsoft.com/en-us/library/cc280564.aspx

As that resource explains, it could also be caused by a setting, but insufficient memory is more likely the cause.

ERROR 17312 is directly related.

This could be caused by another process eating up all the available memory. For example Windows explorer file copy operations are known to reserve memory and not release it.

Make sure you have adequate memory installed on the system and monitor how much of it is reserved.


MySQL: can I put the database into a maintenance mode? (i.e. no user connections) I'm going to be doing some complicated maintenance on our MySQL servers and I'd like to keep users out during the work.   I'd prefer not to drop and re-create all of the user accounts, but that's the only solution I've come up with so far.  Hmm..  Refusing network connections would work as well, as the maintenance will be done via an os-local mysql CLI.  I'd prefer to not have to stop and re-start the server, but that's not impossible.

What are best practices here?

Thanks in advance,
Brad

As said by Brad Silva, you can skip connections, but you need to restart the mysql server anyway ( with --skip-networking ), as skip_networking is a read only variable.

When finished, restart mysql without this parameter.

EDIT: When I say it's a read-only variable, I mean you cannot simply set it in the console, like other variables you can switch at runtime. This requires a restart, unless I'm wrong.


mysql trigger on update with insert statement I am trying to create a simple trigger which is handled on update operations. The trigger is checking before update if a record of an update exists, then if not it is created.

DELIMITER ;;
CREATE TRIGGER trig
BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
IF old is null then
INSERT INTO table(table.col1, table.col2) VALUES ('new.table.col1','new.table.col2');
END IF;
END;
;;
DELIMITER ;

Why is it not adding a record if update is trying to update some non existing record?

I think that you don't need a trigger at all and - based on your comments - you want an INSERT ... ON DUPLICATE KEY UPDATE. If there is a UNIQUE constraint on (col2), this will work:

INSERT INTO table_name
  (col1, col2)
VALUES
  (@col1, @col2)                  -- the new values here
ON DUPLICATE KEY UPDATE
  col1 = col1 + VALUES(col1) ;    -- update the old value by adding the new one


Copy data to another database I would like to know how to copy all the data from a database X1 into a database X2 on SQL Server please.

Some options are:

Create a backup and restore as a different name (if on same server):
https://www.nucleustechnologies.com/blog/restore-sql-database-with-a-different-name/

Script a database by using the Generate Scripts option using Types of data to script set to Schema and Data;

Use the Import and Export Data with the SQL Server Import and Export Wizard.


Is there a postgresl server configuration that will let me expose an untrusted language like pl/R while still maintaining security? I am building a postgresql database and I want users to be able to run their own R and python scripts at the database rather than pulling it all down locally.  Unfortunately, pl/R and pl/python are untrusted, so I can't just let 20 users have pl/R access to my database for fear some person will do something bad to my core database.

Is there a server configuration that will let me provide this flexibility to my users but still stop them from having inappropriate access to my database?  For example, can I set up a special linked database just for them, that is close enough to the data to make it useful while still having security separation? (or anything else, just trying to give an illustrative example).

I don't think that there is any general method to do this.  (Otherwise someone probably would have implemented a trusted version of the language using that method).

You could create a hot-standby database on a separate machine (or at least running as a separate and little-privileged OS-user) and let your users have at that with superuser permissions.  Note that they would be able to do nasty things to each other if they are clever and malicious enough, but should have limited capability to mess with the primary server.

Since a hot-standby is read only, you would have to set up the (super)user roles you want in the primary server, but disable them in the pg_hba.conf file.  Then use a different pg_hba.conf in the standby which allows those roles to connect.

I think it is kind of playing with fire to do even this much, though.


psycopg2 connection with multihosts I have 3 db servers on docker containers, and i used to connect like this from my python app:
psycopg2.connect(host="db-0", user="user", password="password", database="data") This works,but if i add more then 1 host it fails.

psycopg2.connect(host="db-0, db-1", user="user", password="password", database="data")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.7/site-packages/psycopg2/__init__.py", line 126, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not translate host name "db-0, db-1" to address: Temporary failure in name resolution

my package is:

psycopg2-2.8.4

python3.7

psql 11.5 on docker containers db-0 and db-1
I used pip install psycopg2 to install.

Specify a single libpq style connection string:

psycopg2.connect("host=db-0,db-1 user=user password=password dbname=data")


Get all changed tables from CDC I am polling CDC tables to synchronize data between SQL Server and MongoDB. The approach I have taken is below:

Enabled cdc for dbo.table1 and dbo.table2 which creates cdc.dbo_table1_CT and cdc.dbo.table2_CT
Created an insert trigger for cdc.dbo_table1_CT and cdc.dbo.table2_CT. Then the trigger will writes the changed table's name and schema to another table, say tbl_consolidated_tracks
Then I have a service which polls tbl_consolidated_tracks and then from that the service reads the changes and syncs to mongodb

What I am trying to do here is to avoid triggers and the usage of tbl_consolidated_tracks.

Can I directly poll the changed tables from any cdc procedure or table?

Instead of using triggers on the CDC tables, you should use the built-in CDC functions for returning the changes recorded by CDC. If you only need the latest value for each row, use cdc.fn_cdc_get_net_changes but if you need all intermediate changes as well, use cdc.fn_cdc_get_all_changes.

These functions are created for each table (or 'capture instance') that you enable CDC on and the functions return changes on a table from the earliest LSN to the latest LSN. There is a good article here that delves into CDC and retrieving the data.

To use it in your periodic polling process, you could do something like this:

Fetch the earliest LSN from cdc.lsn_time_mapping after the last datetime that you polled.
For each table, fetch the max lsn value using sys.fn_cdc_get_max_lsn
Use cdc.fn_cdc_get_net_changes or cdc.fn_cdc_get_all_changes with the LSNs from step 1 and 2 to fetch the changes that have occurred since the last polling.
Process the changes into MongoDB
Log the min and max LSN and the start and end datetime used during this polling to a table. This can be used on the next poll to get the datetime value to determine your next min LSN.

You could even use SQL Server Integration Services which has built-in tools for working with CDC data.


Is PostgreSQL JSONB @> operator equal to ->''=? Is the jsonb_column @> '{"key":value}'::jsonb operator equal to jsonb_column->'key' = value? in terms of result, performance, and indexes that will be used?

I have created a small test for checking this:

CREATE TABLE jsonb_test (id serial, data jsonb);

Then inserted some generated data:

INSERT INTO jsonb_test (data)
SELECT row_to_json(x.*)::jsonb
  FROM (SELECT i AS bla,
               ARRAY[i, i+1, i+2] AS foo,
               ARRAY[('v' || i::text, i), ('v' || (i * 10)::text, i)] AS bar
          FROM generate_series(1,100000) t(i)
       ) x;

Let's find something in there (version 1):

SELECT * FROM jsonb_test WHERE data @> '{"bla": 545}'::jsonb;

The execution plan looks like

 Seq Scan on jsonb_test  (cost=0.00..7604.89 rows=369 width=36) (actual time=0.425..47.600 rows=1 loops=1)
   Filter: (data @> '{"bla": 545}'::jsonb)
   Rows Removed by Filter: 99999
   Buffers: shared hit=2997 dirtied=676
 Planning time: 0.078 ms
 Execution time: 47.692 ms

If I do the same with the other version (version 2) you propose (with the operator changed from -> to ->> as I think you meant that):

SELECT * FROM jsonb_test WHERE data ->> 'bla' = '545';

I get a plan like

 Seq Scan on jsonb_test  (cost=0.00..8526.47 rows=1843 width=36) (actual time=0.613..34.657 rows=1 loops=1)
   Filter: ((data ->> 'bla'::text) = '545'::text)
   Rows Removed by Filter: 99999
   Buffers: shared hit=2997
 Planning time: 0.116 ms
 Execution time: 34.727 ms

Running them repeatedly, I see no significant difference between the execution times.  It is no wonder - the expensive operation here is the sequential scan, while the two different filter conditions should't be very different in this regard.

Now I add a GIN index on data:

CREATE INDEX ON jsonb_test USING gin (data);

When executing the queries now, version 2 does the same as above, while version 1 has a new plan:

 Bitmap Heap Scan on jsonb_test  (cost=28.77..372.05 rows=100 width=36) (actual time=0.081..0.083 rows=1 loops=1)
   Recheck Cond: (data @> '{"bla": 545}'::jsonb)
   Rows Removed by Index Recheck: 2
   Heap Blocks: exact=1
   Buffers: shared hit=10
   ->  Bitmap Index Scan on jsonb_test_data_idx  (cost=0.00..28.75 rows=100 width=0) (actual time=0.062..0.062 rows=3 loops=1)
         Index Cond: (data @> '{"bla": 545}'::jsonb)
         Buffers: shared hit=9
 Planning time: 0.084 ms
 Execution time: 0.122 ms

This already shows that the two versions are not fully eqivalent - a GIN index on the jsonb column supports only one of them (version 1).

One can define an other index to help version 2:

CREATE INDEX ON jsonb_test ((data->>'bla'));

Now version 2 gives the following:

 Bitmap Heap Scan on jsonb_test  (cost=12.17..1323.49 rows=500 width=36) (actual time=0.065..0.070 rows=1 loops=1)
   Recheck Cond: ((data ->> 'bla'::text) = '545'::text)
   Heap Blocks: exact=1
   Buffers: shared hit=1 read=2
   ->  Bitmap Index Scan on jsonb_test_expr_idx  (cost=0.00..12.04 rows=500 width=0) (actual time=0.046..0.046 rows=1 loops=1)
         Index Cond: ((data ->> 'bla'::text) = '545'::text)
         Buffers: shared read=2
 Planning time: 0.480 ms
 Execution time: 0.133 ms

In this experiment, I don't see a huge performance difference between the two, given proper indexing.  However, you should consider the following:

  Insertion into a GIN index can be slow due to the likelihood of many keys being inserted for each item. So, for bulk insertions into a table it is advisable to drop the GIN index and recreate it after finishing bulk insertion.

Usually it is said that BTree indexes are cheaper to maintain (meaning data changes on the table have a smaller overhead) that GIN indexes.  From reading around, this is not always the case - you have to test these solutions on your own data set.  For example, I cannot tell off the top of my head if a set of very complex jsonb values will prefer this or that.

Notes:

to understand the similarities and differences of the json(b) operators, the official documentation is an excellent source.
it might make sense reading Bruce Momjian's presentation about indexing techniques.


How to delete rows from relational database So I have database with two tables. and they have foreign key relation between them. I want to do some testing but the tables are so big. I want to delete most of the data in these tables and just keep around 500 rows in the first table and the related rows in the second table so testing will be faster. How can I do that?

Database structure:

One simple way to do this is to extract the rows from those tables into temporary tables then truncate the source tables.

Example:

-- Load the top 500 UUT_RESULT records into a temp table
SELECT TOP (500) *
INTO #UUT_RESULT_TEMP
FROM UUT_RESULT
ORDER BY ID

-- Load the related STEP_RESULT records into a separate temp table
SELECT *
INTO #STEP_RESULT_TEMP
FROM STEP_RESULT
WHERE UUT_RESULT IN (SELECT TOP (500) FROM UUT_RESULT ORDER BY ID)

-- Remove the record
TRUNCATE TABLE STEP_RESULT
TRUNCATE TABLE UUT_RESULT

-- Load the records back in
INSERT INTO UUT_RESULT
SELECT * FROM #UUT_RESULT_TEMP

INSERT INTO STEP_RESULT
SELECT * FROM #STEP_RESULT_TEMP

Using TRUNCATE will help speed things up as this is a minimally logged operation.


What is the Best way to set Disaster recovery setup for percona Xtra DB Cluster? We do have a Percona XtraDB Cluster(Master-Master-Master) setup in one country. Now we want to setup a disaster recovery setup in some other country. Please provide us the best possible ways to do that.

Is it possible to link between PXC(as Master)and  Mysql community version (as Slave)?
Or else can we just add one more node to PXC itself which serves as slave in that cluster??

Percona version:5.6

In the latest Percona XtraDB cluster version it is possible to make WAN segments so that there is more efficient communication between nodes. If you didn't do that, all nodes would try to replicate to all nodes, which is highly inefficient for Galera. With Galera 3/PXC 5.6, you can establish those so that only one node at a time will replicate to the other segments. This was specifically thought for geographical redundancy.

Before 5.6:

  ---------------
 /     /   \     \
A --- B --- D --- E   (A, B, C Local nodes; D,E Remote nodes; all talk to all)
 \   /     /     /
   C ------------

After 5.6 (Galera 3):

[ A --- B ] --- [ D --- E ]   (no redundant communication between segments)
[  \   /  ]
[    C    ]

In any case, think that the round-trip time will limit the minimum latency for each transaction, which means that using geographical replication will make the cluster much slower.

An alternative option that you can setup is combining Galera and standard replication, like this:

 A --- B ~~~ D --- E (--- : Galera replication; ~~~: MySQL replication )
  \   /
    C

Standard MySQL replication is way more efficient, as it does not wait for the "slaves" to confirm the changes (it does not use group communication). The disadvantage is clear: being asynchronous it does not guarantee that absolutely all changes applied to the primary cluster has been transmitted to the slave node or nodes. If the replications is well managed that should only mean less than 1 second of transactions, but this is certainly a no-go for banking applications or other applications where loss of information is impossible even if the whole datacenter fails. Whoever, that is something that most web applications and services can handle. On the negative aspect, standard replication has some disadvantages, specially if it is not well managed, like potential data drifts or extra latency due to it being single-threaded.


Bridge table question Should be a simple questions, I just want to make sure.

If I had 2 tables I want to bridge, for example food (food_id, foodname, ...) and a plate (plate_id, platetype, ..). When the tables are bridged, would they need to be foreign keys to each other or it wont be necessary as there is a food_plate table with food_id and plate_id ?

Thanks for the help

You will indeed want a third table (food_plate) with two foreign keys, food_id to the food table and plate_id to the plate table. The food and plate tables should not reference each other.


How To Merge Database Projects on Visual Studio 2013 Can I have multiple database projects to the same SQL Server database? Like, one main project referencing multiple subprojects.

I have an application with multiple modules and each module has its own database structure living in a schema.

I want to reuse the modules in another application, for example, the security module, which has the user, roles, groups, etc.

I want to know if there is a way I can define those schemas and all of its objects (tables, stored procedures, indexes, views, etc) in one database project and then reference this "base" database project in the database project of the actual application.

Certainly appears you can do that in Visual Studio 2012.  I just created a Database Project "database1", then added another database project to the same Solution, named "database2".

I then right-clicked the "Database1" project, clicked "Add Database Reference" and selected the "Database2" project.  Then, under "Database Location" I chose "Same Database".


Create Database and LCK_M_S We are experiencing issues when creating a database in the SSMS GUI in that sometimes it times out complaining that it could not get an exclusive lock on the Model database.  Other times the database gets created but does seem to be taking a long time.

I have also added the Service Account to the Perform Volume Maintenance local security policy.

This is a brand new clustered SQL server with no other processes running on it at the moment.

I have queried the wait types and the largest is LCK_M_S

I have queried sys.dm_tran_locks during the time when it does get created and can see two locks

Is this correct, does anyone have any advice why the DB creation may be taking so long?

Same issues when using code, being the following:

CREATE DATABASE [TestTom]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'TestTom', FILENAME = N'G:\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestTom.mdf' , SIZE = 1048576KB , FILEGROWTH = 1048576KB )
 LOG ON
( NAME = N'TestTom_log', FILENAME = N'F:\MSSQL11.MSSQLSERVER\MSSQL\Data\TestTom_log.ldf' , SIZE = 1048576KB , FILEGROWTH = 1048576KB )
GO
ALTER DATABASE [TestTom] SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE [TestTom] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TestTom] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TestTom] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TestTom] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TestTom] SET ARITHABORT OFF
GO
ALTER DATABASE [TestTom] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TestTom] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [TestTom] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TestTom] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TestTom] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TestTom] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [TestTom] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TestTom] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TestTom] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TestTom] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TestTom] SET  DISABLE_BROKER
GO
ALTER DATABASE [TestTom] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TestTom] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TestTom] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TestTom] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TestTom] SET  READ_WRITE
GO
ALTER DATABASE [TestTom] SET RECOVERY SIMPLE
GO
ALTER DATABASE [TestTom] SET  MULTI_USER
GO
ALTER DATABASE [TestTom] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TestTom] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [TestTom]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [TestTom] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

If you see below segment of the code

CREATE DATABASE [TestTom]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'TestTom', FILENAME = N'G:\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestTom.mdf' , SIZE = 1048576KB , FILEGROWTH = 1048576KB )
 LOG ON
( NAME = N'TestTom_log', FILENAME = N'F:\MSSQL11.MSSQLSERVER\MSSQL\Data\TestTom_log.ldf' , SIZE = 1048576KB , FILEGROWTH = 1048576KB )
GO

The size which you are pre-allocating is 1048576KB which is approx 1G.

The same is with Log file 1048576KB which is 1G.

Can you alter your script and put it to few MB's and see if you again get the locking. You can later run alter database modify file script to increase size. This is something I have witnessed to telling you. This is not a definitive answer but I am sure it will reduce the creation time which exactly is your problem.

Did you also noted if any process was blocking the table creation script.

Plus first run create database script and then the alter database commands.


Decision tables in the database: a valid solution? I have to extend a .Net 4/C# case management application that uses Oracle 11g to handle legal cases.

I have minimal budget, my experience with Oracle and a very junior C# developer to accomplish this.  The additional module will be used by no more than 20 people on an intranet.

The existing rules are generally like this:

in order to complete an action there must be property a, property
b, property c present in a table with a clearly defined relationship
to the table where the new insert must be validated
For legal cases new business rules are more like "everything is an exception"
More formally restated as data entries in other tables may or may not affect the outcome of future transactions based on unknown conditions

The existing application uses Microsoft Workflow to implement the business rules. This has proven to be unwieldy, almost unusable for us. Drools and their commercial equivalents are equally complicated and will not achieve the goal of allowing managers to understand their business logic.

it would be a benefit if the business logic could be seen by users in a report without having to read C# comments or code.
logic can be added or disabled quickly without taking the web application down
fairly easy to implement given we have limited budget and skill sets

This brings me to the old fashioned idea of holding the business logic in the database as a series of decision tables. Is this still done or has the idea that the efficiency of holding the business logic in the application so obviously better?

Edit: Workflow is implemented as a service. My understanding is that it was aimed at long running workflows.  In our case users press the save button and the workflow runs. Our issues are
 - Painfully slow to develop with on our machines, freezes, crashes
 - difficult to debug
 - now and again the workflow manager throws errors for a few hours for no reason we can find
 - touch one workflow and you have to change 14 other files
 - application is slow for users, part of which we have traced to the workflows.

Validating in the database starts to look good because the validation is all based on data in the database.
@catcall There are 10 or 12 lawyers who do not use the application but have 8 or 10 admins do the data entry.
We just finished the desktop upgrade but the boss does not believe that programmer contractors should have better equipment than staff. So Windows XP with 4 cores and 3 GB of ram is the best we can do.
I will investigate if there is any classification of types of business logic. Some of it can be done more efficiently in the application.  Other types might be more easily changed and configured on the database side.

The additional module will be used by no more than 20 people on an intranet.

"People" is a fuzzy term when you're talking about legal applications. Supporting 20 lawyers is a lot different than supporting 1 lawyer, 4 paralegals, and 15 staff.

  For legal cases new business rules are more like "everything is an
  exception"

And that's one reason why. Within case management, certain kinds of things are simply deterministic. Timing of responses, deadlines for filing, things like that. But the rules that govern were written by lawyers for lawyers. Companies that build case management software have lawyers on staff to read the rules for procedure (which change over time) and to verify that their software complies with the rules.

Some of them have a lot of lawyers.

Other kinds of things are governed by whimsy. For example, some case management packages let users "template" their tasks. Entering something like "Deposition" on the calendar might also calendar a series of earlier tasks that have to be done to prepare for the deposition.  Under the pressure of multiple, incompatible deadlines, parts of a task chain get dropped or given short shrift.

Now, to (finally) get to your question . . .

Decision tables in the database are a viable solution. In fact, decision tables constitute a database whether they're stored in Oracle or in application code. There are obvious trade offs to storing decision tables in application code.

But decision tables in the database are probably not a viable solution for you. The problem you have, if I understand you correctly, is that you think you might need to simplify the architecture to decision tables in order to eliminate (or at least to greatly reduce) the use of Microsoft Workflow. With just two of you and a minimal budget, success will be difficult at best.

I can suggest two things.

Can you economically produce a prototype of a replacement system
built on decision tables?
Can you use better hardware on the existing system?


How to get a value from a table that a value is referenced from? I've two Postgres tables.

CREATE TABLE questions (
    id serial primary key,
    question varchar(64) unique
);

and

CREATE TABLE answers (
    id serial primary key,
    answerer serial references users(id), -- todo left off here, did not have answerer column
    question serial references questions(id),
    answer int
);

I want to write a query that returns the answers table id (answers.id) and question text (questions.id) where the answer is 1 in one query.

I've tried a query:

SELECT (id, question) FROM answers WHERE answer=1 UNION SELECT question FROM questions WHERE id=(SELECT (id) FROM answers WHERE answer=1 LIMIT 1) LIMIT 15;

and it doesn't work.

How can I fix this?

SELECT  a.question,
        a.answer
FROM    Answers a
INNER JOIN Questions q
    ON q.id = a.question
WHERE a.answer = 1

The JOIN syntax is described in the PostgreSQL documentation "2.6. Joins Between Tables"

(emphasis mine)

  A query that accesses multiple rows of the same or different tables at one time is called a join query.


get query from 4 tables I have three tables:

User (Table)
- Email
- Moblie
- User_id

Invoice_product (Table dbo.payware_factor_product )
- invoice_id
- Product_id

Invoice (Table dbo.payware_factor )
- User_id
- Mobile
- invoice_id

Each product has its own ID.

I want a query that, for example for product_id 16, returns all customers who bought product 16 and the mobile number of customers only once.

But with the below query, if a customer has bought product 16 five times, the result shows his  mobile number five times. How can I get it returned only once?

I wrote this query:

SELECT TBLA.mobile , TBLB.factor_id
FROM dbo.payware_factor_product TBLB
INNER JOIN dbo.payware_factor TBLA ON TBLB.factor_id = TBLA.factor_id
WHERE TBLB.product_id = 16
AND TBLA.mobile = (SELECT mobile )

There is something very wrong with the last line, which is a no-op (it has no effect being there).

AND TBLA.mobile = (SELECT mobile)

Because (SELECT mobile) creates a small subquery returning a single scalar column, which is, tada - TBLA.mobile.  So that condition resolves to nothing, really.

That aside, you're getting something like this

Mobile          Factor_id
0312-1232132    1234
0312-1232132    1235
0312-1232132    1244
0312-1232132    1314

If the customer bought product_id 16 on 4 different invoices.  DISTINCT on the entire row won't work.  If you only need the mobile number to contact, then

  SELECT TBLA.mobile, MAX(TBLB.factor_id) LastFactorId
    FROM dbo.payware_factor_product TBLB
    JOIN dbo.payware_factor TBLA ON TBLB.factor_id = TBLA.factor_id
   WHERE TBLB.product_id = 16
GROUP BY TBLA.mobile

You can substitute MAX for MIN to get the first instance, or a complicated FOR XML subquery if you wanted all the factor_id's in a comma separated list.  Or you could remove the column entirely.

Now, if your query is not as simple as shown, and you wanted everything from the B table, then you'll need to use ROW_NUMBER() to choose an individual row from B for a distinct row in A, like the following:

      SELECT *
        FROM
  (
      SELECT TBLA.mobile, TBLB.*, RN=row_number() over (partition by TBLA.mobile
                                                        order by TBLB.factor_id desc)
        FROM dbo.payware_factor_product TBLB
        JOIN dbo.payware_factor TBLA ON TBLB.factor_id = TBLA.factor_id
       WHERE TBLB.product_id = 16
    GROUP BY TBLA.mobile
  )        X
       WHERE RN=1;


SQL Server 2012 Always-On replica change Newbie question.  Using SQL 2012.  I have AlwaysOn setup for 3 servers.  Server A is my primary, my replicas are Servers B and C.  I have 5 DBs on Server A, all of which are replicated to both replicas.  I want to pull just DB1 off of Server C and leave it replicated between Server A and Server B.  Doable?  If I pull DB1 off of my primary and then try to add it back in, just replicating to Server B, it is grayed out.  The only way to add DB1 back into the mix is by replicating to both of my replicas B and C...not what I want to do.

I want to pull just DB1 off of Server C and leave it replicated between Server A and Server B. Doable?

Yes this is doable. For your scenario if you want to remove the database from availability replica you must do that from secondary replica. So you have to login into secondary replica from which you need to remove database. Expand availability database, right click on it and select remove secondary database. You can choose multiple databases to remove at one go. More information in BOL document

If you want to do using TSQL which I prefer just login into secondary replica from which you want to remove the database and run

ALTER DATABASE db_name SET HADR OFF;
GO

When a secondary database is removed, it is no longer joined to the availability group and all information about the removed secondary database is discarded by the availability group. The removed secondary database is placed in the RESTORING state.

  If I pull DB1 off of my primary and then try to add it back in, just replicating to Server B, it is grayed out.

If you remove from primary replica means you are completely removing it from AOAG. You have to remove it from secondary replica as sated above.


Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones We are just starting design for a new data warehouse and we're trying to design how our date and time dimensions will work. We need to be able to support multiple timezones (probably at least GMT, IST, PST and EST). We were initially thinking that we would have one wide combined date time dimension down to maybe 15 minute granularity, that way we have one key in our fact tables and all the different date time data for all supported timezones are in one dimension table.
(i.e. Date Key, GMT Date, GMT Time, IST Date, IST Time, etc...)

Kimball suggests to have a separate day dimension from the time of day dimension to prevent the table from growing too large (The data warehouse toolkit p. 240) which sounds fine however that would mean we have two keys in our fact tables for each time zone we need to support (one for the date and one for the time of day).

As I'm very inexperienced in this area I'm hoping someone out there knows the tradeoffs between the two approaches, i.e. performance vs. the management of all the different time zone keys. Maybe there are other approaches too, I've seen some people talking about having a separate row in the fact table per timezone, but that seems like a problem if you fact tables are millions of rows then you need to quadruple it to add time zones.

If we do the 15 minute grain, we'll have 131,400 (24 * 15 * 365) rows per year in our date time dimension table which doesn't sound too horrid for performance but we won't know for sure till we test some prototype queries. The other concern with having separate time zone keys in the fact table is that the query has to join the dimension table to a different column based on the desired timezone, perhaps this is something that SSAS takes care of for you, I'm not sure.

thanks for any thoughts,
-Matt

Having the date and time separate will allow you to do aggregates by time much easily. for eg: if you want to run a query to find what time period of the day is most busy. This is much easily performed using a separate time dimension.

Also, you should just have one timekey. Decide on either GMT/ EST time - then use this in the fact table. If you need to run reports based off the other timezone, just convert it in your application or query.


SQL Server - Server name same as the instance name and most common use for instances? Is the server name the same thing as an instance name? I have looked around and am not sure; I think they are but I don't just want to guess.

Also, what is the most common use for SQL instances? I understand why it would be useful to have different instances, but I would like to know what others find the most use of it from.

Servers are like the physical location. Instances are the actual SQL Server functionality. They are what you connect to in order to use SQL Server.

Each Instance belongs to only one Server.
You can have multiple instances per Server (so T_COMPUTER\SQLEXPRESS and T_COMPUTER\SQLSERVER could be 2 instances on the same Server).
An Instance is a fully self contained SQL Server installation.
You can have a default instance configured, which is the Instance accessed when you connect to the Server directly (i.e. T_COMPUTER, actually connects to T_COMPUTER\INSTANCE)


inserting large quantity of data into mysql table I need to insert about 400 000 000 rows of test data into the InnoDB table.
Table is quite simple:

CREATE TABLE IF NOT EXISTS `tgt` (
  `id_1` int(10) unsigned NOT NULL,
  `id_2` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id_1`,`id_2`),
  KEY `id_1` (`id_1`),
  KEY `id_2` (`id_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Data is coming from another table:

CREATE TABLE IF NOT EXISTS `src` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  # skip some columns
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

I need to insert into tgt all possible combinations  of different ids from table src, so naive method is to execute:

INSERT INTO tgt (id_1, id_2) (SELECT  a.id,  b.id FROM `src` a INNER JOIN src b  WHERE a.id <> b.id)

And, of course it take  hours or even days... So I am searching for a faster method of doing that.

Dump the source data into a file:

SELECT  a.id,  b.id INTO OUTFILE '/tmp/result.txt' FROM `src` a INNER JOIN src b  WHERE a.id <> b.id

Tune MySQL for large writes:

innodb_flush_log_at_trx_commit=2
innodb_log_file_size=2G

See how to change the log size

Start a pipe to split results.txt in chunks

pt-fifo-split --lines 1000000 /tmp/result.txt

(pt-fifo-split is a part of Percona Toolkit)
Load the dump

while [ -e /tmp/pt-fifo-split ]; do mysql -u <user> -p<pass> -e "LOAD DATA INFILE '/tmp/pt-fifo-split' INTO TABLE tgt" <tgt db>; done

(if you have problem with "File '/tmp/pt-fifo-split' not found", try  this solution )


MariaDB master-slave replication failover and failback I'm trying to configure a DR instance of MariaDB (10.1.13) using master-slave replication.

I was able to establish the master-slave relationship with GTID by locking the tables on master, backing up the database, restoring the database on the slave and running this on the slave:

CHANGE MASTER TO MASTER_HOST='master.domain.com', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_PORT=3306, MASTER_LOG_FILE='masterlogfile', MASTER_CONNECT_RETRY=10, MASTER_USE_GTID = slave_pos;

I was able to confirm that the replication is working is expected. However, I'm struggling with figuring out the process of converting the current slave to a master and master to the slave (reversing the replication flow).

When I run the following on the ex-master I get errors in the SHOW SLAVE STATUS \G; (Slave_SQL_Running is No and the error is Error 'Operation CREATE USER failed for 'replication_user'@'%'' on query. Default database: ''. Query: 'CREATE USER 'replication_user'@'%' IDENTIFIED BY 'somepassword'', I'm not sure why it's trying to create this user after running the query below):

CHANGE MASTER TO MASTER_HOST='slave.domain.com', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_PORT=3306, MASTER_LOG_FILE='slavelogfile', MASTER_CONNECT_RETRY=10, MASTER_USE_GTID = slave_pos;

I have a feeling I'm doing something wrong and that my scenario is not supported. If it's not supported, what's the best way of implementing asynchronous kind of replication (analog of AlwaysOn in MS SQL) for MariaDB to support failing over to the slave and failing back?

I went with master-master replication. To prevent connections to the "passive" copy of the master I used a script to delete all users (except the DBA and the replication one) which makes sure to prevent writes on the "passive" copy.

As part of the failover process the users will be transferred to the "passive" and dropped from the "active".

This basically achieves what I was looking for.


SSMS 2016, Availability Groups, database is encrypted error While evaluating possibility for rolling upgrade of SQL Server 2012 instances hosting AGs to SQL Server 2016 we ran into strange issue that does not quite make sense. One of the easier way to demonstrate is as follows:

Let’s say you want to add database to AG (2012 instance is being used). SSMS 2012 or SSMS 2014 wizard shows no surprises:

SSMS 2016 however tells completely different story:

For databases that are already part of AG it says “Does not meet prerequisites” instead of “Already part…” and for those not in AG it says “Password required” with the following explanation:

"This database is encrypted by database master key, you need to provide valid password when adding it to the availability group."

Problem with this message is that none of the databases use any encryption.

Same issue manifests itself if you restore database from 2012 AG backup on 2016 instance and try to add it to 2016 AG using SSMS 2016 wizard.

Problem with this message is that none of the databases use any encryption.

That is probably true :) However, that's not what the error is saying...

  "This database is encrypted by database master key, you need to provide valid password when adding it to the availability group."

The error is saying, in plain English: You have a databases that has, inside of it, a database master key. That database master key is encrypted using a password. I need you to give me that password so that I can decrypt any objects that might be encrypted using it.

Here is a super quick repro to give you the "message":

CREATE DATABASE PasswordTest;
GO

USE PasswordTest;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘$up3r$tr0ng’
GO

SMSS isn't saying it can't use the database, it's asking for the password to open the master key.

What's the fix?

You have a few options, but here are the best two:

Click to the right of the message, you can actually put in the password and the wizard will let you continue without issue (since it can now open the master key).
If you're not using the master key... Drop It.

  It turned out that database did have master key.

Yes, as the error specified. This was not in doubt :)

  screenshots in my initial question are from the very same 2012 instance. Why SSMS 2012 and 2014 wizard did not mention anything about master key, but SSMS 2016 wizard did?

It was added in SSMS 2016... Not sure what you were looking for here as items are added to new versions.

  And also SSMS 2016 did not have any edit box to enter password.

I dunno - works in mine...

  I know exactly what you refer to, but there is nothing there in my case, just empty space under "Password" column, which added to confusion. Do you think there might be a bug in SSMS 2016 or you have some explanation for this behavior?

See the screenshot above - I think it's working as intended, at least for me... It might not be intuitive, but it works.

The end result is the same. If you're not a fan of the GUI giving you grief then there is always PowerShell and T-SQL :) If you feel that this isn't working as intended or you don't like it, I'd ask you to post feedback on Connect.


Insert results from query, update old existing row if query doesn't still return same value I am trying to refactor a table which contains historical data and therefore isn't ever deleted, but has an 'active' field.
Each day we run a query (a few joins, but quite slow) to get some data to insert into this table:

if the row exists already, and is present in the query, we do nothing
if the row exists but the result isn't in todays query, we set 'active' to false
if the row doesn't exist but is in the query, we insert it (with active defaulting to true)

It is possible for an entity to be active, made inactive (same row), then a few days later become active again (would be a new row).
The table is using a surrogate key, as the natural key can be repeated as noted above, though there should only ever be a single entry that is active on the natural key.
I'm curious if there is an action or 'pattern' I can take that will enable such functionality - I realise I'm asking a lot :)
I am using a java library (jOOQ) to generate the sql, so I am only looking for sql as the solution, no PL/pgSQL and no stored procs. I doubt it is possible without 2 passes (1. perform the update where not in query result, 2. perform the insert from query result) but I'm intrigued if there are possibilities.
Edit: Example table history, where ** is an update to same row
pk_surrogate | natural_id | active| date_made_active
1            | 1          | true  | 2020-01-01 -- row for entity 1 added
1 **         | 1          | false | 2020-01-01 -- row for entity 1 no longer present on 2020-01-02, so marked inactive
2            | 1          | true  | 2020-01-03 -- new row for entity 1 added when it was present on 2020-01-03
3            | 2          | true  | 2020-01-03 --new value for 2020-01-03

Something like this maybe?
with daily_query as (
  .... this is your current query ...
), upsert as (
  -- covers first and third requirement
  insert into the_table (....)
  select ...
  from daily_query
  on conflict (pk_column) do nothing
)
-- this sets those rows to in active that exist in the target table
-- but are not in the initial query
update the_table t
  set active = false
where not exists (select *
                  from daily_query q
                  where q.pk_column = t.pk_column);


Postgres: compare the dates in rows and find the difference I would like to check if a day is missing during the current month in my result set.The table looks like this.

date               balance
2017-06-01          404.66
2017-06-03          392.41
2017-06-07          295.41
2017-06-10          275.41
2017-06-13          155.24
2017-06-15          151.74
2017-06-20           11.15
2017-06-21           -5.15

Since the result set is always within  a month, only the difference in days is needed. I need to check the following two cases.

if the difference between dates is 1, then take the corresponding balance as it is,
if the date difference is bigger than 1, then multiply the number of day difference by the previous balance.

EDIT: For example, the day difference between the first two rows is 2. My desired output is 2*404,66.
And then, the day difference between 2nd and 3rd row is 4. So 4*392.41.

Is there any short and efficient way of doing this?

I need to analyze more than 300.000 data.


OK, so let's rephrase:

  for each id, if N=1, return price, else, return price for N=1 for the
  same id minus current price.

You can try this ugly:

select *, iif(tt.N = 1, t.price, isnull(tt.price, 0) - t.price)
from my_table t
left join (select * from my_table) tt on t.id = tt.id
where t.N = 1

Or a trendier version using first_value statement (starting from version 2012) and assuming we have, for all id, a row with N=1:

select *, iif(N=1,price, price - first_value(price) over(partition by id order by N) )
from my_table


Group By with inner join I am new to SQL world. I am practising on postgresql pgguide sample database . I wanted to count all the purchases of users who have bought a product 'Williams Selden'.  So i write something like below

select Count( p1.name) , u1.email from users as u1 INNER JOIN
purchases as p1 ON u1.id=p1.user_id
where u1.email IN (select u.email from users as u
INNER JOIN purchases p ON u.id=p.user_id
where p.name='Williams Selden') group by u1.email;

RESULT

 count |           email
-------+----------------------------
    15 | Shanell.Maxson@gmail.com
    23 | Ozella.Yoshimura@gmail.com
    14 | Isabel.Breeding@gmail.com

Now i want to know what all they purchased. Something like below.

However i am not able to do that. Please help.
I wrote

select DISTINCT p1.name , u1.email from users as u1 INNER JOIN
purchases as p1 ON u1.id=p1.user_id  where u1.email
IN (select u.email from users as u INNER JOIN purchases p ON
u.id=p.user_id where p.name='Williams Selden') group by u1.email,
p1.name;

As per first comment i removed the groupby and count and now i get something like. Maybe i am wrong at it, i do not want email with every record, i want it in rows just where  user email changes. Can it be done? If not, can i know why?

select p1.name , u1.email from users as u1 INNER JOIN purchases as p1
ON u1.id=p1.user_id
where u1.email IN (select u.email from users as u INNER JOIN
purchases p ON u.id=p.user_id where p.name='Williams Selden') order by
u1.email;

      name         |           email
----------------------+----------------------------
 Pauletta Arends      | Isabel.Breeding@gmail.com
 Miyoko Potter        | Isabel.Breeding@gmail.com
 Wendie Westmoreland  | Isabel.Breeding@gmail.com
 Williams Selden      | Isabel.Breeding@gmail.com
 Jettie Dilks         | Isabel.Breeding@gmail.com
 Catina Kiser         | Isabel.Breeding@gmail.com
 Bradly Vasko         | Isabel.Breeding@gmail.com
 Inocencia Cousineau  | Ozella.Yoshimura@gmail.com
 Cammy Bough          | Ozella.Yoshimura@gmail.com
 Danyelle Haefner     | Ozella.Yoshimura@gmail.com
 Ricarda Goldsberry   | Ozella.Yoshimura@gmail.com
 Cherryl Arends       | Ozella.Yoshimura@gmail.com
 Susanna Melendez     | Ozella.Yoshimura@gmail.com
 Granville Junge      | Ozella.Yoshimura@gmail.com
 Jerald Schuh         | Ozella.Yoshimura@gmail.com
 Miyoko Allbright     | Shanell.Maxson@gmail.com
 Tommie Ange          | Shanell.Maxson@gmail.com
 Williams Selden      | Shanell.Maxson@gmail.com
 Isabel Tripodi       | Shanell.Maxson@gmail.com

I would not apply formatting directly in the query, but rather at the application layer.

If you really want to, you could do it like this:

WITH CTE
AS
(
SELECT  p1.name,
        u1.email,
        ROW_NUMBER() OVER(PARTITION BY Email) as rownum,
        p1.user_id
FROM users as u1
INNER JOIN purchases as p1
ON u1.id=p1.user_id
WHERE u1.email IN (SELECT u.email
                   FROM users as u
                   INNER JOIN purchases p
                   ON u.id=p.user_id
                   WHERE p.name='Williams Selden')
)
SELECT
name,
CASE WHEN rownum = 1 THEN email
ELSE ''
END AS email
FROM CTE
ORDER BY user_id;

DB-Fiddle

But I would change the IN to EXISTS, due to how IN handles NULL values (and remove the extra inner join)

WITH CTE
AS
(
SELECT  p1.name,
        u1.email,
        ROW_NUMBER() OVER(PARTITION BY Email) as rownum,
        p1.user_id
FROM users as u1
INNER JOIN purchases as p1
ON u1.id=p1.user_id
WHERE EXISTS  (SELECT u1.email
               FROM  purchases p
               WHERE u1.id= p.user_id
               AND p.name='Williams Selden')
)
SELECT
name,
CASE WHEN rownum = 1 THEN email
ELSE ''
END AS email
FROM CTE
ORDER BY user_id;

DB-Fiddle

Result for both

| name                | email                      |
| ------------------- | -------------------------- |
| Pauletta Arends     | Isabel.Breeding@gmail.com  |
| Miyoko Potter       |                            |
| Wendie Westmoreland |                            |
| Williams Selden     |                            |
| Jettie Dilks        |                            |
| Catina Kiser        |                            |
| Bradly Vasko        |                            |
| Inocencia Cousineau | Ozella.Yoshimura@gmail.com |
| Cammy Bough         |                            |
| Williams Selden     |                            |


Query with WHERE NOT and NOT IN with subquery MySQL Version: 5.6.27

I am using the following two tables

TABLE: oc_product
product_id      int(11)
model           varchar(64)
tax_class_id    int(11)

and

TABLE: oc_product_to_category
product_id      int(11)
category_id     int(11)

I want to change the tax_class_id for a few products on TABLE oc_product. So I am running a SELECT query before UPDATE

SELECT `product_id`, `model`, `tax_class_id`
FROM `oc_product`
WHERE
NOT `tax_class_id` = 12
AND
`product_id`
NOT IN ( SELECT `product_id`
        FROM  `oc_product_to_category`
        WHERE  `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)
        )

Unfortunately the results are not as expected.

All I want is to select all product_id from the table oc_product where tax_class_id is not 12 and product_id does not exist in the results of the following sub query.

SELECT `product_id`
FROM  `oc_product_to_category`
WHERE  `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)

Did I miss something? Please advise!

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


How can this data be joined? Definition:

table_A                table_B
id,description         id,description,id_table_A

Column id_table_A is a foreign key to the primary key of table_A and is not mandatory.

Sample data:

table_A
1,some_desc_1
2,some_desc_2

table_B
1,some_desc_1,1
2,some_desc_2,2
3,some_desc_aaa,null
4,some_desc_bbb,null

I need to write a query that shows me all the records, including the records with the foreign key set to null.  What type of join is this?  I'm not able to show the records with null foreign key using an INNER JOIN.

Outer joins...

SELECT *
FROM
    table_B B
    LEFT OUTER JOIN
    table_A A ON B.id_table_A = A.id
;


Are cross-database queries expensive in SQL Server? Are cross-database queries expensive in SQL Server? All of the databases are in the same instance.

Sorry, I don't have enough reputation to comment on the question, but from my experience if the client application raises a transaction for a query which uses cross database joins then it would promote the transaction to distributed and have the overhead of a DTC transaction.

The DTC overhead in this case could be viewed as a negative to performance. Generally the difference would be negligible although Microsoft describe DTC transactions as such:

  Distributed transactions typically consume significant system resources

  Transaction Promotion

...which would suggest a performance degradation if your server cannot offer the resources it requires.

Just to clarify, the article above describes local transactions being promoted when remote systems are introduced, but I have seen this become the case for transactions on the same server when using cross database queries.

As Thomas Stringer points out in his comment, there will be extra overhead in authentication although I think as this will be SID-driven there will be minimal overhead there unless you have to use separate credentials to access the other database.

If there were difference in database settings which caused additional overhead in the join that could impact larger than the previous suggestions - for example database collation. Database collation could manifest as a functional difference, not just a performance difference.

I think Aaron has the strongest argument for performance with the optimizer not having the advantage of using relationships for cross-database queries whereas self-contained within a database you could use relationships to your advantage.


DB2 Tablespace in state Restore pending I have the problem that in my DB2 database (SLES, Db2 v10.5) my User Temp Tablespaces are all in state Restore pending, Storage must be defined, Storage may be defined.

When I set off the following command db2 list tablespace I get the following output (e.g.):

Tablespace ID                        = 6
Name                                 = USERTEMP4
Type                                 = System managed space
Contents                             = User Temporary data
State                                = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Can anybody help me to escape from this situation? I didn't start any restore recently so I'm wondering how or why the tablespaces have changed to this state.

May be you didn't do a restore, but someone surely did, as the indicated tablespace state is only possible when a redirected restore has been initiated -- this is well described in the manual.

Normally to complete the redirected restore you will need to issue SET TABLESPACE CONTAINERS FOR 6 USING (PATH '/whatever'), where "6" is the tablespace ID, and similarly for all other tablespaces in this state, then run RESTORE DB yourdb CONTINUE to complete the operation (details here).

However, since you apparently don't recognize this restore operation, you'll probably want to abort it instead: RESTORE DB yourdb ABORT.


Gap Locking in Read Committed isolation level in Mysql MYSQL VERSION : 5.7.X
STORAGE ENGINE : Innodb

I have a general idea that Read Committed Isolation will mostly use Shared and Exclusive Record Locks. But, as per mysql docs, there are some cases where even Read Committed has to use gap locking.

  READ COMMITTED
  ..... For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking.

IMHO, only record lock is enough. Can anyone explain the scenario for Gap locking and why mysql does that?

Gap locks are necessary under the read committed (RC) isolation level to prevent potential integrity violations due to concurrent inserts -- this is what the documentation statement

  Gap locking is only used for foreign-key constraint checking and duplicate-key checking.

implies.

Suppose transaction T1, with the RC isolation, updates the value of a column that is part of a unique key (index). Clearly this is only possible if the new key value does not already exist. Suppose also transaction T2, also with the RC isolation, attempts to insert a new record with the key value equal to that just created by T1. Due to the RC isolation level T2 does not see the change made by T1, since it has not been committed yet, so the duplicate key error is not raised. If it weren't for a gap lock on the unique index in question, this would eventually result in two records with the same purportedly unique key.

With MySQL 8 you can see this in action using the views data_locks and data_lock_waits in performance_schema. I've created this sample table:

create table fruits (
  id int not null auto_increment primary key,
  name varchar(10),
  property varchar(20)
);
create unique index fruits_ux1 on fruits (name, property);
insert into fruits (name, property)
values ('apple', 'red'), ('apple', 'tart'),
       ('banana', 'yellow'), ('banana', 'sweet');

In transaction 1 I update banana properties:

T1> set transaction isolation level read committed;

T1> begin;

T1> update fruits set property = concat(property, '?') where name = 'banana';

I can see that key entries with new values have gap locks set on them (these rows don't really exist until the transaction is committed, so standard X locks can't be used here):

mysql> select thread_id, object_name, index_name, engine_lock_id, lock_type, lock_mode, lock_data  from performance_schema.data_locks;
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+
| thread_id | object_name | index_name | engine_lock_id                        | lock_type | lock_mode     | lock_data              |
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+
|        47 | fruits      | NULL       | 140491829069632:1066:140491735749880  | TABLE     | IX            | NULL                   |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:1:140491735746840 | RECORD    | X             | supremum pseudo-record |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:4:140491735746840 | RECORD    | X             | 'banana', 'yellow', 3  |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:5:140491735746840 | RECORD    | X             | 'banana', 'sweet', 4   |
|        47 | fruits      | PRIMARY    | 140491829069632:5:4:6:140491735747184 | RECORD    | X,REC_NOT_GAP | 4                      |
|        47 | fruits      | PRIMARY    | 140491829069632:5:4:7:140491735747184 | RECORD    | X,REC_NOT_GAP | 3                      |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:6:140491735747528 | RECORD    | X,GAP         | 'banana', 'sweet?', 4  |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:7:140491735747528 | RECORD    | X,GAP         | 'banana', 'yellow?', 3 |
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+

In transaction 2 I now try to insert a possible duplicate:

T2> set transaction isolation level read committed;

T2> begin;

T2> insert into fruits (name, property) values ('banana', 'sweet?');

The statement blocks, and I can see that it blocks on the gap lock for that unique index key (note the blocking_engine_lock_id value):

mysql> select requesting_engine_lock_id, blocking_engine_lock_id from performance_schema.data_lock_waits;
+---------------------------------------+---------------------------------------+
| requesting_engine_lock_id             | blocking_engine_lock_id               |
+---------------------------------------+---------------------------------------+
| 140491829070536:5:5:6:140491735753104 | 140491829069632:5:5:6:140491735747872 |
+---------------------------------------+---------------------------------------+

Similarly, if T1 deletes a row from a parent table, while T2 attempts to insert a new record to a child table referencing the row just deleted by T1 (which it still sees), the gap lock on the foreign key index prevents insertion of a potential orphan record.


Why the query scan all the partitions? I have the following partitioned table

create table T (Month char(6), ID int, .... primary key (ID, Month)) on psMonth(Month)

The partition function is

CREATE PARTITION FUNCTION [pfMonth](char(6)) AS
RANGE RIGHT FOR VALUES (N'200001', .... N'200705', N'200706', N'200707', ....)

However, the actual partition count of following query shows is 260 instead of 1?

select count(*)
from T (nolock)
where Month = '200706'

The execution plan is

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[globalagg1006],0)))
       |--Stream Aggregate(DEFINE:([globalagg1006]=SUM([partialagg1005])))
            |--Parallelism(Gather Streams)
                 |--Stream Aggregate(DEFINE:([partialagg1005]=Count(*)))
                      |--Clustered Index Scan(OBJECT:([DB].[dbo].[T].[PK_T]), WHERE:([DB].[dbo].[T].[Month]=[@1]))

Update:
The query run fast and the Actual Partition Count is 1 if I run the following query.

select count(*), $partition.pfMonth(Month)
from T with (nolock)
where Month = '200706'
group by $partition.pfMonth(Month)

Update 2:
The following query also only scan one partition.

select count(*), min($partition.pfByMonth(Month))
from T
where Month = '200707'

Update 3:
The original SQL scans only one partition now. No clue what happened. The only difference is that more data has been inserted into the table.
Answer: I used a CTE which wrapped the table. And it worked around the issue caused by type precedence. (However, the CTE didn't work around the issue at first though, the whole thing is strange and unpredictable.)

Your column is of type CHAR(6). Your filter is of type VARCHAR (perhaps counter intuitive, but that what the constant literal '200706' is). The rules of Data Type Precedence dictate that the comparison occurs using the type with higher precedence, ie. the VARCHAR type. This is a different type than the partitioning function, therefore you do not get partition elimination.

Try this instead:

select count(*)
from T (nolock)
where Month = CAST('200706' as CHAR(6));

Your plan should get a partitioning elimination filter.