Guarantee SQL Server Identity Columns that two consecutive values differ by constant increments? I posed a question about Denali sequences here and another question about emulation of identity columns in Oracle Global Tables here.

I always thought that I could trust that identity values increment by constant increments. On the other side, I know that using sequences I can never be sure that there are not some gaps in them.

Is it time to adapt to gaps in sequences or will it be better to stay with identity columns in temporary tables?

You can't guarantee contiguous or consecutive identity values in SQL Server

An insert of multiple rows won't guarantee this. See Do Inserted Records Always Receive Contiguous Identity Values.
The current identity value stays incremented on rollback (either explicit ROLLBACK or implicit due to a CONSTRAINT error).

Also:

Loading a temp table doesn't guarantee consecutive values. Saying that, this is harder to prove, and works in practice (as many folk rely on it), but be warned...

You have to adapt to gaps in your numbering, however generated.


SQL current timestamp I have made a featureclass for a utility company where they can register measures set up to mitigate problems with rats, called "mitigation measure". From this feature class it is possible to state if the current mitigation measure has been removed or not. This field is named "Removed" and it is a domain value with possibilities "Yes", "No" and "Not set".

I now wish to create a sql trigger that can update a time field called "DateRemove" with the current timestamp for the moment the utility company changes the field "Removed" from "No" to "Yes". But only when this exact change happens, not when other changes occur within the feature class.

I changed a few things so that it ended up like this:

Create TRIGGER [sonfor_ags].[trg_date] ON [sonfor_ags].[MitigationMeasure]
AFTER UPDATE
AS BEGIN
SET NOCOUNT ON;

    UPDATE t SET DateRemove = CURRENT_TIMESTAMP
    FROM [sonfor_ags].[MitigationMeasure] AS t
    INNER JOIN inserted AS i ON t.OBJECTID = i.OBJECTID
       AND i.Removed = 1; END GO

and now it works perfectly. Thank you very much


Slony Replication in PostgreSQL Objective : Set up two DB server if first server down then second can be up and running provided condition is that at evening time both server need to sync. with each other

I am new in DBA and we are using PostgreSQL 9.6 and thinking to use PostgreSQL 10.0. We have regular data in tables along with that we have large spatial data (GIS related data having 'postgis' extension) on Windows Platform

We checked few replication tools in which 'Slony' tool is comes with default installation hence we want to use for replication.

we are checking this link for reference as well as documentation.

If anyone knows proper technical article including required steps then it would great!!

I would use some proxy in front of postgresql servers.
I advise using HAProxy to proxy tcp traffic.

Example configuration might look more or less like this:

defaults
    mode tcp
    timeout connect 30s
    timeout client  90s
    timeout server  90s

frontend PGSQL-FRONT
    mode tcp
    bind *:5432
    default_backend PGSQL-BACK

backend PGSQL-BACK
    mode tcp
    balance leastconn
    option tcp-check
    tcp-check connect port 5432
    server s1 10.0.0.101:5432 check
    server s2 10.0.0.103:5432 check backup


How to disable mongodb shell logs? Whenever we connect to mongo thru shell, we get some logs. I just want the query result not the other logs except the failure message. Is there any way to configure that?
MongoDB shell version v3.6.11
connecting to: mongodb://localhost:27070/test?authMechanism=MONGODB-X509
2020-04-10T21:40:59.305+0000 I NETWORK [thread1] Starting new replica set monitor for
2020-04-10T21:40:59.516+0000 W NETWORK [ReplicaSetMonitor-TaskExecutor-0] SSL peer certificate validation failed: self signed certificate in certificate chain
2020-04-10T21:40:59.519+0000 W NETWORK [thread1] SSL peer certificate validation failed: self signed certificate in certificate chain
2020-04-10T21:40:59.586+0000 I NETWORK [thread1] Successfully connected to localhost:27070 (1 connections now open to localhost:27070  with a 5 second timeout)
2020-04-10T21:40:59.586+0000 I NETWORK [ReplicaSetMonitor-TaskExecutor-0] Successfully connected to localhost:27070  (1 connections now open to localhost:27070  with a 5 second timeout)
2020-04-10T21:40:59.653+0000 I NETWORK [thread1] changing hosts to ....
2020-04-10T21:40:59.865+0000 W NETWORK [ReplicaSetMonitor-TaskExecutor-0] SSL peer certificate validation failed: self signed certificate in certificate chain
2020-04-10T21:40:59.868+0000 W NETWORK [thread1] SSL peer certificate validation failed: self signed certificate in certificate chain
2020-04-10T21:40:59.932+0000 I NETWORK [ReplicaSetMonitor-TaskExecutor-0] Successfully connected to localhost:27070  (1 connections now open to localhost:27070  with a 5 second timeout)
2020-04-10T21:41:00.007+0000 W NETWORK [ReplicaSetMonitor-TaskExecutor-0] Failed to connect to localhost:27070, in(checking socket for error after poll), reason: Connection refused
2020-04-10T21:41:00.019+0000 W NETWORK [ReplicaSetMonitor-TaskExecutor-0] SSL peer certificate validation failed: self signed certificate in certificate chain
2020-04-10T21:41:00.021+0000 I NETWORK [ReplicaSetMonitor-TaskExecutor-0] Successfully connected to localhost:27070  (1 connections now open to localhost:27070  with a 5 second timeout)
2020-04-10T21:41:00.032+0000 W NETWORK [ReplicaSetMonitor-TaskExecutor-0] SSL peer certificate validation failed: self signed certificate in certificate chain
2020-04-10T21:41:00.032+0000 I NETWORK [ReplicaSetMonitor-TaskExecutor-0] Successfully connected to localhost:27070   (1 connections now open to localhost:27070  with a 5 second timeout)
Implicit session: session { "id" : UUID("0a2c342e-6afe-4d1e-zz98-49110d4a1767") }
MongoDB server version: 3.6.8
2020-04-10T21:41:00.471+0000 E QUERY [thread1] Error: don't know how to show [collections()] :
shellHelper.show@src/mongo/shell/utils.js:997:11
shellHelper@src/mongo/shell/utils.js:750:15
@(shellhelp2):1:1
bye

You can use the --quiet option to suppress output including the mongo shell version and startup warnings during the connection process.
Those startup warnings are usually issues an administrator would want to be aware of, but you may prefer to suppress them for a development environment or for running scripts. For a production system you would be best addressing the reported issues to remove future startup warnings.
For example, connecting to a 3.6 deployment with a 4.4 shell:
$ mongo
MongoDB shell version v4.4.3
connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb
Implicit session: session { "id" : UUID("f9219e8d-01ff-46e6-9e9d-b4de68207bdf") }
MongoDB server version: 3.6.17
WARNING: shell and server versions do not match
---
The server generated these startup warnings when booting:
2021-02-04T21:57:55.555+1100 I CONTROL  [initandlisten]
2021-02-04T21:57:55.555+1100 I CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.
2021-02-04T21:57:55.555+1100 I CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.
2021-02-04T21:57:55.555+1100 I CONTROL  [initandlisten]
2021-02-04T21:57:55.555+1100 I CONTROL  [initandlisten]
2021-02-04T21:57:55.555+1100 I CONTROL  [initandlisten] ** WARNING: soft rlimits too low. Number of files is 256, should be at least 1000
---

Adding --quiet and connecting to the same MongoDB deployment:
$ mongo --quiet
>

The warnings being suppressed in this example indicate:

My mongo shell version (v4.4.3) is much newer than my server version (v3.6.17). Mixing these versions may have unexpected outcomes, particularly given so many intervening MongoDB releases. For a local installation I normally expect these versions to be identical, and for production usage I would want the major versions to be matching (i.e. both v4.4.x or v3.6.x).
Access control is not enabled on this deployment, so anyone can connect with full access to read & write data. This is a serious issue for a production deployment, but I would follow the MongoDB Security Checklist for any deployment where you are concerned about securing your data.
Resource limits are too low, so this deployment may run into problems with too many connections or open files. I would certainly fix this warning for a production environment, but also address for a development environment to avoid unexpected interruptions.


ORA-39083: Object type INDEX_STATISTICS failed to create with error I am trying to restore Oracle DB from a backup taken recently. While restoring I got the following error:

Failing sql is:

DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   NV VARCHAR2(1);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';  stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';BEGIN  DELETE
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-01403: no data found
ORA-01403: no data found

I searched in the net for some possible solution, one of them suggested to exclude the statistics(EXCLUDE=INDEX_STATISTICS) while exporting or importing the database.
Please let me know if it's safe to exclude statistics while restoring a recently crashed/corrupted database.

Excluding statistics is safe in the sense that it doesn't affect the actual data you're importing. You can always recalculate the statistics in the restored database.

If you need that restore to do performance debugging on specific queries, that's going to be a problem - you'd want to have exactly the same statistics as for the source database for your tests to be realistic. (But you can always re-export and import the statistics specifically if you need that.)

That being said, nothing's really "safe" with a corrupt database, and that type of error "shouldn't happen". It's possible I guess that the export you're running on failed partially. Check the export log file for errors too.


What are the different ways to keep track of active and archived data? I'm looking for different ways to keep track of both active and archived data so I can pro and con them.

The system:
I have a computer with a database on it. The database has several tables in it; one of which contains a list of users that can use the computer; and several tables for auditing (user 1 did this, user 2 did that, etc). This database is a slave of a master database in which a Content Management System is used to say, add a new user and see reports on what user did what.

Example:
As stated above, I have a table (lets call it users) that keeps track of all the users that are allowed to use the computer. As time goes by users will be added and removed. The problem is the audit tables keep track of a user ID so if the user is removed I lose the user information because the rows can't be joined. One idea I had was to use MySql's triggers so that if a user is added, an insert trigger is triggered and inserts a copy of the data to an 'archived' user table (lets call it users_archive). That way the computer can use users to determine if the user has permission to use it and reports can use users_archive for reports.

This seems like the easiest and most simple way to do it, but I can't find any other ways via google search to see if there are any other ways to do something like this.

The two options that spring to mind avoiding triggers are:

Flag the deleted rows as such with an extra field, and update all your existing code to deal with it. This is "simple" but will touch a lot of your code so can be a bug source. Delete is not delete, mearly "set is_achived flag".
Flag the deleted rows in the table, and have views for active and archived data. This avoids changing existing code as you can give the combined table a new name (users_all) and the view of active users (SELECT <fields> FROM users_all WHERE is_archived=0) the name of the old table. Code such as audit reports simple need to query users_all directly. You will need to run a few tests to make sure mysql's query planner handles this efficiently though - I've had it work well is MSSQL but don't use mysql myself so have no expectations one way or the other about it's QP's intelligence.
Separate tables for active and archived. The delete operation then becomes an insert into archived and a delete from active (be sure to wrap this in an explicit transaction so if there is an error half way through you don't lose records completely or end up duplicating them). For code that needs to see both subsets, either UNION directly in the views/procs or have a view that is a UNION of the two tables and use that. This is likely to be less efficient than the above for those code paths that do need to deal with both subsets, but will not affect other areas of the codebase so may be preferable. One key disadvantage with this option is not being able to use the two tables in FK relationships - you can get around this by adding a third table containing just the identifiers for each row, then all tables that contain links to users (including the active users and archived users tables) would use that as the FK source - the only extra work in your business logic as a result would be to create that row before creating the row in the users table.

Triggers can be very powerful, but you need to design them very carefully as they can be a performance problem if not, and of course silent things happening can be a problem when bugs to arise as it can be difficult to see everything that is going on at first glance. they can greatly simplify some processes though, creating easier to maintain structures as a result, and sometimes having a bit of structural logic in the DB is far more efficient than having the next layer up deal with it, so never discount them out of hand. In this instance though, I'd stick with one of the simple boolean flag options.


MySQL: SELECT with SUM() and COUNT() as new columns What is the correct SELECT statement to produce this desired output?

+------------+----------+-------------+-----------------+
| QtyOfParts | FamilyID | NumInFamily | TotalQtyOfParts |
+------------+----------+-------------+-----------------+
  28           1          3               79
  32           1          3               79
  19           1          3               79
  28           2          2               66
  38           2          2               66
  46           3          4              151
  30           3          4              151
  51           3          4              151
  24           3          4              151
...more rows...

'QtyOfParts' and 'FamilyID' are existing columns in the table.

'NumInFamily' is a calculated column for display/report; its
value is the COUNT() of each unique FamilyID. E.g., FamilyID
= 1 has 3 in its family; FamilyID = 2 has 2 in its family;
FamilyID = 3 has 4 in its family.

'TotalQtyOfParts' is a calculated column for display/report;
its values is the SUM() of the QtyOfParts for each FamilyID.
E.g., FamilyID = 1 has 28 + 32 + 19 = 79 parts; FamilyID =
2 has 19 + 28 = 66 parts.

Using 'SELECT ... COUNT(FamilyID) ... GROUP BY FamilyID' fails
to show each and every row -- instead it shows only one row
for each FamilyID. So for example instead of seeing three rows
for FamilyID = 1, only one row is shown. (Note: ROLLUP made no
difference.)

Using SUM() in the select simply tallied the sum of every single
value under QtyOfParts.

CREATE TABLE IF NOT EXISTS `Test`.`NoOps` (
  `ID_NoOps` INT NOT NULL,
  `QtyOfParts` INT(3) NOT NULL,
  `FamilyID` INT(3) NOT NULL,
  PRIMARY KEY (`ID_NoOps`))
ENGINE = InnoDB


grant execute on utl_file to user in Oracle 11g XE sqlplus connect <sys>/<pwd>
grant execute on utl_file to <user>
connect <user>/<pwd>
DESC utl_file

then i get:
ORA-24372: invalid object for describe

How can I access to UTL_FIL from other users in 11g xe? I already tried with public as well

The grant worked just fine. You can't DESC a package or procedure, which is why you're getting an error.

Documentation for UTL_FILE is here. It provides sample PL/SQL that shows you how to use the package.

A short code snippet that sums up its functionality is here.


Best way to migrate a database involved in bidirectional replication I have two SQL Server 2005 that are to be migrated to SQL Server 2008 R2.

The original pair of servers contain a database that are involved in bidirectional transactional replication between them. In other words SQL2005ServerA.DatabaseA and SQL2005ServerB.DatabaseA are involved in bidirectional transactional replication.

Now I have to move these databases to two new servers, call them SQL2008ServerA and SQL2008ServerB.

Is this pretty straight-forward? After disconnecting the application server, do I just backup and restore to the new servers, and then recreate the replication without snapshots?

Is this pretty straight-forward?

Yes and No .. depends on what is your downtime (maintenance window), database size or amount of data you are replicating (whole or subset of articles).

Script out current replication setup (both create and drop).

First make sure that you don't have any pending commands to replicate and no user connections while you are doing a cut-over to new server.

Below are some of the ways you can choose to ease your migration :

Easy and straight forward solution is to simply reset up replication. If the database is too big then you can reinitialize from backup as well followed by a snapshot and everything will be working fine.
Set up log-shipping of current publisher databases that you need to migrate to new server or even you can set up the new server as a subscriber to the old one. Then on the day of cutover, just bring the secondary (new server) database online (restore with recovery)  if log-shipping is used or just point your application to the new server and resetup replication.
A more tedious and possibly error prone solution (if you miss some details) is to backup the publication database and restore it to the new publisher server with the KEEP_REPLICATION flag enabled. Go to the subscriber servers and update the dbo.syssubscription tables with the new publishers information. Update the subscription tables with the new publisher information. Edit all the jobs on the distributor to point to the new publisher. Update the tables in the distributor to point to the new distributor.
Another way to do all of this is to stop SQL on the old server, copy all the system and user databases to the new SQL Server overwriting the system databases, then rename the new SQL Server so that it has the same name and IP as the old server. Then bring SQL on the new server online and everything should be working smooth.

Note:

By default, the generated scripts will have @sync_type = N'automatic' => the subscription will need initializing as a brand new subscription. Instead change it to @sync_type = N'none' or @sync_type = N'replication support only' => does not generate replication procs at subscriber.

Obviously, logins and users with appropriate permissions should be created before hand.

References :

Minimize Downtime When Moving to a New SQL Server Environment
SQL Server 2008 R2 Upgrade Technical Reference Guide


mysqld is not producing binary log I'm trying to understand why mysqld is not writing binary log anymore...

[root@ip-10-179-155-214 mysql]# grep ^log-bin /etc/my.cnf
log-bin=mysql-bin
[root@ip-10-179-155-214 mysql]# ll mysql-bin.*
-rw-rw----. 1 mysql mysql    28463 Jun 27 16:04 mysql-bin.000001
-rw-rw----. 1 mysql mysql  1038814 Jun 27 16:04 mysql-bin.000002
-rw-rw----. 1 mysql mysql      264 Jun 27 16:06 mysql-bin.000003
-rw-rw----. 1 mysql mysql      264 Jun 29 10:22 mysql-bin.000004
-rw-rw----. 1 mysql mysql 23751127 Jun 29 10:30 mysql-bin.000005
-rw-rw----. 1 mysql mysql      264 Jun 29 10:35 mysql-bin.000006
-rw-rw----. 1 mysql mysql 23755331 Jun 29 10:45 mysql-bin.000007
-rw-rw----. 1 mysql mysql      245 Jun 29 10:45 mysql-bin.000008
-rw-rw----. 1 mysql mysql      152 Jun 29 10:45 mysql-bin.index
[root@ip-10-179-155-214 mysql]# date
Thu Jul  3 17:55:13 EDT 2014
[root@ip-10-179-155-214 mysql]# ps auxww | grep mysqld
mysql     4131  0.0  0.0 115348  1688 ?        Ss   Jun29   0:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql     4787  0.1  8.9 3822688 331928 ?      Sl   Jun29  10:13 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/ip-10-179-155-214.ec2.internal.err --open-files-limit=8192 --pid-file=ip-10-179-155-214.ec2.internal.pid --socket=/var/lib/mysql/mysql.sock --port=3306
root     18996  0.0  0.0 112640   976 pts/2    S+   17:56   0:00 grep --color=auto mysqld
[root@ip-10-179-155-214 mysql]#

any ideas what am I missing?

* UPDATE *

MariaDB [(none)]> select @@global.sql_log_bin;
+----------------------+
| @@global.sql_log_bin |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> select NOW() - interval variable_value second from information_schema.global_status where variable_name='uptime';
+----------------------------------------+
| NOW() - interval variable_value second |
+----------------------------------------+
| 2014-06-29 10:45:14.000000             |
+----------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

Things I'd check:

Are any changes happening? Binary log doesn't include SELECT queries, only changes. Compare the timestamp on files like ib_logfile*.
Are replication filters excluding changes being made?
Are sessions making changes while SQL_LOG_BIN=0 is suppressing binary logging, either globally as @RolandoMySQLDBA suggests, or else per session?
Disk is full?
You are looking at binlogs in the wrong directory?


DBCC CHECKDB behavior with MAXDOP settings I am trying to understand the DBCC CHECKDB usage with MAXDOP settings-
If i run DBCC CHECKDB with MAXDOP = 0 on a SQL instance where my MAXDOP settings is 8, what is happening actually? Is all of my available say 98 schedulers would be used? or still just 8? or just few?
if i run DBCC CHECKDB with no MAXDOP say a default with server level MAXDOP 8 , should i see then all 8 schedulers used? or it can be less than 8
Why I am raising above questions is for same CHECKDB runs for some server it seems to use less schedulers and some server with same run all schedulers causing 100 % CPU spikes
Thank you

If i run DBCC CHECKDB with MAXDOP = 0 on a SQL instance where my MAXDOP settings is 8, what is happening actually? Is all of my available say 98 schedulers would be used? or still just 8? or just few?

Per the documentation, in this case "If MAXDOP is set to zero then SQL Server chooses the max degree of parallelism to use" just like it does with any other query where you override the default MAXDOP settings.  SQL Server can use up to the maximum of 64 processors when MAXDOP is effectively set to 0.

if i run DBCC CHECKDB with no MAXDOP say a default with server level
MAXDOP 8 , should i see then all 8 schedulers used? or it can be less
than 8

Then SQL Server chooses a max degree of parallelism up to 8 just like it does with a normal query with a MAXDOP override specified.
You should check to make sure the servers are the same, have the same instance level CPU settings, the same MAXDOP (check database level MAXDOP as well), and ensure that the database being checked is the same. All of these can affect the performance and parallelism chosen.


Connect to PostgreSQL from different host I am facing error while connecting to the PostgreSQL server from different host, I configured both postgresql.conf and pg_hba.conf  but unfortunately with no result.

So I have installed PostgreSQL on Debian from package, added user with createuser and useradd, then created db via createdb as my custom user as an owner. Afterwards I have set postgresql.conf with these values

listen_addresses = '*'
ssl = false

And added this line in pg_hba.conf

host    all      all     192.168.0.0/24  md5

Then I have restarted service and tried to connect from my machine via psql -U admin -d admin -h 10.10.30.88and I am getting this error

psql: FATAL:  no pg_hba.conf entry for host "192.168.10.195", user "admin", database "admin", SSL off

So have I forgotten about something?

Whole pg_hba.conf

local   all             postgres                                peer

local   all             all                                     peer
host    all             all             127.0.0.1/32            md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

host    all     all     192.168.0.0/24  md5
host    all     all     10.10.0.0/24    md5

ipconfig PostgreSQL server

eth0      Link encap:Ethernet  HWaddr 52:54:00:00:00:03
      inet addr:10.10.30.88  Bcast:10.10.30.255  Mask:255.255.255.0
      inet6 addr: fe80::5054:ff:fe00:3/64 Scope:Link
      UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
      RX packets:4001 errors:0 dropped:0 overruns:0 frame:0
      TX packets:2297 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:1000
      RX bytes:365126 (356.5 KiB)  TX bytes:306782 (299.5 KiB)

ipconfig from other VM in same network

eth0      Link encap:Ethernet  HWaddr 52:54:00:00:00:15
      inet addr:10.10.30.83  Bcast:10.10.30.255  Mask:255.255.255.0
      inet6 addr: fe80::5054:ff:fe00:15/64 Scope:Link
      UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
      RX packets:144749 errors:0 dropped:0 overruns:0 frame:0
      TX packets:18950 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:1000
      RX bytes:16160192 (15.4 MiB)  TX bytes:1323052 (1.2 MiB)

ipconfig from my machine

eth0      Link encap:Ethernet  HWaddr c4:7d:46:11:5c:ab
      inet addr:192.168.10.195  Bcast:192.168.10.255  Mask:255.255.255.0
      inet6 addr: fe80::c67d:46ff:fe11:5cab/64 Scope:Link
      UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
      RX packets:198498 errors:0 dropped:0 overruns:0 frame:0
      TX packets:78205 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:1000
      RX bytes:168115954 (168.1 MB)  TX bytes:10312744 (10.3 MB)
      Interrupt:20 Memory:f0600000-f0620000

I have my own KVM as well (not running)

virbr0    Link encap:Ethernet  HWaddr 2e:e4:de:95:07:1b
      inet addr:192.168.122.1  Bcast:192.168.122.255  Mask:255.255.255.0
      UP BROADCAST MULTICAST  MTU:1500  Metric:1
      RX packets:0 errors:0 dropped:0 overruns:0 frame:0
      TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:0
      RX bytes:0 (0.0 B)  TX bytes:0 (0.0 B)

Wifi is not enabled

wlan0     Link encap:Ethernet  HWaddr ac:fd:ce:b3:dc:e7
      inet6 addr: fe80::aefd:ceff:feb3:dce7/64 Scope:Link
      UP BROADCAST MULTICAST  MTU:1500  Metric:1
      RX packets:32251 errors:0 dropped:0 overruns:0 frame:0
      TX packets:159 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:1000
      RX bytes:3268413 (3.2 MB)  TX bytes:21321 (21.3 KB)

The issue is probably due to this line:

host    all     all   192.168.0.0/24  md5

This (the 24) basically tells Postgres to allow connections from any machine that has IP of the form: 192.168.0.x. So, your 192.168.10.195 does not match the criteria. You can replace it with:

host    all     all   192.168.0.0/16  md5     -- any 192.168.x.x

or

host    all     all   192.168.10.0/24  md5    -- any 192.168.10.x

or

host    all     all   192.168.10.195/32  md5  -- only 192.168.10.195

depending on what range of IPs you want to allow.


Will repeated, identical expressions get processed again? I am new to SQL server and I was trying to use an alias in a where clause and then I realized the order of processing makes it hard to do that, so I used this:

SELECT DISTINCT * FROM (SELECT LEFT(fiscal,4) AS fiscalyear FROM
Transactions )      fiscalyears
WHERE fiscalyear >= '2009'

I could have used this too, as it works too:

SELECT DISTINCT LEFT(fiscal, 4) FROM Transactions
WHERE LEFT(fiscal, 4) >= '2009'

Now, the question I have about this is: Is one better than the other method? The query execution plan looks identical for both (I don't know how to read a query plan in depth at all, just saw that it looks similar in both of these cases).
This is just one small table with small number of rows but will there be noticeable differences with huge amounts of data?

SQL Server is usually pretty smart about not computing the same thing multiple times at the same level of scope. So you should only see one compute scalar in either variation.

In fact, even in this more complex case, SQL Server still manages to only require a single compute scalar, and all the costing is the same:

SELECT DISTINCT LEFT(fiscalyear,6)
FROM dbo.Transactions
  WHERE LEFT(fiscalyear,5) >= '2009'
  OR LEFT(fiscalyear,4)>'2008';

(And it should go without saying that the better approach - if fiscalyear is date/time - is to have a sargable where clause, like WHERE fiscalyear >= '20090101'. If it is a string, then like @ypercube mentions above, you don't need the left expression - WHERE fiscalyear >= '2009' does the same thing.)

But as a generic answer, if you ever have any doubts about this, run the two queries with actual execution plan turned on, and see if they're different. However, I will say that the subquery form is often used not to prevent an expression from being evaluated multiple times, but to avoid having to type it multiple times. And even if you haven't typed it multiple times, there are still cases where it can still be evaluated multiple times. So there is no absolute "this method is better than the other one."


Restore from .db folder for Analysis Services There is an objective to recover cube from long time ago retired MSAS instance. Issue I'm currently standing with is that actual .abf backups are now in a cold storage and recovering them would take a while; however, i do have filesystem backup of Data (as well as logs) folder of filesystem with .db folder (seemingly containing all .dim, .ds and .dsv files).

How feasible it is to attempt to recover cube from said folder rather then proceed with searching for .abf files?

Yes.  Attaching the database files for SSAS is a documented and supported operation.  But unlike SQL Server Database Engine I wouldn't count on it working if the installed version of Analysis Services doesn't match the version that was used to run the database initially.


Will DIFF backup size be reduced if we shrink the data files? In our Prod env, DIFF backup job was failing due to insufficient space in the drive ( Total free space 36 GB ). That means DIFF backup file was generating more than 36 GB.
I investigated and found that the DB size 80GB has 70GB of free space. I immediately cleared 60GB space from data files and then I was able to take DIFF backup successfully( DIFF Backup size generated 10 GB).

Given that backups only take backup of data inside the DB not free space, why did the above scenario happened?

A differential backup actually only reflects the changes that were made on your database since the last full backup; more specifically: the extents that were changed since your last full backup.

A reason for the differential backup still backing up the no longer used extents may be that you have deleted a bunch of data using TSQL DELETE and not dropped the tables involved.
From https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-server-2017:

  When rows are deleted from a heap the Database Engine may use row or
  page locking for the operation. As a result, the pages made empty by
  the delete operation remain allocated to the heap. When empty pages
  are not deallocated, the associated space cannot be reused by other
  objects in the database.

Since the pages remain allocated, they will also be included in the backup.

My guess is that a DBCC SHRINKFILE will deallocate the empty pages, thus decreasing the size of DIFFERENTIAL backups, however I was not able to confirm this.

Shanky's comment of course also is a valid question as backup compression surely will not store empty pages themselves but only a reference to them and therefore vastly make a difference in size for you (in case you have Enterprise Edition, though).


How to skip already created table while importing dump file in MySql Is there any way to skip already created tables while importing ? I am trying to import 2GB of database using command prompt but the operation is aborted by mistake. Now if i will do the import again it will drop each table and create it again, That will take very long time.

I want to skip those tables from import which is already created or can i start it from where it was aborted ? I am using this command

mysql -u root -p my_database_name < db_dump.sql

--ignore-table=my_db_name.my_table_name my_db_name > dump.sql


DENSE_RANK not working as expected I have a situation where I need to assign a unique "ID" for every unique address. Each row contains other columns as well, besides the "address" columns.
My query is:
SELECT umLocationID,
       arcs_name1,
       arcs_name2,
       arcs_addr1,
       arcs_addr2,
       arcs_city,
       arcs_state,
       arcs_zip,
       arcs_country,
       arcs_cust_class,
       RANK() OVER(PARTITION BY arcs_name1,
                                arcs_name2,
                                arcs_addr1,
                                arcs_addr2,
                                arcs_city,
                                arcs_state,
                                arcs_zip,
                                arcs_country
                   ORDER BY arcs_name1,
                            arcs_name2,
                            arcs_addr1,
                            arcs_addr2,
                            arcs_city,
                            arcs_state,
                            arcs_zip,
                            arcs_country) AS 'Rank_Address#',
       DENSE_RANK() OVER(PARTITION BY arcs_name1,
                                      arcs_name2,
                                      arcs_addr1,
                                      arcs_addr2,
                                      arcs_city,
                                      arcs_state,
                                      arcs_zip,
                                      arcs_country
                         ORDER BY arcs_name1,
                                  arcs_name2,
                                  arcs_addr1,
                                  arcs_addr2,
                                  arcs_city,
                                  arcs_state,
                                  arcs_zip,
                                  arcs_country) AS 'Dense_Rank_Address#'
  FROM #Cleanout_artstcid2

Both my RANK() and DENSE_RANK() are giving the same result, a value of 1 for every row of the query results. What am I doing wrong? I know there is a need to use DISTINCT with DENSE_RANK but I cannot use it given the requirements for my overall query. Here is a screenshot of what I am getting (partial columns).

Remove your PARTITION clause. The window functions are calculated within the PARTITION itself, and it sounds like you're trying to rank the data globally (i.e. uniquely rank the addresses globally).
Or adjust the PARTITION clause to reflect the window of what you want the rank to be calculated across. For example, if you wanted to rank the data within the field arcs_name1 then you'd use PARTITION arcs_name1 only (but leave your ORDER BY clause as is) and the ranking would only be within the grouping of each value of arcs_name1 (resetting it's counter between each unique value of arcs_name1).


What's the difference between master and slave nodes in MySQL Cluster? I use MySQL Cluster 7.1.15a.

I have 2 nodes, ndb_mgm -e shows:

[ndbd(NDB)]     2 node(s)
id=3    @X  (mysql-5.1.56 ndb-7.1.15, starting, Nodegroup: 0, Master)
id=4    @Y  (mysql-5.1.56 ndb-7.1.15, starting, Nodegroup: 0)

(ignore the starting state, i just started the cluster)

Node id 3 is Master

What does it mean ?
What is the difference between Master and Slave nodes ?

I connect using mysql node anyhow, so I don't choose to which data node to connect.

Do writes happens on the Master and reads on the Slave?
When I connect to the mysql node, does that happen transparently?

The data node marked as 'Master' in the output from the ndb_mgm show command is the coordinator of some cluster internal management tasks.  For example, it coordinates changes to the distributed dictionary due to DDL commands, and manages node join and leave transactions.  The 'Master' role is dynamic, and is generally assigned to the longest running data node in a cluster, only moving when that node fails.

The 'Master' role is completely unrelated to the 'Master/Slave' concept in MySQL replication, and also has no bearing on the replication of data between nodes within a cluster.  Perhaps it would be better if it were called 'Coordinator' or 'Leader'.  As you say, you do not need to worry about which data nodes are used by MySQLD, all are equivalent for most operations and it is handled automatically.


SQL SSMS want to shortcut "Edit View" I usually right click on a view or a stored procedure and then choose Script View -> Alter -> In New Editor Window.

Is there a way to shortcut this - either on Keyboard or on direct menu entry?

If you have a Menu key on your keyboard you can use it to navigate the context menus.  So [Menu],s,a,n will Script as Alter to New Query Editor Window.


Oracle: are objects created by sys in a schema the same as objects created by that user? For example, are there any differences between:

SCOTT@ORCL> create index a_idx on a(x);

and

SYS@ORCL> create index scott.a.idx on scott.a(x);

Apart form all the DOs and DONTs about using SYS in this case is  that "if you connect as sysdba, your schema name will appear to be SYS".

Phil's answer demonstrates it. A nice quote by Thomas Kyte on a similar question -

  In general, unless the documentation tells you, you will NEVER LOG IN
  as sys or system, they are our internal data dictionary accounts and
  not for your use. You will be best served by forgetting they exist.

  sysdba and sysoper are ROLES - they are not users, not schemas. The
  SYSDBA role is like "root" on unix or "Administrator" on Windows. It
  sees all, can do all. Internally, if you connect as sysdba, your
  schema name will appear to be SYS.

  In real life, you hardly EVER need sysdba - typically only during an
  upgrade or patch.

We keep saying the following so many times -

  Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
  SYS/SYSDBA is special
  SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
  SYS/SYSDBA does not act like any other user
  When you use SYS/SYSDBA Oracle deactivates some code path and activates others
  Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

  NEVER EVER use SYS/SYSDBA for anything that can be done by another
  user.  Use SYS/SYSDBA ONLY for something that can't be done by someone
  else.

You might also like to read this.


Rule of thumb: AUTOALLOCATE or UNIFORM size for tablespaces Is there a rule of thumb for creating a tablespace with extent management AUTOALLOCATE or UNIFORM? If the latter applies, how to estimate a reasonable extent size?

Even more, what should apply for default tablespaces like SYSTEM, SYSAUX, UNDO, and TEMP?

I agree with Tom Kyte's 2005+ assessment of this:

  I actually am a fan of system allocated extents now.  It grows the
  extent allocation size as the table grows.

  Autoallocate is not going to cause widespread fragmentation.

  IF you know PRECISELY how big the object is, will be or will grow by -- go
  ahead, do the math, feel  free to use uniform.  Else, use autoallocate...


Sql Server 2008 R2 Memory Caching , why doesn't it release the memory? i am working on SQL server 2008 R2. It has been allowed to take a maximum memory which is around 45 GB. In couple of days it caches everything and the limit is reached. We have other processing as well on the server. Why doesn't it release all the memory after it has finished the processing?

How can i know the reason of the issue which process is doing this caching? I could find out some of the query plans cached but 40 GB of caching after that other processes from the SQL server it takes too much around 47/48 GB which has been an headache.

For any RDBMS, the bottleneck for performance is (usually) disk performance.  Compared to all other parts of the computer, disk I/O is the most expensive in terms of time required.  To this end, RDBMS engines focus on operating on as much data as possible in memory, as RAM is much faster to read and write to than physical disk.  It also provides a safety mechanism for ACID compliance, so that only work that is completed successfully will be persisted to disk.  If anything is rolled back, either due to failure or manual intervention, it can easily be discarded from memory without affecting the physical storage.

This means that relational engines will try and keep as much data in memory for as long as possible.  To continuously push and pull objects from disk to memory is expensive and will hurt application performance.  There are times items need to be cycled out of memory, but this will happen based on a LRU algorithm that is designed to keep active data in memory.

It is possible to view information about what is in your cache.  Note that the memory pool for SQL Server will contain different objects.  Primarily it will be the buffer pool, which stores data objects, and the plan cache, which are the query execution plans.  You can utilize specific dynamic management views for this observation, which are used in Glenn Berry's DMV script.  Look at queries 39 and 55.

If you wish to restrict the amount of memory SQL Server can use, the only option you really have is to reduce your Max Memory configuration for your instance.  Be careful with adjusting this, though, as a reduced memory pool can hamper your performance based on the factors described above.


Create a New AWS RDS Instance of only one database from a snapshot I'm trying to Create a new AWS RDS SQL Server instance of only one database from a snapshot.

I've gone through the AWS documentation, but according to it when I restore from a snapshot it will create all the databases that were in it.

I wanted to just create a new instance with just one specific database from the snapshot.

I was hoping to achieve it using AWS SDK for .NET either using powershell or C#.

RDS Snapshots capture the entire server.

There isn't a way to restore only one database.


Need to Check if a Table Exists Before Deleting a Record I have the following SQL query in my Ruby app:

sql = "DELETE FROM `#{database}`.`table1` WHERE `same_id` = #{some_id};"

The problem is that in rare circumstances table1 might not exist. I need to construct this query so even if the table does not exist SQL does not throw an error.

How can I implement this?

You should probably use a stored procedure to to do this:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`DeleteByID` $$
CREATE PROCEDURE `test`.`DeleteByID` (db VARCHAR(64),tb VARCHAR(64),id_to_delete INT)
BEGIN
    DECLARE FoundCount INT;

    SELECT COUNT(1) INTO FoundCount
    FROM information_schema.tables
    WHERE table_schema = db
    AND table_name = tb;

    IF FoundCount = 1 THEN
        SET @sql = CONCAT('DELETE FROM ',db,'.',tb,' WHERE id=',id_to_delete);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

END $$

DELIMITER ;

All you do in your code is call the stored procedure. For example, to delete ID 128 from drupaldb.comments:

CALL test.DeleteByID('drupaldb','comments',128);

Give it a Try !!!

Another variation on this would be to sculpt the query directly:

set @given_db = 'drupaldb';
set @given_tb = 'comments';
set @given_id = 128;
set @good_sql = CONCAT('DELETE FROM ',@given_db,'.',@given_tb,' WHERE id=',@given_id);
set @evil_sql = 'SELECT 1';
SELECT IF(table_exists=1,@good_sql,@evil_sql) INTO @DeleteSQL
FROM
(
    SELECT COUNT(1) table_exists
    FROM information_schema.tables
    WHERE table_schema=@given_db
    AND table_name=@given_tb
) A;
PREPARE stmt FROM @DeleteSQL; EXECUTE stmt; DEALLOCATE PREPARE stmt;

NOTE: If the table does not exist, the query executed is SELECT 1.

Here is a sample that shows if the table does not exist:

mysql>     set @given_db = 'drupaldb';
Query OK, 0 rows affected (0.00 sec)

mysql>     set @given_tb = 'comments';
Query OK, 0 rows affected (0.00 sec)

mysql>     set @given_id = 128;
Query OK, 0 rows affected (0.00 sec)

mysql>     set @good_sql = CONCAT('DELETE FROM ',@given_db,'.',@given_tb,' WHERE id=',@given_id);
Query OK, 0 rows affected (0.00 sec)

mysql>     set @evil_sql = 'SELECT 1';
Query OK, 0 rows affected (0.00 sec)

mysql>     SELECT IF(table_exists=1,@good_sql,@evil_sql) INTO @DeleteSQL
    ->     FROM
    ->     (
    ->         SELECT COUNT(1) table_exists
    ->         FROM information_schema.tables
    ->         WHERE table_schema=@given_db
    ->         AND table_name=@given_tb
    ->     ) A;
Query OK, 1 row affected (0.00 sec)

mysql>     PREPARE stmt FROM @DeleteSQL; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Now, here is a sample where the table exists:

mysql> select count(1) from mysql.user where user='rolando';
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> grant all on *.* to 'rolando'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> select count(1) from mysql.user where user='rolando';
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> set @given_db = 'mysql';
Query OK, 0 rows affected (0.00 sec)

mysql> set @given_tb = 'user';
Query OK, 0 rows affected (0.00 sec)

mysql> set @given_id = 'rolando';
Query OK, 0 rows affected (0.00 sec)

mysql> set @good_sql = CONCAT('DELETE FROM ',@given_db,'.',@given_tb,' WHERE user=''',@given_id,'''');
Query OK, 0 rows affected (0.00 sec)

mysql> set @evil_sql = 'SELECT 1';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(table_exists=1,@good_sql,@evil_sql) INTO @DeleteSQL
    -> FROM
    -> (
    ->     SELECT COUNT(1) table_exists
    ->     FROM information_schema.tables
    ->     WHERE table_schema=@given_db
    ->     AND table_name=@given_tb
    -> ) A;
Query OK, 1 row affected (0.00 sec)

mysql> PREPARE stmt FROM @DeleteSQL; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.02 sec)
Statement prepared

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select count(1) from mysql.user where user='rolando';
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql>


MySQL DB Replication Okay so here's my scenario. I have two applications that share some tables from the same DB. Let's call them App1 and App2. Lets also assume the DB used here is called DB1. App1 and App2.

App1 is on the same machine as DB1. App2 is on a different server but connects to DB1 remotely via PHP. This setup sometimes slows down access to DB1 from App2 and causes timeouts.

I will be creating DB2 which will be a replica of DB1 and hosted on the same machine as App2. This DB2 should be automatically synched with DB1. Changes are only going to made on DB1, so just need to push changes to DB2 regulary.

How can I do this? I'm assuming the solution to do this will be using cron jobs and PHP but I do not know what code can help do this.

Thanks all!

I am hoping someone has already done something like this and can share what they did.

Setup master-slave replication between DB1 and DB2.
APP1 reads and writes to DB1.
APP2 reads from DB2.
APP2 writes (if it does) to  DB1.

Note that APP2 will read eventually consistent data and it will have to handle writes to DB1 itself. (I do not tend to like master-master replication setup and writes to both ends due to past experiences but it is also an option.)

You said no links but below this link includes steps for setting replication, you might want to refer.


How can I capture queries that fail on the SQL Server due to connection failures? How can I capture queries that fail on the SQL Server due to connection failures?

Profiler doesnt seem to capture if there is a connectivity loss.

This is scenario:

DB server running in Domain A
SQL Client running on a server in Domain B - runs a transaction that would typically take 15 seconds to complete.
Network connectivity breaks at 10th second between Domain A and Domain B
SQL client displays error message - network failure
Transaction not completed on the DB server. No records updated.

I believe you have to do this on application level and not on SQL Server.

Here is .NET way of inserting in to the event viewer: http://forums.asp.net/t/1503109.aspx/1


Occasional/intermittent, slow (10+-second) UPDATE queries on PostgreSQL table with GIN index The Setup

I am running PostgreSQL 9.4.15 on an SSD-based, quad-core Virtual Private Server (VPS) with Debian Linux (8). The relevant table has approximately 2-million records.

Records are frequently being inserted and even more frequently (constantly -- every few seconds at least) updated. As far as I can tell, I have all appropriate indexes in place for these operations to execute snappily, however, and the vast majority of the time they do execute instantly (in milliseconds).

The Problem

Every hour or so, however, one of the UPDATE queries takes an excessive amount of time -- like 10 seconds or more. And when this happens, it's usually like a "batch" of queries that get "blocked", all terminating at roughly the same time. It's as if one of the queries, or some other background operation (e.g., a vacuum) is blocking them all.

Schema

The table, items, has many columns, but I think the following are the only ones possibly relevant to the problem:

id INTEGER NOT NULL (primary key)
search_vector TSVECTOR
last_checkup_at TIMESTAMP WITHOUT TIME ZONE

And these are the relevant indexes:

items_pkey PRIMARY KEY, btree (id)
items_search_vector_idx gin (search_vector)
items_last_checkup_at_idx btree (last_checkup_at)

Likely Culprits

Finally, after rigging together a little script to dump the contents of pg_stat_activity (the list of all active Postgres connections/queries) whenever a "connection leak" warning is emitted to my log-file, I've narrowed down the possible culprit queries/columns (assuming the problem isn't external, like with a misbehaving VPS). These are, roughly, the kinds of queries that seem to appear again and again:

UPDATE items SET last_checkup_at = $1 WHERE items.id = 123245
UPDATE items SET search_vector = [..] WHERE items.id = 78901

Those are slightly paraphrased, but I truly doubt anything relevant is missing. Occasionally other queries (on other tables) appear as well, but those usually look like they were just "unlucky" to get caught in the mix.

Now, even though the first query (setting last_checkup_at) tends to appear most of the time, the query that sets search_vector seems to appear every time. (And in addition, there are probably many more instances of the first query being issued in general, making it more likely to just be there on happenstance.)

(I think I'm winnowing in on a solution here, but even if I have it in the bag I wanted to document the incident here for others... I've been mystified by this problem for months, before getting a chance to deep-dive.)

The problem seems to have been Postgres's "FASTUPDATE" mechanism.

FASTUPDATE is a setting available on GIN indexes which, when enabled, causes changes to the index (caused by UPDATEs and presumably INSERTs as well) to be "queued up". Then, once this "queue" becomes too large, the pending entries are properly integrated into the GIN index.

The aim of FASTUPDATE is (no surprise) to speed up index updates, but it unfortunately leads to an occasional UPDATE query being exceptionally slow. In my case, I found it preferable to take the hit up-front (mainly to avoid warnings of a "slow query" in my logs).

FASTUPDATE is apparently enabled by default and available since PostgreSQL 8.4. I was able to disable it like this:

ALTER INDEX items_search_vector_idx SET (FASTUPDATE=OFF);

At the time of writing, I have been running as such for almost a week with almost no slow queries. (Aside one query that I expect to take a long time, I've noticed little else.)

You may also find more pertinent information in a related thread from the Postgres mailing list. Interestingly, one of the Postgres devs (Tom Lane) suggests processing of the FASTUPDATE pending items "was not supposed to block concurrent insertions", but I'm not sure if that's correct; in my case I would see several queries get "backed up" and then finish all at once.


How necessary are primary and foreign keys when I truncate all the tables in my database daily? I'm fairly inexperienced at database design, so I am trying to plan my database beforehand as much as possible. This database is to hold information about a VMware environment.

The database has 9 tables currently. Currently the idea is to truncate most of the tables daily and then use LOAD DATA LOCAL INFILE to populate the tables from CSVs scraped from each VMware vCenter. I'm using auto-increment columns for primary keys, though I could use composite keys consisting of the object's ID and the vCenter name (the object's ID is only unique per vCenter) if I were more confident in my database knowledge.

Since most of the tables will be truncated daily, is there any benefit in using foreign keys rather than just using WHERE logic? Are there other questions I should be asking that I might not know to ask?

PRIMARY KEYs are important; use them.  Since there seems to be a 'natural' PK, use it instead of a surrogate AUTO_INCREMENT.

FOREIGN_KEYs are not a requirement.  They do, however, create helper INDEXes, but you could create those without FKs`.

If the data is changing every day, what would you do if an FK failed?

Using TRUNCATE, then LOAD leaves the table empty for a brief time; if that is a problem, do this:

CREATE TABLE x LIKE real;  -- or use a regular CREATE.
LOAD DATA ... INTO x;
RENAME TABLE real TO old, x to REAL;
DROP TABLE old;


Why does performing a delete on my partitioned view result in a Clustered Index Insert? I have a partitioned view that has the below insert trigger (poor mans partition).
When I perform a DELETE, I get the below query plan:

delete from factproductprice where pricedate = '20170725'

Trigger on the view:

ALTER TRIGGER [dbo].[factProductPriceDelete] ON [dbo].[FactProductPrice]
INSTEAD OF DELETE AS
BEGIN
  IF @@ROWCOUNT = 0 RETURN;

  DECLARE @PriceDate DATE
  SELECT @PriceDate = CAST(PriceDate AS DATE) FROM DELETED
  IF @PriceDate BETWEEN '20140101' AND '20141231'
  BEGIN
    DELETE FROM dbo.FactProductPrice2014
    WHERE ProductId IN (SELECT ProductId FROM DELETED) AND SalesPriceSchemeId IN (SELECT SalesPriceSchemeId FROM DELETED) AND PriceDate IN (SELECT PriceDate FROM DELETED)
  END
  IF @PriceDate BETWEEN '20150101' AND '20151231'
  BEGIN
    DELETE FROM dbo.FactProductPrice2015
    WHERE ProductId IN (SELECT ProductId FROM DELETED) AND SalesPriceSchemeId IN (SELECT SalesPriceSchemeId FROM DELETED) AND PriceDate IN (SELECT PriceDate FROM DELETED)
  END
  IF @PriceDate BETWEEN '20160101' AND '20161231'

.... etc

INSTEAD OF triggers completely replace the triggering action.

The inserted and deleted pseudo-tables represent changes that would have been made, had the triggering statement actually executed. Row-versioning cannot be used for these triggers because no modifications have yet occurred, by definition.

SQL Server modifies the execution plan for the triggering DML statement when an INSTEAD OF trigger exists. Rather than modifying the affected tables directly, the execution plan writes information about the changes to a hidden worktable.

This worktable contains all the data needed to perform the original changes, the type of modification to perform on each row (delete or insert), as well as any information needed in the trigger for an OUTPUT clause.

The Insert in your execution plan represents writing to this hidden worktable. When you capture a post-execution plan for the statement, you will see this hidden worktable being used as the deleted and inserted pseudo-tables.

See my SQLPerformance.com article, Interesting Things About INSTEAD OF Triggers.


Problem with Restored DB I have 2 remote SQL Server 2014 instances: Server A with version 12.0.4100.1 and Server B with version 12.0.4213.0.

I have exported a DB from server B, copied and restored it to server A for tests. I have a query (really long) which I execute on both servers. The Server B returns the results in 2 min and server A in 1-2 sec. I have tried to export back the DB from Server A to server B, the same DB and nothing changes.

I have noticed the following on server B: if I export all the tables of the DB into a TempDB, then delete all the tables from DB and then import all the tables from TempDB back into DB the problem is gone. This happens for both the old (original) DB and the new (exported back from server A) one as well.

My problem: what can cause this "problem" and how to prevent it?

Firstly as @jonhg points out the servers can give you different execution plans as the tables and indexes are rebuilt. The server hardware (memory, cpu, hard drives) can be different and finally you don't have the same version of SQL server in both cases. Any of these would be sufficient to give different results.

Other queries could be interfering, as the cache on the servers can be different. All the answers to this are found here: http://download.red-gate.com/ebooks/SQL/eBOOK_SQLServerExecutionPlans_2Ed_G_Fritchey.pdf


Postgres Partitioning: Edge case issue on date column partitioning I am using Postgres 9.5

I have tables with date column. All tables are partitioned based on the date column.

Table setup:

Example of current partitioned tables are like below

ORDER (id, description, orderstatus, submitteddate) --> submitteddate default value is current date

ORDER_LINE (lineid, orderid, description, submitteddate) --> orderid is FK from ORDER(id)
(multiple order lines inside each order)

PLAN (planid, orderlineid, description, submitteddate) --> orderlineid is FK from ORDER_LINE(lineid)
(Plan generated for each order line)

Tables are partitioned like below

CREATE TABLE ORDER_NOV2016 (CHECK ( submitteddate < '2016-12-01' )) INHERITS (ORDER);
CREATE TABLE ORDER_DEC2016 (CHECK ( submitteddate < '2017-01-01' )) INHERITS (ORDER);

CREATE TABLE ORDER_LINE_NOV2016 (CHECK ( submitteddate < '2016-12-01' )) INHERITS (ORDER_LINE);
CREATE TABLE ORDER_LINE_DEC2016 (CHECK ( submitteddate < '2017-01-01' )) INHERITS (ORDER_LINE);

Failing Edge Case

Since partition for inserting data is decided based on submitteddate which is a current date, there will be a situation where an order comes at 2016-11-30 at 11:59PM and data in ORDER table is inserted in NOV2016 partition but data in ORDER_LINE and PLAN table is inserted on DEC2016 partition as by the time inserts are done, date may change in the system.

When I try to drop Nov2016 partition from all tables (child first due to FK constraint), ORDER_LINE and PLAN table drop partition might go through but ORDER table partition drop will fail as orderid from Nov2016 partition would be pointing to the DEC2016 partition data in other 2 tables.

How do I make sure that the orders inserted on date change still goes to same partition across all tables?

Added info (based on @dezso reply)

Dezso's transaction suggestions makes sense. But to make question concise, I left some details. With those details, the suggested solution might differ a bit.

Application supports 2 databases, Oracle and Postgres. For Oracle, partitioning has been implemented using Reference partitioning with partitioned ORDER table and child tables are referenced partitioned based on foreign keys.

For postgres, since there is no reference partitioning option like Oracle, each table was supposed to be individually partitioned using submitteddate. The plan was not to add submitteddate to each table but to use inheritance like below

ORDER_PARTITION(submitteddate date not null default CURRENT_DATE);
ORDER (id, description, orderstatus) INHERITS (order_partition);
ORDER_LINE (lineid, orderid, description) INHERITS (order_partition);
PLAN (planid, orderlineid, description)INHERITS (order_partition);

This way application will not insert submitteddate from the code to support Oracle table structure, instead for postgres its a default value which will get added automatically when a row is inserted in each partitioned table.

Therefore calculating single date at the beginning of the transaction would be difficult as this will require changes on Oracle side as well to add date column to each table.

Is there any other option that is possible?

The check constraints of your partitions are strange.  They don't exclude the possibility of an order from DEC2015 being inserted into the JAN2016 table.  This might not be an issue about inserting, but it makes constraint exclusion not working nicely.  Fix this by specifying a beginning date, too.

That aside, the problem of having an order and its lines in the corresponding partitions can be easily solved.  Things depend a bit on how the submitteddate is calculated.

First, in the simple case I would not add such a date to the order lines - the timestamp of the order usually defines the former.  However, if there is a possibility to add lines later, your problem is inherently unsolvable: you either cheat and register lines from the next month with an earlier date, or have lines for a given order in different partitions.

On the technical side, ensuring that all dates are from the same day is easy.  Use a transaction to add the order lines alongside the order (this makes sense in any case), and in this case CURRENT_DATE will return the date of the start of the transaction, even if the last insert flow over into the next month.


Job Information - When querying [msdb].[dbo].SYSJOBS_VIEW through linked server I am trying to build a report to query all my sql server jobs and thier status from the previous night's run.  In an effort to have just one report instead of a separate report for each server, I am using linked server connections to query each sql server from the report.

When I try to run the query through a linked server connection, I don't get the data for the column originating_server, in the [msdb].[dbo].SYSJOBS_VIEW, or any columns in this view.  This is so I know where the job is being run.

When I run the query on a local server, it runs fine and gives me the originating_server as expected.

Is this a permissions issue?  The user connecting has db_datareader on the msdb database on the linked server.

DECLARE @YEAR INT,@MONTH INT,@DAY INT,@DAY2 INT
DECLARE @DATEs VARCHAR(10),@DATES2 VARCHAR(10)
DECLARE @LENTGH VARCHAR(8)
SET @YEAR=YEAR(GETDATE())
SET @MONTH=MONTH(GETDATE())
SET @DAY = DAY(GETDATE())
SET @DAY2 = DAY(DATEADD(DAY,-1,GETDATE()))
SET @DATES =CONVERT(VARCHAR(10),GETDATE(),121)
SET @DATES2 =CONVERT(VARCHAR(10),DATEADD(DAY,-1,GETDATE()),121)

SELECT JOB.NAME
,JOBVIEW.ORIGINATING_SERVER AS SERVERNAME
,CASE
    WHEN JOB.ENABLED=1 THEN 'ACTIVE'
    WHEN JOB.ENABLED=0 THEN 'INACTIVE'
    END ACTIVE
,CASE
    WHEN LAST_RUN_OUTCOME=0 THEN 'FAIL'
    WHEN LAST_RUN_OUTCOME=1 THEN 'SUCCEED'
    WHEN LAST_RUN_OUTCOME=3 THEN 'CANCEL'
    ELSE 'UNKNOWN'
END STATUS
,LAST_OUTCOME_MESSAGE
,LAST_RUN_DATE
,NEXT_RUN_DATE
,LAST_RUN_DURATION
FROM [linkedserver].[msdb].[dbo].SYSJOBS JOB
LEFT OUTER JOIN [linkedserver].[msdb].[dbo].SYSJOBSERVERS JOBSERVER ON JOB.JOB_ID=JOBSERVER.JOB_ID
LEFT OUTER JOIN [linkedserver].[msdb].[dbo].SYSJOBSCHEDULES JOBSCH ON JOB.JOB_ID = JOBSCH.JOB_ID
LEFT OUTER JOIN [linkedserver].[msdb].[dbo].SYSJOBS_VIEW JOBVIEW ON JOB.JOB_ID = JOBVIEW.JOB_ID
WHERE LAST_RUN_DATE IN (REPLACE(@DATES,'-',''),REPLACE(@DATES2,'-',''))

When I give the user sysadmin, I get the information I need, but I don't really want this user to have sysadmin. Is there a specific permission I can grant to the users?

I tested your code by creating a linked server and following @Erik's comment grant the user 'view database state' and then 'view server state'.  Did not work.

If you backtrack the view by using sp_helptext of SYSJOBS_VIEW and sysoriginatingservers_view it uses an undocumented object named sysoriginatingservers.  It is not documented what permission is required to read from sysoriginatingservers.

Minimum permission I was able to get the SYSJOBS_VIEW.ORIGINATING_SERVER column value by adding the user as a member of these to database_role of MSDB database.

EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'taiobtest';
EXEC sp_addrolemember @rolename = 'SQLAgentReaderRole', @membername = 'taiobtest';

Another workaround is to replace your code with below by which you can avoid making the user member of SQLAgentReaderRole role.

UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))  AS SERVERNAME


How do I set MySQL's "character_set_server" and "collation_server" variables when creating a database? I'm using MySQL 5.5.37.  How do I set the default "" and "" variables of my database when initially creating my db?  I'm logged in as the root user but my command is not cutting it, as you can see below

mysql> create database if not exists my_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;
Query OK, 1 row affected (0.00 sec)

mysql> use my_db
Database changed
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | utf8              |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | utf8_bin          |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+

Notice that "character_set_server" is set to "latin1" and "collation_server" is set to "latin1_swedish_ci" despite what I specified in my intiail command. How do I change my initial command so that these values are utf8 and utf8_bin respectively?

Stick these in the [mysqld] section of your MySQL config file:

skip-character-set-client-handshake
collation-server=utf8_unicode_ci
character-set-server=utf8

The documentation for this is here.


I just upgraded PostgreSQL with yum on Amazon Linux, now PostgreSQL won't start. What do I do? I just ran yum update on my Amazon Linux instance as part of an upgrade to 2012.09 and it upgraded PostgreSQL from 9.1 to 9.2. Now PostgreSQL won't start. What do I do?

(This is a very incomplete question because it's just here to provide somewhere to post the answer).

Amazon's packages for PostgreSQL will upgrade from 9.1 to 9.2 as if that was a normal minor update. It isn't; see the PostgreSQL version policy, which makes it clear that the major version is "x.y", eg "9.1", "9.2", etc. This version policy may be weird and perhaps unwise, but it's also widely understood and something you would expect packagers to understand. The documentation for upgrading a PostgreSQL cluster explains this point very clearly.

The Amazon packages don't seem to get that, and just upgrade PostgreSQL. This is a very bad idea. Upgrades can involve downtime and backward-compatibility-breaking changes. C extension functions must be recompiled. A dump and reload may be required if pg_upgrade can't be used with your database. Even if pg_upgrade can be used, downtime can be significant.

See Amazon's severe PostgreSQL packaging blunder by Andrew Dunstan, and this bug report on the AWS forums.

Amazon has had a FAQ entry for this, but it's really the wrong answer. The right answer is "don't upgrade without asking!". Not only that, but doing a pg_upgrade can take some preparation and won't work for all databases. This is really bad advice. (They seem to have silently removed this FAQ entry now, that'll help those customers who're still updating from older versions!)

Nonetheless, pg_upgrade is your only easy option once this has happened. If you can't use it, you'll need to roll back to PostgreSQL 9.1, pg_dump your DBs and pg_dumpall --globals-only as well, then install 9.2 and load the dumps. See upgrading a PostgreSQL cluster


MYSQL - 'Select distinct' - different results on two exact versions of Mysql, databases synced Same versions of Mysql and the OS (Ubuntu), Databases synced fully.  Even the replica gives different results.

One mysql instance is showing 11 results (from a Select distinct statement) and all the other SYNCED db's are showing 12 results.

One the missing result server, if you change it to distinct by uid instead of *, then all 12 show up.  So the record is there it's just not showing up in the select query.

any idea's why this is happening?

mysql server version is 5.1.56

here is the select statement:

select distinct
bdtag3_.*
from
Company bdcompany0_
inner join
Company_Tag tags1_
on bdcompany0_.UID=tags1_.COMPANY_UID
inner join
Tag bdtag2_
on tags1_.TAG_UID=bdtag2_.UID
inner join
Tag bdtag3_
on bdtag2_.PARENT=bdtag3_.UID,
CompanyPostedStatus bdcompanyp4_
where
bdcompany0_.POSTED_STATUS_UID=bdcompanyp4_.UID
and bdcompanyp4_.IS_POSTED=1
and bdtag2_.TAG_TYPE_UID=14
order by
bdtag3_.DESCRIPTION

This is just a hunch, but make sure your collation settings are the same between the dbs.  I know you said they're synced, but check.

If you can't find anything there run each select into their own outfile

select bdtag3_.* into outfile '/tmp/db1.data' ....

and

select bdtag3_.* into outfile '/tmp/db2.data' ....

You have an order by so running a basic commandline diff should quickly highlight differences.

The pt-table-checksum tool is also handy for finding differences.


PL/SQL + after login seems Not see tnsnames.ora file on my PC I get the following error

when I login in by PL/SQL - please advice how to fix this?

Data character set (UTF8) and client character set (IW8MSWIN1255) are different character set conversion may cause unexpected results

you can set the client character set through the NLS_LANG env variable or NLS_LANG registry key in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE|KEY_OraClient10_home1

What do you mean "log in by PL/SQL"? Do you mean SQL*Plus? PL/SQL is a language used to write functions and procedures - you have to be logged in already before you can call them.

The message is fairly self-explanatory: if the client and database are using different character sets, results need to be converted from one to the other before being displayed at the client, which may cause "unexpected results" (like strange characters for example).

As the database is using UTF8 it makes sense to set the client side to use the same - how to do this depends on what platform your client is on.

On Windows, you can either set an environment variable or the NLS_LANG registry setting


What is the date function used to display month name from Date column in Teradata I have a table with Date column and I need to display Month name.

I found EXTRACT(MONTH) function in teradata to display the month_number but it does not serve my purpose. I can still use case statement on month_number to display the month name.

Can someone help me with a better approach?

The full name is returned by to_char(dateCol, 'Month')

'Month' -> 'October'
'MONTH' -> 'OCTOBER'
'month' -> 'october'
'Mon' -> 'Oct'
'MON' -> 'OCT'
'mon' -> 'oct'


Switching between databases with dynamic SQL I have a process which involves executing various commands between multiple databases - however, when I use dynamic SQL to change DB with 'use @var', then it doesn't actually change the database.

Executing this in [test_db]:

declare @currentDB varchar(max)
declare @sql varchar(max)

set @currentDB =  DB_NAME()
set @sql = 'use  [' + @currentDB +']'

use master

exec(@sql)

select  DB_NAME()

Returns [Master] as the current database name - if I put use [test_db] as a command, rather than dynamically, then it returns the correct name.

Is there a way to do this which will correctly switch between databases?

Sure, there is a way - there's always a way...

If you declare variable and store in it the database and the procedure to run, you can exec it, with parameters.

Example

use tempdb;

select db_name();

declare @db sysname = 'master.sys.sp_executesql';

exec @db N'select db_name()';

set @db = 'msdb.sys.sp_executesql';

exec @db N'select db_name()';

It is trivial to then pass a query with parameters to be run in any database

declare @proc sysname, @sql nvarchar(max), @params nvarchar(max);

select
  @proc = 'ssc.sys.sp_executesql'
, @sql = N'select top 10 name from sys.tables where name like @table order by name;'
, @params = N'@table sysname';

exec @proc @sql, @params, @table = 'Tally%'

I know this doesn't change database context in the main query, but wanted to demonstrate how you can conveniently work in another database in a safe parameterised way without too much bother.


How can I use `psql` without installing via brew I want to use the psql utility standalone, without installing postgress via brew or something (I have my reasons). I use psql version 9.5.3 on OS X, which was distributed with PostgressApp.

When I only distribute psql, I get the error:

dyld: lazy symbol binding failed: Symbol not found: _PQsslInUse
...
Expected in: /usr/lib/libpq.5.dylib

On most computers, /usr/lib/libpq.5.5.dylib is installed, but I use version 5.6. So then I tried distributing libpq.5.6.dylib in the same folder. This doesn't work, even when I set the $DYLD_LIBRARY_PATH variable.

Any pointers?

I fixed it here: https://github.com/PostgresApp/PostgresApp/issues/338

The problem was I used install_name_tool not 100% correctly, and I forgot to create aliases.


One to at most one relation or multiple null columns? I have a table with many attributes that can be grouped in logical groups, and thus the idea of putting these attributes in separate tables seems attractive.  The logical groups don't represent entities themselves.  When one attribute in the group is set, most of the rest of the attributes in that group also must be set (but not all).  The fields's type of the groups generally are VARCHAR(15-20).  There's no BLOB or TEXT fields either.

The logical groups don't represent subtypes, because they aren't mutually exclusive.

The entity is requested more for reading than for writing.  Hence a large table looks appropriate.  Also, this way the joins are avoided in queries.  The part of this approach I don't like is the large number of nullable fields.

Looking for Expert advice.

There is nothing wrong with nullable columns, even a lot of them, if that is what your data domain calls for.
On the other hand, the fact that you say these columns can be grouped logically implies to me that something else might be going on.
If they can be grouped logically because different sets of columns apply to different sets of rows, then you might have an entity-subtype situation.
Conversely, if columns can be grouped because they apply at different times, then you may have a normalization issue.  For example, if your columns are something like "January Sales", "February Sales", etc. these should be rows in a child table.
While there is nothing innately wrong with nullable columns, neither is there anything wrong with joining.  It's what RDBMS does for a living.
UPDATE:
Given additional information about the logical groups of columns:
There are two kinds of sub-typing that can be represented in a database using 1:1 relationships.  If the logical groups were mutually exclusive, then the parent entity could have what is known as a partitioning attribute that tells you which one of the subtypes is applicable.  However, without a partitioning attribute, it is possible to have zero, one or even multiple subtypes being applicable at the same time.
The same fundamental question applies then to what you do with this situation.
A good way to resolve it would be to look at the logical groups of columns.  Are the columns in logical group A the same as in logical group B - or are the totally different?  If they are different they might be best modeled in the single table with nullable fields.  If they are the same, then this might be a clue that they should be multiple child rows instead.
Another clue to look at is whether it makes sense that a logical group of columns could take on a life of its own and start attracting relationships from other tables.  If logical group B might sometime soon find itself with multiple child records from another table, then it might be a sign that it makes sense to promote that group to its own subtype style table.
One last thing to consider is physical implementation.  If a logical subgroup is very sparsely populated, you might be able to make a case for segregating these columns into another table to optimize physical storage.  This step shouldn't be done proactively.  This kind of optimization should be done when performance testing proves it is necessary.
If none of these things are true, then you are probably best off leaving the nullable columns in the original table.


what is the password to user mongodb when mongodb is installed in ubuntu? When I installed mongodb in ubuntu, user mongodb and group mongodb are created in /etc/passwd and /etc/group. But I have no idea of the password to these user and group. I have to run mongod as root user, which is not suggested. Someone suggests to change the owner of mongodb data and log files to mongodb:mongodb. But I have no idea how to run mongod as mongod?
Any idea?

Launch Shell as User:

If all else fails, on Ubuntu 14.04 LTS running on AWS, try this...

me@local $ ssh -i aws.pem ubuntu@foo
ubuntu@foo $ sudo -u mongodb bash   # OR whatever shell (zsh)
mongodb@foo $ mongod --dbpath /data/db --fork

OF COURSE, you'll add a much larger set of flags to the command. Note also that this assumes you're authenticated as ubuntu on an AWS EC2 instance. The first portion of each line to the left of the $ is the user@hostname

Invoke Command as User:

YMMV but this should work too...

mongodb@foo $ sudo -u mongodb mongod --dbpath /data/db --fork


UPDATE with LIMIT / OFFSET in PL/pgSQL function In my Postgres 9.2 database I've got the following SELECT, which will be used by a PL/pgSQL function:

SELECT
      id
FROM
      tablea
WHERE
      migrated = false;

I want to get 2000 records each time and perform something like the following:

CREATE or REPLACE FUNCTION migrate_data()
RETURNS integer AS
$$
declare
        row record;

BEGIN

FOR row IN EXECUTE '
        SELECT
              id
        FROM
              tablea
        WHERE
              migrated = false
'
LOOP

INSERT INTO tableb (id)
VALUES (row.id);

UPDATE tablea a SET migrated = yes WHERE a.id = row.id;

END LOOP;

RETURN num_rows; -- I want it to return the number of processed rows

END

$$ language 'plpgsql';

How can I tell the function to process 2000 records with every call? And it must start from where it left when on the next call.

Example:

select migrate_data(); -- id 0 to id 2000

select migrate_data(); -- id 2001 to id 4000

select migrate_data(); -- id 4001 to id 6000

select migrate_data(); -- id 6001 to id 8000

Etc.

It will be much simpler (and faster) to use cursors for that:

begin;
declare foo cursor for
    SELECT
          id
    FROM
          tablea
    WHERE
          mig = true;
fetch forward 2000 from foo;
fetch forward 2000 from foo;
-- etc
rollback;

About declare
About fetch


The way the data is stored, PostreSQL doesn't know what offset to go to look at for the data for int200 column on any given row, without knowing the sizes of all previous columns for that row.  So it has to loop through the null bitmap (at a minimum) of all earlier columns before it can know that most of them have zero size.

There is an optimization for cases where you are selecting a single column and that column is found to be null via use of the null bitmap.  But you are selecting two columns not just the column int199, so you don't trigger this optimization.  It is unlikely an optimization would be added for selecting multiple, widely separated columns.  You would need some heuristic to decide what makes the columns widely separated, and then everyone would pay the cost for running that heuristic, even the vast majority of the cases that can't benefit from it.  If you were willing to get your hands dirty with a bit of hacking, perhaps you could convince us otherwise.  But monkeying around with the internals has a steep learning curve.

If you have a practical use case for making this situation faster, perhaps you should check out Citus Data's column store extension.


How to write a query in SQL Server to find nearest values Let's say that I have the following integer values in a table

32
11
15
123
55
54
23
43
44
44
56
23

OK, the list can go on; it doesn't matter. Now I want to query this table and I want to return a certain number of closest records. Let's say I want to return 10 closest record matches to the numer 32. Can I achieve this efficiently?

It is in SQL Server 2014.

Assuming the column is indexed the following should be reasonably efficient.

With two seeks of 10 rows and then a sort of the (up to) 20 returned.

WITH CTE
     AS ((SELECT TOP 10 *
          FROM   YourTable
          WHERE  YourCol > 32
          ORDER  BY YourCol ASC)
         UNION ALL
         (SELECT TOP 10 *
          FROM   YourTable
          WHERE  YourCol <= 32
          ORDER  BY YourCol DESC))
SELECT TOP 10 *
FROM   CTE
ORDER  BY ABS(YourCol - 32) ASC

(i.e. potentially something like the below)

Or another possibility (that reduces the number of rows sorted to max 10)

WITH A
     AS (SELECT TOP 10 *,
                       YourCol - 32 AS Diff
         FROM   YourTable
         WHERE  YourCol > 32
         ORDER  BY Diff ASC, YourCol ASC),
     B
     AS (SELECT TOP 10 *,
                       32 - YourCol AS Diff
         FROM   YourTable
         WHERE  YourCol <= 32
         ORDER  BY YourCol DESC),
     AB
     AS (SELECT *
         FROM   A
         UNION ALL
         SELECT *
         FROM   B)
SELECT TOP 10 *
FROM   AB
ORDER  BY Diff ASC

NB: Execution plan above was for the simple table definition

CREATE TABLE [dbo].[YourTable](
    [YourCol] [int] NOT NULL CONSTRAINT [SomeIndex] PRIMARY KEY CLUSTERED
)

Technically, the Sort on the bottom branch shouldn't be needed either as that too is ordered by Diff, and it would be possible to merge the two ordered results. But I wasn't able to get that plan.

The query has ORDER BY Diff ASC, YourCol ASC and not just ORDER  BY YourCol ASC, because that was what ended up working to get rid of the Sort in the top branch of the plan. I needed to add the secondary column in (even though it won't ever change the result as YourCol will be the same for all values with the same Diff) so it would go through the merge join (concatenation) without adding a Sort.

SQL Server seems able to infer that an index on X seeked in ascending order will deliver rows ordered by X + Y and no sort is necessary. But it is not able to infer that travelling the index in descending order will deliver rows in the same order as Y-X (or even just unary minus X). Both branches of the plan use an index to avoid a sort but the TOP 10 in the bottom branch are then sorted by Diff (even though they are already in that order) to get them in the desired order for the merge.

For other queries/table definitions it may be trickier or not possible to get the merge plan with just a sort of one branch - as it relies on finding an ordering expression that SQL Server:

Accepts that the index seek will supply the specified order so no sort is needed before the top.
Is happy to use in the merge operation so requires no sort after the TOP


MySQL not allowing text columns? I'm using MySQL 5.5.27. I currently have a BLOB column, that I want to convert to text to allow fulltext searching. The ALTER TABLE command to create a text column is successful, but the ALTER TABLE table ADD FULLTEXT INDEX index (column ASC) to create a fulltext index returns an error, saying I cannot create a fulltext index on that column.

When I go back and look at the specs for the table, it appears as BLOB, instead of text.

I'm using MySQLWorkbench, but I've generally been running the scripts by hand, instead of using the wizard.

I have tried setting a character encoding for the entire schema (none existed before) thinking that this would be needed for text columns, but it didn't help.

The command

ALTER TABLE ADD FULLTEXT INDEX index (column ASC);

sounds like it does not make sense.

What does FULLTEXT indexing a column in ascending order mean?

You should run it like this:

ALTER TABLE ADD FULLTEXT INDEX index (column);

Give it a Try !!!


SQL Server FTS get tokens from string What is the query to get the tokens in a string from SQL Server 2008 R2?

I need it to highlight results of FREETEXT

You can use DMV sys.dm_fts_index_keywords_by_document to list the words for a given document, eg something like this:

SELECT DISTINCT display_term
FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('dbo.Users') ) X

I had a play with this product a few years ago that does hit-highlighting, it was quite nice but I didn't go past the 30-day trial.


Use Powershell to query SSAS connection string I want to retrieve the existing connection string from SSAS for each OLAP Cube or Database. The data will be used in a control/audit report.

I've found that Powershell allows me to drill into SSAS like a directory and I can see what I'm after, but don't know how to query it.

in Powershell I can drill down to here:

PS SQLSERVER:\SQLAS\LAP123\Default\databases\EnvironmentalAnalysisService\Datasources>

And I can see the details I want:

Name                      Isolation     Max Connections Connection String
----                      ---------     --------------- -----------------
Environmental Data Mart   ReadCommitted              10 Provider=SQLNCLI11.1;Data
                                                        Source=LAP123;Integrated
                                                        Security=SSPI;Initial
                                                        Catalog=EnvironmentalDataMart

In this case Server; LAP123 & Database: EnvironmentalDataMart

How can I get the items I want from Powershell like a query?

Woohoo!!!

# Add the AMO namespace
$loadInfo = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)

## Connect and get the edition of the local server
$connection = “localhost”
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($connection)

foreach ($d in $server.Databases )
{

    Write-Output ( “Database: {0}, String {1}:” -f $d.Name, $d.DataSources.ConnectionString)

} # Databases

returns:

Database: EnvironmentalAnalysisService, String Provider=SQLNCLI11.1;Data Source=LAP123;Integrated Security=SSPI;Initial Catalog=EnvironmentalDataMart:
Database: PerformanceAnalysisService, String Provider=SQLNCLI11.1;Data Source=DEV-EDW;Integrated Security=SSPI;Initial Catalog=PerformanceDataMart:

PS SQLSERVER:\SQLAS>


Postgres replication: Do I need to rsync pg_xlog files if I have archiving setup in master? I am trying to replicate a master to slave, without shutting down my master.

In the tutorial here: http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial, they recommend these steps:

pg_start_backup()
rsync -av --exclude pg_xlog --exclude postgresql.conf --exclude postgresql.pid \
 data/*  :/var/lib/postgresql/data/
pg_stop_backup()
rsync -av data/pg_xlog :/var/lib/postgresql/data/

Steps 1-3 are straightforward. However, If I setup archiving in the master and archiving in my slave, do I need to execute step 4? Will it mess things up? Should I NOT setup archiving until everything is rsync'd and my slave is running?

My master's config file looks like this:

wal_level = hot_standby
archive_mode = on
archive_command = 'rsync -a %p <INSERT SLAVE IP>:/var/lib/pgsql/data/archive/%f'

Likewise, my slave has a recovery.conf with a restore_command.

That tutorial appears to be old, and also says "work in progress" at the top. I'll amend it if I have time. Please refer to the main documentation.

If you have continuous archiving set up you don't need to manually rsync the WAL, because your archive_command will do that for you.

You may also wish to consider using pg_basebackup instead of manually rsync'ing the datadir. It'll take care of everything for you over the PostgreSQL replication protocol. If you weren't using WAL archiving you'd use -X stream mode to get it to copy the WAL for you too, but you don't need that.


Optimize MAX or ORDER BY column DESC with LIMIT 1 I have a scenario where I need to Insert a new record and increase a sub-id.
The Query is very slow.
Exist any better Solutions for this?

Table

CREATE TABLE `link_` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `duid` bigint(20) DEFAULT NULL,
  `domain_id` int(11) DEFAULT NULL
  PRIMARY KEY (`id`),
  KEY `domain_id` (`domain_id`),
  KEY `duid` (`duid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Solution 1

INSERT INTO link_ (duid, domain_id)
SELECT
    IFNULL(MAX(duid), 0) + 1 AS duid, 5
FROM
link_
WHERE domain_id = 5;

Solution 2

INSERT INTO link_ (duid, domain_id)
SELECT
    IFNULL(duid, 0) + 1 AS duid, 5
FROM
    link_
WHERE domain_id = 5
ORDER BY duid DESC
LIMIT 1;

You may need to redesign this table, but I think you will love to do so. Why ???
Did you know that MyISAM supports having an auto_increment id grouped to a key ?
In the MySQL Documentation for using auto_increment under the subheading MyISAM Notes:

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

MyISAM will do all the heavy lifting of assigning the next du_id for each domain_id
I discussed this a few times before

Aug 28, 2013 : How to go about modeling the main object of relationship?
Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin (Sample Code)
Jun 10, 2012 : MySQL get next unique value without auto increment
Apr 21, 2012 : How can you have two auto-incremental columns in one table?

HOW TO MAKE USE OF THIS
Change the table's design to look like this:
CREATE TABLE `link_` (
  `duid` bigint(20) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  PRIMARY KEY (`domain_id`,`du_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

You would eliminate id (saving 8 bytes per row) and remove a needless index.
Then, your INSERTs would look like this:
INSERT INTO link_ (domain_id) VALUES (5);

That's it !!! All the auto_increment stuff is handled for you !!!
Please look over the sample code I mentioned.
GIVE IT A TRY !!!


How to get information about query plans that are out of cache? In SQL Server there are a lot of DMV/Fs and other options which allow me to get information about query plans that are in cache, but I can't find any method to get information about all query plans, not just ones saved in cache. Can you, please, show how can I explore all query plans such as a number of times it has been used, its size, etc.?

As Dan noted, you can use Query Store on SQL Server 2016+.

If you're on an earlier version, you can try Open Source Query Store, though I don't have any experience with using it.

As a last resort, you could log sp_BlitzCache to a table. There are instructions on doing that in the README. I'm not going to post them here. Fair warning: I contribute to that open source project.


Repair a SQL Server 2000 MDF file I had a SQL Server 2000 MDF on a SQL Server 2005 server (moved from prior version), there was a hardware failure.

After sending harddisk, it was supposed the mdf / ldf files were recovered, but I am unable to attach.

Something curious is that the MDF file has a date on disk from june 2010 and the problem happened in sept 2010. I have tried several options regarding making a new fake db, replace files, mdf, emergency etc, but with no results, can some one help to see how can this be fixed?

  Attach database failed for Server 'SERVERDATA'.  (Microsoft.SqlServer.Smo)

  SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x55555556). It occurred during a read of page (1:24) in database ID 7 at offset 0x00000000030000 in file 'C:\sqldata\ipvtelas_Data.MDF'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

  Could not open new database 'ipvtelas'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)

After making a dummy database and replacing the MDF/LDF files, the error is now:

  Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) Database 'dummy' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)

The fastest, simplest solution is to restore your last good backup. If that isn't an option you can try a workaround to make MSSQL think the database already exists:

http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-hack-attach-a-damaged-database.aspx

Then you can run DBCC and see if it can repair the database, but even in that case you may still have data loss. You could also call Microsoft (maybe you already have a support contract), but the bottom line is that if you have a damaged database and no backup then there's not much that anyone can do.


PostgreSQL: changing data directory location (sshfs) I'm trying to change my PostgreSQL data directory initially on my Debian server to my NAS (running also on linux).
To do so, I've mounted it on a specific directory through sshfs (in /mnt/titanBDD).

Now I get this error when starting postgre (using service and pg_ctlcluster):

  Error: could not exec /usr/lib/postgresql/9.4/bin/pg_ctl
  /usr/lib/postgresql/9.4/bin/pg_ctl start -D
  /var/lib/postgresql/9.4/redirect_TITAN -l
  /var/log/postgresql/postgresql-9.4-main.log -s -o  -c
  config_file="/etc/postgresql/9.4/main/postgresql.conf"

Here is the modified part of postgresql.conf:

data_directory = '/var/lib/postgresql/9.4/main'

modified in:

data_directory = '/var/lib/postgresql/9.4/redirect_TITAN'

Where redirect_TITAN:

0 lrwxrwxrwx 1 root root 18 nov.   1 16:23 /var/lib/postgresql/9.4/redirect_TITAN -> /mnt/titanBDD/main

I've used the following command to move my data:

root@machine:~# cp --preserve=all -R /var/lib/postgresql/9.4/main/ /mnt/titanBDD/

Acutally, in /mnt/titanBDD I have:

root@machine:~# ls -als /mnt/titanBDD/
total 12
4 drwxr-xr-x 1 postgres postgres 4096 nov.   1 16:19 .
4 drwxr-xr-x 3 root     root     4096 nov.   1 14:05 ..
4 drwx------ 1 postgres postgres 4096 nov.   1 15:59 main

What is going wrong? I guess it's a context issue but don't have any idea where to search.

I finally solved the problem using -o allow_other when mounting the /mnt/titanBDD with sshfs.

The error message was still :

  Error: could not exec

/usr/lib/postgresql/9.4/bin/pg_ctl /usr/lib/postgresql/9.4/bin/pg_ctl
start -D /var/lib/postgresql/9.4/redirect_TITAN
-l /var/log/postgresql/postgresql-9.4-main.log -s -o -c
config_file="/etc/postgresql/9.4/main/postgresql.conf"

(which repeat the command twice?!)

when executing:

usr/lib/postgresql/9.4/bin/pg_ctl
start -D /var/lib/postgresql/9.4/redirect_TITAN
-l /var/log/postgresql/postgresql-9.4-main.log -s -o -c
config_file="/etc/postgresql/9.4/main/postgresql.conf"

but I noticed the user postgres didn't have access to the mounted point which put me on the track.

Now PostgreSQL starts without any problem.


Executing TSQL script generated from SSMS in SQLCMD. Failing on quotes I am attempting to deploy a database to a machine by executing a script generated by the "Generate Scripts" tool in SQL Server Management Studio.

Here is the command I've issued:

sqlcmd -S LOCALHOST\sqlexpress -I -U user -P ******** -i
    C:\Rollouts\NI-9-25-2012_10-42-AM\Rollout.sql > rolloutlog.txt

I'm receiving errors like this:

  Sqlcmd: Error: Syntax error at line 39488 near command ''' in file
  'C:\Rollouts\ NI-9-25-2012_10-42-AM\Rollout.sql'.

When I view this line with a text editor all signs point to an issue with quotation marks.

The line in question looks like this:

  $(''.cat-menu-new'').slideDown(''slow'');

Is there a switch that I should be executing or generating the script with to support quotes within the script?

$(...) denotes a variable in SQLCMD, so it's interpreting your data as a variable.

Use the -x command-line option to disable variables.