PiMané (12/18/2014)

---

There is no problem having an uniqueidentifier as the pk since it ain't the cidx.

You can have the pk on the uniqueid and have a fillfactor of 70 or...

You can do the full db backup ahead of time. You can do a differential just before you do the index changes.

Script out all existing non-clustered indexes ahead of...

Here's a possible re-write of the first temp table INSERT:

insert into #temp_violation

select

tv.OrderID

,tv.StartDate

,tv.EndDate

,ca1.HourID

from EMSTBLSN.dbo.TimeViolation tv

cross apply (

select distinct fh.HourID

from G4SFEP1.dbo.Service fs

...

SELECT name

FROM table_name

UNION ALL

SELECT DISTINCT LEFT(name, 1) AS letter

FROM table_name

ORDER BY name

First thing is to get the best clustering:

Cluster the violation table on ( OrderID, StartDate )

Cluster the sanction table on ( OrderID, IdentifiedDate )

Might as well handle any db and any schema, but defaulting to your main one.

CREATE PROC CLEAR_MY_TABLE

@TableStat varchar(100)

AS

SET NOCOUNT ON;

DECLARE @TableStat varchar(100)

SET @TableStat = ISNULL(PARSENAME(@TableStat, 3),...

Truncating tables is a great idea when applicable. It's vastly less logging overhead than deleting all the rows instead. Dropping the table and recreating is also more overhead...

This query will find logins with a default db that is not online, and show any job(s) that login owns.

select sp.*, j.*

from sys.server_principals sp

left outer join msdb.dbo.sysjobs j on

...

It's only 20 chars, I'd do it as simply as possible rather than over-complicate it:

SELECT MP.RefNum, MP.Phone,

CASE WHEN SUBSTRING(MP.Phone, 01, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone,...

If you just want to do INSERTs to the other table, and not UPDATEs:

CREATE TRIGGER trigger_name

ON dbo.CARTOES

AFTER INSERT, UPDATE

AS

SET NOCOUNT ON;

INSERT INTO dbo.another_table --(column_name1, column_name2, ...)

SELECT

[COD_ID],

[CODCTB],

[NIFCTB],

[NOMECTB],

[DT_EMISSAO],

[DT_VALIDADE],

[DT_ANULACAO],

[DESCR_REP_FISCAL],

[NUMCARTAO],

[STATUS],

[UTILIZADOR]

FROM inserted

GO

PiMané (12/16/2014)

---

that's what i'm currently doing...

select the...

Yes, you need separate physical drives.

Smaller, cheaper drives very often work better than a single, more expensive drive. For I/O, the number of spindles is more critical than the...

Yes, UNION in "A", "B", "C", ..., "Z" to the source table and then sort it, something like this:

SELECT name

FROM table_name

UNION ALL

SELECT v.letter

FROM (VALUES('A'),('B'),('C')) AS v(letter)

ORDER BY name

Yes, using view "sys.foreign_keys", and, if needed, "sys.foreign_key_columns" as well.

FYI:

WHERE sp.data_compression > 0

is more sargable then "<> 0"; avoid <> unless you have to use it.