Hi there. I am going to have to say "no" to the possibility of performance issues in this scenario. Database-level collation is used for:

1. the contents of string literals and variables / parameters. Neither of those come into play here.
2. the default collation used when creating / altering columns in a "CREATE TABLE" / "ALTER TABLE" statement when not using the "COLLATE" clause. That is also not happening here. Tables created from a "SELECT ... INTO..." use the collation of the field in the result set that is being used to create the table.
3. database-level meta-data (e.g. object names, user names, etc). This is also a non-issue for this scenario

-- DB collation is Latin1_General_100_BIN2 ; tempdb collation is SQL_Latin1_General_CP1_CI_AS
CREATE TABLE dbo.ColumnNames(col1 INT, Col1 INT, COL1 INT);
DROP TABLE dbo.ColumnNames;

DECLARE @Table TABLE (col1 INT, Col1 INT, COL1 INT);
/*
Msg 2705, Level 16, State 3, Line XXXXX
Column names in each table must be unique.
    Column name 'Col1' in table '@Table' is specified more than once.
*/

CREATE TABLE #ColumnNames(col1 INT, Col1 INT, COL1 INT);
/*
Msg 2705, Level 16, State 3, Line XXXXX
Column names in each table must be unique.
    Column name 'Col1' in table '#ColumnNames' is specified more than once.
*/
GO

and:

-- DB collation is Latin1_General_100_BIN2 ; tempdb collation is SQL_Latin1_General_CP1_CI_AS
DECLARE @ComputedColumnCollation TABLE
(
[ID] INT NOT NULL,
[SomeChar] AS (NCHAR([ID])),
[String] VARCHAR(10)
);

SELECT *
INTO dbo._comp
FROM @ComputedColumnCollation;

SELECT col.[name], col.[user_type_id], col.[collation_name], col.[is_nullable]
FROM sys.columns col
WHERE col.[object_id] = OBJECT_ID(N'dbo._comp')
ORDER BY col.[column_id];
/*
name      user_type_id   collation_name                 is_nullable
ID        56             NULL                           0
SomeChar  239            SQL_Latin1_General_CP1_CI_AS   1
String    167            Latin1_General_100_BIN2        1
*/

DROP TABLE dbo._comp;

Both of those cases are functional issues, not performance issues.

What this comes down to is: whatever collation is being used by any string column (minus XML) is the collation that must be used in any work table, version store, trigger / OUTPUT clause table (i.e. INSERTED and DELETED), etc. And this has nothing to do with the collation of either DB or of the instance since every field of a result set can use a different collation. If the collation used for fields in these system-created work tables was different than the collation of the field the column is intended to capture, then you could end up with data loss for VARCHAR data if the code page was different. At the very least you would get the beloved collation mismatch error.

For more details on what areas are impacted by which level of collation, please see:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

For info on working with collations in general, please visit: Collations Info

Take care,
Solomon...