Forcing correct execution plan when passing a table UDT to a table function

Summary
The question does not provide execution plans or a full reproduction script, but based on the information given, you should use a FAST 1 hint. If you can, you should also consider converting the scalar function to the inline table-valued type.
AdventureWorks-based examples
Download the Microsoft sample database here.
Table type
CREATE TYPE dbo.PrimaryKeys AS TABLE
(
    PK integer PRIMARY KEY
);

Scalar function
You could try various combinations of INNER LOOP JOIN (with the written join order reversed, and FORCE ORDER added to suppress the warning), FORCESEEK(index_name(columns)) (without schemabinding) and even QUERYTRACEON(8690) to prevent a performance spool; but in my experience, FAST 1 is the most reliable way to get the right plan, and simpler as well.
CREATE FUNCTION dbo.GetXML
(
    @Lines dbo.PrimaryKeys READONLY
)
RETURNS xml
WITH SCHEMABINDING -- If possible
AS
BEGIN
    DECLARE @x xml;

    WITH XMLNAMESPACES (N'important namespace' AS pro)
    SELECT @x =
    (
        SELECT
            SOH.SalesOrderID,
            SOH.OrderDate,
            SOH.DueDate,
            SOH.ShipDate,
            SOH.[Status],
            SOH.PurchaseOrderNumber,
            SOH.AccountNumber,
            SOH.CustomerID,
            SOH.TotalDue
        FROM @Lines AS L
        JOIN Sales.SalesOrderHeader AS SOH
            ON SOH.SalesOrderID = L.PK
        ORDER BY
            SOH.SalesOrderID
        FOR XML PATH(N'pro:Import'), TYPE
    )
    OPTION (FAST 1);

    RETURN @x;
END;

In addition to all the usual reasons to avoid scalar functions, they do make plan analysis harder. First, you need to capture actual plans using Profiler or Extended Events - they do not appear in query tools like SSMS. Second, not all hints work as you would expect, for example RECOMPILE due to scoping and the separate plans.
DECLARE @Lines dbo.PrimaryKeys;

INSERT @Lines (PK)
SELECT TOP (300) SOH.SalesOrderID
FROM Sales.SalesOrderHeader AS SOH;

SELECT dbo.GetXML(@Lines);

The code above reliably produces the desired plan for me regardless of the number of rows in the table variable.
Without the FAST 1 hint in the function, the optimizer chooses a merge join and scan of both tables. This plan is cached and reused for a later function execution where TOP (300)is changed to TOP (1), reproducing the core of the issue.
Inline table-valued function
Unless there are absolutely compelling reasons to stick with the scalar function, you should probably rewrite it to inline table-valued form as follows:
CREATE FUNCTION dbo.GetXMLTable
(
    @Lines dbo.PrimaryKeys READONLY
)
RETURNS TABLE
AS
RETURN
    WITH XMLNAMESPACES (N'important namespace' AS pro)
    SELECT
        X.xml_result
    FROM
    (
        SELECT
            SOH.SalesOrderID,
            SOH.OrderDate,
            SOH.DueDate,
            SOH.ShipDate,
            SOH.[Status],
            SOH.PurchaseOrderNumber,
            SOH.AccountNumber,
            SOH.CustomerID,
            SOH.TotalDue
        FROM @Lines AS L
        CROSS APPLY
        (
            SELECT TOP (1) SOH.*
            FROM Sales.SalesOrderHeader AS SOH
                WITH (FORCESEEK(PK_SalesOrderHeader_SalesOrderID(SalesOrderID)))
            WHERE
                SOH.SalesOrderID = L.PK
            ORDER BY
                SOH.SalesOrderID
        ) AS SOH
        ORDER BY
            SOH.SalesOrderID
        FOR XML PATH(N'pro:Import'), TYPE
    ) AS X (xml_result);

The TOP (1) in the cross apply prevents the optimizer choosing anything other than nested loops join. The FORCESEEK hint is not required; I have included it to illustrate how it could be used if necessary.
DECLARE @Lines dbo.PrimaryKeys;

INSERT @Lines (PK)
SELECT TOP (300) SOH.SalesOrderID
FROM Sales.SalesOrderHeader AS SOH;

SELECT GXT.xml_result
FROM dbo.GetXMLTable(@Lines) AS GXT;

The actual execution plan (without using Profiler/XE) is:


SQL Server : How to disable trigger for an update only for your current session?

To solve your problem, we have to take a programmatic approach to the problem. There are two routes you can go here. The reason for needing these approaches is because you cannot disable a trigger for a particular statement, it can only be disabled for the entirety of the table.

Option 1: Context_Info()

Samuel Vanga on MS SQL Tips had a great example:

USE AdventureWorks;
GO
-- creating the table in AdventureWorks database
IF OBJECT_ID('dbo.Table1') IS NOT NULL
DROP TABLE dbo.Table1
GO
CREATE TABLE dbo.Table1(ID INT)
GO
-- Creating a trigger
CREATE TRIGGER TR_Test ON dbo.Table1 FOR INSERT,UPDATE,DELETE
AS
DECLARE @Cinfo VARBINARY(128)
SELECT @Cinfo = Context_Info()
IF @Cinfo = 0x55555
RETURN
PRINT 'Trigger Executed'
-- Actual code goes here
-- For simplicity, I did not include any code
GO

Now when Samuel does not want want the trigger to execute, they use this:

SET Context_Info 0x55555
INSERT dbo.Table1 VALUES(100)

Context_Info uses the following system views to grab information regarding the current session:

sys.dm_exec_requests
sys.dm_exec_sessions
sys.sysprocesses

The ideology here is that the binary string you are setting is exposed only to the current session, so when the trigger executes during your session, it will see the scope and variable setting of the Context_info function and it will jump to the escape portion of the trigger instead.

Option 2: Temp Table

Itzik Ben-Gan has a great solution in his book "Inside Microsoft SQL Server 2008 T-SQL Programming: T-SQL Programming" which is also in his later book T-SQL Querying. The primary problem with this over the context_info function is the minor TempDB overhead.

To spoil the surprise but not ruin the plot of the books (I felt they are worth purchasing and reading), you will alter your trigger.

Your trigger should perform a check for a temporary table. If the temporary table exists, the trigger should know to end and not perform the actions.

In the update statement you want to perform, create the temporary table first. It will be seen in the same transaction as the trigger and it will cause the trigger to ignore your statement.

Example of trigger:

CREATE TRIGGER TRIGGERNAME ON TABLENAME for INSERT AS

IF OBJECT_ID('tempdb..#FAKETEMPTABLE') IS NOT NULL RETURN;
GO

Example of beginning statement when you don't want the trigger to run:

CREATE TABLE #FAKETEMPTABLE(col1 SMALLINT);

Putting it altogether for your example:

ALTER TRIGGER tiu_benefit ON benefit FOR
...
AS
...
IF OBJECT_ID('tempdb..#FAKETEMPTABLE') IS NOT NULL RETURN;
--... rest of code here
GO

CREATE TABLE #FAKETEMPTABLE(col1 SMALLINT);
UPDATE benefit SET editor = 'srh' where benefit_id = 9876;
GO


Can I install SQL Server Management Studio locally?

If you want the shiny new 2016 monthly releases of Management Studio, which include a handy check for updates mechanism, negates the need for a full setup program and download (since it only retrieves the components you need), as well as a release cycle completely independent from SQL Server, go here (and you can read about it here and here).

If you use this new web installer, much of the below no longer applies. If you want to use previous versions (currently 2012 SP2 and 2014 SP1), they've made it easier to find and download (see this page), but much of the below will still apply.

Well, if you have a legal copy of SQL Server, you should be able to install Management Studio from that media wherever you need to manage SQL Server from.

That said, the Express version of Management Studio, since 2012 Service Pack 1, has been a fully functional product with absolutely no differences (except you can download it for free instead of finding your licensed media). I would recommend downloading the 2014 version, because why use an older version that will be out of support sooner? My philosophy is that SSMS should always be >= the highest engine version you need to manage.

Anyway, I found the link by searching - the hint to not jumping through hoops set up by Microsoft marketing or MSDN is to look for the page with download in the URL:

This brings me here, where I click Download:

Then pick either the 32-bit or 64-bit version, depending on your platform, and click Next:

And success. I even get handy tips for installation if I need them.

If you really, really, really want to use the 2012 version instead of the 2014 version, well first I'd be interested why, but the same process will work, just use this link instead. Note that the 2012 version is a 950 MB download vs. 684 MB for 2014.

Since a lot of people get confused on how to install JUST SSMS with this installer, here are some other things you'll want to follow once you launch the .exe. First it will open the installation center. You want Installation > New SQL Server stand-alone [blah blah]...

Clicking that link will launch a new wizard, and leave the installation center open. Leave it open (see the end of this post for why). On the new screen, you may have product updates offered, and you definitely want to select them if so (currently this should only happen for 2012 SP1 or 2014, since 2012 SP2 was not affected by Security Bulletin MS14-044).

Next, and this is where most people get confused, you want to choose Perform a new installation - even though you are not trying to install an actual instance of the SQL Server database engine.

Then, you want to select both Management Tools options, basic and complete. From here you should be ok on your own.

Regarding the installation center window above, you need to leave the "SQL Server Installation Center" window open during setup, or it will fail with an error like this (the actual folder name is some random GUID-based thing):

  SQL Server Setup has encountered the following error: Could not find file 'c:\1d3c284ce0e72239e2f4e51c802d\MediaInfo.xml'

I filed a Connect item about it, but so far it remains active.


Oracle: How do I add users to the HTTP ACL in Oracle 11?

First, see if you have an ACL for port 80, the HTTP port.  If you do, it should look something like  this.

select * from dba_network_acls;

HOST     LOWER_PORT UPPER_PORT ACL                     ACLID
---      ---------- ---------- ---                     -----
*        80         80         /sys/acls/www.xml       1FBEC09C32D78F5AE05335D6488A7883

If you need to recreate it, you can drop it with this command:

dbms_network_acl_admin.drop_acl('www.xml');

Create the ACL:

 dbms_network_acl_admin.create_acl(
     acl => 'www.xml',
     description => 'WWW ACL',
     principal => 'SCOTT',
     is_grant => true,
     privilege => 'connect'
 );

Assign hosts and ports to the ACL:

 dbms_network_acl_admin.assign_acl(
     acl => 'www.xml',
     host => '*',
     lower_port => 80
 );

Add users to the ACL:

 dbms_network_acl_admin.add_privilege(
     acl => 'www.xml',
     principal => 'OE',
     is_grant => true,
     privilege => 'connect'
 );

  version note: kevinsky points out that this is applicable for Oracle 11 but that it has changed in Oracle 12.


You can use part_name parameter of DBMS_REDEFINITION.START_REDEF_TABLE procedure. Here is the complete example of online table redefinition. Online Table Redefinition Examples from Oracle Docs.
Methods to move a partition to another tablespace


Try adding an "OFFSET 0" in the subquery like this:

SELECT *
FROM table1
WHERE table2_fk IN (SELECT id FROM table2 WHERE table3_fk = 3 OFFSET 0);


What's the name for a table that does not hold "mutable" data?

It could be called an enum, a reference table or a meta-data table.

I prefer to call that kind of table by its real name, i.e. in your case "privileges" or perhaps "PrivilegeLevels".  I don't tend to make distinctions between tables where rows change, and tables where rows don't often change since that tends to needlessly add nomenclature for its own sake.


Trying mysql's built in table check/fix/ etc functions did not help my in restarting mysql server in regular mode.

I ended up:
1) doing mysqldump of all the databases, into one file.
This article was immensely helpful

2) dropping 1 db at a time, and attempting to restart mysql server in regular mode, after each drop (nothing helped).

3) I ended up purging mysql from ubuntu all together, and re-installing it.

4) I did the sql dump restore doing the following command (took 3 hours for 200gb dump, on a fairly fast machine):

mysql -u root -p  <  /home/zzzzz/all_databases.sql

Best of luck, and hope this doesn't happen to you - it was definitely a waste of 2 days of trial and error, with the nuclear option as my last restort.


Fetch large data from Sql Server and process in chunks

Using a permanent table (as the commenter suggests) makes it a lot easier to track dependencies. Instead of recreating the table every time, just TRUNCATE it.

When you implement a pattern like this, you have to make sure you are making progress and not selecting the same records over and over again. One way to do this, is to process records in the order of the primary key on the input table. Here is the pseudocode for doing this:

DECLARE @Progress [Type of key]
/* Initial state, start from the bottom of the table */
SELECT  @Progress = MIN(Key) FROM Input

/* Main event loop to do work */
WHILE 1=1 BEGIN
  INSERT INTO WorkTable
  SELECT TOP (@SizeOfBatch) *
  FROM Input WHERE Key > @Progress
  ORDER BY Key ASC

  /* Do work on WorkTable */

  /* Make sure we start where we left off */
  SELECT @Progress = MAX(Key) FROM WorkTable

  TRUNCATE TABLE WorkTable

  /* Wait for 5-10 min */
END

Of course, the above only works if the Key column is ascending. If it isn't you have to maintain a column in the Input table that track whether work has been done or not (and then SELECT TOP the ones where it hasn't)


Insert sometimes takes more than 2 seconds in table with +6 million records

Well, it takes time (possibly more than 2 seconds) to shovel tens of thousands of rows. Disks, networks, etc, are not fast enough to go faster.

Meanwhile, such a SELECT is interfering, at some level, with anything else that is going on -- such as an INSERT that might be hitting the same user_id.

Also, are you using autocommit = 1, which commits each statement as you go.  Or are you using =0 or BEGIN, in which case groups of statements are bunched together into a "transaction"?  This can impact on how one connection is interfering with another.

When you say that a 1-row INSERT is taking 2 seconds, what tool are you using for the timing?  To an end-user something can 'feel' like 2 seconds, but the real cause may be something else.

(Analysis of VARIABLES and STATUS:)

Observations:

Version: 5.7.24-0ubuntu0.18.04.1-log
16 GB of RAM
Uptime = 4d 00:07:33
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Wow!  This is the first VARIABLES+STATUS I have review for which I saw nothing 'important' to fix.

Details and other observations:

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 451,008 / 786432 = 57.3% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size is bigger than necessary?

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( net_buffer_length / max_allowed_packet ) = 16,384 / 16M = 0.10%

( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue

( Select_scan / Com_select ) = 226,117 / 2089280 = 10.8% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( back_log / max_connections ) = 80 / 151 = 53.0%

Abnormally large:

innodb_page_cleaners = 12
performance_schema_max_file_classes = 80
performance_schema_max_mutex_classes = 210


CXPACKET can be accompanied with a LATCH_XX (possibly with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well). If this is the case (and I believe it is, based on the question) then the MAXDOP value should be lowered to fit your hardware.

Besides this, here are some more recommended steps in diagnosing the cause of high CXPACKET wait stats values (before changing something on SQL Server):

Do not set MAXDOP to 1, as this is never the solution
Investigate the query and CXPACKET history to understand and determine whether it is something that occurred just once or twice, as it could be just the exception in the system that is normally working correctly
Check the indexes and statistics on tables used by the query and make sure they are up to date
Check the Cost Threshold for Parallelism (CTFP) and make sure that the value used is appropriate for your system
Check whether the CXPACKET is accompanied with a LCK_M_XX (usually accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is the case, then parallelism is not the bottleneck. Troubleshoot those wait stats to find the root cause of the problem and solution

If you really need to understand the CXPACKET wait type in depth, I would advise reading the Troubleshooting the CXPACKET wait type in SQL Server article


How to delete large amount of data in sql server without data loss?

Use batched deletes.

DECLARE @keepgoing bit = 1;

WHILE (@keepgoing = 1)
BEGIN
  DELETE d
  FROM
  (
  SELECT TOP 100 *
  FROM Table_A
  WHERE Created < DATEADD(DAY, -10, GETDATE())
  AND NOT EXISTS (....FK check...)
  ORDER BY Created
) d;

IF (@@ROWCOUNT = 0) SET @keepgoing = 0;
END


PostgreSQL - Return type and EXECUTE QUERY

It's a mismatch of nesting levels.

The return type of the function is declared as:

returns setof core.search_result

core.search_result being the composite type you created earlier. This is equivalent to the more explicit declaration:

RETURNS TABLE (
  user_id uuid,
  country core.country,
  ethnicity core.ethnicity,
  is_smoker core.smoking_status,
  existing_conditions varchar[]
)

Then you return values with:

select (
       core.user.id,
       core.user.country,
       core.health_wellness.ethnicity,
       core.health_wellness.is_smoker,
       core.health_wellness.existing_conditions
     )::core.search_result
     from ...

... which would seem to make sense on a first glance. But the expression is exactly 1 column of type core.search_result, which Postgres tries to fit into the first column of the return type (user_id uuid). Hence the DETAIL information in your error message:

Returned type core.search_result does not match expected type uuid in column 1

You need to return 5 separate columns.

Just remove parentheses & cast:

select core.user.id,
       core.user.country,
       core.health_wellness.ethnicity,
       core.health_wellness.is_smoker,
       core.health_wellness.existing_conditions
     from ...

You may have to add casts to individual columns if data types don't match. One way to avoid that would be to cast the whole ROW type and decompose to separate columns again:

select ((
       core.user.id,
       core.user.country,
       core.health_wellness.ethnicity,
       core.health_wellness.is_smoker,
       core.health_wellness.existing_conditions
     )::core.search_result).*
     from ...

But I'd rather avoid that complication.

Aside:
Once you have fixed the exception, raise notice at the very end of your function is reached unconditionally and incorrect. RETURN QUERY ... does not end a function. Only RETURN; does. Rearrange to something like:

IF _where <> '' THEN
  _sql := _sql || ' WHERE ' || _where;
  return query EXECUTE _sql
  USING $1, $2, $3;
ELSE                                     -- !!
  raise notice 'all input params null';  -- !!
END IF;

Details:

PostgreSQL stored procedure to return rows or empty set


SQL Backup/Restore and Disaster Recovery solutions with VMware

VMware HA is not database server high availability...

Read Brent Ozar's article on the subject.


Read Only Access Except Stored Procedures in Specific Schema

If you are looking to create embedded sql-inline Linq queries within Entity Framework, you can assign dbreader or dbwriter permissions appropriately to the application login.

Unfortunately currently in SQL Server, you cannot create specific access to Stored Procedures which only conduct read operations as a category. You will have to grant individual reader or write access to the specific stored procedures by
Grant execute on Stored procedure to user, etc.


How to add CYCLE to BIGSERIAL in phpPgAdmin

I don't use phpPgAmin.

To change an existing column definition to use the CYCLE attribute you need to understand that this is an attribute of the sequence not the "column".

A serial or bigserial is only a shorthand notation to assign a default value to a column which is take from a sequence.

When you define a column as serial Postgres automatically creates a sequences and applies the necessary default expression.

The sequence is usually called <table_name>_<column_name>_seq. But you can run a query to get the name of the generated sequence:

select pg_get_serial_sequence('your_table_name', 'your_column_name');

Once you know the name of the sequence, you can do the following:

alter sequence your_sequence_name cycle;

(Note that you need to commit that if you are not using auto-commit mode).

Here are some relevant links to the manual:

ALTER SEQUENCE: http://www.postgresql.org/docs/current/static/sql-altersequence.html
Details about the serial types: http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL
Information functions: http://www.postgresql.org/docs/current/static/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE


Base on your input data, I will do something like this:

select I.X.value('(key/text())[1]', 'varchar(50)') as ItemID,
       I.X.value('(value/text())[1]', 'varchar(50)') as Store
from @XML.nodes('values/Nationality') as O(X)
  cross apply O.X.nodes('item') as I(X);

ouptput

ItemID  Store
UK  def
IE  def


You could wrap your query inside a subquery and select a max from that subquery like:

SELECT MAX(age) FROM
(
SELECT s.age
FROM  professor as p
join class as c  on p.prof_id = c.prof_id
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller'
 union SELECT s.age from student as s where s.major ='history'
) as subquery;

or rely on a sorting and a limit like this

SELECT age FROM
(
SELECT s.age
FROM  professor as p
join class as c  on p.prof_id = c.prof_id
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller'
 union SELECT s.age from student as s where s.major ='history'
) as subquery
ORDER BY age DESC
LIMIT 1;

But you could also select the maxes before unions like this:

SELECT MAX(age) FROM
(
SELECT max(s.age) AS age
FROM  professor as p
join class as c  on p.prof_id = c.prof_id
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller'
 union SELECT max(s.age) AS age from student as s where s.major ='history'
) as subquery;

or

SELECT age FROM
(
SELECT MAX(s.age) AS age
FROM  professor as p
join class as c  on p.prof_id = c.prof_id
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller'
 union SELECT MAX(s.age) AS age from student as s where s.major ='history'
) as subquery
ORDER BY age DESC
LIMIT 1;

Have a look at my SQLFiddle here

I created 3 approaches (using simplified table structures and sample data).

Using

CREATE TABLE Table1
    (`id` int, `age` int)
;

INSERT INTO Table1
    (`id`, `age`)
VALUES
    (1, 5),
    (2, 6),
    (3, 3)
;

CREATE TABLE Table2
    (`id` int, `age` int)
;

INSERT INTO Table2
    (`id`, `age`)
VALUES
    (1, 6),
    (2, 8),
    (3, 1)
;

and these 3 queries

SELECT MAX(age) FROM
(
  SELECT age FROM Table1
  UNION
  SELECT age FROM Table2
 ) as results

 ;
 SELECT age FROM
 (
   SELECT MAX(age) as age FROM Table1
   UNION
   SELECT MAX(age) as age FROM Table2
 ) AS results
 ORDER BY age DESC
 LIMIT 1
 ;

 SELECT max(age) FROM
 (
   SELECT MAX(age) as age FROM Table1
   UNION
   SELECT MAX(age) as age FROM Table2
 ) AS results
 ;

All three return '8' but the third query looks like it's the most efficient one.
Test it on your table structures and see what you like best.


SQL Server SELECT column not in Group By clause?

FYI, I just solve my issue by using the subquery

SELECT m.MerchandiseId, m.MerchandiseName, (SELECT TOP 1 e.InboxMessage FROM Inbox e WHERE (e.ToMerchantId = m.MerchandiseId AND e.FromCompanyId = 10) OR (ToCompanyId = 10 AND FromMerchantId = m.MerchandiseId) ORDER BY e.CreatedBy ASC) AS InboxMessage, createdAt =  MAX(i.CreatedAt) FROM Inbox i
INNER JOIN [User] u
ON i.FromUserId = u.UserId
LEFT OUTER JOIN Merchandise m
ON u.MerchandiseId = m.MerchandiseId  OR i.ToMerchantId = m.MerchandiseId
WHERE i.ToCompanyId = 10 OR i.FromCompanyId = 10
GROUP BY m.MerchandiseId, m.MerchandiseName

Here's the code that I worked on


Import data from a CSV file without creating a table first in MySQL

There is no way to do this with MySQL own tools. If you look closer it makes sense that you have to create your table first, you have to specify datatypes for the fields of your csv file, possible indexes and so on. MySQL can´t predict which datatype you want to use, so here is some manual work to do.

On the other hand you can use external scripts to archive what you want, a quick and dirty example would be:

#!/bin/bash
# create table for import
head -1 test.csv | awk '{split($0,f,","); printf "create table test (%s INT, %s VARCHAR(50));",f[1],f[2]}' | mysql -u USER -pPASS SCHEMA
# import csv
mysql -uUSER -pPASS SCHEMA -e "LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE testcsv FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES"

Above "script" lefts plenty of room for optimization, it should only give you a basic idea how to archive what you need with common Linux tools.


Create new table with a sum where row from table 2 matches field in table 1 and aggregate on another field

SELECT t1.code_1, SUM(t2.measure_1)
FROM table1 t1
NATURAL JOIN table2 t2
GROUP BY t1.code_1

A working example can be found at db<>fiddle where I used the PostgreSQL 10 engine. You can build on this example.

Instead of using a static table, you might want to consider using a view instead. This has the advantage that the values will be calculated on-the-fly and aren't static.


Deleting MySQL table with pending transactions

There is nothing you can really do because a rollback is being done via the UNDO tablespace inside ibdata1, which should have grown immensely.
If you kill the mysqld process and restart mysql, it will just pickup where it left off as part of the crash recovery cycle.
DISCLAIMER : Not Responsible for Data Loss
What you could do may result in data loss for other tables, but there is something you can do to circumvent InnoDB's normal crash recovery cycle.
There is a startup option called innodb_force_recovery, which allows you to bypass various stages of InnoDB crash recovery.
According to MySQL Documentation on Forcing InnoDB Recovery, here are the settings and its effects:

1 (SRV_FORCE_IGNORE_CORRUPT)
Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.
2 (SRV_FORCE_NO_BACKGROUND)
Prevent the master thread from running. If a crash would occur during the purge operation, this recovery value prevents it.
3 (SRV_FORCE_NO_TRX_UNDO)
Do not run transaction rollbacks after crash recovery.
4 (SRV_FORCE_NO_IBUF_MERGE)
Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.
6 (SRV_FORCE_NO_LOG_REDO)
Do not do the redo log roll-forward in connection with recovery.

With transactional changes buried in the UNDO and REDO logs, you run the risk of

losing data meant to be written
keeping data meant to be deleted

In case you expect bad side effects, backup the entire /var/lib/mysql and put it somewhere in case you want to copy ibdata1, ib_logfile0, and ib_logfile1 and retry normal recovery.
If mysql is fully up in one of the modes

mysqldump all data except the offending table
shutdown mysql
remove everything in /var/lib/mysql except /var/lib/mysql/mysql
start mysql
reload the mysqldump

CAVEAT : Make sure you backup everything !!!
I hope this helps !!!


MySQL trigger and values not supplied in update query

The Boolean expression

NEW.col2 <=> OLD.col2

can be used in an IF statement (or elsewhere).  Notes:

It tests for whether col2 is changing, it does not test whether you specified col2 without changing it.
NULL is properly handled because of the <=> operator.

UPDATE (based on further details)  See if this comes closer:

IF ( NEW.col1 <=> OLD.col1  -- testing only col1 for changes
 AND NEW.col2 IS NULL)      -- avoiding clobbering col2
    NEW.col2 = 'triggered';

(You haven't yet specified enough use cases to provide a complete solution, but maybe I have given you enough building blocks?)


There are the manuals Specifying Archive Destinations,LOG_ARCHIVE_DEST_n. But they are unclear about if it is possible to change it online.

Try it out on a test instance. You always should try out thinks on a test instance. Especially if you are using some tips from the internet.
If you an alter system set = ... scope=both; and the parameter cannot be specified in memory, you get a

ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

error, but nothing bad happens. So you can try it out if the parameter is specified in memory.

I tried to set the parameter to the value already set and it worked

SQL> alter system set log_archive_dest_1=
  2  'location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES,ALL_ROLES)'
  3  scope=both;

System altered.

SQL>

If you change the destination for the archives and there are still archives in the old destination then the database knows this an therefore RMAN knows this and will not forget them but backup them when you do your next archive log backup.


What are practical hurdles in making protein interaction database?

I started writing an answer earlier on certain aspects of database design and implementation, which I abandoned because I realized that these would (or should) not be the very first steps on the way of developing a database (or, more broadly, a software) for your needs.  This way, this may not be the answer you are waiting for.

As a newbie a few years ago, I was also tempted to make something big and interesting.  (To be honest, I had only a part of the competence needed for that particular project.  I hoped that once I would be ready with the database part (which I knew how to do), everybody would be so convinced that it would going to be superb, that they would have the necessary patience for me making mistakes and being (very) slow.)  As I started to do my thing, I realized that

the project was much more complicated than it looked first (partly because of lacking specification)
I was not that good than I'd thought before (especially speedwise)
nobody was patient no matter what I'd achieved so far (understandably, they needed that piece of software very much).

Now, after this coming-out, back to your project :)

To decide whether you have to develop something new, you have to consider a few things.

Are your data so special that only a new software would fit?
There could be (open source) software which you can use after minor modifications.  Going this way you probably can have more help (preferably from those who wrote that software) and finish faster.  And, in the end, if your additions are working and useful, you can publish it.  It will earn reputation for you for sure.  (Not to mention that your citation index will be better :)

Are you in really big need for it?
Since the development will take considerable time (and probably money), you have to assess the costs and the gains.  On the costs side, there will not be the database alone: you must be able to use your database (feed it with data and get results from it).  Even if kept simple, it still can double the efforts needed.  Overall, if the gains aren't significantly bigger than the costs, I would not start with it.

Do you have the expertise required to reach your goal?
Hopefully yes, but if not, than your system will be inefficient, flawed or completely useless.  As you progress with your work, you will gain experience of course, but debugging and rewriting badly written code can be much harder than writing it once again.

(I may be too careful here, but I hate half-written software.  And, if your main goal is to gain experience, and you have the necessary resources (including the luxury of going down all the dead-ends), than start with it.  And, if your answer is three yeses, than you still should read a good book on database design and development - it's far broader a field than a short answer could cover.)


SQL Server 2008 R2 Replication not working - my 1st try

Your Snapshot Agent process account does not have sufficient permissions to access the snapshot folder.

In the section Permissions That Are Required by Agents in Replication Agent Security Model, the Snapshot Agent process account requires db_owner in the distribution and publication databases and needs read, write, and modify permissions on the snapshot share.

Have a look at Lesson 2: Preparing the Snapshot Folder for more information.


Support both stored procedures and functions with same TSQL code base?

Sounds like you've already made the decision to use T-SQL for your back-end.  For OWASP reasons, you are correct in that calling only stored procedures from your data access layer is the safest approach to avoid SQL injection.

Stored procedures and functions are similar in that you can pass arguments for the parameter list.  They also can do similar things.  Code re-use is a good thing and will save you in the end when fixing bugs or making enhancements in the future.  So find the logic that is similar and re-factor that code to functions.  If you want to return tables (single result set), use table based functions.  If you want to return single values, use scalar functions.  Note, you won't be able to perform permanent environment changes with functions (no CRUD operations are allowed... CREATE/INSERT, READ, UPDATE, DELETE).  It's also good practice to wrap your stored procedures with error/exception handling and return those exceptions to the server, to let your logging handle them appropriately.  Also note that it's common to return multiple result sets in a single call to a stored procedure (3 SELECT statements for example).  In your application code, you'd handle each table individually in the response.

Here are some differences:


Replicate/Mirror SQL Server schema to slave databases

Neither mirroring nor replicaiton are appropiate here. Mirroring creates identical physical copies of the database. Replication is good at replicating data, but falls short exactly at replicating schema changes, so you would use the very wrong tool for the job.

What you describe is a a problem of change control, not replicaiton. You should approach it as such. My favourite approach is described in Version Control and your Database. Brent Ozar PLF's last video also addresses the same problem: Coping with Change Control Video.


It's generally best to use stored procedures rather than triggers for business rules. Triggers are more appropriate to enforce data integrity rules in cases where declarative constraints can't be used.

Below is an example that uses a stored procedure instead of a trigger. See inline comments for considerations.

CREATE TABLE dbo.tblCustomer(
      UserID int NOT NULL IDENTITY
        CONSTRAINT PK_tblCustomer PRIMARY KEY CLUSTERED
    , Facebook varchar(100) NULL
    , Twitter varchar(100) NULL
    , PhoneNum varchar(20) NULL
    , CONSTRAINT CK_tblCustomer_not_all_null CHECK (COALESCE(Facebook,Twitter,PhoneNum) IS NOT NULL)
    );
CREATE UNIQUE NONCLUSTERED INDEX idx_tblCustomer_Facebook ON dbo.tblCustomer(Facebook) WHERE Facebook IS NOT NULL;
CREATE UNIQUE NONCLUSTERED INDEX idx_tblCustomer_Twitter ON dbo.tblCustomer(Twitter) WHERE Twitter IS NOT NULL;
CREATE UNIQUE NONCLUSTERED INDEX idx_tblCustomer_PhoneNum ON dbo.tblCustomer(PhoneNum) WHERE PhoneNum IS NOT NULL;

CREATE TABLE dbo.tblSales(
      InvoiceID int NOT NULL IDENTITY
        CONSTRAINT PK_tblSales PRIMARY KEY
    , CustomerID int NOT NULL
        CONSTRAINT FK_tblSales_tblCustomer
        FOREIGN KEY (CustomerID) REFERENCES dbo.tblCustomer(UserID)
    , ProductID int NOT NULL
--      CONSTRAINT FK_tblSales_tblProduct
--      FOREIGN KEY REFERENCES dbo.tblProduct(ProductID)
    );
GO

CREATE OR ALTER PROCEDURE dbo.usp_InsertCustomerSale
      @ProductID int
    , @Facebook varchar(100)
    , @Twitter varchar(100)
    , @PhoneNum varchar(100)
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY

    BEGIN TRAN;

    --This statement will error with a 'subquery returned more than one value' if more than one customer already has these alternate keys.
    DECLARE @UserID int = (
        SELECT UserID
        FROM dbo.tblCustomer WITH(UPDLOCK, HOLDLOCK) --locking hint to serialize concurrent insert attempts
        WHERE
               Facebook = @Facebook
            OR Twitter = @Twitter
            OR PhoneNum = @PhoneNum
        );

    IF @UserID IS NULL
    BEGIN
            --insert new customer row and get assigned identity value
        INSERT INTO dbo.tblCustomer(Facebook, Twitter, PhoneNum)
            VALUES(@Facebook, @Twitter, @PhoneNum);
        SET @UserID = SCOPE_IDENTITY();
    END;

    --insert sales row
    INSERT INTO dbo.tblSales(CustomerID,ProductID)
        VALUES (@UserID, @ProductID);

    COMMIT;

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO

--insert customer and sale for customer 1
EXEC dbo.usp_InsertCustomerSale
      @ProductID = 1
    , @Facebook = 'fb1'
    , @Twitter = 'tw1'
    , @PhoneNum = '(111)111-1111';
--insert customer and sale for customer 2
EXEC dbo.usp_InsertCustomerSale
      @ProductID = 2
    , @Facebook = 'fb2'
    , @Twitter = 'tw2'
    , @PhoneNum = '(222)222-2222';

--insert sale only for customer 2 (with no natural key on sales table, nothing will prevent dup sales)
EXEC dbo.usp_InsertCustomerSale
      @ProductID = 2
    , @Facebook = 'fb2'
    , @Twitter = 'tw2'
    , @PhoneNum = '(222)222-2222';

--this will err due to ambiguous customer (customer 1 has phone and customer 2 has facebook and twitter
EXEC dbo.usp_InsertCustomerSale
      @ProductID = 3
    , @Facebook = 'fb2'
    , @Twitter = 'tw2'
    , @PhoneNum = '(111)111-1111';


I see 2 options to implement this. The first would be what you already have, with a minor adjustment, to enforce this part:

  What I'm seeking is a solution where an action (investment) cannot be made for a child the user has no relationship to. In other words, the father/mother can only invest for him/her or his/her child, not for the child of someone else.

The adjustment would be to have a composite key on the action references (child) foreign key. The action (child_id) is nullable but when the value is not null, the foreign key constraint ensures that it references a child of the user that takes the action.

Sample code:

-- design 1
CREATE TABLE user
( user_id INT NOT NULL PRIMARY KEY
) ;

CREATE TABLE child
( user_id  INT NOT NULL,
  child_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (user_id)
    REFERENCES user (user_id),
  UNIQUE (user_id, child_id)      -- this is needed for the FK below
) ;

CREATE TABLE action
( action_id INT NOT NULL PRIMARY KEY,
  user_id   INT NOT NULL,
  child_id  INT NULL,                  -- nullable
  FOREIGN KEY (user_id)
    REFERENCES user (user_id),
  FOREIGN KEY (user_id, child_id)
    REFERENCES child (user_id, child_id)
) ;

Another way would be to rename the child table to dependent / investor / beneficiary (pick a more appropriate name) and store there not only the children but the users themselves as well (so all investors / beneficiaries / dependent persons of a user). This way only one, the foreign key from action to investor will be needed and the column will be not nullable:

-- design 2
CREATE TABLE user
( user_id INT NOT NULL PRIMARY KEY
) ;

CREATE TABLE investor
( user_id     INT NOT NULL,
  investor_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (user_id)
    REFERENCES user (user_id),
  UNIQUE (user_id, child_id)
) ;

CREATE TABLE action
( action_id   INT NOT NULL PRIMARY KEY,
  user_id     INT NOT NULL,
  investor_id INT NOT NULL,
  FOREIGN KEY (user_id, investor_id)
    REFERENCES investor (user_id, investor_id)
) ;

What you have as table child in the first design, can be a view:

CREATE VIEW child AS
  SELECT user_id,
         investor_id AS child_id
  FROM investor
  WHERE user_id <> investor_id ;

As a side effect, with design 2, we don't really need the user_id in the action table (unless for other, not mentioned in the question, or performance reasons). We could remove it and get rid of the composite foreign key as well. The user_id can be found with a join to investor:

-- design 2b
CREATE TABLE user
-- unchanged

CREATE TABLE investor
( user_id     INT NOT NULL,
  investor_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (user_id)
    REFERENCES user (user_id)
) ;

CREATE TABLE action
( action_id   INT NOT NULL PRIMARY KEY,
  investor_id INT NOT NULL,
  FOREIGN KEY (investor_id)
    REFERENCES investor (investor_id)
) ;

CREATE VIEW child AS
-- unchanged

Another way that is more complicated but takes good points from both previous designs and captures all the different entities (persons, users, children, actions) is to use the supertype/subtype pattern.

This essentially adds the following into the design (entity person):

A person is either a user or a child.
(superype with subtypes)
A person can have any number of children.
A child has exactly one parent (user).
(1:n relationship)
A person can be a beneficiary of any number of action (investments).
An action is taken for exactly one beneficiary (person).
(1:n relationship)

Code:

-- design 3
CREATE TABLE person
( person_id INT NOT NULL PRIMARY KEY
) ;

CREATE TABLE user
( user_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (user_id)
    REFERENCES person (person_id),
) ;

CREATE TABLE child
( user_id  INT NOT NULL,
  child_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (child_id)
    REFERENCES person (person_id),
  FOREIGN KEY (user_id)
    REFERENCES user (user_id)
) ;

CREATE TABLE action
( action_id   INT NOT NULL PRIMARY KEY,
  beneficiary_id INT NOT NULL,
  FOREIGN KEY (beneficiary_id)
    REFERENCES person (person_id)
) ;

We don't need the user_id in the action table, since the user of an action can be found by looking in the other tables, it will either be the beneficiary himself (if the beneficiary_id is in the user table) or it will be the user (from user_id in the related the child).


Yes, you are correct in both cases! Actually, it would be best to do both!

First, increase the opLog size (at least 4x, 10x if you have space) and after that use backup method to copy all data to that node (lvm snapshot is best).

During that time what copy takes, your new opLog (what is this time much bigger) will collect all changes and still keep that starting point.


can I select columns based on their name in Mysql?

No, you can't, nor should you even consider it.
This is a variation on ...

Never use "select *" in Production Code

.. and the risk is the same.
At the moment, your table is nice and tidy, with a few columns of limited size.
At some point, someone is going have the "Great Idea" of adding a whole bunch of BLOB fields and populating them with terabytes of video data.  If you're unlucky and those new fields just happen to be prefixed "GPS_", then your code would start retrieving these fields, even though it has absolutely no interest in them.
Always specify the fields that you want to retrieve explicitly.


Why does SQLPackage include my table-valued function in deployments when it hasn't changed?

My suggestion is to review the ignore list in the publish options.
Things like case sensitive/insensitive or columns order in tables can generate this kind of thiing.
Verify with visual studio compare (if it is possible) with the same publishing options what is happening to highlight codes differences.
What happens if you deploy your dacpac a second time? The TVF alter should disappear.
And after 24 hours? If it returned that means that an application or a script have altered or dropped an recreated it with maybe a simple space or carriage return as a difference and then your deploy is going to find again that an alter is needed.
If you can use visual studio for compare, eventually try to compare with notepad++ getting the alter script from the instance and the alter script from the dacpac apply script generated with the sqlpackage.


First log backup after FULL taking lots of space

Taking a FULL backup won't clear out the transaction log entries (unless the database is in SIMPLE Recovery Model). So whatever changes take place between your last LOG backup before the FULL backup (@ 4:30 AM), and the first LOG backup once that process is over (@ 6:30 AM) would be included in that 6:30 AM LOG backup. That is a 2 hour span (you typically do a LOG backup every 30 minutes otherwise) in which you, at the very least, run index maintenance via the IndexOptimize stored procedure.

If you want to test this out, just temporarily disable both the "IndexOptimize" and "Database Integrity Check" steps (meaning, if you are using the CmdExec step type so it can call SQLCMD, just add REM to the beginning of the line in both steps). Let that run for one night and if I am right, it will be very obvious. Then, remove the REM from one of the two steps so that only one of them runs again (maybe start with the "Database Integrity Check" step) and let that run for one night and then you should see how much impact each of those first two steps has on that "first" LOG backup.

If the "Database Integrity Check" step is taking up more than a small portion of that LOG backup, then you might be able to split that initial backup into two (even if not an even 50/50) by doing a LOG backup after the "IndexOptimize" step. If all (or most) of the LOG backup size is from the "IndexOptimize" step, then maybe try to schedule a LOG backup for 4:36 AM to attempt to cut the resulting LOG backup in approximately half (given that you show that first step running for just under 14 minutes).


This sounds like a known issue with SQL Server 2005.  This page from MS details both a patch if you can upgrade, and a workaround if you cannot.


There is a good bit of "it depends" here due to the question being asked about both size and speed. I'm going to take the size out of it, either you have enough memory or you don't.

Talking just speed from now on and assuming DDR3 for the memory type.

The difference in transfer amount (theoretical) between the two is as follows:

DDR3-1333: 667MHz * 2 bits/clock (rise and fall) * 64-bit wide channel = 10.67 GB/Sec
DDR3-1866: 933 * 2 * 64 = 14.93 GB/Sec

This means there is (theoretically) 14.93-10.67 = 4.26 GB/Sec difference. Unfortunately that's just the memory part.

Continuing our way down the pipeline, we're going to hit our memory controller, of which almost all are integrated into the processor. Thus we'll have to look at the processor (and motherboard layout) to see the number of possible memory channels and throughput of the controller.

You can continue to do this for your specific hardware and figure out the theoretical difference.

Since you've specifically asked about SQL Server In Memory technology, having the faster memory will almost-always be better. That's not the whole case, though, since the way IMOLTP works is by using a specific assembly instruction (cmpxchg16b), thus having faster memory may or may not help if the bottleneck is the CPU speed (think 2.1 GHz [high density VM cpu] vs 3.2 GHz [low density high throughput cpu]).

Assuming the same processor and that the CPU is not a bottleneck, there is dual or triple channel memory, all memory slots are balanced, etc., etc., then yes having the faster memory would generally be better.


Return matches that have at least one message

You can do this efficiently and concisely with an EXISTS clause.

SELECT *
FROM match
WHERE EXISTS (
  SELECT 1
  FROM message_log
  WHERE match_pk = match.id
  AND own_account IS FALSE
)
ORDER BY id DESC;

Btw it's really confusing to have match_id, and match_pk in the message_log table. Normally, the convention of _id means you're referencing something else in a different table.

Also, in PostgreSQL we never use character varying(x) unless there is a spec that imposes x you should just be using text.


Raster Data

  The table is to store data collected from a device. The device produces a PNG image as part of an analysis. The PNG consists of 1024 pixels. Each pixel has an associated numeric value. Along with the pixel data are various other fields related to the analysis. Breaking the table into parts doesn't really make sense. The fields are all logically associated with the particular object being analyzed.

That's called a "raster", not a PNG image. A PNG is in fact an RGB[A] raster stored with DEFLATE compression, but what you want to store has nothing to do with it being a PNG. If these terms don't make sense, it's like telling someone you want to store a PDF. That's possible, but likely what they want is to store the underlying text.

Here your underlying text from the PNG is the raster.

PostGIS

PostgreSQL supports storing actual raster types with PostGIS. I suggest checking out the PostGIS Raster Reference. If your rasters are currently encoded in the PNG channels, you can use GDAL to put them in the database

I would suggest checking that out for information on how to query, process, modify, and export raster data.

Native

Even if you decide not use PostGIS, I don't believe the other answers are adequate. As mentioned above, a RGB pixel is a 3-channel compound data type. So you'll need at least three dimensions for the pixel data.

In addition, you'll likely want information on the XY pixel data, or a method to calculate with that meta data later.

You could use a cube to represent the pixel data

-- white and black pixels (RGB)
SELECT ARRAY['(255,255,255)','(0,0,0)']::cube[]

You can use an array to store each channel (likely not useful for processing), and importing and exporting will be difficult.
You could use a type for the pixel., though a record type even of "char" is substantially larger than our solution below.
You could store each pixel as an int and ignore the channels (assuming you have less than four 1-byte channels). If you need to process this will get super ugly very fast, make analysis near impossible, and exporting and querying very difficult.
Or you could create your own DOMAIN, but at this point your basically recreating PostGIS's raster. With this approach you get to store all four bands as a single int. Access those bands with minimal cost, and construct the pixel from the bands. You can easily expand this to int8 if you need 8 bands.

CREATE DOMAIN pixel AS int4;

CREATE OR REPLACE FUNCTION get_band(p pixel, b int)
RETURNS int AS $$
  SELECT ((p::bit(32)<< ((4-b)*8)) >> (3*8))::int
$$ LANGUAGE sql
IMMUTABLE;

CREATE OR REPLACE FUNCTION create_pixel( int, int, int, int )
RETURNS pixel AS $$
  SELECT (
    $1::bit(32) |
    ($2::bit(32)<<8) |
    ($3::bit(32)<<16) |
    ($4::bit(32)<<24)
  )::pixel;
$$ LANGUAGE sql
IMMUTABLE;

-- Returns 128.
SELECT get_band(create_pixel(255,0,128,32),3)::int;

-- You can use this type in an array too
CREATE TABLE f ( ps pixel[] );

As a side note, if using LIDAR you should check out PG Point Cloud


From the documentation:

  A record can have one of the seven formats

  local      database  user  auth-method  [auth-options] host
  database  user  address  auth-method  [auth-options] hostssl
  database  user  address  auth-method  [auth-options] hostnossl
  database  user  address  auth-method  [auth-options] host
  database  user  IP-address  IP-mask  auth-method  [auth-options]
  hostssl    database  user  IP-address  IP-mask  auth-method
  [auth-options] hostnossl  database  user  IP-address  IP-mask
  auth-method  [auth-options]

Your line:

local all all 0.0.0.0/0 md5

.. does not match any of these.

I suspect you want one of these instead:

host all all 0.0.0.0/0 md5

or:

local all all md5


Determining SQL Server Server Edition without database access

If you have access to file system, one way of getting information could be to check available folders under %PROGRAMFILES%\Microsoft SQL Server\xyz

where xyz translates to:

 80 = SQL Server 2000    =  8.00.xxxx
 90 = SQL Server 2005    =  9.00.xxxx
100 = SQL Server 2008    = 10.00.xxxx
105 = SQL Server 2008 R2 = 10.50.xxxx
110 = SQL Server 2012    = 11.00.xxxx
120 = SQL Server 2014    = 12.00.xxxx
130 = SQL Server 2016    = 13.00.xxxx

Further you can analyze the installation summary file located at
%PROGRAMFILES%\Microsoft SQL Server\xyz\Setup Bootstrap\Log\Summary.txt
which will give you package properties that contains information about Installation edition (Enterprise).


For example, if you have 100,000 rows in a table that gets, say, 75%
  read operations, would it be wise to add a filtered index that only
  covers 500 rows? Or 100 rows? What if the filter covers 85,000 of
  those rows?

That would depend on the queries against the table. If query patterns are such that they target a subset of the 500 or 100 rows that your filtered index covers, perfect. On the flip side, it's unlikely that the optimiser is going to choose a filtered index that includes 85% unless it is a covering index for a particular query.

I haven't tested this but I would expect filtered index utilisation to exhibit the same tipping point behaviour as non-filtered indexes.


I have a beginning balance I need to calculate ending balance running total by month rows

You can use the FIRST_VALUE window function along with SUM(pph.debit-pph.credit) to get your desired output.

SELECT
pph.Acct,
pph.Year,
pph.Prd,
pph.begbal,
pph.debit,
pph.credit,
FIRST_VALUE(pph.begbal) OVER (PARTITION BY pph.Acct ORDER BY pph.Year, pph.Prd)
    + SUM(pph.debit-pph.credit) OVER (PARTITION BY pph.Acct ORDER BY pph.Year, pph.Prd) AS endbal
FROM dbo.GL_PeriodPostingHistory pph;

db fiddle link.

Thanks to HandyD for the sample data written as T-SQL.


Slow execution of a subquery with 0 matches

The ORDER BY inside the subquery is rather useless if you are looking for userid.

You should do two things

First rewrite the query (remove the ORDER BY authorId)

SELECT p.*
    FROM Posts p
   WHERE p.authorId IN (SELECT f.userId
                          FROM Followers f
                         WHERE f.followerId = 9
                      )
ORDER BY posted
   LIMIT 0, 20;

Then, add this index

ALTER TABLE followers ADD INDEX followerId_userId_index (followerId,userId);

This should speed things up, even for empty set results.

Give it a Try !!!

You can also do the query using INNER JOIN

SELECT p.*
   FROM (SELECT userId FROM Followers WHERE followerId = 9) f
   INNER JOIN Posts p ON f.userId = p.authorId
ORDER BY p.posted
LIMIT 0, 20;


Run scripts on multiple SQL Server Express instances

you can use powershell script and csv file, to automate most of your work

$csv = Import-Csv C:\sql\db.csv -Delimiter ";"

foreach ($line in $csv) {

    $DBServer = $line.DBServer
    $DBName = $line.DBName
    $CommandText = $line.CommandText

    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Data Source=$DBServer;Database=$DBName ;Integrated Security=True;"
    $sqlConnection.Open()

    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
    $sqlCommand.Connection = $sqlConnection

    $sqlCommand.CommandText = $CommandText
    $sqlCommand.ExecuteNonQuery()
    $sqlConnection.Close()
    $sqlConnection.Dispose()
}

And, sample csv file for it:

DBServer;DBName;CommandText
.;db;BACKUP DATABASE [db] TO  DISK = N'd:\backup\db.bak' WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
ACER;db2;BACKUP DATABASE [db2] TO  DISK = N'd:\backup\db2.bak' WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

This script connect to server instance DBServer, in my case to default, and to db, for backup it can be master, and execute CommandText

It use integrated security, but you can extend script to use sql server password security, you can extend script to take sql commands from file, etc, etc, etc..

You can add all your 170 instances into csv file, and script will execute sql commands against all your servers.

Of course, for production, script must be more complicated, with error logging.


So it was hard for me to determine what your actual question was from the post, but I assume it is that when you execute:

SELECT ref_no,
   CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN 0
   ELSE
      CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
         MAX(amount)
            OVER (PARTITION BY ref_no ORDER BY paid_date ASC
            ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
            AND CURRENT ROW)
      ELSE
         MAX(amount)
            OVER (PARTITION BY ref_no ORDER BY paid_date ASC
            ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
      END
   END
FROM payment

You still get ORA-01428: argument '-1' is out of range?

I don't think this is a bug. I think it is an order of operation thing. Oracle needs to do the analytics on all of the rows returned by the resultset. Then it can get down to the nitty gritty of transforming the output.

A couple of additional ways around this would be to exclude the row with a where clause:

SELECT ref_no,
   CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
   -- Expression 1
      MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC
         ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
         AND CURRENT ROW)
   ELSE
   -- Expression 2
       MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC
         ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
   END
END
FROM payment
-- this excludes the row from being processed
where MONTHS_BETWEEN(paid_date, start_date) > 0

Or you could embed a case in your analytic like:

SELECT ref_no,
   CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
-- Expression 1
      MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC
               ROWS BETWEEN
               -- This case will be evaluated when the analytic is evaluated
               CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0
                THEN 0
                ELSE MONTHS_BETWEEN(paid_date, start_date)
                END
              PRECEDING
              AND CURRENT ROW)
   ELSE
-- Expression 2
       MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC
         ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
   END
END
FROM payment

Explanation

I wish I could find some documentation to back up the order of operation thing, but I haven't been able to find anything...yet.

The CASE short-circuit evaluation happens after the analytic function is evaluated. The order of operations for the query in question would be:

from payment
max over()
case.

So since the max over() happens before the case, the query fails.

Oracle's analytic functions would be considered a row source. If you execute an explain plan on your query, you should see a "window sort" which is the analytic, generating rows, which are fed to it by the previous row source, the payment table. A case statement is an expression that is evaluated for each row in the row source. So it makes sense (to me at least), that the case happens after the analytic.


Leap second in Database system (Postgresql and SQL Server)

Here's a description of PostgreSQL's date handling I found on the pgsql mailing list from January 2012:

  As best I can tell, that document is talking about issues that are
  beyond Postgres' ken.  When you tell us a timestamp value is
  '2012-01-30 21:13:28.097017-05', that's what we store --- whether you
  meant it to be in TAI, UTC, UT1, or whatever is not our concern.
  If you then ask for that date plus one day, we're going to tell you
  '2012-01-31 21:13:28.097017-05'.  We do know about civil time zones and
  daylight savings transitions, and will adjust such answers for those,
  but not about leap seconds.  There's been very little user demand for
  leap-second-aware date arithmetic, and the difficulties with
  extrapolating such arithmetic into the future mean that we're not
  likely ever to try to support it. [emphasis mine]

As Daniel Vérité mentioned in the comments, the bug here isn't that PostgreSQL can't store leap seconds -- it can -- it's that it can't distinguish them from the following second, and can't do maths involving leap seconds.

Also, as dbafromthecold pointed out, some operating systems won't report leap seconds at all e.g. How the Windows Time service treats a leap second. Another example is Google's services, which apply a leap smear, slightly speeding up the server clocks before the leap second so as to skip over the leap second without ending up too far ahead of UTC.

So: it looks like most services are fine with a potential one-second error around leap seconds. If you need better accuracy around leap seconds, AFAICT you will need to implement your own UTC-based clock time.


Summarizing over Outrigger Dimensions Directly (e.g. Total Customers by Demographic by Signup Date)

Conceptually, CustomerSignup is a fact.  But if every Customer has exactly one Signup, you only care about the date, and there no measures associated with it, it might as well just be stored on the Customer dimension.


Use str_to_date() to handle the conversion.

str_to_date('2016-12-4', '%Y-%m-%d');


The original design called for separate enqueue_enabled (allow SEND) and dequeue_enabled (allow RECEIVE). The added complexity (as SSB wouldn't already be complex enough!) did not warrant the benefits, so in the end it was settled for a less granular 'queue enabled'. The metadata though was intentionally left split in order to enable the separation in future. Now, even if there is no plan to split the enable/disable in SEND/RECEIVE, the view has to stay like that because of back compat issues.

From your point of view: the two values should always be in sync (both 0 or both 1). Altering the enabled/disabled status on the queue should change both values at once.


How to return rows between two datetimes stored as date and time?

It is impossible for any row to satisfy both [TIME] >= '21:00:00' and     [TIME] <= '05:00:00'

SQL server doesn't read your source code comments to know that the time should be applied conditionally.

You need to change the code in order to apply these predicates (which will be residual predicates assuming a seek on date) conditionally on the boundary dates so the time element is only relevant on the days these conditions apply to.

SELECT *
FROM [DB].[dbo].[TABLE]
WHERE [USER_ID] = '005'
AND [DATE] BETWEEN '20160403' AND '20160404'
AND ([TIME] >= '21:00:00' OR [DATE] > '20160403')
AND ([TIME] <= '05:00:00' OR [DATE] < '20160404') ;

or the equivalent (perhaps more self-documenting though you would need to check the execution plans for efficiency):

SELECT *
FROM [DB].[dbo].[TABLE]
WHERE [USER_ID] = '005'
AND ( [DATE] = '20160403' AND [TIME] >= '21:00:00'
  -- OR [DATE] > '20160403' AND [DATE] < '20160404'
   OR [DATE] = '20160404' AND [TIME] <= '05:00:00'
    ) ;


Without the explanation, always use CREATE TABLE AS without exception. At the bottom of each under NOTES this is cleared up,

Notes for SELECT INTO,

  CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.

Notes for CREATE TABLE AS,

  This command is functionally similar to SELECT INTO, but it is preferred since it is less likely to be confused with other uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a superset of the functionality offered by SELECT INTO.

Also in the Compatibility section of the docs  of SELECT INTO it goes even further,

  The SQL standard uses SELECT INTO to represent selecting values into scalar variables of a host program, rather than creating a new table. This indeed is the usage found in ECPG (see Chapter 34) and PL/pgSQL (see Chapter 41). The PostgreSQL usage of SELECT INTO to represent table creation is historical. It is best to use CREATE TABLE AS for this purpose in new code.

So we have,

PostgreSQL thinks it's confusing because SELECT INTO does other stuff in contexts only available in PL/pgSQL, and ECPG.
CREATE TABLE supports more functionality (I assume they're referring to WITH OIDS, and TABLESPACE, IF NOT EXISTS).
SELECT INTO for table creation is "deprecated".

As a side note, the syntax for a CTAS with a CTE may look a bit weird., and SELECT INTO may also be some kind of hold over QUEL's RETRIEVE INTO. QUEL was the predecessor to SQL, that the predecessor to PostgreSQL (INGRES) used.


Filter Condition Difference - Where Clause vs Join Condition

1) will show student names that begins with "A" and whose location is New York.
2) will show all student names that begin with "A" , 'New York' if student state is New York, or null in other cases ( there is no corresponding state, or student state is not New York)

Difference between (1) and (2) - (1) will not have non-New York students.


MySQL Master Slave Replication where master is not allowed to talk to slave

All that is required is for the slave to be able to establish and maintain a connection on the master's MySQL TCP port (3306 by default).  There is no need for the master to be able to establish a connection to the slave.  If you can telnet from the slave to port 3306 (or whatever port mysqld is running on) on the master, that should be all the networking you need.

There are a lot of guides, but the best one is the MySQL manual, see Chapter 17 Replication.


How to pull number code from strings buried in parentheses?

If the data you need is always at the end of the string, would something like this work?

declare @test varchar(200);

set @test= 'Many Widgets (2x) (4567)';

select substring(@test,
                 (len(@test) - charindex('(',reverse(@test))) + 2,
                 charindex('(',reverse(@test)) - 2
                );

We can reverse the string, find the first open paren (which is really the last open paren), and go from there.


Not able to delete transaction log backup file

The underlying partition was initialized using MBR (master boot record) instead of GPT (GUID partition table), and you've broken the 2TB file size limit.

I'm willing to bet that if you ever replay that log backup, it will fail because the backup would have stopped writing when it reached the 2TB boundary.

You will need to reinitialize the partition using GPT. As far as I recall, this is a destructive operation, meaning that you will lose any data on the drive, so back up all files on the D: drive (except that big one) before doing anything else.

Once it is reinitialized, format it as NTFS with 64KB block size (perfect for SQL Server backups), and turn on backup compression inside SQL Server to prevent this kind of problem in the future.

If SQL Server is using this drive for its data files as well, you will definitely need to arrange some downtime for this maintenance. Good luck.


Joining Tables in the FROM vs. the WHERE clause

Both queries are exactly the same, should return exactly the same result set and should produce exactly the same Execution plan.

BUT the second query is using ANSI-92 SQL syntax . The first one is using the older (very old) version of SQL Joins.

I can think of a few reasons why you should use the ANSI-92 SQL Syntax.

It separates the Join Conditions and the actual filters in the WHERE clause if you have any.
With the old syntax, you can only have the equivalent of INNER and CROSS joins. Outer joins were not possible except with complicated UNION queries. Some DBMS had added proprietary syntaxes for outer joins, (+) in Oracle, *= and =* in SQL Server, but these are being or have been deprecated. The explicit LEFT, RIGHT and FULL outer joins were added in the standard and have been adopted from almost all DBMS.
It make query much more readable hence easier to debug.
Now days most of the developers, DBAs and also ORMs use the Join syntax.
When using ANSI-92 SQL Syntax for joins if you miss out a join condition you get a compile time error, hence it forces you to provide a valid join condition whereas in older version it doesn't throw any error but it simply returns a CROSS JOIN, in my experience this is one of the worst cons of the older approach.
When using older version of joins and your query is mixing inner join and outer joins the result set may always not what you expect it to be.


More efficient way to write multiple "AND"?

Not sure if that's is faster than what you have, but you can try

SELECT tableA.NO
FROM tableA
WHERE tableA.NO
IN
(
   SELECT GRI_NO FROM tableBB WHERE GRO_NO IN (62,50,70)
   GROUP BY GRI_NO
   HAVING COUNT(DISTINCT GRO_NO) =3
)


How to enhance speed of UPDATE statement

Change the temp table to:

CREATE TEMPORARY TABLE cons_temp (
    PRIMARY KEY(consultant)
    ) ENGINE=InnoDB
    SELECT DISTINCT consultant_id, consultant
          FROM testdata_biactivtity_rob;
There is no need for the create table to be in the transaction, so start with

SET autocommit=ON;

This will isolate the "wait timeout" to the UPDATE.


Since the MyISAM storage engine only caches indexes, reads from .MYI files are the norm. If the .MYI file of a given MyISAM table never changes, you have to look into external factors.
Just keep in mind that if the index blocks for a MyISAM table are not in the Key Cache, it must be read from disk, then loaded into the Key Cache.
EXTERNAL FACTOR #1 : Disk
If the disk blocks that hold the .MYI file become corrupt, there may be an error reading index blocks. Doing the REPAIR TABLE must have written the rebuilt .MYI elsewhere on disk.
EXTERNAL FACTOR #2 : Memory
If the memory chips become defective and parts of the MyISAM Key Cache is in use there, the .MYI file could possibly be considered corrupt when reading index blocks. Doing the REPAIR TABLE creates a new .MYI and is freshly read into the MyISAM Key Cache.
SUGGESTION
To reduce or eliminate the amount of reads from the .MYI file of a MyISAM table, you should look into setting up a dedicated Key Cache just for this table.
Since your table has a 1.4GB .MYI file, make a 1.5GB  dedicate key cache for it:
USE mydb
SET GLOBAL my_table_keycache.key_buffer_size = 1536 * 1024 * 1024 * 1024;
CACHE INDEX mydb.my_table INTO my_table_keycache;
LOAD INDEX INTO CACHE my_table_keycache;

I have suggested this before

Sep 14, 2012 : Can I partially invalidate the MySQL table cache?
Mar 20, 2012 : optimal table design mysql with primay key and varchar value
Jan 26, 2012 : Less RAM than Index_length MyISAM

Doing this will also let you know quickly if the external factors apply in the future.


disable warning from `drop table if exists`

The "warning" is in fact a "notice". You can disable the display of those notices trough the property client_min_messages  which controls the minimum level of messages returned to the client.

To only display warnings, but not notices use:

set client_min_messages = warning;

Note that this will disable all notices, including those sent through raise notice from e.g. a PL/pgSQL function.


Similar concept in Oracle is called Index-Organized Tables (IOT).

Difference with PostgreSQL CLUSTER is that in PostgreSQL CLUSTER command reorganizes table once and later table still grows as it wants to. In Oracle IOT keeps its structure as ordered by index.

Unlike Phil I'm seeing IOTs now and then. The most often they are used when you need to retrieve many (think hundreds) rows by index.


If you were using SQL Server, what you are looking for is something like:

USE tempdb;
CREATE TABLE MenuHeaders
(
    MenuHeaderID INT NOT NULL CONSTRAINT PK_MenuHeader
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , MenuText VARCHAR(255)
);
CREATE TABLE MenuSubHeaders
(
    MenuSubHeaderID INT NOT NULL CONSTRAINT PK_MenuSubHeader
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , MenuHeaderID INT NOT NULL CONSTRAINT FK_MenuSubHeader_MenuHeaderID
        FOREIGN KEY REFERENCES MenuHeaders(MenuHeaderID)
    , SubMenuText VARCHAR(255)
);
CREATE TABLE MenuItems
(
    MenuItemID INT NOT NULL CONSTRAINT PK_MenuItems
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , MenuSubHeaderID INT NOT NULL CONSTRAINT FK_MenuItems_MenuSubHeaderID
        FOREIGN KEY REFERENCES MenuSubHeaders(MenuSubHeaderID)
    , MenuItemText VARCHAR(255)
    , MenuURL VARCHAR(2048)
);
CREATE TABLE Users
(
    UserID INT NOT NULL CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , UserName VARCHAR(255)
);
CREATE TABLE UserGroups
(
    UserGroupID INT NOT NULL CONSTRAINT PK_UserGroups
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , UserGroupName VARCHAR(255)
);
CREATE TABLE UsersGroupsXRef
(
    UserID INT NOT NULL CONSTRAINT FK_UserGroupsXRef_UserID
        FOREIGN KEY REFERENCES Users(UserID)
    , UserGroupID INT NOT NULL CONSTRAINT FK_UserGroupsXRef_UserGroupID
        FOREIGN KEY REFERENCES UserGroups(UserGroupID)
    CONSTRAINT PK_UsersGroupsXRef PRIMARY KEY CLUSTERED
);
CREATE TABLE UserGroupsMenusXRef
(
    UserGroupID INT NOT NULL CONSTRAINT FK_UserGroupsMenusXRef_UserGroupID
        FOREIGN KEY REFERENCES UserGroups(UserGroupID)
    , MenuHeaderID INT NOT NULL CONSTRAINT FK_UserGroupsMenusXRef_MenuHeaderID
        FOREIGN KEY REFERENCES MenuHeaders(MenuHeaderID)
);

This allows you to have Menus linked to Sub-Menus, with Menu-Items linked to Sub-Menus.

Users can be members of UserGroups, and UserGroups have associated Menus.

MenuItems have a URL field.


Twice job results in sql server, last one is left in progress

This will give you the desired results :

select h.server as [Server],
    j.[name] as [Name],
    h.message as [Message],
    h.run_date as LastRunDate,
    h.run_time as LastRunTime
from msdb.dbo.sysjobhistory h
    inner join msdb.dbo.sysjobs j on h.job_id = j.job_id
        where j.enabled = 1 -- only check for enabled jobs
        and h.instance_id in
        (select max(h.instance_id)
            from msdb.dbo.sysjobhistory h group by (h.job_id))
        and h.run_status != 1
/*0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled*/


In Search of FILESTREAM Insider Information

When the FILESTREAM feature is activated on Microsoft SQL Server 2012 then SQL Server will create a "hidden" share on the system.

It does not do this by default, you have to CHOOSE to enable the share. This is done via SQL Server Configuration Manager. If you deselect the Enable FILESTREAM for file I/O access the share will be removed.

  It's nice to know that SQL Server has everything nice and tied up, but what does that share actually do?

The share allows for clients (local and remote) to have a singular shared location to use the streaming windows api for access to filestream data. This works in conjunction with the SQL Server Instance level settings for filestream access of Full Access Enabled, any other access setting should not work with the streaming API.

  ... Is it the so called "file system filter driver"?

No, it is not. This is just a file share.

I was trying not to muddy the waters but you did ask for as much information as possible. In the above strikethrough text I did, in fact, say that this was not the filter driver. However that technically is a half truth. Yes, it is a shared folder but it actually shares through the filter driver. I really debated about this because it starts becoming a rabbit hole that you really can't go down without the source code (and to be honest it's of little value other than academic in my opinion).

The whole point of the filter driver is to do a few things, but the one of those things is to give transactional access to the data stored in the filestream target via a variety of interfaces; SQL Server, Transact SQL, Windows APIs. It also does a handful of other items - however the access given through the share is done via the filter driver. In fact, if you attempt to access files in a filestream and are not an administrator or SQL Server you shouldn't be able to access them.

So, yes this both is and is not the filter driver. It's half a windows fileshare that is exposed through a filter driver. You can see this is you view the path property of the share.

get-wmiobject -class Win32_share | where {$_.Description -like 'SQL Server*'} | ft name, path -autosize

  2.Seeing as any authenticated user can access the "share", what are the security implications?

You can change the permissions and requires the settings to be properly set. The security implications are that of any other file share.

  3.Is the Device RsFx0320 a predecessor to the resilient file system format that was introduced with Windows Server 2012?

No, this is the name of a specific version of the filter driver. For example, here is a system with the 2016 one loaded RsFx0410. ReFS is a file system, this is a filter driver that sits between the filesystem and the miniport driver. It's actually quite disconcerting that this is a legacy filter driver as denoted by the .10 at the end of the altitude... hmm. You'll also notice it has quite a low altitude, which is generally not acceptable for 3rd party filter drivers.

  If you can supply answers to my questions, then it would be nice if you could provide a source reference.

I have no sources for this but have backed up my information through screenshots and configuration options that change settings. Everything in this answer can be found by looking through the product itself and knowing how pieces of windows work (ex: filter drivers).


Does MongoDB support two 2-way synchronization

No, MongoDB does not support two-way synchronization. It has master-slave replication, where all writes must occur on the master node before being replicated to the slave nodes. You can read about all the replication capabilities of MongoDB in their documentation.


Dilemma of Generating tables dynamically to improve performance

Hope you have store information about  House 1 and member detail somewhere.

Moreover purpose of Result Table is not clear to me.

Normally when you have slow running query  then we take following steps :

i) Denormalize database : So that it reduce table join.At the same time don't disturb normalize table.So I will put SubmitDate in Chunks table

ii) Optimize query :It depend upon your query.what need to be optimize ?

iii)Introducing proper indexing

iv) Paging : As you said.

v) Partition Table

These are few step  from the  the best practices list and follow database design pattern.

  Disadvantage of dynamic table.

i) I am not in favor of creating dynamic table like "Survey_311" because it sound like any design pattern.

ii) There will be several other kind of reports where you will at disadvantage like consolidating them will be problem.

iii) I see lot of dynamic Sql because of dynamic table.

iv) Database will be heavier very soonAlso there will be lot of indexes.

v) Maintainability : From both DBA and developer point of view.


Does taking a log backup, truncating the log, and then deleting the tlog backup break the LSN chain?

If the avamar tool uses its own tracking method to know what log backup files it can restore, then it will fail.

If it uses SQL internal tables to know what log backup it needs, that is good, but if the file has been deleted, then it will fail.

Either way it will fail since you are deleting the files...so don't :)


how to correctly return values from a function

You should return something when nothing is found. Before exception comment in the statement.
The way your routine is written it may does find nothing and then hits the end of the function without returning anything, hence the error function returned without value.
Workaround :
Handle it with the exception

exception
   when TOO_MANY_ROWS then
        return 'Error bla bla '; -- do something meaningful here
   when NO_DATA_FOUND then
        return null; -- if you want to return null on nothing found


Investigating an Oracle 12c outage after the fact, while limited to SQL*Plus

You can inspect V$SQL for statements that don't use binds, eg.

select substr(sql_text,1,80), count(*)
   from v$sql
   group by substr(sql_text,1,80)
   order by count(*) desc

Then inspect the full text of the statements that come on top, see where they differ.


How to store list of unknown size in database

You should keep your waypoints (midway cities) in a separate list, with the trip key and the sequence of the stop as the primary keys.  Consider the following ERD:

This lets you keep the initial origin and the final destination in your trip table and it lets you add zero or more waypoints along the way without storing any redundant data (i.e. the schema is in third normal form).


Updating/Inserting 2000 entries takes over 12 hours to complete

I'm going to give this my best guess, but I will preface saying that we don't have intimate knowledge of your schema, and while I am knowledgeable about MySQL, I would not call myself an expert.

Index-organized tables

One problem you might be having is due to the way in which MySQL stores data, along with your UPSERT behavior.  Using the InnoDB engine in MySQL, every table is an index-organized table.  This means that the data payload is stored directly within the pages of the index. In effect, the primary key index is also the table structure.

Now, in your case, you are inserting rows with a total of 42 columns.  Without knowing the table structure, I am guessing the some of these 40 unknown columns are of variable length.  So each time you update the data, there is likely to be quite a lot of data movement due to this. InnoDB has to rewrite the index and data pages in order to accommodate the new data.

Change your schema?

So, based on this likely limitation you are seeing, I think one thing you should consider is a schema change.  What you have now is essentially one big flat table.  But, you've stated that you receive only 10 of these 40 unknown columns at a time, so why don't you have at least 5 tables? That is (in loose pseudo code, and make sure to add your FK relationships):

CREATE TABLE record (RecID, DATE, NAME);
CREATE TABLE column_set_1 (RecID,COL1,...,COL10);
CREATE TABLE column_set_2 (RecID,COL11,...,COL20);
CREATE TABLE column_set_3 (RecID,COL21,...,COL30);
CREATE TABLE column_set_4 (RecID,COL31,...,COL40);

This way, you are only performing inserts, and only into the relevant tables without touching all this other data.

Table Partitioning

Also, because you have a DATE field, it may be helpful to partition your table.  I can't say for sure if this will have a strong positive impact, but I suspect it may.  If you go for my suggested schema change, for example, I might choose to hash partition the 4 column_set_* tables based on RecID and then the INSERT...ON DUPLICATE KEY UPDATE can make use of partition lock pruning.

Best of luck, and maybe someone with better MySQL expertise can correct any mistakes I've made.


How to retrieve missing values of an id from a list?

I don't fully follow your examples, if you could clarify them a little it might help, but in general it sounds like you're trying to find what data exists in one table but not another? If so, then you want to use a LEFT JOIN and a WHERE clause that filters on the missing records like so:
SELECT *
FROM TableWithAllRecords
LEFT JOIN TableMissingRecords
    ON TableWithAllRecords.KeyField = TableMissingRecords.ForeignKeyField
WHERE TableMissingRecords.ForeignKeyField IS NULL


This is an outstanding bug in SQL Server which has been ignored for quite some time, unfortunately.
You can track down the offending statistic and drop/recreate it, or as some others in the above thread have suggested:

Clearing the userstore_dbmetadata related pools on ASYNC commit resolves the issue.
DBCC FREESYSTEMCACHE (‘dbname’)

Or create a stored procedure to check if the instance is an ASYNC commit, then clear the cache using an agent job.

CREATE PROCEDURE [dbo].[sp_clear_dbmetadatacache]
AS
SET NOCOUNT ON
DECLARE @dbname SYSNAME
DECLARE @linebreak AS VARCHAR
SET @linebreak = CHAR + CHAR

CREATE TABLE #dbcc
(
command VARCHAR
)

DECLARE db_cursor CURSOR FOR
SELECT DISTINCT name
FROM sys.sysdatabases db
INNER JOIN sys.dm_hadr_database_replica_cluster_states dbcs
ON db.name = dbcs.database_name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO dbname WHILE @FETCH_STATUS = 0
BEGIN
CREATE TABLE #avmode
(
availability_mode_desc VARCHAR
)
DECLARE value AS VARCHAR(20) DECLARE @sqlcmd2 AS NVARCHAR(150) SET @sqlcmd2 = 'SELECT DISTINCT availability_mode_desc FROM sys.availability_replicas WHERE replica_server_name = @SERVERNAME’
INSERT INTO #avmode
EXECUTE sp_executesql @sqlcmd2
SELECT @value = availability_mode_desc
FROM #avmode
BEGIN
IF @value = ‘ASYNCHRONOUS_COMMIT’
GOTO A
ELSE
GOTO B
END
A:
DECLARE @sqlcmd AS NVARCHAR
SET @sqlcmd = ‘DBCC FREESYSTEMCACHE (’‘’ + @dbname +’‘’) ’+ @linebreak + ’GO’
INSERT INTO #dbcc EXECUTE sp_executesql @sqlcmd
—PRINT @sqlcmd
B:
DROP TABLE #avmode
FETCH NEXT FROM db_cursor INTO @dbname;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;

GO


Select/Join multiple row values from Column A if dates in Column B are within 'x' seconds of each other

You can join the software table to itself and use DATEADD() for the 5 second condition:

SELECT
    c.PC_Hostname,
    a.NAME_Reg     AS Name,
    b.NAME_Reg     AS Dept,
    a.DATE_Reg,
    b.DATE_Reg     AS DATE_Reg_b
FROM
    assets.software AS a
  JOIN
    assets.software AS b
  ON    a.PC_ID  = b.PC_ID
    AND a.SWType = 8
    AND b.SWType = 7
    AND a.DATE_Reg >= DATEADD(second, -5, b.DATE_Reg)
    AND a.DATE_Reg <= DATEADD(second, +5, b.DATE_Reg)
  LEFT JOIN
    assets.computers AS c
  ON   a.PC_ID = c.PC_ID
WHERE
      a.NAME_Reg IS NOT NULL
  AND b.NAME_Reg IS NOT NULL
  ;


PostgreSQL: restrict a GUC variable to only be set in proconfig?

There's no flag for it, though it's an interesting idea.

If proconfig is non-null then the security-definer function call path fmgr_security_definer is invoked. Interestingly this means that using function-level SET has a higher overhead, which I didn't expect. That path can be entered if a fmgr hook is needed or if the function is actually SECURITY DEFINER so you can't use that alone for testing how a var is set.

Applying proconfig creates a new GUC nesting level with NewGUCNestLevel then applies the GUC array with ProcessGUCArray, passing flags PGC_USERSET (for non-superusers) and PGC_S_SESSION. Those are the same flags passed to a normal SET so it doesn't look like there's any information cleanly available to GUC callbacks to tell if they're called in this context.

However:

It looks like you can hook function entry/exit in the fmgr. See FmgrHookIsNeeded, needs_fmgr_hook and fmgr_hook. A demonstration of use of the fmgr hook infrastructure can be found in contrib/sepgsql. This would let you apply custom GUCs that're otherwise defined as not settable at session level. You couldn't put them in proconfig though, since the fmgr will try to apply them and fail; you'd have to store them elsewhere. You can't apply them and remove them from the proconfig array because it's cached and you really don't want to go modifying things returned from the syscache. Anyway, the fmgr hook is fired just after GUCs are applied.

I'd look into doing it with a fmgr hook.


Enabling "log_queries_not_using_indexes" disables "long_query_time"?

That is expected behavior.  Refer to the docs online, but in summary:

long_query_time is the threshold for query execution time beyond which it is logged.  Any queries taking longer than the threshold are logged, regardless of whether they use an index or not.
log_queries_not_using_indexes tells MySQL to additionally log all queries that do not use an index to limit the number of rows scanned.  Logging on this condition happens regardless of execution time.

Hope that helps explain what you are seeing.  It seems like you probably just want long_query_time and not log_queries_not_using_indexes if your goal is to only capture queries that take longer than a particular threshold.

Doc links:
http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-queries-not-using-indexes

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_long_query_time


Looking for an efficient way to clone tens of millions of database records

Simple approaches:

If your tables are indexed, drop the indexes and put them back after you load the data.  It's generally going to be faster for MySQL to build the index on a table in one pass than to continually index the incoming data as you add it.
Run more than one copy of your script, each with a subset of the data source files, doing inserts in parallel
Enable compression if you're using InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-compression.html  Typically we think of compression as making things smaller with a trade-off in speed... but my experience with InnoDB compression is that it can actually perform faster when you're dealing with a server that is blocking on I/O but has spare CPU capacity, because you can trade some of one for some of the other -- the time saved by not having to do disk read/write improves performance more than the extra CPU cycles for compress/decompress harms performance.

At some point, of course, you're always going to max out either CPU or I/O on your server, but running more than one process in parallel will help to some extent, especially if the rows aren't all going into just one table.  If they are (I'm not familiar with the data set in question) then you do have a potential problem there, since some of these approaches won't help, but if not, then once you max your machine, you still have the option of marshaling multiple servers to do the work of initially creating the tables, and then copy them over to the final destination when you're done.

With MyISAM tables, the table spaces are intrinsically transportable from server to server.  You can create the tables on one or more entirely different servers; run FLUSH TABLES WITH READ LOCK; and then (while keeping the tables locked by not closing your connection) you can actually copy the .frm and .MY* files from one server to the other.  When you run SHOW TABLES after copying the files into the directory for the appropriate database, and setting the permissions, they just magically appear and are accessible, because MyISAM does not have an internal data dictionary.  Everything is in those files.

In MySQL 5.6, InnoDB tablespaces became transportable also; it's not quite as simple as MyISAM, because the files have to be prepared first: http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html ... but still potentially a viable way to get more work done in parallel.

You could also also leverage cloud servers to create and index these tables for you and then copy the tablespace files back to your production system, assuming you have sufficient bandwidth available to actually transfer the files... and you're willing to gamble just a little bit... the spot market price (for idle cloud capacity) for some very serious hardware (multi-core, with high-capacity SSDs, etc.) can be less than 1/10th of the on-demand (normal) price... if you go that route, you have to automate the process to move the completed files off of the cloud server as expeditiously as possible, since the machine can be interrupted at any time if the spot price goes above the amount you're willing to pay.


how to add muliple rows with insert in order

I believe that, in most cases, your specific example (two rows inserted via a single INSERT ... VALUES statement, will not produce the results you're asking for. If you listed the names in alphabetic order, then they'd get assigned as "a" = 1 and "b" = 2 - as you've shown it, you'll get "b" = 1 and "a" = 2.

That said, as others have noted, why do you care? Is it important somehow that the rows get specific ID values, or that ID values are sequential, with no gaps?

If so, then an auto-increment key may not be your best solution.

An auto-increment key works best when you don't care what the ID is, just that it's unique. As your primary key, an auto-increment column simply ensures that each new row will have a unique ID value, nothing else. That ID value may be used to tie rows in other tables back to this table; that's all the value needs to do if that's the case. If you want data from both tables, use a JOIN when you select them.

Often, your data may have other values that provide some sense of order (for instance, the name itself). Don't confuse order with the ID value; they're two separate things.

Yes, when a table has a clustered index, then the data is stored in order based on that index (which is usually, but not always, the primary key). However, that's only really useful for putting queries together.

The order of the data returned by a query is only guaranteed to be meaningful if the query has an ORDER BY clause. Just because the data is stored in a particular order does not mean that's the order the results of a SELECT query will come back in.

Plus, of course, if name in your first two rows happen to be "Aaron Aardvark" and "Zeb Zebra", and these get IDs 1 and 2, respectively - what happens when a third row with name "Penelope Parrot" is added? Now, the IDs are out of alphabetic order by name.

In the very limited case of:

You'll only load data into your table once; and
For some reason, you need the IDs to match the order of some other column in the table;

You can try sorting the data based on that column, and loading it in like that. From a single INSERT statement, the only INSERT you ever intend to run, the data should wind up getting assigned IDs in whatever order the data is presented.

That said, it's not the best way to run a database.


Capture SQL Server queries without third-party tooling and without using deprecated features?

You could use Extended Events to capture this data. However, depending on how much traffic your server gets this could become a lot of data very quickly and could result in performance issues.

I would look at limiting the events that you track to something like sqlserver.rpc_completed or sqlserver.sql_statement_completed, these only capture what was completed. Erin Stellato wrote a great piece on useing XEvent Profiler to capture queries in SQL Server.

From SSMS, you would go to Management > Extended Events > Session and either use the New Session Wizard or New Session to start building your session to track data. The Microsoft Docs go into a lot of detail on how to set this up.

Here is an example of something I implemented recently to capture queries on a specific server, you can add filters to remove queries that you don't want to see - like some server names or application names that execute queries as well:

CREATE EVENT SESSION [Track Queries] ON SERVER
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
    WHERE (NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_hostname],N'%name%')
            AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_hostname],N'name')
            AND NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%name%')
            AND [sqlserver].[server_principal_name]<>N'<username>')),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
    WHERE (NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_hostname],N'%<name>%')
            AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_hostname],N'name')
            AND NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%name%')
            AND [sqlserver].[server_principal_name]<>N'<username>'))
ADD TARGET package0.event_file(SET filename=N'D:\XE\TrackQueries.xel',max_file_size=(5120))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

This captures the details of what has been executed and puts it into a file for easy querying and analysis.


How to Enable Windows Authentication in Firebird 2.5

Few basic things things to check -

Check user name length. Like in few other databases, this could be failing if the Windows user name length is greater than the permitted limit. From FireBird's documentation this seems to be 31.
Try using Trusted mode and if needed enforce Windows authentication.

Is this a web application connecting which is connecting to this database? If so what's the browser being currently used.


To the best of my knowledge you can't change the dbo user without changing the entry in sys.databases.  This is a case where the same piece of data (the owner of the database) is set it in two places.  While they do occasionally become out of sync they mean the same thing.

If you need someone to have database owner access then just add them to the db_owner role. Really the main difference between dbo and members of db_owner is that members of db_owner can be denied permissions while the actual dbo can't.


How to make postgresql SUM to be more accurate on large amount of floating point data?

According to the documentation of the REAL datatype http://www.postgresql.org/docs/9.2/static/datatype-numeric.html#DATATYPE-FLOAT it has a precision of 6 decimal digits. That's what you are seeing. If you need more precision you need to use double or a number with fixed precision.

Maybe casting the reals to something more precise and summing the result will give you what you need, eg:

SELECT SUM(metric::double) FROM metrics;