SQL query for combinations without repetition I need a query which can be used in (or as) a function and retrieves all combinations of n values. And I need all combinations of length k where k = 1..n.

Extended sample input and result so input has 3 values instead of 2 - however, number of input values may vary from 1 to n.

Example:
Input: table with values in one column in multiple rows

Value  (nvarchar(500))
------
Ann
John
Mark

Output#1: table with values concatenated in one column

    Ann
    John
    Mark
    Ann,John
    John,Mark
    Ann,Mark
    Ann,John,Mark

For relatively small values of n (20 in this example), you can use a method that exploits the fact that the natural integers are combinations of bits.

T-SQL Solution

Sample data:

DECLARE @Sample AS TABLE
(
    item_id     tinyint IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    item        nvarchar(500) NOT NULL,
    bit_value   AS
                CONVERT
                (
                    integer,
                    POWER(2, item_id - 1)
                )
                PERSISTED UNIQUE CLUSTERED
);

INSERT @Sample
    (item)
VALUES
    (N'Ann'),
    (N'Bob'),
    (N'Charles'),
    (N'Darren'),
    (N'Eric'),
    (N'Fred'),
    (N'George'),
    (N'Harry'),
    (N'Ian'),
    (N'John'),
    (N'Keith'),
    (N'Larry'),
    (N'Mark'),
    (N'Nathan'),
    (N'Owen'),
    (N'Paul'),
    (N'Quentin'),
    (N'Ryan'),
    (N'Steve'),
    (N'Terry');

Solution:

-- Maximum integer we need
-- for all combinations of 'n' bits
DECLARE
    @max integer =
    POWER(2,
        (
            SELECT COUNT(*)
            FROM @Sample AS s
        )
    ) - 1;

SELECT
    combination =
        STUFF
        (
            (
                -- Choose items where the bit is set
                -- and concatenate all matches
                SELECT ',' + s.item
                FROM @Sample AS s
                WHERE
                    n.n & s.bit_value = s.bit_value
                ORDER BY
                    s.bit_value
                FOR XML
                    PATH (''),
                    TYPE
            ).value('(./text())[1]', 'varchar(8000)'), 1, 1, ''
        )
-- A standard numbers table
-- (single column, integers from 1 to 1048576, indexed)
FROM dbo.Numbers AS N
WHERE
    N.n BETWEEN 1 AND @max;

Output sample:

╔════════════════════════╗
║      combination       ║
╠════════════════════════╣
║ Ann                    ║
║ Bob                    ║
║ Ann,Bob                ║
║ Charles                ║
║ Ann,Charles            ║
║ Bob,Charles            ║
║ Ann,Bob,Charles        ║
║ Darren                 ║
║ Ann,Darren             ║
║ Bob,Darren             ║
║ Ann,Bob,Darren         ║
║ Charles,Darren         ║
║ Ann,Charles,Darren     ║
║ Bob,Charles,Darren     ║
║ Ann,Bob,Charles,Darren ║
║ ...                    ║
╚════════════════════════╝

Execution plan:

It takes 41 seconds to write the 1,048,576 combinations to a variable on my laptop.  With forced parallelism, I was able to drop the execution time at DOP 8 to 13 seconds.

If you need a Numbers table, this is a quick way to produce one:

SELECT TOP (1048576)
    n = ISNULL(CONVERT(integer, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), 0)
INTO dbo.Numbers
FROM sys.columns AS c
CROSS JOIN sys.columns AS c2
CROSS JOIN sys.columns AS c3;

CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.Numbers (n)
WITH (MAXDOP = 1, SORT_IN_TEMPDB = ON);

SQLCLR solution

A much more efficient implementation is possible in SQLCLR (SQL Server 2005 onward):

CREATE ASSEMBLY [Demo]
    AUTHORIZATION [dbo]
    FROM [encoded data, 12290 characters];
GO
CREATE FUNCTION dbo.Combinations
(
    @ElementsCSV nvarchar (4000)
)
RETURNS TABLE
(
    Combination nvarchar (4000) NULL
)
AS EXTERNAL NAME Demo.UserDefinedFunctions.Permute;

Example usage:

SELECT
    f.Combination
FROM dbo.Combinations('A,B,C,D') AS f;

Output:

╔═════════════╗
║ Combination ║
╠═════════════╣
║ A           ║
║ B           ║
║ A,B         ║
║ C           ║
║ A,C         ║
║ B,C         ║
║ A,B,C       ║
║ D           ║
║ A,D         ║
║ B,D         ║
║ A,B,D       ║
║ C,D         ║
║ A,C,D       ║
║ B,C,D       ║
║ A,B,C,D     ║
╚═════════════╝

Using the 20-element set from earlier:

DECLARE @Sample AS TABLE
(
    item_id     tinyint IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    item        nvarchar(50) NOT NULL
);

INSERT @Sample
    (item)
VALUES
    (N'Ann'),
    (N'Bob'),
    (N'Charles'),
    (N'Darren'),
    (N'Eric'),
    (N'Fred'),
    (N'George'),
    (N'Harry'),
    (N'Ian'),
    (N'John'),
    (N'Keith'),
    (N'Larry'),
    (N'Mark'),
    (N'Nathan'),
    (N'Owen'),
    (N'Paul'),
    (N'Quentin'),
    (N'Ryan'),
    (N'Steve'),
    (N'Terry');

SQLCLR solution:

-- Create CSV input
DECLARE
    @Elements nvarchar(4000) =
        STUFF
        (
            (
                SELECT ',' + s.item
                FROM @Sample AS s
                ORDER BY
                    s.item_id
                FOR XML
                    PATH (''),
                    TYPE
            ).value('(./text())[1]', 'varchar(8000)'), 1, 1, ''
        );

DECLARE
    @bitbucket nvarchar(4000);

SELECT
    @bitbucket = combination
FROM dbo.Combinations(@Elements);

Execution time is 2.5 seconds for the 1,048,576 combinations on my laptop at DOP 1.

Creating the CSV input:

Finding the combinations:

C# Source Code:

using System;
using System.Collections;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction
        (
        DataAccess=DataAccessKind.None,
        SystemDataAccess=SystemDataAccessKind.None,
        FillRowMethodName="FillRow",
        TableDefinition="Permutation nvarchar(4000)"
        )
    ]
    public static IEnumerable Permute(string ElementsCSV)
    {
        // Split CSV
        string[] elements = ElementsCSV.Split(new char[] { ',' });

        // Highest integer needed
        int count = (int)Math.Pow(2, elements.Length) - 1;

        // Pre-computed array of 2^n values
        int[] powers = new int[elements.Length];

        for (int i = 0; i < powers.Length; i++)
        {
            powers[i] = (int)Math.Pow(2, i);
        }

        // Test integers
        for (int i = 1; i <= count; i++)
        {
            // Reset output
            string s = string.Empty;

            // Test each bit that could be set
            for (int bit = 0; bit < powers.Length && i >= powers[bit]; bit++)
            {
                if ((i & powers[bit]) == powers[bit])
                {
                    // Add the element corresponding to the set bit
                    s += elements[bit] + ',';
                }
            }

            // Return a row via enumeration
            yield return s.Substring(0, s.Length - 1);
        }
    }

    // Called by SQL Server to fetch a row
    public static void FillRow(object o, out string Permutation)
    {
        Permutation = (string)o;
    }
}


What to do when Sql Server Agent refuses to run a Job Step that references a missing database snapshot I have a SQL Server Agent Job creating a snapshot of my main database every minute:

-- delete snapshot if it exists
if exists (select * from sys.databases where name = 'mydb_snapshot' )
begin
    drop database [mydb_snapshot]
end;

-- create new snapshot (whether or not we just deleted an old one)
create database [mydb_snapshot] on ( NAME = 'MYDBYEAH', FILENAME = 'D:\mydb_snapshot.ss' ) as snapshot of [mydb];

The snapshot is used both for reporting, and for a query later in the same Job Step.

(I'm new to Sql Server Agent Jobs and Snapshots, so feel free to point out any WTFs so far.)

My problem is Sql Server Agent refuses to run the job at all, citing that [mydb_snapshot] doesn't exist:

  Database 'mydb_snapshot' does not exist. Make sure that the name is entered correctly. [SQLSTATE 08004] (Error 911).  The step failed.

(That error doesn't have a line number associated with it. Rather, it seems SS Agent is checking the whole script before it starts running it and is smart enough to see I reference a non-existant snapshot, but not smart enough to see I always create that snapshot in the previous line. With debug statements, I can see that it doesn't even run the first line of the TSQL in my Job Step).

Any ideas what I can do to fool SQL Server Agent into running this job?

Or perhaps a different approach altogether?

(One thing I tried: it does work if I run the above create database [mydb_snapshot] statement manually, first. From then on, the Job runs every minute, no issues. But that means if that snapshot is deleted - which has happened at least once, not sure why - my Job fails for no good reason).

Replace 'drop database [myDb_snapshot]' with:

declare @cmd nvarchar(max);
set @cmd = 'drop database [myDb_snapshot]';
exec (@cmd);

The entire SQL statement is evaluated for correctness prior to running it.  Since the snapshot doesn't exist, it won't run.  Dynamic SQL removes that limitation.


Simple scenario needs to go from Unnormalized Data to 3NF - Review Please NOTE: The Question has been updated. View Edit Revisions to view original question.

The scenario. An animal charity that at the moment, track their establishment's costs via a spreadsheet like so:

[broken image link removed]

1NF:

The above table is already in 1NF because it abides to the definition of 1NF.

  First Normal Form: A relation in which the intersection of each row and column contains one and only one value.

After reading Catcall's answer I can see that it is in determining my dependencies that I messed up. All I did originally was exactly like Catcall said ! added a primary key column, and said to myself "there, that fixes that!"

So obviously that was wrong. I have now read more carefully into functional dependencies and in this book I have, it defines them as:

  Functional Dependency: Describes the relationship between attributes in a relation. For example if A and B are attributes of relation R, and B is functionally dependent on A (denoted by A --> B), if each value of A is associated with exactly one value of B. There exists a functional dependency.

Keeping the above definition in mind, and examining my now expanded (more data) sample table, I can see that PetHouse Ltd is not always going to be associated with Dog food, and this breaks the part of definition about A being associated with EXACTLY one value of B.

So I guess in reality I am stuck here in identifying my functional dependencies. Here I am a little bit in the dark as I've never worked with a composite key that was made up of three different columns, but I'll go ahead anyway and see where I get to.

Catcall suggested using 3 columns [Name, BoughtFrom, TimeBought] as identifiers of data (i.e. a composite key for the table that is made up of 3 columns) and that makes sense as that would uniquely identify each row of data in the table. So the notation for the relation becomes:

IncuredCosts (Name, BoughtFrom, TimeBought, Cost)

The book in my possession also has this interesting part about the functional dependencies we need for normalization. It states that the functional dependencies we are after must have the following characteristics:

There is a one-to-one relationship between the attribute(s) on the left-hand side (determinant) and those on the right-hand side of a functional dependency. (Note that the relationship in the opposite direction can be a one-to-one or one-to-many relationship.)
They hold for all time.
The determinant has the minimal number of attributes necessary to maintain the dependency with the attribute(s) on the right-hand side. In other words, there must be a full functional dependency between the attribute(s) on the left-hand and right-hand sides of the dependency.

I believe all the above characteristics hold true for this functional dependency:

  (Name, BoughtFrom, TimeBought) --> Cost

Now then, Transitive Dependencies, which in the book (could be wrong like Catcall said) is defined as:

  Transitive Dependency: A condition where A, B and C are attributes of a relation such that if A --> B and B --> C, then C is said to be transitively dependent on A via B (provided that A is not functionally dependent on B or C).

Am I right in thinking that my relationship as defined earlier does not have any transitive dependencies? But then again, if that the case, it would imply that I have reached 3NF! cause apparently the major step to 3NF is the removal of Transitive Dependencies into their own relation.

Have I really reached 3NF?

Thank you by the way. I learnt a lot through this post.

Yes, it's in 1NF.

You can't side-step the often hard work of determining all the candidate keys by hanging a number off the end of the table and saying, "There. I've got a primary key."  One natural candidate key for this table is {Name, Bought from, Date bought}.  Consider using "Time bought" instead of "Date bought".

Your definition of 2NF is wrong. Instead of

  Second Normal Form: A relation that is in First Normal Form and every
  non-primary-key attribute is fully functionally dependent on the
  primary key.

you need something more like this.

Second Normal Form: a relation that is in First Normal Form, and every non-prime attribute is fully functionally dependent on every candidate key.

The term non-prime attribute doesn't mean quite what non-primary-key attribute means.

Your definition of 3NF is wrong, and it's wrong for the same reasons as your definition for 2NF was wrong.

Instead of this

  Third Normal Form: A relation that is in First and Second Normal Form
  and in which no non-primary-key attribute is transitively dependent on
  the primary key.

you need something closer to this.

Third Normal Form: A relation that is in Second Normal Form and in which every non-prime attribute is nontransitively dependent on every candidate key. (There isn't a really good way to express all those negative in one sentence.)


Which are the most often used SQL queries? the question sounds pretty simple but I couldn't find anything on topic except for this article here. So is there any (even remotely) scientific (or at least accredited) opinion on the matter? The actual DBMS doesn't matter at all - I just want to know if joins outweigh selects in the long run, or inserts outweigh creates and (if possible) by what margin. Something like that.

It depends...

This is extremely specific to the environment.  There are two types:

OLAP (Online Analytical Processing) - this type of system is designed more for reporting and analysis, in which case often times SELECTs dominate the majority of the queries.

OLTP (Online Transaction Processing) - this type of system is designed more for data manipulation, and typically INSERT`UPDATE\DELETEstatements are significant enough to optimize for this type of transaction.SELECT` is also a large part of this environment, and considerations need to be made for this as well.

Often times these roles are encompassed by a single instance/database.  Because of this both must be considered.  There are dominant systems (like Data Warehouses) that may be dedicated soley to OLAP.

  I just want to know if joins outweigh selects in the long run

Joins simply create a relationship between tables.  They are used in conjuction with the respective DML statement (SELECT, INSERT, UPDATE, DELETE).

  or inserts outweigh creates

It sounds like you're asking the ratio of DDL vs. DML.  In a typical implementation DML will be the majority of a production environment's queries, though DDL is almost never going to be stale.


Problem with complex queries after migrating from MySQL 5.1 to MariaDB 10.4 I completed a migration from MySQL 5.1 master-slave system to a MariaDB 10.4 Galera Cluster. All the databases where copied over since they were on InnoDB per-file and they are working fine. However some complex queries are VERY slow on the new system since they are completely useless and I'm a little bit loss... I fixed some of them by forcing an index but I don't know how to fix others like this one...
This is the query:
SELECT * FROM (
    SELECT * FROM abvalue WHERE deviceid='XXX'
) AS abvalue LEFT JOIN (
    SELECT * FROM abperson WHERE deviceid='XXX'
) AS abperson ON abvalue.person_id=abperson.person_id

(i did the query that way because it was the most efficient way back in the day)
this query is a little slow on the old system (about 2 seconds) but it completes. However in the new system I gave up after 60 seconds... I tried forcing indexes but I was unable to fix it.
This is the explain on MySQL 5.1
+----+-------------+------------+------+----------------+----------------+---------+------+-------+-------------+
| id | select_type | table      | type | possible_keys  | key            | key_len | ref  | rows  | Extra       |
+----+-------------+------------+------+----------------+----------------+---------+------+-------+-------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL           | NULL           | NULL    | NULL | 12428 |             |
|  1 | PRIMARY     | <derived3> | ALL  | NULL           | NULL           | NULL    | NULL |   694 |             |
|  3 | DERIVED     | abperson   | ref  | deviceid_index | deviceid_index | 36      |      |   693 | Using where |
|  2 | DERIVED     | abvalue    | ref  | deviceid_index | deviceid_index | 36      |      | 16484 | Using where |
+----+-------------+------------+------+----------------+----------------+---------+------+-------+-------------+

and this is the explain on MariaDB 10.4
+------+-------------+----------+------------+-----------------------------+-----------------------------+---------+--------------------------+---------+---------------------------------+
| id   | select_type | table    | type       | possible_keys               | key                         | key_len | ref                      | rows    | Extra                           |
+------+-------------+----------+------------+-----------------------------+-----------------------------+---------+--------------------------+---------+---------------------------------+
|    1 | SIMPLE      | abvalue  | ref        | deviceid_index              | deviceid_index              | 36      | const                    | 22780   | Using where                     |
|    1 | SIMPLE      | abperson | ref|filter | deviceid_index,person_index | person_index|deviceid_index | 5|36    | ownspy.abvalue.person_id | 39 (0%) | Using where; Using rowid filter |
+------+-------------+----------+------------+-----------------------------+-----------------------------+---------+--------------------------+---------+---------------------------------+

this is the structures of the tables:
CREATE TABLE `abvalue` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `deviceid` char(32) NOT NULL,
  `value` char(128) DEFAULT NULL,
  `type` int(11) DEFAULT NULL,
  `person_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `deviceid_index` (`deviceid`(12)) USING BTREE,
  KEY `person_index` (`person_id`) USING BTREE,
  KEY `value_index` (`value`(5)) USING BTREE
) ENGINE=InnoDB

CREATE TABLE `abperson` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `deviceid` char(32) NOT NULL,
  `first` text DEFAULT NULL,
  `last` text DEFAULT NULL,
  `person_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `deviceid_index` (`deviceid`(12)) USING BTREE,
  KEY `person_index` (`person_id`) USING BTREE
) ENGINE=InnoDB

Any help is really welcomed!

I would suggest getting rid of the single-column indexes for device and person, and replace them with:
INDEX device_person_index (deviceid, person_id)

If you have queries with predicates for person_id but not deviceid, you can add another index for that:
INDEX device_person_index (person_id, deviceid)

The above suggestion holds for both tables. I find KEY in this context very confusing, so I replaced it with INDEX which I beleive is a synonym.
I don't know whether MySQL/Mariadb can do index anding, but a composite index is anyhow more efficient than two separate indexes.


Which index shall I create in Postgres to speedup join of two big (several million rows) tables connected one-to-many? I have 2 tables, first one contains dated items (something like log records), second one - multiple attributes of those items.

First table:

CREATE TABLE items
(
  date date NOT NULL,
  point_id integer NOT NULL,
  /* several more columns */
);

Second table:

CREATE TABLE attributes
(
  attribute_id integer,
  value double precision NOT NULL,
  point_id integer,
  CONSTRAINT fk_point_id FOREIGN KEY (point_id)
      REFERENCES items (point_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE CASCADE
)

So for single record in items there are 1..N records in attributes. For now able items has about 35M of rows, for each row there are 2 rows in attributes table - one with attribute_id = 0 and second with attribute_id = 1

I have separate btree indexes on all listed fields. And what is unclear to me is why following query leads to Seq Scan of attributes table:

EXPLAIN SELECT s.date, a1.value AS value
    FROM items s
    INNER JOIN attributes a1
        ON s.point_id = a1.point_id
    WHERE a1.attribute_id = 0
    AND s.date >= '2016-12-01'::DATE

Here is explain result:

Hash Join  (cost=14457.15..1529906.57 rows=310075 width=12)
  Hash Cond: (a1.point_id = s.point_id)
  ->  Seq Scan on attributes a1  (cost=0.00..1376027.10 rows=36352418 width=12)
        Filter: (attribute_id = 0)
  ->  Hash  (cost=10569.55..10569.55 rows=311008 width=8)
        ->  Index Scan using items_date_idx on items s  (cost=0.56..10569.55 rows=311008 width=8)
              Index Cond: (date >= '2016-12-01'::date)

I see that Postgres uses index to filter items table and get only needed rows - there are about 300K of them. But then why it iterates over whole 70M attributes table to compose hash map? As I have indexes on 'point_id' column in both tables, why not to use them to perform merge join of just 300K?

I tried to enable/disable different planner options, but of course it get worse. I understand that planner does his job very well, there is just something wrong in my schema and I need to help planner somehow. I've tried to solve the issue with adding composite index like 'date,point_id' - at least it helped in Mongo (after filtering by date you have sorted list of point_id and may perform merge join), but Postgres ignored those indexes and used simple ones.

So which index shall I create (or how shall I redesign my schema) to get provided query work faster and prevent seq scan?

Not sure how can it help, but in items table each row has larger point_id than previous and same or larger date. Also, I do not need exact date/time - only date part.

In the query above if I remove date check condition I get the merge join as expected:

Merge Join  (cost=73.17..4229922.45 rows=36352418 width=12)
  Merge Cond: (s.point_id = a1.point_id)
  ->  Index Scan using items_point_id_idx on items s  (cost=0.44..1143957.50 rows=36461804 width=8)
  ->  Index Scan using attributes_point_id_idx on attributes a1  (cost=0.57..2540470.32 rows=36352418 width=12)
        Filter: (attribute_id = 0)

I use postgres 9.5

Result of explain(analyze,verbose): https://explain.depesz.com/s/P1D

In theory there could be custom amount of attributes, it is defined in runtime.

There is an index on attribute_id. I think it is not used because half of table has attribute_id = 0 and another half has attribute_id = 1.

It is quite reasonable for the planner to think that scanning 70,000,000 rows in sequential order is going to be faster than reading 310,075 in a random order.
If you tried disabling enable_seqscan and the query performance got worse, then it seems likely the planner is making the correct decision there.  Could you post an EXPLAIN (ANALYZE) of what happens in that case?

If it did a merge join on the index attributes_point_id_idx like you suggest, than for each row it returns it would have to jump to some random part of the attributes table to check that that row has not yet been deleted, to check the attribute_id to see if it is 0, and to fetch value so it can be returned.

If you make an index on (point_id, attribute_id, value) and you keep the table vacuumed, then it might choose to use an index only scan (as the input to the merge join) as now it can get all the data it needs from the index, no randomly ordered visit to the table is needed.

It could be a challenge keeping the attributes table adequately vacuumed.  The autovacuum system is only designed to reduce bloat.  Keeping index-only-scans efficient is not yet part of the autovacuum design.


SQLPLUS: SELECT INTO prompting for variable value Any idea why this is prompting me for the value?  I'm calling the script via batch file:

DECLARE
   precount   NUMBER;
   nowcount   NUMBER;
BEGIN
    SELECT COUNT(*) INTO precount FROM PLAN_OFFER_BK_PRE_SCRIPT WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
    SELECT COUNT(*) INTO nowcount FROM PLAN_OFFER WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
END;
/

-- NUMBER OF MEC/MVP RECORDS
SPOOL logs\MVP_MEC_UPDATED.log APPEND
PROMPT &&CLIENTID   &&precount  &&nowcount
SPOOL OFF;

Thanks!

The ampersand character (&) prompts for values before execution time by default. That is not how you display values of variables. Try the below instead.

set serveroutput on

SPOOL logs\MVP_MEC_UPDATED.log APPEND
DECLARE
   precount   NUMBER;
   nowcount   NUMBER;
BEGIN
    SELECT COUNT(*) INTO precount FROM PLAN_OFFER_BK_PRE_SCRIPT WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
    SELECT COUNT(*) INTO nowcount FROM PLAN_OFFER WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';

    dbms_output.put_line('precount: ' || precount || ',  nowcount:' || nowcount );
END;
/

SPOOL OFF

Passing a variable to a script in SQL*Plus. The script, let's say, 1.sql:

set serveroutput on feedback off verify off
define CLIENTID=&1

SPOOL logs\MVP_MEC_UPDATED.log APPEND
DECLARE
   precount   NUMBER;
   nowcount   NUMBER;
BEGIN
    SELECT COUNT(*) INTO precount FROM PLAN_OFFER_BK_PRE_SCRIPT WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
    SELECT COUNT(*) INTO nowcount FROM PLAN_OFFER WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';

    dbms_output.put_line('CLIENTID: ' || '&CLIENTID' || ', precount: ' || precount || ',  nowcount:' || nowcount );
END;
/

SPOOL OFF
exit

Invoking it:

sqlplus -s user/password @1.sql 101

Where 101 is the CLIENTID. Or:

sqlplus -s user/password @1.sql %CLIENTID%

Where %CLIENTID% is a variable in your batch script.


Accessing Data after importing from file I'm going to preface this with saying I'm a complete newbie at this database stuff before the rolling of the eyes happens, so please be patient with me.

I have an sql dump that I am trying to import and access (running Linux mysql). I created the database...

mysql> CREATE DATABASE db_name

And then tried to import the sql file into the database...

mysql -uroot -p db_name < db_import.sql

At this point, I don't see any errors and everything seems fine, so I assume the import worked. However, I have no idea how to access the imported data because SELECT * FROM db_name; returns ERROR 1146 (42S02): Table 'db_name.db_name' doesn't exist. Similarly for FROM db_import;.

I understand that it's looking for a table db_name within db_name, but where would the imported data go? How can I access it? Did I do the import wrong?

Connect using mysql cli:

mysql -u <username> -p<pass> db_name

The following command will show you the imported tables:

show tables;

Then, access the data:

select * from <table_name>;

where *table_name* is one of the tables in the show tables' output.


Copy real time data from Azure SQL into Azure Data Warehouse I would like to copy our data from Azure SQL (OLTP) into Azure Datawarehouse. I want the path to be close to real time.
The Microsoft system looks it takes two steps copying data from SQL Server into Azure Data Warehouse.

First into Blob Storage with Azure Data Factory

and then with Polybase into Azure Datawarehouse.

It looks like a two step ETL and not close to real time.
Does Microsoft have any simpler way of copying data from Azure SQL into DW for analytics?

You may want to consider using Striim as mentioned here by Microsoft Program Manager of Azure SQL Data Warehouse. Ellis Butterfield.


Oracle 19c installation stuck at creating and starting oracle instance I'm trying to install oracle 19c but it's stuck at 52% for over 7 hours. So cancelled it and uninstalled it. I have started installing again but still stuck at 52% that is in creating and starting oracle instance.

Two of the most common reasons for the Oracle DB installation to get stuck at this point are:

inability to write to %TEMP% directory (usually as a result of AD domain policies or a full disk)
virus check software (other than Windows Defender) interfering with the installation process

If you need to cancel the installation and try again, be sure to confirm that the %TEMP% directory on your Windows box does not have any leftover directories prefixed with deinstall, OraInstall or CVU, as these may be reused by future installations, creating a different set of issues.


Avoid already existing objects when importing into Oracle I need help with impdp. I have taken a backup with:

expdp system/pw@orcl full=y directory=abc dumpfile=full.dmp

Now I need to import the dump into another instance, which has recently been created. I was going to import the full database dump, but it raises an error when an object already exists.

However, these objects belong to Oracle's default schemas, which I can not remove. How do I avoid these import errors?

Exclude the schemas with the exclude parameter. E.g:

EXCLUDE=SCHEMA:\"IN\(\'SYS\',\'SYSTEM\',\'OUTLN\',\'ORDDATA\',\'OLAPSYS\',\'MDDATA\',\'SPATIAL_WFS_ADMIN_USR\',\'ANONYMOUS\',\'SPATIAL_CSW_ADMIN_USR\',\'ANONYMOUS\',\'FLOWS_FILES\',\'APEX_PUBLIC_USER\',\'APEX_030200\',\'OWBSYS\',\'OWBSYS_AUDIT\',\'SCOTT\',\'SYSMAN\',\'MGMT_VIEW\'\)\"

The list of schemas depends on the database version and options installed.

In a parameterfile, you can use the above without \ (escaping special characters).


How to use merge hints to isolate complex queries in SQL Server I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal.  However when I know the number of rows for one query is very small I think I should be able to use hints to control the joins.

select cj.a, cv.b
  from (select distinct a from complexJoin) cj -- 2 rows
 inner loop join complexView cv
    on cj.a = cv.a
 order by cj.a, cv.b

If cj is <1s and cv <1s expect this to be <~2s but using any hint (merge/hash/loop) it is often > 1min.

I also tried to use CROSS APPLY because the docs claimed that the inner select is executed exactly once for each outer row. The query takes ~100x longer than running the inner query twice manually so perhaps I don't understand the docs.

select cj.a, cv.b
  from (select distinct a from complexJoin) cj -- 2 rows
 cross apply (select * from complexView
               where a = cj.a) cv
 order by cj.a, cv.b

If I populate a temp table with results from "cj" and then join _with_no_hint_ or use the cross apply it is fast but do I really have to resort to that?  If I use the temp table and attempt "any" of the join hints (loop/merge/hash) it is slow so maybe that is a key point.

I don't believe diving into the depths of the query plan (both are complex to begin with) are required to solve this type of problem in general: I just want guaranteed isolation without resorting to a temp table--is that really not possible?

If you use a multi-statement UDF, then your inner select is executed exactly once for each outer row. The multi-statement UDF is treated as a black box: the execution plan will now show access to the objects used in your complex view.

On the other hand, a subquery and/or an inline UDF is flattened out by the optimizer. When this is the case, the execution plan will include access to the objects used in your complex view.


Difference between Create login, credential and user in sqlserver I am bit confused about sqlserver Login, Credentials and User.
How they are connected or mapped?

When we are using connection string in asp.net what are we using Login or credentials..

Thank you.

Login:
A login is any principal that is granted access to a SQL Server instance.  The access can be granted to domain users, domain group, or SQL Server authenticated accounts.

Credential:
 A credential provides a mechanism to store login information for a domain account within SQL Server.  The credential can then be used to pass that authentication information from into another login or a proxy to grant it permissions to resources external to SQL Server.

.....use of Login
if you need to login and access a SQL Server instance, you’ll just use a login.  Regardless of your needs, if you need to get in and do work, the login is through route to go.  The other two won’t help you out at all.

Use of credentials..
Credentials get brought in when permissions from domain users need to be granted to accounts or services that wouldn’t usually have those permissions.  For instance, if an assembly required EXTERNAL_ACCESS, those permissions could be granted through the use of a credential.  Credentials can also be used to grant SQL Authentication accounts access to external resources.


Numbering multiple dates per delivery I am interested in producing the following view where different delivery dates are numbered.

SalesID    Delivery Date DateNo
SO00000014 2014-02-21    1
SO00000015 2014-02-13    1
SO00000016 2014-01-28    1
SO00000017 2014-03-17    1
SO00000018 2014-02-13    1
SO00000020 2014-03-19    1
SO00000020 2014-03-20    2
SO00000020 2014-03-21    3

My question is how to number different dates associated to a single SalesId like in the
 SO00000020 above. I am using SQL Server 2008.

Not knowing your data, the view definition would be:

SELECT
    [SalesID],
    [Delivery Date],
    ROW_NUMBER() OVER (PARTITION BY [SalesID] ORDER BY [Delivery Date]) AS DateNo
FROM
    [WhatEverTable]

http://technet.microsoft.com/en-us/library/ms186734(v=sql.105).aspx

ROW_NUMBER() by itself will return a running count of the rows, but when you add the OVER(PARTITION BY [SalesID] ORDER BY [Delivery Date]) it gives a running count per sales id, ordered by the date.

Does that make sense?

EDIT: If you do make this a view, just be aware that the results will be filtered by your WHERE clause. Using your data above, if you queried for sales that occurred after 2014-03-20, SalesID SO00000020 will only have a single entry for 2014-03-21 with a DateNo of 1.

If you need the data to persist regardless of the WHERE clause, you will likely just have to scrap the idea of a view and get creative with the query that accesses the data.


Why select holds exclusive lock on table? I'm currently struggling with a deadlock (graph) in our application.

Both processes execute the same query:

exec sp_executesql N'SELECT [GroupBy1].[A1] AS [C1]
                     FROM (SELECT COUNT(1) AS [A1]
                           FROM   [dbo].[Batch] AS [Extent1]
                           WHERE ([Extent1].[BatchGroupId] = @p__linq__0)
                           AND   ([Extent1].[Status] <> @p__linq__1)
                          )  AS [GroupBy1]',
                   N'@p__linq__0 int, @p__linq__1 int', @p__linq__0=6, @p__linq__1=8515

Here is the index definition:

CREATE NONCLUSTERED INDEX IX_Batch_BatchGroupId_Status
ON dbo.Batch
(
    BatchGroupId asc,
    Status asc
)

There are data modification statements within the same transaction.

indexname = "IX_Batch_BatchGroupId_Status"
id = "lock2aa71e32d80"
mode = "X"
associatedObjectId = "72057594166968320"

Exclusive locks associated with those data modifications will be held until the transaction is complete.

See the Deadlock Troubleshooting posts by Bart Duncan.


Max of a query which is inside Create a database for a drone delivery startup that distributes pizzas via drones. The startup has partnerships with several drone manufacturers and pizza shops. The entities of interest to the problem domain are: Drone Manufacturers, Drone Models, Drones, Pizza Shops, Customers, and Deliveries. A drone manufacturer has a name and can produce different drone models. A drone model belongs to a drone manufacturer; it has a name, battery life (in minutes, integer number) and maximum speed. An actual drone is of a particular model and has a serial number. A pizza shop has a name and address. A customer has a name and loyalty score. Pizza shop names and customer names are unique. For each delivery, the system records the corresponding customer and pizza shop, the drone that is used, as well as the date and time. This is how I proceeded:
CREATE TABLE DroneManufacturers(
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(30)
);

CREATE TABLE DroneModels(
    id INT PRIMARY KEY IDENTITY(1,1),
    manufacturer_id INT REFERENCES DroneManufacturers(id),
    name VARCHAR(30),
    battery_life INT,
    max_speed INT
);

CREATE TABLE Drones(
    serial_number VARCHAR(10) PRIMARY KEY,
    model_id INT REFERENCES DroneModels(id),
);

CREATE TABLE PizzaShops(
    name VARCHAR(30) PRIMARY KEY,
    address VARCHAR(50)
);

CREATE TABLE Customers(
    name VARCHAR(30) PRIMARY KEY,
    score INT
);

CREATE TABLE Deliveries(
    pizza_shop VARCHAR(30) REFERENCES PizzaShops(name),
    customer VARCHAR(30) REFERENCES Customers(name),
    drone VARCHAR(10) REFERENCES Drones(serial_number),
    delivery_time DATETIME
);

I think the design is ok, but I have some problems in the next query:
Create a view that shows the names of the startup’s favorite drone manufacturers, i.e., those with the largest number of drones used by the startup.
Example: suppose the startup has partnerships with 3 manufacturers: M1, M2, M3; it has 10 drones from M1, 10 drones from M2, and 8 drones from M3. M1 and M2 are the manufacturers with the largest number of drones.
I have tried like this:
SELECT t.name
FROM (
    SELECT dman.id, dman.name, COUNT(*) AS counts
    FROM Drones d
    INNER JOIN DroneModels dm
        ON d.model_id = dm.id
    INNER JOIN DroneManufacturers dman
        ON dm.id = dman.id
    GROUP BY dman.id, dman.name
    ) t
WHERE t.counts = MAX(t.counts)

And I get an error because the last "where" clause is not written properly. Can somebody help me with this query, please?

Just a heads up that you can't use aggregate functions (like MAX()) in a WHERE clause, rather that's the purpose of the HAVING clause. But instead of using an aggregate function, a window function like DENSE_RANK() simplifies the solution you're looking for, like so:
CREATE VIEW FavoriteDroneManufacturers AS

SELECT s.[name]
FROM
(
    SELECT dman.[name], DENSE_RANK() OVER (ORDER BY COUNT(1) DESC) AS DroneCountRankId
    FROM Drones d
    INNER JOIN DroneModels dm
        ON d.model_id = dm.id
    INNER JOIN DroneManufacturers dman
        ON dm.id = dman.id
    GROUP BY dman.id, dman.[name]
) AS s
WHERE s.DroneCountRankId = 1

(Note the above uses a subquery since you were weary of using CTEs, but the logic inside the subquery could've just as equally been created in a CTE instead, and a View can equivalently be created on both either way.)
DENSE_RANK() generates an ID for each row ordered by the logic you specify in the ORDER BY clause. When there are two rows that tie for ordering (based on the logic specified) then the same ID will be generated for both.
In the above query, I'm ordering (by descending) on the grouped COUNT() of drones for each manufacturer, and when two manufacturers share the same COUNT() of drones, then they get the same DroneCountRankId. In the outer query, I filter on DroneCountRankId = 1 to give us only the manufacturer(s) with the max number of drones (if it's two manufacturers like in your example, then they are both returned this way). (If you changed the WHERE clause to DroneCountRankId = 2 then you'd get all manufacturer(s) with the top 2 number of drones, etc.)
I highly recommend reading up on window functions and the DENSE_RANK() function in particular, via the links I provided above.


How to get the port of a connection in mysql I can do:
select * from information_schema.processlist plist where id=connection_id();

To get the data from the current connection I am on, this will give me something that looks like:
# ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
'6558851', 'avails', '99-29.lit:55353', 'aQvails', 'Query', '0', 'executing', 'select * from information_schema.processlist plist where id=connection_id()'

And the port in there is 55353. Is there a way to grab the port directly, for example something like:
SELECT connection_id(), user(), port_id(??)

You should be able to use one of the solutions in this StackOverflow: How do I find out my MySQL URL, host, port and username?
Specifically I think this is what you're looking for: SHOW VARIABLES WHERE Variable_name = 'port';


Getting Dates In Range Into Table I have a calendar table that shows all dates from 01/01/2015 to 12/31/2016, essentially the calendar shows the date and isworkday.  I need to be able to pass a begindate and enddate to a stored procedure and insert all the dates that are in the range in a table.  So for example something like this

Create Procedure [dbo].[DateRangeTest]
(
  @begindate date
  ,@enddate date
)
As

Set @begindate = '01/01/2016'
Set @enddate = '01/31/2016'

Now here is where my lack of knowledge comes to play, how can I then take all the dates in that range that are flagged as workdays from my workcalendar table and insert those specific dates into a temp table?

Guessing at column names:

INSERT #temp(dateColumn)
  SELECT dateColumn
  FROM dbo.WorkCalendar
  WHERE IsWorkDay = 1
  AND dateColumn >= @begindate
  AND dateColumn < DATEADD(DAY, 1, @enddate);

Also please try to avoid regional formats like m/d/y. These will bite you in the end, because someone will run your code on a server with different language or regional settings, and it will be interpreted as d/m/y. See Bad habits to kick : mis-handling date / range queries. Also, for information on why I didn't use BETWEEN, see What do BETWEEN and the devil have in common? It may work for the date data type, but I'd rather be consistent than surprised.


How to check total allotted space inside a HDFS 'group' Our DBA has created a schema for our team in HDFS/HIVE. Not sure if 'schema' is the right word, they call it a 'group'.
Anyway, we can only write to the data lake inside this schema, whether it is parquet files or hive tables.
Is there a way to check what is the max space allocated to our group , knowing only the schema name?
I dont want to accidentally load too much data.
Thank you.

It is not possible to set space quotas on Hive level only because Hive is quite detached from HDFS storage. In Hive you can create tables not only in the hive.metastore.warehouse.dir but also can have external tables in other directories in HDFS. External tables can be being loaded by not only hive, even Hive-managed tables can be loaded by some other tools without Hive(for example you can put files manually into table directory, does not matter Managed or External). Also you can use HDFS not only for Hive. So, it is not possible to set space quotas in Hive and even if it was possible, it is not practical, Hive is not aware what else you are doing in HDFS.
Read about HDFS space quotas. It is possible to set space quotas for files inside directory. Also it is possible to set permissions for users (groups) allowing them to access some directories. HDFS directory ACL + directory space quota can be combined to restrict users or groups to use only allowed directories (whith space quotas).
You can check directory quota using
hadoop fs -count -q /path/to/directory
To check directory ACL use this command:
hdfs dfs -getfacl [-R] <path>
See more in FS shell commands guide.
Read also about Hive Authorization Options


sp_WhoIsActive and get_plans -- Actual Plan? Does Adam Machanic's sp_WhoIsActive return the actual query plan or the estimated plan when run with @get_plans=1 ?

The tool grabs the plan that is being run at that time. From an email response from Adam:

"Actual plan" usually refers to the query plan that has the actual row counts, number of executions of each iterator, etc. Who is Active cannot return that version of the plan. But the plan it returns is indeed the "actual" plan that's running at the time -- in other words, the plan will not be recompiled into some other plan by the time Who is Active can get it.


Hi, I have sitution where my Database has corrupted but some how we have recovered it but while restoring the .bak file it shows an error this is the error show while restoring the corrupted file .. Pls help if any one know
Thanks

The error message indicates that the backup file is corrupt (something happened after a successful backup to break the file), incorrectly formed (possibly caused by a backup that failed mid way), or possibly not even a SQL Server backup (someone just named the file with a BAK extension).

When attempting to restore a backup from Management Studio Studio, SSMS uses the RESTORE HEADERONLY command before it actually restores the database. This step is the step that is being reported as failing in your error message.

This tells us two important things:

The operation fails prior to the actual restore.
At minimum, the backup header is corrupt or missing, but possibly more.

The header tells SQL Server information about this backup, as well as the database from which this backup was created. Without this information, the backup cannot be restored.

Your best bet is to move on to an older backup and attempt to restore that. If you have no good backups, then you're pretty much out of luck.


Is there a way to auto resume an incomplete job after sql server restart? I ran into problem of job stopped after the sql server restart.

Is there a way to auto resume an incomplete job after sql server restart?

Thanks!

In a nutshell maybe. If you have a sql job without a notion of persistence then no:( The job will continue to run according to schedule but it will not pick up from an earlier starting point unless you have to logic to do so.


How do I restore an Oracle file system backup onto a new developer machine? A server (not mine) has died due motherboard temperature problems and disk controller problems. Given that the company's IT support should have known better, and so on, the company does have a file system backup of a directory structure that looks like this:

Oracle
├───adminscripts
│       (this looks like old custom scripts)
├───EM
│   └───DBNAME
│           emkey.ora
├───fast_recovery_area
│   └───DBNAME
│       └───ARCHIVELOG
│           ├───2015_06_01
│           ├───2015_06_02
│           ├───....
│           ├───2018_11_03
│           └───2018_11_04
│                   O1_MF_1_5091_FXVO24XT_.ARC (~80MB)
│                   O1_MF_1_5092_FXWPML1J_.ARC (~85MB)
│                   O1_MF_1_5093_FXXTSO2W_.ARC (~40MB)
├───oradata
│   ├───orcl
│   │       CONTROL01.CTL (~10MB)
│   │       REDO01.LOG    (~50MB)
│   │       REDO02.LOG    (~50MB)
│   │       REDO03.LOG    (~50MB)
│   │       SYSAUX01.DBF  (~520MB)
│   │       SYSTEM01.DBF  (~750MB)
│   │       TEMP01.DBF    (~20MB)
│   │       UNDOTBS01.DBF (~46MB)
│   │       USERS01.DBF   (~5MB)
│   └───DBNAME
│       ├───CONTROLFILE
│       │       O1_MF_9T04M5YO_.CTL (~10MB)
│       ├───DATAFILE
│       │       O1_MF_SVO_DATA_9T06KN5J_.DBF (~9GB)
│       │       O1_MF_SVO_DATA_9T06LGRN_.DBF (~1.5GB)
│       │       O1_MF_SVO_DATA_9T06LO63_.DBF (~30MB)
│       │       O1_MF_SYSAUX_9T04J8M6_.DBF   (~1.8GB)
│       │       O1_MF_SYSTEM_9T04J8JR_.DBF   (~1.5GB)
│       │       O1_MF_TEMP_9T04MLQV_.TMP     (~600MB)
│       │       O1_MF_TOOLS_9T06LODY_.DBF    (~100MB)
│       │       O1_MF_UNDOTBS1_9T04J8N5_.DBF (~500MB)
│       │       O1_MF_USERS_9T04J8O5_.DBF    (~5MB)
│       └───ONLINELOG
│               O1_MF_1_9T04MB6F_.LOG        (~100MB)
│               O1_MF_2_9T04MC8Y_.LOG        (~100MB)
│               O1_MF_3_9T04MDHC_.LOG        (~100MB)
└───scripts
        (this looks like old custom scripts)

I have to assume that this database backup was done safely, with the database put into backup mode or stopped before the backup was done. The backup is a disk image of a data disk, and doesn't contain other configuration information.

So I need to be able to extract the data from this. How do I do it? I assume that I have to set up an Oracle instance on my computer (64-bit Windows 10 Pro), and then somehow move this data somewhere into the installation and hook it up. I've downloaded the Oracle installer, but is there a particular kind of setup that's going to be best for this?

I know how I'd do it with MySQL, but this is new to me. I've searched for backup / restore procedures for Oracle, but they tend to involve rman.

Here's what I did ... this required a lot of trial and error, but I've got access to the database.

Installed Oracle with it's home on an R: drive. Then opened an administrative command prompt, then:

R:\>set ORACLE_HOME=r:\oracle\product\11.2.0\dbhome_1
R:\>set ORACLE_SID=SVP
R:\>oradim -new -sid SVP -startmode manual -pfile r:\oracle\product\11.2.0\dbhome_1\dbs\init_svp.ora
R:\>sqlplus /nolog
SQL> connect / as sysdba
SQL> create spfile='r:\oracle\product\11.2.0\dbhome_1\database\SPFILESVP.ORA' from pfile='r:\oracle\product\11.2.0\dbhome_1\dbs\init_svp.ora';
SQL> startup nomount
SQL> CREATE DATABASE SVP
   USER SYS IDENTIFIED BY oracle
   USER SYSTEM IDENTIFIED BY oracle
   EXTENT MANAGEMENT LOCAL
   DEFAULT TEMPORARY TABLESPACE temp
   UNDO TABLESPACE undotbs1;
SQL> shutdown immediate

At this point, I moved the new database's CONTROLFILE, DATAFILE and ONLINELOG folders away from R:\oracle\oradata\SVP and replaced them with the ones from the backup oradata\DBNAME folder (from the question).

SQL> startup nomount -- (because we'd get an error here about the control file)
SQL> alter system set control_files='R:\oracle\oradata\SVP\CONTROLFILE\O1_MF_9T04M5YO_.CTL' scope=SPFILE;
SQL> alter system set compatible='11.2.0.4.0' scope=SPFILE;
SQL> shutdown immediate
SQL> startup

The init_svp.ora file looks like this:

db_name='SVP'
memory_target=1G
processes = 150
audit_file_dest='r:\oracle\admin\SVP\adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_create_file_dest='r:\oracle\oradata'
db_recovery_file_dest='r:\oracle\fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='r:\oracle'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
compatible ='11.2.0'

I don't know if I've got anything I don't need, and I don't particularly care. I've got access to the data, so I'm all good.

Thanks to those who posted ... mustaccio's comment about making Oracle think it already existed by supplying the control file was helpful, as were several web pages that got me through the "control_files" and "compatible" parameter issues.

EDIT: After you reboot your computer, you may get an Oracle error saying something like:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

You'll need to mount your database with these commands (substituting in your details, of course):

R:\>set ORACLE_HOME=r:\oracle\product\11.2.0\dbhome_1
R:\>set ORACLE_SID=SVP
R:\>sqlplus /nolog
SQL> connect / as sysdba
SQL> startup


When is the index actually created? I have noticed that even on huge tables with millions of records when I do a CREATE INDEX the index is created immediately (I get the console back in nsecs).
Why is this? Doesn't it create the B-Tree with the table data at that point? I can only assume not due to the immediate return of the CREATE INDEX.
But then how is the index created? On each access?

Secondary index creation on innodb has been much faster since MySQL 5.1 + InnoDB plugin or MySQL 5.5. In fact, we refer to this feature as "fast index creation". If we don't touch the primary key, it only creates an additional structure and it only has to read the current values, without modifying the table data. Assuming you have enough memory, if the data pages are in memory and the index also fits into memory- this will be a memory-only operation, so potentially very fast. But the index will be ready to be used as soon as the process ends. In the background (after you get control back to the console), it will write the new pages to disk whenever it can. It is also dependent on how much data fragmentation you have in that particular tablespace. None of this can lead to losing data due to the transaction log (which is on disk).

But you are completely right by saying that some changes on secondary indexes are done in the background: it is called Change buffer, but it is used when modifying data records.

I must say that in my experience, InnoDB index creation is not as fast as it could :-), specially on very busy servers. However, starting with 5.6, creating secondary indexes is completely online (writers to that table are not blocked), which is a very nice improvement indeed.


Counting show variable result in MySQL I am reading this article and there doesn't seem a way to do something like this:

SELECT COUNT(*)
WHERE SHOW VARIABLES
WHERE Variable_name = 'innodb_ft_cache_size'

I can do the below but this doesn't work in MySQL 5.7 (No variables in table)

SELECT COUNT(*)
FROM information_schema.GLOBAL_VARIABLES
WHERE Variable_name = 'innodb_ft_cache_size' (always returns 0)

The reason I need to do this is to detect in a stored procedure if they have the ability to do full text. It needs to work in MySQL and variants of MySQL so I cannot just simply parse @@verison.

Is there another way to do this?

Here is a stored procedure that works on 5.5,5.6, and 5.7 to detect presence of full text abilities. This should also work on mysql variants

begin
set @supports_ft = 0;
set @information_schema_exists := (SELECT COUNT(SCHEMA_NAME) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'information_schema');

if @information_schema_exists > 0 then
    set @information_schema_exists_global_vars_exists := (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'information_schema' AND TABLE_NAME = 'GLOBAL_VARIABLES');
        if @information_schema_exists_global_vars_exists > 0 then
            set @supports_ft := (SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'innodb_ft_cache_size');
        end if;
end if;

if @supports_ft = 0 then
    set @performance_schema_exists := (SELECT COUNT(SCHEMA_NAME) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'performance_schema');
    if @performance_schema_exists > 0 then
        set @performance_schema_exists_global_vars_exists := (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'GLOBAL_VARIABLES');
            if @performance_schema_exists_global_vars_exists > 0 then
                set @supports_ft := (SELECT COUNT(*) FROM performance_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'innodb_ft_cache_size');
            end if;
    end if;
end if;

You can run

flush status;
show global variables like 'innodb_ft_cache_size';
show status like 'handler_read_rnd_next';

If it is 2 (or incremented by 2) than you have the variable. If it is 1 (or incremented by 1) you don't.

That should work on every version.


How can i automate DIfferential restore using dbatools Powershell How can i automate DIfferential restore using dbatools Powershell
I have configured FUllbackup migration from powershell using dbatools and using multi thread I am able to restore 4.5TB under 4 hours.
As for the downtime I want to also automate the Differential Restore using powershell.
Is this possible?

Can you share what you've come up with so far?
In the Restore-DbaDatabase you can use -IgnoreDiffBackup to ignore the differential backups, so I presume there should be no problem with automating your diffs.
Can you add more details what you are trying to achieve and how your backup process looks like?


IBM DB2 Timetravel logging based on some criteria I have been searching for the condition, where, lets say when we enable time travel to a certain table in DB2 , but don't want to capture all the updates done, but only the updates that's done  by some specific user.

Wanted to know if this is at all possible with the DB2 time travel and how we can achieve it .

With Db2 (LUW) Temporal Tables, there is not any option to exclude updates from a certain user.

However, the SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY procedure is available, and can be called by a user (if a user is granted permission on it), and this will disable the recording of temporal history for system-period temporal tables with the the duration of the current session.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0060661.html

  Set the internal option to disable recording of temporal history for a system-period temporal table and allow values to be specified for the special temporal columns.


How to check backup against SQL AG by Get-DbaLastBackup? I use the following PowerShell script to check if the last full backup successful daily:

$SQL_Server_list =  Get-Content D:\Tools\DBA\SQLPRD_FINAL.txt
$SQL_Server_list | Get-DbaLastBackup | Where-Object { ($(get-date $_.LastFullBackup) -lt (Get-Date).AddDays(-1)) } | Format-Table -AutoSize | Out-File D:\Tools\DBA\SQL_Check_PRD_Backup.txt -Width 255

It works fine except the SQL AG.

I have SQLAG_Listener, SQLAG_NODE1, and SQLAG_NODE2 in SQLPRD_FINAL.txt. I set full backup prefer secondary on AG and I do get the full backup from SQLAG_NODE2.

But I still get "database not fully backed up in 24 Hours" alerts from  SQLAG_Listener and SQLAG_NODE1. I cannot exclude the SQLAG_Listener or SQLAG_NODE1 since the fail-over might happen.

How can I avoid this?

BTW: If I do the full backup against primary side, I will get the alerts from secondary side instead...

Thanks in advance!

Get-DbaLastBackup currently doesn't support the ability to check the last backup on a database that's in an availability group.  You will either need to modify the GetDbaLastBackup code or use another script that queries the servers in the availability group, collects the last backup information from each of them, and returns the latest date that was collected.

This would certainly be a nice feature--even some of the big money SQL Server monitoring tools don't provide a nice mechanism for monitoring transaction log backups on availability groups.


Which is better: many join conditions or many where conditions? I am trying to compare two queries:

Query 1:

SELECT a,b,c,d,e
FROM tableA
LEFT JOIN tableB
ON tableA.a=tableB.a
WHERE tableA.b=tableB.b AND tableA.c=tableB.c  AND tableA.d=tableB.d  AND tableA.e=tableB.e

Query 2:

SELECT a,b,c,d,e
FROM tableA
LEFT JOIN tableB
ON tableA.a=tableB.a AND tableA.b=tableB.b AND tableA.c=tableB.c  AND tableA.d=tableB.d
WHERE tableA.e=tableB.e

Am I right to say that the these two queries give the same results?

Further, is it correct to say that the first query builds a bigger table for which to do a bigger WHERE condition; whereas the second case we have a smaller constructed table to which the simple WHERE is then applied.

Assuming the results are the same, which query should be preferred? Is there an obvious performance issue?

If we consider that you use INNER JOIN instead of LEFT JOIN(which appears to be your intent), these two queries are functionally equivalent.  Query optimizers will review and evaluate criteria in your WHERE clause and your FROM clause and consider all of these factors when building query plans in order to reach the most efficient execution plan.  If we do an EXPLAIN on both statements, we get the same result:

Query 1:

EXPLAIN
SELECT
  tableA.ColA
  ,tableA.ColB
  ,tableA.ColC
  ,tableA.ColD
  ,tableA.ColE
FROM tableA
  JOIN tableB ON tableA.ColA=tableB.ColA
WHERE
  tableA.ColB=tableB.ColB
  AND tableA.ColC=tableB.ColC
  AND tableA.ColD=tableB.ColD
  AND tableA.ColE=tableB.ColE

[Results]:

| ID | SELECT_TYPE |  TABLE | TYPE | POSSIBLE_KEYS |    KEY | KEY_LEN |    REF | ROWS |                          EXTRA |
------------------------------------------------------------------------------------------------------------------------
|  1 |      SIMPLE | tableA |  ALL |        (null) | (null) |  (null) | (null) |    1 |                                |
|  1 |      SIMPLE | tableB |  ALL |        (null) | (null) |  (null) | (null) |    1 | Using where; Using join buffer |

Query 2:

EXPLAIN
SELECT
  tableA.ColA
  ,tableA.ColB
  ,tableA.ColC
  ,tableA.ColD
  ,tableA.ColE
FROM tableA
  JOIN tableB ON tableA.ColA=tableB.ColA
  AND tableA.ColB=tableB.ColB
  AND tableA.ColC=tableB.ColC
  AND tableA.ColD=tableB.ColD
WHERE
  tableA.ColE=tableB.ColE

[Results]:

| ID | SELECT_TYPE |  TABLE | TYPE | POSSIBLE_KEYS |    KEY | KEY_LEN |    REF | ROWS |                          EXTRA |
------------------------------------------------------------------------------------------------------------------------
|  1 |      SIMPLE | tableA |  ALL |        (null) | (null) |  (null) | (null) |    1 |                                |
|  1 |      SIMPLE | tableB |  ALL |        (null) | (null) |  (null) | (null) |    1 | Using where; Using join buffer |

You can review the full details with the following links.  I also created a SQL 2008 example so that you can compare how the two engines work (which is the same):

MySQL query example

SQL 2008 query example (Make sure you 'View Execution Plan' for both results)


Does any RDBMS parallelize or otherwise optimize UNION queries? For an example data set with rows fulfilling both subqueries:

SELECT h.batch, li.id as line_item, d.prusr as price, 0 as advertised
FROM schema.lineitem li
INNER JOIN schema.detail d on d.line = li.line and d.item = li.item
INNER JOIN schema.header h on d.batch = h.batch
LEFT JOIN schema.specialoffer sp ON sp.LINE = d.LINE AND sp.ITEM = d.ITEM
WHERE h.status = 'T' AND sp.batch IS NULL

UNION ALL
SELECT h.batch, li.id as line_item, d.prusr as price, 1 as advertised
FROM schema.lineitem li
INNER JOIN schema.detail d on d.line = li.line and d.item = li.item
INNER JOIN schema.header h on d.batch = h.batch
INNER JOIN schema.specialoffer sp ON sp.LINE = d.LINE AND sp.ITEM = d.ITEM
WHERE h.status = 'T'

What RDBMS will attempt to parallelize this type of query? I'd also be interested in any other "under the hood" sorts of optimizations you may know of, but parallelization is the main focus of this question.

References I have found already:

MySQL: No - Can MySQL parallelize UNION subqueries (or anything at all)?
Oracle: Maybe - Does a database parallelize a SQL query which has a union in it?

Can each section of UNION/UNION ALL run concurrently?

Oracle

YES (oracle 12c+)

MySQL

NO (per OP's question)

PostgreSQL

No for Postgres 10 or earlier
Yes for Postgres 11 currently in Beta

Microsoft SQL Server

Mostly no, when UNION ALL is implemented by a Concatenation operator. Although not documented (or guaranteed), Concatenation has been observed to always read from its inputs in sequence. Each input may be processed using multiple threads of execution. There may be a period of time where threads from different instances are reading from different inputs as processing moves from one input to the next.
Somewhat yes, when UNION ALL is implemented by a Concatenation operator and the inputs reside on different servers (Open() calls are performed asynchronously - Async Concat operator). How much processing occurs during Open() depends on plan shape.
Mostly yes when UNION or UNION ALL is implemented by a Merge Join Concatenation operator, since each instance (thread) reads from any input as needed to perform an order-preserving union.

Are there other "under the hood" sorts of optimizations?

There are several approaches found in literature regarding common subexpression identification: detecting the same expression in two or more union subqueries. Having done so, you can either use the following equivelence: (A join B join C) union (A join B join D) -> A join B join (C union D) to push union before joins when you estimate that this plan will be cheaper, or use methods from an area known as multi-query optimization (here obviously we have a single query, but we can regard different subqueries as separate queries) in order to reuse an expression. Again the decision to reuse an expression is not trivial, as doing so often requires materializing the intermediate result and can lead to much worse execution times. Also, in case that several complex common expressions exist in the subqueries, deciding which one should be materialized (or even which subexpression of an expression) affects the decisions for the rest, making the search space during optimization doubly exponential. In any case, examining these issues heavily affects optimization time, and although there are promising research prototypes, I believe that most commercial products do not consider this kind of optimization. Also note that using such methods results in the whole query being "less-parallelizable", as you can no longer execute each subquery completely independently.


Prefix scale for UTL_MATCH.JARO_WINKLER_SIMILARITY This is a repeat of a previous question from several years ago that did not receive an answer.

Oracle has a string matching package, UTL_MATCH, which contains the subprogram JARO_WINKLER_SIMILARITY.

Jaro Winkler Similarity is a variant of the Jaro Distance, but includes a prefix scale factor to account for common characters at the start of two strings being matched. I cannot find any documentation for the default prefix scale in the UTL_MATCH package, or how to set this manually. Does anyone know of any documentation on this?

You are right that the Oracle documentation does not mention a prefix scale factor. This leads me to conclude that what Oracle is offering is a simpler version of the formula where there is no option to choose a prefix length. There is no other Oracle documentation on this that I know of and it looks unchanged from 10g when it was first implemented.
Even if there was a hidden implementation it would not be advisable to use it in production code as there is no guarantee of support or that it will remain available.
From the Wikipedia reference you gave it looks like you could create your own function which implements the Jaro Winkler similarity formula exactly.


MySQL Junction Table as Foreign Key? I have several tables: company, customer, company_has_customer, and transaction.

company_has_customer is a many-to-many junction table using the PKs of company and customer as its PK.

In almost all situations throughout the database, both the company and customer are used to identify a record (e.g. in the transaction table).

My question is whether I should create the foreign key in the transaction table to point to company_has_customer or to the two tables (company, customer) individually?

I believe it would be best for referential integrity to send those FKs through company_has_customer to ensure that transaction records are only inserted if there is a relationship between the company and the customer.  Are there any standards or conventions to support my hunch?

My second question is doing the lookup from transaction to the customer table if the FK is actually going through the company_has_customer table.  There would still be an index, but should I always have to join through the company_has_customer table?

It depends on what you want to enforce. If a transaction can take place between a customer and a company that is not in company_has_customer the individual foreign keys are preferable. If a transaction only can take place between a customer and a company that is in company_has_customer it is better to have a foreign key to that table.


Replacing the removed database diagrams feature I have a SQL Server database and want to create an ER diagram for it, to analyze tables and relationships.

The option to create a diagram is no longer present in SSMS 18.

Is there any free tool that would help me to create such a diagram, or another workaround?

Update to the latest version of SSMS. The database diagrams feature was reinstated starting with version 18.1.


Fetching wrong results on comparing a variable with 0 in unix I have the following code in unix

 #!/bin/bash
 user=$1
 username=$user'%'
 DB_name=$2
 userpwd=$4
 export PGPASSWORD=$3

 psql -h localhost -U postgres -d $DB_name -At -c "SELECT COUNT(*) AS USER_COUNT FROM pg_roles WHERE rolname = '${user}' " \
| while read -a Record ;
do
USER_COUNT=${Record[0]}
echo 'user USER_COUNT ' $USER_COUNT
done

echo 'count' $count
if   [[ $(($USER_COUNT)) -eq 0 ]]
then
echo 'New User ' $user
else
echo 'User Exists ' $user

fi

It basically accepts a given username and checks if it exists in Postgresql.
After we pass the arguments, it checks its count, which will be 1, if the username exists, otherwise 0, and stores it in the variable USER_COUNT.
It then compares it with 0.
But the comparison is fetching me wrong results.
Regardless of whether a username exists or not, it only runs the 'True' part of if.

Edit-1 The reason I used if [[ $(($USER_COUNT)) -eq 0 ]] instead of
if [$USER_COUNT -eq 0 ], as suggested by Phill, is because I was getting an error message otherwise, saying unary operator expected. I read somewhere that it is because $USER_COUNT is an unset variable.

Edit-2 Thanks for your suggestion Lennart. As suggested by him, I added -x to my first line and found that even though the value of $USER_COUNT is 1 when the user exists, and 0 otherwise, but while comparison, i.e in the step
if [[ $(($USER_COUNT)) -eq 0 ]], $USER_COUNT takes the value 0, even if earlier it was 1. Hence, it always evaluates to true.
But I am not sure what to do to resolve the above situation.

Edit-3 Thanks a lot Lennart. Your code solved my problem.

You may consider something like:

#!/bin/bash
...
cnt=$(psql -h localhost -U postgres -d $DB_name -At -c "SELECT COUNT(*) AS USER_COUNT FROM pg_roles WHERE rolname = '${user}' ")

if [[ ${cnt} -eq 0 ]]
then
    echo 'New User ' $user
else
    echo 'User Exists ' $user
fi

The error you got saying unary operator expected is indeed because the variable is unset. There are various ways to handle that, but in this case, I think it should be fine


Convert a DATETIME value using string Timezone Info? I have a column which has date, time, and timezone info like this:

Sat, 20 Aug 2016 03:50:04 CDT
Sun, 17 Sep 2017 16:51:46 CEST
Mon, 09 Oct 2017 06:43:27 CET
Thu, 28 Sep 2017 14:05:23 IST
Thu, 05 Oct 2017 08:59:01 IDT
Wed, 28 Jun 2017 17:10:22 CEST
Mon, 18 Sep 2017 18:32:52 GMT
Sat, 27 Aug 2016 07:06:22 MEST
Sat, 06 Aug 2016 13:17:09 UTC
Sat, 16 Jul 2016 23:53:02 PDT

I want to normalize these all to UTC.

I can strip off the leading 5 chars, and trailing timezone string, and they cast to datetime just fine... but my question is:

Does SQL Server have any built in function where I can pass in the datetime, the source timezone string e.g. PDT and a target timezone string e.g. UTC and it will handle the conversion?

I am thinking of something similar to what Oracle has:

FROM_TZ(YOUR_TIMESTAMP, 'UTC') AT TIME ZONE 'America/Dawson_Creek'

I am hoping to be able to do it without having to maintain a timezone offsets table to map the string values to an offset. I also don't want to hard-code the offsets in some big case-statement.

I am on sql server 2014, so I can't use the new select * from sys.time_zone_info for timezone info that comes in 2016.

Any thoughts?

If your time zones are IANA time zones and not Windows time zones, then you should be able to use Matt Johnson's "SQL Server Time Zone Support" project, hosted on GitHub at:

https://github.com/mj1856/SqlServerTimeZoneSupport

It is a pure T-SQL solution.


SQL Server: update trigger fires before insert trigger I have an application with a SQL Server-hosted database. I do not have access to the application code, but I do have full access to the database. I have added my own custom auditing to a table to assist in debugging.

I'm using after triggers. Below are simplified versions of my triggers.

Question: I am seeing update audit records that precede the corresponding insert audit records. How is this possible? The difference is only a few miliseconds and doesn't matter for my current purposes, but I can imagine much worse scenarios where program logic depends on the correct chronology.

I know about the ways to control trigger execution order among triggers of the same kind (all insert or all update). What assumptions can I make about heterogeneous trigger execution order?

create trigger dbo.MyTrigger_i on dbo.theTable
after insert
as
begin
    set nocount on
    declare @Date datetime, @User sysname
    set @Date = GETDATE()
    set @User = SUSER_SNAME()
    insert into MyAudit (RowID, [Date], UserName, Comment)
            select i.ID, @Date, @User, 'Insert'
            from
                inserted as i
end
go

create trigger dbo.MyTrigger_u on dbo.theTable
after update
as
begin
    set nocount on
    declare @Date datetime, @User sysname
    set @Date = GETDATE()
    set @User = SUSER_SNAME()
    insert into MyAudit (RowID, [Date], UserName, Comment)
            select
                i.ID, @Date, @User, 'Update'
            from
                inserted as i
                inner join deleted as d
                    on i.ID = d.ID
end
go

Considering that a) Triggers are naturally part of the Transaction that is the DML statement that fired the Trigger, and b) an UPDATE cannot happen on a row until the row exists, it is impossible for the actual UPDATE to show up before the actual INSERT. Hence, something else is going on.

Things to consider:

Are the Trigger definitions shown in the question the actual and current definitions? Is it possible that the Comment values of "Insert" and "Update" are switched in the Triggers such that the INSERT Trigger has the comment of "Update" and vice-versa?
Is it possible that the UPDATE Trigger is somehow defined as being AFTER INSERT, UPDATE ? If so, an INSERT operation would fire both triggers making it look like both an INSERT and an UPDATE happened when in fact there was no UPDATE operation (which might also explain why the times are only a few milliseconds apart for the audit entries).
Is it possible that your query to determine "corresponding" records is flawed and that the results are misleading?


Copy table form MS-SQL server to MySQL, is it possible? I have a procedure, say "proc1", inside a MS-SQL server.
I would like to create a table (copy of proc1) inside a MySQL server.

So far what I do is this:

1-Create a connection to MS-SQL Server
2- run the code $sql = "exec [procedureName] '$param' ";
3-Traverse all 500.000 lines.
4-Store all lines inside a php variable (had to set ini_set('memory_limit','1024M');).
5-Close the connection.
6-Store the data inside a file.
7-Create a table inside a Mysql server, says MySQL_tbl1.
8-Traverse the file, line by line, Sanitize each field -> escape special character and insert inside MySQL_tl1.
Note: I've tried some variations like insert in one shoot. It improve my code only in 10 seconds.

If I was dealing with a MySQL table to generate another table inside MySQL I could use INSERT INTO ... SELECT. . I think this could be the faster way to create a new table inside the MySQL server.
But I am dealing MS-SQL to MySQL.
So my question is that:
Is it possible to use INSERT INTO ... SELECT in this situation? If yes, how?

Just add a note:
I would like to do this using code => (php+Mysql)

The answer is simple... MySQL WorkBench!  Enables migrations from the following systems.

SQL Server, Access, PostgreSQL, SyBase, etc...
Manage migration projects
Source & Target selection
Object migration
Version upgrades

Here's the link... http://www.mysql.com/products/workbench/migrate/

Price is good too! ;-)

-Tony


What's better here - a single column or multicolumn primary key? Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to another group.

When trying to decide on a primary key for the item table, what should I use?

Should I make up an artificial global serial key like this:

CREATE TABLE items
(
    item serial PRIMARY KEY,
    group integer NOT NULL REFERENCES groups(group),
);

... or should I rather use a composite key and per-group item serial like this:

CREATE TABLE items
(
    group integer NOT NULL REFERENCES groups(group),
    item integer NOT NULL,

    PRIMARY KEY(group, item)
);

The reason why I'm leaning more towards the second solution is that the post URL will always show the group and item, so it makes sense to have both of them as the composite primary key. In case of the first solution, the URL contains superfluous information because the group ID can already be deduced from the item ID alone. The URL structure is given, however, and cannot be changed.

The disadvantage of the second solution is that you have to manage a per-group serial (i.e. each item integer should start from 0 for each group).

What's better in terms of best practices, normalization and performance? Or is it simply a matter of taste?

Much of this is a matter of taste and style. And more importantly: specific requirements and consistent conventions. However, there are good reasons for this generic advice:

CREATE TABLE item (
    item_id serial PRIMARY KEY,
    grp_id  integer NOT NULL REFERENCES grp(grp_id)
);

If you have an item_id, better make it unique and ideally a surrogate primary key without any specific meaning for the number itself. That's cheap and versatile. Per-group (not per-row) serials are typically expensive, pointless effort. If you desire a gap-less numbering per group, add some kind of rank column or use row_number() in a view. And don't rely on a gap-less numbering if you can avoid it. Sooner or later you'll have to delete or move around items.
It is convenient for maintenance to have a single column primary key that follows a naming convention (unless you have an obvious natural pk).
Don't use group as identifier. It is a reserved word. Even if these are just symbolic names.
If you have queries by grp_id, add another index:

CREATE INDEX item_foo_idx ON item (grp_id, item_id);

You might want to make it a UNIQUE INDEX for documentation, but that's redundant since item_id is unique already. With two integer columns, a multicolumn index is just as big as an index on just grp_id and there seem to be use cases where you need these two columns, so there is the chance for a covering index. This should be the optimum. More rationale here:
Is a composite index also good for queries on the first field?


Performance problem caused by joining with the result of a scalar function? In the following example the performance of the first version using only one SQL query is very bad. The problem is the last join.

When I store the result of the left side into a temporary table and join this, it runs about 50 times quicker.

It is intended to convert this to Oracle, therefore I try to avoid the use of temporary tables.

Any explanations, why the first query behaves so bad or hint to improve it?

Declare @HO int = 2866;
Declare @Dtz int = 35;

---- version 1 takes about 60 seconds
With ratings as
(
    select
        ROW_NUMBER() Over ( ORDER BY SEDate) lfd,
        SEDate,
        BBCode
    from  PPV
        join BB on BBRefnr = SEBBRefnr
    where SEHORefnr = @HORefnr
    and SEBBRefnr > 0
)
Select
    SEDATE,
    BBCODE,
    Code,
    DTRefnr
FROM (
Select
    l.SEDate,
    l.BBCode,
    dbo.GetCode(@Dtz, l.BBCode) Code
from ratings l left join ratings r on l.lfd = r.lfd + 1
where l.BBCode <> r.BBCode
or r.BBCode is null
) as t
    join DT d on code = d.DTCode and d.DTDTKRefnr = @Dtz
order by 1;

------- version 2 using a temp table takes less than 1 second

With ratings as
(
    select
        ROW_NUMBER() Over ( ORDER BY SEDate) lfd,
        SEDate,
        BBCode
    from  PPV
        join BB on BBRefnr = SEBBRefnr
    where SEHORefnr = @HORefnr
    and SEBBRefnr > 0
)
Select
    l.SEDate,
    l.BBCode,
    dbo.GetCode(@Dtz, l.BBCode) Code
into #tmp
from ratings l left join ratings r on l.lfd = r.lfd + 1
where l.BBCode <> r.BBCode
or r.BBCode is null

Select
    SEDATE,
    BBCODE,
    Code,
    DTRefnr
FROM #tmp
    join DT d on code = d.DTCode and d.DTDTKRefnr = @Dtz
order by 1;

EDIT:

Here the function:

Create function dbo.GetCode (
    @fDtz int,
    @Value varchar(10)
) returns varchar(10)
as
begin
    declare @CODE varchar(10)
    SET @Code =
        (SELECT Code FROM Ableitungen
        WHERE DTZ = @fDtz
        AND Value = @Value )

    return @CODE
end

Exactly as expected.

A scalar UDF is a black box to the optimiser: no indexes can be used, the cost can't be worked out correctly.

And if the scalar UDF has table access, then you have running a CURSOR (bad in SQL Server) to do a lookup per row: it isn't a set based operation

To fix it, don't use a UDF this way. It can be written as a JOIN. There is no hint or magic

Edit: remove the UDF. For each row in ratings your query Ableitungen. It's exponential

And I've move the filters into the JOINs

With ratings as
(
    select
        ROW_NUMBER() Over (ORDER BY SEDate) lfd,
        SEDate,
        BBCode
    from  PPV
        join BB on BBRefnr = SEBBRefnr
    where SEHORefnr = @HORefnr
    and SEBBRefnr > 0
)
Select
    l.SEDate,
    l.BBCode,
    A.Code,
    D.DTRefnr
from
    ratings l
    left join
    ratings r on l.lfd = r.lfd + 1
    JOIN
    Ableitungen A ON A.Value = l.BBCode
    join
    DT d on A.code = d.DTCode and d.DTDTKRefnr = A.DTZ
where
    A.DTZ = @fDtz
    AND
   (l.BBCode <> r.BBCode or r.BBCode is null)
order by
    SEDATE;


Using DTA vs. evaluating DMVs? Currently I am confronted with a production SQL Server database where someone had added almost all missing index proposals from DTA.

Form How to determine if an Index is required or necessary I have learned, that there are DMV views, which can be used to defer information about actual index usage as well as missing indexes.

The script from Jason Strate only uses the current information from that views, while Fun for the day - Automated Auto-Indexing! saves some information from those views into tables.

DTA on the other side requires planning when to run the profiler, select what to profile and has some impact on performance while running.

My impression is that using DTA as first step tuning tool is simply a waste of time and as its results don't cover the complete database usage, are hard to interpret by novices and can lead to adding too much indexes with negative impact on write performance, while Data collection via DMV requires little preparation and covers nearly the whole usage of the database since the last restart.

My question focuses on which strategy to propose to the management.
I want to focus on evaluation the DMV views in the first step and ignore DTA completely.

DTA helps for adding indexes but not removing indexes.  If you're faced with a database that already has more than, say, 5 indexes per table, the DTA isn't the answer.  You'll want to use the DMVs to evaluate usage of existing indexes.

Here's a script with a video tutorial I did a while back:

http://www.toadworld.com/platforms/sql-server/w/wiki/10062.find-indexes-not-in-use.aspx


Migrating from one SQL Server 2008 R2 instance to another I just started a new dba job and have been given a new server and told to move everything from the old server to the new one, brand new windows and SQL Server installations. I know I have to transfer user databases, logins, SSIS packages and maintenance plans.

Is there anything else I should be aware of to move. Both instances are SQL Server 2008 R2.
Is there an easier way to move the maintenance plans other than scripting each one and applying these to the new server?

Thanks for your help.

This should solve your problem:

http://msdn.microsoft.com/en-us/library/ms345408.aspx

That link has instructions to move system databases from one instance to another, if you move all databases as is (including user ones, for which you can copy the file, however you will need downtime, or if your databases are on a SAN you can use a SAN snapshotting software), the SQL Server will come up with everything intact.. Keep in mind that if you have a named instaces (NAME1\NAME2) then the NAME2 should be same for the destination instance..


Increase Max Memory setting on a SQL Server Production I have a production box with 5 instances on.

Version: SQL Server 2014 SP3 Enterprise.

I discovered that even though the machine has ~400GB memory assigned, the combined MAX MEMORY settings on all 5 instances is less than 200GB.

The instances don't suffer from memory pressure, but as we already have that memory assigned, it's a waste not to use it.

I would like to increase it to higher value, leaving 10% for the OS (some instances will be assigned higher than others).

However, I've never done such a large increase of this setting before.

I know it’s a dynamic settings which doesn’t require a restart, however, I'm wondering on the following:

Should I make the increase in 2 phases (i.e increase half the amount, wait a week and then increase it again )?
Can something suddenly break (or make a performance degradation) if SQL server suddenly has so much more memory to play with ?

We don't have lock pages in memory setting enabled, and we don't have traceflag 834 on.

I have a slightly different opinion.

If you are not experiencing memory pressure and your application is just working fine, why introduce an unknown variable (in your case - increasing sql server max memory) ? You have not tested this change, so its a risk.

If you have more bandwidth on your server, you can look into consolidating more instances on your server.

  Can something suddenly break (or make a performance degradation) if SQL server suddenly has so much more memory to play with ?

Yes, and I faced a perf degradation issue with PROD having more memory and using TF 2335 fixed the issue.

you can read Paul's answer for more details but the gist is :

  The potential size of the buffer pool also affects the optimizer's cost model for data access.


SQL Server update query on linked server causing remote scan I have a SQL Server 2012 setup as a linked server on a SQL Server 2008 server.

The following queries executes in less than 1 second:

   SELECT kg.IdGarment
   FROM Products p
   INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID
   INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID
   INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID
   INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment
   INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID
   WHERE log.ActionType = 'I'

t_ProcessIT_Garment contains 37,000 rows, the query returns two records, the IdGarment column is the Primary Key. No problem here.

However, if I run this query to do a remote update, it takes 24 seconds, and 2 rows is affected:

   UPDATE [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment SET
   IdGarment = IdGarment
   FROM Products p
   INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID
   INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID
   INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID
   INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment
   INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID
   WHERE log.ActionType = 'I' ;

I tested using IdGarment = IdGarment to keep things simple. The execution plan shows it uses Remote Query for the first query, but Remote Scan for the second query, which has 100% of the cost.

The table joins are identical in both queries, why is it using Remote Scan for the second query, and how do I fix this?

A friend just had a similar problem and rewriting the query solved it.

I know this is an old question, and likely you have worked it out, but for anyone else you could try this and report back...

UPDATE kg
SET
   IdGarment = IdGarment
   FROM Products p
   INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID
   INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID
   INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID
   INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment
   INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID
   WHERE log.ActionType = 'I' ;

I don't know why this helped, but using the UPDATE "alias" form of the statement meant that the whole UPDATE query was remoted, rather than pulling the data back to the local instance to perform the JOINs.

If anyone has any ideas why this behaviour was seen, I would love to know.


SQL Server LCK_M_X lock from .NET application We've recently enabled READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION on our SQL Server 2014 database, which is accessed primarily from 2 versions our ASP.NET application via ADO.NET.

The new version (still in beta) is working fine, while our legacy (currently still in production) version is exhibiting a peculiar problem inside a particular API.

This API opens a DB transaction and executes a number of SELECTs, INSERTs, and UPDATEs over a set of tables (between 3 and 5, depending on the exact request).

The problem is, the API gets stuck when executed by the legacy version of the app. Inspection by Activity Monitor shows that there are two processes with distinct Session IDs (which correspond to two separate DB connections, right?), one of which is trying to execute an UPDATE, while the other is doing nothing else but blocking the first one.

My question is: am I correct in assuming that the legacy version of our app has some sort of bug that causes the API request to use two separate connections in parallel (possibly one for reading and one for updating), which then trigger the lock we're observing?

If not, what else could possibly explain the behavior we're seeing?

What's bothering me is the fact that the new snapshot isolation level should not cause ANY locks between parallel transactions. At least that's how I understand it. I'd be happy to be shown otherwise!

FWIW, killing the inactive session allows the UPDATE to succeed without a problem. Without that, they both remain "hung" forever. The problem also didn't occur (to my knowledge, at least) before we enabled the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION options.

It turned out we had a bug that sometimes caused a connection with a still active transaction to leak out of the pool without being properly disposed. It was this transaction that caused locks down the road. After fixing the leak, the problem is now gone!

The READ_COMMITTED_SNAPSHOT change was a red herring.


PostGIS 1.5 Error with PostgreSQL 8.4 I am trying to add PostGIS functionality to a database that I created in PostgreSQL using the following command: psql -d <db_name> -f /location/to/this/file/postgis.sql

However, I receive a bunch of errors relating to 'geometry does not exist', but on line 1 of these error messages, it says ERROR: could not access file “$libdir/postgis-1.5”.

I Google'd and checked StackOverflow, and it seems like this error is related to dependencies or scripts not being installed. I installed PostGIS using yum install postgis84. I did not see any dependencies except for proj. Based on these instructions, it seems like I will need Proj4, GEOS, LibXML2, GDAL, JSON-C, etc.

Is there a repository that I can connect to that will let me install PostGIS with all the crucial dependencies rather than installing them one by one? I am running a Red Hat Enterprise Linux 6.5 server on Amazon EC2.

I am new to databases, so please excuse me if I used any incorrect lingo.

I solved it. I found the RPM's for GDAL, GEOS, and Proj4. They are located here: http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/

Since they are not packaged with PostgreSQL 8.4, you have to install them one by one. I hope this helps someone in future.


can I add a custom SSRS e-mail subscription that forces the "TO" address I would like to know if it is possible to add a custom delivery extension(?) in SSRS that will enable me to force a value into the "TO" address for particular subscriptions?

Big picture: I am exploring ways to schedule reports that would only permit them to be sent to a specific email address.

EDIT: Somewhat related, I would also like to know if I can create a second e-mail delivery extension without having do any programming.  i.e. I would copy the extension details from RSReportServer.config for the "Report Server Email", give it a new name, and change a single configuration value.

SECOND EDIT: In the hopes of getting some additional input and ideas, the big BIG picture is I want to create a method to create an SSRS subscription that would guarantee the recipient is a particular e-mail address.  This method should be usable by all users, but even knowing how to let one user might be workable.  Subscriptions to other e-mail addresses must still be permitted.

What version of SQL Server/SSRS are you using? If you have SQL Server Enterprise then you can use Data Driven Subscriptions which allows you to use a query to define the various subscription delivery parameters such as To, CC, Subject etc.


Foreign Key constraint on fixed value field - Ever appropriate? I have a short (15 rows) look-up table that lists the valid values for several columns in the database.
I don't think it can be considered a One-True-Lookup-Table, it is about a single definite concept, but it may conceptually be partitioned in some subgroups (3).
I now happen to have to add a column that actually needs to accept only the values from one of these subgroups.

The proper thing to do now would probably to make one table for each subgroup and turn the original table into a simple list of IDs, from which the subgroup tables take their primary ids from.

It is though very unlikely that I ever further need to refer to the subgroups, I will instead frequently use things for which all the values in the original table are valid.

So the partitioning option would make things more complex for the vast part of the application just to support one case.

The only other option I know of is to add a Type column to the original lookup table and a fixed-value column to the single table that need to refer to the subgroup, and use a two-columns foreign key (to ID + Type) in this single case.

This is very ugly, and I'm not sure if it is theoretically correct to use columns that are not the primary key as foreign key referents, but given the context, is it acceptable, or even the best way?

The proper thing to do now would probably to make one table for each subgroup ...

Probably.

  It is though very unlikely that I ever further need to refer to the subgroups...

You probably said something very similar when you made that first questionable design decision.  There's a reason that normalization never asks the question, "How often will I need to refer to whatever?"

If you don't enjoy fixing this kind of problem over and over, do it right today and get on with more useful and fun work.


How to fetch the string from the column of table in postgresql? I have the following table

Userid  District_code
1        05
1        03
2        03
2        04
3        06

and want to get this output:

userid=1 dist_code(05,03)

SELECT 'userid=' || Userid::text
     , 'dist_code(' || string_agg(District_code::text, ',') || ')'
FROM table
GROUP BY Userid
-- WHERE userid=1


Problem with compare value with its aggregate function within the same table I need to find the list of customer_id for customer who have spent more than the average customer.

This is the the query I use to generate table above:

SELECT customer_id, sum(Total) AS customer_total
FROM (SELECT T.order_id, customer_id, Total
              FROM (SELECT order_id, SUM(quantity * B.price) AS Total
                    FROM ORDERITEM AS O, BOOK AS B
                    WHERE B.b_id = O.bid
                    GROUP BY O.order_id) AS A JOIN "TRANSACTION" AS T
                          ON A.order_id = T.order_id)
GROUP BY customer_id
ORDER BY customer_total DESC

How can I start from here to find the list of customers who have spent more than the average customer? And also how to give the limit of digits for double? Thanks in advance!

Below is the query I tried, but it returns empty. And I am sure the correct result is not empty.

Just use a common table expression:

WITH CustTotals AS ( ... your query above ...)
SELECT *
FROM CustTotals
WHERE customer_total > (SELECT AVG(customer_total)
                        FROM CustTotals);


FOR XML is generating a empty first line I'm parsing with flash a XML file generated by this code:

:XML ON
USE MyDatabaseName
GO
SET NOCOUNT ON

SELECT * FROM ProgramacionDia as programa order by hora
FOR XML AUTO,
ROOT ('toflash'),
ELEMENTS

SET NOCOUNT OFF

But I get a XML file with the first line empty. Removing this first empty line in the generated XML works ok with flash, but with the generated XML no.

How can I remove that line? Is my script wrong? I have no much idea about this code.

I'm running SQL Server 9.0.

After a bit of trial and error, I found this is caused by the USE statement occuring after XML mode is turned on. I have no idea why; I assume it's simply an artifact of sqlcmd processing.

Here are three solutions I came up with from playing around with the code:

Remove the USE completely, and specify the database name on the command line using the -d parameter instead.

:XML ON

SET NOCOUNT ON

SELECT * FROM ProgramacionDia as programa order by hora
    FOR XML AUTO, ROOT ('toflash'), ELEMENTS

SET NOCOUNT OFF

Remove the USE completely, and three-part-name the table.

:XML ON

SET NOCOUNT ON

SELECT * FROM [MyDatabaseName]..[ProgramacionDia] as programa order by hora
    FOR XML AUTO, ROOT ('toflash'), ELEMENTS

SET NOCOUNT OFF

Move the USE statement above the :XML ON directive, and delete the GO (this will suppress the "Changed database context to 'MyDatabaseName'" message in the output). I have no idea why this also fixes the issue.

USE MyDatabaseName

:XML ON

SET NOCOUNT ON

SELECT * FROM ProgramacionDia as programa order by hora
    FOR XML AUTO, ROOT ('toflash'), ELEMENTS

SET NOCOUNT OFF

Depending on what your actual script looks like, one of these options should be adaptable for you.