Alex Erwin's questions

Tenho um banco de dados remoto em execução em um provedor IAAS e preciso executar consultas combinadas em minha estação de trabalho local. Estou tendo problemas porque, quando tento criar o link, ele está tentando usar pipes nomeados para a conexão.

Criei um DSN do sistema usando o SQL Server Native Client 10 e alimentei o IP, Banco de Dados, Usuário e Senha. Testei e estava bom.

1. Fui para Servidores Vinculados e Criar Novo.
2. Especificou um nome "PRINCIPAL"
3. SQL Server Native Client 10 selecionado na caixa de combinação
4. SQL Server inserido no campo Nome do produto
5. Digitei meu nome DSN no campo de fonte de dados
6. SQLNCLI10 inserido no campo Nome do provedor
7. Digitei o nome do meu banco de dados no campo Catálogo
8. Fui para as opções de segurança e especifiquei meu emparelhamento de login local para remoto.

Depois de clicar em OK, recebo um erro informando que ele não consegue encontrar o servidor e mostra que está tentando usar pipes nomeados.

O que devo fazer para corrigir isso?

Estou criando um CLR UDF usando nome externo e nomes de classes aninhadas de segundo nível parecem não ser permitidos. É este o caso? Ao usar a sintaxe a seguir e incluir um nome de classe de primeiro nível e um método associado, posso vincular uma UDF ao método, mas não se incluir níveis de subclasse adicionais.

    CREATE FUNCTION [dbo].[udf_test](@variable [nvarchar](MAX))
    RETURNS [nvarchar](MAX) WITH EXECUTE AS CALLER
    AS 
    EXTERNAL NAME [assembly].[ns.first.second].[method]
    GO

Obrigado

Eu quero resolver um monte de tabelas que tinham relacionamentos pai->filho->neto em uma única tabela usando identificadores de hierarquia. Aqui está o que eu criei com base no que eu tinha.

    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
    )

Meu problema é que preciso exigir que o refname seja exclusivo, mas apenas dentro do contêiner em que está. Como estou usando isso para representar servidores, queria emular uma espécie de árvore DNS, mas adicionar outras camadas para isso também. Meu exemplo sendo o seguinte:

    ROOT
    | something.com
    || www
    ||| index.html
    || staging
    ||| testbed.php
    | someotherthing.com
    || test
    ||| index.html

Quero garantir que algo.com tenha apenas um "www" e que www tenha apenas um "index.html".

Existe alguma maneira de fazer isso com restrições? Se não, existe uma maneira eficiente de forçar a exclusividade?

Obrigado

Atualmente, estou usando XML em meu banco de dados para representar metadados para produtos individuais. Estou usando o esquema abaixo para inserir os dados. Em cada subgrupo de campos, você notará o atributo cms_name que eu uso para corresponder a uma linha da tabela relacional.

Esta tabela relacional, também listada, permite vincular grupos de conteúdo (de usuários) a cada cms_name, associar direitos aos grupos. Observação lateral, amigável, renderização e restrição são apenas para o front-end.

Quero continuar usando um pouco do que tenho porque embutido nos dados XML está a renderização e as restrições de dados a serem ecoadas via XSL; no entanto, promover (por meio de colunas persistentes) e atualizar o XML parece ser um matador de recursos.

Não tenho nenhum entendimento real de segurança de banco de dados. Eu entendo a segurança do NT/2008/(qualquer tipo do mês), incluindo árvore, domínio e segurança local (grupos e usuários), mas não tenho ideia de como traduzir isso para a segurança do banco de dados.

Prevejo uma exibição somente leitura e uma exibição atualizável para cada nó protegido e cada um dos nós analisados ​​em colunas na tabela de contêiner atual que contém o XML, uma chave primária, SKU e datas (criar, modificar etc.) . O que falta na equação é como dar direitos aos usuários sobre o conteúdo.

1. Eu vejo papéis e esquemas, como eles se relacionam com grupos?
2. O que estou tentando fazer é possível usando segurança integrada, exibições e exibições atualizáveis?
3. O potencial para direitos distintos em um "documento" de produto pode aumentar à medida que mais usuários precisam de mais direitos. Vale a pena tentar abandonar o XML?

Meu resultado final é que eu gostaria que algo pudesse escalar. Não milhões de usuários, mas pelo menos uma centena, e o XML parece ser um devorador de recursos. Devo ser capaz de criar programaticamente os usuários e grupos para usar no aplicativo.

Obrigado

Esquema 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>

Tabelas:

    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
    )

Não tenho certeza se devo usar índices não clusterizados corretamente. O estimador de plano do SSMS disse para adicionar outro índice quando o seguinte índice já estava na tabela.

    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]
    )

minha consulta foi

SELECT * FROM tbl_shipping_rates_zones WHERE postal = '10001'

Minha pergunta é... devo criar um índice para todas as chaves de pesquisa possíveis? Pesquiso por mzone, iso e postal em diferentes consultas.

Obrigado

Eu gostaria de usar o código que desenvolvi em C # CLR para ser usado em todos os bancos de dados do sistema, para não precisar definir cada um como confiável e ativar o CLR e manter um monte do mesmo código dentro de cada um .

Existe uma maneira melhor de fazer isso do ponto de vista administrativo e de segurança? As funções CLR são muito básicas, como quebra de string, validação de e-mail, url en/decode, base64 e etc. Gostaria que apenas o esquema dbo em cada banco de dados pudesse acessar as funções.

1. Existe alguma maneira simples de fazer isso?
2. Também não estou claro se a dll CLR está incorporada e se eu mover o banco de dados, ele acompanha ou devo mover a dll também.

Obrigado