How do I create a user-defined aggregate function in MySQL (redux)?

OK, it's almost working; the only thing that is wrong is I evidently don't know how to coerce the constant argument args->args[1] to (any) type of int value. If I code target_depth to a fixed integer, the program works. It's only when I try to read it in from the query that I get the "ERROR 2013 (HY000): Lost connection to MySQL server during query" error. Any help with reading in the target depth for UDF_SMALL (2nd lowest value, 3rd lowest value, etc.) would be greatly appreciated.

/******************************************************************************
 ** udf_small.c
 **
 ** This MySQL user-defined function (UDF) sorts through an aggregate dataset,
 ** then returns the ith smallest element each group in the GROUP BY statement.
 **
 ** Author:        Jeffrey Rolland
 ** Creation Date: 12/07/2014
 ** Modifications: None
 ******************************************************************************/

/******************************************************************************
** A dynamically loadable file should be compiled shared.
** (something like: gcc -shared -o my_func.so -I /usr/includes/mysql/ my_func.c).
** You can easily get all switches right by doing:
** cd sql ; make udf_example.o
** Take the compile line that make writes, remove the '-c' near the end of
** the line and add -shared -o udf_example.so to the end of the compile line.
** The resulting library (udf_example.so) should be copied to some dir
** searched by ld. (/usr/lib/mysql/plugin/ ?)
** If you are using gcc, then you should be able to create the udf_example.so
** by simply doing 'make udf_example.so'.
**
** After the library is made one must notify mysqld about the new
** functions with the commands:
**
** CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";
** CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so";
** CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so";
** CREATE FUNCTION sequence RETURNS INTEGER SONAME "udf_example.so";
** CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
** CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so";
** CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME "udf_example.so";
** CREATE FUNCTION myfunc_argument_name RETURNS STRING SONAME "udf_example.so";
**
** After this the functions will work exactly like native MySQL functions.
** Functions should be created only once.
**
** The functions can be deleted by:
**
** DROP FUNCTION metaphon;
** DROP FUNCTION myfunc_double;
** DROP FUNCTION myfunc_int;
** DROP FUNCTION lookup;
** DROP FUNCTION reverse_lookup;
** DROP FUNCTION avgcost;
** DROP FUNCTION myfunc_argument_name;
**
** The CREATE FUNCTION and DROP FUNCTION update the func@mysql table. All
** Active function will be reloaded on every restart of server
** (if --skip-grant-tables is not given)
**
** If you ge problems with undefined symbols when loading the shared
** library, you should verify that mysqld is compiled with the -rdynamic
** option.
**
** If you can't get AGGREGATES to work, check that you have the column
** 'type' in the mysql.func table. If not, run 'mysql_upgrade'.
**
**************************************************************************/

/*************************************************************************
** Syntax for the new aggregate commands are:
** CREATE AGGREGATE FUNCTION <function_name> RETURNS {STRING|REAL|INTEGER}
** SONAME <name_of_shared_library>
**
** Syntax for avgcost: AVGCOST( t.quantity, t.price )
** with t.quantity=integer, t.price=double
**
** (This example is provided by Andreas F. Bobak <bobak@relog.ch>)
**************************************************************************/

#ifdef STANDARD
    /* STANDARD is defined, don't use any mysql functions */
    #include <stdlib.h>
    #include <stdio.h>
    #include <string.h>
    #ifdef __WIN__
        typedef unsigned __int64 ulonglong; /* Microsofts 64 bit types */
        typedef __int64 longlong;
    #else
        typedef unsigned long long ulonglong;
        typedef long long longlong;
    #endif /*__WIN__*/
#else
    #include <my_global.h>
    #include <my_sys.h>
    #if defined(MYSQL_SERVER)
        #include <m_string.h>   /* To get strmov() */
    #else
        /* when compiled as standalone */
        #include <string.h>
        #define strmov(a,b) stpcpy(a,b)
        #define bzero(a,b) memset(a,0,b)
    #endif
#endif
#include <mysql.h>
#include <ctype.h>
#ifdef _WIN32
    /* inet_aton needs winsock library */
    #pragma comment(lib, "ws2_32")
#endif
#ifdef HAVE_DLOPEN
    #if !defined(HAVE_GETHOSTBYADDR_R) || !defined(HAVE_SOLARIS_STYLE_GETHOST)
        static pthread_mutex_t LOCK_hostname;
    #endif
#endif
#include <stdbool.h>

my_bool udf_small_init( UDF_INIT* initid, UDF_ARGS* args, char* message );
void udf_small_deinit( UDF_INIT* initid );
void udf_small_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
void udf_small_clear( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
void udf_small_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
double udf_small( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );

struct SortArray
{
    ulonglong length;
    double *data;
};

/*************************************************************************
** Example of init function
** Arguments:
** my_bool maybe_null 1 if function can return NULL
** Default value is 1 if any of the arguments
** is declared maybe_null
**
**
** initid Points to a structure that the init function should fill.
** This argument is given to all other functions.
**
** unsigned int decimals Number of decimals.
** Default value is max decimals in any of the
** arguments.
**
** unsigned int max_length Length of string result.
** The default value for integer functions is 21
** The default value for real functions is 13+
** default number of decimals.
** The default value for string functions is
** the longest string argument.
**
** char *ptr; A pointer that the function can use.
**
**
** args Points to a structure which contains:
** unsigned int arg_count Number of arguments
**
** enum Item_result *arg_type Types for each argument.
** Types are STRING_RESULT, REAL_RESULT
** and INT_RESULT.
**
** char **args Pointer to constant arguments.
** Contains 0 for not constant argument.
**
** unsigned long *lengths; max string length for each argument
** char *maybe_null Information of which arguments
** may be NULL
**
**
** message Error message that should be passed to the user on fail.
** The message buffer is MYSQL_ERRMSG_SIZE big, but one should
** try to keep the error message less than 80 bytes long!
**
** This function should return 1 if something goes wrong. In this case
** message should contain something usefull!
**************************************************************************/
my_bool udf_small_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
{
    struct SortArray *sort = NULL;
    ulonglong target_depth;
    ulonglong i;

    if (args->arg_count != 2)
    {
        strcpy(message, "Wrong number of arguments: SMALL() requires two arguments");
        return 1;
    }
    if ((args->arg_type[0] != REAL_RESULT) || (args->arg_type[1] != INT_RESULT) )
    {
        strcpy(message, "Wrong argument type: SMALL() requires an REAL and a INT");
        return 1;
    }
    if (*((longlong*)args->args[1]) < 1)
    {
        strcpy(message, "Wrong argument value: SMALL() requires second parameter that is positive");
        return 1;
    }

    initid->maybe_null = 0; /* The result may be null */
    initid->decimals = 10; /* We want 10 decimals in the result */
    /* initid->max_length = 10; */ /* 10 digits + . + 10 decimals */

    target_depth = *((longlong*)args->args[1]);

    sort = malloc((sizeof(struct SortArray)));

    if(sort == NULL)
    {
        strcpy(message, "Couldn't allocate memory");
        return 1;
    }

    initid->ptr = (char*)sort;

    sort->length = 0;

    sort->data = malloc((target_depth)*(sizeof(double)));

    if(sort->data == NULL)
    {
        strcpy(message, "Couldn't allocate memory");
        return 1;
    }

    for(i = 0; i < target_depth; i++)
    {
        sort->data[i] = 999999999;
    }

    return 0;
}

/****************************************************************************
** Deinit function. This should free all resources allocated by
** this function.
**
** Arguments:
** initid Return value from xxxx_init
****************************************************************************/
void udf_small_deinit( UDF_INIT* initid )
{
    free(initid->ptr);
}

/****************************************************************************
** Small Aggregate Function.
**
** There are 3 extra functions for an aggregate function, xxx_reset,
** xxx_clear, and xxx_add
****************************************************************************/

/****************************************************************************
** xxx_reset
**
** Arguments:
** initid Structure filled by xxx_init
**
** args The same structure as to xxx_init. This structure
** contains values for all parameters.
** Note that the functions MUST check and convert all
** to the type it wants! Null values are represented by
** a NULL pointer
**
** is_null If the result is null, one should store 1 here.
**
** message Error message that should be passed to the user on fail.
** The message buffer is MYSQL_ERRMSG_SIZE big, but one should
** try to keep the error message less than 80 bytes long!.
**
** xxx_reset gets done every time we begin processing the records for a new
** group. It calls xxx_clear and xxx_add
****************************************************************************/

/* This is only for MySQL 4.0 compability */
void udf_small_reset(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message)
{
    udf_small_clear(initid, args, is_null, message);
    udf_small_add(initid, args, is_null, message);
}

/****************************************************************************
** xxx_clear
**
** Arguments:
** initid Structure filled by xxx_init
**
** args The same structure as to xxx_init. This structure
** contains values for all parameters.
** Note that the functions MUST check and convert all
** to the type it wants! Null values are represented by
** a NULL pointer
**
** is_null If the result is null, one should store 1 here.
**
** message Error message that should be passed to the user on fail.
** The message buffer is MYSQL_ERRMSG_SIZE big, but one should
** try to keep the error message less than 80 bytes long!.
**
** xxx_clear resets the processing variables to their initial values for a
** new group
****************************************************************************/

/* This is needed to get things to work in MySQL 4.1.1 and above */
void udf_small_clear(UDF_INIT* initid, UDF_ARGS* args, char* is_null __attribute__((unused)), char* message __attribute__((unused)))
{
    struct SortArray *sort = NULL;
    ulonglong target_depth;
    ulonglong i;

    sort = (struct SortArray*)initid->ptr;

    target_depth = *((longlong*)args->args[1]);

    /* if(sort == NULL)
    {
        strcpy(message, "Initid Pointer is NULL");
        exit(1);
    }

    if(sort->data == NULL)
    {
        strcpy(message, "Couldn't allocate memory");
        exit(1);
    } */

    sort->length = 0;

    for(i = 0; i < target_depth; i++)
    {
        sort->data[i] = 999999999;
    }
}

/****************************************************************************
** xxx_add
**
** Arguments:
** initid Structure filled by xxx_init
**
** args The same structure as to xxx_init. This structure
** contains values for all parameters.
** Note that the functions MUST check and convert all
** to the type it wants! Null values are represented by
** a NULL pointer
**
** is_null If the result is null, one should store 1 here.
**
** message Error message that should be passed to the user on fail.
** The message buffer is MYSQL_ERRMSG_SIZE big, but one should
** try to keep the error message less than 80 bytes long!.
**
** xxx_add is the main processing workhorse of a anggregate UDF. It processes
** each new record in the group.
****************************************************************************/

void udf_small_add(UDF_INIT* initid, UDF_ARGS* args, char* is_null __attribute__((unused)), char* message __attribute__((unused)))
{
    if (args->args[0] && args->args[1])
    {
        struct SortArray *sort = NULL;
        ulonglong target_depth;
        double col_value;
        ulonglong counter;
        ulonglong i, j;
        bool done;

        sort = (struct SortArray*)initid->ptr;

        target_depth = *((longlong*)args->args[1]);

        col_value = *((double*)args->args[0]);

        /* if(sort == NULL)
        {
            strcpy(message, "Initid pointer is NULL");
            exit(1);
        } */

        i = target_depth;
        counter = 1;
        done = FALSE;

        /* sort->data[0] = col_value + 1.0;
        sort->data[1] = col_value;

        col_value = 0.05; */

        while ((!(done)) && (i > 0))
        {
            if(col_value <= sort->data[i-1])
            {
                for(j = 0; j < i-1; j++)
                {
                    sort->data[j] = sort->data[j+1];
                }
                sort->data[i-1] = col_value;
                done = TRUE;
            }
            else
            {
                i--;
                counter++;
            }
        }

        /* if(counter >= sort->length)
        {
            sort->length = counter;
        } */

    }
}

/***************************************************************************
** UDF double function.
**
** Arguments:
** initid Structure filled by xxx_init
**
** args The same structure as to xxx_init. This structure
** contains values for all parameters.
** Note that the functions MUST check and convert all
** to the type it wants! Null values are represented by
** a NULL pointer
**
** is_null If the result is null, one should store 1 here.
**
** error If something goes fatally wrong one should store 1 here.
**
** This function should return a double. It returns a value at the end of
** each group.
***************************************************************************/

double udf_small( UDF_INIT* initid, UDF_ARGS* args __attribute__((unused)), char* is_null, char* error __attribute__((unused)))
{
    struct SortArray *sort = NULL;
    ulonglong target_depth;

    sort = (struct SortArray*)initid->ptr;
    target_depth = *((longlong*)args->args[1]);

    sort->length = target_depth; /* Kill me once working */

    if(sort != NULL)
    {
        if(sort->length == target_depth)
        {
            *is_null = 0;
            return sort->data[0];
        }
    }
    else
    {
        *is_null = 1;
        return  10.0;
    }
}


This can be related to Bug 83912. If you run SHOW ENGINE INNODB STATUS you may see transactions like this:

  ---TRANSACTION 4216198446592, not started sleeping before entering InnoDB

In order to fix it:

SET GLOBAL innodb_thread_concurrency=0;


Yes, it is going to be counterproductive. You can set it slightly higher than CPU count, maybe 2*CPU count, but not 12.5*CPU count. You do not want 500 connection to execute at the same time, CPU-bound. you only want to use as many  CPUs as you got.


MySQL single table static and dynamic pivot

If you have a known number of columns, then you can use a static version similar to the other answer. But if you have an unknown number then you can use a prepared statement similar to this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when kode = ''',
      kode,
      ''' then jum else 0 end) AS ',
      kode
    )
  ) INTO @sql
FROM yourtable;

SET @sql = CONCAT('SELECT name, ', @sql, ', sum(jum) as `count`
                  FROM yourtable
                  GROUP BY name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with demo

Result is the same:

| NAME | KODE1 | KODE2 | COUNT |
--------------------------------
| aman |     2 |     1 |     3 |
| amir |     0 |     4 |     4 |
| jhon |     4 |     0 |     4 |


Fixing parameter-sniffing of an embedded query via Recompile

If @UID is NULL then the query cannot be executed with a seek. That means in order to get a seek you need the value of the variable to not be NULL and you need the parameter embedding optimization to be applied to the query. In other words, the optimizer needs to see the value of the variable before creating the plan. Usually this can be accomplished with a RECOMPILE hint but not always. The final example of the linked blog shows how the parameter embedding optimization cannot be applied when assigning a value to a variable.

We can also see hints of that behavior by running your example queries. For query A I get the following as a seek predicate:

Seek Keys[1]: Prefix: [SE_DB].[dbo].[Entries].UID = Scalar Operator((100))

However, for query B I get the following predicate:

[@UID] IS NULL OR [SE_DB].[dbo].[Entries].[UID]=[@UID]

The @UID reference was not replaced with 100 for query B so we must get a scan.

We can also see the difference in the query trees created for optimization with undocumented trace flag 8606:

Query A is on the left and query B is on the right. For query A, the query optimizer builds the query plan using the literal value of the variable. For query B, the query optimizer builds the plan only knowing that a variable was used.

There are plenty of workarounds available that don't require dynamic SQL or branching code. You can add up the results of two subqueries:

SELECT @cnt =
(
    select count(1) cnt
    from Entries
    where @UID is null
) +
(
    select count(1) cnt
    from Entries
    where @UID is not null and UID = @UID
);

You can use UNION ALL:

SELECT @cnt = SUM(cnt)
FROM
(
    select count(1) cnt
    from Entries
    where @UID is null

    UNION ALL

    select count(1) cnt
    from Entries
    where @UID is not null and UID = @UID
) t
option(recompile);

You can also insert into a temp table and grab the value from the temp table:

select count(1) cnt INTO #t
from Entries
where (@UID is null or UID = @UID)
option(recompile);

SELECT @cnt = MAX(cnt) FROM #t;

DROP TABLE #t;


Check if any of the values is in a subquery result

Try:

where exists (select * ....
        where Customers.orderId = ...
        or Customers.secondaryId = ...
     )

Eg, if you were planning on:

where orderId in (select value from ...)
or secondaryorderid in (select value from ...)

Then you make it so that you only call your subquery once, and build your OR clause into it.

 where exists (select * from ...
        where Customers.orderId = value
        or Customers.secondaryOrderId = value
     )

The whole point of this is to ensure that the complicated subquery is only executed one time. That doesn't happen with a CTE, or by replacing two INs with two EXISTSs.


I see the list of all the operations that can trigger a scan but none of them are happening on my machine, is there a way to tell what causes the scan?

It's a CHECKPOINT on database ID 8.  It says so right in the message.
Do you have INDIRECT CHECKPOINT enabled?  What does
select database_id , name, target_recovery_time_in_seconds
from sys.databases
where database_id = 8

return?  The default of 60 enables indirect checkpoints.


Searching 500 million records of binary data

You should consider using a real indexed search engine such as elasticsearch with runs on Java, uses a REST interface with json and so is exceedingly easy to program to, is free and open source, with a good community behind it.

www.elasticsearch.org

You could have it running on the same server as your application as long as you have a decent enough machine and it's built specifically for searching hundreds of millions of documents in near real time.

Pretty easy to install and setup and extremely easy to customize.  It also has the added benefit of being built to run in the cloud (though not a requirement) and can cluster out very easily when your application begins to grow in popularity.


The example is provided in the answer in the linked question. The author of that answer - and example - states that (Pizza, Topping Type) is a candidate key of the relation - ther's also discussion in the comments below the answer.

So the example has the added restriction that every pizza has not more than one toppings of any type.

This may not be your usual definition of a pizza but with this assumption - even if we assume the common sense dependency that Topping -> Topping Type - the relation is indeed in (2NF and) 3NF.


Problem with database migrated from SQL Server 2005 to SQL Server 2014

Any idea of the reason? What problems can I have when lowering the compatibility level?

SQL 2014 made substantial changes to the "Cardinality Estimator" in the query optimizer.  For applications that were built and optimized using the older behavior, you may see a performance regression.

The workaround you selected of using the lower database compatibility level is fine.  You can also use a Trace Flag 9481 to force the legacy cardinality estimator on newer database compatibility levels.

Eventually you should analyze and optimize the queries and indexes under the new cardinality estimator.

See, eg: SQL Server 2014’s new cardinality estimator (Part 1)


Using pg_dump to create 144 backups per day

That is silly You should instead take one pg_basebackup per day and archive the transaction log with archive_command. That way. you can use point-in-time-recovery to restore the database to any given point in time.

See the documentation for more.


"Just" 300? That might not really be a problem, actually. How many rows do you expect in the table? If not that many, say, less than a million, then doing that might not be a huge problem.

Or if you're using PostgreSQL, as noted, you can use an array of booleans. That, I think, would be preferrable to putting each column individually in the schema.

The "best" way, however, would be a bitfield. Say, an array of a hundred 4-byte integers, with each value being one of the bits in one of those fields.  So your application could have a map of tupes, something like:

has["dog"] = (25, 18)

Noting that 'dog' would be in the 26th field (zero based), in the 19th bit. When you want to query if a record has a dog, you'd need to pull the appropriate tuple and construct an SQL statement like...

(fieldNum, bitNum) = has["dog"] # python syntax

SELECt * from tbl WHERE (has[fieldNum] & (1<<bitNum)) > 0

Finally, Postgres also has bit strings, which I've never used, but it looks like they might do the job as well. http://www.postgresql.org/docs/9.4/interactive/datatype-bit.html


How do I configure SQL Profiler to show me queries to the views sys.databases and sys.tables?

There's nothing inherent in Profiler that would prevent those queries from being displayed. I think you're just not catching the right events. It sounds like you're filtering for Stored Procedure events. Try also filtering for TSQL events.


Getting notification when log truncation is delayed

I'd approach this from a different tack and monitor for long running transactions.  You can query open transactions using the DMVs (sys.dm_tran_active_transactions should show it).  Here's a query (borrowed and modified from Gail Shaw) that will show you currently running transactions:

SELECT
    ec.connect_time,
    tat.transaction_begin_time,
    tat.transaction_type,
    tat.transaction_state,
    es.host_name,
    es.program_name,
    tst.session_id,
    es.login_name,
    est.text
FROM sys.dm_tran_session_transactions tst
    INNER JOIN sys.dm_tran_active_transactions tat ON tst.transaction_id = tat.transaction_id
    INNER JOIN sys.dm_exec_connections ec ON tst.session_id = ec.session_id
    INNER JOIN sys.dm_exec_sessions es ON tst.session_id = es.session_id
    CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) est

From here, I'd set up a polling job against the DMVs and alert me if a transaction is held open for longer than an acceptable period of time.  What this amount of time is will be dependent on your environment.


How to order by alphanumeric codes in SQL Server

In case you are interested on running this kind of sort repeatedly, or on a larger dataset, you may want to consider adding a key table to control the sort order, and JOINing to that table.

First, we setup the test environment:

USE tempdb;
CREATE TABLE dbo.K
(   KOD VARCHAR(255) NOT NULL CONSTRAINT PK_K
        PRIMARY KEY CLUSTERED
);

CREATE TABLE dbo.KSort
(
    KODSort INT NOT NULL CONSTRAINT PK_KSort
        PRIMARY KEY CLUSTERED
    , KODPrefix VARCHAR(255) NOT NULL
);

INSERT INTO dbo.K (KOD)
VALUES ('Z15001')
    , ('Z15002')
    , ('B001'  )
    , ('B002'  )
    , ('C001'  )
    , ('C002'  )
    , ('Z14099')
    , ('Z14098')
    , ('Z14097')
    , ('Z13099')
    , ('Z13098')
    , ('Z13001');

INSERT INTO dbo.KSort(KODSort, KODPrefix)
VALUES (10, 'Z15%')
    , (20, 'B%')
    , (30, 'C%')
    , (40, 'Z14%')
    , (50, 'Z13%');

SELECT K.*
FROM dbo.K;

This shows the expected result, which just happens to be sorted in order of the clustering key:

Now, two queries, the first with the standard ORDER BY clause to sort the results "by hand".

SELECT *
FROM dbo.K
ORDER BY
    CASE
        WHEN Kod LIKE 'Z15%' THEN 1
        WHEN Kod LIKE 'B%' THEN 2
        WHEN Kod LIKE 'C%' THEN 3
        WHEN Kod LIKE 'Z14%' THEN 4
        WHEN Kod LIKE 'Z13%' THEN 5
        ELSE 6
    END ASC;

The plan for this is:

Nice and simple.  The "Sort" operator is taking 77.6% of the plan cost.

If we use a JOIN to accomplish our sort instead, like so:

SELECT K.*
FROM dbo.K
    INNER JOIN dbo.KSort ON K.KOD LIKE KSort.KODPrefix
ORDER BY KSort.KODSort, K.KOD;

We start with a much simpler piece of code, which appeals to me on a deep level; however we also see an interesting execution plan:

Instead of a sort operator, we now have several nested loop joins.

When dealing with a small amount of data, the difference is inconsequential once we have the data in SQL Server's buffer.

However, lets see what happens when we add more rows to the KOD table.

TRUNCATE TABLE dbo.K;

INSERT INTO dbo.K(KOD)
SELECT TOP(1000000) CASE ROW_NUMBER() OVER (ORDER BY o.object_id) % 5
   WHEN 0 THEN 'Z15'
   WHEN 1 THEN 'B'
   WHEN 2 THEN 'C'
   WHEN 3 THEN 'Z14'
   WHEN 4 THEN 'Z13' END
    + CONVERT(VARCHAR(255), ROW_NUMBER() OVER (ORDER BY o.object_id))
FROM sys.objects o, sys.objects o1, sys.columns c;

(The above adds 1,000,000 rows to the table)

On my machine, which is a 4-core Intel I7 with 16GB RAM, I see the following with SET STATISTICS IO,TIME ON; configured:

The "standard" sort:

(1000000 row(s) affected)
Table 'K'. Scan count 9, logical reads 2679, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1094 ms,  elapsed time = 6463 ms.

The "JOIN":

(1000000 row(s) affected)
Table 'K'. Scan count 5, logical reads 2641, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'KSort'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 437 ms,  elapsed time = 5475 ms.

I used DBCC FREEPROCCACHE before starting each query, the results are very similar across multiple runs.  Typically the standard sort is 2 to 3 times slower than the JOIN version.

Above-and-beyond performance implications, using the key-table I propose (dbo.KSort) allows you to change the sort order of the output at will without having to ever modify code, which is a pretty nice bonus, in my opinion.  You simply modify the values of the KODSort column so the results are returned in whatever order you like.  For instance, the following UPDATE would make the Z14* rows display prior to the B* rows:

UPDATE KSort SET KODSort = 15 WHERE KODSort = 40;


How do I convert a TIMESTAMP to a BIGINT in Azure Synapse

Slightly more compact and only one call to CURRENT_TIMESTAMP, you can use CONVERT with its format switch (121) and TRANSLATE and REPLACE to tidy up.  This reduces the statement from six individual function calls to four.  You can also use FORMAT, which is only two function calls:
SELECT
    CURRENT_TIMESTAMP ts,
    REPLACE( TRANSLATE( CONVERT( CHAR(23), CURRENT_TIMESTAMP, 121 ), '-:.', '   ' ), ' ', '' ) c1,
    FORMAT( CURRENT_TIMESTAMP , 'yyyyMMddHHmmssfff' ) c2

I would query the need to store these values however.  You probably should be storing a conventional timestamp for event records so why also store this form of timestamp in an encoded fashion which you have to unpack if you want to use?  Logging to a table with an identity column has a similar effect.  Something to think about.


FCI with Alwayson Availability Group (Asymmetric Storage)

Can you try adding it under another drive letter, and then changing the drive letter via Disk Management. It's what was suggested by Microsoft during the CTPs.


The best way to do this is:

Let us call your original table Original.

Create two new tables Jan and Feb) matching the schema of Original. You might want to use the partition management tool for this: https://sqlpartitionmgmt.codeplex.com/. Place these new tables directly on the filegroup you want to end up in. Do NOT place them on a partition scheme.
Add a check constraint to Jan limiting the data to only the data in January 2015
Do the same for Feb, but limiting data to February
Script and drop index on Jan and Feb
Use INSERT Jan WITH (TABLOCK)... SELECT Original WHERE Month = 'January' to populate Jan. This is bulk logged, and hence, should
run at around 40-80MB/sec with minimal impact on the transaction log
As above, for February.
Add indexes back on Jan and Feb
Switch out the old January/February partition from Original to an empty table. Original now has no 2015 records (validate this)
SPLIT the January partition function of Original on the February/January boundary (and any other months you want to prepare for)
ALTER TABLE Jan SWITCH TO Original PARTITION x where x is the partition number of January
ALTER TABLE Feb SWITCH TO Original PARTITION y, where y is the partition number of February
Profit

This is the fastest way to achieve the result with minimal impact on the disk system and transaction log.

It is rather complicated, but learning Partition Fu tricks like these are key to getting the best out of SQL Server partitioning.


configure remote connection for multiple instances sql server 2008

You need to start the SQL Browser service to enable connections to named instances.

Explanation here:

  Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL
  Server Browser reads the registry, identifies all instances of SQL
  Server on the computer, and notes the ports and named pipes that they
  use. When a server has two or more network cards, SQL Server Browser
  returns the first enabled port it encounters for SQL Server. SQL
  Server Browser support ipv6 and ipv4.


Today's date is 2016-02-11 and this query returns 2016-02-11 00:00:00.000:

SELECT curDay = DATEADD(day, DATEDIFF(dd, 0, getdate()), 0)

It can then be included into the DATEADD statement:

SELECT curDay = GETDATE()
    , DATEADD(day, 1 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Monday
    , DATEADD(day, 2 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Tuesday
    , DATEADD(day, 3 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as wednesday
    , DATEADD(day, 4 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Thursday
    , DATEADD(day, 5 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Friday

This will give this output:

curDay                  | Monday                    | Tuesday                   | Wednesday                 | Thursday                  | Friday
2016-02-11 11:54:24.947 | 2016-02-08 00:00:00.000   | 2016-02-09 00:00:00.000   | 2016-02-10 00:00:00.000   | 2016-02-11 00:00:00.000   | 2016-02-12 00:00:00.000

This query returns Sunday's date (2016-02-06 this week):

SELECT Sunday = DATEADD(day, DATEDIFF(dd, 0, getdate()) - DATEPART(weekday, GETDATE()), 0)

It can be used with this query and it gives the same output:

SELECT curDay
    , DATEADD(day, 1, Sunday) as Monday
    , DATEADD(day, 2, Sunday) as Tuesday
    , DATEADD(day, 3, Sunday) as wednesday
    , DATEADD(day, 4, Sunday) as Thursday
    , DATEADD(day, 5, Sunday) as Friday
FROM (SELECT Sunday = DATEADD(day, DATEDIFF(dd, 0, getdate()) - DATEPART(weekday, GETDATE()), 0)) as cd

For records with a date on Monday, this query can be used:

SELECT ...
FROM ...
WHERE colDate >= DATEADD(day, 1 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0)
    AND colDate < DATEADD(day, 2 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0)

It outputs rows where the date is bigger or equal to Monday at 00:00 and before Tuesday at 00:00.

It is better to use date >= '20160101 00:00' and date < '20160102 00:00' than BETWEEN. See: Why does my query search datetime not match?

Full sample (SQL Fiddle):

CREATE TABLE test(id int, [date] datetime)

INSERT INTO test values(1, '2016-02-07 23:23')
INSERT INTO test values(2, '2016-02-08 01:01')
INSERT INTO test values(3, '2016-02-08 12:00')
INSERT INTO test values(4, '2016-02-08 23:23')
INSERT INTO test values(5, '2016-02-09 00:00')

DECLARE @date datetime = '2016-02-11 12:34'
set datefirst 1

-- Query 1
SELECT id, [date]
    , IntegerColumn = CASE WHEN [date] >= DATEADD(day, 1 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0)
            AND [date] < DATEADD(day, 2 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0)
        THEN IntegerColumn
        ELSE 0
    END
FROM test

-- Query 2
SELECT id, [date], IntegerColumn
FROM test
WHERE [date] >= DATEADD(day, 1 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0)
    AND [date] < DATEADD(day, 2 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0)

Query 1:

id  | date                      | IntegerColumn
1   | 2016-02-07 23:23:00.000   | 0
2   | 2016-02-08 01:01:00.000   | 2
3   | 2016-02-08 12:00:00.000   | 3
4   | 2016-02-08 23:23:00.000   | 4
5   | 2016-02-09 00:00:00.000   | 0

Query 2:

id  | date                      | IntegerColumn
2   | 2016-02-08 01:01:00.000   | 2
3   | 2016-02-08 12:00:00.000   | 3
4   | 2016-02-08 23:23:00.000   | 4


An ALTER TABLE operation that adds a column to a table should be a simple data dictionary only update.  There is no reason for Oracle to visit all the blocks/rows in the table.  So, the operation should be very fast.  (sub-second response time.)

The only exception to this would be, if you're on a version of Oracle prior to 11g, and you're setting a default value for the column being added, Oracle has to visit all the rows.  In 11g, even setting a default value will not precipitate a visit to all the rows in the table.


Seems like the DBA created a logon trigger that verifies connection on logins. ORA-20001-20999 are custom exceptions defined by the code that throws them, this is an environmental issue, ask the DBA.

If you have the necessary privilegs, you can check the trigger yourself, you can start with:

select * from dba_triggers where triggering_event like 'LOGON%';


If you want to put yesterday's date into the variable then do it during assignment. And if you want a varchar, then declare it as one - your current code does an implicit and useless conversion back a varchar back to a date anyway.

So something like:

CREATE OR REPLACE procedure create_table_dateWise27()
LANGUAGE plpgsql
as $$
declare
  d_date varchar;
begin

  d_date := to_char(current_date - 1, 'yyyy_mm_dd'); --<< yesterday

  EXECUTE format('
    CREATE TABLE IF NOT EXISTS %I(
      nodeip text,
      interface text,
      nodeip_interface text
    )','test_'|| d_date);

  commit;
end;
$$;

Note that I changed the format mask for the to_char() call to use underscores (_)  rather than - because a dash is invalid in an SQL identifier (table name) and thus would require the use of a quoted identifier which you should really avoid.


http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmconc1.htm#BRADV111

If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically
backs up the control file and the current server parameter file (if
used to start up the database) in one of two circumstances: when a
successful backup must be recorded in the RMAN repository, and when a
structural change to the database affects the contents of the control
file which therefore must be backed up.
This means that the control file is backed up in the following
situations:

After every BACKUP command issued at the RMAN prompt.
At the end of a RUN block, if the last command in the block was BACKUP.
Whenever a BACKUP command within a RUN block is followed by a command that is not BACKUP.

In your case, the backup is created before RELEASE CHANNEL. The autobackup would happen after COPY, BACKUP ... ARCHIVELOG and BACKUP ... CONTROLFILE, but since these are in the same RUN block, why perform the work of creating the autobackup 3 times unnecessarily, when it is enough to perform the autobackup after the last BACKUP command?
The size is different because the autobackup contains the SPFILE also.
The autobackup finishes after the controlfile backup, so the autobackup contains the entry pointing to the previous controlfile backup, but the previous controlfile backup doesn't know about the autobackup, that is another difference between them.
When the controlfile grows, it gets more space allocated than it immediately needs, leaving some empty space in it that can be used later. Because of this, the controlfile and the controlfile autobackup can be the same size (when no SPFILE in use), but their content will be still different. Outdated entries can also be reused in the controlfile to prevent the controlfile growing too big.

COPY overwrites the copies of datafiles. DELETE NOPROMPT BACKUP OF ARCHIVELOG UNTIL TIME='sysdate-6' deletes archivelog backups older than 6 days.


Inserting values into a column when Table1 column A value matches Table 2 Column B Value

Taking a guess that Vinny wishes to update the PurchaseInfo table, and that SupplierInfo is same as ImportData

UPDATE PurchaseInfo
SET [Supplier Contact Name] = S.[Supplier Contact Name]
FROM ImportData I JOIN PurchaseInfo PI ON PI.[Supplier Name] = I.SupplierName


What are the downsides of using PARTITION for multi-tenant access?

10k stores so we're talking about 10GB of data. One possibility that came up is to create partitions for each store so they will be completely isolated. It seems ideal for our use case, but we don't have experience with this many partitions so I don't know if there are gotchas.

10,000 partitions is insanity. Partitions are done for performance not for security. Their use has planner, index, and underlying file-system implications. There is absolutely no reason to ever do that. You can however create 10,000 users, or authenticate against LDAP and manage security like that. For more information see this, and examples.

https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html

Ultimately, I probably wouldn't do either unless I absolutely needed too. It's easier to design abstracted secure app-level interfaces (database-models/middleware etc) to authenticate and to fly with that. I've seen row-level permissions used in production code, but typically that's for a class of data access.

For example. Perhaps your application has two levels of access one for unauthenticated forward-facing web servers; and, one for on-premise access. In this case, you can ensure with pg_hba that admins are on site (or are otherwise hackers clever enough to have compromised on-site equipment).


I can't give the credit to Sir Swears-A-lot because his response was in a comment, but he was right.  My assumption that dropping the primary key and creating a new one would be effectively the same or more expensive than loading up an empty new table was incorrect.

Setting recovery mode simple, dropping the primary key, recreating it (forcing the reshuffle), and adding all my new indexes afterwards clocked in at 40 minutes.  That beat any bcp/SSIS method I'd tried by more than a half hour, so that was a big improvement.

And the transaction log grew by 10 gig but didn't go nuts like some of my other experiments.


Is it possible to Clone a RedShift Database?

You can do the following:

UNLOAD your data to S3 (http://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html)
Run your deployment scripts against the new database
COPY the data into the new database (http://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html)

This (UNLOAD/COPY) will be fairly quick for a development size dataset.


Two-phase commit for distributed transactions: what if a commit failed?

Server 2 is obliged by its duties under the 2PC to contact the coordinator for advice on the state of the transaction.  Until it gets a Commit or Rollback from the coordinator, it must hold the transaction in the 'Ready to Commit' state, locks and all — even if it is restarted.

This could go on indefinitely, of course, so in practice there are 'Presumed Fail' or 'Presumed Succeed' heuristics that can be applied, but it is not something to undertake lightly.  If you get into a heuristic operation, then you probably do have an inconsistent set of databases — which is just one reason not to go 'heuristic' in a hurry.  Of course, if the Coordinator or the network are out of commission for any extended period of time, you probably have other problems than just database consistency.

You can find out a lot from reading Concurrency Control and Recovery in Database Systems by Philip A. Bernstein, Vassos Hadzilacos, Nathan Goodman (available for download).  That is tough going, though.  You could look in Date (Database Fundamentals, 8th Edn), but it is covered very briefly there, or Recovery Mechanisms in Database Systems by Kumar and Hsu, which has a substantial discussion of 2PC in chapter 13.


Strategies for long term data growth optimization for a query with an index on a joined table

It is not inevitable that it will keep using the seq scan.  Perhaps at some point it will change (indeed, if the tables are vacuumed and analyzed, I don't get the seq scan in the first place, as the anonymous horse indicates.)
However, if your tables keep growing into the future (rather than into the past) your query will count more and more rows, and this will take longer and longer to do.
Since you are using fake data anyway, why not just use more of it and see what happens?  This is the key benefit of working with simulated data sets, you can simulate whatever you want.
With 9.6, I get a nested loop over two index scans as long as the exchange_content table is well-vacuumed enough that the index-only scan seems worthwhile.  So I would say that the main strategy you have overlooked is to design your indexes so that you can get index-only scans, and make sure to vacuum often enough. For insert only/mostly tables, it can be hard to get autovac to vacuum often enough, (until v13 introduces autovacuum_vacuum_insert_scale_factor) and so until then it might be best accomplished by "manually" VACUUMing from cron jobs.
Moving the created_at column to the other table would be an obvious winner if you were using an inner join rather than left join (at which point, you could drop the join with exchange altogether and just query exchange_content). But if the left join is really needed, it is not clear how this could work. Also, assuming that exchange_content is not where the column naturally belongs, then moving/copying it there would be a denormalization.  This generally comes at a high cost from a integrity and maintenance perspective even if it does pay off for SELECT performance.
It is not clear how partitioning could help here.  You can't partition exchange_content by created_at without having the created_at column exist in that table.  And if it did exist in that table, you wouldn't need partitioning you could just index it directly.


SQL Server native log backup running every 5 minutes

Will the next log backup be initiated after 5 minutes or will it be waiting for the log backup to complete.

If the log backup is run by SQL Server Agent
If the scheduled time comes up and the prior instance of the job is still running, it will postpone until the next scheduled time. Two instances of the same job can not run at the same time.
If the log backup is run by some other scheduler software
You'll have to check with your vendor's documentation or support team, but I can't imagine any software allowing this to behave any differently than SQL Server Agent, unless they have an option to not skip collisions and queue them up, and you have enabled that option.


What contributes to the transaction size on a MySQL UPDATE?

The transaction size is not just based on the amount of data you're actually change, rather the whole state of the row before you made the change is preserved in case of a rollback and therefore the entire row is essentially copied. From further research it sounds like as much as even the entire page of rows is copied to the rollback segment, so depending on how many pages contain the rows you're updating, it could easily be a lot more data generated in the transaction then what you're actually updating.


What is the sense and benefit of using SqlCommand.Prepare()?

Preparing a SQL batch separately from executing the prepared SQL batch is a construct that is effectively** useless for SQL Server given how execution plans are cached. Separating out the steps of preparation (parsing, binding any parameters, and compiling) and execution only makes sense when there is no caching. The purpose is to save the time spent on parsing and compiling by reusing an existing plan, and this is what the internal plan cache does. But not all RDBMS's do this level of caching (clearly from the existence of these functions) and so it is left up to the client code to request that a plan be "cached", and so save that cache ID, and then re-use it. This is additional burden on the client code (and programmer to remember to do it and get it right) that is unnecessary. In fact, the MSDN page for the IDbCommand.Prepare() method states:

  The server automatically caches plans for reuse as necessary; therefore, there is no need to call this method directly in your client application.

It should be noted that, as far as my testing shows (which matches what you show in the Question), calling SqlCommand.Prepare(), does not do a "prepare"-only operation: it calls sp_prepexec which prepares and executes the SQL; it does not call sp_prepare which is parse and compile only (and does not take in any parameter values, only their names and datatypes). Hence, there cannot be any "pre-compile" benefit of calling SqlCommand.Prepare since it does an immediate execution (assuming the goal is to defer execution). HOWEVER, there's an interesting potential minor benefit of calling SqlCommand.Prepare(), even if it does call sp_prepexec instead of sp_prepare. Please see the note (**) at the bottom for details.

My testing shows that even when SqlCommand.Prepare() is called (and please note that this call does not issue any commands on SQL Server), the ExecuteNonQuery or ExecuteReader that follows is fully executed, and if it is ExecuteReader, it returns rows. Still, SQL Server Profiler does show that the first SqlCommand.Execute______() call after the SqlCommand.Prepare() call registers as a "Prepare SQL" event, and subsequent .Execute___() calls register as "Exec Prepared SQL" events.

Test Code

It has been uploaded to PasteBin at: http://pastebin.com/Yc2Tfvup.
The code creates a .NET / C# Console App that is intended to run while a SQL Server Profiler trace is running (or an Extended Events session). It pauses after each step so it will be clear which statements have a particular effect.

UPDATE

Found more info, and a slight potential reason to avoid calling SqlCommand.Prepare(). One thing I noticed in my testing, and what should be noted for anyone running that test Console App: there is never an explicit call made sp_unprepare. I did some digging and found the following post in the MSDN forums:

SqlCommand - Prepare or not to prepare?

The accepted answer contains a bunch of info, but the salient points are:

** What prepare actually saves you is primarily the time it takes to transmit the query string over the wire.
A prepared query has no guarantee of a cached plan being saved, and is no faster than an ad-hoc query once it reaches the server.
RPC's (CommandType.StoredProcedure) gain nothing from preparing.
On the server, a prepared handle is basically an index into an in-memory map containing TSQL to execute.
The map is stored on a per-connection basis, no cross-connection use is possible.
The reset sent when the client re-uses the connection from the pool clears the map.

I also found the following post from the SQLCAT team, which appears to be related, but could simply be an issue specific to ODBC whereas SqlClient does clean up correctly upon disposing of the SqlConnection. It is hard to say since the SQLCAT post does not mention any additional tests that would help prove this as a cause, such as clearing the connection pool, etc.

Watch out those prepared SQL statements

CONCLUSION

Given that:

the only real benefit of calling SqlCommand.Prepare() seems to be that you don't need to submit the query text again over the network,
calling sp_prepare and sp_prepexec store the query text as part of the connection's memory (i.e. SQL Server memory)

I would recommend against calling SqlCommand.Prepare() because the only potential benefit is saving network packets while the downside is taking up more server memory. While the amount of memory consumed is probably very small in the majority of cases, network bandwidth is rarely ever an issue since most DB servers are directly connected to the app servers over 10 Megabit minimum (more likely 100 Megabit or Gigabit these days) connections (and some are even on the same box ;-). That and memory is nearly always a more scarce resource than network bandwidth.

I suppose, for anyone writing software that can connect to multiple databases, then the convenience of a standard interface might change this cost/benefit analysis. But even in that case, I have to believe that it is still easy enough to abstract a "standard" DB interface that allows for different providers (and hence differences between them, such as not needing to call Prepare() ) given that doing so is basic Object Oriented programming that you are likely already doing in your app code ;-).


Trying to compare several similiar lists on one table to produce a concatenated result

JOIN is not that effective, because you need to access the table 4 times. With PIVOT you can do it with 1 table access.

create table list_of_stuff
(
  list_id number(5,0),
  item varchar2(40 char)
);

insert all
  into list_of_stuff values (47, 'Baseball')
  into list_of_stuff values (47, 'Hat')
  into list_of_stuff values (47, 'Gloves')
  into list_of_stuff values (47, 'Sparkle Shoes')
  into list_of_stuff values (91, 'Baseball')
  into list_of_stuff values (91, 'Sparkle Shoes')
  into list_of_stuff values (91, 'Bubble Gum')
  into list_of_stuff values (91, 'Hat')
  into list_of_stuff values (91, 'Fish Tank')
  into list_of_stuff values (91, 'Book of Spiders')
  into list_of_stuff values (103, 'Silver Spoon')
  into list_of_stuff values (103, 'Fiddle')
  into list_of_stuff values (103, 'Trumpet')
  into list_of_stuff values (103, 'Paper Towels')
  into list_of_stuff values (103, 'Fireworks')
  into list_of_stuff values (103, 'Mosquito Netting')
  into list_of_stuff values (103, 'Cure for the Common Cold')
  into list_of_stuff values (295, 'Baseball')
  into list_of_stuff values (295, 'Fireworks')
  into list_of_stuff values (295, 'Trumpet')
  into list_of_stuff values (295, 'Bubble Gum')
  into list_of_stuff values (295, 'Hat')
  into list_of_stuff values (295, 'Gloves')
  into list_of_stuff values (295, 'Christmas Tree')
select * from dual;

commit;

JOIN version:

SQL> set null "(null)"
SQL> r
  1  select *
  2  from
  3                  (select * from list_of_stuff where list_id = 47)  L1
  4  full outer join (select * from list_of_stuff where list_id = 91)  L2 using (item)
  5  full outer join (select * from list_of_stuff where list_id = 103) L3 using (item)
  6  full outer join (select * from list_of_stuff where list_id = 295) L4 using (item)
  7  order by item
  8*

ITEM                              LIST_ID    LIST_ID    LIST_ID    LIST_ID
------------------------------ ---------- ---------- ---------- ----------
Baseball                               47         91 (null)            295
Book of Spiders                (null)             91 (null)     (null)
Bubble Gum                     (null)             91 (null)            295
Christmas Tree                 (null)     (null)     (null)            295
Cure for the Common Cold       (null)     (null)            103 (null)
Fiddle                         (null)     (null)            103 (null)
Fireworks                      (null)     (null)            103        295
Fish Tank                      (null)             91 (null)     (null)
Gloves                                 47 (null)     (null)            295
Hat                                    47         91 (null)            295
Mosquito Netting               (null)     (null)            103 (null)
Paper Towels                   (null)     (null)            103 (null)
Silver Spoon                   (null)     (null)            103 (null)
Sparkle Shoes                          47         91 (null)     (null)
Trumpet                        (null)     (null)            103        295

This returns a NULL where the item is not on the list, otherwise it returns the list_id. The actual value does not matter, just the fact that it is not null, so providing the desired output:

SQL> r
  1  select
  2    nvl2(L1.list_id, item, null) as LIST_47,
  3    nvl2(L2.list_id, item, null) as LIST_91,
  4    nvl2(L3.list_id, item, null) as LIST_103,
  5    nvl2(L4.list_id, item, null) as LIST_295
  6  from
  7                  (select * from list_of_stuff where list_id = 47)  L1
  8  full outer join (select * from list_of_stuff where list_id = 91)  L2 using (item)
  9  full outer join (select * from list_of_stuff where list_id = 103) L3 using (item)
 10  full outer join (select * from list_of_stuff where list_id = 295) L4 using (item)
 11  order by item
 12*

LIST_47         LIST_91         LIST_103                       LIST_295
--------------- --------------- ------------------------------ ---------------
Baseball        Baseball        (null)                         Baseball
(null)          Book of Spiders (null)                         (null)
(null)          Bubble Gum      (null)                         Bubble Gum
(null)          (null)          (null)                         Christmas Tree
(null)          (null)          Cure for the Common Cold       (null)
(null)          (null)          Fiddle                         (null)
(null)          (null)          Fireworks                      Fireworks
(null)          Fish Tank       (null)                         (null)
Gloves          (null)          (null)                         Gloves
Hat             Hat             (null)                         Hat
(null)          (null)          Mosquito Netting               (null)
(null)          (null)          Paper Towels                   (null)
(null)          (null)          Silver Spoon                   (null)
Sparkle Shoes   Sparkle Shoes   (null)                         (null)
(null)          (null)          Trumpet                        Trumpet

This requires 28 buffer gets on my demo table:

SQL> select * from table(dbms_xplan.display_cursor(format=>'iostats last'));
...

Plan hash value: 912304261

------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |     15 |00:00:00.01 |      28 |
|   1 |  SORT ORDER BY             |               |      1 |     21 |     15 |00:00:00.01 |      28 |
|   2 |   VIEW                     | VW_FOJ_0      |      1 |     21 |     15 |00:00:00.01 |      28 |
|*  3 |    HASH JOIN FULL OUTER    |               |      1 |     21 |     15 |00:00:00.01 |      28 |
|   4 |     VIEW                   |               |      1 |      7 |      7 |00:00:00.01 |       7 |
|*  5 |      TABLE ACCESS FULL     | LIST_OF_STUFF |      1 |      7 |      7 |00:00:00.01 |       7 |
|   6 |     VIEW                   | VW_FOJ_1      |      1 |     13 |     14 |00:00:00.01 |      21 |
|*  7 |      HASH JOIN FULL OUTER  |               |      1 |     13 |     14 |00:00:00.01 |      21 |
|   8 |       VIEW                 | VW_FOJ_2      |      1 |      6 |      7 |00:00:00.01 |      14 |
|*  9 |        HASH JOIN FULL OUTER|               |      1 |      6 |      7 |00:00:00.01 |      14 |
|  10 |         VIEW               |               |      1 |      4 |      4 |00:00:00.01 |       7 |
|* 11 |          TABLE ACCESS FULL | LIST_OF_STUFF |      1 |      4 |      4 |00:00:00.01 |       7 |
|  12 |         VIEW               |               |      1 |      6 |      6 |00:00:00.01 |       7 |
|* 13 |          TABLE ACCESS FULL | LIST_OF_STUFF |      1 |      6 |      6 |00:00:00.01 |       7 |
|  14 |       VIEW                 |               |      1 |      7 |      7 |00:00:00.01 |       7 |
|* 15 |        TABLE ACCESS FULL   | LIST_OF_STUFF |      1 |      7 |      7 |00:00:00.01 |       7 |
------------------------------------------------------------------------------------------------------

Then I have this PIVOT query that puts the number 1 (or I could have written any other value in MIN(), or use another aggregate function) to a list column where the item in the row exists:

SQL> set null "(null)"
SQL> r
  1  select *
  2  from
  3  (
  4    select list_id, item
  5    from   list_of_stuff
  6    where  list_id in (47, 91, 103, 295)
  7  )
  8  pivot (min(1) as in_list for list_id in (47, 91, 103, 295))
  9  order by item
 10*

ITEM                           47_IN_LIST 91_IN_LIST 103_IN_LIST 295_IN_LIST
------------------------------ ---------- ---------- ----------- -----------
Baseball                                1          1 (null)                1
Book of Spiders                (null)              1 (null)      (null)
Bubble Gum                     (null)              1 (null)                1
Christmas Tree                 (null)     (null)     (null)                1
Cure for the Common Cold       (null)     (null)               1 (null)
Fiddle                         (null)     (null)               1 (null)
Fireworks                      (null)     (null)               1           1
Fish Tank                      (null)              1 (null)      (null)
Gloves                                  1 (null)     (null)                1
Hat                                     1          1 (null)                1
Mosquito Netting               (null)     (null)               1 (null)
Paper Towels                   (null)     (null)               1 (null)
Silver Spoon                   (null)     (null)               1 (null)
Sparkle Shoes                           1          1 (null)      (null)
Trumpet                        (null)     (null)               1           1

Similarly, after transforming it to the desired output:

SQL> r
  1  select
  2    nvl2("47_IN_LIST", item, null)  as list_47,
  3    nvl2("91_IN_LIST", item, null)  as list_91,
  4    nvl2("103_IN_LIST", item, null) as list_103,
  5    nvl2("295_IN_LIST", item, null) as list_295
  6  from
  7  (
  8    select *
  9    from
 10    (
 11      select list_id, item
 12      from   list_of_stuff
 13      where  list_id in (47, 91, 103, 295)
 14    )
 15    pivot (min(1) as in_list for list_id in (47, 91, 103, 295))
 16  )
 17  order by item
 18*

LIST_47         LIST_91         LIST_103                       LIST_295
--------------- --------------- ------------------------------ ---------------
Baseball        Baseball        (null)                         Baseball
(null)          Book of Spiders (null)                         (null)
(null)          Bubble Gum      (null)                         Bubble Gum
(null)          (null)          (null)                         Christmas Tree
(null)          (null)          Cure for the Common Cold       (null)
(null)          (null)          Fiddle                         (null)
(null)          (null)          Fireworks                      Fireworks
(null)          Fish Tank       (null)                         (null)
Gloves          (null)          (null)                         Gloves
Hat             Hat             (null)                         Hat
(null)          (null)          Mosquito Netting               (null)
(null)          (null)          Paper Towels                   (null)
(null)          (null)          Silver Spoon                   (null)
Sparkle Shoes   Sparkle Shoes   (null)                         (null)
(null)          (null)          Trumpet                        Trumpet

Much simpler, 1 table access, 7 buffer gets:

SQL> select * from table(dbms_xplan.display_cursor(format=>'iostats last'));

...

Plan hash value: 1664708432

-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |      1 |        |     15 |00:00:00.01 |       7 |
|   1 |  SORT GROUP BY PIVOT|               |      1 |     24 |     15 |00:00:00.01 |       7 |
|*  2 |   TABLE ACCESS FULL | LIST_OF_STUFF |      1 |     24 |     24 |00:00:00.01 |       7 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("LIST_ID"=47 OR "LIST_ID"=91 OR "LIST_ID"=103 OR "LIST_ID"=295))

Now if you don't insist on naming the columns LIST_47, LIST_91, ..., the JOIN version peforms worse, but it can be easily reused, by changing the 4 list_id parameters, you can even use bind variables there. The PIVOT performs better, but if you want to use different list_id values, you need to rewrite the query, because you can not dynamically provide the values for the pivot( ... in (***)). PIVOT needs to know the column names it generates at parse time.


Is there a way to group the output of a query based on two rows

SELECT MAX(co.[slsman])               AS Salesman,
       MAX(co.[cust_num])             AS "Customer Number",
       MAX(ca.NAME)                   AS "Customer name",
       co.[co_num]                    AS "Customer Order",
       ci.item                        AS item,
       MAX(ca.curr_code)              AS curr_code,
       SUM(ci.price * ci.qty_ordered) AS sum_value
FROM   [co] co
       LEFT JOIN custaddr ca
              ON co.cust_num = ca.cust_num
                 AND co.cust_seq = ca.cust_seq
       LEFT JOIN coitem ci
              ON co.co_num = ci.co_num
WHERE  co.co_num LIKE 's%'
       AND co.stat = 'o'
       AND co.cust_seq = '1'
       AND item NOT LIKE '%carriage%'
       AND ci.stat NOT LIKE 'f'
       AND ci.stat NOT LIKE 'c'
GROUP BY  co.co_num,
          ci.item
ORDER  BY Salesman,
          "Customer Number"


select two record containing dates in it with two input dates

Simple answer:

If you want both date columns to be between the input dates:

SELECT category
FROM tablename
WHERE 'start_date' BETWEEN start_date AND end_date
  AND 'end_date' BETWEEN start_date AND end_date ;

Or if you want any of the date columns to be between the input dates:

SELECT category
FROM tablename
WHERE 'start_date' BETWEEN start_date AND end_date
   OR 'end_date' BETWEEN start_date AND end_date ;

Or if you want any of the ranges that are "overlapping with the range of the input dates
(seems the most likely, based on the wanted result):

SELECT category
FROM tablename
WHERE 'start_date' <= end_date
  AND start_date <= 'end_date' ;


Is there any benefit to having one sql server login for multiple users?

While there is no limit on the number of simultaneous connections by a single login, other than the usual @@MAX_CONNECTIONS value, using one login for multiple users or developers is generally frowned on because doing so makes it difficult or impossible to limit permissions on a per-person basis. (You may still be able to identify/contain people based on something like a workstation name, but this would require much hackery on the DBA's part, while simply using seperete logins addresses the issue.) Such "well-known" logins also have a way of getting hard-coded into things, along with the well-known password, and then developers and users become very resistant to ever changing that password. With so many ways to get onto a corporate network these days (a rogue LogMeIn running somewhere, for example) being able to turn database access off via an AD group is beneficial.

Using a single login for a web application, which can have many simultaneous users, is more common. It might not be feasible to give out SQL Server logins or AD logins to individuals for large sites (for example, facebook). In that case, there is a benefit in that IIS can use connection pooling.

I haven't worked in an environment where it was standard operating procedure to use a shared login like that for many years, although there were some smaller/older projects where a shared login was used (and sometimes abused).


Query that Searches Duplicate Values Based on a Specific Value

You can eliminate the duplicates from your query by using the ROW_NUMBER() aggregate:

IF OBJECT_ID('tempdb..#Table') IS NOT NULL
DROP TABLE #table;
CREATE TABLE #Table
(
      ID01 INT NOT NULL
    , ID02 INT NOT NULL
);

INSERT INTO #Table (ID01, ID02)
VALUES (1, 1)
     , (1, 2) --problematic
     , (1, 3) --problematic
     , (1, 4) --problematic
     , (2, 1)
     , (2, 1)
     , (2, 2) --problematic
     , (3, 1)
     , (3, 1)
     , (4, 1);

;WITH cte AS (
    SELECT DISTINCT
          t1_ID01 = t1.ID01
        , t1_ID02 = t1.ID02
        , rn1 = ROW_NUMBER() OVER (PARTITION BY t1.ID01 ORDER BY t1.ID01, T1.ID02)
        , rn2 = ROW_NUMBER() OVER (PARTITION BY t1.ID01, t1.ID02 ORDER BY t1.ID01, T1.ID02)
    FROM #Table t1
    )
SELECT *
FROM cte
WHERE cte.rn1 > 1
    AND cte.rn2 = 1;

The first ROW_NUMBER() function, rn1, is used to select rows where there are multiple ID02 values for each individual ID01 value.  The second ROW_NUMBER() function, rn2, is used to preserve the case where ID01 and ID02 have multiple duplicate values, "which our system already corrects for automatically".

That pattern can be leveraged to remove the invalid rows from the source table, by using the DELETE FROM <cte> syntax:

;WITH cte AS (
    SELECT t1.ID01
        , t1.ID02
        , rn1 = ROW_NUMBER() OVER (PARTITION BY t1.ID01 ORDER BY t1.ID01, T1.ID02)
        , rn2 = ROW_NUMBER() OVER (PARTITION BY t1.ID01, t1.ID02 ORDER BY t1.ID01, T1.ID02)
    FROM #Table t1
    )
DELETE
FROM cte
WHERE cte.rn1 > 1
    AND cte.rn2 = 1;

SELECT *
FROM #Table;

The output; first the select, then the table after problematic rows have been removed:


Database architecture for interactions between users (8B rows, 1TB of data)

Something wrong with that!  There is no column counter??

But, assuming you meant one of the existing counters...

Having the counter in an index, and updating the counter frequently --> double the work.  Once to update the table, once to update the index.
Since the first part of the PRIMARY KEY is user_id_to, you have a very good way of locating the counter given the user_id_to.  So, DROP that index.  This will speed things up considerably.

Something else to check...  innodb_buffer_pool_size should be about 70% of RAM.  What are the value of those?

PARTITIONing is very unlikely to help.  Given a good index, partitioning is no faster because it is essentially turns a 1-step process (look up in the index) into a 2-step process: first find the partition ("pruning"), then use the index in that partition.

If possible, use either LOAD DATA or "batch" INSERTs.

What do the INSERTs look like?  Perhaps INSERT ... ON DUPLICATE KEY UPDATE?  For batching, that might be best done by creating a tmp table, then using IODKU with a SELECT on the tmp table.  More on high speed ingestion.

Some things look fishy, please provide specifics on the inserts, updates, and selects.


This sounds like an ideal candidate for streaming replication with hot standby.

Alternatives are listed on the Replication wiki page.


Invoke-PolicyEvaluation fails with xml files on network location

Nope you are not missing anything, the command does not support use of UNC paths. At some point when the SQL Server module is published onto GitHub we might be able to help Microsoft fix that issue. However, until then you can work around it by using New-PSDrive to map it to a drive letter and that should work.


SET ANSI_NULLS at the database level versus setting it at the table level

ANSI_NULL_DEFAULT { ON | OFF } :

When you dont specify ANSI_NULL_DEFAULT in CREATE TABLE or ALTER TABLE statements, this database option will determine the default value, NULL or NOT NULL for a column or CLR user-defined type.

When SET to ON, the DEFAULT value is NULL. When SET to OFF, the DEFAULT value is NOT NULL.

  Connection-level settings that are set by using the SET statement override the default database-level setting for ANSI_NULL_DEFAULT. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULL_DEFAULT to ON for the session when connecting to an instance of SQL Server.

ANSI_NULLS { ON | OFF }:

ANSI_NULL should be ON. This will be a default behavior in future versions of SQL Server.

When ANSI_NULLS is set to ON then NULLs follow the ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators.

  When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When ANSI_NULLS is set to OFF then a comparison of NULL=NULL or NULL<>NULL returns TRUE or FALSE respectively, instead of an unknown answer NULL. It treats NULL into a value of its own.

  When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

Always remember that session level setting will override database level setting for the above SET options.

Reference : SET ANSI_NULL_DFLT_ON and SET ANSI_NULLS


Why does SQL Server not perform constant (UNION ALL) branch elimination with OPTION(RECOMPILE) when selecting the result into a scalar variable?

Constant folding has a particular meaning in SQL Server. It is not directly involved in your question. The features that combine to produce extensive simplification of the execution plan for you are the parameter embedding optimization (PEO) and contradiction detection.
PEO embeds the literal value of e.g. a parameter or local variable into the query text, in circumstances where it is safe to do so. One of the requirements is that OPTION (RECOMPILE) is specified. This guarantees that the plan generated will never be reused, so it may be safe to replace non-literals with sniffed literals.
The OPTION (RECOMPILE) hint itself only provides that a new plan will be generated on each execution, the sniffed values of any parameters will be used for cardinality estimation, and the one-off plan generated will not be cached for reuse after execution.
PEO was first added to the product in SQL Server 2008, though it was disabled shortly afterwards due to the possibility of incorrect results. It was re-enabled in SQL Server 2008 SP1 CU5 (Microsoft blog post).
A query optimized with PEO applied appears to the query optimizer exactly as if the query had been written with literals instead of parameters or variables. Contradiction detection can remove entire clauses or relational operator subtrees where literal expressions like WHERE 0 = 1 appear. This facility exists because automated tools often generate such SQL.
It is not always safe to apply PEO, but the exceptions are not officially documented. One exception is where variable assignment occurs (others exist, such as where the parameter appears in an OPTIMIZE FOR clause). My understanding is that variable assignment involves a good deal of complex legacy behaviour, with occasionally odd semantics, preserved for backwards compatibility reasons. It would be impractical to guarantee that PEO would operate correctly in all circumstances, so it is disabled for that case.
PEO is an opportunistic facility that goes beyond the documented behaviour of OPTION (RECOMPILE). It can deliver significant performance benefits in many cases, but it is not officially documented. One might usefully regard it as a bonus feature - nice when you get it, but there are no refunds in case of disappointment.
In your example, where PEO cannot be applied, elimination of subtree execution is provided by start-up filters (which are documented). The Filter operators shown in the "unoptimized" plan are start-up filters that only execute their subtree if the start-up predicate evaluates to true.
The lack of a "seek plan", in the PEO context, is often due to optimizations that can only be performed (either due to safety issues or implementation limitations) when a literal value is present. This literal may appear in the original text, or it may have been substituted in via PEO. An example of this is the optimization rule SelOnSeqPrj, which allows a predicate to move past a sequence function like ROW_NUMBER when safe, but only when a literal value is available.
A SQL Server 2017 repro of the code in the question does not produce the extra Compute Scalar and implicit conversion mentioned in the question. It appears the query used to produce that plan was different to that given in the question. Or perhaps the instance or database has some important configuration or option that was not specified. In any case, I was unable to reproduce it.
The OPTION (RECOMPILE) hint is never ignored. The only implicit conversion in the query is from the internal bigint result of COUNT(*) to integer as required by COUNT (as opposed to COUNT_BIG).
Depending on the requirements and restrictions of the real application behind the question, you may need to employ dynamic SQL, or some other solution. Feel free to ask a new question about potential solutions to the underlying problem, if it can be expressed in a way that is suitable for our Q & A format.

The brief answers to your questions are:

Yes.
I cover it in Parameter Sniffing, Embedding, and the RECOMPILE Options.
No.


Why does my SQL Server database triple in size after deleting millions of records?

You are doing two giant deletes, each one occurring inside an implied transaction.  Each record being deleted is first written to the transaction log.

Because you have changed the database to be in simple mode, the transaction log will be truncated on checkpoint, but you are still causing it to grow during each delete statement.

I suggest you break up the deletes into chunks.  Inside a loop, you can delete one day at a time, starting with the oldest data, stopping at the 6 month mark.  (make sure you have indexes that support your delete criteria, create them if you have to, drop them afterwards).

This way, the checkpoints will occurr more frequently, thus keeping the size of the transaction log down to a minimum.

Truncating a table will NOT grow the database.

Also, before you issue the delete statements, make sure you find out if any triggers exist on that table.


What significance does an index_id < 256000 have?

This is based on the misconception that XML indexes are currently the only type that could ever have an id scheme that is >= 256000 (at least based on their observation; this scheme is not documented AFAIK, so not even sure if it's intentional). Probably fine in current versions, but who knows what type of index will be added next and where its id scheme will start? If you want to exclude XML indexes, you are now also excluding something else. Spatial indexes, for example, seem to start at id = 384000. If the query above is intending to include spatial indexes but not XML indexes, they're going to be in for a surprise.

A much better filter would be:

WHERE type <> 3;

...or even better, since it is self-documenting...

WHERE type_desc <> N'XML';

And now when you want to also exclude, say, spatial indexes, your query changes to...

WHERE type_desc NOT IN (N'XML', N'SPATIAL');

...instead of having to figure out what numeric range the id values for spatial indexes might occupy (or not). Good luck with that.

These are pretty clearly documented in sys.indexes (Transact-SQL). I see no reference to this magic number and I highly recommend you point your tutorial author here so they can see that this magic number is not something they should be relying on (never mind teaching others to rely on).


Get User IDs with no boolean true answer

You can just map false/true to 0/1 and sum those per user. Those that have a zero sum have all false.

select userid
from T
group by userid
having sum(case when active then 1 else 0 end) = 0;

A bit shorter is to use count instead of sum. Nulls are not counted, so we only need to map true to a value, and count all values. I use 1 here but you can pick any value that you want:

select userid
from T
group by userid
having count(case when active then 1 end) = 0;


xp_cmdshell Date Parameter - Looks for File create Date of last modify date?

If you are thinking about using the FORFILES command with the /D parameter, this uses the last modified date. Type FORFILES /? to get more info about this command. See also https://stackoverflow.com/questions/51054/batch-file-to-delete-files-older-than-n-days


Generate lower case tag names with Oracle DBMS_XMLGEN?

According to ORACLE-BASE,

  Where possible DBMS_XMLGEN should be used as it is more efficient than using the Java based DBMS_XMLQUERY

And:

  The example below is a copy of the previous code with all references to DBMS_XMLQUERY replaced by references to DBMS_XMLGEN. Note that two of the lines have been commented out as this functionality is currently not present in DBMS_XMLGEN

I added my emphasis, because this refers specifically to the settagcase you are looking for.

--DBMS_XMLGEN.settagcase(v_ctx, DBMS_XMLGen.LOWER_CASE);

So it looks like you are stuck with the slower DBMS_XMLQUERY.


Filter created_at and count distinct minutes

For lack of information, assuming created_at is data type timestamp, saving UTC timestamps:
SELECT camera_id, count(*) AS snapshot_count
FROM   snapshots
WHERE  created_at >= timestamp '2015-01-01'   -- From date: 2015/01/01
AND    created_at <  timestamp '2015-12-31'   -- to date: 2015/12/30
                                              -- chop off '2015-12-31'?
AND    created_at::time BETWEEN time '09:00' AND time '17:00'
                                              -- 9 AM to 5 AM UTC
                                              -- assuming you meant 5 PM
AND    EXTRACT('ISODOW' FROM created_at) < 6  -- Monday-Friday
GROUP  BY camera_id;

The check for date seems trivial - but be careful what to include / exclude exactly with upper lower bound.

For the time component cast to time: created_at::time.

For the day of week use EXTRACT() with the pattern ISODOW (simpler for your kind of pattern than DOW).
In case you are not aware of the difference between the data types timestamp and timestamptz and the role of the timezone settings and DST, now is the time to read up:

Ignoring timezones altogether in Rails and PostgreSQL

Count minutes with multiple rows as one
For your added Update:
SELECT camera_id
     , count(DISTINCT date_trunc('minute', created_at) AS snapshot_minute_count
FROM ...

Effectively, minutes are counted in which at least one (filtered) snapshot was taken. If 7 snapshots were taken in the same minute, they still count as one.
Performance
The filters on time and day of week are not sargable. If your table is big and performance important, create functional indexes to change that - using IMMUTABLE expressions. Depending on your actual data type, you need to create an IMMUTABLE wrapper function that works independently of the timezone setting, because neither the cast to time nor EXTRACT() (internally the function date_part()) are defined IMMUTABLE for various reasons.
CREATE FUNCTION f_to_time(timestamp)
  RETURNS time LANGUAGE sql IMMUTABLE COST 5 AS
'SELECT $1::time';

CREATE OR REPLACE FUNCTION f_to_isodow(timestamp)
  RETURNS int LANGUAGE sql IMMUTABLE COST 20 AS
$$SELECT EXTRACT('ISODOW' FROM $1 AT TIME ZONE 'UTC')::int$$;

Then:
CREATE INDEX snapshots_created_at_time_idx ON snapshots (f_time(created_at));
CREATE INDEX snapshots_created_at_isodow_idx ON snapshots (f_to_isodow(created_at));

To utilize these indexes, the query hast to use the same expressions:
SELECT camera_id
     , count(*) AS snapshot_count
     , count(DISTINCT date_trunc('minute', created_at) AS snapshot_minute_count
FROM   snapshots
WHERE  created_at >= timestamp '2015-01-01'
AND    created_at <  timestamp '2015-12-31'
AND    f_time(created_at) BETWEEN time '09:00' AND time '17:00'
AND    f_to_isodow(created_at) < 6
GROUP  BY camera_id;


Good articles for SQL Server Health Check specifically for Web Applications

The two I've found most useful:

Brent Ozar's Blitz! Script
Glenn Berry's DMV Diagnostic queries

Posting these as an answer since you haven't received any other responses. Also, not sure how long this question will live; it may be viewed by some as a shopping list request that will eventually be obsolete.


How large will the Transaction Log backup be?

If the logs are full, then yes you will probably be looking at needing the same space to backup them up.

If you're running SQL Server 2008 Enterprise then you may find that enabling backup compression will save you plenty of space, unfortunately the only way to really find out how much is to run the backup (do you have a dev environment where you could restore a copy to?).

The other option if you don't need the ability to restore to a point in Time previous to when you do this, would be to:

put the database into Simple Recovery mode
Checkpoint
put database back into Full Recovery
Take Full backup
Start taking log backups.

By putting the database into Simple Recovery you break the log chain, so when you move back to Full Recovery there will be no log as such for the first log backup to take. Don't forget to take the Full Backup so that SQL Server can start a new log chain.

As mentioned this will mean you'd lose the ability to recover the database to 15:25 21/10/2012 for example. But you'd still be able to restore to a full or differential backup.

Added 03/06/2013

Sorry, I completely forgot this option as well:

Do you have any remote storage options? You can just backup the transaction logs to a UNC path. This may be a  bit slower depending on your network but it'll mean you'll have a full transaction backup covering the previous period and can then start taking smaller regular backups.


Constraint column value based on another column? SQL Server

Use a unique filtered index.

CREATE UNIQUE INDEX uixDefault
ON dbo.Promotions(membershipType)
WHERE defaults = 'Y';

This stops there being a second row with defaults='Y' of the same membershipType.


You would not be able to partition that table as you need to remove the unique key and make it a regular index instead.

To answer your question, if you query on column that isn't partitioned then MySQL would query every partition one-by-one until it answers the query.
So if you query a partitioned table on a column that it is not partitioned on, you may get slower results then if the table was not partitioned.

That is why you need to choose the column you want to partition very carefully.

I would select a column that fits 80% of the most important use cases.


Adding a password authenticated user to a peer authenticated postgres setup not working

Your user connects from a remote client host, so the authentication type, must be host, not local which is used for local connections using named pipes.
The "password" authenticaion method should not be used either, as it sends the password in cleartext. Use md5 instead (or if your Postgres server is configured for it, scram-sha-256)
To allow remote connections you also need to put in an IP address. If you know the IP address you could add that. Using 0.0.0.0/0 would allow a connection from any IP address.
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host   my_db           my_user          0.0.0.0/0               md5

If the client computer is located in the same network as the Postgres server, samenet would be a safer choice than 0.0.0.0/0
To apply changes to pg_hba.conf you need to reload the configuration. A restart is not needed. Reloading can e.g. be done through select pg_reload_conf(); while being connected as the superuser.


SQL Replication components install in 2016 standard edition

Your currently installed instance is at version 13.0.4466.4 (2016 SP1-CU7-GDR), and your installation media is at a lower patch level for the same major version (in your case, 2016 SP1, without the CU).

When you install an additional component (Replication, Full-text search, etc) with install media at a lower version, the installation will succeed, but those components will be unpatched. Essentially, the database engine & previously installed bits will be at one version (SP1-CU7) and the newly installed bits will be at a lower version.

After installing the additional components, you will want to re-patch the instance to install the CU. This will ensure that all components on the instance are at a consistent patch level, and ensure you have a supported configuration.

Essentially, whether you're installing additional components or a brand new instance, you'll want to run through the same combination of installers to ensure you get the desired major version + SP + CU.


DPT_ENTRY_LOCK waits are caused when the workload is constantly modifying the same pages (so constant updates would cause this to show up). Practically nothing has been written around this subject so trying to get definitive answers (along with supporting evidence) is more than a challenge, as such I'll give you what I've seen from experience on my side. You would probably need to open a CSS call at this point for something more definitive.

When you have a large number of transaction on your primary that have to be flushed to the secondary, and you have a high number of reads on a secondary that access the same tables, you end up with this lock, or a DIRTY_PAGE_TABLE_LOCK wait.

These happen when there is a lot of work going on, meaning that there are a large number of dirty pages that need to be flushed, expect to see very short wait times on this usually. You wouldn't expect this thanks to RCSI on the secondary, but it happens, and I've only seen it in the case where there are a large number of writes on the primary and read calls on the secondary.

As for a way around this, you could try using indirect checkpoints (if not already enabled) which uses sorted, partitioned lists to track dirty pages rather than the random ordering used by prior versions - see SQL 2016 – It Just Runs Faster: Indirect Checkpoint Default

There is a possibility that this could cause unforeseen performance issues on your primary, so you should test it at load prior to implementing on a production system. And there is no guarantee that this will work. I'm actually about to try this very thing for some systems that I'm experiencing the same issues with.

You might expect to see PARALLEL_REDO_TRAN_TURN waits as well, which are caused by page splits.


PostgreSQL: pg_upgrade 9.5 to 13 failing with "connection to database failed: FATAL: database "template1" does not exist"

From the PostgreSQL mailing list:
The problem is that postgis, through gdal, ends up being linked to two different versions of proj at the same time.
You can check it by doing:
ldd /usr/pgsql-13/lib/postgis_raster-3.so | grep proj
If that shows up two different proj libraries, then you have that same problem.
In this case, uninstall the OS supplied proj library. If that removes postgis through dependency, let it and then install it with:
dnf install --excludepkg proj --excludepkg proj-datumgrid postgis30_13


Mapping the oracle database directory object to a network path

If you're talking about moving the path of the directory object, just use the Create or replace option :

CREATE OR REPLACE DIRECTORY Result AS 'c:\newpath';


Looking at your original query, I see that the only static WHERE clause is

WHERE eo.member = 103

This being the case, you should make establishment_owner the main table in the query:

SELECT
    m.id,
    IF(CURRENT_TIMESTAMP BETWEEN bc.start_date AND bc.expiration_date, FALSE, TRUE) AS hasFreeAccount,
    CONCAT(e.id, ' - ', e.name) AS establishment,
    bc.start_date,
    bc.expiration_date
FROM
    establishment_owner eo
    LEFT JOIN establishment e ON eo.establishment = e.id
    LEFT JOIN member m ON eo.member = m.id
    LEFT JOIN business_command bc ON m.id = bc.member
WHERE eo.member = 103
    AND (bc.start_date <= CURRENT_TIMESTAMP() OR bc.start_date is NULL)
    AND (bc.id NOT IN (
        SELECT bc.id
        FROM member m
            LEFT JOIN business_command bc on m.id = bc.member
            INNER JOIN establishment_owner eo on m.id = eo.member
        WHERE CURRENT_TIMESTAMP() > bc.expiration_date
            AND eo.member IN (SELECT m.id FROM member m LEFT JOIN business_command bc ON m.id = bc.member HAVING COUNT(m.id) > 1)
    ) OR bc.id IS NULL);

In other words, LEFT JOIN from establishment_owner instead of establishment.


How to enforce an "exactly-one-per-group" constraint?

The problem is similar to this one: How to have a one-to-many relationship with a privileged child?

The "at most one per group" part of the constraint can be solved with a partial index:

CREATE UNIQUE INDEX is_FavoriteChild
  ON x (yid)
  WHERE is_principal  ;

Another way to solve the problem is by removing the is_principal column and add a 3rd table. This doesn't solve the "exactly one" either:

CREATE TABLE x (
    xid INTEGER NOT NULL PRIMARY KEY,
    yid INTEGER NOT NULL REFERENCES y (yid),
    UNIQUE (yid, xid)
);

CREATE TABLE x_principal (
    xid INTEGER NOT NULL,
    yid INTEGER NOT NULL PRIMARY KEY,
    FOREIGN KEY (yid, xid) REFERENCES x (yid, xid)
);

If you want to enforce the "exactly one" restriction using DDL alone, it can be done in Postgres with deferrable constraints (I don't think this is an option in SQLite).

For more details and options, you can see the excellent answer in the SO question by @Erwin: Complex foreign key constraint in SQLAlchemy.

(Editing the answer for the additional detail that not all values of y.yid have to appear in table x. One table is added):

--- This table will hold all values of yid that appear in table x
CREATE TABLE y_x (
    yid INTEGER NOT NULL PRIMARY KEY REFERENCES y (yid),
    --- **no other columns**
    principal_xid INTEGER NOT NULL
);

CREATE TABLE x (
    xid INTEGER NOT NULL PRIMARY KEY,
    yid INTEGER NOT NULL REFERENCES y_x (yid)
                         DEFERRABLE INITIALLY DEFERRED,
    UNIQUE (yid, xid)
);

ALTER TABLE y_x
ADD CONSTRAINT y_principal_x_fk
   FOREIGN KEY (yid, principal_xid)
   REFERENCES x (yid, xid)
   DEFERRABLE INITIALLY DEFERRED;


Query using $gte and New Date() working in MongoDB shell but not through python pymongo

As per MongoDB pymongo documentation here

The  count() method is deprecated and not supported in a transaction. Please use count_documents() instead.

count(with_limit_and_skip=False)

DEPRECATED - Get the size of the results set for this query.

The count() method is deprecated and not supported in a transaction. Returns the number of documents in the results set for this query. Does not take limit() and skip() into account by default - set with_limit_and_skip to True if that is the desired behavior. Raises OperationFailure on a database error.

When used with MongoDB >= 2.6, count() uses any hint() applied to the query. In the following example the hint is passed to the count command:

collection.find({‘field’: ‘value’}).hint(‘field_1’).count()

The count() method obeys the read_preference of the Collection instance on which find() was called.

Changed in version 3.7: Deprecated.
Changed in version 2.8: The count() method now supports hint().

count_documents(filter, session=None, kwargs)

Count the number of documents in this collection.

The count_documents() method is supported in a transaction.

All optional parameters should be passed as keyword arguments to this method. Valid options include:

skip (int): The number of matching documents to skip before returning
results.
limit (int): The maximum number of documents to count.
maxTimeMS (int): The maximum amount of time to allow this operation
to run, in milliseconds.
collation (optional): An instance of <a
href="https://api.mongodb.com/python/current/api/pymongo/collation.html#pymongo.collation.Collation" rel="nofollow noreferrer">
Collation. This option is only supported on MongoDB 3.4 and
above.
hint (string or list of tuples): The index to use. Specify either the
index name as a string or the index specification as a list of tuples
(e.g. [(‘a’, pymongo.ASCENDING), (‘b’, pymongo.ASCENDING)]). This
option is only supported on MongoDB 3.6 and above.

The count_documents() method obeys the read_preference of this Collection.

Note When migrating from count() to count_documents() the following query operators must be replaced:

Operator     Replacement
$where        $expr
$near         $geoWithin with $center
$nearSphere $geoWithin with $centerSphere

$expr requires MongoDB 3.6+

For your further ref here and here


According to Garcia-Molina (this is from memory) a database is any permanent store of data where the data is retrievable.

This, to my mind, makes a notepad file a "database". The whole issue here is context and semantics!

A database can mean pretty much whatever the person who uses the word says!

To give you another example, I used to work for an airline. The word "flight" meant 3 different things depending on whether you were talking to engineers, flight crew or sales people.

1) to an engineer, a "flight" meant an aircraft taking off and landing (one "leg")
2) to flight crew, a "flight" meant out from base and back to base (two "legs" - aka a "return")
3) to sales, a "flight" meant a season (high/low) of "flights" over a six month period - obviously many "legs" (also known as a "rotation").

Database terminology is fraught with the same issues:

Does a database have to be relational? Clearly not, although some purists might argue otherwise.
Does it have SQL or not? Does that make it a  database? Clearly there were databases prior to SQL and many current databases don't support it!
If you store a list of phone numbers in a notepad file on Google drive, is that a database? Clearly by some criteria it is - it's searchable, the data is retrievable and permanent!

My answer to your question is to be clear in your definitions before you start a detailled technical conversation - as an IT person in the airline who dealt with engineers, flight crew and sales, I always  had to stop and ask them what they meant by "flight" and then gently nudge them towards the terms "leg", "return" or "rotation".

Do the same in your conversations about databases and you'll not go wrong - "Are we talking about SQL Server or MongoDB?" &c.


Postgres cannot simulate table with a view: column must appear in the GROUP BY clause or be used in an aggregate function

Postgres is smart enough to know in the TABLE that your group-by column is UNIQUE (PRIMARY KEY) ... grouping by a column that is UNIQUE is pointless, so postgres just gives you back what you ask for (column values a and b)..

However, Postgres does NOT know that the group-by column in the VIEW is UNIQUE..  Thus, it's confused by your query (as well as most humans would be confused by your query - including myself).

FYI - your query makes no sense.  Why would you group by a column when you apparently have no interest in performing an aggregate?  What would you expect a and b to give you?