(1) Microsoft .NET Framework: Common Language Runtime Integration
The Microsoft .NET Framework common language runtime (CLR) is now hosted in the SQL Server Database Engine. This CLR integration environment supports procedural database objects, including functions, stored procedures, and triggers, that are written in .NET Framework-based languages such as Microsoft Visual C# and Visual Basic .NET.
(2) Microsoft .NET Framework: CLR User-Defined Aggregates
(3) User-defined aggregate functions written in a .NET Framework-based language extend the basic aggregate functions, such as SUM, MIN, and MAX, included in Transact-SQL. An aggregate function is one that processes several data values and returns a single value.
(4) Microsoft .NET Framework: User-Defined Types
CLR user-defined types (UDTs) allow you to implement database data types in any of the languages supported by the .NET Framework CLR. CLR user-defined types are implemented as classes in a .NET Framework-based language and can have complex structures and behavior defined by the fields, methods, and properties of the class. CLR user-defined types are integrated with the Database Engine type system and can be used in all the contexts where the built-in types are used.
(5) Microsoft .NET Framework: Stored Procedures, Triggers, and User-defined Functions
Database procedural objects can be written in .NET Framework-based languages to incorporate more complex logic than is supported by the Transact-SQL language. Programmers develop the objects using the same language and development environment as they use to develop .NET Framework components and services.
(6) Web Access: Native HTTP SOAP Access – deprecated in SQL Server 2008 and replaced with Windows Communications Foundation (WCF) or ASP.NET.
==========================Extended Reading================================
Native XML Web Services Overview
This topic compares Native XML
Web Services in Microsoft SQL Server 2005 to Microsoft SQLXML, describes how
native XML Web services works, and lists some benefits from using it.
Native XML Web Services is not
useful or recommended for the following scenarios:
·
Applications characterized by real time highly concurrent
access, with short duration transactions.
·
Web farm type scale-out.
·
As a replacement for the middle tier, specifically where your
application architecture has large-scale business logic demands that are better
accommodated within middle-tier components.
Comparing Native XML Web Services to SQLXML
In earlier versions of SQL
Server, access to a SQL Server database requires using Tabular Data Stream
(TDS). TDS is a proprietary protocol that must be supported for Windows-based
desktop clients. Sometimes, SQL Server clients must use Microsoft Data Access
Components (MDAC). The MDAC stack is installed on the client computer that
connects to SQL Server. For SQL Server, SQLXML 3.0 is a middle-tier component
that supports Web-based access to SQL Server, but Internet Information Services
(IIS) must also be used.
In SQL Server 2005, by
combining the use of HTTP and SOAP, native XML Web services provides an
alternative for environments other than Windows, as shown in the following
illustration.
Because there is no longer a
need for either MDAC installed at the client or for SQLXML with its dependency
at the middle-tier on IIS, SOAP and HTTP access enables a broader range of
clients to access SQL Server. These include Web application clients that use
existing client applications, such as a Web browser. Native XML Web Services
makes it easier to work with the Microsoft .NET Framework, Microsoft SOAP
Toolkit, Perl, and other Web development operating systems and toolsets.
The following table shows some
features that each technology offers.
|
Native XML Web
Services |
Microsoft SQLXML |
|
|
How Native XML Web Services Works
To use Native XML Web Services
in SQL Server, an HTTP endpoint must to be established at the server. This
endpoint is essentially the gateway through which HTTP-based clients can query
the server. After an HTTP endpoint is established, stored procedures or
user-defined functions can be added or made available to endpoint users. This
can occur when the endpoint is either created or updated. When procedures and
functions are enabled, they are specified as Web methods. A collection
of Web methods that are designed to be used together can be called a Web
service.
These Web services can be
described by using the WSDL format. The WSDL format is generated by an instance
of SQL Server and returned to SOAP clients for any HTTP endpoints on which WSDL
is enabled, as shown in the following illustration. If required, the WSDL
format can be a custom solution instead of one generated by SQL Server. Optionally,
the endpoint can be configured to not answer WSDL requests.
Following this process,
collections of SQL Server-enabled Web services can be implemented and used to
help build and populate a Service-Oriented Architecture (SOA).
In short, in SQL Server
2005, SQL Server provides native XML Web Services through the SQL Server
Database Engine by using the following open standards:
·
Hypertext Transfer Protocol (HTTP) - As the core protocol behind
the World Wide Web, HTTP provides a platform-neutral Web-based exchange of
data.
·
SOAP - SOAP defines how to use XML and HTTP to access services,
objects, and servers regardless of the operating system.
·
Web Services Definition Language (WSDL) - WSDL is an XML
document format that can be used to describe Web-based services.
When you use Native XML Web
Services in SQL Server 2005 or later, you can send SOAP messaging requests to
an instance of SQL Server over HTTP to run the following:
·
Transact-SQL batch statements, with or without parameters.
·
Stored procedures, extended stored procedures, and scalar-valued
user-defined functions.
=====================================================================================
(7) Transact-SQL Enhancements: New xml Data Type
XML data can be stored in columns, stored procedure parameters, or variables created using the xml type.
(8) Transact-SQL Enhancements: FOR XML Enhancements (RAW, AUTO, EXPLICIT, PATH)
·
TYPE
Directive in FOR XML Queries
·
Generating
Elements for NULL Values Using the XSINIL Parameter
·
FOR
XML Support for Various SQL Server Data Types
·
Inline
XSD Schema Generation
·
Change
in Inline XDR Schema Generation
(9) Transact-SQL Enhancements: Improved Error Handling - TRY-CATCH.
(10) Transact-SQL Enhancements: New Metadata Views - System catalog views and DMVs
(11) Transact-SQL Enhancements: Ranking Functions
·
RANK
·
DENSE_RANK
·
NTILE
·
ROW_NUMBER
(12) Transact-SQL Enhancements: Data Definition Language (DDL) Triggers
(13) Transact-SQL Enhancements: Event Notifications – still in SQL Server 2016
Event notifications execute in response to a variety of
Transact-SQL data definition language (DDL) statements and SQL Trace events by
sending information about these events to a Service Broker service.
Event notifications can be
used to do the following:
·
Log and review changes or activity occurring on the database.
·
Perform an action in response to an event in an asynchronous
instead of synchronous manner.
Event notifications can offer
a programming alternative to DDL triggers and SQL Trace.
Benefits of event
notifications: Event notifications run asynchronously, outside the scope of a
transaction. Therefore, unlike DDL triggers, event notifications can be used
inside a database application to respond to events without using any resources
defined by the immediate transaction.
Unlike SQL Trace, event
notifications can be used to perform an action inside an instance of SQL Server
in response to a SQL Trace event.
When an event notification is
created, one or more Service Broker conversations between an instance of SQL
Server and the target service you specify are opened. The conversations
typically remain open as long as the event notification exists as an object on
the server instance. In some error cases the conversations can close before the
event notification is dropped. These conversations are never shared between
event notifications. Every event notification has its own exclusive
conversations. Ending a conversation explicitly prevents the target service from
receiving more messages, and the conversation will not reopen the next time the
event notification fires.
Event information is delivered
to the Service Broker as a variable of type xml that provides information about when an
event occurs, about the database object affected, the Transact-SQL batch
statement involved, and other information
USE AdventureWorks2008R2;
GO
CREATE EVENT
NOTIFICATION NotifyALTER_T1
ON DATABASE
FOR ALTER_TABLE
TO SERVICE '//Adventure-Works.com/ArchiveService' ,
'8140a771-3c4b-4479-8ac0-81008ab17984';
(14) Transact-SQL Enhancements: Queue Processing Extensions
Three Transact-SQL enhancements simplify
building message queuing applications in loosely coupled, reliable,
Internet-scale and business process automation systems. The INSERT, UPDATE, and
DELETE now support an OUTPUT
clause that return data based on the rows modified by the statement.
READPAST can now be specified on UPDATE and DELETE statements, letting these
statements skip queue rows
held by other tasks and picking the next available row from a queue.
Question 1: Why three, it should be two:
OUTPUT and READPAST
Question 2: How does OUTPUT fit the picture of
queuing is not very clearly explained.
(15) Transact-SQL Enhancements: Unified Large Object Programming Model
In earlier versions of SQL Server, large
strings over 8,000 bytes had to be stored in the text, ntext, and image data types which
had a different programming model than the data types for shorter strings (char, nchar, varchar, nvarchar, binary,
and varbinary). The Database Engine now
supports a MAX length specification for the shorter data types. When MAX is
specified, the data types can store the same size strings as text, ntext, and image values up to 2
gigabytes (GB), but are processed in the same way as when they store shorter
strings.
(16) Transact-SQL Enhancements: Recursive Queries
A Common Table Expressions (CTE) is a results set that is
stored temporarily during the execution of a SELECT, INSERT, UPDATE, or DELETE
statement. Using a CTE allows the use of recursive queries and can
simplify logic by replacing
the use of temporary tables or views.
(17) Transact-SQL Enhancements: New APPLY Operator
The APPLY operator supports invoking a
table-valued expression (a table-valued function or subquery) for each row
returned by an outer table reference. The table-valued expression is evaluated
for each row in the outer table reference.
A UDF
can be joined with other tables, but not by simple joins. They have special
joins called APPLY operator. An APPLY operator allows you to invoke a table-valued function
for each row returned by an outer table expression of a query. The
table-valued function acts as the right input and the outer table expression
acts as the left input. The right input is evaluated for each row from the left
input and the rows produced are combined for the final output. The list of
columns produced by the APPLY operator is the set of columns in the left input
followed by the list of columns returned by the right input.
– CROSS APPLY acts as INNER JOIN, returns only rows from the outer table that
produce a result set from the table-valued function.
–
OUTER APPLY acts as
OUTER JOIN, returns both rows that produce a result set, and rows that do not,
with NULL values in the columns produced by the table-valued function.
--Create Employees table and insert values.
CREATE TABLE
Employees
(
empid int
NOT NULL,
mgrid int
NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees PRIMARY
KEY(empid),
)
GO
INSERT INTO
Employees VALUES(1
, NULL, 'Nancy'
, $10000.00)
INSERT INTO
Employees VALUES(2
, 1
, 'Andrew' ,
$5000.00)
INSERT INTO
Employees VALUES(3
, 1
, 'Janet' ,
$5000.00)
INSERT INTO
Employees VALUES(4
, 1
, 'Margaret', $5000.00)
INSERT INTO
Employees VALUES(5
, 2
, 'Steven' ,
$2500.00)
INSERT INTO
Employees VALUES(6
, 2
, 'Michael'
, $2500.00)
INSERT INTO
Employees VALUES(7
, 3
, 'Robert' ,
$2500.00)
INSERT INTO
Employees VALUES(8
, 3
, 'Laura' ,
$2500.00)
INSERT INTO
Employees VALUES(9
, 3
, 'Ann' , $2500.00)
INSERT INTO
Employees VALUES(10, 4
, 'Ina' , $2500.00)
INSERT INTO
Employees VALUES(11, 7
, 'David' ,
$2000.00)
INSERT INTO
Employees VALUES(12, 7
, 'Ron' , $2000.00)
INSERT INTO
Employees VALUES(13, 7
, 'Dan' , $2000.00)
INSERT INTO
Employees VALUES(14, 11 , 'James' ,
$1500.00)
GO
--Create Departments table and insert values.
CREATE TABLE
Departments
(
deptid INT
NOT NULL PRIMARY KEY,
deptname VARCHAR(25) NOT NULL,
deptmgrid INT NULL REFERENCES Employees
)
GO
INSERT INTO
Departments VALUES(1, 'HR',
2)
INSERT INTO
Departments VALUES(2, 'Marketing',
7)
INSERT INTO
Departments VALUES(3, 'Finance', 8)
INSERT INTO
Departments VALUES(4, 'R&D',
9)
INSERT INTO
Departments VALUES(5, 'Training', 4)
INSERT INTO
Departments VALUES(6, 'Gardening', NULL)
CREATE FUNCTION
dbo.fn_getsubtree(@empid
AS INT) RETURNS @TREE TABLE
(
empid INT
NOT NULL,
empname VARCHAR(25) NOT NULL,
mgrid INT
NULL,
lvl INT NOT NULL
)
AS
BEGIN
WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = @empid
UNION all
-- Recursive Member (RM)
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM Employees AS e
JOIN Employees_Subtree AS es
ON e.mgrid = es.empid
)
INSERT INTO @TREE
SELECT * FROM Employees_Subtree
RETURN
END
GO
/*
deptid deptname deptmgrid empid empname mgrid lvl
1 HR 2 2 Andrew 1 0
1 HR 2 5 Steven 2 1
1 HR 2 6 Michael 2 1
2 Marketing 7 7 Robert 3 0
2 Marketing 7 11 David 7 1
2 Marketing 7 12 Ron 7 1
2 Marketing 7 13 Dan 7 1
2 Marketing 7 14 James 11 2
3 Finance 8 8 Laura 3 0
4 R&D 9 9 Ann 3 0
5 Training 4 4 Margaret 1 0
5 Training 4 10 Ina 4 1
*/
SELECT *
FROM Departments AS D
CROSS APPLY
fn_getsubtree(D.deptmgrid) AS ST
/*
deptid deptname deptmgrid empid empname mgrid lvl
1 HR 2 2 Andrew 1 0
1 HR 2 5 Steven 2 1
1 HR 2 6 Michael 2 1
2 Marketing 7 7 Robert 3 0
2 Marketing 7 11 David 7 1
2 Marketing 7 12 Ron 7 1
2 Marketing 7 13 Dan 7 1
2 Marketing 7 14 James 11 2
3 Finance 8 8 Laura 3 0
4 R&D 9 9 Ann 3 0
5 Training 4 4 Margaret 1 0
5 Training 4 10 Ina 4 1
6 Gardening NULL NULL NULL NULL NULL
*/
SELECT *
FROM Departments AS D
OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST
(18) Transact-SQL Enhancements: New PIVOT and UNPIVOT Operators
·
What is PIVOT?
PIVOT rotates
a table-valued expression by turning the unique values from one column in the expression
into multiple columns in the output, and performs aggregations where they are
required on any remaining column values that are wanted in the final output.
·
T-SQL Pattern for PIVOT:
SELECT <non-pivoted column>,
[first pivoted
column] AS <column name>,
[second pivoted
column] AS <column name>,
...
[last pivoted
column] AS <column name>
FROM
(<SELECT query that produces the
data>)
AS <alias for the
source query>
PIVOT
(
<aggregation
function>(<column being aggregated>)
FOR
[<column that contains the values
that will become column headers>]
IN ( [first
pivoted column], [second pivoted column],
... [last
pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>
· Example:
USE AdventureWorks;
GO
SELECT VendorID,
[164] AS Emp1,
[198] AS Emp2,
[223] AS Emp3,
[231] AS Emp4,
[233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID,
VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
·
What is UNPIVOT?
UNPIVOT
performs the opposite operation to PIVOT by rotating columns of a table-valued
expression into column values.
·
Data sample:
o
Original Pivoted Table
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5
o
Desired unpivoted table
VendorID Employee Orders
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
……
5 Emp5 5
·
T-SQL Pattern for UNPIVOT:
SELECT <non-pivoted column>,
[Unpivoting
the pivoted column] AS <column name>,
[Aggregate] AS <column name>,
FROM
Pivoted
UNPIVOT
(
Aggregation FOR
Collapsed column from the pivoted columns
IN ( [first
pivoted column], [second pivoted column],
... [last
pivoted column])
) AS <alias for the unpivot
table>
<optional ORDER BY clause>
· Example:
CREATE TABLE
pvt (VendorID int, Emp1 int, Emp2 int,
Emp3
int, Emp4 int, Emp5 int);
GO
INSERT INTO
pvt VALUES (1,4,3,5,4,4);
INSERT INTO
pvt VALUES (2,4,1,5,5,5);
INSERT INTO
pvt VALUES (3,4,3,5,4,4);
INSERT INTO
pvt VALUES (4,4,2,5,5,4);
INSERT INTO
pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID,
Employee, Orders
FROM
pvt
UNPIVOT
(Orders FOR
Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO
(19) Transact-SQL Enhancements: Query Notifications
Query notifications allow an application to
cache a result set and request the Database Engine to notify it if any of the
underlying data is modified by another application. Applications using query
notifications do not have to periodically verify the state of the data in the
database. They only have to validate the data if the Database Engine notifies
them of a change.
Query
notifications are a new feature available in SQL Server 2005 and SQL Native Client.
Built upon the Service Broker infrastructure introduced in SQL Server 2005,
query notifications allow applications to be notified when data has changed. This feature is particularly
useful for applications that provide a cache of information from a database,
such as a Web application, and need to be notified when the source data is
changed.
Microsoft SQL Server 2005
introduces query notifications, new functionality that allows an application to
request a notification from SQL Server when the results of a query change. Query notifications allow
programmers to design applications that query the database only when there is a
change to information that the application has previously retrieved.
The Database Engine uses Service Broker to deliver
notification messages. Therefore, Service Broker must be active in the database
where the application requests the subscription. The query notification functionality does not require or
use Notification Services. Query notifications are independent of event
notifications!!!
Note 1: Event notifications execute in
response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace
events by sending information about these events to a Service Broker service.
Note
2: Notification Services is a programming framework for creating applications
that generate and send notifications, as well as a platform for hosting those
applications. Using the programming framework, you can quickly create
applications to generate and send notifications to subscribers. After creating
the application, you can deploy the application on the Notification Services
platform.
The syntax of the query
notifications options string is:
service=<service-name>[;(local
database=<database> | broker instance=<broker instance>)]
For example:
service=mySSBService;local database=mydb
SQL
Native Client applications typically receive notifications by using the
Transact-SQL RECEIVE command to read notifications from the
queue associated with the service specified in the notification options.
To use
query notifications a queue and a service must exist on the server. These can
be created using Transact-SQL similar to the following:
CREATE QUEUE myQueue
CREATE SERVICE myService ON QUEUE myQueue
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])
The
service must use the predefined contract http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification as shown above.
For
more, see https://technet.microsoft.com/en-us/library/ms190270(v=sql.90).aspx
(20) Transact-SQL Enhancements: Bulk Operations on OPENROWSET
OPENROWSET now supports a BULK operation for
bulk copying data from data files. OPENROWSET bulk operations are controlled
using new table hints and bulk options, such as BULK_BATCHSIZE and FORMATFILE.
The new bulk_column_alias clause supports assigning
column names to bulk operation data.
Uses the BULK rowset provider for OPENROWSET to read data from a file. In
SQL Server 2005, OPENROWSET can read from a data file without loading the data
into a target table. This lets you use OPENROWSET with a simple SELECT
statement.
The arguments of the BULK
option allow for significant control over where to start and end reading data,
how to deal with errors, and how data is interpreted. For example, you can
specify that the data file be read as a single-row, single-column rowset of type varbinary, varchar, or nvarchar. The
default behavior is described in the argument descriptions that follow.
For more information, see OPENROWSET
(Transact-SQL), Table
Hint (Transact-SQL).
OPENROWSET ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' } , { [ catalog. ] [ schema. ] object | 'query' } | BULK 'data_file' , { FORMATFILE = 'format_file_path' [ <bulk_options> ] | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }} ) <bulk_options> ::= [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] [ , ERRORFILE = 'file_name' ] [ , FIRSTROW = first_row ] [ , LASTROW = last_row ] [ , MAXERRORS = maximum_errors ] [ , ROWS_PER_BATCH = rows_per_batch ] CREATE TABLE
myTable(
FileName nvarchar(60),
FileType
nvarchar(60),
Document
varbinary(max)
)
GO
INSERT INTO
myTable(FileName, FileType, Document)
SELECT 'Text1.txt'
AS FileName,
'.txt' AS FileType,
* FROM OPENROWSET(BULK N'd:\Text1.txt', SINGLE_BLOB) AS Document
GO
SELECT * from MyTable
--Example 2 - Using OPENROWSET to bulk insert
file data with a format file
/*
The following example uses a format file to
retrieve rows from a tab-delimited text file, values.txt that contains the
following data:
1 Data Item 1
2 Data Item 2
3 Data Item 3
The format file, values.fmt, describes the
columns in values.txt:
9.0
2
1
SQLCHAR 0 10 "\t" 1 ID SQL_Latin1_General_Cp437_BIN
2
SQLCHAR 0 40 "\r\n" 2 Description
SQL_Latin1_General_Cp437_BIN
*/
SELECT a.*
FROM OPENROWSET( BULK 'c:\test\values.txt',
FORMATFILE
= 'c:\test\values.fmt') AS a;
(21) Transact-SQL Enhancements: TOP Enhancements
The TOP operator has been enhanced to take any
numeric expression (such as a variable name) instead of only an integer number
to specify the number of rows returned by the operator. TOP can also now be
specified in INSERT, UPDATE, and DELETE statements.
(22) Transact-SQL Enhancements: Distributed Query
The EXECUTE statement now supports an AT LinkedServer clause that specifies executing a
stored procedure on a linked server. The CONTAINS full-text predicate supports
four-part names to execute queries against linked servers. Distributed queries
also support the new large object data types nvarchar(max),varchar(max),
and varbinary(max). New SQL Trace
events have been added to help analyze the interaction of distributed queries
and OLE DB providers.
/* Using EXECUTE with AT linked_server_name
The following example passes a command string to a remote
server.
It creates a linked server SeattleSales
that points to another instance of
SQL Server and executes a DDL statement (CREATE TABLE) against
that linked server.
*/
EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'
GO
EXECUTE ( 'CREATE TABLE AdventureWorks.dbo.SalesTbl
(SalesID int,
SalesName varchar(10)) ; ' ) AT SeattleSales;
GO
(23) Transact-SQL Enhancements: TABLESAMPLE
The new TABLESAMPLE clause limits the number
of rows processed by a query. Unlike TOP, which returns only the first rows
from a result set, TABLESAMPLE returns rows selected from throughout the set of
rows processed by the query.
/* Example 1 - Selecting a percentage of rows
The Person.Contact table contains
19,972 rows. The following example returns approximately 10 percent of the
rows. The number of rows returned usually changes every time that the statement
is executed.
*/
USE AdventureWorks ;
GO
SELECT FirstName, LastName
FROM Person.Contact
TABLESAMPLE (10 PERCENT) ;
/* Example 2 - Selecting a percentage of rows with a seed
value
The following example returns the same set of rows every time
that it is executed. The seed value of 205 was chosen arbitrarily.
*/
USE AdventureWorks ;
GO
SELECT FirstName, LastName
FROM Person.Contact
TABLESAMPLE (10 PERCENT)
REPEATABLE (205) ;
/* Example 3 - Selecting a number of rows
The following example returns approximately 100 rows. The
actual number of rows that are returned can vary significantly. If you specify
a small number, such as 5, you might not receive results in the sample.
*/
USE AdventureWorks ;
GO
SELECT FirstName, LastName
FROM Person.Contact
TABLESAMPLE (100 ROWS)
(24) Transact-SQL Enhancements: New CASCADE Integrity Constraints
The REFERENCES clause now supports the SET
NULL and SET DEFAULT cascading referential integrity actions. SET NULL
specifies that cascading referential integrity actions set foreign keys to
NULL. SET DEFAULT specifies that foreign keys are set to the default value
defined for the column.
·
[ ON DELETE { NO
ACTION | CASCADE | SET
NULL | SET DEFAULT } ]
·
[ ON UPDATE { NO
ACTION | CASCADE | SET
NULL | SET DEFAULT } ]
(25) Transact-SQL Enhancements: Overflow Data Can Exceed Page Size
Rows can exceed the limit of 8,060 bytes if
they contain variable-length columns using the nvarchar, varchar, varbinary, and sql_variant data
types. If the combined widths of these variable-length columns cause the row to
exceed the page size limit, the Database Engine may move data from
variable-length columns to separate pages.
(26) Database Administration Programming: New SQL Server Management Objects (SMO) API
The SMO object model extends and supersedes
the Distributed Management Objects (DMO) object model. The SMO object model
provides classes for configuring and managing instances of the Database Engine.
The Replication Management Objects (RMO) object model complements the SMO object
model, providing classes for configuring and managing replication.
SQL
Server Management Objects (SMO) is a collection of objects that are designed
for programming all aspects of managing Microsoft SQL Server. It Provides
information about programming the SMO objects in the Microsoft.SqlServer.management
namespaces. You can use SMO to create databases, perform backups, create jobs,
configure SQL Server, assign permissions, and to perform many other
administrative tasks, etc.
·
Microsoft.SqlServer.Management.NotificationServices,
·
Microsoft.SqlServer.Management.Smo,
·
Microsoft.SqlServer.Management.Smo.Agent,
·
Microsoft.SqlServer.Management.Smo.Broker,
·
Microsoft.SqlServer.Management.Smo.Mail, Microsoft.SqlServer.Management.Smo.RegisteredServers, Microsoft.SqlServer.Management.Smo.Wmi,
·
Microsoft.SqlServer.Management.Trace
SQL
Server Replication Management Objects (RMO) is a collection of objects that
encapsulates SQL Server replication management. It provides information about
programming the RMO objects in the Microsoft.SqlServer.Replication
namespace.
(27) Database Administration Programming: New SQL Server WMI Providers
The
WMI Provider for Computer Management is an instance provider. The purpose of this
layer is to provide a unified way for interfacing with the API calls that
manage the registry operations requested by the Server and Client network
utility functionality and that provide enhanced control and manipulation over
the selected SQL services of the Computer Manager snap-in component.
The
WMI Provider for Server Events enables you to use the Windows Management
Instrumentation (WMI) to monitor events in SQL Server. The provider works by
turning SQL Server into a managed WMI object. Any event that can generate an
event notification in SQL Server can be leveraged by the WMI using this
provider. Additionally, as a management application that interacts with the
WMI, SQL Server Agent can respond to these events, increasing the scope of
events covered by SQL Server Agent over previous releases.
