How to set up signed SSL Root and Leaf certificates on a PostgreSQL 11 database on Ubuntu and Windows 10 Power BI plus PostgreSQL? I have Power BI and PostgreSQL 11 installed on Windows 10 and my primary PostgreSQL 11 database on Ubuntu 18.10 Linux. I edited the postgresql.conf and pg_hba.conf files correctly to the IP Adresses my Windows 10 computer uses with PostgreSQL 11 and Power BI to connect to my PostgreSQL 11 Ubuntu database. However, I got errors in Power BI while I could manually connect via command line.
After troubleshooting with Microsoft Support, I was told:

This indicates that the Postgresql server does not have its certificates configured and therefore cant connect to PowerBI. To allow the client to verify the identity of the server, place a root certificate on the client and a leaf certificate signed by the root certificate on the server. To allow the server to verify the identity of the client, place a root certificate on the server and a leaf certificate signed by the root certificate on the client. One or more intermediate certificates (usually stored with the leaf certificate) may be used.

However, I am confused by the tutorials at

https://www.postgresql.org/docs/current/libpq-ssl.html and
https://www.gab.lc/articles/postgresql_with_ssl/.

Error messages
The error messages I received were:

"ConnectionId":"bcfc620b-0fda-4db9-bda6-d9ac4aabcc4c","Exception":"Exception:\r\nExceptionType: System.Security.Authentication.AuthenticationException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: The remote certificate is invalid according to the validation procedure.\r\nStackTrace:\n   at System.Net.Security.SslState.StartSendAuthResetSignal(ProtocolToken message, AsyncProtocolRequest asyncRequest, Exception exception)\r\n   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)\r\n   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)\r\n   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)\r\n   at System.Net.Security.SslState.CheckCompletionBef

and

Details: "An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.'"

and

Details: "Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remost host.)"

Questions
I can't tell what steps need to be done on my Ubuntu PC and what steps need to be done on my Windows PC. I don't know if special steps are needed to configure Power BI with the certificates. How do I set up my bidirectional certificates?
Can someone offer a very detailed tutorial, tell me 'how' to read a tutorial, or explain the steps in an answer please?

I believe that PowerBI uses npgsql to connect, which defaults to the behavior that libpq/psql gives you under 'PGSSLMODE=verify-full'.  Can you connect to your server from Windows psql if you use 'PGSSLMODE=verify-full'?

The tutorial you linked assumes that you use the same machine for the certificate authority and for the database server, which will probably work for you.  But it also assumes the client machine is debian/ubuntu, not Windows.  So you will have do everything before "Configure the client" on your ubuntu machine.  But you probably already did that, based on the error message you are getting.

And you will have to do everything after "Configure the client" on your Windows machine, except you have to do it using Windows mechanisms, not linux mechanisms.  How to do that is a question of how your infrastructure is set up--you need some way to copy the file ca-cert.pem from the cert authority to the Windows machine, but that mechanism could be scp (if you have that installed on Windows, perhaps via cygwin) or a CIFS mount, or just copy the text of the .pem files out of putty and paste it into notepad, or use a thumbdrive.

Note that the tutorial is for using certs in both directions--for the client to verify the identity of the server, and for the server to verify the client too.  But it is rare for people to actually use client certs--they usually use server certs only.  If you are not using client certs, then you only need to install one cert on the client, which is the "ca-cert.pem" file (which should get named "root.crt" when installed on the client).  The other two are only needed for client certs.  And on Windows, the path it needs to be installed in is %APPDATA%\postgresql\root.crt, rather than ~/.postgresql/root.crt.


Increase all primary key values by 1 How do I increase all of my EmployeeID values by one if it is the primary key?

I assume that this question is about how to update a bunch of rows to a value on higher without stepping on each other. If you update id=1 to id=2 in the first row while the second row (with id=2) is still there you will get a primary key violation because now you have two rows with id=2.

To prevent that type of collision you just need to start at the largest value and increase it by one. that opens up a gap for the second largest value to move into which in turn opens a gap for the third largest value to move into, and so on.

In SQL Server however, you need not worry, as the engine is taking care of that automatically:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.Employee(Id INT PRIMARY KEY CLUSTERED, Name NVARCHAR(MAX));

INSERT INTO dbo.Employee(Id,Name)
VALUES(1,'John'),(2,'Jane'),(3,'Max');

Query 1:

SELECT * FROM dbo.Employee;

Results:

| ID | NAME |
|----|------|
|  1 | John |
|  2 | Jane |
|  3 |  Max |

Query 2:

UPDATE dbo.Employee
  SET Id = Id + 1;

Execution Plan

Query 3:

SELECT * FROM dbo.Employee;

Results:

| ID | NAME |
|----|------|
|  2 | John |
|  3 | Jane |
|  4 |  Max |

Query 4:

UPDATE dbo.Employee
  SET Id = Id - 1;

Execution Plan

Query 5:

SELECT * FROM dbo.Employee;

Results:

| ID | NAME |
|----|------|
|  1 | John |
|  2 | Jane |
|  3 |  Max |

If you actually follow the SQL Fiddle link, you can also see the execution plans for the two update statements. The first one looks like this:

There you can see fairly far to the left a Table Spool operator. That operator basically creates a copy of all rows that were updated before they get written back by the actual `Clustered Index Update' operator next to it. Because of this SQL Server is able to handle "overlapping" updates without tripping over itself.

An added benefit (and the original reason why this behavior was implemented) is that this two step approach provides Halloween Protection.


Storing a combination of IDs For my scenario I have already created a database user id that is auto incremented and they have already put their company id number in the registration. In the registration table both inputs are stored, but I would like to combine both together in a (database_id) (user_id) + "_" + (company_id) form so that everyone who has signed up for the application is unique in the database. Would this be possible?

Well, I think the questions is not well formulated, but as far as I got it:

You have a table containing two columns user_id and company_id
You have set the user_id to be AUTO_INCREMENT (so it's a PRIMARY KEY)
You are willing to change this column to user_id + "_" + company_id to make it UNIQUE

Answer: you're doing it wrong

Both data must not be concatenated.

Instead, what you want is a (user_id, company_id) UNIQUE KEY, which can be done with an ALTER TABLE my_table ADD UNIQUE INDEX user_company (user_id, company_id).

Thus, the way you want your AUTO_INCREMENT to work is not clear. IMO, you should remove it from the user_id, and add it on a new dedicated column id. Then, when you INSERT/UPDATE in that table, you'll have to know the user_id you're inserting/updating, which makes sense since that user_id is no longer UNIQUE to identify a row.


Recover disk in NORMAL redundancy As we know NORMAL redundancy means 2 copies of each block.

I have created diskgroup:

CREATE DISKGROUP FG NORMAL REDUNDANCY
  FAILGROUP controller1 DISK
    'ORCL:DISK3' NAME disk3
  FAILGROUP controller2 DISK
    'ORCL:DISK4' NAME disk4;

I have cleared small number of blocks for the first time.

dd bs=1024 count=200 if=/dev/zero  of=/dev/oracleasm/disks/DISK3

I have corrupted header. And I was able to repair it by kfed repair, because second copy of header (located in aus=0 bs=256) was available.

kfed repair /dev/oracleasm/disks/DISK3  ausz=4194304

And it have been repaired.

But when I've corrupted the whole disk (disk size is 1GB)

dd bs=1M count=1024 if=/dev/zero  of=/dev/oracleasm/disks/DISK3

I was not able to recover disk with kfed

[oracle@node1 ~]$ kfed repair /dev/oracleasm/disks/DISK3  ausz=4194304
KFED-00320: Invalid block num1 = [0], num2 = [1], error = [endian_kfbh]

Can't I rescue disk when I have NORMAL redundancy diskgroup where other disks are healthy?

kfed is only used to access asm disk headers and metadata - it won't help you in case of a full disk corruption.

You should take the bad disk offline and recover the disk group using the remaining good disk.

Regards,
Jony


How to delete data from both tables or least one of them, when using "delete from ... using ... where" in Postgresql? I have this sql script for my Postgresql db:

  DELETE FROM table1
  USING table2
  WHERE table2.frg_table1_id = table1.id
  AND table1.id = $1

I want to it to always delete data from table1 if the condition table1.id = $1 is met. And, optionally, delete corresponding data from table2 too. Even when  corresponding data in table2 doesn't exist, it still must delete from table1

An issue now is that, it'll delete data either from both tables or from none.

How should I twist my script?

A single DELETE statement only deletes rows from a single table (ignoring table inheritance for now). So if you want to delete from two tables, you have to run two delete statements.

You can do it in "one" statement, but using a common table expression that first deletes from table2, then from table1:

with t2_deleted as (
  delete from table2
  where frg_table1_id = $1
)
delete from table1
where id = $1;

But in the end, this is exactly the same as simply running two DELETE statements in a single transaction:

delete from table2
where frg_table1_id = $1;

delete from table1
where id = $1;


MySQL Stored Procedure not throwing error if it contains a query that succeeds I am writing a simple MySQL Stored Procedure, and calling it from PHP using PDO.

This is just simple enough to illustrate the problem I'm having.

This procedure test_procedure_2, does not throw any error.

CREATE PROCEDURE `test_procedure_1`()
BEGIN
    select 'gary';

    CALL raise_error;
END

It returns the result set:

[0] => Array
    (
        [0] => Array
            (
                [gary] => gary
            )

    )

However...

CREATE PROCEDURE `test_procedure_2`()
BEGIN
    CALL raise_error;

    select 'gary';
END

...this procedure test_procedure_2, when called, will actually throw an PDOException with the message "SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE vjs_admin_dev.raise_error does not exist". And the select 'gary' query never gets executed. I like that.

Why is it that the first procedure did NOT throw an error when it clearly contained an error? I would like to come up with a stored procedure that, whenever there is an error anywhere in it, it will throw an error. Thanks for your help!

I'm using

Ubuntu: 16.04, and
PHP: 7.0.15
mysql: Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using  EditLine wrapper

EDIT:
I have tried this with mysqli, and with PDO, and I get the same results. Here are some simple code examples that show my issue:

Mysqli Version: https://pastebin.com/rATMST4H
PDO version: https://pastebin.com/Wiq3yFmQ

The output of the Mysqli version is:

Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN  select 'gary';  CALL raise_error;END
RESULTS:Array ( [gary] => gary )
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN  CALL raise_error;   select 'gary';END
CALL failed: (1305) PROCEDURE vjs_admin_dev.raise_error does not exist
----------

The output of the PDO version is:

Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN  select 'gary';  CALL raise_error;END
RESULTS:Array ( [gary] => gary [0] => gary )
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN  CALL raise_error;   select 'gary';END
CALL failed: (42000) Array ( [0] => 42000 [1] => 1305 [2] => PROCEDURE vjs_admin_dev.raise_error does not exist )
----------

Wow, that's a relevant problem, because you can't identify an error in a procedure, an this passes hidden next to some valid result! I think your question should be more popular.

After many tries and tests I found a way to solve it. You'd expect to loop through the procedure result set, until no more results are available (the PHP Manual way). However, I found an alternate control flow to catch the errors after previous valid results:

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
    $sql="CALL procedureWithErrors()";
    $conn=new mysqli($host,$user,$pass,$db);

    /*The usual way (does not work)*/
    $conn->multi_query($sql);
    do{
        $result=$conn->store_result();
        if($result){
            while($row=$result->fetch_assoc()){
                print_r($row);
            }
            $result->free();
        }
    }while($conn->more_results() && $conn->next_result());

    /*This way works*/
    $conn->multi_query($sql);
    while(($result=$conn->store_result())!==FALSE){
        if($result){
            while($row=$result->fetch_assoc()){
                print_r($row);
            }
            $result->free();
        }
        $conn->next_result();
    }

    /*Just closing and error handling, not relevant code from here*/
    $conn->close();

}catch(mysqli_sql_exception | Exception $e){
    $error="Error #".$e->getCode()." ".$e->getMessage().PHP_EOL;
    if(isset($conn) && get_class($e)=="mysqli_sql_exception")
        $error.="SQLSTATE #".$conn->sqlstate." Statement: $sql".PHP_EOL;
    $error.=$e->getTraceAsString();
    echo(nl2br($error));
}finally{
    if(isset($result) && $result instanceof mysql_result) $result->free();
    if(isset($conn) && is_resource($conn) && get_resource_type($conn)==='mysql link') $conn->close();
}
?>

The problems seems to be that mysqli::more_results and mysql::next_result return FALSE if an error happens, so they can't distinguish between an error and the lack of further results.


Using a sequence created in the same script in Oracle SQL Server developer working on Oracle so apologies in advance if I'm fundamentally coming at this incorrectly from an Oracle perspective.

I've got a script I've written which:

Checks ALL_SEQUENCES to see if a sequence of a given name exists
If it doesn't creates it using EXECUTE IMMEDIATE
Attempts to use the sequence as part of an insert

The idea is that the script can be run multiple times without modification so the person running it doesn't have to know what's in place and what's not.

My issue is that in SQL Developer if the sequence doesn't exist, the script errors on running claiming that the sequence doesn't exist. Create the sequence and the error goes away but that rather defeats the point.

What's the best way around this?

Something like this:

declare
   counter integer;
begin
   select count(*)
      into counter
   from user_sequences
   where sequence_name = 'MY_SEQUENCE';

   if (counter = 0) then
      execute immediate 'create sequence my_sequence';
   end if;
end;
/

insert into my_table (id, some_col)
values
(my_sequence.nextval, 42)
/

EDIT: If the insert is in the same block as the create then it will need to be done as an execute immediate as well else the Sequence won't be recognised. If they're in separate blocks then it should work fine.


PostgreSQL: How to backup only One Schema from a database and restore it on another server I have a database named "A" which has two schemas "B" and "C".
I want to backup and restore Schema "B" on a different server? not sure how to do this as I am new to Postgres.
Do I have to create a new DB on new sever as of name "A" and then restore the Schema "B" on it.
pls help with commands.

--- information from comment on Peter's answer:

I want to backup and restore Schema "B " + data. Secondly I forgot to mention that Postgresql 9.1 running on Ubuntu 12.04

You can select which schemas to dump with the -n option of pg_dump.  Create a dump of schema B:

pg_dump ...other...options... -Fc -n B >dump.dmp

Restore the dump file:

pg_restore -d somedb dump.dmp

The target database does not have to have the same name as the original one.

Note that you will have problems if schema B has dependencies on schema C.  Then you won't be able to restore it separately.


Is SET INTEGRITY of any use after IMPORT statements, or just after LOAD statements? I'm getting the feeling, both from documentation and empirically, that you can only put SET INTEGRITY to good use after a LOAD, not after an IMPORT. LOAD is mentioned for SET INTEGRITY, but not IMPORT.

If you try an IMPORT with INSERT_UPDATE, you may get some rows rejected, as you may with a LOAD. Whereas you can embed the use of an exception table directly in the syntax of the LOAD or in a SET INTEGRITY statement used directly after the LOAD statement, and the exception tables will "get" the rejected rows, but hat doesn't appear to be the case with IMPORT.

There's no ability to embed the use of the exception table in the IMPORT statement, and if you run the SET INTEGRITY statement after the IMPORT has rejected the rows, it's too late.

Am I right or wrong?

Short answer: You are right in that you cannot specify an exception table for the IMPORT command. Also, there is no need to run the SET INTEGRITY statement if you use IMPORT.

Here is why:

The biggest reason for SET INTEGRITY is based on how LOAD and IMPORT work. IMPORT uses normal INSERT statements to get the data into the tables. Doing this engages the database transaction logs, triggers, and referential integrity. If a row is rejected by referential integrity, you will know about it.

LOAD bypasses referential integrity, triggers, and database transaction logs, and puts the data directly into the table (though it cannot bypass unique constraints and identity constraints). The main reason why you want an exception table is to catch the data that doesn't load into the table (perhaps it was the wrong data type etc.)

Because you bypass referential integrity, you MUST do a SET INTEGRITY afterwards to make the table aware of the data it now contains to see if anything does violate referential integrity (otherwise tables are put into a CHECK PENDING state until you do so). (It is also a good idea to run a RUNSTATS, REORG, FLUSH PACKAGE CACHE DYNAMIC, and REBIND afterwards too to make sure that indexes and the optimizer know about the new data. Actually, it is a good idea to do that anyway if you do a mass IMPORT...but that aside...).

Note that you can use the MESSAGES option with both LOAD and IMPORT to capture errors that happen so you know how to deal with them (usually after the fact though). Note that if you use both MESSAGES for a message file and FOR EXCEPTION <table name> for an exception table, any rows in error that get put in the exception table don't get listed in the message file.

Here is a good comparison of LOAD vs IMPORT straight from IBM DB2 documentation (version 9.7)


How does a database conciliate data to files on distributed filesystems? The question title might be a little misleading, but I was wondering what happens internally in a database implementation when the file used for storage are placed on a distributed filesystem (like Gluster or Ceph).

I would imagine that the database implementation keeps some data in memory and will conciliate to disk periodically. Does the database instance assume that it is the only one writing to the storage file and use this for optimizations somehow? Can someone elaborate and suggest some good materials on this topic?

My previous understanding was that this wouldn't work, but after a quick search on Google it seems that not only it works but it is fairly common (I'm not talking about explicit replication features).

In simple terms, the DBMS doesn't care (or in some cases even know) if the file system where its files are located is distributed or not. In a high availability scenario with shared (distributed) storage an active-passive setup is most commonly used, where only one instance of the DBMS at a time is running and has access to the database files. It's the responsibility of the cluster manager (software or human) to control DBMS instance startup and failover.

In a shared data cluster, such as Oracle RAC or Db2 pureScale, there are multiple instances of the DBMS running concurrently and accessing the same set of database files, and a special component of the cluster manages concurrent access to the files by the DBMS processes.

That "special component" is usually tightly integrated with the DBMS itself but the actual implementations differ wildly. Oracle Clusterware plays this role in an Oracle RAC; Cluster Cache Facility in Db2 pureScale; both are tightly integrated with their respective underlying shared file systems.

I'm not aware of any open source shared-data cluster databases; they all tend to choose shared-nothing architectures, which are somewhat simpler to implement and more tolerant towards low-end hardware.


Left join on a varchar column I know this is probably something stupid on my part, but please bear with me. I have basically 3 tables (let's simplify things):

users
user_id | account_id | phone | name | ...

accounts
account_id | name | ...

phonedetails
phone | country_code | ...

Let's pretend we have a simple query like this:

SELECT u.*, a.* FROM users u
LEFT JOIN accounts a ON a.account_id = u.account_id
ORDER BY u.user_id DESC
LIMIT 100

This correctly assesses that the query needs to use eq_ref on account_id and properly selects only 100 records and is almost instant. However, when I want to do this (this is a simplified version of what I want to use):

SELECT u.*, p.* FROM users u
LEFT JOIN phonedetails p ON p.phone = u.phone
ORDER BY u.user_id DESC
LIMIT 100

It suddenly tries to go through Full Table Scan (so it's about 300 000 records at the moment) and then does its job. Sure, it works, but it takes almost 4 seconds for whatever reason!

account_id => type int in both tables (users, accounts)
phone => varchar(24) in both tables (users, phonedetails)

It doesn't seem to matter if I have an index on that column or not.
Funny thing is that if I do something like this:

SELECT u.*, (SELECT p.country_code FROM phonedetails p WHERE p.phone = u.phone)
FROM users u
ORDER BY u.user_id DESC
LIMIT 100

It correctly joins the tables using eq_ref and it's fast.

Is there something I can do to improve on the query performance?

Edit with more info:

Interesting (at least for me), is that if I execute this query:

SELECT u.* FROM users u
LEFT JOIN phonedetails p ON p.phone = u.phone
ORDER BY u.user_id DESC
LIMIT 100

Explain shows those tables are joined correctly with eq_ref on the primary key and it's fast again. Of course, it's useless for me as I don't get the columns I need.

OK, I'll stop trying to figure out why your query is not working and give you a technique for solving it...

SELECT  u.*, p.*
    FROM
       ( SELECT  user_id   -- minimal data (just PK in this case)
            FROM  users
            ORDER BY  user_id
            LIMIT  100    -- now in subquery
        ) AS x
    JOIN  users AS u  ON u.user_id = x.user_id
    JOIN  phonedetails AS p  ON p.phone = u.phone
    ORDER BY  x.user_id   -- yes, need extra ORDER BY

The general principle:  When doing a LIMIT or GROUP BY and the query is even somewhat complex, it may be better to build a subquery that identifies which rows as simply as possible.  Then do the JOIN, etc, to get the rest of the data.

The subquery (called a "derived" table in this syntax) finds the user_ids -- and only the 100 needed.  Since you are doing LEFT, there is no need to involve phonedetails in this query.
Then reach out to the other tables to get the desired columns.

Caveat:  This version of the query may be slow for the same yet-to-be-discovered reason.  This reformulation is effective in some situations.


naming a column differently then its original one in foreign data wrapper of PostgreSQL I have two databases. In each database I have an employee table which differ from each other in the number of columns and column names.

Here is what the table looks in the first database:

 Emp_ID    Name        Father_Name      Family_Name
 1         Max         Stjepan          Neg
 2         John        Chris            James

Here is what the second table looks like which will be in the second database:

 ID        Name        Father_Name      Family_Name   Blood_Type
 1         Max         Stjepan          Neg           ARH+
 2         John        Chris            James         ORH-

Now when creating the foreign table in Second Database I want to store the Emp_ID values from First Database with column name ID and also I want my foreign table to include the blood type from the table which is in the current database Second Database.

Shortly, how can I create a foreign table with different column names than the original table in remote database? It should also include the columns from first table which is in the current database.

You can name the columns of your foreign table whatever you want.  For example, after an IMPORT FOREIGN SCHEMA ... you can do

ALTER FOREIGN TABLE myschema.mytable RENAME a_column TO another_column;

(For CREATE FOREIGN TABLE, you have to specify the different column name in the OPTIONS clause to the column: CREATE FOREIGN TABLE foreign.employee (emp_id integer OPTIONS (column_name 'id'), ...)

What you cannot do is mixing columns from local and foreign tables.  To achieve something similar, you can create a view:

CREATE VIEW unified_employee AS
SELECT local.id AS emp_id,
       local.name,
       ...,
       foreign.blood_type
  FROM local_schema.employee AS local
  JOIN foreign_schema.employee AS foreign ON local.id = foreign.emp_id;

I swapped the tables in this example on purpose.  Your example tables don't differ enough, so a simple view on the local employee table, where the id column is named emp_id would do the trick, without ever touching the other database.


Result Cache disabled in Oracle 12c Enterprise - BYPASS status I have two database instances, development and production that are fairly identical.  On production, result cache is working fine, in development it is not.  The value for the database parameter result_cache_max_size was set to 0 so I reset it to remove the value, bounced the database, and when the database was back up, the value was still set to 0 so I tried starting with a PFILE with no value for result_cache_max_size and same problem.  Then I tried setting it to 20M (value from production) and bounced, but same results.
I then invoked

SELECT dbms_result_cache.status() FROM dual;

which returns BYPASS.

The parameters result_cache_max_result and result_cache_mode are set to 5 and MANUAL on both databases.

I am using Enterprise Edition on both instances (so question Why is the result cache always disabled in Oracle 11g does not apply to me):

Development:

SYS@extdev02> select banner from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
...

Production:

extuat01> select banner from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
...

Edit:  Tried bypass proc per @BalazsPapp answer but no luck.  I think I tried it already, but without the second parm, which would have defaulted to false per the documentation.  Today's try, including a flush proc just for fun:

SYS@extdev02> select dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
---------------------------
BYPASS

SYS@extdev02> exec dbms_result_cache.bypass(true, false);

PL/SQL procedure successfully completed.

SYS@extdev02> exec dbms_result_cache.flush

PL/SQL procedure successfully completed.

SYS@extdev02> select dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------
BYPASS

SYS@extdev02> exec dbms_result_cache.bypass(false, false);

PL/SQL procedure successfully completed.

SYS@extdev02> select dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------
BYPASS

which returns BYPASS.

Then enable it:

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL> exec dbms_result_cache.bypass(true, false);

PL/SQL procedure successfully completed.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
BYPASS

SQL> exec dbms_result_cache.bypass(false, false);

PL/SQL procedure successfully completed.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL>

DBMS_RESULT_CACHE.BYPASS


Query problem with array_to_json() Considering my basic knowledge of PostgreSQL I am unable to find out why my second statement for where clause (record->'details'->>'amount')::numeric(19,2) = 2.99 is not working with current query. Idea is to send common filter from UI for header and details and when I am trying to filter to inner array details Json column its not giving me a exact result though it has some data within it

WITH t as (select row_to_json(rr)::jsonb as record
from (
       select (
                select row_to_json(row)
                from (
                       select
                             c.customer_id,
                             c.first_name,
                             c.last_name,
                             c.email,
                             count(*) total,
                             sum(p2.amount) total_amount
                       from payment p2
                       where p2.customer_id = c.customer_id
                     ) row
              ) as header,
              (select array_to_json(array_agg(row_to_json(row)))
                from (
                       select
                             amount,
                             payment_date
                       from payment p1
                       where p1.customer_id = c.customer_id
                     ) row
              ) as details
       from customer c
       join payment p on p.customer_id = c.customer_id
     ) rr)

    SELECT record FROM t
    where record->'header'->>'customer_id'= '2'
    and (record->'details'->>'amount')::numeric(19,2)= 2.99

It would be performance suicide to first build a huge jsonb object from joining two tables, and then filter a single record representing an underlying row. You would filter first, of course.

Going out on a limb, this might be what you are trying to do:

SELECT to_jsonb(rr) AS record
FROM  (
   SELECT (SELECT to_jsonb(row) FROM  (
              SELECT c.customer_id
                   , c.first_name
                   , c.last_name
                   , c.email
                   , p3.total
                   , p3.total_amount) h) AS header
        , p3.details
   FROM   customer c
   JOIN   LATERAL (
      SELECT count(*) AS total
           , sum(p2.amount) AS total_amount
           , jsonb_agg(detail) AS details
      FROM  (
         SELECT p.amount
              , (SELECT to_jsonb(d) FROM (SELECT p.amount, p.payment_date) d) AS detail
         FROM   payment p
         WHERE  p.customer_id = c.customer_id
         ) p2
      ) p3 ON p3.total_amount = 2.99   -- here !
   WHERE  customer_id = 2              -- and here !
   ) rr

There is no record->'details'->>'amount' in your object. There are array elements like record->'details'->0->>'amount', but those make no sense with the = operator. So I assumed you are aiming for total_amount.

A little less confusing with jsonb_build_object():

SELECT to_jsonb(rr) AS record
FROM  (
   SELECT jsonb_build_object('customer_id', c.customer_id
                           , 'first_name', c.first_name
                           , 'last_name', c.last_name
                           , 'email', c.email
                           , 'total', p3.total
                           , 'total_amount', p3.total_amount) AS header
        , p3.details
   FROM   customer c
   JOIN   LATERAL (
      SELECT count(*) AS total
           , sum(p2.amount) AS total_amount
           , jsonb_agg(detail) AS details
      FROM  (
         SELECT p.amount
              , jsonb_build_object('amount', p.amount
                                 , 'payment_date', p.payment_date) AS detail
         FROM   payment p
         WHERE  p.customer_id = c.customer_id
         ) p2
      ) p3 ON p3.total_amount = 2.99   -- here !
   WHERE  customer_id = 2              -- and here !
   ) rr;


Is there a DMV that shows the aggregate logical reads on a SQL server? I want to use this metric to compare 2 servers. I've looked at sys.dm_io_virtual_file_stats, but this is missing logical. Also looked at query_stats, but that only includes cached plans.

I think that what you might be after you may find in a different DMV:

select * from sys.dm_os_performance_counters

sys.dm_os_performance_counters

But pay attention to these features:

  Counters like Page reads/sec appear to be cumulative. Which just
  blows my poor head. In that case it should be named like 'pages read'.
  So to get actual Page reads/sec you have to do smth like:

  divide the counter value by amount of seconds since startup (you can
  get it by select datediff(ss,create_date, getdate()) as uptime from
  sys.databases where name = 'tempdb').


Order database view in new columns I've created a view from a table in SQL Server which cannot be altered in any way. The purpose is to make the fairly complex table conceivable for the users.

The view consists of several columns which share an ID and a relation, vendor & vendee, (VO & VE). Think of the ID as some kind of transaction-ID:

NAME | REL | ID
JAKE | VO  | 1234
JOSH | VE  | 1234
ANNA | VE  | 1212
FAYE | VE  | 1212
MATT | VO  | 1212

What I want to do is to create two new columns in the view based on their given relation and sorted by the ID and add a count based on the number of vendees involved in that given transaction.

  VO |  VE  |  ID  |CNT
JAKE | JOSH | 1234 | 1
MATT | ANNA | 1212 | 2
MATT | FAYE | 1212 | 2

Kind of new to SQL, is this even possible to perform with a view?

This code should get your sorted.  Note: the top portion is just to set up sample data.

This will include a row if there is a vendor, but no vendee (VE will be null).  It will not include a record if there is a vendee, but no vendor.

-- You Sample Data
WITH YourTableName AS (
    SELECT * FROM (
        VALUES ('JAKE' , 'VO'  , 1234)
                ,('JOSH' , 'VE'  , 1234)
                ,('ANNA' , 'VE'  , 1212)
                ,('FAYE' , 'VE'  , 1212)
                ,('MATT' , 'VO'  , 1212)
    ) AS V(NAME, REL, ID)
)
-- Your View Text, replace 'YourTableName' with... wait for it... your table name:
SELECT VO, VE, Vendor.ID, COUNT(*) OVER(PARTITION BY Vendor.ID) AS CNT
FROM
    (SELECT Name AS VO, ID FROM YourTableName WHERE Rel = 'VO') Vendor
        LEFT JOIN (SELECT Name AS VE, ID FROM YourTableName WHERE Rel = 'VE') Vendee
        ON Vendor.ID = Vendee.ID
ORDER BY Vendor.ID DESC, VE  -- Can be included, really need to be specified when querying the view


MySQL Performances Issues I'm in debate with my database admin. He claims that my queries I'm sending through PHP are the problem and he is stating several things I believe are completely false.

To begin I have a site receiving 64,000 daily views. My server monitors queries over 5 seconds. Multiple times during the day I see 3-6 "SELECT" queries all taking more than 10 seconds, sometimes even up to 200 seconds. This is on an InnoDB table on MySQL 5.0. Also PHP is calculating the speed of the query using microtime. Ironically enough I try the same queries in PHPMyAdmin and they take 0.0031 seconds.

My Admin says I shouldn't use an asterisk in my code, but nowhere online goes against that. He says I should convert all my tables from MyISAM to InnoDB. I have already done that but still the same things happen. He doesn't seem to be budging and even worse once in a while my server is down for 1-2 minutes with a MySQL error of "Too Many Connections" or "MySQL Server has gone away"

The Admin isn't listening to reason and these problems continue. I'm on a version of mysql with profiling disabled so I can't really see query times of just mysql. Can anyone suggest anything I can do or try to diagnose this? I'm only the coder but I have experience in mysql (probably more than the admin).

In your question, there is not enough information to see where the problem is from technical point of view; however, I can see that there is a problem in communication between you as developer and the DBA. This is not abnormal though as DBAs and devs do not always 'speak the same language'.

Let's go the some points in your question:

You should rely on the 'slow query log' to get your slow queries. Monitoring them in PHP may not be accurate measurement for slowness of the queries.
The query that took very little time in PhpMyAdmin could be cached. Use "SELECT SQL_NO_CACHE", and repeat at times that are not close to each other to avoid hardware level caching.
"SELECT *" vs "SELECT field1, field2": If you don't need all fields in your result, don't use "SELECT *", as it will waste resources sending non-wanted values.
"Too Many Connections" could be of different reasons, one of them is slow queries that lock the tables so other connections are waiting and piled up to the max where the server refuses new connections.

My recommendation is that you talk to your admin and work both as one team, not as two separate teams.

HTH


MySQL Download Question I want to download MySQL and use it on CentOS. But I found the downloading selection menu on the MySQL website is confusing. Which one should I choose? See the attached image.

Thanks.

The first looks like it is for RHEL5 and the second for RHEL6 (and clones) - judging by the filenames for the downloads:


Load data from a temporal table to another temporal table in Azure Currently we have only a Live Database where all our data resides. I have created a separate Azure server and DB for development.
The DEV DB should be some what up to date with the Live DB for developers to work on.

The Live DB has temporal or system-versioned tables. I need a process that would sync the data from the Live temporal tables to the DEV temporal tables. It should load the sysStartTime and sysEndTime that is in the Live temporal tables to dev(should not generate new sysStartTime  and endTime in DEV).

What is the best way of doing this?

I already tried Azure data sync but it does NOT support temporal tables. Please help.

You have two interacting problems here: transferring data between Azure databases, and updating data maintained by system versioning. And possibly a third: live data in dev.

Transferring Data in Azure

If Azure Data Sync is not appropriate then there are other options that you might consider.

You could perhaps use SSIS packages or other ETL options to shuffle the data from A to B.
Linked tables are not supported in the same way as in on-prem SQL Server, but you can use "external tables" as an alternative.
Elastic Database Jobs offer a way to query data from one DB and write it to another, though this is more intended for logging rather than fuller data transfer so using it this way feels a bit hacky.

Updating History Tables

What-ever method you use to transfer data from one DB to the other, you have the issue of needing to directly manipulate the history data. As you have noted the system versioned tables protect themselves from this sort of modification: you can't directly modify the history table at all or specify the timings logged for modifications in the base table.

What you have to do for each table is:

Turn off system versioning:
ALTER TABLE schema.table SET(SYSTEM_VERSIONING = OFF);
ALTER TABLE schema.table DROP PERIOD FOR SYSTEM_TIME;
Make the changes you need to both the base and history tables
Restart history tracking:
ALTER TABLE schema.table ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime); ALTER TABLE schema.table SET(SYSTEM_VERSIONING = ON); (you may need to add other options depending on how your tables are currently setup, for instance if your tables do not use the default naming convention).

So your overall process might be in four stages: bring updated data from the source, turn off history tracking, update the target data, turn back on history tracking. While those last three stages are happening you need to block other access to the target DB or corruption of the history data could result.

Alternative

Depending on the size of your databases, and the nature of your dev workflows, it may be far easier to simply backup and restore a complete fresh copy each time you need to refresh the other copies.

You may want to work with a restored copy of the database anyway, to remove the extra IO load that may slow production instances of your application, to easily make sure you get an accurate snapshot of the data without holding many locks, or to allow for a full depersonalisation process (see below).

Live Data In Dev

Unrelated to the technical matters of your question, this is likely to be problematical from a data protection and security PoV. If your application contains any personally identifying, or otherwise sensitive, information it is a big "no no".

If you do take data directly from Live systems for development then you should be "depersonalising" is on the way. This is usually easier to do reliably with the whole database backup+restore method: copy the DB in production, depersonalise the copy, back that up, transfer to the dev server, delete the now unneeded copy on the production server.

It is often better to generate sample data as part of your development process rather than trying to use production data. This way a problem in any depersonalisation processes won't expose you to security problems (and failed audits if you work with companies that require you be audited) because you never have live data to accidentally not alter. This test data can be more useful than real data if well-designed because you can make sure it contains all your expected edge cases (and some unexpected ones!) so it becomes part of your development and testing procedures.

You may need to occasionally copy production data for debugging purposes, when you have failed to reproduce a reported problem using other data, but this sort of testing is different from development and should be a last resort not a first port of call.


Freeze I/O on purpose like a vss snapshot I want to freeze the I/O of a SQL Server instance like the shadow copy does.

The result should look like this:

2020-01-19 23:11:40.90 spid73      I/O is frozen on database prod. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

2020-01-19 23:11:57.89 spid73      I/O was resumed on database prod. No user action is required.

But I want to decide when the I/O is resumed. I tried it myself with a backup, but it finished too fast.

I want to debug my application during this I/O freeze.

How can I achieve this behavio(u)r?

Edit - 2020-01-27 10:53

The purpose is that the SQL Server doesn't accept new connections during this time:

Exception:
Class name:    EMSError
Message:       [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Unit name:     OLEDBAccess

There are two undocumented DBCC commands you can use for this, on a test system of course.

DBCC FREEZE_IO('database_name');
DBCC THAW_IO('database_name')

Check out Erik Darling's blog post here, which is where I first heard about these commands: Wait Stats When VSS Snaps Are Slow

He's also using them to diagnose what behavior is like during these snapshot-related freezes.


Proper normalization for an Orders table--whether to split or no Each order has a unique SalesOrderNumber. An order is made up of various PartID numbers, each with an associated OrderQuantity number.

I have the following table currently in my model:

I'm wondering if this table should be split into two tables as such:

This would leave the Order table with only one row for each SalesOrderNumber, but I'm not sure if this is necessary from a normalization point of view. It seems that either arrangement should meet 2NF. I'm still a bit shaky with 3NF and BCNF however.

Here is what a sample from the table might look like now, with only relevant columns displayed for a made up SalesOrderNumber of 12345:

SalesOrderNumber PartID OrderQuantity
12345            1a     1
12345            2b     2
12345            3c     43

The objective of normalization is to remove update anomalies. Specifically, changing a single fact should result in a change to one column of one row only. Consider how this would apply in your proposed designs if, say, ShippingDestination were altered, which is a very reasonable business requirement.

In the first design multiple rows may change as, potentially, there are multiple rows for a single SalesOrderNumber, each with its own PartID. With the second design only a single row is updated. So the second design is more normalized.

For a different point of view you can look at the dependencies - what values change in unison. In the first design if you go from one row to another with the same SalesOrderNumber the ShippingDestination will be the same as will DueDate. However OrderQuantity will differ between these two rows. So OrderQuantity depends on more than just SalesOrderNumber - you need to know PartID also. Two different dependencies so two different normalised tables. (There's more to Functional Dependencies / Dependency Decomposition, but that's the gist of it.)

2NF talks about "part key dependency" - whether all of the columns in the key are required to determine all non-key columns' values. Looking at the first design there is a composite key of (SalesOrderNumber, PartID). As we move from one row with a particular SalesOrderNumber to the next with a the same number some columns (ShippingDestination for example) will always be the same by definition. PartID is in the key but is not required to determine the value of ShippingDestination. So the first design is not 2NF.

3NF talks about "transitive dependency" - whether a column's value changes because the key changes or because a separate non-key column's value changes. I don't know enough about the actual business rules to say if the second design is in 3NF so I'll invent a scenario to demonstrate. Let's say a customer can belong to only one channel. As we move from one row in Order to the next we see that both CustomerNumber and Channel change value. However, Channel changes value not because there is a different SalesOrderNumber but because there is a different CustomerNumber. Channel varies by SalesOrderNumber by way of (i.e. transitively) CustomerNumber. In my made-up scenario Channel should be moved from Order to a new table (Customer) keyed by CustomerNumber.

To violate BCNF the table must have more than one candidate key which share at least one column. Neither design has this so, once in 3NF, the design is also in BCNF.

So yes, split the tables.


Unable to Upgrade SQL Server from 2012 to 2016 SP2 in Cluster environment I have a Windows 2012 R2 Datacenter cluster environment.
With active Passive nodes of SQL Server 2012 SP4.

I am trying to upgrade the SQl Server from 2012 SP4 to 2016 SP2.
I am performing in-place upgradation first on Passive node and then on active node.

My installation failed with below error.
Error: the cluster resource is not online. Bring analysis service server online before starting upgrading process

I have analysis services install on both the instance node.
First the Analysis service was stopped when I got the error, So I started the analysis services and re run the installation but it failed with same error.

Since analysis services are not required I tried to remove the feature but guess we cant add or remove features from 2012 cluster environment.

So, Basically I am stuck here and dont know how to proceed with the installation.

Can anyone please help me out!!

Thanks in advance.

@Learning_DBAdmin Following you lead, i checked for dependencies and found there are no dependencies on analysis services.

Also i dont see analysis service resource in roles tab in failover cluster manager.

Is it posssible that i havent properly configured the analysis services on the server?

Following your previous link i came to know that i need to create SSAS generic resource manually in the failover cluster manager. Can you help me to create SSAS generic resource in failover cluster manager.

Thanks in advance.

Ideally in-place upgrade is not very much recommended as this poses multiple risk and has potential to bring your whole database down. You may perform the same on a different server using existing set-up and replace the old servers with new server(set-up) in your go-live activity.

Having said above, few customers go for this upgrade in order to save licensing and servers. From the error message, its very clear that MSSQL service is having dependency on SSAS. Please refer the below screenshot of dependency tab:

No Dependency:

With Dependency:

Service depends on other service:

[

Other service depends on this service:

Based on terminology, I hope its clear that what service depends on other service.

This error would have been logged in either summary or detail log as described here at Mr. Pinal Dave's article:

https://blog.sqlauthority.com/2017/06/20/sql-server-upgrade-failure-cluster-resource-not-online-bring-analysis-services-server-online-starting-upgrade-process/

This also suggests that there was something wrong with the installation, meaning same set of SQL components are not available on active and passive nodes.

As you have already confirmed from the comments that it has worked, you may find details about dependencies above.

I hope above helps.


What is the expected performance impact when dropping a large table I have a 100G+ innodb table that needs to be dropped in a production environment. I am trying to do this without interruption to service.

The table is no longer being written to, and needs to be dropped to free up space. My MySQL version is 5.1.52.

What is the expected impact to performance while this table is being dropped? I was thinking of slowly shrinking it by deleting rows and then optimizing. However, optimize makes a copy of the table so it will be just as disk IO intensive, if not more.

Deleting in batches and then optimizing is really the worst thing you can do.  Each delete will be performed as a transaction and each optimize will effectively recopy what remains of the giant table.

Think of it like this.  You have a 5 gig table, delete 0.5 gigs, optimize (copy 4.5 gig file), delete 0.5 gigs (copy a 4.0 gig file) etc..

And really the overhead associated with each optimize is much more than just copying that much data on a filesystem since it's tantamount to inserting all those rows into a new table and all the transactional overhead that comes along with it.

Dropping a table should be the most efficient way to do it

Edit:  Here is some thorough benchmarks though

http://www.mysqlperformanceblog.com/2011/04/20/drop-table-performance/


How to become an administrator for SQL Server Here is the situation:

I Installed VS2013
That comes with SQL Server Express
I am not able to create a DB from VS2013
thinking that it's because of user privilege issues, I Installed SQL Server Management Studio
I have looked to figure out how to give myself Administrator privileges on SQL Server, and nothing has worked so far
I am the administrator of my Windows7
I run the SQL Server Management Studio as Administrator

Still I get error messages about not having permission to perform this action: Error 15247

Any ideas what I need to do to have permission

Link to image.

Since you have made sure that you have SQL Server installed on your machine all is left is to make sure that you follow the following steps to create a database.

1) Go to Server Explorer right click Data Connections and Select Create New Sql Server Database...

2) This will open a pop-up window enter your MachineName\SQLEXPRESS in Server Name section or select a server name from the Drop down list.

3) Enter the New Database Name.. .

And you are good to go.


Remote MySQL host - is connecting to remote port 3306 vulnerable? Hello experienced colleagues,

I have a web application built on a MySQL database. I need to run another copy in a remote location, and refresh the data weekly. The size of the refresh is about 500k records, and is not the entire database, which is >15 million records.

My idea was to get the data running on a local Test Server, then connect to the remote Production Server's port 3306, and copy the data. the 'copy' part is not  problem, we have a python script, it is fast allright.

However, I have been warned that connecting to port 3306 is not safe, the data is not encrypted, etc etc.

I am in a desperate search for a solution. Here is what I have come up with so far:

Connection to MySQL client's port 3306 with SSL -- is it an off-the-shelf solution?
Connection over SSH tunnel -- I'm worrying about the speed of the connection.
Make the remote machine a VPN client of our intranet, and connect to the intranet IP's port 3306  (there are problems with DNS)
Write a sort of a 'back door', a MySQL client that would receive the data over SSL and update the database

As you see, the ideas are sorted in the order of ease-of-implementation, and I would really like one of the first two to work.

Establish SSL connections.  Don't worry about the speed -- your 500K-row refresh will take minute(s), with or without SSL.


MySQL - MySqlDump uses \n instead of null Using Windows with MySQL 5.1.

I am running a script that uses mysqldump to backup a database.

The mysqldump syntax used is:

mysqldump --user=root --port=3306 --password=topsecret some_database --tab=C:\ARCHIVE\some_database --triggers --routines --fields-enclosed-by=\"" --fields-terminated-by=, --lines-terminated-by=\r\n

This creates a text file and when I open it all NULL fields show as \n

Is there a setting for mysqldump that would export NULL fields as NULL?

There are no options for handling the output of NULLs.
You may want to experiment with --hex-blob. This will dump BINARY, VARBINARY, BLOB fields in hexadecimal format. This should make the data portable. You can see what this option produces for NULL values.
mysqldump --user=root --port=3306 --password=topsecret --hex-blob some_database --tab=C:\ARCHIVE\some_database --triggers --routines --fields-enclosed-by=\"" --fields-terminated-by=, --lines-terminated-by=\r\n

Give it a Try and let us all know if this helps.
UPDATE 2012-01-13 18:26 EDT
This may sound very gross but you can pipe the output of mysqldump into sed as follows:
mysqldump --user=root --port=3306 --password=topsecret --hex-blob some_database --tab=C:\ARCHIVE\some_database --triggers --routines --fields-enclosed-by=\"" --fields-terminated-by=, --lines-terminated-by=\r\n | sed 's/\\N/NULL/g'

Of course, you cannot import that into MySQL until you want the string NULL to be the value to be imported.


Normalization to Third Normal Form (3NF) I have been given the example data and have been struggling to normalize to 3NF. I think the issue lies with the Tutor Name but this is my final chance at submitting so would like to get it right.

This is how I have normalised it,

3NF

Course

Course Code, Course Title, Level, Mode of Attendance, Course Fee, Start Date, End Date, Department*

Student/Course

Course Code, Student ID, Enrolment Date, Enrolled By

Student

Student ID, Student First Name, Student Last Name, Student Street, Student Town, Student Postcode, Student Landline, Student Mobile, Student DOB

Department

Department, Department Head Title, Department Head First Name, Department Name Last Name, Tutor First Name, Tutor Last Name

Thank you in advance

I feel that you could add an additional Entity in there, one that represents people as a part of this (school-like?) institution, call it "People" (yeah...)

So Tutors, Students, Teachers, Head of Departments, etc are all People.
That table will hold Attributes that the sub classes/entities will share (because they're all people, right?): Firstname, Lastname, DoB, etc.

You then refer to this table on your other entities. Eg the Teachers by using the FK PeopleId (or whatever you call it =)

A student is then able to become a tutor and even a Teacher or a Head of Department at some point without being added to simultaneous tables within the database.

To, then, define a relationship between your Course, your Students and your Tutors you'll use a junction table in between that could look like:

------------------
|  CourseTutors  |
------------------
| FK | CourseId  |
| FK | StudentId |
| FK | TutorId   |
------------------

Thats very granular, it will even allow you to have one tutor per student.


Why do we have undocumented and unsupported functions in SQL Server? After reading an article, dated 2012, about the undocumented functions fn_dblog and fn_dump_dblog I was wondering why they are still undocumented and unsupported if they have been around for a long time and are quite useful. Same with this sp_msforeachtable and sp_msforeachdb article from 2004.
I could benefit from one of them to answer a question on the forum (During a log backup is the data backed up to the start or end of the operation?), but I'd like to know why they're shipped with SQL Server in a undocumented and unsupported way until now.
I understand they're said not to be reliable, but if that was the only reason, in 16 years they could have been corrected. So there must be another explanation.

As someone working for a large software vendor, I can say that undocumented and unsupported1 features typically fall into one of these three categories:

Tools for the exclusive use by the vendor support engineers. They are rarely needed, typically require intimate knowledge of internals, hard to set up and use, and often dangerous enough to be protected by one-time passwords or keys that should be explicitly provided by the vendor. They are never intended to become product features. They are there to aid support when troubleshooting hard to debug problems.

Fixes and workarounds for esoteric bugs. They are implemented as hot fixes for critical situations. Such bugs are rare, and their root causes are eventually resolved, so these "features" lose their usefulness at that point, and it's not in the vendor's interest to provide continuous support for them.

Beta and experimental features. They are typically first made available and sparsely documented for a small cohort of beta testers and major customers who requested them. Some of these will eventually become fully supported, some others will be left there to rot when stakeholders lose interest or when they conflict with other features that are deemed more important or useful.

As mentioned in the comments to your question, each fully supported and documented feature carries a cost to the vendor, and that cost must be justified by the expected benefit. If there is little benefit to the vendor from maintaining a certain feature, there's no reason to support it.

1 - They are not really totally unsupported; they are supported on a "best effort" basis, meaning "We might have a technote somewhere that briefly describes the feature, but we won't spend much time helping you make it work, and if it doesn't work or if it wipes out your data, well, tough".


stored procedure not showing a result If I run the query outside the stored procedure, I get results, but I only get the column names as the result after executing the query - even though return value is 0. Any help would be appreciated.

ALTER PROCEDURE [dbo].[Reconciliation2]
(
    @Param1 int,
    @Param2 int,
    @Param3 varchar,
    @Param4 varchar
)

AS

BEGIN
    SET NOCOUNT ON;

    SELECT
        T.FY AS [FY],
        T.FY_PERIOD AS [Period],
        CONVERT(DATE, CREATED_DATE) AS [Created Date],
        CONVERT(DATE, TRANS_DATE) AS [Trans Date],
        T.TRAN_DOC_TYPE AS [Doc Type],
        TASK_ID AS [Task],
        T.DOC_NO AS [Doc Number],
        LINE_NO AS [Line],
        SEQNO AS [Seq],
        LINE_DESCRIPTION AS [Description],
        SUBSTRING(ACCT_NO,1,2) + '-' +
           SUBSTRING(ACCT_NO,3,5) + '-' +
           SUBSTRING(ACCT_NO,8,5) + '-' +
           SUBSTRING(ACCT_NO,13,6) + '-' +
           SUBSTRING(ACCT_NO,19,4) + '-' +
           SUBSTRING(ACCT_NO,23,4) AS [Account Number],
        DEBIT,
        CREDIT
    FROM
        DBVW_FIN_TRANSACTIONS_UDR T
    INNER JOIN
        (SELECT
             INST_ID, FY,
             TRAN_DOC_TYPE, DOC_NO
         FROM
             DBVW_FIN_TRANSACTIONS_UDR
         WHERE
             INST_ID = 'SC00'
             AND FY = @Param1
             AND FY_PERIOD = @Param2
             AND ([ACTION] = '' OR [ACTION] = 'V')
             AND SYS_GEN_DETAIL_LINE = 0
             AND TRAN_DOC_TYPE = @Param3
             AND TASK_ID = @Param4
         GROUP BY
             INST_ID, FY, TRAN_DOC_TYPE, DOC_NO) TRAN_BASE ON TRAN_BASE.INST_ID = T.INST_ID AND
                                                      TRAN_BASE.FY = T.FY AND
                                                      TRAN_BASE.TRAN_DOC_TYPE = T.TRAN_DOC_TYPE AND
                                                      TRAN_BASE.DOC_NO = T.DOC_NO
    WHERE
        T.INST_ID = 'SC00' AND
        T.FY = @Param1 AND
        T.SYS_GEN_DETAIL_LINE = 0 AND
        T.TRAN_DOC_TYPE = @Param3 AND
        TASK_ID = @Param4
    ORDER BY
        T.DOC_NO, T.LINE_NO, T.SEQNO
END

Stop using things like varchar without specifying the length. Watch what happens here:

CREATE PROCEDURE [dbo].[Reconciliation_broken]
    @Param1 varchar
AS
BEGIN
    SET NOCOUNT ON;
    PRINT @Param1;
END
GO
EXEC dbo.Reconciliation_broken @Param1 = 'what happened?';

Results:

w

When you don't specify the length, you get silent truncation - in some cases it is 30 characters; in this case, it is 1 character. There is no error message or warning - in fact if you were inserting data here you would be inserting 1 character and who knows how long you would be doing that before you realized. That data would be lost with no hope of recovery.

So, change those parameters to match the data type and length of the underlying columns. For more information, see:

Bad habits to kick : declaring VARCHAR without (length)


Rebuild Index in system database I use SQL Server and have multiple indexes in system database whit index fragmentation 30% or more.
I don't know that SQL server manage system database index automatically or I want to manage them. and manage index manually by I is a good thing or not.

thanks in advance

The SQL Server will maintain these indexes automatically.  If they have become fragmented you'll need to defragment them manually or via a scheduled job just like the user databases.


Powershell Script Error Connecting To SQL Server Problem
I have a PowerShell script that pulls user information (Domain and/or SQL users, their groups and other information) and spits it out to an Excel/csv file. This script worked in my virtual lab environment; however, when executed from client issued laptop or when I remote directly into server I get the following error:

Error connecting to server  [Server Name]

Notes
I have also gone into SSMS and opened PowerShell directly from within and get same error.
I mentioned my virtual lab environment. That is a virtual server cluster I have on my personal laptop. When I execute the script on my virtual environment it executes fine and spits out the Excel file. This environment is a 2 node cluster using Windows Server 2012. My SQL Server is the 2012 Developer version. Powershell version 4.0.
Environments
Client environment varies. On desktop using Windows 7, Powershell 5.0 and SQL Server 2014. The particular server I am connecting to is Windows 2008 R2 and SQL Server 2008 R2 and Powershell 4.0
Script
    clear
Write-Host -ForegroundColor Green "Starting"

$MaximumErrorCount = 15000
#import SQL Server module
Import-Module SQLPS -DisableNameChecking

$today=(get-date -f yyyyMMdd-HHmmss)

#a list of instances you want to send the query to
$instances = Get-Content "C:\Users\Jimmy.Johns\Desktop\Instance_list\instance.txt"
$query = 'TSQL. Left out full code for space/formatting'
$databasename = "master"
$instances |
ForEach-Object {
try {
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $_
     Invoke-Sqlcmd -ServerInstance $_ -Database $databasename -Query $query -QueryTimeout 15   | Export-Csv -Append -Path C:\Users\Jimmy.John\Desktop\Output\ListPermisions-$today.csv
} Catch {
    $_ | Out-Null
    "Error connecting to server  $server" | Export-Csv -Append -Path C:\Users\Jimmy.John\Desktop\Output\ListPermisions-$today--Errors.csv
    Write-host "Error connecting to server  $server"
 }
}

Write-Host -ForegroundColor Green "Done"

I have read http://www.sqlshack.com/connecting-powershell-to-sql-server/ and used connection string and still will not connect.
Update
After changing $_|out-null with $error[0].exception and running from the client desktop I got the following error:

Could not file or assembly 'Microsoft.SQLServer.BatchParser, Version= 12.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system could not load the file specified.

I also got this error when I ran the edited script inside the server.

Import-Module : The specified module 'ActiveDirectory' was not loaded because no valid module file was found in any module directory.

This particular issue I was able to resolve. I went and installed Active Directory Module Feature on the server thus removing the Import-Module- Active Directory error.

Even though I already had Microsoft.SQLServer.BatchParser assembly installed on the client desktop, I had read a few posts on StackOverflow how the reinstalled SSMS and it worked. So I re-installed SSMS 2014 with SP 2 and it still gave me the same error. My Processor Architecture was x86. So I also installed SQL Server 2014 SMO for 64-bit on my client issued desktop. I have been able to connect to the server and got the scripts running.


Calling two stored procedures from one, is the order guaranteed? I have some code that looks like this:

    EXEC StoredProcedure1 @Arguments

    EXEC StoredProcedure2 @Arguments

Are the two datasets guaranteed to return in the called order? I am expecting two result sets to be passed back to my server code and am running into some issues and I feel the ordering may not be guaranteed. If it is not guaranteed, is there a way to enforce ordering?

Not the ordering of the data, but ordering of StoredProcedure1 always being returned as result set 1, and StoredProcedure2 always being returned as result set 2.

Yes.  StoredProcedure1 will execute first, and any resultsets from StoredProcedure1 must be consumed by the client before StoredProcedure2 is executed.

It's possible that some client driver or DAL processes all the resultsets and then returns them to your calling code in a different order, but for SQL Server and the Microsoft client drivers, you will get the resultsets in the order in which they are produced.


Programmatically find indexes that cannot be rebuilt online I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included.

Is there a way to programmatically identify them as part of this statement? So kind of addition to the WHERE predicate

SELECT
--TOP 20
OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent, SI.name         [IndexName],
schema_name(ST.schema_id) AS [SchemaName], 0 AS IsProcessed
INTO #FramentedTableList
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS
JOIN sys.tables ST WITH (nolock) ON IPS.OBJECT_ID = ST.OBJECT_ID
JOIN sys.indexes SI WITH (nolock) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id =   SI.index_id
WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL
AND avg_fragmentation_in_percent >= CONVERT(DECIMAL,   @FragmentationThresholdForReorganizeTableLowerLimit)
ORDER BY avg_fragmentation_in_percent DESC

I use the following to detect LOBs:

SET @LOBCount = (
    SELECT COUNT(*) FROM (
        select t.name as TableName, i.name as IndexName, c.name as ColumnName, ty.name, ic.is_included_column
            from [' + @DatabaseName + '].sys.indexes i
            INNER JOIN [' + @DatabaseName + '].sys.tables t ON t.object_id = i.object_id
            inner join [' + @DatabaseName + '].sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
            inner join [' + @DatabaseName + '].sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
            INNER JOIN [' + @DatabaseName + '].sys.types ty ON c.system_type_id = ty.system_type_id
            where
                t.name = ''' + @TableName + '''
                AND i.name = ''' + @IndexName + '''
                AND (
                    ty.name IN (''text'', ''ntext'', ''image'')
                    or
                    (
                        ty.name IN (''varchar'', ''nvarchar'', ''varbinary'', ''xml'')
                        AND c.max_length = -1
                    )
                )
        ) as x
    )';

Obviously, this is a code fragment from a much larger stored proc that we use to rebuild and/or reorganize indexes.  I would recommend you look at Ola Hallengren's scripts at:  http://ola.hallengren.com/


join vs virtual table in query performance Suppose I have two tables linked by a foreign-key

example DDL's

CREATE TABLE raw_collection (
    info_datetime DATETIME NOT NULL,
    asset_id INTEGER NOT NULL,
    price FLOAT,
    PRIMARY KEY (info_datetime, asset_id),
    FOREIGN KEY(asset_id) REFERENCES assets (id)
);

CREATE TABLE assets (
    id INTEGER NOT NULL,
    asset_symbol VARCHAR,
    PRIMARY KEY (id)
);

when filtering by asset_symbol on the raw_collection tables, what (and why) would be a faster solution. using a join or creating an in memory table from the asset_symbol table and filtering raw_collection according to it?

the queries

-- query 1
with needed_assets as (
  select id
  from assets
  where asset_symbol in ('a', 'b'))
select *
from raw_collection
where asset_id in (select id from needed_assets) ;
-- where asset_id in (table needed_assets) ;  -- Postgres only syntax
-- where asset_id in needed_assets ;          -- SQLite only syntax

-- query 2
select r.*
from raw_collection r join assets a on r.asset_id = a.id
where a.asset_symbol in  ('a', 'b') ;

I am either using PostgreSQL 11 or SQLite 3.28.0

It depends on a number of things. You list postgres in one of your tags, so it should be noted that the CTE will be materialised in full as CTEs are an optimisation fence that block predicate push-down. I'm not sure about sqlite, the other DB you list in the tags, but this is not the case for all databases, SQL Server for one can optimise across CTEs.

So for postgres currently†, all other things being equal and appropriate indexes being present, I would expect query 2 to be faster if there is enough data in assets matching asset_symbol in ('a', 'b')) for the difference to be noticeable.

In either case what indexes you have defined will also make a difference. Two things to note in particular are that marking raw_collection.asset_id as a foreign key does not  automatically create an index for that column which might limit the query planner's choices, and if you don't have one already then an index covering assets.asset_symbol is likely to be useful unless that table has very few rows.

† Note: this is due to change in version 12, which is slated for release soon, though as you state you are using v11 this is not relevant to you yet


Initializing Associative Array in PL/SQL Using PL/SQL, suppose we've declared a nested table type and its instance as follows:

  type nested_tab is table of pls_integer;
  tab nested_tab;.

We are then able to initialize our nested table as follows:

  tab := nested_tab(1, 2, 3);.

Is there something similar we can do to initialize an associative array?

In other words, for the following associate array:

  type associative_tab is table of varchar2(100) index by pls_integer;
  a_tab associative_tab;

is there a shorter way than this:

  a_tab(1) := 'hello';
  a_tab(2) := 'world';
  ...
  a_tab(100) := '100th string';

for intialization?

Updated answer for 18c and beyond
In 18c Oracle has introduced qualified expressions that can also be used to initialize an associative array. Example from the documentation:
DECLARE
  TYPE t_aa IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
  v_aa1 t_aa := t_aa(1=>FALSE,
                     2=>TRUE,
                     3=>NULL);
BEGIN
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(1)));
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(2)));
  DBMS_OUTPUT.PUT_LINE(print_bool(v_aa1(3)));
END;

See also chapter Qualified Expressions for Associative Arrays from Easy Initializing for Records and Arrays by Steven Feuerstein
Original answer upto 12c
No - there is no a short-cut syntax to initialize an associative array. Unlike varrays and nested tables associative arrays do not have collection constructors.
Instead you should write your own initializer function. See an example from Declaring Associative Array Constants.


Query Simplification for better execution Select a.item_no,
       a.item_type,
       a.item_group,
       a.uom_group,
       a.unit_price,
       c.service_category,
       c.item_no,
       b.manufacturer,
       b.issue_method,
       b.item_no,
       d.preferred_vender,
       d.mfr_catlog_no,
       d.purchasing_uom,
       d.items_per_purchase_unit,
       d.quantity_per_package,
       d.item_no,
       e.sales_uom_name,
       e.item_per_sales_unit,
       e.package_uom_name,
       e.quanti_per_package,
       e.item_no
  from Item_Master_Details a,
       Item_Master_General_Material b,
       Item_Master_General_Service c,
       Item_Master_Purchasing_Data d,
       Item_Master_Sales_Data e
 where a.item_no = b.item_no
   and b.item_no = c.item_no
   and c.item_no = d.item_no
   and d.item_no = e.item_no;

I want to simplify this query..because it gets more time to execute in database.

Other than changing your query so that you are using a JOIN when referencing your tables there is not much that can be done to simplify the query itself as it is pretty basic. Here is your query rewritten to use a JOIN:

Select a.item_no,
       a.item_type,
       a.item_group,
       a.uom_group,
       a.unit_price,
       c.service_category,
       c.item_no,
       b.manufacturer,
       b.issue_method,
       b.item_no,
       d.preferred_vender,
       d.mfr_catlog_no,
       d.purchasing_uom,
       d.items_per_purchase_unit,
       d.quantity_per_package,
       d.item_no,
       e.sales_uom_name,
       e.item_per_sales_unit,
       e.package_uom_name,
       e.quanti_per_package,
       e.item_no
  from Item_Master_Details a
  join Item_Master_General_Material b
    on a.item_no = b.item_no
  join Item_Master_General_Service c
    on b.item_no = c.item_no
  join Item_Master_Purchasing_Data d
    on c.item_no = d.item_no
  join Item_Master_Sales_Data e
    on d.item_no = e.item_no;

If it is still running slow then you might want to consider an indexing strategy, materialized views or even creating a de-normalized table to contain the data that you require.


processlist showing '%' as host from trigger I'm trying to populate a certain column in a table with the host/IP of the caller. I have an insert trigger on the table which sets the column like so:

CREATE TRIGGER access_insert_trg BEFORE INSERT ON access
FOR EACH ROW
set NEW.hostname = (select SUBSTRING_INDEX(host,':',1)
                    from information_schema.processlist
                    WHERE ID=connection_id())
;

I end up getting % as the hostname even though when I manually run the same query on the processlist table I get an actual hostname. I've checked the connection_id() value and it's correctly the id of a row in processlist with a real hostname:port value in the host column.

I can't figure out how or why it's being translated to a generic %:<port> value. Any ideas on what's going on & how to fix it?

I'm using MySQL 5.1.57 if that changes anything.

Are you doing the insert that fires the trigger from inside a stored procedure?

Apparently, when inside a stored procedure, the row matching your thread in information_schema.processlist is populated (albeit somewhat half-heartedly) with the DEFINER username and hostname if the calling user is not the same as the definer.

This is somewhat unexpected, so I'll document it a bit more, below...

But first, your fix:

SET NEW.hostname = SUBSTRING_INDEX(user(),'@',-1);

If this works, it's definitely a better approach -- it's a much more lightweight way to get the information you're looking for, and seems to return the correct answer where the other doesn't.  Making the call to information_schema.processlist is expensive, since apparently the entire table is rendered each time you call for it.

Now, to replicate the behavior for the benefit of those who (me included) whouldn't have believed it without seeing it in action:

DELIMITER $$

DROP PROCEDURE IF EXISTS `bizarre` $$
CREATE DEFINER=`super_dev`@`%` PROCEDURE `bizarre`()
BEGIN

SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID();

END $$

DELIMITER ;

Now to test the SP.  Note that I am not connecting as super_dev, I am connecting as a different super user.  If I connect as super_dev (the DEFINER user), this works the way we expect.

mysql> call bizarre();
+-------+---------------+---------+------------+---------+------+-----------+-------------------------------------------------------------------------+
| ID    | USER          | HOST    | DB         | COMMAND | TIME | STATE     | INFO                                                                    |
+-------+---------------+---------+------------+---------+------+-----------+-------------------------------------------------------------------------+
| 17261 | super_dev     | %:49730 | dev_testsv | Query   |    0 | executing | SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID() |
+-------+---------------+---------+------------+---------+------+-----------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

My best guess, this is an artifact of the environment changes that need to happen internally when a stored procedure is called.


Multiple counts in one table output should be one table I want to create the following mysql query to put multiple count run on same table and output should be in one table only using as name.

select
    count(select count(TXN)
          from sale1
          where date(SALEDATE) = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))
         ) as c1,
    count(select count(TXN)
          from sale1
          where RESPONSE='Y'
            AND date(SALEDATE) = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))
         ) as c2
FROM sale1;

Syntax error, remove the ( and ) from the parent query, do away with the outer FROM and the extra count's. That is,

SELECT
   (SELECT count(TXN)
        FROM sale1
        WHERE DATE(SALEDATE)=DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))
   ) AS c1,
   (SELECT count(TXN)
        FROM sale1
        WHERE RESPONSE='Y'
        AND DATE(SALEDATE)=DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))
   ) AS c2;

Note count of count i.e. count(count(*)) will normally give you 1.


Should I assimilate all address lines to a single line? I'm new here, so I'll keep my question straight and short. I am building a webapp that lets users select some product, submit their details and place an order.

Currently, my ORDER table contains only one field for the address. I was planning to write code to assimilate all address lines to one, including the state, city, etc. I will then add functions to retrieve the address field and parse it for state, city, etc.

Is this a good approach, or will the database be faster if there is seperate field for states, cities and the like?

Separate fields. Every time. String operations are costly, especially cutting them up into bits.


SQL Server 2012 ALTER Index Job failing We setup ALTER Index job with Ola's maintenance solution, the job is failing with below error, the job is failing with locking,

Here are a few possible solutions to your problem:

Option 1: Don't rebuild that index

That's a 30 GB index you have there.  What measurable performance problem are you trying to solve by rebuilding it?  Especially at 5% fragmentation, this seems like an incredibly expensive operation (in terms of system resources and locking) for very little gain.

You can read some very well-founded opinions on why you might want to give up on the index rebuild here:

Stop Worrying About SQL Server Fragmentation
Good Reasons to Rebuild or Reorganize Indexes
Video: Why Defragmenting Your Indexes Isn’t Helping

This is far and away your best, and EASIEST, option.  This is the home run.  Do this.

Option 2: Rebuild online

Index rebuilds require a SCH-M lock.  If you add WITH (ONLINE = ON) to your ALTER INDEX command, that lock will be deferred until the very end of the rebuild operation, which might increase the potential that whatever is preventing your maintenance task from completing has released its locks.

Option 3: Identify the blocking query

This is probably the most work of the options.  You can run sp_WhoIsActive while the ALTER INDEX command is running, and it should show you what else is running, and specifically it will show you what other session is blocking the ALTER INDEX command from acquiring its lock.  At that point, you have a bunch of options to deal with the problem:

reschedule your index maintenance so it's not running at the same time as the blocking query
re-work your blocking query so that it takes less locks / different locks / holds locks for less time (you could post another question with execution plan details if you need help with that)
reschedule the blocking query so that it is not running during the period when you do index maintenance


How can I remove a bad execution plan from Azure SQL Database? DBCC FREEPROCCACHE doesn't work in Azure SQL DB. How else can I force a plan to kick itself out of the cache in a way that won't hurt a production system (i.e. I can't just go alter tables willy nilly)? This is specifically for SQL created by Entity Framework, so these aren't self-managed stored procs - it's effectively dynamic SQL.

(Source was bad indexes -> bad stats, etc. That's all fixed, but a bad plan won't go away.)

UPDATE:
I selected @mrdenny's solution as he got there first. I am, however, successfully using @Aaron Bertrand's script to perform the work. Thanks to everybody for the help!!

Azure SQL now directly supports this

Azure SQL Database directly supports clearing the proc cache of the current user database without any hacks:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Additional Information

The following script (by Shannon Gowen) can be used to watch the process step-by-step:

-- run this script against a user database, not master
-- count number of plans currently in cache
select count(*) from sys.dm_exec_cached_plans;

-- Executing this statement will clear the procedure cache in the current database, which means that all queries will have to recompile.
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

-- count number of plans in cache now, after they were cleared from cache
select count(*) from sys.dm_exec_cached_plans;

-- list available plans
select * from sys.dm_exec_cached_plans;


How to Retrieve Credit Debit and Balance From More Than One Table in SQL Server I have more than 1 table such as INVOICE, PAYMENT, RETURN_INVOICE, RETURN_PAYMENT, etc. i want to retrieve  data CREDIT, DEBIT AND BALANCE each customer wise

below are tables and insert query statements,

CREATE TABLE Customer
(
    CustomerId INT IDENTITY(1,1),
    RegistrationDate DATE,
    CustomerName VARCHAR(45),
    OpeningBalance MONEY,
    PRIMARY KEY(CustomerId)
)
INSERT INTO Customer (CustomerName, RegistrationDate, OpeningBalance) VAlUES ('JOHN', '2020-01-15', 1000)

CREATE TABLE Invoice
(
    InvoiceId INT IDENTITY(1,1),
    InvoiceDate DATE,
    CustomerId INT,
    InvoiceTotal MONEY,
    PRIMARY KEY(InvoiceId)
)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-16', 1, 2000)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-17', 1, 500)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-17', 1, 250)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-20', 1, 1000)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-22', 1, 2250)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-24', 1, 1750)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-28', 1, 3000)

CREATE TABLE Payment
(
    PaymentId INT IDENTITY(1,1),
    PaymentDate DATE,
    CustomerId INT,
    PaymentTotal MONEY,
    PRIMARY KEY(PaymentId)
)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-18', 1, 750)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-20', 1, 2000)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-23', 1, 5000)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-26', 1, 200)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-28', 1, 500)

CREATE TABLE ReturnInvoice
(
    ReturnInvoiceId INT IDENTITY(1,1),
    ReturnInvoiceDate DATE,
    CustomerId INT,
    ReturnInvoiceTotal MONEY,
    PRIMARY KEY(ReturnInvoiceId)
)
INSERT INTO ReturnInvoice (ReturnInvoiceDate, CustomerId, ReturnInvoiceTotal) VAlUES ('2020-01-25', 1, 500)
INSERT INTO ReturnInvoice (ReturnInvoiceDate, CustomerId, ReturnInvoiceTotal) VAlUES ('2020-01-28', 1, 300)
INSERT INTO ReturnInvoice (ReturnInvoiceDate, CustomerId, ReturnInvoiceTotal) VAlUES ('2020-01-29', 1, 1000)

CREATE TABLE ReturnPayment
(
    ReturnPaymentId INT IDENTITY(1,1),
    ReturnPaymentDate DATE,
    CustomerId INT,
    ReturnPaymentTotal MONEY,
    PRIMARY KEY(ReturnPaymentId)
)
INSERT INTO ReturnPayment (ReturnPaymentDate, CustomerId, ReturnPaymentTotal) VAlUES ('2020-01-21', 1, 500)
INSERT INTO ReturnPayment (ReturnPaymentDate, CustomerId, ReturnPaymentTotal) VAlUES ('2020-01-27', 1, 2000)

I want to retrieve the output like this,

All the table should be inner join to customer (CustomerId Is Foreign Key)
Need to retrieve extra column Description and mention the table transaction name and No. (3rd column in the picture above)
Customer OPENING BALANCE should come on top Row that is from Customer Table
Ordered by date and its no under it.

How to get the Expected output on above picture.

I believe this will do what you need. I appreciate you making it easy to write by providing a solid MVCE.

We have 5 queries in UNION ALL to generate our data set, and one outer query to generate the running total. The reason we have 5 queries in the union is that we are using one for the Opening Balance, and the other 4 for each transaction type.

I cheated a bit with the running calculation using ORDER BY Date, Description in the balance. Because we generated the number inside Description. There is surely a cleaner way to do that. If you need to order by a specific type you could add an additional column to the union queries and code them (1, 2, 3, 4, 5) and then order by that column in the outer query.

However, this query does get you to the goal line:

SELECT
  CustomerName,
  [Date],
  Description,
  ABS(Debit) as Debit,
  ABS(Credit) as Credit,
  SUM(CASE WHEN Credit = 0 THEN DEBIT ELSE Credit END) OVER (PARTITION BY CustomerId ORDER BY [Date], Description)  as Balance
FROM

(
    SELECT
      CustomerId,
      CustomerName,
      RegistrationDate as [Date],
      'OPENING BALANCE' as Description,
      OpeningBalance as Debit,
      0 as Credit,
      OpeningBalance as Balance
    FROM Customer

UNION ALL

    SELECT
      c.CustomerId,
      CustomerName,
      InvoiceDate as [Date],
      'Invoice No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, InvoiceDate)) AS VARCHAR(10)) AS Description,
      InvoiceTotal as Debit,
      0 as Credit,
      OpeningBalance
    FROM Customer c
    JOIN Invoice i ON c.CustomerId = i.CustomerId

UNION ALL

    SELECT
      c.CustomerId,
      CustomerName,
      PaymentDate,
      'Payment No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, PaymentDate)) AS VARCHAR(10)) AS Description,
      0,
      -1.0 * PaymentTotal,
      OpeningBalance
    FROM Customer c
    JOIN Payment i ON c.CustomerId = i.CustomerId

UNION ALL

    SELECT
      c.CustomerId,
      CustomerName,
      ReturnInvoiceDate,
      'ReturnInvoice No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, ReturnInvoiceDate)) AS VARCHAR(10)) AS Description,
      0,
      -1.0 * ReturnInvoiceTotal,
      OpeningBalance
    FROM Customer c
    JOIN ReturnInvoice i ON c.CustomerId = i.CustomerId

UNION ALL

    SELECT
      c.CustomerId,
      CustomerName,
      ReturnPaymentDate,
      'Return Payment No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, ReturnPaymentDate)) AS VARCHAR(10)) AS Description,
      ReturnPaymentTotal,
      0,
      OpeningBalance
    FROM Customer c
    JOIN ReturnPayment i ON c.CustomerId = i.CustomerId
) u

ORDER BY [Date]

Here is a SQLFiddle


how often to run reorganize index & update statistic maintenance task on sql server 2012? I know this is a hard question to answer without more details, but I am hoping for some general guidelines or rule of thumb ideas, or perhaps a how-to determine what would be best for my set of databases.

I have a system of about 100 databases, that since its creation 8 years ago, i did index reorg, stats update, check integrity task on all databases, every night. most databases are about 1GB in size, one is 10GB

As the databases grew, this task takes more and more time, and now has adverse effects on performance, and there is no good time when I can handle the performance effect.

I am afraid of just turning this task off, and I am looking for some guidance - I don't even know if doing such maintenance task is necessary!  Should I be doing such maintenance job? how often?

Thank you!

You should intelligently do index reorg and rebuilds. As a general (widely accepted) rule of thumb is

If your indexes are fragmented :

If index has less than 1000 pages - do not perform any index maintenance operation.
Less than 10% - then do nothing
between 10% and 30% - then do a reorg
more than 30% - then rebuild them.

I would highly recommend to use Ola's maintenance solution (index and checkdb)

Since your databases are 1GB to max 10GB, depending on your server spec it should not take very long time.


Can anyone explain TokuDBs seemingly large amount of data_free for an append only table? I have a large table I've been maintaining replicating in parallel in innodb in one slave and tokudb in another.

I'm seeing promising results in expected compression ratios but am noticing something very strange about the data_free reported from information_schema.tables for the tokudb copy.

This is an append only table.  No updates or deletes.  I'll let the following table explain what I'm seeing

engine  data_length(GB) index_length(GB) data_free(MB)  Size on disk (GB)
InnoDB  728.40          195.63           61.00          939
TokuDB  453.42          119.07           27507.60       253

Can someone explain why the data_free is magnitudes larger than the innodb version.   Is data_free for tokudb expected to mean the same thing as it does for innodb?

I've seen tokutek guys explain elsewhere that table status figures such as data_length etc are basically rough estimates by the tokudb engine, and they are sometimes off by large amounts, so I wouldn't read too much into it.


Splitting a DB into multiple files I'm managing a 1.5Tb DB that is growing at a rate of ~37Gb/Week. I have been asked to split the DB into 500Gb files, but I cannot use Filegroups due to restrictions from the vendor.

My thinking was to create 3 devices and use the DBCC SHRINKFILE command to equally spread the DB across the devices. Could anyone comment on best practices? Any pit falls to my process?

You really do not have much choice because how proportional fill works in SQL Server.

Proportional fill and how to check whether it works

I am assuming you currently have one file in primary filegroup.

You can rebuild objects with clustered index and those will go to
new files totally because of more free space in newly created
tables.  As your current file start freeing up space you might not be able to move to new files any more (depending on free space in each file).
With DBCC SHRINKFILE only way this can work is with EMPTYFILE
option because you cannot shrink smaller than your current used
space. If you try to shrink with smaller size it will not work.
Once you shrink with EMPTYFILE option current file will only hold
some system objects and rest will be split over to your 3 new files.

Next problem will be subsequent data inserts will use the old file (currently emptied) because of proportional fill algorithm.

Here is an article by Paul Randal on the same topic.


Understanding data length How does the LENGTH() query relate to actual size of the data?
I notice that when I save this data to a text file, it's approximately 2% larger than the LENGTH() query.  What is happening here?  Here's an example on linux using MariaDB 10.3.16.
du and ls both agree`.
$ mysql -ufugu -p projects -e "SELECT ID, LENGTH(CutXML) as length FROM renders ORDER BY length DESC LIMIT 1"
Enter password:
+-----+---------+
| ID  | length  |
+-----+---------+
| 350 | 3431670 |
+-----+---------+
$ mysql -ufugu -p projects -e "SELECT CutXML FROM renders WHERE ID=350" > cut.xml
Enter password:
$ ls -al cut.xml
-rw-r--r--. 1 root root 3512937 Aug 20 14:23 cut.xml
$ du -b cut.xml
3512937 cut.xml

Only 2%?
Check the line terminators.  For the column, do something like SELECT HEX(LEFT(COL(100))).  For the file, do something like od or hd or take the cat to the vet (cat -vet).
A Rule of Thumb is that an average line of "text" is 30 characters, which would lead to about a 3% difference.  XML is rather verbose, and your data may be averaging 50 bytes per "line".


Attach 110(2012) from SQL Server 2014 Instance to a (110) SQL Server 2012 INSTANCE I have a SQL Server database on a 2014 instance with compatibility set to 110. When I try to attach it to a SQL Server 2012 instance it says cannot attach version 782 to 706.

I thought I could attach a compatibility of 110 from a SQL Server 2014 to a SQL Server 2012 since in the 2014 the compatibility to 110

Any help would be appreciated.

You can't do this. Compatibility mode makes no difference here. You simply can't go back to 2012 from 2014 via backup/restore or detach/attach.

Your options are limited to either generating scripts to recreate the schema and objects or using a third party tool. Red gate have one but I've never used it.