Search for values in xml data I have a table of names:

Table A

John
Jim
Jason

and a table of xml strings:

Table B

Example 1

    <Show Title="" ShowTitle="=False" ShowLine="=False" ShowDescription="=False" ShowExpandCollapse="=False" IsVisible="=True" Description="" PageBreakAfter="=False" PageCaption="" AppLink="" InfoLink="" ImageLink="" Pause="=False" PauseMessage="" PauseMessageStyle="" PauseTitle="" ScreenStyle="">
  <ShowOption Sequence="1" Name="Jim" Caption="Test" SelectOptionsImageLinkFieldExpression="ImageLink" />
</Show>

Example 2

<vars>
  <var name="MatrixName">
    <value>="LockExitPairCompatability"</value>
  </var>
  <var name="Jason">
    <value>=If(Exists(Minute.Value.Min), ToNumber(Minute.Value.Min), 0)</value>
  </var>
  <var name="MinNum">
    <value>=If(Exists(Agency) AND Agency = "Cert", 0, MinNum)</value>
  </var>
  <var name="Where">
    <value>="Site='" + Root.Site + "' AND HWType IN " + ToSQLArray(Root.Components[ActiveDoor].HardwareType)</value>
  </var>
  <var name="Where2">
    <value>=" AND HWSubType IN " + ToSQLArray(Root.Components[ActiveDoor].LockSubType)</value>
  </var>
  <var name="CompatibleList">
    <value>=usr.FetchMatrix</value>
  </var>
</vars>

Example 3

<rule>
  <property name="CollectionVariable" DisplayName="Collection Variable" ValueType="RValueExpression">John</property>
  <property name="KeyVariable" DisplayName="Key Variable" ValueType="LValueExpression">NextLock</property>
</rule>

How can I search to find which names in Table A aren't in any of the xml strings?
The data shown are just examples. The xml in the database is of all different formats.

In the xml data it might be "Root.Name". I would still want it to find it even with the "Root." attached to it.

I assume you want to look in all attributes and all elements.

Use the function exist against the XML column in table B with a predicate to check for the existence of values from Table A. The function sql:column is used to get values from A into the XQuery expression.

declare @A table(Name varchar(50));
insert into @A(Name) values('John'),('Jim'),('Jason'),('Mike');

declare @B table(XMLCol xml);

insert into @B(XMLCol) values
('<Show Title="" ShowTitle="=False" ShowLine="=False" ShowDescription="=False" ShowExpandCollapse="=False" IsVisible="=True" Description="" PageBreakAfter="=False" PageCaption="" AppLink="" InfoLink="" ImageLink="" Pause="=False" PauseMessage="" PauseMessageStyle="" PauseTitle="" ScreenStyle="">
  <ShowOption Sequence="1" Name="Jim" Caption="Test" SelectOptionsImageLinkFieldExpression="ImageLink" />
</Show>'),
('<vars>
  <var name="MatrixName"><value>="LockExitPairCompatability"</value></var>
  <var name="Jason"><value>=If(Exists(Minute.Value.Min), ToNumber(Minute.Value.Min), 0)</value></var>
  <var name="MinNum"><value>=If(Exists(Agency) AND Agency = "Cert", 0, MinNum)</value></var>
  <var name="Where"><value>="Site=''" + Root.Site + "'' AND HWType IN " + ToSQLArray(Root.Components[ActiveDoor].HardwareType)</value></var>
  <var name="Where2"><value>=" AND HWSubType IN " + ToSQLArray(Root.Components[ActiveDoor].LockSubType)</value></var>
  <var name="CompatibleList"><value>=usr.FetchMatrix</value></var>
</vars>'),
('<rule>
  <property name="CollectionVariable" DisplayName="Collection Variable" ValueType="RValueExpression">John</property>
  <property name="KeyVariable" DisplayName="Key Variable" ValueType="LValueExpression">NextLock</property>
</rule>');

select A.Name
from @A as A
where not exists (
                 select *
                 from @B as B
                 where B.XMLCol.exist('(//@*, //text())[. = sql:column("A.Name")]') = 1
                 );

// searches all descendants.

@ specifies that you are looking for attributes.

* is a wild card for the attribute name.

//text() gives you all descendant text values (not attributes).

(//@*, //text()) combines the attributes with the text values

[. = sql:column("A.Name")] a predicate that checks the current value against A.Name.

If you want to check if a value contains A.Name you should use the contains function.

select A.Name
from @A as A
where not exists (
                 select *
                 from @B as B
                 where B.XMLCol.exist('(//@*, //text())[contains(., sql:column("A.Name"))]') = 1
                 );

If need a case-insensitive comparison, lower-case both parameters to contains.


How to find exact string from a sentence I'm using SQLITE and I'm trying to find the exact word on a sentence, but I'm not getting just the word I'm getting for instance if I'm looking for Water I'm getting also aWater, wateri24 and that's not what I'm looking for.

I've created an example here on sqliteonline to show you how do I store the sentences, there are in table b.

This is an example of what is happening

NOTE: I'm looking for an answer that uses ignore cases, for instance if I want to find water I want to get wAtER and WatER, and so...

What I'm missing?

You could do something like this:

When considering that a word like water can only be surrounded by spaces, comma's and square brackets.

SELECT * FROM b WHERE list LIKE '%[water,%'
OR list LIKE '%, water,%'
OR list LIKE '%, water]%';

And you could technically remove some of the wildcards:

SELECT * FROM b WHERE list LIKE '[water,%'
OR list LIKE '%, water,%'
OR list LIKE '%, water]';

SQLliteOnline example #1

SQLliteOnline example #2

Both returning:

  NOTE: I'm looking for an answer that uses ignore cases, for instance
  if I want to find water I want to get wAtER and WatER, and so...

Like operator should be case sensitive.

  Note that SQLite LIKE operator is case-insensitive. It means "A" LIKE
  "a" is true. However, for Unicode characters that are not in the ASCII
  range, SQLite LIKE operator is case sensitive e.g., "Ä" LIKE "ä" is
  false.

Source

If it wasn't, COLLATE NOCASE could be used
More on that here


Slow running Oracle query caused by unnecessary full table scan I'm by no means an expert with Oracle, but I know enough in terms of how to analyze explain plans and perform basic optimizations. However, this time, Oracle has stumped me with it's explain plan.

Basically, I've got a single table that is very large (27 million rows) with an index on ScheduleID. What I'm trying to do is get all the records that have a ScheduleID that match one of the ScheduleIDs in a given list (currently stored in a temp table). The temp table will probably have around 100 rows, although it could be up to 500 rows. And for each row in temp row, it will have up to around 10 matching records in the large table. So we are talking about the query returning about 5000 rows max (out of 27 million).

Now, for some reason, Oracle thinks that the best solution is to use a hash join and do a full table scan of the large table. You can see the query and its explain plan that Oracle generates below.

select scheduleintervalid, io_scheduleinterval.version
from io_scheduleinterval
inner join io_querytemp tmp on tmp.data = scheduleid

This doesn't seem to make any sense to me. It is not taking advantage of the index on ScheduleID, which I would think would make the query significantly faster. Now from my understanding, the hash join couldn't take advantage of the index, but wouldn't a nested loop join be much faster. Am I missing something here?

Is there something else I can do to speed up the query (I've tried things like using a dynamic IN clause)?

Thanks for the help!

Answer left as comments by the question author

I think I may have found the issue, and it definitely makes sense. The type of the data column on the temp table was nvarchar2(100) while the type of the scheduleid column was char(22).

So apparently with the different types, the index cannot be used. When I added a to_char to the data column, it was able to finally use the index.

This temp table is also used in some other joins, where the data is of type varchar(100). In hindsight, maybe I could have had multiple columns on the temp table for different types, but what I have now works.


Dual management node for mysql clustering I currently have mysql cluster setup and running.
Here is my current setup:
1 - management node
2 - data nodes
2 - mysql nodes

I wish to incorporate a failover for the management node. The management node seems to be the bottleneck in my current setup. If the management node fails, my entire cluster is down. How can I incorporate a dual management node settings? Is it possible to have a 2nd management node? Must I also increase my data node and mysql node to accommodate the 2nd management node. Please assist.

This is what I have in my config.ini now: The 2 data nodes is together with my management node. I have separate out the mysql nodes.

[ndb_mgmd]
hostname=192.168.0.1
datadir=c:\Users\user1\my_cluster\ndb_data
NodeId=1
[ndbd default]
noofreplicas=2
datadir=c:\Users\user1\my_cluster\ndb_data
[ndbd]
hostname=192.168.0.1
NodeId=3
[ndbd]
hostname=192.168.0.1
NodeId=4
[mysqld]
hostname=192.168.0.2
NodeId=5
[mysqld]
hostname=192.168.0.3
nodeId=6

Hmm, the two data nodes on one server means that if you lose 192.168.0.1 (node 3/node 4) you lose the whole cluster (possibly why you are experiencing the failure).

In fact the fact that you have effectively a single data node (unless I'm missing something) means you don't really have a cluster - you only have one copy of data.


How to Create a PostgreSQL database with different Encoding? I want to create a New Database that will store data in German(i.e Text with accents etc). Hence I tried to create it using the following SQL:

CREATE DATABASE testdb
  WITH ENCODING='LATIN1'
       OWNER=postgres
       TEMPLATE=template0
       CONNECTION LIMIT=-1;

When I run this on my Windows Development machine, I get the following error message:

  ERROR:  encoding "LATIN1" does not match locale "English_United
  States.1252"
  DETAIL:  The chosen LC_CTYPE setting requires encoding "WIN1252".

On my Linux server, I get the following error message:

  ERROR:  encoding "LATIN1" does not match locale "en_US.UTF-8"
  DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".

What do I need to do to create a Database which can correctly store German text?

On Linux,
I was able to create a database with encoding LATIN1 by 1st initializing the database using initdb in \usr\pgsql-10\bin as initdb --encoding=en_US.iso88591. Read more about localization and available character set support.

Then restart the service using-

systemctl restart postgresql-10

This will re-initialize the template databases template0 and template1 with the new encoding and then you can create the database using

createdb <dbname>

Hope this helps.


SQL Server connectivity without specifying tcp/port Having connection issues out of nowhere in my lab (Azure VMs):

SQL 2016, sp1, cu4 all the way around, Windows 2016 Datacenter

Windows Failover cluster for SQL AG testing.

SQLServer-0:

Default instance (port 1431)

Inst1 (port 1499)

SQLServer-1:

Default instance (port 1431)

Inst1 (port 1499)

From SQLServer-0, I can only connect to the remote instance(s) using tcp:SQLServer-1, 1431 (or tcp:SQlServer-1.kevinsdomain.com,1431).

Same thing for the named instance.

Same thing from SQLServer-1 to SQLServer-0.

Ping of just the NetBIOS name works just fine, ping -a resolves the FQDN.

None of these are running on port 1433, both servers are running SQL Browser under NT Authority\Local Service.

This is my test domain, along with a domain controller, DNS, etc.

Windows firewall on each has all of the ports open that are needed, including UDP 1434.

I'm sure there's something simple I've forgotten to check, flush, whatever.  Just can't think of it.

Just to clarify your question... you wish to connect to the instance on either server without the need to specify the Port Number?

In that case, did you check if the instance name is added into your SQL configuration manager?

Open SQL Configuration Manager
Expand "SQL Native Client Configuration"
Select "Aliases"
See that if the instance that you wish to connect exists, if no, create the alias for the instance

Hope I do answer your question...


Redundant entries in an IN clause I have a dynamically built query with a where clause that looks like this:
where column in ('a', 'a', 'a', 'b')
The actual data is a LOT longer with a lot of duplicates, as in the example above where a is repeated three times.
Is there a potential performance hit for a very large WHERE clause using an IN with a large number of values, many of which are duplicates?

This is pretty easy to test.
USE tempdb;

CREATE TABLE dbo.d
(
    col varchar(1)
);

INSERT INTO dbo.d (col)
VALUES ('a')
    , ('b');
GO

Enable "actual" execution plans, and run this:
SELECT *
FROM dbo.d
WHERE d.col IN ('a', 'b', 'a', 'b', 'c');

The results pretty clearly show that SQL Server eliminates duplicate items from the IN (...) clause before executing the query.

Be aware, if the IN (...) contains a sufficiently large number of duplicated items, the query may perform more poorly than if those duplicates weren't present.  Causes for the poor performance might include:

The client-end might have difficulty compiling the list of items for the IN clause.
The time spent by the query optimizer will obviously increase somewhat with more items in the IN clause.
If the query is being sent over the network, a long list of items in the IN clause may result in higher latency if multiple TCP packets need to be compiled, sent, and received.

The items listed above will be compounded if the query is being sent to the server very frequently.
If the list is big enough, the size of the text alone can cause performance issues in other ways, such as compilation time (just parsing the thing) and sending the query over the wire.  So just because the execution plan doesn't show it, doesn't mean those additional values can't have an effect on performance.


How to turn Event Scheduler On permanently in MySQL? How do I turn on the event scheduler in mysqld permanently? I'm using phpMyAdmin and I have scheduled an event to copy records from one table to another everyday at a specific time, but the records are not being copied everyday. I've tried setting the event scheduler on.

SET GLOBAL event_scheduler="ON";

When I restart my pc, the event scheduler is turned off. How do I make it be always on, and not turned off when mysqld is restarted?

This is quite an easy one really (one of those "Doh"  moments :-) ).

You set it in my.cnf as described here:

[mysqld]
..
.. other_stuff
..
event_scheduler=on
..
.. more_stuff
..

(or my.ini if you're running Windows as pointed out below)

From the MySQL documentation here, you have:

  When the Event Scheduler is ON, the event scheduler thread is listed
  in the output of SHOW PROCESSLIST as a daemon process, and its state
  is represented as shown here:

A daemon process is one that runs in the background as described here. Also known as a service in Windows

So, run:

mysql> SHOW PROCESSLIST\G

and there should be an entry like this:

*************************** 2. row ***************************
     Id: 2
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon      <<<<<====== Note: Daemon!!!
   Time: 3
  State: Waiting for next activation
   Info: NULL
2 rows in set (0.00 sec)


SQL-Sum Over Partition By Issue I wrote a statement similar to this one. I am new to SQL and wondering how I can or if I can use SUM(___)over(partition by ___)  to sum all the NonUsableTotal parts inventory for that PartID.

The issue is that f.Netflag is a numerical value either 1 or 0 to define it as usable or nonusable.

Here is the statement:

select distinct
       l.whse,
       l.partid,
       i.descr,
       l.qty,
       case when f.Netflag=0 then 'NONUsable' else 'Usable' end as UsableStatus,
       SUM(l.qty*f.Netflag) over (partition by l.partid) as TotalUsable
from   location l (nolock)
join   inventory i (nolock)
on     l.partid=i.partid
join   loctable f (nolock)
on     l.whse=f.whse

Here is what I want the results to look like:

+-------+--------+-----+------------+-----------+--------------+
|Partid |  Whse  | Qty |UsableStatus|TotalUsable|TotalNonUsable|
+-------+--------+-----+------------+-----------+--------------+
 134       INSP     10    Usable         30            20
 134       RRR      15    NONUsable      30            20
 134       VVV      20    Usable         30            20
 134       QRR      5     NONUsable      30            20

I appreciate your help thank you.

You can use a CASE expression inside SUM():

SELECT
    l.whse, l.partid, i.descr, l.qty,

    CASE WHEN f.Netflag = 0 THEN 'NONUsable' ELSE 'Usable' END
        AS UsableStatus,
    SUM(CASE WHEN f.Netflag = 0 THEN l.qty ELSE 0 END)
      OVER (PARTITION BY l.partid) AS TotalNonUsable,
    SUM(CASE WHEN f.Netflag = 0 THEN 0 ELSE l.qty END)
      OVER (PARTITION BY l.partid) AS TotalUsable
FROM
    location l
  JOIN inventory AS i ON l.partid = i.partid
  JOIN loctable AS f ON l.whse = f.whse ;

By the way, I don't see why you need DISTINCT in the query.


XML Large DataBase is the best? Is the best way to implement a large database is XML? Or is there a better way?

1 - reduce the time complexity
2 - Reduce the amount of traffic between the tables
And other factors ...

Which example is better, one or two?

1.Student Table: (without XML)
 id
 name
 family
 age
 address

2.Student Table With XML
 id
 XMLDT (contains name,family,age,address)

There's no right or wrong answer to this without considering the requirements, but if you need to do anything except return to the client the entire XML blob -- based on some other criteria than the XML contents itself -- XML fields are not a good idea.

For searching, while XML indexes can mitigate some of the performance problems, there is a big storage penalty to pay for those. Since this question is tagged data-warehouse, this kind of solution (your option 2) is almost certainly inappropriate for all kinds of reasons.

The one thing you definitely want to do is shred the XML and put all commonly-used (searched, selected) attributes in relational fields. Depending on the structure of the XML, this could mean a single table, or it could mean multiple tables.

The only question is what to do with everything else that doesn't fit. The remaining fields could continue to live as XML if the schema has to be really flexible (probably a good idea to strip out everything else to save storage space). You could also consider implementing an architecture based on sparse columns (SQL Server 2008+) if the structure is more predictable.

If you need to put things back into XML for client applications, FOR XML will likely be your best friend.


SQLPlus configuration problem or firewall issue I need to connect to a remote Oracle DB, so I requested to open the firewall on the port 1521 to the DB and the replica.
[root@ansible-test ~]$ telnet <oracle_db_active> 1521
Trying <oracle_db_active>...
Connected to <oracle_db_active>.
Escape character is '^]'.

^CConnection closed by foreign host.

[root@ansible-test ~]$ telnet <oracle_db_replica> 1521
Trying <oracle_db_replica>...
Connected to <oracle_db_replica>.
Escape character is '^]'.

^CConnection closed by foreign host.

I'm able to telnet to the DB on port 1521.
But when I try to connect using SQLPlus I get an error as the host doesn't exist.
[root@ansible-test ~]$ echo exit |sqlplus "<user>/<pass> @(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <oracle_db_active>)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = <oracle_db_replica>)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = <service>)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 12)(DELAY = 5))))"

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 7 17:37:23 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

ERROR:
ORA-12545: Connect failed because target host or object does not exist

Enter user-name: Enter password:
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

I have another server in the same network as the DB and I'm able to connect with the same string.
I don't have an sql.ora or tnsname.ora configure on any of the servers.
The only difference between the two servers is that one is RedHat and the other centos.
Do I need to open any other port to the DB o it is a configuration issue?
Thanks
(sorry for all the redacting)

I end up finding the answer :)
On my test server on the same network as the DB I made a tcpdump and I notice that my connection started on the primary IP and the DB reply through the secondary IP. There was a request on the DNS server in the middle.
I made the same test on my ansible-test server and found out that there was problem on then DNS request and fail the connection. Basically, it was not able to understand the secondary IP name.
So this end up being a weird networking problem.
Thanks All.


One oracle user two tablespace issue I have an application which works with two separate firebird database, but each database has the same user, but in case of Firebird the connection could be driven to each by connection string. My question is that if I would like to do the same in oracle, how can I do it? I created two tablespace(one for matadata one for data). User 'User1' has access to both of them, but how can I handle that once I want to read/write in Mata TS once in the DATA TS? I need to create a new user for the second one, or there is a way to manage it?
I would be surprised if there was no solution because then why would it allow a user to have several tablespace if I can not control where to write?
Thank you in advance for the answers.

A tablespace is a logical entity in Oracle database which consists of physical data files stored on the underlying filesystem.

We provide a quota of a certain size on the tablespace to a user. Which allows the user to create objects on it. And the user can explicitly defines on which tablespace he/she wants to create the object.

Here is a simple example which represents(as far as I understand) your scenario.

-- Create two tablespaces
SQL> create tablespace meta_data datafile size 1m;

Tablespace created.

SQL> create tablespace data datafile size 1m;

Tablespace created.

--Create a user and assign quota on one of the tablespaces
--or you can define a tablespace a default tablespace
--on which user can create an object by default.

SQL> create user user1 identified by user1 quota unlimited on meta_data
  2  ;

User created.

--Assign quotas on another tablespace
SQL> alter user user1 identified by user1 quota unlimited on data;

User altered.

--Now, the user can create objects on both of the tablespaces.
--In order to make a connection, he or she needs to have
--`create session` system privilege

SQL> grant create session to user1;

Grant succeeded.

SQL> conn user1@orclpdb
Enter password:
Connected.

--Let's create a table on meta_data tablespace.

SQL> create table t1(col1 number) tablespace meta_data;
create table t1(col1 number) tablespace meta_data
*
ERROR at line 1:
ORA-01031: insufficient privileges

--Oops, lack of create table privilege

SQL> conn sys@orclpdb as sysdba
Enter password:
Connected.
SQL> grant create table to user1;

Grant succeeded.

SQL> conn user1@orclpdb
Enter password:
Connected.
SQL>  create table t1(col1 number) tablespace meta_data;

Table created.

SQL> create table t2(col1 number) tablespace data;

Table created.


Query between two related tables I have two tables. One of them (user_profile table) has a field named "user_profile_id" and the other one (user_friend table) has two fields named "user_profile1_id" and "user_profile2_id" which are FK to the first table.

I want to check if there are any users in user_profile which are not in user_friend table, neither in user_profile1_id, nor user_profile2_id.

What query should I use?

EDIT:

CREATE TABLE user_profile (
  --fields
)

CREATE TABLE user_friend (
  user_profile1_id INT,
  user_profile2_id INT
)

Use a LEFT JOIN and check for null values. Something like:

SELECT profile.* FROM user_profile profile
 LEFT JOIN user_friend friend1 ON friend1.user_profile1_id = user_profile_id
 LEFT JOIN user_friend friend2 ON friend2.user_profile2_id = user_profile_id
 WHERE friend1.user_profile1_id IS NULL AND friend2.user_profile2_id IS NULL

Here's a link to the various joins


Disabling Indexes and Auditing for large data import Hopefully the title describes this sufficiently, but I have a process that runs and performs thousands of individual inserts and updates (nothing I can do to change that). My questions are:

1.
Is it worthwhile to disable all the indexes on the tables before the import, and rebuild the indexes afterwards

2.
Is it worthwhile (and is it even possible) to disable SQL auditing during the import and enable it when the import is complete.

The import is a binary operation, so either it works or it doesn't. It's also done on off-hours, so I'm not concerned with keeping either indexes up to date, or keeping auditing data.

Disabling indexes is a popular method when inserting a bulk amount of data.  So if you are looking to get this done in the shortest amount of time (your window isn't long enough for the with-index import??), simply do this:

alter index YourNonClusteredIndex
on YourDestinationTable
disable
go

Do this for your nonclustered indexes.

Granted, when you disable your indexes (provided you want to continue to use them) to enable then you'll need to rebuild or recreate them.  So there is that time-consuming side effect.

Another consideration pointed out by @JNK:

  Just disabling indexes on the table may be an issue if he uses any of those indexes to find the row to update or check for a previously inserted row.

In other words, whatever you choose you should be testing to ensure you're getting the gains you are looking for.

As for auditing, you need to be more specific on what type of auditing you are using.  If you have triggers on your table, and that is your auditing design then you can also disable all of the triggers on your table by doing the following:

disable trigger all
on YourDestinationTable
go

Disabling the triggers on a table could potentially save you a lot of time on your insert (or whatever action fires the trigger[s]).


SELECT columns which contain data first I have result of a query as below first table but the requirement is to display columns first which contain data in it.

Result:

Requirement:

Sample data generation:

Create table #CustData
(
CustID int,
CustName varchar(500),
VL1 varchar(10),
VL2 varchar(10),
VL3 varchar(10),
VL4 varchar(10),
VL5 varchar(10),
VL6 varchar(10),
NUMBER INT
)

INSERT INTO #CustData (CustID,CustName,VL1,VL2,VL3,VL4,VL5,VL6,NUMBER)
SELECT 1,'ABC', NULL, 'dl2','dl3','dl4','dl5','dl6',08586
UNION ALL
SELECT 2,'DEF', 'dl1', 'dl2',NULL,'dl4','dl5','dl6',54646
UNION ALL
SELECT 3,'GHI', 'dl1', 'dl2','','dl4',NULL,'dl6',6464
UNION ALL
SELECT 4,'JKL', NULL, 'dl2','dl3','dl4','dl5',NULL,44645
UNION ALL
SELECT 5,'MNO', 'dl1', NULL,NULL,'dl4','dl5','dl6',46457256

You could UNPIVOT, apply a ROW_NUMBER and then PIVOT it back.

select
    CustID,
    CustName,
    [1] as [VL1],
    [2] as [VL2],
    [3] as [VL3],
    [4] as [VL4],
    [5] as [VL5],
    [6] as [VL6],
    NUMBER
from(
    select
        CustID,
        CustName,
        NUMBER,
        V,
        Seq = row_number() over (partition by CustID order by case when V = '' then 7 else null end)
    from
        (
        select
            CustID,
            CustName,
            VL1,
            VL2,
            VL3,
            VL4,
            VL5,
            VL6,
            NUMBER
        from #CustData
        ) as cp
    unpivot
        (
        V for Vs in (VL1, VL2, VL3, VL4, VL5, VL6)
        ) up
    ) s
pivot
    (
    Max(V) for Seq in ([1],[2],[3],[4],[5],[6])
    ) p


Selecting a id from a pivot table Trying to make a messaging system, similar to FB, people can message more than one person, when they do it creates a row for the user with the conversation id and user_id in conversation_user.

create table conversations(
  id int
);
create table users(
  id int,
  name text
);
create table conversations_users(
  user_id int,
  conversation_id int
);
create table conversations_reply(
  id int,
  user_id int,
  body text,
  conversation_id int
);

I want to select the last 10 conversation that user_id 1 is a participant in and select the last post of that conversation. How would I go about doing this?

http://sqlfiddle.com/#!9/eb0075

We can find with subquery each conversation_id for user_id from table conversations_users and use left join for get one reply for each conversation_id from conversation_reply table (see sqlfiddle example and code below)

select cp1.* from conversations_reply cp1
left join conversations_reply cp2
  on (cp1.conversation_id = cp2.conversation_id AND cp1.id < cp2.id)
where cp2.id is NULL and
  cp1.conversation_id in (
    select conversation_id from conversations_users where user_id = 4
  )
order by cp1.id desc limit 10;

Output will be

+---+-------+--------+---------------+
|id |user_id|body    |conversation_id|
+---+-------+--------+---------------+
|9  |      4| message|              4|
|8  |      4| message|              3|
|7  |      1| message|              1|
|6  |      4| message|              2|
+---+-------+--------+---------------+


After mysql_upgrade to 5.7, optimizer not using index on large IN clause I have a similar issue to some other people have described.

In MySQL 5.6.23-72.1-log, a query with a large number of values in an IN clause uses an index, and takes 10 minutes to run;
In 5.7.19-17 the same query does not use an index, and takes at least 2 (sometimes more than 4) hours.

I've tried

set session eq_range_index_dive_limit=4294967295; no luck.
set @@global.max_seeks_for_key=100; still no luck.

This happened after I upgraded some of the servers.

No issues were reported by mysql_upgrade.

As I noted, I've seen other questions, but none of the proposed answers there have resolved the situation for me.

Thanks very much to Rolando for his answers so far. I went ahead and tested with this in the [mysqld] section of my.cnf

optimizer_switch = index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=off,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=off,derived_merge=off

Alas the indexes are still not being picked up.
I tried with all the newly added options off:

duplicateweedout=off
condition_fanout_filter=off
derived_merge=off

I even tried with SET optimizer_switch='block_nested_loop=off'
because I was getting 'Using where; Using join buffer (Block Nested Loop)' and 'Using temporary; Using filesort' in my explain plan.

Still scratching my head. Thanks, The bottom line is the upgrade has caused a lot of queries to not use indexes and its a pain still. The database is almost 2TB but queries run fine in last few masters that are on 5.6

You may not be aware of this, but MySQL optimizer has a different setting between versions

For MySQL 5.6, optimizer_switch looks like this:

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,
                    index_merge_intersection=on,
                    engine_condition_pushdown=on,
                    index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,
                    block_nested_loop=on,batched_key_access=off,
                    materialization=on,semijoin=on,loosescan=on,
                    firstmatch=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on

For MySQL 5.7, optimizer_switch looks like this:

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,
                    index_merge_intersection=on,
                    engine_condition_pushdown=on,
                    index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,
                    block_nested_loop=on,batched_key_access=off,
                    materialization=on,semijoin=on,loosescan=on,
                    firstmatch=on,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,
                    condition_fanout_filter=on,derived_merge=on

You could set the default for optimizer_switch for MySQL 5.6 in my.cnf and restart MySQL 5.7. Then, the optimizer's behavior will be the same as before the upgrade. For the new options, set duplicateweedout=off and derived_merge=off.

This is not a complete answer. You'll have to test this.


Number of logical reads in SQL server JOIN Please refer to the SELECT query shown below, which includes a JOIN between two tables that I have renamed as Table1 and Table2, where the latter is a temporary one:

Table1 has over 15M records, whereas Table2 is around 9K.
Product team says the JOIN acts as a filter as far as reads are concerned and that they should be 9K at most. However, DBA insists that the JOIN here is not a filter and that the reads are more than 14M because there is no WHERE clause here.

My humble opinion (not being a SQL developer or DBA myself) is that the JOIN can be thought of as a filter in the final resultset, but SQL Server somehow has to read the entire Table1 before it can actually perform the JOIN with Table2.
The reason that prompted this discussion was a performance issue and the fact that the execution plan shows +14M of actual rows associated with this query:

Unfortunately, this is proprietary code I am not allowed to post publicly. My question is more geared towards what takes place during a SQL JOIN under the hood. Does SQL Server need to read the entire tables before returning the result set?
The exact question would be: Does it make any sense that SQL Server had to make +14M reads to perform the JOIN in this scenario?

There are cases where every single row from your larger table could be read, and cases where it is closer to the number from your smaller table.  SQL Server will pick the method it thinks is most efficient based upon the information it has.
For example, if you have an index on the larger table that includes the join column, there is likely going to be less reading, as it should be able to skip a bunch of records.  If you don't, then the only way it will know what rows to select is by reading each row.  Keep in mind that statistics will impact the choices made, and if they are out of date it could lead to a bad choice.
Note there are implications for adding indexes, and there are numerous cases where they hurt over all performance, versus increasing it, so don't go willy-nilly in creating them.  I'd say ask your DBA, but...
Personally, I would question if you want to retain your DBA.  An INNER JOIN (which is what you have, with the INNER omitted) is a filter and in many (but not all) cases you can move predicates between the WHERE clause and the ON clause with zero impact (SQL Server will optimize to the exact same query plan), so the "because there is no WHERE clause" is not only wrong, but in fundamentally misunderstand sort of way.
This is not to say your product team is correct. Looking at the screen shot, there are clearly more reads than 9K. If your products team is in Marketing, then I can understand their confusion (<grin>).
Lastly, the NOLOCK in there should be a big red flag.  There are cases where it won't cause a problem, but one should be able to clearly be able to articulate what the problems are, and why it won't be an issue before using it.


Microsoft SQL Server 2008 encryption over multiple DB versions I have about 5 clients deployed running different versions of SQL Server 2008 (Some standard, some enterprise, etc.) And I am being tasked with encrypting the data so:

The software we are using can still query the data with decent efficiency.
DBA's can still get into the data through Management Studio.
If someone were to obtain a copy of the backup files (being stored on an external HDD), they would be unable to restore the database.

It seemed as if Transparent Data Encryption would be perfect, and I have it functioning, but "This feature is only available in Enterprise and Developer Editions of SQL Server 2008".

I want to have the method of encryption standard over all of the deployed servers.
What is a good method to do so?  Should I simply encrypt the HDDs?

Also, am I missing any other way that a person could potentially get a copy of database (Assuming no passwords are breached)?

Requirement 2 requires the data be stored "normally": that is, you can't use encryption in the client for all data. Encrypting data in the client also contradicts requirement 1

Requirement 3 requires the media or the actual backups are encrypted. Encrypting the HDDs isn't reliable because once someone has the actual media then it can normally be unencrypted by a sysadmin.

So, I'd suggest using a 3rd party tool like Red Gate SQL Backup Pro or LiteSpeed by Quest to secure your backups. Points 1 and 2 are satisfied because the on-line database is unchanged


In Rails: How is existing data affected by data type change? I'm working on a Rails app and would like to change the datatype for an existing column. It's currently a DateTime type, and I want to change it to a Date type. I found a way to do this here, but in this case, the person was not worried about preexisting data.

Right now, I plan to generate a migration...

rails g migration change_my_column_in_my_talbe

...and make the following change:

class ChangeMyColumnInMyTable < ActiveRecord::Migration
  def change
    change_column :my_table, :my_column, :date
  end
end

My question is: will the existing data be converted to a Date type, or will I need to create a rake task to convert the values for all of my existing DateTime values?

I found a similar question in which the conversion was from Boolean to String, and it seemed like the change would be automatic for existing data. I just want to be sure before I jump into making this change.

I'm using Rails version 4.2.0 and MySQL version 5.6.27 Homebrew. Any advice on this issue would be greatly appreciated!

I was able to set up a way to test this, and the existing data did convert from DateTime to Date types! As explained here, when doing this with MySQL as the adapter, using change_table triggers the following SQL call:

ALTER TABLE table_name MODIFY COLUMN column_name DATE

Two helpful warnings (courtesy of @SacWebDeveloper):

This is an irreversible change, so if anyone else is trying this and may need to reverse, be sure to use the reversible command or write out the up and down changes.
When converting from DateTime to Date, be aware that rounding uses fractions of a second. Example: '1999-12-31 23:59:59.499' => '1999-12-31', but '1999-12-31 23:59:59.500' => '2000-01-01'.


How to manage very large amount of data in mysql I have a table in MySQL in which I write logs of users in a specified format. It have just 2 columns:id (auto-increment), log. Currently data is very less, so I'm using this.

Now there is a requirement which involves insertion of very large amount of data, (may be 60-70x of current), that may even involve concurrent insertion of even approx 2000 record. It will obviously slow down our system, and cant be used in production environment. Moreover database size will become an issue.

I need some ideas that will serve my purpose.

Use a "staging table".  Insert the unprocessed data into a separate table, then remove it when processed.

How big will the table eventually become?  A million row -- no problem.  A billion rows -- let's discuss more details.

For really high speed ingestion, see my blog.


Query to find rows containing ASCII characters in a given range I am using some scripts from another topic, but the accepted answer isn't working for all my data scenarios.  I would have asked my question on the original How to check for Non-Ascii Characters post, but I don't have enough reputation to comment or up-vote as of yet.

Questions:

Why is the accepted answer not working for char(150)?
Is there a way to fix the accepted answer?

My Testing

I created SQL Fiddle with sample data, the stored procedure from one of the answers, and queries to demonstrate the issue.

  Query 1: sample_table

-- Note: The "bad dash" row has char(150)

SELECT * FROM sample_table;

+-------------------+
|    DataColumn     |
+-------------------+
| test - good dash  |
| test – bad dash   |
+-------------------+

  Query 2: The other answer by John shows the "bad dash" row containing char(150):

SELECT dbo.Find_Invalid_Chars(DataColumn) [Invalid Characters]
FROM sample_table
WHERE dbo.Find_Invalid_Chars(DataColumn) IS NOT NULL;

+----------------------+
|  Invalid Characters  |
+----------------------+
| test [150] bad dash  |
+----------------------+

  Query 3: The accepted answer by Martin Smith returns no results:

SELECT DataColumn AS [Bad Data]
FROM sample_table
WHERE DataColumn LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_100_BIN2;

+------------+
| [Bad Data] |
+------------+

-- No rows returned.

Conclusion

Unfortunately, I often need to find characters within (or outside of) a range in databases I can't create stored procedures in.  I'd really like to find a fix for the accepted answer or a simple script that wouldn't require creation of any objects (including temp tables).

Any suggestions?  Thanks in advance.

EDIT 1: The solution cannot modify or add any objects or settings in the database. I'm looking for a self-contained query that will select rows with one or more characters in a range between two CHAR() numbers, regardless of the ASCII or Extended ASCII number supplied.

EDIT 2: The DataColumn can be in either VARCHAR or NVARCHAR. I have no control over this, so I'm hoping to figure out a self-contained query that works for both. The purpose of the query is to find characters in the source table/column that are not handled correctly by some software applications. The applications are interpreting the source correctly, but sometimes have issues with characters outside "standard" ranges, though the ranges vary by application.

Why is the accepted answer not working for char(150)?

Actually, it does. The problem is your test is bad / invalid. You test column, DataColumn, is using NVARCHAR instead of VARCHAR. The character itself works in both datatypes, but the behavior is different due to how it is being used in each case:

In the Find_Invalid_Chars() function (i.e. the "other" answer), the string is being converted back into VARCHAR since that is the datatype of the input parameter for that function. In this case it works as expected (although I believe it can be done much more efficiently than that loop, but that's for another time ;-)
In the LIKE query (i.e. the "accepted" answer), the expanded and concatenated result of '%[' + CHAR(127)+ '-' +CHAR(255)+']%' is actually converted into NVARCHAR since that is the datatype of the column it is being compared to (and NVARCHAR has a higher datatype precedence), hence that LIKE function is not behaving as expected: either the CHAR(255) character maps to a different code point, and/or the CHAR(150) character in the column itself maps to a different code point (the CHAR(127) character does not change as it's in the standard ASCII range). In either case, the conversion to NVARCHAR is causing the numerical value of the "En Dash" character ("–") to no longer be within that range. Meaning, the LIKE function is looking for values, y, between 127 and x (where x >= 128), and y for the "En Dash" character is now > x. Whereas in VARCHAR, x = 255 and y = 150.

The quick fix to see that it does work is simply to change the NVARCHAR datatype of the  DataColumn column to be VARCHAR (yes, just remove the initial "N"), then re-build the schema, then execute, and the LIKE query will behave as expected.
The following might help explain why making the test column NVARCHAR caused the LIKE query to not match the row:
SELECT UNICODE(CHAR(127)) AS [CHAR(127)],
       UNICODE(CHAR(150)) AS [CHAR(150)],
       UNICODE(CHAR(255)) AS [CHAR(255)];

/*
CHAR(127)     CHAR(150)     CHAR(255)
127           8211          255
*/

As you can see in the results below the query, the "bad dash", which was CHAR(150) became NCHAR(8211) when stored in the NVARCHAR column. And, since that predicate is using a binary collation (generally the correct thing to do in this scenario), it was looking at the code points / values, not the characters. Hence, the LIKE clause was looking for characters with values between 127 and 255, and 8211 is typically not in that range ;-).
P.S. Please keep in mind that the function CHAR(150) can return different characters, or even NULL, based on the default collation of the database in which you execute that function. This is because VARCHAR data is based on code pages, and those are determined by the collation, and the collation being used when executing the CHAR() function is the default collation of the active / current database. This affects values 128 - 255. Values 0 - 127 will always return the same characters, regardless of collation, as those are the standard ASCII character set and are the same across all of the code pages supported in SQL Server (though not in all code pages in general).
P.P.S. ALSO, I just noticed a slight difference in logic between the function and the query (i.e. the two answers from the linked question): CHAR(127) is considered good / valid in the Find_Invalid_Chars() function, yet it's considered bad / invalid in the LIKE query. If it were me, I would consider CHAR(127) valid as it's part of the standard ASCII character set. But, you need to decide what you consider it. Just be aware of that difference in case you do need to adjust the LIKE syntax a little.

Given:

The purpose of the query is to find characters in the source table/column that are not handled correctly by some software applications.

and:

The data can be in either VARCHAR or NVARCHAR.

I would say that:

You don't want to convert NVARCHAR source data into VARCHAR as there might be "best fit" mappings that translate invalid source characters into valid characters, but one or more of your software applications might not use "best fit" mappings.
SELECT NCHAR(178) AS [Unicode], -- Superscript 2 (U+00B2)
       CONVERT(VARCHAR(5), NCHAR(178)
                   COLLATE SQL_Latin1_General_CP1_CI_AS) AS [CodePage-1252],
       CONVERT(VARCHAR(5), NCHAR(178)
                   COLLATE Turkmen_100_CI_AS) AS [CodePage-1250]

/*
Unicode    CodePage-1252    CodePage-1250
²          ²                2
*/

It will likely be more reliable to look for characters not in a specific "valid" range as opposed to those in a specific invalid range, especially when dealing with NVARCHAR which holds a lot more than 256 characters.

You could get away with a single query if the "valid" range is always between values 0 and 127 (since those values are the same in both cases). But if you need to specify values above 127, then you will need one query for VARCHAR and one for NVARCHAR.

All that being said:

The following query returns rows containing at least one character that is not in the range of 0 - 127, for both VARCHAR and NVARCHAR. But, it only works with NVARCHAR columns for values above 127.
SELECT *
FROM   (VALUES (NCHAR(178)), (NCHAR(8211)), (N''), (NULL), (N'xy' + NCHAR(165)),
           (N'AA'), (N'mM' + NCHAR(999) + N'Nn'), (N'#!~()')) tmp(TestValue)
WHERE  tmp.[TestValue] LIKE N'%[^' + NCHAR(0) + N'-' + NCHAR(127)
          + N']%' COLLATE Latin1_General_100_BIN2;

/*
TestValue
²
–
xy¥
mMϧNn
*/

The following query also returns rows containing at least one character that is not in the range of 0 - 127, but only works for VARCHAR columns. However, it does allow using values between 128 and 255.
SELECT *
FROM   (VALUES (CHAR(178)), (CHAR(150)), (''), (NULL), ('AA'), ('#!~()'),
        ('xy' + CONVERT(VARCHAR(5), NCHAR(165) COLLATE Latin1_General_100_BIN2)),
        ('mM' + CONVERT(VARCHAR(5), NCHAR(199) COLLATE Latin1_General_100_BIN2) + 'Nn')
       ) tmp(TestValue)
WHERE  tmp.[TestValue] LIKE '%[^' + CHAR(0) + '-' + CHAR(127)
          + ']%' COLLATE Latin1_General_100_BIN2;

/*
TestValue
²
–
xy¥
mMÇNn
*/

Regarding:

The applications are interpreting the source correctly, but sometimes have issues with characters outside "standard" ranges, though the ranges vary by application.

Not sure I understand how there can be "issues" with some characters if the app is interpreting the source data correctly, unless you meant that they "mostly" interpret the data correctly.
The ranges varying by application sound like this might require more detailed investigation than can be done in a simple Q & A format such as this. This behavior can be due to them using different drivers to connect (ODBC / OLEDB / etc), what language they are written in, what assumptions they are making about the data they are getting, and so on. Some problems might be fixable with a configuration (no code change) of the app, some might be fixable only with a code change, etc.


Cluster is causing my query to run SLOWER then before So I have been stuck on this for 6 hours now and I have no clue what to do. I am doing university homework that requires us to create a unoptomized sql query (does not have to make sense) and then apply index's and see if it makes it faster (which it did for me, from 0.70 elapsed time to 0.66) and then we had to apply clusters.
I applied clusters and it has now almost doubled the amount taken to finish the query. From 0.70 to 1.15. Below is how I specified my cluster:
CREATE CLUSTER customer2_custid25 (custid NUMBER(8))

SIZE 270

TABLESPACE student_ts;

I tried all my previous times with INITIAL and NEXT but that seemed not to make a difference.  Below are the tables:
CREATE TABLE CUSTOMER18 (

    CustID         NUMBER(8) NOT NULL,
    FIRST_NAME     VARCHAR2(15),
    SURNAME     VARCHAR2(15),
    ADDRESS     VARCHAR2(20),
    PHONE_NUMBER NUMBER(12))

    CLUSTER customer2_custid25(CustID);

CREATE TABLE product18(

    ProdID     NUMBER(10) NOT NULL,
    PName    Varchar2(6),
    PDesc    Varchar2(15),
    Price    Number(8),
    QOH        Number(5));

CREATE TABLE sales18(

    SaleID     NUMBER(10) NOT NULL,
    SaleDate    DATE,
    Qty            Number(5),
    SellPrice    Number(10),
    CustID        NUMBER(8),
    ProdID        NUMBER(10))

    CLUSTER customer2_custid25(CustID);

CREATE INDEX customer2_custid_clusterindxqg ON CLUSTER customer2_custid25 TABLESPACE student_ts ;

Base Query:
SELECT
    AVG(s.sellprice), s.qty, c.custid
FROM
    SALES s, CUSTOMER2 c
WHERE
    c.custid = s.custid
GROUP BY
    s.qty, s.qty, c.custid
HAVING
    AVG(sellprice) > (SELECT MIN(AVG(price))
                      FROM product
                      WHERE pname in
                    (SELECT pname
                       FROM product
                       GROUP BY pname
                       HAVING pname LIKE 'FA%')
                       GROUP BY price);

I also tried taking the tablespace section in the cluster index away.
I followed this formula to help calculate cluster sizes:

"Size of a cluster is the size of a parent row + (size of Child row *
average number of children). "

This brought me to the size of 270. However, after testing sizes (going up 20) from 250 to 350 I found 320 to be the fastest at 1.15.

No matter what I try, I can not for the love of me get it lower then my base query times.
Other students have done the same and halved their query time.
All help is really appreciated.

Chat Recap
According to the Plan, the CBO decided to not take advantage of the data being in a CLUSTER.


Running Excel addin for MDS2019 vs MDS2016 We are upgrading master data services (MDS) of SQL2016 to SQL2019.Is there a way to run excel 2019 addin for connecting to SQL 2019 MDS along side(parallely) with excel 2016 addin for connecting to MDS 2016 on user's desktop machine so that same user can connect to MDS 2019 with excel 2019 and MDS2016 with excel 2016 simultaneously?.The reason we want to run parallelly is we doing a migration for UAT environment to MDS2019 and we don't want to restrict their access to MDS 2016 till the time they are on prod.
We are open for any solution like techniques (if any) from desktop virtualization perspective or anything from SQL...Appreciate all the responses
-Kev

It's not entirely clear what part of the stack your question is on. Assuming you're running a second server in parallel with SQL Server 2019 with MDS 2019, then you can certainly connect to either instance in parallel. Specifically here's the download for the Excel Add-In for MDS 2019 which should be backwards compatible to connecting to your MDS 2016 instance. You should be able to even deploy your models from your 2016 instance over to your 2019 instance by following these docs.
If you do need to still give your end users a version of Excel with the MDS 2016 Add-In, then you'll need a separate machine (a VM, or however you prefer) with Excel installed and the MDS 2016 Add-In installed, as I doubt there's stability in installing two instances of Excel, each with the respective version of the Add-In, on the same machine. But maybe it's possible, you'd have to test first.


How do you delete achieve log image copies that are already backed up? How do you delete achieve log image copies that are already backed up ?

It seems like below command doesn't work:

delete copy of archivelog all until time 'sysdate-1' backed up 1 times to disk

all and until time does not work together, choose one:

delete copy of archivelog until time 'sysdate-1' backed up 1 times to disk;

or

delete copy of archivelog all backed up 1 times to disk;


Oracle Data Guard - redo logs not in chronological order? The disadvantages section of the Wikipedia entry for Oracle Data Guard says:

  If the network link connecting primary and standby is over-subscribed, the redo logs are not applied in chronological order

Is this true? I can't find a reference to this in the Oracle documentation.

I simply do not believe. It's impossible. There is process called FAL (Fetch Archive Log), it can happen that redo-logs are not TRANSFERRED in chronological order, but anyway they must be APPLIED in chronological order.

Also logical standby has can have some gotchas but those are not related to network link.
(For example for some reason LOCK TABLE statement is not propagated to slave)


How to import a.csv file into a postgresql database using \copy? I am trying to import a .csv file into a postgresql database. I am using pgadmin III for that on a win7 machine.

My query looks like that:

\COPY pop_grid(GRID_ID, POP_TOT, YEAR, METHD_CL, CNTR_CODE, DATA_SRC) from 'C:\...\popgrid.csv' DELIMITERS ',' CSV;

The data inside the .csv looks like that:

1kmN5142E2862   2   2006    D   IS  AIT
1kmN5141E2862   13  2006    D   IS  AIT

My table:

CREATE TABLE pop_grid
(
    GRID_ID      text            PRIMARY KEY,
    POP_TOT      int         NOT NULL,
    YEAR         date        NOT NULL,
    METHD_CL     varchar(2)      NOT NULL,
    CNTR_CODE    varchar(32)     NOT NULL,
    DATA_SRC     varchar(4)      NOT NULL
);

Why do I get this error?

ERROR: Missing data for column "pop_tot"
CONTEXT: COPY pop_grid, Row 1: "1kmN5142E2862;2;2006;D;IS;AIT"

There is nothing missing?

You tell the COPY command to look for commas as delimiters (DELIMITERS ','), but there are no commas in your CSV.  Use the 'text' format instead (it's the default, so you don't have to specify it) and do not specify a delimiter:

  The default is a tab character in text format.

(source)


How to store file paths, which collation to use? I have a table that stores Windows system paths. I am scratching my head about how to store them and in particular which collation to use.

The paths are unicode strings. They are case insensitive, therefore 'utf8_binis probably not the best thing to do. However all theutf8_*_ci` collations allows equality between strings corresponding to different paths.

I am aware that path comparison is generally much more involved that string comparison, due to the potential presence of parent (..) directories, symbolic links, 8.3 alternative naming, aso. I am assuming that the folders are input in a systematically consistent fashion.

What do people do when storing paths? Do they assume that folders are indeed always stored in a consistent fashion and say always store Windows paths in lower or upper case and use utf8_general? Or another collation? Or simply forget about the idea of using server-side path comparison?

Not until MySQL 8.0 is there a "case-insensitive, accent-sensitive" collation:

mysql> set names utf8mb4 collate utf8mb4_0900_as_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> select 'a'='A', "a" = "à" ;
+---------+------------+
| 'a'='A' | "a" = "à"  |
+---------+------------+
|       1 |          0 |
+---------+------------+
1 row in set (0.00 sec)


SELECT DISTINCT ON subquery uses inefficient plan I've got a table progresses (contains on the order of hundreds of thousands of records currently):

    Column     |            Type             |                        Modifiers
---------------+-----------------------------+---------------------------------------------------------
 id            | integer                     | not null default nextval('progresses_id_seq'::regclass)
 lesson_id     | integer                     |
 user_id       | integer                     |
 created_at    | timestamp without time zone |
 deleted_at    | timestamp without time zone |
Indexes:
    "progresses_pkey" PRIMARY KEY, btree (id)
    "index_progresses_on_deleted_at" btree (deleted_at)
    "index_progresses_on_lesson_id" btree (lesson_id)
    "index_progresses_on_user_id" btree (user_id)

and a view v_latest_progresses which queries for the most recent progress by user_id and lesson_id:

SELECT DISTINCT ON (progresses.user_id, progresses.lesson_id)
  progresses.id AS progress_id,
  progresses.lesson_id,
  progresses.user_id,
  progresses.created_at,
  progresses.deleted_at
 FROM progresses
WHERE progresses.deleted_at IS NULL
ORDER BY progresses.user_id, progresses.lesson_id, progresses.created_at DESC;

A user can have many progresses for any given lesson, but we often want to query for a set of the most recently created progresses for a given set of users or lessons (or a combination of the two).

The view v_latest_progresses does this nicely and is even performant when I specify a set of user_ids:

# EXPLAIN SELECT "v_latest_progresses".* FROM "v_latest_progresses" WHERE "v_latest_progresses"."user_id" IN ([the same list of ids given by the subquery in the second example below]);
                                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=526.68..528.66 rows=36 width=57)
   ->  Sort  (cost=526.68..527.34 rows=265 width=57)
         Sort Key: progresses.user_id, progresses.lesson_id, progresses.created_at
         ->  Index Scan using index_progresses_on_user_id on progresses  (cost=0.47..516.01 rows=265 width=57)
               Index Cond: (user_id = ANY ('{ [the above list of user ids] }'::integer[]))
               Filter: (deleted_at IS NULL)
(6 rows)

However if I try to do the same query replacing the set of user_ids with a subquery, it becomes very inefficient:

# EXPLAIN SELECT "v_latest_progresses".* FROM "v_latest_progresses" WHERE "v_latest_progresses"."user_id" IN (SELECT "users"."id" FROM "users" WHERE "users"."company_id"=44);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Merge Semi Join  (cost=69879.08..72636.12 rows=19984 width=57)
   Merge Cond: (progresses.user_id = users.id)
   ->  Unique  (cost=69843.45..72100.80 rows=39969 width=57)
         ->  Sort  (cost=69843.45..70595.90 rows=300980 width=57)
               Sort Key: progresses.user_id, progresses.lesson_id, progresses.created_at
               ->  Seq Scan on progresses  (cost=0.00..31136.31 rows=300980 width=57)
                     Filter: (deleted_at IS NULL)
   ->  Sort  (cost=35.63..35.66 rows=10 width=4)
         Sort Key: users.id
         ->  Index Scan using index_users_on_company_id on users  (cost=0.42..35.46 rows=10 width=4)
               Index Cond: (company_id = 44)
(11 rows)

What I'm trying to figure out is why PostgreSQL wants to perform the DISTINCT query on the entire progresses table before it filters by the subquery in the second example.

Would anyone have any advice as to how to improve this query?

Aaron,

In my recent work, I've been looking into some similar questions with PostgreSQL.  PostgreSQL is almost always pretty good at generating the right query plan, but it isn't always perfect.

Some simple suggestions would be to make sure to run an ANALYZE on your progresses table to make sure that you have updated statistics, but this isn't guaranteed to fix your problems!

For reasons that are probably too long-winded for this post, I've found some odd behaviors in the statistics gathering of ANALYZE and the query planner that may need to be resolved in the long-term.  In the short-term the trick is to rewrite your query to try and hack out the query plan you want.

Without having access to your data for testing, I'll make the following two possible suggestions.

1) Use ARRAY()

PostgreSQL treats arrays and sets of records differently in its query planner.  Sometimes you'll end up with an identical query plan.  In this case, as in many of my cases, you don't.

In your original query you had:

EXPLAIN SELECT "v_latest_progresses".* FROM "v_latest_progresses"
WHERE "v_latest_progresses"."user_id"
IN (SELECT "users"."id" FROM "users" WHERE "users"."company_id"=44);

As a first pass at trying to fix it, try

EXPLAIN SELECT "v_latest_progresses".* FROM "v_latest_progresses"
WHERE "v_latest_progresses"."user_id" =
ANY(ARRAY(SELECT "users"."id" FROM "users" WHERE "users"."company_id"=44));

Note the change of the subquery from IN to =ANY(ARRAY()).

2) Use CTEs

Another trick is to force separate optimizations, if my first suggestion doesn't work.  I know many people use this trick, because queries within a CTE are optimized and materialized separate from the main query.

EXPLAIN
WITH user_selection AS(
  SELECT "users"."id" FROM "users" WHERE "users"."company_id"=44
)
SELECT "v_latest_progresses".* FROM "v_latest_progresses"
WHERE "v_latest_progresses"."user_id" =
ANY(ARRAY(SELECT "id" FROM user_selection));

Essentially, by creating the CTE user_selection using the WITH clause, you are asking PostgreSQL to perform a separate optimization on the subquery

SELECT "users"."id" FROM "users" WHERE "users"."company_id"=44

and then materializing those results.  I then, once again use the =ANY(ARRAY()) expression to try to manually manipulate the plan.

In these cases, you probably can't trust just the results of EXPLAIN, because it already thought that it found the least costly solution.  Make sure to run an EXPLAIN (ANALYZE,BUFFERS)... to find out what it really costs in terms of time and page reads.


Do the different "types" of autovacuum have different performance characteristics? In monitoring the autovacuum activity on my database, I've noticed that autovacuums tagged with (to prevent wraparound) seem to take longer than "regular" autovacuums.  That got me started trying to understand the differences in performance/behavior of the various "kinds" or autovacuums.

From the docs I've identified a few different reasons why autovacuum might VACUUM a table:

The table has old transactions that need to be frozen (relfrozenxid > min(autovacuum_freeze_max_age, table.autovacuum_freeze_max_age). This is the "(to prevent wraparound)" autovacuum.
The number of tuples obsoleted in the table since the last VACUUM exceeds the "vacuum threshold"

Are there any others that I'm missing?

The "aggressiveness" knobs that seem to be exposed for a manual VACUUM are FREEZE and DISABLE_PAGE_SKIPPING.  Do these different kinds of autovacuum ever use these options, and if so, when?

In monitoring the autovacuum activity on my database, I've noticed that autovacuums tagged with (to prevent wraparound) seem to take longer than "regular" autovacuums.

That's to be expected. They have to do more work. These are essentially a vacuum freeze run by autovacuum.

Freezing tuples means marking them as visible by all current and future transactions by replacing their transaction ID with a special fixed value FrozenTransactionId (xid 3). Since the xmin for a tuple is stored in the tuple on disk, this means each tuple that's not already frozen and is a candidate for freezing needs to be modified. This often triggers a full-page write since the tuple is generally the first modification on the page since the last checkpoint, resulting in an 8k write for the first frozen tuple on each page. (This also bloats WAL and therefore PITR backups, replication streams, etc).

Freeze vacuums in PostgreSQL 9.6 and below cannot skip parts of the table; they must scan the whole table each time, even if only a little of it is a candiate for freezing.

Unless you tune autovacuum to freeze more aggressively, it tends to leave forced freezes quite late and then do a LOT of work at once. This is efficient since we don't scan the table lots of times, but expensive since we do a lot of work each time we do process it.

When vacuum isn't freezing tuples it's still doing much of the same work. But it's usually touching much smaller numbers of pages on each pass since it only has to care about tuples that have a not-for-locking-only xmax set by a since-committed transaction. The pages with these tuples are also MUCH more likely to be in cache since they're recently deleted, and any multixact data for them is more likely to be in cache too.

You can make freeze vacuums faster by:

Lowering autovacuum_vacuum_cost_delay - this will make autovacuum use more resources and put more load on the system, but run faster.
Lowering autovacuum_freeze_max_age so they run more often and do less work each time. This is overall less efficient, since the whole table must be scanned each time, evicting pages from the OS buffer cache as we read the table. (Pg uses a ring-buffer for seq scans, but the OS generally won't). But it means we do fewer writes for each pass and each pass takes less time.
Doing COPY with the FREEZE option to bulk-load a table inside the same transaction that TRUNCATEd it or CREATEd it. Tuples loaded this way start off pre-frozen. (See the manual on COPY for details).
Manually freezing tables after bulk-loading them, so you pay the freeze cost at a time you control.

Personally I'd like normal VACUUMs (and autovacuums) to do more opportunistic freezing; in particular, if we've just touched the item pointer array on a page to mark some tuples as free space, we should probably scan for freeze-able tuples while the page is dirty. This would play very well with the new freeze map code in PostgreSQL 10.

There are some big improvements in freezing in PostgreSQL 9.6 that reduce the impact of repeated vacuums on tables and make freezing much cheaper. Due to the introduction of the freeze map functionality in the visibility map we can avoid scanning the whole table, skipping over frozen pages. See commits a892234 and fd31cd265.

commit fd31cd265138019dcccc9b5fe53043670898bc9f
Author: Robert Haas <rhaas@postgresql.org>
Date:   Thu Mar 10 16:12:10 2016 -0500

    Don't vacuum all-frozen pages.

    Commit a892234f830e832110f63fc0a2afce2fb21d1584 gave us enough
    infrastructure to avoid vacuuming pages where every tuple on the
    page is already frozen.  So, replace the notion of a scan_all or
    whole-table vacuum with the less onerous notion of an "aggressive"
    vacuum, which will pages that are all-visible, but still skip those
    that are all-frozen.

    This should greatly reduce the cost of anti-wraparound vacuuming
    on large clusters where the majority of data is never touched
    between one cycle and the next, because we'll no longer have to
    read all of those pages only to find out that we don't need to
    do anything with them.

    Patch by me, reviewed by Masahiko Sawada.

I think there is also work in progress for PostgreSQL 10 to entirely avoid the need to modify tuples on disk to mark them as frozen, but can't find the relevant mailing list thread atm.


Log Shipping from Synchronous Automatic Failover AlwaysOn Availability Group AAG I have an AlwaysOn Availability Group which is running with Synchronous Commit & Automatic Failover. For example, connected to as SQL01 with nodes SQL01A or SQL01B.

I want to make this data available for Warehousing on another server SQL02, due to high numbers of changes and high numbers of reads we cannot use AAG Asynchronous Commit as it gets behind and causes high waits.

Because of this I have decided to logship to SQL02 once daily. However with the AAG failing over between the nodes SQL01A & SQL01B I am not sure on best practices around this.

I had set up LSBackup,Copy,Restores from SQL01A. Then failed over to SQL01B and set up. However then my jobs error on the copy and restore, so I can add an additional step onto each job to check if it is the primary copy or restore job and exit out if not, but this just seems messy and I feel I am missing something?

Any advice would be greatly appreciated.

Are you doing your backups to a neutral independent server like a file share? If you're doing them locally (like to a drive on SQL01A or SQL01B) then log shipping won't work since the file path would change whenever there's a failover.

Instead, on SQL01A and SQL01B, write your backups to a UNC path (like \\myfileserver\myfileshare) and then SQL02 will always be able to find them there.


multiple count and use subsctract function in one query I try to do more advance query in mysql but got error in query, i try to count and group by. here is my query:
"select department,
(select count(id) from mcu group by dept) as tot1,
(select count(status_mcu) from mcu where status_mcu='done' group by dept) as tot2,
(tot1 - sum(select count(status_mcu) where status_mcu='done') as tot3,
FROM mcu"

can you help me out ? what wrong with my query ? i do some search in other threads and this is what i got. try do query like :
"select department,
select count(id),
sum(case when status_mcu='done' then 1 end)
...
"

not worked too. Any help is appreciate.

my sql version is 5.5.25 – casthrotopes

Without seeing the exact error message returned, I can't be 100% certain, but there are two glaring errors:

Is the column name Department or Dept?  This does not seem clear.
You cannot reference tot1 unless it is first derived in a CTE or sub-select (derived table).
The subquery sum(select count(status_mcu) where status_mcu='done') does not have a FROM clause.  Furthermore, I'm not sure what purpose where aggregating a high-level aggregate is necessary/desired.
As a general critique, you're hitting the same table over and over again which is not optimal from a performance standpoint.

Start with this and see if it returns the desired results (if it doesn't, you'll need to clarify your question to provide the proper logic):
SELECT
  mcu.Dept
 ,COUNT(mcu.Id) AS Tot1
 ,SUM
    (
      CASE
        WHEN mcu.status_mcu='done' THEN 1
        ELSE 0
      END
    ) AS tot2
 ,COUNT(mcu.Id) -
    SUM
      (
        CASE
          WHEN mcu.status_mcu='done' THEN 1
          ELSE 0
        END
      ) AS tot3
FROM
  mcu mcu
GROUP BY
  mcu.Dept


Format function returning minutes instead of months? While exploring the new function FORMAT I have come across the following issue, would like to know if any one else has faced a similar problem and found the fix for this, or if it is a bug?

SELECT FORMAT(GETDATE(),'mm/dd/yyyy','en-US');
SELECT GETDATE();

Output:

02/02/2013
2013-06-02 12:02:54.067

The Format function argument is a .net format string, where mm is minutes and MM is months. You specified minutes, so minutes is what you got.

There are example format strings at Standard Date and Time Format Strings. As an alternative to MM/dd/yyyy you can also use just d which is the Short Date Format specifier (detailed on the linked page).


How to find current setting of "FORCE PARALLEL QUERY" in Oracle I can change (override) the current level of parallelism in an Oracle session using e.g.:

alter session force parallel query parallel 6;

Is there any system view of function that will return me the value 6 that I have requested?

I would like to check (in a stored procedure) if my current session has changed this (ideally the setting for a alter session force parallel dml as well)

Tested on 12.1 but should work if you have appropriate privileges in versions where this view and feature is available. you will see rows only if value is non-default. If query returns 0 rows, it means value is default. Please test and adjust view names for your environment.

Select name, value From Sys.V_$ses_Optimizer_Env
Where Sid = (Select Distinct Sid From Sys.V_$mystat)
And Name In ('parallel_query_forced_dop','parallel_dml_forced_dop');


Best structure for user-level versioning of data in mysql I have an application I'm building that allows users to upload data files and I read the contents of the data file into a database. All the data files have the same fields, but there may be slight differences between the data elements depending on the original source and\or user (differences in the values, not in the data types).

To respect this, I need every user to have a personal copy of the data in the database, so no user can read the data that another user uploaded. The files are translated into a number of tables with foreign keys relationships, etc..., so its not trivial.

I have thought of the following solutions:

A single table structure with a user_id column in every table to  keep the data specific to the user - The current solution, but I'm  scared it may be a nightmare to maintain.
A different copy of the table structure per user (so datatable_1 and datatable_2 for users 1 and 2) - simpler to maintain and segment, but could be a lot of   tables and I'll need to create seperate views for each table   strucure... just alot of objects. Also, table-level locking would    prevent access issues when one user is uploading and another is   reading.
A different database for each user (db_1.datatable,   db_2.datatable) - Even better structuring and segmentation, and fewer   tables per database, but most pricing for web-supported applications   is per database, so this may be a non-starter...

Any thoughts or other suggestions would be much appreciated...

It is possible to use one-database-per-user or one-table-per-user models… But these will start to become unwieldy as the number of users in your application grows… MySQL (and, really, no RDBMS) handles hundreds of thousands of tables or databases very well.

Having an user_id (or similar) column on each table that contains user data is totally standard, and the "right" way to do what you're describing.


Can 2 different versions of MySQL be run side by side? I have Debian 10 installed and have an instance of MySQL 8 running.

I'd like to install 5.7 to try and bring up the database of a machine that failed, where I have the data directory fully intact, so I can inspect it and save the schemas, data, triggers, functions and stored procedures.

Does this question make sense?

The database I wanted to restore was actually MySQL 5.5.29. OH NO!
Based on the suggestion of @mustaccio, I set up a docker container to
run the MySQL 5.5 instance.

bazzani has a MySQL 5.5 docker image builder so I used that
The last step, to use the backed up MySQl instance files with the docker image
was completed with:

docker run --name=mysql55 -d -v ~/mysql:/var/lib/mysql mysql5.5

Find the IPAddress of MySQL in the container:

docker inspect mysql55 | grep -i ipaddress

After that it was easy to access the database and properly dump the data, schema and triggers and grants:

Grants

mysql -u user -ppassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';') FROM mysql.user WHERE user<>''" | mysql -u user -ppassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql
Routines

mysqldump --column-statistics=0 -h 172.17.0.2 -u user -ppassword  --no-data --no-create-info --routines ${DB} > ${DB}-routines.sql
Schema and Triggers

mysqldump --column-statistics=0 -h 172.17.0.2 -u user -ppassword --no-data --triggers ${DB} > ${DB}-schema-triggers.sql
Data

mysqldump --column-statistics=0 -h 172.17.0.2 -u user -ppassword --no-create-info --skip-triggers --no-create-db --compact ${DB} > ${DB}-data-only.sql

This whole solution was important as 12 months of research in multiple databases had been poorly backed up using mysqldump this way:

mysqldump --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db

This command dumps everything in a single file, which is not workable, so all current backups mostly useless. Fortunately the old drive is solid though the machine is dead.


Auto increment id column vs index VARCHAR columns I'm wondering the differences or comparisons of having an Auto Increment int Id column or an indexed VARCHAR(32) column. Which one would be faster if I had a second table that references the first table by joining? Like in the second table, would I store the Auto Increment INT id or the indexed VARCHAR column? What are the advantages or disadvantages, if any?

Which would be the more common practice? Create Auto Increment id column for almost every table? Or we can simply index a column that has a unique 36-char ID (like in SugarCRM) so the other tables can just use that when doing joins? I hope I explained my question fairly clear.

UUIDs are terrible when the table becomes too large to be cached.  (If your table will never get very big, you can ignore the rest of my rant.)

The problem comes with the randomness of UUIDs.  You fetch one row; it's in one place.  You fetch another row, it is extremely likely to be in some other block.  This leads to lots of I/O.

In contrast, AUTO_INCREMENT ids are inserted in roughly chronological order.  A bunch (say, 100) rows will land in one block before moving on to the next block.  This makes INSERTs efficient.  It often makes reads efficient, too -- because often you are looking for records of similar vintage -- such as with a date range.

Think of News articles.  Most people look at the latest news; the older records collect dust in the cache until they are bumped out.  So, the cache tends to keep the latest rows.  With UUIDs, old and new articles share the same block.

More on UUIDs.


Acceptable size for MS Access Database? I have developed an Access 2010 database.

It has three main tables:

HOUSES
id,
address,
number_stories,
garden,
parking,
imageAttachments (~1-4 per record),
etc...,
about 60 fields total

CONTACTS
id,
houses.id(fk),
contactType(owner, manager, etc),
contactEmail,
contactPhone

VISITS
id,
Contacts.id(fk),
Houses.id(fk),
visitDate,
visitNotes

It also has a bunch of small lookup tables for some of the  fields in the houses table.

Right now I have about 100 houses listed and only about 5-10 contacts and visits.
I'm expecting at its peak this database will hold 300-500 houses, with 3-5 contacts and visits for each.

Right now the database size is about 50 MB.  I realize I didn't compact/repair it while I was building it or initially populating it which might be one problem.  It seems to run very slow.  Has this database grown too much?  Is this a typical size for a database of this scope?

(I'm on  windows 7 with 8 gig RAM so I don't think my own computer is the issue)

I've had Access databases of 1.5 GB (7 years ago on a machine much weaker than what you've described) run fine - for some queries. It really depends on what you are querying and how it's indexed. Without knowing the details of the queries and reports you're running, I can't say if it's too slow or not (and you don't actually define slow: 10 seconds? 2 seconds? 3 hours?) . I know in the past that Access had a 2GB file-size limit. I don't know if recent versions have changed that.


how to test if XML = '' in sql server? I am working with sql server 2005 and I had a situation where many values can be passed in a parameter.

Based on this: Passing multiple values for one SQL parameter this procedure  uses XML as a parameter.

here is the code of the stored procedure:

CREATE PROCEDURE [DENORMV2].[udpProductBulletPointSelectByTier1NoteTypeCode] (
    @Tier1 VARCHAR(10),
    @LanguageID INT,
    @SeasonItemID VARCHAR(5) = NULL,
    @ListNoteTypeCode XML,
    @CacheDuration INT OUTPUT )
    WITH EXECUTE AS 'webUserWithRW'
AS

        SELECT  pbp.Tier1, pbp.LanguageId, pbp.NoteText, pbp.NoteTypeCode,
                pbp.NoteGroup, pbp.SortOrder
        FROM    dbo.ProductBulletPoint pbp

        WHERE   Tier1 = @Tier1
          AND   LanguageId = @LanguageID
          AND   (      SeasonItemId = @SeasonItemID
                  OR
                       @SeasonItemID is null
                )

          AND pbp.NoteTypeCode IN (

                 SELECT  NoteTypeCode=BulletPoint.NoteTypeCode.value('./text()[1]', 'varchar(50)')
                   FROM  @ListNoteTypeCode.nodes('/BulletPoint/NoteTypeCode') AS BulletPoint ( NoteTypeCode )

          )

SELECT  @CacheDuration = Duration
FROM    dbo.CacheDuration
WHERE   [Key] = 'Product'
GO

more info about this procedure here

this is an example of how it can be called:

declare @p5 int  set @p5=86400
exec DenormV2.udpProductBulletPointSelectByTier1NoteTypeCode
@Tier1=N'WW099',
@LanguageID=3,
@SeasonItemID=N'16AUT',
@ListNoteTypeCode=N'<BulletPoint><NoteTypeCode>GarmentComposition</NoteTypeCode><NoteTypeCode>FootwearAccessoryComposition</NoteTypeCode></BulletPoint>',
@CacheDuration=@p5 output  select @p5

Question:

what is the best way to find out whether or not the parameter @ListNoteTypeCode XML is empty?

what if they call this procedure like this:

declare @p5 int  set @p5=86400
exec DenormV2.udpProductBulletPointSelectByTier1NoteTypeCode
@Tier1=N'WW099',
@LanguageID=3,
@SeasonItemID=N'16AUT',
@ListNoteTypeCode=N'',
@CacheDuration=@p5 output  select @p5

as it was suggested here I could avoid the select altogether by testing the parameter @ListNoteTypeCode.

My main goal in this scenario is to retrieve the data in the best possible performance, since this procedure is not cached in the web-servers and is called over a million times a day.

You may check for NULL and nodes absence (exist method of xml type):

@ListNoteTypeCode is NULL OR @ListNoteTypeCode.exist('*') = 0

Your XPath can be more specific, if necessary:

@ListNoteTypeCode is NULL OR @ListNoteTypeCode.exist('/BulletPoint/NoteTypeCode/text()') = 0


Migrating Oracle Views to SQL Server In the process of retiring an Oracle 11g host for an HRIS database, I've been asked to migrate as many assets as possible to a SQL Server 2016 instance for read-only archival. The volume of table objects was nearly 30,000 according to the dba_tables listing. Use of the Import/Export wizard was immediately discounted and we don't have knowledge of BIML to build a SSIS-centric solution. We tried using Microsoft SQL Server Migration Assistant 7.10 for Oracle but the utility was non-responsive (probably stuck trying enumerate all the objects).

We did a crude workaround constructing SELECT * INTO <SQLTable> FROM OPENQUERY("SELECT * FROM...<Oracle Table>") queries with a linked server (painful, but effective) and were able to import all the tables.

How to move the views over, however, is our next challenge. Can we try to use the Migration Assistant to convert the views (how do we get past it choking on the object volume)? Are there meta views in the v$ schema or elsewhere that can at least export the DDL utilized so we can then edit with the newly-imported tables?

Option 1

The correct answer is:

git export

In other words, the DDLs should exist in a code repository.

Option 2

Second option, use SQL*Developer's cart feature.

You can drag+drop the VIEWS of interest and export out all of the DDLs at once.

Option 3

I believe the cart feature uses DBMS_METADATA.GET_DDL() behind the scenes.

You'd have to roll your own code that loops through all of the relevant VIEWS.

Alternatives

  The fastest way to do something is to not do it at all

  -- Thomas Kyte

In this case:  Don't migrate off of Oracle.

Most Oracle licenses are bought on a perpetual basis.  You can run Oracle until the end of time, you just can't upgrade/patch it after you stop paying "maintenance and support".

I would have upgraded the database to 18c (latest) and stop paying the "maintenance and support".

"long term access" would still be a problem.  I would at least investigate if 18c XE (not yet out) can accommodate all of the static data.  It is supposed to include Advance Compression.

Final Thoughts

Since you said this is for READ ONLY archive purpose, I'll only remind you:
 "Database agnostic code is a myth".


SQL Server 2008 - is "NT AUTHORITY\ANONYMOUS LOGON" login "mandatory"? My spider senses are tingling when I found this "NT AUTHORITY\ANONYMOUS LOGON" account on a SQL Server logins. I tried to research the matter and I even have a book about SQL Server management. Also all the web searches I've done seem to relate only to login problems. The account is not on the local SQL express installation that I have, so I don't really know if someone added it there and if so, why.

Anyways my take on this is as follows: This account seems to allow login for every people that are in my domain. This just seems wrong and I want to shut it down. My concern is, that if I shut down this account, will applications start to fail login ? Is user/password login considered "anonymous" in this regard ? In other words, do other than AD-accounts need this for the login to work ?

I wish I could just test it, but it could do some damage in production environment.

This account seems to allow login for every people that are in my domain

That is actually incorrect. It allows access to anyone that cannot be authenticated. Members of your domains will successfully authenticate and they will be rejected.

On a serious note: there is absolutely ZERO reasons to have this login enabled. It was added intentionally to your system by somebody, this is not a default configuration. If I'd venture a guess, it would be that somebody tried to configure integrated authentication with impersonation on a web application and did not understand the requirement for 'double hop' constrained delegation. Needless to say, the 'solution' is a huge security hole that requires immediate action. You need to identify the application that uses this login and fix the authentication as appropriate (the link contains the primer on how to set up constrained delegation).


Handling MySQL length=22 and decimals=31 I'm using MySQL's C api to determine the maximum possible value that can be stored in a specific field.

I have the following table:

CREATE TABLE test (
    f1 double(4,2),
    f2 double
)

When I get the field information for f1 and f2 using the API, here is what I see:

f1: { [...], length = 4, decimals = 2 }

f2: { [...], length = 22, decimals = 31 }

I understand that the maximum value that f1 can store is 99.99 (total length = 4, so 4-2=2 digits before the decimal and 2 digits after the decimal).

Is it safe to assume that if a column type is DOUBLE, its length is 22 and its decimal is 31, then there is no (m, d) specified? If so, then can we also assume that the maximum value this column can store is 1.7976931348623157E+308 (as per the docs)? Can this value ever change between different machines/operating systems?

Do not use (m,n) on FLOAT or DOUBLE.  It is virtually useless and leads to double-roundings.

DOUBLE is an IEEE-754 "Double", which includes 53 significant bits and a range that tops out at that number given (over 1.E+308).  This is universal to nearly all languages, and machines.

There are virtually no machines / OSs today that deviate from IEEE754 in the representation of numbers.  In theory, you could see differences, but I doubt that you will.

The "22" in information_schema.COLUMNS.numeric_precision may come from how many characters it takes to display (but even that does not make sense).  I don't know where you got the "31".


Modelling Relationships in an E-R Diagram, Table or Foreign Key? Should a relationship in an E-R diagram be created in the database as a table or a foreign key?

For example:

  a tutor lectures a class

I see it as three possibilities:

there is a "lectures" table showing this relationship, containing
tutor_id and class_id, both as foreign keys
Or "tutor_id" is a foreign key in the class table
Or "class_id" is a foreign key in the tutor table

I say no to #3, because a lecturer can take multiple classes, and then there would be multiple entries in the lecturer table for the same lecturer (breaking 3NF?).

But I can see an argument for both #1 and #2. I imagine that both might be allowed, but that #1 would be over-normalised (is there such a thing?), and that #2 is most practical. #1 has two joins to get the lecturers details, using a potentially unnecessary table.

If when creating an E-R diagram you put in the relationships, then you have the table structure for modelling that relationship.

For what it's worth, I would use approach #2, but the idea of going a step further with #3 came to me today while drawing a diagram, and I wonder if it is worth the extra effort.

Your first option works best when there is the possibility that a lecture can have multiple tutors or when a tutor can teach multiple classes. Your second option works best when you can guarantee that a class will only ever have a single tutor at any one time (otherwise you would need multiple entries in your class table to accommodate extra tutors). The third one will only work if you can guarantee that a tutor will only ever teach one class (the opposite of the second scenario).

If you want/need maximum flexibility then go with option 1. However, you should choose the option that best suits your needs based on the descriptions of the types I have given above.

You can indeed over-normalize a database, and in some cases a de-normalized structure may suit the requirement better - but I don't think this is one of those times.

I hope this helps you.


Forcing Flow Distinct I have a table like this:

CREATE TABLE Updates
(
    UpdateId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    ObjectId INT NOT NULL
)

Essentially tracking updates to objects with an increasing ID.

The consumer of this table will select a chunk of 100 distinct object IDs, ordered by UpdateId and starting from a specific UpdateId. Essentially, keeping track of where it left off and then querying for any updates.

I've found this to be an interesting optimization problem because I've only been able to generate a maximally optimal query plan by writing queries that happen to do what I want due to indexes, but do not guarantee what I want:

SELECT DISTINCT TOP 100 ObjectId
FROM Updates
WHERE UpdateId > @fromUpdateId

Where @fromUpdateId is a stored procedure parameter.

With a plan of:

SELECT <- TOP <- Hash match (flow distinct, 100 rows touched) <- Index seek

Due to the seek on the UpdateId index being used, the results are already nice and ordered from lowest to highest update ID like I want. And this generates a flow distinct plan, which is what I want. But the ordering obviously isn't guaranteed behavior, so I don't want to use it.

This trick also results in the same query plan (though with a redundant TOP):

WITH ids AS
(
    SELECT ObjectId
    FROM Updates
    WHERE UpdateId > @fromUpdateId
    ORDER BY UpdateId OFFSET 0 ROWS
)
SELECT DISTINCT TOP 100 ObjectId FROM ids

Though, I'm not sure (and suspect not) if this truly guarantees ordering.

One query I hoped SQL Server would be smart enough to simplify was this, but it ends up generating a very bad query plan:

SELECT TOP 100 ObjectId
FROM Updates
WHERE UpdateId > @fromUpdateId
GROUP BY ObjectId
ORDER BY MIN(UpdateId)

With a plan of:

SELECT <- Top N Sort <- Hash Match aggregate (50,000+ rows touched) <- Index Seek

I'm trying to find a way to generate an optimal plan with an index seek on UpdateId and a flow distinct to remove duplicate ObjectIds. Any ideas?

Sample data if you want it. Objects will rarely have more than one update, and should almost never have more than one within a set of 100 rows, which is why I'm after a flow distinct, unless there's something better I don't know of? However,  there is no guarantee that a single ObjectId won't have more than 100 rows in the table. The table has over 1,000,000 rows and is expected to grow rapidly.

Assume the user of this has another way to find the appropriate next @fromUpdateId. No need to return it in this query.

The SQL Server optimizer cannot produce the execution plan you are after with the guarantee you need, because the Hash Match Flow Distinct operator is not order-preserving.

Though, I'm not sure (and suspect not) if this truly guarantees ordering.

You may observe order preservation in many cases, but this is an implementation detail; there is no guarantee, so you cannot rely on it. As always, presentation order can only be guaranteed by a top-level ORDER BY clause.
Example
The script below shows that Hash Match Flow Distinct does not preserve order. It sets up the table in question with matching numbers 1-50,000 in both columns:
IF OBJECT_ID(N'dbo.Updates', N'U') IS NOT NULL
    DROP TABLE dbo.Updates;
GO
CREATE TABLE Updates
(
    UpdateId INT NOT NULL IDENTITY(1,1),
    ObjectId INT NOT NULL,

    CONSTRAINT PK_Updates_UpdateId PRIMARY KEY (UpdateId)
);
GO
INSERT dbo.Updates (ObjectId)
SELECT TOP (50000)
    ObjectId =
        ROW_NUMBER() OVER (
            ORDER BY C1.[object_id])
FROM sys.columns AS C1
CROSS JOIN sys.columns AS C2
ORDER BY
    ObjectId;

The test query is:
DECLARE @Rows bigint = 50000;

-- Optimized for 1 row, but will be 50,000 when executed
SELECT DISTINCT TOP (@Rows)
    U.ObjectId
FROM dbo.Updates AS U
WHERE
    U.UpdateId > 0
OPTION (OPTIMIZE FOR (@Rows = 1));

The estimated plan shows an index seek and flow distinct:

The output certainly seems ordered to start with:

...but further down values start to go 'missing':

...and eventually:

The explanation in this particular case, is that the hash operator spills:

Once a partition spills, all rows that hash to the same partition also spill. Spilled partitions are processed later, breaking the expectation that distinct values encountered will be emitted immediately in the sequence they are received.

There are many ways to write an efficient query to produce the ordered result you want, such as recursion or using a cursor. However, it cannot be done using Hash Match Flow Distinct.


Azure SQL Database and Database Maintenance If SQL Agent isn't available for Azure SQL Database then is it possible to maintain (indexes, statistics etc) the databases with Ola Hallengren's maintenance scripts, for example?

Once you have created an Azure SQL Database you can use all tools listed on the following image to provide maintenance:

Regarding to Ola Hallengren's maintenance scripts, you can install them using psdbatool on a Managed Instance

Learn more about psdbatool here.

You can also install them manually.

And run them.

EXECUTE dbo.IndexOptimize
@Databases = 'mydbnamehere',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

You may also need Azure Automation to schedule execution.


Is it possible to install a single instance of sql server on a server with 2 NICS and 2 different subnet? I have a server that has 2 NICS and each is connected to 2 different subnet.  Is it possible to connect to this Sql server in each of the different subnet?  If so, what special procedure do I have to do to configure the Sql Server?  I am using sql server 2008.  Thank you!

By default SQL Server will bind listen to all addresses, so it will be visible from all subnets. You can also explicitly configure SQL Server to listen on only one or the other of the NICs, see CREATE/ALTER ENDPOINT. IT can even listen multiple times on each address (see How to: Configure the Database Engine to Listen on Multiple TCP Ports) usually done for affinitizing clients to hard or soft NUMA (see How to: Map TCP/IP Ports to NUMA Nodes).

Long story short: you don't have to do anything.


2 Different Oracle DB Version on same server Is it possible to install and run 2 different oracle version(11g and 12c)software and db on same server?
is it possible then how we can? and if not then why?

Thanks

Rahul Joshi

Yes, it is possible.

https://docs.oracle.com/database/121/NTDBI/intro.htm#NTDBI2648

  Oracle Database supports multiple Oracle homes. You can install this
  release or previous releases of the software more than once on the
  same system, in different Oracle home directories. This allows
  flexibility in deployment and maintenance of the database software.
  For example, it enables you to run different versions of the database
  simultaneously on the same system...