halifaxdal (7/31/2015)

---

Can you explain '%[a-z]:%\%.%'? My first time to use it, thanks. Basically the sample data is sample only, the real data could be randomly and I have no idea...

SELECT pm_emp.ProgramID, pm_emp.EmployeeID, e.EmployeeName

FROM (

SELECT

pm.ProgramID,

eq.EmployeeID

FROM tblProgramModules pm

LEFT OUTER JOIN...

Technically "usp_" is illogical, since "sp_" stands for "special", but "usp_" wouldn't be meant as "user special".

Maybe this will give you some ideas/alternatives:

SELECT

breach_content,

RIGHT(breach_content, CASE WHEN SUBSTRING(breach_content, LEN(breach_content) - 3, 1) = '.' THEN 3 ELSE 4 END)...

You'll have to specify COLLATE on every column to which it applies.

For example:

CREATE TABLE #test ( col1 varchar(30) NOT NULL COLLATE Latin1_General_CI_AS, col2 int NOT NULL, col3 datetime NOT NULL,...

Get rid of the parentheses around the main queries -- those "tell" SQL that a subquery is coming, but these not subqueries.

/*(*/

Select distinct b.lev5

from bf_data a

inner join bf_orgn_cnsl_tbl b

on a.bf_orgn_cd...

You should be able to purge/archive older data without any blocking, unless someone is constantly reading old data too.

What is the table clustered on?

I thought the time was needed on the rows where it does appear.

You can directly CAST format 'YYYYMMDD hh:mm[:ss[.sss]]' to a datetime or date.

Since you're updating the column anyway, also remove the wasteful dashes as well and just store YYYYMMDD.

UPDATE table_name

SET column_name = REPLACE(REPLACE(column_name, '00:00:00', ''), '-', '')

A fragmented table that must be scanned will consume more total buffer pages. Several such tables could affect overall SQL performance even if queries on that specific table don't...

What about "/d -0"?

rootfixxxer (7/29/2015)

---

No doubt of that.

But like i said before, even if change the index and the logical reads decrease about 50%, the time is the same.

And if i use...

rootfixxxer (7/29/2015)

---

Just for testing i tried your suggestion, but the time it's almost the same, so in this case, at least with this amount of rows the only difference that...

Not a problem. But, if you prefer, add the id...

It's a virtual certainty that the clustering key should be pf, not a meaningless id. That would also give better performance across the board on that table. ...