How to fetch related rows on multiple condition with ranking

SQL Query to replicate the data:
CREATE TABLE Products (
  Id INT,
  name NVARCHAR(max),
  category NVARCHAR(max),
  seller INT,
  m_age INT,
  status NVARCHAR(2),
  type_id INT,
  region NVARCHAR(2)
);

INSERT INTO Products
  (Id,
  name,
  category,
  seller,
  m_age,
  status,
  type_id,
  region)
VALUES
('10','TBox','Gun','14','4','Q4','12','EU' ),
('11','Globe','Gun','36','12','Q1','17','EU' ),
('12','Nike','CFK','12','5','Q2','12','AS' ),
('32','Raven','Knife','21','14','Q1','12','EU' ),
('46','TBox','Gun','14','10','Q4','12','AS' ),
('46','Blaster','Gun','14','10','Q4','12','EU' ),
('78','PaintGun','Gun','12','10','Q2','54','US' ),
('98','TBox','OD','21','4','Q3','54','AS');

Solution:
DECLARE @id INT = 10,
@name NVARCHAR(MAX) = 'TBox';

Select
    P4.id,
    P4.name,
    p4.category,
    P4.seller,
    P4.m_age,
    P4.status,
    P4.type_id,
    P4.region,
    (DENSE_RANK() OVER (ORDER BY P3.Category DESC, P3.name DESC, P3.Info DESC, P3.seller DESC)) -1 AS Rank
From (
    Select
        *,
        ROW_NUMBER() OVER (ORDER BY ID) AS SNO
    From Products
) P4
LEFT JOIN
(Select
    ROW_NUMBER() OVER (ORDER BY P1.ID) AS SNO,
    CASE WHEN P1.Category=P2.Category THEN 1 ELSE 0 END Category,
    CASE WHEN P1.name=P2.name THEN 1 ELSE 0 END name,
    (
        CASE WHEN P1.m_age=P2.m_age THEN 1 ELSE 0 END +
        CASE WHEN P1.status=P2.status THEN 1 ELSE 0 END +
        CASE WHEN P1.type_id=P2.type_id THEN 1 ELSE 0 END +
        CASE WHEN P1.region=P2.region THEN 1 ELSE 0 END
    )                                                       info,
    P1.seller
From Products P1
CROSS Join
(
    Select * From Products
    WHERE Id = @id and name = @name
) P2 ) P3
ON P3.SNO = P4.SNO
ORDER BY P4.ID;

Query Output:
id|name    |category|seller|m_age|status|type_id|region|Rank|
--|--------|--------|------|-----|------|-------|------|----|
10|TBox    |Gun     |    14|    4|Q4    |     12|EU    |   0|
11|Globe   |Gun     |    36|   12|Q1    |     17|EU    |   3|
12|Nike    |CFK     |    12|    5|Q2    |     12|AS    |   7|
32|Raven   |Knife   |    21|   14|Q1    |     12|EU    |   6|
46|TBox    |Gun     |    14|   10|Q4    |     12|AS    |   1|
46|Blaster |Gun     |    14|   10|Q4    |     12|EU    |   2|
78|PaintGun|Gun     |    12|   10|Q2    |     54|US    |   4|
98|TBox    |OD      |    21|    4|Q3    |     54|AS    |   5|

FYI: Rank of Id - 12 and 32 should be flipped as Id 32 record has 2 matched info so it's rank should be higher compared to Id 12 record.


Select count(*), where 2 rows with different values exists

I think I found the solution for this case. My solution is to use SUM() aggregation with IF() condition. The final logic is substitute COUNT(*) to boolean - have rows or empty query. This query will return results only if it match both conditions:

SELECT SUM(IF((value='john' AND type='first_name') OR (value='doe' AND type='last_name'), 1, 0)) AS cnt FROM tablename HAVING cnt=2;

If I want to select all data where first name is John and last name is Doe OR last name is Smith, query will be:

SELECT sum(IF((value='john' AND type='first_name') OR (value='doe' AND type='last_name'), 1, 0)) AS first_last, SUM(IF(value='smith' AND type='last_name', 1, 0)) AS last FROM tablename HAVING first_last=2 OR last=1;


I've always found SQL Examiner to be a great tool that's super easy to use when synchronizing one database from another.
Since you said the names don't collide between entities, then SQL Examiner will basically create all the entities (which you get to choose, e.g. you can exclude all procedures, or hand pick which views, etc) in the destination database from the source database.
It also has features such as showing you the scripts it generates before executing them, creating backups before making changes, and is just an overall good "comparison" tool for both schema and data differences between two databases.
They give you a 30 day full featured free trial, so you can test it out for a while first before deciding to buy.
Additionally you can check out ApexSQL's SQL Compare.


SQL Server stored procedure that would enforce atomicity?

BEGIN TRY
BEGIN TRANSACTION

INSERT INTO tEmpl  (lastName)  VALUES('Smith'),('Jones'),('Smitty'),('Smithson')

COMMIT TRANSACTION
PRINT 'Transaction committed successfully'

END TRY

BEGIN CATCH
ROLLBACK
PRINT 'Transaction rolled back'

END CATCH


How to return value for key from a jsonb column?

This post gives you your answer:

CREATE TABLE json_test
(
  id serial primary key,
  data jsonb
);
INSERT INTO json_test (data) VALUES ('{"0":"INVALID","1":"BELOW","2":"EQUAL","3":"ABOVE"}');

You specify the element that you wish to extract thus:

SELECT id, data->>'1' AS whatever FROM json_test;

And the result is:

id      whatever
1       BELOW

There are a shedload of new json functions in PostgreSQL - there could be more examples (hint PostgreSQL team...).


While technically this is possible, as MySQL 5.7 supports multisource replication, so one instance can replicate from 2 other servers, it is unlikely you want to setup that.

Regular replication does not resolve write conflicts, so those would have to be solved at application layer or it would only serve work for trivial configurations (e.g. each server working with a separate database or just doing appends to a table).

If you want a proper MySQL cluster with multi-masters and conflict resolution, check Galera/Percona XtraDB Cluster, InnoDB Cluster or NDB Cluster.


What is the best practices for storing sales tax rates?

Sales taxes are almost infinitely complicated, so you might want to consider using a rules engine like Drools for this.

Sales Tax is a rate or an amount that is charged on the value of an item, or by unit (per day, per litre, per each). An item can have zero, one or many sales taxes applicable to it.

Warning: Some sales taxes are recursive! Ex: there is GST on top of the Vehicle Rental Tax in BC.

The sales tax rates on an item is a function of the following:

Location of the buyer
Product category of the item being sold (gas, liquor, books)
Value of the item (ex higher rate on higher priced luxury goods)
Category of the buyer (ex charity, First Nations individual, industrial camps)
Category of the seller
Intended use of the item (for export, for manufacturing)
Duration of the service (car rentals > 8 hours and < 28 days)
Whether it's a gift or not

I think what you ultimately want is a large look-up table (generated by a materialized view) like the below. It would include default tax rates as well as exemptions (represented by null) or zero-rated (with a rate of zero)

This is only a sample of taxation rules for hotel accommodation in British Columbia:

name | area | product cat  | price range | buyer cat | service duration | rate | unit  | seller cat
-----------------------------------------------------------------------------------------------
GST    BC     Accommodation  null          null        null           0.05   Percent null
PST    BC     Accommodation  null          null        null           0.08   Percent null
MRDT   BC     Accommodation  null          null        null           0.02   Percent null
MRDT   Vanc   Accommodation  null          null        null           0.03   Percent null
PST    BC     Accommodation  [,30]         null        null           null   null    null
MRDT   BC     Accommodation  [,30]         null        null           null   null    null
PST    BC     Accommodation  null          null        [30,]          null   null    null
MRDT   BC     Accommodation  null          null        [30,]          null   null    null
PST    BC     Accommodation  null          1st Nations null           null   null    null
MRDT   BC     Accommodation  null          1st Nations null           null   null    null
PST    BC     Accommodation  null          Gov Canada  null           null   null    null
PST    BC     Accommodation  null          null        null           null   null    Industrial Camp
MRDT   BC     Accommodation  null          null        null           null   null    Industrial Camp
PST    BC     Accommodation  null          null        null           null   null    Charity
MRDT   BC     Accommodation  null          null        null           null   null    Charity

Then you'd have a crazy query that would figure out the default rates, replacing them with the most appropriate exemptions or zero-rates.

5% GST always applies
8% PST unless price is < 30 or duration > 30 days or buyer is First Nations or Government of Canada or seller is an Industrial Camp or Charity
2% Municipal and Regional District Tax unless price is < 30 or duration > 30 days or buyer is First Nations or seller is an Industrial Camp or Charity
3% Municipal and Regional District Tax if accommodation is in Vancouver unless price is < 30 or duration > 30 days or buyer is First Nations or seller is an Industrial Camp or Charity


Probably refers to "fresh installation".

The workaround is painful...

Dump all the data.
Stop mysqld.
Blow away ibdata1 and iblogs.
Change the setting in my.cnf.
Restart.  (This should feel like a 'fresh' install.)
Reload the data.

Do you have some indication that changing that setting will help you significantly?  If so, please explain.  It is a very new feature, and is there for a reason, but not many users need it.

Also

from the 5.7 documentation, - dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespaces.html

  The innodb_undo_tablespaces configuration option is deprecated and will be removed in a future release." and "The number of undo tablespaces can only be configured when initializing a MySQL instance and is fixed for the life of the instance."

– Thanks to @OutfastSource


Create second instance of mysqld on windows xp

=> only set my_ibt.ini  in the pateh C:\RailsInstaller\
=> my_xxx.ini

[mysqld_xxx]
#if already installed then use that basedir
basedir = "C:\RailsInstaller\MYSQL"

#where you want to set your database
datadir="C:\RailsInstaller\MYSQL\data"
port = 43307
enable-named-pipe
socket = mypipe1

In the command promt which runs as administrator go to
\MYSQL\bin>
And run Command :

mysqld --install mysqld_xxx --defaults-file=C:\RailsInstaller\my_xxx.ini

Service successfully installed.

Then :

net start mysqld_xxx

The mysqld_ibt service is starting.
The mysqld_ibt service was started successfully.


Join SQL Server table in OPENQUERY on multiple keys

OK, then think the other way around.  Something like the general outline:

Create a Linked Server from Oracle to SQL Server and SQL Server to Oracle.
From Oracle select the 100 rows of SQL Server data and store in a temporary table in Oracle.
Run the query in Oracle to join/filter/whatever the 1 billion Oracle rows by the 100 SQL Server rows in the temporary table to create the result set.
Use OPENQUERY from SQL Server to bring the results back to SQL Server.

INSERT INTO SqlServerTable
  SELECT * FROM OPENQUERY (LinkedServerName, 'query text');

I likely missed some step, but this basic approach is to move the small amount of SQL Server data over to Oracle, run the query, and then read the result back to SQL Server.

EDIT:  At https://stackoverflow.com/questions/29200147/how-do-i-convert-an-oracle-timestamp-data-type-to-sql-server-datetime2-data-type Jon of All Trades showed how he converts an Oracle timestamp to a SQL Server datetime2.

(I have no Oracle server to test against, however.)


DAC (Dedicated Admin Connection) is (a metadata tool) only use for troubleshooting purposes. This is used by SQL Server DBA as a last resort if you can't access or connect to SQL Server. There are some cases wherein a process or someone tries to use all your system memory and CPU and nobody can use SQL Server (no new processes can connect).
DAC was built in SQL Server 2005 because prior to SQL Server 2005, if we can't access/connect to SQL Server, we either kill all the sessions and restart SQL Server. In SQL Server 2005, Microsoft decided to create DAC for troubleshooting purpose (backdoor).
When we connect as DAC (special connection), we got dedicated processor (scheduler) and a reserved memory to run a simple queries. We need a sysadmin permission to connect. We can only connect locally by logging in to the local server and connect (except when you need to troubleshoot cluster or AGs that needs remote DAC). Only 1 session that can connect as DAC in SQL Server and no one can connect as DAC until we disconnect our DAC session.
We can check if someone is using DAC by querying:SELECT dese.session_id
FROM sys.tcp_endpoints as te
JOIN sys.dm_exec_sessions as dese ON te.endpoint_id = dese.endpoint_id
WHERE te.name ='Dedicated Admin Connection';

What can we do when we are connected as DAC:

access to hidden system tables (like sys.sysobjvalues)
run simple queries for troubleshooting
advanced troubleshooting (like repairing corrupt system table pages)

What we can't do:

run complex queries (parallelism is prohibited)
backup/restore operation

We can't query systems tables in normal connection.
Try SELECT * FROM sys.objects WHERE type_desc='SYSTEM_TABLE';. These are the tables that you can't query in a normal connection.

When we are connected as DAC, you can access systems table (eg. sys.sysrscols)

Lastly, as a best practice always always disconnect the DAC connection after use.
I would recommend using SSMS and read the book about internals of SQL Server and Troubleshooting SQL Server. It's not SQL Server 2017 specific but most of the concepts are still applies to current version of SQL Server.
From Microsoft:

Important:
The system base tables are used only within the SQL Server Database Engine and are not for general customer use. They are subject to change and compatibility is not guaranteed.
Access to system base tables by using DAC is designed only for Microsoft personnel, and it is not a supported customer scenario.


Load index to cache is not using memory available in the key_buffer_size variable

You have a pretty strange buffer configuration:
max_connections=4500
. . . . .
key_buffer_size=62G
read_buffer_size = 200M
read_rnd_buffer_size = 100M
sort_buffer_size = 1G
join_buffer_size = ???

While key_buffer_size is a global value for all MyISAM indexes across all the databases, (read|read_rnd|sort|join)_buffer_size are per-connection values. That amounts are reserved for each client attached to the mysqld service. The overall RAM consumption is calculated as:
(read_bs + read_rnd_bs + sort_bs + join_bs) * max_connection + key_buffer_size

In your case it seems to be at least 6TB in total that is too optimistic.
First you have to lower your buffer sizes to the reasonable values - 2-8MB each. Second you have to install mysqltuner.pl - a neat tool that analyze both config and status variables and propose a number of reasonable advices.
Third you have to perform a system monitoring for slow queries and on-disk temporary tables and tune your mysql accordingly to your certain needs.


Is splitting a CSV column into a separate table (enforcing 1NF) unnecessary complication?

The answer to this question is the same as the answers to this question.  Both are really about first normal form (1NF).  The linked question had to do with justification for a key column made up of different data elements concatenated via a dash "-".  This question has to do with justification for a non-key column made up of different values of the same data element contented via a pipe "|".  To answer, we must first assess if such a design violates the intent of 1NF, and second, if so, when would it be justified to make that violation of 1NF anyway.
Background
First, a quick re-cap of what constitutes 1NF.  A table is a relational (R-Table) table, and thus normalized (meaning in 1NF by definition) if in its design a discipline is followed that ensures:

Distinct, un-ordered rows
Uniquely named, un-ordered columns
Each column contains a single value from the corresponding domain

Note requirement 3 implies no column can have a missing value (or a NULL marker).  Let us assume for the moment that requirements 1 and 2, and the "no missing value" part of requirement 3, are met.  I say "assume" because we really cannot assess compliance without seeing all the rows in the table to verify there are no missing values, no duplicate rows, and no implicit information encoded in the ordering of columns or rows.  Now if the schema included integrity constraints which implement the business rules from the conceptual model to guarantee no duplicate rows and no missing values, we would not have to examine the all the data to verify compliance with respect to those requirements as we know the DBMS will enforce consistency with those rules in every case.  We would still have to examine all the data however to ensure row or column ordering is not being used to implicitly encode additional information.
Given the assumptions just set forth, we have left to determine if the placement of multiple disaster cause codes into a single column separated by pipes violates requirement 3 for single value columns.  In my answer to the previous question about a key column with multiple data elements separated by a dash I said such a design did violate that requirement, and that under no circumstances would it be justified.  My answer is based upon study of the most current and definitive thinking on 1NF from Fabian Pascal and Chris Date (references to which I will provide at the end of the answer).  Fabian himself contributed to answering that question with a correction to my answer.  He said my assertion was too strict, and that:

If the combination is just for visual purposes and it is guaranteed that it will always be accessed by the DBMS as a single value (emphasis mine here).  The problem is that such guarantees are shaky.

Fabian's correction strikes at the heart of the subtlety of the single valued column requirement.  If that column's domain is a relation-valued domain (RVD), and a single table thus contained in that column, it would not be a violation of 1NF.  Before we rejoice in the ability to now justify the practice of your "senior" dev in creating columns with multiple data elements separated by dashes or pipes or whatever else, I want to first point out that no SQL DBMS actually supports RVDs, thus making operating on the column as a single value difficult.  Second, both Date and Pascal point out that while an RVD is not a violation of 1NF, it does introduce additional complexity and asymmetry without (except in a few extreme circumstances) any additional benefit.  So while it may be permissible, it may well not be a good idea.  Please refer to the references for the details on this.
Evaluating the Design
With the background on 1NF in place, let's get down to this situation.  The column in question, disaster cause, I would submit is not a single value column precisely because you give an example query where individual values within the larger string are accessed.  Therefore the table is not normalized.  Given this, is it still justified to design the table in a such a way as to make it not an R-Table simply because the normalized design calls for two tables and is thus perceived to be "unnecessarily complicated?"  I would say absolutely not for two reasons.  First, Einstein said things should be as simple as possible, but no simpler.  This un-normalized design is an example of "too simple" as by having a table that is not an R-Table none of the benefits of an R-Table can be gained.  The references go into detail on this point, but the main point is that guaranteed logical access to each data element is lost.  A more subtle issue is that maintaining the integrity of the disaster cause is much more difficult.  If the disaster cause were a child table, ensuring each disaster cause code can only be assigned once per disaster is guaranteed by the key constraint.  As a multi-value column in the disaster history table an application could easily accidentally concatenate the same cause code twice.  It makes my head hurt even contemplating how to write the procedural trigger that would be necessary to implement this constraint on the multi-value column.  Finally, note I have addressed only the logical design.  I am sure there are many experts in the physical implementation of various SQL DBMS' who would cringe at the resulting optimization of the queries that will be run, like the one provided, to access this data.
Lastly, I would argue that even the rationale that a normalized design with two tables is "unnecessarily complicated" is wrong.  Instead, the multi-value column in the single table is in my opinion "unnecessarily complicated."  The query you provided, which is trying to count up the number of times a given disaster cause contributed to a disaster, is much more complicated to write than it would be if the design simply had a disaster table and a child disaster cause table.  The nesting would be unnecessary and the join would be simple and obvious as to its purpose.  Someone looking at the schema for the first time can easily understand, without even seeing the data, that we have information about disasters, and each disaster can have many causes.  With the multi-value column this simple structure is obscured and the first time user would have to dig into the data values to discover that in fact a disaster can have many causes.
References
Fabian Pascal's Practical Database Foundation Series.  Paper #5 addresses 1NF and RVDs.  If you purchase all the papers (which you really should as they are a complementary set addressing all the fundamentals) two additional papers can be obtained that specifically address 1NF - "What First Normal Form Really Means" by CJ Date, and "What First Normal Form Means NOT" by Fabian Pascal.  Next, CJ Date has written extensively on relational theory.  The most current reference with respect to normalization is Database Design and Relational Theory:  Normal Forms and All That Jazz.  Finally, Fabian Pascal has blogged recently about 1NF and those blogs are excellent reading.


SELECT speler1.Roepnaam as team1,     -- unique alias for 1st value from 1st table copy
       speler2.Roepnaam as team2      -- unique alias for 2nd value from 2nd table copy
FROM wedstrijd
LEFT JOIN speler as speler1           -- unique alias for 1st table copy
ON wedstrijd.Speler1ID = speler1.ID   -- join this 1st copy by its alias
LEFT JOIN speler as speler2           -- unique alias for 2nd table copy
ON wedstrijd.Speler2ID = speler2.ID   -- join this 2nd copy by its alias


Clear old log entries from API while saving N of each type by user

Not going to be that simple.

Instead, write a script that walks through the table using the index on user_id for keeping track of where it is.

For each user, find the 100th from last, then DELETE any older ones.

This script will take minutes or hours, but who cares.  The script could even start over when it finishes -- keep going forever.

It will be desirable to have

INDEX(user_id, dt)

(I am assuming dt is the column giving the order within the user.)

More details...

The loop starts with finding the next $user_id:

SELECT user_id FROM tbl  WHERE user_id > $last_user_id
    ORDER BY user_id  LIMIT 1;

To find the 100th newest:

SELECT dt FROM tbl  WHERE user_id = $user_id
    ORDER BY user_id DESC  LIMIT 100,1

If that comes back empty, there is nothing to delete for this $user_id.  Else, use the return value ($dt) and do:

DELETE FROM tbl  WHERE user_id = $user_id  AND dt < $dt;

It might work to combine the SELECT and DELETE thus:

DELETE FROM tbl  WHERE user_id = $user_id
    ORDER BY user_id DESC  LIMIT 100, 99999

That says skip 100, then delete the next 99990 rows (working backward).

I have more discussion here .

(I have successfully implemented a similar script in a 200GB table.)


Calculating a percentage discount in SQL

This is not really database administration. The way you do it cannot be done in most programming languages too. Calculate the discount as 95% of the drink price:

2.75*0.95


How to make a trigger that will compare input with a value of other table?

SQL Server Answer

CREATE TRIGGER FightNumberExists ON dbo.Bookings
AFTER INSERT
AS
IF EXISTS (SELECT fk_flight_number
           FROM inserted i
           INNER JOIN Flights f
              ON f.flight_number = i.fk_flight_number
           WHERE i.date_of_reservation < f.date_of_departure
      )
BEGIN
    RAISERROR ('Flight Number does not exist', 16, 1);
    ROLLBACK TRANSACTION;

    RETURN
END;

GO

Notes
In SQL Server the values are first loaded into the 'inserted' or 'deleted' table before being written to the physical table.

You can accomplish this with adding constraints to the tables as well.


Performance refactoring: Trying to avoid the table scan

There are a few things that make this challenging. If you aren't careful the NULL checks can prevent index seeks. Also, when the columns are NULL you obviously can't search against them. So if IP1 is NULL then the four column index idx_2 won't be very useful. It doesn't really seem to be possible to define one index that will be selective for any combination of NULL variables. Also, SQL Server cannot continue an index seek after it seeks on an inequality predicate:

  In the same way, if we have an index on two columns, we can only use
  the index to satisfy a predicate on the second column if we have an
  equality predicate on the first column.

That means that tricks that use the bounds of the TINYINT data type aren't likely to be effective, such as the following:

a.IP1 >= NULLIF(ip.IP1, 0) AND a.IP1 <= NULLIF(ip.IP1, 255)

In addition to that, the strategy that I'm using seems to work much better with the new cardinality estimator introduced in SQL Server 2014 and your question is tagged with SQL Server 2008.

I strongly recommend splitting up the table variable into rows and processing one row at a time. This is mostly to deal with the NULL values and because your comments hinted that most of the time you only get one row. As long as performance is good enough for one row and you don't have too many rows it should be fine. If that isn't acceptable perhaps you can check if any of the rows in the temp table (don't use a table variable) are NULL and branch your code in that case.

With all of that said, it appears to be possible to get very good performance without a clustered index scan as long as at most two of the IP address pieces aren't NULL. When three of them are NULL you're getting back most of the table and at that point it probably makes sense to do a clustered index scan.

Below is the data that I mocked up to test various solutions. I generated 100 million IP addresses by randomly picking an integer between 0 and 255 for each piece. I know that IP address distribution in real life isn't quite that random but didn't have a way to generate better data.

CREATE TABLE [dbo].[TableA](
    [TableAID] [bigint] NOT NULL,
    [UserID] [int] NOT NULL,
    [IP1] [tinyint] NOT NULL,
    [IP2] [tinyint] NOT NULL,
    [IP3] [tinyint] NOT NULL,
    [IP4] [tinyint] NOT NULL
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED
( [TableAID] ASC )
);

-- insert 100 million random IP addresses
INSERT INTO [dbo].[TableA] WITH (TABLOCK)
SELECT TOP (100000000)
      ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 10000
    , ABS(BINARY_CHECKSUM(NEWID()) % 256)
    , ABS(BINARY_CHECKSUM(NEWID()) % 256)
    , ABS(BINARY_CHECKSUM(NEWID()) % 256)
    , ABS(BINARY_CHECKSUM(NEWID()) % 256)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3;

CREATE TABLE [dbo].[TableB] (
    [UserID] [int] NOT NULL,
    FILLER VARCHAR(100),
    PRIMARY KEY (UserId)
);

-- insert 10k users
INSERT INTO [dbo].[TableB] WITH (TABLOCK)
SELECT TOP (10000) -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

Notice how I didn't create either of the nonclustered indexes that you already have. Instead I'll create one index for each IP piece:

CREATE NONCLUSTERED INDEX [idx_IP1] ON [dbo].[TableA] ([IP1] ASC);
CREATE NONCLUSTERED INDEX [idx_IP2] ON [dbo].[TableA] ([IP2] ASC);
CREATE NONCLUSTERED INDEX [idx_IP3] ON [dbo].[TableA] ([IP3] ASC);
CREATE NONCLUSTERED INDEX [idx_IP4] ON [dbo].[TableA] ([IP4] ASC);

The space required for the indexes is not insignificant. Here is the comparison:

╔═══════════╦═════════════╗
║ IndexName ║ IndexSizeKB ║
╠═══════════╬═════════════╣
║ idx_1     ║     1786488 ║
║ idx_2     ║     1786480 ║
║ idx_IP1   ║     1487616 ║
║ idx_IP2   ║     1487616 ║
║ idx_IP3   ║     1487632 ║
║ idx_IP4   ║     1487608 ║
║ PK_TableA ║     2482056 ║
╚═══════════╩═════════════╝

If necessary you can weigh the pros and cons of using row or page compression to reduce the size of the indexes. However, if you don't know which IP pieces will be NULL and you need to avoid a clustered index scan then I don't see a better alternative to the four indexes. The strategy that I'm going to employ is called an index join. The nonclustered index includes the clustered key TableAID which makes it possible to join the indexes together. Each index should have a selectivity of around 0.4% and it should be relatively cheap to find those rows with a nonclustered index seek. Joining together all of the indexes should greatly reduce the result set and at that point you can do clustered index seeks against the table to get the other column values that you need such as UserID.

Here's the query:

DECLARE @ip1 TINYINT = ?;
DECLARE @ip2 TINYINT = ?;
DECLARE @ip3 TINYINT = ?;
DECLARE @ip4 TINYINT = ?;

SELECT DISTINCT a.UserID, a.IP1, a.IP2, a.IP3, a.IP4
FROM [dbo].[TableA] a
JOIN [dbo].[TableB] b ON b.UserID = a.UserID
WHERE
((@ip1 is NULL) OR (@ip1=a.IP1)) AND
((@ip2 is NULL) OR (@ip2=a.IP2)) AND
((@ip3 is NULL) OR (@ip3=a.IP3)) AND
((@ip4 is NULL) OR (@ip4=a.IP4))
OPTION (RECOMPILE, QUERYTRACEON 9481);

With the RECOMPILE hint I'm taking advantage of the parameter embedding optimization. This optimization is only available with a certain service pack (SP4?) so make sure that you're patched up. The query optimizer is able to split up the single table access on TableA into an index join if it sees fit. Note that the estimated plan is very likely to be misleading here. You want to pay attention to the actual plan.

The QUERYTRACEON 9481 hint should not be included in your version of the query. I'm using it to force SQL Server to use the legacy CE which is only necessary because I'm testing against SQL Server 2016.

Let's run a few tests. With the following parameters:

DECLARE @ip1 TINYINT = 1;
DECLARE @ip2 TINYINT = 102;
DECLARE @ip3 TINYINT = 234;
DECLARE @ip4 TINYINT = 172;

I get merge join and loop joins.

With the following parameters:

DECLARE @ip1 TINYINT = NULL;
DECLARE @ip2 TINYINT = 102;
DECLARE @ip3 TINYINT = 234;
DECLARE @ip4 TINYINT = 172;

I get a very similar plan, except that the index on IP1 is not used in the query. The query still finishes in around 125 ms.

With the following parameters:

DECLARE @ip1 TINYINT = 88;
DECLARE @ip2 TINYINT = NULL;
DECLARE @ip3 TINYINT = NULL;
DECLARE @ip4 TINYINT = NULL;

The query finishes in around 5 seconds. I get a hash join along with a clustered index scan. This isn't necessarily a bad thing but can be avoided with some effort (see the following paragraph):

In case there is a need to effectively force index joins (perhaps SQL Server 2008 is missing an optimization that I'm taking advantage of), it can be done, but it is much more complicated.


Schema design in Cassandra, for efficient tables, will grate against your RDBMS experience; for efficiency, the Cassandra prefers denormalization, not normalization.  By this, I mean that if you have some user information and you want to look up that data using two different primary keys, then using Cassandra, it actually is better to use two tables (and duplicate the data).  Yes, this means more storage space, but it also allows for faster reads.

As a side note, based on my own experiences, I would recommend against using a secondary index, and instead simply use another table.  Secondary indexes in Cassandra are treated a little differently, with background threads which update the indexes periodically; this makes reading from an index not quite as reliable (i.e. more likely to surprise you, in a not good way) than just using a table.

Thus I would recommend something like the following two tables for your needs:

CREATE TABLE users (
  id TIMEUUID PRIMARY KEY,
  user UUID,
  friend UUID
);

CREATE TABLE friends (
  id TIMEUUID,
  user UUID,
  friend UUID,
  PRIMARY KEY (user, friend)
);

This second table would let you do your CQL query:

SELECT * FROM friends WHERE user = ?

Notice that this friends table uses a compound primary key.  This allows there to be multiple friend values associated with that single user value.

One of the downsides of this multiple-table approach is that your application code now has to be responsible for writing into both tables for a single "update", and you have to deal with any potential skew/reconciliation.  Cassandra achieves its performance, in many ways, by avoiding enforcing of foreign key constraints and such and leaving that to the application.

Hope this helps!


I need to use a permenent table instead of temporary but I still need the table's data to be private to the connection

Dynamically create a table name of

CONCAT("T_", CONNNECTION_ID())

With that, create the table.  (You cannot use "binding" to do so; you must create the entire CREATE TABLE string.)


How to set simple parameterization for a json_value query (without using plan guides)?

So far I have collected the following workarounds:

Wrap the json_value function in a dummy subquery. For example,

select  mt.pk

from    My_Table mt

where   (select json_value(mt.json_data, '$.group_id')) != ''

Per David's first comment, try "Optimize for Ad Hoc Workloads" instead of forced parameterization. I haven't tested this, but it's plausible since the problem is forced parameterization.
Per David's second comment, execute the SQL statement dynamically in another database, and reference the original db with 3 part naming. I have confirmed that this works. For example,

exec tempdb.sys.sp_executesql N'select * from TargetDB.dbo.t where json_value(doc, ''$.group_id'') = '''' '


I think I got it.

Here is the table as it appears in Linux from the Chat Session

-rw-rw----. 1 mysql mysql 9478 Jul 30 07:18 /var/lib/mysql/bsgtran/t2010_08.frm
-rw-rw----. 1 mysql mysql 1882892084 Jul 30 07:21 /var/lib/mysql/bsgtran/t2010_08.myd
-rw-rw----. 1 mysql mysql 74588160 Jul 30 07:22 /var/lib/mysql/bsgtran/t2010_08.myi

Since the database is on a Linux OS, filenames are case-sensitive.

Here is the solution

mv /var/lib/mysql/bsgtran/t2010_08.myd /var/lib/mysql/bsgtran/t2010_08.MYD
mv /var/lib/mysql/bsgtran/t2010_08.myi /var/lib/mysql/bsgtran/t2010_08.MYI

File extensions must be uppercase for MyISAM tables in Linux

Give it a Try !!!


Continuous replication between Oracle Database and Azure SQL Database

Oracle Golden Gate has always supported replication between Oracle and SQL Server. For a project I recently confirmed that Golden Gate for Oracle 10 is still available if you push a bit.  If you are using an Oracle version that is out of support then you need to update for a number of reasons.

Synchronization with Azure is detailed here:

Configuring Oracle GoldenGate for Azure


Mariadb galera cluster master node started and reporting 2 nodes but second node service says activating mariadb

I had the same issue with 2 nodes configured. mariadb-10-1-galera-cluster-error.
When using Galera Cluster, it’s recommended that the cluster have at least three nodes. In a two-node cluster, when there is a conflicting pair of transactions, it could lead to a split-brain situation. Also, when one node fails, the remaining node will become non-operational. (Two-Node Clusters)


Yes, you can use the $lookup operator as part of an aggregation, after you $unwind the batches array. For example:

db.collectionA.aggregate[

  { $unwind: "$batches" },

  { $lookup:
    from: "collectionB",
    localField: "batches",
    foreignField: "_id",
    as: "collectionB"
  }
]


Grant privileges to all users on a single table

There is a way to do it in previous versions of MySQL referring to anonymous users with the following query, however I think it does not work for your version of MySQL.

GRANT SELECT ON database_name.CUSTOMER TO '' @ '%';

I recommend you create a ROLE and assign this role to all users, after that you can assign a permission to that ROLE and it will have an effect on all the users to whom it is assigned. Administration in this way is much easier. For example:

CREATE ROLE r1;
GRANT r1 TO 'user1' @ '%', 'user2' @ '%', 'user3' @ '%' .....;
GRANT SELECT, INSERT, UPDATE ON database_name.CUSTOMER TO r1;


Why must TVPs be READONLY, and why can't parameters of other types be READONLY

The explanation seems to be tied to a combination of: a) a detail from the linked blog that was not mentioned in this question, b) the pragmatics of TVPs fitting within how parameters have always been passed in and out, c) and the nature of table variables.

The missing detail contained in the linked blog post is exactly how variables are passed in and out of Stored Procedures and Functions (which relates to the phrasing in the Question of "a safer version of pass-by-reference if they are OUTPUT parameters"):

  TSQL uses a copy-in/copy-out semantics to pass parameters to stored procedures and functions....

  ...when the stored proc finishes executing (without hitting an error) a copy-out is made which updates the parameter passed in with any changes that were made to it in the stored proc.

  The real benefit of this approach is in the error case.  If an error occurs in the middle of a stored procedure's execution, any changes made to parameters will not propagate back to the caller.

  If the OUTPUT keyword is not present no copy-out is made.

  The bottom line:
  Parameters to stored procs never reflect the partial execution of the stored proc if it encountered an error.

Part 1 of this puzzle is that parameters are always passed "by value". And, it is only when the parameter is marked as OUTPUT and the Stored Procedure completes successfully that the current value is actually sent back. If OUTPUT values were truly passed "by reference", then the pointer to the location in memory of that variable would be the thing that was passed, not the value itself. And if you do pass in the pointer (i.e. memory address) then any changes made are immediately reflected, even if the next line of the Stored Procedure causes an error and it aborts execution.

To sum up Part 1: variable values are always copied; they are not referenced by their memory address.
With Part 1 in mind, a policy of always copying variable values can lead to resource issues when the variable being passed in is quite large. I have not tested to see how blob types are handled (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), XML, and those that should not be used anymore: TEXT, NTEXT, and IMAGE), but it is safe to say that any table of data being passed in could be quite large. It would make sense for those developing the TVP feature to desire a true "pass-by-reference" ability to prevent their cool new feature from destroying a healthy number of systems (i.e. to want a more scalable approach). As you can see in the documentation that is what they did:

  Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data.

Also, this memory management concern was not a new concept since it can be found in the SQLCLR API that was introduced in SQL Server 2005 (TVPs were introduced in SQL Server 2008). When passing NVARCHAR and VARBINARY data into SQLCLR code (i.e. input parameters on the .NET methods within a SQLCLR Assembly), you have the option to go with the "by value" approach by using either SqlString or SqlBinary respectively, or you can go with the "by reference" approach by using either SqlChars or SqlBytes respectively. The SqlChars and SqlBytes types allow for full streaming of the data into the .NET CLR such that you can pull small chunks of large values as opposed to copying an entire 200 MB (up to 2 GB, right) value.

To sum up Part 2: TVPs, by their very nature, would have a propensity to consume a lot of memory (and hence deteriorate performance) if staying within the "always copy the value" model. Hence TVPs do a true "pass by reference".
The final piece is why Part 2 matters: why would passing in a TVP truly "by reference" instead of making a copy of it change anything. And that is answered by the design goal that is the basis for Part 1: Stored Procedures that do not complete successfully should not alter, in any way, any of the input parameters, whether they are marked as OUTPUT or not. Allowing DML operations would have an immediate affect on the value of the TVP as it exists in the calling context (since passing by reference means you are changing the thing that was passed in, not a copy of what was passed in).

Now, someone, somewhere, is at this point probably talking to their monitor saying, "Well, just build in an automagic facility for rolling back any changes made to TVP parameters if any were passed into the Stored Procedure. Duh. Problem solved." Not so fast. This is where the nature of Table Variables comes in: changes made to Table Variables are not bound by Transactions! So there is no way to roll the changes back. And in fact, this is a trick used to save info generated within a transaction if there needs to be a rollback :-).

To sum up Part 3: Table-Variables do not allow for "undoing" changes made to them in the case of an error that causes the Stored Procedure to abort. And this violates the design goal of having parameters never reflecting the partial execution (Part 1).

Ergo: the READONLY keyword is needed to prevent DML operations on TVPs since they are Table Variables that are actually passed "by reference", and hence any modifications to them would be immediately reflected, even if the Stored Procedure encounters an error, and there is no other way to prevent that.

Additionally, parameters of other datatypes can't use READONLY because they are already copies of what was passed in, and so it wouldn't protect anything that isn't already protected. That, and the way that parameters of the other datatypes work was intended to be read-write, so it would probably be even more work to alter that API to now include a read-only concept.


Override default SERVICE_NAME in init.ora

This is the default behaviour.
As long as you don't have the default service in your tns_aliasses that are in use it should not be a problem.


How to renew or extend the CMK Certificate in Always Encrypted feature?

Always encrypted does not check for validity of the CMK certificate explicitly. Always Encrypted treats the CMK certificate purely as (PK, SK) pair.  so you can use the expired cert without running into any issues. However, the best practice is to rotate the CMK at regular intervals. Always Encrypted provides a mechanism to rotate CMK without incurring any application downtime. Please read the following official articles for details.
Article 1
Article 2

How to do CMK rotation without powershell:

Provision a new CMK (CMK2) (Create a new Certificate or create a new AKV key)
Use an appropriate instance of SqlColumnEncryptionStoreProvider.DecryptColumnEncryptionKey(String, String, Byte[]) method to decrypt your current encrypted CEK (CEK1) value (CekVal1)
Use an appropriate instance of SqlColumnEncryptionStoreProvider.EncryptColumnEncryptionKey(String, String, Byte[]) method to encrypt the cek with the new CMK (CMK2) to get new encrypted CEK value (CekVal2).
Use Create Column Master Key T-SQL to specify the new CMK (CMK2)
Use Alter Column encryption key T-SQL to ADD CekVal2 to your current CEK (CEK1)
At this point, your clients will be able to decrypt CEK1 with both CMK1 and CMK2.
After you have successfully distributed CMK2 to all clients that need it, Use Alter Column encryption key T-SQL to DROP CekVal1 from your current CEK (CEK1)
After this point, your clients will no longer be able to decrypt CEK1 using CMK1


How do I "link" two or more rows of the same table with a unique "link" id?

If the ID in your 'link' table absolutely has to be unique, and if it's ALWAYS a link between two IDs, you could use 3 columns: ID, min_position, max_position. That would allow you to use joins with your positions table.

I'm a bit confused regarding the goal of your application, i must admit ;).


After Rackspace server creation, PostgreSQL query planner doesn't work as expected

I work for Rackspace and would like to help. Can you please post some details on exactly how the image was created? Are these both listed in your control panel as "next-gen servers" or is one "first-gen"? Differences here may explain some of the performance discrepancies you're experiencing.

It would be extremely helpful if you could provide query plans from both servers (http://www.postgresql.org/docs/current/static/performance-tips.html#using-explain).

A tool like explain.depesz (http://explain.depesz.com/help) may also help you to identify whether the queries are performing the same way on both machines.

Feel free to post your answers here for the community to help (if you are comfortable) - or drop me a line at shaggy@rackspace.com with details and I'll be happy to coordinate with your support team.


How to implement a dynamic referral program?

Separate data and business logic.

A database is a repository for data, so put those numbers in a table (let's call it Creds), possibly with date ranges of when they are applicable.

Your application code is where you build the "business logic" that interprets "referrer", "referree", "first time", etc, based on the data.

For example "first time" would come from some other table that, for example, has the entire list of "referred spends" for a user.  You would write a suitable query to determine "first time", then use Creds to and the current date to decide how much credit to give a user.


I had some changes in that table and it got executed. mainly the primary key. pl go through the same,

    mysql> CREATE TABLE Student (
        ->     StudentID INT,
        ->     Fname VARCHAR(30),
        ->     Lname VARCHAR(30),
        ->     DOB DATE,
        ->     Major VARCHAR(10),
        ->     PRIMARY KEY (StudentID)) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.10 sec)

    mysql> CREATE TABLE Professor (
        ->     Emp_no INT,
        ->     FName VARCHAR(10),
        ->     LName VARCHAR(10),
        ->     DeptCode VARCHAR(2),
        ->     PRIMARY KEY (Emp_no)) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.14 sec)

    mysql> CREATE TABLE Phone (
        ->     StudentID INT,
        ->     PNumber VARCHAR(20),
        ->     Type VARCHAR(5),
        ->     FOREIGN KEY (StudentID) REFERENCES Student(StudentID) ON DELETE CASCADE
        ->     ) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE Class (
    ->     ClassID VARCHAR(5),
    ->     NoCredits INT,
    ->     Description VARCHAR(30),
    ->     Requisite1 VARCHAR(6),
    ->     Requisite2 VARCHAR(6),
    ->     Requisite3 VARCHAR(6),
    ->     PRIMARY KEY (ClassID)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.10 sec)

            mysql> CREATE TABLE Section (
                ->     ClassID VARCHAR(5),
                ->     SecNo VARCHAR(10),
                ->     Semester VARCHAR(4),
                ->     ClassRoom VARCHAR(6),
                ->     TimeOffered VARCHAR(20),
                ->     Emp_no INT,
                -> PRIMARY KEY (SecNo, Semester),
                -> FOREIGN KEY (ClassID) REFERENCES Class(ClassID),
                ->     FOREIGN KEY (Emp_no) REFERENCES Professor(Emp_no)
                ->     ) ENGINE=InnoDB;
            Query OK, 0 rows affected (0.13 sec)

            mysql> CREATE TABLE QualToTeach (
                -> ID varchar(5),
                -> ClassID VARCHAR(5),
                ->     Emp_no INT,
                ->     PRIMARY KEY (ID),
                -> FOREIGN KEY (ClassID) REFERENCES Class(ClassID),
                ->     FOREIGN KEY (Emp_no) REFERENCES Professor(Emp_no)) ENGINE=InnoDB;
            Query OK, 0 rows affected (0.13 sec)

            mysql> CREATE TABLE Enrolled (
                ->     StudentID INT,
                ->     ClassID VARCHAR(5),
                ->     SecNo VARCHAR(10),
                ->     Semester VARCHAR(4),
                ->     Grade CHAR(2),
                ->     DropDate DATE,
                -> Enrolled_id INT,
                -> PRIMARY KEY (Enrolled_id),
                -> FOREIGN KEY (ClassID, SecNo, Semester) REFERENCES Section(ClassID, SecNo, Semester),
                ->     FOREIGN KEY (StudentID) REFERENCES Student(StudentID))
                ->     ENGINE=InnoDB;
            Query OK, 0 rows affected (0.19 sec)


Managing Transaction Log Size using a Maintenance Plan

If your log file is growing that large then you are doing at least one of these things wrong:

if you need point-in-time recovery, then you are not backing up the log frequently enough (and perhaps the database too). Perhaps you have created a maintenance plan to back up the database daily or weekly, but haven't configured a maintenance plan that backs up the log.
if you don't need point-in-time recovery, then you are in full recovery model but shouldn't be. You should be able to switch the recovery model for your database(s) to simple. This will mean that you will only be able to recover to the time of the last full or differential backup, but it also means your log file will stay in check on its own.
in either recovery model, the problem may be that you have an extremely long-running transaction. As in, someone called BEGIN TRANSACTION, locked their workstation, and went on vacation.

A lot more details about this can be seen in this question:

Why Does the Transaction Log Keep Growing or Run Out of Space?

In order to shrink the log for now, if you are in full recovery model and want to stay that way, you should perform these steps and restart your log chain to be safe:

USE [master];
GO
ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- if you are already in simple, remove this line:
ALTER DATABASE db_name SET RECOVERY SIMPLE;
GO
CHECKPOINT;
GO
-- if you're already in simple, or want to stay that way, remove this line:
ALTER DATABASE db_name SET RECOVERY FULL;
GO
BACKUP DATABASE db_name TO DISK = 'path' WITH INIT, COMPRESSION;
GO
USE db_name;
GO
DBCC SHRINKFILE(N'db_name_log', 30); -- pick some size that makes sense
GO
USE [master];
GO
-- if recovery is full, re-init log chain, otherwise remove this line:
BACKUP LOG db_name TO DISK = 'path' WITH INIT, COMPRESSION;
GO
ALTER DATABASE db_name SET MULTI_USER;
GO

I commented lines that should be removed if you're already in simple (highly unlikely) or if you want to switch to simple.


SSIS Package Duration is big before it begins executing

I think it's hard to predict where those 2 extra seconds are coming from without seeing your SSIS Package and how you're running it. A rough guess is it's some additional runtime that added up from starting the package execution + starting execution of each step. I wouldn't worry much about it unless you start seeing huge discrepancies (but if it's consistently off by a few seconds, then I would lean to what I previously guessed).
According to this StackOverflow, those reports aren't the most accurate way to track and analyze execution times too: SSIS job taking 'hours' -- All Executions report not adding up or showing errors -- where to find cause?
Specifically see Nick.McDermaid's comments:

Everything you need should be in the logs / reports. No need to run it locally. It's a
pain, introduces network traffic, is not a true representation and
doesn't give you much more information. There is a package 'total run
time' in the top level report (displayed in seconds).

Here are some alternative ways you can track SSIS Package execution times: Tracking Execution Times for SSIS Packages


Add unique constraint on combination of 2 columns

A constraint like:

UNIQUE(cartID, productID)

means that the combination of cartID and productID is unique, not the individual columns. It is however possible that something in your syntax makes MySQL ignore the constraint. Can you try the following:

CREATE TABLE Orders
( cartID int not null
, productID int not null
, unique (cartID, productID) );

insert into Orders (cartID, productID) values (10, 7), (10,8), (11,7),(11,7);
ERROR 1062 (23000): Duplicate entry '11-7' for key 'cartID'

insert into Orders (cartID, productID) values (10, 7), (10,8), (11,7);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0


What type of SQL Server Replication Allows Modifications

I would suggest Transactional Replication.  This will be more of a "Real-Time" copy of the data than the one refresh per day that you have now.  You can add new tables into the database without affecting the replicated tables.


PostgreSQL randomising combinations with LATERAL

I'd write the query like this, using LIMIT (3) instead of DISTINCT ON.

The generate_series(0, 9) is used to get all the distinct bins. You could use (SELECT DISTINCT line % 10 FROM foo) AS g (bin) instead, if the "bins" are not all the integers from 0 up to 9:

SELECT
    g.bin,
    ROW_NUMBER() OVER (PARTITION BY g.bin ORDER BY d.x) AS i,
    d.*
FROM
    generate_series(0, 9) AS g (bin),
  LATERAL
    ( SELECT f.*, random() x
      FROM foo AS f
      WHERE  f.line % 10 = g.bin
      ORDER BY x
      LIMIT 3
    ) AS d
ORDER BY
    bin, x ;

Also, if you don't need the random() number in the output, you could use ORDER BY random() in the subquery and remove x from the select and order by clauses - or replace ORDER BY d.x with ORDER BY d.line.


SQL Server Index, difference between Average fragmentation and Total fragmentation

There's a big difference between your query and the query the UI sends (which you can spy on using server-side trace, Extended Events, etc). In yours, the last argument to sys.dm_db_index_physical_stats is DETAILED:

... sys.dm_db_index_physical_stats( ..., 'DETAILED') ...

Whereas the UI is on a budget and sends SAMPLED instead:

... sys.dm_db_index_physical_stats( ..., 'SAMPLED') ...

DETAILED scans all of the pages, while SAMPLED only scans 1 percent. That's right, 1%. You pay for more accuracy obviously, but relying on the info about every 100th page to make any judgments about the state of the index is, well, not very useful. You could always change your manual query to SAMPLED if your goal is to just match the uselessness of the UI. :-)

(In fairness, this topic about the UI properties page should explicitly document the method that is used to determine fragmentation. I have a confirmation from Microsoft's Pedro Lopes that he will keep it in mind for an upcoming doc revision.)

Just never use or trust the UI. Never mind that it's a pain in the rear to look at more than one index at a time, it's also cumbersome to figure out what it's even doing, and you always have to worry about whether it is still doing what you used to think it did... it's just not worth the occasional expediency in looking up one thing rather than having a verifiable, repeatable query to run...


In SQL Server you can do this with a recursive CTE, but unfortunately this isn't supported by mySQL. Instead, you need a recursive function/sp which calls itself to get the full tree, such as the following:

set @@session.max_sp_recursion_depth=255;

delimiter $$

create procedure get_bu_tree (
   in parent int
)
begin
    create table if not exists temp_bu_tree_ids (id int);
    set @i = (select count(1) from temp_bu_tree_ids);
    if @i = 0 then insert into temp_bu_tree_ids values (parent); set @i = 1; end if;

    insert into temp_bu_tree_ids (id)
    select bu_id
    from bu
    inner join temp_bu_tree_ids as t
    on bu.parent_id = t.id
    where bu_id not in (select id from temp_bu_tree_ids);

    if (select count(1) from temp_bu_tree_ids) > @i
    then
        call get_bu_tree ((select bu_id from bu where parent_id = parent));
    else
        select bu.*
        from bu
        inner join temp_bu_tree_ids as t
        on bu.bu_id = t.id;

        drop table temp_bu_tree_ids;
    end if;
end$$

delimiter ;

call get_bu_tree (1);

drop procedure get_bu_tree;

Note the call get_bu_tree (1); where 1 is the parent id for which you want to retrieve the children. Also note that this creates and drops a table (temp_bu_tree_ids), check for conflicts!


MySQL determine day of week when working with multiple date fields

What you're doing is fine as far as a working solution goes, and helps avoid using functions to calculate the day of the week in memory every time the query runs. But MySQL has built in functions to calculate it as well which is just as fine of a solution too.
For example, the following returns the week day index using MySQL's WEEKDAY() function:
CASE
WHEN dl.date_inserted IS NOT NULL
THEN WEEKDAY(dl.date_inserted)
ELSE WEEKDAY(dl.entry_date)
END AS day_of_the_week

If you want the name of the week day, then you can use the DAYNAME() function instead.


generate_series for multiple record types in postgresql. Fill missing with last previous value

Example data with two rows added:

insert into pest_counts(pest_id, date, count) values
    (1, '2015-01-03', 10),
    (2, '2015-01-04', 7);

select * from pest_counts;

 id | pest_id |    date    | count
----+---------+------------+-------
  1 |       1 | 2015-01-01 |    14
  2 |       2 | 2015-01-02 |     5
  3 |       1 | 2015-01-03 |    10
  4 |       2 | 2015-01-04 |     7
(4 rows)

Use count() as a window function to generate groups. Each not null value starts a new group:

select name, day, count, count(count) over (partition by name order by day) as grp
from (
    select id, name, day::date
    from pests
    cross join generate_series('2015-01-01'::timestamp, '2015-01-04', '1 day') day
) d
left join pest_counts on d.id = pest_id and day = date;

     name     |    day     | count | grp
--------------+------------+-------+-----
 fungus gnats | 2015-01-01 |       |   0
 fungus gnats | 2015-01-02 |     5 |   1
 fungus gnats | 2015-01-03 |       |   1
 fungus gnats | 2015-01-04 |     7 |   2
 thrip        | 2015-01-01 |    14 |   1
 thrip        | 2015-01-02 |       |   1
 thrip        | 2015-01-03 |    10 |   2
 thrip        | 2015-01-04 |       |   2
(8 rows)

Now you can use sum() as a window function over partitions by names and generated groups:

select name, day, coalesce(count, sum(count) over (partition by name, grp order by day), 0) as count
from (
    select name, day, count, count(count) over (partition by name order by day) as grp
    from (
        select id, name, day::date
        from pests
        cross join generate_series('2015-01-01'::timestamp, '2015-01-04', '1 day') day
    ) d
    left join pest_counts on d.id = pest_id and day = date
) s;

     name     |    day     | count
--------------+------------+-------
 fungus gnats | 2015-01-01 |     0
 fungus gnats | 2015-01-02 |     5
 fungus gnats | 2015-01-03 |     5
 fungus gnats | 2015-01-04 |     7
 thrip        | 2015-01-01 |    14
 thrip        | 2015-01-02 |    14
 thrip        | 2015-01-03 |    10
 thrip        | 2015-01-04 |    10
(8 rows)


SQL Server 2017 & External Python Modules

Just in case someone finds this question when searching the net

I logged an advisory case with MS as I needed an answer on this. They have come back to me with the following method that works:

Open a command prompt (as administrator) and navigate to the python directory (and inside the scripts directory)

cd c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\Scripts\Python

pip install <packagename>

Nb: There is a cleaner way to do this with R packages, but this functionality is not there for python yet. I would assume in later versions that a similar process will be available for python: https://docs.microsoft.com/en-us/sql/advanced-analytics/r/install-additional-r-packages-on-sql-server


ORA-12516 but max_utilization not reaching limit

I think select 1 from dual; is a syntax error. Here's what happens on the wire:

With select 1 from dual a connection's lifecycle is:

Initial establishment
First query (validation does not yet happen)
Return to the pool
Validation: the pool sends select 1 from dual
The database answers: ORA-01403: no data found
Next query

and so on. The connection is normally not closed.

With select 1 from dual; however, the following happens:

Initial establishment
First query
Return to the pool
Validation: the pool sends select 1 from dual;
The database answers: ORA-00911: invalid character
The pool terminates the connection with a normal FIN packet.

The next query then begins with a freshly established connection. Now, as long as the load stays low, you might not even notice this. But under high load, all this reconnecting must exhaust some database resource. Which one, i don't know...


Why do you need to convert the average into a varchar?  Also, why do you have two extra SELECT * FROM (...) AS T0 wrapping the main select statement?

This runs without error:

USE tempdb;

CREATE TABLE [dbo].[tblQuestaoOpcao](
    [CodQuestaoOpcao] [int] IDENTITY(1,1) NOT NULL,
    [CodNRQuestao] [int] NULL,
    [Opcao] [int] NULL,
    [DescOpcao] [varchar](5000) NULL,
 CONSTRAINT [PK_tblQuestaoOpcao] PRIMARY KEY CLUSTERED
(
    [CodQuestaoOpcao] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF
    , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
    , FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO dbo.tblQuestaoOpcao(CodNRQuestao, Opcao, DescOpcao)
VALUES (811962, 2, 'CodOpcao_1')
    , (811757, 3, 'CodOpcao_2')
    , (812009, 3, 'CodOpcao_3')
    , (812011, 3, 'CodOpcao_4')

SELECT *
FROM (
    SELECT 39 as codigo
        , (ROW_NUMBER() over
            (
            PARTITION by T1.CodNrQuestao
            ORDER BY T1.CodNrQuestao
            )
        ) as ID
        , 0 as Grupo
        , T1.Opcao as CodOpcao_1
        , T2.Opcao as CodOpcao_2
        , T3.Opcao as CodOpcao_3
        , T4.Opcao as CodOpcao_4
    From tblQuestaoOpcao T1
        Cross Join tblQuestaoOpcao T2
        Cross Join tblQuestaoOpcao T3
        Cross Join tblQuestaoOpcao T4
    Where T1.codNrQuestao = 811962
        And T2.CodNRQuestao = 811757
        And T3.CodNRQuestao = 812009
        And T4.CodNRQuestao = 812011
)  as T0
UNPIVOT (
[CodOpcao] FOR [DescQuestao] IN (
[CodOpcao_1]
, [CodOpcao_2]
, [CodOpcao_3]
, [CodOpcao_4]
)
) AS U
PIVOT ( -- from here I start to get errors
    AVG(codOpcao)
    FOR descQuestao IN (
        CodOpcao_1
        , CodOpcao_2
        , CodOpcao_3
        , CodOpcao_4
        )
) AS U;

Results:

Perhaps I really don't understand what you are attempting to do, or perhaps this is just a code fragment, or perhaps my sample data is incorrect, however the following query returns the same result as the above PIVOT / UNPIVOT:

SELECT 39 as codigo
    , (ROW_NUMBER() over
        (
        PARTITION by T1.CodNrQuestao
        ORDER BY T1.CodNrQuestao
        )
    ) as ID
    , 0 as Grupo
    , T1.Opcao as CodOpcao_1
    , T2.Opcao as CodOpcao_2
    , T3.Opcao as CodOpcao_3
    , T4.Opcao as CodOpcao_4
From tblQuestaoOpcao T1
    Cross Join tblQuestaoOpcao T2
    Cross Join tblQuestaoOpcao T3
    Cross Join tblQuestaoOpcao T4
Where T1.codNrQuestao = 811962
    And T2.CodNRQuestao = 811757
    And T3.CodNRQuestao = 812009
    And T4.CodNRQuestao = 812011

This can be further simplified by removing the CROSS JOINs and performing a PIVOT, as per this:

SELECT *
FROM (
SELECT 39 as codigo
    , (ROW_NUMBER() over
        (
        PARTITION by T1.CodNrQuestao
        ORDER BY T1.CodNrQuestao
        )
    ) as ID
    , 0 as Grupo
    , DescOpcao
    , Opcao
FROM tblQuestaoOpcao T1
) pvt
PIVOT (
AVG(Opcao) FOR DescOpcao IN
    (
        [CodOpcao_1]
        , [CodOpcao_2]
        , [CodOpcao_3]
        , [CodOpcao_4]
    )
) u;

Results:


What is the difference between  All and Not In

There is no difference in result but there is a bit different semantics.

X [comparison] ALL(set) mean that set is empty or the comparison is TRUE for each entry in the set.

X NOT IN (set) means that X does not belong to the set.

While [comparison] is "not equal", both forms are equivalent. But for other comparisons it may be different.


How is this SQL Server PK violation possible?

Now I have a SQL statement, which selects the MAX value, adds to it, and INSERTs that value into the same table -- all inside one statement. So in theory, it's not possible to get a PK violation on this statement (although I think could possibly get a deadlock). But, somehow, it IS getting one.

It definitely IS possible. This is due to your isolation level and how locking works.

  Violation of PRIMARY KEY constraint 'PK_IDMaster'. Cannot insert duplicate key in object 'IDMaster'. The duplicate key value is (25309587).

This is due to locking; initially in the read committed isolation level will take a shared lock on the (in this case) key while reading what the maximum value is. After it figures it out, an X lock will be needed to insert into the table. That's the simple overview of it.

When you have a single session, this isn't a problem. When you have multiple sessions this is a huge problem as shared locks are compatible with each other. This means that multiple readers can obtain the same value.

  don't know how this is possible, but apparently it is; because it just happened.

See above.

So How Do You Fix It?

Don't try to be smarter than SQL Server. Use the available constructs that fit the need - in SQL Server 2012 (major version 11) has both identity and sequences. Use them.
Purposely kill your concurrency by either using serializable or lock hints such as XLOCK. This will on purpose block other sessions which means you'll have less performance... and you're doing it on purpose... so yeah that seems bad but a possible solution.
Pre-create values (again trying to be smarter than SQL Server) and use XLOCK + READPAST to get a little better concurrency from a different table. Blah. Let me reiterate #1, don't try to be smarter than SQL Server [in this case].


To respond to your question, if you want to validate multiple conditions for a case statement, you can use coalesce or nest case statements.

However judging by what you want to do, it might be best to handle this within your application.
SQL Server is not made for computing, it's a database, inherently meant to store and produce data. As such it's been highly optimized for just those types of tasks (storing and handling data) and not for handling the data after it's been found.

Quick edit to link MySQL case nesting after noticing you're talking about MySQL not MSSQL.


Cannot connect to MS SQL 2008 R2 by DBVisualizer. "Native SSPI library not loaded" error

This isn't a really widely used tool for SQL Server database administrators.  For better answers, I'd contact DBVisualizer's support.


Backup a hot standby database (streaming replication) whilst it is in recovery

Yes, it can be a good idea.  In fact, allowing this was a major feature in the release of PostgreSQL 9.2.


In the manuel they advise to use port 33061.
https://dev.mysql.com/doc/refman/5.7/en/group-replication-configuring-instances.html :

  "The recommended port for group_replication_local_address is 33061"


Two different MYSQl instances on same machine?

Since I am doing partial recovery on a damaged database (I did an iso image cold backup, but did not give the iso maker the proper options and ended up with DOS 8.1 filenames, when they should have been Joliet Long names) I must have previously used the option --skip-grant .

i used the command
    mysqld_safe --defaults-file=/etc/mysql/mybackup.cnf --skip-grant
and the database started up fine.

Now to continue recovering what I can.


If you want to enable symbolic link then add following line in mysql server configuration file in the section of [mysqld].
    symbolic-links=TRUE
    After adding restart mysql server then you will get show variables like 'have_symlink'; value as YES.


MySQL - Maintain History of Records but Using Most Recent

This is well known problem "Max by group" that can be solved by subselect:

SELECT
       dvr.*,
       v.number,
       v.date_registered,
       d.passport_number,
       d.first_name,
       d.last_name,
       d.dob,
       d.address
  FROM daily_vehicle_reports AS dvr
  JOIN vehicles              AS v  ON v.id = dvr.vehicle_id
  JOIN vehicle_drivers       AS vd ON vd.vehicle_id = v.id

  JOIN ( SELECT vehicle_id                            -- here is additional join with subselect
              , MAX(date_assigned) AS date_assigned   -- last assignment for each vehicle
           FROM vehicle_drivers
          GROUP BY vehicle_id
       ) AS vdm  ON vdm.vehicle_id = vd.vehicle_id    -- joined to the full table `vehicle_driver`
                AND vdm.date_assigned = vd.date_assigned -- to exclude NOT LAST rows

  JOIN drivers               AS d ON d.id = vd.driver_id
 WHERE MONTH(dvr.report_data) = 4      -- Too bad, can't be indexed
   AND  YEAR(dvr.report_data) = 2019   -- The same thing

To speedup the filtering by date you have to use the following approach:

WHERE dvr.report_data BETWEEN '2019-04-01' AND '2019-04-30'

This kind of date range filtering is covered by index (if exists) and way more faster


Use %H instead of %h.

select STR_TO_DATE('03.05.2010 00:00','%d.%m.%Y %H:%i');

Or, if HH:MM are always 00:00 you can use date part only:

select STR_TO_DATE('03.05.2010 00:00','%d.%m.%Y');

Check it here: http://rextester.com/CCPKR29919

As @a_vlad has pointed out on his comments, %H is used to format hours as 00..23 and %h as 01..12.

Have a look at DATE_FORMAT() on MySql docs.


Does @@FETCH_STATUS have (or need) an equivalent to SCOPE_IDENTITY?

As long as they are not nested, you should be fine. @@FETCH_STATUS is set at the time the FETCH NEXT is performed. @@FETCH_STATUS is global to the session, not the entire server.
There is no equivalent to SCOPE_IDENTITY() for @@FETCH_STATUS. If you need to nest looped FETCH NEXT operations, make sure that your outer FETCH NEXT is performed after the inner loop completes. If you need to stack FETCH NEXT operations or perform a FETCH NEXT before an inner loop containing a FETCH NEXT, you can DECLARE a temporary variable of type int outside the loop, SET the temporary variable equal to @@FETCH_STATUS immediately after your FETCH NEXT statement, and then use the temporary variable as your loop control instead of @@FETCH_STATUS.


As you gave no details of what you want to accomplish to base any hints on, I can tell you only the generic stuff:

If it is InnoDB then each secondary key contains the primary key values to find the right rows, so (a,b,d,e) is extended to (a,b,d,e,c) and (d,e) to (d,e,a,b,c) - as you can see, the columns indexed are the same but the order is not, and order of columns in multicolumn index is as important as the actual columns so those two are quite different in their effects.

If it is MyISAM, then the extension does not apply, each index references the row by a pointer to heap, so the indexes are totally different.

There is no universal notion of "better", it depends on what do you need the indexes for. Query speedup? Or are they supposed to be unique?


Do I need to detach a database to attach it?

Unfortunately you can't just copy the "hot" mdf & ldf files, even while no data is actually being written to the DB. As long as the files are attached to a SQL Server you cannot copy or move them. In fact I don't think Windows will let you even copy them as they should be locked by the server process.

The trivial solution is just using backup and restore, it's painless and secure. In fact there should be already be a regular backup process since you mention that this is production system, maybe you don't even need to actually take a backup, just wait for the next one to take place and use it to restore. On SQL Server the backup/restore process is really straightforward, probably one of the easiest on all the RDBMS ecosystem.

Any other method will be more difficult and likely yield worse results. I wouldn't recommend them at all. You could try to copy the files using VSS (Volume Shadow Copy) and see if you get consistent enough files to attach. Alternatively you could create an empty database and just export all the tables, views, procedures, etc.


I want to create store procedure of selecting record of last 3 months from 3 tables

You have several errors in your code:

don't convert timestamps back and forth between strings, dates and timestamps. A simple assignment is enough.
references to variables shouldn't be enclosed in single quotes. 'v_time' is a string, v_time1 is a reference to the variable.
procedures are not intended to return results, you need to use a function declared as returns table (...)

Putting all that together, your function should look like this:
CREATE OR REPLACE function data.select_data_last_three_month()
  returns table (... your column definitions go here ....)
  LANGUAGE plpgsql
AS
$procedure$
declare
  v_time1 timestamp;
Begin

    v_time := current_date - 94;

    select q1.*,q2.*,q3.*
    from
    (select * from public.table1 where time > v_time1 order by time desc) q1, --'2020-05-25 00:00:00'
    (select * from public.table2 > v_time1 order by time desc)q2,
    (select * from public.table3 time > v_time1 order by time desc) q3
    ;

 END;
$procedure$
;

You don't actually need PL/pgSQL for this. A language sql function would be enough.
Then use it like this:
select *
from data.delete_interface_tcp_data_three_month_back();

But I highly doubt, a cross join (cartesian product) between the three tables is what you want. If the three tables have the same structure, then you probably want a UNION ALL.


Non clustered index for non-evenly distributed data in large SQL table

If I get rid of the data representing 95 % of the table, I should have no further trouble filtering the rest of the data.

In that case, separating that 95% of the data is way to go. If you want to stay with SSIS, the obvious solution is to redirect those records to a another flat file (can also be a separate db) during import. Specifying this flat file in SSIS is granular too. That is, you can send malformed data rows to a separate rejects flat file, for example. That way you can have clean SalesType1 file.

To specify which records to put in the flat file, use Conditional Split in SSIS to filter the rows (during import) using the same logic as your Where condition consisting of Manufacturer, MsaiSalesType, and Return Volume.


How to convert timestamp with time zone to numeric (unix) in PostgreSQL?

To solve this, I looked here.

For example, I'm in a time zone which is at UTC + 1,

so,

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC';

gives:

2019-06-06 17:41:00.216647

which is 1 hour behind clock time because I'm on UTC + 1.

So, then from here, I got select extract(epoch from ts) from data

Combining the two gives:

SELECT EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'));

which for me gives:

date_part
1559843103.53928

YMMV - fiddle available here - put your timestampz into the formula for current_timestamp and you should be golden!

Interstingly, you can check your results here.

p.s. welcome to the forum! :-)


Update staging data with production data in Postgresql

Update Oct. 30, 2015: This answer is simpler and more accurate than my old answer, which I removed for the sake of brevity. To see the old answer, take a look at the edit history.

As production user: pg_dump -Fc my_prod_db > prod_dump.db
Drop and create staging database
As staging user: pg_restore --no-owner --dbname my_staging_db path/to/prod_dump.db


In SQL Server 2016 what is sys.tables is_external field?

This is my interpretation ..

SQL Server 2016 allows you to configure POLYBASE - accesses and combines both non-relational and relational data - from SQL Server.

It allows you to run queries on external data in Hadoop or Azure blob storage.

SQL server has a column is_external to identify that the table is external. You can find more on external tables using sys.external_tables catalogue view.


What's the best way to migrate from mariadb to mysql on ubuntu 18.04

Confluence officially supports a number of different database systems, including MySQL 5.6 and 5.7, but not 8.0, and also does not officially support MariaDB.

If you want to migrate an existing Confluence database running on MariaDB to MySQL, then mysqldump is a guaranteed route to success. Just dump the existing database to an SQL file, and then import this into MySQL using e.g. the mysql command-line client. This could be slow if it's a big database.

FWIW, the company I work for has a major Confluence installation on MariaDB 5.5, which is the default in RHEL7/CentOS7, and this has been running for several years with no known issues.


It seems an invalid pointer was caught when doing a full table scan on the Archive engine. I recommend you to post the trace on Percona Server bug tracker. Developers are very friendly even if you are not a client. They will be able to tell you if it is a Percona specific problem or an upstream problem (if they have changed anything on the Archive stored engine).

My own experience with the Archive engine is that, while it is a nice theoretical engine for some workloads, it is currently mostly unmaintained and not ready for production, having astounding bugs. Percona does not recommend it, and if you are concerned about stability, you are probably better served by InnoDB compressed or TokuDB (no matter if you are running PS or stock MySQL).


MySQL trigger: if record exists just update

check if the user has already reviewed the same product. If so, just update the fields score, title, comment and last_modified.

No trigger! Use INSERT .. ON DUPLICATE KEY UPDATE.

INSERT INTO reviews (reviewer,
                     product,
                     score,
                     title,
                     comment,
                     creation_date,
                     last_modified)
VALUES (@reviewer,
        @product,
        @score,
        @title,
        @comment,
        @creation_date,
        @last_modified)
ON DUPLICATE KEY UPDATE score = VALUES(score),
                        title = VALUES(title),
                        comment = VALUES(comment)
                        last_modified = VALUES(last_modified);

Additionally - define creation_date field as DEFAULT CURRENT_TIMESTAMP and last_modified field as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, then remove them from all INSERT/UPDATE queries - they will be updated automatically.


They all grew to 63 gb, and filled up the 500 gb drive
Why can't I release space?

To be clear, the files won't "shrink" back to their previous size on their own while the system is running.  They should go back to their configured size after a reboot (since tempdb is recreated when the database engine starts up).
You can check the configured size vs. actual size using these queries from Andy Mallon's blog:
--Check sys.master_files in master
USE master
GO
SELECT DataFileName = mf.name,
       FileSizeMB  = mf.size*8/1024
FROM sys.master_files AS mf
WHERE mf.database_id = db_id('tempdb')
ORDER BY mf.type, file_id;
GO
--Check sys.database_files in tempdb
USE tempdb
GO
SELECT DataFileName = dbf.name,
       FileSizeMB  = dbf.size*8/1024
FROM sys.database_files AS dbf
ORDER BY dbf.type, dbf.file_id;

The values from sys.master_files are the ones that tempdb should return to after a reboot.  If those values have increased to 63 GB, then someone has configured them that way.
If you choose the shrink the files, be sure to heed Andy's suggestion:

If you do shrink a tempdb file, check the sys.master_files metadata before & after to ensure you leave it in the ideal state. Use ALTER DATABASE...MODIFY FILE to repair the metadata for the next restart

Once you have the immediate size problem addressed, you really need to:

identify the root cause (what queries are causing tempdb) to grow, see the Q&A here: How to find the SQL statements that caused tempdb growth?
set the tempdb data files to a fixed size, disabling autogrowth
preferably move them to their own drive (if they are not already on a dedicated drive), to prevent this from causing user databases or the server from being as impacted


Can a Join happen with same attribute names, but different domains?

I think you are being misled by your terminology.

SQL is a standard that has the following schematics

Schema
Table
Column
Attribute

*Oracle does not have separate databases in an Instance. schemas are tied to a user.

SQL is a 4th generation language that separates the need to know where to query an object to high, almost human language-style programming

When you write a Query in SQL, the compiler (called the SQL Optimizer) translates this into machine-level language and retrieves your data. It is therefore highly dependent on the Optimizer to correctly retrieve the data how it thinks is best.

What this means is that your SQL is nothing more than meaningful suggestions to the Optimizer. All you need to know is what the Structures’ names are, not where or how to grab it.

SQL has hints and Operators that help the Optimizer to pick the right datasets, but it is the Optimizer that chooses the ORDER of the Query to parse first.

Thus, you are writing Logical queries. If two tables are joined, it is your responsibility to help the Optimizer by specifically calling out the columns that can be joined.

Even if the attributes are not the same, the Optimizer is fully agile enough to know how to translate on the fly these values. For example, a VARCHAR type value of ‘13’ is still rather similar to a tinyint, integer, numeric Value, so the Optimizer will Implicitly convert these columns into the same data type!

The following example is in TSQL:

CREATE TABLE TableA (col_A int, col_B varchar(24) )
CREATE TABLE TableB (col_A varchar(24), col_B int)
INSERT INTO TableA (col_A, col_B)
VALUES (1, ‘My Value’)
INSERT INTO TableB (col_A, col_B)
VALUES (‘Not my Value’, 1)

SELECT A.Col_B
FROM TableA AS A
INNER JOIN TableB AS B ON A.Col_A = B.col_B

Notice we used an alias to help the optimizer know what we mean by the join and what we mean by which column to return.


Is it advisable to have MySQL Master/Slave running on the same physical server, on the same mysqld, sharing the same disk?

I don't think so. You might reduce locking issues (but I'm not sure of that), but you are adding replication overhead without increasing your hardware resources, which means you'll be doing more with the same resources, which probably means decreased performance.


postgresql dump and load -- need to dump "postgres" db also?

The postgres database is just the default database that is created during initdb. It has no special meaning.

If you created a different one where all your data is kept and you never created tables in the postgres database, then there is no need to dump it.

I don't know what pg_upgradecluster is. The default tool to upgrade the data directory (apart from dump/restore) is pg_upgrade.

If you want help with the upgrade, "got a lot of error" won't get you much help. You need to be a lot more detailed.


SQL Server 2017 Enterprise vs Standard - Online Indexing and Online Schema Change

The confusion seems to be with the verbiage - online/offline is sort of confusing in this context.

It refers to whether the operations being performed (a reindex or a table alter) are performed online or not, and not whether the state of the object is online or offline. Online means that the object does not have an exclusive lock on it, and is possibly accessible to other queries/operations as a result. Offline means it is locked by the operation and can't be touched by other queries no matter what.

The benefit for Enterprise is that I can do my schema changes or index maintenance and not (potentially) adversely impact other queries running. That doesn't mean that other, less restrictive, locks won't be taken (row level, page level), but it does mean that I'm not totally boxed out of the object. It does not guarantee things will keep running the same, though, as many other factors play into this. Check out the individual commands you're interested in running to see the myriad options that may effect locking behaviors.

For example, I've seen an online index reorg end up effectively locking the table from all other SELECT statements until I tuned the MAXDOP it was using, even though it wasn't actually taking a lock on the whole table.

An object can't be taken online or offline, although a database can (not relevant to this topic though).

Resources:

How Online Indexing Works
Online ALTER COLUMN Operation


What is the actual lowest possible positive REAL number

The minimum positive (subnormal) single-precision floating-point value is 2−149 ≈ 1.4 × 10−45. The minimum positive normal value is 2−126 ≈ 1.18 × 10−38 (reference).

DECLARE
    @r1 real = POWER(2e0, -126),
    @r2 real = POWER(2e0, -23)

SELECT
    @r1,
    @r2,
    @r1 * @r2,
    CONVERT(binary(4), @r1 * @r2);

For double-precision, the minimum positive subnormal is:

DECLARE @r1 float = POWER(2e0, -1075);

SELECT @r1, CONVERT(binary(8), @r1);


optimal table design mysql with primay key and varchar value

You may find what I am about to suggest a little surprising

Add the following indexes

ALTER TABLE mytable ADD INDEX id_value_ndx (id,value);
ALTER TABLE mytable ADD INDEX value_id_ndx (value,id);

This will actually cache id and value together in the MyISAM Key Cache. That way, all queries are in memory only. Queries will not go to the table at all.

I would also propose you make a dedicated 1GB MyISAM Key Cache just for your table

STEP 01: Create a script to load the table into the dedicated key cache

cd /var/lib/mysql
echo "SET GLOBAL mykeycache.key_buffer_size = 1024 * 1024 * 1024;" > init-file.sql
echo "CACHE INDEX mytable IN mykeycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE mykeycache; >> init-file.sql

STEP 02 : Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/init-file/sql

STEP 03 : service mysql restart

From here on, every restart of mysql will setup this private key cache for your table

Give it a Try !!!


How can I obtain continuous results with a PostgreSQL query?

I don't think you need the subquery. Would this work?

SELECT sites.abbrev,
       measurements.ztime::date AS period_start,
       count(DISTINCT measurements.task) AS num_tasks,
       count(*) AS total_time
FROM sites
  JOIN tasks ON tasks.site = sites.id
  JOIN measurements
    ON (measurements.task = tasks.id
   AND measurements.ztime >= '2016-10-10'
   AND measurements.ztime < '2016-10-15')
WHERE sites.abbrev = 'AA-10'
GROUP BY sites.id,
         sites.abbrev,
         measurements.ztime::date
ORDER BY measurements.ztime::date ;


If you're OK with using strict mode so that warnings are converted to errors, then you can use the MariaDB SQL Error Log Plugin to log these into a special SQL error log file:

INSTALL SONAME 'sql_errlog';
SHOW VARIABLES LIKE 'sql_mode';

gives:

  STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

So we have strict mode (STRICT_TRANS_TABLES), which means this will produce an error instead of a warning (title is varchar(5)):

INSERT INTO t1 (id, title) VALUES (1, 'foobar');

Yes, we get:

  ERROR 1406 (22001): Data too long for column 'title' at row 1

... and then that error can be found in the SQL error log at /var/lib/mysql/sql_errors.log.

(Note that the example in the question will not produce an error even in strict mode as it doesn't affect a table. As it's not an error it won't be logged to the SQL error log.)


How do you update one side of a MySQL master-master replication setup?

At my company we run MySQL with circular replication. We do not stop replication during a deploy of either the app or schema changes.
We must restart MySQL Server during upgrades or some configuration changes, and replication naturally stops when the server stops, but we bring it back up immediately.
We use pt-online-schema-change to run schema changes without making the app lose access to the database. Apps can still run read or write queries while the schema change is running. It's not perfect; it requires a brief lock at the beginning and end of the task, and you can't have triggers on your table. Also it adds a significant query load to the database server while it's restructuring a table, and this may be enough to slow down the app (it depends on how sensitive the app is to load). But mostly it allows the app to continue working without requiring any downtime.
For the app deployments, we use multiple app servers in each datacenter. A deployment knows how to stop the app instances in a "rolling" manner so there's always a minimum number of them running during the deploy. So there's no need to swing traffic to the opposite datacenter.
Because app deployments and schema deployments are both asynchronous and hard to run absolutely together, the code and schema changes must be designed carefully so both the old and new code versions will work with both the old and new schema versions. For example:

No code can use SELECT * ... or INSERT INTO mytable VALUES(...) because if the schema change adds or drops a column, it would break the code.
Some schema deployments must be done in stages, for example add a new column and finish that deployment before any code can reference the new column.
Renames of columns or tables are forbidden, because it causes a chicken-and-egg problem.

With redundant app servers, online schema change tools, and a few modest restrictions on code style, almost all deployments can be done without downtime.


MobiLink: Where is Sybase Central storing the actual content of an remote task for mlagent

It's storing them inside project folder of MobiLink project as seperate files with mlt-extension. Format is XML.


Limit columns to certain width in MySQL command

Looks like a character set issue. Here is why:
I ran
select left(text, 7) text from
(select 'The quick brown fox jumps over the' text union select '０１２３４５６７８９０') x;

in MySQL 5.6.22 for Windows. Here is that output
+---------+
| text    |
+---------+
| The qui |
| ??????? |
+---------+

Same query in MySQL 5.6.21 for CentOS 6.3
+-----------------------+
| text                  |
+-----------------------+
| The qui               |
| ０１２３４５６        |
+-----------------------+
2 rows in set (0.00 sec)

You should use the CAST operator
select left(CONVERT(text USING latin1), 7) text from
(select 'The quick brown fox jumps over the' text union select '01234567890') x;

When I ran this in MySQL 5.6.22 for Windows, I got this
+---------+
| text    |
+---------+
| The qui |
| ??????? |
+---------+

When I ran this in MySQL 5.6.21 for CentOS 6.3, I got the same thing
+---------+
| text    |
+---------+
| The qui |
| ??????? |
+---------+

Just hunt down the correct character set.
GIVE IT A TRY !!!


Azure: Many Failed Login Attempts Logged

Is the TCP port SQL Server is listening on open globally?

If so, yes I'd be concerned. If there is a password that can be brute-forced or guessed, or an exploit that allows someone to bypass authentication, eventually your database could be compromised. You'll also be vulnerable to attacks that don't require access, such as someone filling up the drive that that has SQL Server error logs on it by creating many failed logins every second.

If the TCP port is open to the internet, the unwanted login attempts are normal. Based on my experience working at ISPs and hosting companies, a machine that's exposed to the internet will get probed on well-known ports all day (and port scanned periodically) if traffic isn't being dropped by a firewall.

On the other hand, if you have restrictive firewall rules that explicitly allow 124.117.233.94 through, it's likely that one of your customers or colleagues at that address has had a computer compromised.

You can currently read about controlling traffic to Azure Virtual Machines here. A good practice is to drop everything by default and explicitly allow only the traffic you want. Perhaps someone else will edit this to add further information.


You'd have more options here:

Perfmon (or ResourceMonitor on W7+) for general resource information;
Activity Monitor in Management Studio;
Inside SQL Server:

Glenn Berry's DMV queries;
Adam Machanic's stored procedure sp_WhoIsActive.

There are also many other external monitoring tools (some free, some paid) that you can use, but these ones are free and worth mentioning as a first step in your work.


Change a partitioned global non-unique index that is the primary key to a non-partitioned unique index

You can do this with DBMS_REDEFINITION, but you'll need double the space occupied by the table.

If I read your question correctly, this should do it:

[oracle@node1 ~]$ sqlplus phil/phil

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jan 6 18:47:12 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set echo on
SQL> @part
SQL>
SQL> drop table PARTITIONED_INTERIM;

Table dropped.

SQL>
SQL> drop table partitioned;

Table dropped.

SQL>
SQL> create table partitioned
  2  (
  3  pk number,
  4  blah  varchar(40),
  5  blah2 varchar(40)
  6  )
  7  partition by range (pk)
  8  (partition p1 values less than (1000),
  9   partition p2 values less than (10000),
 10   partition p3 values less than (30000),
 11   partition p4 values less than (MAXVALUE)
 12   );

Table created.

SQL>
SQL> create index pk_idx
  2  on partitioned (pk)
  3  global partition by range (pk)
  4  (partition p1 values less than (1000),
  5   partition p2 values less than (10000),
  6   partition p3 values less than (30000),
  7   partition p4 values less than (MAXVALUE)
  8   )
  9  ;

Index created.

SQL>
SQL> ALTER TABLE partitioned ADD CONSTRAINT ppk PRIMARY KEY (pk) USING INDEX pk_idx;

Table altered.

SQL>
SQL> select index_name, uniqueness , table_name, partitioned
  2  from user_indexes
  3  where table_name = 'PARTITIONED';

INDEX_NAME                     UNIQUENES TABLE_NAME                     PAR
------------------------------ --------- ------------------------------ ---
PK_IDX                         NONUNIQUE PARTITIONED                    YES

SQL>
SQL> EXEC dbms_redefinition.can_redef_table('PHIL', 'PARTITIONED');

PL/SQL procedure successfully completed.

SQL>
SQL> create table PARTITIONED_INTERIM as ( select * from PARTITIONED where 1=0 );

Table created.

SQL>
SQL> exec DBMS_REDEFINITION.start_redef_table(uname =>     'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> create unique index partitioned_interim_pk on partitioned_interim (pk) global online;

Index created.

SQL>
SQL> exec dbms_redefinition.sync_interim_table(uname => 'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_redefinition.finish_redef_table(uname => 'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> select index_name, uniqueness , table_name, partitioned
  2  from user_indexes
  3  where table_name = 'PARTITIONED';

INDEX_NAME                     UNIQUENES TABLE_NAME                     PAR
------------------------------ --------- ------------------------------ ---
PARTITIONED_INTERIM_PK         UNIQUE    PARTITIONED                    NO

SQL>
SQL>
SQL>

Note that you'll have to deal with other indexes/objects etc too, using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS() and DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT().


How can I get an object's "script as..." script through code?

Exactly which combination of tables to refer to depends on the object type, but it is all in the tables in the sys schema of each database. For instance sys.tables contains user table information, sys.columns lists columns in tables and input parameters to rpocedures, sys.all_sql_modiles includes the definitions of stored procedures and views, and so on.

Most are keyed on the object_id from which you can lookup the name in sys.objects.

For instance to list the definition of SomeProcedure you might run SELECT sm.[definition] FROM sys.objects so JOIN sys.all_sql_modules sm ON sm.object_id = so.object_id WHERE so.name = 'SomeProcedure'.

See https://msdn.microsoft.com/en-us/library/ms189783.aspx for reference information.

The system procedures sp_help and sp_helptext can be used to do the legwork in many cases where you need the information for manual inspection, but if you are trying to take programmed action based on the information it is usually better to refer to the data in sys.* directly than try to parse the output of these procedures.


SELECT *
FROM customer c
LEFT JOIN customer_orders co
    ON c.customer_number = co.customer_number
   AND co.order_status IN ('preparing', 'prepared')
WHERE c.customer_status='unpaid'
  AND c.order_status = 'unserved'
  AND co.cust_ord_number IS NULL


Ndb cluster change variable for heartbeat and disconnect

Configuration variable HeartbeatIntervalDbDb, see in manual:
https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-heartbeatintervaldbdb


Trigger to increment a column value after update in a column of same table

You can also change values using new. You just have to use not after but before trigger.

delimiter $$
create trigger increment
before update on T1
begin
    if new.C1=0 then
        set new.c2 = new.c2 +1;
    end if;
end $$
delimiter ;


Considering the query and the table size, this looks like a good candidate for partitioning on the Send_Time column.


Error during upgrade : Oracle 11g to 12c

The real error is:

ORA-12801: error signaled in parallel query server P000 ORA-01115: IO error reading block from file (block # )
ORA-01115: IO error reading block from file 2 (block # 215616)
ORA-27072: File I/O error
Additional information: 4
Additional information: 215616
Additional information: 212992

The "solutions" you tried make no sense when you get something like this.

Fix the storage/filesystem first.