Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

1/ If after upload you do not update the data, only delete the rows older than 30 days.

2/ And if you can sort your file for the upload by Date, Area, Block

=> Then a clustered index on Date,Area,Block will greatly benefit your aggregations on Date, Date + Area, Date+ Area+Block .

It will also help any request with a where condition on Date, Date + Area, Date + Area + Block , whatever the selected columns for the request.


Convert all ntext columns to nvarchar(max)

While if you get this sorted out before you read my answer, making it unlikely to be useful for your particular case (unless you need to do this in multiple databases), I prefer string concatenation over cursors for this type of work. Primarily because you can print the string instead of executing it, verifying that at least the first 8K worth looks proper and correct.

DECLARE @sql NVARCHAR(MAX) = N'USE Eventlogic;';

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' ALTER COLUMN ' + QUOTENAME(c.name)
  + CASE WHEN c.system_type_id = 99 THEN ' N' ELSE ' ' END
  + 'VARCHAR(MAX)' + CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE '' END
  + ';
  UPDATE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    + ' SET ' + QUOTENAME(c.name) + ' = ' + QUOTENAME(c.name) + ';
  PRINT ''' + QUOTENAME(s.name) + '.' + QUOTENAME(REPLACE(t.name,'''',''''''))
    + '.' + QUOTENAME(c.name) + ' ('
    + CONVERT(VARCHAR(12), COUNT(*) OVER(PARTITION BY c.[object_id]))
    + ' columns)'';'
FROM sys.columns AS c
INNER JOIN sys.tables AS t
  ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE c.system_type_id IN (35,99);

SET @sql += N'
PRINT ''Total columns updated: ' + CONVERT(VARCHAR(12), @@ROWCOUNT) + ''';';

PRINT @sql;
--EXEC sp_executesql @sql;

When you're happy with the PRINT output, uncomment the EXEC and run it again.

Jon Seigel's script offers additional functionality (dealing with columns that participate in FULLTEXT indexes).


Best is to to : show grants for user@'10.%' ;  each user on the original server.
Then apply them to the new.

Or you can use pt-show-grants -hhostname -u -p which will list all the grants.
You can do that in one line of code:

pt-show-grants -horigalHost -u -p | mysql -hnewHost -u -p


If you do AlwaysOn with readable secondaries you don't have to backup and restore every night, SQL Server will send all transactions to the secondary as they are executed.

You will also need to make sure the database drivers your application uses support read intent. I'm saying this because I've worked in an organization that used Java and the particular Java drivers we used did not support this feature. There is also some development overhead since your developers will have to create a read-only connection separate from the connection that writes and changes data. I've never used this in production, but I've done some testing about a year ago with MS Access and this is how I had to set it up.

You can always use SQL replication. I've worked with transactional and snapshot replication and in some cases prefer it to readable AlwaysOn secondaries. The reason being is that I can customize indexes on a replicated databases separate from the writable copy of that database. And this is a good solution if you have users running queries directly against SQL server.

I've set up SQL servers that do nothing but run job, but there is still overhead on the servers where you're reading from and changing data on.


why pg_restore ignores --create ? Error: failed: FATAL: database "new_db" does not exist

This is because it is the way pg_restore works.

pg_restore manual reads as follow :

  -C, --create
             Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before
  connecting to it.

  When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE
  commands. All data is restored into the database name that appears in the archive.

The -d will restore in the given database if and only if -C is not used. If -C is used, the database is used as a "launchpad", not as the destination.


Edit: I didn't notice that this has been suggested already in comments by ypercube. So crediting him here.

Ultimately its up to you and your exact requirements. But here are following suggestions:

SKUs does not exists if Products does not exist. In other words, you cannot make a SKU as a standalone entity. The basic entity therefore is Products. And as a Product can have multiple SKU, make this relationship many to many.
So if you follow point 1, You can safely remove shop_skus relationship because now, a shop can select products via shop_products and as products are in many-many relationship with SKU you can enable shop to pick suitable SKU directly using suitable query.


SET_AUTHENTICATION_FROM_WALLET call returns ACL error

Using a wallet requires an ACE (new name of ACL) as well.

SET_AUTHENTICATION_FROM_WALLET Procedure

  Usage Notes

  To use the password credentials in a wallet, the UTL_HTTP user must
  have the use-passwords privilege on the wallet.

Configuring Access Control to an Oracle Wallet

Example:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE (
  wallet_path => 'file:/home/oracle/wallet',
  ace         => xs$ace_type(privilege_list => xs$name_list('use_passwords'),
                             principal_name => 'YOUR_USER',
                             principal_type => xs_acl.ptype_db));
END;
/


Is there any Cross-platform SQL editors that allow joins from different databases?

This seems more like something you'd set up in the database itself, than via an external tool.

Since this is sql-server, for instance, you can use Linked Servers to accomplish this.


MongoDB transactions are always atomic on single document. However, if it involves multiple documents transactions are not atomic.

Coming to Bulk operations, there is no transactions concept. For example, you are inserting 100 documents in a collection, if 51st insert fails, MongoDB will not insert the remaining documents in the list as it executes the bulk operations serially.


Creating roles and granting rights to the role

A role can be granted to another role.
create role user_updates;
grant table_note_updates to user_updates;


Assuming you mean Microsoft SQL Server, yes you can apply a Filter on the Publication using that usrdate column on each Article you're replicating, but you'll need to add the Filter for each replicated article. Also there are some limitations (mentioned in the previously linked docs) and using Publication Filters aren't applied like regular SQL predicates so they may be less performant than doing it in SQL yourself.
You may be best off creating a series of Views on top of the Tables you want to replicate that apply the date filter as a predicate in SQL, which you probably can more easily script out than trying to add the Publication Filter to each Article.


Configuring Reporting Services - ExecutionLogDaysKept

When you install SSRS 2012 in SharePoint Integrated Mode it does not install an instance of SSRS on the server like it would if you installed it normally.  So you have to configure it via the SharePoint interface. This is the reason you did not see Reporting Services running on the server.


Is SQL Server 2012 Management Studio "Express" any different?

In the RTM release of SQL Server 2012 (and in previous versions), yes, they are quite different. The Express version was missing a bunch of functionality, most notably the ability to manage SQL Server Agent (even on non-Express instances). There are other things missing like Profiler but technically that is not part of Management Studio itself, just part of the suite of client tools.

Starting with SQL Server 2012 SP1, however, Management Studio Express now has full parity in terms of feature set. Essentially this means that full Management Studio functionality no longer requires a license. You can download the more recent version, SP2, here:

http://www.microsoft.com/en-us/download/details.aspx?id=43351

You want the file SQLManagementStudio_x64_ENU.exe or, if you're really old school, SQLManagementStudio_x86_ENU.exe.

As with previous versions, to get Management Studio Express, you'll need either Express with Tools, Express with Tools and Advanced Services, or stand-alone Management Studio Express.

I don't have a system handy without Management Studio proper to test this on, but they've blogged about this on the Internet (it was first announced with CTP4 back in September), so it must be true:

http://blogs.msdn.com/b/sqlreleaseservices/archive/2012/09/20/sql-server-2012-sp1-ctp4-now-available.aspx

I'm going to spin up a VM and install it and come back here and enumerate any differences I find. Unfortunately this is one of those cases where things have changed and pointing to a "duplicate" question doesn't help, since it deals with an older version...


Dropping the automatically created NT AUTHORITY and NT SERVICE accounts

Before you downvote, here is formal documentation you can reference for a legitimate reason why these accounts would be scripted as you see: STIG Rule SV-53421r2_rule.  These controls are SQL Server version specific, but there are a number of other controls in the more recent versions as well.  If you work for an organization that falls under these controls and also adheres to some of the more stringent ones, such as revoking default Grants given to the public role things can get complicated pretty quickly.

As I have some experience in an environment that falls under these controls, I can say that you can disable/drop both the NT SERVICE\SQLWriter and NT SERVICE\Winmgmt accounts outright, but you need to make sure your SQL Server Service is running under an appropriate service account with sufficient OS level permissions, such as a sufficiently locked down Domain Service Account, or even better, standalone or group Managed Service Accounts.  Again, OS permissions need to be treated with care as this does venture into house-of-cards territory if you don't perform sufficient testing.

As for the NT AUTHORITY\SYSTEM account, this is NOT an account you want to drop if you are going to be running any Availability Groups.  In fact, STIG SV-93835r1_rule mentions that you should keep it with only CONNECT SQL permissions granted by default.  In the event that you have an Availability group, this account will also need to have the following, additional permissions:

ALTER ANY AVAILABILITY GROUP
VIEW SERVER STATE
EXECUTE ON [sys].[sp_server_diagnostics]
EXECUTE ON [sys].[sp_availability_group_command_internal]

These restrictions can be a royal pain, but there are legitimate reasons you would need to limit their use and permission sets.  If you don't fall under these guidelines, do yourself the favor and comment out those steps.

Hope that helps!


sql server configuration manager says it is on a 32 bit machine and it is not. Should we change that?

It is misleading and according to some post it is a bug. You cannot change it.

Here is the connect item.

https://connect.microsoft.com/SQLServer/feedback/details/332566/in-configuration-manager-running-under-64-bit-os-proprty-is-set-as-no-where-it-is-installed-on-x64

See the response from Microsoft's Sam Lester.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e83b5ded-8cda-468d-ac36-1b8c31b1073b/running-under-64bit-os?forum=sqlsetupandupgrade

  It turns out that the label is very confusing.  There is a bug filed
  on it in the SQL bug database and it will hopefully get fixed in the
  future to make this clearer.  It turns out that this field shows if
  you are running a 32-bit version of SQL under a 64-bit OS (as in WoW).
  I had an X64 machine here with a 64-bit instance of SQL and a 32-bit
  instance of SQL.  The field shows "No" for the 64-bit version of SQL
  and "Yes" for the 32-bit version of SQL.  A bit misleading, but
  "correct" if you look at it in this way.  :)


If you use Innodb as format databases, you can set a buffer limit.
exemple for 4G ram :
[mysqld]
innodb_buffer_pool_size=3G
innodb_io_capacity=6000

source : https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html


Cannot connect to SQL Server after setting sa password to blank

This error does not sound like a problem with the password at all, but rather a connectivity issue. It could be that some policy is re-establishing firewall rules, or it could be simply that you have flaky DNS, an unreliable connection to the domain controller, or some other network issue.

As @Thomas pointed out, the "pipe" in the error message implies that this is a named pipes error. In most cases I have tended to avoid named pipes altogether. On the server where the SQL Server instance resides, you should go to SQL Server Configuration Manager and experiment with disabling named pipes and making sure TCP/IP is enabled:

Then you'll want to make sure that your app's connection string uses an IP address if feasible or edit the hosts file so that the name resolves to an IP locally, instead of using a physical machine name or FQDN. Doing either of these eliminates your dependency on external name resolution from a domain controller (but obviously won't be a good idea if you are using DHCP for some reason).

Of course you may also want to review network logs (or set up some kind of network monitor) to determine if there is some issue outside of this, and make sure that neither machine has a firewall enabled (or at least ensure that it is open on the right ports and no group policy is coming in and "fixing" that). But these changes will be better for your environment even if they don't solve this immediate issue.

As an aside, you absolutely must fix that application's connection string anyway. A blank sa password is like leaving your wallet at the bus station on purpose.


Is Database Backup Time = Restore Time in SQL Server?

Reference:

SQLskills SQL101: Why is restore slower than backup
Backup Time vs Restore Time
How long will this sql server restore take?

  Does this mean backup and restore times of a SQL Server database are
  always equal, or can they differ?

They are not equal and yes they can (and most likely always) differ.

  Kindly suggest and point me the logic behind.

If you read the article from SQLSKills Paul Randal gave high level steps performed during a backup and restore. Which should explain why timing for these two operations are not same.


What to consider when setting flush_interval_seconds in SQL Query Store?

Is query store data written to the log? If so would a restore let us recover any Query Store data, that had not been pushed to disk?

Query Store is persisted to disk, so it survives a crash. But Query Store is also Async, it means if the data has not been persisted and a crash happens in the meantime, the data will be lost. Although, if SQL Server must be restart because of a maintenance routine you could force Query Store to be flush using the procedure: sp_query_store_flush_db.

  If I have an AlwaysOn AG, do the secondaries get the Query Store data, real time or are they dependent on it actually getting written to disk on the primary? Rephrased if my Query Store data has not been written on the primary for 15 minutes, and the primary gets unplugged will the secondary capture it?

Secondary replicas get the query store data from PRIMARY ONLY. You cant do force plans,etc on secondary. Since data is written in Async fashion, there will be data loss.

  Can a Query Store disk write have wait conflicts with users OLPT data? If so what are they?

I dont get what you mean by wait conflicts. I have not see any conflicts on a very busy OLTP system handling 60K trans/sec.

  What does changing the flush_interval_seconds value really do?

As per BOL - Defines period for regular flushing of Query Store data to disk. e.g. how often the data from memory is flushed onto disk automatically rather than you running sp_query_store_flush_db manually.


It is not possible to restore a single table from an earlier point in time directly into an existing database.

You can restore all or part of the database as a new database, then copy the required data across. Or, you can restore using the REPLACE option, but the whole database will be reverted to the earlier point in time, not just the table you are concerned about.

If all your data is stored in the PRIMARY filegroup (the default), you have no choice but to restore the whole thing. If you have multiple filegroups, you may be able to restore just some of the filegroups, reducing the space needed. The details depend on the database recovery model as well as how any filegroups are arranged, see:

Piecemeal Restores (SQL Server 2012)

You can determine in advance how much space will be needed for the restore by running the following command and inspecting the output:

RESTORE HEADERONLY (Transact-SQL)

The paths to the files that will be written are all from the perspective of the SQL Server instance, not the user performing the restore. You will need to ensure that enough space is available for all the files that will be restored. Note that the size of the file may be larger than the amount of data stored in the file, and larger than the size of the backup file. SQL Server only writes the data actually stored to a backup, which may also be compressed, but a restore must create the full file size.

The process of creating the data files will be much faster if Instant File Initialization is enabled on the restore target.

Backup and restore is a large topic, well covered in the documentation, see:

Back Up and Restore of SQL Server Databases

The T-SQL RESTORE command offers more flexibility and control than using the SQL Server Management Studio UI.

If you have differential and/or transaction log backups as well as a full database backup, you may be able to apply one or more of these to get closer to the point in time when the accidental truncation occurred.


Assembly 'system.web, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog

You can either use Uri.UnescapeDataString (in System), in which case you will also need to do a Replace('+', ' ') on the string before passing it to Uri.UnescapeDataString, or if you would rather not bother with it, this function is available in the Free version of SQL# (which I am the author of).

Importing System.Web is probably more work than it's worth. And in fact, it can be risky.  There is a good reason that System.Web is not in the "Supported Libraries" list that you linked to in the question: it is not guaranteed to work! You might run into situations, especially when dealing with non-US ASCII character sets, that do not behave as expected, and Microsoft will not fix it. So, unless you absolutely have to, you should be careful about adding non-supported DLLs. The DLLs in the "Supported" list have been fully tested and verified to work with SQL Server collations and any other environmental issues that are different between standard CLR running in Windows and the CLR running inside of SQL Server.

Here are some additional resources from Microsoft regarding several pitfalls to incorporating unsupported .NET Framework libraries:

Support policy for untested .NET Framework assemblies in the SQL Server CLR-hosted environment
Error message when you execute a CLR routine or use an assembly in SQL Server: "Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)"

A few notes about your code:

Please do not use .NET types for parameters, input or output. Hence, change string encodedXML to be SqlString encodedXML.
This function is deterministic so should be marked as such, else you will suffer a performance hit. Add IsDeterministic = true to the SqlFunction attribute.


Is PostgreSQL 9.2 autovacuum sufficient for a busy table?

Before you fiddle with cron jobs and other curiosities you can adjust the autovacuum default settings just inside PostgreSQL. This can be done globally or individually for each table.


How do I validate that my purge hasn't deleted the wrong data?

Declaring foreign keys would be a great start, but I think you know that. While they will ensure what is in the database is consistent they will not ensure it is correct. In other words they can't stop you deleting something that should be kept. For that you need to program the "should be kept" rule somewhere. You've put the "should be removed" rule in stored procedures. I think putting the "should be kept" rule in triggers will give an additional level of assurance.

In past purges we have implemented extensive reconciliation scripts. Counting the rows is a good start. Summing values by multiple combinations of keys is better. SQL Server's GROUPING SET syntax makes this not much less efficient than a simple count. Perform these aggregates before the purge with only the retained rows in the WHERE clause. Preform them again afterwards with no WHERE clause. If too many or too few rows were removed the before and after numbers will be different. Because you summed by lots of different keys it will be easier to identify the offending source rows.

Given we accept an error can occur, it is possible that error will delete some number of rows it shouldn't and leave the same number of rows it should. Unlikely, but possible. By summing the error has to remove / leave exactly balancing rows. This is even less likely. By summing over multiple keys the chances are further reduced. This can be continued until an arbitrary level of assurance is achieved.

I would not suggest deploying this against production, at least not initially. A copy will suffice to perform the cross-checks, and to validate the validation code. If you can confirm here that the deleting procs are working as intended you have confidence to deploy them in production without the extra checks.


Unable to restore a PostgreSQL database from a .backup file

An alternartive is using pg_restore command


Oracle 12c Client Compatible with 9i Server?

This is from behind the Oracle registration wall:

Client / Server Interoperability Support Matrix for Different Oracle Versions (Doc ID 207303.1)

Client Version: 12.2.0 or 12.1.0
Server Version: 9.2.0
Supported: No #3

Where:

#3: An ORA-3134 error is correctly reported when attempting to connect to this version.

So I would not expect it to work at all.


ALTER TYPE Not Available So How To Alter a Type?

Simplest would be to create a new type with the updated definition, then you can change the affected objects one at a time (or all at once) to reference the new name. You can’t pull the tablecloth.

I wrote an article about this here:

How to Alter User Defined Table Type in SQL Server

The fundamental process is:

create a new type
determine which objects reference the old type
change those objects manually to point at the new type (or at least those objects that need to use the new type)
optionally, drop the old type

The tricky part is #2, people will hunt and peck manually, or try to parse OBJECT_DEFINITION or sys.sql_modules, but the following is a bit more reliable:

SELECT s.name, o.name, def = OBJECT_DEFINITION(d.referencing_id)
  FROM sys.sql_expression_dependencies AS d
  INNER JOIN sys.objects AS o
     ON d.referencing_id = o.[object_id]
  INNER JOIN sys.schemas AS s
     ON o.[schema_id] = s.[schema_id]
  WHERE d.referenced_database_name IS NULL
    AND d.referenced_schema_name = N'dbo'
    AND d.referenced_entity_name = N'MyType';


From my experience with MYISAM (I am still trying to find documentation that supports it), it is safe to manually delete all three table files (.MYD, .MYI, .frm) and that's it - the table is dropped.

After doing that on a test table, MySQL could not find it anymore (SELECT * FROM test returned an error saying the table does not exist), and I was able to CREATE TABLE test again, so it seems that nothing remains from the original table.

I would recommend the following:

Delete the three files.
Try dropping table table again (this will
probably return an error, hopefully 'table does not exist'.

Good luck.


Can't convert this type of ER diagram into SQL or relational tables

I will agree that @MladenUzelac's approach is probably the more common pattern for this situation, but there is a potential theoretical flaw that can expose itself over time and make life difficult. That flaw is: a Contract, at the end of the day, is not an Annex. Of course, I emphasized "potential" because currently I am not privy to whether or not they are currently considered the same thing (i.e. Contract === Annex). However, to a degree that would not change my recommendation to find out that they are the same. Anything is subject to change at any point in time, even if highly unlikely, so having the same nature today does not imply that they will always have the same nature, and often enough things do grow in different directions. So yes, they share the same attributes today, and they share the same relationship with Dynamic of Payment today; but tomorrow is another day and the nature of things changes, even for irrational reasons ;-).

Don't get me wrong: there definitely are times when a self-referencing entity is the appropriate solution, but I think that requires that the entities be of the same nature. The two most common examples I can think of are:

Employee / Manager: It makes sense to have a [ManagerID] (or I prefer [ManagerUserID]) field in the [Employee] table that is a self-referencing FK back to [UserID]. This works because a manager is an employee.
Departments: In corporate / retail hierarchies, it makes sense to have a [ParentDepartmentID] field in the [Department] table that is a self-referencing FK back to [DepartmentID]. This works because the "parent" Department is a Department.

In both of these cases, the relationship between parent and child is not a primary determining property of those entities. Instead, the relationship is a property just like the name of the entity. In both cases, the relationship is not critical to the existence of the child entities, and the entities defined as "children" exist even without the relationship. On the other hand, if an Annex requires a Contract in order to exist, well, that is something quite different.

What it comes down to is: just because two things look the same does not mean that they are the same.

Storing two "similar yet different" entities together, while reducing some amount of tables / joins / code, leads to some pragmatic concerns, even if only in terms of physical storage (i.e. the DB layer).

I have worked for years on a system, built before I got there, in which this pattern was used several times and for situations that were nearly identical to what is being described here. I am not sure if the entities had the exact same attributes on day 1, but over time at least, they began to diverge and take on different properties from the other. In some cases fields were added that were appropriate to one of the entities but not the other, so we just had to know when a NULL field was appropriate for that type of row, or if it was a bug. Other times a "multivalued attribute" approach was taken with the structure being effectively: [EntityID], [AttributeID], [AttributeValue].

As expected, in order to get a list of "parent" entities we would query with WHERE ParentEntityID IS NULL. And to pull a list of "child" entities we would query with WHERE ParentEntityID IS NOT NULL. It got fun when we needed both entities because they were really a property of another primary entity, so we needed to show both as separate properties of the primary entity. What was fun was how that self-referencing join hurt performance (though it is possible that a slight change in indexing strategy could have fixed that). But it got super-duper fun when we placed that EntityID in other entities as FKs, and we only wanted to reference one of those two entity types, but it was not always guaranteed that we wanted the "child" entity (though that was the most common entity to refer to). In the end, as I started creating new tables and refactoring old ones, I included the entity name in the name of the FK field in the related entity, just to have it "self documented" for anyone looking through the tables needing to create new queries. And in some cases I added two FK fields, one for each entity type, just to avoid that costly self-join (and it was safe to denormalize since that parent-child relationship could never change once created).

So, I would start out heading in the same direction as @Peter in terms of separate tables for Contract and Annex (even if they are identical, or nearly identical, structures), but I would approach the handling of their relationship to Dynamic of Payment differently. I seem to recall having tried the two different FK fields thing in the past and not really liking how it played out. At the very least it seems less manageable over time as other entities are added that can relate to Dynamic of Payment a you need to keep adding FKs back to new parents. And you also need to have a CHECK CONSTRAINT on the table ensuring that one, and only one, of those FK fields is NOT NULL.

Instead, I would create a separate bridge / relationship table for each of Annex and Contract to relate to Dynamic of Payment. Yes, when new entities are added that can relate to Dynamic of Payment you will need to add new bridge / relationship tables, but somehow I feel better about that because it is not altering the structure of Dynamic of Payment: the Dynamic of Payment entity is the same whether 1 or 100 entities relates to it.

(the SQL below is use Microsoft SQL Server T-SQL semantics)

Main Entities

CREATE TABLE SchemaName.[Contract]
(
  ContractID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  ContractName VARCHAR(50) NOT NULL,
  ContractDetails NVARCHAR(MAX) NULL
);

CREATE TABLE SchemaName.Annex
(
  AnnexID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  ContractID INT NOT NULL REFERENCES SchemaName.[Contract] (ContractID),
  AnnexName VARCHAR(50) NOT NULL,
  AnnexDetails NVARCHAR(MAX) NULL
);

Dynamics of Payment

The [DynamicsOfPaymentType] table is a lookup table that assumes that the "dynamics" are named properties, possibly mimicking an enum in the app layer. I chose TINYINT (0 - 255) as typically most things have less than 255 distinct properties / attributes. If needing more than 255 then go up to the next higher size. But don't go higher than you need as that field is copied into the relationship tables which can get large, especially with lots of Contracts and Annexes that each have several DynamicOfPayment entries.

Please note that the PK on DynamicsOfPayment is a composite key made up of [DynamicsOfPaymentID] and [DynamicsOfPaymentTypeID]. While technically only the [DynamicsOfPaymentID] field is needed in the PK as it is guaranteed unique, having the [DynamicsOfPaymentTypeID] field allows us to push that property up to the relationship table for use in constraining each entity to having only one instance of any particular DynamicsOfPayment property. Please see notes in next section for more details.

CREATE TABLE SchemaName.DynamicsOfPaymentType
(
  DynamicsOfPaymentTypeID TINYINT NOT NULL PRIMARY KEY, -- SMALLINT if > 255 are possible
  DynamicsOfPaymentType VARCHAR(50) NOT NULL
);

CREATE TABLE SchemaName.DynamicsOfPayment
(
  DynamicsOfPaymentID INT IDENTITY(1, 1) NOT NULL,
  DynamicsOfPaymentTypeID TINYINT NOT NULL REFERENCES
                          SchemaName.[DynamicsOfPaymentType] (DynamicsOfPaymentTypeID),
  Value NVARCHAR(MAX) NULL,
  PRIMARY KEY (DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
);

Main entity to Dynamics of Payment relationships

Please note that the PK of both relationship tables is a composite key made up of the main entity ID and the [DynamicsOfPaymentTypeID] field--not the [DynamicsOfPaymentID] field. The reason, as mentioned in the prior sections notes, is to enforce a single instance of any particular DynamicsOfPayment property for each main entity. If an entity can have multiple instances of a DynamicsOfPayment property, then it is a simple matter of adjusting the PK on that particular relationship table to be a composite key made up of the main entity ID and the [DynamicsOfPaymentID] field (or if you prefer, both the [DynamicsOfPaymentTypeID] and the [DynamicsOfPaymentID] fields). As you can see, this model can allow for not only the relationships to enforce singular instances of related properties OR for the relationships to accept multiple related properties, but it also allows for mixing the two as the relationship tables don't have to use the same combination of fields for their PK: some relationships can be constrained while others not. So maybe Annex is limited to a single instance of any particular DynamicsOfPayment property, but Contract is allowed to have multiple instances of a particular property.

CREATE TABLE SchemaName.[ContractXDynamicsOfPayment]
(
  ContractID INT NOT NULL,
  DynamicsOfPaymentID INT NOT NULL,
  DynamicsOfPaymentTypeID TINYINT NOT NULL,
  PRIMARY KEY (ContractID, DynamicsOfPaymentTypeID),
  FOREIGN KEY (ContractID) REFERENCES SchemaName.[Contract] (ContractID),
  FOREIGN KEY (DynamicsOfPaymentID, DynamicsOfPaymentTypeID) REFERENCES
              SchemaName.[DynamicsOfPayment] (DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
);

CREATE TABLE SchemaName.[AnnexXDynamicsOfPayment]
(
  AnnexID INT NOT NULL,
  DynamicsOfPaymentID INT NOT NULL,
  DynamicsOfPaymentTypeID TINYINT NOT NULL,
  PRIMARY KEY (AnnexID, DynamicsOfPaymentTypeID),
  FOREIGN KEY (AnnexID) REFERENCES SchemaName.[Annex] (AnnexID),
  FOREIGN KEY (DynamicsOfPaymentID, DynamicsOfPaymentTypeID) REFERENCES
              SchemaName.[DynamicsOfPayment] (DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
);

Pro Tips

Yes, there will be slightly more tables, joins, and app code using this model. But, just keep in mind that refactoring app code is much easier than refactoring tables, especially once the app is live and data is in there. Take the small hit of slightly more time now to code for this and save yourself many, many hours over the years of trying to make changes to simpler models, or as projects come up in a few years, just deciding to put in hacks / band-aids because the business won't go for the 10 - 20 (or more) hours it will take to make changes to the simplified model that saved you 1 - 2 hours now. Trust me, database programmers / architects are not cheap (at least not these days) and my employers have spent an egregious amount of time and money--in the form of my salary (and the salaries of my fellow DB coworkers)--for me to refactor poor design choices that saved maybe even 1 day of initial dev time, but over the next 3 years cost them a week (a week that I was not adding new features)
You can coordinate the cleanup of the relationship tables by marking the FK as ON DELETE CASCADE. The syntax might differ slightly between RDBMS's, but I would expect that most, if not all, of them support cascaded deletes. This way, if you delete a Dynamic of Payment entry, you don't need to worry about its entry in either of the relationship tables.
Depending on what RDBMS you use, you might even be able to coordinate the creation of the relationship records. Microsoft SQL Server has a very cool OUTPUT clause that returns the changes made by a DML operation. Hence, you can insert a record into [DynamicsOfPayment], and within the context of that operation (and hence, transaction), you can do the insert into the relationship table. Assume you have an insert proc that accepts an extra parameter for @ContractID that is not initially needed for the [DynamicOfPayment] table. It can be used as follows:

INSERT INTO SchemaName.[DynamicsOfPayment] (DynamicsOfPaymentTypeID, Value)
OUTPUT @ContractID,
       INSERTED.DynamicsOfPaymentID, -- server-generated auto-increment field
       INSERTED.DynamicsOfPaymentTypeID
INTO   SchemaName.[ContractXDynamicsOfPayment]
       (ContractID, DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
VALUES (@DynamicsOfPaymentTypeID, @Value);

Now you just need two stored procedures (since each one need to reference a different relationship table):

DynamicsOfPayment_CreateForContract (@DynamicsOfPaymentTypeID, @Value, @ContractID)
DynamicsOfPayment_CreateForAnnex (@DynamicsOfPaymentTypeID, @Value, @AnnexID)


You have to consider, basically two tradeoffs:

PROs

Using the "PostgreSQL users" instead of your own table(s) allows for very fine-tuning security privileges without having to add all this logic in your application. Consider whether you actually need all this finesse or not.

CONs

If you want to add extra information about a user (such as an email account to which you can send a restore password link or something similar) you still need to create a secondary table to store all of it [or use something like COMMENT ON ROLE role IS '{"email":"a@b.com", "comment:""structured text where you store all kinds of information"}', which I think denormalizes too much how you use your database].
Login roles are common to all databases within a PostgreSQL cluster, not just for a specific one. If you're hosting several of them simultaneously, this can artificially limit the usernames you can give to your users (because, maybe, you're already using the same username (=login role) for another DB for another purpose).

Alternative (having the best of both worlds)

You can have a mix: create one login role (or group role and login role) per each different role within your application, which allows for fine-tuning security; have one users table with a login role assigned to each. Your application should start with a role that can read this table, and after validation of credentials, disconnect from the database and reconnect using the proper login role. The login roles are internal to the application, and not visible to the users.


I found this script which lists LOB objects in each filegroup. Based on the results I was able to find the table still using the SECONDARY filegroup.

SELECT
    au.*,
    ds.name AS [data_space_name],
    ds.type AS [data_space_type],
    p.rows,
    o.name AS [object_name]
FROM sys.allocation_units au
    INNER JOIN sys.data_spaces ds
        ON au.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p
        ON au.container_id = p.partition_id
    INNER JOIN sys.objects o
        ON p.object_id = o.object_id
WHERE au.type_desc = 'LOB_DATA'


How do I update my database generated pg_basebackup through WAL files?

See the documentation on point-in-time recovery and WAL archiving for how to manage this.

Short version:

Set archive_mode = on in master
Set an archive_command in master that stores the WAL archives in a location accessible to the replica
Create the replica from a pg_basebackup
Create a recovery.conf on the replica that sets a restore_command to fetch the archived WAL. You may also want to set a recovery_target_time if you wish to stop replay at a certain point.
Start the replica and let it replay WAL

You may find tools like pgbarman useful as well.


Can running a query on a readable secondary slow down (block) the primary in AG

In Simple terms..

  Readers won't be blocked by DML operations,but there is a chance of blocking with DDL operations..

This not blocking  is achieved by mapping isolation levels to snapshot isolation levels.As a result of this,there will be row versioning over head,but there won't be any blocking by DML(Insert/Update/Delete)..

See below for more details on how Isolation levels are mapped in various cases

  What can happen is that the REDO-thread is blocked by a long running query => changes can't be applied to the secondary

When there is a blocking on secondary due to lock conflicts (thread trying to apply DDL to a page generated on primary ) and redo thread is blocked.The only impact i could see is log growth (depending on synchronous commit or asynchronous commit). But there will be blocking irrespective of sync model you choose.

SQL team, even tried with a design  option of killing read only query on secondary automatically,when there is blocking ,but went against the option..

Below is the explanation from Sunil Agarwal on the same

  When we were designing this feature, we thought about killing the redo work -- sorry, killing the reporting work automatically. As soon as redo is blocked, we'll say, okay, I'm going to kill the read workload, and the redo moves forward.
  The problem was if you were running a reporting workload that is very critical for now or your reporting workload was taking half an hour to run and you were almost like 39 minutes done and the blocking happens and if you kill it, it's not a good user story.So what we have done is we have exposed the right knobs and we let the customer decide what to do if the redo thread gets blocked.

so you will have to monitor Xevents to know about blockings and take decision

References  used:
AlwaysOn Solution Guide: Offloading Read-Only Workloads to Secondary Replicas
AlwaysOn: Impact of mapping reporting workload on Readable Secondary to Snapshot Isolation


According to the pg_hba.conf snippet, a password is required if you connect from ::1, which is the IPv6 address for localhost.

It may be that the box on which you have the problem has the resolver configured so that the name "localhost" refers to both 127.0.0.1 (IPv4) and ::1 (IPv6), so that the command psql -h localhost... may connect to one or the other address.

I'd suggest to check the /etc/hosts file for different localhost entries, or bypass the resolver by connecting to 127.0.0.1, or update the pg_hba.conf with "trust" for IPv6 local connections.


Since there is a limit on the length of the index, if you want to guarantee the uniqueness of the whole field's value, you may want to add a hash field of that varchar field.

Steps:

Alter the table to add an MD5 field: alter table table1 add md5_of_column char(32)
Update this field: UPDATE table1 SET md5_of_column=md5(column);
Add the unique index: ALTER TABLE table1 ADD UNIQUE (md5_of_column)
Either create a trigger, or do it at application level, to update this new field after each insert/update operation.

Note that this solution might have a light performance hit.


Set a column's default value to the concatenation of two other columns' values?

You don't need to actually store the value; you can create a function that can be referenced much like a generated column.  The one caveat is that references must always be qualified with the table or alias name.

CREATE TABLE person
  (
    id int PRIMARY KEY,
    first_name text,
    last_name text NOT NULL
  );

INSERT INTO person
  VALUES
    (1, 'John', 'Smith'),
    (2, 'Jane', 'Doe'),
    (3, NULL, 'Prince');

CREATE FUNCTION display_name(rec person)
  RETURNS text
  STABLE
  LANGUAGE SQL
  COST 5
AS $$
  SELECT
    CASE
      WHEN $1.first_name IS NULL THEN ''
      ELSE $1.first_name || ' '
    END || $1.last_name;
$$;

SELECT p.id, p.display_name FROM person p;

The results:

 id | display_name
----+--------------
  1 | John Smith
  2 | Jane Doe
  3 | Prince
(3 rows)

You can even index on the generated value, including using KNN searches based on trigram similarity.  For example:

CREATE EXTENSION pg_trgm;

CREATE INDEX person_trgm_name
  ON person
  USING gist
  (display_name(person) gist_trgm_ops);

SELECT
    p.id,
    p.display_name,
    similarity(p.display_name, 'Jane')
  FROM person p
  ORDER BY p.display_name <-> 'Jane'
  LIMIT 2;

This type of search returns rows from the index scan in order of "distance" from the search string.  If you want to see how "close" they were, you could either use the distance operator (<->) or the similarity() function (which is 1 - distance).  A KNN search can return the K "nearest neighbors" very quickly, even with a very large data set.


Memory Grant Warning on what appears to be a better query?

Actually, the warning for excessive memory grant uses an hardcoded logic. It raise an alert when a certain % of the allocated memory is not use but it does not considere the amount of wasted memory.

It cannot be "trusted" and you should not worry about it if, like in this case, it's only 1Mb that is allocated and not use.

I've seen cases where I got a warning for something like this (1Mb allocated, 1 Mb non-used) and cases where there was 256Mo allocated and like 136 Mo used (so 120Mo non-used) and there was no warning.


How do I find the least optimised query?

My relational algebra is pretty rusty but maybe I can put you on the right track. Start with a simpler problem: What if you were looking for book number "ZZZZ81-203-1257-0" which had no matches in the tables? Which query would be the least optimized then?

Let's randomly consider the third relational algebra expression as an example. You are doing a cartesian join between the member and lending tables so you'll end up with 1000 x 400 = 400000 rows as an intermediate result set. You then check the filter condition on each of the 400000 rows and don't keep any of them. Does that seem efficient or inefficient to you? How does it to compare to the others? Can you draw any comparisons between the reasoning that you used for this case compared to the problem that you're trying to answer?


Several problems with your approach:

adding a not null MAX column with default value. Make the column nullable and don't specify a default. Adding a default '' instead of letting it be nullable is incorrect semantically and a waste of significant space per-row. Make you code handle NULLs correctly.
adding a not null with default column to a table without any testing/preparation. This is a size-of-data operation as every single row in the table has to be updated. Has the potential to generate a huge amount of log because is as a single transaction. With a MAX type not even online column add in SQL Server 2012 could help, but again there is little point on having a MAX column not null with default ''.
even if the non nullable MAX with default is truly required, preparing for such an operation would require a mandatory evaluation of the log space needed (between 1.5 and 2.5 times the size of data) and pre-growth of the log file. Most likely, this is what most of the time is spent right now on, growing and zeroing the log.
doing it live.
asking for real-time advice on forums...

If you attempt to cancel the query you may or may not succeed, and the cancellation can be fast or can itself take another 25 minutes or more, depending on where is the current progress at (could be blocked on locks or could be progressing). In the later case resorting to desperation and attempting to restart the server will cause the DB recovery to last even longer.


Cannot Start/Stop Services using SSMS 2014 (but 2008R2 works)

I can't reproduce that problem here, but my first suspicion any time a menu option disappears or becomes disabled is permissions.

Since you've ruled out that your user can perform these features through other tools, then the difference must be the tool acting on your behalf. Right-click the SSMS shortcut (in some operating systems you'll need Shift+right-click) and choose Run as administrator.


Error 35338: Clustered columnstore index is not supported

SQL Server 2012 only supports the creation of nonclustered columnstore indexes. Change your code to:

CREATE NONCLUSTERED COLUMNSTORE INDEX cci_Simple ON [dbo].[Card]

In SQL Server 2012 this will make your table read-only. You should probably look at adding all three columns of the table to the columnstore index but I'll leave that to your testing.

Note that columnstore indexes also require Enterprise Edition.


PostgreSQL: result of range difference would not be contiguous

You could define your own functions range_add/range_sub (intersection doesn't need special handling):

select range_add('[1,5)'::int4range, '[8,10)'::int4range);
     range_add
--------------------
 {"[1,5)","[8,10)"}

select range_sub('[1,10)'::int4range, '[5,8)'::int4range);
     range_sub
--------------------
 {"[1,5)","[8,10)"}

You could define them like this:

create or replace function range_add(int4range, int4range) returns int4range[] as
$$
    select
        case
            when $1 && $2
            then array[$1 + $2]
            else array[$1, $2]
        end
$$
language SQL;

create or replace function range_sub(int4range, int4range) returns int4range[] as
$$
    select
        case
            when $1 @> $2 and not isempty($2) and lower($1) <> lower($2) and upper($2) <> upper($1)
            then array[int4range(lower($1), lower($2), '[)'), int4range(upper($2), upper($1), '[)')]
            else array[$1 - $2]
        end
$$
language SQL;

Unfortunately, PostgreSQL is missing native support for this set-based behavior.


Moving to MySQL 6.0 is highly regarded as a bad move by most people, as it only briefly existed on the control version system of MySQL A.B. plus some very unstable alpha releases. MySQL 6 promised too many features to be able to be delivered, although some of its features were later ported to 5.5 and 5.6. Others, like SQL-layer foreign keys or the Falcon engine never saw the light. The order is more or less:
5.0, 5.1, 6.0 (not released), 5.4 (not released), 5.5, 5.6, 5.7 (not yet released)

You can watch the story by yourself, recounted in first person by a former employee on this video.

Regarding the upgrade (assuming you really meant 5.6), having a minimal HA setup is a must. I wrote a more detailed guide on a previous question, but every setup is different, and all potential problems are too many to summary in an answer, even between 2 specific versions of MySQL.


Yes, I've got clients with 1k-10k databases per server. (Fog Creek is actually one of them, and I can mention it here because I know Joel's talked about it on the podcast.) Usually you run into problems at this scale, and you start thinking about moving down to VMs instead - it's easier to manage more virtual SQL Servers with less databases per instance.

Log shipping technically works, but you have to roll your own solution rather than use the built-in tools. The problem is that these types of businesses usually have new databases coming online all day long, at any time of day, and they need these new databases to be automatically protected by the log shipping. This means as soon as a database is created, you need a full backup taken, plus get it added into the log shipping setup. You'll be rolling your own scripts, and you can't just have one job. One job won't be able to keep up with the sheer number of databases if it backs up one database at a time - you end up with multiple jobs, each of which handles a range of databases or does something like the modulus of the database id.

SAN replication is much easier because it automatically protects any new databases that get added in. For critical databases, I recommend using a combination of VSS-integrated SAN snapshots, dirty (non-VSS) SAN snapshots, and regular SQL Server transaction log backups, but you mentioned you've got SIMPLE recovery mode and a 24-hour RPO/RTO. You'll be able to stick with a much simpler solution: dirty SAN snapshots plus conventional full backups.

The specifics will depend on your SAN vendor's replication options, but typically they can take a dirty snapshot without quiescing SQL Server's writes, then replicate that snapshot over to your DR site. Done correctly, this happens without impacting SQL Server performance. The problem is that without quiescing SQL Server's writes, you're rolling the dice on whether or not each database will come online. Odds are, when you fail over, you're going to have a handful of databases that don't recover correctly - no problem, that's where your full backups come in.

Every night, you should be doing full database backups to another location (not the SAN). That other location should also be replicated to your other site.

This way, when you fail over, most of your databases will come online instantly via the SAN-replicated copy. Some of them will not (usually a fairly small percentage). For those few databases, you restore the most recent full backup. End result: pretty quick recovery time objective at a relatively low complexity cost.

I would NOT recommend this solution if you need every database to be at the exact same moment in time, or if you needed up-to-the-second recoverability. (Just making it clear because some reader is going to freak out at this design - it's certainly not for everybody, just for this one specific business case.)


How to free up disk space to the OS, ideally without using SHRINKFILE?

First, I would confirm what the server admin is seeing, by running some free-space diagnostics on your SQL Server:

/* Get SQL Server Drive Usage Stats */
IF OBJECT_ID('master.sys.dm_os_volume_stats') IS NOT NULL
    SELECT vs.volume_mount_point AS Drive, vs.file_system_type AS [Type]
        ,vs.logical_volume_name AS LogicalName
        ,MAX(CAST(  1.0*vs.total_bytes  / 1073741824 AS DECIMAL(18,2)))AS[Drive Size (GB)]
        ,CAST(SUM(  1.0*size) / 128 / 1000 AS DECIMAL(18,2)) AS [SQL Size (GB)]
        ,MAX(CAST(  1.0*vs.available_bytes/1073741824 AS DECIMAL(18,2)))AS[Free Space (GB)]
        ,MIN(CAST(100.0*vs.available_bytes/vs.total_bytes AS DECIMAL(5,1)))AS[Free Space (%)]
    FROM master.sys.master_files AS f WITH (NOLOCK)
    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
    GROUP BY vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name
    ORDER BY 1
    OPTION (RECOMPILE);

SELECT Drive
    ,ISNULL([ROWS], 0) + ISNULL([LOG], 0)
        + ISNULL([TempROWS], 0) + ISNULL([TempLOG], 0)
        AS 'TotalUsed (MB)'
    ,[ROWS] AS 'Data (MB)'
    ,[LOG] AS 'Logs (MB)'
    ,[TempROWS] AS 'TempData (MB)'
    ,[TempLOG] AS 'TempLogs (MB)'
FROM (
    SELECT LEFT(Physical_Name, 3) 'Drive'
        ,CASE WHEN database_id = 2 THEN 'Temp' ELSE '' END + type_desc 'FileType'
        ,SUM(size) / 128 'SizeMB'
    FROM master.sys.master_files
    GROUP BY LEFT(Physical_Name, 3)
        ,CASE WHEN database_id = 2 THEN 'Temp' ELSE '' END + type_desc
    ) Results
PIVOT(SUM(SizeMB) FOR FileType IN ([ROWS], [LOG], [TempROWS], [TempLOG])) pvt
ORDER BY 1
OPTION (RECOMPILE);

Then check which files are the biggest/worst on the problem drive:

/* Get Individual Database Stats */
SELECT mf.database_id 'DB_ID'
    ,DB_NAME(mf.database_id) 'DBName'
    ,d.state_desc 'DBState'
    ,d.recovery_model_desc AS RecoveryModel
    ,CASE WHEN Log_ReUse_Wait_Desc = 'NOTHING' THEN ''
          ELSE Log_ReUse_Wait_Desc END AS LogReUseWait
    ,mf.[File_ID]
    ,mf.NAME 'LogicalName'
    ,mf.type_desc 'Type'
    ,mf.Physical_Name
    ,mf.state_desc 'FileState'
    ,CAST(size / 128.0 + 0.5 AS INT) AS SizeMB
    ,CAST(max_size / 128.0 + 0.5 AS INT) AS MaxSizeMB
    ,CASE is_percent_growth
        WHEN 0 THEN CAST(growth / 128 AS VARCHAR(10)) + ' MB'
        ELSE CAST(growth AS VARCHAR(10)) + ' %' END AS 'AutoGrowth'
    ,CASE
        WHEN d.STATE <> 6 /* 6 = OFFLINE */
        AND mf.type_desc = 'ROWS'
        AND mf.database_id <> 2 /* not TempDB */
            THEN 'USE '+QUOTENAME(DB_NAME(mf.database_id))
               + ';   DBCC SHRINKFILE(' + CAST(file_id AS VARCHAR(2))
               + ',1,TRUNCATEONLY);
GO'
        ELSE '' END AS 'ShrinkTruncateOnlyCommand'
FROM [master].sys.master_files mf
LEFT JOIN [master].sys.databases d ON d.database_id = mf.database_id
WHERE 1 = 1
    AND d.STATE <> 6 /* 6 = OFFLINE */
    AND mf.database_id > 4 --user DBs only
    --AND mf.database_id <= 4 --system DBs only
ORDER BY SizeMB DESC, DB_NAME(mf.database_id), [file_id]
OPTION (RECOMPILE);

Before you go the SHRINKFILE route, you should ask yourself some higher level questions, like:

Why does the server admin need more free space on that drive?
What will happen to my DB and its data if the file takes up the whole drive and cannot grow when needed? (hint: it's Bad Stuff.)
Should the server admin add space/drives to my SQL Server box, so I don't have to SHRINKFILE right now?

Since you have found a table with data that can (hopefully) be deleted (check with the people who use that data!), go ahead and delete the data, possibly in batches if you are worried about locking the table for a long time:

DELETE FROM <table> WHERE <pick the primary keys to delete in this batch>

If there are useful rows left in your table after getting rid of the old/useless ones, I would rebuild the table's Primary Key:

USE YourDB;

SELECT s.[Name] + '.' + t.[Name] AS ObjectName
    ,'ALTER INDEX [' + i.[Name] + '] ON [' + s.[Name] + '].[' + t.[Name] + '] REBUILD'
FROM sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id
    AND i.type = 1 --clustered index
    AND OBJECTPROPERTY(t.[object_id], 'IsUserTable') = 1
JOIN sys.schemas s ON s.schema_id = t.schema_id
    --AND s.[Name] = 'YourSchema'
    AND t.[Name] = 'YourTable'
ORDER BY 1

...and finally shrink the database file. I would try using the TRUNCATEONLY option first, as it's faster/safer than a normal SHRINKFILE which rearranges pages within the DB file:

/* Get Individual Database Stats */
SELECT d.Name DB
    ,mf.type_desc
    ,CASE
        WHEN d.STATE <> 6 /* 6 = OFFLINE */
        AND mf.type_desc = 'ROWS'
        AND mf.database_id <> 2 /* not TempDB */
            THEN 'USE '+QUOTENAME(DB_NAME(mf.database_id))
               + '; DBCC SHRINKFILE(' + CAST(file_id AS VARCHAR(2))
               + ',1,TRUNCATEONLY);'
        ELSE ''
        END AS 'ShrinkTruncateOnlyCommand'
FROM sys.master_files mf
LEFT JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.Name = 'YourDB'
    AND mf.type_desc = 'ROWS' --data only
ORDER BY DB_NAME(mf.database_id), [file_id]
OPTION (RECOMPILE);


How to create two additional columns in a results set to act as axis for a heatmap

Answer originally left by the question author in a comment

Final solution was:

SELECT
    ...,
    (((ROW_NUMBER() OVER (ORDER BY (SELECT 1))) -1 ) / @cnt) + 1 AS X,
    (((ROW_NUMBER() OVER (ORDER BY (SELECT 1))) -1 ) % @cnt) + 1 AS X


Disabling Resource Governor in SQL Server

I change Resource Governor settings quite often without restarting SQL Server. I am not aware of any need to restart SQL Server after changing Resource Governor settings except possibly for a classifier function change. A few things that might be helpful to know:

Once a session is assigned to a Resource Governor workload group it stays there, even if the classifier function changes.
I have never seen query plan compilation account for Resource Governor settings.
Some aspects of query execution are decided at the start of query execution, such as MAXDOP and query memory. Note that there are some exceptions where that isn't always true such as certain types of index creation or queries that execute in batch mode.

A precise answer that fully answers when the changes are applied to queries (whether or not it is "instantaneous") is likely outside the scope of an answer here. I suggest figuring out what you care about and doing testing as needed to convince yourself that what SQL Server is doing is okay with your workload. For example, consider the MAXDOP limit available through Resource Governor workload group configuration. For nearly all queries MAXDOP is decided at query execution time, so I wouldn't expect disabling Resource Governor to change the MAXDOP of a currently executing query. However, the next query that starts for the session will no longer be limited in MAXDOP. However, what happens if there's a Scalar UDF executing many times during a query with a parallel plan? I'd expect MAXDOP to possibly change for that query, but I've never tested that and don't care to test it. If I had to guess, I suspect that all of the following will be updated "instantaneously":

MIN_CPU_PERCENT
MAX_CPU_PERCENT
CAP_CPU_PERCENT
MIN_IOPS_PER_VOLUME
MAX_IOPS_PER_VOLUME
IMPORTANCE
REQUEST_MAX_CPU_TIME_SEC
GROUP_MAX_REQUESTS

and all of these to update only after the next query starts executing:

MIN_MEMORY_PERCENT
MAX_MEMORY_PERCENT
AFFINITY
REQUEST_MAX_MEMORY_GRANT_PERCENT
REQUEST_MEMORY_GRANT_TIMEOUT_SEC
MAX_DOP


How to combine "SELECT" and "REPLACE INTO" when combining two MySQL tables into another?

as variant:

INSERT INTO t3

SELECT t1.measurement_id,
CASE WHEN t1.value IS NULL THEN
     t2.value
ELSE
     t1.value
END as value

FROM t1 LEFT JOIN t2 ON t2.measurement_id = t1.measurement_id AND t1.value IS NULL

UNION ALL

SELECT
     t2.measurement_id,
     t2.value
FROM t2 WHERE t2.measurement_id NOT IN (SELECT measurement_id FROM t1)

as benefits - no REPLACE


Problems with Differential Backup CheckpointLSN

The checkpoint_lsn of the diff and transaction log do not match so
  that doesn’t seem to be the same

Checkpoint_lsn is the LSN of most recect checkpoint respect to given backup (the closest in time checkpoint that occured prior to given backup), so checkpoint_lsns of different backups are not related to each other.

  neither do the first and last lsn values

This depends on the type of backup.

While for all the backup types these are the first LSN and the (last LSN - 1)* contained in given backup and seem to be independent of other backups, Last_lsn and First_lsn of log backups are always "in relation":

I.e. every next log backup starts exactly where previous log backup stopped.

*
That LSN is not contained in 2 log backups, instead, given current log backup, its last_lsn is not the one contained in this backups, but it's the next lsn respect to the last lsn, contained in this log backup, that's why I wrote that the Last_LSN is Last_lsn - 1.

In other words, it is LSN that will be the starting point of the next log backup and it will be contained in that next log backup.

  My current thinking is the next log to apply after a diff is one where
  the last_lsn of the diff is contained with the first_lsn and last_lsn
  value of the log. Is this correct

Yes it's correct.


Is there any database with versioning (column based) built in?

SQL Server has Change Data Capture in it's Enterprise edition.

From msdn link to information:

  Change data capture records insert, update, and delete activity that
  is applied to a SQL Server table. This makes the details of the
  changes available in an easily consumed relational format. Column
  information and the metadata that is required to apply the changes to
  a target environment is captured for the modified rows and stored in
  change tables that mirror the column structure of the tracked source
  tables. Table-valued functions are provided to allow systematic access
  to the change data by consumers.


I did this on an instance that had 20+ databases, some went fairly quick (minutes/hours).  One was heavy OLPT and I ended up using a job that ran off hours to move, it took about 6 months.

I have tried, file balancing with index rebuilds, it is not as effective as you might hope.

If you just add a new .ndf for the most part only new data will go there, and you may have decreased performance compared to well balanced datafiles.

If you have a TB of data, you probably want more then one .ndf file.  There are recommendations based on CPU.  If you have 8 CPU you should have 8 datafiles (one .mdf & 7 .ndf)

It is not possible to move all the data off of the .mdf, there is some that must stay there.  Also optimally you want to balance files evenly across all your datafiles.  Best course IMHO is to move everything you can to a single temp.ndf.  Then create your 7 new .ndf files mydatabase_2.ndf, etc and move everything back to the 8 datafiles.  This will give you a nicely balanced set of datafiles.  It is close to what would have grown naturally if you had all 8 datafiles at the start.

Disk space may be an issue.  If you don't have 2TB of room you can't have a mydatabase.mdf and a temp.ndf that are both 1TB.  You may have to stop the data movement occasionally so you can trim newly freed space.

Create temp.ndf give it a decent autogrow size, probably 500GB (if you have room, make it 1TB from the start)
Move data off DBCC SHRINKFILE (N'mydatabase' , EMPTYFILE)
Trim free space DBCC SHRINKFILE (N'mydatabase' , 1)
When the original .mdf is as small as it will get (about 10MB) create your new files mydatabase_2.ndf, mydatabase_3.ndf, etc
Shrink back DBCC SHRINKFILE (N'temp', EMPTYFILE), watch for blocking and space, trim as needed.

While doing the move you will want to watch for blocking and dataspace issues.  This is the code I use, run it every few minutes while I am watching.

-- Two queires to provide and idea of how things are going durring a file shrink empty modification on the specific database

-- Look for locks and waits that may be impeding the move in specific database.
-- !!!!!Be sure to put your database name in the where clause!!!!!!-----------------
Select session_ID
, Start_time
, [Status]
, command
, user_id
, blocking_session_id as 'blocking ID'
, wait_type
, wait_time
--, estimated_completion_time  as 'est comp time'--Values can fluctuate wildly, When it is smaller the cpu_time and decreasing can suddendly finish.
, cpu_time
, percent_complete as '%conmplete'
, lock_timeout
, deadlock_priority
, last_wait_type
, SDB.name as 'DB_name'
, SDB.state_desc as 'DB_Status'
--, *

From sys.dm_exec_requests

left join sys.databases as SDB
on sys.dm_exec_requests.database_id = SDB.database_id

where [status] not in ('background','sleeping')
and SDB.name = 'DB_name'-- The database I am working on ---------<<<<<<<<<<<<<<< Change this value------------

--Identifies used space on files, how much data has moved.
select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'  --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

 from sys.database_files
 order by type_desc Desc, name


How to script statistics in Sql Server? (using T-SQL)

A 2005+ compatible option that supports  this subset of the grammar is below.

CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ ,...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH
        [ STATS_STREAM = stats_stream ]
        [ [ , ] NORECOMPUTE ]
        [ [ , ] INCREMENTAL = { ON | OFF } ]
    ] ;

NB: Support for 2005 is somewhat limited however. The product itself doesn't support incremental or filtered statistics and I haven't introduced support for stats stream as it would need one of the kludgy workarounds to convert binary to string before this functionality was added to CONVERT.

DECLARE @Schema             sysname,
        @Table              sysname,
        @StatsName          sysname,
        @IncludeStatsStream BIT,
        @StatsStream        VARCHAR(MAX),
        @TwoPartQuotedName  NVARCHAR(500);

select  @Schema           = 'dbo',
        @Table            = 'T1',
        @StatsName        = 'S1',
        @IncludeStatsStream = 0;

select @TwoPartQuotedName = QUOTENAME(@Schema) + '.' + QUOTENAME(@Table);

IF @IncludeStatsStream = 1 AND @@MICROSOFTVERSION/ POWER(2,24) > 9
  BEGIN
      DECLARE @StatsResults TABLE
        (
           StatsStream VARBINARY(MAX),
           Rows        BIGINT,
           DataPages   BIGINT
        );

      INSERT INTO @StatsResults
      EXEC sys.sp_executesql
        N'DBCC SHOW_STATISTICS(@TwoPartQuotedName, @StatsName) WITH STATS_STREAM;',
        N'@TwoPartQuotedName NVARCHAR(500), @StatsName sysname',
        @TwoPartQuotedName = @TwoPartQuotedName,
        @StatsName = @StatsName;

      --Would need some other method on 2005 hence just skipping this
      SELECT @StatsStream = CONVERT(VARCHAR(MAX), StatsStream, 1)
      FROM   @StatsResults;
  END;

WITH stats AS
(
/*
Support earlier versions without these columns using trick from http://dba.stackexchange.com/a/66755/3690 */
SELECT x.*
FROM (SELECT NULL AS filter_definition, NULL AS is_incremental) AS dummy
CROSS APPLY
(
  SELECT object_id, stats_id, name, no_recompute, filter_definition, is_incremental
  FROM sys.stats
) AS x
)
SELECT '
CREATE STATISTICS ' + QUOTENAME(name) + '
ON ' + @TwoPartQuotedName + ' (' + SUBSTRING(cols, 2, 10000000) +')
'  +
ISNULL(' WHERE ' + filter_definition,'') +
ISNULL(STUFF (
    ISNULL(',STATS_STREAM = ' + @StatsStream, '') +
    CASE WHEN no_recompute = 1 THEN ',NORECOMPUTE' ELSE '' END +
    CASE WHEN is_incremental = 1 THEN ',INCREMENTAL=ON' ELSE '' END
 , 1 , 1 ,  ' WITH '  ) , '') AS [processing-instruction(x)]
FROM   stats s
       CROSS APPLY (SELECT ',' + QUOTENAME(c.name)
                    FROM   sys.stats_columns sc
                           JOIN sys.columns c
                             ON c.object_id = sc.object_id
                                AND c.column_id = sc.column_id
                    WHERE  sc.object_id = s.object_id
                           AND sc.stats_id = s.stats_id
                    ORDER  BY sc.stats_column_id
                    FOR XML PATH(''))CA(cols)
WHERE  s.object_id = OBJECT_ID(@TwoPartQuotedName)
       AND s.name = @StatsName
FOR XML PATH('');


You're losing error messages,

# select parse_ident('abc,efi&xyz');
ERROR:  string is not a valid identifier: "abc,efi&xyz"

They also do different things:

parse_ident takes a string a fully-qualified identifier -- something you can use to identify a thing in a database, and returns the breakdown of it in an array. For example, if you had a column named abc,efi&xyz on a table named table in a schema named schema in a database named db, you could do,

SELECT parse_ident('"abc,efi&xyz".table.schema.db');
           parse_ident
---------------------------------
 {"abc,efi&xyz",table,schema,db}
(1 row)

From this point you could use array addressing to identify the table, (parse_ident(...)[2]).
regexp_split_to_array takes any string and simply splits it returning an array. Unlike parse_ident it doesn't require a fully-qualified identifier, and it doesn't understand ANSI-quotes inside the identifier (as I used in my example above).


Error Msg 10314, Level 16, State 11 with SAFE assembly after upgrade to SQL Server 2017

The error is a result of the new clr strict security server-level configuration option in SQL Server 2017. This new security setting disallows any Assembly, even those marked as SAFE, from being either created or loaded into memory for execution, unless:

the Database property of TRUSTWORTHY is ON (don't do this as it is an unnecessary security risk) and the Login that is the database owner (i.e. same SID used by the [dbo] User) needs to have the UNSAFE ASSEMBLY permission (which it might already have if the owner is sa or a member of the sysadmin fixed server role or maybe has the CONTROL SERVER permission).
the Server-level configuration option of clr strict security is disabled / 0 (don't do this as it is an unnecessary security risk)
the Assembly is signed and you have a corresponding signature-based Login that has been granted the UNSAFE ASSEMBLY permission (please do this)
the SHA-512 hash of the Assembly is registered as a "Trusted Assembly" (absolutely never do this as there are numerous problems with it, not to mention that it was never needed in the first place)

All you need to do in order to fix this situation are the following steps (which are not difficult and provide the highest level of security):

Create a Certificate in the Database where the Assembly resides
Back up the Certificate (both Public and Private Keys) to files (optional)
Sign the Assembly (or Assemblies) with the Certificate
Remove the Private Key (it can be restored later from the backup file) (optional)
For each additional Database that has unsigned, SAFE Assemblies:

Either skip this sub-step (i.e. skip to Step 6), complete the steps, and repeat the whole process (this will result in one Certificate per each Database needing one), OR
Do the following (this will result in one Certificate total, which will be used for all Databases):

Create the same Certificate from the backup files (including the Private Key!)
Sign the Assembly (or Assemblies) with the Certificate
Remove the Private Key (it can be restored later from the backup file)

Copy Certificate to [master] (Public Key only!)
Create Login from Certificate
Grant the Certificate-based Login the UNSAFE ASSEMBLY permission

A simple example of this (minus the optional backup and Private Key removal) would be:

USE [{database_containing_unsigned_safe_assembly}];

CREATE CERTIFICATE [{certificate_name}]
  ENCRYPTION BY PASSWORD = '{some password}'
  WITH SUBJECT = '{simple description}',
  EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE
  TO Assembly::[{assembly_name}]
  BY CERTIFICATE [{certificate_name}]
  WITH PASSWORD = '{some password}';

DECLARE @PublicKey VARBINARY(MAX),
        @SQL NVARCHAR(MAX);

SET @PublicKey = CERTENCODED(CERT_ID(N'{certificate_name}'));

SET @SQL = N'
CREATE CERTIFICATE [{certificate_name}]
  FROM BINARY = ' + CONVERT(NVARCHAR(MAX), @PublicKey, 1) + N';';
PRINT @SQL; -- DEBUG

EXEC [master].[sys].[sp_executesql] @SQL;

EXEC [master].[sys].[sp_executesql] N'
CREATE LOGIN [{login_name}]
  FROM CERTIFICATE [{certificate_name}];

GRANT UNSAFE ASSEMBLY TO [{login_name}]; -- REQUIRED!!!!
';

A fully working example of this (minus the backup and Private Key removal) can be found on PasteBin:
Avoiding "Trusted Assemblies" - Demo

A detailed explanation of why you should use Certificates, and why you should not use "Trusted Assemblies", to fix this problem is provided in my blog post:
SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment

Also, please consider supporting my request to have Microsoft remove the new "Trusted Assemblies" feature given the numerous problems surrounding it (including security concerns), and the overall lack of benefits for having it:

Trusted Assemblies are more problematic yet less functional than Certificates - Please Remove


How To Set Variables in MySQL Update Trigger

How about ...

drop trigger if exists Update_estimate_from_line_items;
delimiter //

CREATE TRIGGER Update_estimate_from_line_items
AFTER UPDATE
   ON estimate_line_items FOR EACH ROW

BEGIN

   -- variable declarations
   DECLARE vPrev_amnt INT;
   DECLARE vNew_amnt INT;
   DECLARE nDiff INT;

   SET vPrev_amnt = OLD.price * OLD.quantity;
   SET vNew_amnt = NEW.price * NEW.quantity;
   SET nDiff = vNew_amnt - vPrev_amnt;  -- names amended

   -- trigger code
   UPDATE estimates SET
     subtotal = total + nDiff
   , total = subtotal + (tax_rate/100 * subtotal)
   WHERE estimate_id = NEW.estimate_id;

END//

delimiter ;


Database migration - Simply change connection string?

To ensure writes from the Master are complete, do the following on the Master:

SET GLOBAL read_only = 1;
FLUSH TABLES;
FLUSH LOGS;

Go to the Slave and run the following:

STOP SLAVE;
RESET SLAVE ALL;

Then, make sure there is no Slave setup

SHOW SLAVE STATUS\G

If it comes back with nothing, setup the new connect string and you're done !!!

GIVE IT A TRY !!!


“Cannot continue the execution because the session is in the kill state” Error?

Solution
The solution to your issue is contained in the error message you included in your question:

2020-09-21 12:23:39.73 Server      A user request from the session with SPID 697
                                   generated a fatal exception. SQL Server is
                                   terminating this session.
                                   Contact Product Support Services with the dump
                                   produced in the log directory.

Make sure you can collect the ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG\SQLDump10000.txt file and open up a service request with (your) Microsoft (representative/consultant). In some cases you might find a minidump *.dmp file located in the same location.
Include everything in the case and hopefully Microsoft will be able provide you with a solution to your issues.
Recommendation (On a Side Note)
I noticed that the log files are generated in the C:\ drive. Normally this drives hosts the Operating System (OS). It isn't recommended to run SQL Server on the OS drive. If the database or anything related to the database produces a large amount of data, then this could fill up the OS drive, which eventually could crash the system.


Is it possible to write a SELECT statement to obtain all dates between 2 date columns?

There is probably something I don't understand in your question, but from the looks of it all you have to do is remove the predicate t.id = ? You can shorten the query a bit by using BETWEEN predicate, I also prefer explicit JOINS over "," joins:

SELECT t.Id, dd.[Date]
FROM dbo.DateDimension dd
JOIN dbo.dbse t
    ON dd.Date BETWEEN t.StartDate and t.EndDate;

In your example in the fiddle, you output 3 id's. If you only want a subset you can use an IN predicate:

SELECT t.Id, dd.[Date]
FROM dbo.DateDimension dd
JOIN dbo.dbse t
    ON dd.Date BETWEEN t.StartDate and t.EndDate;
WHERE t.id IN (4229, 4268, 4269);


Error on re-import: #1231 - Variable 'character_set_client' can't be set to the value of 'NULL'

Not a user of phpMyAdmin, but mysqldump has an option to disable those lines from the dump:

--skip-set-charset

Unfortunately, this will work if you have access to mysqldump. Not sure if phpMyAdmin has configurable options for export, but hope this helps.


Would it be a potential bottle neck on performance if a statistic had already been created automatically for the same column(s) by SQL Sever and my script creates a duplicate statistic?

Surely you can write your script such that it detects such a duplicate statistic, instead of just blindly creating another one (possibly multiple times)? We do this for tables, views, procedures, functions, indexes... why not for statistics too? If you have the ability to create a statistic you have the ability to check the metadata for one that's already there (and maybe just needs to be updated).

I haven't done any performance tests, and I'm not sure if auto-stats (sync or async) will kick off multiple times or just pick one of them, but certainly nothing good can come of it. In the one case you're going to spend twice (or more!) the amount of time updating redundant statistics; if they don't both get updated then it's possible that the optimizer will pick the wrong one when using the statistic for cardinality.

I'd be more interested to know how often you find that - with auto-create stats turned on - SQL Server hasn't created a statistic that would yield better performance or estimates, and then suddenly the plans improve because of the statistic you decided to create (that SQL Server didn't bother). My guess is that would be pretty rare (unless you haven't really enabled auto-create stats for those customers, or maybe these are filtered statistics, though that isn't possible for your 2005 folks).

As for stats going stale in general, you may be able to implement Trace Flag 2371 - which improves the thresholds for engaging auto-stats - for some of your customers.


Generate Dates between Date Ranges in mysql

I tried this solution :

WITH recursive Date_Ranges AS (
    select '2018-11-30' as Date
   union all
   select Date + interval 1 day
   from Date_Ranges
   where Date < '2018-12-31')
select * from Date_Ranges;


Should we really turn on trace flag 1118 for every SQL instance?

For SQL Server 2000 → 2014

I generally agree with Paul Randal, that this is something you could always turn on, but I do have a little bit of hesitation to say it should always be on and you should leave it on regardless of what you observe (with or without adequate testing).

People can have poorly set up tempdb configurations, for example not enough files, files too small, files not equally sized, improper growth settings, instant file initialization not enabled, and bad coding symptoms where temp tables are not cached and reused (see here and here). In this scenario, with the perfect storm workload, it's conceivable that the trace flag could actually lead to worse overall performance. Think about a case where those settings are not optimal and you have tons of users all trying to create their own, not-from-cache copies of temp tables, and they're all using their own dedicated extents instead of sharing them. There may be some level, even if I can't produce it at will, where the overhead of dedicated extents outweighs the allocation contention savings.

Just because Paul hasn't seen a scenario where the scales tip at some threshold doesn't mean it's impossible, just that it's unlikely / uncommon.

You can likely avoid that by following all of the other best practices, because I suspect it really would require a perfect storm, I just do not feel strong enough to say always and without exception.

For SQL Server 2016

In SQL Server 2016, uniform extent allocation is the default behavior - see Bob Dorr's "it just runs faster" post about it. Other than that, many of the same things above hold true.

You can't turn this off for tempdb; so, effectively, trace flag 1118 is always on in 2016, and is permanent. So my suggestion would be to make sure tempdb works effectively under TF 1118 on your current instances and under your current workload, to avoid any surprises when you upgrade (at which point you will no longer have the option to turn it off).

If you want to turn it off for user databases in 2016, you use ALTER DATABASE:

ALTER DATABASE MyDatabase SET MIXED_PAGE_ALLOCATION ON;


Gathering sp_BlitzFirst data into a centralized database

There's nothing special about the data being built by sp_BlitzFirst. You can really zoom out and ask, "What's the most efficient way of moving data between any SQL Servers?"

Some of the approaches that folks have used include:

Pulling the data periodically from a central server via linked servers or openquery
Using their favorite ETL tool, like SSIS
Log shipping their management databases to a central location
Writing data movement jobs with PowerShell

There's no one right answer: it's just whatever data movement you're most comfortable with and most able to support. I don't ship a method with the First Responder Kit because frankly, I don't want to do support for this kind of thing. (It would come with all the baggage that comes with anytime people try to connect two SQL Servers to each other, especially over networks.)


Is it safe to use set-returning sql functions instead of security-barrier views?

Yes - if you use functions in a language other than SQL, or if you define them as STRICT.

Essentially, you must prevent inlining of the function. If the function isn't inlined, then predicates can't be pushed down through it and it can't be flattened.

Only SQL functions are eligible for inlining, and only if they are not defined as STRICT.


SQL Server Execution Plans and Index Drops

Welcome to DBA.StackExchange, and interesting question!...one I've never thought of before. I had to double check myself on what I thought the process was, so I think this StackOverflow Answer should be what you're looking for. Specifically the first statement of the answer:

Dropping/Rebuilding an Index will result in invalidation of any cached
execution plans using this table/Indexed view. And sql server will
generate a new execution plan on next execution.

Essentially, the plans in the plan cache will automatically be marked as invalid of any existing execution plans that reference the dropped index. Then the next time a query runs that involves the entity of which the index was dropped from, a new execution plan is automatically generated and cached in the plan cache because the old one is invalidated. (Eventually the invalidated plans are flushed from the plan cache to make room for new plans being cached.)
Here's an additional resource that concurs this is how it occurs: Dropping unnecessary indexes - effect on query plans in SQL Server 2005


This error is fairly self-explanatory.  The number of columns in the 'insert' column list do not match the number of columns in the 'select' column list.  Check the number of columns being returned by your SELECT *  I would suggest that it is rarely a good idea to use SELECT * when trying to insert into another table.  You should always explicitly specify the selected columns.

I played around with your example XML data by putting it in a file and then executed the following

IF OBJECT_ID('tempdb..#Table') IS NOT NULL DROP TABLE #Table
Create table #Table (ID int, ReferenceNum varchar(20), ArName Varchar(20), EnName Varchar(20),
PublicationDate Date, CreatedBy Varchar(200), CreatedOn Varchar(20))
;
WITH XmlData
AS (
    SELECT cast(BulkColumn AS XML) AS XmlData
    FROM OPENROWSET(BULK 'C:\Test\TestXml.txt', SINGLE_CLOB) AS x
    )
insert into #table
SELECT a.value('ID[1]', 'varchar(15)') AS ID
    ,a.value('ReferenceNum[1]', 'varchar(20)') AS ReferenceNum
    ,a.value('ArName[1]', 'varchar(20)') AS ArName
    ,a.value('EnName[1]', 'varchar(20)') AS EnName
    ,a.value('PublicationDate[1]', 'varchar(20)') AS PublicationDate
    ,a.value('CreatedBy[1]', 'varchar(20)') AS CreatedBy
    ,a.value('CreatedOn[1]', 'varchar(20)') AS CreatedOn
FROM xmlData
CROSS APPLY XmlData.nodes('XML_Articles/Articles') AS XMLArticles(a)
GO
select * from #table


There are multiple ways to go with this but using logs is the simplest way as there is no need of creating table inside mysql to log the root or user login with timestamp as mentioned in : http://www.fromdual.com/sites/default/files/logon_trigger_wp.pdf

I installed logtail and logcheck using yum, then wrote the following script and then created a cron job which runs this script:

#!/bin/sh

if [ -f foundadmin.txt ]
then
    rm foundadmin.txt
fi
if [ -f send.txt ]
then
    rm send.txt
fi

/usr/sbin/logtail2 -f /var/log/mysqld.log -o mysqld.log.offset | grep "root@localhost on  using Socket" > foundadmin.txt

if [ -s foundadmin.txt ]
then
    echo "Subject: MYSQLDB Admin Login on $(hostname)" > send.txt
    cat foundadmin.txt >> send.txt
    /bin/mailx -s "Subject: MySQLDB Admin Login on $(hostname)" myemail@com  < send.txt
    rm foundadmin.txt
    rm send.txt
fi
exit 0

#

this is the cron job to make it run every minute:

This is run a cron job in a time interval and mail the admins to notify

cd /opt/mysql-check ; ./mysql-notify-script.sh


So, when you insert a row into test with an invalid ex_key value (one that isn't in the referenced column in the referenced table), you want that insert to succeed, instead of fail, and you want the invalid value inserted as NULL instead instead of the value you attempted to insert.  Right?

A "before insert" trigger should allow you to accomplish that, by doing the lookup and changing the value of ex_key to NULL if ex_key isn't a legit value.

Of course, there's some overhead on processing any trigger, but if you need this functionality, this is probably the most painless way to do it.

Also, If you already have a "before insert" trigger on this table, you would just add the IF ... END IF block to the body of the existing trigger.

DELIMITER //

CREATE TRIGGER test_bi BEFORE INSERT ON test FOR EACH ROW
BEGIN
    IF (SELECT ex_key FROM ex_table WHERE ex_table.ex_key = NEW.ex_key) IS NULL THEN
        SET NEW.ex_key = NULL;
    END IF;
END;

//

DELIMITER ;

I built a proof of concept of this with MySQL 5.5.27, here: http://sqlfiddle.com/#!2/d0a8e/1/0

Note, this also completely prevents the foreign key error from even occurring, because the trigger replaces NEW.ex_key's incoming value with NULL (when appropriate) before the validity of the value is checked against the foreign key constraint by InnoDB.


EXCEPTION logic is not working in Oracle PL/SQL function

You are trying to find an error where an error can not exist. When you are doing a count it will always return a valid result even if the value that you are looking for does not exist in the table.

What you need to do is two separate counts and do a check to see what the return value is.

SELECT COUNT(*) INTO no_of_departments
FROM    DEPARTMENT
WHERE   DEPARTMENT_NAME = dept_name;

IF no_of_departments = 0 THEN
    * ERROR LOGIC HERE *
END IF;

IF no_of_departments > 1 THEN
    * ERROR LOGIC HERE *
END IF;

SELECT COUNT(*) INTO no_of_employees
FROM    DEPARTMENT, EMPLOYEES
WHERE   DEPARTMENT.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
AND DEPARTMENT_NAME = dept_name;

IF no_of_employees = 0 THEN
    * ERROR LOGIC HERE *
END IF;


PostgreSQL - how to recognize if function was called by delete or update trigger?

Assuming this is a PL/pgSQL function, then - as documented in the manual - a trigger function automatically has access to some special variables. One of them is TG_OP that identifies the trigger operation.

The manual even has an example on how to use it:

IF (TG_OP = 'DELETE') THEN
  -- do something with the OLD record
ELSIF (TG_OP = 'UPDATE') THEN
  -- do something with the NEW record
ELSIF (TG_OP = 'INSERT') THEN
  -- do something entirely different
END IF;


Sort spills to tempdb due to varchar(max)

There are going to be several possible workarounds here.

You can manually adjust the memory grant, though I probably wouldn't go that route.

You can also use a CTE and TOP to push the sort lower, before grabbing the max length column. It will look something like below.

WITH CTE AS (
SELECT TOP 1000000000 r.ID, s.ID AS ID2, s.typeID
FROM Resources r
inner join Settings s on resourceid=r.id
where resourceUID=@UID
ORDER BY s.typeID
)
SELECT c.ID, ca.remark
FROM CTE c
CROSS APPLY (SELECT remark FROM dbo.Settings s WHERE s.id = c.ID2) ca(remark)
ORDER BY c.typeID

Proof-of-concept dbfiddle here.
Sample data would still be appreciated!

If you want to read an excellent analysis by Paul White, read here.


Get the size of list of documents in mongo db

As per MongoDB jira blog documentation Real document size in collection and also MongoDB documentation here the Object.bsonsize(<document>) prints the BSON size of a <document> in bytes.

To find the size of all documents in a collection:

Object.bsonsize(db.collection.find( {dbname:"collectionname"}))

For Example

> use test
switched to db test
> db.createCollection("StackExchange");
{ "ok" : 1 }

> show collections
StackExchange
>

> db.StackExchange.insertOne({"Name" : "user3138864", "Password" : "abc123"})
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5b740353194beb82d3752206")
}
> db.StackExchange.findOne()
{
        "_id" : ObjectId("5b740353194beb82d3752206"),
        "Name" : "user3138864",
        "Password" : "abc123"
}
> db.StackExchange.find().pretty()
{
        "_id" : ObjectId("5b740353194beb82d3752206"),
        "Name" : "user3138864",
        "Password" : "abc123"
}
> Object.bsonsize(db.StackExchange.find( {m040:"StackExchange"}))
45734

For further your ref here and here


Assuming EntityTypeId is the primary key of dbo.EntityTypes then REPEATABLE READ would suffice:

SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;

IF EXISTS (SELECT 1 FROM Entities WHERE EntityTypeID = 1)
BEGIN
    RAISERROR('Cannot delete entity type which is referenced', 16, 1);
    RETURN;
END

DELETE FROM EntityTypes WHERE EntityTypeID = 1;

COMMIT TRAN;

The NOT EXISTS syntax Aaron suggested would also function as you require at REPEATABLE READ. The same would be achieved by specifying a hint on dbo.Entities:

DELETE et
FROM dbo.EntityTypes AS et
WHERE EntityTypeID = 1
AND NOT EXISTS
(
  SELECT 1
  FROM dbo.Entities WITH (HOLDLOCK)
  WHERE EntityTypeID = 1
);


"CREATE FILE encountered operating system error 32" when attempting to attach database

Do not fear everyone, I fixed my problem. What I had to do was detach the databases that I needed so that they were no longer being used by SSMS. This got rid of the error I was having and allowed me to add a connection to the data source in visual studio.


Move Commonly-Repeated Fields into a new Table

Yes, move it.  The extra join overhead will be minimal (if the right indexes are in place!) and you will get a performance improvement for all the other queries which don't use addresses because you will get more rows per page in Customers and Offices.

Down the road, any master data management (MDM) or data clean-up will be easier with the values all in one place.

Sticky points to look out for are common definitions of column semantics (is Address4 the city, say, for absolutely every possible address?).  This could be important for reporting, for example, or summarising sales by region.  Uniqueness could be difficult to define for any natural keys you may choose to create on Addresses.


Add a total quantity column for groups of rows

You can use the OVER clause on the COUNT function to get what you need:

CREATE TABLE #inventory(
   InventoryID  INTEGER  NOT NULL PRIMARY KEY
  ,ItemID       INTEGER  NOT NULL
  ,WarrantyDate DATE  NOT NULL
  ,Status       VARCHAR(9)
);
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (1,103,'12/22/2010','Available');
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (2,103,'05/15/2012','Available');
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (3,103,'02/24/2015',NULL);
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (4,275,'01/01/2010',NULL);
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (5,275,'01/01/2011',NULL);

SELECT *, COUNT(ItemID) OVER (PARTITION BY ItemID) AS Quantity
FROM #Inventory

Output:

+-------------+--------+--------------+-----------+----------+
| InventoryID | ItemID | WarrantyDate |  Status   | Quantity |
+-------------+--------+--------------+-----------+----------+
|           1 |    103 | 2010-12-22   | Available |        3 |
|           2 |    103 | 2012-05-15   | Available |        3 |
|           3 |    103 | 2015-02-24   | NULL      |        3 |
|           4 |    275 | 2010-01-01   | NULL      |        2 |
|           5 |    275 | 2011-01-01   | NULL      |        2 |
+-------------+--------+--------------+-----------+----------+


There are no 100% real workload replication tools for MySQL (at least 100% free)

All you could do is only simulate:

using HammerDB
a very interesting tool from Percona, Percona playback and related article
you can also look for Apache JMeter
you can sometimes enable the general log on MySQL server, and use it to prepare tests with JMeter (after manual preparation)

With Playback - You can not replay collisions as it was on real server
With JMeter (and similar). You would need to define queries by yourself.

Depending from what you want to demonstrate it could be easy or hard to prepare a proper simulation.

Sometimes for proper problem simulation all you need is mysqlslap. Run 3-4 instances with different queries and you can simulate locks, deadlocks etc.


Does restoring an Oracle backup of the whole database restore schema objects?

You're issuing a "recover database" command, which will recover the database as far as possible.  If that includes the archivelogs/redo logs which contain your "truncate" command, then this will also be re-played and the table truncated again.

Try doing the restore on it's own, or recovering to a specific time using RECOVER UNTIL... then opening the DB read-only to see if it's in the state you expect.


From the 11.2 Database Reference documentation:

  Elapsed time (in microseconds) used by this cursor for parsing,
  executing, and fetching

Here is a demonstration:

SQL> SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %';

SQL_TEXT
-----------------------------------------------------------------------------
ELAPSED_TIME
------------
SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %'
        5112

SQL> SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %';

SQL_TEXT
-----------------------------------------------------------------------------
ELAPSED_TIME
------------
SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %'
       26332

SQL> SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %';

SQL_TEXT
-----------------------------------------------------------------------------
ELAPSED_TIME
------------
SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %'
       65886

SQL> SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %';

SQL_TEXT
-----------------------------------------------------------------------------
ELAPSED_TIME
------------
SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %'
      103225

If you need the timing for one execution you could set timing on in an SQL*Plus session, turn on tracing with TIMED_STATISTICS, or add a comment to the SQL so that there is only one execution in v$sql.


In terms of relational databases each table represents a type. Table Content contains the items of content type. Table Comment contains the items of comment type etc. There is no strict requirement like "normalization" to keep all the items of the same type in the same table. Even more - sometimes you have to do that for sake of performance. But having multiple tables of the same type requires all that tables to be modified simultaneously. As of experience that requirement is very easy to violate by mistake. Now I'm trying to avoid this at any cost.

Sure if you have to get "the latest comment for [some|all] content" frequently the table of the same structure as Comment but with UNIQUE constraint combined with INSERT .. ON DUPLICATE KEY UPDATE .. (often acronymed by IODKU) is way more faster than any joined subqueries. But this is the special case.

So in my opinion the first option is much better than second.

And the third and fourth options requires some redundant homemade storage engine over the relational database that should parse string or blobs for fetching the data. This approach insults not only performance but also the reliability and maintanability of the storage. This is the sort of the bad design that should be avoided.


Innodb storage is care about ACID transaction it will make sure your data(insert/update) commit to disk that why take the time:

Considering about the columns which frequently change(insert/update), you should not indexing on that column.
if you always need big data of DML operation try to avoid auto-commit=0.but it not good if server crush or some fallen.
turning your query if your DML operation bases on subquery:

Example:

UPDATE TEST SET NAME='XXXX' WHERE (subquery).


Force number precision in a view with Union

If you say

CREATE OR REPLACE VIEW test as
select 1 AS ONE ,CAST(2 AS NUMBER(1)) AS TWO ,CAST(3 as INT) AS THREE from dual

gives you what you want, why not use the same for union :

CREATE OR REPLACE VIEW test_v11 as
SELECT ONE, CAST(TWO AS NUMBER(1)) AS TWO, CAST(THREE AS INT) AS THREE
FROM
(
 SELECT 1 AS ONE ,2 AS TWO ,3 AS THREE FROM dual
  UNION
 SELECT 1 AS ONE ,2 AS TWO ,3 AS THREE FROM dual
 )a