First step: Understand how backups really work. The main things to understand:

Log backups after SQL Server 2000 can be taken concurrently during full and differential backups. The only thing to remember is during that timeframe the log will not be cleared until that full or differential backup completes.
Full and differential backups that collide will cause whichever backup started first to take precedence, the second backup will wait in line until prior backup is completed. So if you have a FULL backup running, and a DIFF backup starts, that DIFF backup will wait until the FULL is complete. I do believe it should be the same for the opposite as well.

Second Step: Whichever backup plan you choose ensure it meets the recovery requirements for the application AND/OR the business.

So while either schedule will work the schedule 2 will likely lessen the amount of IO activity that is caused when the backups overlap. It would based on the size of the database and how much activity SQL Server has to backup during the differential and log backups. Depending on how you setup schedule 1 you could end up with all three backups trying to occur at the same time frame.


There are two possible solutions

Do a USING join like below

CREATE VIEW report_view
AS
WITH d as(
SELECT *
FROM report.get_sa001('2013-01-01'::date", TO_CHAR(NOW()", 'YYYY-MM-DD')::date", 32)
    )",
     a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Site"", "Reference_internal"", "Customer_code") IN
       ( SELECT "Site"",
                    "Internal_reference"", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a USING ("Site","Internal_reference","Customer_code")
-- here is the USING. Courtesy of Jack Douglas for the tip

However as I'm retrieving datas from 5 years ago, it takes more than 5 minutes if I'm using a USING.

The best way is to call each columns separately like below and be sure to name the origin of the column, here a for Site and Customer_code.

CREATE VIEW report_view AS WITH d as
  ( SELECT *
   FROM report.get_sa001('2015-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
                           a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Site", "Reference_internal", "Customer_code") IN
       ( SELECT "Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT "Period_date",
       a."Site",
       a."Customer_code",
       "Internal_reference",
       "InvoiceNumber",
       "Value_in_currency",
       "Value_in_EUR",
       "Value_Budget_in_EUR",
       "Selling_price_CUR",
       "Selling_price_EUR",
       "Currency_code",
       "Selling_quantity",
       "Variance_price_CUR",
       "Variance_price_EUR",
       "Variance_value_CUR",
       "Variance_value_EUR",
       "Selling_date",
       "Reference",
       "Reference_internal",
       "Reference_customer",
       "Reference_supplier",
       "Reference_description",
       "Reference_workshop",
       "Reference_line",
       "Reference_segment",
       "Reference_purchasingfamily",
       "Reference_motorapplication",
       "Reference_interco",
       "Reference_trading",
       "Cogs_rm",
       "Cogs_dl",
       "Cogs_voh",
       "Supplier_global",
       "Supplier_code",
       "Supplier_name",
       "Supplier_account_manager",
       "Supplier_interco",
       "Supplier_incoterm",
       "Supplier_incoterm_location",
       "Supplier_incoterm_via",
       "Supplier_continent",
       "Supplier_country",
       "Supplier_city",
       "Supplier_zipcode",
       "Purchasing_unit",
       "Purchasing_price",
       "Purchasing_currency",
       "Purchasing_payment_term_days",
       "Purchasing_payment_term_type",
       "Purchasing_consigned",
       "Purchasing_grossweight",
       "Purchasing_grosscube",
       "Purchasing_eco_order_qty",
       "Purchasing_pack_order_qty",
       "Purchasing_moq",
       "Purchasing_mov",
       "Purchasing_leadtime_days",
       "Customer_global",
       "Customer_name",
       "Customer_account_manager",
       "Customer_interco",
       "Customer_incoterm",
       "Customer_incoterm_location",
       "Customer_incoterm_via",
       "Customer_continent",
       "Customer_country",
       "Customer_city",
       "Customer_zipcode",
       "Selling_unit",
       "Selling_price",
       "Selling_currency",
       "Selling_payment_term_days",
       "Selling_payment_term_type",
       "Selling_consigned",
       "Selling_grossweight",
       "Selling_grosscube",
       "Selling_eco_order_qty",
       "Selling_pack_order_qty",
       "Selling_moq",
       "Selling_mov",
       "Selling_leadtime_days",
       "Reference_netweight",
       "Sc_storage_unit",
       "Sc_production_unit",
       "Sc_inventory_status",
       "Sc_inventory_price",
       "Sc_inventory_type"
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code"

This will give the required speed in term of data retrieval.

Cheers


mysql statement based replication - unsafe statements

It will probably cause differences on the slave in the long run. I've had the same warnings (but with INSERT DELAYED, so your mileage may vary), and on a couple of write intensive tables the data would be different a couple of times a week (leading to a lenghty dump and import).

The ordering of some things is not necessarily the same on every mysql server, so you might end up with the same data in a different order. This is what "not deterministic" means, you execute the exact same query, but you might end up with different results.

If your mysql version supports the MIXED mode for binlogging, I'd definitely use that.
As you can see on http://dev.mysql.com/doc/refman/5.1/en/binary-log-formats.html, the only reason it's not MIXED by default is backward compatibility:

  Support for row-based logging was added in MySQL 5.1.5. Mixed logging
  is available beginning with MySQL 5.1.8. In MySQL 5.1.12, MIXED become
  the default logging mode; in 5.1.29, the default was changed back to
  STATEMENT for compatibility with MySQL 5.0.

MIXED mode uses STATEMENT mode binlogging (the default), unless it knows the query is non deterministic, then it'll switch to ROW mode for those queries. With ROW mode, it simply sends the changed rows if any, so that's deterministic again.


Why can a non-clustered index not be dropped using online=on option

Because dropping of a NCI is already as much online as it gets. Is a metadata only operation. There is not even data deletion, a dropped index rowset is simply deallocated, ie. the same operation as truncate does.

Dropping a clustered index, on the other hand, implies a rebuild and is a size-of-data operation, so it does make sense to have an online alternative.


Group by but preserve statistical probability of value in limit

That was a challenge.  I think this will do the job:

CREATE TEMPORARY TABLE t (
     name VARCHAR(99)  NOT NULL,   -- this datatype should match your `name`
     rnd DOUBLE  NOT NULL
)
    SELECT name, RAND() FROM your_table;

SELECT name, MIN(rnd) AS x
    FROM t
    GROUP BY name
    ORDER BY x
    LIMIT ... ;    -- Fill in the "N" you want


MySQL default wait_timeout is 28800 but in my server I can see more than that value

Ah! This one could be application framework has the value passed at the time of creating connection to DBs to set wait_timeout or interactive_timeout for their session level, this overrides default mysql settings.

If you enable general log on any test environment and have their application to point to that DB. You will find wait_timeout or interactive_timeout setting such values.

Short Term Fix:

You might consider killing connections that are more than 500secs in sleep state as a cron running every minute.

Permenant Fix:

Have application team to fix their code, by not to pass any default values to DB.
Once app is fixed, remove the cron that kills every minute.


Is DETERMINISTIC NO SQL function which returns the same value always a good alternative for using as a constant in MySQL?

I'd say yes, it is a good technique.

When there are no input arguments to a stored function declared as DETERMINISTIC and NO SQL, the optimizer does the right thing -- it executes the function once, and resolves the result from the function into an internal constant when the query is planned, so applicable indexes on the column being compared to the result of the function are used, as expected.

The behavior and performance using this technique should (for all practical purposes) be no different than if the query actually contained the literal constant.


Posting the create table statements and insert statements instead of pictures could really help  the next time you ask a question. Less trouble for both parties as you will get a faster answer.

Having said that, below is a way to do it but there are a few considerations / uncertanties due:

the datatypes not being known
'?' returning 'Y' in your example resultset
...

Calculating it for each customer for each month:

;WITH CTE AS
(
        SELECT CALMONTH as CALMONTH, CUSTOMER, INVCD_VAL FROM CUSTOMER_HISTORY
        UNION ALL
        SELECT LEFT(CALDAY, 6) as CALMONTH, SOLD_TO, REVENUE as INVCD_VAL FROM CUST_HISTORY_MIS
)

SELECT DISTINCT CALMONTH,
                CUSTOMER,
                CASE WHEN SUM(INVCD_VAL) OVER(PARTITION BY CALMONTH,CUSTOMER) = 0 then 'Y' else 'X' END AS HISTORY_FLAG
FROM CTE;

But as you will notice, this returns some more values than the result set specified, I also added CALMONTH.

Resultset

CALMONTH    CUSTOMER    HISTORY_FLAG
201701      2340002          Y
201701      2340003          X
201701      2340004          Y
201702      2340001          X
201703      2340001          X
201703      2340016          Y
2019-0      2340001          X
2019-0      2340017          X
2019-0      2340018          X
2019-0      2340019          X
2019-0      2340020          X

To get closer to your resulset, I decided that whenever a customer has a revenue that is <> 0, the flag for that customer is set to 'X', even if he has a revenue of 0 in one of the months.
Query

;WITH CTE AS
(
        SELECT CALMONTH as CALMONTH, CUSTOMER, INVCD_VAL FROM CUSTOMER_HISTORY
        UNION ALL
        SELECT LEFT(CALDAY, 6) as CALMONTH, SOLD_TO, REVENUE as INVCD_VAL FROM CUST_HISTORY_MIS
)
, CTE2 AS
(
SELECT CALMONTH,CUSTOMER, CASE WHEN SUM(INVCD_VAL) OVER(PARTITION BY CALMONTH,CUSTOMER) = 0 then 'Y' else 'X' END AS HISTORY_FLAG
FROM CTE
)

SELECT CUSTOMER, MIN(HISTORY_FLAG) as HISTORY_FLAG
FROM CTE2
GROUP BY CUSTOMER;

Resultset

CUSTOMER    HISTORY_FLAG
2340001         X
2340002         Y
2340003         X
2340004         Y
2340016         Y
2340017         X
2340018         X
2340019         X
2340020         X

Dataset used

CREATE TABLE Customer_history(CALMONTH char(6),CUSTOMER bigint,INVCD_VAL INT);

CREATE TABLE CUST_HISTORY_MIS(CALDAY date,SOLD_TO bigint, REVENUE   INT);

INSERT INTO Customer_history(CALMONTH,CUSTOMER,INVCD_VAL)
VALUES('201702',2340001,10),('201703',2340001,-10)
,('201701',2340002,40)
,('201701',2340002,-40)
,('201701',2340004,0)
,('201701',2340003,-20)
,('201703',2340016,0);

INSERT INTO CUST_HISTORY_MIS(CALDAY,SOLD_TO,REVENUE)
VALUES('20190203',2340001,5),
('20190203',2340001,5),
('20190301',2340019,11),
('20190330',2340017,12),
('20190311',2340018,NULL),
('20190313',2340020,NULL);


Almost sequential GUIDs vs random GUIDs?

Any GUID for an identity column will be an issue due to the size of it. This leaves less space in each page for actual data which means your data density is lowered and you are likely to see more page splits and fragmentation.

The problem when you use non sequential GUIDs on an id column with a clustered index is fragmentation. If you had 100 rows in the table with batches of 20 with sequential GUID values as the id and then wanted to add a random id value the engine may need to find space in the middle of your table to insert that record instead of just adding it to the end.

Depending on your fill factor level this could cause page splits which cause index fragmentation. Lower the fill factor of the index can help with fragmentation but not as much as always sequential values. This way each new record can be added to the newest page or a new page can be added at the end without the need to reorganize existing data.

Check here for more info.


Separate tables or partition one huge table?

I have an suggestion regarding your second option.

If you need to keep the MyISAM tables with each unique my_column value separated from other MyISAM tables, you may want to look into the MERGE (Mrg_MyISAM) Storage Engine as an alternative to table partitioning.

This will allow your multiple MyISAM tables within the same database that have identical table structures and index layout to be mapped together in such a way that single query hits all MyISAM tables.

Suppose you have a table as follows:

CREATE TABLE tb1
(
    my_column INT NOT NULL
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(30),
    PRIMARY KEY (my_column,id),
    KEY name (my_column,name)
) ENGINE=MyISAM;

and you have 3 such tables (tb1, tb2, tb3) where

my_column for tb1 = 1
my_column for tb2 = 2
my_column for tb3 = 3

You can merge them under a single table mapping as follows:

CREATE TABLE tbmerge LIKE tb1;
ALTER TABLE tbmerge ENGINE=Mrg_MyISAM UNION=(tb1,tb2,tb3);

To perform a search through all the tables, just use tbmerge. For example, suppose you want to see every name from tb2 that starts with 'Jack', you run this query:

SELECT name FROM tbmerge WHERE my_column=2 AND name LIKE 'Jack%';

Given the design of the table, you should always specify the value for my_column. In fact, for every index tb1 has, make sure my_column is always the first column. The reason? A query against tbmerge is always a query against tb1, tb2, tb3 (all underlying tables). Otherwise, this query

SELECT name FROM tbmerge WHERE name LIKE 'Jack%';

will experience horrible performance because it will perform table scans against all underlying tables. Please plan carefully the indexes you will be using, following that simple rule (using my_column as the first column of every index)

There is an additional benefit: You can INSERT into the underlying tables at your convenience, thus working with the

I discussed this using MERGE tables in an earlier post (Jan 4, 2012).


Need to convert date to word using trigger

Based on your comment, the trigger has multiple lines.

You need a BEGIN...END block as follows:

USE mcet
DELIMITER $$
DROP TRIGGER IF EXISTS tg_student_details_insert;
CREATE TRIGGER tg_student_details_insert BEFORE INSERT
ON student_details FOR EACH ROW
BEGIN
    SET NEW.created_date = NOW();
    SET NEW.dobw = date_to_words(NEW.dob);
END;
DELIMITER ;

You answered this question before (How to autoincrement Prod_id based on Product_Name using trigger in mysql). In that post, you used a BEGIN...END block for four(4) lines of code. You needed one for this post as well.


How to Prevent an Ever-Growing index file (MyISAM engine) From Causing Disk Fragmentation in Windows (NTFS partition)?

When you perform any OS defragmentation or virus scan operations, please make sure mysql is shutdown. No utilities (mysql-based or otherwise) should be running against any table while the server is up. MySQL is not good to run on a PC that is not dedicated to MySQL DB only, especially in the presence of defragmentation or virus scan software running. Temp tables have also been known to get whacked by virus scan software.

At the very least, move the MyISAM table to a folder away from the standard datadir.

You should consider defragmenting the MyISAM table using:

OPTIMIZE TABLE calllog;

or you can do it in stages:

CREATE TABLE calllognew LIKE calllog;
ALTER TABLE calllognew DISABLE KEYS;
INSERT INTO calllognew SELECT * FROM calllog;
ALTER TABLE calllognew ENABLE KEYS;
ALTER TABLE calllog RENAME calllogold;
ALTER TABLE calllognew RENAME calllog;
DROP TABLE calllogold;

You may need to consider upgrading to MySQL 5.5 for the latest MyISAM storage engine code.

If you want to switch to InnoDB, you must configure it so that each table has its own tablespace.


PostgreSQL - retrieve all IDs in a tree for a given subnode

A very primitive implementation:

It basically divides the problem into two subproblems:

First find all the ancestors of the node in question (including the node itself). If the node has no parents, then this would be just itself.
Then find the descendants of all those ancestors (including themselves). We may have several nodes in the ancestors result set, we may get duplicates here, so we use UNION (and not UNION ALL) to remove them.
Note that the query will work even if the input node is a root with has no children.
It will also work if the data set is not a forest of trees but an arbitrary  directed graph (where nodes can have more than one parent).

The query:

WITH RECURSIVE
ancestors (parent) AS
(
  SELECT :node                      -- start with the given node
  UNION ALL
  SELECT t.parent                   -- and find all its ancestors
  FROM public.customerincustomer AS t JOIN ancestors AS a ON t.child = a.parent
),
descendants (customer) AS
(
  SELECT parent AS customer       -- now start with all the ancestors
  FROM ancestors
  UNION
  SELECT t.child                  -- and find all their descendants
  FROM public.customerincustomer AS t JOIN descendants AS d ON t.parent = d.customer
)
SELECT customer
FROM descendants ;


Would a date clustered index speed up queries with date ranges? (SQL Server)

You ask if the clustered index is a waste: ideally a clustered index is unique, relatively small and does not change once created (see https://www.red-gate.com/simple-talk/sql/learn-sql-server/effective-clustered-indexes/ ) Your integer field is certainly small but my guess is it's not unique, although it's not possible to tell from the question.

To answer the question you could use an IDENTITY(1,1) as your clustered index key, then create a non-clustered index using your Unix time field. To make the queries faster you can INCLUDE commonly used columns in the non-clustered index, then if queries are covered by the index they need not even visit the main table.

Also if your last 3 months data could be defined in terms of particular date ranges, you could even build a filtered index to limit the size of the index and speed the searches. Based on experience with tables of 1m+ rows, if you are in control of the indexing you should be able to satisfy most queries in less than a second.


How to change the owner of a Stored Procedure in DB2?

I believe you are correct on the syntax. A few points to note.

You cannot grant to yourself or revoke from yourself, so someone else may need to grant you the privileges you are after.
You may need the DB level privileges CREATE_EXTERNAL_ROUTINE and/or CREATE_NOT_FENCED_ROUTINE granted to you. May may also need BINDADD granted to you as well. These privileges allow you to create stored procedures.
You do need to be the owner of the stored procedure to do a REPLACE. Otherwise as long as you have CREATIN and DROPIN on the schema, you might be able to DROP and then CREATE the stored procedure.


In shard cluster, if 2 replica set nodes are offline leaving only 1 secondary..where do writes go?

If i have 10 shards, each being a 3 node replica set. If i loose 2 servers in a replica set causing the primary to change to a read-only secondary. Where does the mongos write new chunks to that the hash key would have sent to this shards primary?

Each shard contains a subset of data for the sharded cluster. If the replica set backing a shard becomes read-only you will get exceptions trying to write new data to that shard. The mechanics of failover and high availability for an individual shard are governed by your Replica Set configuration and deployment.

  Does the config server detect the shard being read only and inform the mongos that writes should be redirected to another shard?

No. In order to achieve this, the existing data would have to be migrated to another shard. The config servers and mongos do not monitor whether a shard is read-only or attempt to work around this case. When write operations are directed to that shard, they will generate exceptions.

If you do have an outage for a shard, one of the steps you should take is to disable the balancer. This will prevent attempts to migrate or rebalance data while you work on restoring that shard.

  when the shard gets fixed and has a primary again. Will the chunks be re-balanced to it?

Once the shard has a primary, normal operation can resume. You should also remember to re-enable the balancer if you disabled it during your outage.


It seems that currently PostgreSQL only provides the following

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

The PostgreSQL docs go on to say

  This clause creates the column as an identity column. It will have an implicit sequence attached to it and the column in new rows will automatically have values from the sequence assigned to it.

So it seems it's sequence specific. The SLQ 2011 spec implies there is nothing more to it,

  The declared type of an identity column is either an exact numeric type with scale 0 (zero), INTEGER for example, or a distinct type whose source type is an exact numeric type with scale 0 (zero). An identity column has a start value, an increment, a maximum value, a minimum value, and a cycle option. An identity column is associated with an
  internal sequence generator SG.


SYS.DBMS_INTERNAL_LOGSTDBY causes error on dump import


Nothing is wrong -- you just have to read the documentation. By default, you don't see sleeping SPIDs unless they're blocking another request.

The SPID you're looking at is likely a sleeping session.

Try running

EXEC dbo.sp_WhoIsActive @show_sleeping_spids = 1

More to your problem, you might want to set the database to single user before trying to set it offline.


mysqldump Not Exporting All Rows or mysql Not Importing

Sometimes this can occur because of a combination of a certain sequence of characters and the character set of the MySQL client at the time of the import.

You could have trapped such errors without additional tools by doing the following

mysql -u dbuser -p -f -D thedb < importfile.sql 2>errors_encountered.txt

This would have recorded your trouble spots into errors_encountered.txt

What could have been done with the mysqldump to begin with ???

SIMPLIFY EXPORT CONTENTS

You could have used the --hex-blob options to convert the characters into a hexadecimal representation. This could have eased any character set misinterpretations or misunderstandings on the part of the MySQL client program.

DUMP ONE ROW AT A TIME

Even when using --force for the MySQL client, this can still result in hundreds or thousands of rows not being inserted. Why ?

By default, mysqldump has --opt enabled. This sets the following

  --opt               Same as --add-drop-table, --add-locks, --create-options,
                      --quick, --extended-insert, --lock-tables, --set-charset,
                      and --disable-keys. Enabled by default, disable with
                      --skip-opt.

Notice that one of the options is --extended-insert. This causes mysqldump to set up inserts in chunks of hundreds or thousands of rows at a time.

If just one row in a chunk of rows had an issue with being imported and you are using --force, the entire chunk of rows is not inserted.

How do you get a hold of all the good parts of a chunk. For a mysqldump created with --extended-insert, there is no simple way. What can you do when this happens ? I have good news and bad news.

GOOD NEWS: You have to launch a new mysqldump with --skip-extended-insert. That forces a mysqldump to create an INSERT for every row. That way, when using --force during import, an invalid INSERT due to whenever circumstance will not affect surrounding rows.

BAD NEWS #1 : This makes the resulting mysqldump file much larger.

BAD NEWS #2 : Importing the resulting mysqldump file takes much, much longer.

This is something I recommended Aug 09, 2013 (Backup / Export data from MySQL 5.5 attachments table keeps failing!)

SUGGESTION

To nullify the size problem this creates, you could do the following

gzip the mysqldump while doing mysqldump

nohup mysqldump ... | gzip > importfile.sql.gz &

ungzip the mysqldump and pipe it to mysql for import

gzip -d < importfile.sql.gz | mysql -u dbuser -p -f -D thedb

GIVE IT A TRY !!!


Script to Output Tables to CSV Showing Heading Only Once

You could run two queries - the first pulls the list of field names in tbl1 from user_tables and then the second query then pulls the actual data from tbl1? Spool all to the same file. Don't have a running instance of Oracle to test, but that may be a solution to your issue? Removes the need for PL/SQL - although I fail to see the difference between running a shell script and a simple PL/SQL one.


Creating a UNIQUE constraint from a JSON object

First and foremost: I agree with both the comments of @a_horse_with_no_name and @dezso: you should normalize your data. JSON is not for that.

However, if some  reason I cannot fathom really makes this an advantage, it is possible:

Create an expression based UNIQUE INDEX:

CREATE UNIQUE INDEX people_data_pos_idx ON peoples( (data->>'pos') ) ;

If, at this point, you try to insert the following piece of data into your table (with an already existing ->>pos):

INSERT INTO peoples(data)
VALUES
    ('{"name": "Eve", "pos":"DBA", "age":34}') ;

You get this as a response:

ERROR: duplicate key value violates unique constraint "people_data_pos_idx"
SQL state: 23505
Detail: Key ((data ->> 'pos'::text))=(DBA) already exists.

NOTE: I've assumed that data.pos will always be a string. If you want to generalize, you can use ( (data->'pos') ) instead. You would index then a JSON(B) expression instead of a text. Check JSON Functions and Operators.


Index creation before or after loading data

Adding the index after the table is populated will be faster, perhaps hugely faster.  The only reasons to have the index first are:

Other transactions must not be allowed to see the table without the index (but in this case, try to create the table, populate the table, and create the index all in one transaction)
The index supports a constraint, and you want the constraint enforced during the loading process rather than at the end.
You don't care much about the performance difference, and creating the index upfront is just syntactically easier to do.


RAISERROR on a trigger. What will happen? Will we get informed?

The errors generated by RAISERROR operate the same as errors generated by the Database Engine code. The values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions. When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:

Outside the scope of any TRY block.

With a severity of 10 or lower in a TRY block.

With a severity of 20 or higher that terminates the database connection.

If you are in trigger context, all errors terminate the batch and roll back the transaction on the spot.

When it comes to error handling in SQL Server, no rule is valid without an exception. Errors you raise yourself with RAISERROR do not abort the batch, not even in trigger context

There is one more way that a trigger can terminate the batch. This happens if @@trancount is 0 when the trigger exits. A trigger always executes in the context of a transaction, since even if there is no multi-statement transaction in progress each INSERT, UPDATE and DELETE statement is its own transaction in SQL Server, and the trigger is part of that transaction.
Doc


Create an alias for a SQL Server named instance pointing to sql default instance

My observation is it is necessary to use cliconfg.exe on the application server in order for it to map a specific instance (e.g. '\crazyapp') to the default instance on the remote SQL server.

On the application server, I have set aliases using cliconfg.exe, using both:

C:\Windows\System32\cliconfg.exe (64bit) and
C:\Windows\SysWOW64\cliconfg.exe (32bit)  (thanks, @DaniSQL)

From the application server, I have configured:

I then successfully tested this from the application server, using SSMS to the remote SQL instance.

Creating the alias on the server hosting SQL Server does not work, even if the application server can resolve the SQL Server name via DNS. In order to map from a named instance to a default instance, the alias must be created on the application server.

Thanks to @ypercube for guiding me in the right direction.


How to easily bring 80 MySQL users into ProxySQL?

Since you're using ProxySQL with Percona, there's a nifty tool made by the good people at Percona that does exactly what you're looking for (sync's mysql users to proxysql).

If you install ProxySQL using the Percona RPM, it comes with a nifty script that helps with managing your ProxySQL instance called "proxysql-admin". That script has the option to "--syncusers". Here's an excerpt of the help info for the script:

[root@localhost ~]# /usr/bin/proxysql-admin
Usage: [ options ]
Options:
  --config-file=<config-file>        Read login credentials from a configuration file (overrides any login credentials specified on the command line)
  --quick-demo                       Setup a quick demo with no authentication
  --proxysql-datadir=<datadir>       Specify proxysql data directory location
  --proxysql-username=user_name      Username for connecting to the ProxySQL service
  --proxysql-password[=password]     Password for connecting to the ProxySQL service
  --proxysql-port=port_num           Port Nr. for connecting to the ProxySQL service
  --proxysql-hostname=host_name      Hostname for connecting to the ProxySQL service
  --cluster-username=user_name       Username for connecting to the Percona XtraDB Cluster node
  --cluster-password[=password]      Password for connecting to the Percona XtraDB Cluster node
  --cluster-port=port_num            Port Nr. for connecting to the Percona XtraDB Cluster node
  --cluster-hostname=host_name       Hostname for connecting to the Percona XtraDB Cluster node
  --cluster-app-username=user_name   Application username for connecting to the Percona XtraDB Cluster node
  --cluster-app-password[=password]  Application password for connecting to the Percona XtraDB Cluster node
  --without-cluster-app-user         Configure Percona XtraDB Cluster without application user
  --monitor-username=user_name       Username for monitoring Percona XtraDB Cluster nodes through ProxySQL
  --monitor-password[=password]      Password for monitoring Percona XtraDB Cluster nodes through ProxySQL
  --without-check-monitor-user       Configure ProxySQL without checking/attempting to create monitor user
  --enable, -e                       Auto-configure Percona XtraDB Cluster nodes into ProxySQL
  --disable, -d                      Remove any Percona XtraDB Cluster configurations from ProxySQL
  --node-check-interval=3000         Interval for monitoring node checker script (in milliseconds)
  --mode=[loadbal|singlewrite]       ProxySQL read/write configuration mode, currently supporting: 'loadbal' and 'singlewrite' (the default) modes
  --write-node=host_name:port        Writer node to accept write statments. This option is supported only when using --mode=singlewrite
                                     Can accept comma delimited list with the first listed being the highest priority.
  --include-slaves=host_name:port    Add specified slave node(s) to ProxySQL, these nodes will go into the reader hostgroup and will only be put into
                                     the writer hostgroup if all cluster nodes are down.  Slaves must be read only.  Can accept comma delimited list.
                                     If this is used make sure 'read_only=1' is in the slave's my.cnf
  --adduser                          Adds the Percona XtraDB Cluster application user to the ProxySQL database
  --syncusers                        Sync user accounts currently configured in MySQL to ProxySQL (deletes ProxySQL users not in MySQL)
  --version, -v                      Print version info
[root@localhost ~]#

Notice the "--syncusers" option down there at the bottom. That's what you're looking for, yes? ... and it's made and distributed by Percona. :)

Hope this helps!


how to do select more then 3 table add in select IN() function

Hope It will Help.

SELECT M.post_id, M.uid_fk, M.message, M.image1,M.roles, M.created,U.uname, U.f_name, U.l_name
 FROM posts M, person U
 WHERE M.uid_fk=U.p_id
    AND M.uid_fk in ( SELECT id FROM (
      (select send_id as id from friend_request where p_id = '7' AND status=1)
        UNION ALL
     (select p_id as id from friend_request where send_id = '7' AND status=1)
        UNION ALL
     (select send_id as id from follower_request where p_id = '7' AND status=1)
 ) as ids

  ) AND M.uid_fk = 7
 order by M.post_id DESC


How do I open an ERwin Naming Standards file (.nsm) in Excel?

First, you need to load the .nsm file into the model. This is done through Tools > Names > Model Naming Options... > Use File: (select the appropriate file).

Well, if you've got version R9 or higher, you're in luck. As per this post, the naming standards are an object in the model explorer just like every other entity, so you can simply find it in the model explorer, right click on it, select properties, and on the glossary tab click the save button, and save as .csv.

For pre-R9 versions, you need to go to Tools > Names > Edit Naming Standards... The Naming Standards Editor window should pop up. There's a few tabs near the bottom, click the one that says 'glossary'. You'll now have an option to export the file as .csv.

Took me way too long to figure this out myself, so hopefully this could help the next guy who has the same issue. ;)


Are SqlServer Sequences, guaranteed to return different values for concurrent transactions?

Hence, in my case, there is no duplicate checking while inserting.

If you really do not intend to allow duplicate values, you should create a UNIQUE constraint on the column where the ciphered text is to be stored.  Your application will have to be coded to retry on inserts that fail with a unique constraint error.

  The thing I need to know is that whether it is probable for two clients calling NEXT VALUE FOR function nearly at the same time, and get the same values?

While it's unlikely, under normal circumstances, to get the same value on two separate calls to NEXT VALUE FOR from the same sequence, there are things that can cause it to happen:

the sequence was specified with the CYCLE option, and the maximum value has been reached (causing it to roll over)
the sequence was reset (ALTER SEQUENCE RESTART)
as documented, accessing the same sequence object multiple times in the same statement will produce the same value each time, so make sure not to do something like this expecting two different numbers:

SELECT
    NEXT VALUE FOR dbo.TestSequence,
    NEXT VALUE FOR dbo.TestSequence;

You may also be interested in reading Sequence Tables, by Paul White.


Is it Important to Understand Each Normal Form?

Codd formulated the normal forms as a tool to help evaluate your data model for correctness:

  "To free the collection of relations from undesirable insertion,
  update and deletion dependencies; To reduce the need for restructuring
  the collection of relations, as new types of data are introduced, and
  thus increase the life span of application programs; To make the
  relational model more informative to users; To make the collection of
  relations neutral to the query statistics, where these statistics are
  liable to change as time goes by."

You are absolutely correct that if you design your data model and the resulting schema very well to begin with, it will already be in at least 3rd normal form.

That said, I believe that it is crucial to understand the relational model, and the normalization rules as helping guides. When you say:

  "I can accomplish this easily by arranging the data so that the
  columns in each table, other than the primary key, are dependent only
  on the whole primary key."

You are using the 3rd normal form.
A relation in 3NF, must also be in 2NF and 1NF.

You are asking if you need tools to validate your design, which is something no one can answer.
If you design a car, will you use available tools to validate that it is safe, fast, and reliable? or will you just trust that your original design achieved these goals without testing it with any tools?


Error 18456 error severity 14 state 5 SQL Server 2012 SCCM2012 SP1

When you say you did a database rebuild command, do you mean you rebuilt the system databases for sql server & changed the default collation?  Doing this means you've effectively created a new database server.  I found these instructions for moving the SCCM db from one server to another.  Looks like they're using a db backup/restore & you've attached the db, but everything else should be the same.  There is a step in the ConfigMgr setup that will fix up the database permissions for you, have you tried that?


EN Express, no tablespace selection under storage

This happens when you log in to the root container of a CDB, because tablespaces are local to PDBs.

Configure EM Express at PDB level, for example:

connect / as sysdba
alter session set container=pdb1;
exec dbms_xdb_config.sethttpsport(5501);

Then log in to EM Express using port 5501.

PDB Tablespace is Not Showing in CDB 12c EM Express page (Doc ID 2278612.1)


Why does DELETE leave a lingering effect on performance?

This difference only seems to apply when the object is a B+tree. When removing the primary key on the table variable so it is a heap I got the following results

2560
2120
2080
2130
2140

But with the PK I found a similar pattern in my tests as well with typical results below.

+--------+--------+---------+-------------------+
| @table | #table | ##table | [permanent_table] |
+--------+--------+---------+-------------------+
|   2670 |   2683 |    9603 |              9703 |
|   6823 |   6840 |    9723 |              9790 |
|   6813 |   6816 |    9626 |              9703 |
|   6883 |   6816 |    9600 |              9716 |
|   6840 |   6856 |    9610 |              9673 |
+--------+--------+---------+-------------------+

My theory is that there there is some optimisation available when doing bulk inserts to local temporary B+ trees that only applies when it does not already have any pages allocated.

I base this on the following observations.

When running various versions of your test code I only saw this pattern  with @table_variables and #temp tables. Not permanent tables in tempdb nor ## tables.
In order to get the slower performance it is not necessary to have previously added and removed a large amount of rows from the table. Simply adding a single row and leaving it in there is sufficient.
TRUNCATE deallocates all pages from the table. DELETE will not cause the last page in the table to be deallocated.
Using the VS 2012 profiler shows that in the faster case SQL Server uses a different code path. 36% of the time is spent in sqlmin.dll!RowsetBulk::InsertRow vs 61% of the time spent in sqlmin.dll!RowsetNewSS::InsertRow for the slower case.

Running

SELECT *
FROM sys.dm_db_index_physical_stats(2,OBJECT_ID('tempdb..#values'),1,NULL, 'DETAILED')

after the delete returns

+-------------+------------+--------------+--------------------+
| index_level | page_count | record_count | ghost_record_count |
+-------------+------------+--------------+--------------------+
|           0 |          1 |            0 |                  1 |
|           1 |          1 |            1 |                  0 |
|           2 |          1 |            1 |                  0 |
+-------------+------------+--------------+--------------------+

I found that it was possible to reduce the time discrepancy somewhat by enabling trace flag 610.

This had the effect of reducing the quantity of logging substantially for the subsequent inserts (down from 350 MB to 103 MB as it no longer logs the individual inserted row values) but this had only a minor improvement in timings for the 2nd and subsequent @table , #table cases and the gap still remains. The trace flag improved the general performance of inserts to the other two table types significantly.

+--------+--------+---------+-------------------+
| @table | #table | ##table | [permanent_table] |
+--------+--------+---------+-------------------+
|   2663 |   2670 |    5403 |              5426 |
|   5390 |   5396 |    5410 |              5403 |
|   5373 |   5390 |    5410 |              5403 |
|   5393 |   5410 |    5406 |              5433 |
|   5386 |   5396 |    5390 |              5420 |
+--------+--------+---------+-------------------+

From looking in the transaction log I noticed that the initial inserts against empty local temporary tables seem even more minimally logged (at 96 MB).

Notably these faster inserts had only 657 transactions (LOP_BEGIN_XACT /LOP_COMMIT_XACT pairs) compared with over 10,000 in the slower cases. In particular LOP_FORMAT_PAGE operations seem much reduced. The slower cases have a transaction log entry for this for each page in the table (about 10,270) compared with only 4 such entries in the fast case.

The log used in all three cases was as follows (I have deleted the log records for updates to system base tables to reduce the amount of text but they are still included in the totals)

Logging first insert against @table_var (96.5 MB)

+-----------------------+----------+----------------------------------------------+---------------+---------+
|       Operation       | Context  |                AllocUnitName                 | Size in Bytes |   Cnt   |
+-----------------------+----------+----------------------------------------------+---------------+---------+
| LOP_BEGIN_XACT        | LCX_NULL | NULL                                         |         83876 |     658 |
| LOP_COMMIT_XACT       | LCX_NULL | NULL                                         |         34164 |     657 |
| LOP_CREATE_ALLOCCHAIN | LCX_NULL | NULL                                         |           120 |       3 |
| LOP_FORMAT_PAGE       | LCX_HEAP | dbo.#531856C7                                |            84 |       1 |
| LOP_FORMAT_PAGE       | LCX_IAM  | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |            84 |       1 |
| LOP_FORMAT_PAGE       | LCX_IAM  | dbo.#531856C7                                |            84 |       1 |
| LOP_FORMAT_PAGE       | LCX_IAM  | Unknown Alloc Unit                           |            84 |       1 |
| LOP_HOBT_DDL          | LCX_NULL | NULL                                         |           216 |       6 |
| LOP_HOBT_DELTA        | LCX_NULL | NULL                                         |           320 |       5 |
| LOP_IDENT_NEWVAL      | LCX_NULL | NULL                                         |     100240000 | 2506000 |
| LOP_INSERT_ROWS       | LCX_HEAP | dbo.#531856C7                                |            72 |       1 |
| LOP_MODIFY_ROW        | LCX_IAM  | dbo.#531856C7                                |            88 |       1 |
| LOP_MODIFY_ROW        | LCX_PFS  | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |        158592 |    1848 |
| LOP_MODIFY_ROW        | LCX_PFS  | dbo.#531856C7                                |            80 |       1 |
| LOP_MODIFY_ROW        | LCX_PFS  | Unknown Alloc Unit                           |        216016 |    2455 |
| LOP_SET_BITS          | LCX_GAM  | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |         84360 |    1406 |
| LOP_SET_BITS          | LCX_GAM  | Unknown Alloc Unit                           |        147120 |    2452 |
| LOP_SET_BITS          | LCX_IAM  | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |         84360 |    1406 |
| LOP_SET_BITS          | LCX_IAM  | Unknown Alloc Unit                           |        147120 |    2452 |
| Total                 | NULL     | NULL                                         |     101209792 | 2519475 |
+-----------------------+----------+----------------------------------------------+---------------+---------+

Logging subsequent inserts TF 610 off (350 MB)

+-----------------------+--------------------+----------------------------------------------+---------------+---------+
|       Operation       |      Context       |                AllocUnitName                 | Size in Bytes |   Cnt   |
+-----------------------+--------------------+----------------------------------------------+---------------+---------+
| LOP_BEGIN_CKPT        | LCX_NULL           | NULL                                         |            96 |       1 |
| LOP_BEGIN_XACT        | LCX_NULL           | NULL                                         |       1520696 |   12521 |
| LOP_COMMIT_XACT       | LCX_NULL           | NULL                                         |        651040 |   12520 |
| LOP_CREATE_ALLOCCHAIN | LCX_NULL           | NULL                                         |            40 |       1 |
| LOP_DELETE_SPLIT      | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |          2160 |      36 |
| LOP_END_CKPT          | LCX_NULL           | NULL                                         |           136 |       1 |
| LOP_FORMAT_PAGE       | LCX_HEAP           | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |        859236 |   10229 |
| LOP_FORMAT_PAGE       | LCX_IAM            | Unknown Alloc Unit                           |            84 |       1 |
| LOP_FORMAT_PAGE       | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |          3108 |      37 |
| LOP_HOBT_DDL          | LCX_NULL           | NULL                                         |           648 |      18 |
| LOP_HOBT_DELTA        | LCX_NULL           | NULL                                         |        657088 |   10267 |
| LOP_IDENT_NEWVAL      | LCX_NULL           | NULL                                         |     100239960 | 2505999 |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |     258628000 | 2506000 |
| LOP_INSERT_ROWS       | LCX_HEAP           | dbo.#531856C7                                |            72 |       1 |
| LOP_INSERT_ROWS       | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |       1042776 |   10302 |
| LOP_MODIFY_HEADER     | LCX_HEAP           | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |        859236 |   10229 |
| LOP_MODIFY_HEADER     | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |          3192 |      38 |
| LOP_MODIFY_ROW        | LCX_IAM            | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |           704 |       8 |
| LOP_MODIFY_ROW        | LCX_PFS            | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |        934264 |   11550 |
| LOP_MODIFY_ROW        | LCX_PFS            | Unknown Alloc Unit                           |        783984 |    8909 |
| LOP_SET_BITS          | LCX_GAM            | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |         76980 |    1283 |
| LOP_SET_BITS          | LCX_GAM            | Unknown Alloc Unit                           |        534480 |    8908 |
| LOP_SET_BITS          | LCX_IAM            | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 |         76980 |    1283 |
| LOP_SET_BITS          | LCX_IAM            | Unknown Alloc Unit                           |        534480 |    8908 |
| LOP_SHRINK_NOOP       | LCX_NULL           | NULL                                         |            32 |       1 |
| LOP_XACT_CKPT         | LCX_NULL           | NULL                                         |            92 |       1 |
| Total                 | NULL               | NULL                                         |     367438748 | 5119297 |
+-----------------------+--------------------+----------------------------------------------+---------------+---------+

Logging subsequent inserts TF 610 on (103 MB)

+-------------------------+-------------------------+----------------------------------------------+---------------+---------+
|        Operation        |         Context         |                AllocUnitName                 | Size in Bytes |   Cnt   |
+-------------------------+-------------------------+----------------------------------------------+---------------+---------+
| LOP_BEGIN_CKPT          | LCX_NULL                | NULL                                         |           192 |       2 |
| LOP_BEGIN_XACT          | LCX_NULL                | NULL                                         |       1339796 |   11099 |
| LOP_BULK_EXT_ALLOCATION | LCX_NULL                | NULL                                         |         20616 |     162 |
| LOP_COMMIT_XACT         | LCX_NULL                | NULL                                         |        577096 |   11098 |
| LOP_CREATE_ALLOCCHAIN   | LCX_NULL                | NULL                                         |            40 |       1 |
| LOP_DELETE_SPLIT        | LCX_INDEX_INTERIOR      | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |          2160 |      36 |
| LOP_END_CKPT            | LCX_NULL                | NULL                                         |           272 |       2 |
| LOP_FORMAT_PAGE         | LCX_BULK_OPERATION_PAGE | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |        863520 |   10280 |
| LOP_FORMAT_PAGE         | LCX_IAM                 | Unknown Alloc Unit                           |            84 |       1 |
| LOP_FORMAT_PAGE         | LCX_INDEX_INTERIOR      | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |          3108 |      37 |
| LOP_HOBT_DELTA          | LCX_NULL                | NULL                                         |        666496 |   10414 |
| LOP_IDENT_NEWVAL        | LCX_NULL                | NULL                                         |     100239960 | 2505999 |
| LOP_INSERT_ROWS         | LCX_CLUSTERED           | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |         23544 |     218 |
| LOP_INSERT_ROWS         | LCX_HEAP                | dbo.#719CDDE7                                |            72 |       1 |
| LOP_INSERT_ROWS         | LCX_INDEX_INTERIOR      | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |       1042776 |   10302 |
| LOP_MODIFY_HEADER       | LCX_BULK_OPERATION_PAGE | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |        780216 |   10266 |
| LOP_MODIFY_HEADER       | LCX_HEAP                | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |       1718472 |   20458 |
| LOP_MODIFY_HEADER       | LCX_INDEX_INTERIOR      | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |          3192 |      38 |
| LOP_MODIFY_ROW          | LCX_IAM                 | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |           704 |       8 |
| LOP_MODIFY_ROW          | LCX_PFS                 | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |        114832 |    1307 |
| LOP_MODIFY_ROW          | LCX_PFS                 | Unknown Alloc Unit                           |        231696 |    2633 |
| LOP_RANGE_INSERT        | LCX_NULL                | NULL                                         |            48 |       1 |
| LOP_SET_BITS            | LCX_GAM                 | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |         77100 |    1285 |
| LOP_SET_BITS            | LCX_GAM                 | Unknown Alloc Unit                           |        157920 |    2632 |
| LOP_SET_BITS            | LCX_IAM                 | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 |         77100 |    1285 |
| LOP_SET_BITS            | LCX_IAM                 | Unknown Alloc Unit                           |        157920 |    2632 |
| LOP_XACT_CKPT           | LCX_NULL                | NULL                                         |            92 |       1 |
| Total                   | NULL                    | NULL                                         |     108102960 | 2602218 |
+-------------------------+-------------------------+----------------------------------------------+---------------+---------+


How mongoDB projection affects performance?

By default, queries return all fields in matching documents. If you need all the fields, returning full documents is going to be more efficient than having the server manipulate the result set with projection criteria.

However, using projection to limit fields to return from query results can improve performance by:

removing unneeded fields from query results (saving on network bandwidth)
limiting result fields to achieve a covered query (returning indexed query results without fetching full documents)

When using projection to remove unused fields, the MongoDB server will have to fetch each full document into memory (if it isn't already there) and filter the results to return. This use of projection doesn't reduce the memory usage or working set on the MongoDB server, but can save significant network bandwidth for query results depending on your data model and the fields projected.

A covered query is a special case where all requested fields in a query result are included in the index used, so the server does not have to fetch the full document. Covered queries can improve performance (by avoiding fetching documents) and memory usage (if other queries don't require fetching the same document).

Examples

For demonstration purposes via the mongo shell, imagine you have a document that looks like this:

db.data.insert({
    a: 'webscale',
    b: new Array(10*1024*1024).join('z')
})

The field b might represent a selection of values (or in this case a very long string).

Next, create an index on {a:1} which is a commonly used field queried by your use case:

db.data.createIndex({a:1})

A simple findOne() with no projection criteria returns a query result which is about 10MB:

> bsonsize(db.data.findOne({}))
10485805

Adding the projection {a:1} will limit the output to the field a and the document _id (which is included by default). The MongoDB server is still manipulating a 10MB document to select two fields, but the query result is now only 33 bytes:

> bsonsize(db.data.findOne({}, {a:1}))
33

This query isn't covered because the full document has to be fetched to discover the _id value. The _id field is included in query results by default since it is the unique identifier for a document, but _id won't be included in a secondary index unless explicitly added.

The totalDocsExamined and totalKeysExamined metrics in explain() results will show how many documents and index keys were examined:

 > db.data.find(
     {a:'webscale'},
     {a:1}
 ).explain('executionStats').executionStats.totalDocsExamined
 > 1

This query can be improved using projection to exclude the _id field and achieve a covered query using only the {a:1} index. The covered query no longer needs to fetch a ~10MB document into memory, so will be efficient in both network and memory usage:

 > db.data.find(
     {a:'webscale'},
     {a:1, _id:0}
 ).explain('executionStats').executionStats.totalDocsExamined
 0

 > bsonsize(db.data.findOne( {a:'webscale'},{a:1, _id:0}))
 21

  I have slow MongoDB queries. Is returning a subset affect my slow query (I have compound index on the field)?

This isn't answerable without the context of a specific query, example document, and the full explain output. However, you could run some benchmarks in your own environment for the same query with and without projection to compare the outcome. If your projection is adding significant overhead to the overall query execution time (processing and transferring results), this may be a strong hint that your data model could be improved.

If it's not clear why a query is slow, it would be best to post a new question with specific details to investigate.


SQL 2012 Autogrow and file extension problem

Can the extensions be changed back without any conflict?

Not while the database is active, but you can offline it, rename the files, then bring it back online:

Check the virtual filenames as you'll need to refer to them in a later step:
SELECT name, physical_name, type_desc FROM my_db.sys.database_files
Offline the database:
ALTER DATABASE my_db SET OFFLINE WITH ROLLBACK IMMEDIATE;
(note: the above will kick out other connections and active transactions, if you want to be a little safer to other potential users remove the "WITH ROLLBACK IMMEDIATE" and it will instead fail if there are other open connections)
Rename the files in the filesystem
Let SQL server know what you have done:
ALTER DATABASE my_db MODIFY FILE (NAME='data_file_name', FILENAME='d:\dbs\data\my_db.mdf');
ALTER DATABASE my_db MODIFY FILE (NAME='log_file_name', FILENAME='d:\dbs\data\my_db.ldf');
Bring the database back online:
ALTER DATABASE my_db SET ONLINE;

  I was wondering if anyone has seen this before

I've seen it. It is most likely that someone created the database by hand (rather than using SSMS or other tools), specified file locations (or just names) different to the defaults, but got the filenames slightly wrong. SQL Server would not have complained at all because is doesn't actually care what the filenames are, they could be named "B12746289863413584F2EFC7A9A35815" and "D040839B958143C890A760B5A489E944" and SQL Server would be just as happy to use them as instructed, the mdf/ndf/ldf and having meaningful names generally conventions are just to give us humans a visual clue as to the purpose of a given file.

  what the best way to resolve it is.

As it doesn't upset SQL Server I would be tempted to say "just leave it alone" for production databases. For databases used for development/QA/UAT/other then either the above or alternately detach and re-attach the database instead of offlining it.


Enforcing option (c), using a type attribute:

CREATE TABLE Computer
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType IN ('D', 'L'))
) ;

CREATE TABLE Laptop
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, battery WHATEVER
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType = 'L')
, FOREIGN KEY (computerType, computerID)
    REFERENCES Computer (computerType, computerID)
) ;

CREATE TABLE Desktop
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, monitor WHATEVER
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType = 'D')
, FOREIGN KEY (computerType, computerID)
    REFERENCES Computer (computerType, computerID)
) ;

and using deferred foreign key constraints on nullable columns:

CREATE TABLE Computer
( computerID INT NOT NULL
, laptopID INT NULL                                    -- these 2 columns
, desktopID INT NULL                                   -- are NULLable
, PRIMARY KEY (computerID)
, UNIQUE (laptopID)
, UNIQUE (desktopID)
, CHECK ( laptopID IS NOT NULL AND desktopID IS NULL   -- but only one of them
       OR desktopID IS NOT NULL AND laptopID IS NULL   -- is NULL
        )
, CHECK (laptopID = computerID)                        -- and the non-NULL one is
, CHECK (desktopID = computerID)                       -- equal to computerID
) ;

CREATE TABLE Laptop
( laptopID INT NOT NULL
, battery VARCHAR(20) NOT NULL
, PRIMARY KEY (laptopID)
, FOREIGN KEY (laptopID)
    REFERENCES Computer (computerID)
) ;

CREATE TABLE Desktop
( desktopID INT NOT NULL
, monitor VARCHAR(20) NOT NULL
, PRIMARY KEY (desktopID)
, FOREIGN KEY (desktopID)
    REFERENCES Computer (computerID)
) ;

ALTER TABLE Computer
  ADD CONSTRAINT Laptop_Computer_FK
  FOREIGN KEY (laptopID)
    REFERENCES Laptop (laptopID)
    DEFERRABLE INITIALLY DEFERRED
, ADD CONSTRAINT Desktop_Computer_FK
  FOREIGN KEY (desktopID)
    REFERENCES Desktop (desktopID)
    DEFERRABLE INITIALLY DEFERRED ;

Tested in Postgres, at SQL-Fiddle


--this is postgres syntax, but similar for mysql
--using single table inheritance for simplicity

--organizations (o) and individuals (i) go here:

create table parties (
  party_id int primary key,
  type char(1) not null check (type in ('i', 'o') ), --use a lookup table in real life
  full_legal_name varchar(255) not null
);

--parties that play the role of vendor (v) or buyer (b) go here. parties can play many roles:

create table party_roles (
  party_id int references parties(party_id),
  type char(1) check ( type in ('v', 'b') ), --use a lookup table in real life

  --add extra role info here

  primary key (party_id, type)
);

--sales orders go here. foreign keys ensure vendor is a vendor role, etc

create table sales_orders (
  order_id int primary key,
  order_date timestamptz not null default current_timestamp,
  vendor_party_id int not null,
  vendor_type char(1) not null default 'v',
  buyer_party_id int not null,
  buyer_type char(1) not null default 'b',

  foreign key (vendor_party_id, vendor_type) references party_roles(party_id, type),

  foreign key (buyer_party_id, buyer_type) references party_roles(party_id, type)
);

--create a company that plays the role of a vendor:
begin;
insert into parties values (1, 'o', 'Acme, Inc.');
insert into party_roles values (1, 'v');
commit;

--create a company that plays the role of a buyer:
begin;
insert into parties values (2, 'o', 'Globalcorp.biz');
insert into party_roles values (2, 'b');
commit;

--create a sales order, vendor is acme, buyer is globalcorp:
insert into sales_orders (order_id, vendor_party_id, buyer_party_id) values (1, 1, 2);

Fiddle with it here: http://sqlfiddle.com/#!15/a76fd/2


There are three (well, actually four...) ways to execute export in DB2.

There is the db2export API (.sh in Unix/Linux, .exe in Windows). This can be executed from anything that understands command shell.

There is the export command to the db2 command. This can be run from a DB2 interactive session, or from the command shell.

There is also the export command through the ADMIN_CMD stored procedure. This allows you to invoke the export from a stored procedure which is accessable through anything that can execute SQL against DB2. This is probably the option you are looking for if you want to do export via SQL through another tool.

There is also the db2move utility which can perform an export, but that is done via command shell as well.


Does dropping a Primary Key constraint from a Partitioned Table break the partition?

It shouldn't affect the table's partitioning, as the table was created on the partition scheme with the partitioning column specified.

Each partition has it's own HOBT (Heap or B-Tree). So essentially you have a HOBT for each Date in your table.

If the primary key is also a clustered index, you will be converting each partitions' B-Tree to a Heap. Depending upon the size of the table, moving the data from the B-Tree to the Heap could take a considerable amount of time and resources. It's recommended to drop all nonclustereds before attempting to drop the clustered index, then recreating them after (or disable / rebuild). Here's a detailed explanation from Microsoft:

"When a clustered index is dropped, the data rows that were stored in the leaf level of the clustered index are stored in an unordered table (heap). Dropping a clustered index can take time because in addition to dropping the clustered index, all nonclustered indexes on the table must be rebuilt to replace the clustered index keys with row pointers to the heap. When you drop all indexes on a table, drop the nonclustered indexes first and the clustered index last."

http://msdn.microsoft.com/en-us/library/ms190691%28v=SQL.90%29.aspx

I won't comment on query performance implications of doing this, as I'm not familiar with how the table is queried.

As with any major change, make sure to try this in the test environment before attempting to make this change.


Are SQL Server in-place upgrades as ill advised as they used to be?

Really Short Answer - In Place is okay. You can review your configuration afterwards and implement the best practices for SQL Server 2012.

A Longer Answer on SQL Server Upgrades/Migrations

So this is an opinion thing and there isn't a necessarily wrong or right answer but I prefer migration style upgrades over in-place for a lot of reasons. That being said - some of my clients for various reasons have had no choice but to do an in-place and really since SQL Server 2005, in-place upgrades haven't been as bad as they used to be.

Why I Prefer A Migration to an In-Place Upgrade

Easier Rollback - If something goes wrong you can rollback by simple saying "we aborted the upgrade.. Please change connection strings to old server while we resolve this". With an in-place you are fixing it or you are down.
Refresh Hardware - Hardware changes rapidly. You can easily get stuck on hardware that was right for your company 4 years ago but not right for today and the next four years with an in-place upgrade. You will likely have to do a migration at some point anyway for the new hardware.
Feel Better - Sure... This one is subjective, but it feels good knowing you are starting with a new OS installation, a new SQL installation with no cobwebs from the person on the job before you (or you before you knew what you knew today) that could possibly cause you headaches in the future.
New OS - A migration gives you a chance to start with a new OS version if you aren't on the latest and greatest today.
You Can Test It - Ever want to get a set of baselines on a new machine before you install SQL and cloud it up with databases and usage? You can do that now.
Sometimes it is easier to sneak in best practices - Maybe the SQL Server service account was a local admin. Maybe Builtin Administrators is in the SA server role. Maybe things have been sort of hacked together to make it work before. You can fix that all and start fresh.
Free test environment and extra sleep - It is a great benefit to have an environment you can work in ahead of the actual cutover day when you make this new environment live. Doing a migration to a new environment means you can build it during business hours, well ahead of your actual cutover day and test it in many ways ahead of time. You can run full regression testing on all applications and systems for days and have a great peace of mind before you actually do the final set of restores/attaches and cutover all applications and access to the new environment.
You don't have to do it all at once - A very common situation I bump into is an environment that is trying to consolidate to just a few instances. Perhaps one per version, perhaps one per "tier" and version. A lot of these projects have different timelines for various applications and databases based on testing, project plans and vendor certification timeliness. Doing the migration means you can move those databases that are ready, when they are ready and still handle requests for those databases that can't move for one reason or another.

Mind you I am not saying you have to do this as a migration. In-Place works and it works well if you are not planning on the buying new hardware in your budget and can't do that for this upgrade. The support in the upgrade process is so much better than it was in the 6.5 days so you are not putting yourself in a bad position doing this.

If you do plan on doing in-place for dev/test but want to do a migration for production you might consider doing at least one migration before production. This way you can work out your checklist ahead of time and deal with any potential issues that you weren't thinking of.

Attach/Detach vs. Backup/Restore for Migrations

If you decide to go with the migration approach, there is still one more decision you may still have a debate over and that is how you move your database to the new environment. You can either detach your database from the old server and attach it to the new or back it up and restore it there.

I prefer backup/restore. The biggest advantage I hear about detach/attach is that it saves some time. For me backup/restore wins for a few reasons:

Keep the old accessible - This allows you to still have an accessible database on the source server. detach/attach should do the same, but it will require a few steps and there is room for human error with detach/attach that could complicate this.
You are guaranteeing that you have a backup - Instead of just taking a database from a detach and potentially forgetting a backup step, you've made sure that you've taken that backup.
Human error - If you delete the wrong file, forget where you are sending something or otherwise mess up your steps, you risk much by moving the data and log files around for your database. Now you can mitigate this by copying instead of cutting (and if you do detach, you should get out of the cut and paste habit) but you could stil mess up. SQL Server is no longer locking those files and it's just too easy to delete a file accidentally for me to risk it.
It isn't really that slower - Taking a backup and copying it is a bit more time, but it isn't that much that I am willing to pay the extra risk for it. In fact - using full recovery model and log backups, you can take the downtime down even lower for cutovers as described below in "How to Make the Migration Approach Faster"

If you do decide to do the backup/restore - that means your old source database will still be online. I like to bring that database offline after taking the backup. I sometimes go a step further and take the whole SQL instance offline after I've scripted out security, jobs, linked server, certificates, database mail settings, and other instance wide information. This avoids an issue during testing where someone says "Everything looks great!" only to realize a day or two later that they have been talking to the old database on the old server. Taking those databases offline or the whole instance offline allows you to prevent those false positives and the mess they make.

How to Make the Migration Approach Faster

You can minimize the downtime required for the cutover from an old to new environment for a busy production environment with little downtime by utilizing the full recovery model. Basically - stage the environment you are migrating to by restoring the latest full backup, any differential backups and any already taken log backups specifying NORECOVERY - then all you will have to do for the final cut over is restore the log backups that were not yet restored and the final log backup you wish to restore specifying WITH RECOVERY. This way for a large database, the actual cutover downtime window can be drastically minimized by paying for the cost of the full, differential and most log restores ahead of the downtime window. Thanks to Tao for pointing this out in the comments!

How to Make the In-Place Upgrade Safer

A few things you can do to improve your experience and results when choosing the in-place approach.

Backup - Take appropriate backups of all user and system databases of your environment ahead of time and ensure they are good (I'm paranoid.. I actually would restore them someplace first to really know they are good.. May be wasting your time.. But you may thank yourself in the event of a disaster).. Script out any configuration information about the SQL and OS installation at that environment.
Test things well before you start - Verify that you have a good environment and good databases. You should be doing things like looking at the error logs and running DBCC CHECKDB on a regular basis, but before doing an in-place upgrade is a great time to start. Fix any issues ahead of time.
Ensure OS health - Don't just make sure that SQL is healthy, make sure your server is healthy. Any gnarly errors in your system or application error event logs? How is your free space?
Prepare for the worst - I had a blog post series awhile ago that went on the premise that if you aren't preparing for failure - you are actually preparing to fail.. I still believe that. So think through the issues you may have and deal with them accordingly ahead of time. Get yourself in the "failure" mindset and you will think of things that you wouldn't have otherwise.

The Importance of Upgrade or Migration Checklists

If you do decide to do an upgrade (whether in place or migration) you should seriously consider creating a checklist and using this checklist in each environment. You should include a bunch of things in this checklist, not the least of which:

At the Start - Do some things like perform a test upgrade, test your applications on the latest Database Compatibility level and consider running a tool like the SQL Server Upgrade Advisor ahead of time to see what sort of tasks you need to complete before doing the SQL Server upgrade or migration.
Pre Steps - Cleanup, OS tasks, patching ahead of time, preparing applications for the upgrade (clean shutdowns, connection string work), backups, etc.
Upgrade/Migration Steps - Everything you have to do for the upgrade or migration to succeed and in the right order. Installation, changing (or not changing depending on your testing and approach) compatibility mode changes to databases, etc.
Post Migration/Upgrade Steps - Various tests, post new version or new server configuration options, best practice implementation, security changes, etc.
Rollback Steps - All along the way you should have rollback steps and milestones. If you get this far and this happens, what will you do? What are "do a full rollback" criteria? And how do you do that rollback (reverse connection string changes, change back settings, go back to the old version, reinstall if an in place, point back to the old server if a migration, etc.)

And then have the person who will be doing the production upgrade follow the checklist in some environment other than production - especially one that closes resembles production if possible ("South of prod", as I say...) and note any issues or points where they had to divert from the checklist or improvise because of a lack in the checklist. Then get the changes merged in and have fun with your production change.

I can not over stress the importance of testing thoroughly post migration or upgrade and ahead of your migration enough. Making a rollback decision in the midst of an upgrade should be an easy one - especially during a migration. If there is something uncomfortable, rollback and figure it out if you can't troubleshoot it effectively and reliably in the heat of the migration. Once you are live on this new environment and users connect - rollback becomes a difficult task. You can't restore a SQL Server database to an earlier version. That means manual work and data migrations. I always wait a couple weeks to kill the old environment but you should do all you can to avoid needing that old environment by finding all of your issues before your live users ever touch the new environment. Preferably before you even kick off the upgrade/migration.

Quick note about SQL Server Reporting Services migration/upgrade
Migrating an SSRS installation isn't quite a herculean task that many think it is. This technet/books online article is actually quite handy.  One of the most important admonitions in that article is "Back up the encryption keys" especially if you have a lot of saved sensitive information like scheduled report e-mail recipient e-mail addresses, connection information for a multitude of connections, etc. You can ask one of my clients from awhile back how important that is. They know because I messed that step up and spent quite a lot of time modifying report schedules and connection string permissions.


How to update Different Date formats to one specific datetime formate

You haven't mentioned an RDBMS, so assuming SQL Server, you need to use CONVERT and a style definition to interpret and convert the string into DATETIME:

DECLARE @Table TABLE (ID INT IDENTITY, Country VARCHAR(255), DateString VARCHAR(25))

INSERT INTO @Table
VALUES ('USA', '02/13/2018'),
    ('Germany', '02.12.2018'),
    ('England', '23/02/2011'),
    ('UAE', '08 Jun 2008')

SELECT *,
    CASE
        WHEN [Country] = 'USA' THEN CONVERT(DATETIME, [DateString], 101)
        WHEN [Country] = 'Germany' THEN CONVERT(DATETIME, [DateString], 103)
        WHEN [Country] = 'England' THEN CONVERT(DATETIME, [DateString], 104)
        ELSE TRY_CAST([DateString] AS DATETIME)
    END AS DateValue
FROM @Table

Results

ID  Country     DateString      DateValue
-------------------------------------------------------
1   USA         02/13/2018      2018-02-13 00:00:00.000
2   Germany     02.12.2018      2018-12-02 00:00:00.000
3   England     23/02/2011      2011-02-23 00:00:00.000
4   UAE         08 Jun 2008     2008-06-08 00:00:00.000

Note that any strings that you can't implicitly identify from the Country will be caught by the TRY_CAST and will return NULL if they cannot be accurately converted. These NULLs might be difficult to handle automatically, and this is where data cleansing is important, if you can clean up data at the source and better control the input formats then you will have less of these issues.

CONVERT Styles


Due to the lack of information, I can only give you an example code that could solve this problem.

This is based on the AdventureWorks2014 database. I created a simple testing table that only has 2 attributes, the name of the sales person and the amount of sales he's made in total (count):

CREATE TABLE T (
PersonName nvarchar(30),
Sales int
)
GO

And this procedure takes 2 dates as input, selects the required data and inserts it into the table:

CREATE PROCEDURE test
    @startDate date,
    @endDate date
AS
BEGIN

WITH CTE AS (
SELECT  E.BusinessEntityID,
        COUNT(SOH.SalesOrderID) AS [Sales]
FROM AdventureWorks2014.HumanResources.Employee AS E
    INNER JOIN AdventureWorks2014.Sales.SalesOrderHeader AS SOH
        ON SOH.SalesPersonID = E.BusinessEntityID
WHERE SOH.DueDate > @startDate AND SOH.DueDate < @endDate
GROUP BY E.BusinessEntityID
)
INSERT INTO T (
PersonName,
Sales
)
SELECT P.FirstName, CTE.Sales
FROM AdventureWorks2014.HumanResources.Employee AS E
    INNER JOIN AdventureWorks2014.Person.Person AS P
        ON P.BusinessEntityID = E.BusinessEntityID
    INNER JOIN CTE ON CTE.BusinessEntityID = E.BusinessEntityID

END
GO

For every sales person this calculates the amount of sales they have made between the @startDate and @endDate. You're going to have to edit this solution to fit your own tables and data.

Testing this procedure:

EXEC test '2011-06-30', '2012-06-30'

Gives the table this result


There is no single officially approved or universally accepted implementation as of writing this (May 2019). However, as suggested by Rick James in a comment,

  Search for mysql Levenshtein; there are several implementations out there. I don't know if they are all the same. A UDF implementation is likely to be faster than a Stored Procedure.


When is the SGA created in an Oracle database environment?

https://docs.oracle.com/cd/B28359_01/server.111/b28310/create003.htm#ADMIN11073

To create a database first you must connect to an instance. SGA is a part of an instance so it is created before the database, thus A is a wrong answer.


To provide examples...

Case 1:

  SET autocommit=ON;
  INSERT ...; -- with or without triggers
  UPDATE ...; -- with or without triggers

The INSERT and its trigger actions are performed atomically.  That is, the INSERT and trigger are either completely done or completely undone.
The UPDATE and its trigger actions are performed atomically.  That is, the UPDATE and trigger are either completely done or completely undone.

Case 2:

  -- (the value of `autocommit` does not matter)
  START TRANSACTION;
  INSERT ...; -- with or without triggers
  UPDATE ...; -- with or without triggers
  if ... then ROLLBACK else COMMIT;

The INSERT (and its trigger actions) might succeed or might fail and be rolled back.
The UPDATE (and its trigger actions) might succeed or might fail and be rolled back.
You might choose to ROLLBACK -- In this case all of the above stuff would be undone.
You might choose to COMMIT -- In this case all of the above stuff that has not already been rolled back would be committed.

Case 3:  (Do not use this case; it is too user-error-prone.)

  SET autocommit = OFF;
  INSERT ...; -- with or without triggers
  UPDATE ...; -- with or without triggers
  if ... then ROLLBACK else COMMIT;

This works the same as Case 2, except there is some question about the scope of the ROLLBACK/COMMIT.

(Please edit this answer if it is not correct.)


postgresql: grouping with json columns, eliminating nulls

Unless I don't know your actual query, you can JOIN rows where col_a is not null with rows where col_b is not null.

create table test(my_id int, col_a json, col_b json);
insert into test values
(5001, '["a", "b"]', null),
(5001, null, '["c", "d"]');

    select t1.my_id, t1.col_a, t2.col_b
    from test t1
    inner join (select my_id, col_b
                from test
                where col_b is not null) t2
    on t1.my_id = t2.my_id
    where t1.col_a is not null;

my_id | col_a      | col_b
----: | :--------- | :---------
 5001 | ["a", "b"] | ["c", "d"]

dbfiddle here


Assuming that Asset_ID is thePRIMARY KEY, you will not have any dups until you do theJOINs.  And theDISTINCT` is unnecessary.

Now, let's check the JOINs.  What is the mapping between Assets and each other table?  Is it 1:1?  Or 1:many?  Let's say, for discussion, that an Asset is in multiple "categories".  At this point, what do you want with SELECT ... c.Category?  One solution is

SELECT ..., GROUP_CONCAT(c.Category), ...
    FROM Assets
    JOIN Categories
    GROUP BY Asset_ID  -- this replaces DISTINCT

This avoids exploding the number of rows, and does it by turning that putting a list in the "category" column.


Should I duplicate columns between tables to speed-up aggregations like SUM?

DB design
Seems like a 1:n relationship between waste_card and wastes. Actual table definitions (CREATE TABLE statements) would clarify.
Do not repeat (duplicate) the columns user_id, manufacture_date, and address_id in the many-table wastes. That would bloat your table, introduce problems with maintenance, raise the question which is the true source of information, blatantly defy normalization etc.
Queries
While computing a total sum, just use a plain [INNER] JOIN. Wastecards with no related entries in waste don't change the result and can be excluded:
SELECT SUM(w.amount) AS total_waste
FROM   waste_card wc
JOIN   wastes     w  ON w.waste_card_id = wc.id
WHERE  wc.user_id = $1
AND    wc.manufacture_date < $2
AND    wc.address_id = $3;

LEFT JOIN makes sense to ...

list the sum per entry in waste_card
and include cards with no entries in waste

But then it's typically better to use a LATERAL subquery computing the sum per card. The aggregate function guarantees exactly one row from the subquery so we can just as well switch to CROSS JOIN LATERAL (like Andriy pointed out):
SELECT wc.*  -- or better just the columns you need
     , w.sum_waste
FROM   waste_card wc
CROSS  JOIN LATERAL (
   SELECT SUM(w.amount) AS sum_waste
   FROM   wastes w
   WHERE  w.waste_card_id = wc.id
   ) w
WHERE  wc.user_id = $1
AND    wc.manufacture_date < $2
AND    wc.address_id = $3;

See:

JOIN (SELECT … ) ue ON 1=1?

Fastest way to count how many dates' ranges cover each date from series

Convert right side of join of many to many into array

Covering index
To speed up either query, an index "covering" wastes.amount might pay (requires Postgres 11 or later):
CREATE INDEX your_idx_name ON wastes(waste_card_id) INCLUDE (amount);

For Postgres 10 or older, fall back to a multicolumn index:
CREATE INDEX your_idx_name ON wastes(waste_card_id, amount);

See:

Does a query with a primary key and foreign keys run faster than a query with just primary keys?

Do covering indexes in PostgreSQL help JOIN columns?

Aside:  LIMIT without ORDER BY (like in your first query) produces arbitrary results. Typically you want to add ORDER BY to determine results.


Why isn't Unicode character REPLACEd in some cases?

This behavior is simply due to the fact that you are executing the query in a Database that has a pre-"100" series default Collation, in which case a great many characters do not have any sort weight. No sort weight means that they equate to empty string. They have a value of 0. So they always equal each other when nothing else having a sort weight is involved. They also equate to empty string. The version 100 Collations (starting in SQL Server 2008 which is version 10.0 or 100 without the decimal / minor version) added sort weights for most characters. So, just force the collation by adding COLLATE Latin1_General_100_CI_AS_SC. A binary Collation of any version (one ending in _BIN2, don't use Collations ending in just _BIN) also works as there are no such things as sort weights in binary Collations.

SELECT 1 WHERE NCHAR(1234) = '' COLLATE SQL_Latin1_General_CP1_CI_AS;
-- 1

SELECT 2 WHERE NCHAR(1234) = '' COLLATE Latin1_General_CI_AS;
-- 2

SELECT 3 WHERE NCHAR(1234) = '' COLLATE Latin1_General_100_CI_AS;
-- (no results)

SELECT 4 WHERE NCHAR(1234) = '' COLLATE Latin1_General_BIN2;
-- (no results)

Even multiple characters with no weight still equate to empty string (or any number of characters with no sort weight):

SELECT 5 WHERE NCHAR(1234) + NCHAR(1234) + NCHAR(1234)
       = N'' COLLATE Latin1_General_CS_AS_KS_WS;
-- 5

In this next test, NCHAR(1234) equates to itself only when using a version 100 (or newer) Collation. Pre-version 100 Collations assign no sort weight to NCHAR(1234) such that it doesn't equate to anything, hence it was previously not found in the first expression.

SELECT REPLACE(N'a' + NCHAR(1234) + N'a',
               NCHAR(1234) COLLATE Latin1_General_100_CI_AS,
               N'test');
-- atesta

Continuing example # 5 above, we can prove that 3 characters having no weight equate to 2 different characters that also have no weight:

SELECT 6 WHERE NCHAR(1234) + NCHAR(1234) + NCHAR(1234)
             = NCHAR(5342) + NCHAR(5342) COLLATE Latin1_General_CS_AS_KS_WS;
-- 6

But switching to a version 100 Collation changes that:

SELECT 7 WHERE NCHAR(1234) + NCHAR(1234) + NCHAR(1234)
             = NCHAR(5342) + NCHAR(5342) COLLATE Latin1_General_100_CS_AS_KS_WS;
-- (no results)

SELECT 8 WHERE NCHAR(1234) = NCHAR(5342) COLLATE Latin1_General_100_CS_AS_KS_WS;
-- (no results)

Please see the "Supplementary Characters" characters section of the following post of mine. In that section I show similar behavior as it relates to supplementary characters, and at the end of the section I show how many BMP characters (i.e. non-supplementary characters) match one of these "missing weights" characters per Collation version.

The Uni-Code: The Search for the True List of Valid Characters for T-SQL Identifiers, Part 3 of 2 (Delimited Identifiers)

For more info on working with Collations, please visit: Collations.info


How do I list the constraints of a DOMAIN?

PostgreSQL's System Catalog

SELECT conname
FROM pg_constraint
WHERE contypid = 'dom_zipcode'::regtype;


Oracle Creating ER Diagrams and Data Dictionaries

Oracle's Data Modeller can reverse-engineer from an existing database. They were charging for it at one time, but it is now free to use.


How far should you go with normalization?

You should go as far as you should, and no further. Of course. ~ The problem may be that this
is a bit of an art, and it's why this isn't a pure science.

Our main product is an analysis and reporting system, and so in that regard, we have quite a few detail records. We initially had it designed with lots of joins on a common ID for some of the child records, but we found that if we denormalized a couple of fields we could cut out a LOT of joins and we could take away a lot of performance headaches.

But we only knew that because we 1) created a "normalized" design, 2) started using it, 3) profiled the actual performance after hundreds of millions of rows across dozens of tables.

The end story is that until we profiled we couldn't know for sure what was going to work for us. We liked the idea of normalizing so we could update more easily, but in the end actual performance was the deciding factor. That's my advice for you: Profile, profile, profile.


The actual cause of transaction log full under below scenario

First of all do what the message says and run

SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'your db name'

I'd also check sys.database_files in your database to confirm the actual settings for the transaction log file.

Once you have the reuse identifier then check it against the values at https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-2017


SQL server - data successfully inserted in a table but not returned in Select Statement

If read uncommitted returns rows but read committed does not that rather suggests that you have a number of open transactions. You can look in sys.dm_tran_active_transactions, especially column transaction_begin_time. If there's anything suspicious there I think you have your culprit.

The procedure itself seems innocent. That would suggest another transaction is opened further up the stack perhaps in a parent procedure, through the application or a server setting. SQL Server support multiple nested BEGIN TRANSACTION / COMMIT statements. A single commit will not persist all the work done by all nested BEGIN TRANSACTION statements. Quote:

  When @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active.

If you have open, uncommited transactions I can't think what the solution could be. If the connection were severed those transaction would roll back and the data lost. If the instance were restarted those transactions would roll back and the data lost. The uncommitted values will not be in a backup file. Hopefully you have a source external to SQL Server from which they can be salvaged.


Refresh Materialized View impact on the DB

Your problem is likely the indexes.

IIRC, refreshing a materialized view drops the existing data and create a new "table" with the current data.

What this does for your indexes is re-index the entire subset of data, which based on your indexes send like a significant workload.

My recommendation would be to not use the built-in materialized views and to instead roll your own equivalent, incrementally updating data so your are only writing stale data/indexes.

The trade off is that this will be much more complex to build out than simply creating a materialized view, and you'll need to consider how you want to track invalidation of stale data.


THROW is not returning the correct line number when variables are used

Is this to be expected and a known flaw or am I doing something wrong?

Yes, it is the expected behavior. You've generated a new error message, and it originates at the call site of THROW ....  This is why you should favor just THROW; or you need to encapsulate all the relevant error context information in your message.


How can I create notification on user's connecting to database?

There are three ways to find who is connected to a database in sqlserver.

First one:

Use the SQL SERVER Management Studio -- Management -- Activity Monitor
This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc.

Second One:

Use the built in stored procedure called sp_who2
Run the command exec sp_who2
This gives a list of users, database names, status, command, program with which they are using the database, CPU time, Login time etc.
Third One:

Third one Use the script

DECLARE @temp TABLE(spid int , ecid int, status varchar(50),
                     loginname varchar(50),
                     hostname varchar(50),
blk varchar(50), dbname varchar(50), cmd varchar(50), request_id int)
INSERT INTO @temp

EXEC sp_who

SELECT COUNT(*) FROM @temp WHERE dbname = 'DB NAME'

From the above methods, you can use them to implement in a sql job to get required notification.

The best method i used is the latest sp_whoisactive by MVP Adam Machanic


Can I use SSRS 2016 with SQL Server 2008 R2 database engine?

You can use SSRS 2016 and fetch data from another databases.
When you defines the connection string of data sources, you can use whatever of your installed drivers.
According to MS Docs:
Built-in data extensions

Default data extensions in Reporting Services include the following types of data connections:
Microsoft SQL Server
Microsoft SQL Server Analysis Services
Microsoft SharePoint List
Azure SQL Database
Microsoft SQL Server Parallel Data Warehouse
OLE DB
Oracle
SAP NetWeaver BI
Hyperion Essbase
Teradata
XML
ODBC
Microsoft BI Semantic Model for Power View: On a SharePoint site that has been configured for a Power Pivot gallery and Power View, this data source type is available. This data source type is used only for Power View presentations. For more information, see Building the Perfect BI Semantic Tabular Models for Power View.


garbd with proxysql and mysql replication

garbd is used to a fake Galera cluster member.

It doesn't fake a MySQL server.


Using intersect is almost always an indication that you should have a "composite" index on the columns mentioned.

In your case, the index can be either of these:

INDEX(user_id, ref_url)
INDEX(ref_url, user_id)

The order of clauses in the WHERE does not matter -- the Optimizer is free to reorder things.  It does not matter to the optimization.

On the other hand, the order of columns in a composite index does matter.  (But it does not happen to matter in this case.)

Further explanation:  For the "intersect" technique, this happens:

Find all entires in INDEX(user_id) for 32366; collect the PRIMARY KEYs for those rows.
Find all entries in INDEX(ref_urs) for 'http...'; collect the PRIMARY KEYs for those rows.
Using those two lists, "intersect" them.  That is, find out what entries are common to both.
Now, reach for the desired rows by using the PRIMARY KEYs.

With a composite index:

Drill down the BTree directly to the desired row(s).


Distributed Database Architecture Using MySQL Replication

Bill Karwin has already given the best answer that I can think of... but I think some additional explanation might be in order as to why the link you posted in the original question probably does not provide a sufficient solution.

Your DBA is, at least in one sense, correct.  The problem is that the illustration is deceptive.  The three distributed nodes in the middle look like they are database servers sending updates back to the master, which MySQL can't really offer.  You'll notice that the 3 nodes are actually labeled as "web servers."

It is apparent from the configuration examples that all the MySQL slave instances are doing in this setup is providing the application with read-only access to local replicas of the master database, but the web (application) server is actually sending all of its writes back to the master database -- not through to the local MySQL instances.

That's what MySQL can do -- scale out your ability to read from asynchronous replicas.  It is unlikely that you'd want the ability for applications to write to the database to be dependent on the single point of failure that the document includes.  Yes, you could have multiple masters, but depending on how the application works, it's only safe to direct writes to one such master at a time because there's no provision in multi-master asynchronous (ordinary MySQL) replication to handle the case when conflicting data gets written simultaneously from two (or more) masters.

PXC and MariaDB in cluster configuration don't have that same problem because the cluster nodes don't operate asynchronously... although there's a different problem, since if one node gets isolated from the other two, it can't be written to because in the case of a network split, the server that is left in the minority won't accept writes (to avoid inconsistency).  If each of them is isolated from all of the others, then none of them will work, because they are all in the minority and have no idea what has happened to their peers.  That's not to say it's not a great solution -- it's just not a perfect one.

But there is no perfect solution.

If your data is such that the following is practical, there's a third approach.  This would be a scenario where each site has its own set of uniquely-named schema+table sets (that is, table_schema + table_name is unique to each site, table names could be the same if database names are different) that are written to, but needs to be able to read the data from the other sites.

MariaDB multi-source replication could be configured such that you have a centralized server that is configured as a slave to all of the remote servers, and serves as master to all of the remote servers.  Each replication event received by this central server would be written to the binlog and sent out to all of the remotes, which would execute it locally.  The remotes would log their local changes, sending replication events back to the master for distribution to all of the other remotes... sort of a hub-and-spoke configuration.  The central server would only consolidate the data, it wouldn't be used by an instance of the application... and the remote servers wouldn't necessarily even have to be MariaDB -- only the central one -- I have MySQL 5.5 and MariaDB 10.0 systems interconnected and replicating to each other (5.5 <-> 10.0) without issue in my operation.  The current limit of "spoke" servers would be 64.

But again, this is only practical if each site needs to see, but not change, the data from the other sites, and/or access a common set of read-only data while updating a uniquely-named local set of tables.  In this scenario, a site's isolation would only delay their access to the latest data from outside the local environment but would not impair their ability to write to the things they should be able to write.


When SysServer and Server Property Don't Match

master.dbo.sysservers is a deprecated. You should use sys.servers.

If you change your server name after installing sql server then you have to make the entry in master.sys.servers which can be done by sp_dropserver and sp_addserver


Merge Replication Invalid Object ( on an object that no longer exists ) ComErrorException

From what I can tell... I had a table valued function that used to require another table valued function as part of it's logic.  That behavior was improved (combining the two tbf's into one) and because our replication included schema changes the improvement was pushed out to subscribers and the extra tbf object was removed... which was then also pushed to subscribers.

A few weeks later when a subscriber tried to reinitialize however it went back to the original tbf and then couldn't find the other one that it relied on (before it got to the incremental change that told it, it no longer needed the other one)... and simply died.

I was able to recreate the behavior and I could see the old version of the tbf appearing in the subscriber as it tried to reinitialize.  So, moral to the story is... replicating a work in progress is delicate.