Subselect of MAX(Version) takes many minutes, though there are only ~20K records

A full analysis would require access to the execution plans, table and index definitions, and database statistics (or a copy of the database itself). That's possibly unrealistic, so here's some general observations, and a possible solution for you to try. (Strictly, this question is probably beyond this site's remit.)

General background

The SQL Server query optimizer is the component responsible for choosing an execution plan for the logical results specification represented by your query. For even modestly complex queries, there are an enormous number of possible physical plans. The optimizer uses heuristics and cost estimation to choose from the limited plan space it explores.

The quality of information provided to the optimizer (including database design, indexing, and the accuracy of the current statistics) all have a great impact on the likelihood of the plan chosen by the optimizer performing well in practice. Where the design is relational, well-indexed, and with representative statistics, good plan choices will be the norm. Otherwise, all bets are off.

The query in question is moderately complex, and while its intent will be reasonably clear to most humans, the optimizer sees it as a SQL query like any other. The correlated sub-query with multiple joins and a MAX aggregate means the potential plan search space will be large, and estimates may not be accurate (due to accumulated errors, if nothing else). The optimizer may well end up choosing a plan that would work well if its assumptions were borne out, but which may perform horribly in reality.

The query and a potential rewrite

In broad terms, it appears you are asking SQL Server for the highest version per designation, with a limit on the version number. An alternative way to express this query (that is likely to have more predictable performance) is to number the rows (partitioned and ordered appropriately) then return the single row per group that is ranked #1:

WITH MaxVersionPerDesignation AS
(
    -- Do the joins and number the rows
    -- per designation, in descending
    -- Version order
    SELECT
        c.Parameter_ParameterID,
        p.Designation AS Name,
        c.Data,
        o.[Version],
        rn = ROW_NUMBER() OVER (
            PARTITION BY p.Designation
            ORDER BY o.[Version] DESC)
    FROM dbo.Releases AS r
    JOIN dbo.Overlays AS o
        ON o.Release_ReleaseID = r.ReleaseID
    JOIN dbo.Calibrations AS c
        ON c.Overlay_OverlayID = o.OverlayID
    JOIN dbo.[Parameters] AS p
        ON p.ParameterID = c.Parameter_ParameterID
    WHERE
        r.Mainline_MainlineID = 9
        AND c.[Status] <> 1
        AND o.[Version] <= 68
)
-- Return the row with the highest Version per designation
SELECT
    MVPD.Parameter_ParameterID,
    MVPD.Name,
    MVPD.Data,
    MVPD.[Version]
FROM MaxVersionPerDesignation AS MVPD
WHERE
    -- Row #1 per Designation
    MVPD.rn = 1
ORDER BY
    MVPD.[Version] DESC,
    MVPD.Designation;

Hopefully, the logic in the SQL above is reasonably easy to understand.

Analysis of the uploaded plan

The query optimizer produces an execution plan that attempts the execute the subquery once per outer row. This would be a good idea if the outer query did only produce one row, but sadly it qualifies 16,794 rows at runtime (execution plans captured using SQL Sentry Plan Explorer):

This mis-estimation is the root cause of your problem. Most probably, statistics on the base tables are not representative of the current data. You should refresh these statistics and have an ongoing plan to ensure statistics stay reasonably current. Another side-effect of the erroneous 1-row estimation is that the optimizer considers the whole query to be very low cost, almost trivial, and so does not spend long examining alternatives.

Anyway, the subquery (which is very similar to the outer query aside from the correlation and an extra predicate) is fully executed 16,794 times (with a different value for Designation and Version each time). This is an awful strategy of course (but it would have been just fine for 1 outer row).

The cumulative effect is to generate over 282 million rows (over all 16k iterations). This results in 282 million executions of the Key Lookup in the plan fragment shown above. This latter fact is likely the primary cause of the poor performance, though executing the correlated subquery 16,794 times would never be quick in any case (and the optimizer would not choose this strategy if it knew 16,794 rows would be driving it).

With optimal indexing (following a more detailed analysis than I can do right now), it would be possible to rewrite the query using CROSS APPLY and TOP 1 instead of MAX (see this related question) for possibly-optimal performance, but you may find the ROW_NUMBER alternative (again, see the related question) performs well enough, with good plan-choice stability.

Key Lookups:

You can eliminate the Key Lookups (in the long plan) by including the Parameter_ParameterID, Data, and Status columns in the IX_Overlay_OverlayID index.  For example:

CREATE INDEX [IX_Overlay_OverlayID]
ON dbo.Callibrations (Overlay_OverlayID)
INCLUDE (Parameter_ParameterID, Data, Status)
WITH (DROP_EXISTING = ON);

This will likely not cause the optimizer to choose a better or more stable plan without updating statistics, but it would be remiss of me not to mention it.


Select fields from sub query , but I face field could not be bounded

Based on your comment to the question of whether multiple rows could come out of your subquery for a given account number, you indicated that was not possible.  If that's true, I don't see why you can't simply LEFT JOIN by account number against a derived result of your subquery.

Not tested, but basically like this:

SELECT
    ROW_NUMBER() OVER(ORDER BY c.FullName ASC) AS No#,
    a.AcctBranchName,
    c.FullName,
    c.BirthDate,
    c.Age,
    a.StartDate as 'Account Start Date',
    c.CustStartDate as 'Customer Start Date',
    c.CustomerNum,
    a.Currency,
    a.AccountNum,
    a.ProductType as 'Product Name',
    a.Balance as 'Balance KHR',
    a.BalanceUSDEquiv as 'Balance USA',
    aa.ClosedDate,

    CASE
        WHEN (a.BalanceUSDEquiv*3 >= 200000) then '200,000'
        ELSE a.BalanceUSDEquiv*3 end as 'Sum Insure in USD',
    CASE
        WHEN (a.BalanceUSDEquiv*3 > 200000) then 200000*(0.1/100)/12
        ELSE a.BalanceUSDEquiv*3*(0.1/100)/12 end as 'Premium to be paid'

FROM
     v_Account AS a
LEFT join v_Customer AS c
    ON c.CustomerID = a.CustomerID AND c.Businessdate = a.Businessdate
LEFT JOIN
(
    SELECT AccountNum, ClosedDate FROM v_Account WHERE
        Category = 'Deposit' AND ProductCode IN ('6003', '6253')
    AND Businessdate='2018-12-06' AND ClosedDate is not NULL
) aa on aa.AccountNum = a.AccountNum
WHERE
        aa.AccountNum = a.AccountNum
    AND a.Category = 'Deposit'
    AND a.ProductCode IN ('6003', '6253')
    AND c.Businessdate='2018-12-05'


Try to increase the timeout period by adding -TO <time period in minutes> to your command:

-S server name -D ALL -T DB -R
D:\SQL_BACKUP\SQL_EXPRESSMAINT_LOGS\FULL_BACKUP_LOGS -RU DAYS -RV 5 -B
D:\SQL_BACKUP\SQL_FULL_BACKUP -BU DAYS -BV 5 -TO 25

ExpressMaint issues tab


Database design implementing support for different languages

By my reckoning, then you should not have show_languages between show and show_international_data because it's not an association table between the two, and because you do not want the language column to be filled out for each association between a show and show_international_data.
Basically if you have 10.000 shows and 20 languages, you'll need 20 x 10.000 versions of the 'language' field filled - lots of redundant data and chance for mistakes.
Also associations tables like that are usually used for n:m relationships and not 1:m and because one shows international data cannot belong to another, it shouldn't be mapped as n:m.

Instead show_international_data should be connected directly to show with show_id being the FK in it.
Then you can make a language table (language_id, language) and have that language_id being the second FK in show_international_data, which combined makes up it's PK.
So something like this:

show -> show_international_data <- language.

In this manner you can reuse your language table on everything which needs to be localized with a translation/details (international_data) type structure without.

I don't know what recommendations is going to contain, but that might also need to be localized?

show_title will most likely also need to be localized, because titles differ between countries/languages as some countries dub or completely reworks titles of shows. So that title properly needs to be put into international data and thereby removing that table.
Also you can put an original_title on the show base table.

If you do need to have alternative titles on top of this, then a secondary title table will start to make sense, and then you should - most likely - localize it by having a language_id FK enabling multiple alternative titles per language.


MySQL table design / architecture, table is too big

Well first there is no way that all of this should ever be in one table. You need related tables for information such as Assignee and Inventor. Please read about normalization.

But in reality are you sure that mysql is the way to go for this since you seem mostly to want to search on data that is not always in the same form. Personally, for this type of thing I would look at NoSQL databases for the text parts in conjuction with a relational database for the data that is easy to describe and determine the size of.


Query two SQL Server databases on two different servers

You can indeed do this, but I recommend against it.  You can create a linked server on DB-SERVER-1, connecting it to DB-SERVER-2.

(A linked server definition is setup by the DBA.  It contains the login credentials to the remote server.  If you want to provide the login credentials yourself each time, that would be an openquery or openrowset command.)

You then could write a query using four part naming convention as follows, assuming you are running this query on DB-SERVER-1:

select *
  from DB1.dbo.YourTable a
  join [DB-SERVER-2].DB2.dbo.OtherTable b on b.ID = a.ID

Cross server joins are notorious for having performance problems.  I would only do it for administrative purposes, and definitely not against large result sets.


SQL Server to MySQL migration, replace '\xA0' with a regular space

Maybe you can use CHARINDEX casting your Address1 to varbinary(8000) to identify the offending data:

select *
from [ForgeDB].[Snapshot].[SnapshotPersonEmployee]
where charindex(0xA0, cast(Address1 as varbinary(8000))) > 0;

Check this answer: https://dba.stackexchange.com/a/37355/55596


Foreign key part of a composite primary key

Your clustered index almost certainly should include RoomId for the reasons you correctly identify: it provides an efficient access path to all records for a given Room.
But there are other motivations are well.  If Item is a child of Room, then you probably have an identifying relationship like Room contains zero to many items.
This would naturally lead your primary key to be something like (RoomId,<columns that determine a unique item>).  So that could be (RoomId,ItemId)assuming that ItemId has some value beyond being a row pointer.
Otherwise it could be (RoomId,SerialNumber) or (RoomId,ItemType) - the exact answer depends on the data you're trying to store.


ORA-01017: invalid username/password; logon denied from sql developer but sqlplus logins

The problem was that I had too many instant clients and Oracle clients on my machine (one oracle client 'x86').

The instant clients were too old.

The only working instant client for me is the '12.1.0.2.0'.

I downloaded:

instant client basic lite win64 12.1.0.2.0
instant client jdbc win64 12.1.0.2.0
instant client sqlplus win64 12.1.0.2.0

fixed the PATH variable with the path I extracted the instant client '12.1.0.2.0' and removed all others from it.

In the end I set up the environment variable TNS_ADMIN pointing to my sqlnet.ora and tnsnames.ora. I think this TNS_ADMIN is not necessary.


Help for grouping a field and taking total sum

One way is to do a sub-select for the sum of F4 (this is the only explanation I could come up with in order to get 6 and 9)

SELECT x.F1,  x.F2,  x.F3,  x.F4
     , ( SELECT SUM(F4)
         FROM T as y
         WHERE x.F1 = y.F1 ) as sub_total
FROM T as x

If you DBMS support window functions:

SELECT x.F1,  x.F2,  x.F3,  x.F4
     , SUM(x.F4) OVER (PARTITION BY x.F1) as sub_total
FROM T as x


Turning rows into columns known as a PIVOT operation. There are several ways to implement this operation, including a built-in expression. The simplest to understand, and most flexible, I find to be the CASE construct.

I'll use an in-line CTE to wrap your query. There's no magic here, it's just a convenient way of using your existing code. I've added column type_desc to help distinguish the source of each amount. The built-in function DB_NAME() is used to give a "key" value to the output.

;with YourData as
(
    select
        DBName = DB_NAME(),
        type_desc,
        [Name],
        physical_name [Path],
        CAST(size AS BIGINT)*8192 [TotalBytes],
        CAST(FILEPROPERTY(name,'SpaceUsed') AS BIGINT)*8192 [UsedBytes],
        (case when max_size<0
             then -1
             else CAST(max_size AS BIGINT)*8192 end) [MaxBytes]
    from sys.database_files
)
select
    DBName,
    RowsTotalBytes = sum(case when type_desc = 'ROWS' then TotalBytes end),
    LogTotalBytes  = sum(case when type_desc = 'LOG' then TotalBytes end)
from YourData
    group by DBName;

The values we want occur in your query as two rows in a single column. The CASE expressions separate them into individual columns, with a NULL in the other column, still in two rows. By grouping I collapse the two rows into as single row. Hence the purpose for introducing DB_NAME().

Using SUM() serves two purposes: it returns a single value for all files of a type and ignores the NULL introduced for the other type_desc.

If you have multiple data or log files and want a separate output value for each use Name in the when instead of type_desc.


To run SQL Server Profiler or to use the SQL Server Agent's Jobs function in SSMS or ...?

In the past I wrote some scripts to capture slow running queries.

I used SQL Profiler via T-SQL stored procedures. The overhead for this wasn't huge and at the time provided valuable insight.

More info and some examples are here: http://www.toadworld.com/platforms/sql-server/w/wiki/9827.running-traces-with-tsql.aspx

I would write two stored procedures, one to start the trace and one to stop it.  These could be called via a SQL Agent job or a scheduled task, whatever is preferred. Log only the info you want to a table rather than files for easier management. You could create a third job to clean up records after "X" days/weeks/months depending on your requirements.

The performance hit will depend on the existing load on the server and there is no easy way of doing this so test any recommended solutions thoroughly before deciding which way to go.


How to set or grant SELECTINAUTH for a schema in DB2 11.5

I got the following information from IBM: "So currently the usage of SELECTINAUTH, INSERTINAUTH, UPDATEINAUTH is only available in DB2 Warehouse" and "Schema authorization is one of these features and we have publicly stated (at IDUG conferences etc) that we will be brought to Db2 in an update to 11.5.".
It can be enabled with db2set DB2_ENABLE_SCHEMA_AUTHORIZATION=true, but it's not documented and therefore not supported yet. (as IBM explained).


"Entity-Attribute-Value" or EAV design pattern allows you to create new attributes for products without modifying the database structure.

Aaron Bertrand has an excellent article on the advantages and pitfalls of this design over on SQLBlog.org.

A simplified example, based on your requirements:

CREATE TABLE dbo.Products
(
    ProductID int NOT NULL
        CONSTRAINT PK_Products
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductName nvarchar(100) NOT NULL
);

CREATE TABLE dbo.AttributeTypes
(
    AttributeTypeID int NOT NULL
        CONSTRAINT FK_AttributeTypes
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , AttributeTypeName nvarchar(100) NOT NULL
);

CREATE TABLE dbo.ProductAttributes
(
    ProductAttributeID int NOT NULL
        CONSTRAINT PK_ProductAttributes
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductID int NOT NULL
        CONSTRAINT FK_ProductAttributes_ProductID
        FOREIGN KEY
        REFERENCES dbo.Products(ProductID)
    , AttributeTypeID int NOT NULL
        CONSTRAINT FK_ProductAttributes_AttributeTypeID
        FOREIGN KEY
        REFERENCES dbo.AttributeTypes(AttributeTypeID)
    , AttributeValue nvarchar(100) NOT NULL
);

This will insert some sample data:

INSERT INTO dbo.Products (ProductName)
VALUES ('Oven Trays')
    , ('Cars')
    , ('Vacuum Cleaners');

INSERT INTO dbo.AttributeTypes (AttributeTypeName)
VALUES ('Manufacturer')
    , ('purpose')
    , ('diameter');

INSERT INTO dbo.ProductAttributes (ProductID, AttributeTypeID, AttributeValue)
VALUES (1, 1, 'Farberware')
    , (2, 1, 'General Motors')
    , (3, 1, 'Hoover')
    , (1, 2, 'cookies')
    , (2, 2, 'driving')
    , (3, 2, 'cleaning floors')
    , (1, 3, '12"');

This shows how we get desired output:

SELECT p.ProductName
    , att.AttributeTypeName
    , pa.AttributeValue
FROM dbo.Products p
    INNER JOIN dbo.ProductAttributes pa ON p.ProductID = pa.ProductID
    INNER JOIN dbo.AttributeTypes att ON pa.AttributeTypeID = att.AttributeTypeID
ORDER BY p.ProductName
    , att.AttributeTypeName;

╔═════════════════╦═══════════════════╦═════════════════╗
║   ProductName   ║ AttributeTypeName ║ AttributeValue  ║
╠═════════════════╬═══════════════════╬═════════════════╣
║ Cars            ║ Manufacturer      ║ General Motors  ║
║ Cars            ║ purpose           ║ driving         ║
║ Oven Trays      ║ diameter          ║ 12"             ║
║ Oven Trays      ║ Manufacturer      ║ Farberware      ║
║ Oven Trays      ║ purpose           ║ cookies         ║
║ Vacuum Cleaners ║ Manufacturer      ║ Hoover          ║
║ Vacuum Cleaners ║ purpose           ║ cleaning floors ║
╚═════════════════╩═══════════════════╩═════════════════╝


How to get sum of last previous X days of everyday?

Here is what I think you want,

SELECT days.d::date, sum(sales.number_of_sales)
FROM (
        SELECT min(sales_create_date::date), max(sales_create_date::date)
        FROM sales
) AS minmax
CROSS JOIN LATERAL generate_series(minmax.min, minmax.max, '2 days')
        AS days(d)
LEFT OUTER JOIN sales
        ON sales_create_date >= days.d
        AND sales_create_date < (days.d + '2 days')
GROUP BY days.d;

Notice you have to put the window date twice in the above.

What we're doing is,

Selecting min and max dates for the table
Generating the start for the range on the window
Joining back to sales with the range start and the end interval
Grouping and selecting the count(*) in the window.

Result,

     d      | sum
------------+------
 2015-05-02 | 1000
 2015-05-04 |  500
 2015-05-08 | 1700
 2015-05-06 | 1300
(4 rows)


Trigger not functioning correctly in MYSQL

Try this code:

DROP TABLE applicationsolution;
CREATE TABLE applicationsolution (myid INTEGER AUTO_INCREMENT PRIMARY KEY,
                                  created DATETIME);

Sample table.

delimiter //
CREATE TRIGGER applicationsolution_OnInsert
BEFORE INSERT ON applicationsolution
FOR EACH ROW
BEGIN
  IF NEW.created IS NULL
    THEN SET NEW.created = CURRENT_TIMESTAMP();
  END IF;
END //
delimiter ;

Trigger - which works - see below.

INSERT INTO applicationsolution (created) VALUES (NULL);
INSERT INTO applicationsolution (created) VALUES (NULL);

Sample data insert - will produce two records.

SELECT * FROM applicationsolution;

myid,created
==== =======
1,"2016-06-24 21:50:40"
2,"2016-06-24 21:50:40"

So far, so good for NULLs. Now for the UPDATE trigger:

delimiter //
CREATE TRIGGER applicationsolution_OnUpdate
BEFORE UPDATE ON applicationsolution
FOR EACH ROW
BEGIN
  IF NEW.created = '0000-00-00 00:00:00'
    THEN SET NEW.created = current_timestamp();
  END IF;
END//
delimiter ;

Now do an update.

UPDATE applicationsolution  SET created = '0000-00-00 00:00:00'
WHERE myid = 2;

SELECT * FROM applicationsolution;

And the results:

myid,created
1,"2016-06-24 21:50:40"
2,"2016-06-24 21:54:30"

Note the timestamp change in record 2 which shows that if you try to INSERT a created of '0000-00-00 00:00:00', you get a CURRENT_TIMESTAMP.

Does this do what you want? I think there are two problems with your code. It's not formatted properly - this made it very difficult to follow the logic and difficult to see that the second trigger was incomplete. You also put the INSERT code in the ON UPDATE trigger and vice-versa.

Furthermore, CURRENT_TIMESTAMP seems to work better than NOW().


Is it possible to set a composite NOT NULL constraint in PostgreSQL

Let's assume the structure of your table is this one:

CREATE  TABLE log_logins
(
    user_id INTEGER NOT NULL,
    login_time TIMESTAMP NOT NULL DEFAULT now(),
    ip_v4 TEXT /* or any other representation */,
    ip_v6 TEXT /* or any other representation */,
    PRIMARY KEY (user_id, login_time)
) ;

You can just add a CHECK that guarantees that either one of ip_v4 or ip_v6 is not null, but not both (it makes no sense to me to have both a v4 address and a v6 one; you don't normally use both protocols at the same time). That would be done with the following statement:

ALTER TABLE log_logins
  ADD CONSTRAINT one_and_only_one_of_ip_v4_or_ip_v6
  CHECK ((ip_v4 IS NULL) <> (ip_v6 IS NULL));

If it is reasonable to have both a v4 and a v6 address at the same time, the constraint to use would be:

ALTER TABLE log_logins
  ADD CONSTRAINT at_least_one_of_ip_v4_or_ip_v6
  CHECK ((ip_v4 IS NOT NULL) OR (ip_v6 IS NOT NULL));

Alternatively, take a look at the RhodiumToad/ip4r extension. If you use it, you can represent ip4, ip6 or ipaddress (that can contain either an IPv4 or IPv6 address) in a compact and efficient way, and have a collection of operators on both IP addresses and IP address-ranges. I'd actually recommend it.

Although PostgreSQL includes the inet data type, that can be used to store internet addresses (together with netmasks), the ip4r extension provides a few advantages if you only need an IP address (without the netmask). One of the advantage is a more compact representation, that may play a role if the volume of data to be recorded is big. This extension is really helpful if you need to use ip ranges. One use case would be to join the IP column of the log_logins table to another table containing (arbitrary) IP ranges and the countries to which they correspond.


Oracle writes whole file during database creation is it ok?

If you are able to create this tablespace(logical entity of Oracle database to hold user objects) then you already have  created the database.

Yes, this is expected. The statement defines a datafile of 300MB size in underlying storage.

You can also auto increment size. For example, initially you have defined its size  as 300M. When you create new objects and perform DML, they will use this space. Once this 300M is consumed by objects you need to either add new datafile or increase its size or let the Oracle increment its size as needed.

Example:

CREATE TABLESPACE tbs_02
   DATAFILE '/diskb/tbs_f5.dbf' SIZE 500K REUSE
   AUTOEXTEND ON NEXT 500K MAXSIZE 100M;

It creates a tablespace called tbs_02with a datafile of 500K initial size. Once the 500K is occupied by objects it will be incremented by 500K at once and grows upto 100MB.

If you are new to Oracle database I strongly suggest you to follow Oracle Concepts Manual

Reference:

CREATE TABLESPACE

storage_clause


Introducing FOREIGN KEY constraint 'FK_X_Y' on table 'X' may cause cycles or multiple cascade paths

You can use an INSTEAD OF trigger here, or have logic that re-assigns all the direct-reports prior to deleting a user.  EG

--drop table if exists users
go
CREATE TABLE [dbo].[Users]
(
    [UserId] INT NOT NULL,
    [ManagerId] INT NULL,
    [Data] NVARCHAR(1024) NULL,
    CONSTRAINT [PK_Users_UserID] PRIMARY KEY ([UserId] ASC),
    CONSTRAINT [FK_Users_Manager] FOREIGN KEY ([ManagerId]) REFERENCES [Users] ([UserId]) ON DELETE NO ACTION
)

go
create trigger tg_users_del on [Users] instead of delete
as
begin
  set nocount on;
  update Users set ManagerId = null where ManagerId in (select UserID from deleted);
  delete from Users where UserId in (select UserID from deleted);
end

GO
INSERT INTO Users(UserId, ManagerId) values (1,null),(2,1),(3,1),(4,2)

DELETE FROM Users where UserId = 1

select * from Users


Why does PSQL not allow hash indexes with Bitmapscan?

Hash indexes are obsolete. Don't use them. They don't support crash safety either. Performance tests show that b-tree indexes tend to be on par with hash indexes for most purposes anyway.


Ubuntu postgresql 2 databas in two hard disks

PostgreSQL has no problem with the data on 2 hard-disks. The best thing that you can do is create a tablespace on your second hard-disk and move one of the databases to this tablespace.

CREATE TABLESPACE somename LOCATION 'directory-on-new-disk';
ALTER DATABASE name SET TABLESPACE somename;

Also see this post: Postgresql: Move database to new tablespace.

If you use LVM then you can also consider to use this to extend the logical volume on which your database is.


Inline function Plan Properties in SQL Server 2008

I converted the IIF to CASE statements and cleaned it up a bit.

Combining them into one query, I came up with this:

DECLARE @dbname NVARCHAR(128) = N'dbname'
DECLARE @procname NVARCHAR(128) = N'procname'

SELECT
    ExecutionPlan                       = QueryPlans.query_plan ,
    CachedDateTime                      = ProcedureStats.cached_time ,
    LastExecutionDateTime               = ProcedureStats.last_execution_time ,
    ExecutionCount                      = ProcedureStats.execution_count ,
    AverageElapsedTime_Microseconds     = CAST ((CAST (ProcedureStats.total_elapsed_time AS DECIMAL(19,2)) / CAST (ProcedureStats.execution_count AS DECIMAL(19,2))) AS DECIMAL(19,2)) ,
    SetOptions                          = PlanAttributes.value,
    ANSI_NULLS_Value                    = CASE WHEN CAST (PlanAttributes.value AS INT) & 32 = 32 THEN N'True' ELSE N'False' END ,
    ANSI_PADDING_Value                  = CASE WHEN CAST (PlanAttributes.value AS INT) & 1 = 1 THEN N'True' ELSE N'False' END ,
    ANSI_WARNINGS_Value                 = CASE WHEN CAST (PlanAttributes.value AS INT) & 16 = 16 THEN N'True' ELSE N'False' END ,
    ARITHABORT_Value                    = CASE WHEN CAST (PlanAttributes.value AS INT) & 4096 = 4096 THEN N'True' ELSE N'False' END ,
    CONCAT_NULL_YIELDS_NULL_Value       = CASE WHEN CAST (PlanAttributes.value AS INT) & 8 = 8 THEN N'True' ELSE N'False' END ,
    NUMERIC_ROUNDABORT_Value            = CASE WHEN CAST (PlanAttributes.value AS INT) & 8192 = 8192 THEN N'True' ELSE N'False' END ,
    QUOTED_IDENTIFIER_Value             = CASE WHEN CAST (PlanAttributes.value AS INT) & 64 = 64 THEN N'True' ELSE N'False' END
FROM
    sys.dm_exec_procedure_stats AS ProcedureStats
CROSS APPLY
    sys.dm_exec_query_plan (ProcedureStats.plan_handle) AS QueryPlans
CROSS APPLY
    sys.dm_exec_plan_attributes (ProcedureStats.plan_handle) AS PlanAttributes
WHERE
    database_id = DB_ID (@dbname)
AND
    object_id = OBJECT_ID (@procname)
AND
    PlanAttributes.attribute = 'set_options';

IIF statements started in SQL Server 2012 (https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-2017)

After converting those, everything else worked on SQL2008R2.


Service privileges for Patch Download? (Oracle 11gR2)

Short version: install the 12c software, create an empty database, and transfer the data with Data Pump. This way you can skip 11.2.0.1+ versions.

Long version: OTN Developer License Terms

In this, there is the following:

  No Technical Support
  Unless Oracle support for the Programs, if any,
  is expressly included in a separate, current support agreement between
  You and Oracle, Oracle’s technical support organization will not
  provide technical support, phone support, or updates to You for the
  Programs provided under this Agreement.

So no, you can not download updates officially with the OTN Developer License.

A valid support contract is needed for that. Actually a support contract costs extra on top of the database license, so there are companies that choose not to pay for it (and not to install patches...).


Will "bridge" storage engines work with MariaDB Galera Cluster?

Galera replicates DDL statements so other storage engines that act as a proxy to InnoDB tables or external systems should work normally.  The tables aren't really replicated but they would be created on each node when they are created on any node.

OQGRAPH - I saw you asking about it on the MariaDB site and as the consensus is there, theoretically it should work since the data is stored in InnoDB tables and would be replicated but it's not a common use case and you'll need to do some testing on your own.
Mroonga - same as OQGRAPH
CONNECT - This should be the same as OQGRAPH.
CassandraSE and SphinxSE - since the only information these engines use from MySQL is created with DDL statements, they should work fine.
BLACKHOLE - These are just normal tables that don't have any data to replicate so there shouldn't be any problems.
SEQUENCE - The SEQUENCE engine doesn't create any tables and is triggered by the names of tables in a query so these should work fine anywhere as long as the plugin is loaded on the server that is being queried.


SQL Server target/total server memory is not equal to sum of database size in dm_os_buffer_descriptors

sys.dm_os_buffer_descriptors

From the official documentation on sys.dm_os_buffer_descriptors (Transact-SQL):

  sys.dm_os_buffer_descriptors returns pages that are being used by the Resource database. sys.dm_os_buffer_descriptors does not return information about free or stolen pages, or about pages that had errors when they were read.

You are looking at the data cache usage. But there are more objects in memory than just the data.

sys.dm_exec_cached_plans

You have to consider the plan cache (sys.dm_exec_cached_plans (Transact-SQL)) and other objects.

  Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Memory acquired by cached plans can be retrieved with the following script:

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject,
    omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
JOIN sys.dm_os_memory_objects AS omo
    ON ecp.memory_object_address = omo.memory_object_address
    OR ecp.memory_object_address = omo.parent_address
WHERE cacheobjtype = 'Compiled Plan';
GO

sys.dm_os_memory_clerks

But instead of looking at individual caches, you might want to consider querying the view: sys.dm_os_memory_clerks

  The SQL Server memory manager consists of a three-layer hierarchy. At the bottom of the hierarchy are memory nodes. The middle level consists of memory clerks, memory caches, and memory pools. The top layer consists of memory objects. These objects are generally used to allocate memory in an instance of SQL Server.

sys.dm_os_memory_objects

  ... SQL Server components use memory objects instead of memory clerks. Memory objects use the page allocator interface of the memory clerk to allocate pages. Memory objects do not use virtual or shared memory interfaces. Depending on the allocation patterns, components can create different types of memory objects to allocate regions of arbitrary size.

Use the following query to see what sys.dm_os_memory_objects (Transact-SQL) are using in RAM.

SELECT SUM (pages_in_bytes) as 'Bytes Used', type
FROM sys.dm_os_memory_objects
GROUP BY type
ORDER BY 'Bytes Used' DESC;
GO

Summary

So while sys.dm_os_buffer_descriptors does provide an overview of the data cahce, you will have to query other sys views to determine the actual current memory usage.

Your SQL Server instance may at one time have occupied 53 GB of RAM, but it is now only currently consuming 39 GB of RAM (in data cache) and more GB in plan cache and memory objects, which may sum up to 53 GB RAM, but which may also sum up to less because of free pages in RAM, that may not yet have been released by SQL Server.


In fact, it is not easy to find the right command we need for queries in the MySQL documentation.
However, by testing, I saw that this should be fine:

sql = f"SELECT DATEDIFF(NOW(), timestamp), count_issue FROM total_unresolved_issue WHERE project = '{project}'"

DATEDIFF(NOW(), timestamp) returns the difference between two dates in days.


Connect to Azure KeyVault via SQL Connect failed with Error code 3113

You said in 2. you created an rsa 2048 key named DBEncryptionKey1, but the error shows you are trying to find a key named EncryptionDBKey1, looks like at the very least the name is mismatched?


I have hard time understanding Postgresql OVERLAP function when provided Interval

As far as I understand it, (DATE '2001-02-16', INTERVAL '100 days') is evaluated as (DATE '2001-02-16', DATE '2001-02-16' + INTERVAL '100 days')

Which in turn is the same as (DATE '2001-02-16', DATE '2001-05-27') and that obviously doesn't overlap with (DATE '2001-10-30', DATE '2002-10-30')


rds_superuser is not the same as a PostgreSQL superuser
From one of the best blogs on PostgreSQL, 2ndquadrant, Andrew Dunstan writes,

The Amazon RDS documentation blithely contains this statement: “When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is similar to the PostgreSQL superuser role (customarily named postgres in local instances) but with some restrictions.” But just how super is it?
One of the things I came up against recently was that, unlike the usual postgres superuser, this role has no access other than what is explicitly granted to objects owned by other users. From a table and function privileges point of view, it’s just an ordinary user.
So if you’re using more than one user in your RDS database, even if one or even all of them are rds_superusers, you’re going to become very familiar with the GRANT command if you aren’t already. And if your schema has objects owned by more than one user, then the relevant “GRANT .. ON ALL ..” option fails too, since you probably won’t have sufficient privileges on all of them. Perhaps we should have a “GRANT … ON ALL POSSIBLE …” which would skip those things you don’t have GRANT privilege on.

From our own Craig Ringer (also with 2nd Quadrant)

It’s not very super at all. It cannot LOAD libraries, access the file system, override permissions, get an unrestricted view of pg_stat_activity

Another user, Robins, also points out that rds_superuser can't run pg_dumpall so this may be a source of vendor lock-in.


What is the fastest way to clone/copy oracle databases on the same machine?

rman is the tool for this.

https://docs.oracle.com/cd/E11882_01/backup.112/e10642/toc.htm

There is a learning curve, but once configured, it's much faster than export/import.


How to store schema-less data efficiently in a relational database?

Some really nasty problems would happen with these tables (idea)

Data Redundancy (duplicated data) what you need to keep in synchronisation (and you do you check this because MySQL doesn't have an nice JSON to records function?)
You can't force correct values on the database (say goodbye to data integrity and hello to garbage-in garbage-out data) example key birthdate could have value "hello"

And you need some kind of pivot query to fetch the key/values

If you really need dynamic key values (EAV) storage there are some more options:

Maybe MySQL 5.6 NoSQL using the InnoDB memcached Plugin
XML -> query with xpath
RDF
NoSQL like MongoDB
MariaDB dynamic columns and MariaDB also supports json.


SQL Error [42883] - function does not exist - Cannot call function from trigger

As the documentation says:

  The trigger function must be defined before the trigger itself can be
  created. The trigger function must be declared as a function taking
  no arguments and returning type trigger. (The trigger function
  receives its input through a specially-passed TriggerData structure,
  not in the form of ordinary function arguments.)

The function you have declared is:

insertIntoAutoIncrementExample(companyname text,location text) ... returns void

so it does not fit both in the return type and in the argument types.
A trigger function does not take arguments but can access the values inserted or changed in the row variables NEW and OLD. They get automatically defined in plpgsql, see Trigger procedures in the plpgsql chapter for  details and examples.

Concerning the error message:

function insertintoautoincrementexample() does not exist

it means: this function name, with an empty list of arguments, does not exist.
The presence of parentheses around nothing is relevant, because in postgresql, functions always go with their argument types:
foobar(int) is not the same function than foobar(), or foobar(int,int) or foobar(text).


They're two slightly different animals that can be used for the same purposes, as in your example. CROSS APPLY is your only option for "joining" table value functions and "expanding" xml documents, though.

Some queries, particularly parallel queries, can exhibit vastly improved performance using CROSS APPLY, provided you have the requisite processor threads and indexing strategy.

Microsoft MVP Itzik Ben-Gan elaborates a couple of great examples in this talk


Multiple Basic Availability Groups and Performance - SQL Server

There are two parts to this. First, make sure you install CU1. Notably it contains a fix for

Performance issues occur when using the sys.dm_hadr_availability_replica_states DMV on an AlwaysOn Availability Group that contains many databases in SQL Server 2016

The second part is to use T-SQL instead of PowerShell. When I do it this way, the availability groups get created much, much faster and I'm not seeing any of the CPU/thread issues I complained about above. This is the code I'm using to create the Availability Groups with direct seeding. I've adapted it from this blog post:

SQL Server 2016: Availability Groups, Direct Seeding, and You.

On the primary:

CREATE AVAILABILITY GROUP [vm-ag-TestDb21]
WITH (BASIC,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE)
FOR DATABASE [TestDb21]
REPLICA ON
N'vm-sqllab1' WITH (ENDPOINT_URL = N'TCP://vm-sqllab1.domain:5022',
    FAILOVER_MODE = MANUAL,
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
    SEEDING_MODE = AUTOMATIC),
N'vm-sqllab2' WITH (ENDPOINT_URL = N'TCP://vm-sqllab2.domain:5022',
    FAILOVER_MODE = MANUAL,
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
    SEEDING_MODE = AUTOMATIC)

GO

And on the replica:

ALTER AVAILABILITY GROUP [vm-ag-TestDb21] JOIN
GO
ALTER AVAILABILITY GROUP [vm-ag-TestDb21] GRANT CREATE ANY DATABASE
GO


Order to disable constraint in a SQL Server Database

You need to disable the constraint in the table that references the table mentioned in the error message (I'm sure that's not [Schema].[Table]). You can find this using the constraint name in the error message (which I'm also sure is not Constraint_Name, but I'll use that):

DECLARE @sql nvarchar(max);

SELECT @sql = N'ALTER TABLE '
  + QUOTENAME(OBJECT_SCHEMA_NAME([parent_object_id]))
  + N'.' + QUOTENAME(OBJECT_NAME([parent_object_id]))
  + N' NOCHECK CONSTRAINT ' + QUOTENAME(name) + N';'
FROM sys.foreign_keys WHERE name = N'Constraint_Name';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Check the print output, if it looks good, uncomment the exec, and run it again.

If you need to do this for multiple tables:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME([parent_object_id]))
  + N'.' + QUOTENAME(OBJECT_NAME([parent_object_id]))
  + N' NOCHECK CONSTRAINT ' + QUOTENAME(name) + N';'
FROM sys.foreign_keys WHERE is_disabled = 0;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

The PRINT output will only be useful up to a certain point, depending on your output settings in SSMS (see a workaround here). If you need to narrow it down and not disable all foreign keys, then you can copy the rows you want to the top pane, disable them, then move on to the next set.

Now, if you do ultimately need these foreign key constraints, that means that the values you insert into the table that had the constraint already exist in the table mentioned in the error message, otherwise what is the point of the constraint? You need to fix your code so that it works correctly. Disabling or dropping the constraint is just masking a more important problem, and is going to allow bad data to get into the table in the meantime.

Disabling the constraint is like doing up your seatbelt behind your back just to shut the car up; dropping it is like ripping out the seatbelt and the speaker.


MySQL multi-source replication with identical DB names

I briefly searched MySQL Documentation
I located the Rewrite DB Option embedded in a new mechanism : CHANGE REPLICATION FILTER
It allows you to manipulate the Rewrite DB stuff without restarting MySQL.
UPDATE 2014-04-02 15:48 EDT
After reading the MySQL 5.7 Docs, I can safely say that CHANGE MASTER TO and CHANGE REPLICATION FILTER still cannot help you. Here is why:

All databases are identically named on all Masters
Replication Filters are global in scope. It would be very convenient if a filter could be scoped toward each MASTER_BIND interface name. At this point, MySQL 5.7 does not do that.

ALTERNATIVE
Perhaps you should set up multiple MySQL Instances on the Slave Server. Make each MySQL Instance use standard MySQL Replication. You can scale down all the buffer sizes and settings for all the Instances on the Slave.
Then, you can set up a mysqldump from each of the MySQL Instances to different folders.
If you want to learn how to setup multiple MySQL Instances on a single server, please see my old posts

Sep 30, 2011 : Running multiple instances on the same host
Sep 17, 2012 : mysqlservice command syntax


Can we WITH x AS ( ... ) INSERT INTO y(k) SELECT * FROM x in MySQL?

Something like
insert into item
  with x as (
    select id + 10 as id1, concat(brand,' D') as brand1 from item),
       y as (
    select id + 20 as id1, concat(brand,' E') as brand1 from item)
  select * from x
  union all
  select * from y

Yes, we can!


Difference between surrogate key(s) and composite key(s)?

A composite key is simply a key that contains two or more columns.

A surrogate key is one which is not naturally unique to the data but is added or imposed onto the data for (hopefully) a good reason. Examples can include IDENTITY columns in SQL Server - known as autonumbers sometimes in other products.

Perhaps you are storing information about people, but you can't be sure that they can be uniquely identified by a combination of their name, date of birth etc.. If you don't have some external information about them that ascribes uniqueness (e.g. Social Security Number), you can use a surrogate key to uniquely identify the rows.


The columns ABSENT, LATE and PRESENT in your output represent totals per student, whereas the date columns represent details per student, and details are the pivoted columns. In order to get such output with PIVOT, your source must provide totals on the same row with details.

The source in your query is not providing that kind of information. It is grouping by student_id, attendance_Date, attendance_id, and that grouping does not have any effect since there is always one row per student and date.

In order to have both details and totals on the same row, you can use window aggregation, like this:

SELECT
  student_id,
  attendance_Date,
  attendance_id,
  SUM (DECODE (Attendance_id, 'ABSENT',  1, 0)) OVER (PARTITION BY student_id) AS ABSENT,
  SUM (DECODE (Attendance_ID, 'LATE',    1, 0)) OVER (PARTITION BY student_id) AS LATE,
  SUM (DECODE (Attendance_ID, 'PRESENT', 1, 0)) OVER (PARTITION BY student_id) AS PRESENT
FROM
  sample

Note absence of GROUP BY. The OVER clause makes SUM a window aggregate function. With a window function you can return aggregate data along with detail (non-aggregate) data. The query above returns output like this:

STUDENT_ID  ATTENDANCE_DATE  ATTENDANCE_ID  ABSENT  LATE  PRESENT
----------  ---------------  -------------  ------  ----  -------
SCM-026020  4/4/2016         LATE           1       1     0
SCM-026020  5/4/2016         ABSENT         1       1     0
SCM-026021  5/4/2016         ABSENT         2       0     0
SCM-026021  4/4/2016         ABSENT         2       0     0
SCM-026022  4/4/2016         PRESENT        0       0     2
SCM-026022  5/4/2016         PRESENT        0       0     2

That is, it returns details per date and student along with totals per student. Using that source, the PIVOT clause provides the expected result:

STUDENT_ID  ABSENT  LATE  PRESENT  4/4/2016  5/4/2016
----------  ------  ----  -------  --------  --------
SCM-026020  1       1     0        LATE      ABSENT
SCM-026021  2       0     0        ABSENT    ABSENT
SCM-026022  0       0     2        PRESENT   PRESENT


PostgreSQL 9.6 pg_rewind - takes a long time to replicate

pg_rewind connects to the new master and locates the latest checkpoint it shares with the old master. Then it examines local WAL to find all blocks that have been modified since and copies these blocks from the new master.

So the procedure is slow if

promotion happened a long time ago, so pg_rewind has to dig through many WAL files

or

many blocks have changed since the promotion

From the output of pg_rewind it becomes obvious that half of the blocks in the database cluster have been modified since the slave was promoted. So the problem is that you waited too long after promotion. Immediately after failover, pg_rewind would be much faster.


I'm sorry to have to say to you that it seems like you have corruption in your WAL files. Did you remove some WAL files? If so, copy them back to the WAL directory.
If you don't have the missing WAL files (and I see archiving is disabled, so you likely don't have them), data loss is inevitable.
My suggestion:

copy the entire cluster somewhere else (just to be able to restore should you make things worse)
use pg_resetwal to try to reset your wals (see documentation here and please read the whole page so you understand that this will lead to data loss)
try to start again
if it starts, if you don't have checksums enables (it's not the default), pg_dumpall everything (see documentation here) (redirect to /dev/null if it's too big) to check for any data corruption
if it does not start, I'm afraid you'll have to rely on backup, making the data loss even worse

I'm so sorry for the bad news...


When the IO Thread dies, it usually dies for these reasons

STOP SLAVE;
STOP SLAVE IO_THREAD;
Network Issues

The first two reasons are obvious, but many have been victimized by network connectivity. For example, if there are enough dropped packets along the traceroute of the IP you are using as the Master, the IO Thread will just die off. By default, mysql attempts to reconnect to the Master every 60 seconds.

The mysqld process does not record network level issues, but a dying IO Thread is usually a dead giveaway (no pun intended). If this is happening often, go to work troubleshooting the traceroute on that IP (perhaps ifconfig on that Master IP).


How to update collections and restore while doing mongorestore?

Sorry, not possible. If you could update dump, you must have possibility to update indexes too...


Insert/ Update Scalability (more records, continually longer lookups --- solution?)

Parse the JSON when the record in inserted, not later.  (OK, maybe you need to collect the JSON in a "staging" table between arrival and processing.)

When you parse the JSON, extract only the fields that you need to manipulate/search/etc in MySQL.  Be conservative.  Don't extract many fields.  Leave the JSON as is in case you need the details later.  It would even be good to compress it and store it into a BLOB -- to save space, hence decrease I/O.

What queries will you be doing on the data?  Studying them is critical to the design of a Data Warehouse.

Properly indexed (etc), a table that grows by a factor of 10 may be only 10% slower.  Don't panic, but do look at the queries.  I have seen billion-row tables that hum along nicely.

It sounds like you are inserting only about 2 rows per minute; is that correct?  100 per second is when it starts to get challenging.

Let's worry about the "archiving" after we have covered some of the issues I mentioned.


Selecting only columns where sum(column) > 0

This solution gives you your "Alternate Solution" answer.  The only way to achieve what you are looking for as a primary solution would be a dynamic pivot operation on the result set after you have unpivoted it.  This solution does require maintenance but is straightforward and no function is required.

Sample data:

DECLARE @Names TABLE
    (
      DateValue DATE NOT NULL
    , Bob INT NOT NULL
    , Joe INT NOT NULL
    , Will INT NOT NULL
    );

DECLARE @MinDate DATE = '1/1/2015';
DECLARE @MaxDate DATE = '1/3/2015';
DECLARE @MinValue INT = 1;

INSERT  INTO @Names
        ( DateValue, Bob, Joe, Will )
VALUES  ( '1/1/2015', 1, 0, 1 )
        , ( '1/2/2015', 0, 0, 1 )
        , ( '1/3/2015', 0, 0, 0 )
        , ( '1/4/2015', 1, 1, 1 );

Query:

WITH    CTE_Unpivot
          AS ( SELECT   DateValue
                      , ScoreName
                      , Scores
               FROM     ( SELECT    DateValue
                                    , Bob
                                    , Joe
                                    , Will
                          FROM      @Names
                        ) AS cp
               UNPIVOT  ( Scores FOR ScoreName IN ( Bob, Joe, Will ) ) AS up
             )
    SELECT  ScoreName
          , SUM(Scores) AS ScoreTotal
    FROM    CTE_Unpivot
    WHERE   DateValue BETWEEN @MinDate AND @MaxDate
    GROUP BY ScoreName
    HAVING  SUM(Scores) >= @MinValue;

Edit:
Ideally, you wouldn't have the table stored like that, it looks like it was copied from an Excel spreadsheet.  You would have a table with three columns, date, name and value, then query based on that.


Can i control access end point When some table data changed In Mysql Semisynchronous Replication

"Semisync" implies

One of the slaves has received the DML and stored it safely (in the relay log).
That slave has not necessarily applied the statement.
If the Master crashes, then at least one slave has every transaction that was COMMITted on the Master.
Once you find that Slave, you could promote it to Master with confidence.


how to use an index on a mysql query with a where clause and a order by

Try a compound index on (subscriberid, cdate).
i.e.

ALTER TABLE activity ADD INDEX subid_date(subscriberid, cdate);


In a MySQL Master/Slave replication, what would happen if I write to the Slave?

As long as you do not perform any INSERT/UPDATE/DELETE statements directly on the Slave, your Slave should be just fine. Otherwise, MySQL Replication could break if you INSERT an new row in mydb.mytable on the Slave and, via Replication, the Slave later detects an INSERT of a row to mydb.mytable with the same PRIMARY KEY. This produces error 1062 (Duplicate Key).

The only way you could write to Slave without breaking MySQL Replication is this:

Master has databases db1,db2,db3
Slave is replicating databases db1,db2,db3 from the Master
You run CREATE DATABASE db4; on the Slave
You do INSERTs/UPDATEs/DELETEs only to db4 on the Slave
You do not do INSERTs/UPDATEs/DELETEs only to db1,db2,db3 on the Slave

To detect a Master and Slave being out of sync without downloading any tools, pick any table and run CHECKSUM TABLE against a table on the Master and the Slave's copy of the same table.

EXAMPLE

If you have a table mydb.mytable, run the command against it:

mysql> CHECKSUM TABLE mydb.mytable;

If the values do not come back the same, then something is out-of-sync.

If you want to examine a bunch of table in bulk, you can down Percona's MAATKIT. You will need two specific tools (Percona also has the Percona Toolkit that they themselves forked from MAATKIT which is now being promoted more)

mk-table-checksum
mk-table-sync

or

pt-table-checksum
pt-table-sync

pt-table-checksum will perform a CHECKSUM TABLE against all tables on Master and Slave. You can configure it to do all databases on just specific ones.

pt-table-sync can be run on a Slave against any table. Using the --print and --sync-to-master options, you can see what SQL statements need to be executed on the Slave to have it perfectly match the Master. This tool does not work with table that lacks a PRIMARY KEY or UNIQUE KEY.

I have used MAATKIT for years. I still do. I have not tried the Percona Toolkit yet, but I am sure it should be of the same quality as MAATKIT.


Default values in function, arguments dependency, PostgreSQL

Maybe using Postgres' support of polymorphism might be of use here.

First create the base function with both parameters.

CREATE FUNCTION years_compare(IN year1 integer,
                              IN year2 integer)
                RETURNS integer
AS
$$
BEGIN
  RETURN 1; --<replace by some calculated value>
END;
$$
LANGUAGE PLpgSQL;

Then create one function with only one parameter calling the base function with the first parameter and the calculated second parameter.

CREATE FUNCTION years_compare(IN year1 integer)
                RETURNS integer
AS
$$
BEGIN
  RETURN years_compare(year1, year1 - 1);
END;
$$
LANGUAGE PLpgSQL;

Finally create a parameterless version of the function calling the second one with a calculated parameter.

CREATE FUNCTION years_compare()
                RETURNS integer
AS
$$
BEGIN
  RETURN years_compare(date_part('year'::text, ('now'::text)::date)::integer);
END;
$$
LANGUAGE PLpgSQL;

Then you have three versions of the function with zero to two parameters.


Query using a filtered index but wrong estimated number of rows

This is not intuitive but try adding the char[1] column to the filtered index definition:

CREATE NONCLUSTERED INDEX [IX_HAND00F_HDCHKINDT]
ON [TYDATPRD].[HAND00F]
(
    [HDCHKINDT], STDRECSTS
)
WHERE [STDRECSTS] = 'A'
WITH (DROP_EXISTING = ON) ON [INDEXES];

In my testing, this led to the right estimates.

As an aside, I noticed that the plans in your question were different - one was properly parameterized and the other had a constant. Be very careful about how you're doing local testing with local variables and typed constants, especially if in the real world this will be done by parameters to stored procedures.

Also, be very careful about case - literals in the where clause like = 'a' and = 'A' will generate different plans if they don't get parameterized, because the query text is different. Never mind that in some collations they won't yield the same results. (And does that char(1) column need to allow NULL?)


Does MySQL have problems with nested insert like with subqueries in where?

Using OR in the JOIN part of your query results in the query to be performed for all 4 million records returned from the result set of the left hand side OR clause.

So using a solution with UNION is definitely faster.


Is dropping foreign key constriants an event in Oracle?

The following trigger captures the drop constraint operation.

create or replace trigger foreign_drop after create or alter or drop on database
begin
  dbms_output.put_line('DDL Fired!');
end foreign_drop;
/
SQL> set serveroutput on
SQL> CREATE TABLE t2
( id number,
  CONSTRAINT fk_id
    FOREIGN KEY (id)
    REFERENCES t1(id)
);  2    3    4    5    6
DDL Fired!

Table created.

SQL> alter table t2 drop constraint fk_id;
DDL Fired!

Table altered.


MySQL SELECT slow, but only 2 x 300K rows and indexes

KEY brand_id etc (brand_id,shopid,last_seen,price,saleprice)

This index does not support this query well.
The first field in the index is brand_id, but you are only filtering by "> 0".
Since I doubt you have many brands with negative id's, this probably isn't going to help very much.
The next field in the index is shopid, which doesn't appear in your where clause at all.
Based on this cursory analysis (and MySQL will do a lot more than that), this index will probably be discounted and the table scanned serially instead.
A better discriminator for choosing the rows you want returned would be last_seen.  Consider adding an index on that (or with that as its first field) and you should see some improvement.


Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

No. No gain at all. The manual explicitly states:

  Tip: There is no performance difference among these three types, apart
  from increased storage space when using the blank-padded type, and a
  few extra CPU cycles to check the length when storing into a
  length-constrained column. While character(n) has performance
  advantages in some other database systems, there is no such advantage
  in PostgreSQL; in fact character(n) is usually the slowest of the
  three because of its additional storage costs. In most situations text
  or character varying should be used instead.

Bold emphasis mine.

char(n) is a largely outdated, useless type. Stick with varchar(n). Without need to enforce a maximum length, varchar or text are a tiny bit faster. (You won't be able to measure a difference.)

If all strings are exactly 36 characters in length, there is no storage saving either way, not even a minuscule one. Both have exactly the same size on disk and in RAM. You can test with pg_column_size() (on an expression and on a table column).

And if all strings must have 36 characters, rather make it text with a CHECK (length(col) = 36) constraint enforcing exact length, not varchar(36) only enforcing max. length.

Related:

Any downsides of using data type “text” for storing strings?

You didn't ask for other options, but I'll mention two:

COLLATION - unless you are running your DB with the "C" collation. Collation is often overlooked and possibly expensive. Since your strings don't seem to be meaningful in a natural language, there is probably no point in following COLLATION rules. Related:

How do I efficiently get "the most recent corresponding row"?
EXECUTE within function not using index?

Extensive benchmark comparing (among other) the effect of COLLATE "C" on performance:

Slow query ordering by a column in a joined table

UUID, obviously. Your string suspiciously looks like a UUID (32 hex digits plus 4 delimiters). It would be much more efficient to store these as actual uuid data type, which is faster in multiple ways and only occupies 16 bytes - as opposed to 37 bytes in RAM for either char(36) or varchar(36) (stored without delimiters, just the 32 defining char), or 33 bytes on disk. But alignment padding would result in 40 bytes either way in many cases.) COLLATION would be irrelevant for the uuid data type, too.

SELECT '922475bb-ad93-43ee-9487-d2671b886479'::uuid

This may be helpful (last chapters):

Convert hex in text representation to decimal number

See also:

What is the overhead for varchar(n)?
What is the optimal data type for an MD5 field?


Why the Accounting DB Schemas do not allow to maintain a clean Products Table?

One practice I've seen in financial systems is to snapshot all relevant data at the time of the transaction and store it statically. This would entail relevant product, partner, transactional, audit etc. values being serialized (JSON, XML or proprietary binary format) into a single column. "Relevant" includes de-referencing foreign keys and eliminating system-internal surrogate keys.

Once serialized the future life of the transaction need not rely on the original normalized values being available. They can be amended or removed to satisfy the on-going needs of the OLTP system they are there to support.

Yes, this does duplicate the data and increase the size of the database. These systems did not leave the completed transactions in situ for long, however. Batch jobs promptly removed those rows from the OLTP database into fulfillment, archive and warehouse databases.

This had several benefits. The OLTP schema was separated from that of the other databases. Audit trail was guaranteed as the serialized values were never re-written. Patches to the reference tables could never possibly affect in-process activity. And, as in your case, no-longer needed values need not be retained simply to support history.


Speed up simple MySQL query on large database

Since you're reading every record in the table, the database will simply pull everything you want, wrap it [all] up and send it back across the network to your client machine.  The more data you pull, the larger that payload is going to be and the slower it's going to travel.

I frequently run this very simple query
The database is static and the data inside the database will not change.

To me, these two statements makes no sense together.
If the data does not change, then you should read it once, perhaps at application startup, and hold it in the client application.  Don't keep re-reading the same thing over and over again.  That's pointless.
Then there's the question of what you're doing with all this data after you've retrieved it from the database.  I sincerely hope you're not putting it all on a screen in front of a User - a million rows to read through?  No thanks!
I think you need to explain your situation a little more.


Is there a free software addition to SQLite available for data-replication?

Berkeley DB's SQL API is completely SQLite compatible and offers HA/replication for high availability. The combination of SQLite's easy-of-use and Berkeley DB's scalability and reliability are a great combination.

Disclaimer: I'm the Product Manager for Berkeley DB, so I'm a little biased. However, one of the main benefits of adding the SQLite API to Berkeley DB is that SQLite applications can now have HA/replication for mission-critical, highly available applications.


Join 3 tables and return only distinct values for max datetime

This is a typical "greatest N per group" problem which is usually solved using window functions:

select caseno, date, time, tranno, pcode, pdesc, user
from (
  select a.caseno,a.date,a.time,a.tranno,b.pcode,c.pdesc,a.user,
         row_number() over (partition by a.caseno order by a.tranno desc) as rn
  from tablea a
    right join tableb b on a.caseno=b.caseno
    right join tablec c on c.pcode=b.pcode
  where a.date between 140124 and 140331
) t
where rn = 1;

You could also use your obfuscated timestamp information to do the sorting:

row_number() over (partition by a.caseno order by a.date desc, a.time desc) as rn

Given your example it won't probably make a difference.


You need to use order by credits desc (descending) in order to get the 3 highest credits so the sql would be:

select * from `records`
where `type` = 'my'
order by credits desc
limit 3;


Select rows with same id but null and some other value in another column for that id

You should be able to use conditional aggregation to get the username with both a value in col2 as well as null.

I'd suggest using a HAVING clause with the conditions. The query would be similar to:

select username
from yourtable
group by username
having sum(case when col2 is not null then 1 else 0 end) = 1
  and sum(case when col2 is null then 1 else 0 end) = 1

See SQL Fiddle with Demo. This query groups your data by each username and then uses conditional logic to check if col2 meets both conditions you want - where col2 is not null and col2 is null.

You can then use this in a subquery, etc to get the username and col2 values:

select
  t.username,
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) = 1
    and sum(case when col2 is null then 1 else 0 end) = 1
) d
  on t.username = d.username

See SQL Fiddle with Demo.

If you have more than one col2 row with both null and another value, then you just need to alter the HAVING clause slightly:

select
  t.username,
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) >= 1
    and sum(case when col2 is null then 1 else 0 end) >= 1
) d
  on t.username = d.username;

See SQL Fiddle with Demo


Is it possible to host pbix and rdl files on the same report server instance?

yes, it is possible and I am using it on my organization.

Just one tip: to deploy reports, we need to provide the Report Server URL (by default /reportserver) but on Power BI, we need to provide the report manager URL (default /reports).


Full recovery model and tablock inserts logging

When using TABLOCK your logging even in full model is called "efficient logging", it's when instead of logging every insert row-by-row the whole pages are logged.

It would be minimally logged in simple and bulk logged modes with only page allocations in the log, but in full recovery model you have fully formatted pages of data.

So the number of records that go to the log in simple and full models when inserting with tablock will be nearly the same but the content is different: in simple/bulk logged there will be only page numbers, in full there will be complete pages.

And it is really full logging since the insert operation can be fully reconstituted using these log records while in simple/bulk logged you have only information sufficient to rollback the insert.


How to have one Auto Incremented id for two separate tables in mysql?

If the two tables share ID space, surely then they should have a common parent table?

I would create a parent table and make the two other tables children of it with foreign keys. The parent has the autoincrement field, and you have to insert there first before inserting the child. Add a "type" column to the parent table so you know in which table to look for the child with the given ID.

The biggest advantage to this approach is that you don't need any triggers. No performance or synchronicity problems to worry about.

In PostgreSQL you would implement this using inheritance, but I don't think MySQL has this feature.


Selecting most recent date when dates are stored in another table

Please try the following.

If you need most recent date for every song over all meeting instances:

select song_id, max(meeting_date)
from song_instances si
    join meeting_instances mi on mi.meeting_instance_id = si.meeting_instance_id
group by song_id

If you're interested only in particular meeting (meeting_id = 4):

select song_id, max(meeting_date)
from song_instances si
    join meeting_instances mi on mi.meeting_instance_id = si.meeting_instance_id
where meeting_id = 4
group by song_id


MySQL - Advice for using two tables, one each for open and closed records?

Add a column to distinguish open/closed.  It should be small (TINYINT or ENUM).

Then pick one:

Plan A:

Then have most indexes be composite and start with that column.  These indexes will make it efficient to find just opened (or just closed) rows.

If you also need to find both opened and closed, then also have index(es) that do not start with the new flag.

Plan B:

PARTITION by the new column.


Without knowing the query set that's going to hit this structure, I'll give a generalized answer:

I try to use the TIMESTAMP type for this, which works well with time/date functions, and is timezone agnostic (under the hood it is storing a unix timestamp).

By default, this will only be a 4 byte wide key (instead of an 8 byte bigint), so if you index other columns (which always tacks on the primary key at the end...albeit hidden), those structures can stay skinny and quick as well...and also not eat your RAM ;-).

TIMESTAMP by default has a resolution of 1 second, which is overkill for your purposes, but the easy use of date functions on it makes that worth it.

Main downside: TIMESTAMP is based on a 32-bit unix timestamp integer (it stores that way, but displays as a date/time), so it does eventually run out. At that time (decades from now, when RAM is cheap) you can adjust it, and the date/time display/handling nature of the type will allow you to make the change transparently to your software.

If you want to use it as an integer, you can use FROM_UNIXTIME(your_integer_here) to insert, and UNIX_TIMESTAMP(date) in queries that need that. I'm going to guess though that the date/time format is easier to work with for you though.

Also, you do not require a second UNIQUE key, as any PRIMARY key implies this already, and is included for free in the storage/RAM price of your data table (which is sorted by PK as a B-Tree...your table is an index, and can/will be used as such).


Return results that contain or do not contain punctuation

I had a similar situation a while ago. It was a project in Ireland for a membership system for a cultural organisation and one of the requirements was that the system handle Irish names.

Irish names can be very complex with O, O', Ó, Ó', Mac, Mc', Mc (all with or without spaces after) and in some cases names like Ua hÚigínn (not a typo - the small h followed by upper case Ú is correct in Irish). As you can see, there are also accented characters (a, e, i, o and u can take acute accents). The problem is that many people use many different variants of these names with/without apostrophes, accents and alternative spellings.

You can look on these like your differing spellings in your some_value field!

What we did was to create a "shadow" (some_value_search) column and search on that! On INSERT or UPDATE, we eliminated apostrophes, accents, hyphens and spaces (in the names) using a TRIGGER. Obviously, your phrases need spaces, but you could turn person's thing into PERSONS THING and thing-thing into THINGTHING.

Then you display your data using the original some_value field, but always search on some_value_search.

This was in the days before computed/calculated/generated fields. But MySQL (as of 5.7.6) has them and you should be able to eliminate a lot/most/all of any trigger code using them.

col_name data_type [GENERATED ALWAYS] AS (expression)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

Your (expression) above could be (REPLACE(REPLACE(field, '-', ''), ''', '') to eliminate spaces and hyphens. However, thanks to MySQL's lack of a [TRANSLATE()][3] function, the code for making a string all-capitals is horrible (26 nested REPLACEs!). If you need to do anything complex, there are always TRIGGERs, but the above solution is fairly standard nowadays!


Why is my Target Server Memory slightly higher than my Total Server Memory?

I must say this is normal and nothing to worry about. This blog tells how target server memory calculated. Please see the forumula below

  Target1 = Current committed pages of SQL Server + ( Available Physical
  Memory - min (Total Physical Memory Pages / 20, Available Physical
  Memory Pages / 2))

  ullAvailPageFile: The maximum amount of memory the current process can
  commit, in bytes. This value is equal to or smaller than the
  system-wide available commit value. To calculate the system-wide
  available commit value, call GetPerformanceInfo and subtract the value
  of CommitTotal from the value of CommitLimit.

  If (Max Server Memory < ullAvailPageFile)

  {

  Target2= Max Server Memory

  }

  Else

  {

  Target2=Total Physical Memory

  }

  Target Server Memory (KB) =Minimum (Target1,Target2)

  So if AvailablePhysicalMemory is very high (or) when MaxServermemory
  is low then Target Server Memory (KB) would give you the
  MaxServerMemory  else value derived from above formula.

As per my findings using this formula even if target server memory is bit greater than total it is fine. And this is by virtue of how the value is calculated.

If target server memory is greater that total server memory it does not always signify a memory pressure. You must come out of this belief(if you have one). Like you mentioned other counters have to be taken into picture. SQl Server changes its target and total server memory values many a times and this depends on load on system. Consider a scenario where you run index rebuild for all big tables there would be flurry of activity and PLE would certainly drop increasing target memory but this does not means it is a memory pressure. SQL Server memory just adjusted itslef to current load.

In ideal world you should have RAM such that it can shold all databases in memory but this is many a times not possible.


You could try something like this

SELECT CASE WHEN RIGHT(fieldname,1) = '"' AND LEFT(fieldname,1) = '"' THEN REVERSE(STUFF(REVERSE(STUFF(fieldname, 1, 1, '')), 1, 1, ''))
       WHEN RIGHT(fieldname,1) = '"' THEN REVERSE(STUFF(REVERSE(fieldname), 1, 1, ''))
       WHEN LEFT(fieldname,1) = '"' THEN STUFF(fieldname, 1, 1, '')
       ELSE fieldname END as fieldname
FROM #temp;

DB<>Fiddle


There are multiple audit logging products available. If you are using Percona or MariaDB flavour of MySQL you have the option of their plugin. If you are using Oracle MySQL you can pay for their enterprise version of audit plugin (as part of Enterprise Edition). There is also an audit plugin from McAfee that will fill this requirement and is generally available cross-alternative and from 5.1+. These products permit you to log both logins and queries. Finally there's a plugin to track logins only from a community contributor. Links to all below.

http://www.percona.com/doc/percona-server/5.5/management/audit_log_plugin.html
https://mariadb.com/kb/en/mariadb/documentation/plugins/mariadb-audit-plugin/
http://dev.mysql.com/doc/refman/5.5/en/audit-log-plugin.html
http://code.openark.org/blog/mysql/introducing-audit_login-simple-mysql-login-logfile-based-auditing


Proper way to store a password within a SQL Server agent job

One risk mitigation is to use a signed stored procedure.  Here's an example of this in action.  In summary, you package all the sensitive action (table access etc.) inside a stored procedure.  You create a certificate in SQL server to sign that SP.  You create a new login who's only ability is to execute the SP and you send the password to this low-privilege login in plaintext.  Depending on how sensitive the data coming from the SP are this can limit damage from a password leak.


Test table & data:

create table jsontest
(
jsoncolumn json
);

insert into jsontest values ('{"columns":
[{"name":"cusno","displayName":"Customer number","type":"text"},
{"name":"cname","displayName":"Name","type":"text"},
{"name":"cadd1","displayName":"Address","type":"text"},
{"name":"cstte","displayName":"State","type":"text"}],
"variables":[{"name":"var1", "type":"text"},
{"name": "var2","type":"text"}]}');

Query:

with data as
( select json_array_elements( jsoncolumn ->'columns' ) as element
  from jsontest
)
select *
from data
where cast(element->>'name' as varchar)  like '%cu%'
or cast(element->>'displayName' as varchar)  like '%cu%';

Test:

postgres=# with data as ( select json_array_elements( jsoncolumn ->'columns' ) as element from jsontest )
select * from data where cast(element->>'name' as varchar)  like '%cu%' or cast(element->>'displayName' as varchar)  like '%cu%';
                            element
----------------------------------------------------------------
 {"name":"cusno","displayName":"Customer number","type":"text"}
(1 row)

postgres=#


Unable to login to SQL Server + SQL Server Authentication

Run Sql Server locally with -m and -f flags and login to it as local admin

OR

login as domain administrator


How to verify that at least one point fall into a polygon with Postgis?

You're close. I wouldn't use EXISTS, which is really only useful for reducing a set. I would go with CROSS JOIN LATERAL and then coalesce the result. See how this performs.

SELECT a.id,
  coalesce(result, false) AS flag
FROM a
CROSS JOIN LATERAL (
  SELECT true
  FROM b
  WHERE ST_Contains(a.geom, b.geom)
  LIMIT 1
) AS r(result);

This should work on an index if you have a spatial index on a.geom and b.geom (and you should have indexes for this workload). This also looks like you're caching the results for mere performance improvements. I would suggest under normal circumstances a MATERIALIZED VIEW.

Alternatively you should be able to go,

SELECT
  a.id,
  bool_or(b.geom IS NOT NULL) AS flag
FROM a
LEFT OUTER JOIN b
  ON ST_Contains(a.geom, b.geom)
GROUP BY a.id;


Return every instance of a string contains search

Use following query if you want to count the number of occurrence of the string 'first' in the TextColumn each row.

SELECT (LEN(TextColumn) -
LEN(REPLACE(TextColumn, 'first', '')))/LEN('first') as NumOfOccurrence,
TextColumn FROM dbo.SampleTable;

Update: To cover the trailing space case (as caught by Twinkles in the following comment), I modified the query as below.

SELECT (LEN(TextColumn) -
LEN(REPLACE(TextColumn, 'a ', '')))/(LEN('a ' + 'x') - 1) as NumOfOccurrence,
TextColumn, id FROM dbo.SampleTable;

Following code gets the multiple instances in a New String Column, with separator defined as @sep and search word defined as @word, where @sep <> @ word:

declare @sep as char(2);
declare @word as varchar(24);
set @sep = ', ';
set @word = 'first';

SELECT  ID, TextColumn, ISNULL(STUFF(REPLICATE(@sep + @word, (LEN(TextColumn) -
LEN(REPLACE(TextColumn, @word, '')))/(LEN(@word + 'x') - 1)), 1, 1, ''), '')
as [New String Column] FROM dbo.SampleTable;


SSMS Template Browser Add-In development

That would likely fall into the realm of SSMS Add-In Development.  Having said that, I'm not certain you can add a button to the Template Browser, even then.
Microsoft has a GitHub repo with a sample SSMS Add-In here.


Centos 6.2 mysql configuration file help

The first thing you should configure is the innoDB buffer pool size. According to this blog entry by Percona, the ideal buffer pool size is 10% larger than your total data size.

Other settings to add: InnoDB log file size, query cache size.

innodb_buffer_pool_size=12G

query_cache_size=500M

innodb_log_file_size=64M


Using 'Listagg' function and 'Regular Expression' to REVERSE the value of a specific column in Oracle

select col_id, reverse(col_name) as col_name from tbl_test;

    COL_ID COL_NAME
---------- ----------
         1 pantea
         2 apple
         3 banana


SQL Server freezes (because of application), need logging

SQL Server has a Dedicated Administrative Connection, see How to: Use the Dedicated Administrator Connection with SQL Server Management Studio. The DAC uses distinct, pre-reserved resources (memory, CPU, IO ports etc) exactly so it can be used even when the server is 'frozen'. From the DAC you can run queries that can identify the cause of the spike.

When the server is restarted the ERRORLOG is not deleted, it gets recycled. The errorlog from before the shutdown is usually ERRORLOG.1, then ERRORLOG.2 etc, and is located in the same folder as the current one. Just open it and inspect the tail of the file. Your previous restart logs are probably there still, have a look and see what you can find. Also see if there are any .mdmp files in the same location...


Let's simplify the problem because I think it will be easier to visualize when you don't start adding in the complication of joins and other clauses.

Let's think about objects in the system catalog sys.all_objects, and say you want one of each of the following object types in the resultset:

table ('U')
procedure ('P')
view ('V')
any type of function ('FN', 'IF', 'FS', 'AF', 'TF')

The whole set is simply:

SELECT * FROM sys.all_objects
WHERE type IN (N'U',N'P',N'V',N'FN',N'IF',N'FS',N'AF',N'TF');

To get a single, random row from each type (or set of types), you can say:

;WITH wholeset AS
(
  SELECT *, rn = ROW_NUMBER() OVER
    (
      PARTITION BY CASE
        WHEN [type] IN (N'FN',N'IF',N'FS',N'AF',N'TF')
          THEN N'FN'
        ELSE
          [type]
        END ORDER BY NEWID()
    )
  FROM sys.all_objects
  WHERE type IN (N'U',N'P',N'V',N'FN',N'IF',N'FS',N'AF',N'TF')
)
SELECT * FROM wholeset WHERE rn = 1;

Applying similar logic to your query, assuming -1 is not a valid category, in which case you could use -999 or some other impossible token value:

;WITH wholeset AS
(
  SELECT ...cols..., rn = ROW_NUMBER() OVER
    (
      PARTITION BY CASE
        WHEN Categorie.ParentCategorie = 292 THEN -1
        ELSE Artikel.Categorie
      END ORDER BY NEWID()
    )
  FROM dbo.Artikel -- always use dbo prefix
  INNER JOIN dbo.Categorie ON Artikel.CategorieID = Categorie.CategorieID
  INNER JOIN dbo.CategorieTaal ON Categorie.CategorieID = CategorieTaal.CategorieID
  INNER JOIN dbo.CategorieGroep on Categorie.CategorieGroepID = CategorieGroep.CategorieGroepID
  INNER JOIN dbo.CategorieGroepTaal on CategorieGroep.CategorieGroepID = CategorieGroepTaal.CategorieGroepID
  INNER JOIN dbo.Taal ON CategorieTaal.TaalCode = Taal.TaalCode AND ArtikelTaal.TaalCode = Taal.TaalCode and CategorieGroepTaal.TaalCode = taal.TaalCode
  INNER JOIN dbo.Levertijd ON Artikel.LevertijdID = Levertijd.LevertijdID
  WHERE (Categorie.ParentCategorieID = 292 or artikel.CategorieID = 246 or artikel.CategorieID = 247 or artikel.CategorieID = 288)
  AND taal.taalcode = 'EN'
)
SELECT * FROM wholeset WHERE rn = 1;

There are some edge cases here, like if you don't have any rows in a category, or if you only have one row that is in the parent category and that is the only row in one of the other three categories as well. But for most data sets this should work pretty reliably.


Change the owner of an offline database without bringing it online

This can't be done without bringing the database online, and this is not just a GUI problem.

ALTER AUTHORIZATION ON DATABASE::test TO sa;

Yields:

  Msg 942, Level 14, State 4, Line 1
  Database 'test' cannot be opened because it is offline.

So, as one of the comments suggested, bring it online, set it to single_user if you need to, change the owner, then take it back offline.

ALTER DATABASE test SET ONLINE;
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER AUTHORIZATION ON DATABASE::test TO sa;
ALTER DATABASE test SET MULTI_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE test SET OFFLINE WITH ROLLBACK IMMEDIATE;

Though I have to wonder, why do you care who the owner is until you've brought the database online?


SQL actually executed as planned in ORACLE?

The short answer is that no, the same SQL doesn't automatically lead to the same execution plan.

For example, one may have a small table and the optimizer may realise that there's no point in using an index since an FTS (Full Table Scan) would be cheaper than trawling through an index and then performing lookups.

The "No" answer is the reason that Oracle introduced Outlines to implement Plan Stability. Many posts on the Oracle forums concern poor DBAs complaining about the fact that their system worked fine on Tuesday but that on Wednesday it had slowed right down. Further investigation showed that this was due to a critical change in the Execution Plan for a particular query.

What Plan Stability is basically doing is while accepting that some plans may be sub-optimal, the DBAs/Devs would prefer a stable system over one which might change catastrophically in an unpredictable fashion.

Some good information about this can be found here and here. The oracle-base site is one of the best sources of Oracle information and Tanel Poder is a very big hitter in the Oracle world.


Issue importing large file into SQL server

You're getting a truncation error, meaning the data that's coming in is larger than the field size you have specified.  Try increasing the destination table field size and you should be good to go.

I get these alot when I work with email addresses as the SQL import wizard defaults to 50, I always have to increase it every time I do a fresh import.


Are passwordless logins like ##MS_SQLResourceSigningCertificate## , ##MS_AgentSigningCertificate## a vulnerability?

These logins are created from a certificate.  In fact, if you run the following query:

select
    name,
    type_desc
from sys.server_principals
where type = 'c';

You will see that they are of type CERTIFICATE_MAPPED_LOGIN.  They are used typically to sign code.  And you cannot use a certificate mapped login to connect with SQL Server.  Please see this BOL reference on CREATE LOGIN:

  Logins created from certificates or asymmetric keys are used only for code signing. They cannot be used to connect to SQL Server.


Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working

The usual cause for this problem is that pt-table-sync is using CRC32 as a hash algorithm, because it's fast and cheap, but it can also give collisions. "codding" and "gnu" have the same crc32, for example. I recommend trying again with MD5 as the function.


PostgreSQL import XML to empty database, automatically create columns

I was able to automate the process enough, keyword being enough. To help anyone else dealing with exporting from Microsoft Access and importing to PostgreSQL I'm providing details that helped us work through the errors we encountered. While I'm fairly competent with MySQL and new (though still reasonably well at PostgreSQL) the people who setup the various Microsoft Access databases didn't setup the databases/tables/columns well (understatement).
First download "MS Access to PostgreSQL", it's freeware and I plan to make a donation to them. Currently on their download page the download link is on the right side of the table, threw me off for a little bit.
http://www.bullzip.com/products/a2p/info.php
Secondly I needed to download the "2007 Office System Driver: Data Connectivity Components" which works fine with Office 2010...
http://www.microsoft.com/en-us/download/details.aspx?id=23734
Thirdly I opened up MS Access to PostgreSQL and followed the directions having the data imported to an existing empty database. Keep in mind that the program (at least by default) will spawn errors if there are tables already there with the same names (it won't automatically append/overwrite). Thankfully it provides a checkbox list of tables so when you've confirmed via psql or pgAdmin III (you must click on the left column before pressing F5 or it won't update the database/tables information) that at least some of the data has been imported it is time to deal with errors.
There were two error types that I've encountered for the most part thus far with just two separate databases. For accounting the person who setup Access used a proprietary currency type for some of the columns, the program didn't like that so I was able to maintain the row values by simply converting it to the text type. I'm correcting the database structure and all that after I've freed it from Microsoft's shenanigans as I'm trying to reduce the complexity of exporting it. You can change the column type by choosing the table, then clicking on the Fields menu in Access 2010, then under the secondary Formatting hierarchy you'll find "Display Type:".
Apparently the program also does not like it when a column shares the same name as it's table so I simply added a prefix or suffix underscore and make note of it to correct after the dubious process of migrating the data is complete.
In general a necessary practice in Access is to right-click on the table name under the Tables object list, at the bottom of the context menu should be a Check Web Compatibility menu item. If it finds errors it will create a whole new table in the database; in Access 2010 URLs to the Microsoft website were clickable.
Another error I encountered was binary data, an image that exceeded the programs ability to import. I exported the data as XML and browsed to determine which row had the data, found the ID in Access and removed it. You can save it (in the Microsoft way at least) by copying the row itself and paste it in to Word...how to export it as a normal image I'm not sure offhand.
The following are my notes that I've written down to quickly repeat the process to get Access data migrated to PostgreSQL. I originally was going to just export as XML and import to PostgreSQL though I was unable to find a working solution in that manner and I would have still have had to deal with some of the issues mentioned here.

Access to PostgreSQL - Import Notes
1.) Use freeware "MS Access to PostgreSQL".
2.) When encountering errors use MS Access table web compatibility check (right click table).
3.) If further stumped...
A.) Allow import to create columns.
B.) Export Access to Excel.
C.) Export Excel to CSV/DOS.
D.) Ensure that the first id is NOT 0, must be 1 (may be subjective though).
4.) If table data import success then repeat step 1 but unselect trouble table AND already imported tables.


The .ini needs to contain the plugin followed by the components.

The document Authentication Plugin - Unix Socket hints that the component name is unix_socket.

So the correct contents of the file should be:

echo -e 'auth_socket\nunix_socket' >> /usr/lib/mariadb/plugin/unix_socket.ini

As a workaround use plugin-load=auth_socket in /etc/my.cnf.d/mariadb-server.cnf seems also sufficient.


MongoDB document insertion and referencing

Why not put it all in the same document? You could also put it in a different collection. One way or another since you don't have any schemas to update, just try whatever suits your case. If this is a many to many relationship (like films-actors) then embedding everything wouldn't really work. You would have to reference other documents.

Film Collection:
    {
      {
          film1: ....
          actors: [actor_ids]
      },
      etc...
    }

Actor Collection:
{
     {
          actor1:....
          actor_id: objectId
      },
     etc...
}

https://docs.mongodb.com/manual/core/data-model-design/


How to safely insert a jsonb into a table?

Is it dangerous to use jsonb (that comes from the user) within queries?

Not it's not dangerous because that JSON is essentially just text as it comes from the client, and you've protected yourself by using prepared statements. This text will be transparently fed to the internal function jsonb_in, and you'll be totally safe.


PostgreSQL custom operator UUID to varchar

What you to do is CREATE CAST not an operator. This is the problem:

SELECT pg_typeof(uuid), uuid = uuid::varchar AS eq
FROM gen_random_uuid() AS t(uuid);
ERROR:  operator does not exist: uuid = character varying
LINE 1: SELECT pg_typeof(uuid), uuid = uuid::varchar FROM gen_random...
                                     ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

So we need to create a CAST. This promotes varchar to uuid when needed. Though you could go the other way if you really wanted. If you do this you need to create the cast (uuid AS text). The type system doesn't know varchar: we don't use that in PostgreSQL; it's essentially text with a type-inconsequential length constraint and thus slower.

CREATE CAST (varchar AS uuid)
  WITH INOUT
  AS IMPLICIT;

And now you can try again.

 pg_typeof | eq
-----------+----------
 uuid      | t
(1 row)

For reference,

IMPLICIT

  Indicates that the cast can be invoked implicitly in any context.

INOUT

  Indicates that the cast is an I/O conversion cast, performed by invoking the output function of the source data type, and passing the resulting string to the input function of the target data type.

That said, all of the views have to be recreated when an underlying type changes,

CREATE TABLE foo(uuid)
AS
  VALUES (gen_random_uuid()::varchar);

CREATE VIEW bar AS TABLE foo;

Now we try to change the type in foo

ALTER TABLE foo
  ALTER uuid
  SET DATA TYPE uuid;
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view bar depends on column "uuid"

That fails, so we drop bar change the type and recreate it,

BEGIN;
  DROP VIEW bar;
  ALTER TABLE foo ALTER uuid SET DATA TYPE uuid;
  CREATE VIEW bar AS TABLE foo;
COMMIT;

And we have joy.

 \d bar;
     View "public.bar"
 Column | Type | Modifiers
--------+------+-----------
 uuid   | uuid |


I am assuming the following:

This is a one time job you want to get done
You want another table, in almost identical format to the former, where each row in the original table is duplicated many times in the new table, as many times as there are days between start_date and end_date.

Here's a way to do so:

First, create a table with all possible dates (actually you can use an integer tally table as described above, but I will simplify for purpose of this demonstration):

CREATE TABLE all_dates(dt DATETIME PRIMARY KEY);

INSERT into the above table all dates possible in your system (e.g. 1970-01-01 and up to 2199-12-31, whatever). I mentioned my assumption is that this is a one time job, so this table can be later dropped.

Also create your new exploded table:

CREATE TABLE IF NOT EXISTS `staff_leave_exploded` (
  `id_exploded` int unsigned AUTO_INCREMENT
  `id_staff_leave` int(11) NOT NULL,
  `staff_id_staff` int(11) NOT NULL,
  `leave_type_id_leave_type` int(11) NOT NULL,
  `single_date` date NOT NULL,
  `joining_date` date NOT NULL,
  `is_half_day` date DEFAULT NULL,
  `approved` int(11) DEFAULT NULL,
  `approved_date` date DEFAULT NULL,
  PRIMARY KEY (`id_exploded`),
  KEY `id_staff_leave` (`id_staff_leave`),
  KEY `fk_staff_leave_staff1` (`staff_id_staff`),
  KEY `fk_staff_leave_leave_type1` (`leave_type_id_leave_type`)
) ENGINE=InnoDB;

And now

INSERT INTO staff_leave_exploded SELECT
  NULL, id_staff_leave, staff_id_staff, leave_type_id,
  dt,
  joining_date, is_half_day, approved, approved_date
FROM
  staff_leave JOIN all_dates ON (all_dates.dt BETWEEN start_date AND end_date)
;


PostgreSQL: Change default port used by utilites like psql, createdb, etc

PostgreSQL command-line utilities (and more generally all programs that rely on the libpq library) automatically use the environment variables PGPORT and PGHOST when they're defined.

So if you do in the shell:

$ PGPORT=5433; export PGPORT

any subsequent call to psql will act as if it has been invoked with the -p 5433 command-line option.

See Environment Variables in the libpq documentation for all these variables . They can be used to provide default values to almost every parameter of a connection to the database.


Oracle 11G Clob column with where clause

Prologue

You are not having problems with a "CLOB column with a WHERE clause".
You are having problems processing XML Documents stored in a CLOB field.

This is your third question regarding this task.

I recommend you Read The Fine Manual before you advance on your problem:  Oracle XML Developer's Guide

existsNode

existsNode is deprecated.  Don't use it.

Instead, use:

WHERE XMLExists( '/mt[Error="0"]' passing xml_data)

PL/SQL Error

I suspect this might be due to a conversion error.

You should start investigating this error by fixing your Schema such that you are storing numbers as NUMBER, dates as DATE, and XML Documents as XMLType.  Even if this isn't the case, your Schema would be better for it.

My other thought is: your XML Document has a string but you are attempting to extract a number.


Unexpected index scan instead of seek on update

Since there is no index on (tasklockedby) or any index that has that column in any column position, the only option for the optimizer to find which rows need to be updated is to do a table scan (so clustered index scan in this case).

I don't know why an index suggestion was not made.

I would suggest a simple index on (tasklockedby), without any INCLUDE columns, if you want to improve the efficiency of the UPDATE statement. If you have other (select) queries that need to get more columns in the result set, then you many be better with a different one.


It's not worth trying to cram everything into one generic table.  Better to just build off each entity like so:
CREATE TABLE business
(
  business_id  INT           NOT NULL
 ,name         VARCHAR(255)  NOT NULL
 ,CONSTRAINT PK_business PRIMARY KEY (business_id)
 ,CONSTRAINT AK_business UNIQUE (name)
)
;

CREATE TABLE business_hours_of_operation
(
  business_id  INT              NOT NULL
 ,day_of_week  TINYINT          NOT NULL
 ,start_hour   DECIMAL(4,2)     NOT NULL /* Expressed as fractional - i.e. 12:30 = 12.5 */
 ,hours_open   DECIMAL(5,2)     NOT NULL
 ,CONSTRAINT FK_Hours_Of_Operation_For_Business FOREIGN KEY (business_id) REFERENCES business (business_id)
 ,CONSTRAINT PK_Business_Hours_Of_Operation PRIMARY KEY (business_id, day_of_week, start_hour)
 ,CONSTRAINT CK_Business_Hours_Of_Operation_Day_Of_Week_Is_Valid CHECK(day_of_week >= 1 AND day_of_week <= 7)
 ,CONSTRAINT CK_Business_Hours_Of_Operation_Start_Hour_Is_Valid CHECK(start_hour >= 0.00 AND start_hour < 24.00)
 ,CONSTRAINT CK_Business_Hours_Of_Operation_Hours_Open_Is_Valid CHECK(hours_open > 0.00 AND hours_open <= 168.00)
)
;

To display everything in intervals:
SELECT
  business_id
 ,day_of_week AS start_day_of_week
 ,hour AS start_hour
 ,1 + MOD(day_of_week - 1 + FLOOR((hour + hours_open)/24),7) AS end_day_of_week
 ,MOD(hour + hours_open,24) AS end_hour
FROM
  business_hours_of_operation
;

You'll need to lock things down with stored procedures and/or triggers to ensure that the intervals don't overlap and any other business rules.
See Fiddle here.