How to View XML Schema Information

You can view information about the schema collections in a database by querying the sys.xml_schema_collections catalog
view, as shown in the following example.

SELECT * FROM sys.xml_schema_collections

You can also retrieve the individual XML namespaces defined in the database’s schema collections by querying the
sys.xml_schema_namespaces catalog view, as shown in the following example.

SELECT * FROM sys.xml_schema_namespaces

You can view the XML components defined in the database by querying the sys.xml_schema_components catalog view, as
shown in the following example.

SELECT * FROM sys.xml_schema_components

Implementing Managed Code in the Database

Importing and Configuring Assemblies



-- Enable CLR integration
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- Register assembly
USE AdventureWorks

CREATE ASSEMBLY Utilities
FROM 'D:\Democode\Utilities.dll'
WITH PERMISSION_SET = Safe
GO

-- Drop assembly
DROP ASSEMBLY Utilities
GO

Creating Managed Database Objects

-- Create the managed user-defined function
Use AdventureWorks
GO
CREATE FUNCTION dbo.GetOSVersion()
RETURNS NVARCHAR(50)
AS EXTERNAL NAME Utilities.UserDefinedFunctions.GetOSVersion
GO

-- Test the managed user-defined function
SELECT dbo.GetOSVersion()
GO

-- Create managed user-defined type
CREATE TYPE Point
EXTERNAL NAME Utilities.Point
GO

-- Test the managed user-defined type
DECLARE @P Point
SET @P = '1,5'
SELECT @P.X AS X, @P.Y AS Y
GO

-- Create table using managed user-defined type
CREATE TABLE dbo.Points
(ID int IDENTITY(1,1) PRIMARY KEY, PointValue Point)
GO

INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4'));
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,5'));
INSERT INTO dbo.Points (PointValue) VALUES (CAST ('1,99' AS Point));

SELECT ID,
PointValue.X AS X,
PointValue.Y AS Y,
PointValue.ToString() AS String
FROM dbo.Points
GO

-- Clean up
DROP TABLE dbo.Points
DROP FUNCTION dbo.GetOSVersion
DROP TYPE Point
DROP ASSEMBLY Utilities
GO

Functions in SQL Server 2005

--Scalar Function

CREATE FUNCTION Sales.SumSold(@ProductID int) RETURNS int
AS
BEGIN
DECLARE @ret int
SELECT @ret = SUM(OrderQty)
FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID
IF (@ret IS NULL)
SET @ret = 0
RETURN @ret
END
---
SELECT ProductID, Name, Sales.SumSold(ProductID) AS SumSold
FROM Production.Product
-----------------------------
-- Inline Table-Valued Function

CREATE FUNCTION HumanResources.EmployeesForManager
(@ManagerId int)
RETURNS TABLE
AS
RETURN (
SELECT FirstName, LastName
FROM HumanResources.Employee Employee INNER JOIN
Person.Contact Contact
ON Employee.ContactID = Contact.ContactID
WHERE ManagerID = @ManagerId )
---
SELECT * FROM HumanResources.EmployeesForManager(3)
-- OR
SELECT * FROM HumanResources.EmployeesForManager(6)
-----------------------------
-- Multi-Statement Table-Valued Function

CREATE FUNCTION HumanResources.EmployeeNames
(@format nvarchar(9))
RETURNS @tbl_Employees TABLE
(EmployeeID int PRIMARY KEY, [Employee Name] nvarchar(100))
AS
BEGIN
IF (@format = 'SHORTNAME')
INSERT @tbl_Employees
SELECT EmployeeID, LastName FROM HumanResources.vEmployee
ELSE IF (@format = 'LONGNAME')
INSERT @tbl_Employees
SELECT EmployeeID, (FirstName + ' ' + LastName)
FROM HumanResources.vEmployee
RETURN
END
----
SELECT * FROM HumanResources.EmployeeNames('LONGNAME')

Using XML in SQL server 2005

use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR xml raw
----------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR xml raw,elements
-------------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR XML RAW('Order'), ROOT('Orders')
---------------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR XMl auto
---------------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR XMl auto, elements
---------------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR XML AUTO, ELEMENTS, ROOT('Orders')
-----------------------
use Northwind

use AdventureWorks SELECT EmployeeID "@EmpID", FirstName "EmpName/First", LastName "EmpName/Last" FROM Person.Contact INNER JOIN HumanResources.Employee ON Person.Contact.ContactID = Employee.ContactID FOR XML PATH
-----------------------
use AdventureWorks

SELECT EmployeeID "@EmpID", FirstName "EmpName/First", LastName "EmpName/Last" FROM Person.Contact INNER JOIN HumanResources.Employee ON Person.Contact.ContactID = Employee.ContactID FOR XML PATH('Employee')
-----------------------

hints about mirroring sql server

SQL 2005 Database Mirroring Tutorial (Without Domain)
http://alan328.com/SQL2005_Database_Mirroring_Tutorial.aspx
------------
http://msdn.microsoft.com/en-us/ms189127.aspx
----------
http://wiki.cdyne.com/wiki/index.php?title=Mirroring_SQL_Server_2005
----------
mirror steps
0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR
1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.
2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.
3) MRRUSR is the MSSQLSERVER service account in both machines
4) Machine A is gonna be the PRINCIPAL, B the MIRROR.
5) Log on in my machine as MRRUSR
6) Open Sql server Studio. Connect to both machines using windows authenticacion.
7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data
8) Backup the new DB (Full backup) with .bak extension
9) Backup the new DB (Transaction log) with .trn extension
10) Copy the both files to a location in B machine.
11) Restore .bak into a new DB using NORECOVERY option
12) Restore .trn into the previous DB using NORECOVERY option
13) Go to Machine A, open a new query
14) Take a look to the endpoints
SELECT type_desc, port FROM sys.tcp_endpoints;SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])
16) Create a new endopoint
CREATE ENDPOINT [Mirroring] AS TCP (LISTENER_PORT = 5023)FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)
17) Start it
ALTER ENDPOINT [Mirroring]STATE = STARTED AS TCP (LISTENER_PORT = 5023)FOR database_mirroring (ROLE = PARTNER);GO

18) Do steps 13 to 17 in machine B.
19) Return to machine A. Query.
20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.
ALTER DATABASE NAMESET PARTNER ='TCP://MACHINENAME:5023'
21) Repeat step 20 from MACHINE B pointing to MACHINE A
22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.
23) Lets test them: Add some new data in Machine A database.
24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.
25) Refresh the 2 databases in the UI you should see the new status and roles changed.
26) Open Machine B databse, the new data should be there.
----------------
http://social.msdn.microsoft.com/forums/en-US/sqldatabasemirroring/thread/73fb15c0-9270-4cbf-a74e-544639e792da/
-----------------

New in SQL Server 2008

--Compound Operators

DECLARE @NoOfIterations int
SET @NoOfIterations = 0
- - Do something
SET @NoOfIterations += 1
-------------------------------
--DECLARE Statement

DECLARE @NoOfIterations int = 0
-------------------------------
--GROUPING SETS

use AdventureWorks2008
-- Using UNION ALL
SELECT CustomerID, NULL AS Year, SUM(SubTotal)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
UNION ALL
SELECT NULL AS CustomerID, YEAR(OrderDate), SUM(SubTotal)
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
-- Using GROUPING SETS
SELECT CustomerID, YEAR(OrderDate), (SubTotal)
FROM Sales.SalesOrderHeader
GROUP BY GROUPING SETS ((CustomerID), (YEAR(OrderDate)))
------------------------------------
--Row Constructors

INSERT INTO Person.CountryRegion
VALUES ('UK', 'United Kingdom', SYSDATETIME()),
('GB', 'Great Britain', SYSDATETIME())
-------------------------------------
--datetime2 new data type

• Key features: larger and more accurate than datetime. Contains a new
SYSUTCDATETIME function that returns the Coordinated Universal Time
(UTC) date and time, as opposed to the system date and time.
• Range: from 0001/01/01 00:00:00:0000000 to 9999/31/12 23:59:59:9999999.
• Accuracy: 100 nanoseconds.

DECLARE @newDateTime datetime2
SET @newDateTime = SYSDATETIME()
PRINT @newDateTime
-- OUTPUT
-- 2008-02-24 20:50:46.7892464
--------------------------------------
--date new data type

• Key features: consists of the date portion of datetime2.
• Range: from 0001/01/01 to 9999/31/12.
• Accuracy: one day.

The following code example uses the date data type.

DECLARE @newDate date
SET @newDate = SYSDATETIME()
PRINT @newDate
-- OUTPUT
-- 2008-02-24
---------------------------------------
time new data type

• Key features: consists of the time portion of datetime2.
• Range: from 00:00:00:0000000 to 23:59:59:9999999.
• Precision: variable precision, defined when you declare the variable.
• Accuracy: 100 nanoseconds.

The following code example uses the time data type.

DECLARE @newTime time(3)
SET @newTime = SYSDATETIME()
PRINT @newTime
-- OUTPUT
-- 20:50:46.789
---------------------------------------
datetimeoffset new data type

• Key features: similar to datetime2, but includes a time-zone offset. Time-zone
comparisons are performed against UTC and you use the SWITCHOFFSET
function to modify the time-zone offset and preserve the actual time.
The following code example uses the datetimeoffset data type.
DECLARE @newDateTime datetimeoffset
SET @newDateTime = SYSDATETIMEOFFSET()
PRINT @newDateTime
-- OUTPUT
-- 2008-01-24 20:50:46.7892464 -08:00
----------------------------------------
The User-Defined Table Data Type

CREATE TYPE ProductionHistory AS TABLE
(TransactionID int,
-- Remaining column definitions here
)
CREATE PROCEDURE usp_ArchiveHistory
@PH ProductionHistory ReadOnly
AS -– Remaining SP definition here
DECLARE @PH AS ProductionHistory
INSERT INTO @PH –- Populate table here
EXEC usp_ArchiveHistory @PH;
GO

-------------------------------------------
The hierarchyid Data Type

CREATE TABLE OrgTable
(
OrgNode hierarchyid, EmpID int, EmpName varchar(50)
)
INSERT INTO OrgTable
VALUES (
hierarchyid::GetRoot(), 0, 'Darren')
DECLARE @NodeLevel hierarchyid
SELECT @NodeLevel = OrgNode FROM OrgTable WHERE EmpName = 'Darren'
DECLARE @Manager hierarchyid
SELECT @Manager = max(OrgNode) FROM OrgTable
WHERE OrgNode.GetAncestor(1) = @NodeLevel
INSERT INTO OrgTable
VALUES (@NodeLevel.GetDescendant(@Manager, null), 1,
'Dominic')
SELECT OrgNode.ToString(), OrgNode, EmpID, EmpName
FROM OrgTable
----------------------------------------------
FILESTREAM Data

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

• 0 disables FILESTREAM support for this instance.
• 1 enables FILESTREAM for Transact-SQL access.
• 2 enables FILESTREAM for Transact-SQL and Win32 streaming access.
CREATE TABLE ResumeData
(
ApplicantID int,
Resume varbinary(max) FILESTREAM,
ResumeID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE
)
INSERT INTO ResumeData
VALUES
(
1, CAST('Resume Text' AS varbinary(max)), newid()
)

SQL 2008 : Evaluate and apply policies to a server group

1. Create and test a policy on one server as described in the preceding topics.

2. In Object Explorer, expand Management, expand Policy Management, expand Policies, right-click the policy that you want to use, and then click Export Policy.

3. In the Export Policy dialog box, navigate to an appropriate location for the file, and then click Save.

4. In the Registered Servers window, expand Central Management Servers, expand the Central Management Server that controls your group, right-click your group, and then click Evaluate Policies.

5. In the Evaluate Policies dialog box, next to the Source box, click the ellipsis.

6. In the Select Source dialog box, next to the Files box, click the ellipsis.

7. Navigate to the location of your exported policy file, click the file, and then click Open.

8. In the Select Source dialog box, click OK.

9. In the Evaluate Policies dialog box, click Evaluate.

10. If any servers are not compliant and you want to enforce the policy, in the Target details list, select the server check boxes for the noncompliant server, and then click Apply.

11. In the Policy Evaluation Warning message box, click Yes. 12. In the Evaluate Policies dialog box, click Close.

SQL 2008 : Policy-Based Management

You can use policies to help you to manage one or more instances of SQL Server. You define a policy that you want to apply to objects on an instance of SQL Server, and then you can enforce that policy on one or more instances of SQL Server. Policies can range from disabling SQL Mail, through enforcing naming conventions, through restricting file growth for large data files.

Examples of Policies

The following list describes some business scenarios that can be solved by using policies:

- Many companies like to use standard naming conventions for the objects in their databases. You can create policies that define what the naming convention should be for each particular object; for example, the name of a stored procedure should begin with usp_.

- By default, the local administrators group on a server is a member of the sysadmin SQL Server role. However, in a large enterprise, it is unlikely that system administrators are responsible for database administration, so they should really be removed from the role. This task is simple, but it is equally simple for someone else to add them back into the role at a later stage. To avoid this, you can create a policy that denies access to the BUILTIN\Administrators group, evaluate the policy against your SQL Server instances, and then force any noncompliant servers to deny access to the group. You can also configure the policy to roll back any future attempts to provide access for the group.

- A new corporate policy may define that all SQL Server instances should be using Windows authentication. To manually check the authentication mode of every SQL Server instance and then possibly change it to adhere to the new policy would be a tedious process. Instead, you can create a policy that defines that LoginMode should be set to Integrated, evaluate the policy against the servers, and then enforce the policy on any noncompliant servers.

You can use the Policy Management section of SQL Server Management Studio to create conditions and policies. You must create a condition before you create the policy that uses the condition.

Note: Users must belong to the PolicyAdministratorRole role in the msdb database to create conditions and policies.

Create a policy condition

1. In Object Explorer, expand Management, expand Policy Management, right-click Conditions, and then click New Condition.

2. In the Create New Condition dialog box, on the General page, in the Name box, type a name for the condition.

3. In the Facet box, click the facet that you want your condition to use.

4. In the Expression pane, select the attribute and value for which you want your condition to test, and then click OK.

Create a policy that belongs to a new category

1. In Object Explorer, right-click the Policies folder, and then click New Policy.

2. In the Create New Policy dialog box, on the General page, in the Name box, type a name for the policy.

3. In the Check condition box, click the condition that you previously created.

4. In the Evaluation Mode box, click an evaluation mode for the policy.

5. If you are using the On Schedule evaluation mode, configure a schedule and ensure that the SQL Server Agent service is running.

6. If you are using an automated evaluation mode, select the Enabled check box.

7. In the Create New Policy dialog box, click the Description page.

8. Next to the Category box, click New.

9. In the Create new category dialog box, type a name for the new category, and then click OK.

10. In the Create New Policy dialog box, click OK.

Set the Mandate Database Subscriptions option

1. In Object Explorer, right-click the Policy Management folder, and then click Manage Categories.

2. In the Manage Policy Categories dialog box, in the Mandate Database Subscriptions column, select the check box for the relevant category, and then click OK.

Subscribe an object to a policy category

1. In Object Explorer, right-click the object that you want to subscribe, point to Policies, and then click Categories.

2. In the Categories dialog box, expand the category to which you want to subscribe.

3. Select the Subscribed check box for the category, and then click OK.

Evaluate a policy from a policy

1. In Object Explorer, expand Management, expand Policy Management, expand Policies, right-click a policy, and then click Evaluate.

2. On the Evaluate Results page, examine the results of the policy evaluation.

Evaluate a policy from an object

1. In Object Explorer, right-click a target (for example, a server instance, a database, or a database object), point to Policies, and then click Evaluate.

2. In the Evaluate Policies dialog box, select one or more policies, and then click Evaluate to run the policy in evaluation mode.

3. On the Evaluate Results page, examine the results of the policy evaluation.

View evaluation history

• In Object Explorer, right-click a policy, and then click View History.

Apply a policy

1. In Object Explorer, expand Management, expand Policy Management, expand Policies, right-click a policy, and then click Evaluate.

2. When the policy evaluation is complete, select any noncompliant target, and then click Apply.

3. In the Policy Evaluation Warning message box, click Yes.

4. In the Evaluate Policies dialog box, click Close.

SQL Server 2008 : Use Central Management Servers

1. Open SQL Server Management Studio and connect to the MIAMI database engine instance.

2. Open the Registered Servers window and register a new Central Management Server by using the MIAMI instance of SQL Server.

3. Expand Central Management Servers, right-click MIAMI, and then create a new server group named Miami Management.

4. Add MIAMI\SQLINSTANCE2 and MIAMI\SQLINSTANCE3 to the Miami Management group.

5. Right-click Miami Management, and then click New Query.

6. Note that the status bar shows that you are connected to the two instances.

7. In the Query Editor, type the following code, and then click Execute.

SELECT * FROM sysusers

8. Note that the query returns users from both MIAMI\SQLINSTANCE2 and MIAMI\SQLINSTANCE3.