Disk space full during insert, what happens? Today I discovered the harddrive which stores my databases was full. This has happened before, usually the cause is quite evident. Usually there is a bad query, which causes huge spills to tempdb which grows till the disk is full. This time it was a bit less evident what happened, as tempdb wasn't the cause of the full drive, it was the database itself.

The facts:

Usual database size is about 55 GB, it grew to 605 GB.
Log file has normal size, datafile is huge.
Datafile has 85% available space (I interpret this as 'air': space that was used, but has been freed. SQL Server reserves all space once allocated).
Tempdb size is normal.

I have found the likely cause; there is one query which selects much too many rows (bad join causes selection of 11 billion rows where a couple of hundred thousand is expected). This is a SELECT INTO query, which made me wonder whether the following scenario could have happened:

SELECT INTO is executed
Target table is created
Data is inserted as it is selected
Disk fills up, causing the insert to fail
SELECT INTO is aborted and rolled back
Rollback frees up space (data already inserted is removed), but SQL Server doesn't release the freed up space.

In this situation, however, I wouldn't have expected the table created by the SELECT INTO to still exist, it should be dropped by the rollback. I tested this:

BEGIN TRANSACTION
SELECT  T.x
INTO    TMP.test
FROM    (VALUES(1))T(x)

ROLLBACK

SELECT  *
FROM    TMP.test

This results in:

(1 row affected)
Msg 208, Level 16, State 1, Line 8
Invalid object name 'TMP.test'.

Yet the target table does exist. The actual query wasn't executed in an explicit transaction though, can that explain the existence of the target table?

Are the assumptions I sketched here correct? Is this a likely scenario to have happened?

The actual query wasn't executed in an explicit transaction though,
  can that explain the existence of the target table?

Yes, exactly so.

If you do a simple select into outside of an explicit transaction, there are two transactions in autocommit mode: the first creates the table and the second fills it up.

You can prove it to yourself this way:

In a dedicated database on a test server in simple recovery model, first make a checkpoint and ensure that the log contains only a few rows (3 in case of 2016) related to checkpoint. Then run a select into of one row and check the log again, looking for a begin tran associated with select into:

checkpoint;

select *
from sys.fn_dblog(null, null);

select 'a' as col
into dbo.t3;

select *
from sys.fn_dblog(null, null)
where Operation = 'LOP_BEGIN_XACT'
      and [Transaction Name] = 'SELECT INTO';

You'll get 2 rows, showing you had 2 transactions.

  Are the assumptions I sketched here correct? Is this a likely scenario
  to have happened?

Yes, they are correct.

The insert part of select into was rolled back, but it does not release any data space. You can verify this by executing sp_spaceused; you'll see plenty of unallocated space.

If you want the database to release this unallocated space you should shrink your data file(s).


How can you view the Query Store settings for a database? I see that you can use notion  like the following to set the query store configuration:

ALTER DATABASE <database name>
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

But how can I check to see what the current value for SIZE_BASED_CLEANUP_MODE is?

This can be found (near the end) in the sys.database_query_store_options DMV.

SELECT size_based_cleanup_mode, size_based_cleanup_mode_desc
FROM sys.database_query_store_options


operator does not exist: timestamp with time zone + integer in PostgreSql I came across with a strange issue in postgres. I have two databases create in two different periods.
Both are running in the same PostrgreSql 9.6 version in the same machine. When I run the query SELECT now() + 30 in database 1 -> Its working properly.
The same query I am copy paste in the second database, its giving error:  operator does not exist: timestamp with time zone + integer in PostgreSql
Can some one guide me on this.

Maybe someone created the operator with those types in one database, but not in the other one.

create function addit(timestamptz,int) returns timestamptz immutable language sql as $$
    select $1+ interval '1 day'*$2
$$;
create operator + (leftarg =timestamptz, rightarg =int, procedure=addit);


Performance of selecting strings: substring IN vs regular expression I have a large table with 1 billion+ records. These are the important columns:
CREATE TABLE BigTable
(
   id BIGSERIAL PRIMARY KEY NOT NULL,
   hexid VARCHAR(255), -- usually 24 or 48 characters
   type VARCHAR(255),
   <and other columns>
);

I need to find some records matching on a substring of the hexid column as well as the type column. There is an index on the hexid column. I need to search for a list of strings matching the last 24 characters of the hexid (the records I am interested in will always have 48 character hexids). There are two queries that I could run and I'm wondering which will perform better:
Query 1 using WHERE IN:
SELECT * FROM BigTable
WHERE substring(hexid, 25) IN (
    'deadbeef4b00018c09bf5db1',
    '5f478c50deadbeef97039344',
    '5fc0b855a6a8b600deadbeef')
AND type IN ('OneType', 'SomeType', 'AnotherType')
ORDER BY id ASC;

with explain plan:
Sort  (cost=105476938.69..105477029.92 rows=36492 width=638)
  Sort Key: id
  ->  Seq Scan on BigTable  (cost=0.00..105468257.46 rows=36492 width=638)
        Filter: (((type)::text = ANY ('{OneType,SomeType,AnotherType}'::text[])) AND (""substring""((hexid)::text, 25) = ANY ('{deadbeef4b00018c09bf5db1,5f478c50deadbeef97039344,5fc0b855a6a8b600deadbeef}'::text[])))"

Query 2 using regular expressions:
SELECT * FROM BigTable
WHERE hexid ~ '\w{24}(deadbeef4b00018c09bf5db1|5f478c50deadbeef97039344|5fc0b855a6a8b600deadbeef)'
AND type ~ '(One|Some|Another)Type'
ORDER BY id ASC;

with explain plan:
Sort  (cost=100022576.55..100022577.16 rows=243 width=638)
  Sort Key: id
  ->  Seq Scan on BigTable  (cost=0.00..100022566.92 rows=243 width=638)
        Filter: (((hexid)::text ~ '\w{24}(deadbeef4b00018c09bf5db1|5f478c50deadbeef97039344|5fc0b855a6a8b600deadbeef)'::text) AND ((type)::text ~ '(One|Some|Another)Type'::text))

Based on the explain plans the cost will be more or less the same.

Query 1 performs a substring, doesn't have pattern matching [fast], but does have WHERE IN clauses [can negatively affect performance].
Query 2 has pattern matching (that takes care of the substring) [slow], but doesn't have WHERE IN clauses [doesn't negatively affect performance].

Getting the data is a once-off process. I don't have the luxury of time running these queries individually to determine which one performs better. I need to make an informed decision on which one to go with.

Which query will give the best performance?
Why does the first explain plan show rows=36492 and the second rows=243?
Is there perhaps another query that would perform much better? Keep in mind that, since it is a once-off query, adding additional indices would not be helpful.

(I'm interested in knowing the answers for PostgreSQL, but I guess it will be similar for other RDBMSs.)

The version with IN will be slightly faster, because = ANY is cheaper than ~.
The estimate suggests otherwise, but only because it estimates a different number of result rows.
You can make these queries faster with an index on the more selective condition.


Excluding clustered key columns from non-clustered indexes definitions As I've read the bookmark or the reference of the non-clustered index to the table clustered index is the clustered index key itself. On the SQL Saturday event I've attended, one of the lecturers said it's always good practice to exclude the clustered key columns from the non-clustered index key definition and include clause, too.

I am wondering are there any negatives of doing so, for the following scenario:

Table A has the following columns: RecordID, QuestionID, Pts, PtsOf and many other.
I have created a non-clustered index like this:

CREATE NONCLUSTERED INDEX [IX_TableA_RecordID_QuestionID] ON [dbo].[TableA]
(
    [RecordID] ASC
   ,[QuestionID] ASC
)INCLUDE ([Pts],[PtsOf]);

in order to skip reading the clustered index when score is calculated per records and questions. The [RecordID][QuestionID] pair is the clustered index key.

If I change the index like this:

CREATE NONCLUSTERED INDEX [IX_TableA_RecordID_QuestionID] ON [dbo].[TableA]
(
    [Pts] ASC
   ,[PtsOf] ASC
);

The indexes is again used as before (it satisfies the search criteria) even for queries that are not referring the points columns:

SELECT [RecordID]
      ,[QuestionID]
FROM [dbo].[TableA];

SELECT [RecordID]
FROM [dbo].[TableA];

SELECT [QuestionID]
FROM [dbo].[TableA];

So, the engine is smart enough to see using the non-clustered index will reduce the reads and I know leaving it as the original definition is working, too, but some of the indexes are quite big and I think removing some of the keys can reduce their size.

What I am concerned about is that the first index rows were ordered by the bookmark and now they are not. I am wondering if and how this could harm the performance?

On the SQL Saturday event I've attended, one of the lecturers said it's a always good practice to exclude the clustered key columns from the non-clustered index key definition and include clause, too.

I disagree. Let me explain with the table in your question:

The clustered index is: (RecordID, QuestionID) and there are many more columns.

Any non-clustered index will also have the clustered key columns, too, appended in the end. So, an index like:

(Pts)   is equivalent to:   (Pts, RecordID, QuestionID)

and similarly:

(Pts, PtsOf)           <->:  (Pts, PtsOf, RecordID, QuestionID)

(Pts, RecordId)        <->:  (Pts, RecordID, QuestionID)

(Pts, QuestionID)      <->:  (Pts, QuestionID, RecordID)

(Pts) INCLUDE (PtsOf)  <->:  (Pts, RecordID, QuestionID) INCLUDE (PtsOf)

(QuestionID, RecordID) <->:  (QuestionID, RecordID)

For joint tables or like this one that have a composite primary/unique key - whether it is clustered or not, it's very often to have queries that need the (a,b) index and others that will use better the (b,a) index,  sometimes queries that need both. So, one often needs both of these.

If the composite clustered key has more than two columns - say (a,b,c) - it's often that you may need an index on (b,c,a) or in (d,b) and another on (e,c,a) (which of course will be equivalent to (b,c,a), (d,b,a,c) and (e,c,a,b) respectively.) You can't just remove these columns from the definitions of the non-clustered keys because the column order will change.

The suggestion has one good point though. The clustered key columns can be removed from the INCLUDE part. They are redundant, just noise there.

About the indexes in the question, a non-CI on (Pts, PtsOf) is equivalent to (Pts, PtsOf, RecordID, QuestionID), so it is very different than the original non-CI on (RecordID, QuestionID) INCLUDE (Pts, PtsOf).  It will use a bit more space than the original and of course these two indexes will be useful for different types of queries.

The (Pts, PtsOf) will be be useful, for example, for queries with WHERE Pts BETWEEN 0 AND 100, WHERE Pts = 200 AND PtsOf = 300, etc.
The (RecordID, QuestionID) INCLUDE (Pts, PtsOf) is basically a copy of the table with only the 2 clustered key columns and 2 only extra columns (of the many). This is (rarely) useful and it's a form of vertical partitioning. If you often have queries that need all the rows of the table but only these 2 columns, then it's probably one these (rather rare) cases where the extra space and effort to maintain this index is justified.


WiredTiger prefix compression for index on MongoDB According to the MongoDB documentation (at least this blog entry https://www.mongodb.com/blog/post/new-compression-options-mongodb-30), prefix compression is enabled by default on MongoDB.

However, when I look at one collection, I see this:

db.myCollection.stats()
{
...,
    "wiredTiger" : {
        "metadata" : {
            "formatVersion" : 1
        },
        "creationString" : "...,prefix_compression=0,prefix_compression_min=4"
...
}

Does prefix_compression=0 means that my indexes are not prefix-compressed ?

By default collection stats only include information on the collection data (which uses block-level compression rather than prefix compression).

To see index details you need to provide an additional indexDetails option, eg:

db.myCollection.stats({indexDetails:true}).indexDetails._id_.creationString

You should see "prefix_compression=true" in the index creationString value.


Update query race condition Consider this simple query

UPDATE [SomeTable] SET Value = Value + 1 WHERE Id = 1

For almost any programming language this has a potential risk of race condition if it is executed by more than one thread at the same time.

The question is: Does it apply to Sql?

I believe UPDATE will grab a write lock to that row that prevents other write operations to it

So if two threads try to execute this query the order should be like

T1: write lock
T2: attempt to grab write lock (blocked)
T1: read value
T1: write value = value +1
T1: release write lock
T2: write lock
T2: read value
T2: write value = value + 1
T2: release write lock

Result: Value = Value + 2

Or could it look like this

T1: read value
T1: write lock
T2: read value
T2: attempt to grab write lock (blocked)
T1: write value = value +1
T1: release write lock
T2: write lock
T2: write value = value + 1
T2: release write lock

Result: Value = Value + 1

Mostly interested in Sql server behavior but would be nice to know if the behavior is consistent across all ANSI compliant DBMS

You will typically get result #1 (Value=2).

Update locks make the queries run sequentially

The UPDATE query will place an update lock from the start, because it knows that it will update the row. This prevents another query from placing an update lock of its own, meaning that they will run sequentially.

There are different so-called isolation levels that may be implemented slightly differently in different database platforms, but to the best of my knowledge, the above reflects how it works in SQL Server.

The second scenario

Your second scenario (read-only lock, which escalates to an exclusive read-write lock) can only happen if you separate the UPDATE into two statements, and provided you have a less restrictive isolation level (i.e. not for instance SERIALIZABLE).

DECLARE @val int;

BEGIN TRANSACTION;

    SELECT @val=Value FROM [Some table] WHERE Id=1;
    UPDATE [Some table] SET Value=@val+1 WHERE Id=1;

COMMIT TRANSACTION;

Again, if you have a restrictive isolation like SERIALIZABLE, the initial SELECT statement will place an exclusive lock on the row immediately, preventing other processes from viewing or changing it. If you use, for instance, READ COMMITTED (the default level), the SELECT statement places a shared lock, so other processes can read the row as well.

In-memory OLTP (Hekaton)

If you're using in-memory tables, like @Piotr commented, the second query will fail because it doesn't wait for the first query to complete. Because of this, the row would have changed by the time the second query tries to commit its own changes, so the result will be Value=1.

For this reason, it is best practice to trap and retry data modifications in the client app when using in-memory tables.


How to make a relation database match a dynamic composition of data? I have a database which has a table that contains resources. It does not matter what the resource are, they have a fixed number of properties so perfect for a table.

I would like to have consumers that must be stored in the database. My problem, as far as I am concerned, is that these consumers consume a certain amount of resources that is not constant. Furthermore these consumers can also be Used by other consumers.

I realize that a graph database, like e.g. TinyVirtuoso, can seem like something that is optimal, but for the moment I need it in a relational database format.

So far I was able to come up with the following ideas:

If I were to make assumptions about the number of resource a consumer can have.

Make a table with the maximum number of resources for each consumer,

If no assumptions for the number of resources, then I suppose just a normalized design will have to do.

So I would like to know if there is a smarter way to do this, or this is just about it.

I'd go for the normalized design, mainly for readability and maintainability reasons.

Looks like you have n:m relationships all the time, then i'd use

Resource(key,p1,p2,...,pn)
Consumer(key,p1,p2,...,pm)
Consumer_Resource(resourcekey,consumerkey,...)
Consumer_Consumer(consumerkey1,consumerkey2,...)

This is a tried and tested way to go about such a scenario, and will be understood by lots of database folks.


Can't login ORA-28031: maximum of 148 enabled roles exceeded I can't login - and receive the error code above. How do I fix this?

to let the user connect you need revoke some roles from the user

see discussion about here: AskTom

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1565923300346644029

the discussion about parameter MAX_ENABLED_ROLES that used to limit max count of roles to 150 roles. the parameter is marked as deprecated but as the discussion says it still had some value for limit

  It is deprecated because they are currently maxing it out (just like
  maxtrans is deprecated - but still "exists", it is 255 no matter what
  you set it to)
  MAX_ENABLED_ROLES

Range of values 0 to 148

  deprecated

  MAX_ENABLED_ROLES specifies the maximum number of database roles that
  users can enable, including roles contained within other roles. The
  actual number of roles that users can enable is 2 plus the value of
  MAX_ENABLED_ROLES, because each user has two additional roles, PUBLIC
  and the user's own role.


How to list LOGIN + ROLE in SQL Server 2008? I'm looking for an answer but I can't find it. Just a lot of long scripts.

I need to list Login | Role (sysadmin, dbcreator, etc ) but I can't find a table to join.

I have this for now:

SELECT
    name AS Login_Name, type_desc AS Account_Type
FROM
    sys.server_principals
WHERE
    TYPE IN ('U', 'S', 'G')
    AND name NOT LIKE '%##%'
ORDER BY
    name, type_desc

I need another column with the role.

With table should I search for it?

Thanks.

EDIT:

I see that sys.syslogins has some information with binary.

Is it possible to use a CASE here? ( Programming is not my best )

No, don't use sys.syslogins - this is a deprecated compatibility view that is only there so people have time to change their existing code based on SQL Server 2000. Also, I'm completely unclear how you think a CASE expression will help you. You need to join to sys.server_role_members to find out which logins belong to which roles, and then back to sys.server_principals again to get the name of each role.

SELECT p.name AS Login_Name, p.[type_desc] AS Account_Type,r.name AS [Role]
FROM sys.server_principals AS p
LEFT OUTER JOIN sys.server_role_members AS rm
ON p.principal_id = rm.member_principal_id
LEFT OUTER JOIN sys.server_principals AS r
ON rm.role_principal_id = r.principal_id
AND r.[type] = 'R'
WHERE p.[type] IN ('U', 'S', 'G')
and p.name not like '%##%'
ORDER BY p.name, p.[type_desc];

(I'm not sure what value you expect from the second ORDER BY column here.)

If you want only a single row per login:

SELECT p.name AS Login_Name, p.[type_desc] AS Account_Type,
  Roles = STUFF((SELECT N',' + r.name
    FROM sys.server_role_members AS rm
    INNER JOIN sys.server_principals AS r
    ON rm.role_principal_id = r.principal_id
    WHERE rm.member_principal_id = p.principal_id
    ORDER BY r.name
    FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,N'')
FROM sys.server_principals AS p
WHERE p.[type] IN ('U', 'S', 'G')
and p.name not like '%##%'
ORDER BY p.name;


SQL pivot/transpose/row-column non-proprietary Maybe this question has been asked already (it should
be quite simple). I've Googled loads of phrases
(PIVOT, ROWS to COLUMNS, TRANSPOSE &c.) and all I get is
TSQL this, TSQL that and or Oracle this, Oracle
that, proprietary function this, proprietary function that well, you get the picture.

I have a simple table structure - if it was any simpler, it'd
be a list :-) - i.e. it's two columns.

stock_date       sku
----------       ---
2016-06-01         0
2016-06-02       123
2016-06-03       123
2016-06-04       123
2016-06-06       123
2016-06-07       123
2016-06-03       245
<... &c...>

There may be many thousands of sku's but the date range
will be at most a month or so, but can vary, so the code needs to cater for that.

So, I want to make the stock_date field a column.

sku     1   2   3   4   5   6   7
---     -   -   -   -   -   -   -
123     0   Y   Y   Y   0   Y   Y
245     0   0   Y.... &c.

Ideally, the code would be dynamic and would work with any server (even MySQL).

But if it has to use common table expressions and/or window functions, that's
OK but not proprietary crosstab or pivot functions.

A PIVOT can be written as a CASE statement. So for your example:

SELECT
    sku,
    MAX (CASE WHEN stock_date = '2016-06-01' THEN 'Y' ELSE 'N' END) AS [1],
    MAX (CASE WHEN stock_date = '2016-06-02' THEN 'Y' ELSE 'N' END) AS [2],
    MAX (CASE WHEN stock_date = '2016-06-03' THEN 'Y' ELSE 'N' END) AS [3],
    ...
FROM
    dbo.mytable
GROUP BY sku

Note that this can (and should) be dynamically generated.

(Source: https://avaldes.com/pivot_using_case/)


How can we add BOOLEAN datatype in mysql query browser? I am trying to add a BOOLEAN column using EDIT TABLE in mysql workbench, but it automatically changes to TINYINT on applying changes.

How can I get the column to be BOOLEAN?

BOOL and BOOLEAN are synonyms of TINYINT in MySQL. See MySQL docs: Numeric Type Overview

You probably want to use the BIT datatype.


Seek and you shall Scan... on partitioned tables I've read these articles in PCMag by Itzik Ben-Gan:

Seek and You Shall Scan Part I: When the Optimizer Doesn't Optimize
Seek and You Shall Scan Part II: Ascending Keys

I’m currently having a "Grouped Max" problem with all of our partitioned tables.
We use the trick Itzik Ben-Gan provided for getting a max(ID), but sometimes it just doesn’t run:

DECLARE @MaxIDPartitionTable BIGINT
SELECT  @MaxIDPartitionTable = ISNULL(MAX(IDPartitionedTable), 0)
FROM    ( SELECT    *
          FROM      ( SELECT    partition_number PartitionNumber
                      FROM      sys.partitions
                      WHERE     object_id = OBJECT_ID('fct.MyTable')
                                AND index_id = 1
                    ) T1
                    CROSS APPLY ( SELECT    ISNULL(MAX(UpdatedID), 0) AS IDPartitionedTable
                                  FROM      fct.MyTable s
                                  WHERE     $PARTITION.PF_MyTable(s.PCTimeStamp) = PartitionNumber
                                            AND UpdatedID <= @IDColumnThresholdValue
                                ) AS o
        ) AS T2;
SELECT @MaxIDPartitionTable

I get this plan

But after 45 minutes, look at the reads

reads          writes   physical_reads
12,949,127        2       12,992,610

that I get out of sp_whoisactive.

Normally it runs quite quickly, but not today.

Edit:
table structure with partitions:

CREATE PARTITION FUNCTION [MonthlySmallDateTime](SmallDateTime) AS RANGE RIGHT FOR VALUES (N'2000-01-01T00:00:00.000', N'2000-02-01T00:00:00.000' /* and many more */)
go
CREATE PARTITION SCHEME PS_FctContractualAvailability AS PARTITION [MonthlySmallDateTime] TO ([Standard], [Standard])
GO
CREATE TABLE fct.MyTable(
    MyTableID BIGINT IDENTITY(1,1),
    [DT1TurbineID] INT NOT NULL,
    [PCTimeStamp] SMALLDATETIME NOT NULL,
    Filler CHAR(100) NOT NULL DEFAULT 'N/A',
    UpdatedID BIGINT NULL,
    UpdatedDate DATETIME NULL
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
    [DT1TurbineID] ASC,
    [PCTimeStamp] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = ROW) ON [PS_FctContractualAvailability]([PCTimeStamp])
) ON [PS_FctContractualAvailability]([PCTimeStamp])

GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_UpdatedID_PCTimeStamp] ON [fct].MyTable
(
    [UpdatedID] ASC,
    [PCTimeStamp] ASC
)
INCLUDE (   [UpdatedDate])
WHERE ([UpdatedID] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = ROW) ON [PS_FctContractualAvailability]([PCTimeStamp])
GO

The basic issue is that the Index Seek is not followed by a Top operator. This is an optimization that is usually introduced when the seek returns rows in the correct order for a MIN\MAX aggregate.

This optimization exploits the fact that the min/max row is the first one in ascending or descending order. It may also be that the optimizer cannot apply this optimization to partitioned tables; I forget.

Anyway, the point is that without this transformation, the execution plan ends up processing every row that qualifies S.UpdatedID <= @IDColumnThresholdValue per partition, rather than the desired one row per partition.

You have not provided table, index, or partitioning definitions in the question so I can't be much more specific. You should check that your index would support such a transformation. More or less equivalently, you could also express the MAX as a TOP (1) ... ORDER BY UpdatedID DESC.

If this results in a Sort (including a TopN Sort), you know your index is not helpful. For example:

SELECT
    @MaxIDPartitionTable = ISNULL(MAX(T2.IDPartitionedTable), 0)
FROM
(
    SELECT
        O.IDPartitionedTable
    FROM
    (
        SELECT
            P.partition_number AS PartitionNumber
        FROM sys.partitions AS P
        WHERE
            P.[object_id] = OBJECT_ID(N'fct.MyTable', N'U')
            AND P.index_id = 1
    ) AS T1
    CROSS APPLY
    (
        SELECT TOP (1)
            S.UpdatedID AS IDPartitionedTable
        FROM fct.MyTable AS S
        WHERE
            $PARTITION.PF_MyTable(S.PCTimeStamp) = T1.PartitionNumber
            AND S.UpdatedID <= @IDColumnThresholdValue
        ORDER BY
            S.UpdatedID DESC
    ) AS O
) AS T2;

The plan shape this should produce is:

Notice the Top below the Index Seek. This limits the processing to one row per partition.

Or, using a temporary table to hold partition numbers:

CREATE TABLE #Partitions
(
    partition_number integer PRIMARY KEY CLUSTERED
);

INSERT #Partitions
    (partition_number)
SELECT
    P.partition_number AS PartitionNumber
FROM sys.partitions AS P
WHERE
    P.[object_id] = OBJECT_ID(N'fct.MyTable', N'U')
    AND P.index_id = 1;

SELECT
    @MaxIDPartitionTable = ISNULL(MAX(T2.UpdatedID), 0)
FROM #Partitions AS P
CROSS APPLY
(
    SELECT TOP (1)
        S.UpdatedID
    FROM fct.MyTable AS S
    WHERE
        $PARTITION.PF_MyTable(S.PCTimeStamp) = P.partition_number
        AND S.UpdatedID <= @IDColumnThresholdValue
    ORDER BY
        S.UpdatedID DESC
) AS T2;

DROP TABLE #Partitions;

Side note: accessing a system table in your query prevents parallelism. If this is important, consider materializing the partition numbers in a temporary table, then APPLY from that. Parallelism is not normally helpful in this pattern (with correct indexing) but it would be remiss of me not to mention it.

Side note 2: There is an active Connect item requesting built-in support for MIN\MAX aggregates and Top on partitioned objects.


How can I replace multiple correlated subqueries in the SELECT clause by LEFT JOINs? A fiddle for my question can be found on https://dbfiddle.uk/?rdbms=postgres_10&fiddle=e387589d446d9c9a952294f8c7a98494.

I have simple table layout:

class
person: belongs to a class
room:   belongs to a class

The following query selects all classes with its persons embedded:

select    class.identifier, array(select person.identifier from person where person.class_identifier = class.identifier) as persons
from      class
order by  class.identifier;

While experimenting and learning more about correlated subqueries, I noticed that the query above could be rewritten by replacing the correlated subquery with a LEFT JOIN combined with a GROUP BY:

select     class.identifier, array_agg(person.identifier) as persons
from       class
left join  (
             select  person.identifier, person.class_identifier
             from    person
           ) as person
on         class.identifier = person.class_identifier
group by   class.identifier
order by   class.identifier;

Please note that I made the assumption that each class has at least one person. If not, I could add coalesce() around json_agg.

In my second case, I am going to select all classes with its persons and its rooms embedded. Let's first write in the same manner as the first query above:

select    class.identifier, array(select person.identifier from person where person.class_identifier = class.identifier) as persons,
                            array(select room.identifier from room where room.class_identifier = class.identifier) as rooms
from      class
order by  class.identifier;

This gives the expected results.

Now I want to repeat what I did before: introduce LEFT JOINs. My first attempt was as following:

select     class.identifier, array_agg(person.identifier) as persons
                           , array_agg(room.identifier) as rooms
from       class
left join  (
             select  person.identifier, person.class_identifier
             from    person
           ) as person
on         class.identifier = person.class_identifier
left join  (
             select  room.identifier, room.class_identifier
             from    room
           ) as room
on         class.identifier = room.class_identifier
group by   class.identifier
order by   class.identifier;

Now I am getting wrong results. Persons or rooms are being repeated in the output arrays. I understand why this happens (we are grouping on the cartesian product between class, person and room, hence every person is repeated for every room and vice versa), but I don't know how to proceed.

How can I continue here? Is replacing multiple correlated subqueries in a SELECT clause to LEFT JOIN + GROUP BY possible to start with, or need I other tricks?

You can aggregate over distinct values as:

array_agg(distinct person.identifier)

However, I'm not sure why you are joining against these sub-selects. You can join directly against the tables like:

select class.identifier, array_agg(distinct person.identifier) as persons
                       , array_agg(distinct room.identifier) as rooms
from class
left join  person
    on class.identifier = person.class_identifier
left join room
    on class.identifier = room.class_identifier
group by   class.identifier
order by   class.identifier;

I would also suggest that you use an alias for your tables:

select c.identifier, array_agg(distinct p.identifier) as persons
                   , array_agg(distinct r.identifier) as rooms
from class c
left join  person p
    on c.identifier = p.class_identifier
left join room r
    on c.identifier = r.class_identifier
group by   c.identifier
order by   c.identifier;

Oh, and welcome to the site. A nice first question with code and sample data, well done.


How to give an existing server login access to an attached/migrated database I'm migrating databases from SQL Server 2012 to 2014. I detached the databases from source server, then copied the files (.mdf, .ldf, etc.) to the new server and attached them. That process went fine, now the databases are available on SQL Server 2014.

The problem now is that when I go to Security -> Logins on the new server and check "User Mappings" for LoginX all the new attached databases are checked and apparently LoginX should have access to them.  But when trying to access those databases using that login they are not available for that user.  I have tried several things:

I tried to remove those mappings to try to recreate them but it fails saying that "Cannot drop the user dbo.. (Error 15150)".
Also checked Security -> Users under the new attached databases and there is no user associated with LoginX.  Well I tried creating a user for that login on one of the databases and that also fails saying that "The login already has an account under a different user name. Error 15063.
I have also tried sp_change_users_login 'Auto_Fix' 'loginX' but that did not work and actually was expected to do nothing because loginX is not an orphan... but I'm running out of ideas.
Deleted LoginX from server and then recreated it and then the mappings just reappeared (I did not put them there).  I have no clue why/how those mappings are there.
According sp_change_users_login 'report' there are no duplicate logins or orphan logins/users.

The question is, how can I fix that? This scenario is kind of the opposite of an orphan where I have a login and it is supposed to have access to some databases but the databases do not know about the login and I don't know how to recreate that link or destroy it and make it right.

All users/logins are Windows/Active Directory users so SIDs are (should be?) the same for both servers.

The login was not created using same SID.  We are migrating dozens of databases, so the migration was scripted using the detach/attach method.

All our users/logins are Windows AD logins. I'm going to check that maybe I have mixed SQL and Windows types.

I tried ALTER USER [user name] WITH LOGIN = [Windows Account]; but it fails saying "Cannot alter the user because it does not exists or you do not have permission" (Error 15151). I can see the user listed on server logins and I'm using a sysadmin account.

Finally worked after changing the database owner.  The database was reattached by a different user so the owner was something else.

use [database name];
exec sp_changedbowner @loginame = 'loginX';

After that the user loginX was able to access the database.  It still does not show under Security -> Users but the database is mapped for the loginX login on the server.


How to remove database from explorer after deleting .MDF and .ldf files I wanted to know if I can delete or remove the database shown in the object explorer after someone  has manually deleted the MDF and ldf files from the database folders.

Are you attempting this with SSMS? I followed this process using SSMS v18.4:

Take DB offline
Manually delete mdf and ldf files.
Right click on database in SSMS and click 'Delete'.

This worked just fine for me.


Is it necessary for a foreign key to be a primary key in another table? Why? I am new to database implementation. I want to know if it is necessary for a foreign key to be primary key of another table.
Foreign key is not unique - it may have duplicate entries. If it must be primary key of another table then why it is necessary?
I think any column can be a foreign key because you can put condition at any column.

The referencing column may not be unique, as you point out. If the referenced column is also not unique, you have created a many-to-many relationship, which is a right pain to implement and can cause problems maintaining data integrity.
For example, I have two rows in table 1 with the same value in the referenced column. I also have two rows in table 2 with the same value in the referencing column. I then change one of the rows in table 1; which rows in table 2 should also be updated?
If the referenced column is unique, but is not the primary key, then your table may be in an "abnormal form" and the additional unique column may be redundant. The test for this is to ask yourself two questions:

If I make the unique column(s) the new primary key, and drop the old primary key column(s), do I lose any data about the entity? If not, your table is in an abnormal form.
Can the unique column(s) change during the lifespan of the entity? If not, then the column(s) constitute a key candidate key.

In practice, there are sometimes very good reasons for denormalizing tables, but these must be considered carefully on a case-by-case basis.
Note that it is possible to reference a unique non-primary-key column or column group. However, since conceptually the primary key is "The" canonical identifier for the row, it makes more sense to reference the primary key.


Check constraint references another column in same table I have this table but check constraint is throwing me an error:

  Column CHECK constraint for column 'MedicamentRegulated' references another column, table 'Medicaments'.

I've seen this as allowed in SQL Server but I don't know why it doesn't work :/

CREATE TABLE Medicaments (
MedicamentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(50),
DateValidity DATE,
MedicamentRegulated BIT CHECK((MedicamentRegulated = 1 AND DateValidity IS NOT NULL)
  OR MedicamentRegulated = 0)
)

The syntax you are using is for a column-level constraint, so it can only apply to the column it is attached to... try naming your constraints at the table level instead (this also allows encourages you to give your constraints meaningful names instead of the terrible system defaults):

CREATE TABLE dbo.Medicaments -- always use schema*
(
  MedicamentID INT IDENTITY(1,1) NOT NULL,
  Name VARCHAR(50),
  DateValidity DATE,
  MedicamentRegulated BIT,
  CONSTRAINT pk_Med PRIMARY KEY (MedicamentID),
  CONSTRAINT ck_Med_reg CHECK
  (
    (MedicamentRegulated = 1 AND DateValidity IS NOT NULL)
    OR MedicamentRegulated = 0
  )
);

*see this post.


How to change the firing order of Triggers? Realy I rarely use triggers. So I met a problem at first time. I have a lot of tables with triggers (2 or more for every table). I would like to know and change the order of firing triggers for every table. Is it possible to get this information?

ADDED:

Here is a good enoght article on mssqltips I have found.

You can use the following statement to list all triggers in each table.

EXEC sp_MSForEachTable 'PRINT ''?''
EXEC sp_helptrigger ''?'''

Once you find out all the triggers. You can manually change the order using  sp_settriggerorder


Generate nested array with OPENJSON very slow I've just started using OPENJSON with SQL Server 2016 SP1.

I have this sentence:

select c.Serial as Parent,
    (Select co.Serial, agc.Position
      from AggregationChildren agc, Aggregation ag, Code co
      where agc.AggregationId = a.AggregationId
      and co.CodeId = agc.AggregationChildrenId for json path) as children
    from Aggregation a, Code c
    where c.CodeId = a.AggregationId for json path

To generate this JSON:

{"Parent":"4244287599479491","Children":
[{"Serial":"8915753733724633","Position":"1"},
{"Serial":"1247782815710855","Position":"2"},
...]}

But it is very very slow.

My problem is with the Children array because I don't know how to get it.

Is there a way to do it faster?

These are the tables:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [ ... ],
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    [ ... ]
)

CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    [ ... ],
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)

CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

The Serial column is an nvarchar(20) since the values may be any combination of alphanumerics, even though my example only shows numerics.

I had a hard time parsing your query, however I believe this returns the same result, and is much faster:

SELECT Parent = c.Serial
    , Children = (
        SELECT c.Serial
            , cac.Position
        FROM dbo.Code cc
            INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
        WHERE cac.AggregationId = a.AggregationId
        FOR JSON PATH
    )
FROM dbo.Code c
    INNER JOIN dbo.Aggregation a ON c.CodeId = a.AggregationId
FOR JSON PATH;

The plan for the above query looks like:

The plan for your query looks like:

If we add the following index, we can make the first variant even faster:

CREATE NONCLUSTERED INDEX IX_AggregationChildren_IX0
ON dbo.AggregationChildren (AggregationId)
INCLUDE (AggregationChildrenId,Position);

Clearly, you'd need to evaluate this against your workload, however.

I've created a minimally viable complete example setup to use for testing:

USE tempdb;

IF OBJECT_ID(N'dbo.AggregationChildren', N'U') IS NOT NULL
DROP TABLE dbo.AggregationChildren;
IF OBJECT_ID(N'dbo.Aggregation', N'U') IS NOT NULL
DROP TABLE dbo.Aggregation;
IF OBJECT_ID(N'dbo.Code', N'U') IS NOT NULL
DROP TABLE dbo.Code;
GO

CREATE TABLE dbo.Code (
    CodeId int NOT NULL
        CONSTRAINT PK_CODE
        PRIMARY KEY
        CLUSTERED
    , Serial nvarchar(20) NOT NULL
);

CREATE TABLE dbo.Aggregation (
    AggregationId int NOT NULL
        CONSTRAINT PK_AGGREGATIONS
        PRIMARY KEY
        CLUSTERED
        CONSTRAINT FK_Aggregation_Code
        FOREIGN KEY (AggregationId)
        REFERENCES dbo.Code (CodeId)
)

CREATE TABLE dbo.AggregationChildren (
    AggregationChildrenId int NOT NULL
        CONSTRAINT PK_AGGREGATION_CHILDS
        PRIMARY KEY
        CLUSTERED
        CONSTRAINT FK_AggregationChildren_Code
        FOREIGN KEY (AggregationChildrenId)
        REFERENCES dbo.Code (CodeId)
    , AggregationId int NOT NULL
        CONSTRAINT FK_AggregationChildren_Aggregation
        FOREIGN KEY (AggregationId)
        REFERENCES dbo.Aggregation (AggregationId)
        ON DELETE CASCADE
    , Position int NOT NULL
)

I re-formatted the constraint clauses to more kind to my brain; essentially, the above code is the same as the DDL in your question.

This populates the three tables with enough data to make meaningful comparisons:

;WITH src AS
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n(Val)
)
INSERT INTO dbo.Code (CodeId, Serial)
SELECT s1.Val
        + (s2.Val * 10)
        + (s3.Val * 100)
        + (s4.Val * 1000)
        + (s5.Val * 10000)
    , CONVERT(bigint, CRYPT_GEN_RANDOM(8))
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
    CROSS JOIN src s5

;WITH src AS
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n(Val)
)
INSERT INTO dbo.Aggregation (AggregationId)
SELECT s1.Val
    + (s2.Val * 10)
    + (s3.Val * 100)
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3;

;WITH src AS
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n(Val)
)
INSERT INTO dbo.AggregationChildren (AggregationChildrenId, AggregationId, Position)
SELECT s1.Val
        + (s2.Val * 10)
        + (s3.Val * 100)
        + (s4.Val * 1000)
        + (s5.Val * 10000)
    , s1.Val
        + (s2.Val * 10)
        + (s3.Val * 100)
    , s1.Val
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
    CROSS JOIN src s5;

These are the row counts for each table:

╔════════╦═════════════╦═════════════════════╗
║  Code  ║ Aggregation ║ AggregationChildren ║
╠════════╬═════════════╬═════════════════════╣
║ 100000 ║        1000 ║              100000 ║
╚════════╩═════════════╩═════════════════════╝

My version of the query:

SELECT Parent = c.Serial
    , Children = (
        SELECT c.Serial
            , cac.Position
        FROM dbo.Code cc
            INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
        WHERE cac.AggregationId = a.AggregationId
        FOR JSON PATH
    )
FROM dbo.Code c
    INNER JOIN dbo.Aggregation a ON c.CodeId = a.AggregationId
FOR JSON PATH;

To compare the output of both queries, I've created two user-defined-functions, as in:

CREATE FUNCTION dbo.fn_json_test_1()
RETURNS nvarchar(max)
AS
BEGIN
    RETURN (
        SELECT Parent = c.Serial
            , Children = (
                SELECT c.Serial
                    , cac.Position
                FROM dbo.Code cc
                    INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
                WHERE cac.AggregationId = a.AggregationId
                FOR JSON PATH
            )
        FROM dbo.Code c
            INNER JOIN dbo.Aggregation a ON c.CodeId = a.AggregationId
        FOR JSON PATH
    );
END;
GO

GO
CREATE FUNCTION dbo.fn_json_test_2()
RETURNS nvarchar(max)
AS
BEGIN
    RETURN (
        SELECT c.Serial as Parent,
            (Select co.Serial, agc.Position
              from AggregationChildren agc, Aggregation ag, Code co
              where agc.AggregationId = a.AggregationId
              and co.CodeId = agc.AggregationChildrenId for json path) as children
        from Aggregation a, Code c
        where c.CodeId = a.AggregationId for json path
    );
END;
GO

Now, I can compare the output for both queries via:

DECLARE @res1 nvarchar(max) = dbo.fn_json_test_1();
DECLARE @res2 nvarchar(max) = dbo.fn_json_test_2();

SELECT CASE WHEN @res1 <> @res2 THEN 'mismatch' ELSE 'match' END;

The result is:

The results do NOT match.  The output of my query contains less child nodes than your query.  I'm going back to the drawing board, and will simplify the test bed to see where the disparity lies.

The simplified testbed consists of 10 rows in the Code table, 2 rows in the Aggregation (Parent) table, and 8 rows in the AggregationChildren (child) table:

;WITH src AS
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n(Val)
)
INSERT INTO dbo.Code (CodeId, Serial)
SELECT s1.Val
    , CONVERT(bigint, CRYPT_GEN_RANDOM(8))
FROM src s1

;WITH src AS
(
    SELECT n.Val
    FROM (VALUES (0), (1)) n(Val)
)
INSERT INTO dbo.Aggregation (AggregationId)
SELECT s1.Val
FROM src s1;

;WITH src AS
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7)) n(Val)
)
INSERT INTO dbo.AggregationChildren (AggregationChildrenId, AggregationId, Position)
SELECT s1.Val + 2
    , s1.Val % 2
    , s1.Val
FROM src s1;

Row counts:

SELECT Code = (SELECT COUNT(1) FROM dbo.Code)
    , Aggregation = (SELECT COUNT(1) FROM dbo.Aggregation)
    , AggregationChildren = (SELECT COUNT(1) FROM dbo.AggregationChildren)

╔══════╦═════════════╦═════════════════════╗
║ Code ║ Aggregation ║ AggregationChildren ║
╠══════╬═════════════╬═════════════════════╣
║   10 ║           2 ║                   8 ║
╚══════╩═════════════╩═════════════════════╝

The predicted pattern should be two parent json arrays, each with 4 child arrays.

My Results:

[
  {
    "Parent": "-5601362097731340301",
    "Children": [
      {
        "Serial": "-5601362097731340301",
        "Position": 0
      },
      {
        "Serial": "-5601362097731340301",
        "Position": 2
      },
      {
        "Serial": "-5601362097731340301",
        "Position": 4
      },
      {
        "Serial": "-5601362097731340301",
        "Position": 6
      }
    ]
  },
  {
    "Parent": "-8896860091721838065",
    "Children": [
      {
        "Serial": "-8896860091721838065",
        "Position": 1
      },
      {
        "Serial": "-8896860091721838065",
        "Position": 3
      },
      {
        "Serial": "-8896860091721838065",
        "Position": 5
      },
      {
        "Serial": "-8896860091721838065",
        "Position": 7
      }
    ]
  }

Your query:

[
  {
    "Parent": "-5601362097731340301",
    "Children": [
      {
        "Serial": "5802227619253639548",
        "Position": 0
      },
      {
        "Serial": "5802227619253639548",
        "Position": 0
      },
      {
        "Serial": "4504664379821512162",
        "Position": 2
      },
      {
        "Serial": "4504664379821512162",
        "Position": 2
      },
      {
        "Serial": "6561435639659176802",
        "Position": 4
      },
      {
        "Serial": "6561435639659176802",
        "Position": 4
      },
      {
        "Serial": "-7417083263182709739",
        "Position": 6
      },
      {
        "Serial": "-7417083263182709739",
        "Position": 6
      }
    ]
  },
  {
    "Parent": "-8896860091721838065",
    "Children": [
      {
        "Serial": "-7646118996434234523",
        "Position": 1
      },
      {
        "Serial": "-7646118996434234523",
        "Position": 1
      },
      {
        "Serial": "-6372739442099935942",
        "Position": 3
      },
      {
        "Serial": "-6372739442099935942",
        "Position": 3
      },
      {
        "Serial": "-882384147532911428",
        "Position": 5
      },
      {
        "Serial": "-882384147532911428",
        "Position": 5
      },
      {
        "Serial": "4293317573306886053",
        "Position": 7
      },
      {
        "Serial": "4293317573306886053",
        "Position": 7
      }
    ]
  }
]

Your query has too many children; my query returns the predicted number of children, and it returns the correct Position values, however is returning the incorrect Serial values.

The "bug" in my query appears in the inner query.  The incorrect query is:

SELECT c.Serial
    , cac.Position
FROM dbo.Code cc
    INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
WHERE cac.AggregationId = a.AggregationId
ORDER BY c.Serial
FOR JSON PATH

The correct version is:

SELECT cc.Serial --changed "c." to "cc."
    , cac.Position
FROM dbo.Code cc
    INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
WHERE cac.AggregationId = a.AggregationId
ORDER BY cc.CodeId --not a big deal, but different order for children in output
FOR JSON PATH

The corrected query now looks like:

SELECT  Parent = c.Serial
    , Children = (
        SELECT cc.Serial
            , cac.Position
        FROM dbo.Code cc
            INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
        WHERE cac.AggregationId = a.AggregationId
        ORDER BY cc.CodeId
        FOR JSON PATH
    )
FROM dbo.Code c
    INNER JOIN dbo.Aggregation a ON c.CodeId = a.AggregationId
ORDER BY c.Serial
FOR JSON PATH;

And returns the following result:

[
  {
    "Parent": "-195930341251513493",
    "Children": [
      {
        "Serial": "-6126601633786720400",
        "Position": 1
      },
      {
        "Serial": "5216562173012877678",
        "Position": 3
      },
      {
        "Serial": "-1992909345438478098",
        "Position": 5
      },
      {
        "Serial": "8329388691987940194",
        "Position": 7
      }
    ]
  },
  {
    "Parent": "8774608126018975726",
    "Children": [
      {
        "Serial": "-3380643917643646211",
        "Position": 0
      },
      {
        "Serial": "-2042609074595538493",
        "Position": 2
      },
      {
        "Serial": "7345460002653774160",
        "Position": 4
      },
      {
        "Serial": "-2126530822210070443",
        "Position": 6
      }
    ]
  }
]


ERROR: row is too big: size XXX, maximum size 8160 - related to pg_policies table When trying to CREATE POLICY there is the following error:
ERROR: row is too big: size XXX, maximum size 8160
Is there a size limitation on the pg_policies table?
Can that be enlarged or maybe it was bad practice to write so many conditions with joins?
For now, I solved it by changing the query to fewer characters but how can that be avoided?
Example for an almost too long policy.
        CREATE POLICY check_out_insert ON check_out FOR INSERT
            WITH CHECK (
                (user_id = current_setting('app.current_user_id')::bigint AND
                file_version_id = ANY(ARRAY(
                    SELECT file_version.id fv_id FROM file_version
                        INNER JOIN "file" ON (file_version.file_id = "file".id)
                        INNER JOIN "file_user" ON ("file".id = "file_user".file_id AND file_user.deleted_at IS NULL)
                        WHERE file_user.user_id = current_setting('app.current_user_id')::bigint)
                )
                OR
                true = ANY(
                    ARRAY(SELECT is_owner
                    FROM "group"
                    WHERE id = current_setting('app.current_user_group_id')::bigint
                    LIMIT 1
                    )
                )
                OR
                file_version_id = ANY(
                    ARRAY(SELECT DISTINCT file_version.id fv_id FROM file_version
                        INNER JOIN "file" ON (file_version.file_id = "file".id)
                        INNER JOIN "sub_folder" ON (file.sub_folder_id = "sub_folder".id)
                        INNER JOIN "folder" ON (sub_folder.folder_id = "folder".id)
                        INNER JOIN "folder_group" ON (folder_group.folder_id = "folder".id AND folder_group.group_id = current_setting('app.current_user_group_id')::bigint)
                        INNER JOIN "project_user" ON (folder.project_id = "project_user".project_id AND "project_user".user_id = current_setting('app.current_user_id')::bigint)
                        WHERE folder.type = 'nda'
                    )
                )
            )
        );`);

You are probably running a PostgreSQL version older than v12, because pg_policy has a TOAST table since v12, so that oversized attributes can be stored out of line.
Before that, you were limited to policies where the pg_policy row fits into a single table block. Your policy text is less than that, but PostgreSQL stored the parsed query tree, which can be larger than the text.
Upgrade to a later PostgreSQL release or use several PERMISSIVE policies, which will be connected with OR.


Where can I find a list of CheckIDs for sp_Blitz? We have a home-grown system of monitors and processes that generate events which are then gathered into a central instance and are sent as daily summary reports to operational DBAs via SSRS.  It reports on anything from non-standard configuration, missing backups, disabled jobs that shouldn't be, databases in bad states, mirroring and AG issues, audit related issues (SOX), etc.

One of the things I am currently doing is writing a process that runs sp_Blitz on a weekly basis and generates events based on some of the findings.

Brent Ozar mentions that an Excel file with all of the possible CheckIDs is contained in the First Responder Kit download, and I can't find it in there.  There is also a link on the sp_Blitz page that is supposed to link to it, but it points to a non-existent page on GitHub (I get a 404).

Does anyone know where I can find this document that contains every possible CheckID and its description?

Sure, it's in Markdown here: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/master/Documentation/sp_Blitz_Checks_by_Priority.md

If you find any inconsistencies with it, check out the Contributor Guide for how to open issues in the GitHub repo, discuss the changes you want to make, and check in changes.


Cannot Restore Database in SQL Server 2014 I have created a backup of a SQL database in SQL 2014 and want to use this backup to restore it to a different machine that also runs SQL 2014, hence no version issues. However, I keep receiving an error that the system cannot find the .mdf file.
Once I have selected the backup file I receive the  check mark that the database is ready to be restored but as soon as I click Ok the error appears.
Error message below; the provided link to MSFT does not contain any information.

  TITLE: Microsoft SQL Server Management Studio Restore of database
  'BEWEBAPP' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks) ADDITIONAL
  INFORMATION: System.Data.SqlClient.SqlError: Directory lookup for the
  file "C:\Program Files\Microsoft SQL
  Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\BEWEBAPP.mdf" failed with the
  operating system error 3(The system cannot find the path specified.).
  (Microsoft.SqlServer.SmoExtended) For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&LinkId=20476

Does this means that a backup can only be restored to the machine it was taken from??
Frankly, I do not understand why restoring a database has to be so difficult.

Any solution for this problem are well appreciated.

Sql server stores file locations with the restores with the general idea you'd be restoring on a similarly setup machine. But you can override this -- look at the restore UI for the database files and switch those to appropriate locations based on your setup.


What is a "leaf expression"? What is a "leaf expression"?

Partition-clause expressions are limited in the following ways:
....

You can specify leaf expressions, functions, and operators in the partition clause expression.
All leaf expressions in the partition clause must be either constants or columns of the table.

Source https://my.vertica.com/docs/6.1.x/HTML/index.htm#18125.htm

I'm pretty sure leaf expression means something like "a?b:c". A discrete value from a conditional.

But basically the overall message is saying you need to avoid null values. They can't partition on a null.


How to compare (in MySQL) a DATETIME value to a TIMESTAMP value? I'm trying to understand how to compare a DATETIME value (ostensibly inserted as GMT) and a TIMESTAMP value (automatically generated at INSERT time).

As I understand it, the DATETIME is set to whatever it is set to - and the reporting comes back with the same value, no matter what time zones are in place on the server or client.

In the MySQL documentation, it sounds like TIMESTAMP values are reported in the current timezone, but stored in UTC.

In this situation then, subtracting the DATETIME value (earlier event) from the TIMESTAMP value should be possible without a lot of timezone conversion - because the DATETIME is GMT and the TIMESTAMP value is UTC. However, doing this accurately - and understanding it - is proving to be a problem.

I have situations where I need (DATETIME - TIMESTAMP) in minutes and in days. If we assume that column d is DATETIME and column t is TIMESTAMP... So far, I've tried:

DATEDIFF(d,t)
TIMEDIFF(d,t)
UNIX_TIMESTAMP(d) - UNIX_TIMESTAMP(t) - 1800 (accounting for DST)

Our time zone here is US Central Time - currently Central Daylight Time - GMT-6 for CST and GMT-5 for current CDT.

It also looks like DATEDIFF converts both values into dates then compares them - instead of comparing time between the values and dividing by the number of 24 hours in each. Thus, I don't think DATEDIFF will work directly for me anyway.

The specific questions, then are:

How do you compare a DATETIME in GMT with a TIMESTAMP value? Specifically, finding the number of minutes between the two, assuming the TIMESTAMP value was generated last.
What's the best way to get the difference in 24 hour slots between the same two values? I assume that it would be - using A1 as the answer to the above question - TRUNCATE(A1 / (24.0 * 60.0)) ...

UPDATE: Tried using the CONVERT_TZ function with the values of US/Central and GMT. However, the CONVERT_TZ function with such specifications appears to read the setting of Daylight Saving Time from the current system time and not from the time as read from the database field. This means that when the data should be taken as a date in CST, it is read as CDT instead.

I keep thinking it's got to be simple to get the UTC representation of a TIMESTAMP field - which would simplify things, seems to me.

UPDATE 2: Looks to me like I mispoke regarding CONVERT_TZ. I'm using this function and it seems to work:

TIMEDIFF(CONVERT_TZ(t),'US/Central','UTC'),d)

Even over a time zone change (from CST to CDT) it seems to work...

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.


3 Simple Queries SQL Student Question, Query Errors in Microsoft Access The First Question is: "List the name of each trip that does not start in New Hampshire (NH)."
SELECT TripName
FROM Trip
WHERE State!='NH'; /* works with State='NH' */

The Second Question is: "List the trip name, type, and maximum group size for all trips that have Susan Kiley as a guide."
SELECT TripName, MaxGrpSize
FROM Trip, TripGuides
WHERE Type=Hiking, GuideNum = 'BR01';
/* also i tried WHERE Type=Hiking AND GuideNum = 'BR01'; */

I Cant Filter by only type: "Hiking"
The Third Question is: List the reservation ID, customer number, customer last name, and customer first name for all trips that occur in July 2018.
I Tried This:|
SELECT DISTINCT  LastName, FirstName, CustomerNum, ReservationID
FROM Customer, Reservation
WHERE TripDate = '2018-12'; /* i tried CustomerNum.Customer */

If you need more tables and more information let me know.

the first is simple access  has no !=
SELECT TripName AS Ausdr1
FROM Trip
WHERE ([State])<>'NH';

the form of the secnd is wrong and you should start to use proper JOINs a comma for tables means always a cROSS JoiN, which can be useful, but nit in this case
also strings must always be in quotes
SELECT TripName , MaxGrpSize
FROM Trip, TripGuides
WHERE ((([Type])='Hiking') AND (([GuideNum])='BR01'));

the last  us simple bith tables have the column ´CustomerNum´, so you have to tell access which one you wantg, but i guess it is a joining column.
SELECT DISTINCT  LastName, FirstName, [Customer].CustomerNum, ReservationID
FROM Customer, Reservation
WHERE TripDate = '2018-12';

what i mean by proper joining:
SELECT DISTINCT  LastName, FirstName, c.CustomerNum, ReservationID
FROM Customer C INNER JOIN Reservation r ON c.CustomerNum = r.CustomerNum
WHERE TripDate = '2018-12';

I don#t know ou complete tables , so i am only guessing


What is the difference between SQL Server 2012 SP4 vs SP3 with CU-10? Sorry, may be very stupid question but what is the difference between SQL Server 2012 SP4 and SQL Server 2012 SP3 + CU-10?

2012 SP4 contains all the same fixes as 2012 SP3 CU #10, and was basically just a rollup to release a final service pack. The only differences are going to be:

build number will be higher
longer end of life
potentially better chance of servicing for a security issue in the future
easier slipstreaming

Personally I would go with 2012 SP4.


Always Encrypted - certificate cannot be used as a column master key I want to create a new column master key using certificate created in advanced. I use the following command to create a certificate in the Windows Certificate Store - Local Machine.

New-SelfSignedCertificate -CertStoreLocation cert:\LocalMachine\My -DnsName TEST_CERT_01

The issue is that when I try to select this certificate, I get the following error:

Can anyone tell what's wrong with the certificate?

Please refer to Create a self-signed certificate using PowerShell section of this article.

To be used as Always encrypted CMKs, certificates require a specific configuration.

You should be able to create a certificate to be used as CMK using the following commands

New-SelfSignedCertificate is a Windows PowerShell cmdlet that creates a self-signed certificate. The below examples show how to generate a certificate that can be used as a column master key for Always Encrypted.
$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:CurrentUserMy -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048

# To create a certificate in the local machine certificate store location you need to run the cmdlet as an administrator.
$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:LocalMachineMy -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048

This should work with Server 2012, you will have to install makecert utility if it does not exist:

makecert.exe -n "CN=Always Encrypted Certificate - exported" -pe -sr CurrentUser -r -eku 1.3.6.1.5.5.8.2.2,1.3.6.1.4.1.311.10.3.11 -ss my -sky exchange -sp "Microsoft Strong Cryptographic Provider" -sy 1 -len 2048 -a sha256


Getting error running DBCC Checktable(tablename, repair_allow_data_loss) I ran DBCC CheckDB and got some consistency errors in the database.  In an attempt to recover all the data I have exported out all the data from the database to a new database except for one table.  When I try to copy all the data from that table to the new database I get an error that says

  "A severe error occurred on the current command.  The results, if any,
  should be discarded."

So I ran DBCC CheckTable(tablename) on the table to try to ensure that this was the table that was causing the issue.  It gave the same consistency errors that were coming from the CheckDB.

Once I ran CheckTable it told me the minimum I could do is run with repair_allow_data_loss.  I set the database into SINGLE_USER mode and then I ran the DBCC CheckTable(tablename, repair_allow_data_loss) command and now I get this error

  Repair statement not processed.  One or more files in the database are
  read-only and must be made writeable in order to run repair.

I checked to make sure the files and the file groups are all writeable and they are, so I'm not sure what is causing the issue.

I've tried taking a backup and restoring that to a different server, I've tried making a new copy of the database, I've tried making the files read-only and then making them writeable again.  None of those things worked and all produced the same error about the files being readonly.

Anybody have any thoughts on what else I might try?

For reference, I am running SQL Server 2014 SP2.

EDIT: Here is there output using WITH NO_INFOMSGS, ALL_ERRORMSGS

Msg 8952, Level 16, State 1, Line 1
Table error: table 'sys.sysschobjs' (ID 34). Index row in index 'nc1' (ID 2) does not match any data row. Possible extra or invalid keys for:
Msg 8956, Level 16, State 1, Line 1
Index row (1:18806:22) with values (nsclass = 0 and nsid = 1 and name = 'tmp324504_734167628_1' and id = 385811436) pointing to the data row identified by (id = 385811436).
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysschobjs' (object ID 34).
Msg 8944, Level 16, State 12, Line 1
Table error: Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 71906736119218176 (type LOB data), page (1:28439), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 32512 and 8037.
Msg 8944, Level 16, State 12, Line 1
Table error: Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 71906736119218176 (type LOB data), page (1:28439), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 32512 and 8037.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 71906736119218176 (type LOB data). The off-row data node at page (1:28439), slot 0, text ID 69999198208 is referenced by page (1:27785), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 71906736119218176 (type LOB data): Page (1:28439) could not be processed.  See other errors for details.
Msg 8929, Level 16, State 1, Line 1
Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 72057693155753984 (type In-row data): Errors found in off-row data with ID 69999198208 owned by data record identified by RID = (1:869011:3)
CHECKDB found 0 allocation errors and 6 consistency errors in database 'DataCorruption'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DataCorruption).

From the comments I see that you have it narrowed down to a single corrupt row. You may be able to get the underlying data (or most of the data at least) in that row via a hex editor. Paul Randal has good information on how to crack open MDF files this way and here is an article by Kendra Little as well.

https://littlekendra.com/2011/01/24/corrupthexeditor/

The article is on how to corrupt a database with a hex editor but you've already got that so look at the steps right below "It’s the Moment We’ve Been Waiting For: Trash That Page".

Extracting information in this way can't be healthy for the database, so ensure you are using a backup!

Depending on the data, you can usually see enough to piece together the corrupt values.


Many-to-many relationship with attributes I'm working on a project where I need a book database. The problem is as follows:

A book can be related to multiple people (authors, editors, traslators, etc.). Then a person may contribute to different books in different roles.

Book1 PersonA (author), PersonB(editor)
Book2 PersonC (author), PersonB(translator), PersonD(editor)
Book3 PersonE (author), PersonA(editor)

I have a BOOKS table (bookID, bookTitle), a PEOPLE table (personID, personName) and a ROLES table (roleID, roleName). Then I need a way to relate these tables and then query them. I thought of a junction table that has the following row structure:

bookId personID roleID

Is this the optimal way of handling this problem (when it comes to insertion, update and deletion of entries on all these tables)? If not, what are the alternatives?

Note: I'm using SQLite3.

PS: I hope my use of the terminology is correct.

I would use The Party Model, so that you can associate a book with an Individual or Organization, not just an Individual. For example, you might want Random House in the role of Publisher. Or Mötley Crüe as the author of the book, not just Tommy Lee.
Otherwise, looks good. You might want to consider associating the book and the PartyRole, instead of Party and Role separately, as you might have extra info pertaining to a Party in a particular Role. Perhaps author pseudonyms.

Postgres-y SQL, but close enough to SQLite:

create table parties (
  party_id int primary key,
  party_type smallint not null references party_types(party_type_id), --1=individual,2=org
  name text not null
);

create table party_roles (
  party_role_id int primary key,
  party_id int not null references parties(party_id),
  role_id smallint not null references roles(role_id),
  ...
  extra information about a party pertaining to a role
  ...
);

create table book_roles (
  book_id int references books(book_id),
  party_role_id int references party_roles(party_role_id),

  primary key (book_id, party_role_id)
);


PostgreSQL: Delete Multiple Rows with multiple Conditions from the same table I have a dilemma that makes for sort of curious experiment to be conducted but I was wondering if it already has been done.
I have a table:
create table test(action_date date, item_num int, <various irrelevant columns>,
                  primary key(action_date, item_num));

I have data that is coming in that contains the action_date, item_num and the rest of information for the <various irrelevant columns> I need to update data in the table quickly.
The fastest way I found was to:

delete the data first
insert the new data

The issue I am having is how to delete data in the fastest way possible:
The new data contains 1000s of items and 100s of dates, so I can do one of 2 things:

I can do a single query: delete from test where (item_num = item_num1 and action_date >= action_date1) or (item_num = item_num2 and action_date >= action_date2) or ...

Execute as a single transaction a set of queries of the delete from test where item_num = item_num1 and action_date >= action_date1; delete from test where item_num=item_num2 and action_date >= action_date2; ....

The question is which way would be faster?
P.S.  I could conduct experiments and see which way would be faster but I was wondering if this was already done somewhere.  My own searches yielded nothing.

I don't expect a big difference between both queries. Details depend on undisclosed information and data distribution.
What actually makes a big difference is to have an index supporting it optimally. One might think that you already have that with your PK:
primary key(action_date, item_num)

But that's not so. The sequence of index expressions matters in this case. Instead make it:
primary key(item_num, action_date)

Rule of thumb: equality first, range later. See:

Multicolumn index and performance

You may want to create an index on (action_date) or even (action_date, item_num) additionally - or vice versa - for those other use cases you mentioned in a comment. But you must have the index on (item_num, action_date) one way or another for optimal performance. See:

Is a composite index also good for queries on the first field?

As long as those other queries have equality predicates on both item_num and action_date, either index is equally good, and you don't need another. See (again):

Multicolumn index and performance

Try to keep the number and size of your indexes to a minimum, as they themselves are a burden on write performance. Rule of thumb: as many as necessary, as few as possible.
Whether you DELETE and INSERT, or UPDATE (UPSERT), you produce a lot of dead tuples either way. So make sure to have appropriate autovacuum settings for the table. Default settings may not be aggressive enough if the table is big and the churn is high. The table needs to be vacuumed to be able to reuse space from dead tuples or do HOT updates. Else, the table, and even more so the index, start to bloat and performance goes down. See:

Aggressive Autovacuum on PostgreSQL
Redundant data in update statements

In extreme cases vertical partitioning may pay. See:

Many columns vs few tables - performance wise


How can you split multiple delimited strings of varying sizes into rows? I have a situation where for each ID in my table there are multiple fields that each contain a comma-delimited string. The strings don't necessarily have the same number of elements as one another. The format of the data comes from another team and I can't change it.
How can I split these strings into rows with SQL Server 2016? I'd rather not add a function to the database if it can be avoided.
Here's a sample of what I'm starting with and what I'm looking to achieve:
DECLARE @SourceTable TABLE (ID INT, String1 VARCHAR(100),
  String2 VARCHAR(100), String3 VARCHAR(100))

INSERT INTO @SourceTable (ID, String1, String2, String3)
VALUES (1, 'a1,a2,a3', 'b1,b2,b3,b4,b5,b6', 'c1,c2'),
       (2, 'd1,d2', 'e1', 'f1,f2')

--Source
SELECT *
FROM @SourceTable

Desired Output:
ID  Position    S1_Transformed  S2_Transformed  S3_Transformed
--  --------    --------------  --------------  --------------
1   1           a1              b1              c1
1   2           a2              b2              c2
1   3           a3              b3
1   4                           b4
1   5                           b5
1   6                           b6
2   1           d1              e1              f1
2   2           d2                              f2

EDIT: Corrected output based on Aaron's comment.

If you can bear to have a function to supply functionality not (yet) built into the engine, you can create this function which returns an ordinal position alongside each value (there are dozens of different ways to write this splitting function):
CREATE FUNCTION dbo.SplitCSVInOrder
(
    @List  nvarchar(max)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN
    (
      WITH L0(c) AS (SELECT 1 UNION ALL SELECT 1),
           L1(c) AS (SELECT 1 FROM L0, L0 AS B),
           L2(c) AS (SELECT 1 FROM L1, L1 AS B),
           L3(c) AS (SELECT 1 FROM L2, L2 AS B),
           L4(c) AS (SELECT 1 FROM L3, L3 AS B),
           L5(c) AS (SELECT 1 FROM L4, L4 AS B),
           N (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY c) FROM L5)
        SELECT
           [Position] = ROW_NUMBER() OVER (ORDER BY n),
           [Value] = LTRIM(RTRIM(SUBSTRING(@List, n,
                CHARINDEX(',', @List + ',', n) - n)))
        FROM N WHERE n <= LEN(@List)
            AND SUBSTRING(',' + @List, n, 1) = ','
    );

Then you can unpivot / re-pivot to line up the values from each position:
DECLARE @SourceTable TABLE
(
  ID INT,
  String1 varchar(100),
  String2 varchar(100),
  String3 varchar(100)
);

INSERT INTO @SourceTable (ID, String1, String2, String3)
VALUES (1, 'a1,a2,a3', 'b1,b2,b3,b4,b5,b6', 'c1,c2'),
       (2, 'd1,d2', 'e1', 'f1,f2');

;WITH x AS
(
  SELECT * FROM @SourceTable
    UNPIVOT (s FOR strings IN (String1, String2, String3)) AS up
    CROSS APPLY dbo.SplitCSVInOrder(s)
),
p AS
(
  SELECT * FROM x PIVOT (MAX(Value)
    FOR strings IN ([String1],[String2],[String3])) AS p
)
SELECT ID, Position, S1_Transformed = COALESCE(MIN(String1),''),
                     S2_Transformed = COALESCE(MIN(String2),''),
                     S3_Transformed = COALESCE(MIN(String3),'')
FROM p
GROUP BY ID, Position
ORDER BY ID, Position;

Results:
ID  Position    S1_Transformed  S2_Transformed  S3_Transformed
--  --------    --------------  --------------  --------------
1   1           a1              b1              c1
1   2           a2              b2              c2
1   3           a3              b3
1   4                           b4
1   5                           b5
1   6                           b6
2   1           d1              e1              f1
2   2           d2                              f2

I am sure there is a simpler way to do it but that's what my raised-mostly-on-SQL-Server-2005 brain came up with.
Do you wish that query could be easier to formulate? Me too! For one, it would be great if there were a native string splitting function that also returned ordinal position. That would negate the need for a function to ensure the output comes out in the same order as it's listed in the string. I've asked for everyone's help in getting Microsoft to add a function in the next version of SQL Server. Please vote and add a constructive comment that explains your business case here:

Add row position column to STRING_SPLIT

And see some background on what I'm asking for specifically here:

A way to improve STRING_SPLIT


Just how secure is the data I put on SQL Azure? My wife has been asking me to create a web application for her business and from what I've read using SQL Azure would be perfect.  Except for a couple of small (really really big) gotchas.

She works in the financial industry and is HEAVILY regulated.
The data she works with is very sensitive and has to have a minimum risk of exposure.

So my question is, if I put her companies information on SQL Azure how secure will it be?  I'm not asking about general SQL Server/Azure security.  I'm talking about the fact that this is a shared space.  How possible is it to hack into the data, bypassing the security that I put into place?

Irrelevant. Seriously. It is irrelevant how secure it IS - if she is in a regulated industry, the provider has to provide the necessary guarantees, whether he fullfills them or not.

And you will find that Azure tells you nothing in the contracts. It is thus not good to use them, regardless how good their factual security is.

Example answer:

https://stackoverflow.com/questions/11167152/hosting-a-pci-compliant-app-on-azure

about azure PCI compliance - the asnwer is: not.

Check:

https://www.windowsazure.com/en-us/support/trust-center/compliance/

for more information. This is what you are guaranteed - the factual implementation is then not your problem.


Logical schema development and migration tool for different physical schema available including triggers and stored procedures? We are planning to support for our application another rdbms (oracle).

At the moment we are design our database schema with the MySQL-Workbench. For every new database version we have

schema er-diagram
a full schema sql script (create table.., create index..., create trigger..., ...)
and a migrate sql script (alter table...., drop index, create index..., ...)
and finally a data file which all in it (*.mwb file)

We use table, index, stored routines, views and triggers. We want a tool, where we can design the table, relations, views, triggers and routines and export a full schema sql script for different rdbms and an migration sql skipt to upgrade a database for different rdbms.

Now my question. Is the any tool available, which is able to do the above stuff for both MySQL and Oracle?

I look at AnySQL Maestro, Navicat Premion, Altova DatabaseSpy but all loose information (mostly the triggers). I found also this wikipage, but i can't figure out, which have the feature we need.

EDIT: clarification:

Atilla Ozgur (see his answer) is right. A want a tool whit a logical model including triggers and procedure and which is able to export all the things (especially triggers and routines) to different (at the moment MySQL and Oracle) physical models. The export should be done by sql scripts.

Last time I have used it (but that's a long time ago) Sybase PowerDesigner was able to handle triggers and procedures for all supported DBMS.

Looking at the current manuals, that still seems to be the case.

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc38058.1600/doc/html/rad1232020719541.html

Although I don't see MySQL mentioned there.


Backupset server name on dev server shows Prod server name I'm using the following code to determine the last time a database was backed up:

SELECT
@@ServerName as HostServerName
   ,bus.server_name AS BUSservername
   ,sdb.Name AS DatabaseName
   ,COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101), '-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus
ON bus.database_name = sdb.name
WHERE
   bus.backup_start_date >= DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0) -- Previous day at midnight.
   AND bus.backup_start_date < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) -- Today at midnight.
GROUP BY
   sdb.Name
   ,bus.server_name

And it appears to show the data I'm looking for, but if I run this code on a development server, for example, it shows its corresponding production server as the bus.server_name value.
I'm trying to understand why it's showing a production server' name and not itself.  I may not be understanding how the backupset table is getting its value.
I know I can use @@ServerName for that value but I'm not sure if that is correct way to get that value.
Below is a screenshot of my results with the server names obviously blacked out:

This is confirmed.

If you restore a database from one server to another, but never back it up, and run the code I have above, it will show the server name from the original server it was backed up from.

BUT if you then do a backup of that same database, it will then show the current server it was backed up from which is what I expected.


return null as \N from psql I'm trying to migrate some data from postgres to mysql. for that i'm doing a simple sql in postgres and load it to mysql. since format is a bit different the flag -AF $'\t' in psql makes it tab delimiter which is great.
The missing part is that psql return null as empty string. where in mysql it represent as \N.
Is there a flag that manage it? I would really like to avoid putting ifnull around each column in the select.

psql has a flag that controls the way null is outputted -P 'null=\N'


SQL Server Database Suspect with Memory table Today my database seems suspect. I am running SQL Server 2016 with memory tables. The below code won't work to fix the suspect. SQL Server keeps saying I have to restore. Any ideas?

EXEC sp_resetstatus dbname;
ALTER DATABASE dbnameSET EMERGENCY
 DBCC checkdb(dbname)
ALTER DATABASE dbnameSET SINGLE_USER WITH ROLLBACK IMMEDIATE

 DBCC CheckDB (dbname, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE dbnameSET MULTI_USER

Sadly, corruption checking doesn't work for In-Memory OLTP (Hekaton).

Your best bet is to restore from backup at this point, and then maybe upvote the request for Microsoft to get corruption checking to work. Maybe someday you'll be able to check for corruption on your important data before you're stuck getting surprised like this.


Change Storage Engine on Referenced Tables i need to change storage engine from 'InnoDB' to 'MyISAM' on some tables of different databases.
So i made a script file to do so:

#!/bin/bash
DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select distinct(table_schema) from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema")')
for D in $DATABASES
do
   TABLES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select table_name from information_schema.tables where table_schema="'$D'" and engine="InnoDB"')
   for T in $TABLES
   do
      echo "ALTERING TABLE $T"
      mysql -u XXXXX -pXXXXX -e "USE $D; ALTER TABLE $T ENGINE=MYISAM"
   done
done

It works on some tables, but on those with foreign keys, displays the following error message:

ERROR 1217 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails

Is there a way to change storage engine on those tables?

You need to disable foreign key checks and unique checks

#!/bin/bash
DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select distinct(table_schema) from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema")')
for D in $DATABASES
do
   TABLES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select table_name from information_schema.tables where table_schema="'$D'" and engine="InnoDB"')
   for T in $TABLES
   do
      echo "ALTERING TABLE $T"
      SQL="SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0;"
      SQL="${SQL} ALTER TABLE ${T} ENGINE=MYISAM"
      mysql -u XXXXX -pXXXXX -D${D} -e "${SQL}"
   done
done

Here is a more concise way: Reload data with InnoDB Disabled

#!/bin/bash
DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select distinct(table_schema) from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema")')
mysqldump -u XXXXX -pXXXXX --single-transaction --add-drop-database -B ${DATABASES} > AllMyData.sql
mysql -u XXXXX -pXXXXX -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysql restart --skip-innodb
mysql -u XXXXX -pXXXXX < AllMyData.sql


Where to put a database in case of IP failover? I understand that to achieve some level of availability of an online application it is possible to organize an environment with a redundancy using an IP failover mechanism and switching between several identical servers in the event a problem arises in any one of them.

But what about with databases? Should the database be on each server with some sort of replication data between them or should it sit on its own dedicated server?

What approach usually is used in SQL Server 2008 R2 or SQL Server 2008 R2 Express?

You'll want to put the SQL Server on its own server.  You may also need to protect the SQL Server from reboots or failed hardware by looking at an HA solution.  You can use Clustering (which requires shared storage), or database mirroring (with or without a witness), or peer to peer replication.


Is it possible to take a database offline when doing a backup using an SQL job? I have the scenario where I have to generate a backup of the database (SQL Server 2008) and restore into the new server (SQL Server 2016). While taking backup data should not be changed in any case. So I have two options to do that, but I am not sure how it will work. (Everything is using SQL Job only.)

Set a read-only database and restore into the new DB server.

→ Is it possible to restore a read-only DB on the new server? The destination server is already having a read-write (online) database by the same name.
Set an offline database and restore onto the new DB server.

→ Is it possible to restore an offline DB in the new server? The destination server already has an online (read-write) database by the same name.

→ Is it possible to restore read-only DB in the new server?

Yes

An example:

CREATE DATABASE ReadOnlyDB;
GO

ALTER DATABASE ReadOnlyDB SET READ_ONLY;

BACKUP DATABASE ReadOnlyDB TO  disk = '\\share\readonly.Bak';

  destination server is already having a Read-Write(Online) database by
  the same name.

Not entirely sure what you mean by this, but you could restore the database with a different name if you would like to, remember to remove the read only property afterwards.

RESTORE DATABASE ReadOnlyDB2
FROM DISK = '\\share\readonly.Bak'
WITH MOVE 'ReadOnlyDB' to '\Datalocation\ReadOnlyDB2.mdf',
 MOVE 'ReadOnlyDB_log' to '\Loglocation\ReadOnlyDB_log2.ldf',
STATS = 5;
GO
ALTER DATABASE ReadOnlyDB2 SET READ_WRITE;

You could also replace the existing database with the REPLACE keyword.

  → Is it possible to restore offline DB in the new server?

No, this is not possible as sql server removes all handles from the database files and will not be able to access it while it is offline.

ALTER DATABASE ReadOnlyDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
BACKUP DATABASE ReadOnlyDB TO  disk = 'C:\temp\readonlyOffline.Bak';

  Msg 942, Level 14, State 4, Line 10 Database 'ReadOnlyDB' cannot be
  opened because it is offline. Msg 3013, Level 16, State 1, Line 10
  BACKUP DATABASE is terminating abnormally.

You could look into detach / attach but backup restore is preferable.


Does indexed column with millions of different values make sense? I noticed that one of my queries was pretty slow, taking about two seconds to execute.

This is the slow query:

SELECT EVENT_DATETIME, EVENT_EVENTTYPE FROM EVENT WHERE (EVENT_ID = :1  AND EVENT_RESULT = :2  )

The index used was created like this:

CREATE INDEX "INDEX_EVENT_DATETIME" ON "EVENT" ("EVENT_DATETIME", "EVENT_ID")

The execution plan shows an index skip scan because obviously the EVENT_DATETIME column is not in the where clause.

So at first I thought I would create a second concatenated index on the table for the columns (EVENT_ID, EVENT_RESULT) but then I noticed that the column EVENT_DATETIME has 18,702,145 distinct values. Of a total of 18,722,706 rows.

There are 4 different queries happening regularly on this table. All of them except one have the EVENT_DATETIME column in the where clause but all of them have EVENT_ID in the where clause.

So my assumption is that the index as it is currently being used doesn't make the queries more efficient.

Is it correct that a concatenated index on the columns (EVENT_ID, EVENT_DATETIME) would be more useful?

For b-tree index , the more distinct values the better.

Index skip scan doesn't seem to be an efficient access method in your case because the first column in the index has high cardinality. It works opposite to "regular" index usage like index range scan or index unique scan  where you want the first column to be the most discriminating.  It's hard to say if in this particular case full table scan would behave better without trying (FULL or NO_INDEX hints can do the job) .

No doubt, for the query in  question index on event_id,event_date is much better than on event_date,event_id.

It's hard to tell without seeing your other queries, but I think you may not necessarily need to have composite indexes.  Each column, event_date and event_id, appear to be have very few duplicates (if any) .  Then having two indexes, one on EVENT_DATE and another on EVENT_ID will work better than having multiple concatenated indexes - even if a composite index is covering for some queries, the engine will have to do only one or two extra lookups .


PostgreSQL full text search on many columns I need an advice with searching of record based on specified string.

Search strings can contain values from these columns. Values in this string don´t have to be strictly identical given in the correct order and also the values of some columns in this string may be missing.

Example of search string:

22 Karntner Wien

And I get for example result with top 5 similar records.

I think I should use full text search, but I have no experiences with it. Can you tell me how to proceed?

I suggest this expression for query and index:
SELECT * FROM tbl
WHERE  to_tsvector('simple', f_concat_ws(' ', country, city, street, house_nr, postcode))
    @@ plainto_tsquery('simple', '22 Kärntner Wien');

Note the custom function f_concat_ws() above. That's because concat_ws() is only STABLE not IMMUTABLE. You need to create it first:
CREATE OR REPLACE FUNCTION f_concat_ws(text, VARIADIC text[])
  RETURNS text LANGUAGE sql IMMUTABLE AS 'SELECT array_to_string($2, $1)';

It can be used as drop-in replacement for concat_ws(), except that it only accepts actual text data as input (which allows us to make it IMMUTABLE without cheating, effectively). Detailed explanation (read it!):

Combine two columns and add into one new column

About VARIADIC:

Passing multiple values in single parameter

For many columns, this is shorter and faster. You could do without it but then the syntax gets rather verbose (see joanolo's answer).
The matching index to go with this:
CREATE INDEX tbl_adr_fts_idx ON tbl USING GIN (
       to_tsvector('simple', f_concat_ws(' ', country, city, street, house_nr, postcode)));

You are dealing with international address data, so do not use the english text search configuration. Stemming makes little sense for names  and most of your example data is not even English to begin with. Use the simple configuration instead. You need the form with two parameters - see below.
Concatenate the strings and call the more expensive function to_tsvector() once. Use concat_ws() to deal with possible NULL values elegantly. Cheaper overall, and also shorter.

String replace using concatenated strings from various columns

Like I commented, Full Text Search has limited support for fuzzy matching, but there is the often overlooked feature of prefix matching:

Get partial match from GIN indexed TSVECTOR column

So, if you are not sure whether it's 'Kärntner' or 'Kärnten', and whether it's 'Straße', 'strasse' or 'Strabe' (like in your buggy example data) but you know that the second word follows the first, you could:
... @@ to_tsquery('simple', '22 & Kärnt:* <-> Stra:* & Wien')
<-> is the phrase search operator and requires Postgres 9.6.

How to store short stories for access to individual sentences?

And if you want to ignore diacritical signs as well ('ä' <> 'a'), add unaccent() to the mix. You can use it as separate function or you can add it as dictionary to your text search configuration. You need to install the extension first ...

Does PostgreSQL support “accent insensitive” collations?

Overview over pattern matching option in typical Postgres installations:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Joanolo already provided some basic information about FTS and the link to the manual for more.
Adressing your comment

I am trying add this index it but gives me an error:
ERROR: functions in index expression must be marked IMMUTABLE

There are two variants of the function to_tsvector() - see "function overloading". The 1st takes only text, the 2nd takes regconfig and text. See for yourself:
SELECT proname, provolatile, proargtypes[0]::regtype, proargtypes[1]::regtype
FROM   pg_proc
WHERE  proname = 'to_tsvector';

Only the second is IMMUTABLE and can be used in an index expression directly. 'simple' in the above example is a text search configuration (regconfig).
More importantly, my oversight: concat_ws() (which I had in my first version) is only STABLE, not IMMUTABLE. I added necessary steps above.
Related:

How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?

Combine two columns and add into one new column


ORA-12899 when inserting from sqlplus, but ok with jdbc Inserting 'ééééé' into a column defined as varchar(5) runs fine from squirrel sql client (jdbc).
But doing the same insert from sqlplus fails with ORA-12899 (too long value).

NLS_CHARACTERSET is set to AL32UTF8
NLS_LENGTH_SEMANTICS is set to CHAR

It look like sqlplus is not honoring these settings...

Where should I look ?

I have the same character set:

SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

Result:

VALUE
-----------
AL32UTF8

I have NLS_LENGTH_SEMANTICS set to BYTE, but I can override this option in this way:

CREATE TABLE encoding (name VARCHAR2(5 CHAR));
INSERT INTO encoding VALUES('ééééé');

1 row created.

My NLS_LANG is: AMERICAN_AMERICA.UTF8, because my server is encoded UTF8.
But if I unset this environment variable:

unset NLS_LANG

and if i try again:

INSERT INTO encoding VALUES('ééééé');
ORA-12899; value too large for column "..."."..."."...." (actual: 10, maximum: 5)

so, yo can do a try and set your NLS_LANG variable, which is in the format

[NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]

And it would work. You can obtain this parameters from NLS_DATABASE_PARAMETERS.


How do I store more than 15,000 Japanese characters in a column? I want to store more than 15,000 characters of Japanese text in a SQL Server 2008 database. We have a front end tool that will display this text.

In this case only NVARCHAR(MAX) should be used. It's able to store up to 2^31-1 bytes of Unicode data (about 2 GB).