Question of the month April 2015 – Why is extra comma giving error for table variable and not for temporary table?

Consider the following statement

CREATE TABLE #T (I INT,)

It will get executed without error

But when you use table variable as shown below

DECLARE @T TABLE(I INT,)

You get the following error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

Why is extra comma giving error for table variable and not for temporary table?

Sequence – Union / Union all

You have known the difference between UNION and UNION ALL. But there is similarity when used sequence. It is not possible to use them to combine sequence values.

Create the following sequence

CREATE SEQUENCE MYSEQ
AS INT
START WITH 1 INCREMENT BY 1

You get the sequence value using

SELECT NEXT VALUE FOR MYSEQ 

If you want to use UNION or UNION ALL combing different sequence values, it is not possible. Both the following codes when executed throws error

SELECT NEXT VALUE FOR MYSEQ
UNION
SELECT NEXT VALUE FOR MYSEQ 

SELECT NEXT VALUE FOR MYSEQ
UNION ALL
SELECT NEXT VALUE FOR MYSEQ 

There error is

Msg 11721, Level 15, State 1, Line 2

NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

Scripting out a DDL trigger

A DDL trigger helps you to monitor when a table is Created , altered or dropped. You can perform some auditing whenever these happen.

You can create a database scoped DDL trigger as shown below

create trigger ddl_trigger on database
for
drop_table,
alter_table
as
select
'access denied to alter/drop this table'
rollback

But you can not see the script of DDL trigger using SP_HELPTEXT like other DML triggers

When you execute the below

EXEC sp_helptext ddl_trigger

You get the following error


Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54
The object 'ddl_trigger' does not exist in database 'test' or is invalid for this operation.

The other way is to make use of sys.triggers catalog view to identify and script out the definition using Object_definition function

select object_definition(object_id) as definition from sys.triggers
where parent_class=0