Does my table violate normalization rules?

don't add more stat columns unless you are absolutely sure the required # of columns wont grow, tables tend to get unmanageable that way.

if you need to work with the individual stats in your comma separated text field, you probably should break them off into their own table where each row has 1 stat, make  guid+statid your primary key and then you can make the stat column something more reasonable like a varchar (20). This might take up a little more diskspace but it should be faster working with individual stats...


SQL server 2000 transactions with the same time

It won't be a bug: it's a flaw in your logic.

If you're looping, then it's possible that you are opening transactions on top of another transactions. That is, @@TRANCOUNT keeps incrementing. You may be incorrectly looping on say, a client timeout, that doesn't actually disconnect.

In your loop, in client code, you should test for an open transaction and rollback. Or use SET XACT_ABORT ON to force full rollbacks. See https://stackoverflow.com/questions/917773/do-i-really-need-to-use-set-xact-abort-on/919279#919279 for more

It's hard to be more precise at this stage.


how to show some rows in multiple columns and one row?

One potential way of doing this would be with a common-table-expression combined with a ROW_NUMBER() aggregate to identify each owner as owner #1 to #12.

I solved it like this:

IF OBJECT_ID('tempdb..#land') IS NOT NULL
DROP TABLE #land;
CREATE TABLE #land
(
    lid INT NOT NULL IDENTITY(1,1)
    , name VARCHAR(30) NOT NULL
    , meter VARCHAR(30) NOT NULL
    , address VARCHAR(30) NOT NULL
);

IF OBJECT_ID('tempdb..#owner') IS NOT NULL
DROP TABLE #owner;
CREATE TABLE #owner
(
    ownerid INT NOT NULL IDENTITY(1,1)
    , name VARCHAR(30) NOT NULL
    , family VARCHAR(30) NOT NULL
    , mobile VARCHAR(30) NOT NULL
    , lid INT NOT NULL
);

INSERT INTO #land (name, meter, address)
VALUES ('port', 'meter1', 'address1')
    , ('star', 'meter2', 'address2');

INSERT INTO #owner (name, family, mobile, lid)
VALUES ('owner1', 'family1', 'mobile1', 1)
    , ('owner2', 'family2', 'mobile2', 1)
    , ('owner3', 'family3', 'mobile3', 1)
    , ('owner4', 'family4', 'mobile4', 1)
    , ('owner5', 'family5', 'mobile5', 1)
    , ('owner6', 'family6', 'mobile6', 1)
    , ('owner7', 'family7', 'mobile7', 1)
    , ('owner8', 'family8', 'mobile8', 2)
    , ('owner9', 'family9', 'mobile9', 2)
    , ('owner10', 'family10', 'mobile10', 2)
    , ('owner11', 'family11', 'mobile11', 2)
    , ('owner12', 'family12', 'mobile12', 2)
    , ('owner13', 'family13', 'mobile13', 2)
    , ('owner14', 'family14', 'mobile14', 2);

;WITH owners AS
(
        SELECT o.*
            , rn = ROW_NUMBER() OVER (PARTITION BY o.lid ORDER BY o.ownerid)
        FROM #owner o
)
SELECT *
FROM #land l
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 1) t1(lid1, name1, family1, mobile1) ON t1.lid1 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 2) t2(lid2, name2, family2, mobile2) ON t2.lid2 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 3) t3(lid3, name3, family3, mobile3) ON t3.lid3 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 4) t4(lid4, name4, family4, mobile4) ON t4.lid4 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 5) t5(lid5, name5, family5, mobile5) ON t5.lid5 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 6) t6(lid6, name6, family6, mobile6) ON t6.lid6 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 7) t7(lid7, name7, family7, mobile7) ON t7.lid7 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 8) t8(lid8, name8, family8, mobile8) ON t8.lid8 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 9) t9(lid9, name9, family9, mobile9) ON t9.lid9 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 10) t10(lid10, name10, family10, mobile10) ON t10.lid10 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 11) t11(lid11, name11, family11, mobile11) ON t11.lid11 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 12) t12(lid12, name12, family12, mobile12) ON t12.lid12 = l.lid

It's not a particularly elegant or efficient way of solving this problem, but it seems to do what you want.

This is sample output (it's wider than this in reality, I just didn't want to include a super-wide image because it won't display well).

The select statement updated to not show all columns is:

;WITH owners AS
(
        SELECT o.*
            , rn = ROW_NUMBER() OVER (PARTITION BY o.lid ORDER BY o.ownerid)
        FROM #owner o
)
SELECT l.lid
    , l.name
    , l.meter
    , l.address
    , name1, family1, mobile1
    , name2, family2, mobile2
    , name3, family3, mobile3
    , name4, family4, mobile4
    , name5, family5, mobile5
    , name6, family6, mobile6
    , name7, family7, mobile7
    , name8, family8, mobile8
    , name9, family9, mobile9
    , name10, family10, mobile10
    , name11, family11, mobile11
    , name12, family12, mobile12
FROM #land l
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 1) t1(lid1, name1, family1, mobile1) ON t1.lid1 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 2) t2(lid2, name2, family2, mobile2) ON t2.lid2 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 3) t3(lid3, name3, family3, mobile3) ON t3.lid3 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 4) t4(lid4, name4, family4, mobile4) ON t4.lid4 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 5) t5(lid5, name5, family5, mobile5) ON t5.lid5 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 6) t6(lid6, name6, family6, mobile6) ON t6.lid6 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 7) t7(lid7, name7, family7, mobile7) ON t7.lid7 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 8) t8(lid8, name8, family8, mobile8) ON t8.lid8 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 9) t9(lid9, name9, family9, mobile9) ON t9.lid9 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 10) t10(lid10, name10, family10, mobile10) ON t10.lid10 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 11) t11(lid11, name11, family11, mobile11) ON t11.lid11 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 12) t12(lid12, name12, family12, mobile12) ON t12.lid12 = l.lid;


Convert multiple rows into column base on another column value

Table and sample data

CREATE TABLE dbo.Data
(
    [No] integer NOT NULL,
    Name varchar(4) NOT NULL,
    Relation varchar(2) NOT NULL
);

INSERT dbo.Data
    ([No], Name, Relation)
VALUES
    (1, 'A', 'P'),
    (1, 'B', 'GO'),
    (1, 'C', 'JB'),
    (2, 'AH', 'P'),
    (3, 'TY', 'P'),
    (3, 'DO', 'JB'),
    (3, 'DO1', 'JB'),
    (3, 'DO2', 'JB'),
    (3, 'DO3', 'JB'),
    (3, 'DO4', 'JB'),
    (3, 'DO5', 'GO'),
    (3, 'DO6', 'GO'),
    (3, 'DO7', 'GO'),
    (3, 'DO8', 'GO'),
    (3, 'DO9', 'GO'),
    (3, 'DO10', 'GO'),
    (4, 'PA', 'P'),
    (4, 'PA2', 'GO'),
    (4, 'PA3', 'GO'),
    (4, 'PA4', 'GO'),
    (4, 'PA5', 'GO'),
    (4, 'PA6', 'GO'),
    (4, 'PA7', 'GO');

Query

The basic technique is shown on the related question:

Need help with SQL Server PIVOT

The only extra trick is to get the numbering right:

SELECT
    P.[No], P.P,
    P.JB1, P.JB2, P.JB3, P.JB4, P.JB5,
    P.GO1, P.GO2, P.GO3, P.GO4, P.GO5, P.GO6
FROM
(
    SELECT
        D.[No],
        D.Name,
        RelationSeq =
            CASE
                -- Add a sequence number for rows with Relation JB or GO
                WHEN D.Relation IN ('JB', 'GO')
                THEN D.Relation +
                    CONVERT
                    (
                        char(1),
                        ROW_NUMBER() OVER (
                        PARTITION BY D.[No], D.Relation
                        ORDER BY
                            CONVERT(integer, SUBSTRING(D.Name, 3, 2)))
                    )
                -- Otherwise just use the Relation
                ELSE CONVERT(varchar(3), D.Relation)
            END
    FROM dbo.Data AS D
) AS S
PIVOT
(
    -- Standard pivot rows to columns
    MAX(S.Name)
    FOR RelationSeq IN
    (P, JB1, JB2, JB3, JB4, JB5, GO1, GO2, GO3, GO4, GO5, GO6)
) AS P;

Demo

Try it on Stack Exchange Data Explorer

Result

If you think this query is more difficult than it should be - you're right. Storing the original data in a more normalized structure would have made it considerably simpler. Poor design often leads to difficult queries.


Join query with closest date in "ON" clause

Let's jump "out of the box"...

Have a column in the first table with the datetime rounded to the minute.  (One minute being your resolution.)

Have two columns in the second table, both datetime, one rounded up to the next minute, the other down.

Index both columns in second table:  (id, lower) and (id, upper).

Now run a UNION ALL of two SELECTs, one JOINing the first table to the second via id and lower, with equality; the other via id and upper.

This could catch pairs that are almost 2 minutes apart, so in the outer part of the UNION, do a WHERE to filter using the to-the-second columns -- checking which ones match within one minute.

This technique is likely to be much faster than any other.


Genghis admin ui not working with mongodb 3.0.1 wired tiger

Updating to the latest mongo php driver, 1.6.6 fixed it.

sudo pecl upgrade mongo


Reporting Services TempDB lock blocking report generation

A LCK_M_S is a blocking problem not a deadlocking problem. So you are just waiting on something else.  You need to determine what process is blocking you.

So, "it suddenly disappeared" because that block either completed or failed and rolled back.  This is normal.  What sounds abnormal from your notes or your perspective is the length of the block.

In addition to finding the blocking process you could also consider using a different transaction isolation level.  For example, although not without their own problems, you might consider using "READ COMMITTED SNAPSHOT" or "SNAPSHOT" isolation levels for your report.  This would allow it to read the data as of the start of the transaction and would avoid most blocking situations.


MySql Set Column on First Appearance of Value

This is a classic example of where window functions come in handy! In this case, we shall use the ROW_NUMBER() function.
To answer your question, I did the following (see fiddle here):
The table:
CREATE TABLE people
(
  people_id INT,
  t_timestamp TIMESTAMP,
  favourite_number INT
);

Just a word of advice, you should NEVER call your fields (or your tables) using SQL keywords - it makes debugging more complex and error-prone and your apps less portable and it's bad practice generally.
Populate it:
INSERT INTO people VALUES
(1, '2020-11-04 17:40:33',  14),
(2, '2020-12-06 21:19:41',  59),
(3, '2020-12-10 18:46:11',  14),
(4, '2020-12-15 00:01:53',  7);

Then run the query (I'll go through the logic step-by-step):
SELECT
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn,
  people_id,
  t_timestamp,
  favourite_number
FROM people
ORDER BY people_id, t_timestamp, favourite_number;

Result:
rn  people_id   t_timestamp favourite_number
1   1   2020-11-04 17:40:33 14
1   2   2020-12-06 21:19:41 59
2   3   2020-12-10 18:46:11 14
1   4   2020-12-15 00:01:53 7

So, we can see by inspection that we want all of the records where rn = 1.
We now have to do a SUBSELECT (you can't put window functions in WHERE clauses):
SELECT * FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn,
    people_id,
    t_timestamp,
    favourite_number
  FROM people
--  ORDER BY people_id, t_timestamp, favourite_number
) AS tab
WHERE rn = 1
ORDER BY favourite_number, t_timestamp, favourite_number;

Result:
rn  people_id   t_timestamp          favourite_number
1   4           2020-12-15 00:01:53                 7
1   1           2020-11-04 17:40:33                14
1   2           2020-12-06 21:19:41                59

Now, these are the records we're interested in, but it's not quite the result you want.
We obtain this by using a CASE function, and since we're SELECTing all records, we can eliminate the SUBSELECT:
SELECT
  people_id,
  t_timestamp,
  favourite_number,
    CASE
      WHEN
        ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) = 1
        THEN 1
      ELSE 0
    END AS has_original_number,
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn
FROM people
ORDER BY people_id;

Result:
people_id          t_timestamp favourite_number has_original_number rn
        1  2020-11-04 17:40:33               14                   1  1
        2  2020-12-06 21:19:41               59                   1  1
        3  2020-12-10 18:46:11               14                   0  2
        4  2020-12-15 00:01:53                7                   1  1

Which, excepting rn is the result you want!
This is an example of a class of problems known as greatest-n-per-group - it's a dba.stackexchange tag - look at some of the questions in there and I would strongly urge you to get to know window functions - they will repay any effort you put into them many times over!
I modified your query in this way:
SELECT
  p.people_id, p.t_timestamp, p.favourite_number,
  CASE
  WHEN
    (
      SELECT
        MIN(x.t_timestamp) FROM people x WHERE x.favourite_number = p.favourite_number
    ) = p.t_timestamp
    THEN 1
    ELSE 0
  END AS has_original_number
FROM people p;

The result is the same!
Performance analysis: (MySQL)
I then looked at the performance of both queries using the new EXPLAIN ANALYZE <followed by query text> functionality available in MySQL version >= 8.18 (as explained here and here). For brevity, I'm only showing the plans - see the revised fiddle for the whole thing. I also make use of the MySQL profiling capability as explained here.
(caveat: I am not an expert on query plans - but I think I know a bit - any corrections, references, URLs appreciated):
I turned on SET PROFILING = 1;
Then, after running the two queries, I looked at the profiles (some of the query text is truncated):
SHOW PROFILES;

=================================

Query_ID    Duration    Query

1           0.00078575

SELECT
  p.people_id, p.t_timestamp, p.favourite_number,
  CASE
  WHEN
    (
      SELECT
        MIN(x.t_timestamp) FROM people x WHERE x.favourite_number = p.favourite_number
    ) = p.t_timestamp
    THEN 1
    ELSE 0
  END AS has_original_number
FROM people p
ORDER BY p.people_id

=======================================

2           0.00062000

SELECT
  p.people_id,
  p.t_timestamp,
  p.favourite_number,
    CASE
      WHEN
        ROW_NUMBER() OVER (PARTITION BY p.favourite_number ORDER BY p.t_timestamp) = 1
      THEN 1
      ELSE 0
    END AS has_original_number,
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp)

_
I ran this several times (typical run shown) and the result consistently shows that the ROW_NUMBER() query is faster (by ~ 20% - frequently more) than the MIN() one - this seems logical (to me) since the MIN() query is SELECTing from the people table twice! The difference is larger if I run the MIN() query second. This effect is also seen if the queries are run in the opposite order.
However, when I ran the query plans, I got a surprise:
-> Sort: people.people_id  (actual time=0.064..0.065 rows=4 loops=1)
    -> Table scan on <temporary>  (actual time=0.000..0.001 rows=4 loops=1)
        -> Temporary table  (actual time=0.057..0.058 rows=4 loops=1)
            -> Window aggregate: row_number() OVER (PARTITION BY people.favourite_number ORDER BY people.t_timestamp )   (actual time=0.043..0.049 rows=4 loops=1)
                -> Table scan on <temporary>  (actual time=0.000..0.001 rows=4 loops=1)
                    -> Temporary table  (actual time=0.040..0.042 rows=4 loops=1)
                        -> Window aggregate: row_number() OVER (PARTITION BY people.favourite_number ORDER BY people.t_timestamp )   (actual time=0.031..0.036 rows=4 loops=1)
                            -> Sort: people.favourite_number, people.t_timestamp  (cost=0.65 rows=4) (actual time=0.027..0.027 rows=4 loops=1)
                                -> Table scan on people  (cost=0.65 rows=4) (actual time=0.013..0.016 rows=4 loops=1)

2nd Query (yours modified):
-> Table scan on p  (cost=0.65 rows=4) (actual time=0.013..0.015 rows=4 loops=1)
-> Select #2 (subquery in projection; dependent)
    -> Aggregate: min(x.t_timestamp)  (actual time=0.036..0.037 rows=1 loops=4)
        -> Filter: (x.favourite_number = p.favourite_number)  (cost=0.35 rows=1) (actual time=0.009..0.033 rows=2 loops=4)
            -> Table scan on x  (cost=0.35 rows=4) (actual time=0.008..0.031 rows=4 loops=4)

The performances are roughly comparable - a bit of variability from run to run.
However, because you're returning the whole table, the optimiser will ignore indexes and scan the whole table - Table Scan on people - note that for your query, it performs 2 Table Scans whereas for mine it only performs 1 for mine (temporary tables excluded - in memory?).
IMHO, this means that as the row-count of the people table grows, the query which performs 2 table scans will slow down as a function of O(n2) but the other query will only slow down as a function of O(n). So, long-term, I think you'll be better off with my query - but, I am open to correction on this!


mysql how to copy foreign key data from one table to another

That depends on youir table definition .
If your id fileds are auto increment

CREATE TABLE table1(id int AUTO_INCREMENT PRIMARY KEY)

CREATE TABLE table2(id int AUTO_INCREMENT PRIMARY KEY,t1id int,
    FOREIGN KEY (t1id) REFERENCES table1(id))

CREATE TABLE table3(id int AUTO_INCREMENT PRIMARY KEY,t2id int,
    FOREIGN KEY (t2id) REFERENCES table2(id))

INSERT INTO table1 VALUES(NULL);

INSERT INTO table2 VALUEs(NULL,LAST_INSERT_ID());

INSERT INTO table3 VALUEs(NULL,LAST_INSERT_ID());

SELECT t1.*,t2.*,t3.*
FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.t1id
INNER JOIN table3 t3 ON t2.id = t3.t2id

id | id | t1id | id | t2id
-: | -: | ---: | -: | ---:
 1 |  1 |    1 |  1 |    1

db<>fiddle here
In other cases you have to get the last id number from the father table, which is dependent on which type if id you have.


A Script to insert dummy Data in all tables of DB

I needed to do this for a staging environment to test performance before release. What i ended up doing was using sql data generator by redgate.

Way easier than a custom solution.

sql red gate sql data generator


How would I reset all of the identity values for a table, and reseed the auto-number in Sql Server 2008

The easiest way would be to start a new table. Inject the 30,000 rows you want to keep, then drop the old table and rename the new table.

BEGIN TRANSACTION;

CREATE TABLE dbo.copy_of_foo
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  col2 ..., ...
);

INSERT dbo.copy_of_foo(col2, ...)
  SELECT TOP 30000 col2, ...
  FROM dbo.foo
  ORDER BY ...;

DROP TABLE dbo.foo;

EXEC sp_rename N'dbo.copy_of_foo', N'foo', N'OBJECT';

COMMIT TRANSACTION;

You may need to nest the order by and add OPTION (MAXDOP 1) if you want to ensure that you get the 30K most recent rows and the IDENTITY values are assigned in order from oldest to newest, e.g. hopefully you have some other column, such as a date time column, that can help identify the 30000 rows you want to keep:

INSERT dbo.copy_of_foo(col2, ...)
  SELECT col2, ...
  FROM
  (
    SELECT TOP 30000 col2, ...
      FROM dbo.foo
      ORDER BY date_time_column DESC
  ) AS x
  ORDER BY date_time_column
  OPTION (MAXDOP 1);

Keep in mind though that if someone reported an exception yesterday with row #42,000,564, good luck finding it today. As I suggested in the comment, maybe you shouldn't be exposing these obviously-otherwise-absolutely-meaningless surrogate identifiers to users.


How do I normalize and transform field text algorithmically

Example SQL Script
For the specific example of parsing a name into it's separate elements see (SO question):
How can I parse the first, middle and last name from a full name field in SQL?

The accepted answer is a fantastic example of how to roll your own algorithm to separate out your names from emails.

Tools
There are some out there, I'll update this answer later when I can have some time to find (or please edit this answer if you have found some)

Note: No matter what method you use, do a sanity check validation of your results, most good methods will be able to accurately parse 90+% of your data, the real trick is how to identify the <10% that have not been correctly parsed  (that can be as simple as doing a sanity check and scanning the results)


My question is when could the Age of a plan reach zero?

The algorithm that SQL Server uses to determine when and how plans should be removed from cache is called the eviction policy.
The cost of plan is analyzed to determine which plans gets evicted. Upon detecting memory pressure, zero cost plans are removed from the cache and the cost of all other plans is reduced by half.

For adhoc plans, the cost is considered to be zero, but it is increased by one every time the plan is reused.
For other types of plans, the cost is a measure of the resources required to produce the plan. When one of these plans is reused, the cost is reset to the original cost.
For non–adhoc queries, the cost is measured in units called ticks, with a maximum of 31. The cost is based on three factors: I/O, context switches, and memory. Each has its own maximum within the 31-tick total.

When not under memory pressure, costs are not decreased until the total size of all plans cached reaches 50 percent of the buffer pool size. At that point, the next plan access will decrement the cost in ticks of all plans by 1.
Once memory pressure is encountered, then SQL Server will start a dedicated resource monitor thread to decrement the cost of either plan objects in one particular cache (for local pressure) or all plan cache objects (for global pressure).

so the gist is ...
The Clock algorithm sweeps the cache at regular intervals. Every time an unused entry is found, the cost is decreased by some amount. If the cost is 0 and is not used, then it is removed from the cache.
Best references :

Plan Cache Internals
An in-depth look at SQL Server Memory–Part 1 and Part 2


Maybe GROUP_CONCAT would be sufficient for your purposes? Instead of giving individual columns for each member in a group, it would give a single column with comma-separated lists of members. In your case I think it would be something like:

SELECT gp.group_name as `Group Name`,
   GROUP_CONCAT(CONCAT(lc.first_name, ' ', lc.last_name)) as `Client Names`
FROM `group` gp
...
INNER JOIN client_has_group chg ON (chg.group_gr_id = gp.gr_id)
INNER JOIN loan_client lc ON (chg.loan_client_id = lc.id)
GROUP BY gp.group_name

Which should give something like:

+----------+----------------------+
|Group Name|   Client Names       |
+----------+----------------------+
|Group A   |   Joan,Patrick,Smith |
+----------+-------------+--------+
|Group B   |   Samuel,Elise       |
+----------+----------------------+
|Group C   |   Marco              |
+----------+----------------------+


Designing a bank database schema with multiple balances per account

Use ENGINE=InnoDB and code like this:

BEGIN TRANSACTION;
Update / insert into one account (one database table, that is)
Update / insert into another account
and any other actions that are tied to this 'transaction'
COMMIT;

In some situations, keep two tables for one account:

"Current" balance, etc
"History" of what happened -- This should only have INSERTs, no UPDATEs or DELETEs.  It is a blow-by-blow list of all the changes made to arrive at the "current" state that is shown in the other table.

So the "transaction" might include one UPDATE to the first table, and one INSERT into the other table.


Are master/slave replication relationship between different versions of SQL Server possible?

Short answer is yes that is possible and straight forward (I've just finished setting up transactional replication from SQL 2005 to SQL 2008 R2).

Note: in SQL Server replication the terms are 'Publisher' and 'Subscriber'


SQL Server - is it ok to store link table as a heap?

I agree that there is no point in creating an IDENTITY column clustered index here but I would just create the composite PK as CLUSTERED rather than NONCLUSTERED.

There is no point having 2 copies of the data, One in the heap that never gets used and one in the NCI. In fact it is downright counter productive as shown below.

Your NCI will still get page splits anyway if you are not inserting into the table in order of primary key so you have not avoided the problem. You might also consider a UNIQUE constraint or index on the reversed key order as well depending upon what queries you run against that table.

In general NCIs can be slightly be more compact than the equivalent NCI as they do not have the status bits B or NULL_BITMAP (if no nullable columns) but in this case this is more than outweighed by the necessity to store the 8 byte RID pointer to the row in the heap as can be seen from the below.

Script

CREATE TABLE T_NCI
  (
     table1_id INT NOT NULL,
     table2_id INT NOT NULL,
     CONSTRAINT NCI_PK PRIMARY KEY NONCLUSTERED(table1_id, table2_id)
  );

CREATE TABLE T_CI
  (
     table1_id INT NOT NULL,
     table2_id INT NOT NULL,
     CONSTRAINT CI_PK PRIMARY KEY CLUSTERED(table1_id, table2_id)
  );

INSERT INTO T_CI
OUTPUT inserted.* INTO T_NCI
SELECT DISTINCT number, number
FROM master..spt_values

SELECT LEFT(OBJECT_NAME(object_id),5) AS [Name],
       page_count,
       record_count,
       min_record_size_in_bytes,
       max_record_size_in_bytes
FROM   (SELECT *
        FROM   sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('T_CI'), NULL, NULL, 'DETAILED')
        UNION ALL
        SELECT *
        FROM   sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('T_NCI'), NULL, NULL, 'DETAILED')
) T

DROP TABLE T_NCI
DROP TABLE T_CI

Results

Name  page_count           record_count         min_record_size_in_bytes max_record_size_in_bytes
----- -------------------- -------------------- ------------------------ ------------------------
T_CI  5                    2163                 15                       15
T_CI  1                    5                    15                       15
T_NCI 5                    2163                 15                       15
T_NCI 6                    2163                 17                       17
T_NCI 1                    6                    15                       15

The record length for the non leaf pages in both the CI and the NCI is 15 bytes (1 byte status bits, 8 bytes for the composite key and 6 for the down page pointer) but for the leaf page NCI the rows take up 17 bytes (1 byte status bits, 8 bytes for the composite key and 8 for the row pointer) compared to 15 for the CI (2 bytes status bits, 1 byte column count offset, 8 bytes data, 2 bytes column count, 1 byte null bitmap). And as well as this less compact index structure you also have all the additional pages for the heap itself on top.


This doesn't seem to be specifically an SSIS problem.

It seems to me that the logic of the original code is such that the if the previous update statement updates 0 rows that it indicates a concurrent update took place.

I'm guessing that the update statement includes one or more extra columns in the where clause on which previous values are matched. The update affecting 0 rows indicates that a concurrent update took place.

You'll need to show us the rest of the procedure to determine whether this is the case.


behavior of a Sql Server availability group listener and a jdbc client during a failover?

My understanding is that no client is going to be able to connect to the Listener and get a database connection until the failover completes. Look at the section "How Automatic Failover Works" in the following link. See here


mysql multiple database prioritize innodb buffer pool size

You can configure MyISAM dedicated key buffers. No such buffer scheme exists with InnoDB.

Usually, it is recommended to assign up to 80% of server RAM to the InnoDB Buffer Pool.

Since you cannot prioritize which database or tables can occupy the InnoDB Buffer Pool, all you can really do is create a buffer large enough to hold all InnoDB data.

See my earlier posts on sizing the InnoDB Buffer Pool

Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?
Oct 22, 2012 : How much memory do I need for innodb buffer pool? (This was my post on ServerFault that was later migrated to the DBA StackExchange the next day)
Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?


How to use MySQL stored function argument to refer to column name?

With thanks to Colin 't Hart this worked:

DELIMITER $
CREATE PROCEDURE showUnique( IN col CHAR(64))
BEGIN
SET @s=CONCAT('SELECT COUNT(DISTINCT ',col,') FROM president);
PREPARE stmt from @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $
delimiter ;
CALL showUnique(last_name)


Since what you're doing is the equivalent of an inner join, give this a try:
SSIS Merge Join


Creating User for a single contained database

To create a contained user in SQL Server 2012 that authenticates at the database, you can do the following:

use YourDatabase
go

create user YourContainedUser
with password = 'yourPassword'
go

You will have to ensure that contained database authentication is enabled on the instance first, and that the database is set with partial containment:

exec sp_configure 'contained database authentication', 1
go
reconfigure
go

alter database YourDatabase
set containment = partial
go

Once those above commands are executed you should be able to create your contained user with the CREATE USER statement as explained above.

EDIT:  Great comment and points by @AaronBertrand:

  Also your connection string absolutely must use the database name as part of the credentials - if you don't specify the database, SQL Server isn't going to check all the databases and try to figure out which one you may have meant. And your code can't perform any cross-database queries in case that limitation isn't clear.


Postgres 11 declarative partitioning - using large number of partitions

we implemented partitioning with ID

What does that mean specifically? List partitioning, where each list has only one member?  And what is ID?  Is that the primary key from a parent table, and you are partitioning a child table?

Did you do before-and-after studies, or testing in a good test environment, to show that you actually get a benefit from this setup in the first place?

  Partition rows are never updated and our queries always target single partition.
  Now we have run into a problem that we exhausted maximum allowed locks (cca. 25000).

If you don't update, and queries always target a single partition, then I don't see how you would be running out of locks.  What operation is happening which locks all the partitions at once?  Is 25000 the current setting of max_locks_per_transaction * max_connections, which is inadequate, or is that what you want to increase it to?  If 25,000 is already inadequate and you still expect your data to grow 10 times, I'd be a bit worried there that just increasing it may not be good long term solution (although probably fine as a short term solution).  Or at least, I'd do some simulations in a test environment.

  Or should we switch to hash or date-range partitioning?

Partitioning is not fungible.  If partitioning by ID gave you a substantial benefit, there is no reason to think changing it to date-range would give the same benefit.  Without knowing the source of the benefit, we can't predict what other methods would retain (or better) that benefit.


Can I do a sargable first letter match on two tables?

If the lastname column is indexed in at least one of the tables then you could also use LIKE

SELECT *
FROM   persons p
       INNER JOIN persons2 p2
               ON p2.lastname LIKE LEFT(p.lastname, 1) + '%'

The plan for this can have a seek on the table specified to the left of the like.

i.e. ON p.lastname LIKE LEFT(p2.lastname, 1) + '%' would not be able to make use of the index on persons2that was used above but could seek one on persons.

The suggestion in the other answer of indexing a calculated column on both sides is more flexible however. As for a nested loops plan either table can be on the inside and it would also allow a many to many merge join without requiring a sort.


PostgreSql 12 - Can't see table details \dt (table) but can see all tables \dt

You created a case sensitive table name (which is strongly discouraged) so now you need to use double quotes every time you access it:
\d xekata."TestNodeBase"

I highly recommend to stop using those dreaded quoted identifies. Forget that double quotes exists when dealing with SQL.


Schema/Structure to handle secondary computed data

I think a spin-off of option 2 would be ideal based on your requirements that seem to only be for presentation and not further computation.

You query the database for all the scores from match X and order them from high to low. Then in the presentation layer of the web application, simply add ranked number assignments to them starting with 1 for the first and n+1 from there on for each row you fetch from the result set.

A query could be

SELECT name, score FROM matches WHERE matchId = 'X' ORDER BY score DESC


You can try pg_config with --configure flag.

  --configure

  Print the options that were given to the configure script
  when PostgreSQL was configured for building. This can be used to
  reproduce the identical configuration, or to find out with what
  options a binary package was built. (Note however that binary packages
  often contain vendor-specific custom patches.)

And confirm whether the installed version has SSL enabled or not!


Is there performance difference between "SET ROWCOUNT" and "TOP"?

Generally speaking ... performance should be the same.

Technically speaking ... it depends on the query; for example, if the TOP is used within the confines of a derived table then you may see different performance depending on how/if the optimizer flattens the query.

To answer the question for a given query(s), run some tests with the following options enabled:

set showplan on
set statistics io,time on
go
-- set rowcount XXX
select [top] ...
-- set rowcount 0
go

You'll want to run the query a couple times to make sure data has been pulled from disk so that you can compare in-memory data queries to each other.

Obviously you'll then want to compare the 2x query plans as well as the IOs and timings (keeping in mind that times can vary greatly based on duration of blocking and/or time spent on the wait queue waiting to run on a dataserver engine). IOs should be comparable on repeat runs, but for timings you'll likely want to run each query a few times and take the average.


Nested stored procedures with use of transactions

Let's first get the RAISERRROR() part out of the way, more specifically why does the insert happen when it is removed.

  It works if I remove the raiserror-part of the code. Then the 'Data
  from OuterProc code' gets inserted..

The raise error is defined outside of the TRY block scope

    raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;

And the documentation shows that the error is returned to the caller (OuterProc) when RAISERROR() is ran outside of the try block scope.

  The error is returned to the caller if RAISERROR is run:

  Outside the scope of any TRY block.

Source

As a result, the raiserror returns control to the OuterProc procedure, and as a result of the error happening in the TRY block of the outer procedure it is returned to the CATCH block. And, inside this catch block is a ROLLBACK.

-->  ROLLBACK

If you add this to the CATCH block of the outer proc:

BEGIN CATCH
    declare @error int, @message varchar(4000), @xstate int;
    select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();

raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
 ROLLBACK
END CATCH

The error gets re-raised:

  Msg 50000, Level 16, State 1, Procedure OuterProc, Line 17 [Batch
  Start Line 58] usp_my_procedure_name: 50000: usp_my_procedure_name:
  8134: Divide by zero error encountered.

And the outer proc insert:

 INSERT INTO TABLE1
 VALUES('Data from OuterProc code')

Gets rolled back.

  So my question is - what is the point of the savepoint when there is a
  raiserror-statement in the procedures catch-block

The savepoint is there to not rollback the entire transaction, but due to your use of TRY CATCH in the outer procedure, your outer procedure is also rolled back.

If you don't want the outer transaction to also be rolled back, then you should not use an additional TRY CATCH block around the outer transaction.

You would have to define the procedure like this:

CREATE PROCEDURE OuterProc2 AS

     BEGIN TRANSACTION

     INSERT INTO TABLE1
     VALUES('Data from OuterProc code')

     EXEC usp_my_procedure_name

     COMMIT TRANSACTION

If you then run the procedure:

EXEC OuterProc2;

The inner procedure error is returned to the caller:

  Msg 50000, Level 16, State 1, Procedure usp_my_procedure_name, Line 36
  [Batch Start Line 54] usp_my_procedure_name: 8134: Divide by zero
  error encountered.

But the  Data from OuterProc code string is still inserted.

SELECT *
FROM table1;

Value
Data from OuterProc code

If you do not use a savepoint, then the entire transaction will be rolled back.
As a result, errors will occur when trying to COMMIT the non existing tran.

  Msg 266, Level 16, State 2, Procedure usp_my_procedure_name, Line 0
  [Batch Start Line 53] Transaction count after EXECUTE indicates a
  mismatching number of BEGIN and COMMIT statements. Previous count = 1,
  current count = 0. Msg 3902, Level 16, State 1, Procedure OuterProc2,
  Line 10 [Batch Start Line 53] The COMMIT TRANSACTION request has no
  corresponding BEGIN TRANSACTION.


Good Resources on Operating/Administering MongoDB

The best resource has to be the MongoDB docs themselves ...

http://www.mongodb.org/display/DOCS/Home

I'm constantly finding something new there.

Secondly, I'd say you should run the "try MongoDB" in browser tutorial available here, it's awesome. You can try out querying and everything without installing anything.

http://try.mongodb.org/

Also, I'd personally recommend very highly these two tools:

MongoVUE (Windows MongoDB GUI)
RockMongo (PHP MongoDB interface, beats the pants off the others.)

You can also see my stackoverflow answer here for more details on MongoDB gui's also there are lots of videos available here: http://www.10gen.com/video

Lastly, you can check out http://learnmongo.com for a lot of beginners resources (full disclosure, I founded the site.)


MySQL update one table from another joined many-to-many

This works - I had the JOIN wrong

UPDATE `events`
  INNER JOIN `plays_in_events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
  SET events.Name = plays.Play
WHERE
  events.Name IS NULL and plays_in_events.PlayID = 1

and the earlier comments were quite right, but it took me a while to 'get it' - thanks for your help. I had just copied the JOIN lines from the working SELECT query, but when I looked again, I could finally see what the commentators meant about events appearing twice, and plays_in_events not appearing properly in the JOINs.


'user' is not valid at this position, expecting an identifier

You shouldn't use quotes for the names of objects / entities, that stringifies it as a value instead. This would be the correct syntax you're looking for:
CREATE TABLE users
(
    id int(11) unsigned NOT NULL AUTO_INCREMENT,
    name varchar(75) NOT NULL,
    password varchar(100) NOT NULL,
    email varchar(100) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;


Merging one-to-many and one-to-one relation in the same JSON object in postgresql

See the composite types documentation for how to handle entire rows as a single value (which ends up as an object in JSON).

Apparently, the only way to get the correct field names for the extra info is to create a new type for that subobject:

CREATE TYPE extra_info_object AS (
  extra_info1 TEXT,
  extra_info2 TEXT
);

SELECT to_json(data)
FROM (SELECT name,
             ROW(extra_info1,
                 extra_info2)::extra_info_object AS extra_info,
             (SELECT array_agg(events)
              FROM events
              WHERE events.op_id = ops.op_id
             ) AS events
      FROM ops
      LEFT JOIN op_extra_info USING (op_id)
) AS data;


Postgres query performance - hash aggregate costly operation

Your time is spent in the index scan. Both of your indexes are not perfect for that query.

Try this one:

CREATE INDEX ON program_schedule_source_count (start_date, original_title);

If the third condition is always the same, you might put it into a WHERE clause:

CREATE INDEX ON program_schedule_source_count (start_date, original_title)
   WHERE show_type in ('SM', 'SE');


Differential backups are 2/3 the size of the full backup

After digging into the backup logs between LiteSpeed and SQL Server I noticed that the first LSN number between the daily full backup and the full backup LSN number in differential backups didn't match up.

After doing some reading on how the LSN values are used in the backups of the database I did some digging on what would cause the full backup's LSN number in the differentials to be different then what the first LSN value was in the full backup on the same day.  This post on the Backup Chain in SQL Server pointed me to look at the configuration of the backup jobs and I found that the COPY_ONLY option was enabled for the full backups. (Still trying to determine how it was turned on to begin with and when)

What I learned from the post is that the COPY_ONLY option won't reset the LSN value for the subsequent differential backups which is a big problem if you don't retain the last full backup that was done without the COPY_ONLY option.

From the blog post:

  So, stated differently, IF a FULL backup is executed in your environment WITHOUT the COPY_ONLY clause, it can/will jeopardize your backup chain as it will reset the differential base LSN of all subsequent DIFFERENTIAL backups. Meaning, in turn, that if you don’t have (or retain) access to that FULL backup made without the COPY_ONLY option, you won’t be able to recover your databases using subsequent DIFFERENTIAL backups.

I've since turned the COPY_ONLY option off in the backups and the size of my differential backups have dropped back down to what they historically have been.  I've also verified that the Full Back LSN number in the logs for the differential backups is matching what the first LSN number value is in the logs for the daily backups.


SQL Server blocking report with waitresource Database

The MSDN sys.dm_tran_locks documentation shows a list of resource and resource subtypes, including those affecting database locking:

BULKOP_BACKUP_DB         Database backups with bulk operations.
BULKOP_BACKUP_LOG        Database log backups with bulk operations.
CHANGE_TRACKING_CLEANUP  Change tracking cleanup tasks.
CT_DDL                   Database and table-level change tracking DDL operations.
CONVERSATION_PRIORITY    Service Broker conversation priority operations such
                             as CREATE BROKER PRIORITY.
DDL                      Data definition language (DDL) operations with
                             filegroup operations, such as drop.
ENCRYPTION_SCAN          TDE encryption synchronization.
PLANGUIDE                Plan guide synchronization.
RESOURCE_GOVERNOR_DDL    DDL operations for resource governor operations such
                             as ALTER RESOURCE POOL.
SHRINK                   Database shrink operations.
STARTUP                  Used for database startup synchronization.


You could set up a simple job to backup the database to a single location and then restore it. It would look something like this

--First we back up the DB
BACKUP DATABASE [ABC] TO  DISK = N'C:\temp\ABC.bak' WITH NOFORMAT, NOINIT,
        NAME = N'ABC-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
        GO
--Then we restore the DB
        RESTORE DATABASE [ABC_test] FROM  DISK = N'C:\temp\ABC.bak' WITH  FILE = 1,  MOVE N'ABC' TO N'C:\DBFiles\ABC_test.mdf',
        MOVE N'ABC_log' TO N'C:\DBFiles\ABC_test_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
        GO

You might want to delete the file before you back it up. You could do this with powershell (my recommendation), xp_delete_file (undocumented SP), or xp_cmdshell (not sure if it is enabled on your system or not)


Migrate Management Data Warehouse to a New Instance

I understand this is an old thread, but it was the first hit when I searched "how to move mdw database" on Google.

I had configured MDW (Data Collection) on a SQL Server 2008 R2 production server, and I hosted the MDW database in a QA instance. Later on I had to move the QA instance to a different machine, with a different name (migration and name change mandatory for business reasons) and I wanted to keep the MDW data that was already collected. I could not find anything on that, so I gave this procedure a try and it worked for me:

Stop all data collectors in the monitored server (production in my
case)
Back up the MDW database, optionally put it offline (old QA server)
Restore the MDW database to the new host instance (new QA server)
Re-configure the monitored server to use the MDW database in its new
location (Right click on Data Collection, select Configure Management Data       Warehouse. Select "Set up Data Collection" and click Next. Click on the ellipsis button and connect to the new instance, then from the drop down list select the MDW database that you restored in the previous step. Click Next, then Finish)

The data collectors should start automatically after the Configure Management Data Warehouse wizard finishes, and you can view the MDW reports in the new database location.

Then again, this worked for me in a SQL Server 2008 R2 environment, I am not sure if it works in SQL Server 2012.


'text search dictionary "unaccent" does not exist' entries in postgres log, supposedly during automatic analyze

You are obviously using a fake-IMMUTABLE function wrapper for unaccent() to use it in indexes. It's based on the search dictionary "unaccent" implicitly or explicitly. The dictionary to be used can be provided as optional first function parameter to unaccent(). The name of the dictionary can be schema-qualified. Else it needs to reside in one of the schemas of the current search_path.

I do not advise to manipulate the function unaccent() provided by the extension. Instead, create and use a wrapper function like instructed here:

Does PostgreSQL support “accent insensitive” collations?

In your case, the additional module unaccent might be installed in a schema that's missing from the search_path of the session reporting these errors. When you try manually, you do that in a session where the search_path includes that same schema, so no error there.

You can either set your search_path to include the schema of the extension in all sessions, or schema-qualify the dictionary in the function calls of unaccent().

Security for client programs has been tightened with Postgres 10.3 / 9.6.8 etc. Now you need to schema-qualify function and dictionary when used in any indexes - as demonstrated in the linked answer above.

The release notes for Postgres 10.3:

  Also, the changes described in the second changelog entry below may
  cause functions used in index expressions or materialized views to
  fail during auto-analyze, or when reloading from a dump. After
  upgrading, monitor the server logs for such problems, and fix affected
  functions.

  ...

  Avoid use of insecure search_path settings in pg_dump and other client programs (Noah Misch, Tom Lane)

  pg_dump, pg_upgrade, vacuumdb and other PostgreSQL-provided
  applications were themselves vulnerable to the type of hijacking
  described in the previous changelog entry; since these applications
  are commonly run by superusers, they present particularly attractive
  targets. To make them secure whether or not the installation as a
  whole has been secured, modify them to include only the pg_catalog
  schema in their search_path settings. Autovacuum worker processes now
  do the same, as well.

  In cases where user-provided functions are indirectly executed by
  these programs — for example, user-provided functions in index
  expressions — the tighter search_path may result in errors, which will
  need to be corrected by adjusting those user-provided functions to not
  assume anything about what search path they are invoked under. That
  has always been good practice, but now it will be necessary for
  correct behavior.

Bold emphasis mine.

See the special mention of auto-analyze and autovacuum? To fix, schema-qualify function name and dictionary in the unaccent() call of the wrapper function used in your index(es) as demonstrated in the linked answer. Like:

CREATE OR REPLACE FUNCTION f_unaccent(text)
  RETURNS text AS
$func$
SELECT public.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$func$  LANGUAGE sql IMMUTABLE;

You can change an existing function definition with CREATE OR REPLACE FUNCTION ... without rebuilding existing indexes based on it. Make sure the function keeps using the same function & dictionary, then indexes keep working properly without REINDEX. (You can't DROP the function, though, while it's used in an index.)

Moving the unaccent module to the system catalog pg_catalog also works. Then everything is in the search_path automatically. You ended up doing so, but I do not advocate that. Messing with system catalogs is generally discouraged and can easily damage your installation if you don't know exactly what you are doing.

Related:

Error when creating unaccent extension on PostgreSQL

The search dictionary "unaccent" is also regularly used by text search configurations in full text search. Related:

Get partial match from GIN indexed TSVECTOR column
Finding the most commonly used non-stop words in a column


Oracle CPU vulnerabilities and Database Proactive Bundle Patch

The key word you're looking for is "cumulative".

Note 1962125.1 (Oracle Database - Overview of Database Patch Delivery Methods), includes this text about Bundle Patches:

Bundle Patch (BP)
- a cumulative collection of fixes to address bugs in a given feature, product, or configuration
     For example: Windows Database Bundle Patch, Database Patch for Exadata, Database Proactive Bundle Patch
- a superset of PSU

So that's all you should need to provide!  BPs include all patches previously included in earlier BPs/PSUs.


Which Edition Of SQL Server Is On The Disc?

I believe that all discs are the same, only the Product key will make the difference once entered during the setup.


Timestamp columns in Peer to Peer Replication

Tested it myself.
TimeStamp columns are not allowed. You can't replicate a table if it contains a TimeStamp column.

I got the following message when I tried:

  The table cannot be published because it contains a timestamp column. Timestamp columns are not supported in Peer-to-Peer publication.


MongoDB wiredTiger Error: collection-*.wt does not appear to be a WiredTiger file (Window10)

@Lizhen, may be Wiredtiger does unclean shutdown. Just go to 'E:\Work_App\MongoDB\bin' and type in command prompt like

Then run the query in command prompt “mongod --dbpath /data/db –repair”

Suppose that i have installed MongoDB in C:\Program Files\MongoDB\Server\3.2\bin then the query statement will be like that

C:\Program Files\MongoDB\Server\3.2\bin>mongod --dbpath /data/db --repair

As you already said your MongoDB installation path is E:\Work_App_MongoDB\bin. So in your place it would be

E:\Work_App_MongoDB\bin>mongod --dbpath /data/db –repair

I hope so that it will be help out to you.


Table-based custom type does not verify check constraints

A record will only be null if all of its fields are null or if the record itself is null. If you want to check if any of the fields is null then check one by one:

create table final
(
    name character varying(20) not null,
    data source check (
        (data).id is not null and (data).weight is not null
    ),
    constraint final_pkey primary key (name)
);

insert into final (name, data) values ('a', null);
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, null).

insert into final (name, data) values ('a', (null, null));
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, (,)).

insert into final (name, data) values ('a', ('1', null));
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, (1,)).

insert into final (name, data) values ('a', ('1', 1));
INSERT 0 1


The reason for slow down of query is clearly visible in the query plan. There are columns need to be indexed, to tune the query performance. In your query you have used oc_order_option table which does not have order_id column indexed. Index it using following command:

ALTER TABLE `oc_order_option` ADD KEY `OrderID` (`order_id`)

Same thing is happening with oc_order_product and it needs to be tweaked with following command:

ALTER TABLE `oc_order_product` ADD KEY `ProductOrderID` (`order_id`)

Also you need to tweak oc_order as well, use following:

ALTER TABLE `oc_order` ADD KEY `OrderStatusID` (`order_status_id`)

After implementing the tweaks run your query and see the performance difference.


Oracle 11gR2 Installation on Oracle linux 7 - Prerequisite condition failed for OS kernel parameter "semmni"

Having the correct value in sysctl.conf does not mean that is the current setting in effect system-wide.

You can check the current value in effect with:

sysctl -a | grep "kernel.sem"

If it differs from the one in the file configured, you need to actualize it with

sysctl -p

Or it will happen on the next reboot automatically.


MySQL best way to define a couple of columns as both null or both not-null

I should think a table-level CHECK constraint would serve here, something like
CONSTRAINT c1_c2_null_match
  CHECK ((c1 IS NULL AND c2 IS NULL)
     OR  (c1 IS NOT NULL AND c2 IS NOT NULL))

See the MySQL 8.0 documentation.
You could instead use a trigger, but can run into consistency issues that way. Certain bulk-load operations will bypass triggers, meaning that the check is never performed at all and allowing the condition to be violated. A constraint can be temporarily disabled, but the check will be performed when the constraint is re-enabled.
One note: You don't mention which version of MySQL you're using, but prior to 8.0.16 CHECK constraints were ignored, so you would need to use a trigger to do what you want.
To do this with a trigger you need to return an error if the fields don't match, something like this:
DELIMITER //
CREATE TRIGGER c1_c2_null_match BEFORE UPDATE ON tbl
  FOR EACH ROW
  BEGIN
    IF (NEW.c1 IS NULL AND NEW.c2 IS NOT NULL)
    OR (NEW.c1 IS NOT NULL AND NEW.c2 IS NULL)
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Null columns must match';
    END IF;
  END //
DELIMITER;

See this Stack Overflow question.
Hope that helps.


Completion of SQL Job triggers start of job on a different SQL Server

If you're on SQL Server Enterprise Edition:

You could set up a Data-driven subscription in SSRS. On your database with the FTP/Import Job, the system database MSDB contains a table called sysjobhistory. On your SSRS server, set up a Data-driven subscription to run when the step in your FTP/Import job reports success to this table.

Instructions for setting up a Data-driven subscription in SSRS: Books Online

If you are on Standard Edition:

Please use a linked server as outlined by Martin Smith here:

  Data Driven subscription is an Enterprise Edition feature. Just creating a regular subscription with a schedule in the past and calling EXEC LinkedServer.ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='whatever' would do what they need.

To implement Martin's suggestion, use the SQL Server agent job that is created automatically by SSRS when a subscription is created. The job's name will be capital letters and numbers, separated by hypens.

You should create this subscription, but then alter the job itself to have a schedule in the past, where it will not run. When you call this dbo.AddEvent over your linked server, you can add a new run time to this job


Is this the correct/best way to create a custom column in mysql?

It is unclear whether the method in your example is right or best for you, but if you need to query or return the same expression in many different queries, you can add a custom column to the table itself. In MySQL, this is called a generated column:

ALTER TABLE
  MY_TABLE
ADD COLUMN
  custom_column VARCHAR(50) AS
  (
    SUBSTRING(column2, 4)
  )
;

This way the column will be available for reference in any part of any query on MY_TABLE:

SELECT
  column1,
  column2,
  custom_column
FROM
  MY_TABLE
WHERE
  custom_column = 'xyz'
;

A live demo of this approach is available at db<>fiddle.uk.

On the other hand, if this is a one-off occasion where you simply need to be able to reference a calculated column in the WHERE clause of one specific query, then since MySQL 8 you can define such a column using a LATERAL join to a FROM-less SELECT. Once you have defined the column in this way, you can reference it by name in any part of the query, including the WHERE clause:

SELECT
  t.column1,
  t.column2,
  c.custom_column
FROM
  MY_TABLE AS t
  CROSS JOIN LATERAL
  (
    SELECT
      SUBSTRING(column2, 4) AS custom_column
  ) AS c
WHERE
  c.custom_column = 'xyz'
;

You can play with this method in another live demo.


Platform independent select ordered by primary key

The SQL Standard requires that you specify an explicit column list if you require the result set to be ordered.

Many RDBMS return the results of a single table select in clustered index order. As the primary key again is enforced by the clustered index by default, this gets you close.
however, there is no guarantee of this order. Things that could cause an engine to return the rows in a different order are for example parallelism within the query or other concurrent interactions with that table.

There is a fairly standard way to construct a dynamic select that has your desired order by clause specified. You can use the INFORMATION_SCHEMA management views for that purpose. However this approach falls short when it comes to actually execute the dynamic SQL as there are significant differences between the different vendors.

So in short, the answer is: NO.

There is one, admittedly small exception where you can get away without knowing the column name of a single column surrogate primary key in SQL Server:

CREATE TABLE dbo.A(i INT IDENTITY(1,1) PRIMARY KEY CLUSTERED);
GO

SELECT * FROM dbo.A ORDER BY $IDENTITY;

The key word $IDENTITY always references the one identity column of the table. But this is SQL Server specific.


Select rows based on latest date with multiple joins

Your query can be simplified to:

SELECT
    c.id,
    c.name,
    a.rtime,
    s.status_name,
    u.user_name
FROM company c
    LEFT JOIN
      ( SELECT
            company_id,
            MAX(rtime) AS maxdate
        FROM action
        WHERE deleted IS NULL
        GROUP BY company_id
      ) AS x ON x.company_id = c.id
    LEFT JOIN action a ON  a.deleted IS NULL
                       AND a.company_id = x.company_id
                       AND a.rtime = x.maxdate
    LEFT JOIN user u ON u.id = a.user_id
    LEFT JOIN status s ON s.id = a.status_id
WHERE
    c.name LIKE 'Company%' ;

An index on (deleted, company_id, rtime) would make the derived table subquery efficient. I suppose you already have indexes on the columns used for the joins and on the Company (name).

SQL-Fiddle


MYSQL - Timeclock Count Hours In/Out for each day of current week


Oracle Materialized View Error Log Table?

You can use the following query to check when the MVs were last refreshed.

SQL> select owner, mview_name, last_refresh_type, last_refresh_date
from dba_mviews/user_mviews;

If you were refreshing these MVs using DBMS_SCHEDULER:-

SQL> select log_date, status
from dba_scheduler_job_run_details
where job_name='JOB_NAME';

LOG_DATE                                     STATUS
--------------------------------------       ------------------------------
16-APR-17 04.42.58.546973 AM +05:45          SUCCEEDED
08-APR-17 02.38.12.843886 AM +05:45          FAILED

If you were refreshing these MVs using Job Queues/DBMS_JOB`:-

SQL> select job, last_date last_refresh,next_date next_refresh, total_time,broken, failures, what
from dba_jobs
where what like '%dbms_refresh%';

The reason of the job failure is written in alert_<SID>.log file as shown below as well as in a trace file referenced in the alert log file.

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j003_143260.trc:
ORA-12012: error on auto execute of job "SCHEMA"."JOB_NAME"
ORA-12008: error in materialized view refresh path
ORA-22992: cannot use LOB locators selected from remote tables
ORA-06512: at "SCHEMA.PACKAGE/PROCEDURE", line 70
ORA-06512: at line 1


Should I query from a snapshot or use a backup for reporting purposes

Solution 2 is very confusing as written and your question seems to start with query performance and moves into backup best practices. In solution 2, you still have your app DB and your new reporting DB on the same server. This means you'll still have the same resource contention (memory, CPU). You won't have the same blocking issues since you did create a reporting copy of your. This has nothing to do with the backups though.

Solution 1 is very common. You can even keep the data more "up to date" with log shipping. There's lots of information on this site about that.

Regarding your backups, some general advice would be

Run your backups from your App DB (at least). This is the data you really care about.
Back it up to wherever you want. Ideally not the same drive your data files are on. Corruption on these drives could mean corruption on your data files, and backups, leaving you dead in the water for recovery
Schedule your backups. You shouldn't be doing this manually. The frequency of your backups should be based on your RPO and RTO.


The picture (a) shows us not "SSMS error log table", it's the content of SQL Server error log file.

The errors occured at the service and server level are stored here.

The examples are:
service could not start because the port was alredy occupied,
system database files were not found, max server memory was configured to 0, etc. The errors ot this type cannot be catch by your procedure just because no procedure can be executed as service could not start.

Other kinds of errors stored in error log such as login failed or session was killed also cannot be catch by your code.

Your error logging proc is intended to catch other types of error such as constraint violation, conversion errors, permission errors, all these types of error are NOT logged in the SQL Server error log as they are simply programming errors.

You did not say that your code is executed in jobs and that there are ssis packages. In SSIS you can enable logging (it's not enableds by default), besides your code can have try..catch blocks where you want to suppress the errors and continue code execution.

There is no other functionality in SSMS which logs errors for dbas. SQL Server error log supplies errors for DBA regarding server functionality. The procedure that you showed is for developers, the errors it can catch do not influence server life, they regard only your code execution, and there is no "default" logging mechanism that catches and logs programming errors


Trigger to prevent insertion of duplicate data

I'd stay away from triggers as much as you can, cause they're generally pretty bad for performance. I'd suggest using a non clustered index with a filter.

See below example

CREATE TABLE dbo.Inventory( ProdCode INT, UPCCode Char(5) )

INSERT INTO dbo.Inventory(prodcode,UPCCode)
VALUES  ('1','11111'),
        ('2','11112'),
        ('3','11113'),
        ('4','11114'),
        ('5',NULL),
        ('6',NULL)

CREATE UNIQUE NONCLUSTERED INDEX NCIU_UPCCode
ON dbo.Inventory(UPCCode)
WHERE UPCCode IS NOT NULL;

-- Works
INSERT INTO dbo.Inventory
VALUES  ('7','11115')

-- Works
INSERT INTO dbo.Inventory
VALUES  ('8',NULL)

-- Fails
INSERT INTO dbo.Inventory
VALUES  ('9','11115')

DROP TABLE dbo.Inventory

That should give you the requested results, without needing to use a trigger.


Drop user did not reduce size of tablespace file

I don't think there's much value in me copying what others have done so well in explaining in the past, so I'll just point you at a brilliant blog post: Reclaiming Unused Space in Datafiles.

Essentially you just need to do:

ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 111M;


Setting up binary replication between two PostgreSQL instances

Your last two steps are in the wrong order.

The appropriate steps says

  _ 5. Start the standby first, so that they can't get out of sync. (Messages will be logged about not being able to connect to the
  primary server, that's OK.)
  _6. Start the master.

You are supposed to start the master after the standby.

Try reversing everything as follows:

[root@dbrepa]# service postgresql-9.2 stop
[root@dbrepb]# service postgresql-9.2 stop
rsync everything from Master (except the pg_xlog and config files (Your Step 4)) to Standby
[root@dbrepb]# service postgresql-9.2 start
[root@dbrepa]# service postgresql-9.2 start

Give it a Try !!!


Best practice to shrink Tempdb in a production environment

It is a best practice to proactively monitor the normal usage of Tempdb and set the size accordingly.
If this is one off case where the Tempdb has grown to such a size and its a PROD env, I would restart SQL Server Services during weekly maintenance. There after Tempdb would go back to its configured size.

Shrinking the file is fine as long as Tempdb is not being used, else existing transactions may be impacted from performance point of view due to blockings and deadlocks.

Cleaning procedure cache, buffer caches etc will have negative impact on the database performance itself until those are not re-created. I would not do this on PROD.

Hope that helps!


MySQL not returning successful results on statement - query looks like it's hanging but is successful

You can try setting up a shell script that runs the SQL scripts or write a program in perl/whatever language which tries to log the errors and return codes from MySQL. Otherwise it is very difficult to troubleshoot without knowing the errors.

You could also add SHOW ERRORS; and SHOW WARNINGS; to your SQL scripts, but I suspect the problem is not with your queries.


Is there a way to enforce uniqueness of arbitrary key-value pairs in a JSONB object column

Failed attempt with Exclusion Constraints

I don't believe this is possible to do this with exclusion DDL because I don't believe PostgreSQL supports exclusion constraints on text[]

CREATE TABLE categories(title)
AS VALUES
  ('{"en": "Hello", "ru": "Привет"}'::jsonb);

Helper function

CREATE FUNCTION jsonb_object_keys_as_array(j JSONB)
RETURNS text[]
AS $$
  SELECT ARRAY( SELECT jsonb_object_keys(j) AS d );
$$
LANGUAGE sql
IMMUTABLE;

Exclusion constraint attempt.

ALTER TABLE categories
  ADD EXCLUDE
  USING gist (jsonb_object_keys_as_array(title) WITH &&);

ERROR:  data type text[] has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

I'm asking a more specific question on this right now.

Other solutions

As it pertains to your problem, I believe currently you'll have to resort to a less than optimal solution like,

Implement this with triggers.
Create a function that may or may not use a table or enum to resolve text -> int and then map over text[] returning int[] and use intarray which will permit a GIST index and &&

Or, of course, you can just fix your schema.

Trigger

This method should also be able to make use of a GIST index on catagories(title)

CREATE OR REPLACE FUNCTION resist_duplicate_languages()
RETURNS TRIGGER
AS $$
  DECLARE
    collision bool DEFAULT false;
  BEGIN
    EXECUTE
      FORMAT('SELECT true FROM %I.%I WHERE ctid <> $1 AND title ?| jsonb_object_keys_as_array($2) LIMIT 1', TG_TABLE_SCHEMA, TG_TABLE_NAME )
      INTO collision
      USING NEW.ctid, NEW.title;

    IF collision IS true THEN
      RAISE EXCEPTION 'Can not collide on language %.%(title) languages %', TG_TABLE_SCHEMA, TG_TABLE_NAME, jsonb_object_keys_as_array(NEW.title);
    END IF;

    RETURN null;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER resist_duplicate_languages
  AFTER INSERT OR UPDATE OF title
  ON categories
  FOR EACH ROW
  EXECUTE PROCEDURE resist_duplicate_languages();

Testing

//works
INSERT INTO categories VALUES ('{"fr":"We surrender and eat cheese"}');

//does not work a second time
INSERT INTO categories VALUES ('{"fr":"We surrender and eat cheese"}');
ERROR:  Can not collide on language public.categories(title) languages {fr}


In the msdb database, there is a table named restorehistory:

declare @DB sysname = 'MyDB';
select * from msdb.dbo.restorehistory where destination_database_name = @DB;

This is a table that people with sufficient privileges can clear out, but if the restore was recent and you don't have a job which clears out this table (or if it hasn't run between then and now), you should be able to see the login which performed the restore.

EDIT

You can also join it to a couple of other tables (if they have relevant data):  backupset and backupmediafamily.  If there are records there--with the same retention caveats as before--they'll tell you more about the backup file(s) used to restore:

declare @DB sysname = 'MyDB';
select
    rh.destination_database_name,
    rh.user_name,
    bs.name as backup_set_name,
    bs.user_name as backup_set_username,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.database_name as backup_set_database_name,
    bs.server_name,
    bs.machine_name,
    bmf.physical_device_name,
    bmf.device_type,
    case bmf.device_type
        when 2 then 'Disk'
        when 5 then 'Tape'
        when 7 then 'Virtual Device'
        when 105 then 'Permanent backup device'
        else 'UNKNOWN'
    end as device_type_desc
from
    msdb.dbo.restorehistory rh
    left outer join msdb.dbo.backupset bs on rh.backup_set_id = bs.backup_set_id
    left outer join msdb.dbo.backupmediafamily bmf on bs.media_set_id = bmf.media_set_id
where
    rh.destination_database_name = @DB;

That way, you could also filter on device type or file location.


What data gets passed between mirroring principal and secondary?

As noted it is based on the mode mirroring is configured to, but overall the concept of mirroring you are probably closer with statement one.

However, as noted in the documentation for mirroring:

  Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.

So I would necessarily say it rolls in the transactions to the database on the secondary but it applies them to the secondary. Then depending on which mode you are in would be when the transaction is committed to the database on the primary (synchronous or asynchronous).

So I would probably state it as:

  The primary server reads the transaction log of the mirrored database for a insert/update/delete occurs, that transaction is sent to the secondary and applied to the mirroring database. Based on synchronous or asynchronous the transaction on the primary (or mirrored database) is then committed.


SQL Server Merge Replication - Safely shrink transaction log

Yes - you can shrink the transaction log without it breaking replication.  You can also move the publisher files to a different HDD by using ALTER DATABASE MODIFY FILE syntax, setting the publication database offline, copying the files to the new location, and bringing it back online.

I would start with shrinking the transaction log and see if that helps.


Cross-Domain SQL Server Service Accounts

This is most likely an accounts permission issue.

There are several install logs and utilities that you could check that would help you identify the required permissions.

Sql Server Fails if the setup account doesn't have rights. This article also links to a utility to check the account rights.

The installation log may also be available to view and may give some hints as to why it is failing.

Have you tried the Windows Server Event logs?


Converting Cursor based filtering logic to CTE based

If I understand correctly, and you want to insert 10 values of the 2nd table for every iteration of the cursor (or cte), you can use this:

; WITH myCTE (value) AS
    ( SELECT value FROM sometable )
INSERT INTO @TOP10
    SELECT t.Value1, t.Value2, t.Value3, t.Value4
    FROM myCTE AS mc
      CROSS APPLY
        ( SELECT TOP (10)
              bt.Value1, bt.Value2, bt.Value3, bt.Value4
          FROM BaseTable AS bt
          WHERE bt.SomeVariable = mc.value
          ORDER BY <SomeColumns>                     -- needs something here
        ) AS t ;

You also need an ORDER BY in the subquery with TOP to be consistent. Without order by, the database engine is free to return any 10 rows that match the conditions.

It could also be done using ROW_NUMBER():

; WITH myCTE (Value1, Value2, Value3, Value4) AS
    ( SELECT bt.Value1, bt.Value2, bt.Value3, bt.Value4,
             Rn = ROW_NUMBER() OVER (PARTITION BY bt.SomeVariable
                                     ORDER BY <SomeColumns>)
      FROM sometable AS s
        JOIN BaseTable AS bt
          ON bt.SomeVariable = s.value
    )
INSERT INTO @TOP10
    SELECT Value1, Value2, Value3, Value4
    FROM myCTE
    WHERE Rn <= 10 ;


How to get row(s) with max or null value in Postgres

In order to answer your question, I did the following (fiddle available here):
CREATE TABLE test
(
  fid INTEGER NOT NULL,
  h_id INTEGER,
  CONSTRAINT h_id_uq UNIQUE (h_id)
);

populate the table - added a couple of extra records for testing purposes.
INSERT INTO test VALUES
(13374976, 58280198),
(13374976, 58372885),
(913374976,  NULL  ),
(23, NULL),
(913377411, 58280199),
(13377411, 66666666),
(13377411, 77777777);

And the ran the following query:
SELECT fid, h_id FROM test
WHERE h_id =
(
  SELECT MAX(h_id)
  FROM test
)
UNION
SELECT fid, h_id
FROM test
WHERE h_id IS NULL;

Result:
fid            h_id
23             NULL
13377411       77777777
913374976      NULL

This provides the answer you require - there is an assumption that h_id is UNIQUE or else you'll have to do a GROUP BY fid - but that's a different question.
In future, could you please provide a fiddle with your table structures and your (presumably) sample data - it makes life much easier for those trying to help you and eliminates duplication of effort as well as providing a Single Source of Truth.
Also, although it doesn't make much difference in this case, could you also please provide your PostgreSQL version as a tag or in the question itself - it can be helpful!
p.s. welcome to the forum!


How can I write a window function using something like row_number to increment only on discrete values?

You want dense_rank() instead of row_number()

select my_date,
       dense_rank() over(order by my_date asc)
from my_table


How can I disable parallel queries in PostgreSQL?

From the docs,

  max_parallel_workers_per_gather (integer) Sets the maximum number of workers that can be started by a single Gather or Gather Merge node. Parallel workers are taken from the pool of processes established by max_worker_processes, limited by max_parallel_workers. Note that the requested number of workers may not actually be available at run time. If this occurs, the plan will run with fewer workers than expected, which may be inefficient. The default value is 2. Setting this value to 0 disables parallel query execution.

So to disable the parallel query planning use,

SET max_parallel_workers_per_gather = 0;

You can see that here, same query,

                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..207292.03 rows=1000 width=15) (actual time=0.082..4715.721 rows=666667 loops=1)
   Filter: (lower(x) ~~ '%999.com'::text)
   Rows Removed by Filter: 9333333
 Planning time: 0.083 ms
 Execution time: 4736.202 ms


SQL Server - Copy data from one database to another - identical tables, new identity values needed

Unless I'm missing something your problem is not moving the data it's dealing with the identity values that are already set up.  If that is the case then try this.

Pick a value greater than your current ident values on either DB.  I would pick a round value, say 1,000,000.
Pick the ident values you want to change (for example if you have lookup tables that are the same for both DBs then you probably want to leave them the same.)
When you move your data add the value you selected in step 1 to the idents value you want to change as you move them.

As long as you are consistent in the value you are adding all of your relationships will stay the same into the combined DB.  So for example

You have an employee table with a current max id of 200,000 in DB A and 1,400,000 in DB B.  You decide to move the data from DB A to DB B because it means moving less data.  As you move your employee table you add 2,000,000 to the Employee_Id column.

USE DatabaseB
GO

SET IDENTITY_INSERT Employee ON

INSERT INTO Employee (Employee_Id, Other_Columns)
SELECT Employee_Id + 2000000, Other_Columns
FROM DatabaseA.dbo.Employee

SET IDENTITY_INSERT Employee OFF
GO

And last piece of advice back up everything before you start in case you make a mistake :)


mysql trigger AFTER INSERT if condition syntax error

Based on your own answer, the issue appears to have been with this line:

IF(TIMESTAMPDIFF(HOUR, `triedTime`, CURRENT_TIMESTAMP()) < 60000) THEN

The problem is the `triedTime` reference. According to the manual:

  Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger.

That is to say, without either NEW or OLD you cannot access columns of the affected rows. So, in the above shown line you probably want to use the NEW keyword:

IF (TIMESTAMPDIFF(HOUR, NEW.`triedTime`, CURRENT_TIMESTAMP()) < 60000) THEN

Your own method of looking the value up in the table works as well, only it means an extra table hit, which may affect performance.


As far as I understood the task you want to calculate average speed for every 4 hours as example. Means 0:00-3:59 o'clock, then 4:00-7:59 etc. So, probably it'll help you if you will use date part and floor functions as

floor(date_part('hour', timestamp) / 4)

then you'll get a rank of time gap.


You have all the information you need to populate the new database from the old one with 4 insert statements:

create table team_ids (id serial, name TEXT)

insert into team_ids (name)
select distinct supervisor_name from employee

-- now supervisors have ids assigned by "serial" type

insert into person (id, name, old_ident)
select ident, name, ident from employee
union
select ident, supervisor_name, ident from employee

insert into team (id) -- meh
select id from team_ids

insert into teammember (person_id, team_id, role)
select e.ident, t.id, 'e')
from employee as e, join team_ids as t
on t.name = e.supervisor_name
union -- and, I guess
select t.id, t.id, 'm')
from team_ids as t

You may have to adjust to taste.  I'm assuming employee.ident can be mapped onto person.id, and that your DBMS allows assigning values to columns with auto-generated values.  Except for that, it's just basic SQL, nothing fancy and, of course, no loops.

Additional commentary:

The 'team' table might be (more conventionally) renamed to department.
A SERIAL (with its 2 billion possibilities) should be plenty, no need for a BIGSERIAL.
There appears to be no database mechanism to enforce 1:1 cardinality of manager to team. Doesn't every team need a leader, by definition? Is there not a CHECK or FOREIGN KEY constraint for teammember.role? Perhaps the question simplified these details away.
The "teammember" table name would more conventionally have a word boundary, for example TeamMember or team_member.


Determing when a stored procedure was last compiled

For compile times of procs currently residing in proc cache, see master..monCachedProcedures.CompileDate


SQL Performance Monitor vs DMV Dynamic Management Views

I think the best advice is to use everything that is available, or at least the bits that work best for your workload.

Also consider extended events (often abbreviated to XE) which while harder to get into initially can offer a wealth of information about specific events rather than just counts of things happening as you'll get from PerfMon, and imposes less load than constantly polling DMVs.

Use PerfMon to monitor the general health of the SQL instances, and when that or other indications suggest a problem use information from DMVs to look at what is happening now or has been happening recently, and extended events to "catch things live" and record them for later analysis.

[SQL Profiler can do a fair amount of what XE does too, and is easier to get started with, but it is deprecated so hasn't been updated much since 2012, may vanish completely in later releases, and isn't available against Azure databases at all (apart of course from on-prem SQL Server running in VMs)]


Multicondition Subquery Max Date and Item Number

Is this what you are looking for?

WITH MaxSalesDatesPerCustomer
AS
(
SELECT CustomerID, MAX(SalesDate) AS MaxDate
FROM dbo.PurchaseTransactions
GROUP BY CustomerID
)
SELECT MS.CustomerID, MS.MaxDate AS SalesDate, MAX(OrderNumber /*???*/) AS LargestOrderNumberForMaxDatePerCustomer
FROM MaxSalesDatesPerCustomer AS MS
     INNER JOIN
     dbo.PurchaseTransactions AS PT
     ON PT.CustomerID = MS.CustomerID
WHERE MS.MaxDate = PT.SalesDate
GROUP BY MS.CustomerID, MS.MaxDate


Find queries executed by an user in sql server

You would need to start logging account activity of accounts that you don't trust to a table for a complete list.  All of the other options (like querying dmvs) will give you temporal data and there's no guarantee that it is complete.

Here is an example of what you are looking for but consider creating a job or something that logs these results to a table that only you have access to view or even see.  You could put a filter that only stores accounts that you are wanting to monitor(check out the second where clause that's commented out) if storage is an issue.

This queries session info, although you might want to only select the columns that you are interested in like Database Name, login_name, Loginin_Time, and Query.

/******************************************************
Script : Findout Who did what ?
Author : Kin Shah .. written for dba.stackexchange.com
*******************************************************/

USE master
go
SELECT sdest.DatabaseName
    ,sdes.session_id
    ,sdes.[host_name]
    ,sdes.[program_name]
    ,sdes.client_interface_name
    ,sdes.login_name
    ,sdes.login_time
    ,sdes.nt_domain
    ,sdes.nt_user_name
    ,sdec.client_net_address
    ,sdec.local_net_address
    ,sdest.ObjName
    ,sdest.Query
FROM sys.dm_exec_sessions AS sdes
INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id
CROSS APPLY (
    SELECT db_name(dbid) AS DatabaseName
        ,object_id(objectid) AS ObjName
        ,ISNULL((
                SELECT TEXT AS [processing-instruction(definition)]
                FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
                FOR XML PATH('')
                    ,TYPE
                ), '') AS Query

    FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
    ) sdest
where sdes.session_id <> @@SPID
--and sdes.nt_user_name = '' -- Put the username here !
ORDER BY sdec.session_id


Create a SQL Server Redirect (Alias / Linked Server)

This might be what you are looking to do but as the answer notes you will only be able to have a single instance running after the change.

Pointing all connections to the default instance


Selecting from a view shows the wrong set of columns

First, stop using SELECT * in your views. I talk about this quite a bit here:

Bad habits to kick : using SELECT * / omitting the column list

Next, run sp_refreshview or sp_refreshsqlmodule against each view that references a table (or another view!) that you have changed, e.g.:

EXEC sp_refreshview N'dbo.viewname';

If you want to generate a script that refreshes all of the views in the system:

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

SELECT @sql += N'
  EXEC sp_refreshsqlmodule N'''
  + QUOTENAME(s.name) + '.' + QUOTENAME(v.name) + ''';'
FROM sys.schemas AS s
INNER JOIN sys.views AS v
ON s.[schema_id] = v.[schema_id];

EXEC sp_executesql @sql;

(You may want to run it twice in case there are circular or out-of-order dependencies.)

But this is a temporary fix. You will have to repeat this every time you change any table that might have views that reference it, then go find out which views are involved. This is not the right way to approach schema design. AT ALL. I wrote about a DDL trigger that would help automate this for you, but this is really the wrong approach - just stop using SELECT * in your views!

Keeping sysdepends up to date in SQL Server 2008

You may also want to consider adding WITH SCHEMABINDING to the definition of all of your views. This does two things:

Prevents you from using SELECT * in the view. This is A GOOD THING™.
Prevents you from changing underlying tables without knowing exactly which views would be affected by the change. This is also A GOOD THING™.


Show recent select queries on Firebird database file

There is no history of queries in Firebird. As indicated by Verace in the comments, you can look at one of my answers on Stack Overflow that provides an alternative: using the trace API.

However the trace API requires active monitoring, which has some overhead. As an alternative you could also look at FBScanner, which acts as a proxy between Firebird and your client, which allows it to register the prepared and executed queries.


If this is a simple Excel file load, you can just use the Import Wizard to create an SSIS package for you (available by right clicking the Database, and selecting Tasks -> Import Data) and even save that package for future use.

If this is a process where you need to take data from an Excel file, transform it, then load it, then yes a custom SSIS package will work better.  You should be able to add a new connection and indicate that it is an excel file using the "new Connection Manager" wizard in your SSIS package.


DELETE FROM Line_Items
WHERE Line_Items.Order_Number -- this should be an ON DELETE CASCADE FK.
NOT IN
(
  SELECT id
  FROM Orders
); -- not tested, don't have running system, but this or similar should work.

Note: Don't call a table "Order" - makes debugging difficult and system non-portable. Personally, I'm against the use of plural names for tables - I usually call it System_Order (or Shop_Order) or similar.


Failure with ongoing replication from SQL Server RDS using AWS DMS

Need to disable SQL Server Change tracking and enable RDS Change Tracking.

Find any tables which are enabled for CDC with:

SELECT s.name as Schema_name, t.name AS Table_name, tr.* FROM sys.change_tracking_tables tr INNER JOIN sys.tables t on t.object_id = tr.object_id INNER JOIN sys.schemas s on s.schema_id = t.schema_id;

Then for each of those tables table run:

ALTER TABLE dbo.Products DISABLE CHANGE_TRACKING;

Disable CDC on the database:

alter database AdventureWorks set change_tracking = off

Enable RDS CDC on the database:

exec msdb.dbo.rds_cdc_enable_db 'AdventureWorks'

Enable RDS CDC for each table:

exec sys.sp_cdc_enable_table
   @source_schema           = N'dbo'
,  @source_name             = N'Products'
,  @role_name               = NULL;

Finally rerun your DMS tasks and they should work.


Your question specifies:

  ... a query looking for all documents where a certain field, whose value is an array, is empty.

Your query is count all documents where the array is not empty, is this intended?

Depending upon the cardinality of the values of this field, this could be causing these values to be expensive.

To query for empty arrays, the operation would be:

 db.collection.explain().count( 'things.titles' : [ ] } )

Note that there are edge cases to this query. For example, the following would be returned:

  { "_id" : ..., "things.titles" : [ [ ] ] }

The index usage of the count operation can be determined using the collection.explain() function.

For example:

 db.collection.explain().count({'things.titles': {'$ne': [] }})

The output will provide information showing the index bounds being used (if they exist).


MySQL query r.e covering index with Primary Key

Short answer:  Yes.

Long answer:

You are using InnoDB, correct?  (Run SHOW CREATE TABLE j_data to find out what the Engine is.)

In InnoDB, each "secondary index" (contrast "primary key") silently contains a copy of the primary key.

So...  INDEX(j_name) happens to be identical to INDEX(j_name, j_id).

Tip:  At some point you will realize that prefixing all columns with the table name ("j_") is unnecessary clutter.


How to change all columns from one type to another, changing all FK columns as well?

This might work:

(backup db just in case)
DISABLE FKs.
Using a suitable SELECT into information_schem.columns, construct the ALTER TABLE statements.
Enable FKs.


Why availability group resource goes offline in WSFC?

Seems it's your follow-up question of this.. Hope you got root cause of the issue using FailoverDetection tool. Perhaps the cause could be from either Internal Health Issue or system wide issues as detailed here...

Why is that happening?

As you found in error log, the AG trying to take recovery actions, as part of that either AG resources could fail-over to new primary replica as AG is determined unhealthy and will report failures on IsAlive calls, or if the quorum lost, resources goes offline.
Further reading on lease-time and health-check timeout

If one of the cluster networks is down, then the cluster role goes to offline state?

Yes, if the quorum lost.
For instance, if the WSFC formed with 2 nodes (total 2 votes), to have quorum to be healthy, always more than half of the quorum vote nodes to be alive. In this case if one node is down cluster resource may go off-line as the cluster consider to be unhealthy while there is 1 vote is not more than half of the total votes.
To maintain healthy-cluster, configure a file-share-witness (Node and File Share Majority), so that the quorum can have an additional vote from file-share to support the cluster to be healthy. If one node is down there will be 2 valid votes (1 Node + 1 File-share) out of 3.
In Summary:

If you found that the root cause is quorum lost, you may need to configure proper quorum mode.
If it's Network interface failure, consider having dedicated NIC for the cluster heart-beat communication (screenshot for your reference).
If the cause is aside from those two, you need further investigate to find bottlenecks of resource pressure in servers: some free-tools from Microsoft (Ref1, Ref2, Ref3), or third-party tools can help.

Multiple NIC for WSFC:


AWS limits the IOP size to a maximum of 16KB, so whatever IOPS you buy, you will get throughput capped at 16KB times that number. So if you buy 1,000 IOPS on EBS, this will max out at 16MB/s.

If your application does random I/O using smaller blocks that aren't adjacent, e.g. 8KB, you will get maximum throughout of 8MB/s from the same EBS volume.

Historically, IOPS on spinning disks were limited by the rotational speed of the disk. In a 7200rpm disk, the correct sector comes around only 120 times per second.

A real NVMe SSD can handle much faster throughout at lower latencies than AWS EBS.

AWS is slow and expensive - learn to live with it. That is the price you pay for the convenience of not having to host and maintain your own hardware. How much slower cloud and VMs are than bare metal? This will depend to some extent on the nature of your workload, but in general, for equal spec, VMs are often much slower than bare metal.


What are the potential issues after data or log disk failure?

what are the possible SQL failure modes this could present?  Does the transaction log / SQL server architecture account for this type of whole disk failure?

SQL Server uses a write-ahead log (WAL), so any completed transactions should have at least been flushed to disk (unless you are using delayed durability).  This means that when your database comes back online, all committed transactions will be replayed from the log file.  In-progress transactions will be rolled back as well.

  Is it possible to have data loss in this situation (without considering corruption of the disk itself)

Despite the WAL architecture, I'm sure Windows didn't appreciate the abrupt disk loss.  It certainly seems possible that data could have been corrupted - for instance, if a single 8 KB page was halfway done being flushed to the physical log file, and the disk was removed at that instant, it seems like the log would be corrupted.

If you're using the Full recovery model, you could likely still do a point in time recovery up to right before the corruption.  But there would be some data loss due to the partial write.

  And what checks should/can be performed after such an event?

There's really not much to be done other than running DBCC CHECKDB.  This will check the database to see if it was corrupted during this unusual event.

Another option would be to restore a backup (from before this event) to a separate database / server, and then use a comparison tool (like SQL Data Compare from Red Gate) to see if there is any data that's different between the backup and the current database.


As far as I can see, your basic design shouldn't be anymore complex than I have drawn in the picture below:

This assumes that you have only one entity representing a person ("Player", could be "User" though), that decks MUST belong to a person and that multiple people can enter an Event as Participants. I assume they bring their own decks with them and that Events cannot own Decks.


How to avoid locks by large transactions?

I don't think you have to force all of your deletes to occur within a single, monolithic transaction. Instead of having a transaction that does:

BEGIN TRANSACTION;
  DELETE all the things from child table 1;
  DELETE all the things from child table 2;
  ...
  DELETE all the things from child table N;
  DELETE all the things from parent table;
COMMIT TRANSACTION;

Why not delete in chunks? You can play around with the TOP (?) parameter based on how many rows lead to what kind of duration of transaction (there is no magic formula for this, even if we did have a lot more information about your schema). Pseudo-code:

DECLARE @p TABLE(p INT PRIMARY KEY);

SELECT @rc = 1;

WHILE @rc > 0
BEGIN
  DELETE @p;

  INSERT @p SELECT TOP (?) primary_key FROM parent table
    WHERE (clause that defines the data to be deleted);

  SET @rc = @@ROWCOUNT;

  BEGIN TRANSACTION;
    DELETE child table 1 WHERE parentID IN (SELECT p FROM @p);
    DELETE child table 2 WHERE parentID IN (SELECT p FROM @p);
    ...
    DELETE child table N WHERE parentID IN (SELECT p FROM @p);

    DELETE parent table WHERE parentID IN (SELECT p FROM @p);
  COMMIT TRANSACTION;
  -- to minimize log impact you may want to CHECKPOINT
  -- or backup the log here, every loop or every N loops
END

This may extend the total amount of time that the operation takes (especially if you backup or checkpoint on each loop, or add an artificial delay using WAITFOR, or both), but should allow other transactions to sneak in between chunks, waiting for shorter transactions instead of the whole process.

I wrote a lot more about this technique here.