SQL Server 2000: New Features Provide Unmatched Ease of Use and Scalability to Admins and Users

DECLARE @my_var sql_variant
SET @my_var = 3.142

DECLARE @my_chr VARCHAR(12), @my_dec DECIMAL(5,4)
SET @my_chr = CAST(@my_var AS VARCHAR(12))
SET @my_dec = CAST(@my_var AS DECIMAL(5,4))

SQL_VARIANT_PROPERTY(expression, property)

Dim varName, varAge
varName = CStr(Request.Form("txtName"))
varAge = CInt(Request.Form("txtAge"))

DECLARE @local_variable TABLE <table_defintion>

DECLARE @territory TABLE (territory_id INT, territory_desc NVARCHAR(50))
INSERT INTO @territory
SELECT TerritoryId, TerritoryDescription FROM Territories

SELECT * FROM ProductSalesByYear(1996)

SELECT Product.ProductID, Product.ProductName, Product.ProductSales,
 Categories.*
FROM ProductSalesByYear(1997) AS Product
INNER JOIN Categories ON Product.CategoryID = Categories.CategoryID
WHERE Product.ProductSales > 15000

DECLARE @count INT
SET @count = dbo.SalesForSpecifiedDate('12/12/97')

CREATE FUNCTION dbo.TenMostExpensiveProductsTable ()
RETURNS TABLE
AS
RETURN    SELECT TOP 10
        ProductName AS TenMostExpensiveProducts,
        UnitPrice, SupplierID, UnitsInStock, UnitsOnOrder
        FROM Products
        ORDER BY Products.UnitPrice DESC

SELECT Products.*, Suppliers.*
FROM TenMostExpensiveProductsTable() AS Products
INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID

CREATE VIEW dbo.EmployeeName AS
    SELECT EmployeeId, FirstName + ' ' + LastName AS EmployeeName
    FROM Employees

UPDATE EmployeeName
SET EmployeeName = 'Anne Dodsworth-Smith'
WHERE EmployeeId = 9

ALTER TABLE dbo.[Order Details] ADD 
CONSTRAINT FK_Order_Details_Orders FOREIGN KEY 
    (OrderID) REFERENCES dbo.Orders(OrderID),
CONSTRAINT FK_Order_Details_Products FOREIGN KEY 
    (ProductID) REFERENCES dbo.Products (ProductID)

ALTER TABLE dbo.[Order Details] ADD 
CONSTRAINT FK_Order_Details_Orders FOREIGN KEY 
    (OrderID) REFERENCES dbo.Orders(OrderID)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION,
CONSTRAINT FK_Order_Details_Products FOREIGN KEY 
    (ProductID) REFERENCES dbo.Products (ProductID)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION

ALTER TABLE dbo.[Order Details] ADD 
CONSTRAINT FK_Order_Details_Orders FOREIGN KEY 
    (OrderID) REFERENCES dbo.Orders(OrderID)
    ON UPDATE NO ACTION
    ON DELETE CASCADE,
CONSTRAINT FK_Order_Details_Products FOREIGN KEY 
    (ProductID) REFERENCES dbo.Products (ProductID)
    ON UPDATE CASCADE
    ON DELETE NO ACTION

CREATE UNIQUE CLUSTERED INDEX IDX_CV_ORDERSUBTOTAL_OI_ST
    ON OrderSubTotals (OrderID)

SELECT Orders.*, det.SubTotal, det.DetailCount
FROM Orders INNER JOIN OrderSubTotals det
ON Orders.OrderID = det.OrderID

SELECT Orders.CustomerID, Orders.OrderID,
    SUM(CONVERT(MONEY,(UnitPrice*Quantity*(1-Discount)/100))*100)
    AS SubTotal
FROM Orders INNER JOIN [Order Details] OD
ON Orders.OrderID = OD.OrderID
GROUP BY Orders.CustomerID, Orders.OrderID
ORDER BY Orders.CustomerID, Orders.OrderID

SELECT Orders.OrderID, Orders.CustomerID, det.ProductID,
 det.ExtendedPrice
FROM Orders INNER JOIN [Order Details] det
ON Orders.OrderID = det.OrderID
WHERE det.ExtendedPrice > 2500

CREATE UNIQUE NONCLUSTERED INDEX dbo.IDX_NCT_ORDERSDEATILS_OI_PI_EP
    ON [Order Details] (OrderID, ProductID, ExtendedPrice)

SELECT  CustomerID, OrderID, OrderDate FROM Orders
WHERE OrderDate BETWEEN '01/01/1997' AND '01/31/1997'
ORDER BY CustomerID ASC, OrderId ASC, OrderDate DESC

CREATE NONCLUSTERED INDEX IDX_NCT_ORDERS_CI_OD
    ON Orders (CustomerID ASC, OrderDate DESC)

CustomerID >= 'N'

EXEC sp_addlinkedserver 'NodeName'
EXEC sp_setnetname 'NodeName', 'ServerName'
EXEC sp_serveroption 'NodeName', 'lazy schema validation', 'true'

CREATE VIEW dbo.CustomersUnion
AS
    SELECT * FROM Node1.NorthwindOrd1.dbo.Customers
    UNION ALL
    SELECT * FROM Node2.NorthwindOrd2.dbo.Customers

INSERT INTO CustomersUnion
SELECT     CustomerID, CompanyName, ContactName, ContactTitle,
    Address, City, Region, PostalCode, Country, Phone, Fax
FROM Northwind.dbo.Customers

SELECT * FROM CustomersUnion
WHERE CustomerID BETWEEN 'M' and 'O'

CREATE FUNCTION dbo.GetCustomer (@CustomerID NCHAR(5))
RETURNS TABLE
AS
RETURN  SELECT * FROM CustomersUnion
        WHERE CustomerID = @CustomerID

IF @CustomerID  <  'N'
   Retrieve row from table NorthwindOrd1.dbo.Customers
ELSEIF @CustomerID >= 'N'
   Retrieve row from table NorthwindOrd2.dbo.Customers

CREATE TABLE IntlProduct (
    ProdName    NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI,
    ProdDesc    NVARCHAR(256),
    ProdType    VARCHAR(8),
    LangCode    VARCHAR(16),
    CONSTRAINT pk_intl_product PRIMARY KEY (ProdName)
)

INSERT INTO IntlProduct
VALUES ('Merlot Grape', 'Merlot Wine Grape', 'Fruit', 'French')
INSERT INTO IntlProduct
VALUES ('M�rlot Grape', 'M�rlot Wine Grape', 'Fruit', 'French')

CREATE DATABASE InternationalProducts 
 COLLATE SQL_Latin1_General_CP1_CI_AI

SELECT * FROM IntlProduct
ORDER BY ProdType, ProdName

SELECT * FROM IntlProduct
    WHERE LangCode = 'French'
    ORDER BY ProdType, ProdName COLLATE French_CI_AI

CREATE VIEW IntlProductFrench
AS
SELECT ProdName COLLATE French_CI_AI AS ProdName, ProdDesc, ProdType
        FROM MyProducts
        WHERE LangCode = 'French'
WITH CHECK OPTION

SELECT * FROM IntlProductFrench
    ORDER BY ProdType, ProdName

-- Set the caption for the table
EXEC sp_addextendedproperty 'Caption', 'Employee Details',
    'user', 'dbo', 'table', 'Employees'
-- Set the input mask for the postal code
EXEC sp_addextendedproperty 'Mask', '99999[-9999]',
    'user', 'dbo', 'table', 'Employees', 'column', 'PostalCode'
-- Set the input mask for the home phone
EXEC sp_addextendedproperty 'Mask', '(NNN)NNN-NNNN',
    'user', 'dbo', 'table', 'Employees', 'column', 'HomePhone'

SELECT * FROM ::fn_listextendedproperty
(NULL, 'user', 'dbo', 'table', 'Employees', 'column', default)

sp_extendedproperty ['property_name', 'value']
[, 'level0_object_type', 'level0_object_name'
[, 'level1_object_type', 'level1_object_name'
[, 'level2_object_type', 'level2_object_name' ] ] ]

DECLARE @utcdate DATETIME
SET @utcdate = GetUTCDate()

INSERT INTO FullNames VALUES ('Carl Nolan')
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY],
 @@IDENTITY AS [@@IDENTITY]

INSERT INTO FullNames VALUES ('Jared Nolan')
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY],
 @@IDENTITY AS [@@IDENTITY]

SELECT IDENT_CURRENT('FullNames') AS FullnamesIdent,
IDENT_CURRENT('LastNames') AS LastnamesIdent

SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

-- Persists a unique identifier
DECLARE @var uniqueidentifier
SET @var = NEWID()
SET CONTEXT_INFO @var
GO

-- Select the context information
DECLARE @sess VARBINARY(128), @var uniqueidentifier
SET @sess = (SELECT context_info FROM master.dbo.sysprocesses
 WHERE spid = @@spid)
SET @var  = CAST(SUBSTRING(@sess,1,16) AS uniqueidentifier)
GO

-- Persists a unicode character string
DECLARE @var VARBINARY(128)
SET @var = CAST(N'Hello World' AS VARBINARY(128))
SET CONTEXT_INFO @var
GO

-- Select the context information
DECLARE @sess VARBINARY(128), @var NVARCHAR(64)
SET @sess = (SELECT context_info FROM master.dbo.sysprocesses
 WHERE spid = @@spid)
SET @var  = CAST(@sess AS NVARCHAR(64))
GO

SELECT FirstName + ' ' + LastName AS FullName
FROM LocalNode.Northwind.dbo.Employees
WHERE LastName LIKE 'D%'

SELECT FirstName + ' ' + LastName AS FullName
FROM OPENDATASOURCE(
    'SQLOLEDB','Data Source=myserver;User ID=devuser;
     Password=devpassword'
    ).Northwind.dbo.Employees
WHERE LastName LIKE 'D%'

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 = 'GROSR'
ORDER BY oh.CustomerID, oh.OrderId, od.ProductID

FOR XML {mode} [,XMLDATA][, ELEMENTS][, BINARY Base64]

FOR XML AUTO, XMLDATA, ELEMENTS

OPENXML(idoc int [in], rowpattern nvarchar[in], [flags byte[in]])
[WITH (SchemaDeclaration | TableName)]

DECLARE @idoc INT
exec sp_xml_preparedocument @idoc OUTPUT, @xmldoc