Backup set expire option - Backup SQL Server 2012

Answered here by @JamesAnderson

The "Backup set will Expire" option is used to indicate how long a backup should be prohibited from being over written. It will not clean up old backup files. You can use the "Maintenance Clean up Task" in your maintenance plan to clean up backups files older than a certain number of days.

What is the use of "Backup set will Expire:" option while creating Backup Database task in Maintenance plan


How pt-online-schema-change ensures consistency during data copy

pt-online-schema-change takes advantage of the consistency nature of relational databases at table level. Let's see what happens when we write to the original table, one of three cases:

The chunk has already been copied. No problem, the trigger will overwrite the values of the accessory table within the transaction
The chunk has not yet been copied. The trigger will probably fail, but the original modification will be done thanks to the IGNORE option on the INSERT/UPDATE/DELETE of the trigger. No problem, when the pt-osc process reaches to it, it will copy the newer values.
Your question. The chunk is being copied. There are 2 subcases, depending on which process starts first.

a) If the modification of the original table starts first, either the whole table (in the case of MyISAM) or the set of rows (in the case of InnoDB), will be locked for other modifications, so pt-osc will have to wait for the write to finish. You should avoid very time-consuming write operations while this tool is working, or relying on it to pause if the load is too high, but in the unfortunate case that you are doing in concurrency another large update, the session variable innodb_lock_wait_timout (configured by the parameter lock-wait-timeout, by default 1 second) for pt-osc should kick in and revert/abort the copy of that chunk. In that case, the tool will retry that chunk again --retries times (default, 3), or the whole process will abort, in theory gracefully. In most cases, once the lock disappears (because the concurrent write finishes), you will be essentially on case 2.

b) If pt-osc starts first, it will essentially lock again the whole table (in MyISAM) or those specific rows (in InnoDB) for writing (a READ LOCK), preventing concurrent writes. This will only be hold for, ideally, up to 0.5 seconds, as the chunk size has been calculated so that the chunk copy should not take more than that. Once it has finished, you are essentially on case 1.

So, yes, there is not 100% lock free tool, but no such a tool can exist if you want to guarantee consistency. Even the rename table process takes microseconds (after all, a/several file(s) must be renamed on disk), and for those microseconds, the table will be locked. The idea is to have a minimum amount of locking. For MyISAM, every time you write a row (except for concurrent inserts) the whole table is locked, and for InnoDB, at least that particular row (sometimes more). You cannot change that unless you sacrifice the atomicy and durability of changes. However, you can take into acount some tricks to minimize its problems:

Use InnoDB instead of MyISAM for extra write concurrency
Avoid slow/large writing transactions on that table during the copy process
Lower the value of chunk-time parameter. In an extreme case, you could set chunk-size to one, copying and locking one row at a time, but that will be extremely slow. The chunk-time will be an heuristic worse-case scenario of the maximum locking time when you happen to write the exact rows that you are copying at that time.
MySQL 5.6 allows for concurrent writes while certain DDLs are running. It is less flexible, but it should work faster as it has access to the underlying logic. In some cases, this may work better, but it will suffer from similar problems when doing too many concurrent writes (as those will be buffered).

Reading the code of the tool is not that hard, I invite you to read it for more information.


Why do 59% of my Execution Plans have CONVERT_IMPLICIT warnings?

Not every implicit conversion is a problem that needs to be fixed. Is this contributing to any actual performance issue? How do you know this?

The reason an implicit conversion comes into play here is not because of data type or collation but rather because of precision. SQL Server will come up with a guess that this expression:

LastName + ', ' + FirstName + ' ' + MiddleInitial

Will need to be:

varchar
(
  datalength(LastName)
  + 2
  + datalength(FirstName)
  + 1
  + datalength(MiddleInitial)
)

You can prove this by selecting a row from Person.Person in AdventureWorks into a new #temp table using the same expression:

Output (note that in AdventureWorks these are nvarchar, so every character counts as two. So adding up ', ' and ' ' adds 6 characters to the string:

Your conversion is happening the other way around - your expression, which is:

varchar
(
   50
   + 2
   + 50
   + 1
   + 100
)
= 203

The implicit conversion (which is totally unnecessary and effectively does nothing) is making your expression (203 characters) conform to the column (250 characters). Again, this has nothing to do with type. Not all conversions involve varchar/nvarchar.

If you want to get rid of the warning, you can wrap the expression in an explicit convert(varchar(250), or add a computed column in the source table that does the same, but that won't really change anything about how the code works or how fast it executes.

Finally, you should probably consider using nvarchar for proper names. You may not think you need it today, but you probably will at some point. Conversion is typically more painful later.


If you add the output clause at the end of each update statement with a COUNT(inserted.*) the counts of updated records will be returned as a result set.

You could create a new table in a new database to store the results from your scheduled script or have the SQL Agent output the results to a file.

Have a look at this answer for help with OUTPUT clause here


Are you running flashback database? If you were you could run a process that would start just after the top of every hour. The process would look at each table as it was at the beginning of every hour and do a Merge command between the source and destination databases for a one way sync. You would then be able to replicate all of the tables every hour. If for some reason, this process takes more than an hour, then you would replicated every other hour using the top of every even or odd hours as the starting point. You would be replicating based on consistent data across all tables everytime.

Goldengate would probably work better if you were licensed for it. It would keep the data more current more of the time. Governments should get a higher discount than corporations. It's worth getting a price for Goldengate even if you find out it costs too much.


How to select not exists data in SSIS package

i achieved by following this post;
How to get unmatched data between two sources in SSIS Data Flow?

and here is my screen shot;

by doing this, i am joining 2 different sources and splitting unmatched data than i insert into destination.

i appreciate your efforts. Thank you so much.


They are not equivalent.

NOT (a=1 AND b=1)

is equivalent with:

(NOT a=1 OR NOT b=1) <=> (a<>1 OR b<>1)

This equivalence is known as De Morgan's Law. See for example:

https://en.wikipedia.org/wiki/De_Morgan%27s_laws

A nice technique for proving/disproving equivalences for boolean algebra expressions is to use a cte for the domains, and compare the expressions side by side:

with T(a) as ( values 0,1 )
   , U(a,b) as (select t1.a, t2.a as b
               from t as t1
               cross join t as t2
)
select a,b
    , case when not (a=1 and b=1) then 1 else 0 end
    , case when a<>1 and b<>1 then 1 else 0 end
from U

A           B           3           4
----------- ----------- ----------- -----------
          0           0           1           1
          0           1           1           0
          1           0           1           0
          1           1           0           0

Edit: Since DB2 does not support Boolean datatype I expanded the example at:

http://sqlfiddle.com/#!15/25e1a/19

The rewritten query looks like:

with T(a) as ( values (0),(1),(null) )
   , U(a,b) as (select t1.a, t2.a as b
                from t as t1
                cross join t as t2
)
select a,b
     , not (a=1 and b=1) as exp1
     , a<>1 or b<>1 as exp2
from U;

The result of the query is:

a       b       exp1        exp2
--------------------------------
0       0       true        true
0       1       true        true
0       (null)  true        true
1       0       true        true
1       1       false       false
1       (null)  (null)      (null)
(null)  0       true        true
(null)  1       (null)      (null)
(null)  (null)  (null)      (null)

As shown exp1 and exp2 are equivalent.


You're correct that MongoDB can index only one array per document. This is called Multikey index. The limitation is due to the fact that MongoDB will create one index entry per array element.

So for example if you have an array with 10 elements, MongoDB will create 10 index entries for that single document. If you index more than one array, you can imagine that the number of index entries will go up exponentially, making the overall index impractically large.

One solution to your question is to refactor your document to use only one array, with the key tag, actors, and categories acting as a sub-document key. For example:

> db.test.find()
{
  "_id": ObjectId("597820ef2dbe1d773beb11d4"),
  "url": "whatever.com",
  "values": [
    {"tag": "tag1"},
    {"tag": "tag2"},
    {"actor": "actor1"},
    {"actor": "actor2"},
    {"category": "category1"},
    {"category": "category2"}
  ]
}

You can index this document easily since it only contains one array.

To search for the condition you proposed: tag1 AND tag 2 ... AND tagN AND categorie1 AND categorie2 ... AND categorieN AND actor1 AND actor2 ... AND actorn, you can use the $all operator. For example:

> db.test.find({
    values: {$all: [
        {tag: 'tag1'},
        {tag: 'tag2'},
        {actor: 'actor1'},
        {actor: 'actor2'},
        {category: 'category1'},
        {category: 'category2'}
    ]}
})

Which will return the document shown above.

Please see Query an Array for more methods in querying an array in MongoDB.

Also please see Explain results for methods to observe and optimize your query performance.


MySQL: Securing Access using Stored Procedures

You need to grant the EXECUTE privilege, but there is something else you need to know.

When you create a Stored Procedure (SP), make sure the SQL SECURITY of the SP is DEFINER and not INVOKER. Why?

When you call a Stored Procedure that has DEFINER for SQL SECURITY, the caller is allowed to have the same grants as the DEFINER for the duration of the call. The GRANT EXECUTE for the specified Stored Procedure is necessary.
When you call a Stored Procedure that has INVOKER for SQL SECURITY, the caller is expected to have the needed grants. If any of the needed grants are missing, the call will fail at the earliest point where the needed grant was missing.

In plain English, if SP has INVOKER for SQL SECURITY, you have to give the table privileges away to the user permanently before the call is made. When SP has DEFINER for SQL SECURITY, the table privileges are given away temporarily (as long as the SP is running).

I discussed this before

Sep 11, 2012 : Is it possible to encapsulate a table in MySQL?
Apr 08, 2013 : Why does this procedure raise a privilege error?


How can I setup some kind of buffering database with mariadb

Primary - Primary  (aka multi-Master) replication.
When either is down, the other continues to log changes to the "binlog", which will be promptly sent to the other machine when it comes back up.
Read the manual for details.  It is essentially setting up Replication to go both ways.  There are several steps.  A common mistake is forgetting to turn on log_slave_updates.


Data pump uses BLOCKS method to estimate the size of the dump by default during the export. The estimate is calculated by multiplying the number of database blocks used by the source objects, multiply the block sizes.

If the tables are fragmented then actual size may vary.

In your case, as you have said, you had heavy deletion in the past which didn't shrink down the High Water Mark leading data pump to count empty blocks as well during the estimation.

Dump file only contains index DDLs during the export and these DDLs runs when we import it resulting in bigger database size.

Demonstration:

For demonstration  I have created one table selecting from all_objects view.

SQL> create table test_tbl as select * from sys.all_objects;

Table created.

The newly created table has 1664 blocks.((1664*8)/1024=13). Which is 13MB.

SQL> select blocks from user_segments where segment_name='TEST_TBL';

    BLOCKS
----------
      1664

Now lets estimate the dump size of this table.

[oracle@ora12c Desktop]$ expdp jay/password@orapdb1 tables=test_tbl  directory=dp_dump estimate_only=y

Export: Release 12.1.0.2.0 - Production on Sat Jan 28 13:16:20 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "JAY"."SYS_EXPORT_TABLE_01":  jay/password@orapdb1 tables=test_tbl directory=dp_dump estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "JAY"."TEST_TBL"                               13 MB
Total estimation using BLOCKS method: 13 MB
Job "JAY"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jan 28 13:16:28 2017 elapsed 0 00:00:05

Its exactly 13MB.

Next, lets delete all the records from the table and try estimate the dump size of table.

SQL> delete from test_tbl;

89517 rows deleted.
SQL> commit;

Commit complete.

Lets run expdp-

oracle@ora12c Desktop]$ expdp jay/password@orapdb1 tables=test_tbl  directory=dp_dump estimate_only=y

Export: Release 12.1.0.2.0 - Production on Sat Jan 28 13:24:52 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "JAY"."SYS_EXPORT_TABLE_01":  jay/password@orapdb1 tables=test_tbl directory=dp_dump estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "JAY"."TEST_TBL"                               13 MB
Total estimation using BLOCKS method: 13 MB
Job "JAY"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jan 28 13:25:03 2017 elapsed 0 00:00:09

Its still 13 MB.

So such fragmentation may exist in tables that we try to export and the estimated size can be misleading.

Better way to estimate size of the dump file would be using ESTIMATE=STATISTICS in expdp command. But you should gather table statistics to gain accuracy as shown below.

SQL> exec dbms_stats.gather_table_stats('JAY','TEST_TBL');

PL/SQL procedure successfully completed.

Again, lets try to estimate the dump size.

[oracle@ora12c Desktop]$ expdp jay/password@orapdb1 tables=test_tbl  directory=dp_dump estimate_only=y estimate=statistics

Export: Release 12.1.0.2.0 - Production on Sat Jan 28 13:45:02 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "JAY"."SYS_EXPORT_TABLE_01":  jay/password@orapdb1 tables=test_tbl directory=dp_dump estimate_only=y estimate=statistics
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "JAY"."TEST_TBL"                            11.43 KB
Total estimation using STATISTICS method: 11.43 KB
Job "JAY"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jan 28 13:45:07 2017 elapsed 0 00:00:03

Now it's showing accurate result, its 11.43 KB which contains meta data only.

References:
Data Pump Export
DBMS_STATS


I need the reason why we should not install SQL server on the domain controller

The points you have listed are shown in BOL here, and even though it quotes:

  SQL Server Setup will not block installation on a computer that is a domain controller

It states on this KB article that the setup will fail, although the applies to only list up to 2012 version.

One main reason you are going to have against doing this is Microsoft Support. As it states in documentation you should not install it, they are not likely going to provide support. Especially if you experience any security or performance issues. Unless you fork over a good bit of money they are probably not going to do much for you.

I have never seen a company put these two roles together out of choice. I hate to say it but if they need to run an application in SQL Server and it is going to be a mission critical application there is no reason for them not to invest in running that application. Hardware is cheap these days and it does not take much for AD to run if your organization is that small. You could even go and get 2 or 3 year old hardware for that matter.

Now with that said, Window Small Business server used to be the one stop shop for a situation like you describe. It is an AD, Exchange, and web server all on in one; and if I recall also had an installation of SQL Server but I can't remember. I setup one of these servers for a department one time.

However with Window Server 2012 they changed this to Window Server Essential Edition, and I am not sure what roles you get with this Edition. I would suggest though you simply spend the money to get Standard Edition. A feature you get with Standard is licensing to run two guest OS virtual machines via Hyper-V. If you size the hardware appropriately it will allow you to run your SQL Server instance in one VM and then your application in the second VM. Although your performance is all on one server it provides a boundary separation and would be a fully supported setup by Microsoft. Just my two cents.


MySQL table with identical name multiple times in same database

Resolved the problem through Azure Helpdesk
The problem was that the parameter dir_cache_fine_grained_enabled switched on during an Azure update.
This needed to be switched off and the DB server restarted. After restart things went back to normal
The parameter is not available to the public.


This is possible for PostgreSQL 8.4 or later. In order to create a multi-column GIN index, you need to install the additional module btree_gin.
Simple types like integer or date are usually better off with a (default) B-Tree index, so that's not installed with standard PostgreSQL. But for a case like this a multi-column index is fastest, so you need the additional index methods for the plain types.

In PostgreSQL 9.1 or later you can install it with CREATE EXTENSION:

CREATE EXTENSION btree_gin;

In older versions you would run as privileged system user (like postgres):

psql -d dbname -f SHAREDIR/contrib/btree_gin.sql

For a PostgreSQL 8.4 installation on Debian,this might be:

psql -d mydb -f /usr/share/postgresql/8.4/contrib/btree_gin.sql

Then, given this table:

CREATE TEMP TABLE tbl (age int, names text[], thedate date);

... you can create this multi-column GIN index:

CREATE INDEX tbl_gin_idx ON tbl USING GIN (names, age, thedate);

... which can be used by a query like:

SELECT * FROM tbl
WHERE  age = 26
AND    '{json}'::text[] <@ names
ORDER  BY thedate;

Note that a GIN index caries a non-trivial cost for write operations.
But that's hardly worth mentioning in comparison to what a SELECT on 10 million rows without index would do to you.


Using View filters for tenants in SQL-Server 2008

I'd use SUSER_SID for this reason:

SUSER_ID is the principal_id column in sys.server_principals
SUSER_SID is the SID column in sys.server_principals and be generated by Active Directory (Windows login) or SQL Server

If you move the database to another server

then SUSER_ID will almost certainly be different.
SUSER_SID will be the same for Windows logins, but can be made the same for SQL logins by specifiying SID for CREATE LOGIN.


Does using an auto increment of 1 on numeric PK hurts performance?

I think the answer is that "it depends". In a lot of use cases, using an auto increment PK is good practice.

In InnoDB, the default storage engine in MySQL and MariaDB, the performance penalty of using the auto increment feature depends on the auto increment lock mode used. Two of the modes come at the cost of a table-level lock that is released once the statement has completed, which means only one statement can run at any one time. The third option (interleaved lock mode) doesn't lock the tables.

Oracle (12c) actually has auto incrementing keys (identity columns). In previous versions you had to use sequences, which can serve the same purpose, and more. They are a bit more cumbersome to use, though, than auto incrementing keys.

The suggestion that applications should be generating these ID numbers on their own without the aid of the RDBMS' sequence or auto increment features really isn't realistic in a lot of use cases.


"AT TIME ZONE" with zone name PostgreSQL bug?

Actually, the documentation says clearly that the time zone name and abbreviation will behave differently.

  In short, this is the difference between abbreviations and full names:
  abbreviations always represent a fixed offset from UTC, whereas most
  of the full names imply a local daylight-savings time rule, and so
  have two possible UTC offsets. Reference

FWIW, that same reference also says

  We do not recommend using the type time with time zone (though it is
  supported by PostgreSQL for legacy applications and for compliance
  with the SQL standard).


Error creating database in SQL Server Management Studio. (Could not load file or assembly 'Microsoft.SqlServer.Sqm)

Close Management Studio.

Find the file Microsoft.SqlServer.Sqm.dll located in one of the following folders:

  C:\Program Files\Microsoft SQL Server\110\Shared\
  C:\Program Files\Microsoft SQL Server\110\Tools\Binn\VSShell\Common7\IDE\
  C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\VSShell\Common7\IDE\

Wherever you find it, make sure it also exists in the other two locations.

I don't know that this will solve your issue, so please let me know - if not I will delete this. This was just too complex to add as a comment.


It doesn't matter much if you use an uuid or a bigint.

If you generate the keys in one database, using a bigint column with a sequence has the advantage that it uses only 8 bytes instead of 16. Also, counting an integer up is cheaper than generating a UUID.

UUIDs shine if keys are generated in several places independent from each other and the resulting identifier should still be globally unique.


Each table should have a UserID column, used to "link", or relate, rows in the User table with rows in the Personal, Business, and Board tables.

In SQL language, this looks like:

CREATE TABLE Users
(
    UserID int PRIMARY KEY
    , UserName varchar(30)
);

CREATE TABLE Personal
(
    PersonalID int PRIMARY KEY
    , UserID int FOREIGN KEY Users(UserID)
    , PersonalDetails varchar(40)
);

CREATE TABLE Business
(
    BusinessID int PRIMARY KEY
    , UserID int FOREIGN KEY Users(UserID)
    , BusinessDetails varchar(40)
);

CREATE TABLE Board
(
    BoardID int PRIMARY KEY
    , UserID int FOREIGN KEY Users(UserID)
    , BoardDetails varchar(40)
);

Then, when you want to query those tables, you'd write something like:

SELECT UserName
    , PersonalDetails
    , BusinessDetails
    , BoardDetails
FROM Users
    INNER JOIN Personal ON Users.UserID = Personal.UserID
    INNER JOIN Business ON Users.UserID = Business.UserID
    INNER JOIN Board ON Users.UserID = Board.UserID


If you want to quickly migrate your data from MS SQL Server to MySQL, I suggest using the Migration tool from Oracle. This can really save you some time and effort.

This article can help you get started:

http://www.infoq.com/news/2012/08/MySQL-Migration


Mapping of multiple server logins to one database user

First to make sure of terminology.  A Login is an instance level security principal (sys.server_principals) and a User is a database level security principal (sys.database_principals).  They are joined together by an SID (security identifier).  If you look in the system views above you can see how they are joined together in a 1:1 format by SID.  That's 1 Login to 1 User in a database.  A Login can have multiple Users but they must be in different databases.

So you will need to create a User in your database for each Login that you want to have access to it.  It's a simple enough command.

CREATE USER [UserName] FROM LOGIN [LoginName]

From there you can put all of your users into a single (or multiple) Role.  A role is a container that has permissions and shares those permissions with each User (for database roles) or Login (for instance roles).  There is a built in database role called db_datareader that gives read access to every table and view in the database.  You could add all of your users to that role.  However, a better idea would be creating a new role and adding it to the db_datareader role.  Then add all of your users to the new role.  The benefit here is that if you want to add additional permissions to the group you can by simply changing the permissions on the role.

Create the role by:

CREATE ROLE RoleName

Add a user to a role (or one role to another)

EXEC sp_addrolemember 'RoleName','UserName'

or if you are in 2012 or higher

ALTER ROLE [RoleName] ADD MEMBER [UserName]


SQL table design for primary keys (best practice)

I agree with Maess - if you have distinct entities, each of those ought to have their own ID columns. But I have a strong objections against using GUID's for your primary keys - or more specifically - for your SQL Server's clustering keys.

GUIDs may seem to be a natural choice for your primary key - and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I'd strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless you specifically tell it not to.

You really need to keep two issues apart:

1) the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an INT, a GUID, a string - pick what makes most sense for your scenario.

2) the clustering key (the column or columns that define the "clustered index" on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option.

By default, the primary key on a SQL Server table is also used as the clustering key - but that doesn't need to be that way! I've personally seen massive performance gains when breaking up the previous GUID-based Primary / Clustered Key into two separate key - the primary (logical) key on the GUID, and the clustering (ordering) key on a separate INT IDENTITY(1,1) column.

As Kimberly Tripp - the Queen of Indexing - and others have stated a great many times - a GUID as the clustering key isn't optimal, since due to its randomness, it will lead to massive page and index fragmentation and to generally bad performance.

Yes, I know - there's newsequentialid() in SQL Server 2005 and up - but even that is not truly and fully sequential and thus also suffers from the same problems as the GUID - just a bit less prominently so.

Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.

Quick calculation - using INT vs. GUID as Primary and Clustering Key:

Base Table with 1'000'000 rows (3.8 MB vs. 15.26 MB)
6 nonclustered indexes (22.89 MB vs. 91.55 MB)

TOTAL: 25 MB vs. 106 MB - and that's just on a single table!

Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really.

GUIDs as PRIMARY KEY and/or clustered key
The clustered index debate continues
Ever-increasing clustering key - the Clustered Index Debate..........again!
Disk space is cheap - that's not the point!


How many backup devices is recommended to improve backup performance?

In order to improve performance of our SQL Server backups we backup to multiple backup files

Not generally, no. That is a special purpose technique that can help.

  big amount of threads waiting

Why would that cause a loss of throughput? Threads are normally an ample resource. Problems arise in certain configurations where you burn through hundreds and into the thousands of threads.

  based on [the] number of cores in the server

That would make sense if the backup operation was CPU bound. That could be the case. As long as it is IO bound it cannot help to target the number of cores with any setting.

  one of our server shows BACKUPIO and BACKUPBUFFER as top 2 offenders wait stats

That points to an IO bound backup process. According to my experience that is far more common than CPU boundedness.

  a big DB (several TBs)

The size of the backup does not play a role in throughput considerations. Throughput is performance per unit of time.

There mere presence of waiting threads does not normally affect other threads and operations on the server. Waiting threads do nothing.

  so I have changed the number of backup files to use 4 backup files instead

That was unfounded because the backup is IO bound. Targeting the number of cores is not a good target.

If you added 128 CPU cores, your backup would not become one second faster.

You might have increased performance but only accidentally.

How many backup files to use is influenced by many things. An easy way to find a good number is to simply test different values, including 1 (the most likely value for common systems).


Select distinct values of two columns as single

SELECT a AS result
FROM tableX

UNION DISTINCT

SELECT b
FROM tableX ;


This isn't possible with the proposed table structure declaratively. You would need triggers to enforce this.

A unique index on both columns, together with a pair of check constraints with scalar UDFs, gets quite close however.

CREATE TABLE UserProfile
  (
     Id             INT PRIMARY KEY,
     PrimaryEmail   VARCHAR(100),
     SecondaryEmail VARCHAR(100)
  )

CREATE UNIQUE INDEX IX1
  ON UserProfile(PrimaryEmail)

CREATE UNIQUE INDEX IX2
  ON UserProfile(SecondaryEmail)

go

CREATE FUNCTION dbo.EmailInUseAsPrimary (@Email VARCHAR(100))
RETURNS BIT
AS
  BEGIN
      RETURN
        (SELECT COUNT(*)
         FROM   UserProfile WITH (READCOMMITTEDLOCK)
         WHERE  PrimaryEmail = @Email)
  END;

go

CREATE FUNCTION dbo.EmailInUseAsSecondary (@Email VARCHAR(100))
RETURNS BIT
AS
  BEGIN
      RETURN
        (SELECT COUNT(*)
         FROM   UserProfile WITH (READCOMMITTEDLOCK)
         WHERE  SecondaryEmail = @Email)
  END;

GO

ALTER TABLE UserProfile
  ADD CHECK ( dbo.EmailInUseAsPrimary(SecondaryEmail) = 0),
      CHECK ( dbo.EmailInUseAsSecondary(PrimaryEmail) = 0)

The reason for READCOMMITTEDLOCK is to avoid problems with snapshot isolation.

One problem with the approach above is that because the constraints are evaluated RBAR it can fail some transactions that ought to succeed.

For the example data

INSERT INTO UserProfile
VALUES (1, 'abc@abc.com', 'def@def.com'),
       (2, 'ghi@ghi.com', 'jkl@jkl.com')

This statement fails

UPDATE UserProfile
SET PrimaryEmail = CASE Id WHEN 1 THEN 'jkl@jkl.com' WHEN 2 THEN 'def@def.com' END,
    SecondaryEmail = CASE Id WHEN 1 THEN 'ghi@ghi.com' WHEN 2 THEN 'abc@abc.com' END

even though at the end of the transaction the constraints would have been met. But maybe it is sufficiently unlikely that you will be performing this kind of update (swapping email addresses between both type and person) that this can be ignored.


How to configure an Oracle Listener without SID_LIST_LISTENER in listener.ora?

Bit late to the party on this one...

The database should register with the listener automatically, making the SID_LIST entries redundant, and this seems to be happening with your 192.168.111.111 environment. If the listener is started after the database it can take a while for it to register, and there may be situations where it doesn't do so at all.

You can attempt to make it register with an alter system register command. The database uses the LOCAL_LISTENER parameter to identify the listener it should register with. By default that is null, which according to the documentation is equivalent to hostname:1521.

If alter system register doesn't make the service appear in the lsnrctl status output then I'd suspect it's unable to either identify the hostname or resolve it, or it's resolving to a different address than the one the listener is on, or LOCAL_LISTENER is set to something invalid for the virtual box. You can set the LOCAL_LISTENER to either match the listener.ora directly, e.g:

alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both;
alter system register;

Or you can use an alias that has those details in your tnsnames.ora. For example, if you add a tnsnames.ora entry like:

MY_LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  )

Then you could:

alter system set local_listener='MY_LISTENER' scope=both;
alter system register;

The only real advantage I can see of using the tnsnames.ora version, apart perhaps from brevity, is that you can change the configuration in the SQL*Net files rather than in the database; and even that only really seems useful if you're cloning databases between machines, or have your listeners and databases running under different accounts (e.g. with a grid user for RAC/HA).

There's more on LOCAL_LISTENER here.

Edit: And this seems quite comprehensive.


When installing Oracle, what's the quickest way to check rpm packages and install what is  missing?

Check out Howard Roger's gladstone shell script. His scripts have always done a great job for me in performing all the necessary pre-installation tasks.


Those files belong to the journal, which is essentially what is going to guarantee consistency of your data as you write it to the database.  It is pre-allocated at 3GB (unless you start with --smallfiles) and will not be included in the size of your database.  This will not grow with your database but rather it will be based on how much you are writing to the database.  Unless you are doing a lot of writes the journal size will be 3GB.  If you are curious about journaling and how it works, there is a great write up here.

As for the database itself, the disk space usage question has been asked and answered many times - yes a regular compact (and repair or resync) can be needed, but it very much depends on what you are doing with the data.


Oracle has spoken:

http://docs.oracle.com/cd/B19306_01/server.102/b14231/ds_txnman.htm#i1008473

  Distributed Query Returns Different Results on Consecutive Runs when
  no Changes Exist in the Base Tables. (Doc ID 561471.1)

...skip some more writing...

  Bug.611416 SELECT AFTER UPDATE AND COMMIT DOESN'T SHOW UPDATED COLUMNS

...skip some more writing...

  "You can use the following techniques to ensure that the SCNs of the
  two machines are synchronized just before a query:
  * Because SCNs are synchronized at the end of a remote query, precede each remote query with a dummy remote query to the same site, for
  example, SELECT * FROM DUAL@REMOTE.
  * Because SCNs are synchronized at the start of every remote transaction, commit or roll back the current transaction before
  issuing the remote query. "


I can't start mysqld.service -> status : Data Dictionary upgrade from MySQL 5.7 in progress in centos 7

This worked for me:

Clear the mysql data directory at /var/lib/mysql assuming that you have backups or you don't really need that data anymore.

Then sudo service mysqld start


Can we have two primaries for the same shard in mongodb?

MongoDB does not currently support a multi-master setup such as you describe.

If you were looking to provide localized regional writes via MongoDB, your best bet is to setup a sharded collection, using shard tag ranges. You would have 1 (or more) shard per region, with the primary residing within the region it manages and remote secondaries. Region would be the first member in your shard key and you would then peg a region's range to the shard that owns that region.

Reads for that region's data performed from a different region could then use a read preference of "nearest", which would result in a secondary read to the local secondary.


Combine rows with instruction from separate list

The following query should give you 112, 9, 12

select min(id) as newId,
       sum(value_1) as newValue1,
       sum(value_2) as newValue2
  from myTable t
 where id in ( 112,115);


MySQL - Flush Memory after loading files

If you are not using MyISAM for anything (and you should not), then change key_buffer_size 999997440 to only 10M.  That is a wasted gigabyte.

Also, this seems quite large for your 8GB of RAM:  tmp_table_size  268435456.  Lower it to 16M.


Getting "not a single-group group function"

If you don't use GROUP BY, then you can only use constant, and aggregate functions is the SELECT clause.

This is wrong:
CASE
    WHEN cmve.driver_equip_id is null
    THEN COUNT(distinct(cmve.equip_mdl_id))
  END

CASE
    WHEN cmve.driver_equip_id is not null
    THEN COUNT(distinct(cmve.equip_mdl_id))
  END

count (distinct case ... end) is probably what you need.

SELECT 1                          AS "Contract",
  COUNT(DISTINCT cmv.mdl_ver_id) AS "Number Of Scenarios",
  COUNT(DISTINCT cmvs.site_id)   AS "Number Of Sites",
  count (distinct CASE
    WHEN cmve.driver_equip_id is null
    THEN cmve.equip_mdl_id
  END)                             AS "Number of Driven",
  count (distinct CASE
    WHEN cmve.driver_equip_id is not null
    THEN cmve.equip_mdl_id
  END)                             AS "Number of Driver"
 FROM cost_contract_mdl ccm,
  cost_model_ver cmv,
  cost_mdl_ver_site cmvs,
  cost_mdl_ver_equip cmve
WHERE ccm.mdl_id   =cmv.mdl_id
AND cmv.mdl_ver_id =cmvs.mdl_ver_id
AND cmv.mdl_ver_id =4281021;

P.s.

DISTINCT is not a function, it is a keyword, which makes the parentheses meaningless.

Instead of COUNT(DISTINCT(x))
It should be COUNT(DISTINCT x)


Array to string sometimes concatenates fewer values

Ensure that every field has a default value by using COALESCE.

create table t (highlights varchar(10),
                exceptions varchar(10),
                inter_note varchar(10),
                inter_status int,
                comp_note varchar(10),
                comp_status int,
                hard_note varchar(10),
                hard_status int);

insert into t values ('a','b','c',1,'d',2,'e',3),('',null,'f',4,null,5,'g',null);

✓

2 rows affected

select array_to_string(
         array_agg(
           array_to_string(
             array[coalesce(highlights, ''),
                   coalesce(exceptions, ''),
                   coalesce(inter_note,inter_status::text, ''),
                   coalesce(comp_note, ''),
                   coalesce(comp_status::text, ''),
                   coalesce(hard_note, ''),
                   coalesce(hard_status::text, '')],'|#|'::text)),'|*|'::text) as status
from   t;

| status                                            |
| :------------------------------------------------ |
| a|#|b|#|c|#|d|#|2|#|e|#|3|*||#||#|f|#||#|5|#|g|#| |

dbfiddle here


Optimizing key lookup in subquery with UNION ALL

Have you tried this?

SELECT Id, StatusDate, [ some way to select columns from appropiate table depending on result.tbl ]
FROM (
        SELECT Id, StatusDate, 'Results_201505' as tbl
        FROM Results_201505
        WHERE A = 0, B = 1, C = 3
    UNION ALL
        SELECT Id, StatusDate, 'Results_201504' as tbl
        FROM Results_201504
        WHERE A = 0, B = 1, C = 3
    UNION ALL
        SELECT Id, StatusDate, 'Results_201503' as tbl
        FROM Results_201503
        WHERE A = 0, B = 1, C = 3
    ORDER BY StatusDate DESC
    OFFSET xxx ROWS
    FETCH NEXT 25 ROWS ONLY
) AS result ;


DBCC SHRINKFILE -- will be single-threaded – which will contribute to the long run-time.

Also, Sql Server Integration Services Data Export will be slower due to massive database size (1 TB) !

Instead, you should look for BCP OUT (in binary format) and BULK INSERT in the database.

BCP can read the SQL Server native format from text files. This is a very fast option that requires minimal parsing of the text file input.
The BULK INSERT command is the in-process method for bringing data from a text file into SQL Server. Because it runs in process with Sqlservr.exe, it is a very fast way to load data files into SQL Server.
Additionally, enable Trace Flag 610 to minimally log inserts into Indexed tables along with BATCHSIZE and ROWS_PER_BATCH parameters.
You can even disable Lock Escalation which on sql server 2005 generally occurs at 5000 locks allocated. This can be disabled on sql server 2005 by setting a batchsize to a number lower than this or enableing Trace flag 1211 (to completely disable lock esclation). In sql server 2008, you can do it using alter table  <table_name> (LOCK_ESCLATION = DISABLE)
BCP has a -a switch to allow to specify packet size or if using SSIS it can be done in properties of the connection manager --> PacketSize = 32767
To avoid  PFS contention, consider using -E startup parameter.

Also at hardware level, you can look into

Use fast NIC and switches.
Have the latest certified drivers for your NIC.
Enable full duplex.
Enable support for Jumbo frames.
Use TCP Chimney Offload.
Use Receive Side Scaling (RSS).

Check the BCP & BULK INSERT script that I provided at here

Note: Your hardware including disk partitioning, layout, number of CPU's, NUMA configuration, etc will also play a big role in performance when you load your data.

Excellent reading:

Bulk Inserting 1 Terabyte within 10 minutes
We Loaded 1TB in 30 Minutes with SSIS, and So Can You
Load 1TB in less than 1 hour
The Data Loading Performance Guide


Dealing with large transaction logs in a cluster

You are on the right track.  With the database in full recovery mode, no portions of the log file will be marked for reuse until it has been backed up.  I'm assuming you've created your maintenance plan to have the capability to backup the transaction log.  In which case, that should get you to a point where you should be allowing the reuse of log portions (there are other things that can prevent log reuse as well, but not worth mentioning until/unless you run into them).

How often are your transaction logs getting backed up?  Depending on the frequency of logged operations, you might need to set that to a more frequent schedule than once a day (again, it depends solely on the rate of logged transactions).

As for the physical log file sizes (25 GB NTFS files), they will not shrink just because you have done the transaction log backups.  Backups will not affect the file size.  You'd have to explicitly shrink the transaction log, if you in fact need to reclaim that space.

  Do I then need steps to shrink the databases and cleanup after the plan to get rid of the logs?

As for "steps to shrink the databases", no you shouldn't have to routinely do this.  You may want to do this once to get the log files to management sizes, but then with a well-chosen log backup schedule (see above) those log files shouldn't grow out of control again.  And with the "cleanup" of the transaction log backups, that's really up to you.  I'm not sure what type of disaster recovery plan you have for this environment, so the retention period of those transaction log backups will be driven by your requirements and the DR plan for these databases.


The main indicator is the exit status of the pg_dump command. If it's non-zero, then something went wrong otherwise it worked all along. This is the implicit contract between any command and the shell, and breaching it would be a bug.
And it's also testable when the sub-command could't be launched at all.

Here's a basic skeleton in Unix shell that tests the exit code and acts accordingly:

d=`date +%Y%m%d`
dump=/path/to/backups/daily-backup-"$d"
error=/path/to/backups/errors-backup-"$d".txt

pg_dump -U username dbname >$dump 2>$error
code=$?
if [ $code -ne 0 ]; then
  echo 1>&2 "The backup failed (exit code $code), check for errors in $error"
fi

It is necessary to keep the error messages in durable storage so that they can be analyzed later.
When no error at all occurs, the file for errors should be there but empty, unless the -v option is used but it's intentionally not in the above script.

About the message: saving database definition

It is displayed along with lots of other informational messages only when using the verbose (-v) option. The problem is that in case of error, these messages will be mixed with error messages, and it requires some level of expertise to distinguish what's normal from what's not.


Only recently waved goodbye to the last SQL2K server I looked after, so have a few scripts in the toolbox still. You could also root around in the SSC scripts archive.

Here's an old index maintenance script by Kimberly Tripp:

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp - all rights reserved.
-- UPDATE: AUGUST 25, 2004
-- RE: Fix for DESC clustered indexes. There are no errors for DESC in
-- nonclustered indexes. Thanks!
--
-- For more scripts and sample code, check out
--  http://www.SQLSkills.com
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- Execute this whole script to create the sp_RebuildIndexes stored procedure in Master.
-- Best Viewed with Courier New 12pt. and Tabs saved as 4 spaces not 8. (Tools, Options, Editor)

-- To use the sp_RebuildIndexes procedure once created use:
-- sp_RebuildIndexes
--      To Rebuild All Indexes on All Tables for all that have a Scan Density < 100%
-- sp_RebuildIndexes @ScanDensity = 80
--      To Rebuild All Indexes on All Tables with a Scan Density of < 80%
-- sp_RebuildIndexes 'Authors'
--      To Rebuild All Indexes on the authors table - for a Scan Density of < 100%
-- sp_RebuildIndexes 'Authors', 80
--      To Rebuild All Indexes on the authors table - for a Scan Density of < 80%
-- Object Name and ScanDensity are both optional parameters.
-- ScanDensity must be a whole number between 1 and 100.

USE master
go
IF OBJECTPROPERTY(object_id('sp_RebuildClusteredIndex'), 'IsProcedure') = 1
    DROP PROCEDURE sp_RebuildClusteredIndex
go

IF OBJECTPROPERTY(object_id('sp_RebuildIndexes'), 'IsProcedure') = 1
    DROP PROCEDURE sp_RebuildIndexes
go

CREATE PROCEDURE sp_RebuildClusteredIndex
(
    @TableName      sysname     = NULL,
    @IndexName      sysname     = NULL
)
AS
-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.sqlskills.com
-- NOTE: If your clustered index is NOT unique then rebuilding the clustered
-- index will cause the non-clustered indexes to be rebuilt. If the nonclustered
-- indexes were fragmented then this series of scripts will build them again.
-- This is something this script DOES NOT look for...because you should not create
-- clustered indexes on a non-unique column or composite set of columns.
IF @TableName IS NOT NULL
    BEGIN
        IF (OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0
                AND OBJECTPROPERTY(object_id(@TableName), 'IsView') = 0)
            BEGIN
                RAISERROR('Object: %s exists but is NOT a User-defined Table. This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
                RETURN
            END
        ELSE
            BEGIN
                IF OBJECTPROPERTY(object_id(@TableName), 'IsTable') IS NULL
                    BEGIN
                        RAISERROR('Object: %s does not exist within this database. Please check the table name and location (which database?). This procedure only accepts existing table names to process for index rebuilds.', 16, 1, @TableName)
                        RETURN
                    END
            END
    END

IF @IndexName IS NOT NULL
    BEGIN
        IF INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered') = 0
            BEGIN
                RAISERROR('Index: %s exists but is a Clustered Index. This procedure only accepts valid table names and their clustered indexes for rebuilds.', 16, 1, @IndexName)
                RETURN
            END
        ELSE
            BEGIN
                IF INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered') IS NULL
                    BEGIN
                        SELECT @TableName, @IndexName, INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered')
                        RAISERROR('There is no index with name:%s on this table. Please check the table name and index name as well as location (which database?). This procedure only accepts existing table names and their clustered indexes for rebuilds.', 16, 1, @IndexName)
                        RETURN
                    END
            END
    END

-- So now we have a valid table, a valid CLUSTERED index and we're ready to rebuild.
    -- Here's a quick overview of what this code will do:
        -- Get the Column List and Index Defintion (Use the output from sp_helpindex)
        -- Figure out if it's UNIQUE - to specify in CREATE INDEX statement
        -- Build and Execute the CREATE INDEX command through dynamic string execution

DECLARE @ExecStr        nvarchar(4000) -- more than enough even if 16 cols of 128 chars,
                                       -- Tablename of 128 and Indexname of 128...
                                       -- but if this is the case you have other problems :).
        , @ColList      nvarchar(3000)
        , @Unique       nvarchar(7)    -- Will be either '' or 'Unique ' and added to CR Index String
        , @FillFactor   nvarchar(100)

CREATE TABLE #IndexInfo
(
    IndexName   sysname,
    IndexDesc   varchar(210),
    IndexKeys   nvarchar(2126)
)

INSERT INTO #IndexInfo
    EXEC sp_helpindex @TableName

SELECT @ColList = IndexKeys
         , @Unique = CASE
                        WHEN IndexDesc LIKE 'clustered, unique%'
                            THEN 'Unique '
                        ELSE ''
                    END --CASE Expression
         , @FillFactor = ', FILLFACTOR = ' + NULLIF(convert(nvarchar(3),
                        (SELECT OrigFillFactor
                            FROM sysindexes
                            WHERE id = object_id(@TableName)
                                AND Name = @IndexName)), 0)
FROM #IndexInfo
WHERE IndexName = @IndexName

SELECT @ExecStr = 'CREATE ' + @Unique + 'CLUSTERED INDEX '
                        + QUOTENAME(@IndexName, ']') + ' ON '
                        + QUOTENAME(@TableName, ']') + '(' + @collist
                        + ') WITH DROP_EXISTING ' + ISNULL(@FillFactor, '')

-- Added Aug 25, 04: FIX FOR DESC Indexes.
SELECT @ExecStr = REPLACE(@ExecStr, '(-)', ' DESC')

-- For testing the String
-- SELECT @ExecStr

-- Create the Clustered Index
EXEC(@ExecStr)
go

CREATE PROCEDURE sp_RebuildIndexes
(
    @TableName      sysname     = NULL,
    @ScanDensity    tinyint     = 100
)
AS
-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.sqlskills.com
--
-- This procedure will get the Fragmentation information
-- for all tables and indexes within the database.
-- Programmatically it will then walk the list rebuilding all
-- indexes that have a scan density less than the value
-- passed in - by default any less than 100% contiguous.
--
-- Use this script as a starting point. Modify it for your
-- options, ideas, etc. - and then schedule it to run regularly.
--
-- NOTE - This gathers density information for all tables
-- and all indexes. This might be time consuming on large
-- databases.
--
-- DISCLAIMER - Execute at your own risk. TEST THIS FIRST.
SET NOCOUNT ON

IF @ScanDensity IS NULL
    SET @ScanDensity = 100

IF @ScanDensity NOT BETWEEN 1 AND 100
    BEGIN
        RAISERROR('Value supplied:%i is not valid. @ScanDensity is a percentage. Please supply a value for Scan Density between 1 and 100.', 16, 1, @ScanDensity)
        RETURN
    END
IF @TableName IS NOT NULL
    BEGIN
        IF OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0
            BEGIN
                RAISERROR('Object: %s exists but is NOT a User-defined Table. This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
                RETURN
            END
        ELSE
            BEGIN
                IF OBJECTPROPERTY(object_id(@TableName), 'IsTable') IS NULL
                    BEGIN
                        RAISERROR('Object: %s does not exist within this database. Please check the table name and location (which database?). This procedure only accepts existing table names to process for index rebuilds.', 16, 1, @TableName)
                        RETURN
                    END
            END
    END

-- Otherwise the Object Exists and it is a table so we'll continue from here.
-- First thing to do is create a temp location for the data returned from DBCC SHOWCONTIG

CREATE TABLE #ShowContigOutput
(
    ObjectName          sysname,
    ObjectId                int,
    IndexName               sysname,
    IndexId                 tinyint,
    [Level]                 tinyint,
    Pages                   int,
    [Rows]                  bigint,
    MinimumRecordSize       smallint,
    MaximumRecordSize   smallint,
    AverageRecordSize       smallint,
    ForwardedRecords        bigint,
    Extents                 int,
    ExtentSwitches          numeric(10,2),
    AverageFreeBytes        numeric(10,2),
    AveragePageDensity  numeric(10,2),
    ScanDensity         numeric(10,2),
    BestCount               int,
    ActualCount         int,
    LogicalFragmentation    numeric(10,2),
    ExtentFragmentation numeric(10,2)
)

IF @TableName IS NOT NULL -- then we only need the showcontig output for that table
    INSERT #ShowContigOutput
        EXEC('DBCC SHOWCONTIG (' + @TableName + ') WITH FAST, ALL_INDEXES, TABLERESULTS')
ELSE -- All Tables, All Indexes Will be processed.
    INSERT #ShowContigOutput
        EXEC('DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS')
PRINT N' '

-- Quick test to see if everything is getting here correctly
-- SELECT * FROM #ShowContigOutput

-- Walk the showcontig output table skipping all replication tables as well as all tables necessary for
-- the UI. This is also where you can list large tables that you don't want to rebuild all at one time.
-- NOTE: If you take out a large table from rebuilding this script may have already checked density
-- meaning that the expense in terms of time may have been expensive.
-- Also, you should use a different procedure to rebuild a large table specifically.
-- Even when you pass in the tablename it will be avoided here if MANUALLY added to the
-- list by you.
-- Test, Test, Test!

DECLARE @ObjectName             sysname,
            @IndexName          sysname,
            @QObjectName            nvarchar(258),
            @QIndexName         nvarchar(258),
            @IndexID                tinyint,
            @ActualScanDensity  numeric(10,2),
            @InformationalOutput    nvarchar(4000),
            @StartTime              datetime,
            @EndTime                datetime

DECLARE TableIndexList CURSOR FAST_FORWARD FOR
    SELECT ObjectName, IndexName, IndexID, ScanDensity
    FROM #ShowContigOutput AS sc
        JOIN sysobjects AS so ON sc.ObjectID = so.id
    WHERE sc.ScanDensity < @ScanDensity
        AND (OBJECTPROPERTY(sc.ObjectID, 'IsUserTable') = 1
                OR OBJECTPROPERTY(sc.ObjectID, 'IsView') = 1)
        AND so.STATUS > 0
        AND sc.IndexID BETWEEN 1 AND 250
        AND sc.ObjectName NOT IN ('dtproperties')
            -- Here you can list large tables you do not WANT rebuilt.
    ORDER BY sc.ObjectName, sc.IndexID

OPEN TableIndexList

FETCH NEXT FROM TableIndexList
    INTO @ObjectName, @IndexName, @IndexID, @ActualScanDensity

WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        SELECT @QObjectName = QUOTENAME(@ObjectName, ']')
        SELECT @QIndexName = QUOTENAME(@IndexName, ']')
        SELECT @InformationalOutput = N'Processing Table: ' + RTRIM(UPPER(@QObjectName))
                                            + N' Rebuilding Index: ' + RTRIM(UPPER(@QIndexName))
        PRINT @InformationalOutput
        IF @IndexID = 1
        BEGIN
            SELECT @StartTime = getdate()
            EXEC sp_RebuildClusteredIndex @ObjectName, @IndexName
            SELECT @EndTime = getdate()
            SELECT @InformationalOutput = N'Total Time to process = ' + convert(nvarchar, datediff(ms, @StartTime, @EndTime)) + N' ms'
            PRINT @InformationalOutput
        END
        ELSE
        BEGIN
            SELECT @StartTime = getdate()
            EXEC('DBCC DBREINDEX(' + @QObjectName + ', ' + @QIndexName + ') WITH NO_INFOMSGS')
            SELECT @EndTime = getdate()
            SELECT @InformationalOutput = N'Total Time to process = ' + convert(nvarchar, datediff(ms, @StartTime, @EndTime)) + N' ms'
            PRINT @InformationalOutput
        END
        PRINT N' '
        FETCH NEXT FROM TableIndexList
            INTO @ObjectName, @IndexName, @IndexID, @ActualScanDensity
    END
END
PRINT N' '
SELECT @InformationalOutput = N'***** All Indexes have been rebuilt.  ***** '
PRINT @InformationalOutput
DEALLOCATE TableIndexList
go

Wait stats, the old way (not sure where this originated from):

use master
go

if ((object_id('sp_waitstats') is not null) and (objectproperty(object_id('sp_waitstats'), 'IsProcedure') = 1))
    drop proc [dbo].[sp_waitstats]
go

create proc [dbo].[sp_waitstats] @orderby nvarchar(10) = N'total'
as

set nocount on

if (lower(@orderby) not in ('total', 'resource', 'signal'))
begin
    raiserror('Error: incorrect @orderby value, use ''total'', ''resource'', ''signal''', 10, 1) with nowait
    return
end

declare @requests   bigint,
        @totalwait  numeric(20, 5),
        @totalres   numeric(20, 5),
        @totalsig   numeric(20, 5),
        @endtime    datetime,
        @begintime  datetime

create table [#waitstats]
(
    [wait type]         varchar(80) not null,
    [requests]          bigint not null,
    [wait time]         numeric(20, 5) not null,
    [signal wait time]  numeric(20, 5) not null,
    [now]               datetime not null default getdate()
)

insert into [#waitstats]([wait type], [requests], [wait time], [signal wait time])
    exec ('dbcc sqlperf(waitstats) with tableresults, no_infomsgs')

select  @begintime = min([now]),
        @endtime   = max([now])
from    [#waitstats]
where   [wait type] = 'Total'

--- subtract waitfor, sleep, and resource_queue from Total
select  @requests  = sum([requests]),
        @totalwait = sum([wait time]),
        @totalres  = sum(([wait time] - [signal wait time])),
        @totalsig  = sum([signal wait time])
from    [#waitstats]
where   [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total', '***total***')
and     [now] = @endtime

-- insert adjusted totals, rank by percentage descending
delete  [#waitstats]
where   [wait type] = '***total***'
and     [now] = @endtime

insert into #waitstats values('***total***', @requests, @totalwait, @totalsig, @endtime)

if (@orderby = N'total')
begin

    select  [requests],
            [wait type],
            [total wait time]       = [wait time],
            [resource wait time]    = [wait time] - [signal wait time],
            [signal wait time],
            [%total wait time]      = case @totalwait when 0 then 0 else cast(100 * [wait time] / @totalwait as numeric(20, 5)) end,
            [%resource wait time]   = case @totalres  when 0 then 0 else cast(100 * ([wait time] - [signal wait time]) / @totalres as numeric(20, 5)) end,
            [%signal wait time]     = case @totalsig  when 0 then 0 else cast(100 * [signal wait time] / @totalsig as numeric(20, 5)) end,
            [avg total wait time]   = case [requests] when 0 then 0 else cast(100 * @totalwait / [requests] as numeric(20, 5)) end,
            [avg resource wait time]= case [requests] when 0 then 0 else cast(100 * @totalres  / [requests] as numeric(20, 5)) end,
            [avg signal wait time]  = case [requests] when 0 then 0 else cast(100 * @totalsig  / [requests] as numeric(20, 5)) end
    from    [#waitstats]
    where   [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total')
    and     [now] = @endtime
    order by [%total wait time] desc

end

if (@orderby = N'resource')
begin

    select  [requests],
            [wait type],
            [total wait time]       = [wait time],
            [resource wait time]    = [wait time] - [signal wait time],
            [signal wait time],
            [%total wait time]      = case @totalwait when 0 then 0 else cast(100 * [wait time] / @totalwait as numeric(20, 5)) end,
            [%resource wait time]   = case @totalres  when 0 then 0 else cast(100 * ([wait time] - [signal wait time]) / @totalres as numeric(20, 5)) end,
            [%signal wait time]     = case @totalsig  when 0 then 0 else cast(100 * [signal wait time] / @totalsig as numeric(20, 5)) end,
            [avg total wait time]   = case [requests] when 0 then 0 else cast(100 * @totalwait / [requests] as numeric(20, 5)) end,
            [avg resource wait time]= case [requests] when 0 then 0 else cast(100 * @totalres  / [requests] as numeric(20, 5)) end,
            [avg signal wait time]  = case [requests] when 0 then 0 else cast(100 * @totalsig  / [requests] as numeric(20, 5)) end
    from    [#waitstats]
    where   [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total')
    and     [now] = @endtime
    order by [%resource wait time] desc

end

if (@orderby = N'signal')
begin

    select  [requests],
            [wait type],
            [total wait time]       = [wait time],
            [resource wait time]    = [wait time] - [signal wait time],
            [signal wait time],
            [%total wait time]      = case @totalwait when 0 then 0 else cast(100 * [wait time] / @totalwait as numeric(20, 5)) end,
            [%resource wait time]   = case @totalres  when 0 then 0 else cast(100 * ([wait time] - [signal wait time]) / @totalres as numeric(20, 5)) end,
            [%signal wait time]     = case @totalsig  when 0 then 0 else cast(100 * [signal wait time] / @totalsig as numeric(20, 5)) end,
            [avg total wait time]   = case [requests] when 0 then 0 else cast(100 * @totalwait / [requests] as numeric(20, 5)) end,
            [avg resource wait time]= case [requests] when 0 then 0 else cast(100 * @totalres  / [requests] as numeric(20, 5)) end,
            [avg signal wait time]  = case [requests] when 0 then 0 else cast(100 * @totalsig  / [requests] as numeric(20, 5)) end
    from    [#waitstats]
    where   [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total')
    and     [now] = @endtime
    order by [%signal wait time] desc

end

drop table [#waitstats]
go

exec sp_waitstats
exec sp_waitstats @orderby = 'total'
exec sp_waitstats @orderby = 'resource'
exec sp_waitstats @orderby = 'signal'
go


What's happening if one of concurent lock on a table rename the locked table?

The objects id (oid) at the time the statement is prepared and executed. Pg_locks doesn't even store the relations name.

        View "pg_catalog.pg_locks"
       Column       |   Type   | Modifiers
--------------------+----------+-----------
 locktype           | text     |
 database           | oid      |
 relation           | oid      |
 page               | integer  |
 tuple              | smallint |
 virtualxid         | text     |
 transactionid      | xid      |
 classid            | oid      |
 objid              | oid      |
 objsubid           | smallint |
 virtualtransaction | text     |
 pid                | integer  |
 mode               | text     |
 granted            | boolean  |
 fastpath           | boolean  |


Why COALESCE within a subquery returns NULL?

Things in the select are returned only if there are rows returned in the FROM statement.

First, let's think of it conceptually.

Query 1 is like:

  "Go find all of the Ferraris in your garage. For each Ferrari, give me
  the license plate number, or if it doesn't have a plate number, give
  me 'NO FERRARIS FOUND.'"

The query would come back with no rows - because there wasn't a Ferrari in the garage. (At least, there weren't any rows found in my own garage.)

Query 2 is different:

  "Go to the garage. IF you find a license plate on a Ferrari, give me
  that - otherwise, give me 'NO FERRARIS FOUND.'"

That's why the coalesce has to be outside of the search operation: you need it to happen even when there's no rows in the result set.

Now, let's look at your query.

I'm going to take the subquery out on its own, and I'm going to hard-code values for one of the rows where you want the COALESCE to work, but it can't:

SELECT TOP 1 COALESCE(t2.DateTest, 'NO FERRARIS FOUND')
     FROM         #TEST_COALESCE t2
     WHERE        t2.Id = 1
     AND          t2.DateTest > '2017-02-03 00:00:00.000'
     ORDER BY     t2.Id, t2.DateTest

In the WHERE clause, I've hard-coded Id = 1 and DateTest > '2017-02-03 00:00:00.000'. When this query runs, it returns no results:

That's why the COALESCE doesn't work: there were no rows in this result set, and no Ferraris in your garage. Master that concept, and you will have Ferraris in your...wait a minute...I've mastered that concept, and there are no Ferraris in my garage...


When designing the topology of a data model, does the database implementation matter?

Without knowing anything about the project, yes it does make sense. It is true that different relational database management systems (RDBMS) may support different features. If a certain feature is particular to only a subset of all RDBMS's, then it's theoretically possible to either a) not support that feature when creating the model, b) abstract out the part of the feature that is common across all RDBMS's, or c) support the feature in the model but give an error when trying to convert the model to a sql script for an RDBMS that doesn't support the feature.

Relational databases ("SQL databases") are fundamentally very different from NoSQL databases. In fact, the entities NoSQL databases are largely unmodeled by design. I would be surprised if they add support for NoSQL databases, but then again I don't know anything about the project.


Replace a sequential set of numbers with special character

Two factors are important for performance:

Reduce the number of string operations.
You may find it is possible to implement what you need using e.g. CHARINDEX and PATINDEX to find the start and end of groups, rather than performing very many REPLACE operations on the whole string each time.

Use the cheapest collation that provides correct results.
Binary collations are the cheapest. SQL collations (on non-Unicode data only) are a little more expensive. Windows collations are much more expensive.

For example:
DECLARE @T table
(
    SampleID varchar(200) NOT NULL UNIQUE
);

INSERT @T
    (SampleID)
VALUES
    ('ABC123124_A12312'),
    ('ABC123_A1212'),
    ('ABC123124_B12312'),
    ('AC123124_AD12312'),
    ('A12312_123'),
    ('999ABC888DEF');

SELECT
    T.SampleID,
    Pattern =
    (
        SELECT
            CASE
                WHEN Chars.this NOT LIKE '[0123456789]' THEN Chars.this
                WHEN Chars.prev NOT LIKE '[0123456789]' THEN '*'
                ELSE ''
            END
        FROM dbo.Numbers AS N
        OUTER APPLY
        (
            SELECT
                SUBSTRING(Bin.string, N.n, 1),
                SUBSTRING(Bin.string, N.n + 1, 1)
        ) AS Chars (prev, this)
        WHERE
            N.n BETWEEN 1 AND LEN(Bin.string)
        ORDER BY N.n
        FOR XML PATH ('')
    )
FROM @T AS T
OUTER APPLY (VALUES('$' + T.SampleID COLLATE Latin1_General_100_BIN2)) AS Bin (string);

db<>fiddle demo
That example relies on a permanent table of numbers. If needed, a table sufficient for varchar(200) is:
-- Create a numbers table 1-200 using Itzik Ben-Gan's row generator
WITH
  L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT
    -- Destination column type integer NOT NULL
    ISNULL(CONVERT(integer, N.n), 0) AS n
INTO dbo.Numbers
FROM Nums AS N
WHERE N.n >= 1
AND N.n <= 200
OPTION (MAXDOP 1);

-- Add clustered primary key
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_Numbers_n
PRIMARY KEY CLUSTERED (n)
WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1, FILLFACTOR = 100);

If that isn't faster, you might find that using a binary collation alone would speed up your existing implementation sufficiently. To implement that, change one line of your code to:
(REPLACE(SampleID COLLATE Latin1_General_100_BIN2, '0', '*'),

Users of SQL Server 2017 or later can leverage the built-in TRANSLATE function, which may perform better than the nested REPLACE calls.
You could also use a general regex CLR function, or implement something custom in SQLCLR for this particular task. See for example SQL Server: Replace with wildcards?
Using the SQL# library, a complete solution would be:
SELECT
    T.SampleID,
    SQL#.RegEx_Replace4k(T.SampleID, '\d+', '*', -1, 1, 'CultureInvariant')
FROM @T AS T;

Full regex support is overkill for this task, so if you are able to use SQLCLR, coding a specific function for your needs would probably be the best performing solution of all.


Inserting Data using Cursor but check if the data already in the table

Is there a specific business reason you must insert each row one by one? Would something like this work?
INSERT INTO SomeTable
(
    Column1
    ,Column2
    ,Column3
)
SELECT  Column1
        ,Column2
        ,Column3
FROM    SomeOtherTable t
WHERE   1 = 1
        AND NOT EXISTS (
                            SELECT  1
                            FROM    SomeTable t2
                            WHERE   1 = 1
                                    AND t.Column1 = t2.Column1
                                    AND t.Column2 = t2.Column2
                                    AND t.Column3 = t2.Column3
                        )


pg_stat_statements causing DB to run out of memory

It seems very surprising that you see an out of memory issue due to pg_stat_statements with the default kept statement count (5000). It makes somewhat more sense with a very high count, especially if you tried to query the view frequently.

One scenario, without going through the pgss code in detail, that I could imagine causing a memory issue, is the query text handling itself (not the statistics tracking), especially in the light of a lot of unique entries.

Given this is RDS we unfortunately can't check the size of the pgss file, but if you are open to trying this again (maybe you can reproduce a similar workload on a staging environment?), I think it would be useful to run a simple "SELECT * FROM pg_stat_statements" and visually look at the entries.

In particular I'd look for very large entries in the "query" field, as well as a lot of seemingly identical queries in pg_stat_statements (some ORMs tend to cause problems like this).

Also I'd recommend posting about this to the Postgres mailing lists - possibly to pgsql-bugs and/or pgsql-performance.

(For context: I built and operate pganalyze where we process a lot of pg_stat_statements data - memory issues like this are very rare)


The query result changes in remote and local database

It might be that there is an unenforced (ie, informational) constraint on the table. Those may cause the optimizer to ignore the data that violates that constraint. Here is a good blog post about that.


ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram') THEN
TO_CHAR(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE 0 END
AS Test1

Your case statement returns both a character (then to_char(...)) and a number (else 0). Try modifying the else condition to return a string:
CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram') THEN TO_CHAR(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE '0' END AS Test1

or the first condition to return a number:
CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram') THEN TO_NUMBER(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE 0 END AS Test1


select json_object() into list postgres function

Have a look at SELECT INTO in Postgres docs.

SELECT INTO: define a new table from the results of a query

Synopsis

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

Examples

Create a new table films_recent consisting of only recent entries from the table films:

SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';

Try the next syntax:

CREATE OR REPLACE FUNCTION fn_get_json()
RETURNS text AS
$$
DECLARE
   ite text;
BEGIN
   select * into ite from json_object('{1}','{1}');
   return ite;
END
$$
LANGUAGE plpgsql;

select * from fn_get_json();

| fn_get_json |
| :---------- |
| {"1" : "1"} |

db<>fiddle here

Or as pointed out in comments a reduced version:

CREATE OR REPLACE FUNCTION fn_get_json()
RETURNS json AS
$$
BEGIN
   return json_object('{1}','{1}');
END
$$
LANGUAGE plpgsql;

Or even simpler with a SQL function:

CREATE OR REPLACE FUNCTION fn_get_json()
RETURNS json AS
$$
   select json_object('{1}','{1}');
$$
LANGUAGE sql;


The features data should really exist in a separate table.  Normalize your data for maintainability and scalability.  For instance, you could implement something like this:

create table Manf
(
    pkManf int identity(1, 1) not null primary key clustered,
    Name varchar(256) not null
)

create table Car
(
    pkCar int identity(1, 1) not null primary key clustered,
    fkManf int not null foreign key references Manf(pkManf),
    Name varchar(256) not null
)

create table Features
(
    pkFeatures int identity(1, 1) not null primary key clustered,
    fkManf int not null foreign key references Manf(pkManf),
    Name varchar(256) not null
)

Just because you currently only have one manufacturer with a feature that needs to be noted in data, that doesn't mean that's always going to be the case.  It is a very easy SELECT to retrieve all of the cars with a particular feature.

EDIT: To get all Cars with a particular feature, you'd do something like this:

select c.*
from Car c
inner join Manf m
on c.fkManf = m.pkManf
inner join Features f
on f.fkManf = m.pkManf
where f.Name = 'some feature'


Get count of intersected rows on the same table

To obtain those users use

SELECT user_id
FROM `table`
WHERE skill_id IN(1,3)
GROUP BY user_id
HAVING 2 = COUNT(*)

If there is no unique constraint for (user_id, skill_id), use COUNT (DISTINCT skill_id) instead of COUNT(*).

To obtain only the count of those users you may use the above query as a subquery:

SELECT COUNT(*)
FROM ( SELECT user_id
       FROM `table`
       WHERE skill_id IN(1,3)
       GROUP BY user_id
       HAVING 2 = COUNT(*)
     ) AS `table`


Locking for truncate and delete is different. As for locking TRUNCATE behave more like 'ALTER TABLE' than DELETE.

From Microsoft documentation:
TRUNCATE TABLE (Transact-SQL)
"When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row".

As you can see, there is a schema lock on the whole table.


How do I add a user that has read access to only ONE database?

I don't know MongoDB too well and I assume you have you seen the information set out on the MongoDB System Users page as there would seem to be one or two differences across different DB mangement procedures.

As a suggestion have you set the database you wish to work on/from by adding the 'use ....' before the command. It would seem that you might try:-

use admin
db.addUser( { user: "Alice",userSource: "testdb",roles: [ "readWrite" ] } )

and see if that makes a difference.


How to configure data collection in SQL Server 2012?

It's a bug.  This Connect ticket has a workaround for it from Kendra Little:

  I was able to work around this issue by temporarily adding a scheduled
  to the Utility Information collection (@collection_set_id=4) with the
  SQL command below. After doing this I was able to complete the data
  collection configuration via the GUI successfully. Afterwards I went
  back and set the Utility Information collection to manual start,
  thereby removing the schedule.

declare @schedule_uid uniqueidentifier
select @schedule_uid = schedule_uid from dbo.syscollector_collection_sets where collection_set_id=3

exec dbo.sp_syscollector_update_collection_set
@collection_set_id=4
, @schedule_uid=@schedule_uid


Database connection lost whenever SQL query string is too long

Woo! I finally figured it out!
The problem was solved by upgrading libmariadb-dev on the Rails application server and rebuilding the mysql2 gem.
Previously, the system was running on a single server with a mariadb-10.1 database. After the switch to a separate database server, the database was on mariadb-10.3 but it was still connecting from the same Rails environment as before, with a mysql2 gem built against 10.1.


Generally speaking, what pecking order should be used when tuning SQL Server?

I like to follow the Performance Tuning by Waits and Queues methodology introduced by SQLCAT team member Tom Davidson.

You can download and read his seminal white paper from here.

SQLCAT is Microsoft's SQL Customer Advisory Team - the folks who deal with the most demanding, largest SQL Server customers and thus have unique insights into some of the biggest installations and how to make them fly.

Basically, SQL Server collects a lot of statistical data on why and what it waits for. Collecting and interpreting that information can lead you to perf tune in the right spot and fixing the right issues, instead of just bumbling around and not knowing where to start and what to do.

Read that whitepaper! It's really very enlightening!

And of course - you'll need to establish a baseline first (e.g. have a set of queries - if ever possible a reasonable and realistic "workload" from your system in production - and measure how they perform) and then you need to tweak something (based on the insights from collecting and interpreting the wait stats) and then you need to measure again to see if that tweak help anything at all - or not.


Online Learning: Different User Roles Different Fields

Use Table Inheritance

If you don't mind nulls, use Single Table Inheritance. Add a "type" column, then add nullable columns for the different types, and check constraints for integrity

https://www.martinfowler.com/eaaCatalog/singleTableInheritance.html

if you do mind nulls, add one table per subtype and join to super type with a foreign key

https://www.martinfowler.com/eaaCatalog/classTableInheritance.html

ORMs like Hibernate can automate this for you


What's a good use case for SELECT * in production code?

I generally abhor SELECT * in production code, and I've been in a situation where its use led to massive amounts of rework later.  Your case does look like a fair use of it though.

The place where I find SELECT * to be a must - and its evil cousin "INSERT INTO tbl" without a column list - is in an archiving situation, where rows are being moved to another table that must have the same structure.

INSERT INTO SalesOrderArchive  -- Note no column list
SELECT *
  FROM SalesOrder
 WHERE OrderDate < @OneYearAgo

DELETE FROM SalesOrder
 WHERE OrderDate < @OneYearAgo

If a new column is added to SalesOrder in the future, but not to SalesOrderArchive, the INSERT will fail.  Which sounds bad, but it's actually a really good thing!  Because the alternative is much worse.  If all the columns were listed on the INSERT and the SELECT, then the INSERT would succeed, and so would the following DELETE (which is effectively "DELETE *").  Production code that succeeds doesn't get any attention, and it may be a long time before someone notices that the new column is not being archived, but being silently deleted altogether.


These times where your connection is forced closed is usually an indication of throttling. I have found that the main reason for throttling is from high numbers of outstanding requests within your database. Unfortunately there are no hard limits that can be monitored to identify when this throttling occurs. The good news is that the lower limits have been documented which means you can track when you are getting close to the upper limits.
To give a plug to our Cotega monitoring service, this is one of the main things we track to allow people to be notified when they approach these upper limits. Another useful thing to do when this happens is to do a deep database diagnostics to see what expensive queries, connections, etc are occurring within your database as described on How to Perform SQL Azure Performance & Diagnostics Analysis for SQL Azure.


Associate a piece of data with a session

Use SET CONTEXT_INFO.

The most common use case for this is to send triggers information about the actor that caused the trigger to fire.

For example:

CREATE PROCEDURE [dbo].[ModifyData]
AS
BEGIN
    DECLARE @ObjectID BINARY(4) = CAST(@@PROCID AS BINARY(4));
    SET CONTEXT_INFO @ObjectID;

    UPDATE [dbo].[Data]
    ...
END;
GO

CREATE TRIGGER [LogModifications]
ON [dbo].[Data]
FOR UPDATE
AS
BEGIN
    DECLARE @ModifiedByObjectID INT = CAST(SUBSTRING(CONTEXT_INFO(), 1, 4) AS INT);

    ...
END;
GO


Extract all unique chars from all texts across the table

MySQL has no way to split a cell into a bunch of values.  (Yeah, it can split a piece of a cell off -- see LEFT(), etc.  But not an arbitrary set.)

You would be better off doing the task in PHP or some other language -- more powerful than SQL.  (Yeah, it could be done in a Stored Procedure, the the code would be really messy.)

Here is some pseudo code for PHP (etc):

declare $hash as an associative array
foreach row of SELECT * FROM tbl
    foreach cell
        split cell into characters
            $hash[$char] = 1
dump the _keys_ of $hash

(Getting the counts is a trivial enhancement.)

Be sure to work in "characters", not "bytes".  The details of that depend on the language.


In MongoDB, documents stored in a collection require a unique _id field that acts as a primary key. If an _id does not exist in the document, MongoDB will create an ObjectID field. Generally, _id can itself be a complex document, any BSON data type (still, it must be unique). ObjectID() is described Here.

As MongoDB internally to store documents in BSON format. BSON extends the JSON model to provide additional data types and to be efficient for encoding and decoding within different languages. MongoDB BSON implementation supports embedding objects and arrays within other objects and arrays – MongoDB can even reach inside BSON objects to build indexes and match objects against query expressions on both top-level and nested BSON keys.

As MongoDB documented Here Data in MongoDB has a flexible schema. Collections do not enforce document structure. Decisions that affect how you model data can affect application performance and database capacity.

This document describes a data model that uses embedded documents to describe relationships between connected data.


Create Trigger MySql update or insert in another table

First, you need to specified when do you want to run the trigger inside the table. I've edited your trigger and use AFTER UPDATE and AFTER INSERT:

AFTER INSERT:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAI$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAI` AFTER INSERT ON `TestTable` FOR EACH ROW
    BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM TestTable2 WHERE (ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date)));
    IF @COUNT=0 THEN
        INSERT INTO TestTable2 (ItemId,Year,Month,Open,Close,Increase ) VALUES
        (NEW.ItemId , YEAR(NEW.Date), MONTH(NEW.Date),NEW.Value,NEW.Close,0.0);
    ELSE
        UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT
        Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
        WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));
    END IF;
    END;
$$
DELIMITER ;

AFTER UPDATE:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAU$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAU` AFTER UPDATE ON `TestTable` FOR EACH ROW
BEGIN

    UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT
    Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
    WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));

END;
$$
DELIMITER ;

Note: Be careful about the SELECT Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)), I've added a LIMIT 1 to avoid duplicate rows.

Change the database TEST.

You can also use ON DUPLICATE KEY on the two triggers execution.

You can get more information in the MySQL's documentation manual.


Find rows containing all keywords within a string across multiple columns

You can use a string splitter and do this. I use Jeff Moden's splitter but you can use your favorite. This essentially splits the string on the space, and then does a FULL JOIN and only returns rows where there is a mach for each word. This is handy since it doesn't care the order of the words in the string. i.e. Foo Bar is the same as Bar Foo.

SQL FIDDLE

declare @table table ([Row] int identity (1,1), Col1 char(3), Col2 varchar(6), Col3 varchar(6))
insert into @table
values

('Foo','Bar','Stack'),
('Foo','Blitz','Stack'),
('Foo','Foo','Bar')

declare @search varchar(64)

set @search = 'Foo Bar'

select distinct
    t.*
from @table t
full join
    dbo.DelimitedSplit8K(@search,' ') s on
    s.Item = t.Col1
    or s.Item = t.Col2
    or s.Item = t.Col3
where
    t.Row is not null
group by
    t.Row
    ,t.Col1
    ,t.Col2
    ,t.Col3
having
    count(*) = (select max(ItemNumber) from dbo.DelimitedSplit8K(@search,' '))


You can combine the two queries:
with t as (
  select *
  from the_table
  where id > 5
  order by id limit 1
)
select *
from t
union all
select *
from the_table
where id = 1
and not exists (select *
                from t);

Another option is to use an OR condition:
select *
from the_table
where (id > 5  or id = 1)
order by id = 1, id
limit 1

The order by id = 1 will sort the row with id = 1 at the end and all others (where that condition is false) will be sorted by the actual id value. The disadvantage is that you need to repeat the search condition
Typically OR conditions aren't optimized well, so the first alternative might be faster.


Your query is interpreted as

... SET access_token =
        ('l38r8cfjm31b38ye91yjn1956vr3n74tc5sc0btholj0iain77'
         and uuid='123456789')

That is, AND is treated as a logical operator, and the string constant is interpreted as a boolean literal, which causes the error.

Replace AND with , like the comment suggests.


SQL Server 2005 merge replication failure

Check if you have any conflicts between the subscriber and the publisher and try to resolve these conflicts.


Store and search leveled categories with mysql json

I chose to go with JSON since item data that blacksmith produces is just informative, and does not have any specific value other than finding the specific blacksmith by his skills

I find this paradoxical- searching by a multi-valued field IS one of the reasons to apply normalization, as it simplifies a lot the searches. You can do what you mention, but without extra information that justifies that decision, that is not the simplest approach from a pure relationship model. Because you say:

  Please suggest your own structures you think would be most effective.

I will first suggest how I would do it:

Normalized schema

CREATE TABLE blacksmiths (
  blacksmith_id int unsigned auto_increment PRIMARY KEY,
  blacksmith_name varchar(300) NOT NULL,
  blacksmith_lvl int unsigned NOT NULL
);

CREATE TABLE items (
  item_id int unsigned auto_increment PRIMARY KEY,
  item_name varchar(300) NOT NULL, # or level1, level2, level3?
)

CREATE TABLE blacksmith_items (
  item_id int unsigned,
  blacksmith_id int unsigned,
  level unsigned int,
  PRIMARY KEY(item_id, blacksmith_id),
  KEY(blacksmith_id),
  CONSTRAINT item_id_fk  (item_id) REFERENCES items (item_id),
  CONSTRAINT blacksmith_id_fk  (blacksmith_id) REFERENCES blacksmiths (blacksmith_id),
);

I don't get exactly how levels come into play- if it is a property of blacksmiths or items, but you can modify the join table to make it part of the primary key or not, and/or modify the indexes to search them faster. Or maybe the items columns have to be separated into metal, name and type, etc.

To get all items for blacksmith id1:

SELECT items.name FROM blacksmiths
JOIN blacksmith_items
USING (blacksmith_id)
JOIN items
USING (item_id)
WHERE blacksmiths.blacksmith_id = 1;

To get items with a specific level;

SELECT items.name FROM blacksmiths
JOIN blacksmith_items
USING (blacksmith_id)
JOIN items
USING (item_id)
WHERE blacksmiths.blacksmith_id = 1
AND blacksmith_items.level = 2;

Using joins is not really that complicated, and it will allow you more flexibility when you change the number of levels, or the number of items, and you can generate a json output from your application code.


Alternative to FK for sys.objects reference

You could use a trigger on the log table that rolls back when the object_id being inserted is not found in sys.objects - since triggers are the typical mechanism we use when a foreign key doesn't make sense or isn't possible (like maintaining integrity across database or instance boundaries).
But do you really want to lose all of the data because one column represents an object that no longer exists, or never existed? (Maybe that's ok; I don't what you're auditing/logging.) What is the likelihood of this happening since the trigger produces the object_id, and the system isn't known for messing up?
I'm not sure I understand the point anyway; while a foreign key to sys.objects would prevent someone from logging information about an object that doesn't exist, the presence of just one row in the logging table would make it impossible to ever drop that object. (Maybe that's ok too, but it doesn't sound wise.)
If the fear is that someday a user reviewing log history will hit a dead end because the object has since been dropped, a trigger that made sure the object existed when the row was inserted isn't going to have saved you from that scenario anyway. You'd need a DDL trigger maybe, on DROP_TABLE, so that either the log history associated with that table is purged, or the object_id is moved somewhere else so that you can still track down what that table used to be.


Is there a performance difference between a Dropped Index and a Disabled Index, for a NON-Clustered Index

As documented:

The query optimizer does not consider the disabled index when creating query execution plans. Also, queries that reference the disabled index with a table hint fail.

The index is completely ignored for querying

The index is not maintained while it is disabled.

So it doesn't appear in inserts, updates and deletes.

You cannot create an index that uses the same name as an existing disabled index.

Not documented, but you cannot drop columns that the index depends on.


Why is SQL Server 2014 consuming so much RAM with no active data stored?

First thing is to change the max memory setting on your instance using either the GUI or sp_configure.

EXEC sp_configure 'max server memory (MB)', 1024

Then go into SQL Server Configuration Manager and stop and re-start the instance.  You don't actually have to restart your laptop.

That will fix it so that your instance won't use much more than 1gb.  I'm fairly sure it uses memory that isn't included in this setting but not a huge amount.

SQL Server likes to take memory for what it needs.  It isn't so good at giving it back so it's a good idea to keep the max memory setting on your personal laptop fairly low unless you happen to need more.  I use the 1gb setting for random small queries etc and if I need more I update it.  Of course my laptop only has 6gb of ram total.


Express Edition [XE] is available for usage by small businesses.  You get no upgrades or patches.  Support is only available by internet like Google and DBA SE.  You are limited to 1 CPU Core, 1 GB of RAM, and 11GB of user data.

Current version of XE is 11.2.0.2.  Version 18 XE is due out soon (October 2018).  I've read Oracle will then release newer versions on an annual basis.  Max user data is increased to 12GB of data.  But, "Adv. Compression" is suppose to be included (increases user data to est. 40GB).  This information comes from a post by the Project Manager for XE on the Oracle community forum for XE.

For personal learning, you can use any edition.

For your classes, I would probably use XE for the classroom but mention VirtualBox.  Oracle has a prebuilt VirtualBox VM for learning. A small list can be found here.

Additionally, http://LiveSQL.oracle.com provides a free "test stuff out" similar to "db fidle".  (I would not use that for classroom)

More details (with respect to licensing) should be referred to your local friendly Oracle sales representatives.


Why "Select * into targettable from sourcetable “ is faster than “insert into targettable select * from sourcetable

A couple of ideas/theories:

SELECT INTO... lets the RDBMS determine sort order based on order of your original table.  If you insert into an existing table, there may be a sort needed to match a clustered or nonclustered index(es).

No Indexes - when you SELECT INTO... the RDBMS knows for certain there are no pre-existing indexes to update.

No Contention - since the table you are inserting into does not exist, SQL Server doesn't need to worry about row-level locking or contention handling.  Nothing else can reference the table you create since it doesn't exist.

All that being said, there are other ways to insert into a table very quickly.

Make sure your clustered index keys match when possible.  This means there is no on-the-fly sorting
Disable all non-clustered indexes.  Self-explanatory.
Set recovery mode to simple and trace flag 610 to ON.  Use the TABLOCK hint on your target table and NOLOCK hint on your source table.

For example, assume tablea and tableb have the same clustered index:

INSERT INTO TableB WITH (TABLOCK)
SELECT <Columns>
FROM TableA WITH (NOLOCK)

In my experience this is faster than using SELECT INTO... and then creating the clustered index afterwards.  Please note this can also work on a table that already has data in it which is a much more useful scenario.

EDIT:

Here's a fantastically detailed whitepaper from MS for data load performance in Sql Server 2008.


Never a simple answer...

For a direct DELETE, a user in both roles won't be able to DELETEDENY always has precedence when permissions are checked

For indirect via a stored procedure, the permissions may not be checked if both table and proc have the same owner. So both GRANT and DENY will be ignored. This is called "ownership chaining"

Personally, I don't really use DENY. Here's why:

In your case, you only need to GRANT DELETE to the Admin role. The Basic role needs neither DENY nor GRANT (but run REVOKE DELETE to remove the Basic DELETE permission)

The lack of a DELETE permission (by REVOKE) means a DELETE will fail for a Basic only user. DENY isn't needed to prevent this.
An Admin user (whether in both roles or not) has a GRANT so the DELETE will succeed. No DENY to block them