SELECT Student_ID
FROM dbo.table_name
GROUP BY Student_ID
HAVING MAX(CASE WHEN Student_Status = '2' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN Student_Status <> '2' THEN...

If you're that worried about space, you could just a minutes displacement from Jan 01, 2023.  So, midnight on that date would be 0.  2PM would be 14*60, etc..  Not...

SELECT Student_ID
FROM dbo.table_name
GROUP BY Student_ID
HAVING MAX(CASE WHEN Student_Status = '2' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN Student_Status <> '2' THEN 1 ELSE...

Temp tables should work, but for best performance be sure to create a unique clustered index on the temp tables to aid in the subsequent joins.  You'll want to create...

From the nature of some of the code, it looks like all parameters are being passed as NVARCHAR(4000).

It doesn't matter how well code may be written, that generally means...

Just to be safe, I would CAST the text to varchar(max).  And, also, add code in case '</Node1>' is missing:

select
table1.Id, 
substring(
ca1.source_data_varchar, 
charindex('<Node1>',ca1.source_data_varchar) + 7, 
nullif(charindex('</Node1>',ca1.source_data_varchar), 0) -...

I put a space after the embedded forename (because of the wording of the OP: "who put in their middle name or surname with forenames separated by a space".)

Otherwise, I...

You might avoid some false matches by using:

like '%' + p.forenames + ' %'

 

But you should add the NULLability setting to the column.  The default is somewhat obscure so you should always specify NULL or NOT NULL when adding a column.

I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016).  When we adjust the code to split the table creation...

/* dummy post to allow me to see my previous post */

I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016).  When we adjust the code to split the table creation and the...

I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016).  When we adjust the code to split the table creation and the table load...

I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016).  When we adjust the code to split the table creation and the table load...

Derrick Smith (9/15/2010)

---

Jeff Moden (9/14/2010)

---

Derrick Smith (9/14/2010)

---

It's the combination of that and creating it in a transaction thats causing the issue here. SQL needs to keep the lock on...