SQL - Many-to-Many relationship with same table and with relationship constraint

If I understand your scenario description correctly, you want to manage two different facts, so you should have (a) one table to store product data that is independent of sellers and (b) another table to retain product information that comes into play in the context of each relationship between products and their corresponding sellers.
1. Logical model
I have depicted such approach in the logical IDEF1X[1] data model shown in Figure 1 and you can download it from Dropbox as a PDF.

1.1 Product
As you can see in the referred model, you may use the Product entity type (or table at the implementation level) to store the default (or base) values of the product attributes. It can be useful to think about this entity as some kind of catalogue. Here is where you can keep the product data that does not depend on sellers.
I have denoted the Product PK as ProductNumber for expository porposes but, possibly, the use of some sort of ProductCode or another similar term might be more suitable or advantageous in your business domain.
1.2 ProductSeller
Then, I have set up a PRIMARY KEY (PK) in the ProductSeller associative entity which is composed of SellerId and ProductNumber, and these attributes are, in turn, FOREIGN KEY references to Party.PartyId and Product.ProductNumber, respectively. This is the place where you could store product names, descriptions and prices as defined by every specific seller.
1.3 Party
I decided to include the Party entity in case you have to store sellers that are either an Organization or a Person. These three entities are encompassed in a exclusive Supertype-Subtype relationship, so this post could be pertinent.
1.4 Implementation considerations
It is important to note that I indicated some attributes as ALTERNATE KEYS (AK plus its fitting number), since this implies that, when they become table columns, they should be set with a UNIQUE CONSTRAINT or UNIQUE INDEX.
As in any relational database development, you should seriously consider using ACID Transactions in order to guard your data integrity and consistency.
2. Simplified logical model
If in your case it is only possible for an Organization to become a Seller, then you can employ a structure like the one I propose in the logical model presented in Figure 2, and it is also downloadable from Dropbox as a PDF.

With this model, you just have to migrate[2] the OrganizationId PK from Organization to SellerProduct, and then assign it a role name[3] like SellerId in order to make it more meaningful or, maybe, leave it as OrganizationId if you feel more comfortable. After that, you migrate the Product.ProductNumber to complete the composition of the SellerProduct PK.
3. Data entry process
Having discussed the most relevant parts of the two suggested structures, I esteem opportune addressing the process that should take place when the data entry is being carried out. In this respect —with the aid of an application program that communicates with your database— your properly authorized users can:

Inspect carefully the product information detailed by one certain seller.
Compare it with the rows already contained in the Product table.
If they define that such product instance has not been entered yet and therefore it is not provided by any seller, then they (1.1) INSERT a new Product row with the default or base values and also (1.2) a new row in the ProductSeller table including the suitable SellerId (drawn from Party.PartyId or Organization.OrganizationId) and the ProductNumber just created along with the remaining column values holding the product data as described by that seller in particular.
If, on the contrary, they determine that the product under inspection is already stored in the catalogue and, perhaps, provided by one or more distinct sellers too, then they simply (1) INSERT a new row in the ProductSeller table including the corresponding SellerId and ProductNumber (taken from Product.ProductNumber) values toghether with the complementary column values that contain the product information as designated by the seller at hand.

So, as explained above, the ProductSeller.ProductNumber and ProductSeller.SellerId would be very useful to cover the following specification:

Admin users will be required to identify that products are the same across various sellers.

Since having a given ProductNumber value referenced from multiple ProductSeller rows will assist your database users in the identification and retrieval of those products that are offered by different sellers, who will be recognized by their distinctive SellerId value which, of course, points to a precise Party.PartyId (or Organization.OrganizationId).
4. Tracking Product data modifications
4.1 Product History and ProductSeller History
Since it seems reasonable to assume that product data can suffer modifications over time (and, probably, most of the time these modifications will be related to product prices), I deem necessary the inclusion of a mechanism to track these changes. This is the reason for which I incorporated the entity types denominated ProductHistory and ProductSellerHistory in the above presented logical model.
In this respect, you may find this post about temporal databases and this post about data versioning of relevance.
4.2 Handling ProductPrice as a time series
You might be face the need to follow the trail of time-related product modifications exclusively when they concern to prices, so you could alternatively make use of the model depicted in Figure 3, which you can download from Dropbox as a PDF, as well.

In this way, let us take the Product and ProductPrice entity types as references. As demonstrated in this alternative model, I have moved the DefaultPrice attribute to a new entity, and this new entity has a PK made up of ProductNumber (also playing the role of a FK that makes a reference to Product) and CreatedDateTime (that would mark the exact point in time in which a determined default price was inserted).
Consequently, every time that a default price value needs to be put up-to-date, your database users only have to INSERT a new ProductPrice row, containing the suitable ProductNumber taken from Product and the new price value. You may use a server function so that you can retrieve and store the exact insertion instant, i.e. CreatedDateTime, in a reliable manner.
The current or effective version of a product default or base price occurrence would be the row holding the MAX(ProductPrice.CreatedDateTime) for a definite ProductNumber,  and all the other rows (when they exist) matching the same ProductNumber would be the previous DefaultPrice versions.
You can then employ a comparable approach to handle the changes related to the ProductSeller price datum.

Notes
1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical papers authored by the originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship theory, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown. It is worth noting that this techinque was formalized by way of first-order logic.
2. IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.
3. The use of role names is recommended since 1970 by Dr. Codd in his seminal paper entitled “A Relational Model of Data for Large Shared Data Banks”. For its part, IDEF1X —keeping fidelity regards relational practices— also advocates role naming.


check if found specific record then find another record with the same group

try this

SELECT p.TERM, p.Problem_ID,p.problem, PGROUP
FROM Problem_log p
INNER JOIN problem AS s
ON s.problem_id = p.problem_id
WHERE s.PGROUP IN
(
    SELECT PGROUP
    FROM Problem_log
    INNER JOIN problem
    ON Problem_log.problem_id = problem.problem_id
    WHERE Problem_log.PROBLEM_ID = 3
)


How to install SQL Server 2008 R2 Profiler

if you allready have a setup of MS SQL Server 2008 R2, try to uninstall the tools only:

setup.exe /ACTION=UNINSTALL /FEATURES=TOOLS /QUIETSIMPLE

And the install it again:

setup.exe /ACTION=INSTALL /FEATURES=TOOLS /QUIETSIMPLE /IAcceptSQLServerLicenseTerms


Is it possible to catch the value of any field in a table? and once catched, can it be inserted into another table? SQL

The GO utility statement marks the end of a batch of T-SQL statements.

The variables you declared at the top of the first batch go out of scope after the first GO statement.  So you can't use them anymore in the 2nd batch (where the INSERT statement is).

To solve that, just remove the GO statement.

As far as your other goal, you can store the Id that goes with that UserName by changing your SELECT statement to this:

SELECT @tempUSERID = Id
FROM Users
WHERE UserName = @tempUSERNAME;

Notice the variable assignment in the SELECT clause.


Is there a way to make queries from a database to use only certain core on the host?

One way is to use query hint OPTION (MAXDOP 1).

The problem with above apporach is that you have to hint everything that you don’t want to be limited by the server-wide setting, or use 0 (i.e. unlimited) for the server-wide setting and hint everything that you do want to be limited.

  Can we make queries from 6 "less" important databases A1-A6 to use only one core (always the same core 0) , and leave queries from other 4 "more" important databases A7-A10 to use rest of the cores (in this case 1-3).

A better way is to use Resoruce Governor. It is available only in Enterprise Edition (Dev edition as well).

Note: Make sure you do proper testing before implementing directly in PROD as I have seen really bad cases where without understanding, Resource Governor is implemented and things go horribly wrong.

If you are running on Enterprise Edition then you can create

CREATE RESOURCE POOL -- here you can specify min and max CPU allowance. Also by using AFFINITY SCHEDULER, you can bind specific schedulers to your Pool.
CREATE WORKLOAD GROUP USING RESOURCE POOL. -- Here you can specify max_dop=1.
This MAXDOP setting overrides the server-wide setting and any query hints.

Refer to : Resource Governor in SQL Server 2012 whitepaper for more details.

Aaron Bertrand has explained very nicely at : SQL Server 2012 Resource Governor Enhancement


How can I create new login in SSMS 2008?

Open the Window again to create a new login.  Use the "Search" button to find the Windows login you wish to create an account for.  Then you can click the "Script To" button at the top and get the actual T/SQL Script.  It should look something like

CREATE LOGIN [KAWMIN-PC\Classic .NET AppPool] FROM WINDOWS

The UI doesn't always work correct for creating logins with spaces or other reserved characters.

Once you have the T/SQL script correct any issues and run the script and the login should be created.  If there is an error post the error message to the site.


reverse the characters of a string without changing the position of the numeric value in T-SQL

I am not proud of this, but you can do it in T-SQL. This inline table-valued function breaks the string into a set of characters, and applies row numbers to just the string values, so that you can flip the set upside down.

CREATE FUNCTION dbo.SortString
(
  @s varchar(64)
)
RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN
  ( -- get a sequence number for every character in @s
    WITH n AS
    (
      SELECT n = 1 UNION ALL SELECT n + 1 FROM n WHERE n < LEN(@s)
    ),
    s AS
    ( -- break out each character, apply sequence number, test numeric
      SELECT n, s = SUBSTRING(@s, n, 1), isn = ISNUMERIC(SUBSTRING(@s, n, 1))
      FROM n
    ),
    s2 AS
    ( -- apply reverse match pointers, but only for strings
      SELECT n,s,
        rn1 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER
              (PARTITION BY isn ORDER BY n ASC) END,
        rn2 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER
              (PARTITION BY isn ORDER BY n DESC) END
      FROM s
    )
    SELECT s2.n, New = COALESCE(s3.s, s2.s), Original = s2.s
        FROM s2 LEFT OUTER JOIN s2 AS s3
        ON s2.rn2 = s3.rn1
  );
GO

The following call:

DECLARE @str varchar(64) = 'abc223de11';
SELECT Original, New FROM dbo.SortString(@str) ORDER BY n;

Yields the following results:

Original  New
--------  ---
a         e
b         d
c         c
2         2
2         2
3         3
d         b
e         a
1         1
1         1

In SQL Server 2017, you can pack the string back together with predictable order using STRING_AGG():

DECLARE @str varchar(64) = 'abc223de11';

SELECT
  OriginalString = @str,
  QuasiReversed = STRING_AGG(New,'') WITHIN GROUP (ORDER BY n)
FROM dbo.SortString(@str);

In older versions, you'll need to use a FOR XML hack:

DECLARE @str varchar(64) = 'abc223de11';

SELECT
  OriginalString = @str,
  QuasiReversed = (SELECT '' + New
    FROM dbo.SortString(@str)
    ORDER BY n
    FOR XML PATH, TYPE).value(N'.[1]','varchar(64)');

Results in both cases:

OriginalString  QuasiReversed
--------------  -------------
abc223de11      edc223ba11


Get the Messages stream in an SSRS RDL Report

Removing what I had previously answered, since it was way off. Based on your comment regarding PRINT statements and getting the rows affected messages...

The rows affected message is something SSMS returns, you would generally not see that with datasets in SSRS that I know of.

I did some testing with PRINT statements and in SSDT-BI for SQL Server 2012 the statement is ignored and not returned in my case. As well, there are only so many places that you can actually put a tooltip so you might need to expand a bit more on how and where you want to use this information.

With regards to your specific report if you are having issues with a report rendering only 1500 rows there is no real way for us to assist in that unless you share it more about it, or the actual RDL file.


Availability Group Listener and old client software

In your question, you included the following check to validate that you've set the RegisterAllProvidersIP to false:
PS C:\> get-clusterresource "Cluster Name" | Get-ClusterParameter RegisterAllProvidersIP

Object       Name                   Value Type
------       ----                   ----- ----
Cluster Name RegisterAllProvidersIP 0     UInt32

If you've used the Cluster Name then you have set the RegisterAllProvidersIP parameter on the wrong object.
There are several similarly named objects in your screenshot:

The Windows Cluster: WC01
The Availability Group: AG01
The AG Listener: AGListener01

To validate how the setting is configure, you should use the listener name not the cluster or AG name:
PS C:\> Get-ClusterResource AGListener01 | Get-ClusterParameter RegisterAllProvidersIP

Or, to do this a bit more stepwise, you can do Get-ClusterResource and you'll see something like this:
Name                         State   OwnerGroup     ResourceType
----                         -----   ----------     ------------
10.10.10.199                 Online  Cluster Group  IP Address
Cluster IP Address           Online  Cluster Group  IP Address
Cluster Name                 Online  Cluster Group  Network Name
AG01                         Online  AG01           SQL Server Availability Group
AG01_10.10.20.138            Offline AG01           IP Address
AGListener01                 Online  AG01           Network Name
File Share Witness           Online  Cluster Group  File Share Witness
IP Address 10.10.10.138      Online  AG01           IP Address

Out of all of these things, we only care about the objects that belong to the OwnerGroup for the AG. We can filter a bit further with Get-ClusterResource | Where OwnerGroup -eq AG01:
Name                        State   OwnerGroup   ResourceType
----                        -----   ----------   ------------
AG01                        Online  AG01         SQL Server Availability Group
AG01_10.10.20.138           Offline AG01         IP Address
AGListener01                Online  AG01         Network Name
IP Address 10.10.10.138     Online  AG01         IP Address

These 4 items represent the resources that make up my AG: The AG itself, the listener (listed as "Network Name"), and the two IP addresses, each in different subnets.
The item that I want to check for the RegisterAllProvidersIP parameter is the listener  (Network name) resource: Get-ClusterResource AGListener01 | Get-ClusterParameter will give me the full list of parameters on the Listener object:
Object        Name                   Value                            Type
------        ----                   -----                            ----
AGListener01  Name                   AGListener01                     String
AGListener01  DnsName                AGListener01                     String
AGListener01  Aliases                                                 String
AGListener01  RemapPipeNames         1                                UInt32
AGListener01  HostRecordTTL          1200                             UInt32
AGListener01  RegisterAllProvidersIP 1                                UInt32
AGListener01  PublishPTRRecords      0                                UInt32
AGListener01  ResourceData           {1, 0, 0, 0...}                  ByteArray
AGListener01  StatusNetBIOS          0                                UInt32
AGListener01  StatusDNS              0                                UInt32
AGListener01  StatusKerberos         0                                UInt32
AGListener01  CreatingDC             \\dc01.am2.co                    String
AGListener01  LastDNSUpdateTime      9/29/2020 3:57:12 PM             DateTime
AGListener01  ObjectGUID             abc1234def567890ab12cd3456ef7890 String
AGListener01  DnsSuffix              am2.co                           String
AGListener01  ADAware                1                                UInt32

And I can get the specific parameter by explicitly asking for it Get-ClusterResource AGListener01 | Get-ClusterParameter RegisterAllProvidersIP
Object        Name                   Value  Type
------        ----                   -----  ----
AGListener01  RegisterAllProvidersIP 1      UInt32

If this is not set to 0, then you would need to use the block of code outlined in the docs, and be sure you're using the Listener name, and not the Cluster name. The example also recommends setting the TTL as well (picking an exact number is up to you, though the docs suggest 300 seconds). Note that offlining & onlining the Listener is necessary for the new settings to be picked up by the DNS name:
Get-ClusterResource AGListener01 | Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource AGListener01 | Set-ClusterParameter HostRecordTTL 300
Stop-ClusterResource AGListener01
Start-ClusterResource AGListener01

After this is done (and the old TTL is expired), you'll be able to see the Lister has only a single IP address configured:
Resolve-DnsName AGListener01


Dropping then adding a table to a subscription with Identity option Not for Replication causes errors

I have found a solution though adding:

@identityrangemanagementoption = N'manual'

when I re-add the article using sp_addarticle, means that the automatically created sps used to replicate the data don't try and update the identity column and thus are created succesfully on the subscriber rather than raising the error.


what is difference between database developer and administrator?

At a masters level you should be pretty good at both

Administrator - DBA

install and configure hardware
maybe you say that is outside of DBA but most DBA can do hardware
install and configure database software
optimize the database configuration
users, groups, and security
backups
index maintenance
monitor
need basic SQL just to query systems tables and validate data
build tables and indexes

Developer

database design
write, queries, procedures ....
index planning and implementation
build tables
performance monitoring

Mix

if you get into partitions and other exotics the database design gets into hardware
index maintenance - if the DBA is going to do this need to understand indexes
data load - is this a DBA or developer?
if it is the DBA then need a lot of developer skills

At a master level should know at least one language / platform.  If you load data there going to be times you need to scrub it row by row.

In a large software shop you may have a developer on a programming team that really only does pure development.   Lets say you are running a packaged application like SAP you would have a DBA that only does DBA and not even have a developer.

Also have application developers that are also database developers.  For big high volume applications the back end developer(s) need to be part database developers.  For small apps that cannot afford a database developer the application developer has no choice but to do it all.

If you are a DBA expect to do some mail, web, group policy, and other system tasks at some point in you career.


Splitting the table to get more clusterization options - will it help?

Since this is a theoretical question I'm sure there will be corner cases where that is a good idea but as a general rule I wouldn't go down that path.

You will, as you state, complicate your regular queries by having to join both tables and you will also complicate your inserts/updates.

A well designed, properly normalized, database with proper indexing on a server that has been sized well should perform adequate with a couple of million rows (and a lot more than that).

For reference, the stack exchange network runs on a mainstream RDBMS and uses no special design tricks. You can verify that for yourself by downloading the database.


MySQL deleting lots of rows by id - where in or loop over each row in transaction

Where did you get the list of ids?  If you got them from another table, then do a multi-table DELETE.  This would be best, except that it stills suffers from #1, below.

A single delete with 100K rows will take a long time because that is an awfully large number of things to save for possible ROLLBACK.
Deleting one row at a time is awfully slow if each is a separate transaction (autocommit=ON).
Deleting individual rows in a huge transaction has the same ROLLBACK issue, plus the overhead of doing separate commands (extra parsing, roundtrips, etc).

Here's another thought:  Use PHP, but gather 1000 ids at a time.  That is:

SET autocommit=1;
DELETE FROM tablename WHERE id IN (... 1000 ids ...);
DELETE FROM tablename WHERE id IN (... 1000 ids ...);
...

If convenient, sort the 100K ids before starting this; that will help the "locality" a bit.  This breaking up of the ids softens the negative issues mentioned above.

Not quite the same: Blog on Big Deletes , but it might give you some more insight and ideas.  (It discusses, among other things, the impact on Replication, and a benefit of using PARTITION.)


db2 SQL0930N error on linux - not enough space to process this statement

Check db2diag.log around the time of the error -- you will see what memory pool is exhausted or misconfigured.


PostgreSQL equivalent query which has order by clause alone

The equivalent is simply:

SELECT sum(COLUMN1),sum(COLUMN2)
FROM Table_name;

As the order by is useless in any rdbms.


Visual Studio Code Postgres SQL Connection

As per documentation here Npgsql has a Visual Studio extension (VSIX) which integrates PostgreSQL access into Visual Studio. It allows connecting to PostgreSQL from within Visual Studio's Server Explorer, create an Entity Framework 6 model from an existing database, etc. The extension can be installed directly from the Visual Studio Marketplace page.

The VSIX doesn't automatically add Npgsql to your GAC, App.config, machines.config or any other project or system-wide resource. It only allows accessing PostgreSQL from Visual Studio itself.

As here you van find out, How to Connect Visual Studio LightSwitch to PostgreSQL with dotConnect for PostgreSQL Data Provider.

As per postgresql documentation here  Could not translate host name localhost, service 5432 to address: nodename nor servname provided, or not known.

Usually this error is caused by broken /etc/hosts file. The problem could be a missing localhost entry, syntax errors or incorrect whitespace.

For reference, here is what this file should look like by default on macOS:

##
# Host Database
#
# localhost is used to configure the loopback interface
# when the system is booting.  Do not change this entry.
##
127.0.0.1   localhost
255.255.255.255 broadcasthost
::1             localhost

For further you ref here, here, here and here


How to keep database size low in MongoDB?

Because data is not normalised the storage requirements generally bigger.

Don't forget about compression. When you use gzip it will use more CPU power, but disk size is much smaller than snappy. It depends on your data.

Some MongoDB consultant made benchmarks about different compression algorithms, but this file not public. Maybe some MongoDB guy provide an improved answer ;)

No compression
Snappy (enabled by default) – very good compression, efficient use of
resources
zlib (similar to gzip) – excellent compression, but more resource
intensive

see the startup parameter storage.wiredTiger.collectionConfig.blockCompressor

  Default: snappy

  New in version 3.0.0.

  The default type of compression to use to compress collection data.
  You can override this on a per-collection basis when creating
  collections.

  Available compressors are:

  none snappy zlib storage.wiredTiger.collectionConfig.blockCompressor
  affects all collections created. If you change the value of
  storage.wiredTiger.collectionConfig.blockCompressor on an existing
  MongoDB deployment, all new collections will use the specified
  compressor. Existing collections will continue to use the compressor
  specified when they were created, or the default compressor at that
  time.


How to remove earliest/latest row by two columns (userId, StatusCheck)?

To answer this question, I did the following.

CREATE TABLE shift (worker_name VARCHAR(25), worker_id INTEGER, shift_time TIMESTAMP, in_out VARCHAR(3));

INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 07:58:06.000', 'In');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 07:58:15.000', 'In');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 07:58:38.000', 'In');
INSERT INTO shift VALUES ('Kate B',     4, '2018-05-04 14:15:45.000', 'In');
INSERT INTO shift VALUES ('Kate B',     4, '2018-05-04 14:15:46.000', 'In');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:46.000', 'Out');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:48.000', 'Out');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:49.000', 'Out');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:50.000', 'Out');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:51.000', 'Out');
INSERT INTO shift VALUES ('Patty',      5, '2018-05-04 20:31:36.000', 'Out');

I then ran the following query:

SELECT worker_name, MIN(shift_time), in_out
FROM shift
WHERE in_out = 'In'
GROUP BY worker_name, in_out
UNION
SELECT worker_name, MAX(shift_time), in_out
FROM shift
WHERE in_out = 'Out'
GROUP BY worker_name, in_out
ORDER BY worker_name

Which gives:

worker_name,         shift_time,   in_out
Kate B       2018-05-04 14:15:45       In
Patty        2018-05-04 20:31:36      Out
Zeab Simon   2018-05-04 07:58:06       In
Zeab Simon   2018-05-04 16:34:51      Out

A cursory inspection reveals that these are the correct times - earliest clock_in and latest clock_out. A db-fiddle is available here. To get the best answers to your questions, you might consider giving a fiddle in the question itself - help  us to help you!

Following the OP's clarifications in comments the need to delete extraneous records from the same table, I used the result above in a CTE (Common Table Expression - AKA the WITH clause) to delete the other records from the main shift table as follows.

WITH cte AS
(
  SELECT worker_name, worker_id,
  MIN(shift_time) as shift_time, in_out
  FROM shift
  WHERE in_out = 'In'
  GROUP BY worker_name, worker_id, in_out
  UNION
  SELECT worker_name, worker_id,
  MAX(shift_time) as shift_time, in_out
  FROM shift WHERE in_out = 'Out' GROUP
  BY worker_name, worker_id, in_out
)
-- select * from cte  -- testing
DELETE FROM shift
WHERE shift_time NOT IN
(
  SELECT shift_time FROM cte
);

And then a simple SELECT in the fiddle shows that the correct result has been achieved.

Another solution was kindly provided by @ypercube here - worth looking at for learning purposes. It is actually (no surprise here) more correct. My SQL will not work properly if there are exactly matching times for a MAX(or MIN) and a non significant value!


500M for tmp_table_size is really dangerous, but not for the reason you might think.

tmp_table_size and max_heap_table_size work together.  When a SELECT needs to store intermediate results for, say, a sort (ORDER BY), it first tries to do it in RAM.  There are several cases where this won't work:

A TEXT or BLOB column is being fetched.  It will bypass RAM and go straight to disk.  (tmp_table_size is irrelevant in this case.)
It starts to use RAM but the temp table grows bigger than either of those two settings.  At this point, it copies what it has so far to disk.  (Having tmp_table_size big will actually hurt for large temp tables.)

If you have several SELECTs running at the same time, and doing the second bullet item above, that is potentially several allocations of 500M.  If you run out of RAM, the system will start swapping.  Swapping is much worse than spilling tmp tables to disk sooner.

Note further, that a single SELECT can cause more than one temp table to be used!  One example is GROUP BY something ORDER BY some_other_thing

Bottom Line: Set tmp_table_size and max_heap_table_size to no more than 1% of RAM.

Now, back to 50% of tmp tables being on disk.  Sometimes that is necessary.  But there are some common cases where disk can be avoided.

Don't ever use TINYTEXT.
Don't use TEXT if a modestly sized VARCHAR will suffice.
Don't blindly say VARCHAR(255); make them reasonably sized.  (Before spilling to disk, they are stored in a MEMORY table, which turns VARCHAR into CHAR.  Note: 8.0 fixes that.)
Poor indexes.
Poor formulation of queries.

For the last two, let's see a couple of the queries that are likely to be spilling to disk.  I can often spot what to do.

Filesystems

I dislike chopping disk up into filesystems.  The inevitable result is running out of disk on one FS while there is lots of room on another FS.
I dislike using ramdisk for anything; MySQL does a lot of intelligent work to make use of the RAM it is given; don't rob it for transient use.
I strongly agree with your Reason 1 (give RAM to the buffer_pool).
As for reason 2...  Note that any DDL (Alter/Optimize/...) that needs to copy a table over would prefer to do it in the same FS so that the final step is mv, not cp + rm.
As for OPTIMIZE TABLE, I frequently rant about how that is virtually useless for InnoDB; don't use it.


Inheritance - What's better: three "equal" tables or one with a type column?

This is a very, very common question; people just do not know what to search to get good results. You need to see suggestions on implementing table inheritance (sometimes mentioned as polymorphic associations) for a relational database.

There is no general answer for that problem, there is a few stategies like the ones you mention, and it all depends on your needs, and the focus of later queries (writes and reads). Each option has its advantages and disadvantages- for example, you can sacrifice consistency for better read performance. But you should identify this pattern in order to know the tools to solve it.

You can read what other people has written about it at:

Sql Antipatterns Strike Back (Bill Karwin)- Polymorphic Associations: https://www.slideshare.net/billkarwin/sql-antipatterns-strike-back/32-Polymorphic_Associations_Of_course_some/32
Previous answers on stackexchange: https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database

Check literature by Bill Karwin and Martin Fowler for a more in depth analysis.


While I'm not convinced this is a problem with the query itself (did you check for blocking when it runs slow? did you check the wait type(s) occurring while it was running), IN and OR can be a problematic pattern to optimize for. Have you considered breaking this into multiple statements?

UPDATE dbo.VillageSemaphoreset
  SET [TimeStamp] = GETDATE() -- TimeStamp is a terrible column name btw
  WHERE VillageID = @X;

UPDATE dbo.VillageSemaphoreset
  SET [TimeStamp] = GETDATE()
  WHERE VillageID = @Y;

IF (whatever condition leads you to "sometimes add this OR")
BEGIN
  UPDATE v
    SET [TimeStamp] = GETDATE()
    FROM dbo.VillageSemaphoreset AS v
    WHERE VillageID = @Z
    AND EXISTS
    (
      SELECT 1 FROM dbo.VillageSU AS vs
      WHERE vs.VillageID = v.VillageID
    );
END

This may solve the estimation problem, but I agree with Max, a statistic with a leading column of UnitCount won't help the estimates for these queries anyway.


You could get aggregates before to join both tables.

;WITH g1 AS
(
    SELECT sn, SUM(samt) AS samt
    FROM   @Sub
    WHERE  ssd >= '20170601' AND ssd <= '20170605'
    GROUP BY sn
)
, g2 AS
(
    SELECT sn, SUM(amt) as amt
    FROM   @Main
    WHERE  sd >= '20170601' AND sd <= '20170605'
    GROUP BY sn
)
SELECT g1.sn AS SalesPerson,
       g1.samt AS SalesAmt,
       (g1.samt - g2.amt) AS DiffAmp
FROM   g1
JOIN   g2
ON     g1.sn = g2.sn;
GO

SalesPerson | SalesAmt | DiffAmp
:---------- | -------: | ------:
G           |       11 |     -33

dbfiddle here


Assuming default data directory setup (no partitioning etc), you follow these steps:

1) Stop the mysql server
2) Make a backup of the current mysql/data directory
3) Copy all the files and folders from the mysql/data backup directory into the active data directory
4) Restart mysql

This should let you restore the database to the point-in-time of your backup copy. The mysql-bin files aren't necessary for a recovery, but ibdata* files are (assuming innodb tables). If myisam, you should have .MYI and MYD files for each tablespace as well as the .frm files.

Hope this helps


Wouldn't any db supporting the XML sections of SQL2003 work?  If you are trying to store the document as reported, I think you'd want to store as an XML file, not breaking it apart.

So I think if you are storing these as records of documents, your schema is not going to be based on XBRL but rather based on simple document management.


Multiple update of records in the same table

Philᵀᴹ: Insert all of the data pairs into a temporary staging table, then run a single update. Will be lots quicker.

Kondybas: Create table pairs(id,name) from collected data and then:

UPDATE table
JOIN pairs ON table.id=pairs.id
SET table.name=pairs.name

Wiki answer generated from comments on the question.


Alter user - is password stored in audit trails

You could just try it out.

This is logged in locally sqlplus / as sysdba:

SQL> alter user mat identified by COOLPASSWORD ;

User altered.

SQL> select substr(sql_text,0,40) from v$sqlarea where lower(sql_text) like '%alter%' ;

SUBSTR(SQL_TEXT,0,40)
----------------------------------------
select substr(sql_text,0,40) from v$sqla

SQL> select substr(sql_text,0,40) from v$sql where lower(sql_text) like '%alter%' ;

SUBSTR(SQL_TEXT,0,40)
----------------------------------------
select substr(sql_text,0,40) from v$sql
select substr(sql_text,0,40) from v$sqla

The alter commands are not present in v$sql or v$sqlarea. I have sys operations auditing on that test database, and the audit file for the above password change contains:

<AuditRecord><Audit_Type>4</Audit_Type>...<OSPrivilege>SYSDBA</OSPrivilege>...
<Sql_Text>alter user mat identified by * </Sql_Text>
</AuditRecord>

That's a literal * in the XML file. The normal audit logs contain the same * instead of the password, including if I change another user's password (logged in as a normal DBA user).

Now if you use things like rlwrap to run sqlplus, or fancy GUI clients, the password may well stay in some command line history/buffer. But the database server can't do much about that.


SSAS- Dimension Attribute needs to be sorted

Thank you for your well asked question.
Ordering attribute hierarchy in SSAS can be done in four different ways

By Key
By name
By Other related attribute key.
By other related attribute name.
By in order to activate ordering we need to set the property AttributeHierarchyOrdered=True.


The correct URL for a JDBC based connection would be:

jdbc:oracle:thin:@hp805:7633:idopd

But that information should be available in the DbVisualizer manual and is definitely available in the Oracle manual.

Edit:
The description on how to setup a connection with the wizard is available here:
http://www.dbvis.com/products/dbvis/doc/main/doc/ug/getConnected/getConnected.html


Postgres IDLE State affect Max_Connection value set in Postgresql.conf

When the state is idle, then the query shown in pg_stat_acitivy is the last query that was run before it went idle.  Since that was apparently COMMIT (and since the state is idle, not 'idle in transaction'), then no, you don't need to worry about autocommit not working.

My assumption is that the connections will be re-used but concerned if we run more tests new connections will spin up and the max_connection of 200 users will be hit.

If your java connection poolers are set up correctly, they should be reused.  If you run out of max_connections, you will hear about it in the log files.  Why preemptively worry about it?  Are you running your tests against your production database?


RAID0 vs multiple files in file group
  RAID0, definitely, with SSD's.  There MAY be an edge case exception if you know you're getting hung up on two particular indexes (clustered or not) in a JOIN, where you could gain performance by having two FILEGROUPs, one on each disk, and splitting those indexes between the filegroups, but I really, really doubt it, and you'd only find that out by benchmarking it.

As far as not caring about redundancy, I think you're crazy.  Note that a modern Dell PERC (LSI Logic) controller can get >1GB/s sequential data transfer off of a set of 6 SATA SSD's in RAID5, and the random rates are also in the hundreds of MB/s if you keep your IO depth deep enough.  Note also there's a huge different between high-end SSD's and low-end SSD's, particularly for random writes (i.e. probably your update process).

  I'm trying to improve performance of the update process and the selects.

This is a general performance tuning question, so all the normal performance tuning advice applies.  Run Profiler on your selects during the day, look at reads, writes, duration, CPU.  Use the SSMS-based Standard Report for Top Queries by Total CPU and Total IO during the day.  Tune your queries and data structure, starting with the highest impact and working your way down.

Do the same thing with the update, but separately.

If you really think IO is really the issue, look at IOStall in sys.dm_io_virtual_file_stats or use Perfmon (Avg sec/Read and Avg sec/Write).

As a general shotgun approach, if during the day it's 100% read only (selects), you can ALTER DATABASE x SET READ_ONLY to reduce possible locking.  Set it to READ_WRITE before the update, and back to READ_ONLY afterwards.

As part of your update, are you rebuilding indexes (which also updates their statistics with FULLSCAN as part of the rebuild)?  Or making sure stats are up to date?

If you're really having IO issues on pure reads (selects), and you're unwilling or unable to tune your queries and schema, then there's always buying more RAM or increasing the maximum amount of RAM allocated to this instance of SQL Server.


Finding the Min value for a subgroup, filtering out preceding Min values within the same parent group

Quite a hard problem. Here is a recursive solution:

WITH
  rcte AS
  ( SELECT TOP (1)
        Acc, TranType, posCancelID,
        CAST('=' + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX)) AS IDs
    FROM
        MCancel
    ORDER BY
        Acc, TranType, posCancelID

    UNION ALL

    SELECT
        Acc, TranType, posCancelID,
        CAST(IDs + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX))
    FROM
      ( SELECT
            m.*,
            r.IDs,
            ROW_NUMBER() OVER (ORDER BY m.Acc, m. TranType, m.PosCancelID) AS rn
        FROM
            rcte AS r
            JOIN MCancel AS m
                ON  (m.Acc = r.Acc AND m.TranType > r.TranType)
                OR  (m.Acc > r.Acc)
        WHERE
            r.IDs NOT LIKE ('%=' + CAST(m.posCancelID AS VARCHAR(20)) + '=%')
      ) AS mc
    WHERE
        rn = 1
  )
SELECT Acc, TranType, posCancelID
FROM rcte
ORDER BY Acc, TranType ;

The solution assumes that a posCancelID should not appear twice in the result set. If the requirements are that they should not appear twice  in the same Acc group, then the solution needs a slight adjustment:

WITH rcte AS
  ( SELECT
      Acc, TranType, posCancelID,
      CAST('=' + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX)) AS IDs
    FROM
      ( SELECT
          Acc, TranType, posCancelID,
          ROW_NUMBER() OVER (PARTITION BY Acc ORDER BY TranType, PosCancelID) AS rnk
        FROM MCancel
      ) AS f
    WHERE rnk = 1

    UNION ALL

    SELECT
      Acc, TranType, posCancelID,
      CAST(IDs + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX))
    FROM
      ( SELECT
          m.*, r.IDs,
          ROW_NUMBER() OVER (PARTITION BY m.Acc
                             ORDER BY m.TranType, m.PosCancelID) AS rn
        FROM
          rcte AS r
          JOIN MCancel AS m
              ON  (m.Acc = r.Acc AND m.TranType > r.TranType)
        WHERE
          r.IDs NOT LIKE ('%=' + CAST(m.posCancelID AS VARCHAR(20)) + '=%')
      ) AS mc
    WHERE rn = 1
  )
SELECT Acc, TranType, posCancelID
FROM rcte
ORDER BY Acc, TranType ;

Both are tested at dbfiddle.uk


I think you must be misunderstanding CASCADE foreign keys, and/or the rules around foreign keys in general.

A foreign key can only refer to a table with a unique constraint or primary key. So in your example, you cannot create a foreign key referencing table_b if there can be multiple instances of a value in the referenced column.

When you DELETE FROM ... a table, and another table has an ON DELETE CASCADE foreign key reference to it, all referencing rows are deleted from the other table.

The column referenced cannot have multiple instances of the value, because you cannot create a foreign key to a column that is not unique.

The referring column of the table with the constraint  may have multiple values that refer to the column in the referenced table. If so, when the referenced value is removed, all these are deleted, as you would expect in a CASCADE.

regress=> CREATE TABLE table_b (id integer);
CREATE TABLE
regress=> INSERT INTO table_b (id) VALUES (1), (2), (3), (1);
INSERT 0 4

Can't create the foreign key, no unique constraint or PK:

regress=> CREATE TABLE table_a (b_id integer REFERENCES table_b(id) ON DELETE CASCADE);
ERROR:  there is no unique constraint matching given keys for referenced table "table_b"

Can't add a PK or unique constraint, has duplicates:

regress=> ALTER TABLE table_b ADD PRIMARY KEY (id);
ERROR:  could not create unique index "table_b_pkey"
DETAIL:  Key (id)=(1) is duplicated.


testbed:

create table foo as
select add_months(sysdate, -(level-1)*3) as eventdate from dual connect by level<10;

select * from foo;
/*
EVENTDATE
-------------------------
30-DEC-11 16.09.20
30-SEP-11 16.09.20
30-JUN-11 16.09.20
30-MAR-11 16.09.20
30-DEC-10 16.09.20
30-SEP-10 16.09.20
30-JUN-10 16.09.20
30-MAR-10 16.09.20
30-DEC-09 16.09.20
*/

query:

select to_char(eventdate,'YYYY "Q"- Q') -- "Q" is treated as a literal due to the quotes
from foo
where eventdate>=add_months(trunc(sysdate, 'Q'), -12); -- trunc() gets first day of the Q
                                                       -- then add_months takes us back 4 Qs
/*
TO_CHAR(EVENTDATE,'YYYY"Q"-Q')
------------------------------
2011 Q- 4
2011 Q- 3
2011 Q- 2
2011 Q- 1
2010 Q- 4
*/


Is that possible to group result on 3 months period basis?

For the following I am assuming you already have a numbers table, If you don't have a numbers table, then I'd recommend you make one then, but if you don't want to then you can create a number list on the fly

With this numbers table you can then define your quarters:

SELECT  DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -Number - 1 QUARTER) AS QuarterStart,
        DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -Number QUARTER) AS QuarterEnd
FROM    Numbers

This gives (When run at the time of answering in November 2013):

QUARTERSTART    QUARTEREND
2013-09-01      2013-12-01
2013-06-01      2013-09-01
2013-03-01      2013-06-01

If the same query was run next month the result would be:

QUARTERSTART    QUARTEREND
2013-10-01      2014-01-01
2013-07-01      2013-10-01
2013-04-01      2013-07-01

You can then join your table data to this:

SELECT  DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -n.Number - 1 QUARTER) AS QuarterStart,
        DATE_ADD(DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -n.Number QUARTER), INTERVAL -1 DAY) AS QuarterEnd,
        COUNT(t.ID) AS `Count`
FROM    Numbers n
        INNER JOIN Table_Name t
            ON t.DateField >= DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -n.Number - 1 QUARTER)
            AND t.DateField < DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -n.Number QUARTER)
GROUP BY n.Number;

Example on SQL Fiddle

N.B. In the select I have deducted one day from the quarter end field as visually the last day of the quarter appears more relevant, but for the join I have kept it as the first day of the next quarter, but used the less than operator

Of course if you also want to count empty quarters you can change the join to a LEFT JOIN, but you will probably then want to limit the number of rows you are returning by adding a Where clause.


PostgreSQL doesn't use all the RAM it could

PostgreSQL relies on the operating system's disk cache for most caching. This cache is usually reported as "free" RAM by most tools, because modern operating systems use all but a little bit of the currently-free RAM for disk cache.

This is normal.

To confirm, use a better tool that shows buffers/cache separately from truly free memory. On Linux, free -m will show you more info.

Also note that for most systems the accounting for shared memory means that the memory use reported for PostgreSQL processes is pretty bogus. They tend to count all the shared memory touched by a process as used by that process, thus counting each shared memory page many times over. Or they tend to ignore shared memory entirely, not counting it at all. The best way to estimate PostgreSQL's actual memory use is to get the unshared memory use for each process, add them up, and add the shared_buffers size to the total.

BTW, shared_buffers is a second level cache. Any clean buffers in there probably duplicate what's in the OS's disk cache. So there's a system and workload specific size that's optimal. Too small and you start struggling to find free pages to read in, or forcing dirty buffers out too early for efficient write combining. Too big and you double-cache too many clean pages from the OS disk cache.


List of nodes types in a query plan in PostgreSQL?

If you are just looking for a comprehensive list of types, without any explanations, this piece of source code might be of help to you.


Shared memory connection experiencing network wait type

ASYNC_NETWORK_IO also happens when the app is NOT reading the data fast enough from SQL Server. Note that this is NOT SQL Server issue but its the app issue. Make sure the app is reading the data as a set instead of row by row.


MySQL security group view permission query

SELECT DISTINCT record.id AS id, details from record
LEFT JOIN record_group ON record.id = record_group.record_id
WHERE record_group.group_id IN (SELECT group_id from user_group WHERE user_id = 2)

http://sqlfiddle.com/#!9/e40ee6/12


Backup of Databases not present in Availability Groups

It really Depends,

Technically, as others have mentioned, you can use one built in maintenance plan to backup all of the databases.  Regardless if they are in an AG or not in an AG.

But having it all in one plan, and having that plan fail, means you may miss some SLAs.  There is a question I ask myself when setting up backups:  "What is the Restore Strategy and the SLA?" Your backup strategy should account for your restore and recovery requirements in the SLA.  Can you restore exactly the amount of data that business requires, in the time frame required?  Imagine if your one maintenance plan fails, and only backups up half of the databases one night. What happens if a disaster occurs between the failed backups and the next backup cycle?  Can you recover the data and get the business back online?  It depends on your SLA.  That should help you decide if you need one job, two jobs, or a different strategy entirely.

Also Since you mentioned,  "Do i need to use copy_only?", am I right in assuming you have Databases acting as principals/primaries, and other in an AG group acting as secondary replicas?  If so you can still techincally use one job but you need to understand what copy_only does. The article has some good highlights.


Find times when table is least frequently used

SQL Server doesn't keep track of this information. You'd have to take snapshots from sys.dm_db_index_usage_stats frequently enough that you can graph activity, and you also need to account for service restarts, index rebuilds, etc. which can remove rows from the DMV and set the activity back to zero (this may look on your chart like no activity but it really means it has been reset).

This is not a straightforward task. I think you should add more information to your question explaining why you need this information, because maybe there are other ways to accomplish your real goal (knowing when a table is read less frequently is not a goal, it's the path you've chosen to your goal).


I create the sample you gave with this:
drop database if exists a1ex07;
create database a1ex07;
use a1ex07
CREATE TABLE test_1(id int not null auto_increment PRIMARY KEY, val enum('A','B') not null);
insert into test_1(val) values ('A');
explain select * from test_1 where val is null;
explain select * from test_1 where val ='C';

Here is it loaded:
mysql> drop database if exists a1ex07;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create database a1ex07;
Query OK, 1 row affected (0.00 sec)

mysql> use a1ex07
Database changed
mysql> CREATE TABLE test_1(id int not null auto_increment PRIMARY KEY, val enum('A','B') not null);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test_1(val) values ('A');
Query OK, 1 row affected (0.06 sec)

mysql> explain select * from test_1 where val is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

mysql> explain select * from test_1 where val ='C';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_1 | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

In the first EXPLAIN plan, the Impossible where would be based on the definition of the column you gave, which is val enum('A','B') not null. This would help the MySQL Query Optimizer quickly rule out unneccesary evaluations, especially if the table had millions of rows.
In the second EXPLAIN plan, the Using where indicates that MySQL Query Optimizer would have to evaluate a potential value (C) to see if it resides in the table. I believe what you are looking for is for the MySQL Query Optimizer to check the definition of the column, which is enum('A','B'). It should but it probably doesn't. You should submit that as a bug report to see if Oracle, out of the goodness of their heart, will address this.
UPDATE 2012-01-12 14:08 EDT
I just looked around mysql website for any bugs regarding EXPLAIN and ENUM. At this time, there are no reported bugs of ths nature. You should submit this one ASAP.


Assigned filetype for backups not applying

After a bit more investigation and review, we found that the filename size was too long and was cutting off the tail end of the name. It's very likely the issue has been going on for some time, but was never noticed until the changeover.


SSMS - How to Enable Word Wrap for Messages Pane

Unfortunately, there isn't an option to enable word wrap in the messages pane.  You can enable for the text editor window (through Tools -> Options -> Text Editors), but that doesn't really help in your situation.

In case you didn't know, though, you can click into that messages pane and edit the text.  So you could insert the line breaks yourself.  Not ideal, but good enough to get the desired screenshot.

Before:

After manual editing:

I checked Azure Data Studio as well, it does not have a word wrap feature for output messages either.


Are there advantages to using temporary tables over derived tables in SQL Server?

@user16484 already directed you to Which one have better performance : Derived Tables or Temporary Tables in the comment.

Also see Temp Table 'vs' Table Variable 'vs' CTE. which also covers derived tables.

A quick summary: #temp tables can be indexed, can have UNIQUE indexes/constraints, can be references more than one time in the same query, can be referenced (FROM or JOIN) by more than one query.  Derived tables can be referenced (FROM or JOIN) once in one query.

Performance-wise, pull out Profiler for SQL:BatchCompleted and RPC:Completed, watch the Read, Write, CPU, and Duration columns, and see what a few runs of derived tables vs. #temp tables vs. indexed #temp tables does for each particular query.

In general - if you're going to use it more than once, #temp table wins.  If you're joining a lot of tables, #temp table probably wins.  If you're joining only a few tables, derived table has a reasonable chance of winning.  Benchmark it!


The CONCAT() function returns NULL when one of the arguments is null. You could use COALESCE() to bypass the issue, converting the nulls to empty strings - or to any other constant value, for example '00:00:00' for the time part:

select concat( coalesce(cast(transactionDate as char), ''),
               ' ',
               coalesce(transactionTime, '00:00:00')
             ) as transaction
from transactionTable
where id = 123 ;

In fact, you don't really need an explicit cast(), the cast to char is implicit:

select concat( coalesce(transactionDate, ''),
               ' ',
               coalesce(transactionTime, '00:00:00')
             ) as transaction
from transactionTable
where id = 123 ;


VarBinary(max) updates very slow on SQL Azure

The on-prem instance was just a local install on my surface. The other is a 50eDTU elastic pool on Basic-tier. After the comment I changed it to a standard tier and the query now takes about 2 seconds instead of more than 15!
So I think that's the answer!

I never thought that a local install of SQL on a surface together with all other software like Visual Studio etc running, would out perform SQL Azure by 25 times! (Even on a basic tier). But now I know :-)


Finding the partitions of a set which respect to some rules

Using PostgreSQL

WITH t AS (
        SELECT f1, array_agg(f2) AS p
        FROM (
                VALUES
                 ( 'a',1 ),
                 ( 'a',2 ),
                 ( 'b',1 ),
                 ( 'b',2 ),
                 ( 'c',3 ),
                 ( 'd',4 ),
                 ( 'd',5 )
        ) AS t(f1,f2)
        GROUP BY f1
)
SELECT array_to_string(p1, '') || array_to_string(p2, '') AS partitions
FROM (
        SELECT array_agg(DISTINCT t2.f1) AS p1, p AS p2
        FROM t AS t1
        INNER JOIN t AS t2 USING (p)
        GROUP BY p
) AS t;

Result:

 partitions
------------
 ab12
 c3
 d45
(3 rows)


List all users on SQL Server for auditing

Users are shown 'duplicated' simply because each database has its own users.

It may help to extend your query a little, so you can see which database each set of users is coming from:

exec sp_MSforeachdb 'select ''?'' as DBNAME, * from ?.sys.sysusers'

Note the important differences between a Login and a User :

  A Login is for Authentication.
  A Login verifies that you can access the server.
  There are two types: SQL Logins and Windows Logins.
  Logins are created at the SQL Server instance level (and stored in master).

  A User is for Authorization.
  Authorization decides what you can do in a database.
  A User needs to be linked to a Login.
  A User doesn't have a password - the Login decides where that is stored.
  It is quite usual to have Users from different databases linked to a single Login on the server.
  Users (and their Roles) are stored in each database, not in master (see Security > Users in each database when using SSMS).


Is SQL Server Certificate using asymmetric encryption internally?

In Books Online I've read that both asymmetric key and certificates use the idea of asymmetric encryption.

They both are asymmetric keys, certificates just have extra metadata and can have other functions run on them such as validation and revocation.

  [...] do the certificates created in SQL Server or let's say via Always Encrypted, use the idea of asymmetric encryption?

Yes, see above.

  So internally they have public and private keys stored in the same place?

Yes, although you can change that by removing the private key from asymmetric keys and certificates. Additionally, the keys do not have to be stored in the physical database, you can use a HSM.

  Does it mean that when we create a symmetric key and encrypt it by a certificate it actually means encrypting a key with a asymmetric encryption?

No, it means that the symmetric key is protected by the asymmetric key. This means the asymmetric key much first be successfully decrypted, the private key must be available, and the symmetric key can then be decrypted using the private key if it exists. Once the symmetric key is decrypted it can be used in operations. If the asymmetric key can not be decrypted, you will get an error and be unable to use the symmetric key as it'll still be encrypted. The data itself is protected by the symmetric key.


Top N earliest punched in Employees across departments in a month

It seems to me the specification failed to mention that the ordering should be done using only the hour and minute parts of the date.

The below returns the expected output.

with data as
(
  select 1 as id, 'Dharma' as empname, to_date('26-JAN-2018 08:45', 'DD-MON-YYYY HH24:MI') as emppunchintime, 'Engineering' as empdept from dual union all
  select 2 ,      'Gumpina',           to_date('26-JAN-2018 08:46', 'DD-MON-YYYY HH24:MI'),                   'Finance'                from dual union all
  select 3 ,      'Kumar',             to_date('26-JAN-2018 08:47', 'DD-MON-YYYY HH24:MI'),                   'HR'                     from dual union all
  select 4 ,      'Kamal' ,            to_date('26-JAN-2018 08:48', 'DD-MON-YYYY HH24:MI'),                   'Engineering'            from dual union all
  select 5 ,      'Muruges',           to_date('26-JAN-2018 08:49', 'DD-MON-YYYY HH24:MI'),                   'Finance'                from dual union all
  select 6 ,      'Gumpina',           to_date('25-JAN-2018 09:01', 'DD-MON-YYYY HH24:MI'),                   'Finance'                from dual union all
  select 7 ,      'Kumar',             to_date('25-JAN-2018 08:42', 'DD-MON-YYYY HH24:MI'),                   'HR'                     from dual union all
  select 8 ,      'Biju',              to_date('25-JAN-2018 08:40', 'DD-MON-YYYY HH24:MI'),                   'Engineering'            from dual union all
  select 9 ,      'Divya',             to_date('25-JAN-2018 08:49', 'DD-MON-YYYY HH24:MI'),                   'HR'                     from dual union all
  select 10,      'Vali',              to_date('24-JAN-2018 08:52', 'DD-MON-YYYY HH24:MI'),                   'Engineering'            from dual
)
select f.id, f.empname, f.emppunchintime, f.empdept from
(
  select e.*, row_number() over (partition by e.empdept order by to_char(e.emppunchintime, 'HH24:MI')) as rn2 from
  (
    select d.*, row_number() over (partition by d.empname order by to_char(d.emppunchintime, 'HH24:MI')) as rn from data d
  ) e
  where e.rn = 1
) f
where f.rn2 <= 2
order by f.empdept, f.emppunchintime;

Output:

        ID EMPNAME EMPPUNCHINTIME    EMPDEPT
---------- ------- ----------------- -----------
         8 Biju    25-JAN-2018 08:40 Engineering
         1 Dharma  26-JAN-2018 08:45 Engineering
         2 Gumpina 26-JAN-2018 08:46 Finance
         5 Muruges 26-JAN-2018 08:49 Finance
         7 Kumar   25-JAN-2018 08:42 HR
         9 Divya   25-JAN-2018 08:49 HR


I was interrupted while transferring a very large table. How can I pick it up from where it stopped?

An alternative view:

Are you really using inserts for 2B rows?  You might be better off bulk loading the data.  2B rows shouldn't take 3 days unless they are spectacularly wide.

Also, pg_bulkload might be of interest for this.


Transaction Table in Personal Finance App

My first advice would be to check out a few Open Source projects on the same topic.

Adapt and adopt the concepts you like - pick and choose ideas. No point reinventing the wheel! :-) I appreciate that you want to learn, but translating code from [PHP | Python | Perl | Whatever] could also be a beneficial learning experience! And you might even gain tips and tricks from more experienced programmers!


Table structure for validating data using category and subcategory fields

There are a few different strategies you could take, where on one end you pursue aggressive normalization to, on the other end, full denormalization.  The full denormalization would be equivalent to your second example where all relevant info simply ends up in the transaction table without references to other tables.

Full Normalization

So, to completely normalize, you would still want a Categories table, but you want to even eliminate the storage of redundant information in this table, so you would need a CategoryList table and a SubCategoryList table as

CategoryList

id    category
----------------
 1     Food
 2     Household
 etc...

and

SubCategoryList

id    subcategory
----------------
 1     Work Lunch
 2     Fast Food
 3     Grocery Store
 4     Mortgage
 5     Repairs
 etc...

You could then construct your category table from these two tables  as

Categories

id    category_id     subcategory_id
----------------------------------
 1        1               1
 2        1               2
 3        1               3
 4        2               4
 5        2               5

Treatment of NULL subcategories can easily be handled by either 1) simply placing a NULL entry for the subcategory_id column in the appropriate row of the Categories table, or 2) adding a subcategory entry id, subcategory where the subcategory field is NULL.

Last but not least you would add a foreigh key reference from your Transactions table to the appropriate id in the Categories table.

Does it really need to be so normalized?

Well, in my opinion, no it doesn't.  I've heard a quote, though I can't remember who spoke it, but it basically goes "Normalize until it hurts, denormailize until it works."  Especially in the case where you don't have a lot of categories, the fully normalized design may be a little bit of overkill.

What might simply make more sense would be to keep the above mentioned CategoryList and SubCategoryList tables to enumerate your types, but skip making the separate Categories table, and then simply have your Transactions table referencing the CategoryList and SubCategoryList tables as

id    txdate  amount   category   subcategory   account   ...
--------------------------------------------------------------
 1   6/25/15   15.25      1             2        cash                     ...

This way, you save on storage, and you can easily update/modify any category or subcategory entry in the list without needing to modify your entire Transactions table.  Further, you can simply permit the subcategory column of the Transactions table to permit NULL entries, if need be.

Hope this helps!


Definitely save new day data only if something has changed.
Now the question is onr table with data , or types and data.
Depends on number of columns and the periodicity of change.
For two table, there is a higher overhead, but iwth single wide table (lets say 10 or more types or data), you will be storing lot of duplicate data. In addition, more joins will be needed.
I would say single table is better if storage is not an issue


Any relation between SQL Server Service Name and Instance Name?

Yes, the service name is always MSSQL$<Instance Name> for a named instance and MSSQLSERVER for a default instance. I don't believe either can be altered or overridden, nor can I think of any reason why you'd want to.

You can enumerate the installed instances on a server via the registry, using Powershell for example:

Get-ItemProperty 'HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL'


How to convert a datefield from string to date?

You'll need to run a script to detect for integer/string based values and replace them with the standard Mongo/ISO Date standard.

https://docs.mongodb.com/manual/reference/method/Date/


How to put Oracle Redo Log file at the outer edge of disk for performance?

Oracle redo files are written in a sequential fashion. This is what gives the best performance because the write head is always on the correct location to write again, as long as the disk is not shared with other files. As soon as the disk - or more precisely - the write head is shared with a datafile, the head has to make more movements to satisfy the random IO pattern that is mostly found for datafiles.

The cylinders numbering of disks start at the outer edge of the disk. See Oracle Solaris 11 System Administration: Administering Storage Devices for some info. While formatting the slices you can point them their location on disk.

If you really want high performant redo files, give them their own dedicated disks and stripe them for best performance. Don't forget that the archiver is also going to read the redo after it has been completed ....

Managing files on separate disks is something of the past. Since years Oracle preaches SAME. Stripe And Mirror Everything. See Optimals Storage Configuration Made Easy Add the disks to an ASM diskgroup and let ASM handle the file placement.


Should a multi tenant system with SQL Server 2016, Shard or have Tenant isolation via separate database per tenant?

The gotcha with sharding is that the application has to know which shard to query. Generally, this is done by sharding on something like client. I'll adapt one of my old blog posts to use as my answer.

When you’re building an application for lots of clients, there’s two common ways to design the database(s):

Option A: Put all clients in the same database
Option 2: Build one database per client

Putting All the Clients in the Same Database

It’s simple: just add a Client table at the top of the schema, add a ClientUsers table to make sure people only see their own data, and away we go.

Benefits of this approach:

Easier schema management. When developers deploy a new version of the application, they only have to make schema changes in one database.  There’s no worries about different customers being out of sync or on the wrong version.

Easier performance tuning. We can check index usage and statistics in just one place, implement improvements easily, and see the effects immediately across all our clients.  With hundreds or thousands of databases, even the smallest change can be difficult to coordinate.  We can check our procedure cache contents and know for certain which queries or stored procedures are the most intensive across our entire application, whereas if we’re using separate databases per client, we may have a tougher time aggregating query use across different execution plans.

Easier to build an external API. If we need to grant access to our entire database for outsiders to build products, we can do that easier if all of the data is in a single database.  If the API has to deal with grouping data from multiple databases on multiple servers, it adds development and testing time.  (On the other hand, that “multiple servers” thing starts to hint at a restriction for the one-database-to-rule-them-all scenario: one database usually means all our load impacts just one database server.) In your case, with PowerBI, having everyone in one database will make managing connections much easier.

Easier high availability & disaster recovery. It’s really, really simple to manage database mirroring, log shipping, replication, and clustering if all we have to worry about is just one database.  We can build a heck of an infrastructure quickly.

Putting Each Client in its Own Database or Shard

You still need a client listing, but now it becomes a directory - for each client, you also track the shard it lives in. On startup, your app queries this table, and caches it in RAM. When it needs data for a client, it connects directly to that shard (database & server).

Benefits of this approach:

Easier single-client restores. Clients are unreliable meatbags.  (Except mine – they’re reliable meatbags.)  They have all kinds of “oops” moments where they want to retrieve all of their data back to a point in time, and that’s a huge pain in the rear if their data is intermingled with other client data in the same tables.  Restores in a single-client-database scenario are brain-dead easy: just restore the client’s database.  No one else is affected.

Easier data exports. Clients love getting their hands on their data.  They want the security of knowing they can get their data out anytime they want, avoiding the dreaded vendor lock-in scenario, and they want to do their own reporting.  With each client’s data isolated into their own database, we can simply give them a copy of their own database backup.  We don’t have to build data export APIs.

Easier multi-server scalability. When our application needs more power than we can get from a single server, we can divide up the databases between multiple servers.  We can also spread out the load geographically, putting servers in Asia or Europe to be closer to clients.

Easier per-client performance tuning. If some clients use different features or reports, we can build a specialized set of indexes or indexed views just for those clients without growing everyone’s data size.  Granted, there’s some risk here – by allowing schema differences between clients, we’ve just made our code deployments a little riskier and our performance management more difficult.

Easier security management. As long as we’ve properly locked down security with one user per database, we don’t have to worry about Client X accessing Client Y’s data.  However, if we just use a single login for everyone, then we haven’t really addressed this concern.

Easier maintenance windows.  In a global environment where customers are scattered around the globe, it’s easier to take customers offline for maintenance if we can do it in groups or zones.

Which one is right for you?

There’s no one right choice: you have to know your own company’s strengths and weaknesses.  Let’s take two of my clients as examples.

Company A excels at hardware performance tuning.  They’re really, really good at wringing the very last bit of performance out of hardware, and they don’t mind replacing their SQL Server hardware on a 12-18 month cycle.  (They refresh web servers every 4-6 months!)  Their Achilles’ heel is extreme compliance and security requirements.  They have incredible auditing needs, and it’s just easier for them to implement bulletproof controls on a single server, single database than it is to manage those requirements across thousands of databases on dozens of servers.  They chose one database, one server, many clients.

Company 2 excels at development practices.  Managing schema changes and code deployments across thousands of databases just isn’t an issue for them.  They have clients around the world, and they’re processing credit card transactions for those clients around the clock.  They need the ability to spread load geographically, and they don’t want to replace servers around the world every 12-18 months.  They chose one database for each client, and it’s paying off as they start to put SQL Servers in Asia and Europe for their offshore clients.


Is Oracle 12.2 backward compatible with Oracle 12.1

Here is the list of changes:

Behavior Changes, Deprecated and Desupported Features for Oracle Database 12c Release 2 (12.2)

Such basic functionality that you mentioned (table joins, stored procedures, JDBC connections) should of course work.

Sure, there are new features, and some changes, but I can not recall anything significant related to those areas that worked in 12.1 but did not in 12.2.


create table test(set_name text, value int);
insert into test values
('a', 1),('a', 2),('b', 2),('b', 3),
('b', 4),('c', 3),('c', 5);

You can use a Window function and a CTE (you can move the CTE part to a subquery), to get the desired result.

;with x as
(
    select set_name, value,
           count(value) over (partition by value) cnt
    from   test
)
select set_name, value
from   x
where  cnt = 1;

set_name | value
:------- | ----:
a        |     1
b        |     4
c        |     5

dbfiddle here


Trigger doesn't run when value of a column changes from null to 1

Change the if condition to:

if (new.col1 != old.col1) or ( new.col1 is not null and old.col1 is null )
                          or ( old.col1 is not null and new.col1 is null )

That should solve your problem. The original code didn't work correctly as you can't  test for equality with a NULL value - you have to use IS NULL or IS NOT NULL.

MySQL has also a "null-safe equals" operator: <=> which can be used here to make the above condition simpler (and still equivalent):

if not (new.col1 <=> old.col1)


Run this query:

SELECT * FROM pg_stat_activity ORDER BY xact_start NULLS LAST LIMIT 1;

If you get a row back where xact_start is not null and it's anything more than a few seconds old, that's your culprit.  VACUUM cannot remove rows that have transaction_ids greater than or equal to the oldest open transaction in the system (even if it's in a completely different database).  If you terminate that transaction and re-run the VACUUM, you should see more than 0 rows removable.


Database master key and certificate for backup encryption - SQL Server

Isn't the certificate encrypted by the master key?
Not exactly. To be more precise, the certificate private key is the one encrypted by the master key and you can see that under the Remarks section of the CREATE MASTER KEY doc:

The database master key is a symmetric key used to protect the private
keys of certificates and asymmetric keys that are present in the
database.

You can check sys.certificates to see that information as well:
SELECT name, pvt_key_encryption_type_desc, pvt_key_last_backup_date
FROM sys.certificates;

how does it let you proceed even with a different password for the master key?
When you backed up the certificate using the option WITH PRIVATE KEY you added to the backup the actual key used to encrypt things referenced by that certificate. When the certificate was within SQL Server, its master key secured the certificate private key, but when you back it up the only protection seems to be that password for the backup ENCRYPTION BY PASSWORD = 'bkPofcert!'. If you try to backup the certificate and private key without a password like this:
BACKUP CERTIFICATE CertName TO FILE = 'C:\SQL2019\certbk.cert'
WITH PRIVATE KEY (
FILE = 'C:\SQL2019\certkey.pvk')

SQL Server throws the following error:

An encryption password must be provided to encrypt the private key of
this certificate.

That means while the private key is being moved (by backing it up to restore it later) the only protection you get is that password from the backup, not the one from the master key. The password from the CREATE MASTER KEY command is in fact used when you need to use the command OPEN MASTER KEY.
As you have noticed by now, You shouldn't keep the certificate backup and the database backup at the same place for security reasons.
why do we need to backup the master key?
Because it encrypts objects (like the private keys used by the certificates) while they're still on the instance and if it was to be lost, things on the instance would not be decrypted.
The following backup of the certificate without its private key works, but it would not be enough to restore the database backup later as you did:
BACKUP CERTIFICATE CertName TO FILE = 'C:\SQL2019\CertName.cer';

The reason it does not restore the database properly is because the certificate misses the private key it used before.

Relevant material:
SQL Server Encryption - Youtube video by MVP Ben Miller
Encryption Hierarchy

Disclaimer: I provided this answer based on research and I'd be really glad if someone with more experience could confirm that my answer is appropriate because I understand encryption is a delicate subject and could cause someone to be locked out of its data if used without accurate knowledge.


Add AG Database Add process results in Connectivity Fail

Adding databases to an AG group does not disrupt interactions with the database

If you're adding a new database to an existing AG group then there will be no loss in service to SQL Server whatsoever--no severed connections. SQL Server does not need exclusive rights to the database in order to add the database to an AlwaysOn replica. The majority of what's going on behind the scenes on the source database is a database backup and log backup.

There will, however, be an I/O and CPU performance hit for backing up and copying the database and logs to the SQL replica(s). The bigger the database, the bigger the I/O impact. This may affect the performance of your connected application(s) if the database is large.

Note:
Although you may not experience disconnects to the database(s) you are adding to AlwaysOn, you are not using the listener for that AG group either. After you add database(s) to an AG group you will probably want to change the application connection strings to point to the AG listener rather than the SQL Server directly. Doing so will most likely disrupt service.


MyISAM or InnoDB?  Depending on which, you need to see the appropriate cache buffer.  If you had 16GB of RAM, you could set that so that the table will (eventually) be entirely in RAM.  That would move it from being I/O bound (as it probably is now) to being CPU bound.  At that point, an old, slow, cheap, spinning drive will be almost as good as SSDs.

Can you parallelize the algorithm?  If so, split it into as many programs (connections) as you have cores.  That will (assuming CPU-bound) give you a nice speed-up.

Let's see the main query.  And the SHOW CREATE TABLE for any table(s) involved.  There may be simple ways to speed it up.

(I estimate that a table of 8.4GB is about the 96th percentile.)


Continuous archiving: is it possible to use multiple machines?

First, I always create a wrapper script when creating commands like that. That makes it easier to debug the command without having to signal postgres to reload anything. This also makes it easier to include logging from your archiver script.

What you need to figure out first is how you want your data replicated. The way you're doing it now seems ok, at least if you check if the destination file doesn't exist before copying it. That way it can be re-run as many times as you like and it will try to copy it to the external location.


The log can be forced to grow if you have an old transaction that has not been committed or rolled back, regardless of the size (it could affect a single row). For the database in question, assuming you haven't resolved the problem yet, do this:

DBCC OPENTRAN();

This should show you the oldest active transaction (and there is a good chance this is the one that is preventing the log from wrapping). You can see the last thing they did (but not necessarily the thing that is causing the problem) by (documentation):

DBCC INPUTBUFFER(<spid>);

And if they have a query that is currently running:

SELECT *
  FROM sys.dm_exec_requests
  WHERE session_id = <spid>;

You can also see from sys.databases what the log holdup is (it could be something else entirely):

SELECT log_reuse_wait_desc
  FROM sys.databases
  WHERE name = N'<database name>';

There can also be reasons why an 80 MB transaction might require more than 200 MB of log space - see Remus' answer here:

Why does this 120 GB insert grow the log by over 780 GB?

And I recommend you read this page in its entirety (perhaps you are not using the right recovery model, and perhaps you should consider allowing the log more space to grow when it needs to, rather than shutting down all activity until you resolve the situation manually):

Why Does the Transaction Log Keep Growing or Run Out of Space?

Related:

Identify cause of unusual transaction log growth
Contents of transaction log file in more detail
SQL Server transaction log
Finding exact statement causing error 3930
SIMPLE or FULL recovery model for databases?
SQL Server log file continues to grow?
Changing a column's data type fills the transaction log
Log sizes unexpectedly large: log_reuse_wait_desc == "log_backup"


indexable JSON datatype in MySQL/MariaDB

You can do this in MySql 5.7. What you are looking for is adding an index over Generated Columns. You can find an example here:
http://mysqlserverteam.com/inline-json-path-expressions-in-mysql-5-7/


Does it really matter? It is an undocumented internal dictionary table. It is a number representing some flags set.

Just have a look at the definition of views such as DBA_TAB_COLS_V$.

select dbms_metadata.get_ddl('VIEW', 'DBA_TAB_COLS_V$', 'SYS') from dual;

There you can find stuff like below, example:

   ...
   decode(c.type#, 1, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
                  96, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
                  null),
   decode(bitand(ac.flags, 128), 128, 'YES', 'NO'),
   decode(o.status, 1, decode(bitand(ac.flags, 256), 256, 'NO', 'YES'),
                    decode(bitand(ac.flags, 2), 2, 'NO',
                           decode(bitand(ac.flags, 4), 4, 'NO',
                                  decode(bitand(ac.flags, 8), 8, 'NO',
                                         'N/A')))),
   decode(c.property, 0, 'NO', decode(bitand(c.property, 32), 32, 'YES',
                                      'NO')),
   decode(c.property, 0, 'NO', decode(bitand(c.property, 8), 8, 'YES',
                                      'NO')),
   decode(c.segcol#, 0, to_number(null), c.segcol#), c.intcol#,
   -- warning! If you update stats related info, make sure to also update
   -- GTT session private stats in cdoptim.sql
   case when nvl(h.row_cnt,0) = 0 then 'NONE'
        when exists(select 1 from sys.histgrm$ hg
                    where c.obj# = hg.obj# and c.intcol# = hg.intcol#
                      and hg.ep_repeat_count > 0 and rownum < 2) then 'HYBRID'
        when bitand(h.spare2, 64) > 0
          then 'TOP-FREQUENCY'
        when (bitand(h.spare2, 32) > 0 or h.bucket_cnt > 2049 or
              (h.bucket_cnt >= h.distcnt and h.density*h.bucket_cnt < 1))
            then 'FREQUENCY'
        else 'HEIGHT BALANCED'
   end,                                                          /* HISTOGRAM */
   decode(bitand(c.property, 1024), 1024,
          (select decode(bitand(cl.property, 1), 1, rc.name, cl.name)
           from sys.col$ cl, attrcol$ rc where cl.intcol# = c.intcol#-1
           and cl.obj# = c.obj# and c.obj# = rc.obj#(+) and
           cl.intcol# = rc.intcol#(+)),
          decode(bitand(c.property, 1), 0, c.name,
                 (select tc.name from sys.attrcol$ tc
                  where c.obj# = tc.obj# and c.intcol# = tc.intcol#))),
   decode(bitand(c.property, 17179869184), 17179869184, 'YES',
          decode(bitand(c.property, 32), 32, 'NO', 'YES')),
   decode(bitand(c.property, 68719476736), 68719476736, 'YES', 'NO'),
   decode(bitand(c.property, 137438953472 + 274877906944),
                 137438953472, 'YES', 274877906944, 'YES', 'NO'),
   decode(c.property, 0, 'NO', decode(bitand(c.property, 8796093022208),
                                      8796093022208, 'YES', 'NO')),
   ...


There is a bit of advice on the internet that suggests for Server 2012 and SQL 2012 I should stick to NTFS e.g. itknowledgeexchange) as database commands like DBCC CHECKDB don't work.

This is no longer true with SQL Server 2014 and is fully documented in KB 2974455

  This problem is resolved in Microsoft SQL Server 2014. Beginning in this version of SQL Server, the DBCC CHECKDB command does not use named streams to create the internal snapshot database.

In fact, In-Memory OLTP (Hekaton) is supported as well (initially it had problems), but I would not recommend to put your mission critical database servers on ReFS (it's new and there might be bugs that people have not yet discovered as opposed to NTFS which is around since ages) - check

SQL Server AND REFS Part 1 and 2 by @swasheck

Adding for completeness :

Bob Dorr - Principal SQL Server Escalation Engineer recently published a blog post on this:

Sparse Files – Supported on both NTFS and REFS


Benchmarking or Tuning tool?

IMHO, there's no tool that will be specific to the latter unless you have a super generic usage. You need to identify your usage pattern and tune your database hosts to accommodate. If you're write-heavy, you will have a different configuration than a read-heavy scenario. Bottom line, your tuning follows your applications usage.

For benchmarking, I use Sysbench. Here's an example from my blog.

Added:
Here's my beef with config tools: With the changes in versions 4.x vs 5.x vs 5.5.x there are a lot of tweaks that don't jive between them. So you really need a savvy DBA to evaluate what's going on for each host. Load, storage, traffic, application specific requirements, there's a lot that can go into an optimal configuration. A tool may get you part of the way, but may leave out something or include something that may cause a failure. From buffers to flushing to plugins to threading, a config tool may provide you with false confidence that you are implementing the correct config.


When were OLEDB based linked servers added to SQL Server?

I believe this was introduced in SQL 7. If memory serves me right, I was able to access a SQL 6.5 server from a SQL 7 server, but I could not access SQL 6.5 from/to another SQL 6.5 (I don't think the sp_addlinkedserver stored procedure existed prior to SQL 7).


Why did adding a self-referential foreign key freeze the table?

The quick thing to try is set lock_timeout=1000;, then try your ALTER a bunch of times. Most likely you'll hit the timeout each time, but you might get lucky and that'll save you from having to hunt down the actual fix, which is, I'm sorry to say, not nearly as easy.
Almost certainly what is going on is that the other processes keep their transaction open after performing their SELECT on register. After that first SELECT is performed, the transaction will have an ACCESS SHARE lock on that table until the transaction commits or rolls back.
Unfortunately, ADD COLUMN requires an ACCESS EXCLUSIVE lock - regardless of whether there's a foreign key, self-referential or not, involved. And, per the table of conflicting lock modes, ACCESS EXCLUSIVE does conflict with ACCESS SHARE.
You can poke into which locks specifically are involved with pg_locks, and it's useful to join pg_stat_activity to see the actual queries. What follows is 3 psql sessions, interleaved, so you can see what's going on.
-- psql session 1: what the always-reading application code is doing
testdb=# BEGIN; SELECT * from register where id=10;
BEGIN
 id | name
----+------
 10 | bar
(1 row)

testdb=# -- now going to psql session #2.

-- psql session 2: look at the locks from the SELECT's still-open transaction.

testdb=# select relation::regclass, case when granted then mode else concat(mode, '*') end as mode, query from pg_locks join pg_stat_activity on pg_locks.pid=pg_stat_activity.pid and pg_locks.pid<>pg_backend_pid() where pg_locks.locktype='relation';
   relation    |      mode       |                query
---------------+-----------------+-------------------------------------
 register      | AccessShareLock | SELECT * from register where id=10;
 register_pkey | AccessShareLock | SELECT * from register where id=10;
(2 rows)

-- psql session #3: perform the ALTER
testdb=# begin; ALTER TABLE register ADD COLUMN leader_id integer;
BEGIN
-- is now hanging on the ALTER - back to session #2

testdb=# select relation::regclass, case when granted then mode else concat(mode, '*') end as mode, query from pg_locks join pg_stat_activity on pg_locks.pid=pg_stat_activity.pid and pg_locks.pid<>pg_backend_pid() where pg_locks.locktype='relation';
   relation    |         mode         |                       query
---------------+----------------------+----------------------------------------------------
 register      | AccessShareLock      | SELECT * from register where id=10;
 register_pkey | AccessShareLock      | SELECT * from register where id=10;
 register      | AccessExclusiveLock* | ALTER TABLE register ADD COLUMN leader_id integer;
(3 rows)

As you can see, the ALTER TABLE is trying, and failing (I add an asterisk when pg_locks.granted is false) to get an AccessExclusiveLock on the register table.
At this point, your application is going to start having problems. Let's open up a 4th psql and try another SELECT:
testdb=#  BEGIN; SELECT * from register where id=12;
BEGIN
--- aaaand... the SELECT is hanging now too! Let's go back to #2 and check the locks again

testdb=# select relation::regclass, case when granted then mode else concat(mode, '*') end as mode, query from pg_locks join pg_stat_activity on pg_locks.pid=pg_stat_activity.pid and pg_locks.pid<>pg_backend_pid() where pg_locks.locktype='relation';
   relation    |         mode         |                       query
---------------+----------------------+----------------------------------------------------
 register      | AccessShareLock      | SELECT * from register where id=10;
 register_pkey | AccessShareLock      | SELECT * from register where id=10;
 register      | AccessExclusiveLock* | ALTER TABLE register ADD COLUMN leader_id integer;
 register      | AccessShareLock*     | SELECT * from register where id=12;
(4 rows)

PG, seeing that there's an already-waiting ACCESS EXCLUSIVE lock on the table, will no longer grant additional ACCESS SHARE locks on the table until the ACCESS EXCLUSIVE has finished it's thing and released the lock. So now your SELECTs are piling up and everything is stuck until all of the transactions that had selected from register as of when the ALTER TABLE first tried to grab the lock either commit or rollback.
The fix here, alas, is "don't do that"; the application code needs to issue a COMMIT or ROLLBACK (as long as the SELECT is the only thing that has occurred in the transaction, it doesn't matter which) right after the SELECT, so that the ACCESS SHARE lock is released immediately. You could also split the ADD COLUMN and ADD FOREIGN KEY steps since only the ADD COLUMN requires ACCESS EXCLUSIVE (ADD FOREIGN KEY requires only SHARE ROW EXCLUSIVE, which does not conflict with ACCESS EXCLUSIVE), but I'd be surprised if that helps at all; it's the locking sequence that is causing issues, not the bit of extra work that is involved in doing the ADD FOREIGN KEY.


Does the SQL Server Service Account have to have access to a network share to perform a backup?

If you're performing native SQL Server backups, the service account running the SQL Server database engine must have access to the backup location. The Agent job can run under a proxy to fire off the job itself, but the engine still performs the backup so still needs access.

The only way to have the backup write to disk as another user would be to use a third-party tool that receives the backup data through the SQL VDI interface and writes it to disk as the appropriate user.

From Docs:

  Ownership and permission problems on the backup device's physical file
  can interfere with a backup operation. SQL Server must be able to read
  and write to the device; the account under which the SQL Server
  service runs must have write permissions.

For managing the files after they've been backed up, you can use a SQL Server Agent proxy for this to ensure you're using the correct account for file system access.


What is probably happening is that you have a router that doesn't support NAT loopback or the router configuration isn't properly set up.

You can connect from the outside as proven by the fact that canyouseeme.org sees you, and you can VPN into it because TeamViewer connections come from TeamViewer servers (hence the outside), and you can also connect to the internal IP.

If you want transparent connections from the inside and the outside you would need to either set up DNS, so that in your internal LAN the hostname.yourdomain.org points to the internal IP and make sure that from the outside the same hostname points to your external IP or get a router that supports NAT loopback.

There is plenty to find if you google 'nat loopback connect to external ip from lan' or you might find this post on serverfault interesting: Loopback to forwarded Public IP address from local network - Hairpin NAT


Can't get users default schema to change - SQL Server 2008

You have to migrate all the users from the original instance using the procedure sp_help_revlogin.


DynamoDB: searching with primary key or range key, which way is faster?

Are you trying to determine if to query only using the hash OR only the range key? Unless I'm misunderstanding your question, you are not able to to perform a query with just the range keys. For example, if your range key was the date, you cannot query with just two dates, you MUST also specify the hash key. Remember when using both hash and range keys, the primary key IS both the hash and range, however the range can be optional.

With that said, I suggest specifying the facebook_id as the hash and your user_id as an attribute not the range. Use this if only your logic is using the facebook_id as a lookup id to your application specific user_id.


PostgreSQL - Best way to incremental export 1000+ tables every 5 mins

In the central database, define foreign tables using postgres_fdw that point to the other databases. For each table, remember the timestamp from the last time you fetched data. Then regularly run
INSERT INTO localtable
SELECT * FROM foreigntable
WHERE last_upd_ts > ...
ON CONFLICT ON (...)
   DO UPDATE ...


How to Manage CPU Usage in Oracle Database 11g using Instance Caging?

Instance caging is a feature in the Enterprise Edition of Oracle Database 11g Release 2 (11.2) that simplifies the management of CPU usage in consolidation environments. By enabling Resource Manager and setting the CPU_COUNT parameter in each instance, you can limit the maximum amount of CPUs/Cores the instance can use. You have to notice three points for this.

1. Enabling Resource Manager
2. Setting CPU_COUNT
3. Monitoring Instance Caging

1. Enabling Resource Manager
Resource Manager has been available in the Oracle database.
Resource Manager is not enabled by default, so it must be enabled by specifying a resource plan before instance caging can take effect.

 SELECT plan FROM dba_rsrc_plans;

  PLAN
 ------------------------------
 DEFAULT_PLAN
 INTERNAL_QUIESCE
 INTERNAL_PLAN
 APPQOS_PLAN
 DEFAULT_MAINTENANCE_PLAN
 ETL_CRITICAL_PLAN
 MIXED_WORKLOAD_PLAN
 ORA$AUTOTASK_SUB_PLAN
 ORA$AUTOTASK_HIGH_SUB_PLAN
 DSS_PLAN

 10 rows selected.
 SQL>

If you have no specific resource management needs within the instance, the simplest solution is to use the default plan.

   ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = default_plan;

2. Setting CPU_COUNT

ALTER SYSTEM SET cpu_count = 2;

3. Monitoring Instance Caging

The throttling effect of Resource Manager can be displayed using the CONSUMED_CPU_TIME and CPU_WAIT_TIME columns of the following views.
The CONSUMED_CPU_TIME is the number of milliseconds of CPU time consumed by the consumer group, while the CPU_WAIT_TIME is the time waiting for CPU due to Resource Manager throttling.

SELECT name, consumed_cpu_time, cpu_wait_time
FROM v$rsrc_consumer_group;

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SET LINESIZE 100
SELECT begin_time,
   consumer_group_name,
   cpu_consumed_time,
   cpu_wait_time
FROM   v$rsrcmgrmetric_history
ORDER BY begin_time;