Is Pseudo-Simple SQL Server Recovery a real thing? "Pseudo-Simple SQL Server Recovery" is term and scenario, I was just made aware of in a (now deleted) comment for the new question SQL Server Truncates Transaction Logs with Copy Only Backups

I went to the post Pseudo-Simple SQL Server Recovery Model October 7, 2019 by Rajendra Gupta and using some of the code there and some of my own did some testing.

Create the database (Rajendra's code)

CREATE DATABASE RecoveryModel;

and Validate it is in Full (Rajendra's code)

SELECT name,
    recovery_model_desc
FROM sys.databases
WHERE name = 'RecoveryModel';

Do some work (Rajendra's code, modified slightly)

Use RecoveryModel
CREATE TABLE test(id INT);
GO
INSERT INTO test
VALUES(1);
GO 5000

See how much log space is used (my code)

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

We find that there log is filling.  Run the work again and check the size, the log grows, no surprise.

Try running t-log (My code)

BACKUP LOG [RecoveryModel] TO
DISK = N'E:\SQLBackups\RecoveryModel.trn' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD
GO

It fails with message:

  Msg 4214, Level 16, State 1, Line 8

  BACKUP LOG cannot be performed because there is no current database backup.

  Msg 3013, Level 16, State 1, Line 8

  BACKUP LOG is terminating abnormally.

Nothing remotely simple about that, if you try to backup a database in Simple recovery. you get the message

  Msg 4208, Level 16, State 1, Line 19

  The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Run a copy only backup (my code)

BACKUP DATABASE [RecoveryModel] TO
DISK = N'E:\SQLBackups\RecoveryModel.bak' WITH NOFORMAT, INIT, COPY_ONLY,
NAME = N'RecoveryModel-Full Database Backup', SKIP, NOREWIND, NOUNLOAD
GO

It runs fine, check the log space and it has not shrunk.  Run the work load a few more time, and the log space continues to grow. Run a t-log backup and it continues to fail.

Run a differential backup (My code)

BACKUP DATABASE [RecoveryModel] TO
DISK = N'E:\SQLBackups\RecoveryModel.dif' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'RecoveryModel-Diff Database Backup', SKIP, NOREWIND, NOUNLOAD
GO

It fails just like the t_log

  Msg 3035, Level 16, State 1, Line 13
  Cannot perform a differential backup for database "RecoveryModel", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.

So what is "Pseudo-Simple" about this?  The logs grow, t-log & differnatinal backups fail.  You have a database in Full recovery, with no full backup.

Edit it looks like there is something specific to servers in my build that is causing results different than what everyone else is seeing.  I have accepted Josh's answer.

See this article from Paul Randal, an expert on database recovery models: New script: is that database REALLY in the FULL recovery mode?

In particular this quote confirms the behavior of a "pseudo simple" recovery model:

  ...when you switch a database into the Full recovery mode, it actually behaves as if it’s in the Simple recovery mode until the log backup chain is established (this is commonly called being in ‘pseudo-Simple’).

Kimberly Tripp elaborates on the topic here: Database Maintenance Best Practices Part III – Transaction Log Maintenance

  What that means is that SQL Server automatically clears inactive records from the transaction log once it knows that it no longer needs them. It no longer needs them to be stored in the log because no one is using the log

I don't totally agree with your statement "Nothing remotely simple about that"
 regarding the transaction log backup failing in the FULL recovery model prior to a FULL backup.  That's just like the SIMPLE model (log backups not allowed).  The error message is different because it's trying to tell the end user how to solve the problem.

I tried your test on SQL Server 2017, and running the copy-only backup consistently triggers an internal CHECKPOINT and clears the log (PercentFree increases for the log file).

On the initial run, PercentFree from the _log file is 65.04.
I ran the copy-only backup, and PercentFree jumped to 88.68.

I confirmed with the following extended events session that a CHECKPOINT was run at this time as well.

CREATE EVENT SESSION [checkpointage] ON SERVER
ADD EVENT sqlserver.checkpoint_begin(
    ACTION(sqlserver.database_id)),
ADD EVENT sqlserver.checkpoint_end(
    ACTION(sqlserver.database_id))
ADD TARGET package0.event_file(SET filename=N'checkpointage')
WITH (STARTUP_STATE=OFF)
GO


HKHostFreezeCkptTrimming on SQL Server 2017 Have a SQL Server 2017 Standard edition box with build 14.0.3045.24 with one memory-optimized table. When our 3rd party backups complete the SQL Server 2017 error log shows several [INFO] messages containing HKHostFreezeCkptTrimming(), HKHostBackupDeleteContext(), getMaxUnrecoverableCheckpointID() and with the same user database ID. Any of you familiar with these messages? Is this a bug or is there a fix for these messages?

Additional testing has shown these [INFO] messages appears during full, differential, transaction log, and manual copy-only backups. Performance is good and users aren't complaining, but considering the amount of backups that are required these messages are filling the error log.

Anything I can do to limit the entry of these messages to the error log? Or is this a case of dealing with additional messages?

I don't have an answer for what exactly they mean, but I see them on every machine where I have a memory optimized file group.

Since they start with "HK" they seem to be related to In-Memory OLTP (codenamed Hekaton prior to the production release in SQL Server 2014).

I would say that since they are INFO messages, and they are common on instances with memory optimized filegroups, that they are nothing to worry about.

There isn't currently a documented way of disabling those messages, and I hear that there really is no undocumented way of disabling them either.  There is a precedent for disabling backup-related info messages (see trace flag 3226), so you could make a request on user voice: SQL Server Uservoice


Query optimization when no row is returned I am using explain to figure out what is happening in my query which is:

explain select t from c where u1_id = 1 group by t;

I see "Using where" in the Extra column of the resultset. But this only happens if I use a valid u1_id. So if I run the same query, on the same server but with an invalid u1_id I get "Using where; Using temporary; Using filesort" in the Extra column.

I assume this means that when the user_id is not in the table then MySQL takes another route to process the query. Does anyone know why this happens and how can I prevent this from happening?

EDIT

Here is my table:

CREATE TABLE  c  (
   pk  int(11) NOT NULL AUTO_INCREMENT,
   u1_id  int(10) unsigned NOT NULL,
   u2_id  int(10) unsigned NOT NULL,
   s  decimal(11,5) NOT NULL DEFAULT ''0.00000'',
   date  datetime NOT NULL,
   f  decimal(11,5) NOT NULL,
   t  enum(''F'',''P'') ,
   ti  decimal(11,5) DEFAULT ''0.00000'',
   ts  decimal(11,5) DEFAULT ''0.00000'',
   latest  tinyint(1) DEFAULT ''0'',
   sc  decimal(11,5) DEFAULT ''0.00000'',
   p_id  int(10) unsigned NOT NULL,
  PRIMARY KEY ( pk ),
  KEY  i1  ( u1_id ),
  KEY  i2  ( u2_id ),
  KEY  i3  ( u1_id , u2_id ),
  KEY  i4  ( sc ),
  KEY  i5  ( sc , u2_id , latest ),
  KEY  i5  ( p_id ),
  KEY  i8  ( ti ),
  KEY  i9  ( t ),
  KEY  i10  ( p_id , u1_id , latest , t ),
 )

The best index for this query is (u1_id, t)

My initial guess was right, that you have indexes on (t) alone and on (u1_id) alone. You haven't told us the exact EXPLAIN output (which index is used), so the most probable explanation is that mysql is choosing to use one of these existing indexes or none at all (doing a full scan of the table), which yields the not so efficient plan.


What are the naming rules for named instances on a cluster? I am planning to install two SQL Server 2008 instances on an active/passive cluster. Each instance is supporting the same application, but the DBs will be supporting specific geographic locations. I'd like to name the instances to reflect the name of the application and the site, factoring in that at least one instance will be a named instance.

My understanding is that the SQL Server Virtual Name has to be registered in AD and can only be used once. If this is true, I can not have instances on this cluster named \EAST and \WEST as the  can't be registered in AD more than one, or can I?

Can I have two named instances on a cluster with the same base instance name or will the base instance name have to be unique for AD registration purposes?

You are correct in noting that the virtual name needs to be distinct. I'm having a hard time understanding what you're describing so I'm going to try to lay it all out.

SINGLE INSTANCE FAILOVER CLUSTER

If you're truly looking for "Active/Passive" then you're going to need at least four servers - two nodes for each instance. Let's call the instance SQLPROD-EAST.

You could then have:

Node A: SQLPROD-EASTA
Node B: SQLPROD-EASTB

Virtual Name: SQLPROD-EAST

as your nodes.

The virtual name would be SQLPROD-EAST and you'd install the default instance of SQL Server would would take the name SQLPROD-EAST. You'd then replicate this topology with SQLPROD-WEST:

Node A: SQLPROD-WESTA
Node B: SQLPROD-WESTB

Virtual Name: SQLPROD-WEST

MULTI-INSTANCE FAILOVER CLUSTER

This is where your description got confusing because it seems like you began describing Active/Active. This might look like:

Node A: SQLPRODA
Node B: SQLPRODB

Virtual Name: SQLPROD

as your nodes.

The virtual name would be SQLPROD and you'd install two named instances of SQL Server SQLPROD\EAST and SQLPROD\WEST.


Is it possible to change the MySQL server-id during a SQL session? I have a MySQL 5.5 server with a server-id of 20. I want to execute a few queries on that server, but have them write to the binlog as a different server-id than 20. Is this possible?

I am aware set sql_log_bin is commonly used to turn binlogging on/off at-will, but I actually do want to execute these queries and have them written to the binlog, just as another server-id.

set server_id=21 errors saying it needs to be global, not a session variable, so I may already have my answer, but still, I have a feeling it is possible somehow. I mean, the mysql replication slave component can do it.

server_id is a dynamic variable, so you can change it while the server is running. To change the global variable, do:

SET GLOBAL server_id=21

More info at http://dev.mysql.com/doc/refman/5.5/en/replication-options.html#option_mysqld_server-id


How to access oracle 11g database remotely I have oracle database on my computer that i am accessing through a java program using localhost:1521 more precisely using ojdbc7 and host String as "jdbc:oracle:thin:@localhost:1521:orcl". Everything is working fine but now I want to access same database through another computer with different internet connection. I read that I have to change listener.ora file and add ip address where localhost is.

Now I have tried using ip address of my computer which i have set to 192.168.1.5 in network connection and same in listener.ora file and created a virtual server in my router settings and set this same ip address and port there as well. Still when I try to connect to server computer's Internet IP(not 192.168.1.5) it states "TNS:no listener".

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
    )
  )

Firewall is off. I have also tried using 127.0.0.1 and creating virtual server for that in router as well but didn't work. Oracle 11g is installed on both server and client's computer.

I have also tried using 0.0.0.0 ip in listener.ora file so it may listen to all ip's but that didn't work as well.

Can someone point me in the right direction.

Edit: Different internet connection means one is my home internet (ip:39.40.xx.1xx) other is my friends home internet (different ip) we live few miles apart and use same ISP. My computer is acting as server which is using Windows 10. Its firewall is disabled. I am assuming that my friend would use my internet IP address which would be forwarded to my computer (192.168.1.5)

Presuming port forwarding and network items are working as expected (can be checked with telnet host 1521).

Make sure that you restart the listener service.

Also connect to the database from your local machine and run the following commands:

ALTER SYSTEM SET LOCAL_LISTENER = "(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.5)(PORT=1521))";

ALTER SYSTEM REGISTER;

Outside of that it looks good from a listener config side.


MySQL - change all tables' ENGINE from MyISAM to InnoDB with a single query I had some foreign key issues in my database when I change the table engine "MyISAM" to "InnoDB" but then it works fine.

I found one query to change a single table's engine but I have many tables so what I want to know is there any way to do it all once?

ALTER TABLE tbl_name ENGINE=INNODB;

What else I have tried?

I also tried to update the my.ini file by adding the code below but it doesn't work!

default-storage-engine = InnoDB

I found a query way is more convenient as of my understanding so any suggestion for a change it all table engine at once?

This is the Database server detail:

Server: 13227.3232.3232.3232 via TCP/IP
Server type: MariaDB
Server version: 10.4.10-MariaDB - mariadb.org binary distribution
Protocol version: 10

My strong advice is not to try and do this on a running database! MyISAM isn't transactional in any case, so any operation that spans many tables can't be performed in this manner (it can in InnoDB - your reason for wanting to change?). You will have to either dump the db and modify the text file or iterate through the tables.

MySQL is moving firmly away from MyISAM - it's being deprecated. Not even the system tables are MyISAM anymore (MySQL >= 8.xx).

BTW, please always include the version of MySQL that you are using - it can be very important - see below!

Therefore, I propose the following solution:

Step 1:

Take a backup of your database using mysqldump - you should be doing this on a regular basis anyway - here is a reference (there are many!):

mysqldump database_name > database_name.sql

of if you have multiple databases on the same server:

mysqldump -u root -p --databases database_name_a database_name_b ... > databases_a_b.sql

Step 2:

Using the text editor of your choice, go through the file and search for the word ENGINE and replace each occurrence of ENGINE=MyISAM with ENGINE=InnoDB.

If you're on *nix, then something like this will do the trick:

sed -e 's/ENGINE=MyISAM/ENGINE=InnoDB/g' > new_file.sql

Do not do this for every occurrence of MyISAM in the entire MySQL server! If you are using a version of MySQL which still has MyISAM system tables - this is very risky - from here - MySQL 5.7. Will not apply for later versions which have moved the system tables to InnoDB anyway:

  Warning Do not convert MySQL system tables in the mysql database from
  MyISAM to InnoDB tables. This is an unsupported operation. If you do
  this, MySQL does not restart until you restore the old system tables
  from a backup or regenerate them by reinitializing the data directory
  (see Section 2.10.1, “Initializing the Data Directory”).

So, do not do the change for any tables in the mysql, information_schema or performance_schema databases - only in the databases that you've created yourself.

Step 3:

Restore from this backup and you're good to go! You should drop your original database and recreate an empty schema (see reference above) of the same name and then:

mysqld  database_name < new_file.sql

At this point, you may wish to implement FOREIGN KEYS - one of the many good reasons to upgrade to InnoDB! Be careful - MyISAM doesn't have DRI (Declarative Referential Integrity) so there may be orphaned records in your existing system - you might want to run tests to check on this?

Running system alternative:

If you're unable to bring down the database, then a solution like this (iterating through every table in the schema) might be helpful (adapted from here and here):

CREATE PROCEDURE UpdateTables ()
BEGIN
  DECLARE Finished BOOL DEFAULT FALSE;
  DECLARE TableName VARCHAR(64);

  DECLARE TablesCursor CURSOR FOR
    SELECT c1.TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c1
    WHERE c1.TABLE_SCHEMA = 'my_schema';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = TRUE;

  OPEN TablesCursor;

  MainLoop: LOOP
    FETCH TablesCursor INTO TableName;
    IF Finished THEN
      LEAVE MainLoop;
    END IF;

    SET @queryText = CONCAT('ALTER TABLE ', TableName , ' ENGINE=InnoDB');  -- may or may not need backtics
    PREPARE updateQuery FROM @queryText;
    EXECUTE updateQuery;
    DEALLOCATE PREPARE updateQuery;
  END LOOP;

  CLOSE TablesCursor;
END

Note: this is untested - please use with caution and do your own tests before committing to a definitive solution. I would also advise doing something like this when the system load is as light as possible!

Alternatives to the alternative (again, not tested):

If you're a PHP person, this might be suitable.

Another (interesting) approach is this one by Shlomi Noach - a big hitter in the MySQL world.


Table Lock on INSERT - How to optimize we have a few long running procedures in the database, that cause an instant Table Lock on our main text storage table, which causes all other requests of the web app to wait (because the table is used in nearly every request).

Setup

Web Application with ASP.NET, Entity Framework, SQL Server 2008R2
A lot of Tables (Table per Class Strategie) with data attributes
ONE Table ("dbo.Translations") for dynamic defined texts (to enable translations).
No foreign keys to this table

To get the text of the object in the right language, the Object ID is saved to the translation table with the text value

Table Definition

So the Translation Table looks like:

  dbo.Translations (ID varchar(36), type varchar(10), lang varchar(5), value varchar(max))

whereas Type is defined for each object and lang is like 'en-GB' (not relevant).

Index

The table has two indexes:
PK_Translations on ID (Clustered, not unique)
UNQ_Translations on all columns (nonclustered, unique)

Problem

The table has to handle two operations:
SELECTS and INSERTS.

Update and delete is not a common use-case.

The table holds nearly 1.000.000 rows and an index over ID,type,lang.

When the web application wants to insert (multiple) records, the operation takes a lot of time and creates an instant table lock. This stops every other request to this table (maybe even to other tables, so sometimes it seems to be a database lock?!) and causes them to wait.

Inserts

The INSERTS of large data is done in the following way:

INSERT INTO Translations
   (Id, Typ, lang, value)
SELECT
   td.nId,
   td.Typ,
   td.lang,
   td.value
   FROM #tempDictionary td

Note: #tempDictionairy is a temporary table

Steps

The following steps where tested and did not improve the situation:

Start the SELECT transaction with read uncommitted -> no change
Extend the index sparsity to 50% to ensure that the inserted records can be saved easily -> no change

How can I find out why the SQL Server creates an table / db lock?

Are you 100% sure that this process is escalating to a tablock? You can verify this with Adam Machanic's sp_whoisactive. Take a snapshot (execute the proc) while the process is running (and presumably blocking). Collect the locks and the query plan - this will give you information needed to fix this issue. Please note what the spids waiting in the insert are waiting on...If they are waiting on latches -we have a page split situation locks its a blocking/locking issue
Remember lock escalation happens when a single statement graps 5K locks on an table or index. (http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx) I have found that using the paglock hint (so you take fewer coarser locks) can be used to avoid tablock escalation.
Take a look at the query plan collected in step 1. What is the cardinality estimate of the row being insert (i.e. the select statement)... Is it much greater than 5K? If it is you might want to code a loop - so that you are only inserting 2500 rows at a time...


Restoring an Oracle database from files I am running into a problem now, i have an oracle database that is stored inside a server and now because of the sudden blackout the c drive is messed up. The drive is partitioned, windows on c and oracle on drive e.

The oracle installation folder is fine on E:\app. Now i have to restore that database on the another server to get it running. The problem is i cant boot windows on that drive.

Anybody can help me on restoring the database from oracle installation folder ? I know i cant run rman because rman can only be run from command line only which i cant run it now.

First off, you would want to install a new instance somewhere—possibly on the same host.

Then you would need to mount the control file (the path to the control file can be specified by setting the control_files parameter value in pfile).

Once you've mounted the control file with alter database mount, you can use RMAN to identify the data (temp, undo) files' location with report schema command.

You would also need to identify the undo files' location. Use V$LOGFILE view.

If you want to move the files around before opening the database, use RENAME FILE .. TO .. clause of the ALTER DATABASE statement.

When you're done with all these tasks, you can try to open the database.

SQL> alter database open;

See also.

Redo Log Data Dictionary Views
Renaming and Relocating Datafiles
Relocating and Renaming Redo Log Members


Calculate the total on events with two time conditions I have a table in BigQuery that looks something like this:
    schema = [
        bigquery.SchemaField('timestamp', 'TIMESTAMP', mode='REQUIRED', description='Data point timestamp'),
        bigquery.SchemaField('event_id', 'STRING', description='EventID'),
        [...]
    ]

The table has a fairly large dataset, and I'm trying to find write an efficient query that returns the number of events that happened in the last 24 hours but also within the last N days. That is, two different records with different conditions but the same event_id. I don't care so much about the actual event_id, but rather the distribution.
Ideally, the query would return something like this:
7_days: 20
30_days: 15
60_days: 7

If it's impossible to do this in pure SQL, I also have Pandas available at my disposal.

Thanks to @mustaccio's pointer above. Here's how I managed to get it to work using INTERSECT DISTINCT.
SELECT COUNT(*) FROM (
  SELECT DISTINCT
    event_id
  FROM
    `foo.bar.blah`
  WHERE
    DATE(timestamp) = current_date()

  INTERSECT DISTINCT

  SELECT DISTINCT
    event_id
  FROM
    `foo.bar.blah`
  WHERE
    (DATE(timestamp) BETWEEN DATE_SUB(current_date(), INTERVAL 30 DAY) AND DATE_SUB(current_date(), INTERVAL 10 DAY))
)

The two dates in the last SELECT needs to be adjusted according to the criterion of course.


Create domain user offline Can I add domain users to a database, when Sql Server A (the development server) is not part of the domain and has absolutely no access to the domain?

The domain users are of course not expected to work on server A, but these users are already configured on Sql Server B, the production sql server.

When a backup is taken on the development server A and deployed on the production server B, currently these users have to be re-added to the database on server B.

(The users do not have sysadmin rights on server B. As far as I know, if they had sysadmin rights, they would automatically have rights to access any new databases anyway)

Is it somehow possible to CREATE USER domain\xy on development server A, so that they will work when restoring a backup on the production server B?

Or as a workaround, could I take a blank database backup with only the users from server B, and then use that backup as a base to create new databases on server A?

Short answer: your workaround is fine, other than that: no.
Long answer:
It is not possible to create a user for a non existing domain, though the error message is somewhat strange:
CREATE USER [INVALIDNAME\SOMEUSER] FOR LOGIN [VALIDNAME\SOMEUSER] WITH DEFAULT_SCHEMA=[dbo]
GO
Msg 15006, Level 16, State 1, Line 5
'INVALIDNAME\SOMEUSER' is not a valid name because it contains invalid characters.

Running the same with "VALIDNAME" domain on both user and login side
works fine.

even if it would work, it would create a bunch of other issues for you (SID of INVALIDNAME\SOMEUSER not valid, mapped to wrong login, etc)

Your Workaround
With your workaround, on the other hand, it will be okay as the users are not touched when you restore the database on any server.
Another Workaround
Create a staging instance in the same domain as server B is, create all logins you need and create a database that contains all the users mapped to the logins you need.
Then, restore both, the master and your database, in a new instance running outside your domain.
I haven't tested this, but I am quite sure it should work.


Reporting Services 2016 Multiple Email Accounts Is there a way to configure SSRS 2016 to be able to send email from more than one email account?  I am looking to setup something that is department specific for our organization.  Right now its defaulting to one department and it doesn't really fit our growth plans.

You can only specify one mail account per SSRS installation.


Oracle Materialized View - Is it Safe to Use Only ROWID as Record Identifier? this is a question related to Oracle 12c.

I'm currently trying to create MVs as data sources for Golden Gate replication. We need fast refresh because We have to replicate 60,000 rows + at one time and deleting and reinserting all rows seem to be inefficient.

The base tables have unique columns which act as composite primary keys but there's no primary key constraint defined in them. They're peoplesoft tables so We prefer not to add pk constraints if possible, as They're used in so many applications.

I have built the MVs using only WITH ROWID clause for the materialized view logs and They seem to be working fine. But I don't know if it's a good practice to do so, should I add PK constraints on all the master tables instead? or maybe there's another way?

Feedbacks are appreciated. Thanks.

ROWIDs are good to use in a transient sense, but they're a poor choice for long-term storage.

"You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later." Source: https://docs.oracle.com/database/121/SQLRF/pseudocolumns008.htm#SQLRF00254

You may need to import and export this database when you upgrade the solution or when you refresh a nonproduction environment. If you do decide to use the ROWID anyway, then a complete refresh of the materialized view would be prudent whenever you copy or move data.


Concurrent SELECT and UPDATE in multiprocessing python I have to randomly match about 20 million rows in two tables without using the same row twice using PostgreSQL 9.3.5. Both tables have the following structure

CREATE TABLE de_sim_points_end
(
-- Inherited from table de_sim_points:  id integer NOT NULL DEFAULT nextval('de_sim_points_id_seq'::regclass),
-- Inherited from table de_sim_points:  parent_geometry character varying(12),
-- Inherited from table de_sim_points:  used boolean DEFAULT false,
-- Inherited from table de_sim_points:  geom geometry(Point,900913),
  CONSTRAINT de_sim_points_end_pkey PRIMARY KEY (id)
)
INHERITS (de_sim_points)
WITH (
  FILLFACTOR=50,
  OIDS=FALSE
);
ALTER TABLE de_sim_points_end
  OWNER TO benjamin;

CREATE INDEX de_sim_points_end_geom_idx
  ON de_sim_points_end
  USING gist
  (geom)
  WITH (FILLFACTOR=100);
ALTER TABLE de_sim_points_end CLUSTER ON de_sim_points_end_geom_idx;

CREATE INDEX de_sim_points_end_parent_relation_idx
  ON de_sim_points_end
  USING btree
  (parent_geometry COLLATE pg_catalog."default")
  WITH (FILLFACTOR=100);

CREATE INDEX de_sim_points_end_used_idx
  ON de_sim_points_end
  USING btree
  (used)
  WITH (FILLFACTOR=50);

The SQL Query and Python Code used to match the rows can be found at https://github.com/boerngen-schmidt/commuter-simulation/blob/master/code/builder/process_point_mass_matcher.py (I will not post it here due to its length). But basically what I do is:

SELECT rows, randomly ordered from start point table
JOIN rows, randomly ordered from destination point table
Take the result, iterate over it and update the points in start and destination table as used
Save the result in another table (done by another thread)

When I was using multiple processes with SELECT ... FOR UPDATE I ran into deadlocks (currently I just use one process, so the FOR UPDATE statement is currently omitted). So far I have read a bit about SERIALIZABLE and so forth locking mechanisms, but yet I fail to fully grasp them.

Is it possible to make the matching multiprocessing compatible? How whould the statements look like?

As I dug deeper into the matter at hand trying various approaches I found out that there is no solution to this problem with PostgreSQL versions <= 9.4.

PostgreSQL 9.5 (current development version) has a new feature called SKIP LOCKED which does skip rows that have been locked by a query using SELECT * ... FOR UPDATE

For more information on the new feature have a look at http://www.depesz.com/2014/10/10/waiting-for-9-5-implement-skip-locked-for-row-level-locks/


PostgreSQL: translating user-defined calculations into executable calculation in trigger I am looking to convert user defined machine readable string formulas into a set of queries and calculations, the result of which would then be saved in another non-normal table. The calcs would be executed via a trigger and a generalized/dynamic/flexible calculation function(s) that would execute when a certain table (bbg_pulls in the simple example below) is updated or inserted on.

For example, if the string passed into the calculation function was:

'[bp2][-1]/[bp5]'

Where [bp#] designates the table (ex: bp = bbg_pulls) and id and [+/-#] designate the period lag (day lag in my example) relative to now() for ID just to the left of it.

Then the query would be something like:

select val1/val2 as fin_calc
from
    (select val from bbg_pulls
    where
      id = 2
      and val_date = (select (CURRENT_TIMESTAMP - INTERVAL '1 day')::date)) as val1
    ,(select val from bbg_pulls
    where
      id = 5
      and val_date = (select CURRENT_TIMESTAMP::date)) as val2

(Note that all of the above made up and I have no way to test the queries so they may be syntactically off etc. Hopefully you understand my point though.)

My example above only includes division of two items in the same table, but other strings could be much more complicated, and I would need to incorporate things like percentile rank, the basic mathematically operates, absolute value, etc.

We use PostgreSQL 9.3.5. The queries above are dramatically over simplified for clarity.

What is the correct approach here? Warehouse/MDX/cube solution? Or is there something like this out there already?

My current approach/thinking is to go through all of the various calculations we use and define a separate query etc. in psql for each, create some logic to find out which type of query/function is desired and pass in the IDs as parameters to the specific query/function that was identified. In other words, not a generalized calculation generation function that can take any string calculation, but a set predefined functions and queries that all have to be defined separately and identified in the string formula that is passed in. One would exist for each minor difference in calculation, and we have a lot.

If this question is not answerable, please let me know and I'll take it down.

Essentially, you're asking how to implement a DSL  that generates dynamic SQL based on that proprietary non-spec'd query string '[bp2][-1]/[bp5]'. That's a massive task. I'm not going to be able to answer this question on this medium, it's far from a Q/A: "it's like how do I do this job." That said, this is how it would look.

Create a specification of what kind of features you intend to support with '[bp2][-1]/[bp5]' and '([bp1]/[wc66])*[wc100]', and the numerous other examples you've given in your questions.
Create a parser. This is an art in and of itself. You going to use regexes, a tree parser, a sax parser? What exactly do you need to meet the specification?
Use an off-the-shelf solution that can translate the parsed structure to SQL. Most languages have something that can do this, like SQL::Abstract. If not, you gotta create it.


Are stored procedure query texts stored in any tables? I don't readily see anything in the sys schema objects, other than the DM_EXEC_SQL_TEXT function that requires a plan handle, but the procedures I'm trying to get their query texts from aren't currently running. (Maybe there's a way through their cached plans?...though the ones I'm looking for might not have a cached plan.)

Are the query texts of stored procedures stored anywhere that's queryable?

I see sp_helptext might be an option: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/view-the-definition-of-a-stored-procedure?view=sql-server-ver15#TsqlProcedure

There are several ways to get this that require different input information

INFORMATION_SCHEMA:

You only need to know the name of the object you want the definition for.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyProcName'

Note, this schema is database specific, so you need to be in the context of the database where the procedure exists for this query to return the correct data. Also note, it only returns the first 4000 characters of the procedure.

OBJECT_DEFINITION:

You need to know the object_id of the procedure you're looking for.

SELECT OBJECT_DEFINITION(*object_id*)

Database-specific as well, but returns the complete text of the object.

sys.sql_modules:

You need to know the object_id of the procedure you're looking for.

SELECT * FROM sys.sql_modules WHERE object_id = *object_id*

Database-specific as well, but returns the complete text of the object.


oracle sql: How to not select select duplicate records from table Can any help me to fix the below sql query

       SELECT DISTINCT ORDER_NUMBER, FLAG
FROM TABLE

Below was the result

Order_number FLAG

LP-13288  false

LP-13288  true

LP-13292  false

LP-13290  true

LP-13293

LP-13291   true

LP-13289   false

Desired output

Order_number FLAG

LP-13290 true

LP-13291 true

how to not select the duplicate orders and only print the order which has flag true.

Any inputs are appreciated.

Thanks

You can use NOT IN:

    SELECT DISTINCT t1.ORDER_NUMBER, t1.FLAG
    FROM TABLE t1
    WHERE t1.FLAG='true' AND t1.ORDER_NUMBER NOT IN
    (SELECT t2.ORDER_NUMBER FROM TABLE t2
    WHERE t2.FLAG='false' OR t2.FLAG IS NULL)

If you don't care about nulls (that is you only want to exclude order numbers that explicitly have a false flag) just remove the "OR t2.FLAG IS NULL" condition. Also this query will return orders where possibly more than one records with flag 'true' exist (as long as there are no records with flag 'false' or null)


SQL Server: How to get total number of backups done in a month? I need to gather monthly statistics for my boss, on how many FULL backups were done in a month and which number Succeeded and which failed if any.

Is there a simple query that could give me this, based on SQL Server logs?

Thanks

You might use below script to get history of backups in last 1 month:

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 30)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

Here 30 is a variable, which can be changed with any number up to which you want backup details however this would work only if you are not archiving backupset table in msdb database.

Please refer below links for detailed info on these backup sets and what is backup fails:

https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/

Query that displays backup information (successful and failed) SQL Server

Hope this helps.


How to transfer a few databases from MariaDB master to another master I must transfer databases (everything without system's databases) from one replication server to another replication server.
Everything is on MariaDB (10.0.20) and i need to do it with minimal downtime.
On source servers I have master (serverA1), slave (serverA2), slave (serverA3) replication.
On destination server I have the same situation - master (ServerB1), slave (ServerB2), slave (ServerB3).
Now I must move databases from servers A to servers B, with minimal downtime and not to ruin replication.
Server A and B have different databases.
In future Server A, will be offlined.

The only thing that you can do is this: ServerB must become a slave to ServerA

Let's assume the following

ServerA1 has an IP of 10.20.30.40
ServerA1 has databases db1, db2, db3
ServerB1 has databases db4, db5, db6
ServerA2 is a Slave of ServerA1
ServerA3 is a Slave of ServerA1
ServerB2 is a Slave of ServerB1
ServerB3 is a Slave of ServerB1

Here are the Steps

STEP 01

Make sure ServerA and ServerB and their slave has unique server_id values

STEP 02

OPTION #1 : Dump from ServerA1

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN "
SQL="${SQL} ('information_schema','performance_schema','mysql','test','sys')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL} 2>/dev/null"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --master-data=2"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} | gzip > DataFromServerA.sql.gz

OPTION #2 : Dump From a Slave of ServerA1

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN "
SQL="${SQL} ('information_schema','performance_schema','mysql','test','sys')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL} 2>/dev/null"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --dump-slave=2"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} | gzip > DataFromServerA.sql.gz

Note: In option #2, you use --dump-slave. This tells a slave to publish its master's binary log file/pos to the output of mysqldump instead of its own.

STEP 03 : Get the Replication Log File and Log Position

less DataFromServerA.sql.gz | head -23 | tail -2

This will echo the CHANGE MASTER TO with the log file and position of the Master

For the sake of this example, say the output is

-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.001236', MASTER_LOG_POS=102938;

STEP 04 : Transfer Dump into ServerB1

STEP 05 : Load Dump into ServerB1

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
gzip -d < DataFromServerA.sql.gz | mysql ${MYSQL_CONN}

STEP 06 : Create Replication User for the Cutover

On ServerA1, run

GRANT REPLICATION SLAVE,REPLICATION CLIENT
ON *.* TO 'cutover'@'%'
IDENTIFIED BY 'cutover';

STEP 07

On ServerB1, run

CHANGE MASTER TO
master_host='10.20.30.40',
master_port=3306,
master_user='cutover',
master_password='cutover',
master_log_file='bin-log.001236',
master_log_pos=102938;
START SLAVE;
SLAVE SLAVE STATUS\G

NOTE: Please make sure there are no firewall issues

STEP 08 : Wait for Replication to Catch up

Keep running on ServerB1 SLAVE SLAVE STATUS\G until Seconds_Behind_Master is 0

STEP 09 : Stop your writes to ServerA1

After disabling all apps that write to ServerA1, run this on ServerA

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

STEP 10 : Point all the apps at ServerB1

STEP 11 : Disable MySQL Replication from ServerA1

On ServerB1, run

STOP SLAVE;
RESET SLAVE ALL;

STEP 12 : Decommission ServerA1, ServerA2, ServerA3

CAVEAT : Please try all this out on Staging Servers Before Doing So on Production

GIVE IT A TRY !!!


Are string columns with very few different entries automatically compressed? We have a table which contains a column with only a dozen different strings in it. Is there anything I need to do in order to get the DB to compress this information? We don't have too much disk space available and it seems like a waste to store all these long stings over and over again.

I would like the DB to store this information internally in a compressed form. For example, the current data looks like this:

Column "TypeInfo"
---------------------------------------------------------
very_long_descriptor_someone_came_up_with_a_long_time_ago
this_desciptor_is_also_very_long
nobody_knows_why_this_descriptor_is_so_long
very_long_descriptor_someone_came_up_with_a_long_time_ago
very_long_descriptor_someone_came_up_with_a_long_time_ago
nobody_knows_why_this_descriptor_is_so_long

I'd like to see SQL Server store some kind of small key instead that represents those longer strings that are repeated:

Column "TypeInfo"
-----------------
1
2
3
1
1
3
(+ mapping information)

No, SQL Server does not do anything like this automatically. You can achieve some of what you're after with data compression, but its highest granularity is at the page level. So you'll get some good compression in the index pages if you have an index on only this column (or at least with this column as the leading key column), but assuming the clustered index is not on this column, you'll get much less benefit from it when the data and other index pages contain many variations of the string.

Data compression uses multiple algorithms, including dictionary, which would have the best impact if all of the values on a page are the same. Even at its best, though, this is not going to be extremely valuable on a large table - imagine you have a million pages and each page has two copies of one of those values. Sure, compression buys you some savings by storing the value only once instead of twice on each page (plus some negligible pointer overhead), but SQL Server still stores a million copies - one per page!

What I suggest, instead of storing the same dozen strings over and over again, is to create a lookup table with a TINYINT key, allowing you to only store each description string once, no matter how many times it's used. You can always retrieve the description at query time without ever having to store it with the data, and you can even create views to make this mostly transparent to queries, applications and users. This can also be a more attractive option if your workload is CPU-bound; while data compression saves you on storage and memory, there is some CPU overhead involved in compressing and decompressing each page.

For example:

CREATE TABLE dbo.TypeInfo
(
  TypeInfoID TINYINT PRIMARY KEY,
  Description VARCHAR(64) NOT NULL UNIQUE
);

INSERT dbo.TypeInfo(TypeInfoID, Description) VALUES
  (1,'very_long_descriptor_someone_came_up_with_a_long_time_ago'),
  (2,'this_desciptor_is_also_very_long'),
  (3,'nobody_knows_why_this_descriptor_is_so_long');
-- ... 9 other values ...

Now you need to add the TypeInfoID column to the original table:

ALTER TABLE dbo.OtherTable
  ADD TypeInfoID TINYINT;

Then you can update the existing data as follows:

UPDATE o
  SET TypeInfoID = t.TypeInfoID
  FROM dbo.OtherTable AS o
  INNER JOIN dbo.TypeInfo AS t
  ON o.TypeInfo = t.Description;

(And process this in batches, if the table is large, and you want to minimize impact to the log and blocked operations.)

Then you can add a foreign key constraint:

ALTER TABLE dbo.OtherTable
  ADD CONSTRAINT fk_TypeInfo
  FOREIGN KEY (TypeInfoID) REFERENCES dbo.TypeInfo(TypeInfoID);

Once all of the data is verified to be correct, you can drop the column (first drop any constraints and indexes that reference it):

ALTER TABLE dbo.OtherTable
  DROP COLUMN TypeInfo;

Then you can create a view that your queries can use to keep the semantics the same:

CREATE VIEW dbo.vOtherTable
AS
  SELECT /* o.columns */, TypeInfo = t.Description
    FROM dbo.OtherTable AS o
    INNER JOIN dbo.TypeInfo AS t
    ON o.TypeInfoID = t.TypeInfoID;

(Or in some scenarios you could even rename the table, and give the view the name the table used to have, making it completely transparent.)

And finally, if you don't want to allow NULLs in this column, and if you are on SQL Server 2012 or above, you can alter the column to NOT NULL without incurring updates and logging:

ALTER TABLE dbo.OtherTable
  ALTER COLUMN TypeInfo TINYINT NOT NULL;

If you are on an earlier version, you might consider adding a CHECK CONSTRAINT instead:

ALTER TABLE dbo.OtherTable
  WITH NOCHECK ADD CONSTRAINT ck_TypeInfoNotNULL
  CHECK (TypeInfoID IS NOT NULL);

But this may not be ideal, as an untrusted constraint can affect the ability of the optimizer to use it... in a lot of cases it is better to just take the one-time performance hit up front.

I think much of this is way beyond the scope of your original question, and you may not even consider any of this solution compelling, but if you do, you may want to do your own research and testing to ensure the least intrusive method of making this change. You can start with the following questions here and on Stack Overflow:

Quickly change NULL column to NOT NULL
Why does ALTER COLUMN to NOT NULL cause massive log file growth?
Why does simple ALTER TABLE command take so long on table with full-text index?
https://stackoverflow.com/questions/287954/how-do-you-add-a-not-null-column-to-a-large-table-in-sql-server


If "large value types out of row" table option is enabled, the table size is increased I have recently learned about sp_tableoption which gives you the ability to store "large value types out of row" for particular table.

A colleague of me told me if I enabled it, each field is stored on 8 KB page at least, no matter how large it is. I was not able to find a confirmation of this, so I run the following test:

DROP TABLE Size

CREATE TABLE Size
(
    [ID] TINYINT
   ,[Text] NVARCHAR(MAX)
)

EXEC sp_tableoption 'Size' ,'large value types out of row' ,0

TRUNCATE TABLE Size

INSERT INTO Size
SELECT 1, N'in row text'
UNION ALL
SELECT 2, N'' -- add here very large string

SELECT [ID], DATALENGTH([Text]) Size, LEN([Text]) [Len]
FROM [dbo].[Size]

EXEC sp_spaceused Size;

DROP TABLE Size

CREATE TABLE Size
(
    [ID] TINYINT
   ,[Text] NVARCHAR(MAX)
)

EXEC sp_tableoption 'Size' ,'large value types out of row' ,1

TRUNCATE TABLE Size

INSERT INTO Size
SELECT 1, N'in row text'
UNION ALL
SELECT 2,  N'' -- add here very large string

SELECT [ID], DATALENGTH([Text]) Size, LEN([Text]) [Len]
FROM [dbo].[Size]

EXEC sp_spaceused Size;

and get following results:

So, it seems that really the first row is now occupying more space (8 KB exactly). So, I add one more row and do the test again:

INSERT INTO Size
SELECT 1, N'in row text'
UNION ALL
SELECT 1, N'in row text'
UNION ALL
SELECT 2,  N'' -- add here very large string

The result was the same. So, it is not true that each row is occupying at least 8 KB, but the size is actually increased. I know that:

  varchar(max), nvarchar(max), varbinary(max), xml and large
  user-defined type (UDT) columns in the table are stored out of row,
  with a 16-byte pointer to the root.

But this 16 byte pointer can be ignored in this case.

Can anyone tell is there a way to determined how much space overhead small values are going to cost if the option is true?

I am planning to apply this option to a large table and want to calculated how the table size will be affected.

Note, I know that the value is moved out of the row when it is inserted or updated only.

If you use

DBCC IND('TestDb', 'Size', -1)

You can obtain the file and page number of the LOB pages (where your "out of row" values are saved)

Now, with that information get a dump fo the page:

DBCC PAGE('TestDb', 3, 938947, 1) WITH TABLERESULTS;

From those results I can see that for strings with 64 bytes or less the record size will always be 84 bytes. For longer strings will be the actual bytes plus 14 bytes (I didn't analyse strings longer than a page)
And the storage engine will try to fit as many of those records in a LOB page.


How can I generate TPC-DS queries for PostgreSQL? I'm using TPC-DS to run some benchmarks on a few versions of postgres that I have. I noticed that there is no template for generating the TPC-DS queries with the target database as postgres. The query_templates directory contains template files for ansi, sqlserver, db2, oracle, and netezza, but none for postgres.

I've tried running the queries generated by each of the previously listed targets on postgres, but they all seem to have some kind of syntax that postgres doesn't support, because most of them fail to run.

Is it possible to generate these TPC-DS queries for postgres? Or is there a postgres.tpl file that can be downloaded somewhere?

Not sure about the TPC-DS, but I know that HammerDB will perform a TPC-H(ad hoc DSS) benchmark test against PostgreSQL.  If you're looking for a benchmark, but not specifically a TPC-DS, this is a good tool for testing.


Introducing an 'age' category in a patients database I want to do the following in my DB related to patients:

when patient inserts his birthday it shows his age category as: infants, children, adult, elderly... etc. to help me classify my patients
how can I translate the birthday into age category?
how to design that age category?

Some of this depends upon your particular database implementation.  The part that is general, however, is the idea of an age category.  Here's one implementation, using ID as a surrogate key and written to work with SQL Server (meaning you would probably need to alter the syntax a little bit to get it to work with another product):

CREATE TABLE dbo.AgeCategory
(
    ID tinyint identity(1,1) NOT NULL,
    MinimumAge tinyint NOT NULL,
    Name varchar(20) NOT NULL
);
ALTER TABLE dbo.AgeCategory ADD CONSTRAINT [PK_AgeCategory] PRIMARY KEY(ID);
CREATE UNIQUE INDEX [IX_MinimumAge] ON dbo.AgeCategory(MinimumAge) INCLUDE(Name);

INSERT INTO dbo.AgeCategory(MinimumAge, Name) values
(0, 'Newborn'),
(1, 'Infant'),
(2, 'Toddler'),
(5, 'Child'),
(12, 'Adolescent'),
(18, 'Adult'),
(30, 'Some marker'),
(40, 'Something else'),
(50, '50+'),
(65, 'Retiree'),
(90, 'Amazing');

I've populated this with sample ages and categories.  Here's an example of how you could use this table.  I'm going to populate a Person table with dates of birth, and then get a query to find their age category.

Here's the sample data:

CREATE TABLE dbo.Person
(
    ID int identity(1,1) NOT NULL,
    Name varchar(50) NOT NULL,
    DateOfBirth datetime NOT NULL
);

insert into dbo.Person(Name, DateOfBirth) values
('Bob', '1983-01-08'),
('Mary', '1976-05-05'),
('Jane', '1960-04-01'),
('Tony', '2012-08-16'),
('Marcy', '1955-06-11'),
('Carl', '1930-12-24'),
('Joseph', '1918-11-11');

Here's the query:

with people as
(
    select
        Name,
        DateOfBirth,
        datediff(day, dateofbirth, current_timestamp) / 365.25 as YearsOld
    from
        dbo.Person p
)
select
    p.Name,
    p.DateOfBirth,
    p.YearsOld,
    a.MinimumAge,
    a.Name as AgeCategory
from
    people p
    cross apply
    (
        select top 1
            ac.MinimumAge,
            ac.Name
        from
            dbo.AgeCategory ac
        where
            ac.MinimumAge < p.YearsOld
        order by
            ac.MinimumAge desc
    ) a;

Note that this query works for SQL Server, but I can't guarantee the syntax will be the same for any other product (or that this is a relatively efficient way to solve this problem for any product other than SQL Server).  What we're doing is calculating the age in terms of years based on the date of birth.  Then, we can apply that to the age category table using the CROSS APPLY function.  This will return the one best age category for a particular row.

Note that you could modify the AgeCategory table to have two age values:  MinimumAge and MaximumAge.  That would get rid of the cross apply statement and replace it with an INNER JOIN, but the downside there is that you might end up with overlapping rows:  if you have a minimum age of 10 and maximum age of 20, and then another row with a minimum age of 15 and a maximum age of 25, you'll have two results for each person between the ages of 15 and 20.  The single-entry table gets rid of that problem without having to introduce a trigger or data check other than a unique index on the age column, but might be more costly in terms of performance with large enough data sets.


call a file contains a list of table I'm using Ingres Database and I've this procedure that create a text file with a list of tables. How i can call the file into the where condition?

select request_num,table_name,temp_table
from tables
where temp_table in ('$/tmp/dba_tablelist')

Actually when i run the file is fine, the only problem is that the where condition can't see the list of table, also if the table is just one.

The short answer, I believe, is I don't think you can like that.

I believe you'll have to use the copy() SQL statement to load the file into a temporary table (DECLARE GLOBAL TEMPORARY TABLE session.tables) and use

SELECT temp_table FROM session.tables

in the IN clause.


What's the difference between expensive and cheap SQL Server backup products? I'm looking for SQL Server database backup solution and I have found many third party tools. But their prices vary greatly. For example, SQL Backup from RedGate or Idera costs about $900 - $1000 while SQLBackupAndFTP or SQLBackupMaster costs about $70. What's the reason? Does it mean that RedGate's or Idera's products are more than ten times better than the cheaper ones? Or they more reliable? They all do what I need (e.g. make schedule backups, store them somewhere and notify me by email), but I'm just  completely disorientated with such ten-times price difference. Do I miss anything?

Is there a particular reason you need to pay for a backup solution?

I think 'free' is always a good contender - Ola Hallengren's maintenance scripts are used by a LOT of people.

Simply schedule them using the SQL Agent.


How are my SQL Server constraints being bypassed? We have found a handful of rows in our DB that violate an active constraint.  How is this possible?

The constraint is active, as we can't just manually add a row that bypasses this constraint.  However, when we run CHECKCONSTRAINTS(Files), we find that it has been bypassed on a handful of occasions during our test runs.  The rows in question were all created within a half-second of one another, suggesting some kind of race condition.

Here's the constraint being applied to the table.  The rule is meant to ensure name uniqueness in a given parent folder:

ALTER TABLE Files ADD CONSTRAINT UniqueNameInParentFolder CHECK
    CheckUniqueNameInFolder(ParentFoldersID, Name) = 1;

This constraint calls a function that looks like this:

-- first check for the new name in the Folders table
IF ((SELECT COUNT(*) FROM Folders
     WHERE ParentFoldersID = @FoldersID AND Name = @Name) = 0)
BEGIN
    -- then check for it in the Files table
    IF ((SELECT COUNT(*) FROM Files
         WHERE ParentFoldersID = @FoldersID AND Name = @Name) <= 1)
        RETURN 1
END
RETURN 0

Individual rows are added inside transactions, so I'm having a hard time understanding how duplicate rows are sneaking past this constraint.

Check constraints based on UDFs are rubbish. Concurrency, RBAR, isolation etc as you've found out. Some links:

Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates by Alex Kuznetsov

Using a UDF in a check constraint to check validity of history windows (start - end date windows) by Tony Rogerson

Be careful with constraints calling UDFs by Tibor Karaszi

The safest way for SQL Server in this case would be to use standard constraints such as unique and foreign keys. I can't see why you check the folders table for a constraint on the files table though.
To prevent a file and a folder having the same name in a given parent folder only, use an indexed view. Duplicate files or duplicate folders requires table level uniqueness.
CREATE VIEW CheckUnique
WITH SCHEMABINDING
AS
SELECT fo.ParentFoldersID, fo.Name
FROM
   Folders fo
   JOIN
   File fi ON fo.ParentFoldersID  = fi.ParentFoldersID AND fo.Name = fi.Name
GO
CREATE UNIQUE CLUSTERED INDEX IXCU_CheckUnique ON CheckUnique (ParentFoldersID, Name)
GO

Or a trigger.
But never a UDF in a check constraint


Improve sort performance in PostgreSQL? I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the number of comments for that article. It does that with the following query:

SELECT "articles"."id", "articles"."datestamp", "articles"."title", "articles"."shorttitle", "articles"."description", "articles"."markdown", "articles"."body", "articles"."idxfti", "articles"."published", "articles"."type", COUNT("comments"."id") AS "comment__count"
FROM "articles"
LEFT OUTER JOIN "comments" ON ("articles"."id" = "comments"."article_id")
WHERE ("articles"."type"='NEWS')
GROUP BY "articles"."id", "articles"."datestamp", "articles"."title", "articles"."shorttitle", "articles"."description", "articles"."markdown", "articles"."body", "articles"."idxfti", "articles"."published", "articles"."type"
ORDER BY "articles"."datestamp" DESC
LIMIT 1;

None of these tables is particularly large, and yet that query takes 46ms. The execution plan is:

Limit  (cost=119.54..119.58 rows=1 width=1150) (actual time=46.479..46.481 rows=1 loops=1)
   ->  GroupAggregate  (cost=119.54..138.88 rows=455 width=1150) (actual time=46.475..46.475 rows=1 loops=1)
     ->  Sort  (cost=119.54..120.68 rows=455 width=1150) (actual time=46.426..46.428 rows=2 loops=1)
           Sort Key: articles.datestamp, articles.id, articles.title, articles.shorttitle, articles.description, articles.markdown, articles.body, articles.idxfti, articles.published, articles.type
           Sort Method:  quicksort  Memory: 876kB
           ->  Hash Left Join  (cost=11.34..99.45 rows=455 width=1150) (actual time=0.513..2.527 rows=566 loops=1)
                 Hash Cond: (articles.id = comments.article_id)
                 ->  Seq Scan on articles  (cost=0.00..78.84 rows=455 width=1146) (actual time=0.017..0.881 rows=455 loops=1)
                       Filter: ((type)::text = 'NEWS'::text)
                 ->  Hash  (cost=8.93..8.93 rows=193 width=8) (actual time=0.486..0.486 rows=193 loops=1)
                       ->  Seq Scan on comments  (cost=0.00..8.93 rows=193 width=8) (actual time=0.004..0.252 rows=193 loops=1)
 Total runtime: 46.574 ms

The articles table has the following index defined (amongst others):

idx_articles_datestamp" btree (datestamp DESC) CLUSTER

Before I clustered it, the query execution was more in line with the estimates, around 119ms.

To my untrained eye, it looks like the sort is what's taking the most amount of time here. It also seems to be trying to sort on all the GROUP BY fields, the issue being that it's trying to sort on three relatively large fields, body, markdown and idx_fti.

My question is this: Is this an unreasonable amount of time for this query to take, or is there something obvious I've missed that I can use to speed this query up? All the other queries requested by this blog site take around 1-5ms to execute, so this one stands out as taking a long time. I appreciate there's an OUTER JOIN and a SORT, which don't really help. However, I'm not an expert, so if anyone has any suggestions, that'd be hugely useful.

Why is it slow?
I would advise to use the query @ypercube provided in combination with the mentioned indexes. But why is the query you had so slow in comparison?
You did not provide your table definition, but I assume from the column names and what you wrote that you have several (large) character type (text or varchar) columns in the table articles:
title, shorttitle, description, markdown, body, idx_fti

I further assume that you are running your database with a locale other than C. Sorting large text columns according to a locale is rather expensive. The relevant thing is the collation. Check your (current) setting for LC_COLLATE:
SHOW LC_COLLATE;

With Postgres 9.1 or later you can pick a collation for evaluating your expressions ad hoc. With PostgreSQL 8.4, however, this is set at cluster creation time and can't be changed later.
We recently had a related question on SO where after much deliberation and testing we found sorting according to a locale to be the major slowdown:

Slow query ordering by a column in a joined table

I expect @ypercube's query to solve that problem radically: No GROUP BY for the long text columns eliminates the expensive sort altogether. Problem solved.


Postgres: Reclaiming Space I have a server with 150GB of diskspace. Today I uploaded a dataset of 30GB. I cancelled the import due to internet dying, then noticed there was 29GB of space missing in the database (meaning the CSV was uploaded, but not deleted when I broke the operation). When uploading the data once again, it broke again and I lost another ~25GB. Now there isn't enough free space to upload the data.

This is hosted on AWS RDS, Postgres 10.6.

Is there a way to fix this? I read about VACUUM. But will this delete records? I'm hosting at the moment ~70GB of data and don't want to lose any records. What's the best way to go about this?

PostgreSQL leaves dead data in the table; the space can be reused, but the files won't shrink (significantly).

The official method to reclaim space is VACUUM (FULL), but that rewrites the whole table, which will be unavailable for any access during that time. There are extensions called pg_squeeze and pg_repack which do the same thing with less disruption "behind the scenes".

All these methods have in common that they will require enough free space to create a copy of the table, so you probably won't get around increasing the storage space anyway.

Now for the good news:

If you run a plain VACUUM on the table, which is not disruptive, the wasted space can be reused. So your next attempt to load the data won't increase the size of the table.


Granting permission to SQL database How do you grant a user xp_cmdshell, sp_OACreate, sp_OAmethod, and sp_Destroy on SQL server 2008? What is the prons and cons of granting this permission? Please let me know. Thank you!

All those 4 procedures require the sysadmin fixed server role or CONTROL SERVER permission. What are the pros and cons of granting this level of permission to a user that's not a DBA? This is like opening Pandora's box :-).


When creating primary and foreign keys is the CONSTRAINT keyword optional? When creating a new table, sometimes examples show the primary and foreign keys defined with the CONSTRAINT keyword and other times without the CONSTRAINT keyword. Is the only reason to use the CONSTRAINT keyword to give the constraint a name? Or are there other reasons that you would choose to use or not use this keyword?
Example 1 (without the keyword):
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

Example 2 (with the keyword):
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID)
);

Other than assigning a specified constraint name, do DBMSes see these two examples differently?  I'm thinking about SQL Server, Oracle, MySQL, and PostgreSQL in particular.

For SQL Server, the Microsoft docs spell this out in excellent clarity (bold is my formatting):

  CONSTRAINT
  Is an optional keyword that indicates the start of the definition of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint.

Thus you do not need to specify CONSTRAINT xxx in your CREATE TABLE statement.

Note, however, that if you don't specify the constraint name, SQL Server will generate one for you if you include a constraint definition.  This example shows creating a PRIMARY KEY constraint, without naming it.

CREATE TABLE dbo.ConstraintTest
(
    ConstraintTest_ID int NOT NULL
        PRIMARY KEY CLUSTERED
) ON [PRIMARY];

SELECT o_parent.name
    , o.name
FROM sys.key_constraints kc
    INNER JOIN sys.objects o ON kc.object_id = o.object_id
    INNER JOIN sys.objects o_parent on kc.parent_object_id = o_parent.object_id
WHERE o_parent.name = 'ConstraintTest';

DROP TABLE dbo.ConstraintTest

The results of the SELECT statement above:

+----------------+--------------------------------+
|      name      |              name              |
+----------------+--------------------------------+
| ConstraintTest | PK__Constrai__471ED41490593601 |
+----------------+--------------------------------+

This leads to the generally accepted best-practice of naming all constraints, as in:

CREATE TABLE dbo.ConstraintTest
(
    ConstraintTest_ID int NOT NULL
        CONSTRAINT PK_ConstraintTest
        PRIMARY KEY CLUSTERED
) ON [PRIMARY];

As always, the "It Depends™" moment here is for temporary tables; we almost never specify the name of a constraint applied to a temp table since we want them to be unique, and allowing SQL Server to name them ensures they are in fact unique.  If you do specify the constraint name, then only a single copy of that temp table can exist at any given time; attempting to create more than one copy of the temp table will result in an error:

  Msg 2714, Level 16, State 5, Line 19
  There is already an object named 'PK_ConstraintTest' in the database.
  Msg 1750, Level 16, State 0, Line 19
  Could not create constraint. See previous errors.

You can easily add a UNIQUE constraint to an existing column without a name; as in:

ALTER TABLE dbo.Blah
ADD UNIQUE (SomeColumn);

In the example you've added to your question, the only difference is you are not specifying a constraint name, thereby allowing SQL Server to generate a name for the constraint.  Other than the name of the constraint, there is no difference between the two tables.


How do I query only for committed data while avoid a deadlock I have a system writing batches of queries as transactions (to maintain coherency), these can be sets of 30-100 queries per transaction. These are done on multiple tables

At the same time there are read queries on the same tables using inner selectes - SELECT FROM table1... IN (SELECT from table 2...)

To clarity - there is one writer thread writing the transactions (each transaction writing to all tables) and many reader threads.

I am getting deadlock errors.
1. could the deadlock be related to the IN selection?
2. how can I make the read queries provided only commited data, but not necessarily wait for current transactions to commit (meaning it can return data previously committed)
3. could the deadlock be a result of a transaction that is simply taking too long to complete?

Thanks!

Rather than theorising as to what could potentially be responsible, qualify exactly what is by capturing the SQL Server Profiler Deadlock Graph Event.

How To Track Down Deadlocks in SQL Server using Profiler

Once you know what the problem is that you are trying to solve, you can then proceed to actually solve it.


Table design for linking pictures to a place Could someone give me some advice on designing a table for a system that has to store
x picture for 1 place?

Is it better to have something like this:

place table:

id_place / reference
100           1
200           2
300           3

picture table:
id_picture / reference / picture_url / main_piture
1               1            xxx           false
2               1            xxx           true
3               1            xxx           false
4               2            xxx           false
5               2            xxx           true
6               3            xxx           false
7               3            xxx           true
8               3            xxx           false

Or is it better to have it all in one table?

picture table:
id_picture / id_place / picture_url / main_picture

I don't see there is much more difference in the design and given the choice I would choose to have a single table to store these picture.

Single table removes the need to have JOIN in sql query and will improve the performance.

In a single table structure you can create Indexes depending on your sql query used to read data that will improve the performance too.


Named Parameters in MySQL Workbench Queries? In OpenOffice / LibreOffice I'm used to added :prefixed-parameter-names to my queries.

For example:

SELECT name FROM table1 WHERE id = :id

Is there a similar functionality in MySQL Workbench?

SET @given_id = 1;
SELECT name FROM table1 WHERE id = @given_id ;


what columns to include in the index to improve the performance of the following update? I have the following update:

  UPDATE dbo.fees
  SET amount = @amount,
   currencyCode = @currencyCode,
   feeType = @feeType,
   countryCode = @countryCode,
   gatewayCode = @gatewayCode,
   programAP = @programAP,
   programED = @programED,
   programEX = @programEX,
   mandatory = @mandatory
  WHERE feeID = @feeID  and endDate = @endDate
  and programAP = @programAP and programED = @programED and programEX = @programEX
  and (countryCode = @countryCode or countryCode IS NULL)
   and (gatewayCode = @gatewayCode or gatewayCode IS NULL)

this is the table definition:

CREATE TABLE [dbo].[fees] (
[ID]            INT              IDENTITY(1,1)   NOT NULL,
[feeID]         INT                              NOT NULL,
[startDate]     DATETIME                         NOT NULL,
[endDate]       DATETIME                             NULL,
[amount]        DECIMAL(10,2)                        NULL,
[currencyCode]  CHAR(3)                              NULL,
[feeType]       CHAR(1)                              NULL,
[countryCode]   CHAR(2)                              NULL,
[gatewayCode]   CHAR(3)                              NULL,
[programAP]     BIT                                  NULL,
[programEX]     BIT                                  NULL,
[programED]     BIT                                  NULL,
[mandatory]     BIT                                  NULL  CONSTRAINT [DF_fees_mandatory] DEFAULT ((0)),
CONSTRAINT   [PK_fees]  PRIMARY KEY CLUSTERED    ([ID] asc) WITH FILLFACTOR = 90)

this is the index I intend to create in order to improve the performance of the update.

create nonclustered index i_feed2 on dbo.fees  (feeID ,programAP,programED,programEX,endDate) include (countryCode,gatewayCode )
with(data_compression=page,online=on) on [NONCLUSTERED_INDEXES]

A question came to my mind though:
what sort of data analysis should I do, to identify which of these columns would be better in the index or in the include part? Plus, the order of the columns in the index, is there any starting point as to find out, for this query in particular, or other queries that might hit this table, the best order of the columns?

There are no other indexes on this table.

I cannot change neither the table definition nor the update statement, but I can add indexes.

the solution

On this occasion though, even after adding the index as planned, while monitoring the execution and using the very helpful procedure sp_blitzcache
that I contacted the third party company requesting them to alter the procedure accordingly (adding a with recompile and sort out the updates as described in the comments and the accepted answer)

sp_blitzcache in action:

I fail to see how included columns can be anything but overhead for an UPDATE. If you think about it... Included columns are there to eliminate the lookup for each row. But you still have to get to the row (do the lookup) in order to modify it for an update. And every time a column is in an index (included or key) this value have to be updated (in addition to the actual data) when a modification occurs.


Parsing and binding variables for SELECT .. WHERE column IN ( .. ) queries I'm looking to make sure that all queries in a php web application have proper use of bind variables to minimize parsing of the queries.

I'm wondering how Oracle parses queries that compares a column to a list of values. Will Oracle consider these statements to be the same, or must the list be inside bound variables?

select char from alphabet where char not in ('a', 'b');

select char from alphabet where char not in ('c', 'd');

If the contents of the list must be in a bind variable, can it be done with a single variable, or must one put each item on the list in a separate variable?

select char from alphabet where char not in (:list);

select char from alphabet where char not in (:c1, :c2);

If the latter is true, will queries with a different number of items in the list still be considered to have the same structure?

select char from alphabet where char not in (:c1, :c2);

select char from alphabet where char not in (:c1, :c2, :c3);

You must bind the elements of an IN list, otherwise Oracle will consider these separate statements. Any differences in the text of the SQL will cause a new statement to be parsed (excepting some cases when cursor_sharing = force). Therefore your examples with :a, :b and :a, :b, :c will be considered different SQL statements.

Each element must be bound to a separate variable. If you bind 'fred,nick' to one variable, Oracle will look for the string "fred,nick", instead of the separate items fred and nick.

If you have variable in-lists, but don't want to be parsing a new statement for each additional item, you'll need to pass the elements as a comma separated string into one bind variable, then parse the elements out yourself.

Tom Kyte discusses a couple of ways to do this here. Basically you can do this by writing a function which returns a nested-table which you can wrap a table function around to make it a row source. Alternatively it can be done in pure SQL by using the connect by level <= :num_of_rows trick to generate the number of elements you need and parsing the bind variable that way.


Text-based interface for PostgreSQL? I know that psql is an option, but I'm looking for an easier text-based interface for PostgreSQL. Something that's similar to what 'tig' is to 'git', or 'phpmyadmin' is to 'mysql' (but using the command line). does any one know something like this?

I believe what you're looking for is psql, which is Postgres' command line tool.


How to insert into 2 tables which are dependant on each other I have this 2 tables (MySQL) that are dependent on each other and I don't know what to do when creating a new company as it needs a document type...and a document type needs the id of the company it belongs. I was thinking about asking (in a form) for the document type's info (it has only 2 fields) and then inserting it, but it will trigger a foreign key constraint fail as neither keys exists, right?

CREATE TABLE Company
(
    id long not null,
    document_type_id long not null
)

CREATE TABLE Document_Type
(
    id long not null,
    company_id long not null
)

Which approach should I take?

with correction after comment from author:

example of procedure

CREATE DEFINER=`root`@`%` PROCEDURE `udp_insert_company`(`v_id` double, `v_document_type_id` double)
BEGIN
declare v_error_text varchar(200);
set v_error_text = '';

IF NOT EXISTS (SELECT id FROM document_type where id = v_document_type_id) THEN
        SELECT CONCAT('ERROR - id ', CAST(v_id AS CHAR), ' not found') into v_error_text;
    SELECT v_error_text;
ELSE
    INSERT INTO company VALUES (v_id, v_document_type_id);
END IF;

END

it just example, You can use any other method for return error code

@call `test_db`.`udp_insert_company`(4, 3);

ERROR - id 4 not found


SQL query to check if secondary database is removed from availability group I have an AG with one primary replica and one secondary replica.
I have created a script that queries transaction log backups from these database replicas and restores them to a target database.

Recently one user removed one secondary database from the AG and started using it as a standalone database as explained here https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/remove-a-secondary-database-from-an-availability-group-sql-server?view=sql-server-2017. The user also started a backup chain of this database.

The script now fails to restore as the recovery fork guid has changed on the secondary database.

Is there a way to detect if a database has left the AG so that in the script while I query the backupsets, I can skip the database if it is no longer part of the AG?

Thanks!

You could use exists with the sys.dm_hadr_database_replica_states DMV to see if the DB is in a AG.

  Returns a row for each database that is participating in an Always On
  availability group for which the local instance of SQL Server is
  hosting an availability replica. This dynamic management view exposes
  state information on both the primary and secondary replicas. On a
  secondary replica, this view returns a row for every secondary
  database on the server instance. On the primary replica, this view
  returns a row for each primary database and an additional row for the
  corresponding secondary database

Here is the query:

if exists(
    select
        DBName = db_name(s.database_id)
        ,s.is_local
        ,s.synchronization_state_desc
        ,s.synchronization_health_desc
    from sys.dm_hadr_database_replica_states s
    where db_name(s.database_id) = 'your_database'
    )
begin
--do something useful
end
else
begin
--exit, probably without error
end

This would need to query against the secondary if you aren't on 2014+. If you are, then you can add s.is_primary_replica = 0 to the query. Or you can use this method or a similar approach.


Is "AlwaysOn" not always "Always On?" We created a Windows Failover Cluster, then added two instances of SQL Server as nodes of a SQL Server failover cluster.

We set the servers to use "AlwaysOn Availability Groups" in SQL Configuration Manager.

To test a failover, I loaded up and ran a long query, then brought the active node down by using Failover Cluster Manager to stop the cluster service on the active node.

The query broke with no connection, and the server showed as unavailable for about 20 seconds before the node was drained and the new node took over.

Did I do this wrong? How should I have configured this so that there was little-to-no connectivity loss?

Is AlwaysOn not always on?

You have a bunch of different questions in here.

Q: What is the "Always On" thing?

Microsoft uses that brand name (which was written without a space before 2016) to describe two different features:

Failover Clustered Instances (FCIs) - what your grandpa used to call an active/passive cluster
Availability Groups (AGs) - like database mirroring, but works with groups of databases in some cases (but not the system databases)

Use those terms to describe which specific Always On feature you're using.

Q: In a failover, will it be always on?

Neither FCIs nor AGs are really always on. During a failover, your running transactions will fail, and connection retries can fail for 5-60 seconds (or more). It's up to you to build in graceful retry logic in your applications, or build in degraded capability tools like Stack Overflow does.

Q: How do I configure Always On?

It varies dramatically based on:

Which AO feature you're using (FCIs or AGs)
The number of nodes in the cluster
How you want to handle quorum (voting)
Whether you're using automatic failover via a listener or virtual computer name

These are big decisions that involve a lot of architecture work. For more detailed specifics, include the above details, and we'll be able to tell you more about how to configure it.

Q: Isn't it just a matter of checking the box for Always On?

Nope.


how to download oracle database to a remote server I am trying to install Oracle 11g on my client's server. In order to download it to the server, I have followed this :

Logged in to www.oracle.com with my client's username/pass from my desktop.
Accepted the Licence agreement (OTN).
clicked on the download link
When my browser started the download, I copied the download link, and
wget -c "the_download_link"

However, I get "403 Forbidden" error. So how do I download the Oracle db, directly to the server (rather than downloading to my Desktop first and then uploading it to the server) ?

Using WGET to download My Oracle Support Patches (Doc ID 980924.1).

wget --http-user=<username> --http-password=<password> --no-check-certificate --output-document=filename "paste the copied download address here in quotes"

Also there are a bit different instructions in https://blogs.oracle.com/supportportal/entry/use_of_wget_instead_of_downloa but I believe things a bit changed now when flash MOS page is retired.


Trouble Connecting to SSIS in SQL Server 2017 (14.0.1) Setting up a new server with SQL Server 2017 (14.0.1000) and I just installed SSIS (14.0). As of right now I am unable to connect to SSIS from SSMS and receiving the following error message:

Connecting to the Integration Services service on the computer "localhost" failed with the following error: "Class not registered".

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

For help, click: http://go.microsoft.com/fwlink/?LinkId=506689

I've confirmed that the versions are compatible, tried doing an uninstall/reinstall of SSIS, all the while confirming SSIS is running in SQL configuration manager, but still the problem persists.

Update: I've also tried following the steps here
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/hh213130(v=sql.110)?redirectedfrom=MSDN
to no avail.

If anyone else has a similar setup with SQL Server 2017 (14.0.1000) and SSIS (14.0) as of the creation of this question/answer SSMS 18.4 will not allow you to connect to SSIS. The only fix I have found is following Jacob's suggestion and downloading SSMS 17.9 which resolved the issue.


MySQL Design - What is best primary key for our needs? The site I am designing for is in the final stages of development. It is a forum where, obviously, users ask and answer questions.

The owners want id values to be of no value to competitors. These would be the id fields such as question_id, user_id, answer_id, and a couple of others. In other words, they dont want the value of ids in the site to be able to be used by a competitor to determine how many users, or questions, or answers exist. This would also include a private message section since you could sign up, message me, see message_id=3189 in the url or in the html in case of post, and start to piece together how active the site is.

Ok. So, the expectation is for ids to get into the millions (whether it will or not, we are planning for 5 million for each id)

Currently, we are using auto-increment for each table (type is INT unsigned)(ex.questions table has question_id as auto, answers table has answer_id as autoincrement, etc.) and of course there are many joins, and all works well. Even when I insert millions of test records. But, to be honest, I am skeptical of this testing--just adding records--since I am unsure of if there is an impact with multiple users.

We are considering three options to convert the ids so that we make them not usefull to the outside world. And I would love some thoughts from you guys.

1) Add a 4char hash column next to each id column, and index it. Show this hash in the urls. Then whenever a something needs to be processed, lookup the hashes, gete the autoid, and use that in joins and inserts. The con here is the added code and added db calls--sometimes will need two or three hashes to be resolved. Not sure of performance with the lookups.

2) Add the 4char hash column IN PLACE OF the autoid column. And in joining tables make, obviously, the same change. So, the 4char is the primary key. The pro here is that there is very little change to code, just change my db column types. But not sure of performance with the joins or lookups.

3) Generate random numbers for the id field instead of autoincrementing. This gives billions of random possibiilities which would be of no value to others. I would probably opt for only using numbers above 1 billion so all ids are the same length. The pro here is that it is the easiest, but not sure of lookups and joins with fragmented ids.

Well, what do you guys think? Do any of these make more sense than the others? Is this a case of "I'd never notice a difference anyway, even with millions of records?"

"Random" keys (UUIDs, MD5s, etc) can cause a performance problem, so let's avoid them as indexes (including PKs) of tables.

What is exposed?  Stuff in the url parameters?  Nothing else?

Well, the exposed "numbers" could be encrypted copies of the AUTO_INCREMENT values that work nicely otherwise.  Pass the encrypted stuff wherever a competitor might see it, then decrypt it when you need the simple id.

That is, I want to solve the problem at a higher level than in the bowels of the schema.

One approach is

AES_ENCRYPT(CONCAT(id, '-my-salt'), 'my-secret-string')

together with a similar AES_DECRYPT.  See MySQL, PHP, and other documentation.  The "salting" is to avoid a certain level of easy decoding.

You should probably do the en/decryption in the client and assume the connection between the client and MySQL is secure.  (After all, 'my-secret-string' is quite visible to a hacker on the client or server or network between them.)

AES strings are, I think, 8-bit values.  It would probably be good to use BASE64-encoding to make them nearly url-friendly.  You probably still need to "urlencode" to feed to urls.

Note also,...

MD5 is not reversable.
Random numbers are too likely to duplicate.
Truncate AES strings can't be reversed; I tried to minimize the length of the full AES string, but they will still be somewhat fatter than the minimum.


How do I make binlog-format=ROW the start up default in the /etc/my.cnf file? I can run the query SET GLOBAL binlog_format = 'ROW' and it works for that session.

How do I make this setting stick after stopping and restarting MySQL?

NOTE: I am on version  5.5.19 of MySQL (with Moodle) and I just upgraded from MyISAM to InnoDB and therefore need this new setting.

I have tried adding these two commands (separately) to my.cnf: binlog-format=ROW and binlog_format=ROW and neither work. I have stopped and restarted MySQL every time I changed the my.cnf.

The error I get without this being set is the nefarious error message:

Debug info: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

I think your problem may stem from where you wrote the directive

Make sure you place the option under the [mysqld] header in /etc/my.cnf

[mysqld]
binlog_format = ROW

If you place it under any other header, the mysqld process will not see it.

Give it a Try !!!


If a query contains two identical subqueries, will they both run? Say I have a SELECT query that contains two identical subqueries, like the below:

SELECT
  (SELECT SUM(Price)
    FROM Product p
    WHERE p.GroupID = g.GroupID) AS 'TotalCost',
  (SELECT SUM(Price)
    FROM Product p
    WHERE p.GroupID = g.GroupID) * 0.1 AS 'GST'
FROM Group g

Will the SUM subquery be performed twice, or will SQL use the first result for the second field?

Identical subqueries are normally only performed once. However, the way you write a query isn't neccessarily the actual execution order, so there are no guarantees.
To make sure, view your estimated execution plan with one or both of the subqueries, and compare them.

In your example, you could place the subquery in a JOIN instead, which would make it both reusable and more easy to read.

SELECT g.*, p.Amt AS 'TotalCost', p.Amt * 0.1 AS 'GST'
FROM Group g
LEFT JOIN (SELECT GroupID, SUM(Price) AS Amt
    FROM Product
    GROUP BY GroupID) AS p ON p.GroupID = g.GroupID


MySQL - unique constraints design I am using MySQL and looking to solve a problem with relational data. I am not sure it is possible but wanted to ask the community.

Please consider the diagram:

I would like to add a unique constraint such that only one User can have only one Role per Event (Which is stored in the UserEventRoles table). Not sure how this can be solved? Perhaps with different design? Also the goal is not have a scenario where keys can be "out of sync", such as users role would point to an event where that role does not exist.

EventRoles table describes which Roles are valid at each Event.

I am sure this problem has come up before with other database designs and would like know what the best approach may be.

Also here is a link to sql fiddle to play around schema and some sample data.

sqlFiddle

This is fairly simple to accomplish by making some changes to your primary keys.

http://sqlfiddle.com/#!2/8dcdd/1

I changed the primary key of the UserEventRoles table to be a composite key of user_id and event_id. This forces each User to only ever have at most one Role per Event.

I also modified the EventRoles table to use composite primary key. The surrogate key provided no value and made the resulting query more complex by forcing you to join into the EventRoles table if you wanted to preserve the foreign key constraint between that table and UserEventRoles.

With this change, you make sure you never define a role twice at an event, and you can directly join the columns in the UserEventRoles table to the descriptive tables that actually contain data you want.


SSIS data transfers between two remote servers Let's say I have three servers, ServerA, ServerB, and ServerC.  ServerA has the production database, ServerB has the staging database, and ServerC has the data warehouse.  I've created an SSIS package to transfer data from ServerA to the staging database on ServerB (Stage 1), then from the staging database to the data warehouse on ServerC (Stage 2).  Would it make any sense to have separate SSIS packages for each stage?  Is there network traffic between ServerA and the other two servers during Stage 2 because that's where the job is executed from?

Yes there will be traffic to Server A during stage 2. This is because the data that has been staged will need to come back to the place that is doing the work (Server A) so that the package can send the data to the target (Server C).

I think you may be doing this the wrong way around to be honest. If Server A has the production database on it then you want to put as little extra load on it as possible. Because of this I would suggest placing your SSIS package on Server B which then only has to pull data from Server A - meaning that there will be no additional processing load on Server A other than satisfying the query that is asked of it.

Then, once the staging has been complete stage 2 can begin which will only consume resources on Server B and Server C. This is a much better approach - I don't see any need to have multiple packages unless you want to split the logic of each stage into their own modules so that they can be maintained separate from one another.

The other benefit to this approach is that you can make alterations to the package and the staging database without having to change anything on your production server - this decoupling allows better separation of duties and will make future maintenance easier for you.

I hope this helps you.


At which point does SQL Server validate data for insertion I have a query to insert data from one table into another. One of the source columns is larger than the destination table's, although the actual data selected in the query isn't. So e.g. the source table is varchar(25), the destination table is varchar(10), and the query selects records that do not exceed 10 characters for that specific column.

My initial query failed with a truncation error, but when I rewrote it selecting the same records but changing the where clause i.e. to use WHERE IN and filtering on a cte it ran successfully.

i.e instead of doing

select * from tbl where col1 = 1

I did:

with cte as (select 1) select * from tbl where col1 in (select 1 from cte)

I understand that this is a bug in SQL Server, still I am wondering at which point does SQL Server validate the data for insertion. At which point is the error thrown?

In all honesty, it depends on exactly how the query is processed, and it is certainly possible that changing the query (even though it yields the same end results) can make expressions (like attempted conversions required to fit a big string into a small column) happen before filters have eliminated those rows (and a different plan shape can occur simply because of the syntax, because stats have changed, because indexes have been added, etc. etc.).

Erland Sommarskog has always called those "illogical errors" - this UserVoice issue was migrated from a Connect item that was originally posted at least a decade ago.

In your specific case, there are multiple failsafes you can use here, assuming small_col is a varchar(10) and large_col is something that can't have more than 10 characters when col1 = 1:

INSERT dbo.target(small_col)
  SELECT CASE col1
    WHEN 1 THEN CONVERT(varchar(10), large_col) END
  FROM dbo.source
  WHERE col1 = 1;

In fact an additional failsafe, depending on the data types involved, would be to use TRY_CONVERT() instead of CONVERT(), in SQL Server 2012 and up. I don't think that applies in this specific case, since virtually any string type can be converted to varchar(10), but might be useful in other cases.

There was a similar but not identical question in the past, where I added other examples and links to external references:

CTE Error (nvarchar to numeric)

I also wrote about this in #1 here:

T-SQL Tuesday #56 : SQL Server Assumptions

Anyway, to answer your explicit question, the error is only thrown when the query is processed in such a way that an expression is attempted on the violating rows before they are filtered out. This is why you tend to hear a lot of us old school folks lecturing about always using the right data type, not overloading columns, etc.


Sleeping SPID blocking other transactions I'm really having trouble tracking down some blocking we are experiencing.

The root blocking SPID's status is 'sleeping', the cmd is 'AWAITING COMMAND', and the sqltext is SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

When I view the Top Transactions by Blocked Transactions Count report, the Blocking SQL Statement is '--'.

I've performed a trace on the SQL and when the blocking happens tracing the root blocking SPID but it hasn't really led me anywhere.  The last trace statement is the same as the sqltext above SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

I've checked all the related stored procedures I can find to make sure they have TRY/CATCH BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN statements (we use stored procedures for everything so there are no standalone statements being ran).  This issue just started happening over the last 24 hours and no one is claiming to have made any changes to the system.

Solution: one of our seldomly used stored procedures had an error with an insert (number of columns didn't match), but we are still confused on what exactly was happening.

When looking at all the trace information, the EXEC statement for this stored procedure was listed at times, but NEVER just before the BLOCK happened on the blockking SPID. It seemed that when it starting blocking, the trace didn't record the execution of it (or any of the statements within it either). However there are other times were the trace did record it's execution and no blocking occurred.

The stored procedure error report came from a user, and I was able to find multiple EXEC statements in traces and run them in SSMS. No time when I ran them did we have any blocking occur or did they hang. They ran as expected (the catch block fired and rolled back the transaction after the error).  After resolving the fixing the stored procedure, we have not seen the issue again.

From comments, I'm guessing you had a client side Command timeout that has aborted the SQL query. This does not rollback the transaction because the connection stays open on SQL Server due to connection pooling.

So, you need to use SET XACT_ABORT ON or add some client rollback code

See SQL Server Transaction Timeout for all the gory details