Is it safe to use Strings instead of SqlStrings for CLR function input parameters?

Excellent question. As far as I am aware of, under those conditions (i.e. guaranteed no NULLs and no need for the extra functionality) there shouldn't be any specific concerns. This could be a situation similar to CURSORs where, if a generic rule is needed, it would be: "don't use cursors". But, the actual rule is: "only use cursors when/where appropriate". The problem is educating people on the technical details of cursors such that they can make that decision, which is those of us who know enough about such things ignore the generic rule and proceed to use them appropriately.
So, I advise folks to "always" use the Sql* types because it cuts down on confusion and errors. But, that is not to say that using string in your situation wouldn't be better. I say go for it, and if you ever run into a problem with string, it's easy enough to go back and change it to be SqlString.
With regards to collation and your statement of:

If it matters, I'm using SQL Server's default collation.

While it generally does not matter, it's also a bit unclear what you mean here given that there is no true default collation. Chances are that you are referring to the unfortunate default collation when installing SQL Server on an OS with a language setting of "US English" (i.e. LCID = 1033), which is SQL_Latin1_General_CP1_CI_AS. But then there are still three levels of collations that can all be different (Instance / Server, Database, and Column), and you might be meaning only one or even two of these levels.
The reason I mention all of this is that there are some non-obvious things going on here:

to some degree, none of those 3 levels that collations affect is relevant since the default culture of SQLCLR threads is the Language setting at the OS level (the LCID of the selected language). This impacts operations using String.Equals when using either of the two StringComparison.CurrentCulture* values, and operations using String.Compare when not specifying a culture.

to some degree, none of those 3 levels that collations affect is relevant since the = operator does an ordinal comparison (i.e. should be the same as using a _BIN2 collation). This is also how String.CompareOrdinal works, as well as String.Equals when not passing in StringComparison.CurrentCulture* or StringComparison.InvariantCulture* values.

one instance where the SQL Server collation matters is when concatenating a SqlString input parameter with a string via +. In this case the + operator creates a new SqlString to contain the value of the string so that it can then concatenate the two SqlStrings. The problem is that the new SqlString is created with the current threads LCID (which is the Operating System's LCID), and then the + operator compares the two SqlStringss prior to concatenation (i.e. verifies that they are the "same type"). But, due to the SqlString input parameter having the LCID of the database (not instance or column) and the implicitly created SqlString having an LCID of the OS, the operation gets an exception stating that the "collations" do not match. Nice, eh?
This, however, should be a non-issue since nobody should ever use the SqlString value directly when wanting the string. Everyone should instead always use the Value property to get the string.

That being said, I am curious as to what test you did to determine that string was faster. I tested a simple UDF that accepts a single NVARCHAR(4000) input parameter, concatenates a short string and then returns the new value. One version of that UDF accepts and returns string, and the other version accepts and returns SqlString. Over 1 million iterations, the string version was about 200-300 milliseconds faster than the SqlString version, about 50% of the time, when comparing their fastest times (over all 1 million iterations, not per each). The other 50% of the time the performance gain was around 100 milliseconds, but could also be none.
Also, regarding your test code: is s1 always the direct input parameter, whether  it's string or SqlString? If yes, then you should also test creating a string locally and setting it s1.Value. Meaning:
string s2 = s1.Value; // when s1 is SqlString instead of string

fixed (char* chptr = s2)
{
    char* cp = (char*)current;

    for (int i = 0; i < s2.Length; i++)
    {
        cp[i] = chptr[i];
    }
}

Also, some other options to possibly test:

SqlString.GetUnicodeBytes method (returns byte[])
SqlChars.Value property (returns char[])


Calculate Quantity Based on First in, First Out (FIFO)

This is not a trivial problem but it wouldn't be very hard with window functions (and CTEs for readability).

MySQL has not implemented either but lets see how it could be done:

WITH
  running_purchase AS
  ( SELECT po, date, quantity, item,
           SUM(quantity) OVER (PARTITION BY item
                               ORDER BY date, po
                               ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND CURRENT ROW)
             AS running_total
    FROM purchase
  ),
  running_stock AS
  ( SELECT so, date, quantity, item,
           SUM(quantity) OVER (PARTITION BY item
                               ORDER BY date, so
                               ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND CURRENT ROW)
             AS running_total
    FROM stock
  )
SELECT
    s.so, p.po, p.item,
    LEAST(p.running_total, s.running_total)
    - GREATEST(s.running_total - s.quantity, p.running_total - p.quantity)
        AS quantity
FROM running_purchase AS p
  JOIN running_stock AS s
    ON  p.item = s.item
    AND s.running_total - s.quantity < p.running_total
    AND p.running_total - p.quantity < s.running_total
ORDER BY
    p.item, p.date, p.po ;

Tested at SQLFiddle (in Postgres).

Note that MariaDB (which can replace MySQL) has announced that they are working on window functions and CTEs, probably on their next version (10.2). See MariaDB 10.2 Release notes.

For current MySQL versions, it has to be more complex but the logic is the same:

SELECT
    s.so, p.po, p.item,
    LEAST(p.running_total, s.running_total)
    - GREATEST(s.running_total - s.quantity, p.running_total - p.quantity)
        AS quantity
FROM
     ( SELECT p1.po, p1.date, p1.quantity, p1.item,
             SUM(p2.quantity) AS running_total
       FROM purchase AS p1
         JOIN purchase AS p2
           ON  p1.item = p2.item
           AND  ( p1.date > p2.date
               OR p1.date = p2.date AND p1.po >= p2.po)
       GROUP BY p1.item, p1.date, p1.po
     ) AS p
  JOIN
     ( SELECT s1.so, s1.date, s1.quantity, s1.item,
             SUM(s2.quantity) AS running_total
       FROM stock AS s1
         JOIN stock AS s2
           ON  s1.item = s2.item
           AND  ( s1.date > s2.date
               OR s1.date = s2.date AND s1.so >= s2.so)
       GROUP BY s1.item, s1.date, s1.so
     ) AS s
  ON  p.item = s.item
  AND s.running_total - s.quantity < p.running_total
  AND p.running_total - p.quantity < s.running_total
ORDER BY
    p.item, p.date, p.po ;

Tested at SQLFiddle-2 (in MySQL 5.6).


How to store long string with netstring format in varchar column?

Don't worry about where you put the column. In the background the sql server will store it in a special place (allocation unit for lob_data). See here
So keep it simple and leave the column in the table.
Now if you have a possibility to store it as json instead of netstring try to do that as the sql server has native functionality to deal with json since version 2016


Should login id and password be stored in separate table

Whether you use one table or two, you should be able to insert all the data needed for in a single transaction.

More importantly, you should not store the password, but instead store a password hash. Here is one post on using a password hash such as SQL Server itself uses.  This is using SHA_512 hashing with a 32-bit salt.

http://sqlity.net/en/2460/sql-password-hash/

This way you never store the actual password. At the time of the creation of the password, you need to share the password with your new user. And ideally force him to change the password at first use.

Each time a new password is created, you must, of course, update the hash to match the current password. Which means that you must manage all password changes, not just the initial password.

EDIT Re: "how can I am able to enter employee details in employee table and generate user id / password for user and store in from separate table in one action."

You must do the work in a transaction.  Here is a sketchy outline using 3 stored procedures.  Of course, you need to write the actual code:

BEGIN TRANSACTION;
   EXEC InsertNewUser @Name='UserName', ... , @NewUserId INT OUTPUT;
   EXEC CreateNewUserPassword @UserID=@NewUserID, ..., @Password NVARCHAR(128) OUTPUT;
   EXEC SendPasswordToNewUser @NewUserID, @Password;
COMMIT TRANSACTION;

By collecting the new @UserID and @Password values you can use them in the following steps.


MongoDB shard keeps growing, I don't know why?

If you have one database with hundreds of collections (it's not clear that is the case because you have not included an sh.status() or similar but that seems to be what you are implying), then here is the likely scenario:

The prodLN database has rs1 as its primary shard
Therefore all new collections created within that database will be created on rs1
If you then shard the collections (data is sharded on a collection level, not a database level) the data will be migrated from rs1 to other shards, but then you must rely on MongoDB to clean up the data post-delete (this also assumes a good shard key to distribute the data well across the shards)
You have not mentioned what version you are running, but there are lots of answers out there about deleted space reuse already (some pointers: 1 2 3), let's just say it's not very efficient for certain usage patterns (including yours)
Someone has added a maxSize parameter to the database, so it will be considered "full" and never be a target for chunk migrations, but that does not override the primary shard behavior when creating new databases, they still go on the primary shard, take up space, then get moved off by the balancer

What can you do? Well, you have a few options, but they require changes and/or downtime for the use of the current database:

Start using more than one database, make sure that the new databases have different primary shards (use movePrimary if not), start writing data there, maybe rotate around several
You can use movePrimary on the current database, but you have to stop writing to any unsharded collections (read the behavior section in the link above).  Depending on how much data there is in unsharded collections this could take some time.
You can attempt to reclaim space on the problem shard to buy yourself some time.  If you want to minimize downtime then I would recommend a rolling resync but this is only possible if you have 3 voting members in each set - you may only have two based on your output, unless arbiters have been excluded.  You could add a third member temporarily to the problem shard to perform the resyncs, then remove it later.


Does Grant on MySQL overwrite previous permissions?

Suppose S3 is a table of database DB2, you would

grant insert on DB2.s3 to 'user'@'domain-or-ip-address'

But often you would

grant all on DB2.s3 to 'user'@'domain-or-ip-address'

There is no overwriting, grant modifies existing privileges.


Minimum credentials on SQL Server to connect via Power BI Desktop

I made a test SQL user, created the test user in a Test database I created, and granted it datareader access using scripts, and this worked without an issue.  Switch out the usernames and database names for your app and report in on what happens when you start fresh.  It could be as simple as you're using a old user that is orphaned to other issues so starting fresh could help you isolate it.

Note if you are using this for production you'll want to probably remove the 'check_policy=off' which lets you have passwords that don't conform to your Windows policy.

This creates the user in SQL Server:

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

This creates it in the DB and adds it to the datareader list:

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


I've been searching and i found one solution for this problem, the only one thing that i do was put "WITH (NOLOCK)" at the end of the query, something like this:
"SELECT * FROM table1 WITH (NOLOCK)", with that statement the query is able to read the rows locked.

I hope i was clear and that this can be useful for someone

Regards


First of all, better to combine both of your structures:

and have PK
and have index for "created"
as a variant, think about a natural PK - created + something which uniquely identifies a row
added - also think about units (system_unit) - if it is identification of your point(?), it may be a good idea to change it to INT format, it reduces memory and disk space usage for this column.

PK could be good for some other queries, and InnoDB still creates it even if you do not define it, but you can not use it in this case.

More importantly, response time depends not only on indexes (of course it MUST HAVE), but also from number of records. You can change the query to:

SELECT count(*) FROM <table_name> WHERE created BETWEEN '2017-01-01' AND '2017-03-13';

and it will show you - how many records the server must return.

The only few ways for feature improvements:

reduce timeframe
reduce number of columns - but this is not your case
faster server - SSD (if still not), increase memory for InnoDB buffer at least (if it is less than necessary)


to query the active directory I use the procedure xp_logininfo

  Requires membership in the sysadmin fixed server role or membership in
  the public fixed database role in the master database with EXECUTE
  permission granted.

I have been using the following script and it works fine for me:

SELECT @@SERVERNAME
--myserver

DECLARE @NTLogin nvarchar(128);
SET @NTLogin = 'mycompany\person_or_group_I_want_to_check'

DECLARE @UserList TABLE (
         [Account Name] nvarchar(128),
         [Type] nvarchar(128),
         [Privilege] nvarchar(128),
         [Mapped Login Name] nvarchar(128),
         [Permission Path] nvarchar(128) )

INSERT INTO @UserList EXEC xp_logininfo @NTLogin, 'all' --insert group information

IF EXISTS (SELECT * FROM @UserList WHERE [Type] = 'group') --only if it's a group
   INSERT INTO @UserList EXEC xp_logininfo @NTLogin, 'members' --insert member information

SELECT * FROM @UserList


Join two tables where the other table has no equivalent value in the other table using MySQL

For example, it can look like

FROM table1
JOIN table2 ON table1.TDNO = SUBSTRING_INDEX(table2.tdno, ' ', -1)


How to query Wikipedia's database to retrieve the main topic and the surface forms of a given title?

Initially it seems pretty easy to solve the question, by adding a new criteria to the WHERE clause.

SELECT
    page.page_id,
    page.page_title,
    redirect.rd_from,
    redirect.rd_title
FROM
    redirect
    JOIN page ON page.page_id = redirect.rd_from
WHERE
    redirect.rd_title = 'Dog_breeding' or page.page_title = 'Dog_breeding'

But as Lazhar has pointed out, this only works if the requested text belongs to the main wiki page, not for the redirected pages.

After taking a look at table definitions:

MediaWiki - Manual:page table

  page_is_redirect
  A value of 1 here indicates the article is a redirect; it is 0 in all other cases

MediaWiki - Manual:redirect table

  Contains for each page that is currently a redirect (i.e., not for old revisions which are redirects) the id of the source page and the information about target. The target page may or may not exist.

I've decided to use this field to solve the problem.

First we need to know the title of the main page, the page that is not a redirection. In this question: Dog_breeding

select page.page_title as title from page where page.page_title = 'Dog_breeders' and page_is_redirect = 0
union
select redirect.rd_title as title
from redirect
where redirect.rd_from in (select page.page_id from page where page.page_title = 'Dog_breeders' and page_is_redirect = 1)

Then using the first approach:

SELECT
    page.page_id,
    page.page_title,
    redirect.rd_from,
    redirect.rd_title
FROM
    page
    left JOIN redirect ON page.page_id = redirect.rd_from
    join (select page.page_title as title from page where page.page_title = 'Dog_breeders' and page_is_redirect = 0
          union
          select redirect.rd_title as title
          from redirect
          where redirect.rd_from in (select page.page_id from page where page.page_title = 'Dog_breeders' and page_is_redirect = 1)
          ) cteTitle
WHERE
    redirect.rd_title = cteTitle.title or page.page_title = cteTitle.title;

This is the result:

+----------+--------------+----------+--------------+
|  page_id |  page_title  |  rd_from |   rd_title   |
+----------+--------------+----------+--------------+
|  796236  |  Dog_breeder |  796236  | Dog_breeding |
+----------+--------------+----------+--------------+
| 16274701 | Dog_breeders | 16274701 | Dog_breeding |
+----------+--------------+----------+--------------+
| 37353772 |   Stud_dog   | 37353772 | Dog_breeding |
+----------+--------------+----------+--------------+
| 40738352 |  Dog_Breeder | 40738352 | Dog_breeding |
+----------+--------------+----------+--------------+
|  476072  | Dog_breeding |   NULL   |     NULL     |
+----------+--------------+----------+--------------+

Can check it here: http://rextester.com/OQMV97513

NOTE: There is another solution using pagelinks table in SO


How to update "sorting_order" column of other rows when changing one?

Add new category :

    $t_cat = 'jeans';//new category to insert
    $so_to_set = 2;// so for new category

First update all so greater than or equal to $so_to_set

    $query = "update categaries set so = so+1 where so >= $so_to_set";

Now insert new category with the $so_to_set

    $query = "insert into categaries(name,so) values($t_cat,$so_to_set)";

Change so for categories :

suppose we have following categaries with so

    +-------------------------+
    | id  |   name    | so    |
    +-----+-----------+-------+
    | 1   |   shoes   |  1    |
    | 6   |   jeans   |  2    |
    | 2   |   shirts  |  3    |
    | 3   |   pants   |  4    |
    | 4   |   blazzers|  5    |
    | 5   |   bags    |  6    |
    +-------------------------+

    $a = 2;// so of category whose position we want to change
    $b = 5;// new so for category

    // get id of category whose position we want to change
    $temp_id = "select id from categaries where so = $a";
    // that would be 6

Now update all so between $a and $b

    if($a < $b){
        // if $a is less than $b then
        $set = "so = so - 1";
        $where = "so > $a and so <= $b";
    }else{
        $set = "so = so + 1";
        $where = "so < $a and so >= $b";
    }

    // update so's
    $query = "update categaries set $set where $where";

now update so for $temp_id

    $query = "update categaries set so = $b where id = $temp_id";

Final output of the process would be

    +-------------------------+
    | id  |   name    | so    |
    +-----+-----------+-------+
    | 1   |   shoes   |  1    |
    | 6   |   jeans   |  5    |
    | 2   |   shirts  |  2    |
    | 3   |   pants   |  3    |
    | 4   |   blazzers|  4    |
    | 5   |   bags    |  6    |
    +-------------------------+


Need to Bring Back most Recent Departure Date from Sales table for Customers in Recipient table

If the only two references from the table GccTbl_Sales are the columns iCustomerId and tsDepart, this should work:

SELECT DISTINCT

iRecipientId AS RecipientID, NmsRecipient.iCustomerId AS Cust_Num, sFirstName AS FirstName, sLastName AS LastName, sEmail AS Email,
iBlacklistPostalMail AS NoMail, iBlacklistEmail AS NoEmail, iBlacklistMailGCCL AS DontMailGCCL, iBlacklistGCCL AS DontEmailGCCL,
iBlacklistMailOAT AS DontMailOAT, iBlacklistOAT AS DontEmailOAT, sLastBrand AS LBT,sFrequencyDetail AS Frequency, sRecency AS Recency,
GccTbl_HouseholdScores.iNumTripsBkd AS Trips, GccTbl_Sales.tsDepart AS Departure_Date

FROM (neolane.NmsRecipient

INNER JOIN neolane.GccTbl_HouseholdScores ON NmsRecipient.iRecipientId = GccTbl_HouseholdScores.iHouseHoldId)
INNER JOIN (
            SELECT GccTbl_Sales.iCustomerId, MAX(GccTbl_Sales.tsDepart) AS tsDepart
            FROM neolane.GccTbl_Sales
            GROUP BY GccTbl_Sales.iCustomerId
            ) AS GccTbl_Sales ON NmsRecipient.iCustomerId = GccTbl_Sales.iCustomerId

WHERE iIrateFlag= 1
    AND iOperationalFlag= 1
    AND iEmployeeFlag= 0
    AND iDeceased= 0

ORDER BY iblacklistPostalMail,iBlacklistEmail,iBlacklistMailGCCL,iBlacklistGCCL,iBlacklistMailOAT,iBlacklistOAT;

And if the DISTINCT was put there in order to remove the duplicates of iCustomerID, I think you're safe to remove it now.


Need a solution on dataset as given below

;with LetterSet as
(
    select *,
        row_number() over(partition by FirstCol order by SecondCol) as rownum
    from YourLettersTable
)
select
    FirstCol,
    SecondCol,
    ThirdCol,
    FourthCol,
    FifthCol
from LetterSet
where rownum = 1

Likewise, you could have rownum = 2 for your second result set.


Here's another option:

mysql> SET GLOBAL wsrep_desync=1;
shell# <execute backup>
mysql> SET GLOBAL wsrep_desync=0;

Just make sure to take the backup from the non-write node and no traffic is going through the node that's running XtraBackup. Test first on non-production systems.

https://www.percona.com/doc/percona-xtradb-cluster/5.6/wsrep-system-index.html#wsrep_desync


SQL Database Snapshot before code depolyment faster than backup

Depending on the changes being performed, database snapshots are a viable alternative to backups. You need to ensure you're meeting the prerequisites and that you won't be impacted by the limitations for the source and snapshot (see here).

There are limitations around certain feature usage, such as no MEMORY_OPTIMIZED_DATA filegroups, that prevent snapshots. In addition, some features like FILESTREAM filegroups and Full Text Indexes are unavailable in the snapshot database.

You also need to ensure you have sufficient disk space available when undergoing post-deployment testing because the snapshot will grow as you change data.

You also can't drop database files from the source DB after a snapshot is created. In circumstances where this is happening, backup is the solution.


Break the link between a Database User and SQL Server Login

You can accomplish this in a roundabout way be creating a temporary login, remapping the user to the temporary login, and then dropping the temporary login.  For a SQL login:

USE [master]
CREATE LOGIN [temp_user] WITH PASSWORD=N'asdf' MUST_CHANGE,
    DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
ALTER LOGIN [temp_user] DISABLE

USE [test_database]
ALTER USER test_user WITH LOGIN = [temp_user]
DROP LOGIN [temp_user]

For a Windows authenticated login/user, you would need to create a temporary Windows account, and then delete it, so it's not a 100% T-SQL solution:

-- Create a Windows account with a name of Temp_User
USE [master]
CREATE LOGIN [COMPUTERNAME\Temp_User] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
ALTER LOGIN [COMPUTERNAME\Temp_User] DISABLE

USE [test]
ALTER USER [COMPUTERNAME\Existing_User] WITH LOGIN = [COMPUTERNAME\Temp_User]
--Delete the Temp_User Windows account


According to the documentation:

  In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.

Therefore, a more direct approach would be:

INSERT INTO `table2`(`aid`, `count`)
SELECT `aid`, COUNT(`aid`) AS `count`
FROM `table1`
GROUP BY `aid`
ON DUPLICATE KEY UPDATE `table2`.`count` = VALUES(`count`)


How to let particular logins to work on the secondary replica only?

You can configure this by setting the "Connections in primary role" setting when configuring read-only routing. You have 2 options:

Allow all connections (default)
Allow read/write connections - which really means, "Connections where the Application Intent connection property is set to ReadOnly are not allowed. This can help prevent customers from connecting a read-intent work load to the primary replica by mistake."

This setting isn't configured at the login level though - this relies on what you said in the first part of your question, that your logins are using ApplicationIntent=ReadOnly in their connection string.

If they don't ask for that specifically - meaning, if they don't declare that they're only going to write - then SQL Server has no fast way of knowing that they have no writeable permissions in any database, on any object. After all, they could even execute a stored procedure signed with a cert, and that cert could allow them to do writes.

So if you really want to stop them from logging in even if they don't use ApplicationIntent=ReadOnly, then I'd do it by simply disabling their logins on the primary. Then, run an Agent job every minute on every replica, checking to see if this particular replica is a primary. If a failover has occurred, and this replica is now a primary, disable the reporting-only logins. If the replica is a secondary, and the reporting-only logins are disabled, then enable them.

This will of course run into problems if you really do want to disable someone's login - it'll instantly be enabled again in a minute.


Speed inserts in database with a table with +47 million data per each year, containing more than 10 years?

Simple this one.
Create a staging table with the same columns as your main table. No indexes on the staging table please as this will slow it down.
Insert your rows into the staging table via .NET as normal.
Then run:

BEGIN TRAN
INSERT INTO main_table (cols here)
    SELECT cols here from staging_table
TRUNCATE TABLE staging_table
COMMIT

You can still run that SQL inline via .net, or simply use a stored proc.
Either way, the insert into the main table will be in a SINGLE insert, rather than .nets single insert for each row (RBAR).
As the above method will insert data into the main table in one go, the indexes are updated once. We use this to great effect in our warehousing databases.

As your inserting a lot of data into the main_table, an alternative is to drop all the indexes on the main_table, insert your data, then re-create the indexes. This too is a very efficient (read fast) way.


How to replicate from Standby mode database

Any reason you can't just use the native Replication features of SQL Server, such as Transactional Replication?
You can real-time synchronize between your two servers, and then reporting off the Subscriber server won't affect performance of the Publisher server.
You can also look into Snapshot Replication if you want something less real-time and more scheduled on a recurring basis.


TempDB default number of files in SQL Server 2016

According to Paul Randal the number of tempdb files should be:

equal to the number of CPU cores for 8 or less cores
1/4 to 1/2 of CPU cores for more than 8 cores

I think it's a great news that Microsoft finally changed this default setting. The previous one was really bad - it caused problems with latch contention.

Paul Randal's article on the topic


duplicate key violates unique constraint when using multi-threading

By creating a unique constraint on your table, you are telling the database "each time I try to insert a row in this table, please check that there is no existing row with this combination of columns the same. Oh, and make sure you do it in an atomic fashion so that if someone else tries to insert one at the same time as me, only one of us will succeed".

After doing that, in most cases there is little point in replicating the same logic in your application. It will only make the process less efficient. You would be better off to take @horse's suggestion and catch the exception.

There are a couple of things to watch out for though. One is that if you doing this inside a transaction, then the transaction will be automatically rolled back on the error. If you did anything else prior to this in the transaction, it will have been undone. To avoid this, you need to set a SAVEPOINT prior to the insert, and then RELEASE or ROLLBACK the savepoint as appropriate.

The other issue is that the postgresql log will still contain these errors, even if they are caught and ignored by the Java code. Flooding your logs with meaningless noise helps to hide true problems that might be lurking in there, so it is not a good thing.

Possibly a better solution, which avoids both of these problems, is to create a stored procedure which inserts the record, handles any exceptions which occur, and returns an indication to your application as to whether the record was stored or not.

For example, if you had a table that looked like this:

CREATE TABLE test(a INTEGER, b TEXT, UNIQUE(A,B));

Then you could have a function like this:

CREATE FUNCTION test_insert(pa INTEGER, pb TEXT) RETURNS INTEGER AS $$
BEGIN
    INSERT INTO test(a,b) VALUES (pa, pb);
    RETURN 1;
EXCEPTION
    WHEN unique_violation THEN
       RETURN -1;
END;
$$ LANGUAGE plpgsql;

and you could use it like this:

testdb=> SELECT test_insert(1,'foo');
 test_insert
-------------
           1
(1 row)

testdb=> SELECT test_insert(1,'foo');
 test_insert
-------------
          -1
(1 row)

If an attempt is made to insert a record that violates the constraint, no exception is thrown or logged, and if executed inside a transaction, the transaction is not affected. The function returns a value which you can use to see if the record was inserted or not. It is a very basic example, but should illustrate the point.


How to search for compound words, and get the word parts returned

I would look for a library that specializes in splitting compound words.  Search Java, PHP, Perl, etc.  SQL is not likely to be involved.

If you must use a database, then consider this for a piece of the algorithm:

SELECT word FROM words
    WHERE word < 'menneskerettighedsforkæmperens'
    ORDER BY word DESC
    LIMIT 5;

That is likely to return a few compound words that begin with 'menneske' and perhaps 'menneske', itself.

In MySQL, that query will be quite fast (assuming an index on word).  You would need some code in addition to a few SQL calls.


Mysql query grouping by two columns for max date takes too long

EDIT 1: The query below is quite like the original query. It very much depends on how strict are the other conditions, i.e. how many rows can be ignored with those conditions. I think the index should contain the group-max-triple (l_a, l_b, timestamp), but if l_act='q' is a strong reduction and the condition is always there then l_act should come before l_a.

Origin:
I would write it as

select tmp.*
from tmp inner join (
    select l_a, l_b, max(timestamp) as max_time
    from tmp where timestamp < cast('2013-01-01T00:00:00Z' as binary)
    group by l_a, l_b
) as selmax
on selmax.l_a = tmp.l_a
   and selmax.l_b = tmp.l_b
   and selmax.max_time = tmp.timestamp
;

and give tmp an index on (l_a, l_b, timestamp).

And also: what says explain?

EDIT 2:

Seeing the explain and looking at the indices clears the case. l_project starts the primary key but you give no restriction on that in your inner query.

The second index needs name as a second field.

If l_act is a strong restriction (All of the queries give a l_act - condition which meets less than  1/3 of the table), you should make an index

l_act, l_a, l_b, timestamp

otherwise

l_a, l_b, timestamp

If the 4-part-index is used, it should be very fast.

I wish much fun adding the index. ;-)

If you use the 4-part-index, then you must add the l_act-condition in the inner AND the outer join.


First, see Aaron Bertrand's answer on TLS 1.2 with older SQL Server, and/or Aaron's SentryOne blog post.

If you're talking about the normal SQL Server connection encryption (SQL Server Configuration manager, Force Encrypt yes), with modern SQL Server, then there are two steps:

select encrypt_option, count(*) from sys.dm_exec_connections GROUP BY encrypt_option

If there are any FALSE results, you have unencrypted connections.  Fix that.
If there are 100% TRUE or NULL results, continue.

With buy-in from and in coordination with your IT Security team, download Microsoft Message Analyzer, Microsoft's Windows packet sniffer, and watch some connections.  You're looking for the ClientHello and ServerHello messages, within which you can see cipher suites offered and which one happened to be accepted.

It's a combination of registry and group policy changes to adjust that at the Windows OS level of your SQL Server installations.

WATCH OUT FOR THIRD PARTY SOFTWARE

Lots of it does NOT support TLS of ANY kind, much less TLS 1.2, either in the application, or, much more often, during one or more parts of the installation and/or upgrade process.  The vendor will be clueless if you ask beforehand, and just as clueless watching it happen in front of them.


SQL Server : execute stored procedure from another stored procedure

you could store your result in a Table Variable

DECLARE @TableVar TABLE (col1 varchar,col2 int,...)
insert into @TableVar
exec (Proc1)

select Col1 from @TableVar


Why do some rows returned by sys.dm_exec_query_profiles have "???" for the physical operator name?

Batch mode adapters (places in a query plan in which row processing switches to batch processing or the other way around) show up as ??? in the DMV with a thread_id of 0. However, the example query doesn't use batch processing so that isn't the cause here.
Nested loops prefetching can also be responsible for extra rows showing up in sys.dm_exec_query_profiles. There is a documented trace flag for disabling nested loop prefetching:

Trace flag 8744 disables pre-fetching for the Nested Loops operator.
Incorrect use of this trace flag may cause additional physical reads when SQL Server executes plans that contain the Nested Loops operator. For more information about the Nested Loops operator, see the "Logical and physical operators reference" topic in SQL Server 2005 Books Online.

If I add a query hint of QUERYTRACEON 8744 to the query then the ??? nodes no longer appear.
For a reproducible example of nested loop prefetching I'm going to borrow Paul White's example against Adventure Works from his Nested Loops Prefetching article:
SELECT TOP (1000)
    P.Name,
    TH.TransactionID
FROM Production.Product AS P
JOIN Production.TransactionHistory AS TH
    ON TH.ProductID = P.ProductID
WHERE
    P.Name LIKE N'[K-P]%'
ORDER BY
    P.Name,
    TH.TransactionID;

If I run that query against SQL Server 2016 SP1 and quickly capture the output of sys.dm_exec_query_profiles I get the following results:
╔════════════════════╦════════════════════════╦═════════╦═══════════╗
║    OBJECT_NAME     ║ physical_operator_name ║ node_id ║ thread_id ║
╠════════════════════╬════════════════════════╬═════════╬═══════════╣
║ NULL               ║ Top                    ║       0 ║         0 ║
║ NULL               ║ Nested Loops           ║       1 ║         0 ║
║ TransactionHistory ║ ???                    ║       2 ║         0 ║
║ Product            ║ Index Seek             ║       3 ║         0 ║
║ TransactionHistory ║ Index Seek             ║       4 ║         0 ║
╚════════════════════╩════════════════════════╩═════════╩═══════════╝

If I run the same query in SQL Server 2014 I get these results:
╔════════════════════╦════════════════════════╦═════════╦═══════════╗
║    OBJECT_NAME     ║ physical_operator_name ║ node_id ║ thread_id ║
╠════════════════════╬════════════════════════╬═════════╬═══════════╣
║ NULL               ║ Top                    ║       0 ║         0 ║
║ NULL               ║ Nested Loops           ║       1 ║         0 ║
║ Product            ║ Index Seek             ║       3 ║         0 ║
║ TransactionHistory ║ Index Seek             ║       4 ║         0 ║
╚════════════════════╩════════════════════════╩═════════╩═══════════╝

In both cases the nested loop prefetch optimization happens. It appears that only SQL Server 2016 reports it though which could explain why I've never seen this in SQL Server 2014.


SMSS will start for one user, but not another?

Perhaps something in their documents\user data profile got corrupted. If you've fixed the space issue, then I suggest running a repair on the application (or installing a newer version - you can manage SQL Server 2012 with SSMS 2014 or newer, and it's free - installation instructions in this answer). However it may still end up being hosed because of that user's profile - if so, as a next step, you could back up their files and re-create their profile (though I do concede that this will force you to lose a lot of their other settings etc).


Best practice for "recycling" rows on PostgreSQL (UPDATE vs INSERT + DELETE)

Postgres is MVCC. Every write produces a new row-version irrespective of whether the operation is insert or update. So I would not expect the proposed ring buffer style to give performance benefits.
With single-value systems, and fixed length rows I would expect faster performance. I'd like to see some thorough testing before I believed it was worthwhile, though.


Good learning source for learning normalization

I know it's essentially a comic, but I keep it on my shelf for reference and to start conversations.  If you want something to talk about database normalization from scratch with good examples, I recommend "The Manga Guide to Databases by Mana Takahashi".  http://amzn.com/1593271905


Concurrent usage of rdbms, write 4K rows without lockdown

I'm not sure you understand.

PostgreSQL has max_connections. If that number is hit people won't be able to connect to the database. If your server is waiting you may get a "server busy" message or the like. As @a_horse_with_no_name suggests connection pooling may solve this problem.
You can speed up writes by batching them. When you convert from your "excel format" to SQL are you doing one insert, or one insert per row? Consider inserting into an unlogged table if you're inserting one row at a time, and then running a single INSERT INTO SELECT to UPSERT into the master.
You should also check out the non-durability settings for faster writes


Reuse SELECT query by adding results in array?

It's a textbook case for a CTE, like @Daniel commented.
The example can be simplified some more. And you need to be aware of how LIMIT works in a UNION query.

CREATE OR REPLACE FUNCTION featured_products(valid_to_in      timestamp
                                           , taxonomy_id_in   integer
                                           , product_limit_in integer)
  RETURNS SETOF integer AS
$func$
BEGIN
   RETURN QUERY
   WITH featured AS (SELECT supplier_id FROM products LIMIT 2)
   SELECT supplier_id
   FROM   featured

   UNION ALL
   (
   SELECT p.supplier_id
   FROM   products p
   LEFT   JOIN featured f USING (supplier_id)
   WHERE  f.supplier_id IS NULL
   LIMIT  product_limit_in
   )  -- parens required - or not?
END
$func$  LANGUAGE plpgsql VOLATILE;

LIMIT can only be applied once in a UNION (ALL) query, unless you enclose the leg of the query in parentheses. You may or may not want to add parentheses.

The way I have it, a maximum of product_limit_in rows are returned in addition to the "featured" rows from the CTE.
If you remove the parentheses you get a maximum of product_limit_in rows total - meaning that even "featured" products may be discarded.
Related: Optimize a query on two big tables

Either way, don't ORDER BY the outer (combined) result before you LIMIT, if you can avoid it. Postgres can optimize the query very efficiently and just stop evaluating once enough rows have been returned (possibly fetching tuples from the top of a matching index). That would not be possible any more, which can make a huge difference in performance.
Using LEFT JOIN / NOT NULL to exclude featured rows from the second SELECT, which is probably faster than NOT IN and does not carry "surprises" when dealing with NULL values or empty results.

Select rows which are not present in other table

In Postgres (as opposed to some other RDBMS), you can refer to p.supplier_id and f.supplier_id after joining with USING (supplier_id).

And yes, the CTE is only evaluated once:

  A useful property of WITH queries is that they are evaluated only once
  per execution of the parent query, even if they are referred to more
  than once by the parent query or sibling WITH queries.

Bold emphasis mine.


You can use a Calendar table for this and other purposes. A calendar table is always useful.

I've set up a minimal example, but usually a Calendar table has more detailed fields like, hours, minutes, ect.

CREATE TABLE Calendar (
    cDate datetime,
    cDay int,
    cDayOfWeek int,
    cDayName varchar(20)
);

CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO Calendar (cDate, cDayOfWeek, cDayName)
    VALUES (dateStart, DAYOFWEEK(dateStart), DAYNAME(dateStart));
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;

CALL filldates('2018-01-01','2018-12-31');

  I want empty records/dates to be filled with zeros so I need to use mysql to generate the dates and then join a matching record where available. How can I use mysql to determine the Sundays for the last year?

You can use the calendar table to build this empty table in this way:

INSERT INTO daily_stats (`date`, `stat`, `value`, `created_at`)
SELECT
    cDate, 0, 0, now()
FROM
    Calendar
WHERE
    cDayOfWeek = 7;

NOTE: cDayOfWeek could be different depending on first day of week in your system.

SELECT * FROM daily_stats LIMIT 10;

SELECT * FROM daily_stats LIMIT 10;

id | date       | stat | value | created_at          | updated_at
-: | :--------- | ---: | ----: | :------------------ | :---------
 1 | 2018-01-06 |    0 |     0 | 2019-01-19 18:16:49 | null
 2 | 2018-01-13 |    0 |     0 | 2019-01-19 18:16:49 | null
 3 | 2018-01-20 |    0 |     0 | 2019-01-19 18:16:49 | null
 4 | 2018-01-27 |    0 |     0 | 2019-01-19 18:16:49 | null
 5 | 2018-02-03 |    0 |     0 | 2019-01-19 18:16:49 | null
 6 | 2018-02-10 |    0 |     0 | 2019-01-19 18:16:49 | null
 7 | 2018-02-17 |    0 |     0 | 2019-01-19 18:16:49 | null
 8 | 2018-02-24 |    0 |     0 | 2019-01-19 18:16:49 | null
 9 | 2018-03-03 |    0 |     0 | 2019-01-19 18:16:49 | null
10 | 2018-03-10 |    0 |     0 | 2019-01-19 18:16:49 | null

db<>fiddle here


Why different users from same login group can not access the database objects?

2 possible reasons:

User A receives necessary permissions via his membership in some other group,
or explicitly being a member of server / database role, or has
permissions granted directly.
User B is denied some permissions, again - either via his membership in some other group, or explicitly being a member of server / database role, or has these permissions denied to him directly.

You can compare their role memberships on the database / server level, but membership in Windows groups cannot be checked via SQL Server - you would have to look in local groups AND Active Directory, if the machine is a member of a domain.

EDIT: here is a set of queries that may help you track down actual origins of permissions. Just make sure that you run them under the user with appropriate credentials, otherwise you will never see anything. Membership in sysadmin fixed server role is preferable.

declare @ObjectName sysname = N'YourObjectName',
    @UserName sysname = N'YourUserName';

-- Grants on database securable
select schema_name(ao.schema_id) as [SchemaName], ao.name, ao.type_desc,
    pr.type_desc, pr.name as [Principal], dp.permission_name, dp.state_desc
from sys.database_permissions dp
    inner join sys.all_objects ao on ao.object_id = dp.major_id
    inner join sys.database_principals pr on pr.principal_id = dp.grantee_principal_id
where dp.class_desc = 'OBJECT_OR_COLUMN'
    and ao.name = @ObjectName;

-- Direct permissions of database principal
select schema_name(ao.schema_id) as [SchemaName], ao.name, ao.type_desc,
    pr.type_desc, pr.name as [Principal], dp.permission_name, dp.state_desc
from sys.database_permissions dp
    left join sys.all_objects ao on ao.object_id = dp.major_id
    inner join sys.database_principals pr on pr.principal_id = dp.grantee_principal_id
where pr.name = @UserName;

-- Membership in database roles
select rl.name as [DatabaseRole]
from sys.database_role_members rm
    inner join sys.database_principals rl on rl.principal_id = rm.role_principal_id
    inner join sys.database_principals pr on pr.principal_id = rm.member_principal_id
where pr.name = @UserName;

-- Membership in server roles
select rl.name as [ServerRole]
from sys.server_role_members rm
    inner join sys.server_principals rl on rl.principal_id = rm.role_principal_id
    inner join sys.server_principals pr on pr.principal_id = rm.member_principal_id
where pr.name = @UserName;

The only thing not covered here is a membership in Windows / Active Directory security groups, but, as I've said, it is impossible to track it within SQL Server.


Configure unconstrained delegation for BULK INSERT

Your configuration looks solid. SPNs and Delegation are configured as I would have done it myself.

The only thing which you haven't explained are the file/share permissions. This is where my solution steps in.

Solution 1  (Personal Account)

Verify that your personal account is allowed to access the file on the file server's share

your personal account needs at least READ permissions at the share permission level or in most cases you might see EVERYONE has READ permission.
your personal account needs at least READ permissions at the file's permission level
Check permission inheritance at file level for that one file

Reboot the SQL Server after changing the file permissions or cycle the SQL Server service.

Alternatively: Log in to the SQL Server with your personal account and connect to the share and open the file.

Open SSMS with your personal account.
Open up a connection to your SQL Server instance with Windows Authentication.

Verify you are connected to your instance with the SQL Server service account with the script you have used before.

Perform the import with BULK INSERT

Solution 2 (SQL Server Service Account)

Verify that the SQL Server service account is allowed to access the file on the file server's share

the SQL Server service account needs at least READ permissions at the share  permission level or in most cases you might see that EVERYONE has READ permissions.
the SQL Server service account needs at least READ permissions at the file's  permission level
Check permission inheritance at file level for that one file

Reboot the SQL Server after changing the file permissions or cycle the SQL Server service.

Alternatively: Log in to the SQL Server with the SQL Server Service Account and connect to the share and open the file.

Open SSMS with the SQL Server service account.

Runas: your_domain\SQL_Server_service_account
Provide password

Open up a connection to your SQL Server with Windows Authentication.

Verify you are connected to your instance with the SQL Server service account with the script you have used before.

Perform the import with BULK INSERT

BULK INSERT permissions

  Security Account Delegation (Impersonation)

  If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data. In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.

Reference: BULK INSERT (Transact-SQL)


SQL Server Log Reader Agent while trying to add node in p2p Replication

Adding the resolution for the benefit of anyone who faced this issue on SQL 2012:

We engaged MS support on this. Apparently their stored procedure "sp_dropsubscriber" misses some tables sometimes. In our case, this entry was not deleted from table MSnosyncsubsetup. Once they found this out after few hours of analysis, a backup was taken and the row related to new subscriber was deleted.
This fixed the log reader and replication hummed along properly.


You can do this using the EXCEPT operator to remove the weekdays worked from a CROSS JOIN query that returns all possible weekdays worked by each employee.

SELECT DISTINCT wr.employee, c.work_date
FROM cal c
CROSS JOIN work_record wr
WHERE c.day_type = 'Weekday'
EXCEPT
SELECT employee, date_worked
FROM work_record wr

DDL for creating the tables and data:

CREATE TABLE [cal](
    [work_date] [date] NULL,
    [day_type] [varchar](20) NULL
) ON [PRIMARY]
GO

CREATE TABLE [work_record](
    [date_worked] [date] NULL,
    [employee] [varchar](20) NULL
) ON [PRIMARY]
GO

INSERT [cal] ([work_date], [day_type])
VALUES (CAST(N'2020-02-10' AS Date), N'Weekday'),
(CAST(N'2020-02-11' AS Date), N'Weekday'),
(CAST(N'2020-02-12' AS Date), N'Weekday'),
(CAST(N'2020-02-13' AS Date), N'Weekday'),
(CAST(N'2020-02-14' AS Date), N'HOLIDAY'),
(CAST(N'2020-02-14' AS Date), N'Weekend'),
(CAST(N'2020-02-14' AS Date), N'Weekend')

INSERT [work_record] ([date_worked], [employee])
VALUES (CAST(N'2020-02-10' AS Date), N'john'),
(CAST(N'2020-02-10' AS Date), N'jimmy'),
(CAST(N'2020-02-10' AS Date), N'mary '),
(CAST(N'2020-02-11' AS Date), N'john '),
(CAST(N'2020-02-11' AS Date), N'jimmy'),
(CAST(N'2020-02-11' AS Date), N'mary '),
(CAST(N'2020-02-12' AS Date), N'john '),
(CAST(N'2020-02-12' AS Date), N'mary '),
(CAST(N'2020-02-13' AS Date), N'jimmy'),
(CAST(N'2020-02-13' AS Date), N'mary '),
(CAST(N'2020-02-14' AS Date), N'jimmy')


You could create a hidden timestamp column in DB2 on the replicated tables. When you run the replication job you can pull just the data that has changed since the last replication.  This would lower the quantity of data sent.

For instance, run the SSIS package every minute pulling just the last minutes worth of data.

Or, instead of a timestamp field, you could use an auto incrementing int and keep a small table on the replicated instance that records the last int value replicated. The replication process would grab any new records, and update the small table's value to reflect the last value replicated.


In Postgresql 12 how to I make the primary server a slave after a deliberate failover

Sounds about right, but I would keep the configuration identical on both servers, so that you don't have to change anything:

recovery settings like restore_command are ignored on the primary

you can set archive_mode and archive_command on the standby, since it doesn't archive WAL anyway (unless you set archive_mode = always)

Before you remove the data directory and re-initialize the standby with pg_basebackup, you could try to run pg_rewind, which does the same, but faster.


Share mongo database between two OS on dual boot

In theory, YES... If your mongod dbpath is at partition what both sides can read and write. So, you cannot use linux partitions (ext2,ext3,ext4,...) because your windows don't know how to handle. It's better not to use ntfs at windows side, because linux don't always handle ntfs right. So, solution is use older vfat32 partition type. Both sides can handle that well.


Oracle 11g bug ? not returning the record until I triggered index to invisible and then to visible

The data returned should be always the same independent of the access paths used. It never depends. The result set is defined by the text of the sql statement. Hints are comments and do not influence what data is retrieved. Indexes are auxiliary data structures that facilitate data retrieval but have no influence on what data is returned.
So this is a bug and you should contact Oracle support.


JDBC connection error for SQL Server Database

It is unlikely that a named instance is running on port 1433, unless you explicitly set it as such. I also suspect that SSMS is using shared memory (if local) or named pipes (if remote), unlike JDBC which is using TCP/IP (and I don't know how to set JDBC to use a different protocol).

From this documentation, it seems your connection string should be:

jdbc:sqlserver://MS8AT09;instanceName=AT09;...

With no port designation. You could also try using the IP address instead of the name, in case name resolution is hampered for some reason.

Please check what port the instance is running on (look at SQL Server Configuration Manager)...

...and confirm your connection string does not hard-code a port that is different (JDBC might ). Do not hard-code the dynamic port, because it may not be the port used next time SQL Server starts. From Microsoft's documentation:

  We recommend that the port number always be specified, as this is more secure than using sqlbrowser.

So, you may want to set your named instance to run on a specific port number, instead of using a dynamic port (the default), and then hard-coding that port number in the JDBC connection string. You can use the one that the instance was using dynamically, since you know it isn't in use by anything else. Also see KB #823938. So your connection string would become (assuming you used port 56737):

jdbc:sqlserver://192.168.0.65;instanceName=AT09;port=56737;...


InnoDB
InnoDB has to lock up because you do not have it configured properly.
You have to realize the framework behind the InnoDB
Here is a Pictorial Representation of InnoDB from Percona CTO Vadim Tkachenko

Looking at your my.cnf, there are many settings you do not have enabled or is underconfigured

innodb_buffer_pool_size : 400M is your setting right now. If the DB Server is dedicated to just the database, you need to scale this up to 60-75% of the installed RAM.
innodb_log_file_size : For MySQL 5.6 it's 48M. Before MySQL 5.6, it's 5M.
innodb_log_buffer_size : This is 8M by default. A larger log buffer makes for reduced I/O
innodb_read_io_threads and innodb_write_io_threads are both 4 by default (Percona Server's default is 8). Increase this for more CPU engagement (See my old post Possible to make MySQL use more than one core?)

The list can go on. The main point is that you cannot leave InnoDB in its default settings and expect good performance. You must configure InnoDB properly. Left unconfigured, oldver version of MySQL run better than newer versions (As an example, see my old post Why mysql 5.5 slower than 5.1 (linux,using mysqlslap))
You can read many posts in the DBA StackExchange from me (just another MySQL DBA) and a whole host of others MySQL and Percona Experts.
MyISAM
As regards MyISAM, you have key_buffer_size=60M. That's probably OK if just one Wordpress site is all MyISAM. If you really want to know how to set up caching for InnoDB and MyISAM, please read my old post What are the main differences between InnoDB and MyISAM?


When dumping a schema with the mentioned command, as postgres user:

pg_dump -f file.sql -F plain --inserts -n schemaname database

it produces a file whose basic structure looks like:

-- various SET commands
CREATE SCHEMA schemaname;

ALTER SCHEMA schemaname OWNER TO ownername;

SET search_path = schemaname, pg_catalog;

CREATE TABLE tablename(...) ...

ALTER TABLE schemaname.tablename OWNER TO ownername;

COPY tablename (columns...) FROM stdin;
data...
\.

The fact that search_path is set to the newly created schema implies that the CREATE TABLE and COPY statements that follow will happen in that schema and not in the public schema.

Or SET search_path would have to fail, which shouldn't happen even if the creation of the schema just above in the script failed. If schemaname does not exist, the next schema tried would be pg_catalog and CREATE TABLE would refuse to write into it, and after that there's no next schema in the search path, and in particular not public.

It's hard to imagine what mishappening could cause the above script to end up creating and populating anything in the public schema.


Can I limit access to postgresql 8.4 databases via ODBC on the server side?

ODBC is a client tool and does not affect the way that Postgresql verifies access.   I don't know if its any different for a postgresql server on windows, but in linux, there is a file called pg_hba.conf. This file is usually located in the data directory of the server install.  In Linux, you typically find it in /var/lib/pgsql/data/.

This file allows you to define which ip addresses can access your postgresql server and how they may access it.  Types of access include trust (no password), md5 (password), etc.

So for example, if your access users are on say 192.168.0.10 and you want them to use a password you would add this entry.

# TYPE  DATABASE    USER        IP-ADDRESS    IP-MASK             METHOD
host    all         access_user         192.168.0.10    255.255.255.255     md5

In postgresql you would then have to create the user and give him the ability to login.

1. psql -d nameofdatabase
2. CREATE ROLE access_user WITH LOGIN PASSWORD 'jw8s0F4';
3. GRANT SELECT ON nameoftable TO access_user;

Good luck


What to do when you need a cursor and you have a columnstore clustered index

This answer is written from the point of view of SQL Server 2016, but I believe that most of the important details are the same.

Updates are extremely expensive from a transaction logging point of view. They are implemented behind the scenes as a delete + insert. The delete has a fixed transaction log cost per row. If I remember correctly it's about 200 MB per 1 million rows deleted. The insert can only go to the delta store, so the data is written as heaps. These heaps are uncompressed in 2016 and page-compressed in 2014.

It's a best practice to do delete + inserts instead of updates against columnstore indexes. If you're truly processing most of the rows in the table you could just insert all of the new data into a new copy and switch the tables when you're done. Nearly all of the inserted rows will bypass the delta store and be written to compressed rowgroups instead. Writing data to compressed rowgroups requires significantly fewer transaction writes compared to writing to the delta store.

In SQL Server 2016 you can significantly improve performance with such an approach. The same is true for SQL Server 2014 according to Niko's article.


Replace part of a string in MYSQL, when only the substring's starting characters, ending characters & a unique string in the middle is known?

Test:

UPDATE source_table
SET field = CONCAT( SUBSTRING_INDEX(field, @start, 1),
                    @replacement,
                    SUBSTRING_INDEX(field, @end, -1)
                  )
WHERE LOCATE(@middle, SUBSTRING_INDEX(SUBSTRING_INDEX(field, @start, -1), @end, 1))


How to fetch those records who appears in many records in many to many relationship in PostgreSQL?

Here's my sample query for your question, the query could be slow depending on your table size

SELECT t.uid, u.name, t.groups_user_has_access_to
FROM (
  -- Each of GID, finding its big group (PERSON, PAY) --> called: group_type.
  -- Using distinct to remove duplicated element in group_type
  SELECT ug.uid,
    array_agg (distinct
      case when g.name like 'Person%' then 'Person'
           when g.name like 'Pay%' then 'Pay' end) as group_type,
      array_agg(g.name) as groups_user_has_access_to
  FROM user_groups ug
  JOIN groups g on ug.gid = g.id
  GROUP BY ug.uid
)t
JOIN users u on t.uid = u.id
WHERE array_length(t.group_type, 1) > 1 -- Reject any UID which belong to 1 group.
ORDER BY t.uid;

I would suggest you to read the array functions in the query above.


You can get the 2-way Sync feature using Couchbase Mobile (client-side) solution and on the backend using Couchbase Server

Can check out an example on this blog post


How to find the number of rows changed by an UPDATE statement in Oracle?

If you issue an update statement on your table that effectively updates the data to the same value that is already there, then the database does update the row, so the SQL%ROWCOUNT is accurate even though the new value is the same as the old value.

You can use a flashback versions query to see that database has indeed updated the record:

select t.*
     , VERSIONS_OPERATION
     , VERSIONS_STARTTIME
     , VERSIONS_ENDTIME
     , VERSIONS_XID
  from t
  versions between timestamp systimestamp - interval '10' minute and systimestamp
 where id = 0;

UPDATE t SET name = 'John' WHERE id = 0;
commit;

select t.*
     , VERSIONS_OPERATION
     , VERSIONS_STARTTIME
     , VERSIONS_ENDTIME
     , VERSIONS_XID
  from t
  versions between timestamp systimestamp - interval '10' minute and systimestamp
 where id = 0;

In the first query above there will likely be only one row returned for ID=0.  Then after issuing and committing an update against ID=0 the second time the flashback versions query is run you should get back two records, one for the original record where the VERSIONS_ENDTIME column has been filled in and one for the new version of the record showing that the operation against it was an Update and that the record has a VERSIONS_STARTTIME equal to the end time of the original record.

The only way to prevent records from being updated with the same values is either with an appropriate predicate in the where clause, or in an on update for each row trigger that rejects the change by raising an error.


What steps do I need to take to move my SQL instance to a different drive on the same server but with the same drive letter?

There are the usual precautions such as ensuring up to the minute backups before you start.

Another option you could consider though is to create Mount Points for the new drive so that you don't need to worry about changing drive letters afterwards - the fewer moving parts the better.


why am I getting a warning of Depreciation of INT(11) in latest version of MySQL?

Just because it is deprecated does not mean it won't work.
Just don't explicitly specify length anymore when creating new tables.
Let MySQL figure out how the length will be handled.
Personally, I never used the length operator in my years as a DBA. Besides, the length operator (11) simply stands for the display on a signed INT.
What is the range of a signed INT ??? -2147483648 to 2147483647. Please note that -2147483648 is 11 characters. That's why 11 will appear in SHOW CREATE TABLE if you created the table without a length operator.
If you are concerned with how MySQL Workbench handled it, you may need to consider:

Upgrading to the latest version of MySQL Workbench
Switching to another up-to-date MySQL GUI

For now, the best thing is to run SHOW CREATE TABLE and ALTER TABLE commands without length operators. Let MySQL deprecation happen and don't look back.


Removing foreign key when it is not named

You can use cascade constraints to also drop any constraint related to the column:

alter table student drop column course_id cascade constraints;

More details regarding the drop column clause can be found in the manual.

If you want to find out how the constraint was actually named (e.g. if you only want to drop the constraint, not the column), you can query the system catalogs for this:

select constraint_name
from user_cons_columns
where table_name = 'STUDENT'
and column_name = 'COURSE_ID';

That will return something like SYS_C0052446 (it will be a different value on your system). And that name then can be used for the drop constraint:

alter table student drop constraint SYS_C0052446;

More details about the system catalog can be found in the manual


Compare and auto fix conflicts between two databases T-sql

Columns in old database but not in new , or default value, nullable and data type changed:

SELECT t1.*,
       t2.*
FROM   [OldDb].[INFORMATION_SCHEMA].[COLUMNS] t1
       LEFT JOIN [NewDb].[INFORMATION_SCHEMA].[COLUMNS] t2
         ON t1.COLUMN_NAME = t2.COLUMN_NAME
WHERE  t2.COLUMN_NAME IS NULL
        OR ( t1.TABLE_NAME = t2.TABLE_NAME
             AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA
             AND t1.COLUMN_NAME = t2.COLUMN_NAME
             AND ( t1.DATA_TYPE != t2.DATA_TYPE
                    OR t1.IS_NULLABLE != t2.IS_NULLABLE
                    OR t1.CHARACTER_MAXIMUM_LENGTH != t2.CHARACTER_MAXIMUM_LENGTH
                    OR t1.COLUMN_DEFAULT != t2.COLUMN_DEFAULT ) )
ORDER  BY t1.COLUMN_NAME

Columns in new database but not in old :

You can change [OldDb] place with [NewDb] in query

I wrote this question and answer because I've needed this and I've spent so much of time on this.

So I hope everyone can find and use this easily :).
I'm looking forward for opinions for make this better.


SQL identity column with unique code column

If your column2 is immutable then it can serve as primary key. Only if it was to hold long values then you could consider to create a separate primary key field to save space in the tables that have a foreign key constraint with this table.


Issue using COPY command with AWS Redshift

Perhaps the JSON you have is not quite correct. When I paste your JSON into jsonlint.com I get the following:

Parse error on line 7:
...        "username": “user23"} ] }
-----------------------^
Expecting 'STRING', 'NUMBER', 'NULL', 'TRUE', 'FALSE', '{', '['

The AWS Redshift documentation on COPY SSH parameters has the following

  The quote characters must be simple quotation marks (0x22), not slanted or "smart" quotation marks.

So double check you quote marks around the username you've provided, and if they are how you've provided them above, change it from to “user23" >> "user23" (note the first quote mark is different).

The first quote mark in your code is unicode : U+201C and not U+0022


Just my 5 cents contribution to the topic (not a complete answer)

I had a similar issue and I've caught the blocking query with sp_whoisactive - so it is

create procedure sys.sp_columns_100
(
    @table_name         nvarchar(384),
    @table_owner        nvarchar(384) = null,
    @table_qualifier    sysname = null,
    @column_name        nvarchar(384) = null,
    @NameScope          int = 0,
    @ODBCVer            int = 2,
    @fUsePattern        bit = 1 -- To allow users to explicitly disable all pattern matching.
)
...

and as @Cody_Konior suggested I do have "many active ODBC connections" from legacy SSIS packages in my ETL system, so...

perhaps it's time to finally change them to OLEDB or ADO.NET versions as I wanted for some time already but posponed


Data Warehouse Modeling: Sales Order Charges

Can someone guide me as to which of these options is the most apt design?

Funilly enough, that question does have an answer: No.  No one can.

Dimensional modeling is always about the questions you want to answer, and typically requires eliminating some information in the transformations.  There's lots of modeling decisions you could make, but the only way to choose between them is comparing the ease and performance of answering questions about your business.

Note that you didn't consider simply using Periodic Snapshot fact, where you would calculate the interesting measures at a grain of something like (day, org, product).  This will give you a very simple dimensional model that probably won't answer every last question, but you can combine that with storing the full transaction details for scenarios that need it.


There is no built in functionality.

The stored procedure route will work, but you might also want to look into an ETL package (like SSIS) to do this as it may be easier to maintain.

You could also look into partitioning on CreateDate for all your tables  in order to efficiently drop older data, but that would still require some custom code to rotate the partitions. They could, however, use a common partition scheme and function if the DDL on the CreateDate is the same for each.

If you were to upgrade, you could also look at using Temporal Tables or Stretch Database from SQL 2016, though those aren't out of the box auto-archive features necessarily.


PowerShell command 'New-Item' fails when invoked through SQL Server Agent job

When accessing UNC paths, or any 'location' that isn't a local drive (i.e. registry), from a PowerShell job step, you need prefix the path with Microsoft.PowerShell.Core\FileSystem::. This tells SQLPS which provider to use, which isn't required in normal PowerShell but is required in the SQL Server implementation.

Alternatively, you can change directory beforehand to a local drive (cd C:) and it should then work without prefixing the provider name, but you may want to stay in the default SQLSERVER:\SQL\SERVERNAME\INSTANCENAME path depending on what your script requires.

More info:

https://dbatools.io/agent/

https://social.technet.microsoft.com/Forums/windowsserver/en-US/ec7f3ae8-d196-459e-b9dc-e6ed0df93004/running-powershell-from-sql-server-using-unc-paths


Error with FUNCTION ( SQL Server 2008 ) - ''Cannot find either column...'

You can't use dynamic SQL inside a function (well, aside from this awful hack). The main reason is that functions can only return data and can't have side effects. There is no way for SQL Server to prevent you from passing an insert or an update to sp_executesql (as an aside, that looks like a SQL injection attack vector no matter how you end up implementing it).

It's unclear to me why you're doing this as a multi-statement TVF in the first place. You're returning one column and one row. Why not use a stored procedure with an output parameter?


Performance impact of NOT using a foreign key (many dedicated 1:many key-key tables vs non-fk generic key-key table)

It looks to me that you are considering the EAV (Entity Attribute Value) model, i.e. wedging every object into a single table, rather than having different tables for different objects. EAV is generally considered an antipattern.

It can be known as an Open and/or flexible Schema. It is also known (sarcastically) as the OTLT (One True Lookup Table), (even more sarcastically) as MUCK (Massively Unified Code Key - Joe Celko) and perhaps best of all (I only just found this) as DEMONS (Diabolically Enticing Method Of iNformation Storage - adapted from Mike Smithers).

It is interesting to note that Celko (who was a member of the ANSI/ISO SQL committee which determines/ed the SQL standard) said (see reference) about the programmer who had written such a system that the questioner should "Find the moron that did this to you and kill him".

See the Smithers' post (and and Celko's writings here and here) about the difficulties of writing queries against EAV schemas. It's no coincidence that Celko titles his first article "Avoiding the EAV of Destruction". Take a look here for an absolutely hilarious visual analogy of the EAV system from Bill Karwin - a highly respected SQL guru and author of a book "SQL Antipatterns".

You mention "having defined foreign key relationships in smaller tables, but potentially hundreds of them". Modern RDBMSs can cope with hundreds of tables and FK relationships between them and unless you have VERY strong reasons (with proven testing behind your argument), then stick with traditional relational principles.

To directly answer your question, in all likelihood, the complexity of the queries that you will need to use to obtain data results from an EAV system will have FAR more of a negative performance impact than a system with many tables with clear, properly defined FK relationships using INT(11) foreign keys or even UUIDs.

Another point about performance - use of an EAV system will force you to use inappropriate datatypes and this will seriously interfere with your RDBMSs query optimiser's ability to (err...) optimise the queries. Under these EAV systems, many values are (again inappropriately) stored as strings and converted on-the-fly, which is both time-consuming and, again, confusing for the optimiser - not recommended.

From my accepted answer here and for completeness and honesty, just one further remark. There is only one significant system out there that makes use of the EAV model - and that's Magento (1, 2). Its principal niche is the fashion industry where the EAV model may be suitable for sparse tables (fashion items tend to be available in a myriad of colours, syles, sizes...). It is popular (1, 2), but then so is MySQL which is inferior in many ways to PostgreSQL, Firebird and (multi-user capability aside) SQLite.

I also noticed this in the comments

  but I never rely entirely on the DB's RI, I generally do it at the
  application layer falling back on RI as a backup. I've considered
  using a maint task that runs occasionally to remove orphaned records
  to shore that up as well.

This is nothing short of madness.

The RI (Referential Integrity) capabilities of MySQL (and other RDBMSs) have been tested by literally MILLIONS of users. If you set up a proper PK-FK relationship between two of your tables, then the chances of this failing are virtually NIL! You'd be far better off using your time and effort to focus on other areas of your application than ensuring proper RI - unless you've committed the unpardonable sin of implementing RI in the app rather than using the capabilities of the RDBMS. See Jonathan Lewis' disaster no.2 in my answer here. Lewis wrote this book, so he knows a thing or two about databases.


Does hard-coding schema names in procedure calls for Oracle jobs have any performance impact?


I would recommend Building a Data Warehouse With Examples in SQL Server by Vincent Rainardi, as this covers SQL Server specifics.  The Data Warehouse Toolkit is also an excellent and practical guide, but isn't platform specific.


Does the Binary Data column shown in SQL Profiler contain useful information?

I know the BinaryData column is useful for a few event classes.  However, if the event class describes the column as "Binary value dependent on the event class captured in the trace" I haven't found it to be particularly useful.  I'm not aware of any meaningful use for the RPC:Starting and RPC:Completed events and I wasn't able to find any.

There are a few event classes where they column is useful though.  For example,  the degree of parallelism event class uses the BinaryData column to show how a plan is being executed (http://technet.microsoft.com/en-us/library/ms187943(v=sql.105).aspx).

The CursorImplicitConversion class uses it to tell you the resulting cursor type (http://technet.microsoft.com/en-us/library/ms180764(v=sql.105).aspx).

The ShowPlan event classes use the BinaryData column to store the XML data.  There are a few others but on the whole it's not very useful.


How to insert values into a highly normalized database without excessive duplicate checking?

A little thought...

Formally you have something similar to this simplified structure:

CREATE TABLE slave1 (id PK, value UNIQUE);
CREATE TABLE slave2 (id PK, value UNIQUE);
CREATE TABLE main (id PK, id_slave1 FK, id_slave2 FK);

When you need to insert 2 records (id_1, val_1_1, val_1_2) and (id_2, val_2_1, val_2_2), you execute:

CREATE TEMPORARY TABLE temp (val_slave1, val_slave2) [ENGINE=Memory];

INSERT INTO temp (val_slave1, val_slave2)
VALUES (val_1_1, val_1_2),
       (val_2_1, val_2_2);

INSERT IGNORE INTO slave1 (value)
SELECT DISTINCT val_slave1
FROM temp;

INSERT IGNORE INTO slave2 (value)
SELECT DISTINCT val_slave2
FROM temp;

INSERT INTO main (id_slave1, id_slave2)
SELECT slave1.id, slave2.id
FROM temp
JOIN slave1 ON temp.val_slave1 = slave1.value
JOIN slave2 ON temp.val_slave2 = slave2.value;

The engine of temp may be Memory when the amount of inserted values is low, and InnoDB or something else if inserted data array is huge.

INSERT IGNORE works fast enough over UNIQUE indexed field. It guarantees that no duplicates in slave tables, and that the values which must be inserted will exist in slaves while insert into the main table.

And final query must be fast too - especially when temporary table fields are indexed too.

If you need to insert one record only, then you can, of course, do not use table temp... but I think uniformity is more safe then a little simplification.

Of course, this may be optimized. For example, all inserts may be joined into one stored procedure, and you do not need in "60 database hits", one CALL is enough. Finally you must execute only 3 queries independent by the records count for to insert. And only one of them (inserting into temptable) may be huge (or even it can be divided to a lot of inserts).


What do you need to take into account when migrating SQL Server to another domain?
The steps below presume:

IP address will also change
SQL Server is NOT clustered

Steps:
A.  Backup:

BEFORE: backup the databases off-machine

B.  Services:

BEFORE: depending on the nature of the change/move, you may want to set service start to Manual for all SQL Service
AFTER: Once things or up and running properly, return service start to its original setting

C.  SA account:

BEFORE: If all administrator accounts are domain accounts or groups, temporarily enable the 'sa' account with a strong password
AFTER moving: once the domain-based accounts are setup in the new domain, 'sa' can be disabled again

D.  Service Windows account:

BEFORE moving: for each SQL-Server-related Windows service, change the service to use a LOCAL windows account or one of the built-in accounts
AFTER moving: grant the necessary privileges to the service new domain accounts.  When special permissions are not needed, the SQL Service Configuration Manager can be used to change the service account

E.  Windows domain accounts used to login to SQL Server

Re-create the needed accounts or use corresponding accounts in the new domain.
BEFORE moving, script out permissions for OLD domain accounts, including SQL permissions, Windows folder and file permissions, Windows permissions, etc.
AFTER moving, apply these scripts to the corresponding NEW domain accounts so they will have the same permissions

F.  IP Address: SQL Server (unless clustered) will use the new IP address

AFTER: Client applications that reference the service by IP address will need to be configured with the new IP address.

G.  Firewall:

AFTER: OLD firewall openings that are no longer used will need to be closed, NEW firewall openings may need to be created for SQL Server, OLAP services, SSRS between servers and clients

H.  DNS entries:

AFTER: verify DNS has correctly updated
AFTER: Clients and services that reference by DNS name, may need to be restarted AND/OR their host systems may need their DNS cache flushed.  For windows workstations, this can be done with "ipconfig /flushdns"

I.  Service Principle Names (SPNs).  Some standalone (and all clustered) instances use SPNs.

AFTER: The OLD SPN must be dropped and a NEW SPN must be created.  Although it's not recommended to use a SQL Server service account to manage (its own) SPNs, if this is the case, the NEW domain service account will need to be granted WriteServicePrincipalName" privilege

J.  Client Network Utility Alias.

AFTER: Update any clients that use these will need to updated

K.  Client application and service connection configuration:

AFTER: Data Source Names (DSNs), connection strings, config files, Oracle TNS names for connections - will need to be udpated and applications and services may need to be restarted

L.  Internal machine name.

AFTER: If the machine name is also changing, SQL Server's internal machine name entry may need to be udpated
sp_dropserver 'MyOldMachineName'
go
sp_addserver 'MyNewMachineName','local'
go

M.  Merge Replication - If merge replication is in use, it will also need to be reconfigured.

BEFORE: ensure all replicas are up-to-date
AFTER: re-configure merge replation

Attributions - some information added from these sources:
https://serverfault.com/questions/49681/change-ad-domain-membership-of-a-server-2008-running-ms-sql-08
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/f3e8ff83-8fcd-4335-87fe-ea5641ae6b88


Where do Database Snapshots store the information what pages have been copied to the snapshot files?

From the Microsoft SQL Server 2012 Internals book:

The snapshot files contain only the data that has changed from the source.  For every file, SQL Server creates a bitmap that is kept in cache, with a bit for each page of the file, indicating whether that page has been copied to the snapshot.  Every time a page in the source is updated, SQL Server checks the bitmap for the file to see if the page has already been copied and if hasn't it is copied at that time.  This operation is called a copy-on-write operation.
The bitmap is stored in cache, not the file itself, so it is always readily available.  When SQL Server shuts down or the database is closed, the bitmaps are lost and need to be reconstructed at database startup.

When performing reads, SQL Server checks the snapshot bitmap to determine where to read from.


first row and last row fields value from where data changed

SELECT job_title,
       charge_center,
       MIN(id) start_id,
       MAX(id) end_id
FROM source_table
GROUP BY job_title, charge_center;

If there exists 2 separate blocks with the same (job_title, charge_center) pair values the query will give wrong result.


Calculating values from three related tables, without using join or union

It seems you are being asked to write the query using correlated subqueries, for example:

SELECT
    IOB.ItemID,
    IOB.Descript,
    IOB.Quantity +
    (
        -- Total received for this item
        SELECT ISNULL(SUM(IR.Quantity), 0)
        FROM dbo.ItemsReceived AS IR
        WHERE IR.ItemID = IOB.ItemID
    )
    -
    (
        -- Total delivered for this item
        SELECT TotalDelivered = ISNULL(SUM(ID.Quantity), 0)
        FROM dbo.ItemsDelivered AS ID
        WHERE ID.ItemID = IOB.ItemID
    )
FROM dbo.ItemsOpeningBalance AS IOB;

SQLFiddle Demo

If you're using SQL Server 2005 or later, you could also use APPLY (a lateral or correlated join) but I suspect this would be viewed as not in the spirit of the exercise. The following actually requires SQL Server 2008 or later, because it (gratuitously) uses GROUP BY ().

SELECT
    IOB.ItemID,
    IOB.Descript,
    QuantityAvailable =
        IOB.Quantity +
        ISNULL(ItemsReceived.Quantity, 0) -
        ISNULL(ItemsDelivered.Quantity, 0)
FROM dbo.ItemsOpeningBalance AS IOB
OUTER APPLY
(
    SELECT SUM(IR.Quantity)
    FROM dbo.ItemsReceived AS IR
    WHERE IR.ItemID = IOB.ItemID
    GROUP BY ()
) AS ItemsReceived (Quantity)
OUTER APPLY
(
    SELECT SUM(ID.Quantity)
    FROM dbo.ItemsDelivered AS ID
    WHERE ID.ItemID = IOB.ItemID
    GROUP BY ()
) AS ItemsDelivered (Quantity);

SQLFiddle Demo


How can SUSER_SNAME return the localized user name?

Since you already have the SID — a value that does not change based on culture — you might as well use it:

DECLARE @Login sysname,
        @DelimitedLogin NVARCHAR(258),
        @SID VARBINARY(85);

SET @SID = 0x010100000000000514000000;

SELECT @Login = sp.[name]
FROM   sys.server_principals sp
WHERE  sp.[sid] = @SID;

SET @DelimitedLogin =  QUOTENAME(@Login);

IF (NOT EXISTS(
        SELECT *
        FROM   sys.database_principals dp
        WHERE  dp.[sid] = @SID
              )
    AND @Login IS NOT NULL
   )
BEGIN
  EXEC(N'
CREATE USER ' + @DelimitedLogin + N' FOR LOGIN ' + @DelimitedLogin
  + N' WITH DEFAULT_SCHEMA = [dbo];
ALTER ROLE [db_owner] ADD MEMBER ' + @DelimitedLogin + N';
');
END;
GO


The subquery is uncorrelated, so you can simply make it a derived table (moving it to the FORM clause) or make it a CTE:

-- derived table
SELECT il.*, q.qty_in
FROM item_ledger AS il
  CROSS JOIN
    (SELECT SUM(qty) AS qty_in
     FROM item_ledger
     WHERE from_item_ledger_id = '5') AS q
WHERE il.to_id = '7'
  AND q.qty_in < il.intransit_qty ;

-- common table expression
WITH q AS
    (SELECT SUM(qty) AS qty_in
     FROM item_ledger
     WHERE from_item_ledger_id = '5')
SELECT il.*, q.qty_in
FROM item_ledger AS il
  CROSS JOIN q
WHERE il.to_id = '7'
  AND q.qty_in < il.intransit_qty ;

You could also use an inline subquery or a LATERAL join (available in 9.3+ versions) but those solutions would be more useful if the subquery were correlated.


The primary key is a way to distinguish one row in a single table from all other rows in that same table. It is not a way to distinguish one row in the context of its associated rows from other tables.

Sometimes a table's primary key consists of a single column. A person's user_id would be an example.

Sometimes it is made up of several columns. A location is both latitude and longitude. This is known as a compound key. Sometimes one or more of those columns may also be a foreign key. This is termed a weak entity type.

To take your example - could a single row in the Orders table be distinguished from all other rows by the Order Number alone? Typically, yes. The order number is unique across the whole system. So given order number 8765 we know that's for customer A. This makes Order a strong entity type.

How about the OrderLine table? Given a single order line number, say "1", could we unambiguously find which Order that relates to? Typically no, because order line numbers start again for each Order. OrderLine is therefore a weak entity because its primary key (order number, order line number) requires the primary key from another related table, viz. Order.

So according to the business rules it makes no sense for an Order to exist without the Customer but according to the database rules this is OK. An OrderLine cannot exist without the Order under either set of rules.


Fastest way to move MongoDB to a new server

Final solution:
We went with option #2 - turning off MongoDB and manually copying the database files over to the new server.  It worked like a charm.  For future database migrations and changes, I will probably be making a replica set as suggested by @SqlWorldWide and @Markus.  That looks like it would definitely the best solution for someone who has time to properly plan a migration.


GiN(Generalized Inverted Index) is inverted index, a structure that has only one single index entry per a key and store the mapping information(posting list) of all key row to have the same value in the index entry. In postgreSQL, the key is stored in the index entry and mapping information for the key is stored in the posting tree. To search the index entry and posting tree is using a B-Tree. Therefore, GIN is useful when an index must map many values to on row, such as indexing array, documents.

I suppose to that before creating GIN index , you are doing some mistake. as you see here postgresql GIN pg_trgm default operator class

To Creates a GIN (Generalized Inverted Index) the syntax should be like that

CREATE INDEX name ON table USING gin(column);

Creates a GIN (Generalized Inverted Index)-based index. The column must be of tsvector type.

Default operator class jsonb_ops supports existence operators( ?, ?&, ?| ) and containment operator( @> ). And jsonb_path_ops supports containment operator only. Therefore GIN index is only possible to search for that have a particular key or key-values.

When we use ->> operator of JSONB, PostgreSQL can use B-tree or Hash index for processing the operations. ->> oerator returns the value of the specified attribute in text format. PostgreSQL can use indexes for the text results as compare operands. GIN index can be used by GIN jsonb operator class.

To see the json operator Here

For further ref Here and Here


A tool to convert PostgreSQL database dumps between versions?

Are there tools or options to create a pg_dump file that is compatible to earlier versions of PostgreSQL

No there are no options or tools to create a backwards compatible pg_dump files.

Depending on how far back the version the data needs to be ported to the dump file may work there is no guarantee

A work around this limitation  would be run pg_dump using the -P --plain and --schema-only  modify the  resulting SQL file to make it compatible.

Restore the file.

Then run pg_dump again using -P --plain and -a --data-only options then restore the file.


Migrate Availability Groups from SQL Server 2014 to 2016

Yes it is possible and you do not need to rebuild from scratch.

This is well documented here and you can follow the steps and best practices from this document.

Upgrading Always On Availability Group Replica Instances

To check all versions that qualify for rolling upgrade see this document.

Supported Version and Edition Upgrades

This question was answered before here.


How do I deal with the inevitable scenario when a table's bigserial runs out of ids but many of the old records are deleted?

If you consume 10000 sequence values per second, you'll have consumed 10000*3600*24*365 values per year, that is 3,1536E11. At that rate, it takes a couple of million years to exhaust a bignt sequence.

So you shouldn't lose any sleep over it.


Memory utilization per database - SQL Server

SELECT db = DB_NAME(t.dbid), plan_cache_kb = SUM(size_in_bytes/1024)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.dbid < 32767
GROUP BY t.dbid
ORDER BY plan_cache_kb DESC;

This tip I wrote on mssqltips.com might be useful as well if you want to dig deeper into buffer usage.


SQL Server Logon Trigger causing problems with the Distributed Transaction Coordinator

If you dig through the discussion in the articles comments, you'll find a link to KB951188 which details a bug in SP2 that was fixed in CU7. Check your version level to see if it applies.


Consequences of increase Oplog to large size

Nothing negative, if you don't count the usage of disk space.  Actually you need just make your Primary nodes OpLog bigger and then wait at least that 6 hours to fill it enough. But of course it's good to have same size OpLog on every RS node.
I try to keep OpLog size at least 48h. That gives you "time" to solve problems, if those rise up.


Yes, you need to bother about datatype, always know your datatypes and use the proper ones. The database can implicitly convert compatible datatypes, so it is not always mandatory to specify and convert to the correct datatype, but it is a really bad practice to rely on implicit conversion.

The below is a typical scenario I saw countless times:

select column1 from table1 where indexed_number_column = '12345';

Here you compare a number to a string. The database will convert this query to:

select column1 from table1 where indexed_number_column = to_number('12345');

or

select column1 from table1 where to_char(indexed_number_column) = '12345';

The second one can not use the existing index on the column indexed_number_column, so it will perform much slower. For the second one, you would need a function-based index on to_char(indexed_number_column).


The LOAD DATA INFILE statement was not always replicated correctly to a slave running MySQL 5.1.42 or earlier from a master running MySQL 4.0 or earlier. When using statement-based replication, the LOAD DATA INFILE statement CONCURRENT option was not replicated. This issue was fixed in MySQL 5.1.43. This issue does not have any impact on CONCURRENT option handling when using row-based replication in MySQL 5.1 or later (See this bug report http://bugs.mysql.com/bug.php?id=34628)

Also In MySQL 5.1.52 and later, LOAD DATA INFILE is considered unsafe. It causes a warning when using statement-based logging format, and is logged using row-based format when using mixed-format logging.

http://dev.mysql.com/doc/refman/5.1/en/replication-rbr-safe-unsafe.html


Is it possible to get server roles and database roles in a same query/script?

Add an INSERT...SELECT query to get the server-level role memberships. The example below also uses QUOTENAME to properly handle the concatenated literals and identifiers.

DECLARE @DatabaseName SYSNAME,
        @sql VARCHAR(1000);
DECLARE @ResultTable TABLE(DatabaseName VARCHAR(100), ServerName VARCHAR(100), Name VARCHAR(100), TypeDesc Varchar(100), DatabaseRole VARCHAR(100) );
DECLARE DatabaseCursor CURSOR
     FOR SELECT Name FROM sys.databases;
OPEN databaseCursor;
FETCH NEXT FROM databaseCursor INTO @DatabaseName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @sql ='SELECT ' + QUOTENAME(@DatabaseName, '''') + ' AS [Database],
                      ' + QUOTENAME(@@SERVERNAME,'''') +  'AS [Server Name],
                      logins.name Name,
                      logins.type_desc TypeDesc,
                      roles.name RoleName
               FROM '+QUOTENAME(@databaseName) +'.sys.database_role_members rm
               INNER JOIN '+QUOTENAME(@databaseName) +'.sys.database_principals roles ON rm.role_principal_id = roles.principal_id
               INNER JOIN '+QUOTENAME(@databaseName) +'.sys.database_principals logins ON rm.member_principal_id = logins.principal_id';
    INSERT INTO @ResultTable EXEC(@sql);
    FETCH NEXT FROM databaseCursor INTO @databaseName;
END;
CLOSE databaseCursor;
DEALLOCATE databaseCursor;

--get server roles
INSERT INTO @ResultTable
SELECT
      NULL
    , @@SERVERNAME
    , logins.name
    , logins.type_desc
    , roles.name AS RoleName
FROM master.sys.server_role_members AS rm
JOIN master.sys.server_principals AS roles ON rm.role_principal_id = roles.principal_id AND type_desc = N'SERVER_ROLE'
JOIN master.sys.server_principals AS logins ON rm.member_principal_id = logins.principal_id;

SELECT * FROM @ResultTable;


Grant select on all synonyms of schema to another schema in Oracle

Here the script to generate grant select on all the tables and synonyms.

select 'grant select on Admin.' || object_name || ' to User;'
from user_objects
where object_type in('TABLE','SYNONYM');

Then you have to create a script to run these grant statements at once or you can use PL/SQL as well. Type the following in the SQL prompt.

SQL> spool grant_statements.sql
SQL> set pagesize 0
SQL> set feedback off
SQL> set linesize 300
SQL> set trimout on
SQL> set trimspool on
SQL> select 'grant select on Admin.' || object_name || ' to User;' from user_objects where object_type in('TABLE','SYNONYM')
/
SQL> spool off

And you have got the script file you can run it.
OR
You can run the following PL/SQL block (Run as admin user).

BEGIN
  FOR s IN (SELECT *
              FROM user_objects where object_type in('TABLE','SYNONYM'))
  LOOP
    EXECUTE IMMEDIATE  'grant select on admin.' || s.object_name || ' to user';
  END LOOP;
END;


The Check Database Integrity Task provided in the maintenance plan issue DBCC CHECKDB WITH NO_INFOMSGS on the database selected. You can view its command by clicking the view-SQL in the task setup. If you doubt the generated SQL command, you can use SQL profiler to see its SQL command. If corruption was found, the agent job with this maintenance task will generate error and fail (with proper job step setup).

One thing to point out, running DBCC CHECKDB is equal as performing DBCC CHECKALLOC, DBCC CHECKTABLE, DBCC CHECKCATALOG and other validation. If you are running DBCC CHECKDB, you do not have to run them separately. Running them separately usually is to perform specific integrity check or need to spread out the integrity check to smaller task due to limited time to perform entire DBCC CHECKDB. More information can be found here on MSDN.


Error Code: 1133. Can't find any matching row in the user table

This is not a problem with MySQL installation or set-up.

Each account name consists of both a user and host name like 'user_name'@'host_name', even when the host name is not specified.  From the MySQL Reference Manual:

  MySQL account names consist of a user name and a host name. This enables creation of accounts for users with the same name who can connect from different hosts.

This also allows MySQL to grant different levels of permissions depending on which host they use to connect.

When updating grants for an account where MySQL doesn't recognize the host portion, it will return an error code 1133 unless if it has a password to identify this account.

Also, MySQL will allow an account name to be specified only by it's user name, but in this case it is treated as 'user_name'@'%'.


Is MySQL syntax GROUP BY ... ASC/DESC removed or not?

From the "changelogs".  This was unusually fast from 'deprecation' to 'removal'!
----- 2018-07-27 8.0.12 General Availability & 2018-07-27 5.7.23 General Availability -- -- -----

Explicit ASC or DESC qualifiers for GROUP BY clauses are now deprecated and will be removed in a future MySQL version.

----- 2018-10-22 8.0.13 General Availability -- -- Incompatible Change -----

The deprecated ASC or DESC qualifiers for GROUP BY clauses have been removed. Queries that previously relied on GROUP BY sorting may produce results that differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.
Queries and stored program definitions from MySQL 8.0.12 or lower that use ASC or DESC qualifiers for GROUP BY clauses should be amended. Otherwise, upgrading to MySQL 8.0.13 or higher may fail, as may replicating to MySQL 8.0.13 or higher slave servers.


How can I reliably determine if a given SQL Server instance has the "Lock Pages in Memory" right?

If xp_cmdshell is an option, here is a script making use of whoami:

DECLARE @LockPagesInMemory VARCHAR(255);
SET @LockPagesInMemory = 'UNKNOWN';
DECLARE @Res TABLE
(
    [output] NVARCHAR(255) NULL
);

IF (SELECT value_in_use
    FROM sys.configurations c
    WHERE c.name = 'xp_cmdshell'
    ) = 1
BEGIN
    INSERT INTO @Res
    EXEC xp_cmdshell 'WHOAMI /PRIV';

    IF EXISTS (SELECT *
        FROM @Res
        WHERE [output] LIKE 'SeLockMemoryPrivilege%'
        )
        SET @LockPagesInMemory = 'ENABLED';
    ELSE
        SET @LockPagesInMemory = 'DISABLED';
END

SELECT LockPagesInMemoryEnabled = @LockPagesInMemory;

whoami.exe has the ability to report all the rights held by the account executing whoami.  This, combined with the xp_cmdshell provides a reliable way to determine if the instance of SQL Server has the necessary right to lock pages in memory.

If xp_cmdshell is not enabled, this code returns UNKNOWN.  Assuming xp_cmdshell is enabled, and the right is enabled for the SQL Server account, it returns ENABLED, otherwise it returns DISABLED.