3NF Normalization Problem Employing an Algorithm I'm getting stucked in figuring out how to obtain the third normal form (3NF) using an algorithm ( removing attributes and then functional dependencies ).

The relation R( A,B,C,D,E,F,G,H,I,J,K ) has the following dependencies:

A → BEFG,
ACDI → H,
CD → J,
CDF → K,
B → G,
CF → ABE,
AC → D,
ABD → C,
EG → B

And the result is:

R1(B#, G)
R2(E#, G#, B)
R3(A#, B, E, F)
R4(C#, D#, J)
R5(A#, C#, I#, H)
R6(A, C, D, F, K) with primary key one of: AD or AC or CF.

When I tried to resolve, I came up with:

R1(B#, G)
R2(E#, G#, B)
R3(A#, B, E, F)
R4(C#, D#, J)
R5(A#, C#, I#, H)

and

R6(A#, C#, D )
R7(A#, D#, C )
R8(C#, F#, A, K )

With is similar but separates the last one.

Why is that the answer?
( I took it from the book ).

You have not specified the algorithm used for the decomposition, and actually both the decompositions (yours and that of the book) have relations that include others, which is something that normally is not done, since is a form of redundancy.

For instance, having R6(A C D), and R7(A C D) (remember that the order of attributes in a relation schema is not relevant) means that you have two relations with exactly the same attributes (and exactly the same functional dependencies projected from the original ones, AD -> C and AC -> D), so that they have both the same candidate keys (AD and AC), and chosing one of them as primary key in a table and the other one in the other is meaningless.

A similar problem is in the schema R2(E G B), where the projected functional dependencies are EG -> B and B -> G, with candidate keys EB and EG, and it is not clear the advantage of having a separate table R1 with only the attributes B and G, and with the key B.

In fact a frequently used algorithm, presented in almost all books on databases, is the so-called “synthesis” algorithm, that avoids this kind of redundancy, while maintaining both the properties of dependency preservation and lossless-join. This algorithm, applied in this case, produces the following decomposition:

R1(A B E F), with candidate key A

R2(A D H I), with candidate key A D I

R3(A C D), with candidate keys AC and AD

R4(C D J), with candidate key CD

R5(A C F K), with candidate key CF

R6(B E G), with candidate keys EG and EB

Basically this algorithm groups all the dependencies with the same left part, produces a relation for each group with all the attributes of the functional dependencies of the group (and the left part is a candidate key). Then all relation schemas included in others are eliminated (to avoid the kind of redundancy discussed previously), and finally, if no relation schema in the decomposition contains one of the original candidate key, adds a new scheme with one (any one) of the original candidate keys. As said above, this algorithms is guaranted to produce a dependency preserving and lossless-join decomposition.


Curious Database building Problem (ER Diagram) I am trying to build an EER Model for a Autostore that has 5 locations and offers a range of auto products. They offer car repairs and roadworthy tests as a service also.

I need to be able to make fortnightly reports on unfinished service jobs, and fortnightly reports on the sales. They have a wide customer database filled with full addresses.

There is a constant inflow of new stock items and restocking of old ones. There should also be a way to know the cost of each item in stock and where its being held.

I swear I've researched it enough to be able to understand it by now but Im really struggling to map this out as I'm constantly running into a wall when dealing with the products that are being restocked, sold and stocked by particular stores in different locations.

I'm a total rookie with this kind of thing but if anyone can help me it would be amazing.

This appears to be a fairly standard multi-site case.  Model the data as if there is only one location.  This will get the basic model down correctly.  Then add a location column to the tables where location is important.  Options you may want to use are:

Simple foreign key.  (Transactions for incoming/outgoing stock.)
Multi occurrence foreign key. (Transfer records.)
Primary key column.  (Inventory records.)
Splitting tables into core data and location specific data (Customers, product details.)

  I have the AUTOSHOP "stocks" PRODUCT, AUTOSHOP "has" CUSTOMER, CUSTOMER  "purchase" SERVICE, CUSTOMER "purchase" SALE, SALE "uses" PRODUCT, SUPPLIER "supplies" STOCK plus all the necessary attributes for each one. is that correct

I would remove AUTOSHOP entirely, you should attributes that are appropriate for LOCATION.  Depending on how you are modelling PRODUCT, it may be fine.  The AUTOSHOP stock PRODUCT relationship, likely should be an INVENTORY table.  SALES (Invoices) usually have line items which may be either SERVICES or PRODUCT.  I find it is better to model in the general and specialize as needed.  The EER model is intended to make specialization simpler.

From a business perspective, I see three related subsystems: Sales, Inventory, and Purchasing.  These three systems will all share Product, and Location data.  In a single site system Location would not be required, or at least would not need to relate to the other tables.


What is the "Chaos" Isolation level and when should it be used? ADO.NET documentation shows the possibility of setting the transaction level for a SQL transaction to Chaos. It sounds unpleasant, but if the feature is there, presumably it has some legitimate use.

The SET TRANSACTION ISOLATION LEVEL command in BOL (ah! see, I can use google and BOL) nothing seems to be named "chaos" and ADO.NET does have 5 modes that match up nicely to documented levels in addition to "chaos"

What or who is this Chaos level for? (And why does it have an unfriendly name?)

Refs:
The ADO.NET enum

You can't use it against SQL Server.

It was in DTS:

Chaos Isolation Level In SQL Server - SQL Server Code,Tips and Tricks, Performance Tuning

And is in SSIS:

DtsContainer.IsolationLevel Property - MSDN

More references, originally added by Nick Chammas in a comment:

Rollback is not supported
SQL Server and Oracle don't support the chaos isolation level


Storing meta about a cropped photo Want to know how to save cropped images in the database? The info about the images or reference to the cropped images in itself?

Working on a marketplace for artists where artists can upload their work. The way the app is designed, most artists asked us to provide them with a functionality to crop their images so they look good on their profiles

I propose two solutions and wanted to know if they are feasible:

When a user has cropped the photo, save that cropped photo in your storage and store the cropped photo's path in the table
I was exploring the internet and I found a library which allows the user to crop the image and when he/she's done, the library returns a meta object about the image, it's co-ordinates, width height etc.

I'd like to know your thoughts, the first one consumes memory, the second one causes rendering issue since I'll have to show the image based on the co-ordinates

Let's order things by complexity and performance.

The cropped image will be displayed many times.  And cropping is a rather complex process.  So, store the cropped image as a file.  Note:  By storing as a file, you can simply use an <img src=...> tag in HTML to fetch it -- no extra processing (for you) when providing the web page.
Given that you are doing #1, the rest becomes somewhat moot.  Yes, you could store the original image somewhere, plus meta info about cropping.  But will you ever change the cropping?  Or maybe the only real use case is when the user resends the image and you redo the cropping?  My point is, you business model now takes over and answers what to do for #2; not database considerations.


Connecting to a MySQL database on Mac from a VMWare fusion Virtual Machine I have MariaDB (5.5.34-MariaDB) installed on a Mac (OS 10.8.5) I can connect to the database fine locally (127.0.0.1) on port 3306. But when I try to connect to the database from windows 7 64bit running in VMWare fusion I get an error message.

I am using 32bit ODBC, as I believe that is what is required. I do have Little Snitch running on the mac side, but I have checked this and it is not blocking the connection. Windows side does have the firewall enabled, but I have added a rule to allow outgoing connections on port 3306. I have tried telnet from windows on port 3306 to the Mac and it also fails. Pinging the Mac from windows works fine.

Checking my.cnf, there does not seem to be anything that would block the connection. No bind-address = 127.0.0.1 in the file. I have added permissions for the user to connect from the IP using the GRANT SQL command. I am at a loss on how to get this working so any ideas would be appreciated

Finally figured this out. I used home-brew to install MariaDB and as part of the setup it was using launchctl to start the server. The file ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist had --bind=127.0.0.1 in it.


Database design for logging varied types of data I am currently in the process of moving our logging system from file-based to database-based, and I'm trying to come up with a suitable schema to fit. I'd like my application to be logged extensively, with dozens of possible event types (and some with sub-types) that I'd like logged. Each event I log will have different data logged with it, depending on the event type.

My end goal here is to be able to present the data on a website, and perhaps run some statistics on it later easier than traditional log files would allow. For context, my application is a gameserver and mostly involve logging ingame player actions.

So far, I've thought of a couple of approaches to doing this:

I could create tables for each event I want to log. This would be
fine if I were only logging a few events, but I  would rather not
have dozens of tables - one for logging each specific thing. The
advantage of this is that I don't have to worry about storing the
data associated with each event - each table would have specific
fields for the data I'm storing. I also imagine presenting this data
would be a challenge - probably involving JOINs across many tables.
A reference table of event_types and event_subtypes, and a
generic, single logs table for storing the actual log data. My concern
here is: how do I store the data associated with each event? This
data is different depending on what event is being logged. I thought
of storing this data as JSON inside a generic 'data' field, but that
would severely limit my ability to query the data later, so I'm out
of ideas. Another option is the EAV model which is new to me, but I
hear a lot of criticism of it so I'm wary.

A couple of examples of events I'll be logging, to further illustrate the context here:

A player sends a chat message. I'd like to store the player's account
ID and the message text.
An admin uses an admin action on the player. I'd like to store the
admin action type, the player's account ID, the admin's account
ID, and the reason.

My question is: what would be the best approach here given my requirements? I'm leaning towards the second option, but would value any input regarding alternative approaches or something I haven't considered.

I've thought of a way to do it which isn't ideal but it should work.

Rather than naming the fields in relation to what they store (in terms of the log data) you could simply name them LOG_ARGUEMENT_1, LOG_ARGUEMENT_2, LOG_ARGUEMENT_N ...
And have a permanent field for ACCOUNT_ID which is a foreign key to an ACCOUNT table containing information about each user (such as if their an admin or not and when they last completed and action) Also have an EVENT / EVENT_SUB_TYPE table as you suggested linked to the main LOG table. Finally you could also have a field with the full log on a separate table linked to the log table for error checking.


Retrieve one of two possible values from 1 table in INFORMIX I have a table which for the same key, it will always have one record with value 'P' but sometimes that same key could have two records one with value 'P' and one with value 'L'; I need to create a VIEW that if both 'P' and'L' exist; it will return the record with 'L', ELSE, it will return record with value 'P';

ex1 has two records 'P' and 'L' -> only 'L' should be returned
ex2 has one record 'P' -> 'P' will be returned.

This answer assumes that your table Ex1 has columns:

key_col — the key column.
flag_col — the flag column, containing L or P (with a check constraint).
data_col — a proxy for the unidentified set of columns containing the other data.
Primary key on key_col and flag_col.

A view like this should work (I didn't say it would work fast):

CREATE VIEW Relevant_Ex1(key_col, flag_col, data_col) AS
    SELECT A.Key_Col, A.Flag_Col, A.Data_Col
      FROM Ex1 AS A
      JOIN (SELECT B.Key_Col, MIN(Flag_Col) AS Flag_Col
              FROM Ex1 AS B
             GROUP BY B.Key_Col
           ) AS C
        ON A.Key_Col = C.Key_Col AND A.Flag_Col = C.Flag_Col;

This uses the fact that the flag column contains either L or P and L sorts before P.  So, if there are two rows for a given key, the value L will be selected, else the value P will be selected.


use a string array without CREATE privileges I have a set of tables where I need to determine if a particular set of about 1000 row IDs are present in one specific field in each table.
select 'Accounts', row_id Acct_id, pr_postn_id
from schema.s_org_ext
where pr_postn_id in ('1-ABCD','1-BCDE')
UNION
select 'Orders', row_id Order_id, pr_postn_id
from schema.s_orders
where pr_postn_id in ('1-ABCD','1-BCDE')

The above works well for two row_ids, but copy/pasting the IN clause for 1000 IDs is unwieldy when I need to update the list, especially as the list appears in each IN clause on ten UNION-ed queries.
I am looking for an array or a string variable to use in the IN clause and refer to that variable or array in the subsequent queries, in the form UNION SELECT ... WHERE id IN  some kind of huge list of VARCHAR2 IDs .
Normally I would just create a table but in this system I'm a business analyst, not a dba. CREATE privileges won't be happening for me. If I do try create anything in the database, I get
[Error] Compilation (2: 1): ORA-01031: insufficient privileges

I've tried to use a DECLARE block with cursor, but with no success (no  privilege violations, just faulty SQL code)
DECLARE
    TYPE posrec_t IS RECORD (
    pos_row_id VARCHAR2(15) );
    TYPE poslist IS TABLE OF posrec;
    todaypos poslist :=
      poslist (
      posrec ('1-103KO-18'),posrec ('1-10TOV-1'));
    posrec posrec_t;

CURSOR MYCURSOR IS select 'Opportunity' Data_Object, row_id Data_object_Id, pr_postn_id
from siebel.s_oppor
WHERE PR_POSTN_ID IN posrec
UNION
[etc]
BEGIN
OPEN MYCURSOR;
END;

With the DECLARE BEGIN END block syntax, it seems like I could create a short-lived temporary table of 1000 IDs that appear once as a definition statement (and without needing CREATE rights), but I cannot figure out how to make it work.

I would use the following approach:

Create a string array type:

create or replace type string_array as varray(1000) of varchar2(10);
/

Use that in a with clause to construct the list of values as a virtual table, and use that table in your query, like this:

with t as (
  select column_value as code from table(string_array('1-ABCD','1-BCDE',...))
)
select 'Accounts', row_id Acct_id, pr_postn_id
from schema.s_org_ext
where pr_postn_id in (select code from t)
UNION
select 'Orders', row_id Order_id, pr_postn_id
from schema.s_orders
where pr_postn_id in (select code from t)
UNION
...


SSRS - : How to make a conditional formatting for matrix by group? I currently have a report produces a matrix, displayed per user group.  (1 user group per page, with 35 user groups/pages)
I want to highlight the row in pink in this condition:
=IIf(Fields!CountFailures.Value>=1,"Pink","White")

However, it is not showing any colour.  Please help! (I am using PowerBI Report Builder... should be same as SSRS)

If you are formatting cells based on group totals and not a row value, you need to use an aggregate and indicate the correct row group scope.  In this sample, "UserName" is the name of the row group scope on the Row Groups section of the Grouping pane
=IIf(Sum(Fields!CountFailures.Value, "UserName") >=1, "Pink", "White")


Tablespace of an index organized table I want to find the tablespace of an index organized table with primary key.

With dba_segments I can only find the tablespace for the primary key.

But how can I find the tablespace for the table?

Index organized tables (IOTs) don't actually have a normal heap table-type segment as their main storage - that's pretty much the point of IOTs. The data is stored in a B-Tree structure, essentially like a normal Oracle index.

So you won't see any segments in dba_segments for the table itself. What you will see are (index) segments for the primary key, and possibly overflow segments (this is used when you want to store parts of the row outside the B-Tree structure).

In dba_tables, you'll see one entry for your table with an IOT_TYPE of IOT (and pretty much everything else null or defaulted), and, if you have an overflow segment, another entry with IOT_TYPE of IOT_OVERFLOW and IOT_NAME the name of your table. (The table name for the overflow segment is system-generated, something like SYS_IOT_OVER_XYZ.)

See also:

Using Index-Organized Tables for Performance
Managing Index-Organized Tables


Why did the data type of my DB change? I had PostgreSQL installation in an EC2 instance. I wanted to migrate to RDS and thus used the DMS service. However, I later came to realize that the data type of the columns have been changed. The changes in the datatype can be seen in the images attached.

The issue related to JSONB columns is a known limitation of DMS. In particular, if you're using DMS to migrate from your EC2 instance to RDS Postgres, it will convert the type to varchar(765) and truncate any JSONB column longer than 756 chars.

https://forums.aws.amazon.com/message.jspa?messageID=735961

We heard during AWS Summit - San Francisco that the DMS team is planning a fix for this issue. For an update, please feel free to comment on the message itself.


How can I get the actual SSAS error when processing a cube from a SQL Agent job? I have a SASS cube on one server and a SSIS package on another server that triggers processing the cube. The SSIS package is executed via a scheduled SQL Agent job. I have enabled error reporting for the SSIS package via an OnError event handler that sends an email message with the MessageSource property set to @[System::ErrorDescription], but this has two problems:

The first problem is that I receive 4 emails for every failure, containing:

"Internal error: The operation terminated unsuccessfully."
"Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation."
"Errors in the OLAP storage engine: An error occurred while processing the 'XXX Fact' partition of the 'XXX Fact' measure group for the 'XXX' cube from the XXX database."
"Server: The current operation was cancelled because another operation in the transaction failed."

Only the third of these is useful to me, so is it possible to prevent the others from being sent?

The second problem is that even the third error message is only barely useful - if I process the affected cube manually on the SSAS server, I get the real error:

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_XXXFact', Column: 'XXXId', Value: 'YYY'. The attribute is 'XXX Id'.

and:

Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute XXX Id of Dimension: Dim XXX from Database: XXX, Cube: XXX, Measure Group: XXX Fact, Partition: XXX Fact, Record: YYY.

How can I have the OnError handler in my SSIS package retrieve these detailed error messages and send them via email instead of the vague "An error occurred" messages it currently sends?

It's been a while since I did this and I don't have a test setup, but to get a single event you should probably use the OnTaskFailed event instead of the  OnError.

That won't get you the error you see when processing the cube manually but should at least give you a single mail.

See this article on SSIS error handling.

  OnTaskFailed

  This event is generated only once when a Task "failure", independently
  of failure occurrence be in the Package context or just in a Task
  specific.


Mysql (win) Is it possible to get databases from multiple drives in the same time? My question would be, is there any way to read the mysql data folder from different drives in the same time (Windows) ?
For example:
I have 1TB database from the drive C:, and another 1TB from the drive D:.
So when I start mysql it will simply see both databases from both drives.

A given instance of MySQL can only use one datadir. Under the datadir, each MySQL schema lives in a subdirectory.
For example, the default datadir is C:\Program Files\MySQL\MySQL Server 8.0\data.
Schema1 would be under C:\Program Files\MySQL\MySQL Server 8.0\data\Schema1
Schema2 would be under C:\Program Files\MySQL\MySQL Server 8.0\data\Schema2
But if you could trick Windows into thinking that Schema2 subfolder actually uses storage from your physical drive D:, then you could achieve what you want.
One method is to use mklink as shown here: https://dev.mysql.com/doc/refman/8.0/en/windows-symbolic-links.html I would prefer this solution because it allows you to target a specific path on your D: drive.
A second option is to use virtual drives. Windows allows you to map drives to specific paths of another drive. Not many Windows users employ this option, they prefer to use drives as drive letters instead.
https://winaero.com/create-virtual-drive-from-a-folder-in-windows-10/
So you would use
SUBST D: C:\Program Files\MySQL\MySQL Server 8.0\data\Schema2

Read the article I linked to for more details.
Whichever solution you use, now files under Schema2 are automatically stored on your physical drive D:, and do not take up space on the physical drive C:.
What if you have already created the Schema2? So there is already a subdirectory for that schema's tables on C:. Well, you'll have to do some file juggling to make that work. I don't have a Windows machine so I can't test it or give advice on what the steps are.
Remember not to do any manual moving of MySQL tables around while the MySQL Server service is running. Stop the service first, or you will corrupt your data. Practice this sort of configuration on another server, not your important production data, and also make a backup of the whole database instance before you try anything.


Mysql - Rows not updated in transactions without any errors I have a stored procedure that updates a user's balance and inserts a transaction log. I don't know why, but sometimes it doesn't update the users table.

Stored Procedure Code

DELIMITER $$
USE `db_name`$$
DROP PROCEDURE IF EXISTS `update_balance`$$
CREATE DEFINER=`username`@`%` PROCEDURE `update_balance`
    (IN balancechange INT,
     IN userid INT,
     IN tableName VARCHAR(100),
     IN `type` INT,
     IN description  VARCHAR(1000) CHARSET utf8,
     IN trans_id VARCHAR(150))
BEGIN
START TRANSACTION;
SET @tmp = (SELECT id FROM users WHERE id = userid FOR UPDATE);
UPDATE users SET gold = @gold := ((@oldbalance:=gold) + balancechange)
    WHERE id = userid AND (gold + balancechange) >= 0;
SET @countRow =  ROW_COUNT();
IF (@countRow > 0) THEN
    SET @t1 =CONCAT("INSERT INTO ", tableName,
                    " (user_id, type, balance_change, old_balance, new_balance, description, trans_id)
                    values ('", userid, "', '", `type`, "', '", balancechange, "', '", @oldbalance, "', '", @gold, "', '", description, "', '", trans_id, "')");
    PREPARE stmt3 FROM @t1;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;
    SELECT @oldbalance AS oldbalance , @gold AS gold,
           balancechange, 0 AS result;
ELSEIF (balancechange = 0) THEN
    SET @oldbalance = (SELECT gold FROM users WHERE id = userid LIMIT 1);
    SET @t1 =CONCAT("INSERT INTO ", tableName,
                    " (user_id, type, balance_change, old_balance, new_balance, description, trans_id)
                    values ('", userid, "', '", `type`, "', '", 0, "', '", @oldbalance, "', '", @oldbalance, "', '", description, "', '", trans_id, "')");
    PREPARE stmt3 FROM @t1;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;
    SELECT @oldbalance AS oldbalance , @oldbalance AS gold,
           0 AS balancechange, 0 AS result;
ELSE
    SET @oldbalance = (SELECT gold FROM users WHERE id = userid LIMIT 1);
    SELECT @oldbalance AS oldbalance , @oldbalance AS gold,
           0 AS balancechange, -1 AS result;
END IF;
COMMIT;
END$$

DELIMITER ;

Usage

CALL update_balance(1000, 123, 'user_transaction201806', 1, '{$desc}', '123456789')

The users table is InnoDB, the user_transaction201806 is MyISAM.

Sometime the data for a user isn't updated in the users table, but the data for a user is still added in the relevant transaction table (e.g. user_transaction201806) with correct data.

Because the table containing the user's transactions is in myISAM format, the transaction cannot be rolled back.

MyISAM ignores all transaction semantics.  Convert to InnoDB !

But maybe that does not answer the Question.  The one MyISAM table seems to be merely an audit log.

Further head scratching....

Please provide the transaction log for the bad case.


SQL Server - creating a historical data backup I've run out of space on my SQL Server 2008 Express DB.

i'm creating a "duplicate" SQL Server 2008 Express DB so i can backup my data before i purge my DB so i can start over.  Is there a way in SQL Server to view the DDL text file, so i can easily recreate the empty table before i push data into it?

Also, is there as "easy" way to bulk push data from one table into another once i've created the backup structure?

at first i tried deleting some old rows, but of course that just increases the log file.

I've run out of space on my SQL Server 2008 Express DB.

Best is to script out the schema using SSMS. Then

Take a full backup of your database --or--
bcp out the data from the tables. (note: the script will bcp out in native format. Change it to bcp out data in human readable format - by removing -n)


Cannot Utilize Maximum CPU and Memory Usage for MySQL Good day.

I know this may be a duplicate of other questions however I have applied all the suggestions in many of the threads, but I remain with the same problem.

I have a single stored procedure working with max 3 tables. when I run the procedure, only 30% of my CPU is used and about 25% of RAM.

I am sitting with a CPU with 4 cores and 16GB RAM.

my.ini looks as follows:

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 512M
max_allowed_packet = 32M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size= 32M
thread_concurrency = 0

log-bin=mysql-bin

binlog_format=mixed

server-id   = 1

innodb_buffer_pool_size = 12G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_read_io_threads = 64
innodb_write_io_threads = 64

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Is it the nature of the procedure called which is causing mysql to under utiize the hardware or is it my configuration?

I was running XAMPP but then realised it was 32-bit so I switched to the 64-bit version of WAMP. I use a 32-bit MySQLWorkbench to run queries.

I am using the InnoDB engine.

Using MySQL Ver 14.14 Distrib 5.5.24 Win64 (x86).

The authors of High Performance MySQL describe part of your problem fairly succinctly (p. 234):

  MySQL can’t execute a single query in parallel on many CPUs. This is a feature offered
  by some other database servers, but not MySQL.

Running a single stored procedure will use no more than 1 of your CPU cores for executing queries, with a little extra on other cores being used by various background and worker threads, such as those performing I/O. MySQL will only really use the full power of a multi-core or multi-processor system when there are multiple queries running at once.

If you're seeing 30% of 1 core being used, that indicates a potential problem, but 1.2 cores out of 4 for a single query is quite normal.


Any drawbacks of using GIN PostgreSQL index for an integer foreign key? I have a large table (600 millions rows) with a foreign key other_id of type integer. A single value of the foreign key is repeated about 100 times on average. I need to have an index on that FK column because the data is frequently selected by other_id.

My tests show that the gin index type is about 10 times smaller and about 3 times as performant as the default btree index (the performance was tested using SELECT queries).

The question is are there any real-world drawbacks of using the gin index instead of the btree index? It looks like this index type isn't used much for a very common case like mine, i.e. an integer foreign key. But my tests show vast performance gains. Why then gin isn't recommended for such scenarios?

I had to execute CREATE EXTENSION btree_gin to be able to use the gin index for the integer column.

I know about the UPDATE being possibly slow due to FASTUPDATE being enabled by default: Occasional/intermittent, slow (10+-second) UPDATE queries on PostgreSQL table with GIN index

I care only about the equality = operator being able to use the index (also, IN (...) with a possibly large number of values but I assume this is also equality).

This seems like a rather esoteric thing, which is probably why it doesn't get recommended all that much.  I do recommend it when the issue comes up, which just isn't all that often.  The size of one index is unlikely to be all that meaningful in the context of an entire database, so making it smaller usually isn't worth worrying about a great deal.

I don't think there are any downsides which are specific to this being a foreign key. The automatically-generated queries that are used to maintain the many-valued side of the constraint can use the GIN index just as well as the Btree index.

I'd turn "fastupdate" to off, unless you do relevant benchmarking which shows you want it on.  Since the index is over a scalar using "btree_gin", you don't get the explosion in index page writes for each inserted row, like you do with a normal GIN index, so the need for fastupdate is less.

Since this index type is much less used than regular btree indexes, there are more likely to be undiscovered bugs lurking in it (especially if fastpdate = on).  I wouldn't (and don't) let that put me off from using it if it really provided me with something of value, but it keeps me from just blindly using "btree_gin" everywhere I conceivably could.

One thing I've noticed is that the replay of updates/inserts to GIN indexes seem pretty slow (compared to what I naively expected), which could be relevant for PITR, recovery, or streaming replication.  If any of those things are important to you, make sure you include them in your test.


MySQL - Exclude a result if even one foreign key doesn't meet criteria (date availability checker) I have three tables, one that lists regions, one that lists hotels and another that lists reservations. The hotels table has a foreign key for the regions table, to show which hotel is within which region, and the reservation table has a foreign key for the hotels table, to list reservations made against a hotel.
I'm trying to make it so that you can input a date, and the hotel isn't returned in the results, if that date is unavailable (though it's also possible to NOT input a date).
Currently, using the query listed at the bottom of the page, it lists out all the hotels even if they don't have a reservation against them. At this time, only one hotel has any reservations, and at that, there are two reservations in total. Before I included the 'DISTINCT' clause, to make it so that duplicate entries aren't listed, it would list out all hotels, including the one with reservations, except this one it would list TWICE as there were TWO reservations. Now, with the DISTINCT clause, it will only ever show one result, but it will ALWAYS show one result. It essentially says "This entry is in the time range specified, so we can't show that, but the other one isn't, so we will show it", even though it's the same hotel.
For reference, my query currently, using 2020-09-02 as the date to check availability for;
SELECT DISTINCT hotels.hotel_name FROM hotels

LEFT JOIN reservations ON hotels.id=reservations.hotel_id

LEFT JOIN regions ON hotels.region_id=regions.id WHERE Cast('2020-09-02' as DateTime) NOT BETWEEN reservations.start_date AND reservations.end_date OR reservations.start_date is NULL OR reservations.end_date is NULL;

Current results (Where date IS available);
Hotel 1 with two reservations
Hotel 2
Hotel 3
Hotel 4
Hotel 5

Current results (Where date ISN'T available);
Hotel 1 with two bookings
Hotel 2
Hotel 3
Hotel 4
Hotel 5

Current results (Where date is available, but 'DISTINCT' ISN'T used);
Hotel 1 with two bookings
Hotel 1 with two bookings
Hotel 2
Hotel 3
Hotel 4
Hotel 5

Current results (Where date isn't available, but 'DISTINCT' ISN'T used);
Hotel 1 with two bookings
Hotel 2
Hotel 3
Hotel 4
Hotel 5

Expected results (If chosen date ISN'T available);
Hotel 2
Hotel 3
Hotel 4
Hotel 5

Expected results (If chosen date IS available);
Hotel 1 with two bookings
Hotel 2
Hotel 3
Hotel 4
Hotel 5

Hopefully the expected results scenarios give an idea of what I'm going for.
Any help is appreciated.
EDIT: Link to a DBFiddle of the issue here: https://www.db-fiddle.com/f/n9Ew6nLCWQHDjYfxzp49xB/16

You need a DISTINCT because of the n:m relationships
But the date selection i would write differently, so that all holtel.id should be excluded, which have a reservation on that day, so that it can't be choosen anymore
Schema (MySQL v5.7)
CREATE TABLE `reservations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` int(10) unsigned DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

insert  into `reservations`(`id`,`hotel_id`,`start_date`,`end_date`) values
    (1,1,'2020-08-26','2020-09-02'),
    (2,1,'2020-12-06','2020-12-13');

CREATE TABLE `regions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `region_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

insert  into `regions`(`id`,`region_name`) values
    (1,'Region 1'),
    (2,'Region 2'),
    (3,'Region 3'),
    (4,'Region 4'),
    (5,'Region 5');

CREATE TABLE `hotels` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `region_id` int(10) unsigned DEFAULT NULL,
  `hotel_name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

insert  into `hotels`(`id`,`region_id`,`hotel_name`) values
    (1,1,'Hotel 1'),
    (2,3,'Hotel 2'),
    (3,5,'Hotel 3'),
    (4,5,'Hotel 4'),
    (5,4,'Hotel 5');

Query #1
SELECT
    DISTINCT hotels.hotel_name, regions.region_name
FROM
    hotels
        LEFT JOIN
    reservations ON hotels.id = reservations.hotel_id
        LEFT JOIN
    regions ON hotels.region_id = regions.id
WHERE
(reservations.start_date is NULL OR reservations.end_date is NULL)

        OR hotels.id NOT IN
        (SELECT h.id
FROM hotels h
    INNER JOIN
    reservations r ON h.id = r.hotel_id
WHERE
   CAST('2020-09-02' AS DATE) BETWEEN r.start_date AND r.end_date)
;

hotel_name
region_name

Hotel 2
Region 3

Hotel 3
Region 5

Hotel 4
Region 5

Hotel 5
Region 4

View on DB Fiddle


migrating from a local PostgreSQL database to Amazon Aurora I have a local PostgreSQL database and I want to migrate it to Amazon Aurora, how do I do that? I found a documentation on how to migrate from RDS PostgreSQL database, however, mine is local and I couldn't find a documentation on how to migrate it.

As per @Pavel Tsiukhtsiayeu blog documentation Migrating PostgreSQL standalone instance to Aurora (Amazon RDS) The idea behind migration is simple: create Aurora cluster in RDS, setup database source and target endpoints at DMS, and launch database migration task. The data migration task will migrate all data from the source database, and replicate ongoing changes to the target endpoint. All you have to do is to point your web server to the new Aurora cluster, and hot reload web server once the source and target endpoints completely in sync.

For further your reference here


mongodb: replica-set with load balancer Is there an option for load balancing with mongodb replica sets?
I am not asking for a HAProxy or similar, but for a built-in feature from mongodb.
Could not find tips for this in the docs.

If you are using sharding, then the "load balancer" is the mongos process - actually it is more like a router - it keeps an in-memory copy of the config database and can make decisions based on the shard key.

If you mean load balancing across identical replica sets or across the members of the set, then there is a feature request to have mongos handle that scenario too (https://jira.mongodb.org/browse/SERVER-1594), however given how the drivers operate it is not really necessary (it would make the drivers less complex though).

In a single replica set, you cannot distribute writes, they all must go to the primary.  You can distribute reads to the secondaries already, via Read Preferences as you deem appropriate.  The driver keeps track of what is a primary and what is a secondary and routes queries appropriately.


Unable to backup after recovery model change Previously I had the recovery model as SIMPLE. I have altered it to FULL.

While trying to perform BACKUP LOG, I am getting an error saying

  BACKUP LOG cannot be performed because there is no current database backup.

How can I get the backup of transaction logs?

In SQL Server you have 3 forms of backup: Full, Differential and Log:

The Full backup is the most important because it has all the information of the database.
The differential is the diference of the last full backup until now.
The Log is the diference of the last full or differential backup until now.

So you can have just the Full Backup or Full and Diferrential or Full and Log or Full, Differential and Log backups. You choose the best combination for your need.

Also look for the recovery/nonrecovery option in the restore clause


Questions concerning performance/production resources where exporting / importing big data sets I have a MS SQL Server database that contains data for many customers and I am working on a feature to export a single customers data.

I have basically created a script that generates inserts for all the customer records to run on another database. This is working great in my test environment but I am concerned about what impact this will have on production.

We could of used a product like RedGate but we decided to integrate this logic within our app itself.

Export Action

No transaction required
Need to be run on live database but will be off hours.
Right now I have a stored procedure that returns the insert statements.

Import Action

Transaction required.
Need to be run on live database but will be off hours.
I run the returned insert statements on the new database.

Questions (mostly regarding the production environment)

The export script touches virtually every table in the system (with no lock), should I be concerned about this action? I have had suggestions to break up this action into pieces, to not create one giant execution. Would there be an advantage to this?
The import script will potentially be running millions of inserts. With similar concerns to the export, should I be adding "GO"s every X inserts? Is that enough? Should this be broken into multiple executions? Is this a scenario where I need to use SQL Bulk Copy?

Thank you very much for your help.

I hope i have understood everything good.

You have MSSQL server let's name it S1 from whitch you have to export data to other MSSQL server, let's name it S2.

The best solution depends from possibilites.

With Linked Server object.
If you have access over network to both of them, I would suggest using linked server to communicate over network and retrive data from S1 to S2.
https://www.google.pl/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=mssql%20linked%20server

Here is my script, may help.

DECLARE @OPENQUERY NVARCHAR(4000) ,
    @TSQL NVARCHAR(4000) ,
    @LinkedServer NVARCHAR(4000);

SET @LinkedServer = 'lnk_test';
SET @OPENQUERY = 'SELECT * FROM OPENQUERY(' + @LinkedServer + ',''';

----------------------------------------------------------------------------
-------------------------------- Retrieving Data ---------------------------
----------------------------------------------------------------------------
--##StatusMig - Table containg progres and times

IF OBJECT_ID('tempdb..##StatusMig') IS NOT NULL
    BEGIN
        DROP TABLE ##StatusMig;
    END;
CREATE TABLE ##StatusMig
    (
      LP INT ,
      NAME VARCHAR(MAX) ,
      DATA DATETIME
    );

INSERT  INTO ##StatusMig
        ( LP ,
          NAME ,
          DATA
        )
        SELECT  ISNULL(MAX(LP), 0) + 1 ,
                'Start' ,
                GETDATE()
        FROM    ##StatusMig;

-----------------------------------------------------------------
-------- TABLE_A
-----------------------------------------------------------------

IF OBJECT_ID('tempdb..##TABLE_A') IS NOT NULL
    BEGIN
        DROP TABLE ##TABLE_A;
    END;

CREATE TABLE ##TABLE_A ( col_1 INT, col_2 INT );

INSERT  INTO ##StatusMig
        ( LP ,
          NAME ,
          DATA
        )
        SELECT  ISNULL(MAX(LP), 0) + 1 ,
                'Start Start TABLE_A' ,
                GETDATE()
        FROM    ##StatusMig;

PRINT 'Start TABLE_A';

SET @TSQL = '
        select col_1, col2_2 from TABLE_A
          '')';

PRINT @OPENQUERY + @TSQL;

INSERT  INTO ##TABLE_A
        EXEC ( @OPENQUERY + @TSQL
            );

INSERT  INTO ##StatusMig
        ( LP ,
          NAME ,
          DATA
        )
        SELECT  ISNULL(MAX(LP), 0) + 1 ,
                'End TABLE_A' ,
                GETDATE()
        FROM    ##StatusMig;

No network access, possibility to use files.
Generating insert statments and execution them is last thing you want to do. Will work slow and it will consume alot of resources.

Instead of creating insert statments, you can create new database for example data_migration with customer data.

S1 - MSSQL server from you export data
S2 - MSSQL server to you export data

On S1 create job whitch backup data_migration table every day.
Send data_migraction backup file over ftp etc.
On S2 create job whitch checks ftp and if backup from today is avalaible restores it.
Run T-SQL with inserts.

Remember, work with sets not single values.
Sorry for spelling and grammar im after 36h work marathon.


Understanding data abstraction levels The three-level ANSI SPARC Database Architecture suggests three data abstraction levels, namely, external, conceptual, and internal levels.

If I understand correctly, the external level represents the users' views, the conceptual level is the conceptual diagram (ER model), and the internal level is the relational model.

My problem is that I found in the literature that besides those, there is a fourth level (the lowest one), which is the physical level.

I want to understand what is it concretely? Is it at this point that we define the type of index, access paths, and things related to physical access to data?

Although the definitions of the schemas or levels of representation of the ANSI/SPARC architecture did not reach the desired precision, they are only three, and they are named external, conceptual and internal, respectively.

As far as I know, there is no fourth level, because the physical structures employed by a database management system (DBMS for brevity) compose, in fact, the internal schema, as I will explain below.

Note: Even if imprecise, the ANSI/SPARC architecture is definitely a very useful reference when building a system, so it looks like the literature you found contains inaccurate information or perhaps it makes a reference to the (also very useful) notion where the layers of abstraction of a database are called conceptual, logical and physical, which has different implications.

Brief descriptions of the three ANSI/SPARC levels

I deem opportune to describe each level (ordered from the highest to the lowest degree of abstraction) so as to expose its scope:

The external level is the way in which the information is presented to end-users of a computerized information system; e.g., the specific organization of the pieces of information as displayed or requested in the screens or windows (via, e.g, lists, grids, labels, text boxes, check boxes, drop-down menus, etc.) of an application program.
The conceptual level involves the representation of the structure of the information as (a) perceived by the business experts and (b) modeled by the database analysts, commonly in terms of entity types, properties and associations, so it is comprised of ideas or conceptualizations, it is not “concrete” yet and its representation requires constructs that are in lower levels of abstraction. The conceptual level is informally referred to as the business rules of a certain organization regarding its informational requirements. As an side, schemas of this kind are usually —though not necessarily— expressed via graphical devices like, e.g., entity-relationship diagrams to facilitate communication between the interested parties.
The internal level has to do with how the DBMS of relevance stores the information in a concrete form; e.g., arrays, pages, extents, files, records, fields, pointers, etc. This is the lower level  of abstraction since, to put it some way, the DBMS “saves” the information in “real” structures, so this may also be conceived as the physical level of the architecture but naming it in this way does not imply that there is a fourth level. It is worth to mention that there can be different sublevels of abstraction within the internal level itself, but that factor depends on the particular mechanisms used by the DBMS in question.

Relational databases created on SQL DBMSs with respect to the ANSI/SPARC architecture

When a practitioner uses a SQL platform (Microsoft SQL Server, IBM DB2, Sybase ASE, PostgreSQL, etc.) to build a database using the mechanisms proposed by the relational model by Dr. E. F. Codd:

The base tables, columns, types (and domains when available) and constraints must reflect the characteristics of the conceptual schema (entity types, properties, associations, cardinalities) so that the data (in the shape of rows) remains consistent with it. The declarations made by means of a data sublanguage (e.g., SQL DDL) of the base tables, columns, types and constraints are instruments that formalize the conceptual schema, so they are considered logical constructs and, as such, they do not yet “tocuch” the physical level, they are still free from DBMS-specific concrete storage structures. Normalization, being a logical procedure, applies here, not at the conceptual schema.
The views (i.e., derived tables that “combine” columns from one or more base tables or from other derived tables as well, typically by virtue of SQL DML operations involving JOINs) maintain a close correspondence to the external level, because they gather and provide the information requested or sent by the screens or windows of one or more application programs, just like the end-users see it.
The indexes, pages, extents, files1, records2, fields3, partitions, allocation maps, and many other arrangements match the internal schema, since they are the “physical” structures utilized by a DBMS to store the information.

The SQL DBMS functions as a mediator (1) between the internal and the logical representations of the conceptual schemas, and (2) between the internal and external representations through connections via views.

Notes

1, 2, 3 Note how important it is to distinguish between (a) files, records and fields and (b) tables, rows and columns —or relations, attributes and tuples—, as they are elements that belong to distinct levels of abstraction and, hence, are susceptible of very different operations and treatments, having therefore very dissimilar impacts.


The full contents of the bind-variable are not displayed in SQL Plus I wrote a big query in bind-variable. I want to print it in SQL Plus, but the text doesn't show, only small part of it.

The length of a CLOB output is limited.
You can increase the limit with set long n, example:

set long 50000


Understanding the TempDB growth Need to understand below behavior of tempdb:-

Due to some poor running queries which caused Tempdb bloating, we had shrink and tried to adjust the sizes of each tempdb file:

Before issue:

no of TempDB data files 8

Size of each TempDB data file 100 GB

During issue:

  no of TempDB data files 8
  Size of each TempDB data file 200 GB

Due to above we had entire disk of 1.6TB filled out, hence we shrinked and adjusted the file to 120 GB each thus allocating 960 GB to temp drive from that 1.6 TB and disabling autogrowth

Later after couple of days we see below

  no of TempDB data files 8
  Size of each TempDB data file 50 GB

I am not able to get above why tempdb on its own went to 50 GB each file and how even though we though of fixing its initial size to 120 GB each ?

Adding some info: - NO restart happened or anyone manually reducing the file size further to 50 GB. Autogrowth was also disabled since the last shrink change.

Really, the only things that will cause your tempdb files to shrink are:

Someone used the 'SHRINKFILE` command to manually resize the files
The SQL Service was restarted (due to a reboot, or the service being restarted manually, etc) and the file reverted to it's configured "default size" (which can be found in sys.master_files - note that it's different from current / initial size reported in sys.database_files)

You can audit your system (using the default trace or the file growth reports in SSMS) to see when and why file shrink events happened.  You can find a history of reboots in the system_health XE session.

A side note about this:

  we shrinked and adjusted the file to 120 GB each thus allocating 960 GB to temp drive from that 1.6 TB and disabling autogrowth

Unless you are leaving room for tempdb's log file, you should really size the tempdb files to fill this dedicated drive.  Since you have autogrowth turned off, it's simply wasted space at this point =)


Sync database table schema and stored procedures We have 2 databases. One is at our server, the other is local. We need to sync them both in a manner that local database has all data and server database has no data only empty tables and stored procedures and functions.

In server database we are changing stored procedures, functions, as well as tables. These changes are to be synchronized into Local DB i.e.

Common at both databases:

table 1 (id, name, price)

Changes happened in Server DB:

table 1 (id, name, price, IsActive)

We want IsActive field to be synchronized into local db, same way we need to sync stored procedures and functions.

So in simple words we need a solution to sync stored procedures, functions, and table schema without messing up existing data.

Can any one suggest any tool or code with which we can satisfy the same? With some google and search we found a codeproject article but that can be used for fresh database. Still sharing the link.

Thanks

SQL Server Data Tools (SSDT) has the ability to do just this. It can compare a source and target database, determine the differences between them for many classes of object including tables and executables. It can produce a script to make the target look like the source. You get to choose which changes to include and which to ignore.

It is not magic, however. If you had new column and declare it as NOT NULL in the source, then try and apply that to a target table that contains data it will fail because no default is specified. Be sure to properly account for this and similar circumstances.


Is it safe to add a new node to a Cassandra cluster while a repair is running? I'm getting ready to expand an existing Cassandra cluster.  I have repairs scheduled to run on a reoccurring basis.  Do I need to disable repairs when adding a new node to a cluster, or can I bootstrap new nodes while repairs are running elsewhere in the cluster?

As per Datastax documentation,

  Warning: DataStax recommends stopping repair operations during topology changes; the Repair Service does this automatically. Repairs running during a topology change are likely to error when it involves moving ranges.

So, it is clear that topology changes (adding/removing nodes) are not recommended when there is a repair process alive in your cluster


How to select records with two columns matching values in one table in PostgreSQL? I have a table with the below structure and data:

create table employee (id int, name varchar, father_name varchar);
insert into employee values(1, 'John', 'Alex'),(2, 'Simi', 'Expatri'),(3,
'John', 'Alex'),(4, 'Hezad', 'Ambrose'),(5, 'John', 'Alex'), (6, 'Simi',
'Expatri'), (7, 'Hezad', 'Ambrose'), (8, 'John', 'Reman'), (9, 'Komal',
'Ambrose');

Now I want to fetch those records whose two columns name and father_name match each other.
The desired result would be as following:

id    |    name    |    father_name
1     |    John    |    Alex
3     |    John    |    Alex
5     |    John    |    Alex
2     |    Simi    |    Expatri
6     |    Simi    |    Expatri
4     |    Hezad   |    Ambrose
7     |    Hezad   |    Ambrose

Any help is appreciated in advance.

Ordering by name and father_name is the first step, but I assume you don't want records where no other matching records are found. This would work:

select e1.id, e1.name, e1.father_name
  from employee as e1
  inner join employee as e2
    on e1.name = e2.name
   and e1.father_name = e2.father_name
   and e1.id != e2.id
  group by e1.id, e1.name, e1.father_name
  order by e1.name, e1.father_name

Here is a working demo.


This actually turned out to be fairly painful. :) The details of this logic ran though my head most of last night... but in my current sleep-deprived state, I think I have devised a solution where every case is handled, and which does what we need in a reasonably-optimal fashion.

It returns the correct values for all of the test cases.

You need an index on (EventTime) so that the optimizer can turn the date-level tests into constants and use that index to find the matching rows by date range... then the server can filter the date-matched rows based on their time match in the CASE statement of the where clause.

In the WHERE clause, the CASE expression matches a condition in our data, and will yield a boolean value based on the truthiness of the expressions in the THEN.  This value will include a row if TRUE and exclude the row if FALSE.

I've used variables, but that's mostly for clarity... assuming there's any clarity to be found in what I've written, below, and to prevent errors since the literals are needed in so many different places.

SET @TargetDateTime = '2013-09-26 00:00:00';
SET @DateRange = 7;
SET @TimeRange = 15;

SELECT E.*
 FROM EventTable E
      -- must be at or after midnight the morning of the first valid day
WHERE EventTime >= DATE_SUB(DATE(@TargetDateTime), INTERVAL @DateRange DAY)
      -- must be before midnight on the morning after the first valid day
  AND EventTime <  DATE_ADD(DATE(@TargetDateTime), INTERVAL (@DateRange + 1) DAY)
      -- now, hold my beer and watch this...
  AND CASE
  -- easy first case, the time portions of both values are equal
  WHEN TIME(EventTime) = TIME(@TargetDateTime)
  -- in each case if the expression in the "THEN" evaluates to something true,
  -- then the row will be returned if the other conditions in WHERE are also met
  THEN TRUE
  -- next case, if the event appears to be earlier than the target,
  -- we match the row IF either...
  WHEN TIME(EventTime) < TIME(@TargetDateTime)
  -- ...the simple case, difference in seconds is within range
  THEN TIME_TO_SEC(@TargetDateTime) - TIME_TO_SEC(EventTime)
       BETWEEN 0 AND (@TimeRange * 60)
       OR
  -- ...the complicated case, when we span midnight
       86400 + TIME_TO_SEC(EventTime) - TIME_TO_SEC(@TargetDateTime) <= (@TimeRange * 60)
  -- next case, event appears later than target, we match if either...
  WHEN TIME(EventTime) > TIME(@TargetDateTime)
  -- ... simple subtraction returns a value in range
  THEN TIME_TO_SEC(EventTime) - TIME_TO_SEC(@TargetDateTime)
       BETWEEN 0 AND (@TimeRange * 60)
       OR
  -- ... we wrap around midnight
      86400 + TIME_TO_SEC(@TargetDateTime) - TIME_TO_SEC(EventTime) <= (@TimeRange * 60)
  -- ELSE isn't encountered unless we run into null values, in which case, no match
  ELSE FALSE END; /* END of CASE expression */

A working demo of the final version is here: http://sqlfiddle.com/#!2/7b8e5/2

It is usually bad form in a WHERE clause to use columns as arguments to functions, because this prevents the optimizer from using an available index, since it has to evaluate the data in every row of the table in order to evaluate the function.  There's no harm, here, though, since an index can't help us match the times.  This is because DATETIME values are on the left hand side of the field, as stored, and the time values are on the right, and an index stops helping as soon as we encounter irrelevant information from left to right.  (That's usually discussed in the context of multi-column indexes, but it also holds true, here.)

It's also often bad form in a WHERE clause to use a CASE expression because that too may prevent the optimizer from choosing the optimal path because the CASE can prevent the optimizer from forming a proper understanding of the logic, but this is acceptable here because we have nothing but AND in the WHERE clause and two other conditions that are easily satisfied by an index.

Update: This code and the answer went through several iterations before arriving at its current (working) form, as you can see in the comments.

Essentially, what we are doing is considering the three cases than can be identified when trying to compare wall-clock time of day: the times are equal, A is earlier than B, or B is earlier than A.  It seems like "absolute value" might come into play, but the problem we encounter is that when a time value has no date component, earlier and later aren't meaningful concepts when the times might span midnight.  Without a date component, 00:05:00 and 23:55:00 are 23 hours and 50 minutes apart.  With a date component, they could also be "n days and 10 minutes" apart.  When the date component is missing but uninteresting (or already handled, in this case), we first check the simple case, subtracting the "earlier" from the "later" to see if they're within range, but when they're not, we add 86400 seconds to the "earlier" component, essentially making it "the same time, the following day"... then the subtraction can be done to see if the times are close enough together when they're calculated as having spanned midnight.


Why is ORDER BY LIMIT 1 so much faster than MAX? Here are my two queries:

select sts_in
from sta_session
where sts_user_id=2006
AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6'
order by sts_in desc limit 1;

And

select max(sts_in)
from sta_session
where sts_user_id=2006
AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6';

For reference, the table looks like this:

CREATE TABLE `sta_session` (
    `sta_session_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `sts_sessid` varchar(255) NOT NULL COMMENT 'PHP session ID',
    `sts_user_id` int(10) unsigned NOT NULL,
    `sts_in` bigint(20) NOT NULL,
    `sts_out` bigint(20) NOT NULL,
    `sts_ip` varchar(255) NOT NULL,
    `sts_browser_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`sta_session_id`),
    KEY `sts_sessid` (`sts_sessid`),
    KEY `sts_user_id` (`sts_user_id`),
    KEY `sts_ip` (`sts_ip`),
    KEY `fk_sta_browser_id` (`sts_browser_id`),
    KEY `idx_last_login` (`sts_user_id`,`sts_in`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

If I run explain on both the queries, the first one (with order by) uses my special index that I created just for this query, idx_last_login, but the other one uses sts_user_id.

Can the MAX query not take advantage of idx_last_login? Why not?

I know it can use that index, because I can force it, but I suspect it's ignoring the sts_in part of it and just searching by user id.

The EXPLAINs, in JSON format for easy reading.

explain select sts_in from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6' order by sts_in desc limit 1;

[{
  "id": 1,
  "select_type": "SIMPLE",
  "table": "sta_session",
  "type": "ref",
  "possible_keys": "sts_sessid,sts_user_id,idx_last_login",
  "key": "idx_last_login",
  "key_len": "4",
  "ref": "const",
  "rows": 723106,
  "Extra": "Using where"
 }
]

explain select max(sts_in) from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6';

[{
  "id": 1,
  "select_type": "SIMPLE",
  "table": "sta_session",
  "type": "ref",
  "possible_keys": "sts_sessid,sts_user_id,idx_last_login",
  "key": "sts_user_id",
  "key_len": "4",
  "ref": "const",
  "rows": 723107,
  "Extra": "Using where"
 }
]

It makes a lot of sense to me.
The MySQL Query Optimizer looks over the WHERE, GROUP BY and ORDER BY clauses.
Look at the first query
select sts_in
from sta_session
where sts_user_id=2006
AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6'
order by sts_in desc limit 1;

Which index in sta_session has the most columns mentioned in the WHERE, GROUP BY, and ORDER BY clauses ? idx_last_login
How does MySQL locate the MAX value ?

Traverse sts_user_id in idx_last_login index for 2006
Go to the Last Index Entry for sts_in for sts_user_id 2006
Scroll backwards until sts_sessid!='0jitkt80gg3avere03tqk4lhi6'
Limit result to 1 row

Look at the second query
select max(sts_in)
from sta_session
where sts_user_id=2006
AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6';

Which index in sta_session has the most columns mentioned in the WHERE, GROUP BY, and ORDER BY clauses ? Not idx_last_login, but sts_user_id.
How does MySQL locate the MAX value ?

Full Index Range Scan sts_user_id for 2006
Aggregate sts_in from the table, comparing all values of sts_sessid <> '0jitkt80gg3avere03tqk4lhi6'

POSSIBLE WORKAROUND
Since sts_user_id and idx_last_login are duplicate indexes (because they have the same first column), you should run
ALTER TABLE sta_session DROP INDEX sts_user_id;

There is a possibility for idx_last_login to be chosen and aggregate sts_in. However, there is still some traversal across the table.
If you really want to get aggressive with indexing, create this one
ALTER TABLE sta_session ADD INDEX everything_and_the_kitchen_sink_index
(sts_user_id,sts_in,sts_sessid);

This index might get selected and never have to touch the table because all columns (WHERE, ORDER BY, MAX()) are in the index.
GIVE IT A TRY !!!


Can I restrict user's access on a certain IP? Like in a certain way in MySQL.
CREATE 'dbuser'@'10.1.0.1' identitifed by 'password';
GRANT USAGE on *.* to 'dbuser'@'10.1.0.1';
We are not using any mongodb tools except for Robo

When you create a user then you can specify Authentication Restrictions
Would be like this:
use admin
db.createUser(
   {
     user: "dbuser",
     pwd: "password"
     roles: [ { role: "readWrite", db: "reporting" } ],
     authenticationRestrictions: [ {
        clientSource: ["192.0.2.0"],
        serverAddress: ["198.51.100.0"]
     } ]
   }
)

But for me it is not clear, if you are looking for this.


Remove all values of a key in PostgreSQL hstore How do I delete/remove the value of a postgresql hstore column?

I've got a table named pages and a column named settings - I need to remove the read_only value (basically, set it to empty string or nil).

Something like this, pages is the table, setting is the column, and key is what you are setting to null.

update pages set settings = settings || '"key"=>null'::hstore;


Optimize query execution based on two datetime columns I've been struggling all day long against this.

We have a very busy database (still on development) and we have records being inserted very frequently. The record logs have a start time and a end time.

If I want to select something between col1(datetime) and col2(datetime) MySQL can't use indexes properly because it will search the index for col1 but will never look into col2.
The database engine is InnoDB. What happens is for example that MySQL will search 80 thousand rows when the interval requested should only return two rows.

My biggest problem is that I'm trying to do some aggregate functions on this time ranges and its taking a very long time when it should be really fast considering how many rows it is actually counting.

Also note that I can't do dateStart between col1 and col2 nor dateEnd between col1 and col2 because dateStart can be lower than col1 and dateEnd can also be lower than col2.

Lets assume this sample data:

     col1      |     col2
---------------+---------------
date 10:20:00  |date 10:21:00
date 10:21:00  |date 10:22:00
date 10:22:00  |date 10:23:00
date 10:23:00  |date 10:24:00
date 10:24:00  |date 10:25:00
date 10:25:00  |date 10:26:00
date 10:26:00  |date 10:27:00

If I need the rows that range between 10:21:30 and 10:25:30 I need to do something like this:

'10:25:30' <= col1 AND '10:21:30' >= col2.

So how do I index these columns properly? MySQL only picks up one of the date columns in the index.

Thanks to everyone who took the time to respond.
I got it fixed in a different way.
Since the values can't overlap and mysql is efficient using one single index for ranges, what i did was find the first col1 and last col1 and use a select between. This turned out really fast because mysql now analyses only the lines in the range.

As for the aggregates which were causing problems I still had to go round them anyways because I had to execute it separately or otherwise MySQL optimizer was really inefficient. The way I did it was by selecting the results into a temporary memory table and then loop those results (cursor wasn't a choice because I already had a loop) and doing the 3 aggregate functions at once. This is was the final result (just the loop part):

myLoop: LOOP

IF (SELECT COUNT(*) FROM (SELECT
    @CarrId := carrier_id,
    @FistVTime := firstValidTIME,
    @LastVTime := lastValidTIME
FROM startStopReportTable
WHERE maxSpeed = -1) AS dummyTbl) < 1 THEN
    LEAVE myLoop;
END IF;

UPDATE startStopReportTable AS t0,
    (SELECT
        SUM(l0.distance) AS distance,
        AVG(l0.speed) AS avgspeed,
        MAX(l0.speed) AS maxspeed
        FROM
            pcoarg AS l0
        WHERE
            l0.carrier_id = @CarrId
        AND
            l0.lastValidTIME BETWEEN @FistVTime AND @LastVTime
    ) AS t1
SET
    t0.distance = t1.distance,
    t0.averageSpeed = t1.avgspeed,
    t0.maxSpeed = t1.maxspeed
WHERE t0.carrier_id = @CarrId AND t0.firstValidTIME = @FistVTime AND t0.lastValidTIME = @LastVTime;
END LOOP;

LastValidTIME is col1
startStopReportTable is temporary table

Profiling results:

96  0.00058100  UPDATE startStopReportTable AS t0,
      (SELECT
       SUM(l0.distance) AS distance,
       AVG(l0.speed) AS avgspeed,
       MAX(l0.speed) AS maxspeed
       FROM
        pcoarg AS l0
       WHERE
        l0.carrier_id = @CarrId
       AND
        l0.lastValidTI...
97  0.00045100  UPDATE startStopReportTable AS t0,
      (SELECT
       SUM(l0.distance) AS distance,
       AVG(l0.speed) AS avgspeed,
       MAX(l0.speed) AS maxspeed
       FROM
        pcoarg AS l0
       WHERE
        l0.carrier_id = @CarrId
       AND
        l0.lastValidTI...
98  0.00094900  UPDATE startStopReportTable AS t0,
      (SELECT
       SUM(l0.distance) AS distance,
       AVG(l0.speed) AS avgspeed,
       MAX(l0.speed) AS maxspeed
       FROM
        pcoarg AS l0
       WHERE
        l0.carrier_id = @CarrId
       AND
        l0.lastValidTI...

It's really a shame that the optimizer can't combine sub query aggregates in one query while it can combine them in one query.


Do conditional 1:1 relationships improve normalization? Imagine a database schema that includes (among other things) two tables:

USER - A table of users, including their signon ID, name, etc.  The primary key is a surrogate key, "UserID" (auto integer).

ADMIN - A table of administrators, including a series of columns that a USER does not need but an ADMIN does, e.g. "SuperuserPassword" which is a hash of a password used for elevated permissions (this is just an example).  The primary key of this table is also "UserID" and is a foreign key to USER.UserID.

These two tables technically have a 1:1 relationship, although not every USER record will have an ADMIN record.  I believe this sort of relationship is referred to as a variant record, or in C terms it might be called a tagged union.  Essentially USER is a supertype and ADMIN is a subtype.

In the above scenario, assume both tables are in BCNF.

Now for my question.  Imagine a second scenario, where the DBE was a little bit lazier, and has all of the above data combined into a single table USER.  If the record pertains to a non-admin user, the SuperuserPassword field is simply left NULL.

In the second scenario, does the table design violate normalization rules?  Which type of normalization does it violate?

I am thinking there is an implied attribute here (user type) which can be inferred by the presence of the admin record.  Since the SuperuserPassword is an attribute that depends on a non-key [implied] attribute, the second design is a violation of third normal form.  But I am not certain this is technically accurate.

Please note, this is a completely academic question, and I am not asking for advice on this table design, which is just a straw man.

Normal forms based on functional dependencies (e.g. BCNF) are defined for relation schemas and relations consisting of values without nulls. As soon as you permit nulls you already have something that is not a normalized relational database.

In your first scenario, if your ADMIN table has (non-nullable) attributes, X,Y,Z, then the dependency {UserID}->{X,Y,Z} is enforced and will be satisfied for every admin user. If you combine the tables and make X,Y,Z nullable then those attributes may be null even for admin users. The dependency {UserID}->{X,Y,Z} is therefore no longer automatically enforced if you combine the tables. Assuming that dependency is important to you then you would have to add extra integrity constraints to validate that each of those attributes has a value for admin users and a null for non-admins. The advantages of BCNF (i.e. having both ADMIN and USER tables) are that the intended business rule {UserID}->{X,Y,Z} is declared as part of your table design, is obvious to anyone inspecting the schema and is guaranteed simply by the presence of keys and non-nullable attributes in your tables.


How are foreign key indexes helpful? How are foreign key indexes helpful?  What is the criteria for keeping a foreign key index?

This will be non-specific to oracle, but in general foreign key indexes are most useful because they speed up JOINs.

If you have a foreign key field, it is extremely likely you will use it as a JOIN key as well - that's the whole point of an FK, to make a direct association between one field in tablea to another field in tableb.

Having this field indexed will speed up any queries that use that field in either the WHERE filter or in the JOIN criteria.


can you run sql server 2005 and sql server 2008 r2 in mirror mode? Must all db servers be running the exact same version to enable mirroring without a witness server? How about with a witness server in place?

The partners must be the same edition and the same version.

Please see BOL's reference on Database Mirroring Overview

  For a new mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.

  Since SQL Server 2005 Service Pack 1 (SP1), database mirroring partners and witnesses have been supported by Standard and Enterprise Edition. But the partners must use the same edition, and asynchronous database mirroring (high-performance mode) is supported only by Enterprise Edition. Witnesses are also supported by Workgroup Edition and Express Edition.


How to order by clause on distinct select stament condition value This is my query,

SELECT DISTINCT
    BASE_Product.ProductId,
    (CASE WHEN (BASE_Art.ArtId IS NOT NULL AND BASE_Heel.HeelId IS NULL)
          THEN BASE_Art.ArtNo
          ELSE (CASE WHEN (BASE_Art.ArtId IS NULL AND BASE_Heel.HeelId IS NOT NULL)
                     THEN BASE_Heel.HeelNo
                     ELSE (BASE_Art.ArtNo + '-' + BASE_Heel.HeelNo) END)END) AS ArtHeelNo,
    BASE_InvoiceLine.Price
FROM
    BASE_Product
INNER JOIN BASE_ProductVariation
    ON BASE_Product.ProductId = BASE_ProductVariation.ProductId
INNER JOIN BASE_InvoiceLine
    ON BASE_ProductVariation.ProductVariationId = BASE_InvoiceLine.ProductVariationId
LEFT OUTER JOIN BASE_Art
    ON BASE_Product.ArtId = BASE_Art.ArtId
LEFT OUTER JOIN BASE_Heel
    ON BASE_Product.HeelId = BASE_Heel.HeelId
WHERE
    (BASE_InvoiceLine.InvoiceId = 2)

Once I execute the above query I'm getting the result like below screen shot.

This above result ordered by ProductId wise I want to get this order by HeelNo wise.

Issue is I can't order by HeelNo wise because of order by clause on distinct select and case..

I want the result should be,

How can I do this..

Since ORDER BY is executed after SELECT you can use the alias that you've defined in your SELECT clause.

ORDER BY ArtHeelNo

Another (less recommended) option would be to just order by the number of the column

ORDER BY 2

It's also possible to just repeat the statements in your ORDER BY, this is less recommended since if your statement would change, you have to change it twice. It's also a lot messier to read.

`ORDER BY (CASE WHEN (BASE_Art.ArtId IS NOT NULL AND BASE_Heel.HeelId IS NULL) THEN BASE_Art.ArtNo                              ELSE (CASE WHEN (BASE_Art.ArtId IS NULL AND BASE_Heel.HeelId IS NOT NULL)                               THEN BASE_Heel.HeelNo ELSE (BASE_Art.ArtNo + '-' + BASE_Heel.HeelNo) END)END)`


Do SQL Server Express Editions support CLR procedures? Do SQL Server Express Editions (2005, and 2008) have support for installing, enabling  and running CLR Stored procedures and functions.

Yes, the Express editions do support CLR integration. The link is for the latest version, but if you switch to 2008 you will see it is supported there too.


How to deal with large offsets in select? Table jtest with 200k rows, each row contains jsonb { id: "<uuid>", key: <index> } (<index> is integer 1-200k incremented per each row). There is also btree index on data->'key'.

create extension if not exists pgcrypto;
create table jtest (data jsonb not null default '{}');
insert into jtest (data)
select json_build_object('id', gen_random_uuid(), 'key', i)::jsonb
FROM generate_series(1,200000) i;
create index jtest_key on jtest ((data->'key'));

First query (fast):

EXPLAIN ANALYZE
select j.data
from jtest j
order by j.data->'key'
limit 20;

-- "Limit  (cost=0.42..1.43 rows=20 width=74) (actual time=0.023..0.044 rows=20 loops=1)"
-- "  ->  Index Scan using jtest_key on jtest j  (cost=0.42..10150.42 rows=200000 width=74) (actual time=0.023..0.039 rows=20 loops=1)"
-- "Planning time: 0.082 ms"
-- "Execution time: 0.066 ms"

Second query with large offset (slow):

EXPLAIN ANALYZE
select j.data
from jtest j
order by j.data->'key'
offset 100000
limit 20;

-- "Limit  (cost=5075.42..5076.44 rows=20 width=74) (actual time=112.861..112.896 rows=20 loops=1)"
-- "  ->  Index Scan using jtest_key on jtest j  (cost=0.42..10150.42 rows=200000 width=74) (actual time=0.027..106.378 rows=100020 loops=1)"
-- "Planning time: 0.105 ms"
-- "Execution time: 112.931 ms"

In PG docs I found:

  The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient.

But they don't say anything about how to deal with large offsets. How I can improve this query? Also is this behaviour (inefficiency for large offsets) common to all RDMS or just PG (using 9.4)?

Why not try a where clause? If you index j.data->'key', a where will only be a log(n).

Another reason may be order by j.data->'key' is a slow computation. Indexing this can also boost your order by (but the benefit will be far less noticeable than the benefit due to the where clause itself).

This article may help better answer your question:
https://use-the-index-luke.com/sql/partial-results/fetch-next-page


How to copy migrate data to new tables with identity column, while preserving FK relationship? I want to migrate data from one database to another. The table schemas are exactly the same:

CREATE TABLE Customers(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY,
    (some other columns ......)
);

CREATE TABLE Orders(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY,
    [CustomerId] INT NOT NULL,
    (some other columns ......),
    CONSTRAINT [FK_Customers_Orders] FOREIGN KEY ([CustomerId]) REFERENCES [Customers]([Id])
)

The two databases have different data, so the new identity key for the same table would be different at the two databases. That is not a problem; my goal is to append new data to the existing one, not complete replacing all data of the entire table. However I would like to maintain all parent-child relationship of the inserted data.

If I use the "Generate Script" feature of SSMS, the script would attempt to insert using the same ID, which would conflict existing data in the destination database. How can I copy data using database scripts only?

I want the identity column at the destination to continue normally from its last value.

Customers does not have any other UNIQUE NOT NULL constraint. It is ok to have duplicate data in other columns (I'm using Customers and Orders just as an example here, so I don't have to explain the whole story). The question is about any one-to-N relationship.

Here's a way that scales easily to three related tables.

Use MERGE to insert the data into the copy tables so that you can OUTPUT the old and new IDENTITY values into a control table and use them for related tables mapping.

The actual answer is just two create table statements and three merges. The rest is sample data setup and tear down.

USE tempdb;

--## Create test tables ##--

CREATE TABLE Customers(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [Name] NVARCHAR(200) NOT NULL
);

CREATE TABLE Orders(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [CustomerId] INT NOT NULL,
    [OrderDate] DATE NOT NULL,
    CONSTRAINT [FK_Customers_Orders] FOREIGN KEY ([CustomerId]) REFERENCES [Customers]([Id])
);

CREATE TABLE OrderItems(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [OrderId] INT NOT NULL,
    [ItemId] INT NOT NULL,
    CONSTRAINT [FK_Orders_OrderItems] FOREIGN KEY ([OrderId]) REFERENCES [Orders]([Id])
);

CREATE TABLE Customers2(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [Name] NVARCHAR(200) NOT NULL
);

CREATE TABLE Orders2(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [CustomerId] INT NOT NULL,
    [OrderDate] DATE NOT NULL,
    CONSTRAINT [FK_Customers2_Orders2] FOREIGN KEY ([CustomerId]) REFERENCES [Customers2]([Id])
);

CREATE TABLE OrderItems2(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [OrderId] INT NOT NULL,
    [ItemId] INT NOT NULL,
    CONSTRAINT [FK_Orders2_OrderItems2] FOREIGN KEY ([OrderId]) REFERENCES [Orders2]([Id])
);

--== Populate some dummy data ==--

INSERT Customers(Name)
VALUES('Aaberg'),('Aalst'),('Aara'),('Aaren'),('Aarika'),('Aaron'),('Aaronson'),('Ab'),('Aba'),('Abad');

INSERT Orders(CustomerId, OrderDate)
SELECT Id, Id+GETDATE()
FROM Customers;

INSERT OrderItems(OrderId, ItemId)
SELECT Id, Id*1000
FROM Orders;

INSERT Customers2(Name)
VALUES('Zysk'),('Zwiebel'),('Zwick'),('Zweig'),('Zwart'),('Zuzana'),('Zusman'),('Zurn'),('Zurkow'),('ZurheIde');

INSERT Orders2(CustomerId, OrderDate)
SELECT Id, Id+GETDATE()+20
FROM Customers2;

INSERT OrderItems2(OrderId, ItemId)
SELECT Id, Id*1000+10000
FROM Orders2;

SELECT * FROM Customers JOIN Orders ON Orders.CustomerId = Customers.Id JOIN OrderItems ON OrderItems.OrderId = Orders.Id;

SELECT * FROM Customers2 JOIN Orders2 ON Orders2.CustomerId = Customers2.Id JOIN OrderItems2 ON OrderItems2.OrderId = Orders2.Id;

--== ** START ACTUAL ANSWER ** ==--

--== Create Linkage tables ==--

CREATE TABLE CustomerLinkage(old INT NOT NULL PRIMARY KEY, new INT NOT NULL);
CREATE TABLE OrderLinkage(old INT NOT NULL PRIMARY KEY, new INT NOT NULL);

--== Copy Header (Customers) rows and record the new key ==--

MERGE Customers2
USING Customers
ON 1=0 -- we just want an insert, so this forces every row as unmatched
WHEN NOT MATCHED THEN
INSERT (Name) VALUES(Customers.Name)
OUTPUT Customers.Id, INSERTED.Id INTO CustomerLinkage;

--== Copy Detail (Orders) rows using the new key from CustomerLinkage and record the new Order key ==--

MERGE Orders2
USING (SELECT Orders.Id, CustomerLinkage.new, Orders.OrderDate
FROM Orders
JOIN CustomerLinkage
ON CustomerLinkage.old = Orders.CustomerId) AS Orders
ON 1=0 -- we just want an insert, so this forces every row as unmatched
WHEN NOT MATCHED THEN
INSERT (CustomerId, OrderDate) VALUES(Orders.new, Orders.OrderDate)
OUTPUT Orders.Id, INSERTED.Id INTO OrderLinkage;

--== Copy Detail (OrderItems) rows using the new key from OrderLinkage ==--

MERGE OrderItems2
USING (SELECT OrderItems.Id, OrderLinkage.new, OrderItems.ItemId
FROM OrderItems
JOIN OrderLinkage
ON OrderLinkage.old = OrderItems.OrderId) AS OrderItems
ON 1=0 -- we just want an insert, so this forces every row as unmatched
WHEN NOT MATCHED THEN
INSERT (OrderId, ItemId) VALUES(OrderItems.new, OrderItems.ItemId);

--== ** END ACTUAL ANSWER ** ==--

--== Display the results ==--

SELECT * FROM Customers2 JOIN Orders2 ON Orders2.CustomerId = Customers2.Id JOIN OrderItems2 ON OrderItems2.OrderId = Orders2.Id;

--== Drop test tables ==--

DROP TABLE OrderItems;
DROP TABLE OrderItems2;
DROP TABLE Orders;
DROP TABLE Orders2;
DROP TABLE Customers;
DROP TABLE Customers2;
DROP TABLE CustomerLinkage;
DROP TABLE OrderLinkage;


SQL Error: 1038: An object or column name is missing or empty Currently I'm working with windows credentials, when i trying to create a user with SQL Authentication encountered the attached error. I have seen it is due to empty login name. How to resolve this issue.

Are you trying to create empty login?

CREATE LOGIN [] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

If we run above in the SSMS query window, we get below error message:

  Msg 1038, Level 15, State 4, Line 1 An object or column name is
  missing or empty. For SELECT INTO statements, verify each column has a
  name. For other statements, look for empty alias names. Aliases
  defined as “” or [] are not allowed. Change the alias to a valid name.

Now the error makes sense;

  Because login is created as [] and error clearly says that “…[] are
  not allowed…”

Solution:

  The issue was caused because of the empty login name. If we provide name, then the issue will be resolved.

For more info check  CREATE LOGIN


OS Paging too high in sql server For one of my servers with below configuration:

  Total RAM: 32 GB

  CPU: 8

  Max Server Mem: 22.5 GB

  Min Server:: 4 GB

  Edition: Enterprise Edition with 2k8R2 installed on SP3

  Page file: System Managed on the C:\

  AWE Enabled: NO as it being 64 Bit

I am continuously getting the alerts for OS paging High..

When I checked using RAMMAP Tool for memory utilization, I found that there is AWE which using an approx of 23.8 GB and thus blocking any further memory usage:

More over for the user right assignments : "Locked pages in Memory" has not been assigned any rights!

Kindly help me understanding where more I need to investigate, or a solution for this

Thanks!

am continuously getting the alerts for OS paging High.

Memory is basically two types the physical random access memory (RAM) and virtual memory. A process has to be first mapped to Virtual memory and then to physical memory. All programs use RAM, but when there isn't enough RAM for the program you're trying to run, Windows temporarily moves information that would normally be stored in RAM to a file on your hard disk called a paging file. The amount of information temporarily stored in a paging file is also referred to as virtual memory. Now error page file is less can have multiple meanings

You are running some processes which is heavily utilizing memory and since OS cannot find enough RAM for it it has to use Page file as temporary area for storage.
Since SQL Server is running with Locked pages in memory privilege this memory cannot be paged out to disk and OS cannot directly used this memory by asking SQL Server to release it heavily. So in this case you need to lower max server memory of SQL Server a bit and give more memory to OS so that it can function properly. use how to set optimum value for max server memory to set correct value for max server memory. In your case you have left 10 G for OS which I consider is pretty much good unless you are using SSIS,SSAS and SSRS.

Problem may be not SQL Server memory or memory allocated by AWE API( Locked pages) problem here is windows page file size is less. You need to act on this. Page file is temporary area which is used to hold temporary data which is swapped in and out of physical memory in order to provide a larger virtual memory set. Page file space is reserved when the pages are initially committed, however the page file locations are not chosen until the page is written to disk.

The page file needs of an individual system will vary based on the role of the server, load etc.  There are some performance counters that you can use to monitor private committed memory usage on a systemwide or per-page-file basis.  There is no way to determine how much of a process' private committed memory is resident and how much is paged out to paging files.

Memory: Committed Bytes: Number of bytes of virtual memory that has been committed.  This does not necessarily represent page file usage - it represents the amount of page file space that would be used if the process was completely made nonresident

Memory: Commit Limit:   Number of bytes of virtual memory that can be committed without having to extend the paging files.

Paging File: % Usage    Percentage of the paging file committed

Paging File: % Usage Peak   Highest percentage of the paging file committed

Please use above counters to set proper value for page file. You can read This Link to get more information about page file

You can set page file by Right click on My Computer select properties and then go on Advanced System Settings.In Performance section click on setting and then advanced tab. You would see virtual memory section and then click on change you can set virtual memory here. Please refer to screenshot. Take help of windows team in configuring optimum value. If option automatically set page file for drives is set you can uncheck it and click on SET to set optimum value of page file.

  When i checked using RAMMAP Tool for memory utilization i found that there is AWE which using an approx of 23.8 GB and thus blocking any further memory usage:

This is pretty much normal considering the fact that SQL Server service account is Local system so by default SQL server account will get Locked pages in memory privilege.


SSRS table populating data into a field that should be blank I am having trouble resolving an issue. I have a table on an SSRS report. The query for the table gets the definition, the total count of that definition and calculates the percentage of all the counts. However, as you can see in the image, one field is being populated with another fields data when it should be blank. Are there any SSRS experts out there who can lend a hand?

Here is the query:

SELECT fundingyear
    , loan_type
    , COUNT() AS total_lntype
    , COUNT() * 1.0 / SUM(COUNT(*)) OVER (PARTITION BY fundingyear) as lntype_perc
FROM vw_LoanRpt
WHERE loan_type not in ('Line of Credit')
GROUP by fundingyear, loan_type

I found that using a Matrix instead of a table resolves the issue.


Statistics that are neither auto_created nor user_created So far, I was under the impression that there are two types of statistics objects in SQL Server:

automatically-created statistics, which are created by SQL Server on index columns or single columns to improve performance, and
user-created statistics, which are created manually with a CREATE STATISTICS statement.

Thus, I was quite surprised to see the following query:

SELECT s.name, s.stats_id, s.auto_created, s.user_created
  FROM sys.stats AS s
       INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
       INNER JOIN sys.columns AS c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
       INNER JOIN sys.tables AS t ON c.object_id = t.object_id
 WHERE t.name = 'mytable'

yield the following result:

name                    stats_id   auto_created   user_created
--------------------------------------------------------------
PK__mytable__7A73B1B8       1          0              0
SomeIndex                   2          0              0
SomeIndexedColumn1          3          0              0
SomeIndexedColumn2          4          0              0
SomeIndexedColumn3          5          0              0
SomeColumn                  6          0              1

What are those statistics, which are neither auto_created nor user_created? I've looked at the documentation of sys.stats, but did not find an answer there.

Those are statistics created with the creation of an index.

I guess they somehow infer that the statistics haven't been created as statistics but as a side effect of an index.

I've noticed this in my databases before and they just seem to always be index-related. I can't find any documentation proving this though.


plpgsql function to replace ' ' with null after insert using a trigger I have a table in PostgreSQL 9.5 on which insert statements are run frequently. After an insert statement was ran on the table there are ' ' (single quote, space, single quote) values when no data was inserted for a specific column.
I want to run a trigger function after each insert to replace the ' ' with Null. However, I am struggling with writing the function.

What I have thus far:

CREATE OR REPLACE FUNCTION delete_space() RETURNS TRIGGER AS $_$
BEGIN
UPDATE proj.general
SET deliv1 = Null,
deliv2 = Null,
fmt = Null,
spec_instr = Null
where general.deliv1 = ''
or general.deliv2 = ''
or general.fmt = ''
or general.spec_instr = '';
RETURN NULL;
END $_$ LANGUAGE 'plpgsql';

This clears the ' ' but it also clears any new input in the columns specified in the function. As an example, if 'this is special instructions' is inserted into column general.spec_instr, the input is cleared after the function ran. How do I rectify this?
Also, is there a way to check all columns of new inserts for ' ' without having to specify the specific column name as in the function above?

EDIT:

CREATE TRIGGER del_spaces_trig
  AFTER INSERT
  ON projects.general
  FOR EACH STATEMENT
  EXECUTE PROCEDURE public.delete_space();

Just modify your SET clause and use CASE ... WHEN ... END:

CREATE OR REPLACE FUNCTION delete_space() RETURNS TRIGGER AS $_$
BEGIN
UPDATE proj.general
SET deliv1 = case when deliv1 ='' then NULL else deliv1 end,
SET deliv1 = case when deliv2 ='' then NULL else deliv2 end,
SET deliv1 = case when fmt ='' then NULL else fmt end,
SET deliv1 = case when spec_instr ='' then NULL else spec_instr end
where general.deliv1 = ''
or general.deliv2 = ''
or general.fmt = ''
or general.spec_instr = '';
RETURN NULL;
END $_$ LANGUAGE 'plpgsql';


SQL Server : Perfmon Counter Incorrect value I'm trying to understand why this Performance counter shows it is 0.

   "Mssql$instance1:locks(_total)\lock wait time (ms)"

I tried with wild card to check all instance_name and they're all 0.

   "Mssql$instance1:locks(*)\lock wait time (ms)"

But if I run the query

SELECT cntr_value
FROM master..sysperfinfo
WHERE (object_name LIKE '%:Locks%'
  AND counter_name = 'Lock Wait Time (ms)'
  AND instance_name = '_Total')

shows a different value (which is a count of "Object", "Key", and "Metadata")

You should use the sys.dm_os_performance_counters DMV instead of the sysperfinfo backward compatibility view. That will report the same value, though.

The DMV counter value is cumulative since the SQL instance was started. Counter values reported by Performance Monitor for this counter are per second so you'll see non-zero values only when the total lock wait time is 1 ms or greater over the last second.

To verify the counters are working as expected run the scripts below to create a blocking lock for the duration of a transaction:

--Run this script in SSMS query window #1
USE tempdb;
CREATE TABLE dbo.LockTest(
    Col1 int NOT NULL IDENTITY CONSTRAINT PK_LockTest PRIMARY KEY
    );
BEGIN TRAN;
INSERT INTO dbo.LockTest DEFAULT VALUES;
WAITFOR DELAY '00:00:10';
COMMIT;
GO
DROP TABLE dbo.LockTest;
GO

--Run this in SSMS query window #2 while the first script is running
SELECT * FROM dbo.LockTest;

PMON will show a non-zero value for both the 'KEY' and '_Total' instances of Lock Wait Time(ms) after running these scripts.


MSSQL Mirroring + db integrity check Is it necessary to do a database integrity check (DBCC check) on mirrored databases?

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/automatic-page-repair-availability-groups-database-mirroring?view=sql-server-2017

  Automatic page repair is supported by database mirroring and by Always
  On availability groups. After certain types of errors corrupt a page,
  making it unreadable, a database mirroring partner (principal or
  mirror) or an availability replica (primary or secondary) attempts to
  automatically recover the page.

What is you best practice?

No, that's one of the main benefits of mirroring is the page repair; it checks for corruption and then tries to address it if it finds any.


"ERROR:malformed array literal" when using json_to_record with a JSON array element in Postgres 9.4 This illustrates the issue nicely:

When column b is of type text, and not an array, the following works:

select *
from json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}')
    as x(a int, b text, d text);

 a |         b          | d
---+--------------------+---
 1 | ["hello", "There"] |

But if I define the b column as an array, I get this error:

select *
from json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}')
    as x(a int, b text[], d text)

ERROR:  malformed array literal: "["hello", "There"]"
DETAIL:  "[" must introduce explicitly-specified array dimensions.

How can I convince/coerce json_to_record (or json_populate_record) to convert a JSON array into the Postgres array of the target column type?

Just a slight variation to Chris's answer:

SELECT a, translate(b, '[]', '{}')::text[] AS b, d
FROM json_to_record('{"a": 1, "b": ["hello", "There"], "c": "bar"}')
AS x(a int, b text, d text);

The idea is the same: massage the JSON array into an array - in this case, through an array literal.  In addition to a bit cleaner looking code (though I love it, regex usually does not help much in this regard :), it seems slighly faster, too:

CREATE TABLE jsonb_test (
    id serial,
    data jsonb
);

INSERT INTO jsonb_test (id, data)
SELECT i, format('{"a": %s, "b": ["foo", "bar"], "c": "baz"}', i::text)::jsonb
FROM generate_series(1,10000) t(i);

SELECT a, string_to_array(regexp_replace(b, '\[*\"*\s*\]*','','g'),',') AS b, d
FROM jsonb_test AS j,
LATERAL json_to_record(j.data::json) AS r(a int, b text, d text);

-- versus

SELECT a, translate(b, '[]', '{}')::text[] AS b, d
FROM jsonb_test AS j,
LATERAL json_to_record(j.data::json) AS r(a int, b text, d text);

On this dataset and on my test box, the regex version shows and average execution time of 300 ms, while my version shows 210 ms.


Why select max partitioned column is much slower than indexed one? I have a big partitioned table (300+M rows, 70 active partitions). The table has clustered PK of (K0, K1) and it's partitioned on column K1. And K1 has very low selectivity.

However the following query takes a half minute and the scan count is 170 and logical read is 603K.

select max(K1) from table

However the similar query on on non-partitioned table with index on K1 takes no time and scan count is 1 and logical read is 5.

Should a none partitioned index be created on K1?

Edit:
Plan text for partitioned table without index on K1. With clustered PK on K0,K1, before create an index on C1.

  |--Stream Aggregate(DEFINE:([Expr1004]=MAX([partialagg1005])))
       |--Parallelism(Gather Streams)
            |--Stream Aggregate(DEFINE:([partialagg1005]=MAX([DB1].[dbo].[table1].[K1])))
                 |--Clustered Index Scan(OBJECT:([DB1].[dbo].[table1].[PK_dbo_tabl1]))

Plan text for non-partitioned table with index on K1.

  |--Stream Aggregate(DEFINE:([Expr1003]=MAX([DB1].[dbo].[table2].[K1])))
       |--Top(TOP EXPRESSION:((1)))
            |--Index Scan(OBJECT:([Db1].[dbo].[table2].[idx_K1]), ORDERED BACKWARD)

Plan text for partitioned table with clustered PK on K0,K1, and index on C1.

  |--Stream Aggregate(DEFINE:([Expr1004]=MAX([partialagg1005])))
       |--Parallelism(Gather Streams)
            |--Stream Aggregate(DEFINE:([partialagg1005]=MAX([db1].[dbo].[table1].[K1])))
                 |--Index Scan(OBJECT:([db1].[dbo].[table1].[IX_C1]))

Think I mis-understood/mis-read the question initially.

  The table is partitioned on column K1. However the following query
  takes a half minute and the scan count is 170 and logical read is
  603K.

To satisfy the MAX(k1) query in this case requires a clustered index scan of each of the 70 partitions.

  However the similar query on on non-partitioned table with index on K1
  takes no time and scan count is 1 and logical read is 5.

With an index defined on K1, an ORDERED BACKWARD scan of the index is chosen, which probably translates to 4 pages read from the non-leaf index pages plus 1 for the leaf level.