The full, unlimited Cartesian join will be huge overhead.  At least turn the match of the tables into a standard join:

select a.Ref_Tran_OU'Tran_OU', a.Ref_Tran_Type'Tran_Type', a.Ref_Tran_No'Tran_No', 'N', b.Remarks
from SampleTable...

D'OH, quite right, a copy/paste mishap I think.  I have corrected the original code.

OK, adjust it as you need to.  You didn't provide enough details to know which...

I completely agree with Celko.

At my place of work we had to import some employee data that came from 2 completely separate systems, one for temporary and one for...

Edit: Corrected typo, as pointed out by Mr. Brian Gale.

UPDATE D
SET MarketCompleted = 0, MarketAssigned = 0
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID,...

• This reply was modified 5 years, 8 months ago by ScottPletcher.

UPDATE dbo.table_name
SET data_column = REPLACE(data_column, '*', ISNULL(REPLICATE('0', 12 - (LEN(data_column) - 1)), ''))
WHERE (LEN(data_column_name) < 12 OR LEN(data_column_name) = 13) AND
    (data_column LIKE '%*%')

REPLACE(data_column, '*', ISNULL(REPLICATE('0', 12 - (LEN(data_column) - 1)), ''))

Some examples:

SELECT data_column,
    REPLACE(data_column, '*', ISNULL(REPLICATE('0', 12 - (LEN(data_column) - 1)), '')) AS new_data
FROM ( VALUES('ABC*1234'),('BC*234'),('CDEFG*1'),('D*23456789012')...

Assuming the PKs are the clustering keys [*], for best performance you'd need to change the order of the keys in the Data table.

If you always (with perhaps only extremely...

Create a proc in the master db, with a name beginning with sp_, and you can directly run it against any user db. [If you're in Azure, nevermind, this won't...

We need the full DDL for all tables involved, including indexes, but for now:

1 How is the dmhdb.dbo.Data clustered?

2 The syntax on your update is not best practice and you...

1 The nonclus indexes should be unique, not non-unique.  Worst case, add an identity to the table and use that to make unique non-clus keys.

2 Are the nonclus indexes page...

Create two base source tables: one with only current_record = 'Y' rows, and one with only current_record <> 'Y' rows.  Create a SQL constraint on the each table that enforces...

1.2M rows * 10KB bytes per row is a huge amount of data, ~12GB.  I can imagine it could take 6 mins to transfer that much data.

It would be very...

As I noted above, views sys.foreign_keys and sys.foreign_key_columns should give you FK details.  I can't imagine how your SQL doesn't have those views, since they've been around a long time.

Agreed.

Views sys.foreign_keys and sys.foreign_key_columns should give you FK details.

Also look at sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.

For just any reference to another object, not for a defined relationship, try sys.sql_expression_dependencies.  For example, if...