USE tempdb
CREATE TABLE T (IntCol int, XmlCol xml)
GO
INSERT INTO T(XmlCol)
SELECT * FROM OPENROWSET(
   BULK 'c:\SampleFolder\SampleData3.txt',
   SINGLE_BLOB) AS x

<?xml version="1.0" encoding="UTF-8"?>
<Root>
          <ProductDescription ProductModelID="5">
             <Summary>Some Text</Summary>
          </ProductDescription>
</Root>

Run the following Sql codes to create the snapshot

CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\central data\central data\snapshots\data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;

1. can be used to maintain historical data
how does the data look like at  a particular point in time such as the end of every quarter or month which can be used for historical reporting.
2. It serves a read-only mirror of the data
You can offload your reporting task to the snapshot as an availability manuever and free up the main database from intensive read only query
3. safe guard data from administrative error
database can be reverted from snapshot in case of administrative error schema changes, bulk insert and the likes. aside from that you can't delete, drop or detach the

source database without first deleting its snapshots
4. Safe guard data from user error
You can easily recover unwanted user error from the snapshot such as deletion and dropeed tables. Snapshot provides faster recovery as compared from recovering from the backup

1. It should exist on the same server as the source database
2. contains only commited transaction at the point of which it was taken. Uncommited chages are rollback
3. It is read only
4. You cannot drop files from a snapshot.
5. You cannot back up or restore snapshots.
6. You cannot attach or detach snapshots.
7. You cannot create snapshots on FAT32 file system or RAW partitions.
8. Full-text indexing is not supported on database snapshots, and full-text catalogs are not propagated from the source database.
9. A database snapshot inherits the security constraints of its source database at the time of snapshot creation. Because snapshots are read-only, inherited permissions cannot be changed and permission changes made to the source will not be reflected in existing snapshots.
10. A snapshot always reflects the state of filegroups at the time of snapshot creation: online filegroups remain online, and offline filegroups remain offline. For more information, see "Database Snapshots with Offline Filegroups" later in this topic.
11. If a source database becomes RECOVERY_PENDING, its database snapshots may become inaccessible. After the issue on the source database is resolved, however, its snapshots should become available again.
12. Reverting is unsupported for read-only filegroups as well as compressed filegroups. Attempts to revert to either of these types of filegroups fail

BEGIN TRY
    <SQL Statements>
END TRY
BEGIN CATCH TRAN_ABORT
    <SQL Statements>
END CATCH

Prior to sql server 2005 error handling is done by querying @@error after every line of execution. In sql server 2005, try catch block provides a new structure way of handling error similar to those of many development language such as VB, Java and C#

One of  the advantage of try catch block over  its old friend @@error is that it enables transaction abort errors to be captured with no loss of the transaction context

a. constraint violation

USE AdventureWorks

    BEGIN TRY
      UPDATE HumanResources.Employee
      SET Gender = 'X'
      WHERE EmployeeID = 1;
    END TRY
    BEGIN CATCH
      SELECT ERROR_NUMBER(  ) ErrorNumber,
      ERROR_STATE(  ) ErrorState,
      ERROR_SEVERITY(  ) ErrorSeverity,
      ERROR_MESSAGE(  ) ErrorMessage;
    END CATCH

 

b. selecting from non existing table

USE AdventureWorks;
GO

BEGIN TRY
    -- Table does not exist; object name resolution
    -- error not caught.
    SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH

 

c. erroneous sp

-- Verify that the stored procedure does not exist.
IF OBJECT_ID ( N'usp_ExampleProc', N'P' ) IS NOT NULL
    DROP PROCEDURE usp_ExampleProc;
GO

-- Create a stored procedure that will cause an
-- object resolution error.
CREATE PROCEDURE usp_ExampleProc
AS
    SELECT * FROM NonexistentTable;
GO

BEGIN TRY
    EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH;

D. division  by zero

USE AdventureWorks;
GO

BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

 

d. try catch with transaction

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
        WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

 

e. try catch with xact- state

USE AdventureWorks;
GO

-- Check to see if this stored procedure exists.
IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL
    DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_LINE () as ErrorLine,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_MESSAGE() as ErrorMessage;
GO

-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
        -- A foreign key constrain exists on this table. This
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the DELETE statement succeeds, commit the transaction.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;

    -- Test XACT_STATE:
        -- If 1, the transaction is committable.
        -- If -1, the transaction is uncommittable and should
        --     be rolled back.
        -- XACT_STATE = 0 means that there is no transaction and
        --     a COMMIT or ROLLBACK would generate an error.

    -- Test if the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state.' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test if the transaction is committable
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT
            N'The transaction is committable.' +
            'Committing transaction.'
        COMMIT TRANSACTION;  
    END;
END CATCH;
GO

1.  Try catch block can be nested

2.  Only one block is allowed per batch

3.  Catches all execution errors with severity greater than 10 that do not terminate the database connection
4.  A TRY block must be followed immediately by an associated CATCH block. Placing any other statements 

      between the END TRY and BEGIN CATCH statements generates a syntax error.
5.  GOTO statements cannot be used to enter a TRY or CATCH block. GOTO statements can be used to jump to a label within the same TRY or CATCH block or to leave a TRY or CATCH block.
There is no finally block

 

 TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] ) 
 [ REPEATABLE (repeat_seed) ]

Sample TSQL codes:

USE AdventureWorks ;
GO
SELECT *
FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ;

TABLESAMPLE SYSTEM returns an approximate percentage of rows. It generates a random value for each physical 8 KB page in the table. Based on the random value for a page and the percentage specified in the query, a page is included in the sample or excluded. Each page that is included returns all rows in the sample result set. For example, when specifying TABLESAMPLE SYSTEM 10 PERCENT, SQL Server returns all the rows on approximately 10 percent of the specified table's data pages. If the rows are evenly distributed on the pages of the table, and if there is a sufficient number of pages in the table, the number of rows returned should approximate the sample size requested. However, as the random value generated for each page is independent of the values generated for any other page, it is possible that a larger, or smaller, percentage of pages than requested are returned. The TOP(n) operator can be used to limit the number of rows to a given maximum

 

REPEATABLE indicates that a selected sample should be returned again. When specified with the same repeat_seed value, SQL Server returns the same subset of rows as long as no changes have been made to the table. When specified with a different repeat_seed value, SQL Server will likely return a different sample of the rows in the table. The following actions to the table are considered changes: insert, update, delete, index rebuild, index defragmentation, database restore, and database attach.

Synonyms

Synonyms are words with the same meaning. In SQL server 2005 synonyms works like a permanent alias.

Microsoft SQL Server 2005 introduces the concept of a synonym. A synonym is an alternative name for a schema-scoped object. Client applications can use a single-part name to reference a base object by using a synonym instead of using a two-part, three-part, or four-part name to reference the base object.

A synonym is a database object that serves the following purposes:

• It provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
• It provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.

Something to do:

1.  Create a table with a very long name

CREATE TABLE [dbo].[myverylonglonglonglongtablename](

                [id] [int] IDENTITY(1,1) NOT NULL,

                [field1] [nchar](10) COLLATE Latin1_General_CI_AS NULL,

 CONSTRAINT [PK_myverylonglonglonglongtablename] PRIMARY KEY CLUSTERED

(

                [id] ASC

)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

2. insert some thing in the table

declare @x int

select @x=0

while @x<>10

begin

select @x=@x+1

INSERT INTO [AdventureWorks].[dbo].[myverylonglonglonglongtablename]

           ([field1])

     VALUES

           ('hellox')

end

3.  Use select statement on the table with the long name

                Select * from [dbo].[myverylonglonglonglongtablename]

4.  create and use the synonyms

-- Create a synonym for the Product table in myverylonglonglonglongtablename

CREATE SYNONYM shortname

FOR [dbo].[myverylonglonglonglongtablename]

GO

 

-- Query the  table by using the synonym.

 

select * from shortname

USE AdventureWorks ;

GO
DECLARE @p AS int
SET @p='10'
SELECT TOP(@p)*
FROM HumanResources.Employee;
GO
 

DDL trigger

 DDL trigger is a new feature of Sql server 2005.  This feauture can be use to audit DDL statement executions on the server. DDL stands for Data definition language which are composed of alter, create and drop statements.

 Thus DDL trigger as opposed to DML (insert,update,delete) trigger responds only to DDL statement  (e.g  create, alter and drop )

To do:

1. Open Sql serve management studio
2. Connect to your Sql server 2005 database
3. Use the adventurework database and
4. Type and run the following scripts

Use Adventureworks
CREATE TRIGGER safety
ON DATABASE
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE
AS
   PRINT 'You must disable Trigger "safety" to drop, create or alter tables!'
   ROLLBACK ;

5. Test the trigger by running this scripts

create table myContact_table
(
lastname varchar(200),
firstname varchar(200),
middlename varchar(200)
 )

6. Notice that the system relpies with

 You must disable Trigger "safety" to drop, create or alter tables!'

Notes on DDL trigger:

1.  there is no "instead of" DDL trigger
2. DDL triggers fire only in response to DDL events specified by Transact-SQL DDL syntax. System stored procedures that perform DDL-like operations are not supported.
3. DDL triggers can fire in response to a Transact-SQL event processed in the current database, or on the current server. The scope of the trigger depends on the event.
4. DDL triggers do not create the inserted and deleted tables

Introduction to Table Partitioning

In SQL Server 2005

Table partitioning is one of Scalability and maintainability features of SQL Server 2005. With these feature a table can be spread across multiple filegroups. It also allows maintenance (such backup and restore) operations to be performed only on subsets of data (partition) rather than the whole table.

Some partitions in a table can be mapped to a read-only filegroup while other partitions can be mapped to a read/write enabled filegroups.These feature can be used as security implementation to protect subset of sensitive data residing in the same table. Consider a case of a Sales transaction table which contains sales transaction from the present and previous years. Those transactions from the previous years (partition) can be mapped to a readonly filegroup while the present sales transaction can be mapped on a read/write filegroups thereby protecting previous records from being altered irregardless of whoever has logged-in to the system provided he/she did not modify the filegroups readonly attribute

Although the data can be spread across multiple files and filegroups it is still treated as a single table in the datbase

1. A partition function - which defines how the rows of a table or index are mapped to a set of partitions based on the values of certain columns, called partitioning columns.

2. A partition scheme – which maps each partition specified by the partition function to a filegroup.

            One of the new feature of SQL server 2005 is the CLR integration.  This feature allows the use .NET base classes such as the use of arrays  to expand SQL Server functionality. This feature is somewhat similar to extended stored procedures in earlier SQL Server versions.