Query performance of searching pattern Posted the same question on SO, but did not get answer.

As per Andrey Nikolov comment, tried with Trigram, but the table name and column(more than 50 columns) are dynamic i mean will get from front end.

Question:

I have the following sample data for understanding the requirement.

Table:

create table ft_test
(
    col1 int identity(1,1),
    col2 varchar(max)
);

insert into ft_test values('John A Henry');
insert into ft_test values('Dsouza mak Dee');
insert into ft_test values('Smith Ashla Don');

Creation of FULLTEXT INDEX:

CREATE UNIQUE INDEX UIX_test_Col1 ON ft_test(col1);

CREATE FULLTEXT CATALOG ftcat
WITH ACCENT_SENSITIVITY = OFF

CREATE FULLTEXT INDEX ON ft_test
(col2 LANGUAGE 1033)
KEY INDEX UIX_test_Col1
ON ftcat
WITH STOPLIST = SYSTEM

Notes:

  Search for the given pattern in the given column of the table.
  I have more than 200 millions of records in the table.
  Created FULLTEXT index on required column.

Looking for best performing search query.

Givens:

  Pattern: oh
  Column to  Search: col2

Expected Output:

col1    col2
----------------------
1       John A Henry

Try:

Using FULLTEXT Index: CONTAINS

SELECT col1, col2
FROM ft_test1
WHERE CONTAINS(col2, '"*oh*"')

No output.

Using FULLTEXT Index: FREETEXT

SELECT col1, col2
FROM ft_test1
WHERE FREETEXT(col2, '"*oh*"')

No output.

Using Pattern Index: PATINDEX

SELECT col1, col2
FROM ft_test1
WHERE PATINDEX('%oh%',col2)>0

Got an output.

Execution Plan: Table Scan

Using character expression: CHARINDEX

SELECT col1, col2
FROM ft_test1
WHERE CHARINDEX('oh',col2)>0

Got an output.

Execution Plan: Table Scan

Using LIKE.

SELECT col1, col2
FROM ft_test1
WHERE col2 LIKE '%oh%'

Got an output.

Execution Plan: Table Scan

...tried with Trigram, but the table name and column(more than 50 columns) are dynamic...

There is no particular problem with using the trigram technique I wrote about in Trigram Wildcard String Search in SQL Server with multiple columns, dynamic SQL, or 200 million records.
The comments on that article show people using the basic technique with multiple columns, much larger tables, and even with encryption. The modifications needed are normally quite straightforward, assuming the person making the changes understands the basic underlying implementation shown in my article.
Note though that trigram search requires at least a three-character substring to search for (as the name suggests). If you really need to perform two-character searches (as in the question example), more modifications would be needed, and you would need to carefully assess the costs and benefits with your data. Without further information, it would seem that a two-character match on hundreds of millions of rows would produce a great number of matches.
If n-gram search is not suitable for you, and you can tolerate some latency, an external solution like Elasticsearch might be worth looking into.
Example
The code below shows one way to extend the basic trigram search to work with three string columns in the same table. For brevity, it does not implement the trigger logic shown in my article to keep the trigrams synchronized with the underlying data. If this is required, the modifications needed follow much the same pattern.
CREATE TABLE dbo.Example
(
    id integer IDENTITY NOT NULL,
    string1 varchar(10) NOT NULL,
    string2 varchar(20) NOT NULL,
    string3 varchar(30) NOT NULL,

    CONSTRAINT [PK dbo.Example (id)]
        PRIMARY KEY CLUSTERED (id)
);
GO
-- 1 million rows
INSERT dbo.Example
    WITH (TABLOCKX)
(
    string1,
    string2,
    string3
)
SELECT TOP (1 * 1000 * 1000)
    -- 10 characters numeric
    REPLACE(STR(RAND(CHECKSUM(NEWID())) * 1e10, 10), SPACE(1), '0'),
    -- 10 characters numeric plus 10 characters [0-9A-F]
    REPLACE(STR(RAND(CHECKSUM(NEWID())) * 1e10, 10), SPACE(1), '0') + RIGHT(NEWID(), 10),
    -- 30 characters [0-9A-F]
    RIGHT(NEWID(), 10) + RIGHT(NEWID(), 10) + RIGHT(NEWID(), 10)
FROM master.dbo.spt_values AS SV1
CROSS JOIN master.dbo.spt_values AS SV2
OPTION (MAXDOP 1);
GO
--- Generate trigrams from a string
CREATE FUNCTION dbo.GenerateTrigrams
(
    @string varchar(255)
)
RETURNS table
WITH SCHEMABINDING
AS RETURN
    WITH
        N16 AS
        (
            SELECT V.v
            FROM
            (
                VALUES
                    (0),(0),(0),(0),(0),(0),(0),(0),
                    (0),(0),(0),(0),(0),(0),(0),(0)
            ) AS V (v)),
        -- Numbers table (256)
        Nums AS
        (
            SELECT n = ROW_NUMBER() OVER (ORDER BY A.v)
            FROM N16 AS A
            CROSS JOIN N16 AS B
        ),
        Trigrams AS
        (
            -- Every 3-character substring
            SELECT TOP (CASE WHEN LEN(@string) > 2 THEN LEN(@string) - 2 ELSE 0 END)
                trigram = SUBSTRING(@string, N.n, 3)
            FROM Nums AS N
            ORDER BY N.n
        )
    -- Remove duplicates and ensure all three characters are alphanumeric
    SELECT DISTINCT
        T.trigram
    FROM Trigrams AS T
    WHERE
        -- Binary collation comparison so ranges work as expected
        T.trigram COLLATE Latin1_General_BIN2 NOT LIKE '%[^A-Z0-9a-z]%';
GO
-- Trigrams for Example table
CREATE TABLE dbo.Example_Trigrams
(
    id integer NOT NULL,
    column_id integer NOT NULL,
    trigram char(3) NOT NULL
);
GO
-- Generate trigrams for string1
DECLARE @column_id integer =
    COLUMNPROPERTY(OBJECT_ID(N'dbo.Example', 'U'), N'string1', 'ColumnId');

INSERT dbo.Example_Trigrams
    WITH (TABLOCKX)
    (
        id,
        column_id,
        trigram
    )
SELECT
    E.id,
    @column_id,
    GT.trigram
FROM dbo.Example AS E
CROSS APPLY dbo.GenerateTrigrams(E.string1) AS GT;
GO
-- Generate trigrams for string2
DECLARE @column_id integer =
    COLUMNPROPERTY(OBJECT_ID(N'dbo.Example', 'U'), N'string2', 'ColumnId');

INSERT dbo.Example_Trigrams
    WITH (TABLOCKX)
    (
        id,
        column_id,
        trigram
    )
SELECT
    E.id,
    @column_id,
    GT.trigram
FROM dbo.Example AS E
CROSS APPLY dbo.GenerateTrigrams(E.string2) AS GT;
GO
-- Generate trigrams for string3
DECLARE @column_id integer =
    COLUMNPROPERTY(OBJECT_ID(N'dbo.Example', 'U'), N'string3', 'ColumnId');

INSERT dbo.Example_Trigrams
    WITH (TABLOCKX)
    (
        id,
        column_id,
        trigram
    )
SELECT
    E.id,
    @column_id,
    GT.trigram
FROM dbo.Example AS E
CROSS APPLY dbo.GenerateTrigrams(E.string3) AS GT;
GO
-- Trigram search index
CREATE UNIQUE CLUSTERED INDEX
    [CUQ dbo.Example_Trigrams (column_id, trigram, id)]
ON dbo.Example_Trigrams (column_id, trigram, id)
WITH (DATA_COMPRESSION = ROW);
GO
-- Selectivity of each trigram (performance optimization)
CREATE VIEW dbo.Example_TrigramCounts
WITH SCHEMABINDING
AS
SELECT
    ET.column_id,
    ET.trigram,
    cnt = COUNT_BIG(*)
FROM dbo.Example_Trigrams AS ET
GROUP BY
    ET.column_id,
    ET.trigram;
GO
-- Materialize the view
CREATE UNIQUE CLUSTERED INDEX
    [CUQ dbo.Example_TrigramCounts (column_id, trigram)]
ON dbo.Example_TrigramCounts (column_id, trigram);
GO
-- Most selective trigrams for a search string
-- Always returns a row (NULLs if no trigrams found)
CREATE FUNCTION dbo.Example_GetBestTrigrams
(
    @column_name sysname,
    @string varchar(255)
)
RETURNS table
WITH SCHEMABINDING AS
RETURN
    SELECT
        -- Pivot
        trigram1 = MAX(CASE WHEN BT.rn = 1 THEN BT.trigram END),
        trigram2 = MAX(CASE WHEN BT.rn = 2 THEN BT.trigram END),
        trigram3 = MAX(CASE WHEN BT.rn = 3 THEN BT.trigram END)
    FROM
    (
        -- Generate trigrams for the search string
        -- and choose the most selective three
        SELECT TOP (3)
            rn = ROW_NUMBER() OVER (
                ORDER BY ETC.cnt ASC),
            GT.trigram
        FROM dbo.GenerateTrigrams(@string) AS GT
        JOIN dbo.Example_TrigramCounts AS ETC
            WITH (NOEXPAND)
            ON ETC.column_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.Example', 'U'), @column_name, 'ColumnId')
            AND ETC.trigram = GT.trigram
        ORDER BY
            ETC.cnt ASC
    ) AS BT;
GO
-- Returns Example ids matching all provided (non-null) trigrams
CREATE FUNCTION dbo.Example_GetTrigramMatchIDs
(
    @column_name sysname,
    @Trigram1 char(3),
    @Trigram2 char(3),
    @Trigram3 char(3)
)
RETURNS @IDs table (id integer PRIMARY KEY)
WITH SCHEMABINDING AS
BEGIN
    DECLARE @column_id integer =
        COLUMNPROPERTY(OBJECT_ID(N'dbo.Example', 'U'), @column_name, 'ColumnId');

    IF  @Trigram1 IS NOT NULL
    BEGIN
        IF @Trigram2 IS NOT NULL
        BEGIN
            IF @Trigram3 IS NOT NULL
            BEGIN
                -- 3 trigrams available
                INSERT @IDs (id)
                SELECT ET1.id
                FROM dbo.Example_Trigrams AS ET1
                WHERE ET1.trigram = @Trigram1
                AND ET1.column_id = @column_id
                INTERSECT
                SELECT ET2.id
                FROM dbo.Example_Trigrams AS ET2
                WHERE ET2.trigram = @Trigram2
                AND ET2.column_id = @column_id
                INTERSECT
                SELECT ET3.id
                FROM dbo.Example_Trigrams AS ET3
                WHERE ET3.trigram = @Trigram3
                AND ET3.column_id = @column_id
                OPTION (MERGE JOIN);
            END;
            ELSE
            BEGIN
                -- 2 trigrams available
                INSERT @IDs (id)
                SELECT ET1.id
                FROM dbo.Example_Trigrams AS ET1
                WHERE ET1.trigram = @Trigram1
                AND ET1.column_id = @column_id
                INTERSECT
                SELECT ET2.id
                FROM dbo.Example_Trigrams AS ET2
                WHERE ET2.trigram = @Trigram2
                AND ET2.column_id = @column_id
                OPTION (MERGE JOIN);
            END;
        END;
        ELSE
        BEGIN
            -- 1 trigram available
            INSERT @IDs (id)
            SELECT ET1.id
            FROM dbo.Example_Trigrams AS ET1
            WHERE ET1.trigram = @Trigram1
            AND ET1.column_id = @column_id;
        END;
    END;

    RETURN;
END;
GO
-- Search implementation
CREATE FUNCTION dbo.Example_TrigramSearch
(
    @column_name sysname,
    @search varchar(255)
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
    SELECT
        Result.id,
        Result.string
    FROM dbo.Example_GetBestTrigrams(@column_name, @search) AS GBT
    CROSS APPLY
    (
        -- Trigram search
        SELECT
            E.id,
            string =
                CASE @column_name
                    WHEN 'string1' THEN E.string1
                    WHEN 'string2' THEN E.string2
                    WHEN 'string3' THEN E.string3
                    ELSE NULL
                END
        FROM dbo.Example_GetTrigramMatchIDs
            (@column_name, GBT.trigram1, GBT.trigram2, GBT.trigram3) AS MID
        JOIN dbo.Example AS E
            ON E.id = MID.id
        WHERE
            -- Residual exact match check
            CASE @column_name
                WHEN 'string1' THEN E.string1
                WHEN 'string2' THEN E.string2
                WHEN 'string3' THEN E.string3
                ELSE NULL
            END LIKE @search
            -- At least one trigram found
            AND GBT.trigram1 IS NOT NULL

        UNION ALL

        -- Non-trigram search
        SELECT
            E.id,
            string =
                CASE @column_name
                    WHEN 'string1' THEN E.string1
                    WHEN 'string2' THEN E.string2
                    WHEN 'string3' THEN E.string3
                    ELSE NULL
                END
        FROM dbo.Example AS E
        WHERE
            CASE @column_name
                WHEN 'string1' THEN E.string1
                WHEN 'string2' THEN E.string2
                WHEN 'string3' THEN E.string3
                ELSE NULL
            END LIKE @search
            -- No trigram found
            AND GBT.trigram1 IS NULL
    ) AS Result;

That particular implementation choice allows trigram searches with the column name as a parameter:
SELECT
    ETS.id,
    ETS.string
FROM dbo.Example_TrigramSearch
(
    N'string1',
    '%12345%'
) AS ETS;

SELECT
    ETS.id,
    ETS.string
FROM dbo.Example_TrigramSearch
(
    N'string2',
    '%ABC12%'
) AS ETS;

SELECT
    ETS.id,
    ETS.string
FROM dbo.Example_TrigramSearch
(
    N'string3',
    '%ABC45%'
) AS ETS;


Multicolumn extended statistiscs, NLS_SORT and NLS_COMP, CHAR columns, and the bane of my existance This is as a result of this question.
So I'm working with a query that looks like this:

select count(*)
from table1
where col1 = 123
and col2 = '1';

The problem is this:  col2 is defined as a CHAR(1) and all sessions set NLS_SORT = 'BINARY_CI' and NLS_COMP = 'LINGUISTIC' .  (col1 is a NUMBER column.)

First off, without the NLS settings on for the sessions, defining a multicolumn extended stat over (col1,col2) works fine and produces appropriate cardinality.

But with NLS_SORT = 'BINARY_CI' and NLS_COMP = 'LINGUISTIC', it doesn't use the extended stats.  I believed its much like an index and the NLS settings - we had to define indexes with (NLSSORT("COL2",'nls_sort=''BINARY_CI''')) .

So, I implemented a multi column extended stats over ("COL1", (NLSSORT("COL2",'nls_sort=''BINARY_CI''')) .  But it still doesn't work (but an index defined the same will work).

In the predicate information, it shows that with the NLS settings on, col2 = '1' is transformed into:
NLSSORT(INTERNAL_FUNCTION(col2),'nls_sort=''BINARY_CI''')=HEXTORAW('3100') .  I believe that the INTERNAL_FUNCTION() is due to col2 being defined as CHAR(1).  I can't change that.

How can I create a multicolumn extended stat column group for these columns given my conditions?

I think your only option here is to 'roll your own' pseudo-multicolumn stats - as extended statistics cannot be created on Virtual Columns. For example:

testbed:

drop table table1;
create table table1(col1 integer, col2 char(1));
insert into table1(col1,col2) select mod(level,10), '0' from dual connect by level<=1000;
insert into table1(col1,col2) select mod(level,10)+100, '1' from dual connect by level<=1000;
commit;
select count(*) from table1 where col1=1 and col2='0';
/*
COUNT(*)
--------
     100
*/

first try with normal histograms (note 'rows' estimate is 'poor'):

exec dbms_stats.gather_table_stats(null,'TABLE1');
explain plan for select * from table1 where col1=1 and col2='0';
select * from table(dbms_xplan.display);
/*
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    50 |   250 |    59   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE1 |    50 |   250 |    59   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"=1 AND "COL2"='0')
*/

now create virtual concatenated column (note 'rows' estimate is 'good'):

alter table table1 add col21 generated always as (col2||col1);
exec dbms_stats.gather_table_stats(null,'TABLE1');
explain plan for select * from table1 where col21='01';
select * from table(dbms_xplan.display);
/*
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   100 |   900 |    59   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE1 |   100 |   900 |    59   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL21"='01')
*/

finally repeat with NLS parameters set:

alter session set nls_sort='BINARY_CI';
alter session set nls_comp='LINGUISTIC';
explain plan for select * from table1 where col21='01';
select * from table(dbms_xplan.display);
/*
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   100 |   900 |    59   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE1 |   100 |   900 |    59   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NLSSORT("COL21",'nls_sort=''BINARY_CI''')=HEXTORAW('303100
              ') )
*/


Best Practices for Testing Application Dashboard Data We have a dashboard that contains metrics based on a 6 month reporting period.  Users log in and see their metrics based on the current 6-month reporting period.

Every 6 months, we load new metrics in our DEV/QA environment and configure them for the upcoming reporting period so we can test. But, our application and ETL use GETDATE(), so for the ETL to calculate properly, we have been setting the server's DATE in the future so everything will calculate and display as if we were in the future.

  Our DBA group is now telling us that this should not be done and no
  one else in the industry does this.  Can someone give me some examples
  of what they have done for testing in this situation?  Should we be
  looking to re-design our ETL and application and what are some ideas
  for best practices?

We have unit testing for the application, so it will work.  We are more worried that the data from the ETL will not be correct on Day 1 of the new period.  It is very important that the metrics are displaying properly and the data is correct on Day 1.

I would not use GetDate() for this fixed-period scenario as the end date for queries, because the reports are only meningful for specific periods.

A better approach is to use a specific run-date for each reporting period. That run-date is not the current date.

For example, let's say one of your KPIs is the sales amount for the first 6 months of 2013. Your system should use end of June as the end date not the current run-date regardless of when the report is run. If you do this, you'd not have to touch the data.


SQL Server 2016 SP1 + strategy for long term version retention (snapshots, temporal tables, other?) We have a data mart fed from an upstream data warehouse that we generate reports out of for external parties. I have a business requirement to preserve the state of the database for each month end to ensure reproducibility of results in case we are asked to do more analysis on the same time period. Database is <100GB, about 40-50 tables, nothing too crazy.

I know there are some interesting new options in SQL 2016 SP1 to consider. My goals here are less about quick/easy access to prior months and more about integrity/immutability of the data, but any solution that covers the latter while coming out ahead in the former wins the round.

Which approach makes the most sense here?

Traditional backups: Archive to external storage, restore as needed. But this is clearly the most hassle from the DBA side, and the least self-service option. Note: Since I need to keep around all the copies, after while this will be a lot of storage space.
Database Snapshots: Create snapshots and keep indefinitely. But I'm not aware of any solution that lets me safeguard the snapshot itself, from what I've if there is any corruption on the server(s) then your snapshots are hosed and there is no way to reproduce them. But the self-service aspect looks hard to beat. Is there a 3rd party solution I'm missing?
Temporal Tables: The cool new feature of 2016, is anyone actually using this? Refactoring our scheme to support this looks like a heavy lift, so maybe this is off the table.
Based off of @Max Vernon's suggestion...Monthly Restores to Read-Only DBs, maybe using Stretch Database: This sounds pretty good as this will be a lot of cold data....buuut is anyone actually using Stretch Database? Is this something Microsoft will quietly deprecate in a couple of years?
Something else I'm totally missing here?

Update: As of mid-2019, Stretch Database has a pretty high cost to get off the ground, starting at about $1.8K/mo just for compute in Azure (storage extra) so I would say this puts it out of reach for all but the larger use cases.

Since you're dealing with a single database (or a known set of databases), simply create a SQL Server Agent Job that restores the month end backup to a "month-end" database.

Schedule the job to occur early on the first day of each month.  The job would simply restore "last nights" full backup.  You are taking nightly backups, right?

In order to ensure the immutability of the month-end data, simply mark the database as read-only after the restore finishes.  Something like this:

ALTER DATABASE [xyz] SET READ_ONLY WITH ROLLBACK IMMEDIATE;

Details for the above command are here.


Getting only the necessary row I am working on a query for a project and sometimes the results have multiple rows for the same entity. What I need to do is:

If the results look like this:

   Type        |   Entity
-----------------------------
   TypeA       |      B. Didly

   TypeA       |      J. Hendrix

   TypeC       |      G. VanFleet

   TypeA       |      M. Manson

   TypeB       |      M. Manson

   TypeC       |      M. Manson

Then I need to choose TypeB over TypeA or TypeC for M. Manson

My results need to look like this:

   Type        |   Entity
-----------------------------
   TypeA       |      B. Didly

   TypeA       |      J. Hendrix

   TypeC       |      G. VanFleet

   TypeB       |      M. Manson

I am struggling with using a cursor or finding another option.

Basically if an entity has all three types, I need to only get TypeB for that entity.

There are 10 possible "types" from what I have been told. And the client has given me their ranking of those types. I am thinking a case statement to set the rank.

Assuming you also only want B when an entity has two types, or if they have A and C you don't care which one you get:

;WITH x AS
(
  SELECT type, entity, rn = ROW_NUMBER() OVER
    (
     PARTITION BY entity
     ORDER BY CASE type WHEN 'B' THEN 1 ELSE 2 END
    )
  FROM dbo.tablename
)
SELECT type, entity
  FROM x
  WHERE rn = 1;

If you care about the order of A and C in the case where those are the only two (in this example A will be returned):

     ORDER BY CASE type WHEN 'B' THEN 1 WHEN 'A' THEN 2 ELSE 3 END


RDS crosstab: ERROR: permission denied for language c We're currently using PostgreSQL 9.2 in our production database, we want to migrate to a new RDS instance, using PostgreSQL 9.4. However, we need to have permission to create a function, but only superuser can create functions on RDS and my user can't be granted as superuser.

CREATE FUNCTION crosstab50(text, OUT rownumber integer, OUT c1 text,...)
    RETURNS SETOF record
    LANGUAGE c
    STABLE STRICT
    AS '$libdir/tablefunc', 'crosstab';

But we're getting this error during our restore operation:

ERROR:  permission denied for language c

RDS doesn't let you install arbitrary C functions. It's a restricted environment.

crosstab is part of the tablefunc extension. You may be able to CREATE EXTENSION tablefunc if it's on the whitelist but you won't be able to add new variants, even if the underlying C function is already loaded and approved.


What PUBLIC access means on tables in Oracle? I recieved a communication from my DBA that they want to revoke PUBLIC access and they shared attached screenshot. What does this mean by PUBLIC access on these tables? And what happen if they "Revoke" the access.

I don't know the query which result is this. Can anyone help to understand this resultset. I want to understand the impact on my application if they revoke this access.

Grants and Revokes of Privileges to and from the PUBLIC Role

  You can grant and revoke privileges and roles from the role PUBLIC.

  Because PUBLIC is accessible to every database user, all privileges
  and roles granted to PUBLIC are accessible to every database user. By
  default, PUBLIC does not have privileges granted to it.

  Security administrators and database users should grant a privilege or
  role to PUBLIC only if every database user requires the privilege or
  role. This recommendation reinforces the general rule that, at any
  given time, each database user should have only the privileges
  required to accomplish the current group tasks successfully.

  Revoking a privilege from the PUBLIC role can cause significant
  cascading effects. If any privilege related to a DML operation is
  revoked from PUBLIC (for example, SELECT ANY TABLE or UPDATE ON emp),
  then all procedures in the database, including functions and packages,
  must be reauthorized before they can be used again. Therefore, be
  careful when you grant and revoke DML-related privileges to or from
  PUBLIC.

If you do not use the above packages, it will not have any impact on your application.


Does the Inserted table contain Identity values? I have an issue with this AFTER INSERT trigger that I'm using for auditing:

  Cannot insert the value NULL into column 'TermID', table
  'AuditTerms'; column does not allow nulls. INSERT fails.

TermKeys table definition:

TermID int Identity PK
Key    int
Action int

AuditTerms table definition:

TermID     int PK
UpdatedAt  datetime

Trigger definition:

ALTER TRIGGER [dbo].[TRG_TermKeys_AuditTerms_IUD] ON [dbo].[TermKeys]
AFTER INSERT, UPDATE, DELETE NOT FOR REPLICATION AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @termid int;
  IF EXISTS(SELECT * FROM Deleted) SELECT @termid = TermID FROM Deleted;
  IF EXISTS(SELECT * FROM Inserted) SELECT @termid = TermID FROM Inserted;
  IF EXISTS(SELECT TermID FROM AuditTerms WHERE (TermID = @termid))
  BEGIN
    UPDATE AuditTerms SET UpdatedAt = getdate() WHERE (TermID = @termid);
  END
  ELSE
  BEGIN
    INSERT INTO AuditTerms (TermID, UpdatedAt) VALUES (@termid, getdate());
  END;
END;

TermID is an Identity field and the PK, does the Inserted table contain the Identity value at this point?
Should I be using @@Identity or some other way?

The error is caused by attempting to insert AuditTerms when there is no corresponding primary key in TermKeys. After creating the table schema and trigger in tempdb and inserting a few records in TermKeys, I was able to reproduce it by issuing a DELETE twice on the same TermID:

DELETE FROM TermKeys
WHERE TermID = 1

DELETE FROM TermKeys
WHERE TermID = 1

gives me:

  Msg 515, Level 16, State 2, Procedure TRG_TermKeys_AuditTerms_IUD,
  Line 15 Cannot insert the value NULL into column 'TermID', table
  'tempdb.dbo.AuditTerms'; column does not allow nulls. INSERT fails.
  The statement has been terminated.

Something in the application code may be running the query twice; I can't see any other way to get a null value. The best way to avoid the error is to add a precautionary check of @@ROWCOUNT at the beginning of the trigger, since it will return the total number of rows affected by the DML statement that fired it.

ALTER TRIGGER [dbo].[TRG_TermKeys_AuditTerms_IUD] ON [dbo].[TermKeys]
AFTER INSERT, UPDATE, DELETE NOT FOR REPLICATION AS
BEGIN
  -- If the trigger was fired but no rows were affected, do nothing
  IF @@ROWCOUNT = 0
    RETURN;
  SET NOCOUNT ON;
  DECLARE @termid int;
  IF EXISTS(SELECT * FROM Deleted) SELECT @termid = TermID FROM Deleted;
  IF EXISTS(SELECT * FROM Inserted) SELECT @termid = TermID FROM Inserted;
  IF EXISTS(SELECT TermID FROM AuditTerms WHERE (TermID = @termid))
  BEGIN
    UPDATE AuditTerms SET UpdatedAt = getdate() WHERE (TermID = @termid);
  END
  ELSE
  BEGIN
    INSERT INTO AuditTerms (TermID, UpdatedAt) VALUES (@termid, getdate());
  END;
END;


How to combine date and time to datetime in SQL Server? I just found the following code:

CREATE FUNCTION dbo.JoinDateAndTime
(
    @d datetime,
    @t datetime
)
RETURNS Datetime
as
    BEGIN
        declare @res datetime
        set @res = convert (datetime, Convert(varchar(10), @d, 104) + ' ' + convert(varchar(8), @t, 108), 104)
        return @res
    END
go

After seeing the answers to How to the get current date without the time part I'm sure that involving conversions to varchar can't be the best solution.

BTW I searched SO for this question, but only found a solution for mySql.

The time part is the fractional part and the date part the integer part, so if you date is genuinely just a date (no time part at all) and your time is genuinely just a time (no date part at all) you can simply combine them by adding them together.

For example:

SELECT CAST(40597 AS DATETIME), CAST(0.5 AS DATETIME)
SELECT CAST(40597 AS DATETIME) + CAST(0.5 AS DATETIME)

The top SELECT shows what values represent today and 12 noon, and the second line adds them to make a single value representing today at 12 noon.


Estimated stored procedures time I have executed a stored procedure in SQL Server but it took a very long time to complete.

Any SQL queries I can run to get estimated completion time?

You can get the average run time since last restart, last run time, shortest run time  and longest run time among all run. One catch here is the plan still have to reside in your plan cache.

  total_elapsed_time

  Total elapsed time, in microseconds, for
  completed executions of this stored procedure.

  last_elapsed_time

  Elapsed time, in microseconds, for the most
  recently completed execution of this stored procedure.

  min_elapsed_time

  Minimum elapsed time, in microseconds, for any
  completed execution of this stored procedure.

  max_elapsed_time

  Maximum elapsed time, in microseconds, for any
  completed execution of this stored procedure.

You can also change ORDER BY clause to order it by read, write etc.

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-procedure-stats-transact-sql

SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
    d.cached_time, d.last_execution_time, d.total_elapsed_time,
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
    d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;


MySQL incremental backups per database (not global) While searching for free/open-source MySQL incremental backups solutions I have found a number of ways to do incremental backups and all of them seem to either rely on "logical" way of backups/restore like mysqldump and binlog (or LSN) or "physical" way (like Percona's xtrabackup). However, in both cases the only scenario implied is backing up the database globally (all databases).

I am looking for the way to provide incremental backups per database: there are two db servers (master and slave) and each handles lots of databases. Each of these databases belong, let's say, to different customers and some of them tend to grow really big. While software upgrades database schema sometimes changes and before the upgrade we must make sure we are on the safe side so backup is a must but full backup is definitely not a way to go due to database sizes.

Please advise how this can be solved or at least suggest some ways to look for solution.

You can read binlogs with the mysqlbinlog tool, and produce a stream of logical changes, like a mysqldump backup. The mysqlbinlog has an option --database=dbname so you can extract only changes pertaining to the named database.
Read more about using this option here: http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_database
Two caveats:

This assumes that you have a continuous set of binary logs back either to the last full backup or to the last incremental dump.

It's not really a filter for changes to dbname. It's a filter for all binlog events applied to any database while dbname was selected as the default database. So if you have an event where someone updated dbname2.tablename while dbname was the default, then the dump will include that change to dbname2.
This makes this method unsafe to use this as a per-database incremental backup method, unless you have 100% certainty that no such transactions occurred.

P.S.: Your comments about LSN are a bit incorrect. Mysqldump knows nothing about the LSN, which is a feature of the InnoDB redo log. Mysqldump does examine the binary log position, and can output that position if you use the option --master-data.


Does implicit casting negatively affect index usage? I have a query similar to the following:

SELECT id FROM MyTable
WHERE eventCount > eventCutoff * eventFactor

eventCount and eventCutoff are defined as a smallint, and eventFactor as a float.

I have two questions: is there an implicit cast of eventCount to a float for the comparison? And if so, will this prevent me from effectively leveraging an index on eventCount?

Such conversion often will occur, and they can often impact cardinality estimation (and hence can affect plan quality). It will depend though on data types, existing indexes, cardinality etc. For example this query might not be able to seek in any case, as SQL Server might be using a scan regardless.

You should be able to determine if this is an issue in your case by inspecting the execution plan - do you get a scan on eventCount if you first cast eventFactor to a smallint? What if you use a local smallint variable that would result in the same number of rows that would match the filter? We can't tell from here...


SQL Server on azure virtual machines default instance We have two ways of provisioning SQL Server on an Azure virtual machine, one is creating the VM and then upload the binaries of SQL server and do the installation manually, the second one is using SQL Server on Azure virtual machines which provides images of the OS and the desired version on SQL Server.
The question applies to the second option. Is there a way to provision a SQL Server virtual machine on azure with a named instance or does it always install the default?

Is there a way to provision a SQL Server virtual machine on azure with a named instance or does it always install the default?
Not with the pre-built images.
If you provision your own VM and use IaaS agent extension you can use named instance.
Can I install a second instance of SQL Server on the same VM? Can I change installed features of the default instance?

Yes. The SQL Server installation media is located in a folder on the C
drive. Run Setup.exe from that location to add new SQL Server
instances or to change other installed features of SQL Server on the
machine. Note that some features, such as Automated Backup, Automated
Patching, and Azure Key Vault Integration, only operate against the
default instance, or a named instance that was configured properly.

Can I use a named instance of SQL Server with the IaaS extension?

Yes, if the named instance is the only instance on the SQL Server, and
if the original default instance was uninstalled properly. If there is
no default instance and there are multiple named instances on a single
SQL Server VM, the SQL Server IaaS agent extension will fail to
install.

Ref:

Frequently asked questions for SQL Server on Azure VMs
Automate management tasks on Azure virtual machines by using the SQL Server IaaS Agent Extension


Can a slave with a dynamic ip update the master How can I setup mySQL replication in such a way that a slave on localhost with dynamic IP can update the master as well as get updates from the master which has a static IP.

The slave will not be always online.

Only the master would need a fixed IP address, since its the slaves that pulls data. You say UPDATE the master in your question, but it is not possible for a slave to update a master, unless you have a MASTER to MASTER replication setup. As you are using the word slave, then that assumes that the Slave server only pulls data but doesn't update the Master.

As for security, when it comes to grants, you would need to give your replication user access to the Master using a wild card.

On the Master MySQL server

GRANT REPLICATION SLAVE ON *.*
TO 'replication'@'192.168.1.%'
IDENTIFIED BY 'replicationuserpassword';

Where percent represents 0 to 254. You could also just put % like this

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'replicationuserpassword';

So that the replication user can access the master server from anywhere

Here is a post I answered on setting up replication.

If the slave will not always be online, there is a threshold of transactions that will be available. If you wait past that threshold, it will no longer be possible to synchronize.

On the master MySQL server, in the my.cnf file, you might find an entry like this:

expire_logs_days    = 1

Which means that only 24 hours of binary logs will be conserved on the master server.

By default, MySQL does not automatically remove binary log files. Considering the amount of space they could take up, it is unlikely that there is no automatic removal.


PAGELATCH_EX occures in update query with UPDLOCK, READPAST We have a queue organized with a table on MS SQL Server 2014:

CREATE TABLE [queue].[Messages](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Status] [tinyint] NOT NULL,
    [Data] [nvarchar](max) NOT NULL,
    [CreationDateUtc] [datetime] NOT NULL
)

CREATE NONCLUSTERED INDEX [IX_Messages_Status] ON [queue].[Messages]
([Status] ASC)

Stored procedure to send message:

ALTER PROCEDURE [queue].[SendMessage]
    @MessageBody NVARCHAR(MAX),
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [queue].[Messages]
    (
        Data,
    )
    VALUES
    (
       @MessageBody,
    )
END

And a stored procedure to receive messages:

ALTER PROCEDURE [queue].[ReceiveMessage]
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE TOP (1) m
    SET
       m.Status = 2,
       m.StartDateUtc = GETUTCDATE()
    OUTPUT
       INSERTED.*
    FROM [queue].[Messages] m WITH (UPDLOCK, READPAST)
    WHERE m.Status = 1
END

This stored procedure executes every second to check new messages in queue.
Very often we get blocks (PAGELATCH_EX) on this query even if the queue is empty:

First idea was - diff-backups, but they are scheduled at different times:

Other queries don't make blocks so often.

Am I right that this is due to UPDLOCK and READPAST hints?
Are there other reasons?

This is because you use a Status field for messages and leave unreceiveable messages in the queue. Retaining messages (keeping them after process, with a different status) leads to queue bloat, contention, poor plan choices and general slow processing.

Follow Using tables as Queues and do destructive message processing:

DELETE TOP (1)
FROM [queue].[Messages] m WITH (ROWLOCK, READPAST)
OUTPUT deleted.*;

Also drop all indexes on the queue. The linked article has details how to achieve fancier behavior like FIFO or Pending.

Use queues only for events, not for state.


how did you become a DBA? maybe this is more of a wiki type question, but I'd like to learn more about the (no doubt many) paths taken to become a DBA.
What path lead you to become a DBA?

The thing with being a "serious" DBA (and by that I mean, you are a person responsible for meeting a defined SLA on a system handling physical goods or other people's money) is that it is very difficult to get the job without already having had the job for 5 years - experience is everything in this game, a reputation as a safe pair of hands who isn't going to panic and make a bad situation worse, and has the maturity to weigh up commercial and technical considerations. That is what makes DBAs different from developers - that we need to make decisions in real time - and from sysadmins - that we are only one step removed from the cash that flows through the business, personal details of customers, stock levels in the warehouse, etc, so we are much more business-focussed.

The usual way in is that you are either a developer or a sysadmin with an interest in databases who gets involved with DBA work, fills in when other DBAs are unavailable, and gradually work your way sideways into the job. That is the best route, it is almost like an apprenticeship. I dare say it is the same if you want to be a "web" DBA it is similar but I have no personal experience of that.


In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index Let's assume that I have table data:

CREATE  TABLE `test`.`data` (
`row_id` INT NOT NULL ,
`data_id` INT NOT NULL ,
PRIMARY KEY (`row_id`) );

Now, let's assume that data_id has unique values. Is there any difference(space, performance, memory) between having B-Tree index on data_id and UNIQUE index on it? (besides the case when InnoDB is used as storage engine and UNIQUE index can be used as clustering key so row_id is uneccesary)

According to the Book

Page 198 says the following:

Paragraph 6 : A MyISAM B-tree consists of leaf and nonleaf nodes, or pages.
Paragraph 7 : Both leaf and nonleaf nodes key values and pointers to the record positions in the datafile. Nonleaf nodes additionally contains pointers to child nodes.

Given this description

a unique index would have more nonleaf nodes. This would lend itself mode towards ordering and searching.
nonunique index would requires less nonleaf nodes. This would lend itself mode towards doing range scans (tables and index)
A covering index (which would contain all needed columns for specific SELECT queries) would combine the best and worst of both. This would allow for a range scans that would need more nonleaf nodes  and provide for ordering/searching. The additional benefit would be bypassing the need for reading table data if all needed columns are present and accounted for in the index.
I mentioned other aspects in my past post Benefits of BTREE in MySQL

SPACE

Unique Indexes would take up more space for nonleaf nodes.
Covering indexes would need more space than a nonunqiue index but would have a higher multi-columns index cardinality that can approach the actual row count of the table.

PERFORMANCE (Heavy-Write Environment)

SELECTs would be very fast for exact keys
SELECTs that only touch a Covering Index would be extremely fast
Random INSERTs can be a nightmare due to rotations/rebalancing of nonleaf nodes
UPDATEs that change key values can be a nightmare due to rotations/rebalancing of nonleaf nodes
The higher the cardinality, the more nonleaf nodes, the better the performance
The lower the cardinality, the fewer the nonleaf nodes, the worse the performance
The more columns in the index increases the effects of the cardinality

MEMORY
Depends on Storage Engine

MyISAM : The key buffer only caches index pages. Covering indexs are your best friend here
InnoDB : Buffer Pool Needs to be Big Enough to Hold Data Pages, Index Pages, and Index Page Changes (See InnoDB Architecture, What are the main differences between InnoDB and MyISAM?)


Will altering an nvarchar(max) column to nvarchar(50) lock the table? I have a table in SQL Server (Azure) with 1.4 million rows.

I'd like to index one of the columns; however, its datatype is nvarchar(max) (defaulted to such by using EF I imagine). I'm using Entity Framework and can make a migration that will alter this datatype to nvarchar(50).

My concern is how long this will take, and whether it will cause issues with locking.  I've only ever expanded columns, so I'm not sure what to expect.  I do know the data in this column will fit fine in the new size.

I was planning to create a copy of the database and try it out. I have also considered migrating to a new column, but would much prefer to update the current column if it's not overly painful.

There's no way for us to fully answer your question. Depends on way too many factors, including the table definition, other columns in the table, if the data is off page, the T-SQL generated by your ORM, and so on. The documentation is pretty good though:

  WITH ( ONLINE = ON | OFF)  Applies
  to: SQL Server 2016 through SQL Server 2017 and Azure SQL Database.

  Allows many alter column actions to be performed while the table
  remains available. Default is OFF. Alter column can be performed on
  line for column changes related to data type, column length or
  precision, nullability, sparseness, and collation.

  Online alter column allows user created and auto statistics to
  reference the altered column for the duration of the ALTER COLUMN
  operation. This allows queries to perform as usual. At the end of the
  operation, auto-stats that reference the column are dropped and
  user-created stats are invalidated. The user must manually update
  user-generated statistics after the operation is completed. If the
  column is part of a filter expression for any statistics or indexes
  then you cannot perform an alter column operation.

  While the online alter column operation is running, all operations
  that could take a dependency on the column (index, views, etc.) will
  block or fail with an appropriate error. This guarantees that online
  alter column will not fail because of dependencies introduced while
  the operation was running.

  Online alter column has similar requirements, restrictions, and
  functionality as online index rebuild. This includes:

  Online index rebuild is not supported when the table contains legacy
  LOB or filestream columns or when the table has a columnstore index.
  The same limitations apply for online alter column.

  An existing column being altered requires twice the space allocation;
  for the original column and for the newly created hidden column.

  The locking strategy during an alter column online operation follows
  the same locking pattern used for online index build.

In addition it's also pretty straightforward to test. I'm on SQL Server 2017 testing with the default read committed isolation level. First I'll create the table:

DROP TABLE IF EXISTS dbo.CONVERT_ME;

CREATE TABLE dbo.CONVERT_ME (
    ID BIGINT NOT NULL,
    OVERWEIGHT_COLUMN VARCHAR(MAX) NOT NULL,
    PRIMARY KEY (ID)
);

INSERT INTO dbo.CONVERT_ME WITH (TABLOCK)
SELECT TOP (1500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), REPLICATE('Z', 50)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

Then I'll alter the column OFFLINE, which is the default:

SET STATISTICS IO, TIME ON;

BEGIN TRANSACTION;

ALTER TABLE dbo.CONVERT_ME
ALTER COLUMN OVERWEIGHT_COLUMN VARCHAR(50) NOT NULL WITH (ONLINE = OFF);

My machine this takes about three seconds. The operation takes a schema modification lock during the entire transaction which prevents any other lock from being taken on it, including querying it with NOLOCK.

Now I'll try it with WITH (ONLINE = ON). The operation is now eligible for parallelism, so it may run faster on your server. A schema modification lock is still taken on the object, but it occurs near the end instead of throughout the whole transaction. Here's an example snapshot of the lock table to show the schema modification locks held during most of the transaction:

Note that an object level schema modification lock has not yet been taken. Here is what it looks like after the operation has finished but before the transaction has committed:

A schema modification lock on the object, along with a few others, are taken briefly near the end.

So great, there should be no problem if you use WITH (ONLINE = ON), right? As I said before the time the operation takes is going to depend on a lot of factors. Consider a different table which has many more data pages:

DROP TABLE IF EXISTS dbo.CONVERT_ME;

CREATE TABLE dbo.CONVERT_ME (
    ID BIGINT NOT NULL,
    OTHER_COLUMN VARCHAR(8000) NOT NULL,
    OVERWEIGHT_COLUMN VARCHAR(MAX) NOT NULL,
    PRIMARY KEY (ID)
);

INSERT INTO dbo.CONVERT_ME WITH (TABLOCK)
SELECT TOP (1500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), REPLICATE('Z', 4200), REPLICATE('Z', 50)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

On my machine the same ALTER TABLE now takes 39635 ms when run OFFLINE and 117877 ms when run ONLINE.

My advice to you is to read the documentation until you feel confident that you understand the locking considerations and to test with your real table's schema populated with real data before doing the switch in production.

PLEASE NOTE:

Not sure about Azure SQL Database, but in regular SQL Server the ONLINE option is only for Enterprise Edition. Attempting to do this in another edition will result in the following error:

  Msg 1712, Level 16, State 1, Line XXXXX
  Online index operations can only be performed in Enterprise edition of SQL Server.


Why does ANSI SQL define SUM(no rows) as NULL? The ANSI SQL standard defines (chapter 6.5, set function specification) the following behaviour for aggregate functions on empty result sets:

COUNT(...) = 0
AVG(...) = NULL
MIN(...) = NULL
MAX(...) = NULL
SUM(...) = NULL

Returning NULL for AVG, MIN and MAX makes perfect sense, since the average, minimum and maximum of an empty set is undefined.

The last one, however, bothers me: Mathematically, the SUM of an empty set is well-defined: 0. Using 0, the neutral element of addition, as the base case makes everything consistent:

SUM({})        = 0    = 0
SUM({5})       = 5    = 0 + 5
SUM({5, 3})    = 8    = 0 + 5 + 3
SUM({5, NULL}) = NULL = 0 + 5 + NULL

Defining SUM({}) as null basically makes "no rows" a special case that does not fit in with the others:

SUM({})     = NULL  = NULL
SUM({5})    = 5    != NULL + 5 (= NULL)
SUM({5, 3}) = 8    != NULL + 5 + 3 (= NULL)

Is there some obvious advantage of the choice that was made (SUM being NULL) that I have missed?

I'm afraid that the reason is simply that the rules were set in an adhoc fashion (like quite many other "features" of the ISO SQL standard) at a time when SQL aggregations and their connection with mathematics were less understood than they are now (*).

It's just one of the extremely many inconsistencies in the SQL language.  They make the language harder to teach, harder to learn, harder to understand, harder to use, harder to whatever you want, but that's just the way things are.  The rules cannot be changed "cold" and "just like that", for obvious reasons of backward compatibility (If the ISO committee publishes a final version of the standard, and vendors then set out to implement that standard, then those vendors will not appreciate it very much if in a subsequent version, the rules are changed such that existing (compliant) implementations of the former version of the standard "automatically fail to comply" the new version ...)

(*) It is now better understood that aggregations over an empty set behave more consistently if they systematically return the identity value (= what you call the 'neutral element') of the underlying binary operator at hand.  That underlying binary operator for COUNT and SUM is addition, and its identity value is zero.  For MIN and MAX, that identity value is the highest and lowest value of the type at hand, respectively, if the concerned types are finite.  Cases like averaging, harmonic means, medians, etc. are extremely intricate and exotic in this respect, though.


Machine account causing a Token-based server access validation failed with an infrastructure error As the error suggests the SQL log is filling with the error:

Login failed for user 'xxx\xxx$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]

This is a SQL cluster and the account trying to login is the passive node account.  The account doesn't exist in SQL itself so I don't think this is a SID mismatch.

I have checked the ring buffer DMVs which I have placed below, however I don't think this is giving me much.

runtime Notification_Time   ErrorCode   CallingAPIName  APIName SPID    Record Id   Type    Record Time Current Time
2014-10-24 10:22:50.953 2014-10-23 15:38:24.547 0x139F  NLShimImpersonate   ImpersonateSecurityContext  62  18610   RING_BUFFER_SECURITY_ERROR  10966336138 11033802544

I have checked and IIS is not on the passive node so there is no double hop coming from there, and in the services I can see nothing obvious as to what could be causing it.  SQL Browser is set to local service, could that be trying to log in?

It seems I have found the issue.  It was System Center Operations Monitor trying to log into SQL via the cluster name however it was set to use Local System and didn't have access.  I changed this to a specific monitoring account and its working now.


Create SQL login to multiple instances at once I have a AD group that needs to be added to multiple SQL instances, I am wondering is there a quicker way of adding this group at once rather than having to login to each server and creating a login in SQL?

Thanks!

Download the awesome Powershell scripts from dbatools and use New-DbaLogin to add the new login.

Example:

New-DbaLogin -SqlInstance sql1 -Login domain\user


Postgres Column Level Security I have a schema snow within a database testdb. I'm trying to achieve column level security for my users. My users are boss, manager and intern. I set all of this table up as the superuser me. They are all in the group company.

The table I have is this: snow.company_table

 property_id |     property_address     |     city     | zip_code | state
-------------+--------------------------+--------------+----------+-------
           1 | 2564 Wescam Court        | Reno         |    89511 | NV
           2 | 1732 Rubaiyat Road       | Grand Rapids |    49603 | MI
           3 | 4094 Francis Mine        | Standish     |    96128 | CA
           4 | 3193 Ashton Lane         | Briggs       |    78608 | TX
           5 | 1519 North Street        | Alamosa      |    81102 | CO

I created the roles like this where boss, manager and intern are all within company.

CREATE ROLE company WITH LOGIN CONNECTION LIMIT 0;
CREATE ROLE boss WITH LOGIN CONNECTION LIMIT 1 PASSWORD '1234' IN ROLE company;
CREATE ROLE manager WITH LOGIN CONNECTION LIMIT 1 PASSWORD '1234' IN ROLE company;
CREATE ROLE intern WITH LOGIN CONNECTION LIMIT 1 PASSWORD '1234 IN ROLE company;

I would like for boss to have access to be able to select all columns, manager to only select property_id, zipcode and state, and the intern to only select property_id and state. I used these commands to create the roles and their grants: To grant the column privileges, I revoked all first, then granted them back.

REVOKE ALL ON snow.property_table FROM snow; --remove all privileges automatically assigned to everybody
GRANT ALL ON snow.company_table TO boss; --boss has the ability to do anything to this table
GRANT SELECT( property_id, zip_code, state) ON snow.company_table TO manager;
GRANT SELECT( property_id, state) ON snow.company_table TO intern;

for more information, running \dn+ gives the results:

  Name  |    Owner     |      Access privileges       |
--------+--------------+------------------------------
 snow   |     me       | me=UC/me                    +|
        |              | boss=U/me                   +|
        |              | company=U/me                +|
        |              | manager=U/me                +|
        |              | intern=U/me                  |

My problem is: when I connect to a user (ex. intern), I don't have permission to access the table shown above. I still want to keep the column permission, but I only want my users to see the columns I have specified without giving them full permissions on the whole company_table.

Here is the error:

STATEMENT:  select * from snow.company_table;
ERROR:  permission denied for table company_table

As the administrator or boss, you can create user-specific views which include only the columns you want that user to be able to view. Then grant the user access to their specific view, and not to the underlying table. If you do this, then you don't need to use column permissions in the first place, as the column access is already restricted via the views. If you accidentally include a column into the view that you didn't want to, column permissions won't save you as the view is executed as the view owner, not the view invoker, and so will offer up the columns anyway.

Or each user can create his own view for his own convenience, in which case the column privileges in the underlying table will still be enforced.

Note that many people think that 'select *' is just a crutch for development and testing, and shouldn't be used in production code anyway.


Are Snapshot files created during MS SQL replication one time use only? I added a new article in an existing publication.

I saw that it generated a new snapshot folder (E:\Database\Snapshots\unc\S749TCAPDB01_NFS_XXXX_XXXX_PUBLICATION\20190417114556).

But this folder only contains a single article definition which I added previously.

It makes me wonder if this snapshots folder are only one time use and can be deleted afterwards

The lifecycle of snapshot files is dependent on a number of factors. Firstly, if your publications have immediate_sync enabled it means that new subscribers will use the existing snapshot to initialise the new subscription (provided the snapshot is within the retention period).

This has additional impacts as commands in the distribution DB are also retained for the retention period which can cause excess growth of that database. The snapshot remains valid until the retention period expires at which point it is removed by the distribution cleanup job.

Secondly, if you have immediate_sync disabled and you reinitialise a subscription, a new snapshot must be generated for the reinitialisation. You can reinitialise multiple subscriptions at a time, but only if they're all marked for reinitialisation prior to running the snapshot agent. Once those subscriptions marked for reinitialisation have been reinitialised, the snapshot is invalid for further use. At this point, it should be cleaned up by the distribution cleanup job.

Thirdly, if you perform a mini-snapshot due to something like adding a single article to an existing publication (as per your described scenario), that mini-snapshot is only valid for the subscribers that existed and were synchronising/synchronised at the time the snapshot was generated. Any new or expired subscriptions would require a full snapshot to reinitialise.

At all stages, the distribution cleanup job should be cleaning up expired or invalid snapshots, however, you can remove them manually if desired.


DELETE from Linked Server table using OPENQUERY and dynamic criteria I'm attempting to run a DELETE statement from my local SQL Server to delete appropriate records from a table in my remote linked server using OPENQUERY(). I'm using the following code, but it seems slow and I don't want to be pounding my server with bad SQL. Should I be using OPENQUERY() for this? Should I be deleting from it a different way that's more direct?

Here's my code:

DELETE RemoteTable
FROM OPENQUERY(MyLinkedServer,'SELECT * from RemoteTable_T1') AS RemoteTable
INNER JOIN MyDB.dbo.LocalTable_T1 AS LocalTable
ON LocalTable.LocalTable_T1_EntryID = RemoteTable.RemoteTable_T1_EntryID;

Any improvements I can make on this code since it is going to be set up as a recurring task on my server? I have about 5 different variations of this in order to delete from 5 different tables on my linked server.

In my experience, DML statements over Linked Servers is sllooowww (I believe that is the proper, technical spelling of this particular type of slowness ;-).

I found that the following setup was mucho faster:

Create a stored procedure on the remote server:

Call it something like RemoteTable_DeleteByEntryIDList
It should accept an NVARCHAR(MAX) parameter called @EntryIDs
The format of the data for that parameter will be a list of EntryIDs in XML
The parameter cannot be of type XML as that is not valid for passing over a Linked Server
The proc will do something along the lines of:

CREATE TABLE #TempIDs ([EntryID] INT NOT NULL);

;WITH cte AS
(
    SELECT CONVERT(XML, @EntryIDs) AS [Data]
)
INSERT INTO #TempIDs ([EntryID])
  SELECT tmp.[EntryID]
  FROM   cte
  CROSS APPLY (SELECT tab.col.value('./EntryID[1]', 'INT') AS [EntryID]
               FROM   cte.[Data].nodes('/row') tab(col)
              ) tmp;

-- optional: test to see if it helps or hurts
-- ALTER TABLE #TempIDs
--   ADD CONSTRAINT [PK_#TempIDs]
--   PRIMARY KEY CLUSTERED (EntryID ASC)
--   WITH FILLFACTOR = 100;

DELETE rt
FROM   RemoteTable rt
INNER JOIN #TempIDs tmp
        ON tmp.[EntryID] = rt.[EntryID];

Update your local stored proc to do something like:

DECLARE @IDsToDelete NVARCHAR(MAX);

SET @IDsToDelete = (
  SELECT EntryID
  FROM   dbo.LocalTable
  FOR XML RAW
);

EXEC [MyLinkedServer].[DatbaseName].[SchemaName].[RemoteTable_DeleteByEntryIDList]
     @EntryIDs = @IDsToDelete;


SQL Server on VMware why 3 vnics We have VMWare ESXi hosts administered in VMware vSphere.  I've sent my network admins this artice, http://www.sqlservercentral.com/articles/Virtualization/125226/
and asked what they think.  Currently our SQL Server VMs have one vmnic.  They wonder why the article is suggesting 3 when according to them that's redundant.  See their email that I've anonomized and shortened below.

"The author isolated SQL frontend traffic, availability traffic, and backup traffic. Since the vmnic does not have a hardware speed limit, multiple NICs will not increase, according to our VMWare expert, performance. What other reason would there be to separate out these VLANs? We currently have a flat network within the datacenter, so unicast traffic between these three VLANs would not be limited by default. Broadcast traffic would be confined to the VLAN."

I'm wondering if this is necessary and/or recommended, and if so why?

Thanks in advance,

The three NICs won't help with redundancy, because the VM layer should have already handled this configuration under the hood in the virtualization layer itself.

However, three NICs would allow for segregation of particular types of server traffic (backups, AG replication, etc.) away from the primary NIC, which allows you to monitor and profile the usage patterns of these NICs better.


Understanding below execution plan I have two tables tb1 and tb2, tb1 doesn't have any indexes on it. I populated tb1 with 1000000 rows and tb2 has 500 rows in it and with a clustered index on ID column.

To understand nested loop join, I have used below query:

SELECT *
FROM tb1
INNER JOIN tb2 ON tb1.id=tb2.id
OPTION(loop join);

I got below execution plan for this:

Tb1 which doesn't have index is scanned and cost is 2 %, whereas index is being used on tb2 and cost is 98%.

My question is:

How to understand estimated operator cost shown for above snip (138.236)

From the above snip of table scan (whose cost is 2%), number of executions are 24, will that mean sql reads rows in batches stored in memory, and for each row it did a seek operation from tbl2?

Can someone please explain to me the execution plan and also any pointers to know more about force scan, force index when I pressed F4 after clicking an operator.

1. How to understand estimated operator cost? Tb1 which don't have index is scanned and cost is 2 %, whereas index is being used on tb2 and cost is 98%.

The heap table is only fully scanned once, but the index seek is executed 1,000,000 times. The optimizer estimates that a million seeks in this case will represent 98.4% of the total cost of executing the query, whereas a single parallel scan of the heap table will represent 0.9% of the cost.

These are just estimates used for internal plan choice reasons; they do not generally reflect real-world performance on modern hardware, and are never anything more than an estimate - even in a post-execution ("actual") execution plan.

In Management Studio:

In SQL Sentry Plan Explorer:

  2. From the above snip of table scan (whose cost is 2%),number of executions are 24,will that means sql read rows in batches and stored in memory and for each row it did a seek operation from tbl2.

No, it means 24 parallel threads co-operated to perform a single scan of the heap table. Each thread still reads a row at a time from the scan, performs a seek into the indexed table, then gets the next row from the scan, and so on until the task is complete.

Rows are not read in batches and stored in memory in this plan. SQL Server reports 24 scans because 24 threads each performed a partial scan of the table, resulting in one full scan overall.

  3. Also any pointers to know more about force scan,force index when I pressed F4 after clicking an operator

The ForceScan, ForceSeek, and ForcedIndex properties are set to true if the query specifies a FORCESCAN, FORCESEEK, or INDEX hint - or if the query optimizer decides that a particular access strategy is required for correctness (for example, when checking foreign key constraints).


Mongodb Not Restarting with Collections, WiredTiger.wt may be corrupt I believe my MongoDB did not have a clean shutdown. I am able to restart it in a new location which doesn't have all of my collections. If I try to repair or start it in the old location, it gives the following error:

[ec2-user@ip-172-31-30-192 tmp]$ mongod --repair --dbpath /data

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] MongoDB starting : pid=31865 port=27017 dbpath=/data 64-bit host=ip-172-31-30-192

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] db version v3.2.16

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] git version: 056bf45128114e44c5358c7a8776fb582363e094

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] OpenSSL version: OpenSSL 1.0.0-fips 29 Mar 2010

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] allocator: tcmalloc

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] modules: none

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] build environment:

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] distmod: amazon

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] distarch: x86_64

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] target_arch: x86_64

2017-08-20T16:20:30.951+0000 I CONTROL [initandlisten] options: { repair: true, storage: { dbPath: "/data" }

} 2017-08-20T16:20:30.972+0000 I - [initandlisten] Detected data files in /data created by the 'wiredTiger' storage engine, so setting the active storage engine to 'wiredTiger'.

2017-08-20T16:20:30.972+0000 I STORAGE [initandlisten] Detected WT journal files. Running recovery from last checkpoint.

2017-08-20T16:20:30.972+0000 I STORAGE [initandlisten] journal to nojournal transition config: create,cache_size=17G,session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),checkpoint=(wait=60,log_size=2GB),statistics_log=(wait=0),

2017-08-20T16:20:30.981+0000 E STORAGE [initandlisten] WiredTiger (-31802) [1503246030:981472][31865:0x7f6ad1d9fd80], file:WiredTiger.wt, connection: unable to read root page from file:WiredTiger.wt: WT_ERROR: non-specific WiredTiger error

2017-08-20T16:20:30.981+0000 E STORAGE [initandlisten] WiredTiger (0) [1503246030:981530][31865:0x7f6ad1d9fd80], file:WiredTiger.wt, connection: WiredTiger has failed to open its metadata

2017-08-20T16:20:30.981+0000 E STORAGE [initandlisten] WiredTiger (0) [1503246030:981548][31865:0x7f6ad1d9fd80], file:WiredTiger.wt, connection: This may be due to the database files being encrypted, being from an older version or due to corruption on disk

2017-08-20T16:20:30.981+0000 E STORAGE [initandlisten] WiredTiger (0) [1503246030:981564][31865:0x7f6ad1d9fd80], file:WiredTiger.wt, connection: You should confirm that you have opened the database with the correct options including all encryption and compression options

2017-08-20T16:20:30.981+0000 I - [initandlisten] Assertion: 28718:-31802: WT_ERROR: non-specific WiredTiger error

2017-08-20T16:20:30.982+0000 I STORAGE [initandlisten] exception in initAndListen: 28718 -31802: WT_ERROR: non-specific WiredTiger error, terminating

2017-08-20T16:20:30.982+0000 I CONTROL [initandlisten] dbexit: rc: 100

Is there a way to fix my wiredtiger.wt file or move my collections and indexes from the old location into the new location?

There's now an official repair option that worked for me after a sudden computer shutdown https://docs.mongodb.com/manual/tutorial/recover-data-following-unexpected-shutdown/

Create a backup copy of the data files in the --dbpath
Start mongod with --repair

mongod --dbpath /data/db --repair
Upon completion, the dbpath should contain the repaired data files and
an empty mongod.lock

The collections were repaired without data loss.


Sharing certificates encryped by password between DBs and instances Question:

I am trying to create a single (self signed) certificate and deploy across many databases and instances.

I create and backup the cert with:

USE MASTER
CREATE CERTIFICATE DavesCert ENCRYPTION BY PASSWORD ='S3creT!' WITH SUBJECT = 'The master cert'

BACKUP CERTIFICATE DavesCert TO FILE = 'd:\DavesCert.cer'
WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = 'S3creT!' ,
FILE = 'd:\DavesCert.pvk' ,
ENCRYPTION BY PASSWORD = 'S3creT!' );

I restore with

USE FOO
GO

CREATE CERTIFICATE ERecruitStatsGatheringCert
FROM FILE = 'd:\DavesCert.cer'
WITH PRIVATE KEY (FILE = 'd:\DavesCert.pvk',
DECRYPTION BY PASSWORD = 'S3creT!')

and get the following error: "Please create a master key in the database or open the master key in the session before performing this operation."

I don't want to create a database master key. I'm happy to decrypt the cert by password as needed.

Background:
SaaS application.  Many to Many relationship between DB's and instances.

I need each DB to be able to query it's own stats by executing a stored procedure that wraps up some calls to DMV's to return the stats.

App runs under a low-privileged account. DMV's require VIEW SERVER STATE permission, therefore, I'm implementing signing of the stored procedures using certs.

Basic way of setting this up is to:

Create a cert in the user DB.
Backup/restore that cert to master.
Create login in master, assigned that permissions, etc.
Add Certificate to stored procedure.

I have test code for the above and it works well, however the model does not scale very well for deployment across multiple instances/dbs.

Therefore, I think I want to use the same cert across all DB's/instances.

Happy for other suggestions/approaches.

--Originally posted to Stack Exchange then moved on suggestion from another use

You only specify the password to decrypt the private key file with. You need to add a password to store the certificate with:

CREATE CERTIFICATE ERecruitStatsGatheringCert
ENCRYPTION BY PASSWORD = 'S3creT!
FROM FILE = 'd:\DavesCert.cer'
WITH PRIVATE KEY (
    FILE = 'd:\DavesCert.pvk',
    DECRYPTION BY PASSWORD = 'S3creT!');

But your Background information makes the whole exercise futile. You're doing it wrong. The correct sequence of actions is:

Create a cert in the user DB
Add the signature to the store procedure(s)
Drop the private key of the certificate
Backup/restore that cert to master (public key only!)
Create login in master, assigned that permission, etc.

Notice that not only the private key never leaves the database, is in fact explicitly dropped right after signing the procedure. This is required in order to prevent further use of this certificate to sign other procedures and abuse the login permissions created at step 5. You repeat these steps on each database and use a different certificate on each database. You repeat these steps each time you modify any of the signed procedure and generate a new certificate each time.

As a general rule, any signature/encryption public/private key (RSA) scheme that requires a copy of the private key is broken. This is why is called private and this is what gives value to the signature: the knowledge that there exists only one instance of this private key in the world, therefore anything signed by it is proof that it came from the one and only owner/holder of the unique private key.

I know there are some who shun my recommendation of dropping the private key right after is used to sign the procedure. I stand by my recommendation, but the important thing for your problem is that it is not required to copy the private key into [master] in order to leverage the the code signing permission you desire. You can (and should) create the login derived from the certificate using just the public key.

Regarding automation: As I see it, basically it is a tool (script, app) that takes two inputs: a securable (procedure, function, module) and a permission (VIEW SERVER STATE). Everything else is an automated process (create a one-time-use throw-away cert, sign, export cert, import cert in [master], create login, grant permission). The names implied (cert name, login name) can all be generated.

Proliferation of names is a valid concern. An alternative would be to use only one certificate and associated private key, and the signature tool could add the private key when needed (when signing the procedure), then remove it after the signing. Is really a matter of how you run your shop and how important is the asset you're protecting. But the important thing is that you do not need to import the private key in [master].


Different categories of missings Most database systems can represent missing values, typically as "Null" or something similar.  But what if I want to represent different categories of missing data?

For categorical data, this is not necessarily a big problem.  Just create additional categories for each type of missing.  But this can be a more challenging problem when representing continuous data (e.g., income).  Some people will use codes like 99999, 99998, 99997 to represent missings, leaving it to the user to recognize that these values are missing and recode them as such when performing analysis.  I would like to avoid this.  Other workarounds include having a parallel table that includes a categorical variable for each continuous variable, where the categorical variable indicates missingness and category of missingness.  I would also like to avoid this.

For comparison, Stata (which, of course, is not a database management system) stores data in a single table and encodes missings as ., .a, .b, etc.  Each of these categories of missing can have a different label affixed to it indicating its category of missing.

What I would love is if a database system could accommodate missings the way that stata does.  Basically, different types of Nulls, each with their own label.  Does anything like this exist?  Is there a convenient way to simulate this functionality if it does not exist with any database or DBMS?

For context, I am a 'data scientist' (what does that even mean).  We are collecting a large amount of not-very-well-organized data and normalizing it in a database.  Some data is missing for reasons we don't know (data entry error?).  Other data is missing because it is not applicable to the person.  Sometimes it is missing because the value was implausible and we recoded it as missing.

The appropriate way to handle these missings in subsquent statistical analysis will depend upon why these variables are missing.  It is important not to throw away this information as we take disorganized flat files and convert them into our well-organized, normalized data tables.

I imagine you experts might be familiar with this problem and might have some insights into how to deal with it.  Maybe I am thinking about this problem the wrong way.

SQL has a single category NULL and boolean algebra is extended to the ternary case with all the boolean operators AND, OR, NOT and XOR are modified with respect to the third logical state "Unknown" or "Unavailable". All the other subcategories can be implemented as reference table of arbitrary numbers of states. That is for relational databases.


Why Database use Buffer pool for write operation? In this case, first we write data in RAM,then same data write in database?

Why we need  do write operation two time?

If you are using SQL Server, write operations pull pages into memory if they need to be modified and write the changes to the page in memory as well as the transaction log. At given intervals those changes, and other changes made to adjacent pages since the last disk write operation occurred, are written to disk. This allows the server to make more logical writes to the disk in one go, making that write operations much more efficient.

Here are some links with good explanations (again assuming you are using SQL Server):

When things are written to disk: http://technet.microsoft.com/en-us/library/ms189573%28v=sql.105%29.aspx
What happens during a write operation: http://technet.microsoft.com/en-us/library/aa337560%28v=sql.105%29.aspx


SQL Alwayson availabity group latency We have sql server 2014 alwayson availability group set up for a database. Synchronous-commit availability mode is used. We ran a few PAL (Performance Analyzer Log) reports to obtain performance data on the primary replica and they showed IO alerts where read and writes were >25ms. The IO alerts occurred for both logical and physical IOs and on drives C:, F: (that has db files) and L: (that has transaction logs). I ran this statement:

SELECT
    wait_type, waiting_tasks_count, wait_time_ms,
    wait_time_ms/waiting_tasks_count as 'time_per_wait'
FROM
    sys.dm_os_wait_stats
WHERE
    waiting_tasks_count > 0
    and wait_type = 'HADR_SYNC_COMMIT';

and got 12ms for the time_per_wait value. This means the latency between the primary and secondary replicas were 12 ms.

My question: does the IO response time reported by PAL include this 12ms latency between the primary and the secondary replicas?

Thanks

According to PSS that wait type is the time it takes for the primary to receive a notification that the log block has been written on the destination (though not replayed), and after this the wait type changes to WRITELOG and the log block gets written locally.

Logically then any disk waits to write the block remotely must be included in that HADR_SYNC_COMMIT time.

Perhaps if you run PAL on your secondary you will see that it has lower disk latency and so can complete the writes faster. It would be interesting for you to look and see.

Incidentally the MSSQL Tiger Team have a video and demo XE script and Power BI sample that gathers AG sync information and displays it in graphs; if you're interested in exploring that you could give it a try.


12.0 Access Database Engine OLD DB Provider Not showing up in SQLEXPRESS 2008 I am trying to create a linked server to an 32 bit 2010 access database inside of SQLEXPRESS 2008 R2 64bit. Everything I've read has pointed me at the download : Access Database Engine

I've downloaded the 32 bit version (64 bit tells me my office is 32 bit) and installed it; however, the drop down in SQLEXPRESS > Create New Linked Server does not come back with an option for Microsoft Office 12.0 Access Database Engine OLD DB Provider.

I tried createing the linked server via sp_createlinkedserver but when I run a test connection on that server I get the error

OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308)

Does this mean that it found the driver I needed? If so how do I get it to run with the linked server?

For a 64bit build of SQL to use the Access drive you'll need to install the 64bit version of the Access drive.  If you have the 32bit version of Office installed you'll need to either remove that and install the 64bit version of office or uninstall the 64bit build of SQL and install the 32bit build.


Is there a maximum length constraint for a postgres query? The app we are building might execute quite a big insert queries. Is there limit that my postgres query can have only a certain number of characters?

For current PostgreSQL version (up to 9.5), queries are received by a backend in a Stringinfo buffer, which is limited to MaxAllocSize, defined as:

#define MaxAllocSize   ((Size) 0x3fffffff) /* 1 gigabyte - 1 */

(see http://doxygen.postgresql.org/memutils_8h.html)

So a query is limited to 1 gigabyte (2^30) in size, minus 1 byte for a terminating null byte.

Should a client try to send a larger query, an error looking like this would come back:

  ERROR:  out of memory
  DETAIL:  Cannot enlarge string buffer containing 0 bytes by N more bytes.

where N is the size of the query.

Be aware that a query just below 1GB might require large amounts of memory to be parsed, planned or executed, in addition to that 1GB buffer.

If you need to push a large series of literals into a query, consider the alternative of creating a temporary table, COPY rows into it and have the main query refer to that temporary table.


Understanding of Oracle Statement-Level Read Consistency between Oracle 10g and 10g+ I'm new to Oracle and I just want to confirm my understanding of Statement-Level Read Consistency for Oracle 10g and 10g+.
The Oracle 10g document states:

Oracle always enforces statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query never sees dirty data or any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins.

Oracle 11g document states:

Oracle Database always enforces statement-level read consistency, which guarantees that data returned by a single query is committed and consistent with respect to a single point in time. The point in time to which a single SQL statement is consistent depends on the transaction isolation level and the nature of the query:

In the read committed isolation level, this point is the time at which the statement was opened. For example, if a SELECT statement opens at SCN 1000, then this statement is consistent to SCN 1000.

In a serializable or read-only transaction this point is the time the transaction began. For example, if a transaction begins at SCN 1000, and if multiple SELECT statements occur in this transaction, then each statement is consistent to SCN 1000.

In a Flashback Query operation (SELECT ... AS OF), the SELECT statement explicitly specifies the point in time. For example, you can query a table as it appeared last Thursday at 2 p.m.

See the bold text. If I understand correctly, Oracle changed its behavior between 10g and 10g+. In 10g, Statement-Level Read Consistency is always based on the view when the statement starts. In 10g+, Statement-Level Read Consistency is based on a view that depends on the isolation level and the query itself. Is my understanding correct? Thank you.

The behaviour did not change. It has been the same since Oracle 7 at least (probably before, but that was before my time) and still is today with Oracle 19. The documentation was just expanded to include the behaviour at isolation levels other than the default READ COMMITTED, or when using the Flashback Query mechanism.

(You should really be looking at the latest documentation: https://docs.oracle.com/en/database/oracle/oracle-database/index.html - 11g is very old)

The important point is that the default isolation used by Oracle (statement-level consistency) implies that repeating the same statement may not deliver the same result since other transactions may have updated the data during the first execution of the statement. Oracle uses multi-version consistency to provide concurrent access such that readers don't block writers and writers don't block readers.


Oracle Data Guard Error DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> show configuration

Configuration - oradb

  Protection Mode: MaxPerformance
  Members:
  oradb    - Primary database
    Error: ORA-16778: redo transport error for one or more databases

    oradb_s2 - Physical standby database
      Warning: ORA-16857: standby disconnected from redo source for longer than specified threshold

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 1 second ago)

DGMGRL>

is someone has any idea about the following error and warning?

ORA-12700 to ORA-19400

  ORA-16857: standby disconnected from redo source for longer than specified threshold

  Cause: The amount of time the standby was disconnected from the redo source database exceeded the value specified by the
  'TransportDisconnectedThreshold' database property. It is caused by no
  network connectivity between the redo source and the standby
  databases.

  Action: Ensure that there is network connectivity between the redo source and standby databases, and the redo source is working properly.


Get rows with minimum value I have tables
company:

    company_id |
- - - - - - - - - - - - - -
      1        |
      2        |

employee:

employee_id |
- - - - - - - - - - - -
      1     |
      2     |

They are connected via table employee_company:

employee_id | company_id
- - - - - - - - - - - -
      1     |     1
      2     |     1
      1     |     2
      2     |     2

Also there is some factor, connected to company via table:

factor_id | company_id
- - - - - - - - - - - -
    1     |     1
    1     |     2
    2     |     3

Also there is score table, and for each score there is unique pair of company and employee:

score_id | employee_id | company_id | score |
- - - - - - - - - - - - - - - - - - - - - - - -
   1     |     1       |      1     | 21
   2     |     1       |      2     | 30
   3     |     2       |      1     | 50
   4     |     2       |      2     | 11

The goal is to get table having set of rows presenting min score for each employee, filtered by factor, something like that:

  | employee_id | score |
 - - - - - - - - - - - - -
        1       |  21
        2       |  11

Now I built query joining all tables and showing all scores:

select * from employee
join employee_company on employee.employee_id=employee_company.employee_id
join company on company.company_id=employee_company.company_id
join score on score.employee_id=employee.employee_id and score.company_id=company.company_id
join factor_company on factor_company.company_id=company.company_id
join factor on factor_company.factor_id=factor.factor_id
where factor.factor_id =:factor_id;

How to reduce it to what I need? Thank you.

Try this query:

select * from employee join employee_company on
employee.employee_id=employee_company.employee_id
join company on
company.company_id=employee_company.company_id
join (select
employee_id,
company_id,
min(score) score from score
group by employee_id,
company_id)
score on score.employee_id=employee.employee_id and
score.company_id=company.company_id
join factor_company on
factor_company.company_id=company.company_id
join factor on
factor_company.factor_id=factor.factor_id
where factor.factor_id =:factor_id;


rt_raster_to_gdal: Could not load the output GDAL driver I have already set the environment in windows

SET postgis.gdal_enabled_drivers = 'ENABLE_ALL';

Even I tried by adding following in environment path-

POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

But still I am getting error:

  ERROR: rt_raster_to_gdal: Could not load the output GDAL driver SQL

The query I am running is

SELECT ST_AsGDALRaster(rast, 'GTiff') As rastjpg
FROM myRaster WHERE rid=1;

How to remove this issue?

It seems likely that you're not using PostGIS 2.2.

Pay special attention to postgis.enable_outdb_rasters.

Taken from my answer on GIS.SE

As explained in the PostGIS blog,

  This has been an issue since our release of 2.0.6/2.13 PostGIS security patches that by default disabled all raster drivers and out-of-db raster storage. Raster is much less useful if you don’t have any raster drivers enabled, so you need to renable them or the selective ones you need.

The interface you're using is referred to as Grand Unified Custom Variables (GUC). It's a PostGIS 2.2 thing.

If using PostGIS 2.1, you do not GUC, you must set them on the command line,

  As of PostGIS 2.1.3, out-of-db rasters and all raster drivers are disabled by default. In order to re-enable these, you need to set the following environment variables POSTGIS_GDAL_ENABLED_DRIVERS and POSTGIS_ENABLE_OUTDB_RASTERS in the server environment.


Find records that do not have matching closing value Sorry for unclear title, i am having trouble formulating the right question subject.
Hopefully the details will help you understand more clearly the issue i am facing.

I have a table with the following schema:

+----+----------+-----+
| ID |  Status  | PID | Time ...
+----+----------+-----+---
|  1 |  STARTED |  10 |
|  2 |  STARTED |  12 |
|  3 |  ENDED   |  10 |
|  4 |  STARTED |  18 |
|  5 |  STARTED |  21 |
|  6 |  ENDED   |  12 |
+----+----------+-----+

Every time a process starts, an entry containing its PID set to 'STARTED' Status in database.
Every time a process finishes its job, another entry containing its PID gets written to the database with the Status value of 'ENDED'.
A process with same 'PID' can start once again after finish.

I would appreciate if someone can guide me in the right direction to a query which will return all the PID's that have started but not (yet) completed.

Desired output:

+----+----------+-----+
| ID |  Status  | PID | Time ...
+----+----------+-----+---
|  4 |  STARTED |  18 |
|  5 |  STARTED |  21 |
+----+----------+-----+

If the process with some PID was started, but was not finished, there is only one record for it.

SELECT pid
FROM table
GROUP BY pid
HAVING COUNT(pid) = 1

If process with the same PIDcan start/finish more than once, working process have odd records count.

HAVING 1 = (COUNT(pid) MOD 2)

Having PIDs list you can easily obtain the whole records.


AND conditions within unbracketed ORs work but why? I have very similar code to the first block below. It is in production in a critical process, and I am not confident that it will always work. I would like to know why it does. It uses or (1=1) to group sets of and conditions. I am concerned that the returned results will change if the SQL version is changed.

Here is the simple table I am using for testing:

Id  Description
1   One
2   Two
3   Three
4   Four
5   Five
6   Six
7   Seven
8   Eight
9   Nine
10  Ten
11  Eleven

This code works, but I don't know why:

SELECT [Id] ,[Description]
FROM [Seminars].[dbo].[CodeTable]
    where (1=1)
    and Id = 1
    and Description = 'One'
    or (1=1)
    and Id = 7
    and Description = 'Seven'
    or (1=1)
    and Id = 3
    and Description = 'Three'

It returns:

Id  Description
1   One
3   Three
7   Seven

Here is the traditional code that uses parentheses (that I would expect to work):

SELECT TOP 1000 [Id], [Description]
FROM [Seminars].[dbo].[CodeTable]
where
    (Id = 1
    and Description = 'One')
or
    (Id = 7
    and Description = 'Seven')
or
    (Id = 3
    and Description = 'Three')

Returns the same result.

This is explained by the rules for operator precedence:

Operator Precedence (Transact-SQL)

As noted there, AND has a higher evaluation precedence than OR.

The 1 = 1 parts of your query are redundant (and removed by the optimizer). You could equally well have written:

DECLARE @Test AS table
(
    Id integer PRIMARY KEY,
    [Description] varchar(10) UNIQUE
);

INSERT @Test
    (Id, [Description])
VALUES
    (1, 'One'),
    (2, 'Two'),
    (3, 'Three'),
    (4, 'Four'),
    (5, 'Five'),
    (6, 'Six'),
    (7, 'Seven'),
    (8, 'Eight'),
    (9, 'Nine'),
    (10, 'Ten'),
    (11, 'Eleven');

SELECT
    T.Id,
    T.[Description]
FROM @Test AS T
WHERE
    T.Id = 1 AND T.[Description] = 'One'
    OR Id = 7 AND T.[Description] = 'Seven'
    OR Id = 3 AND T.[Description] = 'Three';

This produces the same result, for the same reason (precedence):

Demo on Stack Exchange Data Explorer

I would strongly suggest using the version with parentheses for clarity.


Expressing some queries without employing JOIN or OUTER JOIN Assume the following relations schemas:

S(A,B)
T(B,C)

Rewrite the following expressions without using join or outer join operators explicitly:

S LEFT OUTER JOIN T
S FULL OUTER JOIN T

How do I do these two questions? I've tried using a where clause but I don't think it is correct. I know the first one has to use a union but how do I go about writing the queries?

Left outer join will be:

select s.a, s.b, (select c from t where s.b =t.b) as outercol
from s

Full outer join will be:

select s.a, s.b, (select c from t where s.b =t.b) as outercol
from s
union all
select null, b, c from t
where b not in (select b from s)


configsvr and nojournal trying to start mongo config server :

mongod --configsvr --dbpath /data/config-1 --port 27019 --logpath /data/config-1.log --fork --journal

gives:

F CONTROL  [main] Failed global initialization: BadValue: nojournal is not allowed when configsvr is specified

Can anyone advise on how to resolve this one?


Automating SQL scripts in Toad for Oracle (13.1) I'm not a DBA, so bear with me.

I've scheduled tasks through Toad's automation designer, but this uses Windows task scheduler. It's using Oracle 12c. All I'm doing is updating (creating/dropping) tables with the most recent data. Then I use an ODBC to report on that data in another application.

How can I ensure my scripts execute on time without running a local machine with scheduled tasks? Can this be done without additional software? Thanks in advance.

DBMS_SCHEDULER is your friend https://docs.oracle.com/database/121/ADMIN/scheduse.htm
  and  https://oracle-base.com/articles/10g/scheduler-10g – Philᵀᴹ Oct 21 at 19:11

Also, I recommend you use SQL Developer instead.  That software has a GUI interface for creating the scheduled jobs to run.


Planning of Select on PostgreSQL 9.6 partitioned table very slow I have a rather massive table of financial data in Postgres 9.6. It is partitioned by trading day (DATE type) and security ID (BIGINT). Overall the table comprises about 9300 partitions, takeing up 1765 GB in total. I use the conventional method of partitioning using CHECK constraints. The partitions are unlogged tables which I load in-bulk via COPY.

When I perform a query which can be satisfied from a single partition, the planner applies constraint exclusion and determines correctly which table should be consulted. However this takes about 12 seconds, and sometimes a lot more the first time. Could you suggest ways to help the planner work faster?

Example EXPLAIN ANALYZE output:

trading_offline=# EXPLAIN ANALYZE--
select * from public."ticks"
where trading_day ='2016-06-20'::date and inst_id= 1535681 and is_trade = true
and qty<-200;
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Append  (cost=0.00..16751.50 rows=16 width=177) (actual time=299.068..415.080 rows=5 loops=1)
->  Seq Scan on ticks  (cost=0.00..0.00 rows=1 width=161) (actual time=0.008..0.008 rows=0 loops=1)
        Filter: (is_trade AND (qty < '-200'::integer) AND (trading_day = '2016-06-20'::date) AND (inst_id = 1535681))
->  Bitmap Heap Scan on "ticks$20160620$1535681"  (cost=766.35..16751.50 rows=15 width=177) (actual time=299.059..415.068 rows=5 loops=1)
        Filter: (is_trade AND (qty < '-200'::integer) AND (trading_day = '2016-06-20'::date) AND (inst_id = 1535681))
        Rows Removed by Filter: 41833
        Heap Blocks: exact=10002
        ->  Bitmap Index Scan on "ticks$20160620$1535681_is_trade_idx"  (cost=0.00..766.35 rows=41323 width=0) (actual time=45.212..45.212 rows=41838 loops=1)
            Index Cond: (is_trade = true)
Planning time: 12233.724 ms
Execution time: 415.411 ms

The normal advice is not to go above 100 or so partitions, and 9300 is way beyond that.  But still, it shouldn't take 12 seconds.  Creating a simple simulation of 9300 partitions in 9.6, each with one constraint and one index, it takes about 1.2 seconds to plan a query (or about 2.0 seconds to do it the first time in a given session) which hits the parent and exactly one child.

I've repeated the simulation with two separate check constraints, instead of one constraint which ANDs together the conditions, and with an additional index on all the tables, and it didn't meaningfully change the results.  Planning it is not speedy, but still nowhere near 12 seconds.

Can you monitor the system (with top, for example) to see what is going on?  Or better yet, use perf top to identify the bottleneck?


PostgreSQL filtering on array length inside JSON I have a table table with a JSONB field data, which contains a variable-length array, e.g.

{"label": "xyz", "items": [ ... ]}

I created an index on the length of the "items" element:

CREATE INDEX n_items ON table ( JSONB_ARRAY_LENGTH(data->'items') )

but when I filter, I still get a sequential scan when I try to filter on it:

EXPLAIN ANALYZE SELECT COUNT(*) FROM table WHERE JSONB_ARRAY_LENGTH(table.data->'items') = 2;

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Aggregate  (cost=2565655.67..2565655.68 rows=1 width=8)
   ->  Seq Scan on table (cost=0.00..2535256.19 rows=12159794 width=8)
         Filter: (jsonb_array_length((table.data -> 'items'::text)) = 2)
 Planning time: 0.121 ms
 Execution time: 482891.694 ms

That's about 8 minutes to filter! Did I do something wrong here, or is this the consequence of PostgreSQL not keeping statistics on JSON(B) objects? It should be possible to flatten out this data column, but I'd like to be sure that's what I need to do before I start working on it.

edit: these array lengths do not vary much. There are only 4 distinct values in the data currently, and I don't expect to have many more. Is the index just not very useful in this case, or can I improve filtering some other way?

Without knowing your data, I can only guess that the selectivity of your index is low (which happen if the length of the array does not vary much).

One trick to overcome this might be changing the query slightly and creating a covering index.  For this, choose a NOT NULL column (for example, the primary key of the table) to count, and then include this column in the index:

CREATE INDEX n_items ON your_table (jsonb_array_length(data->'items'), id);

SELECT count(id)
  FROM your_table
 WHERE JSONB_ARRAY_LENGTH(table.data->'items') = 2;

This will hopefully turn into an index-only scan (I tested this omitting the jsonb part, but you will  be able to tell if it works).