T-SQL ENHANCEMENTS When we first came across the

T-SQL ENHANCEMENTS When we first came across the enhanced size of the VARCHAR data type in SQL Server 7 (from 256 to 8,000), we thought, Great, we can now have a table with several VARCHAR columns with the size of 8,000 instead of a text column. You probably know that this doesn t work, because in SQL Server 7 and 2000, you cannot have a row exceeding 8,060 bytes, the size of a page. In SQL Server 2005 this has changed as well, and a row can now span several pages. T-SQL Language Enhancements Even though this book is much about the CLR and outside access to SQL Server, let s not forget that Microsoft has enhanced the T-SQL language a lot in SQL Server 2005. In this section, we will look at some of the improvements. TOP TOPwas introduced in SQL Server 7. Until SQL Server 2005, the TOPclause allowed the user to specify the number or percent of rows to be returned in a SELECTstatement. In SQL Server 2005, the TOPclause can be used also for INSERT, UPDATE, and DELETE (in addition to SELECT), and the syntax is as follows: TOP(expression)[PERCENT]. Notice the parentheses around the expression; this is required when TOP is used for UPDATE, INSERT, and DELETE. The following code shows some examples of using TOP. create a table and insert some data CREATE TABLE toptest (col1 VARCHAR(150)) INSERT INTO toptest VALUES( Niels1 ) INSERT INTO toptest VALUES( Niels2 ) INSERT INTO toptest VALUES( Niels3 ) INSERT INTO toptest VALUES( Niels4 ) INSERT INTO toptest VALUES( Niels5 ) this returns Niels1 and Niels2 SELECT TOP(2) * FROM toptest this sets Niels1 and Niels2 to hi UPDATE TOP(2) toptest SET col1 = hi SELECT * FROM toptest the two rows with hi are deleted DELETE TOP(2) toptest SELECT * FROM toptest create a new table and insert some data CREATE TABLE toptest2 (col1 VARCHAR(150))

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

LARGE VALUE DATA TYPES Large Value Data Types

LARGE VALUE DATA TYPES Large Value Data Types In SQL Server 2000 (and 7) the maximum size for VARCHAR and VARBINARY was 8,000 and for NVARCHAR4,000. If you had data that potentially exceeded that size, you needed to use the TEXT, NTEXT, or IMAGEdata types (known as Large Object data types, or LOBs). This was always a hassle because they were hard to work with, in both retrieval and action statements. This situation changes in SQL Server 2005 with the introduction of the MAX specifier. This specifier allows storage of up to 231 bytes of data, and for Unicode it is 230 characters. When you use the VARCHAR(MAX) or NVARCHAR(MAX) data type, the data is stored as character strings, whereas for VARBINARY(MAX) it is stored as bytes. These three data types are commonly known as Large Value data types. The following code shows the use of these data types in action. CREATE TABLE largeValues ( lVarchar VARCHAR(MAX), lnVarchar NVARCHAR(MAX), lVarbinary VARBINARY(MAX) ) We mentioned earlier that LOBs are hard to work with. Additionally, they cannot, for example, be used as variables in a procedure or a function. The Large Value data types do not have these restrictions, as we can see in the following code snippet, which shows a Large Value data type being a parameter in a function. It also shows how the data type can be concatenated. CREATE FUNCTION dovmax(@in VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN supports concatenation RETURN @in + 12345 END SQL Server s string handling functions can be used on VARCHAR(MAX) and NVARCHAR(MAX) columns. So instead of having to read in the whole amount of data, SUBSTRING can be used. By storing the data as character strings (or bytes), the Large Value data types are similar in behavior to their smaller counterparts VARCHAR, NVARCHAR, and VARBINARY, and offer a consistent programming model. Using the Large Value data types instead of LOBs is recommended; in fact, the LOBs are being deprecated.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

T-SQL ENHANCEMENTS BEGIN do something useful WITH the

T-SQL ENHANCEMENTS BEGIN do something useful WITH the message here we just select it as a result SELECT CONVERT(NVARCHAR(MAX), @msg) END Running this code against the CREATE TABLE statement shown earlier produces the same output as in Listing 7-4. An additional benefit with event notifications is that they can be used for both system level and trace events in addition to DDL events. The following code shows how to create an event notification for SQL Server logins. CREATE EVENT NOTIFICATION loginEvents ON SERVER FOR audit_login TO SERVICE evtLoginService For system-level event notifications, the ON SERVER keyword needs to be explicitly specified; it cannot be used at the database level. Listing 7-6 shows the eventdata information received after executing a login. Listing 7-6: eventdata Output from Login 2003-06-29T09:46:23.623 51 AUDIT_LOGIN ZMV44 ZMV44Administrator ZMV44Administrator eventstest < ! additional information elided > You may wonder what happens if the transaction that caused the notification is rolled back. In that case, the posting of the notification is rolled back as well. If for some reason the delivery of a notification fails, the original transaction is not affected. Some of the previous code examples have used VARCHAR(MAX)as the data type for a column. Let s look at what that is all about.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

EVENT NOTIFICATIONS application s requirements. For event notifications, however,

EVENT NOTIFICATIONS application s requirements. For event notifications, however, Microsoft hasapredefined message type, {http://schemas.microsoft.com/SQL/ Notifications}EventNotification, and a contract, http://schemas. microsoft.com/SQL/Notifications/PostEventNotification. The following code shows how to create an event notification for DDL events scoped to the local database, sending the notifications to the evt DdlService. CREATE EVENT NOTIFICATION ddlEvents ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS TO SERVICE evtDdlService With both the event notification and the service in place, a new process can now be started in SQL Server Management Studio, using the WAITFOR and RECEIVE statements (more about this in Chapter 15) as in the following code. WAITFOR( RECEIVE * FROM evtDdlNotif ) You can now execute a DDL statement, and then switch to the process with the WAITFOR statement and view the result. Running CREATE TABLE evtNotifTbl(id INT)shows in the WAITFORprocess a two-row resultset, where one of the rows has a message_type_id of 20. This is the {http:// schemas.microsoft.com/SQL/Notifications}EventNotification message type. The eventdata information is stored as a binary value in the message_body column. To see the actual data, we need to change the WAITFORstatement a little bit. DECLARE @msgtypeid INT DECLARE @msg VARBINARY(MAX) WAITFOR( RECEIVE TOP(1) @msgtypeid = message_type_id, @msg = message_body FROM evtDdlNotif ) check if this is the correct message type IF @msgtypeid = 20

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

T-SQL ENHANCEMENTS event_type This is the name of

T-SQL ENHANCEMENTS event_type This is the name of an event that, after execution, causes the event notification to execute. SQL Server Books Online has the full list of events included in event_type. DDL_DATABASE_LEVEL_EVENTS The event notification fires after any of the CREATE, ALTER, or DROPstatements that can be indicated in event_typeexecute. broker_service This is the SSB service to which SQL Server posts the data about an event. The event notification contains the same information received from the eventdata function mentioned previously. When the event notification fires, the notification mechanism executes the eventdata function and posts the information to the Service Broker. For an event notification to be created, an existing SQL Server Service Broker instance needs to be located either locally or remotely. The steps to create the SQL Server Service Broker are shown in Listing 7-5. Chapter 15 covers SSB in detail and also covers how to create queues, services, and so on. Listing 7-5: Steps to Create a Service Broker Instance first we need a queue CREATE QUEUE queue evtDdlNotif WITH STATUS = ON then we can create the service CREATE SERVICE evtDdlService ON QUEUE evtDdlNotif this is a MS supplied contract which uses an existing message type {http://schemas.microsoft.com/SQL/Notifications}EventNotification (http://schemas.microsoft.com/SQL/Notifications/PostEventNotification) First, the message queue that will hold the eventdata information is created. Typically, another process listens for incoming messages on this queue, or another process will kick off when a message arrives. A service is then built on the queue. When a SQL Server Service Broker service is created, there needs to be a contract to indicate what types of messages this service understands. In a SQL Server Service Broker application, the developer usually defines message types and contracts based on the

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

EVENT NOTIFICATIONS certain tasks, it would be beneficial

EVENT NOTIFICATIONS certain tasks, it would be beneficial to have an asynchronous event model. Therefore, in SQL Server 2005 Microsoft has included a new event notification model that works asynchronously: event notifications. Event Notifications Event notifications differ from triggers by the fact that the actual notification does not execute any code. Instead, information about the event is posted to a SQL Server Service Broker (SSB) service and is placed on a message queue from where it can be read by some other process.1 Another difference between triggers and event notifications is that the event notifications execute in response to not only DDL and DML statements but also some trace events. The syntax for creating an event notification is as follows. CREATE EVENT NOTIFICATION event_notification_name ON { SERVER | DATABASE | [ ENABLED | DISABLED ] { FOR { event_type | DDL_DATABASE_LEVEL_EVENTS } [ ,...n ] TO broker_service The syntax looks a little like the syntax for creating a DDL trigger, and the arguments are as follows. event_notification_name This is the name of the event notification. SERVER The scope of the event notification is the current server. DATABASE The scope of the event notification is the current database. ENABLED This specifies that the event notification is active when the CREATEstatement has executed. DISABLED This specifies that the event notification is inactive until the notification is activated by executing an ALTER EVENT NOTIFICATIONstatement. 1 SQL Server Service Broker is a new technology in SQL Server 2005 that facilitates sending messages in a secure and reliable way. It is covered in Chapter 15.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

T-SQL ENHANCEMENTS pubs dbo foo TABLE CREATE TABLE

T-SQL ENHANCEMENTS pubs dbo foo TABLE CREATE TABLE evtTest (id int primary key) Because the data returned from the function is XML, we can use XQuery queries to retrieve specific item information. This can be done both in the trigger and from the table where we store the data. The following code illustrates how to retrieve information about the EventType, Object, and CommandText items in the eventdata information stored in the table ddlLog. Notice that we first store it into an XML data type variable, before we execute the XQuery statement against it. DECLARE @data XML SELECT @data = logTxt FROM ddlLog WHERE id = 11 SELECT CONVERT(NVARCHAR(100), @data.query( data(//EventType) )) EventType, CONVERT(NVARCHAR(100), @data.query( data(//Object) )) Object, CONVERT(NVARCHAR(100), @data.query( data(//TSQLCommand/CommandText) )) Command If the syntax in the previous code snippet seems strange, that s because it is XML and XQuery; read Chapters 8 and 9, where the XML data type and XQuery are covered in detail. The programming model for both DML and DDL triggers is a synchronous model, which serves well when the processing that the trigger does is relatively short-running. This is necessary because DDL and DML triggers can be used to enforce rules and can roll back transactions if these rules are violated. If the trigger needs to do longer-running processing tasks, the scalability inevitably suffers. Bearing this in mind, we can see that for

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

DDL TRIGGERS The additional information included in the

DDL TRIGGERS The additional information included in the result from eventdata is covered in SQL Server Books Online, so we will not go through each item here. However, for our trigger, which fires on the DROP TABLE command, the additional information items are as follows: Database Schema Object ObjectType TSQLCommand In Listing 7-3 we change the trigger to insert the information from the eventdata function into the ddlLog table. Additionally, we change the trigger to fire on all DDL events. Listing 7-3: Alter Trigger to Use eventdata alter the trigger ALTER TRIGGER ddlTri ON DATABASE AFTER DDL_DATABASE_LEVEL_EVENTS AS INSERT INTO ddlLog VALUES CONVERT(VARCHAR(max)eventdata() From the following code, we get the output in Listing 7-4. delete all entries in ddlLog DELETE ddlLog create a new table CREATE TABLE evtTest (id INT PRIMARY KEY) select the logTxt column with the XML SELECT logTxt FROM ddlLog Listing 7-4: Output from eventdata 2004-01-30T11:58:47.217 57 CREATE_TABLE ZMV44 ZMV44Administrator ZMV44Administrator

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

T-SQL ENHANCEMENTS create our test table CREATE TABLE

T-SQL ENHANCEMENTS create our test table CREATE TABLE triTest (id INT PRIMARY KEY) GO create the trigger CREATE TRIGGER ddlTri ON DATABASE AFTER DROP_TABLE AS INSERT INTO ddlLog VALUES( table dropped ) You may wonder what the VARCHAR(MAX) is all about in creating the first table we ll cover that later in this chapter. The trigger is created with a scope of the local database (ON DATABASE), and it fires as soon as a table is dropped in that database (ON DROP_TABLE). Run following code to see the trigger in action. DROP TABLE triTest SELECT * FROM ddlLog The DROPTABLEcommand fires the trigger and inserts one record in the ddlLogtable, which is retrieved by the SELECTcommand. As mentioned previously, DDL triggers can be very useful for logging and auditing. However, we do not get very much information from the trigger we just created. In DML triggers, we have the inserted and deleted tables, which allow us to get information about the data affected by the trigger. So, clearly, we need a way to get more information about events when a DDL trigger fires. The way to do that is through the event datafunction. Eventdata The eventdata() function returns information about what event fired a specific DDL trigger. The return value of the function is XML, and the XMLis typed to a particular schema (XSD). Depending on the event type, the XSD includes different information. The following four items, however, are included for any event type: The time of the event The SPID of the connection that caused the trigger to fire The login name and user name of the user who executed the statement The type of the event

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

DDL TRIGGERS What about changes based on Data

DDL TRIGGERS What about changes based on Data Definition Language statements, changes to the schema of a database or database server? It has not been possible to use triggers for that purpose that is, until SQL Server 2005. In SQL Server 2005 you can create triggers for DDL statements as well as DML. The syntax for creating a trigger for a DDL statement is shown in Listing 7-2, and as with a DML trigger, DDL triggers can be written using .NET languages as well. Listing 7-2: Syntax for a DDL Trigger CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH ENCRYPTION ] { FOR | AFTER } { event_type [ ,...n ] | DDL_DATABASE_LEVEL_EVENTS } [ WITH APPEND ] [ NOT FOR REPLICATION ] { AS { sql_statement [ ...n ] | EXTERNAL NAME < method specifier > } } < method_specifier > ::= assembly_name:class_name[::method_name] The syntax for a DML trigger is almost identical to that for a DDL trigger. There are, however, some differences. The ONclause in a DDL trigger refers to either the scope of the whole database server (ALL SERVER) or the current database (DATABASE). A DDL trigger cannot be an INSTEAD OFtrigger. The event for which the trigger fires is defined in the event_type argument, which for several events is a comma-delimited list. Alternatively, you can use the blanket argument DDL_DATABASE_LEVEL_EVENTS. The SQL Server Books Online has the full list of DDL statements, which can be used in the event_type argument and also by default are included in the DDL_DATABASE_LEVEL_EVENTS. A typical use of DDL triggers is for auditing and logging. The following code shows a simple example where we create a trigger that writes to a log table. first create a table to log to CREATE TABLE ddlLog (id INT PRIMARY KEY IDENTITY, logTxt VARCHAR(MAX)) GO

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services