Unable to Restart the SQL server Agent from SSCM

Since we identified it was a permissions issue via the comments, I would suggest you adjust the account running the SQL Agent service to something different if this is a server.  Microsoft does a great job to outline the permissions needed for the SQL Agent service account.  That article also highlights a few pitfalls regarding some minimal permission grants you want to do so users outside of the sysadmin role can properly utilize Proxy accounts, etc.

If it's your local PC, it likely doesn't matter that you adjust this account nearly as much since that service isn't configured for automatic startup by default.

Glad you got it sorted.


Basically the mysqld is started as mysql user (in the init script is something like su mysql). Therefore there is no other option beside having access to this user.

But you can setup your system to run mysqld as a normal user.


Interpreting Decision Time (DT) in temporal databases

I don't know if there is necessarily a decision period, but there might be a decision date, separate from the valid period start date and the transaction date, where it makes sense to indicate when exactly a decision was made to make a new entry valid.

In your example the decision to hire Okihara probably had been made at some time prior to '2014-01-01', in which case it might have put certain obligations on the employer even before Okihara's first day on the job, so it made sense to record it separately.

One might presume that the decision date will never be after the transaction date, while the valid date might still be arbitrary.


Looks like a list of match results and you want to get won/lost points:

SELECT team, SUM(pointa), SUM(pointb)
FROM
 ( -- use all rows twice with switched points
   SELECT teama AS team, pointa, pointb
   FROM tab
   UNION ALL
   SELECT teamb, pointb, pointa
   FROM tab
 ) AS dt
GROUP BY team


getting the output of delete statement in UDF in postgres

You can use the GET DIAGNOSTICS command for this purpose.  A quick example:

DO $$
DECLARE i integer;
BEGIN
    DELETE FROM event1;
    GET DIAGNOSTICS i = ROW_COUNT;
    RAISE NOTICE 'DELETE %', i;
END;
$$;

For DELETE statements, the only useful variable is ROW_COUNT, for INSERTs there is also RESULT_OID.

Set the error level after the RAISE to your choice and set the logging configuration accordingly.


This is possible with ORDER BY score DESC and user variable.

Create table/insert data

CREATE TABLE logins
    (`id` INT, `login` VARCHAR(4), `score` INT)
;

INSERT INTO logins
    (`id`, `login`, `score`)
VALUES
    (1, 'log1', 4),
    (2, 'log2', 4),
    (3, 'log3', 28),
    (4, 'log4', 1),
    (5, 'log5', 3)
;

Query

With ORDER BY score DESC and user variable this also counts the same score up.

 SELECT
   *
   , (@rank := @rank + 1) AS rank
  FROM
   logins
  CROSS JOIN(
   SELECT
     @rank := 0
   )
   AS
    init_var_var
  ORDER BY
   logins.score DESC

Result

   id  login    score  @rank := 0    rank
------  ------  ------  ----------  --------
     3  log3        28           0         1
     1  log1         4           0         2
     2  log2         4           0         3
     5  log5         3           0         4
     4  log4         1           0         5

Query

As delivered table so you can filter out one record.

SELECT
   id
 , login
 , score
 , rank
FROM (
  SELECT
   *
   , (@rank := @rank + 1) AS rank
  FROM
   logins
  CROSS JOIN(
   SELECT
     @rank := 0
   )
   AS
    init_var_var
  ORDER BY
   logins.score DESC
)
 AS logins_ordered_ranked
WHERE
  id = 2

Result

    id  login    score    rank
------  ------  ------  --------
     2  log2         4         3

Query without WHERE id=2

SELECT
   id
 , login
 , score
 , rank
FROM (
  SELECT
   *
   , (@rank := @rank + 1) AS rank
  FROM
   logins
  CROSS JOIN(
   SELECT
     @rank := 0
   )
   AS
    init_var_var
  ORDER BY
   logins.score DESC
)
 AS logins_ordered_ranked

Result

    id  login    score    rank
------  ------  ------  --------
     3  log3        28         1
     1  log1         4         2
     2  log2         4         3
     5  log5         3         4
     4  log4         1         5


Single query approach to split and copy string values in a column

You should make use of a split string function.

select left(S.Item, charindex('|', S.Item) - 1),
       stuff(S.Item, 1, charindex('|', S.Item), '')
from dbo.tblProjects as P
  cross apply dbo.SplitString(P.Documents, '$') as S;

Aaron Bertrand blogged about some of the alternatives you have in Split strings the right way – or the next best way


Is this inconsistent treatment of `void` return from sql and plpgsql functions documented?

I don't believe it's documented. The behavior is incorrect or at best inconsistent but maintained probably because of a huge existing user code base that would break if it were corrected. The issue is caused by differences in how returns VOID is coded with SQL vs plpgsql. See: https://stackoverflow.com/questions/8319986/postgresql-functions-returning-void


Xquery, insert, Trigger and no place to squeeze stored procedure

Are you familiar with sql:column and sql:variable?  They allow you to manipulate XML with external variables or tables?  Work through this demo and examine the different 'before' and 'after' results to see if they could help you:

USE tempdb
GO

DECLARE @xml XML
DECLARE @someString VARCHAR(50) = 'test string'

SET @xml = (
SELECT
    object_id AS "@object_id",
    SCHEMA_NAME(schema_id) AS "@schemaName",
    name AS "@tableName"
FROM sys.tables t
FOR XML PATH('table'), ROOT('tables'), TYPE
)

SELECT 'before 1' s, DATALENGTH(@xml) dl, @xml x

-- Use sql:variable to add a variable into the XML
SET @xml.modify('insert element someData{sql:variable("@someString")} into (tables/table)[1]')

SELECT 'after 1' s, DATALENGTH(@xml) dl, @xml x

-- Use sql:column to add a column from a table into the XML
DECLARE @t TABLE ( object_id INT PRIMARY KEY, identity_col SYSNAME )

INSERT INTO @t ( object_id, identity_col )
SELECT object_id, name
FROM sys.identity_columns

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT
    t.c.value('@object_id', 'INT') object_id,
    t.c.query('.') tableData
INTO #tmp
FROM @xml.nodes('tables/table') t(c)

SELECT 'before 2' s, DATALENGTH(tableData) dl, tableData
FROM #tmp

UPDATE t
SET tableData.modify('insert element identCol{sql:column("ids.identity_col")} into (table)[1]')
FROM #tmp t
    CROSS APPLY @t ids

SELECT 'after 2' s, DATALENGTH(tableData) dl, tableData
FROM #tmp


MySQL trigger - delete data from multiple tables

You're missing a BEGIN ... END, as follows:

DELIMITER $$

CREATE TRIGGER `delete_organisation`
BEFORE DELETE ON `organisations`
FOR EACH ROW
BEGIN
  DELETE FROM contacts WHERE OLD.id = organisation_id ;
  DELETE FROM orders WHERE OLD.id = orders.organisation_id;
  DELETE from organisations_products where OLD.id = organisation_id ;
  DELETE FROM organisations WHERE OLD.id = organisations.parent ;
END $$


Select row if column data was not selected

This can easily be done using Postgres' distinct on operator:

select distinct on (book_id) name, title, bookd_id, date
from the_table
order by book_id, date;

This will pick the first row for each book_id based on the date column.


SQL query max aggregate selecting correct nonaggregate columns?

Such a bare column in an aggregate query is not allowed by the SQL standard. But SQLite allows it, and returns the intuitively correct result:

When the min() or max() aggregate functions are used in an aggregate query, all bare columns in the result set take values from the input row which also contains the minimum or maximum.

If you want to write portable SQL, don't use this feature.


(x IS NOT NULL) vs (NOT x IS NULL) in PostgreSQL

You have to distinguish two situations: you compare one COLUMN against NULL, or you compare the whole ROW (RECORD) against NULL.

Consider the following query:

SELECT
    id,
    txt,
    txt     IS NULL AS txt_is_null,
    NOT txt IS NULL AS not_txt_is_null,
    txt IS NOT NULL AS txt_is_not_null
FROM
    (VALUES
        (1::integer, NULL::text)
    )
    AS x(id, txt) ;

You get this:

+----+-----+-------------+-----------------+-----------------+
| id | txt | txt_is_null | not_txt_is_null | txt_is_not_null |
+----+-----+-------------+-----------------+-----------------+
|  1 |     | t           | f               | f               |
+----+-----+-------------+-----------------+-----------------+

This is, I guess, what you and I would expect. You are checking one COLUMN against NULL, and you get "txt IS NOT NULL" and "NOT txt IS NULL" are equivalent.

However, if you do a different check:

SELECT
    id,
    txt,
    x       IS NULL AS x_is_null,
    NOT x   IS NULL AS not_x_is_null,
    x   IS NOT NULL AS x_is_not_null
FROM
    (VALUES
        (1, NULL)
    )
    AS x(id, txt) ;

Then you get

+----+-----+-----------+---------------+---------------+
| id | txt | x_is_null | not_x_is_null | x_is_not_null |
+----+-----+-----------+---------------+---------------+
|  1 |     | f         | t             | f             |
+----+-----+-----------+---------------+---------------+

This may be surprising. One thing looks reasonable (x IS NULL) and (NOT x IS NULL) are the opposite of one another. The other thing (the fact that neither "x IS NULL" nor "x IS NOT NULL" are true), looks weird.

However, this is what the PostgreSQL documentation says that should happen:

  If the expression is row-valued, then IS NULL is true when the row expression itself is null or when all the row's fields are null, while IS NOT NULL is true when the row expression itself is non-null and all the row's fields are non-null. Because of this behavior, IS NULL and IS NOT NULL do not always return inverse results for row-valued expressions; in particular, a row-valued expression that  contains both null and non-null fields will return false for both tests. In some  cases, it may be preferable to write row IS DISTINCT FROM NULL or row IS NOT  DISTINCT FROM NULL, which will simply check whether the overall row value is null  without any additional tests on the row fields.

I must confess I don't think I've ever used a row-valued comparison against null, but I guess that if the possibility is there, there might be some use-case for it. I don't think is common, anyhow.


How can I synchronise Always Encrypted data from On-Premises to Azure SQL Database (near real time)?

In general, any tool that copies data (e.g. using SELECT/INSERT or bulk select/copy) should work. If the target database (in SQL DB) uses a different set of keys than the original database (in SQL Server), the tool needs to have access to both keys (and it would need to re-encrypt the data during the migration). If the target database using the same keys as the source database, it should be possible to avoid re-encrypting the data (please, see https://blogs.msdn.microsoft.com/sqlsecurity/2016/01/07/best-practices-for-moving-data-encrypted-with-always-encrypted/).

SSIS support both options to migrate the data (with and without re-encryption), but I'm not sure if it meets your near-real time requirement. You will basically need to run the SSIS job periodically.

Stretch Database supports moving encrypted data (without re-encrypting), but does not replicate encryption metadata. To retrieve the plaintext values or run queries on the part of the table that "stretches" to SQL DB, an application needs to connect to SQL Server (which has encryption metadata and will delegate the query to SQL DB). Your app can't access plaintext by connecting to SQL DB directly.


How much should I care about prepared statements for simple queries?

The difference is negligible much of the time. You'll see significant benefits if:

There are lots of joins or lots of predicates; or
The query contains long IN lists or similar large data elements

In general, unless it's a "hot" code path where you really hammer a particular query   I wouldn't make much effort optimising to focus on re-usable prepared statements.

(You should still parameterise your SQL rather than substituting parameters directly into the text, and people often confuse that with prepraed statements, but you seem to understand the difference.)


I think your first design is better with two additional table highlights and bookmarks.

In long run it will be difficult with design to save data in JSON format, Let say you need to search a bookmark or search a highlights, I am thinking what would be sql query for that. or Let say for page you need to show the highlights, How you will show that.

Even if somehow you solve above problem, performance issue will be a concern. The first design offers indexes and even with long run big tables data can fetched quickly.

Check the datatype of id column in table books_users, it would be INT,


Why postgresql pg_dump exports a view as a table?

Internally, a view is just a table with a rule, so this makes sense.

See here: https://postgresql.org/docs/9.5/static/rules-views.html

  Views in PostgreSQL are implemented using the rule system. In fact,
  there is essentially no difference between:

CREATE VIEW myview AS SELECT * FROM mytab;

  compared against the two commands:

CREATE TABLE myview (same column list as mytab);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
    SELECT * FROM mytab;

  because this is exactly what the CREATE VIEW command does internally.
  This has some side effects. One of them is that the information about
  a view in the PostgreSQL system catalogs is exactly the same as it is
  for a table. So for the parser, there is absolutely no difference
  between a table and a view. They are the same thing: relations.


Database design for bidirectional relationship

To reduce memory overhead,

That should never be factor in database design. Let the engine worry about that; just create your design based on how you will use your data.

  I have decided on the following system to keep the entire relationship represented by a single row instead of one row per direction.

That's fine, but in both cases you'll have to do two separate queries (or two criteria) to find all relevant relationships for a user:

SELECT *
FROM RELATIONSHIPS
WHERE From_User = @UserID OR To_User = @UserID

Note that this might be a little different if there is a meaningful difference between the two types of users. Say, between VENDORS and CLIENTS or something. But it sounds like the two are interchangeable.

  I am looking for feedback on whether I should represent both directions with a single column of (3x3 choices) or two horizontal columns of 3 choices each.

I wouldn't cram them into a single column, you'll just have to parse them back out when you need to use them, and that's a violation of First Rule of Normalization.

EDIT: Here's a (contrived) example to show what I mean:

Let's say you're designing a database for a social network, one more like Twitter (user_a can follow user_b independent of whether user_b follows user_a), and less like Facebook (user_a and user_b are either "friends" or not).

So if Marsha follows Joe, but Joe doesn't follow Marsha, we have two ways to create a "follows" table:

Followed     Follower    Status
---------    --------    ------
Joe          Marsha      T
Marsha       Joe         F

or

User1     User2    RtL_Follow_Status   LtR_Follow_Status
-----     ------   -----------------   -----------------
Joe       Marsha   T                   F

Sure, the second might save you a byte or two (or not, since I might not even need a row indicating that Joe doesn't follow Marsha), but the first is vastly easier to query, especially for the most likely application needs:

--Show all users following Joe
SELECT [Follower]
FROM [Follow_v1]
WHERE [Followed] = 'Joe'
AND [Status] = TRUE

vs

SELECT [User2] as [Follower]
FROM [Follow_v2]
WHERE [User1] = 'Joe'
AND [RtL_Follow_Status] = TRUE

UNION ALL

SELECT [User1] as [Follower]
FROM [Follow_v2]
WHERE [User2] = 'Joe'
AND [LtR_Follow_Status] = TRUE

Now your application might be nothing like this, or there might be reasons that I always need to know the reverse-follow-status when pulling up a relationship, but all I'm saying is that "saving a few bytes" is a low priority compared to making a database easy to understand and easy to query.

Note also the first example is likely to perform better as well, which might be more important as the application grows than saving a few bytes.


Importing mysqlnd database to MySQL server

The cause of the problems may be that the utf8mb4 character set has been added in Mysql 5.5.3.

You need to upgrade MySQL server or change your data to working with utf without mb4. Here you can find some tips how to do it


Change the Existing Topology of a MySQL Replication Setup

Based strictly on your question, the topology you want is not possible.  Why?

Here is what you presented (call it Topology#1)

    a -> b
    b -> c
    c -> a

and you want it to be (call it Topology#2)

    a -> c
    b -> c

Since ServerC cannot execute CHANGE MASTER TO with multiple host/port combinations, Topology#2 is mechanically impossible.

I have addressed this as to why IT IS NOT POSSIBLE:

Feb 03, 2012 : One slave, multiple masters MySql
May 15, 2011 : Is Multi Master Single Slave possible in mySQL DB? (My ServerFault Post)

Now, it you want to turn Topology#1 into this (call it Topology#3)

    a -> b
    a -> c

this is quite possible. Here are the steps:

STEP01) On ServerB, run STOP SLAVE;

STEP02) On ServerC, run SHOW SLAVE STATUS\G and check to make sure Exec_Master_Log_Pos is no longer updating.

STEP03) On ServerB, run SHOW SLAVE STATUS\G.

STEP04) Record Relay_Master_Log_File (call it RMLF) and Exec_Master_Log_Pos (call it EMLP) from STEP03 (For the sake of example, let RLMF='mysql-bin.000345' and EMLP=98765)

STEP05) On ServerC, run the following (Using RMLF and EMLP from STEP04):

STOP SLAVE;
CHANGE MASTER TO
master_log_file='mysql-bin.000345',
master_log_pos=98765;`
START SLAVE;

STEP07) On ServerB, run START SLAVE;

STEP08) On ServerA, run STOP SLAVE;

Going forward, have all INSERTs, UPDATEs, and DELETEs occur on ServerA only.


You can simply use a check constraint however I would personally restructure my schema. Essentially what you seem to be doing is serializing a lot of data into a path, and then you want to be sure it's correct. That's kind of yuck, from my perspective.

CREATE TABLE foo (
  id    serial,
  path  text
    CHECK ( path ~ '/files/client/\d+/(attachment|user)/\d+/(main|thumbnail)' )
);
INSERT INTO foo(path) VALUES
  ('/files/client/743052/attachment/2123598/main'),
  ('/files/client/743052/attachment/2123598/thumbnail'),
  ('/files/client/1475296/user/3541234/main'),
  ('/files/client/1475296/user/3541234/thumbnail');

INSERT INTO foo(path) VALUES ('/STUD/EVAN/beefcake_hotstuff.jpg');
ERROR:  new row for relation "foo" violates check constraint "foo_path_check"
DETAIL:  Failing row contains (6, /STUD/EVAN/beefcake_hotstuff.jpg).

I would probably go the opposite direction and normalize to some degree.

Normalizing

Normalizing would look something like this,

CREATE SCHEMA aws;

CREATE TABLE aws.client (
  client_id serial PRIMARY KEY
);
CREATE TABLE aws.attachment (
  client_id       int REFERENCES client,
  attachment_id   int PRIMARY KEY,
  name            text
);
CREATE TABLE aws.user (
  client_id       int REFERENCES client,
  user_id         int PRIMARY KEY,
  name            text
);

SELECT '/files/client' || client_id || '/attachment' || attachment_id || '/' || a.name
FROM aws.attachment AS a
JOIN aws.client AS c USING (client_id);


Linq compiled query uses clusted index instead of filtered index

Why does filtered index has such poor performance in this case?

The query couldn't use the filtered index, because of the IS NULL part.  By definition, the query needs to consider rows that have a Val of NULL, and the filtered index doesn't have those rows.

  How can I make linq to omit the IS NULL part?

This isn't really a database question, but...

LINQ is accounting for your parameter being nullable.  In the example you gave, it's not nullable (it's an integer literal).  In your real code, it's probably a nullable int (int?) or a string, or some other reference type.  Change the parameter to be non-nullable (or if it's a nullable value type, call .Value) to remove the IS NULL part of the query.

  Is there a way to enforce unique constrain that allows NULL with just one non filtered index?

If I understand your question, then no - I don't think so.  A UNIQUE constraint or index will allow 1 NULL value, but not multiple.  However, the filtered index might work for your purposes once the query issue has been fixed.


How to diagnose try_parse being very slow?

CPU time = 719 ms,  elapsed time = 718 ms.

Shows that this is CPU bound.

If you can reproduce the issue on a dev machine one way of seeing what the CPU is spending time on is to use Windows Performance Recorder.

After tracing for a few seconds whilst the following was running concurrently...

SET nocount ON;

DECLARE @d DATETIME

WHILE 1 = 1
  SELECT @d = try_parse(NAME AS DATETIMEusing 'en-us')
  FROM   master..spt_values

... I see (click to embiggen)

SQL Server is attributed 20.88% of total CPU time over that period. Over 75% of that amount is taken up with

SqlAccess.dll!System.Data.SqlServer.Internal.SqlParseIntrinsicImpl::<ParseSsDate>
SqlAccess.dll!System.Data.SqlServer.Internal.SqlAppDomain::ExecuteExtension

With a healthy chunk of that taken up with

clr.dll!IL_Throw
mscorlib.ni.dll!System.DateTimeParse.GetDateTimeParseException(System.DateTimeResult ByRef)

None of the names in master..spt_values happen to be parsed as valid dates so all end up returning null.

The above shows that for some reason TRY_PARSE calls the DateTime.Parse method and catches the exception rather than using the built in TryParse method that would likely perform better in this case.


SSRS Query Timeout vs DatabaseQuery Timeout

I once supported an application that would timeout certain queries after 300ms (not a reporting system). If that application were to default to a server level setting, they wouldn't have been able to achieve the different timeouts that the business required. This is an extreme case of course, but it's an example of why you might want different settings at the server and data set level.


You want something like this,

SELECT *, lag(cumsum,1,0::bigint) OVER (PARTITION BY day ORDER BY id)/60 AS grp
FROM (
  SELECT
    id,
    day,
    price,
    sum(price) OVER (PARTITION BY day ORDER BY id) AS cumsum
  FROM price
) AS t;

Working from the inside out we calculate the cumulative sum with sum() in a window function,

SELECT
  id,
  day,
  price,
  sum(price) OVER (PARTITION BY day ORDER BY id) AS cumsum
FROM price;

 id | day | price | cumsum
----+-----+-------+--------
  1 |   1 |    40 |     40
  2 |   1 |    20 |     60
  3 |   1 |    50 |    110
  4 |   1 |    10 |    120
  5 |   1 |    20 |    140
  6 |   1 |    60 |    200
  7 |   2 |    10 |     10
  8 |   2 |    40 |     50
  9 |   2 |    10 |     60
 10 |   2 |    20 |     80
 11 |   2 |    10 |     90
(11 rows)

Notice our cumsum resets on a new day, but it persists past your 60. We need to fix that, for that we simply look at the prior row, and divide by 60. We don't want to start a new group, unless the last row exceeds a multiple of 60.

SELECT *, lag(cumsum,1,0::bigint) OVER (PARTITION BY day ORDER BY id)/60 AS grp
FROM (
  SELECT
    id,
    day,
    price,
    sum(price) OVER (PARTITION BY day ORDER BY id) AS cumsum
  FROM price
) AS t;
 id | day | price | cumsum | grp
----+-----+-------+--------+-----
  1 |   1 |    40 |     40 |   0
  2 |   1 |    20 |     60 |   0
  3 |   1 |    50 |    110 |   1
  4 |   1 |    10 |    120 |   1
  5 |   1 |    20 |    140 |   2
  6 |   1 |    60 |    200 |   2
  7 |   2 |    10 |     10 |   0
  8 |   2 |    40 |     50 |   0
  9 |   2 |    10 |     60 |   0
 10 |   2 |    20 |     80 |   1
 11 |   2 |    10 |     90 |   1
(11 rows)

And you're done.


Replacing NULL values in LEFT JOIN with MAX function

You can use ISNULL(MAX(T2.LOGIN_TIME), 'Default Value') to replace the NULL.

But first you should consider that LOGIN_TIME varchar(255) with values such as 17.07.17 that holds dates should be DATE data type. Always use DATE or DATETIME when using dates, as varchars become troublesome when comparing or ordering (and even understanding them depending on culture info).

For example, the MAX() on a varchar value of 17.07.18 against 27.08.17 will return 27.08.17 because 27 is higher as literal than 17, but the year of the latter (2018) is higher than the former, thus returning an incorrect result. This won't happen if the column is DATE or DATETIME type.

This is the fixed SQL:

CREATE TABLE Table1 (
    USERID varchar(255),
    NAME varchar(255)
)
CREATE TABLE Table2 (
    USERID varchar(255),
    LOGIN_TIME DATE -- It's date here!
)
INSERT INTO Table1(USERID, NAME)
VALUES ('XYZ_1234', 'RAMBO'),
       ('XYZ_1235', 'ARNOLD'),
       ('XYZ_1236', 'BRUCE')
INSERT INTO Table2(USERID,LOGIN_TIME)
VALUES ('1234','2017-07-17'),
       ('1234','2017-07-20'),
       ('1235', '2017-08-27') -- ISO format

SELECT
    T1.NAME,
    ISNULL(CONVERT(VARCHAR(200), MAX(T2.LOGIN_TIME)), '5')
FROM
    Table1 T1
    LEFT JOIN Table2 T2 ON T1.USERID ='XYZ_' + T2.USERID
GROUP BY
    T1.NAME

If you are using a default value of a literal '5' then you have no other choice than converting the max login time to varchar so both types are compatible.

Maybe you wanted to display a particular date instead?


Are there ways to only replace SQL Server stored procedures when the definition has changed?

Permissions shouldn't be assigned to the stored procedure

Your stored procedure should sit in a schema, permissions are on the schema (granted to roles). This way, new objects in that schema have permissions automatically. No single object should have permissions

So either way would be OK.

See these other DBA questions:

Set permissions on all objects for SQL users
What standard should I follow when naming tables and views?
Schema design - best practices?

FYI, object_id is signed int so use "does not equal"

if object_id('my_proc') <> 0 drop procedure my_proc

Edit, nvarchar(max) is comparable directly

if object_Id('my_proc') <> 0
    drop proc ThenewProc
GO
CREATE PROC ThenewProc
...
GO

IF object_definition(object_id('my_proc')) <>
         object_definition(object_id('ThenewProc')) <> 0
BEGIN
    drop procedure my_proc
    drop proc ThenewProc
END
GO
CREATE PROC my_proc
...
GO


The on-disk structure of a table is independent of the order of columns the CREATE TABLE. This means that performance is not affected.

Each entry (this applies to both data and index) looks like

Header
Fixed length columns
Null bitmap
Variable length columns
Row version

For more, see Inside the Storage Engine: Anatomy of a record

There are a few cases where it can matter. But you won't usually find them day to day: normaly in repro scripts you'll find here or in blogs. For example, see comments to my SO answer here: https://stackoverflow.com/a/6121952/27535 and the link in comments below by @SQL Kiwi

However, from a consistency, maintainability and readability, I'd have PK columns first with columns in the same order as the actual PK key columns. Saying that, I don't normally rebuild tables to ensure that columns are in the "correct" place. It really does make little difference except to my OCD.


You could use an inner join to filter for rows that share a (p_id, k_id) value with rows that have a (t_id, pos) of (1,1) (sqlfiddle):

select  base.p_id
,       base.k_id
,       json_agg(base order by base.p_id, base.k_id, base.t_id, base.pos) as stats
from    (
        select  p_id
        ,       k_id
        ,       t_id
        ,       pos
        from    two
        ) base
join    two as filter
on      filter.t_id = 1
        and filter.pos = 1
        and filter.p_id = base.p_id
        and filter.k_id = base.k_id
group by
        base.p_id
,       base.k_id;

Or an equivalent way using an exists subquery (sqlfiddle):

select  base.p_id
,       base.k_id
,       json_agg(base order by base.p_id, base.k_id, base.t_id, base.pos) as stats
from    (
        select  p_id
        ,       k_id
        ,       t_id
        ,       pos
        from    two
        ) base
where   exists
        (
        select  *
        from    two as filter
        where   filter.t_id = 1
                and filter.pos = 1
                and filter.p_id = base.p_id
                and filter.k_id = base.k_id
        )
group by
        base.p_id
,       base.k_id;

Updated to use json_agg from @ErwinBrandstetter's answer.


NoSQL or RDBMS for a Giant Lookup Table?

As for NoSQL vs Relational, Postgres gives you the best of both but with the benefit of ACID. Along with its robust reliable relational engine, the JSONB data type allows you to save JSON data, internally stored in an optimized binary format that provides for indexing individual attributes. So you get the flexibility of less-structured data (JSON) with the very fast performance of Postgres indexed queries.

Also, Postgres supports UUID as a data type, storing the native 128-bit values. Support includes indexing and use as primary key.

As for your database structure, while I am do not know your data well, it appears from your example data that have a simple parent-child relationship between an "identity" or "login" or whatever you want to call it, and a "cred". On the cred table, for rows without a salt, just leave the extra fields null or with empty text ('').

Your description suggests you have a many-to-many between Person and Key entities. If so, in a relational database a many-to-many is always represented with a third table containing at lead two fields, the key of each table, so a pair of foreign keys. Classic example: Book table and Author table are connected by an Authorship table contains an Author ID and a Book ID for each author contributing to each book.

Tip: devise another name for your Key entity as the word key is, well, key to deigning and using a database. So, "key" is a confusing entity name.

Seems like you should be able to store all this data relationally rather than as JSON. But Postgres gives you both options.


Is there a way to pass two SQL tables w/o JOIN for processing into R using MS SQL Server 2016?

@input_data_1 can be a T-SQL statement, or a reference to a view masking a complex SQL statement, so things can really be as complicated as you make them.  Here's some simple examples of how to combine tables of the same or different structures.  You could conceivably do some filtering in the r script on the table source column (xsource):

DROP VIEW IF EXISTS dbo.vw_union1
DROP VIEW IF EXISTS dbo.vw_union2
DROP TABLE IF EXISTS dbo.table1
DROP TABLE IF EXISTS dbo.table2
DROP TABLE IF EXISTS dbo.table3
GO

CREATE TABLE dbo.table1 (
    col1 INT NOT NULL,
    col2 INT NOT NULL,

    CONSTRAINT PK_table1 PRIMARY KEY ( col1 )
)
GO

-- Same structure as table1
CREATE TABLE dbo.table2 (
    col1 INT NOT NULL,
    col2 INT NOT NULL,

    CONSTRAINT PK_table2 PRIMARY KEY ( col1 )
)
GO

-- Different structure to table1
CREATE TABLE dbo.table3 (
    col7 VARCHAR(5) NOT NULL,
    col8 DATE NOT NULL,

    CONSTRAINT PK_table3 PRIMARY KEY ( col7 )
)
GO

INSERT INTO dbo.table1 ( col1, col2 )
VALUES
    ( 1, 2 ),
    ( 3, 4 )

INSERT INTO dbo.table2 ( col1, col2 )
VALUES
    ( 1, 6 ),
    ( 3, 8 )

GO

INSERT INTO dbo.table3 ( col7, col8 )
VALUES
    ( 'A', GETDATE() ),
    ( 'B', GETDATE() )

GO

CREATE VIEW dbo.vw_union1
AS
SELECT 'table1' AS xsource, col1, col2
FROM dbo.table1
UNION ALL
SELECT 'table2' AS xsource, col1, col2
FROM dbo.table2
GO

CREATE VIEW dbo.vw_union2
AS
SELECT 'table1' AS xsource, col1, col2 , NULL AS col7, NULL AS col8
FROM dbo.table1
UNION ALL
SELECT 'table3' AS xsource, NULL AS col1, NULL AS col2, col7, col8
FROM dbo.table3
GO

-- Long SQL statement; same text as vw_union1
EXEC dbo.sp_execute_external_script
    @language = N'R',
    @input_data_1 = N'SELECT ''table1'' AS xsource, col1, col2 FROM dbo.table1 UNION ALL SELECT ''table2'' AS xsource, col1, col2 FROM dbo.table2',
    @script = N'OutputDataSet <- aggregate(InputDataSet$col2, by=list(InputDataSet$col1), FUN=median)'
GO

-- SELECT from view where structures are the same
EXEC dbo.sp_execute_external_script
    @language = N'R',
    @input_data_1 = N'SELECT xsource, col2 FROM dbo.vw_union1',
    @script = N'OutputDataSet <- aggregate(InputDataSet$col2, by=list(InputDataSet$xsource), FUN=median)'
GO

-- SELECT from view where structures are the different
EXEC dbo.sp_execute_external_script
    @language = N'R',
    @input_data_1 = N'SELECT xsource, col2 FROM dbo.vw_union2',
    @script = N'OutputDataSet <- aggregate(InputDataSet$col2, by=list(InputDataSet$xsource), FUN=median)'
GO

-- subset example
EXEC dbo.sp_execute_external_script
    @language = N'R',
    @input_data_1 = N'SELECT xsource, col2 FROM dbo.vw_union1',
    @script = N'InputDataSet <- subset( InputDataSet, xsource == "table1")
OutputDataSet <- aggregate(InputDataSet$col2, by=list(InputDataSet$xsource), FUN=median)'
GO

There is not an @input_data_2 or @input_data_n at this time.


How to traverse a tree in SQL? Sybase edition

It's bugging me that this question doesn't have at least one answer.  So...

You could use a mix of primary/foreign keys within the table per Joe Celko.  Alternatively, you might want to consider a closure table.

Generally, the table referencing itself seems to be the most common method for handling this.  Of course, you would have to consider looping.  Some DBMSs have CTEs which can use Recursion, so the looping is inherent in the CTE.  For Sybase, you'll probably have to throw a loop in code somewhere.


Get Data to connect to the AdventureWorksDW

In SQL Server, databases don't have passwords.  SQL Server uses logins, via either Windows Authentication or SQL Server Authentication.

Try using the same login you use to sign on to your workstation.


Stored Procedure doesn't show result in SQL Server

You need to split your parameters into rows and use where ... in as a check.

Using a split function like this one your code could look like this.

CREATE PROCEDURE [dbo].[sp_tst_CSENG_JulieCapitalHours]
    @StartDate DATETIME ,
    @EndDate DATETIME ,
    @ProjHomeGrp NVARCHAR(MAX) ,
    @ProjHier NVARCHAR(MAX)
AS
BEGIN

    DECLARE @ProjHomeGrpTbl TABLE (Value NVARCHAR(4))

    INSERT INTO @ProjHomeGrpTbl(Value)
    SELECT LTRIM(RTRIM(s))
    FROM dbo.Split(',', @ProjHomeGrp);

    DECLARE @ProjHierTbl TABLE (Value NVARCHAR(5))

    INSERT INTO @ProjHierTbl(Value)
    SELECT LTRIM(RTRIM(s))
    FROM dbo.Split(',', @ProjHier);

    SELECT  [Capital Project] ,
            [Capital Task] ,
            ResourceName ,
            ProjectName ,
            [Project Home Group] ,
            ActualWork ,
            TimeByDay ,
            ResourceStandardRate ,
            ActualWork * ResourceStandardRate AS Dollars ,
            [Project Hierarchy]
    FROM    [IR.CapOnly]
    WHERE   ( TimeByDay >= @StartDate )
            AND ( [Project Home Group] in (SELECT Value FROM @ProjHomeGrpTbl) )
            AND ( TimeByDay <= @EndDate )
            AND ( ActualWork > 0 )
            AND ( [Project Hierarchy] in (SELECT Value FROM @ProjHierTbl) )
    ORDER BY ProjectName ,
            ResourceName

END


Another possible solution: (thanks John Eisbrener for the sample data)

DECLARE @Assignment TABLE
(
    StartDate DATE,
    EndDate DATE,
    Equipment VARCHAR(50)
);

INSERT INTO @Assignment VALUES
    ('4/1/2016', '4/23/2016', '1001')
   ,('4/3/2016', '4/10/2016', '1002')
   ,('3/30/2016', '4/20/2016', '1003');

DECLARE @Event TABLE
(
    EventID INT NOT NULL IDENTITY(1,2),
    StartDate DATE,
    EndDate DATE,
    Equipment VARCHAR(50),
    [Event] VARCHAR(50)
);

INSERT INTO @Event
    (StartDate, EndDate, Equipment, [Event])
VALUES
    ('4/2/2016', '4/3/2016', '1001', 'maintenance')
   ,('4/8/2016', '4/10/2016', '1001', 'maintenance')
-- Uncomment out prove it still works when stacking events back-to-back
-- ,('4/11/2016', '4/12/2016', '1001', 'maintenance')
   ,('4/4/2016', '4/5/2016', '1002', 'maintenance');

WITH AllEvents AS (

    -- Unpivot all the Assignments
    SELECT Equipment, V.EventDate, V.DateType, 'Active' AS EventType
    FROM @Assignment
    CROSS APPLY (
        VALUES
            (StartDate, 'start'),
            (EndDate, 'end')
    ) AS v(EventDate, DateType)

    UNION ALL

    -- Unpivot all the Events
    SELECT Equipment, V.EventDate, V.DateType, [Event] AS EventType
    FROM @Event
    CROSS APPLY (
        VALUES
            (StartDate, 'start'),
            (EndDate, 'end')
    ) AS v(EventDate, DateType)

)
SELECT AE.Equipment,
    AE.EventDate AS StartDate,
    NextEvent.EventDate AS EndDate,
    EventStatus =
        CASE
            WHEN AE.DateType = 'end'
                AND AE.EventType = 'maintenance' THEN 'Active'
            ELSE AE.EventType
        END
FROM AllEvents AE
CROSS APPLY (
    -- Next Event by date
    SELECT TOP(1) EventDate, EventType, DateType
    FROM AllEvents
    WHERE Equipment = AE.Equipment
        AND EventDate > AE.EventDate
    ORDER BY EventDate
) AS NextEvent
ORDER BY AE.Equipment, AE.EventDate


Performance: ALTER TABLE with default value vs. iterating over all records to set new value

The primary difference you need to be concerned about is whether or not a table lock is acceptable for an extended period of time.  One approach acquires a lock against the table and one acquires locks on rows (if done correctly).

You might also consider, if possible, creating a view against the original table that also includes the new column (and value).  Export the view data using BCP.  Then, drop and recreate the table with the new column and use BCP to import.  You could probably take advantage of being minimally logged.

Many popular databases offer a free version for non-production use. If you want to understand the performance difference your best bet is to run tests on the most common platforms that you expect your clients to use.


Well, almost universally, any wait that is a queue or timing wait (especially one with QUEUE in the name), can largely be ignored. While this one is not explicitly listed in Paul Randal's list of waits to ignore, it may be more that this wait is rare than because it shouldn't be ignored. Across all of the instances uploading SQL Sentry wait data to the cloud, this wait type has yet to be encountered (compare that to, say, LCK_M_SCH_M, a much more frequently observed wait type - seen on close to 1% of instances).

With the combination of:

there are no blog posts about this wait type (that I could find);
this is the first time it has been mentioned on this site or on Stack Overflow; and,
Microsoft hasn't even bothered to say anything about it beyond "TBD"...

...leads me to believe this is an innocuous wait that you shouldn't be concerned about.

And this is one of the problems I have with using a waits methodology for finding "problems" - is there any actual performance-related symptom involved with these waits? You're always going to have a wait that is the highest; that doesn't mean it's a problem. Now, if you can associate this wait type with observable performance symptoms in something related (such as reading from or writing to a filestream container), there might be more to investigate.

Also, are you sure you are accurately translating to minutes? There have only been 7,200 minutes in the past 5 days, so I'm not sure how this wait type could have registered more than double that. I already feel this wait type can't be anything more than a timer, which means it's simply accumulating benign wait time, and I can't imagine this also happens in parallel. So maybe the minutes you quote are actually seconds or some other unit.


How can I disallow restore from backup on SQL Server?

Quick answer: You can't.

Physical access to the backup file will allow someone to restore it. The password feature isn't secure and will be removed anyway. See "Security Considerations for Backup and Restore" on MSDN

To prevent someone running RESTORE and destroying a working database, then ensure that only the correct folk have permissions to do this


Appending data from one program to the other using batch commands

Here's an example of how you can do this in shell script (bash or ksh for instance). Here's simple SQL script to test, it turns off (most?) of the usually undesired output from SQL*Plus:

set feedback off
set echo off
set heading off
set pages 0
whenever sqlerror exit 4
whenever oserror exit 8
select status from v$instance;
exit

You can run it like this, assuming your environment is set up properly (-s suppresses the banners and prompts):

result=$(sqlplus -s user/pass @script.sql)
if [ $? -eq 0 ] ; then
  # the script worked
  echo "$result"
else
  # something went wrong
  ...
fi

If you want to process multiple lines, you can use a while/read loop, with process substitution. Something like:

while read -r line ; do
   echo "Got: $line"
done < <(sqlplus -s user/pass @script.sql)

Or you can just redirect the output of SQL*Plus to a text file and process that later if it's not convenient in a variable.

On Windows, with cmd.exe scripts, it's all a bit harder, but you can use variants of what is described in this question: Setting a variable from an executable. Something like (not tested):

for /f "tokens=*" %%a in ('sqlplus -s user/pass @script.sql') do set RESULT=%%a

or

for /f "tokens=*" %%a in ('sqlplus -s user/pass @script.sql') do (
  @rem process one line at a time
  echo "%%a"
)

But you might be better off using PowerShell these days. (Which I know nothing about, but a quick google seems to indicate that there are good options for this.)


Using Foreign Key and References for ON DELETE CASCADE in mysql

It is a MySQL "gotcha". The first way does NOT work.

From MySQL docs, FOREIGN KEY Constraints:

  Important:

  ... 4 paragraphs below...

  Furthermore, InnoDB does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. InnoDB accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For other storage engines, MySQL Server parses and ignores foreign key specifications.

Creating the 2 tables (way 1):

CREATE TABLE parent_table (
parent_id int(11) not null auto_increment,
PRIMARY KEY(parent_id)
);

CREATE TABLE child1 (
id int(11) not null auto_increment,
parent_id int(11) REFERENCES parent_table(parent_id) ON DELETE CASCADE,
PRIMARY KEY(id)
);

Lets see what is child1:

> SHOW CREATE TABLE child1 ;

delimiter $$

CREATE TABLE `child1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

Where is the FOREIGN KEY ? ... Gone with the wind (and without warning)

Creating the table child2 (way 2) works fine:

CREATE TABLE child2 (
id int(11) not null auto_increment,
parent_id int(11),
PRIMARY KEY(id),
FOREIGN KEY(parent_id) REFERENCES parent_table(parent_id) ON DELETE CASCADE
);

> SHOW CREATE TABLE child2 ;

delimiter $$

CREATE TABLE `child2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `child2_ibfk_1`
    FOREIGN KEY (`parent_id`) REFERENCES `parent_table` (`parent_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$


How to setup db credentials for `sqlcmd` using a config file?

The odbc.ini for mssql doesn't support the user and password.

Server parameter is used to specify port with syntax [protocol:]Server[,port]

Check /etc/odbcinst.ini for what driver name to use.

$ cat /etc/odbcinst.ini
[ODBC Driver 17 for SQL Server] # must match the 'Driver' in odbc.ini
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1
UsageCount=1

$ cat /etc/odbc.ini
[ExampleDataSourceName]
Driver=ODBC Driver 17 for SQL Server
Database=example_database_name
Server=tcp:10.1.2.3,50001

$ sqlcmd -D -S ExampleDataSourceName -U user


Can SSIS packages call other SSIS packages in the connection string?

The connection string is not calling another SSIS Package. The property Application Name is optional filler text so you can differentiate one connection from another.
Application Name=SSIS-<Transfer-Now-name of other SSIS package>-{8ABA18EE-637E-424F-A3F7-F7E4EA50DD9D}<IP.db_name.user>

If Transfer-Go has the a reference to Transfer-Now in the connection string, the probable cause is that the developer created Transfer-Now and then copy/pasted either the package in whole or at least the Connection Manager between the packages.
The mechanisms for one package to run another package are

Execute Package Task {either as an in-process (synchronous) or out of process (asynchronous)}
Execute SQL Task
Execute Process Task

The later two options will have you either running a predefined Agent Job or building out the appropriate commands to dtexec


As documented in SET QUOTED_IDENTIFIER (Transact-SQL)

  SET QUOTED_IDENTIFIER must be ON when you invoke XML data type
  methods.

A simple test

set quoted_identifier off

DECLARE @xmlRecords XML
SET     @xmlRecords = '<records><record orderId="1" refCode="1234"></record></records>'

SELECT  records.record.value('(@orderId)[1]', 'INT') AS orderId
FROM    @xmlRecords.nodes('/records/record') records(record)

  Msg 1934, Level 16, State 1, Line 8 SELECT failed because the
  following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
  Verify that SET options are correct for use with indexed views and/or
  indexes on computed columns and/or query notifications and/or xml data
  type methods.

You are using FOR XML PATH

SET QUOTED_IDENTIFIER off

DECLARE @T TABLE (id VARCHAR(5),col1 XML)

INSERT INTO @t (id,col1) VALUES ('1','<node1>one</node1>')

SELECT ISNULL(STUFF((
                SELECT ', ' + id
                FROM @t
                FOR XML PATH('')
                    ,TYPE
                ).value('.', 'NVARCHAR(MAX)'), 1, 2, ' '), '') UNAMESLIST

  Msg 1934, Level 16, State 1, Line 8 SELECT failed because the
  following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
  Verify that SET options are correct for use with indexed views and/or
  indexes on computed columns and/or query notifications and/or xml data
  type methods.


Mysql setting a record as deleted or archive

You basically have two choices.  You could use a view to do the filtering though this has some performance issues on MySQL, or you could use a trigger to copy deleted rows into an archive table.  I suspect that on MySQL, the trigger solution will be much better (on other DB's the tradeoffs may go other ways.


What is the permission needed to add permissions?

To change permissions within the restored database the account which the automation is using will need to be a member of the securityadmin fixed server role.  That'll give you rights to create a user within the database and give that user permissions within the database.

Using sp_change_users_login requires that the login which is running it be a member of the db_owners fixed database role within the database you are running it.


Why we can FLASHBACK DROP table where it is DDL command and hence autocommited?

We need to understand how DB performs both the operations. Database in itself maintains record for each table. For simplicity, you can consider a database as a table, and all the tables information of database as data of that table.

So now when you DROP a table (which actually deletes the data and structure of table), Database doesn't delete the entry of that table and it maintains an entry of that table (in other words, sort of a soft delete operation). When you PURGE, then only database start looking for those table entries which are marked as soft delete and start deleting them permanently. Hence when it is purge (or permanently deleted) then you cannot recover it.

OTOH when you execute a TRUNCATION query, then it deletes the data but it is also combined with PURGE command. So when you TRUNCATE then it removes the data permanently.

In simple words, you can relate this with example of DEL and Shift+Del. When you DEL a file, then it goes to RecycleBin and you can recover it from there. But when you delete a file with Shift+Del then it permanently deletes. I hope it helps.


How can I take backup MySQL database from one cloud server to another?

Clouds can't talk to each other

There is a 3rd machine that can access both of them, correct -- your machine.

your> mysqldump -h cloud1 ... >somefile.sql
your> mysql     -h cloud2 ... <somefile.sql

Or, to be more daring:

your> mysqldump -h cloud1 ...  |
      mysql     -h cloud2 ...

Then you aren't even touching your disk.

Performance will be determined by the bandwidth between you and each cloud.  Adding a gzip in the path won't help because it won't be compressed until it gets to your machine.  Percona's Xtrabackup is a good alternative to mysqldump.  It has compression options and even some parallelism, but, again, you may not be able to take advantage of such.

I avoided the obvious (having one cloud send to the other) because I expect such traffic is forbidden.  Or you can't run commands from a shell script on either cloud.

Connection possible

On either cloud machine, do

mysqldump -h cloud1 ...  |  mysql -h cloud2 ...

If the network is the slow part, then do this on the source machine

mysqldump ... | gzip  |  ssh cloud2 ... "gunzip | mysql"

(I forget the details of how to use ssh to execute the stuff over yonder.)

That could be reversed to run on cloud2:

ssh cloud1 "mysqldump ... | gzip" | gunzip | mysql ...

but it may not matter much.


Oracle 11 IMPDP Table Exists but gets Does Not Exist Error

It looks like a permission issue. As Raj pointed out make sure that export_user has select privilege for the accounts table. Which it will have if it owns the table. Also make sure that it has select, insert, update and delete for MIKETESTCONSOLE_OWNER.ACCOUNTDEPLOYMENTLINKS and all of the tables in that schema. If tables are added over time, you probably need to add grants.


Structure of database with billions of rows of data

This is a typical way to deal with this kind of data.

If you had a postgres database, you'd create one master table (to formalize the structure of the sensor data), and then create tables that inherit all the columns from the master table without adding any additional columns. This would create a partitioning of your data based on date / timestamp of the sensor data.

Querying the master table in this configuration means going through all the child tables and returning the approapriate answers. If one adds the "check" constraints for the child tables (corresponding to the temporal data in the tables), only the relevant children will be scanned.

See more e.g., https://www.postgresql.org/docs/10/static/ddl-partitioning.html

SQL Server 2014 seems to have a similar concept:

https://docs.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes


PostgreSQL: cache, disk, replication. What order?

PostgreSQL doesn't have a result cache. So the only caching that happens is in shared_buffers and the kernel buffer cache - both of which must inhertently be filled in the process of generating the query results, so it must happen before  (or while) results are sent.

WAL is written as a transaction executes, and WAL archives get replicated as they're filled. WAL records also get streamed over the streaming replication protocol as they're generated. So replication occurs during the execution of a query, but it's not as simple as "before" or "after" results are sent.

Results can be sent incrementally to the client. Replication can occur incrementally. Really, both happen at the same time.

The only guarantee is that a commit will not be replicated and applied to replicas before it commits on the master.

If you're using synchronous replication there is the additional guarantee that a committed transaction is replicated to all nodes before a successful commit is reported to the client.

I really think you're asking the wrong question here, though. You're making a bunch of assumptions and asking questions based on those assumptions. It would be helpful if you'd take a step back, to the why behind these questions, and explain what you are trying to achieve with this. What's your goal? What're your requirements? What outcome do you seek?


Insert into int8range from columns of another table

It's so simple:

INSERT INTO indexces_grid ("Row2")
SELECT int8range(ipfrom, ipto, '[]')
  FROM citydetails;

The documentation linked above tells you all.

Note that one has to use the three-parameter form of the range constructor function (here int8range()) to include the upper bound, too.  Using the two-parameter form, the upper bound would be non-inclusive.

I've created an SQLFiddle to show this at work.


How can I modify an existing column into a foregin key in postgres?

First make sure you have no duplicate agency_names, or you may have problems.

SELECT agency_name
FROM agency
GROUP BY agency_name
HAVING count(*) > 1;

Make sure your primary key is set on agency,

ALTER TABLE agency
  ADD PRIMARY KEY (agency_id);

Then wrap the modifications all in a transaction.

BEGIN;

    ALTER TABLE employee
      ADD COLUMN agency_id int
      REFERENCES agency;
    UPDATE employee
      SET agency_id = a.agency_id
      FROM agency AS a
      WHERE employee.agency = a.agency_name;
    ALTER TABLE employee
      DROP COLUMN agency;

COMMIT;

Output,

TABLE employee ;
 employee_id | first_name | agency_id
-------------+------------+-----------
          16 | Paul       |         1
(1 row)

Note I assumed you wanted the primary key on the agency_id based on the wording of the question. You can, as @Craig Ringer points out, create an FKEY using the text of the column too. That column will still have to be unique -- see the above. Doing so would look like this,

CREATE UNIQUE INDEX
  ON agency (agency_name);
ALTER TABLE employee
  ADD FOREIGN KEY (agency)
  REFERENCES agency(agency_name);


How can i select columns from two tables using REF

you can do a simple join

select t.*,s.*
from Teacher t
join Teach_In r on r.teacher = ref(t)
join School s on r.school = ref(s)
;


Selecting from random amount of tables with identical structure

I am not proud of publicly writing something like this.

Sample + data:

CREATE TABLE donaldduck ( SURNAME VARCHAR(100), TOWN VARCHAR(100) );
CREATE TABLE daisyduck  ( SURNAME VARCHAR(100), TOWN VARCHAR(100) );
CREATE TABLE goofy      ( something number(1), SURNAME VARCHAR(100), TOWN VARCHAR(100) );

INSERT INTO DONALDDUCK ( SURNAME, TOWN ) VALUES ( 'Dagobert Duck', 'Entenhausen' );
INSERT INTO daisyduck ( SURNAME, TOWN ) VALUES ( 'Daisy Duck', 'Entenhausen' );
INSERT INTO goofy ( SOMETHING, SURNAME, TOWN ) VALUES (   1, 'Goofy Dog', 'Entenhausen' );
commit;

Types and PL/SQL using a pipelined function:

create type t_NAME_OF_USER as object
(
    SURNAME VARCHAR(100),
    TOWN VARCHAR(100)
);
/

create type t_name_of_user_tab IS TABLE OF t_NAME_OF_USER;
/

CREATE OR REPLACE FUNCTION get_surname_town RETURN t_name_of_user_tab PIPELINED AS
  rc sys_refcursor;
  query clob;
  l_surname varchar2(100);
  l_town varchar2(100);
begin
  for t in (
    select table_name from user_tables ut
    where
      'SURNAME' in (select column_name from user_tab_columns utc where utc.table_name = ut.table_name) and
      'TOWN'  in (select column_name from user_tab_columns utc where utc.table_name = ut.table_name)
    )
  loop
    open rc for 'select surname, town from ' || t.table_name ;
    loop
      fetch rc into l_surname, l_town;
      exit when rc%notfound;
      pipe row(t_NAME_OF_USER(l_surname, l_town));
    end loop;
  end loop;
end;
/

SELECT:

select * from table(get_surname_town);

SURNAME                        TOWN
------------------------------ --------------------
Daisy Duck                     Entenhausen
Dagobert Duck                  Entenhausen
Goofy Dog                      Entenhausen


How to set SSIS flat file connection row delimiter to blank line?

You can't specify a ROW (even empty) as a row delimiter. The problem is not really with the flat file connection. Actually there is another problem here than just row delimiter - you need to combine multiple rows of input and "pivot" them to create one row in the output table.
This calls for multiple steps in your package:

create a temp table with one col to hold the input file.
input the raw data into that temp table.
write some SQL code (i would encapsulate it in a stored proc) to process the raw input and build rows of data. this needs to be done in using a cursor. you loop thru each input and build a record until you reach an empty input. then you write out as output. continue until no more records in temp table.

Hope this helps.


index does not get used when there is "OR" in the query

In general PostgreSQL is capable of using an index in this situation.  It can scan the index two times and combine them with a BitmapOr.

The reason it cannot or has chosen not to do this in your particular case would depend on the particulars of your case, which you are not sharing with us.  Perhaps it just thinks the alternative plan will be cheaper.


Does MariaDB require aliases for subquery table locks?

Really peculiar behaviour, unfortunate this limitation still exists atleast in my version of MariaDB:

MariaDB [test]> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.0.23-MariaDB |
+-----------------+
1 row in set (0.00 sec)

MariaDB [test]> create table t ( x int not null );
Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> lock tables t read;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from t t1, t t2;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Locking the aliases does not seem to help

MariaDB [test]> lock tables t read, t1 read, t2 read;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from t t1, t t2;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Weird indeed.


querying a JSOn object in separate columns from Postgres table

You can extract each array element using the->> operator:
select request_id,
       job_info ->> 0 as cluster_1,
       job_info ->> 1 as cluster_2
from requests_table
where cast(job_id as varchar) like '%4665-b583-3b92218db8ce%'


Time out while connecting to the database. Current time out value is 30 seconds

Better use TNSNAMES.ORA file in your client. You can set Instant Client's timeout in SQLNET.ORA file

%ORACLE_HOME%\admin\network\

ORACLE_HOME is where your Instant Client binaries are and should be already added to your local environment variables.

If you don't know what Instant Client is, it is a binary dll that provides connection, execution and fetching capabilities over network. sqldeveloper has a lite one inside it's "installation" (read unzipped) folder, but you either have to reference to that manually, or install a proper one yourself. It can be downloaded from Oracle.com


If you want to use the CONTAINS or FREETEXT you need to create a FULL TEXT CATALOG and create a FULL TEXT INDEX on the column. However, you can use the LIKE statement to validate if a column "contains" a specific string.

SELECT FirstName, SecondName FROM Persons WHERE FirstName LIKE '%Mat%'

You must be aware that the LIKE is slower than the contains but need less configuration.
If you want to use the FULL TEXT CATALOG, you can use this link.

Hope this help you.


When you have a query that has multiple criteria on the same table a compound index is needed. MySQL will only use a single index for a table (or alias) so make sure its the right one.

First look at discovery since its on the left of the join. Start with items referenced and then ranges. So:

provider
event_datetime

Elements like resource because of the LIKE %x% expression can't use and index. For a small table we relay that (provider,event_datetime) restruct the table small enough to be be significant about including resource in the index.

So:

CREATE INDEX provider_event_datetime ON discovery (provider,event_datetime);

Note: is whitelisted.provider need to be the same as the discovery_provider?

A limit on the inner query is quite unpredictable without an ORDER BY.

The remaining join on logs is looking it up by the PK. No additional indexing is required there.

Look at EXPLAIN {query} to examine what indexes it is using and how.


MySQL Performance Gains By Splitting Tables?

I'd vote for #2. Will you not need a item_price and a quantity in the user_purchased table? (for which order_details looks a better name, by the way.) Or other, extra columns for the user_basket table? I can easily see how the table will get wide, with a lot of nullable columns. MySQL has no CHECK constraint so how do you plan to enforce integrity?

And why have both kind of queries, the ones that search the Basket and the ones that search the Purchased, all trying to access one table, when you can avoid it?

Other advantages:

Narrower tables, which is usually good for indexing.
One of the 2 tables (the user_purchased) will have only INSERT operations and rarely DELETE or UPDATE and will be growing, so you will not have fragmentation there. With one table for all, which will be affected by all kind of operations, it will be hard to avoid fragmentation.


Maintenance plan is backing up database transaction log but it is not selected

Your radio button is on All user databases and NOT on These Databases


How to troubleshoot SQL Server Merge Replication Conflict?

You need to inspect the Conflict Viewer and observe the conflict types and the conflicting row values.  Something to note is that the Conflict Winner column will display the winning row values which are pulled from the base tables.  If you think about it, these values are volatile and can change, so the values you see in the Conflict Winner column may not be the values they were when the conflict occurred, keep this in mind.  The Conflict Loser column will display the losing row values which are pulled from the MSmerge_conflict_publication_article table and these values are static.

If you are unable to determine the root cause of the conflicts based on the Conflict Viewer, you may need to implement an auditing scheme.  I have provided an example of how to do this in Auditing Changes in Merge Replication.

There is a small chance you are getting false conflicts but auditing should reveal whether or not that is the case.


BEGIN UPDATE "item"
    SET "score" = (select AVG("vote"."score") FROM "vote")

Use it as follows:

create or replace function test (id int) returns void as
$$
begin
update employees
set commission_pct=(select min(commission_pct) from employees)
where employee_id= $1;
end
$$ LANGUAGE plpgsql VOLATILE;

select test(198); --call function

The process of aggregate functions is calculated after getting data set from data in table, we cannot use any calculating without selecting data from table first. In the above function is update column score* to AVG(score) but there is not data-set to calculate the AVG(score).


What ISN'T included in SQL Server snapshot replication?

I know this might not be a full answer. But this is too long to put in a comment.

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/publish-data-and-database-objects

Publishing User-Defined Types and Alias Data Types

  Columns that use user-defined types or alias data types are replicated
  to Subscribers like other columns. The CREATE TYPEstatement for each
  replicated type is executed at the Subscriber before the table is
  created. In the case of user-defined types, the associated assembly is
  also copied to each Subscriber. Changes to user-defined types and
  alias data types are not replicated to Subscribers.

  If a type is defined in a database, but it is not referenced in any
  columns when a publication is created, the type is not copied to
  Subscribers. If you subsequently create a column of that type in the
  database and want to replicate it, you must first manually copy the
  type (and the associated assembly for a user-defined type) to each
  Subscriber.

What CANNOT be replicated in SQL Server?

Anything not listed here cannot be replicated.


You create the fuzzystrmatch extension, but you never use it.  Are you missing an example?

This type of query is very context sensitive, so you have to test it on realistic example data with realistic queries.  Your example doesn't look very realistic.  For one thing, your random string is the same random string in every row, not a different random one for each.  Also, having small integers embedded in your names seem pretty odd.

Your query of '120000' also doesn't seem very realistic, or if it is then it is pretty unfortunate.  Due to the repetition of zeros, it is a low complexity query and doesn't give the query engine much to grab on to.  If you are worried about the average case rather than the worst case, it is a poor choice of example.

Using the "%" operator on strings of quite unequal length is rarely the right thing to do.  Perhaps you want "%>" or "%>>" instead. There were added to pg_trgm in 9.6 and 11, respectively.  They measure how similar the query is to the most similar substring chunk of the column, rather than the whole of the column. They differ in that %>> requires the "chunk" to be aligned at word boundaries, while %> does not.  Not only do they give what I think are better results, but they are also much faster (in this case, I don't know about in general).

Finally, your query would be much faster if you increased the setting of pg_trgm.similarity_threshold.  Of course, then you also get no results, because none of your data is really all that similar to '120000'.  This goes back to the choice of operator, as the presence of 'CLXBHQVEKC' drags down how similar anything can be when using "%".


Restore from SQL Server 2014 Enterprise to BI

You can restore it if you're not using any Enterprise Edition features.

In this case, you are using table partitioning so that restore path is not supported.

You can run:

SELECT *
FROM sys.dm_db_persisted_sku_features;

...to check for other features that can block your cross-edition restore.

Consider using Developer Edition as your test/development platform. It is very cheap and has all of the features of Enterprise Edition.


Entries count by date is giving wrong results

Looks like StockUpdated has a time part that makes the query not count the values on 2016-12-31 with time other than 00:00.

Try a non inclusive comparison instead. StockUpdated < '2017-01-01'


Does binlog_group_commit_sync_delay affect existing binary logging in MySQL

Existing logs are not affected. MySQL won't read and modify existing logs, it would be incredibly expensive and risky. So, yes, you'll see the benefit of this in 2 days (assuming the lag won't increase or decrease).
To me, it sounds like you are trying random optimisations to reduce the lag. If I'm correct, I recommend to investigate the reasons for slowness before making changes. Once you have an idea to make the slave faster, change a single variable, see if your hypothesis was correct, and if not undo the change.
If solving the problem is urgent, there are some things you can do to make a replica faster, like setting sync_bilog=0 and/or innodb_flush_log_at_trx_commit=0. In most cases you'd see the replica start to catch up. However, note that this is risky: if your replica crashes you will lose data. Depending on how unlucky you are, you may have to import a new backup from the source.


Phil has an interesting test case. But the dual table has one column and one row with the value x. When you select NULL from dual you are selecting a literal from a table. The literal is not actually in the table. That you can select sysdate from dual does not mean
that sysdate is in the dual table either.

SQL> select sysdate from dual;

SYSDATE
---------------
23-MAY-14


How can I load csv data into mongodb and choose the types I'd like to have created for each column?

Ensure you're using mongoimport version 3.4 or higher, and use the --columnsHaveTypes parameter to tell the import what type each column is.

See the MongoDB Reference Documentation for details.

The following types can be specified:

auto()
binary(<arg>)
boolean()
date(<arg>)
date_go(<arg>)
date_ms(<arg>)
date_oracle(<arg>)
decimal()
double()
int32()
int64()
string()

Consider the following CSV data:

Katherine Gray, 1996-02-03, F, 1235, TG9yZW0gaXBzdW0gZG9sb3Igc2l0IGFtZXQsIGNvbnNlY3RldHVyIGFkaXBpc2NpbmcgZWxpdCwgc2VkIGRvIGVpdXNtb2QgdGVtcG9yIGluY2lkaWR1bnQgdXQgbGFib3JlIGV0IGRvbG9yZSBtYWduYSBhbGlxdWEuIFV0IGVuaW0gYWQgbWluaW0gdmVuaWFtLCBxdWlzIG5vc3RydWQgZXhlcmNpdGF0aW9uIHVsbGFtY28gbGFib3JpcyBuaXNpIHV0IGFsaXF1aXAgZXggZWEgY29tbW9kbyBjb25zZXF1YXQuIER1aXMgYXV0ZSBpcnVyZSBkb2xvciBpbiByZXByZWhlbmRlcml0IGluIHZvbHVwdGF0ZSB2ZWxpdCBlc3NlIGNpbGx1bSBkb2xvcmUgZXUgZnVnaWF0IG51bGxhIHBhcmlhdHVyLiBFeGNlcHRldXIgc2ludCBvY2NhZWNhdCBjdXBpZGF0YXQgbm9uIHByb2lkZW50LCBzdW50IGluIGN1bHBhIHF1aSBvZmZpY2lhIGRlc2VydW50IG1vbGxpdCBhbmltIGlkIGVzdCBsYWJvcnVtLg==
Albert Gilbert, 1992-04-24, T, 13, Q3VwY2FrZSBpcHN1bSBkb2xvciBzaXQgYW1ldCB0b290c2llIHJvbGwgYm9uYm9uIHRvZmZlZS4gQ2FuZHkgY2FuZXMgcGllIGNyb2lzc2FudCBjaG9jb2xhdGUgYmFyIGxvbGxpcG9wIGJlYXIgY2xhdyBtYWNhcm9vbi4gU3dlZXQgcm9sbCBjdXBjYWtlIGNoZWVzZWNha2Ugc291ZmZsw6kgYnJvd25pZSBpY2UgY3JlYW0uIEp1anViZXMgY2FrZSBjdXBjYWtlIG1hY2Fyb29uIGRhbmlzaCBqZWxseS1vIHNvdWZmbMOpLiBDYWtlIGFwcGxlIHBpZSBnaW5nZXJicmVhZCBjaG9jb2xhdGUgc3VnYXIgcGx1bS4gU3dlZXQgY2hvY29sYXRlIGNha2UgY2hvY29sYXRlIGNha2UganVqdWJlcyB0aXJhbWlzdSBvYXQgY2FrZS4gU3dlZXQgc291ZmZsw6kgY2hvY29sYXRlLiBMaXF1b3JpY2UgY290dG9uIGNhbmR5IGNob2NvbGF0ZSBtYXJzaG1hbGxvdy4gSmVsbHkgY29va2llIGNha2UgamVsbHkgYm

The --fields option specifies which field type mongoimport will use when importing the data into MongoDB (command-line wrapped for readability):

mongoimport --db users --collection contacts --type csv
  --columnsHaveTypes --fields "name.string(),birthdate.date(2006-01-02),contacted.boolean(),followerCount.int32(),user thumbnail.binary(base64)"
  --file /example/file.csv


Find the number of rows in a table from metadata

For SybaseIQ 16 (SAP IQ 16.0 SP08) use system viewSYSTAB as:

select * from SYSTAB

Here is a link to SyBase IQ v16 documentation. Page 918 (correction to my comment above) says:

  Each row of the SYSTAB system view describes one table or view in the
  database. Additional information for views  can be found in the
  SYSVIEW system view. The underlying system table for this view is
  ISYSTAB.

  Column:      count     Datatype:    UNSIGNED BIGINT     Description:
  The number of rows in the table or mate rialized view. This value is
  updated during each successful checkpoint. This number is used to
  optimize database access. The count is always 0 for a
  non-materialized view or remote table.


Make sure date of departure is different than date of arrival?

Make a BEFORE INSERT TRIGGER, which checks in case of an Inserting a row for that flight and Airport the condition is met.

This is an example, you have ti check the conditions and error message if the condition is not met.

DELIMITER $$

CREATE TRIGGER before_Departures_Arrivals_insert
BEFORE INSERT
ON Departures_Arrivals FOR EACH ROW
BEGIN
    DECLARE _date  DATETIME;
    DECLARE _tyoe  INTEGER;

    IF NEW.tyoe = 1 THEN
        SET _type = 0;
    ELSE
        SET _type = 1;
    END IF;

    SELECT `date`
    INTO _date
    FROM Departures_Arrivals
    WHERE id_flight = NEW.id_flight AND  id_airport = NEW.id_airport
            AND type = _type
    ORDER BY `date` DESC
    LIMIT 1;

    IF NEW.`date` > _date THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = "The new date is before the arrival";
    END IF;

END $$

DELIMITER ;


how to update date field in a record to null?

Use one of the suggested formats in MySQL documentation, for datetime literals, like the 'YYYY-MM-DD HH:MM:SS' format:

WHERE validity = '1947-05-18 00:00:00' ;

or the simpler (in this case, that there is no time part):

WHERE validity = '1947-05-18' ;


Select number of times 2 specific values show up for one record id

You can do this in two different ways:

1) Use EXISTS

SELECT
    Order_Number
FROM
    (-- Get the list of Order_Number
    SELECT DISTINCT
        Order_Number
    FROM
        Orders
    ) AS onr
WHERE
    -- Check that there is an 'abc' associated
    EXISTS
    (
    SELECT *
    FROM Orders os1
    WHERE os1.Order_Number = onr.Order_Number and os1.SKU = 'abc'
    )
    AND
    -- and also Check that there is an 'ghi' associated
    EXISTS
    (
    SELECT *
    FROM Orders os2
    WHERE os2.Order_Number = onr.Order_Number and os2.SKU = 'ghi'
  )
ORDER BY
    onr.Order_Number
GO

| Order_Number |
| -----------: |
|            1 |
|            2 |
|            4 |

2) Substitute the EXISTS by equivalent JOINS with join condition including the desired value of SKU:

SELECT
    onr.Order_Number
FROM
    (SELECT DISTINCT
        Order_Number
    FROM
        Orders
    ) AS onr
    JOIN Orders AS sk1 ON sk1.Order_Number = onr.Order_Number AND sk1.SKU = 'abc'
    JOIN Orders AS sk2 ON sk2.Order_Number = onr.Order_Number AND sk2.SKU = 'ghi'
ORDER BY
    onr.Order_Number
GO

You get exactly the same result. Which version you use depends on which one you find clearer to understand. If your tables are really big, test whether one gives a better execution plan.

You can check all the statments at dbfiddle here


Can't access MySQL after sourcing a database and reboot

Oh, you nailed it!

It seems that my dump actually contained mysql.user data, so that after loading the database, I could only connect with these distant login information.
Taking a look at this mysql.user table, it appears that these login were specified, not for localhost, but the distant host.

Anyway... Knowing this, I've  been able to connect to mysql client, then reset these login info :

mysql –u root –p [distant password]
use mysql.user UPDATE mysql.user
SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;
quit

I finally deleted these distant host lines from phpmyadmin, to be sure not to mess anything up.

Thanks a lot for your help !


In what order does PostgreSQL check object permissions?

The problem in that case is not exactly about permission order, but execution order.

In resume, for PostgreSQL :

1- Views that are acessing tables will override the tables permission

2- Views acessing functions, will need to evaluate all the functions, before being checked - so the functions must be executed before acessing the view, even if the view does not has select permissions...

How can we prove that?

In postgresql, views are able to give you permissions for doing a select in a table, even if the user don't have this permissions.

For example:

create view view2 as select * from table1;
revoke all on table1 from user1;
grant select on view2 to user1;

Log in as user 1:

select * from table1 (permission denied)
select * from view2 (sucess - the query executes)

In the case, the user will be able to select view2 even don't having permission to select the table.

But what if we do the same thing with a function? The behavior are NOT the same. Lets create a function that wait 5 seconds before returning 1 (so we can debug if postgresql is running the function everytime that we call the view)

CREATE OR REPLACE FUNCTION something() RETURNS integer
AS 'select 1 from pg_sleep(5);'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT; --this function will delay 5 seconds

create view view1 as select * from something();
revoke all on function something() from user1;
grant select on view1 to user1;

Log in as user 1:

select * from something(); (permission denied for something)
select * from view1 (permission denied for something )

The permission to do select on the view do not overrides the function permission, and even worst if we revoke the permissions from view1, the message still shows that postgresql stopped our query because of the function, no matter what the permission of the view.. (that is exactly what is happening in the question)

But is the function really being checked first? If we give the 'all' permissions to the function, but revoke the view permission...

grant all on function something to user1;
revoke all on view1 from user1;
select * from view1;
Delayed 5 seconds... (the function executed!)
Permission denied for select on view1

As you see postgresql WAITED 5 SECONDS before saying that we dont have permission to output the view, showing that the "something()" function are executed. So the function data return must exist before the check of the view.

So now with this tests, we now know that PostgreSQL needed to first evaluate all the functions before continuing our query, is just like the query still does not exist until all the functions envolved are fully completed, so the view cant be solved for postgresql to know if we have or not permission to select it.

I think this answer your question in the terms of "permission order", but why does postgresql needs to evaluate all the functions before continuing, thats is another question...


Unsent log behaviour in database mirroring

So my question would be , is this suppose to happen and block unsent log on P till all previous 200 GB is restored on Mirror or there could be different problem here? And can we again PAUSE and RESUME mirroring if mirroring is still synchronizing ?

You might be experiencing a network issue. The PAUSE and RESUME of mirroring should just be your workaround.

Check with your network admin on your network bandwidth and latency (shared network with other apps? 1Mbps NIC?). This can build up your Unsent Log and can adversely impact performance of your database mirroring. 200GB is huge amount of data. You could loss 200GB data. You don't want that to happen. Consider setting up Threshold Alert for Log Send Queue KB. If it reaches 1GB, send an email alert and take action. This way you can minimize your data loss.

Read this KB Article on things to consider when setting up Database Mirroring in SQL Server. The 2 main important points about network:

  To mitigate the impact of network latency on the database mirroring throughput, you should consider the following:

  Gigabit network cards
  Dedicated NICs for mirroring.

Don't wait for 10hrs or 200GB of unsent data, setup an alert and be proactive.

Additionally, consider migrating/upgrade to SQL Server 2016/2017. Use Availability Groups (better than mirroring and faster) because Database Mirroring is now deprecated by Microsoft and you won't be able to get support or updates from them (except you paid for extended support). If you run into Database Mirroring bug it is unlikely Microsoft will patch it for you. You can read the SQL Server 2008 lifecycle support here.


Indexes, their various types and internal structures, are described in the documentation.


Microsoft SQL Server 2016 Service Pack 1 (SP1) support for TDE (transparent data encryption)

The documentation Editions and supported features of SQL Server 2016 was updated to cover what is, and what is not, added in SQL Server 2016 Service Pack 1.
Transparent Data Encryption is still an Enterprise-only feature.

You can find more details on the official SQL Server Release Services blog. The matrix there shows all new additions in SP1, to enable a common programming surface. Reproduced below in case the link stops working.

The following table compares the list of features which were only available in Enterprise edition which are now enabled in Standard, Web, Express, and LocalDB editions with SQL Server 2016 SP1. This consistent programmatically surface area allows developers and ISVs to develop and build applications leveraging the following features which can be deployed against any edition of SQL Server installed in the customer environment. The scale and high availability limits do not change, and remain as–is for lower editions


This is a simple backup and restore process, with the additional step of making sure you backup the Reporting Services key, so you can restore that as well.

A number of links on the internet, but this one is a step by step guide with screenshots,  much easier than trying to follow MSDN

http://www.mssqltips.com/sqlservertip/2692/migrating-sql-reporting-services-to-a-new-server-by-moving-the-reporting-services-databases

or if you are only talking a small number of reports and you want to change the credentials,etc you could simply import the .rdl definition files from SourceControl (hopefully :) )

If the 2 domains can authenticate with one another, then the current credentials will work. otherwise you will need to change the account used.


Is it possible to audit query results in SQL Server?

You could (a) force data access through a stored procedure, then (b) that stored procedure would dump the results to a #temp table before returning them to the user, then (c) the procedure would log the results to some background auditing table.

You'd want to rotate that though because it's going to get very big very fast, not to mention this will not exactly make queries faster. - aaron-bertrand


I wasn't able to use wiredTiger upgrading MongoDB. However, at May 17 I uninstalled MongoDB 2.6 then installed MongoDB 3.0.3 . Immediate after the installation, I added storageEngine=wiredTiger on top of my mongod.conf file. Then I gave sudo service mongod start command and eventually I could.

Edit:

For fresh installed as directed by official documentation;

Open configuration file using sudo nano /etc/mongod.conf
Change the #  engine line to engine: wiredTiger like the below
Run mongod using the command sudo service mongod start

.

# Where and how to store data.
storage:
  dbPath: /var/lib/mongodb
  journal:
    enabled: true
  engine: wiredTiger
#  mmapv1:
#  wiredTiger:

Edit:

If the current version is supported the wiredTiger;

Get the backup of the current database using mongodump command
Stop the mongod service using sudo service mongod stop command
Add storageEngine=wiredTiger text as the first line of mongod.conf file
Delete the all file on /var/lib/mongodb (or /data/db folder if used)
[This is important. Because MongoDB cannot convert the current MMAP db files to wiredTiger format]
Start the mongod service using sudo service mongod start command
Restore the database from the backup using [mongorestore][2] command
wiredTiger is being used...


If you expect your InnoDB data to grow, you should prepare for it by having a larger InnoDB Buffer Pool.

Where does the idea of having additional 10% extra space for the Buffer Pool come from ???

Please note the following diagram of the InnoDB Architecture

Look in the upper left hand corner where you see the buffer pool. There is a section of it called the "Insert Buffer Part". The purpose of the Insert Buffer is to populate changes to non-unique indexes into the Insert Buffer inside the system tablespace file (a.k.a. ibdata1).

From the diagram, up to 50% of the Buffer Pool can be used to manage the Insert Buffer. That being the case, it would be in your best interests to assign a lot more memory to the Buffer Pool. With a data set of 60M, 256M would be a great place to start,


Accessing the insert statement which triggers a trigger inside the trigger's body

You can meet all your requirements with Standard Database Auditing or with Fine-Grained Auditing.

For the standard auditing to also capture the SQL statements, you would set AUDIT_TRAIL initialization parameter to DB,EXTENDED or XML,EXTENDED (See "Settings for the AUDIT_TRAIL Initialization Parameter" in Database Security Guide). The audit records would include timestamps and SQL text, and could be queried from SYS.AUD$ table if AUDIT_TRAIL value is set to DB,EXTENDED or DB, or read from OS files if AUDIT_TRAIL is set to XML,EXTENDED or XML. In the latter case you can determine where the audit files are located based on the value of the initialization parameter AUDIT_FILE_DEST:

SQL> show parameter audit

NAME                 TYPE        VALUE
-------------------- ----------- ------------------------------
audit_file_dest      string      /u01/app/oracle/admin/orcl/adump

As far as FGA is concerned, I have already described it in my other post here. You would just need to set the other value for the audit_trail parameter when creating the FGA with DBMS_FGA.ADD_POLICY procedure, e. g. DBMS_FGA.DB_EXTENDED.


Is splitting tempdb files across drives necessary?

If I have 4 tempdb files split across 2 drives with the log on a third drive, is that necessary/helpful?

I suggest you to understand why we use multiple files for tempdb. Once you understand that clearly and know your underlying storage system (as suggested by comments) you will be able to answer the question. Things might vary depending on your SQL Server Version and major TempDB enhancements are done in SQL Server 2016, 2017 and 2019.

  Tempdb contention refers to a bottleneck for threads trying to access
  allocation pages that are in-memory; it has nothing to do with I/O.

Read more details about this here by Paul Randal.

Microsoft's official recommendation about reducing tempdb allocation contention is here:

Recommendations to reduce allocation contention in SQL Server tempdb database

If one of those tempdb files became corrupt, e.g. file D on drive B, would file A and B on drive A and file C on drive B continue to function, would tempdb 'break'?

I am sure the answer can be depends on the type of corruption. But I do not see any relevance with this question and splitting your files into two LUNs.

I assume SQL would create another tempdb file, based on the temporary nature of tempdb.

SQL Server will NOT automatically create new files, but you can do it yourself. Will it cause downtime?  It depends. Read this question and answer.

Tempdb Add files require Restart
Correctly adding data files to tempdb by Paul Randal


First, you need to make sure MySQL slave user is active and has the needed permission then check if MySQL OS user on the slave machine has the needed access.


Can I restore my SQL Server database to a point before the last full backup with my current backup strategy?

Yes, you could do a point-in-time restore.
You would need the last full backup and all log backups up to the point you need, as log backups do not contain all earlier logs (they are in one sense incremental, rather than differential).
For a week's worth of logs on a busy database, it may take some time to restore. Therefore a better solution (if you want to stick to weekly fulls) is to do a differential backup every day or twice a day.
This means that you only need the last full backup, the last diff backup, and all logs going forward up to whenever you want to restore.

A fairly standard backup cycle on many servers is: full daily, diff 4-6 per day and logs every few minutes. This gives a pretty fast restore time. It also means more importantly, that your maximum data loss is never more than a few minutes, rather than up to an hour.


Better DB design to have a SUM column, or to constantly add up all relationship items per query?

As ever - it depends.

If you have a blisteringly quick DBMS or only a handful of items in each invoice, then totalling them up on the fly would be the cleanest (and, therefore, "best") option.

The "Ivory-Tower Database Purists" would say that you should never store anything that you can derive dynamically (citing Date-of-Birth vs Age).  Realistically, it depends on just how long it takes to do that derivation!

If you have an "ordinary" database, or hundreds or even thousands of items in each invoice and you need that summarised total frequently, then maintaining the summary field at the Invoice level might be the better solution.  [Per-row] Triggers are the way to achieve this, so that no update to any item gets missed.

All that said, if I were in your position, I'd go with on-the-fly totalling ...   at least until the application's performance began to suffer, and then I'd think about "optimising" this aspect of it.


How to check if a value is in an array in a table

Use instead:

DO $do$
BEGIN
   IF EXISTS (
      SELECT FROM hello_world_table
      WHERE  name = 'HelloWorldList'
      AND    'Hello' = ANY (list)
      ) THEN
       RAISE NOTICE 'Found';
   ELSE
      RAISE NOTICE 'Not found';
   END IF;
END $do$;

db<>fiddle here

With a subselect between the parentheses, like you tried, the ANY construct expects a set of element types, not an array. That would work with the column list being type text, not text[].

I can see how this is confusing, as Postgres automatically extracts the single value from a single column in a returned set in most other contexts to match the expected form. Like:

SELECT '{Hello,World}' = (SELECT list FROM hello_world_table WHERE name = 'HelloWorldList');

The untyped string literal '{Hello,World}' is even cast to text[] automatically, deriving the type from the subselect on the other side.

But in this case, the ANY construct is resolved to its alternative form expecting a set, which makes your attempt fail.

You could make it work by unnesting the array to produce the expected set:

SELECT 'Hello' = ANY (SELECT unnest(list) FROM hello_world_table
                      WHERE name = 'HelloWorldList');

But don't. Use an EXISTS subquery instead and move the condition to the WHERE clause like demonstrated.

Related:

How to use ANY instead of IN in a WHERE clause with Rails?
Check if value exists in Postgres array
PL/pgSQL checking if a row exists


Is possible to mysql change some engine tables automatically?

SHOW VARIABLES LIKE `%engine%`;

Of interest are default_storage_engine, which was MyISAM in older versions, but is now InnoDB.  This comes into play when you do CREATE TABLE without including ENGINE=....

Also of interest is enforce_storage_engine.  This strengthens the previous setting by not letting you create a table of Engine other than the default.

When changing (ALTER TABLE .. ENGINE=..) a table from one engine to another, there are some number of potential pitfalls.  I cover MyISAM --> InnoDB in here.

Did you recently upgrade MySQL?  If so, any number of problems can occur if the upgrade failed to run mysql_upgrade.

What version of MySQL are you running?

Please provide the recent contents of mysqld.err.

Please provide ls -ld /tmp

Are you using RoundCube?  The bug seems to be related to their installation.


Assuming that the Ageing values match the names of the columns in your expected output, then this should work:

SELECT  *
FROM    (
    SELECT  BU, Ageing, NetDue
    FROM    [dbo].[vw_FACT_CONSOL_CREDITORS]
    WHERE   Date = @date
) src
PIVOT
(
    SUM(NetDue)
    FOR Ageing IN ([0-30], [31-60], [61-90], [91-120], [121-180], [181-365], [365+])
) piv

If they are not, then you will need to add some logic to the interior select statement to convert Ageing from whatever it is into those string values (e.g. '0-30', '365+').

As an example to check this, I did the following:

CREATE TABLE x (
    BU INT,
    NetDue SMALLMONEY,
    Ageing VARCHAR(10),
    Date DATE
)

INSERT x
VALUES
  (1, 100, '31-60', '2017-04-01'),
  (2, 25.47, '31-60', '2017-04-01'),
  (1, 90.17, '31-60', '2017-05-01'),
  (1, 16.42, '181-365', '2017-04-01'),
  (2, 99.99, '365+', '2017-04-01')

SELECT  *
FROM    (
    SELECT  BU, Ageing, NetDue
    FROM    x
    WHERE   Date = '2017-04-01'
) src
PIVOT
(
    SUM(NetDue)
    FOR Ageing IN ([0-30], [31-60], [61-90], [91-120], [121-180], [181-365], [365+])
) piv

which produced the output:

BU  0-30    31-60   61-90   91-120  121-180 181-365 365+
1   NULL    100.00  NULL    NULL    NULL    16.42   NULL
2   NULL    25.47   NULL    NULL    NULL    NULL    99.99

All the information (and more) for determining this logic can be found here.


Rather than tweaking configs for query optimizing internals, do the following

SUGGESTION #1

You may want to refactor the query as follows

select
    t.id, t.subject,
    count(distinct p.id) as postCount
from thread t left join
(
    select id,thread_id from post where type=0 and
    posted_on >= DATE_SUB(CURDATE(), INTERVAL 3 DAY)
    order by thread_id
) p on t.id = p.thread_id
group by p.thread_id
order by max(p.posted_on) desc
limit 0, 25;

SUGGESTION #2

Create a compound index for the subquery

ALTER TABLE post
    DROP INDEX type,
    ADD INDEX type_posted_on_ndx (type,posted_on)
;

GIVE IT A TRY !!!

ALTERNATE QUERY

select
    t.id, t.subject,
    count(distinct p.id) as postCount
from thread t left join
(
    select id,thread_id from post where type=0 and
    posted_on >= DATE_SUB(CURDATE(), INTERVAL 3 DAY)
    order by thread_id
    limit 0,25
) p on t.id = p.thread_id
group by p.thread_id
order by max(p.posted_on) desc;


The statement you are trying to tune ("select firstName from user;") doesn't have a where clause so it will perform a full table scan.  When firstName is indexed, then a scan of the index will occur.  This will be faster on the basis that the amount of data the engine must read and is substantially less than the entire row.

Most applications are interested in smaller sets of data (i.e., what are the users with a first name of Peter) and not all of the stored data values.  This is where tuning really pays off.  A firstName index would be beneficial to find people with a certain firstName but comes at a cost - inserting, updating and deleting row becomes slower.  You do not want to add indexes indiscriminately.


ORDER BY usage in subquery (derived table) SQL standard allowed or not

A cursor is not a table, it refers to a table or a view of that table.  The cursor has an order only to the extent that the query that defines the cursor has an order by.  A subquery within that larger query would not define the order, and the fact that the query is being used for a cursor doesn’t make an order by in subquery legal syntax.

Basically, as with any other query, if you don’t have an order by on the outermost layer, then the optimizer is free to return the results in what it deems is the easiest/quickest way. It may take advantage of indexes or caches or anything else that allows it to produce the correct result, but is not constrained to do so.


Diagnose slow script executions on specific server

Which version is each server using?
ALTER TABLE has become significantly slower in 8.0.  It probably has to do with the ability to rollback DDLs.
It has never been wise to depend on a lot of DDLs, but this makes it worse.
If this ALTER was being done to a brand new table, it probably would have been better to include the CONSTRAINT as part of the table definition.  Of course, if the ALTER was generated by mysqldump`, this is not practical.  Furthermore, FKs are very picky about order, so you may be stuck.


lock-ddl-per-table option not working as expected in Xtrabackup

How about xtrabackup 2.4.21, which redesigns --lock-ddl-per-table?
See also https://www.percona.com/blog/2020/12/22/redesign-of-lock-ddl-per-table-in-percona-xtrabackup/


Return Bit Value based on previous records by Category

Here is one approach (but please, stop changing your requirements):

DECLARE @x TABLE(MAKEID INT, FAILURES varchar(5), CREATEDATE date);

INSERT @x VALUES
(101,'TRUE ','20151201'),    (101,'FALSE','20151205'),    (101,'TRUE ','20151206'),
(101,'FALSE','20151208'),    (101,'TRUE ','20151210'),    (101,'FALSE','20151215'),
(101,'FALSE','20160101'),    (101,'FALSE','20160102'),    (102,'TRUE ','20151202'),
(102,'TRUE ','20151204'),    (102,'FALSE','20151205'),    (102,'TRUE ','20151206'),
(102,'FALSE','20151207'),    (102,'TRUE ','20160101');

;WITH x AS
(
  SELECT MAKEID, FAILURES,
    -- allow us to identify the last 6 rows:
    rn = ROW_NUMBER() OVER (PARTITION BY MAKEID ORDER BY CREATEDATE DESC)
  FROM @x
)
SELECT MAKEID, FOUROFLASTSIXFAILED = CASE
  -- make sure there have been at least 6 tests - though this may change:
    WHEN COUNT(*) >= 6
  -- make sure *at least* 4 of those have failed *within the last 6*
  -- though this may change (again), too:
    AND COUNT(CASE WHEN rn <= 6 AND FAILURES = 'TRUE' THEN 1 END) >= 4
  THEN 'TRUE' ELSE 'FALSE' END
FROM x
GROUP BY MAKEID
ORDER BY MAKEID;

Results (I think these are right, as opposed to the desired output stated in your question, which seem to conflict with your stated requirements):

MAKEID    FOUROFLASTSIXFAILED
------    -------------------
101       FALSE
102       TRUE

It would seem to me that MAKEID = 102 is the only one that has failed exactly four times out of its last six entries, unless the FAILURES column in the original table really represents NOT_FAILURES.


You can convert the version string to XML and then extract the four parts replacing no value (nulls) with a 0. Put the version back together and compare to your original table.

-- Sample table
declare @T table
(
  Title varchar(20),
  Version varchar(10)
);

-- Sample data
insert into @T(Title, Version) values
('SQL Server', '13'),
('SQL Server', '13.0.0'),
('SQL Server', '13.0.0.0'),
('SQL Server', '13.0.0.175'),
('Visual Studio', '13'),
('Visual Studio', '13.0.0'),
('Visual Studio', '13.0.0.0'),
('Visual Studio', '13.0.0.175');

-- Get the required versions
select *
from @T as T1
where exists (
             select *
             from @T as T2
               -- Convert version to XML
               cross apply (select cast(replace(T2.Version, '.', '<X/>') as xml)) as X(Value)

               -- Extract the values from the XML and build a new version string with 0 instead of null.
               cross apply (select X.Value.value('text()[1]', 'varchar(10)')+'.'+
                                     isnull(X.Value.value('text()[2]', 'varchar(10)'), '0')+'.'+
                                     isnull(X.Value.value('text()[3]', 'varchar(10)'), '0')+'.'+
                                     isnull(X.Value.value('text()[4]', 'varchar(10)'), '0')) as V(Version)
             where  T1.Title = T2.Title and
                    T1.Version = V.Version
             );

-- Or if you want to delete from your original table ...
delete from @T
where not exists (
                 select *
                 from @T as T2
                   -- Convert version to XML
                   cross apply (select cast(replace(T2.Version, '.', '<X/>') as xml)) as X(Value)

                   -- Extract the values from the XML and build a new version string with 0 instead of null.
                   cross apply (select X.Value.value('text()[1]', 'varchar(10)')+'.'+
                                         isnull(X.Value.value('text()[2]', 'varchar(10)'), '0')+'.'+
                                         isnull(X.Value.value('text()[3]', 'varchar(10)'), '0')+'.'+
                                         isnull(X.Value.value('text()[4]', 'varchar(10)'), '0')) as V(Version)
                 where  [@T].Title = T2.Title and
                        [@T].Version = V.Version
                 );

select *
from @T;