How can I generate fake data that adheres to the PRIMARY KEY-FOREIGN KEY constraints of my schema? I have a schema for which I would like to generate fake data. I can generate fake data using Python for each of the tables separately, but I don't know how to make sure the fake data generation adheres to the constraints I have set between the tables.

I would like to generate 1 million rows for each of the tables. My system is Ubuntu 18.04 LTS.

I came across Red Gate's SQL generator, but it is only for SQL Server AND available only in Windows. SQL Server is currently not available for Ubuntu 18.04 either. I currently have PostgreSQL and MySQL in my system.

http://www.generatedata.com I can install locally (didn't try it yet) but I still can't figure out how to make sure the data generation adheres to PK-FK constraints.

Basically, chain data-modifying CTEs in PostgreSQL for the purpose. Details very much depend on the undisclosed details of your setup and specific requirements. The meaning of "random" varies wildly.

Minimal demo:

CREATE TABLE fk_tbl(fk_id serial PRIMARY KEY, data int);
CREATE TABLE pk_tbl(pk_id serial PRIMARY KEY, fk_id int REFERENCES fk_tbl, data int);

WITH ins1 AS (
   INSERT INTO fk_tbl(data)
   SELECT trunc(random() * 10000)::int      -- or whatever
   FROM   generate_series(1, 10) g          -- or 1000000 instead of 10?
   RETURNING fk_id
   )
INSERT INTO pk_tbl(fk_id, data)
SELECT fk_id, trunc(random() * 55555)::int  -- or whatever
FROM   ins1
ORDER  BY random();                         -- optional

db<>fiddle here

Related:

Insert data in 3 tables at a time using Postgres
How can I generate a random bytea
Best way to select random rows PostgreSQL
Set random value from set


time executing inserts + slow SSH _ Hello,

my setup is a slow SSH to some linux machine. From there I start a mysql terminal client and execute about 200k inserts (in a while loop inside a stored procedure). This takes about 3 hours to complete. However, my inner sense is telling me that 200k inserts can't take that long (the DB is empty btw), so I am guessing that DB's feedback (line prints to terminal's stdin file descriptor) gets printed onscreen too slow; first on the local terminal on the linux machine, and then even slower on my screen I am SSH-ing from.

I would surely like to turn off all feedback from mysqld so that I would know when the inserts finished only when the mysql's client prompt got back. Probably much sooner than 3 hours. How could I do that ?

/etc/my.cnf follows:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

follows the stored procedures:

    select 'Initializing database..';
call TigInitdb();

-- Possible encodings are:
-- - 'MD5-USERID-PASSWORD'
-- - 'MD5-PASSWORD'
-- - 'PLAIN'
-- More can be added if needed.
call TigPutDBProperty('password-encoding', 'PLAIN');
call TigPutDBProperty('schema-version', '4.0');

select 'Adding new users with PlainPw: ', '+391xxxxxx', 'pwd_xxxxxx';
drop function if exists TigUserGen;

delimiter //

create procedure TigAddUser(_user_id varchar(2049) CHARSET utf8, _user_pw varchar(255) CHARSET utf8)
begin
    declare res_uid bigint unsigned;

    insert into tig_users (user_id, sha1_user_id, user_pw)
        values (_user_id, sha1(lower(_user_id)), _user_pw);

    select LAST_INSERT_ID() into res_uid;

    insert into tig_nodes (parent_nid, uid, node)
        values (NULL, res_uid, 'root');

    if _user_pw is NULL then
        update tig_users set account_status = -1 where uid = res_uid;
    end if;

    select res_uid as uid;
end //

create procedure TigUserGen(usr_count INT)
begin
    DECLARE cnt,len INT;
    DECLARE usr CHAR(6);
    SET cnt = 0;
    SET len = 0;
    SET usr = '';

    WHILE cnt < usr_count DO
        SET cnt = cnt + 1;
        SET usr = CAST(cnt AS CHAR(6));
        SET len = CHAR_LENGTH(usr);
        CASE len
            WHEN 1 THEN
                call TigTestAddUser(CONCAT('+39100000', usr), CONCAT('pwd_00000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 2 THEN
                call TigTestAddUser(CONCAT('+3910000', usr), CONCAT('pwd_0000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 3 THEN
                call TigTestAddUser(CONCAT('+391000', usr), CONCAT('pwd_000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 4 THEN
                call TigTestAddUser(CONCAT('+39100', usr), CONCAT('pwd_00', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 5 THEN
                call TigTestAddUser(CONCAT('+3910', usr), CONCAT('pwd_0', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            ELSE
                call TigTestAddUser(CONCAT('+391', usr), CONCAT('pwd_', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
        END CASE;
    END WHILE;
end //

delimiter ;

call TigUserGen(200000);

Thanks

Try commenting out the echoing of the uid

create procedure TigAddUser(_user_id varchar(2049) CHARSET utf8, _user_pw varchar(255) CHARSET utf8)
begin
    declare res_uid bigint unsigned;

    insert into tig_users (user_id, sha1_user_id, user_pw)
        values (_user_id, sha1(lower(_user_id)), _user_pw);

    select LAST_INSERT_ID() into res_uid;

    insert into tig_nodes (parent_nid, uid, node)
        values (NULL, res_uid, 'root');

    if _user_pw is NULL then
        update tig_users set account_status = -1 where uid = res_uid;
    end if;

    -- select res_uid as uid;
end //

Try throttling the output of the ids in groups of 1250

create procedure TigUserGen(usr_count INT)
begin
    DECLARE cnt,len,echo_count,echo_limit INT;
    DECLARE usr CHAR(6);
    SET cnt = 0;
    SET len = 0;
    SET usr = '';

    SET echo_limit = 1250;
    SET echo_count = 0;
    WHILE cnt < usr_count DO
        SET cnt = cnt + 1;
        SET echo_count = echo_count + 1;
        SET usr = CAST(cnt AS CHAR(6));
        SET len = CHAR_LENGTH(usr);
        CASE len
            WHEN 1 THEN
                call TigTestAddUser(CONCAT('+39100000', usr), CONCAT('pwd_00000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 2 THEN
                call TigTestAddUser(CONCAT('+3910000', usr), CONCAT('pwd_0000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 3 THEN
                call TigTestAddUser(CONCAT('+391000', usr), CONCAT('pwd_000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 4 THEN
                call TigTestAddUser(CONCAT('+39100', usr), CONCAT('pwd_00', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 5 THEN
                call TigTestAddUser(CONCAT('+3910', usr), CONCAT('pwd_0', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            ELSE
                call TigTestAddUser(CONCAT('+391', usr), CONCAT('pwd_', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
        END CASE;
        IF echo_count >= echo_limit THEN
            SELECT CONCAT('Users Generated : ',cnt) TigUserGenStatus;
            SET echo_count = 0;
        END IF;
    END WHILE;
    IF echo_count >= 0 THEN
        SELECT CONCAT('Users Generated : ',cnt) TigUserGenStatus;
    END IF;
end //

delimiter ;

call TigUserGen(200000);


How to query-and-increase a value (counter) in a thread-safe way? (avoid race conditions) In a table where each row has a counter (just an integer value), I need to get the current value and increase it at the same time.

Effectively, I want to do this:

SELECT counter FROM table WHERE id=123
UPDATE table SET counter=counter+1 WHERE id=123

But doing this as two queries is obviously not thread-safe: multiple processes doing the same thing (on the same row) may get the same counter value. I need them all to be unique, so each process would get the actual current value and increase it by one.

I can think of a construction where I implement a manual lock per row, but I wonder if there's an easier way of doing this?

The update statements works perfectly fine without the select before! Since single statements are safe by definition, even two UPDATE queries performed at the same time only will result in the row incremented twice.

If you actually want to select the value for your PHP script, do something with it and later want to update this exact counter value, you can do the following:

BEGIN;
SELECT `counter` FROM `table` WHERE `id` = 123 FOR UPDATE;
UPDATE `table` SET `counter` = `counter`+1 WHERE `id` = 123;
COMMIT;

This starts a new transaction, then selects the rows you want to update and locks them exclusively. You can then safely update those without worrying about other clients changing their content or even accessing the locked rows. Finally you need to commit your changes.

You should also read something about isolation levels. You likely do not want a value like READ UNCOMMITTED as isolation level. Everything else should be fine for this use-case.


Can a single database be excluded from an AG backup? I want to backup all the databases in an availability group except one. Using the @AvailabilityGroups option in Ola's Maintenance Solution scripts, can a single database in an Availability Group be excluded from a backup?

Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES,
  ALL_DATABASES, and AVAILABILITY_GROUP_DATABASES are supported. The
  hyphen character (-) is used to exclude databases, and the percent
  character (%) is used for wildcard selection. All of these operations
  can be combined by using the comma (,).

Ola Documentation

So as you list the databases to back up, it will look like this:

AVAILABILITY_GROUP_DATABASES,-%Db%...

All databases like Db will be excluded from the backup while the rest of the databases in the availability group will be backed up. Change that Db to be the one you want to not backup and adjust the % signs as applicable to your database.


Enable TLS 1.2 for SQL Server 2016 database mail I have been puzzled by this issue for almost 1 week. Hopefully someone in our community has experienced the same issue and already found a solution.

So here is my problem:

As per our company policy, we want database mail to be able to send emails over port 25 with TLS 1.2 enabled and with TLS 1.0 & TLS 1.1 disabled.

Our mail server is Exchange Server 2010, our SQL Server 2016 (Developer and Enterprise editions) boxes have OS of Windows Server 2016 Standard editions.

Our SQL Server version is:

select @@version
----------------------------------------
Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64)
    Dec 22 2017 11:25:00
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

We have the DB mail configuration as shown here.

The issue is whenever we turn on SSL

use msdb
exec dbo.sysmail_update_account_sp @account_id=2, @enable_ssl = 1;

We CANNOT send db mail (no matter whether our SMTP authentication is Windows Authentication, Basic authentication or Anonymous Authentication). The error message in db mail log is as follows:

  Message

  The mail could not be sent to the recipients because of the
  mail server failure. (Sending Mail using Account 2
  (2018-07-30T10:52:41). Exception Message: Cannot send mails to mail
  server. (Failure sending mail.). )

But if we turn off this SSL, there is no problem for db mail sent out.

So how can we enable SSL and uses TLS 1.2 for db mail?

I have enabled TLS 1.2 by adding registry as shown below

The details is from this link (see the FAQ section)

TLS1.2 is the only version of TLS considered secure now (March 2019).  It took considerable time and effort to discover that there are 2 essential, additional settings which are required to get this working which are not well known nor well documented, by Microsoft or on the web generally.  The following could save you a great deal of time and effort.
These are the 2 new Registry settings that fixed the problem for us:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

This is a reference to the thread where we eventually found this information, buried halfway down the thread: TLS 1.2 in .NET Framework 4.0

Below is the content for a simple executable registry file that I put together that will make the 2 new settings and the settings already shown on the thread above (i.e. this makes all of the necessary Registry settings*):
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

Note 1: SQL needs to be restarted for these settings to take affect but it is better to restart Windows since the new settings will affect .NET 4.x generally.
Note 2: In SQL, the SSL-checkbox must be ticked in the mail profile to use TLS1.2.
*Note 3: FYI We ran the free tool, Crypto V2, with the "Best Practices" option enabled before starting on getting this working.  We verified our changes afterwards using the new Crypto version 3.
Hopefully this will save considerable time, effort and frustration for others ;)


Study material for Advanced Databases module inc SQL, Relational Model, ERM & TM For my next module in my masters, I am planning on studying Advanced Databases. (Long story short, the masters is designed for people with NON-CS degrees, so I have never studied databases, therefore I have literally 0 knowledge).

The lecturer mentioned that I must have an understanding on the following concepts:

The relational Model;
Relational Algebra (and maybe Relational Calculus);
SQL;
Entity-Relationship Modelling (ERM) and Enhanced ERM (we are using UML notations);
Transactions Management;

I'm aware that questions have been asked about study resources on the subjects, particularly SQL, but, does anyone some good resource that covers a few of the subjects mentioned? Preferably online sources (so they will keep up to date).

I found the course at Standford to be helpful - if you sign up, you can view past lectures on many of these topics.

https://class.stanford.edu/courses/Engineering/db/2014_1/info


Optimization: Delete with Top and minimize the where clause date range I have a delete statement that get run repetitive until all the data are removed.

Delete TOP (1000) From Log Where CreationDate < '2011-Dec-31' AND pKey NOT IN (...)

I am considering of truncating the where clause so that the pseudo code is like:

while(@parameter < '2011-Dec-31' AND rowCount > 0)
{
 Delete TOP (1000) From Log Where CreationDate < @parameter AND pKey NOT IN (...)
 SET @parameter = DATEADD(day, 1, @parameter)
}

Would this help speed up the delete because it doesn't have to do cache as much data, or it doesn't matter that much since I have an index on CreationDate?

Thanks

You trade off

speed
smaller transaction log
concurrency

So running this in batches with a good index satisfies the last 2, but is slower. However, you don't need absolute speed if it is less intrusive to other operations

Otherwise, I'm not sure what you want...


Error loading example PostgreSQL database: "Could not find header for file ..." I am learning o use PostgreSQL so I am trying to insert a whole practice database "dvdrental", which I downloaded from PostgreSQL learning blog. I am on a Mac OSX and I am using PostgreSQL through my terminal (and ZSH).

These are the steps I took:

download dvdrental.zip file
create dvdrental database under the right user, with the right permissions
unzipped dvdrental.zip
tar-ed the folder that I extracted from .zip file

Here is an ls -l output of that is inside the extracted directory:

2163.dat
2165.dat
2167.dat
2168.dat
2169.dat
2171.dat
2173.dat
2175.dat
2177.dat
2179.dat
2181.dat
2183.dat
2185.dat
2187.dat
2189.dat
restore.sql
toc.dat

Here is the command that I am using to get the sample db dump into my newly created db:

~>pg_restore -U postgres -d weather /users/edchigliak/downloads/dvdrental.tar

The error I receive:

~>pg_restore: [tar archiver] could not find header for file "toc.dat" in tar archive

restore.sql is a plain SQL statement file in your "dvdrental.tar" file. First create your db with the statement below;

CREATE DATABASE db_name ENCODING = 'UTF8';

After that, instead of using pg_restore you can use psql.

psql --echo-all --file=restore.sql db_name


Oracle 12cR2 - How To Create DDL Script Of A View With All Dependencies and Permissions I have a view in my production database and I want to create same view in my test database.
I tried this commmand:
select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;

It generated a create script and I run it. But it didn't copy all of the dependencies, data types, etc.
How can I do this?
Regards,

That would require selecting from dba_dependencies. Since your dependencies may have their own dependencies that query would probably need to be recursive.
Also, if any of the dependencies are in a different schema then you'll also need the grants to go with them which you can get by selecting from dba_tab_privs and dba_col_privs.


What is right datatype with numbers along with '-' I know it might sound silly, I have to look up MySQL manual reference and another MySQL forum. as I could not find what is right datatype for numbers with '-' e.g

'405-6444235-1711500'

I'm not sure this number datatype like TINYINT, SMALLINT, MEDIUMINT INT and BIGINT will not working on this.
I'm not sure if varchar is a right choice for this.

So Any Suggest what is the right choice for this before I build a new table and Data await to upload it.

With hyphens, it's a string, full stop!

You could store it as a BIGINT (without the hyphens) and format it using the hyphens on the client side - putting hyphens at 4 and 12.

However, if the position of the hyphens varies, you'll have to store it as a string and not any form of number.

You should only store values as numbers if you're going to add/subtract/multiply or divide them - otherwise, they are strings and not "numbers" anyway! How do you perform mathematical operations with "numbers" which have hyphens?


wrong number or types of arguments in call to X? Here is the code for which I am getting the error mentioned in the question:

CREATE OR REPLACE PACKAGE collegeManagement IS
TYPE studId_scholarshipModification IS RECORD(studentID studenti.id%TYPE, modificationPercentage NUMBER);
TYPE modificationsRecordVarray IS VARRAY(2) OF studId_scholarshipModification;

PROCEDURE modifiyScholarships(modificationsList IN modificationsRecordVarray);
END collegeManagement;
/

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ --

CREATE OR REPLACE PACKAGE BODY collegeManagement IS

v_currentSholarship studenti.bursa%TYPE;

PROCEDURE modifiyScholarships(modificationsList IN modificationsRecordVarray) AS
BEGIN
    FOR i IN modificationsList.FIRST..modificationsList.LAST LOOP
        dbms_output.put_line(modificationsList(i).studentID);
        IF modificationsList.EXISTS(i) THEN
            SELECT bursa INTO v_currentSholarship FROM studenti WHERE id=modificationsList(i).studentID;
            IF v_currentSholarship=NULL THEN
                UPDATE studenti SET bursa=100+100*modificationsList(i).modificationPercentage WHERE id=modificationsList(i).studentID;
            ELSE
                UPDATE studenti SET bursa=bursa+bursa*modificationsList(i).modificationPercentage WHERE id=modificationsList(i).studentID;
            END IF;
        END IF;
    END LOOP;
END modifiyScholarships;
END collegeManagement;
/

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ --

set serveroutput on;

DECLARE

    TYPE studId_scholarshipModification IS RECORD(studentID studenti.id%TYPE, modificationPercentage NUMBER);
    TYPE modificationsRecordVarray IS VARRAY(2) OF studId_scholarshipModification;

    modifications modificationsRecordVarray:=modificationsRecordVarray();

BEGIN

    modifications.extend;
    modifications(1).studentID:=500;
    modifications(1).modificationPercentage:=0.4;
    modifications.extend;
    modifications(2).studentID:=501;
    modifications(2).modificationPercentage:=0.8;

    collegeManagement.modifiyScholarships(modifications);

END;

I already tried debugging the code using the steps suggested here but for the moment I can not understand which is the cause for the error.

This:

DECLARE

    TYPE studId_scholarshipModification IS RECORD(studentID studenti.id%TYPE,
      modificationPercentage NUMBER);
    TYPE modificationsRecordVarray IS VARRAY(2) OF studId_scholarshipModification;

    modifications modificationsRecordVarray:=modificationsRecordVarray();

BEGIN

    modifications.extend;
    modifications(1).studentID:=500;
    modifications(1).modificationPercentage:=0.4;
    modifications.extend;
    modifications(2).studentID:=501;
    modifications(2).modificationPercentage:=0.8;

    collegeManagement.modifiyScholarships(modifications);

END;

Should be this:

DECLARE

    modifications collegeManagement.modificationsRecordVarray :=
      collegeManagement.modificationsRecordVarray();
    -- no type definition, using the type from the package

BEGIN

    modifications.extend;
    modifications(1).studentID:=500;
    modifications(1).modificationPercentage:=0.4;
    modifications.extend;
    modifications(2).studentID:=501;
    modifications(2).modificationPercentage:=0.8;

    collegeManagement.modifiyScholarships(modifications);

END;

Defining a type with the same name and properties is not the same as using the actual type defined in the package.

You can test it with:

create or replace package pkg1 is
type t_m is varray(2) of number;
procedure p1 (p_m in t_m);
end;
/

create or replace package body pkg1 is
procedure p1(p_m in t_m) as
begin
  null;
end;
end;
/

declare
  type t_m is varray(2) of number;
  m t_m := t_m();
begin
  pkg1.p1(m);
end;
/

declare
  m pkg1.t_m := pkg1.t_m();
begin
  pkg1.p1(m);
end;
/

The first call fails, the second call succeeds.


SQL Server 2012 import/export wizard not showing at all So I have access of two databases that are stored in Azure, and using SQL Server 2012 to access them. All I need to do is copy a table from one db and duplicate and create it in another. I keep reading everywhere that all you have to do to bring up the import and export wizard and right click the db, go to tasks and then the option for the wizard will be right there. But I cannot find it in the menu anywhere! I feel like I'm crazy or something, but I hope this is a simple solution! Here is a screenshot of what I am seeing. Any help would be greatly appreciated! Thanks!

The import/export wizard is not showing because it is not available for Azure SQL. Even if you downloaded the latest version of SSMS, you shouldn't see it.

You are in a different environment that how on-premise SQL Server databases work. In that Azure SQL does not support using cross database queries. So doing an insert into or select insert are not going to work.

Depending on how big the table is, your easiest thing to do is using the script object wizard and just include the data. Then just run that under the destination database.

If you need to do this on a routine basis then look at the data sync services Azure offers.


Index rebuild not working with SQL Server 2016 I have some index with very high fragmentation, 85-100% and rebuilding does not help

The only clue I have is that it may not work if the data file is fragmented on disk and that could very well be the case. However, data is stored on a high performance SSD SAN. Disk fragmentation doesn't really mean anything.

I have the same problem with multiple index in the same database but the one I am most concerned with has 847653 pages and fillfactor 75%. Most other index have fill factor 90% and many of them has the same issue.

This is the script used

SELECT
    CASE WHEN MAX(ps.avg_fragmentation_in_percent) > 30 THEN
        'ALTER INDEX [' + i.name + '] ON [' + sc.NAME + '].[' + so.NAME + '] REBUILD With (maxdop = 1);'
    ELSE
        'ALTER INDEX [' + i.name + '] ON [' + sc.NAME + '].[' + so.NAME + '] REORGANIZE;'
    END AS [query], max( ps.avg_fragmentation_in_percent)
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps
    INNER JOIN sys.indexes i
        ON ps.OBJECT_ID = i.OBJECT_ID
            AND ps.index_id = i.index_id
            AND ps.database_id = DB_ID()
            AND i.name is not NULL
            AND ps.avg_fragmentation_in_percent > 5 -- min % to return
            AND ps.page_count > 1000 -- otherwise table is to smal.
    INNER JOIN sys.objects so
        ON i.OBJECT_ID = so.OBJECT_ID
    INNER JOIN sys.schemas sc
        ON sc.SCHEMA_ID = so.SCHEMA_ID
GROUP BY sc.NAME, so.NAME, ps.index_id, i.name
ORDER BY sc.NAME, so.NAME, ps.index_id DESC;

Since the question is closed being too broad, I have edited it back to the original question.

As mentioned in the comments there are various factors that could cause fragmentation, and following are the areas that i can think of in your case.

Fill Factor
Seems it's already lower than normal on particular index. However, consider fine tuning with it (in development server) if following options are not applicable in your scenario.

Auto Shrink
Verify by any chance the AUTO SHRINK option set to ON at database level as this could cause heavy fragmentation

GUID Columns
There is also possibility when the entries are inserted without an order, switching to SEQUENTIAL-GUID would be better option (if possible)


When is it ok to use an Index Hint on a query in production code? I've recently been working with decently sized tables (between 100s of million of rows to 10s of billions), and have ran into a few cases where I was forced into a corner to use query hints, which I used to think were bad practice but since learned they have their place (mostly the ForceSeek and ForceScan hints).

I've finally ran into a query where no matter what I do, I can't seem to get it to use the better index naturally (which is the primary key clustered index).

The query is pretty simple:

SELECT A.Field1, B.Field2
FROM TableA AS A WITH (FORCESEEK)
INNER JOIN TableB AS B WITH (FORCESEEK)
    ON A.PrimaryKeyClusteredIndexedField = B.PrimaryKeyClusteredIndexedField
WHERE B.NonClusteredIndexedField = SomeBooleanValue -- SomeBooleanValue = 1 (True)

I run this query across many databases (about 500 - this is just how our schema is designed) and in the relatively smaller databases it does default to using the Primary Key Clustered Index in the query plan. In a couple one-off much larger databases it instead tries to do an Index Seek on the Nonclustered Index field "B.NonClusteredIndexedField" which also then requires a Key Lookup.

Not sure why it would pick the Nonclustered Index over the Primary Key Clustered Index in this case, but the Key Lookup then severely impacts the performance of the query. Using an Index Hint on the TableB so that the code becomes INNER JOIN TableB AS B WITH (FORCESEEK, INDEX(IX_PrimaryKeyClusteredIndex)) fixes this problem but feels dirty to me. Is it ok to use in one-off cases like this, especially when it's an Index Hint to the Primary Key Clustered Index which is unlikely to change?

It's OK to do this only when a better solution doesn't exist.

As David Browne - Microsoft commented:

  If you were writing in another language you wouldn't think twice about telling the computer not just what to do by how to do it.  That's all an optimizer hint is.


SQL Server - What taks/queries should I need to execute when there is a lack of space in TempDB? I am having some troubles loading data to my fact table. Every time that I want to insert, I'm getting the following issue:

  Could not allocate space for object 'dbo.SORT temporary run storage:  140739614015488' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

My question is: Which practices should I apply in order to understand why this error or to know if tempDB is close to full or not? There exists any queries that normally tell me some information about this?

Check your index rebuild plan, db.SORT usually indicates a SORT_IN_TEMPDB option being used and not enough space to accommodate it.


Should SQL full text search match the value test2 when searching for test? if i have table data that is indexed using full text search in Microsoft SQL server 2005 that includes raw table data such as "test2" should a query using FTS find a match if the search string is "test"?

Yes you can use full text search in this case.

Select * from your_table WHERE CONTAINS(your_column, '"test*"' );

will find test2.

See section

C. Using CONTAINS with prefix_term in msdn


want to find out which databases are used in last 30 days or not I have about 120 databases in my SQL. I want to migrate so I want to know which databases are not used in last 30 days.

Unfortunately, the INFORMATION_SCHEMA and SHOW STATUS are of no value for InnoDB tables.

I am also sure you don't have time to gore through and interpret servers logs.

Here is the fastest way: Check the timestamp of the most recently accessed file inside every database.

All you need is the most recent file written in each of the 120 databases. This is true for both MyISAM and InnoDB.

I wrote a crazy post back on Dec 21, 2011 (  Fastest way to check if InnoDB table has changed) to check the timestamp of any individual InnoDB table.

You should write a bash script to cd into every folder under /var/lib/mysql, find the most recent file, and check the timestamp. Just subtract the most recent timestamp from UNIX_TIMESTAMP(NOW()).

If the difference is less than 2592000 (That's the number of seconds in 30 days), that database has been updated in the last 30 days.
If the difference is greater than 2592000, that database is over 30 days old.

Here is that script

#!/bin/bash

DATADIR=/var/lib/mysql

AGE_DAYS=30
(( AGE_SECS = AGE_DAYS * 86400 ))

RIGHTNOW=`date +%s`

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"

SQL="SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')"
DB_CSV=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
DBLIST=`echo "${DB_CSV}" | sed 's/,/ /g'`

OLDDBLIST=""
SPC=""
for DB in `echo "${DBLIST}"`
do
    DBFOLDER=${DATADIR}/${DB}
    cd ${DBFOLDER}
    FILECOUNT=`ls -lt *.[fiM][rbY][mdDI] | wc -l`
    if [ ${FILECOUNT} -gt 0 ]
    then
        FILENAME=`ls -lt *.[fiM][rbY][mdDI] | head -1 | awk '{print $9}'`
        FILETIME=`ls -l --time-style="+%s" ${FILENAME} | awk '{print $6}'`
        (( TIMEDIFF = RIGHTNOW - FILETIME ))
        if [ ${AGE_SECS} -gt ${TIMEDIFF} ]
        then
            OLDDBLIST="${OLDDBLIST}${SPC}${DB}"
            SPC=" "
        fi
    fi
done

echo ${OLDDBLIST}

CAVEAT : @Jynus wrote a post about using the audit_plugin to determine that info.

If you are using Percona Server, check this forum.


Find duplicate subscriptions in SQL Server I have these table:

create table Customers
(
    Id bigint identity(1,1) primary key not null,
    Name varchar(100) not null
)

create table CustomerActions
(
    Id bigint identity(1,1) primary key not null,
    [Date] datetime not null,
    CustomerId bigint not null
    [ActionId] bigint not null
)

create table Actions
(
    Id bigint identity(1,1) primary key not null,
    Name varchar(100) not null
)

and I have this view:

create view CustomerActionsView
as
select
    CustomerActions.*,
    Customers.Name as CustomerName,
    Action.Name as ActionName
from CustomerActions
inner join Actions
on CustomerActions.ActionId = Actions.Id
inner join Customers
on CustomerActions.CustomerId = Customers.Id

I have two actions: Subscription, and Cancellation.

So basically this is a simple design to store when a customer subscribes, and when he cancells.

A sample data might look like this:

[Date]-CustomerName-Action

"2018-04-24 17:44:20.000"-Rose-Subscribed
"2019-01-11 10:37:45.000"-Rose-Cancelled

Now I'm stuck at finding customers who have subscribed twice (or more than once). That is, they've subscribed when they were already subscribed. Or they've cancelled when they were already cancelled.

In other words, I'm trying to find consecutive actions, ordered by [Date] descending.

I've tried this:

select *
from
(
    select [Date], CustomerName, ActionName, row_number() over (partition by CustomerName, ActionName order by [Date] desc) as RowNumber
    from CustomerActionsView
) as Temp
where RowNumber > 1

If I exclude ActionName then row_number() won't be useful. If I include ActionName then it preceeds [Date] column.

I'm stuck at this point. How can I get repetitive consecutive actions?

Update: SQL Server version using select @@VERSION returns this value:

Microsoft SQL Server 2014 - 12.0.2000.8 (X64)   Feb 20 2014 20:04:26   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

You can use LEAD function that will 'give' access to the next record value

LEAD(ActionID,1,NULL) OVER(PARTITION BY CustomerID ORDER BY Date ASC) as next_ActionID - this is the key point here. For more details , please read on BOL
https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-2017

Test values;

INSERT [dbo].[Actions] ([Name])
    VALUES ( N'Subscription')
            ,(N'Cancellation')

INSERT [dbo].[Customers] ([Name])
    VALUES ( N'test')

INSERT [dbo].[CustomerActions] ([Date], [CustomerId], [ActionId])
    VALUES ( CAST(N'2018-04-24T17:44:20.000' AS DateTime), 1, 1)
    ,(CAST(N'2019-01-11T10:37:45.000' AS DateTime), 1, 2)
    ,(CAST(N'2019-01-12T10:37:45.000' AS DateTime), 1, 1)
    ,(CAST(N'2019-01-13T10:37:45.000' AS DateTime), 1, 1)

Query:

SELECT DISTINCT c.Name as CustomerName --,ca.id
FROM
    (
        SELECT
            id,customerid,actionID
            ,ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY Date ASC) as RN
            ,LEAD(ActionID,1,NULL) OVER(PARTITION BY CustomerID ORDER BY Date ASC) as next_ActionID
        FROM dbo.CustomerActions
    ) as CA /* customerActions  with next Action field */
    INNER JOIN dbo.Customers as c
    ON CA.CustomerID = c.ID
WHERE
    actionID = next_ActionID

or you can do it with ROW_NUMBER

;WITH CA AS
(
        SELECT
            id,customerid,actionID
            ,ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY Date ASC) as RN
            --,LEAD(ActionID,1,ActionID) OVER(PARTITION BY CustomerID ORDER BY Date ASC) as next_ActionID
        FROM dbo.CustomerActions
)
SELECT Distinct m.Name  --,c.CustomerID, c.ID
FROM CA as C -- current set
    INNER JOIN CA as N -- next set
        ON c.CustomerID = N.CustomerID
        AND c.rn + 1 = n.rn
    INNER JOIN Customers as m
        ON m.ID = c.CustomerID
WHERE
    c.ActionID = n.ActionID

output:

Name
test


How can I import Create Table Statement from SQL Developer into Visio to create an Entity Relationship Diagram? I have the following Create table statement from SQL Developer and I am trying to generate an ERD Diagram using 2003 Visio. I was wondering what I need to do to convert the Create Table from SQL Developer to an ERD Diagram Using Visio.

CREATE TABLE “TT_TEAM”.”HR_REVOKED_SECURITY_ORGS”
(
“ID” NUMBER NOT NULL ENABLE,
“ACAT_CODE” VARCHAR2(6 BYTE) NOT NULL ENABLE,
“APPR_SEQ_NO” NUMBER(3,0) NOT NULL ENABLE,
“USER_ID” VARCHAR2(30 BYTE) NOT NULL ENABLE,
“ACTION_IND” VARCHAR2(1 BYTE) NOT NULL ENABLE,
“ACTIVITY_DATE” DATE NOT NULL ENABLE,
“COAS_CODE” VARCHAR2(1 BYTE),
“ORGN_CODE” VARCHAR2(6 BYTE),
“POSN” VARCHAR2(6 BYTE) NOT NULL ENABLE,
“MANDATORY_APPR_IND” VARCHAR2(1 BYTE),
“APPR_POSN” VARCHAR2(6 BYTE),
“ORGN_MANAGER” VARCHAR2(6 BYTE) NOT NULL ENABLE;

You can't do that in Visio. However, you can reverse engineer a database using Visio. The answer to the question below should help you.

How can I import the contents of an Oracle database into Visio to create an Entity Relationship Diagram?


Why Is My pg_xlog Directory So Big? I am running a PostgreSQL 9.5 datbase. There is a master and a slave that is configured as hot_standby using streaming replication (I don't think this is related).

On the master I have the following wal settings:

postgres=# select name, setting, unit from pg_settings where name like '%wal_%';
             name             |   setting   | unit
------------------------------+-------------+------
 max_wal_senders              | 5           |
 max_wal_size                 | 128         | 16MB
 min_wal_size                 | 64          | 16MB
 wal_block_size               | 8192        |
 wal_buffers                  | 983         | 8kB
 wal_compression              | off         |
 wal_keep_segments            | 10          |
 wal_level                    | hot_standby |
 wal_log_hints                | off         |
 wal_receiver_status_interval | 10          | s
 wal_receiver_timeout         | 60000       | ms
 wal_retrieve_retry_interval  | 5000        | ms
 wal_segment_size             | 2048        | 8kB
 wal_sender_timeout           | 60000       | ms
 wal_sync_method              | fdatasync   |
 wal_writer_delay             | 200         | ms
(16 rows)

I guess the pg_xlog directory should grow to be 2GB max (max_wal_size = 128 * 16MB = 2GB), but it is 5GB now.

I don't understand why.

I had streaming replication set up using a replication slot, but the slave did not use the slot.
Writing

primary_slot_name = 'replication_slot_1'

in /var/lib/postgresql/9.5/main/recovery.conf on the slave solved the problem.


tempdb growth and shrinking tempdb I’m a DBA that is fairly new to SQL Server coming over from a UNIX/Informix.

I have a question about tempdb growth:

From what I can see on regular databases if I start with a database that is 10 GB and then put in a bunch of data into it to make it 100 GB, and then delete 50 GB out of it I have to perform a shrink to reclaim space on the hard drive. Until I do that the space is allocated to the database

How does the tempdb work?

If an initial size is 10 GB and then some query grows tempdb to let’s say 100 GB and then terminates does the space become available for future tempdb queries/temp tables etc..?

Or does it release the space to the operating system?

Tempdb is recreated whenever the instance is restarted.  At startup tempdb is created at the size specified in the system catalog.  This means if you define tempdb to be 10GB, and it grows over time to 100GB, then you restart the server, tempdb will now be 10GB again.

The question about shrinking is one loaded with bad connotations.  For a discussion around that, refer to this question.


Working with variables in stored proc I want to create a meta table on the fly using a stored pdocedure and passing a table prefix as "in" parameter and then build the final table name somehow.

My code looks like this:

SET @table_ = CONCAT(tablename, '_meta');
CREATE TABLE @table_ (name varchar(50), value varchar(50)) ENGINE=MyISAM;

Where tablename is the in parameter (as varchar(50) utf-8)

But I always face this error:

  Syntax error near 'CREATE TABLE @table_ (name varchar(50), value
  varchar(50)) ENGINE=MyISAM'

Am I missing something? Anyone has a hint?

You can use prepare statement for this and also need a delimiter for the procedure something as

delimiter //

create procedure table_create (in tablename varchar(100))
begin
 set @table_ = concat(tablename, '_meta');
 set @qry = concat("create table ",@table_,"(name varchar(50), value varchar(50)) ENGINE=MyISAM;");
 prepare stmt from @qry;
 execute stmt;
end;//

delimiter ;

Here is a test case in mysql

mysql> delimiter //
mysql>
mysql> create procedure table_create (in tablename varchar(100))
    -> begin
    ->  set @table_ = concat(tablename, '_meta');
    ->  set @qry = concat("create table ",@table_,"(name varchar(50), value varchar(50)) ENGINE=MyISAM;");
    ->  prepare stmt from @qry;
    ->  execute stmt;
    -> end;//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> call table_create('test');
Query OK, 0 rows affected (0.08 sec)

mysql> describe test_meta ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | YES  |     | NULL    |       |
| value | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


SQL Server Reporting Services: Generate Scripts Programmatically I'm looking to export SSRS reports from SSRS 2005 into SSRS 2017. I have around 100 reports to move but in total there are around 500 subscriptions across all of these reports.

The Script to file tool in SSMS 2005 would save a large amount of time however is there a way to programmatically call the Script to file function?

I know you can do something similar with Powershell to export databases.

I'm not sure how well powershell would handle the guids for the reports when you only transfer the jobs, but you could use rs.exe to execute a script file and call the webservice to download/copy/deploy Reporting Services Objects.

On the Sample Reporting Services rs.exe Script to Copy Content between Report Servers it is stated that Shared Schedules are supported for both native and sharepoint integrated instances.

There used to be a tool called "rsscripter" that provided a gui to create the scripts but I'm not sure how well it will work with more recent versions. It's included in the SQL Server fine build and looks like this:

The original article on that tool can still be found on archive.org Download Reporting Services Scripter


How do I insert a record and put the ID into an existing record in another table? I want to insert a record in one table and save the resuting ID in a record I already have in another table. Which sounds easy.

Simplistic example:

create table aaa (id serial primary key, foo int);
create table foos (id serial primary key, foo int, aaa_id int);

Imagine foos has rows which I want to insert in aaa.  But I want a record of aaa.id stored in foos.aaa_id.

Heres some code that definitely does not work:

with rows as (
    insert into aaa (foo)
        select foo from foos
        returning foos.id as foos_id, aaa.id as aaa_id
)
update foos
    set aaa_id = (select aaa_id from rows where foos_id = foos.id);

I can't find a way of getting the "returning" clause to return anything other than records from the table it inserted.

Surely there must be a simple way to do this?

You can use DO block:

DO $$
DECLARE
    rows CURSOR FOR
        SELECT * FROM foos;
BEGIN
    FOR r IN rows LOOP
        WITH _ins AS (
            INSERT INTO aaa (foo)
            VALUES (r.foo)
            RETURNING aaa.id AS aaa_id
        )
        UPDATE foos
            SET aaa_id = (SELECT aaa_id FROM _ins)
            WHERE id = r.id;
    END LOOP;
END$$;


DDL-trigger for ALTER_AUTHORIZATION I need to perform some audit, when change of a securable ownership, such as

ALTER AUTHORIZATION ON SCHEMA::[SchemaName] TO [PrincipalName];

happens in database.

Database scope DDL-trigger seems an appropriate mechanism for this purpose. In the documentation (section DDL Statements That Have Server or Database Scope) I see that there should be ALTER_AUTHORIZATION event.

However, when I'm trying to create appropriate DDL-trigger

CREATE TRIGGER [OnAlterAuthorization] ON DATABASE
FOR ALTER_AUTHORIZATION
AS
BEGIN
    PRINT 'Perform audit';
END

I'm getting error Msg 1084

  Msg 1084, Level 15, State 1, Procedure OnAlterAuthorization, Line 2
  [Batch Start Line 0] 'ALTER_AUTHORIZATION' is an invalid event type.

In sys.event_notification_event_types

SELECT type_name
FROM sys.event_notification_event_types
WHERE type_name LIKE 'ALTER_AUTHOR%';

there is no ALTER_AUTHORIZATION event, just

type_name
-----------------------------
ALTER_AUTHORIZATION_SERVER
ALTER_AUTHORIZATION_DATABASE

of them ALTER_AUTHORIZATION_SERVER does not suit obviously, and ALTER_AUTHORIZATION_DATABASE according to documentation

  Applies to the ALTER AUTHORIZATION statement when ON DATABASE is
  specified

So, the question is. Where is ALTER_AUTHORIZATION promised in the documentation? How can I catch change of a securable ownership in database?

I ran into the same issue you did. I would go on to discover that Event Notifications can be used to handle the AUDIT_CHANGE_DATABASE_OWNER event. (Note that this event can't be used with a DDL trigger.) I wrote an Event Notifications blog article that just happens to use the AUDIT_CHANGE_DATABASE_OWNER event as an example: SQL Server Event Handling: Event Notifications

Below is a script that can get you started.

USE SomeDatabase
GO

--Create a queue just for change db owner events.
CREATE QUEUE queChangeDBOwnerNotification

--Create a service just for change db owner events.
CREATE SERVICE svcChangeDBOwnerNotification
ON QUEUE queChangeDBOwnerNotification ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])

-- Create the event notification for change db owner events on the service.
CREATE EVENT NOTIFICATION enChangeDBOwner
ON SERVER
WITH FAN_IN
FOR AUDIT_CHANGE_DATABASE_OWNER
TO SERVICE 'svcChangeDBOwnerNotification', 'current database';
GO

CREATE PROCEDURE dbo.ReceiveChangeDBOwner
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @MsgBody XML

    WHILE (1 = 1)
    BEGIN
        BEGIN TRANSACTION

        -- Receive the next available message FROM the queue
        WAITFOR (
            RECEIVE TOP(1) -- just handle one message at a time
                @MsgBody = CAST(message_body AS XML)
                FROM queChangeDBOwnerNotification
        ), TIMEOUT 1000  -- if the queue is empty for one second, give UPDATE and go away
        -- If we didn't get anything, bail out
        IF (@@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION
            BREAK
        END
        ELSE
        BEGIN
            --Interrogate the event data for relevant properties/values.
            DECLARE @Cmd VARCHAR(1024)
            DECLARE @MailBody NVARCHAR(MAX)
            DECLARE @Subject NVARCHAR(255)

            SET @Cmd = @MsgBody.value('(/EVENT_INSTANCE/TextData)[1]', 'VARCHAR(1024)')
            SET @Subject = @@SERVERNAME + ' -- ' + @MsgBody.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(128)' )

            --Build an html table for use with an html-formatted email message.
            SET @MailBody =
                '
' +
                '
Server Name	' + @MsgBody.value('(/EVENT_INSTANCE/ServerName)[1]', 'VARCHAR(128)' ) + '
' +
                '
Start Time	' + @MsgBody.value('(/EVENT_INSTANCE/StartTime)[1]', 'VARCHAR(128)' ) + '
' +
                '
Session Login Name	' + @MsgBody.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'VARCHAR(128)' ) + '
' +
                '
Login Name	' + @MsgBody.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)') + '
' +
                '
Windows Domain\User Name	' + @MsgBody.value('(/EVENT_INSTANCE/NTDomainName)[1]', 'VARCHAR(256)') + '\' +
                    @MsgBody.value('(/EVENT_INSTANCE/NTUserName)[1]', 'VARCHAR(256)') + '
' +
                '
DB User Name	' + @MsgBody.value('(/EVENT_INSTANCE/DBUserName)[1]', 'VARCHAR(128)' ) + '
' +
                '
Host Name	' + @MsgBody.value('(/EVENT_INSTANCE/HostName)[1]', 'VARCHAR(128)' ) + '
' +
                '
Application Name	' + @MsgBody.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'VARCHAR(128)' ) + '
' +
                '
Command Succeeded	' + @MsgBody.value('(/EVENT_INSTANCE/Success)[1]', 'VARCHAR(8)' ) + '
' +
                '

' +
                '
Text Data:
' + REPLACE(@Cmd, CHAR(13) + CHAR(10), '
') +'

'
            --PRINT @Subject
            --PRINT @MailBody

            --Note: you may need to set [msdb] to trustworthy for this to work.
            --Another option is to sign a stored proc with a certificate.
            --See: https://docs.microsoft.com/en-us/sql/relational-databases/tutorial-signing-stored-procedures-with-a-certificate
            EXEC msdb.dbo.sp_send_dbmail
                @recipients = 'You@YourDomain.com',
                @subject = @Subject,
                @body = @MailBody,
                @body_format = 'HTML',
                @exclude_query_output = 1
            /*
                Commit the transaction.  At any point before this, we
                could roll back -- the received message would be back
                on the queue AND the response wouldn't be sent.
            */
            COMMIT TRANSACTION
        END
    END
END
GO

ALTER QUEUE dbo.queChangeDBOwnerNotification
WITH
    STATUS = ON,
    ACTIVATION (
        PROCEDURE_NAME = dbo.ReceiveChangeDBOwner,
        STATUS = ON,
        MAX_QUEUE_READERS = 1,
        EXECUTE AS OWNER)
GO


pg_restore: [archiver] could not find entry for ID 12 I'm trying to restore a dump (-Fd --data-only) from PostgreSQL v9.4.9, but I've come across the following error:

pg_restore: [archiver] could not find entry for ID 12

Exactly the same happens if pg_dump is invoked using -Fc instead of -Fd.

As per this link, this seems to be a known issue, but I'm not sure of the workaround I should apply to move over it.

Could this actually be overcome in this PostgreSQL version? If so, what should I alter to achieve it?

You need to identify in which table its throwing this error.

Are you taking full database dump or single table?

Anyhow Try this workaround.

Take schema dump alone.

pg_dump -O -s dbname > schema.sql
Restore it on the target.
psql -h host -U user -d < schema.sql
On target disable trigger on all the tables.

ALTER TABLE table_name Disable TRIGGER ALL;

Use this query to generate this statement for all the tables.

SELECT 'ALTER TABLE '
       || table_name
       || ' DISABLE TRIGGER ALL;'
FROM   information_schema.TABLES
WHERE  table_catalog = 'dbname' -- replace with your db name
       AND table_schema IN( 'public' );  -- add all your schema

Then take the data only dump.

pg_dump -U user dbname -Fc --data-only > dbname.dump

Then restore your backup.

pg_restore -h host -U user dbname < dbname.dump

To make the restore fast, use -j

pg_restore -h host -U user -j 10 dbname < dbname.dump


Changes to the database schema are called migrations, what are data changes called? Not sure where to ask this but here it is anyway.

I understand that changes to the database schema are called migrations from a certain version to another.

What do DBAs call changes to the underlying data, ie. the sequence of INSERT, UPDATE, DELETE? Think of the MySQL binlog for instance. Is there a specific term for this?

Check out DML - Data Manipulation Language. This includes CRUD operations (Create (i.e. INSERT - not to be confused with the CREATE TABLE statement below), Read (i.e. SELECT), Update, Delete) on Tables. It's what people associate most classically with SQL.

Compare this with DDL - Data Definition Language. DDL involves the CREATE TABLE and ALTER TABLE operations (among others - related to INDEXes amongst other things).

For completeness, there is also DCL - Data Control Language. DCL is for the control of access to tables - GRANT and REVOKE (i.e. GRANT READ ON schema_name.table_name TO user_name.

The SQL standard is an attempt to homogenise DML among various vendors - its success has been limited so far. Many servers implement non-standard extensions, their own Stored Procedure language, DDL and DCL. Take a look at the "Procedural extensions" section on the SQL page above.

Changes to a schema (from the same version of the same server to another or even within the same instance) are simply called DDL - CREATE TABLE, ALTER TABLE, DROP TABLE.

Changes to a schema brought about (normally) by an upgrade (e.g. Oracle 9 to Oracle 11) are called migrations - going the other way is called either downgrading or backward migration.

Changes to a schema due to a change of server (i.e. Oracle to SQL Server) are called ports ("porting"). These are normally quite difficult and involved due to the differences between servers mentioned above.


SQL developer: Setup debugger for plsql I'm trying to debug remotely pl/sql. But I can't  - database returns me an error. What should I do to fix this and start debugging ?

    ORA-30683: failure establishing connection to debugger
    ORA-12541: TNS:no listener
    ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
    ORA-06512: at line 1

UPD
I'm trying to debug procedure on remote server. On local computer I have only sql developer installed.

Do you have a local firewall running? You will need to add an exception for SQLDeveloper. It starts a listener that listens for the database/session debugging events. If it can't connect back to you, there-in lies the problem.


Catching two error messages/Throw into table My goal is to catch errors from backup jobs into monitoring table.
The problem is, that there are cases that backup statement returns more than one error, so ERROR_MESSAGE() is not enough:

  Msg 3201, Level 16, State 1, Line 1 Cannot open backup device '...'
  Operating system error 3(The system cannot find the path specified.).

  Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating
  abnormally.

I can use throw to catch both messages, but is there an easy way to insert throw output into table or overcome this problem in another way?

You can enhance error handling with Extended Events to overcome the TRY...CATCH shortcoming you are experiencing.  The design pattern involves these steps:

Create and start an Extended Events Session: it will capture sqlserver.error_reported events, filtered primarily by SPID.
Execute a statement in a TRY block.
Within (or after) the CATCH block, read the XEvents session data.
Use the available data to respond to the error(s) as appropriate.
Stop and drop the XEvents session.

Here's an example that can be run manually in a single batch from SSMS. Just make sure to replace all occurrences of "2016" in the script with your SPID.

CREATE EVENT SESSION [Error Handling Session(2016)]
ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION(
        sqlserver.session_id,
        sqlserver.sql_text
    )
    WHERE [package0].[not_equal_unicode_string]([message],N'''''')
    AND [severity]>(10)
    AND [sqlserver].[session_id]=(2016)
)
ADD TARGET package0.ring_buffer
WITH (
    --ALLOW_SINGLE_EVENT_LOSS
    --NO_EVENT_LOSS
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,

    MAX_MEMORY=4096 KB,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=OFF
);

ALTER EVENT SESSION [Error Handling Session(2016)] ON SERVER STATE=START;

BEGIN TRY
    BACKUP DATABASE master
    TO DISK = 'master.diff.bak'
    WITH DIFFERENTIAL;
END TRY
BEGIN CATCH
    DECLARE @XEData XML

    SELECT @XEData = CAST(xet.target_data AS XML)
    FROM sys.dm_xe_session_targets AS xet
    JOIN sys.dm_xe_sessions AS xe
        ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'Error Handling Session(2016)';

    /*
    Check value of "totalEventsProcessed" to ensure events have been
    dispatched to event session target (ring_buffer).
    If no events have been processed, delay for a period of MAX_DISPATCH_LATENCY +1 (in seconds).
    */
    IF @XEData.value('(/RingBufferTarget/@totalEventsProcessed)[1]', 'INT') = 0
    BEGIN
        WAITFOR DELAY '00:00:02';

        SELECT @XEData = CAST(xet.target_data AS XML)
        FROM sys.dm_xe_session_targets AS xet
        JOIN sys.dm_xe_sessions AS xe
            ON (xe.address = xet.event_session_address)
        WHERE xe.name = 'Error Handling Session(2016)';
    END

    --Comment/uncomment as desired to show the formatted XML session data.
    SELECT @XEData;

    --Shred the XML. Do whatever you want with this data:
    -- log it to a table, create and send an email alert, etc.
    SELECT
        x.c.value(N'(@name)[1]', N'NVARCHAR(MAX)') AS EventName,
        x.c.value(N'(@timestamp)[1]', N'datetime') AS EventTime,
        x.c.value(N'(data[@name="error_number"]/value)[1]', N'NVARCHAR(MAX)') AS ErrorNumber,
        x.c.value(N'(data[@name="severity"]/value)[1]', N'NVARCHAR(MAX)') AS Severity,
        x.c.value(N'(data[@name="state"]/value)[1]', N'NVARCHAR(MAX)') AS [State],
        x.c.value(N'(data[@name="user_defined"]/value)[1]', N'NVARCHAR(MAX)') AS UserDefined,
        x.c.value(N'(data[@name="category"]/text)[1]', N'NVARCHAR(MAX)') AS Category,
        x.c.value(N'(data[@name="destination"]/text)[1]', N'NVARCHAR(MAX)') AS Destination,
        x.c.value(N'(data[@name="is_intercepted"]/value)[1]', N'NVARCHAR(MAX)') AS IsIntercepted,
        x.c.value(N'(data[@name="message"]/value)[1]', N'NVARCHAR(MAX)') AS [Message],
        x.c.value(N'(action[@name="sql_text"]/value)[1]', N'NVARCHAR(MAX)') AS SqlText,
        x.c.value(N'(action[@name="session_id"]/value)[1]', N'NVARCHAR(MAX)') AS SessionId
    FROM @XEData.nodes('//RingBufferTarget/event') AS x(c)
    --WHERE x.c.value(N'(data[@name="destination"]/text)[1]', N'NVARCHAR(MAX)') = 'USER'

END CATCH

ALTER EVENT SESSION [Error Handling Session(2016)] ON SERVER STATE=STOP;
DROP EVENT SESSION [Error Handling Session(2016)] ON SERVER;
GO

After running the script, your SSMS output should look similar to this:

There's a lot of moving parts there. But you might be able to make it work for you. I wrote a few related blog posts about TRY...CATCH that may be helpful:

The Unfulfilled Promise of TRY...CATCH

Enhanced T-SQL Error Handling With Extended Events

Part 2: Enhanced T-SQL Error Handling With Extended Events


Longest matching suffix while using aggregate functions Background

Looking to find the longest matching string suffix.

Setup

Consider the following fiddle:

CREATE TABLE noun
    ("label" varchar(10))
;

INSERT INTO noun
    ("label")
VALUES
    ('bar'),
    ('blue bar'),
    ('red bar'),
    ('green bar'),
    ('purple bar'),
    ('handlebar')
;

CREATE TABLE noun_inflection
    ("label_singular" varchar(9), "label_plural" varchar(9))
;

INSERT INTO noun_inflection
    ("label_singular", "label_plural")
VALUES
    ('bar', 'bars'),
    ('handlebar', 'handlebar')
;

And the following query:

select * from noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular;

This returns two rows:

LABEL       | LABEL_SINGULAR | LABEL_PLURAL
------------+----------------+-------------
handlebar   | bar            | bars
handlebar   | handlebar      | handlebar

The first row is correct, but not desired. For this particular purpose, the Levenshtein distance can be used to eliminate the duplicate:

select * from noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular
order by
  levenshtein( n.label, ni.label_singular )
limit 1;

This re-orders the rows based on the label similarity. In this example, "handlebar" exactly matches "handlebar", and has a distance of 0. Adding the limit 1 restricts the query to a single row.

Problem

The setup works, except PostgreSQL 9.1 does not respect LIMIT modifiers in aggregate functions. That is, the following doesn't work:

SELECT
  xmlagg( xmlement( ... ) ORDER BY levenshtein( ... ) LIMIT 1 )
FROM
  noun n, noun_inflection ni

The problem remains. The word 'handlebar' matches '%bar' and '%handlebar', and so this returns two rows, which, in turn, injects two xmlelements into the resulting XML document when only one is expected.

Update #1

To clarify:

select
  xmlagg(
    xmlelement(
      name "noun",
      trim( TRAILING label_singular FROM n.label ) || ni.label_plural
    )
  )
from
  noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular;

That should return a single 'handlebar' XML element. Currently, it returns 'handlebars' and 'handlebar':

{ "Value": "<noun>handlebars</noun><noun>handlebar</noun>", "Type": "xml" }

The desired output is:

{ "Value": "<noun>handlebar</noun>", "Type": "xml" }

Update #2

Even though the following code solves the handlebar/handlebars problem, it prevents multiple different nouns from being returned:

select
  xmlagg(
    xmlelement(
      name "noun",
      trim( TRAILING label_singular FROM n.label ) || ni.label_plural
    )
  )
from
  noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular
group by n.label, ni.label_singular
order by levenshtein( n.label, ni.label_singular )
limit 1

Update #3

This looks like it will require a stored function. Something along the lines of:

  SELECT
    trim( TRAILING label_singular FROM p_noun ) || ni.label_plural
  FROM
    noun_inflection ni
  WHERE
    p_noun ILIKE '%'||ni.label_singular
  ORDER BY
    levenshtein( p_noun, ni.label_singular )
  LIMIT 1;

Question

How would you match and return only the longest substring?

What is wrong with the (maybe too obvious?):

select * from noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular
order by
  char_length(ni.label_singular) DESC
limit 1;


Computed column 'IsWeekend' in table  cannot be persisted because the column is non-deterministic I'm in the process of tuning a query in SQL Server 2012 that is spilling to tempdb due to an incorrect cardinality estimation when using the DATENAME() function on a column to check if the data is weekend or weekday. Due to the the use of the function, the query is non-sargable and mis-estimates the number of rows (est 1700, actual 38000).

The where clause was simply:

WHERE DATENAME(WEEKDAY, vqc.DateRecorded) NOT IN ('Saturday', 'Sunday')

I was hoping to use a Persisted Computed Column and then index it:

ALTER TABLE <table_name> ADD IsWeekend AS DATENAME(WeekDay, DateRecorded) PERSISTED;

However get the error:

  Computed column 'IsWeekend' in table  cannot be persisted
  because the column is non-deterministic.

According to BOL, a large number of Date functions cannot be persisted because they are non-deterministic.

  This is because the results depend on the LANGUAGE and DATEFORMAT
  settings of the server session. For example, the results of the
  expression CONVERT (datetime, '30 listopad 1996', 113) depend on the
  LANGUAGE setting because the string '30 listopad 1996' means different
  months in different languages. Similarly, in the expression
  DATEADD(mm,3,'2000-12-01'), the Database Engine interprets the string
  '2000-12-01' based on the DATEFORMAT setting.

There was an interesting way around this issue for the MONTH() function using a CASE statement that was posted on Stack Overflow, but this doesn't work for DATENAME due to SET DATEFIRST and the LANGUAGE setting.

Aside from joining to a calendar table in my query and then filtering the weekends from there, is there a way of determining whether a date is the weekend so it can be persisted and then indexed?

Or am I trying to overcomplicate the situation?

You can do a datediff between 1900-01-01, that just happens to be a monday, and check if modulo 7 is a 5 or a 6.

create table D
(
  DateRecorded date,
  IsWeekend as cast(case when datediff(day, 0, DateRecorded) % 7 in (5, 6)
                      then 1
                      else 0
                    end as bit) persisted
)

BTW, you don't need to persist the computed column in order to use it in an index.


How can I see execution time of an ALTER TABLE statement? Is it possible to see the execution time of an alter table command?
I think with \timing on in psql, it shows more than the execution time, but also the client round trip, and maybe other things as well. What I want is the "execution time" shown by explain analyze. But I can't run alter table through explain analyze (right?).

Turn on \timing, run your ALTER TABLE and SELECT 1.
Subtract the values for a good estimate.


mysql user creation with % to host , can only log on from remote host Using MySQL v5.1.52

After creating a mysql user with the following commands:

create user 'user1'@'%' identified by 'pass1';
grant select on db1.* to 'user1';
flush privileges;

Then doing a 'mysql -uuser1 -p' in the command line then entering the password I still get this error:

Enter password:
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)

However when connecting from a remote system (with the -h) to the above server using the above credentials it works.

Why can I not log on locally with this user?

Here is the user/host outpout:

mysql> select User,Host from mysql.user;
+----------------+-----------------+
| User           | Host            |
+----------------+-----------------+
| user1          | %               |

When connecting to your localhost it's going you use sockets instead of ports to connect.  Those grants need a specific 'localhost' grant.  To use your % grant locally connect specifying you want to connect via TCP

mysql -u user -p --proto=TCP

Further reading


Difference between an endpoint and a port in SQL Server While I know why are ports used in SQL Server, I wonder what is the difference between an endpoint and a port. Ports can be managed using Configuration Manager, is it also possible to manage endpoints with CM?
Thanks for your time.

I like to think of it as the endpoint being the "SQL Server side" part of what a client connects to. There are endpoints for TCP (probably the one you refer to), Named Pipes and Shared Memory. Those are the regular endpoints that a client application connects to. You can, for instance, set privileges on an endpoint, allowing login A to connect to TCP and B connect to Shared Memory (not that I've ever had the need for this). The endpoint is implemented by a DLL (I usually refer to all this as "NetLib", and for TCP and Named Pipes exposed through a port.

Then there are endpoints for mirroring and the old SOAP web-methods as well.


MariaDB replication from master to slave After doing some research around this topic, I should be attempting this task soon. However, I have two questions going around my head - could do with an experienced professional answering them.

I am using MariaDB 10.3 and the latest version of Centos 7.

Q1. From the MySQL console, when granting user permission rights, you would grant these for the Private IP and not the Public IP?

Q2. Currently, root permissions are disabled for remote sign-in. Is it a good practise to create a slave user instead and don’t touch root user permissions?

Thank you all!

A1. Use Private IPs where practical -- they are somewhat more secure.  This assumes, however, that both Master and Slave are on the same Private network or is 'tunneled' through.

A2. Create a separate "user" for the replication stream.  GRANT only REPLICATION SLAVE to that user.

Use root (or other SUPER) only for admin purposes.  Create specific user(s) for application code.  Grant them more limited access, perhaps GRANT ALL PRIVILEGES ON app_db.*


Why am I getting error 35353 when enabling CDC on a table with a CCI? I'm trying to enable Change Data Capture on a table with a clustered columnstore index, a unique non-clustered index, and a non-unique non-clustered index in SQL Server Standard 2019. The command fails with this error:
Msg 35353, Level 16, State 1, Procedure sys.sp_cdc_enable_table_internal,
Line 243 [Batch Start Line 16]
Change Data Capture cannot be enabled on a table with a clustered columnstore index.
Consider dropping clustered columnstore index

I don't understand why I'm getting this error since, as far as I can tell from Microsoft documentation, CDC is supported for CCIs.
Has anyone else run into this error or know why it's occurring?

I just heard back from Microsoft. The documentation on CCIs and CDC is incorrect/misleading. Change Data Capture is not supported for clustered columnstore indexes. It is supported for nonclustered columnstore indexes starting in SQL Server 2016. They are correcting the documentation.


MySql-Workbench: Can't connect to MySQL server on '127.0.0.1' (111) The hard data first:
Local machine: Xubuntu 17.10, with MySql-Workbench 6.3
Remote Machine: Ubuntu 16.04, with MySql-Client 5.7 and MySql-Server 5.7

The connection method is: Standard TCP/IP over SSH

The error:

  Your connection attempt failed for user 'root' from your host to
  server at 127.0.0.1:3306:   Can't connect to MySQL server on
  '127.0.0.1' (111)

Then it gives me a list with things to check.

  1 Check that mysql is running on server 127.0.0.1

check

  2 Check that mysql is running on port 3306

check

  3 Check the root has rights to connect to 127.0.0.1 from your address

I checked the mysql.user table and it says that root has the host: localhost and since MySql-Workbench does an ssh connect to the remote machine and then connects to the MySql-Server, localhost should be fine.
Nonetheless I tried setting the host to % as a wildcard but that didn't change anything either so I switched back to default localhost.
I also checked the my.cnf. That is the entire content of it:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

There is no bind-address or skip-networking directive set.

  4 Make sure you are both providing a password if needed and using the
  correct password for 127.0.0.1 connecting from the host address you're
  connecting from

Password and stuff is all fine. Passwords are set, they are typed correctly when logging in.

When I do, what Workbench is supposed to do

ssh to the remote machine (ssh user@remotemachine)
login to mysql server (mysql -u root -p)

It works flawlessly
I just can't do via MySql-Workbench

Help is much appreciated because I already tried everything I was able to research.

The error message says clearly the MySQL server could not be reached, hence check everything that could block access to it. This is not user related.

Do you have a running network adaptor on the machine you run Workbench on? Does it allow TCP/IP connections?
Can you reach the target machine your MySQL runs on?
Does MySQL run on that target machine?
Can you create an SSH tunnel to the target machine?
Can you reach the MySQL server when you are logged in on the target machine (e.g. by means of an SSH)?

If you can answer all that with yes there should be no problem connecting to the MySQL server from MySQL Workbench. Point 6 can be a bit more complicated, if your MySQL server is not on the same machine as where the SSH endpoint is or if it has networking disabled or if it uses a different port than what you tried.

However, the general idea should be clear now. Try to isolate the actual problem (is it network related or a config problem?). Maybe this video can also help you: https://www.youtube.com/watch?v=DCgRF4KOYIY&t=76s


How to calculate differences between maximum value and current value for each row? My sales table looks like this.
region          sales
======================
East            1200
Midwest         (Null)
North           2500
South           4000
West            2400

Expected query result:
region          sales     diff
================================
East            1200      2800
Midwest         0         4000
North           2500      1500
South           4000      0
West            2400      1600

The diff column is calculated by max(sales) - sales. I don't know well about SQL and I even don't know how I can write a search on google. Any help will be appreciated.

The MAX value you get in an subselect, the rest is simple math

CREATE TABLE Sales
    (`region` varchar(7), `sales` varchar(6))
;

INSERT INTO Sales
    (`region`, `sales`)
VALUES
    ('East', '1200'),
    ('Midwest', '(Null)'),
    ('North', '2500'),
    ('South', '4000'),
    ('West', '2400')
;

SELECT
`region`
, `sales`
,(SELECT MAX(`sales` ) FROM Sales) - `sales` AS diff
FROM Sales

region  | sales  | diff
:------ | :----- | ---:
East    | 1200   | 2800
Midwest | (Null) | 4000
North   | 2500   | 1500
South   | 4000   |    0
West    | 2400   | 1600

db<>fiddle here


"Oneway" Replication from Oracle DB to TimesTen currently I'm really stuck into a problem and I not able to finde a nice way to realize that.

I need a suggestion to replicate from oracle db (some tables) to a TimesTen. A connection is only possible from Oracle DB to Timesten.

I tried the following possibilities and I think they won't work, because at no time I can realize a connection from TimesTen to Oracle Database.

Active/Passive with Subscribers (On the subscriber will be done a clone, so this connection is not possible) And overall, active passive is oversized.
TT replication with subscribers over replication sheme: could be possible. From what I saw there will be a connection initiated from master to subscriber. But, I don't know how to get the data from oracle db into TT without using Cache groups. So this possibility it out too, because from what I got that is not possible.

Has anyone a suggestion. I will be very thankful for any idea.

It is possible (but not simple).
I just made the implementation of ORACLE to TimesTen real time replication.
The solution is based on ORACLE LogMiner utility. Using this tool you can generate the ORACLE transactions in a real time and then ship them to the program (TimesTen side) which will read and apply those transactions to the TimesTen database. The synchronization point is provided between ORACLE and TimesTen by another intermediate ORACLE database. This is very general description, but I can share with you the details if you wish...


How to revoke "Alter role" on users I have a user "sa" which is sysadmin in a MSSQL instance.
All users who log into the database have the permission to change the password and delete the "sa" user.
Is there a way to revoke this permission?

The only way other users have the ability to change the password of the SA account are other members of the sysdamin role and members of the security admin role. Are your users part of the sysadmin or security admin role?

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-2017


Should an update statement query plan ever go parallel? If an update statement's query plan does go parallel, does it guarantee a communication buffer resource deadlock, or is that determined by whether the parallel plan is using all cores?

From the documentation:

The update and delete operators in a parallel query execution plan are executed serially, but the WHERE clause of an UPDATE or a DELETE statement may be executed in parallel. The actual data changes are then serially applied to the database.

As to your second question,

does it guarantee a communication buffer resource deadlock

Any deadlock is predicated on the query / queries being run and how they are structured and executed, SQL Server does not inherently guarantee any deadlocks on its own.


How to get value from subquery in mysql? I have this two tables:
Article:
- id:(int) primary key, autoincrement, not null,
- title:(varchar) not null,
- price:(decimal) not null,
- cart_id(int) not null, //foreign key
- book_id(int) not null, //foreign key
- image_id(int) not null, //foreign key

Image:
- id:(int) primary key, autoincrement, not null,
- image:(Blob) not null

and
 Book:
- id:(int) primary key, autoincrement, not null,
- title:(varchar) not null,
- price:(decimal) not null,
- image_id(int) not null, //foreign key

I want when inserting into Article to insert also right image_id, here is query where i try to that:
insert into article(title,price,cart_id,book_id)
values('Book1',20.00,387,
(select b.id from books b join article a where b.title =  a.title limit 1))

When i run query like this(without image) it works fine, but i can't figure out query with image_id. So i thought maybe if i can get value from subquery select b.id from books b join article a where b.title =  a.title i can just to thisselect id from image where id= result from that query(i have foreign key book_image in book table btw). Am i totally missing point if not, how to solve this?

But as i said you don't need in article
title and image, as they are already existent in books and you can always join the books table
But you would do this so
INSERT INTO article(title,price,cart_id,book_id,image_id)
VALUES ('Book1',20.00,387,
(select b.id from books b  where b.title =  'Book1' limit 1),
(select b.image_id from books b where b.title =  'Book1' limit 1))


Configuring SQL Server for SSL Encryption I'm new to the DBA role and I've been tasked with configuring a SQL Server install for a client, with the requirement that databases will be distributed across two instances.  Connections to one of the instances require encryption under an SSL certificate so I'm hoping that I could gather some advice on how best to implement and test the encryption.

I've obtained a trusted certificate from a Certification Authority and installed this on the server, and set the properties on INS1 protocols to force encryption using the new cert.

When I query the Exec Connection DMV the encrypt_option value shows as True for all active SPIDS.  However, I'm able to query the instance under an encrypted connection via SSMS on my local machine, and I don't have a copy of the certificate installed locally.  I was under the impression that without a corresponding copy of the certificate client side my connection would be refused as I would need access to the public / private keys in order to decrypt / encrypt any data exchanged over the connection.

Questions

What is the expected behaviour if the client does not have a copy of the certificate installed and ForceEncryption=True on the server?
Should a connection to the instance be refused, or should an unencrypted connection be allowed assuming the client has the required credentials to access the instance?
Could it be that I'm allowed to establish an encrypted connection without a copy of the certificate installed client side, due to the fact that the issuing CA is a Trusted Root CA rolled out as part of our Group security policy?

Considerations

Additionally; external clients will be connecting to the instance via a Web Server in our DMZ, then routed to an internal app server which communicates with the DB server.  In this example would each device in the chain need a copy of the certificate installing (client machine, web server, app server, database server)?

Unfortunately as an inexperienced DBA there's probably a whole host of things I haven't even considered, so any advice that can be provided will be greatly appreciated. It would be good to have input from people who've implemented a similar solution if possible.

Response to comment

  Can you elaborate on "that databases will be distributed across two instances"?

The client has requested that the databases that will be hosted on this server are split across two instances, with the first instance hosting databases that require encrypted connections and the second instance hosting databases which allow unencrypted connections.

Many thanks

When the client connects to the server, the server provides its certificate.  If the client is not explicitly configured to trust whatever certificate the server returns, it will check the client keystore to see if it can trust the certificate.  The client keystore does not have to contain a copy of the server's certificate if it trusts the chain of certificate authorities that issued the certificate.

It is the exact same principle as to how your browser establishes an HTTPS connection to a web server.  Although you may not know it, the browser's keystore is configured to trust numerous certificate authorities, so as long as the certificate that is returned by the site was created by one of those certificate authorities, it will trust it.  This is what allows you to establish an HTTPS connection to your bank's website, for example, without installing certificates from the bank.  The bank used GoDaddy or DigiCert or a similar public certificate company, and browsers are configured by default to trust these "known good" certificate generators.

For example, an organization typically installs root and infrastructure certificates in the keystore of every Windows system on their network.  Then when SQL Server (or a web server or whatever) has a certificate installed that was generated by that organization's key infrastructure, most native Windows processes will trust the certificate because they'll use a Microsoft SQL Client driver, which refers to the Windows keystore to determine what certificates to trust.

A Java SQL client, such as Microsoft JDBC and jTDS, will NOT use the Windows keystore, so one would need to install the root and infrastructure certificates into the keystore that the associated Java process is using (a file named "cacerts", typically, but JDBC allows the keystore file to be specified).  The other option is to configure the connection string such that whatever certificate is returned by the server is trusted.  However, this would present the risk of connecting to a rogue server and it would trust its certificate.

There are a few other SQL clients out there, such as DataDirect Wire Protocol, that have options to specify a certain keystore, trust the server certificate, etc.

  Could it be that I'm allowed to establish an encrypted connection without a copy of the certificate installed client side, due to the fact that the issuing CA is a Trusted Root CA rolled out as part of our Group security policy?

Yes, as explained above.

  What is the expected behaviour if the client does not have a copy of the certificate installed and ForceEncryption=True on the server? Should a connection to the instance be refused?

I might amend this question to, "What is the expected behavior if the client does not trust the certificate..." since it doesn't have to have a copy of the certificate as long as it trusts the chain that generated it.  ForceEncryption is exactly what it sounds like--it will NOT allow an unencrypted connection.  The client has to trust the certificate that is returned and establish an SSL connection in order to connect and login to SQL Server.  If the client doesn't trust the certificate, the client will not be able to login and you'll see an error in the SQL Server error log.

  Additionally; external clients will be connecting to the instance via a Web Server in our DMZ, then routed to an internal app server which communicates with the DB server. In this example would each device in the chain need a copy of the certificate

No, the only system that needs to trust the certificate that SQL Server returns is the system that has a connection to the SQL Server.  For example, with a web application, the web server that connects to SQL Server would need to trust the certificate, or the chain that generated the certificate, that is returned from SQL Server.  The browsers that connect to the web application would need to trust the certificate that the web server returns.  The browsers are not connecting to SQL Server, and the SQL Server's certificate would not be forwarded by the web server to the browsers.  The browsers are not establishing a connection to SQL Server, so they don't need to trust the SQL Server's certificate.


How to sort the id users that have the maximum of a property? How to return the id of users that have the maximum of a property in a database?

id A B C

with A B and C being integer?

For instance: if managing a database of users that stole things in a computer game. I want to return users that have stolen the maximum amount of a resource called Metall.

The database sketch is the following:

id  Name    Adresse     Verteidiger     Metall  Kristal     Deuterium   Zerstörung  Datum

Even if this is false, I tried:

SELECT id I1 FROM `Angriff` WHERE NOT EXISTS
    SELECT id I2 WHERE Metall > I1.METALL;

To get a single row with a maximum value in MySQL you can simply use LIMIT:

SELECT *
FROM `Angriff`
ORDER BY Metall DESC
LIMIT 1;

if there are multiple rows with the same maximum this will return only one randomly.

To get all rows with the same maximum you can modify your query to use a Correlated Subquery:

SELECT * FROM `Angriff` AS t1
WHERE NOT EXISTS  -- there's no row with a higher `Metall`
  (  SELECT * FROM `Angriff` AS t2
     WHERE t2.Metall > I1.METALL );

A more common way in Standard SQL:

SELECT * FROM `Angriff`
WHERE Metall =
  (  SELECT MAX(Metall) FROM `Angriff` );


How to identify which connection is causing error 17886? I need a way to identify which connection is causing error 17886. I've researched and there are solutions to fix the error, but only if you know which connection is causing the issue.
I want to log errors so I don't have to sit and wait for it to happen - if it does again. Is there a way to use extended events to track:
Error #17886, connection, time it happened, etc.
Any ideas?

You can do this with an Extended Event Session.  EG
CREATE EVENT SESSION [TrackError_17886] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.username)
    WHERE ([error_number]=(17886)))
ADD TARGET package0.event_file(SET filename=N'TrackError_17886')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
go

Will record the errors in files in your "log" folder.


Show clients with staff assigned and no staff assigned I am trying to write a query to show a client list including whether or not there is a staff member assigned. If I use this query:

SELECT g.name AS group,  cl.name_lastfirst_cs AS client
FROM clients cl, groups g, link_group lg
WHERE lg.zrud_group = ?
AND lg.zrud_group = g.zzud_group
AND cl.zzud_client = lg.zrud_client
ORDER BY cl.name_lastfirst_cs

It shows all clients in a group although the staff assignment is left out. If I add the staff in:

SELECT g.name AS group,  cl.name_lastfirst_cs AS client, s.staff_name_cs AS Staff
FROM clients cl, groups g, link_group lg, staff s
WHERE lg.zrud_group = 'NEWHAN'
AND lg.zrud_group = g.zzud_group
AND cl.zzud_client = lg.zrud_client
ORDER BY cl.name_lastfirst_cs

It results in a blank result. Can anyone steer me to the correct way to build this?

UPDATE

I believe I have the answer resulting from your help:

List of clients by group No Staff

SELECT g.name AS group,
cl.name_lastfirst_cs AS client
FROM clients cl
JOIN link_group lg
ON cl.zzud_client = lg.zrud_client
JOIN groups g
ON lg.zrud_group = g.zzud_group
WHERE g.name = 'BEAUFT'
ORDER BY cl.name_lastfirst_cs

With Staff

SELECT g.name AS group,
cl.name_lastfirst_cs AS client,
s.staff_name_cs AS Staff
FROM  clients cl
LEFT OUTER JOIN link_group lg
ON cl.zzud_client = lg.zrud_client
LEFT OUTER JOIN staff s
ON cl.zrud_staff = s.zzud_staff
LEFT OUTER JOIN groups g
ON lg.zrud_group = g.zzud_group
WHERE g.name = 'MARTIN'
ORDER BY g.name, cl.name_lastfirst_cs

You are using deprecated syntax for joining tables that doesn't allow for OUTER joins.

I'm not 100% sure what you're trying to accomplish because I can't see your data, but it seems to me like you're performing JOINS when you might want LEFT OUTER JOINS.

First thing to do is to rewrite your query using JOIN keywords.

SELECT
  g.name AS group,
  cl.name_lastfirst_cs AS client
FROM
  clients cl
JOIN
  link_group lg
ON
  cl.zzud_client = lg.zrud_client
JOIN
  groups g
ON
  lg.zrud_group = g.zzud_group
WHERE
  lg.zrud_group = ?
ORDER BY
  cl.name_lastfirst_cs

Next, try using LEFT OUTER JOINs.

SELECT
  g.name AS group,
  cl.name_lastfirst_cs AS client
FROM
  clients cl
LEFT OUTER JOIN
  link_group lg
ON
  cl.zzud_client = lg.zrud_client
LEFT OUTER JOIN
  groups g
ON
  lg.zrud_group = g.zzud_group
WHERE
  lg.zrud_group = ?
ORDER BY
  cl.name_lastfirst_cs

Here is a decent visual representation of the different kinds of JOINS using Venn diagrams: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html


pt-online-schema-change tool will work for existing triggers for a table? I have a table name "user" ,I have tried to do partition the table and it was having a trigger already.

When i have tried to alter the table with pt-online-schema-change.It was showing the below error.

"The table Client.user has triggers.  This tool needs to create its own triggers, so the table cannot already have triggers."

I have tried to do partition by this below way,

pt-online-schema-change --execute --user=root --password=***** --host=localhost  --alter "partition by range(id) (partition po values less than (963299666),partition p1 values less than (963499666),partition p2 values less than (963699666),partition p3 values less than (MAXVALUE))" D=Client,t=user

The table structure is,

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scrip` int(11) DEFAULT NULL,
  `entered` int(11) NOT NULL DEFAULT '0',
  `customer` varchar(50) NOT NULL DEFAULT '',
  `machine` varchar(64) NOT NULL DEFAULT '',
  `username` varchar(50) DEFAULT NULL,
  `clientversion` varchar(20) DEFAULT NULL,
  `clientsize` int(11) DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  `description` varchar(80) DEFAULT NULL,
  `type` varchar(20) NOT NULL DEFAULT '',
  `path` varchar(255) DEFAULT NULL,
  `executable` varchar(20) DEFAULT NULL,
  `version` varchar(20) DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  `lenth` int(11) DEFAULT NULL,
  `windowtitle` varchar(255) DEFAULT NULL,
  `string1` varchar(255) DEFAULT NULL,
  `string2` varchar(255) DEFAULT NULL,
  `text1` text,
  `text2` text,
  `text3` mediumtext,
  `text4` text,
  `servertime` int(11) NOT NULL DEFAULT '0',
  `uuid` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `sservertime` (`servertime`,`customer`,`machine`,`scrip`),
  KEY `custmach` (`customer`,`machine`,`servertime`),
  KEY `customer` (`customer`,`scrip`,`servertime`),
  KEY `machine` (`customer`,`machine`,`scrip`,`servertime`),
  KEY `servertime` (`servertime`)
) ENGINE=InnoDB

Whether can I do partition with the existing trigger using pt-online-schema-change?

How to resolve this issue?

Can anyone give solution?

According to the documentation, the tool accomplishes its magic of keeping the old and new tables in sync while the copy process is happening via the creation of its own triggers and is therefore not compatible with tables already using triggers no matter what kind of change you intend to make.

Any modifications to data in the original tables during the copy will be reflected in the new table, because the tool creates triggers on the original table to update the corresponding rows in the new table. The use of triggers means that the tool will not work if any triggers are already defined on the table.
— http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html


Get a real query plan for EXPLAIN DELETE I have a delete query that runs a long time. I try to explain it:
EXPLAIN DELETE FROM matches
WHERE id = 1

And I get:
QUERY PLAN
Delete on matches  (cost=0.43..2.65 rows=1 width=6)
  ->  Index Scan using matches_pkey on matches  (cost=0.43..2.65 rows=1 width=6)
        Index Cond: (id = 1)

But this couldn't possibly be the complete cost, because the this table is referenced in many foreign key constraints that need to be checked, and I know for a fact that this query takes 40 seconds to complete.
How can I see the total cost of such a delete query and all the foreign key checks that need to happen before it completes?

I tried EXPLAIN (ANALYZE, BUFFERS) but that does not show me this information when the query is aborted due to the foreign key constraint in another table. It just shows the error message ("violates foreign key constraint"), but does not explain how much time it spent to get to that error message.
It's OK for it to fail (and, for my logic, revert the whole transaction), but I don't want this failure to take 2ms instead of 40 seconds. I already solved my issue, but I'm still curious if it's possible to get costs of a failed query.

You need to run EXPLAIN (ANALYZE, BUFFERS).
Beware that that actually executes the DELETE, so run it in a transaction and ROLLBACK afterwards.
That will show you information about foreign key constraints unless they are deferred, in which case they are executed at commit time.

I'm still curious if it's possible to get costs of a failed query.

In this case, the costs will be somewhere between the costs of the index scan (after which the trigger for the foreign key starts) and the total costs. It depends if the conflicting row is found early or late.


Slow insert with MySQL full-text index I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds.  It seems that MySQL rebuilds (a part) of the full text index after each insert/update.  Is this right?

The table has about 30 varchar and text fields, some indexes on varchar-fields and a fulltext index over the three fields title_text (char(255)), content_text (text), author_text (char(255)). In the table are about 210.000 rows, the table size is about 2.5 GB and the index size about 0.5 GB. The insert queries are done for every new row (no combined query for all inserts).

How can I get better performance from the INSERT? Is there perhaps an option to set when MySQL rebuilds the full-text index?

It must be as quick if not quicker to do:

ALTER TABLE mydb.datafulltxt DISABLE KEYS;
Do many INSERTs into mydb.datafulltxt
ALTER TABLE mydb.datafulltxt ENABLE KEYS;
Repeat as required.


How to access the error log Is there any other ways to access the error log in MySQL on ubuntu other than using SSH? I really need to access the error log, but I do not have any privileged to connect via SSH. Please help me.  Thanks

There are all kinds of ways to view those error logs. All those ways require some kind of configuration by a person with access to shells (that is, ssh access) on the Ubuntu host.

Your best bet is to ask the person who controls that host for help.

Unless things are horribly goofed up (that's a technical term -:) in your MySql server, you can get enough, or almost  enough, troubleshooting information from the errors MySql gives your applications.


select query not returning expected result I have a table which is having 30 columns. And I have executed the below query (using postgres 9.1, executed via pgAdmin)

select column2 || '|' || column3 || '|' || column4
from tablename
order by column1 asc

I am getting 5 rows with empty result. and I noticed that column3 having values as 'Data1|Data2' while I execute select * from table name.

Any idea?

Table structure is simple. All are character varying except column1 (numeric) and data having simple texts except column3. Column3 having data with delimer ("|). i.e., 12345|23333

How can I achieve the same in this?

psql -d mydb-U user1 -c "\copy tablename to
    '/home/user/table_data.csv' delimiter '|' csv header";

If any of the columns is null the result of the concatenation using || is also null.

You can use concat() or even concat_ws() ("concat With Separator") to make Postgres treat null values like empty strings:

select concat_ws('||', column2, column3, column4)
from tablename
order by column1 asc;

Note that concat_ws() will not append the separator if a value is "missing". If you do need it, you have to use concat()

select concat(column2, '||', column3, '||', column4)
from tablename
order by column1 asc;

Details in the manual:

9.4. String Functions and Operators

Regarding the last part of your question, you can use a select statement for the \copy command. For details please see the manual.


SQL Server Plan Guide vs Plan Freezing I am bit confused with terminology as official Microsoft SQL Server documentation including BOL refers to plan guide the one you create using sp_create_plan_guide proc. Does plan freezing is same thing or slightly different?. Reading Erland's great article here

  There are two ways to set up plan guides, a general way, and a
  shortcut which is also known as plan freezing. General plan guides
  were introduced in SQL 2005, whereas plan freezing was added in SQL
  2008.

I am reading plan freezing is when you create guide using sp_create_plan_guide_from_handle

Seeking a clarification on terminology being used, does plan guide and plan freezing is the same thing or they refer to slightly different way to stick the execution plan in the cache?

Yes, that is my understanding as well, and this post helps provide some examples for you to review.

Plan guides (plan freezing) in SQL Server 2005/2008