Insert Values Into Table, Putting Duplicates In Another Table So I posted this question yesterday.  Some of the responses I got were helpful, however it seems my issue is a bit more complex than I originally thought.

After doing some looking the reason I was getting errors with my INSERT statement was because I was having columns like this:

part_number  |  description  | information
------------------------------------------------
331335A11       Desc1          Info1
331335A11       Desc2          Info1

Essentially, there are a number of entries that have the same value for the part_number field (which is suppose to be a UNIQUE column) but different vales for their other columns.  As such the query was trying to insert them into the database, and I have my problem.

So what I am trying to do, because I am unsure just how many records in my table have this problem, is to do the INSERT into my parts table, but every time I get a repeated part_number value, instead of inserting it into the parts table, it is instead inserted into a table called parts_duplicates which won't have the unique restriction for the part_number column (but still have all the same columns as the parts table.  From here I can analyze my incorrect data points and fix them (hopefully).

My only problem is...I have no idea where to even get started on tackling this.  In the question I posted above one of the responses suggested using MERGE and I am currently in the process of testing that, but I am wondering if there is a better way to go about this.

Here is a possible solution that seems to work and doesn't require triggers - you'd have to test it against your real data.

--Demo setup
Declare @Parts table (part_number varchar(30), description varchar(30), information varchar(30))
Declare @PartsTemp table (part_number varchar(30), description varchar(30), information varchar(30))
Declare @PartsDuplicates table (part_number varchar(30), description varchar(30), information varchar(30))

insert into @Parts(part_number,description,information) values
('331335A10', 'Desc1', 'Info1')     --Row already exists on the @Parts table

insert into @PartsTemp(part_number,description,information) values
('331335A00', 'Desc1', 'Info1'),    --No row on the @Parts table and no duplicate
('331335A10', 'Desc1', 'Info1'),    --Row already exists on the @Parts table
('331335A11', 'Desc1', 'Info1'),    --No row on the @Parts table
('331335A11', 'Desc2', 'Info1')     --Duplicate row on the @PartsTemp table

--The solution

--Common table expression to add row number to each PartsTemp row
;WITH PartsTempAndRowNumber
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY part_number ORDER BY description
            ) AS rn
    FROM @PartsTemp
    )

--Insert into @PartsDuplicates where either:
--The rn<>1 - meaning duplicates on the @PartsTemp table
--OR
--The part number already exists on the @Parts table
INSERT INTO @PartsDuplicates (
    part_number
    ,description
    ,information
    )
SELECT part_number
    ,description
    ,information
FROM PartsTempAndRowNumber ptarn
WHERE rn <> 1

UNION ALL

SELECT ptarn.part_number
    ,ptarn.description
    ,ptarn.information
FROM PartsTempAndRowNumber ptarn
JOIN @Parts pt
    ON pt.part_number = ptarn.part_number
        AND ptarn.rn = 1

--Insert rows to @Parts selecting from @PartsTemp where the part_number can't be found
--on the @PartsDuplicates table
INSERT INTO @Parts (
    part_number
    ,description
    ,information
    )
SELECT part_number
    ,description
    ,information
FROM @PartsTemp pt
WHERE NOT EXISTS (
        SELECT *
        FROM @PartsDuplicates
        WHERE part_number = pt.part_number
        )

--Verify @Parts rows
SELECT *
FROM @Parts
ORDER BY part_number

--Verify @PartsDuplicates rows
SELECT *
FROM @PartsDuplicates
ORDER BY part_number

After execution @Parts

| part_number | description | information |
|-------------|-------------|-------------|
| 331335A00   | Desc1       | Info1       |
| 331335A10   | Desc1       | Info1       |

After execution @PartsDuplicates

| part_number | description | information |
|-------------|-------------|-------------|
| 331335A10   | Desc1       | Info1       |
| 331335A11   | Desc2       | Info1       |


could not receive data from WAL stream: ERROR: requested WAL segment XXXXXX has already been removed I did a vacuum on the master server today and the read / slave fell behind.

When I check, that WAL file is in the pg_xlog directory on the slave and in the wal_archive directory which I created on the master.

Why is Postgres not "seeing" that WAL file.

(I saw an answer on Postgres requested WAL segment has already been removed (however it is actually in the slave's directory) however I'm not keen to copy the file into the pg_xlog of the master in case it causes an issue on the master.)

Add a. restore_command that can restore the required WAL file from the archive to recovery.conf (or postgresql.conf from v12 on) and restart the standby.


how to upgrade the config servers to use WiredTiger in mongodb 3.0.0 I am reading the document upgrade config servers to use WiredTiger. In the section of "change config server to use WiredTiger", the second step is "stop the last config server listed in the mongos' configDB setting". But the 5th step is to restart the second config server with WiredTiger. I am confused by the sequence of stopping and restarting. Is there problem on this document?

Another question, why the data in config servers should be exported and uploaded manually? Why the data in replicate set should not be exported and uploaded manually? Instead, the data in replicate set can be imported by "Resync a Member of a Replica Set" please see official doc

The current wording in the documented steps may be somewhat confusing given the relative references to first/last/second config servers.

I've added some context for the documented steps below, but you should consider the manual the definitive source. It's also worth noting that you do not have to upgrade the config servers to use WiredTiger (even if your shards are using WiredTiger). Config servers typically have a small data set and are not under high write load.

Annotated version of the steps from the MongoDB 3.0 Upgrade Guide

Disable the balancer.

Disabling the balancer ensures any active migrations have completed.
Stop the last config server listed in your mongos' configDB setting (will call that config3 for the purpose of these steps):

At this stage you should have:

config1 (running mmap)
config2 (running mmap)
config3 (stopped)

Stopping one of the config servers ensures there are no changes to the metadata in the cluster (chunk splits or migrations cannot be committed without all three config servers available).
Use mongodump to export the config database from config2

After running the mongodump you should have a dump directory with bson files.
Create a new data directory on config2.

The storage format for WiredTiger data is different from the existing mmap data, and cannot use the same dbpath as mmap.
Restart the config2 server with the WiredTiger and appropriate storage options:

mongod --storageEngine wiredTiger --dbpath <newWiredTigerDBPath> ...

At this stage you should have:

config1 (running mmap)
config2 (running WiredTiger with no data)
config3 (stopped)

Note that config2 doesn't have any data yet, because it has just been started up with the new WiredTiger dbpath.
Use mongorestore to load the config database backup you created in step 3.

At this stage you should have:

config1 (running mmap)
config2 (running WiredTiger)
config3 (stopped)

Shut down config2:

At this stage you should have:

config1 (running mmap)
config2 (stopped)
config3 (stopped)

config2 is stopped at this point to ensure no metadata changes can happen when we start config3 up in the next step.
Restart config3.

At this stage you should have:

config1 (running mmap)
config2 (stopped)
config3 (running mmap)

(steps 9-15) These steps are just repeating the same mongodump and mongorestore for each config server. There's a bit of shuffling to ensure you always have at least one config server available, and do not have all three up while you are still migrating data.

Upload the data into config1

At this stage you should have:

config1 (running WiredTiger)
config2 (stopped)
config3 (running WiredTiger)

Start config2

With all three config servers are available & upgraded, changes to the sharded cluster metadata can now resume.
Re-enable the balancer so normal balancing activity & chunk migration can resume.


Why does this implicit join get planned differently than an explicit join? In this answer I explain SQL-89s implicit syntax.
But I noticed different query plans while playing around:

EXPLAIN ANALYZE
  SELECT *
  FROM (values(1)) AS t(x), (values(2)) AS g(y);

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.052 ms
 Execution time: 0.020 ms
(3 rows)

Versus this:

EXPLAIN ANALYZE
  SELECT *
  FROM (values(1)) AS t(x)
  CROSS JOIN (values(2)) AS g(y);
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Subquery Scan on g  (cost=0.00..0.02 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.075 ms
 Execution time: 0.027 ms
(4 rows)

Why would one of them show Subquery Scan?
Isn't the implicit syntax just rewritten to be the same as the explicit syntax?

Isn't the implicit syntax just rewritten to be the same as the explicit syntax?

Not necessarily. You are building on slightly incorrect assumptions. Like I explained under the referenced question:

What does [FROM x, y] mean in Postgres?

Comma-separated items in the FROM list are almost, but not quite identical to explicit CROSS JOIN notation. Explicit joins bind stronger. The query planner has to handle both cases differently in certain cases.

Obviously, the planner is smart enough to be able to treat VALUES expression with a single row expression with a simplified plan. We see a more complex plan for more than one row in the VALUES expression:

EXPLAIN ANALYZE
SELECT *
FROM  (VALUES (1), (2)) t(x)
    , (VALUES (2), (3)) g(y);

 Nested Loop  (cost=0.00..0.11 rows=4 width=8) (actual time=0.059..0.064 rows=4 loops=1)
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.004..0.004 rows=2 loops=1)
   ->  Materialize  (cost=0.00..0.04 rows=2 width=4) (actual time=0.025..0.026 rows=2 loops=2)
         ->  Values Scan on "*VALUES*_1"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.001..0.002 rows=2 loops=1)

Simplifying the query plan is easier for a VALUES expression separated by comma(s). When bound by explicit join(s), Postgres needs to consider join conditions before combining with other comma-separated FROM items. I expect the "subqery scan" we see for this case is a (completely harmless) side effect of the more complex code path for this case.


MariaDB: granting access to a DB before creating the DB I've just discovered that I can create a user and grant right to it on a given database even if that DB doesn't exist yet (it will be created in a second step).

MariaDB [(none)]> CREATE USER foo2@localhost IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.08 sec)

MariaDB [(none)]> GRANT SELECT, UPDATE, DELETE ON  DBNotCreatedYet.* TO 'foo2'@'localhost';
Query OK, 0 rows affected (0.01 sec)

Do you think this procedure can cause troubles and it'd be better to create the DB and THEN granting rights to a user on it? I'd prefer not to put a constraint about what operation to do first but I don't want to run into troubles.

I think I have the answer: it works.

I leave it here just in case someone else run into the same doubt.

Here is an example:

D:\xampp\mysql\bin>mysql.exe -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.1.13-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE USER foo2@localhost IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT SELECT, UPDATE, DELETE ON  DBtest.* TO foo2@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select host, user, password from mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root |                                           |
| 127.0.0.1 | root |                                           |
| ::1       | root |                                           |
| localhost |      |                                           |
| localhost | pma  |                                           |
| localhost | foo2 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-----------+------+-------------------------------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> CREATE DATABASE DBtest CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use DBtest;
Database changed
MariaDB [DBtest]>  CREATE TABLE t1 (a int, b int);
Query OK, 0 rows affected (0.23 sec)

MariaDB [DBtest]> quit;
Bye

D:\xampp\mysql\bin>mysql.exe -ufoo2 -p
Enter password: ********
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.1.13-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use DBtest;
Database changed
MariaDB [DBtest]> show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| t1               |
+------------------+
1 row in set (0.00 sec)

MariaDB [DBtest]> select * from t1;
Empty set (0.00 sec)

MariaDB [DBtest]> INSERT INTO t1 (a, b) VALUES (23, 23);
ERROR 1142 (42000): INSERT command denied to user 'foo2'@'localhost' for table '
t1'
MariaDB [DBtest]>

The user can work in the specified DB, with the granted rights, even if it was created (and granted its rights) before the DB was created.


How to split a single query into two? I'm trying to figure out how to split a single query into two separate queries. The following two queries is what I want:

Query #1:

Label            Value
TOTALSTUFF       100

Query #2:

Label           Value
NONSTUFF        50

Currently the query displays like this:

TOTALSTUFF     NONSTUFF
100            50

If someone would be so kind as to help me out regarding the pattern(s) I should look for when reading/reconstructing these types of queries it would be awesome, since there will be more I'd need to do this with. That may or may not be possible but I'm giving everything a shot at this point.

Here's the original query:

SELECT  FORMAT(SUM(c."COUNTWORKORDERS"),'##,##0')
AS 'TOTALSTUFF' ,FORMAT(SUM(c.countworkorders -
countpositivecontact),'##,##0') AS 'NONSTUFF'
FROM (SELECT DISTINCT
dpt.dateperiodtypeid
,wo.workordercode
, 1 AS countworkorders
, wo.successfulcompletionind AS countpositivecontact
FROM (SELECT 'CALYEAR' AS dateperiodtypeid
UNION ALL
SELECT 'CALMONTH'
UNION ALL
SELECT 'ROLLBCKPM24MBYMAM') ddpt
JOIN wh.cpy_dateperiodtype_dim dpt
ON dpt.dateperiodtypeid = ddpt.dateperiodtypeid
JOIN wh.cpy_dateperiod_dim dp
ON dp.dateperiodtypekey = dpt.dateperiodtypekey
JOIN wh.mwm_workorder_fact wo
ON wo.createdatetime >= dp.startdate
AND cast(wo.createdatetime as Date) <= dp.enddate
JOIN wh.cpy_employee_dim e
ON e.employeekey = wo.assignedtokey
JOIN wh.cpy_employee_dim mgr
ON mgr.employeekey = e.managerkey
JOIN wh.cpy_org_dim o
ON o.orgid = e.orgid
WHERE wo.dnpind = 1) c
WHERE c.dateperiodtypeid = 'CALMONTH';

Without knowing the logic in the query or the underlying dataset, the easiest (but not necessarily the best) way to do what you're asking is to use these 2 queries:

SELECT  'TOTALSTUFF' as 'Label', FORMAT(SUM(c."COUNTWORKORDERS"),'##,##0')
AS 'Value'
FROM (SELECT DISTINCT
dpt.dateperiodtypeid
,wo.workordercode
, 1 AS countworkorders
, wo.successfulcompletionind AS countpositivecontact
FROM (SELECT 'CALYEAR' AS dateperiodtypeid
UNION ALL
SELECT 'CALMONTH'
UNION ALL
SELECT 'ROLLBCKPM24MBYMAM') ddpt
JOIN wh.cpy_dateperiodtype_dim dpt
ON dpt.dateperiodtypeid = ddpt.dateperiodtypeid
JOIN wh.cpy_dateperiod_dim dp
ON dp.dateperiodtypekey = dpt.dateperiodtypekey
JOIN wh.mwm_workorder_fact wo
ON wo.createdatetime >= dp.startdate
AND cast(wo.createdatetime as Date) <= dp.enddate
JOIN wh.cpy_employee_dim e
ON e.employeekey = wo.assignedtokey
JOIN wh.cpy_employee_dim mgr
ON mgr.employeekey = e.managerkey
JOIN wh.cpy_org_dim o
ON o.orgid = e.orgid
WHERE wo.dnpind = 1) c
WHERE c.dateperiodtypeid = 'CALMONTH';

and

SELECT 'NONSTUFF' as 'Label', FORMAT(SUM(c.countworkorders -
countpositivecontact),'##,##0') AS 'Value'
FROM (SELECT DISTINCT
dpt.dateperiodtypeid
,wo.workordercode
, 1 AS countworkorders
, wo.successfulcompletionind AS countpositivecontact
FROM (SELECT 'CALYEAR' AS dateperiodtypeid
UNION ALL
SELECT 'CALMONTH'
UNION ALL
SELECT 'ROLLBCKPM24MBYMAM') ddpt
JOIN wh.cpy_dateperiodtype_dim dpt
ON dpt.dateperiodtypeid = ddpt.dateperiodtypeid
JOIN wh.cpy_dateperiod_dim dp
ON dp.dateperiodtypekey = dpt.dateperiodtypekey
JOIN wh.mwm_workorder_fact wo
ON wo.createdatetime >= dp.startdate
AND cast(wo.createdatetime as Date) <= dp.enddate
JOIN wh.cpy_employee_dim e
ON e.employeekey = wo.assignedtokey
JOIN wh.cpy_employee_dim mgr
ON mgr.employeekey = e.managerkey
JOIN wh.cpy_org_dim o
ON o.orgid = e.orgid
WHERE wo.dnpind = 1) c
WHERE c.dateperiodtypeid = 'CALMONTH';

This is just using Column aliases as described here - https://msdn.microsoft.com/en-GB/library/ms176104.aspx

Edited to add a better explanation:

The change is in the columns section of the query. In your original you're requesting that SQL Server returns:

SELECT  FORMAT(SUM(c."COUNTWORKORDERS"),'##,##0')
AS 'TOTALSTUFF' ,FORMAT(SUM(c.countworkorders -
countpositivecontact),'##,##0') AS 'NONSTUFF'

which is 2 columns, one named TOTALSTUFF and one named NONSTUFF. In the first of my reworked queries we request:

SELECT  'TOTALSTUFF' as 'Label', FORMAT(SUM(c."COUNTWORKORDERS"),'##,##0')
AS 'Value'

First we request a dummy column 'TOTALSTUFF' and alias it as 'Label'. Next we pull back the value that was aliased as TOTALSTUFF in your original query and this time alias it as 'Value'

The logic behind your 2 values TOTALSTUFF and NONSTUFF is the same, so I could just split out the columns in the column list.


Dynamic paths for postgres file_fdw (csv) I've got a postgres database which relies on the use of foreign tables to support the loading of large data sets based on user-provided (system sanitized) tables. I'd use COPY, but the tables loaded on a recurring (but non-daily) cycle and have complex load parameters which we've built into functions (using a combination of UPDATE WHERE, DELETE WHERE, TRUNCATE AND INSERT, INSERT, ON CONFLICT)

The process works well, and is stable.  While I'm generally happy with it, I realize that it opens up the possibility that at some point the file paths specified in the CREATE FOREIGN TABLE command will change (e.g. from /home/postgres/path_primary/table.csv to /home/postgres/path_secondary/table.csv).

My question: Is there a way to point the base of the file path to a non-static value to abstract that change? Or would I be better off writing a script to collect the list of foreign tables, drop them, and rebuild the DDL with new filepath arguments?

My question: Is there a way to point the base of the file path to a non-static value to abstract that change? Or would I be better off writing a script to collect the list of foreign tables, drop them, and rebuild the DDL with new filepath arguments?

You can point to a symlink. However, I wouldn't. I would keep things running just the way you're doing it now. What's the reason to do that? I imagine under any reasonable load the DDL is not an issue. But, it may be worth experimenting and reporting back.


What does 0 in DATEDIFF(MINUTE, 0, ) actually mean? So, our data team asked for some help in solving a problem they had.  I eventually tracked it down to some really out of range data (1/1/0001) and a DATEDIFF function they were using.  While I've solved their problem, It came about that I don't actually know what the 0 turns into when used as they were using it.

I originally thought it was closer to an integer overflow rather than a true conversion error, but that's not it.  I tried it on a SQL 2016 box with DATEDIFF_BIG and same error.  I have a sample for you guys below to play with along with what works and what doesn't.

/** Setup The Sample */
DECLARE @TestValue DATETIME2(7)
SET @TestValue = '0001-01-01 10:30:00.0000000'

/** Conversion Error
Msg 242, Level 16, State 3, Line 10
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
*/
SELECT DATEDIFF(MINUTE, 0, @TestValue)

--Also does not work, same error.
SELECT DATEDIFF_BIG(MINUTE, 0, @TestValue)

/** Works */
SELECT DATEDIFF(MINUTE, '1/1/1900', @TestValue)

/** Works */
SELECT DATEDIFF(MINUTE, CAST(0 AS DATETIME), @TestValue)

/** Doesn't Work, you can't cast 0 to a DATETIME2 */
--SELECT DATEDIFF(MINUTE, CAST(0 AS DATETIME2), @TestValue)

/** Works (or no error, which is fine)*/
SELECT DATEDIFF(MINUTE, 0, TRY_CAST(@TestValue AS DATETIME))

Bonus Question, since 0 doesn't work in all cases for DATETIME2, what's the alternative?

WHAT WE DECIDED TO DO

So, I have started recommending my team do the following, since you see 0 in lots of examples for datemath (first day of month, etc.).  So I recommend that you do an explicit cast of 0 to datetime, then continue as you will.  This will avoid the error while still working. So:

DATEDIFF(MINUTE, CAST(DATETIME, 0), <Date>)


Why does MySQLTuner only see 4Gb of RAM? I ran MySQLTuner to verify the configuration of a MySQL database, and it reported this problem:

[!!] Maximum possible memory usage: 40.9G (1022% of installed RAM)

These figures come from computing max_connections multiplied by read_buffer_size + sort_buffer_size + join_buffer_size in the my.cnf file, which effectively gives 40Gb.

However, the server has 128Gb of RAM.  It appears that MySQL only sees 4Gb.

At first I thought that this was because a MySQL 32-bit binary on a 64-bit OS can effectively access only 4Gb of RAM.  However, both MySQL binary and OS are 64-bit:

root@box# isainfo -v
64-bit amd64 applications
        pclmulqdq aes sse4.2 sse4.1 ssse3 popcnt tscp ahf cx16 sse3 sse2 sse
        fxsr mmx cmov amd_sysc cx8 tsc fpu
32-bit i386 applications
        pclmulqdq aes sse4.2 sse4.1 ssse3 popcnt tscp ahf cx16 sse3 sse2 sse
        fxsr mmx cmov sep cx8 tsc fpu

root@box# uname -a
SunOS box 5.11 11.2 i86pc i386 i86pc

root@box# file /usr/local/bin/mysql
/usr/local/bin/mysql:  ELF 64-bit LSB executable AMD64 Version 1 [SSE2 SSE FXSR CMOV FPU], dynamically linked, not stripped

and there is no limit on how much memory a process can access:

root@box# ulimit -a
address space limit (kbytes)   (-M)  unlimited
core file size (blocks)        (-c)  unlimited
cpu time (seconds)             (-t)  unlimited
data size (kbytes)             (-d)  unlimited
file size (blocks)             (-f)  unlimited
locks                          (-x)  not supported
locked address space (kbytes)  (-l)  not supported
message queue size (kbytes)    (-q)  not supported
nice                           (-e)  not supported
nofile                         (-n)  256
nproc                          (-u)  29995
pipe buffer size (bytes)       (-p)  5120
max memory size (kbytes)       (-m)  not supported
rtprio                         (-r)  not supported
socket buffer size (bytes)     (-b)  5120
sigpend                        (-i)  128
stack size (kbytes)            (-s)  8192
swap size (kbytes)             (-w)  not supported
threads                        (-T)  not supported
process size (kbytes)          (-v)  unlimited

Is there another way I can see how much memory MySQL is effectively using?

(In case you ask, shell is tcsh; I cannot tag the post "tcsh" due to insufficient privileges.)

It is not a MySQL thing - MySQLTuner itself cannot see more memory if it is running with 32bit perl interpreter.


SSRS Lookup #Error on some rows I'm having trouble working out the details with an IIF containing some lookup statements.

The setup:

Dataset 1: Transactions
Fields: itemid, transactionnotes, rdesc

Dataset 2: EmployeeInfo
Fields: code, fname, surname

I have an expression column with the following:

=iif(Fields!itemid.Value = 100785,
    "Allocated to: "
    & lookup(cint(Fields!transactionnotes.Value), fields!code.value, fields!fname.value, "EmployeeInfo")
    & " "
    & lookup(cint(Fields!transactionnotes.Value), fields!code.value, fields!surname.value, "EmployeeInfo")
  , Fields!rdesc.Value)

itemid = 100785 indicates a line in the DB where it records a transaction allocated to another staff member. In all isntances where the itemid = 100785 the transactionnotes field (datatype string) will contain a numeric value of who it was allocated to. For those lines, this expression is working exactly as intended.

The problem:

For rows that do not contain itemid = 100785, the rdesc value should just be a string description of the transaction. For about half the rows this is working fine. But for about half the rows it shows #error. I've dug into the DB and the transactionnotes.value is NULL for all the rows getting #error. But its also NULL for the rows that are working. I'm including a screenshot so you can see the relevant values for itemid and rdesc for some rows that work and some that get #error. Description Calc is the field with the expression.

If I had to guess I'd say that the datatype of transactionnotes does not match the datatype of code. I suggest trying ...

=iif(Fields!itemid.Value = 100785,
    "Allocated to: "
    & lookup(cint(Fields!transactionnotes.Value), cint(fields!code.value), fields!fname.value, "EmployeeInfo")
    & " "
    & lookup(cint(Fields!transactionnotes.Value), cint(fields!code.value), fields!surname.value, "EmployeeInfo")
  , Fields!rdesc.Value)

or perhaps it should go the other way using CStr() instead of CInt() for both transactionnotes and code. Try it out and see.


CopyDatabaseWizard.exe issue on SQL 2014 I have four servers running Server 2008r2 SP3, of which three were running SQL Server Express 2008r2 and one was running SQL Server Standard 2008r2. After much ado I got these all upgraded to be running SQL Server Standard 2014 SP2.
These servers have .NET 4.6.2 installed and Powershell 2.0 installed.

Unfortunately, now I'm getting this issue in the Application Log on each of these four servers:

Event ID 63:
SideBySide (Application Log)
Activation context generation failed for "c:\program files (x86)\microsoft sql server\120\Tools\Binn\managementstudio\CopyDatabaseWizard.exe".
Error in manifest or policy file "c:\program files (x86)\microsoft sql server\120\Tools\Binn\managementstudio\CopyDatabaseWizard.exe" on line 8.
The value "1.0" of attribute "version" in element "assemblyIdentity" is invalid.

All I can find are some vague technet posts suggesting to use a hex editor to edit the assembly, but that apparently leads to another issue. Editing the exe shouldn't be necessary IMO.

Related technet posts:

https://social.technet.microsoft.com/Forums/office/en-US/43ff2fe9-3112-4c0e-ba57-5a2d85a018ab/exception-has-been-thrown-by-the-target-on-an-invocation-during-installation-of-sql-2014-ctp2?forum=sqlsetupandupgrade
https://social.msdn.microsoft.com/Forums/silverlight/en-US/fa5901bc-a93e-444d-8208-8f2f5584ed94/sidebyside-63?forum=sqlgetstarted
https://connect.microsoft.com/SQLServer/feedback/details/1764660/sidebyside-error

Has anyone run across this or know how to fix this issue? Or is this issue safe to ignore?

As weird as it is, it currently appears I have resolved this issue by upgrading Powershell. On one server the powershell version was 3.0 and on the other three it was still 2.0.

You can check your powershell version installed by running:

$PSVersionTable.PSVersion

The upgrade package I used was Windows Management Framework 4.0, which includes Powershell 4.0. PS 4.0 is the last version supported for Server 2008R2.
Here's a download link to WMF 4.0:
http://www.microsoft.com/en-us/download/details.aspx?id=40855

Edit

Credit to Antoine Hernandez; Powershell 5.0 is supported on Windows Server 2008r2.
Download: https://www.microsoft.com/en-us/download/details.aspx?id=50395

Also quick update after a few days: I haven't had any instances of this issue since I updated Powershell to v4.0.


Modelling an E-R diagram for a Pizza place database, need help representing the payment method for the customer I am modeling a "pizza place" and a customer has an account info. I created a separate table for account info. In here a customer will have an amount spent and method of payment attributes. The payment methods can be cash, cheque or credit card. If it is credit card, it needs to have a number. I'm wondering how to best represent this attribute. Does it need to be another table.

Credit card info should be in a separate table because normally, you need to have more than just a number. You will also need, at the very least, the card's expiry date. You may also need to store cardholder information (name, address, etc...) in case it is different from the name on the account. All of that extra information is only relevant to customers that pay by credit card. And will you allow multiple cards for one account? How will you handle cards that expire and need to be replaced? Delete the old record, mark as expired, add a new one? Or just update existing data?

And although it's not necessarily going to be reflected in an ER diagram, you have to consider security if you plan to implement this. You might want to start looking here: https://www.pcisecuritystandards.org/


How to model optional product delivery companies I would like to model the following in my database. I have several ways to deliver a product. Each product can be sent by only some, or by every method of delivery.

In my current design every product has a table where I type available  methods of delivery, for example: ups;dhl;fedex.

Do you have a better solution?

Assuming tables like:

CREATE TABLE PRODUCTS
( PRODUCT_ID ... NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE DELIVERY_COMPANIES
( COMPANY_ID ... NOT NULL PRIMARY KEY
, ...
,
);

I assume what you mean by ups;dhl;fedex is a table like:

CREATE TABLE PRODUCT_DELIVERY_COMPANIES
( PRODUCT_ID ... NOT NULL
, COMPANY_ID ... NOT NULL
, ...
,    CONSTRAINT ... FOREIGN KEY (PRODUCT_ID)
     REFERENCES PRODUCTS (PRODUCT_ID)
,    CONSTRAINT ... FOREIGN KEY (COMPANY_ID)
     REFERENCES DELIVERY_COMPANIES (COMPANY_ID)
,    CONSTRAINT ... PRIMARY KEY (PRODUCT_ID, COMPANY_ID)
);

and that seems about right.


sql query to get results specific rows but count of all the rows of table I am trying to create a query to get specific rows on conditions and along with that, all the rows present in the table. I am using the following query for this
select name, email, count(users.id) OVER (PARTITION BY users.id) from users where user.city='new york';

with the count(), I want to get the count of all the rows present irrespective of the where clause.
I am trying to reduce the query because right now in my list page view I am doing two queries one for count of all the records and other for the filtered list of records. But I want to do that in the single query.

with the count(), I want to get the count of all the rows present
irrespective of the where clause.

This can be done with an independent subquery in the select-list, if it returns a single value, as in:
select name, email, (select count(*) from users) as "Total" from users WHERE ...

Example:
postgres=> select relname,(select count(*) from pg_class) as "Total"
           from  pg_class where relname like 'a%';

              relname              | Total
-----------------------------------+-------
 administrable_role_authorizations |   348
 applicable_roles                  |   348
 attributes                        |   348

Note that from the point of view of the relational model, this is an unorthodox trick . Normally each query is an answer to one question with a structure of result that fits exactly that question. Doing two queries would be right here.


Small startup: Cassandra + Hadoop or ElasticSearch To give some context, I am from a small start up (4 team members) and we work in the education space.

We've developed an e-learning platform that several schools are already using. We currently have around 20k page requests to the platform coming in a day.

We want to start gathering every bit of data we can about our users so that we can start doing some data analysis and learning from our data.

I've spent the last couple of weeks learning up about Cassandra as I think it will be a great solution for gathering all this user data and being able to read it quickly. I've also learned that to do things like SUM and AVG queries, I'll have to use something like Hadoop to be able to run mapreduce operations on the data as Cassandra doesn't allow for this sort of querying.

Our current MySQL data base is VERY small (150mb or so). With that in mind, is it worth going down the Cassandra road yet?

I'm beginning to wonder if I'm looking too far ahead, so far as scalability is concerned.
An option I have been considering is to simplify things and begin with ElasticSearch, storing all our user event data and allowing us to easily search it without having to use something like Hadoop.

The main question: Is Cassandra going to be suitable for us yet at such a small scale? Or should we consider a simpler noSQL store like ElasticSearch?

If you foresee that moving to Cassandra is definitely in your future, it will be easier to do while your dataset is still small and manageable.  Also, as you learn and get a feel for Cassandra, a small dataset is a better one to make mistakes on (and thus, easier to correct them).  That way your data model is solid by the time your dataset gets big, and that's when it really matters.  And IMHO, there is no such thing as "too small" for Cassandra.

One of our applications uses Cassandra and ElasticSearch in prod.  Based on those experiences, I would offer some caution about using ElasticSearch as a persistent datastore.  We've seen it lose writes fairly often.  Have a read through this discussion on Quora, appropriately titled: Why should I NOT use ElasticSearch as my primary datastore?  That being said, it works great as a search engine.


I can offer how I'd handle it in MS SQL, using PIVOT and ROW_NUMBER().  These don't appear to exist in MySQL (perhaps the latest version has added them?), so I'm afraid it won't be helpful to you.  Perhaps someone more familiar with MySQL can use this to help get to a solution.

SELECT
    "1"
    + CASE WHEN "2" IS NULL THEN '' ELSE '-' + "2" END
    + CASE WHEN "3" IS NULL THEN '' ELSE '-' + "3" END AS Applications,
    Instances
FROM
    (
    SELECT
        SID, ProgramName, ROW_NUMBER() OVER (PARTITION BY SID ORDER BY ProgramName) AS Seq, Instances
    FROM
        (
        SELECT
            SID, ProgramName, COUNT(*) AS Instances
        FROM
            Login_Apps
        GROUP BY
            SID, ProgramName
        ) AS X
    ) AS Y
PIVOT
    (MIN(ProgramName) FOR Seq IN ("1", "2", "3")) AS Z


Similar hardware, Same DBconfiguration and Database but different status variables result between Windows and Linux server I have two database servers - one is Windows - 2008 and another is RHEL5 with similar hardware configuration. Both OS are 64 bit.

MySQL 5.1.58 community edition is installed on Windows servers while MySQL 5.1.73 is installed on Oracle Linux. Same database is installed on both servers - i..e same number of tables with same data.
At a time, if I run `show global status like 'Qcache%' on both servers and below is the comparison result:

Status variables       Linux           Windows
Qcache_free_blocks     24              54301
Qcache_free_memory     53025440        151737216
Qcache_hits            162509          44648032
Qcache_inserts         33073           6866308
Qcache_lowmem_prunes   0               657080
Qcache_not_cached      11291            994889
Qcache_queries_in_cache 7446            112378
Qcache_total_blocks     14959           279520

Below is the result of show global variables like 'query_cache%';:

Status variables                Linux        Windows
query_cache_limit               2097152      4194304
query_cache_min_res_unit        1024         4096
query_cache_size                67108864     367001600
query_cache_type                ON           ON
query_cache_wlock_invalidate    OFF          OFF

Why is there huge differences?
Also, In Linux, Qcache_free_blocks is only 3 while Qcache_free_memory is about 53MB?

Since you started both servers, you have executed (approximately) 162509 + 33073 + 11291 = 206,873 queries on the Linux server and 44648032 + 6866308 + 994889 = 52,509,229 queries on Windows. Why would you expect similar numbers when one has done more work than the other?

The ratios, however, are similar with:

162509 / (162509 + 33073) ~= 83%
44648032 / (44648032 + 6866308) ~=87%

80-90% ratio of query cache hits. You should only compare statistics over the same period of time, for example, by using pt-mext -- mysqladmin ext

In order to answer specific questions about size, you should provide the output of SHOW GLOBAL VARIABLES like 'query_cache%'; and if the OSs and mysql versions are 32 or 64 bits.

UPDATE: You have complete different configuration on Linux and Windows, window's query cache is 30x larger. Why would you expect equal or similar statistics from both, when you are not even executing the same queries? I do not understand why you are surprised that you get different "numbers" when you input different ones: free blocks is the amount of blocks that are not in use by query results. The Windows one is not yet full, or it has deallocated more than the Linux one.


Oracle sequences equivalent in MySQL I did ask same question on stackoverflow, but no response, hence I thought database experts will help me to solve this.

I have following table similar to Oracle user_sequences.

I have logic of sequence prefix/suffix something, but for simplicity, I'm skipping as matters less here.

create table my_seq(
min_value integer,
Max_value integer,
last_value integer,
increment_by tinyint,
customer_id integer);

Assume in current table there are two records.

insert into my_seq(min_value,max_value,last_value,increment_by,customer_id)
  values(1,99999999,1,1,'foo#',1),(1,999999999,100,1,'foo#',2);

My foo table structure is like,

create table foo(id Auto_increment,foo_number varchar(20),customer_id integer);

Constrained:
I can't use MySQL AUTO_INCREMENT columns as foo contains different customers data, and every customer could opt foo_number auto generation or manual entry and there should be gap if customer opted for auto_generation. So customer=1 has opted for it, foo# should be 1,2,3,4 etc, no gaps are allowed.

So far so good, with auto increment logic that we have implemented if my app runs in single thread. We generate foo_number and populate in foo table, along with other data points.

I simply do a query to get the next auto#.

select last_number from my_seq where customer_id=?;

reads the # and the update the record.

update my_seq set last_number=last_number+increment_by where customer_id=?;

Problem:
When multiple concurrent session tries the run select last_number from my_seq..., it returns same foo_number multiple times. Also, I can't enforce single thread in application because of application side limitation and performance bottleneck, hence need to solve it in database side.

Please suggest, how I could avoid duplicate numbers? Please help, thanks in advance.

I did google, many stackoverflow links suggests get_last_id(), as you could see, I can't use it. Please suggest.

Possible solution.

Each thread which will obtain the values from "sequence generator" have some customer_id value it will use when ask for next value. Additionally, it generates some random and unique (guaranteed!) value when starting (if all threads works in one OS instance, it can be thread's PID, for example, if not, it can be PID+IP or something else - but with 100% no collision). This random allows to distinguish the threads used the same customer_id value.

You create the my_seq table as shown above:

create table my_seq(
min_value integer,
Max_value integer,
last_value integer,
increment_by tinyint,
customer_id integer);

Additionally you create the table looks like

CREATE TABLE my_gen (
customer_id integer,
session_id integer,
generated_value integer,
UNIQUE KEY idx (customer_id,generated_value)
);

When a thread needs next sequence value, it tries to obtain it by the next code:

SELECT last_value+increment_by
    INTO @next_val
    FROM my_seq
    WHERE customer_id = @customer_id;

In that timepoint any parallel thread can obtain the same value... to avoid it, the threat tries to secure this value by:

INSERT IGNORE INTO my_gen (customer_id, session_id, generated_value)
    VALUES (@customer_id, @session_id, @next_val);

Only one thread used the same @customer_id can insert the record to this table with generated value - all another will not because of unique key.

And now the thread checks was the secure process for that value successfull:

SELECT *
FROM my_gen
WHERE customer_id = @customer_id
  AND session_id = @session_id
  AND generated_value = @next_val;

If 1 record returns - the secure process is successfull, the thread can use obtained value, and it updates my_seq table:

UPDATE my_seq
SET last_value = @next_val
WHERE customer_id = @customer_id;

If 0 records returns - the secure process is unsuccessfull, obtained value was obtained, secured and used by another thread with the same customer_id... and this thread needs to repeat the whole process of generate sequence value.

You may to assemble this operations into one stored procedure or function. Moreover, you can organize the cycle directly into SP which will execute till the next value obtained... or any amount of attempts performed to avoid infinite loop.

Or simply lock the my_seq table exclusive for read and write before first read and till last update - no parallel thread can do something to interfere in that case.


Does PostgreSQL take time to start streaming replication after restart master? I set streaming replication in postgresql 9.3. I have one master and 3 slaves and replication works fine.

But when i restart the master server and login using postgres database, pg_stat_replication does not show replication details immediately. It takes several seconds ( around 5 seconds ) show replication details .

I want to know why this happens ? whether it's problem with pg_stat_replication ?

I want to make sure replication start as soon as server starts .

I want to make sure replication start as soon as server starts .

You can't. The server needs to start up, and it can only establish a streaming replication connection when it's up and ready.

  I want to know why this happens

Because there's no reason not to accept connections before streaming replication begins.

Among other things, if you're using WAL shipping instead of streaming replication, there might be no replication connection to wait for. Even if you're using streaming, you probably want the replica to keep on serving results if it loses its connection to the master.

  My thought was, when server started it will be ready to accept queries, and then after few seconds server start streaming replication. I though data will be lost in those few seconds.

It isn't.

The server is in recovery mode. Attempts to perform read/write queries will be rejected. This is the case whether or not there's a streaming replication connection.

After all, a server in hot standby mode that uses only a restore_command (no streaming replication) also rejects read/write queries.

You don't need to take any special action.

(In future, please try to include the background, the underlying problem, in your questions, so there's less guesswork and back and forth).


Audit 'sa' login in SQL Server 2005 I have a requirement where I need to audit (and potentially be notified) whenever the 'sa' user account logs into an SQL Server 2005 instance. Additionally, there is a preference that the database that the login accesses is also auditted.

I understand that I could use extended events or server audit in a newer version of SQL Server, but as I am limited to SQL Server 2005, the only option I can think of is to:

set up a server-side trace for user logon events for the 'sa' user into a table
set up a job to email the contents of the table at a specified interval

Are there more efficient ways to accomplish this in 2005 ?

I would say consider logon triggers to accomplish this. This way you aren't using a server side trace. You can also audit all sucessful logins in the SQL Server error log (right click on instance, properties, security and then choose both successful and failed logons.. I don't believe this shows you the DB context info, though)

Also - I would seriously consider upgrading to a later version of SQL if possible. SQL 2005 is two (three if you count 2008 R2) versions back. I know you probably know that but I'd feel bad if I didn't call it out :-)

I would also highly discourage the use of SA for any logons. SA is a highly privileged account. It is -the- highly privileged account in SQL Server. Everyone knows there is an account named SA and it can be prone to hack attempts. I tend to push for windows authentication only and ensure a group is added that the proper DBAs team can properly and securely added to. I often disable the SA account in mixed mode and will create another account with SA rights but a non descript name. If, for some reason, the SA account needs to stay around, I try and give that a horrible password and store it someplace really safe and not use it.

By not having an SA account used by so many people that you need to audit its usage, you can give more granular permissions to do the required activities in SQL and no more. Least privilege and tight access lists will take you much further than watching SA account activity.

At any rate, Logon triggers may be the best bet. Trace would work but there is a cost associated with that (there is with logon triggers also but my guess is the cost of trace will be more expensive for you)


Issues regarding phpMyAdmin - character set I'm trying to migrate a Wordpress site from one server to another. Everything is going fine apart from the database migration.

I have my database 'A', and using phpMyAdmin I am attempting to export it so that I may use phpMA on the destination server to to import it.

The problem appears to be that when I'm importing the database, it doesn't recognize the character type utf8mb4 despite my efforts to change the character set/type in phpmyadmin to utf8 via queries and being told that it has changed by the console.

I also ensured that UTF8 was selected on both locations and that doesn't seem to help at all. I've tried to search for help but I've been unsuccessful.

MySQL did not have utf8mb4 until version 5.5.3; are both versions newer than that?

Is the exported file a text file?  If so, look in it for utf8 and see if it says utf8 or utf8mb4.

Did you use any "character set" argument on mysqldump when exporting?  What about on importing?  For mysql and phpmyadmin it needs to be utf8mb4.  For the rest of the world, UTF-8 or maybe UTF8.


Exit a query in a case statement? I'm trying to setup a query, so that it will compare two dates from two different tables, and if they are equal, then the query exits. If they are not equal, then the query will continue and insert some stuff. I can't figure out how to get it to do what I want however.

SELECT TOP(1) @dateA=a.someDate
FROM a
ORDER BY DESC;
SELECT TOP(1) @dateB=b.someDate
FROM b
ORDER BY DESC;

CASE WHEN @dateA=@dateB THEN raiseerror('dates equal',20,-1) with log;

Insert statements;

Any help would be super appreciated.

CASE is an expression (not a statement) and cannot be used for control-of-flow like that - not to call commands, not to return more than one column/value, not to be used as a command on its own.

It seems to me you can just use IF to raise the error when the dates are equal, otherwise run the inserts.

IF @dateA = @dateB
BEGIN
  raiseerror('dates equal',20,-1) with log;
END
ELSE -- maybe you don't need a batch-aborting, logging error level
BEGIN
  INSERT ...
END

You could also do it the other way. Run the inserts only if the dates are not equal, otherwise raise the error:

IF @dateA <> @dateB
BEGIN
  INSERT ...
END
ELSE
BEGIN
  raiserror ...
END

If you thought to use the error only for the purpose of getting out of running the inserts, then you can just remove everything from ELSE down, since the only way the inserts will run is when @dateA and @dateB are not equal:

IF @dateA <> @dateB
BEGIN
  INSERT ...
END

I have scaled way back on being pedantic about things like rows (vs. "records"), and columns (vs. "fields"), but the whole expression vs. statement thing is a very important distinction, for exactly this reason. See "Dirty Secrets of the CASE Expression."


Querying 500 million records in Oracle view takes forever I really need professionals help now after wasting a week of time to solve my problem and searching on Stack Overflow. I have a view created on top of two different tables. This view has 500 million records.

view:-

CREATE VIEW VZ.SERVICE_UTILIZATION
AS (
select T0base.SNO as SNO,
    T0base.SERVICE_CALL_DATE as SERVICE_CALL_DATE,
    T1.LOOKUP_VALUE as ORIG_SYS,
    T2.LOOKUP_VALUE as DEST_SYS,
    T0base.MSG_ID as MSG_ID,
    T7.LOOKUP_VALUE as SERVICE_NAMESPACE,
    T4.LOOKUP_VALUE as OPERATION_ACCESSED,
    T0base.RESPONSE_TIME as SERVICE_PROVIDER_TIME,
    T5.LOOKUP_VALUE as SERVICE_END_POINT,
    T0base.SLA_TIME as SLA_TIME,
    T0base.SUCCESS as SUCCESS,
    T8.LOOKUP_VALUE as PERSPECTIVE,
    T0base.CREATION_DTM as CREATION_DTM,
    T3.LOOKUP_VALUE as SERVICE_TYPE,
    T0base.PROCESS_ID as PROCESS_ID,
    T0base.ACTOR as ACTOR,
    T6.LOOKUP_VALUE as CLIENT_END_POINT,
    T0base.SERVICE_PROVIDER_TIME_TYPE as SERVICE_PROVIDER_TIME_TYPE,
    T0base.PARENT_PROCESS_ID as PARENT_PROCESS_ID,
    T0base.ORIG_SYS_NO as ORIG_SYS_NO,
    T0base.DEST_SYS_NO as DEST_SYS_NO,
    T0base.SERVICE_TYPE_NO as SERVICE_TYPE_NO,
    T0base.OPERATION_NO as OPERATION_ACCESSED_NO,
    T0base.SERVICE_END_POINT_NO as SERVICE_END_POINT_NO,
    T0base.CLIENT_END_POINT_NO as CLIENT_END_POINT_NO,
    T0base.NAMESPACE_NO as NAMESPACE_NO,
    T0base.SERVICEBUSPROCESSID as SERVICEBUSPROCESSID,
    T0base.TRANSACTIONID as TRANSACTIONID,
    T0base.PARTNERMACHINE as PARTNERMACHINE,
    T0base.PARTNERCONNECTIVITYTIME as PARTNERCONNECTIVITYTIME,
    T0base.DATASIZE as DATASIZE
from t_vz_service_utilization T0base
  left join t_vz_lookup T1
    on (T0base.ORIG_SYS_NO = T1.SNO )
  left join t_vz_lookup T2
    on (T0base.DEST_SYS_NO = T2.SNO )
  left join t_vz_lookup T3
    on (T0base.SERVICE_TYPE_NO = T3.SNO )
  left join t_vz_lookup T4
    on (T0base.OPERATION_NO = T4.SNO )
  left join t_vz_lookup T5
    on (T0base.SERVICE_END_POINT_NO = T5.SNO )
  left join t_vz_lookup T6
    on (T0base.CLIENT_END_POINT_NO = T6.SNO )
  left join t_vz_lookup T7
    on (T0base.NAMESPACE_NO = T7.SNO )
  left join t_vz_lookup T8
    on (T0base.PERSPECTIVE_NO = T8.SNO )
)

Now, I'm querying for the distinct values from this view

SELECT /*+ FULL(su1) PARALLEL(a, 8) */
               DISTINCT su1.orig_sys AS orig_sys,
                        su1.dest_sys AS dest_sys,
                        su1.operation_accessed AS operation,
                        su1.service_namespace AS namespace
          FROM service_utilization su1;

This query operation takes forever to run. Also, I'm assuming that the indexes on the underlying tables doesn't work when querying on the views. I'm looking for other alternative to speed up my query performance when dealing with views having 500+ million records.

Note:- Materialized view is not an option for me.

Edit:-

Indexes on table t_vz_service_utilization are SNO,MSG_ID,ORIG_SYS_NO, compound index(ORIG_SYS_NO, DEST_SYS_NO, OPERATION_NO, NAMESPACE_NO)

Indexe on table t_vz_lookup is SNO

Explain Plan:-

select /*+ full(su) no_index(su) parallel(su 8) */
  distinct ORIG_SYS_NO, DEST_SYS_NO, OPERATION_NO, NAMESPACE_NO
from
  t_vz_service_utilization su
;

There. Now you can use the lookup table on this, something like this.

select /*+ no_merge(T0base) leading(T0base) */
  T1.LOOKUP_VALUE as ORIG_SYS,
  T2.LOOKUP_VALUE as DEST_SYS,
  T4.LOOKUP_VALUE as OPERATION_ACCESSED,
  T7.LOOKUP_VALUE as SERVICE_NAMESPACE
from
  (select /*+ full(su) no_index(su) parallel(su 8) */
      distinct ORIG_SYS_NO, DEST_SYS_NO, OPERATION_NO, NAMESPACE_NO
    from
      t_vz_service_utilization su) T0base
  left join t_vz_lookup T1
    on (T0base.ORIG_SYS_NO = T1.SNO )
  left join t_vz_lookup T2
    on (T0base.DEST_SYS_NO = T2.SNO )
  left join t_vz_lookup T4
    on (T0base.OPERATION_NO = T4.SNO )
  left join t_vz_lookup T7
    on (T0base.NAMESPACE_NO = T7.SNO )
;

Of course this could be far from optimal, but you gave us nothing else than the query. We do not know your indexes (which columns, b-tree/bitmap?), constraints (FK/UK/PK), table structure (width, row migration/chained rows), init parameters (star transformation enabled?), table/index statistics, not even a plan...


SQL Server Transactional Replication over Internet I have 2 instances with SQL Server 2008R2. I would like to build Transactional replication between those servers. One is in the Local Network (Local_Instance) and the second server is over Internet (Internet_Instance). The Internet Server is running an FTP service. I have read something that I can use replication using FTP but I don't have any idea how to make this. I have built the publication but I need help to configure the suscription. The distribution database is in the Local Network.

You have probably read that you are able to deliver the initial snapshot via FTP.  This is covered in Deliver a Snapshot through FTP.

It is possible to replicate two SQL Servers across a VPN.  This is covered in Publish Data over the Internet Using VPN.

Once the VPN is setup, you should be able to connect and authenticate as though the servers are on a LAN or WAN.

If you have anymore questions please let me know.  I hope this helps.


Table constraint, number of row taken by a fk key Is it posible to add a table constraint, to prevent an insert if there are already 3 row of the same fk key

My table: ProjectTitles (id(pk), project_id(fk), title_id(fk))

Check if project_id has less then 3 rows, if true insert if not show error

One way would be to add a column (say project_title_no) that allows only 3 possible values and a unique constraint on (project_id, project_title_no):

ALTER TABLE ProjectTitles
  ADD COLUMN project_title_no smallint not null ;

ALTER TABLE ProjectTitles
  ADD CHECK (project_title_no IN (1,2,3)) ;

ALTER TABLE ProjectTitles
  ADD UNIQUE (project_id, project_title_no) ;

One drawback is that this column is not automatically filled and you have to supply the value, either in your application or by creating suitable insert/update procedures (that check first if for example, for some project there are rows with title_no 1 and 3, and fill the missing 2).

It requires 2 extra bytes* which seems a lot for only 3 values but it's easily modifiable (the requirements change to 10 titles per project? Easy, just change the CHECK constraint.)

There are no table locking required though or race conditions that come with a trigger solution.


Creating index on TIMESTAMP column to use with range operators TLDR: Can I create an index that's used by the following WHERE clause:

WHERE foo_date <@ tsrange('2018-01-01', '2018-02-01')

Let's say I have a table like this:

CREATE TABLE foo
(
    foo_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    foo_date timestamp without time zone NOT NULL,
    CONSTRAINT foo_pkey PRIMARY KEY (foo_id)
);

This table contains 100,000 records with dates from 2009-01-01 to 2018-12-29. I'd like to be able to query for rows in a given date range (e.g. for rows in January 2018).

Option 1

One approach is to use the BETWEEN operator:

SELECT * FROM foo WHERE foo_date BETWEEN '2018-01-01' AND '2018-01-31';

The problem of this approach is that if foo_date occurred on 2018-01-31 after midnight, they wouldn't be included in this query. So I could change the query to BETWEEN '2018-01-01' AND '2018-02-01'. The problem then, however, is records that occur on 2018-02-01 00:00:00. These would be included, which I don't want.

Option 2

Another option, put forth by Aaron Bertrand, is to use this construct:

foo_date >= '2018-01-01' AND foo_date < '2018-02-01'

(Yes, this blog is for SQL Server, but seems to be applicable here).

While this form unequivocally gives me the results I want, it's cumbersome: I have to repeat the column name twice.

Option 3

Since Postgres gives us the range data type, I thought a clearer form might be:

foo_date <@ tsrange('2018-01-01', '2018-02-01')

So my next question is, if I use this form, can I use an index to speed up operation?

With Options 1 and 2 above, a normal b-tree index can be used:

CREATE INDEX idx_foo ON foo(foo_date);

A query using Options 1 or 2 will use the index:

EXPLAIN SELECT * FROM foo
WHERE
    foo_date >= '2018-01-01'
    AND foo_date < '2018-02-01';

gives me this query plan:

Bitmap Heap Scan on foo  (cost=21.95..592.70 rows=942 width=12)
  Recheck Cond: ((foo_date >= '2018-01-01 00:00:00'::timestamp without time zone) AND (foo_date < '2018-02-01 00:00:00'::timestamp without time zone))
  ->  Bitmap Index Scan on idx_foo  (cost=0.00..21.71 rows=942 width=0)
        Index Cond: ((foo_date >= '2018-01-01 00:00:00'::timestamp without time zone) AND (foo_date < '2018-02-01 00:00:00'::timestamp without time zone))

However, if I use Option 3, the index isn't used:

EXPLAIN SELECT * FROM foo
WHERE foo_date <@ tsrange('2018-01-01', '2018-02-01');

gives me:

Seq Scan on foo  (cost=0.00..1791.00 rows=500 width=12)
  Filter: (foo_date <@ '["2018-01-01 00:00:00","2018-02-01 00:00:00")'::tsrange)

If I try to create a gist index, I initially get an error message.

ERROR:  data type timestamp without time zone has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
SQL state: 42704

After adding the btree_gist extension, I can create the index:

CREATE INDEX idx_foo ON foo USING gist (foo_date)

However, using the @> or <@ still doesn't make use of the index.

Is there something I'm missing? Or is it just not feasible to create an index that can be used by this construct?

The name "foo_date" indicates a date and is a bad choice for a timestamp column. Option 1 would work just fine with actual dates.
A plain btree index in combination with Option 2 is the unequivocally best solution. Look no further. Except maybe for the special case of a BRIN index for large tables with physically sorted data. See:

Speed up creation of Postgres partial index

Proof of concept

That said, to make a GiST or SP-GiST index work, you could create an expression index on fake ranges. You do not need the module btree_gist for this. Demonstrating with SP-GiST since that is typically a bit faster here. See:

Perform this hours of operation query in PostgreSQL

CREATE INDEX foo_date_spgist_idx ON foo USING spgist(tsrange(foo_date, foo_date, '[]'));

SELECT * FROM foo
WHERE  tsrange(foo_date, foo_date, '[]') <@ tsrange('2018-01-01', '2018-02-01')

Or with range literal:

...
WHERE  tsrange(foo_date, foo_date, '[]') <@ '[2018-01-01,2018-02-01)'

But: bigger, more expensive to maintain, slower than the btree index. Not even less cumbersome to write. Pointless for your case.

Aside: technically, you could:

... WHERE foo_date BETWEEN '2018-01-01' AND '2018-01-31 23:59.999999';

The Postgres timestamp type is (currently) implemented with µs resolution, i.e. max. 6 fractional digits. Hence the expression does exactly what you want. But I strongly advice against building on this implementation detail. Option 2 is the way to go. Related:

Interval (days) in PostgreSQL with two parameters
Ignoring time zones altogether in Rails and PostgreSQL


Cannot Check Cluster Index Sql Server I am just check if my table has Cluster Index or not by running this query

Table Design

CREATE TABLE [dbo].[postTB](
[postID] [bigint] IDENTITY(1,1) NOT NULL,
[slug] [nvarchar](300) NULL,
[title] [varchar](150) NOT NULL,
[postContent] [nvarchar](max) NULL,
[createDate] [datetime] NULL,
[publishDate] [datetime] NULL,
[userID] [bigint] NOT NULL,
[categoryID] [bigint] NOT NULL,
[isShow] [bit] NOT NULL,
[isApproved] [bit] NULL,
[websiteID] [int] NULL,
[miniPostContent] [nvarchar](300) NULL,
[modifedDate] [datetime] NULL,
[pageTitle] [nvarchar](300) NULL,
[pageKeyword] [nvarchar](300) NULL,
[pageDescribtion] [nvarchar](300) NULL,
[CombinedTags] [nvarchar](300) NULL,
[blogImage] [nvarchar](50) NULL,
[postBlogType] [nvarchar](50) NULL,
[postTag] [nvarchar](100) NULL,
[firstname] [nvarchar](50) NULL,
[lastname] [nvarchar](50) NULL,
[contactemail] [nvarchar](50) NULL,
[viewcount] [bigint] NULL CONSTRAINT [DF_postTB_viewcount_1]  DEFAULT ((1)),
[blogCategoryTypeID] [int] NULL CONSTRAINT [DF_postTB_blogCategoryTypeID]  DEFAULT ((0)),
CONSTRAINT [PK_postTB] PRIMARY KEY CLUSTERED
(
[postID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I am executing this

  Execute sp_helpindex [postTB]

But the result is

  The object 'postTB' does not exist in database 'cwpros' or is invalid for this operation.

Please help how to properly create cluster if not exist then check using above command?

Your script is correct, and if you get the error

  The object 'postTB' does not exist in database 'cwpros' or is invalid
  for this operation.

this means some of the following:

you created your table in other db than cwpros
someone dropped your table
your default schema is not dbo and you have anothe object with the
same name in your schema, but it's not a table

So first of all make sure that the object does exist in your database:

select *
from sys.objects
where name = 'postTB'


Why is the schema prefix (dbo) mandatory when we call a function? When user is mapped with default schema (dbo) and we can select all tables under [dbo] without prefixing the schema.

We can execute stored procedures without prefix if it's under the default schema.

Given that, why do we need to prefix a function with the schema?

Thanks!

Then why we can call function without prefix (schema), created under dbo?

From Books Online Document on UDF's

  Scalar-valued functions can be invoked where scalar expressions are used. This includes computed columns and CHECK constraint definitions. Scalar-valued functions can also be executed by using the EXECUTE statement. Scalar-valued functions must be invoked by using at least the two-part name of the function.

So this is basically a restriction set by SQL Server development team and I consider it quite correct. Even if it is somehow allowed (just for sake of conversation) I would still use Schema prefix.

I always support using schema name even if it would work without adding it. This is best practice and all "Good" developers use it no matter how much redundant it is.

The other reason what I see is, Database engine needs something to differentiate between system function like getdate () and user defined functions. If you are allowed to call function without schema name how would database engine differentiate between User created function named Getdate or system GETDATE() function.


After Upgrade to 2016, SQL Server Agent can't connect to Instance On a clustered Windows 2012 R2 server with SQL Server 2014 Enterprise.
Just upgraded an instance from 2014 SP1 CU4 to 2016 RTM and now getting this error when attempting to start the SQL Server Agent.
SQL SERVER AGENT LOG
2016-06-06 11:53:58 - ? [100] Microsoft SQLServerAgent version 13.0.1601.5 (X64 unicode retail build) : Process ID 10884
2016-06-06 11:53:58 - ? [495] The SQL Server Agent startup service account is DOMAIN\USERNAME.
2016-06-06 11:54:28 - ! [150] SQL Server does not accept the connection (error: 65535). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2016-06-06 11:54:28 - ! [000] Unable to connect to server 'SERVERNAME\INSTANCENAME'; SQLServerAgent cannot start
2016-06-06 11:54:33 - ! [298] SQLServer Error: 65535, SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQLSTATE 08001]
2016-06-06 11:54:33 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2016-06-06 11:54:33 - ! [298] SQLServer Error: 65535, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
2016-06-06 11:54:33 - ! [382] Logon to server 'SERVERNAME\INSTANCENAME' failed (DisableAgentXPs)
2016-06-06 11:54:33 - ? [098] SQLServerAgent terminated (normally)

Windows Application Log
SQLServerAgent could not be started (reason: Unable to connect to server 'A08SQL-EDI\EDI'; SQLServerAgent cannot start).

The agent starts and runs for about 30 seconds then dies with the above error. Anyone run into this issue? And do you know how to resolve it?

Problem resolved by adding an alias to the instance in the SQL Server Agent Configuration.

The alias was defined using the fully qualified domain name of the server. Example:servername.domain.biz\instance_name otherwise the agent will still error out and shutdown.

Also, the Agent was disabled in sp_configure

sp_configure 'Agent XPs', 1
go

reconfigure with override
go

So reenabled it.


How can you close or flush a binlog file on a remote server I am replicating my binlog files to a secondary server using the mysqlbinlog --stop-never --read-from-remote-server flags.  If the primary database server is compromised I want to replay the binlogs on the remote server, but I'm having trouble with the "current" binlog.

When using mysqlbinlog to convert it to SQL I always get an error like:

ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 2682, event
_type: 2
ERROR: Could not read entry at offset 4426095: Error in log format or read error

I'm assuming that this issue is related to the fact that this binlog file hasn't been closed or flushed on the main database server.  I do not have this problem on the file after running flush logs on the main database server.

So, my question is this:  If the main database server is compromised and I'm unable to run flush logs, only having access to the binlog files that were replicated to a remote server.  How can I clean up,  close or flush that binlog that was open at the time of a DB outage event?

Flushing the log on the master solves your problem pretty much by accident.  When the master log file rotates, mysqlbinlog closes the current file and opens the next one, which implicitly flushes any buffered data.

You can't trigger mysqlbinlog to flush its log on demand the way you can an actual server, but I've found a way to trigger it to maintain the file in an always flushed state, which seems to be a viable way to improve your results.

When Oracle added the --raw option to mysqlbinlog, which, in conjunction with --read-from-remote-server and --stop-never can be used to capture a real-time backup of the master's binlogs, they overlooked at least a couple of seemingly-obvious things.

In light of the documented purpose of the feature...

  The --raw option can be used to make a backup of a server's binary log. With the --stop-never option, the backup is “live” because mysqlbinlog stays connected to the server.

  https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_raw

...it seems like a distinct oversight that mysqlbinlog does not bother to flush its output stream after each write.

Maybe that's why they put “live” in quotes. :)

My testing confirms that mysqlbinlog, as shipped, leaves dangling data in its buffer that could have been flushed, but this seems like a fairly straightforward problem to fix.

For example, this little change in mysql-5.6.27/client/mysqlbinlog.cc at line 2477:

2477c2477
<         if (my_fwrite(result_file, net->read_pos + 1 , len - 1, MYF(MY_NABP)))
---
>         if (my_fwrite(result_file, net->read_pos + 1 , len - 1, MYF(MY_NABP)) || fflush(result_file))

This change results in the size of the file created by mysqlbinlog continually matching the size returned by SHOW MASTER STATUS;, rather than always seeming to be a few bytes behind.  I've always known of this slight lag, but never looked into it in depth.  Of course, continually flushing the buffer is going to come with a cost, but this seems like a non-negotiable necessity for "backup" purposes.  Note that this is only a flush of the user space buffers, it's not an fsync() or similar, so I don't think it's going to be that different in performance terms... and in any event, it's not going to impact the master.

The logical || is used because my_fwrite() returns 0 on success, so we short-circuit and don't try to flush if that fails, and we error out if either operation fails, since fflush() also returns 0 on success.

This change should significantly improve the behavior you see.

Of course, it's still always possible for you to end up with an incomplete log, since it's still an open file and still being written to... but when testing this fix in a low traffic environment, hot-copying the file results in a usable binlog for me -- while, otherwise, in the same environment, the file end is almost always incomplete when using the stock binary.

For different versions (of 5.6+ only -- this feature wasn't implemented at all before 5.6) the line in the source may be different but it should still be pretty easy to spot, and not far from the line numbers indicated here.

Facebook's fork of MySQL 5.6 features a similar but more sophisticated fix, and a new option --flush-result-file that causes the flushing to be a little less aggressive, but configurable.  However, I found it unnecessarily difficult to compile in its current form, so I developed the little patch above.

As long as you're patching mysqlbinlog, you might find another small tweak useful for solving another design oversight.  The mysqlbinlog utility in the official distribution lacks support for the --compress command line option (although Percona had the good sense to add it to theirs.)

But, it's also easy enough to enable compression.  I just came up with the fix above for this question, but I've been using the fix, below, for quite some time now, since -- again -- it has always seemed to me like an obviously missing feature.

From the 5.6.27 source, it looks like this:

1948c1948
<   if (!mysql_real_connect(mysql, host, user, pass, 0, port, sock, 0))
---
>   if (!mysql_real_connect(mysql, host, user, pass, 0, port, sock, CLIENT_COMPRESS))

This change doesn't enable support for passing the --compress command line option -- it just turns on the client compression protocol on the connection to the master, for any master that supports it (which they essentially all do), thereby reducing the amount of network bandwidth needed to transport the logs -- sometimes by a factor of 10:1 or higher, depending on the specific payload.  It does not change the contents of the generated files -- it just enables compression of the data traversing the network.  On the wire, this has the same effect from the master's perspective as enabling slave_compressed_protocol on the slave server.  (The documentation is ambiguous, but this setting has no effect on the master unless the master is also a slave of another server.)


What is the best way to shut down a 2 node availability group This fall we will be powering down the whole data center to replace a defective power module. There is no DR site to move anything to, everything last bit of the company resides in one DC at one location.
What is the best way to shut down a 2 node availability group? Should I remove the databases from the AG then power off the servers? When bringing the servers online after the power work is done. Should I have the share witness server up first, then the primary server and second server last? Then re-add the databases to the AG? We run Windows 2008 R2, and SQL Server Enterprise 2012 core edition in a VMware environment.

No need to touch the AGs.  You should have full set of database backups on a USB external drive or on Azure Blob Storage before you shut down the datacenter.
Just shut down the secondary node first, then the primary.  Bring them back up in the reverse order.  Boot up your domain controller VMs first, then any other supporting infrastructure, like the VM hosting the FSW.  When you bring the VM hosting the primary replica online it should establish quorum and come online.


How to access anonymous user account in MySQL? I have followed the MySQL on Ubuntu 18.04 installation tutorial on Digital Ocean, and during the process of running mysql_secure_installation noticed the following:

  By default, a MySQL installation has an anonymous user,
  allowing anyone to log into MySQL without having to have
  a user account created for them. This is intended only for
  testing, and to make the installation go a bit smoother.
  You should remove them before moving into a production
  environment.

Question is, how do I access this anonymous user account ?  I've installed the database server simply for course. The machine is on LAN, there's no critical data on it, so I understand in production removing anonymous user is desirable but on personal machine for  learning it'd be nice just have one simple account to practice SELECTs and JOINS.

Have them login as test with no password.

To check the existence of such logins, you connect as root into the commandline mysql tool (or some other tool) and run

SHOW GRANTS FOR 'test'@'localhost';
SHOW GRANTS FOR 'test'@'%';

The first will tell you if users can get in from the same machine.  The second will say whether you can get in from arbitrary other machines (assuming no network or firewall limitations).


Compatibility levels and reported engine versions I'm seeking a bit of clarification around SQL Azure versions and upgrades.
Upon trying to use some specific functionality it came to my attention that the compatibility level of the database was 100 (I needed 130). Upon looking a bit further I see that the engine version is reported as 12.0.2000.8 (equivalent to SQL Server 2014), but I need to be at a minimum engine version of 13.x in order to use compatibility level 130.
What do I need to do to upgrade the engine version for this SQL Azure instance? The article Hot patching SQL Server Engine in Azure SQL Database specifically states:

Azure SQL Database is evergreen, meaning that it always has the latest version of the SQL Engine

Additionally, answers like this one imply that the engine will always be v.latest but the versions reported will be incorrect.
Does this mean that I can ignore the reported engine version and go ahead with setting the compatibility level, or are there other steps I need to take to upgrade the engine?

Yes, you can ignore the reported engine version in Azure SQL Database.  This note was added to the @@VERSION function in March of 2018, so I guess it's not gonna change anytime soon:

We are aware of an issue where the product version reported by @@VERSION is incorrect for Azure SQL Database. The version of the SQL Server database engine run by Azure SQL Database is always ahead of the on-premises version of SQL Server, and includes the latest security fixes. This means that the patch level is always on par with or ahead of the on-premises version of SQL Server, and that the latest features available in SQL Server are available in Azure SQL Database.
To programmatically determine the engine edition, use SELECT SERVERPROPERTY('EngineEdition'). This query will return '5' for Azure SQL Database and '8' for Azure SQL Managed Instance.
We will update the documentation once this issue is resolved.


Select when group by contains more than one row with the same value? I am trying to write a query that returns one row from table A whenever a column in joined table B contains multiple distinct values for a single matching row in table A. (A -> B is a 1 -> many relationship.) I built a SQL fiddle to demonstrate this in context: http://sqlfiddle.com/#!6/83952/1

In this fiddle, the design column of the perf_ticket_type table should be the same for every ticket_type that has the same perf_id, but I'm trying to select only the instances where it doesn't. So for perf_id 3, there is more than one unique design being returned with the query I'm using currently.

What I want as my result is the two columns of the performance table only for perf_id 3, based on the multiple values of design for that perf_id in the joined table.

I've been frustrated by understanding GROUP BY in the past, so I'm unsure if there's something different I could be doing here to get my desired result. At the moment, I think I can select what I have in the fiddle into a temp table and then do another select on that with a GROUP BY perf_id HAVING COUNT(*) > 1 to get what I want (as per select rows where column contains same data in more than one record), but that seems like it's an extra step.

Your idea is right but you need HAVING COUNT(DISTINCT design) > 1

Like this:

WITH multi_design_perfs AS
(SELECT b.perf_id
FROM perf_ticket_type b
GROUP BY b.perf_id
HAVING COUNT(DISTINCT b.design) > 1
)
SELECT m.perf_id,
    STUFF((select ', ' + CAST(b.design AS varchar(10))
     FROM perf_ticket_type b
     WHERE b.perf_id = m.perf_id
     ORDER BY b.design
     FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,2,'')
FROM multi_design_perfs m
;


Failure notification with multiple profiles In my SQL Server Agent having several jobs which are running with different Proxies... and in Database mail having more than one profile [each profile having individual accounts]... now my question is , how can I send a mail using a specific profile from Database mail for failure notification...

Thanks
Kiran Kumar

SQL Server will use the default mail profile on alerts. On the otherside if you need to specify a special mailaccount or some other error handling, just create a step in your job which will be executed in the case of an error which uses sp_sendDbMail to send the alert.

If you just want to change the alert sender for ALL of your jobs, you can change it by right clicking your sql server agent, select properties, alert system and there you go. See here.


Update Top 1 Row Only I have a table where I need to update only 1 field per employee.  Below is sample ddl and my desired output, how should an update query be written in order to update only the Top 1 row?

Declare @Table1 Table (empName varchar(100), fieldtoupdate varchar(10))

Insert Into @Table1 (empName) VALUES ('Blue'), ('Blue'), ('Blue'),
('Blue'), ('Blue'), ('Blue'), ('Red'), ('Red'), ('Red'), ('Red'),
('Green'), ('Green'), ('Green')

Select * from @Table1

empName fieldtoupdate
Blue    Top 1
Blue
Blue
Blue
Blue
Blue
Red     Top 1
Red
Red
Red
Green   Top 1
Green
Green

I would use a CTE here to provide row numbers to your table data (partitioned by the empName). That way you can update the cte, which has the advantage of actually updating the base table.

No bear in mind that because you have no ordering column there is no guarantee of which row would actually get updated (you would want more restrictive sorting conditions around this), however for a base example you can use the following.

DECLARE @Table1 TABLE
    (
      empName VARCHAR(100) ,
      fieldtoupdate VARCHAR(10)
    );

INSERT  INTO @Table1
        ( empName )
VALUES  ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Red' ),
        ( 'Red' ),
        ( 'Red' ),
        ( 'Red' ),
        ( 'Green' ),
        ( 'Green' ),
        ( 'Green' );
WITH    cteTop1Table ( RowNum, empName, fieldtoupdate )
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY empName ORDER BY empName ) AS RowNum ,
                        empName ,
                        fieldtoupdate
               FROM     @Table1
             )
    UPDATE  cteTop1Table
    SET     fieldtoupdate = 'Top 1'
    WHERE   RowNum = 1;

SELECT  *
FROM    @Table1;

empName fieldtoupdate
Blue    Top 1
Blue    NULL
Blue    NULL
Blue    NULL
Blue    NULL
Blue    NULL
Red     Top 1
Red     NULL
Red     NULL
Red     NULL
Green   Top 1
Green   NULL
Green   NULL


Upgrading to SQL Server 2012 from SQL Server 2008 R2 I am planning to upgrade my SQL Server 2008 R2 Standard Edition to SQL Server 2012 Standard Edition.

I had been through many blogs on advantages of 2012 but I didn't find any performance comparison of both the versions.

Are there any performance variations between the two versions?

What impact (if any) will 2008R2 and 2012 have on ability to service larger loads of data?

Benchmarks aren't done as frequently as before, at least not publicly. This is true across the industry, not just SQL Server.

Internally, performance improvements are always pursued for each major release, though it may target some specific areas or something broader. One area that still gets somewhat regular updates are TPC-H benchmarks. Check out the TPC website and filter on SQL Server.

That said, you really should get your customer to reconsider their business preference on "upgrading" to SQL Server 2012. Mainstream support for 2012 SP3 ends in July 2017. See the Microsoft product support lifecycle search page for details.

That means they're investing in the upgrade for a product that is out of mainstream support within a year. The business might think it's ok, but make sure they understand the impact. Definitions of mainstream and extended support are documented in the Microsoft Business, Developer and Desktop Operating Systems Policy.

Fixes aside, some industries have specific requirements about only using products that are still in mainstream support so make sure your client is not affected there.

Try to get them on 2016 or at least 2014. You'd be doing your client a huge service.


Partitioning a table will boost the performance? I have a very large table approx 28GB and that is continually increasing.   I am thinking about partitioning my table.

The table is InnoDB and The File Per Table is enabled

I have a field name created which is an integer field and stores the timestamp so I was thinking about creating the table like this:

ALTER TABLE TABLE_NAME PARTITION BY RANGE (created)
(
PARTITION p0 VALUES LESS THAN (1325356200) ENGINE = InnoDB, # Data before 2012
PARTITION p1 VALUES LESS THAN (1333218600) ENGINE = InnoDB, # Data for JAN,FEB,MARCH YEAR 2012
PARTITION p2 VALUES LESS THAN (1341081000) ENGINE = InnoDB, # NEXT THREE MONTH DATA FOR YEAR 2012
PARTITION p3 VALUES LESS THAN (1349029800) ENGINE = InnoDB, # NEXT THREE MONTH DATA FOR YEAR 2012
PARTITION p4 VALUES LESS THAN (1356978600) ENGINE = InnoDB, # NEXT THREE MONTH DATA FOR YEAR 2012
PARTITION p5 VALUES LESS THAN MAXVALUE              # DATA for Next years
) ;

I have partitioned the year 2012 in Quarters(As it is our requirement) and will also do the same for year 2013 but not done here.

  Questions:

  Does partitioning boost INSERTs and SELECTs Performance
  How much time the ALTER take as it is a huge amount of Data(Any way to minimize the Alter time)?
  Do I need to use the field created in the queries so that the optimizer can use the partition?
  Are there any limitation's or risks by partioning this table?
  Is there any way to ALTER the table without downtime?

Does partitioning boost INSERTs and SELECTs Performance

It depends.  See the PARTITION section of http://mysql.rjweb.org/doc.php/ricksrots

  How much time the ALTER take as it is a huge amount of Data(Any way to minimize the Alter time)?

ALTER (usually) takes a time proportional to the data size.  Hour(s) for 28GB.  OTOH, ALTER PARTITION may be much faster.  DROP PARTITION is virtually instantaneous, hence useful for purging 'old' data.  Be more specific about your ALTER question.

  Do I need to use the field created in the queries so that the optimizer can use the partition?

The rules on indexes may seem screwball.  Let's see the CREATE TABLE and the SELECT(s).

  Are there any limitation's or risks by partioning this table?

No more than 1024 partitions, but 100 is 'too many', in my opinion.  The biggest risk is that you will do all the work to do PARTITIONing, only to get no performance boost.

  Is there any way to ALTER the table without downtime?

It depends -- be specific about the ALTER.


Postgres synchronized backup - what's with the archive? I am setting up a Postgres 9.1 master and a hot standby-server. I read the documentation but I'm not quite sure yet what I should do with my WAL files.

Every commit is done on both servers before returning to the application, therefore archiving the WALs onto some third system seams useless. But image my hot backup server fails, then I will have to manually reconfigure the master instance not use any standyby server. Is it wise to activate the WAL archive only then?

I presume not, because the newly created WALs will be useless for any newly set up hot standby. Should I always have WAL archive active and from time to time do a complete snapshot backup of the running system and then delete the old archived WALs manually by their timestamp? Then I could quickly set up a new hot standy instance if the current hot standby instance fails? Or is it better just to let a new hot standby server catch up per TCP connection because it does some high throughput bulk transfer of the database and only switches to replaying stream WALs when it is nearly up to date? This would be great, because then I wouldn't have to care about WAL archiving at all?

If writing to the database doesn't work for a few hours I don't care, only reads have to be highly available.

Edit: I'm also interested in some details on how a new hot standby would catch up with the master database. Is everything done in transactions or is there some high speed TCP catchup involved, which would be similar (in runtime complexity) to just copying the disks.

Wal archive is useful for backup purposes. For example - you make full backup every day, and keep wal segments since last abckup, thanks to this you can always get database to any state that it was during the day (for example - just before someone did run: drop table users cascade;).


DB backups not secheduled I have done backup plan to backup 1 DB . When  look in the log i can see that the system has taken backup of all databases (master , reportserver ...etc) . However on the drive where data are backed up , i can see only my DB for which i have done the bakup plan .

Questions:

1) why i am seeing in the log that there were many DB backed up .

2) If 1) is true where are these backups ?

Any comment will help.

The problem was that i was doing a classic  backup using windows server utility  (nothing to have with DB backup) for some folder . I was doing backup for only 1 folder in the root of the C:\ drive  .

But somehow when this backup starts it freezes the I/O on each database, then it resumes the I/O of all databases and finally all DB are backed up .


What normal form does a surrogate key violate? I have following question:

"What normal form does a surrogate key violate?"

My thought was the 3rd normal form, but I'm not quite sure it's just an assumption i am making. Could someone explain that to me?

Arguably, it doesn't.

Adding a surrogate key is an implementation decision (to respect how the RDBMS works) taken at implementation time. During modelling and normalisation, you should end up with BCNF (slightly stricter and more correct 3NF) without surrogate keys

That is, introducing surrogate keys at the start of the design process is wrong. Even though we all do it...


Export Materialized View from postgresql as a table? I have a materialized view I created in Postgresql. I was wondering is there a way to pg_dump the materialized view as if they were tables, so when I import them into another database it sees it as tables rather than a view created from other tables?

I don't think there is a single-step way to do this.  Are you trying to do this with one MV, or with a whole database full of them?

To get the proper CREATE TABLE statement with all the column types, you could do this:

CREATE TABLE dummy1 (like mv1 including indexes);

And then use pg_dump -t dummy1 > mv1.sql to get the CREATE TABLE statement.

To get the data, you would do:

\copy (select * from mv1) to mv1.txt

You would then have to stitch the two files together with a name change from 'dummy1' to 'mv1' in the first file, to get the definition and the data to be combined in one replayable file.


MSSQL Logon Error in /var/opt/mssql/log/errorlog Hello I checked in /var/opt/mssql/log/errorlog. then I get an error message like this:

The login packet used to open the connection is structurally invalid; the connection has bee
2021-01-22 16: 47: 38.21 Logon Error: 17832, Severity: 20, State: 2

this happens every day.
I've done a search on the web search but I can't find the answer.
I only get the answer for Windows users. here is the link URL.
Hopefully I can find the answer here. thanks

The login packet used to open the connection is structurally invalid; the connection has

SQL Server listens on a TCP/IP port and expects clients who connect to use the TDS network protocol.  If a client connects and sends data that doesn't conform to the TDS protocol, you'll get an error like this.  Could be a port scanner (possibly malicious), or some client trying to connect to SQL Server using FTP, Telnet, HTTP or some other TCP/IP-based network protocol that SQL Server doesn't use.


mysql xtrabackup missing binary logs It seems xtrabackup is not backing up binary logs, although it creates xtrabackup_binlog* with apparently correct information inside.
Should the binary logs be copied manually beforehand or is this a bug?

When using xtrabackup the binary logs aren't copied per file (but in one binlogfile).
You need to restore this differently, see docs: Preparing a Full Backup with innobackupex.

Or, with more extensive explanations:

Percona documentation - Prepare with xtrabackup
Percona documentation - Restore with xtrabackup

In relation to your database not starting after restore, you might want to paste your my.cnf and server log hostname.err.


I am receiving the following error - The definition of object x has changed since it was compiled One of my clients have been receiving this error that is causing issues in their process. Basically, they are thinking the error is related to replication of stored procedures that they assume I messed up. I had to drop some stored procedures that was part of replication and in order to do that I had to remove them from replication and I did generate a new snapshot for synchronizing with the subscriber and it was from then on they saw this error popping up in one of their stored procedures which happened during the same day, but then later on Sunday it happened again for other stored procedures. Now, my question is this related to replication or there is something else to it. Client already mentioned that they did not make any changes at their end.

Modifying my answer based on the comment by @sepupic. Thank you for pointing out my mistake.

This error is generated if stored procedure definition changes while it is running.


Filter condition not correctly applied to Clustered Columnstore index Using the below example, the predicates are the same, however the top statement (correctly) returns 0 rows, the bottom statement returns 1 - even though the predicates do NOT match:

declare @barcode nchar(22)=N'RECB012ZUKI449M1VBJZ'
declare @tableId int = null
declare @total decimal(10, 2) = 5.17

SELECT 1
FROM
    [dbo].[transaction] WITH (INDEX([IX_Transaction_TransactionID_PaymentStatus_DeviceID_DateTime_All]))
WHERE
    Barcode = @barcode
    AND StatusID = 1
    AND TableID = @tableID
    AND @total <= Total

SELECT 1
FROM
    [dbo].[transaction]
WHERE
    Barcode = @barcode
    AND StatusID = 1
    AND TableID = @tableID
    AND @total <= Total

Why could this be happening?

Further info:

The Non Clustered Index in the top statement is NOT filtered
CheckDB returns 0 issues
Server Version: Microsoft SQL Azure (RTM) - 12.0.2000.8   Dec 19 2018 08:43:17   Copyright (C) 2018 Microsoft Corporation

Paste the Plan link:

https://www.brentozar.com/pastetheplan/?id=S1w_rU68E

Further Info:

Have ran dbcc checktable ([transaction]) with all_errormsgs, extended_logical_checks, data_purity which indicates no issues.

I can reliably reproduce the issue against this table when restoring a backup of this database.

This is a bug with SQL Server. If a column is deleted from a table with a clustered columnstore index, and then a new column is added with the same name, it appears to be using the old, deleted column for the predicate. Here is the MVCE:

This script starts off with 10000 rows with statusId of 1 and statusId2 of 5 - then drops the statusID column and renames statusId2 to statusId. So at the end all rows should have a statusId of 5.

But the following query  hits the non clustered index...

select *
from example
where statusId = 1
    and total <= @filter
    and barcode = @barcode
    and id2 = @id2

... and returns 2 rows (with the selected statusId different from implied by the WHERE clause)...

+-------+---------+------+-------+----------+
|  id   | barcode | id2  | total | statusId |
+-------+---------+------+-------+----------+
|     5 |    5    | NULL |  5.00 |        5 |
| 10005 |    5    | NULL |  5.00 |        5 |
+-------+---------+------+-------+----------+

... whereas this one accesses the columnstore and correctly returns 0

select count(*)
from example
where statusId = 1

MVCE

/*Create table with clustered columnstore and non clustered rowstore*/
CREATE TABLE example
(
id        INT IDENTITY(1, 1),
barcode   CHAR(22),
id2       INT,
total     DECIMAL(10,2),
statusId  TINYINT,
statusId2 TINYINT,
INDEX cci_example CLUSTERED COLUMNSTORE,
INDEX ix_example (barcode, total)
);

/* Insert 10000 rows all with (statusId,statusId2) = (1,5) */
INSERT example
       (barcode,
        id2,
        total,
        statusId,
        statusId2)
SELECT TOP (10000) barcode = row_number() OVER (ORDER BY @@spid),
                   id2 = NULL,
                   total = row_number() OVER (ORDER BY @@spid),
                   statusId = 1,
                   statusId2 = 5
FROM   sys.all_columns c1, sys.all_columns c2;

ALTER TABLE example
  DROP COLUMN statusid
/* Now have 10000 rows with statusId2 = 5 */

EXEC sys.sp_rename
  @objname = N'dbo.example.statusId2',
  @newname = 'statusId',
  @objtype = 'COLUMN';
/* Now have 10000 rows with StatusID = 5 */

INSERT example
       (barcode,
        id2,
        total,
        statusId)
SELECT TOP (10000) barcode = row_number() OVER (ORDER BY @@spid),
                   id2 = NULL,
                   total = row_number() OVER (ORDER BY @@spid),
                   statusId = 5
FROM   sys.all_columns c1, sys.all_columns c2;
/* Now have 20000 rows with StatusID = 5 */

DECLARE @filter  DECIMAL = 5,
        @barcode CHAR(22) = '5',
        @id2     INT = NULL;

/*This returns 2 rows from the NCI*/
SELECT *
FROM   example WITH (INDEX = ix_example)
WHERE  statusId = 1
       AND total <= @filter
       AND barcode = @barcode
       AND id2 = @id2;

/*This counts 0 rows from the Columnstore*/
SELECT COUNT(*)
FROM   example
WHERE  statusId = 1;

I have also raised an issue on Azure feedback portal:

And for anyone else who encounters this, rebuilding the Clustered Columnstore Index fixes the problem:

alter index cci_example on example rebuild

Rebuilding the CCI only fixes any existing data. If new records are added, the issue arises again on these records; so currently the only known fix for the table is to recreate it entirely.


ORA-01830 Error for 11.2.0 client but not for 10.2 client I have this piece of code:

BEGIN
   DBMS_SCHEDULER.DROP_JOB (
   job_name => 'LOANSBUILD.LOANSNEWYORKCLOSE');
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name           =>  '***.LOANSNEWYORKCLOSE',
    job_type           =>  'PLSQL_BLOCK',
    job_action         =>  'begin loans_schedule_job.loans_close(TRUNC(SYSDATE), ''N''); end;',
    start_date         =>  '15-NOV-08 12.00.00.000000000 AM AMERICA/NEW_YORK',
    repeat_interval    =>    'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=16;BYMINUTE=0;BYSECOND=0',
    auto_drop          =>   FALSE,
    job_class          =>  'DEFAULT_JOB_CLASS',
    enabled             =>   TRUE,
    comments           =>  'Test.'
);
END;
/
exit;

When the above is executed from an environment which has oracle sql client 10.2 installed , this goes fine but when the same is executed fron an environment that has 11.2 client installed, this fails as below:

BEGIN
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 2

The variable nls_date_format is set to 'DD-MON-RR' in the 10.2 environment and set to 'YYYY-MM-DD HH24:MI:SS' in the 11.2 env.

As this was getting compiled for 10.2 env, I updated nls_lang_date in the 11.2 env as well to make it 'DD-MON-RR' but even after that I get the same error. Is there anything else I should be setting.

Please note that I am sysadmin and as this code is getting compiled on of the server, my job is to ensure that it does on others as well. Which also means that I do not have permissions to update code.

Is possible that for the 10g client you either have a the date format set in a local login.sql file or set in the $ORACLE_HOME/sqlplus/admin/glogin.sql. If you can find this date formatting, then you should set it the same for your 11g client and it should work.

Essentially, when you put in a date string Oracle look at the nls_date_format to see how a date is formatted by default. If that is different than how you have it in a sting, Oracle might have an issue handling the date. you can also use the to_Date funtion to force a particular date format to work.