Liked Rune's comment.

And that is why you should always explicitly specify NOT / NOT NULL when creating a table.  From the context of the q, I could work out that...

Shrink only the file(s) that are part of the PRIMARY filegroup:

DBCC SHRINKFILE(1, 512)

DBCC SHRINKFILE(?, 512) --if you have additional file(s) in the PRIMARY fg

SELECT CAST(SUM(OrigWeight/2000.0) AS decimal(10,2)) Tons

SELECT OBJECT_NAME(object_id) AS object_name
    SUBSTRING(definition, CHARINDEX('##', definition) - 20, 200) AS definition_first_match,
    definition
FROM sys.sql_modules
WHERE definition LIKE N'%##%'
ORDER BY 1

Edit: Sorry, I...

• This reply was modified 2 years, 10 months ago by ScottPletcher.

SQL will take only those locks needed to guarantee consistency and accuracy in results.  As Jeff said, the details of that depend, but that is the general approach.

Is A an...

Do you -- the user running the code -- also have sysadmin permission?  If you have sysadmin permission, any DENYs are ignored.

Search for '##' in the text of the proc.  The only real reason that string should be in there is to reference a global temp table.

The PARTITION SCHEME tells SQL which filegroups to use for the partitions.

would you care to...

would you care to explain exactly...

There are a number of restrictions and possible gotchas with filtered indexes.  In theory they are great, in practice they can be difficult.

Until you've had time to fully test out...

Are you sure you matched the original data type and length?  For example, if the original columns was varchar(8000) and you specified varchar(4000), obviously data might get truncated.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER test_trg1
ON dbo.test
AFTER INSERT,UPDATE
AS
SET NOCOUNT ON;

IF UPDATE(field_key)
BEGIN
    INSERT INTO dbo.audit ( name, a_date, col, old_col, new_col )
  ...

I'd go with:

SELECT CONCAT(@Route, '(' + NULLIF(@USRoute, '') + ')', 
    CASE WHEN LEN(@Route) + LEN(@USRoute) = 0 THEN '' ELSE ', ' END,...

I think you would have to add a GO, otherwise if the table already exists SQL Server won't be able to "compile" the SQL:

drop table if exists...