ALTER TABLE dbo.person_test2

ADD CONSTRAINT CK_ext

CHECK ( Pext LIKE REPLICATE('[0-9]', LEN(Pext)) )

You really shouldn't need full securityadmin just to grant EXECUTE on a proc(s):

GRANT EXECUTE TO user1 WITH GRANT OPTION

FILLFACTOR directs SQL on how full to make each page of data. 96% leaves 4% -- roughly 300 bytes -- free on each page to allow for new rows...

Technically it should be left at 0, as that's more secure.

The ROLLBACK could indeed cause an error if a transaction wasn't active at the time. Here's how to correct that:

IF XACT_STATE() <> 0

BEGIN

ROLLBACK;

END;

You can use a PK or simply a UNIQUE constraint or index. For example:

CREATE UNIQUE CLUSTERED INDEX Student_Classes__CL

ON dbo.Student_Classes ( Student_ID, Class_ID ) WITH...

T_Peters (1/14/2015)

---

No, I didn't turn off compression or make any other changes to the maintenance jobs. Is there any way to query a backup file to see if it used...

Or even another view. A view could also explicitly reference objects in a different db.

Were the backups using compression before and are did not use compression last time for some odd reason?

The complexity of the plan is also a factor in how much SQL weights keeping a plan in cache (by assigning more complex plans higher initial values).

Also, I believe ad-hoc...

curious_sqldba (1/13/2015)

---

ScottPletcher (1/13/2015)

---

You don't need to re-count the rows every time.

DECLARE @BatchSize int

DECLARE @RowRount int

DECLARE @TableRowCount int

DECLARE @TableRowLimit int

SET @BatchSize = 10000

SELECT @TableRowCount = COUNT(*)

FROM PtActs WITH (NOLOCK)

SET @TableRowLimit...

RHJ (1/13/2015)

---

I have a working version of this that is limping along by using a UniqueSession table as a bridge between Locations and Sessions. My boss is unhappy that...

Technically you should still be dealing with a logical not a physical design, i.e. "entities" rather than "tables". but let's jump ahead to tables anyway, I guess.

You're missing at least...

I'd try instead to just:

(1) just in case, as a backup, copy existing key column(s) and data column to be changed to a keyed backup table

(2) verify that existing data...

You don't need to re-count the rows every time.

DECLARE @BatchSize int

DECLARE @RowRount int

DECLARE @TableRowCount int

DECLARE @TableRowLimit int

SET @BatchSize = 10000

SELECT @TableRowCount = COUNT(*)

FROM PtActs WITH (NOLOCK)

SET @TableRowLimit = 10050000

WHILE...