none
70-461 Work with Data

    Question

  • I'm filling gaps in our 70-461 Exam Guide Querying Microsoft SQL Server 2012 and work with data has some gaps, where I cannot find appropriate links to Microsoft documentation.

    I would appreciate if you could clarify the points for me and ideally points me to Microsoft articles.

    1) implement logic which uses dynamic SQL and system metadata.

    -- I'm ok with first part but I'm not entirely sure about system metadata? What exactly should I understand by that?

    2) write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables;

    I'm not able to find Microsoft documentation page that would specifically talk about derived tables... has anyone come across it?

    3) determine what code may or may not execute based on the tables provided;

    ok in this case I have no idea what they means.

    4) use and understand different data access technologies;

    Any links to Microsoft discuss it??


    Many thanks for your time and input

    Emil

    • Changed type Naomi NModerator Sunday, December 30, 2012 9:07 PM Question rather than discussion
    Friday, December 21, 2012 7:17 PM

Answers

All replies

  • FOR 2ND QUESTION

    http://technet.microsoft.com/en-us/library/aa337504.aspx

    http://msdn.microsoft.com/en-us/library/ms239725.aspx


    Mudassar

    Wednesday, December 26, 2012 9:08 AM
  • Hi

    Thanks for the links, although they mention derived tables I was more hoping fro a page that goes into more details and examples of T-SQL with derived tables (from Microsoft site), I think in this case I might just include a link to our page without Microsoft page to support it.

    Take care

    Emil

    Wednesday, December 26, 2012 10:46 AM
  • >1) implement logic which uses dynamic SQL and system metadata.

    Reference blog: http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

    Take a look at the logic:

    A. It uses metadata from INFORMATION_SCHEMA.TABLES view (to get the list of tables)

    B. It uses a cursor (loop) to process all tables

    C. It constucts a dynamic SQL string and executes it

    -- including all types of joins versus the use of derived tables;

    If you take a query and put it into a wrapper ( SELECT query) as X  - you created a derived table X. For more sophistication, you can add column alias list:

    ( SELECT query) as X ( OrderDate, OrderQty, ProductNumber, CustomerID)

    CTE is the new competition to derived table.

    Reference:

    http://www.sqlusa.com/bestpractices2005/derivedtable/


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Saturday, December 29, 2012 5:44 PM
    Moderator
  • Hi Kalman,

    Thanks for your answer.

    The way I read your reply is that information_schema.tables is part of system metadata? which sounds fine. I suspect 'system metadata' can be also called database metadata? not necessarily server metadata? 

    Would system metadata be both database and server (instance) metadata?

    I think I found the link I was after (I changed my google query from system metadata to system views) system views and I presume the most relevant category for 70-461 is information schema views

    With q2. I think I won't find any links

    q3. Still not sure...security? syntax?

    Many thanks

    Emil

    Saturday, December 29, 2012 7:39 PM
  • The way I read your reply is that information_schema.tables is part of system metadata?

    It's rather a view of it. The actual system tables are not directly visible.

    I suspect 'system metadata' can be also called database metadata? not necessarily server metadata? 

    Well, I guess that depends on the context, and I don't know yours, so I cannot comment further.

    With q2. I think I won't find any links

    This query features a derived table:

      SELECT col1, col2, colcnt
      FROM   (SELECT col1, col2, COUNT(*) AS colcnt
              FROM   tbl
              GROUP  BY col1, col2) AS x
      WHERE  colcnt > 3

    q3. Still not sure...security? syntax?

    Again, I have no idea of what the context may be. Can you be more specific?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, December 29, 2012 8:45 PM
  • Hi Erland,

    I don't have anything specific in mind with my comments. The initial question was to find out if there are Microsoft pages (links) that can support specific exam requirements that I listed (taken from 'skills measured').

    Regards

    Emil

    Saturday, December 29, 2012 9:06 PM
  • > I suspect 'system metadata' can be also called database metadata? not necessarily server metadata?

    Database metadata is preferred use for system information related to a specific database, although in the right context system metadata understood as the same. In the old days, they were located in sys... (system tables). Nowadays, they are located in  system views (400!) which includes the sys. views and the INFORMATION_SCHEMA. views (ANSI). Strictly speaking system metadata covers server metadata as well such as sys.databases  or sys.servers system views. 

    The "INFORMATION_SCHEMA" schema has 21 views and the "sys" schema has 379 views, altogether 400.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Saturday, December 29, 2012 9:22 PM
    Moderator
  • Saturday, December 29, 2012 9:29 PM
    Moderator
  • >3) determine what code may or may not execute based on the tables provided;

    For an INNER JOIN between two tables a FOREIGN KEY constraint is required. Similar consideration for LEFT OUTER JOIN & RIGHT OUTER JOIN. Example: Sales.SalesOrderDetail has an FK constraint to Sales.SalesOrderHeader, I can JOIN them.

    If I want to INNER JOIN Sales.SalesOrderDetail with Purchasing.PurchaseOrderHeader, I cannot do it due to lack of FK constraint between them. The attempted INNER JOIN would not give a syntax error, but the result would be meaningless.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Saturday, December 29, 2012 9:41 PM
    Moderator
  • Hi Everyone,

    Many thanks for all comments and your time.

    We have updated our guide. Our goal is to provide Microsoft links that are useful but in these cases we haven't managed to find links to would specifically cover the requirement therefore we decided it is better for most of our visitor if we do not include Microsoft links and later on we will create our own article and include it.

    Take care

    Emil

    Sunday, December 30, 2012 2:30 PM