A SQLCLR function could be used to emulate the TRANSLATE Transact-SQL function new to SQL Server 2017.

Function definition

CREATE ASSEMBLY [Translate] AUTHORIZATION [dbo]
FROM [encoded data, 9218 characters]
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[Translate]
(
    @Input nvarchar(4000),
    @Find nvarchar(4000),
    @Replace nvarchar(4000)
)
RETURNS nvarchar(4000)
AS EXTERNAL NAME
    [Translate].[UserDefinedFunctions].[Translate];

Usage

SELECT
    S.[name],
    S.string1,
    Result = dbo.Translate(S.string1, N'ACD', N'594')
FROM #String1 AS S;

╔════════╦═════════╦════════╗
║  name  ║ string1 ║ Result ║
╠════════╬═════════╬════════╣
║ John   ║ AB      ║ 5B     ║
║ Kyle   ║ ABC     ║ 5B9    ║
║ Steven ║ ABCD    ║ 5B94   ║
╚════════╩═════════╩════════╝

This simple demo implementation uses a case-sensitive comparison.

Source code

using Microsoft.SqlServer.Server;
using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [SqlFunction(
        DataAccess = DataAccessKind.None,
        IsDeterministic = true,
        IsPrecise = true,
        SystemDataAccess = SystemDataAccessKind.None
    )]
    [return: SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 4000)]
    public static SqlChars Translate
        (
            [SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 4000)]
            SqlChars Input,
            [SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 4000)]
            SqlChars Find,
            [SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 4000)]
            SqlChars Replace
        )
    {
        if (Input.IsNull || Find.IsNull || Replace.IsNull)
        {
            // Return unchanged input for any NULL parameters
            return Input;
        }

        if (Find.Length != Replace.Length)
        {
            throw new ArgumentException("Find and Replace parameters must have the same length.");
        }

        // For each character in the input string
        for (int i = 0; i < Input.Length; i++)
        {
            // For each character in the Find string
            for (int j = 0; j < Find.Length; j++)
            {
                // If the character matches...
                if (Input[i] == Find[j])
                {
                    // ...replace it
                    Input[i] = Replace[j];
                }
            }
        }
        return Input;
    }
}


No, that's what snapshots are for: a static copy of the database at a specific instant. If you update the base database, the updated data pages are moved from the base database's data files to the the snapshots data files, with a "copy on write" operation, so that the snapshot always contains the same version of the data as when it was created.


How to set up signed SSL Root and Leaf certificates on a PostgreSQL 11 database on Ubuntu and Windows 10 Power BI plus PostgreSQL?

I believe that PowerBI uses npgsql to connect, which defaults to the behavior that libpq/psql gives you under 'PGSSLMODE=verify-full'.  Can you connect to your server from Windows psql if you use 'PGSSLMODE=verify-full'?

The tutorial you linked assumes that you use the same machine for the certificate authority and for the database server, which will probably work for you.  But it also assumes the client machine is debian/ubuntu, not Windows.  So you will have do everything before "Configure the client" on your ubuntu machine.  But you probably already did that, based on the error message you are getting.

And you will have to do everything after "Configure the client" on your Windows machine, except you have to do it using Windows mechanisms, not linux mechanisms.  How to do that is a question of how your infrastructure is set up--you need some way to copy the file ca-cert.pem from the cert authority to the Windows machine, but that mechanism could be scp (if you have that installed on Windows, perhaps via cygwin) or a CIFS mount, or just copy the text of the .pem files out of putty and paste it into notepad, or use a thumbdrive.

Note that the tutorial is for using certs in both directions--for the client to verify the identity of the server, and for the server to verify the client too.  But it is rare for people to actually use client certs--they usually use server certs only.  If you are not using client certs, then you only need to install one cert on the client, which is the "ca-cert.pem" file (which should get named "root.crt" when installed on the client).  The other two are only needed for client certs.  And on Windows, the path it needs to be installed in is %APPDATA%\postgresql\root.crt, rather than ~/.postgresql/root.crt.


Ola Hallengren Index Optimize script failed because a deadlock

In most cases, running an index job should be done only once per week.  If there is an off peak time (like weekends) that you can run it that would be better.

  Index maintenance one day per week, Scheduling, How should I schedule jobs?

If you must run daily, you can change the variables below to address lock timeouts.

WaitAtLowPriorityAbortAfterWait
LockTimeout

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


How to change password version by myself in oracle?

If you have the proper permissions, you can change anyone's password. Of course, if you want to keep the password the same, just "changing" it so that it gets upgraded to new standards, you will need to know the current value.
And you don't have to expire it first. Expiring the password simply forces the user to set a "new" password the next time they log in. And even that depends on what client they are using. Some do not trap and handle expired passwords, and simply fail the logon.
Exactly which oracle doc were you quoting? (Never quote a source without full citation.)


how to add trigger in mysql workbench, without UDF, using EER design

A trigger could be like this:

DELIMITER $$

CREATE TRIGGER
    acts_INSERT_min_salary
BEFORE INSERT ON
    acts
FOR EACH ROW
    BEGIN
        DECLARE msg VARCHAR(255);
        IF NEW.salary <
            ( SELECT min_salary
              FROM artist
              WHERE artist_id = NEW.artist_artist_id
            )
        THEN
            SET msg = 'Violation of Minimum Actor Salary.' ;
            SIGNAL SQLSTATE '45000' SET message_text = msg ;
        END IF ;
    END ;

$$
DELIMITER ;

You will need a similar trigger for the UPDATE operation on acts and another one for the UPDATE operation on artist table:

CREATE TRIGGER
    artist_UPDATE_min_salary
BEFORE UPDATE ON
    artist
FOR EACH ROW
    BEGIN
        DECLARE msg VARCHAR(255);
        IF NEW.min_salary >                             -- this part
            ( SELECT MIN(salary)                        -- is slightly
              FROM acts                                 -- different than
              WHERE artist_artist_id = NEW.artist_id    -- the other
            )
        THEN
            SET msg = 'Violation of Minimum Actor Salary.' ;
            SIGNAL SQLSTATE '45000' SET message_text = msg ;
        END IF ;
    END ;


SQL Server index randomly performs poorly

I don't think this corruption. It sounds to me like out-of-date statistics are resulting in bad Execution Plans. When you rebuild the index the statistics are implicitly updated which is temporarily fixing your issue but not for the reason you think. You should have a strategy for updating Statistics (and re-organising/rebuilding indices).

We use Ola Hallengen's index maintenance scripts.


I have a largely automated process to upgrade versions and replicate data back to the old version using transaction replication without a snapshot init.  I need minimal downtime in this environment so we don't have time to make the databases generate a snapshot and send it back just in case we need to roll back.  You might allow yourself downtime but in that case if you will use a snapshot make sure to script out the 'create database' section with all the proper filegroups and options.  You'll need to snapshot back to a database.  It can create it for you but you're better off doing it in advance.

Be careful of PK constraints if you are not going to init.  If you get even 1 row in your old table your PKs which doesn't match, you will get PK conflicts and errors.

Have a backup plan handy so script out snapshot replication or something else like Red Gate Data Compare just in case things go sour.

Test this process repeatedly in staging and automate as much of it as you can.  That way it's consistent and easy in the day off.  Preferably check your code in.

Ensure you make the databases not writable and that all of your data is fully synced up, while ensuring nothing can write to the old db before you cut over clusters.

You can also look at switching your cluster IP address if you have tons of clients that you can't keep track of, but if you can, it's better to get a new IP.  If you change IPs make sure to automate that as well and ensure you update DNS/RDNS and add a CNAME if you need to the new machine with the old name just in case clients connect by IP.

If you have enough downtime capability you can roll back at your leisure.  Good luck!


Create publication is not working in PostgreSQL

As JJanes mentioned, Publication is available from Version 10.
I was using version 9.6.


Transaction log is full changing column type in a large table

Scott's answer made me realise you might have enough space for this:
Create a datetime2 column with a temporary name, and transfer the original column contents to it in batches (to prevent your log running out of space -- and I'm assuming your database is in Simple recovery model).
Then drop the original column, and rename the new column to the old column name.


Run out of virtual memory after added to triple physical RAM and change SQL Server memory option?

I believe your virtual memory should be, at minimum, the same size as your physical memory.

I once had a new server deployment that had tested well, but performed terribly under a full load. MS got involved, and finally figured out we had a 16GB page file, with 128GB physical memory. Their recommendation was that the page file be at least 1.5 times as large as physical memory.

Bumping our page file size up to 192 GB got performance back to normal. Not sure if 1.5 X physical memory is truly required (in fact, I doubt it), but page file significantly less than physical memory does seem to be a Bad Thing.

NOTE: Our system at the time was Windows Server 2008R2, SQL Server 2008.


Need FLOAT datatype with 2 digits after decimal point when importing from a CSV file

If the data is only used for this JDEdwards report, you could simply convert from dollars (164051, 0, 16578.98) to cents (multiply by 100) (16405100, 0, 1657898).

If the data is used in its correct form properly elsewhere, create a view that converts from dollars to cents, and have the JDE report use the view.

If you ever get fractional cents ($12358.9275), then you'd want to round to remove the fractional part. However, I believe that would be an issue now (it'd be reported as $1235892.75, from what you're saying).


Update postgres json value by other attribute

The concatenation operator || will do that:
update the_table
  set data = data || jsonb_build_object('new-attr', data -> 'my-value')
where data ? 'my-value'; --<< only update rows that contain the key

The ? operator tests if the key is present in the JSON value.

If your column is defined as json (rather than jsonb which it should be), you probably need to cast it, to make the || operator work properly: data::jsonb || ...


What is the use of the select statement in SQL(ite) triggers?

In a trigger body, the result of a SELECT statement is simply thrown away.

Using a SELECT statement makes sense only for its side effects, i.e., when using a user-defined function, or RAISE().


CREATE TRIGGER PreventCursorUDFs
    ON DATABASE
    FOR CREATE_FUNCTION
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML = EVENTDATA();

    IF LOWER(@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]','NVARCHAR(MAX)'))
      LIKE N'%declare%cursor%fetch%'
    BEGIN
        RAISERROR('Yo, no cursors in functions!', 11, 1);
        ROLLBACK;
    END
END
GO

With a caveat, this will also disallow a trigger that contains a comment, like:

/* we used to do this a dumb way, using DECLARE CURSOR and FETCH */
/* now we're a little smarter and use this table-valued function */

...or if you have really inconvenient queries such as:

SELECT [declare] = [cursor] * 10 FROM dbo.[fetch];

...or even if you call your function:

CREATE FUNCTION dbo.ModeClarevoyantForCursoryFetching()
RETURNS TABLE
AS ...

EDIT

Addressing Nick's question here instead of as a comment, because it's going to get a little long-winded.

PBM is great for some stuff, but not so great at other stuff. This requirement falls under the "other stuff" category. The main problem is that the definition (e.g. OBJECT_DEFINITION()) is not exposed as a property to facets like user-defined functions. This means your condition cannot simply express something like:

@ObjectDefinition NOT LIKE '%DECLARE%CURSOR%FETCH%'

If this were the case, you could create this condition, wrap a policy around it, set it to "On prevent: change" and go to lunch. To implement this as a policy requires a little more work.

Since @ObjectDefinition is not a valid Field for this facet, we need to obtain it using ExecuteSql(). So your condition would have to be something like:

ExecuteSql('Numeric', 'SELECT x = PATINDEX(''%declare%cursor%fetch'',
  LOWER(OBJECT_DEFINITION(OBJECT_ID(@@SchemaName + ''.'' + @@ObjectName))))')

Ugly, right? When this evaluates to 0, the policy should succeed; when it is <> 0, the policy should fail. (Remember that a policy is supposed to be expressed in terms of the state you want the system to be in, not the state that you don't.)

So to start you would create a condition by opening Object Explorer, expanding Management > Policy Management, right-clicking Conditions, and selecting New Condition... Give it a name, choose the User Defined Function facet, and click on the Advanced Edit button. There you can enter the ExecuteSql() string above and click OK.

Change the Operator to =, enter 0 as the Value, and click OK. Now create a Policy. Right-click Policies, New Policy... Give it a name, choose the condition you just created, and then choose the Evaluation Mode:

Uh oh. Why aren't "On prevent" actions available? On prevent: change of course would allow you to prevent the function from being created. Because properties like ID and definition aren't available through the facet, requiring us to go through ExecuteSql(), we are subject to a restriction that prevents policies with conditions using ExecuteSql() from being automated. While this PBM blog post from 2008 suggests that this restriction has been lifted, I'm still finding it is enforced in SQL Server 2012 with Cumulative Update #1 applied (11.0.2316).

So for now, you can implement this using a policy, but you won't be able to prevent such a function from being created - you will only be able to investigate violations after the fact (by choosing either On Demand or On Schedule). Keep in mind that even if you run this policy on demand, it uses the exact same logic as the DDL trigger, so is subject to the same caveat: it may introduce false positives if you have comments or valid non-cursor queries that include the same sequence of words.

If you want the properties available to facets to be more flexible and more complete, which would allow for more control in using policies in favor of DDL triggers, please vote for and comment on these two Connect items:

http://connect.microsoft.com/SQLServer/feedback/details/552345/pbm-add-objectid-as-a-parameter-for-executesql

http://connect.microsoft.com/SQLServer/feedback/details/649944/pbm-enable-the-ability-to-pass-more-parameters-to-executesql

I don't know of an item that addresses relaxing the restriction on ExecuteSql() in general. I will look later and file one if I can't find one. I think the blog post reflects the fact that you can now run these on a schedule, but if you can run it on a schedule, why can't it work in "on prevent" mode?

Closing the loop: here is the new Connect item. Please vote and/or add comments describing your use case / business need (this is often much more valuable than just raw votes):

http://connect.microsoft.com/SQLServer/feedback/details/749317/allow-on-change-prevent-for-policies-with-executesql-conditions


You shouldn't put those lines at the very bottom of the file.

Look for the [mysqld] group header in my.cnf and put those lines under it

[mysqld]
query_cache_type = 1
query_cache_size = 4096M
query_cache_limit = 2M
query_cache_strip_comments =1

Those line cannot go under any other group header.

Then, restart mysqld.


Is there any way to find out if any alteration is done to any table?

You can check the modify_date column in either the sys.tables or sys.all_objects (with type = 'U' for user table) system views.  The below query will work, note that this cast defaults to midnight.

select name from sys.all_objects where type = 'U' and
modify_date >= cast('2018-08-22' as datetime)


Postgresql: How to pass an array to trigger function in TG_ARGV?

You would have to use the pure string representation of the array.
CREATE TRIGGER on_create_flag_special
BEFORE INSERT ON example_table
FOR EACH ROW
EXECUTE PROCEDURE example_function(
    '{magicname1,magicname2}',
    '{user1,user2}'
);


How to configure connection accessing Sybase Anywhere from Sybase IQ via ODBC on Linux

Checked official documents for this error and could see it as below:

Source the SAP IQ setup scripts using the same user that starts the
SAP IQ server.
This will set the LD_LIBRARY_PATH and other important environment
variables.

The error could be occurring due to not setting the appropriate environment variables on the host on which SAP IQ is running.
You need to set the environment variables on Linux Box.
You my do below for setting these variables:
Change to the SAP IQ parent installation directory and execute one of the following:
For the tcsh or C (csh) shell, enter:
source ./IQ.csh

For the Korn (ksh), Bash, or Bourne (sh) shells, enter:
source ./IQ.sh

Hope this helps.


Can SQL Server execute two BULK INSERTs in parallel?

After a long and beneficial discussion with Dan Guzman in this MSDN forums thread, the underlying scheduling scheme is finally clear to me. Here is a brief answer borrowing from the thread above:

  So what's happening here is that the 2 SSMS connections both initially
  use the same scheduler because it has the least load. The BULK INSERTs
  then both run on the same scheduler, which is the reason for the high
  SOS_SCHEDULER_YIELD waits.

  The choice of which scheduler is used is not based on query cost. SQL
  Server (actually SOS) basically tries to balance the workload among
  schedulers by examining the load_factor column of sys.dm_os_schedulers.

  When a new task is created for a query execution, SOS prefers to use the same
  scheduler it used for the last request on the session.  However, it
  will use a different scheduler if the load factor of the preferred
  scheduler is greater than a certain percentage of the other
  scheduler's average load factor.

In depth discussion could be found in the link above.

Thanks all guys for your enthusiastic help!


mysql "can't create test file" error on Centos

If your chmod and chown settings are already correct you might need to look into selinux.
This page explains how to configure selinux for mysql:
https://blogs.oracle.com/jsmyth/entry/selinux_and_mysql


INSERT DATA The database disk image is malformed

Im about to try the process as detailed on the link here.
https://www.databasefilerecovery.com/blog/sqlite-error-database-disk-image-is-malformed.html
will let you know how it goes.


Import using pgAdmin3 produces ERROR: duplicate key value violates unique constraint

The column default for daqa_report_id would have to be:

nextval('jira_tickets.daqa_id_seq'::regclass)

Not:

'jira_tickets.daqa_id_seq'::regclass

That would just fetch the OID for the sequence object from pg_class, which is converted to a meaningless, static bigint number.

pgAdmin has nothing to do with this.

But just use the pseudo data type bigserial instead. It does everything automatically. Here's how you would add a bigserial to an existing table:
Most efficient way to add a serial column to a huge table


Auditing of database connections in SQL Server 2008 R2

I faced a similar problem a couple of years ago and created a SQL Agent job to run the query below every few minutes. The query polled
sys.dm_exec_sessions, sys.sysprocesses, sys.dm_exec_connections
it's a bit rough and ready but it met my purposes and might help you too
CREATE TABLE [dbo].[log_db_connections](
[Database] [nvarchar](128) NULL,
[Login] [nvarchar](128) NOT NULL,
[nt_user_name] [nvarchar](128) NULL,
[LoginTime] [datetime] NOT NULL,
[CollectionTime] [datetime] NOT NULL CONSTRAINT [DF_CollectionTime] DEFAULT GETDATE(),
[Program] [nvarchar](128) NULL,
[client_interface_name] [nvarchar](32) NULL,
[Host] [nvarchar](128) NULL,
[client_net_address] [varchar](48) NULL,
[client_tcp_port] [int] NULL,
[net_transport] [nvarchar](40) NOT NULL,
[net_packet_size] [int] NULL,
[protocol_type] [nvarchar](40) NULL,
[endpoint_id] [int] NULL,
[encrypt_option] [nvarchar](40) NOT NULL,
[auth_scheme] [nvarchar](40) NOT NULL,
[num_reads] [int] NULL,
[num_writes] [int] NULL
) ON [PRIMARY]
GO

-- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates
CREATE UNIQUE CLUSTERED INDEX [CIX_log_db_connections] ON log_db_connections
(
    [Database] ASC,
    Login ASC,
    Host ASC,
    Program ASC,
    LoginTime ASC
) WITH (IGNORE_DUP_KEY = ON)

INSERT [dbo].[log_db_connections]
(
[Database],
[Login],
[nt_user_name],
[LoginTime],
[Program],
[client_interface_name],
[Host],
[client_net_address],
[client_tcp_port],
[net_transport],
[net_packet_size],
[protocol_type],
[endpoint_id],
[encrypt_option],
[auth_scheme],
[num_reads],
[num_writes]
)
SELECT
DB_NAME(p.dbid) ,
s.login_name ,
nt_user_name,
s.login_time as LoginTime,
s.[program_name] as Program,
s.client_interface_name,
[host_name] as Host,
c.client_net_address,
c.client_tcp_port,
c.net_transport,
c.net_packet_size,
c.protocol_type,
c.endpoint_id,
c.encrypt_option,
c.auth_scheme,
c.num_reads,
c.num_writes
FROM sys.dm_exec_sessions s
inner join sys.sysprocesses p on
s.session_id = p.spid
inner join sys.dm_exec_connections c on
s.session_id = c.session_id
WHERE s.[program_name] not like 'Microsoft SQL Server Management Studio%'


Peter Saddow has written about this issue, and it's more likely a problem with the setup program rather than your OS (though it can't hurt to make sure you've installed all Windows updates too):

Invoke or BeginInvoke cannot be called on a control until the window handle has been created.

So instead of installing RTM from RTM media, try slipstreaming with the final service pack for 2008 R2 (Service Pack 3).

I came across this issue on Windows 7, even when slipstreaming, but in my case only a reboot was required to get past the issue (simply closing setup and re-launching was not enough).

Peter has also written about slipstreaming:

How to slipstream SQL Server 2008 R2 and a SQL Server 2008 R2 Service Pack 1 (SP1)

That post was about SP1, but you can follow the same basic steps to slipstream a newer service pack.


ERROR 17300 is caused by SQL Server not being able to execute one of the system tasks.
It points to insufficient memory: http://technet.microsoft.com/en-us/library/cc280564.aspx

As that resource explains, it could also be caused by a setting, but insufficient memory is more likely the cause.

ERROR 17312 is directly related.

This could be caused by another process eating up all the available memory. For example Windows explorer file copy operations are known to reserve memory and not release it.

Make sure you have adequate memory installed on the system and monitor how much of it is reserved.


I believe REPEAT will give you what you are looking for.

First example from the page referenced above:

Example 1: Repeat 'abc' two times to create 'abcabc'.

SELECT REPEAT('abc', 2)
FROM SYSIBM.SYSDUMMY1;


For Oracle 11.2+ you can do this with LISTAGG. Here is a demonstration.

Setup:

DROP TABLE t1;
CREATE TABLE t1 (Course_Id Number(1), Group_Id Varchar2(20));

INSERT INTO t1 VALUES (1,'SE123');
INSERT INTO t1 VALUES (1,'BA134');
INSERT INTO t1 VALUES (2,'SE213');
INSERT INTO t1 VALUES (3,'SE223');
INSERT INTO t1 VALUES (3,'BA344');
INSERT INTO t1 VALUES (4,'SE222');

Query:

SELECT Course_ID, LISTAGG(Group_ID, '/') WITHIN GROUP (ORDER BY Group_ID DESC)
FROM t1 GROUP BY Course_ID;

Output:

COURSE_ID GROUPS
--------- --------------------
        1 SE123/BA134
        2 SE213
        3 SE223/BA344
        4 SE222

Here are some other string aggregation techniques for Oracle if you aren't on 11.2 yet.


how to get multiple rows from multiple tables concatenated into a single string in a stored procedure in Oracle

So you want to concatenate multiple rows and columns into a single string, just to parse it later. Overhead 1.

Next, you want to write PL/SQL for something that can be easily done in plain SQL. Overhead 2.

Something like this (I just used random views for the 7 data source):

select listagg(x, '|') within group (order by null) from
(
  select 'COL11' || '-' || 'COL12' as x from v$database where rownum = 1 union all
  select 'COL21' || '-' || 'COL22'      from v$instance where rownum = 1 union all
  select 'COL31' || '-' || 'COL32'      from v$logfile  where rownum = 1 union all
  select 'COL41' || '-' || 'COL42'      from v$log      where rownum = 1 union all
  select 'COL51' || '-' || 'COL52'      from v$datafile where rownum = 1 union all
  select 'COL61' || '-' || 'COL62'      from v$tempfile where rownum = 1 union all
  select 'COL71' || '-' || 'COL72'      from v$version  where rownum = 1
);

You want to minimize round trips. You do not need all this hassle for that, you can fetch multiple rows at a time.


Learning Oracle, does it matter if I install 10g or 11g?

Are you really trying to use Windows 8?  No version of Oracle is supported on Windows 8 yet and, if history is any guide, I wouldn't expect any of them to work without patches that aren't available yet.  Is Windows 8 even in public beta yet?  I'd hate to try to take a class using an early beta operating system.

Oracle 11.2 is supported on Windows 7 (which seems like a more realistic Windows version).  If you have Windows 7, that's realistically the version you need to install.

What, exactly, does the class cover?  If you are learning how to do data modeling and to write basic SQL and PL/SQL, you should be fine using 11g since everything will be backwards compatible.  If, on the other hand, your instructor is teaching about database administration or is making heavy use of the Oracle Enterprise Manager (OEM) GUI, there are going to be a lot of visual differences that you'd have to compensate for across versions.  You can almost certainly get the same information from the 11g OEM GUI, it just may be in a slightly different place.


Is there anyway to UPDATE a series of columns on a row with a composite type record/row?

There are actually lots of posts regarding this..

Support UPDATE table SET(*)=...
WIP patch for multiple column assignment in UPDATE
Supporting multiple column assignment in UPDATE (9.5 project)
Better handling of UPDATE multiple-assignment row expressions

However, according to /u/pstef, one of these is committed, and this syntax now works on HEAD, though @ypercubeᵀᴹ reports it does not work on 9.6.5

UPDATE foo SET (a,b,c,d) = ROW(10,9,8,7);

The docs don't say much about this

  According to the standard, the source value for a parenthesized sub-list of target column names can be any row-valued expression yielding the correct number of columns. **PostgreSQL only allows the source value to be a row constructor or a sub-SELECT. **An individual column's updated value can be specified as DEFAULT in the row-constructor case, but not inside a sub-SELECT.

But you can also see at the syntax for UPDATE

   ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |


Running a query on the results of another query?

When you put a subquery between IN(…), you have to ensure it returns exactly one column. Going by your other query, it seems cases.referred_link is actually already a names_id, so you are already pretty much there. All you need to do is remove that extra count(*) from the list of selected columns.

… and names.names_id IN (SELECT cases.referred_link
                FROM CASES
                GROUP BY referred_link
                HAVING COUNT(*) >= 3)


Check existence on other tables: performance advice

I'd probably write something like the following:

SELECT TOP 1 -- Top command optional and may not actually help speed things up
  p.ProfileId -- No need to alias to the same thing
  , p.Username
  , p.UsernameCleaned
  , IsUser = CAST(CASE WHEN u.UserId IS NOT NULL THEN 1 ELSE 0 END AS BIT)
  , IsPage = CAST(CASE WHEN pg.PageId IS NOT NULL THEN 1 ELSE 0 END AS BIT)
FROM dbo.Profiles p
  LEFT JOIN dbo.Users u ON u.UserId = p.ProfileId -- Left join here instead of exists above
  LEFT JOIN dbo.Pages pg ON pg.PageId = p.ProfileId
WHERE
  p.UsernameCleaned = @Token;

If you notice I basically just traded EXISTS for LEFT JOIN. This should give you reasonable performance and be fairly clear for people who review your code. Of course the standard disclaimer applies for any performance question.

  You should profile this on a representative workload to determine what is fastest, and indexes are your friend.

In general though you want to avoid subqueries in the SELECT portion of your query because they will probably be run once for each row in the result set. Here that isn't a big deal but if you have more results then the difference can be dramatic.

Try at all costs to avoid the NOLOCK query hint. It looks nice on paper and can give you an apparent boost, but nothing is free. For more detailed information on why this probably isn't a good idea I'd advise you read this blog post by Aaron Bertrand.

Also of note rewriting your query made it clear that a profile can be both a user and a page. I don't know your use cases but that feels wrong to me especially since this query seems to be designed to tell the difference. If that is true then you might need to rework your data model.

Finally as a personal preference I like to have my aliases on the left if the column is computed/derived like IsUser above. My reasoning goes the alias can get lost in the confusion of the calculations so it is best to put it first. Of course you should go with what you and your coworkers find is most legible.


When importing raw files for internal conversion, should I use a secondary database or just isolate them within the database?

Well, since you have the CSV files, they can serve as a backup to the staging tables, so I see no benefit to keeping them in the primary database (or maintaining the staging tables after the import is done). If those staging tables can be discarded / truncated once the import is finished, then they really should be separate and have as minimal impact on the primary database as possible.

Also, I see no value in putting the staging tables in the primary database in any case, no matter how permanent the loaded data will be. And if you need the data to be permanent, you can always reference the data even if it is in a different database - in fact that is arguably better, because now:

you can handle that data alone independently:

set the recovery model of that database to simple
mark it as read only
heck back it up and drop it, knowing that you can always recover it later
restrict access to it in simpler and higher level ways than you could tables in the primary database (to prevent the "until someone truncates it" fear)

or just abandon it because, as I said above, your CSV files are already acting as a secondary backup.

In addition to the cons you've already identified, well, they're essentially just scratch tables. Even if you're only on a single spindle, why bother disrupting the primary database? There's no penalty in crossing database boundaries on the same instance, e.g. INSERT ... FROM db.dbo.table; compared to INSERT ... FROM dbo.table;.

Finally, when you say "autogrowth" I cringe. If you know your database is going to get bigger than it is now, why not manually expand it to the size you need it to be? Autogrow is a performance nightmare when it happens during peak activity - and you will certainly notice it happening even on a single machine and even as the only one performing any work - basically all of your data transfer will halt when either the data or log file needs to grow. This is particularly true if you've left the autogrow settings at the current, ridiculous, defaults, and/or if you don't have instant file initialization. Don't forget to set your log growth settings appropriately too, as log files are not able to take advantage of IFI.


Removing locks in Oracle without being admin

If this happens often there is an option to write a package or a stored procedure for that. The owner of the package would need the alter system privilege and the package should be created with definers rights.
You just need execute privileges on that package and feed the session kill procedure with the right parameters.
There is still a good chance that zombie processes keep running until the end user that was running the killed processes tries to do anything. These zombies kan be killed from the OS. To find those processed, before killing read their os pids by joining v$session with v$process. An other option is to regularly clean those orphaned processes by finding processes in v$process that have no session in v$session.


Which performance counters can you look at on a SQL Server Instance to determine it's performance and over all health?

Here's my Perfmon tutorial for SQL Server: http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

For more counters and thresholds, here's a poster we did when I was at Quest: http://www.quest.com/documents/landing.aspx?id=11635


Accessing the Oracle cloud with MySQL workbench

The correct tool to use for this is Oracle SQL Developer.

You can download it here.

A guide to setting up a connection to a cloud DB is here.


Just do a dist-upgrade or the like on Linux, or install the newer versions on Windows/OSX. Minor versions ensure total compatibility with the data directory (heap, and index).

Stands to reason though, you'll have to stop the database during the upgrade.

From the official docs on versioning

  Minor releases are numbered by increasing the last part of the version number. Beginning with version 10, this is the second part of the version number, e.g. 10.0 to 10.1; for older versions this is the third part of the version number, e.g. 9.5.3 to 9.5.4. The PostgreSQL team only adds bug fixes to minor releases. All users should upgrade to the most recent minor release as soon as possible. While upgrades always have some risk, PostgreSQL minor releases fix only frequently-encountered, security, and data corruption bugs to reduce the risk of upgrading. The community considers not upgrading to be riskier than upgrading.

So essentially, PostgreSQL is so conservative that minor-version number upgrades are only issued when there is more risk to not-upgrading than upgrading. I've been working with PostgreSQL for about 15 years. I don't do any additional backups nor take any special precautions when doing minor upgrades. Not saying you can't -- even the docs say they come with "some risk."


Neither DBCC FREEPROCCACHE nor DBCC FREESYSTEMCACHE('SQL Plans') do anything to free CACHESTORE_SQLCP memory

I upgraded to 13.00.4422.0.v1 (SQL Server 2016 SP1) and restarted.

So far, I am able to clear SQL Plan memory using DBCC FREESYSTEMCACHE('SQL Plans'); command!

Time will tell to see if it uses the memory a little wiser now, but at least now I have a way to reset it if its gets too bloated again.


You can improve the WAN link or improve the query to make it more efficient.
Other than that you are limited by the laws of physics as the data has to travel back and forth through the link there is no getting around that.


Is it possible to define a function within a stored procedure?

No this isn't possible.

A permanent TVF or view isn't an option due to the reference to #MyTempTable

I've seen a connect item request for temporary views and agree sometimes they would be useful. This was closed as duplicate of one requesting Module-level table expressions.

You might be able to rewrite as

SELECT A,
       B,
       C,
       CASE @calcType
         WHEN 1
           THEN sum(Amount)
         WHEN 2
           THEN avg(Amount)
       END
FROM   #MyTempTable t
       JOIN AnotherTable a
         ON t.Field1 = a.Field1
GROUP  BY A,
          B,
          C

Or if your needs were more complex (e.g. same shape resultset and using same source but different grouping conditions)

WITH Base
     AS (SELECT A,
                B,
                C,
                Factor,
                Type
         FROM   #MyTempTable t
                JOIN AnotherTable a
                  ON t.Field1 = a.Field1)
SELECT A,
       B,
       C,
       sum(Amount)
FROM   Base
WHERE  @calcType = 1
GROUP  BY A,
          B,
          C
UNION ALL
SELECT A,
       B,
       C,
       CalcField = 0.0 * t.Factor
FROM   Base
WHERE  @calcType NOT IN ( 1, 2, 3 )

With the last one in particular you might consider OPTION (RECOMPILE) to simplify the plan. (Likely it would have a filter with a start up predicate without the hint and not actually execute the redundant branches but you would need to check. Also row estimates could be wrong with this approach if the startup predicate is retained).

If neither of those suited you would need to get into the realms of dynamic SQL.


The transaction log for database is full due to 'OLDEST_PAGE'

This is not something you should ever see on an Azure SQL Database.  You should engage support on this.

There are a couple things you could do yourself, if you want.  First just try to CHECKPOINT the database.  Second if you are sure you have no active transactions, change the database SLO.  Taking it out of the Elastic Pool, then putting it back in.


What is a “transition table" in Postgres?

You know how there are OLD and NEW record-variables for FOR EACH ROW triggers?

Transition tables are the FOR EACH STATEMENT equivalents. They're tables with the old and new tuples, so your triggers can see what changed.


Questions concerning performance/production resources where exporting / importing big data sets

I hope i have understood everything good.

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

The best solution depends from possibilites.

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

Here is my script, may help.

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

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

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

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

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

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

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

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

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

PRINT 'Start TABLE_A';

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

PRINT @OPENQUERY + @TSQL;

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

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

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

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

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

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

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


Using a subquery in place of a SELECT fieldname ...or how to achieve the same result?

What you want to do is called a Cartesian product and uses CROSS JOIN:

SELECT NULL, t1.UID, t2.UID
FROM table1 t1
CROSS JOIN table2 t2
WHERE T1.`Groups` LIKE 'V'
    AND t2. `IsDefault` = 1;

This mixes each rows from table1 with each rows from table2.  (see sqlfiddle)


Using INSERT INTO...EXEC AT linked server into temp table fails with Msg 7391

In my testing (to another instance on the same server, not on a separate server), this worked if I had the Linked Server option of Enable Promotion of Distributed Transactions for RPC set to "False". You can accomplish that via the following command:

EXEC master.dbo.sp_serveroption
       @server = N'{linked_server_name}',
       @optname = N'remote proc transaction promotion',
       @optvalue = N'false';

This worked with the Distributed Transaction Coordinator (MSDTC) both ON (Running) and OFF (Stopped).

If you normally need the "remote proc transaction promotion" option set to "True", and if setting it to "False" allows this INSERT...EXEC to work, then you can set up another Linked Server with all of the same properties except this one option being different.

The main drawback to disabling "remote proc trans promotion" is that well, it's not a transaction at the remote server. So, if an error occurs there, you won't get the data inserted locally (obviously), but if there were any DML statements run remotely, those could still commit (depending on if it was a single statement or multiple statements). Still, you could / should still use proper transaction handling on the remote query (i.e. use the TRY...CATCH construct):

CREATE TABLE #Local ([name] [sysname]);

INSERT INTO #Local ([name])
    EXEC (N'
BEGIN TRY
    BEGIN TRAN;
    SELECT [name] FROM sys.servers WHERE 1/0 = ''a'';
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK TRAN;
    THROW;
END CATCH;
') AT [{linked_server_name}];

P.S. The RPC Out option needs to be enabled / True. This was not mentioned above since according to a comment on the question, this option is already set correctly.


How to auto-refresh/re-run a query every few seconds?

You don't state what OS you are using, but under Linux/OSX and other Unix-a-likes you can use the mysql command line tools with the pretty standard watch utility to do this. Something like:

watch -n 10 'mysql --database=dbname --user=username --password=$(cat /path/to/file/with/mysql/password) --execute="select *, (select count(*) from domains) as count, (select 456976-count(*) from domains) as remaining from domains order by domain desc limit 0, 1;"'

Breaking that down:

watch -n 10 '<stuff>' runs <stuff> every ten seconds and echos anything that goes to stdout to your console window, the single quotes wrapped around the command(s) to run tell your shell not to run any wildcard globbing or other substitutions before giving the command to watch (we want this to happen when watch runs the command)
mysql --database=dbname --user=username --password=password runs the command line tool against a given DB with the authentication details given
Using command substitution ($(cat /path/to/file/with/mysql/password)) to read the password instead of giving it directly on the command line stops your password being presented to anyone looking at the output of top or ps (this is why we use the single quotes: without them this substitution will be done before watch is in control so the real password will end up on the process list as watch runs the resulting command)
--execute=<stuff> tells the command line tool to run <stuff> then exit immediately (otherwise it logs in and waits for you to enter commands/queries/statements).
The rest is just your SELECT statement. Each time watch runs the mysql command it will output the results of this and watch will draw them on your screen.

More detail:

mysql command line syntax
watch command man page
watch command examples with animated example output
some more examples of simple mysql command-line use (the first of many in a simple search, so you will find a lot more examples if you need/want them)


Longer time to bring SQL online after node failover

Most likely your problem is caused by the databases going through recovery to redo or undo transactions that haven't been hardened to data files.
Avoid recovery all together
Before doing planned server reboots or failovers of an FCI, particularly one with a lot of memory, I like to run a CHECKPOINT on every database. This minimizes the time spend on a clean shutdown of all databases, and (when databases aren't shut down cleanly) minimizes crash recovery time on restart.
I use sp_ineachdb from the First Responder Kit to do this:
EXEC DBA.dbo.sp_ineachdb 'CHECKPOINT;`;

If you hate free code that makes your life easier, you could do something with dynamic SQL:
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'CHECKPOINT ' + QUOTENAME(name) + '; '
FROM sys.databases;

EXEC sys.sp_executesql @stmt = @sql;

But make recovery faster by doing less work
And of course, as Erik, Darling mentioned in the comments, make sure your VLFs are in order and properly sized. Scanning those VLFs during crash recovery is what can cause you all the pain you're seeing. For planned maintenance you can CHECKPOINT to minimize or eliminate crash recovery. But if you have... uhhh.... a crash and failover unexpectedly, that crash recovery is still going to happen, and there's not much you can do about it.
Be less direct
I've also had tons of luck with indirect checkpoints. We've rolled this out across our entire environment to much success.
And lobby the powers that be for an upgrade
SQL Server 2019 includes a feature called Accelerated Database Recovery, which can speed up recovery process, particularly when there are long-running, large transactions. ADR is not just for recovery after a crash, but also helps in other scenarios where the transaction log needs to be recovered–including Availability Group secondary redo and Failover Cluster Instance failovers.


Sean Gallardy provided the information that led to the answer when he commented about the Backup forcing a Checkpoint:

Paul Randall Blog on Checkpionts


Yes, SELECT..INTO creates a new table

You'd need this to add rows to an existing table

INSERT MessageQueue (MessageQueue, OneId, OtherId)
SELECT MessageId = 9999, OneId, OtherId
   from @TempTable


How to modify MySQL 5.5 settings that were set during the installation?

Use InnoDB tables and configure it properly.  This site is a great resource:

http://www.mysqlperformanceblog.com/


Since you're using SQL Server 2012 Standard, you're not able to take advantage of DATA COMPRESSION.  It's an Enterprise feature up until SQL Server 2016 SP1.  When you're able to upgrade, you should check out that functionality.  'In addition to saving space, data compression can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk.'

However, there are a few things you can do now to possibly reduce the size of your databases.  Look for the tables that are taking up the most space and...

Archive (and delete) any data that doesn't have to reside in the database.  Afterwards, rebuild indexes on any affected tables so you can consolidate the data into fewer pages.
If you haven't done so already, consider increasing the fill factor on your indexes to 100%.  Having your pages as full as possible can reduce the overall space needed requiring fewer pages in memory.  Afterwards, rebuild indexes on any affected tables so you can consolidate the data into fewer pages.  Check out Does index fill factor affect fragmentation?
Try to find unused or redundant indexes.  If an index is not being used, it's just taking up space in the database for no reason.  Likewise, you may be able to consolidate several redundant indexes into fewer ones.  There are numerous free tools and queries available by searching for 'sql server unused indexes'.  A personal favorite of mine is sp_BlitzIndex.


Archiving of old data combined with replication to data warehouse

Consider the following...

The Master is collecting "raw" data in a "Fact" table.  (I am using Data Wherehouse terminology; I hope the analogy works.)
The Fact table is not replicated.  But it is PARTITIONed by date for efficient archiving.  (We can discuss that separately.)  (If you need 'some' of the Fact table replicated, we can discuss that.)
On the Master, a few "Summary Tables" ("rollups") are built and maintained.
The Summary tables are replicated.  Querying against these will be much faster than against the Fact table.

DW discussion:  http://mysql.rjweb.org/doc.php/datawarehouse
Summary Tables:  http://mysql.rjweb.org/doc.php/summarytables
Time Series pruning:  http://mysql.rjweb.org/doc.php/partitionmaint

If you want to dig deeper into these ideas, please provide more details of the application and its data.


Error while Uninstalling SQL Server 2016 instance

Check for the space issues and also the permission on the Temp folder. If the permission is read-only then change it to Full Control.

https://social.technet.microsoft.com/Forums/en-US/bb81cf3f-de94-409e-b1aa-63803632afe4/the-temp-folder-is-on-a-drive-that-is-full-or-is-inaccessible-when-search-service-started?forum=sharepointsearchlegacy


Fastest method for inserting large number of records from query between tables

After trying numerous solutions, this is the best I could come up with

DECLARE @BatchSize INT = 50000
DECLARE @page INT = 0

SELECT
 IDENTITY(int,1,1) as id
 v.Param1,
 v.Param2,
 lt.Param3,
 lt.Param4
INTO #tempTable
FROM <View> v
  join LargeTable lt
   on lt.nVarCharCol = v.nVarCharCol

WHILE(1=1)
BEGIN
 BEGIN TRANSACTION
  INSERT INTO DestTable SELECT Para1, Param2, Param3, Param4 FROM #tempTable
  WHERE id > @BatchSize * @Page AND id <= @BatchSize * (@Page + 1)
 IF @@ROWCOUNT = 0
  BREAK;
 SET @Page = @Page + 1
 COMMIT TRANSACTION
END

Also be sure to keep in mind that regardless of how fast your query or T-SQL statement is Azure SQL Databases have an artificial I/O ceiling called DTUs. I found that there was a substantial difference in performance between the different performance levels with the exact same T-SQL statement.

More information about DTUs can be found here:
SQL Database options and performance


As I understand you want to check each combination of a/b and i_id/o_id

then simple rewrite to join is like this:

SELECT a, b
  FROM links
  JOIN t_i ON (a = i_id OR b = i_id)
  WHERE id = x
UNION
SELECT a, b
  FROM links
  JOIN t_o ON (a = o_id OR b = o_id)
  WHERE id = x

But for good index usage I suggest splitting it once more. It is even longer query but can be really fast even on bigger data with indexes on t_i(id,i_id), t_o(id,o_id) links(a) and links(b)

SELECT a, b
  FROM links
  JOIN t_i ON (a = i_id)
  WHERE id = x
UNION
SELECT a, b
  FROM links
  JOIN t_i ON (b = i_id)
  WHERE id = x
UNION
SELECT a, b
  FROM links
  JOIN t_o ON (a = o_id)
  WHERE id = x
UNION
SELECT a, b
  FROM links
  JOIN t_o ON (b = o_id)
  WHERE id = x


Split Date/Time value for Night Shift Employee


mysqldump with LIMIT and --order-by-primary

My guess is that you cant use limit in combination with --order-by-primary

One way around this is to define the order by within the -where argument. If you know the primary key for the table:

mysqldump -u lelle -p -w"1 = 1 order by <primary key> LIMIT 40" <database> <table>

Note that <primary key> is a place holder for the columns in the primary key.

if you don't know the primary key in advance you can use the INFORMATION_SCHEMA to determine that:

select GROUP_CONCAT(column_name SEPARATOR ',')
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where CONSTRAINT_NAME = 'PRIMARY'
  and CONSTRAINT_SCHEMA = ... and table_name = ...;

and then use the information in mysqldump.


Internal Oracle users (SYS, SYSTEM etc) should never be modified in any way, except for password changes.

As far as roles & grants are concerned, SYS already has unrestricted access to the entire RDBMS due to the nature of the user.


Which Postgresql Replication Solution to Use? (Asynchronous Multimaster / Bucardo?)

If you're not a DBA - i.e. you're not experienced with the complexities of replication, failover, subtleties of transaction isolation, etc - then if possible do not use multi-master.

There's no multi-master clustering for PostgreSQL, only multi-master replication via 3rd party add-ons. These add-ons have a significant performance impact, but more importantly they don't deal with inter-node locking and transaction isolation. Concurrent transactions on different nodes can see different data, can both modify the same rows, etc.

Frankly, multi-master clustering is pretty underwhelming even in products that do have it available. Performance tends to be pretty poor and scale badly. This isn't a deficiency in the products, it's just that MM clustering is hard and you pretty much get to pick either correct or fast.

Personally, I recommend using two big nodes. Have one node running a streaming replica and the other running as master. If one goes down, fail over to the other. Tune your app and queries properly before adding more capacity.


You can do something like this:
SELECT T.card_number
FROM Cards AS C
INNER JOIN Transactions AS T
    ON C.card_id = T.card_number
GROUP BY T.card_number -- Grouping up on Card_Number to get the count of unique ATMs each card was used at in the HAVING clause
HAVING COUNT(DISTINCT ATM_id) = (SELECT COUNT(1) FROM ATM) -- Count of distinct ATMs each card was used at needs to equal the total number of ATMs


MySQL: Unique constraint on large column

You do not want a gigantic gen_clust_index (Internal Clustered Index). That size is ungodly huge even for a secondary index.

You may have to resort to triggers or stored procedures to check for the key well in advance.

You could also think about performing an SHA1 function call using the VARCHAR(3071) field. SHA1 will return a 40-character field. This hash may be just what you need to index.

Suppose you have this

CREATE TABLE mytable
(
    id int not null auto_increment,
    txt VARCHAR(3071),
    primary key (id)
) ENGINE=InnODB;

and you want to make a UNIQUE index on txt. Try the SHA1 approach

CREATE TABLE mytablenew LIKE mytable;
ALTER TABLE mytable ADD txtsha1 CHAR(40);
ALTER TABLE mytable ADD UNIQUE KEY (txtsha1);
INSERT INTO mytablenew (id,txt,txtsha1)
SELECT id,txt,SHA1(txt) FROM mytable;

Then, count them

SELECT COUNT(1) FROM mytable;
SELECT COUNT(1) FROM mytablenew;

If the Counts are the Same, CONGRATULATIONS !!! Now you have a unique index of length 40. You can finish up with:

ALTER TABLE mytable RENAME mytableold;
ALTER TABLE mytablenew RENAME mytable;
DROP TABLE mytableold;

This could be more atomically as pointed out in the comments below:

RENAME TABLE mytable TO mytableold, mytablenew TO mytable;
DROP TABLE mytableold;

Perform this on whatever table you intend to have this big column. You have to remember to add the SHA1 of the data along with the data upon INSERT.

The odds of duplicate keys is 1 in 2 to the 160th power (that 1.4615016373309029182036848327163e+48. If I get the exact figure, I'll post it someday).

Give it a Try !!!


Compare two users permissions on SQL Server database?

Here are two queries I have used to compare permissions between database users.
The first shows the database roles (if any) to which the database user has membership.  The second shows individual GRANT and DENY permissions.

--Database user and role memberships (if any).
SELECT u.name, r.name
FROM sys.database_principals u
LEFT JOIN sys.database_role_members rm
    ON rm.member_principal_id = u.principal_id
LEFT JOIN sys.database_principals r
    ON r.principal_id = rm.role_principal_id
WHERE u.type != 'R'
AND u.[name] = 'DBUserName';
GO

--Individual GRANTs and DENYs.
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission],
    sec.class_desc Class, object_name(sec.major_id) [Securable],
    sec.major_id [Securible_Id]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
    ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE prin.[name] = 'DBUserName'
ORDER BY [User], [Permission];
GO


cannot execute as the database principal because the principal does not exist - DB Restoration

When you restore a database it contains database users. These users are transported with the database, whenever you perform a backup and a restore to a different SQL Server.

On your source and target SQL Servers you will have SQL Server Logins. These logins are either SQL Server accounts (Native SQL Server logins) or they will be Windows Server/Domain accounts (Windows Accounts).

SQL Server Logins

SQL Server Logins (Native SQL Logins or Windows Accounts) are stored in the master database and have a unique SID assigned to each SQL Login. You can retrieve a list of SQL Server logins when you query the system catalog view sys.server_principals.

select * from sys.server_principals

You will receive a list of SQL Server logins for your SQL Server instance.

Note: These are the SQL Server Logins and not the database users.

Database Users

Database Users can be queried by querying the system catalog view sys.database_principles of each user database by issuing the following query:

USE [<your_db>]
GO
SELECT * FROM sys.database_principals

You will receive a list of database users that have database privileges assigned to them.

Note: These are the Database Users and not SQL Server Logins.

Normal Behaviour

When you create a database and assign a "user" to the database with certain privileges you are in fact doing the following (partly in the background):

Creating SQL Server Login with password (sys.server_principal)
Creating a database user in the database (sys.database_principal)
Granting permissions to the database user (in various tables, depending on the privileges)

In this case the SID of the SQL Login (sys.server_principal) will match the SID of the database user (sys.database_principal).

Restore Behaviour

When you restore a database from a source SQL Server the database users (Native SQL Server Logins and local Windows Accounts only) in the source database will have different SIDs than the SQL Server Logins on the target server. This is because the SID is unique for the source and target Native SQL Server Login or Local Windows Account.

The SQL Server Logins that are based on Windows Domain Accounts will always have the same SID, because SQL Server will retrieve these values from Active Directory.

When you restore the database from the source to the target SQL Server the SIDs of the Native SQL Server Logins will be mismatched, even though a user might be listed in the sys.server_principals system management catalog of the SQL Server instance and in the sys.database_principals system management catalog of the restored database.

Solution

To rectify this and allow you to navigate the "SQL Server Login | permissions" and/or the "Database Properties | Permissions" you can relink these orphaned database users to the SQL Server Login.

Switch to your user database and query the orphaned database users:

USE [your_db]
GO
sp_change_users_login 'Report'

If a user is reported as orphaned you can relink the SQL Server Login with the Database User by issuing the following command:

USE [your_db]
GO
sp_change_users_lgoin 'Update_one', '<database_user>', '<sql_server_login>'

This will relink the (Native) SQL Server Login (sys.server_principal) on your target instance, with the Database User (sys.database_principal) of the restored database.

Alternative

Seeing as sp_change_users_login is deprecated, you could achieve the same with the ALTER USER statement:

USE [<your_db>]
GO
ALTER USER <database_user>
    WITH LOGIN = <sql_server_login>

In some cases

...when you receive a database backup from a client, you might not have a corresponding SQL Server Login to link to the Database User. In that case you can create a SQL Server Login without assigning permissions to the restored database and then link the newly create SQL Server Login with the Database User with the above mentioned statements.


Are foreign keys important while restoring database

If by "index" you truly mean index, then probably yes because the indexes are likely used to speed up read queries as well as lookups for when enforcing the FK constraint.

Of course this may not be true for all the indexes you currently have on FK columns, but that will depend very much upon your application. I would keep them all unless you have tested to make sure some are not needed (in which case those indexes could be dropped to save space).

As for the FK properties themselves, I would leave them in place. Rather than just limiting what gets inserted/updated to maintain referential integrity FKs also act as internal documentation for your DB.


Goldengate warning on table with primary key

This happens when the PK exists, but it is not enabled or not validated. Make sure the PK is enabled and validated, then try adding trandata again.


How to retrieve data from a table with two of the columns in the table with the same value?

You can use SELF JOIN to get result

select t1.*
from my_table as t1
join my_table as t2 on t1.userid = t2.followinguserid


How to Get the SUM Value from Grouped by Last value in a table result

Based on your images, something like this should run on SQL Server 2008.

(It's not completely tested because I didn't want to have to type all that data from your screenprints.)

;WITH _cte
AS (
    SELECT PurchaseId
        ,PurchaseDate
        ,SupplierName
        ,Amount
        ,Outstanding
    FROM BASE_Purchase
    )
SELECT PurchaseId
    ,PurchaseDate
    ,SupplierName
    ,Outstanding
FROM _cte c
WHERE PurchaseDate = (
        SELECT max(PurchaseDate)
        FROM _cte
        WHERE SupplierName = c.SupplierName
        )


The answer came in with adding a documents into the seetings collection inside the config database.

syntax:

use config

show collections

db.settings.insert({ "_id" : "chunksize", "value" : NumberLong(64) })

db.setting.find()           ---#to check

This worked for me.


Transfer data from the cloud to a local database

This is returns column and table names you can use to build up the statements

select column_name, table_name, ORDINAL_POSITION
from information_schema.columns
order by table_name, ordinal_position

I would do it in this order: delete, update, insert
Merge is known to have some issues


SQL Query return a column per hour / day / week / month betwen 2 dates


Selecting distinct records having multiple properties

The easiest way would be group by and having

select
ID from table_name
where Property in('Sweet','Sour')
group by ID
having count(*)=2


How can I SELECT an 'entry' that spans adjacent months?

I keep the following bit of ASCII art around that I drew a few years back as reference for dealing with matching ranges, reformatted, as it was originally in POD; in your case, you're using the second one, which only matches example 3:

Interval Matching

 .........A============B......... Search Range
 ...x--y..|............|......... (never match)
 .........|............|...x--y.. (never match)
 ......x--|--y.........|......... (1)
 ......x--|------------|---y..... (2)
 .........|...x-----y..|......... (3)
 .........|..........x-|----y.... (4)

Intersection ... matches examples 1,2,3,4

A <= y && B >= x
(item fully contained by search range) ... matches example 3 only.

A <= x && B >= y
(item fully includes search range) ... matches example 2 only.

A >= x && B <= y


Can't restore MySQL DB because of a function

Such DB migration is not obvious and you are not the first one to have trouble with functions and definers after importing DB as things have to be created in the right order.

I'm not sure to understand how you can grant right on a function to an user when this function failed to import so function wasn't created yet.

But instead of restarting restoration from the beginning, split your export in several files. fix the problem (create user analytic, create functions manually, if there is failure, don't use definer, you will fix the issue later after import completed) then continue execute export file from when it failed.


Can I see the RAID group name for my SQL Server Database files?

SQL Server doesn't have any access to anything that low level.  Typically Windows can't even see the RAID group information given that each hardware vendor stores that differently and typically exposing that information to the OS doesn't have any benefit.  You'll need to work with your hardware team to make the drive labels match, but even then that isn't exposed within SQL Server.  You would need to use PowerShell or WMI to pull that information from the OS.


Finding login or user name of last person to use a DB

If your server has been up for a reliable amount of time (e.g. at least one business cycle), you can tell when there has been any read or write against any table in a database using DMVs like sys.dm_db_index_usage_stats.

There isn't really a reliable way to tell who last "used" a database. SQL Server doesn't log this information unless they do something substantial (e.g. you might see that a user created or dropped an object in the default trace, but this will by no means tell you they were the last person to access the database, or if that was the last time they accessed the database).

For the database file sizes, you don't really need to use sp_MSforeachdb at all; and you shouldn't use that method anyway. Here's why:

https://sqlblog.org/2010/12/29/a-more-reliable-and-more-flexible-sp_msforeachdb
https://sqlblog.org/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior
http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

In this case, you don't need to loop through all of the databases anyway; this information is replicated in the view master.sys.master_files.

SELECT
  d.name,
  [Size (MB)] = SUM(mf.size)*8/1024
FROM master.sys.databases AS d
INNER JOIN master.sys.master_files AS mf
ON d.database_id = mf.database_id
GROUP BY d.name;

If you have to do something through a bunch of databases that isn't supported by something in master, I'd rather do this type of technique than use sp_MSforeachdb:

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'SELECT ''' + name + ''', (SUM(df.size)*8)/1024 as [Size (MB)]
  from ' + QUOTENAME(name) + '.sys.database_files as df;';
EXEC sp_executesql @sql;

(As an aside, don't use 'single quotes' to delimit an alias, use [square brackets]. The former is deprecated in some forms - see here and here - and makes your alias look like a string literal anyway.)

For the "when" part, copying from my answer here:

SQL Server doesn't really track database access in the way you want, at least going backward (you can set up things like server-side trace, extended events, auditing, etc. going forward).

There is one ballpark thing you can use: DMVs that track index usage and procedure/trigger/query stats. For example:

;WITH d AS
(
  SELECT d = database_id, name FROM sys.databases
  WHERE state = 0 AND database_id BETWEEN 5 AND 32766
),
index_usage(d,lsk,lsc,llk,lupd) AS
(
  SELECT database_id, MAX(last_user_seek), MAX(last_user_scan),
    MAX(last_user_lookup), MAX(last_user_update)
  FROM sys.dm_db_index_usage_stats
  WHERE database_id BETWEEN 5 AND 32766
  GROUP BY database_id
),
proc_stats(d,lproc) AS
(
  SELECT database_id, MAX(last_execution_time)
    FROM sys.dm_exec_procedure_stats
    WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
),
trig_stats(d,ltrig) AS
(
  SELECT database_id, MAX(last_execution_time)
    FROM sys.dm_exec_trigger_stats
    WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
),
query_stats(d,lquery) AS
(
  SELECT t.[dbid], MAX(s.last_execution_time)
    FROM sys.dm_exec_query_stats AS s
    CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
    WHERE t.[dbid] BETWEEN 5 AND 32766
    GROUP BY t.[dbid]
)
SELECT d.name,i.lsk,i.lsc,i.llk,i.lupd,p.lproc,t.ltrig,q.lquery
FROM d LEFT OUTER JOIN index_usage AS i ON d.d = i.d
LEFT OUTER JOIN proc_stats  AS p ON d.d = p.d
LEFT OUTER JOIN trig_stats  AS t ON d.d = t.d
LEFT OUTER JOIN query_stats AS q ON d.d = q.d;

Note that these statistics aren't completely reliable, since you may not have any stored procedures, and the queries found in sys.dm_exec_query_stats may reference more than one database, and may not ever reflect the one you are concerned about.

Also, they are reset when SQL Server is restarted, or a database is detached / attached or restored, or when a database is auto-closed, and can also depend in some cases on the plans still being in the cache (which another database could completely take over within minutes). So if you are looking into the past, unless you know none of these things have happened for an entire business cycle, I wouldn't rely on just these numbers to determine whether a database is used (there could also be automated processes that are making a database look current, even if you don't care that those automated processes will fail when you remove the database).

Another note is that certain index access may not be tracked in the index usage views; for example, in SQL Server 2014, which adds memory-optimized tables, activity against those hash indexes are not captured this way (and the views where you think the activity would be captured, like sys.dm_db_xtp_hash_index_stats, do not include any date/time columns). If you are using SQL Server 2014 and in-memory OLTP ("Hekaton"), you may want to add some research to cover those objects (in case they are the only ones being referenced in a database).

And one more note is that the queries captured by sys.dm_exec_query_stats could be false positive. For example, if your database has filestream/filetable, you will see these queries being run by the system occasionally:

select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
from [database].[sys].[filetable_updates_<some_id>] with (readpast) order by table_id

So, you may want to add additional filtering to the above query to filter those out (as long as the filter doesn't accidentally filter out queries you do care about). This is probably a safe addition to that derived table:

AND t.[text] NOT LIKE N'%oplsn_fseqno%'

In the end, the safest thing to do in a dev environment is to take the databases you're not sure about offline for a week. If nobody complains, back them up, and drop them. If it takes more than a week for someone to notice they're missing, you can always restore them (there or elsewhere).

I've blogged about this a bit as well:

https://sqlblog.org/2008/05/06/when-was-my-database-table-last-accessed


Max date record for items for given set of dates

I think this is what you want:

SELECT
    i.item,
    q.query_dates,
    s.value,
    s.data_entry_date
FROM
    qdate AS q
  CROSS JOIN
    ( SELECT DISTINCT item
      FROM svalue
    ) AS i
  OUTER APPLY
    ( SELECT TOP (1) s.value, s.data_entry_date
      FROM svalue AS s
      WHERE s.item = i.item
        AND s.data_entry_date <= q.query_dates
      ORDER BY s.data_entry_date DESC
   ) AS s
ORDER BY
    item,
    query_dates ;

Now about efficiency, I doubt there is way to make this blazing fast with a big table. Even with the rather small tables (600 dates, 6K items), you still want a result set of 3.6M rows.

An index on (item, data_entry_date) INCLUDE (value) will probably help a lot but still, it's 3 million rows of results. Are you going to display them? Or save them in another table for further analysis?

If there is another table with items, it would also be probably better to replace the i derived table with that table.


String manipulation tools to populate a variable for two "functions"

The first thing you need to do is chop the filename off of that full path, else you will just get an error. The following does that using a CTE to apply the expression twice so that I didn't have to copy/paste the CONVERT:

;WITH cte AS (SELECT CONVERT(NVARCHAR(259), SERVERPROPERTY('ErrorLogFileName')) AS [Log])
SELECT LEFT(cte.[Log], CHARINDEX(N'\error', cte.[Log] COLLATE Latin1_General_100_CI_AS))
FROM   cte;

In the query above, I used COLLATE Latin1_General_100_CI_AS to force a case-insensitive comparison as I noticed different casing between "regular" SQL Server (i.e. running via a service) and SQL Server Express LocalDB in terms of the output of the SERVERPROPERTY('ErrorLogFileName') function:

"regular":

C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017EXPRESS\MSSQL\Log\ERRORLOG
LocalDB:

C:\Users\Solomon\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\LostInTranslation\error.log

You can use that with Dynamic SQL to generate the CREATE SERVER AUDIT statement as follows:

DECLARE @SQL NVARCHAR(MAX);

;WITH cte AS (SELECT CONVERT(NVARCHAR(259), SERVERPROPERTY('ErrorLogFileName')) AS [Log])
SELECT @SQL = N'CREATE SERVER AUDIT [ServerAuditName]
TO FILE
(   FILEPATH = N''' +
  LEFT(cte.[Log], CHARINDEX(N'\error', cte.[Log] COLLATE Latin1_General_100_CI_AS)) + N'''
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
) '
FROM   cte;

PRINT @SQL; -- DEBUG (just to see what is being executed)

EXEC(@SQL);

For reading the audit file, you don't need the Dynamic SQL as you can pass the expression to the DMF as follows:

;WITH cte AS (SELECT CONVERT(NVARCHAR(259), SERVERPROPERTY('ErrorLogFileName')) AS [Log])
SELECT *
FROM   cte f
CROSS APPLY sys.fn_get_audit_file(LEFT(f.[Log],
       CHARINDEX(N'\error', f.[Log] COLLATE Latin1_General_100_CI_AS)) + N'\*.sqlaudit',
    DEFAULT, DEFAULT);


How to SELECT from SHOW TABLE STATUS results

This has more columns than SHOW TABLE STATUS; but does the trick:

SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();

UPDATE 2011-06-07 19:02

SELECT table_name,Engine,Version,Row_format,table_rows,Avg_row_length,
Data_length,Max_data_length,Index_length,Data_free,Auto_increment,
Create_time,Update_time,Check_time,table_collation,Checksum,
Create_options,table_comment FROM information_schema.tables
WHERE table_schema = DATABASE();

These queries work if you set the current database.

You can also hard code the specific database:

SELECT table_name,Engine,Version,Row_format,table_rows,Avg_row_length,
Data_length,Max_data_length,Index_length,Data_free,Auto_increment,
Create_time,Update_time,Check_time,table_collation,Checksum,
Create_options,table_comment FROM information_schema.tables
WHERE table_schema = 'mysql';


Oracle data dictionary, tell built-in roles apart from custom roles

A list of predefined roles in Oracle 12c can be found in the Security Guide. Also in Oracle 12c the view dba_roles has a column ORACLE_MAINTAINED. A 'Y' in this column tells you that this is a role created by the Oracle installation scripts. For Oracle versions before 12c this column does not exist. So for Oracle version lower than 12c you have to check the manuals or search the internet for lists of Oracle predefined roles.

Note

There is a dictionary table SYS.USER$ that contains all roles and users. This view is not visible to non dba users.

select user#,type#,name from user$ order by user#;

lists all  users and roles ordered by their internal Iduser#. If typeis 0 the item is a role. Most of the time these IDs will be created in a sequential manner, so that roles and user created during installation have a low Id. But I found 12c databases with a lot of Oracle created roles with a high Id. Also if you upgrade a database then there will be roles created by Oracle scripts that have a higher Id than user created roles. So the user# is not a reliable criterion.


Do MySQL can do automatic EXPLAIN on every query?

Enable MySQL's Slow Query log.
Anything that takes more than the defined time will get written into that log and you can take a look at it.
Or, better still, throw it back at the Developer that wrote the query and get them to take a look at it and then then write a better query.
"Teach a man to fish ..." and all that ...
Databases are everybody's responsibility, not just the DBA's.


How to copy Azure SQL database to on-premises SQL Server daily for reporting?

You can't copy direct from AzureSQL to on-prem SQL (even on-prem SQL running in an Azure VM) that way.

To copy the database manually you need to export it via SSMS (or the relevant command line tool) or the Azure portal, transfer the resulting bacpak file to the target server and import it there using SSMS or the relevant command-line tools. If this is to be a regular thing (weekly or daily to provide a regularly updated reporting copy?) then you'll want to try automate that via powershell script run from task scheduler or similar.

Is there a reason that you want your extra copy in an on-prem SQL Server install instead of also being in AzureSQL? If not then you might consider changing that as CREATE AS COPY OF is likely to be significantly quicker than export+transfer+import.

Another option after creating your initial copy is to use SSIS to update it if it is easy to work out (from audit trail records, change tracking, etc.) what data has changed since the last update. This may be far more efficient as you only need to transfer new/changed data, though it relies on you keeping the database schema on the on-prem side identical to the production schema in AzureSQL. You may also wish to look into Azure Data Sync (currently in preview): https://docs.microsoft.com/en-us/azure/sql-database/sql-database-sync-data


Need an Oracle query to get the Oracle Home directory for a particular instance

You can do this from a Unix shell, like so:

[oracle@node1 ~]$ grep "^YOURSID:" /etc/oratab | cut -d: -f 2
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@node1 ~]$


What you described is a classic UPSERT pattern, named after the dual intention of Updating some records and Inserting others.

T-SQL offers the MERGE statement which can be used and has good performance. Not always the easiest to write/maintain.

SSIS options:

1) UPSERT via SSIS - Essentially what you do is identify your update rows and store them in a temp table so that you can handle Updates via an Execute SQL task in the SSIS Control Flow instead of the row by row OLEDB command in the data flow.
Essential needs are to ID your update rows and send them to the TMP table destination. Add an Execute SQL task after your data flow and update as a set. You will see huge performance increase over the OLEDB command task and this may be all you need. Handle inserts as normal into the destination table.

Source Query vs Existing table join if existing then determine if an update is needed otherwise insert. You can either blindly update or in my case I have a quick join if they exist, and another check to see if there is actually an update... you can do this all in a single step depending on your data/join conditions.

2) DELETE/INSERT via SSIS - Very similar to #1, but instead of performing an update you use two SQL tasks to first delete the update rows then insert new versions of them. All non-update rows get written as normal inserts in your data flow.


Setting skip-innodb does disable the InnoDB Storage Engine, but that does not convert the tables.

You need to script the conversion of all the tables.

STEP #1

Comment out the skip-innodb

[mysqld]
#skip-innodb

STEP #2

service mysql restart

STEP #3

Execute the Following Script snf View Results

CONVERT_SCRIPT=/tmp/InnoDBConversionToMyISAM.sql
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') "
SQL="${SQL} FROM information_schema.tables "
SQL="${SQL} WHERE engine='InnoDB' AND table_schema <> 'mysql'"
mysql -uroot -p -ANe"${SQL}" > ${CONVERT_SCRIPT}

STEP #4

View the script

vi /tmp/InnoDBConversionToMyISAM.sql

or

less /tmp/InnoDBConversionToMyISAM.sql

If you like the contents, then ...

STEP #5

Login to mysql and run

mysql> source /tmp/InnoDBConversionToMyISAM.sql

STEP #6

Uncomment out the skip-innodb

[mysqld]
skip-innodb

STEP #7

service mysql restart

STEP #8

There is no STEP 8. That's it !!!

Give it a Try !!!


Create trigger that executes maximum one time per day

Use INSERT IGNORE and have a unique key on the date.  The first insert to run for the day will succeed; the rest will be "ignored".


SQL Server 2016 issue regarding migration of in-memory table and foreign keys

Risk is in between you drop and recreate the Foreign key constraint any process (application, user etc etc) will be allowed to  insert data in any one of these tables which can break the referential integrity. If that happens you will not be able to recreate the Foreign key constraint (with checking integrity on existing data, which is a good thing) and you will have to clean up the data.

There are ways to tell SQL Server to ignore existing data and enforce the constraint only on new data. Which is BAD or GOOD depends on your business need.

Details here:

Delete Foreign Key Relationships
Create Foreign Key Relationships


Why is it bad practice to install SQL Server to the root of a drive

Yes SQl server installation is likely to fail if you are trying to install it on root of a drive which is never advisable and has security implications. Actually it is related to permission setting on specific folders. As per this connect item

  Based on further investigation this issue is due to the fact that SQL tightens the ACL for default data/log directories to exclude broad access for all authenticated users. If the drive root is chosen as the default data/log location then SQL will tighten the root ACL and thus effectively locking down the whole volume. The recommended course of action is to always use a subdirectory to store SQL data/log files.

So there seems to be problem with ACL's when installing on root of a drive in your case.


Avoiding foreign key repetition on related tables in a relational DB

Looks like you can remove the Period FK entirely from the object. When assigned to a Region, it is already assigned to the Period assigned to the Region. Define a special Region -- a NULL Region, "not a Region", Pass-Thru Region, an all inclusive region, whatever -- to act as a placeholder for a Period when you mean "assigned only to Period rather than a Region."

This, I think, would involve the minimum amount of changes to your schema: drop the Period FK field from the Object table.


What is the difference between `ORDER BY` and `PARTITION BY` arguments in the `OVER` clause?

The PARTITION BY works as a "windowed group" and the ORDER BY does the ordering within the group. However, because you're using GROUP BY CP.iYear, you're effectively reducing your window to just a single row (GROUP BY is performed before the windowed function). The average of a single row will be the value of that row, in your case AVG(CP.mUpgradeCost).

As for query 2, are you trying to create a running average or something? Not even sure what you would expect that query to return.

If you remove GROUP BY CP.iYear and change AVG(AVG(...)) to just AVG(), you'll see the difference.

Here's an example that will hopefully explain the use of PARTITION BY and/or ORDER BY:

Random table:

a     b
----- ------
X     1001
X     1002
X     1003
Y     1001
Y     1002

Now,

SELECT a, b, COUNT(*) OVER (PARTITION BY a) FROM r;

will return

a     b
----- ------ ------
X     1001   3
X     1002   3
X     1003   3
Y     1001   2
Y     1002   2

So you can see that there are 3 rows with a=X and 2 rows with a=Y. The ORDER BY clause comes into play when you want an ordered window function, like a row number or a running total.

SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY b),
             SUM(b) OVER (PARTITION BY a ORDER BY b ROWS UNBOUNDED PRECEDING)
FROM r;

... gives you:

a     b
----- ------ ------ ------
X     1001   1      1001
X     1002   2      2003
X     1003   3      3006
Y     1001   1      1001   <- row number and running total resets here; new partition
Y     1002   2      2003


SUSPECT Status Of Databases, but Ola Hallengren backup job completed SUCCESSFULLY

Your problem was easily reproduced.

I used the code in Creating a SUSPECT Database to create a suspect database on my local instance and then ran Ola's backup script.  The script executed without error (as you described).  The output did report the database as being suspect, but the script did not error.

Date and time: 2018-03-18 06:20:07
Database: [DemoSuspect]
Status: SUSPECT
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: SIMPLE
Encrypted: No
Differential base LSN: N/A
Last log backup LSN: N/A

In addition to not backing up suspect databases, there are (at least) Three cases where Ola Hallengren's Maintenance Solution won't backup a database.

A Major takeaway from this post is:

  You should always always double-check either the msdb backup
  history
  or the master.dbo.CommandLog table to make sure any important backup
  was taken.

There are a variety of ways to query successful backup using the samples in Script to retrieve SQL Server database backup history and no backups.  An example would be to look for any databases that haven't been backed up today.

SELECT d.NAME
    ,bck.last_db_backup_date
FROM sys.databases d
LEFT JOIN (
    SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SERVER
        ,msdb.dbo.backupset.database_name
        ,MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
    FROM msdb.dbo.backupmediafamily
    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
    WHERE msdb..backupset.type = 'D'
    GROUP BY msdb.dbo.backupset.database_name
    ) bck ON bck.database_name = d.NAME
WHERE bck.last_db_backup_date IS NULL
    OR convert(DATE, bck.last_db_backup_date) <> convert(DATE, sysdatetime())

My suggestion would be to add an extra job step to verify that all databases are being backed up and force the job to abend if a problem situation is detected.


There is no point in updating your statistics if you are rebuilding your indexes. The statistics for an index are updated if the index is rebuilt.

Another problem is that the rebuild index job in the Microsoft maintenance plans are not intelligent so they just rebuild all indexes whether they need it or not. Brent Ozar talks about this here http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

The best solution for index maintenance is to schedule a custom script that checks for things like fragmentation levels and then performs the appropriate action. The best script out there to do this can be found here http://ola.hallengren.com/

If you want to stick with the native maintenance plans then I would just drop the update statistics job.


Anomalous Updates in Normalized Database

Normalization is the formal process for removing redundancy from relations by taking projections which when joined back form the original relational and thus eliminate some redundancy without data loss.  It is the science underlying database design.  The first three normal forms, and BCNF, deal specifically with eliminating redundancy due by ensuring that every non-trivial functional dependency is fully dependent only on candidate keys.  Higher normal forms deal with other kinds of dependencies to further eliminate redundancies.  Even when fully normalized (5NF is generally considered the "final" normal form although there are four others in the literature) redundancy can still remain as not all redundancies can be removed by taking projections.

Another tool to address eliminating redundancy is the principle of orthogonal design which states that two distinct relvars cannot have in them a tuple with the property that if it appears in the first relvar it must also appear in the second and vice versa.  But this principle only addresses redundancy across relvars whereas normalization addresses redundancy within them so it doesn't help with your example.

Ultimately Date contends we just need more science to guide database design as that which we have today as you show isn't quite enough.  One practical point to your example is that although there is redundancy, at least it can be controlled redundancy if a table is defined to hold the dancers, all key, with name and birth date.  Then, name and birth date become a foreign key to the dances table, and that foreign key can be defined to cascade updates.  Then, if a particular dancer's birthdate is found to be in error and corrected, the DBMS will automatically handle updating all the places in the dance table where that dancer was listed.  Moving the control of the redundancy from the user to the system is a big step forward that you can get with today's SQL DBMS'.

All of this information is paraphrased from Date's excellent book Database Design and Relational Theory which provides a significant amount of thinking and detail around just this issue.  It is indeed the case that we stand on the shoulders of giants.


Constraining data based on related data in a different table. Should this be done on the database level or the application level?

From my understanding of your question, I feel this might work.

Remove CustomerVendorServices table from your design.
Add ServiceID and CustomerCost in VendorAccounts table.
Remove the FK reference for VendorID in VendorAccounts table from Vendors table.
Add a FK reference for VendorID in VendorAccounts table from VendorServices table.
Also have a separate Services table identified by ServiceID having ServiceDescription and DefaultCost.
VendorServices will then have a composite primary key of VendorID and ServiceID.


How to define a parameter that will be used as an IN argument inside a function?

Use an array and the ANY operator:
CREATE FUNCTION public.get_employees_id(P_IN_SALARIES int[], P_IN_DEPARTMENTS_CODES text[])
...
    select STRING_AGG(employee.id || '', ',') into v_employees_ids
    from employee
    inner join departament on department.id = employee.departament_id
    where employee.salary = any (P_IN_SALARIES)
    and department.code = any (P_IN_DEPARTMENTS_CODES);
...

When calling the function you can use any array notation you like, I prefer array[...]:
select get_employees_id(array[100,200], array['code1', 'code2'])


Increase query performance where large number of values appear in primary key

Put your values into #tmp table and join it with the rest of your query


pg_dump issue while dumping single schema

Seems your command is wrong, Follow the below command to dump dm_geo schema.

To dump without data

pg_dump dbname -s -n dm_geo -f backup.sql

To dump schema and data

pg_dump dbname -n dm_geo -f backup.sql


How to pattern match against Unicode characters by PostgreSQL `SIMILAR TO` expressions?

Here is a check constraint:
CHECK (col ~ '^[0-9\u0660-\u0669\u06F0-\u06F9\u0966-\u096F]+$')

The pattern starts with "beginning of string" (^) and ends with "end of string" ($)and allows one or more "digits" in between.
^ and $ are necessary, otherwise the pattern would match abc123efg.


Shrinking VARCHAR with MODIFY: data alignment and potential for data loss

You should properly examine the data metrics
Lengths of ip values greater than 45
SELECT IFNULL(LENGTH(ip),'Total'),COUNT(1) rowcount FROM mytable
GROUP BY LENGTH(ip) HAVING LENGTH(ip) > 45 WITH ROLLUP;

Suggested data type for ip using PROCEDURE ANALYSE()
SELECT ip FROM mytable PROCEDURE ANALYSE();

Whatever SELECT ip FROM mytable PROCEDURE ANALYSE(); reports is what you should use instead of just picking VARCHAR(45).
GIVE IT A TRY !!!


How is data lake and polyglot persistence different?

The term "polyglot" (meaning "speaking or using several different languages") was first used by Neal Ford* with respect to ICT in 2006 in a blog post in which he coined the term "polyglot programming" where he stressed that nowadays, it's not good enough to be an expert in (for example) C and C only - a competent software engineer must be able to move between significantly > 1 langauge(s).
Interestingly, he also mentions the JVM (and/or .NET) as kinds of universal machines (which they sort of are) and where one would use different JVM languages for different tasks and then combine the bytecode into one executable - we see this now for example (an interest of mine) in Clojure and Java - you have the functional aspects of Clojure which you can combine with the massive Java ecosphere (think of the huge number of libraries for example) to get the best of both worlds. Scala and Kotlin are other examples of where this can also be achieved.
This concept of platform diversity was extended by Martin Fowler to databases in approx. 2011 when he coined (in what he calls his "bliki" - blog/wiki) the term "polyglot persistence". His point was that different functionalities within a system can be best served by different databases - as per this image:

With the current move away from software monoliths and the trend for microservices where each area of a system is a different programme, possibly/probably living in a different container and/or server, this model is becoming more and more pertinent since the universal data interchange language of JSON and RESTful web services means that the impedance mismatch between different database systems is mitigated and not as important as it perhaps once was.
Now, as for data lakes, an excellent definition is available on the wiki (and excellent article BTW - well worth reading in its entirety):

A data lake is a system or repository of data stored in its
natural/raw format, usually object blobs or files. A data lake is
usually a single store of all enterprise data including raw copies of
source system data and transformed data used for tasks such as
reporting, visualization, advanced analytics and machine learning. A
data lake can include structured data from relational databases (rows
and columns), semi-structured data (CSV, logs, XML, JSON),
unstructured data (emails, documents, PDFs) and binary data (images,
audio, video).

So, the relationship between polyglot persistence and data lakes is clear, but they are definitely not one and the same thing!
To my mind, one can think of a data lake as an instance of polyglot persistence (in much the same way as an object is an instance of a class). Polyglot persistence is a concept, a data lake is a materialisation of that concept in action.
Unsurprisingly, traditional vendors are fighting back against this trend by trying to consolidate these database functionalities in their own products - Oracle for example has embraced JSON document stores into its flagship product (from here):

Oracle as a Document Store
Oracle Database 18c fully supports schemaless application development
using the JSON data model. This allows for a hybrid development
approach: all of the schema flexibility and speedy application
development of NoSQL document stores, combined with all of the
enterprise-ready features in Oracle Database 18c.

and it also has a separate Key-Value store - from here:

Oracle NoSQL Database is a solution for applications with the
following characteristics:
Produce and consume data at high volume and velocity
Require instantaneous response time to match user expectations
Developed with continuously evolving data models
Scale on–demand based on the dynamic workloads

It is worth nothing that the Oracle's flagship database and their NoSQL one are separate products. Interestingly, the most "holistic" approach to polyglot persistence appears to be being taken by PostgreSQL. In their database, you can have a Key-Value store, JSON documents and of course, normal relational tables and one can perform SQL between and within these different storage types.
Amazingly, PostgreSQL's performance as a document store is approx. 2.5 times better than MongoDB's with JSON documents according to this benchmark. Admittedly, EnterpriseDB could be seen to be biased as they are PostgreSQL specialists, but they put the benchmark up on GitHub for anyone to test and I haven't been able to find a refutation by MongoDB.
Finally, just a word of warning - while you may be able to do it, this might not necessarily be a good idea! I'll leave the final word to 2ndQuadrant (PostgreSQL experts):

PostgreSQL has json support – but you shouldn’t use it for the great
majority of what you’re doing. This goes for hstore too, and the new
jsonb type. These types are useful tools where they’re needed, but
should not be your first choice when modelling your data in
PostgreSQL, as it’ll make querying and manipulating it harder.

One thing's for sure, the database world will keep evolving and keep all of us in jobs for a long time to come! :-)
p.s. +1 for an interesting question and welcome to the forum!
* Interestingly, he calls himself a "meme-wrangler" - I like it, kinda quirky


connect to Oracle Cloud from SQLPlus and Instant Client

Assuming your Oracle database on Oracle cloud then you can connect to SQPLUS over SSH:

connect to oracle cloud service
click on the name of the deployment you want to connect to
copy public IP address
switch to Linux command prombt
check your and confirm the private public key for your deployment exist
run ssh -i ua_key_openssh oracle@IPADDRESS_YOU_GOT_FROM_POINT3
execute sqlplus command
enter username\password


Percona Node fails to join: Operation not permitted

It turned out to be a bug in percona-xtrabackup-2.1.2-611.rhel6.x86_64. Downgrading to percona-xtrabackup-2.0.7-552.rhel6.x86_64 solved the issue.