curso de diseño de datos en MsSQL en México

Sample Database Enhancements
Transact-SQL Data Types
Database Console Commands (DBCC) Statements

New Statements for CLR Integration

New statements	Description
CREATE ASSEMBLY	Registers a .NET Framework assembly as an object inside an instance of SQL Server 2005, against which common language runtime (CLR) functions, stored procedures, triggers, user-defined aggregates, and user-defined types can be created in SQL Server.
ALTER ASSEMBLY	Alters an assembly by modifying its properties and adding or removing files associated with it.
DROP ASSEMBLY	Removes an assembly and all its associated files from the current database.
CREATE AGGREGATE	Creates a user-defined aggregate function in SQL Server whose implementation is defined in a class of an assembly in the .NET Framework.
DROP AGGREGATE	Removes a user-defined aggregate function from the current database.
CREATE TYPE	Creates an alias data type in SQL Server, or a CLR user-defined type in SQL Server whose implementation is defined in a class of an assembly in the .NET Framework.
DROP TYPE	Removes an alias data type or CLR user-defined type from the current database.
EXECUTE AS	Controls which user account SQL Server uses to validate permissions on any database objects that are referenced by a stored procedure or user-defined function.

Enhanced statements

Description

ALTER DATABASE

Adds the following options:

ALLOW_SNAPSHOT_ISOLATION
DB_CHAINING
Database mirroring
DATE_CORRELATION_OPTIMIZATION
EMERGENCY (database state option)
PAGE_VERIFY (CHECKSUM or TORN_PAGE_DETECTION)
PARAMETERIZATION
READ_COMMITTED_SNAPSHOT
Service Broker
TRUSTWORTHY

FILEGROWTH default increment for data files changes from 10 percent to 1 MB.

Instant file initialization when adding or growing data files.

CREATE DATABASE

Adds the following items:

DATABASE SNAPSHOT syntax
ATTACH_REBUILD_LOG clause
DB_CHAINING option
Service Broker options
TRUSTWORTHY option

FILEGROWTH default increment for data files changes from 10 percent to 1 MB.

Instant file initialization.

DROP DATABASE

Adds support for dropping database snapshots.

New and Enhanced DDL Statements

New statements	Description
ALTER INDEX	Modifies an existing table or view index (relational or XML) by disabling, rebuilding, or reorganizing the index; or by setting options on the index.

Enhanced statements

Description

CREATE INDEX

Includes the following new syntax elements:

XML INDEX
INCLUDED (column)
PARTITION SCHEME

Adds the following index options:

ONLINE
ALLOW_ROW_LOCKS
ALLOW_PAGE_LOCKS
MAXDOP

DROP INDEX

Adds new format for specifying index and table names.

Adds the MOVE TO clause. This enables moving data from a clustered index to another filegroup or partition scheme.

Adds the following index options:

ONLINE
MAXDOP

Enhanced Statements for CLR Integration

Enhanced statements	Description
CREATE PROCEDURE	Enables creating CLR procedures. Adds EXECUTE AS clause to specify the context in which the stored procedure is executed.
ALTER PROCEDURE	Adds EXECUTE AS clause to specify the context in which the stored procedure is executed.
CREATE FUNCTION	Enables creating CLR functions. Adds EXECUTE AS clause to specify the context in which the function is executed.
ALTER FUNCTION	Adds EXECUTE AS clause to specify the context in which the function is executed.
CREATE TRIGGER	Enables creating CLR trigge

Enhanced DDL Statements

Enhanced statements

Description

CREATE TABLE

Allows for the following:

Creating persisted computed columns.
Using SET NULL and SET DEFAULT with ON UPDATE and ON DELETE clauses.
Specifying max for storing large amounts of varchar, nvarchar, and varbinary data.
Creating xml columns, with option to type the column to an XML schema.

ALTER TABLE

Allows for the following:

Creating persisted computed columns.
Using SET NULL and SET DEFAULT with ON UPDATE and ON DELETE clauses.
Specifying max for storing large amounts of varchar, nvarchar, and varbinary data.
Creating xml columns, with option to type the column to an XML schema.

Enhanced DML Statements and Clauses

Statement or clause	Description
SELECT	Provides the following new syntax items: WITH common table expression TOP expression User-defined type column name for selecting a user-defined type value
INSERT	Provides the following new syntax items: WITH common table expression TOP expression OUTPUT clause Allows for the insertion of user-defined type values.
UPDATE	Provides the following new syntax items: WITH common table expression TOP expression OUTPUT clause User-defined type column name for updating a user-defined type value .WRITE clause
DELETE	Provides the following new syntax items: WITH common table expression TOP expression OUTPUT clause
FOR Clause	Provides the following new FOR XML directives: TYPE enables the retrieval of query results as an xml type. XMLSCHEMA enables requests of an XSD schema. Allows for nested FOR XML queries.
FROM	Provides the following additional table-source syntax: APPLY PIVOT/UNPIVOT TABLESAMPLE Bulk column alias
TOP	Can be used with SELECT, INSERT, UPDATE, and DELETE statements.

Data Manipulation Language Statements
Metadata
Replication System Stored Procedures
Other Statements

Signals the end of a batch of Transact-SQL statements to the SQL Server utilities.

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.

Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

USE AdventureWorks;
GO
DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'
GO -- @MyMsg is not valid after this GO ends the batch.

USE AdventureWorks;
GO
DECLARE @NmbrContacts int
SELECT @NmbrContacts = COUNT(*)
FROM Person.Contact
PRINT 'The number of contacts as of ' +
CAST(GETDATE() AS char(20)) + ' is ' +
CAST(@NmbrContacts AS char (10))
GO

Curso de diseño de base de datos en MsSQL

curso de diseño de base de datos en MsSQL en México

84000.com.mx [ capacitación ]

Temario del curso de diseño de base de datos en MsSQL