Bloated transaction log and .bak backup size confusion

How is a 365 .bak file restoring both a 369MB .mdf file and a 29.7GB
  .ldf file?

It's not restoring the data from the log file, or backing it up, it's just creating it with the same size as it was when backed up.  Check the free space and it should be 99% free.

  Any suggestions on what to do regarding the recovery model and bloated
  .ldf file? We are only concerned about doing backups once nightly - we
  don't need to do differential backups at this time - but it's
  something I will tackle later.

If you are only worried about doing backups once a night then change the recovery model to simple.  If you leave it in full you will need to perform log backups as well.  Once you switch the recovery model or start performing log backups you could do a one time shrink to get back the space.


How to handle process blocking in SQL Server Profiler?

The Blocked Process Report event class indicates that a task has been blocked for more than a specified amount of time.

To configure the Blocked Process Threshold and frequency at which reports are generated, use the sp_configure command to configure the blocked process threshold option, which can be set in seconds. The threshold can be set from 0 to 86,400.


Is sharding effective for small collections?

Is it also effective for 10 000 collections with 10 000 documents each ?

Most people have the "single large collection" problem and so the sharding is clearly useful for reducing headaches of balancing this data.

However, when you have 10 000 small collections, your headache is probably not "balancing the data". With this many small collections your problem is likely about tracking these collections. Depending on your document size, you may not even break the lower limit for sharding to actually happen.

For the really small collections, you can use the little-known movePrimary command to manage the location of your data.

Of course, the other way to look at this is why do you have 10k collections? A collection doesn't need homogeneous objects and with 10k collections most of them have to be generated. It's quite possible to store different "types" of data in the same collection, reduce the number of collections and then include the type as part of the shard key.


EAV structure explained in Layman's terms

Here is what I think is intended in this model:

Category is a kind of thing.  In a data model it would be an entity type, in a database it would be a table.
Attribute is a facet of a thing.  In a data model it would be a predicate type, in a database it would be a column.
Object is an instance of a thing.  In a data model it would be an entity, in a database it would be a row (in a particular table).
Value is an instance of a predicate or a column value in a database table.
Domain Lookup is a mechanism that allows you to constrain attribute values.  This implementation works by constraining the value to one in a set of legal values.  In a database this could be likened to a check constraint or a lookup table.  Obviously there are other types of constraints that could be imposed (but aren't in this metadata model) such as range constraints.
RIConfig is a relational constraint between two kinds of things.  This is a relationship type in a data model or a foreign key constraint in a database.  At this level it is describing the relational rule, not the instances of relationships.
RI is an instance of a relationship between to specific entities.  In a physical database, this would be a record in a foreign key index (perhaps?)

You are quite right to observe that the modeling of relationships is flawed insofar as it doesn't account for the implementation of these relationships.  RIConfig should join to Attribute not to Category - or rather, there should be an intersection table between RIConfig and Attribute to allow for compound primary/foreign key relationships.

As I noted above, domain constraints are also not handled with realistic flexibility.

Given the limitations of this model, it would not be adequate as a metadata repository for many real-world databases.


Getting new and updated data with an archive column

Since you are using SSIS and capturing the changed rows, you should have SSIS capture the PK values for the rows that are being processed. The last step of the package should be to do a targeted update on the source table, setting the [Archive] field to 1 based on those PK values.

And if not already done, the update trigger needs to exclude those changes from setting the [Archive] field to 2. You will probably need something along the lines of:

UPDATE tab
SET    tab.[Archive] = 2
FROM   dbo.MyTable tab
INNER JOIN INSERTED ...
INNER JOIN DELETED ...
WHERE INSERTED.Archive = DELETED.Archive
AND   DELETED.Archive IN (0, 1) -- Add 3 to this list; see below for why

BUT, then you need to deal with the scenario where a row gets update during the ETL process. You still want to ETL that change the next time around. But if the ETL process is running it will change the [Archive] field to 1 at the end and then there is no record of that row being changed. So then you kinda need another value to indicate "Being ETLed", maybe 3. You can then, similar to what @billinkc suggested in a comment on the question, do an UPDATE with the OUTPUT clause as follows:

UPDATE TOP (@BatchSize) tab
SET    tab.[Archive] = 3
OUTPUT INSERTED.*
FROM   dbo.MyTable tab
WHERE  tab.[Archive] IN (0, 2)
ORDER BY tab.[Archive] ASC;

Then, in the final step of the SSIS package, the targeted update should be on both the PK values AND [Archive] = 3. And add 3 to the IN list in the trigger update query above.  This way, only records that have not changed during the ETL process will get updated to 1, while records that have been changed during the ETL will appear as 2 and will get picked up again the next time around with their modified values.

Of course, you might also want to create a filtered index on the [Archive] field to support the getting of records marked as 0, 2, or 3. Hence:

CREATE INDEX ... WHERE [Archive] IN (0, 2, 3);

You want to grab the records marked as 3 because, assuming this is single-threaded per field server, records marked as 3 represent stuck records due to a prior process that failed. This is also why you don't want to mark the records as 1 in the UPDATE...OUTPUT when you initially get them; if the process fails then they will be marked as already archived and not picked up again.

That being said, you might be better off using a queue table instead of a status field. You could create a simple table with just a few fields:

QueueID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
MyTablePKfield  INT NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()

Then the trigger becomes a simple INSERT, UPDATE trigger that just does:

INSERT INTO dbo.MyTableEtlQueue (PKfield)
  SELECT ins.PKfield
  FROM   INSERTED ins;

Then the SSIS process gets the values via:

SELECT TOP (@BatchSize) etl.QueueID, tab.field1, tab.field2...
FROM   dbo.MyTableEtlQueue etl
INNER JOIN dbo.MyTable tab
        ON tab.PKfield = etl.MyTablePKfield
ORDER BY etl.QueueID ASC;

And the end of the SSIS process does a simple delete against the [MyTableEtlQueue] table based on the [QueueID] values it pulled in at the beginning.

In this model, you don't need the filtered index, or status column on a large table, which reduces contention on, and size of, that already large table. You don't need to worry about records in transit, whether in terms of updates or process failures.

EDIT:
Given the new information of the source table not having any uniqueness, that requires only a minor change to this queue table method: instead of tracking only the PKfield (which apparently doesn't exist ;-), track all of the fields (or at least all that need to be ETLed).

So the queue table changes to be:

QueueID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
MyTableField1  INT NOT NULL,
MyTableField2  INT NOT NULL,
...
MyTableFieldN  INT NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()

And the trigger changes to be:

INSERT INTO dbo.MyTableEtlQueue (PKfield)
  SELECT ins.MyTableField1, ins.MyTableField2, ..., ins.MyTableField1N
  FROM   INSERTED ins;

Then the SSIS process changes to be (note the lack of JOIN to the source table now):

SELECT TOP (@BatchSize) etl.QueueID, etl.MyTableField1, etl.MyTableField2, ...
FROM   dbo.MyTableEtlQueue etl
ORDER BY etl.QueueID ASC;

This queue table method isolates the source table (and its processing) from the audit/ETL process (much like CDC does). This allows you app to interact with the source table with no issues, and no new column which takes up more space on the datapage, and no potentially long running transaction holding onto the source table while the ETL processes.


From your comments, Person represents the generic information about any person tied to this system; students, teachers, other school employees.  Information that's in Person wouldn't need to be repeated in Student (name, address, DOB).

I would add the student's current grade/class (kindergarten through high school) to Student. Let's call that StudentClass; we'll use it later.

Also - you may need an intermediary table to go with MarkType and Mark.  I would think you'd need something to indicate the total available points for each item (as shown in the headers of your output). MarkType makes sense as is. I would have Mark as a table indicating a particular exam, quiz, homework assignment (etc.). So, it would need:

MarkID (primary key)
MarkType
SubjectId
MarkValue - the points this specific item is worth (for example 10 points).

Then, I would have StudentMarks:

StudentMarkId (primary key)
MarkId
StudentId
Mark - the points earned on this specific item, by this student (for example one student might get 9 points out of the possible 10, other might only get 5).

I would also be inclined to decouple Term and Subject - there may be subjects that are only offered for one term. Similarly, different instructors may provide a different mix of assignments, and assign different values to a given assignment than another instructor. I would create two new tables:

Instructor:
 - InstructorId (primary key)
 - PersonID (foreign key to Person, where name, address, DOB would be)
 - ... (other information specific to a given instructor, regardless of the subject they're teaching, or the term/year they're teaching it in)

Course:
 - CourseId (primary key)
 - SubjectId
 - TermId
 - InstructorId

TermId would be removed from Subject, and CourseId would replace SubjectId in Mark.

Another note: it might make sense to specifically tie a student to a course. that could be called....

CourseRoster:
 - StudentCourseId (primary key)
 - StudentId
 - CourseId
 - StudentCurrentClass (the value of StudentClass at the time this student was in this class).

In that case, replace StudentId with StudentCourseId in StudentMarks.

This would even let you identify if there were exams/quizzes/assignments that a particular student in a particular course had no mark for.

UPDATE: Based on your comments, I added some fields to track the student's current "class" (K through high school), and their class when they took a specific course. consists of hard-coded values (in the US, there's kindergarten plus 12 years of education before university, so "K" and "1" - "12" would work here), the Class table shouldn't actually be needed.If you need to establish explicit classes for some reason, then the Class table would still be needed. I would reverse it, though - one class has multiple students, so put ClassID in the Student table. Note: you may want to consider tying each Class to a specific Term, as well.


Add partition works but not the drop partition

I have solved it the issue is here CONCAT(' p' when I remove the space it is solved. But I don't understand why it does not work with the space because when we created it is with space?


Select column name dynamically based on rows from another table in SQL server

It's probably not the optimal way to do it and I would probably look at normalizing the database but here's a way to get the data you want.

with cte as (
select 'field1' "name", convert(nvarchar(max),field1) "value" from [dbo].[DynamicFieldsValue] where customerID=1
union
select 'field2' "name", field2 "value" from [dbo].[DynamicFieldsValue] where customerID=1
union
select 'field3' "name", field3 "value" from [dbo].[DynamicFieldsValue] where customerID=1
)
select * from [DynamicFieldsMaster] dm
join cte on dm.name = cte.name


How to export mysql database based on a where condition

Using SQL from the mysql command-line:

SELECT * from YOURTABLE
WHERE status=0 and id>20
INTO OUTFILE 'yourtable.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

or using mysqldump with the --where= option:

mysqldump -u youruser -p yourdbname yourtablename --where="status=0 and id>20">yourtable.sql

Using phpMyAdmin you can execute the query in the GUI & click "export" under the resultset.


Does SQL Server log record uncommited operations?

Understanding 1 is correct. SQL Server records every operation that changes data to the transaction log. A rollback is a change to data so it also records that to the transaction log as well. As statement A run it will write data to the transaction log and will also reserve data in the transaction log in case statement A needs to be rolled back. The same is true for B and C. When you rollback the transaction more information will be written to the log.

There are plenty of ways to see this in action so below is a quick demo. Here is the query that I'll use to see what was written to the log:

SELECT
  COUNT(*) transaction_count
, SUM(database_transaction_log_bytes_used) used_bytes
, SUM(database_transaction_log_bytes_reserved) reserved_bytes
FROM sys.dm_tran_database_transactions
where database_id = 10;

My table:

create table TLOGDEMO (FLUFF VARCHAR(1000));

BEGIN TRANSACTION

Query A uses minimal logging:

INSERT INTO TLOGDEMO WITH (TABLOCK)
SELECT REPLICATE('A', 1000)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

After A:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║                 1 ║   24006640 ║      175429451 ║
╚═══════════════════╩════════════╩════════════════╝

Query B does not use minimal logging:

INSERT INTO TLOGDEMO
SELECT REPLICATE('B', 1000)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

After B:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║                 1 ║ 7352935708 ║     1613986255 ║
╚═══════════════════╩════════════╩════════════════╝

Query C changes less data:

INSERT INTO TLOGDEMO
SELECT REPLICATE('C', 1000)
FROM master..spt_values c;

After C:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║                 1 ║ 7355821748 ║     1614545331 ║
╚═══════════════════╩════════════╩════════════════╝

Now I will issue a ROLLBACK and query the DMV while the rollback happens. Below is a table of a few snapshots:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║ 1                 ║ 7393305528 ║ 1573797677     ║
║ 1                 ║ 7458767420 ║ 1502635737     ║
║ 1                 ║ 7682482356 ║ 1259440979     ║
║ 1                 ║ 7803881368 ║ 1127471233     ║
║ ...               ║ ...        ║ ...            ║
╚═══════════════════╩════════════╩════════════════╝

During the ROLLBACK, the used bytes increases and the reserved number of bytes decreases. That's because SQL Server is using the space it set aside before to undo the transaction. To undo the transaction it must change data so it writes more data to the log.


MYSQL-BEFORE UPDATE Set value of Column A to value in Column B if Column A is to be updated

Adjust the trigger's syntax

DROP TRIGGER IF EXISTS `update_last_price`;
CREATE TRIGGER `update_last_price`
BEFORE UPDATE ON `products`
FOR EACH ROW
SET NEW.last_price = OLD.unit_price;

or if the trigger is to have multiple lines, do this

DROP TRIGGER IF EXISTS `update_last_price`;
DELIMITER $$
CREATE TRIGGER `update_last_price`
BEFORE UPDATE ON `products`
FOR EACH ROW
BEGIN
    SET NEW.last_price = OLD.unit_price;
END $$
DELIMITER ;


When you create an RDS instance, you pick an instance size, a database size, and optionally determine if you need specific IOPS.

I would start by looking at the RDS monitors, and see if there are any parameters that are hitting limits.  You may need to increase the instance size or IOPS to increase performance.


In Teradata SQL how to get random number between a pair, coming from field/column?

This will return a number within the requested range:
RANDOM(0, 2147483647) mod (T.VAL_MAX - T.VAL_MIN +1 ) + T.VAL_MIN

But there might be a minimal bias towards the lower numbers due to MOD, e.g. for 1-3 you get following probablities:
1,2 = 715,827,883 / 2,147,483,648
  3 = 715,827,882 / 2,147,483,648


What happens when you change a heap into a clustered table in SQL Server?

Yes, when you create a clustered index on a heap the rows are all sorted moved to the new clustered index.  Any non-clustered indexes are rebuilt with the new clustered index key as the row locator.
It's the same table, though, so triggers, constraints, etc don't have to change.
The reverse is not true, however.  When you drop a clustered index on a table, the leaf-level pages of the clustered index are left in-place and they become the new heap.  Nonclustered indexes are still rebuilt as the row locator switches from the CI key to the rowid (file:page:slot).


Every "INSERT" query check a combination of column's records

It sounds like what you are looking for is a "unique" constraint:

ALTER TABLE Table1 ADD UNIQUE(Series, Orders)

That will cause an error to be raised when you try create a row (by inserting new or updating an existing one) that duplicates an existing combination of Series and Order.

Depending on the rest of the database design, these columns might be a good candidate for a compound primary key which would also enforce uniqueness.

This is fundamentally basic database stuff, I would suggest you grab an introductory book or find a good tutorial online. Keywords that you should understand: primary key, foreign key, check constraint, unique constraint, surrogate key


MySQL Replication - Resync master after slave taking over

If the Master was already acting as a Slave, then you really had a Master-Master setup, and the replication from the old Slave (current Master) will flow to the old Master that had been down.

Otherwise, you are in deep weeds.  You may as well promote the currently-acting-as-Master machine to be the Master.  And then rebuild the other machine as a Slave.  Or rather a Master in Master-Master setup so you don't hit this problem again.


Disk I/O increased when changing RBR to MIXED

When a complex UPDATE is run on the master, and it results in only a few rows being updated, then one of these happens:

SBR:  Re-execute the UPDATE on the Slave.  This stalls replication, waiting for the complex query to finish.
RBR:  Only the changed rows are sent to the Slave.  These can be applied much more rapidly.

A simple UPDATE that modifies a million rows:

SBR:  Again, slow on the slave -- because of all the I/O do update the million rows.
RBR:  This is also slow, but for a different reason:  A million records are sent through the replication stream.  This involves I/O at both ends, plus network traffic.

DELETE, INSERT...SELECT, and a few other things have similar characteristics.

I can't be more specific without knowing what, exactly, you were replicating.  I hope my examples give you some clues of what could be happening.


NO!  MySQL is not designed to allow two servers to access the same databases.

Or, maybe...

If your goal is to provide redundancy for the case of server failure, then maybe you could set up a non-running MySQL on a secondary server, ready to mount the disk subsystem in the case of failure.

Consider, instead, Clustering (Galera, PXC, InnoDB Cluster).


Your PIVOT syntax is incorrect. You don't filter records in the PIVOT, you do that in the inner SELECT that represents your source for the PIVOT. I've got an example below, but I also should point out that it requires manually entering the dates you're pivoting as columns. If this date range will change or is to be parameterised then you need to look into dynamically generating this PIVOT statement. There are lots of questions and answers on DBA.SE, like this, that can help you.

Setup:

CREATE TABLE ##DateDimension (DateVal DATETIME, StrVal NVARCHAR(25))
GO
DECLARE @StartDate DATETIME,
    @Counter INT = 0

SET @StartDate = DATEADD(YEAR, -1, GETDATE())

WHILE (SELECT COALESCE(MAX(DateVal), '1900-01-01') FROM ##DateDimension) < GETDATE()
BEGIN
    INSERT INTO ##DateDimension (DateVal, StrVal)
    VALUES (DATEADD(DAY, @Counter, @StartDate), 'Data')

    SET @Counter = @Counter + 1
END
GO

Query Example:

SELECT
    [01/02/2019], [02/02/2019], [03/02/2019], [04/02/2019], [05/02/2019], [06/02/2019], [07/02/2019]
FROM
(
    SELECT CONVERT(VARCHAR(25), DateVal, 103) AS DateVal, StrVal
    FROM ##DateDimension
    WHERE DateVal BETWEEN '2019-02-01' AND '2019-02-08'
) src
PIVOT
(
    MAX(StrVal) FOR DateVal IN ([01/02/2019], [02/02/2019], [03/02/2019], [04/02/2019], [05/02/2019], [06/02/2019], [07/02/2019])
) pvt

Results:

01/02/2019  02/02/2019  03/02/2019  04/02/2019  05/02/2019  06/02/2019  07/02/2019
----------------------------------------------------------------------------------
Data        Data        Data        Data        Data        Data        Data

EDIT: Based on comments, below is a new example pivoting the Licence Plates before left joining to the date dimension.

CREATE TABLE ##LicencePlates (LicPlate NVARCHAR(255),
    DateVal DATETIME,
    Department NVARCHAR(25))
GO
INSERT INTO ##LicencePlates (LicPlate, DateVal, Department)
VALUES ('858-456', '2019-02-01', 'ABC'),
    ('128-5045', '2019-02-03', 'DEF'),
    ('111-7090', '2019-02-15', 'ABC'),
    ('111-7091', '2019-02-27', 'DEF'),
    ('111-7092', '2019-02-08', 'ABC'),
    ('111-7093', '2019-02-21', 'XYZ')
GO

CREATE TABLE ##DateDimension (DateVal DATETIME)
GO
DECLARE @StartDate DATETIME = '2019-05-16 00:00:00.000',
    @Counter INT = 0

SET @StartDate = DATEADD(YEAR, -5, @StartDate)

WHILE (SELECT COALESCE(MAX(DateVal), '1900-01-01') FROM ##DateDimension) < GETDATE()
BEGIN
    INSERT INTO ##DateDimension (DateVal)
    VALUES (DATEADD(DAY, @Counter, @StartDate))

    SET @Counter = @Counter + 1
END
GO

WITH LicencePlatesPivot AS
(
    SELECT DateVal,
        COALESCE([858-456], '') AS [858-456],
        COALESCE([128-5045], '') AS [128-5045],
        COALESCE([111-7090], '') AS [111-7090],
        COALESCE([111-7091], '') AS [111-7091],
        COALESCE([111-7092], '') AS [111-7092],
        COALESCE([111-7093], '') AS [111-7093]
    FROM
    (
        SELECT LicPlate, DateVal, Department FROM ##LicencePlates
    ) src
    PIVOT
    (
        MAX(Department) FOR LicPlate IN ([858-456],[128-5045],[111-7090],[111-7091],[111-7092],[111-7093])
    ) pvt
)

SELECT dd.DateVal,
    COALESCE(pvt.[858-456], '') AS [858-456],
    COALESCE(pvt.[128-5045], '') AS [128-5045],
    COALESCE(pvt.[111-7090], '') AS [111-7090],
    COALESCE(pvt.[111-7091], '') AS [111-7091],
    COALESCE(pvt.[111-7092], '') AS [111-7092],
    COALESCE(pvt.[111-7093], '') AS [111-7093]
FROM ##DateDimension dd
LEFT JOIN LicencePlatesPivot pvt ON pvt.DateVal = dd.DateVal
WHERE dd.DateVal BETWEEN '2019-02-01' AND '2019-02-28'

DROP TABLE ##DateDimension
DROP TABLE ##LicencePlates


date in_date, date in_date_end: that is incorrect syntax. Use TO_DATE with the appropriate format string, example: to_date(in_date_end, 'YYYY-MM-DD').

But date values should be passed with DATE type, not VARCHAR2 and all the above becomes unnecessary.


Postgresql streaming replication and wal archive?

Further testing revealed that the different WAL files were mostly those that had been rotated but not yet written to. The new slave would try to archive these (possibly fixed in 9.2) but they wouldn't be used for recovery.

The other problem is the overwriting. We disabled the overwrite (instead storing with a new filename so we at least keep the WAL just in case) and everything works... both failover and later restore.

NOTE: my earlier answer here that was completely wrong. See the edit log if you need to see how wrong.


How do I cancel an app lock request in SQL Server?

Looking at the source of the sp_getapplock wrapper proc, all the return values except for -999 originate from the underlying sys.xp_userlock  internal stored procedure. I'd bet the internal proc returns a -2 when the request is cancelled by an attention event (client query timeout or explict client query cancel). However, no further sp_getapplock code executes after the batch is cancelled, including the RETURN statement. Consequently, the -2 return code may be returned internally but there's no practical way for the client to get the value.

Assuming this theory is correct, there isn't any value in translating to -2 to more descriptive message since it's the client that cancelled the request in the first place.

I'll leave it to Paul to confirm this by stepping through the SQL database engine code with a debugger :-)


Erro MYSQL #1193 - Unknown system variable 'lc_messages'

SET lc_messages = ... doesn't work in MySQL Server 5.1.  This variable was introduced in 5.5.

  Before MySQL 5.5, the lc_messages_dir and lc_messages system variables were unavailable.

  To start mysqld with a particular language for error messages, the --language or -L option were used. The option value can be a language name or the full path to the error message file.

  https://dev.mysql.com/doc/refman/5.5/en/error-message-language.html


Check for DBCC OPENTRAN for any transaction on these tables. If NOT then you are free to KILL these sessions as these are only select statements. Find the sessions by querying sp_who2 and kill by syntax KILL  :-)


Permission denied in file trying to import

You've just run into a peculiarity of psql on Windows.

In my comment I just wanted to exclude the possibility that you really didn't have the necessary permission.  Then it turned out that even on Windows, you have to use 'normal' slashes instead of backslashes, that is:

\i 'C:/Users/Work/Desktop/School Work/load_database.sql'

should work fine regardless which folder you start psql from.  Notice that I used single quotes - with double quotes you'd got

"C:/Users/Work/Desktop/School Work/load_database.sql": Invalid argument


MariaDB/MySQL: Import from dump (.sql) file remotely via network

After a few tests, I conclude that the server itself (where I want to import) is slow due to having a lot of process going on. I think that was obvious from the beginning but I was just frustrated that I've set identical setting on both machines and getting a significantly different benchmarks.

The last test that I did was the following, 200MB sql dump file size I import to the destination server (server 2) using two method; locally & remotely; bench marked against another server unit I have locally (server 1). Few more details:

Server 1 - CPU load 20% - 30%  and RAM load 50% - 60%
Import on MariaDB 10.3 for 200MB sql dump file took less than 2 minutes.
Server 2 - CPU load 50% - 60% and RAM load 50% - 60%
Import on MariaDB 10.3 for 200MB sql dump file took up to 8 minutes locally.
Import on MariaDB 10.3 for 200MB sql dump file took up to 18 minutes remotely.

Like I mentioned, both servers have similar my.ini settings. I did not investigate further why Server 2 is slower but the import perform miles better when I discard mysqldump --extended-insert=FALSE setting on my dump files after finding that this causes extremely slow inserts!. The other obvious probable reason would be the server is busy (hint: CPU load). Also, the data that I need to import is actually for another party to use but since the time zone difference between us and them is 6 hours (I just found out about this!), I didn't pursue this aggressively.


having 'Measure' > 0 syntax does not work in mdx query

You put the HAVING clause in the ON COLUMNS axis (or ON 0) which attempts to filter out any column which has a null or zero IndentCount. Instead you need to move it to the ON ROWS axis (or ON 1) if you want it eliminate some rows.

I would also suggest you may optimize the performance by using the NonEmpty function instead of HAVING. Since you can’t have a zero or negative IndentCount the following will do what you want and will perform better since it will only calculate the TOTAL measure for rows you will end up keeping.

with member [Measures].[TOTAL] AS
SUM(Periodstodate([DimTime].[Year].[(All)] ,[DimTime].[Year].currentmember)
,[Measures].[IndentCount])

Select {[Measures].[IndentCount],[Measures].[TOTAL]}
   on 0 ,
   NonEmpty([DimTime].[Year].[Year], [Measures].[IndentCount]) on 1
from [Procurement]


I wrote these posts

Mar 28, 2014 : MySQL not releasing memory
Apr 24, 2012 : How costly is opening and closing of a DB connection?
May 24, 2011 : What are user connections - when are the created and destroyed?

and discussed the many buffers that are allocated for each connection. They are part of RAM and not encapsulated within the cache. Since the buffers are set based whether it is defined in my.cnf or defaulted if it is not, it would not make sense that the memory is released if a thread needs to be reused and will set its buffers to the same sizes again. As I mentioned in How costly is opening and closing of a DB connection? it would be an expensive operation to deallocate and then immediately allocate the buffers.
Therefore, with regard to your actual questions

Does it mean thread occupied the memory even system is using threads from cache?
A cache in thread still use memory?

The answer to both would be Yes


Remove the log shipping configuration on the secondary server, when the primary is no longer available

You are facing what is called orphaned entries in logshipping tables in msdb. You can use below tsql to just manually clean up logshipping orphan entries. For your case, since you don't have the primary database available, you should just run the secondary part or you can try running sp_delete_log_shipping_secondary_database N'secondary_database'. It will just remove the secondary database settings from msdb.

----- on primary
delete from [dbo].[log_shipping_monitor_primary]
where primary_database = 'your database name'       -- Change the database name
go
delete from [dbo].[log_shipping_primary_databases]
where primary_database = 'your database name'        -- Change the database name
go
delete from [dbo].[log_shipping_primary_secondaries]
where secondary_database = 'your database name'      -- Change the database name

----- on Secondary
delete from [dbo].[log_shipping_monitor_secondary]
where secondary_database  = 'your database name'     -- Change the database name
go
delete from [dbo].[log_shipping_secondary]
where primary_database = 'your database name'        -- Change the database name
go
delete from [dbo].[log_shipping_secondary_databases]
where secondary_database = 'your database name'      -- Change the database name

Note: Above methods are safe and will delete ONLY logshipping related information.


Need a faster method to concatenate rows into column(s)--currently using CTEs

Looks like the issue in your code is multiple access to the CTEs and all the complicated logic you have in there. You should try to do everyting in a single pass with UNPIVOT/PIVOT. I did something similar here:
http://spaghettidba.com/2011/10/13/concatenating-multiple-columns-across-rows/
In my example you will find the concatenation of sentences in different languages (not exactly what you're after), but it seems to me that you could use the same technique.


Assuming your RDBMS supports windowing functions, something like this will give you what you are asking for:

select *
from( select dataentry.*,
             row_number() over (partition by fname, mname, lname order by title) n
      from dataentry )
where n=1;

But note that I have arbitrarily chosen to order by title when discarding 'duplicates' - and even more arbitrarily chosen to overlook that if two matching rows also have matching titles the database will order them in an undefined and possibly unpredictable way. You will need to adjust that to meet your requirements.


Ensuring a GROUP has specific values, no more no less with an aggregate

I think you want something like this..

SELECT a
FROM tt
GROUP BY a
HAVING array_agg(b ORDER BY b) = ARRAY[
  -- whatever
]::uuid[];

sample data,

CREATE TABLE tt ( a text, b uuid DEFAULT gen_random_uuid() );
INSERT INTO tt(a) VALUES ('foo'), ('bar'), ('baz'), ('foo'), ('foo');

SELECT a, b FROM tt ORDER BY a,b;
  a  |                  b
-----+--------------------------------------
 bar | 6a7d1d36-d16f-459c-9fb0-b5a54f1fc5b3
 baz | 2fca5dee-4bfe-4862-9b8a-79890fbfda28
 foo | 32f9d608-f87f-4830-b0d8-6215f60a71ee
 foo | 3fe0cffe-446e-4e94-8785-2f893ee91a0c
 foo | f9d07c5a-e69b-4501-a441-5bbd86ed7f4b
(5 rows)

This will return foo,

SELECT a
FROM tt
GROUP BY a
HAVING array_agg(b ORDER BY b) = ARRAY[
  '32f9d608-f87f-4830-b0d8-6215f60a71ee',
  '3fe0cffe-446e-4e94-8785-2f893ee91a0c',
  'f9d07c5a-e69b-4501-a441-5bbd86ed7f4b'
]::uuid[];

However, without exactly those three things and nothing more in the array_agg and in that order and you won't get a match.

If you don't care about excess matches, that is to say if foo having addition UUIds should return a match you can optimize this expression by copying them into an additional where clause which will use an index.

Shy of that, if you need faster you could move to use Materialized Views, or write a count clause too...

HAVING count(*) = ...
  AND array_agg(b ORDER BY b) = ...


Can I rely on this SQL shortcut to save a query?

Answer to self!

A better way to perform the query in the OP in a single query would be to explicitly group by status:

mysql> SELECT COUNT(*) AS total, status FROM users GROUP BY status;
+-------+---------+
| total | status  |
+-------+---------+
|     2 | active  |
|     1 | default |
+-------+---------+
2 rows in set (0.00 sec)

In addition to being clearer as to intention, this solution also works for the case in which status has more than two possible values.


How to determine consumption of data for archiving in sql server?

Analyzing the usage pattern of the server via profiler and/or extended events is going to be the way to go.  Or ask the business unit what data they want archived.  If they say that they don't want any data archived, then the conversation is over.


SELECT row based on range from columns in second table

When writing the answer below I wasn't aware of postgres' BETWEEN SYMETRIC, since pointed out by a_horse_with_no_name. That would be the simplest solution and I assume would be as easy for the query planner & runner to use indexes to serve as just BETWEEN.

In my defense, I'm mainly a SQL Server person... The longer answer is hopefully still useful for others in a similar position but not using prosgres, or where cross-DB compatibility is a requirement.

The ends of a BETWEEN comparison are always assumed to be low and higher, in that order. In most cases X BETWEEN Y AND Z it is simple syntactic sugar for X>=Y AND X<=Z and 41.33>=41.35 AND 41.33<=41.30 obviously isn't true.

You could use CASE to swap the values:

ON  lat BETWEEN
        (CASE WHEN lat_start<lat_end THEN lat_start ELSE lat_end END)
        AND
        (CASE WHEN lat_start>lat_end THEN lat_start ELSE lat_end END)

or the less verbose version suggested by ypercube™ in the comments:

ON (lat BETWEEN lat_start AND lat_end  )
OR (lat BETWEEN lat_end   AND lat_start)

but both of those would be inefficient because your join predicate is no longer saragble so will not result in an indexed search. If the number of rows being considered in each case is small because of other filters then this will not be an issue.

Another option is to UNION two comparisons:

SELECT <stuff>
FROM   location
INNER JOIN
       segments
       ON lat BETWEEN lat_start AND lat_end
WHERE  lat_start <= lat_end
UNION ALL
SELECT <stuff>
FROM   location
INNER JOIN
       segments
       ON lat BETWEEN lat_end AND lat_start
WHERE  lat_start > lat_end
;

(here the extra WHERE in each half stops you getting two rows for instances when lat_start = lat_end, so we can use the more efficient UNION ALL instead of plain UNION without worrying about duplicates in those cases)

This means you get indexed lookups for both queries instead of a scan of segments.
(Technically it would be possible for a query planner to decompose ypercube™'s example into the two lookups like the UNION example, but I'm not aware of a query planner that does this - there is a limit to how much thinking a QP can do before it starts taking too much time planning in the general case, compared to the time spent running the result.)

You could change how you are storing your data and insist that the line/area is always defined bottom-left to top-right (so you have lat_left and lat_right instead of lat_start and lat_end) then your original query will word. If direction of travel is important then you can add that as a separate boolean value: 0 for left-to-right,  1 for right-to-left, or similar.

You might want to look into extensions that exist for postgres specifically designed for geometric (including geological, lat+long based) data types and related queries: https://postgis.net
They may do a chunk of your work for you, after the initial learning curve, especially if you start needing to consider more complex shapes.


Error when restoring database from disk using a variable

Try removing the 'GO' statement.

Referencing the documentation for SQL Server Utilities Statements - GO

  The scope of local (user-defined) variables is limited to a batch, and
  cannot be referenced after a GO command.


That is a TNS entry that should be in tnsnames.ora. Listener can't do anything with that. If you want to do static registration, this is what you should put in listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME = TESTSID)
    )
  )


MDX - Sum values greater than then a median calculated measure

You need first to define a measure that will compte the median price across the siblings:

[Measures].[Siblings_median_price]
AS
MEDIAN([DimItems].[Dim Items Id].CurrentMember.Siblings, [Measures].[Price])

The following measure should return what you want:

[Measures].[Sum_below_median]
AS
SUM(
     [DimItems].[Dim Items Id].CurrentMember.Children
   , IIF([Measures].[Price] < [Measures].[Siblings_median_price]
   , [Measures].[Quantity], Null)
)


The biggest reason to have a large table_cache is so that LOCK_open mutex is not hot.  MySQL prior to 5.5 has a lot of contention when you are trying to open/close tables, so you want to restrict doing this as much as possible, i.e. have a large table cache.

So you don't care about any particular ratio of hits to misses (infact you should ignore ratios altogether - this blog post explains why).  What you care about is the miss rate, because the more times this happens per second, the higher the chance that you will have contention (one thread has to wait for another thread to release the lock.)

How do you spot the miss rate?  You fetch a few samples of Opened_Tables a few seconds apart during the busiest period of the day, and if there are increases in each sample, it's probably a good idea to see if you can bump up the table_cache.

Note: I very specifically do not recommend comparing to uptime.


Insert data in current table and update another table MySQL

Yes, a trigger could be a good approach, but first, you should look more closely at your code:

$ok = $mysqli->query( $sql );
$ok1 = $mysqli->query( $sql );

It seems fairly clear that the second line should be executing the $sql1 query, not re-executing the $sql query.

Your design also seems less than optimal, for a couple of reasons:

you're storing the same data in two different places in your database... the facilities table contains totals of product quantity, and that same information could be found by summing up the values found in the purchases table; information stored in more than one place is ripe for inconsistencies to creep in.
if changing the above isn't practical, consider also that you're fetching $product_current_quantity from the facilities table, then letting your application add that value to the value in the $product_quantity and then you're stuffing that value back into the facilities table... this practice will also cause problems for you soon, if it hasn't already.

What you should be doing in this case is letting the database update the values in place:

UPDATE facilities
   SET product_current_quantity = product_current_quantity + $product_quantity
 WHERE ...                       # ^^^^^ use the column, not a variable

The big problem here is one of improper handling of concurrency.  Assume two concurrent accesses involving the same facility/product:

Start with product_current_quantity of, for example, 5.  Request #1 will add 3 to the quantity and Request #2 will add 2 to the quantity... and these two requests happen at almost exactly the same time.  The total should now be 5 + 3 + 2 => 10, right? But...

request #1 reads product_current_quantity = 5
request #2 reads product_current_quantity = 5 (#1's update hasn't happened yet)
request #1 adds its local "5" + 3 and writes "8"
request #2 has already read "5" also, so it writes 5 + 2 => "7"
database value is now "7" instead of 10.

Letting the database do the math, and not your application, avoids this, because even if the two updates arrive at exactly the same time, each one will atomically modify the column's value, and the final result will be correct.

Additionally, this seems like a very appropriate place to be using transactions, because everything done inside a single transaction stands (COMMIT) or falls (ROLLBACK) together.

And, yes, a trigger would work, too:

# change the statement delimiter from semicolon to another symbol, such as "$$"
DELIMITER $$
DROP TRIGGER IF EXISTS purchases_ai $$
CREATE TRIGGER purchases_ai AFTER INSERT ON purchases FOR EACH ROW BEGIN

UPDATE facilities
   SET product_current_quantity = product_current_quantity + NEW.product_quantity
 WHERE facility_id = NEW.facility_id
   AND id = NEW.product_id;

END $$
# change the delimiter back
DELIMITER ;

In a trigger, the NEW and OLD aliases refer to the row in the table before and after the query that was just executed.  NEW applies only to INSERT and UPDATE triggers, and OLD applies only to UPDATE AND DELETE triggers.  Triggers can fire either BEFORE or AFTER the affected row is actually changed.  In the example above, this trigger fires AFTER INSERT -- immediately after the insert of the record into purchases.


Getting Today's , MTD, YTD Sale and profit

Mocked up some data and used a couple of CTEs to generate the aggregates.  Similar to creating a bunch of separate queries joined or unioned however you wanted.  This provides a little more readability and flexibility.  I also added a variable to simulate running this for different dates.

    /*  Data mockup

    IF OBJECT_ID('SalesTable') IS NOT NULL
        DROP TABLE SalesTable;

    CREATE TABLE SalesTable
    (
          [Date] DATE
        , Sales  NUMERIC(19,4)
        , PROFIT NUMERIC(19,4)
    );

    INSERT SalesTable
    VALUES
           ('2014-12-07', 133333, 12348)
         , ('2015-03-07', 133333, 12348)
         , ('2015-03-08', 214563, 34545)
         , ('2015-03-09', 299593, 59940)
         , ('2015-05-07', 233333, 12348)
         , ('2015-05-08', 214563, 34545)
         , ('2015-05-09', 299593, 59940)
         , ('2015-09-07', 333333, 12348)
         , ('2015-09-08', 214563, 34545)
         , ('2015-09-09', 299593, 59940);
    */

    DECLARE @salesDT DATE;

    --SIMULATES PASSING IN A DATE TO AN SP
    --SET @salesDT = '2014-12-07';

    --SIMULATES A NULL BEING PASSED IN AND USING TODAY
    SET @salesDT = ISNULL(@salesDT, CONVERT(DATE,GETDATE()));

    WITH
    cteMonthlySales AS
    (
        SELECT MONTH([Date]) AS SalesMonth, SUM(Sales) MonthlySales
        FROM SalesTable
        WHERE MONTH([Date]) = MONTH(@salesDT)
        GROUP BY MONTH([Date])
    ),
    cteAnnualSales AS
    (
        SELECT YEAR([Date]) AS SalesYear, SUM(Sales) AS AnnualSales
        FROM SalesTable
        WHERE YEAR([Date]) = YEAR(@salesDT)
        GROUP BY YEAR([Date])
    )
    SELECT CAST(st.Sales AS MONEY) AS Today, CAST(sm.MonthlySales AS MONEY) AS MTD, CAST(ym.AnnualSales AS MONEY) AS YTD
    FROM SalesTable st
        JOIN cteMonthlySales sm ON sm.SalesMonth=MONTH(st.[Date])
        JOIN cteAnnualSales ym ON ym.SalesYear=YEAR(st.[Date])
    WHERE [Date] = @salesDT;


MSSQL Server Upgrade, go for latest or version before that

You're asking for opinions, so here's mine...

Upgrading Sql Server is a labor intensive process with tons of planning and testing.  I like to minimize the number of times I have to do that.  If I were in your shoes, I would definitely opt for 2016 and test, test, test in non-production environments.


Find history of rollbacks on SQL Server 2012

Do you mean ROLLBACK TRANSACTION? Not very easily. You could use DBCC LOG or fn_dblog() to find rollbacks that have happened in the active transaction log. Getting the same information from old log backups requires fn_dump_dblog().

SELECT
    l.name,
    r.*
FROM fn_dblog(NULL, NULL) r
    LEFT OUTER JOIN fn_dblog(NULL, NULL) b
        ON r.[Transaction ID] = b.[Transaction ID]
        AND b.Operation = 'LOP_BEGIN_XACT'
    LEFT OUTER JOIN master.sys.server_principals l
        ON b.[Transaction SID] = l.sid
WHERE r.Operation = 'LOP_ABORT_XACT'

A better option would be to configure either a trace or extended event session to record rollbacks, though this would only help you going forward.


Cleaning up specific domain logins on about 50 servers and therefore the users

I have had a similar task recently. My solution:

Create a temporary stored procedure #sp_helplogins which is a copy of sp_helplogins but returns only the second rowset.
Get logins to delete (in my case by using sp_validatelogins)

DECLARE @Login  sysname, @SQL   NVARCHAR(4000)

CREATE TABLE #BadLogins
(
    [SID]       varbinary(85)
    ,NTlogin    sysname
)

CREATE TABLE #BadUsers
(
    LoginName       VARCHAR(100)
    ,DBName         VARCHAR(100)
    ,UserName       VARCHAR(100)
    ,UserOrAlias    VARCHAR(100)
    ,IsDeleted      INT
)

INSERT #BadLogins
EXEC sp_validatelogins

Get associated users

DECLARE myCursor CURSOR READ_ONLY
FOR
SELECT DISTINCT NTlogin
FROM #BadLogins

OPEN myCursor

FETCH NEXT FROM myCursor INTO @Login

WHILE @@FETCH_STATUS = 0 BEGIN

INSERT #BadUsers
EXEC #sp_helplogins @Login

FETCH NEXT FROM myCursor INTO @Login

END

CLOSE myCursor
DEALLOCATE myCursor

Drop the users

DELETE #BadUsers
WHERE DATABASEPROPERTYEX(DBName, 'Updateability') != 'READ_WRITE'

WHILE EXISTS ( SELECT 1 FROM #BadUsers WHERE IsDeleted = 0 AND UserOrAlias = 'User' ) BEGIN

SELECT TOP 1 @SQL = 'USE ' + DBName + '
DROP USER ' + QUOTENAME(UserName)
FROM #BadUsers
WHERE IsDeleted = 0
    AND UserOrAlias = 'User'
ORDER BY UserName,DBName

EXEC sp_executesql @SQL
--SELECT @SQL

UPDATE u
SET IsDeleted = 1
FROM #BadUsers u
    JOIN (
        SELECT TOP 1 DBName, UserName
        FROM #BadUsers
        WHERE IsDeleted = 0
            AND UserOrAlias = 'User'
        ORDER BY UserName,DBName
    ) d ON d.DBName = u.DBName AND d.UserName = u.UserName
WHERE IsDeleted = 0

END

Notice that I don't handle a case when a user have an owned schema so the users won't be deleted by my script.


So, the xidWarnLimit is 3.747 billon where my current txid_current>70 billion. And strange thing is txid_current is greater than xidWrapLimit which is 3.758 billon.

You are presumably looking at the raw return of txid_current(), which is not a 32-bit value like the XID Limits you are looking at. Instead, it is

  a 64-bit format that is extended with an "epoch" counter so it will not wrap around during the life of an installation.

(ref). You can coax a 32-bit value out of txid_current() which you can compare against your xidWrapLimit, xidStopLimit, etc. like this:

SELECT txid_current() % (2^32)::bigint;

H/T to Erwin for this tip. Remember that these XID values wrap around at 2^32.

For what it's worth, I think that blog post you linked to is excessively complicating this topic. To watch out for XID wraparound, you really just need to check:

SELECT max(age(datfrozenxid)) FROM pg_database;

and raise an alarm if that max. age is over 1 Billion or so. And if it gets close to 2 Billion you are in imminent danger! You can use a tool like check_postgres.pl which will handle this check for you with some configurable thresholds.


MongoDB 3.6 - Journal files are not getting cleaned up and they keep building everyday and use up a lot of disk space

This is a known issue recently discovered in the MongoDB 3.6.3 release when using WiredTiger on Windows: SERVER-33982: Unbounded growth of pre-allocated log files.

This issue will be corrected in the 3.6.4 production release, so upgrading to MongoDB 3.6.4 (or newer) when available should resolve the issue.

In the interim, possible workarounds are:

Disable WiredTiger preallocation in 3.6.3

In your MongoDB config file:

storage:
  wiredTiger:
    engineConfig:
      configString: log=(prealloc=false)

Or, via command-line parameter:

--wiredTigerEngineConfigString=log=(prealloc=false)

Downgrade to MongoDB 3.6.2
Restart your mongod to free up the preallocated space


As answered before, you have loads of different ways of doing this. Here are a few:

External logical replication: projects like Slony, Londiste, Bucardo can replicate the needed objects through triggers;
Extensions like pglogical;
In-core logical replication which is available since Postgres 10
Foreign Data Wrappers: you could use postgres_fdw to make your table readable from a remote server. This means connecting to the other table, not replicating it.
Backup and restore: you could cron a pg_dump and restore of your table from server A to B.
Physical Replication: you could replicate the whole A cluster on cluster B using streaming replication. This could give you a nice disaster recovery solution. On the B server, you could then issue one of the solutions above to a second instance on the server B.
and probably several other ways :-).


How to avoid timeouts during big DML operations

Break the operation into chunks of separate transactions.

BEGIN TRANSACTION;
  INSERT ...
  INSERT ...
  -- maybe 1000 or 2500 of these
COMMIT TRANSACTION;

GO
WAITFOR DELAY '00:00:01';
GO

BEGIN TRANSACTION;
  INSERT ...
  INSERT ...
  -- maybe 1000 or 2500 more of these
COMMIT TRANSACTION;

This will prevent all 55,000 inserts from jamming up your log and operating as a single, blocking transaction. I talked about this methodology to some degree in this blog post even though it talks about more tightly controlled DELETE operations.

If you can, have the file(s) regenerated in a format that makes them easier to BULK INSERT or bcp.


Add data from rows to different columns if more than 1 row have a partial match

Welcome.

Try this.

select name, surname,
    max(prog1) as prog1, max(prog2) as prog2, max(prog3) as prog3
 from
    (select name, surname,
     if(program= 'program1', 'program1', '') as prog1,
     if(program= 'program2', 'program2', '') as prog2,
     if(program= 'program3', 'program3', '') as prog3
    from db1) as tempdb
 group by name, surname;

This is possible only if the three program values are fixed / known prior to the code (itself).

For unknown values of program this is not possible directly. In such case, you may have to move them to another table / temporary table / view by multiplying columns and then assembling the same into one with more columns.


[tran_start_time] - Date and time that the first transaction opened by a session caused a transaction log write to occur.

[start_time] -  For active requests, shows the time the request started |   For sleeping sessions, shows the time the last batch completed

[login_time] -  Shows the time that the session connected


Process to reclaim free space from mongodb ,replication is there

Once and for all: The database repair command is potentially harmful and is advised against

  You should not use repairDatabase for data recovery unless you have no other option.

And

  This command obtains a global write lock and will block other operations until it has completed.

Hardly what you want in a production system. I already answered a similar, though not identical question. However, the same rules apply. Read the part on capacity planning closely.

And honestly: if you have to ask for the procedure, you really, really, really should not even think about executing it. Even when you get a detailed How-To, you will lack any knowledge of potential side effects. If it is a production system, get yourself a DBA. It is worth it.


ALTER DATABASE statement not allowed within multi-statement transaction

No, you're not doing anything wrong. I got the same thing. I solved it by breaking the sample up into multiple scripts and running each section of the script sequentially, in its own query window, instead of as one big script. This worked in my case because I'm always running these samples in an isolated VM (not on a production server!) and transaction handling is unnecessary since I'm the only one here.

Looking at the script again today more closely, there is no transaction handling defined explicitly, but perhaps you pasted the script into a query window that already had an active transaction, or created a new query window that automatically added BEGIN TRANSACTION; / COMMIT TRANSACTION; statements.

I also pointed out a couple of other potential gotchas in this blog post.


Columns count in SELECT query and SQL Server buffer cache

SQL Server stores all the columns for one row together in a single disk page.  (It's more complex than this but for int and char etc. this is more-or-less true.)  To retrieve any column's value the whole page is read into RAM.  So once you have any column available for a given row, all of the columns for that row are available.

There are several buffers / caches involved in responding to a query.  One is the great big page bufferpool where pages read from disk are held in memory so execution plans can act on them.  And yes, if one query causes a page to be loaded into the bufferpool all subsequent queries will use the same page from the bufferpool and not have to suffer the IO overhead.  This is an incidental performance gain, however, and shouldn't be relied upon because SQL Server may choose to evict any page from the bufferpool at any time it needs the space.

Another set of buffering occurs when the output from your query gets sent over your connection to your application.  This is (mostly) on a per-connetion basis so duplicating the work duplicates the resources required (more or less).

Another cost is the optimisations you miss out on when you return all columns.  Covering indexes and "INCLUDES" columns are meaningless.  Every read will have to retrieve the underlying clustered index (or heap) page.  It's a fair bet that SQL Server will not be using specific indexes it might otherwise have chosen because a scan-the-cluster plan is cheaper than an index-and-cluster plan.


You have double DO at the end of the third line.


Where EVENTS history are logged/recorded in MySql?

There is no log for scheduled events, unless they throw exceptions... in which case, you'll find an error written to the standard MySQL "hostname.err" error log.  If there isn't an error, and they aren't still running, then they processed.  Each event gets a new thread id in the processlist each time it fires and the thread is destroyed when the event is done.

One option you could use is creating a table for the purpose, and inserting log entries into that table from within the body of the event, or the procedure that the event calls.

Inside a running event, the CONNECTION_ID() function returns the id of the thread where the event is running, and you can use this function in queries that do your logging, if that's useful, such as inserting a log entry including that value when the event starts, and updating that log entry when the event is almost done.

Another option is a bit of a hack (some would say), but it also works -- you can generate a "warning" in the MySQL error log from within the event or procedure fired by the event by raising a SQL warning at the end of the event.  Note that this only writes to the error log from within an event, and that's because there is no client connected.

SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'hey, this job ran';

On some of my systems where the MySQL error log is monitored by an external process "tailing" the error log file, I have scheduled events whose only purpose is to write a warning like this to the error file periodically, so that the external process never goes more than so many minutes without seeing "something" in the often-quiet error log, to keep it happy that it is properly seeing the correct log file growing.


How to get the schema name of a table of type regclass in PostgreSQL?

Can I just implement this function with e.g. split_part(_tbl::text, '.', 1)?

I'm not sure that would be safe. Instead, I would use,

SELECT nspname
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS ns
  ON c.relnamespace = ns.oid
WHERE c.oid = _tbl;

That is guaranteed to work.

  In writing a function to test if a column col_name exists in a table _tbl, I'd like to extract the table's schema name, which is passed into the function as a regclass parameter (for security??).

So you could do something like,

SELECT nspname
FROM pg_catalog.pg_attribute AS a
JOIN pg_catalog.pg_class AS c
  ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace AS ns
  ON c.relnamespace = ns.oid
WHERE c.oid = _tbl
  AND a.attname = col_name;

Update

If you have a fully-qualified identifier, one with the table name, and schema name inside, You can use parse_ident to parse it safely.


Combine relational DB and Elastic search

Use both.

There is a line that needs to be drawn by you and your team here. SQLSERVER is more expensive than ElasticSearch so when I faced a similar issue it made more sense to spend CPU resources on elasticsearch than on sqlserver.

There's a few things that can make you decide to index your text data in elasticsearch

What kind of load are you looking at ?

A few searches per minute or dozens per seconds ?
This is subjective but again if a large amount of your database resources are spent on this one query you might want to offload that.

Keep your data structured

I find elasticsearch query language to be far less intuitive than SQL. I strongly suggest to have an as-normalized-as-possible version of your data in a standard relational database. Then base your elastic index on that.

Elasticsearch is great at many things but writing complex ad hoc queries with aggregations and/or subqueries isn't one of them.

How do you sync the data then ?

Triggers and queues are what I've been using.

Add a trigger on the table that has data you want to keep track of. Here's what one of the queues I've done looks like.

The trigger logs the action (insert/update/delete) and from there you know what to do in your Elasticsearch index. I've found rebuilding the entire record in elastic wasn't too expensive so this is what I do.

This way, you can take a project with a large code base and index any data you like in elasticsearch without having to do any code changes. Everything is handled by the state of your data in your RDBMS of choice.

Elasticsearch (and all the other nosql/document stores) have amazing use cases but storing relational data as a main database is not one of them. Use relational databases for that.


Using multiple Active Directory accounts to connect to SQL Server

Yes, the most secure means to for authentication is Windows Authentication.  Read more about this topic and the differences here.

As for different applications having different logins, that is going to be the most secure method.  The reason behind this is because each application will most likely require a different set of permissions (I'm assuming a bit here), and instead of having one server principal with a handful of permissions to suit numerous applications, it will be more secure to have each application have a login with permissions tailored to its own needs.  It's all about the Principle of Least Privilege.


Split Multiple Columns Into Multiple Columns by Delimiter

In SQLite, you have to do it the hard way with the instr() and  substr() functions:

SELECT before_semicolon,
       substr(rest, 1, pos - 1) AS after_semicolon1,
       substr(rest, pos + 1)    AS after_semicolon2
FROM (SELECT before_semicolon,
             rest,
             instr(rest || ';', ';') AS pos
      FROM (SELECT substr(data1, 1, pos - 1) AS before_semicolon,
                   substr(data1, pos + 1)    AS rest
            FROM (SELECT data1, instr(data1 || ';', ';') AS pos
                  FROM example)));

It might be a better idea to create a user-defined function like Postgres's split_part().


One-way read-only replica

If one is a read-only replica and the other one is the master, you can and should use WAL archive shipping for this. The master generates WAL and stores it somewhere accessible to both servers. Some like to use WAL-E to store it in S3, but you can just as easily have the replica scp it from the master on demand or whatever.

The replica, whenever it is online, fetches WAL and replays it to catch up with the master. It can also remove WAL from the archive after successful replay if there's only one replica. (Otherwise deciding when to remove WAL is ... hard).

The replica can run read-only queries, but nothing read/write.

Two-way read/write

If you want two-way disconnected sync, then I suggest you start with the chapter in database theory named "El Dorado", then continue reading into "Magic". Everybody wants this, and it does exist, but always with some difficult compromises. The biggest: how do you deal with conflicts where both modify the same record? How do you handle read-modify-write cycle inconsistencies when disconnected?

You need a database plugin that has application specific merge logic, or to write your application very carefully so it can survive last-update-wins merge logic. If you're doing that, and really keen to try very experimental code, keep an eye on http://wiki.postgresql.org/wiki/BDR_User_Guide, an in-development two-way logical replicaiton feature for PostgreSQL. Otherwise, you may have some success with Slony-I or Bucardo, external replication tools for Pg.

If at all possible, design you app not to require this, or have your app deal with the synchronization at a higher level using application specific logic, where you record changes (with triggers or with app logicl) and then replay them using your application's knowledge of what the data means.


Does using lower Isolation Level improve OLTP performance?

SQL Server 2008 Solution

Seeing as you are thinking of duplicating the database into two instances, changing the isolation level from the default Read committed isolation level to Read uncommitted will not have much of an impact.

Reference: Isolation Levels in the Database Engine

The inserts occur on instance1.database which does not have any active (SELECT) queries.

Transferring the data from instance1.database to instance2.database (Reporting database) will have a small impact during the night. Otherwise the users are not impacted by the INSERTs occurring on instance1.database during normal office hours while accessing instance2.database.

No need to change the isolation level.

Alternate Solution : Availability Groups

You might be better off with a newer SQL Server version (2012 or greater) and Availability Groups. This way you could have your live data sent to the AG and you just configure the second replica to be READ-ONLY, which you then query for reporting purposes. This is documented in the MSDN article:

Configure Read-Only Access on an Availability Replica (SQL Server)

Here is an overview of the AlwaysOn Availability Groups (SQL Server)


Postgres multi tables vs single table performance

Do you find yourself writing lots of queries in which you combine rows from many of those tables together with UNION or UNION ALL?  If not, then the current setup is probably more efficient than the single table.

Once you have tens of thousands of tables, you can have scalability problems when doing things like backup, dump, and restore, and upgrades.  The nature of those problems would depend on which PostgreSQL version you are using, which tools you are using to do those things, and the details of your database objects.


C# ASP.net MVC - Which way is more efficient and quicker to store data into database?

Compilation of comments
@Sole DBA Guy pointed out:

I'd say method 1 is what most DBAs will say is better of the two options because if you decide to add Banana, it's just another row for the first option but for the second option, it would be a new column. However, why does it need a loop to add the values? Is the loop to get the values for each fruit? Do the same fruit values not exist in the database already?

@MDCCL mentioned:

I agree with @Sole Dba Guy. From a modeling perspective, as usual, it depends on different considerations. For instance, if you want to store an entity type called, perhaps, Fruit which, say, has the attributes FruitNumber, Name and QuantityAvailable, then your first procedure is certainly more suitable and extensible. You have to determine with precision the kinds of things that exist in your context of interest so that you can define which option is more convenient.

Then, I replied to his comment with the following question:

Wouldn't the first method take up more processing time or CPU usage? Since it has to do {sql.add()} again and again due to being in a for loop rather than using {sql.add()} once like the second method.

To which @MDCCL responded:

Maybe, or maybe not, honestly, it is hard to tell accurately without knowing the whole context. That is, in part, an application program question, but it certainly has database implications. Since you would be executing multiple INSERTs, perhaps some kind of batch operation might be helpful in your case. Application programming and relational database implementation are different disciplines so, as a mere suggestion, you should read about relational theory, data modeling and Structured Query Language (particularly the Transact-SQL dialect that is used in Microsoft SQL Server, the platform that, according to your tag, you are currently employing).

Finally, @srutzky added:

The query string might have multiple instances of the same variable? The example shows "apple", "orange", and "grape" showing up twice, plus the mention of the for loop. How likely is it that other fruits will be added, over the next few years at least? If fruits show up multiple times in the query string, what's your plan for doing #2 for ones with multiple entries? Aggregate them into a single entry? Is the relationship between them in terms of when the data was submitted important? Option 1 doesn't have that, outside of the time. Option 1 also repeats fruit names instead of using FruitID.


Confused on how a value gets added with the DEFAULT constraint

If your column definition does not have default constraint and it is nullable, the nullable field will have null mark, when you add a row even when you specify DEFAULT keyword for your insert value. For example for the following table definition

 CREATE TABLE Test(
    ColA  [nvarchar](50) NOT NULL,
    COLB [nvarchar](50) NULL)

The first three insert statements will insert NULL mark for ColB. Only the last one will have ColB value assigned.

 insert into Test(ColA, ColB) values ('a', DEFAULT);
 insert into Test(ColA, ColB) values ('b', NULL);
 insert into Test(ColA) values ('c');
 insert into Test(ColA, ColB) values ('d', 'e');

Whereas for the following table definition with DEFAULT constraint.

CREATE TABLE Test(
    ColA  [nvarchar](50) NOT NULL,
    COLB [nvarchar](50) NULL DEFAULT 'x')

Only the second statement below will insert NULL mark for ColB. The first and the third statements will assign 'x' as the DEFAULT value.

 insert into Test(ColA, ColB) values ('a', DEFAULT);
 insert into Test(ColA, ColB) values ('b', NULL);
 insert into Test(ColA) values ('c');
 insert into Test(ColA, ColB) values ('d', 'e');


PostgreSQL - Truncate a table on cascade and reset all hierarchic sequences with 1

The TRUNCATE statement has an additional option RESTART IDENTITY which resets the sequences associated with the table columns.

TRUNCATE sch.mytable RESTART IDENTITY CASCADE;

If CASCADE is defined, then the sequences of all affected tables are reset.


Creating a flexible table containing prices and periodes

Here's one possibility.

The idea is that every time the price for a room changes, a PricingChange row would be added. From that date on, until there is another PricingChange row for the same RoomType, the price for that RoomType would be set to that row's price.

Sample data for this model:

With this sample data, Single rooms ware set at the price of $100 for June 1 2015, $50 for June 2 2015 to June 4 2015, and then back to $100 from June 5 2015 onwards. Double rooms are set to the price of $200 from June 1 2015 onwards.


Where can I find good literature for beginners?

It sounds like you are looking for something on an overview level rather than a practical level.  If you are interested in learning about Oracle databases from a practical level, there is no better place to start than the Oracle Concepts Guide.


Is it possible to do a ALL SERVER DML Trigger?

You are not going to be able to create a server-wide trigger, because it has to exist in the same database that the DML changes are occurring in.

Having self-deleting code sounds a bit problematic as well.

I think you are going to have to have a process in place that creates the triggers when a new database is attached, and removes them when you are finished with them.  This is likely going to be a manual process.


It may be miserably slow (until 5.6.7 or MariaDB 5.5) because of the following.  Let me give a simplified version:

SELECT ... FROM ( SELECT ... ) a
JOIN ( SELECT ... ) b ON a.x = b.x

There are (and cannot be) indexes on either of the temp tables (a, b).  If there are, say, 10K rows in each of a and b, then the JOIN will have to do 100M compares (10K*10K).  Slow.

If one of the subqueries boils down (via GROUP BY, LIMIT, or...) to only a few rows, then performance won't be too bad.

As a general rule, avoid subqueries.  Some subqueries are ok -- namely those that shrink the number of rows (GROUP BY, etc).  But the JOIN problem above still stands.

The general 'fix' for a subquery is to turn it into a JOIN.  But that does not apply here because most have GROUP BY.  So... Consider CREATEing TEMPORARY TABLEs with indexes for some or all of the subqueries.

Another performance killer is OR.  Generally it makes it impossible to use an index on the fields in the OR.  Sometimes the fix is to do two SELECTs and UNION ALL/DISTINCT them.

Wade through some of those suggestions, then come back for more advice.  Bring SHOW CREATE TABLE next time.


Restarting SQL Server Agent on a cluster installation

Set the resource/service for SQL Agent as offline via the failover cluster manager, and then back online.


Unable to apply order by clause on distinct select in Oracle

You can't order by columns that aren't selected when using DISTINCT, or their alias.

To keep your order with a substring and another column, you will have to calculate them first (without DISTINCT), then just display the name while ordering by the other columns behind the curtain.

SELECT
    F.name
FROM
    (
        SELECT
            S.name name,
            Substr(S.name, -3, 3) substrName,
            MAX(S.ID) ID
        FROM
            Students S
        WHERE
            S.marks > 75
        GROUP BY
            S.name
    ) F
ORDER BY
    substrName,
    ID ASC


Should date formats be specified in SQL statements?

Because '2012/12/1' in the US is 11 months after the same string date in Europe.

Allowing implicit conversions means you are at the mercy of location settings.

If you can name a business where 11 months is an acceptable margin of error I'll be impressed.


I have instead edited my PHP code that handles this data:

//Show the location in the truck description
if ($mainrow[24] == "0") {
$mainrow[16] = $mainrow[16] . " Location: Edmonton";
} else if ($mainrow[24]=="1") {
$mainrow[16] = $mainrow[16] . " Location: Vancouver";
}

where $mainrow[24] is the "Inventory" column, and $mainrow[16] is the Description column.


You need to specify the data type of the parameters.


For your example, all you want is

SELECT
  min(pk),
  id,
  array_agg(value ORDER BY value)
FROM foo
GROUP BY id;

 min  | id  |    array_agg
------+-----+------------------
 pk1  | id1 | {A1,A2,A3,A4,A5}
 pk10 | id2 | {B1,B2,B3,B4,B5}
(2 rows)

If you need the array_agg to be unwrapped, you can do,

SELECT pk, id, a[1] AS v1, a[2] AS v2, a[3] AS v3, a[4] AS v4, a[5] AS v5
FROM (
  SELECT
    min(pk),
    id,
    array_agg(value ORDER BY value)
  FROM foo
  GROUP BY id
) AS t(pk, id, a);

  pk  | id  | v1 | v2 | v3 | v4 | v5
------+-----+----+----+----+----+----
 pk1  | id1 | A1 | A2 | A3 | A4 | A5
 pk10 | id2 | B1 | B2 | B3 | B4 | B5

Note, in all future examples please don't use id1 and pk1. You're sample values should preferably be just 1 and 2. As you can see here, we're struggling with getting pk10 instead of pk6 and that's a problem only because of the sort order.


SQL Management Studio user permissions with multiple databases

Privilege to backup the database part is straight forward.

You create 2 database:

CREATE DATABASE dbuser1
GO
CREATE DATABASE dbuser2
GO

Create 2 login:

CREATE LOGIN loginuser1 WITH PASSWORD ='Aa123456789!@#'
GO
CREATE LOGIN loginuser2 WITH PASSWORD ='Aa123456789!@#'
GO

Create 2 user from the login created above:

USE [dbuser1]
GO
CREATE USER [user1] FOR LOGIN [loginuser1]
GO
USE [dbuser2]
GO
CREATE USER [user2] FOR LOGIN [loginuser2]
GO

Make the users member of db_backupoperator in corresponding databases:

USE [dbuser1]
GO
EXEC sp_addrolemember db_backupoperator, [user1]
GO
USE [dbuser2]
GO
EXEC sp_addrolemember db_backupoperator, [user2]
GO

Now user1 can only take backup up of dbuser and user2 for dbuser2. If user2 attempt to backup dbuser1 following error message will be issued.
Msg 916, Level 14, State 1, Line 1

  The server principal "user2" is not able to access the database
  "user1" under the current security context. Msg 3013, Level 16, State
  1, Line 1 BACKUP DATABASE is terminating abnormally.

For restore you have to add the users to dbcreator fixed server role.
Then user2 can restore dbuser1 with REPLACE option.  That is something you do not want.

There is few solution discussed here and hope you can implement one of these for restricting restore.


How to copy migrate data to new tables with identity column, while preserving FK relationship?

Here's a way that scales easily to three related tables.

Use MERGE to insert the data into the copy tables so that you can OUTPUT the old and new IDENTITY values into a control table and use them for related tables mapping.

The actual answer is just two create table statements and three merges. The rest is sample data setup and tear down.

USE tempdb;

--## Create test tables ##--

CREATE TABLE Customers(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [Name] NVARCHAR(200) NOT NULL
);

CREATE TABLE Orders(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [CustomerId] INT NOT NULL,
    [OrderDate] DATE NOT NULL,
    CONSTRAINT [FK_Customers_Orders] FOREIGN KEY ([CustomerId]) REFERENCES [Customers]([Id])
);

CREATE TABLE OrderItems(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [OrderId] INT NOT NULL,
    [ItemId] INT NOT NULL,
    CONSTRAINT [FK_Orders_OrderItems] FOREIGN KEY ([OrderId]) REFERENCES [Orders]([Id])
);

CREATE TABLE Customers2(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [Name] NVARCHAR(200) NOT NULL
);

CREATE TABLE Orders2(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [CustomerId] INT NOT NULL,
    [OrderDate] DATE NOT NULL,
    CONSTRAINT [FK_Customers2_Orders2] FOREIGN KEY ([CustomerId]) REFERENCES [Customers2]([Id])
);

CREATE TABLE OrderItems2(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [OrderId] INT NOT NULL,
    [ItemId] INT NOT NULL,
    CONSTRAINT [FK_Orders2_OrderItems2] FOREIGN KEY ([OrderId]) REFERENCES [Orders2]([Id])
);

--== Populate some dummy data ==--

INSERT Customers(Name)
VALUES('Aaberg'),('Aalst'),('Aara'),('Aaren'),('Aarika'),('Aaron'),('Aaronson'),('Ab'),('Aba'),('Abad');

INSERT Orders(CustomerId, OrderDate)
SELECT Id, Id+GETDATE()
FROM Customers;

INSERT OrderItems(OrderId, ItemId)
SELECT Id, Id*1000
FROM Orders;

INSERT Customers2(Name)
VALUES('Zysk'),('Zwiebel'),('Zwick'),('Zweig'),('Zwart'),('Zuzana'),('Zusman'),('Zurn'),('Zurkow'),('ZurheIde');

INSERT Orders2(CustomerId, OrderDate)
SELECT Id, Id+GETDATE()+20
FROM Customers2;

INSERT OrderItems2(OrderId, ItemId)
SELECT Id, Id*1000+10000
FROM Orders2;

SELECT * FROM Customers JOIN Orders ON Orders.CustomerId = Customers.Id JOIN OrderItems ON OrderItems.OrderId = Orders.Id;

SELECT * FROM Customers2 JOIN Orders2 ON Orders2.CustomerId = Customers2.Id JOIN OrderItems2 ON OrderItems2.OrderId = Orders2.Id;

--== ** START ACTUAL ANSWER ** ==--

--== Create Linkage tables ==--

CREATE TABLE CustomerLinkage(old INT NOT NULL PRIMARY KEY, new INT NOT NULL);
CREATE TABLE OrderLinkage(old INT NOT NULL PRIMARY KEY, new INT NOT NULL);

--== Copy Header (Customers) rows and record the new key ==--

MERGE Customers2
USING Customers
ON 1=0 -- we just want an insert, so this forces every row as unmatched
WHEN NOT MATCHED THEN
INSERT (Name) VALUES(Customers.Name)
OUTPUT Customers.Id, INSERTED.Id INTO CustomerLinkage;

--== Copy Detail (Orders) rows using the new key from CustomerLinkage and record the new Order key ==--

MERGE Orders2
USING (SELECT Orders.Id, CustomerLinkage.new, Orders.OrderDate
FROM Orders
JOIN CustomerLinkage
ON CustomerLinkage.old = Orders.CustomerId) AS Orders
ON 1=0 -- we just want an insert, so this forces every row as unmatched
WHEN NOT MATCHED THEN
INSERT (CustomerId, OrderDate) VALUES(Orders.new, Orders.OrderDate)
OUTPUT Orders.Id, INSERTED.Id INTO OrderLinkage;

--== Copy Detail (OrderItems) rows using the new key from OrderLinkage ==--

MERGE OrderItems2
USING (SELECT OrderItems.Id, OrderLinkage.new, OrderItems.ItemId
FROM OrderItems
JOIN OrderLinkage
ON OrderLinkage.old = OrderItems.OrderId) AS OrderItems
ON 1=0 -- we just want an insert, so this forces every row as unmatched
WHEN NOT MATCHED THEN
INSERT (OrderId, ItemId) VALUES(OrderItems.new, OrderItems.ItemId);

--== ** END ACTUAL ANSWER ** ==--

--== Display the results ==--

SELECT * FROM Customers2 JOIN Orders2 ON Orders2.CustomerId = Customers2.Id JOIN OrderItems2 ON OrderItems2.OrderId = Orders2.Id;

--== Drop test tables ==--

DROP TABLE OrderItems;
DROP TABLE OrderItems2;
DROP TABLE Orders;
DROP TABLE Orders2;
DROP TABLE Customers;
DROP TABLE Customers2;
DROP TABLE CustomerLinkage;
DROP TABLE OrderLinkage;


Is it possible to target a specific instance in a clustered environment with group policy?

Community Wiki answer generated from a comment on the question by Steve Mangiameli

It is, but you'll need to be a little creative.  Remember, you will be setting two different permissions sets - OS and SQL Server.  Hopefully, each instance has install and DBs set in different directories, for each instance.  OS level permissions should be directed for the disks/directories for that instance.

SQL server permissions are relatively simple at that point.


How subscriber table updated during snapshot replication?

Is it really how it is supposed to work ?

Yes this how it is supposed to work. You only run snapshot at the beginning of  setting up transactional replication. Unless you are out of sync due to any error you do not need to run snapshot again. If you are running snapshot replication ONLY you can control when you want to resync all your articles and nothing should connect to subscriber while snapshot is being applied.

From books online:

  Snapshot replication is most appropriate when data changes are
  substantial but infrequent. For example, if a sales organization
  maintains a product price list and the prices are all updated at the
  same time once or twice each year, replicating the entire snapshot of
  data after it has changed is recommended. Given certain types of data,
  more frequent snapshots may also be appropriate. For example, if a
  relatively small table is updated at the Publisher during the day, but
  some latency is acceptable, changes can be delivered nightly as a
  snapshot.

  Snapshot replication has a lower continuous overhead on the Publisher
  than transactional replication, because incremental changes are not
  tracked. However, if the dataset set being replicated is very large,
  it will require substantial resources to generate and apply the
  snapshot. Consider the size of the entire data set and the frequency
  of changes to the data when evaluating whether to utilize snapshot
  replication.

When is a subscription available; when can the subscription database be used?

  A subscription is available after the snapshot has been applied to the
  subscription database. Even though the subscription database is
  accessible prior to this, the database should not be used until after
  the snapshot has been applied. Use Replication Monitor to check the
  status of snapshot generation and application.

Are there settings somewhere to avoid such behaviour ?

If you are sure that your schema and data is already in sync or you only want future changes to get replicated you can use following option to avoid snapshot. I used this many time during upgrade with downtime.

@sync_type=replication support only

  Provides automatic generation at the Subscriber of article custom
  stored procedures and triggers that support updating subscriptions, if
  appropriate. Assumes that the Subscriber already has the schema and
  initial data for published tables. When configuring a peer-to-peer
  transactional replication topology, ensure that the data at all nodes
  in the topology is identical. For more information, see Peer-to-Peer
  Transactional Replication.

  Not supported for subscriptions to non-SQL Server publications.


sqlcmd returning literal 'NULL' for null columns when outputting to CSV

I cannot reproduce the problem you're seeing.

I'm using a SQL Server 2008 R2 instance, connecting to it via sqlcmd.exe using the following test command, which I've wrapped for readability:

.\sqlcmd -S "My2008R2Instance" -E -d tempdb -Q "set nocount on;
   create table #t (col1 varchar(10) null, col2 varchar(10) null);
   insert into #t(col1, col2) values (null, null);
   select coalesce(col1, '') as col1, coalesce(col2, '') as col2
   from #t;" -o C:\temp\test.csv -s "," -h -1

The output consists of a single line file with a comma between the two columns:

          ,

As you can see, there are no 'NULL' values returned; they've been replaced by the zero-length-string, as required by the COALESCE function.


Is it possible you've got two processes attempting to do this at almost the same time?  If this is a web application, could you have a site user who is double-click happy?

Process #1 "examine existing users"
Process #1 "remove all users"
Process #2 "examine existing users" (no users would found)
Process #1 "insert new users and put existing users back"
Process #2 "remove all users"
Process #2 "insert new users and put existing users back (but no existing users were found)"


Left outer join not returning all rows in a grouping query

The WHERE o.date ... condition makes the outer join behave like an inner join, cutting out any rows of peoplethat don't have at least one matching row in orders.

You'll have to move the condition about o.date from the WHERE clause to the joining ON:

SELECT p.id, p.name, coalesce(sum(o.price), 0.00) AS total
FROM people p
  LEFT OUTER JOIN orders o
    ON  p.id = o.person_id
    AND o.date BETWEEN date '2014-09-01' AND date '2014-09-30'
GROUP BY p.id;

Test in: SQLFiddle


Given the new information (you should probably edit the question, not add an answer, but I assume you are not entitled to). First I don't understand the database design, do you always get 4 affected devices per fault? Anyhow, you can transpose your table like:

select affected_device_1 from faults
union all
select affected_device_2 from faults
union all
select affected_device_3 from faults
union all
select affected_device_4 from faults

I won't be efficient but it is the best I can think of. From there you can count the number of occurrences for each device:

select affected_device, count(1)
from (
    select affected_device_1 as affected_device from faults
    union all
    select affected_device_2 as affected_device from faults
    union all
    select affected_device_3 as affected_device from faults
    union all
    select affected_device_4 as affected_device from faults
) as T
group by affected_device;

Note that if a fault contains the same device twice the count will be wrong.


Looking for a database that supports fulltext but does not lock table on insert or update

With the current MySQL version this is not possible. Apparently the next version (5.6) will support full text indexing on InnoDB tables. I have no idea when that version will officially be released.

You might also want to consider an upgrade to PostgreSQL which has pretty decent full text indexing (I haven't really used that, but it appears it has some additional features like stemming that MySQL does not have).

And PostgreSQL copes very well with concurrent reads and writes.


Is it normal that occasionally Page Life Expectancy drops to 0 and queries start giving timeouts in SQL Server?

If your laptop has 4 gigs that means that SQL Server probably has access to 500 megs of RAM at best.

PLE tells you how long SQL Server is able to keep data in memory. If there's no load on the SQL Server then the PLE will just keep climbing. Once you put load on it again the PLE will drop as the SQL Server needs to read data from disk. The more data that it needs to load from disk the longer the queries will take.

If your disk is slow, which it probably is in your laptop the queries which hit the disk will just run slower as you want for the disk to spin.

If you want good performance on your laptop you'll want to ensure that SQL Server has enough RAM to ensure that the working set of your data sits in memory and not on disk. The first time it is accessed it'll need to be loaded from disk but after that you want it to stay in memory. You'll probably want an SSD as well so that the disk is fast enough.


Is there a SQL Server 2008 R2 specific target for SSDT projects?

A 2008 and 2008R2 user database is schema compatible, hence one version for both in SSDT. In other words, there aren't any schema objects that you could add to a 2008R2 SSDT model that couldn't be created in 2008.

This isn't the same as either database version or database compatibility level.

Database version:

  The database version is a number stamped in the boot page of a
  database that indicates the SQL Server version of the most recent SQL
  Server instance the database was attached to. The database version
  number does not equal the SQL Server version.

Database compatibility level:

  The database compatibility level determines how certain database
  behaviors work. For instance, in 90 compatibility, you need to use the
  OUTER JOIN syntax to do an outer join, whereas in earlier
  compatibility levels, you can use *= and =*.


What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

If the procedure just returns one result set and the ad hoc distributed queries option is enabled.

SELECT *
INTO #T
FROM OPENROWSET('SQLNCLI',
                'Server=(local)\MSSQL2008;Trusted_Connection=yes;',
                 'SET FMTONLY OFF;EXEC sp_who')

Or you can set up a loopback linked server and use that instead.

EXEC sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                        @provider = 'SQLNCLI', @datasrc = @@servername

SELECT *
INTO  #T
FROM OPENQUERY(LOCALSERVER,
               'SET FMTONLY OFF;
               EXEC sp_who')


I may be wrong, but I don't think this is possible unless you restrict access to the data to be via stored procedures - then the procedure can trigger audit mechanism that you can expose to the users. You would also need polling of the audit if both users need to be aware.


I would suggest NOT to use Designer in creating key constraints or doing any DDL / DML operations.

Best is to use T-SQL - much flexible, more options, more powerful and you can automate many things with it. There is a learning curve, but its worth learning TSQL rather than keep using GUI.

Also, make sure you are using the latest version of SSMS - sql server 2014. Download it from here.

Read up : Managing SQL Server via Management Studio vs TSQL Commands

e.g. From BOL

ALTER TABLE dbo.DocExc
   ADD ColumnD int NULL
   CONSTRAINT CHK_ColumnD_DocExc -----> This is your constraint
   CHECK (ColumnD > 10 AND ColumnD < 50);
GO


I would suggest something different than you have here- a separate table to record the student_class relation:

| student_id | class | date       |
-----------------------------------
| 2          | 7     | 2013-10-24 |

This would have one record for a student where a class was attended. For each class and date for which there is no record, it can be seen that the student did not attend.

If you want to further normalize the design, I would suggest instead three new tables:

Student_Class: (to determine enrolled students)

| class | student_id     |
--------------------------
| 1     | 2              |

Class_Date: (class_date_id surrogate key for pk(class, date)) (to determine dates classes  were held)

| class_date_id | class | date       |
-----------------------------------
| 1             | 7     | 2013-10-24 |

Student_Class_Date: (to determine which students attended which class period)

| class_date_id | student_id     |
----------------------------------
| 1             | 2              |

This would allow you to easily define which classes were held on which date, easy updating etc.

Edit: Added additional detail with student_class implied relation from question context


Is it safe to use pt-online-schema-change in a multimaster environment?

The logic is more complex.  (Caveat:  This is not a definitive Answer to the Question, just more insight.)

Generally (probably in this case), the triggers should be only on the one machine.
RBR replicates the effect of inserts/etc, not the actual statements.  That is what is needed on the Slave (aka other Master).
Are you 'writing' to both Masters?  If so, that is a recipe for extra problems; I recommend you stop writing to the other Master while running pt-osc.

Also look into gh-ost, which uses the binlog in tricky ways.  (Obviously, you have the binlog turned on.)


How to transfer data from denormalized table to normalized tables in Oracle?

General

In general, you need to fill out the table in hierarchical order (parent, child, grandchild, etc).  The best way to do that is using SELECT DISTINCT.

This works very well if the text value (eg class_name) is UNIQUE.  This way, you can JOIN your source data to get the parent Id ( class_id ) for your child tables.

In some cases, as the process gets more complicated, you may want to consider creating some staging tables. These are tables whose purpose in life is to hold partially processed data temporarily so that you can review it before going onto the next step.

Your Specific

First, I am assuming that one and only one of the Category columns will have a value of 1.

First thing you need to do is UNPIVOT all of your data into a staging table so that you can use @Akina's method.

with data as (
SELECT
  'Sally' AS first_name
 ,'Biology' AS class_name
 ,0 AS overall
 ,0 AS hw
 ,1 AS test
 ,0 AS quiz
 ,0 AS project
 ,95 AS val
FROM dual
)
select *
from data
UNPIVOT (
  is_used
  for Category in ( OVERALL,HW,TEST,QUIZ,PROJECT)
)
WHERE IS_USED = 1;

This will return a row like this

Name  | Class | Val | Category | IS_USED
Sally | Biology | 95 | TEST | 1

Now you can

SELECT DISTINCT student_name to get individual students for
Student.
SELECT DISTINCT class_name to get individual Classes for each
Class.
SELECT DISTINCT student_name, class_name to get everyone's
ClassEnrollment

You'll need to join to Class on class_name to get class_id
You'll need to join to Student on student_name to get student_id

Finally, after you JOIN all the tables together, you can record
everyone's Grades

This assumes that you have clean data.  @Akina's method seems to assume your data is not clean (which is usually the case)


Try this

select *
from Campaign
where CampaignId = 3001626
AND Company LIKE '%74108%'

I suspect you will get more than one row


I am not sure what you mean by

  Bindings will be done in another function.

You have an EXECUTE statement that is going to invoke your query right there and then, and that statement is not providing parameter values for your :name parameters. That is what the error message is about.

Your EXECUTE statement must have a USING clause, as per the manual:

EXECUTE stmt
USING @Id, @uId, @name, @address;

where @Id, @uId, @name, @address would probably be the names of arguments of the stored program containing the script in your question.

And then you will also need to address the issues mentioned by Julien Vavasseur and ypercubeᵀᴹ:

revisit the number of values vs the number of columns in the INSERT statement (and correct the USING clause above if necessary);
make sure your INSERT statement is syntactically correct (no missing spaces between its various tokens where required).


Does SQL Server care about the location of accounts in Active Directory?

No SQL Server doesn't care where in the OU the account and/or groups are.  Everything is done via SIDs, or in the case of the service accounts with the actual username and password to startup the service.


How can I set maxIdleTimeMS by command line or mongo-shell?

maxIdleTimeMS is applicable to MongoDB drivers and is managed on client side. It has nothing to do with MongoDB server so can't be set inside shell


Short answer

No

Long Answer

The WITH clause, also known as CTE ( Common Table Expressions ) is evaluated first. If there are multiple CTE's the are evaluated in order of the appearance.

The resultset is kept in something like an "in memory temporary table" without any index on it.

Unless there is a very good reason, i would suggest to avoid CTE's as they are optimization fences ( for the query planner).


How do I get the current and next greater value in one select?

Doing a JOIN is one thing you might need.

SELECT l.id, l.time, r.time FROM
    idtimes AS l LEFT JOIN idtimes AS r ON l.id = r.id

I suppose the outer join is deliberate, and you want to be getting nulls.  More on that later.

WHERE l.time < r.time ORDER BY l.id ASC, l.time ASC;

You only want the r. row that has the lowest (MIN) time that is higher than the l.time.  That is the place where you need subquerying.

WHERE r.time = (SELECT MIN(time) FROM idtimes r2 where r2.id = l.id AND r2.time > l.time)

Now to the nulls.  If "there is no next-higher time", then the SELECT MIN() will evaluate to null (or worse), and that itself never compares equal to anything, so your WHERE clause will never be satisfied, and the "highest time" for each ID, could never appear in the result set.

You solve it by eliminating your JOIN, and moving the scalar subquery into the SELECT list :

SELECT id, time,
    (SELECT MIN(time) FROM idtimes sub
        WHERE sub.id = main.id AND sub.time > main.time) as nxttime
  FROM idtimes AS main


In PLPGSQL assign a variable based on result of SQL function

While you can use SELECT INTO, it's simpler to use the assignment operator := (or just =) to assign a single value:

_city := split_part(_location, ', ', 2);

EXECUTE would only be needed for dynamic SQL (building and executing SQL strings dynamically). Not for a simple assignment.

Demo

CREATE OR REPLACE FUNCTION f_dummy(_location text, OUT _city text) AS
$func$
BEGIN
   _city := split_part(_location, ', ', 2);
END
$func$  LANGUAGE plpgsql;

SELECT f_dummy('main street, anytown, KS');  -- 'anytown'

db<>fiddle here

Related:

The forgotten assignment operator “=” and the commonplace “:=”


What is SQL Server doing in the background once a Backup/Restore is complete?

Database restore process is performed in 3 steps:

copy -Copy data from the backup
redo - commit completed transactions in T-Log
Undo - Rollback incomplete transactions

Restore process doesn't perform any maintenance tasks like index rebuild etc.

  When I run a backup or restore through the SSMS GUI, the dialog shows
  Executing with 20% increments rather than every 1% tick.

If you have used T-SQL statements to backup your database, you would have been aware of STATS parameter. It take int as input and prints what % of the backup have been processed.

Ex:-

If we use STATS=5 then it'll print restore status after every 5% completion. Like

5%
10%
15%
..

Post database restore it's good practice to execute DBCC CHECKDB. I would prefer to use PHYSICAL_ONLY.

When a log is restored, it also takes some time to zero out the log file.  If the log file is large, this can take several minutes.