Explain plan gives different results depending on schema

In general, if 2 queries are identical, they will have the same sql_id, and as a result, the same execution plan (which may change over the time, but it shouldn't depend on which user executes the query).  (** It's oversimplified, because starting with 11g Oracle can generate multiple plans based on values of bind variables, presence of histograms ).

So first thing I'd check if they're really identical - run both of them, and check v$sql/v$sql_area .

Another thing is OPTIMIZER_MODE. When it's set to FIRST_ROWS(N), optimizer prefers NESTED LOOPS over HASH/MERGE JOINS ; when it's ALL_ROWS , HASH JOIN is more preferable for optimizer.  It's quite common that different users set different optimizer mode , FIRST_ROWS is great for OLTP applications; ALL_ROWS is more suitable for DSS.

You can also check whether any outlines are used.

If you're on Oracle 11 and higher, check IS_BIND_AWARE, IS_BIND_SENSITIVE values in v$sql.

Finally, always check actual execution plan with dbms_xplan.display_cursor, don't rely too much on the output of EXPLAIN without executing the query :
 - run query
 - find sql_id in v$sql
 - check plan SELECT * FROM table(dbms_xplan.display_cursor('[sql_id from previous step'],null));


Insert into table if column  "Text" for each project

You can do everything needed in a single trigger.
For example, given the tables:
CREATE TABLE dbo.Projects
(
    ProjectID integer NOT NULL,
    [Status] character(1) NOT NULL,

        CONSTRAINT [PK dbo.Projects ProjectID]
            PRIMARY KEY CLUSTERED (ProjectID),

        CONSTRAINT [UQ dbo.Projects ProjectID, Status]
            UNIQUE NONCLUSTERED (ProjectID, [Status]),

        CONSTRAINT [CK dbo.Projects Status]
            CHECK ([Status] IN ('A'))
);

CREATE TABLE dbo.ProjectPeriods
(
    ProjectID integer NOT NULL,
    [Status] character(1) NOT NULL,
    [Period] character(6) NOT NULL,
    PeriodCheck varchar(5) NULL,

        CONSTRAINT [PK dbo.ProjectPeriods ProjectID, Period]
            PRIMARY KEY CLUSTERED (ProjectID, [Period]),

        CONSTRAINT [FK dbo.Projects ProjectID, Status]
            FOREIGN KEY (ProjectID, [Status])
            REFERENCES dbo.Projects (ProjectID, [Status]),

        CONSTRAINT [CK dbo.Projects Period]
            CHECK ([Period] NOT LIKE '^[0-9]'),

        CONSTRAINT [CK dbo.Projects PeriodCheck]
            CHECK (PeriodCheck = 'Later')
);

The following trigger creates the twelve periods and the 'Later' entry if they are missing. It also removes any entries that shouldn't be there.
CREATE TRIGGER [dbo.Projects AI]
ON dbo.Projects
AFTER INSERT
AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;

    MERGE dbo.ProjectPeriods AS PP
    USING
    (
        SELECT
            I.ProjectID,
            I.[Status],
            [Period] = ISNULL(CONVERT(character(6), DATEADD(MONTH, Months.n, CURRENT_TIMESTAMP), 112), '205001'),
            PeriodCheck = CASE WHEN Months.n IS NULL THEN 'Later' ELSE NULL END
        FROM Inserted AS I
        CROSS JOIN
        (
            VALUES
                (01), (02), (03), (04), (05), (06),
                (07), (08), (09), (10), (11), (12),
                (NULL)
        ) AS Months (n)
    ) AS PD
        ON PD.ProjectID = PP.ProjectID
        AND PD.[Period] = PP.[Period]
    WHEN NOT MATCHED BY TARGET
        THEN INSERT (ProjectID, [Status], [Period], PeriodCheck)
        VALUES (PD.ProjectID, PD.[Status], PD.[Period], PD.PeriodCheck)
    WHEN NOT MATCHED BY SOURCE
        AND PP.ProjectID IN (SELECT I.ProjectID FROM Inserted AS I)
        THEN DELETE;
END;

Executing:
INSERT dbo.Projects (ProjectID, [Status]) VALUES (1, 'A');
INSERT dbo.Projects (ProjectID, [Status]) VALUES (2, 'A');

or the equivalent:
INSERT dbo.Projects
    (ProjectID, [Status])
VALUES
    (1, 'A'),
    (2, 'A');

Produces the required outcome:

ProjectID
Status

1
A

2
A

ProjectID
Status
Period
PeriodCheck

1
A
202106
NULL

1
A
202107
NULL

1
A
202108
NULL

1
A
202109
NULL

1
A
202110
NULL

1
A
202111
NULL

1
A
202112
NULL

1
A
202201
NULL

1
A
202202
NULL

1
A
202203
NULL

1
A
202204
NULL

1
A
202205
NULL

1
A
205001
Later

2
A
202106
NULL

2
A
202107
NULL

2
A
202108
NULL

2
A
202109
NULL

2
A
202110
NULL

2
A
202111
NULL

2
A
202112
NULL

2
A
202201
NULL

2
A
202202
NULL

2
A
202203
NULL

2
A
202204
NULL

2
A
202205
NULL

2
A
205001
Later

Online demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8f34f1d9eb00aa1b8603498860a9826d


Interesting problem. I'd use a calendar table:

calendar

calendar_date
--------------
2014-01-01
2014-01-02
...
2014-12-01
2014-12-02
..
2014-12-31
2015-01-01
...

and restructure the pricelist to use closed-open intervals, instead of closed ones:

pricelist

startHour | endHour | price
----------------------------
00:00     | 08:00   | 10
08:00     | 16:00   | 20
16:00     | 24:00   | 5

It would not work exactly as it is above because TIME datatype cannot hold the value of 24:00:00 hours, so you probably have to use datetime. (In the linked example, I used datetime2(0) but I think it would work with datetime as well.

Then you could use this query to check for overlapping intervals between your pricelist (statDate, enddate) intervals and the ones calculated from the calendar and the pricelist values:

SELECT
    startDate = CASE WHEN a.startDate < x.startD THEN x.startD ELSE a.startDate END,  -- max
    endDate = CASE WHEN a.endDate < x.endD THEN a.endDate ELSE x.endD END,            -- min
    a.jobID,
    p.price
FROM actualUse AS a
  CROSS JOIN calendar AS c
  CROSS JOIN pricelist AS p
  CROSS APPLY
    ( SELECT
          startD = DATEADD(day, DATEDIFF(day,'19000101',c.calendar_date), p.startHour),
          endD = DATEADD(day, DATEDIFF(day,'19000101',c.calendar_date), p.endHour)
     ) AS x
WHERE a.startDate < x.endD
  AND x.startD < a.endDate ;

The complicated DATEADD(day, DATEDIFF(day, ...) expression is there for adding a DATE and a DATETIME value:

SQL-Fiddle is down today, but (thank you SO!) we can test at dba.se Data Explorer


Not 100% sure what you are attempting here, but you could record the names of "partial" databases in a table in SQL and then run a separate process after the fact to drop them.


I would upgrade away from MySQL 5.0 altogether.

Please note you are using mysqld-nt.exe

It has been a very long (probably over 1.5 yrs) since the NT-featured mysqld was distributed. The only benefit to using mysqld-nt was if you are using shared memory or named pipes, which were just nuances when MySQL 5.0 came out for Windows.

The MFC C++ runtime distributables have been updated several times over the years. If you have been running updates against Win2003, things are probably very different for mysqld-nt.exe now.

Before upgrading, try using mysqld.exe instead of mysqld-nt.exe. If you still get the same crashing behavior, switch out of MySQL 5.0 for Windows and go to MySQL 5.5 because MySQL Support for Windows 2003 went EndOfLife back in April of 2011. I would also think about using a Windows 7 Server instead of Windows 2000/2003/NT.


Can't do a full backup because the log is full

I assume you don't have space in your drives to take a log backup. You can use the following query to trick SQL server into thinking that you are actually saving the log backup to disk, But you're not.

BACKUP LOG dbname TO DISK = 'NUL:';

Also if you're running SQL server 2005 or less, You can use the below query

BACKUP LOG dbname WITH TRUNCATE_ONLY;

Once the query completes you can go ahead and shrink the log file as usual using

USE dbname;
DBCC SHRINKFILE(fileid , 0);

Also do note that the above backup query will break your log backup chain and your log-shipping setup, if you have one.


If you don't already have one, create a composite nonclustered index on prism72ext.dbo.pb_WorkRequestLog(WorkRequestId, Id DESC) and run the query without the index hint.

Also, have your inner select for the owner derived table return mwl.WorkRequestId instead of ml.WorkRequestId.


is it possible to do a reverse pattern search in a database?

This is what I can come up with (untested).

You have to be able to tell the server that you're searching for "AB" and "ED" separately - otherwise you will get a very large number of possible combinations very quickly.

SELECT t1.*, t2.*
FROM table1 t1, table2 t2
WHERE t1.t1_field LIKE '%AB%'
OR t1.t2_field LIKE '%ED%'.

If you require different substring sets per substring, then you will have to use your programming environment (PHP, Java, &c.) to split the main input search string ("ADED") into its components.

[EDIT - in reponse to the OP]

Are these random values or do you have a (limited) specific list? If you have more than ~ 25, then I suggest you go with your programming environment, otherwise the SQL will become very messy.

Just a thought, if you have a limited number of these substrings (at a specific part of the field - i.e. 3 characters in? Or similar), you could create a joining table with the substring and the PRIMARY KEY of the main table to get your lookups done faster.

Are these codes you are decomposing? We really need to see the entire scope of your data to get an idea of what would be the best solution.


Does a COMMIT work within an anonymous plgpsql function in PostgreSQL 9.5?

No,

You can't control a transaction inside a plpgsql function (or anonymous block).

The only option that you have its creating a transaction outside the block, eg:

BEGIN;

DO $$
  -- function stuff

  -- but if you use a exception, you will force a rollback
  RAISE EXCEPTION 'message';
$$ LANGUAGE 'plpgsql';

COMMIT; -- OR ROLLBACK

BTW, DO BLOCKS have the same effect that functions who returns void.

Please, see more at the doc:

https://www.postgresql.org/docs/current/static/sql-do.html


Best way to Design Key,value pairs in relational database?

What you want is called (exact) Relational Division.

See also this article: Divided We Stand: The SQL of Relational Division

There are several ways to have a query like that solves this problem. You can load the input data to a (temporary) table or use a CTE like this:

WITH input_data (attr_id, attr_value) AS
  ( SELECT *
    FROM
      ( VALUES                              -- the input data.
          (1, 'value1'),
          (2, 'value2')
      ) AS i (attr_id, attr_value)
  ),
t AS                                        -- all the object_key_id values.
  ( SELECT DISTINCT object_key_id           -- if you have a separate table with those
    FROM tableX                             -- you can use it, instead.
  )
SELECT object_key_id
FROM t
WHERE NOT EXISTS                            -- this subquery ensures
      ( SELECT attr_id, attr_value          -- that all input attribute/value pairs
        FROM input_data                     -- appear for an object_key
      EXCEPT
        SELECT attr_id, attr_value
        FROM tableX AS tt
        WHERE tt.object_key_id = t.object_key_id
      )
  AND NOT EXISTS                            -- and this subquery does the reverse
       ( SELECT attr_id, attr_value         -- i.e. the exact part of the division
        FROM tableX AS tt
        WHERE tt.object_key_id = t.object_key_id
      EXCEPT
        SELECT attr_id, attr_value
        FROM input_data
      ) ;

Test at SQL-Fiddle

This question at StackOverflow: How to filter SQL results in a has-many-through relation has a few more ways to solve it and benchmarks for Postgres but it's not for the exact variation, only for the "Division with Remainder". The queries will be similar but an extra check/condition has to be added for the exact variation.


How to design a persistent table with dependencies which are allowed to be deleted in the future?

This looks like a case where you might create Foreign Keys with set null on delete.  From that post:

  What is a foreign key with "Set NULL on delete" in SQL Server?

  A foreign key with "set null on delete" means that if a record in the
  parent table is deleted, then the corresponding records in the child
  table will have the foreign key fields set to NULL. The records in the
  child table will not be deleted in SQL Server.

CREATE TABLE child_table
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT fk_name
    FOREIGN KEY (child_col1, child_col2, ... child_col_n)
    REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
    ON DELETE SET NULL
    [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
);

Just make sure that Teachers in the Schedules table is defined to allow nulls


Execute Dynamic SQL Generated From Select

While I would prefer to generate true dynamic SQL statements with strongly-typed parameters, concatenation is probably ok here because the proper way will be a lot more cumbersome. I do, however, prefer sp_executesql over EXEC(), always.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N'EXEC [sim].[ROI_Save] '
        + CONVERT(varchar(6), VSL.PathId)           + N', '
        + CONVERT(varchar(6), @ROIResultId)         + N', '
        + CONVERT(varchar(6), RT.routeTypeResultId) + N', '
        + CONVERT(varchar(6), RT.ROI_RouteTypeId)   + N'; '
FROM  @routeResultIds AS RT
JOIN sim.ROI_Result_Vehicle AS VSL
ON RT.ROI_Result_VehicleId = VSL.ROI_Result_VehicleId;

EXEC sys.sp_executesql @sql;

I still don't like that the parameters to the procedure aren't named, and if any of your parameter values were strings or dates, the build-up of single quotes would quickly get very annoying.


Full-text: lots of FT_MASTER_MERGE waits in SUSPENDED state after multiple full-text indexes created (server hangs)

You will need to stagger the operations instead of doing all at once.  Connect item does not talk about accepting new connections.  But due to this wait threads are not being released (in your case) and new connectoins are not possible.

Ref:

https://connect.microsoft.com/SQLServer/feedback/details/1069538/fulltextindex-master-merge-operation-was-not-done
https://www.sqlskills.com/help/waits/ft_master_merge/

This is a known issue with SQL Server. From the connect item:

  This is caused by the way our current job scheduler is setup resulting
  in multiple master merge operations getting queued up but never
  signalled. To be clear, this only happens when multiple
  indexing/reorganize operations are invoked simultaneously - the
  indexing operation completes just fine and the results are queryable.
  It's only the master merge that times out and is re-scheduled for a
  later period of time.

  Due to the complexity of the fix we have decided to wait till the next
  major release before triaging it. At the moment, it is advised to
  stagger index populations in order to not cause such timeout issues.
  Please let me know if you have any further questions.


SP how can I sum the amount of all leftchild in parentid

Assuming the following:

alter table mytree add constraint ck1tree unique (memberid);
alter table mytree add constraint ck2tree unique (parentid,position);

One suggestion is to start with a procedure that calculates the sum of the subtree (regardless of position):

There is probably more elegant solutions to this, but I'm in a bit of a hurry and I think it will get you started so I'll post anyhow. Note that you will probably have to adjust the variable @@max_sp_recursion_depth;

set @@max_sp_recursion_depth = 10;
delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE sum_subtree
    (IN `p_memid` INT, OUT `tot_left_amount` DECIMAL(10,2)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE tmp decimal(10,2) default 0;
    DECLARE tmp2 decimal(10,2) default 0;
    DECLARE a decimal(10,2);
    DECLARE mid int;
    DECLARE finished int default 0;

    DECLARE c1 CURSOR FOR
        select memberid, amount
        from mytree where parentid = p_memid;

    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;

    OPEN c1;
    get_amount: LOOP
        FETCH c1 into mid, a;
        IF finished = 1 THEN
            LEAVE get_amount;
        END IF;

        -- recursive call to procedure
        CALL sum_subtree(mid, tmp);
        SET tmp2 = tmp2 + tmp + a;
    END LOOP;
    SET tot_left_amount = tmp2;
END $$
delimiter ;

call sum_subtree(7,@x);
select @x;
+----------+
| @x       |
+----------+
| 18000.00 |
+----------+

Now you can create a procedure that select the value from the 'L' branch, call this procedure and return the sum of those two values. Hope it helps

Edit: example of calling procedure:

delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `count_left_right_amount`(
    IN `p_memid` INT, OUT `tot_left_amount` DECIMAL(10,2)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
    DECLARE a decimal(10,2);
    DECLARE m int;
    DECLARE s decimal(10,2);

    select memberid, amount into m, a from mytree
    where parentid = p_memid and position = 'L';

    call sum_subtree(m,s);
    set tot_left_amount = a + s;
END $$
delimiter ;

call count_left_right_amount(5,@y);

MariaDB [test]> select @y;
+----------+
| @y       |
+----------+
| 22500.00 |
+----------+
1 row in set (0.00 sec)


Locate all tables with IDs in them (FOREIGN KEYs, etc).  Figure out the exact numbers for below (I took your 10M and 100K as exact.)
Take your application "down".
Disable FOREIGN KEYS
Run UPDATE tbl SET x_id = x_id - 9900000 WHERE x_id >= 10000000; for each such table.
Enable FOREIGN KEYS
Do ALTER TABLE ... AUTO_INCREMENT = ... (or otherwise make sure it gets lowered)
Bring your app back up.

It would be wise to experiment before launching into the entire task.  If you have a test platform do it all there.  If not...

Limit the damage (for testing):  WHERE x_id >= 10000000 AND x_id < 10000010 LIMIT 11 -- then check that the number of rows changed was no more than 10.  And verify that all actions on those users are still OK.  You still must go through all 6 steps, but if something breaks, you will have less damage to clean up.  And, in doing this small test, you will (hopefully) figure out all the UPDATEs that are needed.

To be more daring,...  Don't disable/enable the FKs.  Instead, figure out what order to do the UPDATEs in so that the FKs won't complain.  Verfiy that the CASCADEs will do what you need.  This also gives you more confidence that the values will be correctly fixed; that is, no dangling messed up FKs.

(A caveat on AUTO_INCREMENT - The handling of such has changed with 8.0; my steps may be changed a bit.)

As a Postlog, ask yourself whether FKs have been more a benefit or more a pain.


Deleting records from tables containing FK pointing to each other

The problem can be solved with many ways:

First we notice that one of the FK columns is nullable. This allows to delete from both tables, using three statements in a single transaction and without need for deferrable constraints. First update latest_reply_id to null, then delete from PublicReply, then from Link:

-- Delete one (or more) `Link` rows
-- and all the `Publicreply` rows associated with them:
BEGIN ;
    UPDATE public.links_link
    SET latest_reply_id = NULL
    WHERE id IN (?, ?, ..., ?) ;              -- Link ids to be deleted

    DELETE FROM public.links_publicreply
    WHERE answer_to_id IN (?, ?, ..., ?) ;    -- Link ids to be deleted

    DELETE FROM public.links_link
    WHERE id IN (?, ?, ..., ?) ;              -- Link ids to be deleted
COMMIT ;

Then we notice that one of the foreign key constraints is already defined as deferrable. This allows to delete from both tables, using two statements in a single transaction:

-- Delete one (or more) `Link` rows
-- and all the `Publicreply` rows associated with them:
BEGIN ;
    DELETE FROM public.links_link
    WHERE id IN (?, ?, ..., ?) ;              -- Link ids to be deleted

    DELETE FROM public.links_publicreply
    WHERE answer_to_id IN (?, ?, ..., ?) ;    -- Link ids to be deleted
COMMIT ;

Using modifiable CTEs, we can delete from both tables in a single statement. The constraints do not need to be deferred for this. Example:

-- Delete one (or more) `Link` rows
-- and all the `Publicreply` rows associated with them:
WITH del_link AS
  ( DELETE FROM public.links_link
    WHERE id IN (?, ?, ..., ?)                -- Link ids to be deleted
    RETURNING id
  )
DELETE FROM public.links_publicreply
WHERE answer_to_id IN (TABLE del_link) ;

Deleting from PublicReply will be a bit more complicated, depending the requirements but can be done with any of the above methods. What are the requirements?

delete a PublicReply, its parent Link and all the associated replies?
delete a PublicReply and if it's the latest reply, change the parent Link to point to the previous reply? If it's the only one, set it to NULL?
delete a PublicReply and if it's the latest reply, change the parent Link to point to the previous reply? If it's the only one, delete the parent Link as well?


Disconnect from network without stopping the query

As the answer referenced in your question indicates, SQL Server will cancel the executing query and rollback the transaction when it detects the network connection has terminated. A SQL Server session needs a connected client to query return results and messages (informational, warning, and error messages).
You have a few options to maintain the connection/session for a long-running query, with and without an interactive client applciaton.
RDP into the SQL Server machine or jump box and execute the query from there. This will allow you to disconnect from the RDP session while the client application (e.g. SSMS, SQLCMD, ADS, etc.) continues to run. You can later reconnect to the same Remote Desktop session to review the results.
Create a SQL Server Agent job with a T-SQL, PowerShell, CmdExec, or SSIS job step to execute the query without an interactive client.
Create a Windows Task Scheduler task to execute the query, using SQLCMD, PowerShell, or other command-line tool.
Encapsulate the query in a stored procedure and execute it as a Service Broker activated proc. This requires a Service Broker enabled database and the proc code will need to capture results. See Asynchronous procedure execution for code examples of this technique and other considerations.
Without an interactive client, results and messages that won't be visible so make sure these are logged/captured according to your needs. Also ensure command-line applications return a non-zero exit code after errors so it's reported as an error by SQL Server Agent or Windows Task Scheduler.


Supporting non-ascii characters in Postgres full text (english)

You can use unaccent extension, either by pre-processing the text with unaccent function, or creating your own text search configuration. For instance (based on the example on unaccent's doc):

CREATE TEXT SEARCH CONFIGURATION my_conf ( COPY = english );
ALTER TEXT SEARCH CONFIGURATION my_conf
    ALTER MAPPING FOR hword, hword_part, word
    WITH unaccent, english_stem;

So you can match it:

mydb=> SELECT to_tsvector('english','cañon'), to_tsvector('my_conf', 'cañon');
 to_tsvector | to_tsvector
-------------+-------------
 'cañon':1   | 'canon':1
(1 row)

mydb=> SELECT
mydb->     to_tsvector('english','cañon') @@ to_tsquery('english', 'cañon'),
mydb->     to_tsvector('english','canon') @@ to_tsquery('english', 'canon'),
mydb->     to_tsvector('english','cañon') @@ to_tsquery('english', 'canon'),
mydb->     to_tsvector('english','canon') @@ to_tsquery('english', 'cañon'),
mydb->     to_tsvector('my_conf','cañon') @@ to_tsquery('my_conf', 'cañon'),
mydb->     to_tsvector('my_conf','canon') @@ to_tsquery('my_conf', 'canon'),
mydb->     to_tsvector('my_conf', 'cañon') @@ to_tsquery('my_conf', 'canon'),
mydb->     to_tsvector('my_conf', 'canon') @@ to_tsquery('my_conf', 'cañon');
 ?column? | ?column? | ?column? | ?column? | ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------+----------+----------+----------+----------
 t        | t        | f        | f        | t        | t        | t        | t
(1 row)

See the last query, I'm matching combinations of cañon and canon, when using english configuration (first 4 columns) will only match if both are the same (first 2 ones), but my_conf configuration (last 4 columns) will match in all of them.


Unable to set user/group as principal on a proxy

A post for handling job permissions can be found at:

Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

This post outlines:

Using standard SQL Agent roles such as SQLAgentOperatorRole.
 Note: Active Directory Groups are not able to login to start a job.
Erland Sommarskog's post http://www.sommarskog.se/grantperm.html#countersignatures outlines using countersignatures.  This is a more complex setup, but it does work.
Points to a custom StartAgentJob stored procedure to allow rights to be granted within the context of the procedure.  This includes support of AD Groups as well.  The custom StartAgentJob has code in it that explicitly allows members of an Active Directory Group (but not the Active Directory Group itself) to start a job that they do not own.


Why is SQLite adding unused disk space to "free-list" after deleting a lot of data in a database?

As Phil wrote, freeing pages from the free-list would require that pages are moved around (and that all references to those pages are adjusted).

When data is deleted, it is very likely that some other data will be inserted afterwards, and that data can then just use the pages in the free-list. Returning those pages to the OS only to allocate new ones afterwards would be inefficient, and will increase fragmentation (both of the database file on disk, and of the objects in the database file). But if you really want SQLite to do that, you can enable it with PRAGMA auto_vacuum.

In practice, deleting lots of data usually doesn't happen during normal operation, and if you know that you will not insert new data after deleting, you can run the VACUUM command manually.


Better to create a table for each user account, or one huge table for all user data in MYSQL

MySQL can easily cope with 20M rows if it's properly indexed. We have tables with more than a billion rows in it.

Having one table is cleaner. Don't need to do magic in application based the user(name). Easier to make any statistics on the documents table too.

I would definitely go with the one big table approach. If you are concerned about the table (physical) size you should consider partitioning the documents table. http://dev.mysql.com/doc/refman/5.5/en/partitioning-types.html


Oracle RAC can you have a cluster that contains one node

From the documentation:

  Oracle RAC One Node is a new option available with Oracle Database 11g
  Release 2. Oracle RAC One Node is a single instance of an Oracle RAC
  enabled database running on one node in a cluster. This option adds to
  the flexibility that Oracle offers for database consolidation while
  reducing management overhead by providing a standard deployment for
  Oracle Databases in the enterprise.

This is a means to have a multi-node RAC, one which one database can only run on one node at a time, so it allows you to (more cheaply) use RAC for active-passive clustering, which saves on having both RAC and another clustering product such as VCS. Or are you asking if it is possible to have a RAC system entirely on one physical host, running Clusterware, ASM, etc and a database? Yep, that is possible too, the standard installer can do this for you.


What is the best way to insert a XML input to an oracle table in stored procedure?

You can query your XML data directly in SQL with XMLTABLE:

SQL> VARIABLE xml VARCHAR2(4000);
SQL> BEGIN :xml :=
  2  '<rowset>
  3       <row>
  4           <name>tom</name>
  5           <family>mack</family>
  6       </row>
  7       <row>
  8           <name>marry</name>
  9           <family>skot</family>
 10       </row>
 11   </rowset>';
 12  END;
 13  /

PL/SQL procedure successfully completed

SQL> SELECT x.*
  2    FROM XMLTable('for $i in /rowset/row return $i'
  3                    passing XMLTYPE(:xml) columns
  4                       name VARCHAR2(200) path 'name',
  5                       family VARCHAR2(200) path 'family'
  6                  ) x;

NAME    FAMILY
------- -------
tom     mack
marry   skot

You can also use XMLSequence and EXTRACT (see this SO question for example) but as noted in the documentation these functions are deprectated.

Another option is DBMS_XMLSTORE, although you can't really customize it much (needs perfect match between input xml tags and output column names):

SQL> CREATE TABLE my_table ("name" VARCHAR2(10), "family" VARCHAR2(10));

Table created

SQL> DECLARE
  2     l_ctx  dbms_xmlstore.ctxtype;
  3     l_rows NUMBER;
  4  BEGIN
  5     l_ctx := dbms_xmlstore.newcontext('MY_TABLE'); -- Get saved context
  6     dbms_xmlstore.clearUpdateColumnList(l_ctx); -- Clear the update settings
  7
  8     -- Set the columns to be updated/inserted as a list of values
  9     dbms_xmlstore.setUpdateColumn(l_ctx, 'name');
 10     dbms_xmlstore.setUpdateColumn(l_ctx, 'family');
 11     -- Set ROW tag
 12     dbms_xmlstore.setRowTag(l_ctx, 'row');
 13
 14    -- Insert the doc.
 15    l_rows := DBMS_XMLSTORE.insertXML(l_ctx, :xml);
 16    DBMS_OUTPUT.put_line(l_rows || ' rows inserted.');
 17
 18    -- Close the context
 19    DBMS_XMLSTORE.closeContext(l_ctx);
 20
 21  END;
 22  /

2 rows inserted.

PL/SQL procedure successfully completed

SQL> select * from my_table;

name       family
---------- ----------
tom        mack
marry      skot

I'm not sure performance-wise which option is faster, this certainly depends on the data -- you'll have to benchmark.


Troubleshoot corruption in SQL Server Database

Well, right off I would verify with the person asking the question that they are referring to corrupted data and not database corruption, there is a difference. I would also determine who was interviewing me and base the answer on that (e.g. non-DBA would not care about the details, where a DBA Manager or something could require me to provide more details).

For somewhere in the middle...

Corrupt Data

With corrupt data entering the database it would be based on how quickly it was caught and likely how wide spread it is (isolated to a table, or effects multiple tables). If a single table was involved it could be as easily as restoring a copy of the database and replace the data in the table. If the database is large this may not be so easily, or if multiple tables are involved, require recreating the data. The focus would be in designing of the application to put in controls that prevent this from happening to begin with because of the pain it can cause, and possibly be unrecoverable.

Database Corruption

Database corruption would mean the database as a whole or underlying structure of the database is damaged. I would start out taking a full backup of that database in its current state before starting anything. I would start out reviewing the previous DBCC CHECKDB output since I would have had that information readily available for systems I manage. Reviewing that output would indicate what level of severity I am dealt.

I would want to determine and ensure the underlying disk the database files reside on are in good condition and may not have been the cause of the issue.

I would then start re-reading post from folks like Paul Randal on corruption recovery to ensure I fully understood the liability of my actions, before doing anything. In a last ditch effort and if the tables that are found having the problem have data that can be recreated easily, I would issue the DBCC CHECKDB WITH ALLOW_DATA_LOSS if it was in a critical timeframe. Outside of that it is time to prepare for the restore process.

I would also go into small rant that alerting and ensuring backups are good, and meet the business needs, would ensure the company is ready to easily handle this type of issue. If my backups are current and good, I would just start the restore process to another database, while I was trying to recover the corruption. This would allow me to have a "backup" plan if the recovery steps above did not work. It is all really based on the criticality of the system.


Is there a way to check connection properties of an active SQL Server connection?

That property is a setting for the client-side of the connection.  Not the server-side, so you won't be able to directly observe this setting from SQL Server.

You can observe the effect of that setting using SQL Server Profiler or Extended Events, looking for the Batch and RPC events.  They will show you the parameter data types sent by the client.


Converting Microsoft SQL Server collation to UTF 8

Encoding? Do you mean collation? The collation only affects things like equality tests, sort order, etc. They don't affect the allowed encodings for column values.

If you want to store unicode text, you should make sure your columns are among

NCHAR
NVARCHAR
NTEXT (2-byte characters)

rather than

CHAR
VARCHAR
TEXT (1-byte characters).


You mean the IN operator. There are limits, but they differ from DB engine to engine.

SQL Server: 65,536 * Network Packet Size - see Batch Size
Oracle seems to allow 1,000.


Under what conditions does SQL Server encrypt tempdb?

When you encrypt the first database in the instance, tempdb will also be encrypted.  As per this reference on TDE:

  Transparent Data Encryption and the tempdb System Database

  The tempdb system database will be encrypted if any other database on
  the instance of SQL Server is encrypted by using TDE.

This will be the case when you set ENCRYPTION ON on a database.


Blocking SPID has Zero Disk IO and Zero CPU Time

This can sometimes happen when applications are using implicit transactions

It doesn't help that sp_who2 is really confusing -- Aaron is right that you're better off with sp_WhoIsActive or sp_BlitzWho to find running queries.

One dead giveaway of implicit transactions is seeing the query text like begin tran or IF @@TRANCOUNT > 0.

This can also happen when queries are using explicit transactions, of course. It's just far easier to catch those in code.

As to why CPU time is very low -- well it doesn't take much CPU to do an index seek to one row and lock it, then cause problems for any other queries that also want that row.


Is it possible to recover from a LOST_EVENTS incident from an RDS read replica?

In this specific case (a failed grant statement) stopping the slave, skipping the event, and restarting the slave is likely a safe operation... you just have to do it the RDS way.

mysql> CALL mysql.rds_skip_repl_error;

This is one of several stored procedures installed on every RDS instance that partially make up for the lack of SUPER privilege.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html


SQL Server Failover Cluster Scalable by adding nodes?

It doesn't mean that it provides every type of scalability, such as for adding memory and CPUs to a given instance.  It means that you can scale up the overall environment by installing more instances that are spread across various nodes and have different possible owning nodes.


Use LDAP for name look up with Oracle Instant Client

Place the sqlnet.ora and ldap.ora files in your $ORACLE_ADMIN directory and make sure to set the environment variables. I made mine the TNS_ADMIN directory

(Linux) my .bashrc

export TNS_ADMIN=/opt/oracle/instantclient_12_1/network/admin
export ORACLE_ADMIN=${TNS_ADMIN}

[ku14lts:/opt/oracle/instantclient_12_1/network/admin]
$ ls
ldap.ora  sqlnet.ora  tnsnames.ora

And I was able to connect to a DB not in my tnsnames.ora


Generate Data Only Script without Identity SQL 2017

Disabling to script the primary key and foreign key only restrict the generation of scripts creating these constraints. Identity is a table property. I would suggest (not efficient since duplicating data) to copy the column you required to a new table and script that table.

You can use,

Select machineno,amount into tmp_table
from tablename

Note the new table created will not create constraints and triggers
or you can script the DDL of the original table and create a dummy table without identity column in it and copy the data to the new table so it will retain the constraints

Then script the tmp_table using generate scripts. This will not have the identity column. You can change the table name as required after you have scripted it.

OR
Scripting dynamically

SELECT 'INSERT INTO tablename (machineno,amount) values ('+convert(varchar(28),machineno)+','+convert(varchar(28),amount)+')'
FROM tablename


There are many ways. Probably simplest:

SELECT Title_id
FROM   TitleAuthor
GROUP  BY Title_id
HAVING count(*) > 1;

You can JOIN to Titles to get book details.


Add header to multi server or instance query (powershell)

If you just want to hard-code the header, you can use the Powershell New-Item cmdlet.  I used the -Force parameter to initialize the outfile with the single header row and the rest of your script uses -Append to add the detail information.

New-Item $outfile -value "Header,Header,Header,Header`n" -Force
Get-Content $servers | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append $outFile}

If you want dynamic columns generated based on the actual query, you can run an initial Get-Content using -First 1 (to only get the first server) and limit the output to the first row only by using -First 1.  Then, follow up with your usual Powershell script.

Get-Content $servers -First 1 | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -First 1 | Out-File $outFile -Force}
Get-Content $servers | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append $outFile}


SQL Server was unable to communicate with the LaunchPad service

I had the same issue and I solved it by installing the Service Pack on top of the media installed R.

I think it is a related to install an old version of LaunchPad on an already updated SQL Server.

As per this Q&A

SQL Server LaunchPad "The service did not respond in a timely fashion" + No Log in ExtensibilityLog

My timeline.

My sql install was a semi-updated SP1.
My media was a stock SP1.
I installed R, wouldn't work (Restarted, same)
LaunchPad wouldn't start (Restarted, same)
Made sure the SQL user account was part of the R group
LauchPad would start but R wouldn't work. (Restarted, same)
Updated to SP2 (Restarted)
R worked.
Updated to SP2 CU4 (Restarted)
R worked.

My testing script

    EXEC sp_execute_external_script
                      @language =N'R',
                      @script=N'OutputDataSet<-InputDataSet',
                      @input_data_1 =N'SELECT 1 AS hello'
                      WITH RESULT SETS (([Hello World] INT));
                        GO


Does this database table violate normalization rules?

WallsMaster doesn't break any of the rules, if each of those fields is an independent property of the wall. I would suggest it's probably not worth redesigning...

This is assuming that the properties listed are the fixed set of properties. If there are going to be extra things added, then it would be worth splitting it out into a table which links properties and walls.


File locations for Full Text Catalogs using SQL Server 2005 on VMware

Use a folder other than the root of the hard drive.  UAC is preventing you from writing to the root of the drive.


Is command BACKUP asynchronous operation

If you are running your backups as per the posted example code, they will not overlap.

An  EXEC() statement will complete before the next statement is executed, which you can demonstrate easily:

DECLARE @SQL VARCHAR(8000);

SET @SQL = 'WAITFOR DELAY ''00:00:10:00''';

SELECT GETDATE() AS [Start]
EXEC (@SQL);
SELECT GETDATE() AS [End]

BACKUP, as with all TSQL commands, is executed synchronously. Each statement in a SQL batch will complete in turn before executing the next, whether its executed standalone or via EXEC() as in your example.

Backup using SMO is different and unrelated to the TSQL BACKUP command. With SMO you have the option of executing a synchronous backup with Backup.SqlBackup or an asynchronous backup with Backup.SqlBackupAsync.


Insert values from a record variable into a table

There is a solution with just PL/pgSQL. Simple and elegant, too. Pretty advanced stuff, though.
Requires Postgres 9.0 or later (workaround for older versions possible).
CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void AS
$func$
BEGIN

FOR _tbl_in IN EXECUTE
   format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
   -- do something with record

   EXECUTE format('INSERT INTO %s SELECT $1.*', _tbl_out)
   USING _tbl_in;
END LOOP;

END
$func$  LANGUAGE plpgsql;

Call (important!):
SELECT gesio(NULL::t, 't1');

t and t1 being the tables with identical schema.
Note that the polymorphic parameter (anyelement) is only needed if you need it's value or data type for the computation in the function body. Else you can simplify like demonstrated in this later answer:

Insert values from a record variable into a subclass table

Major ingredients

Implicit cursor of a FOR loop instead of explicit cursor. That's generally preferable.

Polymorphic types

Object identifier types

Dynamic SQL in plpgsql

VALUES can take a row type directly.

An obstacle to overcome is that variables inside the function cannot be defined as polymorphic type anyelement (yet). This related answer on SO explains the solution. Provides a workaround for older versions, too.
I am handing in a NULL value of type t, which serves three purposes:

Provide table name.
Provide table type.
Serve as loop variable.

The value of the first parameter is discarded. Use NULL.
Consider this related answer on SO with more details. The most interesting part being the last chapter Various complete table types.
SQL Fiddle demo.
If your computations are not too sophisticated, you may be able to replace the loop with a single dynamic SQL statement, which is typically faster.


This oracle package will do the job

select dbms_lob.substr(b,2000,1) from blobtest;


are overlapping multi column statistics redundant?

They're not redundant, no.

Each statistics object includes a histogram of the values stored in the first listed column. For the remaining columns, only density information is kept at each level (no histogram).

For example, the proposed statistic on (ClassID, SegmentID, GroupID, Tier1, LanguageID, Active) would create:

A histogram for ClassID values
A density number for:

ClassID, SegmentID
ClassID, SegmentID, GroupID
ClassID, SegmentID, GroupID, Tier1
ClassID, SegmentID, GroupID, Tier1, LanguageID
ClassID, SegmentID, GroupID, Tier1, LanguageID, Active

These densities can be useful when there is correlation between columns, but only for equality predicates (and you will need SQL Server 2016 to get this benefit when using the 'new' cardinality estimator. The original cardinality estimator used this density information by default).

You can see the histogram and density information using DBCC SHOW_STATISTICS.

Note the order of columns is important. This statistic could provide average density information for equality predicates on (ClassID, SegmentID, and GroupID) but not (ClassID, GroupID).

Statistics are quite lightweight as far as storage is concerned, but you need to be aware of the potential cost of maintaining them, especially on large tables where a high sampling rate is necessary to achieve acceptable quality.

If DTA recommends a statistic, that is a good indication that the statistic would be useful for one or more queries in your workload. I don't recall if DTA reports on the queries that contributed to a recommendation, but if not, for the example above, you would be looking for queries with equality predicates on all the columns listed. Compare the quality of the plan and cardinality estimates with and without the new statistic if you really want to dive into the detail.

The missing index would create an associated statistic on (IconTypeID, Active), with a histogram on IconTypeID, and average density information for the combination (IconTypeID, Active).

See the following for more information:

Statistics
Statistics Used by the Query Optimizer in Microsoft SQL Server 2005


How to index this table (a_level, b_level, item_id)

Since your table only three columns, this setup can be straightforward.

Here is an ideal table layout for items

CREATE TABLE items
(
    item_id int not null auto_increment,
    a_level int not null,
    b_level int not null,
    KEY  ai  (a_level,item_id),
    KEY  bi  (b_level,item_id),
    KEY  abi (a_level,b_level,item_id),
    KEY  bai (b_level,a_level,item_id),
    PRIMARY KEY     (item_id)
);

Here are the realm of possible queries

SELECT * FROM items WHERE id = 100; (uses the PRIMARY KEY)
SELECT * FROM items WHERE a_level = 200; (uses KEY ai)
SELECT * FROM items WHERE b_level = 300; (uses KEY bi)
SELECT * FROM items WHERE a_level = 400 order by b_level (uses KEY abi ordering all b_level values under a_level 400 and orders item_id under each b_level)

YYou may try out other combinations of SELECT...GROUP BY...ORDER BY.... You should also run EXPLAIN on every SELECT query and see which indexes are summoned for query execution. Indexes that do not appear as a chosen index can be eliminated (dropped) over the course of time.

Here were questions you asked?

  If i INDEX both on a_level, b_level (and of course item_id) and SELECT
  WHERE a_level= b_level= item_id= will it be bad? I guess only INDEX on
  b_level and item_id and SELECT WHERE b_level= AND item_id= will be
  enough/the best solution?

Those queries are not bad given the indexes I just presented.

The only reason why the item_id appears in all the indexes is to make those indexes covering indexes. That way, the retrieval of data is done using the index only, bypassing the table.


SQL Server command line usage? Is it possible?

Have you considered SQLCMD?
Here's an article on Using the SQLCMD command line utility.
You can execute adhoc SQL statements, or run .sql scripts.

The following example runs the C:\MyScript.sql script from the command line and outputs the queries and results to a text file located at C:\MyResults.txt.
C:\Windows>sqlcmd -S NOTTINTRA3\SQL2005 -i C:\MyScript.sql -o c:\MyResults.txt -e


Start failed for Job in SQL Server Agent

Looks like the problem is exactly in the description - the job is already running.

Try stopping the job first, then re-running it. Either with the GUI, or

USE msdb
GO

EXEC dbo.sp_stop_job
    N'Job Name'
GO

Other things to check:
What is the job doing? Can you do that (run the script, for example) directly (not using Agent)?

Has the job got elements (e.g. a script) which can never actually finish, therefore the job is never going to stop (and therefore will not start again on schedule).

It's a bit hacky, but you can schedule a job-killing job to stop any jobs that are over-running (although it's usually better to try fix your jobs so they finish properly).

This script from SQL Server Central provides some handy info about currently running jobs:

SELECT
    ja.job_id,
    j.name AS job_name,
    ja.start_execution_date,
    ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
    Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
    ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
    ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;

That should help you sort out your problem, if not, post some more details about what exactly the job is doing.

Edit-
Code to check SQL server agent is running (run from Management studio).
DECLARE @agent NVARCHAR(512);

SELECT @agent = COALESCE(N'SQLAgent$' + CONVERT(SYSNAME, SERVERPROPERTY('InstanceName')),
  N'SQLServerAgent');

EXEC master.dbo.xp_servicecontrol 'QueryState', @agent;

You can manage Agent from SQL Server Configuration Manager.

Ian


MySQL 'LOAD DATA INFILE' : Can't replace entry when foreign key exists

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

The above link clearly explains that mysqldump too puts set foreign_key_checks = 0; in the dump file so that data can be restored without the foreign key constraint error. I have always used set foreign_key_checks = 0 for importing data into tables and then set it back to 1.


Should I drop dbo from the database role db_owner?

Don't worry about it, as J.D. says.
In fact, you cannot drop dbo from db_owner. There's a hard-wired blocker in the engine prohibiting you to do so. Try and you get the error:
Msg 15405, Level 16, State 1, Line 1
Cannot use the special principal 'dbo'.


Permission hierarchy vs. WITH GRANT OPTION

Unfortunately the WITH GRANT permission is not inherited in some, or perhaps all, cases.  I couldn't find anything in the documentation or other articles on the [cue thunder] World Wide Web [stop thunder] stating that, though.  I am with you in that I would expect it to be inherited.

I tested this on SQL Server 2016 and got the same results as you did.  There are several options to work around it, but the most fine-grained option would be to assign EXECUTE WITH GRANT to SchemaUpdater on each stored procedure.

GRANT EXECUTE ON [dbo].[sp_MyProc] TO [SchemaUpdater] WITH GRANT OPTION

I have confirmed that WITH GRANT is not inherited on other permissions and objects as well.  I tested the same scenario with VIEW DEFINITION on a table, and had to explicitly GRANT WITH GRANT on the table before SchemaUpdater could grant that permission to another user.

So the specific answers to your questions:

Since WITH GRANT is not inherited, a user must have explicit WITH GRANT permission on an object in order to grant permissions.  Since SchemaUpdater has EXECUTE WITH GRANT on the database, it can grant EXECUTE on the database.  Since it doesn't have an explicit EXECUTE WITH GRANT on the stored procedure, it cannot grant explicit permissions on that stored procedure.
As mentioned, SchemaUpdater will need EXECUTE WITH GRANT explicitly on each stored procedure, or a higher privilege that includes that permission (such as db_owner).


Reason of ORA-00604 - ORA-00904: on online Redefining

Performing online redefinition creates materialized view log on the source table, that is where DML changes are tracked during the redefinition process.

Finishing the online redefinition gracefully (finish_redef_table or abort_redef_table) removes the materialized view log automatically, otherwise it will stay there.

Truncating the table with an existing materialized view works with the following syntax:

truncate table tab1 purge materialized view log;

Or just simply drop the materialized view log before.

Without specifying the above, truncate fails because of a recursive query on SYS.MLOG$, as that table does not have the column CURRENT_SCN (version <= 11.2). It is fixed from 12.1.


Allow entering application while in transaction

As I ran tests if I comment out the BEGIN TRANSACTION and just run the sprocs (outside of a transaction) then I no longer have any issue as the tables aren't locked.

Inside a transaction, the modification locks are held for the duration of the entire transaction. Without the BEGIN TRAN, locks are only held until the end of each statement.  There will still be blocking, but it won't last as long, so your site is probably able to operate more effectively.

The customer still wants the site not to lock up and continue this process so I played around with adding:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

But this did not help in any way as it still seems users cannot do anything in the application until after the transaction is complete.

Data modification queries don't work that way.  Check out the documentation on isolation levels:

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.

What you actually want is the opposite - the nightly process is going to take locks, you want the client (website) to be able to bypass them.  But using READ UNCOMMITTED / NOLOCK is not the ideal way to do that.  There's a better way, which is
Use RCSI
If the web site is mostly reading data, then you might have success with the read committed snapshot isolation level (RCSI).  This would allow the website to continue to read from the database without being blocked by the nightly process.
You can read a detailed account of what to consider when turning on RCSI here: Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide


How to show the Join with Crows Foot Notation

There ought to be another entity, whose key is StoreID.
There is a many to many relationship between stores and customers.

In a simple ERD, you can just draw a line between the two boxes for Store and Customer and put a crow's foot at either end.  You can't put lifetime purchases in either of those two boxes.  So if you want to include that attribute in your diagram, add a diamond in the middle of the line you just drew.

The diamond diagrams the relationship, and you can put lifetime purchases in there.

When you go to design a relational database, all of the attributes will end up being stored in relational tables.


How can I DRY up a series of upserts of the same fields from multiple tables?

You could use UNION in the CTE. There is still some duplication of code but less than before:

with etl (storenumber, filename, last_extracted) as
(
    ( select distinct on (storenumber)
          storenumber,
          'storefinancialdate',
          extracteddate + extractedtime::time
      from storefinancialdate
      order by
          storenumber,
          extracteddate desc, extractedtime desc
    )
  union all
    ( select distinct on (storenumber)
          storenumber,
          'storeproduct',
          extracteddate + extractedtime::time
      from storeproduct
      order by
          storenumber,
          extracteddate desc, extractedtime desc
    )
  -- union all ...
)
insert into star.stat_etl
    (storenumber, filename, last_extracted, last_loaded)
select
    storenumber,
    filename,
    last_extracted,
    transaction_timestamp()
from etl
  on conflict (storenumber, filename) do update
    set last_extracted = excluded.last_extracted,
        last_loaded    = excluded.last_loaded ;


OK here is your situation: your INNODB_FILE_PER_TABLE is OFF.

The data and index pages for the InnoDB tables are all inside the system tablespace (ibdata1). For those tables, you only see the .frm files. Therefore, everything is OK.

Here is what InnoDB looks like (image courtesy of Percona CTO Vadim Tkachenko)

These days, many use the default innodb_file_per_table = 1. This used to be 0 for the default back in the days of MySQL 5.1. If you have innodb_file_per_table = 0 in your my.cnf, then MySQL is leaving all InnoDB tables inside ibdata1.

For more info on this, see my posts

Why does InnoDB store all databases in one file?
MySQL dynamically optimize innodb tables without "file per table" setting

You can leave the InnoDB tables inside ibdata1. If you want them stored outside, see the CAVEAT of my post How can I determine which Innodb table is being written?


select string that doesn't contain a substring/field

I believe the problem could be summarised like this:

  Select a row from TableA if no row exists in TableB such that TableB.keyword is a substring of TableA.content.

It would probably be fair to say that the previous answer implements roughly the same logic. At the same time, it does so in a less straightforward way, in my opinion. The query below matches the proposed logic more directly and is arguably easier to follow:

SELECT
  content
FROM
  TablA
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      TableB
    WHERE
      TableA.content LIKE '%' || TableB.keyword || '%'
  )
;


Steps to restore a SQL Server database to a point in time

Your question is bit unclear; you have not specified whether there was a failure and at which time the failure happened.

In general, restore steps are:

Restore the latest full backup with no recovery
Restore the latest diff backup with no recovery
Restore transaction log backups with no recovery, except the last transaction log backup which falls withing the recovery time and specify the stop at time.

i.e. in above scenario:

for 5:17

restore full backup that was taken at midnight with no recovery
the diff backup taken at 4am with norecovery
restore all t-log backups until 5.15 with no recovery
restore t-log backup taken at 5.30 with stop at 5.17 and with recovery

For 5:14, do step 1, do step 2, step 3 all t-log backups till 5am with no recovery.  Then restore t-log backup taken at 5.15 with stop at 5.14 with recovery.


Is there a way to check which computer or IP used a particular script?

You could try reading the default trace to view all Host Names for queries which performed an object DROP (since you mention a drop table).
Default trace should contain log record about it.

SCRIPT:

This script reads events from the default trace, if it is enabled.
Then it displays events for when any object changes occured (CREATE, DROP, ALTER).

USE tempdb
GO

DECLARE @DatabaseName nvarchar(200)
SET @DatabaseName = 'Your database' -- <<<======Specify the name of the database here, else comment this line to get details of databases

DECLARE @enable int
SELECT top 1 @enable = convert(int,value_in_use) FROM sys.configurations WHERE name = 'default trace enabled'

IF @enable = 1 --default trace is enabled
BEGIN

    DECLARE @d1 datetime;DECLARE @indx int ;
    DECLARE @curr_tracefilename varchar(500);
    DECLARE @base_tracefilename varchar(500);
    DECLARE @temp_trace TABLE (obj_name nvarchar(256) COLLATE database_default,database_name nvarchar(256) COLLATE database_default,start_time datetime,
                  event_class int,event_subclass int,object_type int,server_name nvarchar(256) COLLATE database_default,
                  application_name nvarchar(256) COLLATE database_default,ddl_operation nvarchar(40) COLLATE database_default,
                  SessionLoginName nvarchar(512) COLLATE database_default, LoginName nvarchar(512) COLLATE database_default, HostName nvarchar(512) COLLATE database_default);

    SELECT @curr_tracefilename = PATH FROM sys.traces WHERE is_default = 1 ; SET @curr_tracefilename = reverse(@curr_tracefilename)
    SELECT @indx  = PATINDEX('%\%', @curr_tracefilename); SET @curr_tracefilename = reverse(@curr_tracefilename)
    SET @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';

    INSERT INTO @temp_trace SELECT ObjectName,DatabaseName,StartTime,EventClass,EventSubClass,ObjectType,ServerName,ApplicationName,'temp'
        ,SessionLoginName,LoginName,HostName
    FROM ::fn_trace_gettable( @base_tracefilename, default ) WHERE EventClass in (46,47,164) AND EventSubclass = 0 AND DatabaseID <> 2

    --------------------------------------------
    AND (DatabaseName = @DatabaseName OR @DatabaseName IS NULL)

    --------------------------------------------

    UPDATE @temp_trace SET ddl_operation = 'Object:Created' WHERE event_class = 46
    UPDATE @temp_trace SET ddl_operation = 'Object:Deleted' WHERE event_class = 47
    UPDATE @temp_trace SET ddl_operation = 'Object:Altered' WHERE event_class = 164

    SELECT @d1 = min(start_time) FROM @temp_trace

    SELECT start_time AS [Event Time]
        , server_name AS [Server Name]
        , database_name AS [Database Name]
        , HostName AS [Host Name]
        , application_name AS [Application Name]
        , SessionLoginName AS [Session Login Name]
        , LoginName AS [Login Name]
        , obj_name AS [Object Name]
        , ddl_operation AS [DDL Operation]
    FROM @temp_trace WHERE object_type not in (21587)
                  --AND LoginName = 'suspect login'
    ORDER BY start_time  DESC

END

Useful links:

MSSQLTips: Using the SQL Server Default Trace to Audit Events
brentozar: Default Trace Contents


SSIS Execute Sql Task not executed on server

Expanding my comment into an answer - scr_Start has a precedent constraint, f/x, that is not being satisfied when this executes on the server. You'll see that seq_Execute never shows in your SSIS execution report (assuming project deployment model).

Either disable the script task or ensure that whatever condition is being set is valid for your server deployment.


LOB_DATA, slow table scans, and some I/O questions

Presence of XML field causes most of the table data to be located on LOB_DATA pages (in fact ~90% of table pages are LOB_DATA).

Merely having the XML column in the table does not have that effect. It is the presence of XML data that, under certain conditions, causes some portion of a row's data to be stored off row, on LOB_DATA pages. And while one (or maybe several ;-) might argue that duh, the XML column implies that there will indeed be XML data, it is not guaranteed that the XML data will need to be stored off row: unless the row is pretty much already filled outside of their being any XML data, small documents (up to 8000 bytes) might fit in-row and never go to a LOB_DATA page.

  am I correct in thinking that LOB_DATA pages can cause slow scans not only because of their size, but also because SQL Server can't scan the clustered index effectively when there's a lot of LOB_DATA pages in the table?

Scanning refers to looking at all of the rows. Of course, when a data page is read, all of the in-row data is read, even if you selected a subset of the columns. The difference with LOB data is that if you don't select that column, then the off-row data won't be read. Hence it is not really fair to draw a conclusion about how efficiently SQL Server can scan this Clustered Index since you didn't exactly test that (or you tested half of it). You selected all columns, which includes the XML column, and as you mentioned, that is where most of the data is located.

So we already know that the SELECT TOP 1000 * test wasn't merely reading a series of 8k data pages, all in a row, but instead jumping to other locations per each row. The exact structure of that LOB data can vary based on how large it is. Based on research shown here ( What is the Size of the LOB Pointer for (MAX) Types Like Varchar, Varbinary, Etc? ), there are two types of off-row LOB allocations:

Inline Root -- for data between 8001 and 40,000 (really 42,000) bytes, space permitting, there will be 1 to 5 pointers (24 - 72 bytes) IN ROW that point directly to the LOB page(s).
TEXT_TREE -- for data over 42,000 bytes, or if the 1 to 5 pointers can't fit in-row, then there will be just a 24 byte pointer to the starting page of a list of pointers to the LOB pages (i.e. the "text_tree" page).

One of these two situations is occurring each time you retrieve LOB data that is over 8000 bytes or just didn't fit in-row. I posted a test script on PasteBin.com ( T-SQL script to test LOB allocations and reads ) that shows the 3 types of LOB allocations (based on the size of the data) as well as the effect each of those has on logical and physical reads. In your case, if the XML data really is less than 42,000 bytes per row, then none of it (or very little of it) should be in the least-efficient TEXT_TREE structure.

If you wanted to test how quickly SQL Server can scan that Clustered Index, do the SELECT TOP 1000 but specify one or more columns not including that XML column. How does that affect your results? It should be quite a bit faster.

  is it considered reasonable to have such a table structure/data pattern?

Given that we have an incomplete description of the the actual table structure and data pattern, any answer might not be optimal depending on what those missing details are. With that in mind, I would say that there is nothing obviously unreasonable about your table structure or data pattern.

  I can (in a c# app) compress XML from 20KB to ~2.5KB and store it in VARBINARY column, preventing usage of LOB data pages. This speeds SELECTs 20x times in my tests.

That made selecting all columns, or even just the XML data (now in VARBINARY) faster, but it actually hurts queries that don't select the "XML" data. Assuming you have about 50 bytes in the other columns and have a FILLFACTOR of 100, then:

No Compression: 15k of XML data should require 2 LOB_DATA pages, which then requires 2 pointers for the Inline Root. The first pointer is 24 bytes and the second is 12, for a total of 36 bytes stored in-row for the XML data. The total row size is 86 bytes, and you can fit about 93 of those rows onto a 8060 byte data page. Hence, 1 million rows requires 10,753 data pages.
Custom Compression: 2.5k of VARBINARY data will fit in-row. The total row size is 2610 (2.5 * 1024 = 2560) bytes, and you can fit only 3 of those rows onto a 8060 byte data page. Hence, 1 million rows requires 333,334 data pages.

Ergo, implementing custom compression results in a 30x increase in data pages for the Clustered Index. Meaning, all queries using a Clustered Index scan now have about 322,500 more data pages to read. Please see detailed section below for additional ramifications of doing this type of compression.

I would caution against doing any refactoring based on the performance of SELECT TOP 1000 *. That is not likely to be a query that the application will even issue, and should not be used as the sole basis for potentially needless optimization(s).

For more detailed info and more tests to try, please see the section below.

This Question cannot be given a definitive answer, but we can at least make some progress and suggest additional research to help move us closer to figuring out the exact issue (ideally based on evidence).

What we know:

Table has approximately 1 million rows
Table size is approximately 15 GB
Table contains one XML column and several other columns of types: INT, BIGINT, UNIQUEIDENTIFIER, "etc"
XML column "size" is, on average approximately 15k
After running DBCC DROPCLEANBUFFERS, it takes 20 - 25 seconds for the following query to complete: SELECT TOP 1000 * FROM TABLE
The Clustered Index is being scanned
Fragmentation on the Clustered Index is close to 0%

What we think we know:

No other disk activity outside of these queries. Are you sure? Even if there are no other user queries, are there background operations taking place? Are there processes external to SQL Server running on the same machine that could be taking up some of the IO? There might not be, but it isn't clear based solely on the info provided.
15 MB of XML data is being returned. What is this number based on? An estimation derived from the 1000 rows times the average of 15k of XML data per row? Or a programmatic aggregation of what was received for that query? If it is just an estimation, I wouldn't rely upon it since the distribution of the XML data might not be even in the way that is implied by a simple average.
XML Compression might help. How exactly would you do the compression in .NET? Via the GZipStream or DeflateStream classes? This is not a zero-cost option. It will certainly compress some of the data by a large percentage, but it will also require more CPU as you will need an additional process to compress / decompress the data each time. This plan would also completely remove your ability to:

query the XML data via the .nodes, .value, .query, and .modify XML functions.
index the XML data.

Please keep in mind (since you mentioned that XML is "highly redundant") that the XML datatype is already optimized in that it stores the element and attribute names in a dictionary, assigning an integer index ID to each item, and then using that integer ID throughout the document (hence it does not repeat the full name per each usage, nor does it repeat it again as a closing tag for elements). The actual data also has extraneous white space removed. This is why extracted XML documents don't retain their original structure and why empty elements extract as <element /> even if they went in as <element></element>. So any gains from compressing via GZip (or anything else) will only be found by compressing the element and/or attribute values, which is a much smaller surface area that could be improved than most would expect, and most likely not worth the loss of capabilities as noted directly above.

Please also keep in mind that compressing the XML data and storing the VARBINARY(MAX) result won't eliminate the LOB access, it will just reduce it. Depending on the size of the rest of the data on the row, the compressed value might fit in-row, or it might still require LOB pages.

That information, while helpful, is not nearly enough. There are a lot of factors that influence query performance, so we need a much more detailed picture of what is going on.

What we don't know, but need to:

Why does the performance of SELECT * matter? Is this a pattern that you use in code. If so, why?
What is the performance of selecting only the XML column? What are the statistics and timing if you do just: SELECT TOP 1000 XmlColumn FROM TABLE; ?
How much of the 20 - 25 seconds it takes to return these 1000 rows is related to network factors (getting the data across the wire), and how much is related to client factors (rendering that approximately 15 MB plus the rest of the non-XML data into the grid in SSMS, or possibly saving to disk)?

Factoring out these two aspects of the operation can sometimes be done by simply not returning the data. Now, one might think to select into a Temporary Table or Table Variable, but this would just introduce a few new variables (i.e. disk I/O for tempdb, Transaction Log writes, possible auto-growth of tempdb data and/or log file, need space in the Buffer Pool, etc). All of those new factors can actually increase the query time. Instead, I typically store the columns into variables (of the appropriate datatype; not SQL_VARIANT) that get overwritten with each new row (i.e. SELECT @Column1 = tab.Column1,...).

HOWEVER, as was pointed out by @PaulWhite in this DBA.StackExchange Q & A, Logical reads different when accessing the same LOB data, with additional research of my own posted on PasteBin ( T-SQL script to test various scenarios for LOB reads ), LOBs are not accessed consistently between SELECT, SELECT INTO, SELECT @XmlVariable = XmlColumn, SELECT @XmlVariable = XmlColumn.query(N'/'), and SELECT @NVarCharVariable = CONVERT(NVARCHAR(MAX), XmlColumn). So our options are a little more limited here, but here is what can be done:

Rule out network issues by executing the query on the server running SQL Server, either in SSMS or SQLCMD.EXE.
Rule out client issues in SSMS by going to Query Options -> Results -> Grid and checking the option for "Discard results after execution". Please note that this option will prevent ALL output, including messages, but can still be useful to rule out the time it takes SSMS to allocate the memory per each row and then draw it in the grid.
Alternatively, you could execute the query via SQLCMD.EXE and direct the output to go to nowhere via: -o NUL:.

Is there a Wait Type associated with this query? If yes, what is that Wait Type?
What is the actual data size for the XML columns being returned? The average size of that column across the entire table doesn't really matter if the "TOP 1000" rows contain a disproportionately large portion of the total XML data. If you want to know about the TOP 1000 rows, then look at those rows. Please run the following:

SELECT TOP 1000 tab.*,
       SUM(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [TotalXmlKBytes],
       AVG(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [AverageXmlKBytes]
       STDEV(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [StandardDeviationForXmlKBytes]
FROM   SchemaName.TableName tab;

The exact table schema. Please provide the full CREATE TABLE statement, including all indexes.
Query plan? Is that something that you can post? That info probably won't change anything, but it is better to know that it won't than to guess that it won't and be wrong ;-)
Is there physical / external fragmentation on the data file? While this might not be a large factor here, since you are using "consumer-grade SATA" and not SSD or even Super-Expensive SATA, the effect of sub-optimally ordered sectors will be more noticeable, especially as the number of those sectors that needs to be read increases.
What are the exact results of the following query:

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),
                          OBJECT_ID(N'dbo.SchemaName.TableName'), 1, 0, N'LIMITED');

UPDATE

It occurred to me that I should try to reproduce this scenario to see if I experience similar behavior. So, I created a table with several columns (similar to the vague description in the Question), and then populated it with 1 million rows, and the XML column has approximately 15k of data per row (see code below).

What I found is that doing a SELECT TOP 1000 * FROM TABLE completed in 8 seconds the first time, and 2 - 4 seconds each time thereafter (yes, executing DBCC DROPCLEANBUFFERS before each run of the SELECT * query). And my several-year-old laptop is not fast: SQL Server 2012 SP2 Developer Edition, 64 bit, 6 GB RAM, dual 2.5 Ghz Core i5, and a 5400 RPM SATA drive. I am also running SSMS 2014, SQL Server Express 2014, Chrome, and several other things.

Based on the response time of my system, I will repeat that we need more info (i.e. specifics about the table and data, results of the suggested tests, etc) in order to help narrow down the cause of the 20 - 25 second response time that you are seeing.

SET ANSI_NULLS, NOCOUNT ON;
GO

IF (OBJECT_ID(N'dbo.XmlReadTest') IS NOT NULL)
BEGIN
    PRINT N'Dropping table...';
    DROP TABLE dbo.XmlReadTest;
END;

PRINT N'Creating table...';
CREATE TABLE dbo.XmlReadTest
(
    ID INT NOT NULL IDENTITY(1, 1),
    Col2 BIGINT,
    Col3 UNIQUEIDENTIFIER,
    Col4 DATETIME,
    Col5 XML,
    CONSTRAINT [PK_XmlReadTest] PRIMARY KEY CLUSTERED ([ID])
);
GO

DECLARE @MaxSets INT = 1000,
        @CurrentSet INT = 1;

WHILE (@CurrentSet <= @MaxSets)
BEGIN
    RAISERROR(N'Populating data (1000 sets of 1000 rows); Set # %d ...',
              10, 1, @CurrentSet) WITH NOWAIT;
    INSERT INTO dbo.XmlReadTest (Col2, Col3, Col4, Col5)
        SELECT  TOP 1000
                CONVERT(BIGINT, CRYPT_GEN_RANDOM(8)),
                NEWID(),
                GETDATE(),
                N'<test>'
                  + REPLICATE(CONVERT(NVARCHAR(MAX), CRYPT_GEN_RANDOM(1), 2), 3750)
                  + N'</test>'
        FROM        [master].[sys].all_columns sac1;

    IF ((@CurrentSet % 100) = 0)
    BEGIN
        RAISERROR(N'Executing CHECKPOINT ...', 10, 1) WITH NOWAIT;
        CHECKPOINT;
    END;

    SET @CurrentSet += 1;
END;

--

SELECT COUNT(*) FROM dbo.XmlReadTest; -- Verify that we have 1 million rows

-- O.P. states that the "clustered index fragmentation is close to 0%"
ALTER INDEX [PK_XmlReadTest] ON dbo.XmlReadTest REBUILD WITH (FILLFACTOR = 90);
CHECKPOINT;

--

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET STATISTICS IO, TIME ON;
SELECT TOP 1000 * FROM dbo.XmlReadTest;
SET STATISTICS IO, TIME OFF;

/*
Scan count 1, logical reads 21,       physical reads 1,     read-ahead reads 4436,
              lob logical reads 5676, lob physical reads 1, lob read-ahead reads 3967.

 SQL Server Execution Times:
   CPU time = 171 ms,  elapsed time = 8329 ms.
*/

And, because we want to factor out the time taken to read the non-LOB pages, I ran the following query to select all but the XML column (one of the tests I suggested above). This returns in 1.5 seconds fairly consistently.

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET STATISTICS IO, TIME ON;
SELECT TOP 1000 ID, Col2, Col3, Col4 FROM dbo.XmlReadTest;
SET STATISTICS IO, TIME OFF;

/*
Scan count 1, logical reads 21,    physical reads 1,     read-ahead reads 4436,
              lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1666 ms.
*/

Conclusion (for the moment)
Based on my attempt to recreate your scenario, I don't think we can point to either the SATA drive or non-sequential I/O as the main cause of the 20 - 25 seconds, especially because we still don't know how fast the query returns when not including the XML column. And I was not able to reproduce the large number of Logical Reads (non-LOB) that you are showing, but I have a feeling that I need to add more data to each row in light of that and the statement of:

  ~90% of table pages are LOB_DATA

My table has 1 million rows, each having just over 15k of XML data, and sys.dm_db_index_physical_stats shows that there are 2 million LOB_DATA pages. The remaining 10% would then be 222k IN_ROW data pages, yet I only have 11,630 of those. So once again, we need more info regarding the actual table schema and actual data.


Yes, one table is probably enough. You don't usually gain anything by splitting the messages into multiple tables, i.e. horizontal partitioning. On the contrary, it will usually make your queries more complex and slower.

However, horizontal partitioning does make each index smaller, thereby potentially resulting in faster queries provided your data is segregated into e.g. different countries or regions, and you have an obvious, robust, implicit way to identify in which table a particular row will be found without first needing to search the index.

But in most cases, a single table is what you want.


Generally speaking when you want to fix a big-ball-of-mud architecture it is best to focus on one small aspect that you can fix relatively quickly with maximum payoff. What that thing is will vary depending on the system. Once you identify that thing then implement it and look for an opportunity to fix something else.  Over time your big-ball-of-mud will get more and more under control.  This is assuming that the rest of the team is on board with fixing the mess you're in.  If they aren't committed to moving away from the status quo then you'll be fighting a battle you can't hope to win.
Adding a few indexes seems like a quick and easy win, so that will probably be a good place to start, but YMMV.
In parting keep in mind what Warren P mentioned in a comment:

A mess that was made over several years is seldom less than several years work to clean up.

You and the rest of the team need to be committed to this clean-up over the long term if there is any hope for success.

Responses to OP's comments:

Wouldn't it be better to start with database normalization? I mean in a development environment, in order to end up with a fully structured database? There are like 6 tables which HAVE TO be separated, in order to be more "elastic" for future requests.

Normalization is likely to touch many queries/statements across the system. That is a very intrusive change that will take lots of time and effort to show a benefit. Making small incremental steps will be easier, quicker, and steadily improve your life. Before considering normalizing I would move all data access into stored procedures. That will give you tangible benefits quickly, allow you to spread out the work, and make the normalization step easier.

[T]he entire team is ready, and we are talking about a 6 month - 9 month project of restructuring everything, the big bosses are still undecided however.

Read this post by Joel Spolsky before advocating stopping everything and pursuing the grand redesign. Blocking updates to the existing system while you restructure the old system over the next 6 to 9 months makes it hard for the business to succeed. Supporting the current product while concurrently improving the same code base is the smartest play IMO (in my opinion). In this way the code base improvements will just be a symbiotic extension of supporting the current product.
Note: I'm not suggesting you have one team doing a rewrite (version 2.0) while another is supporting the current system (version 1.x).


Query slower with index on 14M rows table

You should play with it a bit, because it might not be clear beforehand what solution will produce the best results.

A few points to consider

It is very likely that an index on the date column will provide you with better selectivity, as it has a higher selectivity.
Composite indexes are usually a good idea, but please make sure to chose the order correctly date_time, meterid  vs. meterid, date_time. In most cases it makes more sense to leave columns with dense values (i.e dates, floats) to the end, as any column in the index following them is unlikely to have any effect. ( try meterid, date_time for an index.)
Subselects might force the optimizer to use a specific plan. Try converting it into a join if possible.


Two SQL Server databases share the same MDF and LDF files but have different data

It is not possible to have two databases point to the same database files, except in the weird Autoclose case that you linked to, which no one should be using that setting anyway.

Run the below query to view what physical files the databases are pointing to:

SELECT database_id, name, physical_name
FROM master.sys.master_files

With that information, you should be able to see that the databases are in fact not pointing to the same database files.

You also can see this information in the SSMS GUI. Right click on the database, go to properties and then to Files. Now SCROLL to the right as the physical files aren't visible yet. The combination of Path and File Name will be unique.


Need help in optimising the MySql stored procedure

So, your problem is, that you want to give a string like 1,3,5,7 as parameter to the procedure to actually do a query like delete from foo where id in (proc_parameter), right?

The problem with this is, that the query becomes this: delete from foo where id in ('1,3,5,7'). Then an implicit conversion from string to integer happens and the only entry that gets deleted is the one with id 1.

The most convenient solution would be, that the query with the replaced parameter wouldn't have those single-quotes. You can do this with prepared statements. The procedure would then look something like this:

DELIMITER $$
CREATE PROCEDURE sp_del(IN my_array VARCHAR(100))
BEGIN
    SET @sql := CONCAT('DELETE FROM foo WHERE id IN (', my_array, ');');
    SELECT @sql AS the_fancy_query_I_will_execute; -- debugging, not needed ;)
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

and called with

CALL sp_del('1,3,5,7');

But that all being said, why don't you just solve this on application level instead? That's what I would do!


Does creating / updating statistics have an impact on the transaction log?

It doesn't really in any measurable fashion
Statistics are stored as histograms, with at most 200 entries per index/column. This is trivial, compared to the actual column or index.
See https://www.simple-talk.com/sql/performance/sql-server-statistics-questions-we-were-too-shy-to-ask/

Where are the statistics actually stored? Do they take up much space? Can I save space by only having the essential ones?
The statistics themselves are stored within your database in a series of internal tables that include sysindexes. You can view some of the information about them using the system views sys.stats and sys.indexes, but the most detail is gleaned using a function, DBCC SHOW_STATISTICS. The statistics themselves take very little space. The header is a single row of information. The density is a set of rows with only three columns, equal in the number of rows to the number of columns defining the key columns of the statistic. Then you have the histogram. The histogram is up to 200 rows and never exceeds that amount. This means statistics do not require much room at all. While you can save a little bit of space by removing unneeded statistics, the space savings are too small to ever be worthwhile .

Because the system tables are updated, then there is some minor data change that will be logged, but as I said it is trivial and not worth considering at all


Select results inclusively based on NOW()

Subtract the seconds part of NOW() from the value of NOW() and you will get the timestamp marking the beginning of the current minute. Now you can subtract your interval to get the beginning of the minute that was 30 minutes ago:

WHERE (date > (NOW() - INTERVAL SECOND(NOW()) SECOND - INTERVAL 30 MINUTE))


Count hours when a specific value is higher than a treshold

Q: This is the query that i've built, actually i'm able to select only the rows that have the kw value greater than 1 and group them by
month, but i'm not able to figure out how to calculate the production
hours:

You need to get each measurement and the next measurement into a single row. This is typically what the lag window function does:
SELECT mt.insert_time,
       lag(mt.insert_time) OVER w AS next_time
       (mt.data->>'kw')::int AS curr_value,
       ((lag(mt.data) OVER w)->>'kw')::int AS next_value
FROM mytable mt
WHERE sensor = 7
WINDOW w AS (order by pk)  -- or order by insert_time if you prefer

Once both the current and next measurements are in the same row, you have the duration between them (=next_time - insert_time) and can sum and filter out rows when the kw value went from above 1 to below 1 or the opposite, or were both below 1.


Don't know how to make two selects bridge together ad run a single query

you need to reference the race fields in the initial SELECT statement (before the FROM clause) with an alias to the inner select.  I'm not quite sure what it is that you're trying to accomplish, but this is certainly more on track with what you desire.  Try this for starters

SELECT
    s.student_number,
    s.dob,
    s.gender,
    s.ethnicity,
    RaceTable.race1,
    RaceTable.race2,
    RaceTable.race3
FROM students s INNER JOIN
    (SELECT
        lastfirst, grade_level,
        max(case when rn = 1 then racecd end) race1, -- find 1st row
        max(case when rn = 2 then racecd end) race2, -- find 2nd row
        max(case when rn = 3 then racecd end) race3  -- find 3rd row
     FROM
         (SELECT
             st.lastfirst,st.grade_level,sr.racecd,
             st.student_number,st.dob,st.gender,st.ethnicity,
             row_number() -- one row per race, numbered 1,2,3,...
                 over (partition by sr.studentid
                 order by sr.racecd) rn
          FROM students st
          JOIN studentrace sr
              ON st.id=sr.studentid
          WHERE st.enroll_status=0
         ) AS RTinner
     GROUP BY lastfirst,grade_level
  -- ORDER BY grade_level asc
    ) AS RaceTable
ON s.lastfirst=Racetable.lastfirst AND
   s.grade_level=Racetable.grade_level


Getting exception on running sh.status() command on MongoDB sharded cluster

It was becuase notablescan property enabled in my mongo config servers. After disable notablescan, command is working fine. you can check status of notablescan by running this command.

db.adminCommand( { getParameter: 1, notablescan: 1 } )

Output would be like below if notablescan is enabled.

{
  "notablescan": true,
  "ok": 1
}


How do I count the number of different child id's belonging to a parent record?

There are various ways to solve this problem, but with a composite index on (album_id, artist_id) most DBMS will manage to use only this index (a full index scan) with the following query and thus be quite efficient:

SELECT album_id
FROM track
GROUP BY album_id
HAVING MIN(artist_id) < MAX(artist_id) ;

If you also want the number of artists per album, you can add COUNT(DISTINCT artist_id) in the select list.

A less obfuscated alternative condition is to use (which could be less efficient than the above MIN < MAX method):

HAVING COUNT(DISTINCT artist_id) > 1 ;


current_timestamp default column value working in Windows but not in Linux

The reason is the different versions installed in the two environments.

The option for DEFAULT CURRENT_TIMESTAMP for datetime columns was added in 5.6 version (specifically in 5.6.5). See the doumentation about the DATE, DATETIME, and TIMESTAMP Types:

  The TIMESTAMP and (as of MySQL 5.6.5) DATETIME data types offer automatic initialization and updating to the current date and time. For more information, see Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

Most probably your Linux installation has an older version while the Windows has a newer. The solution would be to upgrade to the newer version (or have it installed besides the old).

General info about upgrading from 5.5 to 5.6 here: Upgrading from MySQL 5.5 to 5.6 and specific about upgrading in Ubuntu 14.04 LTS.

Don't forget to backup your data before you upgrade and run mysql_upgrade immediately after.


Your original connection string:

sqlplus64 "user/pw@pashratgdb01.lex.bb.com:1876/atgpen1.lex.bb.com"

This specifices atgpen1.lex.bb.com as a service name. The JDBC syntax you used specifies a SID. Service name identifies a service within a database. SID identifies an instance within a database. Those two are not the same.

Instead of this:

jdbc:oracle:thin:@pashratgdb01.lex.bb.com:1876:atgpen1.lex.bb.com"

Use this:

jdbc:oracle:thin:@//pashratgdb01.lex.bb.com:1876/atgpen1.lex.bb.com"


Why do I have 2 connections when im using 1 client?

The second connection gets started when you start the query tool.


Can auto failover cause split-brain? (SQL AlwaysOn)

This following from Microsoft seems to indicate that only a person with SA, doing something stupid can cause it.

Yes, or doing completely unsupported things like using DSFR on a fileshare witness. If you're staying in supported land and aren't going around forcing stuff up, the worst case is the entire cluster will be offline until an admin can intervene.

  There are a lot of variables, and I wonder if something like changing the Dynamic quorum (is on by default) could create a scenario where a split-brain scenario exist.

I wouldn't turn this off, it's actually your friend. This stops the need from having to change the node weight per node every time something happens in the cluster. BTW, you can't turn dynamic witness off.

There are extremely rare times that I would even consider turning dynamic quorum off and in those times it's because we've looked at the way we want the cluster to work and we specifically want it to fail if certain things happen. It's not something that you'd want to do without great testing and consideration.

  Can auto failover be configured so a split-brain scenario is possible?

Absolutely not.


Have fun.

with
  num_years as (select extract(year from max(cond_date)) - extract(year from min(cond_date)) +1 as years from road_condition where road_id = 100),
  generated_dates as (select add_months(trunc((select min(cond_date) from road_condition where road_id = 100), 'YYYY'), (rownum - 1) * 12) as cond_date from dual connect by level <= (select years from num_years)),
  generated_data as (select rc.cond_id, rc.road_id,nvl(rc.cond_date, gd.cond_date) as cond_date, rc.condition
    from generated_dates gd left join (select * from road_condition where road_id = 100) rc on (gd.cond_date = trunc(rc.cond_date, 'YYYY'))
  ),
  data1 as (select
      last_value(cond_id ignore nulls) over (order by cond_date) as cond_id,
      cond_id as original_cond_id,
      road_id, cond_date, condition,
      last_value(condition ignore nulls) over (order by cond_date) as s1,
      lead(condition ignore nulls) over (order by cond_date) as s2
    from generated_data
  ),
  data2 as (
    select
      cond_id, road_id, original_cond_id, cond_date, condition, s1, s2,
      count(*) over (partition by cond_id) + 1 as s,
      count(*) over (partition by cond_id order by cond_date rows between unbounded preceding and current row) as s0
   from data1
  ),
  data3 as (
    select original_cond_id as cond_id, road_id, cond_date,
     round(nvl(condition, (1-s0/s)*(s1-s2)+s2)) as condition
    from data2
  ),
  final_data as (
    select cond_id, road_id, cond_date,
      case when condition between 15 and 20 then condition end as cond_good,
      case when condition between 11 and 14 then condition end as cond_overlay,
      case when condition between 8 and 10 then condition end as cond_fdr,
     case when condition between 0 and 7 then condition end as cond_recon
    from data3
  ),
  data_for_graph as
  (
    select
      cond_id, road_id, cond_date, cond_good,
      case when cond_good is not null and lead(cond_overlay) over (order by cond_date) is not null then cond_good else cond_overlay end as cond_overlay,
      case when cond_overlay is not null and lead(cond_fdr) over (order by cond_date) is not null then cond_overlay else cond_fdr end as cond_fdr,
      case when cond_fdr is not null and lead(cond_recon) over (order by cond_date) is not null then cond_fdr else cond_recon end as cond_recon
    from final_data
  )
select * from data_for_graph order by cond_date;

Output:

   COND_ID    ROAD_ID COND_DATE   COND_GOOD COND_OVERLAY   COND_FDR COND_RECON
---------- ---------- ---------- ---------- ------------ ---------- ----------
         1        100 1984-11-01         18
                      1985-01-01         17
                      1986-01-01         17
                      1987-01-01         16
                      1988-01-01         16
                      1989-01-01         15
                      1990-01-01         15           15
                      1991-01-01                      14
                      1992-01-01                      14
                      1993-01-01                      13
                      1994-01-01                      13
                      1995-01-01                      12
                      1996-01-01                      12
                      1997-01-01                      12
                      1998-01-01                      11
                      1999-01-01                      11         11
                      2000-01-01                                 10
                      2001-01-01                                 10
                      2002-01-01                                  9
                      2003-01-01                                  9
                      2004-01-01                                  8
                      2005-01-01                                  8          8
                      2006-01-01                                             7
                      2007-01-01                                             7
                      2008-01-01                                             6
         2        100 2009-01-01                                             6
                      2010-01-01                                             5
                      2011-01-01                                             5
         3        100 2012-06-19                                             4
                      2013-01-01                                             4
                      2014-01-01                                             4
         4        100 2015-04-29                                             4

You can follow the steps by selecting from the CTEs in the last line in the order I listed them (so select * from num_years, then select * from generated_dates, then select * from generated_data, and so on).


a company may have both an inactive and an active account in M

SO I assume it can be only one active and one inactive row. Here is a solution with JOINs only. If there can be multiple (active) rows, you should use EXISTS instead, as @ypercube already mentioned in his comment.

SELECT R.*, M1.active
FROM   R
JOIN   M AS M1 ON M1.company = R.company
              AND M1.account = R.account
              AND M1.active  = 'inactive'
LEFT   JOIN M AS M2 ON M2.company = R.company
                   AND M2.account = R.account -- or no active account at all?
                   AND M2.active  = 'active'
WHERE  M2.company IS NULL;

As @ypercube commented: it did not become entirely clear whether you don't want any active account to invalidate the company or just a matching active account.

I went with matching account in my query. Remove AND M2.account = R.account from the JOIN condition if you want any.


Ok, it was simple :)

I wanted to limit each database to 256MB of space per student. In order to achieve that, I enabled smallfiles in /etc/mongod.conf and ran the mongod process with mongod -f /etc/mongod.conf --quota --quotaFiles 4 which limited the size of the database to 256MB.

smallfiles starts preallocating space with 16MB, then 32MB, 64MB and finally 128MB. Add this up and you get 240MB plus 16MB file for the namespace = 256MB

This however doesn't stop them from creating another database with the same user. Any help on this?


Making a field greater than the limit unique

A realistic email limit could have a length of 64
email addresses are never utf8, only ANSI
innodb_large_prefix can give you a bigger index.
generated column
with a HASH as a unique index is just as good, like:

ALTER TABLE members ADD email_hash VARBINARY(56) AS (SHA2(email, 224)) UNIQUE KEY


On a mac, your MySQL installation is located in:

/usr/local/mysql/

This is the folder structure:

$ ls -lah /usr/local/mysql/
total 64
drwxr-xr-x  16 root    wheel   544B Nov 13 15:54 .
drwxrwxr-x  27 root    admin   918B Nov 24 11:42 ..
-rw-r--r--   1 root    wheel    18K Nov 13 15:47 COPYING
-rw-r--r--   1 root    wheel   7.2K Nov 13 15:47 INSTALL-BINARY
-rw-r--r--   1 root    wheel   2.5K Nov 13 15:47 README
drwxr-xr-x  46 root    wheel   1.5K Nov 13 15:54 bin
drwxr-xr-x  18 _mysql  wheel   612B Apr 27 13:37 data
drwxr-xr-x   6 root    wheel   204B Nov 13 15:54 docs
drwxr-xr-x  37 root    wheel   1.2K Nov 13 15:54 include
drwxr-xr-x  12 root    wheel   408B Nov 23 08:58 lib
drwxr-xr-x   5 root    wheel   170B Apr 25 06:48 man
drwxr-xr-x  19 root    wheel   646B Nov 23 08:58 mysql-test
drwxr-xr-x   3 root    wheel   102B Nov 13 15:54 scripts
drwxr-xr-x  32 root    wheel   1.1K Nov 13 15:54 share
drwxr-xr-x  28 root    wheel   952B Nov 13 15:54 sql-bench
drwxr-xr-x  16 root    wheel   544B Nov 13 15:54 support-files

You probably want to copy all of it to your local disk, then install MySQL from a disk image (DMG), backup the installed data folder, copy your backed up data folder contents into /usr/local/mysql/data, permission the folder to mysql, start mysqld and do an mysql_upgrade.

install mysql server from DMG... then...

 sudo mv /usr/local/mysql/data /usr/local/mysql/data_ORIG
 sudo cp /mybackupfolder/mysql/data /usr/local/mysql/data
 sudo chown -Rf mysql:wheel /usr/local/mysql/data
 sudo su -
 nohup /usr/local/mysql/bin/mysqld_safe &
 exit
 /usr/local/mysql/bin/mysql_upgrade -u root -p
 # read output

*Note: if you have a my.cnf, mv it to /etc/my.cnf.ORIG before you start mysqld.