SQL Server 2005 introduces DDL Triggers

When you wanted to audit changes for the underlying schema in earlier versions of SQL Server and it was very difficult, now with SQL Server 2005 introduces DDL Triggers to address this issue. A DDL Trigger can now be created either at a server or database level and can be set to fire on creation, alteration, or deletion of virtually every SQL Server object type.

-- Example of DDL Trigger @ Database Level:
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK ;

In the next example, a DDL trigger prints a message if any CREATE LOGIN, ALTER LOGIN, or DROP LOGIN event occurs on the current server instance. It uses the EVENTDATA function to retrieve the text of the corresponding Transact-SQL statement.

-- Example of DDL Trigger @ Server Level:
CREATE TRIGGER ddl_trig_login
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
PRINT 'Login Event Issued.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_login
ON ALL SERVER
GO

SQL 2005 - SQLCMD command-line tool

SQLCMD command-line tool allows you to pass variables from the command line into the SQL Script itself. In SQL 2005 now, you’ll can able to use SQLCMD as below example:
- Database build scripts where you need to pass values specific to a certain environment. - Hot fixes and patches.
- Any scenario where use of stored procedures (with it’s built-in parameters) is not an option and you must rely on a SQL script.
In this example SQLCMDTest.SQL [NOTE: That you must delimit character strings with a single quote]
declare @DaysToAdd datetime,
@MyString varchar(32)

SELECT @DaysToAdd =$(daystoAdd),
@MyString='$(mystring)'

SELECT GETDATE()+@DaysToAdd,
@MyString

Then, you call the SQL Script passing in the values as:
SQLCMD -b -w4000 -l10 -E -i"SQLCMDTest.sql" -v daystoAdd="10" -v mystring="CeltoGrass"

SQL 2005 - Share Job Schedule

With SQL Server 2005 you can now share job schedules that are owned by same user. In SQL Server 2005 Agent, User can create a single schedule (for instance, occurring every day at midnight), and attach it to one or more jobs, provided he is the owner of the jobs.

Consider the following example:
User1 creates a job called "Job1" with a schedule called "Schedule1". Since he was told only to run jobs starting at the time defined in Schedule1 by the product team, User1 wants to create his second job, called Job2, with this same schedule.

The simplest to do this would be from SQL Server Management Studio by clicking on the job schedule properties and selecting the "Pick" button which will allow him to select the schedules from the listed job. This would also allow User1 to view all the other jobs that has same schedule. He would be able to see only those jobs that were created by him unless he is a system administrator.

In SQL Server 2005 Implicit conversion from string to datetime (Ex: in "where" clause "where BirthDay='01/01/1980'") is considered not-deterministic and can't be present in Indexed View definition.
-- Example:
Create view v_test with SCHEMABINDING as
Select c1,c2 from dbo.t_test where c2 = '01/01/1980'
go
Create unique clustered index idx_v_test on v_test(c1)
go
-- Result:Cannot create index on view 'db_test.dbo.v_test' because the view uses an implicit
-- conversion from string to datetime or smalldatetime. Use an explicit CONVERT with a
-- deterministic style value.

-- Solution: You can use explicit conversion specifying style value for example:
Create view v_test with SCHEMABINDING as
Select c1,c2 from dbo.t_test where c2 = convert(datetime,'01/01/1980',120)
go

-- In this case string will be converted to datetime input as yyyy-mm-dd hh:mi:ss (24h).
Create unique clustered index idx_v_test on v_test(c1)
go
--Result:Command(s)
-- completed successfully.

In SQL Server 2005 Control permission is special cased. Control grant chains are always rooted at the owner unless you explicitly use an AS clause. This is to prevent orphaned grant arcs for other permissions that Control covers.
Example:
1. You give User1 Control permission on an object with GRANT OPTION;
2. User1 give User2 Control permission on the object;
3. You REVOKE Control permission on the object from User1 with CASCADE option.
4. User2 still have Control permission on the object.

Grant Control on T to usr1 WITH GRANT OPTION
go
Execute as user = 'usr1'
go
Grant Control on T to usr2
go
REVERT
go
Revoke Control on T from usr1 Cascade
go

-- Usr2 still has control permission on T. To be able to revoke control permission from all users
-- who's been given this permission by User1.
-- User1 should be specified explicitly when granting permission to User2:
Grant Control on T to usr2 AS usr1
go

SQL 2005 - OUTPUT clause with DML

SQL Server 2005 now introduces an OUTPUT clause as a part of DML statements that can help you in tracking changes made during any DML operation. The OUTPUT clause can save the resultset in a table or table variable. This functionality is similar to what triggers had with INSERTED and DELETED tables which used to access the rows that have been modified during the DML operation.

Example: Let's change the address from the address table to the reverse of the original value.
--Create the address table
Create Table Address (ProductID Int, SupplierID Int, Address Varchar(255))
--Insert data
Insert into Address Values (234,567,'1234 One SQL Way, Microsoft City, U.S.')
Insert into Address Values (345,678,'1234 One Windows Way, Microsoft City, WA')
--Declare a table variable
Declare @Recordchanges table (change Varchar(255))
--Update the address
Update Supplier.Address Set Address=reverse(address)
--Record the updates into the table variable
OUTPUT 'Original Value:' + DELETED.Address+' has been changed to: '+ INSERTED.Address+'' into @RecordChanges
--Query the changes from table variable
Select * from @RecordChanges

--Result-set
------------------------
Original Value:'1234 One SQL Way, City, U.S.' has been changed to: '.S.U ,ytiC,yaW LQS enO 4321'
Original Value:'1234 One Windows Way, City, WA' has been changed to: 'AW ,ytiC ,yaW swodniW enO 4321'

Query Notification in SQL 2005

Query Notification in SQL 2005 can be used to send a query to SQL Server and request that a notification be generated if executing the same query produces different results from those obtained initially. That means if any row in one of the tables included in the query is changed, .NET code will get an automatic notification.
MSDN Link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/querynotification.asp

SQL 2005 - SYNONYMS

Remember how uneasy it was to write multiple queries which had four part object names
(ServerName.DatabaseName.OwnerName.ObjectName). Using SYNONYMS (new in SQL Server 2005) you can create an alias for objects.

Example:
-- Without SYNONYMS you would use the query the following way:
Select * from LongServerName.LongDatabaseName.LongOwnerName.LongObjectName
-- With SYNONYMS CREATE SYNONYM LNG FOR LongServerName.LongDatabaseName.LongOwnerName.LongObjectName
-- Once you create the SYNONYM you can use the above query as follows
Select * from LNG

SQL 2005 - Excute Remotely

Prior to SQL Server 2005 you could execute EXECUTE command only on the local server, with SQL Server 2005 we have AT parameter which can be used for executing the statement on a remote linked server.
Example: Setup a linked server using SP_AddLinkedServer:
-- Add the linked server to the local machine
EXEC sp_addlinkedserver 'SQLSERVER2', 'SQL Server'

--Enable the linked server to allow RPC calls
Exec SP_Serveroption 'SQLSERVER2','RPC OUT',TRUE

-- Now you are ready to execute T-SQL statements across linked servers using AT command
EXEC('Select * from AdventureWorksDW..DatabaseLog') AT SQLSERVER2

DBCC DBREINDEX - Deprecated

DBCC DBREINDEX is deprecated in SQL Server 2005. With SQL Server 2000 and earlier versions we used to use DBCC DBREINDEX for rebuilding/defragging/repairing indexes, etc. But with SQL Server 2005 this command is being deprecated.
SQL Server 2005 introduces ALTER INDEX command with REBUILD option.This command can help you perform ONLINE or OFFLINE re-indexing operations (not like DBCC DBREINDEX which was an offline operation only)