Can a table have multiple editioning views in Oracle RDBMS 11 Can a table ABC have multiple editioning views in Oracle Database?

I have an editioning view ABC (created from table ABC) that shows the old edition to be OLD_EDITION_1 as its edition_name in the USER_OBJECTS table. I am trying to create a new editioning view named ABC with an edition name of NEW_EDITION_2.

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

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

Editions
Editioning views

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

edition_name.schema_name.object_name

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

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

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

create edition e1;

grant use on edition e1 to usr;

conn usr/usr

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

select object_name, object_type, edition_name
from   user_objects;

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

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

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

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

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

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

alter session set edition = e1;

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

select object_name, object_type, edition_name
from   user_objects;

OBJECT_NAME   OBJECT_TYPE   EDITION_NAME
abc           TABLE         <null>
ABC           VIEW          E1

select object_name, object_type, edition_name
from   user_objects_ae;

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

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

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

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

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


Emulate REGEXP like behaviour in DB2 SQL I posted the same on stackoverflow (please, let me know if I have to delete one).

I'm working on a DB2 database and as far as I can see regexp is not supported (without additional libraries).

So I cannot implement something similar to what is explained in this article "Bringing the Power of Regular Expression Matching to SQL"

Do you know if I can "emulate", with a SQL statement, a regular expression like this?

  ^a[aofdmep]{1}[a-z]{1}[a-z0-9]{4}[sidbfkfpo]{1}

Edit

In the above hypothesis I found that it's acceptable for my case a WHERE Like predicate:

  WHERE USER_NAME NOT LIKE 'a_______'

But it's a unsafe and doesn't cover other cases where I don't have a fixed char that I can match.

Since this old question has been dug up anyway, I'll mention that you can use the built-in XQuery support in DB2 for regular expression matching, something along the lines of

select whatever
from users
where
   xmlcast(
     xmlquery('fn:matches($USER_NAME,"^a[aofdmep][a-z][a-z0-9]{4}[sidbfkfpo]")')
     as integer) = 1

XMLQUERY above calls the XQuery matches function against the column USER_NAME. The result is an XML boolean, so the XMLCAST is used to convert it to an SQL data type.


Writing to a DKNF database, but reading from a de-normalized table for performance? Would the following approach be reasonable?

The Goal: queries have to be relatively fast, but without the hassles incurred by writing to a de-normalized database.

The Context: A database for handling user profiles for some CMS. User profiles sometimes change, but are not updated too regularly; so for the most part the information for each user remains the same for some time.

The Database Design:

First, we put together a highly normalized database, call it "N". User profiles are stored in N.
Being highly normalized, N is secure and elegant to update, but slow to query. So we take all the data stored in N and we create a single de-normalized table. We call this table T.
T is used for the sole purpose of retrieving user information. It is only read from, not written to, because it's prone to anomalies and would be a mess to update.

With one exception:

To table T, we add a boolean column, "hasChanged", default value "FALSE".

The CMS's algorithm for handling queries:

Suppose we want to load user 47's profile.

If hasChanged == FALSE for a row with ID == 47, we can safely retrieve the user profile from this row. (Because we know nothing has changed.)
If hasChanged == TRUE, then row 47 in T cannot be trusted (something has changed), so the query is passed on to the reliable but slow N.
When a user updates his profile, N is updated. When N is updated, hasChanged is set to TRUE on all rows in T containing information that could be affected by this change.
T is completely rewritten by the system whenever it becomes too unreliable (this is determined by how frequently queries are passed on to N).

Use the full normalized database, scan which queries need more performance than this model can give them and create materialized views on them. If the data is very volatile, having them fast refreshing introduces some overhead but you know the value and validity of your data, that is protected by the database. Oracle handles this very nice.

You queries remain using the normalized tables and Query Rewrite will take care of the usage of the Materialized Views, when appropriate. When the Materialized Views are not fresh, the queries fall back to the original tables.

For docu see Materialized View Concepts and Architecture


How to dump multiple databases of the same cluster in single execution? I have 100 databases in my cluster. I need to backup 30 databases (as single file or multiple file, anything is fine). I could do it for tables using pg_dump with -t.

Is there any way to do something like this (pseudocode):

pg_dump -d db1 -d db2 -d db3 -Fc > dbs_backup.backup

pg_dump can only dump a single DB. The manual:

  pg_dump only dumps a single database. To back up an entire cluster, or
  to back up global objects that are common to all databases in a
  cluster (such as roles and tablespaces), use pg_dumpall.

pg_dumpall, on the other hand can only dump all DBs in the cluster, even if you provide a specific DB name in the connection string. The manual:

  -d connstr
  --dbname=connstr

  Specifies parameters used to connect to the server, as a connection
  string. See Section 34.1.1 for more information.

  The option is called --dbname for consistency with other client
  applications, but because pg_dumpall needs to connect to many
  databases, the database name in the connection string will be ignored.
  Use the -l option to specify the name of the database used for the
  initial connection, which will dump global objects and discover what
  other databases should be dumped.

To dump some DBs but not all, use a shell script, like has been advised.
You may want to include commands to create each DB in the dump:

How to get a pg_dump -s to include the CREATE DATABASE command?


SQL Server Login attempts fail when using SSMS but succeed when using SQLCMD I have my SQLServer 2005 instance configured for mixed mode fine. When using SSMS, none of the sql server logins seem to work. Just "Login failed for user '..'" message. But when I use SQLCMD, it works fine. The behavior is not just for one specific login account. All sql server logins including 'sa' fail when tried via SSMS. Windows logins work fine.

Any ideas?

Issue seems to be with the local SSMS install on the server. Everything works fine from odbc connections, remote SSMS connections etc.


Should I be alarmed by this NO JOIN PREDICATE warning? I'm troubleshooting the bits and pieces of a poorly-performing stored procedure. This section of the procedure is throwing a NO JOIN PREDICATE warning

select
    method =
        case methoddescription
            when 'blah' then 'Ethylene Oxide'
            when NULL then 'N/A'
            else methoddescription
        end,
    testmethod =
        case methoddescription
            when 'blah' then 'Biological Indicators'
            when NULL then 'N/A'
            else 'Dosimeter Reports'
        end,
    result =
        case when l.res is null or l.res <> 1 then 'Failed'
        else 'Passed'
        end,
    datecomplete = COALESCE(CONVERT(varchar(10), NULL, 101),'N/A')
from db2.dbo.view ls
    join db1.dbo.table l
        on ls.id = l.id
    where item = '19003'
        and l.id = '732820'

The view ([ls]) calls a remote server (the remote query %41 on the right of the plan).

Here is an image of the plan:

I only ask this question because of this blog post and I want to make sure this won't come back to bite me later.

Because we know that l.id = '732820' and ls.id = l.id then SQL Server derives that ls.id = '732820'

i.e.

FROM   db2.dbo.VIEW ls
       JOIN db1.dbo.table l
         ON ls.id = l.id
WHERE  l.id = '732820'

is the same as

  ( /*...*/ FROM   db2.dbo.VIEW ls WHERE id = '732820'  )
   CROSS JOIN
  ( /*...*/  FROM   db1.dbo.table l WHERE id = '732820'  )

This rewrite is not bad for performance .

  This derivation is a good thing.  It allows SQL Server to filter out
  rows ... earlier than would otherwise be possible.


How do I identify the remote db agent name to use in create_database_destination on Oracle 11gR2? I am trying to setup DBMS_SCHEDULER in Oracle 11g to run a remote database job.

I have a remote Oracle 11g R2 database on unix and a local one on Windows.

I read that you can install the oracle scheduler agent from the 11g client install for machines that don't have Oracle installed but this is not needed for running remote jobs if Oracle is present on both machines.  With the remote agent installation, you run schagent and provide parameters to register the agent to the remote machine but I cant find any instructions on the web regarding how to register remote agents when both machines have Oracle installed or what to use as the agent name in this case.

I have added an entry to tnsnames.ora for the remote DB and can tnsping, etc.

If I run the dbms_scheduler.create_database_destination procedure, it requires an agent name but where can I find this for the database or how can I check that it is running on Unix or Windows?

Scheduler agent is part of database Oracle Home. Anyway must be manually deployed; there is no way to use the database as agent. I think that the documentation is misleading on this case. To help you I will write steps to be performed for agent configuration. I suppose that on the central database you have already enabled HTTP port (etc.).
On the remote database (the one where agent have to be configure):

cd $ORACLE_HOME\bin
extjobo -createagentzip execution_agent.zip

Unzip the file into a directory under the $ORACLE_HOME\scheduler.

cd $ORACLE_HOME\scheduler
unzip execution_agent.zip

Modify the file named schagent.conf located in the $ORACLE_HOME\scheduler\execution_agent directory setting the Scheduler Agent port number to listen for connections. (the one you should have already set with dbms_xdb.sethttpport() )

PORT=<schagent_port>

You have to run the root.sh to fix the uid. (You can follow the README file in the execution_agent directory and run the root.sh). NOTE Verify a directory named /wallet exists under /execution_agent/data before running root.sh. If not create them manually and then run root.sh.

Register the database with.

set ORACLE_SID=<your sid>
cd $ORACLE_HOME\scheduler\execution_agent\bin
schagent -registerdatabase <db_host> <db_http_port>

You will be asked to input the registration password. Last start manually the agent:

schagent -start

These steps will install and configure the agent. Now you can create credentials on the central database and create the database destination. Database destination can exists along with a remote agent destination.


Rebuilding the transaction log We have a very large database (~6TB), whose transaction log file was deleted (while SQL Server was shut down. We have tried:

Detaching and reattaching the database; and
Undeleting the transaction log file

...but nothing has worked so far.
We are currently running:
ALTER DATABASE <dbname> REBUILD
LOG ON (NAME=<dbname>,FILENAME='<logfilepath>')

... but given the size of the database, this will probably take a few days to complete.
Questions

Is there a difference between the command above and the following one?
  DBCC CHECKDB ('<dbname>', REPAIR_ALLOW_DATA_LOSS)

Should we be executing REPAIR_ALLOW_DATA_LOSS instead?

It's worth noting that the data is derived from other sources so the database can be rebuilt, however we suspect it will be much quicker to repair the database than to reinsert all the data again.

Update
For those keeping score: the ALTER DATABASE/REBUILD LOG command completed after around 36hrs and reported:

Warning: The log for database 'dbname' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were.
You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

We then ran a DBCC CHECKDB (took about 13hrs) which was successful. Let's just say that we've all learnt the importance of database backups (and granting project managers access to the server...).

Never detach a Suspect database. Anyway, how did you attach the database after detaching it? You used CREATE DATABASE with FOR ATTACH_REBUILD_LOG option?

These commands should have done the trick:

ALTER DATABASE recovery_test_2 SET EMERGENCY;
ALTER DATABASE recovery_test_2 SET SINGLE_USER;

DBCC CHECKDB (recovery_test_2, REPAIR_ALLOW_DATA_LOSS)
WITH NO_INFOMSGS, ALL_ERRORMSGS;

I wrote a post for this situation:

SQL 2005/2008 Database Recovery Procedure – Log File Deleted (Part 3)

You asked about the difference between:

DBCC CHECKDB ('<dbname>', REPAIR_ALLOW_DATA_LOSS) and
ALTER DATABASE <dbname> REBUILD LOG ON (NAME=<dbname>,FILENAME='<logfilepath>')

The thing is that you can run both to rebuild the log file, but with CHECKDB you rebuild the log and check database for integrity errors as well.

Also the second (alter database) will not work if there were active transactions (not written to disk) when the log file was lost. At start-up or attach, SQL Server will want to perform recovery (rollback and rollforward) from the log file which is not there. It happens when a disk crashes or an unexpected shutdown of the server occurs and the database is not cleanly shutdown. I guess it was not your case and all sorted out good for you.

DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS) run on a database in emergency status checks the database for inconsistency errors, tries first to use the log file to recover from any inconsistencies. If this is missing, the transaction log is rebuilt.
ALTER DATABASE REBUILD LOG ON... is an undocumented procedure and requires a subsequent DBCC CHECKDB to fix any errors.


How do we free up disk space from allocated namespace? / ORA-03297 We are running out of disk space.

The server has a 420 GB HDD, and runs a Microsoft SQL Server and an Oracle Server(10g).

The Oracle server uses up to 315 GB space.  We have just loaded a 100GB Microsoft SQL Server database.  Now we have approximately 5GB free and need to make a backup of the SQL Server database.

First we tried to move the backup to an other drive but this didn't work.

Then we tried to resize the allocated space of our Oracle Server, which consists of 6 files of 20GB each with about 4GB used in each file.  When we tried to resize this file to 10GB, we got the Oracle Error ORA-03297 but it shows 4GB used.  After several tries we could shorten some files to 16/17/18GB, but we are still in need of more space.

Is there a Solution how we can resize the Oracle files to 10GB each?

We've tried Coalesce and deallocate unused space, however that did not work either.

You are receiving an Oracle ORA-03297 error because the HWM (High Water Mark) of a table is beyond the size you tried to shrink a datafile to.

First try and "shrink space" for each affected table:

alter table fragmentedtable enable row movement;

alter table fragmentedtable shrink space;

Next, check to see how much space can be freed from each of the existing tablespace files without moving the current database objects.

The following (taken from Ask Tom & assuming a block size of 8k) tells you how much space you can currently free from each datafile, in megabytes:

select file_name,
       ceil( (nvl(hwm,1)*8192)/1024/1024 ) SHRINK_TO,
       ceil( blocks*8192/1024/1024) CURRENT_SIZE,
       ceil( blocks*8192/1024/1024) -
       ceil( (nvl(hwm,1)*8192)/1024/1024 ) SAVINGS
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)

For example:

FILE_NAME                                           SHRINK_TO CURRENT_SIZE    SAVINGS
-------------------------------------------------- ---------- ------------ ----------
/u01/app/oracle/oradata/PHIL112/system01.dbf              696          700          4
/u01/app/oracle/oradata/PHIL112/sysaux01.dbf              579          610         31
/u01/app/oracle/oradata/PHIL112/users01.dbf                85           93          8
/u01/app/oracle/oradata/PHIL112/undotbs01.dbf              65          120         55

SQL>

To resize, use:

alter database datafile '/path/to/data/file.dbf' resize 100m;

The next option you have is to create a new tablespace (and associated datafile(s)) and move the current database objects to the new tablespace. You can do this by using datapump, or do it manually. Tim Hall has an excellent article on how to perform this here.


What is the value of sort_buffer_size?  200M might be reasonable on your machine.  (A sort might be involved in the index creation.)

Keep in mind that it will rebuild all the indexes.  Still 58 hours is unreasonable.

What is the value of innodb_buffer_pool_size?  It may be using that.  It should be about 11G for that size machine, assuming you are using only InnoDB.


Tools or built-in functionality to alert upon table data changes We have the following (unfortunate) scenario: we have a database where on-the-fly changes are being made by developers as quick remedies to dictionary tables (order types, document types, fee distributions, and so forth). This wouldn't be an issue if these particular developers didn't forget to check their changes into TFS, but alas, we face it. This causes an issue where the database project(s) in TFS do not properly reflect what is in the database.

Essentially, I want receive an e-mail or other type of notification (table insert with necessary information) when a change is made to 15-20 tables, what the change was, and who committed the transaction.

What is the best method to monitor this type of behavior? We are willing to invest in tools that allow for this ability. I've checked out RedGate SQL Monitor, but it doesn't seem to have this feature set (or I am missing it).

I've also seen usage of triggers, but we have a large amount of tables that would require monitoring and I don't know the performance degradation that might incur and if we can collect the appropriate data. I do like the trigger approach where it would insert the information into a table ([dbo].[TABLE_UPDATES]) because it would allow us to build an SSRS report to run against and pull data as needed, but I'm not sure I 100% trust this as a solution.

We currently use RedGate DLM Dashboard for monitoring schema changes, and it has proven quite invaluable to receive those updates. We just want to expand it to the data itself.

Disclaimer: I know - terrible practice by developers to apply things directly to a database without checking in their changes, but we are currently having to deal with this hurdle and reign these individuals from wildly shooting at the hip. We are wanting a long-term solution for accountability purposes as our database and company grows.

Thanks in advance.

If you need the data changes themselves then you will probably need to use Triggers. You could convert all values to string, build an HTML table of those changes, and then send that via sp_send_dbmail. Please note that while sp_send_dbmail does accept a query to include the results of in the email (either in the body or as an attached file), it runs in a separate session so it won't have access to the INSERTED or DELETED pseudo-tables, nor even local temporary tables (and global temporary tables either might not exist when the query is run or it would at least interfere with multiple DML operations happening at the same time, even if those are unlikely). You could also include the login information from SESSION_USER and ORIGINAL_LOGIN().

Regarding performance and Triggers, there doesn't seem to be much reason for concern:

These are lookup tables and there aren't that many changes coming through.
The sp_send_dbmail command runs asynchronously via Service Broker.

Another option is to track the data automatically in your version control system (e.g. TFS). This does not mean that the changes are introduced automatically into release scripts, but the changes are tracked so that you can investigate what changed and then figure out how to best get it scripted into your release process. I wrote an article on SQL Server Central several years ago about doing just this: Exporting and Versioning Lookup Data: A Real-World Use of the CLR (free registration is required to read content on that site). That article discusses using a SQLCLR Stored Procedure to do a daily/nightly export of the lookup tables into delimited files. Please note that the SQLCLR Stored Procedure, DB_BulkExport, is found in the SQL# library (that I created), and was available in the Free version when the article was written, but is now only in the Full version and no longer available for free. However, there is always the option of writing ones own SQLCLR Stored Procedure to do the same thing.

The system that I developed and discussed in that article is based upon running via a SQL Server Agent job once per night. But we had hundreds of lookup tables. The effects of running nightly are that you miss incremental changes made during the day, and you aren't able to capture who made those changes. If you need each individual change and who did it, then you can rethink that approach to get kicked off from a Trigger that exports the data and calls whatever external program handles the commit into the versioning system. A SQLCLR Trigger might be able to accomplish the export and calling the "commit" program all at the same time since it does have access to the INSERTED and DELETED tables.


SUM function fails when tie exist I want to do ABC analysis on this.
As you see, each model has severval partID, each partID has a number of counts.
As you see, the Cum_Counts is wrong when 'ties' exists.

I want to calcute Cumulated counts in each Model.

SELECT
      Model,
      PartID,
      PartDesc,
      Counts,
      SUM(Counts) OVER (PARTITION BY Model ORDER BY Counts DESC) AS Cum_Counts,
      SUM(Counts) OVER (PARTITION BY Model) AS Total_Counts
FROM ASL_2
ORDER BY Model

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

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

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

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


MYSQL - Problem with foreign keys on same table In Mysql 5.5, I have a table, in which I store some informations about "events".. In order to have a chronological order, I made two columns that reference to the previous and to the next event. So I made two foreign keys that reference to the primary key of the same table.
If a row is the first in the chronological order, the value of the previous element would be null.

So, my table is:

calendars
--------
calendar_id (PRIMARY KEY)
calendar_prev
calendar_next
other columns...

I have this data (as an example)

+--------------+---------------+---------------+
|  calendar_id | calendar_prev | calendar_next |
+--------------+---------------+---------------+
|       1      |    NULL       |       2       |
|       2      |      1        |       3       |
|       3      |      2        |       4       |
|       4      |      3        |       5       |
|       5      |      4        |     NULL      |
+--------------+---------------+---------------+

and these foreign keys.

ALTER TABLE `calendars`
ADD CONSTRAINT `calendars_ibfk_3` FOREIGN KEY (`calendar_next`) REFERENCES `calendars` (`calendar_id`) ON DELETE SET NULL ON UPDATE CASCADE,
ADD CONSTRAINT `calendars_ibfk_2` FOREIGN KEY (`calendar_prev`) REFERENCES `calendars` (`calendar_id`) ON DELETE SET NULL ON UPDATE CASCADE;

This works fine on my server and on sqlfiddle.

The problem comes when I try to modify the ID of an element.
If I'm not missing something about how foreign keys work, if I edit the calendar_id 5 and set it, for instance, to 7, the row with calendar_id = 4 should change the calendar_next value as well, from 5 to 7. But i just get an error.
In other words, if I try this:

UPDATE `calendars` SET `calendar_id` = 7 WHERE `calendar_id` = 5;

I get this error:

 Cannot delete or update a parent row: a foreign key constraint fails (`db_2_813f44`.`calendars`, CONSTRAINT `calendars_ibfk_3` FOREIGN KEY (`calendar_next`) REFERENCES `calendars` (`calendar_id`) ON DELETE SET NULL ON UPDATE CASCADE):

I really can't figure out what I am doing wrong.

You have identified the cause of the problem. A possible workaround is to create another table for the linked list:

CREATE TABLE calendars
  ( calendar_id INT PRIMARY KEY
  -- other columns
  ) ;

CREATE TABLE calendar_list
  ( calendar_id INT NOT NULL
  , calendar_next INT NOT NULL
  , PRIMARY KEY (calendar_id)
  , UNIQUE (calendar_next)
  , CONSTRAINT calendar_list_1
      FOREIGN KEY (calendar_id)
      REFERENCES calendars (calendar_id)
      ON DELETE CASCADE ON UPDATE CASCADE
  , CONSTRAINT calendar_list_2
      FOREIGN KEY (calendar_next)
      REFERENCES calendars (calendar_id)
      ON DELETE CASCADE ON UPDATE CASCADE
  ) ;

The following query can then be used to show what you have now:

SELECT c.calendar_id,
       prev.calendar_id AS calendar_prev,
       next.calendar_next
FROM calendars AS c
  LEFT JOIN calendar_list AS prev
    ON c.calendar_id = prev.calendar_next
  LEFT JOIN calendar_list AS next
    ON c.calendar_id = next.calendar_id ;

The UPDATE will work fine with this setup. Test at SQL-Fiddle. (Deletes would be a different issue but that wasn't addressed with the previous design either. Setting to null would break the list if the delete was anywhere but the first or last point.)


Looking for recommendation I am a non IT background, my recent projects uses PowerShell for a lot of SQL Server administration. I would like to start from absolute zero, and learn to use and code the Windows PowerShell as it was meant to be used. I am looking for a good book for beginners or other materials? Thoughts?

Check out these article series on SQLServerCentral.com:

Stairway to Server Management Objects (SMO)

Stairway to SQL PowerShell

These are a series of articles that provide great foundational skills right through to some advanced concepts and practises with specific focus on SQL Server and SMO.


Reason for using write barrier before unlocking the buffer header in Postgresql I was reading the source code for buffer management in Postgres. In buffer management, there is a macro to unlock the buffer header
#define UnlockBufHdr(desc, s)   \
    do {    \
        pg_write_barrier(); \
        pg_atomic_write_u32(&(desc)->state, (s) & (~BM_LOCKED)); \
    } while (0)

This code clears the BM_LOCKED flag in state variable present inside buffer header atomically.
I know that pg_write_barrier() blocks the compiler from reordering loads and stores around the barrier and also ensures that the stores issued prior to the barrier are completed before
any stores issued after the barrier. This is used to remove the inconsistencies raised in SMP systems due to out of order execution.
But I am unable to see how reordering memory accesses here can be a problem. It will be of great help if someone can give me the reason for using a write barrier before the unlocking operation.

Without looking at the code, that seems not surprising to me: if you have the buffer header locked and write to it, then you want all these writes to happen before you unlock the buffer, right?


Is there a "Full Containment" option for databases in SQL Server 2014? In creating a new database on SQL Server 2014, the "Containment Type" dropdown in the create database dialog has only "None", and "Partial".  This kind of hints that there should be a "Full" option, but there isn't.

And, indeed, this article actually refers to both partially contained, and fully contained databases.

https://msdn.microsoft.com/en-us/library/ff929071.aspx

Is there some SQL Server configuration option that I didn't select when installing SQL Server?  Or is there really just no actual "Fully Contained" option?

SQL Server doesn't support fully contained databases (yet).

Azure SQL databases are fully contained.


Converting dbo file from windows to linux I have a .dbo file which is created from windows. This file is succesfully reloaded into the mysql database in windows. I need to reload the dbo file into the mysql/mariadb database in linux. How I convert the file that was created from windows to linux?

You said you have reloaded the file into a MySQL database.
A simple way is:

use mysqldump to backup the the data into a file
transfer the file to the linux system
use command source filename.sql to load the data into the MySQL database on the linux server.


DBMS_DATAPUMP error: The current API cannot be executed because of inconsistencies between the API and the current definition of the job I'm trying to do an impdp on a Amazon RDS Oracle instance using DBMS_DATAPUMP.

I'm following the steps described in the Amazon RDS guide (Importing Data into Oracle on Amazon RDS).

I've already copied successfully the dmp to the RDS instances.

This is my impdp job executed with awssys user:

-- IMPORT DATAPUMP
DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => '20180618_my_copied.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''MYSCHEMA'')');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/

I can't understand the meaning of the error displayed:

ORA-39002: operazione non valida
ORA-06512: a "SYS.DBMS_DATAPUMP", line 6224
ORA-06512: a line 7
39002. 00000 -  "invalid operation"
*Cause:    The current API cannot be executed because of inconsistencies
           between the API and the current definition of the job.
           Subsequent messages supplied by DBMS_DATAPUMP.GET_STATUS
           will further describe the error.
*Action:   Modify the API call to be consistent with the current job or
           redefine the job in a manner that will support the specified API.

This is the version of the source DB:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE   11.2.0.1.0  Production"
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

This is the version of Amazon RDS instance:

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE   12.1.0.2.0  Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

EDIT: I've added version=COMPATIBLE to both the expdp command and DBMS_DATAPUMP options but error remains.

expdp system@source_db version=COMPATIBLE dumpfile=20180618_my.dmp logfile=20180618_my.log directory=DATA_PUMP_DIR CONSISTENT=Y  SCHEMAS=MYSCHEMA,MYSCHEMA2,MYSCHEMA3,MYSCHEMA4

Your versions differ between source and destination.

hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name=>null, version=>'COMPATIBLE');

Oh, the bad news: you need this for the expdp too.


How to read "CPU (ms/sec)" in the "Recent Expensive Queries" of Activity Monitor? Is CPU (ms/sec) the cpu time in ms in previous second? If it reaches 1000, does it means that it uses 100% CPU?

However, on my 8 cores server, the % Processor Time is usually very low like several percent when the most expensive query in the "Recent Expensive Queries" reaches 1000.

This is the ratio of CPU time used by that query per second. Internally to SQL Server in this DMV (sys.dm_exec_query_stats) CPU time is measured in microseconds and the report is pulling the usage per second. However, the ms in the report is in milliseconds for the Activity Monitor and microsecond would be mcs.

The reason you're seeing 1000 in this result set could be because that query used all CPU time for that one-second interval. Restated, it has used 1000 ms of CPU time for that second. On a system with multiple schedulers (sys.dm_os_schedulers), it is possible to have queries go parallel and use multiple schedulers and have > 1000ms/sec of CPU time. In your example below, 500ms of CPU time for the SQLOS scheduler has been consumed by that query in the last second. This means that 50% of one CPU's time was dedicated to this query. Note that it could have been a parallel query and only used 10% of five schedulers (which correlate to the number of CPUs). Depending on the timing of these parallel threads, it could present as low CPU usage in the % Processor Time. In your performance monitor are you looking at the _Total or per processor? Also, you can run the query below to see how SQL Server is reporting its own CPU utilization (thank you Glenn Berry).

DECLARE @ts_now BIGINT;
SELECT @ts_now = cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info;
SELECT TOP(10)
    SQLProcessUtilization AS sql_server_process_cpu,
    SystemIdle AS system_idle_process_cpu,
    100 - SystemIdle - SQLProcessUtilization AS other_process_cpu,
    DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS event_time
    FROM (
            SELECT
                record.value('(./Record/@id)[1]', 'int') AS record_id,
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS [SQLProcessUtilization],
                [timestamp]
            FROM (
                    SELECT [timestamp], convert(xml, record) AS [record]
                    FROM sys.dm_os_ring_buffers
                    WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
                        AND record LIKE '%<SystemHealth>%') AS x
                  ) AS y
    ORDER BY record_id DESC;

Do keep in mind that total_worker_time is the amount of CPU time used by the query since the plan was compiled*. Take a look at the Executions/min right next to the CPU (ms/sec) in the report to get a sense of whether or not this query plan has been used multiple times. Note: This may require math. Having said all of this, I fired up Activity Monitor and looked at one of the queries that was showing high CPU time on a development instance. I then went to the DMV to look at total_cpu_time for that query and the number is quite high (which is to be expected). This leads me to believe that the math is being handled internally by Activity Monitor and is taking the difference between current total_cpu_time and what it knew the total_cpu_time to be 1 second ago.

So in summary, this is showing you how much total_cpu_time in microseconds the query in question has used in the last second.

I'd also make a note of that query and look at the sys.dm_exec_query_stats results for that query plan hash and look at the other information provided by that DMV.


Scalar Operator in Seek Predicate I have following simplified version of my actual query, in SQL Server 2012. It has a scalar operator in seek predicate when selecting data from Containers table.

What is the purpose of scalar operator in this seek predicate?

CREATE TABLE #EligibleOrders (OrderID INT PRIMARY KEY,
                             StatusCD CHAR(3),
                              CreatedOnDate DATETIME
                              )
--insert logic into #EligibleOrders

--Final Query
SELECT T2.OrderID ,olic.LineItemID,
        SUM(c.quantity) AS ShippedQty,
        COUNT(DISTINCT c.ContainerID) AS ShippedCases
FROM #EligibleOrders T2
INNER JOIN dbo.OrderLineItemContainers (NOLOCK) AS olic
    ON  olic.OrderID = T2.OrderID
INNER JOIN dbo.Containers (NOLOCK) AS c
    ON olic.Containerid = c.Containerid
GROUP BY T2.OrderID ,olic.LineitemID
OPTION (MAXDOP 1)

Execution Plan

Seek Predicate

What is the purpose of scalar operator in this seek predicate?

The "Scalar Operator" on the olic.ContainerID column in the question simply indicates that column outer reference is providing a single row (scalar) per iteration of the nested loop (apply). It's purely architectural and nothing to worry about.
Details
Internally, the query processor acts on a tree representation, which at a basic level contains a combination of relational (table-valued) and scalar (single row) operations.
When SQL Server builds showplan output, each operator in the tree (that is enabled for public showplan) is asked to produce a representation suitable for the output target (SHOWPLAN_XML, SHOWPLAN_TEXT, STATISTICS XML...and so on).
The XML output format has to comply with the showplan schema, which contains elements for both relational and scalar operators. The schema specifies scalar operator elements in many places.
Whether the specific text "Scalar Operator(...)" appears in an SSMS tooltip, in the Properties window, or only in the raw XML depends on implementation details at each layer.
For example, the simple query:
SELECT TOP (1) 1;

...produces a plan without "Scalar Operator" in the SSMS tooltips, but present for the Compute Scalar relational operator in the properties window:

...and only in the raw XML for the Top relational operator:

For all practical purposes, the "Scalar Operator" text should simply be ignored. It means nothing except that the thing it encloses is a scalar.


Is it OK to create hundreds of databases in SQL Azure versus one big one and run the risk of deadlocking I need to create highly scalable solution - where field devices in thousands of sites are delivering data in real time to a back end system, and SQL Azure seems to fit the bill nicely in terms of adding sql databases and application servers.

Each field device is effectively sending 400 sensor values every second - for about two hours a day, and those 400 sensor values every 5 minutes for all other times forever. Additionally, when an error occurs on this field device, it will send up the last minute's data for all 400 sensors as well (400 * 60 readings) - causing a mass flood of data when anything goes wrong.

I really want to design the system so that the independent field devices and the data in which they store can not affect other devices. Allowing each field device to not affect the performance of other field devices.

I started the design with thinking a single database holding all the device's data - but have started to get deadlocks occurring when simulating multiple site devices. Hence, am in the process of moving to a multiple database solution. Where a master database holds a lookup table for all the devices - returning a connection string to the real database

At this stage of the project, it is most important that I am able to pass that data back to User Interfaces running in web browsers in real time - updating their screens every second.

In future stages of the project it will be necessary to start aggregating data across multiple devices showing statistics such as sum of sensor X in region Y. I can see this will be hard to do with the multiple database approach.

So would value any advice e.g.

Do you think it is sensible to use Sql Azure to host potentially 1000's of databases and to use this master database to indirectly point to the real ones?

Will I have a problem with Connections to the databases from the applications- with issues with connection pooling for example?

How will I be able to aggregate data from all these different databases in Sql Azure.

Would be interested in all your comments. Regards, Chris.

Since no one else has answered, I'll share some opinions and do some hand-waving.

As long as you aren't locking common resources, or are locking resources in the same order, you shouldn't have problems with deadlocks.

I'd look at separate tables before separate databases. Each additional database will definitely cost you more but additional tables won't necessarily cost you more. You might need to use more than 1 database becuase of the sheer volume of data you will store or because of the rate at which you need to store your burst traffic. If you can manage it, I think that a table-level granularlity will be more flexible and possibly a good deal cheaper than starting with a database-level granularity.

The problem with putting each device's data into it's own tables is that it makes the reporting hard since all of the table names will be different.

I presume that you have some way of detecting when you get a "failure resend" of data. You don't want to put the same value in a table twice and I i'm sure that the devices can fail (local power failure?) in ways that have nothing to do with whether or not earlier values where properly stored.

WAG: Assuming each "value" is 4 bytes, I calculated about 11.5 MB of collected data per device, per day. (This ignores all kinds of stuff, like device identifiers and timestamps, but I think it is OK as a rough estimate.) So, with "thousands" of sites, we are looking at tens of GB, per day. You don't mention any kind of lifetime on that data. The largest Azure database currently maxes out at 150 GB. You could fill those up pretty quickly.

Getting anything to happen in a web browser in a short period of time is iffy. When you are reading from (possibly multiple) databases with GBs of data, continuously inserting lots of new data into the tables you are reading from and interacting with web servers across the open internet, "real time" is wishful thinking. IMO. "Fast enough" is the usual goal.

If you can't keep all of the data you need in a single report in one SQL Azure database, it's a problem. There are no linked servers or distributed views (at this point). There is no simple way to aggregate accross many Azure databases. You'd have to pull all of the data to a central location and report from there. I'd guess that the aggregated data would be too large to store in a single SQL Azure database, so you'd have to go to on-premise or maybe EC2. A data mart or warehouse with a star-schema structure would be the classic answer there, but that takes significant processing time and that means no "real time". Also, that's potentially a lot more data transfer from Azure to wherever it goes, and that will cost you.

I wouldn't commit to this strategy without a pilot program first. The first thing to do would be to build a single instance (can it handle 400 sensor values a second? (Is that a series of rows, a big denormalized row, an XML document or something else? The format of the incoming data will affect how fast the data can be stored. Can you do bulk inserts, or does it have to be row-by-row?) How about 4,000 sensor values a second? It's possible that an single SQL Azure instance might not be able to store that much that quickly.) and see how it handles insertions at your expected rates and see how the reporting might work. And I'd talk to Microsoft too. Just dealing with the billing for hundreds or thousands of seperate databases might be quirky.

I don't know if this is applicable to you, but have you looked at Microsoft's "Stream Insight" product? It seems to be aimed at situations like yours. Caveat: I've never used it.

The marketting blurb:
Effectively analyze large amounts of event data streaming in from multiple sources. Derive insights from critical information in near real time by using Microsoft StreamInsight. Monitor, analyze, and act on data in motion and make informed decisions almost instantaneously

While doing some quickly googling, I noticed a blog posting which states that StreamInsight available on SQL Azure as a CTP last year. it might be ready for prime time by now.

Good luck, it sounds like an interesting project.


Dropping Global Temporary Tables In Oracle when you try to drop a Global Temporary Table that is in use you get the following exception:

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

This is caused by a session that is using the GTT in a current transaction. Is there a way to query the data dictionary to determine which sessions are blocking the drop?  I know I can get the sessions with in progress transactions with something like this, but I would like to narrow it down further.

select * from v$session
 where session_id in (select session_id from dba_locks where lock_type='Transaction');

The session(s) using the temporary table can be queried as:

select sid from v$lock where type = 'TO'
and id1 = (select object_id from dba_objects where object_name = '&temp_table_name');

Then you can kill these sessions or have them release the locks gracefully.


Stable triangle relationship? I have a table that represents a specific type of action. Let's call it [ACTION].
An action can be performed by a user ([USER]), so I have the relation [USER] 1:N [ACTION].
The user can have children ([CHILD]), so [USER] 1:N [CHILD].
The problem starts by the fact the action must be performed by the user, however it can be performed for the user or one of its children.
If every user always had one or more child, I could simply do [CHILD] 1:N [ACTION] instead of [USER] 1:N [ACTION], because over the child I will find the user who performed the action.
If I simply add [CHILD] 0...1:N [ACTION], so the child becomes an optional field in the action table, it's possible a child is added that does not belong to the user that is added on the action.
Questions

What is this scenario called, typically?

How to overcome this problem or what is the best way I can go?

I would not like to check this in code, a database-only (SQL Server 2012) solution would be perfect.
Example
The action is an investment. The user will always take the investment, but he/she can decide he/she is investing the money for him/her or for one of his/her children. In this scenario children are underage, always. If a child is old enough to do investments of its own, it will register and become a user, instead. So this is why a child cannot be a user. They need to stay two different entities.
So I kinda need two relations that stay stable to each other. I need to know who (user) took the action (investment) and I need to know for who (user or one of his/her children) the action (investment) is taken.
What I'm seeking is a solution where an action (investment) cannot be made for a child that the user has no relationship to. In other words, the father/mother can only invest for him/her or his/her child, not for the child of someone else.

I see 2 options to implement this. The first would be what you already have, with a minor adjustment, to enforce this part:

  What I'm seeking is a solution where an action (investment) cannot be made for a child the user has no relationship to. In other words, the father/mother can only invest for him/her or his/her child, not for the child of someone else.

The adjustment would be to have a composite key on the action references (child) foreign key. The action (child_id) is nullable but when the value is not null, the foreign key constraint ensures that it references a child of the user that takes the action.

Sample code:

-- design 1
CREATE TABLE user
( user_id INT NOT NULL PRIMARY KEY
) ;

CREATE TABLE child
( user_id  INT NOT NULL,
  child_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (user_id)
    REFERENCES user (user_id),
  UNIQUE (user_id, child_id)      -- this is needed for the FK below
) ;

CREATE TABLE action
( action_id INT NOT NULL PRIMARY KEY,
  user_id   INT NOT NULL,
  child_id  INT NULL,                  -- nullable
  FOREIGN KEY (user_id)
    REFERENCES user (user_id),
  FOREIGN KEY (user_id, child_id)
    REFERENCES child (user_id, child_id)
) ;

Another way would be to rename the child table to dependent / investor / beneficiary (pick a more appropriate name) and store there not only the children but the users themselves as well (so all investors / beneficiaries / dependent persons of a user). This way only one, the foreign key from action to investor will be needed and the column will be not nullable:

-- design 2
CREATE TABLE user
( user_id INT NOT NULL PRIMARY KEY
) ;

CREATE TABLE investor
( user_id     INT NOT NULL,
  investor_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (user_id)
    REFERENCES user (user_id),
  UNIQUE (user_id, child_id)
) ;

CREATE TABLE action
( action_id   INT NOT NULL PRIMARY KEY,
  user_id     INT NOT NULL,
  investor_id INT NOT NULL,
  FOREIGN KEY (user_id, investor_id)
    REFERENCES investor (user_id, investor_id)
) ;

What you have as table child in the first design, can be a view:

CREATE VIEW child AS
  SELECT user_id,
         investor_id AS child_id
  FROM investor
  WHERE user_id <> investor_id ;

As a side effect, with design 2, we don't really need the user_id in the action table (unless for other, not mentioned in the question, or performance reasons). We could remove it and get rid of the composite foreign key as well. The user_id can be found with a join to investor:

-- design 2b
CREATE TABLE user
-- unchanged

CREATE TABLE investor
( user_id     INT NOT NULL,
  investor_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (user_id)
    REFERENCES user (user_id)
) ;

CREATE TABLE action
( action_id   INT NOT NULL PRIMARY KEY,
  investor_id INT NOT NULL,
  FOREIGN KEY (investor_id)
    REFERENCES investor (investor_id)
) ;

CREATE VIEW child AS
-- unchanged

Another way that is more complicated but takes good points from both previous designs and captures all the different entities (persons, users, children, actions) is to use the supertype/subtype pattern.

This essentially adds the following into the design (entity person):

A person is either a user or a child.
(superype with subtypes)
A person can have any number of children.
A child has exactly one parent (user).
(1:n relationship)
A person can be a beneficiary of any number of action (investments).
An action is taken for exactly one beneficiary (person).
(1:n relationship)

Code:

-- design 3
CREATE TABLE person
( person_id INT NOT NULL PRIMARY KEY
) ;

CREATE TABLE user
( user_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (user_id)
    REFERENCES person (person_id),
) ;

CREATE TABLE child
( user_id  INT NOT NULL,
  child_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (child_id)
    REFERENCES person (person_id),
  FOREIGN KEY (user_id)
    REFERENCES user (user_id)
) ;

CREATE TABLE action
( action_id   INT NOT NULL PRIMARY KEY,
  beneficiary_id INT NOT NULL,
  FOREIGN KEY (beneficiary_id)
    REFERENCES person (person_id)
) ;

We don't need the user_id in the action table, since the user of an action can be found by looking in the other tables, it will either be the beneficiary himself (if the beneficiary_id is in the user table) or it will be the user (from user_id in the related the child).


“SQL command” vs “Table or view” when importing entire tables When using an OLEDB source editor, is there a performance benefit or difference in using "Sql Command" over "Table or View" when you want the entire table?

This is a variation on this question, in which the asker is talking about scenarios where they only want one or some columns in a table - in which case there is a benefit to using "Sql Command", as a select * from is still executed in the background when using "Table or View" even when columns are unticked.

I've also found this entry on the MSDN blog which states it is more efficient when looking at views due to the use of OpenRowset and a SET ROWCOUNT 1 which results in an inefficient query plan being cached and reused for the actual execution.

But if you're loading in a table and want every column, is there still an advantage to using the "Sql Command" option over "Table or View"?

At one point in the product's past, selecting the table name from the drop down versus writing an explicit SELECT * FROM MyTable resulted in one fewer operation being performed. The table name selection did...something I can't reproduce now. It'd issue a command like though that resulted in a SELECT * but with an outer wrapper around it.

Profiler reports for 2014, it issued the command select * from [SB].[EventLog] A 2005/2008 instance would have indicated it did something like sp_somethingweird [SB].[EventLog]


multiple select query for the same tables with union Using below query i want to find out the top number of students from each catagory(SC,ST,OBC,etc) on plus2percentage basis. eg i want top 3 students from SC catagory, top 2 from ST and Top3 from OBC. But i also want the top 5 from the open catagory, which include all catgory(SC,ST,OBC). but excluding the previous top 8 students from SC,ST,OBC Catagory.

So the final out output i want is like

top 3 students from SC Catagory. top 2 students from ST Catagory. top 3 students from OBC Catagory. top 5 student from(SC/ST/OBC/OPEN)but no one from the previous 8 students.

Total number of top Student= 13

WITH PRIMARY_CHOICE AS ( SELECT RollNo, ApplicantName, Catagory, Plus2Percentage,AppliedCourse, Major1 FROM (
            SELECT RollNo, ApplicantName, Catagory, Plus2Percentage, AppliedCourse,Major1,  row_number() over (partition by Catagory order by Plus2Percentage desc) as rn
            FROM RKMVApplicationFormData) as T
            WHERE rn <= CASE WHEN Catagory='SC'  AND Major1='English' AND AppliedCourse='B.A'  AND Plus2Percentage >= 50  THEN 2
                           WHEN Catagory='ST'  AND Major1='English' AND AppliedCourse='B.A'  AND Plus2Percentage >= 60 THEN 2
                           WHEN Catagory='OBC'  AND Major1='English' AND AppliedCourse='B.A' AND Plus2Percentage >=50 THEn 1
                            ELSE 0
END )
SELECT RollNo, ApplicantName,  Catagory, Plus2Percentage,AppliedCourse,Major1  FROM PRIMARY_CHOICE UNION ALL
SELECT RollNo, ApplicantName,  Catagory, Plus2Percentage,AppliedCourse,Major1
FROM ( SELECT RollNo, ApplicantName, Catagory, Plus2Percentage,AppliedCourse,Major1,  row_number() over (order by Plus2Percentage desc) as rn
FROM RKMVApplicationFormData x
WHERE NOT EXISTS (
select 1 from primary_choice y
where x.RollNo = y.RollNo ) ) AS T2
WHERE rn<=2 AND Major1='English' AND AppliedCourse='B.A' and Plus2Percentage>=65  order by Plus2Percentage desc

SQLFiddle demo

I'm not entirely clear on what you are trying to do, how should the top 4 in each category be chosen for example, randomly?

WITH PRIMARY_CHOICE AS (
  SELECT ApplicantName, Category, Plus2Percentage
  FROM (
    SELECT ApplicantName, Category, Plus2Percentage
         , row_number() over (partition by Category
                              order by Plus2Percentage desc) as rn
    FROM RKMVApplicationFormData
  ) as T
  WHERE rn <= CASE CATEGORY WHEN 'SC' THEN 25
                            WHEN 'ST' THEN 30
                            WHEN 'OBC' THEn 15
                            ELSE 0
              END
)
SELECT ApplicantName, Category, Plus2Percentage
FROM PRIMARY_CHOICE
UNION ALL
SELECT ApplicantName, Category, Plus2Percentage
FROM (
    SELECT ApplicantName, Category, Plus2Percentage
         , row_number() over (order by Plus2Percentage desc) as rn
    FROM RKMVApplicationFormData x
    WHERE NOT EXISTS (
        select 1 from primary_choice y
        where x.ApplicantName = y.ApplicantName
    )
) AS T2
WHERE rn <= 30;


DBCC CHECKDB command creating database file with same name but with space character in extension I am running simple DBCC CHECKDB ('DBNAME') command on my machine every thing works perfect!

But when i execute same command on the live server the command executes without any error, but it creates one extra file int the same location as my mdf file.  For instance, if my mdf file name is "mydatabase.mdf" then this command is creating one more file with name "mydatabase.mdf ".  Additionally, I am not able to delete this file.

Whats wrong here?

Checkdb creates a snapshot in the background. Snapshots are supported by sparse files (they look large in Windows but are typically almost empty).

Could it be that you are looking at this file?


How to show difference between two tables? I have two databases, each containing a 'settings' table with the same structure like this.  (There are hundreds of rows, but this is an example.)

db1.settings                              db2.settings
+-------------------+-----------+        +-------------------+-----------+
| param             | value     |        | param             | value     |
+-------------------+-----------+        +-------------------+-----------+
| MinimalShotLength | 50        |        | MinimalShotLength | 30        |
| OneTemplate       | 0         |        | OneTemplate       | 0         |
| MP4Type           | Universal |        | MP4Type           | Universal |
| Mixdown002        | Default   |        | VideoDevice       | Aja       |
+-------------------+-----------+        +-------------------+-----------+

db1 may contain rows that are missing in db2 and vice-versa.  The rows are not in the same order.

I want to create a new table showing only the differences between the two like this:

+-------------------+-----------+------+
| param             | db1       | db2  |
+-------------------+-----------+------+
| MinimalShotLength | 50        | 30   |
| Mixdown002        | Default   | NULL |
| VideoDevice       | NULL      | Aja  |
+-------------------+-----------+------+

Is this possible in MySQL or do I need to do it in PHP?  The closest I could find was this answer, but that puts each param on a separate row so it's harder to read the output.

MySQL knows nothing about FULL OUTER JOIN, so

SELECT t0.param, t1.value, t2.value
FROM (SELECT param from db1.settings
          UNION
      SELECT param from db2.settings) t0
LEFT JOIN db1.settings t1 ON t0.param = t1.param
LEFT JOIN db2.settings t2 ON t0.param = t2.param
-- remove duplicates if not need in
WHERE NOT (t1.value <=> t2.value)


Add multiples of rows till they satisfy an arbitrary sum then return the rows that do Am not sure if this is possible. Let's say I have an arbitrary figure like 200, I have a table with two columns (both ints). Lets call the columns col_a and col_b.

I need to perform one query such that if col_a * col_b for the first row does not equal to my arbitrary figure (in this case 200), keep whatever value the first row equates to and go to second row, do the same while adding the results to the value we got from the first row, repeat this till the total equates to our arbitrary value.

In an example. Lets use our arbitrary figure of 200 say we have row 1 (col_a*colb) = 20, row 2 (col_a*col_b) = 70, row 3 (col_a*colb) = 110, row 4 (col_a*col_b) = 30.

My query should return row 1, row 2 and row 3. How is this possible in sql? I am using mysql.


SQL Server ; working around collation mismatches Imagine the following scenario: I have two servers which are on two separate private networks.

A linked server has been created, and there is a table in server A which has an updated version of the same table on server B. The updates are not extensive, essentially just a reassignment of some Boolean values.

The connection between the two servers is reliable but relatively slow, and there is a collation mismatch between the two tables.

Is there a recommended or standard way of updating information between two servers with a collation mismatch?

You use the collate operator in the join.  Such as:

select <column list>
from dbo.firsttable f
join linkedserver.database.dbo.secondtable d
   on f.name collate database_default = d.name collate database_default

Of course, you can also specify a specific collation, such as: SQL_Latin1_General_CP1_CI_AI


Keep getting "Connected to an idle instance" in 18c when trying to connect to database I installed  Oracle Database 18c yesterday and then installed Oracle Application Express v20.1. Everything was perfect and I was able to work on Apex too.

Then I shutdown the computer and today I cannot connect to the database. Yesterday when I connected to the database I got Connected, but now I keep getting Connected to an idle instance. When I issued the command startup I got the following errors:

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3256M
ORA-01078: failure in processing system parameters

Environment : Windows 10 Pro x64, 8GB RAM, AMD R5 1600 Processor, Installed on Samsung PM961 256GB NVMe

If the environemnt variables ORACLE_SID, ORACLE_HOME and ORACLE_BASE are set, then you should be able to connect to an IDLE INSTANCE with SQLPlus.

C:\> sqlplus sys@<SID> as sysdba

When connected you then create a editable PFILE copy of the instances current SPFILE (run time configuration). Before that be sure to copy the current editable PFILE to some safe locaiton. If you don't have an SPFILE then skip to step 5.

  Notice:
  Please replace the <SID> placeholder I used in my commands with the SID of your actual Oracle instance, and of course the paths to the local SPFILE and PFILE should reflect your actual paths of your installation.

Configuring Oracle Memory Settings

Save the initial PFILE that is created after a setup

C:\> COPY C:\Oracle\admin\<SID>\pfile\init<SID>.ORA C:\Oracle\admin\<SID>\pfile\init<SID>_backup.ORA

Save any current PFILE that may be laying around

C:\> COPY C:\Oracle\admin\<SID>\pfile\pfile_<SID>.ORA C:\Oracle\admin\<SID>\pfile\pfile_<SID>_backup.ORA

Log into idle instance

C:\> sqlplus sys@<SID> as sysdba

Create a PFILE (modifiable configuration file) from current SPFILE (run-time configuration file)

SQL> CREATE PFILE = 'C:\Oracle\admin\<SID>\pfile\pfile_<SID>.ORA' from SPFILE = 'C:\Oracle\admin\<SID>\pfile\spfile_<SID>.ORA';

Shutdown the idle instance

SQL> SHUTDOWN IMMEDIATE

In an editor open your current PFILE C:\Oracle\admin\\pfile\pfile_.ORA or the INIT_.ORA file and locate these parameters:

*.memory_max_target=0
*.memory_target=0

These parameters are set when using Automatic Memory Management. If these are both set to 0 (like above) then you are using Automatic Shared Memory Management. You will have to set the following parameters instead:

*.sga_max_size=0
*.sga_target=0

Either of these value pairs should be set. If not, then modify the memory_max_target and memory_target parameters to something like 4G. Save the configuration file and...
Start the Oracle instance using the freshly created PFILE.

C:\> sqlplus sys@<SID> as sysdba
SQL> STARTUP PFILE='C:\Oracle\admin\<SID>\pfile\pfile_<SID>.ORA';

When instance is running, create the run-time configuration file.

SQL> CREATE SPFILE='C:\Oracle\admin\<SID>\pfile\spfile_<SID>.ORA' FROM PFILE='C:\Oracle\admin\<SID>\pfile\pfile_<SID>.ORA';

When the SPFILE has been created shutdown the instance.

SQL> SHUTDOWN IMMEDIATE

Restart the instance which should use the freshly create SPFILE (run-time configuration).

C:\> sqlplus sys@<SID> as sysdba
SQL> STARTUP

And you should be on your way.


PostgreSQL - query first on indexed column? I have a 2 tables - listings and users.

CREATE TABLE users (
  id text PRIMARY KEY,
  internal_id serial,
)

CREATE TABLE listings (
  id bigserial PRIMARY KEY,
  created_at timestamptz default current_timestamp,
  posted_by text REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE,
)

When I want to select a listing, I want to first query by listing.id because there is a b index on it and it will give the fastest look up. Then I want to filter out by user_id. In addition, join the users table so I can have both the listing record and the user.internal_id from posted_by.

This query works:

SELECT
    *, users.internal_id
FROM
    listings
    JOIN users ON listings.posted_by = users.id
WHERE
    listings.id = 2
    and posted_by = '1009280753550922';

but I do not think it is querying by listing.id, which is indexed, for best performance:

dev_dolphin_db=# explain SELECT *, users.internal_id FROM listings JOIN users ON listings.posted_by = users.id WHERE listings.id = 2 and posted_by = '1009280753550922';
Nested Loop  (cost=0.14..10.46 rows=1 width=1560)
   ->  Seq Scan on listings  (cost=0.00..2.29 rows=1 width=840)
         Filter: ((posted_by = '1009280753550922'::text) AND (id = 2))
   ->  Index Scan using users_pkey on users  (cost=0.14..8.16 rows=1 width=720)
         Index Cond: (id = '1009280753550922'::text)

How could I build a query that would explicitly query on the indexed listings.id first?

If you look carefully at your query plan, you will find that the Seq Scan expects to find just one row (rows=1 width=840) in the listings table.

This is what happens if you have a situation such as this:

 CREATE TABLE users
 (
   id text PRIMARY KEY,
   internal_id serial,
   something_else char(800)
 ) ;

 CREATE TABLE listings
 (
   id bigserial PRIMARY KEY,
   created_at timestamptz default current_timestamp,
   posted_by text REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE
 ) ;

We add a few users, but just ONE listing:

 INSERT INTO users (id) VALUES('1009280753550922') ;
 INSERT INTO users (id) SELECT generate_series(1, 1000)::text ;

 INSERT INTO listings (posted_by) VALUES('1009280753550922') ;

Have the tables analyzed, so that the query planner has got good statistics:

 ANALYZE users;
 ANALYZE listings;

And make it explain the SELECT:

 EXPLAIN
 SELECT
     *, users.internal_id
 FROM
     listings
     JOIN users ON listings.posted_by = users.id
 WHERE
     listings.id = 2
     and posted_by = '1009280753550922';

 | QUERY PLAN                                                                      |
 | :------------------------------------------------------------------------------ |
 | Nested Loop  (cost=0.28..9.32 rows=1 width=3248)                                |
 |   ->  Seq Scan on listings  (cost=0.00..1.01 rows=1 width=33)                   |
 |         Filter: ((posted_by = '1009280753550922'::text) AND (id = 2))           |
 |   ->  Index Scan using users_pkey on users  (cost=0.28..8.29 rows=1 width=3211) |
 |         Index Cond: (id = '1009280753550922'::text)                             |

dbfiddle here

The reason why PostgreSQL decides to perform a Sequential Scan of the listings table is because there is just one row. In that case, reading the whole table takes less time than using an index.

If you just add more data to the listings table, PostgreSQL changes plans, because then, reading the whole table is no longer the cheapest alternative:

 INSERT INTO listings (posted_by) SELECT generate_series(1, 1000)::text ;
 INSERT INTO listings (posted_by) SELECT generate_series(1, 1000)::text ;
 INSERT INTO listings (posted_by) SELECT generate_series(1, 1000)::text ;

 EXPLAIN
 SELECT
     *, users.internal_id
 FROM
     listings
     JOIN users ON listings.posted_by = users.id
 WHERE
     listings.id = 2
     and posted_by = '1009280753550922';

 | QUERY PLAN                                                                          |
 | :---------------------------------------------------------------------------------- |
 | Nested Loop  (cost=0.56..16.60 rows=1 width=3234)                                   |
 |   ->  Index Scan using listings_pkey on listings  (cost=0.28..8.30 rows=1 width=19) |
 |         Index Cond: (id = 2)                                                        |
 |         Filter: (posted_by = '1009280753550922'::text)                              |
 |   ->  Index Scan using users_pkey on users  (cost=0.28..8.29 rows=1 width=3211)     |
 |         Index Cond: (id = '1009280753550922'::text)                                 |

dbfiddle here

PostgreSQL (and most other databases) has a Query Planner that chooses an execution plan based on a cost-estimate of all available alternatives. The cost-estimate is based on the amount and statistical distribution of data within the different columns of the different tables, and takes into consideration the availability of indexes, but will choose to use them only if it is the cheapest alternative (or you force it to use them via settings).

With a query as simple as the one in this example, just joining two tables using an equality JOIN condition, you shouldn't need to outsmart PostgreSQL query planner. Let the planner plan. It will nearly always choose the best plan.

You may need to play some tricks when JOINs are much more complicated: many more tables, conditions which aren't necessarily just equalities, function calls, correlations between tables that the planner doesn't take into account, etc. ... and the planner ends up making bad cost-estimates, and the estimated cheapest plan is not really the cheapest.


Whay does the SQL Server Service account has the ""Impersonate a client after authentication" Right? Our database is running on SQL Server 2008 hosted on a Windows 2008 Server. We have found that the security policy “impersonate a Client after authentication” is enabled for the SQL Server service account. Will removing “impersonate a Client after authentication” cause any issues? is this URA required for SQL to function correctly. The database hosted on this instance is a client-server application.

The documented Windows Privileges and Rights for the database engine service account are:

  Log on as a service (SeServiceLogonRight)
  Replace a process-level token (SeAssignPrimaryTokenPrivilege)
  Bypass traverse checking (SeChangeNotifyPrivilege)
  Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
  Permission to start SQL Writer
  Permission to read the Event Log service
  Permission to read the Remote Procedure Call service

The SeImpersonatePrivilege is only documented as required for the SSIS service.

That being said, I will say that the SeImpersonatePrivilege privilege is required for the database engine. Windows Authenticated client connections impersonate the client when accessing OS resources like SSPI linked servers and network shares (think db attach, backups etc).


How do I move tables and indexes owned by user to new tablespaces in Oracle 19C? I have a user named SDE and the tablespace for this users tables are in SDE_TBS, I would like to move all of these tables to tablespace SDEBUS_DT.  For indexes I would like to move to SDEBUS_IX.  How would I accomplish this? I have reference this thread, but it does not address my question completely.

You can also use simple ALTER TABLE/INDEX command
for aTab in (select table_name from dba_tables where owner = 'SDE') loop
   execute immediate 'ALTER TABLE SDE.' ||aTab.table_name|| ' MOVE TABLESPACE SDEBUS_DT';
end loop;

for aInd in (select index_name from dba_indexes where owner = 'SDE') loop
   execute immediate 'ALTER INDEX SDE.' ||aInd.index_name|| ' REBUILD TABLESPACE SDEBUS_IX';
end loop;

But DBMS_REDEFINITION.REDEF_TABLE is more versatile than ALTER TABLE/INDEX, e.g. when user has partitioned tables.


How to monitor SQL Server data and log file growth? I'm trying to find a way to monitor data and log file size from SQL Server databases (within an SQL Server instance). I would not want to use third party tools, but simply pure SQL or PowerShell. I know that one can query this data from sys.database_files or use DBCC SQLPERF(logspace) to get data from log file usage. Is there any way to automate this monitoring and get reports from this data? Any ideas and code samples are helpful!

If you want to use PowerShell you can easily create task scheduler jobs in Windows to run the PowerShell scripts which would dump the output into a table or log file.

If you want to use SQL you can easily create SQL Server Agent jobs to pull the data from the DMVs or DBCC and dump them in a table.

It is difficult to understand exactly what you would want any reports against the stored data to look like, or which part is challenging.


How do I disable MySQL on Linux from starting on boot or statup? After adding MySQL to Ubuntu, it automatically starts up on server boot. I would like it to not start up with the system.

How can I disable it and manually start it?

MariaDB use systemd as does MySQL. To disable MySQL or MariaDB, from starting on bootup, run

systemctl list-unit-files '*mariadb*' '*mysql*'

If you see mysql.service, try disabling that one first.

sudo systemctl disable mysql

Now you can start and stop MySQL with

sudo systemctl stop mysql.service
sudo systemctl start mysql.service


Slow window function query with big table I'm doing some performance testing on a new DB design on PostgreSQL 9.4rc1 and I'm seeing some pretty slow queries using window functions.  Here is my table setup:

CREATE TABLE player_stat (
  player_id    VARCHAR(200) NOT NULL,
  stat_id      BIGINT NOT NULL,
  value        BIGINT NOT NULL DEFAULT 0,
  last_updated TIMESTAMP WITH TIME ZONE NOT NULL,
  last_active  TIMESTAMP WITH TIME ZONE DEFAULT NULL,

  CONSTRAINT player_stat_pk PRIMARY KEY (player_id, stat_id),
  CONSTRAINT player_stat_fk1 FOREIGN KEY(stat_id) REFERENCES stat (id)
);
CREATE INDEX player_stat_stat_value_player_desc
  ON player_stat (stat_id, value DESC, player_id ASC);

I've inserted 30 million rows into this table split among 3 stats:

INSERT INTO player_stat (player_id, stat_id, value, last_updated) SELECT x.id, 1, x.v, now() FROM (SELECT generate_series(1,10000000) as id, trunc(random() * (1900-1200) + 1200) as v) AS x;
INSERT INTO player_stat (player_id, stat_id, value, last_updated) SELECT x.id, 2, x.v, now() FROM (SELECT generate_series(1,10000000) as id, trunc(random() * (1900-1200) + 1200) as v) AS x;
INSERT INTO player_stat (player_id, stat_id, value, last_updated) SELECT x.id, 3, x.v, now() FROM (SELECT generate_series(1,10000000) as id, trunc(random() * (1900-1200) + 1200) as v) AS x;

Then I try to rank the players for a given stat (EDIT):

SELECT * FROM
( SELECT player_id
       , rank() OVER (ORDER BY value DESC, player_id ASC)  as rank
  FROM player_stat
  WHERE stat_id = 1
) as t
WHERE rank <= 20
ORDER BY rank ASC;

This query takes about 5.5 seconds to return.  Running Explain on it returns the following:

"Sort  (cost=1167612.28..1176082.26 rows=3387993 width=15) (actual time=9726.132..9726.135 rows=20 loops=1)"
"  Sort Key: t.rank"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Subquery Scan on t  (cost=0.56..684349.57 rows=3387993 width=15) (actual time=0.080..9726.116 rows=20 loops=1)"
"        Filter: (t.rank <= 20)"
"        Rows Removed by Filter: 9999980"
"        ->  WindowAgg  (cost=0.56..557299.83 rows=10163979 width=15) (actual time=0.077..8351.124 rows=10000000 loops=1)"
"              ->  Index Only Scan using player_stat_stat_value_player_desc on player_stat  (cost=0.56..379430.20 rows=10163979 width=15) (actual time=0.054..2319.007 rows=10000000 loops=1)"
"                    Index Cond: (stat_id = 1)"
"                    Heap Fetches: 0"
"Planning time: 0.187 ms"
"Execution time: 9726.172 ms"

Is there any way can I speed this up?  The time it takes seems to be growing linearly with the number of players in the table.

Is there any way I can speed this up?

Yes. Don't use a varchar column for an integer number. Use integer or bigint if you burn that many IDs - much smaller in table and index and faster to process. Since you are ranking 10 million rows in your test, this is going to make a substantial difference.

player_id    VARCHAR(200) NOT NULL,
player_id    int NOT NULL,

Or a uuid if you must (I doubt that):

Postgres uuid: Use as primary key, or in addition to SERIAL -for disconnected app-

Your query ranks 10 million rows. This is going to take some time, even when read from the index directly and no sort step.

Side note: If you bulk-insert rows first and add index and PK constraint (and FK constraint) after, that's going to be much faster, plus you get perfect indexes without bloat without running REINDEX or VACUUM FULL.
Do make sure ANALYZE has been run on the table before testing performance, though.

What you didn't ask

.. but, going out on a limb here, what are probably looking for.

The EXPLAIN output reveals that you filter the top 20 rows: (t.rank <= 20). Your presented query does not show that. The query actually matching your EXPLAIN output would be:

SELECT * FROM (
   SELECT player_id
        , rank() OVER (ORDER BY value DESC, player_id ASC) AS rank
   FROM   player_stat
   WHERE  stat_id = 1
   ) t
WHERE t.rank <= 20;

Which can be improved dramatically:

SELECT row_number() OVER (ORDER BY value DESC, player_id ASC) AS rank
     , player_id
FROM   player_stat
WHERE  stat_id = 1
ORDER  BY value DESC, player_id
LIMIT  20;

Explanation

The important part for performance is the LIMIT clause in combination with ORDER BY matching the index: now the query reads exactly 20 rows from the top to the index, where it had to read 10000000 in your original version. We only use player_id and value, so we can still have an index-only scan. The rest is peanuts.
That's all due to the sequence of events in a SELECT query: window functions are applied before LIMIT. Only if the sort order agrees, we don't have to consider the rest of the applicable 10000000 rows.
We can use LIMIT 20 because the top 20 ranks are guaranteed to span no more than 20 rows. The PK on (player_id, stat_id) guarantees unique player_id per stat_id and since that is included in the ORDER BY, each rank is only assigned once - which also means we can use the slightly cheaper row_number() instead.


High memory usage in Masters with MariaDB and Vitess I'm running a Kubernetes Cluster with MariaDB 10.3.20 and Vitess 6. Everything is running fine, but the memory usage in the Master nodes is always around 90%. I don't have OOM right now, but I was keen to understand this high memory usage.
Currently, each node (master and replica) has the same configuration of 10GB RAM and 10 CPU cores. The MariaDB configurations, for now, are the default ones (innodb_buffer_pool_size has 128MB, I will try to update to 70% of the node memory), except for the number of max_connections which is set to 15758.
The data inside database is bellow 1GB and 5 tables. SELECT peak is around 700 queries per seconds, INSERT peak is around 200 QPS and UPDATE 100 QPS.
Masters are receiving only writes (INSERT, UPDATE, DELETE) and replicas are receiving only reads (SELECT).
Below are two charts for memory usage (the top ones are masters, the ones at the bottom are replicas).
The master's memory does an interesting "pattern" every week, not sure why.
I've tried to understand this behavior, reading MariaDB Memory Allocation, but couldn't anything that could explain this.

The chart below has a span of 15 days.

max_connections which is set to 15758.

Far too high.  Try 200.
Do those interesting mem spikes correspond to big queries?  Or garbage collection in Vitess?  "Every week" sounds like a backup by the host?
Do increase the buffer_pool to 2G, but not until you get the memory under control.
That the heck are those 12 things in the first image?


SQL Join to a Same Combination of Tables I want to join a first table to same combination of joins: X,Y,Z. The first table can be different table types, but they all join to same set of tables. Table A may have 8 columns of different data types, Table B may have 15 columns of different table types. How do I code for this without repetition? The tables join to other tables later. How would I save this combinations of joins in a view or table function, etc, if the first table is always different?

We have 100+ queries like this, and eventually may want to add more tables to saved X-Y-Z combination, maybe 7 in the future. S-T-U-V-X-Y-Z. The question becomes more of code versatility. Would like to change in 1 spot, rather than 100 different queries.

Note: Table X,Y,Z have different columns and data types, except on ProductId and ProductType.

Select a.*, x.*,y.*,z.*
from TableA a
left join DimShelf x
  on b.ProductId = x.ProductId
  and b.ProductTypeId = x.ProductTypeId -- 1
left join DimDesk y
  on b.ProductId = y.ProductId
  and b.ProductTypeId = y.ProductTypeId -- 2
left join DimCouch
  on b.ProductId = z.ProductId
  and b.ProductTypeId = z.ProductTypeId -- 3
left join other items of variation with where clauses at end...

Select b.*, x.*,y.*,z.*
from Tableb b
left join DimShelf x
  on b.ProductId = x.ProductId
  and b.ProductTypeId = x.ProductTypeId -- 1
left join DimDesk y
  on b.ProductId = y.ProductId
  and b.ProductTypeId = y.ProductTypeId -- 2
left join DimCouch
  on b.ProductId = z.ProductId
  and b.ProductTypeId = z.ProductTypeId -- 3
left join other items of variation with where clauses at end...

create table dbo.DimChair
(
    DimChairId int primary key identity(1,1),
    ProductTypeid int,
    ProductId int,
    Color varchar(55),
    LegNumber int
)

create table dbo.DimShelf
(
    DimShelfId int primary key identity(1,1),
    ProductTypeid int,
    ProductId int,
    Length float,
    Height float,
    NumberofShelves varchar(55)
)

create table dbo.DimCouch
(
    DimCouchId int primary key identity(1,1),
    ProductTypeid int,
    ProductId int,
    FabricType varchar(255),
)

As suggested by @Akina, it might be better to just write a query that joins the desired table to a view that joins the common tables. In this manner, we might be able to get good optimization and inlining of the view.

We need to note that for all 3 joins, we use the same value from the table joining to each table. Therefore we should be able to create a view that has the entire set of value {ProductID, ProductType} available to join.

I will assume that not all valid values necessarily exist in each table, so we can't just left join the other tables to the X table; it might not even have that value that we ultimately are looking for. Thus, we need a set of all possible values in all tables first.

CREATE VIEW AllThings AS
WITH AllPossibleValues AS (
  SELECT ProductID, ProductType
  FROM DimShelf
  UNION
  SELECT ProductID, ProductType
  FROM DimDesk
  UNION
  SELECT ProductID, ProductType
  FROM DimCouch
)
SELECT
  b.ProductID,
  b.ProductType,
  x.... -- All columns you need from x
  y.... -- All columns you need from y
  x.... -- All columns you need from z
FROM AllPossibleValues AS b
LEFT JOIN DimShelf x
  ON b.ProductId = x.ProductId
  AND b.ProductTypeId = x.ProductTypeId -- 1
LEFT JOIN DimDesk y
  ON b.ProductId = y.ProductId
  AND b.ProductTypeId = y.ProductTypeId -- 2
LEFT JOIN DimCouch
  on b.ProductId = z.ProductId
  and b.ProductTypeId = z.ProductTypeId -- 3
;

Note that it is not recommended to use SELECT * with any of the table in a view definition - it implies that it'll select all columns but in fact it only selects all columns that existed at the time of view's creation/modification. For that reason, it's best to enumerate the columns explicitly. This does make for lengthy query definition but as @Akina correctly points out, that will not necessarily hurt the optimization.

Furthermore, if you find the performance to be problematic, you might need to push down the predicate to the CTE, which necessitates the use of table-valued function in order to pass a parameter. Something similar to this:

CREATE FUNCTION AllThingsParameterized (
  @ProductID int,
  @ProductType varchar(50)
) AS RETURNS TABLE
RETURN
WITH AllPossibleValues AS (
  SELECT ProductId, ProductType
  FROM DimCouch AS x
  WHERE ProductID = @ProductID
    AND ProductType = @ProductType
  UNION
  ....
) ...

This variant, however, is good only for one value of the ProductID & ProductType; if you need a set of ProductID & ProductType, then you may need to consider using a table-valued parameter, which may further complicate the setup.

Do note that if the view is used in another view, you may negate the benefit of inlining as the final statement may become too complex for the optimizer to effectively optimize. If that is your typical scenario, you might be actually better off generating a temporary table result from a stored procedure and joining the table to that instead.


To me it would seem like it should be a bug. Yet, note the MySQL Documentation:

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

Here is an output of every count from -5 to 5
mysql> SET @foo = '|A|B|';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT x,SUBSTRING_INDEX(@foo,'|',x)
    -> FROM
    -> (
    ->     SELECT 0 x
    ->     UNION SELECT  1 UNION SELECT  2
    ->     UNION SELECT  3 UNION SELECT  4 UNION SELECT 5
    ->     UNION SELECT -1 UNION SELECT -2
    ->     UNION SELECT -3 UNION SELECT -4 UNION SELECT -5
    -> ) A ORDER BY x DESC;
+----+-----------------------------+
| x  | SUBSTRING_INDEX(@foo,'|',x) |
+----+-----------------------------+
|  5 | |A|B|                       |
|  4 | |A|B|                       |
|  3 | |A|B                        |
|  2 | |A                          |
|  1 |                             |
|  0 |                             |
| -1 |                             |
| -2 | B|                          |
| -3 | A|B|                        |
| -4 | |A|B|                       |
| -5 | |A|B|                       |
+----+-----------------------------+
11 rows in set (0.00 sec)

mysql>

Notice in both directions, since @foo only has 3 pipe characters, any position whose absolute value exceeds 3 should produce an error. Since the Documentation says final delimiter, it does not seem to enforcement counts outside the range of delimiters (at least in the Windows version).
I tried it out on my laptop running MySQL in Windows 8.1
mysql> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t as select '|A|B|' as foo;
Query OK, 1 row affected (0.64 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t;
Query OK, 0 rows affected (0.13 sec)

mysql> create table t(foo varchar(5) not null);
Query OK, 0 rows affected (0.35 sec)

mysql> insert into t(foo) values('|A|B|');
Query OK, 1 row affected (0.03 sec)

mysql> select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)

What version of MySQL am I running ?
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.15                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.15                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql>

My Windows version works ? I say you definitely have a bug in the Ubuntu version.
I reran it again, this time running SHOW CREATE TABLE t\G
mysql> drop table if exists t;
Query OK, 0 rows affected (0.11 sec)

mysql> create table t as select '|A|B|' as foo;
Query OK, 1 row affected (0.36 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `foo` varchar(5) CHARACTER SET cp850 NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t;
Query OK, 0 rows affected (0.09 sec)

mysql> create table t(foo varchar(5) not null);
Query OK, 0 rows affected (0.31 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `foo` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into t(foo) values('|A|B|');
Query OK, 1 row affected (0.03 sec)

mysql> select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)

mysql>

The bug may involve the character set since the last line of the SUBSTRING_INDEX Documentation says This function is multibyte safe. Perhaps SUBSTRING_INDEX may be OK after all, but that's me messing around in Windows.
Run the script
SET @foo = '|A|B|';
SELECT x,SUBSTRING_INDEX(@foo,'|',x)
FROM
(
    SELECT 0 x
    UNION SELECT  1 UNION SELECT  2
    UNION SELECT  3 UNION SELECT  4 UNION SELECT 5
    UNION SELECT -1 UNION SELECT -2
    UNION SELECT -3 UNION SELECT -4 UNION SELECT -5
) A ORDER BY x DESC;

and see if you get the same output that I did.
GIVE IT A TRY !!!


PostgreSQL conditional join and value distribution with materialized view So I essentially have two tables in a database. One which is the base table of all of our media data, and another that has potential override data for spend. Not everything in the display table will need to get overridden, but some rows will. Also, the only grouping is done at the date AND pid level, and does not include creative. So if there are date AND pid combinations that have more than one creative, the spend will need to get distributed based on the proportion of impressions for that date AND pid combination. I would like to create a view that does this for me, but I'm not quite sure where to begin here or if it's even possible. I have a python script that can do the work, but I wanted to see if it was possible with just SQL. SQL Fiddle with basic schema and data

display table:

date    pid   creative  impressions  spend
1/1/16  1234  a         10           $100
1/1/16  1234  b         20           $200
1/1/16  1235  a         20           $300
1/1/16  1236  a         20           $300
1/2/16  1234  b         15           $150
1/2/16  1235  a         20           $200
1/2/16  1236  a         10           $150
1/2/16  1236  b         20           $200
1/2/16  1237  a         10           $100
1/2/16  1237  b         15           $150

spend table:

date    pid     spend
1/1/16  1234    $400
1/1/16  1235    $600
1/2/16  1235    $1,200
1/2/16  1236    $500
1/2/16  1237    $1,000

expected output:

date    pid     creative    impressions spend
1/1/16  1234    a           10          $133.33
1/1/16  1234    b           20          $266.67
1/1/16  1235    a           20          $600
1/1/16  1236    a           20          $300
1/2/16  1234    b           15          $150
1/2/16  1235    a           20          $1,200
1/2/16  1236    a           10          $166.67
1/2/16  1236    b           20          $333.33
1/2/16  1237    a           10          $400
1/2/16  1237    b           15          $600

Answer

SELECT
  D.date,
  D.pid,
  D.creative,
  D.impressions,
  COALESCE(S.spend * D.impressions / SUM(D.impressions)
  OVER (PARTITION BY D.date, D.pid), D.spend) AS "spend"
FROM display D
LEFT JOIN spend S
  ON D.date = S.date
  AND D.pid = S.pid

So I finally got around to having a good chunk of real world data to play with and this is giving me issues. This:

SELECT D.rep_date, D.placement_id, D.creative
  , COALESCE(S.media_cost * D.impressions /
       SUM(D.impressions) OVER (PARTITION BY D.rep_date, D.placement_id)
    , D.media_cost
    ) AS  "spend"
FROM dc3.display D
LEFT JOIN dc3.display_spend S ON D.rep_date = S.rep_date AND D.placement_id = S.placement_id

returns exactly half of what I would expect for spend. This:

SELECT D.rep_date, D.placement_id, D.creative
  , SUM(COALESCE(S.media_cost * D.impressions /
      (SELECT SUM(D2.impressions)
       FROM dc3.display D2
       WHERE D.rep_date = D2.rep_date AND D.placement_id = D2.placement_id)
    , D.media_cost
    )) AS  "spend"
FROM dc3.display D
LEFT JOIN dc3.display_spend S ON D.rep_date = S.rep_date AND D.placement_id = S.placement_id
GROUP BY 1, 2, 3

returns exactly what I would expect, but takes a long time to run. Any thoughts on what the issue could be?

A Window Function can be used (SUM): Display.impressions * Spend.spend / SUM(...) OVER(...)
Query:
SELECT D.date, D.pid, D.creative
    , COALESCE(
        D.impressions * S.spend
        / SUM(D.impressions) OVER(PARTITION BY D.date, D.pid)
      , D.spend
    )
FROM display D
LEFT JOIN spend S ON D.date = S.date AND D.pid = S.pid;

SQL Fiddle
Output:
date                        pid     creative    spend
January, 01 2016 00:00:00   1234    a           133.33
January, 01 2016 00:00:00   1234    b           266.66
January, 01 2016 00:00:00   1235    a           600
January, 01 2016 00:00:00   1236    a           300
January, 02 2016 00:00:00   1234    b           150
January, 02 2016 00:00:00   1235    a           1200
January, 02 2016 00:00:00   1236    a           166.66
January, 02 2016 00:00:00   1236    b           333.33
January, 02 2016 00:00:00   1237    a           400
January, 02 2016 00:00:00   1237    b           600


xp_delete_file - error message when no files for deleting I have a job that deletes transaction log backups older than 3 days.

if for some reason there are no files to delete, it comes out with the following error message:

Msg 22049, Sev 16, State 1: xp_delete_file() returned error 2, 'The system cannot find the file specified.' [SQLSTATE 42000]
Job 'Log Shipping - Delete tran backups older than 3 days' : Step 1, 'delete the trn files older than 3 days' : Began Executing 2019-01-18 23:00:02

Msg 22049, Sev 16, State 1: xp_delete_file() returned error 2, 'The system cannot find the file specified.' [SQLSTATE 42000]

this is the code of the job:

-This will set the date time stamp to pass onto the Stored Proc for 72 hours from the current date when it runs
--The stored proc will recursively delete all TRN files from the parent level specified all the way down
declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -72, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

--5th argument below "0 - don't delete recursively (default)" and  "1 - delete files in sub directories"
EXECUTE master.dbo.xp_delete_file 0,N'\\homer\S_SYSTEMS\FullBackups\MY_SERVER\_log1\',N'trn', @DeleteDate,1

--5th argument below "0 - don't delete recursively (default)" and  "1 - delete files in sub directories"
EXECUTE master.dbo.xp_delete_file 0,N'\\homer\S_SYSTEMS\FullBackups\MY_SERVER\_log12',N'trn', @DeleteDate,1

Here is the job:

how can we change this?
if there are no files, that's fine, no need to fail the job.

I managed to get the same error when executing the query on a random chosen folder (that was empty).

Message
Executed as user: DOMAIN\ServiceAccount. xp_delete_file() returned error 2, 'The system cannot find the file specified.' [SQLSTATE 42000] (Error 22049).  The step failed.

At first, I wanted to see if we could use aTRY CATCH Block to 'muffle' the sound of the error screams our SQL Server is throwing at us.

It still fails with the same error message, so we cannot use that to get a 'false positive'.

I do think that the error is happening because either

The folder does not exist
The service account does not have permissions on the folder

When executing on a folder where i knew that my service account did not have permissions on, i got the same error.
After changing the folder, and adding my service account as 'full control' on the folder, it worked, even if it was empty.

Permissions on the folder for my service account

The folder is empty

Checking the job T-SQL

Looks good

Message

  Executed as user: DOMAIN\ServiceAccount. The step succeeded.

This was tested on SQL Server 2014 (SP2-CU12)

There could be better alternatives for the proc, or other
 workarounds.