61 votes
Accepted

Can I write portable SQL statements?

No, not for any significant and practical amount of code anyway. You can try to hew to standards (e.g., use COALESCE rather than ISNULL) but there are too many differences, big and small. Off the ...
Jon of All Trades's user avatar
44 votes
Accepted

Convert Postgres TIMESTAMP to TIMESTAMPTZ

Data type and time zone Pay attention that the correct time zone (UTC in your case) is applied during the conversion. If you are not explicit about this, the time zone of the current session is ...
Erwin Brandstetter's user avatar
44 votes

Can I write portable SQL statements?

There are ANSI SQL standards, see for example the part on Interoperability and standardization in the Wikipedia article. Problem is, few actually follow these standards, which are often written and ...
fds's user avatar
  • 646
16 votes

Best practices to maximize portability in SQL Server 2016

User Aaron Bertrand made some comments that align well with my thoughts on your question. This is more of a frame challenge than an answer to your specific question, but I think it's valuable to ...
Josh Darnell's user avatar
  • 29.2k
15 votes
Accepted

ERROR: column "field" must appear in the GROUP BY clause or be used in an aggregate function

This isn't my query, I'm translating a system prototyped in MySQL to PostgreSQL. This query does work in MySql. Does that constitute a bug in MySql, or a shortcoming of PostgreSQL, or just a ...
ypercubeᵀᴹ's user avatar
12 votes

Best practices to maximize portability in SQL Server 2016

Do not enforce STD SQL. Decide first which DBMS you will use according to the needs of your project, and take advantage of it.
McNets's user avatar
  • 23.7k
12 votes
Accepted

MariaDB - MySQL - ALL-DB Import - Table 'user' already exists

As someone already mentioned, this has to do with the mysql.user table being changed to a view in 10.4. The problem and solution is documented on the MariaDB website in MDEV-22127. The solution is to ...
foobrew's user avatar
  • 254
11 votes

How to export an image column to files in SQL Server?

I had the same problem with the extra 4 bytes being added to the beginning of all of my files as well. Instead of using -N option in my bcp command, I changed it to -C RAW. When you do this, bcp ...
Jacob Dababneh's user avatar
11 votes

Restore a SQL Server 2012 backup file in SQL Server 2016/2014

Yes this will work. You can always go forward, you can't go backwards, at least not with backup and restore. With older version eg SQL 2000, SQL 2005 sometimes you have to do a stepped upgrade, ie ...
wBob's user avatar
  • 10.4k
10 votes

Best practices to maximize portability in SQL Server 2016

Not really. There is SET FIPS_FLAGGER 'FULL'. This prints out a warning for non standard SQL - but some caveats are I am unsure what specific standard this uses (and suspect it may be SQL 92) From ...
Martin Smith's user avatar
  • 84.7k
9 votes

Quickest way to migrate mirroring to AG

If you want to ensure you minimize the amount of time you are without HA during your migration, here's a trick I recently used: Set up the AG with a "dummy" database so that your AG is ready in ...
AMtwo's user avatar
  • 16.1k
9 votes

Migrating SQL Server databases

Basically yes, all you'd need is to backup and restore the databases. However, there's a bunch of stuff that won't do. Server level settings (MAXDOP/CTFP, etc.) Agent Jobs Maintenance plans Linked ...
Erik Darling's user avatar
  • 40.8k
9 votes
Accepted

Move data from a table to another without losing data

Consider creating a new table with the partitioning strategy you want, and add a view atop both tables that does a union all. Have people use the view, and write instead-of triggers against the ...
Brent Ozar's user avatar
8 votes

How to move a database from SQL Server 2012 to SQL Server 2005

In addition to the methods already suggested here, you could also try creating a BACPAC file and import it into your destination. This is similar to how Microsoft recommends migrating your databases ...
Eitan Blumin's user avatar
8 votes
Accepted

Postgres 9.4 wal_level=logical increases disk space

This is an AWS DMS issue. DMS has recently added a feature WAL heartbeat [1] (run dummy queries) for replication from a PostgreSQL source so idle logical replication slots do not hold on to old WAL ...
Aravindan R's user avatar
7 votes

How do I REPLACE values in column after querying

You can use CASE for what you're using the replace for. Also, I've modified the WHERE to be more streamlined; SELECT CASE WHEN column = '1' THEN 'ABC' WHEN column = '2' THEN 'DEF' WHEN ...
Rich Benner's user avatar
7 votes

Is it possible to migrate a postgresql database without downtime?

If you need to migrate to the same version of postgres, then you can setup slave of your db on new server with streaming replication and then just promote it to master. There will be no downtime while ...
Pavel Kazhevets's user avatar
7 votes

Migrating Data from SQL Server 2005 to 2016

Max gave a decent answer which I will upvote once I'm done typing this alternate view up. I am not a fan of restoring system databases when doing an upgrade migration and I prefer doing migrations ...
Mike Walsh's user avatar
  • 18.2k
7 votes
Accepted

Easiest way to import access db (.accdb) into SQL Server 2016?

1.Open MS SQL Server Import and Export Wizard. 2.For “Data Source” choose “Microsoft Office 12.0 Access Database Engine OLE DB Provider”. 3.Click “Properties” enter the location of the .accdb file ...
bwilliamson's user avatar
7 votes

SQL Managed Instance - Cross Subscription Restores/Migration

Cross-subscription PITR restore for the same region and same Tenant ID (e.g.same customer/company only) is available as a limited preview for EA, CSP or PayGo subscriptions only at this time (April ...
Danimir Ljepava's user avatar
7 votes
Accepted

Adding indexes to production MySQL database without downtime?

If you wanted to add indexes to a live table, there are two major approaches: APPROACH #1 : pt-online-schema-change (Preferred Choice) This tool is spectacular. It will create a temp table for you and ...
RolandoMySQLDBA's user avatar
7 votes

Hosting older SQL DB on new server - what's the maximum compatibility level I can set

This will entirely depend on your database schema and code plus your application code. Your best bet is to run the Database Migration Assistant which can help identify incompatibilities within the ...
Stephen Morris - Mo64's user avatar
6 votes

What is the SQL Server equivalent of MySQL's utf8mb4 character set?

First: the particular "edition" of SQL Server (i.e. Express, Standard, Enterprise, etc) does not matter. All editions for a particular version will behave the same.   When I switch to "...
Solomon Rutzky's user avatar
6 votes
Accepted

Can I create a new user in MS SQL from windows and provide the SID

No, when creating a login from Windows, the SID is the Windows-provided one. There is consequently no need to remap users; they automatically assume the same SID across servers within the same domain....
Hannah Vernon's user avatar
  • 70k
6 votes
Accepted

SQL Server 2008R2 Alias not working

Summarizing what we discovered in the chat. When connecting over the network if the Windows Server is registered in Active Directory (AD) with a Service Principal Name (SPN), clients that try to ...
Aaron's user avatar
  • 1,771
6 votes
Accepted

Renaming table and putting a view in its place on Postgres

If you do this in production, be aware of prepared statements. Those have already been parsed, rewritten (and the query plan cached). The effect kicks until prepared statements are deallocated (which ...
Erwin Brandstetter's user avatar
6 votes
Accepted

Bypass MS Access 2GB file size limitation

You can't bypass the built-in limitation of an Access database. You can however circumvent the built-in limitation by using built-in features like splitting the database objects between multiple *....
John K. N.'s user avatar
  • 17.7k
6 votes
Accepted

Migrate 7TB of Database for Colo Migration

One could write a book on this topic, but one option would be an out-of-band initial replication of your database to the new site by flying a copy of the server hard drive(s) to New Jersey, followed ...
mustaccio's user avatar
  • 26k
6 votes
Accepted

How to definitively clear away database before restoring from new version of backup file

Just use WITH REPLACE in your restore command and it will overwrite the existing databases on the destination. Simple, and done. You can DROP first, but its not necessary. WITH REPLACE is the T-...
Kevin3NF's user avatar
  • 1,211
6 votes
Accepted

SQL Managed Instance - Cross Subscription Restores/Migration

copy only backups are currently the only solution. Cross subscription pitr and geo restore don't work because currently we cannot verify that initiator has all required permissions both on source and ...
Jovanpop's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible