( Please note that the original wording of the Question was: "Is SYSUTCDATETIME a safe clustered index?" )
What exactly do you mean by "safe"? A Clustered Index doesn't need to be unique, so it is "safe" in the sense that it won't break anything. But it can't be a Primary Key since it can't be guaranteed to be unique. Just try the following and you will see that the value coming back from the function is the same across all rows:
SELECT SYSUTCDATETIME(), * FROM sys.objects;

And so the question is then: what exactly would you gain by using SYSUTCDATETIME() instead of an IDENTITY? I assume you are using a DATETIME2 column, which is 8 bytes, yet you could most likely get away wit a 4 byte INT for the IDENTITY column.
So, I would go with an INT populated via IDENTITY as it is:

smaller (4 bytes instead of 8; and that is 8 bytes in the ideal case of having unique values, but when there are duplicates, a "uniquifier" value is added to any duplicate rows that is an extra 4 bytes)
guaranteed unique (not only better for a Clustered Index, but eligible to be a PK).
they would essentially be in the same order anyway (although it is possible that occasionally they will not match in order)

UPDATE
No, I personally would not trust a value, even with such high resolution as DATETIME2, to be a Primary Key since it is still possible for two operations to happen at the same microsecond. AND, you cannot guarantee that at no point in the future will someone or some process insert more than 1 record in a single statement.
UPDATE 2
@ypercubeᵀᴹ brings up a good point in a comment below (a comment that will soon magically disappear now that I mention it here ;-):

you could use the datetime column for the Clustered index and the identity column for the PK. (That's sometimes good, if all or almost all your queries depend on ordering by the datetime. So the occasional not matching of the two orders won't hurt performance.)

Yes, this is sometimes a good option, but it is not without potential consequence due to the Clustered Index key(s) getting copied into Non-Clustered Indexes:

If there is only one Non-Clustered Index on this table, then the space taken up by the index will be mostly the same either way. But because the Clustered Index keys have to ultimately be unique (at least internally) as they are the RowID for the Non-Clustered Indexes, for all rows with duplicate DATETIME2 values there will be that extra 4 byte "uniquifier" added. And it will be added twice: once to the Clustered Index, and once to the Non-Clustered Index for the IDENTITY PK.

If there are several Non-Clustered Indexes, then the impact on space used will be:
(4 bytes minimum + 4 bytes for any duplicated Clustered Index key values)
* number_of_indexes
* rows_in_table (less if filtered indexes are used)
These numbers might seem small to some, but if we are working with hundreds of millions of rows, they do add up. And for those who mistakenly believe that "disk is cheap" please consider that a) Enterprise storage is not cheap, and b) disk operations such as index maintenance, etc are also not cheap (less of an issue with SSD, but still). For a more detailed analysis regarding the down-stream effects of data modeling decisions, please see the following article I published on SQL Server Central: Disk Is Cheap! ORLY? (that site requires free registration to view content).

This is not to say "don't do it", but more so "do it only if the benefit outweighs the cost".
UPDATE 3
For the sake of completeness, I should mention that one issue with using DATETIME2 values in the hopes of separating individual INSERT and/or UPDATE statements is that they are not as granular as they appear to be. This is an issue between "resolution" and "precision". The "precision" of DATETIME2 is 7 decimal places. But that doesn't mean that the most granular time value represented is incremented at the next value as it happens. This is just like with DATETIME values that are precise to the millisecond, you will never get a value that has anything but a 0, 3, or 7 in the millisecond position:
SELECT CONVERT(DATETIME, '2016-04-14 20:30:40.121'), -- 2016-04-14 20:30:40.120
       CONVERT(DATETIME, '2016-04-14 20:30:40.122'), -- 2016-04-14 20:30:40.123
       CONVERT(DATETIME, '2016-04-14 20:30:40.128'); -- 2016-04-14 20:30:40.127

Likewise with DATETIME2 values, there is a moment when a call to SYSUTCDATETIME() is completely accurate. But because the value does not get refreshed at the next microsecond, the reported value will stay constant for a moment until the next time it is refreshed. This is why the simple test that @Paul posted in a comment on the Question gets PK violations, even though the two INSERT statements are arguably running at least 1 microsecond apart.
Please see the following Stack Overflow answer for more details, including a link to a project on CodeProject.com that has code that can be used in SQLCLR to overcome this limitation:
SQL Server CLR Integration not calling system time as expected


MySQL Cluster work well but ndb_desc doesn't work

The cause of problem is no free nodeid to allocate for ndb_desc. ndb_desc will attempt to connect to management node as api node thus it need free node. To make free node for ndb_desc, management node need to have empty [mysqld] in global config config.ini (in production, I think the use empty [mysqld] is no safe because any client is possible to connect to managment node to get free nodeid)


I adapted the example at http://www.postgresql.org/docs/8.4/static/queries-with.html to your case:

WITH RECURSIVE search_graph(id, depends, depth, path, cycle) AS (
        SELECT g.Col1, g.depends, 1,
          ARRAY[g.Col1],
          false
        FROM deps g
      UNION ALL
        SELECT g.Col1, g.depends, sg.depth + 1,
          path || g.Col1,
          g.Col1 = ANY(path)
        FROM deps g, search_graph sg
        WHERE g.Col1 = sg.depends AND NOT cycle
)
SELECT distinct id FROM search_graph where cycle = true;

results:

ID
 4
 2

for the first example,

ID
 4
 2
 5

for the second

You can find the sql fiddle at http://sqlfiddle.com/#!15/87a96/2


Something like the following (untested) should do what you want:
select name, sum(mm)
from a join b using (ref)
group by name;


MySQL Unions/Subselects not utilizing keys from associated tables

The optimizer is not that clever - yet (see footer).

You could still use UNION, if you rewrite the query (a job that could/should be done by a decent optimizer):

    SELECT
    `part_number`,
    `part_manufacturer_clean`,
    `part_number_clean`,
    `part_heci`,
    `part_manufacturer`,
    `part_description`
    FROM `new_products` AS `a`
    WHERE `part_manufacturer_clean` = 'adc'
UNION
    SELECT
    `part` as `part_number`,
    `manulower` as `part_manufacturer_clean`,
    `partdeluxe` as `part_number_clean`,
    `heci` as `part_heci`,
    `manu` as `part_manufacturer`,
    `description` as `part_description`
    FROM `warehouse` AS `b`
    WHERE `manulower` = 'adc' ;

You can also try latest MariaDB versions (5.3 and 5.5) that have several improvements on the optimizer (if changing to MariaDB is an option).

You can also try MySQL 5.6 (still in beta) which has some improvements on the optimizer, too.


Execute SSIS (package) in SSMS using T-SQL and return a value

Fun question!
When you hit F5/Debug within Visual Studio, that spins up a special environment to allow for breakpoints, debugging and graphical interaction (MessageBox). That can't happen when the package runs on the server because who's going to log into the server and hit the [Ok] button on that message box? DTS, the precursor to SSIS, did allow you to have message boxes and yes, I've logged onto servers only to be assailed by all these popups...
Instead, when the SSIS package is running in unattended mode, if you left code in there that interacts with the UI, the package run-time will throw an exception. Instead, what we have from a command line execution perspective, is what you see in the Output window. The Results window is a graphical representation of package execution progression. It's fine, but you can't copy from there and it truncates messages. Instead, click to the Output window to see and copy all the information.
How do things "get" to the Output window? Events. In your code, instead of writing MesssageBox.Show(id); raise events instead.
bool fireAgain = false;
Dts.Events.FireInformation(0, "ID results", id.ToString(), "", 0, ref fireAgain);

Now when you run your packages, assuming you request that Information events are reported
dtexec.exe /file foo.dtsx /rep eiw

you will see the value in your run log. This covers Visual Studio execution, but what about the SSISDB? That's a different animal.
The SSISDB captures all the output information from SSIS packages and stores it into its own structure. I tech reviewed Tim Mitchell's book "The SSIS Catalog: Install, Manage, Secure, And Monitor Your Enterprise ETL Infrastructure" and he does an excellent job covering the catalog's structure and how to use it. (I get no compensation for mentioning the book).
In the above TSQL, you will see that creating an instance of execution generates a bigint that is assigned into @execution_id. That is the key to the kingdom. Assuming the value is 531463, the following script would tease out the value I fired off in the Information event above
DECLARE @execution_id bigint = 531463;

SELECT
    -- Script Task:Information: 42
    OM.message
,   *
FROM
    SSISDB.catalog.operation_messages AS OM
WHERE
    OM.operation_id = @execution_id
    AND OM.message_type = 70 /*Information*/ ;

SELECT
    -- Script Task:Information: 42
    EM.message
    ,   EM.*
FROM
    SSISDB.catalog.event_messages AS EM
WHERE
    EM.operation_id = @execution_id
    AND EM.message_type = 70 /*Information*/ ;

event_messages and operation_messages are generally the same data, just in different views but you'd see the message value in an information message type (70) is recorded as Script Task:Information: 42 which shows the 42 which was my id.  If your SQL Server supports STRING_SPLIT, it's a simple modification to extract the value itself
DECLARE @execution_id bigint = 531463;

SELECT
    -- Script Task:Information: 42
    OM.message
,   D.value
,   *
FROM
    SSISDB.catalog.operation_messages AS OM
    CROSS APPLY
(
    SELECT
        *
    FROM
    (
        SELECT
            SS.*
        ,   ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) AS row_num
        FROM
            STRING_SPLIT(OM.message, ':') AS SS
    ) AS SSO
    WHERE
        -- skip the first two elements as they are Script Task and Information
        SSO.row_num > 2
) AS D
WHERE
    OM.operation_id = @execution_id
    AND OM.message_type = 70 /*Information*/ ;

Finally, you could always control your own destiny by having a custom table for storing these values.
CREATE TABLE dbo.PackageRunLog
(
    PackageName nvarchar(255) NOT NULL
,   ExecutionId bigint NOT NULL
,   ExecutionDate datetime2(0) NOT NULL
,   MyId int NOT NULL
);

Use an Execute SQL Task to populate that and the first 3 parameters would by System scoped variables from the SSIS package: System::PackageName, System::ServerExecutionID, System::StartTime. The final would then be @[User::Id] or whatever you needed.


I dropped MySQL's `performance_schema` database, how can I create it?

Tables in the performance_schema database are a collection of views and temporary tables that do not store data permanently. The mysql_upgrade command will restore the performance_schema database

From the shell

mysql_upgrade --user=root --password=password


How can I find state name which has the most vehicle accidents in the database?

A join should do, and then select the state name instead.

SELECT US.STATE_NAME, COUNT(*) AS FREQUENCY
FROM ACC_DATA AD, US_STATES US
WHERE AD.STATE_CODE = US.STATE_CODE
GROUP BY US.STATE_NAME
ORDER BY COUNT(*) DESC


Combine datetime + string to make a proper datetime

This will also deal with values < 10 AM that don't have all 4 digits. (Also that column should probably be CHAR(4) instead of VARCHAR(5) - or better yet, stop separating these and store a proper datetime value in the first place).

DECLARE @t TABLE(ColumnA DATETIME, ColumnB VARCHAR(5));

INSERT @t VALUES('20130701', '1330'),('20130701', '930');

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), ColumnA, 112)
    + ' ' + LEFT(RIGHT('0' + ColumnB, 4),2)
    + ':' + RIGHT(ColumnB, 2))
FROM @t;


How to view the original script that built a materialized view and that was created by another user?

Specify the owner in GET_DDL, or use ALL_MVIEWS instead of USER_MVIEWS:

select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MVIEW_NAME', 'OWNER') from dual;

select query from all_mviews
  where mview_name = 'your materialized view';


Allow only find and insert action to mongodb collection

I created role with your db.createRole(..., I didn't "need" to use your grantPrivilegesToRole command. Then I created user

db.createUser({"user":"test2",pwd:"password",roles:["master"]})

and changes to it with

db.auth("test2","password")

If I change to database test, command db.lock.insert({}) fails, as it should be, but if I change database to db_name, insert commands works fine and find command works

> db.lock.find({})
{ "_id" : ObjectId("5ae9a57a119b0cf4316e2964") }

Then If I try to remove that document

> db.lock.remove({})
WriteResult({
    "writeError" : {
        "code" : 13,
        "errmsg" : "not authorized on db_name to execute command { delete: \"lock\", deletes: [ { q: {}, limit: 0.0 } ], ordered: true }"
    }
})

Nor update command works

> db.lock.update({"_id" : ObjectId("5ae9a57a119b0cf4316e2964")},{$set:{"something":"somewhere"}})
WriteResult({
    "writeError" : {
        "code" : 13,
        "errmsg" : "not authorized on db_name to execute command { update: \"lock\", updates: [ { q: { _id: ObjectId('5ae9a57a119b0cf4316e2964') }, u: { $set: { something: \"somewhere\" } }, multi: false, upsert: false } ], ordered: true }"
    }
})

"Stupid" question: Have you remember to enable "authentication" (--auth -parameter) at your mongod? Because if no authentication, no authorization...


How do I connect to localDB from SQL Server 2014 Express

SQL Server Express LocalDB (commonly referred to as just "LocalDB") is not a service-based application: it does not show up in "Services", nor does it start up when the machine starts or even stay running until manually shut-down.

LocalDB is user-mode background process. It starts either when the first request to connect comes in (assuming the request is using the "(localdb)\InstanceName" syntax) or when explicitly started via the SqlLocalDB.exe command-line utility.

So, if you know the instance name, just try to connect and the first time will take a moment while it starts up, but then subsequent connections (assuming consistent activity) will be quick.

If you don't know the name of the instance, you can get the list by using the SqlLocalDB.exe utility as follows:

C:\>SqlLocalDB i

That will return a list of instances that your Windows login has access to. If an instance name starts with .\ then it is a shared instance that other users can access as well (the default is that instances are private to the login that created them).

From there, you can get more info (including whether the LocalDB instance is "Running" or "Stopped") by doing:

C:\>SqlLocalDB i {InstanceName}

And you can start it by doing:

C:\>SqlLocalDB s {InstanceName}

Once the last connection to a LocalDB instance is closed, the instance will automatically stop after about 5 minutes (this is the default setting but can be configured to be longer). The delayed shutdown is to prevent needing to wait for the startup again for connections that come in shortly after the last connection ended.

LocalDB always has a "default" instance that is created when installing, even if you never use it. In SQL Server 2012 Express LocalDB, the default instance name is v11.0. In the 2014 version of LocalDB, the default instance name is MSSQLLocalDB. And, just as a period "." can be used to connect to a non-LocalDB default instance:

C:\>SQLCMD -S . -E -Q "SELECT @@SERVERNAME;"

it can also be used with the "(LocalDB)\InstanceName" syntax to connect to the default instance:

C:\>SQLCMD -S (LocalDB)\. -E -Q "SELECT @@SERVERNAME;"

Please note that you can only have one default instance of LocalDB specified as being the default instance. Meaning, if you have both the 2012 and 2014 versions of LocalDB installed, using (LocalDB)\. will refer to the MSSQLLocalDB instance and not the v11.0 instance.


Postgres 9.0 recovery from inadvertent drop-all-tables event

Your only chance would be to restore that one year old backup and recover that using all the wall files that are archived since then. For that, you need to run archived log mode have all those files. With a little luck they are in filesystem backups.
It would need some time to process.


Find substrings within between 2 string fragments

Use substring() with a regular expression instead:

substring(ls.attribute_actions_text FROM 'name="(.*?)"/>')

The dot (.) matches any character, *? is the non-greedy quantifier for a sequence of 0 or more matches and the parentheses (()) mark the substring to be returned.

Like your code, this selects the first string matching the pattern and does not look further.

Also, you don't need to make your expression a subquery, that adds nothing but overhead:

CREATE OR REPLACE VIEW vw_actions AS
SELECT ls.f_table_schema
     , ls.f_table_name
     , substring(ls.attribute_actions_text FROM 'name="(.*?)"/>') AS actions
FROM   layer_styles ls;

Quick test case (you should have provided):

SELECT *, substring(ls.attribute_actions_text FROM 'name="(.*?)"/>')
FROM  (
   VALUES
     ('bar name="View SHED Database"/> foo')
   , ('bar name="View SHED Database"/> fooname="View SHED Database"/>xx')
   , ('name="buntch a bull"/> fooname="View SHED Database"/>xx')
   , ('xxname="bla foo grr"/>')
   , ('')
   , (NULL)
   ) ls(attribute_actions_text)


(SQL Server /SSIS) Managing IS Catalog permissions on a shared server?

You probably want to add Users in the msdb database to the db_ssisltduser role as per the Microsoft Docs on Integration Services Roles. Under the Read and Write Actions section for the db_ssisltduser role it says the following permissions will be applied:

Enumerate own packages.
Enumerate all packages.
View own packages.
Execute own packages.
Export own packages.   Import packages.
Delete own packages.
Change own package roles.

You can also make a custom role that encompasses some of these permissions and/or other permissions as needed too.


If the SELECT works for you, then it will work for a hacker.

Think twice about breaking through this security barrier.


SQLite - is it faster to use SELECT NOT EXISTS / EXISTS or IN / NOT IN in a WHERE clause?

Let's assume that lol.haha is declared as NOT NULL, so that both queries give the same result.

When there is no index on lol.haha, the EXPLAIN QUERY PLAN outputs look similar:

> SELECT foo.bar,foo.baz FROM foo WHERE NOT EXISTS (SELECT lol.haha FROM lol WHERE lol.haha = foo.haha);
QUERY PLAN
|--SCAN TABLE foo
`--CORRELATED SCALAR SUBQUERY
   `--SCAN TABLE lol
> SELECT foo.bar,foo.baz FROM foo WHERE haha NOT IN (SELECT haha FROM lol);
QUERY PLAN
|--SCAN TABLE foo
`--LIST SUBQUERY
   `--SCAN TABLE lol

However, the correlated subquery scans the lol table once for every row in the foo table, while the NOT IN query puts all rows returned by the subquery into a temporary index, and then does all searches using that.
This can be seen in the output of the EXPLAIN command, if you really want to bother to decode it:

> EXPLAIN SELECT foo.bar,foo.baz FROM foo WHERE NOT EXISTS (SELECT lol.haha FROM lol WHERE lol.haha = foo.haha);
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     19    0                    00  Start at 19
1     OpenRead       0     4     0     3              00  root=4 iDb=0; foo
2     Rewind         0     18    0                    00
3       Integer        0     2     0                    00  r[2]=0; Init EXISTS result
4       Integer        1     3     0                    00  r[3]=1; LIMIT counter
5       OpenRead       1     2     0     1              00  root=2 iDb=0; lol
6       Rewind         1     13    0                    00
7         Column         1     0     4                    00  r[4]=lol.haha
8         Column         0     2     5                    00  r[5]=foo.haha
9         Ne             5     12    4     (BINARY)       51  if r[4]!=r[5] goto 12
10        Integer        1     2     0                    00  r[2]=1
11        DecrJumpZero   3     13    0                    00  if (--r[3])==0 goto 13
12      Next           1     7     0                    01
13      If             2     17    1                    00
14      Column         0     0     7                    00  r[7]=foo.bar
15      Column         0     1     8                    00  r[8]=foo.baz
16      ResultRow      7     2     0                    00  output=r[7..8]
17    Next           0     3     0                    01
18    Halt           0     0     0                    00
19    Transaction    0     0     6     0              01  usesStmtJournal=0
20    Goto           0     1     0                    00
> EXPLAIN SELECT foo.bar,foo.baz FROM foo WHERE haha NOT IN (SELECT haha FROM lol);
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     26    0                    00  Start at 26
1     OpenRead       0     4     0     3              00  root=4 iDb=0; foo
2     Rewind         0     25    0                    00
3       Noop           0     0     0                    00  begin IN expr
4       Once           0     12    0                    00
5       OpenEphemeral  3     1     0     k(1,B)         00  nColumn=1
6       OpenRead       1     2     0     1              00  root=2 iDb=0; lol
7       Rewind         1     12    0                    00
8         Column         1     0     1                    00  r[1]=lol.haha
9         MakeRecord     1     1     2     A              00  r[2]=mkrec(r[1])
10        IdxInsert      3     2     1     1              00  key=r[2]
11      Next           1     8     0                    01
12      Column         0     2     2                    00  r[2]=foo.haha
13      IsNull         2     16    0                    00  if r[2]==NULL goto 16
14      Affinity       2     1     0     A              00  affinity(r[2])
15      Found          3     20    2     1              00  key=r[2]
16      Rewind         3     21    0                    00
17      Column         3     0     3                    00  r[3]=
18      Ne             2     21    3     (BINARY)       00  if r[3]!=r[2] goto 21
19      Goto           0     24    0                    00  end IN expr
20      Goto           0     24    0                    00
21      Column         0     0     4                    00  r[4]=foo.bar
22      Column         0     1     5                    00  r[5]=foo.baz
23      ResultRow      4     2     0                    00  output=r[4..5]
24    Next           0     3     0                    01
25    Halt           0     0     0                    00
26    Transaction    0     0     6     0              01  usesStmtJournal=0
27    Goto           0     1     0                    00

With an index, both queries do an index search for every foo row, i.e., they execute in exactly the same way:

> create index i on lol(haha);
> SELECT foo.bar,foo.baz FROM foo WHERE NOT EXISTS (SELECT lol.haha FROM lol WHERE lol.haha = foo.haha);
QUERY PLAN
|--SCAN TABLE foo
`--CORRELATED SCALAR SUBQUERY
   `--SEARCH TABLE lol USING COVERING INDEX i (haha=?)
> SELECT foo.bar,foo.baz FROM foo WHERE haha NOT IN (SELECT haha FROM lol);
QUERY PLAN
|--SCAN TABLE foo
`--USING INDEX i FOR IN-OPERATOR


Is a 5+ column primary key bad for large (100 million+) table?

There are performance issues with very complex primary keys.  And It may not be defending against duplication as well as a simpler primary key might.

However, there is one design pattern that frequently yields tables with a primary key made up of six or so components.  It's star schema fact tables.  If the fact table of a star schema has six dimensions, then the primary key will have six components.  I've never seen a fact table with no declared primary key, and I think it's well worth the overhead, even though the ETL process still has to be quite carefully written.

Some reporting databases imitate the pattern of star schema even if it's not explicitly designed that way.

100 million + rows is not overly big for a fact table, especially with today's big data.


Full text index search returns an unexpected result

This is a known issue when using SQL Server 2008 to search for a number with more than nine digits. When using certain word-breakers, the number is stored in its scientific form e.g. nn5d68952e+009, leading to false-positive matches. One of the word-breakers affected is the neutral language (lcid 0):

-- Result includes nn5d68952e+009
SELECT display_term
FROM sys.dm_fts_parser('"5689523657*"', 0, 0, 0);

A language that does not behave this way is us-english (lcid 1033):

-- Result includes nn5689523657
SELECT *
FROM sys.dm_fts_parser('"5689523657*"', 1033, 0, 0);

There are several other workarounds and fixes mentioned in the KB article link above.


Database design problem: where to put UNIQUE constraint

Since orderInForm is the position of a particular block on a particular form, it should be a column in the FormBlock table, not a column in the Block table.  If orderInForm is in the Block table, a block would need to have the same position in every form it was on.

Once you move the column to FormBlock, you can then create a unique constraint on the combination of (form_id,orderInForm) on the FormBlock table.


Repeat all base table rows for EACH and don't aggregate the NULL from the join table

You need to create all customer_id/feedback_id combinations first and then left join to it.

Assuming there's a customer table with unique ids:

select x.customer_id, x.feedback_id, x.feedback_desc, cf.given_answers
from
 (
   select c.customer_id, f.feedback_id, f.feedback_desc
   from feedback as f
   cross join customer as c -- or (select distinct customer_id
                            --     from customer_feedback) as c
 ) as x
left join customer_feedback as cf
  on x.customer_id = cf.customer_id
 and x.feedback_id = cf.feedback_id ;

or, without a derived table:

select c.customer_id, f.feedback_id, f.feedback_desc, cf.given_answers
from feedback as f
  cross join customer as c -- or (select distinct customer_id
                           --     from customer_feedback) as c
  left join customer_feedback as cf
    on  c.customer_id = cf.customer_id
    and f.feedback_id = cf.feedback_id ;


Creating a stored Proc that creates new user and password

One mistake for sure:  Each of the 3 statements needs a ; at the end.

Possibly the syntax calls for literals, not variables for user_name and password.  So use CONCAT(), prepare, execute, and deallocate prepare to build and execute the CREATE and GRANT statements.


MS SQL Server - Checkpoint process stuck in SLEEP_BPOOL_FLUSH

If Resource Monitor says that SQL Server is actually performing the checkpoint (i.e., it's working okay, just that it's slow), all you can and should do is wait.

If the disk subsystem is busy anyway (shared storage?), is it possible the other load has increased and now the I/O subsystem is near or at capacity? If you aren't measuring (recording) metrics that would tell you so, I would highly recommend this, using either PerfMon directly or some other 3rd-party tool. (It also depends on the storage architecture. The main point is to be able to track the I/O load over time.) If you're on a SAN, was there a change in the I/O subsystem itself that would be transparent to your SQL box?

One thing you can consider is changing the Recovery Interval setting, which lets you manually control how frequently checkpoints occur. Note that this is an instance setting. I strongly recommend reading the documentation thoroughly before changing it, because it's possible you could make things worse by moving away from the default (depending on your workload).


Transactional replication reports ok, but tracer token not arriving

Ok, in case anyone ever has this problem I found the fix (though not the root cause).

Fix

Stop the distribution agents for all subscribers to publications from this database
Get the LSN that is causing the problem from one of the subscribers:

SELECT
        publication,
        '0x' + CONVERT(VARCHAR(32),MAX(transaction_timestamp),2) as LastPubLSN
    FROM
        dbo.MSreplication_subscriptions
    GROUP BY publication

Find a valid LSN to start from:

DECLARE @Publisher sysname = N'PubA',
            @PubDB sysname = N'DB2',
            @Publication sysname = N'PushPub_Broken';
    SELECT TOP 1000 trans.entry_time, trans.publisher_database_id, trans.xact_seqno
        FROM dbo.MSpublications AS p
            JOIN master..sysservers AS srv
                ON srv.srvid = p.publisher_id
            JOIN dbo.MSpublisher_databases AS d
                ON d.publisher_id = p.publisher_id
                AND d.publisher_db = p.publisher_db
            JOIN dbo.MSrepl_transactions AS trans
                ON trans.publisher_database_id = d.id
        WHERE p.publication = @Publication
              AND p.publisher_db = @PubDB
              AND srv.srvname = @Publisher
              AND trans.xact_seqno >= p.min_autonosync_lsn
    ORDER BY trans.entry_time DESC

Check what invalid commands are in the repl queue:

DECLARE @PubDB INT = 5  /* use the publisher_database_id retrieved above */
    select * from MSrepl_commands with (READPAST)
              where
                 publisher_database_id = @PubDB and
                 command_id = 1 and
                 type <> 30 and  /* 30 = normal repl commands */
                 type <> -2147483611 /* -2147483611 = is ignored by sp_MSget_repl_commands (it's snapshot related?) */

Backup and delete the commands that are causing the blocking, this should be the same LSN that is causing the problem (see step 2)
Reset the LSN on each of the subscriber DBs

EXEC sp_setsubscriptionxactseqno
        @publisher = 'PubA',
        @publisher_db = 'DB2',
        @publication = 'PushPub_Broken',
        @xact_seqno = 0x0000...  /* New valid LSN to start from (step 3) */

Start up your distribution agent and verify that all is working.

How I found it

So after finding and trialling sp_setsubscriptionxactseqno in conjunction with knowing how to get the LastLSN applied to the subscriber, I ran a trace on the distributor, I was able to see that the sequence was:

exec sp_MSget_repl_commands 126,/* good LSN that I was telling it to use */,0,10000000
go
exec sp_MShelp_distribution_agentid 6,N'PushPub_broken',N'DB2',8,N'PushBroken2',0
go
exec sp_MSget_repl_commands 126,/* bad LSN that it was resetting too */,0,10000000
go

Running the first command myself, I could see 2 resultsets returned, the 2nd of which included the bad LSN.  Obviously something inside this proc was causing it to overwrite the LSN I wanted it to apply.

Finding the code to sp_MSget_repl_commands, I was able to identify that it was:

select @max_xact_seqno = max(xact_seqno) from MSrepl_commands with (READPAST)
  where
     publisher_database_id = @publisher_database_id and
     command_id = 1 and
     type <> -2147483611

From there, I found the culprit records and was able to restart replication (after resyncing my data)


Running remote stored procedure with DDL locally

I don't have access to Azure to verify this all works without tweaking, but here is something you can try.

Here is some information on creating a linked server to Azure that might help you - I have not tested that part - https://stackoverflow.com/a/41145784

On Azure, create the stored procedure that contains the commands you want to execute on each client machine.  My example simply creates a TestUpdateTable and inserts the current timestamp.  I'm sure your stored procedure would be much more involved.

drop procedure if exists [dbo].[UpdateClient];
go
create PROC [dbo].[UpdateClient]
AS
begin transaction
drop table if exists dbo.TestUpdateTable;
create table dbo.TestUpdateTable (TextCol varchar(max));
insert into dbo.TestUpdateTable(TextCol)
    select convert(varchar(30),sysdatetime());
commit

In the following example, I'm using a linked server connection to a server I call SqlCompare.  I'm also referencing a database called SCUTILITY on that linked server.  This is where I put the central stored procedure.  This simulates what would be in Azure.

On each client machine, run the following dynamic TSQL

Declare @cmd nvarchar(max)

DROP PROCEDURE IF EXISTS dbo.UpdateClient;

SET @cmd = (
        SELECT *
        FROM openquery(SqlCompare, 'exec(''use scutility;select object_definition(object_id(''''dbo.UpdateClient''''))'')')
        )

PRINT @cmd

EXEC (@cmd)
GO

The intent is to drop the existing local client UpdateClient stored procedure first.  Then retrieve the text definition of the Azure stored procedure by using object_definition and put the results in a variable (@cmd).  Issuing EXEC (@cmd) will create the remote stored procedure locally on each client.

Now, execute the UpdateClient stored procedure locally on the client machine.

EXEC dbo.UpdateClient

The execution of the UpdateClient stored procedure just created the table TestUpdateTable and populated it with the current timestamp.  Again, your stored procedure would be more involved.

Now, you could issue select * from TestUpdateTable on the client machine and you should get a result back


How to convert an XML deadlock file into a graph or something readable?

You can bcp out and save it as .xdl. Many ways you can do it .. e.g. BCP OUT (I am using xp_cmdshell but you can use powershell or any other method).

Borrowing code from my answer (You need to adjust the code as per your requirement)  :

  -- bcp out as .xdl file. This is the deadlock graph that will be emailed. Note that it will be overwritten everytime !!

exec master..xp_cmdshell ''BCP.exe "SELECT  [Deadlockgraph].query(''''/TextData/deadlock-list'''') FROM dba_db.dbo.Deadlock where Notified = 0" queryout "d:\logs\deadlock.xdl" -c -q -T -Slocalhost''; ---- change localhost ..with the servername\instance or servername !!


Cannot allocate memory for the buffer pool when using remote access MySQL

"Separate" -- meaning they are on 2 different servers?

If so, then 10GB for the buffer_pool should be fine for a mysql-only server with 16GB of RAM.

If you have both on the same server, set the buffer_pool to

70% * (16G - (ram needed for web server))

  down with the log "Cannot allocate memory for the buffer pool"

Something is hogging memory.  It could be:

Other processes
Settings in mysql that are too high.  Did you change some other settings?

Or are you getting that error on the separate web server?  If so, then you have MySQL there!


Will index on joined table be used here?

HASH JOIN - no

NESTED LOOPS JOIN - yes, depends on statistics

Oracle can not use index range scan for join predicates when performing a hash join. But it can use indexes for other predicates for the probe table ("second" table), so hash join does not automatically result in a full table scan of the probe table.


Could not complete cursor operation because the table schema changed after the cursor was declared

Try explicitly setting your cursor to be LOCAL STATIC FORWARD_ONLY.

I don't know what specific changes might be happening on your linked servers, or what that stored procedure might be touching, but static makes a copy of the data so that the cursor isn't impacted (well, doesn't care about) any of those changes.

This is the same issue with sp_MSforeachdb that causes it to skip databases. They use the wrong kind of cursor there and, thanks to error handling, databases that have been touched in any way since the cursor opened just get silently skipped. This is why I wrote a replacement that is also available - with some handy improvements - in Brent Ozar Unlimited's First Responder Kit.

As an aside, there is a better way to execute dynamic SQL on databases/servers supplied by a variable. This usually makes it possible to use sp_executesql and pass strongly typed parameters (instead of doubling up quotes all over the place, making messy conversions of ints and dates to strings, and so on).

DECLARE @exec nvarchar(512);

... while loop ...

SET @exec = @Environment + N'.db.sys.sp_executesql';

INSERT INTO #Table
(
   Environment, Column1, Column2, Column3,
     Column4, Column5, Column6, Column7
)
EXEC @exec N'EXEC dashboard.storedproc @StartDate, @EndDate;',
   N'@StartDate datetime, @EndDate datetime', @StartDate, @EndDate;


First and last records in a string of linked rows

If you are using SQL 2012 as your tag indicates, then you can do this with a recursive CTE, although it's a little different since you find the child first and then the parents.  But Take a look at the sample below.

The Setup

DECLARE @Rentals TABLE
    (
    PK INT NOT NULL
    , CustID INT NOT NULL
    , ProdID INT NOT NULL
    , OverrideByPK INT NOT NULL
    , StartDate DATE NOT NULL
    , EndDate DATE NOT NULL
    );

INSERT INTO @Rentals
        ( PK
        , CustID
        , ProdID
        , OverrideByPK
        , StartDate
        , EndDate
        )
VALUES  ( 1,1,1,0,'1/1/2000','2/1/2000')
    , ( 2,1,1,3,'1/2/2000','2/2/2000')
    , ( 3,1,1,4,'3/2/2000','4/2/2000')
    , ( 4,1,1,0,'4/2/2000','5/2/2000');

The Query

WITH CTE_Rentals AS
    (
    SELECT R.PK AS PK
        , R.OverrideByPK AS NextPK
        , R.PK AS OldestPK
    FROM @Rentals AS R
    WHERE R.OverrideByPK = 0
    UNION ALL
    SELECT R.PK AS LastPK
        , R.overrideByPK
        , CR.OldestPK
    FROM @Rentals AS R
        INNER JOIN CTE_Rentals AS CR ON R.OverrideByPK = CR.PK
    WHERE R.OverrideByPK <> 0
    )
, CTE_Single AS
    (
    SELECT C.OldestPK AS LastPK
        , MIN(C.PK) AS FirstPK
    FROM CTE_Rentals AS C
    GROUP BY C.OldestPK
    )
SELECT S.FirstPK
    , FirstRental.CustID
    , FirstRental.ProdID
    , FirstRental.StartDate
    , LastRental.EndDate
FROM CTE_Single AS S
    INNER JOIN @Rentals AS FirstRental ON FirstRental.PK = S.FirstPK
    INNER JOIN @Rentals AS LastRental ON LastRental.PK = S.LastPK;


Insert a row into the table omitting all values?

Yes, you can use the default values clause (at least in Postgres):

insert into some_table
default values;

Online example


Grant table privileges to a user connecting from any host

When you execute GRANT SELECT ON store.catalog TO 'wordpress'@'%';, mysqld wants to insert a row into the grant table mysql.tables_priv. Here is mysql.tables_priv:

mysql> show create table mysql.tables_priv\G
*************************** 1. row ***************************
       Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)

mysql>

Since you want to insert a row into mysql.table_priv where user='wordpress' and host='%', there has to exist a row in mysql.user where user='wordpress' and host='%'.

You also mentioned that you are using MySQL Workbench. You must be using 'root'@'localhost'. That would usually have all rights and a password.

If you want to just allow anonymous SELECT against that table, first run this:

GRANT USAGE ON *.* TO 'wordpress'@'%';

This will place wordpress@'%' into mysql.user. Afterwards, GRANT SELECT ON store.catalog TO 'wordpress'@'%' should run just fine.

You will have to see what other wordpress entries are in mysql.user. This should show what SQL GRANT commands you need:

SELECT CONCAT('GRANT SELECT ON store.catalog TO ',userhost,';') GrantCommand
FROM
(
    SELECT CONCAT('''',user,'''@''',host,'''') userhost
    FROM mysql.user WHERE user='wordpress'
) A;


GRANT ALL ON ALL TABLES IN SCHEMA does not allow user to see tables

Create a group, which means a ROLE with NOLOGIN.

CREATE ROLE api_group NOLOGIN;

Create (or drop first if you want) your database. And then connect to that database.

CREATE DATABASE new_db;
\c new_db

Add default privileges for your group. The code below says if postgres role creates an object grant ALL to the role api_group. Instead of ALL you can be more specific (e.g. SELECT, INSERT, USAGE etc).

ALTER DEFAULT PRIVILEGES FOR ROLE postgres ALL ON TABLES TO api_group ;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres ALL  ON SEQUENCES TO api_group ;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ALL  ON FUNCTIONS TO api_group ;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ALL  ON TYPES TO api_group ;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ALL  ON SCHEMAS TO api_group ;

Now you can use postgres user to create new objects.

CREATE TABLE addresses (
    address_id INTEGER,
    address_line_1 VARCHAR(50) NOT NULL,
    address_line_2 VARCHAR(50),
    city VARCHAR(50) NOT NULL,
    state VARCHAR(2) NOT NULL,
    zipcode VARCHAR(12) NOT NULL,
    PRIMARY KEY (address_id)
);

-- Create more objects....

The setup is ready. If you add new user (lets say) api even after you create all the objects the user will have all the privileges. The INHERIT keyword there is for inheriting the privileges of roles it is a member of. The codes below create 2 users as a member of api_group.

CREATE ROLE api INHERIT WITH ENCRYPTED PASSWORD 'pwd' IN ROLE api_group;
CREATE ROLE another_api INHERIT WITH ENCRYPTED PASSWORD 'pwd' IN ROLE api_group;

Suggested sections of the documentation;

ALTER DEFAULT PRIVILEGES
CREATE ROLE


case statement sub query need to return multiple values

This should work:

select

CASE WHEN LTRIM(RTRIM([Order].ClientProject_Id))!=''
    THEN StationeryClientProject.ImpFile
ELSE Stationery.ImpFile
END AS ImpFile

FROM [Order]
    LEFT OUTER JOIN ClientProject ON   ClientProject.ClientProject_Id = [Order].ClientProject_Id
    LEFT OUTER JOIN StationeryClientProject ON StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id
    LEFT OUTER JOIN Stationery ON Stationery.Stationery_Id = [Order].Stationery_Id

The error message is caused by multiple rows being returned inside the case statement.  Since you are already returning the required rows in your FROM clause, you can simply reference the required columns in the case statement.


SQL Server - Stopping a server side trace

I figured out a way to do it.

declare @files table (ID int IDENTITY, FileName varchar(100))
insert into @files execute xp_cmdshell 'dir d:\Output\*.trc /b'

Checking the file count and if it is at my limit then I lookup the trace id and use sp_trace_setstatus  to stop it. Since my trace files take a while to get up to the filesize limit, I run this job every 5 or 10 minutes on agent.


The answer to your first question is No

When you execute Sleep function, it only block current session and not related to another sessions. So all other sessions work properly

But about your second question.

Note: gcache used to store only writesets in circular buffer style.

Usually a service have a Peak time that users have huge interactive to it.

So at the peak time of the system execute those queries that you said in your question.

After that use this formula to find writesets data bytes per minute

(second wsrep_received_bytes – first wsrep_received_bytes) + (second wsrep_replicated_bytes – first wsrep_replicated_bytes)

Well, now you can decide How much time you want to keep changes to server.

For example if writesets data is 10 MB per minutes and you plan to keep changes data to 24 hours you need to set gcache size to 14400 MB.

Note: In my opinion for more certainty add 20% more to writesets data bytes per minute. In this case for example consider 12 MB instead of 10 MB.


Since the data model of your SELECT is difficult to understand for me, I prepared following sample

The sample HANA SQL CTE selects the production lines with the products that are manufactured on it. I number each production according to their production date in an descending order on production line basis. For this, I use SQL Row_Number function with Partition By clause

After I filter the CTE data using rn = 1 WHERE clause, I get the most recent production for each production line.

I used LEFT JOIN so the result set displays product line 2 although there is no production on it.

/*
create column table ProductLines (
 pline int,
 productline varchar(100)
);

create column table ProductLineProduction (
 pline int,
 product varchar(10),
 productdatetime datetime,
 quantity int
);
insert into ProductLines values (1,'LineA');
insert into ProductLines values (2,'LineB');
insert into ProductLines values (3,'LineC');

insert into ProductLineProduction values (3,'Hat','201711011010',100);
insert into ProductLineProduction values (1,'Rug','201711031500',5);
insert into ProductLineProduction values (3,'Shirt','201711051200',200);
insert into ProductLineProduction values (1,'Fabric','201711080830',1000);
insert into ProductLineProduction values (3,'Trouser','201711091600',50);
*/

with cte as (
    select *,
        row_number() over (partition by pl.pline order by p.productdatetime desc) as rn
    from ProductLines pl
    left outer join ProductLineProduction p
        on pl.pline = p.pline
)
select * from cte where rn = 1;

The output for my sample will be as follows


You need to add these indexes:

CREATE INDEX ix_forum_posts_topic_id ON forum_posts(topic_id);
CREATE INDEX ix_forum_topics_last_activity ON forum_topics(last_activity);
CREATE INDEX ix_forum_topics_seen_topic_id_user_id  ON forum_topics_seen(topic_id, user_id);

After that you could use this simple query:

WITH CTE_topics AS (SELECT id FROM forum_topics ORDER BY last_activity DESC LIMIT 3)
SELECT    forum_topics.id,
         forum_topics.title,
         forum_topics.date_created,
         forum_topics.updated,
         forum_topics.last_activity,
         forum_topics.category_id,
         SUBSTR(forum_topics.content, 1, 70) as content,
         forum_topics.author_id,
         users.username,
         users.avatar,
         (SELECT COUNT(forum_posts.id) FROM forum_posts WHERE  forum_posts.topic_id = forum_topics.id) as posts,
         CASE WHEN forum_topics_seen.user_id = 49 then 1 else 0 end as seen,
         forum_categories.category_order,
         forum_categories.name as cat_name
FROM forum_topics
INNER JOIN CTE_topics ON forum_topics.id = CTE_topics.id
INNER JOIN users ON users.id = forum_topics.author_id
LEFT JOIN forum_topics_seen ON forum_topics_seen.topic_id = forum_topics.id AND forum_topics_seen.user_id = 49
LEFT JOIN forum_categories ON forum_categories.id = forum_topics.category_id;


Where is the generic plan stored when using PREPARE statement in PostgreSQL?

The generic plan is stored in the in-memory plan cache of the back-end that created the plan. See doxygen for plancache.c or the source.

Plans are not written to disk or read from disk, and are not shared between different backends (therefore, different sessions/connections). The documentation should really read:

  if a prepared statement is executed enough times in a single session, the server may eventually decide to save and re-use a generic plan in the per-connection plan cache rather than re-planning each time

You could probably modify the plancache code to serialize plans to disk, but it's not a trivial job to do it in a useful way. Reading them in on another backend correctly is much, much harder, when you consider the consequences of transactional DDL. Writing a proper persistent plan cache with correct plan invalidation etc would be a major job for an experienced PostgreSQL developer who already knew the optimizer/executor/planner/rewriter code well. If you wanted to tackle this you should expect to spend months studying the code before you could propose a reasonable approach, let alone implement it. (I've just been doing that with row-security for the AXLE project, so I'm all too aware of the complexity of this code).

As I said in your last question, let go of the idea that you can save plans, you can't, and concentrate on sorting out the issue that's causing the wrong plan to be chosen.


Adding foreign key that references a column which is not Primary or Unique key

So table2.columnB must contain a value from table1.columnA but there could be duplicate values in table1.columnA so you can't declare it unique? Apparently InnoDB does allow this, see https://stackoverflow.com/questions/2362163/mysql-foreign-key-by-non-unique-key-how-is-that-possible for discussion, but standard SQL (and therefore most databases) explicitly does not (it renders some operations like ON {DELETE|UPDATE} CASCADE potentially inconsistent) so I would avoid the option.

If there is a fixed set of valid values you could keep them in their own table with a unique/primary key defined upon the column, then both table2.columnB and  table1.columnA can be foreign keys referencing the new table.column.

A less clean option would be to write triggers to maintain the foreign key manually, as mentioned in http://cvuorinen.net/2013/05/validating-data-with-triggers-in-mysql/ amongst other places you should find easily via your preferred search engine. You need to be careful to consider all possibilities logically and efficiently, so I would recommend the "new entity type and foreign key" method instead - that will be less bug prone, probably more efficient, and might better model your data anyway (perhaps the properties represented by those columns should be entities in their own right).


Subtracting sum of previous rows' data from current row's data in SQL

It seems you need a running total.

If tot_qty is the same in all rows then you can use

SELECT  id,
        tot_qty,
        rel_qty,
        tot_qty - SUM(rel_qty) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS qoh
FROM dbo.foo
ORDER BY id;

ROWS UNBOUNDED PRECEDING is the abbreviated version of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

This window frame-ing option means that for each row the SUM will include itself and all predecessors as ordered by id.

The ROWS option is potentially important for efficiency as described in Best approaches for running totals – updated for SQL Server 2012


How to split a wide table in Production?

From my own experience of doing this your approach is almost identical to the approach that I took when I needed to do it. There are a few bits that I did extra prior to performing the split which gave me a better understanding of the potential impact this would have such as:

Query sys.sql_modules for any objects that reference the table in their definition
Create a scaled-down copy of the end-design in test and point clients to it (this enables you/app developers to know what is likely to need changing in the future)
Run a profiler trace to capture queries that are executed against the database which are not stored in the database itself.
Design indexes for the new table structure based on the queries identified from the steps above.

The only major difference between your approach and the one I took is I only created a single new table (I altered the original by dropping columns I did not need and then renaming it) so that it reduced the time to perform the release (I needed to copy less data doing it this way). I took a full backup prior to the release and I wasn't willing to have two copies of the same data in my database.


SQL Server logs "blob data", viewable with journalctl

This happens when there are unprintable characters in the output.

It seems sqlservr is outputting \r (or ^M) (line feed) characters.

You can view the raw data using: journalctl -u mssql-server.service --all

From what I can see, this appears to be the same content as /var/opt/mssql/log/errorlog, so that might be an easier way to view logs.


how to optimise this group by on sys.dm_exec_query_stats?

You're getting multiple queries from within the same stored procedure but because you're just looking at execsql.text you're getting the whole body of the procedure. If you want to know the individual queries you need to extract those. (The solution is definitely not to try to group by the entire procedure body.) Try this query:

SELECT TOP (1008)
  execquery.last_execution_time AS [Date Time],
  Database_Name = DB_NAME (execsql.dbid),
  [Script] = SUBSTRING(execsql.text, (execquery.statement_start_offset/2) + 1,
    (( CASE execquery.statement_end_offset
        WHEN -1 THEN DATALENGTH(execsql.text)
        ELSE execquery.statement_end_offset END
            - execquery.statement_start_offset)/2) + 2)
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC;


postgres filtering select based on combination of joins

The schema is basically Entity-Attribute-Value (EAV), and the same query pattern applies: Use repeated joins with different filters to obtain the desired information.

SELECT *
FROM sample_container sc
INNER JOIN sample_link sl_proj
  ON (sc.id = sl_proj.sample_container_id
      AND sl_proj.owner_type = 'project'
      AND sl_proj.owner_id = 1)
INNER JOIN sample_link sl_rm
  ON (sc.id = sl_rm.sample_container_id
      AND sl_rm.owner_type = 'raw_material'
      AND sl_rm.owner_id = 2);

See: http://sqlfiddle.com/#!15/5b309/3

Like most EAV schemas, this will scale very poorly to large numbers of attributes, and requires dynamic SQL most of the time. Consider whether EAV is the right choice for you.


You are wanting GROUP BY and the COUNT aggregate to return a row with a zero value when there are otherwise no rows to return - GROUPing does not work like that. It combines what rows would have been returned if GROUP BY was not present, so no rows in that case.
You could add a second SELECT, combined with UNION ALL, that returns one row containing inyear, incode, 0 if there are zero matching rows (counted by sub-select). If there are rows this part returns nothing, if there are no matches it returns a row indicating that. Note that this may be quite inefficient.


Dynamic database reference in T-SQL scripts

The answer is Synonyms.

Repeat for each database in foo family:

create synonym bar for [srv-db].<bar-db>.dbo.bar;

And then use it as follows:

select f.*, b.*
from foo f inner join bar b on f.barid = b.id ;


Difference between 'GrantedMemory' and 'MaxQueryMemory' attributes in Showplan XML

It's a snapshot of the same information that you can get from sys.dm_exec_query_resource_semaphores. I believe that it's target_memory_kb for the regular resource semaphore multiplied by the maximum query grant percent for the Resource Governor group that the query is executing in. If RG is not enabled then just use the default of 25%.

On the servers that I looked at, I generally see about 28% of server memory not available for query memory grants. So once I've run a workload the max query grant that's available for a query is 0.25 * 0.72 * MaxServerMemory.

Here's a query that you can run that finishes almost instantly but asks for a large memory grant:

DECLARE @zero INT = 0;

WITH CTE AS (
    SELECT high
    FROM master..spt_values
    WHERE @zero = 1
)
SELECT *
FROM CTE t1
CROSS JOIN CTE t2
CROSS JOIN CTE t3
ORDER BY t1.high + t2.high + t3.high;

If I force that query to timeout then I get the following in the actual plan:

MaxQueryMemory does not change as a result of the timeout. It isn't directly affected by other queries currently executing and using query memory. It's indirectly affected by anything that changes target memory KB for the resource pool.

This information could be useful if you have a bunch of different Resource Governor pools and groups and want to validate that a query goes to the correct place. It can also provide context to the total size or configuration of a server that you don't have access to. It's another way to tell if a query timed out waiting for a memory grant, but there are already other ways to do that. The intended use case may be to give more information about performance problems caused by target memory changing quite a bit on servers, but I've never seen that happen personally. Maybe it can happen on servers with multiple instances of SQL Server running?


Need to move TempDB to another drive. But its files cannot be dropped or shrunk

It turns out there are a couple of ways of quickly shrinking or emptying TempDB files and drop them.
The first way.

Change locations and sizes for all TempDB data and log files in system catalogs and restart SQL instance.

ALTER DATABASE tempdb MODIFY FILE(NAME = tempdev, FILENAME = '<file_location>', SIZE = <size>);
ALTER DATABASE tempdb MODIFY FILE(NAME = templog, FILENAME = '<file_location>', SIZE = <size>);

Restart SQL Server instance, drop old TempDB files

And one more way.

Start SQL Server with minimal configuration - using -f parameter and connect via sqlcmd
Delete unneeded files:

ALTER DATABASE tempdb REMOVE FILE [<LogicalFileName>]


Can Microsoft JDBC Driver 4.0 connect to SQL Server 2019 and perform DML without issue?

Microsoft and my testing have confirmed that JDBC drivers not marked as compatible in the SQL version compatibility matrix might connect to a non compatible version of SQL Server and continue to work without issue.

However, there is no guarantee that all functionality detailed in the Driver feature support matrix for Microsoft SQL Server is supported. They also only test the  driver versions marked as compatible against the stated version of SQL Server.
JDBC version 4.0 was not specifically tested.


PostgreSQL import CSV File causes Syntax Error

\copy is a psql (commandline) command. It is not a regular SQL command.

You will need to use COPY instead (but that requires the file to be present on the database server)


Backups in SQL Server are non disrupting. I.e. the database stays operational. Read the documentation.
Do a full backup every day, followed by shipped (copied off) LOG backups (again, the documentation has... documentation) more regularly - like every 15 minutes or so.


Results of using a comparison operator in the ORDER BY clause

This is common comparison operators.
When comparison is performed then some boolean result is produced. It may be TRUE, FALSE or NULL.
These 3 values are related as
NULL < FALSE < TRUE

This relation is used for sorting.
For example:
select * from x order by a<b;

If a and/or b is NULL then the result of ordering expression (comparison result) is NULL, and such records will be at the beginning of the output.
If a less then b then the result of ordering expression is TRUE and such records will be at the end of the output.
The records where a is greater or equal to b will be in the middle.


Oracle 12c export and import schema with different data tablespace and index tablespace

I wonder why would you use EXP when you have EXPDP-more efficient and performant tool- on your hand?

Using IMPDP

  If I do this, where are the data and indexes in stored? Are they still
  in the data_tbs_1 and idx_tbs_1 or they all in the data_tbs_2.

Yes. Since the import job executes the DDL to create those tables and the DDL includes TABLESPACE clause to define where to create the table not the default tablespace of the user. The objects will be created in their original tablespace regardless of the default tablespace of the new user.

This can be confirmed by exporting the dump file to SQL file.
IMPDP Command-

impdp system@orclpdb directory=dp_dir dumpfile=expdp_jay.dmp sqlfile=jay_ddl.sql

DDL-

Here is an excerpt from the SQL file.

CREATE TABLE "JAY"."TBL1"
   (    "TEXT" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ; -- Here you can see it has defined the tablespace to create the table.

  If the indexes of the are still stored in idx_tbs_1, how do I switch them to a new index tablespace (say idx_tbs_2) that I will create?

You can use REMAP_TABLESPACE parameter while importing the dump.

Here is the DDL for the same table imported with the REMAP_TABLESPACE clause.

IMPDP command-

impdp system@orclpdb directory=dp_dir dumpfile=expdp_jay.dmp sqlfile=jay2_ddl.sql remap_tablespace=USERS:DATA

DDL-

CREATE TABLE "JAY"."TBL1"
   (    "TEXT" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA" ;

REMAP_TABLESPACE

Update:

  The reason I still use the original exp and imp utilities is because my company still have that legacy data on previous version Oracle database.

If that is the case, I would import the objects in the same tablespace and later on I would migrate to another tablespace using ALTER INDEX REBUILD TABLESPACE <new_tbs> command.

Using IMP

If you have to use the original import tool then-

First of all, you need to extract the DDL from the dump file using
indexfile
parameter along with fromuser and touser parameter(Same schema name for fromuser and touser).

Example: imp system@orclpdb file=dumpfile.dmp fromuser=jay touser=jay indexfile=DDL.sql
Note: It takes some efforts to edit the SQL file which contains REM keywords as well as output for the number of rows.

Sample:

[oracle@oracdbuat fra]$ cat DDL.sql

REM  CREATE TABLE "JAY"."EMP2" ("ID" NUMBER) PCTFREE 10 PCTUSED 40
REM  INITRANS 1 MAXTRANS 255 TABLESPACE "DATALRG" LOGGING NOCOMPRESS ;
REM  ... 0 rows
REM  CREATE TABLE "JAY"."EMPLOYEE" ("ID" NUMBER) PCTFREE 10 PCTUSED 40
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS
REM  1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
REM  "USERS" LOGGING NOCOMPRESS ;
REM  ... 0 rows

Edit the SQL file generated by the import tool to change the
tablespace for the objects.
Run the SQL script using SQLPLUS.
Disable all the constraints
Import the dump file with the parameter IGNORE=Y to ignore the
warning about the already existed objects.
Enable the constraints.


Understanding the recreation of index in Table partitioning

Dropping the index is not required, and is likely a coding oversight in the wizard output.  It appears you've not just blindly ran the generated code; this is a Good Thing™, and is why I much prefer to write my own T-SQL statements by hand.

The procedure for modifying a clustered primary key to a non-clustered primary key, then adding a clustered index to a separate column is exactly as you've described:

DROP the primary key constraint.
ADD a new primary key constraint with the NONCLUSTERED keyword.
CREATE a clustered index on the desired column.

The DROP INDEX statement is certainly not required.


Inline Scalar UDF Under RCSI - Can Results Differ

Yes, an in-lined function can show different results than its out-lined (!?) counterpart. The following reliably reproduces the situation on my machine (Windows 10, 4 cores + HT @ 2GHz, 16GB RAM, SSD).

Configure the database and session to use Read Committed Snapshot Isolation (RCSI):

alter database Sandpit
set read_committed_snapshot on
with rollback immediate;
GO
set transaction isolation level read committed;
GO

This table will give a shared object on which concurrent workloads can act.

drop table if exists t;
go
create table t(c int);
insert t(c) values (1);
go

This table is to capture the results from the test, hopefully revealing divergent behaviour between functions that are in-lined and not:

drop table if exists #Out;
go
create table #Out(Base int, Old int, New int);
go

To demonstrate the different behaviour I want two function executed within a single SELECT, one of which in in-line and the other isn't. The documentation says

  A scalar T-SQL UDF can be inline if .. the UDF does not invoke any intrinsic function .. such as GETDATE()

To ensure one UDF cannot be in-line I add a reference to GETDATE. Note that this additional statement plays no part in the logic of the UDF, it simply suppresses in-lining. (Indeed, this function could be optimised away. Perhaps some future release will implement just such a optimization?)

create or alter function dbo.Old_skool()
returns int
as
begin
    declare @tot int = 0;
    declare @d date = GETDATE();   -- inhibits in-lining
    select @tot = SUM(C) from t;
    return @tot;
end
go

create or alter function dbo.New_kid_on_the_block()
returns int
as
begin
    declare @tot int = 0;
    select @tot = SUM(C) from t;
    return @tot;
end
go

To reference the shared table I've arbitrarily chosen to use SUM. I believe, but haven't tested, that any other technique that surfaces differences in the rows seen by the functions and the containing SELECT (MIN, MAX, TOP(1)) would do just as well.

Next I start two sessions. The first is to perform the SELECT, the second to make concurrent writes against the shared table.

-- Session 1 for reads

set transaction isolation level read committed;
GO

truncate table #Out;

declare @c int = 0;

while @c < 99   -- large enough to exhibit the behaviour
begin
    insert #Out(Base, Old, New)
    select
        c,
        dbo.Old_skool(),
        dbo.New_kid_on_the_block()
    from t;

    set @c += 1;
end

-- Session 2 for writes

declare @c int = 0;

while @c < 99999
begin
    update t set c = c + 1;
    set @c += 1;
end

I set running the session performing writes. On my machine it runs for about 24s, which is ample time to switch to session 1 (the reads) and start it.

For one run over 99 SELECTs there are 12 instances where the in-line and traditional execution mechanism return different results. In every case the in-lined function returns the same result as the containing query (which is not to say this test shows such behaviour is guaranteed).

Base        Old         New
----------- ----------- -----------
1801        1802        1801
1803        1804        1803
1814        1815        1814
1841        1842        1841
1856        1857        1856
1857        1858        1857
1860        1861        1860
1861        1862        1861
1864        1865        1864
1883        1884        1883
1884        1885        1884
1890        1891        1890


How to represent NULL in relational algebra?

There are no NULL values in relational algebra. Here you can use the antijoin operator ▷:

  πROLLNO, NAME(STUDENT ▷ENROLLMENT)

that selects all records from STUDENT where the ROLLNO is not in ENROLLMENT.

In SQL there are NULL values. You may use a left outer join and select the rows where the courseid is NULL:

select STUDENT.ROLLNO, STUDENT.NAME
from STUDENT left outer join ENROLLMENT
    on(STUDENT.ROLLNO=ENROLLMENT.ROLLNO)
where ENROLLMENT.COURSEID is null


Directory Verification with For Each Loop in SSIS

if you have the right amount of permissions you can use something like the function below to check if a file exists.

I have not tried to check if a folder exists though.

USE MASTER
GO

create FUNCTION dbo.fn_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

this is an example as how to call the above function:

--=============================================
-- get the latest Full backup still on Disk
--=============================================

select
  a.backup_set_id AS [LATEST Full backup still on Disk]
  ,a.media_set_id
  ,a.backup_start_date
  ,a.backup_finish_date
  ,a.type
  ,backup_size=a.backup_size/1024.00
  ,a.is_copy_only
  ,compressed_backup_size= a.compressed_backup_size
  --,b.media_set_id
  ,b.physical_device_name
  ,[File Exists]= CASE WHEN master.dbo.fn_FileExists(b.physical_device_name) = 1 THEN 'Yes' ELSE 'No' END
  FROM msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
         ON a.media_set_id = b.media_set_id

  WHERE 1=1
  and a.backup_set_id = (
                            SELECT MAX(f.backup_set_id)
                                FROM msdb.dbo.backupset f
                          INNER JOIN msdb.dbo.backupmediafamily b
                                  ON f.media_set_id = b.media_set_id
                               WHERE 1=1
                                 AND f.[type] = 'D'
                                 AND f.DATABASE_NAME='Junoreporting'
                                 AND master.dbo.fn_FileExists(b.physical_device_name) = 1
)


Can I do vacuumlo,vacuumdb and reindex in live production system without any downtime in postgres database

"reindexdb" takes the most stringently lock, and is the only thing likely to be causing downtime.

Unless you have evidence that it is necessary, just stop doing the "reindexdb".  It might be necessary to do it once, in order to get you out of the hole, but shouldn't be necessary to do each time you do "vacuumlo".


SQL MATCH AGAINST incomplete string with space

If you want to use Full-Text Search, then in order to get an exact phrase match it would seem that doing a Boolean Full-Text Search would be the way to go, enclosing the term New York in double-quotes, hence:

SELECT * FROM places WHERE MATCH (field) AGAINST ('"New York"' IN BOOLEAN MODE);

But, if you are looking to do a "contains" type of search where the user-entered value is a literal fragment (i.e. what they entered needs to match as-is, but can also be a part of a larger string and is not necessarily the entire match), then perhaps doing a regular LIKE '%{user_input}%' would be best.


SQL Server: Using Dual Databases for Performance?

Two things

You don't actually say you have an index on the table -- I expect this would just solve your problem.  An index on f_application, f_computername, f_endtime, f_starttime should make your update time tiny with only 6mill records.
If you want to split it up, don't do it the way you describe, create a table for open but not closed records that you use before your current table.  Then when something "updates" delete it from the that staging table and insert it in your big table.  Using a staging table in this way is considered a leading/best practice - having a table arbitrarily split in two is always a nightmare to work with


For the most part, the primary impact is that the stats gather faster. For the vast majority of cases it produces equally good stats, but does it way faster. There are a few edge cases where you can end up with inferior stats from an approximated NDV. But for the vast majority of datasets it's a superior method due to greatly reduced computational intensity.

To compensate for the problem of occasionally inferior stats, you can manually increase the sample size of the stats gather job to increase its accuracy.

If you want to know how it works in great detail, here is a white paper by a subject expert:

http://jonathanlewis.files.wordpress.com/2011/12/one-pass-distinct-sampling.pdf


"Cannot find data type" restoring a SQL Server 2014 backup on SQL Server 2008 R2

Actually, tCompressedBinary sounds like it might be a true UDT (a user-defined type implemented using CLR) rather than a UDDT (a user-defined data type, which are just aliases for other types created with syntax like CREATE TYPE creditcardno FROM CHAR(11) NOT NULL;

See the answers to https://stackoverflow.com/questions/35494575/sql-server-udt-versus-uddt for more information and links to MS documentation.

If tCompressed is a UDT then it won't be included in the database scripts you have generated. You will need to install and activate that CLR module in the new SQL Server instance before you can run the scripts to build this schema. Otherwise go with my original answer, below.

If you really can't do that (you don't have permission to turn on CLR on the new instance, the module is a 3rd party one that doesn't support 2008r2 at all, etc.) then you might get away with replacing all references to the type in column and parameter definitions with with what-ever type the application code is compatible with (VARBINARY(MAX) would be my first guess).

If you are using 2008r2 Enterprise Edition then compression is supported so that would not be the problem. In that case, check the definition of the user-defined type tCompressedBinary. Are there errors when it is created? Or is it present in the wrong order (defined in the scripts after it is first used)?

If you are using an edition other then Enterprise, then search the scripts and remove all references to [WITH] COMPRESSION. Of course, there may be other compatibility issues once you have removed all the compression directives which will become apparent once you've tidied up this one.


Count duplicate versions of databases (SQL Server 2008 R2)

You can use CASE...WHEN logic to only apply the CHARINDEX function where appropriate:

SELECT      'Production' as Class,
            getdate() as DateRecorded,

            CASE WHEN   d.name like '%[_]p[_]%' and d.name not like '%import' and d.name like '%[_]v[0-9]%'
            THEN        left(d.name, CHARINDEX('_v',d.name)-1)
            ELSE        d.name
            END         as [DBGroup],

            d.name as [DatabaseName],
            ROUND(SUM(MF.SIZE) * 8 /1024,0) AS SizeMB

FROM        SYS.MASTER_FILES MF
            INNER JOIN SYS.DATABASES D ON D.DATABASE_ID = MF.DATABASE_ID

GROUP BY    CASE WHEN   d.name like '%[_]p[_]%' and d.name not like '%import' and d.name like '%[_]v[0-9]%'
            THEN        left(d.name, CHARINDEX('_v',d.name)-1)
            ELSE        d.name
            END,
            d.name

ORDER BY    CASE WHEN   d.name like '%[_]p[_]%' and d.name not like '%import' and d.name like '%[_]v[0-9]%'
            THEN        left(d.name, CHARINDEX('_v',d.name)-1)
            ELSE        d.name
            END,
            d.name


Why default character_set_server is latin1?

Think about it:

You are storing data in the database as latin1
You are data is handled internally by mysqld as latin1

If data coming from the OS or from the connection is utf8, how is mysqld going to treat it?
Rather than guessing or hoping for the best, you could change the incoming character set behavior. With the exception of information_schema and mysql, take all your databases and set the default character set to utf8:
ALTER DATABASE dbname CHARACTER SET utf8;

If you have a specific colllation to go with it, do this:
ALTER DATABASE dbname COLLATE 'utf8_general_ci';

Here are the collations to choose from:
mysql> select * from information_schema.collations where CHARACTER_SET_NAME = 'utf8';
+--------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------+--------------------+-----+------------+-------------+---------+
| utf8_general_ci    | utf8               |  33 | Yes        | Yes         |       1 |
| utf8_bin           | utf8               |  83 |            | Yes         |       1 |
| utf8_unicode_ci    | utf8               | 192 |            | Yes         |       8 |
| utf8_icelandic_ci  | utf8               | 193 |            | Yes         |       8 |
| utf8_latvian_ci    | utf8               | 194 |            | Yes         |       8 |
| utf8_romanian_ci   | utf8               | 195 |            | Yes         |       8 |
| utf8_slovenian_ci  | utf8               | 196 |            | Yes         |       8 |
| utf8_polish_ci     | utf8               | 197 |            | Yes         |       8 |
| utf8_estonian_ci   | utf8               | 198 |            | Yes         |       8 |
| utf8_spanish_ci    | utf8               | 199 |            | Yes         |       8 |
| utf8_swedish_ci    | utf8               | 200 |            | Yes         |       8 |
| utf8_turkish_ci    | utf8               | 201 |            | Yes         |       8 |
| utf8_czech_ci      | utf8               | 202 |            | Yes         |       8 |
| utf8_danish_ci     | utf8               | 203 |            | Yes         |       8 |
| utf8_lithuanian_ci | utf8               | 204 |            | Yes         |       8 |
| utf8_slovak_ci     | utf8               | 205 |            | Yes         |       8 |
| utf8_spanish2_ci   | utf8               | 206 |            | Yes         |       8 |
| utf8_roman_ci      | utf8               | 207 |            | Yes         |       8 |
| utf8_persian_ci    | utf8               | 208 |            | Yes         |       8 |
| utf8_esperanto_ci  | utf8               | 209 |            | Yes         |       8 |
| utf8_hungarian_ci  | utf8               | 210 |            | Yes         |       8 |
| utf8_sinhala_ci    | utf8               | 211 |            | Yes         |       8 |
+--------------------+--------------------+-----+------------+-------------+---------+
22 rows in set (0.03 sec)

You could also run
mysql> show collation where charset='utf8';
+--------------------+---------+-----+---------+----------+---------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen |
+--------------------+---------+-----+---------+----------+---------+
| utf8_general_ci    | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin           | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci    | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci  | utf8    | 193 |         | Yes      |       8 |
| utf8_latvian_ci    | utf8    | 194 |         | Yes      |       8 |
| utf8_romanian_ci   | utf8    | 195 |         | Yes      |       8 |
| utf8_slovenian_ci  | utf8    | 196 |         | Yes      |       8 |
| utf8_polish_ci     | utf8    | 197 |         | Yes      |       8 |
| utf8_estonian_ci   | utf8    | 198 |         | Yes      |       8 |
| utf8_spanish_ci    | utf8    | 199 |         | Yes      |       8 |
| utf8_swedish_ci    | utf8    | 200 |         | Yes      |       8 |
| utf8_turkish_ci    | utf8    | 201 |         | Yes      |       8 |
| utf8_czech_ci      | utf8    | 202 |         | Yes      |       8 |
| utf8_danish_ci     | utf8    | 203 |         | Yes      |       8 |
| utf8_lithuanian_ci | utf8    | 204 |         | Yes      |       8 |
| utf8_slovak_ci     | utf8    | 205 |         | Yes      |       8 |
| utf8_spanish2_ci   | utf8    | 206 |         | Yes      |       8 |
| utf8_roman_ci      | utf8    | 207 |         | Yes      |       8 |
| utf8_persian_ci    | utf8    | 208 |         | Yes      |       8 |
| utf8_esperanto_ci  | utf8    | 209 |         | Yes      |       8 |
| utf8_hungarian_ci  | utf8    | 210 |         | Yes      |       8 |
| utf8_sinhala_ci    | utf8    | 211 |         | Yes      |       8 |
+--------------------+---------+-----+---------+----------+---------+
22 rows in set (0.00 sec)

mysql>

To see the individual charset of a database run this:
mysql> show create database sample;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| sample   | CREATE DATABASE `sample` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

As for the settings, you could try this:
Add the lines to my.cnf
[mysqld]
character_set_database=utf8
character_set_server=utf8

then restart mysql
I discussed this back on Aug 01, 2011 : Character Set Encoding in a Table
CAVEAT (For MySQL DB Servers in Windows)
These commands
ALTER DATABASE dbname CHARACTER SET utf8;
ALTER DATABASE dbname COLLATE 'utf8_general_ci';

do not work in the Windows Version of MySQL because of the way Windows locks files. The file needed is called db.opt which is located in the database subfolder in datadir.
You may have to do the following:

mysqldump that database (no database create info, just table creation and INSERTs)
drop that database
create database with the specific charset and collation
reload in the dump into it

EPILOGUE
No matter what you do, please perform any changes on a Dev/Staging Server to see if you get the desired effects
UPDATE 2012-12-05 11:00 EDT
Your Questions

Should I really change it?

To guarantee the proper treatment of the data, you may want to make sure you have apples-to-apples. Data prepared as one charset and loading it into a table with the database possibly aligning the data as if it sees another charset would probably not display the data with the charset mysqld sees when retrieved and sent back to a DB Connection.  Try loading the database on a Dev/Staging Server and experiment with setting default charsets.

Why some defaults use utf8 but some default use latin1 ?

This would depend on the OS version of the MySQL Binary. Windows versions may have latin1 while Linux Versions may use utf8.


I'm not aware of a particular method that records this, the information doesn't appear in file$ or x$kccfe . In general though I wouldn't worry about these events so much as how much free space I have in both the tablespace and the file system. There are alternative methods to monitor this

Updated At Leigh's suggestion

In answer to Gaius comment - but too long for a comment itself.

I think there are 2 basic things to consider for "efficiency" and the results will be system and site dependent. Its worth re-iterating though that I think the efficiency or otherwise of autoextend from a performance viewpoint is way down the list of priorities. (it was just interesting at a quiet time).

First is, overall how efficient do you want the allocation of space to be. The best result will be achieved if you never autoextend but size exactly correctly to begin with :) In general the fewer file extension operations you do the better, but its a declining win.

Second, how much do you want to delay any individual transaction that triggers a file extension event? here the opposite applies, the more small file extensions you have the shorter each individual event will be.

I have some figures and a sample script for others to test/critique etc at http://orawin.info/blog/2012/01/04/proof-by-extension/


Compare two columns for difference mark the difference in mysql

That is not the correct syntax for an UPDATE+JOIN. Try the following (assuming it is what you want, updating the table customer):

UPDATE customer b
JOIN received_customer n
ON b.phonenumber = n.phonenumber
SET b.followUpStatus= 'y' ;


Enable the slow query log and log_queries_not_using_indexes on the slave server and should be able to confirm what your gut is telling you.  You can take the captured queries, run EXPLAIN on them, and have some better documentation of your suspicions.

To more directly answer the question, it's absolutely valid to declare indexes on a slave that are not declared on the master.  I would suggest that it's a common practice, and one of many reasons why replicas are useful for so many things.  Connect to the slave with an account with sufficient privilege, and alter the table directly on the slave.

The only exceptions to this -- which should be intuitively obvious, since the row data needs to be consistent on master and replica -- is that you cannot safely declare a UNIQUE constraint or a foreign key constraint on a slave when it doesn't exist on the master.  This won't work, which is fine, since it doesn't make sense at any rate.

Remember also that indexes in MySQL have names, which are automatically generated if they are not supplied when the index is added.  A good practice might be to explicitly name these indexes on the replica so that future indexes added to the master cannot possibly cause an index name collision, which would break replication.  My local convention is to prefix the index name with "ix_repl_" which would of course never be used on the master.

ALTER TABLE t1 ADD KEY ix_repl_last_first (last_name,first_name);

Note also that proper duplicate indexes (where exactly the same columns, and no others, are included in more than one index) are deprecated in MySQL 5.6 and disallowed by default in MySQL 5.7, making it apparently possible in later versions to cause replication to stop if you subsequently declare an identical index (even with a different name) on the master.  This wouldn't be a critical issue (as long as you're monitoring replication -- you are, right?) since replication would be safe to restart by simply removing the now-redundant index on the replica before restarting the slave SQL thread.  The failed event would be retried, and would now be valid, since there's no conflicting index, and the replacement index would be built on the replica.

Side note: remember that MySQL replication requires the version of MySQL on the replica servers to be the same as, or newer than, the version on the master... so when upgrading (or migrating to MariaDB) you will almost certainly want to upgrade the replicas first, and then the master.  The reason for this is that a newer replica will understand the capabilities and quirks of an older master, but a newer master may introduce behaviors in the replication stream that an older replica server won't be able to interpret.  There are limited exceptions to this rule, but it's most definitely the rule.


How MongoDB allocate new data block when data block is full

Data files are allocated in advance. The following is slightly simplified, for the sake of shortness.

When a MongoDB database is created, an empty datafile with a size of 64 MB is created. As soon as data is written to this pristine data file, a background process creates a new datafile in order to have one ready of the first datafile is full. This will be repeated every time a document is written to a pristine data file. For each new data file, the file size is doubled until the size to allocate reaches 2GB. From that point on, all new datafiles will have 2GB in size.

As for the data, this is a bit more complicated. Put simply, documents are never moved unless it is really necessary, for example if the document grows in size and exceeds its padding space. So if one of your data files is full and a new document is added, only the new document will be written to the new and formerly pristine data file (triggering the allocation of a new data file), and the old documents stay where they were.


Stored procedure execution time went from under 2mins to "infinity" without modifications

I received a follow-up from the database administrator. So I will post his message as the answer.

I tried again after receiving his message and sure enough the stored procedure ran in under 2 minutes.

His email:

  Seems that the issue was a deadlock on the db. I killed the locked processes and it seems fine now.

  The system update essentially caused rehashing in some of the tables and the replication job to your datamart hung up because of this.

  Please try again and let me know.

Edit: Additional Information

If you have the same issue, please also consider the comment from @Kin below since I am unable to confirm 100% that the DBA actually did what he said.

@Kin: Well, SQL Server will automatically choose the deadlock victim based on the log usage (the process using less log will be killed unless you have deadlock priority set). I think, it is a blocking issue not a deadlock as thats where you kill the offending process.


Possible to create databases in MongoDB but not have access to databases created by others?

Answer is NO. If user can create database what don't exists, user must have 'dbAdminAnyDatabase' role and then user can access any DB.

IF database names are know forehand, you can give 'dbAdmin' role to user for that database, even database don't actually exists yet. In this case, if those "prelisted" databases are different names, different users don't have right to those database what other users has created.

As you can create empty databases (without collections) forehand and give rights to users. But, DB names are "selected" forehand and user don't have possibility to choose their own DB name... Collections they can then create, if they have right to DB.


Script to identify which AD accounts are no longer active

I'd highly recommend the dbachecks PS module which makes this (and pretty much every other important check) quite easy to run.

Running it is as simple as:

Invoke-DbcCheck -Checks ADUser -SqlInstance localhost


Not able to attach database file without transaction log file

This is the problem with detaching the database and deleting the transaction log file as a method of keeping the transaction log file short.  You can end up with corruption in the database that can't be resolved.

First try attaching the database using the sp_attach_db_single_file system stored procedure.  If you can't get it attached using that google for "hack attach sql server" and look at the first link from SQLSkills.com.  If that doesn't work then the database is lost and you won't be attaching it back to the SQL Server instance.

Next STOP DOING THIS.  Configure the databases for simple recovery so that the transaction log isn't kept any longer than needed.  While the transaction log appears to be just a log you can delete isn't actually a very important file that the SQL Server requires to keep the database up and running and in a consistent state.


Force drop db while others may be connected

PostgreSQL 13 added:
DROP DATABASE mydb WITH (FORCE);

The manual:

FORCE
Attempt to terminate all existing connections to the target database.
It doesn't terminate if prepared transactions, active logical
replication slots or subscriptions are present in the target database.
This will fail if the current user has no permissions to terminate
other connections. Required permissions are the same as with
pg_terminate_backend, described in Section 9.27.2. This will
also fail if we are not able to terminate connections.

In PostgreSQL 12 and earlier, you cannot drop a database while clients are connected to it.
At least, not with the dropdb utility - which is only a simple wrapper around DROP DATABASE server query.
Quite robust workaround follows:
Connect to your server as superuser, using psql or other client. Do not use the database you want to drop.
psql -h localhost postgres postgres

Now using plain database client you can force drop database using three simple steps:

Make sure no one can connect to this database. You can use one of following methods (the second seems safer, but does not prevent connections from superusers).
/* Method 1: update system catalog */
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'mydb';

/* Method 2: use ALTER DATABASE. Superusers still can connect!
ALTER DATABASE mydb CONNECTION LIMIT 0; */

Force disconnection of all clients connected to this database, using pg_terminate_backend.
 SELECT pg_terminate_backend(pid)
 FROM pg_stat_activity
 WHERE datname = 'mydb';

 /* For old versions of PostgreSQL (up to 9.1), change pid to procpid:

 SELECT pg_terminate_backend(procpid)
 FROM pg_stat_activity
 WHERE datname = 'mydb'; */

Drop the database.
 DROP DATABASE mydb;

Step 1 requires superuser privileges for the 1st method, and database owner privileges for the 2nd one. Step 2 requires superuser privileges. Step 3 requires database owner privilege.


Database design for students and families/parents

How about creating a separate Table called Person ? You will need separate tables for storing Guardian or Student related information and they both can refer to person Id.

Instead of family you can have relation table that joins two different Person with Extra column of RelationshipType (and of course another lookup table.)

Address table can connect to person table as well and same as Address you can have another table for contact details as well with contact type lookup table that stores values like phone number, email, mobile, work email, work phone etc.

This will also help you in scenarios where an ex student becomes a guardian.

Hope that helps.

Maulin Thaker


Can't create federated table in MariaDB using CREATE SERVER - 'Foreign data src error'

Nuts.  I'd been looking at the documentation on the MySQL web site for version 5.5 of MySQL.

When I looked up the MariaDB's site and explored its knowledge base pages I discovered that:

a.  They've abandoned the FEDERATED engined and have replaced it with a FederatedX engine, even though:

b.  The syntax still uses the 'FEDERATED' key word, but also

c.  They have added a 'Socket' option to the CREATE SERVER statement.

The 'Socket' column was in the listing of the mysql.server table but I assumed it should just be blank, and it wasn't mentioned in the MySQL documentation.

Anyway, adding a 'socket' option to CREATE SERVER to tell MariaDB to explicitly user the database server's socket has solved the problem.

Weird how the SOCKET wasn't necessary in the long-form CONNECT string though.


Is it ok for MySQL to always use 18-25% of Memory (I have 2 GB Ram)?

It's normal. Memory usage is determined by various configuration parameters, some of which are shared (e.g. innodb_buffer_pool_size), while others are per thread.


Should I use a surrogate key in a mapping table?

Option 5:

(ItemGroupId, ItemId) (note the order) as the primary key (clustered)
Unique constraint (non-clustered) on ItemId

While searching by ItemId is not conducive to range scans (yet?), searching by ItemGroupId is 1, which makes it a good candidate to be the leading column of the clustered index key.

My normal design for this type of table is to index both combinations, i.e., (Column1, Column2) and (Column2, Column1) so I (and others) never have to think about whether it's indexed properly; it just is. In your case here, one combination is degenerate and so only one column goes into the key, but the non-key column is still covered by the index because it's part of the clustered index key. The situation becomes more complicated, of course, if there are non-key columns involved, but that's beyond the scope of what you asked here.

In this case, while you could flip-flop the primary key with the unique constraint, I chose the 2-column primary key on purpose because it will be more stable during the life of the table. This works to your advantage as a DBA (one word: Replication) and also to developers (it could be quite a bit of code updates if the primary key definition changes).

1 There might be an issue with terminology here. Searching by a single ItemId will return 0 or 1 row; searching by a single ItemGroupId (an incomplete key) returns >= 0 rows, and so is a seek + scan (it will show up as an index seek in the execution plan). Obviously this isn't a "range scan" on multiple ItemGroupIds. The idea is to physically group together (and order, in this case) the ItemIds for every ItemGroupId. I hope that's clear.


Dealing with Violation of PRIMARY KEY constraint SSIS

If the primary key is a surrogate (ie an IDENTITY valued INT) then you need to remove it from the field list of the insert (if possible) otherwise (if you need to retain the surrogate key values to support foreign keys) to bracket the insert with set identity_insert on; and set identity_insert off;.


Postgres View with content from another Databaseserver

You can use a FDW1 to connection on your MSSQL database and get your data. A FDW expose a remote service (a database, webservice, api, etc) like a table for read and write.

For MSSQL, the tds_fdw2can do the trick.

Let me know if you need a example or more details how the FDW work.

References:

https://wiki.postgresql.org/wiki/Foreign_data_wrappers
http://pgxn.org/dist/tds_fdw/


Mysqli prepare error: Unknown storage engine 'InnoDB'

Error:   mmap(127713280 bytes) failed; errno 12 : not enough memory to start the service:

Possible solutions:

add swap page
check innodb_buffer_pool_size value it may be too low to start the service or too high for the server

After that try to delete ib_logfile# then start the service


Like this:

select sum(case when pstatus=2 then pafgifte else 0 end) pafgifte_total
from projecten

Assuming you mean to add the numbers together.


Incorrect results when combining full-text search and regular comparison

I think your primary problem is that you are using non-Unicode string delimiters for Unicode data.
Compare:
SELECT  ' "پ*" ';
SELECT N' "پ*" ';
-------^ this N is important


Lock a row if it exists, create new record otherwise

If LockedBy is null when not locked then...

Using select @CurrentLockedBy = isnull(LockedBy,0) lets us find out which of the three states the record is in. If it is null, then the record doesn't exist. If it is 0 then it exists and is unlocked. Otherwise it exists and is locked by someone (possibly the caller).

I would use something like this:

create procedure dbo.docs_upsert (
    @docid int output
  , @cols varchar(max)
  , @LockedBy int
  , @CallerUserId int
  ) as
begin
  set nocount on;
  set xact_abort on;
  declare @CurrentLockedBy int;
  begin try;
    begin tran;
      select @CurrentLockedBy = isnull(LockedBy,0)
        from docs d with (updlock, holdlock) -- lock for update/insert
        where d.id = @docid;
    /*
     If @CurrentLockedBy >0 then Locked by someone
     If @CurrentLockedBy = 0, exists and not locked.
     If @CurrentLockedBy is null, row doesn't exist.
     */
    /* Locked by another user*/
    if isnull(@CurrentLockedBy,@CallerUserId)!=@CallerUserId
    begin;
      return -1;
    end;
    /* Exists, not Locked */
    if @CurrentLockedBy = 0
    begin;
      update docs set
          cols = @cols
        , LockedBy = @LockedBy
        where id = @docid;
    end;
    else
    /* Doesn't exist, insert */
    begin;
      insert into docs(cols)
        values(@cols);
        set @docid=scope_identity();
    end;
    commit tran;
  end try
  begin catch;
    if @@trancount > 0
      begin;
        rollback transaction;
        throw; -- or other error handling
      end;
  end catch;
end;