As of May 31, 2023, we have updated our Code of Conduct.
61 votes

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

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
40 votes


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 assumed - typically not UTC. ...
Erwin Brandstetter's user avatar
25 votes

Backup/Restore Users/Passwords/Privileges

The 'mysql' database contains users/privileges/passwords. So take the dump of mysql database along with other databases mysqldump [options] --all-databases > all_databases_dump.sql mysqldump -u ...
mysqlrockstar's user avatar
24 votes

Is there a penalty for using BINARY(16) instead of UNIQUEIDENTIFIER?

Should I be concerned? Well, there are a couple of things here that are a little concerning. First: while it is true that a UNIQUEIDENTIFIER (i.e. Guid) is a 16-byte binary value, it is also true ...
Solomon Rutzky's user avatar
21 votes

Migrate from SQL Server 2000 to 2012 without a 2005 or 2008 instance

No, there is no workaround to upgrading a database directly from 2000 to 2012. Since you don't have a ton of data, you can do all kinds of things to move the data (but not the database as a whole), ...
Aaron Bertrand's user avatar
19 votes

Schema Migration: SQL Server Data Tools vs Liquibase and Flyway

SSDT is comparable to Liquibase/Flyway as it does what they do but by taking a different approach. With SSDT you have the development environment so you get things like go to definition, find ...
Ed Elliott's user avatar
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
  • 28.6k
15 votes

SQL Server data folder access rights

Have you looked under Advanced Security Settings? The arrows point to the modifications I've often had to specifically apply when installing SQL Server on Windows 2012 R2. For the SQL Server Root ...
Craig Efrein's user avatar
  • 9,600
15 votes

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
14 votes

Archiving of old data

I think the answer to many of your questions is that it depends. What performance problems are you having? It seems unusual that a database would have performance problems just from growing to 250GB ...
Geoff Patterson'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.3k
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

Move live SQL Server database to a new server

since you are using SQL Server 2008 Web edition, your best bet is to use LOG SHIPPING. Your database size is 100GB, so initializing the secondary from a backup should be considered. Dont use GUI to ...
Kin Shah's user avatar
  • 61.8k
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.3k
10 votes

Archiving of old data

I would prefer having a history schema or a second historical database over a linked server any day. It saves license costs is easier to manage and query. You can then also use simpler schema and drop ...
Spörri's user avatar
  • 4,639
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
  • 81.3k
9 votes

Moving Databases to New Datacenters

Your current situation : 90 GB database that you want to move to a new data center. Database is in simple recovery. You are thinking of using T-Rep to keep the data in sync. There are many tables in ...
Kin Shah's user avatar
  • 61.8k
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
  • 15.9k
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
  • 37.6k
9 votes

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
  • 42.5k
9 votes

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
  • 209
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

Archiving of old data

In my experience a second database would be the preferred choice for two reasons. You can restore the data from an historic backup then drop the tables and indexes you don't need. You can move this ...
Guy's user avatar
  • 175
8 votes

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

moving large PostgreSQL/PostGIS database

I would recommend dumping the 9.1 database from your new 9.3 server like this: pg_dump -h remoteserver -U remoteuser remotedbname -Fc -f my_old_server_backup.dump I recommend using the 9.3 pg_dump ...
Kassandry's user avatar
  • 3,539
7 votes

Set "Encryption Enabled" to FALSE...what can go wrong?

Do not disable TDE. Besides being a lengthy size-of-data operation, it was established initially for a reason and you may be breaking whatever compliance/operations reason exists for TDE. Rather set ...
Remus Rusanu's user avatar
  • 51.5k
7 votes

Migrating BLOB in Oracle into PostgreSQL

From a conceptual point of view the equivalent to Oracle's BLOB datatype is bytea in Postgres. bytea only allows you to read and write the complete data (you can't just retrieve 100KB from a 1GB ...
a_horse_with_no_name'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

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

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