Adding to Andriy's answer, there is a neater way around called dollar quoting.  See what the documentation tells us about it:

  While the standard syntax for specifying string constants is usually
  convenient, it can be difficult to understand when the desired string
  contains many single quotes or backslashes, since each of those must
  be doubled. To allow more readable queries in such situations,
  PostgreSQL provides another way, called "dollar quoting", to write
  string constants. A dollar-quoted string constant consists of a dollar
  sign ($), an optional "tag" of zero or more characters, another dollar
  sign, an arbitrary sequence of characters that makes up the string
  content, a dollar sign, the same tag that began this dollar quote, and
  a dollar sign.

Using this technique simplifies your life and makes the statement much more readable:

CREATE OR REPLACE FUNCTION foo() RETURNS void AS
$$
DECLARE
    empty_str text := '';
BEGIN
    EXECUTE $sql$ SELECT ' $sql$ || empty_str || $sql$ ' $sql$;
END;
$$
LANGUAGE plpgsql;

I've added an extra space after and before $sql$ to make things more clear.  Here the tag that the documentation mentioned is 'sql'.  Note that you are already using dollar quoting: it's the pair of $$s around the function body.

Upon a hint from ypercube, I have to say that in the general case one should use

EXECUTE 'SELECT quote_nullable($1)' USING empty_str;

Read more about quoting in dynamic queries at https://www.postgresql.org/docs/9.6/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE


Derby slows down after 1.2 million records

DISCLAIMER : Not a Derby Expert

DERBY

There are options you can set to increase data pages for Derby

For a mass load of table prodtable, you may want to consider

create temptable just like prodtable, but with no indexes
load data into the temptable
rebuild indexes on temptable
rename prodtable to zaptable
rename temptable to prodtable
drop zaptable

MySQL

If you switch over to MySQL, you could do wonderful things to performance tune data processing

Start with using only InnoDB
You can configure InnoDB to

hyperthread read threads
hyperthread write threads
access multiple CPUs/Cores
setup partitions buffer pools

Here are some of my past posts on configuring InnoDB in MySQL for multicore performance

Is the CPU performance relevant for a database server? (Apr 26, 2012)
Multi cores and MySQL Performance (Sep 20, 2011)
Possible to make MySQL use more than one core? (Sep 12, 2011)
How do you tune MySQL for a heavy InnoDB workload? (Feb 12, 2011)

While I may be a little biased towards MySQL, make sure you have Derby's caching configured to accommodate mass INSERTs. That way, you need not perform a major data migration if Derby can be made to handle large transactions.


How to send big email via procedure sp_send_dbmail?

I would recommend dynamically building the body of the mail message itself using a query, instead of attaching the query results to the mail using sp_send_dbmail.

For example, something like:

DECLARE @BodyHtml nvarchar(max);

SET @BodyHtml = N'';
SELECT @BodyHtml = @BodyHtml + CONVERT(nvarchar(max), html) FROM ##t;

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profile',
    @recipients = 'mail@server.com',
    @subject = 'subject',
    @body = @BodyHtml,
    @body_format = 'HTML';

This should send the entire HTML inside the e-mail body itself instead of as an attachment.

The @body parameter is defined as nvarchar(max), so it's likely that you're getting implicit conversions to varchar(8000) when you're building your html body, and that's why it was truncated at 8000 characters (unless, maybe, you're using a very old version of SQL?). This is why the CONVERT function is important while you're building your html body.


SQL server Principal and Mirror logins and permissions Issue

See the article Transferring Logins to a Database Mirror by Robert Davis, where he writes:

There are two main issues with transferring SQL Logins. Foremost is the SID (security identifier) for the login. The SID must be transferred with the login or it won’t automatically map to the database user. Secondly, the password is not available in clear text, so you can’t just read it from the database.
This script addresses both of these issues. It creates the SQL login with the same SID and it uses the stored hash of the password to recreate the password using the HASHED property to indicate that we are supplying the password already hashed.

There is a link at the bottom to download the script dba_CopyLogins.sql. This will sync your logins between principal and mirror.


Additional space required for INDEX REORGANIZE

To answer more concisely than the existing answers:

REORGANIZE produces the same amount of log with SIMPLE and FULL. It's just that SIMPLE almost immediately makes that space available again.

Except if something is preventing log truncation such as an open transaction (unrelated to the session the REORGANIZE runs in).


Best practice regarding concurrency for INSERT into a table with composite primary key?

If I wanted to add a user to a team, what's the best option

For just INSERT INTO teams_users ...  and without raising an exception, the answer is option 3: Use Postgres 9.5 Beta for the UPSERT functionality. For a "school project" this is the best option anyway. Your students will want to study the latest version.

INSERT INTO teams_users (team_ID, user_ID)
VALUES (1, 2)
ON CONFLICT ON CONSTRAINT teams_users_pkey DO NOTHING;

Where teams_users_pkey is the actual name of your PK constraint.

This inserts the new row or does nothing if the unique index would raise a duplicate key violation. Designed to be fast and safe against concurrent writes.

Additional question

  Finally, I have no idea if there's a race condition when it comes to
  the 2nd option.

Option 2 being:

  While inserting, use the WHERE EXISTS syntax (2nd example).

That would actually be WHERE NOT EXISTS in your case and yes, there is a race condition. Two parallel transactions could find that a certain combination does not exist yet at virtually the same time and happily try to insert it. The slower one would run into a unique violation and error out. Not much of a problem if your application is prepared for that eventuality.


TIMESTAMP stores the number of seconds from 1970-01-01 00:00:01 to now. It automatically converts to the date and time format when you retrieve the data.

CURRENT_TIMESTAMP(): Returns the current date time with your timezone configured.
UTC_TIMESTAMP(): Returns the current date and time using UTC timezone.

If you're using a timezone that's not UTC, these functions always return different values, equal otherwise.

MySQL always stores the date and time in UTC so it can be easily converted to different timezones. NOW() or CURRENT_TIMESTAMP() return the date/time plus your timezone configured.

UTC_TIMESTAMP() just returns the date/time ignoring your timezone setting.


How to FETCH from a cursor into a table variable?

No, you can't fetch scalar values from a cursor row into a table variable. You would have to declare the variables, fetch into them, and then insert:

FETCH NEXT FROM idCursor INTO @id, @data;
INSERT @currentId([id],[data]) SELECT @id, @data;

However, perhaps it's the case that you don't need a cursor at all. Why are you processing one row at a time? Why not populate the @table variable with whatever query originally populated #tempRemaining?

INSERT @currentId([id], [data])
SELECT DISTINCT [id], [data]
    FROM #tempTable;

Or even just using #tempTable by itself and skipping the table variable altogether? Maybe even skipping the #temp table? A cursor combined with a table variable and a #temp table just sounds like a nightmare.

At the risk of sounding like Celko, this seems very much like flat file processing from the 1970s... and it's even worse that there are so many columns that declaring those variables would be prohibitive.


Getting Values of the two rows before the selected row POSTRESQL

You can use either a window function:

with ct as
(
  select
      id,
      amount,
      (sum(amount) over (order by id rows between 2 preceding and 1 preceding)) / 2 as new_amount
  from
      t
)
update t
set    amount = ct.new_amount
from
    ct
where
    t.id = ct.id
    and ct.id = 3;

Or use a subquery that returns the 2 previous rows:

update t
set amount = (select sum(t3.amount) / 2
             from (select amount
                   from t t2
                   where t2.id < t.id
                   order by id desc
                   limit 2) t3)
where
    t.id = 3;

Check them for performance.

select * from t;

id |             amount
-: | -----------------:
 1 |                  1
 2 |                  3
 3 | 2.0000000000000000

db<>fiddle here


Delete relations efficiency in PostgreSQL

You can do all in a single data modifying CTE

with deleted_impressions as (
  delete from impressions i1
  where exists (select *
                from impressions i2
                where i2.id > i1.id
                  and i2.created_at = i1.created_at)
  returning id
), delete_reg_impressions as (
  delete from regional_impressions
  where impression_id in (select id
                        from deleted_impressions)
)
delete from demographic_distribution
where impression_id in (select id
                        from deleted_impressions);

First the duplicates from impressions are removed and all IDs that are returned from that query are then used to delete the corresponding rows in demographic_distribution as this is done in a single (atomic) statement the foreign keys are still valid.


EXECUTE AS does not appear to be changing security context

Wouldn't be nice to find a tool that allows you to impersonate to Windows a user without knowing the password? Every intern could impersonate the CFO and withdraw millions from the bank... The possibilities!

No, EXECUTE AS is changing the security context exclusively with regard to access SQL Server objects (tables, procedures etc). By no means will (or even can, for the matter) be able to change the real NT execution context for the process or for processes launched by SQL Server.

Update

I obviously forgot to add the actual useful piece of information: there is a way to make xp_cmdshell use a specific credential, provided you do create such a credential in SQL Server:

create credential [##xp_cmdshell_proxy_account##]
   with identity = 'DOMAIN\ID2',
   secret = '<ID2 password>';

The key difference is that you are very explicitly giving the password here to SQL Server. Now SQL Server will use this credential 'proxy' whenever faced with calling xp_cmdshell from a SQL login context or from a EXECUTE AS context.

You can also associate a credential with a login for 'generic' access to resources outside SQL Server (eg. accessing a network share):

  A credential is a record that contains the authentication information
  that is required to connect to a resource outside SQL Server. Most
  credentials include a Windows user and password.


Remote accessing a MySQL server for non-root user

I found the solution! I needed to add "identified by" clause to the grant statement. I added that clause to the "create user" statement but I wasn't aware that it was required for adding permissions to the user as well!
It works great now.

P.S. So can you have different passwords to have different permissions for the same user? I get the idea of having different permissions based on where they're signing in from (localhost, ip-address, etc) but doing it based on what password they use seems odd (or is it just a by-product of the way the authentication system works?).


How to combine concatenate operator on JSON values with AS

Not exactly sure what you are asking, but in Postgres 9.3 or later you can use json_agg() to translate a whole table into a JSON array of records. Prepare the rows in a single subquery and feed whole rows to json_agg() using the table alias:

Multiple layers of nested subselects like you display seem uncalled for.

Simple example:

CREATE TABLE tbl (
  tbl_id serial PRIMARY KEY,
  foo    text
);

INSERT INTO tbl VALUES
  (1, 'First' )
, (2, 'Second');

Query:

SELECT json_agg(sub)
FROM  (
   SELECT tbl_id        AS some_id
        , foo           AS bar
        , tbl_id || foo AS some_derived_column
   FROM  tbl
   ) sub;

Result:

[{"some_id":1,"bar":"First","some_derived_column":"1First"},
 {"some_id":2,"bar":"Second","some_derived_column":"2Second"}]

SQL Fiddle.


Postgres 'could not write to hash-join temporary file: No space left on device'

1- How do the internals of postgres work that cause this message to occur in the first place?

When joining two very large data sets, one strategy PostgreSQL can use is to scan one table and hash its join columns. Then scan the other table and compare the hashes of the join columns to hashes calculated for the second table.

See https://en.wikipedia.org/wiki/Hash_join

It works really well when you're joining a small table against a much larger one, especially where no indexes suit the join conditions.

PostgreSQL can use a hash join strategy for bigger tables where it writes the hash table partitions out to disk first. This is presumably what you got.

  What is the hash-join in reference to the creation of the temporary file that the db creates?

Hash tables. I don't really understand this question.

  2- How can I predict and prevent errors like this (related to performance and memory) occurring in the future?

Have more spare disk space for temporary storage. It doesn't have to be in the main tablespace; see the temp_tablespaces setting.

As far as I know PostgreSQL doesn't have the ability to estimate hash table sizes and decide the hash table will be too big for the available disk space.


Creating this custom view makes no sense. The view ALL_SCHEDULER_JOBS is accessible to everyone. You will not lose access to it, so creating VIS_SCHEDULER_JOBS is just pointless. ALL_SCHEDULER_JOBS lists the jobs the current user can access. You may lose access to the jobs, but not to ALL_SCHEDULER_JOBS view.

ALL_SCHEDULER_JOBS


What does FTS_ file mean after converting from MyISAM to InnoDB?

These files are related to the new FULLTEXT index support in InnoDB (starting with MySQL 5.6). Removing them is not a good idea for sure.

Unlike with MyISAM, where FULLTEXT indexes are stored in the tables .MYI file together with all other indexes fulltext indexes in InnoDB are implemented using several internal helper tables which show up as separate .idb table space files in innodb_file_per_table mode:

The "...INDEX_1.ibd" to "...INDEX_6.ibd" table files contain the actual fulltext index, split up into 6 partitions. The "...CONFIG.idb" stores meta information about the fulltext index and the "...DELETED..." files are needed for housekeeping of deleted data.

For more details see

http://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html#idm140684547617952


limit the number of rows returned when a condition is met?

I'm not sure how this will scale but it's an answer :)

I should note that I'm summing for CN.RowNum < ClassNumbered.RowNum and not <= for a reason.  This way I can return everything lower than the requested value (2.0 in this case).  If I did <= then I would have to return everything <= 2.0 which won't work if the three values happen to be .5, 1 and 1 for a total of 2.5.  You wouldn't get the last class.  I also broke this up by subject so that if you have more than one subject you will get multiple groupings by subject.

WITH ClassNumbered AS (SELECT [Subject], Name, Grade, Credit, Pass,
                        ROW_NUMBER() OVER (ORDER BY [Subject],Credit,Grade) AS RowNum
                    FROM Classes),
    ClassTotals AS (SELECT [Subject], Name, Grade, Credit, Pass,
                        (SELECT ISNULL(SUM(Credit),0) FROM ClassNumbered CN
                            WHERE CN.[Subject] = ClassNumbered.[Subject]
                              AND CN.RowNum < ClassNumbered.RowNum) AS RunningTotal
                    FROM ClassNumbered)
SELECT *
FROM ClassTotals
WHERE RunningTotal < 2.0


In Powershell Retrieving Printed Message From SSMS

Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell

The trick, as Jonathan points out, is that you need to have a listener attached to the connection manager listening for the print or error actions.

$cd.Parameters.Add("@f", $bak.FullName) | Out-Null
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message }
$SqlCon.add_InfoMessage($handler)
$SqlCon.FireInfoMessageEventOnUserErrors = $true

I also piped the output of adding a parameter to Out-Null as that was noise I didn't need.


Selecting, ranking and applying a point score based on rank

Sample data:

CREATE TABLE v
    (`vote` varchar(8), `game` int, `year` int)
;

INSERT INTO v
    (`vote`, `game`, `year`)
VALUES
    ('player 1', 2, 2015),
    ('player 1', 2, 2015),
    ('player 2', 2, 2015),
    ('player 2', 2, 2015),
    ('player 2', 2, 2015),
    ('player 3', 2, 2015),
    ('player 1', 2, 2015),
    ('player 3', 2, 2015),
    ('player 4', 2, 2015)
;

Query:

SELECT
vote,
no_votes,
rank,
3 - rank + (1 / CASE rank WHEN 1 THEN @rc1 WHEN 2 THEN @rc2 WHEN 3 THEN @rc3 END) AS points
FROM (

    SELECT
    sq.*
    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes
    FROM
    (

        SELECT
        vote,
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

    ) sq
    , (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery
    ORDER BY no_votes DESC

) q

Result:

|     VOTE | NO_VOTES | RANK | POINTS |
|----------|----------|------|--------|
| player 1 |        3 |    1 |    2.5 |
| player 2 |        3 |    1 |    2.5 |
| player 3 |        2 |    2 |      2 |
| player 4 |        1 |    3 |      1 |

see it working live in an sqlfiddle

Explanation:

This

        SELECT
        vote,
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

is simple enough, I hope :)

This

, (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery

is just a subquery to initialize some variables.

Here

    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes

in the first row @prevNoVotes holds the value of the previous row (or the value from the initialization subquery). @rc1, @rc2 and @rc3 are just rank counters. They count for each rank, how many people are on the same rank.
In the last row, the value of the current row is assigned to the variable. When the next row is processed, the variable will hold the value of the previous row in the first line of this part of the code.

And finally in the most outer part of the query we calculate the points distribution.

Feel free to ask any questions :)

UPDATE:

Added this table:

CREATE TABLE points
    (`r` int, `p` int)
;

INSERT INTO points
    (`r`, `p`)
VALUES
    (1, 3),
    (2, 2),
    (3, 1)
;

Added more logic :)

SELECT
vote,
no_votes,
rank,
@rc := @rc + 1 AS rowcounter,
((SELECT p FROM points WHERE r = IF(rank = @prevRank, rank, @rc)) - 1) + (1 / CASE rank WHEN 1 THEN @rc1 WHEN 2 THEN @rc2 WHEN 3 THEN @rc3 END) AS points,
@prevRank := rank
FROM (
    SELECT
    sq.*
    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes
    FROM
    (

        SELECT
        vote,
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

    ) sq
    , (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery
    ORDER BY no_votes DESC
)q
, (SELECT @rc := 0, @prevRank := 1) row_counter_init
ORDER BY rank

see it working live in this sqlfiddle


If you are using MySQL v5.6 because Performance_Schema has lot of metadata about your query/table and how the processing of a query happened (almost everything that is helpful for you to debug a problem/query internals)

You can have a look at my existing answer here:
Is there a way to get slow-query-log-like profiling information on a single query?

Otherwise, You can try Show Profiling functionality of MySQL to find out where most of the time has been taken by a query execution and optimize based on result:

set profiling=1;

Run your slow query (eg SELECT * FROM table WHERE name='xxx';

SHOW PROFILES;

http://dev.mysql.com/doc/refman/5.0/en/show-profile.html

Analyze EXPLAIN EXTENED output and optimize query accordingly and profile the query again to see gain in performance.


TDE prep: key/certificate backup for restores

If you want to restore on a different server, you should be able to do so with the certificate, private key and database backup file(s).

When a certificate is created in any database in SQL Server, it is part of an encryption hierarchy. The certificate in the master database itself only contains a public key, which needs no protection, however, the master database will also contain a separate but mathematically related private key which does need to be protected. The method of protecting the private key is to encrypt it using the database master key you created in the master database prior to creating the certificate. The next layer in the encryption hierarchy is that the DMK is encrypted by the service master key. There is only one SMK on the system and it is in the master database.

Even though you don't need the DMK and SMK to restore an encrypted backup to another server, I would back up these two keys anyway as it makes recovery much more flexible.

When you restore the backup encryption certificate to the master database, what happens behind the scenes is that the private key is read from the file, decrypted using the password you provide in the restore command, then encrypted using the database master key and saved. As you know, the private key from the certificate can then be used to decrypt the Database Encryption Key in the backup file and successfully restore the database.

I don't have a specific recommendation for storing the certificate and key backup files, but they do need to be available to you and whoever does the disaster recovery in your organization.


Need to fetch latest record always and ignore if there is no latest data

I would look at creating a trigger on the table, and having the trigger set to email the inserted rows as appropriate.

There is no point me explaining how exactly, as Aaron Bertrand gave a super answer to this premise here: https://stackoverflow.com/questions/10755469/send-e-mail-from-a-trigger

I have implemented this sucesfully to send email alerts when certain values change or rows are deleted. Works perfectly.


Database Space Used Decreased after Index Rebuild (Ola Script)

This is quite common behavior. You are reclaiming unused space by reordering pages in the leaf level.

Just don't shrink your datafile unless it's critical - you'll probably need that space in the future. You are just wasting time, fragmenting (again) the database and locking tables.


UPDATE T1 SET
Opening_date = T2.Opening_date,
ORDINAL = DATEDIFF(DAY, T2.Opening_Date, T1.DATE)
FROM FactTransaction T1
INNER JOIN DimStore T2 ON T1.cod_Store = T2.Cod_StoreKey;

...if I understand you correctly.

Edit: Ok, just go with:

UPDATE T1 SET
ORDINAL = DATEDIFF(DAY, T2.Opening_Date, T1.DATE)
FROM FactTransaction T1
INNER JOIN DimStore T2 ON T1.cod_Store = T2.Cod_StoreKey;


Are the performance gains from data consistency enough of a reason to develop in NoSQL, rather than relational?

I think your question presupposes a certain way of looking at things.  Your fundamental tradeoff with NoSQL is a tradeoff between declarative ad hoc reporting and fast and loose inputs.  This tradeoff is unacceptable for many (maybe most) businesses, and so "rather than relational" I think is the wrong way to look at it.  Even folks like Martin Fowler advocate something different.

My recommendation is always to start relational and then add NoSQL adjuncts to the rdbms to handle velocity issues when those arise.


Inner join with greater or equal VS where condition using a variable

Yes there will be a (marginal) impact on performance, but if it's for debugging purposes I don't think you'll be able to tell the difference.

You can easily test this yourself on your data by adding statistics IO and looking at the query plans

Take this code for example:

SET STATISTICS time ON;
SET STATISTICS IO ON;

WITH randowvalues
    AS(
       SELECT 1 id, CAST(RAND(CHECKSUM(NEWID()))*100 AS int) randomnumber
        UNION  ALL
        SELECT id + 1, CAST(RAND(CHECKSUM(NEWID()))*100 AS int)  randomnumber
        FROM randowvalues
        WHERE
          id < 1000
      )

SELECT *
INTO sample
FROM randowvalues
OPTION(MAXRECURSION 0)

GO

DECLARE @VAR AS INT

SET @VAR = 500

SELECT * FROM sample WHERE ID > @VAR

CREATE TABLE #cut ( ID int )

INSERT INTO #cut VALUES (500)

SELECT * FROM sample A INNER JOIN #CUT B ON A.ID > B.ID

You will notice that in the execution plan that the first query does a simple table scan for an estimated cost of 0.005.

For the second construction you'll see that the plan for the insert has an estimated cost of around 0.01, and the query itself added an extra table scan (for your temporary table) and a nested loops join to combine data from both tables.
The estimated cost for this select is also around 0.01.

The output for the IO statistics for the first select is:

  Table 'sample'. Scan count 1, logical reads 3, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0.

So this query does 3 reads.

The output for the insert and the select with the join is this:

  Table '#cut'. Scan count 0, logical reads 1, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0.

  Table 'sample'. Scan count 1, logical reads 3, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0. Table '#cut'. Scan count 1, logical reads 1,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

So if you add that up the second construct with the join does a bit more IO.

Creating a clustered index on the ID column replaces the table scan with a clustered index seek as can be seen in this plan or creating a nonclustered index on ID results in this plan.

Even though the impact in your situation can vary depending on your actual data and table structure, your construction with a temporary table will always add some IO, a second table access and a join operator which will impact performance negatively (maybe marginally).


Migrate complete postgres database cluster to another machine using pg_dump

The question you are referencing is a bit of a mess, I think you would be better off not referring to it at all.  It just introduces misconceptions to then partially remove some of them.

If you want to backup and restore the whole cluster, you should use "pg_dumpall", not "pg_dump".  Using "pg_dumpall" without any of the restrictive options will emit the necessary "CREATE DATABASE" commands.

If you run "pg_dump" with -C option, it too will emit "CREATE DATABASE" commands.

  I would like to be able to restore single databases later on, so pg_dumpall doesn't seem to be the right way, or is it?

If you want to do something different in the future, then do something different in the future.  "pg_dumpall" preserves your options.  You can restore the whole thing, and then drop databases you don't want.  Or you can restore the whole, and re-dump what you do want out of it.  Or just dump what you want out of the original server, if that is still around.  Or use vi or sed or awk or perl to munge the "pg_dumpall" .sql file so that it does only what you want.

If you don't like any of those options, you could do a pg_dumpall -g accompanied with a pg_dump -C of each individual database.  If your primary use case to restore a single database but without knowing ahead of time which one it will be, this would be faster than the previous options, and could be run on a smaller machine than the original.  But it is also a bit harder to document, and more likely for files to get out of sync with each other.


Your correct, 2008R2 and onwards has the *_rows added to sys.dm_exec_query_stats. I would say that's the way to go.

You'll have plenty of queries that can create massive disk IO but still have a small result set. However, it's not possible to have a huge result set without huge logical IOs.

So look at query stats ordered by logical io's first, then row count second. That will give you a pretty good starting point.

Before 2008 R2 I would look at which queries with large logical IOs have network related waitstats. The problem with this approach is. 1) Once the network is saturated, it's likely that not only your large queries, but pretty much all your queries will have network related waitstats. 2)Network related wait stats are in many cases not caused by network related issues ;-)

cheers,
Edward


Com_commit and Com_rollback count are almost same. What doest it mean?

com_* status variables are a pure count of what the database server receives from the application.
As those are the numbers from JIRA, you can conclude that's what JIRA is issuing to the database.
I'd suggest a JIRA forum maybe able to diagnose why it is issuing so many rollbacks. It does look very suspicious.


Where is Connection String Located as it relates to Availability Group Listeners?

If you've set-up an Always On Availability Group Listener (and you should), then you'll have a new virtual name to use in your connection string. So if your SQL Servers are SQL01 and SQL02, and there's an availability group called SQLAG01 - then you'll specify this. You could continue to specify SQL01 in your connection string, but if the Always On Availability Group has failed over to SQL02, it won't work. But if you specify the listener name, all will be well.

Make sure that you've got the latest SQL Server Native Client drivers installed so that you can use the extra settings like Multisubnetfailover and potentially ReadIntent


I have SQL Server Management Studio 18 - Where I can find SQLServerManager / SQL Server Configuration Manager

To configure SQL client aliases, there is no need for SQL Server Configuration Manager. The aliases are often for client-side usage, so installing the Configuration Manager would be extra hoop to jump through. There are two executables called cliconfg.exe in c:\windows\syswow64 and c:\windows\system32. These should be present after installing SQL Server Client tools or, in your case, SSMS.
The system32 version of cliconfg will create aliases for 64 bit applications, whilst syswow64 does the same for 32 bit. It is a good practice to create aliases for both environments, even if one uses mainly x64. If 32-bit aliases are omitted, legacy applications cannot connect to the database via alias.
For scripted solutions, one can edit registry keys directly. The keys are in paths
HKLM\Software\Microsoft\MSSQLServer\Client
HKLM\Software\Wow6432Node\Microsoft\MSSQLServer\Client

There are several pages how to configure aliases (search for network utility on the latter link). Googling for sql server client alias provides a lot of hits about the subject too.


How to set default innodb_buffer_pool_size before mysqldump and set it back to 1600M after mysqldump?

90% is dangerously high for any server.  For a "tiny" 2G, a much lower 500M should be safe.  Swapping is terrible for performance.  But, things are worse -- --max_allowed_packet=500M is taking up a terribly large amount of RAM.  Try 50M.  If that gives an error, then raise it and lower the buffer_pool.


Get maximum from aggregated values in a query

SELECT *
FROM  (
   SELECT match_id
         ,SUM(CASE WHEN d.player_slot BETWEEN   0 AND   4
                   THEN d.kills ELSE 0 END) AS team1_kills
         ,SUM(CASE WHEN d.player_slot BETWEEN 128 AND 132
                   THEN d.kills ELSE 0 END) AS team2_kills
   FROM   details d
   WHERE (d.player_slot BETWEEN   0 AND   4
      OR  d.player_slot BETWEEN 128 AND 132) -- parens optional
   GROUP  BY d.match_id
   ) sub
ORDER  BY @(team1_kills - team2_kills) DESC
LIMIT  1;

The subquery is only for convenience.

The WHERE can might improve performance if a matching index is available. Redundant otherwise.

The CASE statements sum only kills from qualified players.

The @- operator produces the absolute value of the score difference. This is used to sort by (descending), then we only need to pick the first.

Don't use m as table alias for details, when a second table called matches is involved. That's confusing. Using d instead.

More generally, why do you join to matches at all? It is not used in the query. Are there details without associated match to exclude? (Then it would make sense.)

Based on comments
The query could probably be simpler / faster this way:
SELECT *
FROM  (
   SELECT match_id
         ,SUM(CASE WHEN p.team_id = ? THEN d.kills ELSE 0 END) AS team1_kills -- team1
         ,SUM(CASE WHEN p.team_id = ? THEN d.kills ELSE 0 END) AS team2_kills -- team2
   FROM   details d
   JOIN   players p ON p.id = d.player_slot  -- guessing from comment
   WHERE  p.team_id  IN (?, ?)               -- teams to compare go here.
   GROUP  BY d.match_id
   ) sub
ORDER  BY @(team1_kills - team2_kills) DESC
LIMIT  1;


I believe the reason you're getting these results is because the namespace for the xml is not the same as in the select statements.
I've altered your example to briefly explain each case.

declare @xmlMessage xml

set @xmlMessage ='<pmsg:Messages xmlns:pmsg="pmsg">
    <pmsg:Message Info="blah"/>
    <pmsg:Message Info="blah"/>
</pmsg:Messages>'

;WITH XMLNAMESPACES('pmsg' as pmsg)
SELECT msgs.msg.value('@Info', 'nvarchar(max)') FROM @xmlMessage.nodes('pmsg:Messages/pmsg:Message') msgs(msg);
-- return twos rows 'blah'

The nodes() expression provides a set of two pmsg:Message nodes, and the select reads the value of @Info from each row.

;WITH XMLNAMESPACES('pmsg' as pmsg)
SELECT @xmlMessage.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)');
--Returns a single rowset of one row containing 'blah'

This will only return a single row 'blah' because the [1] in the query restricts to the first item found.  Actually in this form the [1] is required. SQL will throw exeception of you dont include it becuase the expression must evaluate to a single value.

;WITH XMLNAMESPACES('pmsg' as pmsg)
SELECT t.x.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)') as INFO FROM @xmlMessage.nodes('/*') t(x);
--Returns a rowset of one row containing 'blah'

Like the previous example, the xpath query evaluates to a single item and the nodes() expresson provides a row set on the top level node:


High CPU in SQL Server supporting SharePoint databases while running FAST search crawl

DaniSQL Feb 28 '14 at 19:46: Convert comment to Answer

Yes it is resolved. We have done multiple things in parallel so its hard to tell what exactly fixed it.

We applied latest service pack and CU on all servers including SharePoint and SQL, started maintaining indexes and updating stats manually in addition to SharePoint.

Then run the full crawl couple times and problem went away.


MongoDB:Read from slave dirctly,how to guarantee that the data is just up-to-date?

Master/Slave replication is deprecated, will be removed in future versions and has no advantage over a replica set with two data bearing nodes and an arbiter (which is extremely cheap in terms of disk, CPU and RAM usage), safe for the fact that you don't need to run an arbiter. A replica set however offers automatic failover and some administrative advantages.

Another misconception you have is that replication (no matter which kind) is supposed to increase read performance. While you can use it for this, it is a very poor man's solution. I'd rather call it a highly impovered man's solution for performance scaling. Replica sets are MongoDB's means of ensuring data availability and durability.

That being said: there is a way to ensure that the secondary nodes are consistent with the primary node of a replica set, but it comes at a cost - and not a small one. You can set the write concern in a way that a write is only considered successful when it is written to all nodes of a replica set. The problem is that this requires some round trips which make the write operation slow - by orders of magnitude in the worst case. Also, if one of your servers fails, write operations with a write concern of "all" will fail, since the nodes originally forming the replica set are taken into account, not only the ones which are up. In case you can live with those drawbacks, you can even make absolutely positively sure that the data is written to the data files and not "only" the journal by setting the fsync option to true. Be warned that writes may take a VeryVeryLongTime(TM) to return with this option activated. I generally discourage it's use except for very special use cases.

For performance scaling, the solution offered by MongoDB is scaling out by means of sharding. When done correctly, you basically distribute your collections over several shards, thereby keeping a larger part of your working set in RAM and making read and write operations on the data files being done in parallel (document wise).


As a one-off you can use EXEC sp_who or EXEC sp_who2 or similar to find the SPIDs of that user's connections and then use KILL <SPID> to stop them. Be aware that any transactions they have open in which changes have been made will need to be rolled back which could potentially take some time and be I/O intensive, but if they are just running reports the effect of this should be minimal. To stop them immediately restarting the processes disable their access before doing this (using ALTER LOGIN <name> DISABLE or by adjusting their access rights more granularly).

If you find yourself needing to do this regularly you could write a procedure to automate the task, so you could just run EXEC kick_user_off '<name>';, though if you need to do this sort of thing regularly enough for it to be worth the effort automating then I suspect you have a process and/or user problem that needs fixing of which this is just a symptom.


SQL Server 2016 Compatibility Level and Datetime 2

From this KB article:

  Starting with SQL Server 2016 and Azure SQL Database, the database engine includes improvements in data type conversions and in several other operations. Most of these improvements offer increased precision when you deal with floating-point types, as well as with classic datetime types. These improvements are all available when you use at least database compatibility level of 130. This means that for some (mostly uncommon) expressions you will see different results for some input values after you upgrade the database to compatibility level 130 or a higher setting.

In your specific example, the comparison still works if:

you don't force the conversion to datetime2 (and this is unnecessary since you don't have any more precision in the input anyway);
start with a datetime2 column in the first place;
convert the input to datetime after converting to datetime2; or,
stay with the old compat level.

Otherwise you are trying to make SQL Server forever know that, for example, .333 = .334. The new types were introduced in SQL Server 2008, and were replaced to address exactly this type of imprecision; I guess they feel they have to move on at some point.

As an aside, I don't think the current behavior is 100% correct, but it's what we're stuck with for now.

Further, compatibility level is one way (and not a really good way) to affect the cardinality estimation model, but the reverse is not true. You can't change compatibility level by changing the CE model. This is why you didn't have any success using the query-level trace flag or database configuration option. But you don't have to go back to 100; 120 would probably work as well.


How to get rows containing ONLY certain strings?

Try some REPLACE() tricks:

SELECT *
FROM #words
WHERE REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    word
    ,'ka','')
    ,'ke','')
    ,'ki','')
    ,'ko','')
    ,'ku','')
    = '';


Get the rows where id_user_rq exists in id_user_ap and viceversa

Please try this:

SELECT A.* FROM
(SELECT * FROM default_relation_users WHERE id_user_rq = 1) A
LEFT JOIN
(SELECT * FROM default_relation_users WHERE id_user_ap = 1) B
ON
(A.id_user_rq=B.id_user_ap AND B.id_user_rq=A.id_user_ap)
WHERE B.id_user_ap IS NOT NULL;


You have two options
OPTION #1 : Create a RAM Disk
RAMDISK_SIZE=32g
service mysql stop
mkdir /var/tmpfs
echo "none   /var/tmpfs  tmpfs  defaults,size=${RAMDISK_SIZE} 1 2" >> /etc/fstab
mount -t tmpfs -o size=${RAMDISK_SIZE} none /var/tmpfs
cp -R /var/lib/mysql/* /var/tmpfs
mv /var/lib/mysql /var/lib/mysql_old
ln -s /var/tmpfs /var/lib/mysql
chown -R mysql:mysql /var/tmpfs
chown -R mysql:mysql /var/lib/mysql
service mysql start

If this does not work, you can reverse it out
service mysql stop
rm -f /var/lib/mysql
mv /var/lib/mysql_old /var/lib/mysql
service mysql start

You can set the RAMDISK_SIZE to your liking
OPTION #2 : Use FUSION IO
Mount /var/lib/mysql on a FusionIO Disk (all memory, CPU aggressive).
Have fun clearing this with your CFO.
EPILOGUE
Both of these options allow you to use MyISAM and InnoDB as you normally would. The goal is simply to place the entire datdair in RAM.
Give it a Try !!!


Automatically updating a record using count() of another collection

As you have noticed, MongoDB doesn't support naturally triggers. So, you need "programming skills" to do that functionality. You can use mongoTriggers, what can installed with mongo-triggers npm package.


Difference between Flashback Query and Serializable Transaction Mode?

They are completely unrelated concepts.

Serializable is a type of isolation level (read here for more info)
Flashback Query is an Oracle technology for getting querying data as it was at a certain point in time - for example, if certain conditions are met you can use Flashback Query to get those million rows back after committing that transaction that accidentally deleted them.


What is the best way to import this non-standard text file into SQL Server?

Based on your sample data it looks like there are 6 columns in total.

COL1, COL2, COL4, COL5, COL6 = All fixed width .

COL3 = This is a fixed width for a given day, but it is variable day to day (based on my understanding).

You should be able to calculate COL3's width each day based on the length of any given line. You know COL1,2,4,5,6 are all fixed width. Take the line total and subtract COL1,2,4,5,6 to get COL3's width each day.

COL3 = LINE_WIDTH - (COL1 + COL2 + COL4 + COL5 + COL6)

After you get COL3's width you will be able to figure out all of your start and end points.


Take database backup of a SQL server 2012 from local SQL Server 2008 R2

You should fix the SQL agent first.

as a temporary workaround, you can use SQLCMD and schedule it using windows scheduler for doing backups.

no need to spawn a job from different server and do the backup on another server.


Your "where" clause restricts it to products where it is matching the client products (As you may know, left join returns everything from the left side, with matching entries from the right side or NULL otherwise. But then you filter it for a specific client value) - I would amend your WHERE clause to:

WHERE
    product_client.client_id = 1
    OR product_client.client_id IS NULL

My logic is - you are matching either the specific client, or the values that are "unmatched" to another (e.g. client_id = 2) client. client_id is null where the product doesn't match a specific client.

To determine which one is a global product and which one is a client's only, add to the select statement:

CASE
    WHEN product_client.client_id IS NULL
        THEN 'Global'
    ELSE 'Client'
END

For MySQL/PHP add ( ) around the statement.

You could also ISNULL(client_id,"Global") if wanting to take the specific client if it's specified, or otherwise 'global'.


Update trigger fired before insert trigger upon insert of new row

Solved: I have update statements for the same table inside the body of the INSERT trigger. Nested triggers are disabled on my database on SQL Server 2012 but after the restore on SQL Server 2016, the setting was somehow enabled.

Edit: It appears that the 'nested triggers' setting of the SQL Server instance was set to 'False' on SQL12 and to 'True' on SQL16.


How do I return an index for each nvarchar col in a table?

Check out the INFORMATION_SCHEMA. I guess something like:

SELECT COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ...
  AND TABLE_NAME = ...
  AND DATA_TYPE IN (...)


It's possible to order alphabetically all columns in tables of a mysql db?

I would like to order all columns in all tables

The order of column [names] within a Table should not matter.

Every "select" statement that goes anywhere near your database should explicitly specify the columns that it wants, in the order that it wants them.  How the actual table is arranged matters not a jot.

If you have lots of columns with similar names, this may be indicative of a larger problem.


Connection refused when adding ApplicationIntent

Ok, got it. I had to force the TCP1433 port in SQL Configuration manager... That was only that.
I found it when trying to connect with a wrong password : SQLServer denied connection because of this wrong pass.
So, the mistake was between the listener and the server supposed to accept the connection... which was the second one, because of the read-only intent.

And this second one, even if it was listening on TCP1433, wasn't responding without explicitely configuring it in Configuration Manager.

Sorry!


Merging two tables by minimal interval values

I'd approach this in three logical stages:

Expand the rows in both tables for all the days in the month and join them together.
Assign a 'group' to each series of rows where condition and supervisor don't change for a period (a "gaps and islands" problem).
Group the results.

So the solution looks like this:

create table t1(
  id serial primary key
, begin_on integer
, end_on integer
, condition text
);

insert into t1(begin_on,end_on,condition)
values (1,8,'Normal')
     , (8,23,'Critical')
     , (23,30,'Normal');

create table t2(
  id serial primary key
, begin_on integer
, end_on integer
, supervisor text
);

insert into t2(begin_on,end_on,supervisor)
values (1,14,'John')
     , (14,30,'Janice');

select min(g) begin_on, max(g)+1 end_on, condition, supervisor
from( select g
           , condition
           , supervisor
           , row_number() over (order by g)
             - row_number() over (partition by t1.id, t2.id order by g) grp
      from generate_series(1,30) g
           join t1 on g>=t1.begin_on and g<t1.end_on
           join t2 on g>=t2.begin_on and g<t2.end_on ) z
group by condition, supervisor, grp
order by begin_on;

begin_on | end_on | condition | supervisor
-------: | -----: | :-------- | :---------
       1 |      8 | Normal    | John
       8 |     14 | Critical  | John
      14 |     23 | Critical  | Janice
      23 |     30 | Normal    | Janice

dbfiddle here

I am assuming this is a cut-down example of your real-world problem or I would also suggest changing the way you store the data in the first place, perhaps using date ranges instead of integers.


SQL Trigger that creates new table when a new record is added to current table

First things first

this would you use, to create special table per id Name and table you have to add

DELIMITER $$

CREATE TRIGGER after_table_insert
AFTER INSERT
ON tabl1 FOR EACH ROW
BEGIN
    SET @sql = CONCAR("CREATE TABLE table_",NEW.name,"_",NEW.ID, " (id INT,name TEXT)");
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END$$

DELIMITER ;

If you had a table for all

CREATE TABLE Table1_companion
( id BIGINT AUTO_INCREMENT PRIMARY KEy
, Refid  INTEGER
,col1 TEXT
,col2 TEXT
,FOREIGN KEY (Refid) REFERENCES tavle1(id));

So you have

SELECT t1.*,t2.* FROM table1 t1 INNER JOIN Table1_companion t2 ON t1.id = t2.Refid;

And when you have a n:m Relationship

You need two tables

CREATE TABLE Table1_companion
( tc_id BIGINT AUTO_INCREMENT PRIMARY KEy
,col1 TEXT
,col2 TEXT);

CREATE TABLE Table1_relation
(
, Ref_id  INTEGER
, Ref_tc_id   BIGINT
,FOREIGN KEY (Ref_id) REFERENCES table1(id)
,FOREIGN KEY (Ref_tc_id) REFERENCES Table1_companion(tc_id ));

Of course you need two joins for that.

You could take a look at normalization.

With that you have a relational database, that works with every rdms.

this is of course only a basic idea,  you must have your own datatypes and foreign keys need of both sides the same datatype, and always remember have every information only once in  your database


Generally the defaults cause LOAD DATA INFILE to act as follows when reading input:

Look for line boundaries at newlines.
Break lines into fields at tabs.
Do not expect fields to be enclosed within any quoting characters.
Interpret occurrences of tab, newline, or \' preceded by\' as literal characters that are part of field values.
Conversely, the defaults cause SELECT ... INTO OUTFILE to act as follows when writing output:

Write tabs between fields.
Do not enclose fields within any quoting characters.
Use \' to escape instances of tab, newline or\' that occur within field values.
Write newlines at the ends of lines.
Note that to write FIELDS ESCAPED BY '\\', you must specify two backslashes for the value to be read as a single backslash.

For Further Details Please Have a Look at following Link :-
dev.mysql.com/doc/refman/5.1/en/load-data.html


MySQL query using left join on two inline views not returning rows without values in second view

You're using po in the WHERE clause, and in effect, saying that it can't be null.

Maybe use: IFNULL(po.tot2,0) instead of po.tot2


Duplicating some rows of a data flow in SSIS

I see no glaring issue with that data flow. I always suggest to do as much work as possible in your source queries, so if you can create a dataset at the outset that allows you to populate both tables via a simple split, it would certainly use less memory. But things like that are not always possible depending on the data source and data format.

Also, the trash destination is good for development/debugging but does no good in production. Remove it. Let the 'Unwanted Data'expire at the split. SSIS can figure out the rest.

As long as you avoid blocking components (UNION, MERGE, etc.) there isn't any reason this process would take days. I frequently process many millions of rows in SSIS without issue. SSIS is only as slow as the person who designed the process.

Is it currently having performance issues?


Since you don't care about the data, you can try the following which will offline and drop the datafile in question allowing you to open the database.

SQL> startup
ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2255904 bytes
Variable Size            1275069408 bytes
Database Buffers          436207616 bytes
Redo Buffers                6795264 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'C:\ORACLE\ORADATA\ORCL\TRASHED01.DBF'

SQL> alter database datafile 6 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL>


Can an InnoDB page contain data from different indexes?

No a page can only hold records from a single index.
see: innodb page header docs
long story short - you have a field PAGE_INDEX_ID which is an identifier of the index the page belongs to.

Regards,
Jony


Log sizes unexpectedly large: log_reuse_wait_desc == "log_backup"

Few suggestions from my end, Kindly check and let us know

Run SQL Profiler and check for any unusual activities.
Check if the increase in size of the log files happens regulary at or around the same time (If yes, growht in log file might be the result of scheduled job)
Run DBCC LOGINFO  and check if the VLF's are active or inactive.
I randomly GUESS the result of the log growth is due to index maintenance activities you might have scheduled.
Lastly, Just for information: You need to change the database recovery model to SIMPLE and then perform the  DBCC SHRINKFILE  command.


remove duplicate rows in mysql table that does not contain primary key

In the spirit of @yercube's answer, I have an answer that has an added twist.

CREATE TABLE stage
(
    id int not null auto_increment,
    name varchar(20),
    primary key (id)
);
CREATE TABLE stage2 LIKE stage;
INSERT INTO stage (name) SELECT name FROM item;
INSERT INTO stage2 (id) SELECT min_id FROM
(SELECT MIN(id) min_id,name FROM stage GROUP BY name) A;
UPDATE stage2 A INNER JOIN stage B USING (id) SET A.name=B.name;
TRUNCATE TABLE item;
INSERT INTO item (name) SELECT name FROM stage2;
DROP TABLE stage;
DROP TABLE stage2;

This will load stage2 with the first occurrence of each name from item, zap the item table, and load the unique occurrences back.

If you look back in @yercube's answer and compare it to my answer, his is much more simplistic because

@yercube uses one temp table, while I use two
I had to create a column for iteration control, @yercube did not need to
@yercube has fewer steps
both answers achieve the same thing

I do not expect my answer to be accepted. The sole purpose of my answer was demonstrate that other answers lose the concise clarity needed to solve your problem. Again, hats off to @yercube.


Also striping tempdb on the same underlying spindle is just as likely to increase latency as it is to lower it, since this may actually make tempdb access slower - a single drive still has the same moving parts, and the more files you are trying to read/write simultaneously, the worse off you'll be.

I would start with 2 files at most and see if it improves the situation or not. You may even consider leaving one file where it is and adding a second on the new drive (depending on the other activity on the original drive).

Also make sure they are sized exactly the same and they have the same autogrowth setting. As soon as one file is larger than the other, the proportional fill algorithm will only use one file. Consider using trace flag 1117 to make sure that all data files autogrow at the same time, keeping the proportional fill algorithm in check. Just note that this trace flag affects all databases - so if you have other databases with multiple data files, you'll want to make sure their autogrow settings won't cause an issue.


DB Design - Assigning users to multiple sites with different roles

For the simplicity of maintaining it in the future (especially if it's not you) I would go with your first design. It contains a table with information on the user (login, name,etc), a table for the site (location, category) and then a table linking a person to a site (Steve is the account admin at Site 3) The second design would make things more complicated than they really needed to be


Importing external data with specific time format into MS Access

As you have discovered, the Date/Time column type in Access does not support fractional seconds. If you need to preserve the precision (and not round to the nearest second) then you'll need to import the time values as Text and then populate another Long Integer column with the times converted to tenths-of-milliseconds (which I have abbreviated to "tms"):

After the initial import of the times as Text you will have something like this

ID  hms_time       tms_time
--  -------------  ---------
 1  00:00:00.0001
 2  23:59:59.9999
 3  00:00:00.0000
 4  03:25:45.6789
 5  22:57:40.0000

If you create a VBA function in an Access Module like this

Option Compare Database
Option Explicit

Public Function Hms2Tms(hms As Variant) As Variant
    ' convert "hh:mm:ss.dddd" String
    '     to Long Integer tenths-of-milliseconds (0..863999999)
    Dim arr() As String, rtn As Long

    If IsNull(hms) Then
        Hms2Tms = Null
    Else
        arr = Split(hms, ":", -1, vbBinaryCompare)
        rtn = CLng(arr(0)) * 36000000 + CLng(arr(1)) * 600000
        arr = Split(arr(2), ".", 2, vbBinaryCompare)
        rtn = rtn + CLng(arr(0)) * 10000 + CLng(arr(1))
        Hms2Tms = rtn
    End If
End Function

then you can use the query

UPDATE TimesTable SET tms_time = Hms2Tms([hms_time])

to calculate the numeric equivalents

ID  hms_time       tms_time
--  -------------  ---------
 1  00:00:00.0001          1
 2  23:59:59.9999  863999999
 3  00:00:00.0000          0
 4  03:25:45.6789  123456789
 5  22:57:40.0000  826600000

You can also use the following VBA function to convert a numeric "tms" time into an "hms" string:

Public Function Tms2Hms(tms As Variant) As Variant
    ' convert Long Integer tenths-of-milliseconds (0..863999999)
    '     to "hh:mm:ss.dddd" String
    Dim hh As Long, mm As Long, ss_dddd As Currency, remainder As Long, rtn As String

    If IsNull(tms) Then
        Tms2Hms = Null
    Else
        hh = Int(CCur(tms) / 36000000)
        rtn = Format(hh, "00")
        remainder = tms - hh * 36000000
        mm = Int(CCur(remainder) / 600000)
        rtn = rtn & ":" & Format(mm, "00")
        remainder = remainder - mm * 600000
        ss_dddd = CCur(remainder) / 10000
        rtn = rtn & ":" & Format(ss_dddd, "00.0000")
        Tms2Hms = rtn
    End If
End Function


Check for duplicates in column and determine which row to keep

As @sabinbio commented, you need to add a ROW_NUMBER:

SELECT *
FROM
 (
   SELECT
      stamps.stampsnr AS 'stampsnr'
     ,stamps.time AS 'time'
     ,stamps.amount AS 'amount'
     ,products.productnamn AS 'productnamn'
     ,products.Artikelnummer AS 'Artikelnummer'
     ,FContainer.id AS 'FlaggId'
     ,FContainer.FlaggId AS 'Flagga'
     ,Tempo.tempo AS 'nuTempoNr'
     ,Tempo.amounttempo AS 'amountTempon'
     ,ROW_NUMBER()
      OVER (PARTITION BY FContainer.FlaggId  -- for each FlaggId
            ORDER BY stamps.time DESC) AS rn -- assign #1 to latest time
   FROM stamps
   INNER JOIN Tempo
     ON stamps.temponr = Tempo.temponr
   INNER JOIN products
     ON Tempo.productnr = products.productnr
   INNER JOIN FContainer
     ON stamps.ID = FContainer.id

   WHERE  (stamps.TIME > '" & dtmYesterday & "' + ' 06:00:00')
     AND (stamps.TIME < '" & dtmNow & "' + ' 06:00:00')
     AND fcontainer.flaggid = 5
 ) AS dt
WHERE rn = 1
ORDER BY time


DB2 connection rejected with JDBC and ODBC

As I understand it, your development team got the error when using DbVisualizer with JTOpen (jt400.jar) for direct connections to the database.

I got the same exception with DBvis ver 9.1.10. The tool took a long time when clicking the connect button, then finally showed error code -99999.

What I do to fix this is to disable the Proxy setting of DBvis:

Menu: Tool/Tool Properties
Go to General Tag->Proxy Setting
Check "No Proxy" and restart DBvisualizer.


Why Does Microsoft SQL Server Management Studio Keep Creating Table Aliases?

The best option is to stop using the SSMS View Designer.

By sticking to using a plain old query window to create or edit views, you will have full control over the queries as well as regular practice in Transact-SQL.

Besides, the SSMS View Designer is known to be buggy (see view corrupted when viewed using design feature) or produce peculiar syntax1 (see View designer strange join syntax) or encourage syntax that has become obsolete and is now misleading (see After upgrade of SQL Server 2000 database to SQL Server 2008 the view is not sorted on the group by column).

So, in short, you will just be better off, in the long run, without the View Designer.

1Not necessarily an issue in itself, but syntax that is not very familiar to you does not lend itself well to maintainability of the query.


You could be utilizing the transaction log backups that are taken by the log shipping process.  In fact, you can't double-up on transaction log backups.

So, as long as you have the actual transaction log backups and the ability to copy them to other storage (in order for your custom backup preservation and retention for Disaster Recovery), you should be fine on the t-log front.

But, the real problem is that you can't take full (or differential) database backups.  You will need these for disaster recovery.  A file level backup is not good enough for a sound DR plan, and surely not sufficient for point-in-time recovery.  You must have, at one point in time, been able to take a full database backup in order to initialize your secondary database(s).  Why is that no longer an option?

BOL reference on Using Log Shipping as Part of a Recovery Plan


Questions around migrating to a live replicated database from an existing log shipping database in SQL Server 2016 SP1

Your requirement is real-time data synchronization from S1 to S2 for users requirement of realtime reports.

  It works but isn't quite up to date enough anymore for my users'
  needs, and having it go offline every 2 hours and interrupt any
  queries running at that point is less than ideal, so they end up
  running queries on S1 and causing untold problems.

So you have 2 options other than Log-Shipping.

Transactional Replication
AlwaysOn

Transactional Replication

Advantages:

Allows you to replicate all or required data by adding condition while adding an article to the publisher.
We can create indexes on the subscriber (S3/S2) according to the requirement of reports queries to improve report performance.

disadvantage

We need to run index maintenance script on both servers separately.
Separate FULL & T-Log backup of the subscriber database maxes redundant backup of same data.
Distribution database maintenance like index rebuilding/reorganizing, a database full & transaction log backup to make sure transaction log don't grow to consume available space.
For large OLTP database, replication performance becomes a challenge.
Above all handling, replication is quite tough.

AlwaysOn

Advantages:

Read-Only secondary database.
All database maintenance script will be executed on the primary database.
You can choose the preferred database server to execute a backup.
No need to execute T-log backup of the database across all nodes.
Easy to manage.

Disadvantage:

You can't create additional indexes on the secondary replica according to the requirement of report queries.

  Is it as simple as just detaching S2, then renaming S3 to S2?

If all SQL Server credentials, Security, and settings are same, then yes you can simply detach S2 and rename S3 to S2. make sure computer name/object name (S2) is updated at DNS server and redirecting to the IP address of new S2(renamed from S3) server.

Or

You can also CName a CName connecting to current S2. Once S3 is ready you can redirect the CName to connect S3.

This way wouldn't have to make any changes in the applications connection string whenever you'll be migrating DB server. Only you'll need to update CName on the DNS server.

  Provided the tables and indexes etc were all identical, Would all my
  users' queries just work at that point?

If you have all database objects (tables, views, type, function, stored procedures and triggers) on S2 identical to S1, Yes it will work properly.

  Instead of doing this is it a better idea to reconfigure the existing
  DB from a log shipping model to a transactional model, or is that
  painful?

I would say Yes it would be better to reconfigure Log-Shipping secondary DB to the subscriber of transactional replication.

Reason: DB on S2 is already in sync so if you initialize subscription from backup, subscriber database will take very less time to sync in comparison to the lengthy process prepare S3 server.

Thanks!


How to link users and logins in an Availability Group?

What you will have to do is connect to the instance that has a mis-matched SID, and you'll have to recreate the login and specify an explicit SID.  For instance, on the instance where you have the orphaned user and the following returns the user:

exec sp_change_users_login 'report';
go

Copy the SID from the column UserSID.  And if you already have an existing login that you want to preserve the name from on that instance, you can't.  There is no way to ALTER LOGIN and change the SID.  So, you will need to drop the login and recreate it:

drop login YourLogin;
go

create login YourLogin
with
    password = 'password',
    check_policy = off,     -- simple password and no check policy for example only
    sid = 0xC26909...................;
go

Again, you will want to set the sid parameter of CREATE LOGIN to the UserSID you retrieved from executing sp_change_users_login with the report.

Note:  You will want to set the password on this/these replica(s) to the same so that you don't have a password mismatch between replicas.

I am assuming that your login(s) in question here are SQL Server logins, as this shouldn't be a problem for Windows Auth domain accounts because the SIDs should be the same on each instance.

You could also leverage contained databases here and have the database principal authenticate at the database level.  That way you wouldn't have to worry about orphaned users.

Here is some documentation on this:
BOL reference on Management of Logins and Jobs for the Databases of an Availability Group
KB article on How to transfer logins and passwords between instances of SQL Server

Also, this should go without saying, but test this out in a development/QA environment to verify and ensure proper functionality before hitting production.


How to use pgloader to transfer sqlite to postgres with table name in Uppercase

In SQLite, table names always are case-insensitive (even when quoted).

In PostgreSQL, unquoted identifiers are folded to lower case, but then the search for the table is done case-sensitively. So the only way to get the same behaviour as in SQLite queries is to use unquoted names, which implies that the actual names must be lower case.
In other words: the table name must be testname in order for a query like SELECT * FROM TestName to work correctly.


SQL Server : parsing with XPath Performance

When running the execution plan, I noticed that each one of the 5 or
  so queries that are parsing the filter data are costing about 12%
  which is over 60% of the query just to determine the data we are going
  to be filtering by.

The query costs are based on estimates even in the actual execution plans. They do not tell you how efficient the query actually was.

The estimates are in turn based on statistics and those can be outdated giving you estimates and costs that are wrong.

The estimates for XML queries is always wrong. There is no statistics generated for XML columns and there is certainly no statistics generated for XML parameters or variables.

Have a look at this rather simple XML query.

declare @X xml;

select 1
from @X.nodes('*') as T(X);

Estimated query plan

SQL Server assumes there are 10000 elements in the XML and keeps on guessing from there. Using the nodes() function assumes that 200 of those will be returned. Before that there is a Filter operator that checks if @X is not null limiting the number of estimated rows to 66. Pure guesswork and not influenced at all by what data you actually have in your XML.

To know if a query is good enough you should have a look at things like duration, number of reads and allocated memory. Don't use the estimated cost and please don't use the percentages of individual queries to compare performance.

Your XML query could be improved as Mister Magoo suggests in a comment.

SELECT ParamValues.x1.value('(location/text())[1]', 'VARCHAR(200)')
FROM  @xml.nodes('data/teammateLocations/locations') AS ParamValues(x1);

If you don't specify the text() node, SQL server have to generate a plan that works with mixed content XML, concatenating all the node values from the sub nodes.


Perhaps you could add a counter to the loop so that every 100 rows, you run SLEEP for one second.

I will add that code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_UsersLoop`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_UsersLoop`(
)
BEGIN
  DECLARE v_BatchCount,v_BatchLimit INTEGER;
  DECLARE v_user_id INTEGER DEFAULT 0 ;
  DECLARE v_officialjobtitle VARCHAR(100) DEFAULT '';
  DECLARE v_location VARCHAR(100) DEFAULT '';
  DECLARE v_corp_id BIGINT DEFAULT 0;
  DECLARE v_name VARCHAR(100) DEFAULT '';
  DECLARE maxcount INTEGER DEFAULT 5; -- 1000; -- 10 ;  -- max numbers of users (testing) - change to select count(*) from users where role='Level 6';
  DECLARE loopcounter INTEGER DEFAULT 0 ;
  DECLARE user_cursor CURSOR FOR
  SELECT u.`user_id`,TRIM(u.`officialjobtitle`),TRIM(u.`location`),u.`corp_id`,CONCAT(TRIM(u.`fname`),' ',TRIM(u.`lname`)) `name` FROM users u WHERE u.`role`='Level 6' ORDER BY u.`officialjobtitle` ;
  SELECT COUNT(*) INTO maxcount FROM users WHERE role='Level 6' and corp_id=1; -- number of users to loop through

  SET v_BatchCount = 0;
  SET v_BatchLimit = 100;

  OPEN user_cursor ;
  get_users :
  LOOP
    FETCH user_cursor INTO v_user_id,v_officialjobtitle,v_location,v_corp_id,v_name ;
    IF v_officialjobtitle<>'' THEN
        CALL sp_Step2 (v_user_id,v_officialjobtitle,v_location,v_corp_id,v_name) ;  -- Do all process for the current user
        SET v_BatchCount = v_BatchCount + 1;
        IF v_BatchCount = v_BatchLimit THEN
            SELECT SLEEP(1) INTO @dummy;
            SET v_BatchCount = 0;
        END IF ;
    END IF ;
    SET loopcounter = loopcounter + 1 ;
    IF loopcounter > maxcount THEN
        LEAVE get_users ;
    END IF ;
  END LOOP get_users ;
  CLOSE user_cursor ;

  SELECT loopcounter AS `Rows_Processed`;

END$$

DELIMITER ;

Please note the code

SET v_BatchCount = 0;
SET v_BatchLimit = 100;

and

SET v_BatchCount = v_BatchCount + 1;
IF v_BatchCount = v_BatchLimit THEN
    SELECT SLEEP(1) INTO @dummy;
    SET v_BatchCount = 0;
END IF ;

These encapsulate the SLEEP for every 100 rows.

Feel free to increase the v_BatchLimit to 1000 or more if you are dealing with large result sets.

Please note that I changed

SELECT SLEEP(1);

to

SELECT SLEEP(1) INTO @dummy;

That way, each SLEEP command does not echo anything.


Securing a MySQL database with more than just username and password

Here are some basic security recommendations.

Set complex password.
Use validate_password plugin.
Disable remote root login.
Disallow incoming traffic apart from your app and report server.
Always create users with the proper host(don't use '%').
Rotate password at every month.
Restrict MySQL data, log and backup directories.
Try to implement SSL communication between the server and the clients.
Restrict SSH access to the server where MySQL installed.
Prevent my.cnf from other users.

This blog may help you to implement more security:

https://lalitvc.wordpress.com/2017/04/02/basics-of-mysql-administration-and-best-practices/


Is it possible for it to be inpenetrable?

Yes, build it on a physical server.  Unplug the server and put the server in a time lock physical safe.  When the safe opens plug the server back in.

I can't imagine how it would be possible as a coded solution.

You might be able to do something with an encrypted database, if you had some way to tie the encryption to a specific date.  Doing that would be a question for INFORMATION SECURITY

Related time-based encryption algorithm?


Multi-tenancy can be achieved in several ways; opaque multi-tenancy doesn't necessarily mean a design with one schema per tenant. It can also be achieved by adding a "tenant ID" column to every single table. As an example, this is the way that SAP has done it for a very long time already.

Applying this principle to your design, you would add "tenant ID" to all tables that need to be multi-tenant, while the "customers" table won't have it. Instead you need to add a "tenant_customers" table with two columns; "tenant ID" and "customer ID" to associate customers with tenants. Wikipedia calls this concept an Associative Entity.

Alternatively, you can store your tenant-specific data in one schema per tenant and have the customer table and "tenant_customers" associative table stored in its own schema. Instead of "tenant ID" you could use a string storing the "tenant schema name".


Implications of using PostgreSQL COPY command to stdout and then immeadiately reading it from stdin

The drawback is that if the operation fails part way through due to network hiccup, a reboot of HOST1, or something like that, you've done a lot of work on DB2 that needs to be rolled back and repeated.  This can blow up your WAL archive, and can lead to table bloat if you don't vacuum the table in DB2 before repeating the operation.  I'd spool it to a local-storage file on HOST2 first, unless I had a good reason not to.

Most of the caveats you link to are about PostgreSQL interpreting the data format in a different way than Excel, Python, Perl, human beings, etc. might interpret it.  Going PostgreSQL to PostgreSQL (either with a pipe or using a file intermediate) eliminates most of those caveats, assuming you choose the same options to \copy on each end and have the same table structure.  The caveats about performance and foreign keys, however, remain.


mariabackup from remote mariadb10.3 galera | '/var/lib/mysql/' (Errcode: 2 "No such file or directory")

Wrong approach - mariabackup isn't designed to work remotely. See also MDEV-15075.

So you need to run mariabackup locally on one of the nodes in the cluster.

To prevent the backup from slowing down the cluster, you can prevent the node from engaging Flow Control and allow it to fall behind the other nodes by doing:

SET GLOBAL wsrep_desync=ON;

Once the backup is completed, you can do:

SET GLOBAL wsrep_desync=OFF;

(See wsrep_desync in the MariaDB knowledge-base.)

Obviously, don't direct any database traffic at the node while you're running the backup.


MySQL: How turning off the logging affect the performance?

Except for the general log and the slow log with very small long_query_time, performance shouldn't get affected too much when on, as most logging should be written to the filesystem cache (those logs do not need to be transactional safe).

There are certain combination of configuration and workloads that certainly can affect negatively performance:

example: binary log with binlog_sync and no group commit on a heavy-write server
binlog disallows group commit for innodb in earlier versions of mysql
huge transactions altering blobs in row-based replication
workload is mostly very fast primary key lookups, que log those queries in text (a read generates a write)
Error log constantly outputing critical errors with GBs of information

But in general, with a health filesystem cache (not too much memory pressure), things should be ok (small to almost negible amount of overhead, or admissible thanks to the extra features that they provide). For example, even the binary log added, let's say, a 5% overhead, it allows read scales through replication and point in time recovery.

What should not happen is that disabling them reduces performance. Either that is an error of measure or it is provoked by something else.

Also, for future readers, be careful not to confuse "server logs" (error, general, slow, binary) with the InnoDB transaction log.


SQL Server 2014 - Disabled All Admin Users

Yes, you can get back in.  :)

Go into SQL Server Configuration Manager
Select SQL Server Services
On the right side of the pane, find your instance and right-click for Properties
On the Startup Parameters tab, in the Specify a startup parameter box, type -m and then click Add. (That's a dash then lower case letter m.)
Click OK and restart.
MSDN suggests launching Management Studio as an administrator for 2014.  The traditional way is to use SQLCMD -E.  Either way.

(If it doesn't let you because that account is explicitly denied, close the window, run the command line as a different user, and try again.)

Once you're connected, you should be able to create a new login

CREATE LOGIN [YOURDOMAIN\SomeUser] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [YOURDOMAIN\SomeUser];

or

CREATE LOGIN TempLogin WITH PASSWORD = 'SomeReallyGoodPassword';
ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin;

and/or enable the disabled accounts.

ALTER LOGIN yourLogin ENABLE
ALTER LOGIN AnotherLogin ENABLE

Because this is production and this requires a restart, you'll have to do it after hours.  Good luck!


If you want to return the minimum id for each name, then there are a few ways that you can get the result.

One way would be to use an aggregate function in a subquery.  The subquery will return the min(id) for each name, you then use this subquery an join it back to your table on those two columns:

select t1.name,
  t1.value,
  t1.id
from yourtable t1
inner join
(
  select min(id) id, name
  from yourtable
  group by name
) t2
  on t1.id = t2.id
  and t1.name = t2.name

See SQL Fiddle with Demo

Another way you can get the result would be using the row_number() windowing function.  This will allow you to create a unique identifier for each row based on the name and order the result by the id, you get the final result by returning only those rows with a sequence value of 1:

select name, value, id
from
(
  select name, value, id,
    row_number() over(partition by name
                      order by id) seq
  from yourtable
) d
where seq = 1;

See SQL Fiddle with Demo.  Both will return a result:

| NAME | VALUE | ID |
|------|-------|----|
|    A |    15 |  1 |
|    B |    12 |  1 |
|    C |    12 |  1 |


Recovering SQL Express 2016 RC1 database

You should be able to attach your database files using the instructions in the documentation: How to: Attach a Database File to SQL Server Express


See if XML element exists at any level in document with a specific value

For this you want to use the .exist() XML function as it will return a BIT (i.e. boolean) value indicating whether or not the XQuery find anything.

To handle the non-static location of an element, you would use either * (indicating that it should check all nodes of a particular level, but not other levels), or // (indicating that it should check all nodes at that level and below).

The following examples use the example query from the question as a base, and adds a few test cases to place the element at different levels, and adds a test case that changes the name to show that the XQuery isn't just selecting everything.

Test Setup (run once)

SET NOCOUNT ON;
CREATE TABLE #Table (ID INT NOT NULL, XmlCol XML);

INSERT INTO #Table (ID, XmlCol) VALUES (1, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
        <ContactLName>Brandt</ContactLName>
    </MCTLocation>
</MCTLocations>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (2, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
        <ContactLName>Grandt</ContactLName>
    </MCTLocation>
</MCTLocations>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (3, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
    </MCTLocation>
</MCTLocations>
<ContactLName>Brandt</ContactLName>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (4, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
    </MCTLocation>
    <NewElement>
       <SubElement>
          <ContactLName>Brandt</ContactLName>
       </SubElement>
    </NewElement>
</MCTLocations>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (5, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
    </MCTLocation>
    <NewerElement>
       <ContactLName>Brandt</ContactLName>
    </NewerElement>
</MCTLocations>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (6, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
    </MCTLocation>
    <NewerElement>
    </NewerElement>
</MCTLocations>
</root>
<ContactLName>Brandt</ContactLName>
');

Test 1 (* in place of a node name)

This will check all nodes at the specified level, which in this case is just under <root>. But it won't check other levels.

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/*/ContactLName[text()="Brandt"]') = 1;

Returns row with ID value of 3.

Test 2 (* in place of a node name)

This will check all nodes at the specified level, which in this case is just under <root><MCTLocations>. But it won't check other levels.

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/root/MCTLocations/*/ContactLName[text()="Brandt"]') = 1;

Returns rows with ID values of 1 and 5.

Test 3 (// in place of a node name)

This will check all nodes starting at the specified level, which in this case is just under <root><MCTLocations>, and below.

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/root/MCTLocations//ContactLName[text()="Brandt"]') = 1;

Returns rows with ID values of 1, 4, and 5.

Test 4 (/* or */ in place of a node name)

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'*//ContactLName[text()="Brandt"]') = 1;

-- and:

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//*/ContactLName[text()="Brandt"]') = 1;

Both return rows with ID values of 1, 3, 4, and 5.

These do not return row ID of 6 due to the * being a placeholder for a single node, hence the highest level allowed would be under <root> (or any top-level node).

Test 5 (// at the top level)

This will check all nodes starting at the top level.

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//ContactLName[text()="Brandt"]') = 1;

Returns rows with ID values of 1, 3, 4, 5, and 6.

Test 6 (use local variable value for element text in XQuery)

DECLARE @Name NVARCHAR(50) = N'Brandt';

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//ContactLName[text()=sql:variable("@Name")]') = 1;

SET @Name = N'Grandt';

-- exact same query, just different value in the variable
SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//ContactLName[text()=sql:variable("@Name")]') = 1;

First query returns rows with ID values of 1, 3, 4, 5, and 6.

Second query returns row with ID value of 2.

Test 7 (use function and string literal for element name in XQuery)

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//.[local-name()="NewerElement"]') = 1;

Returns rows with ID values of 5 and 6.

Test 8 (use function with local variable value for element name in XQuery)

DECLARE @Node NVARCHAR(50) = N'SubElement';

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//.[local-name()=sql:variable("@Node")]') = 1;

Returns row with ID value of 4.

Test 9 (put all of the pieces together)

DECLARE @NodeName NVARCHAR(50) = N'ContactLName',
        @NodeText NVARCHAR(500) = N'Brandt';

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//.[local-name()=sql:variable("@NodeName")]
   [text()=sql:variable("@NodeText")]') = 1;

Returns rows with ID values of 1, 3, 4, 5, and 6.

General XML Note:

XML data (in SQL Server) is encoded as UTF-16 Little Endian, same as NVARCHAR / NCHAR. Hence it is best to prefix sting literals with a capital-N when the value is really XML.


Restoring Database when system can't find filepath specified

The file path is simply V:\someFile.bak

V: relative to WHAT machine?

  I have been trying to load this into SQL Server Management Studio via the
  command

No, you have not. You have instructed SSMS to send this command to the server, which will execute it ON THE SERVER.

At no point is SSMS loading anything.

The typical mistake here would be that you have a V: drive attached to your local machine and then think that the server will find the file under V: - it will not. The file must be reachable for the SERVER process, not you.

And SSMS only acts as a glorified text editor in this case, sending your command to the database server.


Optimise MySQL SELECT with LEFT JOIN subquery

The main reason for the slow query is the join on a subquery. This will not use indexes. Then, you not only join with a derived table (subquery), but as well group total result based on subquery column - GROUP BY l.id, s.Siblings

In this case it could help to:

create temporary table from subquery, it also could include subquery
for return correct parent_id
create index on this table
use temporary table in join
drop temporary table

This could have variants, but it is often faster and less server-loading than a complicated set of subqueries with joins.


Using a linked server with OPENQUERY in a database project

I've managed to get it working:

I created a new database project master. In there I created a folder Server Object and a     file LinkedServer.sql. In the SQL file i added the linked server:

GO
EXECUTE sp_addlinkedserver @server = N'LinkedServer', @srvproduct = N'sqlserver', @provider = N'SQLNCLI', @datasrc = N'LinkedServer.domain';

After adding the database Project master to my solution and referencing it in my original databse project, I was able to build the project using the initial syntax;

CREATE VIEW dbo.vwStatus
AS
SELECT     StatusID, StatusName
FROM       OPENQUERY(LinkedServer, 'SELECT * FROM [DB].[dbo].tbStatus') AS derivedtbl_1


Grouping over multiple data dimensions (pivot)

The PIVOT operator may be of help. Take a look at Books Online

For example:

SELECT
    DtStart AS Date,
    ISNULL([1], 0) AS [Server 1],
    ISNULL([2], 0) AS [Server 2],
    ISNULL([3], 0) AS [Server 3]
FROM
(
    SELECT
        DtStart,
        nServerID,
        NDirection
    FROM dbo.events
) AS SourceTable
PIVOT
(
    SUM(NDirection)
    FOR nServerID IN ([1], [2], [3])
) AS PivotTable
ORDER BY
    DtStart ASC


You can reverse the sort direction to get the minimum instead of the maximum value:

 # Sort by myfield (ascending value) and return first document
 collection.find_one(sort=[("myfield", 1)])["myfield"]

This example assumes that:

myfield is a numeric value (so the sort order makes sense to determine a minimum or maximum)
myfield exists in the matching document returned (otherwise Python will report a KeyError when trying to reference a non-existent field).
all documents in the collection have a myfield value (documents that have do not have a myfield value will sorted before the minimum numeric values)

To ensure your sort is based on documents that actually have a myfield value you can add $exists to the query criteria:

 collection.find_one({"myfield": {"$exists": True}}, sort=[("myfield", 1)])["myfield"]

For more information on sorting see:

Use Indexes to Sort Query Results
What is the compare order for BSON types?


Should I add multiple secondary indexes to search

It really depends.  If as in your example the users do a search for just the firstname and country then the index will be used.  Let's say your user does a search by zip.  At that point SQL may do a scan on your index (generally faster than a table scan) but an index starting with ZIP would be much faster.

The problem is every index you add increases your write time.  Generally adding indexes is a balancing act between the increased write time caused by an additional index and the benefit of the index on reads.  If for example this is a table where there are tons of writes all the time you may want to be careful with your indexes.  If on the other hand it's a read only table then you can be less careful.

I recently added a very large index to a table on a query that is only being run once a day.  However that particular query is very important to my business and HAD to go faster.  So the additional cost of the index wasn't as important as the increased speed of the query.

If I had to guess from your question I would say that this search is going to be run frequently.  Given that this is your only index on the table you might add additional indexes with LastName first and another with Zip first.  Initially I wouldn't bother with an index starting with CountryCd because it isn't going to be all that selective and so probably not as useful. If on the other hand you find users querying on CountryCd alone frequently then you may have to add it later.

After your system has been running with the new indexes for a bit then you should check the sys.dm_db_index_usage_stats DMO to confirm that the index are in fact being used.


Page life expectancy very low on certain time?

It's almost certainly going to be full backups or indexing. The queries won't show up as a bunch of memory allocated but rather as intensive disk IO. If you are tracking those counters then have a look. Some other easy ways to confirm that these are the cause of that disk IO:

Looking at agent job schedules. If it wasn't checkdb, or indexing, then apps often do their own internal database clean-ups (e.g. deleting records) late at night also.
When checkdb runs it will be written to the ERRORLOG, so run sp_readerrorlog and look for anything at the PLE drop time. This is also useful because some apps like OpsMgr will run their own indexing and checkdb routines at random times without using Agent jobs.
There are also other queries you can run to identify if indexes or stats were recently updated (you'd run it in each database and cast an eye over the results). They aren't 100% accurate but if you saw lots of indexes or stats updated at the PLE drop time it would be an indicator.

Otherwise just stay up late one night and run sp_WhoIsActive interactively to see what's happening. You can also make sp_WhoIsActive output to a table, which means you can schedule an Agent job to run it to a table a few times during your PLE drop window, and then check the output in the morning.

If it turns out to be checkdb or indexing this PLE behaviour is fairly normal and doesn't cause a problem unless you have a very high throughput or time sensitive system that's very busy that early in the morning; if nobody is complaining (and you don't see an increase in query timeouts at that time if you're even tracking them) then likely it's just smoke and no fire.

What if it's not checkdb or indexes? You could have a bad query compilation combined with special parameters suddenly chewing up disk IO and wrecking the buffer. If you're lucky it'll be obvious when you run sp_WhoIsActive, but if you're unlucky then you'd attempt to go through the plan cache using DMVs to find it.

That's a very tough thing to do and there are entire books on it; even if you have the scripts they often don't really aggregate how a plan changes for a query over time along with execution times. You can waste days and weeks trying to work it out while setting up extended events only to find they didn't capture the plan handle for some reason or it's a cursor and you don't know what it's really doing.

If you reach that point then tell your boss you need a tool to assist and buy a license of a proper monitor like SQL Sentry to deploy just during the real tricky problems like this. That's what I did. You can also use an evaluation or use another similar tool to drill down.

Anyway if it does turn out to be a false alarm, surprise, that's a common downside of OpsMgr. It may be amazing for some tasks but for SQL Server it's never been a good fit no matter how they try to shoehorn it. I haven't been able to tune its alerting to a functionally acceptable level so can't comment on improving that.


TINYINT vs Nullable BIT performance in MS SQL Server

It's highly unlikely that there is a material performance difference here.

So the choice is between

create table AppUser
(
  Id int identity primary key,
  Name nvarchar(200) not null,
  UserAccepted bit null
)

And

create table UserStatus
(
  Id tinyint primary key,
  Description varchar(200)
)

create table AppUser
(
  Id int identity primary key,
  Name nvarchar(200) not null,
  UserStatus tinyint not null references UserStatus default 0
)

And while one might be preferable in your particular application, they are both reasonable designs.


Avoiding a possible table reference error

Based on the comments, it sounds like every department_role will belong to one and only one manager (but a single manager could manage multiple department roles).

If this is the case, then the database design should enforce it; department_role should include the column manager_id, a foreign key to the employee table.

And, if that's true, and every employee with a manager has a department_role as well, then including manager_id in employee would be denormalizing the database; you'd have to keep the employee.manager_id in sync with the department_role.manager_id. Since you could identify the manager based on the department_role_id, manager_id shouldn't be in employee.

You brought up the concern that this would be an illegal reference in Oracle. I located a StackOverflow Q & A that addressed this.

Their solution is:

Create the table without the constraint first
Optionally, "prime the pump" by manually inserting managers and database roles before adding the constraints.
Create the constraints next (before inserting any data), using ALTER TABLE ... ADD CONSTRAINT ....
If at some point you find that you can't add a manager because a department role doesn't exist, and you can't add the role because the manager doesn't exist, you can use the following to get around it:

ALTER SESSION SET CONSTRAINTS = DEFERRED;
insert into emloyee (<column_list>) values (<value_list>);
insert into department_role (<column_list>) values (<value_list>);
commit;

That said, given your set-up, I don't think you'll need to (unless someone is explicitly given a department_role that they manage, meaning they manage themselves, which seems unlikely).

NOTE: Logically, the top person in the organization will not have a manager, and may or may not have a department role, so your constraint must allow for that (either an employee with no department_role_id, or a department_role with no manager_id). For example, either the company CEO has no department_role listed, or the department_role of "CEO" would have no manager listed.


Show Dollar Amount By Name On A Month To Month Basis

To tackle this problem, I did the following.

Created my tables (workprovided and people):

CREATE TABLE workprovided(wpid VARCHAR(20), dollaramount INTEGER, dateofwork timestamp);

CREATE TABLE people (personid VARCHAR(20), name VARCHAR(20));

Populated my tables:

INSERT INTO workprovided VALUES('A', 200, '2016-01-01 00:00:00-05');
INSERT INTO workprovided VALUES('A', 200, '2016-01-13 00:00:00-05');
INSERT INTO workprovided VALUES('A', 400, '2016-12-01 00:00:00-05');
INSERT INTO workprovided VALUES('A', 500, '2016-12-07 00:00:00-05');

and

INSERT INTO people VALUES ('A', 'Billy');

And then ran the following query, largely based on yours. You were close, just not grouping by the DATE_TRUNC - which means that your results were inconsistent.

SELECT
  na.name,
  SUM(jl.dollaramount),
  DATE_TRUNC('MONTH', jl.dateofwork)
FROM workprovided jl
INNER JOIN people na
ON jl.wpid = na.personid
GROUP BY na.name, DATE_TRUNC('MONTH', jl.dateofwork)

Result:

name;     sum;           date_trunc
-----     ----           -----------
Billy;    400;           2016-01-01 00:00:00
Billy;    900;           2016-12-01 00:00:00

Note that this sums them by the DATE_TRUNC('MONTH'...) part - which is, if I've understood you correctly, what you're looking for?

For extra credit, I came up with this - nicer formatting and sorting:

SELECT
  na.name,
  SUM(jl.dollaramount),
  CASE
    WHEN EXTRACT(MONTH FROM jl.dateofwork) = 1 THEN 'January'
    -- fill in the rest here...
    WHEN EXTRACT(MONTH FROM jl.dateofwork) = 12 THEN 'December'
  END AS Month
FROM workprovided jl
INNER JOIN people na
ON jl.wpid = na.personid
GROUP BY na.name, Month, EXTRACT(MONTH FROM jl.dateofwork)
ORDER BY na.name, EXTRACT(MONTH FROM jl.dateofwork) ASC

Result:

name;     sum;    month
-----     ---     -------
Billy;    400;    January
Billy;    900;    December


SELECT  u.user_id,
        u.user_name,
        u.pass_word,
        p.permission_name,
        p.permission_type
FROM Users u
  INNER JOIN Permissions p
ON u.permission_id  = p.p_id;

Max.


MySQL how do I priorities single column by multiple keywords

Full text searches have their good site, but need a bit of learning, but are interesting for big tables with alot of text.
you can combine columns also for searches
Also check the manual
A small example how it works

CREATE TABLe t1 (title varchar(20),
          FULLTEXT (title))

insert into t1 VALUES ("men\'s t'shirts"),("men\'s socks"),("men\'s socks short")

SELECT * ,
(IF(MATCH (title)
        AGAINST ("men\'s" IN NATURAL LANGUAGE MODE) > 0,1,0)
        )+
        (IF(MATCH (title)
        AGAINST ('socks' IN NATURAL LANGUAGE MODE) > 0,2,0) )
+
        (IF(MATCH (title)
        AGAINST ("+men\'s +socks" IN NATURAL LANGUAGE MODE) > 0.03,3,0))    as weight
        FROM t1

title             | weight
:---------------- | -----:
men's t'shirts    |      1
men's socks       |      6
men's socks short |      6

db<>fiddle here
For much bigger searches and more possibilities, you could also look into elasticsearch