我有一个在 IAAS 提供程序上运行的远程数据库，我需要能够从我的本地工作站执行连接查询。我遇到了麻烦，因为当我尝试创建链接时，它正在尝试使用命名管道进行连接。
我使用 SQL Server Native Client 10 创建了一个系统 DSN，并为其提供了 IP、数据库、用户和密码。测试了它，它很好。
单击确定后，我收到一个错误，它找不到服务器，它表明它正在尝试使用命名管道。
我应该怎么做才能纠正这个？
我正在使用外部名称创建 CLR UDF，而二级嵌套类名似乎是不允许的。是这样吗？当使用以下语法并包含第一级类名和关联方法时，我可以将 UDF 绑定到该方法，但如果包含其他子类级别则不能。
CREATE FUNCTION [dbo].[udf_test](@variable [nvarchar](MAX))
RETURNS [nvarchar](MAX) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [assembly].[ns.first.second].[method]
GO
谢谢
我想使用 hierarchyid 标识符将一堆具有 parent->child->grandchild 关系的表解析为一个表。这是我根据自己的想法得出的结论。
CREATE TABLE [dbo].[tbl_server_group]
(
[refid] [int] IDENTITY(1,1) NOT NULL,
[refhid] [hierarchyid] NOT NULL,
[reflvl] AS refhid.GetLevel() PERSISTED,
[refdate] [datetime] NOT NULL DEFAULT GETDATE(),
[refname] [varchar](50) NOT NULL
)
我的问题是我需要能够要求 refname 是唯一的，但只能在它所在的容器内。因为我用它来表示服务器，所以我想模拟某种 DNS 树，但将其他层添加到它也是。我的例子如下：
ROOT
| something.com
|| www
||| index.html
|| staging
||| testbed.php
| someotherthing.com
|| test
||| index.html
我想确保 something.com 只有一个“www”，而 www 只有一个“index.html”。
反正有约束来做到这一点吗？如果不是，是否有一种有效的方法来强制唯一性？
谢谢
我目前在我的数据库中使用 XML 来表示单个产品的元数据。我正在使用以下模式输入数据。在字段的每个子分组中，您会注意到我用来匹配关系表行的 cms_name 属性。
此关系表也已列出，允许我将（用户的）内容组链接到每个 cms_name，将权限关联到组。旁注、friendly、render 和 restrict 仅适用于前端。
我想继续使用我拥有的一些东西，因为嵌入在 XML 数据中的是要通过 XSL 回显的呈现和数据限制；然而，提升（通过持久化列）和更新 XML 似乎是一种资源杀手。
我对数据库安全性没有真正的了解。我了解 NT/2008/（无论哪个月份）安全性，包括树、域和本地安全性（组和用户），但我不知道如何将其转化为数据库安全性。
我为每个受保护节点设想了一个只读视图和一个可更新视图，并且每个节点都解析为当前容器表中的列，其中包含 XML、主键、SKU 和日期（创建、修改等） . 等式中缺少的是如何授予用户对内容的权利。
我的最终结果是我希望有些东西可以扩展。不是数百万用户，但至少有一百个，而且 XML 似乎是一个资源消耗者。我必须能够以编程方式创建要在应用程序中使用的用户和组。
谢谢
XML模式
<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:simpleType name="search_minage">
<xsd:restriction base="xsd:positiveInteger">
<xsd:minInclusive value="1" />
<xsd:maxInclusive value="99" />
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="search_maxage">
<xsd:restriction base="xsd:positiveInteger">
<xsd:maxInclusive value="99" />
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="decMONEY">
<xsd:restriction base="xsd:decimal">
<xsd:fractionDigits value="2"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="intBOOL">
<xsd:restriction base="xsd:nonNegativeInteger">
<xsd:minInclusive value="0" />
<xsd:maxInclusive value="1" />
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="option_time">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="mon"/>
<xsd:enumeration value="yr"/>
<xsd:enumeration value="months"/>
<xsd:enumeration value="years"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="option_dimension">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="in"/>
<xsd:enumeration value="ft"/>
<xsd:enumeration value="inches"/>
<xsd:enumeration value="feet"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="option_weight">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="oz"/>
<xsd:enumeration value="lb"/>
<xsd:enumeration value="ounces"/>
<xsd:enumeration value="pounds"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:complexType name="price_cost">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Cost"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="decMONEY"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="price_sale">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Sale Price"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="decMONEY"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="price_openbox">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Open Box Price"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="decMONEY"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="price_distressed">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Distressed Price"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="decMONEY"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="openbox">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Open Box Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="distressed">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Distressed Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="consigned">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Marketplace Consigned Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="listed">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Marketplace Listed Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="external">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Amazon Warehoused Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="dropship">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Available Drop Ship Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="preorder">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Customer Pre-Order Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="sold">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Sold Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="sub_grp_normal_counts">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Onhand Qty"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:nonNegativeInteger"/>
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_normal_counts"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="sub_grp_extend_counts">
<xsd:sequence>
<xsd:element name="openbox" type="openbox"/>
<xsd:element name="distressed" type="distressed"/>
<xsd:element name="consigned" type="consigned"/>
<xsd:element name="listed" type="listed"/>
<xsd:element name="amazonhosted" type="external"/>
<xsd:element name="dropship" type="dropship"/>
<xsd:element name="preorder" type="preorder"/>
<xsd:element name="sold" type="sold"/>
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_extend_counts"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="sub_grp_normal_pricing">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Price"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="decMONEY"/>
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_normal_price"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="sub_grp_extend_pricing">
<xsd:sequence>
<xsd:element name="cost" type="price_cost" />
<xsd:element name="sale" type="price_sale" />
<xsd:element name="openbox" type="price_openbox" />
<xsd:element name="distressed" type="price_distressed" />
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_extend_price"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="sub_grp_sale_date">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="On Sale Exp. Date"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="date"/>
<xsd:element name="value" type="xsd:date" default="1999-01-01"/>
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_sale_date"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="grp_quantities">
<xsd:sequence>
<xsd:element name="onhand" type="sub_grp_normal_counts"/>
<xsd:element name="extend" type="sub_grp_extend_counts"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="grp_pricing">
<xsd:sequence>
<xsd:element name="normal" type="sub_grp_normal_pricing"/>
<xsd:element name="extend" type="sub_grp_extend_pricing"/>
<xsd:element name="saledateexp" type="sub_grp_sale_date"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="search_shops">
<xsd:sequence>
<xsd:element name="shop" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:positiveInteger"/>
<xsd:attribute name="short" type="xsd:string"/>
<xsd:attribute name="display" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="search_genres">
<xsd:sequence>
<xsd:element name="genre" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:positiveInteger"/>
<xsd:attribute name="short" type="xsd:string"/>
<xsd:attribute name="display" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="sub_grp_shops">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Shops"/>
<xsd:element name="render" type="xsd:string" fixed="select"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="values" type="search_shops" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="sub_grp_genres">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Genres"/>
<xsd:element name="render" type="xsd:string" fixed="select"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="values" type="search_genres" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="search_description">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Description"/>
<xsd:element name="render" type="xsd:string" fixed="large_text"/>
<xsd:element name="restrict" type="xsd:string" fixed="string"/>
<xsd:element name="value" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="dim_weight">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Weight"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="xsd:positiveInteger" default="6"/>
<xsd:element name="units" type="option_weight" default="oz"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="dim_length">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Length"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_float"/>
<xsd:element name="value" type="decMONEY" default="1"/>
<xsd:element name="units" type="option_dimension" default="in"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="dim_width">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Width"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_float"/>
<xsd:element name="value" type="decMONEY" default="1"/>
<xsd:element name="units" type="option_dimension" default="in"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="dim_height">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Height"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_float"/>
<xsd:element name="value" type="decMONEY" default="1"/>
<xsd:element name="units" type="option_dimension" default="in"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="safe_minage">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Minimum Age"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="search_minage" default="10" />
<xsd:element name="units" type="option_time" default="years"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="safe_maxage">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Maximum Age"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="small_pos_number"/>
<xsd:element name="value" type="search_maxage" default="99" />
<xsd:element name="units" type="option_time" default="years"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="safe_choking">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Choking Hazard"/>
<xsd:element name="render" type="xsd:string" fixed="bool"/>
<xsd:element name="restrict" type="xsd:string" fixed="bool"/>
<xsd:element name="value" type="intBOOL" default="0" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="safe_smallparts">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Small Parts"/>
<xsd:element name="render" type="xsd:string" fixed="bool"/>
<xsd:element name="restrict" type="xsd:string" fixed="bool"/>
<xsd:element name="value" type="intBOOL" default="0" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="attr_fragile">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Fragile Item"/>
<xsd:element name="render" type="xsd:string" fixed="bool"/>
<xsd:element name="restrict" type="xsd:string" fixed="bool"/>
<xsd:element name="value" type="intBOOL" default="0" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="attr_adult">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Adult Themed"/>
<xsd:element name="render" type="xsd:string" fixed="bool"/>
<xsd:element name="restrict" type="xsd:string" fixed="bool"/>
<xsd:element name="value" type="intBOOL" default="0" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="attr_shipsbyself">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Ships In Own Box"/>
<xsd:element name="render" type="xsd:string" fixed="bool"/>
<xsd:element name="restrict" type="xsd:string" fixed="bool"/>
<xsd:element name="value" type="intBOOL" default="0" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="attr_available">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Available For Sale?"/>
<xsd:element name="render" type="xsd:string" fixed="bool"/>
<xsd:element name="restrict" type="xsd:string" fixed="bool"/>
<xsd:element name="value" type="intBOOL" default="0"/>
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_available"/>
</xsd:complexType>
<xsd:complexType name="name_brand">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Brand Name"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="string"/>
<xsd:element name="value" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="name_subbrand">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Sub-Brand Name"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="string"/>
<xsd:element name="value" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="name_product">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Product Name"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="string"/>
<xsd:element name="value" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="name_model">
<xsd:sequence>
<xsd:element name="friendly" type="xsd:string" fixed="Model/Part Number"/>
<xsd:element name="render" type="xsd:string" fixed="text"/>
<xsd:element name="restrict" type="xsd:string" fixed="string"/>
<xsd:element name="value" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="grp_search">
<xsd:sequence>
<xsd:element name="shops" type="sub_grp_shops"/>
<xsd:element name="genres" type="sub_grp_genres" />
<xsd:element name="description" type="search_description" />
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_search"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="grp_safety">
<xsd:sequence>
<xsd:element name="minage" type="safe_minage" />
<xsd:element name="maxage" type="safe_maxage" />
<xsd:element name="choking" type="safe_choking" />
<xsd:element name="smallparts" type="safe_smallparts" />
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_safety"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="grp_attributes">
<xsd:sequence>
<xsd:element name="fragile" type="attr_fragile" />
<xsd:element name="adultproduct" type="attr_adult" />
<xsd:element name="shipbyself" type="attr_shipsbyself" />
<xsd:element name="available" type="attr_available" />
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_attributes"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="grp_dimensions">
<xsd:sequence>
<xsd:element name="weight" type="dim_weight"/>
<xsd:element name="length" type="dim_length" />
<xsd:element name="width" type="dim_width" />
<xsd:element name="height" type="dim_height" />
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_dimensions"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="grp_names">
<xsd:sequence>
<xsd:element name="brand" type="name_brand" />
<xsd:element name="sub_brand" type="name_subbrand" />
<xsd:element name="product" type="name_product" />
<xsd:element name="model" type="name_model" />
</xsd:sequence>
<xsd:attribute name="cms_name" type="xsd:string" fixed="tbl_inv_meta_names"/>
<xsd:attribute name="cms_internal" type="intBOOL" fixed="1"/>
</xsd:complexType>
<xsd:complexType name="mDetail">
<xsd:sequence>
<xsd:element name="name" type="grp_names"/>
<xsd:element name="safety" type="grp_safety"/>
<xsd:element name="attributes" type="grp_attributes"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="lDetail">
<xsd:sequence>
<xsd:element name="dimensions" type="grp_dimensions"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="sDetail">
<xsd:sequence>
<xsd:element name="search" type="grp_search"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="fDetail">
<xsd:sequence>
<xsd:element name="quantities" type="grp_quantities"/>
<xsd:element name="pricing" type="grp_pricing"/>
</xsd:sequence>
</xsd:complexType>
<xsd:element name="metadata">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="information" type="mDetail" />
<xsd:element name="measurements" type="lDetail" />
<xsd:element name="categorization" type="sDetail" />
<xsd:element name="financial" type="fDetail" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
表：
CREATE TABLE [dbo].[tbl_cms_securables]
(
[refid] [int] NOT NULL IDENTITY(1,1),
[refname] [varchar](100) NOT NULL,
[refdescription] [varchar](MAX) NULL,
CONSTRAINT pk_cms_secdocs_refid PRIMARY KEY (refid)
)
CREATE TABLE [dbo].[tbl_cms_secureusers]
(
[refid] [bigint] NOT NULL IDENTITY(1,1),
[secid] [int] NOT NULL,
[grpid] [int] NOT NULL,
[read] [bit] NOT NULL DEFAULT 0,
[modify] [bit] NOT NULL DEFAULT 0,
[delete] [bit] NOT NULL DEFAULT 0,
CONSTRAINT pk_cms_secuser_refid PRIMARY KEY (refid),
CONSTRAINT fk_cms_secdocs_secid FOREIGN KEY (secid) REFERENCES dbo.tbl_cms_securables(refid),
CONSTRAINT fk_cms_secgrps_grpid FOREIGN KEY (grpid) REFERENCES dbo.tbl_cms_groups(refid) ON DELETE CASCADE
)
我不确定是否正确使用非聚集索引。SSMS 计划估计器说当以下索引已经在表上时添加另一个索引。
CREATE NONCLUSTERED INDEX [ix_zone_fetch_shipping] ON [dbo].[tbl_shipping_rates_zones]
(
[iso] ASC, [mzone] ASC, [postal] ASC
)
INCLUDE
(
[region], [zone_dom], [zone_emi], [zone_pmi], [zone_fci],
[zone_ups], [zone_fed]
)
我的查询是
SELECT * FROM tbl_shipping_rates_zones WHERE postal = '10001'
我的问题是......我应该为所有可能的查找键创建一个索引吗？我在不同的查询中按 mzone、iso 和 postal 进行搜索。
谢谢
我想使用我在 C# CLR 中开发的代码用于系统上的所有数据库，这样我就不必将每个数据库设置为可信任并打开 CLR 并在每个数据库中保留一堆相同的代码.
从管理和安全的角度来看，有没有最好的方法来做到这一点？CLR 函数非常基本，如字符串中断器、电子邮件验证、url en/decode、base64 等。我希望每个数据库中只有 dbo 模式能够访问这些函数。
谢谢