SQL Server 2008 R2: From a SQL Server Log file, how do I find out what is causing an event

For the purpose of not leaving this question open and in hopes that it helps someone in the future, I wanted to detail the steps I followed to solve my problem.
In following the advice given about running a trace around the time the Recovery model is changed, I found that there was indeed a task that was shrinking the log.

Summary

Using SQL Server Profiler trace allowed me to find out what was causing the problem.


You should do this with a foreign key constraint.

You can create a check constraint with the following definition

CREATE TABLE T
(
Color varchar(10) CHECK (Color in ('red','green','blue'))
)

But there is no way of altering a check constraint definition without dropping it and recreating it (thus requiring all rows to be revalidated against the new definition)

  To modify a CHECK constraint, you must first delete the existing CHECK
  constraint and then re-create it with the new definition.

This is trivial to do with a Foreign Key constraint

CREATE TABLE Colors
  (
     Color VARCHAR(10) PRIMARY KEY
  )

INSERT INTO Colors
VALUES      ('red'),
            ('green'),
            ('blue')

CREATE TABLE T
  (
     Color VARCHAR(10) REFERENCES Colors
  )

Though I'd probably introduce a surrogate key to the Colors table rather than storing the string repeatedly in the main table.

I have come across the argument before that using a check constraint is somehow "more correct" than using foreign keys and a lookup table but the advantages of the lookup table to me are.

Easier and more efficient to add items to the list.
Easier to get a distinct list of allowable colours (e.g. to display in a listbox in your application)
Using a fixed length integer surrogate key can have performance advantages compared to a variable length string both in terms of reducing row size and avoiding fragmentation on updates.

NB: It is possible to have a check constraint reference a scalar UDF that in turn references a table but this approach should be avoided. It does not simulate a foreign key correctly (e.g. does not validate on DELETE FROM Colors)


How to create an index to improve this query

There is no way to test this without your actual data. Theoretically, your optimal index for this query will be on:

agency_id, employer, amount, service_years

This index should allow immediate push-down filtering on agency_id, followed by a stream aggregate instead of the hash aggregate by employer, and the last 2 are just to cover the query, and avoid lookups to the main table to fetch amount and service_years which the query needs for the aggregates.

Generally speaking, when designing indexes, you should consider more than one query, and see if you can find common query patterns that will allow you to create near optimal indexes that will serve multiple types of queries.
The way too common indexing strategy of 'Let's create an optimal index for every painful query' tends to lead to over-indexed databases, which will pose a set of challenges of its own, including modification overhead, and over-sized optimizer search trees.

Let us know if the optimizer uses this index efficiently.


In some cases (perhaps most) the servers are already at capacity physically.  An increase in the number of CPU's would require a motherboard swap.  To add RAM to an existing server could be expensive, depending on how old the server is.  Memory modules more than 5 years old and sourced from a dealer can be prohibitively expensive.

What all this amounts to is that upgrading from an 8 processor box with 32GB RAM to a 64 processor box with 128GB would involve purchasing an entire new server to replace the old one.  This is still vertical scaling from the database/application point of view.  A server that supports this many CPU's is likely far more expensive than 7 servers with 8CPU's each (keeping the original one as the 8th).

As per mustaccio's comment: High-end hardware is not just about more processors and RAM; designing hardware to support throughput and power/cooling requirements for dozens of multicore processors is far from trivial. Thus, a 256-core 2TB RAM server might cost $150000, which is about an order of magnitude more expensive than 64 4-core 32GB commodity servers


Updating Multiple Rows with Data from Column A into Column B?

If you just want the discharge_dt to be overwritten with the admit_dt from the same row, it sounds like you just need

UPDATE patient
   SET discharge_dt = admit_dt
 WHERE facility_id = 'X'
   AND pat_seq IN (<<query that returns the 60,000 keys>>)


Downgrade MySQL 8.0.20 to 8.0.19 on Ubuntu for using XtraBackup

Xtrabackup 8.0.12 should work with MySQL 8.0.20
Percona xtrabackup 8.0.12 release

Percona XtraBackup 8.0.12 now supports backup and restore processing for all versions of MySQL up to and including 8.0.20; previous versions of Percona XtraBackup will not work with MySQL 8.0.20 and higher.


Your First Question

  why does a OPTIMIZE query block the replication catching up ?

The SQL thread in MySQL Replication processes SQL commands from the Relay Logs as a FIFO queue (i.e. first-come, first-server basis). Once the SQL thread see a command, it processes it to completion. Meanwhile, the Seconds_Behind_Master just keeps creeping up.

While the SQL thread on the Slave is running OPTIMIZE TABLE, once you run STOP SLAVE; it SQL thread has complete the running OPTIMIZE TABLE before the SQL thread can terminate. That's why STOP SLAVE; hangs. To verify this, open another mysql session and run SHOW PROCESSLIST;, You should see a line that has OPTIMIZE TABLE with the status Killing slave....

Your Second Question

  can I prevent those OPTIMIZE queries ? I didn't put any cron to do that, and as far as I can see, nothing in /etc/cron.{d,daily,hourly,weekly} does this.

Someone or something is running the OPTIMIZE TABLE. You have to hunt it down in all your jobs or PHP apps. Use the mysqlbinlog utility to dumps all the binary logs into a text file. Then, grep the text file for OPTIMIZE TABLE or optimize table. The timestamp of when it was submitted into the binlogs will be a few lines about the command.

When you do find that OPTIMIZE TABLE command in your jobs or PHP code, simply run this:

SET SQL_LOG_BIN=0; OPTIMIZE TABLE tblname; SET SQL_LOG_BIN=1;

This will not register the OPTIMIZE TABLE in the binlogs, thus preventing a Slave from replicating it.

As was just pointed out in the comments below, You can also replace

OPTIMIZE TABLE tblname;

with one of the following:

OPTIMIZE NO_WRITE_TO_BINLOG TABLE tblname;
OPTIMIZE LOCAL TABLE tblname;

to restrict the OPTIMIZE TABLE command from landing in the binlogs and thus prevent replication of it.


Is it possible to edit data inside an unencrypted SQL Server backup file (*.bak) without SSMS?

In my experience, the root cause of these symptoms is most commonly a race condition in the application and/or T-SQL code rather than data corruption due to hardware, network, or malicious activity. Race conditions are insidious because the undesired outcome occurs by happenstance, difficult to reproduce, and may rarely occur.
One must use transactions, along with the appropriate isolation level (or locking hints), to ensure aggregated values like Remaining Qty in the items table reflects the related Purchase and Sales transactions.
EDIT
Testing concurrency problems is a challenge because multiple sessions are required and often many iterations too. Below is an example technique using multiple SSMS query windows I use to reproduce production issues as well as proactively identify suspect code before release. Be aware that race conditions are a matter of timing so even due diligence with testing may not catch rare occurrences.
--example setup script with race condition vulnerability
USE YourDatabase;
DROP TABLE dbo.Transactions;
DROP TABLE dbo.Item;
CREATE TABLE dbo.Item(
      ItemNumber int NOT NULL
          CONSTRAINT PK_Item PRIMARY KEY
    , RemainingQTY int NOT NULL
    );
CREATE TABLE dbo.Transactions(
      TransactionID int NOT NULL IDENTITY
        CONSTRAINT PK_Traansactions PRIMARY KEY
    , ItemNumber int NOT NULL
          CONSTRAINT FK_Traansactions_Item FOREIGN KEY REFERENCES dbo.Item(ItemNumber)
          INDEX idx_Transactions_ItemNumber
    , TransactionType varchar(10) NOT NULL
        CONSTRAINT CK_Transactions_TransactionType CHECK (TransactionType IN('Purchase', 'Sale'))
    , QTY int
);
--load 5000 items
WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t10k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num  FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c CROSS JOIN t10 AS d)
INSERT INTO dbo.Item(ItemNumber, RemainingQTY)
SELECT num, 1000
FROM t10k
WHERE num <= 5000;
--load 5000 purchase transactions
WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t10k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num  FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c CROSS JOIN t10 AS d)
INSERT INTO dbo.Transactions(ItemNumber, TransactionType, QTY)
SELECT ItemNumber, 'Purchase', 1000
FROM dbo.Item;
GO

CREATE OR ALTER PROC dbo.usp_InsertTransaction
      @ItemNumber int
    , @TransactionType varchar(10)
    , @QTY int
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @RemainingQTY int;
BEGIN TRY

    BEGIN TRAN;

    SELECT @RemainingQTY = RemainingQTY
    FROM dbo.Item
    WHERE ItemNumber = @ItemNumber;

    IF @TransactionType = 'Purchase'
        SET @RemainingQTY = @RemainingQTY + @QTY
    ELSE
        SET @RemainingQTY = @RemainingQTY - @QTY;

    UPDATE dbo.Item
    SET RemainingQTY = @RemainingQTY
    WHERE ItemNumber = @ItemNumber;

    INSERT INTO dbo.Transactions VALUES
        (@ItemNumber, @TransactionType, @QTY);

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

Open 3 SSMS query windows and run these scripts as instructed:
--Step 1: Run script in SSMS query window 1 to acquire exclusive lock to sync start of test executions
DECLARE @return_code int;
EXEC @return_code = sp_getapplock
     @Resource = 'concurrency_test'
    ,@LockMode = 'exclusive'
    ,@LockOwner = 'session';
RAISERROR('sp_getapplock return code is %d', 0, 0, @return_code) WITH NOWAIT;
GO

--Step 2: Run this script in SSMS query windows 2 and 3 to acquire a shared lock on same resource as session 1.
--These will block until session 1 lock is released to start test.
DECLARE @return_code int;
EXEC @return_code = sp_getapplock
     @Resource = 'concurrency_test'
    ,@LockMode = 'shared'
    ,@LockOwner = 'session';
RAISERROR('sp_getapplock return code is %d', 0, 0, @return_code) WITH NOWAIT;
GO
--execute queries to insert transactions and update RemainingQTY 100 times
DECLARE @IterationCount int = 0;
WHILE @IterationCount < 100
BEGIN
    EXEC dbo.usp_InsertTransaction
          @ItemNumber = 1000
        , @TransactionType = 'Sale'
        , @QTY = 1;
    SET @IterationCount +=  1;
END;
GO
--release lock after test completes
DECLARE @return_code int;
EXEC @return_code = sp_releaseapplock
     @Resource = 'concurrency_test'
    ,@LockOwner = 'session';
RAISERROR('sp_releaseapplock return code is %d', 0, 0, @return_code) WITH NOWAIT;
GO

--Step 3: Run script in SSMS query window 1 to release lock to start test
DECLARE @return_code int;
EXEC @return_code = sp_releaseapplock
     @Resource = 'concurrency_test'
    ,@LockOwner = 'session';
RAISERROR('sp_releaseapplock return code is %d', 0, 0, @return_code) WITH NOWAIT;
GO

--Step 4: Run this script to validate RemainingQTY after test completes
WITH transaction_summary AS (
    SELECT
          t.ItemNumber
        , SUM(CASE WHEN t.TransactionType = 'Purchase' THEN t.QTY END) AS Purchase
        , SUM(CASE WHEN t.TransactionType = 'Sale' THEN t.QTY END) AS Sale
    FROM dbo.Transactions AS t
    WHERE t.ItemNumber = 1000
    GROUP BY t.ItemNumber
)
SELECT
      i.ItemNumber
    , i.RemainingQTY AS ItemRemainingQTY
    , t.Purchase
    , t.Sale
    , t.Purchase - t.Sale AS ActualTramsactionsRemainingQTY
FROM dbo.Item AS i
JOIN transaction_summary AS t
    ON t.ItemNumber = i.ItemNumber
WHERE i.RemainingQTY <> t.Purchase - t.Sale;
GO

Below is the output of the step 4 validation query after a test on my box that shows item RemainingQTY is invalid. This happened 31 times of 200  total proc calls on my test machine. The cause is different sessions both executed the SELECT query at about the same time and read the same RemainingQTY value. One session reduced the RemainingQTY as desired but the value was subsequently overwritten by the other session based on stale data. This test ran in a tight loop so it's more likely to occur than in a common prod workload.

ItemNumber
ItemRemainingQTY
Purchase
Sale
ActualTramsactionsRemainingQTY

1000
831
1000
200
800

One way to fix this code bug is to refactor the proc to avoid the local variable. This will serialize updates to the item row and perform slightly better too by eliminating the SELECT query:
CREATE OR ALTER PROC dbo.usp_InsertTransaction
      @ItemNumber int
    , @TransactionType varchar(10)
    , @QTY int
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @RemainingQTY int;
BEGIN TRY

    BEGIN TRAN;

    UPDATE dbo.Item
    SET RemainingQTY = RemainingQTY +
        CASE @TransactionType WHEN 'Purchase' THEN @RemainingQTY ELSE @RemainingQTY * -1 END
    WHERE ItemNumber = @ItemNumber;

    INSERT INTO dbo.Transactions VALUES
        (@ItemNumber, @TransactionType, @QTY);

    COMMIT;

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


SSMS constraint script: Purpose of second alter table statement?

The second line is redundant but I agree the overuse of CHECK in that syntax is confusing.

According to the ALTER TABLE documentation the existing data is checked anyway if the WITH CHECK ADD option is used and the newly added constraint is enabled.

  WITH CHECK | WITH NOCHECK
  Specifies whether the data in the table is
  or is not validated against a newly added or re-enabled FOREIGN KEY or
  CHECK constraint. If not specified, WITH CHECK is assumed for new
  constraints, and WITH NOCHECK is assumed for re-enabled constraints.

The second line enables the constraint, again from the documentation, but that constraint was already enabled.

  { CHECK | NOCHECK } CONSTRAINT   Specifies that constraint_name is
  enabled or disabled. This option can only be used with FOREIGN KEY and
  CHECK constraints. When NOCHECK is specified, the constraint is
  disabled and future inserts or updates to the column are not validated
  against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE
  constraints cannot be disabled.

However, the constraint is already enabled as can be evidenced from this code:

CREATE TABLE [dbo].[constraintdemo](
    [constraintfield] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[constraintdemo]  WITH CHECK ADD  CONSTRAINT [CK_constraintdemo] CHECK  (([constraintfield]>(0)))
GO

INSERT INTO constraintdemo (constraintfield) VALUES (-1)

Which results in:

  Msg 547, Level 16, State 0, Line 12   The INSERT statement conflicted
  with the CHECK constraint "CK_constraintdemo". The conflict occurred
  in database "PlayGround", table "dbo.constraintdemo", column
  'constraintfield'.   The statement has been terminated.

See this dbfiddle where you can run the above code

If you want evidence that the existing data is effectively validated by the WITH CHECK you can run this code:

CREATE TABLE [dbo].[constraintdemo](
    [constraintfield] [int] NULL
) ON [PRIMARY]

GO

INSERT INTO constraintdemo (constraintfield) values (-1),(-2)
GO

ALTER TABLE [dbo].[constraintdemo]  WITH CHECK ADD  CONSTRAINT [CK_constraintdemo] CHECK  (([constraintfield]>(0)))
GO

Which returns:

  (2 row(s) affected)   Msg 547, Level 16, State 0, Line 21   The ALTER
  TABLE statement conflicted with the CHECK constraint
  "CK_constraintdemo". The conflict occurred in database "PlayGround",
  table "dbo.constraintdemo", column 'constraintfield'.

Again: dbfiddle

In this example it doesn't matter if it's a check constraint or a foreign key constraint as evidenced by this somewhat more complicated example (note that I commented out the second line):

CREATE TABLE [dbo].[keytable](
    [key] [nchar](10) NOT NULL,
 CONSTRAINT [PK_keytable] PRIMARY KEY CLUSTERED
(
    [key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[valuetable](
    [key] [nchar](10) NULL,
    [value] [nchar](10) NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[valuetable]  WITH CHECK ADD  CONSTRAINT [FK_valuetable_keytable] FOREIGN KEY([key])
REFERENCES [dbo].[keytable] ([key])
GO

--ALTER TABLE [dbo].[valuetable] CHECK CONSTRAINT [FK_valuetable_keytable]
GO

INSERT INTO keytable ([key]) VALUES (1),(2);
GO

INSERT INTO valuetable ([key],[value]) VALUES (3,'test');
GO

This still results in an error showing the foreign key is enabled:

  Msg 547 Level 16 State 0 Line 1   The INSERT statement conflicted with
  the FOREIGN KEY constraint "FK_valuetable_keytable". The conflict
  occurred in database "fiddle_7378e515ee284b358b4e2edbc07d1329", table
  "dbo.keytable", column 'key'.   Msg 3621 Level 0 State 0 Line 1
  The statement has been terminated.

Dbfiddle here

And again we can turn things around to prove that it checks existing data on creation using this code:

CREATE TABLE [dbo].[keytable](
    [key] [nchar](10) NOT NULL,
 CONSTRAINT [PK_keytable] PRIMARY KEY CLUSTERED
(
    [key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[valuetable](
    [key] [nchar](10) NULL,
    [value] [nchar](10) NULL
) ON [PRIMARY]

GO
INSERT INTO keytable ([key]) VALUES (1),(2);
GO

INSERT INTO valuetable ([key],[value]) VALUES (3,'test');
GO

ALTER TABLE [dbo].[valuetable]  WITH CHECK ADD  CONSTRAINT [FK_valuetable_keytable] FOREIGN KEY([key])
REFERENCES [dbo].[keytable] ([key])
GO

Which returns:

  Msg 547 Level 16 State 0 Line 1   The ALTER TABLE statement conflicted
  with the FOREIGN KEY constraint "FK_valuetable_keytable". The conflict
  occurred in database "fiddle_59761b4587c14f2b96b8029a10de6229", table
  "dbo.keytable", column 'key'.

dbfiddle here


The difference between sys/ and sys@xe (Oracle)

sqlplus sys/ as sysdba connects directly to the database without using networking (TNS. This type of connection is called a bequeath connection.

sqlplus sys@xe as sysdba connects through the listener.


"When specifying a local instance, always use (local)" -- why?

This is from memory and without reference so my recall may not be accurate.

IIRC for 2005+ "(local)", "localhost" and "." are equivalent and will use the Shared Memory protocol. Prior to 2005, "(local)" and "." would use Shared Memory whereas localhost would not necessarily do so by default. Use of machine name or FQDN could result in a DNS lookup so is not recommended for a local machine connection.

Lastly, the "(local)" term (including brackets) is a special keyword, rather than a reference to the local machine.


DB collation is used for comparison instead of column collation

If you do not specify a collation when creating a table (regular or temporary or variable), then the database's default collation is used. When creating a temporary table (not table variable), it exists in tempdb, so tempdb's default collation is used. TempDB's default collation is derived from the server default since the server default was used to create the model database which is in turn used to create all new databases, including tempdb.

Fortunately, you can use the COLLATE clause to specify which collation to use for the column(s), regardless of any database's default collation. And if you are creating temporary tables in databases that have different collations and you don't always know ahead of time what the collation will need to be, then you can specify DATABASE_DEFAULT which will use the current database's default collation instead of tempdb's default collation.

You can see this behavior by running the following tests. The setup creates a database with a collation that I assume is not your server's default (so that it can be obviously different from tempdb).

Test Setup

USE [master];
IF (DB_ID(N'CollateEBCDIC') IS NULL)
BEGIN
  CREATE DATABASE [CollateEBCDIC]
    COLLATE SQL_EBCDIC277_CP1_CS_AS;
END;
GO

Test Queries

1) Creating a table without specifying the COLLATE clause will use that database's default collation:

USE [CollateEBCDIC];

CREATE TABLE dbo.TestCollate (Col1 NVARCHAR(50));

SELECT name, collation_name
FROM   sys.columns col
WHERE  col.[object_id] = OBJECT_ID(N'dbo.TestCollate');

Returns:

name    collation_name
Col1    SQL_EBCDIC277_CP1_CS_AS

2) Creating a temporary table without specifying the COLLATE clause will use tempdb's default collation:

-- DROP TABLE #TestCollate1;
CREATE TABLE #TestCollate1 (Col1 NVARCHAR(50));

SELECT name, collation_name
FROM   tempdb.sys.columns col
WHERE  col.[object_id] = OBJECT_ID(N'tempdb.dbo.#TestCollate1');

Returns:

name    collation_name
Col1    SQL_Latin1_General_CP1_CI_AS

3) Creating a temporary table with specifying the COLLATE clause will use the specified collation. If you don't always know what that collation will be, you can specify DATABASE_DEFAULT which will use that database's default collation:

-- DROP TABLE #TestCollate2;
CREATE TABLE #TestCollate2 (Col1 NVARCHAR(50) COLLATE DATABASE_DEFAULT);

SELECT name, collation_name
FROM   tempdb.sys.columns col
WHERE  col.[object_id] = OBJECT_ID(N'tempdb.dbo.#TestCollate2');

Returns:

name    collation_name
Col1    SQL_EBCDIC277_CP1_CS_AS

4) Declaring a table variable without specifying the COLLATE clause will use the current database's default collation:

SELECT name, collation_name
FROM   sys.dm_exec_describe_first_result_set(N'
DECLARE @TestCollate3 TABLE (Col1 NVARCHAR(50));
INSERT INTO @TestCollate3 (Col1) VALUES (NCHAR(42));
SELECT * FROM @TestCollate3;
', NULL, NULL);

Returns:

name    collation_name
Col1    SQL_EBCDIC277_CP1_CS_AS

Test Cleanup

DROP DATABASE [CollateEBCDIC];

Contained Database Wackiness

When working within a Contained Database, there are two differences from the behavior noted above:

Temporary Tables use the Database's default Collation (i.e. DATABASE_DEFAULT) for string columns that are not declared using the COLLATE keyword. Test #2 shows Temporary Tables using the Instance's default Collation.
Database-level meta-data (e.g. names of objects, Users, Indexes, columns, etc) always has a Collation of Latin1_General_100_CI_AS_KS_WS_SC (i.e. CATALOG_DEFAULT) regardless of the Database's default Collation.


You could expand your activities table so that instead of a single timestamp column, it includes two timestamps, ts_from and ts_to, that indicate the time period for which the row's data is applicable. As long as no data changes, you do not insert any new rows into your table. When data does change, you insert the new data into a new row and update the old row's ts_to column.

Then you can create a calendar table that contains all timestamps of interest to you (in your case that table would contain a row for every day and every hour in the period of time that is of interest to you).

Finally, you get the result by cross-joining the two tables and keeping only those records whose date (from calendar table) falls between ts_from and ts_to.

For example (my example uses only dates and not datetimes, but you can change that easily):

create table calendar (
    d date
);

create table time_data (
    d_from date,
    d_to date,
    val1 int(10),
    val2 int(10)
);

insert into time_data values('2015-01-01', '2015-01-15', 10, 15);
insert into time_data values('2015-01-15', '2015-02-21', 7, 18);
insert into time_data values('2015-02-21', '2015-03-04', 1, 55);
insert into time_data values('2015-03-04', '2015-04-21', 3, 22);
insert into time_data values('2015-04-21', null, 9, 99);

I filled the calendar table with all dates from 2015-01-01 to now and did the simple query

select d, val1, val2
from calendar, time_data
where d >= d_from and d < ifnull(d_to, curdate())
order by d

The resultset contains an entry for every date in the range (2015-01-01, 2015-05-01) and val1, val2 from last entry from time_data before that date.

For a nice procedure to fill the calendar table, see this article.

However, since a cross join is an expensive operation, and you have large amounts of data, thoroughly test this (or any other) solution before applying it to your production system.


need sql to represent relation negation and date compare problem

The problem is not simple as it involves 2 entities/tables (Employee and Project) that have 2 relationships between them. One is through the Department (a project belongs to a department, an employee belongs to a department) and the other through Onproject (which shows that an employee has started working on a project).

Moreover, the answer needs a relational division query ("all is workers belong ..."). There are many ways to do relational division - and all are in the range of not simple to very complicated.

One way to express the problem is with a (double) negative. Instead of finding projects where all workers do not do something, find projects where none of the workers do something. This can be expressed with a NOT EXISTS subquery:

SELECT p.*
FROM Project AS p
WHERE p.ddate > CURRENT_DATE
  AND NOT EXISTS
      ( SELECT *
        FROM Employee AS e
            JOIN Onproject AS w
            ON w.eid = e.eid
        WHERE w.pid = p.pid
          AND e.did = p.did
      ) ;

Or with EXCEPT:

SELECT p.*
FROM Project AS p
    JOIN
      ( SELECT p.pid, p.did
        FROM Project AS p
        EXCEPT
        SELECT w.pid, e.did
        FROM Employee AS e
            JOIN Onproject AS w
            ON w.eid = e.eid
      ) AS pw
      ON pw.pid = p.pid
WHERE p.ddate > CURRENT_DATE ;

Given the tables definitions, the latter can be (simplified?) to use NATURAL JOIN:

SELECT *
FROM Project
    NATURAL JOIN
      ( SELECT pid, did FROM Project
        EXCEPT
        SELECT pid, did FROM Employee NATURAL JOIN Onproject
      ) AS pw
WHERE ddate > CURRENT_DATE ;


The questions:

Your indexes are appropriate for these queries and your conclusions are correct.
About partitions, they are rarely useful for performance. I don't think there is any point in using them in your case.

Minor remarks:

The DESC doesn't mean anything in that place. You can (try to) define a composite index with 4 ways:

(FromUser ASC, DateCreated ASC)
(FromUser ASC, DateCreated DESC)
(FromUser DESC, DateCreated ASC)
(FromUser DESC, DateCreated DESC)

but MySQL does not supoort DESC indexes and will silently ignore these ASC and DESC. The index created will be the (ASC, ASC) one.

Nothing to worry though as the indexes can and will be used for the queries you have. Indexes can be read fowards or backwards. The index would not be very efficient if you had (for example) a query with:

ORDER BY FromUser ASC, DateCreated DESC

where an (ASC, DESC) index would be better.


How to only update if nothing has changed? I need to validate several things before I insert

Instead of issuing those checks as separate SELECT statements, make them part of the INSERT statement. You will need to replace the INSERT...VALUES syntax with the INSERT...SELECT one to be able to use a WHERE clause:

INSERT INTO
  waypoint_checkins (runner_id, waypoint_id, time)
SELECT
  runners.id,
  %waypoint_id,
  %time
FROM
  runners
WHERE
  name = %runner_name
  AND is_finished = 'false'
  AND EXISTS (
    SELECT
      *
    FROM
      waypoints
    WHERE id = %waypoint_id
      AND currently_open = 'true'
  )
;

You can see that the first check is directly integrated into the INSERT statement, because the argument applied to runners is a name and you need to get an ID from it as one of the values to be inserted. The second check is in the form of an EXISTS predicate.

All three initial arguments (runner_name, waypoint_id, and time) are thus parameters of this single statement.


Security for Application Developers doing PL/SQL work in Oracle

Back in the days when I worked in an Oracle shop, we had a specific 'dev' (development) server, which had different security restrictions than the 'prod' (production) server.  Developers could do whatever they needed, and then we'd hand off the necessary scripts to the DBA to apply to the production server.

In the case of our critical systems (SCT Banner, for tracking classes & students, and Oracle Financials), there were also 'test' and 'seed' servers.  Test was for user acceptance testing before stuff migrated from dev to prod;  'seed' was stock install of the software, so should we find a bug, we could verify if it was something we had introduced or came from SCT or Oracle's software.


Error while configuring REST for Oracle APEX on Windows

Everything works if you:

create a new database (or use existing)
do alter session set container = yourDBname
and only then run @apexins.sql and other installation and config scripts (e.g., @apxchpwd.sql, @apex_rest_config.sql).

This will lead to creation of a new APEX instance inside current DB.


Unable to run job - Subquery returned more than 1 value

You must have more than one availability group in the instance as mentioned in the comment. I suggest you use this function. Details here.

If sys.fn_hadr_is_primary_replica ( @dbname ) <> 1
BEGIN
-- If this is not the primary replica, exit (probably without error).
END
-- If this is the primary replica, continue to do the backup.

You can run this query to check how many Availability group you have.

SELECT count(*) AS [NumOfAG]
FROM sys.availability_groups


Install SP 3 on SQL Server 2008, where merge replication is running

You should be able to apply the SP3 without dropping any merge replication publications.  The order of upgrade should be to upgrade (1) distributor, then (2) publisher, then (3) subscribers.

The SP3 is for SQL Server 2008, not the SQL CE 3.5 mobile tools.  Since you are running merge replication then I assume that you have SQL CE 3.5 SP2.  So all should be well.

However:  Do you have a test system and test mobile devices?  If so, test your upgrade in the test system first. I always like to deal with suprises in a test system before applying changes to Production.


Do the showplan (any or all) events add more overhead than a tuning trace should have

There is no way to tell you any exact order of magnitude - this will depend greatly on how many plans you are collecting, how big they are, etc.

That said, yes, capturing actual showplan information will absolutely have an impact on your tracing. This article demonstrates that and this article shows that it is still a problem with Extended Events as well.


Would using SELECT FOR UPDATE and a subsequent INSERT guarantee uniqueness in text column?

Yes, that should work.  However, consider either of these one-liners:

INSERT IGNORE ...

INSERT ... ON DUPLICATE KEY UPDATE ...

If you are using AUTO_INCREMENT, be cautious about 'burning' ids.

Links:

https://stackoverflow.com/questions/24616315/how-to-use-insert-on-duplicate/24617186
https://stackoverflow.com/documentation/mysql/866/insert


MySQL: 'user'@'hostname' vs 'user'@'IPaddress'

I had this problem before, and after long investigation I found out sometimes MySQL can not resolve any issue related to DNS by default, so I did the following: edited hosts file ( can be found under Linux /etc/hosts, for Windows C:\Windows\System32\drivers\etc\hosts) and added ip and hostname for the client machine,in your example its

10.11.12.13 foobarhost

then save and close ( note Windows will require reboot before taking any effect), you may need to add the following parameters --skip-name-resolve and\or --skip-host-cache .


Repopulating PostgreSQL database with data directory from another database Instance

Assumptions

I'm assuming you have two instances each one with a different data directory but with the same schema for the same version of postgres installed.
I'm also assuming when the server starts is using your newly created, possibly empty, instance

What to do

You should try to start a new instance of the server pointing to your backup data directory...
Somenthing like

pg_ctl -D /path/to/backup/dir -l /path/to/log start -o "-p 5433"

If this works, your backup instance is running on port 5433.
Check if everything is in place and the data is consistent.

If so then you could simply make arrangements for your server to always start pointing to your backup directory and safely delete your newly created data directory.


How to change all connections to SQL Server without investigating each hard-coded connection to serverName?

For our system we set up a DNS Alias and have all the connection strings point at that  i.e. sqlSERVERAlias\Database where SQLSERVERAlias points at 08Serverx or 08Servery

Then all you've got to do is change the Alias when you change the servers


Filter customers with negative balance across multiple accounts

This is a very simple query. To get the total balance of each customer (with non-negative total):

SELECT customer_no, SUM(balance) AS total_balance
FROM accounts
GROUP BY customer_no
HAVING SUM(balance) >= 0 ;

To get the total sum of all the above customer balances:

SELECT SUM(total_balance) AS super_total_balance
FROM
  ( SELECT SUM(balance) AS total_balance
    FROM accounts
    GROUP BY customer_no
    HAVING SUM(balance) >= 0
  ) AS t ;

or with a rather cryptic variation:

SELECT TOP (1)
       SUM(SUM(balance)) OVER () AS super_total_balance
FROM accounts
GROUP BY customer_no
HAVING SUM(balance) >= 0 ;


SQL Server COUNT() seems to be ignoring WHERE

You need to add the same where clause in your union select.

SELECT     SiteTour, COUNT(SiteTour) AS count
FROM       tblConference
WHERE      (RegistrationType NOT LIKE 'cancellation')
GROUP BY   SiteTour
UNION ALL
SELECT     'TOTAL' AS SiteTour, COUNT(tblConference_1.SiteTour) AS Expr1
FROM       tblConference AS tblConference_1
WHERE      (tblConference_1.RegistrationType NOT LIKE 'cancellation')


Solution 1:
Script all of your foreign keys out and create the tables on the new server without foreign keys. Load the data, then rerun the scripts to create the foreign keys.
Solution 2:
Backup the database, the restore it to the new server.
Solution 3:
Run a query similar to this and figure out the dependencies for yourself.
Pick the one that sounds like the least work :)


Trying to solve "outside tablespace bounds" MySQL error, but recovering table with mysqldump fails

Let's elaborate the error message

  InnoDB: Error: trying to access page number 197105 in space 89,

InnoDB calles an .ibd file a "table space" or just "space" in this error message. So, there is a space 89 and one of its pages refers to a non-existing page number 197105. The error message doesn't say what table corresponds to the space 89. I think some MySQL version tell that, but it's possible to find from the InnoDB dictionary.

mysql> select NAME from information_schema.INNODB_SYS_TABLESPACES WHERE SPACE=4304;
+----------------------+
| NAME                 |
+----------------------+
| sakila/film_category |
+----------------------+
1 row in set (0.01 sec)

Now, as we know what table is corrupt the question is how to fix it. InnoDB doesn't heal tablespaces (like MyISAM does with REPAIR TABLE), the only way is to drop the table space(=table) and re-create it.

In this particular case it's not necessary to start MySQL in innodb_force_recovery mode. Just start it normally and drop the table. But first, you probably need to save the data in this table.

There are two ways.
First, you can choose ranges of non-corrupt primary key values and dump them into another table or a text dump. I had recovery cases like this one and wrote a script to do that. The script iterates over the PK ranges and dumps as many records as possible into another (MyISAM) table.

Second way is requires more time and skills. You will need a tool (https://github.com/twindb/undrop-for-innodb) and a blog post https://twindb.com/recover-corrupt-mysql-database/ with steps.

Then drop the corrupted table, create an empty one and load the recovered dump.


Since you want to be application developer who understands a DBMS.

In my opinion there exists, five foremost databases in minds of developers. These are

Oracle
DB2
SQL Server
My SQL
PostgreSQL

Why these five, first three because of market share. Last two because of usage share in open source applications.

  Also you stated that you are working with JavaEE.

Then I suggest you choose one of the following. In the following order.

PostgreSQL Open Source
DB2 (Universal) Express Edition
Oracle Express Edition
MySQL
SQL Server Express Edition

I put SQL Server last since you are working with Java. Other databases are more appropriate. Note that I am Certified in SQL Server (2000,2005,2008). Therefore this is not a bias in my opinion but I acknowledge that others are more strong with Java.

I put MySQL in last rows since I believe you should start with a DBMS which supports SQL Standard more, and first three are better (out of box) than MySQL in this regard.

There are not much difference for learning application development with first three. If you are to be primarily open source developer choose PostgreSQL. If you are to be primarily commercial developer, look your local career site. Which DBMS wanted more, choose it. For example Oracle is more popular in my local area.


how to config SSRS 2005 email setting with google apps

This msdn walkthrough should have everything you need to accomplish what you want to do on the SSRS 2005 side.  Pay attention to the section on setting up for remote smtp.  Especially the part about setting SendUsing to 2.
This google guide provides the steps to configure things on their end.

The setup for SSRS 2008 isn't much different.


Moving database objects to another database without wizard

You can use SMO to script a database schema from Powershell, C# or VB. A great article on the subject can be found here.

Regarding data, I think that the simplest option is to pipe each table's data to a file using BCP, then import it back to the destination database, again using BCP. I strongly suggest the native format (-n option), as it simplifies the process enormously.


Purchased in Previous 12 Months from Date of Purchase - SQL

You can accomplish this with a self join:

SELECT a.customer_id, a.product_id, a.purchase_date, MAX(b.purchase_date) IS NOT NULL AS previous_12_months
FROM products a
LEFT JOIN products b
  ON a.product_id = b.product_id AND
     a.customer_id = b.customer_id AND
     b.purchase_date < a.purchase_date AND
     b.purchase_date >= a.purchase_date - INTERVAL 12 MONTH
GROUP BY a.customer_id


An answer:

After looking into this further, I determined that the call:

UPDATE mytable SET Flag = 0 where Id = 10

was being called also in a stored procedure which took a parameter of type BIT and did something like so:

UPDATE mytable SET Flag = theFlagValue where Id = theId;

While this worked fine on the master - the mytable would be updated with the correct flag value for any rows matching the Id - it wouldn't work on the slave.

My thought on this is that it involves how the data is serialized when sent to the slave.  That type mismatch was fine on the master but not on the slave.

I was able to modify that stored procedure to take a TINYINT instead and it works flawlessly now.


Fastest Way to Move Data from Current Database for Multiple Tables to New Database?

You say that you're worried that your proposed solution won't work because it won't run in parallel. You already have a PowerShell cmdlet which deletes from all 160 tables, right? Deleting rows can often take longer than inserting them, especially if you have indexes on the table that you're deleting from. If that cmdlet doesn't run in parallel then why do you need the insert commands to run in parallel? I recommend starting with a relatively simple solution, doing performance testing, and only making it more complex if necessary.

I'm not familiar with the restrictions of what can be run in PowerShell. However, it appears to be possible to execute a stored procedure from PowerShell , so I will provide a T-SQL solution. You should be able to easily translate that into what you need, but I may refer to the code as a "stored procedure".

If your database is about 200 GB and you have about 1000 entries then on average you only need to move 200 MB of data. That's definitely doable if you have suitable indexes on the tables (such as one on the Entity column). I'm going to assume that you already did that analysis and you have the indexes that you need to speed up the SELECT statements.

1) Create the database for the inserts

I recommend that you create your temporary database with a recovery model of simple. That will reduce data written to the transaction log for minimally logged queries. I also see no advantage in using a different recovery model because the data will be so short-lived. I also recommend that you create a database with the right settings and just keep it around. Grow the data and log files to appropriate sizes to prevent auto-growth events. You can just drop all of the tables in the database as they are no longer needed. Do you really need to go through the work each time of creating and dropping the database?

2) Figure out your DML strategy

I can think of three different ways to insert data into the temporary database. Two of them require changes to your delete applet.

All of them insert into heaps and take advantage of minimal logging. Since you just need the data and nothing else that will minimize the overhead of doing the inserts. On SQL Server 2012 you cannot do parallel inserts. You can do them if you upgrade to SQL Server 2014 and use SELECT INTO.

The first method uses the OUTPUT clause to delete the rows from the table and insert the deleted rows in a single query. This will likely be the fastest way because you only need to read the underlying data once per table. You can't use SELECT INTO for the insert statement (but you can run one beforehand to create the target table). Your query could look something like this:

INSERT INTO [other_db]..[Entity_Table_1] WITH (TABLOCK)
SELECT t.*
FROM
(
    DELETE FROM [source_db]..[Entity_Table_1]
    OUTPUT deleted.*
) t;

The second method is similar to the first but you process the insert query for a table right before the delete query. The insert query will in theory move the data that you need from the table into the buffer cache. That will reduce the number of trips to disk that your code needs so it will run faster. This will require changing the code of your delete applet.

The third method is just doing inserts. For this method you won't need to modify the delete applet. If you don't use SELECT INTO you'll want to use the TABLOCK hint to get minimal logging.

3) Figure out the method and order of looping

I don't recommend using sp_MSforeachtable. It's undocumented and could be changed or removed by Microsoft at any time. Instead, you could use a cursor to loop over relevant tables from sys.tables. There should be plenty of examples on how to do that online, but let me know if you need further help.

The order that you process the tables may matter. For example, suppose that you need to have separate applications for the delete statements and the insert statements. If you process the tables in the same order in both procedures then it's possible that the data for the current table will be pushed out of the buffer cache before you process it again. That's because you're querying 159 other tables before you query the same table again. If you did something process the tables in the opposite order that you delete from them, you may reduce physical reads when reading the data.

4) Implement parallelism if necessary.

After testing you may find that your code is too slow and that you need to run multiple concurrent insert statements. For one way to do this, first make the stored procedure always loop over the tables in a deterministic order. Add two input parameters to the procedure. The first should be the total number of threads and the second should be the thread for that procedure call. If you called the procedure with 4 total threads and the active thread as 1 then that procedure would process the 1st, 5th, 9th, 13, ... tables. For N threads you could open N PowerShell windows and change the second parameter for each.

Happy inserting!


Sorting by month by chronological occurrence when the year doesn't matter?

Test data

create table dates ( d date);
insert dates values ('2012-01-01')
,('2008-01-01')
,('2008-02-01')
,('1987-01-02')
,('1994-12-28')
,('1987-05-02')
,('1994-11-28')

Query

select d, DATE_FORMAT(d, "%m-%d")
from dates
join (select 11 ms, 2 me) params
where ms<=me and MONTH(d) between ms and me
  or ms>me and (MONTH(d) >=ms or MONTH(d) <= me)
order by case when MONTH(d) < ms then 100+MONTH(d) else MONTH(d) end


SQL Server Version Store Performance differences between RCS and ASI

Just turning on the database setting will affect for how long the versioned rows need to stay in tempdb (since transactions that could be running in true snapshot would have repeatable read). I.e., enabling ASI will keep row versions for a longer time than enabling (only) RCSI.

As for performance for the applications we can't say. Only enabling ASI doesn't change client application behaviour unless they also explicitly ask to run with snapshot isolation. I.e., doesn't change default behaviour for apps.

Assuming that all your apps consistently are modified to use the snapshot isolation (pretty unlikely) then the performance will be pretty similar between the two. Readers aren't blocked by writers. You can get into edge cases with update conflicts and how they are handled with error handling, re-try etc. But there are so many dependencies here, so that cannot be generalized - and those are edge-cases anyhow so it is highly unlikely that they will have a measurable impact.


Failed to restored indexes using mongorestore - MongoDB

For the people who will come here in the future the issue was, I was using MongoDB 4.0 on the local and server was on 4.2:

Starting in version 4.2, mongodump uses Extended JSON v2.0 (Canonical) format for the metadata files. To parse these files for restore, use mongorestore version 4.2+ that supports Extended JSON v2.0 (Canonical or Relaxed mode) format.
For details please see "Metadata Format" section on this : docs.mongodb.com/manual/reference/program/mongodump


String comparison with integer is not working sometimes in MySQL 5.5

This isn't a full answer but you might try looking over the 5.5 change list starting at http://dev.mysql.com/doc/refman/5.5/en/news-5-5-0.html

Note the section nav on the right that goes subversion by subversion.  Skim through each looking at "incompatible changes".  If you find something describing what you are seeing it will at least give you better context of what is happening and why.


What is DBCC CHECKIDENT current column value

Not sure what you mean by "recovered" - current column value tells you the maximum value in the table. It's meant to show you when the maximum value is greater than the value SQL Server assigned last (and from which it determines what it thinks it's going to assign next, even if in practice this isn't always true). Example:

SET NOCOUNT ON;
CREATE TABLE dbo.smudge(id INT IDENTITY);
GO
-- insert 11 rows
INSERT dbo.smudge DEFAULT VALUES;
GO 11
DBCC CHECKIDENT('dbo.smudge'); -- 11, 11
GO
-- delete the largest id
DELETE dbo.smudge WHERE id = 11;
GO
DBCC CHECKIDENT('dbo.smudge'); -- 11, 10
GO
-- reseed the table
DBCC CHECKIDENT('dbo.smudge', RESEED, 1);
GO
DBCC CHECKIDENT('dbo.smudge'); -- 1, 10
GO
-- insert another row
INSERT dbo.smudge DEFAULT VALUES;
GO
DBCC CHECKIDENT('dbo.smudge'); -- 11, 11
GO
-- insert a much higher row
SET IDENTITY_INSERT dbo.smudge ON;
INSERT dbo.smudge(id) VALUES(255);
SET IDENTITY_INSERT dbo.smudge OFF;
GO
DBCC CHECKIDENT('dbo.smudge'); -- 255, 255
GO
DROP TABLE dbo.smudge;
GO


sql server stored procedure - collection of data for fields as input

One of the drive is the source, because that could define the best way.
Now, i will assume there is some internal logic to the procedure.
Possibilities:

send it as json/xml and parse it using MSSQL functions
send it as a table valuable
place it into a temporar table and process it in procedure


is this time to remove the databases from the availability group? which ones?

I have decided to remove the databases from the availability group, on this occasion.
for this I have used the following scripts:

-- https://blog.pythian.com/list-of-sql-server-databases-in-an-availability-group/
-- How do we know that this instance is part of Availability Group(s):
SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC

-- https://blog.pythian.com/list-of-sql-server-databases-in-an-availability-group/
-- How do we get some information about the databases in the Availability Group:
SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole],
dbcs.database_name AS [DatabaseName],
ISNULL(dbrs.synchronization_state, 0) AS [SynchronizationState],
ISNULL(dbrs.is_suspended, 0) AS [IsSuspended],
ISNULL(dbcs.is_database_joined, 0) AS [IsJoined],
remove_database='USE [master]' + char(13) + 'ALTER AVAILABILITY GROUP ' + quotename(AG.name ) + char(13) + 'REMOVE DATABASE ' + quotename(dbcs.database_name) + char(13)
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
ORDER BY AG.name ASC, dbcs.database_name


What is the purpose of attaching a database?

Attaching/Detaching could be quicker than backup/restore depending on the situation.

A detach is instantaneous and you can start copying the files right away, and the attach is near instantaneous too. Backup takes a while, and restore takes a while too.

This technique was a lot quicker before we had backup compression because the size of the files that needed to be copied over were more or less the same size depending on the situation.

Backup compression has an influence on that, since the backups are now a lot smaller than the data files.

It all depends on your IO speed and the source and destination of the copy.

The math is fairly easy, If the time of taking a backup, copying over the files, and restoring the backup is shorter than copying over the mdf+ldf files it's faster to backup/restore, otherwise it's faster to detach/attach.

The detach/attach method obviously only works if your database can go offline.

If your IO subsystem is fast, and the network link over which you have to transfer the database is slow it might be faster to transfer a compressed backup (if that's available in your version/edition). If your IO subsystem is slow and so is the process of backing up and restoring but you can copy the files over quickly it might make sense to detach, copy over the relatively larger .mdf files and reattach.

In my opinion, this used to be more of an issue with large databases before we had backup compression available. If I recall correctly this was a possible question in Microsoft Certification exams before we had compression but I haven't seen it in the material for more recent versions.

The end result is basically the same, an exact copy of the database.


Get the windows account which runs the query?

It depends, and I'm afraid Martin give you only part of the answer. The credential used to access the file is different whether you logged in with SQL login or a Windows login.

For SQL logins (ie. user and password based connection string) the credential used is either the SQL server process account of the login proxy credential account, if one is set up:

  The information stored in a credential enables a user who has
  connected to SQL Server by way of SQL Server Authentication to access
  resources outside the server instance. When the external resource is
  Windows, the user is authenticated as the Windows user specified in
  the credential.

For Windows logins (ie. Integrated Security or SSPI connection string) the engine will always impersonate the caller before accessing outside resources. Which implies that the access to the shared folder will occur under the impersonated context of the original Windows login. Which in turns implies 'double-hop' delegation will occur and the authentication with the shared file will be subject to the domain policies on constraining delegation. And all domains that give 1 cent on security will have constrained delegation restriction in place and will require you to set up the SQL Server service account to be enabled for constrained delegation.


Capture when any database is restored on the server

Under the assumption that you are using SQL Server, you can create a trigger against the msdb..restorehistory table.

Go ahead and query that table to see what information is in there; I think you can achieve what you are doing.

See Know when a database has been restored


Why would execution plan decide to sort 500k rows instead of 4k?

The general answer here is: Because it does not know.

The Query Optimizer makes assumptions based on expense based on statistics. Missing indices, outdated statistics and some other things (too complex query -> running out of time to find the optimal plan, more rare) can lead to a bad execution plan.

Missing indices and outdated statistics are the most likely culprits.

For a detailed analysis you need to look at the plan - not a picture of the plan. Of an actual execution (so you can compare initial estimated rows with actual rows, which gives an idea how far off the query optimizer's assumptions were).

If you have a top clause, then sql server really gets a little into murky water. It knows the distribution of foreign keys in the whole data set via statistics (histogram), but how far are those represented in your top results? This can leave sql server on a bad path because statistical mathematics may break down. Which results in a non optimal query path.

Query optimizers are also under constant development ;) Not saying bug - more like "edge cases better handled in newer versions".


Why should I use Visual Studio 2010 over SSMS for my database development?

Actually I was a bit underwhelmed with VS2010, to be honest.  I think an old-school create table script and files for stored procedures are easier to work with.  If you need schema management then you can get Redgate SQL Compare Pro for a few hundred dollars.

If you really need a database modelling tool then Powerdesigner or even Erwin does a much better job, although they're not particularly cheap.

Although I prefer SSMS I've used both.  Some pros and cons:

SSMS has a user interface that 'just works' well for SQL development.  Just building and using creation scripts is much more convenient than the hoops VS2010 forces you to jump through.  Much, much more flexible (+ SSMS).
VS2010 has basic schema management (i.e. difference/patch script generation) (+ VS2010).  However it's not all that good and has some flaws.  For example it matches constraints on name.   If you have check or default constraints on a column without naming them, SQL Server generates a random name behind the scenes.  This will confuse VS2010 if you install the script on a different machine as the constraints may have different names.  Redgate is cheaper and better. (+ VS2010, but flawed).
VS2010 is really clumsy - you need to have one file for each table or other DB object.  Essentially you have to do things the VS2010 way, which is quite cumbersome.   (- VS2010)
VS2010 Is also somewhat fragile and source control integration is flaky.  Even on a simple database every table, constraint, stored procedure, index and other database object is its own file.  Files get added to the project all the time, much faster than a typical programming project with (say) C#.  With optimistic concurrency it has a tendency to silently drop files from the project as check-ins get out of sync.  Even with good team discipline the feedback from the UI about status is very poor.  This would be a disaster on a complex model.  (-VS2010 - I'd almost consider that a show-stopping flaw for a large project).
SSMS comes with SQL Server - can't beat the price (+ SSMS).
VS2010 still does not have a proper repository like (say) PowerDesigner or Oracle Designer.  You can't easily query the data model without installing it into a database. (- VS2010).

On the whole, I would rate VS2010 about a B-.  It was clumsy on a relatively simple database project with two fact tables and around 15 dimensions.

The largest data model I ever did was for a court case management system, which had around 560 tables.  I would not recommend VS2010 for a project that size (I did that on Oracle Designer).  Essentially it's trying to be clever and the paradigm doesn't really work all that well.  You're better off with a best-of-breed modelling tool like PowerDesigner or just using create table scripts by hand.

SSMS is simple and reliable but manual.  You have pretty much infinite control over how you want to manage the model.  Combine it with a schema manager such as Redgate SQL Compare and maybe a decent modelling tool such as PowerDesigner and you have a much better package than VS2010.

Summary
I'm not sure I could quote any killer features or benefits apart from (perhaps) integration with VS solutions containing other projects.  If you already have VS2010 premium or ultimate then you get a somewhat flawed database development tool thrown in with your .Net tool chain.  It will integrate DB projects into your VS solution, so you can use it to make sproc deployment scripts at least.

However, VS has no modelling tool to speak of, so PowerDesigner or even Erwin is better on that count.  Redgate's schema management is much better and SQL Compare Pro is quite cheap (about £400 IIRC).  IMHO SSMS works much better for T-SQL development but you can certainly do it with VS2010.

VS2010 premium isn't much cheaper than a best-of-breed database modelling tool and VS2010 ultimate is at least as expensive.  At the expense of tight integration with your VS project you could probably do better with third party tools.

An alternative

I guess one shouldn't slag off VS2010 too much without suggesting at least one alternative and outlining its pros and cons.  For the purposes of this I'll assume a large project.  Although I mainly do A/P work these days I have been involved in a 100+ staff year project where I did the data model (and some development work) and a couple of others in the 10 staff-year range, where I mainly worked as an analyst or a developer.  Mostly I work on data warehouse systems these days but the larger projects were mainly applications.  Based on my experiences with various tools, here are some suggestions for an alternative tool chain:

VS2010 professional or higher.  You may or may not want to use project management features of premium or ultimate.
Subversion, AnkhSVN and TortoiseSVN - Better than TFS any day and plays nicely with VS.  It's also quite amenable to farming off local repositories for concurrent development workstreams.
SSMS for T-SQL development - Project management and SC integration not so good but works well for DB development work.
VS2010 DB project for tracking sproc files - slightly clumsy if you're using SSMS but works OK.  It will also generate deployment scripts.
PowerDesigner - Way better at modelling a database and managing DB schema items.  It also does UML if you want to get heavily into MDA.  If you want to drive your DB design from an object model you might consider Sparx EA instead.  It does the best job of Meta CASE (extensible meta model) of any CASE tool I've seen, although its database modelling leaves something to be desired.
SQL Compare pro - Use this to generate DB patch scripts or to test manual patch scripts (see 1 below).
Framemaker - Much more stable and better groupware features than Word if you have multiple analysts working on a spec.  It also supports conditional inclusion, so you can have versioned releases of a spec with WIP changes hidden off.  MIF and MML make it fairly easy to integrate API docs and data dictionaries into the spec documents.  It's quite useful to do this as you can then cross-reference them in the spec.  Textual label anchors make cross-references stable across re-imports.  You can also use TCS to single-source the document to PDF, HTML and CHM output.
Open-source issue tracker - Many good open-source ones (e.g. TRAC, Bugzilla to name a couple I've used).  Open-source ones are easier to modify or integrate into a custom workflow and you can't beat the price.
NUnit or other testing tools - whatever automated testing tools are most appropriate to your requirements.
Anything but MS project - Considered harmful.  MS project is very inward looking and forces project plans into a model that does not effectively represent uncertainty, risk or dependencies on stakeholders or other third parties (see 2 below).

Pros: Better database modelling and schema management than VS2010, better version control system, easier to customise the build and project work flow, better management of specs and documentation.

Cons: More effort to integrate tools, limited DB integration into build process.

Assumptions: Assumes that control of change/release process in more important than automated or tightly integrated release management for DB schemas.  Also assumes that automated DB schema management is not 100% reliable.

Not terribly high tech or slickly integrated, but for a complex project you're probably more interested in control than cute automated features.  I'd argue that you're better off with a set of best of breed tools and whatever home brew build and test scripting is necessary to integrate them.  Just try to keep the build (a) relatively simple to understand and (b) fully automated.

QA on DB patches.  On a large schema you may want to have a manual patching process for live systems, particularly if the patches involve data migration.  For example, it may be desirable to have roll-forward and roll-back scripts to support backing out a change if necessary.  In this case you will want to have a facility to test that the patch actually works properly.  If you manage the database schema in a repository you can test the scripts by setting up before databases and generating a reference database from the repository.  Running the patch script on the before database should synchronise it with the repositiry model.  A schema compare tool can be used to test this.
I've done a lot more data warehouse and integration work than bespoke application development lately, so I encounter this more often than a development team will in most cases.  However, project management tools and methodologies do a very poor job of managing external stakeholders.  In an integration project such as a data warehouse I really want to see a project management tool that really pushes external dependencies (i.e. ones I don't control) in the face of programme management.  On any non-trivial integration project the external dependencies are by far the biggest drivers of wasted time.


How to delete duplicate rows in merge replication

The duplicate rows in the MSmerge_tombstone occurs when you restore the database with KEEP_REPLICATION bit.

Its a good practice to configure replication from scratch since you never know that you run into some unknown issue just like you did.

As confirmed by OP - drop the index uc1MSmerge_tombstone, delete the offending row and   recreating the index fixes the problem.

Remember that you have to be caution when fiddling with MSmerge* tables.


Oracle Import problem caused by different character sets

If that is the actual DDL you are using to create the table, you could use the NLS_LENGTH_SEMANTICS parameter.  If you set that to CHAR rather than the default of BYTE, a VARCHAR2(5) will be allocated enough space to store 5 characters in the database character set (potentially up to 20 bytes) rather than 5 bytes (which could allow just 1 character).

Unfortunately, changing the NLS_LENGTH_SEMANTICS probably won't be terribly helpful if you're relying on the import process to create the table-- the dump file will inherently add the CHAR or BYTE keyword so it would actually issue the statement

CREATE TABLE BDATA.Artikel(
    Key                   VARCHAR2(3 BYTE)  NOT NULL,
    Name                  VARCHAR2(60 BYTE) NOT NULL,
    Abkuerzung            VARCHAR2(5 BYTE)  NOT NULL
);


Both columns specified in the SET clause of your UPDATE are going to be touched (written to) for all the rows matching the some_more_conditions, no matter what x_specific_condition or y_specific_condition may evaluate to. There is no syntax to work around that while sticking to a single statement.

So, either live with that or go for two statements:

UPDATE some_table
SET column_x = new_value_for_x
WHERE some_more_conditions
  AND x_specific_condition;

UPDATE some_table
SET column_y = new_value_for_y
WHERE some_more_conditions
  AND y_specific_condition;


Predicates: in the WHERE clause or the JOIN clause?

I guess this is one of the "It depends"-moments.
In a regular execution there is no or at least nearly no difference.

Thought it will become more interesting once the queries are more complex.
More complex scenarios are e.g.:

Nested Joins
FORCE ORDER  (Query Hint)

I would have added LEFT and CROSS Joins as well, thought they are already part of your example.

About the "experience in SQL Server normalization" - I'd say, besides looking in the code, there will not be much you can hold your self on. It is often just a "It depends!"


MYSQL MyISAM data recovery implementation

I have BAD NEWS / GOOD NEWS for you

BAD NEWS

MyISAM is simply not suitable for you because...

MyISAM is not a transactional storage engine
Writes to a MyISAM cannot be rolled back
MyISAM tables that are open crash very easily if the mysqld process or DB server crashes

GOOD NEWS

Using InnoDB is absolutely in your best interests. You will have to do some work for this to become a reality.

STEP 01 : Create a Script that will Convert all tables to InnoDB

Conversion of all tables to InnoDB is incredibly straightforward.

MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM'"
SQL="${SQL} AND table_schema NOT IN ('information_schema','mysql','performance_schema')"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -ANe"${SQL}" > /root/ConvertMyISAMToInnoDB.sql

STEP 02 : Configure InnoDB Settings for Multithreading and Caching

If the DB Server has 8GB RAM, use the following settings in /etc/my.cnf

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table
innodb_buffer_pool_size=4G
innodb_log_file_size=1G
innodb_log_buffer_size=32M
innodb_open_files=16384
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=5000
key_buffer_size=8M

STEP 03 : Create new InnoDB Logs

cd /var/lib/mysql
service mysql stop
rm -f ibdata1 ib_logfile0 ib_logfile1
service mysql start

STEP 04 : Perform InnoDB Conversion

mysql ${MYSQL_CONN} < /root/ConvertMyISAMToInnoDB.sql

STEP 05 : Make changes in your code for transactional support

You will have to employ the use of three commands : 1) START TRANSACTION; 2) COMMIT; 3) ROLLBACK;

Before you starting writing new data, precede it once with

START TRANSACTION;

START TRANSACTION; will create an initial checkpoint in the event you want to

commit all changes to the database (via COMMIT)
abandon all subsequent changes (via ROLLBACK)

Thus, in your PHP code, you must strategically place COMMIT; commands in places your know you want to fully submit all INSERTs, UPDATEs, and DELETEs of all InnoDB tables. In like fashion, you must also place ROLLBACK; commands in places where you wish to abandoned compiled changes to InnoDB tables.

CAVEAT

If you still want to the data as MyISAM, I have an additional suggestion.

Get another DB Server. Load all data into the second server as follows:

STEP 01 : service mysql stop

STEP 02 : Add this to /etc/my.cnf

[mysqld]
skip-innodb

STEP 03 : service mysql start

STEP 04 : Load the data into the second server

mysql -hIPofSecondServer ${MYSQL_CONN} < /root/ConvertMyISAMToInnoDB.sql

STEP 05 : Setup replication from InnoDB Server to MyISAM server

That way, you can change back to MyISAM by just failing over to the server that's MyISAM


DB2 10.1 Client throws SQL0552N running a script

You will need to grant Client101 CONNECT and CREATETAB on database in order to be able to create a table. It may also help to grant IMPLICIT_SCHEMA as well. And depending on the version of your client, you may either need to connect with an admin ID first, or grant BINDADD to your ID so it can bind version specific system packages.

db2 connect to mydb as admin101 using "password"
db2 grant connect,createtab,implicit_schema,bindadd on database to user client101
db2 connect reset

then

db2 connect to mydb as client101 using "other_password"
db2 create table .....


SSAS 2016 tabular : Discrepancy on countrows between measures visual studio vs Excel

The solution was to change the processing in Visual Studio (VS) from Default to Full.

The issue is described in detail here: https://connect.microsoft.com/SQLServer/feedback/details/685325/processing-ssas-in-tabular-mode-not-refreshing-data (see Cathy Dumas's reply under "Posted by Microsoft on 8/25/2011 at 2:02 PM")


Question 1, how to automatically manage this in case I restore a new
  db on that server and I want to assign the user USER_R?

As described here you could add the user to the model database to automatically be
added to other, created databases.

However, this will not work for restored databases.

USE [model]
GO
CREATE USER [testlogin] FOR LOGIN [testlogin]
GO
USE [model]
GO
ALTER ROLE [db_datareader] ADD MEMBER [testlogin]
GO

Restored databases are hard to track, more information on why life is hard in this article by Erik Darling.

Unfortunately, triggers won't directly work on restored databases.

If you where on SQL Server 2016, you could try and cook something up with an extended event on backup_restore_progress_trace, but that does not help you here.

In this case I would just add the user creation to your restore process.

Either with dynamic SQL if you have to

DECLARE @SQL nvarchar(max)
  SET @SQL =
   '
        USE ' + QUOTENAME(@RestoredDatabaseName) + '

IF NOT EXISTS (
SELECT * FROM sys.sysusers where name = ''testlogin'')
BEGIN
CREATE USER [testlogin] FOR LOGIN [testlogin];
ALTER ROLE [db_datareader] ADD MEMBER [testlogin];
END
'

EXEC(@SQL);

(Only use dynamic sql if needed)

Or without dynamic SQL if it is not needed

USE NewlyRestoredDatabase
GO
IF NOT EXISTS (
SELECT * FROM sys.sysusers where name = 'testlogin')
BEGIN
CREATE USER [testlogin] FOR LOGIN [testlogin];
ALTER ROLE [db_datareader] ADD MEMBER [testlogin];
END

Additionally, you could run a schedule a job to check if the user exists in all databases.

While doing all this, if the user already exists in the database, you have to watch out for orphaned users, and act accordingly.

If the user already exists in the restored database

Use NewlyRestoredDatabase
ALTER USER testlogin WITH LOGIN = testlogin

  Question 2, now that I have the user USER_R, a user A want to connect
  to management studio, he doesn't see any table or stored procedure,
  what's missing to let them see all objects and besides be read only?

I am guessing that this user needs to be able to see all objects, and what they are?
Like the T-SQL statements inside a stored procedure?

To do this, GRANT VIEW ANY DEFINITION to the login userA

USE master
GO
GRANT VIEW ANY DEFINITION TO userA;

The login still needs a mapped user to the databases that it needs to access, and be added to the public role (default).


Aggregating high volumes of data from many MySQL servers

Yes, MySQL will be able to do 20 million inserts per day, no problem. My calculator says 20 million per day translates to roughly 231 inserts per second. See e.g. this blog post from Percona's Database Performance Blog from 2010 which talks about more than 36 thousand writes per second. With the optimisations that have gone into MySQL since then as well as the hardware improvements, this number will obviously be even higher today.

And from a skills perspective, since you're already using MySQL, it might make sense to use that for this aggregation database as well.

Note that you can increase write throughput by combining multiple inserts together in a single transaction.

However, you may be able to use multi-source replication:

  MySQL Multi-Source Replication enables a replication slave to receive
  transactions from multiple sources simultaneously. Multi-source
  replication can be used to back up multiple servers to a single
  server, to merge table shards, and consolidate data from multiple
  servers to a single server. Multi-source replication does not
  implement any conflict detection or resolution when applying the
  transactions, and those tasks are left to the application if required.

So if you're able to use this, then you won't have to write any code to do the actual replication.


NLS_LANG can't be changed from inside a session, however other settings can.

You can't change the character set once a database connection has been established (ie: the 2nd part of NLS_LANG), but you can change the language with:

alter session set NLS_LANGUAGE=SPANISH

... and the territory with:

alter session set NLS_TERRITORY=SPAIN


"relation does not exist" trying to import mysql dump into postgres

This won't work. Don't even try feeding mysqldump output directly into psql. You'll need to dump schema and data separately, convert the schema either by hand or with a tool, load the converted schema into PostgreSQL then load the dumped data.

mysqldump's compatibility flags are moderately useful for dumping data but pretty useless for dumping schema definitions.

The "relation does not exist" error will be a follow-up error caused by a prior one. Try running with psql --set ON_ERROR_STOP=1 .... .


Piecemeal restore of entire database - fastest way to roll forward and restore multiple files

The first sequence you wrote is correct:

Restore file1 filegroup1
restore tlog set2 --roll forward to file 2 backup
restore file2 filegroup1
restore tlog set3
restore file1 filegroup2
restore tlog set4
restore file2 filegroup2
restore tlog set5
restore tail

You have no need to bring online the first file, then the second file, etc as all you want is to restore the whole database in minimal time.

I've just tried the first sequence as I thought it was right and it worked.

I tried then to restore from full backup + all the log files to find out if it was faster and it was. Certainly my database was small enough, just a test database, but you can try to restore your database to another database or to another server in both ways to compare the time.


Converting 'False'(string) to False(boolean) During LOAD DATA LOCAL INFILE

Convert the first value using user variables to load the True/False values.

Then, compare it to the value 'True'

PROPOSED SOLUTION

LOAD DATA LOCAL INFILE 'C:/bin/input.txt' INTO TABLE n1
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n' (@var1,c2)
SET c1 = (@var1 = 'True');

SAMPLE DATA

C:\bin>dir
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\bin

10/21/2014  10:01 AM    <DIR>          .
10/21/2014  10:01 AM    <DIR>          ..
10/21/2014  10:01 AM                23 input.txt
               1 File(s)             23 bytes
               2 Dir(s)  685,548,244,992 bytes free

C:\bin>type input.txt
"False","1"
"True","2"
C:\bin>

CODE FOR PROPOSED SOLUTION

USE test
DROP TABLE IF EXISTS n1;
CREATE TABLE `n1` (
  `c1` boolean ,
  `c2` int
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOAD DATA LOCAL INFILE 'C:/bin/input.txt' INTO TABLE n1
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n' (@var1,c2)
SET c1 = (@var1 = 'True');
SELECT * FROM n1;

CODE FOR PROPOSED SOLUTION EXECUTED

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS n1;
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE `n1` (
    ->   `c1` boolean ,
    ->   `c2` int
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.38 sec)

mysql> LOAD DATA LOCAL INFILE 'C:/bin/input.txt' INTO TABLE n1
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
    -> LINES TERMINATED BY '\r\n' (@var1,c2)
    -> SET c1 = (@var1 = 'True');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM n1;
+------+------+
| c1   | c2   |
+------+------+
|    0 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Grant User Permission To Restore Database And Access that database in sql server?

But when I restore another database backup that gets disconnected, i.e. not accessible.

You have to add the user to the db_owner role after the restore. You can use

ALTER ROLE [db_owner] ADD MEMBER [frank]

or EXEC sp_addrolemember 'db_owner', 'frank';

Note:

ALTER ANY DATABASE - You dont want to give the user too much rights since this will allow the user rights for all the databases.

  Grants or denies the ability to drop or modify existing databases as well as create new databases. The dbcreator fixed server role has this permission granted implicitly.


dangers of using SHRINKFILE in sql server 2008

If you use the full or bulk logged recovery model,
I advise that you take a look at this post for shrinking the log file. First perform a full database backup as switching the recovery models will break the log chain. Also chose a reasonable initial size for the log file, and generally speaking I use an increment in MB rather than percents.


PROPOSED QUERY

SELECT T1.name,T2.name FROM
(SELECT B.name FROM table1 A
INNER JOIN table2 B ON A.id=B.table1id
WHERE A.name='color') T1 INNER JOIN
(SELECT B.name FROM table1 A
INNER JOIN table2 B ON A.id=B.table1id
WHERE A.name='size') T2
ORDER BY T1.name,T2.name;

SAMPLE DATA

DROP DATABASE IF EXISTS rush1312;
CREATE DATABASE rush1312;
USE rush1312
CREATE TABLE table1
(id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
PRIMARY KEY (id));
INSERT INTO table1 (name) VALUES ('color'),('size');
CREATE TABLE table2
(id INT NOT NULL AUTO_INCREMENT,
table1id INT NOT NULL,
name VARCHAR(20),
PRIMARY KEY (id));
INSERT INTO table2 (table1id,name) VALUES
(1,'white'),(1,'red'),(2,'small'),(2,'medium'),(2,'large');

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS rush1312;
Query OK, 2 rows affected (0.55 sec)

mysql> CREATE DATABASE rush1312;
Query OK, 1 row affected (0.00 sec)

mysql> USE rush1312
Database changed
mysql> CREATE TABLE table1
    -> (id INT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20),
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.30 sec)

mysql> INSERT INTO table1 (name) VALUES ('color'),('size');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table2
    -> (id INT NOT NULL AUTO_INCREMENT,
    -> table1id INT NOT NULL,
    -> name VARCHAR(20),
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.34 sec)

mysql> INSERT INTO table2 (table1id,name) VALUES
    -> (1,'white'),(1,'red'),(2,'small'),(2,'medium'),(2,'large');
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT T1.name,T2.name FROM
    -> (SELECT B.name FROM table1 A
    -> INNER JOIN table2 B ON A.id=B.table1id
    -> WHERE A.name='color') T1 INNER JOIN
    -> (SELECT B.name FROM table1 A
    -> INNER JOIN table2 B ON A.id=B.table1id
    -> WHERE A.name='size') T2
    -> ORDER BY T1.name,T2.name;
+-------+--------+
| name  | name   |
+-------+--------+
| red   | large  |
| red   | medium |
| red   | small  |
| white | large  |
| white | medium |
| white | small  |
+-------+--------+
6 rows in set (0.00 sec)

GIVE IT A TRY !!!


PostgreSQL vs. MySQL: spatial feature comparison

I can't speak to advantages/disadvantages vis-a-vis MySQL, but the PostGIS code is pretty widely regarded as one of the best (in terms of speed/functionality) and most mature (in terms of testing/real-world exposure) available.

By way of example, there was a talk at PGEast 2010 by some folks from the FAA on their converting their airport database (used by AeroNav and others to compile charts) to Postgres/PostGIS from Oracle.
The avationDB site is also built on top of Postgres (8.0).

If GIS-related queries are at the heart of what you're doing my suggestion would be to go with Postgres.  It can certainly handle everything else you would normally do in a relational database as well.

In terms of making the switch from MySQL, the documentation behind Postgres is first-rate, and there's also a section of the Oostgres Wiki about switching from MySQL to Postgres.
The initial learning curve may be a bit steep and you may need to tweak your database and any stored procedures (if you've written them for MySQL already), but it is not an insurmountable task.

You should be able to pick up enough to make the switch in a couple of weeks time, and if you set up a development database you can probably be well versed in routine tasks within a month, and confident you know where to look in the manual for the not-so-routine ones.


Generally speaking, recent Intel processors perform better on most SQL Server workloads compared to recent AMD processors. Intel processors have better single-threaded performance and lower physical core counts (so the licensing costs are lower for SQL Server 2012). Even with the SQL Server Core Factor Table discount for AMD, it is pretty hard to make a case for using AMD processors for SQL Server 2012.


Sql Server Measuring Query Memory Consumption

You should be using dynamic management view (sys.dm_exec_query_memory_grants or sys.dm_exec_cached_plans ) to investigate memory consumption .
Example:-

Select * from sys.dm_exec_cached_plans where objtype='Proc';

I suggest you read Understanding SQL server memory grant article


How to use a relative path in sqlcmd script

If you want a relative path, just use relative path notation.
:SETVAR ScriptDir "../../"

That will set your ScriptDir to two directories higher than wherever you invoked sqlcmd from.

or
:SETVAR ScriptDir "moo/oink/bah/"

That will look for scripts in the moo/oink/bah subdirectory of wherever you invoked sqlcmd from.


Where can I download an old version of PostgreSQL for Windows?

For good reason PostgreSQL 8.4 ceased being supported at July 2014. Moreover, PostgreSQL doesn't host compiled versions for Windows, and Enterprise DB likely doesn't want to incur the liability of hosting packages known to be insecure. Even AS-IS it becomes a question of reputation.

Upgrade your software 8.4 was released on July 2009. It's older than Windows 7.

If you must have 8.4 feel free to download the source and compile or to seek out a consultant,

  PostgreSQL for Windows can be built using MinGW, a Unix-like build environment for Microsoft operating systems, or using Microsoft's Visual C++ compiler suite. The MinGW build variant uses the normal build system described in this chapter; the Visual C++ build works completely differently and is described in Chapter 16. It is a fully native build and uses no additional software like MinGW.

You can find the source for 8.4 on git, or in a zip

Archives

As others have pointed out,

  If you are adventurous: http://oldapps.com/de/postgresql.php or http://oldversion.com/windows/postgresql – a_horse_with_no_name 30 mins ago

However, whether or not you want to trust oldapps.com or oldversion.com is another question. I certainly wouldn't.

Linux

You could always use Linux to bring yourself up to a recent version and then dump it and load it in a version supported by Windows and hosted by EnterpriseDB.


Why can't I take RDBMS Database Design practices, and use for NoSQL MongoDB Database Designs?

If you want to use a relational structure, and you're thinking about doing it like this with a NoSQL system, use an RDBMS, that's what they're designed to do.

Using the right tool for the job will always result in better performance, and no-one has ever been fired for using an RDBMS where one fits instead of using a NoSQL solution where it doesn't make sense, like in a banking situation where you need 100% guaranteed consistency, not "eventual consistency".

Using relational algebra in a non-relational database will just make you replicate relational functionality in code, and I can guarantee you're not better at that than, say, SQL Server or Oracle.


Always return X rows with variable starting row

Okay, so we need to find out how many rows are in the table, and how many rows are left once we've moved to the marker defined by @Partial.

-- simple variable to hold the offset:
DECLARE @offset int;

;WITH x(o,c) AS
(
  SELECT
    -- how many rows are at or past the @Partial marker?
    SUM(CASE WHEN CustomerID >= @Partial THEN 1 END),
    -- how many rows total?
    COUNT(*)
  FROM @Customers
 )
 -- the magic: a conditional that determines if we're
 -- too close to the end, and subtracts the difference
 -- between how close we are and how close we want to be
 SELECT @offset = (c-o) - CASE
   WHEN @NumWindowRows > (c - (c-o)) THEN
       (@NumWindowRows - (c - (c-o)))
   ELSE 0 END
FROM x;

SELECT
  ROW_NUMBER() OVER (ORDER BY CustomerID),
  CustomerID,
  Name
FROM @Customers
ORDER BY CustomerID
OFFSET @offset ROWS
FETCH NEXT @NumWindowRows ROWS ONLY;

You could probably do this all inline instead of using the @offset variable, but it gets messy very fast, and I don't think it's possible to avoid the additional scan required to derive those counts anyway.

This is far simpler than your logic. But it assumes that you will always order by CustomerID and never add additional filters. If there are less than @NumWindowRows rows in the table, well...


Rolling week numbers into one row and getting cummulative values

create table orders(id int identity, dt_created date);

insert into  orders (dt_created) values
('20180101'),('20180108'),('20180109'),('20180110'),('20180112'),
('20180113'),('20180114'),('20180115'),('20180116'),('20180117'),
('20180118'),('20180120'),('20180121'),('20180122'),('20180123');
GO

15 rows affected

SELECT
    DATEPART(WK, dt_created) week,
    COUNT(*) CT
FROM
    orders
GROUP BY
    DATEPART(WK, dt_created)
GO

week | CT
---: | -:
   1 |  1
   2 |  5
   3 |  6
   4 |  3

Have a look at OVER clause and how to get a cumulative total.

WITH WK AS
(
SELECT
    DATEPART(WK, dt_created) week,
    COUNT(*) CT
FROM
    orders
GROUP BY
    DATEPART(WK, dt_created)
)
SELECT
    week,
    SUM(CT) OVER (ORDER BY week) total
FROM
    WK;
GO

week | total
---: | ----:
   1 |     1
   2 |     6
   3 |    12
   4 |    15

db<>fiddle here


How to read "CPU (ms/sec)" in the "Recent Expensive Queries" of Activity Monitor?

This is the ratio of CPU time used by that query per second. Internally to SQL Server in this DMV (sys.dm_exec_query_stats) CPU time is measured in microseconds and the report is pulling the usage per second. However, the ms in the report is in milliseconds for the Activity Monitor and microsecond would be mcs.

The reason you're seeing 1000 in this result set could be because that query used all CPU time for that one-second interval. Restated, it has used 1000 ms of CPU time for that second. On a system with multiple schedulers (sys.dm_os_schedulers), it is possible to have queries go parallel and use multiple schedulers and have > 1000ms/sec of CPU time. In your example below, 500ms of CPU time for the SQLOS scheduler has been consumed by that query in the last second. This means that 50% of one CPU's time was dedicated to this query. Note that it could have been a parallel query and only used 10% of five schedulers (which correlate to the number of CPUs). Depending on the timing of these parallel threads, it could present as low CPU usage in the % Processor Time. In your performance monitor are you looking at the _Total or per processor? Also, you can run the query below to see how SQL Server is reporting its own CPU utilization (thank you Glenn Berry).

DECLARE @ts_now BIGINT;
SELECT @ts_now = cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info;
SELECT TOP(10)
    SQLProcessUtilization AS sql_server_process_cpu,
    SystemIdle AS system_idle_process_cpu,
    100 - SystemIdle - SQLProcessUtilization AS other_process_cpu,
    DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS event_time
    FROM (
            SELECT
                record.value('(./Record/@id)[1]', 'int') AS record_id,
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS [SQLProcessUtilization],
                [timestamp]
            FROM (
                    SELECT [timestamp], convert(xml, record) AS [record]
                    FROM sys.dm_os_ring_buffers
                    WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
                        AND record LIKE '%<SystemHealth>%') AS x
                  ) AS y
    ORDER BY record_id DESC;

Do keep in mind that total_worker_time is the amount of CPU time used by the query since the plan was compiled*. Take a look at the Executions/min right next to the CPU (ms/sec) in the report to get a sense of whether or not this query plan has been used multiple times. Note: This may require math. Having said all of this, I fired up Activity Monitor and looked at one of the queries that was showing high CPU time on a development instance. I then went to the DMV to look at total_cpu_time for that query and the number is quite high (which is to be expected). This leads me to believe that the math is being handled internally by Activity Monitor and is taking the difference between current total_cpu_time and what it knew the total_cpu_time to be 1 second ago.

So in summary, this is showing you how much total_cpu_time in microseconds the query in question has used in the last second.

I'd also make a note of that query and look at the sys.dm_exec_query_stats results for that query plan hash and look at the other information provided by that DMV.


Performance difference in accessing differrent columns in a Postgres Table

The way the data is stored, PostreSQL doesn't know what offset to go to look at for the data for int200 column on any given row, without knowing the sizes of all previous columns for that row.  So it has to loop through the null bitmap (at a minimum) of all earlier columns before it can know that most of them have zero size.

There is an optimization for cases where you are selecting a single column and that column is found to be null via use of the null bitmap.  But you are selecting two columns not just the column int199, so you don't trigger this optimization.  It is unlikely an optimization would be added for selecting multiple, widely separated columns.  You would need some heuristic to decide what makes the columns widely separated, and then everyone would pay the cost for running that heuristic, even the vast majority of the cases that can't benefit from it.  If you were willing to get your hands dirty with a bit of hacking, perhaps you could convince us otherwise.  But monkeying around with the internals has a steep learning curve.

If you have a practical use case for making this situation faster, perhaps you should check out Citus Data's column store extension.


Database Consistency History fails in SQL Server 2017 Express

This is a limitation in SSMS. This report does not properly support Linux, as the script is performing path manipulation using '\', which is the Windows path seperator.

This is a snippet of code run for that report:

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'' ;

You can post product feedback here.


Get not matching record as null while joining two queries

Seems to me like you just need to move the filter you are using in the second query as a CASE expression:

SELECT  UDA.production_date_T,
        COUNT(WO.order_state) cntplan,
        SUM(CASE WHEN WO.order_state = 'BOOKED' THEN 1 ELSE 0 END) ACTUAL
FROM dbo.WORK_ORDER WO
INNER JOIN dbo.UDA_Order UDA
    ON UDA.object_key = WO.order_key
GROUP BY UDA.production_date_T
;


Manually VACUUM bloated tables once `hot_standby_feedback` is on on postgresql?

If you have hot_standby_feedback = on, a manual VACUUM won't delete the dead row versions if there is a long running query on the standby.

The only way to do that would be to set old_snapshot_threshold to a value different from the default value. Then VACUUM (and autovacuum) will remove dead tuples even if an old transaction might still need them. Note that that would defy the purpose of hot_standby_feedback, as then the long running query might receive a snapshot too old error.


NVARCHAR in SQLServer to VARCHAR in AWS Redshift

Got the answer from aws.

NCHAR and NVARCHAR types are converted to CHAR and VARCHAR types, respectively, and are stored in the specified number of bytes. A VARCHAR(120) column consists of a maximum of 120 single-byte characters, 60 two-byte characters, 40 three-byte characters, or 30 four-byte characters.
So, if I am storing double-byte characaters in SQL using NVARCHAR(x), I can define VARCHAR(2x) in Redshift to have the same storage behavior.
Following is the link for further reference:
http://docs.aws.amazon.com/redshift/latest/dg/r_Character_types.html