PostgreSQL operator uses index but underlying function does not I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a simple table:

CREATE TABLE jsonthings(d JSONB NOT NULL);
INSERT INTO jsonthings VALUES
    ('{"name":"First","tags":["foo"]}')
  , ('{"name":"Second","tags":["foo","bar"]}')
  , ('{"name":"Third","tags":["bar","baz"]}')
  , ('{"name":"Fourth","tags":["baz"]}');
CREATE INDEX idx_jsonthings_name ON jsonthings USING GIN ((d->'name'));

Using the command line I can run a simple select and it uses the index as expected:

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE d->'name' ? 'First';

                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on jsonthings  (cost=113.50..30236.13 rows=10000 width=61) (actual time=0.024..0.025 rows=1 loops=1)
   Recheck Cond: ((d -> 'name'::text) ? 'First'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_jsonthings_name  (cost=0.00..111.00 rows=10000 width=0) (actual time=0.015..0.015 rows=1 loops=1)
         Index Cond: ((d -> 'name'::text) ? 'First'::text)
 Planning time: 0.073 ms
 Execution time: 0.047 ms
(7 rows)

Since I cannot use the ? character I resorted to using a function which underpins the ? operator. However, it is not using the index:

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE jsonb_exists(d->'name','First');
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on jsonthings  (cost=10000000000.00..10000263637.06 rows=3333334 width=61) (actual time=0.016..3135.119 rows=1 loops=1)
   Filter: jsonb_exists((d -> 'name'::text), 'First'::text)
   Rows Removed by Filter: 10000003
 Planning time: 0.051 ms
 Execution time: 3135.138 ms
(5 rows)

Why is this happening, and what can I do to make the function use the index? Note that in reality the table has another 10MM rows in it and I also have enable_seqscan turned off so this isn't a case of the planner deciding not to use the index.

In response to a comment I tried using a custom operator instead:

CREATE OPERATOR ### (
  PROCEDURE = jsonb_exists,
  LEFTARG = jsonb,
  RIGHTARG = text,
  RESTRICT = contsel,
  JOIN = contjoinsel);

But that has the same problem:

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE d->'name' ### 'First';
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on jsonthings  (cost=10000000000.00..10000263637.06 rows=10000 width=61) (actual time=0.012..3381.608 rows=1 loops=1)
   Filter: ((d -> 'name'::text) ### 'First'::text)
   Rows Removed by Filter: 10000003
 Planning time: 0.046 ms
 Execution time: 3381.623 ms
(5 rows)

Update

The latest PostgreSql driver (as of March 2015) has the ability to escape the ? character so this specific case is no longer a problem.

Supported operators and workaround

The default operator class for GIN indexes on json columns jsonb_ops only supports these operators (per documentaion):

Name        Indexed Data Type   Indexable Operators
...
jsonb_ops   jsonb               ? ?& ?| @>

You can achieve this the other way round: Create a simple IMMUTABLE SQL function using the ? operator, which can be inlined and will use the index just like the operator itself:

CREATE OR REPLACE FUNCTION jb_contains(jsonb, text)
  RETURNS bool AS
'SELECT $1 ? $2' LANGUAGE sql IMMUTABLE;

This works, I tested in Postgres 9.4 ...

Misunderstandings

However, you have been asking the wrong question. There are two basic misconceptions in your question.

The jsonb operator ? cannot be used to search for values. Only for keys or array elements. The manual:

  Description:
  Does the string exist as a top-level key within the JSON value?

You got the wrong operator, the WHERE condition cannot work: WHERE d->'name' ? 'First'
The expression index you have doesn't makes sense either way

CREATE INDEX idx_jsonthings_name ON jsonthings USING GIN ((d->'name'));

The expression d->'name' returns a jsonb value. You would need d->>'name' to get the value as text.
But that would still be pointless. Since the value of the name key is a simple string, a GIN index (while possible) makes no sense to begin with.

Solutions

You don't need the operator ? - so no workaround either.
Here are two ways that would actually work:

Plain GIN index on d and use the "contains" operator @>:

CREATE INDEX idx_jsonthings_d_gin ON jsonthings USING GIN (d);

SELECT d FROM jsonthings WHERE d @> '{"name":"First"}'

You could even use the more specialized operator class jsonb_path_ops. See:

How to get particular object from jsonb array in PostgreSQL?

B-tree expression index on d->>'email' and test with good old =:

CREATE INDEX idx_jsonthings_d_email ON jsonthings ((d->>'email'));

SELECT d FROM jsonthings WHERE d->>'email' = 'First';

The second index would be considerably smaller and the query faster.


Restore Database w Master Key and storing password in plain text We have a (SQL Agent)job that backs up our production db nightly(and restores to a test db server) and we're adding encryption to some of the columns in our tables.  I created a master key and bound it to the Service Master Key:

Create MASTER KEY Encryption By Password = 'MyReallyStrongPW'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY CLOSE MASTER KEY

I also created my certificate:

CREATE CERTIFICATE [My_Cert] With Subject = 'My Certificate'
CREATE SYMMETRIC KEY My_Symm_Key WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE [MY_Cert];

The same account

  domain\MyBigSqlAccount

is running both instances of sql (prod & test).

The only way to be able to see the data(on test) is to run this:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyReallyStringPW'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY CLOSE MASTER KEY

and then this would work:

OPEN SYMMETRIC KEY My_Symm_Key DECRYPTION BY CERTIFICATE [My_Cert]

Select Convert(varchar, DecryptByKey(EncryptedColumn)) as DecryptedCol From SomeTable

The only way I've been able to get this to work is to put the password into plain text as a step in the sql job

  step 1:  restore DB, step 2:  use password to bind Master Key to Service Master Key on test server.

Which doesn't seem too wise to store password in plain text(or password to decrypt files which contains the passwords).

Sql Server 2012 for both.

Related (Q1b)Simple Implementation of SQL Server 2008 Encryption

The question is how do I get around storing my passwords in plain text and still use encryption for both prod & text?

Without a doubt, a new design is needed for step 2. I would suggest that, instead of passing the Database Master Key password, which typically remains fairly constant and is not changed frequently, step 2 would back up the Database Master Key using a randomly generated password of sufficient length. The password can then be encrypted at the source, passed to the destination server encrypted, then decrypted and used to restore the Database Master Key. For this design, you will need to add a procedure, a certificate and a view to the source server msdb database and a procedure and a certificate to the desitination server msdb database. You will also need to create a share folder on the destination server with write permissions granted to the prod server's SQL Agent service account. The objects involved are:

ProdServer.msdb.vwGetRandomPass - view that generates random long
passwords
ProdServer.msdb.DMKEncryptionCertificate - certificate to encrypt DMK
password
ProdServer.msdb.usp_BackupDMK - retreieves a random password from the
view vwGetRandomPass, uses it to back up the DMK to the share,
encrypt the password using the certificate DMKEncryptionCertificate,
return the encrypted password as an output varbinary variable
TargetServer.msdb.DMKEncryptionCertificate - certificate to decrypt
DMK password restored from a backup of the cert and private key on
the prod server
TargetServer.msdb.usp_restoreDMK - accepts varbinary parameter
password, decrypts password using DMKEncryptionCertificate, restore
DMK from share using decrypted password, upon successful restore
delete the DMK file

Step 2 would consist of calling the prod server procedure, followed by a call to the TargetServer procedure to complete the restore. You can use a linked server, osql call or other method to call the procedure on the target server.

For even further security, you can drop the private key on the Production Server after backing it up. That way only the destination server can decrypt the password. The password will also be different and unpredictable every day. The other benefit is that the DMK would be deleted every time and would only exist on the share for the duration of step 2, which should be a matter of seconds.

This can be done successfully, however, I would also ask if it should be done. If this data is so sensitive that it needs to be encrypted, then should it be available outside of your production system? If you decide against it, then you could just drop the certificate and symmetric key in the target database and create new ones with the same name to avoid exceptions. Any call using these would return null. I've included the view below:

/*
generates a random 128 character string from all
valid password characters except single quote

*/
CREATE VIEW dbo.vwGetRandomPass
AS
WITH s1
AS (
    SELECT TOP 32 CHAR(number) AS chr
    FROM master..spt_values
    WHERE number BETWEEN 48
            AND 57 -- number characters
    ORDER BY newid()
    )
    ,s2
AS (
    SELECT TOP 32 CHAR(number) AS chr
    FROM master..spt_values
    WHERE number BETWEEN 65
            AND 90 -- Upper letters
    ORDER BY newid()
    )
    ,s3
AS (
    SELECT TOP 32 CHAR(number) AS chr
    FROM master..spt_values
    WHERE number BETWEEN 97
            AND 122 -- Lower letters
    ORDER BY newid()
    )
    ,s4
AS (
    SELECT TOP 32 CHAR(number) AS chr
    FROM master..spt_values
    WHERE number BETWEEN 33
            AND 38
        OR number BETWEEN 40
            AND 47 -- sign characters
        OR number BETWEEN 58
            AND 64
        OR number BETWEEN 91
            AND 96
        OR number BETWEEN 123
            AND 126
    ORDER BY newid()
    )
    ,final
AS (
    SELECT chr
    FROM s1

    UNION ALL

    SELECT chr
    FROM s2

    UNION ALL

    SELECT chr
    FROM s3

    UNION ALL

    SELECT chr
    FROM s4
    )
SELECT pass = (
        SELECT chr AS [text()]
        FROM final
        ORDER BY newid()
        FOR XML path('')
        )
GO


Is there any benefit to SCHEMABINDING a function beyond Halloween Protection? It is well-known that SCHEMABINDING a function can avoid an unnecessary spool in update plans:

  If you are using simple T-SQL UDFs that do not touch any tables (i.e. do not access data), make sure you specify the SCHEMABINDING option during creation of the UDFs. This will make the UDFs schema-bound and ensure that the query optimizer does not generate any unnecessary spool operators for query plans involving these UDFs.

Are there any other advantages of SCHEMABINDING a function, even if it does not access data?

Yes.
Failing to specify WITH SCHEMABINDING means SQL Server skips the detailed checks it normally makes on the function body. It simply marks the function as accessing data (as mentioned in the link given in the question).
This is a performance optimization. If it did not make this assumption, SQL Server would have to perform the detailed checks on every function invocation (since the unbound function could change at any time).
There are five important function properties:

Determinism
Precision
Data Access
System Data Access
System Verification

For example, take the following unbound scalar function:
CREATE FUNCTION dbo.F
(
    @i integer
)
RETURNS datetime
AS
BEGIN
    RETURN '19000101';
END;

We can look at the five properties using a metadata function:
SELECT
    IsDeterministic = OBJECTPROPERTYEX(Func.ID, 'IsDeterministic'),
    IsPrecise = OBJECTPROPERTYEX(Func.ID, 'IsPrecise'),
    IsSystemVerified = OBJECTPROPERTYEX(Func.ID, 'IsSystemVerified'),
    UserDataAccess = OBJECTPROPERTYEX(Func.ID, 'UserDataAccess'),
    SystemDataAccess = OBJECTPROPERTYEX(Func.ID, 'SystemDataAccess')
FROM (VALUES(OBJECT_ID(N'dbo.F', N'FN'))) AS Func (ID);

The two data access properties have been set true, and the other three are set false.
This has implications beyond those that might be expected (use in indexed views or indexed computed columns, for example).
Effects on the query optimizer
The Determinism property in particular affects the query optimizer. It has detailed rules concerning the types of rewrites and manipulations it is allowed to perform, and these are very much restricted for non-deterministic elements. The side-effects can be quite subtle.
For example, consider the following two tables:
CREATE TABLE dbo.T1
(
    SomeInteger integer PRIMARY KEY
);
GO
CREATE TABLE dbo.T2
(
    SomeDate datetime PRIMARY KEY
);

...and a query that uses the function (as defined previously):
SELECT *
FROM dbo.T1 AS T1
JOIN dbo.T2 AS T2
    ON T2.SomeDate = dbo.F(T1.SomeInteger);

The query plan is as expected, featuring a seek into table T2:

However, if the same logical query is written using a derived table or common table expression:
WITH CTE AS
(
    SELECT *, dt = dbo.F(T1.SomeInteger)
    FROM dbo.T1 AS T1
)
SELECT *
FROM CTE
JOIN dbo.T2 AS T2
    ON T2.SomeDate = CTE.dt;

-- Derived table
SELECT
    *
FROM
(
    SELECT *, dt = dbo.F(T1.SomeInteger)
    FROM dbo.T1 AS T1
) AS T1
JOIN dbo.T2 AS T2
    ON T2.SomeDate = T1.dt;

The execution plan now features a scan, with the predicate involving the function stuck in a Filter:

This also happens if the derived table or common table expression is replaced by a view or in-line function. A FORCESEEK hint (and other similar attempts) will not succeed:

The fundamental issue is that the query optimizer cannot reorder nondeterministic query elements as freely.
To produce a seek, the Filter predicate would need to be moved down the plan to the T2 data access. This movement is prevented when the function is non-deterministic.
Fix
The fix for this example involves two steps:

Add WITH SCHEMABINDING
Make the function deterministic

The first step is trivial. The second involves removing the non-deterministic implicit cast from string to datetime; replacing it with a deterministic CONVERT. Neither is sufficient on its own.
ALTER FUNCTION dbo.F
(
    @i integer
)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
    -- Convert with a deterministic style
    RETURN CONVERT(datetime, '19000101', 112);
END;

The function properties are now:

With the optimizer freed up, all examples now produce the desired seek plan.

Note that using a CAST to datetime in the function would not work, because it is not possible to specify a conversion style in that syntax:
ALTER FUNCTION dbo.F
(
    @i integer
)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
    -- Convert with a deterministic style
    RETURN CAST('19000101' AS datetime);
END;

This function definition produces the scan plan, and the properties show it remains non-deterministic:


Large hexadecimal PRIMARY KEY - how to index substrings of the PK field? I have a requirement for a very large (in terms of the number of rows, not data) database, and need to use a very long, unique, hexadecimal PRIMARY KEY instead of the usual AUTO_INCREMENT integer.
As an example, lets say that the PK is similar to an IPv6 address, say something like 2001:0db8:0000:0000:0000:8a2e:0370:7334.
So, apart from the usual searching by the PK, there will be a need to search by subfields of it - i.e. find all keys, that begin with "2001" (covered by the PK- should be sargeable), or (say) has 0db8 in their second set of four hex characters, and then to reference this 2nd key from other tables.
What is the best way to store such a primary key (assume that I am using MariaDB or MySQL)?
My thoughts are to do one of the following:

one big column,

have 8 columns and make a composite key,

or something else.

From a general database point of view, what would be the best technical solution to server my requirements?
Apart from the purely technical aspects of the question above, I would also be interested in any recommendations that users here might have as to the best database engine to use in this case?
I will only have a couple more tables, that will have FOREIGN KEY references to the long hex PRIMARY KEY. I can do without the DRI (Declarative Referential Integrity) if there are major performance benefits

<TL;DR>

Regular expressions can be a very powerful tool for various data storage,  manipulation and indexing requirements - regexes' capabilities in conjunction with GENERATED columns can be even more flexible and powerful and provide a decent solution to the OP's issue.
In this particular case (rowing back from my initial answer!), MySQL or its derivatives are the only feasible solution. PostgreSQL doesn't allow GENERATED fields to be FOREGIN KEYs unless they're UNIQUE. Obviously, the requirement to be able to use small substrings as FKs can't be satisfied in this case, despite the fact that MySQL's regex implementation leaves a lot to be desired! PG could use TRIGGERs - but that's another question!

</TL;DR>
A relatively simple solution to your issue exists if (and maybe it's a big if), you have PRIMARY KEY string separated by the same character. A more complex solution is possible using regular expressions - both options are explored below.
You can use GENERATED(*) columns (confusingly, these are also referred to as VIRTUAL in the Wikipedia entry, which is also one of the storage classes for such fields - the other storage classes are [STORED | PERSISTENT]. From the MariaDB documentation:

PERSISTENT (a.k.a. STORED): This type's value [i.e. data] is actually stored in the table.

VIRTUAL: This type's value is not stored at all. Instead, the value [data] is generated dynamically when the table is queried. This type is the default.

(*) There are other terms for these columns, e.g. COMPUTED BY or CALCULATED.
MySQL's syntax is (versions 5.7 and up):
col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED]  -- storage class
  [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

In order to use such columns as FOREIGN KEYs, their storage class has to be STORED.
"Simple" solution:
Your PK is of the format (a fiddle with the code and tests for this part is available here):
str:str:str:str:str:str:str:str  (str = 1-4 characters(*) - separated by colons)

(*) Not hex characters in this case - I messed around with MySQL's HEX() and UNHEX() functions to no avail. However, see discussion!
MySQL has an IS_IPV6() function, however, the MySQL 5.7 documentation also contains this gem:

CHECK
The CHECK clause is parsed but ignored by all storage engines.

So, I'm going to use MySQL 8 which has implemented them - if you want valid IP_V6 addresses in a version prior to 8, then you'll have to use a trigger. MariaDB has CHECK constraints from at least 10.3 onwards.
You mentioned that IPv6 was (only) an example - if any set of characters will do, then just remove the CHECK constraint - and see the complex solution below for a discussion on regular expressions.
I created this MySQL 8 table (see fiddle here):
CREATE TABLE test
(
  ip_v6 VARCHAR (39) NOT NULL,
  host VARCHAR (100) NOT NULL,

  ip_v6_token_1 VARCHAR (4) GENERATED ALWAYS AS
    ((SUBSTRING_INDEX(SUBSTRING_INDEX(ip_v6, ':', 1), ':', -1))) STORED,
  ip_v6_token_2 VARCHAR (4) GENERATED ALWAYS AS
    ((SUBSTRING_INDEX(SUBSTRING_INDEX(ip_v6, ':', 2), ':', -1))) STORED,

  INDEX (ip_v6_token_1),  -- required for FOREIGN KEYs
  INDEX (ip_v6_token_2),  --           "

  CONSTRAINT test_ip_v6_pk PRIMARY KEY (ip_v6),
  CONSTRAINT ip_v6_valid_ck CHECK (IS_IPV6(ip_v6))
);

-- do the same for tokens 3 - 7 as per requirements!

I inserted a few records and then:
SELECT
  ip_v6_token_1 AS "Token 1",
  ip_v6_token_2 AS "Token 2",
  ip_v6         AS "IPv6:",
  host          AS "Host:"
FROM test ORDER BY host;

Result:
Token 1 Token 2 IPv6:                                    Host:
   2001    0db8 2001:0db8:0000:FFF0:0000:8a2e:0370:7334 host_1
     10       9 10:9:8:7:6:6:7:6                        host_2
   4001    1111 4001:1111:2222:FFF0:0000:8a2e:0370:7334 host_3

So far, so good - we have our tokens split out as required.
Now, we check the FOREIGN KEY support - create a table:
CREATE TABLE test_fk
(
  t_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,

  --
  -- Other fields
  --

  token_1 VARCHAR (4) NOT NULL,
  token_2 VARCHAR (4),

  CONSTRAINT test_fk_test_ip_v6_token_1_fk
    FOREIGN KEY (token_1)
      REFERENCES test (ip_v6_token_1) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT test_fk_test_ip_v6_token_2_fk
    FOREIGN KEY (token_2)
      REFERENCES test (ip_v6_token_2) ON DELETE CASCADE ON UPDATE CASCADE
);

So, I tested the ON DELETE CASCADE and the ON UPDATE CASCADE functionality and they appear to work. Also, when one tries to INSERT on test_fk it fails if there isn't a corresponding token in the ip_v6 field of the test parent table - see the fiddle for the tests.
Complex solution (modelled on the above):
This part of the answer makes extensive use of regular expressions. I'm taking IPv6 as an exemplar of the power and flexibility of regexes - ultimately, you should be able to write your own in line with your particular requirements.
A fiddle for the code and tests can be found here.
As above, I CREATEd a table:
CREATE TABLE test
(
  ip_v6 VARCHAR (39) NOT NULL,
  host VARCHAR (100) NOT NULL,

  ip_v6_token_1 VARCHAR(39) GENERATED ALWAYS AS
    (SUBSTRING_INDEX(SUBSTRING_INDEX(ip_v6, ':', 1), ':', -1)),
  ip_v6_token_2 VARCHAR(39) GENERATED ALWAYS AS
    (SUBSTRING_INDEX(SUBSTRING_INDEX(ip_v6, ':', 2), ':', -1)),

  -- as in simple solution, same for tokens 3-7 as per requirements!

  INDEX (ip_v6_token_1),  -- required for FOREIGN KEYs
  INDEX (ip_v6_token_2),

  CONSTRAINT test_ip_v6_pk PRIMARY KEY (ip_v6),
  CONSTRAINT ip_v6_regexp_ck
    CHECK(ip_v6 REGEXP '^([[:xdigit:]]{1,4}:){7,7}[[:xdigit:]]{1,4}$')
);

So, as you see, the change is on the CHECK constraint on the ip_v6 field:
  CONSTRAINT ip_v6_regexp_ck
    CHECK(ip_v6 REGEXP '^([[:xdigit:]]{1,4}:){7,7}[[:xdigit:]]{1,4}$')

A brief explanation of the regular expression '^([[:xdigit:]]{1,4}:){7,7}[[:xdigit:]]{1,4}$':

the ^ character is an anchor - it matches the beginning of a line.
This ensures that the format of the ip_v6 string is (from the beginning) of 1-4 hex characters (of any combination) in the ranges of 0-9 OR a-f OR A-F - from here - groups of characters enclosed in [ & ] are called character classes. My regex above is equivalent to '^([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}$'.
From here:

When it is necessary to specify the minimum and maximum number of
repeats explicitly, the bounds operator {} may be used. Thus. a{2} is
the letter ‘a’ repeated exactly twice, a{2,4} represents the letter
‘a’ repeated between 2 and 4 times, and a{2,} represents the letter
‘a’ repeated at least twice with no upper limit. Note that there must
be no whitespace inside the {}, and there is no upper limit on the
values of the lower and upper bounds.

the {1,4} [:xdigit:] characters are followed by a (character literal) : - it has no special meaning in regexps.

the enclosing parentheses (()) (important for this discussion, see below) mean that the groups of hex digits concatenated with a single : are to be treated as a group - "captured groups" - see discussion re. MySQL vs. PostgreSQL!

the {7,7} means 7 and no more nor no less than 7 repeats of that group can be present - since it ends with a colon, it can't be the last element. That is taken care of by the final group - [0-9a-fA-F]{1,4}$ - where the usual 1 - 4 digit hex is followed by the $ character.

the $ character is another anchor, this time signifying the end of the line - so the end of our ip_v6 string must be the hex character(s) without a trailing :!

So, we've ensured that our ip_v6 string at least corresponds to some of the basics about IPv6 addresses - now what can we do?
As can be seen, there are INDEXes (or KEYs - synonyms in MySQL) for the three token fields generated - so far so good. I don't have a realistic dataset to test, but if you do, you can see the effect of these indexes.
You can see that I've used SUBSTRING_INDEX (esp. from here) - which is all well and good - but this will become more and more (totally) restrictive as your requirements become more complex! For some reason, the relatively easy SUBSTRING() function is "disallowed" in GENERATED columns.
What you really need is the power of Regular expressions - these are very powerful and come in handy in lots of scenarios - like the one in this question!
You will see in the fiddle that I have run a few tests - some of which work and some of which fail - as with the simple solution!
Actual regexps for IPv6 addresses can be found here, here. These are not for the faint-hearted - first link's example:

(([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,7}:|([0-9a-fA-F]{1,4}:){1,6}:[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,5}(:[0-9a-fA-F]{1,4}){1,2}|([0-9a-fA-F]{1,4}:){1,4}(:[0-9a-fA-F]{1,4}){1,3}|([0-9a-fA-F]{1,4}:){1,3}(:[0-9a-fA-F]{1,4}){1,4}|([0-9a-fA-F]{1,4}:){1,2}(:[0-9a-fA-F]{1,4}){1,5}|[0-9a-fA-F]{1,4}:((:[0-9a-fA-F]{1,4}){1,6})|:((:[0-9a-fA-F]{1,4}){1,7}|:)|fe80:(:[0-9a-fA-F]{0,4}){0,4}%[0-9a-zA-Z]{1,}|::(ffff(:0{1,4}){0,1}:){0,1}((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9]).){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])|([0-9a-fA-F]{1,4}:){1,4}:((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9]).){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9]))

That's over 660 characters, and the second link's example is 1080 characters:

^\s*((([0-9A-Fa-f]{1,4}:){7}([0-9A-Fa-f]{1,4}|:))|(([0-9A-Fa-f]{1,4}:){6}(:[0-9A-Fa-f]{1,4}|((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3})|:))|(([0-9A-Fa-f]{1,4}:){5}(((:[0-9A-Fa-f]{1,4}){1,2})|:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3})|:))|(([0-9A-Fa-f]{1,4}:){4}(((:[0-9A-Fa-f]{1,4}){1,3})|((:[0-9A-Fa-f]{1,4})?:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){3}(((:[0-9A-Fa-f]{1,4}){1,4})|((:[0-9A-Fa-f]{1,4}){0,2}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){2}(((:[0-9A-Fa-f]{1,4}){1,5})|((:[0-9A-Fa-f]{1,4}){0,3}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){1}(((:[0-9A-Fa-f]{1,4}){1,6})|((:[0-9A-Fa-f]{1,4}){0,4}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(:(((:[0-9A-Fa-f]{1,4}){1,7})|((:[0-9A-Fa-f]{1,4}){0,5}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:)))(%.+)?\s*$

However, the IS_IPV6() function removes the need to use horrors like these!
Whether or which is correct/better is a bit above my pay-grade, but you can see from the discussion under the first link's answer (> 250 upvotes!) that it's a complex task to get right but also that regular expressions can be very powerful!
However, all is not rosy in the garden - if you look at my complex solution fiddle, I've added some regular expression tests and MySQL fails on a few - notably those using captured groups (that is, bracketing using ( and ).
An example is this one (see the MySQL 8 complex fiddle):
It SHOULD work like this (see the last code snippet in the PostgreSQL fiddle here):
SELECT
  REGEXP_REPLACE
  (
    ip_v6, '(^[[:xdigit:]]{1,4}):([[:xdigit:]]{1,4}):([[:xdigit:]]{1,4}):(.*$)', '\4 xx \3   \2   \1'
  )
  AS "Rep text 2",
  ip_v6
FROM test;

Result:
Rep text 2                                      ip_v6
FFF0:0000:8a2e:0370:7334 xx 0000   0db8   2001  2001:0db8:0000:FFF0:0000:8a2e:0370:7334
123:123:123:123:123      xx  321    654    987  987:654:321:123:123:123:123:123

Notice the 5 last tokens of the ip_v6 string followed by xx followed by tokens 3, 2 & 1 in that order - i.e. reversed as per the captured groups (\3 &c.) in the (brackets).
MySQL returns (fiddle - last snippet in the fiddle):
Rep text 2      ip_v6
4 xx 3   2   1  123:123:123:123:123:123:123:123
4 xx 3   2   1  2001:0db8:0000:FFF0:0000:8a2e:0370:7334

MySQL simply treats the replacements for the captured groups (i.e. what would be the tokens) as string literals (4, 3, 2, 1) - this is quite simply appalling - instead of failing, it returns the WRONG DATA. MySQL does the same thing - incorrect data instead of failing for character class shortcuts - \d fails as a replacement for [[:digit:]] which works for PostgreSQL. Again, instead of failing, it returns an incorrect result!
In this PostgreSQL fiddle, I set up the GENERATED columns and my FOREIGN KEYs as per the MySQL complex solution fiddle - however, with PostgreSQL, there is a total showstopper for the OP's requirements.
There has to be a UNIQUE index (not just a simple one) on that column for it to be able to be used as an FK. Just for the sake of completeness, I did this, but it completely puts the kibosh on using PostgreSQL for this question's requirements - but hey, I learnt a lot going through all of this - I hope you have too!


Count duplicate occurrences in MYSQL table based on value in two columns using having and inner join. I have a table that lists vehicles by owner. In the table I have owner_id and vehicle_type columns, I want to pull all owners that have both more than 1 car and more than 1 van. The query I put together appears to be working but it has some performance issues.

My table is hosted in a MYSQL database.

Here's what I'm currently using, any recommendations on what I can change?

select * from (
  select owner_id, vehicle_type, count(*)
  from vehicles
  where vehicle_type = 'Car'
  group by owner_id, vehicle_type
  having count(*) > 1
) t1
inner join (
  select owner_id, vehicle_type, count(*)
  from vehicles
  where vehicle_type = 'Van'
  group by owner_id, vehicle_type
  having count(*) > 1
) t2
on t1.owner_id= t2.owner_id

You can reduce the queries to one:

select owner_id, sum(vehicle_type='Car') as cnt_car, sum(vehicle_type='Van') as cnt_van
from vehicles
where vehicle_type in ('Car', 'Van')
group by owner_id
having cnt_car > 1 and cnt_van > 1

Apart from that, the index that would provide the best performance would be a combined index on the columns (owner_id, vehicle_type).


Find max and min date over multiple fields of the same record I have a table with 4 distinct data-type fields. For each record, I need to find the max and min date over them.

That's easy to do for a set of records over a unique field. But how to do it in my case, without using cursor?

I'd need some sort of function that receives a list of fields, like max(field1, field2, field3, field4).

If I understood you correctly, I'd use CROSS APPLY with VALUES to "unpivot" your fields and then a standard MIN and MAX can be used. Something like this:

DECLARE @T TABLE (ID int IDENTITY, f1 int, f2 int, f3 int, f4 int);

INSERT INTO @T (f1, f2, f3,f4) VALUES
(1, 2, 3, 4),
(5, 6, 7, 8),
(7, 8, 0, 1),
(2, 3, 6, 5);

SELECT ID, MIN(f) AS MinF, MAX(f) AS MaxF
FROM
    @T
    CROSS APPLY
    (
        VALUES (f1), (f2), (f3), (f4)
    ) AS CA(f)
GROUP BY ID
ORDER BY ID;

Or without CROSS APPLY:

SELECT
    ID
    ,(SELECT MIN(f) FROM (VALUES (f1), (f2), (f3), (f4)) AS Fields(f)) AS MinF
    ,(SELECT MAX(f) FROM (VALUES (f1), (f2), (f3), (f4)) AS Fields(f)) AS MaxF
FROM @T
ORDER BY ID;

One more variation of the same theme. Aggregate within CROSS APPLY.

SELECT ID, MinF, MaxF
FROM
    @T
    CROSS APPLY
    (
        SELECT MIN(f) AS MinF, MAX(f) AS MaxF
        FROM (VALUES (f1), (f2), (f3), (f4)) AS Fields(f)
    ) AS CA
ORDER BY ID;

SQL Fiddle

All variants return the same result

+----+------+------+
| ID | MinF | MaxF |
+----+------+------+
|  1 |    1 |    4 |
|  2 |    5 |    8 |
|  3 |    0 |    8 |
|  4 |    2 |    6 |
+----+------+------+

The last variant has a better-looking execution plan for this simple table.


What is the best way to migrate multiple SQL Server databases? I want to migrate my database server to a new server. Right now I have a database server with Windows Server 2008 and now I am migrating to a new, separate server with Windows Server 2012.

There are around 50 to 100 databases.

What is the best way to migrate the database server without affecting clients (meaning no downtime)?

Copy 50 to 100 databases to a completely new server with zero downtime?  I don't think that's going to happen.  When you're talking about a migration, planned downtime is an absolute must.

In this case, depending on the size of the databases, I'd take the scripting route with this.  If you are proficient with PowerShell, you can loop through all the databases and take backups to store in accessible storage for the new server.  Then on the new server, you can script out the restore of all the databases.

Don't forget about the server-level items (logins, jobs, etc.).  You can script these as well, but with this particular requirement SSIS actually makes it very easy.  There are tasks to do all of these server-level items.

If you're expecting no downtime, how are the clients going to redirect their requests to the new database server?  There are a few external solutions for this, but something to consider when you promise "zero downtime".


Optimal way to store leaderboards by month I have a leaderboard table of users for current month. I need to save all leaderboards by months and display current/old leaderboard on site. Should i create a new table when every month stars? Is this a good approach? In two years i will have 24 additional tables in my database which is really confusing me.
What is optimal way to do this?

Why not just add a MonthOf and YearOf column (likely as integer types) to your Leaderboard table? Then you'll only need one table to store all the leaderboards. Whenever you need to show the Leaderboard for a particular month and year, you just add those fields as predicates in your WHERE clause of your query. E.g. WHERE MonthOf = 1 AND YearOf = 2021 to get the Leaderboard for month of January 2021.
As Vérace mentioned, knowing which database system and version you're using, your Leaderboard table's current structure, and some sample data, would be helpful. But in any case, the above generic solution should generally work, and you should ensure the MonthOf and YearOf fields are part of an index for optimal execution of your queries.


How to split the comma separated list in the stored procedure I am working on small module in which we pass the column name as comma-separated string to a stored procedure. In the stored procedure, we want to separate the column names out.

The definitive article on the topic is Arrays and Lists in SQL Server 2005 and Beyond which contains code and performance test results for a variety of split functions.

You say these are column names? You should probably read another of Erland's articles on The Curse and Blessings of Dynamic SQL where he discusses both SQL injection and when dynamic SQL is a good idea.


How to disable flashback query logging for a specific table (Oracle)? We have a specific table that has a lot of activity and it creates a lot of change records. The consequence is that the flashback data only goes back a couple of days. That is OK for many cases but it would be beneficial to have access to more historical data.

We would like to either restrict logging on that table. Or disable it completely. I imagine that we may be able to do this by tablespace, I just have not found much on how to make these changes.

You can declare table as NOLOGGING. Then some of the DML and DDL operations will not be recorded in redo logs.

Oracle logging_clause

Of course this has many caveats:

if you have many NOLOGGING operations basically you won't be able to flashback or restore the table
UPDATE or DELETE operations are logged in any case
only direct path INSERTS can skip logging. But that means that such INSERT will not reuse empty space in table segment and will extend it every time you do such INSERT.

In my experience in some situations one can save a lot of redo/archivelog space using NOLOGGING but you have to adapt your code to it.


SQL timestamp to specific hour I need to set the time of a timestamp in SQL.
My results need to be between yesterday at 7 o'clock and today at 7 o'clock.

Here is the query

WHERE created_at BETWEEN
    ?::timestamp - INTERVAL '1 day' AND ?::timestamp
ORDER BY created_at DESC

The value is:

2017-05-30

I've seen the PostgreSQL datetime functions. But I don't know how to specify a time

Here is the query with static datetimes

SELECT
    *
FROM
    "myTable"
WHERE
    created_at BETWEEN '2017-05-30 07:00:00' AND '2017-05-30 07:00:00'
ORDER BY
    created_at DESC

Now I need to replace the datetime strings with a date string and add the time in SQL

You can use current_date (without a time) and then add the 7 hours:

WHERE created_at BETWEEN (current_date - 1) + time '07:00:00'
                     AND current_date + time '07:00:00'

(current_date - 1) is "yesterday"

Alternatively you could use an interval:

WHERE created_at BETWEEN (current_date - 1) + interval '7' hour
                     AND current_date + interval '7' hour

I prefer adding a "time" as that is easier to write if you also want to include minutes, e.g. current_date + time '07:30:00'


Ola Hallengren Index Optimize script failed because a deadlock to accomplish the nightly maintenance tasks on production servers, every night I have scheduled the script to defrag the indexes. Due to other nightly application maintenance tasks, sometimes the job fails because of a deadlock. At the same time starts the script to defrag an index and on the same table a query is doing an update where the index was created. I would like to find a solution to avoid that job fails. Because I can't have a control on that query, I would find a way to check if the table is locked until the query will be completed and after that, start the defragmentation of the indexes.

Any suggestions would be greatly appreciated.

Thanks.

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

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

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

WaitAtLowPriorityAbortAfterWait
LockTimeout

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


MySQL Cluster MaxNoOfConcurrentOperations and RAM Why increasing of MaxNoOfConcurrentOperations lead to higher consumption of RAM?

I need high value for fast filling tables with some random data, but i noticed that my datanodes are swapping at starting with high values (5,000,000 - 10,000,000).

There is another way to solve it: use innodb then alter table, but there might be other difficulties (some people complain about "lock wait timeout exceeded").

I just want to know how it working and how number of operations tied with RAM.

This is a good summary: http://fromdual.com/mysql-cluster-memory-sizing

Most memory buffers are preallocated in memory to avoid latency issues when reserving huge chunks of memory.


Google search console data - find the newly appeared keyword for the month I have a table named sconsole for google searchconsole api query data with the fields specified in the code, I need to find the new keywords (r.query field) appeared each month. I tried using this but it returns zero results. At the moment all the data is saved to the same table. Is there any easy way to do this?

Previously I tried with a very simple query

SELECT DISTINCT s.*
FROM sconsole s
JOIN sconsole t ON (s.site_id = t.site_id AND month(str_to_date(s.month,'%b')) >  month(str_to_date(t.month,'%b')))
where (s.site_id = t.site_id)

It returned an error. the requirement is that the site_id is same and the month is the next month. This way each month should be iterated and returned with the new keywords.

I figured it out after a brief study, here it is,

SELECT * FROM sconsole WHERE sconsole.query IN
                (SELECT  tbl.query FROM
                  (  SELECT  s1.* FROM sconsole s1 WHERE s1.month = '{$params['m1']}' UNION ALL SELECT  s2.* FROM sconsole s2 WHERE s2.month = '{$params['m2']}'
                  ) AS tbl  GROUP BY  tbl.query HAVING COUNT(tbl.query) = 1)

works like a charm.


Getting time delta between two related rows I have a table with a lot of entries in pairs, related to one another by a field name, each entry has a timestamp timestamp_received. I'd like to get the delta of the timestamp per name

table:

|id|name|timestamp_received|
|1|A|2016-09-21 11:11:00|
|2|A|2016-09-21 11:11:10|
|3|B|2016-09-21 11:11:20|
|4|B|2016-09-21 12:12:21|

desired output:

|name|delta|
|A|00:00:10|
|B|01:01:01|

What I have tried so far:

SELECT name, timestamp_received - LAG(timestamp_received, 1) OVER (PARTITION BY name ORDER BY id ASC) delta
FROM message;

Which does not produce the expected output

SELECT fpm_left.name, age(fpm_right.timestamp_received, fpm_left.timestamp_received)
FROM
  (SELECT * FROM message WHERE id IN (SELECT min(id) AS id FROM message GROUP BY name)) fpm_left
JOIN (SELECT * FROM message WHERE IN in (SELECT max(id) AS id FROM message GROUP BY name)) fpm_right
    ON fpm_left.name = fpm_right.name AND fpm_left.id != fpm_right.id;

Which produces the expected output, but seems a little innefficient

Can this be done more elegantly?

You can use the solution with the window function, you just need to filter out the "other" row. This can e.g. be done using Postgres' distinct on ()

SELECT distinct on (name)
       name,
       timestamp_received - LAG(timestamp_received, 1) OVER (PARTITION BY name ORDER BY id ASC) as delta
FROM message
order by name, timestamp_received desc;

This will always pick exactly one row per name (the one with the latest timestamp_received), even if there are more then two rows per name.


How many date dimensions for one fact About designing star schema: I have three date columns in a fact table fact_table (insert_date, trade_date, close_date ...). And I don't know how many date dimensions should be created?

Case 1: Dim A . It means: one row @fact_table have three FKs to A.

Case 2: Dim A (for insert_date), Dim B (trade_date),Dim C (close_date). It means: one row @fact_table have one FK to A, one FK to B, and one FK to C .

Question: How many date dimensions should be created?

A dimension represent a category of information: e.g. date, product...
  in your case you have three attributes in the fact table referring to
  the same analysis axis which is 'DATE', so if you are using a star
  schema only one date dimension is needed, this is a dimension role playing implementation.
  There are two dimension role playing implementations :

The table alias type uses the dimension more than once in an SQL
statement by assigning an alias for each use.
The database view type, you create as many views as the number of
roles you need the dimension on the fact.

You need to only one date dimension, for exemple  :
the 'type' of date dimension will mentioned in the fact table as above.

  Updated :

Modeling Date and Time dimensions
There should be only one Date dimension and one Time dimension across all facts and dimension tables.  Not all date and time fields needed to be mapped to date and time dimensions. Map a date-time field to a Date or Time dimension only when you need the extra attributes from the dimension.
Typically the lowest granularity of the Date dimension is Day, and the lowest granularity of the Time dimension is Second. Many warehouses do not need a Time dimension, but a Date dimension is used in virtually every data warehouse system.
In general, create separate dimensions for Date and Time.
If it is necessary to extract contiguous chunks of time that cross day boundaries (for example 11/24/2000 10 p.m. to 11/25/2000 6 a.m.), then it is easier if the hour and day are in the same dimension. Otherwise, separate dimensions for Date and Time are easier to manage and to query.
It is easier to analyze cyclical and recurring daily events if Date and Time are separate dimensions.
Example:
Number of meetings active at 9AM this week.  This is an easy query if Date and Time are separate; it is much more complex if Date and Time are combined into a single dimension.
Following are issues of having date and time dimensions together in one Date/Time dimension :

  Issue#1:

  Combining Date and Time into a single dimension creates an extremely    large dimension, particularly if you use Seconds as the
  grain. The    problem is not as great if you use Hour as the grain of
  the combined    table.
  Cardinality of Date and Time Dimensions (separated) for 10 years of    data Date Dimension:  10 * 365 = 3650 Time Dimension (granularity:
  seconds): 24 * 60 * 60 = 86400
  Cardinality of Date and Time Dimensions (combined) for 10 years of    data DateTime Dimensions (granularity Hourly): 10 * 365 * 24 = 87600
  DateTime Dimensions (granularity Seconds): 10 * 365 * 24 * 60 * 60 =
  315,360,000

The more records in the DateTime dimension, the slower query performance will be.

  Issue #2 Having both date and time dimension in the same dimension can
  cause interpretation problems for fact tables that have granularity of
  a Day. It would be easy to inadvertently enter two records into the
  fact table for the same day, because of the extra granularity in the
  dimension table.

Find more : source


Set a column's default value to the concatenation of two other columns' values? I have a Postgresql 8.1 database. In one table, there are three columns: first_name, last_name, display_name.

Is it possible to set the default value of display_name to be first_name + " " + last_name?

You don't need to actually store the value; you can create a function that can be referenced much like a generated column.  The one caveat is that references must always be qualified with the table or alias name.

CREATE TABLE person
  (
    id int PRIMARY KEY,
    first_name text,
    last_name text NOT NULL
  );

INSERT INTO person
  VALUES
    (1, 'John', 'Smith'),
    (2, 'Jane', 'Doe'),
    (3, NULL, 'Prince');

CREATE FUNCTION display_name(rec person)
  RETURNS text
  STABLE
  LANGUAGE SQL
  COST 5
AS $$
  SELECT
    CASE
      WHEN $1.first_name IS NULL THEN ''
      ELSE $1.first_name || ' '
    END || $1.last_name;
$$;

SELECT p.id, p.display_name FROM person p;

The results:

 id | display_name
----+--------------
  1 | John Smith
  2 | Jane Doe
  3 | Prince
(3 rows)

You can even index on the generated value, including using KNN searches based on trigram similarity.  For example:

CREATE EXTENSION pg_trgm;

CREATE INDEX person_trgm_name
  ON person
  USING gist
  (display_name(person) gist_trgm_ops);

SELECT
    p.id,
    p.display_name,
    similarity(p.display_name, 'Jane')
  FROM person p
  ORDER BY p.display_name <-> 'Jane'
  LIMIT 2;

This type of search returns rows from the index scan in order of "distance" from the search string.  If you want to see how "close" they were, you could either use the distance operator (<->) or the similarity() function (which is 1 - distance).  A KNN search can return the K "nearest neighbors" very quickly, even with a very large data set.


Always Encrypt and SSIS - Security Risk We have a database using the AlwaysEncrypted feature of SQL Server 2016.

We need to create SSIS packages (deployed on SSISDB) for certain application tasks.

What options we have to store the key securely so those SSIS packages can use that key to decipher the data.  Assuming the application DB and SSISDB are on same server

Alternatively, Would it be mandatory/good practice to have the application DB and SSISDB both on different servers ?

There are a few parts to implement proper security in your environment:

SSIS packages are encrypted by default when referring to sensitive information, you can set with their own password
SSIS can also be signed by a certificate
There is a master key, that ideally would be in a KM outside the SQL Server
There is a database key
There is the user that you would use for running the package, which you should grant specific access and deny access to anything the package shouldn't touch

There is nothing that says you would have the keys on the same server; but even if you have them in the same server, it doesn't mean they would have the same security user. Best practice would be to have a separate KM server.


Find records with disjoint dates Consider the following table,

CREATE TABLE temp (
    [TabName] VARCHAR(255),
    [ID] VARCHAR(255),
    [AsOfDate] DATE)

  INSERT INTO temp VALUES
    ('TAB1', 'C103', '2019-05-01'),
    ('TAB1', 'C103', '2019-06-01'),
    ('TAB2', 'C103', '2019-06-01'),
    ('TAB2', 'C103', '2019-07-01'),
    ('TAB1', 'C103', '2019-09-01'),
    ('TAB1', 'C104', '2019-06-01'),
    ('TAB1', 'C104', '2019-08-01')

TabName     ID      AsOfDate
TAB1        C103     '2019-05-01'
TAB1        C103     '2019-06-01'
TAB2        C103     '2019-06-01'
TAB2        C103     '2019-07-01'
TAB1        C103     '2019-09-01'
TAB1        C104     '2019-06-01'
TAB1        C104     '2019-08-01'
TAB1        C105     '2019-04-01'
TAB1        C105     '2019-05-01'

I am wanting to find the IDs from the table that have skipped dates. In this table, I would want to identify that ID C103 and C104 have skipped dates as they have jumped from '2019-07-01' to '2019-09-01' and '2019-06-01' to '2019-8-01' respectively.

I have found the following question asked previously Find Missing Dates in Data which I believe gives some potential clues on the approach, i.e. using CTE, however, I am unsure how to apply them to this question where the dates are not consecutive across the whole table.

Should we be looking at partitioning here?

You can use a recursive CTE to achieve this, in conjunction with two windowing functions (ROW_NUMBER and RANK), to produce a running total of the date difference between the current date value and the previous value (by tabname & id group). You then SELECT only those rows with a date difference higher than 1.

If you're on SQL Server 2012 or higher, there is a much simpler way. Use the LAG function to retrieve the previous value (grouped by tabname and id) and performt he DATEDIFF on that.

Examples are included below, and you can see them in action in this db<>fiddle.

LAG Example:

SELECT
  TabName,
  Id,
  AsOfDate
FROM
(
  SELECT
    TabName,
    Id,
    AsOfDate,
    ISNULL(DATEDIFF(DAY, LAG(AsOfDate) OVER (PARTITION BY TabName, Id ORDER BY AsOfDate), AsOfDate), 0) AS PrevDateDiff
  FROM Temp
) t
WHERE t.PrevDateDiff > 1
ORDER BY tabname, id, asofdate

Recursive CTE example:

;WITH CTE AS (
  SELECT
    TabName,
    Id,
    AsOfDate,
    RANK() OVER (ORDER BY TabName, Id) AS Grp,
    ROW_NUMBER() OVER (PARTITION BY TabName, Id ORDER BY AsOfDate) AS Rn
  FROM Temp
), CTE2 AS
(
  SELECT c1.TabName,
    c1.Id,
    c1.AsOfDate,
    c1.Grp,
    c1.Rn,
    0 AS DateDiff
  FROM CTE c1
  WHERE c1.Rn = 1
  UNION ALL
  SELECT c1.TabName,
    c1.Id,
    c1.AsOfDate,
    c1.Grp,
    c1.Rn,
    DATEDIFF(DAY, c2.AsOfDate, c1.AsOfDate) AS DateDiff
  FROM CTE c1
  INNER JOIN CTE2 c2 ON c2.Rn = c1.Rn - 1 AND c2.Grp = c1.Grp
  WHERE c1.Rn > 1
)

SELECT
  TabName,
  Id,
  AsOfDate
FROM CTE2
WHERE DateDiff > 1
ORDER BY tabname, id, asofdate


Leaf Joins in Data Warehouse Design Upon modeling my very first data warehouse model (for historical stock market data), I stumbled across a data relationship that I don't know how to best account for. The fact tables include  multiple years of end-of-day data and intraday data for various assets and exchanges. Now I expect to filter the data by date, which is why I created a date dimension. Similarly, I want to filter intraday data by time, which includes a date. This far it seems like a usual Snowflake Schema to me.
However, trading days depend on both the date and exchange. Similar is true for opening hours of an exchange. Both of these dimension tables would mean a join of two snowflake leaves. Is this the standard way of doing this or is there a better design pattern?

This kind of dimension is called Role-Playing Dimension.
This is tipical of Multi-Dimensional SSAS modeling.
However SSAS Tabular modeling does not support role-playing dimensions;  you can overcome that limitation by using DAX, enabling inactive relationships for specific calculations (you can define multiple relationships between the same tables in Tabular). However, if you want to offer navigation in different role-playing dimensions to the user, the best practice for both Multidimensional and Tabular is to import the same table multiple times, renaming data and metadata.


SQLAnywhere: Syntax error on call function; Call without IF construct is working I've got this little code example where I call a procedure, which is taking 4 arguments:

DECLARE @tmp BIT
SET @tmp = 0
IF @tmp = 1
    call __insert_to_notifications_table('foo', '1234', '5678', 'Some comment')
ELSE
    print 'Path, which is choosen'

When running this, I'm getting

Syntaxerror at 'call' in line 4
SQLCODE=-131, ODBC 3-Status="42000"

If I run

call __insert_to_notifications_table('foo', '1234', '5678', 'Some comment')

directly, it is working fine. (the procedure is really called with double __ at the beginning; Also when renaming it's not working)

When running the code like this:

DECLARE @tmp BIT
SET @tmp = 0
IF @tmp = 1
    print 'other path'
ELSE
    print 'Path, which is choosen'

it seems to work. So I would hunt the issue down to the call of call.

What did I wrong here?

I think this is caused by a confusion between Watcom-SQL and T-SQL syntaxes.

Because you use CALL (rather than EXEC) in the first example the database engine will assume that the code is in Watcom SQL. (There is a PRINT statement later but the decision will have been made before then).

In the second example the command PRINT is only valid in T-SQL and the whole code segment is valid T-SQL so the database engine will assume that all the code is T-SQL and the THEN is not needed.

If I'm right then this should work (untested in Watcom-SQL):

DECLARE @tmp BIT;
SET @tmp = 0;
IF @tmp = 1
   THEN  call __insert_to_notifications_table('foo', '1234', '5678', 'Some comment')
ELSE
    MESSAGE 'Path, which is chosen' TO CLIENT
END IF;

For the differences in SQL Anywhere support for various SQL dialects see: http://dcx.sybase.com/index.html#1200/en/dbusage/ug-sql-compat.html

Update - this batch runs fine in dbISQL:

BEGIN
  DECLARE @tmp BIT;
  SET @tmp = 0;
  IF @tmp = 1
       THEN call dbo.sa_info()
  ELSE
      MESSAGE 'Path, which is chosen' TO CLIENT
  END IF;
END

(I've added a BEGIN and END to give the DECLARE a context & used a built-in procedure rather than your user defined one) Tested in 10.0.1.4310 & 16.0.0.1535. If I omit the THEN I get the same error as you were getting.

A pure T-SQL version would be:

BEGIN
  DECLARE @tmp BIT
  SET @tmp = 0
  IF @tmp = 1
   EXEC dbo.sa_info
  ELSE
   PRINT 'Path, which is choosen'
END

which also works on both 10.0.1.4310 & 16.0.0.1535. Don't forget that if you use T-SQL EXEC the parameters have to be specified individually (not in brackets):

[ EXECUTE ] | [ EXEC ][ @return_status = ] [creator.]procedure_name [ argument, ... ]
argument :
 [ @parameter-name = ] expression
| [ @parameter-name = ] @variable [ output ]


How to represent foreign key in an Extended ER Model in Chen notation? This is a spin-off of the question at How to represent foreign key in an ER diagram? which is too vague about the needed notation.
There the idea of underlining FKs with a dotted line is stated. I also remember this from an exam. I am not sure though, it could have been a double underlining as well, this question is not determined to the dotted line. I have searched a bit, finding that a dotted line represents a weak key attribute.

Source: https://conceptdraw.com/a977c4/p1/preview/640/pict--chen's-erd-design-elements---er-diagram-(chen-notation).png--diagram-flowchart-example.png
Can I use a certain format to show FKs in an EERM Extended Entity-Relationship Model in Chen notation?

I think I found the answer, it is more or less not possible and anyway not intended in an ERM. There is a small workaround left in using double underlining.
There is simply no FK to mark in an official ERM. See http://faculty.juniata.edu/rhodes/dbms/ermodel.htm:

E-R Model is not SQL-based. It's not tied to any particular logical implementation of a DBMS. It is a conceptual and semantic model, which attempts to capture meanings rather than an actual implementation.

DO NOT THINK OR START WITH TABLES--YOU WILL BE MISGUIDED ON RELATIONSHIPS AND SOME ATTRIBUTES.

...

Foreign key: term used in relational databases (but not in the E-R model) for an attribute that is the primary key of another table and is used to establish a relationship with that table where it appears as an attribute also.

And in https://en.m.wikipedia.org/wiki/Weak_entity:

In a relational database, a weak entity is an entity that cannot be uniquely identified by its attributes alone; therefore, it must use a foreign key in conjunction with its attributes to create a primary key. The foreign key is typically a primary key of an entity it is related to.

...

An identifying relationship is one where the primary key is populated to the child weak entity as a primary key in that entity.

Thus, the weak key attribute is not equal to a foreign key, it is simply a primary key of another entity needed to reach an entity’s uniqueness (a weak entity is effectively the interim entity that you need to dissolve an M to N relation, using the PKs of “both sides” as attributes; this is out of my course script). Thus the dotted line is not the format for FKs.
Result:
Then, only double underlining remains as an unofficial convention which is up to now used at the university. And mind that showing the FKs is not needed anyway, a named relation implies it and should not explain it, an ERM is not about FKs; do not think in tables.


How to turn off header only in psql (postgresql) I'm using PostgreSQL 9.1.X

I am trying to build psql script to print results without a header but including a footer.

http://www.postgresql.org/docs/9.1/static/app-psql.html

From the document above

\pset tuples_only

will turn both header and footer off. and

\pset footer off

will turn footer off only.

Is there a way in psql to turn the header off and keep the footer on?

I know there are many ways to work around this issue using shell/perl/whatever text tool you like, however I am wondering why there is a config for the footer but not one for the header?

id <--this line I don't want
---- <-- this line I don't want either
 1  <-- this line, yes
(1 row) <-- yes, I want it!

When executing psql from shell you can use -t (prints tuples only) option:

$ psql -t -c "SELECT version();"
 PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

Within psql use \t to turn off printing header and row count.


Which SQL Developer Version will be compatible with Oracle 9i My Company is using Oracle 9i. I want to connect to the database. I'm planning to use SQL Developer. Which version will be compatible Oracle 9i?

Since SQLDeveloper is a Java program using a jdbc driver, the restrictions are made by the version of the jdbc driver. The v10 and v11 drivers can connect to v9 without any problems. For older version databases you might need to get the v8 or v9 jdbc drivers.


SQL Server recovery model cannot be changed I need your advice. Is there any way to prevent any user from changing the recovery model? It should throw an error when the user tries to change recovery model of SQL server.

To change the recovery model requires the ALTER permissions on the database. You can revoke or deny that permission to a user and it will prevent them from altering the recovery model.
Note that denying permissions overwrites any grants, ensure you test any permissions changes before applying to a production server!

Required permissions
Requires ALTER permission on the database.

Ref: https://msdn.microsoft.com/en-au/library/ms189272(v=sql.120).aspx


Database version is higher than SSMS and throwing error when attempted to create table I have installed VS 2017. After that I have installed SQL Server 2014.

Whenever I create a DB using SSMS and attempt to create a table it returns the following error:

  This backend version is not supported to design database diagrams or
  tables. (MS Visual Database Tools).

When creating the database using SSMS the compatibility level has no value to select from.

Version of the SQL Server is 2017 and version of SSMS is 2014. How do I resolve this?

As there any way to use SQL Server 2017 with SSMS 2014?

AS MSDN Blog Here  Use the SSMS version designed for your database version. You can use the command select @@version; to check which version of SQL Server you are actually using.  Update your SSMS to the latest version and try.  You may have the latest version of SQL Server and older version of SSMS.

To Download the latest SQL Server Management Studio (SSMS) https://msdn.microsoft.com/en-us/library/mt238290.aspx

For your further ref https://stackoverflow.com/questions/25146474/the-backend-version-is-not-supported-to-design-database-diagrams-or-tables


Moving Databases between Instances of different Versions of SQL Server I found out SQL Server Developer Edition is now free.

I would like to use my older databases from SQL Server Express 2016 to a new instance of SQL Server Developer 2017.

I want all the data objects: indexes, triggers, etc. to be moved together with the data files.

I am trying to figure out the ways in which I can do this. I am pretty sure these would work between instances of the same version, but I am not sure if it would work if moving between different versions of SQL Server:

Backup Old and Restore to New
Script Old and run the script on New
Detach Old and reattach it on New

Are these 3 workable? Are there any other ways?

The instances are on the same machine, and I have not yet installed SQL Server developer edition.

Migration does not need to be immediate, but at most within a week or so.

The best way will be to use backup-restore - using start-dbamigration .. This will take care of migrating entire instance including all databases, logins, jobs, etc.

Make sure you use -BackupRestore switch with appropriate network location

e.g.

  Start-DbaMigration -Source sql2016 -Destination sql2017 -BackupRestore -NetworkShare \nas\sql\migration


VLF count suddenly growing and unable to reduce it Owing to reductions in IT resources, I am now finding myself having to manage this with limited knowledge and so am seeking advice, please.

We have 2 SQL servers with replication of multiple databases between them. The high VLF count is coming from the "parent" and started to be reported on 21st February 2020 and has continued to grow on odd days, despite my efforts. These servers are in production and in use 24/7.

The servers are running SQL Server 2008 R2 (and yes I do know that is out of support, but there is no money at all to do anything about that) and are in Full Recovery model.

There are 3 databases that are warning of high VLF count and so what I have tried to do it this:

Back up the log file using:

USE dbName
GO
BACKUP LOG dbName TO DISK='M:\dbNamelog2020_02-24.bak'
GO

Shrink the log using the SSMS GUI:

right click dbName
choose Tasks > Shrink > Files
change the drop down from Data to Log and leaving all other choices as their default

Taking one of the databases as a specific example the VLF count has gone from:

53 on 21/2/20
85 on 24/2/20
101 on 26/2/20
117 on 27/2/20

...and then I ran the above shrink and now it is 103.

The system I have inherited alarms when the threshold for VLFs is over 60, so apparently there is still a problem.

I have followed the same process with 2 other databases and with similar results (i.e. the VLF count remains above 60).

Now the Log file for my above specific example today shows (via SSMS):

Currently allocated space = 59750.38Mb
Available free space = 54423.27Mb (91%)

The Log file has an Initial Size of 59,751Mb and Autogrowth is set to grow By 10000 MB, restricted growth to 2097152 MB.

I hope I have given enough detail that either someone can spot a stupid mistake or illuminate how I can progress with this issue. I thought I read somewhere that it should be possible to work out what is creating these VLF's (especially if they are due to incomplete transactions, such as replication), but if that is true then I cannot discover how to actually identify what is creating them.

Any and all advice will be much appreciated.

Many thanks,
Caspar

The VLFs grow when the log grows
When VLF’s are created, either by autogrow or a manual grow; a certain number of VLF’s are allocated according to the size.
< 64MB will create 4 VLF’s
= 64MB < 1GB will create 8 VLF’s
and > than 1GB will create 16 VLF’s.

This was prior to 2014. From 2014 SQL Server onwards this has changed and can read in more details here : Understanding increasing number of VLFs

A few hundred of VLF is actually not that a big issue and you can re-configure your threshold. To fix this you can shrink the log file to the lowest possible and make it grow to a bigger size at once and then allow autogrowth if required. More on this can be read here :

Fixing vlf issue

VLFs will always increase and only when huge in number will cause performance issues (thousands ).


How often does Postgres or Mysql make the fsync call? How often does Postgres or Mysql make the fsync call?

Does Postgres or Mysql fsync for every write to the Write Ahead log?
Does Postgres or Mysql fsync for every transaction? so if I do a SQL Insert will postgres or mysql fsync immediately?

I can only answer for PostgreSQL:
About sync on the WAL:
The transaction log (WAL) is synced to disk on every commit (unless you set synchronous_commit = off.
In addition, the WAL writer regularly syncs the WAL; this is governed by wal_writer_delay (this will only take effect if WAL hasn't been synced for a while). Also, if more than wal_writer_flush_after bytes have been written without a sync, data will be synced to disk.
About sync on the data files:
Data files are normally synced during checkpoints.
Since PostgreSQL uses buffered I/O, there is the danger that too many dirty blocks in the file system cache cause write spikes. Since version 9.6, PostgreSQL has various *_flush_after parameters that avoid that by regulary syncing data when a lot is written:

bgwriter_flush_after (default 512K) makes the background writer flush data to disk after it has written that amount of data
backend_flush_after (default 0 = disabled) makes the client session flush data to disk after it has written that amount of data
checkpoint_flush_after (default 256KB) makes the checkpointer flush out data after writing this much, rather than flushing all at the end


Candidate key = Key = Minimal Superkey? I got a little confused by all these keys. As I understand

A key of a relational schema =
The minimal subset of a superkey that is still a key =
A candidate key

Is this correct or not?

Just found out very neat explanation:

superkey: a set of attributes which will uniquely identify each tuple in a relation

candidate key: a minimal superkey

primary key: a chosen candidate key

secondary key: all the rest of candiate keys

prime attribute: an attribute that is a part of a candidate key (key column)

nonprime attribute: a nonkey column


How to find maximum of average grouped by in sql I have three tables Faculty {id, name}, Student {id, faculty_id, name}, Course_student {id, student_id, mark}

And i would select top student in each faculty

I'm tired to perform this in multiple ways but i can't

SELECT Faculty.name, Student.name, MAX(AVG(mark))
FROM (Faculty inner join Student On Faculty.id = Student.faculty_id) inner join Course_student On Student.id = Course_student.student_id
group by Faculty.name

I cannot check the results but I hope that the query below should work.
In future, please, add the DDL (Data Definition Language) including the scripts for table creation and also to insert a few records that will ilustrate the expected result.

WITH CTE AS (
SELECT Faculty.name AS Faculty_Name,
       Student.name AS Student_Name,
       AVG(mark) AS Avg_Mark,
       ROW_NUMBER() OVER(PARTITION BY Faculty.name ORDER BY AVG(mark) DESC) AS Row
FROM Faculty
INNER JOIN Student On Faculty.id = Student.faculty_id)
INNER JOIN Course_student On Student.id = Course_student.student_id
GROUP BY Faculty.name
)
SELECT Faculty_Name, Student_Name, Avg_Mark
FROM CTE
WHERE Row = 1


Can not login user SEC_MGR in SQLPLUS I can login user SEC_MGR when I use the TOAD tool. But, It has error when i login by SQLPLUS:

Because this version I am using is Oracle 12c, so user SEC_MGR is replaced by SYSTEM user.

Try to login with user SYSTEM/manager to access.


Oracle RMAN - MAXPIECESIZE ignored for Level 0 backups I'm having issues with Oracle 12.1 rman - specifically the MAXPIECESIZE parameter not being honored by my Level 0 backups.

This value is set in rman as

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/rman/prdb/rman/%U' MAXPIECESIZE 10 G;

and the level 0 backup is triggered via the following script:

RUN {
  BACKUP INCREMENTAL LEVEL 0 TAG 'prdb_incr' DATABASE;
  BACKUP DEVICE TYPE DISK TAG 'prdb_incr' ARCHIVELOG ALL NOT BACKED UP DELETE ALL INPUT;
}

However, this is creating a single 30G file rather than splitting it up into 10G chunks.

[oracle@localdomain]$ ls -ltrh /u01/rman/prdb/recovery/PRDB/backupset/2018_05_20/
total 32G
-rw-r-----. 1 oracle oracle 309M May 20 09:50 o1_mf_nnnd1_PRDB_INCR_fj2jb825_.bkp
-rw-r-----. 1 oracle oracle 1.2G May 20 09:50 o1_mf_annnn_PRDB_INCR_fj2k6m7d_.bkp
-rw-rw----. 1 oracle oracle  30G May 20 11:35 o1_mf_nnnd0_PRDB_INCR_fj2pj8p0_.bkp
-rw-rw----. 1 oracle oracle 289M May 20 11:36 o1_mf_annnn_PRDB_INCR_fj2qdwf2_.bkp

I have tried explicitly setting the CHANNEL DEVICE as part of the backup script  (immediately before the BACKUP INCREMENTAL line) - this made no difference.

Also maybe related, the location does not seem to match what I am specifying above - /u01/rman/prdb/recovery vs /u01/rman/prdb/rman - this seems to match up with db_recovery_file_dest set in the database:

db_recovery_file_dest            string  /u01/rman/prdb/recovery

but I can't see any options in there that would override the MAXPEICESIZE

To be clear - I'm NOT concerned about the location just the file sizes.

Is there another setting somewhere that I am missing? Do I need to remove the db_recovery_file_dest parameter maybe?

Thanks

Backup summary of the latest backup:

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1961    Incr 0  29.64G     DISK        00:15:06     20-MAY-18
        BP Key: 1964   Status: AVAILABLE  Compressed: NO  Tag: PRDB_INCR
        Piece Name: /u01/rman/prdb/recovery/PRDB/backupset/2018_05_20/o1_mf_nnnd0_PRDB_INCR_fj2pj8p0_.bkp
  List of Datafiles in backup set 1961
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/system01.dbf
  2    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/users02.dbf
  3    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/sysaux01.dbf
  4    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/undotbs01.dbf
  5    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/example01.dbf
  6    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/users01.dbf
  7    0  Incr 755820030  20-MAY-18 /data/oracle/app/oracle/oradata/prdb/users03.df
  8    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/doco_small.dbf
  9    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/doco_large.dbf
  10   0  Incr 755820030  20-MAY-18 /data/oracle/app/oracle/oradata/prdb/users04.dbf
  19   0  Incr 755820030  20-MAY-18 /data/oracle/app/oracle/oradata/prdb/audi_tbs.dbf

Try removing the MAXPIECESIZE from the configuration of the channel, and use SECTION SIZE 10G on the backup command.  The following is working for me:

run {
        backup section size 10g incremental level 0  as compressed backupset archivelog all database;
        delete noprompt obsolete;
}

You may need to set paralellism to > 1:

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;

If that does not solve the problem, please post the output of show all;


Slow enumeration of schemas and tables in a largish database We have a Postgresql 8.4.8 database server on a Debian system with a database that has 500+ schemas with roughly 300+ tables each. The schemas and tables are created by scripts, we cannot influence the process much short of deleting unnecessary schemas every now and then. Perhaps there were 5000+ schemas during the lifetime of this database, of which 4500- are gone now.

Opening the database using pgAdmin3 takes ages. Opening a schema takes ages. Autocompletion in psql is horribly slow. This does not happen with "small" databases. I have tried VACUUM ANALYZE pg_tables, but this is not supported.

Are there some cleanup tasks that need to be carried out to handle this situation? Or perhaps there is a tuning parameter that can be set? An index on a system table missing or outdated?

As a side question: Is it possible to group several schemas so that there is yet another hierarchy level between "database" and "table"?

This can also be a shortcoming of pgAdmin. There have been a number of improvements for recent versions. Which one do you use? 1.14.2 is the current version (as of 2012-05-21).

Are queries from the command line interface psql slow, too?

Apart from that, you need to keep vacuuming all your tables in the database, not just pg_tables. I would strongly advice to enable the auto vacuuming daemon.

Also, pg_tables is not relevant at all. It is a system view to implement the SQL standard information schema. The relevant system tables would be pg_namespace, pg_class, pg_attribute and others. But it is pointless to target just one of them. For your kind of setup you need to be vacuuming the whole catalog. And it is very advisable to vacuum all of your database on a regular basis anyway. So, let me repeat: use autovacuum.

For starters, you can run in a Debian shell:

vacuumdb -afz
reindexdb -a
reindexdb -s

The last line targets system tables for possible bloat of indexes in the catalog in particular. See man reindexdb or the fine manual.

Finally, another option would be to upgrade to PostgreSQL 9.1. There have been a number of improvements for VACUUM and ANALYZE (among many others). In particular VACUUM FULL has been rewritten for 9.0 and does a much better job now.


A tuple relational calculus equivalent for the given SQL query Well, here's a SQL query:

select Employee.Name,Department.Name
from Employee,Department
where Employee.Dept_no=Department.No

I have tried a tuple relational calculus on it as:

{t|Ǝ s ∈ Employee (s[Name]=t[Name] ^
Ǝ u ∈ Department(u[Name]=s[Name]^u[No]=s[No]))}

Is my approach correct? if not, then can anyone help me understand?

A tuple relational calculus expression should be written with the usual language of first order logic.

In this case, for instance, you could write:

{ [e.Name, d.Name] | Ǝ e ∈ Employee, Ǝ d ∈ Department . e.Dept_no = d.No }

(a part from minor variations on the notation used, like “:” instead of “.”).

The idea is that you obtain the result of the query (a well formed formula of a first order logic), by finding the elements of the relations that, substituted to the free variables of the formula, make the formula true.

In this case, we have two variables, d and e, that range on Departments and Employees respectively, a condition that must be true (e.Dept_no = d.No), and from every pair of tuples of the relations that satisfy this condition, you add to the resulting set a tuple formed by the Name of e and the Name of d (in other words you perform a simple join).

Note that the structure of the expression can be easily mapped to the SQL query (and actually this language is the origin of the SQL language, together with other additions). This can be a suggestion on how to write other queries similar to this one.


Getting error while creating a New Connection using Studio 3T for Mongo DB I tried to create a new connection with Studio 3T for Mongo DB. I don't have a previous DB connection and this is the new one that I am trying to create it. I can't able to figure out why this is happening.

When tried to do so, I got an error stating:

Error Details :

  Database error (MongoSecurityException): Exception authenticating
  MongoCredential{mechanism=SCRAM-SHA-256,
  userName='{{my_db_username}}', source='admin', password=,
  mechanismProperties={}}.

This is StackTrace:

  Stacktrace: |_/ Database error (MongoSecurityException): Exception
  authenticating MongoCredential{mechanism=SCRAM-SHA-256,
  userName='sociofyDB_rifaye', source='admin', password=,
  mechanismProperties={}} |____/ Mongo Server error
  (MongoCommandException): Command failed with error 18
  (AuthenticationFailed): 'Authentication failed.' on server
  localhost:27017. |____... |____... The full response is: |____... {

  |____... "ok" : 0.0,
  |____... "errmsg" : "Authentication failed.",
  |____... "code" : NumberInt(18),
  |____... "codeName" : "AuthenticationFailed"
  |____... }

EDIT: I see this error majorly when I try to provide the authentications. This does not occur when I connect normally without the authentication(username/pwd).

As per MongoDB articles blog here The Connection Manager in Studio 3T makes it easy to connect to MongoDB, whether it is cloud-hosted or on-premise, or through a direct connection or a replica set.

Create a new connection

Open Studio 3T.
Click on the Connect button either in the global toolbar or under
the Recent Connections tab. This will open the Connection Manager.
Click on New Connection. This will open the New Connection window.

  Please note that with the release of Studio 3T 2018.6 and support for
  SCRAM-SHA-256, it will no longer be possible to connect to MongoDB
  version 2.6, which reached its end of life in October 2016. Only
  MongoDB versions 3.0 and higher are supported.

Studio 3T supports various settings nested under the Server, Authentication, SSL, SSH Tunnel, and Advanced tabs:

Server

Direct Connection
Replica Set or Sharded Cluster

Authentication

Basic (SCRAM-SHA-256)
Legacy (MongoDB-CR or SCRAM-SHA-1)
X.509
Kerberos (GSSAPI) - Enterprise only
LDAP (PLAIN) - Enterprise only

SSL

Accept any server SSL certificate
Use own Root CA file
Accept trusted server SSL certificates
Use Client Certificate
Allow invalid hostnames
User Server Name Indication

SSH Tunnel
 - Use SSH tunnel to connect

Advanced

Max. connection idle time
Server selection timeout
Socket timeout
Connect timeout

As I am able to see from attached screen shot as Studio 3T is showing the TRIAL License. Instead of Studio 3T IDE you can use the Robo 3T (formerly Robomongo) is the free lightweight GUI for MongoDB.

For example the simple new connection of Robo 3T (Robomongo) will be as follows:

For further your ref here , here and here


Regular expression problem SELECT FIRSTNAME,LASTNAME, EMAIL
   FROM CUSTOMERS
   WHERE REGEXP_LIKE(EMAIL,'[A-Z0-9._%-]{1,10}@[a-z0-9._%-]{1,5}\.[net|com]','i');

I know this is not practical or make a ton of sense, but its a constraint I'm working with: email address with upto 10 characters prior to @ followed by upto 5 characters, then a '.' and finally the .net or .com extension. This works for the most part except the 1-10 constraint on the first half does not appear to do what I'd like.
It pulls in pretty much any number characters prior to the '@'.
For example it should accept jreed but not jodywilliamreed as the later is more than 10 characters. Anyone see what I've done here that would cause this problem?
edit: I'm using sql developer with oracle

Try adding the ^ character to your regex, which means to match the position at the beginning of the string, like this

^[A-Z0-9._%-]{1,10}@[a-z0-9._%-]{1,5}\.[net|com]


How do I connect to a SQL Server 2008 instance on my network? I have 2 SQL instances in 2 virtual machines. I'm able to see the other instance in one machine but I cant connect to it.

Probably some permission problem...but I don't know where to look.

Can someone help me?

edit:
the first problem was in fact not having TCP/IP enabled, solved that and the error changed now I have error 18452. (I'm trying to connect with windows authentication, also tried with sql authentication in that case I get error 18456)

Both SQL instances are default

edit2:
After changing the autentication to windows and SQL, I can login in one of the instances using sql autentication (user SA), on the other...it gives me error 18470, but everything looks ok with that login (at least for me... maybe one of you can tell me were should I look)

edit3:
After enabling login sa everything works as expected in SQL authentication mode.

I change my question to, what if I wanted windows authentication mode? how could I do it?

To answer your question we really need more information like errors.

Firstly need to ensure your instance allows remote connections by configuring network protocols in SQL server configuration tool.

Also need to check if your connecting to a default instance or named..

If your still stuck post more information about your scenario.


Select rows where value of second column is not present in first column I have this table structure:

 id1 id2
 1   2
 1   3
 1   4
 2   1
 2   5

I need to build a query to select id2 where id1 is not in id2. For example if id1 = 1 then only id2=3 and id2=4.
I tried this one:

SET @a=1;
SELECT DISTINCT x.id2
 FROM tt x, tt y
 WHERE x.id1=@a AND x.id1 != y.id2;

but it's not working.

For the simple case of a single given id1:
SELECT DISTINCT x.id2   -- DISTINCT only needed if there are dupes
FROM   tbl x
WHERE  x.id1 =  @a
AND    x.id2 <> @a;

Generally, there are basically four techniques to do what you are after. Both columns could just as well be in different tables, almost the same problem.
NOT EXISTS
SELECT DISTINCT x.id2
FROM   tbl x
WHERE  <some condition>
AND    NOT EXISTS (
    SELECT FROM tbl y
    WHERE  <some condition>
    AND    y.id1 = x.id2
    );

LEFT JOIN / IS NULL
SELECT DISTINCT x.id2
FROM   tbl x
LEFT   JOIN tbl y ON y.id1 = x.id2
                 AND <some condition for y>
WHERE  <some condition for x>
AND    y.id1 IS NULL

EXCEPT
SELECT DISTINCT id2
FROM   tbl
WHERE  <some condition>

EXCEPT ALL            -- ALL to make it faster - no dupes left after DISTINCT
SELECT tbl.id1
FROM   tbl
WHERE  <some condition>

NOT IN
SELECT DISTINCT x.id2
FROM   tbl x
WHERE  <some condition>
AND    x.id2 NOT IN (
    SELECT DISTINCT id1
    FROM   tbl y
    WHERE  <some condition>
    );

You'll have to test which one is fastest for you. Benchmarks disagree. It depends on data distribution and other details. NOT IN rarely wins. It's mostly one of the first two.
As @ypercube commented: if (id1, id2) is unique, the DISTINCT clause is not needed - except the one in the NOT IN sub-query, which is meant to help performance.
Related answer with more details for PostgreSQL on SO:

Select rows which are not present in other table


varchar(MAX) text cuts off while going more than 8000 characters inside a procedure I have a procedure in which i have declared a variable with datatype varchar(MAX),

declare @str varchar(MAX);

set @str='select * from Table1...'

print (@str);
exec (@str);

but when the text written inside @str goes above 8000 characters (i.e. 8193 characters) it cuts off the string and prints only 8000 characters and executing it gives error off course.

I have tried 2 solutions after some search, but it doesn't work.

1) I have tried using 2 variables of same type "varchar(MAX)", and concat it at the time of execution, but doesn't works.

2) I have tried to cast "into varchar(MAX) again" both the variables before concatinating it, and then execute it, but that also doesn't works.

SSMS cannot display a varchar greater than 8000 characters by default.

However, it can store a varchar(max) value up to 2GB.

You could set up a loop and display "chunks" of the @str data, using an 8,000 character chunk size.

This answer will show you how confirm the actual length of the @str value in memory.

And this MSDN article discusses execute. In particular:

  In earlier versions of SQL Server, character strings are limited to 8,000 bytes. This requires concatenating large strings for dynamic execution. In SQL Server 2005, the varchar(max) and nvarchar(max) data types can be specified that allow for character strings to be up to 2 gigabytes of data.

This implies that the error you are receiving is something else, not a result of a truncate operation on your @str value.


Availability Groups Service Account has over 25000 sessions open I am running Availability Groups on SQL Server 2014 with one database in the group. Today, the server was responding very slowly and when I got on the server I found that the Availability Groups service account had over 25000 sessions open. Every session was to the MSDB database with the same query,  The query was:

(@P1 nvarchar(max),
 @P2 uniqueidentifier,
 @P3 int,
 @P4 int,
 @P5 int)
DECLARE @logTextWithPreAndPost nvarchar(max) set @logTextWithPreAndPost = N'' + @P1 + N'';
EXECUTE msdb.dbo.sp_agent_write_sysjobstep_log
 @job_id = @P2,
 @is_system = @P3,
 @step_id = @P4,
 @log_text = @logTextWithPreAndPost,
 @append_to_last=@P5

Does anyone know why it would hold so many sessions open or what I can do to stop it.

This is most likely because you have enabled the Log to table option in the agent job step.

Run

SELECT spid,
       program_name,
       db_name(dbid) AS databasename
FROM   sys.sysprocesses
WHERE  spid > 50

or if you have sp_whoisactive installed (http://whoisactive.com/)

EXEC sp_whoisactive
     @output_column_list = '[%dd%][session_id][sql_text][%database%][login_name][program_name]' /* if they are sleeping spids, add @show_sleeping_spids = 2*/

Get the uniqueidentifier of the job from the program_name, then run

SELECT name
FROM   msdb.dbo.sysjobs
WHERE  CONVERT(BINARY(16), job_id)=0x[id]

Replace 0x[id] with your id. Once you get the job name, go to the job step, then un-check the log to table option.


Heavy performance drop on Progress query, looks like based on string character inside table I'm launching following query on a Rel 11.6 Progress database:
FIND FIRST Table1 WHERE Table1.Field1 = 1
                    AND Table1.Field2 >= 2
                    AND TRUE
                    AND
  CAN-FIND(Table2 WHERE Table2.Field1 = Table1.Field3    /* join */
                    AND Table2.Field2 = 0
                    AND Table2.Field3 = "WHATEVER").

On both tables, the necessary indexes are created and rebuilt.
The problem is the following: when the criterion Table2.Field3 = "WHATEVER" is present (although that field is present in an index), the query takes entire minutes to run, otherwise the query is done very rapidly (in a matter of seconds).
What can explain such behaviour in a Progress 11.6 database and are there any profiling possibilities for monitoring what's happening? (I'm working with the appBuilder/procedure editor "IDE")
Thanks in advance

It isn't quite clear to me but, as I read the question, you are saying that you have two distinct versions of the code. One with that phrase, another without.
I expect that in the version with that bit of code it is slow because there is no index where field1, field2, and field3 are in consecutive order. In a case like that you will have a table-scan to resolve the query.
To illustrate using the standard sports2000 database:
define variable dbx as int64 no-undo.

find _myconnection no-lock.
find _connect no-lock where _connect-id = _myconn-userid + 1.
find _userio  no-lock where _userio-id = _myconn-userid + 1.

display _connect-name _connect-type _connect-device.

dbx =  _userio-dbaccess.

find first order no-lock
  where order.orderStatus = "shipped"
    and order.shipDate    >= 2/10/1997
//  and can-find( employee where employee.lastName = "parker" )
    and can-find( employee where employee.firstName = "karen" )
  no-error.

find _userio  no-lock where _userio-id = _myconn-userid + 1.
dbx =  _userio-dbaccess - dbx.

display orderNum dbx.

When the CAN-FIND() references employee.lastName (commented out above) you will see that "dbx" = 4. That is to say that your query accessed a total of 4 db blocks (2 index entries and 2 data records). When you run it with employee.firstName it takes 114 db accesses because, while firstName is an indexed field, it is the 2nd component of a compound index and thus cannot be used to bracket the query. Obviously in the tiny sports2000 database this has very little impact on runtime. But the 25x difference in db access operations shows the table-scan in action and hints at the impact that would be felt in a real database.
In OpenEdge 4gl (aka "ABL") queries are optimized at compile time by following rules. There are many rules but the "golden rule" is to have equality matches on leading components of composite indexes. There is a lot of information on the topic shared in the various user groups. A good repository of past presentations can be found at PUG Challenge. The various "pick an index" talks are a good place to get started.
(The OpenEdge SQL engine uses a cost-based optimizer - but we know that this is a 4GL query since the FIND FIRST and CAN-FIND syntax is being used.)
You can see which indexes are being selected by compiling your code with the XREF option. If you see WHOLE-INDEX in that output that is telling you that a table scan is required. (If you do not see that it is not a sign that your query is efficient - just that you didn't trigger that particular warning.)
You can also profile activity at run time using the virtual system tables or by using a tool such as ProTop (if you go with ProTop you probably want to investigate "programmer mode"). Either approach will reveal which indexes are actually being used and how much activity there is. If you carefully control your test environment you can learn quite a lot.


Open SQL Server file I've downloaded an unspecified SQL file with no file extension. When reading the first 10 lines of the file it shows among unreadable characters:

TAPE
Microsoft SQL ServerSPA
L
SFMB
8SSET
4Oct_2015_Latest backupDBA\TSSHTSPAD2
VOLBX
C:\DBASPAD
MSCI8
Oct_2015_LatestDBASFGI
PRIMARYSFINf
Oct_2009_DataC:\Program Files\Microsoft SQL Server 2000\MSSQL\data\Oct_2015_Latest.mdfSFINl
Oct_2009_LogC:\Program Files\Microsoft SQL Server 2000\MSSQL\data\Oct_2015_Latest_log.ldf
SPAD
MSDA
APAD
MQDA

I have done some reading on .mdf files, but cannot find out how to read this file. I am using Linux. Is there a way to read this in Linux or Windows?

The RESTORE HEADER ONLY command gave this:

Oct_2009_Data
C:\Program Files\Microsoft SQL Server 2000\MSSQL\data\Oct_2012_Latest.mdf
D
PRIMARY
1005584384
35184372080640
1
0
0
00000000-0000-0000-0000-000000000000
0
0
0
512
1
NULL
968000005802100003
4D42441E-4BCF-4351-9BAD-A03C53801BEF
0
1
NULL
NULL

Oct_2009_Log
C:\Program Files\Microsoft SQL Server 2000\MSSQL\data\Oct_2012_Latest_log.ldf
L
NULL
5477695488
35184372080640
2
0
0
00000000-0000-0000-0000-000000000000
0
0
0
512
0
NULL
0
00000000-0000-0000-0000-000000000000
0
0
NULL
NULL

And RESTORE FILEONLY gave INCOMPLETE in the first column and the rest were null.

A MDF is a primary database file, ndf is a non-primary database file, ldf is a log database file. Together, these objects create a database in SQL Server. These files can be attached or detached from the SQL Server Instance.

Hierarchy is: SQL Server Instance -> SQL Server Databases -> SQL Server Schema -> SQL Server Tables.

The most common way to attach and detach a database is to use SSMS. (SQL Server Management Studios) SSMS has recently been placed as a standalone install, you could download SSMS on a Windows machine and then follow this KDB to attach the database to your Instance. I would recommend using caution when attaching unknown databases to your instance. You will also need to attach the log file and any other files that were included.

Once it has been attached, you may be able to view the objects inside it. This will depend on whether the database is encrypted with Transparent Data Encryption, what user permissions are set to, and other factors.

Objects include tables (this has data inside it which you are probably looking for), stored procedures (programmed scripts that can be executed and run), views (queries that look at tables to retrieve a set of data in a specific fashion) and may other things.


Best High Availability Solution for SQL Server 2014 Please we are about to implement High availability for for SQL server 2014 on our new infrastructure but i need to know which is best between SQL Failover Clustering and Always On High Availability Group. We needed to consider below as part of our requirement ;
1. Automatic Failover
2. Ensure that if storage hosting SQL database fails or get corrupted all data are moved to another Storage and still work perfectly with Automatic Failover
3. Needs to ensure that secondary(passive) node can used for reporting

I will be very happy if i can get assisted with above queries as well as advantages/Disadvantages of both Always on High availability and SQL Failover Clustering

Always On Availability group will server your purpose for which you will need Enterprise Edition of SQL Server license. It will provide :

Automatic failover is available when the AG is configured with Synchronous-commit mode with automatic-failover.Failover Modes

Since AlwaysON has data separated for each instance that is part of the AG, the storage corruption for one of the node will leave you safe with the other node/nodes.

Readable secondary will help you in reporting purpose.
Active Secondary

The only disadvantage/Limitation of AG I would say is the need of Enterprise edition.


Multiligual database design I assume that the best approach for many languages (5 and more) is concept based on:

    CREATE TABLE `language` (
    `language_id` char(2) NOT NULL,
    `collation` varchar(64) NOT NULL,
    PRIMARY KEY (`language_id`)
);

CREATE TABLE `product` (
    `product_id` int(11) NOT NULL auto_increment,
    PRIMARY KEY (`product_id`)
);

CREATE TABLE `product_translation` (
    `product_id` int(11) NOT NULL,
    `language_id` char(2) NOT NULL,
    `group_id` int(11) NOT NULL,
    `name` varchar(50) NOT NULL,
    `url` varchar(50) NOT NULL,
    `price` decimal(9, 2) NOT NULL,
    `description` text NOT NULL,
    UNIQUE KEY `language_id_2` (`language_id`, `url`),
    KEY `language_id` (`language_id`, `group_id`, `name`),
    FULLTEXT KEY `name` (`name`, `description`),
    FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`),
    FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`),
    PRIMARY KEY (`product_id`, `language_id`)
);

But what if content is filled by user? Should this structure be used only for static content ? Registred user can at any time change his language settings also the data that he is adding (private messages,articles) can be added in any language we cannot force him to add only english content. Should all data be stored in one table and column (body), am I right ?

For example private messages:

    CREATE TABLE `msg_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thread_id` int(11) NOT NULL,
  `body` text NOT NULL,
  `priority` int(2) NOT NULL DEFAULT '0',
  `sender_id` int(11) NOT NULL,
  `cdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

You can indicate what language a message body is in, assuming that you know what language it is because the user told you so or because you have a process that reads the message and guesses the language.  Therefore your message table could look something like this:

CREATE TABLE `msg_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thread_id` int(11) NOT NULL,
  `body` text NOT NULL,
  `priority` int(2) NOT NULL DEFAULT '0',
  `sender_id` int(11) NOT NULL,
  `cdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `language_id` char(2) NOT NULL,
  FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

NOTE: Unless you plan on charging a different price in different languages, you don't want to keep the price in the product_translation table.  If you want to differentiate the price based on markets then you are better to split price into its own intersection table between product and whatever is suitable, like country or region or currency.