Why does batch mode window aggregate yield arithmetic overflow? The following query performs a windowed SUM over a columnstore table with 1500 total rows, each of which has the value 0 or 1, and it overflows the INT data type. Why is this happening?

SELECT a, p, s, v, m, n,
    SUM(CASE WHEN n IS NULL THEN 0 ELSE 1 END)
        OVER (PARTITION BY s, v, a ORDER BY p) AS lastNonNullPartition
FROM (
    SELECT a, p, s, v, m, n,
        RANK() OVER (PARTITION BY v, s, a, p ORDER BY m) AS rank
    FROM #t /* A columnstore table with 1,500 rows */
)  x
WHERE x.rank = 1
--Msg 8115, Level 16, State 2, Line 1521
--Arithmetic overflow error converting expression to data type int.

Full script

See this file for a fully contained reproduction script.

Query plan

Here is an annotated estimated query plan (full XML on Paste the Plan).

Similar queries that execute successfully

If any of the following modifications are made, the error does not occur:

Use trace flag 8649 to prefer a parallel plan regardless of the cost threshold for parallelism
Use trace flag 9453 to disable batch mode
Use the COUNT aggregation function instead of the SUM function
Remove the WHERE x.rank = 1 predicate

For example, this query executes successfully:

SELECT a, p, s, v, m, n,
    SUM(CASE WHEN n IS NULL THEN 0 ELSE 1 END)
        OVER (PARTITION BY s, v, a ORDER BY p) AS lastNonNullPartition
FROM (
    SELECT a, p, s, v, m, n,
        RANK() OVER (PARTITION BY v, s, a, p ORDER BY m) AS rank
    FROM #t /* A columnstore table with 1,500 rows */
)  x
WHERE x.rank = 1
OPTION (QUERYTRACEON 9453/* Disable batch mode */)

Multiple commenters have been able to reproduce this issue. We initially thought that SQL Server 2017 CU10 resolved the issue, but then found that the error can be reproduced in all versions of SQL Server we tried, including CU10. However, some commenters observed an element of chance where the same script did not always trigger the error.

Because there is no logical way that computing a sum across a set of non-negative numbers whose maximum possible sum is 1,500 can overflow a 32 bit integer, we believe this is a bug in the batch mode window aggregate operator. Being a new operator in SQL Server 2016, it's reasonable to assume that there might still be some edge cases to iron out.

Here is the bug report we filed with Microsoft.

The response was:

  This is fixed in SQL Server 2019 CTP 2.1 and will also be fixed soon in Azure SQL Database.


How to disable foreign key constraints in Postgres dump file? I used pg_dump -U postgres -p 65432 --table=tableName --data-only --column-inserts DBName>fileName.sql to dump one particular table's rows. Now I need to insert them to another database but I get this error

  insert or update on table "tableName" violates foreign key constraint "tableName_fk1"

How to proceed by dropping the foreign key contraint?

the command is:

alter table "tableName" drop constraint "tableName_fk1"

The foreign key must already exist in the database where you want to import, since the dump was made --data-only.


restoring Postgres database: pg_restore -vs- just using psql I am dumping my Postgres database using pg_dump (in plain-text format) and then restore it simply by using psql (with the -f option).

Which begs the question: am I missing anything by not using pg_restore which seems like a specialized restore tool (compared to the generic psql)?

I can control options like disabling of triggers and such by using pg_dump parameters. What, then, is pg_restore used for? non-plain-text dump formats ?

If you created an SQL-format dump, all you can use is psql.

If you created a custom-format (pg_dump -Fc) or directory-format (pg_dump -Fd) dump, you can and must use pg_restore.

Custom and directory format dumps offer a lot of advantages over plain SQL script dumps, and I use them exclusively. You can selectively restore only some tables/schema, can choose whether to include only schema, only data, or both at restore time, etc. Lots of the options you have to specify at pg_dump time with SQL-format dumps can be chosen at restore-time if you use a custom-format dump and pg_restore.

If it weren't for backward compatibility I'm sure the default for pg_dump would be -Fc (custom) format.

You can't convert an SQL-format dump into custom-format or directory-format without restoring it to a PostgreSQL database and then dumping the restored DB.


Altering endpoint of existing AG Looking for some advice here:

I currently have 3 SQL servers in a single AG with a single network card on each server. During times of heavy network usage, we are sometimes seeing failovers to a secondary server. After some research, I see that the way round this is to have a dedicated NIC for the AG traffic on a private IP address range.

What I was wondering is, it is possible to drop and create existing endpoints/listeners with the updated IP addresses or would I have to create a new AG and move databases across to it?

During times of heavy network usage, we are sometimes seeing failovers to a secondary server. After some research, I see that the way round this is to have a dedicated NIC for the AG traffic on a private IP address range.

I'm skeptical that this will actually fix the issue. You'd basically need to collect network captures to prove the point. You'd also need a separate network if the one you were on was overloaded if you didn't QoS the current traffic.

  What I was wondering is, it is possible to drop and create existing endpoints/listeners with the updated IP addresses or would I have to create a new AG and move databases across to it?

It's completely possible to alter/drop the endpoint(s) while everything is running. There is no need to remove the availability group already setup. However, there will be communications issues and failures while the endpoints are down/unavailable.

Again, I'm not sure this is going to actually solve your issue.


getting error while starting memecached service with ndb engine i am installed memcached library by following the steps in the internet.
now there is a binary file created in bin folder of mysql

now i exicuting the command memcached i am getting error like this

[root@centos1 ~]# /usr/local/mysql/bin/memcached E /usr/local/mysql/lib/ndb_engine.so -e "connectstring=10.10.111.111:1186;role=db_only" -vv -c 20 -u root

24-Feb-2012 15:21:06 IST NDB Memcache 5.5.19-ndb-7.2.4 started [NDB 7.2.4; MySQL 5.5.19]
 Contacting primary management server (10.10.111.111:1186) ...
FAILED.
Could not connect to NDB. Shutting down.
Segmentation fault

please help me to resolve this problem

Make sure that you have at least 2 unused API slots ([mysqld] sections) in your configi.ini file (and perform a rolling restart of your Cluster after adding them). These slots will then be used by memcached.

Note that you can choose to run mysqld processes on the host as well and have them part of the cluster (can access the same data through SQL and NoSQL) - you just need to make sure that you include enough [mysqld] sections in your config.ini file so that there are a couple left for memcached.


Grant access to all tables of a database I recently wanted to share regular access rights with one user of a server and I realized that a simple CREATE USER and GRANT ALL ON DATABASE commands didn't let him run a simple SELECT on the data.

I would like to grant rights to all tables from a given database to a specified user, but I am not sure if it is the best idea to grant him access to whole schema public as I don't know if it would allow some kind of privilege escalation. Is there any other way?

The privilege on DATABASE only grants general connection rights to the database and no more. A user with just that privilege can only see what the general public is allowed to see.

To grant read access to all tables, you also need privileges on all schemas and tables:

GRANT USAGE ON SCHEMA public TO myuser; -- more schemas?
GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuser;

You may also want to set default privileges for future schemas and tables. Run for every role that creates objects in your db

ALTER DEFAULT PRIVILEGES FOR ROLE mycreating_user IN SCHEMA public
GRANT SELECT ON TABLES TO myuser;

But you really need to understand the whole concept first.
And it's almost always better to bundle privileges in group roles and then grant/revoke the group role to/from user roles. Related:

Why can a new user select from any table?
How to grant all privileges on views to arbitrary user
Grant privileges for a particular database in PostgreSQL
How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?


Oracle 12.2.0 installation NLS_CHARACTERSET QUESTION Im creating fresh database 12.2.0 on linux(RH7.6) but not with GUI (dbca). Im installing it from scripts and which in file i can set parameter NLS_CHARACTERSET to EE8MSWIN1250 in pre-install process so I don't have to change it after install.

The characterset is provided as part of the CREATE DATABASE statement, not as a parameter.

  Creating a Database: Example

  The following statement creates a database and fully specifies each
  argument:

CREATE DATABASE sample
   CONTROLFILE REUSE
   LOGFILE
      GROUP 1 ('diskx:log1.log', 'disky:log1.log') SIZE 50K,
      GROUP 2 ('diskx:log2.log', 'disky:log2.log') SIZE 50K
   MAXLOGFILES 5
   MAXLOGHISTORY 100
   MAXDATAFILES 10
   MAXINSTANCES 2
   ARCHIVELOG
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE
      'disk1:df1.dbf' AUTOEXTEND ON,
      'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE temp_ts
   UNDO TABLESPACE undo_ts
   SET TIME_ZONE = '+02:00';


Endpoint modification in SQL Server mirroring I have three SQL Server 2008 R2 machines which are configure to be Principal, Mirror and Witness.

I need to move them to another subnet, therefore their IP addresses will change; and i had configure the mirror using the IPs and no their names.

My question is: if I just use ALTER ENDPOINT with the new ip address (or the name), would it keep rolling or it won't work?

I'd appreciate any advise.

Thanks!

There is no need of modifying the endpoints. This is the process:

ALTER DATABASE IICS SET PARTNER OFF (Server2)
Change IP address in Server2
Change IP address in Witness
Change IP address in Server1
ALTER DATABASE IICS SET PARTNER = 'TCP://SERVER1:Port' (Server2)
ALTER DATABASE IICS SET PARTNER = 'TCP://SERVER2:Port' (Server1)
ALTER DATABASE IICS SET WITNESS = 'TCP://WITNESS:Port' (Server1)


Optimizing a query that retrieves five rows for each letter of the alphabet A site that I'm currently working on will execute a query 26 times but only changes a letter within the where clause. I am looking to make this more efficient by running only one query.

Here is an example query:

SELECT table1.id, table1.name, table1.url
FROM table1
       LEFT JOIN table2 ON table1.aid = table2.aid
WHERE LOWER(table1.name) LIKE 'a%'
  AND table1.rank > 0
  AND table1.view = 1
ORDER BY table2.important DESC, table1.rank ASC
LIMIT 5

I have managed to create a temporary table in which would return each letter of the alphabet however I cannot figure out how to fetch the letter and push it into the where clause.

I will end up creating a table for the alphabet but this is how it currently runs (for just now):

SELECT *
FROM (SELECT 'a' AS letter
    UNION SELECT 'b'
    UNION SELECT 'c'
    ...
    UNION SELECT 'y'
    UNION SELECT 'z') AS alphabet

Clarification: I didn't really explain what I am looking to do. I am looking to pull 5 rows for each letter.

Any and all help would be greatly appreciated.

You have to use the REGEX_LIKE or briefly RLIKE function:

. . . . . .
WHERE RLIKE(table1.name, '^[:alpha:].*', 'i')
. . . . . .

Here ^ mean "from the very beginning of the string",
[:alpha:] is any alphabetical character
.* is any number of any characters and
'i' is a flag that mean case-insensitive comparison.

RLIKE() function returns TRUE if matched and FALSE otherwise.

Further reading can be found here: https://dev.mysql.com/doc/refman/8.0/en/regexp.html

UPDATE

SELECT table1.id
     , table1.name
     , table1.url
     , IF(@tname <> @tname := table1.name, @cnt := 1, @cnt := @cnt + 1) AS cnt
  FROM table1
  LEFT JOIN table2 ON table1.aid = table2.aid
 WHERE RLIKE(table1.name, '^[:alpha:].*', 'i')
   AND table1.rank > 0
   AND table1.view = 1
 ORDER BY table1.name ASC
        , table2.important DESC
        , table1.rank ASC
HAVING cnt <= 5


Count(*) as rolecount, filter by rolecount = 1 How can i filter out the rows who only got rolecount = 1?
WHERE rolecount = 1 gives an error. Can't really figure out how to do it.

SELECT
u.uid AS uid,
u.name AS name,
u.picture AS picture,
n.field_full_name_value AS field_full_name_value,
f.field_function_value AS field_function_value,
pi.field_phone_intern__value AS field_phone_intern__value,
ur.rid AS rid,
COUNT(*) AS rolecount
FROM `dev_drupal_wurth-intranet`.users u
LEFT OUTER JOIN `dev_drupal_project`.users_roles ur ON u.uid = ur.uid
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_full_name n ON u.uid = n.entity_id
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_function f ON u.uid = f.entity_id
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_phone_intern_ pi ON u.uid = pi.entity_id
GROUP BY u.uid;

Filters after the GROUP BY is executed can be applied with the HAVING clause, which is equivalent to a WHERE condition, but for the latest stage of the execution. So, assuming the rest of the query is right:

SELECT
  u.uid AS uid,
  u.name AS name,
  u.picture AS picture,
  n.field_full_name_value AS field_full_name_value,
  f.field_function_value AS field_function_value,
  pi.field_phone_intern__value AS field_phone_intern__value,
  ur.rid AS rid,
  COUNT(*) AS rolecount
FROM `dev_drupal_wurth-intranet`.users u
LEFT OUTER JOIN `dev_drupal_project`.users_roles ur
  ON u.uid = ur.uid
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_full_name n
  ON u.uid = n.entity_id
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_function f
  ON u.uid = f.entity_id
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_phone_intern_ pi
  ON u.uid = pi.entity_id
GROUP BY u.uid
HAVING rolecount = 1;

You can use the alias (I recommend it) or the count(*) expression for filtering, but those cannot be on a WHERE clause.


PostgreSQL cluster has two primary databases after network glitch My test environment had network glitch over the weekend and I ended up having two Primary databases up and running in the cluster.

 ID | Name                              | Role    | Status    | Upstream | Location | Priority | Connection string
----+-----------------------------------+---------+-----------+----------+----------+----------+------------------------------------------------------------------------------------
 1  | node-1 | primary | ! running |          | default  | 100      | host=node-1 user=repmgr dbname=repmgr connect_timeout=2
 2  | node-2 | primary | * running |          | default  | 100      | host=node-2 user=repmgr dbname=repmgr connect_timeout=2

Is there a way to demote the new Primary back to Standby and resume WAL shipping instead of perform the Standby clone?

PgSQL version: 10.7

Repmgr version: 4.3

Any help is greatly appreciated.

the bad news is: so far as I know the only solution is to demote one of your primaries and re-clone.

the good news is: rsync should be able to do the cloning fairly quickly if most of the data is unalteted,


Cassandra goes down after some some days I am running cassandra in 3 node cluster with replication factor 3. It was working fine for 2 days and all of a sudden it went down. I am beginner to cassandra database. I went through documentation to understand why it is happening . I could not get it .Below are the logs.

INFO  [main] 2020-02-17 11:52:34,270 CassandraDaemon.java:473 - Hostname: QCS7
INFO  [main] 2020-02-17 11:52:34,271 CassandraDaemon.java:480 - JVM vendor/version: Java HotSpot(TM) 64-Bit Server VM/1.8.0_152
INFO  [main] 2020-02-17 11:52:34,274 CassandraDaemon.java:481 - Heap size: 998.438MiB/998.438MiB
INFO  [main] 2020-02-17 11:52:34,275 CassandraDaemon.java:486 - Code Cache Non-heap memory: init = 2555904(2496K) used = 4815808(4702K) committed = 4849664(4736K) max = 251658240(245760K)
INFO  [main] 2020-02-17 11:52:34,275 CassandraDaemon.java:486 - Metaspace Non-heap memory: init = 0(0K) used = 18448024(18015K) committed = 19005440(18560K) max = -1(-1K)
INFO  [main] 2020-02-17 11:52:34,275 CassandraDaemon.java:486 - Compressed Class Space Non-heap memory: init = 0(0K) used = 2277864(2224K) committed = 2490368(2432K) max = 1073741824(1048576K)
INFO  [main] 2020-02-17 11:52:34,275 CassandraDaemon.java:486 - Par Eden Space Heap memory: init = 214827008(209792K) used = 98821656(96505K) committed = 214827008(209792K) max = 214827008(209792K)
INFO  [main] 2020-02-17 11:52:34,276 CassandraDaemon.java:486 - Par Survivor Space Heap memory: init = 26804224(26176K) used = 0(0K) committed = 26804224(26176K) max = 26804224(26176K)
INFO  [main] 2020-02-17 11:52:34,276 CassandraDaemon.java:486 - CMS Old Gen Heap memory: init = 805306368(786432K) used = 0(0K) committed = 805306368(786432K) max = 805306368(786432K)

INFO  [pool-3-thread-1] 2020-02-17 11:53:24,320 AutoSavingCache.java:262 - Harmless error reading saved cache /var/lib/cassandra/saved_caches/KeyCache-e.db
java.io.IOException: Corrupted key cache. Key length of 83886081 is longer than maximum of 65535
    at org.apache.cassandra.service.CacheService$KeyCacheSerializer.deserialize(CacheService.java:481) ~[apache-cassandra-3.11.2.jar:3.11.2]
    at org.apache.cassandra.cache.AutoSavingCache.loadSaved(AutoSavingCache.java:219) ~[apache-cassandra-3.11.2.jar:3.11.2]
    at org.apache.cassandra.cache.AutoSavingCache$3.call(AutoSavingCache.java:164) [apache-cassandra-3.11.2.jar:3.11.2]
    at org.apache.cassandra.cache.AutoSavingCache$3.call(AutoSavingCache.java:160) [apache-cassandra-3.11.2.jar:3.11.2]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_152]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_152]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_152]
    at java.lang.Thread.run(Thread.java:748) [na:1.8.0_152]
INFO  [pool-3-thread-1] 2020-02-17 11:53:24,320 AutoSavingCache.java:173 - Completed loading (21 ms; 560 keys) KeyCache cache

INFO  [main] 2020-02-17 11:55:35,746 MessagingService.java:753 - Starting Messaging Service on /172.30.55.233:7000 (br-provider)
INFO  [main] 2020-02-17 11:55:35,827 OutboundTcpConnection.java:108 - OutboundTcpConnection using coalescing strategy DISABLED
WARN  [main] 2020-02-17 11:56:06,859 Gossiper.java:1425 - Unable to gossip with any seeds but continuing since node is in its own seed list
INFO  [main] 2020-02-17 11:56:06,863 StorageService.java:707 - Loading persisted ring state
INFO  [main] 2020-02-17 11:56:06,869 StorageService.java:825 - Starting up server gossip
INFO  [main] 2020-02-17 11:56:07,546 TokenMetadata.java:479 - Updating topology for /172.30.55.233
INFO  [main] 2020-02-17 11:56:07,546 TokenMetadata.java:479 - Updating topology for /172.30.55.233

This seems to be an issue with the values for column_index_cache_size_in_kb and key_cache_size_in_mb in your cassandra.yaml file. As explained here, the database will stop when the total size of all the index data for a partition is greater than the value defined in column_index_cache_size_in_kb.

If the tuning of these values don't fix the issue, you may consider using larger instances for the nodes


Using PostgreSQL what is the difference between a smallint and a bool for storing boolean? What is the difference between the smallint type and the bool type for storing boolean values?

This question arose in the comments to a question on Geographic Information Systems Stack Exchange.

Always store boolean data as boolean. Only exotic exception imaginable.
Just to address the storage angle in addition to what you posted as answer:
boolean requires 1 byte on disk, smallint requires 2. But that's not the whole story.
smallint (like other integer types and unlike boolean) also has special needs for alignment padding. It can only start at an even offset from the start of the tuple data. So another byte is consumed every odd time (literally).
In a worst case scenario, when mixing with types that require 8-byte alignment like bigint or timestamp / timestamptz:
SELECT pg_column_size(row("char" 'a', FALSE   )) AS char_bool
     , pg_column_size(row("char" 'a', int2 '1')) AS char_int2
     , pg_column_size(row(text 'abcdef', TRUE    , now())) AS text7_bool_ts
     , pg_column_size(row(text 'abcdef', int2 '1', now())) AS text7_int2_ts;  -- worst case

 char_bool | char_int2 | text7_bool_ts | text7_int2_ts
-----------+-----------+---------------+---------------
        26 |        28 |            40 |            48

Details:

Calculating and saving space in PostgreSQL (updated)
Configuring PostgreSQL for read performance
What is the overhead for varchar(n)?

If you have many boolean NOT NULL values and want to optimize space on disk:

Should I use the PostgreSQL bit string?


MySQL distinct string I have the following sample table.

drop table if exists fruit;

create table fruit
    (
    id int not null auto_increment primary key,
    tag varchar(100) not null
    );

insert into fruit (tag) values
('apples, oranges, apples'),('apples, pears, pears'),('figs, oranges, figs, oranges');

So the table looks like

> select tag from fruit;
+--------------------------------+
| tag                            |
+--------------------------------+
| apples, oranges, apples        |
| apples, pears, pears           |
| figs, oranges, figs, oranges   |
+-----------------+
3 rows in set (0.00 sec)

How can I make the rows have unique fruits? For example, I would like to have the following resulting table.

        tag

| apples, oranges |
| apples, pears   |
| figs, oranges   |

Any suggestions would be greatly appreciated.

You came to the right place !!! Hang onto something because you are going to roll on the floor laughing

I answered a similar question just like this back on April 22, 2014 involving apples, oranges, pears, and figs: MySQL group concat not showing distinct values when joined to another table

In your case, you need to assemble distinct fruits by the id it belongs to

Ready for a convoluted solution tailor made for you ?

PROPOSED QUERY

SELECT CONCAT('SELECT id,REPLACE(GROUP_CONCAT(DISTINCT frt),'','','', '') tag FROM (',
CONCAT('SELECT ',GROUP_CONCAT(fruitstr SEPARATOR ' UNION SELECT ')),') A GROUP BY id')
INTO @TagSQL FROM
(
    SELECT REPLACE(CONCAT(REPLACE(QUOTE(tag),', ',CONCAT(''',',
    id,' id UNION SELECT ''')),',',id,' id'),',',' frt,') fruitstr FROM fruit
) A;
SELECT @TagSQL\G
PREPARE s FROM @TagSQL; EXECUTE s; DEALLOCATE PREPARE s;

YOUR SAMPLE DATA

mysql> drop database if exists dinesh;
Query OK, 2 rows affected (0.38 sec)

mysql> create database dinesh;
Query OK, 1 row affected (0.01 sec)

mysql> use dinesh
Database changed
mysql> create table fruit
    -> (
    ->     id int not null auto_increment primary key,
    ->     tag varchar(100) not null
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> insert into fruit (tag) values
    -> ('apples, oranges, apples'),
    -> ('apples, pears, pears'),
    -> ('figs, oranges, figs, oranges');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from fruit;
+----+------------------------------+
| id | tag                          |
+----+------------------------------+
|  1 | apples, oranges, apples      |
|  2 | apples, pears, pears         |
|  3 | figs, oranges, figs, oranges |
+----+------------------------------+
3 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT CONCAT('SELECT id,REPLACE(GROUP_CONCAT(DISTINCT frt),'','','', '') tag FROM (',
    -> CONCAT('SELECT ',GROUP_CONCAT(fruitstr SEPARATOR ' UNION SELECT ')),') A GROUP BY id')
    -> INTO @TagSQL FROM
    -> (
    ->     SELECT REPLACE(CONCAT(REPLACE(QUOTE(tag),', ',CONCAT(''',',
    ->     id,' id UNION SELECT ''')),',',id,' id'),',',' frt,') fruitstr FROM fruit
    -> ) A;
Query OK, 1 row affected (0.03 sec)

mysql> SELECT @TagSQL\G
*************************** 1. row ***************************
@TagSQL: SELECT id,REPLACE(GROUP_CONCAT(DISTINCT frt),',',', ') tag FROM (SELECT 'apples' frt,1 id UNION SELECT 'oranges' frt,1 id UNION SELECT 'apples' frt,1 id UNION SELECT 'apples' frt,2 id UNION SELECT 'pears' frt,2 id UNION SELECT 'pears' frt,2 id UNION SELECT 'figs' frt,3 id UNION SELECT 'oranges' frt,3 id UNION SELECT 'figs' frt,3 id UNION SELECT 'oranges' frt,3 id) A GROUP BY id
1 row in set (0.00 sec)

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

+----+-----------------+
| id | tag             |
+----+-----------------+
|  1 | apples, oranges |
|  2 | apples, pears   |
|  3 | figs, oranges   |
+----+-----------------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

GIVE IT A TRY !!!

P.S. Like I said in my earlier post,

I TOLD YOU IT WAS CONVOLUTED !!!


Database Design for a Wizard's Output I'm not necessary sure if the "wizard" is the correct term for it, but essentially what I have is a form that have a specific set of questions that a user must answer in order to determine which option is the best solution for them.

I have the following tables:

Question
Answer
QuestionAnswer
WizardOption

What I'm having an issue with is creating the relationship between QuestionAnswer and WizardOption. (I'm not sure if I'm just over thinking it; I'm new to designing a database structure).

QuestionAnswer Table

QuestionID INT
AnswerID INT

WizardOption Table

WizardOptionID INT
WizardOptionText VARCHAR(25)

To connect the two, is it ideal to create another table, like so:

WizardOptionByAnswers Table

WizardOptionID INT
Answers VARCHAR(100)

And then in the Answers column I would store the correct order of answers needed to match with the WizardOptionID.

+----------------------------------+
| WizardOptionID | Answers         |
+----------------------------------+
| Option1        | 1,2,1,2,3,1,1,1 |
| Option2        | 2,1,2,2,1,2,1,1 |
| Option3        | 2,2,2,1,3,1,1,2 |
+----------------------------------+

Not sure if that is the correct way to do this. Please let me know if more information is needed.

Based on the complexity of your wizard, you may face here two possible scenarios. A wizard is nothing else but a paged form.
1. Your wizard has always the same amount of answers
1.a. The questions are always the same.
2.b. The questions may be different (opting between one payment method or the other, and thus the answer might be different)
2. Your wizard may have different amounts of questions, eg. you have a groups of answers that might be enabled or disabled.

So let's analyze case by case.
1.a That's easy, you have questions Q1, Q2, Q3, Q4... And you can simply store the answers in the same order as a simple series, as in your example.
1.b. This is a bit more complex, since storing simply the answers is not enough, as the questions are not the same. However, since we know the amount of answers is always the same you can create a table with as many columns as possible answers answers, adding a special columns to identify ambivalent questions. So the structure would be something like

CREATE TABLE FormResults (
A1 VARCHAR(50), -- eg. Name
A2 VARCHAR(50), -- eg. Surname
Q3 INTEGER, -- Q3 is the Payment method chosen, can be PayPal, credit card, bank transfer
A3 VARCHAR(50), -- This answer can be the email for PP, the card number, the bank account... corresponding to the Q3 chosen.
A4 DECIMAL(4,2) -- e.g. The amount
);

The benefit of this solution is that your table will be quite compact with nulls only when an answer is optional.

For the case 2, if the number of answers is variable that's the most complicated case. The easiest way to solve this is simply to create a table with columns for all possible answers. However depending on your wizard this may result in a table plenty of NULL values. If this is the case, and if this is a problem in your RDBMS, then you may create a table like

CREATE TABLE FormResults (
AnswerSetId INTEGER,
QuestionId INTEGER,
AnswerValue VARCHAR(50)
);

Being the PK (AnswerSetId, QuestionId)

Here you may find that different answer sets will have different number of rows in this table, depending on the choices of the user. The good thing of this solution is that it creates a very compact and easily indexable table.


Do not have permission to use 'dbo' when creating a view I want to create a user with minimal required access to two databases within a server.  The user will be used by someone from a third party integrating a Business Intelligence tool into our database.   They have specified that they will only need to create views.

So I created a new user on the server,  then used the database properties on each database to grant them the SELECT and CREATE VIEW privileges on that database.

I then logged in on SSMS using that user, and tried to create a view but I got a message saying that

  'dbo' either does not exist , or I do not have permission to use it.

I tried to figure this problem out myself by trial and error but I just don't understand user permissions on sql server.   What am I doing wrong and what extra/different steps do I need to take?

Remember, I don't want them to have more control than the minimum they need.

Your user should have the permission to modify the dbo schema. You can accomplish that by assigning the permission to a role the User is a member of:

So its like:

CREATE ROLE Limitedaccess;
GO

GRANT CREATE VIEW TO Limitedaccess;
GO

GRANT SELECT ON SCHEMA::dbo TO Limitedaccess;
GO

CREATE USER user WITHOUT LOGIN; --- this would be the user here
GO

EXEC sp_addrolemember 'Limitedaccess', 'User';
GO

Once permission is granted, re-run the previous CREATE VIEW statement. It will now succeed.

GRANT ALTER ON SCHEMA::dbo TO Limitedaccess;
GO


Does software exist for transforming data from one DB schema to another? I need to transform some data between two different database schemas. For example, I have one table in old schema like this:

and I need to transform it to new schema like this:

So I have few questions to ask:

Does there exist software that can migrate data between these two schemas (Without or with little usage of SQL)?
If it exists, can this software migrate even between two platforms (i.e. MS SQL Server to Oracle)?

Thanks!

You'll need to use a multi-database tool - I would recommend SQuirreL SQL - it's a Java based tool that connects through JDBC. If you want to migrate to Oracle, you could also consider SQL Developer (which IIRC can connect to MS SQL through JDBC also).

As @ConcernedOfTunbridgeWells said in his comment, you can't get a "push-button" migration of data - you have to explicitly tell the different systems from what column they're to take data and where to put it and join the tables back and forth. If it could be done automatically, there'd be no need for programmers or DBAs :-).

I've used the script below for PostgreSQL to solve your problem. For simplicity, I haven't put in PRIMARY KEYs, FOREIGN KEYs or INDEXes which may (probably) help with performance.

1st I created the employee table and put a small amount of sample data into it.

CREATE TABLE employee
(
  id serial,
  lname VARCHAR(25),
  city VARCHAR(25)
 );

INSERT INTO employee (lname, city) VALUES('Lino', 'Dublin');
INSERT INTO employee (lname, city) VALUES('Mary', 'Cork');
INSERT INTO employee (lname, city) VALUES('Bill', 'Galway');
INSERT INTO employee (lname, city) VALUES('Fred', 'Dublin');

Below is not strictly necessary - but it's always a good idea to "eyeball" your data, particularly when testing!

 SELECT * FROM employee; <--- result snipped for brevity.

Create the new table city.

CREATE TABLE city
(
  id serial,
  city_name VARCHAR(25)
);

Now, insert into this new city table, the DISTINCT values from the city field in your employee table.

INSERT INTO city (city_name)
SELECT DISTINCT city
FROM employee;

And again, to double-check.

SELECT * FROM city;  <---again, snipped.

Add a column city_id to the employee table.

ALTER TABLE employee ADD COLUMN city_id INTEGER; <-- syntax may vary according to RDBMS.

You can select again from the table to double-check (step skipped for brevity).

Next, you have to update the employee table with the corresponding id value that you've created in the new city table. The syntax for this step may vary according to your RDBMS - I found this post helpful.

UPDATE employee
SET city_id = city.id
FROM city
WHERE city.city_name = employee.city;

SELECT * FROM employee; <--- snipped for brevity.

Drop the city column from the employee table. Again, step may vary.

ALTER TABLE employee DROP COLUMN city;

SELECT * FROM employee;

Result - et voila!

id;      lname     city_id
 3;      Bill            1
 2;      Mary            2
 4;      Fred            3
 1;      Lino            3


PostgreSQL - automatically create a database for users on first login Is there a way to configure PostgreSQL to create a database whose name is the same as the username on the user's first login?

Scenario:

In pg_hba.conf there will be a rule something like:

local sameuser all ident sameuser

When a user connects to PostgreSQL via console/psql,

If a database with the users name exists, just connect to database.
If it does not exist, create a database named after the user and connect the user to the newly created database.

There are a lot of users and I do not want to manually create these databases all at once, upfront.

No that's not possible.

Even if it was possible: as you need to specify a database when you connect, the new database wouldn't be the one the user connected to (and there is no way to re-route the user to the newly created one).


What's better: a temporary column or a temporary table? We have a table in our database with a Reference column. For a long time these reference values only pointed to one system thus several legacy apps assume that if it contains value, they should use it with that system.

We're updating that model to add indication which of the external systems the value refers to.

Unfortunately not all the apps caught up with that change, which is causing problems. So as temporary measure we're planning to store the reference elsewhere.

We have two options:

Add a new column to the table
Add a new table

Once all the apps are up to scratch, we'll copy the values from temp location over to the Reference column and then drop the source.

Question

Which of those two options is less messy and has less negative impact especially when it comes to cleaning it up?

Considerations

The table currently has 70k rows, 40 columns. Only rows inserted/updated by new apps will have that temporary value.

As per my experience, adding new table (Option 2) referencing index column would be you best choice. And will be less messy and easy to use.

Also, going further you may be able to add columns to the newly created table.

Hope this help!


Any script to auto test tune and benchmark mysql config? I'm looking or willing to do a script to benchmark mysql config variations for a specific system.

A script that would change certain mysql config values and do a sysbench, save results, change config and restart service, and test again.

max_connections = A
table_open_cache = B
query_cache_type = C
innodb_buffer_pool_size = D
innodb_buffer_pool_instances = E
innodb_adaptive_hash_index_partitions = F

...

And the script does a sysbench looping through all possibilities that make sense:

on A takes values of 50 100 200 250
on B takes values of ....

I know it depends on system, but it would be great to have a recommended "set" of values / ranges to test. (Even check the current free RAM and do a small CPU bench and with this values create the set of values to test)

Is there anything outside that can acomplish this?

Would it be useful for you or it's not worth doing it?

Such a tool would be almost completely worthless in practical use.

max_connections has nothing to do with performance.  It's a safety mechanism to prevent a runaway application from making so many connections that the server becomes overwhelmed.  The value makes absolutely no difference unless you are using that many connections.  It is also a value that you should never reach under normal operating conditions.

table_open_cache I had hoped by now that everybody knew just to leave this one alone.  It tends to scale negatively -- the more "sensible" the value seems to be, the more wrong you are.  The worst performing servers in my entire network were the ones where someone, at some time in the past, had felt a need to mess with this.

query_cache_type just set it to 1, with a reasonable size, something in the 32 to 128 MiB range, and walk away.  You will be hard-pressed to develop a meaningful benchmark here, and a small query cache definitely has its value for many workloads.  But keep it reasonably small, or turn it off. Above a certain size, it will scale negatively and degrade performance.  The more processor cores you have, the more potential exists for the query cache mutex to become a bottleneck. For workloads where there is virtually no chance of a cache hit, it's a waste of cpu cycles.

innodb_buffer_pool_size has only one correct answer: How much memory can you spare?  Set that here.  Lower will always be slower, and higher will also be slower if the server goes into swap, and of course infinitely slower if it crashes because the system ran out of memory entirely.  Correlated to this, if you're running anything else on your database server, you are doing it wrong.

innodb_buffer_pool_instances As long as each pool is at least 1GB, there's not a lot of variation available or needed here on a decent sized server.

innodb_adaptive_hash_index_partitions is probably a poster child for an argument that applies in some sense to all of the other values above, and some not mentioned: even if you tweaked this to find a sweet spot with sysbench... who cares?  How does that help you?  It doesn't.

Your specific workload is the only thing really relevant to the ideal values for the adaptive hash index, the query cache, and so many of the other tweakable parameters on MySQL Server.

Given two servers with "identical everything" -- storage, memory, CPUs, background cosmic radiation levels -- identical everything, that is, except the workload (meaning the schemata, datafill, and the actual queries being run) the "optimum" configuration parameters may still be wildly divergent.

Benchmarking with anything other than a real live workload will not give you anything of significant value, and tweaking a server is not usually the ticket to performance.

Well-written queries against well-structured schemata and appropriately indexed tables, and use of read replicas, will take you much further than tweaking.


BM25 (full-text search) implementation in SQL Server I've hit a stump, while trying to implement the BM25 algorithm in SQL Server 2008 R2. I know that SQL Server includes the Full-Text Search option, which already implements a variant of BM25, but I would like to do some parameter tuning tests and since the FTS procedures are non-editable (as far as I know), I've decided to implement it myself.

I have two tables, TF (term frequency) and DF (document frequency) with the following structures:

TF

*Note: the weight column denotes importance of the word (it is usually 1)

ID | Term | DocumentID | Count | TermID | Weight*

DF

ID | Term | Count

The TF table contains the relationship between a term and a document; that is, the frequency of the term in a document. The DF table contains information about how many documents contain a term. The two tables can be linked using DF.ID and TF.TermID. Using these two tables I would now like to compute BM25 similarity values between two documents (one document acts as a query) according to the formula in the Wikipedia article. The tables TF and DF translate to functions f(q, D) and n(q) respectively:

I would like the result to be in this format:

DocumentA_ID | DocumentB_ID | BM25_Value

Here is some code that I have up to now:

DECLARE @N FLOAT;
DECLARE @AVGDL FLOAT;
DECLARE @K1 FLOAT;
DECLARE @B FLOAT;
SET @K1 = 1.2;
SET @B = 0.75;

-- number of all documents
SELECT @N = COUNT(DISTINCT DocumentID) FROM TF;

-- average document length (in words)
SELECT @AVGDL = AVG(DocumentLength) FROM (SELECT DocumentID, SUM(TF.Count) AS DocumentLength FROM TF WHERE Weight = 3 GROUP BY DocumentID) A;

-- BM25 implementation
SELECT  D.DocumentID AS DocumentA,
        Q.DocumentID AS DocumentB,
        *,
-- need help here (SUM or something ...)
        LOG((@N - DF.Count + 0.5)/(DF.Count + 0.5)) AS IDF
FROM TF AS D
        INNER JOIN TF AS Q ON D.Term = Q.Term
        INNER JOIN DF ON D.TermID = DF.ID
WHERE   D.DocumentID <> Q.DocumentID

I'm having trouble constructing the query in the last section (BM25 implementation) to get the desired result format. Any help would be greatly appreciated.

After a few days of fiddling around, I finally managed to get this done. Here is the code I ended up with, packed into a stored procedure. For this to work, I added another table, which just holds a document ID and its length in words.

ALTER PROCEDURE [dbo].[BuildIndexBM25]
-- default parameters K1=> [1.2 - 2.0], B => [0.0 - 1.0], Weight => 3 (e.g. titles only)
    @K1 FLOAT = 1.2,
    @B FLOAT = 0.75,
    @Weight FLOAT = 3
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @N FLOAT;
    DECLARE @AVGDL FLOAT;

    -- number of all documents
    SELECT @N = COUNT(DISTINCT DocumentID) FROM TF;

    -- average document length (in words)
    SELECT @AVGDL = AVG(Length) FROM DocumentLength;

    -- BM25 implementation
    -- result set: | DocumentA | DocumentB | BM25 |
    SELECT
        DL.ID AS DocumentA,
        BM.DocumentID AS DocumentB,
        BM.BM25 AS BM25
    FROM (
        SELECT ID FROM DocumentLength
    ) DL
    CROSS APPLY (
        SELECT
                Q.DocumentID,
                SUM(LOG((@N - X.Count + 0.5) / (X.Count + 0.5)) * (Q.Count * (@K1 + 1)) / (Q.Count + @K1 * (1 - @B + (@B * L.Length / @AVGDL)))) AS BM25
        FROM    TF D, TF Q, DF X, DocumentLength L
        WHERE   D.TermID = Q.TermID
            AND D.DocumentID = DL.ID
            AND Q.TermID = X.ID
            AND D.DocumentID = L.ID
            AND Q.Weight = @Weight
            AND D.Weight = @Weight
        GROUP BY Q.DocumentID
    ) BM
    WHERE   DL.ID != BM.DocumentID
END

I hope this answer helps anyone who wanted to implement a full-text search algorithm by themselves. The default algorithm in SQL Server 2008 R2 has only minor differences to this implementation.


How to copy a schema with another name but with same data? I want to copy all the objects of Scott schema to another schema.

I can not understand what the code should be. Like bellow one?

create user scott1 identified by tiger1;

It's a quite old database. Your SQL command creates new user.

You can use Export in order to copy contents of one schema to another.

exp FILE=$ORACLE_HOME/scott_export.dmp LOG= $ORACLE_HOME/scott_export.log OWNER=scott

Create new user as you have shown in your post and grant required privileges.
Then import it to new schema.

imp FILE=$ORACLE_HOME/ scott_export.dmp LOG= $ORACLE_HOME/scott_import.log FROMUSER=scott TOUSER=scott2

Details are here and here


Insert multiple times XML data (SQL SERVER) I have some difficulties to solve my problem.
I have a stored procedure which take 2 input parameters.
The first one is a varchar, but in reality it's going to be a list of SomeID separated by comma like that '1,2,3,4'.
The second parameter is XML, something like :

<objectList>
    <object>
        <objectTypeId>1</objectTypeId>
        <objectId>13456</objectId>
        <mode>ADD</mode>
    </object>
    <object>
        <objectTypeId>1</objectTypeId>
        <objectId>654321</objectId>
        <mode>ADD</mode>
    </object>
</objectList>

There is no limit to the number of objects in the XML.

I created a temp table with 4 columns (SomeID,objectTypeId,objectTypeId,mode)

If I have multiple ids in my first parameter (I use a function to split my varchar) and multiple objects in my second parameter, I want the temp table to look like this:

SomeID | objectTypeId | objectTypeId | mode
-------------------------------------------
  1    |        1     |    123456    | ADD
  1    |        1     |    654321    | ADD
  2    |        1     |    123456    | ADD
  2    |        1     |    654321    | ADD
  3    |        1     |    123456    | ADD
  3    |        1     |    654321    | ADD
  4    |        1     |    123456    | ADD
  4    |        1     |    654321    | ADD

I need that all of the someIDs match with each object of my XML parameter.
I hope that I'm clear.

My actual code looks like that but it works only with multiple someIDs but only one XML object:

IF OBJECT_ID ('tempdb..#tmpObject') IS NOT NULL DROP TABLE #tmpObject

CREATE TABLE #tmpObject (tmpTagId INT, tmpObjectTypeId INT NULL, tmpObjectId INT NULL, tmpMode VARCHAR(16) NULL)

INSERT INTO #tmpObject (tmpTagId)
SELECT value FROM dbo.f_Split(@TagIdList,',')

UPDATE #tmpObject
SET tmpObjectTypeId = ParamValues.item.value('objectTypeId[1]','INT'),
    tmpObjectId = ParamValues.item.value('objectId[1]','INT'),
    tmpMode = ParamValues.item.value('mode[1]','VARCHAR(16)')
FROM @ObjectList.nodes('objectList/object') AS ParamValues(item)

One way is to insert all at once

INSERT INTO #tmpObject (tmpTagId,tmpObjectTypeId , tmpObjectId , tmpMode )
SELECT
    o.value ,
     ParamValues.item.value('objectTypeId[1]','INT'),
     ParamValues.item.value('objectId[1]','INT'),
     ParamValues.item.value('mode[1]','VARCHAR(16)')
FROM dbo.f_Split(@TagIdList,',')(@TagIdList,',') as o
     cross apply @ObjectList.nodes('objectList/object') AS ParamValues(item)

select * from #tmpObject order by tmpTagId

output:

tmpTagId    tmpObjectTypeId tmpObjectId tmpMode
----------- --------------- ----------- ----------------
1           1               654321      ADD
1           1               13456       ADD
2           1               13456       ADD
2           1               654321      ADD
3           1               654321      ADD
3           1               13456       ADD
4           1               13456       ADD
4           1               654321      ADD


Days in between two dates group by month I'm trying to create a lost time report in SSRS. I found a script that gives me what I need for a single date range, but I need this for multiple dates, and I need the total for the range.

So if I have 6 date ranges, I just need the total for the month/year combination and type combination (either L or R).

DECLARE @s SMALLDATETIME, @e SMALLDATETIME;
SELECT  @s = '20161209',  @e = '20170113';
    ;WITH n(n) AS
(
  SELECT TOP (DATEDIFF(MONTH, @s, @e)+1) ROW_NUMBER() OVER
  (ORDER BY [object_id])-1 FROM sys.all_objects
),
x(n,fd,ld) AS
(
  SELECT n.n, DATEADD(MONTH, n.n, m.m), DATEADD(MONTH, n.n+1, m.m)
  FROM n, (SELECT DATEADD(DAY, 1-DAY(@s), @s)) AS m(m)
)
SELECT [Month] = DATENAME(MONTH, fd), [Days] = DATEDIFF(DAY, fd, ld)
  - CASE WHEN @s > fd THEN (DATEDIFF(DAY, fd, @s)+1) ELSE 0 END
  - CASE WHEN @e < ld THEN (DATEDIFF(DAY, @e, ld)-1) ELSE 0 END
  FROM x;

My table is called HRAL and the main fields are BeginDate (inclusive), EndDate (exclusive) and Type.

Sample Data

StartDate   EndDate    Type
2017-09-28  2017-10-02 L
2017-10-03  2017-10-10 R
2016-11-10  2016-11-11 L
2017-08-17  2017-12-25 R

Results

Date        Days  Type
2017-09-01  3     L
2017-10-01  1     L
2017-10-01  38    R
2017-11-01  1     L
2017-08-01  15    R
2017-09-01  30    R
2017-11-01  30    R
2017-12-01  24    R

I assume the solution needs recursion?

First I'll create a very abbreviated version of a calendar table that stores just month start and end dates.

CREATE TABLE #LAZY_DATE_DIM (
    MONTH_START_DATE DATETIME,
    MONTH_END_DATE DATETIME,
    PRIMARY KEY (MONTH_START_DATE)
);

INSERT INTO #LAZY_DATE_DIM WITH (TABLOCK)
SELECT DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '18991201')
, DATEADD(DAY, -1, DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '19000101' ))
FROM master..spt_values;

You may find it useful to create a permanent calendar table in your database, but there are ways to generate the data that you need on the fly if that isn't an option.

Next step is to mock up your sample data:

CREATE TABLE #HRAL (
    StartDate DATETIME,
    EndDate DATETIME,
    [Type] VARCHAR(1)
);

INSERT INTO #HRAL VALUES ('2017-09-28','2017-10-02','L');
INSERT INTO #HRAL VALUES ('2017-10-03','2017-10-10','R');
INSERT INTO #HRAL VALUES ('2016-11-10','2016-11-11','L');
INSERT INTO #HRAL VALUES ('2017-08-17','2017-12-25','R');

I broke down the overall query into a few different steps. First step is to join the tables together such that we get a row for every relevant month that intersects with the start and end dates in your HRAL table. I used the following code:

SELECT
  h.*
, dd.*
FROM #HRAL h
INNER JOIN #LAZY_DATE_DIM dd ON
    dd.MONTH_START_DATE > DATEADD(MONTH, -1, h.StartDate)
    AND dd.MONTH_START_DATE < h.EndDate

The intermediate results:

╔═════════════════════════╦═════════════════════════╦══════╦═════════════════════════╦═════════════════════════╗
║        StartDate        ║         EndDate         ║ Type ║    MONTH_START_DATE     ║     MONTH_END_DATE      ║
╠═════════════════════════╬═════════════════════════╬══════╬═════════════════════════╬═════════════════════════╣
║ 2017-09-28 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║ L    ║ 2017-09-01 00:00:00.000 ║ 2017-09-30 00:00:00.000 ║
║ 2017-09-28 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║ L    ║ 2017-10-01 00:00:00.000 ║ 2017-10-31 00:00:00.000 ║
║ 2017-10-03 00:00:00.000 ║ 2017-10-10 00:00:00.000 ║ R    ║ 2017-10-01 00:00:00.000 ║ 2017-10-31 00:00:00.000 ║
║ 2016-11-10 00:00:00.000 ║ 2016-11-11 00:00:00.000 ║ L    ║ 2016-11-01 00:00:00.000 ║ 2016-11-30 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-08-01 00:00:00.000 ║ 2017-08-31 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-09-01 00:00:00.000 ║ 2017-09-30 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-10-01 00:00:00.000 ║ 2017-10-31 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-11-01 00:00:00.000 ║ 2017-11-30 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-12-01 00:00:00.000 ║ 2017-12-31 00:00:00.000 ║
╚═════════════════════════╩═════════════════════════╩══════╩═════════════════════════╩═════════════════════════╝

We can find the overlapping days by taking the maximum of the two start dates, the minimum of the two end days (adding one to the month end date from the calendar table), and using DATEDIFF to find the difference of days. Below is the query that shows some of the intermediate column values:

SELECT
  dd.MONTH_START_DATE
, h.StartDate
, start_dt.dt calc_start_dt
, h.[Type]
, dd.MONTH_END_DATE
, h.EndDate
, end_dt.dt calc_end_dt
FROM #HRAL h
INNER JOIN #LAZY_DATE_DIM dd ON
    dd.MONTH_START_DATE > DATEADD(MONTH, -1, h.StartDate)
    AND dd.MONTH_START_DATE < h.EndDate
CROSS APPLY (
    SELECT MAX(start_dt)
    FROM (VALUES (dd.MONTH_START_DATE), (h.StartDate)) x (start_dt)
) start_dt (dt)
CROSS APPLY (
    SELECT MIN(end_dt)
    FROM (VALUES (DATEADD(DAY, 1, dd.MONTH_END_DATE)), (h.EndDate)) x (end_dt)
) end_dt (dt)

The result set:

╔═════════════════════════╦═════════════════════════╦═════════════════════════╦══════╦═════════════════════════╦═════════════════════════╦═════════════════════════╗
║    MONTH_START_DATE     ║        StartDate        ║      calc_start_dt      ║ Type ║     MONTH_END_DATE      ║         EndDate         ║       calc_end_dt       ║
╠═════════════════════════╬═════════════════════════╬═════════════════════════╬══════╬═════════════════════════╬═════════════════════════╬═════════════════════════╣
║ 2017-09-01 00:00:00.000 ║ 2017-09-28 00:00:00.000 ║ 2017-09-28 00:00:00.000 ║ L    ║ 2017-09-30 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║ 2017-10-01 00:00:00.000 ║
║ 2017-10-01 00:00:00.000 ║ 2017-09-28 00:00:00.000 ║ 2017-10-01 00:00:00.000 ║ L    ║ 2017-10-31 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║
║ 2017-10-01 00:00:00.000 ║ 2017-10-03 00:00:00.000 ║ 2017-10-03 00:00:00.000 ║ R    ║ 2017-10-31 00:00:00.000 ║ 2017-10-10 00:00:00.000 ║ 2017-10-10 00:00:00.000 ║
║ 2016-11-01 00:00:00.000 ║ 2016-11-10 00:00:00.000 ║ 2016-11-10 00:00:00.000 ║ L    ║ 2016-11-30 00:00:00.000 ║ 2016-11-11 00:00:00.000 ║ 2016-11-11 00:00:00.000 ║
║ 2017-08-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ R    ║ 2017-08-31 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-09-01 00:00:00.000 ║
║ 2017-09-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-09-01 00:00:00.000 ║ R    ║ 2017-09-30 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-10-01 00:00:00.000 ║
║ 2017-10-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-10-01 00:00:00.000 ║ R    ║ 2017-10-31 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-11-01 00:00:00.000 ║
║ 2017-11-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-11-01 00:00:00.000 ║ R    ║ 2017-11-30 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-12-01 00:00:00.000 ║
║ 2017-12-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-12-01 00:00:00.000 ║ R    ║ 2017-12-31 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║
╚═════════════════════════╩═════════════════════════╩═════════════════════════╩══════╩═════════════════════════╩═════════════════════════╩═════════════════════════╝

Now I can put it all together by using DATEDIFF along with GROUP BY. The final query:

SELECT
  dd.MONTH_START_DATE
, SUM(DATEDIFF(DAY, start_dt.dt, end_dt.dt)) [days]
, h.[Type]
FROM #HRAL h
INNER JOIN #LAZY_DATE_DIM dd ON
    dd.MONTH_START_DATE > DATEADD(MONTH, -1, h.StartDate)
    AND dd.MONTH_START_DATE < h.EndDate
CROSS APPLY (
    SELECT MAX(start_dt)
    FROM (VALUES (dd.MONTH_START_DATE), (h.StartDate)) x (start_dt)
) start_dt (dt)
CROSS APPLY (
    SELECT MIN(end_dt)
    FROM (VALUES (DATEADD(DAY, 1, dd.MONTH_END_DATE)), (h.EndDate)) x (end_dt)
) end_dt (dt)
GROUP BY
  dd.MONTH_START_DATE
, h.[Type];

The results match yours:

╔═════════════════════════╦══════╦══════╗
║    MONTH_START_DATE     ║ days ║ Type ║
╠═════════════════════════╬══════╬══════╣
║ 2016-11-01 00:00:00.000 ║    1 ║ L    ║
║ 2017-09-01 00:00:00.000 ║    3 ║ L    ║
║ 2017-10-01 00:00:00.000 ║    1 ║ L    ║
║ 2017-08-01 00:00:00.000 ║   15 ║ R    ║
║ 2017-09-01 00:00:00.000 ║   30 ║ R    ║
║ 2017-10-01 00:00:00.000 ║   38 ║ R    ║
║ 2017-11-01 00:00:00.000 ║   30 ║ R    ║
║ 2017-12-01 00:00:00.000 ║   24 ║ R    ║
╚═════════════════════════╩══════╩══════╝

If you prefer to avoid the CROSS APPLY syntax you can accomplish the same thing with a CASE expression.


Dropping and recreating indexes for performance reasons? I'm maintaining Somebody Else's Code.  There's a stored procedure that's run once a day or week (depending on config), that migrates a bunch of records from an active table to an archive.  But it's not a true archive, since there are several live processes that include the archive table in SQL queries, which is properly indexed for the purpose.

Now the history of this story is that the archive table grew and grew, and the archiving SP started taking a very long time.  So Somebody decided it would be a good idea at the beginning of the SP to drop all the indexes on the archive table and recreate them at the end.  Of course, recreating them also takes eons, but arguably not as long as inserting the new records with the indexes in place.

This situation just screams at me: "THIS IS SINFUL!"  It just seems wrong, wrong, wrong to be screwing around with indexes at runtime.  I'm sure this is not an uncommon situation, so what is the "correct" way of handling a situation like this?

Instead of dropping the indexes, I'd recommend disabling them then enabling them.  Preferably through a cursor (ignoring the clustered index) so that if an index is added or removed from the archive table the stored procedure doesn't need to be modified.

This is pretty standard when doing large data warehouse loads, which is basically what you are doing.

UPDATE: Scrub that.  With the very small workload that you are doing just insert the records without dropping and readding the indexes.  If you were moving hundreds of millions of rows then removing the indexes would be worth it.


Can anyone recommend an encryption method for ALL user dbs using SQL Server 2016 STANDARD edition? Our clients are insisting that we now encrypt ALL their SQL Server data at rest, which must include tempdb. I think I have the following options available –

Bitlocker entire drive(s)
TDE (but the cost of Enterprise edition breaks our support model for these clients!)
Always-Encrypted (but more a GDPR/Personal information solution, not for whole databases) – and cannot then “wildchar” search these fields, which is a requirement.
EFS the User databases location on disk
Some 3rd party application that does SQL Server encryption
Others?

Any recommendations appreciated (for SQL Server 2016 STANDARD edition only)

There are 3rd party tools which replicate TDE functionality.

I have tested the following during an exercise to evaluate whether we can move from Enterprise to Standard Edition:

https://www.database-encryption.com/
https://netlibsecurity.com/

In both cases, they seem to work by placing a driver between the SQL binaries and the storage layer, and after configuration are transparent to the connecting application. Queries work in the exact same way as for TDE-enabled databases. Once the data leaves the storage, it is unencrypted.  It would appear as an unencrypted database to all authenticated connections.

They do cost, but I believe there are trial versions available.

In performance tests (10,000 small insert queries into a clustered index) I found that DBDefence closely mirrored the performance of a TDE-enabled database. Query times for Netlib increased by approx 8%.  Obviously your specific scenario may differ.

All my tests were performed on SQL 2016 Standard.


mysql ssl replication - fails while attempting to connect to master I'm trying to troubleshoot why my slave cannot connect to the master.
This is what I see in the output from the command "show slave status \G"

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 10.1.1.1
                  Master_User: test_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 107
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/ssl/ca-self-cert.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: /etc/ssl/server-self-cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: /etc/ssl/server-self-key.pem
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1129
                Last_IO_Error: error connecting to master 'test_user@10.1.1.1:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
1 row in set (0.00 sec)

What I've Checked:

Made sure port 3306 is open on master:

test-dev:/etc/ssl# netstat -lnp | grep 3306
tcp        0      0 10.1.1.1:3306       0.0.0.0:*               LISTEN      -
test-dev:/etc/ssl#

Made sure firewall is open from slave machine:

test_dev_2:/etc/ssl# nc -vzw 3 10.1.1.1 3306
10.1.1.1 (10.1.1.1:3306) open
test_dev_2:/etc/ssl#

Checked permissions on the certs on both master and slave:

 master:

-rw-r--r--    1 root     root       10.4K Nov 11  2014 server-cert.pem
-rw-r--r--    1 root     root        1.9K Nov 11  2014 server-key.pem
-rw-r--r--    1 root     root        1.3K Dec  2 15:45 server-self-cert.pem
-rw-r--r--    1 root     root        1.7K Dec  2 15:45 server-self-key.pem
-rw-r--r--    1 root     root         948 Nov 25 13:14 server-self-req.pem
slave:

-rw-r--r--    1 root     root       10.4K Nov 11  2014 server-cert.pem
-rw-r--r--    1 root     root        1.9K Nov 11  2014 server-key.pem
-rw-r--r--    1 root     root        1.3K Nov 20 12:06 server-self-cert.pem
-rw-r--r--    1 root     root        1.7K Nov 20 12:05 server-self-key.pem
-rw-r--r--    1 root     root         964 Nov 20 12:05 server-self-req.pem

Made sure ssl is enabled on the server.

 mysql> show variables like "%ssl%";
 +---------------+-------------------------------+
 | Variable_name | Value                         |
 +---------------+-------------------------------+
 | have_openssl  | YES                           |
 | have_ssl      | YES                           |
 | ssl_ca        | /etc/ssl/ca-self-cert.pem     |
 | ssl_capath    |                               |
 | ssl_cert      | /etc/ssl/server-self-cert.pem |
 | ssl_cipher    |                               |
 | ssl_key       | /etc/ssl/server-self-key.pem  |
 +---------------+-------------------------------+
 7 rows in set (0.00 sec)

 mysql>

Finally, I ran the command

 "openssl x509 -in server-self-cert.pem -text -noout"

on both the master and the slave and it looked like the certs were okay.
But I'm no expert and am just learning about certs.

I'm not too sure what else I should check / try. I'm a programmer ... not dba or even sys admin so .. I've been running out of ideas.

The manual gives an explanation for IO error number 1129 and also the command that solves the problem:

  Error: 1129 SQLSTATE: HY000 (ER_HOST_IS_BLOCKED)

  Message: Host '%s' is blocked because of many connection errors;
  unblock with 'mysqladmin flush-hosts'

Note that this error may be a consequence of another underlying problem.


Generate All days in moth report using PIVOT of SQL Server My table, tblEmployeeScan(EMPLOYEE_ID varchar(7), ScannedTime)

Everyday each employee scans 2 times, 1 time or  0 time

Now I want to create a stored procedure

Create proc spMonthScanReport
    @MONTH int,
    @YEAR int

which returns a day all in month report like below :

The result you requested won't help you if you want to use this in any type of data set, cause the column names change with every call, a better solution is to use static column names from 1 - 31 (You might not need dynamic sql in this case), but that is up to your need, maybe you just want to export them directly.

And one more thing, you didn't specify the conditions for scan numbers, what if a user scanned more than 2 times a day, what if he didn't scan the whole month, he won't even appear in the results (and to show him, it requires a different query).

USE [YOUR_DATABASE];

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Ahmad Magdy
-- Create date: 2014-10-10 13:10
-- Description:
-- =============================================
CREATE PROCEDURE [PROCEDURE_NAME]
    @Year INT = NULL,
    @Month INT = NULL
AS
BEGIN
SET NOCOUNT ON;

SET @YEAR = CASE WHEN @Year IS NULL THEN YEAR(GETDATE()) ELSE @Year END;
SET @Month = CASE WHEN @Month IS NULL OR @Month NOT BETWEEN 1 AND 12 THEN MONTH(GETDATE()) ELSE @Month END;

DECLARE @FirstDayOfMonth DATE = CAST(@Year AS VARCHAR(4)) + '-' + CASE WHEN @Month < 10 THEN '0' ELSE '' END + CAST(@Month AS VARCHAR(2)) + '-' + '01';
DECLARE @FirstDayOfNextMonth DATE = DATEADD(MONTH, 1, @FirstDayOfMonth);
DECLARE @LastDayOfMonth DATE = DATEADD(DAY, -1, @FirstDayOfNextMonth);

DECLARE @SQLCode NVARCHAR(MAX) =
'SELECT *
FROM
(   SELECT ap.EMPLOYEE_ID, ap.ScannedTime, CONVERT(DATE, ap.ScannedTime) AS Date, ''SCN'' + CAST(ROW_NUMBER() OVER (PARTITION BY ap.EMPLOYEE_ID, CONVERT(DATE, ap.ScannedTime) ORDER BY ap.ScannedTime ASC) AS VARCHAR) AS Type
    FROM tblEmployeeScan ap
    WHERE ap.ScannedTime >= ''' + CAST(@FirstDayOfMonth AS VARCHAR) + ''' AND ap.ScannedTime < ''' + CAST(@FirstDayOfNextMonth AS VARCHAR) + ''' -- You can use YEAR(ap.ScannedTime) = @Year AND MONTH(ap.ScannedTime) = @Month, but that will not make this query Sargable
) pv
PIVOT
(
    MAX(ScannedTime)
    FOR Date IN (';
DECLARE @i DATE = @FirstDayOfMonth;
WHILE(@i <= @LastDayOfMonth)
BEGIN
    SET @SQLCode = @SQLCode + '[' + CAST(@i AS NVARCHAR) + '], ';
    SET @i = DATEADD(DAY, 1, @i);
END
SET @SQLCode = LEFT(@SQLCode, LEN(@SQLCode) - 1);
SET @SQLCode = @SQLCode + ')
) p
--WHERE Type = ''SCN1'' OR Type = ''SCN2'' -- TODO You can use this query to limit the scans to 2
ORDER BY EMPLOYEE_ID, Type; --OPTION (RECOMPILE)';

PRINT CAST(@SQLCode AS NTEXT);

EXECUTE SP_EXECUTESQL @SQLCode;
END
GO


Should you create separate tables for fixed options for a column? As shown below, in multiple places in my DB Design I am creating tables for "options" if you will. Example, RFP Stages. Which would hold things like "Complete", "Bidding", etc.

I'm doing this because it was recommended to me at one point in a question that was deleted. Is this the proper way to do this? Or should I just store these options as text, given there is only 5 or so possibilities?

If I interpret your question correct. I think you have at least 4 alternatives.

ENUM https://www.postgresql.org/docs/current/datatype-enum.html
Table lookup
Local lookup
Plain Text

Personally I prefer table lookup with id and name. You store only the ID. So you have to join tables when fetching. It is easier to add or delete options. And it is easier to search for status 1. And the footprint will be smaller.


replication filtering replicate-rewrite and do-table Mysql 5.6 (innodb engine ) I have a master/slave replication setup based on statement based replication.
On master database named DB1, I have 5 tables and I want to replicate only two table on slave's database named DB2.

Also, I want to change the database name on my slave end.

So my configuration file on Slave end look like this and replication filtering is this:

   replicate-rewrite-db="DB1->DB2"
   replicate-do-table=DB2.table4
   replicate-do-table=DB2.table5

Also I have tried below filtering as well:

   replicate-rewrite-db="DB1->DB2"
   replicate-do-table=DB1.table4
   replicate-do-table=DB1.table5

But in both case records not getting replicated , also no error is reporting.

As per MySQL documentation , replicate-rewrite-db doesn't work with replicate-do-table.

Any method to resolve this? Or is this not possible?

I'm using MySQL 5.6 community edition on Linux server.

I haven't made any changes to master end as it is on customer side.

As you already mentioned documentation that you cannot use table level filtering on replication.
You might like to try another approach instead.
Replicate whole database and change all tables except those you need to use to BLACKHOLE engine.

alter table X engine=BLACKHOLE;


Modelling document sharing permissions with NoSQL I'd like to use NoSQL databases for the first time and was going to use MongoDB.  I have a project where an employee with @employeed_id can go to a web app and get a list of documents that he can read.  If this were a relational database, I might begin my SQL statement like this to fetch all the documents he has read access to (I'll explain in plain english what I'm trying to achieve next):

SELECT * FROM t_document
WHERE EXISTS (
    SELECT 1 FROM t_user
    WHERE t_user.user_id = t_document.author_id
    AND t_user.user_id = @employee_id
) OR EXISTS (
    SELECT 1 FROM t_user
    WHERE EXISTS (
        SELECT 1 FROM t_document_share
        WHERE t_document_share.user_id = t_user.user_id
        AND t_document_share.read_access = 1
    )
    AND t_document.company_id = t_user.company_id
    AND t_user.user_id = @employee_id
) OR EXISTS (
    SELECT 1 FROM t_user
    WHERE EXISTS (
        SELECT 1 FROM t_document_group_share
        WHERE t_document_group_share.read_access = 1
        AND EXISTS (
            SELECT 1 FROM t_user_group
            WHERE EXISTS (
                SELECT 1 FROM t_user
                WHERE t_user.user_id = t_user_group.user_id
                AND t_user.user_id = @employee_id
                ...
                ...
            )
        )

    )
)

Essentially, what I'm saying is get me all documents where at least one of these conditions apply:

I am the author of the document
the document has been shared with me, I have read access, and the document is owned by a company that I am employed at
the document has been shared with a group that I belong to.  The group has read access to the document.  Both I, the group, and the document belongs to the same company

After some quick online research, I read opinions that my use case is not meant for NoSQL.  All these sub queries (and potentially joins) to investigate relationships between different entities is cumbersome to do in NoSQL.  So my question is, can someone confirm if using NoSQL to solve my problem is like trying to make a fish climb a tree?  If so, then I will look for other projects to practice NoSQL on

I guess I'll answer my own question until someone gives a better answer.

When the relationships between entities are known and important to preserve, these relationships are costly to implement and enforce with a NoSQL database.  Looking at the relationships between entities is easy to do with a single SQL statement that is comprised of more sub queries and joins.  Doing something similar with lookups or other javascript/json programmatic instructions is cumbersome and likely to be substantially more computationally intensive when compared with a relational database/SQL approach.

Hence, using a NoSQL database in a situation where the relationships between many entities are known, important to preserve and important to enforce, is like using a steak knife to chop down a tree.  It can be done, but there are better tools for the job.


How to keep a live backup of an SQL server in sync? For our clients we want to provide a solution of having an off-site mirror of their database.

At this moment we do this by scheduling a database-backup every night, move and import that backup in our datacentre and during the day catch each and every sql query that updates or changes data or the schema in our clients' database, send that to our datacentre and run it against the backup we imported the previous night.

This process is brittle to say at least.

What I'm looking for is a way that keeps a database in our datacentre exactly the same as our clients database realtime. Mind you, this is one-way traffic only, from the client to our datacentre.

I have been testing with replication, but that doesn't work as straightforward as I would expect it to.

Are there other straightforward ways to do this?

Have you looked at SQL Server mirroring? You can have high safety mirroring which means that a transaction at the client is not done until the transaction has been mirrored to your data center database. Mirroring is done at the database level and there are different types of mirroring depending on your needs.

Please look at the following to see a more extensive description of mirroring:
https://msdn.microsoft.com/en-us/library/ms189852.aspx


How to Pivot in T-SQL I have a question I can't solve.

My query would be:

SELECT
    f.Title, p.StartOfShowing
FROM
    PlayDates p
INNER JOIN
    PlayDateSections ps ON p.PlayDateId = ps.PlayDateId
INNER JOIN
    Films f ON p.FilmId = f.FilmId
WHERE
    DATEDIFF(d, p.StartOfShowing, '2018-04-30') = 0

The result of the table would be this:

A Quiet Place                      2018-04-30 12:40
A Quiet Place                      2018-04-30 15:05
A Quiet Place                      2018-04-30 17:30
A Quiet Place                      2018-04-30 19:55
A Quiet Place                      2018-04-30 22:20
Avengers : Infinity War            2018-04-30 11:10
Avengers : Infinity War            2018-04-30 11:30
Avengers : Infinity War            2018-04-30 12:00
Avengers : Infinity War            2018-04-30 13:00
Avengers : Infinity War            2018-04-30 13:20
Avengers : Infinity War            2018-04-30 14:00
Avengers : Infinity War            2018-04-30 14:20
Avengers : Infinity War            2018-04-30 14:40
Avengers : Infinity War            2018-04-30 15:10
Avengers : Infinity War            2018-04-30 16:10
Avengers : Infinity War            2018-04-30 16:30
Avengers : Infinity War            2018-04-30 17:10
Avengers : Infinity War            2018-04-30 17:30
Avengers : Infinity War            2018-04-30 17:50
Avengers : Infinity War            2018-04-30 18:20
Avengers : Infinity War            2018-04-30 19:20
Avengers : Infinity War            2018-04-30 19:40
Avengers : Infinity War            2018-04-30 20:20
Avengers : Infinity War            2018-04-30 20:40
Avengers : Infinity War            2018-04-30 21:00
Avengers : Infinity War            2018-04-30 21:30
Avengers : Infinity War            2018-04-30 22:50
Avengers : Infinity War  ATMOS     2018-04-30 12:40
Avengers : Infinity War  ATMOS     2018-04-30 15:50
Avengers : Infinity War  ATMOS     2018-04-30 19:00
Avengers : Infinity War  ATMOS     2018-04-30 22:10
Avengers : Infinity War  PLATINUM  2018-04-30 14:40
Avengers : Infinity War  PLATINUM  2018-04-30 17:50
Avengers : Infinity War  PLATINUM  2018-04-30 21:00
Avengers : Infinity War  PLATINUM  2018-04-30 23:30
Love Simon                         2018-04-30 14:25
Love Simon                         2018-04-30 19:15
Rampage                            2018-04-30 16:55
Rampage                            2018-04-30 21:45

However I wanted to have a pivot query with result like the ones below :

A Quiet Place                     4/30/2018  12:40  15:05  17:30  19:55  22:20
Avengers : Infinity War           4/30/2018  11:10  11:30  12:00  13:00  13:20
Avengers : Infinity War           4/30/2018  14:00  14:20  14:40  15:10  16:10
Avengers : Infinity War           4/30/2018  16:30  17:10  17:30  17:50  18:20
Avengers : Infinity War           4/30/2018  19:20  19:40  20:20  20:40  21:00
Avengers : Infinity War ATMOS     4/30/2018  12:40  15:50  19:00  22:10  NULL
Avengers : Infinity War PLATINUM  4/30/2018  23:30  14:40  17:50  21:00  NULL
Love Simon                        4/30/2018  19:15  14:25  NULL   NULL   NULL
Rampage                           4/30/2018  16:55  21:45  NULL   NULL   NULL

To your data set, I added a row number (ROW_NUMBER), and this field is divided by 5 (you wanted 5 columns in the output) and hold the rest and the hole number of this division in fields inRows , inColumns. Base on this, will do the pivot.

There is a special case when you divide by 5 and have rest = 0 ; for this I use case...

,RN / 5 + CASE WHEN RN % 5  = 0 THEN -1   ELSE 0  END AS inRows

,CASE WHEN RN % 5  = 0 THEN 5 ELSE RN % 5 END AS inCols

;WITH myCTE AS
(
SELECT
  Title,StartOfShowing
  ,ROW_NUMBER() OVER(PARTITION BY Title ORDER BY StartOfShowing ASC) AS RN
FROM
  tblResults
)
, myPivotSource AS
(
SELECT Title,StartOfShowing
   --,RN
   --,(RN - 1 )/ 5  AS inRows
   ,RN / 5 + CASE WHEN RN % 5  = 0 THEN -1   ELSE 0  END AS inRows
   ,CASE WHEN RN % 5  = 0 THEN 5 ELSE RN % 5 END AS inCols
FROM
   myCTE
)

SELECT Title
    , CONVERT(varchar(10),[1],101) As dateofShowing
    , CONVERT(varchar(5),[1],108) AS [1]
    , CONVERT(varchar(5),[2],108) AS [2]
    , CONVERT(varchar(5),[3],108) AS [3]
    , CONVERT(varchar(5),[4],108) AS [4]
    , CONVERT(varchar(5),[5],108) AS [5]
FROM
   myPivotSource AS PS
   PIVOT
   (
      MIN(StartOfShowing) FOR inCols IN ([1], [2], [3], [4], [5])
   )PV
ORDER BY Title, inRows

output of it:

Title                               dateofShowing   1       2       3       4       5
A Quiet Place                       04/30/2018      12:40   15:05   17:30   19:55   22:20
Avengers : Infinity War             04/30/2018      11:10   11:30   12:00   13:00   13:20
Avengers : Infinity War             04/30/2018      14:00   14:20   14:40   15:10   16:10
Avengers : Infinity War             04/30/2018      16:30   17:10   17:30   17:50   18:20
Avengers : Infinity War             04/30/2018      19:20   19:40   20:20   20:40   21:00
Avengers : Infinity War             04/30/2018      21:30   22:50   null    null    null
Avengers : Infinity War  ATMOS      04/30/2018      12:40   15:50   19:00   22:10   null
Avengers : Infinity War  PLATINUM   04/30/2018      14:40   17:50   21:00   23:30   null
Love Simon                          04/30/2018      14:25   19:15   null    null    null
Rampage                             04/30/2018      16:55   21:45   null    null    null

dbfiddle


Create local table and foreign table from the same SQL I want to build a set of PostgreSQL databases that derive from a separate structure definition. The structure is defined as a set of .sql files that will be imported using docker-entrypoint-initdb.d during start.

One of the databases will have regular tables that are created e.g. like this:

CREATE TABLE data."origin" (
    origin_id serial PRIMARY KEY,
    name text UNIQUE
);

Another database needs to be connected to this one using foreign tables that should have the same structure, e.g.

CREATE FOREIGN TABLE data."origin" (
    origin_id serial,
    name text
)
    SERVER localdb
    OPTIONS (schema_name 'data', table_name 'origin');

So the question is, whether there is an elegant way to use the original .sql files from the first database to construct the second one as well.

The only way that we have in mind is to fiddle with sed or similar magic during the image build to add the FOREIGN and the SERVER … stuff in there, remove the PRIMARY KEY etc. This will most likely break sooner or later.

I think I'd try to get a list of tables from the .sql file (or directly from the server to which you applied the .sql file), then feed that list of names to IMPORT FOREIGN SCHEMA ... LIMIT TO (...).  That seems less error prone than what you are envisioning.


Granting access to users to run specific SQL jobs that runs SSIS packages We need to grant a couple of users access to run only specific SQL jobs that runs SSIS packages. Is there a solution that can be used to accomplish this?

Create a stored procedure that uses sp_start_job and grant the execute permission on the stored procedure to the necessary users.  The stored procedure can use EXECUTE AS to elevate its permissions so that it can start the job even though the users don't have permission to start the job.


Having trouble with a query. DBMS Assignment Working on an assignment for school, was able to figure out all but this one.

I need to display the employee name and employee identification number along with their HOD's name and identification number.
The table is structured like this:

(TABLE NAME): EMPLOYEES

EMPLOYEE_ID
EMPLOYEE_NAME
HOD (which is an EMPLOYEE_ID)

In SQL, you can join a table to itself.
Treat the two sides of the join as two separate tables and you're on your way to solving it.


INSERT INTO remote table on different server with OUTPUT clause gives error I have a procedure which makes one or two inserts into a remote table. To accomplish the second insert, I need to know the recently inserted Id of the first insert. To make this work my insert clause fascilitates the OUTPUT clause which let me know the Id of the first insert.

This works fine if the calling database (containing the procedure making the insert) and the receiver database resides on the same SQL Server, which is the case on my development environment. In the production environment, however, the calling database and the receiver database resides on different SQL Servers, which outputs the following error message:

  Msg 405, Level 16, State 1, Procedure ProcedureName, Line
  LineNumber. A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

Edit (the OUTPUT clause into temp table):

    declare @message table(MeddelandeKöId bigint primary key, MessageId bigint)
...
    output
    cast(substring(inserted.ApplicationCustomPublicData, 4, 16) as bigint) MessageQueueId,
    inserted.Id MessageId
    into @message

Which is the best approach to solve the current error? Should I make a simple select, disregarding the output statement, to get the recently inserted row or is there another better solution to this particular problem?

Thanks for your time!

(re-wrote, misunderstood)

If you want the remote ID, then the best way is to use a stored procedure on the remote server and return a result set.

Or don't redirect into a temp table/table variable. This isn't in scope on the remote server

See these on SO:

https://stackoverflow.com/questions/4537560/get-identity-from-another-serverlinked-server/4537600#4537600
https://stackoverflow.com/questions/2621887/sql-server-identity-issue/2621940#2621940


RAISERROR when replication falls behind I have a third party incident management tool, that creates tickets from errors in the SQL logs.

Occasionally replication stops replicating without creating errors in the SQL logs.

I want to create a job that runs every 10 minutes or so to check on replication and then uses RAISERROR to start our ticketing (and alerting) process.

There are a number of stored procedures to Programmatically Monitor Replication that will show issues, and there is also a pretty good solution at Monitoring Transactional Replication in SQL Server(by Francis Hanlon 11 April 2013) that is good start on what I want. But would need to be tweaked a bit to meet my needs.  Before I start reworking Francis's solution I am wondering if there are any other solutions I might leverage on.

I have searched around here and google, and Francis's solution is the only one I found that gets close to inhouse monitoring, without new third party tools.

Are there any solutions to monitor SQL replication real time with T-SQL?

SQL 2008 to SQL 2019
Mostly transactional replication
Same AND cross server replication

TL:DR, there is a good reason, you are not finding any good solutions for what you want.  It does not seem possible, within the constraints you have.

At first glance getting details from the existing stored procedures in Programmatically Monitor Replication putting them together in a table (temp or permanent) to use for tracking and reporting seems like a great idea.  But when you start googling around you don't find anything as simple as you imagined it would be.

The two top solutions google finds involve INSERT-EXEC or OPENQUERY & OPENROWSET, but they have issues and ultimately most googling leads to How to Share Data between Stored Procedures, by Erland Sommarskog

  INSERT-EXEC

  It's a method that is seemingly very appealing, because it's very simple to use and understand... It Can't Nest

  Msg 8164, Level 16, State 1, Procedure BigSalesByStore, Line 8

  An INSERT EXEC statement cannot be nested.

and

  OPENQUERY and its cousin OPENROWSET

  Moreover, it is not aimed at improving performance. It may save you from rewriting your stored procedure, but most likely you will have to put in more work overall – and in the end you get a poorer solution. While I'm not enthusiastic over INSERT-EXEC, it is still a far better choice than OPENQUERY.

The first thing I tried that should have been a really good start.

IF OBJECT_ID('tempdb..#SP_RMHP') IS NOT NULL DROP TABLE #SP_RMHP
GO

Create Table #SP_RMHP(
    publisher SYSNAME
    , distribution_db SYSNAME
    , status INT
    , warning INT
    , publicationcount INT
    , returnstamp CHAR (16)
    )
INSERT #SP_RMHP
  EXEC distribution.dbo.sp_replmonitorhelppublisher

Select * from #SP_RMHP

drop table #SP_RMHP

The problem is that, sometimes you get the error An INSERT EXEC statement cannot be nested. and sometimes you don't.  In SQL 2017 I almost always get it.  In SQL 2014 sometimes I don't, sometimes I do.  Very frustrating and not a path to solution.

So now what?

Focus on pulling data directly from the distribution database tables.

Data supporting the replication process is kept in tables on 4 different database. MSN details are here

msdb Database
Distribution Database
Publication Database
Subscription Database

In one of my replication instances I do not have access to the Subscription servers, so I focused on just the first 3.

The table msdb.sysreplicationalerts has some errors, but everyone I saw was also displayed in the SQL logs, I am looking for evidence of broken replication when there are no errors reported in the SQL logs. So not what I need, but might be helpful to others.

select top (100) * from msdb..sysreplicationalerts

I looked through all of the tables, examined the column descriptions and looked at the data durring uptime.  I found a few potential source, so I set the subscription database offline, in theory this would duplicate an issue with the subscriber that did not directly impact replication or the publication.

With the subscriber database offline (for hours during this testing)

The replication monitor showed green, no Latency, and the Last Synchronization time was updated every couple of minutes to be current.

Clearly Replication is not "working" as the subscriber is offline :(

A possible candidate is 'MSreplication_monitordata' (distribution database), but essentially shows the same thing as the replication monitor, I did not dig deeper, but it is probably the source for Last Synchronization time in the replication monitor

.

Select last_distsync
, DATEDIFF ( MINUTE , last_distsync , GETDATE() )  as 'MinDifflast_distsync'
, agentstoptime
, time_stamp
, DATEDIFF ( MINUTE , time_stamp, GETDATE() )  as 'MinDifftime_stamp'
, GETDATE() as 'GETDATE'
, agent_id --may want this for reporting
, agent_name --may want this for reporting
--, *
from MSreplication_monitordata
where agent_type = 3 --3=Distribution Agent

.

The only other thing that looked promising is the table MSsubscriptions on the publication database.  There are some sequence numbers there that differ when the subscriber is offline, unfortunately they still differ when you bring the subscriber database back online.  Looking at one of live publication replications, there are 100's that differ at any given time.

.

select subscription_seqno
, publisher_seqno
, *
from MSsubscriptions
where subscription_seqno <> publisher_seqno

Now I am going to look into Canary tables as discussed by Kendra Little in this article This is problematic, because it requires modification of databases (add the table) with can be problematic if you are responsible for monitoring, but prohibited from altering the user database.


PLS-00302: component 'COMP_FOR_OLTP' must be declared I'm trying to execute compression script GET_COMPRESSION_RATIO from here:
And this line gives me an error:

comptype => DBMS_COMPRESSION.comp_for_oltp,

  PLS-00302: component 'COMP_FOR_OLTP' must be declared

In oracle documentation, they say "The DBMS_COMPRESSSION package is owned by user SYS, and must be created by SYS. The execution privilege is granted to PUBLIC. Subprograms in this package are executed using the privileges of the current user."

I granted PUBLIC role to my user but don't know how to create  this package by SYS user. Or the problem lays somewhere else?

The terminology changed in 12c. See the constants here.

You could use DBMS_COMPRESSION.COMP_ADVANCED instead.


Adding subpartitions to existing partitioned table In Oracle 11g, is there a way to introduce subpartitions into an existing partitioned table?

I cannot seem to find a combination of EXCHANGE and SPLIT partition that does the right thing.  SPLIT will split a partition into multiple partitions, not introduce subpartitions.  Any suggestions?

I did find an existing post on partitioning an existing non-partitioned table by exchanging to a table with one partition and then using SPLIT, but can't figure out the equivalent process for subpartitions.

A new subpartition can be added to the existing partitioned table using the following command:

ALTER TABLE PART_TEST
modify partition OCT19
add subpartition OCT19AXCS
values ('AXCS');


What happens if you accidentally insert data into a replication target table? We use transactional replication to replicate data from table1 on server1 to table2 on server2. However, one of my colleagues accidentally inserted some data into table2. In order to keep the two tables synced up, should we delete the rows inserted? If so, do we need to reset identity?

Delete the rows to not cause any conflicts later. If you still need the data insert them on the publisher after you deleted them on the subscriber.

You do not need to worry about the identity seed. Replication inserts its own values and does not rely on the identity-auto-value-generation. As this is a table on the subscriber you should not insert data ever again, so to you it should not matter what the identity seed is either.


Error while inserting into array of timestamps Im using postgres 10.1 and the Datestyle config is the following

 DateStyle
 -----------
 ISO, MDY

Im having this error

  ERROR: date/time field value out of range: "1535673858" Hint: Perhaps
  you need a different "datestyle" setting.

While executing the following insert statement:

INSERT INTO ex (taken_ats) VALUES('{ 1535673858 , 1535678856}')

Schema:

CREATE TABLE ex (
    taken_ats TIMESTAMP WITHOUT TIME ZONE[]
)

1535673858 is neither in the ISO format, nor in any of the other valid date input formats.

To convert a Unix epoch value into the internal Postgres type, you must use the single-argument to_timestamp() function:

INSERT INTO ex(taken_ats) VALUES(ARRAY[to_timestamp(1535673858), to_timestamp(1535678856)]);


Is it possible to disable use of fast forward-only cursor on server side? I have an application that is using ODBC to access a SQL Server 2012 database using SQLSetStmtAttr to set the SQL_SOPT_SS_CURSOR_OPTIONS option to SQL_CO_FFO. This means that its reads from the DB are backed by fast forward-only cursor.
This is Ok in most of the cases, however sometimes the performance of the fast forward-only cursor is significantly lower that it would be with a "usual" static one (the one that we would get with default SQL_CO_OFF).
Question: Is there a way to force SQL Server not to use fast-forward cursors?
Obviously, the proper way is to change the application, and that process in on the way, however it's going to take time, and meanwhile I am looking for temporary workarounds.
So far the only idea that I have is to use this: (from Fast Forward-only Cursors)

Implicit Conversion of Fast Forward-only Cursors
Fast forward-only cursors are implicitly converted to other cursor types when:

If the SELECT statement joins one or more tables with trigger tables (INSERTED/DELETED), the cursor is converted to a static cursor

However, that seems a bit too ugly even for a temporary workaround.  Are there maybe better approaches?

Side note:  Main reason why fast forward-only cursors under-perform in this case is that they do not support parallelism.
For non fast forward sys.dm_exec_cursors gives me:
API | Snapshot | Read Only | Global (0)
while fast forward option is:
API | Fast_Forward | Read Only | Global (0)
The snapshot option is 5x times faster than the fast forward one.  If I look at the query plans for both of them, they are not so much different except for DegreeOfParallelism. The snapshot is 16 while fast forward is 0. The problem is that we are reading from the views (that we do not have much control over), and these views are sub-optimal in their design.

No, it is not possible to disable fast forward cursors on the server side, as far as I know.

If your application requests a fast forward cursor, that is what it will get, at least in 'modern' versions of SQL Server (2005+). From Using Implicit Cursor Conversions:

  Fast forward cursors are never converted.

I have long since forgotten what might or might not have worked on SQL Server 2000 and earlier in this scenario. You might find one of the implicit conversion cases in the document you reference can be made to work for you temporarily.

Back to the modern world. The fast forward cursor may compile to a static-like or dynamic-like plan, but the restriction to only generate a non-parallel plan will remain in either case.

There is generally no single class of cursor or set of options that will perform acceptably well in all situations. For best performance, the type of cursor and the options set need to be evaluated for each case.


MySQL ignores foreign keys constraints in a table representing a many to many relationship In MySQL, the foreign keys of a table standing for a many to many relationship do not work even though I did set them to the "child" table named course_inscription.

When I execute a statement to insert a new row into course_inscription, MySQL must launch the error due to the checking of the foreign key constraint, but it does not work. I do not understand why the new row is "added" to the table successfully.

The structure and constraints of the relevant database are:

 CREATE TABLE student (
    idStudent INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    Gender VARCHAR(10),
    birthdate VARCHAR(40) NOT NULL,
    PRIMARY KEY ( idStudent )
  );

  CREATE TABLE subject (
    idSubject INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Credit INT NOT NULL,
    PRIMARY KEY ( idSubject )
  );

  CREATE TABLE course_inscription (
    idSubject INT NOT NULL,
    idStudent INT NOT NULL,
    Classroom VARCHAR(40) NOT NULL,
    PRIMARY KEY ( idSubject, idStudent ),
    FOREIGN KEY (idSubject) REFERENCES subject(idSubject),
    FOREIGN KEY (idStudent) REFERENCES student(idStudent)
  );

  SET FOREIGN_KEY_CHECKS = 1;

The insert statement:

INSERT INTO `studentmgnt`.`course_inscription`
    (`idSubject`, `idStudent`, `Classroom`)
VALUES
    ('1', '1', '12');

What did I do wrong to my database?  How can I correct the issue?

Worth checking that you are using a Storage Engine that supports Foreign Key Constraint checking.

SELECT TABLE_NAME,
       ENGINE
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'dbname';

The MyISAM Storage engine for instance does not support checking of Foreign Key Constraints.

If so and you need Foreign Key checks, try changing the storage engine to InnoDB:

ALTER TABLE my_table ENGINE = InnoDB;

If you need this to be the more standard behavior, look at the --default-storage-engine option in your startup.


how to disable delete in replication Cassandra datastax my previous scenario i  used Sql server  one instance in amazon for live and the second
in local network for archiving by disable delete in replication can i do this in Cassandra or what best practices for this scenario?
 im using datastax  4.8.2

You can disable delete,insert and update using the built-in Cassandra security

CREATE USER test_user WITH PASSWORD xxxx NOSUPERUSER;
GRANT SELECT TO test_user ON my_table;

Unfortunately, it will also disable INSERT and UPDATE. Right now there is no way to disable only DELETE.

Indeed, even if you disable DELETE and allow INSERT & UPDATE, it is still possible for the user to delete data, by just inserting null or updating with null values because inserting null == DELETE in CQL semantics