Since there is no direct permission in SQL Server to drop a table, here are two techniques to implement that.

The first one is based on creating a view on the table with option SCHEMABINDING. When the SCHEMABINDING option is used the table cannot be modified in a way that will affect the view definition, as well as it cannot be dropped unless the view is dropped first.

The second method is using the new DDL triggers in SQL Server 2005. Defining a trigger for DROP_TABLE with rollback in the body will not allow dropping tables.

CREATE TABLE dbo.Foo (
 keycol INT PRIMARY KEY,
 datacol CHAR(1));
 
GO 
 
-- Using view with SCHEMABINDING
CREATE VIEW DoNotDropFoo WITH SCHEMABINDING
AS
SELECT keycol, datacol
FROM dbo.Foo;
 
GO
 
-- Attempt to drop table Foo
DROP TABLE dbo.Foo;
 
GO
 
Msg 3729, Level 16, State 1, Line 3
Cannot DROP TABLE 'dbo.Foo' because it is being referenced by object 'DoNotDropFoo'.
 
 
-- Using DDL trigger
CREATE TRIGGER DoNotDropTables 
ON DATABASE
FOR DROP_TABLE
AS
  RAISERROR ('Cannot drop tables!', 10, 1);
  ROLLBACK;
 
GO
 
-- Attempt to drop table Foo
DROP TABLE dbo.Foo;
 
GO
 
Cannot drop tables!
Msg 3609, Level 16, State 2, Line 3
The transaction ended in the trigger. The batch has been aborted.

It is a common misunderstanding when comparing temporary tables and table variables that one difference is that table variables cannot have indexes. While table variables are not directly materialized and you cannot execute CREATE INDEX on them, an index can be part of the definition of the table variable.

Here is one example with two indexes on a table variable.

SET SHOWPLAN_TEXT ON;
GO
 
DECLARE @Employees TABLE (emp_nbr INT PRIMARY KEY NONCLUSTERED,
                          emp_name VARCHAR(35),
                          ssn CHAR(11) UNIQUE CLUSTERED);
 
 
INSERT INTO @Employees VALUES(1, 'Jim Brown', '987-11-0234');
INSERT INTO @Employees VALUES(2, 'Chris Defoe', '987-13-9899');
INSERT INTO @Employees VALUES(3, 'Keith Goldwin', '986-31-1543');
 
SELECT emp_nbr, emp_name, ssn
FROM @Employees
WHERE emp_nbr = 2;
 
SELECT emp_nbr, emp_name, ssn
FROM @Employees
WHERE ssn LIKE '998-13%';
 
--Results
  |--Nested Loops(Inner Join, OUTER REFERENCES:([ssn]))
       |--Index Seek(OBJECT:(@Employees), SEEK:([emp_nbr]=(2)) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:(@Employees), SEEK:([ssn]=[ssn]) LOOKUP ORDERED FORWARD)
 
  |--Clustered Index Seek(OBJECT:(@Employees), SEEK:([ssn] >= '998-12þ' AND [ssn] < '998-14'), WHERE:([ssn] like '998-13%') ORDERED FORWARD)

The ABS function in SQL Server is a little bit overlooked and unutilized. It returns the absolute, positive value of the given numeric expression.

One particular set of problems that ABS is very useful is when needing the find the closest match to a given value. That could be to find the closest ZIP code, date, etc. Below are two examples of utilizing ABS for this purpose.

-- Create table with sales regions
CREATE TABLE SalesRegions (
 zip_code INT PRIMARY KEY,
 region_name VARCHAR(35));
 
-- Insert sampel data
INSERT INTO SalesRegions VALUES(40320, 'North Area');
INSERT INTO SalesRegions VALUES(40324, 'North/East Area');
INSERT INTO SalesRegions VALUES(40326, 'North/West Area');
 
-- Define search zip code
DECLARE @search_zip INT;
SET @search_zip = 40323;
 
-- Find the closes region by zip code
SELECT TOP(1) zip_code, region_name
FROM SalesRegions
ORDER BY ABS(zip_code - @search_zip);
 
-- Results
zip_code    region_name
----------- ---------------
40324       North/East Area
 
 
-- Create Employee table
CREATE TABLE Employees (
 employee_nbr INT PRIMARY KEY,
 employee_name VARCHAR(35));
 
-- Employee evaluation dates
CREATE TABLE EmployeeEvaluations (
 employee_nbr INT,
 evaluation_date DATETIME,
 PRIMARY KEY (employee_nbr, evaluation_date));
 
-- Sample data
INSERT INTO Employees VALUES(1, 'John Doe');
INSERT INTO Employees VALUES(2, 'Jeff Brown');
 
INSERT INTO EmployeeEvaluations VALUES(1, '20070101');
INSERT INTO EmployeeEvaluations VALUES(1, '20080101');
INSERT INTO EmployeeEvaluations VALUES(1, '20080304');
INSERT INTO EmployeeEvaluations VALUES(2, '20080604');
 
-- Find the closest evaluation date, could be in the future
SELECT A.employee_nbr, A.employee_name, B.evaluation_date
FROM Employees AS A
CROSS APPLY
(SELECT TOP(1) B.evaluation_date
 FROM EmployeeEvaluations AS B
 WHERE A.employee_nbr = B.employee_nbr
 ORDER BY ABS(DATEDIFF(DAY, B.evaluation_date, CURRENT_TIMESTAMP))) AS B;
 
-- Results
employee_nbr employee_name  evaluation_date
------------ -------------- -----------------------
1            John Doe       2008-03-04 00:00:00.000
2            Jeff Brown     2008-06-04 00:00:00.000