Export (0) Print
Expand All

Figure 1 Variant Data Type Properties


DECLARE @my_chr sql_variant, @my_dec DECIMAL(5,4)
SET @my_chr = '3.142'
SET @my_dec = 3.142

SELECT    SQL_VARIANT_PROPERTY(@my_chr,'BaseType') AS VarBaseType,
    SQL_VARIANT_PROPERTY(@my_chr,'Precision') AS VarPrecision,
    SQL_VARIANT_PROPERTY(@my_chr,'Scale') AS VarScale,
    SQL_VARIANT_PROPERTY(@my_chr,'TotalBytes') AS VarTotalBytes,
    SQL_VARIANT_PROPERTY(@my_chr,'Collation') AS VarCollation,
    SQL_VARIANT_PROPERTY(@my_chr,'MaxLength') AS VarMaxLength
UNION
SELECT    SQL_VARIANT_PROPERTY(@my_dec,'BaseType'),
    SQL_VARIANT_PROPERTY(@my_dec,'Precision'),
    SQL_VARIANT_PROPERTY(@my_dec,'Scale'),
    SQL_VARIANT_PROPERTY(@my_dec,'TotalBytes'),
    SQL_VARIANT_PROPERTY(@my_dec,'Collation'),
    SQL_VARIANT_PROPERTY(@my_dec,'MaxLength')

GO

VarBaseType  VarPrecision  VarScale  VarTotalBytes  VarCollation                 VarMaxLength  
-----------  ------------  --------  -------------  ---------------------------  ------------
varchar       0             0         13            SQL_Latin1_General_CP1_CI_AS  5
decimal       5             4         9             NULL                          5
Figure 2 XML Document Example

<ROOT>
<Region RegionId="5" RegionDescription="Canada">
   <Territories RegionId="5" TerritoryId="10001" TerritoryDescription="British Columbia"/>
   <Territories RegionId="5" TerritoryId="10002" TerritoryDescription="Alberta"/>
   <Territories RegionId="5" TerritoryId="10003" TerritoryDescription="Saskatchewan"/>
   <Territories RegionId="5" TerritoryId="10004" TerritoryDescription="Manitoba"/>
   <Territories RegionId="5" TerritoryId="10005" TerritoryDescription="Ontario"/>
   <Territories RegionId="5" TerritoryId="10006" TerritoryDescription="Quebec"/>
   <Territories RegionId="5" TerritoryId="10007" TerritoryDescription="Newfoundland"/>
   <Territories RegionId="5" TerritoryId="10008" TerritoryDescription="Nova Scotia"/>
   <Territories RegionId="5" TerritoryId="10009" TerritoryDescription="New Brunswick"/>
   <Territories RegionId="5" TerritoryId="10010" TerritoryDescription="Prince Edward Island"/>
</Region>
<Region RegionId="6" RegionDescription="United Kingdom">
   <Territories RegionId="6" TerritoryId="20001" TerritoryDescription="England"/>
   <Territories RegionId="6" TerritoryId="20002" TerritoryDescription="Scotland"/>
   <Territories RegionId="6" TerritoryId="20003" TerritoryDescription="Wales"/>
   <Territories RegionId="6" TerritoryId="20004" TerritoryDescription="Northern Ireland"/>
</Region>
</ROOT>
Figure 3 XML Stored Procedure Parser

CREATE PROCEDURE dbo.usp_xml_regionterritory (@xmldoc NTEXT)
AS
BEGIN

    DECLARE @idoc    INT

    DECLARE @territory TABLE (
        region_id    INT,
        region_desc    NVARCHAR(50),
        territory_id     INT,
        territory_desc     NVARCHAR(50)
    )

    --Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc

    -- SELECT statement using OpenXML rowset provider
    INSERT INTO @territory
    SELECT * FROM OpenXML (@idoc, '/ROOT/Region/Territories', 0) WITH (
        RegionId          INT          '../@RegionId',
        RegionDesc        NVARCHAR(50) '../@RegionDescription',
        TerrorityId       INT          '@TerritoryId',
        TerritoryDesc     NVARCHAR(50) '@TerritoryDescription')

    -- remove the XML document from memory
    EXEC sp_xml_removedocument @idoc

    -- Create the new regions
    INSERT INTO Region
    SELECT DISTINCT region_id, region_desc
    FROM @territory

    -- Create the new territories
    INSERT INTO Territories
    SELECT DISTINCT territory_id, territory_desc, region_id
    FROM @territory

END
GO
Figure 4 Sales by Year User-defined Function

CREATE FUNCTION dbo.ProductSalesByYear (@year INT = NULL)
RETURNS @Categories TABLE (
    CategoryID    INT,
    CategoryName    NVARCHAR(15),
    ProductID    INT,
    ProductName    NVARCHAR(40),
    ProductSales    MONEY
)
AS
BEGIN
    IF @year IS NULL
        SET @year = (SELECT MAX(DATEPART(year, ShippedDate)) FROM Orders)
    
    INSERT INTO @Categories
    SELECT Categories.CategoryID, Categories.CategoryName,
    Products.ProductID, Products.ProductName, 
    SUM(CONVERT(MONEY,([Order Details].UnitPrice*Quantity*(1-Discount)/100))*100)
    AS ProductSales
    FROM (Categories
    INNER JOIN Products
        ON Categories.CategoryID = Products.CategoryID) 
    INNER JOIN (Orders
    INNER JOIN [Order Details]
        ON Orders.OrderID = [Order Details].OrderID) 
        ON Products.ProductID = [Order Details].ProductID
    WHERE (DATEPART(year, Orders.ShippedDate) = @year)
    GROUP BY Categories.CategoryID, Categories.CategoryName,
        Products.ProductID, Products.ProductName
 
    RETURN
END
GO
Figure 5 Sales for Specified Date User-defined Function

CREATE FUNCTION dbo.SalesForSpecifiedDate (@date DATETIME)
RETURNS INT 
AS
BEGIN
    DECLARE @ordercount INT
    DECLARE @startdate DATETIME, @enddate DATETIME
    DECLARE @txtdate VARCHAR(12)

    SET @txtdate = CAST(
        DATENAME(year, @date) + '/' +
        CAST(DATEPART(month, @date) AS VARCHAR(2)) + '/' +
        DATENAME(day, @date) AS DATETIME
    )
    SET @startdate = CAST(@txtdate AS DATETIME)
    SET @enddate = CAST(@txtdate AS DATETIME) + 1

    SET @ordercount = (SELECT COUNT(*) FROM Orders
        WHERE OrderDate >= @startdate AND OrderDate < @enddate)

    RETURN (@ordercount)
END
GO
Figure 6 INSTEAD OF Insert Trigger

CREATE TRIGGER dbo.trg_region_ins ON Region
    INSTEAD OF INSERT
AS
BEGIN
    -- Process records that require an update
    UPDATE Region SET
    Region.RegionDescription = inserted.RegionDescription
    FROM Region INNER JOIN inserted
    ON Region.RegionID = inserted.RegionID

    -- Process records that require an insert
    INSERT INTO Region
    SELECT inserted.*
    FROM inserted LEFT JOIN Region
    ON inserted.RegionID = Region.RegionID
    WHERE Region.RegionID IS NULL

END
GO

CREATE TRIGGER dbo.trg_territories_ins ON Territories
    INSTEAD OF INSERT
AS
BEGIN
    -- Process records that require an update
    UPDATE Territories SET
    Territories.TerritoryDescription = inserted.TerritoryDescription,
    Territories.RegionID = inserted.RegionID
    FROM Territories INNER JOIN inserted
    ON Territories.TerritoryID = inserted.TerritoryID

    -- Process records that require an insert
    INSERT INTO Territories
    SELECT inserted.*
    FROM inserted LEFT JOIN Territories
    ON inserted.TerritoryID = Territories.TerritoryID
    WHERE Territories.TerritoryID IS NULL

END
GO
Figure 7 Updateable View Trigger

CREATE TRIGGER dbo.trg_employeename_update ON EmployeeName
    INSTEAD OF UPDATE
AS
BEGIN
    UPDATE Employees SET
    FirstName = SUBSTRING(inserted.EmployeeName, 1,
        (CHARINDEX(' ', inserted.Employeename) - 1)),
    LastName = SUBSTRING(inserted.EmployeeName,
        (CHARINDEX(' ', inserted.EmployeeName) + 1), DATALENGTH(inserted.EmployeeName))
    FROM Employees INNER JOIN inserted
    ON Employees.EmployeeId = inserted.EmployeeId
END
GO
Figure 8 Group By Indexed View

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF 
GO 
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,
QUOTED_IDENTIFIER,ANSI_NULLS ON
GO

-- Create aggregation view for order totals
CREATE VIEW  dbo.OrderSubTotals
WITH SCHEMABINDING
AS
    SELECT OrderID,
        SUM(CONVERT(MONEY,(UnitPrice*Quantity*(1-Discount)/100))*100)
        AS SubTotal,
        COUNT_BIG(*) AS DetailCount
    FROM dbo.[Order Details]
    GROUP BY OrderID
GO
Figure 9 Data Partition Definition

-- Define the first partition
USE NorthwindOrd1
GO

-- Create order details table
CREATE TABLE dbo.OrderDetails (
    OrderID int NOT NULL ,
    CustomerID nchar (5) NOT NULL ,
    ProductID int NOT NULL ,
    UnitPrice money NOT NULL ,
    Quantity smallint NOT NULL ,
    Discount numeric(3, 2) NOT NULL,
    CONSTRAINT PK_Order_Details_OI_CI_PI PRIMARY KEY (CustomerID, 
               OrderID, ProductID),
    CONSTRAINT CHK_Order_Details_CI CHECK (CustomerID < 'N')
)
GO

-- Create customers table
CREATE TABLE dbo.Customers (
    CustomerID nchar (5) NOT NULL,
    CompanyName nvarchar (40) NOT NULL ,
    ContactName nvarchar (30) NULL ,
    ContactTitle nvarchar (30) NULL ,
    Address nvarchar (60) NULL ,
    City nvarchar (15) NULL ,
    Region nvarchar (15) NULL ,
    PostalCode nvarchar (10) NULL ,
    Country nvarchar (15) NULL ,
    Phone nvarchar (24) NULL ,
    Fax nvarchar (24) NULL,
    CONSTRAINT PK_Customers_CI PRIMARY KEY (CustomerID),
    CONSTRAINT CHK_Customers_CI CHECK (CustomerID < 'N') 
)
GO

--Create orders table
CREATE TABLE dbo.Orders (
    CustomerID nchar (5) NOT NULL ,
    OrderID int NOT NULL ,
    EmployeeID int NULL ,
    OrderDate datetime NULL ,
    RequiredDate datetime NULL ,
    ShippedDate datetime NULL ,
    ShipVia int NULL ,
    Freight money NULL ,
    ShipName nvarchar (40) NULL ,
    ShipAddress nvarchar (60) NULL ,
    ShipCity nvarchar (15) NULL ,
    ShipRegion nvarchar (15) NULL ,
    ShipPostalCode nvarchar (10) NULL ,
    ShipCountry nvarchar (15) NULL,
    CONSTRAINT PK_Orders_OI_CI PRIMARY KEY (CustomerID, OrderID),
    CONSTRAINT CHK_Orders_CI CHECK (CustomerID < 'N')
)
GO
Figure 10 Extended Properties Display

Objtype    objname       name    value
-------    -------       ----    -----
COLUMN     HomePhone     Mask    (NNN)NNN-NNNN
COLUMN     PostalCode    Mask    99999[-9999]
Figure 11 Extended Property Levels
Level 0
Level 1
Level 2
User
Table
Column, index, constraint, trigger
View
Column, index, INSTEAD OF trigger
Stored procedure
Parameter
Rule
(none)
Default
(none)
Function
Column, parameter, constraint
User-defined data type
(none)
(none)
Figure 12 Table INSERT Scope Sample

CREATE TABLE FullNames (
   NameId    INT IDENTITY(1,1) PRIMARY KEY,
   FullName    VARCHAR(50) NOT NULL
)
CREATE TABLE LastNames (
   NameId    INT IDENTITY(100,1) PRIMARY KEY,
   LastName    VARCHAR(50) NOT NULL
)
GO

CREATE TRIGGER names_lastname_ins ON FullNames
FOR INSERT
AS
BEGIN
    INSERT INTO LastNames
    SELECT DISTINCT SUBSTRING(inserted.FullName, (CHARINDEX(' ', inserted.FullName) + 1),
        DATALENGTH(inserted.FullName))
    FROM inserted
    WHERE SUBSTRING(inserted.FullName, (CHARINDEX(' ', inserted.FullName)+ 1),
        DATALENGTH(inserted.FullName))
        NOT IN (SELECT LastName FROM LastNames)
END
GO
Figure 13 SELECT ... FOR XML Output

<!--Data returned using RAW clause-->
<row CustomerID="GROSR" OrderId="10268" ProductID="29" UnitPrice="99" Quantity="10"/>
<row CustomerID="GROSR" OrderId="10268" ProductID="72" UnitPrice="27.8" Quantity="4"/>
<row CustomerID="GROSR" OrderId="10785" ProductID="10" UnitPrice="31" Quantity="10"/>
<row CustomerID="GROSR" OrderId="10785" ProductID="75" UnitPrice="7.75" Quantity="10"/>

<!--Data returned using AUTO clause -->
<oh CustomerID="GROSR" OrderId="10268">
    <od ProductID="29" UnitPrice="99" Quantity="10"/>
    <od ProductID="72" UnitPrice="27.8" Quantity="4"/>
</oh>
<oh CustomerID="GROSR" OrderId="10785">
    <od ProductID="10" UnitPrice="31" Quantity="10"/>
    <od ProductID="75" UnitPrice="7.75" Quantity="10"/>
</oh>
Figure 14 XML Data with Schema

<Schema name="Schema" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="oh" content="eltOnly" model="closed" order="many">
<element type="od" maxOccurs="*"/>
<element type="CustomerID"/>
<element type="OrderId"/>
</ElementType>
<ElementType name="CustomerID" content="textOnly" model="closed" dt:type="string"/>
<ElementType name="OrderId" content="textOnly" model="closed" dt:type="i4"/>
<ElementType name="od" content="eltOnly" model="closed" order="many">
<element type="ProductID"/>
<element type="UnitPrice"/>
<element type="Quantity"/>
</ElementType>
<ElementType name="ProductID" content="textOnly" model="closed" dt:type="i4"/>
<ElementType name="UnitPrice" content="textOnly" model="closed" dt:type="fixed.14.4"/>
<ElementType name="Quantity" content="textOnly" model="closed" dt:type="i2"/>
</Schema>
<oh xmlns="x-schema:#Schema">
<CustomerID>GROSR</CustomerID>
<OrderId>10268</OrderId>
<od>
<ProductID>29</ProductID>
<UnitPrice>99</UnitPrice>
<Quantity>10</Quantity>
</od>
<od>
<ProductID>72</ProductID>
<UnitPrice>27.8</UnitPrice>
<Quantity>4</Quantity>
</od>
</oh>
<oh xmlns="x-schema:#Schema">
<CustomerID>GROSR</CustomerID>
<OrderId>10785</OrderId>
<od>
<ProductID>10</ProductID>
<UnitPrice>31</UnitPrice>
<Quantity>10</Quantity>
</od>
<od>
<ProductID>75</ProductID>
<UnitPrice>7.75</UnitPrice>
<Quantity>10</Quantity>
</od>
</oh>
Figure 15 EXPLICIT Mode Sample Query

SELECT 1 AS Tag, NULL AS Parent,
oh.CustomerID AS [Order!1!CustomerID],
oh.OrderId AS [Order!1!OrderId],
NULL AS [OrderDetail!2!ProductId!element],
NULL AS [OrderDetail!2!UnitPrice!element],
NULL AS [OrderDetail!2!Quantity!element]
FROM Orders oh
WHERE CustomerId = @customer

UNION ALL

SELECT 2, 1,
oh.CustomerID, oh.OrderId, od.ProductID, od.UnitPrice, od.Quantity
FROM Orders oh INNER JOIN [Order Details] od
ON oh.orderid = od.orderid
WHERE CustomerId = @customer

ORDER BY [Order!1!CustomerID], [Order!1!OrderId], [OrderDetail!2!ProductId!element]
FOR XML EXPLICIT
Figure 16 EXPLICT Mode XML Output

<Order CustomerID="GROSR" OrderId="10268">
<OrderDetail>
    <ProductId>29</ProductId><UnitPrice>99</UnitPrice><Quantity>10</Quantity>
</OrderDetail>
<OrderDetail>
    <ProductId>72</ProductId><UnitPrice>27.8</UnitPrice><Quantity>4</Quantity>
</OrderDetail>
</Order>
<Order CustomerID="GROSR" OrderId="10785">
<OrderDetail>
    <ProductId>10</ProductId><UnitPrice>31</UnitPrice><Quantity>10</Quantity>
</OrderDetail>
<OrderDetail>
    <ProductId>75</ProductId><UnitPrice>7.75</UnitPrice><Quantity>10</Quantity>
</OrderDetail>
</Order>
Figure 17 Orders Summary Template Files

ordercustsql.xml


<?xml version ='1.0' encoding='UTF-8'?>
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:header>
    <sql:param name='CustName'></sql:param>
</sql:header>
<sql:query>
    SELECT oh.CustomerID, oh.OrderId, od.ProductID, od.UnitPrice, od.Quantity
    FROM Orders oh INNER JOIN [Order Details] od
    ON oh.orderid = od.orderid
    WHERE oh.CustomerID = @CustName
    ORDER BY oh.CustomerID, oh.OrderId, od.ProductID
    FOR XML AUTO
</sql:query>
</ROOT>

orderscustxpath.xml


<?xml version ='1.0' encoding='UTF-8'?>
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:header>
    <sql:param name='CustName'></sql:param>
</sql:header>
<sql:xpath-query mapping-schema="orderschema.xml">
    Order[@CustomerID=$CustName]
</sql:xpath-query>
</ROOT>
Figure 18 XDR Annotated Schema Definition

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
        xmlns:dt="urn:schemas-microsoft-com:datatypes"
        xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="Order" sql:relation="Orders" 
                                  sql:key-fields="OrderID">
    <AttributeType name="OrderID" dt:type="id" sql:id-prefix="Ord-" />
    <AttributeType name="CustomerID" />

    <attribute type="OrderID" />
    <attribute type="CustomerID" />

    <element type="OrderDetail">
      <sql:relationship key-relation="Orders" key="OrderID"
        foreign-relation="[Order Details]" foreign-key="OrderID" />
    </element>
</ElementType>

<ElementType name="OrderDetail" sql:relation="[Order Details]"
                                  sql:key-fields="OrderID ProductID">
    <AttributeType name="ProductID" dt:type="idref" sql:id-prefix=
                                  "Prod-" />
    <AttributeType name="UnitPrice"/>
    <AttributeType name="Quantity" />

    <attribute type="ProductID" />
    <attribute type="UnitPrice" sql:field="UnitPrice" />
    <attribute type="Quantity" />

</ElementType>

</Schema>
Figure 19 XML Data Using ADO 2.6

Dim conNW As New ADODB.Connection
Dim comNW As New ADODB.Command
Dim strmNW As New ADODB.Stream
Dim strXML As String
    
conNW.Provider = "SQLOLEDB"
conNW.ConnectionString = "Server=myserver;Trusted_Connection=yes"
conNW.Open
conNW.DefaultDatabase = "Northwind"
    
strmNW.Open
 
comNW.CommandText = "OrderSummaryXMLBase"
comNW.CommandType = adCmdStoredProc
comNW.Parameters.Append  comNW.CreateParameter("@customer", adVarChar, _
                         adParamInput, 5, "GROSR")
comNW.ActiveConnection = conNW
comNW.Properties("Output Stream") = strmNW
comNW.Execute , , adExecuteStream
    
strXML = strmNW.ReadText
Figure 20 OLAP Linked Server

EXEC sp_addlinkedserver
    @server='FoodMart_OLAP',   /* local SQL name for the linked server */
    @srvproduct='',            /* not used */
    @provider='MSOLAP',        /* OLE DB provider */
    @datasrc='carlnolsql',     /* olap server name (machine name) */
    @catalog='FoodMart 2000'   /* default catalog/database */
GO
EXEC sp_addlinkedsrvlogin
    @rmtsrvname='FoodMart_OLAP',
    @useself='true'
GO

-- Provides schema rowset information about the dimensions
EXEC sp_tables_ex @table_server='FoodMart_OLAP'
GO

-- Provides schema rowset information about the measures and levels
EXEC sp_columns_ex @table_server='FoodMart_OLAP', @table_name='Sales'
GO
Show:
© 2015 Microsoft