Alternative for Cross Join with a Table having Single Row (Multiple Columns)

Question

I have a table for Configuration - tblConfig as Shown below

-------------------------------------------
| SiteCode  |  CompanyCode | CompanyGroup |
-------------------------------------------
| AISH78    |  SPWI85      |SFTIT         |
-------------------------------------------

And another table tblData With More than 10K rows.

I want to write a query to return tblData Table Rows along with Columns of tblConfig tables.

Eg:

---------------------------------------------------------------------------
| SiteCode  |  CompanyCode | CompanyGroup | tblDataCol1 | tblDataCol2|.....etc
---------------------------------------------------------------------------
| AISH78    |  SPWI85      |SFTIT         |    1        |  XY       | ....etc
----------------------------------------------------------------------------
| AISH78    |  SPWI85      |SFTIT         |    2        |  MN       | ....etc
----------------------------------------------------------------------------
| AISH78    |  SPWI85      |SFTIT         |    3        |  PQ       | ....etc
----------------------------------------------------------------------------
...
...
...
..etc

I Know i can write a cross Join Query as

 Select * from tblConfig
 Cross Join tblData

But above Query is affecting performance as tblData is having more than 10K rows. (I have few other queries after this cross Join).

Is there any alternative for cross join for the above scenario.? Please help.

Answer 1

Hm... you don't need to use a cross join. You can use a regular inner join as well. However, might be that the Optimizer changes it into a cross join after all - you will have to try if performance suits your requirements:

DECLARE @t1 TABLE(
  SiteCode NVARCHAR(10),
  CompanyCode NVARCHAR(10),
  CompanyGroup NVARCHAR(10)
)

INSERT INTO @t1 VALUES ('AISH78','SPWI85', 'SFTIT')


DECLARE @t2 TABLE(
  c1 NVARCHAR(10),
  c2 NVARCHAR(10),
  c3 NVARCHAR(10)
)

INSERT INTO @t2 VALUES ('AAA','BBB', 'CCC'), ('DDD','EEE', 'FFF'), ('GGG','HHH', 'III')

SELECT t1.*, t2.*
  FROM @t2 t2
  JOIN @t1 t1 ON t1.SiteCode = t1.SiteCode